Is a Postgres long-running query aborted if the connection is lost/broken? If I open a connection to Postgres and issue a long-running query, and then break the connection (e.g., kill the client process that opened the connection), will the long-running query continue to run, or will it be automatically aborted?  Is this configurable?

(I'm using Postgresql 9.2.9)

"It depends".

If the client vanishes due to network connection loss the query will generally run until it's retrieved enough rows to fill its network send buffer, then stop and get stuck until the TCP connection drops, at which point it'll abort. If it completes before it fills the TCP send buffer it'll complete successfully, so if it's autocommit the query will commit.

If the client is killed in a way that the client's operating system can report to the server via a TCP RST (like a client segfault/crash, SIGTERM, SIGKILL, etc), the PostgreSQL server will set the interrupt flag. Next time the query checks for interrupts as it executes it will see the flag and abort. Sometimes a query might be doing CPU-heavy work within code that doesn't check for interrupts - some extensions, and a few places within the PostgreSQL core - in which case it might not notice the interrupt for a long time and keep on running. It'll pretty much always see the interrupt and abort before completing and committing if it's autocommit, though.

If the client is killed by something like a sudden OS reboot, so that the client host suddenly knows nothing about the TCP connection but can still respond on the network, the query will probably get aborted the first time it triest to write a row, like Jeff said, because the client's host will send a TCP RST in response to the first packet sent by the server after the reboot. PostgreSQL checks for interrupts at each row it sends.

This behavour isn't configurable. As far as PostgreSQL is concerned if the client goes away its job is to terminate any queries the client was running.  To change that you'd need some kind of query completion token that you could obtain at query start, then use to ask the server about the query via another connection later. Essentially you'd have to implement asynchronous/background queries. Possibly a nice feature, but not currently supported.

If the query is autocommit, or if your query was a COMMIT that was in-flight at the time you killed the client/lost the connection, it is possible for a transaction to be in an indeterminate state where the client doesn't know whether or not it committed. There's no real way to find out, other than looking for the effects of the transaction on the data.

Where that is unacceptable you can use two phase commit and a client-side transaction manager.


How to update conflict resolver when upgrading from SQL-Server 2005 to SQL-Server 2008 We have recently upgraded from SQL Server 2005 to SQL Server 2008 (R2, SP1). This upgrade included some publications, where all tables are published with a default conflict resolver based on the "later wins" principle.

As you all know, once a table is published with a conflict resolver, the same conflict resolver must be used in all later publications using this table. Fair enough, but, when adding previously published tables to new publications, and specifying the very same conflict resolver to be used for this table, , we are getting an error message:

use [myDb]
exec sp_addmergearticle
    @publication = N'myDb_Pub',
    @article = N'Tbl_blablabla',
    @source_owner = N'dbo',
    @source_object = N'Tbl_blablabla',
    @type = N'table',
    @description = N'',
    @creation_script = N'',
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000C034FD1,
    @identityrangemanagementoption = N'none',
    @destination_owner = N'dbo',
    @force_reinit_subscription = 1,
    @column_tracking = N'false',
    @article_resolver = N'Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver',
    @subset_filterclause = N'',
    @resolver_info = N'ddmaj',
    @vertical_partition = N'false',
    @verify_resolver_signature = 0,
    @allow_interactive_resolver = N'false',
    @fast_multicol_updateproc = N'true',
    @check_permissions = 0,
    @subscriber_upload_options = 0,
    @delete_tracking = N'true',
    @compensate_for_errors = N'false',
    @stream_blob_columns = N'false',
    @partition_options = 0
GO

And this is the error we get:

The article '...' already exists in another publication with a different article resolver.

By trying to understand how the same conflict resolver is not considered by the machine as 'the same conflict resolver', I discovered that there were two conflict resolvers with the same name, different versions, in the registry:

the 2005 version:

file ssrmax.dll,
version 2005.90.4035.0,
cls_id D604B4B5-686B-4304-9613-C4F82B527B10

the 2008 version:

file ssrmax.dll,
version 2009.100.2500.0,
cls_id 77209412-47CF-49AF-A347-DCF7EE481277

And I checked that our 2008 server is considering the second one as the 'available custom resolver' (I got this by running sp_enumcustomresolvers). The problem is that both references are available in the registry, so I guess that old publications do refer to the 2005 version, while new publications try to refere to the 2008 version, which is indeed different from the previous one.

So the question is: how can I have the server consider only one of these 2 versions, and this (of course) without having to drop and recreate the existing publications (which would turn our life into hell for the next 2 weeks).

Well .. so nobody got an answer. But I think I (finally) got it. Guess what... it is somewhere in the metamodel (as usual)!

When adding an item to the subscription, the new conflict resolver references to be used by the stored procedure come from the [distribution].[MSmerge_articleresolver] table
But, for existing subscriptions, previous conflict resolver references are stored in the system tables of the publishing database, ie [sysmergearticles], [sysmergeextendedarticlesview], and [sysmergepartitioninfoview]

So we have on one side an item initialy published with SQLSERVER 2005, where the publication references the 2005 conflict resolver, as per the publishing database metamodel. On the other side, the machine will attempt to add the same item to a new publication, this time with a default reference to the conflict resolver available in the distibution database, which is indeed different from the 2005 one ....

To illustrate this, you can check the following

USE distribution
go
SELECT article_resolver, resolver_clsid
  FROM [MSmerge_articleresolver] WHERE article_resolver like '%Later Wins%'
  GO

Then,

USE myPublicationDatabase
go
SELECT article_resolver, resolver_clsid
  FROM [sysmergearticles] WHERE article_resolver like '%Later Wins%'
  GO
 SELECT article_resolver, resolver_clsid
  FROM [sysmergeextendedarticlesview] WHERE article_resolver like '%Later Wins%'
  GO
 SELECT article_resolver, resolver_clsid
  FROM [sysmergepartitioninfoview] WHERE article_resolver like '%Later Wins%'
  GO

So it seems that I should update either the references in the distribution database or the references in the publication database. Let's give it  a try!


Postgres upgrade issue from 9.3 to 9.4 I upgraded Postgres from 9.3 to 9.4. But when I login to server then it is showing 9.3.

see the screenshot details below:

$ psql --version
psql (PostgreSQL) 9.4.10
$ pg_dump --version
pg_dump (PostgreSQL) 9.4.10

$psql
psql (9.4.10, server 9.3.13)
Type "help" for help.

postgres=# select version();
                                                           version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
        (1 row)

How can I upgrade it? What is the issue?

Updated:
After running pg_lsclusters, it is showing:

pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

You didn't upgrade the database. You're likely on a system that permits different versions of PostgreSQL to run concurrently and you likely have two versions of the server running on two different ports. Usually 5432..543x. You'll have to

use pg_dump on the server running with the old port (the one you're connecting to here).
use pg_restore on the server running with the new port (that you probably didn't even know was running)

Try

sudo rgrep -i "port =" /etc/postgresql

Or, if you have Debian/Ubuntu, run pg_lsclusters


Nested, inline pivot table (oracle) I'm trying to go from a table like this:

ID | NAME | METADATA
-------------------------------
1  | test | '{"a":"x","b":"y"}'

where the metadata column is a JSON string. What I'd like to end up with is this:

ID | NAME | A | B
-----------------
1  | test | x | y

where the JSON "keys" are merged in-line as column names and the corresponding JSON "values" as the fields. So far, I have managed to parse the JSON and create a "pivot" table like this:

A | B
-----
x | y

but I can't work out how to append the other columns from the original table. My code so far:

SELECT * FROM (
    SELECT
        SUBSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)), 1, INSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),':') - 1) COL_NAME,
        SUBSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),    INSTR(TRIM (REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL)),':') + 1) COL_VAL
    FROM   (SELECT REPLACE (REPLACE (REPLACE (:json, '}'), '{'),  '"') AS str FROM DUAL)
    CONNECT BY LEVEL < LENGTH (str) - LENGTH (REPLACE (str, ',', NULL)) + 2
)
PIVOT
(
  MAX(COL_VAL)
  FOR COL_NAME IN ('a' AS A, 'b' AS B)
);

This is where my SQL knowledge fails.

Note: I know the JSON parsing is ugly, but I can't use a 3rd-party library and I'm running on 11g so don't have any built-in JSON support.

Thanks in advance for any help!

I don't really understand why you're trying to pivot anything, so maybe I'm missing your point. But try this:

select
  id, name
, regexp_replace(metadata, '.*"a":"([^"]+)".*', '\1') m_a
, regexp_replace(metadata, '.*"b":"([^"]+)".*', '\1') m_b
from foo;

Doesn't rely on the order of the JSON attributes to always be the same. It's not completely safe - doesn't handle quoted " in particular, but it does the job for your sample.


Can you upgrade a single SQL Server 2008 Failover Cluster Instance to SQL Server 2012? Or do you have to upgrade every FCI on the Failover Cluster? The documentation doesn't seem to be clear.

We have a 7 instances on a 3 node cluster. We want to upgrade only one of the instances from SQL Server 2008 to SQL Server 2012. Can we perform an upgrade or do we need to backup the instance as best we can, uninstall that instance, install SQL Server 2012 then add that instance back?

I know you can run SQL Server 2008 and SQL Server 2012 on the same failover cluster...

The installation makes it seem like you can upgrade a single instance, but I'm still apprehensive since we haven't tested and no documentation seems to explicitly state it's possible.

This is possible and we successfully completed an upgrade of a single failover cluster instance to SQL 2012 on a cluster with multiple SQL 2008 failover cluster instances.

Perform the upgrade of the instance on the two nodes not hosting the instance first (be sure to select the correct instance when asked as in the screenshot), then finally perform the upgrade on the node hosting the instance.


How can you have a Buffer cache hit ratio of 100% with PLE of 103? I found What does a "Buffer cache hit ratio" of 9990 mean? (I was getting result of 9000+ from a blog that was wrong) and used the solution by LowlyDBA/Denis Gobo combined with the PLE checker from Denis Gobo and got a Buffer cache hit ratio of 100% with PLE of 103, how is that possible?
The buffer chache hit ratio of 100% is saying the 100% of pages needed are being found in memory, but the PLE is saying pages are only staying in memory for a couple of minutes.
Maybe there is some PLE value, below which buffer chache hit ratio does not add value?
SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters  a
JOIN  (SELECT cntr_value, OBJECT_NAME
    FROM sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
        AND OBJECT_NAME = 'SQLServer:Buffer Manager') b ON  a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = 'Buffer cache hit ratio'
AND a.OBJECT_NAME = 'SQLServer:Buffer Manager'

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND OBJECT_NAME = 'SQLServer:Buffer Manager'

Why I care:
My instance is SQL 2017 CU13, heavy OLTP, with 4GB of RAM for the SQL instance (single instance, 6GB RAM on the server).
I know that more than 1.5GB of RAM is devoted to the Plan Cache, according to some sources of the 4GB of RAM here, the Plan Cache should be able to use 3GB (found similar details several places, but all are old and none are Microsoft)
Select ( SUM(size_in_bytes)) /1024 /1024 AS size_in_MB FROM sys.dm_exec_cached_plans

I have not used any xevents yet to measure plan eviction, just trying to understand things.

Buffer cache hit ratio and Buffer cache hit ratio base are measurements for a very small timeslice. You just ran your query at times when the recent reads hit the buffer cache.

For proof, just look at the raw values over time - they go up and down.

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Buffer cache hit ratio','Buffer cache hit ratio base')

I would recommend a look at other metrics (perhaps sys.dm_io_virtual_file_stats?) to see what kind of IO you're driving.


Is it a good approach to design tables according to the queries I'll perform on it? Watching this video, and pretty new to dbms.
The speaker explains that in a row-oriented DB, rows are read in blocks.
So, my understanding is that if I have rows with fewer fields, more rows can fit into a single block and when I query the table it should take less IO operation, resulting in better performances.. Am I right?
Can I extract the rule that I shouldn't design tables according to the entity they represent but, instead, to the frequency I'll read or update that fields?
For example:
table employers:

ID
Name (frequently used)
Badge number (frequently used)
Birth date (rarely used)
Birthplace (rarely used)

Should I split the table into 2?
tbl1: ID | Name | Badge number
tbl2: ID | Birth date | Birthplace

In most database management systems, data is is stored as pages, not blocks.  Pages are normally  4 or 8 KB, depending on the database and how its been configured.
All else being equal, smaller row size will equate to better reuse of cached pages and less page reads on queries that require a large number of rows - so less I/O and faster read performance.
However
If you vertically partition the table (as you have in your example), there will be a slight increase to overall storage (equal to the primary key length and number of rows, plus the b-tree) and insert performance will be slightly slower as you'll need to maintain a PK-FK relationship between the two tables.
Furthermore, if most of your queries are for single-record lookups, you're still going to be reading a single page.  There's a greater chance that page will be cached, but reading 4 or 8 KB off a modern disk is really not an expensive operation.
Splitting the table would require 2 page reads (and navigating two B-trees) when you require BirthDate/BirthPlace.  Again, not really a big deal on modern hardware.
The only time I would vertically partition a table would be in certain data warehouse situations, or if BirthDate/BirthPlace were nullable and infrequently populated.
Other Considerations
If badge number is relatively small in size (say, under 20-30 bytes), the best thing you can do to increase performance would be to drop the unneeded ID column and make your primary key BadgeNumber since:

You shouldn't have duplicates in that column
Most likely you will primarily lookup on that column, so using BadgeNumber:

Saves you a column, making your table more compact
Removes the need for an index (and associated overhead) on BadgeNumber
Eliminates the need to join to your table to get the BadgeNumber when the table has a PK-FK relationship to another table.

There are other ways to reduce I/O and improve read performance.  Most commercial DBMSs will support some form of data compression. This can fit more rows on a single page without any changes to the structure of the table, at the expense of some CPU overhead to compress/decompress the data as it is written/read.  CPU is usually a cheaper operation than disk, so compression is usually a net benefit.


MariaDB handling composite unique indexes that are foreign keys at the same time I have a possible issue with how MariaDB (and probably MySQL) handles composite unique indexes that are also foreign keys. Not sure if that is by design and if it is, why does it work like that.

I have a table called action_contact. It has a foreign key action_id to the 'action' table and contact_id to the 'contact' table. Both are unsigned bigint columns. I need these two fields to be unique indexes as well. If I don't add the unique index, both fields are also indexes in the database by default, which is fine. But, once I add the unique composite index (action_id, contact_id) - it’s added in the database, but the normal index for contact_id is still present and the normal index for action_id is removed. The foreign keys are still present, as they should. Is this normal behavior?

This is documented behaviour:

  MySQL requires indexes on foreign keys and referenced keys so that
  foreign key checks can be fast and not require a table scan. In the
  referencing table, there must be an index where the foreign key
  columns are listed as the first columns in the same order. Such an
  index is created on the referencing table automatically if it does not
  exist. This index might be silently dropped later if you create
  another index that can be used to enforce the foreign key constraint.
  index_name, if given, is used as described previously.

Multiple indexes over the same columns are allowed, but deprecated (as it is redundant- there is almost nothing that the non-unique key can do that the unique index cannot), and may error out in the future (although probably not for different index types like this case).

My suggestion, to prevent confusion, is to just create the unique index (or any explicit index that is adequate with a custom name) from the beginning. I agree with you, that this could be confusing, so looking at the manual is always a great idea, and I believe the current implementation make sense from a compromise between control and optimization perspective. :-)


How to use barman to backup PostgreSQL database correctly? I am using PostgreSQL 10 now. Installed newest Barman on RHEL 7.

When I run this command by barman user:

-bash-4.2$ barman receive-wal pg
Starting receive-wal for server pg
ERROR: ArchiverFailure:pg_receivexlog not present in $PATH

Official Introduction

WAL streaming

Barman can reduce the Recovery Point Objective (RPO) by allowing users to add continuous WAL streaming from a PostgreSQL server, on top of the standard archive_command strategy.

Barman relies on pg_receivewal, a utility that has been available from PostgreSQL 9.2 which exploits the native streaming replication protocol and continuously receives transaction logs from a PostgreSQL server (master or standby). Prior to PostgreSQL 10, pg_receivewal was named pg_receivexlog.

IMPORTANT: Barman requires that pg_receivewal is installed on the same server. For PostgreSQL 9.2 servers, you need pg_receivexlog of version 9.2 installed alongside Barman. For PostgreSQL 9.3 and above, it is recommended to install the latest available version of pg_receivewal, as it is back compatible. Otherwise, users can install multiple versions of pg_receivewal/pg_receivexlog on the Barman server and properly point to the specific version for a server, using the path_prefix option in the configuration file.

In order to enable streaming of transaction logs, you need to:

setup a streaming connection as previously described
set the streaming_archiver option to on
The cron command, if the aforementioned requirements are met, transparently manages log streaming through the execution of the receive-wal command. This is the recommended scenario.

However, users can manually execute the receive-wal command:
barman receive-wal <server_name>

It said:

Prior to PostgreSQL 10, pg_receivewal was named pg_receivexlog.

Why it's version 10, got pg_receivexlog error?

Check Backup

When I check backup, got:

-bash-4.2$ barman check pg
Server pg:
    WAL archive: FAILED (please make sure WAL shipping is setup)
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
    replication slot: FAILED (slot 'barman' not initialised: is 'receive-wal' running?)
    directories: OK
    retention policy settings: OK
    backup maximum age: OK (no last_backup_maximum_age provided)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: OK (have 0 backups, expected at least 0)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    systemid coherence: OK (no system Id stored on disk)
    pg_receivexlog: FAILED
    pg_receivexlog compatible: FAILED (PostgreSQL version: 10.12, pg_receivexlog version: None)
    receive-wal running: FAILED (See the Barman log file for more details)
    archiver errors: OK

How to fix the failed items?

Edit

When I check pg_receivewal by postgres user:

$ sudo su - postgres
-bash-4.2$ pg_receivewal
-bash: pg_receivewal: command not found
-bash-4.2$ psql
psql (9.2.24, server 10.12)
WARNING: psql version 9.2, server version 10.0.
         Some psql features might not work.
Type "help" for help.

PostgreSQL version: 10.12, pg_receivewal not exist?

When pg_receivexlog was renamed, barman didn't add sprinkle conditional error messages all over its code.  It is looking for pg_receivewal, but the text of the error message still says "pg_receivexlog" as an umbrella term to cover whichever applies.

The solution is to add the directory holding pg_receivewal to your $PATH.  It should already be there, but apparently it isn't.  How did you install both PostgreSQL and barman?  Which OS user are you running this command as?


Allow two users permission to execute single agent job I am trying to allow two users to run a single agent job. With this in mind, I don't want to add the users to the SQLAgentOperatorRole role in msdb as that will give them excess priveleges.

I tried creating a stored procedure which will run as the owner (dbo):

Create the stored procedure (logged in with a sysadmin account)

CREATE LOGIN [UnPriveleged] WITH PASSWORD = 'MyPassword'
GO

USE AdventureWorks2017
GO

CREATE USER UnPriveleged FOR LOGIN [UnPriveleged]
GO

CREATE PROCEDURE spRunJOb
WITH EXECUTE AS OWNER
AS
       SELECT CURRENT_USER
       SELECT SYSTEM_USER
       EXEC msdb..sp_start_job @job_name = 'test'
GO

GRANT EXECUTE ON spRunJOb TO UnPriveleged

and then I run it as the Unpriveleged user in another session:

SELECT CURRENT_USER
EXECUTE [spRunJOb]

and I get an error

Msg 229, Level 14, State 5, Procedure msdb..sp_start_job, Line 1 [Batch Start Line 0]
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

I thought this would run as the login for the owner of the stored procedure (sa) as that is a sysadmin and should be able to do everything.

The output I get from the selects confirms the procedure is executing as the sa system user. The overall SELECT output is

Unpriveleged
dbo
sa

How can I achieve what I want here?

The issue here is cross-database ownership chaining, basically once you leave the context of the AdventureWorks database, you don't have the elevated rights that dbo/sa implies and cannot actually execute the procedure in msdb.

You should recreate the stored procedure in msdb instead and grant your unprivileged user CONNECT permission to msdb and EXECUTE permission on the new procedure in msdb.

Alternatively, you can turn TRUSTWORTHY on for the AdventureWorks database but, generally, that is ill-advised. See this answer from Aaron Bertrand for additional info and a number of great links explaining the risks of TRUSTWORTHY.


How to create multiple entries on index based on the fields of one row? I never found a good way to index multiple fields of one row as entries of an index or simulate this feature on MySQL.

The problem arises when you have fields working as tags or similar concept. Ex.: fields names as tag1, tag2, tag3. To search lines with a specific tag fast requires that you have 3 index and do 3 separate queries on the most basic and obvious way.

Is there any way to index these 3 fields as entries of one index allowing just one search.

ID tag1 tag2 tag3
-- ---- ---- ----
01 abc  xyz  bla
02 foo  bar  ble
03 xyz  bla  bar

index
abc -> 01
bar -> 02 03
bla -> 01 03
ble -> 02
foo -> 02
xyz -> 01 03

Or is there any other way to do this efficiently?


Storing a Shipping Address Best Practice I'm building a checkout system which needs to store the customer's shipping address in the database and am unsure the best way to go about it.

Currently I have an addresses table which stores each address with a unique ID:

id | address         | city     | division   | country | postcode
-----------------------------------------------------------------
1  | 23 Example Road | York     | Yorkshire  | GB      | PE21 5QR
2  | 7 Lala Land     | Stockton | California | US      | 96341

I then have a many to many relationship table called account_addresses which links addresses to user accounts:

account_id | address_id
-----------------------
23         | 1
48         | 2

For storing the shipping address in the database I was going to allow the user to select one of their existing addresses or let them add a new one to their account and store that address ID in the orders table. However, I realised that if I just store the address ID, if the user edits that address it would change the shipping address thus making it wrong.

I only see two solutions:

Store the shipping address as a new row in the addresses table
although this will duplicate the data that already exist.
When a user edits their address it inserts a new address and leaves
the old one in the database instead of updating the data.

What's the best solution and why?

The Shipping Address is a property of each and every order, not of the customer. Even if it was a property of the Customer, it would be necessary to know where past orders were shipped to if a customer relocated.

Therefore all that can be stored as a Customer property are the Default Shipping Address (and Default Billing Address), for pre-populating each order and invoice as they are created.  This is not a denormalization, because it directly reflects and captures the required business rules.

Now that the historical accounting records are taken care of,  there should be a CustomerLocation table with FK's to three Address records in an Address table: Physical Address; Default Billing Address; and Default Shipping Address. The first of these records should be associated with an EffectiveDate, so that again a historical record exists as changes occur.


MySQL permits setting a foreign key constraint that references only one column of a composite primary key In the database structure depicted in the diagram that follows

the columns key1 & key2 make up the composite primary key of table4, but in table3 I'm able to set up a foreign key constraint that makes a reference to key1 only.

Why does MySQL allow this? Does the above database structure make no sense at all?

Quote from the documentation:

  InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Although this may come as a surprise, it is "documented behaviour".  This is probably one of these cases where you/we must just accept that things have been implemented in a certain manner ie InnoDB have implemented foreign keys this way.

Whether the design (in question) makes sense or not depends on the underlying business rules.  There may well be situations that require such a constraint.


MySQL - cannot examine view definition - Error getting DDL for object - No database selected I have a view that I can run, it returns a dataset, but whenever I try to alter the view or look at its definition, I get
Error getting DDL for object
No database selected

But I do have a database selected and I can run the view with results.
How do I recover the definition?  Please advise

No database selected error message means that no database is selected as current. Specify default database in my.ini, connection definition, or execute USE dbname; as the very first query in the connection.

why no other view required a default schema be defined, and why is a default schema required at all? – Roy Hinkley

Default scheme needed when tablename without database name is specified somewhere in SQL text.
For example, you execute SHOW CREATE TABLE my_view - but without default datatbase set the server does not know where it must take this view from (and even does this is a table or a view).


SQL Server Job failing to delete html files out of a folder We have a simple job that runs every Sunday on sql server 2000. Copies files over, and then deletes any .txt, .rpt, .html and .log files.

It looks to me like it isn't finding any .txt and .rpt files and is reporting back no output generated, but those steps are passing.

Then for the del .html files, it reports back that it can't find any html files and fails.

The code is:

   COPY C:\DATA\LOGS\*.* C:\DATA\LOGS\Sunday\*.*    Succeeds
   DEL  C:\DATA\LOGS\*.txt                          Succeeds
   DEL  C:\DATA\LOGS\*.rpt                          Succeeds
   DEL  C:\DATA\LOGS\*.html                     FAILS
   DEL  C:\DATA\LOGS\*.log                      Never gets here

Any ideas as to why it wouldn't like that del *.html ? Why wouldn't it just look for .html files, and when not finding any, just pass the step, and go onto the last step?

It looks like the problem is that, while there is always some file present (so the COPY always succeeds), there is not always a file with the specified extensions.

One quick-and-dirty solution - add the following lines between the COPY and the first DELETE:

ECHO z >> C:\DATA\LOGS\ZZZ_tmp.txt
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.rpt
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.html
ECHO z >> C:\DATA\LOGS\ZZZ_tmp.log

This will create one file with each required extension (or append a z to an existing file with the name).

If you want to go this way, make sure you:

Pick a base file name that you will never see in reality, so you don't corrupt an existing file; and
Document the purpose of the extra files.

Ideally, you'd want to create a solution where you don't fail the DELETE step if there's no matching files, instead of creating dummy files just to get deleted. This can probably be done with CMD.EXE; however, I'd suggest you implement a script in whatever language your department uses (or, at least in whatever language you're comfortable with). Unless you're comfortable with DOS (or whatever you call what CMD.EXE uses now), that's probably not your best solution.

Also - note the COPY will fail if there are no files in C:\DATA\LOGS\ to begin with. This could also be handled by a script, as could what to do if both source and destination directory have a file with the same name.


permissions for yet another "ORA-06512: table or view does not exist" scenario See the code below.  I'm running the code with user x in schema y.  The exact same code works if I run it by user x in schema x, but not with user x in schema y.  I get the error ORA-06512: table or view does not exist.  Why would this be?  I'm setting the schema explicitly to y.  When running JUST the create statement with user x in schema y, it runs fine.  And when running JUST the create statement with user x in schema x, it also runs fine.

There are two synonyms in schema y, called aaaaa and bbbbb.  See y.aaaaa and y.bbbbb in the create statement.  Synonym y.aaaaa selects data from table z.aaaaa in schema z.  Synonym y.bbbbb selects data from table z.bbbbb in schema z.  user x currently has SELECT permissions on these two tables in schema z.  I renamed the synonyms (y.aaaaa and y.bbbbb) in schema y so they're easier to find.

The XSQL is the culprit, but not sure what permissions we need.  When XSQL(sql_code) is commented out, it doesn't throw the error.

This doesn't work:

alter session set current_schema=y;

DECLARE sql_code VARCHAR2(4000) :=
'create table basis as '||
'(select my_basecode,c_fullname,encounter_num,concept_cd from aaaaa basis '||
'        inner join enc on enc.patid = basis.patient_num and enc.encounterid = basis.encounter_num '||
'     join bbbbb basiscode  '||
'        on basis.modifier_cd = basiscode.c_basecode '||
'        and basiscode.c_fullname like ''\BASIS\%'') ';
BEGIN
  DROPSQL('DROP TABLE basis');
  XSQL(sql_code);
END;

This works:

alter session set current_schema=y;

create table basis as
(select my_basecode,c_fullname,encounter_num,concept_cd from aaaaa basis
        inner join enc on enc.patid = basis.patient_num and enc.encounterid = basis.encounter_num
     join bbbbb basiscode
        on basis.modifier_cd = basiscode.c_basecode '||
        and basiscode.c_fullname like '\BASIS\%');

For reference purposes:

create or replace PROCEDURE XSQL(sqlstring VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE sqlstring;
  dbms_output.put_line(sqlstring);
END XSQL;

Well I have simulated your problem in my test environment. I found the issue with the table name(aaaa and bbbbb which belongs to z schema).
Though you have select privilege on these tables you must use schema name explicitly(z.aaaaa and z.bbbbb). The following is the test case.

SQL> create user z identified by z;

User created.

SQL> create user y identified by y;

User created.

SQL> create user x identified by x;

User created.

SQL> grant connect, resource to x,y,z;

Grant succeeded.

SQL> grant create any table to x;

Grant succeeded.

SQL> conn z/z
Connected.
SQL> create table aaaaa(id number, name varchar2(20));
Table created.

SQL> create table bbbbb(id number, address varchar2(20));
Table created.

SQL> grant select on bbbbb to y;
Grant succeeded.

SQL> grant select on aaaaa to y;
Grant succeeded.

SQL> grant select on aaaaa to x;
Grant succeeded.

SQL> grant select on bbbbb to x;
Grant succeeded.

SQL> conn / as sysdba
Connected.
SQL> grant create synonym to y;

Grant succeeded.

SQL> conn y/y
Connected.
SQL> create synonym aaaaa for z.aaaaa;

Synonym created.

SQL> create synonym bbbbb for z.bbbbb;

Synonym created.

//As you said you have renamed the synonyms.

SQL> rename aaaaa to a1;

Table renamed.

SQL> rename bbbbb to b1;

Table renamed.

SQL> conn x/x
Connected.
SQL> create or replace procedure xsql(sqlstring varchar2) as
begin
execute immediate sqlstring;
dbms_output.put_line(sqlstring);
end xsql;
/

Procedure created.

SQL> alter session set current_schema=y;

Session altered.

SQL> declare sql_code varchar2(2000):=
'create table basis as '||
'select basis.id, name, address from aaaaa basis join bbbbb basis_code on(basis.id=basis_code.id)';
begin
x.xsql(sql_code);
end;
/
declare sql_code varchar2(2000):=
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "X.XSQL", line 3
ORA-06512: at line 5

SQL> declare sql_code varchar2(2000):=
'create table basis as '||
'select basis.id, name, address from z.aaaaa basis join z.bbbbb basis_code on(basis.id=basis_code.id)';
begin
x.xsql(sql_code);
end;
/

PL/SQL procedure successfully completed.


Reset global MySQL status variables I'm  currently looking into an issue where when running MySQL prepared statement I get an exception:

  SQL Exception: Can't create more than max_prepared_stmt_count statements (current value: 16382)

Looking into this it looks like I'm definetely leaking prepared statements somewhere shown by the global status using the following query:

show global status like 'com_stmt%';

Gives me back the following results:

Com_stmt_close          37693
Com_stmt_execute        54079
Com_stmt_fetch          0
Com_stmt_prepare        54079
Com_stmt_reprepare      0
Com_stmt_reset          0
Com_stmt_send_long_data 0

I'm trying to reset these counters so my app can go back to a normal running state, while I investigate where the prepared statements are being closed but I can't find any way to reset these counters.

I've tried being logged in to mysql and running flush status, I've tried sending flush-status from the mysql-admin command line utility and I've tried resetting the DB but I can never get it to reset.

I'm using MySQL 5.7

After you are finished with a prepared statement, and executing it, be sure to deallocate_prepare_statement.  I think this will keep the counter from ever climbing to the limit.


What is the SQL Standard method of doing LIMIT OFFSET? I know LIMIT and OFFSET are pretty common but unstandardized methods to page through a result set. I know there is a standardized method, but I can never find this when I'm going to look it up,

The PostgreSQL LIMIT/OFFSET docs don't link to it,
The PostgreSQL FETCH docs don't link to it either.

I know it's not either of them. What is the SQL Standard and PostgreSQL syntax for LIMIT and OFFSET?

SQL 2011 Spec

The syntax in the spec is defined as,

<query expression> ::=
  [ <with clause> ] <query expression body>
  [ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

You can see here that <result offset clause> and the <fetch first clause> are both optional and stand by themselves, moving on

<result offset clause> ::=
  OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
  FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }

<fetch first quantity> ::=
  <fetch first row count>
  | <fetch first percentage>

<offset row count> ::=
  <simple value specification>

<fetch first row count> ::=
  <simple value specification>

<fetch first percentage> ::=
  <simple value specification> PERCENT

<simple value specification> ::=
  <literal>
  | <host parameter name>
  | <SQL parameter reference>
  | <embedded variable name>

RDBMS-Specific

PostgreSQL

The SQL:2011 Spec supports a bit more than PostgreSQL, namely a WITH TIES option (F867) and the ability to specify PERCENT option (F866). The query is defined as,

PostgreSQL only documents this, AFAIK, in SELECT,

SELECT
...
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

For more information aside from PostgreSQL, and the spec see the tag-info for offset-fetch


Display all tables and their columns Is there a way to display the layouts for all the tables to see their columns? I am trying to find tables with a certain column names.

I am using the web app Web Data Administrator

Example:

table1
- column 1
- column 2
table2
- column 1

is there a way to get a display like that?

Here is a start, if I can guess that by row you actually mean column:

SELECT
  [table]  = QUOTENAME(OBJECT_SCHEMA_NAME([t.object_id])) +'.'+ QUOTENAME(t.name),
  [column] = c.name
FROM sys.tables AS t
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
ORDER BY t.name, c.column_id;

This doesn't provide exactly the output you want, but surely your presentation tier can - while it is looping through all of the rows - figure out to only list a table when it comes across a new one.


You can accomplish this with the DATE_FORMAT function,

SELECT YEAR(datetime) AS Year, DATE_FORMAT(datetime, '%b %e') AS Week, COUNT(*) AS total
FROM table
GROUP BY Year, Week;


How do I CASCADE DROP a table being referenced in MariaDB? If I have a table that references another one

CREATE TABLE foo ( x int PRIMARY KEY );
CREATE TABLE bar (
  x int,
  CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x)
);

DROP TABLE foo;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

In PostgreSQL I can easily use DROP ... CASCADE

  CASCADE Automatically drop objects that depend on the table (such as views), and in turn all objects that depend on those objects (see Section 5.13).

Like this,

# CREATE TABLE foo ( x int PRIMARY KEY );
# CREATE TABLE bar ( x int REFERENCES foo );
# DROP TABLE foo CASCADE;
NOTICE:  drop cascades to constraint bar_x_fkey on table bar
DROP TABLE

However, in MySQL, even with CASCADE you get an error,

DROP TABLE foo CASCADE;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

I don't think that's how CASCADE works with MySQL derivatives!

Tables:

CREATE TABLE foo
(
  x int PRIMARY KEY
);

CREATE TABLE bar
(
  x int,
  CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x) ON DELETE CASCADE
);

Some values:

INSERT INTO foo VALUES (3), (45), (7);
INSERT INTO bar VALUES(3), (3), (45), (7), (7);

Then:

DELETE FROM foo WHERE x = 3;

Now:

SELECT * FROM bar;

 x
 7
 7
45

So, (for the MySQL family) the DRI (Declarative Referential Integrity) works on the level of records and not on a table level. The referential constraint exists on the table bar and not on foo - therefore deleting foo has no effect on that constraint - and the system won't let you delete the parent table, but will let you delete the parent record.

Take a  look at the fiddle here and it's fairly clear (and actually logical enough in one sense) if you reflect on it - at least to me it is!

However, a big caveat to the logic argument - PostgreSQL also doesn't allow

DROP TABLE foo;

but with a far more informative error message:

ERROR:  cannot drop table foo because other objects depend on it
DETAIL:  constraint asdf on table bar depends on table foo
HINT:  Use DROP ... CASCADE to drop the dependent objects too

however

DROP TABLE foo CASCADE;

does succeed.

So, MySQL fails on both statements, whereas PostgreSQL's solution is (IMHO and as usual) superior - it "forces" the CASCADE if it's explicitly invoked. MySQL won't let it delete under either circumstance.


Not able to install any DB2 LUW 10.5 fixpacks I am currently on Fixpack 1 and am trying to install ANY more recent fixpack. All of them refuse to apply themselves with similar errors. The one below is from Fixpack 2 file v10.5fp2_ntx64_server. Each of them up to the most recent says that they cannot upgrade my version, but none of them provides a reason.

After reading Installation Planning link, my brain hurts after coming across of this:

DB2 must be installed on a drive with short names enabled or a directory with no spaces such as E:\DB2\SQLLIB. To check the current 8.3 file name settings for a drive, in a command window with administrator privileges, enter

fsutil.exe behavior query disable8dot3

The following possible values are returned when you run the command:

0: Create 8.3 short file names (default)
1: Do not create 8.3 file names
2: Set 8.3 file names on a per volume basis
3: Disable 8.3 file names on all volumes, except the system volume

The registry state of NtfsDisable8dot3NameCreation is 0, which means that 8.3 file names are enabled on all volumes. To enable 8.3 file names on a global basis, enter

fsutil.exe behavior set disable8dot3 0

If DB2 is installed on a drive without 8.3 name support andin a directory without spaces, it must be reinstalled on a drive with 8.3 name support. If the folder you are trying to install DB2 is already created when 8.3 name support is disabled, then the short name is not created if you try a reinstall onto the same directory. Windows short names are created during the directory creation, hence you must install it into a new directory or if the directory is empty delete the directory and allow recreation.

On my system, the setting is at its default 2, which means "per volume". I am not sure if this is the reason the fixpacks refuse to install themselves.
Does this mean that IBM essentially puts their customers into a death trap, by allowing the initial install of DB2 to place it into C:\Program Files\ but refusing to install fixpacks later? I cannot believe that this is the case. Can anyone whose DB2 runs off of C:\Program Files\ explain how we are supposed to upgrade?
UPDATE:
By running dir /x I found out that all directories in the hierarchy from c:\ down to sqllib have short names. The lack of them cannot be the reason why updates are refusing to install.

From your screenshot this is Express-C edition. It cannot be upgraded with fixpack.
See here

In my opinion your best bet is to leave it as is or plan for migration to newer version. IBM provides only newest Express-C edition from what I could gather.


Weird PostgreSQL expression parsing How is PostgreSQL parsing supposed to work?

rivet=# SELECT 1 IS NOT NULL::int + 2 IS NOT NULL::int;
 int4
------
    1
(1 row)

But

rivet=# SELECT 1 IS NOT NULL::int + (2 IS NOT NULL)::int;
 ?column?
----------
        2
(1 row)

This seems very unintuitive?

Is this a mistake? Can anyone help me understand what PostgreSQL is doing?

In the first example, you end up with 1 + 2 IS NOT NULL - which, it is not, 3 is certainly not null. 3 IS NOT NULL evaluates to true, casting it to an int makes it 1 again. Hopefully this makes it clearer:

testdb=# select 1 is not null;
 ?column?
----------
 t
(1 row)

testdb=# select 1 is not null::int;
 int4
------
    1
(1 row)

testdb=# select 1 is not null::int + 2;
 ?column?
----------
        3
(1 row)

testdb=# select 1 is not null::int + 2 is not null;
 ?column?
----------
 t
(1 row)

testdb=# select 1 is not null::int + 2 is not null::int;
 int4
------
    1
(1 row)


Temp postgres table ignores "excluding constraints" In PostgreSQL 9.5.

I'd like to create a temporary copy of my table without its primary key. Postgres has a feature { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } - which I'm attempting to use.

create table test (
    col1    integer,
    col2    integer,
    col3    text,
    primary key (col1)
);

create temporary table t (like test excluding constraints);

insert into t (col1, col2, col3) values (1, 2, 'foo'), (null, 2, 'bar');

If I'm understanding excluding constraints correctly, that should not bring over the non-null constraint on test to t.

But here's the error that I receive:

ERROR:  null value in column "col1" violates not-null constraint
DETAIL:  Failing row contains (null, 2, bar).
********** Error **********

ERROR: null value in column "col1" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, 2, bar).

I have a workaround, create temporary table t as select * from test where true = false, but I'd like to know what I'm not understanding correctly about excluding constraints.

I'd like to know what I'm not understanding correctly about excluding constraints.

What is happening here is that primary key (col1) creates a primary key constraint but doesn't create a separate not null constraint. It adds a not null "modifier" to the column. This works as a constraint, ie. no nulls are allowed in col1 but it is not a named constraint. That's why it's not removed by the excluding constraints, it's a column property.

As the CREATE TABLE documentation states:

  Not-null constraints are always copied to the new table. CHECK constraints will be copied only if INCLUDING CONSTRAINTS is specified. ..

If you had the table defined with an explicit CHECK column is not null constraint instead:

create table test (
    col1    integer,
    col2    integer,
    col3    text,
    unique (col1),
    check (col1 is not null)
);

then the excluding constraints would have succeeded. But if the table has a primary key defined, then all the columns of this primary key are created with the not null modifier and a create .. like test will copy these modifiers.

Your workaround is valid. Either of these will work:

create temporary table t as select * from test where false ;
create temporary table t as select * from test limit 0 ;


Optimize this query: SQL I have this query in MySQL:

SELECT
    p.idSentence,
    p.idPath,
    p.token,
    p.posTag,
    p.ner,
    p.isTV,
    r.relationType,
    t.tag
FROM path p
 LEFT OUTER JOIN relation r ON(p.idTokenN=r.idTokenN)
 JOIN contentDoc t ON p.idContDoc=t.idContDoc
 JOIN (
    SELECT idPath,
           MAX(isTV='true') AS hasTV,
           COUNT(*) AS pathLength
    FROM path
    GROUP BY idPath

  ) pf
 ON p.idPath=pf.idPath
    AND pf.hasTV =1
    AND pf.pathLength BETWEEN 2 AND 5
 JOIN (
     SELECT idSentence,COUNT(DISTINCT(idPath)) AS paths
     FROM path
     GROUP BY idSentence
    ) ps
 ON p.idSentence=ps.idSentence
    AND ps.paths BETWEEN 1 AND 3 ;

Because the data are so many, the query takes a long time to run, how can I optimize it to speed it up?

Here is some recommentations:

Make sure all the columns used in the joins are indexed properly.

if possible, replace subqueries with indexed temporary tables.

Move when possible the filtering clause from the join statement to the where statement (e.g. Where  pf.pathLength BETWEEN 2 AND 5)

Try to move the joins order to start filtering with the table which reduce the more efficiently the returned dataset.


Denormalized tables within OLTP database My company has a product centered around a fairly normalized mssql server database.  At this point we have several reports and a web application that needs the data in a much flatter fashion to display the data appropriately to the user.  Today we are using views/stored procedures to provide flattened data model needed by the reports and web app.

Currently there are performance problems though with several of the views due to the amount of aggregate data and joins being performed inside the views.  My thought was to instead provide denormalized tables that could be optimized for performance for both the reports and the web application.  These denormalized tables would be built at some interval of time or maintained as updates happened to the underlying oltp tables.

Is this a common thing to do?  Am I asking for trouble as far as performance just keeping these tables up to date?  What mechanisms are the best for building these sort of tables?  Is there a better way to do this than these denormalized tables?

What you are thinking about is relatively common and pre-processing the data can indeed help the performance of reports and web pages.  The 'cost' to this is that the pre-processing takes time, so your denormalized tables (you can think of this as a  mini-OLAP Data Warehouse) are never fully up-to-date.

I do not consider that to be a big problem, but just one that your users need to understand.  (E.g. - "I just finished the xyz transaction and it is not in my report.")

Any reports that need the latest transaction will need to include at least some elements from the live data, instead of the pre-processed "OLAP" data.

Here is a generic link explaining the relationship. http://datawarehouse4u.info/OLTP-vs-OLAP.html

The web page also explains some nuances of the OLTP/OLAP divide, but there is nothing really surprising in the description.


Optimizing numeric range (interval) searches in SQL Server This question is similar to Optimizing IP Range Search? but that one is restricted to SQL Server 2000.

Suppose I have 10 million ranges provisionally stored in a table structured and populated as below.

CREATE TABLE MyTable
(
Id        INT IDENTITY PRIMARY KEY,
RangeFrom INT NOT NULL,
RangeTo   INT NOT NULL,
CHECK (RangeTo > RangeFrom),
INDEX IX1 (RangeFrom,RangeTo),
INDEX IX2 (RangeTo,RangeFrom)
);

WITH RandomNumbers
     AS (SELECT TOP 10000000 ABS(CRYPT_GEN_RANDOM(4)%100000000) AS Num
         FROM   sys.all_objects o1,
                sys.all_objects o2,
                sys.all_objects o3,
                sys.all_objects o4)
INSERT INTO MyTable
            (RangeFrom,
             RangeTo)
SELECT Num,
       Num + 1 + CRYPT_GEN_RANDOM(1)
FROM   RandomNumbers

I need to know all ranges containing the value 50,000,000. I try the following query

SELECT *
FROM MyTable
WHERE 50000000 BETWEEN RangeFrom AND RangeTo

SQL Server shows that there were 10,951 logical reads and nearly 5 million rows were read to return the 12 matching ones.

Can I improve on this performance? Any restructuring of the table or additional indexes is fine.

Columnstore is very heplful here compared to a nonclustered index which scans half the table. A nonclustered columnstore index provides most of the benefit but inserting ordered data into a clustered columnstore index is even better.

DROP TABLE IF EXISTS dbo.MyTableCCI;

CREATE TABLE dbo.MyTableCCI
(
Id        INT PRIMARY KEY,
RangeFrom INT NOT NULL,
RangeTo   INT NOT NULL,
CHECK (RangeTo > RangeFrom),
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.MyTableCCI
SELECT TOP (987654321) *
FROM dbo.MyTable
ORDER BY RangeFrom ASC
OPTION (MAXDOP 1);

By design I can get rowgroup elimination on the RangeFrom column which will eliminate half of my rowgroups. But due to the nature of the data I also get rowgroup elimination on the RangeTo column as well:

Table 'MyTableCCI'. Segment reads 1, segment skipped 9.

For larger tables with more variable data there are different ways to load the data to guarantee the best possible rowgroup elimination on both columns. For your data in particular, the query takes 1 ms.


Auditing of modification of change to encryption keys Is it possible to audit/log any attempts or actual changes to the keys used in the encryption hierarchy?

We are using 2014 Enterprise edition and have recently enabled TDE across our databases and would like to add changes the the encryption keys to our auditing.

As we have recently upgraded to enterprise edition to allow us to enable TDE, I wondered if SQL Server Audit would have this out of the box?

Turns out SQL Server Audit  has this out of the box.

Querying the contents of sys.dm_audit_actions revealed all the audit actions available and the Audit action types that I needed to add to the audit

Once I added DATABASE_OBJECT_CHANGE_GROUP and SERVER_OBJECT_CHANGE_GROUP to my audit and enabled it they appeared in the log.


What's the most efficient way to perform a case insensitive search for a word in a text field? What is the most efficient way to perform a case insensitive search for a word in a TEXT (or CLOB) field?

I've been using WHERE LOWER(body) LIKE '%word%' but I think the calls to LOWER() might be slowing the query significantly.

In PostgreSQL, you can use Text Search:

create index on foo_table using gin ( to_tsvector('simple', body) );

select
*
from foo_table
where
  to_tsvector('simple', body) @@ plainto_tsquery('simple', 'word');


INSERT OR REPLACE INTO + SELECT as source in Oracle I have two tables A and B.

I want to insert B's content into table A. The issue is there's a row in B that already exists in A.

I've searched and I found some answers talking about MERGE, but no one says how to use it with a SELECT as source.

In summary, I would like to use this statement

INSERT INTO A (data1, data2, dataX)
SELECT B.data1, 'String', B.data2, B.data3
FROM B;

But avoiding existing rows.

Thank you!

Table1: A (DATA1, DATA2, DATA3)
Table2: B (DATA1, DATA2, DATA3)

Assumption: First fieldName of both table is primary key

MERGE INTO A TA
    USING (SELECT * FROM B) TB
    ON (TA.DATA1 = TB.DATA1)
    WHEN NOT MATCHED THEN INSERT (TA.DATA1, TA.DATA2, TA.DATA3)
      VALUES (TB.DATA1, TB.DATA2, TB.DATA3);


What's causing [ERROR] Invalid (old?) table or database name 'd:q'? I've two entries in my MariaDB 10.5 error log file last night:
[ERROR] Invalid (old?) table or database name 'd:q'

I've looked at my nginx access log around that time and can't see any unusual entries.  I've searched for that error with the d:q bit and got no hits.  Anyone know what this could be?  Does it suggest my server has been exploited in some way...?
Thanks

The following day I got the same errors at exactly the same time (2am and 2:05am), so looked into it more.
The :q felt like a bit of a clue to me.  I couldn't really understand how I could have accidentally affected MariaDB when (failing) to quit vim... but I went looking in my /var/lib/mysql directory, and sure enough there was an empty directory d:q.  Deleted it and I'm assuming the problem will go away.


How to create a custom view (using lookup) from aggregated data I have data imported into a postgresql database:

groups (data import)
id | csv_user_ids
1  | 1,2,3
2  | 4,5,6
3  | 3,1,4

... and a lookup table for user names:

users (lookup)
id | name
1  | one
2  | two
3  | three
4  | four
5  | five
6  | six

I need to build a VIEW of groups with JSON column matching the user IDs as such

groups_view
id | json_array_users
1  | [{ 'id': 1, 'name': 'one'}, { 'id': 2, 'name': 'two'}, { 'id': 3, 'name': 'three'}]
2  | [{ 'id': 4, 'name': 'four'}, { 'id': 5, 'name': 'five'}, { 'id': 6, 'name': 'six'}]
3  | [{ 'id': 3, 'name': 'three'}, { 'id': 1, 'name': 'one'}, { 'id': 4, 'name': 'four'}]

How do I do that?

I guess I'd need to disaggregate rows in groups before aggregating into my new view again?

  JSON result is an array.

source:

groups (data import)
id | csv_user_ids
1  | 1,2,3
2  | 4,5,6
3  | 3,1,4

to:

group_user
group_id | user_id
1        | 1
1        | 2
1        | 3
2        | 4
2        | 5
2        | 6
3        | 3
3        | 1
3        | 6

How do I do that in SQL?

With string_to_array(text, text [, text]) from doc functions array, I was able to turn the CSV string into an array e.g. {1,2,3}

groups (data import)
id | csv_user_ids
1  | 1,2,3        => {1,2,3}
2  | 4,5,6        => {4,5,6}
3  | 3,1,4        => {3,1,4}

after what I tried array_to_json(anyarray [, pretty_bool]) doc functions-json ...

To disaggregate
SELECT id,
   json_array_elements(array_to_json(string_to_array(csv_user_ids, ',')))
FROM groups

string_to_array converts the CSV string into ARRAY
array_to_json   converts ARRAY into JSON
json_array_elements each element into a separate row

next I just need to aggregate again.


Restricted logins with automatic full permission in databases My scenario:
I have some groups with full - and inappropriate - access to all Sql Server instances. I want to change that.

My Plan:
Give full and automatic access to all databases (it is customer databases and this groups must be allowed to change any property if necessary).

Restrict permissions on Instance Level, because this groups should not be allowed to do any unexpected change on instance properties.

I don't want to create a user in databases, because our external customer could drop the user ( they have db_owner permission )

Someone have any idea?
Tks

You can create a user that has certain non-db_owner fixed database roles assigned, and then you can create a user-defined role which contains additional permissions. (See https://msdn.microsoft.com/en-GB/library/ms189121.aspx)

So in effect, you can create a user that has all the rights of db_owner except say, managing users and dropping database. But I don't think there's a way to directly deny such privileges, you have to instead add everything but them.

Your database creation SP can configure new databases in this way, and you’d have to do it for each existing database.


How to get "mysqlbinlog" utility for Ubuntu? I can't find "mysqlbinlog" utility in my Ubuntu server, i can see mysql, mysqldump, mysqlshow etc... command line utilities but not mysqlbinlog utility.

Can someone please help me from where I can get this utility and install it?

mysqlbinlog utility is in mysql server package. You should try install mysql server.


VoltDB Logging Level How much (estimated percentage) is going to worsen the response-time of the database if the logging level is lowered to "DEBUG". ?

I work at VoltDB. I wouldn't recommend setting the overall logging level to DEBUG except perhaps for a DEV environment for limited tests. It's hard to say what % the performance impact would be. Everything might be a bit slower, but there could be a lot more GC triggered which can have an unpredictable impact.

You can adjust the level for specific loggers, if you have an idea of the area of functionality where you want to see more detail. We have a howto guide on configuring logging here: https://github.com/VoltDB/voltdb/blob/master/examples/HOWTOs/ConfigureLogging.md


Is it possible to optimize this query? Currently taking days (no sign of stopping) on ~11 million records Table in question:

CREATE TABLE `observations` (
  `time_of_observation` datetime NOT NULL,
  `station` varchar(5) NOT NULL,
  `number` varchar(20) NOT NULL,
  `destination` varchar(100) NOT NULL,
  `type` varchar(20) NOT NULL,
  `departure_time` datetime NOT NULL,
  `owner` varchar(20) NOT NULL,
  `altered` tinyint(1) NOT NULL DEFAULT '0',
  `delay` bigint(20) NOT NULL DEFAULT '0',
  `notes` text CHARACTER SET latin1,
  `route_text` text,
  `info` text CHARACTER SET latin1,
  `delay_text` text CHARACTER SET latin1,
  KEY `uindex2` (`time_of_observation`,`departure_time`,`number`,`delay`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

With index:

+--------------+------------+----------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| observations |          1 | uindex2  |            1 | time_of_observation | A         |       58588 |     NULL | NULL   |      | BTREE      |         |               |
| observations |          1 | uindex2  |            2 | departure_time      | A         |     5507365 |     NULL | NULL   |      | BTREE      |         |               |
| observations |          1 | uindex2  |            3 | number              | A         |    11014731 |     NULL | NULL   |      | BTREE      |         |               |
| observations |          1 | uindex2  |            4 | delay               | A         |    11014731 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Query:

SELECT time_of_observation as last_seen_on, number, departure_time,
       departure_delay as last_known_delay
FROM observations as o1
WHERE NOT EXISTS (
  SELECT 1
  FROM observations o2
  WHERE o1.number = o2.number
  AND o1.departure_time = o2.departure_time
  AND o1.time_of_observation < o2.time_of_observation
);`

number and departure_time uniquely identify an observed entity. Goal of this query is to find for each entity the last observation and the delay seen on this last observation.

This query is currently running for 140 hours (and has not finished) with the table containing ~11 million rows.

See EXPLAIN:

+------+--------------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
| id   | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows     | Extra                    |
+------+--------------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
|    1 | PRIMARY            | o1    | index | NULL          | uindex2 | 86      | NULL | 11014731 | Using where; Using index |
|    2 | DEPENDENT SUBQUERY | o2    | index | uindex2       | uindex2 | 86      | NULL | 11014731 | Using where; Using index |
+------+--------------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+

Am I doing something wrong here? Is it possible to optimize this query? Should it be this slow?

First of all you need to get rid of the dependent subquery.
For this you could rewrite your query like this:

SELECT o.time_of_observation as last_seen_on, o.number,
       o.departure_time, o.departure_delay as last_known_delay
FROM observations as o
JOIN (  SELECT
        number,
        departure_time
        MIN(time_of_observation) AS minobstime
        FROM observations
        GROUP BY
        number,
        departure_time
) m ON m.number = o.number
   AND m.departure_time = o.departure_time
   AND m.minobstime = o.time_of_observation;

or this:

SELECT o1.time_of_observation as last_seen_on, o1.number,
       o1.departure_time, o1.departure_delay as last_known_delay
FROM observations as o1
LEFT JOIN observations as o2 ON o1.number = o1.number
  AND o1.departure_time = o2.departure_time
  AND o1.time_of_observation < o2.time_of_observation
WHERE o2.<any column, preferably your primary key> IS NULL;

Then it would be a good idea if you had a primary key. It can be an auto_increment column, too.

The columns in the index you have are in the wrong order. time_of_observation should be the last column in the index. First should be the columns where you make an equality comparison, then the ones where you scan a range.

Create an index like (number, departure_time, time_of_observation) and try one of the two queries from above.


How to pass a set or unnamed relation into a PostgreSQL function? In PostgreSQL, there are Set-Returning-Functions (SRF). But I'm wondering if there is also a way to pass an unknown relation into a function? In other words, is it possible to write Set-Taking-Functions (so to speak), other than passing the name of the tuple-set?

Obviously, there is dynamic SQL where one can pass a table name. With pgRouting, one can even use an entire string to pass the set of tuples into the function:

SELECT * FROM shortest_path('
   SELECT gid AS id,
          start_id::int4 AS source,
          end_id::int4 AS target,
          shape_leng::float8 AS cost
   FROM network',
1,
5110,
false,
false);

But the SQL string in the parameter does not seem very secure. The other possibility is to store the result into a temporary table first and pass the name of the temporary table, which seems to be against the idea of set-based programming in SQL.

Are there any alternative ways of passing a set into a function?

You could define a record type that represents one tuple of what you want to pass, then pass an array of that type:

create type path_info
(
  id  integer,
  source integer,
  target integer,
  cost float
);

Then pass an array to your function:

select *
from shortest_path((select array_agg( (gid,start_id,end_id,shape_leng)::path_info )
                    from network),
                   ...);

The function parameter needs to be defined as path_info[]:

create function shortest_path(p_path_info path_info[], ...)
  returns ...
as
...

Depending on what you are doing with those values, a function that receives a single record used with a lateral join might be more efficient:

Something like:

select *
from network n
  join lateral shortest_path( (n.gid, n.start_id, n.end_id, n.shape_leng)::path_info, ...) on ....


How to create Clustered and non clustered index in Oracle? I dug couple of hours regarding my question and didn't get satisfactory answer. Still I have doubt. I have found the following about Clustered Index:

Data is stored in the order of the clustered index.
Only one clustered index per table.
When a primary key is created a clustered index is automatically created as well.

I got these points, but my questions are:

Does Clustered index exist in Oracle database? since I read in some blogs "Oracle does not have a concept of a clustered index."
If yes, please let me know the sql statement to create a clustered index.
As said above, clustered index automatically gets created when primary key is defined on a column of a table, how can I check the index type if it is created or not ?

Please find my table architecture :

Let me know if anything else is required to get answers for these questions.

Does Clustered index exist in Oracle database? since I read in some blogs

Yes there is.
It is called "index organized table" (IOT) - which in my opinion is the better name as it makes it absolutely clear that the index and the table are the same physical thing (which is the reason why we can have only one clustered index in SQL Server)

If yes, please let me know the SQL statement to create a cluster index.

There is no such thing as create clustered index in Oracle.
To create an index organized table, you use the create table statement with the organization index option.
In Oracle you usually use IOTs for very narrow tables. Very often for tables that only consist of the primary key columns (e.g. m:n mapping tables), e.g.
create table assignment
(
   person_id  integer not null,
   job_id     integer not null,
   primary key (person_id, job_id)
)
organization index;

You can create IOTs with more column, in that case the you need to define the non-pk columns as "included" columns. E.g. if the assignment table should have additional columns, like start and end date that are not part of the primary key:
create table assignment
(
   person_id   integer not null,
   job_id      integer not null,
   start_date  date,
   end_date    date,
   primary key (person_id, job_id)
)
organization index
including start_date
overflow storage (initial 4k);

See the manual for more details and examples: https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2153235

Somewhat unrelated, but maybe interesting anyway:
An interesting blog post that questions SQL Server's (and MySQL's) behaviour of using a clustered index as the default when creating a table:
Unreasonable Defaults: Primary Key as Clustering Key
Coming from an Oracle background I wholeheartedly agree with that.


DROP TABLE on a huge InnoDB table I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query?

processor: Intel® Xeon® E3-1220 4 Cores x 3.1 GHz (3.4 Turbo Boost)
ram: 12 GB DDR3 ECC

Perhaps you can disable foreign key checks and unique checks:

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
DROP TABLE ...;

This has to work since a standard mysqldump embeds commands like these and a DROP TABLE before creating and reloading it. I have written about before

Jun 11, 2013 : Speeding up mysqldump / reload
May 26, 2013 : Is it possible to force drop of mysql tables with FK?
Jul 09, 2012 : What can cause TRUNCATE TABLE to take a really long time?

Further Suggestion : Based on a post from mysqlperformanceblog, you should get Percona Server or upgrade to the latest version of MySQL (5.5.23 has improvements to DROP TABLE, so 5.6 should have those improvements as well).

As for your current DROP TABLE, you must let it ride for maintain InnoDB's stability.


performance impact when renaming a column in 100M rows table We are running SQL Server 2012 Standard Edition.

Need to change the name of a column in a table with +100M rows.

This table is heavily used at all times.

I can't say at this moment if the column in question is part of an index (should I expect different behavior in case it is?)

What is the performance impact I should expect when running:

EXEC sp_rename 'myTable.oldColumnName', 'newColumnName','COLUMN'

EDIT: This column was created for future needs, we know is it not used in any part of the system. It has only null values. I'm offline right now, not sure if it is included in an index (by mistake)

EDIT 2:
Most transactions on this table have with(nolock) hint. Do you think running sp_rename at isolation lever read uncommitted is the way to go?

The size of the table does not matter. Changing the column name just changes metadata in the table. However, there's no concept of a column metadata lock in SQL Server. Renaming a column requires a schema modification lock on the table. Here's what the lock request can look like:

<Database name="TEST">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="rename_col_test" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-M" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>
  </Objects>
</Database>

Under the default isolation level, after you issue the rename it will be blocked by any queries that were already running with any kind of lock on that object. Any queries kicked off after your rename request that require a lock on the table will be blocked by the rename.

Here's an example of how a blocking chain can form:

Session 52 is a long running SELECT that holds a lock on the table. Session 53 tries to rename the table. Session 54 is another SELECT query that is compatible with the locks taken by session 52, but it is blocked by the rename.


SQL Server 2014 uses high memory at startup The process of SQL Server 2014 is using high memory even it's idle and no query is running on. after restarting the service, it happens again and memory usage grows to 2.2GB after a while.

the host is Windows Server 2012.

Why? and how can I find the problem?

It's not unusual for SQL Server to have rather high memory utilization, even when the server is idle.

SQL Server will increase memory utilization up to the configured maximum--then hold on to that memory, even if it is not being actively used. This increase in memory usually corresponds to data being read from disk, into memory, and held in cache (thus improving the performance the next time that data needs to be used). The only time you might see SQL Server release memory is if the system is under extreme memory pressure and SQL Server is also idle.

The default configuration for SQL Server is to use a minimum of 16 MB, and a maximum of 2147483647 MB (ie, unlimited). Under this configuration, you are essentially allowing SQL Server to self-manage it's own memory.

To see how your instance of SQL Server is configured, you can run this in Management Studio to see the minimum & maximum memory configuration:

EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'min server memory (MB)'
EXEC sp_configure 'max server memory (MB)'

If you wanted to configure SQL Server to allocate 128MB of memory immediately on startup, and max out at 1024MB, you would use this script:

EXEC sp_configure 'min server memory (MB)',128
EXEC sp_configure 'max server memory (MB)',1024


What's the easiest way to create a temp table in SQL Server that can hold the result of a stored procedure? Many times I need to write something like the following when dealing with SQL Server.

create table #table_name
(
    column1 int,
    column2 varchar(200)
    ...
)

insert into #table_name
execute some_stored_procedure;

But create a table which has the exact syntax as the result of a stored procedure is a tedious task. For example, the result of sp_helppublication has 48 columns! I want to know whether there is any easy way to do this.

Thanks.

If the procedure just returns one result set and the ad hoc distributed queries option is enabled.

SELECT *
INTO #T
FROM OPENROWSET('SQLNCLI',
                'Server=(local)\MSSQL2008;Trusted_Connection=yes;',
                 'SET FMTONLY OFF;EXEC sp_who')

Or you can set up a loopback linked server and use that instead.

EXEC sp_addlinkedserver @server = 'LOCALSERVER',  @srvproduct = '',
                        @provider = 'SQLNCLI', @datasrc = @@servername

SELECT *
INTO  #T
FROM OPENQUERY(LOCALSERVER,
               'SET FMTONLY OFF;
               EXEC sp_who')


Accelerated log growth The log file for my SQL Server 2014 database is growing rapidly (currently in 32 GB in COMPLETE RECOVERY mode).  This is creating storage issues on my server.
The database has a set of job's with large SELECT - UPDATE - DELETE from another DB occurring at different times of the day.

How do I avoid rapid growth of the log file?
What practices should I use to minimize log growth?
COMMIT TRANSACTION is generally not used in STORE PROCEDURES, is it essential to add them as part of improvement?
Researching I read that I should use TRUNCATE TABLE, WITH (NOLOCK) as part of optimization, I would like to know your opinions.

Sorry for my English, thank you for your support.

The most important consideration is to pick the right database recovery model that meets the business needs. If you're using a recovery model of full then you also need to backup the log on an appropriate frequency. There's also a question of scale here. If your log is 32 GB in size for a 10 GB database then I understand why you're considered. However, if that log file is for a 10 TB database then the right thing to do might be to allocate more space for the transaction log files. Be careful in how you think about the log files. It's a good thing that protects against data loss. It shouldn't be viewed as your enemy that should shrunk as much as possible.
With all of that said, there may be different ways of writing code which lead to the same results but reduce total writes on the transaction log file. Taking advantage of possible optimizations in this space can improve performance. Some high level tips:

If your business needs allow you to change the recovery model to simple then some code may qualify for minimal logging.
There may be an opportunity to change code to use temp tables instead of user tables. Tempdb always has a recovery model of simple so you can get minimal logging for those operations.
Your application design may allow for the use of a scratch database to hold temporary data, such as a staging database. Your business requirements may allow the staging database to have a recovery model of simple while keeping the recovery model of the real database as full.
Getting data Right The First Time can reduce log writes. For example, a single INSERT instead of an INSERT + UPDATE will often write less to the log. Same concept applies to avoiding operations which delete large numbers of rows, if possible.
SQL Server acquired locks need to be written to the transaction log as well. In high scale scenarios you may see an improvement by adding TABLOCK hints to reduce transaction log activity.


Can I programmatically generate reports that are emailed as Excel docs from SSRS? I've searched already and don't see (or can't decipher) a solution for my issue. I have SSRS emailing a report for 1 district (with 12 retail stores). However, each Region has over 8 Districts, and I need to put each District on its own tab. I have multiple tabs working, but I'd like to avoid creating each sheet if possible. I also need to generate 4 of these master spreadsheets - is there a way to make some sort of template for one worksheet using parameters?
We're using SQL Server 2008 R2 and Report Builder 3.0

Ini. My first thought here is that you could use a List control to iterate across Districts. SQL Server MVP Jes Borland has a great intorductory post on Lists here: http://jesborland.wordpress.com/2010/11/17/the-power-of-reporting-services-%E2%80%93-lists/

Inside the list control will be a rectangle. By setting the Page properties of that rectangle, you can create yours tabs for each District, dynamically setting the Page Name property to an expression that returns the District.

You could configure your dataset (ideally, a stored procedure) to take the Region as a parameter and then you can run this ONE report for each of your Regions and they will all work the same and keep you from having to create multiple copies of the same object.

Assuming I understood your question correctly, this should get you moving in the right direction.


Should a Sql Server be accessible through the service's virtual network name? In a working SQL-Server failover cluster environment I can open a SQL-Server connection using the configured service's IP address independantly of the current node it is on. I wonder if the assigned virtual network name should allow to be used as well to connect? Actually it's not possible to use it.

So instead of 10.20.30.40\instance1 should I be able to connect using sqlnetwork01\instance1 as well? Do I face a configuration mistake if I can't or do I have a misunderstanding of the concepts?

Edit:
sqlnetwork01\instance1 is accessible from the same domain, my mistake was to try from a different one where it does not answer.

Given CLUSTER_1, with NODE_1 and NODE_2, and virtual network VNN_1 with IP_1, these will work:

VNN_1\INSTANCE
IP_1\INSTANCE

These will not work:

CLUSTER_1\INSTANCE
NODE_1\INSTANCE
NODE_2\INSTANCE

If you are unable to connect using VNN_1\INSTANCE then, can you ping it, and what does it resolve to? Is it configured for a single IP (i.e. not multi-subnet)?


Check for changes in a text column Using SQL Server 2012, how can I check for changes in a text column?

I know the text datatype has been deprecated, and get the ever helpful message

  Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

when trying to reference the existing text column in a trigger, so things like joining inserted & deleted tables on the column does not work.  UPDATE() would work, except it is always true (I am guessing the front end just pushes whatever data is in the form field regardless if it changed or not).

Any suggestions on how I can check for changes on the column?

Can you implement an INSTEAD OF UPDATE trigger? - a quick test on my local instances indicates you can reference the inserted and deleted tables.  Naturally, you still have to apply the real update.

here was my quick test

create table TextDataTypeTest (Col1 text)
insert into dbo.textdatatypetest (col1) values('test')
----------------
create TRIGGER dbo.TextDataTypeTest_Trigger
   ON  dbo.TextDataTypeTest
   instead of UPDATE
AS
BEGIN

select col1 into #Before from deleted
select col1 into #After from inserted

select * from #Before

select * from #After
update a
set a.col1=b.col1
from dbo.textdatatypetest a join
inserted b on 1=1

END
GO

update dbo.textdatatypetest set col1='oneforalx'


Optimize finding events with overbooked attendees I have the following strcture to store events and their attendees:

CREATE TABLE `plan_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start_datetime` datetime DEFAULT NULL,
  `end_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `start_datetime_idx` (`start_datetime`),
  KEY `end_datetime_idx` (`end_datetime`),
  KEY `start_end_datetime_idx` (`start_datetime`,`end_datetime`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `plan_event_attendee` (
  `event_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`event_id`,`user_id`),
  KEY `IDX_E8625CBD71F7E88B` (`event_id`),
  KEY `IDX_E8625CBDA76ED395` (`user_id`),
  CONSTRAINT `FK_E8625CBD71F7E88B` FOREIGN KEY (`event_id`) REFERENCES `plan_event` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_E8625CBDA76ED395` FOREIGN KEY (`user_id`) REFERENCES `plan_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I would like to find all events where someone is attending in another event at the same time (no need to know who exactly, just which events has at least one of such attendee). This is what I have so far:

    SELECT e1.id, e2.id
      FROM plan_event e1
INNER JOIN plan_event e2
        ON e2.start_datetime < e1.end_datetime
       AND e2.end_datetime > e1.start_datetime
       AND e2.id <> e1.id
INNER JOIN plan_event_attendee a1
        ON a1.event_id = e1.id
INNER JOIN plan_event_attendee a2
        ON a2.event_id = e2.id
     WHERE a1.user_id = a2.user_id
       AND e1.start_datetime > '2015-06-01'
       AND e1.end_datetime < '2016-06-01'

Unfortunately with the given dataset the query is not performant enough, likely caused by the exponential nature of joining tables to themselves. EXPLAIN gives me the following output:

+----+-------------+-------+--------+--------------------------------------------------------------------+------------------------+---------+------------------+-------+--------------------------+
| id | select_type | table | type   | possible_keys                                                      | key                    | key_len | ref              | rows  | Extra                    |
+----+-------------+-------+--------+--------------------------------------------------------------------+------------------------+---------+------------------+-------+--------------------------+
|  1 | SIMPLE      | e1    | range  | PRIMARY,start_datetime_idx,end_datetime_idx,start_end_datetime_idx | start_end_datetime_idx | 9       | NULL             | 26640 | Using where; Using index |
|  1 | SIMPLE      | a1    | ref    | PRIMARY,IDX_E8625CBD71F7E88B,IDX_E8625CBDA76ED395                  | PRIMARY                | 4       | aire.e1.id       |     4 | Using index              |
|  1 | SIMPLE      | a2    | ref    | PRIMARY,IDX_E8625CBD71F7E88B,IDX_E8625CBDA76ED395                  | IDX_E8625CBDA76ED395   | 4       | aire.a1.user_id  |   475 | Using where; Using index |
|  1 | SIMPLE      | e2    | eq_ref | PRIMARY,start_datetime_idx,end_datetime_idx,start_end_datetime_idx | PRIMARY                | 4       | aire.a2.event_id |     1 | Using where              |
+----+-------------+-------+--------+--------------------------------------------------------------------+------------------------+---------+------------------+-------+--------------------------+

What other performance optimizations could I attempt or this the best I can get?

The query itself looks pretty much the way to do it. As for indexing strategies I suggest that you drop indexes:

KEY `start_datetime_idx` (`start_datetime`) -- covered by start_end_datetime_idx

KEY `IDX_E8625CBD71F7E88B` (`event_id`) -- covered by PRIMARY

and replace:

KEY `IDX_E8625CBDA76ED395` (`user_id`),

with:

KEY `IDX_E8625CBDA76ED395` (`user_id`, event_id)


Can PostgreSQL support integration test with some kind of throwaway overlay? It's a common problem to write integration tests that include a database. If the test changes the database then it could effect other tests or the next run of itself.

I know that I could wrap my test in a transaction and rollback the transaction after the test run. But it would be very nice if PostgreSQL could provide some kind of global snapshoting or throwaway overlay. In an ideal case such a feature would cover all state of the database including schemas and stored procedures.

One way you could approach this, is to create a "base" test database (including all test data) once. Then before each test suite, create a new database using the "base" database as the template.

create database test_db
   with template = base_test_db;

Using this, everything that is in the template will be copied to the new database (including data, stored procedures, extensions).

After each test suite, you simple drop test_db and re-create it with the above statement.

This approach might not be fast enough if you do it before each test, but that highly depends on how large your test database is and how fast the harddisk of your build environment is.


MySQL Database Temporarily Compromised? Two days ago, I had connected to my ClearDB test server with MySQL workbench. I had left this workbench open and when I had checked it today, there were two new schemas named "test_justin" and "test_vishnu". I promptly reset my connection and sure enough, they were gone.

I have since reset the username and password for my database (which should have been secure enough in the first place). My questions are:

Has anyone encountered this as a bug in MySQL workbench or ClearDB?
How would an attacker create a schema when even my user account does not have permissions to do so?
What are some safety precautions I should be taking to ensure my database is not compromised again?

No worries my friend! Your database was not compromised.

The systems administrator had this problem as well this week. We found out after he cursed across the room! After sending a call to our hosting provider, Heroku, it turned out that they were doing a test on a selected amount of machines (most likely on their Cedar stack) and created test schemas as a result.


Replication in mysql with many servers There are 3 shops.  In each of these is a mysql server.  I want all servers to synchronize their date if at least one server changes the date. I read a bit of literature and realized that I need master master replication. However, I must take into account the fact that one or more of the databases can work a couple of hours in offline mode (e.g. shut down Internet).  How will this work in this case? Will there be duplication of column with Primary key? if so then do I need to take this into account?

Advise me, please. I'm new to mysql.

If your Servers get out of sync they play back the events from the binlog, so as long you have all statements writen into your binlog, the master/slave is able to catch up. I had to catch up 12days without any problems, but in a cluster this is not what you want to happen.
And be careful when using "unsafe" statements, this are statements which the server can't reproduce on your master/slave setup.

Mysql Article on safe/unsafe statements - http://dev.mysql.com/doc/refman/5.1/en/replication-rbr-safe-unsafe.html


Oracle database connection instant timeout I have to check automatically if a connection to another database in oracle is vaild. I want a trigger to perform different actions based on whether the connection is vaild while it's running or not. It takes about 40 seconds every time to return ORA-12170 (Connection timeout) if i try to check the connection by using a query including the database link to the remote database. Is there any way to set a much shorter time interval for the timeout or to abort the query if it takes that much time? I just want to get a quick response if the connection is working. The trigger is called several times every view seconds, so i can't wait for 40 seconds every time it's called when the connection is interrupted.
Im using Oracle XE 11g.

That timeout can be configured with the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter.

For example, place the below in sqlnet.ora, which results in outbound connections timing out after 10 seconds without answer:

SQLNET.OUTBOUND_CONNECT_TIMEOUT=10


Mysql Selecting from multiple tables I have the following tables:

-- Table structure for table `Operator`
--

CREATE TABLE Operator (
  OperatorID int(11) NOT NULL,
  Name varchar(25) NOT NULL,
  Street varchar(25) NOT NULL,
  Town varchar(25) NOT NULL,
  Postcode varchar(25) NOT NULL,
  Email varchar(35) NOT NULL,
  Phone varchar(20) NOT NULL,
  PRIMARY KEY (OperatorID)
)

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

--
-- Table structure for table `Route`
--

CREATE TABLE Route (
  RouteNumber varchar(5) NOT NULL,
  Frequency int(11) NOT NULL,
  Start varchar(5) NOT NULL,
  Destination varchar(5) NOT NULL,
  PRIMARY KEY (RouteNumber)
)

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

--
-- Table structure for table `RouteOperators`
--

CREATE TABLE RouteOperators (
  RouteNumber varchar(5) NOT NULL,
  OperatorID int(11) NOT NULL,
  Proportion int(11) NOT NULL,
  PRIMARY KEY (RouteNumber, OperatorID)
)

I'm attempting to retrieve the phone numbers and emails of operators who serve bus stop 7628 (i.e Start or Destination = 7628) with the following query:

SELECT Phone,Email FROM Operator, Route, RouteOperators
WHERE (Route.Start = 7628 OR Route.Destination = 7628)
AND RouteOperators.RouteNumber = Route.RouteNumber
AND Operator.OperatorID = RouteOperators.RouteNumber

The query doesn't return anything, although there are also no errors. Can anyone see what I'm doing wrong?

You probably meant AND Operator.OperatorID = RouteOperators.OperatorID

I'd suggest using the JOIN...ON (...) syntax, it makes the query more readable.

SELECT o.Phone,o.Email
FROM Route r
INNER JOIN RouteOperators ro
    ON r.RouteNumber = ro.RouteNumber
INNER JOIN Operator o
    ON ro.OperatorID = o.OperatorID
WHERE Route.Start = 7628
OR Route.Destination = 7628

You might also want to consider introducing foreign key constraints.


Updating a table which contains an Image datatype column I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF.

I have a requirement to update one of the other columns in the table, however the query is taking a ludicrously long time to complete - that's if it does complete at all.

The table is the following format:

ID (int), HexData (image), FileName(nvar), Filetype(nvar), folder(nvar), user(int)

The query used is simply -

update [database].[dbo].[myTable]
set user = 5
where id = 2

This is a test table on a test database, not the live system. There are only 5 rows, no indexes (including clustered), and no other tables in the database.

The table in question is a heap. This storage structure does not release pages made empty by delete operations, unless the delete occurs while an exclusive table lock is taken (and not always then).

From DELETE (Transact-SQL) in the documentation:

  To delete rows in a heap and deallocate pages, use one of the following methods.
  Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take an exclusive lock on the table instead of a row or page lock. This allows the pages to be deallocated.

The upshot is that your table likely contains a large number of empty pages allocated for rows the heap held in the past. You can check this using:

SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.index_type_desc,
    DDIPS.alloc_unit_type_desc,
    DDIPS.index_depth,
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.record_count,
    DDIPS.ghost_record_count,
    DDIPS.version_ghost_record_count,
    DDIPS.forwarded_record_count
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.Heap', 'U'), -- Your table name here
        0,
        NULL,
        'DETAILED'
    ) AS DDIPS;

Without any indexes, the only way for the SQL Server to process the update query is to employ a full table scan. This will touch every data page allocated to the table, including the empty ones. If there are many pages that must be brought into memory from permanent storage, this can take a long time.

The solution is to rebuild the heap table using ALTER TABLE <table_name> REBUILD, or create a clustered index.

The image data type has been deprecated in favour of varbinary(max) for a very long time now. If you have any freedom to change this, you probably should. Even so, it is also possible that the existence of this LOB column is causing the slowness through a quite separate mechanism I describe in detail here on SQLblog.com:

Deletes that Split Pages and Forwarded Ghosts

From that article:

  Summary

  If you ever wonder why your deletes are so slow, it is worth checking to see if you are suffering from page splitting due to an enabled trigger and a table definition that allows for LOB allocations or ROW_OVERFLOW.

  Any table with a LOB column (including the max data types) qualifies, as does one with even a surprisingly small number of variable-length columns, as shown in the examples in this post.

  This is a great reason to avoid using 'max' or old-style LOB data types unnecessarily, and to be careful about the maximum length of 'normal' variable-length data types too.

  Remember, it is the potential maximum row size that is important, not the actual row size.


Postgresql.conf not found I was trying to install postgresql by this tutorial http://powa.readthedocs.io/en/latest/quickstart.html to use the tool for bd Powa.

I am use S.O. debian 8 Jessie.

I ran:

 apt-get install postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6
 apt-get install postgresql-9.6-powa

So far so good, the DBMS has gone up and I can create tables etc.

But I'm not finding where the postgresql.conf file is.
I tried searching using locate postgresql.conf, but can not find, I suspect it was not created.
I need to add some libraries in the parameter: shared_preload_libraries

Has anyone installed postgresql in this way, and could it tell you where the postgresql.conf file might be, or another one to configure the shared_preload_libraries parameter?

If you wish to know under which settings PostgreSQL is currently running, one good way to do it is to have a look at the pg_settings view:

SELECT name, setting, context
FROM pg_settings
WHERE name LIKE '%conf%';

I've added the context column to know how new values will be taken into account (a server restart, a config reload, ...).

You could also use the SHOW command:

SHOW config_file;


SQL cannot add columns to query I have the following query (PostgreSQL 9.5.19):

with userlist as (
  select a.ips,
         array_agg(t.super_user_id) as super_users
  from tmp_table_1 t
    left join unnest(t.ips) as a(ips) on true
  group by a.ips
), shared_users as (
  select u.ips,
         array(select distinct ul.uid
               from userlist u2, unnest(u2.super_users) as ul(uid)
               where u.super_users && u2.super_users
               order by ul.uid) as super_users
  from userlist u
)
select s.super_users, array_agg(distinct s.ips) as ips,
    (select sum(msg_count) from tmp_table_1 where super_user_id in s.super_users) as sum_msg_count
from shared_users s
group by s.super_users;

I get the following error:

ERROR:  syntax error at or near "s"
LINE 38: ...sg_count) from tmp_table_1 where super_user_id in s.super_us...
                                                              ^

It seems like I cannot access s.super_users from within the subquery.

Anyone know how I can do this?

Here is a full example:  https://www.db-fiddle.com/f/geB9V6TdHEkF2mSqpd2b4g/1

Maybe I am not approaching this correctly?

Question

How can I add the additional columns (sum_msg_count, sum_user_count, max_last_date and pubs) to the result set?

Desired result

|super_users                      |  ips                                                        |sum_msg_count| sum_user_count  |   max_last_date                | pubs                        |
|---------------------------------|-------------------------------------------------------------|-------------|-----------------|--------------------------------|-----------------------------|
|{522460771,522460772,522460773}  |  {94.134.88.115,94.134.88.124,94.134.88.125,94.134.88.136}  | 30          |         9       |  "2018-06-30 21:29:48.664000"  |  {ad2,googlecooler,ads,ad1} |
|{522460774,522460775}            |  {127.0.0.1,5.5.5.5}                                        | 20          |         6       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ad3,ad1}     |
|{522460776}                      |  {8.8.8.8}                                                  | 10          |         3       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ads}         |

You should use ANY, not IN to search in an array

...where super_user_id = ANY (s.super_users)