PostgresSQL copying newer entries from a table in one database to a table in a database on a remote machine I have two databases, I'm going to need to get more recent data from one database table and place it in another table that is on another PostgresSQL database on a different machine. The structure of the tables is identical. How is the best way to go about this?

Using dblink, you can copy records from one server to another. I installed dblink, using this Installation guide. The databases are the same and have the same table called sometable. This is similar to federated tables on MySQL and openquery on SQL Server.

The older server is listening on port 5432 and the new server is listening on port 5433. I only installed dblink on the new server. The dblink extension was only added to the receiving database on the new server. In my example, I didn't include the host because both postgresql servers are on the same machine.

Here are the basic steps I took using a 9.1 on a CentOS 6 server

Install dblink

-- go to the postgresl_source directory. If you installed via yum or apt-get you will have to install the postgresql_source packages

cd /postgresql_source_directory/contrib/dblink

-- install the dblink binaries

make install

-- Connect to 9.1 listening on 5433

psql -p5433

-- Connect to the database that will be receiving data

\connect somedatabase

-- Add in the dblink extension

CREATE EXTENSION dblink;

-- Verify that the extension exists

\dx
dblink  | 1.0     | public     | connect to other PostgreSQL databases from within a database

Copy the data to the new server

I am using a temporary storage table, because it made using an EXISTS statement easier when copying in new records.

-- truncate the temporary storage table on the new server

truncate table sometable_2;

-- copy in records to the temporary storage table on the new server

insert into sometable_2 select * from dblink('port=5432 dbname=somedatabase', 'select * from sometable') as t1(t int);

If your server is on a different ip address, you would run this instead

insert into sometable_2 select * from dblink('port=5432 dbname=somedatabase host=some.remote.ip.address', 'select * from sometable') as t1(t int);

-- Copy only new records into the new sometable

insert into sometable select * from sometable_2 st2 where not exists(select * from sometable st where st.t = st2.t);
INSERT 0 5

If I run the same queries again, only new records will be copied in. This is because the correlated subquery (select * from sometable st where st.t = st2.t) only retrieves records that don't already exist in the table "sometable" on the new server.


Kerberos Issue - Anonymous Login I have a linked server that connects one SQL Server to another SQL Server. For two weeks now there has been an error when testing the connection;

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (.Net SqlClient
Data Provider)

I have posted about this issue when I first received the user was complaining about it. The projected solution was to manually register the SPN. Our environment already has SPN registered to the service account names for each of the servers. Below are the ways me and my team troubleshooted:

Used the Kerberos Configuration tool to check if there were any issues with SPNs. Yes there was in fact an issue with multiple SPNs which were shown as "Misplaced" in the Kerberos tool. So we updated the SPN's and the status returned to "Good"

Checked CMD -setspn -l to view the list of the services and the service we are looking for with the correct port number is one of the service account under the SPN.

Restarted the SQL Server services to make sure the updates take full effect.

Used DMV to find out the authentication scheme being used. Kerberos authentication is being used.

Queried the data the user has requested to see that the anonymous issue is still present.

Tested the connection to the linked server and the anonymous issue is still present.

Checked the error log to see an error that states:

The SQL Server Network Interface library could not register the SPN
[XXXX] for the SQL Server service. Windows return code: 0x2098, state
15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.

I have notified the Windows AD team to check the delegation status. He has confirmed that the delegation is set to: 'Trust this user for delegation to any service (Kerberos only)'
I am running out of ideas on how much further I can troubleshoot or where the problem lies. If anyone has any other idea, please feel to share and give some feedback. What else can I do to resolve this issue? This issue has been persistent for 2 weeks now. And the users really need to access the data they need for their daily work. Thank you in advance.

Could be this.  Part of Security Patch that came out in Nov. & was updated in Dec.
This explains the problem:
https://support.microsoft.com/en-us/topic/managing-deployment-of-kerberos-s4u-changes-for-cve-2020-17049-569d60b7-3267-e2b0-7d9b-e46d770332ab
This contains the patch under Security Updates header:
https://msrc.microsoft.com/update-guide/vulnerability/CVE-2020-17049 (first 1 is for 2016 & up)
First, make sure all AD Servers are on the same patch.
Create "enforce modes" create reg key if not there. Set to 1.  Let sit 7 days to reset.
Change to reg key to 2. Restart Service or wait another 7 days.


How to shrink space on table with a function-based index? I have a partitioned table for which I'd like to remove unnecessary free space. Usually I would run commands like this:

ALTER TABLE {table name} MODIFY SUBPARTITION {subpartion name} SHRINK SPACE;
ALTER TABLE {table name} MODIFY PARTITION {partion name} SHRINK SPACE;

But I get an error for a particular table because the table has function-based indexes (see Restrictions on the shrink_clause):

  ORA-10631: SHRINK clause should not be specified for this object

All of the indexes on my table are local. There are no global indexes. Does anyone know a solution to shrink the free space?

Dropping function-based indexes, shrinking space and re-creating the indexes is not an option. My table is quite big (200 billion rows, resp. 20 TByte) so it would take far too much time. I need to shrink only a few subpartions, not the entire table.

I also tried to disable the function-based indexes on that partition (by using Partial Indexes for Partitioned Tables) but that did not work either.

One idea is to use partition exchange. That should let you shrink the space needed one partition at a time. I can't test this out myself so please test carefully first in a dev environment. There are at least two different ways to do this.

Method 1:

Create a new table with the exact same columns and indexes as the old table. The new table should have one partition.
Copy all of the data from the partition to shrink to the new table. You can use parallel DML to speed this up. Since you're writing all of the data again there should be far less wasted space in the new copy.
If you aren't already using compression on the big table you could apply it to the new table before swapping the partitions.
Use ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... to swap the partitions.
Truncate the new table.
Repeat as necessary.

Method 2:

Create a new table with the exact same columns and indexes as the old table. The new table should have one partition.
Use ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... to move one partition to the new table. This should work even for partitions with function-based indexes according to Partition Exchange and Database VLDB and Partitioning Guide. Since your indexes are local it shouldn't invalidate indexes on your big table. If you specify the INCLUDING INDEXES option then indexes should be all valid on the new table as well.
Reduce the space needed by the new table. You may just want to use Oracle's MOVE functionality. You can try dropping the function-based index, issuing a SHRINK SPACE command, and recreating the index, but recreating the index could result in a lot of unnecessary free space in the new table.
If you aren't already using compression on the big table you could apply it to the new table before moving back the partition.
Partition exchange with the old table.
Repeat as necessary.

I personally like method 1 better. It gives you increased availability on the big table and you should get more predictable results with less work.

One thing to watch out for is there was some kind of issue with statistics at one point with exchanging partitions with function-based indexes. You can find more information about it here.


MySQL 8.0, MacOS, create database issue? when I use: CREATE DATABASE test;

  ERROR 3680 (HY000): Failed to create schema directory 'test' (errno: 2 - No such file or directory)

Run this in the mysql client (or similar):

SHOW VARIABLES LIKE 'datadir';

Then look at the ownership and permissions for that directory. It should be owned by the mysql user,  have the mysql group, and the mysql user should have read, write and execute (rwx) permissions, and I think similar for the group.

Which process is running the mysql daemon? It should be the mysql user. In Linux you can get a list of processes which includes the user who is running each. I assume you can do something similar on MacOSX (if that is what you're using).

Assuming this is a new installation, please see this documentation page about Initializing the data directory.


MySQL - Duplicate Entry when trying to install a Plugin I'm trying to install a Plugin on my MySQL database, but each time I try I get a Duplicate Entry error:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Error Code: 1062. Duplicate entry 'audit_log' for key 'PRIMARY'

But If I run:

SHOW PLUGINS;
SELECT * FROM `information_schema`.`plugins`;

There is no sign of it.

If I run SELECT * FROM mysql.plugin; then there is an entry for it.

But If I run UNINSTALL PLUGIN audit_log; then it tells me it doesn't exist. Error Code: 1305. PLUGIN audit_log does not exist

I know the file is there, because there are two databases on this server running under mysqld_multi and it installed fine under the other one.

The server is 5.5.54, the server is Debian 8 (jessie).

Is there anything else I can do? Can I simply delete it from the mysql.plugin table without causing problems? Restarting the server isn't feasible as it is my master database.

In the end, I renamed the entry in mysql.plugin, and then I was able to install it. Once I checked it was OK I then removed the entry in mysql.plugin.

So far it seems to be working fine,


Speeding up MySQL NOT IN (Subquery) I'm currently up to try to improve the performance of some of my queries.
As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.

SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)

When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.

SELECT *
FROM table1
WHERE
    field1 NOT IN (
        SELECT
            index_field
        FROM table2
        WHERE
            user_id = '2'
    )

I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.

How may I solve this problem? Currently, I've no clue about that.

Kind regards,

Dominik

//--- EDIT -->

The original query looked sth. like this

SELECT
    i.*
FROM stream_item si
LEFT JOIN user__publisher_item ui
    ON ui.user_id = 2
    && ui.item_id = si.item_id
INNER JOIN item i
    ON i.id = si.item_id
WHERE
    si.stream_id IN (5,7,8,9,19,24,29,42,43,44)
    && ui.id IS NULL || (
        ui.`read` = 0 || ui.saved = 1
    )
GROUP BY
    si.item_id
ORDER BY
    si.`found` DESC
LIMIT 0,10

I have a table which represents the n:m relationship between stream and items.
An unique item can be accessible via multiple streams.
Based upon this i have a state-table for user dependent states of an item.
Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table.
In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.

//--- Edit 2 -->
The table-structure in the background is as follows:

stream
stream_item //n:m relation between stream and items
item //unique items
stream_group //user-defined groups of multiple streams
stream_group_streams //_n:m relation between stream_groups and streams_
user
user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item

btw. sorry for my bad english

SELECT * FROM table1 t1

  LEFT JOIN table2 t2 ON t1.field1 = t2.index_field

  WHERE

   t2.user_id = '2'

this will be optimized and will serve the purpose! although u can use EXIST as well!


List foreign keys dependencies that refer to a given table RDBMS: PostgreSQL 9.6

I desire to list the foreign keys for a given table and schema. I am about 90 % there but I also desire to know what options were used to make the fkey. Example on delete restrict or on delete update

create or replace function it.table_foreign_keys_getlist(_schema text
                                                       , _table text)
                           returns TABLE(primary_key_ns text
                                       , primary_key_table text
                                       , foreign_key_ns text
                                       , foreign_key_table text
                                        )
     language sql
 as $$
 SELECT
     n1.nspname::TEXT  AS primary_key_ns,
     c1.relname::TEXT  AS primary_key_table,
     n2.nspname::TEXT  AS foreign_key_ns,
     c2.relname::TEXT  AS foreign_key_table
 FROM pg_catalog.pg_constraint c
 JOIN ONLY pg_catalog.pg_class c1     ON c1.oid = c.confrelid
 JOIN ONLY pg_catalog.pg_class c2     ON c2.oid = c.conrelid
 JOIN ONLY pg_catalog.pg_namespace n1 ON n1.oid = c1.relnamespace
 JOIN ONLY pg_catalog.pg_namespace n2 ON n2.oid = c2.relnamespace
 WHERE c1.relkind = 'r'
   AND c.contype  = 'f'
   AND CASE WHEN _schema = '' THEN TRUE ELSE n1.nspname = _schema END
   AND CASE WHEN _table  = '' THEN TRUE ELSE c1.relname = _table  END
 ORDER BY 1,2,3,4;
 $$;

You are looking for the columns confupdtype and confdeltype in the catalog pg_constraint.

The codes mean:

  Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default


How to restore symmetric key on the new database? I have an issue with symmetric key restoring on the another database.
I have table in the first(old) database with encrypted column.(The table contains encrypted data)
The key in the old database has been created with the next sql script:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TESTPSWD'
GO
CREATE CERTIFICATE [CertificateSecurity] WITH SUBJECT = 'Key Protection';
GO
CREATE SYMMETRIC KEY [EncryptKey] WITH
    ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE [CertificateSecurity]
GO

And now I have to move this table with all data to the new database.
I import table with data from the old database.
And there is a question: Is there any way to create the same encryption key in the new database without specifying key_source and the identity_value? (Because I don't have it)
I tried to create it with the same sql query as above, but decryption in this case returns NULL.

P.S. I know what I can decrypt data then recreate key(with all parammeters supplied) and encrypt data with a new key.But it would be great to find another solution.

There is no built-in solution in SQL Server. A symmetric key that was created without specifying the source and identity can never be scripted or copied.

That means, in your case you have to decrypt and re-encrypt on the fly while copying the data.

You could also create a new key in the current database (specifying source and identity) and decrypt and new-key-re-encrypt locally. Then you can move the encrypted data and recreate the key in the new location, as you have all the important seed data available for that new key.


MySQL - Enabling Scheduled Event on Master and Slave Simultaneously This may seem a bit strange, but I am trying to get a Scheduled Event to execute on both Master and Slave.

I have two databases set up in a Master (A) to Master (B) replication environment.

Master A is READ_ONLY=OFF

Master B is READ_ONLY=ON

I create a user on both Databases:
GRANT INSERT, EVENT ON test.* TO 'user'@'localhost' IDENTIFIED BY 'Password';

I then create my Event on Master A:

DROP EVENT `e_test`;
DELIMITER $$
CREATE DEFINER=`user`@`localhost`
EVENT `e_test`
ON SCHEDULE EVERY 1 MINUTE
STARTS NOW()
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Adds a new row to test.tab1 every 1 minute'
DO
    BEGIN
        INSERT INTO test.tab1 (`fname`) VALUES (NOW());
    END;
$$

So far so good. It executes every minute, and adds an entry to the table, which replicates to the other Database.

However, on the Master B, it is marked as SlaveSide Disabled, and so doesn't execute.

If I do:

ALTER DEFINER=user@localhost EVENT e_test ENABLE;

on Master B, it starts to execute on Master B, but on Master A it is now flagged as Slaveside_Disabled, and so doesn't execute.

If I then enable it on Master A, Master B is Slaveside_Disabled.

The reason for wanting this (in case you were wondering), is so that as part of my failover script I simply need to execute SET GLOBAL READ_ONLY = { ON | OFF } on each database accordingly,
as opposed to having to enable / disable all my events (one command vs many commands).

Under normal circumstances, on Master A (READ_ONLY=OFF) the events execute as per normal and adds the entry; On Master B (READ_ONLY=ON) the events execute, but don't insert an entry as they don't have permission.

I looked at using SET GLOBAL EVENT_SCHEDULER = { ON | OFF } as the one command, but if I set it to OFF as the default, then we need to remember to enable it each server restart.
Alternatively if we set it to ON as the default we need to remember to disable it every server restart.
The use of READ_ONLY seemed a better option as it can be easily included in a failover script.

Any ideas?

The only way I have found to do this so far is by using:

On the Master B:

SET SQL_LOG_BIN=0;
ALTER DEFINER=user@localhost EVENT e_test ENABLE;
SET SQL_LOG_BIN=1;

This works, but as soon as I alter the Event on Master A, Master B goes back to Slaveside_disabled and I have to repeat this process. So not perfect by any means, as it relies on us remembering to do this for each event, if we Alter them.


oracle - does this have the potential to cause a lock? Note how no commit was done in between each statement. Could this cause a table lock or blocked session?

delete from tableblah;
insert into tableblah
(select * from othertable);

In Oracle, writers don't block readers, and it won't lock the table, just the affected rows.

Until you commit, all other sessions will continue to see the data as it was before your delete, and can continue to query.

The only blocking you'd see in another writer session is if there is a primary key or unique index on the table and it tries to insert a row which clashes; or it tries to update/delete a row that you deleted. That second session will wait until your first session issues a commit or rollback; the second session will then either get an error (if you committed) or complete the action (if you rolled back).


SQL Server 2008 - Restore Database With RESTART I am trying to restore a database from a backup file on a SQL Server 2008 instance using the following SQL command:

BEGIN
    RESTORE DATABASE [MyDataBase]
    FROM DISK=@db_location
    WITH STATS=5,
    FILE=1,
    MOVE @db_OldName TO @db_NewFile,
    MOVE @db_OldLogName TO @db_NewLogFile,
    NOREWIND, NOUNLOAD, RESTART, RECOVERY
END

and it fails with the following error:

  Msg 3264, Level 16, State 1, Line 19
  The operation did not proceed far
  enough to allow RESTART. Reissue the
  statement without the RESTART
  qualifier. Msg 3013, Level 16, State
  1, Line 19 RESTORE DATABASE is
  terminating abnormally.

If I do not use the RESTART option the backup is successful. On SQL Server 2005 it works even with the RESTART parameter.

Since the SQL command is issued by a program I cannot change it. Is there a way to work around this issues on SQL Server 2008?

Has this ever worked?

RESTART is only specified when you want to continue an interrupted restore.
If you can't change the SQL, then you can only somehow break a first restore to generate an error condition.

Because of this, you can't be sure that it's the same on SQL 2005 unless it happens every time. Or they changed behaviour.


Should I create new table, or add new columns to existing one for song lyrics? I'd like to ask for some advice in whether I should create a new table or just add some new columns to existing table?

Honestly I've been thinking about this whole day and I really don't know.

So basically here's the structure of song table.

+------------+-----------+------+-----+---------+----------------+
| Field      | Type      | Null | Key | Default | Extra          |
+------------+-----------+------+-----+---------+----------------+
| ID         | int(6)    | NO   | PRI | NULL    | auto_increment |
| Title      | char(100) | YES  |     | NULL    |                |
| Type       | char(20)  | YES  |     | NULL    |                |
| Singer     | char(50)  | YES  |     | NULL    |                |
| Youtube    | char(30)  | YES  |     | NULL    |                |
| Score      | double    | YES  |     | 0       |                |
| Ratings    | int(8)    | YES  |     | 0       |                |
| Favourites | int(7)    | YES  |     | 0       |                |
+------------+-----------+------+-----+---------+----------------+

In this I store information about songs obviously. And yesterday I thought I would add a feature on my site to check the lyrics of the available songs. I'm only planning to add the lyrics in 2 different languages, English and German for now.

That being said, I don't know if it would be better to just add some new columns like English_lyrics and German_lyrics to the song table, or I should create a new one, called lyrics, with these two columns and the ID of the song, SongID. This way I would just select the lyrics using INNER JOIN.

As per normalization, you should not have similar value attributes in multiple columns. In future, let's say, if you have to add 10 more languages later, your table will become huge. Moreover, adding new columns in a table with lots of data in a production environment is not so easy. Relational data should not be considered like a Flat Excel file. Searching for a combination of languages in lyrics will not be easy as well. Another issue could be that you will have lots of NULL values, because not all songs would have lyrics in all the languages. NULL is a special value, and handling it requires special care while formulating queries, and lots of common mistakes happen due to not accounting for the NULL.

Instead, you can create a new table, let's say, songs_lyrics. It will have three columns, viz., song_id (Foreign Key to ID in songs table), language_id (Foreign Key to ID in a language master table), lyrics (text containing the lyrics in the specific language). You will also have a composite Primary Key on (song_id, language_id)

CREATE TABLE songs_lyrics (
  song_id INT NOT NULL,
  language_id INT NOT NULL,
  lyrics TEXT,
  PRIMARY KEY (song_id, language_id),
  FOREIGN KEY (song_id) REFERENCES song(ID),
  FOREIGN KEY (language_id) REFERENCES language(ID)
) engine= InnoDB;

You will also need to create the Master table for the languages:

CREATE TABLE language (
  ID INT NOT NULL AUTO_INCREMENT
  name VARCHAR(64) NOT NULL
  PRIMARY KEY (ID)
) engine=InnoDB;


Long term storage of sp_server_diagnostics results Is the output from sp_server_diagnostics stored anywhere by default please ?

No, but as MS recommends, you should save this off either to an external file via Extended Event or log it to a table.

Taken from sp_server_diagnostics:

-- Create the Event
CREATE EVENT SESSION [diag]
ON SERVER
           ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)
           ADD TARGET [asynchronous_file_target] (set filename='c:\temp\diag.xel');
GO
ALTER EVENT SESSION [diag]
      ON SERVER STATE = start;
GO

-- Read Output
SELECT
    xml_data.value('(/event/@name)[1]','varchar(max)') AS Name
  , xml_data.value('(/event/@package)[1]', 'varchar(max)') AS Package
  , xml_data.value('(/event/@timestamp)[1]', 'datetime') AS 'Time'
  , xml_data.value('(/event/data[@name=''component_type'']/value)[1]','sysname') AS Sysname
  , xml_data.value('(/event/data[@name=''component_name'']/value)[1]','sysname') AS Component
  , xml_data.value('(/event/data[@name=''state'']/value)[1]','int') AS State
  , xml_data.value('(/event/data[@name=''state_desc'']/value)[1]','sysname') AS State_desc
  , xml_data.query('(/event/data[@name="data"]/value/*)') AS Data
FROM
(
      SELECT
                        object_name as event
                        ,CONVERT(xml, event_data) as xml_data
       FROM
      sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
)
AS XEventData
ORDER BY time;

Alternatively you can log it to a table:

CREATE TABLE SpServerDiagnosticsResult
(
      create_time DateTime,
      component_type sysname,
      component_name sysname,
      state int,
      state_desc sysname,
      data nvarchar(max)
);
INSERT INTO SpServerDiagnosticsResult
EXEC sp_server_diagnostics;


Temporarily Turn Off Table Type Reference Checking On A Development Database During my database design process I have begun to add Table Types to the database and have run into the need to alter a table type. It is painfully clear that one cannot remove or drop the table type if it is being used in stored procedure(s); and unless one drops the related sprocs first, the table type can't be dropped/altered.
I get why the table type is important in an active database and why it should not be dropped if live, but if one is in a development mode is there/why not be able to turn off the database, so to drop the table type, and the add the modified type and restart? ...
 So is there a way to stop the checking and allow the drop of the table type, hence avoid the warnings to be able to alter the type?

No, there is no way to alter a table type, or drop it while any object references it directly. It would be nice to have a "just trust me, I know what I'm doing" mode, but we don't; you'll have to file a feature request to get that functionality.
In the meantime, what I would suggest is to just create a new table type, and then update the stored procedures in the order that they need to use the new type's definition. This prevents breaking the old procedures until you can modify them, too. A little more info here:

How to Alter User Defined Table Type in SQL Server


StartsWith query using LIKE is returning the wrong results on a non ascii column I want to query a table for entries that begin with a specific fragment of text.

I am under the impression that the LIKE operator is a way to do this, so if I wanted to find all rows that have a Column2 that starts with "a" then I would do that with a query such as the following

SELECT Column1, Column2, Column3
FROM Table1
WHERE Column2 LIKE 'a%';

I have found that using a query like this returns unexpected results.

Here is an example:

CREATE DATABASE TestDB;
GO

USE TestDB;
GO

CREATE TABLE TestTable
(
    Id INT NOT NULL,
    Romanian NVARCHAR(MAX) COLLATE SQL_Romanian_CP1250_CI_AS NOT NULL,
    English  NVARCHAR(MAX) NOT NULL
);
GO

SET NOCOUNT ON;

INSERT TestTable SELECT 1, N'da', 'yes';
INSERT TestTable SELECT 2, N'ani', 'years';
INSERT TestTable SELECT 3, N'a avea', 'to have';
INSERT TestTable SELECT 4, N'a fi', 'to be';
INSERT TestTable SELECT 5, N'șase', 'six';
INSERT TestTable SELECT 6, N'șapte', 'seven';
INSERT TestTable SELECT 7, N'opt', 'eight';
INSERT TestTable SELECT 8, N'zece', 'ten';
INSERT TestTable SELECT 9, N'nu', 'no';
GO

SELECT *
FROM TestTable
WHERE Romanian LIKE 'a%';

USE master;
GO

DROP DATABASE TestDb;
GO

I was expecting this to return the following

+----+-------------+----------+
| Id | Romanian    | English  |
+----+-------------+----------+
| 2  | ani         | years    |
+----+-------------+----------+
| 3  | a avea      | to have  |
+----+-------------+----------+
| 4  | a fi        | to be    |
+----+-------------+----------+

but it returned this instead

+----+-------------+----------+
| Id | Romanian    | English  |
+----+-------------+----------+
| 2  | ani         | years    |
+----+-------------+----------+
| 3  | a avea      | to have  |
+----+-------------+----------+
| 4  | a fi        | to be    |
+----+-------------+----------+
| 5  | șase        | six      |
+----+-------------+----------+
| 6  | șapte       | seven    |
+----+-------------+----------+

The version number I have is 13.0.1601.5

SQL Collations are provided only for backward compatibility.

This has been the case for over 10 years.

Use a more modern collation such as Romanian_100_CI_AS.

This does not produce that behaviour.


Alternate per 2 postgresql rows, but start over if there are rows remaining I'm trying to select below table, grouped by two (=max, if there is only one, show one) of each supplier_id. But, after all suppliers are selected it needs to start over. So this is the table:

Table: products

ID   Supplier_id   Name            created_at
---------------------------------------------
1       1          productname     2014-01-12
2       3          productname     2014-01-12
3       2          productname     2014-01-18
4       2          productname     2014-01-18
5       3          productname     2014-01-17
6       1          productname     2014-01-17
7       2          productname     2014-01-16
8       4          productname     2014-01-10
9       4          productname     2014-01-14
10      2          productname     2014-01-17
11      5          productname     2014-01-13

After selecting the rows I want this result:

Table: products

ID   Supplier_id   Name             created_at
----------------------------------------------
1       1          productname      2014-01-12
6       1          productname      2014-01-17
3       2          productname      2014-01-18
4       2          productname      2014-01-18
2       3          productname      2014-01-12
5       3          productname      2014-01-17
8       4          productname      2014-01-10
9       4          productname      2014-01-14
11      5          productname      2014-01-13
7       2          productname      2014-01-16
10      2          productname      2014-01-17

On https://stackoverflow.com/questions/15969614/in-sql-how-to-select-the-top-2-rows-for-each-group I almost found what I was looking for.

SELECT *
FROM   products as p
WHERE
        (
            SELECT  COUNT(*)
            FROM    products as pr
            WHERE p.supplier_id = pr.supplier_id  AND p.created_at >= pr.created_at
        ) <= 2
        order by p.supplier_id

But as I told, after 2 of each supplier_id's are selected it needs to start over if there are any rows left. Anyone who knows this is possible with a query?

I think you need something using ROW_NUMBER() and (integer) division by 2, along the lines of this:

WITH p AS
( SELECT *, ROW_NUMBER() OVER (PARTITION BY supplier_id ORDER BY id)-1 AS rn
  FROM products
)
SELECT *
FROM p
ORDER BY rn/2, supplier_id, rn ;

Tested at SQLFiddle.


PL-SQL query to find the most recent record the "Operation_date" of which is lower than a specified date I have a table with below structure:
create table DOC_AMOUNT_DETAIL
(
  col_id         NUMBER,
  amount         NUMBER,
  operation_date DATE
)

some example data for this table is :
col_id    |  amount   |  operation_date
---------------------------------------
  1       |  5000     |  11/1/2020
  2       |  1000     |  11/1/2020
  3       |  3000     |  11/1/2020
  1       |  1000     |  11/14/2020
  2       |   500     |  11/14/2020

If the amount column of each col_id changes , a new record will be inserted in the table with a new operation_date which is exactly the date of the change. What I need from the table is the amount for each col_id which has the max(operation_date)<= (a specific date for example 11/15/2020) by this I mean I need the last update of the record the operation_date of which is lower than the date I'm querying the table. So my desired result is this :
    col_id    |  amount
    --------------------
      1       |  1000
      2       |  500
      3       |  3000

What I've written so far is this and it gives me the correct output:
select d.col_id,
       d.amount
  from doc_amount_detail d
 inner join (select t.col_id,
             max(t.operation_date) operation_date
               from doc_amount_detail t
              where t.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
              group by t.col_id
             ) d2
    on d.col_id = d2.col_id
   and d.operation_date = d2.operation_date

and I want to know whether there are better ways of writing this query.
Thanks in advance.

Analytic Functions
select
  col_id, amount
from
 (
   select
     d.col_id, d.amount,
     rank() over (partition by d.col_id order by d.operation_date desc) as rk
   from doc_amount_detail d
   where.d.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
 )
where rk = 1;


Find rows with specific string inside column text I'm new in working with SQL Server and I'm trying to select all rows which has a specific string inside a column.
The field has a big string of characters, for example:

  0x8F28EA0000000000000094EFFF00275F0A7F0000F86FFF00275EF57F0000

I tried using the simple

SELECT * FROM DB WHERE COLUMN LIKE '%0F86FFF00%'

But as I have read that SQL Server doesn't have this syntax so how could I do it differently but get the same result?

Your column is most likely of the VARBINARY data type, so it's visual representation starts with 0x and displays hex characters (from 0 to F). You can't simply use LIKE against a VARBINARY column, since LIKE operates with string data types like VARCHAR or NVARCHAR.

You can convert your VARBINARY to the string you are seeing by using CONVERT with the 3rd parameter as 1, check this example:

DECLARE @Varbinary VARBINARY(MAX) = CONVERT(VARBINARY(MAX), 'This is my string value.')

SELECT
    OriginalRepresentation = @Varbinary,
    ConvertToString = CONVERT(VARCHAR(MAX), @Varbinary),
    ConvertToStringHex = CONVERT(VARCHAR(MAX), @Varbinary, 1)

Result:

OriginalRepresentation                              ConvertToString             ConvertToStringHex
0x54686973206973206D7920737472696E672076616C75652E  This is my string value.    0x54686973206973206D7920737472696E672076616C75652E

If you are using SSMS, when you query a VARBINARY column, SSMS is automatically converting your VARBINARY to the hex-string representation so it's "interpretable" to the user.

So to correctly filter using LIKE:

SELECT * FROM DB WHERE CONVERT(VARCHAR(MAX), [COLUMN], 1) LIKE '%0F86FFF00%'


Add Oracle Label Security to an existing Oracle installation We are using Amazon EC2 to host some Oracle database instances while we are evaluating and prototyping some software. We are using the AMIs provided by Oracle - specifically, Oracle Database 11g Release 2 (11.2.0.1) Enterprise Edition - 64 Bit (Linux).

I now need to do some work based on Oracle Label Security - OLS. It appears that when Oracle was first installed into an AMI, the Oracle Label Security option was not enabled. So I want to add OLS to an existing installation of Oracle.

I have tried following the installation instructions in the Oracle Label Security Administrator's Guide, using media downloaded from following the download links on the Oracle home page. However the instructions don't match up with what I see when I run the installer - according to the instructions, on the second page of the wizard I should see an Advanced Installation option, but I don't see that - I can only see three radio buttons, "Create and configure a database", "Install database software only" and "Upgrade an existing database". I tried to muddle through the instructions but there were several inconsistencies, as if I was running a different version of the installer to the documentation writer. The AMI, documentation and media are all Oracle Database 11g Release 2 (11.2.0.1) so I don't know why they don't all seem to match.

I tried to proceed with an installation anyway, and did find the checkbox to install Oracle Label Security. However it would not accept my existing ORACLE_HOME as an installation destination, telling me: [INS-32025] The chosen installation conflicts with software already installed in the given Oracle home.

So, in short - is it possible for me to add the OLS option to an existing installation? If so - how? If not - how do I make a new installation and attach the existing database to the new installation?

Edit 2011-05-23

After doing some more poking around, I found a copy of the Oracle installer at $ORACLE_HOME/oui/bin/runInstaller. This installer looks visually different and has different page flows. First thing it wanted to know was the source location, so I gave it the path to stage/products.xml on the installation media. It then gave me a choice of Enterprise, Standard or Custom - I selected Custom. It then asked for the Oracle home - this was pre-selected with the value of $ORACLE_HOME. So far so good.

Then, the first odd thing: it gave me a long list of installable products. Oracle Label Security was in this list, but it was already marked as "Installed". However if I clicked the Installed Products... button for information, Oracle Label Security was not present.

I selected Oracle Label Security anyway and it changed to say "reinstall". But on clicking Next I got this error:

Just guesswork, but do you select "Upgrade an existing database" early on:

and "advanced install" later on:

The docs do look as if they are referring to the 10g installer.


Prevent auto commit of DROP without SET AUTOCOMMIT OFF I dropped my table and I lost my data and I can't roll it back because DROP statement automatically commits. How can I prevent auto commit action of DROP without using SET AUTOCOMMIT OFF?

DROP and TRUNCATE are Data Definition Language commands and thus cannot be rolled back. However, in Oracle you can use the following technologies to recover your table:

Flashback Drop
Flashback Database
Tablespace Point in Time Recovery
Table-Level Recovery From Backups (new in 12c)

Flashback Drop

If the Recycle Bin is enabled in your database, you can use the following command to recover your table (if it's still in the Recycle Bin).

SQL> flashback table1 to before drop;

If your table is not in the Recycle Bin (it was purged by the user manually or due to space pressure), you can attempt to recover your database from backup to another host and export/import the data and associated metadata to the production database using Data Pump Export and Import commands (or DBMS_DATAPUMP package).

Flashback Database

If you have Flashback Database enabled (check the flashback_database column in v$database view), you can flashback your database to the point before your table was dropped. But you must ensure that there's enough data in flashback logs which you can check using v$flashback_database_log view.

Tablespace Point in Time Recovery

To recover your table using tablespace point in time recovery (TSPITR), you can use the recover tablespace until ... command. But be sure to check what objects can be lost in the tablespace being recovered by checking SYS.TS_PITR_OBJECTS_TO_BE_DROPPED view. These objects were created after the recovery point you specify in this command. You would export them before TSPITR and re-import once TSPITR is complete.

Table-Level Recovery From Backups

New in Oracle Database 12c is the ability to recover individual tables. You employ it by using recover table command in RMAN, and it works very similar to TSPITR – creating auxiliary instance, – but on a more granular (table) level.

See also.

Using Flashback Drop and Managing the Recycle Bin in Database Administrator's Guide.
Using Flashback Database and Restore Points in Database Backup and Recovery User's Guide.
Performing RMAN Tablespace Point-in-Time Recovery (TSPITR) in Database Backup and Recovery User's Guide.
Table-Level Recovery From Backups in Oracle Database 12c Release 1 (12.1.0.1) New Features Guide.
Recovering Tables and Table Partitions from RMAN Backups in Oracle Database Backup and Recovery User's Guide.


SQL Server - Is there a way to query the specific Size and Space available settings for each Database, including system databases on an instance? I am trying to query the two default database properties as seen in the picture below for all databases on an instance, including system databases.
I've used various scripts such as the following, but I'm not sure exactly where data for those two fields are stored.
SELECT
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db
GROUP BY database_id

Thank you for your help.

Just check the properties of the model database - everything else being equal a new database is created using the model database as a template.
If you want to change the default size, resize your model database - job done.
Note however if the database creation script supplies a specific value that will take precedence.
re-read the question including your title and I think you want the results of running
exec sp_helpdb

or try if you want to see the space available
exec sp_msforeachdb "use [?]; exec sp_spaceused"


MS SQL Server 2014: which stored procedure is my error in? As a general question or three on Microsoft SQL Server 2014, that perhaps I should know the answer to already, or even perhaps did once -
When my stored procedures run other stored procedures, how can I find out which stored procedure's code generated the error?
Is there a particular way to handle errors from Transact-SQL that identifies the location of the error in a given stored procedure?
Can program code in a stored procedure obtain the name of the procedure it is in?
I am prepared to put "SET @MyNameIs = N'HastilyWrittenProcedure' " in program code if it's the only way.  Then I can put "PRINT @MyNameIs + ' broke.' " in the error handler in everything.

Use @@procid to get the currently executing routine and object_name() to convert the id to a name
Hth


SQL to MDX translation? An MDX (a language dealing with multidimensional data, in particularly dealing with the cube metaphor) statement can be translated to an SQL (a well-known language for the relational model) statement - Mondrian is one example solution which provides such translation.

I was wondering if there are any available solutions to provide a translation in the other direction, i.e. from SQL to MDX. If not, what would be involved in creating such a functionality?

Mondrian is indeed a good example, in that it's a ROLAP engine (in which MDX statements are translated to SQL which are then fired to the underlying database (other than it caches results, so ends up being a Hybrid approach instead of pure ROLAP).

However, the "translation" between MDX and SQL isn't a straightforward one. A lot of MDX statements require multiple SQL statements, not just one. You can see that from Mondrian's logs (if you have them enabled), where for the same MDX query sometimes several dozen SQL statements are fired, and their results used to feed the next batch of SQL statements (e.g., a first SQL statement gets all customer keys from the customer dimension, a second one creates an IN statement with the customer keys from the previous query).


Can I extend SQL Server with C++ or C? So, I created a PE32+ DLL, in an attempt to load it into SQL Server,

$ file example_dll.dll
example_dll.dll: PE32+ executable (DLL) (console) x86-64, for MS Windows

but when I try to compile it as an assembly, with CREATE ASSEMBLY [ok] AUTHORIZATION [dbo] ... WITH PERMISSION_SET = UNSAFE; I get,

Msg 6544, Level 16, State 1, Server x230, Line 1
CREATE ASSEMBLY for assembly 'ok' failed because assembly 'ok' is malformed or not a pure .NET assembly.
Unverifiable PE Header/native stub.

It as come to my attention that what Micrsoft calls CLR "safe" may just mean, .NET's C++. I know that C# is the prefered method of extending SQL Server, but regardless does Microsoft permit loading shared objects or DLL into the server at all? Can you extend SQL Server with "native code"?

SQLCLR only allows for managed code / pure-MSIL Assemblies. So .NET Visual C++ might work, and I think I have seen one example of it.

You can load / call unmanaged C++ DLLs in an UNSAFE C# / VB.NET Assembly, but that can be riskier than using "unsafe" .NET functionality.

In theory, any .NET language should work IF it produces a pure-MSIL Assembly, but I have yet to see working examples of this. I have seen an example of Visual C++, and I have seen an example of someone coding actual MSIL (on Stack Overflow), though I'm still doubtful as to the supposed benefit of that.

Folks have tried F#, but can't doing that as SAFE since it requires another library for F#:

F# with sqlclr in a reasonably safe way and scripted assembly

As to the updated question, which now also asks about the Extended Stored Procedure (xp) API, that should be deprecated and frowned-upon.


Query to Access a specfic data in the row using python Am using wordpress and phpmyadmin for handling my database
Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as desired.

import MySQLdb as mdb
con = mdb.connect('localhost', 'root', 'password', 'WordpressDB', unix_socket="/opt/lampp/var/mysql/mysql.sock");
with con:
    cur = con.cursor()
    cur.execute("SELECT data FROM `wp_form_builder_entries` WHERE entries_id = 5")
    rows = cur.fetchall()

Output generated by this code is as follows:

Works just as desired but I don't want all the data. I just need to know whether its checked or unchecked. If the user has checked value is "s:9:'checked'" else its "s:9:'unchecked'"

What query should I use to retrieve "s:9:'checked'"?

If I understand it correctly, you are storing all data in one field of the table? If so, MySQL will not be of much use as you need to unserialize the data in python. If so, then this question is not really for DBA, but more for StackOverflow.

If you however want to restructure the table and keep various data elements in different columns instead of one column called "data", then you could SELECT only that particular column(s) that are relevant instead of querying whole string.


Storing numbers that will be also used as digits I'm new to databases, please help me to figure out the best design to store 8-digit numbers that are going to be used for two purposes:

Statistical analyses of separate digits. For example all 2nd digits across all entries or frequency of appearance of a particular digit etc.
Matching whole number to find if the number is in the database in order to get data from relevant columns.

The usage ratio between these two purposes is expected to be close to 50/50. The initial amount of entries is around 6000, with yearly growing close to 2000 entries. Chosen DMS is MariaDB (MySQL).

I can see the 3 possible ways of implementing this:

Store whole numbers and split them each time via programming languages later (for usage #1).
Make 8 columns and store numbers as separate digits. Concatenate them each time via programming language later (for usage #2).
Store both, the whole numbers and separate digits in 8 columns.

But personally I'm not excited about any of these ideas of mine, so I'm seeking an explanatory advice from you.

Your best solution for this is undoubtedly the use of GENERATED columns (also known as VIRTUAL or COMPUTED columns).

You mention MariaDB - the documentation is available here. The core bit (the syntax) is here:

<type>  [GENERATED ALWAYS]  AS   ( <expression> )
[VIRTUAL | PERSISTENT | STORED]  [UNIQUE] [UNIQUE KEY] [COMMENT <text>]

So, for MariaDB, I constructed the following table here (fiddle):

CREATE TABLE the_code_1
(
  code CHAR(8),
  the_code_location CHAR(1) GENERATED ALWAYS AS (SUBSTR(code, 3, 1))
  VIRTUAL COMMENT '3rd character'
);

Then I INSERTED some data:

INSERT INTO the_code_1 (code) VALUES ('12345678');
INSERT INTO the_code_1 (code) VALUES ('87654321');

Note that only 1 field is inserted! Then, I SELECT from that table:

SELECT * FROM the_code_1;

Result:

code    the_code_location
_________________________
12345678                3
87654321                6

Which is the 3rd character in both of those fields!

The table definition above has VIRTUAL, meaning that it's calculated on the fly. The fiddle also shows the use of the PERSISTENT keyword (in place of VIRTUAL), meaning that the data is physically stored on disk.

This is a fiddle using MySQL 5.7 - the only difference is that in place of the the PERSISTENT keyword, STORED is required. The MySQL syntax is here. This is also interesting:

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]  -- Note! no PERSISTENT keyword!
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

All in all, depending on your disk versus performance requirements, one of these two options is the way to go - you can do a few tests. Your requirements are minimal so either of the two options should work satisfactorily.


sql server service broker For the last few days the service broker command which run from master database is consuming much more RESOURCES than before. The Impact on the Database is high (from the bad side), It causes for internal waits.
I didn't make any changes in the database before and after the 10.07.2014 (you can see this start date in the graph).
I checked if service broker is enabled in any database and found that it is, in Tempdb & Msdb. What can cause these changes ?

I attached a graph, you can see the behavior change.

i attached another screen shot as you can see from sp_who2, what i don't understand is, you can see that the "LastBatch" is in the very old , from the 10/12 07:55 (this results are from today 10/20/2014 10:23 Local time). why the session not closed . ?

Looks like you're using a tool not capable of identifying benign waits. See Wait statistics, or please tell me where it hurts. There is no impact, just bad monitoring.

I recommend you read Filtering out benign waits. The engine has certain background tasks and when these background tasks don't have any work they simply sit idle, waiting for work. while waiting for work, the wait times are aggregated by the wait stats but unfortunately there is no way to distinguish between 'wait for a resource to continue the work' or simply 'wait for something to do to start the work'. The later case is a 'benign wait'. The less work these tasks actually do, the more wait stats they aggregate. These tasks never shut down and there is no session to close. You have embarked on a snipe hunt.

For more details read Understanding how SQL Server executes a query and How to analyse SQL Server performance.


How can I change collation for all tables and columns in MySQL database? I have a problem with MySQL collation. It turns out that MySQL uses latin1_swedish_ci by default. Collation setup step has been missed on MySQL database instantiation for some reason. Thus, application setup wizard has created tables in MySQL database without regard to the required collation. As a result, all tables and columns have latin1_swedish_ci collation. Changing database collation with ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci does not help as long as it does not affect tables and columns.

I have many tables and columns in my database, it will take a lot of time to traverse whole database and set collation manually. It will be also difficult to write long SQL altering every table and column as long as there are too many of them.

The question is where there any way to change collation for tables and columns in the whole MySQL database? If there is no dedicated mysql command for this purpose, maybe there are some examples of stored procedures available?

PS: I cannot use scripting languages as long as I do not have access to the serverside. MySQL is installed in restricted network area. All I can do is use phpmyadmin in order to run SQL queries.

In order to change the collation for all of the tables in certain database you should create a custom script and execute it on the server.

Here is a sample PHP script:

<?php

$database_name='dbname';
$database_username='dbusername';
$database_password='dbpassword';

$connection = mysql_connect('localhost',$database_username,$database_password);

if(!$connection) {
    echo "Cannot connect to the database – incorrect details";
} else{
    mysql_select_db($database_name);
    $result=mysql_query('show tables');

    while($tables = mysql_fetch_array($result)) {

        foreach ($tables as $key => $value) {
            mysql_query("ALTER TABLE ".$value." CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
        }
    }
echo "Successfull collation change!";
}
?>


Is there a way to get the IP address of a client virtual machine from pg_stat_activity? I have several C# applications where I have to ensure that there is only one instance of the application running against a given schema.

I am migrating from Oracle to PostgreSQL 10.

In Oracle, I would run the following query:

select sid, username, machine, program, logon_time
  from sys.v_$session v
 where status not in ('KILLED')
   and upper('\' || machine) not like upper('%\' || :MACHINE)
   and upper(username) = upper(:USERNAME)
   and upper(program) = upper(:PROGRAM)

I use Environment.MachineName in my applications to get the value for the MACHINE parameter.

I have rewritten the query for PostgreSQL as follows:

select pid, user, client_hostname, application_name, backend_start
  from pg_stat_activity
 where upper(state) not in ('KILLED')
   and upper('\\' || client_hostname) not like upper('%\\' || :MACHINE)
   and upper(user) = upper(:USERNAME)
   and upper(application_name) = upper(:APPNAME)

I have added the ApplicationName to my connection string and enabled the log_hostname setting on my database to populate the application_name and client_hostname columns in pg_stat_activity, respectively.

The problem that I have run into is that, when running the application on a virtual machine, the client_address column shows the IP address of the physical machine that is hosting the VM, not the IP address of the virtual machine, and therefore the client_hostname is wrong also.

Am I doing something wrong?  Is there a way to get my application to push the correct information to PostgreSQL when connecting?

I have added the ApplicationName to my connection string

I believe that the machine name on Oracle is voluntarily reported by the client to the server, which is why it works even behind NAT.

PostgreSQL only has one self-reported field, so I think that what you will have to do is stuff both "program name" and "machine name" into the ApplicationName field with some kind of delimiter between them.


Would a sequence provide any performance gains over SELECT MAX(id)? I am working on re-factoring a database. There is a process that inserts a row in one of two tables, and the id needs to be unique across the two tables.

Currently the application layer iterates through a predetermined array of keys.

I am considering a Sequence to generate the next id, as apposed to a SELECT MAX(id) + 1.

Are there any performance gains to be realized from using a Sequence?

SELECT MAX(id)+1; is incorrect, will not work under even mild concurrency as multiple processes will find the same MAX and then attempt to insert the same next value.

A SEQUENCE is correct, so by definition better, but not necessarily the best. It should be used as a DEFAULT constraint on the column.

By far simplest solution is to simply use an IDENTITY column.


AT TIME ZONE direction changes based on column having a timezone Why does Postgres do the opposite thing for TS with and without Timezones?  In one case it adds hours, in the other case it subtracts.  Just to confuse things more, when specifying GMT+x, the hours are added in the case of TZ-naive and subtracted when the column is TZ-aware, but the opposite is done when using a time zone specified as 'Region/City'

Africa/Johannesburg is ahead of UTC by 2 hours, but when I select the column with AT TIME ZONE 'Africa/Johannesburg' it returns the time as the stored value MINUS 2 hours.

SELECT
   admission_at AT TIME ZONE 'gmt+2' AS gmt_plus_2,
   admission_at AT TIME ZONE 'Africa/Johannesburg' AS africa_jnb,
   admission_at AS naieve
 FROM admissions WHERE admission_at::date = '2018-06-11'
 ORDER BY admission_at LIMIT 3;
       gmt_plus_2       |       africa_jnb       |        naieve
------------------------+------------------------+---------------------
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
 2018-06-11 08:00:00+00 | 2018-06-11 04:00:00+00 | 2018-06-11 06:00:00
(3 rows)

As a test I created a table with two columns, one for time with and one without timezone information.

SELECT
 time1,
 time1 AT TIME ZONE 'Africa/Johannesburg' AS naieve,
 time1 AT TIME ZONE 'gmt+2' AS t1_gmt2,
 time2, time2 AT TIME ZONE 'Africa/Johannesburg' with_tz,
 time2 AT TIME ZONE 'gmt+2' AS t2_gmt2
FROM times;

           time1            |            naieve             |            t1_gmt2            |             time2             |          with_tz           |          t2_gmt2
----------------------------+-------------------------------+-------------------------------+-------------------------------+----------------------------+----------------------------
 2018-06-14 08:47:35.382964 | 2018-06-14 06:47:35.382964+00 | 2018-06-14 10:47:35.382964+00 | 2018-06-14 08:47:35.382964+00 | 2018-06-14 10:47:35.382964 | 2018-06-14 06:47:35.382964
 2018-06-14 08:47:36.468022 | 2018-06-14 06:47:36.468022+00 | 2018-06-14 10:47:36.468022+00 | 2018-06-14 08:47:36.468022+00 | 2018-06-14 10:47:36.468022 | 2018-06-14 06:47:36.468022
 2018-06-14 08:47:37.342908 | 2018-06-14 06:47:37.342908+00 | 2018-06-14 10:47:37.342908+00 | 2018-06-14 08:47:37.342908+00 | 2018-06-14 10:47:37.342908 | 2018-06-14 06:47:37.342908
(3 rows)

The result is exactly the opposite of what I expect.  Why would a timestamp stored as naive convert correctly to 'GMT+2' by adding 2, but a TS with timezone do the opposite?

And why would it be the other way around when converting to 'Africa/Johannesburg', where the TZ-naive sees the value returned having 2 hours deducted while the TZ-aware value gets 2 hours added for the same value?

Note:  This is NOT a duplicate of "AT TIME ZONE" with zone name PostgreSQL bug? which relates to Time Zone abbreviations, which behaves differently from Time Zone Names.

In this instant regardless whether the name or the GMT+offset format is used, the result is reversed (time added vs time subtracted) depending on whether the column type is a TZ naive or TZ aware timestamp.

Yes! it does!

The syntax to convert from timestamp with timezone (AKA timestamptz) to timestamp without timezone (AKA timestamp) is identical to the syntax to do the opposite transformation.

So, depending on which way you are converting the value you will see the printed value change in the corresponding direction.

You have a column called admission_at of type timestamp
when you say admission_at AT TIME ZONE 'GMT+2' postgres interprets that to mean treat the values in  admission_at as-if they are in the zone 'GMT+2' and produce a result of type timestamptz

If you actually want to treat the admission_at values as GMT timestamps and display the apropriate Johannesburg wall time, convert them first to
timestamptz using the GMT rules and then back to timestamp using the Johannesburg` rules.

SELECT
   admission_at AT TIME ZONE 'gmt' AT TIME ZONE 'gmt+2' AS gmt_plus_2,
   admission_at AT TIME ZONE 'gmt' AT TIME ZONE 'Africa/Johannesburg' AS africa_jnb,
   admission_at AT TIME ZONE 'gmt' AS gmt
   admission_at AS naieve
 FROM admissions WHERE admission_at::date = '2018-06-11'
 ORDER BY admission_at LIMIT 3;


pg_restore: [archiver] did not find magic string in file header I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump:

sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name

This command generates a valid sql file that starts with droping any existing database objects, then generates all tables, indizes, sequences and so on, and finally inserts data.

When I try to restore the generated backup file with: (line breaks added for display purposes only)

sudo pg_restore
    -d database_name -h 127.0.0.1 -U postgres
    --format=c --clean --create out.sql

it fails and prints:

pg_restore: [archiver] did not find magic string in file header

What is the reason for that?

You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default,  plain format.

From pg_dump manpage:

  -F format, --format=format
       Selects the format of the output.  format can be one of the
       following:

       p, plain
           Output a plain-text SQL script file (the default).

A dump in plain format should be fed directly to the psql command-line tool, pg_restore doesn't know what it is, which is the reason of this error message: did not find magic string in file header.

You can look directly at the dump file with more out.sql in shell and you'll see readable SQL commands. Restore it with psql -f out.sql [other options]. You will probably want to create the target database first, as the --create option is not present in the pg_dump invocation.

On the other hand, you may reinvoke the dump adding --format=c to its options. Then that would be the opposite: pg_restore must be used to interpret a dump file in the custom format.


Why would SequelPro only import 23k rows out of 130k? I use SequelPro for MySQL on a Mac OS X. I used the import function to upload a 130k .csv file to my database. Everything seems to work fine, then I get the message:

  File Read Error: An error occurred when reading the file, as it could not be read using the encoding you selected (Auto-detect - Unicode (UTF-8)). Only 23,000 rows were imported.

When I hit "Ok," everything else seems to work relatively fine, I'm just missing about 107,000 rows.

Any idea as to what it could be? Maybe I should use something other than auto-detect during the import? I thought that it might have been some extra commas floating around in the actual .csv file, which there were, but I got rid of those and the same thing happened.

Out of 130,000 rows, there's definitely the possibility for some non-English characters. Which ones doesn't MySQL accept and how would I find and replace them?

This is what I'm getting when I run the character set query:

show variables like 'character_set%';

Variable_name               Value
character_set_client        latin1
character_set_connection    latin1
character_set_database      latin1
character_set_filesystem    binary
character_set_results       latin1
character_set_server        latin1
character_set_system        utf8
character_sets_dir          /usr/local/mysql-5.6.10-osx10.7-x86_64/share/charsets/

This may depend on where you generated the CSV file. If the CSV file was generated on a Windows machine, there could be some character set issues

See https://code.google.com/p/sequel-pro/issues/detail?id=1629

See the following URLs as SequelPro's character set problems are not new

https://stackoverflow.com/questions/8910528/understanding-mysql-charset-and-collation
https://stackoverflow.com/questions/11185324/special-characters-get-lost-in-mysql-export-import
http://forums.mysql.com/read.php?28,284657,284657

If the CSV file was generated on another Mac OSx server, you should not be having this issue.

You may have to resort to setting the default character set to match that CSV file. Sounds weird to here it goes:

Please run this query and you will see something like this:

mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql>

You can also see the character set of the database

mysql> show create database mydb\G
*************************** 1. row ***************************
       Database: mydb
Create Database: CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

mysql>

Perhaps you should load another table that has the matching character set:

CREATE TABLE anothertable LIKE mytable;

Change the whole table's character set

ALTER TABLE anothertable CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

or change a column's character set

ALTER TABLE anothertable MODIFY col1 CHAR(50) CHARACTER SET utf8;

Then, have SequalPro load anothertable.

I guess to be less aggressive, just change the column's character set.


Specify Connection in Management Studio T-SQL Query When adding users are roles to DB servers I often use the "Script this action" function from the GUI. I then just go to "Connection :: Change Connection" to do the same on my other servers.

Is there a way I can specify the connection in the scripted action so I don't have to do that second Change Connection step?

No way to do this as part of a script from SSMS, but you do have two options.

One thing you can do is use SQLCMD mode and the ::connect command in order to have a script that will connect to multiple servers and run the script. This works well if you save the script for the user and use the :r command to load the script from a file.

Another thing you can do is configure a Central Management Server and then run your script against multiple servers at once.


How to ship new rows from the source to a target server? Context
We have a SQL Server 'source' where daily 10M rows inserted to a table. No updates. I would like to ship the new rows to a 'target' server, then delete those (sooner or later) on the source where disk space is limited. PK is monoton increasing identity column.
(edited: I forgot mentioning, the target server is mostly offline)
Question
Besides of programmed export import which based on administering which PK ranges which were shipped, is there any more efficient solution?. I have very limited DBA knowledge, my first idea was differential backup with recovery model simple, then some log shipping with recovery model full, but I think both cases the deletes on the 'source' will by applied on the 'target' which I do not want to happen.

It sounds like in this case, a simpler solution might be best for you, such as scheduling a daily SQL Agent job that INSERTS INTO the Target Database's Table the 10 million rows you need from your Source Table. If you're always deleting those 10 million rows from the Source Table after they get sent to the Target Table then it sounds like a pretty simple INSERT statement.
You'd have to remotely INSERT INTO the Target Table which can be accomplished via a number of means such as with a Linked Server and/or OPENQUERY.
Part of your SQL Job will need to manage exceptions for if the Target Server is offline, based on your own retry / abort logic.


Linked Server : Syncing two tables on two SQL Servers Suppose these database structure :

--On main server(server)
TicketRequest (id, date, count, start, end/*,and some other columns*/)
Ticket (id, reqId, blockedDate, usedDate, userId/*,and some other columns*/)
--and some other tables

--On linked server(gate)
--Just this table
Ticket (id, userId, blockedDate, usedDate)

A Ticket may use on gate, so on the gate just its usedDate may change.

At server, a Ticket may blocked, deleted; or after importing gate's data, set its usedDate.

I want to sync them online.

I wrote sp_server_export , sp_gate_import for transport data from server and  sp_gate_export , sp_server_import for transport data from gate, to sync them via a file(a server_export file goes to the gate, and server_import file come back to the server).

I used MERGE and its performance is really good! Now when I'm trying to find a solution for syncing server with gate, after some searches UNFORTUNATELY found Microsoft says : MERGE not working on remote table as target!

So, how I can sync with a transaction from server?!

NOTE : As our plan, syncing must only execute from server! Gate operator cannot change DB at all!

IMPORTANT NOTE : Consider that concurrency is my first concern! I wanna sync while a user may delete a Ticket! Or a Ticket may block at the same time! Or at the gate a Ticket may use!

UPDATE :

Our syncing logic is :

1 - delete old tickets(which returned with a file syncing and have returnDate and have modificationDate > one month later timestamp to ensure delete used tickets from gate)

2 - update gate.ticket where usedDate is null set blockDate = server.blockDate;

3 - insert new rows from server

4 - set transferDate for Tickets transfered to gate ( i mean new/updated tickets in step 2 and 3 )

5 - update server.Ticket.usedDate from gate.Ticket where gate.Ticket.usedDate is not null. ( this will send used tickets from gate back to server )

6 - insert a transferLog ( in transferLog table on server )

Have you considered Merge Replication?  This would seem to be a great solution to me.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication


SQLite: Leave only n rows for each field I have a table

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Window        | 5         |
|  Zinaida Serebriakova       | Florence      | 1         |
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Bath          | 4         |
|  Thomas And William Daniell | Turbine Makers| 2         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Galleons      | 1         |
|  Robert Motherwell          | Ulysses       | 2         |
+-----------------------------+---------------+-----------+

I need to get 2 paintings for each painter with the highest rate.

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Window        | 5         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Ulysses       | 2         |
|  Robert Motherwell          | Galleons      | 1         |
+-----------------------------+---------------+-----------+

And delete everything else!

WITH
cte AS ( SELECT author,
                name,
                rating,
                ROW_NUMBER() OVER ( PARTITION BY author
                                    ORDER BY rating DESC ) rn
         FROM ratings )
DELETE
FROM ratings
WHERE EXISTS ( SELECT 1
               FROM cte
               WHERE ratings.author = cte.author
                 AND ratings.name = cte.name
                 AND cte.rn > 2 );

fiddle


Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.

Example: for the data

employee_id | manager_id | salary
------------|------------|-------
          1 |       null |   1000
          2 |          1 |    100
          3 |          1 |    100
          4 |          3 |     10

I need the results

employee_id | total_salary
------------|-------------
          1 |         1210
          2 |          100
          3 |          110
          4 |           10

Oracle docs give the following example:

SELECT name, SUM(salary) "Total_Salary" FROM (
 SELECT CONNECT_BY_ROOT last_name as name, Salary
  FROM employees
  WHERE department_id = 110
  CONNECT BY PRIOR employee_id = manager_id)
  GROUP BY name;

But I also need the rows in tree pre-order, i.e each row should have its descendants immediately after it. Is there an elegant way to do this, since a simple CONNECT BY ... START WITH ... generally does give its results in pre-order?

This seems to be a working solution (I still wonder if there's a more elegant one):

select name, sum(salary) from (
    select rownum rnum, name, employee_id join_id
    from employees
    connect by manager_id=prior employee_id
    start with manager_id is null
    order siblings by name
  )
  join (
    select salary, connect_by_root employee_id ancestor_id
    from employees
    connect by manager_id=prior employee_id
  ) on join_id=ancestor_id
  group by rnum, name
  order by rnum;


will alwaysOn deal with certificates? do I have to do anything? in a database that is part of an availability group (alwayson), in the current primary server I create a certificate and sign some stored procedures using it.

all is working fine so far.

use mydatabase
go

-- check if certificate exist, create otherwise
IF NOT EXISTS(SELECT 1 FROM SYS.CERTIFICATES WHERE NAME = 'dyntsqlcert')
BEGIN
    CREATE CERTIFICATE dyntsqlcert
    FROM FILE = 'C:\Certificates\dyntsqlcert.cer'
    WITH PRIVATE KEY (FILE = 'C:\Certificates\dyntsqlKey.pvk',
    ENCRYPTION BY PASSWORD = 'oH8F*%G9pnyqfa',
    DECRYPTION BY PASSWORD = 'oH8F*%G9pnyqfa');
 END

--check if a user for the certificate exist, create otherwise
IF NOT EXISTS(SELECT * FROM SYS.database_principals where name ='dyntsql')
BEGIN
    CREATE USER [dyntsql] FOR CERTIFICATE [dyntsqlcert];
END

-- Granting permissions to the tables
        GRANT SELECT ON [app].[applicant] TO [dyntsql];
        GRANT SELECT ON [usr].[user] TO [dyntsql];
        GRANT SELECT ON [usr].[userCountry] TO [dyntsql];
        GRANT SELECT ON [app].[country] TO [dyntsql];
        GRANT SELECT ON [app].[application] TO [dyntsql];
        GRANT SELECT ON [upl].[applicationDocument] TO [dyntsql];
        GRANT SELECT ON [upl].[document] TO [dyntsql];
        GRANT SELECT ON [upl].[applicationDocumentImages] TO [dyntsql];
        GRANT SELECT ON [upl].[ref_documentType] TO [dyntsql];
        GRANT SELECT ON [app].[program] TO [dyntsql];
        GRANT SELECT ON [app].[ref_applicationStatus] TO [dyntsql];
        GRANT SELECT ON [app].[ref_CPIStatus] TO [dyntsql];
        GRANT SELECT ON [app].[applicationItemGroupChange] TO [dyntsql];
        GRANT SELECT ON [app].[applicationComments] TO [dyntsql];
        --GRANT SELECT ON [APIA_Repl_Sub].[repl].[flightChangesHistory] TO [dyntsql];
        GRANT SELECT ON [app].[applicationstatechange] TO [dyntsql];
        GRANT SELECT ON [app].[applicationPlacementInfo] TO [dyntsql];
        GRANT SELECT ON [app].[applicationPreDepartureQuestions] TO [dyntsql];
        GRANT SELECT ON [app].[applicationComments] TO [dyntsql];
        GRANT SELECT ON [app].[ref_visaStatus] TO [dyntsql];
        GRANT SELECT ON [agy].[agency] TO [dyntsql];

--Adding Signature to SPS'
IF NOT EXISTS
(
    SELECT 1
    FROM sys.crypt_properties cp
    JOIN sys.objects o ON cp.major_id = o.object_id
    LEFT JOIN sys.certificates cer ON cp.thumbprint = cer.thumbprint AND
    cp.crypt_type IN ('SPVC', 'CPVC')
    LEFT JOIN sys.asymmetric_keys ak ON cp.thumbprint = ak.thumbprint AND
    cp.crypt_type IN ('SPVA', 'CPVA')
    WHERE o.name = 'usp_sel_workqueue_applicationsWithNewMedia'
)
BEGIN
    ADD SIGNATURE TO [app].usp_sel_workqueue_applicationsWithNewMedia
    BY  CERTIFICATE [dyntsqlcert] WITH PASSWORD = 'oH8F*%G9pnyqfa';
END

question is,
in the event of a failover, when this current server becomes the secondary, alongside its c:\ drive, would the certificate still be valid?

I should be testing this instead of asking but I don't have a good testing environment in hand.

Since the certificate and permissions are all included at the database level, there should be no reason to re-create the certificate. Always On will take care of transferring the data to the secondary, it should just be there if it's synchronized or synchronizing but matching log block numbers in the DMVs.

If you wanted the certificate to "be" in the "C:\Certificates" folder and constantly use it (not clear as to why from the questions) then it'll need to be on each replica. I'd also say that this would be bad as the keys are on the same server as the databases, which sort of defeats the purpose.


How to grant access to SQL server agent to read network location file? Description: Cannot open the datafile "Z:\Folder\File.txt".  End Error
Error Code: 0xC004701A

Under Windows Administrative Tools and Services, find the login account for the Sql Server Agent and grant that login the appropriate rights to the folder(s)/File(s)

You can also find the login account for the Sql Server Agent by using the sql Server Configuation Manager and clicking on the Sql Server Services node.


Get WAL files from AWS RDS PostgreSQL instance We have a Postgres RDS instance on Amazon Web Services.  We have automatic backups enabled, and we take snapshots on a daily basis.  We would like to generate a local 'up-to-date' backup of the RDS instance that we can manage ourselves.  Running pg_dump against the instance is not sufficient because we want to be able to restore the database to any point in time.  We would prefer to have a local backup of RDS and all WAL files since that backup was taken.  Questions:

Is it possible to access the WAL files and backups that RDS is automatically generating in its backup routine?  This would be ideal.  I would want to download a local copy of them.  After initial investigation, I feel the answer to this question is 'no'.  It sounds like RDS is storing its WAL files and backups in S3, but it makes them inaccessible to us.  I would love confirmation.
Is there any other way to access transactions (WAL files) that have occurred on the RDS instance?  I imagine we should be able to create a Postgres database on an EC2 and 'feed' transactions from our primary 'live' RDS instance into this EC2 instance.  Once our EC2 instance is updated, we could pull WAL files from there.  What a headache, though :/  Is this setup possible?  What is the magic to 'feed' from our RDS instance to the EC2 instance so that it is always up to date?

Thanks!

Update: I've posted about this to the AWS forums - please go chime in and ask for it there.

At time of writing, Amazon RDS does not support physical replication outside RDS. You can GRANT users the REPLICATION right using an rds_superuser login, but you can't configure replication entries for outside IPs in pg_hba.conf.

Furthermore, when you create a DB parameter group in RDS, some key parameters are shown but locked, e.g. archive_command, which is locked to /etc/rds/dbbin/pgscripts/rds_wal_archive %p. AWS RDS for PostgreSQL does not appear to expose these WALs for external access (say, via S3) as it would need to if you were to use WAL-shipping replication for external PITR.

So at this point, if you want wal-shipping, don't use RDS. It's a canned easy-to-use database, but easy-to-use often means that it's also limited, and that's certainly the case here. As Joe Love points out in the comments, it provides WAL shipping and PITR within RDS, but you can't get access to the WAL to that from outside RDS.

So you need to use RDS's own backup facilities - dumps, snapshots and its own WAL-based PITR.

Even if RDS did let you make replication connections (for pg_basebackup or streaming replication) and allowed you to access archived WAL, you might not be able to actually consume that WAL. RDS runs a patched PostgreSQL, though nobody knows how heavily patched or whether it significantly alters the on-disk format. It also runs on the architecture selected by Amazon, which is probably x64 Linux, but not easily determined. Since PostgreSQL's on disk format and replication are architecture dependent, you could only replicate to hosts with the same architecture as that used by Amazon RDS, and only if your PostgreSQL build was compatible with theirs.

Among other things this means that you don't have any easy way to migrate away from RDS. You'd have to stop all writes to the database for long enough to take a pg_dump, restore it, and get the new DB running. The usual tricks with replication and failover, with rsync, etc, won't work because you don't have direct access to the DB host.

Even if RDS ran an unpatched PostgreSQL Amazon probably wouldn't want to permit you to do WAL streaming into RDS or import into RDS using pg_basebackup for security reasons. PostgreSQL treats the data directory as trusted content, and if you've crafted any clever 'LANGUAGE c' functions that hook internal functionality or done anything else tricky you might be able to exploit the server to get greater access than you're supposed to have. So Amazon aren't going to permit inbound WAL anytime soon.

They could support outbound WAL sending, but the above issues with format compatibility, freedom to make changes, etc still apply.

Instead you should use a tool like Londiste or Bucardo.


Does the recovery model affect query speed? Would using the Full Recovery model make something like a long running DELETE operation slower than it would be under the Simple Recovery model?

No, the transaction log behaves and performs the same way in all recovery models, the only difference is when log truncation occurs.

The MSDN article on the transaction log provides detail on this.


Print message comes out in batches I have simple query

WHILE ( @counter < 50 )
    BEGIN
        BEGIN TRANSACTION
        PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
        WITH    CTE
                  AS (
                       SELECT TOP 50000 * FROM MyTable
                     )
            DELETE FROM CTE OPTION ( MAXDOP 1 )
        SET @counter += 1
        COMMIT TRAN
    END

When I look at messages, the PRINT command does not return row every loop but shows up as multiple rows at ones. I have added one line and now it shows up 1 line at the time and the the whole process is much faster.

WHILE ( @counter < 50 )
    BEGIN
        WAITFOR DELAY '00:00:00.5' --< This line
        BEGIN TRANSACTION
        PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
        WITH    CTE
                  AS (
                       SELECT TOP 50000 * FROM MyTable
                     )
            DELETE FROM CTE OPTION ( MAXDOP 1 )
        SET @counter += 1
        COMMIT TRAN
    END

so why is it when I have added .5 second wait time to each loop iteration it actually runs faster and allows the print message to come out one at the time?

PRINT output is buffered.  If you replace PRINT ... with RAISERROR ... WITH NOWAIT you will see results as they happen.

DECLARE @msg VARCHAR(MAX);
DECLARE @counter INT;
WHILE (@counter < 50)
BEGIN
    BEGIN TRANSACTION
    SET @msg = 'Counter = ' + CONVERT(VARCHAR(4), @counter);
    RAISERROR (@msg, 0, 1) WITH NOWAIT;
    ;WITH CTE
    AS (
        SELECT TOP(50000) *
        FROM MySchema.MyTable
        )
    DELETE FROM CTE OPTION (MAXDOP 1);
    SET @counter += 1;
    COMMIT TRAN
END


SQL aggregate function performance in a table with many records that is well-indexed My question is about 3 tables that stores data of a single entity, and there is a report generation feature that refers these tables and calculate COUNT and AVG information.

There are three tables: review, rating, and rating_values, all three using MySQL InnoDB.

Review table

CREATE TABLE IF NOT EXISTS `review` (
  `review_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique review ID of this review.',
  `nid` int(10) unsigned NOT NULL,
  `sub_target` int(10) unsigned NOT NULL,
  `timestamp` int(10) unsigned NOT NULL DEFAULT '0',
  `score` float DEFAULT NULL,
  PRIMARY KEY (`review_id`),
  KEY `nid` (`nid`),
  KEY `uid` (`uid`),
  KEY `nid_uid` (`nid`,`uid`),
  KEY `score` (`score`),
  KEY `timestamp` (`timestamp`),
);

Review table acts as the the primary table. Each review can have 0 or more (we have a limit of 18 at the moment in application level) rating values. A rating value is something like "Food", "Location", etc for a restaurant review.

Rating table

'rating' (
  `review_id` int(10) unsigned NOT NULL, //foreign key to {review}.review_id
  `type` varchar(255) NOT NULL,
  `score` float DEFAULT NULL,
  KEY `review_id` (`review_id`),
  KEY `score` (`score`)
);

In this table, type is a machine name of the "food", "location", etc I mentioned above. There is a foreign key to the base table that stores these rating type information.
In our application, each rating can have multiple rating values that count towards the main rating. Say, there is "food", and there can multiple sub questions such as "food - appearance", "food - taste". These values can be 0 or more. If there are more than 1 sub question, this score field contains the average of the sub questions (please see the table below).

Rating value table

'rating_value' (
  `review_id` int(10) unsigned NOT NULL,// foreign key to the {review}.review_id
  `type` varchar(255) NOT NULL,
  `sid` int(10) unsigned DEFAULT NULL,
  `score` int(10) unsigned DEFAULT NULL,
  KEY `review_id` (`review_id`),
  KEY `type` (`type`)
)

This is where I expect most of the data would go into. Each sub question has an sid that is referred here.

A typical review would add rows like this:

Review:

review_id | nid | sub_target | timestamp | score
1         | 583 |         786| 1388167200| 4.32

Rating (please note how the two floats add up to 4.32 above):

review_id|type     |score
1        |food     |2.10
1        |location |2.22

Rating value:

review_id|type     |sid|score
1        |food     |1  |2.00
1        |food     |1  |2.30
1        |location |2  |2.12
1        |location |3  |2.22
1        |location |3  |2.22

We will be generating reports with COUNT and AVG on rating_value tables. However, there will be always a JOIN with review table, and the filter would be review.nid, which is indexed.
When accessing reviews, application will almost every time use the review_id, which are either primary or foreign keys in all tables.
Sometimes we need to CAST the score field to nearest integer.

Admittedly I'm not an expert in databases, but I spent a lot of time normalizing the above, and I ended up with the above database after all three steps. I'm eager to learn, so if you have suggestions to above table structure, please don't hesitate to mention them. Note that all review and rating tables will contain some extra data that I didn't mention here. the rating table has a text field to allow users to write a text review. That's why rating values and rating are separated.

The reports we generate are never going to run on all rows. Just a small set of rows filtered by the nid (plus additional filters).

My questions are:

Is it common to spread data of a single user operation across multiple rows? I expect the rating_value table to grow almost 10 times (in record count) than the review table.
While (inner) JOINing tables, and calculating averages, can database engines filter down the eligible rows using the indexes first?
If you have any thoughts about the above schema, please don't hesitate to comment on.

Is it common to spread data of a single user operation across multiple rows?

Think of the "entities":  User, Review, Rating.  A "user" may go to the restaurant many times, and give a "review" each time.  The "review" may give ratings for multiple things, such as 'food' and 'location'.

These are "Many-to-one" relationships, so Ratings has a review_id and Reviews has a user_id.  ("Many-to-many" relationships need an extra table.)

Yes, database optimizer can usually use indexes as needed.  We need to look at the specific SELECTs to say further.

KEY `nid` (`nid`), -- Redundant with the next; DROP this.
KEY `nid_uid` (`nid`,`uid`),

It looks like you did a shotgun approach to adding INDEXes.  Do EXPLAIN SELECT ... on your various SELECTs -- This is to see which indexes are actually used.  Then DROP the unused queries.

Every table should (must?) have a PRIMARY KEY.  For Ratings, it should probably be PRIMARY KEY(review_id, type).

Your "reports" are likely to do JOINs and may end up COUNTing and SUMming things extra times, so beware.  Do a sanity check on the results.  The overages can be fixed, but I'll let you get to that point before trying to explain the problem.


Master Slave database syncronization Hi I have just started reading about this replication concept and one thing I want to ask is that when we change something in the slave db how is it going to effect the master db? Is there a sync algo that will change it in master db as well?
and another thing is if there are multiple databases can we apply replication on them as well? like multiple databases in masters server replicated into multiple databases in slave server. Thanks

How replication happens:

DMLs on Master are written to Binary logs.
Slave connects to master from it's IO thread and reads the binary logs. Save them as Relay log.
Slave's SQL Thread execute the Relay log SQLs to apply the changes.

Now, if you need slave's changes to be reflected on master, you need your master to be replicating from your slave as well. That's known as master-master replication. You can find plenty of posts around that.

By default, all the databases are replicated. If you want to replicate only few of the objects (DB / tables) there are replication filters for that.


Free disk space after stopping query I have a table with a foreign key column to another table with the value on most rows being null. The table is around 10 million rows.
I (accidentally) ran a query that would have updated the rows where the foreign key was null to be x.
I stopped the query with
SELECT pg_cancel_backend(procpid);

as instructed here https://stackoverflow.com/questions/3508627/stop-long-running-sql-query-in-postgresql-when-session-or-requests-no-longer-e
The query did run for some time before I stopped it and disk usage went up while it ran.
None of the rows was actually updated and the disk usage didn't go down after the query had stopped.
Googling suggested VACUUM, which I ran on the table and it finished just fine but didn't free the used disk space. Tried running VACUUM FULL, which finished with
HINT:  Check free disk space.

What is taking the disk space and what can I do to free it?
I don't know what else I should tell so please ask for any other information that might help

Most likely bloat is taking your disk space. VACUUM FULL requires temporary space to work. It makes a copy of the table without bloat, so for big table it requires a lot of space.
If adding storage is not an option, then perhaps you can try using pgcompact from https://github.com/grayhemp/pgtoolkit - this page has examples of uses.


Query to find sql server services currently running Is there any query to find all SQL services currently running on the server including SSRS, SSIS, SSAS?

The DBATools PowerShell module is very useful for such tasks.
You can use Get-DbaService function from DBATools to get list of SQL related services installed on one or more servers.
Get-DbaService -ComputerName YourServer


Execute T-SQL Only If AlwaysOn Database Is Primary Little background on the system. It is SQL Server 2012 AlwaysOn Availability Group with 1 primary and 1 secondary.

I have been following this article (SSIS with AlwaysOn) to make SSIS work with AlwaysOn. I have a table that records the current role of the server and a job that runs every two minutes checking if it recently failed over. The problem I am running in to is the secondary only accepts readonly connections for the SSISDB and my script won't execute because of it.

Here is the script:

USE master;

DECLARE @last_role TINYINT;
SET @last_role = (
   SELECT TOP 1 [replica_role]
   FROM [dbo].[replica_role]
);

DECLARE @current_role TINYINT;
SET @current_role = (
   SELECT ROLE
   FROM sys.dm_hadr_availability_replica_states
   WHERE is_local = 1
);

IF (@last_role = 2 AND @current_role = 1)
BEGIN
    USE SSISDB;
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END

USE master;
UPDATE dbo.[replica_role] SET [replica_role] = @current_role;

I am confused as to why the error is happening because the IF statement is evaluating to false so the USE SSISDB is not getting executed. I could turn the statements into strings and use exec but I would rather not if possible. When I execute the script this is the error I get:

The target database ('SSISDB') is in an availability group and is currently accessible
for connections when the application intent is set to read only. For more information
about application intent, see SQL Server Books Online.

The client connection role for secondary replicas determines whether your database is always available, or only available to read-only connections, or not available at all. If you set the database to read-only connections, then your connection string has to include the parameter ApplicationIntent=ReadOnly as defined in that Books Online page.

When you try to connect to that database without specifying ApplicationIntent=ReadOnly, your query will fail.


How to clean every non-digit except the + at beginning of string with regexp_replace in Postgresql? Related to a comment, I would like to find a way to clean with regexp_replace in PostgreSQL any non-digit except the + at beginning of the string.

For example +(7)9125415501 shall become +79125415501

You can anchor the first character to be replaced to the begin of the string and search for everything not 0 - 9 or + and assert with a look behind, that any other replacement is preceded by any character, hence it's not the begin of the string.

regexp_replace('+(7)9125415501', '^[^0-9\+]|(?<=.)[^0-9]', '', 'g')

Look behinds were introduced in version 9.6. If you're on anything lower than that you can only use look aheads. You can do so reversing the string and instead of looking behind and anchoring for the beginning look ahead and anchor for the end. Rereverse it in the end.

reverse(regexp_replace(reverse('+(7)9125415501'), '[^0-9](?=.)|[^0-9\+]$', '', 'g'))


what is the right way to restart a mariadb galera cluster? I am trying to restart cluster. I have two problems in stopping cluster and starting cluster

In stopping cluster, I follow the instruction https://software.mirantis.com/reference-documentation-on-fuel-folsom/galera/

For safe, I stop replication on node by set global wsrep_on='OFF'
and then, I cannot stop it gracefully. I must kill it. It is not clean.

In starting cluster, I start the first node by service mysql start --wsrep-cluster-address=gcomm:// I am not sure it is the right way because it create new cluster.

The instruction is in https://mariadb.com/kb/en/mariadb/documentation/replication-cluster-multi-master/galera/getting-started-with-mariadb-galera-cluster/#state-transfer-failure not available to me.

What is the right way to me ?

As far as I read the right way is to use the init.d/systemd scripts of MySQL/MariaDB in order to stop a node. This will inform WSREP/Galera that the replication must stop and will save on the disk, in the MySQL/MariaDB data folder, in a file the last transaction number that the node has committed (So that next time it will boot, the nodes will compare their last committed transaction and sync).

You have to stop a node and wait a little bit (like a minute or so) before to stop the next node, to be sure the other nodes see that.

After that, it is very important to start again the nodes in the reverse ordre, so that the last node you shutdown is the first booting one, having the most up-to-date data.
Then the next nodes will be able to sync with the first one.

Using wsrep_on='OFF' doesn't sound right to me, WSREP is embedded in MySQL, so when MySQL gracefully stops, WSREP knows what it has to do.