Migration path for IDENTIFIED BY PASSWORD 'password_hash' when removed in MySQL 8 We have a solution where the management of users to a MySQL database is automated. Today we use MySQL 5.6 but we want to move to MySQL 8.

According to https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/mysql-nutshell.html

IDENTIFIED BY PASSWORD

was removed. Unfortunately, this is something that we make use of. We store the hash of the password outside the database, and then whenever a user needs to be created we can use it to directly create the account without actually knowing the password.

Why not create user immediately and remove the need for external storage?

We also make use of MySQL's host concept to limit access from only certain IP addresses. However, these may change. Use case:

A new user 'myUser' should be given access from 1.2.3.4 with password 'myPassword'
The hash for 'myPassword' is calculated and stored together with other metadata in an external storage
A MySQL user myUser@1.2.3.4 is created with the given hash
Later, user should also be given access from IP 5.6.7.8
No problem, a new MySQL user myUser@5.6.7.8 is created using same hash as before

The end result is a solution that is restrictive and user isn't aware of that technically two accounts exist. We also don't know the user's password.

As far as I can understand, this solution will not work going forward with MySQL 8 so I'm looking at possible migration paths. One possible solution would be if the following was possible:

Is it possible to create a new user in MySQL and set its password and authentication plugin to exactly the same as another user, without actually knowing the underlying password with MySQL 8? If yes, we could use that with some trickery to create any additional users by simply copying the information we have. We would only need to know the password while the first initial user is created, but whenever a new IP adress is given access we can perform a "copy" logic instead.

Any ideas? Thanks in advance!

We will need to perform additional checks, but a workaround we have found is to simply instead manually set "authentication_string" and "plugin" in "mysql.user" to the desired values, and then execute "flush privileges". That is, to keep using 'mysql_native_password'  and sha1(sha1(password)) that we know the hash is calculated as.

We then wouldn't be able to make use of the new default authentication plugin though. A quick check also says that caching_sha2_password (new default) doesn't have a stable authentication_string wrt password. Two users with the same password yielded different values so I'm assuming its implementation is more advanced in what input is used.


how to get monthly date in teradata I have a table called oth_mbr. I need to get 3 years of data month wise. current date minus 36 months and each month data betwwe from_dt and thru_dt needs to be fetched.
data shoulbe be in below formate
DATE , COLUMN A, COLUMN B
2017-08-01,FEP000,FPr
2017-09-01,FEP000,FPr
2017-10-01,FEP000,FPr
.
.
.
2020-08-01,FEP000,FPr

tried below query but this gives only one data
SEL distinct ADD_MONTHS(DATE - DATE MOD 100 + 1,- 36)  AS   DATE ,
COLUMN 1,COLUMN 2
OTH_MBR
WHERE
ADDD_MONTHS(DATE - DATE MOD 100 + 1,- 36)  BETWEEN FROM_DT AND THRU_DT

Result for above query is
2017-08-01,FEP000,FPr

Teradata supports proprietary syntax for time series expansion:
select begin(pd), column1, column2
from OTH_MBR
expand on period (from_dt, thru_dt) as pd
by anchor period month_begin
-- if you want to restrict the range of months
for period(ADD_MONTHS(current_date,- 36), current_date )

You can wrap it in a Derived Table/CTE for further processing:
with cte as
 (
   select begin(pd) as mon, column1, column2
   from OTH_MBR
   expand on period (from_dt, thru_dt) as pd
   by anchor period month_begin
   for period(ADD_MONTHS(current_date,- 36), current_date )
 )
select mon, count(*)
from cte
group by 1;


Who created that table in SQL Server? I was wondering if there was a way to see who created a table in SQL Server 2017.

The forensic information may still be available in the default trace if the table was created recently. The default trace writes to rollover files with a maximum of 5 files of 20MB each (100MB total) so the event may have rolled off, depending on system activity.

Below is an example T-SQL query. This is the same source used by the SSMS Schema Change History report (right-click on the server in SSMS Object Explorer and select Reports-->Standard Reports-->Schema Change History).

SELECT
     trace.TextData
    ,trace.DatabaseName
    ,trace.ObjectName
    ,te.name AS EventName
    ,tsv.subclass_name
    ,trace.EventClass
    ,trace.EventSubClass
    ,trace.StartTime
    ,trace.EndTime
    ,trace.NTDomainName
    ,trace.NTUserName
    ,trace.LoginName
    ,trace.HostName
    ,trace.ApplicationName
    ,trace.Spid
    --,*
FROM (SELECT REVERSE(STUFF(REVERSE(path), 1, CHARINDEX(N'\', REVERSE(path)), '')) + N'\Log.trc' AS path
    FROM sys.traces WHERE is_default = 1) AS default_trace_path
CROSS APPLY fn_trace_gettable(default_trace_path.path, DEFAULT) AS trace
JOIN sys.trace_events AS te ON
    trace.EventClass=te.trace_event_id
LEFT JOIN sys.trace_subclass_values AS tsv ON
    tsv.trace_event_id = EveNtClass
    AND tsv.subclass_value = trace.EventSubClass
WHERE
    te.name = 'Object:Created'
    AND trace.DatabaseName = N'YourDatabase'
    AND trace.ObjectName = N'YourTable'
    AND tsv.subclass_name = 'Commit'
ORDER BY trace.StartTime;


Two servers, different execution plans. Several orders of magnitude difference in run time Two servers, Development and Live. Live server is an Amazon RDS SQL Server Web instance. Both servers have identical schema and data. There is a good spatial index on the geometry column. On my development server the query executes in < 30 milliseconds.
On Live server the query takes > 20 minutes.

Examining the execution plans shows that they are drastically different. For one thing, on my development environment the query is parallelised whereas on the live server it is not.

I have rebuilt the indexes and regenerated the stats.
I cannot account for the massive discrepancy.
The servers' DOP are the same.
The Live server's CPU is getting hammered 100% during the execution of the query.

I'd appreciate any insights into the cause or how to best go about diagnosing the problem.

DECLARE @geoBoundary geometry;
SET @geoBoundary = geometry::STGeomFromText('POLYGON((407439.5 108792.25, 408022.5 108792.25, 408022.5 108575.75, 407439.5 108575.75, 407439.5 108792.25))', '0');

SELECT
     ogr_geometry.ToString() AS strGeometry
    ,ogr_geometry
FROM inspire as geo
WHERE geo.ogr_fid IN
(
    SELECT
        geo.ogr_fid
    FROM .inspire as geo
    WHERE
    (
        (@geoBoundary.STContains(geo.ogr_geometry) = 1)
    )
    UNION
    SELECT
        geo.ogr_fid
    FROM .inspire as geo
    WHERE
    (
        (@geoBoundary.STOverlaps(geo.ogr_geometry) = 1)
    )
)

The cost threshold for parallelism on both servers is 5 (the default).
The development instance has 4 physical cores, 8 logical; the live instance has 8 virtual cores.
max server memory is the same on both.
Development instance is SQL Server 12.0.4100.1; Live is SQL Server Web 12.0.2100.60
There is a huge disparity between actual and expected rows. However, this remained after rebuilding the statistics.
I cleared the plan cache. It keeps compiling the same plan.
Execution plans can be downloaded from here.

Comparing details from the two execution plans provided:

Slow plan was compiled on build 11.0.2100.60 - SQL Server 2012 RTM
Fast plan was compiled on build 12.0.4100.1 - SQL Server 2014 SP1
Slow plan uses the version 70 cardinality estimator
Fast plan uses the version 120 cardinality estimator
Slow plan has estimated available DOP of 2 (half the visible CPUs)
Fast plan has estimated available DOP of 4 (half the visible CPUs)
Estimated memory grant available is also different between the two
Parameter values used during compilation are identical

With so many fundamental software and hardware differences, it is not really surprising the plans are different.

Ideally, development and test environments should match production as closely as possible. They should certainly be on the same version of SQL Server (and note the 2012 installation there is still at RTM, two full Service Packs and seven further Cumulative Updates behind current.

Without a statistics-only copy of the schema involved in the query, it is hard to know what to suggest to improve the 2012 query plan as it stands. If you are comfortable using hints, you might try adding:

OPTION (LOOP JOIN, HASH JOIN, CONCAT UNION, QUERYTRACEON 8649);

at the end of the query to encourage the optimizer in the general direction of the better plan. The last part of that hint collection (setting trace flag 8649 for the query) is undocumented and not supported for production.

Those hints may or may not be successful, since the underlying issue is the inaccurate cardinality estimation provided by the 70 level cardinality estimator. Query rewrites or indexing changes might be successful as well, but it's really not practical to guess on that from here.

Related reading:

Forcing a Parallel Execution Plan
Different Plans on UAT and PROD
Multiple Plans for an "Identical" Query
Different Plans for "Identical" Servers


Restore of copy_only backup of encrypted database made without FORMAT but with INIT fails I have a task of making periodical copy of the database, twice a day, and I should not break any backup chain niether accumulate these temporary backups. So I tried to take a copy_only backup with init in order to overwrite the single backup file.
My database in encrypted by using TDE.
I use backup compression at the server level, "backup compression default" is 1 in my sys.configurations. I don't use explicit with compression option in my command.
Here my full @@version:

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)
Sep 23 2020 16:03:08    Copyright (C) 2019 Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0
 (Build 14393: ) (Hypervisor)

My problem is this error below:

Msg 3241, Level 16, State 40, Line 8 The media family on device
'W:\SQL_backup\copy_only\billing_prod2_test.bak' is incorrectly
formed. SQL Server cannot process this media family. Msg 3013, Level
16, State 1, Line 8 RESTORE DATABASE is terminating abnormally.

Here is the full code I executed:
backup database billing_prod2
to disk = 'W:\SQL_backup\copy_only\billing_prod2_test.bak'
with init, copy_only--, format;
go

restore database billing_1
from disk = 'W:\SQL_backup\copy_only\billing_prod2_test.bak'
with move 'billing_prod2' to 'E:\SQL_data\billing_1.mdf',
move 'billing_prod2_log' to 'E:\SQL_data\billing_1_log.ldf',
replace

The first time it executes without errors, the size of full backup is about 35Mb and it restores perfectly.
The second time the backup executes without error but strangely has a size of 260Mb. This is the size I see on the disk and the same size is written in the output of restore headeronly that still executes without errors. But when I do restore database the error mentioned above appears.
I tried to execute the same code using another db and another disk to save the backup but the result is always the same: the first time it works, the second time the file is overwritten but has different size that is not just a double size, it's almost 10 times of initial size. restore headeronly never fails and always reports only one file within the backup file but the size is multipticated and restore database fails.

Only when I added format option the size of backup became normal and restore succeeded.

My question is: why should I use format with my backup in this case to be able to restore?

This is indeed a bug in SQL Server. We (@sepupic and I) had some discussions offline and managed to narrow down the circumstances: The error occurs when:

You use TDE
The backup is compressed
You specify COPY_ONLY
You specify INIT
The backup file exists

Repro:
--The database has TDE
--Backupfile doesn't exist at this stage

BACKUP DATABASE db1Encrypted
TO DISK = 'R:\db1Encrypted.bak'
WITH INIT, COPY_ONLY, COMPRESSION, CHECKSUM

--backup file 660 kb

RESTORE VERIFYONLY FROM DISK = 'R:\db1Encrypted.bak' WITH CHECKSUM
--OK

--Backupfile now exists, below causes unreadable backup

BACKUP DATABASE db1Encrypted
TO DISK = 'R:\db1Encrypted.bak'
WITH INIT, COPY_ONLY, COMPRESSION, CHECKSUM

--backup file 3890 kb

RESTORE VERIFYONLY FROM DISK = 'R:\db1Encrypted.bak' WITH CHECKSUM
--Fails "The media family on device 'R:\db1Encrypted.bak' is incorrectly formed"


Does the function inside the Select clause evaluate the passed in columns before the where clause? I have a simple select statement that uses a (not so) simple function. I'll try to simplify and demonstrate to the best of my abilities for purposes of this question.

            select submission_identifier,
                   submitted_by_person_id,
                   submission_date,
                   form_version_date_time,
                   form_id,
                   func_get_open_days_2(
                     submitted_by_person_id,
                     submission_date,
                     form_version_date_time,
                     form_id)` open_days
            from form_submission
            where submission_identifier = 2070;

submission_identifier is the PK for the form_submission table, so naturally, this query returns one result.

As you can see, the function takes 4 parameters. I'm assuming the values in those parameters are the results of the query AFTER the where clause has been evaluated. Here is a snippet of the function.

create or replace function        func_get_Open_Days_2
   ( SUBMITTED_BY_PERSON_ID in number,
  SUBMISSION_DATE in date,
  FORM_VERSION_DATE_TIME in timestamp,
  FORM_ID in number
   )
RETURN number

IS
   n1 number:=0;
   n2 number:=0;

   cursor records is
      SELECT
        SUBMITTED_BY_PERSON_ID,
        SUBMISSION_DATE,
        FORM_ID,
        FORM_VERSION_DATE_TIME,
        AUDIT_DATE_TIME,
        STATUS_DATE,
        SUBMISSION_STATUS_CODE,
        CREATE_USER_ID
      from form_state_audit_trail t
      where
        t.submitted_by_person_id = SUBMITTED_BY_PERSON_ID and
        t.form_id = FORM_ID and
        t.submission_date = SUBMISSION_DATE and
        t.form_version_date_time = FORM_VERSION_DATE_TIME and
        t.submission_status_code NOT IN (3469, 3462);
        order by t.status_date asc;

   rec records%ROWTYPE;

BEGIN

dbms_output.put_line('submitted by person id ='||submitted_by_person_id);
dbms_output.put_line('form id ='||form_id);
dbms_output.put_line('submission date ='||submission_date);
dbms_output.put_line('form_version_date_time ='||form_version_date_time);

  -- go through all the durations in audit_trail table
  FOR rec in records
  LOOP

I have tried hard-coding the return values and putting them in this cursor select query. I receive 1 result.

This suggests to me that the "records" array or list should only be 1 result long. However, I am getting numerous results as it stands now. "records" are being returned which suggest the where clause is not being evaluated using the 4 values/results I'd expect I'm not sure what I'm doing wrong. The the "dbms_output.put_line" are the BEGIN but before the LOOP confirm the expected values are reaching the function.

The references in the function's WHERE clause are comparing with the identically named columns in its SELECT clause, rather than the argument list. Try changing it as follows (note the addition of ARG_ to the argument names and the predicates in the WHERE clause):

create or replace function        func_get_Open_Days_2
   ( ARG_SUBMITTED_BY_PERSON_ID in number,
  ARG_SUBMISSION_DATE in date,
  ARG_FORM_VERSION_DATE_TIME in timestamp,
  ARG_FORM_ID in number
   )
RETURN number

IS
   n1 number:=0;
   n2 number:=0;

   cursor records is
      SELECT
        SUBMITTED_BY_PERSON_ID,
        SUBMISSION_DATE,
        FORM_ID,
        FORM_VERSION_DATE_TIME,
        AUDIT_DATE_TIME,
        STATUS_DATE,
        SUBMISSION_STATUS_CODE,
        CREATE_USER_ID
      from form_state_audit_trail t
      where
        t.submitted_by_person_id = ARG_SUBMITTED_BY_PERSON_ID and
        t.form_id = ARG_FORM_ID and
        t.submission_date = ARG_SUBMISSION_DATE and
        t.form_version_date_time = ARG_FORM_VERSION_DATE_TIME and
        t.submission_status_code NOT IN (3469, 3462);
      order by t.status_date asc;


Proper way to map IDs: By adding additonal columns for each external column or by inserting new rows I am working on a new database structure in which we get external IDS from a few different sources and map them against our internal ID in one table.

Method 1

In the first version the table structure is shown below by the following "create database query":

create table external_id_mapping(
id  int not null auto_increment,
internal_id int,
external_id varchar(150),
external_source_name varchar(60),
varchar(70),
primary key(id))

In which case we would be able to look our own internal_ID dependant on the external through these types of queries:

select internal_id
from external_id_mapping
where external_id = 4214
and external_source_name = 'source_name_x'

Each new source would have a new row with an external_id and a source_name. External_id cannot be integer as it needs to cover every possible variation of external_ids we are using, some of which are integers others which are strings.

Method 2

In the other method the database structure would look like this:

create table external_id_mapping(
internal_id int,
external_id_x int,
external_id_z int,
external_id_y varchar(150),
primary key(internal_id))

Thus query would look like this:

select internal_id
from external_id_mapping
where external_id_x = 4214

Each internal_id represents its own row and gets matched for different columns against different external_ids. If we in the future wants to use additional external sources we would create new columns.

In terms of inserting new data in the table, I highly prefer the latter method sicne I can use "replace into" and overwrite the existing primary key ID.

Whereas in the first method, the ID is auto-incremented and it needs to be looked up before we know whether it needs to be inserted or updated.

However, which method is generally considered best practice?

INDEX(external_source, external_id) in that order.  This would facilitate looking up the external id to find the internal one.  Now, assuming the main use of this table is to go from "external" to "internal", then I would do

PRIMARY KEY (external_source, external_id)
INDEX(...)   -- for either `internal_id` or `id`, whichever you keep.

You have two internal ids -- internal_id and id.  Why both?  id could be AUTO_INCREMENT to take care of inventing new numbers.  internal_id would need some mechanism for inventing new numbers.

The external_* may as well be VARCHAR, since you can't trust them to stick to numbers.

I would not do the extra 'normalization' even if you have a million rows in this mapping table.  I might do it for a billion rows.  (I claim there is such a thing as "over-normalization".)


Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite? If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In this case, I am inserting all data at once, and there will never be any records that are deleted.

What if the scenario is slightly different: the table is WITHOUT ROWID, and the PRIMARY KEY is a clustered index on several columns -- does that change the wisdom here?

The documentation says:

  Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.

Here, "significantly" means that the new data changes the characteristics so much that the index is no longer as useful, or that it is much more useful than previously.

However, for a primary key, the selectivity usually does not change, so it should not be necessary to run ANALYZE after larger inserts.

(With or WITHOUT ROWID does not make any difference for this.)


Is it possible to convert mysql binlog from statement format to row format? The mysql server online is of version 4.1, which doesn't support row-based binary log. Nevertheless, I need the row-based binlog. Can I use the binlog generated by old mysql and import it into another mysql of higher version that supports row-base binlog to get the row-based binlog?

You cannot convert a binlog to another version, but you import its SQL as long as have mysqlbinlog for version 4.1.

Simply do the following:

Run MySQL 4.1 version of mysqlbinlog against the MySQL 4.1 binlog, saving output to stmts.sql
Install MySQL 5.x with these in my.cnf

log-bin=mysql-bin
binlog-format=ROW

Login to MySQL 5.1 and run source stmts.sql
Run SHOW BINARY LOGS;

When done, the binlogs you see should be row-based.

Give it a Try !!!

Why can't you just convert ? The BINLOG Magic Number gets displaced with later versions.

I wrote about before

Nov 30, 2012 : How can you monitor if MySQL binlog files get corrupted?
Feb 04, 2011 : MySQL master binlog corruption

Every time you restart mysql, the next empty binlog is created. Each version of MySQL has a different size:

MySQL 5.6 : 120
MySQL 5.5 : 107
MySQL 5.1 : 106
MySQL 5.0 : 98

In light of this, generating binlogs from scratch is the best way to go.

UPDATE : Thanks to Michael's Comment, you have to preload the data the binlog came from into the MySQL 5.1 Instance.


How to distinguish between system and user defined roles in a single database? Is there a way to distinguish between a system role/user and a user defined role/user? As system role/user I understand a role/user which is installed/assigned implicitly at the time the database has been created. As user defined role/user I understand a role/user created "manually" afterwards.

The only way I have found is by looking at the result of

select * from sys.database_principals order by create_date, is_fixed_role desc

or by comparing the result with a freshly created database. Is there another way?

The column 'is_fixed_role' on sys.database_principals is your answer, check the MS document:

  If 1, this row represents an entry for one of the fixed database
  roles: db_owner, db_accessadmin, db_datareader, db_datawriter,
  db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader,
  db_denydatawriter.

Just use

SELECT * FROM sys.database_principals WHERE [type]='R' and [is_fixed_role]=1


Performing wildcard search using the json_contains function in mysql 5.7 and above I have some nested json data that i get using the json_object() function. Now I need to to check whether a certain value exists for a particular key which I am using as follows in my query:-

select json_object(..) from tblxyz
  where json_contains(columnname, '{"MyName" : "FirstName LastName"}')

The problem with this approach is that I require the entire string "FirstName LastName" to find this row in the actual result.
Now I need to perform a wild card search. That is:-

select json_object(..) from tblxyz
  where json_contains(columnname, '{"MyName" : "FirstName"}')

should also return this entry to me in the final result. Can someone please suggest any workaround?

You can use JSON_SEARCH with wildcards:

SET @j = '{"MyName": "FirstName LastName"}';
SELECT JSON_SEARCH(@j, 'all', 'FirstName%', NULL, '$.MyName');

Will return the path: "$.MyName"

If you want to filter by this, use an alias and check it in a HAVING clause:

SELECT JSON_SEARCH(`json_col`, 'all', 'FirstName%', NULL, '$.MyName') as `search`
FROM `users`
HAVING `search` IS NOT NULL;

or.. in a WHERE:

SELECT *
FROM `users`
WHERE JSON_SEARCH(`json_col`, 'all', 'FirstName%', NULL, '$.MyName') IS NOT NULL;

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html


Do Sequences Have the Same Replication Range Limits As Identities? With merge replication, if the subscriber runs out of identities for a range when inserts occur into it, the subscriber will throw an error on the insert into the table, because it is the publication must grant more identities - and the insert is happening on the subscriber.  A better practice would be to make the publisher be the receiver of inserts only, but in some environments or situations, inserts can occur to both the publisher and subscriber, thus many people extend the range.

I am curious how sequences compare to identities here, as I can't find any indication that sequences are limited by merge replication setups like identity fields are.

I believe Aaron Bertrand or another MVP has been really big on sequences and I've been trying to push my organization to use them over identities, but it's a long journey.

With merge replication, identity ranges must be allocated to the publisher and each subscriber. Identity range management can be either manual or automatic.

As you already have found out, when a range is exhausted, a new range must be allocated. With automatic range management, the merge agent takes care of allocating a new range, with manual range management you decide what should happen.

With sequences, you will still have the same problem, with the difference that there is no such thing as automatic sequence range management, so your only option is manual range allocation. (FWIW, identities are implemented with sequences in SQL Server 2012 and above).

My suggestion is to avoid identities and sequences altogether: they do more harm than good. Did you know that in order to let identity range allocation happen at the publisher, the users that insert data in the database need to be members of the db_owner role? Yeah, that's awful.

With merge replication you need a uniqueidentifier ROWGUIDCOL anyway, so why don't you use that column as your surrogate key instead? If your key is really a surrogate, nobody should be able to see it or use it and it should be completely transparent to the application.


MySQL not using PRIMARY KEY to sort by default I have a large MySQL table on 5.6.35-81.0 Percona Server with gaps in the PRIMARY key. The table has around 53 Million records and the auto-increment key is now at around 370 million. The table is a list of phone numbers and users add/delete the numbers very frequently hence the PRIMARY key which is BIGINT has gaps inside.

The problem I am facing now is that when I do a simple SELECT * FROM table, MySQL does not used the PRIMARY key to sort the results by default. It uses the phone number field from the table to sort the data and because of this the data is SELECTed as sequential instead of the way it's saved in the DB.

Offcourse SELECT with ORDER BY id takes a very long time. The other solution is to copy all the data to another table but that would require application downtime. How can I fix this or to avoid such in the future?

CREATE TABLE `contacts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `phonebook_id` int(11) NOT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `contact` varchar(45) NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `admin` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `call_status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `active_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `test_call` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `dnc_contact` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `v_node_name` varchar(45) DEFAULT NULL,
  `timezone` varchar(4) DEFAULT NULL,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_date` timestamp NULL DEFAULT NULL,
  `contact_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phonebook_id` (`phonebook_id`,`contact`),
  KEY `idx_001` (`active_call`,`campaign_id`,`test_call`),
  KEY `idx_002` (`phonebook_id`,`call_status`,`test_call`,`campaign_id`,`active_call`,`dnc_contact`),
  KEY `idx_003` (`phonebook_id`,`test_call`,`call_status`,`dnc_contact`),
  KEY `FK_campaign_id_idx` (`campaign_id`),
  KEY `idx_testcall` (`test_call`),
  KEY `idx_004` (`campaign_id`,`call_status`,`test_call`),
  KEY `idx_active_call` (`active_call`)
) ENGINE=InnoDB AUTO_INCREMENT=2369201 DEFAULT CHARSET=latin1;

MySQL like any other RDBMS do not sort results by default. You have to add ORDER BY clause to get an ordered result. Otherwise the order rows are returned is undefined.

In some degenerate cases you have a chance to get an ordered result without ORDER BY - e.g. when you have bulk-inserted preordered rows into the newly created table, and if you do not DELETE or UPDATE any row before SELECT. But when the table is heavily DELETEd or UPDATEd, mysql performs so-called "index tree rebalancing". Traversing of the rebalanced index tree returns rows in the unpredictable order. Therefore you should never rely on assumption that query without ORDER BY returns an ordered result.


Error after removing sql server 2008 R2 Express edition and instaling Entreprise edition: Unable to connect to MyHost\MSSQLSERVER So I'm having this issue after installing MS SQL Server 2008 R2 Enterprise edition, I'm getting this message error:

Unable to connect to WIN7 \ MSSQLSERVER

A network-related error or instance-specific occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces , error : 25 - Connection string is not valid ) (Microsoft SQL Server, Error: 87)

NB: Before installing this version i did remove an existing MSSQL 2008 R2 EXPRESS, but after launching "SQL Server Management Studio" i strangely got for the first time the following instance of the express edition version: WIN7 \ SQLEXPRESS

My configs are: Windows 7 Enterprise Edition SQL SERVER 2008 R2 Enterprise

the SQL connection session: - DB Engine - WIN7 \ MSSQLSERVER - Windows Authentication

Please HELP !!

For a default instance (MSSQLSERVER), you only specify the computer name when connecting; the instance name (which is actually a ficticious placeholder) is implied.

Also, if you're connecting from another machine now, you may need to configure the Windows Firewall to allow connections through. The error message you get will be the same as the one you're seeing now. See my video here for more on that.


MySQL optimization - more tables vs more columns/rows Im new to SQL and looking to build a database. As an example to what I'm trying to do is have a database with about 5000 stock tickers. Each ticker will have about 35 data-points, corresponding to specific dates. All data will be INT or a small string.
I'm wondering if it's better to have each ticker represent a table, with all its data-points inside the table (about 15 columns and 20 rows), or should I put this all in one table, with the index being the ticker symbol, then having a bunch of columns.
It seems easier to make a new table for each ticker, as I can put the column titles as dates, and row titles as what the data represents, but not sure if this is optimized or would cause problems with multiple people searching for different tables.
Thanks!
EDIT: Here is an example of the table I'm looking to create, is this possible to have 2 rows indexes?

One table.  Period.  Full-stop.
A general rule is not to have multiple "identical" tables.  It is a maintenance problem, and it is likely to be slower.  (The common exception is SaaS, such as WordPress, where they simply live with lots of data bases with the 'same' 20 tables.)
"35 datapoints ... dates"  -- I'm confused.  Either you have information about a stock or you have daily prices going back years.  Are you saving only 5 weeks of data?  Or what do you mean?
5K rows in one table is "tiny" as datasets go.
Does this also have stock prices for each day?  With this, you get to millions or maybe billions of rows of about 6 columns.  Again, I strongly recommend a single table for all prices.  (Plus a table for all the tickers -- again 5K rows of 30 columns.)
I have dealt with such datasets, so ask away.  Or look around; I have answered questions like this in this forum and also in stackoverflow.com
The prices table would have something like
stock_id MEDIUMINT UNSIGNED -- 2 bytes
dy DATE  -- 3 bytes
open, high, low, close   -- see below

The prices get tricky.
FLOAT -- ~7 significant digits, 4 bytes; maybe good enough, might have roundoff
DECIMAL -- but how many decimal places; potentially bulky
INT -- not good enough
DOUBLE -- 8 bytes; overkill

Indexing will be important, but first, figure out what the inserts and selects are like.  These may be optimal:
PRIMARY KEY(stock_id, dy)
INDEX(dy, stock_id)

For one stock_id, there will be many rows.  Do not use 'many' columns for an "array"; that is very clumsy.  Perhaps your dates are "quarterly", as in company reports, not "daily" as in stocks?
5000 companies with 8 years of quarterly reports, at one row each == 160K rows in the single table.  That's still a "small" table.
Plus another table with 5000 rows, one per company, with name, address, etc.  That is, attributes that don't change.
Yes, income, balance, cashflow, etc should be separate columns in the table.  Assuming those values come from a quarterly report, then they would be in the 160K-row table I discussed above.
A table with a few dozen columns and a few million rows is no problem (in general).  If you get to 100 columns or a billion rows, you may encounter non-trivial problems.


Mongodb find() operation sometimes extremely slow I have a collection in mongodb (latest 3.0.4, not wiredtiger) with about 8 million documents. For the most part it's very fast but sometimes all reads to the database start stacking up and the culprit is the following query:

> db.currentOp();
{
    "inprog" : [
        {
            "desc" : "conn619",
            "threadId" : "0x858e8a0",
            "connectionId" : 619,
            "opid" : 4190651,
            "active" : true,
            "secs_running" : 171,
            "microsecs_running" : NumberLong(171584220),
            "op" : "query",
            "ns" : "default.products",
            "query" : {
                "$query" : {
                    "ps" : {
                        "$gt" : 0
                    },
                    "price" : {
                        "$type" : 1
                    },
                    "ships_to_australia" : true,
                    "category_memberships.node" : "2344577"
                },
                "orderby" : {
                    "salesRate" : -1
                }
            },
            "planSummary" : "IXSCAN { salesRate: -1.0 }",
            "client" : "142.0.36.114:29880",
            "numYields" : 135044,
            "locks" : {

            },
            "waitingForLock" : false,
            "lockStats" : {
                "Global" : {
                    "acquireCount" : {
                        "r" : NumberLong(270088)
                    }
                },
                "MMAPV1Journal" : {
                    "acquireCount" : {
                        "r" : NumberLong(154166)
                    },
                    "acquireWaitCount" : {
                        "r" : NumberLong(26)
                    },
                    "timeAcquiringMicros" : {
                        "r" : NumberLong(101532)
                    }
                },
                "Database" : {
                    "acquireCount" : {
                        "r" : NumberLong(135044)
                    }
                },
                "Collection" : {
                    "acquireCount" : {
                        "R" : NumberLong(135044)
                    },
                    "acquireWaitCount" : {
                        "R" : NumberLong(19122)
                    },
                    "timeAcquiringMicros" : {
                        "R" : NumberLong(14462598)
                    }
                }
            }
        }
    ]
}

The strange thing is the identical query is fast most of the time just sometimes it's incredibly slow like this and seems to lock any further read operations to the database completely which I thought shouldn't happen in mongo.

The server is dual CPU with 48GB of ram so the issue shouldn't be caused by hardware.

You are not using the correct index. The current index will bring the results sorted but it have to perform a full collection scan. You need an index like:

{"category_memberships.node":1,"ships_to_australia":1,"ps":1,"salesRate":-1}

This would limit the search and sorts at the same time.

The query execution variation has to do with the hotset and collection locking.


InnoDB Read and Write speed constantly spikes I am not a dedicated mysql admin but my friend and I have been working hard to fix the badly configured mysql files. We have reduced our number of pools to 2 from 20 and increased pool size to 90GB for a 124GB ram server. I've also enabled file_per_table. What is bugging me is the constant spikes in disk read/write speeds. Picture below is while the server is under decent load (file parsing into db) and it has been up with the updated config for about two days.

my.cnf file

max_connections = 1000
max_connect_errors = 10
max_allowed_packet = 1G
binlog_cache_size = 1M
max_heap_table_size = 3072M
table_open_cache = 20000
table_definition_cache = 20000
read_buffer_size = 64M
read_rnd_buffer_size = 8M
join_buffer_size = 64M
sort_buffer_size = 64M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_size=0
query_cache_limit = 128K
ft_min_word_len = 4
memlock
thread_stack = 256K
transaction_isolation = READ-COMMITTED
tmp_table_size = 3G
tmpdir = /mysql-tmp
group_concat_max_len = 33554432
ignore-db-dir=lost+found
server-id = 1
secure-file-priv = "/tmp/"

# *** INNODB Specific options ***
innodb_buffer_pool_size = 92160M
innodb_buffer_pool_instances=2
innodb_data_file_path=ibdata1:12M:autoextend
innodb_file_per_table=ON
innodb_data_home_dir=
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_log_buffer_size = 8M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_io_capacity = 30000
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_adaptive_hash_index=ON
innodb_large_prefix
innodb_autoinc_lock_mode = 0

numactl --hardware

available: 2 nodes (0-1)
node 0 cpus: 0 2 4 6 8 10 12 14 16 18 20 22
node 0 size: 65490 MB
node 0 free: 4880 MB
node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23
node 1 size: 65536 MB
node 1 free: 9789 MB
node distances:
node   0   1
  0:  10  20
  1:  20  10

Harddisk info smartctl -a /dev/sda -d sat+megaraid,00

=== START OF INFORMATION SECTION ===
Model Family:     Seagate Constellation ES (SATA 6Gb/s)
Device Model:     ST500NM0011
User Capacity:    500,107,862,016 bytes [500 GB]
Sector Size:      512 bytes logical/physical
Device is:        In smartctl database [for details use: -P show]
ATA Version is:   8
ATA Standard is:  ATA-8-ACS revision 4
Local Time is:    Mon Jun 19 10:45:43 2017 CDT
SMART support is: Available - device has SMART capability.
SMART support is: Enabled

We are altering an existing config file and I do not want to touch stuff that I do not know about. I am not a mysql admin. If you see a setting that makes no sense to you, please let me know. In addition to reducing the pool count to 2 from 20 and increasing the pool size to 90gb from 20gb, I've also set read/write thread counts to 16 each. I did not get a chance to restart the server after adding the thread counts (clients are on it all the time) but I've done the same number for my other servers and they all felt much better.

I don't know ... we are all just doing try and see method here but this inconsistent read/write speeds are making me think something is not right and I am not sure where to look.

Update 1

I've written a script to go through the database and call OPTIMIZE TABLE on every table. I do not think that was done before, ever... After I've done that, pool usage value jumped to %95+ on all servers but the efficiency value decreased.

Here is a gist of more information

Thank you for your time!

max_heap_table_size = 3072M     --> 1G
tmp_table_size = 3G             --> 1G
innodb_buffer_pool_instances=2  -->   16

Other details may be in the STATUS.  For further analysis, please provide

SHOW VARIABLES;
SHOW GLOBAL STATUS;


MySQL Workbench - Creating a one-to-one relationship using existing columns? How do I create a one-to-one relationship using existing columns in MySQL Workbench?

I have an EER diagram tab open and I am selecting from the toolbar on the left hand side.

Unfortunately it seems that I can create a one-to-many or many-to-one relationship but not a one-to-one.

Right click on the relationship line
Select 'Edit Relationship'
Click on the Foreign Key tab in the Relationship Tab
The Cardinality of the relationship can be changed with the two radio buttons


is there a way to run the same job multiple times without scheduling it? I have to run a process, and it says:

    --first see what is the situation with the job
    EXEC msdb.dbo.sp_help_job @job_name=N'Create new gift vouchers'

    EXEC msdb.dbo.sp_start_job N'Create new gift vouchers' ;
-- RUN THIS JOB FOR AS MANY TIMES AS THERE ARE INSERTS ABOVE --> 2 IN THIS CASE
    GO

this time I need to run the same job (that executes a SSIS package and takes about 8 min on average to run) for 15 times!!!

is there a way to put a script together that will run this job 15 times?

I've been using the following stored procedure (I can't even remember where I got the code from) for a long time.  It allows you to start a job and waits for the job to complete before returning control to the user.  You could stack 15 occurrences of this call in a tsql script

CREATE procedure [dbo].[StartAgentJobAndWait](@job nvarchar(128), @maxwaitmins int = 5) --, @result int output)
as begin

set NOCOUNT ON;
set XACT_ABORT ON;

    BEGIN TRY

    declare @running as int
    declare @seccount as int
    declare @maxseccount as int
    declare @start_job as bigint
    declare @run_status as int

    set @start_job = cast(convert(varchar, getdate(), 112) as bigint) * 1000000 + datepart(hour, getdate()) * 10000 + datepart(minute, getdate()) * 100 + datepart(second, getdate())

    set @maxseccount = 60*@maxwaitmins
    set @seccount = 0
    set @running = 0

    declare @job_owner sysname
    declare @job_id UNIQUEIDENTIFIER

    set @job_owner = SUSER_SNAME()

    -- get job id
    select @job_id=job_id
    from msdb.dbo.sysjobs sj
    where sj.name=@job

    -- invalid job name then exit with an error
    if @job_id is null
        RAISERROR (N'Unknown job: %s.', 16, 1, @job)

    -- output from stored procedure xp_sqlagent_enum_jobs is captured in the following table
    declare @xp_results TABLE ( job_id                UNIQUEIDENTIFIER NOT NULL,
                                last_run_date         INT              NOT NULL,
                                last_run_time         INT              NOT NULL,
                                next_run_date         INT              NOT NULL,
                                next_run_time         INT              NOT NULL,
                                next_run_schedule_id  INT              NOT NULL,
                                requested_to_run      INT              NOT NULL, -- BOOL
                                request_source        INT              NOT NULL,
                                request_source_id     sysname          COLLATE database_default NULL,
                                running               INT              NOT NULL, -- BOOL
                                current_step          INT              NOT NULL,
                                current_retry_attempt INT              NOT NULL,
                                job_state             INT              NOT NULL)

    -- start the job
    declare @r as int
    exec @r = msdb..sp_start_job @job

    -- quit if unable to start
    if @r<>0
        RAISERROR (N'Could not start job: %s.', 16, 2, @job)

    -- start with an initial delay to allow the job to appear in the job list (maybe I am missing something ?)
    WAITFOR DELAY '0:0:01';
    set @seccount = 1

    -- check job run state
    insert into @xp_results
    execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

    set @running= (SELECT top 1 running from @xp_results)

    while @running<>0 and @seccount < @maxseccount
    begin
        WAITFOR DELAY '0:0:01';
        set @seccount = @seccount + 1

        delete from @xp_results

        insert into @xp_results
        execute master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

        set @running= (SELECT top 1 running from @xp_results)
    end

    -- result: not ok (=1) if still running

    if @running <> 0 begin
        -- still running
        return 0
    end
    else begin

        -- did it finish ok ?
        set @run_status = 0

        select @run_status=run_status
        from msdb.dbo.sysjobhistory
        where job_id=@job_id
          and cast(run_date as bigint) * 1000000 + run_time >= @start_job

        if @run_status=1
            return 1  --finished ok
        else  --error
            RAISERROR (N'job %s did not finish successfully.', 16, 2, @job)

    end

    END TRY
    BEGIN CATCH

    DECLARE
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    SELECT @ErrorMessage =
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
            'Message: '+ ERROR_MESSAGE();

    RAISERROR
        (
        @ErrorMessage,
        @ErrorSeverity,
        1,
        @ErrorNumber,    -- original error number.
        @ErrorSeverity,  -- original error severity.
        @ErrorState,     -- original error state.
        @ErrorProcedure, -- original error procedure name.
        @ErrorLine       -- original error line number.
        );

    END CATCH

end

GO


Deny DDL_ADMIN permissions to a role on [dbo] schema I have several database schemas like,
[ext],[stag],etc
and of course the built in [dbo] schema,
and also have a role called [MyRole].
My plan is to add [MyRole] to the DDL_ADMIN role, so they can create, alter, delete objects in all schemas,
but I wish to prevent them to do exactly that in the [dbo] schema.
The DDL_ADMIN built in role provides the following permissions for its members:
ALTER ANY ASSEMBLY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
ALTER ANY CONTRACT
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATASPACE
ALTER ANY FULLTEXT CATALOG
ALTER ANY MESSAGE TYPE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY SCHEMA
ALTER ANY SERVICE
ALTER ANY SYMMETRIC KEY
CHECKPOINT
CREATE AGGREGATE
CREATE DEFAULT
CREATE FUNCTION
CREATE PROCEDURE
CREATE QUEUE
CREATE RULE
CREATE SYNONYM
CREATE TABLE
CREATE TYPE
CREATE VIEW
CREATE XML SCHEMA COLLECTION
REFERENCES

Since DENY takes precedence over GRANT, I can DENY exactly the same permissions above on the dbo schema for [MyRole].
It should be as simple as:
DENY ALTER ANY ASSEMBLY                    ON SCHEMA::dbo TO MyRole
DENY ALTER ANY ASYMMETRIC KEY              ON SCHEMA::dbo TO MyRole
DENY ALTER ANY CERTIFICATE                 ON SCHEMA::dbo TO MyRole
DENY ALTER ANY CONTRACT                    ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATABASE DDL TRIGGER        ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATABASE EVENT NOTIFICATION ON SCHEMA::dbo TO MyRole
DENY ALTER ANY DATASPACE                   ON SCHEMA::dbo TO MyRole
DENY ALTER ANY FULLTEXT CATALOG            ON SCHEMA::dbo TO MyRole
DENY ALTER ANY MESSAGE TYPE                ON SCHEMA::dbo TO MyRole
DENY ALTER ANY REMOTE SERVICE BINDING      ON SCHEMA::dbo TO MyRole
DENY ALTER ANY ROUTE                       ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SCHEMA                      ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SERVICE                     ON SCHEMA::dbo TO MyRole
DENY ALTER ANY SYMMETRIC KEY               ON SCHEMA::dbo TO MyRole
DENY CHECKPOINT                            ON SCHEMA::dbo TO MyRole
DENY CREATE AGGREGATE                      ON SCHEMA::dbo TO MyRole
DENY CREATE DEFAULT                        ON SCHEMA::dbo TO MyRole
DENY CREATE FUNCTION                       ON SCHEMA::dbo TO MyRole
DENY CREATE PROCEDURE                      ON SCHEMA::dbo TO MyRole
DENY CREATE QUEUE                          ON SCHEMA::dbo TO MyRole
DENY CREATE RULE                           ON SCHEMA::dbo TO MyRole
DENY CREATE SYNONYM                        ON SCHEMA::dbo TO MyRole
DENY CREATE TABLE                          ON SCHEMA::dbo TO MyRole
DENY CREATE TYPE                           ON SCHEMA::dbo TO MyRole
DENY CREATE VIEW                           ON SCHEMA::dbo TO MyRole
DENY CREATE XML SCHEMA COLLECTION          ON SCHEMA::dbo TO MyRole
DENY REFERENCES                            ON SCHEMA::dbo TO MyRole
GO

SQL Server says "Incorrect syntax near DENY ALTER ...' for all above.
I tried to spell out the correct syntax from BOL, but could only come up with:
DENY ALTER ON SCHEMA::dbo TO MyRole;

nothing else.
What would be the easiest way to DENY all the permissions what DDL_ADMIN membership provides to [MyRole] members, but only on [dbo] SCHEMA?
Thank you!

Some of the permissions you're trying to change don't exist in the way you're trying to use them.
For example, to deny CREATE TABLE and most other DDL statements, for a particular schema, the correct syntax and permission would be DENY ALTER ON SCHEMA::dbo TO MyRole.
This should cover the equivalent of the db_ddladmin role's permissions. (I know it's a little less than intuitive, but denying all of ALTER, like in my example above, does include denying the CREATE permissions as well.)


Performance of count(*) in subquery Suppose we have the following queries:

1.

    SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'

2.

    SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' )

Does any of the previous queries perform better? Or are they the same?

I'm using Postgresql 9.4 but, are there a big difference to another DBMS?

PS: I'm asking this question because SQLAlchemy, a Python based ORM, executes the COUNT operation by default on a subquery, but there is an option to force it to execute the COUNT directly on the query.

Do you have a reason to believe that they will perform differently? If so, why not test them on your RDBMS and with your data? In general I would say to start with the simplest query, test performance, and only attempt something more complicated (such as option 2) if necessary.

You didn't list an RDBMS so I'll give a SQL Server example. The SQL Server query optimizer doesn't directly work with the SQL that you write. It transforms it into an internal format for optimization. The following image describes the various phases of optimization and is borrowed from Query Optimizer Deep Dive - Part 1 by Paul White:

Here's my test query against the publicly available Adventure Works 2014 database:

SELECT COUNT(*)
FROM Sales.SalesOrderDetail
WHERE CarrierTrackingNUmber = '2E53-4802-85'

I can get a representation of the query tree after simplification with the undocumented trace flag 8606:

*** Simplified Tree: ***
        LogOp_GbAgg OUT(COL: Expr1002 ,)

            LogOp_Select

                LogOp_Get TBL: Sales.SalesOrderDetail Sales.SalesOrderDetail TableID=1154103152 TableReferenceID=0 IsRow: COL: IsBaseRow1000

                ScaOp_Comp x_cmpEq

                    ScaOp_Identifier QCOL: [AdventureWorks2014].[Sales].[SalesOrderDetail].CarrierTrackingNumber

                    ScaOp_Const TI(nvarchar collate 872468488,Var,Trim,ML=24) XVAR(nvarchar,Owned,Value=Len,Data = (24,506953514552564850455653))

            AncOp_PrjList

                AncOp_PrjEl COL: Expr1002

                    ScaOp_AggFunc stopCount

                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)

The above is an internal representation of your query plan. The actual steps in that query plan aren't important here. What is important is that I get the exact same simplified tree for this query:

SELECT COUNT(*)
FROM
(
    SELECT *
    FROM Sales.SalesOrderDetail
    WHERE CarrierTrackingNUmber = '2E53-4802-85'
) t;

Effectively, that means that SQL Server rewrites those two queries to the exact same thing before continuing with optimization. This means that there will not be a performance difference between the two of them. As expected, they have the exact same query plan:


Recover PostgreSQL password with admin access to host Good day.

I have a PostgreSQL server.

Once i had admin login and password, then i created a new login for web application (with low permissions like select form some tables etc). And i just lost my admin login and password.

I have access (administator) to host where the server is running. Server is windows 2008 server.
Is there any way to recover admin permissions? May be i somehow could create another superuser?

This is easy to fix. Edit pg_hba.conf to add a line like this:

host all postgres 127.0.0.1/32 trust

(You may need to restart Postgres server after editing it).

Then, use this:

psql -d postgres -U postgres

You should be able to get admin access without password now (that's why you added trust rule to pg_hba.conf).

Once this command works, change your password:

ALTER ROLE postgres PASSWORD 'newpassword';

After doing this, edit pg_hba.conf to remove line you added.


How to allow connection for edb database in Postgres By mistake we have executed disallow connections for all databases after that we are not able to connect any of the database, see below for reference:
Executed Query:

edb=# update pg_database set datallowconn=false;

[root@keng02-dev01-ins01-udm12-dbs-1510678526-01 ~]# psql -U tkcsowner
Password for user tkcsowner:
psql.bin: FATAL:  database "edb" is not currently accepting connections

Is there a way we can allow connections to edb or enterpriseDB without using PSQL? Please advise.

With PostgreSQL in general (not EDB specifically) you would do:

stop the PostgreSQL server
start postgres  in a shell in single user mode.
UPDATE pg_database SET datallowconn=true WHERE ... [to undo the error]. Be aware that template0 should normally be left with datallowconn=false.
Type Ctrl+D to quit the single user mode.
start PostgreSQL normally.


Memory Used On global_status mariadb on mariadb at information_schema.global_status they have variable name  memory_used in bytes
this is actual memory use from mariadb ?
edit add example case
on my servers i have threads_running=10 and memory_used=2666 on MB so total 26 GB
but when run command free -mh is 89 gb
with configuration
...
innodb_buffer_pool_size = 70G
innodb_buffer_pool_instances = 70
innodb_log_file_size = 8G
...

Local testing on toy test instance:
show global status like 'memory_used' 276360624
show session status like 'memory_used'; 69608
Looking the code the "global memory used" is the total of all the per connection memory allocations, but exclude actual global allocations like key_buffer, innodb_buffer_pool etc.


Out of memory in MySQL error log What should I check to solve this error that appears in the error log?
Do I need to add RAM to the server or adjust some configuration variable?

I copy part of the log:

  180102 10:45:53  InnoDB: Started; log sequence number 0 267632652
  mysql3: Out of memory (Needed 2299002880 bytes)

  180102 10:45:54 [Note] mysql3: ready for connections. Version:
  '5.0.27-community-log'  socket: ''  port: 3307  MySQL Community
  Edition (GPL)

  180102 10:46:14180102 10:46:14 [ERROR] Cannot find table
  test/cliconcepw from the internal data dictionary of InnoDB though the
  .frm file for the table exists. Maybe you have deleted and recreated
  InnoDB data files but have forgotten to delete the corresponding .frm
  files of InnoDB tables, or you have moved .frm files to another
  database?

  See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
  how you can resolve the problem.

  180215 20:15:05 [ERROR] Out of memory; check if mysqld or some other
  process uses all available memory; if not, you may have to use
  'ulimit' to allow mysqld to use more memory or you can add more swap
  space

Windows Server 2008 64 bits / 8 GB RAM

MySQL 5.0

A 32-bit MySQL on a 64-bit OS in 8GB of RAM:

The OS can access all 8GB.
The 32-bit MySQL is given no more than 4GB; it is impossible for it to use more than that.  Actually, it can't quite use all 4GB.
You could (should) reconfigure MySQL (via my.cnf) as if it were on a 4GB machine.
You could get a 64-bit version of MySQL
You could upgrade MySQL -- 5.0 is really antique.  Even 5.1 is 'dead'.  There is 5.5, rapidly aging, 5.6, 5.7, and soon 8.0.

More on configuring memory:  http://mysql.rjweb.org/doc.php/memory


MySQL DB Migration from on-premises to AWS through replication I am having a production MySQL server with set of 20 databases in on-premises server. Now I require to migrate few databases from production server to AWS RDS without disturbing other databases in one-by-one.

We can perform this activity using AWS DMS. Instead, I am looking at tranditional MySQL replication for setting up replication between existing production server to AWS RDS.

In RDS, as we are not having access to my.cnf, I am not able to set replicate-do-db configuration. Is there any other workaround to setup replication from on-premises to RDS for few databases.

Here's pretty detailed blog post about how to do this:
How to Migrate to MySQL RDS Without Downtime

Our approach was to configure M/M replication between our MySQL instance hosted on EC2 and the new RDS, then gracefully switch traffic from our application servers one at a time without any downtime for our customers.
To clarify, the Non-RDS server in this article refers to MySQL instance running either in your data center or on an Amazon EC2 instance


Create a stored-procedure to insert multiple rows on a junction table and other tables I am new to Databases and Design and needs some help on a INSERT issue.
I want to create a procedure that will insert multiple values to different tables, I need to make sure it stays atomic so I need to use transact in the procedure .

Invoice_Table

--Invoice_ID (PK)

--Purchased_Date

Fruit_Table

--Fruit_ID (PK)

--Fruit_Name

Driver_Table

--Driver_ID (PK)

--Driver_Name

--Driver_LastName

Invoice_details_Table

--Invoice_ID FK

--Driver_ID FK

--Fruit_ID FK

--Quantity

--Amount

The reason why I created a junction table( Invoice_Details_Table) is for the reason that some invoices will have more than one driver with one or more fruits.

For example a trip was done so we need to create an invoice(1). Two drivers went on this trip and delivered Strawberries and Apples.

Trip number two so invoice(2) needs to be created. This delivery was with one driver and only apples were delivered.

The problem that I am having is how to create a procedure that will update the invoice table and the Invoice_Detail_Table and rollback if something happens in the insert process. Also how can I pass the driver/fruit parameters since sometimes it will be more than one.

I am using SQL Server 2012.
Any help is much appreciated

So what you want is an insert, an update and a delete procedure for each table.  Then you want a "wrapper" procedure that tracks the inserts/updates/deletes and responds accordingly to errors.

A "transaction" would be the batch and "rollbacks" would be explicit.  I put these in quotes because you don't want to encompass all of the transactions into a single transaction.  You can't nest user transactions and a large single transaction would cause some serious blocking.

Pseudo Code for inserts...

Exec usp_NewInvoice
      @purchaseDt=@purchaseDt
    , @fruitID   =@fruitID
    , @driverID  =@driverID
    , @qty       =@qty
    , @price     =@price

--parameters are passed with the variable values created by the app

usp_NewInvoice
      @purchaseDt datetime2
    , @fruitID    int
    , @driverID   int
    , @qty        int
    , @price      money

    TRY
    BEGIN
        @ReturnValue= exec usp_InsertInvoice
                          @purchaseDt=@purchaseDt
                        , @invoiceID=@invoiceID OUTPUT

        if @ReturnValue<>0
        BEGIN
            --Error handling and rollback
            goto ERROR
        END
    END

    CATCH
    BEGIN
        @ReturnValue<>0
        BEGIN
            --Error Handling and rollback
            goto ERROR
    END

    BEGIN
        @ReturnValue= exec usp_InsertInvoiceDetail
                          @invoiceID =@invoiceID
                        , @fruitID   =@fruitID
                        , @driverID  =@driverID
                        , @qty       =@qty
                        , @price     =@price

        if @ReturnValue<>0
        BEGIN
            --Error handling and rollback
            goto ERROR
        END
    END

    CATCH
    BEGIN
        @ReturnValue<>0
        BEGIN
            --Error Handling and rollback
            goto ERROR
    END

ERROR:
IF @ReturnValue <>0
BEGIN
    --RETURN ERROR
END

For each operation you are making sure if succeeded.  If it doesn't you will need to roll back all previous transactions with other procedures (updates and or deletes).  You'll want to do something similar for the updates and deletes.


How can we get table name from inside trigger? I need to pass the table name from trigger to procedure.

ALTER TRIGGER [dbo].[Trg_ProjectCreation]
   ON  [dbo].[Projects]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
 -- procedure call
    exec Proc_Test @Tablename (not need to hardcode)
END

I want to know is there any way I can get table name from trigger itself. I need this to be dynamic because when the table's name changes I don't want to change my code.

This might not be the best approach to dealing for your problem, but it will certainly achieve the stated goal.

ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON  [dbo].[Projects]
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    declare @TableName sysname
    select @tablename = object_schema_name(parent_id) + '.' + object_name(parent_id)
    from sys.triggers where object_id = @@PROCID

    exec dbo.myProcedure @Tablename
END
GO

A better option may be to change the way the trigger is created, and putting the name down manually (if it fits your scenario), for e.g.:

declare @tablename sysname, @sql nvarchar(max)
set @tablename = '[dbo].[myTableName]'
set @sql = 'ALTER TRIGGER [dbo].[Trg_ProjectCreation] ON  ' + @tablename + '
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    exec dbo.myProcedure ' + @tablename + '
END
GO'
exec sp_executeSQL @sql


Could anyone help to index the below Query SELECT ITEM.I_TITLE,ITEM.I_PUB_DATE,ORDER_LINE.OL_DISCOUNT
FROM ITEM JOIN ORDER_LINE ON ITEM.I_ID = ORDER_LINE.OL_I_ID
WHERE OL_QTY = '&olQty'
AND I_SUBJECT = '&ISubject'
AND I_COST = '&ICost';

I am not sure do you want to create index key so you can create relations or force index keys on this statement but if you want to force it on this statement you can use this

SELECT *
FROM Table WITH(INDEX(Index_Name))

Hope this helps


MySQL 5.5 to RDS 5.6 Replication Error I am facing following error and unable to fix it. So far I have disabled binlog checksum on the RDS.

[Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2015-06-29 11:01:01 8026 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.41-0ubuntu0.12.04.1-log), even though it is on the slave (version: 5.6.22-log), Error_code: 1193

This is due to MySQL Master and Slave version mismatch

The message below is clear, and confirms the master/slaves run different versions. The variable SERVER_UUID is not supported with MySQL 5.5 but is supported with MySQL 5.6

2015-06-29 11:01:01 8026 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.41-0ubuntu0.12.04.1-log), even though it is on the slave (version: 5.6.22-log), Error_code: 1193

Please have a look at Link1 & Link2


Schema design for user activity logging I'm designing a schema to support logging of user activity, where users must be able to search:

Across all events (events of any type), with datetime range, by username;
Across events of one type, with same as above and additionally with parameters of that module.

I created this schema:

And designed queries to search:

across all events:

SELECT extract(epoch from log_time) * 1000,
  u.username,
  CASE WHEN l.event_type IN (0, 2) THEN e.template
    WHEN l.event_type = 1 THEN format(e.template, ecs.field)
    WHEN l.event_type = 3 THEN format(e.template, ess.field)
    WHEN l.event_type = 4 THEN format(e.template, ect.ip, ect.port)
  END
FROM logs l
JOIN users u ON u.id = l.user_id
JOIN event_def e ON e.id = l.event_type
LEFT JOIN event_client_search ecs ON ecs.log_id = l.id
LEFT JOIN event_switch_search ess ON ess.log_id = l.id
LEFT JOIN event_cable_test ect ON ect.log_id = l.id

across same-type events:

SELECT extract(epoch from log_time) * 1000,
    u.username,
    format(e.template, ect.ip, ect.port)
FROM logs l
JOIN users u ON u.id = l.user_id
JOIN event_def e ON e.id = l.event_type
JOIN event_cable_test ect ON ect.log_id = l.id

This is what the event_def table looks like:

 id |                template
----+----------------------------------------
  0 | Logged in
  2 | Logged out
  1 | Searched in clients %s
  3 | Searched in switches %s
  4 | Cable test switch %s port %s

But what I didn't like, when I have about 100 events (now I have about 50, but haven't implemented them yet), it is going to be a performance issue.

So I thought to merge events with same parameters, like this:

I don't think that will help a lot (it'll cut the number of events by half at most), maybe I'm thinking in the wrong direction?

One of the assumptions that I would ask you to reconsider is whether you really want to have different tables for each event type, or if rather these event types are rows in a table instead of distinct tables.

If you had an EVENT_TYPE table containing your list of event types (about 100 of these) you could take the columns from your current various event tables and make them rows in an EVENT_PARAMETER table.

This type of design would turn your schema changes into data changes when you add new event types.  That would save you code maintenance issues and also avoid making your queries more complex and potentially slower.

The instances of events would similarly be kept in two tables, for example a LOG table and a LOG_DETAIL table.

LOG would be an intersection between user and event type.

LOG_DETAIL would be an intersection between log and event parameter.

Your data model might look something like this:

Note that one objection some people may have to this approach is that you end up saving the log parameter values in a string format, rather than in a native format.  This is clearly a trade-off.  You have to ask yourself is it a good trade-off for your situation.


Adding NotNull FK field in existing DB Context:
We’re building a web application to manage business-data. Each “client” of the application has a separate DB (this wont change), and each DB is based off a model (so they are all identical in structure). RDBM is MySQL.

Problem:
When we make modifications to the model DB, we propagate those changes to all client DBs. This is fine in most cases, EXCEPT when we add a new field that is also a not-null FK. In this case the DB upgrade fails.

What we’ve explored:

We could “impose” that all new FK fields can be nullable, however
this potentially defies the business logic that the DB must respect.
We could “select” a default value from the existing data in each
client DB. However, this is potentially absurd: Think of adding a NotNull
FK from Invoice table to Client table… which client should be
selected?

Is there a way to make these types of upgrades without making (crazy) default selections or breaking the business logic of the DB?

It seems that you have run into the reason why NULLs exist in relational databases.  Choosing an arbitrary value from somewhere is a little crazy as you describe.

Options:

Create the needed key (PK or other Unique value) prior to doing the update of the data.  This would require you to pre-decide how to fill each Not NULL FK.  Obviously you want the FK to connect to something or else you would be happy with a NULL.
Note:I suppose that you could create a row for a default value that joined to each new Not NULL FK that you add. Perhaps give it an ID of -1 and a text of "(Unknown)" or something else that you could work around.  You might wind up scattering around many such "keys" if you are not careful.
Use an Intersection Table instead of adding a foreign key column to the existing table.  The intersection table would consist of two columns: TableAID and TableBID.  In that way, the intersection row will be inserted as needed without any dummy values.

Sample, from:https://stackoverflow.com/questions/977615/many-to-many-relation-design-intersection-table-design

CREATE TABLE SomeIntersection
    (TableAID INT REFERENCES TableA NOT NULL,
     TableBID INT REFERENCES TableB NOT NULL,
     PRIMARY KEY(TableAId, TableBId ));


Backup and trimming data idea Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows.

We would like to maintain only 3 months of data.

The rest we would like to purge and keep to generate history reports. What is the best mechanism to go about in this scenario?

Should we run a cron job to keep deleting data which is more than 3 months old? If so, how can we store it in a .sql file?

As you have foreign keys in your tables so you can't go with partitioning directly.As Partitioning does not support foreign keys in MySQL.

You may go with partitioning but then you will have to create the work around for that.you have to ensure your integrity by using triggers for (INSERT/UPDATE/DELETE) on tables.

i.e Insert into the Child table will only be allowed if the corresponding record exist in Parent table.
You have to maintain all these integrity(INSERT/UPDATE/DELETE) by using triggers.

Then you can create the partitioning on table's on a quarter basis and then drop the  partition.

for creating the .sql files you may use --where clause with mysqldump it will create the backup in .sql file format.

You can also create the csv's for your old data for the future reference.


Nested queries or temp (#) tables in SQL Server I have written a web application which aggregates and presents data from a SQL Server. The data involved is large, and there is a lot of aggregation involved. Currently, under advice from an 'expert' I have used heavily nested queries including the aggregations, however I have just been reading here about optimising large complex queries by breaking them down into temp (#) tables.  This seems to conflict with a lot of literature suggesting that using temp tables is not the best practice.

The web app itself is to be used by quite a few people, so ideally I wouldn't want the temp table, if they need indexing, to conflict with each other.

What is the best way forward here for query optimisation, and best practice for a widely used web app?

Thanks in advance.

CM

The SQLCat team ideas and articles are sometimes pitched at edge cases that most folk will never see given their load or database size.

Saying that, the ideas they give there are common techniques to improve performance (eg change OR to UNION)

Temp tables have some issues around stored procedure compilation etc, but don't confuse these with table variables (the SQLCat articles mentions this). Also, temp tables should be local not global to separate processes don't affect each other

Personally, I use temp tables quite often to break queries down: but not all the time. If I can do it in one SQL statement that runs well enough (for it's frequency of use) then I'll use that.

In your case, I'd identify a few problem queries and see if using temp tables suits these better. If you can't see any problem queries then do nothing.


Oracle 11g: SQLDeveloper How would I use the pre-installed SQLDeveloper that comes packaged within Oracle 11g if I am invoking it with a user that has no oracle or admin rights on linux?

Would it be enough just to change permissions to the sqldeveloper folder, just as shown below:

chmod -R a+x sqldeloper/

Or it would be better to install SQLdeveloper with the "user" that has not oracle or admim rights?

I would not touch that directory, I have not found any references of other users using the SQL Developer software that comes with database binaries. Additionally, SQL Developer shipped with the database binaries is kind of outdated. Even 12.1.0.2 comes with SQL Developer 3.2

I would just perform a seperate install.

Also, I find it unusual that someone, who is not the DBA, wants to run SQL Developer on the database server itself. Why not just install it on the client side?

(To be honest, I would not even include SQL Developer in the database binaries package and install process, but it can not be customized.)


store result set in temporary table, variable or separate variables within a trigger I'm in the process of creating some triggers to store the the average rating for a given product on the product table. I've also been asked to store the total number of reviews for the product. So - I need to retrieve 2 values in my trigger, and then update the target table appropriately.

Should I use separate select statements

SET my_a = SELECT a FROM foo;
SET my_b = SELECT b FROM foo;

a single statement to retrieve a result set as a variable (is this even possible?):

SET var = SELECT (a,b) FROM foo;
UPDATE bar SET c=var.a,d=var.b;

or maybe even store the result set in a temporary table within the trigger(is that even possible?)?

The first method is the best because of the least overhead for the queries generated. In fact, in the MySQL Stored Procedure Language, you want as few declared variables as possible.

The second is not possible since the MySQL Stored Rrocedure Language does not have object support. A clumsy but workable UPDATE JOIN is possible if you...

update the question with the CREATE TABLE statement for the product table
show us the trigger defintion you have thus far

The third method is not possible because explicit DDL and DDL via Dynamic SQL are not allowed in MySQL Triggers.

You may have to create a regular table using either the MyISAM or MEMORY storage engine. Then, you can have the trigger compile your data to a table that actually exists. MyISAM is better because should a server go down, the compiled data thus far is on disk. MEMORY tables are faster to write to, but are gone on system restart.

DO NOT USE CREATE TEMPORARY TABLE AT ALL because such tables only last as long as the DB connection lives, and would be private unto the call of the trigger also. Even worse off, if you are using MySQL Replication and you run STOP SLAVE on the slave, any tables created via CREATE TEMPORARY TABLE disappears from the SQL Thread and replication breaks immediately when you run START SLAVE and those temp tables no longer exist.


SELECT * Returns 1 Record, But SELECT COUNT(*) Shows 3k+ Records I imported a csv file that had more than 3k records into a database table in SQL Server using the Import Wizard.  I do not receive any errors, and the import wizard shows success, with 3217 records imported.  If I run the following statement, I get 3217 records:  SELECT COUNT(*) FROM tbl.  However, if I try SELECT * FROM tbl, only one record is returned.  What's going on here?

Using SQL Server 2014 Management Studio.

So I figured this out less than a minute after I hit post, despite thinking about it and googling for a bit.

So the problem is that I ran a procedure earlier that deleting some duplicate values in a table using SET ROWCOUNT 1 and a WHILE loop.  I realize that I didn't actually set rowcount back to zero (SET ROWCOUNT 0) after the break in the loop.  After doing that, I can see all the records now.


Do BIT columns offer any performance advantages for CCIs? Do BIT columns offer any performance advantages when used in Clustered Columnstore Indexes? I am interested in any performance benefits gained from defining a column in a CCI as BIT instead of BIGINT, for example. I'm working with SQL Server 2016.

I have a very limited understanding of how CCI compression works, but based on what I've read and some testing it seems like the data type (limited to exact numerics that store whole numbers) really shouldn't matter when it comes to columnstore compression. For example, if I insert 10 full rowgroups into tables with BIGINT columns as opposed to BIT columns I don't see a size difference between the compressed rowgroups. Here is the source data for one test:

DROP TABLE IF EXISTS dbo.CCI_BIT_TEST_SOURCE;

CREATE TABLE dbo.CCI_BIT_TEST_SOURCE (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    ID_BIT BIT NOT NULL,
    ID_BIGINT BIGINT NOT NULL,
    INDEX CCI__CCI_BIT_TEST_SOURCE CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_BIT_TEST_SOURCE WITH (TABLOCK)
SELECT
  t.RN
, t.RN
, t.RN % 2
, t.RN % 2
FROM
(
    SELECT TOP (10485760) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
) t
OPTION (MAXDOP 1);

Inserting into a CCI with 8 BIT columns took an average of 18729 ms of CPU time.  The table has 56960 KB of reserved space:

DROP TABLE IF EXISTS dbo.CCI_BIT;

CREATE TABLE dbo.CCI_BIT (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    ID_BOOL_1 BIT NOT NULL,
    ID_BOOL_2 BIT NOT NULL,
    ID_BOOL_3 BIT NOT NULL,
    ID_BOOL_4 BIT NOT NULL,
    ID_BOOL_5 BIT NOT NULL,
    ID_BOOL_6 BIT NOT NULL,
    ID_BOOL_7 BIT NOT NULL,
    ID_BOOL_8 BIT NOT NULL,
    INDEX CCI__CCI_BIT CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_BIT WITH (TABLOCK)
SELECT
  ID1
, ID2
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
, ID_BIT
FROM dbo.CCI_BIT_TEST_SOURCE
OPTION (MAXDOP 1);

Inserting into a CCI with 8 BIGINT columns took an average of 18531 ms of CPU time.  The table has 56960 KB of reserved space, the same as before:

DROP TABLE IF EXISTS dbo.CCI_NO_BIT;

CREATE TABLE dbo.CCI_NO_BIT (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    ID_BOOL_1 BIGINT NOT NULL,
    ID_BOOL_2 BIGINT NOT NULL,
    ID_BOOL_3 BIGINT NOT NULL,
    ID_BOOL_4 BIGINT NOT NULL,
    ID_BOOL_5 BIGINT NOT NULL,
    ID_BOOL_6 BIGINT NOT NULL,
    ID_BOOL_7 BIGINT NOT NULL,
    ID_BOOL_8 BIGINT NOT NULL,
    INDEX CCI__CCI_NO_BIT CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_NO_BIT WITH (TABLOCK)
SELECT
  ID1
, ID2
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
, ID_BIGINT
FROM dbo.CCI_BIT_TEST_SOURCE
OPTION (MAXDOP 1);

We can see this in the columnstore DMVs as well:

There are some advantages to using BIT columns in CCIs. For example, data loaded into delta stores will take up less space with BIT columns because delta stores are basically uncompressed heaps. In query plans, the formula for the estimated data size is based on the data types of the columns as opposed to the size of the table on disk. The table with BIT columns has a total data size of 250 MB and the table with BIGINT columns has a total data size of 880 MB. In some cases the estimated size of 250 MB could lead to a better plan.

Are there any other performance benefits of BIT columns for CCIs? Or does the data type not really matter as long as you're using an exact numeric that stores whole numbers (BIT, TINYINT, SMALLINT, INT, or BIGINT)?

First, we are not comparing like data types.
Bit is defined as:

An integer data type that can take a value of 1, 0, or NULL.
BIT

Whereas BIGINT is a larger integer that consumes a significant amount of space by default.
That means by default SQL Server has statistics on each of the BIT columns and only one set for the BIGINT.
As you rightfully noted, BIT columns are optimized:

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
int, bigint, smallint, and tinyint

there must be a reason you are even considering taking at least 8 - 10 binary values and shoving them into a numerical number.
After all, why lot just use INT and save half the space? Since last I checked, 2,147,483,648 is 10 characters for just 4 bits and BIGINT has about 19, which technically is less space than BIT of youvspoit then into columns.
But this is losing sight of what your data is. How does BIT answer questions on what it represents? 10010 is just a number greater than ten thousand and yet in binary that actually represents something. If in "Saving" space you are forcing transformation before even using the data, will it still be efficient?
But please, do not conflate BIT with a numeric data type like Tinyint or BIGINT. They serve two different purposes.


Unique-like constraint in a complex scenario I have a table in SQL Server 2016, as shown below. Records are inserted into the table each quarter, for all partners.
Constraint Needed: Should allow only one BatchID for a partnerID for a year-quarter, for a Distribution_Type. However it is allowed to have multiple records with same BatchID for the partner for a given year-quarter, for a distribution type.
How to create such a constraint in the table? If it can be done without trigger that would be great.

Table
CREATE TABLE [dbo].[Distribution]
(
    [Distribtution_Result_ID] [int] IDENTITY(1,1) NOT NULL,
    [PartnerID] [int] NOT NULL,
    [Distribution_Year] [int] NOT NULL,
    [Distribution_Quarter] [int] NOT NULL,
    [Distribution_Amount] [decimal](16, 4) NULL,
    [Distribution_Type] [varchar](100) NULL,
    [Batch_ID] [int] NOT NULL
)

To explain ypercubeᵀᴹ's comment:

Yes, this can be solved if you add a second table and an FK from this to the second table

If the requirement is one BatchId per PartnerId,Year,Quarter,Distribution_Type then you will need a table such as:
CREATE TABLE dbo.Partner_Batch --Or whatever
(
    PartnerID             INT           NOT NULL,
    Distribution_Year     INT           NOT NULL,
    Distribution_Quarter  INT           NOT NULL,
    Distribution_Type     VARCHAR(100)  NOT NULL,
    Batch_ID              INT           NOT NULL,
    CONSTRAINT PK_Partner_Batch PRIMARY KEY (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type)
)

Of course you'd need the relevant FK references for PartnerID and Batch_ID as well as something to ensure Quarter is only valid values (1,2,3,4).
You would then modify your existing table with the foreign key constraint to ensure the rule is enforced:
CREATE TABLE [dbo].[Distribution]
(
    Distribtution_Result_ID   INT IDENTITY(1,1) NOT NULL,
    PartnerID                 INT               NOT NULL,
    Distribution_Year         INT               NOT NULL,
    Distribution_Quarter      INT               NOT NULL,
    Distribution_Amount       DECIMAL(16,4)     NULL,
    Distribution_Type         VARCHAR(100)      NOT NULL, --Note: This column cannot be NULL
    Batch_ID                  INT               NOT NULL,
    CONSTRAINT FK_Distribution_Partner_Batch FOREIGN KEY (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type) REFERENCES dbo.Partner_Batch (PartnerId, Distribution_Year, Distribution_Quarter, Distribution_Type) --I would use a more descriptive name for the relation, but I don't have enough information about your system
)

From afar, there's a lot more work to be done, but at minimum I'd say you'd need to:

Have a reference table for Distribution_Type to only allow valid values.
Trim down the data type for Distribution_Type or substitute a shortname/code.
Define an actual key for Distribution other than the row identifier.


Error installing Microsoft SQL server 2008 R2 with tools! Error code When trying to install SQL Server 2008 R2, I get the following error:

Error installing Microsoft SQL server 2008 R2 with tools! Error code: -2068578303

Does anyone know what this means?

Error code: -2068578303 translates into hex 0x84B40001 which corresponds to an error in the SQL Server setup command line.  You can likely find the answer to your problem at:

http://msdn.microsoft.com/en-us/library/ms144259%28SQL.100%29.aspx


Sync MySQL with SQL Server 2008 My current project requires a form on my website to populate a table in a MySQL database with data a user submits via the form. This will work great but my complete goal involves getting that data into a MSSQL table.

I'm populating the MySQL table and linking to this MySQL data from MS SQL Management Studio via a linked table. I am able to connect to the MySQL database successfully.

My question is whether or not I'm going in the right direction, and further, how do I get this data sent into a MSSQL table? The project I am on involves giving my users visibility to data submitted via the web forms as soon as it is submitted.

Curious if there is any feedback that can point me in the right direction for this particular scenario? What is the best practice for a situation that requires a website to populate a MySQL database, and that data getting "copied" to a MSSQL server database?

Thanks!

Try to find a soft that simulates replication or write a module for mssql yourself like adviced here https://stackoverflow.com/questions/14874587/replicate-microsoft-sql-to-other-databases


Deny All DDL & DML Commands In Sql Server I want to deny all ddl and dml commands to AD and SQL Server users over all the databases on my database server. How can i do that?

Well...the AD part is unnecessary, that only gives authentication/login privileges to users (basically all AD does in SQL Server is tell SQL Server "I have verified that this person is who she/he says it is and you can allow this person to connect because I say so" - this is a very basic explanation, of course).

If you want to set up privileges about what data users can see and what they can or cannot execute on your SQL Server it is enough to do this in SQL Servers itself. Also, I would not over-complicate things with 'deny', just 'revoking the permission'is more than enough. If they don't have the right to execute DDL and DML, they will not be able to do so, deny is unnecessary.

Here is how to do it:

Step 1. Go to your SQL instance -> Security-> right click the login of the user / group you want to do this for, then on Properties and in your newly opened window got to  'ServerRoles' and make sure only public is selected.

Step2: Go to 'ÚserMapping' --> Database you want to do this for --> make sure only 'public'and 'data reader'are selected.

Step3: Repeat this for all DBs you need to give permission to the user for data reading, but not DML & DDL statements.

Do the same for all other logins you need to do this for.

In SQL Server 2016 or newer make sure you also check the DB user because they decoupled the server level permission from the database level permissions. So you can deny this on server level, but you have this set up on a DB level you and then your sever level setting has no effect.
You can check the database level permissions by going to Database --> Security --> right click on the user you are interested in --> properties. Then on your newly opened window go to both 'owned schemas' and 'membership' and make sure nothing or only 'db_reader'are selected.

You find documentation here and here.

Also, you might want to check this and this out - it shows some examples of how you can get a list of the logins,users and permissions - it will make your life a lot easier.


Postgres serializable snapshot isolation for jsonb I'm trying to acquire a serializable lock on a JSONB column, but it looks like by default JSONB supports only row level lock on transactions.

Basically I want to store JSON like:

{a:"v1", b:"v2"}

Here I want to acquire lock on my json, so that when two users edit a/b concurrently for me database shouldn't block transactions. However, if both user edit the same key concurrently, I want to stop them.

My application needs properties that need to be stored and updated that's the reason I went ahead for JSONB. Not sure are there any way to achieve serializable lock on JSONB.

The keys will be fixed. They can edit more than one key. No one can delete the JSON keys according to my design, only update them.

You can't do this with JSONB.  To get this level of separation, you would have to have a parent and child table, where the child table would be something like:

(parent_id whatever, key text, value text, primary key (parent_id, key))

If your JSON can be nested and not just a flat key-value store, then it is ambiguous at what level you want the locking to occur.  If it always occurs at the first level, then this would still work but the value column would be of type JSONB.

If the locking occurs deeper, then that would really be a mess.  What if someone wants to change a value 4 levels down, and someone else wants to remove the entire sub-tree from the 2nd level?

EAV is the design which fits your desired concurrency/locking model. Obviously there are other considerations than that, but I wouldn't know what those are.  You could always use a single row, block until the other guy commits, then "manually" re-check that the value you want to update wasn't changed by the other guy.


RDS MySQL instance is unavailable, probably during binlog rotation One of my customers uses a few EC2 linuxes with PHP application, connected to a standalone MySQL RDS.
He reported application outage. During my investigation  I found that this outage happened immediately after  some RDS operation with mysql binlogs, probably log rotation.
Is there some known issue?

Update: Many thanks to Rolando and Maxime. I have a few years of MySQL experience; but I'm pretty new @ AWS. So my question in fact: "is there some RDS-specific issue with MySQL binlog processing which causes to DB outage?"

Your graph shows a binary log purge launched at approximatively 4am the 23th.

You can search for a query like:

PURGE BINARY LOGS BEFORE '2014-04-23 04:00:00';
-- or
PURGE MASTER LOGS BEFORE '2014-04-23 04:00:00';

If you are sure that your MySQL is a standalone instance, there is no know issues with the PURGE BINARY LOGS statement and I see that your binary log is 1.5Mb so i'm sure your system can manage that purge without pain. In a Master/Slave infrastructure, you can purge binary logs before they been "played" on slaves, the result is the slaves replication goes down (with impact on the apps).

Max.


Why is there a collation conflict between system views I'm using the following statement to retrieve a list of triggers from the database:

select tr.name
from sys.triggers tr
union all
select tr.name
from sys.server_triggers tr;

However on my local SQL Server 2012 Express installation this fails with the error:

  Cannot resolve collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in UNION ALL operator occurring in SELECT statement column 1

I can "fix" this by changing the statement to explicitely use a different collation in the second part of the union:

select tr.name
from sys.triggers tr
union all
select tr.name collate database_default as name
from sys.server_triggers tr;

However I feel this is an ugly hack, and I don't really understand why there is a conflict in the first place. Both views ultimately retrieve the name from sys.sysschobjs.name and thus all values should have the same collation.

So my two questions are:

why is there a collation conflict in the first place?
is it safe to use collate database_default only in the second part, or should I better use it for both queries (performance is not an issue for this).

Interesting enough the same query does not create a problem when using it against a SQL Server 2005 (standard edition) with the same triggers defined.

sys.server_triggers is a server view, so it has the server collation. sys.triggers is a database view, so it has the database collation. You have a DB that has a collation different from the server. QED. Repro:

create database foo collate Latin1_General_CI_AI

use foo

select tr.name
from sys.triggers tr
union all
select tr.name
from sys.server_triggers tr;


pg_switch_wal In Point In Time Recovery PostgreSQL I was following this tutorial about point in time recovery in postgres and come up with the following questions. Can anybody help me.

Is it necessary to use pg_basebackup every time I want to back up the database or the postgres backs up the database and stores somewhere considering its configurations?
What does the psql -c "select pg_switch_wal();"do? and is it necessary to run every time I want to back up the database?

I use postgres 11 on ubuntu 18.04.3 if that helps.

EDIT

Let me be more specific in my question. As I've understood by searching on the internet, we should have two files to back up the database: one base which contains the general files such as table attributes and a WAL file which contain the log files showing instances in the database. And I have seen that pg_basebackup backs up whole database cluster not only WAL files, so there should be a function which only backs up WAL files.

Thanks

In theory, you can run "pg_basebackup" just once (or once per major version upgrade) but then it will take longer and longer to do a restore, as it has to run through all the accumulated WAL since that backup.  So it usually makes sense to do a new pg_basebackup periodically.

"The database" won't do it for you.  There are a variety of backup tools out there that probably will (pg_barman, pgbackrest, pg_probackup, etc.).  But you didn't mention using any of them.

pg_switch_wal() is not necessary as pg_basebackup does it for you.

pg_basebackup will archive the wal files if you set it to, but will only do so for WAL files which are generated during the duration of the base backup itself.  This is suitable for a snapshot backup, but not for PITR.  In other words, it will let you restore to the exact moment the backup command finished, but is not enough to let you restore to some point after that time. To backup WAL files at other times, you need to set the "archive_command" setting to something that will copy the files for you.  (Also, if you have a valid "archive_command" in place before you start the basebackup, then the basebackup itself doesn't really need to backup the WAL in the first place.  Then the base backup will not be self contained, you will have to pair it with the WAL archive in order to restore.)

The tutorial you refer to shows a setting for "archive_command".  However, the setting it shows is not bullet-proof.  As the docs say, "this is an example, not a recommendation".  It has two main problems.  Once is that it does not fsync the copy before declaring success.  That means the system can remove/recycle the WAL file from pg_wal, before the copy of it has been flushed to the disk drive; and so a crash at the wrong time can leave you with zero valid copies.  The other problem is that it seems to be copying the file locally.  That means that if your computer is destroyed, you will lose your backup at the same time as you lose your master copy.  (It is possible that it is not copying locally, because /var/lib/postgresql/pg_log_archive might be a network mounted file-system; but if so then that exacerbates the fsync problem, how do you know the copy on the remote file system is truly safe?)

If you don't want to use "archive_command", then another tool is "pg_receivewal".  You would run this on another server, and it will connect to your Postgresql instance and "pull" the wal files, rather than the "push" that "archive_command" does.

Yet another option is to set up a streaming standby on another server, set "archive_mode" to "always", and then have "archive_command" run on the standby, rather than on the master, to save your WAL files.


adcfgclone.pl dbconfig fails when trying to configure the database to use logical host names adcfgclone.pl dbconfig fails when trying to configure the database to use logical host names. Unable to make database connection, error in ApplyDatabase.log
"   Exception occurred: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection
    Connection could not be obtained; returning null"

Database version: 12.1.0.2
Oracle E-Business Suit version : 12.1.3

Steps performed:
1. Adpreclone.pl run on apps tier and two database node (2 node rac database)
2. Created context file on the first database node to use logical hostname.
3. adcfgclone.pl dbconfig being run to configure the database to use logical name. Error at this step.

Vip name from /etc/hosts was being used for the setup and causing the error.
Corrected the context file to use the name specifies with physical name and the issue was resolved.


Pl/SQL web application I have a PL/SQL web application,got the entire DB dump from remote DB.
Its very old application, running on oracle 9i.
I basically want to get the application up and running on my laptop,but i am not able to figure out how.
Can somebody help me with this?

IMHO the easiest way to get an OWA application to work is by installing a standard Apache distribution and the open source mod_owa.

This is easiest on modern 64bit Windows, downloading Apache 2.4 from http://www.apachelounge.com/download/ and mod_owa from https://oss.oracle.com/projects/mod_owa/dist/documentation/modowa.htm