texts: mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0

texts:
I'm struggeling the last two days with the following issue:

Restoring a ~1GB database dump with only innodb tables on a mysql 5.7 server takes about 5 minutes. After upgrading the same server to mysql 8.0(.13) it takes about 40 minutes (with exactly the same configuration). Are there any changes in mysql 8.0 that can cause the performance lack?

The dump was created local on a mysql 5.7 server with the following command:

$ mysqldump --opt -uuser -p database > database.sql

The dump was restored locally with the following command:

$ mysql -uuser -p database < database.sql

The first few lines of database.sql:

-- MySQL dump 10.13  Distrib 8.0.13, for Linux (x86_64)
--
-- Host: xx.xx.xx.xx    Database: xxx
-- ------------------------------------------------------
-- Server version       5.7.19-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,     FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'        */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `xxx`
--
[...]

This issue is tested on a virtual testserver with exactly the same hard- and software configuration in all test cases:

OS: Debian 9.6
Disk: SSD
CPU: 4 CPU Cores
RAM: 10 GB

The my.cnf is the same for mysql 5.7 and mysql 8.0(.13) server:

[mysqld]
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
language                = /usr/share/mysql/english
skip-external-locking
skip-name-resolve

innodb_buffer_pool_size         = 2048M
innodb_log_buffer_size          = 128M
innodb_log_file_size            = 265M
innodb_flush_log_at_trx_commit  = 2
innodb_flush_method             = O_DIRECT

max_allowed_packet      = 64M
key_buffer_size         = 2048M

thread_stack            = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

server-id               = 7
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_format           = MIXED

lc_messages                     = de_DE
default_authentication_plugin   = mysql_native_password

character-set-client-handshake  = FALSE
character-set-server            = utf8mb4
collation-server                = utf8mb4_unicode_ci

sql_mode        = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Any suggestions? Thank you folks!


texts: Package deployed in SSIS 2012 catalog fails with timeout error

texts:
I created an SSIS package in Visual Studio 2008 and deployed it to SQL Server 2012 by using Integration Services Catalogs. When I run the package from BIDS it completes successfully. When I schedule the package to run using a job the execution of the job fails (sometime randomly) with the following message:

  Environment reference Id: NULL. Description: Timeout expired. The
  timeout period elapsed prior to completion of the operation or the
  server is not responding. Source: .Net SqlClient Data Provider

I tried to add retry for this step and at second retry it works fine. I don't know the reason,  this job worked for 2 years with no issues.


texts: How to change pg_largeobject data from psql? (posgresql-9.1)

texts:
16-byte password hashes of some application are stored in the postgresql-9.1 database as LOB data. (I don't know why)

So I can get hash:

# select * from pg_largeobject where loid=16916;
 loid  | pageno |                data
-------+--------+------------------------------------
 16916 |      0 | \x098f6bcd4621d373cade4e832627b4f6

How can I replace data of this blob or create new blob with specified data in postgres-9.1?

I see that there is lo_from_bytea function since posgresql-9.4. But there is no something like this in 9.1.

I can use lo_import - load lob from external file, but it is hard-way.
Is there something simplier?


texts: How to add/update a column with an incremented value and reset said value based on another column in SQL

texts:
I am stumped, I have searched and researched until my browser tabs no longer show the close button ("X") until you hover over them.
I tried rewriting the search term over and over but I guess I do not know exactly what to ask so I apologise if this has been answered already.

I am trying to figure out how I can add an INT column to a table which starts at and increments by 1 (let's call this field version) based on another columns value (which we shall call case_id) IF that value is the same as the previously iterated row - in the case it is NOT the same, then the version value should reset to 1.

I have tried to figure it out by either updating a temp table I create, or adding the value during the SELECT INTO statement.

The purpose of this table is to store a record of the historic updates to a case, identified by the case_id column, with this version value in the same order as the modify timestamp.

Consider this table structure as an example of the source data:

---------------------------------
| case_id | modify_time         |
---------------------------------
| 1       | 2015-05-01 11:12:13 |
| 1       | 2015-05-02 12:13:14 |
| 1       | 2015-05-03 13:14:15 |
| 2       | 2015-05-01 11:12:13 |
| 2       | 2015-05-01 12:13:14 |
| 3       | 2015-05-02 11:12:13 |
---------------------------------

Consider this as an example of exactly what I want to achieve as the end result:

-------------------------------------------
| case_id | modify_time         | version |
-------------------------------------------
| 1       | 2015-05-01 11:12:13 | 1       |
| 1       | 2015-05-02 12:13:14 | 2       |
| 1       | 2015-05-03 13:14:15 | 3       |
| 2       | 2015-05-01 11:12:13 | 1       |
| 2       | 2015-05-01 12:13:14 | 2       |
| 3       | 2015-05-02 11:12:13 | 1       |
-------------------------------------------

As you can see, I need to add the version (INT) column and insert an incremented value based on the modify_time (ascending order) which will reset when the case_id value changes.

I have tried a few things I have stumbled on during my searches, but nothing I have come across has been able to help me figure this out.

I guess this is just a little too advanced for me.. :-(
This is on SQL Server 2012 and I am not afraid to try anything no matter how advanced as I can only learn from this exercise.

Thank you for taking the time to view my question and thank you in advance if you can help me out here.
Cheers.


texts: How to shard a database like that of SO?

texts:
I know a bit about sharding but I cannot wrap my head around that fact that say eventually SO cannot scale vertically then how would they shard their SQL DB? Even if we assume SO will not reach that day consider this a theoretical problem.

We have questions by timestamp, by vote, by answers, by users and so on. How this kind of database be sharded, if possible at all?


texts: SQL Server 2016 All Queries recompile all the time (plan cache corruption?)

texts:
I have a SQL Server 2016 SP1 production server. It's been running without a restart for 330 days. Everything's been running fine. Until last week, one day, all of the sudden, every query on the server has been generating a compilation.

Usually when I check the Perfmon, I see a lot of Batch Requests/Sec but this day, I saw as much Requests/Sec as Compilations/Sec

When I tried to look at what's using my cache, I had nothing! I've never seen anything like that. It went on and on all day, the CPU was at 100% since it was compiling all the time.

Finally, the IT decided to restart the machine. Everything got fixed after a reboot. But I was wondering, have you ever seen something like that? Can the cache plan be corrupted or something? (Like maybe if I had ran a DBCC FREEPROCCACHE it would have fixed itself without a restart?)

Does that mean that I should reboot my server at least once every six months or so?

Here is a regular baseline for the same server.


texts: Cannot find slow (frozen) running query in Query Store

texts:
I have huge issues with a stored procedure that has not finished the last week. It seem slike it stops at around 140 000ms of CPU time and freezing there, while the elapsed time increases. This makes sense as the query is active but not doing anything.

The select from the query takes ~2 minutes to finish, while the procedure uses 3+ hours (I usually cancel job at 3 hours due to batch issues)

In general I would look for regressed/resource expensive in Query Store and analyze what has changed causing this query to take so much time, I cannot find it however.

My questions are:

What reasons makes the query stop from increasing CPU time? I assume this can me memory spill such as disk reads or sorting, but I do not know for sure.
Is it possible to find a query id (for tracking) when I cannot find it in Query Store? I have execution plan, SPID etc. I also know procedure name. How can I find it?
The procedure has paramteres as input, one which is used for a later join. Should I SET parameters in the procedure instead of just using them as input in procedure?
Any other suggestions regarding tracking and monitoring of a given procedure? I have heard about trace flags and extended events, but I do not have any experience with this.

As a note, we tried force 2 join hints since these joins were used in fast select. Plan changed but performance did not change. I know this is a last resort solution.

EDIT:

I tried to reduce parameter sniffing issues by declaring and setting values from SSIS-input in the procedure, and this seemed to solve issues in test environment, however test environment chose bad plan when recompiling.

Production environment has bad plan as estimate, how can I ensure that it chooses the solution and not the problem plan?


texts: Recursivly get a Tree Via self joined table

texts:
Using other questions here & Postgresql documentation I've managed to build a many-to-many self joined table.

However adding a WHERE clause is giving me trouble.

Problem:

A Category can have many child categories, and many parent categories. Given a category.Id, I want to retrieve the category, the category children, childrens children and so on.

Example: given this structure:

child_1
    child_11
        child_111
        child_112
            child_1121
    child_21
child_2

Given: a clause of id = child_11

Expected results:
child_11, child_111, child_112, child_1121,

Actual results: child_11, child_111, child_112

Here is my attempt: http://sqlfiddle.com/#!17/3640f/2

In case Sqlfiddle is down: https://www.db-fiddle.com/#&togetherjs=LhDjxfPHo6

Note: I don't care about duplicating the where clause, my application can handle that

Table structure:

CREATE TABLE Category(id SERIAL PRIMARY KEY, name VARCHAR(255));
CREATE TABLE Categories(parent_id INTEGER, child_id INTEGER, PRIMARY KEY(parent_id, child_id));

ALTER TABLE Categories ADD FOREIGN KEY (parent_id) REFERENCES category (id);
ALTER TABLE Categories ADD FOREIGN KEY (child_id) REFERENCES category (id);

Table data:

INSERT INTO Category(id, name) VALUES (1, 'parent_1');
INSERT INTO Category(id, name) VALUES (2, 'child_1');
INSERT INTO Category(id, name) VALUES (3, 'child_2');
INSERT INTO Category(id, name) VALUES (4, 'child_3');
INSERT INTO Category(id, name) VALUES (5, 'child_1_1');
INSERT INTO Category(id, name) VALUES (6, 'child_1_2');
INSERT INTO Category(id, name) VALUES (7, 'child_1_1_1');
INSERT INTO Category(id, name) VALUES (10, 'child_of_many');
INSERT INTO Category(id, name) VALUES (11, 'parent_1');
INSERT INTO Category(id, name) VALUES (12, 'parent_2');

INSERT INTO Categories(parent_id, child_id) VALUES (1, 2);
INSERT INTO Categories(parent_id, child_id) VALUES (1, 3);
INSERT INTO Categories(parent_id, child_id) VALUES (1, 4);
INSERT INTO Categories(parent_id, child_id) VALUES (2, 5);
INSERT INTO Categories(parent_id, child_id) VALUES (2, 6);
INSERT INTO Categories(parent_id, child_id) VALUES (5, 7);

My query which is giving me the children, but not the childrens children etc.
If I remove the WHERE clause's I can get all rows:

WITH RECURSIVE categories_category AS (
  SELECT id, 'Category' AS COLUMN_TYPE, c1.name
  FROM Category c1
  WHERE c1.id=2
UNION
  SELECT c2.id, 'Category' AS COLUMN_TYPE, c2.name
   FROM Category c1
   INNER JOIN categories cs1 ON c1.id = cs1.parent_id
   INNER JOIN Category c2 ON c2.id = cs1.child_id
   WHERE cs1.parent_id = 2
) SELECT * FROM categories_category

Edit: More Detailed example:

Given the following category row's, I'd like to be able to run a query given a WHERE clause the matches the id of warmStoreAlcohol and get a result of:

+---+------------------+
|id |name              |
+---+------------------+
|2  |warmStoredAlcohol |
|3  |vodka             |
|4  |beer              |
|5  |frozenBeer        |
+---+------------------+

coldStoredAlcohol would give a result of:

+---+------------------+
|id |name              |
+---+------------------+
|6  |coldStoredAlcohol |
|5  |frozenBeer        |
|7  |cooler            |
+---+------------------+

The database structure will not change often. In this example 'frozenBeer' has two parents, and should return for querying both warmStoredAlcohol and coldStoredAlcohol.

I am open to changing the table structure, adding new tables and even upgrading postgres version etc. The database will hold ~2,000 rows, therefore I value an easy-to-understand table structure over a super complicated optimal one. (But any solution is better than my broken one)


texts: Multiple Transaction Log files and performance impact

texts: So I've gotten into an argument with several people regarding the number of transaction log files a give databases should have.  I've seen several post saying you should only have 1 transaction log file for each database, nothing in any Microsoft whitepapers though. However in many cases I've seen that increasing the number of transaction log files for a given database will actually improve the write performance to the database.  I should note that all these databases are in Full recovery model and are using a large SAN frame for the I/O sub system.  If as many of the posts say that the transaction writes all happen serially to the one file until the end of the file is reached then the writes move on to the subsequent log file, why would increasing the number of log files end up have a very noticeable improvement on the Write speed to the Disk?  In the most recent case we saw IO jump from 700 Kb/s to more than 60 Mb/s by increasing the number of log files from 1 to 8.  Any input would be appreciated.


texts: How to recover data around corrupted data in table?

texts: I have a SQL Server database with some corrupted data unrepairable via DBCC CHECKDB in a table. I can get many of the rows before the corruption out - but how do I get the rows after the corruption out?


texts: macOS homebrew: database system was not properly shut down; automatic recovery in progress

texts:
I'm noticing some errors in my logs when restarting PG 9.6. I also tried upgrading to 10.1, and the problem continues. This is on macOS via a homebrew installation.

First, I shut down with "brew services stop postgres":

2017-12-16 13:56:09.778 EST [34295] LOG:  received smart shutdown request
2017-12-16 13:56:09.779 EST [34295] LOG:  worker process: logical replication launcher (PID 34316) exited with exit code 1
2017-12-16 13:56:14.787 EST [34317] FATAL:  terminating connection due to unexpected postmaster exit

Then I restart it with "brew services start postgres":

2017-12-16 13:56:43.152 EST [5604] LOG:  listening on IPv6 address "::1", port 5432
2017-12-16 13:56:43.152 EST [5604] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2017-12-16 13:56:43.153 EST [5604] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2017-12-16 13:56:43.215 EST [5619] LOG:  database system was interrupted; last known up at 2017-12-16 10:43:27 EST
2017-12-16 13:56:43.407 EST [5619] LOG:  database system was not properly shut down; automatic recovery in progress
2017-12-16 13:56:43.409 EST [5619] LOG:  redo starts at 0/5B51E580
2017-12-16 13:56:43.409 EST [5619] LOG:  invalid record length at 0/5B51E660: wanted 24, got 0
2017-12-16 13:56:43.409 EST [5619] LOG:  redo done at 0/5B51E628
2017-12-16 13:56:43.435 EST [5604] LOG:  database system is ready to accept connections

This is with a default postgres.conf. I believe the brew services script ends up running:

/usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres stop

I also tried using pg_ctl -D /usr/local/var/postgres stop, but that just gives:

waiting for server to shut down......................................... failed
pg_ctl: server does not shut down

What is the proper way to stop and then re-start postgres?


texts: Query for all the Postgres configuration parameters‘ current values?

texts:
You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands. These are two avenues for writing the settings, but how about reading?

➥ Is there a way to query for all the current settings?

I know the settings in the client authentication configuration file pg_hba.conf can be read with the pg_hba_file_rules view, like this: table pg_hba_file_rules ;. Is there something similar for postgresql.conf?


texts:
I try to get d.id only if all rows with same id have same pday:
with dif_zero as (d.id,
date_part('day', p.docdate)::text pday
from sh_billing.dogovor d
join sh_billing.pays p on p.dogovor_id = d.id and p.is_actual
)
select id
from
dif_zero
group by id
having sum(char_sal_dif) = 0
and avg(pday::int) =  (array_agg(pday))[1]::int
and avg(pday::int) =  (array_agg(pday))[2]::int -- and so on...

Maby there is other way (more simple) to get to know if all elements in array equals to one another?
Or I can use window functioin in subquery, and after take only rows with value = 1.
select id, count(id) over(partition by id) one_day

Wich way is better? Or maby there is other way to do it more corrent?


texts:
I would like to be able to get a table of function names and function owner names.

Currently, this query returns a list of functions for a given schema:

  SELECT  proname
  FROM    pg_catalog.pg_namespace n
  JOIN    pg_catalog.pg_proc p
  ON      pronamespace = n.oid
  WHERE   nspname = '<%= schema_name %>';

I can see there is a proowner column which returns the ID of the role.
https://www.postgresql.org/docs/10/catalog-pg-proc.html

What I would like is something that returns the name of the owner, I just don't know enough SQL yet to figure out how to JOIN the tables etc..

|function_name|function_owner|
______________________________
|func1        |func1_owner   |
|func2        |func2_owner   |

If someone could explain how to do this I would be very grateful!

Thanks


texts: Use EMCLI to update OEM Oracle Home Path

texts:
Is is possible to use the Enterprise Manager Command Line Interface (EMCLI) to change the Oracle Home Path?

We recently upgraded many databases, listeners, and ASMs, and I'd like to automate this process:

Select the target
Go to Target Setup --> Monitoring Configuration
Change "Oracle Home Path", click Next, click Submit.

I looked through the EMCLI Verb Reference and could not find a way to do this.


texts: Always return X rows with variable starting row

texts:
My goal is to return X rows from a table, where the value of X is some value in the low 100's.  For the purposes of testing this query, I am experimenting with different X values.

This is a take on pagination for populating a grid in a user interface.  I tried using Offset / Fetch in this query, but I could not get it to function with my @Partial variable.

@Partial is a string that will identify the starting row number and all row numbers that follow the first match.  This is pretty simple until @Partial has returned the last row.  In that instance, I want to return the last row plus the previous X - 1 rows.

My query appears to be functioning, but I feel that I'm over thinking the problem.  I have done hours of testing and online research.  I have provided a full working query that will execute.

When @Partial is null, then the query abides by the @StartRow and @NumWindowRows variables.  Otherwise, @NumWindowRows will be returned, starting or ending with the matched string.

Thanks for any assistance on this query.  To recap, I am getting my intended results, but I am looking for guidance on a simpler approach or method to improve efficiency.

/***
Dynamic variables
******/
DECLARE
@StartRowNum INT = 1
,@NumWindowRows INT = 3
,@Partial NVARCHAR(10) = 'M';

/*****
Test table
*******/
DECLARE @Customers TABLE (
    CustomerID VARCHAR(10)
    ,[Name] VARCHAR(155)
    );
/***
Generate test data
*****/
INSERT INTO @Customers(CustomerID, [Name])
SELECT Char(number+65), 'Customer ' +Char(number+65)
  FROM master.dbo.spt_values
 WHERE name IS NULL AND
       number < 26

/***
Return @NumWindowRows
******/

;WITH FullResult AS (
        SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
        ,COUNT(*) OVER () AS FullCount
        ,CustomerID
        ,[Name]
        FROM @Customers),
PartialResult AS (SELECT *, COUNT(*) OVER () AS FullCount
        FROM
        (SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
            ,CustomerID
            ,[Name]
         FROM @Customers) c
         WHERE CustomerID >= @Partial)

SELECT f.*
FROM FullResult f
LEFT OUTER JOIN PartialResult p ON f.RowNum = p.RowNum
WHERE (@Partial IS NULL AND f.RowNum >= @StartRowNum
        AND f.RowNum <= @NumWindowRows + @StartRowNum -1)
OR (f.RowNum >= CASE
                WHEN (SELECT MAX(FullCount) FROM PartialResult) < @NumWindowRows
                THEN ((SELECT MIN(RowNum) FROM PartialResult) - (@NumWindowRows - (SELECT MAX(FullCount) FROM PartialResult)))
                ELSE (SELECT MIN(RowNum) FROM PartialResult)    END
        AND f.RowNum <= CASE
                WHEN (SELECT MAX(FullCount) FROM PartialResult) > @NumWindowRows
                THEN ((SELECT MAX(RowNum) FROM PartialResult) - ((SELECT MAX(FullCount) FROM PartialResult) - @NumWindowRows))
                WHEN (SELECT MAX(FullCount) FROM PartialResult) + @StartRowNum < @NumWindowRows
                THEN f.FullCount
                ELSE (SELECT MAX(RowNum) FROM PartialResult) END
            )


texts:
I have made this query:

SELECT COUNT(*)
from sat_msgPos
where  TIMESTAMPDIFF(MINUTE,tmUpdate,NOW())<=30
  and idTrigger = 9
  and idVeh = 12590;

and an index (idVeh,tmUpdate) on a big table. It's much better after I created the index, but I want to make it faster.

Does anybody have a suggestion?


texts: How Do I Execute SSIS Catalog Packages As A Different User With TSQL?

texts:
We've been moving application packages from the older methods of storage and execution into the SSIS Catalog.  We're required to use an enterprise scheduling system instead of the SQL Server Agent to execute batch jobs and SSIS packages.  Basically, the scheduler opens a command line on the server and executes Windows commands.  In our case, we'd be using SQLCMD to execute a stored procedure like the one below.

Here's what I need to be able to do:

CREATE PROCEDURE usp_Execute_Packages @Package_name NVARCHAR(260)
WITH EXECUTE AS 'Domain\User'
AS
DECLARE @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=@Package_name, @execution_id=@execution_id OUTPUT, @folder_name=N'A Folder Name', @project_name=N'Test Deployment'

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

But, this doesn't seem to work; I get an error telling me that I can't use a SQL Server account to start an execution.  I assume it's having an issue with the  EXECUTE AS statement.  Any ideas how to start the package execution as a different user without using SQL Server Agent and proxy accounts?


texts: Why is Log File Auto Grow sometimes performed by my application user and sometimes by sa?

texts: Many database tasks (including Data File Auto Grow, Log File Auto Shrink, and Data File Auto Shrink) are completed only by the sa user, but Log File Auto Grow seems to be associated with both sa and my application user via .Net SqlClient Data Provider. Is there a reason for this?


texts: Why is PostgreSQL's auto-explain not working in AWS RDS?

texts:
These are my settings:

According to the documentation, as far as I can tell, this combination of settings along with a statement_timeout value of 3600000 should mean that hour-long queries time out, and well before then, they and anything else longer than ~214ms should be producing EXPLAIN output in the logs.  But they're not.

shared_preload_libraries already contained auto_explain by default when I checked.
The parameters above are applied to the server.
I've restarted the instance after applying the parameters.
My back end has reported an incidence of canceling statement due to statement timeout (which only started happening after I set statement_timeout) but there is nothing in the logs (at all, let alone in the vicinity of the time reported) showing anything other than xlog starting and stopping.

What else could be missing?


texts: Logical Replication without Direct Access?

texts:
Is there a way to do logical replication from a master to which we cannot directly connect?
I've been able to do physical replication by means of WAL-shipping, but that leaves me with a standby that only allows for basic reads (i.e., no logical replication subscriptions).
Is there software that can read directly from the WALs and emit SQL to a separate, master target DB?


texts: MariaDB 10.0.25 installs without configuration

texts:
While trying to install MariaDB on Ubuntu 15.10, I issue command

$ sudo apt-get install mariadb-server

and it outputs

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  libdbd-mysql-perl libmysqlclient18 libterm-readkey-perl mariadb-client-10.0
  mariadb-client-core-10.0 mariadb-common mariadb-server-10.0
  mariadb-server-core-10.0 mysql-common
Suggested packages:
  mailx mariadb-test tinyca
The following NEW packages will be installed:
  libdbd-mysql-perl libmysqlclient18 libterm-readkey-perl mariadb-client-10.0
  mariadb-client-core-10.0 mariadb-common mariadb-server mariadb-server-10.0
  mariadb-server-core-10.0 mysql-common
0 upgraded, 10 newly installed, 0 to remove and 0 not upgraded.
Need to get 0 B/11.6 MB of archives.
After this operation, 122 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

At the end the installation of packages are completed and the service mysql get installed. But, I never get a prompt/wizard for setting root credentials and other configuration. And later I'm not able to connect to the service

$ mysql -u -root -p
Enter password:
ERROR 1698 (28000): Access denied for user '-root'@'localhost'

Not able to understand what is going wrong.

UPDATE
What's more intriguing is the following

$ sudo mysqladmin -u root password <myPassword>
$ mysql -u root -p
Enter password: <myPassword>
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I'm completely puzzled.


texts: MySQL: error 1118 (42000) row size too large

texts:
storage_engine = MyISAM
table_type = MyISAM
version = 5.1.25-rc

database_name.sql dump size is 3.3G:

mysqldump -u user -p database_name > database_name.sql

When I try to restore the database (trying to restore on MySQL 5.1.25-rc and on 5.7.17):

mysql -u user -p database_name < database_name.sql

...an error:

  ERROR 1118 (42000) at line 7192: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

"Questions that may already have your answer" doesn't have answer for me :(


texts: Sub Query with group by returns more than 1 row Issue

texts:
I am facing the issue of subquery returns more than 1 row cause I am using group by in the subquery and outer query both to get the desired result.

This is my query

SELECT Count(*)                                       AS totalCat,
       attempted_questions.category_id_fk,
       (SELECT Count(*)
        FROM   attempted_questions
        WHERE  attempted_questions.correct = 1
        GROUP  BY attempted_questions.category_id_fk) AS correct
FROM   attempted_questions
GROUP  BY attempted_questions.category_id_fk

I want to get total questions count and total correct questions count from this table against the category, like this below result.

   Category   Total_Questions   Total_Correct
      1             4                 3
      2             3                 1

This above mentioned is the required result from my query but it is not working for me, I have tried to fix it but could not find any solution.

Please have a look at my attached image to see the database records and table structure.


texts: Query and index optimization for a table containing 10 million records

texts:
I was trying to retrieve data from a table A to retrieve data for 1 month, which has around 10 million records and 21 indexes built on it, It took me 3 min to retrieve around 20 columns and total of 300 K records from that table A joined with some other table B which has around 40000 records.

We are using SQL Server 2012 standard edition.

Will like to know how can I improve the query execution time.

Query plan can be found in the below link :

Query Plan

Server information  :
Server Info


texts:
I cannot see all queries with v$sql_monitor or gv$sql_monitor.  I can see some queries, none of which have /*+ MONITOR */.  I have tested a query with MONITOR and it still did not show.  I thought it might just be a big explain problem, so I adjusted my session with:

ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;

select first_refresh_time, last_refresh_time,
status, sql_text
from v$sql_monitor
order by last_refresh_time desc;

, but it didn't help.  What I am trying to do is see query submitted via an application.  The query works, so I know it is hitting the database.  I simply cannot see the query in Oracle.

What am I doing wrong?  I am using SQL Developer if that matters.


texts:
My HDD crashed, so, I can't make a Postgresql backup. It seems that my data tables are OK (I can view the data via pgAdmin). However, when I run pg_dump I get this:

pg_dump: Error message from server: ERROR:  could not read block 2 of relation
base/16384/2607: IO error
pg_dump: The command was: SELECT tableoid, oid, conname, connamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = conowner) AS rolname FROM pg_conversion

It seems that the system catalog pg_conversion is broken. How can I fix this problem?


texts: Changing Auto-Growth Setting to MBs vs Log Drive Huge Occupancy

texts:
These are our PROD Databases current settings and total information about log file utilization.

We are using SQL Server AOAGs 2014

Auto-Growth I am planning to set 512 MB

Recently we have seen huge T-Log drive utilization (close to 70 GB) each time Index Maintenance task runs (Re-build Index Task Online and Offline).

Our Log Drive Total Capacity is 300 GB (Expanded 100 GB and now 400 GB Total)

Now I am planning for below two changes :

I am planning to change the auto-growth setting to MBs from %.
Removed Maintenance Task (Rebuild Index Task) which is created from maintenance plans option.
Now I deployed Ola Hallengrens Index Optimization (Image below) and scheduled the SQL Job to run every Sunday @1:00 AM.

My Concerns and expert suggestions is highly appreciated:

Will the above changes help my Disk utilization to be at least bit lower than before. Because expanding drive each time is becoming burden for us.
What else should I keep in mind along with this to optimize Log Drive Occupancy to be lower.

Please see current settings :

PROD Server 1 :

Ola Hallengrens Code for Index Optimization:


texts: How do I compare all tables with nonzero rowcounts (in an archive db) with tables of the same name in the line of business db?

texts:
(Full Disclosure: I am a software tester who is out of my depth. Because I am unable to get a definitive answer from the developer due to the necessary independence of the test team, I have to figure this out myself.)

This query gives me a rowcount of all the tables in the archive db:

query0:

SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   ARC_DB.sys.tables ta
   INNER JOIN ARC_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN ARC_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
GROUP  BY sc.NAME,
      ta.NAME
ORDER  BY Sum(pa.rows) DESC

query1:

SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   LOB_DB.sys.tables ta
   INNER JOIN LOB_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN LOB_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
GROUP  BY sc.NAME,
      ta.NAME
ORDER  BY Sum(pa.rows) DESC

But how can I use this to compare the ARC_DB table names and row counts to the line of business (LOB_DB) db, whose tables have much larger rowcounts? Note: I do not care about those LOB_DB tables whose ARC_DB counterparts have no rows. Could I do something like giving query0 a variable in the "from" clause, giving query1 a variable through an inner join, then comparing one variable to another? Does that make sense?

Any help is super appreciated. I am on a deadline, and will respond immediately.


texts: Generate nested array with OPENJSON very slow

texts:
I've just started using OPENJSON with SQL Server 2016 SP1.

I have this sentence:

select c.Serial as Parent,
    (Select co.Serial, agc.Position
      from AggregationChildren agc, Aggregation ag, Code co
      where agc.AggregationId = a.AggregationId
      and co.CodeId = agc.AggregationChildrenId for json path) as children
    from Aggregation a, Code c
    where c.CodeId = a.AggregationId for json path

To generate this JSON:

{"Parent":"4244287599479491","Children":
[{"Serial":"8915753733724633","Position":"1"},
{"Serial":"1247782815710855","Position":"2"},
...]}

But it is very very slow.

My problem is with the Children array because I don't know how to get it.

Is there a way to do it faster?

These are the tables:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [ ... ],
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    [ ... ]
)

CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [ ... ],
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)

CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

The Serial column is an nvarchar(20) since the values may be any combination of alphanumerics, even though my example only shows numerics.


texts: SQL Server 2012 Can't log in with machine account

texts:
We're migrating an IIS/SQL Server site from 2008 to 2012, and I'm having a problem with authentication. We use Windows Authentication to authenticate as DOMAIN\COMPUTER$, but this isn't working, giving an error saying:

  Login failed for user 'DOMAIN\COMPUTER$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

I've verified that the account exists, and even deleted and recreated it

create login "DOMAIN\COMPUTER$" from windows

If I do it again, I get the response:

  Msg 15025, Level 16, State 2, Line 1
  The server principal 'DOMAIN\COMPUTER$' already exists.

(DOMAIN\COMPUTER is substituted for the real domain and computer name)

So it appears that the site is querying the SQL Server, but the SQL Server isn't looking at its logins very hard.

What have I missed?


texts: Scaling and Reducing Reads On A Similar Query Filter

texts:
Suppose that I run five queries against the same table with the same starting filter, like:

SELECT ColumnDate, ColumnKey, ColumnValue
FROM QueryTable
WHERE ColumnDate > DATEADD(DD,-1,GETDATE())
---- each other query would have additional criteria

All queries hit the same exact table five times each with further filters and grabbing one record.  The starting filter is always the same though - and it generally grabs less than 1% of the total records on the table.

An alternative would be to read the table once, keeping it in a subset - like a CTE or temp table - then filtering on that subset in further queries or subqueries in the SELECT statement, which would be a fraction of the table with only one read of main table.  Temp table example:

SELECT ColumnKey
(SELECT ColumnKey FROM #temp WHERE ColumnValue = 'sf')
FROM #temp
WHERE ColumnValue = 'nyc'

On a smaller data set of less than 10K records, I don't see an impact in testing this alternative; as this scales to higher amounts, where the date could be filtering a two day period out of twenty years, would this start to perform better?

Or, another better question to ask is what is a better "best practice" than hitting a table five times with a similar query, if any?


texts: How to check for record which has no entry in another table with some specific value?

texts:
For example, if we have two tables: Table1 and table 2

Table1 - main table (with unique entry for all records)
table2 - is a history table(where is containg many entries for same record on time stampe basis) (also contains some specific field type)

I need to retreive data like, all data from table1 where there is no entry for the record with type = 'Some vaue'


texts: How I can copy from local file to remote DB in PostgreSQL?

texts:
I am a novice in psql and need some help.
How can I load a local CSV to a remote DB?

I am using the following command

\COPY test(user_id, product_id, value)
      FROM '/Users/testuser/test.tsv' WITH DELIMITER '\t' CSV HEADER;

but this searches the file on the remote DB while I need to do that on my local PC.


texts:
What are the benefits (if any) of dropping a temporary table in SQL Server?

eg:

DROP TABLE #temp;

Since temporary tables are automatically dropped by the server this statement would seem to be non-functional.


texts:
I have a database AfricanRatings on which I used the query

INSERT INTO AfricanRatingsAVG
SELECT RecipeID,
       Count(*) AS cnt,
       AVG(Rating)
FROM   AfricanRatings
GROUP  BY RecipeID
ORDER  BY RecipeID

to get the average rating per recipe and the total count per recipeID.  The average and the count were placed into a table AfricanRatingsAVG via the query. On the initial query, AfricanRatingsAVG is updated but not with subsequent ratings.  I have tried all sorts of UPDATES to get the AfricanRatingAVG table to update every time a new rating is added to the AfricanRatings table, however, no update occurs.  Any guidance in the right direction would be appreciated.


texts: Data distribution in mongos with shards or replica sets

texts:
I am a newbie to mongodb.I have set an mongodb architecture with a single mongo shard(mongos) connected to three shards(replica sets) with three config servers. The architecture looks as below:

Now the mongos are connected with these shards.The data in the mongos will be splitted among these three shards.What is my doubt is whether the shard 1 contains the same exact data as that of shard 2 and shard 3 or all the 3 shards have different data.


texts:
I have set sql.expire_time parameter to 10 which recommends me to keep in my two node RAC database. At times, my application gets the error ORA-3135, therefore I suspect that this parameter could have some role to play here..

What exactly does this parameter do? Does it have anything to do with the error im facing?

I do not have any firewalls between my database and app server.


texts:
I need to create copy/replication of our databases for the development environment.

I'm not sure how to do it. DBs are updating many times during a day; not only new records added but also  old records are updated.

How should I implement it?


texts: Oracle: using "as of" clause with table aliases?

texts:
I can run this flashback query with no problem:

select x from a as of timestamp sysdate;

But if I use a table alias I get an error.

select foo.x from a foo as of timestamp sysdate;
ORA-00933: SQL command not properly ended

How can I use "as of" with table aliases?


texts:
I am logging the DBCC CHECKDB job output to a table.

When I look at the job log using the below command, I want to verify that all is well.

use msdb
EXEC dbo.sp_help_jobsteplog
@job_name = N'DatabaseIntegrityCheck - USER_DATABASES' ;

Would you say that we are good if we see "Outcome: Succeeded" in the log for the job or is there something else we should also review?

We are using Ola's job output but I am also logging it to a table and am just wondering about the output. If we see "outcome succeeded' in the job, is this enough to know the CHECKDB found nothing wrong?


texts: Levenshtein function returning different result based on limit value


texts: I want to sharpen my database normalization skills.  Where can I find good quality beginner-to-advanced level exercises (with solutions) on the web ?


texts: Is it possible to search within a MySQL JSON array in a case insensitive manner?

texts:
e.g. with the table:

create table test (id int primary key, tags json);
insert into test (id, tags) values (1, '["Lorem ipsum", "dolor", "sit"]'), (2, '["foo", "bar"]');

This will match:

select id from test where json_search(tags, 'one', 'Lorem%') is not null

But this will not:

select id from test where json_search(tags, 'one', 'lorem%') is not null

Ideally I'd like to be able match both cases.

Running on 5.7.16 Percona XtraDB Cluster


texts: Cursor-SQL Help- Need another Column for Dynamic SQL

texts:
My company decided to change the value "Unknown" to (-1) in ALL of our lookup tables (tables that end with 'LU'). I am trying to write a script to automatically add a value for VALUES(-1, 'Unknown')

BEGIN TRAN

DECLARE @tablename nvarchar(250)
DECLARE @idcolumn nvarchar(250)
DECLARE @command nvarchar(500)

DECLARE LU CURSOR LOCAL FAST_FORWARD for
select TABLE_NAME, COLUMN_NAME
      from INFORMATION_SCHEMA.COLUMNS
       where TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE '%LU'
       and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
       order by TABLE_NAME

OPEN LU
WHILE 1=1
BEGIN
    FETCH NEXT FROM LU INTO @tablename, @idcolumn
    IF @@FETCH_STATUS<>0
    BEGIN
        BREAK
END

SET @command = N'
SET IDENTITY_INSERT '+ @tablename+ ' ON
INSERT into '+ @tablename+ ' ('+@idcolumn+')
VALUES(-1)
SET IDENTITY_INSERT ' +@tablename+ ' Off'
PRINT @command
END
CLOSE LU
DEALLOCATE LU

ROLLBACK tran

which outputs something like this:

SET IDENTITY_INSERT AccessiblityLU ON
INSERT into AccessiblityLU (AccessiblityID)
VALUES(-1)
SET IDENTITY_INSERT AccessiblityLU Off

I am a beginner and realize Cursors shouldn't be used when there are other options available. I tried adding another cursor to grab the second column from each table (the ordinal position of the columns i need is always 2 luckily).
I tried this with no luck:

BEGIN TRAN
--DECLARE VARIABLES
DECLARE @tablename nvarchar(250)
DECLARE @idcolumn nvarchar(250)
DECLARE @command nvarchar(500)
DECLARE @secondcol nvarchar(100)

--FIRST CURSOR
DECLARE LU CURSOR LOCAL FAST_FORWARD for
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE '%LU'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

OPEN LU
while 1=1
BEGIN
FETCH NEXT from LU into @tablename, @idcolumn
if @@fetch_status<>0
begin
    break
end
DECLARE @secondcol nvarchar(100)
--SECOND CURSOR
DECLARE secondcolumn cursor local fast_forward for
select name
from sys.columns
where OBJECT_NAME(object_id) = @tablename AND column_id=2

OPEN secondcolumn
while 1=1
BEGIN
FETCH NEXT from secondcolumn into @seondcol
if @@fetch_status<>0
begin
    break
end

deallocate LU
deallocate secondcolumn

SET @command = N'
SET IDENTITY_INSERT '+ @tablename+ ' ON
INSERT into '+ @tablename+ ' ('+@idcolumn+', '+@secondcol+')
VALUES(-1, ''Unknown'')
SET IDENTITY_INSERT ' +@tablename+ ' Off'
PRINT @command
end

Any help would be greatly Appreciated!


texts:
I have a dedicated webserver and a dedicated database server.

Often, I get errors connecting to the database server

couldn't connect to database

Here is my current /etc/my.cnf configuration file

[mysqld]
local-infile=0
innodb_file_per_table=1
max_allowed_packet = 100M

log-slow-queries=/var/log/slow.log

open_files_limit=7306
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
nice = -10

How can I increase the timeout connect for the database server?


texts: Use only one Junction Table per Database?

texts:
I am currently working on a Database-Design for an insurer. Example-Entities are "Customer", "Address", "Vehicle", "Building" etc..
So there are many Relations and every Relation has some Context. e.g. Customer-Vehicle: Customer can be owner, driver, lessee of a vehicle...
Basically I see two options on how to handle that:

(1): A Junction Table for every relation, with the two foreign keys and a context-ID. This would lead to many junction tables, with partially very few data in it. Selects would be a little bit difficult, because of the amount of tables.

(2): One Junction Table for the whole Database. This table would contain two foreign keys, one key as to what relation it is (e.g. customer-vehicle) and a key for the context. This would lead to a huge junction table, but also simpler selects.

Which one is the better solution? Are there any main advantages/disadvantages or even other solutions I didn't think of?

Thank you for your help. If I didn't express myself clear enough or something is not understandable, please ask. (English is not my native language)


texts: Reference implementation of a scalable storage system with strong consistency?

texts:
Is there a storage system with these properties?

horizontally scalable - can handle arbitrary read and write throughput if you add enough nodes to the cluster
consistent, in the sense that all reads will reflect the latest successful writes
[edit] "safe" in the sense that if a write is reported successful, it is guaranteed that all subsequent successful reads will reflect that write
tolerant of individual node failures

In CAP terms, I'm interested in systems which guarantee consistency rather than availability in the event of network partitions. Or systems which at least can be configured to behave this way.

I'm not concerned with latency, as long as writes are guaranteed to terminate eventually.

I'm not asking which tool I "should use". I'm asking, out of academic interest, for a proof of concept of a CP storage system. It doesn't matter if it's modern, actively maintained, widely used or what not; I'm just seeking a reference implementation.


texts:
Does mysql have a way to delete all sub links if a row was deleted from a "parent" table?

Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the data as the table ex_item_a will have a lot of similar tables[they have that same foreign key relationship]....In this case, let's say if I delete someone from the user table it should delete all links.

CREATE TABLE IF NOT EXISTS `account` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `user` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `account_id` int(10) unsigned NOT NULL,
    `email` varchar(50) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `account_id` (`account_id`),
    CONSTRAINT `FK_user_account` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `account_tbl_ex` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `account_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `account_id` (`account_id`),
    CONSTRAINT `FK_account_tbl_ex_account` FOREIGN KEY (`account_id`) REFERENCES     `account` (`id`)
);

CREATE TABLE IF NOT EXISTS `account_tbl_ex_link` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `link_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `link_id` (`link_id`),
    CONSTRAINT `FK_account_tbl_ex_link_account_tbl_ex` FOREIGN KEY (`link_id`) REFERENCES `account_tbl_ex` (`id`)
);

CREATE TABLE IF NOT EXISTS `ex_item_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `tbl_link_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `tbl_link_id` (`tbl_link_id`),
    CONSTRAINT `FK_ex_item_a_account_tbl_ex_link` FOREIGN KEY (`tbl_link_id`) REFERENCES `account_tbl_ex_link` (`link_id`)
);


texts:
I have the following table which I'm trying to limit the column "prefix" to ASCII alphabetical characters. However, I am still able to insert other characters after using the following constraint. Why is it not working?

CREATE TABLE test
(
    id INTEGER NOT NULL PRIMARY KEY,
    prefix TEXT NOT NULL,
    CHECK(prefix NOT LIKE '%[^a-zA-Z]%')
)

Using Python's sqlite3 package and DB Browser for SQLite


texts: Demographics Dimension Table - Finding the Key!

texts:
I have a dimension table which captures demographics on Employees.  The schema is similar to below:

    CREATE TABLE [dbo].[DimDemographics]
(
    [DemographicsKey] [int] NOT NULL,
    [AgeBand] [nvarchar](50) NULL,
    [LowerAge] [tinyint] NULL,
    [UpperAge] [tinyint] NULL,
    [SalaryBand] [nvarchar](100) NULL,
    [LowerSalary] [int] NULL,
    [UpperSalary] [int] NULL,
    [Gender] [nvarchar](50) NULL,
    [MaritalStatus] [nvarchar](50) NULL,
)

In the AgeBand, you'll have values such as '25 or Less', '26-30', 31-35'....'Over 65'
The LowerAge and UpperAge are just to allow us to query, i.e '25 or Less' will have a LowerAge of 0 and an UpperAge of 25 and so on.

The SalaryBand, LowerSalary and UpperSalary follow the same principal as AgeBand.

In T-SQL, we've written a query which joins the Employee table to the Demographics table and can extract the DemograhicsKey based on their age, salary, marital status and gender.

The Fact table in question has an Employee Key and a Company Key and we've obtained these with lookups - No problem here.

The problem is how to get a DemographicsKey in SSIS.  We've tried lookups, OLE DB Commands.  The SQL Query is similar to

    SELECT d.DemographicsKey
FROM dbo.DimEmployee e
INNER JOIN dbo.DimDemographics d on
    e.Age >= d.LowerAge and e.Age<=UpperAge
    and e.Gender=d.gender
    and e.Maritalstatus = d.MaritalStatus
    and CAST(e.SalaryReferenceLocal AS FLOAT) >= d.LowerSalary and CAST(e.SalaryReferenceLocal AS FLOAT) <= d.UpperSalary
    and DATEPART(YEAR, DATEADD(YEAR, -e.Age, GETDATE())) >= d.LowerYear AND DATEPART(YEAR, DATEADD(YEAR, -e.Age, GETDATE())) <= d.UpperYear
WHERE e.EmployeeID =?

The ? is because of the OLE DB Command.  I was trying to do it here, but could not get any output from a T-SQL query input.  One consideration was to use a Stored Procedure with OUTPUT, but at this point, I'd prefer to avoid that approach and solve it (if possible) with transformations!

I'm out of ideas now as to how to obtain a key from the demographics based on a list of criteria.

For reference, the table and code is scaled down and not the full version, but has enough information to describe the problem.

Any help would be greatly appreciated!

Thanks!


texts: SQL Server Management Studio 2012 separated toolbars

texts: In SQL Server Management Studio 2012, is there a way to get the toolbars separated, so there is space between them? I can do this with 2008 R2 by just dragging the toolbar handle. But in 2012, I can snap them to a different row, but if I just drag them apart, they always dock right next to each other. I find it convenient to have some space between them, with the left-most button of each one being my most-used.


texts: Why does mongodb require three config servers

texts: The official mongodb documentation states to use three config servers. They say its for redudancy, because 1 server would be a point of failure.... but why not 2? in a production sharded cluster, if I have 1 shard, I shouldn't need more than 2, right? Is one config server only meant to handle 1 shard? so, if the minimum is 2 shards, then at least I should have 3?


texts: WAL archiving from a standby backup strategy Postgresql 9.3

texts:
I am trying to figure out if wal archiving from a standby is possible and a viable option for a backup strategy on Postgresql 9.3.

The idea is to be able to take regular base backups and wal archives from a standby server in a remote DR site.

Unfortunately, the archive_command functionality is not available while the server is in standby_mode mode so I am wandering if the wal archiving could be done successfully using alternative ways like rsync, lsyncd etc.

Has anyone tried to this path can you share your experience?


texts:
I am having this strange effect from evaluating truthiness of a field

IF ( a IS NOT NULL && ((b IS NOT NULL) || (NOT b = '')) && c IS NOT NULL ) THEN
   set NEW.results = "Passed"
ELSE
   set NEW.results = "Failed"
END IF;

Basically, if a,b,c are not null (or empty) then pass otherwise fail.

When there is no data for b, the field is set to NULL and the condition returns "Failed"

BUT

When no data is an empty string ('') the condition returns "Passed"

What am I missing here?

UPDATE:
@akina pointed out what my logic was delivering, which was not desired.

I adjusted the condition logic to the proper condition:

( NOT ((v_sectionnumber IS NULL) || (v_sectionnumber = '')))

which gave the desired results.


texts: How should I model a "communication preferences" field which may require different supporting info for each row?

texts:
I am building a system which sends information to other systems.  Each client system has its own communication requirements.  For example, a client system might require:

An HTTP request
An HTTPS request
An HTTPS request with some form of authentication
An email
A fax
A physical letter in the mail
A carrier pigeon
Other methods we have yet to discover

Each of these methods will likely require ancillary information.  For example, a fax requires a fax number, while an HTTP request requires a URL.

Currently, we have a simple "client" table that looks something like this:

id           NUMBER
name         VARCHAR
description  VARCHAR

What is the best way to model each client's communication requirements in our database?

The simplest way would be to add a number of nullable columns to this table that store the information in the client's row:

id              NUMBER
name            VARCHAR
description     VARCHAR
comm_pref       VARCHAR  // would store things like 'email', 'fax', 'http', etc.
http_address    VARCHAR
https_address   VARCHAR
https_username  VARCHAR
https_password  VARCHAR
https_api_key   VARCHAR
email_address   VARCHAR
fax_number      VARCHAR
mailing_address VARCHAR
pigeon_name     VARCHAR

But not only does this look ugly, it doesn't scale well if new communication methods are added in the future.

Is there a good way to model this kind of data?


texts:
The table is:

Employee (
    EID,
    Ename,
    PID,
    ProjectName,
    MID,
    ManagerName )

For the functional dependencies I got:

EID==>(Ename)
PID==>(ProjectName)
MID==>(ManagerName)

and for BCNF tables I got:

Employee(EID,Ename)
Project(PID, ProjectName)
Manager(MID, ManagerName)

Is this done right?


texts:
I would like request how to do cross tab in SQL using two table.

I want achieve result :

        amy           pal          chin
amy               2017-02-18   2017-02-18
pal                            2017-02-18
chin 2017-02-18

Below is my stored procedure:

USE [IRIS]
GO
/****** Script Date: 02/20/2017 14:03:28 ******/
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[crosstab]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[crosstab]
GO

CREATE PROCEDURE CROSSTAB
AS
DECLARE @SQL1 VARCHAR(8000)
DECLARE @VAR1 INT
DECLARE @VAR2 INT
DECLARE @VAR3 VARCHAR(100)
SET @VAR1=(SELECT DISTINCT UserId FROM [IRIS].[dbo].[NDRoaster])
SET @SQL1='SELECT Mysingle, '
WHILE @VAR1 <> 0
BEGIN
SET @VAR3=(SELECT Mysingle FROM [IRIS].[dbo].[I_User] WHERE Id =@VAR1)
SET @SQL1=@SQL1 +' MAX(CASE Uids WHEN '+CONVERT(VARCHAR(10),@VAR1)+' THEN Dates  ELSE  '''' END) AS ['+CONVERT(VARCHAR(MAX),@VAR3)+'],'
SET @VAR1=@VAR1+1
END
SET @SQL1=LEFT(@SQL1,LEN(@SQL1)-1)
SET @SQL1=@SQL1+'
 FROM (SELECT
  A.UserId AS Uids,
  A.DateShcdule AS Dates,
  B.Mysingle AS Mysingle
  FROM [IRIS].[dbo].[NDRoaster] A LEFT JOIN [IRIS].[dbo].[I_User] B
  ON A.UserId=B.Id)AS MYTABLE GROUP BY Uids'
PRINT @SQL1
EXEC (@SQL1)
Go

I prompt error as below:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.  SELECT Mysingle   FROM (SELECT    A.UserId AS Uids,
  A.DateShcdule AS Dates,   B.Mysingle AS Mysingle   FROM
  [IRIS].[dbo].[NDRoaster] A LEFT JOIN [IRIS].[dbo].[I_User] B    ON
  A.UserId=B.Id)AS MYTABLE GROUP BY Uids  Msg 8120, Level 16, State
  1,Line 1 Column 'MYTABLE.Mysingle' is invalid in the select list
  because it is not contained in either an aggregate function or the
  GROUP BY clause.

I am following the article SQL Server: Dynamic Cross-Tab

There are 2 tables:

(1) user

[Id],
[Mysingle],
[Password],
[EmpId],
[LevelActive],
[DateCreated],
[LastLoginDate],
[DeptId] ,
[EmailId] ,
[Isactived],
[CommTypeId],
[IPAdd],
[HOD]

(2) Roaster

[NDR_Id],
[UserId] ,
[DateShcdule],
[DateCreated]

I'm still in learning process on SQL Server.


texts:
I have a strange behaviour on one of my SQL Server's tempdb.
It has six tempdb files, according to SSMS and queries, all pointing to S directory. Each of them have ~5gb of initial size.
The weird thing is S is quorum actually with only 2gb of free space. Tempdb files simply don't exist in S nor in its sub directories.
The question is where are the tempdb files? :)  How can I physically see them?
Thank you.


texts: Insert Into Array of object MongoDB Ask Question

texts:
I have

{
    "_id" : ObjectId("5a25af80d2c4dd065991dccc"),
    "username" : "abc@gmail.com",
    "password" : "$2a$10$ptFyLKtyyrL5gMYdXS6wV.HLUA4Py5iudMJDldf5qsHFS4.9TPCyy",
    "role" : "Admin",
    "__v" : 0,
    "list" : [
        {
            "name" : "We",
            "arr" : [
                "5a26d554677475818a795f75",
                "5a395bb0976d5304c07f7dd4"
            ]
        },
        {
            "name" : "sandeep",
            "arr" : [
                "5a26d554677475818a795f75"
            ]
        }
    ]
}

I want to add an element inside list.arr where name = 'we' and add only if the element does not exist

how do i perform this query.


texts: I am working on a basic blood bank database on Oracle. I would like to create a completely basic normalized table and then I will add more details to it. I have done it to the 2NF and I will further proceed on to the 3NF afterwards, but first can someone take a look at my normalization and help me improve it (if needed) or any advices regarding it.


texts: Do SQL service packs effect SQL Compatibility mode?

texts:
We will soon be upgrading from a SQL 2008 R2 instance to a SQL 2016 instance.

One of our databases has an application which our supplier says is compatible up to SQL 2012 SP1, but not SQL 2012 SP2, 2014 or 2016.

Once we upgrade to SQL 2016 and change the compatibility level of the database to 110 (2012), would the fact that the application is not compatible with SQL 2012 SP2 have any effect?


texts: Sysaux tablespace is almost full . Could this be the reason AWR snapshot is not generated ? Although I do have the AutoExtend ON for SYSAUX tablespace.


texts: SQL Server experiencing THREADPOOL waits while still having plenty of available workers

texts:
I recently took a look at my production SQL Server's cumulative threadpool waits, and it's literally days worth of waits. So I started doing some digging.
I'm on a 32 core, 64 bit server (running on EC2). Max workers is correctly set to 0 in config, and the following DMV reports the correct, expected number:
SELECT max_workers_count FROM sys.dm_os_sys_info

Output: 960

Looking at my total number of workers, I'm rarely seeing any over 300-400:
SELECT count(*) FROM sys.dm_os_workers

Output: 372

And yet, if I run the following DMV, I always have threads listed:
SELECT * FROM sys.dm_os_waiting_tasks
WHERE wait_type = 'THREADPOOL'

SELECT dow.state , dow.is_preemptive , dow.is_sick , dow.is_in_polling_io_completion_routine , [Num Workers] = COUNT(1)
FROM sys.dm_os_workers dow
GROUP BY dow.state , dow.is_preemptive , dow.is_sick , dow.is_in_polling_io_completion_routine;

The waits in milliseconds are usually pretty short, 20-200 ms generally, and they disappear quickly as well, but they are adding to the overall cumulative threadpool figure. They also never have blocking sessions.
I am stumped as to why anything is encountering a threadpool wait when I have so many available workers. Shouldn't the hundreds of available workers be handling these requests instantly without any threads going to threadpool?
I would appreciate any input or direction here.
SQL Server Version: SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64) Enterprise Edition: (64-bit) on Windows Server 2016 Datacenter 10.0  (Build 14393: ) (Hypervisor)
Cost Threshold for Parallelism is set to 1400.
Hyperthreading is not enabled.
Maximum Degree of Parallelism is set to 8.


texts:
I am trying to understand how mongodb works with kerberos. To do that I have configured a kerberos server, just only for local. This is the configuration of my realm:
this is the content of krb5.conf file:
[libdefaults]
    default_realm = LOCALHOST

# The following krb5.conf variables are only for MIT Kerberos.
krb4_config = /etc/krb.conf
krb4_realms = /etc/krb.realms
kdc_timesync = 1
ccache_type = 4
forwardable = true
proxiable = true

v4_instance_resolve = false
v4_name_convert = {
        host = {
                rcmd = host
                ftp = ftp
        }
        plain = {
                something = something-else
        }
}
fcc-mit-ticketflags = true

[realms]
    LOCALHOST = {
        kdc = localhost
        admin_server = localhost
}
[login]
    krb4_convert = true
    krb4_get_tickets = false

And this is the content of kdc.conf file:
[kdcdefaults]
    kdc_ports = 750,88

[realms]
    LOCALHOST = {
        database_name = /var/lib/krb5kdc/principal
        admin_keytab = FILE:/etc/krb5kdc/kadm5.keytab
        acl_file = /etc/krb5kdc/kadm5.acl
        key_stash_file = /etc/krb5kdc/stash
        kdc_ports = 750,88
        max_life = 10h 0m 0s
        max_renewable_life = 7d 0h 0m 0s
        master_key_type = des3-hmac-sha1
        supported_enctypes = aes256-cts:normal arcfour-hmac:normal des3-hmac-sha1:normal des-cbc-crc:normal des:normal des:v4 des:norealm des:onlyrealm des:afs3
        default_principal_flags = +preauth
    }

The kerberos seems to work fine. I created some principals (two users for test, one user for admin and one for the mongo server) and I tested that I can create tickets for each of them with kinit. This is the list of the principals:
ardi/admin@LOCALHOST
mongodb/localhost@LOCALHOST
testUser@LOCALHOST
testUser2@LOCALHOST

The problem comes when I trying to start the mongodb service with the GSSAPI mechanism. The service never starts and I get this message:

2015-03-10T12:52:12.285+0100 ERROR: Couldn't find mech GSSAPI
2015-03-10T12:52:12.286+0100 SEVERE: Failed global initialization:
BadValue SASL(-4): no mechanism available: Couldn't find mech GSSAPI

Of course, if I remove the authenticationMechanisms parameter, mongodb is started without no problems
Other information that could be useful:

The value of the krb5_ktname is /home/ardi/test/mongodb.keytab
The permissions of the keytab file are 777 (I know this is wrong but it is only for test)
My SO is ubuntu 14.04
I have this problem with the mongo enterprise versions 2.6.8 and 3.0.0

Does anyone of you have any idea where is the problem?
Thank you in advance for your time


texts: Using Pt-online schema change on multi master MYSQL

texts:
We are using two MYSQL servers in ring replication where master is also a slave of the other master. Both have slaves connected to each other.
We want to run pt-online-schema-change to add couple of columns but we are not sure if it may break the replication due to ring replication in place. Any advice would be great.


texts: Is there any way to break out of the string and inject SQL without using a single quote in oracle?

texts:
I'm testing an oracle based application and I've found the following code:

Query = "SELECT name FROM employees WHERE id='"+PKID+"';"

i.e. the query string contains quotes around the PKID value which is obtained straight from the URL.

Obviously, this is a classic SQL injection waiting to happen...except the application is behind CA SiteMinder which blocks any URL with a single quote (in any form) from being passed to the application.

Is there any way to break out of the string and inject SQL without using a single quote?

Edit: Sorry, I should have been clearer - I understand how it should be written, but I need to persuade people that it's an exploitable issue. At the moment because it's behind siteminder which blocks single quotes so it's going to be a low priority fix.


texts: Mysql And Galera cluster mariaDB , Private and Public IP Addresses multisite

texts:
I am just wondering has anyone come accross this before and the best method to go about doing it.

We have a customer that wants multi site replication over a galera cluster.

I am familiar with setting this up but not in the way they are asking for.

First of all the client has a dedicated L2 connection between the datacentres so I have set up the cluster with Private IP addresses going across the dedicated L2 link, the issue is if the L2 link goes down they loose the replication between sites, So I was thinking of natting the nodes private IP addresses to Public IPs.

My question is: Is there a way I can set the galera cluster so that if it cant reach the private IPs it will go out over the internet through the firewall and hit the public IPs?

Could I maybe put the private and public IPs of each site within this setting:

wsrep_cluster_address=gcomm://server1private_IP1,server1public_IP1,server2private_IP1,server2public_ip1 etc

Saying that server1 is local in my datacentre and server2 is in another datacentre.

Or would that write the data twice over the 2 links?

Thanks in advance for any help that is thrown my way.


texts: FastTrack Data Warehouse -E startup parameter

texts:
We are running a data warehouse based on the Fast Track architecture. One of the recommendations with Fast Track is to enable the -E parameter at SQL Server startup (increases the number of extents that are allocated for each file in a filegroup).

I've just discovered that our instance does not have this option enabled. If I enable it now, how can I ensure that existing database files are created with the additional extents?


texts: Oracle: Interval partitioning and dependant tables

texts:
I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to another 80M records between them. This database is basically a log of data so the data going in is written once and only read after that.

The admims want to partition the data per month because they want to quickly remove old data after two year and avoid having to rebuild all the indexes. So their intent to to remove old partitions.

After reading a lot, my idea was to do interval partitioning as per many examples I found on the net, however I then found that the other two tables cannot then employ partition by reference. Why oracle does not allow this, no-one here knows.

So my question is what, in your experience, would you do to partition the data? given it's is spread across the 3 tables. And is this the best technique for handing a rolling removal of old data like this?


texts:
I got a new Windows 10 machine and installed the latest version of SQLEXPRESS and then the latest version of SSMS (17.9.1).

When trying to connect to my Azure database, I'm getting the following error.

  Error connecting to 'mydb.database.windows.net'.

  ------------------------------ ADDITIONAL INFORMATION:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  A transport-level error has occurred when receiving results from the
  server. (provider: TCP Provider, error: 0 - An existing connection was
  forcibly closed by the remote host.) (Microsoft SQL Server, Error:
  10054)

  An existing connection was forcibly closed by the remote host

What I've tried so far:

Uninstall/Reinstall - nope. I did this twice
Turn off firewall - didn't work. Besides, I can connect to the database fine through Visual Studio 2017, so it isn't a firewall issue. I also validated with netstat that port was open after I telnet-ed to the server.
Different version - I tried downgrading to SSMS version 17.9, but I get the same error. I'm in the process of bringing up 18.0 (preview 5) just to see if something will work for me. I may update here whether that works for me or not.
Different database - I have two Azure servers, I can't access either. I can connect to my local SQLEXPRESS instance without any issues.

My Google-foo has failed me on this so far. What am I missing? My current theory is that the SQLEXPRESS installation is colliding with SSMS install, but I don't know how to know that for sure - it was just something I read that used to be a problem.

Update

18 Preview 5 didn't help me. Same error :(

Others that appear to have this same issue without a resolution:

https://www.reddit.com/r/SQLServer/comments/9vfos7/issue_with_ssms_cant_connect_to_specific_server/
https://stackoverflow.com/questions/52885575/ssms-connect-to-azure-failure

Update 2

Uninstalling SQLEXPRESS and using IISCrypto to add specific TLS entries into the registry didn't help either.

Update 3 (After the answer below)

I installed SSDT and everything broke again :(. However, I did find that I can connect a single query in SSMS with no problem, but I cannot connect to a Server in the Object Explorer. Go figure.

I'll update here if there is something else I discover.


texts:
CREATE TABLE `cities` (
  `id` int(11) NOT NULL,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cities`
--
ALTER TABLE `cities`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables

-- --------------------------------------------------------

--
-- Table structure for table `cities_extended`
--

CREATE TABLE `cities_extended` (
  `id` int(11) NOT NULL,
  `city` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `state_code` char(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip` int(5) UNSIGNED ZEROFILL NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `county` varchar(50) NOT NULL,
  `state_id` int(11) NOT NULL DEFAULT '0',
  `city_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `cities_extended`
--
ALTER TABLE `cities_extended`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `cities_extended`
--
ALTER TABLE `cities_extended`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

UPDATE
    cities_extended
SET
    cities_extended.`city_id` = cities.`id`
FROM
   cities_extended
INNER JOIN
    cities
ON
    cities_extended.`state_id` = cities.`state_id`
AND
    cities_extended.`city`= cities.`city`

Results in

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM
   cities_extended
JOIN
    cities
ON
    cities_extended.`state_id' at line 5

giving this error can you help me


texts:
I have a table with a last_update column, I want to add a partition based on the last_update column to remove old values.

My problem is that I have a unique key in the table which does not include the last_update column.

What are my options?

Should I change the way I work with this table and remove the unique index?

EDIT

CREATE TABLE `fileso` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `hostId` int(11) unsigned NOT NULL COMMENT 'code',
  `sha256` binary(32) NOT NULL COMMENT 'meir',
  `fileName` varchar(150) DEFAULT NULL COMMENT 'meir',
  `fullPath` varchar(350) DEFAULT NULL COMMENT 'meir',
  `datein` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `uniqueness` binary(32) NOT NULL DEFAULT '0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueness` (`uniqueness`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The unique key is an Hash representation of a couple of columns in the table(sha256, fullPath,hostId).


texts: Copying Oracle datafiles to a system clone

texts:
We have an Oracle 11G R2 on Windows 2008 R2.

One night, a windows update package caused our Oracle Instance to be down (no way to connect using sqlplus. we believe it's a network, NIC issue.).

We have restored the system from an 36 hours old system snapshot. We do not use archive mode. So every single parameter file in oracle stays same, but datafiles are old.

Question is, can we shut the db off and copy the newer datafiles to the restored system? Is this operation safe?

Thanks in advance.


texts: Issues setting up a publisher for transactional replication on a SQL Server with a custom instance

texts:
I'm trying to set up transactional replication of a fairly large database (Business Activity Monitor database from BizTalk Server 2013R2) (>500 GB).

I have an active/passive cluster which holds the main database, another server configured to act as distributor and an active/passive server cluster that will be subscriber. These are all different machines in the same LAN, and they're all running SQL Server 2014 Standard Edition.

I've set up the distributor and set up the publisher as, well, a publisher.

I then continued setting up the publisher with all articles and filters needed, but when trying to set it up and starting a snapshot I ran into the following error:

Cannot promote the transaction to a distributed transaction because there
is an active save point in this transaction.
Changed database context to 'BAMPrimaryImport'. (Microsoft SQL Server, Error: 3933)

I've tried looking for open transactions using DBCC OPENTRAN but it returned nothing.

What I do see however is that Linked Server is set up in which the server is linked back to itself and the instance name is not default but rather an alias.
I'm not sure why this is (I've not set the environment up myself back in the day but I've just inherited it) and I'm not sure this has anything to do with it.

Has anyone else seen these issues and/or have any ideas on how to get this to work?


texts: What could be the best DBMS to store airbnb data for price prediction

texts:
I have a school project in which I need to predict the airbnb listings prices in 5 different cities.
So I have about 400,000 records each with 19 columns.
All the examples on the internet that are implemented in python use data frames.. But the goal of my project is to learn how to handle big data in a DBMS.
I was thinking about using RDBMS but I'm not sure at all if it could be good or not.
Any help or hint regarding a good DBMS for my project would be highly appreciated.


texts: Mapping auto incremented primary keys of deeply nested objects in PostgreSQL when moving between databases

texts:
Situation
I have a Postgres database where most of the data belongs to a specific tenant. Most tables have an auto-incremented primary key, and the tables have a deeply nested web of foreign keys through each other's primary keys (the schema is generated by Entity Framework Core ORM).
This means each table has a similar structure:
ID: int PK
TenantID: int FK
Other fields

Where tenantId is the Primary Key of the Tenants table.
There are no foreign keys where the 2 records are for different tenants.
Deleting a tenant is as simple as delete from Tenants where ... as a lot of cascade deletes are set up.
Currently there is a second instance of the system being built, meaning that certain tenants are moved to the new system. I plan to perform the following for this:

Dump-restore the database from the old to the new system
Delete tenants from the new DB that are not intended to be moved yet
Profit

What do I want to do
The tenants left in the old system will have to be moved later. They are not ready to be moved, as the 2 system will work as Test/Production and depending on the tenants they are either just testing the system or using it in production.
For moving to production it would be imperative to move all their data to the new system whenever they are ready for it.
Problem
Initially I thought that getting a dump for a specific tenant (with deleting all other data from the dump) and running it on the new system would be easy however from now on both systems will live side-by-side so both systems will generate auto-incremented primary keys for the same tables but they will mean different records.
I would need a way to map all those primary keys to ones that are not taken in the new database, keep all foreign key in sync.
Questions
Is there a specific tool I can utilize?
Is there some simple SQL logic to precisely do that?
Do I need to roll my own software to do this (e.g. a service that connects to both databases, read everything in EF with including all relations, and save it to the other DB)? I fear that there are too many gotchas and it is time consuming.


texts: Can drbd be used for mongodb replication or active/passive setting?

texts:
From this guide we know DRBD can used for PostgreSQL Active/Passive setting:

https://www.howtoforge.com/how-to-set-up-an-active-passive-postgresql-cluster-with-pacemaker-corosync-and-drbd-centos-5.5-p4

It use /dev/drbd0 to mount PostgreSQL data path /var/lib/psql:

mount -t ext3 /dev/drbd0 /var/lib/pgsql

So is it possible to use this way to mount a MongoDB data path and where is that path?（Maybe this on CentOS 7.3: /var/lib/mongo）

If use this way, the multiple nodes will use the synced same file data but not master-slave replication. Maybe this is called master-master replication. Is it good?


texts: Move PostgreSQL database cluster to another server

texts: We have a PSQL database installation on a server, we are trying to dump the entire data (~950GB) to text files for further processing but we can't do it because the server is low on resources. Is it possible to move the PSQL cluster files /var/lib/pgsql to another server? Right now I am trying pg_dump remotely but I don't think it will complete successfully, either this or any other data dump methods. If moving the files work out it would be great.


texts: SQL Server - Inconsistent, hidden machine precision error when truncating result of LOG to integer

texts:
I noticed some strange rounding behavior in SQL Server (2008R2 at least) when casting log base 10 of 1000 to an integer. The answer is clearly the exact value 3, but there must be some decimals SQL server is hiding from the output:

SELECT LOG(1000, 10)--returns 3 with no visible decimals
SELECT CONVERT(INT, LOG(1000, 10))--returns 2

I reverse-engineered the minimum epsilon to get the correct value when added to the result of LOG before conversion as approximately:

SELECT CONVERT(INT, LOG(1000, 10)+0.0000000000000002220446049250313)
SELECT CONVERT(INT, LOG(1000, 10)+0.0000000000000002220446049250312)

It seems I could go on all day making this value more precise.

This only appears to be necessary for the case of 1000, as I get the proper integer values for 1, 10, 100, and 10000 and larger without using any epsilon term.

The error term appears to be the tail of the Taylor Series expanded out 16 terms (as per this page), but why is SQL Server suddenly behaving as expected (no hidden machine precision error) when I try this for the case of 10000 and larger?

Why is SQL Server inconsistently demonstrating a machine precision error?

Thanks.


texts: What would a schema look like for setting up tables for RPG character creation restrictions?

texts:
I'm trying to figure out how to layout tables for an RPG database where information such as character race, class, and deity have restrictions on what combination is permissible.
For example:
Race
-----------
id | name
1  | Human
2  | Elf
3  | Dwarf

Class
-----------
id | name
1  | Warrior
2  | Wizard
3  | Thief

Deity
-----------
id | name
1  | GodA
2  | GodB
3  | GodC

I want to limit certain combinations such as only humans and elves can be wizards, only humans and dwarves can be warriors, but all three can be thieves. But I also want to restrict the deity to certain race/class combinations. For example, humans can only worship GodA or GodB but NOT GodC; choosing to be a thief means you can't worship GodB so you end up only being able to choose GodA, etc.
What kind of schema can be used to map this information, is there a name for this type of relational data, and what would a query look like to retrieve the final mapping?


texts: Should an OFFLINE database be SET EMERGENCY before dropping?

texts:
As a rule before dropping a database I, SET OFFLINE, for 30 days before dropping it.  If there is anything that was missed and the database is still needed, I can SET ONLINE quickly for a full restore.

USE master
GO
ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

If you drop a database while it is offline, the files stay on the server forever unless you purposely go clean them up.  Assuming the you have taken whatever backups you want and are sure you want to completely drop the database on the server, you put it back online then drop it.

USE [master]
GO
ALTER DATABASE database_name
SET ONLINE

DROP DATABASE database_name
GO

The database comes fully back online then gets dropped.  Might it be a better practice to SET EMERGENCY before dropping?  Depending on many variables, in theory when you go online a missed OLTP connection could access the database between ONLINE and DROP.

Using SET EMERGENCY, grants "READ_ONLY to members of sysadmin and brings the database far enough online so that files on the server are deleted. At least with my limited testing on SQL2014

USE [master]
GO
ALTER DATABASE database_name
SET EMERGENCY

DROP DATABASE database_name
GO

If a missed OLTP (or anything) manages to connect to the database between ONLINE and DROP you would have deleted any record of it with the drop.

Might it be a better practice with an OFFLINE database to SET EMERGENCY before dropping?  If you are using T-SQL is it to quick for any possible connection to occur?

If you are using the Delete Option on SSMS, even best case between online and delete, is going to measurable in seconds. Should this always be avoided?


texts: Estimated versus actual query plan with function calls

texts:
I have this query on SQL server, a merge replication query:

SELECT DISTINCT
    b.tablenick,
    b.rowguid,
    c.generation,
    sys.fn_MSgeneration_downloadonly
    (
        c.generation,
        c.tablenick
    )
FROM #belong b
LEFT OUTER JOIN dbo.MSmerge_contents c ON
    c.tablenick = b.tablenick
    AND c.rowguid = b.rowguid;

The estimated query plan includes information about 3 queries:

The query above
The function call to fn_MSgeneration_downloadonly
The function call to fn_MSArticle_has_downloadonly_property

The actual query plan includes only this information:

The query above

Nothing about the functions. Why is the function information missing in the actual plan?

I tried these options:

SET STATISTICS PROFILE ON
SET STATISTICS XML ON

Which created an actual plan, but it was missing parts 2 and 3 same as when I used the actual query plan option in Management Studio.

If for instance I was to use Profiler to capture the information about the function calls what events would I select?

Didn't find an answer specifically related to the query plans, but I profiled SP:StmtStarting and SP:StmtCompleted and it showed the function calls.


texts: Procedure with Insert Data From One Table to Another Table

texts:
I have two tables Person with PersonId, Name,Address,Mobile and PersonLog with Id,Name,Address,Mobile,FK_PersonId. I am trying store the old data in PersonLog and Person to be updated.

This is my procedure, but it is only updating Person and not storing selected(edited) data  from Person into PersonLog :

CREATE PROCEDURE [dbo].[UpdateInsertPerson]
(
@PersonId int,
@PersonName nvarchar(40),
@Address nvarchar(60),
@Mobile nvarchar(15)
)

AS

BEGIN

INSERT INTO
dbo.PersonLog(PersonName, Address, Mobile, FK_PersonId)
SELECT
Person.PersonId, Person.PersonName, Person.Address, Person.Mobile
FROM
dbo.Person JOIN dbo.PersonLog ON PersonLog.FK_PersonId = Person.PersonId;

UPDATE
    dbo.Person
SET
    PersonName = @PersonName,
    Address = @Address,
    Mobile = @Mobile
WHERE
    PersonId = @PersonID;

END

Any help?


texts: Filter Data to different data files on SQL server

texts:
I'm sure this is possible but I have no idea how or where to find the information on how to do it.

Scenario:
I have a reporting database (on SQL2008R2) which is split up into multiple file groups and multiple files. We're reaching the data limit of the current SAN drive and have reclaimed space from another SAN to provide the space we need.

Problem:
The 'new' SAN is on a 1GB connection whereas the current SAN is a 10GB connection, the intention is to keep all current data on the current SAN and put all old data or data for inactive users onto the slower SAN as it will not be used for anything other than very specific reports.

Pseudo Solution 1

ALTER DATABASE Reporting
ADD FILE(
    NAME =DataFile2,
    FILENAME = E:\OldSan\Reporting\Data2.ndf,
    SIZE= 512000MB,
    MAXSIZE = 716800MB,
    FILEGROWTH = 2048MB,
    DATAQUERY ='SELECT ALL OLD/INACTIVEDATA'
)

Thoughts

I know the above is not possible strictly like that and it would need to be done on a table by table basis of the data to move across. but not sure where to start or how to do it.

Theoretically(in my head) this would be done with splitting the clustered index on a filter across the filegroups (and the files just deal with it), there's options on a table properties for storage for filegroup or partition scheme, however on a clustered index this seems to always be greyed out so I'm not sure if I'm just clutching at straws there.

NOTE: it is potentially possible to upgrade from SQL 2008R2 to 2012 if that is absolutely necessary(but not desired)

EDIT: The query for this would ideally be based on data in another table, so DataToOldSAN = where userid in (select id from users where active =0)

Thanks
Ste


texts: Oracle SQL for left outer join to rownum = 1 of another query?

texts:
Background

I have a query joining many tables together, the chief of which is a workorder table.

I also have a table called WOStatusHistory, which stores every status for every workorder.

A diagram of the left outer join between the two would be something like:

 +------------+           +----------------+
 |Workorder   |           |WOStatusHistory |
 |------------| LO JOIN   |----------------|
 |WONum       |+--------->|WONum           |
 |Location    |           |Status          |
 |Description |           |ChangeDate      |
 |...         |           +----------------+
 +------------+

Goal

I need to return the most recent date that the status was complete for a specific workorder (in human terms, "tell me the last time that this workorder was completed")

Process So Far

The SQL for this without returning the top record is easy enough:

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM        AS STATUSWONUM
                               , STATUS     AS STATUS
                               , CHANGEDATE AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        ORDER  BY CHANGEDATE DESC)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM )

;

The Issue

However, I'm having trouble figuring out how to take rownum = 1 at the appropriate time in order to return only the most recent date.

It seems my only options are (though I hope I'm wrong):

Place rownum = 1 inside the wostatushistory query. This seems like it would limit the results before they're even linked.
Place rownum = 1 outside of the join. This wouldn't serve the purpose, as it would give me only one record from my total returned set.

Thanks in advance for any help you can give! Happy to clarify anything.


texts: Deleting rows from Oracle table user_scheduler_job_run_details

texts:
Oracle Database 12c
Red Hat Linux 7

I want to delete the last 1000 records on the Oracle table user_scheduler_job_run_details because it is getting too large.
How best to delete rows from the Oracle table user_scheduler_job_run_details?
Are any precautions needed?
Is it simply a matter of doing a DELETE FROM ... DML statement?
Is there an existing job or procedure that handles this?


texts: How to check Oracle database connection encryption type

texts: How to check the Oracle database connection encryption type?(between Oracle database and other client applications)How do I assure between Oracle database and Toad the database connection is encrypted? (My Toad version is 9.5 and Oracle database version is 11g)


texts:
My Database has Full BackUp Plan on Every Morning on 01:00 Am. Today I came to work and Deleted all of the Records in a table by mistake in sql server management Studio by running this command

delete * from Table1 where 10=10

instead of

delete * from Table1 where Id=10

this happened around  13:00 pm. worktime starts from 8:00 am . is it possible to undo the deleted Records? if I want to recover it from the backup file, I loses the records entered between 8:00 and 13:00. what is the best solution for this problem?

I read this post Recover deleted records but it was not the same as mine. and I read it was a duplicate post. but again i couldn't find the the main post for this problem.


texts: With PostgreSQL getting ERROR: COPY format "CSV" not recognized

texts:
Whever I try to copy from

test=# \COPY table FROM 'file.csv' WITH ( FORMAT 'CSV', HEADER true );
ERROR:  COPY format "CSV" not recognized

What's the reason for this? I know I've used this before.


texts:
I need to find the ids of all rows in a table A without a matching row on table B. Following the answer on this question I'm using a left join like this:

select A.id from A left join B on B.id_A = A.id where B.id_A is null;

And it works perfectly well. However now I need to do the same query with an additional conditional for the matching, meaning I need to find the ids of all rows in a table A without a matching row on table B whose column c has the value x. If I try something like:

select A.id from A left join B on B.id_A = A.id where B.id_A is null and B.c = x;

It obviously gives me an empty result set.

So far, the only way I figured to do that is using a subquery for B with a "not exists" clause:

select A.id from A where not exists (select id from B where B.id_A = A.id and B.c = x);

Any ideas on how to do this with a join without using subqueries?


texts: When using expdp to create a snapshot of an oracle schema, how do I ensure that no post-export additions are retained upon using impdp to revert?

texts:
I want to create a snapshot of an Oracle schema that I can revert back to after testing. Impdp and expdp seem to be the best fit as far as how I'd like to accomplish it, but the problem is that impdp only overwrites the existing schema without getting rid of any data that is not included in the dumpfile.

I could "DROP USER megan CASCADE," but wouldn't that involve using SQL *Plus and quitting after the drop before executing impdp? Is that even possible?

I tried that, and it didn't work. I may be doing it incorrectly, as I have about two days of experience working with and researching databases, but I haven't been able to find a way to drop the user without using SQL *Plus.

Questions:

How can I clean my schema so that my import is an accurate snapshot?
Is using "DROP USER" a viable option?
Would that even accomplish what I'd like to do?
Are there other options?

Thanks!


texts: Add foreign key constraint for denormalized columns in detail tables?

texts:
When a detail table contains denormalized data, should denormalized columns be included in foreign key relationships between the master table and detail table?  Here's more details:

We have a master/detail pair of fact tables: an Invoice table with about 1M rows and an InvoiceDetail table with about 20M rows. To improve reporting performance for date-range queries we've partially denormalized by adding SaleDate to the InvoiceDetail and creating a covering index on SaleDate with the other columns INCLUDEd.

There's already a foreign key relationship between the InvoiceId column in both tables.

But SQL Server is unaware that the SaleDate in both tables is the same if the InvoiceId is the same. Should I help SQL Server to know about the relationship?  If so, how?

Finally, will adding denormalized columns to foreign keys improve cardinality estimates when joining the master/detail pages by telling SQL Server that cardinality shouldn't be reduced when filtering both tables by the same a date range?  If not, then what's the benefit of maintaining this foreign key relationship that includes the denormalized column?

We're running SQL Server 2014 an are soon upgrading to SQL 2017, if that matters to the answer.


texts: Oracle 12C, cannot drop NOT NULL constraint on a DEFAULT ON NULL column

texts:
In windows7 I Install oracle 12 C

Then I connect to database as sys user and create new user so:

CREATE USER C##USER
IDENTIFIED BY user_pass;

GRANT ALL PRIVILEGES TO C##USER;

Then I connect to database as C##USER and create table

CREATE TABLE autos (
    id INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY UNIQUE,
    OWNER_NAME NVARCHAR2(50)
);

and when I am trying insert rows into table, I got problems:

INSERT INTO autos
(owner_name)
VALUES
('Tommy');

On this query, oracle returns: ORA-30667: cannot drop NOT NULL constraint on a DEFAULT ON NULL column

When I connect as sys user, this error not occurred, this error happens only when I connect to database as new created user (in this case this is user: C##USER)

Can you tell me please what is wrong here?

P.S. This error returned both, in SQL Developer and in SQLPlus also.

Thanks


texts:
I have an array column containing information like

{a,b,c,a}
{a,b,a,b}
{b,a,a,b}

I want to run unnest on this array column, get the occurence of of each character.

a 2
b 1
c 1
a 2
b 2
b 2
a 2

I run the following query which works when considered for one row:

SELECT i, count(*) AS count
FROM   unnest(array::text[]) i
GROUP  BY i
ORDER  BY i;