Query results not as expected - Jsonb Postgres We've created the following table:

CREATE TABLE public.influencers
(
    id integer NOT NULL DEFAULT nextval('influencers_id_seq'::regclass),
    location jsonb,
    gender text COLLATE pg_catalog."default",
    birthdate timestamp without time zone,
    ig jsonb,
    contact_info jsonb,
    created_at timestamp without time zone DEFAULT now(),
    updated_at timestamp without time zone DEFAULT now(),
    categories text[] COLLATE pg_catalog."default",
    search_field text COLLATE pg_catalog."default",
    search_vector tsvector,
    ig_updated_at timestamp without time zone,
    CONSTRAINT influencers_pkey PRIMARY KEY (id),
    CONSTRAINT ig_id_must_exist CHECK (ig ? 'id'::text),
    CONSTRAINT ig_username_must_exist CHECK (ig ? 'username'::text)
)

And created the following index in order to filter a field inside ig that contains integers. This is the index we created:

CREATE INDEX idx_btree_ig_follower_count
  ON public.influencers
  USING BTREE ((ig->>'follower_count'));

The following query should return 427000 results. Instead, it returns a random number that nothing has to do with what it should return. This is the query:

SELECT count(*)
FROM "influencers"
WHERE ((ig ->> 'follower_count') >= '1000')
  AND ((ig ->> 'follower_count') <= '10000')
  AND (ig->>'follower_count') IS NOT NULL

We suspect that it is comparing strings (or text) instead of integers and that's why it's returning incomprehensible results.

However, the query that works (return expected rows) but doesn't use the idx_btree_ig_follower_count index is the following:

SELECT count(*)
FROM "influencers"
WHERE ((ig -> 'follower_count') >= '1000')
  AND ((ig -> 'follower_count') <= '10000')
  AND (ig->'follower_count') IS NOT NULL;

How can we create the query or the index in order to being able to query efficiently?

Try this.. Just out of curiosity..

CREATE INDEX idx_btree_ig_follower_count
  ON public.influencers
  USING BTREE (CAST(ig->>'follower_count' AS int));

Then in your query, cast to int.

SELECT count(*)
FROM "influencers"
WHERE ((ig ->> 'follower_count')::int >= '1000')
  AND ((ig ->> 'follower_count')::int <= '10000');

You don't need to specify IS NOT NULL if you specify >=, and <=

Test case

I created some test data with 10 million rows of jsonb, with btree index over it.

CREATE TABLE influences
AS
  SELECT
    jsonb_build_object('follower_count', trunc(random()*1e7)::int) AS ig
  FROM generate_series(1,1e7);

CREATE INDEX ON influences( CAST( (ig->>'follower_count') AS int) );

I'm getting the same query plan you provided,. You can see mine here.

EXPLAIN (ANALYZE,BUFFERS)
SELECT count(*) FROM influences
WHERE (ig->>'follower_count')::int BETWEEN 0 AND 854780;
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=130124.36..130124.37 rows=1 width=0) (actual time=482.615..482.615 rows=1 loops=1)
   Buffers: shared hit=85674
   ->  Bitmap Heap Scan on influences  (cost=18483.87..127946.94 rows=870969 width=0) (actual time=206.182..426.880 rows=855570 loops=1)
         Recheck Cond: ((((ig ->> 'follower_count'::text))::integer >= 0) AND (((ig ->> 'follower_count'::text))::integer <= 854780))
         Heap Blocks: exact=83334
         Buffers: shared hit=85674
         ->  Bitmap Index Scan on influences_int4_idx  (cost=0.00..18266.13 rows=870969 width=0) (actual time=182.158..182.158 rows=855570 loops=1)
               Index Cond: ((((ig ->> 'follower_count'::text))::integer >= 0) AND (((ig ->> 'follower_count'::text))::integer <= 854780))
               Buffers: shared hit=2340
 Planning time: 0.341 ms
 Execution time: 482.665 ms
(11 rows)

I'm aggregating far more rows, 855570 in my example, and it's over 10x faster.

I clustered it

CLUSTER influences USING influences_int4_idx ;

And, that halfed the time further to 200ms

EXPLAIN (ANALYZE,BUFFERS) SELECT count(*) FROM influences WHERE (ig->>'follower_count')::int BETWEEN 0 AND 854780;
                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=130124.66..130124.67 rows=1 width=0) (actual time=211.880..211.881 rows=1 loops=1)
   Buffers: shared hit=4 read=9466
   ->  Bitmap Heap Scan on influences  (cost=18483.94..127947.22 rows=870976 width=0) (actual time=69.503..155.670 rows=855570 loops=1)
         Recheck Cond: ((((ig ->> 'follower_count'::text))::integer >= 0) AND (((ig ->> 'follower_count'::text))::integer <= 854780))
         Heap Blocks: exact=7130
         Buffers: shared hit=4 read=9466
         ->  Bitmap Index Scan on influences_int4_idx  (cost=0.00..18266.20 rows=870976 width=0) (actual time=68.150..68.150 rows=855570 loops=1)
               Index Cond: ((((ig ->> 'follower_count'::text))::integer >= 0) AND (((ig ->> 'follower_count'::text))::integer <= 854780))
               Buffers: shared hit=3 read=2337
 Planning time: 0.412 ms
 Execution time: 211.929 ms
(11 rows)


SQLServer: "order by" clause causing excessive locking I can't seem to find an explanation as to why this is happening, but while trying to debug a deadlock issue, I found out that SQLServer is locking far more records than it needs to when I include an "order by" clause in my query with the updlock and readpast hints.

For context, I am doing a "select top 1" query on a table that is used for queuing jobs in a multi-node application. I want to grab the oldest job that has not been processed yet, that matches certain criteria that requires joining on a few tables (all of these tables have the nolock hint) If I sort the list by the creation date, all of the records matching the filter are locked, but only one is returned. But if I remove the sorting, only the one record that I want is locked.

Any hints as to why this is happening?

As it turns out, adding an index on the column that I'm sorting by fixes the problem.


Block some entries. MySql I am newbie in MySql, thus i have very naive question.
I have some database (for example, about hotels). All users may read information about these hotels. Admins are allowed to change this information.
My question is the following: How to block some hotel entry during its modification? In other words if some admin send a query for modification data for hotel1 (it takes time 1-10 secs) all other admins should wait with their modifications of this hotel. Users should see only old information until modification will be complete.

Million Thanks in advance!

If you are looking for a locking mechanism, You have several options.
For example you may create a new table where you track who is editing and what is being edited. This new table will have an unique key with the fields of "record_edited".

When an administrator starts to edit a hotel detail, you insert a row into this table, and if the result says duplicate entry, then there is somebody already having that record modifying, and tell the admin to wait a little.

If this query returns without error, you then allow the admin to proceed.

When the admin finished with the update on the hotel details, you then remove the "locking record" from the table, and update the hotel details in the database.

Example table for the locking mechanism:

CREATE TABLE `mod_lock` (
 `who_editing` int(11) NOT NULL,
 `record_edited` int(11) NOT NULL,
 UNIQUE KEY `record_edited` (`record_edited`)
)

This method will allow multiple hotel details being edited, while "table lock" will not permit changes to the whole table for the hotel details until the admin finishes with one update.


Use GUID as primary key in Azure? I want to use GUIDs since they are portable and makes it easier to use several database. But since there are performance issues it might not be a good idea? Although I've found a MS blog entry which states otherwise: http://blogs.msdn.com/b/cbiyikoglu/archive/2012/05/17/id-generation-in-federations-identity-sequences-and-guids-uniqueidentifier.aspx

I've read about an alternative where a datetime2 column is used as the clustered index and the Guid as a PK. As most of my tables have a CreatedAtUtc column that should be a viable option for me. But do it really help?

So my questions is whether I should use GUIDs to get portability or if there are better solutions?

(Using GUIDs I can also create the Id in my code and do more stuff before committing). I've also created a COMB generator which should work better with MSSQL than regular GUIDs.

GUIDs only really cause performance problems for a couple of reasons.

First their size.  You are using a 16 byte identifier where an int(4 bytes) or even bigint(8 bytes) is much smaller.  This doesn't really matter so much on a wide table or short table.  If your table is fairly narrow then the percent increase is of course greater.  A 2000 byte width vs a 2012 byte width is really not that big a deal where a 20 vs 32 is obviously a greater difference.  And if your table is going to be large, millions or even 100's of millions of rows, then that 8-12 bytes you are adding is going to make a more obvious difference than say a table with only 1000 rows.  On the other hand if you need that portability then you may be willing to accept the space issues.

Next are the issues that only occur if the GUID is part of the clustered index.  If the GUID is the clustered index then the GUID will be included the leaf level pages of all non-clustered indexes.  So to compare, a table with an integer clustered index will only add 4 bytes to the leaf page while the GUID will add 16.  This can add up over time particularly with multiple indexes.  It also will of course increase the number of reads to use that index since you can fit less entries per page.  The other problem GUIDs have with a clustered index is that they are not typically in order.  This means potential page splits during inserts.  The link you provided mentioned that this isn't a huge increase over the write times in Azure however.  Typically you could use use NEWSEQUENTIALID() to generate the values as it returns a value greater than any previous value generated by NEWSEQUENTIALID() on that computer since windows was started however it is not supported by Azure.

In my opinion if you are already going to have a CreatedAtUTC column then go ahead and make it the clustered index and use the GUID as your primary key.  Also here is a link I found to a blog by the Azure team on the subject.  Of course he self processes to love GUIDs so he is somewhat biased.  Also another benefit of using your CreatedAtUTC as the clustered index is that it doesn't change.  This means minimal row moves.


Database normalization: Which is the best fit? I have 3 different types of data set:

Notification:

start_date
end_date
notification_type
country_id
state_id

Campaign:

start_date
end_date
user_notification

Transaction

email
transaction_value

All these 3 have the same types of child tables where there will be foreign keys pointing to these tables.

What type of design will be best?

3 different tables and one child table that will have 3 different columns as primary keys pointing to each different parent?
1 combined parent table allowing null values for columns which will be not needed for any particular type and a main_type column which says whether each record is a campaign, a notification or a transaction. The child table will have only one foreign key column.
3 different tables, 1 for each of the main table types with a child table for each main table that uses the main table's primary key as a foreign key.

Having the same types of columns is not the same thing as being the same entity type.  Normalization is not the same thing as code reuse.

Normalization is about arranging your columns into tables in such a way that you avoid insert, update and delete anomalies.  It is about reducing the kinds of redundancy that can lead to these anomalies, it isn't about putting things together because they look alike.

As a first choice, you should let your database engine use its declarative constraints (foreign keys, unique keys, etc.) to protect the integrity of your data.  This saves writing application code to do this and makes your system less buggy in the long run.

This means that you should aim for at least third normal form (3NF) by default and then consider later whether any denormalization is necessary.

From this perspective, your first option is not good, because you are jamming three different kinds of child data into one table.  You can't control referential integrity as cleanly or easily this way.  The only two conventional, practical options you have are 2 and 3.

Choosing between options 2 and 3 depends on the circumstances, and may depend quite a bit on your personal preferences.  Some people would avoid option 2 because they believe all nulls are evil.  Other people would avoid option 3 because they don't like having too many different tables.  This is a "holy war" debate without a definitive, logical answer.

For practical purposes, looking at your three main tables, I would say that they don't look like they have very much in common.  There is a little overlap, but not very much.  Therefore, I would tend towards option 3.  Keep your tables separate so that your code and your data stay clean.

As an aside: You don't show the details of the child table(s).  If these three child tables have an identical structure and you're worried about having application code that is duplicated, consider creating a class that handles the child table structure and inheriting from this class for each of the three child tables.  This will give you code reuse without compromising data integrity.


Worry to delete Read replica RDS I created a read replica from RDS , and I do want to delete it.

I am so worry about affecting the origin RDS which is in PROD .

Is it recommended to take snapshots before deletion of read replica instance ?
Or it is a safe action , known that AWS console shows the screen when I click on delete?

Taking a snapshot is never a bad idea when making changes in a production environment.

That said, it should be safe to delete a replica in RDS as long as you don't have any service connected to it.


Migrate a remote oracle database (11g) into a docker oracle XE database (11g) I am brand new to Oracle and I am trying to migrate an existing VM that has deployment scripts run on it every release to use a docker container instead. Then I can run the release scripts and tag a new container each release (to be used for our integration testing).

Unfortunately it seems like no matter what I try (exp, expdp, generating ddl from datagrip, generating ddl from data studio, expdp in toad) it is impossible to fully copy a remote database into the XE database without a ton of errors in the packages or key constraint errors.

What am I missing?

Here's what I've done so far:

expdp user/pass@XE directory=dp_dir FULL=YES dumpfile=8_17.dmp logfile=8_17.log network_link=XE_BUILD content=ALL TRANSPORT_FULL_CHECK=YES

This creates a dump file that seems like it should be valid.

Then I do this;
impdp importer/importer@XE directory=dp_dir full=Y dumpfile=8_17.dmp logfile=imp_full.log

This also completes with a relatively small number of errors. However I constantly get key constraint errors and broken packages that I don't experience when using the remote database.

I've tried rebuilding all package and things like that, still no luck.

A child table may be exported at some point of time, let's say 12:00. Then as expdp goes on, the parent table may be exported minutes later, let's say at 12:05. Your dump file contains the content of those tables from different points of times.

Yes, if those tables were changing during that 5 minutes, their relation may become inconsistent and you will get key constraint errors. The child table may have references on parent entries that do not exist.

That is why you should use the flashback_scn or flashback_time or consistent=y parameters for expdp. With them, everything will be exported as they existed at 1 specific SCN or point of time. Not using these parameters is a very common mistake.

Then for the packages. No, not even a FULL=Y expdp exports everything. Most of SYS objects are not exported.

Let's say your packages use the SYS.DBMS_LOCK package. By default, users are not able to use that package, because they need to have the EXECUTE privilege granted on it. As the package was not exported, it will not be imported. Since DBMS_LOCK will not be imported, privileges granted on it will not be imported as well. So for this specific package, you need to grant the required privilege manually.

That was just 1 example. Then there is SYS.DBMS_CRYPTO and the others.

The "broken packages" part is just too broad to answer here. You are the one with access to the packages and the code, you are the one who can check the errors.


Why is log(greatest()) so slow? We have some complex query that was very slow. I managed to reduce the query to a simple reproduction. It seems that the combination of greatest and log is the cause, but I don't understand why.

Here is a full sql-fiddle example to run the queries - and you can also View the execution Plans of the queries (press the link at the bottom of the query result on the sql-fiddle page)

So here is the slow query:

select count(value)
from (
         SELECT  log(greatest(1e-9, x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

We just generate a series of 20k numbers and use log(greatest()). This query takes about 1.5 seconds.

I thought that calculating the log may take long, but the following query is also fast (~5ms):

select count(value)
from (
         SELECT  log(x) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

Just as a test I exchanged greatest and log - this is also fast (~5ms):

select count(value)
from (
         SELECT  greatest(1e-9, log(x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;

The QUERY PLANS for all 3 queries are the same:

Aggregate (cost=22.51..22.52 rows=1 width=8)
-> Result (cost=0.00..5.01 rows=1000 width=4)

Can anyone explain why the first query is so slow - and maybe anyone knows a workaround?

More details

slow platforms

I get similar results on all of these (first query is a magnitude slower):

SQL Fiddle uses pg 9.6
my local PC with similar results: Win10 64bit, pg 11.5 running in Docker
remote server: Ubuntu 18.04 64-bit running pg 11.5 in Docker
rextester.com

slow query ~ 3sec
fast query ~ 0.5sec

count

When I change count(value) to count(*) or count(1) (number one) the query is fast

but this does not help me because the production query does not even include a count
anyway, I wonder why there is a difference in this case (there are no null-values in the data)

You're invoking two different log functions here: log(numeric,numeric) and log(double precision), and the first is much slower than the second.

Notice how the functions call differ in the EXPLAIN (ANALYZE, VERBOSE) below,
run with PostgreSQL 11.5 (Linux Ubuntu):

Slow version:

explain (analyze, verbose) select count(value)
from (
         SELECT  log(greatest(1e-9, x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25.02..25.03 rows=1 width=8) (actual time=1174.349..1174.349 rows=1 loops=1)
   Output: count(log('10'::numeric, GREATEST(0.000000001, ((generate_series(1, 20000, 1)))::numeric)))
   ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=4) (actual time=0.004..1.310 rows=20000 loops=1)
         Output: generate_series(1, 20000, 1)
         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.123 ms
 Execution Time: 1174.385 ms

Fast version:

explain (analyze, verbose) select count(value)
from (
         SELECT  log(greatest(1e-9::float, x)) as value
         from (select generate_series(1, 20000, 1) as x) as d
     ) t;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=25.02..25.03 rows=1 width=8) (actual time=6.693..6.693 rows=1 loops=1)
   Output: count(log(GREATEST('1e-09'::double precision, ((generate_series(1, 20000, 1)))::double precision)))
   ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=4) (actual time=0.004..2.561 rows=20000 loops=1)
         Output: generate_series(1, 20000, 1)
         ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
 Planning Time: 0.096 ms
 Execution Time: 6.731 ms

greatest() is not responsible: considering the query with just log(x), if you cast x to numeric it will be as slow with or without greatest().


Is it possible to install just the mongo Shell? I have Docker installed and am running a MongoDB container for my local development on my Mac.  The problem is that I can't connect to said DB easily from CLI.  I have Robo 3T installed, but I would prefer to use the CLI client instead.

Is there a known way to install JUST the mongo shell (command mongo) and not the full DB distribution on OS X?

You can use HomeBrew, see the official MongoDB HomeBrew page.

Just add the tap:

brew tap mongodb/brew

And then after adding the tap from above, you can then install the Mongo shell with:

brew install mongodb-community-shell


Oracle SQL Developer - Database Diff tables & constraints without table data I know I can compare two Oracle schemas by using Tools -> Database Diff in Oracle SQL Develop. It will open the following wizard:

Which will compare everything you've checked.

The problem I'm currently facing is that I only want to compare the Schema (tables, constraints, etc.) itself. Everything except for the data inside the tables. All the data combined was more than 1 GB of INSERT INTO statements (which I couldn't even load into SQL Developer, so I had to use SqlPlus to insert it), and it's currently already running for 1 hour with the Database Diff without result.

Because the database I want to compare it to is empty anyway, I'm only interested in comparing the actual tables, instead of also the data inside the tables.
Is this possible with Oracle SQL Developer?

If not I guess I'll have to use an alternative to check if there are any differences. Either a different tool, or exporting both schemas without data and compare those two files (excluding timestamps and such), or duplicating the existing database with data - empty it - and then compare with Database Diff. There are work-arounds, but I'm just wondering if it's possible to use Database Diff without table-data.

PS: The reason why I want to check if they are the same: a colleague of mine created Liquibase scripts to create the database, and I want to check if everything is correct by comparing it to the database (with data) we already had.

SQL Developer's Database Diff doesn't compare the data in your tables, only the table definitions.

If you check 'Tables' it by default compare ALL tables.

However, if in the proceeding wizard dialog page, you specify one or more tables, it will ONLY compare those tables.

Your output report will look like this - note there is no mention of how rows may differ between any two tables or views.


SQL Server, attaching a database with Filestream, error 2 I'm trying to attach a database sent to me by someone. Unfortunately no backup is available, so I've been sent the raw files themselves.  I'm getting the following error in Management Studio:

Unable to open the physical file
"C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_fs".
Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".

A file activation error occurred.  The physical file name
'C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_fs' may be incorrect.
Diagnose and correct additional errors, and retry the operation.

Could not open new database 'MyDatabase'. CREATE DATABASE is aborted.
File activation failure.
The physical file name
"C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_log.ldf" may be incorrect.
New log file
'C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_log.LDF' was created.

From what I can see, it's struggling with the FILESTREAM folder (which contains around 21Gb of data).  I've made it writeable, am running Management Studio and running as Administrator. The folder I'm trying to attach from is a folder with other working databases in.  I'm at a loss to interpret this error.  The server version is 2008 (the same as that at the other end).

Can anyone assist here?

Edit:  To add to this, what I did was create a new database with the same name, stop SQL Server, copy the old database files over and then start SQL Server again.  The table that contains the FILESTREAM records can't be enumerated in Management Studio, however, giving me the following error: "A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)".  Some reading leads me to believe there's a permissions issue with the filestream files, although as far as I can see, it has SYSTEM, Administrator and local user permissions, so I'm at a loss to see what I should do here.

The command I used was:

CREATE DATABASE [MyDatabase] ON  (FILENAME =
N'C:\Data\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase.mdf'), FILEGROUP
[MyDatabase_fs_Group] CONTAINS FILESTREAM DEFAULT  (NAME =
N'MyDatabase_fs', FILENAME =
N'C:\Data\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase_fs') FOR
ATTACH_REBUILD_LOG GO

Also, looking in the logs, there's an EXCEPTION_ACCESS_VIOLATION when I do a SELECT involving the FILESTREAM field in the table.

2013-12-06 13:58:22.89 Server      Error: 17310, Severity: 20, State:1.
2013-12-06 13:58:22.89 Server      A user request from the session with SPID 52
    generated a fatal exception. SQL Server is terminating
    this session. Contact Product Support Services with the dump produced
    in the log directory.
2013-12-06 13:58:23.83 spid52      Starting up
    database 'ManageTTX'.
2013-12-06 13:59:25.07 spid52      Using 'dbghelp.dll' version '4.0.5'
2013-12-06 13:59:25.07 spid52      ***Stack Dump being sent to
    C:\Data\MSSQL10.Data\MSSQL\LOG\SQLDump0006.txt
2013-12-06 13:59:25.07 spid52      SqlDumpExceptionHandler: Process 52 generated
    fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
    terminating this process. 2013-12-06 13:59:25.07 spid52

It appears you are trying to use the GUI interface to attach the database. In this instance you are going to have to write a T-SQL statement in order to attach it, and let SQL Server know where you put the filestream data as well.

There is a write up here that provides a walk through on doing this, the command the author used in order to attach a database once he had moved the default location of the filestream data for the example database:

USE [master]
GO
CREATE DATABASE [FileStreamDB] ON
( FILENAME = N'C:\FileStreamDB\FileStreamDB.mdf' ),
( FILENAME = N'C:\FileStreamDB\FileStreamDB_log.ldf' ),
FILEGROUP [FileStreamGroup] CONTAINS FILESTREAM DEFAULT
( NAME = N'FileStreamDB_FSData', FILENAME = N'C:\FileStreamDB\FileStreamData' )
FOR ATTACH
GO


Are there limits to SSRS 2014 RDL files going over to SSRS 2008 (or 2012)? Is there any incompatibility with reports developed in SQL Server SSRS 2014, and then exported to .RDL files that will be imported into SQL Server 2008?

SSRS Report Definition Language for SQL Server Reporting Services 2014 cannot be reliably imported into previous versions of SQL Server Reporting Services.

The Report Definition Schema files were modified in 2010; SSRS 2014 uses that version, which supports features that are not supported in the 2008 version of the schema.

The 2010 schema is at: http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition/

The 2008 schema is at: http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition/


Postgres query very slow when using index So I've run into this issue with a slow Postgres query. I've added an index but it doesn't seem to help. It takes 8 minutes to get 100 records from a table with 2822968 records.

Here's the query that I run:

SELECT  "organisations"."id", "organisations"."url"
FROM "organisations"
WHERE "organisations"."url" IS NOT NULL
AND "organisations"."domain" IS NULL
ORDER BY "organisations"."id" ASC
LIMIT 100

And here is the explain analyze

Limit  (cost=17468.48..17468.73 rows=100 width=38) (actual time=517448.061..517449.431 rows=100 loops=1)
  ->  Sort  (cost=17468.48..17482.23 rows=5501 width=38) (actual time=517448.057..517448.791 rows=100 loops=1)
        Sort Key: id
        Sort Method: top-N heapsort  Memory: 38kB
        ->  Bitmap Heap Scan on organisations  (cost=193.31..17258.23 rows=5501 width=38) (actual time=93.370..516988.919 rows=1096205 loops=1)
              Recheck Cond: ((url IS NOT NULL) AND (domain IS NULL))
              Heap Blocks: exact=59205
              ->  Bitmap Index Scan on index_organisations_on_url_and_domain  (cost=0.00..191.93 rows=5501 width=0) (actual time=82.242..82.243 rows=1098765 loops=1)
Planning Time: 0.202 ms
Execution Time: 517453.118 ms

I use rails - here is the partial multi column index from the schema file:

t.index ["url", "domain"], name: "index_organisations_on_url_and_domain", where: "((url IS NOT NULL) AND (domain IS NULL))"

If it helps this is running on Postgres RDS on a small instance. There are no other connections running as I turned off all background jobs, and there are no users connected using the app at the time of running.

I'm not sure how I can get this to return faster.

It seems that the statistics for that index are way off, and PostgreSQL may be faster performing a sequential scan.

Try to

ANALYZE organizations;

and see if that improves matters.

Another idea may be an index on organizations.id (if you don't already have that) with the idea to fetch rows in sort order and discard the ones that don't meet the criterion.

The perfect index would be

CREATE INDEX ON organizations (id)
   WHERE url IS NOT NULL AND domain IS NULL;

You could also include the url column in the index (using the INCLUDE clause in v11) and aim for an index only scan.


KILLED/ROLLBACK query doesn't stop running I've tried to kill a query on ms sql server management studio.
Now it has the status  killed/rollback.

When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I delete this query without restarting the sql server?

I will really appreciate all your answers.

Even if not applicable here, please be wary of runaway queries initiated from an OPENQUERY command.  These run on remote servers, and continue to do so no matter how many times the user tries to cancel from their SSMS console....these pass-thru queries will continue to run on the target server. And if, God forbid, there's a recursive loop in there, it'll keep on running until the kill command is issued from the remote server end


Get the value of the first item of JSON object I know that the first element of an object is kind of a nonsense, but cannot find a different title. If you find a better name I'll be happy to change!

I use pg_memento to log a schema. Some tables in my schema have a stats column (it's business logic, nothing to do with SQL). So here is my request:

SELECT changes, stmt_date::date FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id=el.id)
  JOIN pgmemento.transaction_log t on (el.transaction_id=t.txid)
WHERE r.changes ?& (SELECT array_agg(key)
                    FROM pgmemento.row_log,
                    LATERAL jsonb_each_text(changes)
                    WHERE key like '%_stat'
                    GROUP BY key)
ORDER BY stmt_date::date

Ok great so this what I get:

changes, stmt_date
{"X_stat": "INCOMING"}, 2017-12-13
{"W_stat": "CREATED"}, 2017-12-13

What I would like is grouping "INCOMING", "CREATED", etc. I'm sure this is doable with a CTE, but cannot find a what to get the element of the first key.

What I would like is

type, date, count
INCOMING, 2017-12-13, 1
CREATED, 2017-12-13, 1

The tricks is to count *_stat by by date :-/

EDIT:

Almost there :

WITH status_per_table as (
SELECT changes, stmt_date::date FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id=el.id)
  JOIN pgmemento.transaction_log t on (el.transaction_id=t.txid)
WHERE r.changes ?& (SELECT array_agg(DISTINCT key) FROM pgmemento.row_log, LATERAL jsonb_each_text(changes) WHERE key like '%_stat' GROUP BY key)
ORDER BY stmt_date::date)
SELECT value from status_per_table, LATERAL jsonb_each_text(changes)

The problem is SELECT array_agg(DISTINCT key) FROM pgmemento.row_log, LATERAL jsonb_each_text(changes) WHERE key like '%_stat' GROUP BY key return multiple value

OK so the final query is :

WITH status_per_table AS (
SELECT
  changes,
  stmt_date :: DATE
FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id = el.id)
  JOIN pgmemento.transaction_log t ON (el.transaction_id = t.txid)
WHERE r.changes ?| (SELECT array((SELECT DISTINCT key
                                  FROM pgmemento.row_log, LATERAL jsonb_each_text(changes)
                                  WHERE key LIKE '%_stat'
                                  GROUP BY key)))
) SELECT
    value,
    stmt_date,
    count(value)
  FROM status_per_table, LATERAL jsonb_each_text(changes)
  GROUP BY value, stmt_date
  ORDER BY stmt_date DESC

Don't hesitate to post a  suggestion from a new post name...


How do we implement SQL Server IF EXISTS (SELECT) in MySQL 5.6? We would like to convert the following SQL Server statement:

IF EXISTS(SELECT OptionValue
          FROM Options
          WHERE OptionCatagory = 'General' AND
                OptionName = 'HVRCustomPortNumber')
   UPDATE Option
   SET OptionValue = @custportnum
   WHERE OptionCatagory = 'General' AND
         OptionName ='HVRCustomPortNumber'
ELSE
   INSERT INTO Options (OptionCatagory, OptionName, OptionValue)
   Values
   ('General', 'HVRCustomPortNumber, @custportnum);

to MySQL version 5.6 so that it runs correctly using C# MySQL Connector .NET on Ubuntu Linux 15.10.

I tried googling how to do this and could not find any "hits" that passed the MySQL GUI Query Analyzer.

Assuming Options has a unique key on (OptionCatagory, OptionName), you can use a fake UPSERT/MERGE. More details can be found on MySQL's documentation:
INSERT ... ON DUPLICATE KEY UPDATE syntax.

INSERT INTO Options
(OptionCatagory, OptionName, OptionValue)
VALUES( 'General', 'HVRCustomPortNumber', @custportnum)
ON DUPLICATE KEY UPDATE
OptionValue = @custportnum;

The last part can also be written:

ON DUPLICATE KEY UPDATE
OptionValue = VALUES( OptionValue );


Select Record With Higher Value I have the following table:

+-----+---------+-------+
| ID  | Account | Score |
+-----+---------+-------+
|   1 |       1 |  500  |
|   2 |       1 |  200  |
|   3 |       2 |  150  |
|   4 |       2 |  200  |
|   5 |       3 |  100  |
|   6 |       3 |  100  |
|   7 |       4 |  300  |
+-----+---------+-------+

What I want my query to do is check the table to see if any accounts match.  If they do, then check the Score and see which is higher.  Output the record with the higher score.  If the scores are the same, output the ID that is first.  If there are no accounts that match, just output the record.

The output would look like this:

+-----+---------+-------+
| ID  | Account | Score |
+-----+---------+-------+
|   1 |       1 |  500  |
|   4 |       2 |  200  |
|   5 |       3 |  100  |
|   7 |       4 |  300  |
+-----+---------+-------+

Any ideas?

Thanks

There is at least two ways to do this. One with a derived table, and one with a CTE. These two queries assume there aren't two accounts with the same score. If that's a possibility, we can use another method to get the last ID, or pick a random one.

;select
    t.ID
    ,t2.Account
    ,t2.Score
from
    YourTable t
inner join
    (select Account, Score = max(Score)
    from YourTable
    group by Account) t2 on t2.Account = t.Account and t2.Score = t.Score;

;with cte as(
    select RN = row_number() over (partition by Account order by Score desc, id)
            *,
    from YourTable)

select
    ID,
    Account,
    Score
from cte
where RN = 1;


Why does CREATE INDEX ... WITH ONLINE=ON block access to the table over a period of minutes? I have an existing table:

CREATE TABLE dbo.ProofDetails
(
    ProofDetailsID int NOT NULL
        CONSTRAINT PK_ProofDetails
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , ProofID int NULL
    , IDShownToUser int NULL
    , UserViewedDetails bit NOT NULL
        CONSTRAINT DF_ProofDetails_UserViewedDetails
        DEFAULT ((0))
);

This table has 150,000,000 rows.  The system is in operation 24x7x365, so there are no regularly occurring maintenance windows.

I want to add an index to the table, and with the Enterprise edition of SQL Server, I should be able to do that without blocking write access to the table.  The command I used was:

CREATE INDEX IX_ProofDetails_ProofID_Etc
ON dbo.ProofDetails (ProofID, IDShownToUser)
INCLUDE (UserViewedDetails)
WITH (ONLINE=ON
    , ALLOW_ROW_LOCKS=ON
    , ALLOW_PAGE_LOCKS=ON
    , FILLFACTOR=100
    , MAXDOP=4
);

I executed the statement by itself in SSMS, by pressing F5. It ran for over a minute, then began blocking other sessions.  I then immediately cancelled the CREATE INDEX command since I cannot block other sessions.

During the first minute, nothing was blocking my CREATE INDEX command, sys.dm_exec_requests showed the process with a wait type of CXPACKET - of course.  I don't think that is a bad thing since the operation was parallelized.

I didn't have a lot of time to inspect the output of sys.dm_exec_requests.  There was only a single row returned from the query WHERE session_id = xxx.  The blocked sessions were attempting to insert rows into the target table.

I don't know how long the locks lasted, except to say I cancelled the execution of the statement around 2 minutes after it started. Blocks were occurring for around a minute at that point.

Am I misunderstanding the implementation of WITH (ONLINE=ON)?  Or is there something else I need to be aware of?

The server is a fairly beefy machine, with 2 quad-core Xeon E5-2643 3.3Ghz processors, 192GB RAM, and SAN storage capable of 5,000+ iops.  CPU is typically below 20%, RAM is 93% utilized, mostly by SQL Server.  There is nothing else running on the box, just Windows Server 2012, and SQL Server 2012.

When creating an index with online = on, the create index process will not block when creating the index object itself, but when it comes to near the end of the process, it will acquire a schema modification lock* for a period in order to actually add the index to the table, this lock type will block all outside operations until the lock is released, which could account for your blocking issues.

* An Sch-M lock is not required for online build of a new nonclustered index, though it is required in all other cases. A new nonclustered index requires only a table-level shared lock during the final phase, same as was needed during the preparation phase.

See this White Paper for details:

Online Indexing Operations in SQL Server 2005

As suggested by Mushtaq Mohammed in a comment on the question, also see:

Unicorns, rainbows, and online index operations by Paul Randal


What would be a better many-to-many solution in MongoDB of the relationship would number 1000000? In mongoDB Many-to-Many Relationship Data Modeling by Mark Starkman, he tries to see how many-to-many relationship can be implemented in the MongoDB database.

He suggests the following code will help us implement many-to-many relationship can be implemented in the MongoDB database:

An example of person's collections:

db.person.insert({
  "_id": ObjectId("4e54ed9f48dc5922c0094a43"),
  "firstName": "Joe",
  "lastName": "Mongo",
  "groups": [
    ObjectId("4e54ed9f48dc5922c0094a42"),
    ObjectId("4e54ed9f48dc5922c0094a41")
  ]
});

db.person.insert({
  "_id": ObjectId("4e54ed9f48dc5922c0094a40"),
  "firstName": "Sally",
  "lastName": "Mongo",
  "groups": [
    ObjectId("4e54ed9f48dc5922c0094a42")
  ]
});

An example of Group collection:

db.groups.insert({
  "_id": ObjectId("4e54ed9f48dc5922c0094a42"),
  "groupName": "mongoDB User",
  "persons": [
    ObjectId("4e54ed9f48dc5922c0094a43"),
    ObjectId("4e54ed9f48dc5922c0094a40")
  ]
});

db.groups.insert({
  "_id": ObjectId("4e54ed9f48dc5922c0094a41"),
  "groupName": "mongoDB Administrator",
  "persons": [
    ObjectId("4e54ed9f48dc5922c0094a43")
  ]
});

The aforementioned solution is alright if a person can belong to groups on the 100s, and vice versa for groups.  However, Array of ObjectIds would Not be practical if a person can belong to 1000000 groups, and vice versa for groups.

What would be a better many-to-many solution in MongoDB of the relationship would number 1000000?

You can add a third collection, named person_group, being described in that blog picture.

db.person_groups.insert({
  "_id": ObjectId("5e54ed9f48dc5922c0094a42"),
  "groupId": ObjectId("4e54ed9f48dc5922c0094a43"),
  "personId": ObjectId("4e54ed9f48dc5922c0094a44")
});


Why does MySQL allow HAVING to use SELECT aliases? In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Following this list it's easy to see why you can't have SELECT aliases in a WHERE clause, because the alias hasn't been created yet. T-SQL (SQL Server) follows this strictly and you can't use SELECT aliases until you've passed SELECT.

But in MySQL it's possible to use SELECT aliases in the HAVING clause even though it should (logically) be processed before the SELECT clause. How can this be possible?

To give an example:

SELECT YEAR(orderdate), COUNT(*) as Amount
FROM Sales.Orders
GROUP BY YEAR(orderdate)
HAVING Amount>1;

The statement is invalid in T-SQL (because HAVING is referring to the SELECT alias Amount)...

Msg 207, Level 16, State 1, Line 5
Invalid column name 'Amount'.

...but works just fine in MySQL.

Based upon this, I'm wondering:

Is MySQL taking a shortcut in the SQL rules to help the user? Maybe using some kind of pre-analysis?
Or is MySQL using a different conceptual interpretation order than the one I though all RDBMS were following?

Well when you have a question of this sort the best source of information IMHO is MySQL documentation. Now to the point. This is the behavior of MySql extension to GROUP BY which is enabled by default.

  MySQL Extensions to GROUP
  BY
  MySQL extends this behavior to permit the use of an alias in the
  HAVING clause for the aggregated column

If you want standard behavior you can disable this extension with sql_mode ONLY_FULL_GROUP_BY

SET [SESSION | GLOBAL] sql_mode = ONLY_FULL_GROUP_BY;

If you try to execute the above-mentioned query in ONLY_FULL_GROUP_BY sql_mode you'll get the following error message:

  Non-grouping field 'Amount' is used in HAVING clause: SELECT
  YEAR(orderdate), COUNT(*) as Amount FROM Orders GROUP BY
  YEAR(orderdate) HAVING Amount > 1

Here is SQLFiddle demo

Therefore it's up to you how to configure and use your instance of MySQL.


Security access to a table I have a database and table dbo.Test_Table, with list of Users under Security -> Users. those users have access to all the tables in my database.

I would like to know how do I assign certain users to specific tables (e.g dbo.Test_Table) within the database, that only those Users can view those tables within my database.

In SQL Server 2008 you can control user access to a table and fields also.All tables and fields are available in the security system, and access can be set up individually for each user group working within a company or domain without affecting other user groups. Table and field access is configured when you set security keys (Administration > Setup > Security > User group permissions on the Permissions tab).
For more information visit following link in Microsoft site:
http://technet.microsoft.com/en-us/library/aa834466(v=ax.50).aspx


Idempotent user and database creation script with MySQL I have written a shell script which creates a database and user on a MySQL server. The user and database are to be used by a web application and generally this task only be performed once per website installation, but sometimes, (e.g. testing the script) the script needs to be run many times.
Currently the script assumes the user always exists and just pre-emptively deletes, i.e.

DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';

Ive also tried drop user ${web_app_sql_user}@localhost; but then that fails if the user doesn't exist if you can believe that! lol

Script

#!/bin/bash
echo -e "Creating a SQL user and database for the webapp"

read -s -p "enter SQL root user password: " password

cat <<- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;

    -- Deal with possibility user already exists!
    DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';
    -- drop user ${web_app_sql_user}@localhost;
    FLUSH PRIVILEGES;

    CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    -- CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    GRANT ALL ON ${web_app_database}.* TO '${web_app_sql_user}'@'localhost';

    GRANT USAGE ON * . * TO  '${web_app_sql_user}'@'localhost' IDENTIFIED BY  '${web_app_sql_user_password}' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';

    FLUSH PRIVILEGES;
EOF

Result when running the script

--------------
DROP DATABASE IF EXISTS `web_app_database`
--------------

--------------
CREATE DATABASE `web_app_database` CHARACTER SET utf8 COLLATE utf8_general_ci
--------------

--------------
DELETE FROM mysql.user WHERE User = 'web_app_user'
--------------

--------------
FLUSH PRIVILEGES
--------------

--------------
CREATE USER 'web_app_user'@localhost IDENTIFIED BY ''
--------------

ERROR 1396 (HY000) at line 17: Operation CREATE USER failed for 'web_app_user'@'localhost'

Is it possible for MySQL to be able to handle these situations?

MySQL Server version: 5.5.46-0ubuntu0.14.04.2 (Ubuntu)

Updated script based on michael-sqlbot's answer

read -s -p "enter SQL root user password: " password
cat <<- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';
    FLUSH PRIVILEGES;
EOF

The task is too complex to safely (and simply) do in a shell script.  Get into some programming language (Perl, etc) and write enough code to check for various end cases, syntax errors, punctuation in user names, etc, etc.  You can also check for the existence of the user and/or database and exit or say "Dup name" or proceed with the delete, or whatever.

And totally disallow 'root' and 'mysql' and 'information_schema' and 'performance_schema'!

The CREATE USER + 3 GRANTs can be simplified to just

GRANT ALL PRIVILEGES ON db.* TO user@localhost IDENTIFIED BY '...' WITH ...


Migrating and normalizing data We are rewriting a large Django application which uses a MySQL database, and the database schema needs to undergo major modifications. We're also porting the app to use PostgreSQL, for a couple of good reasons.

I've written a small script that allows me to dump the old MySQL tables into a PostgreSQL database, prepending their name with oldapp_*

We are starting to write some data migration scripts which are basically:

insert into foobar(c1, c2, ..., cn)
select c1, c2, ..., cn from oldapp_foobar where ...

Now, this app uses surrogate keys extensively (Django doesn't allow composite primary keys and encourages the use of surrogates heavily). For the first migration scripts I've tentatively skipped the surrogate keys from the insert/select, and thus PostgreSQL generates new ids automatically.

This works, of course, but when migrating foreign keys this is not a good idea. I'm pondering several approaches, but I'd appreciate some input here.

Just keep the keys. You'll be able for example to locate data in old database much more easily.

But remember to update sequences associated with your keys after migration so they will have a value bigger than the biggest key in database.


Can I create a Azure SQL Server (server) with a different collation? I want for my application the same collation at all database levels (server, database, columns): Latin1_General_CI_AS
This is recommanded by Dan Guzman to have constistent collations and get the collation configuration right from the start.

The default collation for a Azure SQL Server (server) is SQL_Latin1_General_CP1_CI_AS. I would like to change this to Latin1_General_CI_AS. Or create a new Azure Sql Server (server) with the collation Latin1_General_CI_AS. For on premise SQL Servers it is possible.

In the SQL Azure Collation Documentation from 2010 is stated that  ...the server and database collations cannot be configured in SQL Azure... Besides this, I didn't found something useful.

I would be totally happy if I could create a new Azure SQL Server (server) with a collation Latin1_General_CI_AS.

Is it somehow possible or exists a workaround?
I really appreciate any help.

The collation for user databases can be specified for SQL Azure Database user databases nowadays (e.g. CREATE DATABASE YourDatabase COLLATE Latin1_General_CI_AS;).

EDIT:

The logical Azure SQL Server collation is fixed at SQL_Latin1_General_CP1_CI_AS and is not configurable (as of this writing). However, this collation is only used for system objects in master, tempdb, and user databases. It does not affect the collation of user objects in tempdb, which is the collation of the current Azure SQL Database context (not logical SQL Server) unless otherwise specified. This behavior differs from on-prem and IaaS SQL Server instances where the instance collation also determines the tempdb default database collation used for both system and user objects, therefore having a more significant impact.

With this in mind, you might think of system and user collations orthogonally in the Azure SQL Database world because you won't run into collation conflict errors in the user space as long as you use the same collation consistently. Also, if you use either Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS as your user database collation, you'll still get the same sorting and comparison behavior as system object names because Windows sorting and comparison rules are used for Unicode data and system object names are stored as nvarchar(128) (a.k.a sysname).


distribution.dbo.MSdistribution_history comments explanation The comment column of distributor.dbo.MSdistribution_history contains comments such as

<stats state="2" fetch="11554" wait="214007" cmds="17898546" callstogetreplcmds="106880">
    <sincelaststats elapsedtime="300" fetch="8" wait="291" cmds="1948689" cmdspersec="6495.000000" />
</stats>

The state value translates to

STATE   Indication
1       Normal Log
2       Reader Thread has to WAIT for Writer
3       Writer Thread has to WAIT for Reader

Is the comment telling me that the reader thread is waiting for the writer thread but has read 1948689 cmds since the last stats entry. Or is it telling me that the reader thread is waiting for the writer thread and the writer thread has written 1948689 cmds since the last stats entry?

http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/0b8bac44-da87-4115-90f6-b8bf1e1d0bd8

Both cmd values relate to the number of commands written. Since last stats - cmds are all cmds written since the last entry and stats- cmd are an accumulation of all cmds written since the last comment with a state of 1.


MySQL5.7 (Windows) how to install from zip file without the Data Directory? This has probably been asked before, but I am struggling to find the answer.

Historically I have always installed MySQL (on Windows) from a template based on the mysql-xxx-win64.zip file.
I would basically

1) download the zip file,
2) copy the contents to the new location,
3) add in my template ini file,
5) add in my template mysql directory (with default users)
4) install a Windows Service,
5) start MySQL

This has worked fine for years (5.5 and earlier). But now I am starting to test 5.7, and I find that the zip file no longer contains a data directory?
If I try and create one by copying over from an existing database, I get an error.

Looking at the documentation I see that I am now supposed to initialize the data directory. But when I try this I get an error:

c:\MySQL\bin\mysqld.exe --initialize --user=mysql
mysqld: Can't change dir to 'C:\MySQL\DATA\' (Errcode: 2 - No such file or directory)

If I create the Directory it seems to work, but there is still no MySQL directory, so when I run mysql_upgrade or try and (start service then) login, it fails.

2016-05-16T09:03:16.222540Z 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist.

I found this post which says the password shouold be in the error log, but it isn't.

So, how am I supposed to install MySQL if they don't give us the data directory (and therefore mysql user etc. tables) anymore?

(and before someone suggests using the msi file - that's not an option as they are blocked on our network, and require me to jump through numerous hoops to even download it, let alone run it).

If I do: Copy MySQL Directory >> initialize >> install service >> start service, it seems to be ok, until I look in the error log and see that virtually every table has the wrong structure. But I can't run mysql_upgrade as I have no password, and mysql@localhost is access denied.

UPDATE: 2016-05-18

So I got hold of the MSI file, and (tried) to install using that. Eventually, I managed to get a copy of MySQL 5.7 installed and running. So now I need to install a second copy. The MSI installer fails at this point as I 'already have a copy installed', with no option that I can see to install another copy.

So I copied the MySQL Directory to a new folder, removed the DATA directory, updated the ini file, and ran c:\Mysql\...\mysqld.exe --initialize.

That re-created the DATA directory for me.
I then set it up as a Service c:\Mysql\....\mysqld.exe --install MySQL57-2 --defaults-file=c:\MySQL\...\my.ini

I got the temporary password from the error log, and tried to log in, only to be told ERROR 1130 (HY000): Host '::1' is not allowed to connect to this MySQL server.

So I stopped the service, started Mysqld using --skip-grant-tables, logged in, added a new user, logged out, restarted the service. But still can't log in.

If it's going to be this much hassle every time I need to install a new Database, I think I'll just stick to 5.5.


What's wrong in this SQL Query? I am trying to update a column value I would like to know what is wrong in below stored procedure as I am not returning any error but at same time I am unable to update my Watermarkvalue column. it returns NULL.

ALTER PROCEDURE [dbo].[usp_UpdateWatermark]
 @tableName nvarchar(500)

AS
BEGIN

 DECLARE
 /* ============= Variables ============= */
  @watermarkValue  nvarchar(MAX)

  DECLARE
 /* ============= Variables ============= */
  @tabName  nvarchar(MAX)

  Set @tabName ='SELECT '+ @watermarkValue +'= MAX([LOAD_DATE]) FROM '+ @tableName

 Execute sp_executesql @tabName

 UPDATE dbo.WatermarkTable
 SET  WatermarkValue  = @watermarkValue
 WHERE TableName = @tableName
END

Here my WatermarkValue always return NULL. The watermark table has columns as TableName and WatermarkValue and LOAD_DATE is a column in another table in database

You never set your variable @Watermarkvalue to anything, so the value of the variable is NULL.

So this line:

Set @tabName ='SELECT '+ @watermarkValue +'= MAX([LOAD_DATE]) FROM '+ @tableName

Will always be NULL.

You want to fill your variable, so think you need something like this: (change to correct datatype)

ALTER PROCEDURE [dbo].[usp_UpdateWatermark]
 @tableName nvarchar(500)

AS
BEGIN

 DECLARE
 /* ============= Variables ============= */
  @watermarkValue  nvarchar(MAX)

  DECLARE
 /* ============= Variables ============= */
  @tabName  nvarchar(MAX)

  DECLARE @ParmDefinition nvarchar(500);
  SET @ParmDefinition = N'@watermarkValue int OUTPUT';

  SELECT @tabName = N'SELECT @watermarkValue = MAX(ID) FROM ' + @tablename;

 Execute sp_executesql @tabName,@ParmDefinition,@watermarkValue=@watermarkValue OUTPUT;

 UPDATE dbo.WatermarkTable
 SET  WatermarkValue  = @watermarkValue
 WHERE TableName = @tableName
END


Advice on choosing a primary key I will have to do this process in EXCEL first before it is made into a database. I will have many files, but there is about 7 - 9 files that work all together as one e.g.

X192AD1-test.docx
X192AD1-test.avi
X192AD1-test.mpg
X192AD1-test-small.jpg
X192AD1-test-preview.jpg
X192AD1-test-pdf

All the above works as ONE file.

I do not want to use a numbering system, e.g. 00001, 00002, 00003 because people can determine the age of the files, 00001 is older than 000032.

Can anyone give me some advice regarding this because of course I cannot repeat the primary key and I am not sure how to approach this.

You need 2 tables: one called say, Fileset and the other FilesetFile

Fileset contains information about the group: source, owner, who and when etc
FilesetFile contains a foreign key to Fileset.

Example:

Fileset

FilesetID, PK
FilesetOwner
FilesetAddedBy
...

FilesetFile

FilesetID, PK, FK
FileSuffix, PK
FileContent (BLOB column say)
...

You can then create a computed column "FileName" that concatenates FilesetID and FileSuffix to generate the pattern you need above

Trying this with one table will end in failure.


How to remove read-only routing URL How can I remove the Read-Only Routing URL in an availability group.

I have set the Read-Only routing URL and I'm trying to get it back to how it was before my test and I can't find a way to remove those URL.

I tried with :
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N''));
but SQL does not allow an empty value here.

Thank you for you precious help.

There is no method for deleting a read only routing URL for a given replica. Your only options are to change the URL to something else (just putting a different URL in the READ_ONLY_ROUTING_URL = N'tcp://newRoutingString' or dropping the replica entirely from the AG and putting it back in again (less than ideal I know).

If you adjust the replica to SECONDARY_ROLE(ALLOW_CONNECTIONS = NONE) then you can at least prevent any potential attempts to hit that URL.


Restrict server login by user AND machine (within or not within a domain): is it possible? I'm trying to setup a SQL Server 2008 R2 instance, restricting access to my database so only specific users on specific machines can login there.

Let's say, we have 3 devs: John, Jill and Steve, with 3 machines: JohnPC, JillPC and StevePC.
John and Jill use their machines inside a domain, but Steve doesn't.
So, the machines "full names" are: MYDOMAIN\JohnPC, MYDOMAIN\JillPC and StevePC, since Steve isn't in MYDOMAIN, but in windows default "WorkGroup".

I know that I can create accounts to restrict access from a machine, as in [MYDOMAIN\JohnPC$].

But in my case, I don't want to allow Jill to login from JohnPC.

I'm using mixed-mode login to server, so I have the 'sa' account enabled (and it's supposed to access only within the server's localhost environment).

I've read this article that shows how to create an account for a computer within a domain, but that doesn't apply to my case [Edit: because it doesn't tell how to restrict user to each computer, and it doesn't tell how to create an account for a computer with no domain]. Searched google about it, and can't find any answer that could remotely fit what I'm trying to do.

So, is this possible to achieve? And if it is, how can I do it?

You can create a server level trigger for LOGON and in there you can check the user, domain and the client host name using sys.dm_exec_sessions to determine whether user is allowed or not. (We can have the permitted combinations in a separate table if require)

I have done a similar thing to stop users accessing the SQL Server databases using SSMS with any SQL Logins. Only domain users are allowed via SSMS.

Hope this helps you.


Get the date interval from table rows, based on a start stop column I have a table returning the following result set:

  mydate    | startstop
------------+----------
 2018-02-07 | start
 2018-02-14 | stop
 2017-02-06 | start
 2017-02-12 | stop
 2016-02-05 | start
 2016-02-12 | stop

I need to know if my current date is in one of the intervals, for example if I query the table from the current date '2017-02-07', I need to get 'TRUE'.

I know it looks like that simple but it is not being simple!

The best that I found is this:

       select true
        where '2017-02-06'>=
         (select mydate from mytable where starstop='start' order by id limit 1)
        and '2017-02-06' <=
         (select mydate from mytable where startstop='stop' order by id limit 1);

It works returning TRUE if the date is in one of each intervals, but only if the table does not have intervals in the future, and as you can see my table has intervals in the future.

Note: The database management system is PostgreSQL 9.1

It's actually very simple. Start from the wanted date and descend the dates until you find the first row. If it's 'start' then you are inside an interval. If it's 'stop', you are not:

select
  ( select startstop
    from mytable
    where mydate = '2017-02-07' and startstop = 'start'
       or mydate < '2017-02-07'
    order by mydate desc
    limit 1
  ) = 'start'
  as result ;

The complicated WHERE is for dealing with the inclusive date ranges. If inclusive-exclusive were used, the condition would be simper.

A similar way - perhaps a bit more clear - would be:

with ct as
  ( select mydate, startstop
    from mytable
    where mydate <= '2017-02-07'
    order by mydate desc
    limit 1
  )
select
       ('start') = (select startstop from ct)
    or ('2017-02-07', 'stop') = (table ct)
  as result ;


Oracle, changed NLS_LANGUAGE and is now invalid and can't fix I'm using oracle 12c, and changed the NLS_LANGUAGE variable to a invalid text using the manager at localhost:5500/em, after reboot the oracle db doesn't get up and Database Configuration Assistant errors out with :

ORA-12700:invalid NLS parameter value (nls_language)

I tried looking for init.ora but there's nothing about language in it.

I looked for spfile.ora but no file found.

I found file SPFILEORCL.ORA, inside it I have *.nls_language='invalidvalueIput'

But if I change it to *.nls_language='AMERICAN' and reboot it just says the file is invalid, so how am I supposed to fix this value since the EM doesn't come up anymore and nothing seems to fix the file ?

You are not supposed to edit the spfile manually as it is in binary format and also stores a checksum. Start sqlplus, create a text version of the parameterfile:

sqlplus / as sysdba
create pfile from spfile;

Edit the initORCL.ora, then recreate the spfile from it:

create spfile from pfile;


PostgreSQL 9.3: Aggregate collection of JSON arrays I have a table inventory with a value column that contains JSON strings, with the following data structure:

{
  "category": {
    "item": ["price", "quantity"]
  },
  "widgets": {
    "foo": [300, 15]
    "bar": [500, 5],
    "baz": [400, 10]
  },
  ...
}

To query for a particular item, I use the following parameterized SQL:

SELECT
  (value::JSON->$1->$2->>0)::INT AS price
, (value::JSON->$1->$2->>1)::INT AS quantity
FROM inventory
WHERE as_of_date = $3::DATE

...where $1 is the category, $2 is the item and $3 is the date.

Now I need to sum() all of the prices and quantities for each item in a category, and I'm not sure how to go about it. I've tried using JSON functions like json_each, but can't manage to get the nth array element from all JSON field values.

You can use json_each and LEFT JOIN LATERAL for this purpose:

WITH inventory AS (
  SELECT '{
  "category": {
    "item": ["price", "quantity"]
  },
  "widgets": {
    "foo": [300, 15],
    "bar": [500, 5],
    "baz": [400, 10]
  }
}'::text AS value
  )
SELECT
  v.key,
  (v.value->>0)::INT AS price
, (v.value->>1)::INT AS quantity
FROM inventory i
LEFT JOIN LATERAL json_each(i.value::JSON->$1) v ON (true)
WHERE as_of_date = $3::DATE;

You can then filter, group by and sum as you need.


Is there an easy way to update a mysqldump file from version 9.11 to 10.11? We have a client that has had their servers for soo long, they were still running MySQL 4.0.24.
Now that they are refreshing their servers, we took a mysqldump of their current database to put into the new servers and are getting errors like:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'call' at line 1
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'call
(
path varchar(255) NOT NULL default '',
call_num int(12) unsigned ' at line 1

The section of the dump file it is complaining about is:

--
-- Table structure for table `call`
--

DROP TABLE IF EXISTS call;
CREATE TABLE call (
  path varchar(255) NOT NULL default '',
  call_num int(12) unsigned NOT NULL auto_increment,
  date datetime NOT NULL default '0000-00-00 00:00:00',
  idf varchar(4) NOT NULL default '404',
  location varchar(255) default NULL,
  who_req varchar(8) default NULL,
  who_res varchar(8) default NULL,
  PRIMARY KEY  (path,call_num)
) ENGINE=MyISAM;

--
-- Dumping data for table `call`
--

So, does anyone have any ideas on this one?

Well,

I figured out that if you use the backticks ("`") around the table name (like mysqldump uses in the comment-separator sections, e.g. `call`), it works as expected.

So, the above should look like:

--
-- Table structure for table `call`
--

DROP TABLE IF EXISTS `call`;
CREATE TABLE `call` (
  path varchar(255) NOT NULL default '',
  call_num int(12) unsigned NOT NULL auto_increment,
  date datetime NOT NULL default '0000-00-00 00:00:00',
  idf varchar(4) NOT NULL default '404',
  location varchar(255) default NULL,
  who_req varchar(8) default NULL,
  who_res varchar(8) default NULL,
  PRIMARY KEY  (path,call_num)
) ENGINE=MyISAM;

--
-- Dumping data for table `call`
--

Hope that helps.


SQL Server 2008: High CPU historical queries We have a SQL Server with 15 databases on it. This is used by many users in each database simultaneously. Recently we started getting high CPU alerts from this server. When the alert comes, I can trace to the exact user and query.

We cannot turn on SQL Server Profiler on this server. I like to know if we can get information about historical expensive queries run in any of the databases (causing high CPU) using any script. for example - I need most expensive queries in last 4 hrs along with database name and username.

No, SQL Server does not track individual occurrences of queries, for the same reason you don't want to run profiler: tracing constantly can have a substantial performance impact on the server, and the last thing Microsoft wants to do is to turn on high-cost diagnostics most people will rarely or never use. Profiler is the absolute last tool you want to use for this anyway, as it is the worst offender. A server-side trace is much lighter weight, but you should also look into extended events and perhaps even 3rd party monitoring tools - I work for a vendor of one such tool and would be happy to give you guidance there.

In the meantime, you can track expensive queries in aggregate using DMVs like sys.dm_exec_procedure_stats and sys.dm_exec_query_stats. Here's a procedure from Glenn Berry's Diagnostic Queries that gets the top 25 CPU-consuming stored procedures for a specific database:

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime],
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p WITH (NOLOCK)
INNER JOIN sys.dm_exec_procedure_stats AS qs WITH (NOLOCK)
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

You can do a similar thing with sys.dm_exec_query_stats if you have a lot of ad hoc queries. For example:

SELECT TOP(25) SUBSTRING(t.text, (qs.statement_start_offset/2)+1,
    ((CASE qs.statement_end_offset
      WHEN -1 THEN DATALENGTH(t.text)
     ELSE qs.statement_end_offset
     END - qs.statement_start_offset)/2) + 1) AS statement_text,
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count,
ISNULL(qs.execution_count/DATEDIFF(Second,qs.creation_time,GETDATE()),0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY AvgWorkerTime DESC OPTION (RECOMPILE);

In fact a similar example can be found in the documentation.

Note that these DMVs do not persist data beyond a service restart and some other events, so you might not have the history you expect.


Two FOREIGN KEY constraints with SET NULL targeting the same PRIMARY table I feel like I'm missing something obvious here...

I have a situation with a primary table (let's call it People) and a secondary table (let's call it Groups) with two different roles that people might fill (Secretary & Treasurer).  Both those columns are int and FKs back to People.ID.

CREATE TABLE People(
ID int NOT NULL,
FullName varchar(50) NOT NULL,
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED (ID ASC))

CREATE TABLE Groups(
ID int NOT NULL,
Treasurer int NULL,
Secretary int NULL,
CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (ID ASC))

So far so good.  But the roles are optional (nullable) so I want to be able to delete a Person and have the FK values in Groups set to NULL.

ALTER TABLE dbo.Groups ADD CONSTRAINT FK_Groups_Treasurer FOREIGN KEY (Treasurer) REFERENCES dbo.People (ID) ON DELETE SET NULL

ALTER TABLE dbo.Groups ADD CONSTRAINT FK_Groups_Secretary FOREIGN KEY (Secretary) REFERENCES dbo.People (ID) ON DELETE SET NULL

Now we have a problem:

  Introducing FOREIGN KEY constraint 'FK_Groups_Secretary' on table 'Groups' may cause cycles or multiple cascade paths.

What is the problem here, and why does SQL Server care?  What is the possible cascade conflict?  If I delete a Person, set NULL the FK values in any related tables.  This would be no problem against two different tables, each with a FK.  But having two FK values in the same table seems to be prohibited.

Am I missing something?  Is this somehow bad design?  What's the correct way to deal with this situation?  It seems like a common use-case.

It would seem you have cascading deletes on and you'll end up deleting someone who could theoretically be referenced in another group as a treasurer or secretary.

The group table is violating the 1st normal form. This is akin to having address1, address2 in a table.

You need a third table GroupPersonRole that holds groupid, the person id and the roleid.

Define your person in one table. Define your group in another table. And define your role in another table. Then in a fourth, say GroupPersonRole, you can combine them and draw your FKs as needed.

What if someone wants to add more group roles? You don't want to be modifying schema every time someone gets a new idea.


SSIS Memory allocation Is SSIS subject to the maximum memory limitations for SQL Server 2012 Standard edition?

If I have a server with 128GB RAM and I allocate 64GB of RAM to SQL Server does SSIS utilise this memory or does it utilise the memory that is available to the OS?

I am assuming it utilises the remaining memory left to the OS since there is no settings for memory consumption and it runs as a separate service.

If anyone has any information on the available memory that should be left for SSIS to consume I would appreciate the help.

SSIS runs outside of SQL Server's memory space so it's going to be contending with the memory allocated to the OS.

How much memory you allocate to SSIS is entirely dependent on how you define your SSIS packages. To go fast, which is what SSIS is designed for, it's going to push as much data into memory so that operations can be performed on it and slap it into the target system. You might only have 10GB of data from your source system but in theory you could consume more than 10GB of memory by using semi or fully blocking components as they will result in memory copies and such.


Groups and Members database: Which of these two options is a good design? Each group can have either 1 or 2 members, no more, no less. Every select of group data will need to retrieve the member data. The eventual state of each group will tend towards 2 members (full group).

Group
group_id | ...

Member
group_id | user_id

vs

Group
group_id | user_id_1 | user_id_2 | ...

The first one is probably the best option.

Members of a group is a kind of data that could increase some day and if you keep the first option you will only need to add a new row in the Members table.
Also you can remove or add users from a group without messing with any possible Groups indexes and without needing to update which is a more expensive action.


need information on a deleted SSRS report In SSRS, once a report is deleted, the ItemPath no longer appears in the ExecutionLog because the Catalog no longer has the report information.  I can track down the ReportID, but I can't find anywhere with the history of the report name.

Is there anywhere I could find the name and path of the deleted report?  If not, any suggestions on how to best stash the information moving forward?

If you need to get the information restore a backup taken prior to removing the report into a different environment and get it from there.


Exporting scheduled jobs in Oracle This is probably very simple but i already exported all but the scheduled jobs i created to subsequent schemas.

How can I create a sql script of the scheduled jobs I created in Oracle? I know in SQL developer, you can use the Database Export wizard to export DDLs and data but you can't use it to export prior scheduled jobs.

I am trying not to have to recreate all my jobs for each environment as I go. Can you please help!

Assuming that we are talking about dbms_scheduler jobs (rather than, say, jobs scheduled using the dbms_job package), you can use the dbms_metadata package to generate the DDL. Specifically, the dbms_metadata.get_ddl function.  Something like

SELECT dbms_metadata.get_ddl( 'PROCOBJ', <<job name>>, <<schema>> )
  FROM dual

should work, for example.

Here is a list of the object types that dbms_metadata.get_ddl supports.

Here is an example of using the PROCOBJ trick to get dbms_metadata to script out a job.


Slow queries on indexed columns (large datasets) I've got some large MySQL (5.5.60) tables where queries take 60 seconds+ to complete.

product_base (1.5GB, 26 million rows)

CREATE TABLE `product_base` (
  `EAN13` varchar(13) NOT NULL DEFAULT '',
  `company_tk` int(11) DEFAULT NULL,
  `category` int(11) DEFAULT NULL,
  `modified` varchar(30) DEFAULT NULL,
  KEY `EAN13` (`EAN13`),
  KEY `company_tk` (`company_tk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

attribute (11.8GB, 126 million rows)

CREATE TABLE `attribute` (
  `EAN13` varchar(13) DEFAULT NULL,
  `attribute_type` int(11) DEFAULT NULL,
  `val_t` varchar(1500) DEFAULT NULL,
  `val_n` decimal(15,4) DEFAULT NULL,
  `val_d` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  KEY `EAN13` (`EAN13`),
  KEY `attribute_type` (`attribute_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

attribute_type (16KB, 59 rows)

CREATE TABLE `attribute_type` (
  `attribute_type` int(11) DEFAULT NULL,
  `title` varchar(90) DEFAULT NULL,
  `field_name` varchar(90) DEFAULT NULL,
  `data_type` char(3) DEFAULT NULL,
  `seq` int(11) NOT NULL,
  `html` int(11) DEFAULT NULL,
  KEY `attribute_type` (`attribute_type`),
  KEY `seq` (`seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Example slow query:

Query_time: 61.629684  Lock_time: 0.000138 Rows_sent: 1  Rows_examined: 28141979
select p.EAN13, p.company_tk, a.attribute_type, a.val_t, t.title, t.field_name from product_base p
inner join attribute a on p.EAN13=a.EAN13
inner join attribute_type t on a.attribute_type=t.attribute_type
where p.EAN13=705632109762 and (a.attribute_type=1 or a.attribute_type=3)
order by t.seq;

Running explain on that query:

+----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+
| id | select_type | table | type  | possible_keys        | key            | key_len | ref                      | rows    | Extra                       |
+----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+
|  1 | SIMPLE      | t     | range | attribute_type       | attribute_type | 5       | NULL                     |       2 | Using where; Using filesort |
|  1 | SIMPLE      | a     | ref   | EAN13,attribute_type | attribute_type | 5       | eandata.t.attribute_type | 6299937 | Using where                 |
|  1 | SIMPLE      | p     | ref   | EAN13                | EAN13          | 54      | eandata.a.EAN13          |       1 |                             |
+----+-------------+-------+-------+----------------------+----------------+---------+--------------------------+---------+-----------------------------+

Server/OS:
Debian 8 64bit | 24GB RAM (MySQL is using 70% of this) | 60GB SSD | 2xIntel Xeon CPU E5506 @ 2.13GHz | MySQL 5.5.60

Innodb settings here

MySQL my.cnf

# * Fine Tuning
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections        = 20
table_cache            = 9552
thread_cache = 10
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
query_cache_size = 512M
join_buffer_size = 2G
innodb_buffer_pool_size = 15G
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
#innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8

max_heap_table_size = 2G
tmp_table_size = 2G
wait_timeout = 120
interactive_timeout = 120

Any suggestions for the slow queries?

I would try using UNION except for OR condition.
You could try:

( select p.EAN13, p.company_tk, a.attribute_type, a.val_t, t.title, t.field_name, t.seq from product_base p
inner join attribute a on p.EAN13=a.EAN13
inner join attribute_type t on a.attribute_type=t.attribute_type
where p.EAN13=705632109762 and a.attribute_type=1 )
union all
( select p.EAN13, p.company_tk, a.attribute_type, a.val_t, t.title, t.field_name, t.seq from product_base p
inner join attribute a on p.EAN13=a.EAN13
inner join attribute_type t on a.attribute_type=t.attribute_type
where p.EAN13=705632109762 and a.attribute_type=3 )
order by 7;

Maybe MySQL optimizer will choose better execution plan.


How to reduce the mdf file size in this database which I have 50 GB reserved space I have a DB with 50 GB of size I need to reduce the size of it.
How to reduce the file size of it?

I already did this: shrink | files | selected 'Data' as filetype.
but still it shows 50 GB Reserved space is using.

You probably used the default in the GUI, which resulted that the GUI added the TRUNCATEONLY option. This means "don't move any data" and you probably had a situation where the last page in the database was in use. Hence the file didn't shrink.

By using the "Reorganize ..." option, SQL Server will move pages towards the beginning of the files, thus making the end of the file free and the file can be shrunk. Such page movements can take a terrible long time, fragment your indexes etc etc, so don't skrink unless you have a high enough payback. My article on shrink.


How do I determine the time period to which the SQL Server i/o warning is referring? SQL Server log shows batches of warnings, of the type:

  SQL Server has encountered 18757 occurrence(s) of I/O requests taking
  longer than 15 seconds to complete on file [T:\..

  SQL Server has encountered 951 occurrence(s) of I/O requests taking
  longer than 15 seconds to complete on file [M:\..

Our server is very busy in the hours before these warnings. Are they related to very recent requests or all requests since the last time a similar warning occurred?
I am trying to tie in the warnings to identify the jobs that might have caused them.
We have a long running job that finishes 10 minutes before these warnings occur. Are warnings collected and expressed on reaching a request threshold or on schedules? Can anyone explain the rules behind this warning log?

Are warnings collected and expressed on reaching a request threshold or on schedules? Can anyone explain the rules behind this warning log?

Let me start by saying that this is mostly not a SQL Server issue, unless you have totally flawed system. The message means that SQL Server waited more than 15 seconds for one of the I/O requests to complete. For example when SQL Server wants a data page into memory from disk it would initiate an I/O request, if that request takes longer than 15 seconds such messages are logged into errorlog. Normally, on a good system, an I/O response should complete within milliseconds but if this warning is coming this mostly indicates

The SQL Server is producing more I/O for a storage subsystem to handle and thus overwhelming it with I/O responses
The underlying disk subsystem is slow, outdated, not on updated firmware and so cannot cope up with SQL Server I/O requests.

Quoting from Blogs.msdn

  The message indicates that SQL Server has been waiting on at least one
  I/O for 15 seconds or longer. The exact number of times you have
  exceeded this time for the specified file since the last message is
  included in the message. The messages will not be written more than
  once every five minutes. Keep in mind that read IOs on an average
  system should take no more than 10-20ms and writes should take no more
  than 3-5ms (the exact acceptable values vary depending on your
  business needs and technical configuration). So anything measured in
  seconds indicates a serious performance problem. The problem is NOT
  within SQL Server, this message indicates SQL has sent off an IO
  request and has waited more than 15 seconds for a response. The
  problem is somewhere in the disk IO subsystem. For example, the disk
  IO subsystem may have more load than it is designed to handle, there
  is a "bad" hardware or firmware somewhere along the path, filter
  drivers such as anti-virus software are interfering, your file layout
  is not optimal, or some IO subsystem setting such as HBA queue depth
  is not set optimally.

There two phases of analyzing this message by SQL Server, recording and reporting. More details in This support Article

  Recording There are two moments when a record action occurs in SQL Server. The first is when the I/O operation actually finishes. If an
  I/O request takes more than 15 seconds to finish, a record operation
  occurs. The second moment is when the lazy writer runs. When the lazy
  writer runs, the lazy writer checks all the pending data and all the
  pending log file I/O requests. If the 15-second threshold has been
  exceeded, a record operation occurs.

  Reporting

  Reporting occurs in intervals that are 5 minutes or more apart.
  Reporting occurs when the next I/O request is made on the file. If a
  record action has occurred and 5 minutes or more have passed since the
  last report occurred, the informational message that is mentioned in
  the "Summary" section is written to the SQL Server error log.

  The 15-second threshold is not adjustable. However, you can disable
  stalled or stuck I/O detection by using trace flag 830, although we do
  not recommend that you do this.

Troubleshooting

Make sure SQL Server data and log files are distributed on different drive this will give some I/O relief
Make sure drive on which SQL Server files reside are not compressed
Get your storage checked from storage team/vendor.
If the message says I/O request taking longer for tempdb make sure you have configured tempdb properly.  Make sure trace flag 1117,1118 is enabled. This support article should help you.

To find out whether disk is slow or SQL Server is spawning more I/O's which disk could handle you would have to take help of perfmon counters and DMV Sys.dm_io_virtual_file_stats. For perfmon counters and how to gauge whether disk is issue or it is SQL Server please refer to blog by Karthik PK.

For DMV, Paul Randal owner of SQLSkills.com has Caputing IO latencies for period of time. This will let you capture I/O latency for period of time and analysze. If you have HDD or flash drives and see latency in seconds that is pointing to storage slowness issue.

Also read Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data


Time stored using now()::timestamp function stores wrong value I'm using AWS RDS for a PostgreSQL 11 database with PGAdmin. I store creation date of every row using now()::timestamp to store date and time without time zone. The problem is the time stored is never the value I want (UTC+05:30).

If I set parameter timezone as UTC in RDS, then the time stored is 5:30 hours behind India's time but if I set UTC+05:30, it stores either 11 hours behind time or 1 hour more, this happens randomly in random rows. I haven't changed anything else, just this timezone parameter.

I searched a lot but didn't find any similar answer.

tl;dr

Instead of the data type TIMESTAMP WITHOUT TIME ZONE, define your column for the data type TIMESTAMP WITH TIME ZONE.
Set the default time zone of your session within PgAdmin to UTC.
To adjust the retrieved UTC value into a particular time zone in SQL, call AT TIME ZONE 'Asia/Kolkata'. (But better to leave that zone adjustment to your application code rather than your SQL.)

TIMESTAMP WITHOUT TIME ZONE

  to store date and time without time zone.

You are using the wrong data type when defining your column.

The TIMESTAMP WITHOUT TIME ZONE data type cannot represent a moment.

This type purposely lacks the context of a time zone or offset-from-UTC. So you have a date and a time-of-day, such as noon on the 23rd of next January. But we have no way to know if you meant noon in Tokyo Japan, noon in Toulouse France, or noon in Toledo Ohio US, all very different moments several hours apart.

Any time zone or offset information passed along with an input is ignored when reaching a column of type  TIMESTAMP WITHOUT TIME ZONE.

TIMESTAMP WITH TIME ZONE

The TIMESTAMP WITH TIME ZONE type can track a moment, a specific point on the timeline.

Any time zone or offset information passed along with the date and time-of-day is used to adjust into UTC. The zone/offset is then discarded. So if you care about the original zone/offset, you need to explicitly store that in an extra column.

When retrieving a TIMESTAMP WITH TIME ZONE value, the value will always be in UTC (an offset of zero hours-minutes-seconds). However, beware of tools, middleware, or drivers carrying the anti-feature of injecting some default time zone or offset onto the retrieved value. This creates the illusion of that zone/offset having been stored when in fact Postgres always stores in UTC values of the data type TIMESTAMP WITH TIME ZONE.

PgAdmin is one of the tools with this unfortunate anti-feature. I suggest always setting the default zone of the session to UTC to see your retrieved values as they were stored.

Adjusting to time zone

I suggest you learn to think and work as a programmer in UTC. Most of your business logic in programming should be in UTC. Adjust into a time zone only for presentation to the user or where business logic demands. So this means that in most cases you should adjust to time zone only in your app code rather than in your SQL and database admin tools.

AT TIME ZONE function

But if you insist on adjusting to time zone with your SQL, use the AT TIME ZONE function.

You mentioned the offset of five and a half hours ahead of UTC, +05:30. I assume you mean time in India. Always prefer a real time zone name rather than a particular offset. Politicians frequently change the offset used by their jurisdictions. So hard-coding an offset could lead to faulty results.

Keep the tzdata file in your Postgres installation up-to-date, and then let Postgres determine the offset appropriate to the named time zone for that particular moment.

… AT TIME ZONE 'Asia/Kolkata' …

Various date-time data types

This chart I made for Java programmers may also be of use here, to better understand the various data types defined by the SQL standard.


Accessing nested array in json blob in PostgreSQL I have a query I need to run that accesses a doubly nested array inside a json blob. The data is structure like the following:

{
"id" : "5",
"data" : "[[1,2,3],[2,3,4],[4,5,6]....]
}

My query needs to take the max of the second value (e.g. 5 in the case given). The following query returns almost what I need:

SELECT
  id,
  (
    SELECT
      string_agg(value :: text, ',') AS list
    FROM
      jsonb_array_elements_text(data -> 'MMC')
  ) as MMCPow
FROM
  comp

gives me:

[1,2,3],[2,3,4],[4,5,6]....

Can anyone get me the rest of the way?

Your example data and your example query do not fit together.  There is no JSONB field named 'MMC' in the data, but there is in the query, for example.

Assuming the query is the thing that is correct, then something like this would work:

SELECT
  id,
  (
    SELECT
      max((value->1) :: int) AS _max_
    FROM
      jsonb_array_elements(data -> 'MMC')
  ) as MMCPow
FROM
  comp

Don't use "jsonb_array_elements_text" or ":: text" when you don't want them converted to text, use the -> operator to index into a JSONB array to get the 2nd element (JSONB arrays start counting from 0), and use "max" to take the max.


Your PHP MySQL library version 5.5.44 differs from your MySQL server version 10.0.20. This may cause unpredictable behavior How can I fix this "error" in phpmyadmin ? Is this a serious problem, I should care about?

Server Version: 10.0.20-MariaDB-1~trusty-log - mariadb.org binary
distribution Apache/2.4.7 (Ubuntu)
Datenbank-Client Version: libmysql 5.5.44
PHP-Erweiterung: mysqli Dokumentation
All packages are uptodate (official ubuntu 14.04 repos)
On MariaDB I use the official repos from mariadb.org

Thanks in advance!

I just fixed the problem:

apt-get remove php5-mysql
apt-get install php5-mysqlnd

I had to reinstall phpmyadmin, the errors disappeared now.


MySQL: Rank by Department in an employees table I need Mysql query to rank the hours used by each department in an employees table, but it just ranks all without taking into consideration the departments.

PROPOSED SOLUTION

SET @currhash = MD5('dummy');
SELECT A.id,B.deptname,B.hours,A.rank
FROM (SELECT
    (@prevhash := @currhash),
    (@currhash := MD5(depttype)),
    (@rnk:=IF(@prevhash=@currhash,@rnk+1,1)) rank,
    id,depttype FROM
    (SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>0,LOCATE('9',deptname),99))-1) depttype
FROM dept ORDER BY depttype,hours DESC) AA) A
LEFT JOIN dept B USING (id)
ORDER BY id;

YOUR SAMPLE DATA

use test
DROP TABLE IF EXISTS dept;
CREATE TABLE dept
(
    id int not null,
    deptname varchar(32),
    hours INT NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO dept VALUES
(1003,'sales1'     ,  2),(1004,'sales2'     ,  6),(1005,'sales3     ',  4),
(1006,'sales4'     ,  3),(1007,'operations1',  5),(1008,'operations2', 10),
(1009,'operations3',  7),(1010,'operations4',  8),(1011,'operations5',  4);

YOUR SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS dept;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE dept
    -> (
    ->     id int not null,
    ->     deptname varchar(32),
    ->     hours INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO dept VALUES
    -> (1003,'sales1'     ,  2),(1004,'sales2'     ,  6),(1005,'sales3     ',  4),
    -> (1006,'sales4'     ,  3),(1007,'operations1',  5),(1008,'operations2', 10),
    -> (1009,'operations3',  7),(1010,'operations4',  8),(1011,'operations5',  4);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @currhash = MD5('dummy');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.id,B.deptname,B.hours,A.rank
    -> FROM (SELECT
    ->     (@prevhash := @currhash),
    ->     (@currhash := MD5(depttype)),
    ->     (@rnk:=IF(@prevhash=@currhash,@rnk+1,1)) rank,
    ->     id,depttype FROM
    ->     (SELECT id,LEFT(deptname,LEAST(
    ->     IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99),
    ->     IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    ->     IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    ->     IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    ->     IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    ->     IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    ->     IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    ->     IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    ->     IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    ->     IF(LOCATE('9',deptname)>0,LOCATE('9',deptname),99))-1) depttype
    -> FROM dept ORDER BY depttype,hours DESC) AA) A
    -> LEFT JOIN dept B USING (id)
    -> ORDER BY id;
+------+-------------+-------+------+
| id   | deptname    | hours | rank |
+------+-------------+-------+------+
| 1003 | sales1      |     2 |    4 |
| 1004 | sales2      |     6 |    1 |
| 1005 | sales3      |     4 |    2 |
| 1006 | sales4      |     3 |    3 |
| 1007 | operations1 |     5 |    4 |
| 1008 | operations2 |    10 |    1 |
| 1009 | operations3 |     7 |    3 |
| 1010 | operations4 |     8 |    2 |
| 1011 | operations5 |     4 |    5 |
+------+-------------+-------+------+
9 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION FOR TIES (BONUS)

SET @currhash = MD5('dummy');
SET @curhours = -1;
SET @rank = 0;
SET @rnk = 0;
SELECT A.id,B.deptname,B.hours,A.rank
FROM (SELECT
    (@prevhash := @currhash),
    (@currhash := MD5(depttype)),
    (@curhours := IF(@prevhash=@currhash,@curhours,-1)),
    (@rnk      := IF(@prevhash=@currhash,@rnk+1,1)),
    (@rank     := IF(@curhours=hours,@rank,@rnk)) rank,
    (@curhours := hours),
    id,depttype FROM
    (SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>0,LOCATE('9',deptname),99))-1) depttype,hours
FROM dept ORDER BY depttype,hours DESC) AA) A
LEFT JOIN dept B USING (id)
ORDER BY id;

GIVE IT A TRY !!!

NOTE : Why this works

All the department names are unique. I needed to strip off the digits on the far right.

I did that with the innermost subquery

SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>0,LOCATE('9',deptname),99))-1) depttype
FROM dept ORDER BY depttype,hours DESC) AA

I designed it so that it will strip everything to the right of the first digit in the deptname. That will handle two-digit or three-digit numbers in deptname. After doing so, you end with two distinct values: sales and operations. I used this as the department type, which is aliased as depttype.

I could then iterate using local variables on the current depttype. I would also reset the ranking back to 1 when the depttype changes while iterating. The ranking technique I used is borrowed from the algorithm that I posted in Get the rank of a user in a score table. I implemented it to mechanically group by depttype and rank by hours.