mysql directory grow to 246G after one query, which failed due to table is full I was trying to run the following statement with the hope to create a join of two existing tables.

create table CRS_PAIR
select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, concat_ws(',', a.YTG, b.YTG) as YTG
from CRS_TESTING a, CRS_TRAINING b
where a.TESTING_C=b.TRAINING_C;

Currently the size of these two tables are:

mysql> SELECT table_name, round(((data_length + index_length) / (1024*1024)),2) as "size in megs" FROM information_schema.tables WHERE table_schema = "crs";
+----------------+---------------+
| table_name     | size in megs  |
+----------------+---------------+
| CRS_TESTING    |         36.59 |
| CRS_TRAINING   |        202.92 |
+----------------+---------------+

After a little over a day, The query finished and I got the following result.

140330  2:53:50 [ERROR] /usr/sbin/mysqld: The table 'CRS_PAIR' is full
140330  2:53:54  InnoDB: ERROR: the age of the last checkpoint is 9434006,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

It turned out that the size of /var/lib/mysql has grown to 246GB in disk space, and the disk run out of space. However, for some reason, the CRS_PAIR table does not show up in the shell. Even when I try to get the size of all databases.

mysql> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / (1024 * 1024) "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| crs                |            1426.4531 |
| information_schema |               0.0088 |
| mysql              |               0.6453 |
| performance_schema |               0.0000 |
+--------------------+----------------------+
4 rows in set (0.74 sec)

This is the show tables command.

mysql> show tables;
+----------------+
| Tables_in_crs  |
+----------------+
| CRS_TESTING    |
| CRS_TRAINING   |
 some other tables
+----------------+
9 rows in set (0.00 sec)

CRS_PAIR is not there.

May I ask if anyone can help me figure out where this mysterious table went to so that I can clean up my disk space?

The table simply does not exist. How can you verify this ?

Goto the operating system and run the following

cd /var/lib/mysql/crs
ls -l CRS_PAIR.frm

If that file does not exist, the table does not exist. If that is the case, then what's all that 246G bloated space ? You need to look the following

InnoDB Architecture

Take a closer look at ibdata1 ( system tablespace ). What does it comprise of ?

Data Dictionary
Double Write Buffer (support data consistency; used for Crash Recovery)
Insert Buffer (Buffers Changes to Secondary Non-Unique Indexes)
Rollback Segments
Undo Space (where the most uncontrolled growth can happen)

What you are the victim of is a large transaction that cannot be rolled back effectively.

I discussed InnoDB wild growth back on April 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?

You are probably wondering....

But I got a Table Full Error !!!

Think about what you were doing

You create an empty InnoDB table via DDL (it's technically a temp table at this point)
You attempted to load that empty table with 246G of BLOB/TEXT. There are tons of transactional records (for MVCC and Transaction Isolation Support) being accumulated in the interim.
Since you logically ran out of space due to BLOB/TEXT records in a single transaction, the query would have to rollback and the temp table would vanish, leaving no .frm to show for it.. What you are left with is a 246G undo log entry that could not hold records as wide as your query demands. InnoDB is notorious for not reclaiming space (See my very first StackOverflow post ever from October 29, 2010 on how to reclaim that wasted space).

CAVEAT #1

Table Full Errors for InnoDB is not that far-fetched because it is a logical failure to manipulate the Storage Engine. In your case, it would be due to BLOB/TEXT fields passing through logs and a Log Buffer that simply not big enough. I have another post about how you can get a "Table Full" Error when consuming all the Rollback Segments (How to solve "The table ... is full" with "innodb_file_per_table"?)

CAVEAT #2

If you insist on running that query and trying to make the table, you will have to expand the Transaction Logs (innodb_log_file_size) and the Log Buffer Size (innodb_log_buffer_size) (Look back at the InnoDB Architecture at the Log Buffer and the two Log Files).

My Post about doing so
Where my inspiration for that post came from (someone else's 5 years old post)


How can I combine two queries I would like to combine these queries:

UPDATE T1
SET Opening_date = T2.Opening_date
FROM FactTransaction T1
INNER JOIN DimStore T2 ON T1.cod_Store = T2.Cod_StoreKey

and

UPDATE FactTransaction
SET ORDINAL = DATEDIFF(DAY, Opening_Date, DATE)

In order to avoid insert the field Opening_date in my fact table. I don't know how to do it just with a query.

UPDATE T1 SET
Opening_date = T2.Opening_date,
ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.DATE)
FROM FactTransaction T1
INNER JOIN DimStore T2 ON T1.cod_Store = T2.Cod_StoreKey;

...if I understand you correctly.

Edit: Ok, just go with:

UPDATE T1 SET
ORDINAL = DATEDIFF(DAY, T2.Opening_Date, T1.DATE)
FROM FactTransaction T1
INNER JOIN DimStore T2 ON T1.cod_Store = T2.Cod_StoreKey;


Suggestions for large DB maintenance i have one large database (more than 1,500 tables and total db size of 56GB) and i would like to ask for tips and guidance regarding the maintenance of such large database!

Will the size be a problem anytime? For example when i reach 4000 or 5000 tables, i am going to have an issue? At the moment i have a "problem" with the backup/restore of it. A complete backup takes around 5 hours to finish, so you can imagine how long it takes the restore!

As you can understand this is not a proper solution, because in case of a db crash, it will take me at least 12 hours to be back online! Any suggestion?

As your dataset rises, ensure that your active dataset is still mostly in the innodb buffer pool.

As you increase number of tables make sure the table cache can contain all the active tables.

Overall you don't have a large database or a runtime problem but your skills in monitoring have to be in place.

Yes, you have a backup problem, in general look at xtrabackup, replication slave (that you could take offline and copy the datadir), binary logs for PITR (point in time recovery). For a logical backup look at mydumper running on a replication slave.

Tradeoffs are normally infrastructure required vs restore time. Work out your acceptable restore time, a couple of failure scenarios, and run the backup and restore procedure to ensure you are ready.


Starting a slave from behind master We're moving to a different cloud provider so need to migrate our database with as little downtime as possible.

Here's our current setup:

master
└───slave1
└───slave2
└───slaveBackup

A dump of slaveBackup was imported into our new master database, newMaster. slaveBackup logs changes to its bin logs using log-slave-updates and binlog_do_db to only log the correct database which is identically named throughout.

To reduce downtime we now want to add newMaster as a slave to slaveBackup, thus creating the following chain while we migrate:

master
└───slave1
└───slave2
└───slaveBackup
    └───newMaster
        └───newSlave1
        └───newSlave2

newMaster is replicated as intended to newSlave1 and newSlave2, all are identical.

We recorded the bin log file and position on slaveBackup made immediately after the dump and used this to add newMaster as a slave of slaveBackup:

CHANGE MASTER TO
    MASTER_HOST = 'slaveBackup',
    MASTER_USER = 'slave',
    MASTER_PASSWORD = '',
    MASTER_LOG_FILE = 'mariadb-bin.00000X',
    MASTER_LOG_POS = X;

Starting the slave worked perfectly and in time using SHOW SLAVE STATUS\G we could see it catch up to its master.

Here's where it gets weird. Despite the slave newMaster crunching through the bin logs, no rows are being inserted into the new database. What's weirder during this time Seconds_Behind_Master was always 0.

The logs were definitely being processed as the slave user is different on newMaster ('slave'@'X.X.X.X') vs slaveBackup ('slave'@'Y.Y.Y.Y') and a moderation made after the dump of this user caused newMaster to error and required SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; to recover.

Now the slave is fully caught up with master in terms of bin log file and position yet not data has been inserted into its database.

Why is this?

The bin logs on slaveBackup definitely have the correct USE DATABASENAME; statements before each insert/update, hence why I'm completely stumped.

To clarify the replication chain:

master -> slaveBackup -> newMaster -> newSlave1/2

One of the server IDs conflicted further down the chain which caused the issue of the bin logs syncing but not replicating

Warning to all, make sure all your server IDs are unique!


Storing emojis in MySQL I'm new to MySQL database. I want to store emojis in my database. I already have data in my database, and I don't want to lose it.
I don't know the proper way of storing the utf8mb4 character-set using the MySQL console. Is there any way of doing that using mysql-administrator?
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-----------------------+
| Variable_name           | Value                 |
+-------------------------+-----------------------+
| innodb_version          | 5.7.33                |
| protocol_version        | 10                    |
| slave_type_conversions  |                       |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2 |
| version                 | 5.7.33-log            |

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR
Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8               |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

I changed my.cnf file to
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

It shows no error, but neither does it store the emojis - when enter emojis it shows as ? this question mark.
I convert the utf-8 encoding to utf8mb4_general_ci:
|--------------------------+--------------------|
| character_set_system     | utf8               |
|--------------------------+--------------------|
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8_general_ci    |
|--------------------------+--------------------|

Do SHOW CREATE TABLE for one of the tables where you need Emoji.  It probably says 'utf8' on the columns and/or default for the table.  The columns must be utf8mb4.
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8mb4;

Will blindly change all the columns to utf8mb4.  This is probably the action you need.
Also, the connection needs to specify utf8mb4.  Since I don't know what client you are using, I can't provide specific information.  If necessary, run this right after connection: SET NAMES utf8mb4;
Terminology:

"character set", such as utf8 or utf8mb4 is the encoding for the characters.  This is where you are having trouble with storing Emoji.
"collation" is the ordering, such as whether 'a' < 'b' and whether there is case folding to make 'a' = 'A'.  Collation may be important when comparing (WHERE) or sorting (ORDER BY).


Find dates when exactly records changed for user I need to find the dates when exactly the status really changed.

Order should be analysed from Start to end period:

Start Period : 11-10-2014
End Period : 21-10-2014

Data:

ID, Name, Effective_date, Status
1   A       21-10-2014      OFF
2   A       20-10-2014      OFF
3   A       19-10-2014      On
4   A       18-10-2014      On
5   A       17-10-2014      On
6   A       16-10-2014      OFF
7   A       15-10-2014      On
8   A       14-10-2014      On
9   A       13-10-2014      OFF
10  A       12-10-2014      OFF
11  A       11-10-2014      OFF

I am using SQL Server 2000.

Expected output:

ID, Name, Effective_date, Status

2   A       20-10-2014      OFF
5   A       17-10-2014      On
6   A       16-10-2014      OFF
8   A       14-10-2014      On
11  A       11-10-2014      OFF

-- Guessed table definition
CREATE TABLE dbo.Example
(
    [ID]            integer NOT NULL,
    [Name]          char(1) NOT NULL,
    EffectiveDate   datetime NOT NULL,
    [Status]        varchar(3) NOT NULL,

    CONSTRAINT CUQ_dbo_Example_Name_EffectiveDate
    UNIQUE CLUSTERED ([Name], EffectiveDate DESC),

    CONSTRAINT PK_dbo_Example_ID
    PRIMARY KEY NONCLUSTERED ([ID])
);

-- Sample data from the question
INSERT dbo.Example (ID, Name, EffectiveDate, [Status])
SELECT 1, 'A', CONVERT(datetime, '21-10-2014', 105), 'OFF' UNION ALL
SELECT 2, 'A', CONVERT(datetime, '20-10-2014', 105), 'OFF' UNION ALL
SELECT 3, 'A', CONVERT(datetime, '19-10-2014', 105), 'On' UNION ALL
SELECT 4, 'A', CONVERT(datetime, '18-10-2014', 105), 'On' UNION ALL
SELECT 5, 'A', CONVERT(datetime, '17-10-2014', 105), 'On' UNION ALL
SELECT 6, 'A', CONVERT(datetime, '16-10-2014', 105), 'OFF' UNION ALL
SELECT 7, 'A', CONVERT(datetime, '15-10-2014', 105), 'On' UNION ALL
SELECT 8, 'A', CONVERT(datetime, '14-10-2014', 105), 'On' UNION ALL
SELECT 9, 'A', CONVERT(datetime, '13-10-2014', 105), 'OFF' UNION ALL
SELECT 10, 'A', CONVERT(datetime, '12-10-2014', 105), 'OFF' UNION ALL
SELECT 11, 'A', CONVERT(datetime, '11-10-2014', 105), 'OFF';

Solution:

-- Query
SELECT
    E.[ID],
    E.[Name],
    E.EffectiveDate,
    E.[Status]
FROM dbo.Example AS E
WHERE
    NOT EXISTS
    (
        SELECT NULL
        FROM dbo.Example AS E2
        WHERE E2.[Name] = E.[Name]
        AND E2.[Status] = E.[Status]
        AND E2.EffectiveDate = DATEADD(DAY, -1, E.EffectiveDate)
    )
ORDER BY
    E.Name,
    E.EffectiveDate DESC;

Execution plan:


MySQL: How to decrement an unsigned int column? I have a table with a column unsigned int, let's name it col1.

I want to decrement the column by a number, if value after decrement is less than 1 then set it to 1. Like this:

UPDATE `tbl1` SET `col1` = GREATEST(1, `col1`-3);

This works fine, but if col1 is 0 or 1 or 2 and I try to do -3 then MySQL throws an error.

I've tried this and this but didn't worked.

You can use a case clause

update tbl1 set col1 =  (case when col1 <= 3 then 1 else (col1 - 3) end);

As @RDFozz described :

With the OP's listed code, col1 - 3 must be computed, and since it's an unsigned integer, the result must be an unsigned integer. This code prevents the computation from happening unless the result would actually be an unsigned integer.

UPDATE :

Another possible way as suggested by @kondybas

update tbl1 set col1 = IF(col1<=3, 1, col1-3);


Index table for horizontal shard Lets assume we have a vary amount of clients, which could manage some stuff on our site. Each client has his own table, which is used to store data. For instance: client_1, client_2, ..., client_N. Each table could contain different amount of records, but there could be clients which could have more than a hundred of thousand records in a table.

The task is to create aggregate search on the site, which will display results from different tables for a certain search keyword. In this case we face with a problem that we need to perform up to N queries to N tables. This is not cool.

The idea is to create an index table which will collect all information from all tables and will be updated by insert/update/delete trigger on a concrete table.

I feel uncomfortable with this "index table" idea, because it could contain a huge amount of records and will be quite big overhead for our database. Is there any other approach how to build an index for horizontal shard?

There are many general solutions to the problem. Here are three that quickly come to mind.

SSAS
VoltDB
Map Reduce Framework

What you're describing is what would historically fall under an OLAP solution. If you can handle some lag time in your data then perhaps a solution such as SQL Server Analytic Services (SSAS) could provide you a solution. You could read data from these tables, perhaps via some secondary reporting server to not affect your operational database, and then perform these aggregations and processing of data you described.

However, this problem also has some newer solutions we see in the Big Data realm with a specific focus on "Velocity" not necessarily "Volume" of data. Velocity can mean either the processing of relatively static data quickly or the processing of dynamically changing data that is actively being ingested by the DB.

A second option is to allow a contemporary "NewSQL" database engine, such as VoltDB, to do this for you. VoltDB handles a lot of this "indexing" as you referred to it and it does so in real-time as opposed to per-aggregated data that may not be current. There are always performance tradeoffs when performing aggregations across many tables as opposed to a specific table but in my experience an in-memory DB engine, such as Volt, can beat traditional OLAP solutions when considering many metrics including processing speed and management.

Another option is to use the Map Reduce framework many DB engines support today. This implies that you're happy with off-line processing of a problem\query as opposed to real-time processing.

It's not an exhaustive list but hopefully that gives you some other ideas. I know that the triggers you are speaking of can easily spiral out of control due to hotspots in your tables especially if your SQL is not optimized. The one recommendation I will strongly make is to definitely get this processing off of your operational server and do this on a secondary replica\reports server.


Calculations with group-wise sum and row count Is it possible to sort a table by a value, divided by the sum of values within each year, again divided by number of rows for each year? I've tried to illustrate it by this faulty pseudo-query:

select *, value / sum(value group by year) / count(value group by year) result from bar order by result desc;

A very simple table to show what the result should be.

+-------+-------+-------+---------------+
| title |  year | value | result        |
+-------+-------+-------+---------------+
| Hello |  2017 |  100  | 100 / 250 / 2 |
| Hello |  2017 |  150  | 150 / 250 / 2 |
| Hello |  2018 |  300  | 300 / 903 / 3 |
| Hello |  2018 |  301  | 301 / 903 / 3 |
| Hello |  2018 |  302  | 302 / 903 / 3 |
+-------+-------+-------+---------------+

I thought I could get there by creating dummy columns, but that just got an error message.

update bar set groupsum = sum(value) group by year;
update bar set groupcount = count(value) group by year;

Does anyone have a solution, preferably a short and sweet one? (I'm using MySQL if it matters.)

Please Alter you column year to col_year and value to col_value because YEAR is a builtin function of mysql. and run below query. I also attached screenshot of query and result. Thanks

 SELECT title, col_year, col_value, (col_value/total_sum/total_row) AS  result FROM bar
LEFT JOIN
(SELECT SUM(col_value) AS total_sum, COUNT(col_value) AS total_row,col_year AS qq FROM bar GROUP BY col_year) AS sub_query
ON sub_query.qq = bar.col_year ORDER BY col_value


How can a PostgreSQL standby decide whether its primary is still active or not I have a setup with asynchronous streaming replication so that PSQL1 is primary and PSQL2 is hot standby.
Since PSQL2 is running in hot standby mode I can do run queries against it.
Is it possible on PSQL2 to tell whether PSQL1 is still active or already shutdown?
I tried select pg_is_in_recovery(); but it returns true regardless whether PSQL1 is running or not.

For streaming replication, you can check the pg_stat_wal_receiver view. There will be one line here when wal receiver is streaming.
Another question is why you need such information. For monitoring purposes, the amount of replication delay is much more important. Async replica may be significant lagged, but primary and wal receiver still alive (e.g. while reach the network bandwidth limit). Usually on hot standby:
select now() - pg_last_xact_replay_timestamp();

and pg_stat_replication on primary.


Insert Statement Take too long to insert - MYSQL Following Insert query taking too long time to insert :

  Query_time: 14.798782  Lock_time: 0.000062 Rows_sent: 0  Rows_examined: 0

SET timestamp=1523459374;
insert into patient_last_examined
     (patient_id,operator_id,section_name,created_date,status,save_or_review)
VALUES  ('227350','72','Allergies','2018-04-11 11:09:20','0','1');

Table have 17k records before insertion and
Total Column : 13
Index Columns : id (PK), patient_id,section_name
Db Engine : Inno DB

Can you please suggest how to increase the performance?
(At timestamp=1523459374, 15-20 more insertion take place for other tables)

The table is MyISAM

You should really switch to InnoDB.

If something taking 14.7 (or more) seconds touches that table, then writes can occur until it finishes.  The "something" could be mysqldump, a complex SELECT, etc, etc.

InnoDB avoids most such lengthy hangs.


How to securely store the password for an Oracle DB link? Having a system consisting of two linked Oracle DBs, we currently have the password for the dblink stored in a table, in order to be able to change it (session issues) and to re-created the dblink in some weird cases.

Having a password in a table, however, is not a particularly good move from the security perspective. So are there any alternatives to this? Does Oracle allow safely storing the password in a keystore or vault or something? Can you create a dblink without actually retrieving the password?

Your real problem is not about storing the password in a table securely, but the need for repeatedly changing and recreating the dblink.

Yes, you can store a password in a wallet, and no, you will not be able to retrieve the actual password from the wallet.

To store a password securely in a table, you can encrypt it (e.g. with DBMS_CRYPTO) and store the encrypted value. The encryption key should be stored outside the database.

FYI: Oracle stores dblink passwords in a table, in an encrypted form. The encryption key is a constant (kind of) and decryption takes no time.

12c: https://mahmoudhatem.wordpress.com/2016/12/08/reverse-engineering-db-link-password-decryption-in-plsql/

11g: https://www.krenger.ch/blog/find-password-for-database-link/


SQL Cluster without a shared disk. Possible? Is it possible to create a two node cluster using two basic network servers that only have local disks?

One will act as an idle standby server.  Without knowing too much about clustering I'm not sure if a separate shared disk array is required.

If so, is it possible to use a DAS setup, or does it have to be a SAN that supports clustering?

I am afraid that this is not possible. Clustering works on the basis of resource ownership and acquisition; so anything that is not accessible to all nodes in the cluster cannot be used.

If shared storage is not an option for you then have a look at using mirroring which does not require storage to be shared across multiple servers. You could even use a witness server to automatically fail over should the primary become unreachable for any reason:

DATABASE MIRRORING

http://msdn.microsoft.com/en-us/library/ms189852.aspx

SQL CLUSTERING CONSIDERATIONS

http://msdn.microsoft.com/en-us/library/ms189910.aspx

I hope this helps you.


Delete back ups more than 2 weeks in sql server I am not a DBA, I need to delete the USER_DATABASE backups older than 2 weeks. Is this below stmt work for this scenario?

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASE',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 336

This below script is my regular backup.

declare @Date varchar(16),
        @query nvarchar(100);

select @Date = convert(varchar,Year(getdate())) + right('00' +  convert(varchar,Month(getdate())),2) + right('00' + convert(varchar,Day(getdate())),2) + right('00'+convert(varchar,datepart(hh,getdate())),2) + right('00'+convert(varchar,datepart(Mi,getdate())),2);

select @query = N'BACKUP DATABASE db_CP_Staging TO  DISK = ''D:\backups\db_C_Staging\db_C_Staging' + @Date + '.bak' + '''';

exec sp_executesql @statement = @query;

How can I add cleanup time to this query? Can some one help me on this?

Yes, it looks like you're using Ola Hallengren's backup scripts which operate in hours, so 14 days * 24 hours = 336 hours.

Every time this is run, a full backup of the user databases will be taken and any copies older than 2 weeks will be deleted.

Edit:

Based on the OP's edit, you are fine to schedule a job executing your first script as a replacement for the second script you posted. The frequency of this will depend on your backup and restore needs for your business.

As Kris said, this delete only occurs after a successful backup unless you specify the parameter @CleanupMode = 'BEFORE_BACKUP'


Find efficient way of migration 2TB GBK table in SybaseIQ to UTF-8 files My project need to migrate more than 2TB raw data from SybaseIQ to another Database. SybaseIQ using GBK, the target Database using UTF-8, the target database can only load from UTF-8 files.

We use The Extract Options to extract data to GBK files first, then convert data into UTF-8, the converting is very slow because it's single processor only.

Can anyone tell me the efficient way of extract data from SybaseIQ and convert charset and encode to UTF-8?

I've promoted about 2X speed by named pipe.

First step:(create a named pipe)

mkfifo p1

Second step:(waiting to convert)

cat p1| iconv -f GBK -t UTF-8 > target.file

Last step:(export to p1, see the sql scripts below)

SET OPTION TEMP_EXTRACT_NAME1 = 'p1';
SELECT * FROM MY_TABLE;
SET OPTION TEMP_EXTRACT_NAME1 = '';


In general should we avoid using ORM's with Redshift My team is considering utilizing Hibernate/Envers for a granular point in time rollback solution for Redshift data. I have heard that using an ORM with Redshift is not a good idea. Are there particular drawbacks related to using an ORM such as hibernate with Redshift?

There are known drawbacks related to using ORMs, period. They are a result of the inherent impedance between the object-oriented and relational models of the world. These issues will probably be exacerbated by the lack of Redshift-specific Hibernate dialect, and using a Postgres dialect will likely cause additional problems, as many Postgres features are not supported by Redshift.

In short, my opinionated advice would be, don't use an ORM for any non-trivial application, Redshift or not. "[G]ranular point in time rollback solution for Redshift data" doesn't sound like one.


TimescaleDB distributed setup strategy What is the best setup strategy for TimescaleDB?
My initial thoughts based on the TimescaleDB FAQ was that it is indeed recommended and possible to basically have one database that contains both time series data and my other regular data.
I am just wondering what is the strategy when using multinodes. We can create a distributed hypertable, but what about the rest of my non-timeseries data? Can I distribute them?
Does it make sense to keep TimescaleDB data into its own "cluster" of nodes and have separate instances (perhaps even use other extension like Citus) for non-timeseries data ?

Current version of TimescaleDB, which is 2.0.0, doesn't support push down of aggregates to data nodes (see limitations) and all joins are performed on the access node. So for the queries, which join a distributed hypertable with non-timeseries data (which, I guess, are stored in normal tables), the data will be brought to the access node for the join. Thus you might want to store non-timeseries data on the access node. You can also manually distribute non-timeseries data, however there will be no performance benefits.
Note that if a hypertable refers to non-timeseries data through a foreign key, it is necessary to have the non-timeseries table on the access node and all data nodes and the timeseries data be partitioned across data nodes in the same way as the hypertable.


Copying a database for testing in a safe way For testing purposes I have to create a copy of a production MySQL database that cannot be touched.

These are the steps I would follow:

Create a reduced size database backup:

mysqldump prodDB --single-transaction --where="true limit 20" > test.sql
Create a new test database: testDB
Load the dump file in the new test database:

mysql testDB < test.sql

Is this procedure correct?
Can I be sure that the original database will not be modified whatsoever?

Thank you!

System: MySQL 5.1 with InnoDB

Is this procedure correct ?

If you have no foreign key constraints, then yes this is correct.

If you have foreign key constraints, some data may be inaccessible. You would have to fix it.

  Can I be sure that the original database will not be modified whatsoever?

After the mysqldump do the following:

grep -c "DROP DATABASE" test.sql

If you get back 0, then yes the original database will not be modified whatsoever


UPDATE a table with just ~2000 rows is slow, would LOCK help? RDS Performance Insights tell me UPDATE foobar SET expire=0 is slow , foobar has about 2000 rows and there's an index on expire, there needs to be. Now, would it be faster if I did a LOCK TABLES foobar WRITE before the UPDATE? I feel like UPDATE is locking piecemeal which causes the problem.
Alternatively, as a terrible hack, I could imagine doing a CREATE TABLE LIKE ; INSERT - SELECT ; RENAME TABLE series of commands which should be lightning quick...

I hope you are using InnoDB.
Was that the entire query?  So, you are changing all rows of a 2K-row table?
Why?  Might there be another way to design the schema?  Please describe the task further.  There are several things that come to mind -- and they won't involve such a sluggish action.
How big is the table?  Is that 2000 rows out of a million?  Or 2000 out of 3000.
The query must save the old value of 2000 rows, update 2000 rows, update 2000 rows in the index.  Then later it must toss the saved old row copies.
How big is innodb_buffer_pool_size?
If it is a single statement, there is already a LOCK.  So don't bark of that tree.


Storing a large number of small size entries in MySQL I have a database that stores readings from numerous gauges.  Depending on the gauge, there may be data for that gauge every 1-5 minutes with multiple readings (i.e.- weather station and it stores 5 different readings).

I currently store the data by date.  I group everything for a particular gauge for a single date together and store is as xml in a text column.

Would it be more efficient to store this data as individual rows?

I'm trying to decide which is the smartest solution long term (i.e.- fewer rows with more data per row, or many more rows, each being smaller).

It is a read heavy environment.

EDIT:

By 'efficient' I am most concerned about speed and resource usage (if I pull back a year's worth of data, which would be least processor/memory intensive).

I almost exclusively use this data to graph for clients.  The number of readings per timestamp is variable, from 1 to 6 readings.

The database is currently an INNODB.

EDIT 2:

I'm looking more towards keeping the data in MySQL for ease of access/updating.

The answer I was looking for more was whether it would be smarter to save each individual timestamp (which may have multiple readings) in an individual row or whether to group multiple timestamps (i.e.- up to a day's worth) into a single row.

Thanks

It depends on exactly what you're trying to do with the data -- if it's only being used for graphing, and you don't need high resolution the further out it goes (eg, you're not trying to plot a graph with full temporal resolution for a day from 1 year ago), you might actually want to look at RRDTool rather than a relational database.

If you're going to ever need to do analysis of the values (how often does a place get above 80°F?), you'll want to store discrete values, not an XML structure;  but you could also use flat file stuctures that are meant for dealing with this type of data (eg,  CDF, NetCDF ... maybe even HDF )

update :

I'd store each time as a separate record, as it makes it easier to adjust the granularity when graphing.  For instance, to extract the high/low/mean for each hour:

SELECT   min(date_obs),min(temp),max(temp),avg(temp)
FROM     observations
WHERE    date_obs between ...
GROUP BY floor(unix_timestamp(date_obs)/3600)

Also, it allows you to change the cadence for the measurements without needing to change the table structure.


Read only privileges I have created a user and want to grant the user read only privileges on all database schemas. Can I do that using a single grant statement for all 20 schemas.

This may not exactly what you want, but it should be close enough.

grant read any table to username;

This provides read privileges on all user tables and views (but no privileges on dictionary tables or DBA_ catalog views).

If you want to this only for specific schemas, you need to grant the specific object privileges.


How to interpret this "db.explain()" for this mongodb query db.tablebusiness.find({ "LongitudeLatitude" : { "$within" : { "$centerSphere" : [[106.772835, -6.186753], 0.0089967464101566] } }, "indexContents" : { "$all" : ["warung"] }, "Prominent" : { "$gte" : 15 } }).limit(50).explain;

{
        "cursor" : "GeoBrowse-circle",
        "nscanned" : 50,
        "nscannedObjects" : 50,
        "n" : 50,
        "millis" : 214,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "LongitudeLatitude" : [ ]
        },
        "keysChecked" : NumberLong(13215),
        "lookedAt" : NumberLong(13215),
        "matchesPerfd" : NumberLong(13215),
        "objectsLoaded" : NumberLong(6674),
        "pointsLoaded" : NumberLong(0)
}

The query search for businesses within a circle that contains certain character. Typical data looks like this

/* 4 */
{
  "_id" : "made's-warung__-6.18_106.78",
  "BuildingID" : null,
  "Title" : "Made’s Warung",
  "InBuildingAddress" : null,
  "Building" : null,
  "Street" : "Seminyak-Kuta",
  "Districts" : [],
  "City" : "Bali",
  "Country" : "Indonesia",
  "DistanceFromGoogleAddress" : 0.0,
  "Checkin" : 0,
  "Note" : null,
  "PeopleCount" : 106,
  "Prominent" : 106.0,
  "CountViews" : 0,
  "StreetAdditional" : null,
  "LongitudeLatitude" : {
    "Longitude" : 106.783746,
    "Latitude" : -6.177905
  },
  "Rating" : {
    "Stars" : 0.0,
    "Weight" : 0.0
  },
  "CurrentlyWorkedURL" : null,
  "Reviews" : [],
  "ZIP" : null,
  "Tags" : ["Asian Restaurant"],
  "Phones" : [],
  "Website" : null,
  "Email" : null,
  "Price" : null,
  "openingHour" : null,
  "Promotions" : [],
  "SomethingWrong" : false,
  "BizMenus" : [],
  "Brochures" : [],
  "Aliases" : [],
  "indexContents" : ["asian", "sian", "ian", "an", "n", "restaura", "estauran", "staurant", "taurant", "aurant", "urant", "rant", "ant", "nt", "t", "made’s", "ade’s", "de’s", "e’s", "’s", "s", "warung", "arung", "rung", "ung", "ng", "g"]
}

Also look at this one:

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^asi/] } }).limit(50).explain();

{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 50,
        "nscannedObjects" : 50,
        "n" : 50,
        "millis" : 2977,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {

        }
}
>

What does it mean? Is indexes used? Which one?

Yes, an index is used in both cases, just with different operators.  The key to determining whether a query has used an index or not is the "cursor" : field.  If this says BasicCursor then no index was used (a table scan was performed).  Otherwise an index was used.

In your case you are using a "2d" geo index each time, in fact for $nearsphere and $within, $centersphere to work, you must be using a geo index.

As for the rest of the output, they are both returning 50 objects, the first is significantly faster.  You can see the explanation of the various fields here:

http://www.mongodb.org/display/DOCS/Explain

You have not listed what indexes you have defined, but if you wanted to attempt to speed things up you can try adding a compound index.  Remember a query in MongoDB can only use one index at a time and for the geo commands to work that must be a 2d/geo index, so if you have indexes on your other fields, they will not be used unless they are part of a compound index with your geo index.  You should also note that the 2d index must currently be the first field in the compound index, per here:

http://www.mongodb.org/display/DOCS/Geospatial+Indexing#GeospatialIndexing-CompoundIndexes


Get the list of tables used in a stored-procedure for a database our company is planning to administer the report server database and the requirement is to get the list of the tables used in stored-procedures in the productions server database.Is there any query/function to retrieve this information ?

If you use dynamic SQL to construct table names (or accept them as arguments), or use nested views, it becomes very difficult to automate this. However if you stick to traditional T-SQL and proper references you can get there with this:

DECLARE @procid int = OBJECT_ID(N'dbo.procedurename');

;WITH src AS
(
  SELECT name =
      COALESCE(QUOTENAME(d.referenced_server_name)   + N'.', N'')
    + COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
    + QUOTENAME(d.referenced_schema_name) + N'.'
    + QUOTENAME(d.referenced_entity_name)
  FROM sys.sql_expression_dependencies AS d
  WHERE d.referencing_id = @procid
)
SELECT name FROM src GROUP BY name;

And if you just want a catalog of all references, you can do this:

;WITH src AS
(
  SELECT [procedure] = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name),
      ref =
      COALESCE(QUOTENAME(d.referenced_server_name)   + N'.', N'')
    + COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
    + QUOTENAME(d.referenced_schema_name) + N'.'
    + QUOTENAME(d.referenced_entity_name)
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o
  ON d.referencing_id = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE o.[type] = N'P'
)
SELECT [procedure],ref
  FROM src
  GROUP BY [procedure],ref;


Transaction control to change triggers I need to create a trigger in a specific table that already has another trigger.
What I want to do is:

Disable the old trigger
Enable the new trigger (In a new history table)
Populate the values from the previous history table to the new one.

How should I control the transaction in this case to make sure I will not mess up the data with both tables?

I was considering using TABLOCK hint, so it would be something like this:

BEGIN TRAN

    SELECT 1
    FROM  table
       WITH (TABLOCK, HOLDLOCK)

--Disable the old trigger
--Enagle the new trigger
--Populate the data to the new table
COMMIT

Does it make sense?
Another question: Is there a way to automaticly add a column in the history table (trigger table) in case we add a new column in the main table?

Thank you in advance!

In my testing, this DID lock the table after I executed the disable for the old trigger up until I committed my transaction.  I had to put the GO statements in before disable/enable to keep SSMS from fussing.

begin transaction
go
--Disable the old trigger
go
--Enable the new trigger
go
--Populate the data to the new table
commit

As far as automatically adding a new column to the history table upon adding a column to the main table, see Aaron Bertrand's post on how this 'might' be achieved (and caveats) using DDL triggers.


Internal MariaDB error code: 1927 ('Connection was killed') Edit: filed a bug report at https://jira.mariadb.org/browse/MDEV-14493

Mariadb nodes freeze occasionally with the following error in the logs, any clues?

(drupal app, db freezes on clearing application cache)

[Warning] WSREP: Failed to apply app buffer: seqno: 903792, status: 1
Nov 23 21:42:55 websrv4 mysqld[1725]: #011 at
galera/src/trx_handle.cpp:apply():351
Nov 23 21:42:55 websrv4 mysqld[1725]: Retrying 2th time
Nov 23 21:42:55 websrv4 mysqld[1725]: 2017-11-23 21:42:55 140081879742208
[Warning] WSREP: BF applier failed to open_and_lock_tables: 1927, fatal: 0
wsrep = (exec_mode: 1 conflict_state: 5 seqno: 903792)
Nov 23 21:42:55 websrv4 mysqld[1725]: 2017-11-23 21:42:55 140081879742208
[ERROR] Slave SQL: Error executing row event: 'Connection was killed',
Internal MariaDB error code: 1927

I understand from the comments that your application is writing to more than one DB node. This is known for causing deadlocks, and this appears to be the underlying problem in your first crash log that you uploaded to the MariaDB Jira. If you're using a DB proxy (like MaxScale or ProxySQL), then make sure to use a read-write splitter so you write to only one node, but read from all.


Design database for real estate property management? I have design this database diagram to real estate property management just I want to see if I get it right especially the relation between properties and finances because I made it one(property) to many(finances"bank loans")?

Properties can be resold multiple times, refinanced multiple times, have multiple loans/cash financing options per, be sublet/subdivided, have to follow government reporting requirements for all of that, etc. You need a thorough understanding of the business and proper specifications; the fact that you asked the question you have asked suggests to me that either you don't, and/or that you lack experience in the biz. Also, "property" is the singular of "properties", so, "propertyID".


Facing warning message while creating tar.gz file I am creating a tar.gz of database hot backup files.

During execution of tar command, I am getting this warning for only this file every time I run tar:

  /u01/app/oracle/oradata/db5/sysaux01.dbf: file changed as we read it

I intend to perform clones and recoveries of Oracle database using this tar file. Will it cause problems?

As you have said, you are trying to create a hot image copy backup for cloning and recovery purposes, it's not the recommended way to take image copy backup. The file may contain fractured blocks-which have been changed during the copy command(tar in your case). The tar command has no intelligence to check whether the block has been changed or not during the archiving process resulting broken block known as split or fractured blocks included in the file.

Warning from tar command has spoken the same theme as I mentioned here. The file was being modified by Oracle processes, possibly doing read/write operations, as you said, you were trying to archive these files while the database is up and running.

Instead, if you use RMAN it has the ability to know whether the block is fine or changed by comparing the header and tail of a data block.

Documentation: RMAN Backup Concept/Image Copy Backup


Can a role be granted roles+privileges in oracle all at the same time? I know that you can grant a role:

Other roles
System privileges
Object privileges

But, can you grant to the same role all at once? I mean, for example you grant a role:

2 roles
1 system privilege
1 object privilege

Is this possible?

If so, what is the priority of the grants? Are roles the top priority, or the privileges?

Yes, you can grant as many roles or privileges to a role as you want.
The order or priority does not matter because a role or privilege always permit something. Unlike for example Windows file permissions you don't have "allow write" and "forbid write" - where the priority/precedence matters.

Either the role gets a privilege or he does not.

However, you can create ROLES as use the ROLE as marker in PL/SQL at DBMS_SESSION.IS_ROLE_ENABLED(...), there is would be possible to "restrict by granting" and the order and priority could matter. But such design would be quite ugly and is not recommended.


How to secure my data queried from my local MySQL server on a remote machine with root shared with untrusted entity? I have a remote client machine put in a server center owned by an entity who must also have the root of the client machine according to industry regulations. The client machine runs a C++ program that uses libmysqlclient.so and periodically queries a MySQL server located in my office via the internet.

The data is highly sensitive so I need to protect the data from being disclosed in the whole process of data transfer. Nobody is trusted in the server center including its owner who has the root of the client machine.

At first, the json format database config of the C++ program contains explicit IP, port, user, and password. Anyone who has root can read those and use them to connect to our server from the client machine. Since IP and port must be disclosed to the owner of the server center who manages the intranet, gateway, and firewall according to regulations, I replace user and password with encrypted strings in the config and the C++ program will decrypt them at startup to obtain the real user and password to connect to the server.

The root user, however, may replace libmysqlclient.so in the system with a modified version so that the user and password are printed out when a connection is being established. Then I change the C++ program to static linking to libmysqlclient.a at compile time.

However, the data being transferred is still clear-text. So I enable SSL on the MySQL server and alter the user as REQUIRE X509. Then man-in-the-middle attack is supposed to be not easy.

To make the two-way TLS work, I also need to put ca, client-cert, and client-key files to the client machine. I'm wondering if the server center owner can capture the encrypted traffic being transferred and use those files to decrypt the data without any knowledge of the database username and password?

Also is there any obvious vulnerability on the client side after having all these setups? Let's assume that the net structure and the use of MySQL are not to be changed.

Assuming you only need to run 'a C++ program' when you are logged in, maybe use a reverse SSH tunnel when you connect -R /tmp/mysql.remote.socket:/var/run/mysql/mysql.sock (as ssh argument). This would be easier to sort out that SSL and not require firewall changes as its piggybacking on your ssh connection in.

There is no absolute way to run a program on an untrusted machine with 0 risk of malicious intent. Mitigations include:

staticly compiling the executable
on the server sshfs mount a directory on your server with this executable on it (with -o reconnect,idmap=user), make this readonly and make it operate over your reverse ssh tunnel (add another -R remoteport:localserver:localport for the sshfs connection to the ssh server).
protect against gdb attacks using mprotect and madvise(DONT_DUMP) so information isn't stolen for some points during all points at which the program is run
there is some technology supported by hardware where a guest VM can run in a mode where the host cannot access the guest memory (needs further investigation)


Not sure why code is failing when CONCAT_NULL_YIELDS_NULL is set to OFF In my question over at Pull every other row, starting at 0, and concatenate them together user @AaronBertrand provided me with a chunk of code that worked fine in test but as soon as I put the procedure using the code into place and tested from the calling app I get an error.

  SELECT error: SELECT failed because the following SET options have
  incorrect settings: 'CONCAT_NULL_YIELDS_NULL'

In testing if I set CONCAT_NULL_YIELDS_NULL OFF the procedure will fail when called from SSMS but if I set CONCAT_NULL_YIELDS_NULL ON it works fine, further testing is showing the calling app to explicitly be setting CONCAT_NULL_YIELDS_NULL OFF and I can not override that. So I need to figure out how to update the code block to prevent this problem.

SELECT @TreeLocationStructure = STUFF (
    (
        SELECT ' -> ' + TreeLocation
        FROM (
            SELECT rn = ROW_NUMBER()
            OVER (ORDER BY ID), TreeLocation
            FROM #TreeSplit
        ) AS x(rn, TreeLocation)
        WHERE rn % 2 = 1
        ORDER BY rn FOR XML PATH,TYPE
    ).value('.[1]','nvarchar(max)'), 1 ,4, ''
);

Try changing your procedure to:

ALTER PROCEDURE dbo.ProcedureName
  @Params ...
AS
BEGIN
  SET NOCOUNT ON;
  SET CONCAT_NULL_YIELDS_NULL ON;

  ... the rest of your code ...
END
GO

You might also want to get in touch with the vendor of the software, as changing this setting to OFF has been deprecated since SQL Server 2005, and soon the setting will have no effect or cause an error:

http://technet.microsoft.com/en-us/library/ms143729(v=sql.90).aspx


IBM DB2 9.7 and latest 10 version compatibility At some point projects need to move from IBM DB/2 9.7 to some 10 version. Are there any significant compatibility issues that should be taken into account? We ofcourse use tables in schemas, but then also triggers and some stored procedures.

Not as much a compatibility thing, but definitely something to watch out for.....table spaces that are still DMS/SMS. You can upgrade a V 9.7 database with them to a V 10.x database, and it will accept them. You can't create DMS/SMS anymore, but it appears to be backwards compatible for migration purposes. You will probably want to convert to automatic storage as soon as possible.

Now...here's what really to watch out for....SMS/DMS tablespaces from a database originally created prior to version 9.7 and then migrated to version 9.7. Reason being, there is something called the reclaimable storage attribute that was not introduced until version 9.7 and unfortunately it is only set on table space creation. It is not added if you migrate to 9.7 or higher (even if you migrate the table space to automatic storage!!!!). This becomes a problem only in the sense that if a REORG is issued after mass deletes, DB2 may not release the freed up disk back to the OS. You may need to lower the high water mark to do so. However you cannot lower the high water mark on DMS or converted automatic storage table spaces if the table space was from a database created prior to 9.7. From what I have researched, the only way to resolve that issue (ie, getting tablespaces with the reclaimable storage attribute) is to create new table spaces with copies of your tables and unload and load all your data into these new tables and then drop the old ones.


How Can I Convert SQL Server 2012 Version 11.0.3000.0 to SQL Server 2012 Version 11.1.3000.0? I have SQL Server 2012 with version 11.0.3000.0 .

I need upgrade my version to SQL Server 2012 SP1 because I need install Cumulation 4 in Sql Server 2012 Cumulative Update 4 for use PowerPivot in Multidimensional Database.

Before install Sql Server 2012 Cumulative Update 4 I need Upgrade my SQL Server to Service Pack 1.

How Can I Update My Version to SP1.

I sow this Link and download SQLServer2012SP1-KB2674319-x64-ENU.exe and SQLServer2012SP1-FullSlipstream-ENU-x64.iso but this not work in my Server.

I have this error :

It looks like you are already at Service Pack 1 (this is why MSSQLSERVER is checked and greyed out). 11.0.3000 and 11.1.3000 are essentially the same thing, different files just have different reasons for exposing a different number in the middle - that number is really not relevant to your problem.

So now you just need to download and install the latest CU or, if you really want CU #4 specifically, you can download that here (just keep in mind  you are missing a boatload of fixes if you limit yourself to that update).

P.S. you check the version of SQL Server by opening a query window and running:

SELECT @@VERSION;

The method you're using now checks the version of Management Studio - these are client tools that can be older or newer than the server service itself.


Transform 14 columns into 2 (SQL Server 2008) - Pivot/Cursor not helping After hours googling pivot and cursors. I couldn't think or figure out how to do this.

I have this query:

declare @banco varchar(100)
set @banco = 'CascVW'

declare @tbltemp1 table
    (
      prodt_8 float,
      prodt_9 float,
      prodt_10 float,
      prodt_11 float,
      prodt_12 float,
      prodt_13 float,
      prodt_14 float,
      prodt_15 float,
      prodt_16 float,
      prodt_17 float,
      prodt_18 float,
      prodt_19 float,
      prodt_20 float,
      prodt_21 float
     )

insert into @tbltemp1
   Select
      Round(Case When Round(Avg(Convert(float,HC08)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod08)),1) / Round(Avg(Convert(float,HC08)),1)  End,1) As Prodt_8,
      Round(Case When Round(Avg(Convert(float,HC09)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod09)),1) / Round(Avg(Convert(float,HC09)),1)  End,1) As Prodt_9,
      Round(Case When Round(Avg(Convert(float,HC10)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod10)),1) / Round(Avg(Convert(float,HC10)),1)  End,1) As Prodt_10,
      Round(Case When Round(Avg(Convert(float,HC11)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod11)),1) / Round(Avg(Convert(float,HC11)),1)  End,1) As Prodt_11,
      Round(Case When Round(Avg(Convert(float,HC12)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod12)),1) / Round(Avg(Convert(float,HC12)),1)  End,1) As Prodt_12,
      Round(Case When Round(Avg(Convert(float,HC13)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod13)),1) / Round(Avg(Convert(float,HC13)),1)  End,1) As Prodt_13,
      Round(Case When Round(Avg(Convert(float,HC14)),1)  = 0 Then 0 Else Round(Avg(Convert(float,Prod14)),1) / Round(Avg(Convert(float,HC14)),1)  End,1) As Prodt_14,
     Round(Case When Round(Avg(Convert(float,HC15)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod15)),1) / Round(Avg(Convert(float,HC15)),1)  End,1) As Prodt_15,
     Round(Case When Round(Avg(Convert(float,HC16)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod16)),1) / Round(Avg(Convert(float,HC16)),1)  End,1) As Prodt_16,
     Round(Case When Round(Avg(Convert(float,HC17)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod17)),1) / Round(Avg(Convert(float,HC17)),1)  End,1) As Prodt_17,
    Round(Case When Round(Avg(Convert(float,HC18)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod18)),1) / Round(Avg(Convert(float,HC18)),1)  End,1) As Prodt_18,
    Round(Case When Round(Avg(Convert(float,HC19)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod19)),1) / Round(Avg(Convert(float,HC19)),1)  End,1) As Prodt_19,
    Round(Case When Round(Avg(Convert(float,HC20)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod20)),1) / Round(Avg(Convert(float,HC20)),1)  End,1) As Prodt_20,
    Round(Case When Round(Avg(Convert(float,HC21)),1)  = 0 Then 0 Else   Round(Avg(Convert(float,Prod21)),1) / Round(Avg(Convert(float,HC21)),1)  End,1) As Prodt_21
from
    tblDadosProd
Where
    DescProjeto = @banco

select * from @tbltemp1

the results will be like this:

I need to make a TOP 3 in prodt_8 to prodt_14 and prodt_15 to prodt_21, but i can't do this with columns. So, I need something like this:

I've created a table and tried to insert, but with no success.

This cursor is not working either:

--DECLARE @AccountID INT
--DECLARE @getAccountID CURSOR

--SET @getAccountID = CURSOR FOR

--SELECT prodt_8,prodt_9
--FROM @temp1

--OPEN @getAccountID
--FETCH NEXT
--FROM @getAccountID INTO @AccountID

--WHILE @@FETCH_STATUS = 0
--BEGIN
--PRINT @AccountID
--FETCH NEXT
--  FROM @getAccountID INTO @AccountID
--END

--CLOSE @getAccountID
--DEALLOCATE @getAccountID

And this Pivot is even worse:

select u.p814, up1521
    from #tbltemp1 as u
pivot
    ( prodt_8,prodt_9)

How can i make this?

Thank you very much.

There are lots of questions here, but it helps only the asker.

If I figured out correctly what you're doing, you could also do it using unpivot, something like this with the separate columns in 2 CTEs and join them side by side using number for row_number:

;with D1 as (
select row_number() over (order by (select null)) as RN, p8_14
from (
  select prodt_8, prodt_9, prodt_10
  from test
) p unpivot (
    p8_14  for colname1 in (prodt_8,  prodt_9,  prodt_10)
) as up),

D2 as (
select row_number() over (order by (select null)) as RN, p15_17
from (
  select prodt_15, prodt_16, prodt_17
  from test
) p unpivot (
    p15_17  for colname1 in (prodt_15, prodt_16,  prodt_17)
) as up)

select p8_14, p15_17 from D1 join D2 on D1.RN = D2.RN

That's just couple of the columns, made also SQL Fiddle: http://sqlfiddle.com/#!6/33d41/1


Why does TSQL return the wrong value for POWER(2.,64.)? select POWER(2.,64.) returns 18446744073709552000 instead of 18446744073709551616.  It seems to have only 16 digits of precision (rounding the 17th).

Even making the precision explicit select power(cast(2 as numeric(38,0)),cast(64 as numeric(38,0))) it still returns the rounded result.

This seems like a pretty basic operation for it to be flaking out arbitrarily at 16 digits of precision like this.  The highest it can calculate correctly is only POWER(2.,56.), failing for POWER(2.,57.).  What is going on here?

What's really terrible is that select 2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.*2.; actually returns the right value.  So much for terseness.

From the online documentation:

POWER ( float_expression , y )

  Arguments

  float_expression
  Is an expression of type float or of a type that can be implicitly converted to float

The implication is that whatever you pass as the first parameter is going to be implicitly cast to a float(53) before the function is executed. However, this is not (always?) the case.

If it were the case, it would explain the loss of precision:

  Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.

On the other hand, the literal 2. is type numeric…:

DECLARE @foo sql_variant;
SELECT @foo = 2.;
SELECT SQL_VARIANT_PROPERTY(@foo, 'BaseType');
GO

| (No column name) |
| :--------------- |
| numeric          |

dbfiddle here

…and the multiply operator returns the data type of the argument with the higher precedence.

It appears that on 2016 (SP1), all the precision is retained:

SELECT @@version;
GO

| (No column name)                                                                                |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
        Oct 28 2016 18:17:30
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
 |

SELECT POWER(2.,64.);
GO

| (No column name)     |
| :------------------- |
| 18446744073709551616 |

dbfiddle here

…but on 2014 (SP2), they are not:

SELECT @@version;
GO

| (No column name)                                                                                |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
        Jun 17 2016 19:14:09
        Copyright (c) Microsoft Corporation
        Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
 |

SELECT POWER(2.,64.);
GO

| (No column name)     |
| :------------------- |
| 18446744073709552000 |

dbfiddle here


Backup of truncated database large I have truncated all tables in a rather large Microsoft SQL Server 2014 database, so that I could take a backup of just the data schema, permissions, etc. (I can't just let SSMS create scripts of the database, since there are circular dependencies between different databases..).

When I'm creating a backup of the database the resulting file size is 1.3GB which seems rather excessive for an empty database. What kind of meta data (all tables are empty and report sizes of 0) could explain such a large file size and how could I investigate this?

There are some CLR assemblies in the database, but when I let SSMS generate a database creation script (which includes the assemblies base64 encoded it seems), the resulting sql file is only 12MB large so I don't think this would explain it.

As far as I know the backup really only backs up the actual data and not the unused space - otherwise most backups would be much larger than they are. I tried to shrink the files just to be on the safe side though (not like I can fragment an index of an empty table too badly) but that didn't change anything.

sp_spaceused returns database_size 1291.57MB, unallocated space 1.95 MB; reserved 1192696 KB; data 1174208 KB; index_size 15208 KB; unused 3280 KB. SQL Server really seems to think that I have lots of data somewhere, just not in my tables.

SELECT is_read_committed_snapshot_on FROM sys.databases returns 1 for this database.

I put the database in single user mode and took it offline (so that should definitely kill every transaction) and then ran sp_spaceused again. I get the same numbers as before and the backup is still the same size.

SELECT count(*) AS NumRows FROM sys.dm_tran_version_store returns 0.

I think this might be a problem related to service broker messages - please take a look at this link

https://stackoverflow.com/questions/5172686/sql-server-database-file-not-being-truncated


postgresql: how to define a JSONB column with default value I am unable to find in the documentation how to create
a JSONB column in PostgreSQL that has a DEFAULT value of
an empty json document.

How the above can be stated in the CREATE TABLE definition ?

That's the same as with any other default value:

create table amsterdam
(
   id       integer primary key,
   payload  jsonb not null default '{}'::jsonb
);


Some SQL Server geospatial queries take much, much longer than others We have a simple SQL Server table with geospatial data that looks like this:

CREATE TABLE [dbo].[Factors](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StateCode] [nvarchar](2) NOT NULL,
    [GeoLocation] [geography] NULL,
    [Factor] [decimal](18, 6) NOT NULL,
 CONSTRAINT [PK_dbo.Factors] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)

We have something like 100k+ rows in it right now, but that's expected to grow to millions.

We run queries on it that look like this:

declare @state nvarchar(2) = 'AL'
declare @point geography = geography::STGeomFromText('POINT(-86.19146040 32.38225770)', 4326)

select top 3
    Lat,
    Lon,
    Factor,
    GeoLocation.STDistance(@point) as Distance
from dbo.Factors
where StateCode = @state and GeoLocation.STDistance(@point) is not null
order by Distance

Here's the bit that's weird though. The data in that table is spotty: we've got it for the southern parts of a state, for instance, but not for the whole state. If the point we search for is within a few hundred meters of points we've got data for (for instance, from the southern part of the state), the query returns subsecond. But if it's, say, 100 kilometers from the nearest data point (for instance, if the target point is from the northern part of the state), then the query will take up to 3 minutes or so to return. In both instances, query plans indicate that they're starting with a scan of the geospatial index, so it's not the problem that sometimes happens, that SQL Server can't figure out it's supposed to use the index in question.

My assumption is that it has something to do with how the geospatial index is laid out.

CREATE SPATIAL INDEX IX_Factors_Spatial
ON [dbo].[Factors] (GeoLocation)
USING GEOGRAPHY_AUTO_GRID
    WITH (
            CELLS_PER_OBJECT = 16,
            PAD_INDEX = OFF,
            STATISTICS_NORECOMPUTE = OFF,
            SORT_IN_TEMPDB = OFF,
            DROP_EXISTING = OFF,
            ONLINE = OFF,
            ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON);

But I don't know that I grok the details well enough to put my finger on the problem.

Any suggestions for how to approach troubleshooting this?

Short answer: Compare actual execution plans for the fast and slow variants and you'll see yourself.

When the given @point is close to the points in the table, the tessellations used in the spatial index actually help to dismiss most of the rows and only few seeks of the index are necessary.

When the given @point is far from any point in the table, the engine effectively has to read all rows. It seeks an index 100K times, which is slow.

If you disable the spatial index you'll see that performance of the query becomes the same for any given @point. It will be slower than your fast variant when index is useful, but it will be faster than your slow variant when the index is harmful.

See Spatial Indexes Overview if you haven't already for the basic details about the internal structures of such index.

Sample test data

CREATE TABLE [dbo].[Factors](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [GeoLocation] [geography] NULL,
    [Factor] [decimal](18, 6) NOT NULL,
 CONSTRAINT [PK_Factors] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Generate ~100K rows within ~20km by ~20km area.

DECLARE @MinLat float = -38.180184;
DECLARE @MaxLat float = -38.000000;
DECLARE @MinLon float = 145.000000;
DECLARE @MaxLon float = 145.227707;
DECLARE @PointCount int = 317;

WITH
x AS
(
    SELECT TOP (@PointCount)
        ROW_NUMBER() OVER (ORDER BY [object_id]) AS rn
    FROM sys.all_objects
)
INSERT INTO [dbo].[Factors]
    ([GeoLocation]
    ,[Factor])
SELECT
    geography::Point(
         @MinLat + (TLat.rn-1) * (@MaxLat - @MinLat) / (@PointCount-1)
        ,@MinLon + (TLon.rn-1) * (@MaxLon - @MinLon) / (@PointCount-1)
        ,4326) AS GeoLocation
    ,0 AS Factor
FROM
    x AS TLat CROSS JOIN x AS TLon
ORDER BY TLat.rn, TLon.rn;

Create default spatial index

CREATE SPATIAL INDEX [IX_GeoLocation] ON [dbo].[Factors]
(
    [GeoLocation]
)USING  GEOGRAPHY_AUTO_GRID
WITH (
CELLS_PER_OBJECT = 16,
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Test queries

@point1 is close to other points in the table.
@point2 is far away from other points in the table.

declare @point1 geography = geography::Point(-38.000000, 145.000000, 4326);
declare @point2 geography = geography::Point(+38.000000, 145.000000, 4326);

select top 3
    Factor,
    GeoLocation.STDistance(@point1) as Distance
from dbo.Factors
where GeoLocation.STDistance(@point1) is not null
order by Distance
option(recompile);

select top 3
    Factor,
    GeoLocation.STDistance(@point2) as Distance
from dbo.Factors
where GeoLocation.STDistance(@point2) is not null
order by Distance
option(recompile);

Execution plans and IO

Index enabled

IO. Top result is fast (7ms, 171 reads). Bottom result is slow (5,693ms, 234,662 reads).

Fast.

Slow.

Index disabled

IO. Both queries have the same number of reads (601) and same duration (~1700ms).

Plan is the same for both queries:

It is faster to scan 100K rows, than seek 100K times.

I don't know how to solve the problem, if there is a way to get the best of the both worlds and somehow automatically decide whether to use the index or not.

You can try to calculate the bounding box (min/max lat/lon) and change the logic based on whether the given point is within the bounding box.

The most interesting thing happens in that built-in Geodetic Tesselation table-valued function and I don't see how to fine-tune it.

With spatial indexes a lot depends on your data distribution.

In some cases you may be better off with two separate simple standard indexes on latitude and longitude if you know that your data is dense and you can limit the search to a narrow stripe or small area (given point +- few km).


Mysql - Daily Sum by currency select DATE(added), SUM(amount) ,currency
from transactions
where  DATE(added)  > subdate(CURDATE(),10)
AND status = 'APPROVED' GROUP BY  DATE(added);

This will return one sum per day which is wrong since i  have many currencies.
i want to get daily sum's by currency.
example:

24/10/2019 EUR 15000
24/10/2019 USD 18000

23/10/2019 EUR 55000
23/10/2019 USD 68000

... adding to my recent comment ...

http://sqlfiddle.com/#!9/8e8f57/4

select DATE(added), SUM(amount) ,currency
from transactions
where  DATE(added)  > subdate(CURDATE(),10)
  AND status = 'APPROVED'
GROUP BY  currency,DATE(added)
ORDER BY DATE(added) desc;

delivers daily amounts separated by currency as you wanted


cross database ownership - The server principal is not able to access the database under the current security context I have this script that set a bunch of databases to the same db owner, and set cross database ownership on at database level

select database_name=name,
       db_owner_name=suser_sname(owner_sid),
       db_owner_sid=owner_sid,
       is_db_chaining_on,
       SQL='USE ' + QUOTENAME(name) + char(10) + 'EXEC sp_changedbowner ''MY_COMPANY\sqldbslaveowner'';'  + CHAR(10),
       CROSS_DB_OWNERSHIP_SQL='USE MASTER' + CHAR(10) + 'ALTER DATABASE ' + QUOTENAME(name) + SPACE(1) + 'SET DB_CHAINING ON;' + CHAR(10)
from sys.databases
where name in('apcore','junocore','APIA_Repl_Sub','JUNOCore_Repl_Pub','JunoFinance')

It sets the db owner to a login, that has no permissions associated with it - my_company\sqldbslaveowner. It is an AD account.
and the following:

USE MASTER ALTER DATABASE [JUNOCORE] SET DB_CHAINING ON;
USE MASTER ALTER DATABASE [JUNOFinance] SET DB_CHAINING ON;
USE MASTER ALTER DATABASE [apcore] SET DB_CHAINING ON;
USE MASTER ALTER DATABASE [JUNOCore_Repl_Pub] SET DB_CHAINING ON;
USE MASTER ALTER DATABASE [apia_repl_sub] SET DB_CHAINING ON;

so, after done that, when I run the below I am sorted:

select database_name=name,
       db_owner_name=suser_sname(owner_sid),
       db_owner_sid=owner_sid,
       is_db_chaining_on
from sys.databases
where name in('apcore','junocore','APIA_Repl_Sub','JUNOCore_Repl_Pub','JunoFinance')

This was all good, and working fine, then yesterday, for no apparent reason, it broke up, and  I can't figure it out the bit that is missing.

What I am experiencing is the following (it is all within the databases shown above):

in one of the databases junofinance I go ahead and create a stored procedure that selects data from a table in another database called JunoCore. It should all be good since we are under cross database ownership.

Note that the procedure has execute as owner and the owner is the same guy on both databases.

I grant permission to execute the procedure to one of my application logins.

use JUNOFinance
go
create proc prc2
with execute as owner
as
begin
select * from junocore.[dbo].[agency]
end
go
grant execute on prc2 to [juno]
go

Now when I execute this procedure I get the following error:

use JUNOFinance
go
execute as login='juno'

exec prc2

  The server principal "My_Company\sqldbslaveowner" is not able to
  access the database "JUNOCORE" under the current security context.

This was working fine.

I will have to check on the  creating certificates as it was all working before!

The databases are part of an availability group

Only msdb is set to trustworthy

As I said it was all working, the only thing I have done lately is to upgrade sql server 2016 to SP2, but I did not have any issues with this server in particular.

what else can be suggested that I do to fix this scenario?

The server principal "My_Company\sqldbslaveowner" is not able to
  access the database "JUNOCORE" under the current security context.

You should not need to EXECUTE AS OWNER in this scenario, and it will break as you can't cross databases when impersonating a database principal.  Impersonation and Ownership Chains are two separate mechanisms for delegation.

And the normal thing that people forget is that the end user must have database access to the target database.  Cross Database Ownership chains don't give a user access to a database, they only suppress permissions checks on objects in that database.

Here's a simple repro:

/*
revert
use master
drop database a
drop database b
drop login appuser
*/
go
create database a
create database b

ALTER DATABASE a SET DB_CHAINING ON;
ALTER DATABASE b SET DB_CHAINING ON;
go

use b
go
create procedure foo
as
begin
  select 1 a;
end

go

use a
go
create procedure foo
as
begin
  exec b.dbo.foo;
end

go

create login appuser with password='P@Jhs9d!$12'
create user appuser for login appuser
grant exec on foo to appuser

go
execute as login='appuser'
  exec foo;
--fails
--Msg 916, Level 14, State 1, Procedure foo, Line 4 [Batch Start Line 37]
--The server principal "appuser" is not able to access the database "b" under the current security context.

go
revert

go
use b
create user appuser for login appuser
deny select,insert,update,execute to appuser --not strictly necessary
go
use a
execute as login='appuser'
  exec foo;
revert
--works

go

--`execute as owner` is incompatible with cross database ownership chains
create or alter procedure foo
with execute as owner
as
begin
  exec b.dbo.foo;
end
go

use a
execute as login='appuser'
  exec foo;
revert
--fails
--Msg 916, Level 14, State 1, Procedure foo, Line 5 [Batch Start Line 69]
--The server principal "NORTHAMERICA\dbrowne" is not able to access the database "b" under the current security context.


How to store single result in variable and reuse it in a query (Oracle)? I have a query like

SELECT id FROM xyz WHERE ...;

which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a script.
So my goal was to reuse this result in a variable but I don´t come to a solution which works.

Joining this query would not be a good option in this circumstance. So is it possible to tell Oracle to store this single result in a variable and use this variable to build up new queries - explicitly to use in in the conditional clause of other queries?

If so, perhaps somebody could post a simple example - most simple perhaps would be sth. like

SELECT :VARIABLE FROM DUAL

For Toad, from this answer on SO:

  I think this will accomplish what you want.  You can declare a bind variable, insert a value into it, and then use it in future statements.

variable l_var varchar2(1);

begin
  select dummy
    into :l_var
    from dual;
end;

select *
from dual
where dummy = :l_var;


What could cause "lagging" in SSIS execute SQL task? Recently I noticed that the part of my ETL process loading the data into the staging area sometimes takes longer and sometimes shorter.

With the following query (executed in the integration services catalogs database) I was comparing two different runs and came to find out, that the recreate table statements are taking much longer (sometimes also the loading part, but I think the main problem is this). Here's the query to compare the different runs:

select
es1.execution_path,
es1.execution_duration as es1dura,
es2.execution_duration as es2dura,
(es2.execution_duration * 1.0) / es1.execution_duration * 100
from
catalog.executable_statistics es1
join catalog.executable_statistics es2 on es1.execution_path = es2.execution_path
where es1.execution_id = 239
and es2.execution_id = 10290
and es1.execution_path like '%create table%'
order by
--(es2.execution_duration * 1.0) / es1.execution_duration * 100 desc
es1.execution_path

Part of the result is this:

This corresponds to the following part of the ETL:

Please don't mind the additional green line in the screenshot, this is just messy formatting in Visual Studio. There are no more tasks running in parallel and the job is running around midnight, there's no other job running on the database at the same time.

These tasks are really simple. One of the Recreate table tasks for example looks like this:

IF EXISTS (
    SELECT * FROM sys.tables
    WHERE name = 'admin_perso_abteilung'
)
DROP TABLE admin_perso_abteilung
GO
CREATE TABLE admin_perso_abteilung (
    [id] int,
    [perso_abteilung] nvarchar(50)
)
GO

Anyway, the question is, since the query result suggests, that the whole process is stuck there for some time before executing the recreate table statements in parallel, what could cause this? What can I inspect further to narrow down the problem?

Since I'm more of a developer than an administrator I'm a bit lost here, please guide me a little. Thanks.

Chances are your drop/create statements are blocking each other on occasion.  As already mentioned, you can truncate the tables instead, however a fairly simple test would be to run the drop/create tasks sequentially, and the data loads afterwards in parallel.

If you're desperate to know the detailed reasons behind the variable times, then do as suggested and run a trace or XE session to capture locking & blocking during the runs.


Is there a good way to run a trigger for each record in a postgres table? I have a system where I can't control the design of some tables (replicated via Slony-I), and so I have a series of what we refer to as 'shadow tables', where I extract some information out of the replicated tables, and store it in the processed form that I need, while stripping out the records that I want to ignore.

Right now, after setting up a new replica, I run an update and set a value back to itself (eg, UPDATE tablename SET field=field) to force the trigger to run, but some of the tables are millions of records, and growing, and it can take 30min.  (and then there's the vaccuum, too).

Is there some better way to trigger it, or some way to write a function such that it'll work with either input passed in or NEW depending on calling context?  I'm reluctant to keep two different functions around, as I've seen too many times where one gets updated, and not the other.

It can be done, using the following template:

CREATE TABLE tablename ( ... );

/* for direct invocation */
CREATE FUNCTION propagate_data(newrow tablename) RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO other_table VALUES (newrow.a, newrow.b, ...);
END:
$$;

/* trigger function wrapper */
CREATE FUNCTION propagate_data_trg() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM propagate_data(NEW);
END;
$$;

CREATE TRIGGER propagate_data AFTER INSERT ON tablename FOR EACH ROW
    EXECUTE PROCEDURE propagate_data_trg();


Why do cross-database statements break replication when using filters? For example, say you you have table A and table B on the master and on the slave you've set up replicate-do-db = A.

Now if I do something like this:

USE B;
DROP TABLE A.sometable;

Why should this not work?  I know why it doesn't work in the sense that I've used a database then operated on another one but why has MySQL been implemented this way?
Was it a conscious decision or is there some grander reason why this shouldn't/can't be made to work?

I don't know the rationale, but it is a problem that has bitten many users.

It is not just DDL statements, but also DML:

replicate_do_db = A  or  binlog_do_db = A  or ... (several syntaxes possible)
USE B;
INSERT INTO A ...;

Won't perform the INSERT.


Real time or close to real time with MySQL or MSSQL I work at a startup that is working with different databases among which are MySQL (v4/5) and MSSQL (2014 and older). Different clients we cater to run different dbs. Currently we have a Java program that connects to the db and then pushes data to the API - we tap into the db. We were looking to upgrade the system to have real time updates to the API and then got into a bit of standstill with designing real time updates with a MySQL or MSSQL databases.

One of the developers is pushing for having triggers in either db that run an external command (our java program perhaps) that will initiate a push to the API. Somewhat similar to some NoSQL dbs like Rethink. Another dev is against triggers and is proposing more frequent polling of the db, which will not provide real time, but will get pretty close to real time.

I searched for a while for SQL real time solution, but was not able to find any solid reads with use cases and examples that support either approach. So my questions are:

Is frequent polling of the db engine like that of MySQL or MSSQL an ok thing to do? I am talking about 10 or 30 second polling. The db normally gets traffic from 3-4 clients at most and has roughly 20-40k records in table we are interested in. So we'd be adding another client that's polling aggressively.
Has anyone ran into actual negative use cases with triggers calling external commands? We won't be using them to guarantee integrity and consistency is also not that big of a concern in our use case (it is, but there is some wiggle room).

I am not a DBA or an experienced dev yet. Quite fresh from CS university with some experience. And all of us are like that so I am concerned that this lack of knowledge might backfire later with clients.

My opinion is that polling in your case (10 seconds is OK to me) is better than the trigger solution. The trigger is closely coupled with your transaction, and can cause lots of un-expected issues that are hard to trouble-shoot. Personally, I have never used trigger to call external application (what if the application fails, long to start, stuck? )

A better (but more difficult solution) can be using service broker to engage with external application.


Select multiple columns in subquery I am having trouble selecting all columns in a sub query and can't seem to get anywhere with joining for replacement.

My Table Data is:

CREATE TABLE `w7nwd_com_mtapp_product_engine` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `make` varchar(45) DEFAULT NULL,
  `model` varchar(45) DEFAULT NULL,
  `engine_size` varchar(15) DEFAULT NULL,
  `engine_type` enum('P','D') DEFAULT NULL,
  `engine_code` varchar(155) DEFAULT NULL,
  `year` varchar(15) DEFAULT NULL COMMENT 'This isn''t a standard year, this could be 09-12.',
  `bhp` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_w7nwd_com_mtapp_engine_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_engine_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product_partnumber` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) DEFAULT NULL,
  `partnumber` varchar(245) DEFAULT NULL,
  `override_price_new` int(11) DEFAULT NULL,
  `override_price_ex` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `partnumber_UNIQUE` (`partnumber`),
  KEY `fk_w7nwd_com_mtapp_product_alias_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_product_alias_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

CREATE TABLE `w7nwd_com_mtapp_product_oem` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `oem` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_w7nwd_com_mtapp_product_oem_w7nwd_com_mtapp_product1_idx` (`product_id`),
  CONSTRAINT `fk_w7nwd_com_mtapp_product_oem_w7nwd_com_mtapp_product1` FOREIGN KEY (`product_id`) REFERENCES `w7nwd_com_mtapp_product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `w7nwd_com_mtapp_product` VALUES (1);

INSERT INTO `w7nwd_com_mtapp_product_partnumber` VALUES ('1', '1', '708638', NULL, NULL),('7', '1', '708639', NULL, NULL);

INSERT INTO `w7nwd_com_mtapp_product_oem` VALUES ('1', '1', '345474574545'),('2', '1', '453453454543');

INSERT INTO `w7nwd_com_mtapp_product_engine` VALUES ('1', '1', 'AUDI', 'A5', '2.2', 'D', NULL, '2012', '1400'),
('2', '1', 'FORD', 'C5', '2.2', 'D', NULL, '2012', '1400');

And I tried:

SELECT e.*, p.*,(
SELECT GROUP_CONCAT(`partnumber`)
FROM `w7nwd_com_mtapp_product_partnumber` AS `n`
WHERE `p`.`id`=`n`.`product_id`) as `partnumbers`,(
SELECT GROUP_CONCAT(`oem`)
FROM `w7nwd_com_mtapp_product_oem` AS `n`
WHERE `p`.`id`=`n`.`product_id`) as `oems`
FROM `w7nwd_com_mtapp_product` AS `p`
LEFT JOIN `w7nwd_com_mtapp_product_engine` as `e`
    ON `e`.`product_id` = `p`.`id`;

Here is a sql fiddle with details -- http://sqlfiddle.com/#!2/2481e/1

The link is to the schema with my current results, the issues is I only want one engine to come back and not duplicate all the rows.

It seems like you want the following:

SELECT p.*,
  `e`.*,
  GROUP_CONCAT(`n`.`partnumber`) as `partnumbers`,
  GROUP_CONCAT(`o`.`oem`) as `oems`
FROM `w7nwd_com_mtapp_product` AS `p`
LEFT JOIN `w7nwd_com_mtapp_product_engine` as `e`
  ON `e`.`product_id` = `p`.`id`
LEFT JOIN `w7nwd_com_mtapp_product_partnumber` AS `n`
  ON `p`.`id`=`n`.`product_id`
LEFT JOIN `w7nwd_com_mtapp_product_oem` AS `o`
  ON `p`.`id`=`o`.`product_id`
GROUP BY `p`.`id`

This replaces the subqueries with a LEFT JOIN on each table. Then to get the GROUP_CONCAT() to work, I added a GROUP BY p.id


Connect to MS SQL Server I'm busy setting up a MS SQL Server instance on a Windows Server

The server is Windows Server 2012 R2 and the version of MS SQL Server is 2014 Essentials.

I've installed MS SQL and created my database, I've also installed HeidiSQL to test making connections to the server.

All this software is running off of the same machine.

When I run the MS SQL Management studio it connects to

Server name    : SPKWINVMSERVER\SQLSERV
Authentication : Windows Authentication

So I've tried to create a login using the management studio

right click Security > New  > Login

and I've created the login as

Login name : mssqladmin
password   : #FongKong123

I gave this user all the server roles for testing and it seemed to create ok.

However when I try to connect to it using this Login I've just created using HeidiSQL it fails

The connection details I'm using in HeidiSQL to connect are

Network type : Microsoft SQL Server (TCP/IP)
Hostname     : SPKWINVMSERVER\SQLSERV
User         : mssqladmin
Password     : #FongKong123
Port         : 1433

The above connection seems to work when connecting to the SQL Server on the server itself.

If trying to connect with another computer on the network (also using HeidiSQL) I imagine I won't be able to use SPKWINVMSERVER\SQLSERV as the hostname but it doesn't work on IP address which is in this case 192.168.20.67

Network type : Microsoft SQL Server (TCP/IP)
Hostname     : 192.168.20.67\SQLSERV
User         : mssqladmin
Password     : #FongKong123
Port         : 1433

What did I miss?

Go to Sql Server Configuration Manager, and check on your client protocols if TCP/IP is enabled.


Change Data Capture for Asynchronous Secondary Replica MSDN states the following in Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups) for SQL Server 2014:

  Limitations and Restrictions:

  Change tracking and change data capture are not supported on secondary
  databases that belong to a readable secondary replica:

  Change tracking is explicitly disabled on secondary databases.
  Change data capture can be enabled on a secondary database, but this is not supported.

This confuses me: You can not track the changes. However you can enable CDC?

The scenario I am trying to achieve is to use SSIS CDC components on an asynchronous secondary replica. Is this possible? If not what would be other viable approaches?

Change Tracking and Change Data Capture are, while having similar goals, actually separate features.  For AlwaysOn Availability groups, the limitations indicate that you can't use the synchronous Change Tracking feature at all, though the asynchronous Change Data Capture is possible to enable: It's just not a feature Microsoft will support for Active Secondaries.

Generally, things like that aren't supported because there exists a supported way to do so.  In this case, enabling CDC on the primary node of an AlwaysOn Availability Group is completely supported, allowing the captured changes to be replicated along to the Active Secondaries, so would likely be the path you should explore for your SSIS needs on the secondaries.

You can read more about the differences between Change Tracking and Change Data Capture here, and more about setting up CDC on the primary replica here.


SQL Server 2016 Database Backup and Restore Is there any different approach to restore a SQL Database backup having memory optimized tables as we know that there's additional filegroup present for holding In-Memory data.
The restore command showing error for not able to create xtp folder for memory optimized database while performing restore operation.

Note: to get the best help on this question, please include your actual RESTORE statement, and the specific error message that you're getting.

When using In-Memory OLTP, SQL Server has to create a new folder named "xtp" in the root of the default file location for the SQL Server instance.  This folder contains the DLLs for compiled stored procedures and other in-memory objects.  You can find more details about that here:

In-Memory OLTP files –what are they and how can I relocate them?

If you've changed the location for your data files, you may need to update SQL Server's access to the file system there:

Configure File System Permissions for Database Engine Access

As a test / workaround, you could manually create the "xtp" folder, and then try the restore again.


32-bit or 64-bit for development and deployment? I'm creating a Windows desktop application and want to include a database. I've chosen SQL Server Compact 4 but when I download it there are two versions (32-bit and 64-bit). My development machine is 64-bit, so do I download this version? Or, do I download the version that I expect users to run (which, I guess, is most likely to be 32-bit). If I download and work with the 64-bit, when I release the database, will it cause errors on 32-bit machines?
Thank you.

If you distribute your application with scripts to build the database (not a database backup or something like that), then in theory it should make no difference which version (of 32-bit and 64-bit) you develop against. Back when 64-bit was new and shiny (which was SQL Server 2000), there were some advanced features that 64-bit didn't support, but in 2005/2008 they should be functionally identical.

(Not that it should matter if you're using Compact Edition, but there are some configuration differences between 32-bit and 64-bit once you get to the point where your DB performance is an issue.)


Postgresql ERROR: duplicate key value violates unique constraint when trigger insert I've created a function which is called when a trigger is fired while insert. But every time I try to insert the data in the table I get the duplicate key value violates unique constraint

This is my trigger function:

CREATE OR REPLACE FUNCTION trg_new_rows()
  RETURNS trigger AS
$BODY$
BEGIN
IF EXISTS (SELECT 1 FROM organization WHERE organization.name = 'TV' AND organization.country = 'D') THEN
    --RAISE '1';
        INSERT INTO organization VALUES ('TV1','TV1',NULL,'TV1',NULL,NULL);
ELSE
        --RAISE '2';
        INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL);
        INSERT INTO organization VALUES ('Tivoli2','Tivoli2 organization',NULL,'GB',NULL,NULL);
END IF;
RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

And this is the trigger

CREATE TRIGGER delaft
  AFTER INSERT
  ON organization
  FOR EACH ROW
  EXECUTE PROCEDURE trg_new_rows();

And then when I try executing this:

INSERT INTO organization VALUES ('TV','Tivoli D organization',NULL,'D',NULL,NULL);

I get this error:

ERROR:  duplicate key value violates unique constraint "organization_pkey"
DETAIL:  Key (abbreviation)=(Tivoli1) already exists.
CONTEXT:  SQL statement "INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)"
PL/pgSQL function trg_new_rows() line 8 at SQL statement
SQL statement "INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)"
PL/pgSQL function trg_new_rows() line 8 at SQL statement
, Time: 0.000000s

Although when I check the organization table I can't find any abbreviation having the value Tivoli1. Any advice please?

This is the create and insert queries for table organization:

CREATE TABLE Organization
(Abbreviation VARCHAR(12) PRIMARY KEY,
 Name VARCHAR(80) NOT NULL,
 City VARCHAR(35) ,
 Country VARCHAR(4) ,
 Province VARCHAR(35) ,
 Established DATE,
 CONSTRAINT OrgNameUnique UNIQUE (Name));

 INSERT INTO organization VALUES ('EFTA','European Free Trade Association','Geneva','CH','Geneve','1960-01-04');
 INSERT INTO organization VALUES ('EIB','European Investment Bank','Luxembourg','L','Luxembourg','1957-03-25');
 INSERT INTO organization VALUES ('CERN','European Organization for Nuclear Research','Geneva','CH','Geneve','1953-07-01');
 INSERT INTO organization VALUES ('ESA','European Space Agency','Paris','F','Ile de France','1973-07-31');
 INSERT INTO organization VALUES ('EU','European Union','Brussels','B','Brabant','1992-02-07');
 INSERT INTO organization VALUES ('FAO','Food and Agriculture Organization','Rome','I','Lazio','1945-10-16');

I'm using navicat for postgresql.

You're calling this

INSERT INTO organization VALUES ('Tivoli11','Tivoli organization',NULL,'F',NULL,NULL)

Which is triggering this.

INSERT INTO organization VALUES ('Tivoli1','Tivoli1 organization',NULL,'F',NULL,NULL)

Your primary key, organization_pkey, is on abbreviation. Here you have two Tivoli1 abbreviations. That triggers the exceptions which causes the trigger to fail, and the commit to abandon.

I have no idea what you want to do, but perhaps you need a consultant to audit your schema and workflow. It seems like you may be in over your head.

Re: "there is no Tivoli11"

Right,

You tried to insert it
That triggered a trigger trg_backup_row which you did not show in the question (please do)
trg_backup_row attempted another insert.
That attempted insert violated the unique constraint of the primary key.
That violation threw an exception.
That exception caused the transaction and the initial commit to rollback.

Transactions, even AFTER INSERT that throw exceptions cause the insert to rollback.


InnoDB: will uncommitted transaction produce redo log? Before MySQL commit a transaction, it will write REDO log first, then commit the transaction, that is write ahead log.

start transaction;

update users set uuid = UUID() from user where id = 1
update users set uuid = UUID() from user where id = 2
update users set uuid = UUID() from user where id = 3
...
...
update users set uuid = UUID() from user where id = 1,000,000
// not yet commit

If a transaction is going to update 1 million records, which takes 100 seconds. During the period of execution, will this uncommitted transaction produce redo log?

innodb produce redo log during transaction and may sync to disk even if the transaction has not committed.