Transactional replication pre and post scripts Just a preface that I'm working at the moment as an accidental DBA so sorry for any ignorance.

I need to completely rebuild one of our transactional replications.  The indexing strategy is different on the subscriber database than it is on the publisher, the primary key is the same but the clustered index on NC indexes are different. I am aware that the tables by default are completely dropped on the subscriber.  I believe I can use scripts pre and post snapshot application to drop and create indexes.

Both the clustered indexes on the subscriber and publisher are UniqueIdentifier but different columns.  The table has nearly 1 billion rows and is sadly pretty wide.  Just thought that I'd provide that information in case it makes a difference.

My questions are:

Is using the post execution scripts the best way to go about this?
If the clustered index is different on the subscriber than the publisher am I best to allow the bulk insert to happen with the publisher clustered index or would it be better to set the alternate clustered index before the bulk insert?

Thanks in advance for any help.

At my last job this is exactly what we did for a very similar scenario (also on SQL Server 2008 R2 - Standard Edition), and I was the one to build it out. We had an off the shelf vendor application we heavily used and needed to report out of. It was indexed differently than how we were reporting out of it.

We had one way transactional replication setup to sync from the vendor app to our report server. I setup pre and post scripts to drop and recreate the appropriate indexes (and some other relevant entities like indexed views), which of course checked for object existence first too (you can check this via the sys schema's tables and views) before executing.

The scripts themselves just pointed to a stored procedure where all the logic lived, on the subscriber server. This just felt more natural for maintaining the code than updating an external script file. The pre script to drop the custom indexes before the schema was synced was important because there was a bug we ran into where transactional replication would occasionally break and stop syncing otherwise. (If you need a code example of my pre and post scripts, let me know and I'll update this answer.)

In regards to your second question, in general it's faster to bulk insert your records into a table and then add your indexes after (this gives SQL server the full picture to arrange the data in the most efficient manner, at one time). To do this with transactional replication, you'll need to de-select "copy clustered indexes" (and non-clustered indexes for that matter) in the Publisher's Properties window under Article Properties for that table. (Letting it sync the data with the Publisher's clustered index is essentially just doubling the work your server will need to do when you re-index it on the Subscriber with your preferred clustered index.)


You can use a CTE to create a dataset for SchoolBranch that includes the contactId value for the parent record then simply INNER JOIN to the main dataset to get the data you're after.

WITH CTE AS (
  SELECT id, contactId, parentBranchId, NULL AS parentContactId
  FROM SchoolBranch
  WHERE parentBranchId IS NULL
  UNION ALL
  SELECT sb.id, sb.contactId, sb.parentBranchId, p.contactId AS parentContactId
  FROM SchoolBranch sb
  INNER JOIN CTE p ON p.id = sb.parentBranchId
)

SELECT sb.id AS branchId
  , c.id AS contactId
  , sb.parentBranchId
  , c.NonImportantColumn
FROM Teachers t
INNER JOIN Department d ON d.id = t.deptId
INNER JOIN Contact c ON c.id = d.contactId
INNER JOIN CTE sb ON (sb.contactId = c.id) OR (sb.parentContactId = c.id)
WHERE t.id = 2

You can see a working example on db<>fiddle.


Explaining the problem with a column of values from a non-mutually exclusive domain I'm trying to convince the powers-that-be that their current database design is risky and inefficient. I'd like to verify whether my assumptions are correct, and I'd like help in finding technical literature and/or terminology that explains the problem in a way that sounds more convincing than my "It's bad, mmmkay."

It's parcels data. The current structure is as seen here:

PARCEL_ID   PARCEL_TYPE     RIGHTS_SURFACE      RIGHTS_MINERAL
46          EASEMENT            0                   0
47          DISPOSED            1                   0
47          OWNED               0                   1

I see two, related issues with the PARCEL_TYPE column:

1) A single column identifies ownership. However, we may own mineral rights but not surface rights. In which case, the current workflow is (as above) to create a second entry for that parcel.  The mineral rights and surface rights columns do not actually indicate ownership; they only indicate whether the value found under PARCEL_TYPE applies to them.

2) The column refers to non-mutually exclusive characteristics: ownership (current, past, never--as it applies to the surface vs minerals) and access (whether we have an easement, lease, or permit). That is, we could have a parcel that we formerly owned that we now have a permit to access; with the current data structure, we could only indicate that by creating a copy of the parcel.

To address both of these issues at once, my recommendation would be to restructure the table like so:

PARCEL_ID   RIGHTS_SURFACE      RIGHTS_MINERAL      ACCESS
46          NOT OWNED           NOT OWNED           EASEMENT
47          DISPOSED            OWNED               OWNED

This would prevent duplicate entries from referring to the same parcel, and--as far as I can tell--it would keep the possible values in fields mutually exclusive.

Is this a better (or the best) way to structure the data? And, if so, to what database management standards is this change conforming?

EDIT:
More research has brought me suspect that this results from not normalizing to the 1st, 3rd, and Boyce-Codd normal forms (and that there are some domain/key normal form issues with the table as well). Maybe throwing around that terminology will be convincing enough.

The best argument against the existing design is that there's really no good primary key on the table (except perhaps an identity column, which I don't consider a good primary key). That makes it hard to join to other tables.

An advantage with your design is that a query can easily identify parcels where you (for instance) have mineral rights but not surface rights using a simple WHERE clause. With the existing design, this is still possible, but it's a lot messier (and not really feasible using graphical tools or entry-level SQL). This is particularly important if the table in question is a dimension table in a datawarehouse.


mysql database update possible timing issue I have a strange situation.
I have a cronjob running queue tasks one after the other. I have a delay of 1s between the tasks.

After a task is run, it sets processed=1, and continues with the next one, that has processed=0.

I have some situations where a task is executed twice. This means that processed=1 was set, but when we read again for processed=0, it finds it.

I use PHP, and ->execute(). Is it possible changes in the database not to be reflected immediately for some reasons?

The issue was that since the queue run for 1 minute, there were cases that one cronjob was not finished yet, and another was starting. So they were processing the same tasks from the database.

The solution is to run cronjob using flock(), thus making sure that only 1 is run:

* * * * * /usr/bin/flock -n /tmp/fcj.lockfile /usr/local/bin/frequent_cron_job


SQL trigger that outputs incremental numbers from zero whenever the month changes I have the following trigger that outputs a number in the format such as 170200005 where 17 is the year and 02 is the month (February) and changes as months changes e.g March 03 then the last digits are incremental.

BEGIN

  SET @var1 = LPAD((SELECT MONTH(CURRENT_DATE())), 2, '0');
  SET @var2 = (SELECT YEAR(CURRENT_DATE));
  SET @var2 = (SELECT MID(@var2, 3, 2));
  SET @var1 = CONCAT(@var2, @var1);

  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT(@var1, LPAD(LAST_INSERT_ID(), 5, '0'));

END

I would like to have a code that will output in the same format as highlighted before but then starts from 00001 whenever the month changes.

One way to do this would be to just maintain a table with the sequence you want to use, something like this:

Create the table:

mysql> CREATE TABLE seq1 (seqnum int, primary key(seqnum));

Insert one time to have a row to update later:

mysql> INSERT INTO seq1 VALUES (0);

Create a stored procedure to update the sequence number:

DELIMITER $
DROP PROCEDURE IF EXISTS setseq1;
CREATE PROCEDURE `setseq1`(OUT newseq INT)
-- Get a new sequence number that is the greater of the two-digit month number
-- plus a 5-digit sequence number, or the current sequence number + 1
BEGIN
DECLARE myseq INT;
DECLARE monthseq INT;
START TRANSACTION;
   SET myseq=(SELECT seqnum FROM seq1 FOR UPDATE);
   SET monthseq=(SELECT DATE_FORMAT(NOW(), '%y%m') * 100000 + 1);
   SET newseq = IF(myseq < monthseq, monthseq, myseq + 1);
   UPDATE seq1 SET seqnum= newseq;
COMMIT;
END
$

Whenever you need a new sequence number, call the procedure, something like this:

mysql> CALL setseq1(@mynewnumber);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @mynewnumber;
+--------------+
| @mynewnumber |
+--------------+
|    170300016 |
+--------------+
1 row in set (0.00 sec)

mysql> CALL setseq1(@mynewnumber);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @mynewnumber;
+--------------+
| @mynewnumber |
+--------------+
|    170300017 |
+--------------+
1 row in set (0.00 sec)

The first time the procedure is called in any given month, it will set the sequence number to %y%m00001.  Thereafter it will be incremented by 1 each time the procedure is called until the month changes again.

Be sure not to mess with the sequence number table other than to insert one row initially and to call the procedure.


Do agile software development approaches apply to SQL? I would like to learn if agile software development methods/principles/patterns are valid to SQL programming as well. If yes, where would be a good place to start learning about that? Are there any articles or books targeting agile development in SQL context?

Yes. There is a whole web site devoted to it: Agile Data

I'm using it now for a brand new project.

Also see: Should you design the database before the application code is written?.

My answer there doesn't capture what I do now: 2 week sprints, good enough for that sprint. As per the Agile Data site.

I find it a good practice. It focuses on the here and now, not "we might need this". So you manage a well scoped delivery. Note: I still model using a whiteboard and meatsacks first, and it's in 5NF.


Is InnoDB Engine up to speed against Memory Engine? I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows:

SELECT P.col1, P.col2, P.col3, P.col4, P.col5, P.col6, P.col7, P.col8, P.col9
 , P.col10, P.col10 * R.col3 as 'combi'
FROM PRODUCT P INNER JOIN RATE R ON R.col2 = P.col2
WHERE P.col3 = 'y'
 AND P.col4 >= 1000
 AND P.col5 >= 5
 AND P.col6 BETWEEN 10 AND 100
 AND P.col7 >= 0
 AND P.col8 >= 7
 AND P.col9 >= NOW()
 AND P.col10 * R.col3 BETWEEN 50 AND 80
ORDER BY P.col8 DESC LIMIT 100;

Based on some discussion on Stackoverflow, I learnt that I may be able to load the database into the RAM by setting InnoDB parameter innodb_buffer_pool_size larger than the size of the dataset. Following the adjustment of this parameter, I am disappointed that the speed of the query wasn't even faster than MyISAM on most occasions (average of 3s versus 0.3s) and 100 times slower than Memory.

Upon further examination of the MySQL manual, it stated the following two points:

InnoDB maintains a storage area called the buffer pool for caching
data and indexes in memory.
The larger the buffer pool, the more InnoDB acts like an in-memory
database, reading data from disk once and then accessing the data
from memory during subsequent reads.

It seems to me that innodb_buffer_pool_size is more for caching the results of repeated queries than to provide a hot copy of the database from which to run queries. Is my understanding correct or am I missing out something that would allow InnoDB Engine to match Memory Engine, performance-wise for non-repeating range queries such as the one above?

UPDATE:

Frederick Cheung pointed out correctly that InnoDB buffer pool does cache a hot copy of the database. What I missed out was a loading procedure found in the link given by Rolando:

Scan the entire contents (all data and index pages) of the table on each startup to preload the content into memory with SELECT * FROM <table> ORDER BY <pkey fields> for each table followed by SELECT <indexed fields> FROM <table> ORDER BY <index fields> for each index.

Upon further testing, I am able to bring down the query times (0.3 to 1.0s) to a level comparable to that of MyISAM Engine. But this is still about seven times slower than that obtained with Memory Engine.

Since both engines are querying the database from RAM, can anyone tell me why InnoDB Engine is still not up to speed against Memory Engine?

It is ironic I answered a question earlier about InnoDB vs MEMORY storage engines.

There is something very weird about the MEMORY Storage Engine you must consider.

MEMORY tables perform full table locks each time there is an INSERT, UPDATE, and DELETE.

MEMORY tables still trigger a little disk I/O because the .frm file of the MEMORY table is a disk file that must be referenced with each query as far the table's existence goes and query parsing thereafter.

The default index type for a MEMORY table is the HASH index not BTREE. If you forget to declare USING BTREE, all range searches become table scans. HASH indexes are poor candidates for indexes to fulfill range queries. The query you have in the question body would quickly be victimized by this.

Even if you create indexing in the MEMORY table with the USING BTREE clause, BTREE indexes in RAM grow at a pace of O(log n) so expect disk I/O again for checking the index definition in the .frm file plus O(log n) running time on page access.

Another crazy thing to think about when using the MEMORY storage engine is this : If you try to join a MEMORY table and an InnoDB table, the resulting lock behavior defaults to the worst one, which in this case is full table locking.

CAVEAT

Others have answered question like this back in March 2011

David Splllett's answer to What are reasons *not* to use the MEMORY storage engine in MySQL?
Morgan Tocker's answer to What are reasons *not* to use the MEMORY storage engine in MySQL?

Here is one on why an all memory Database is good or bad : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?


My Ola Hallengren nightly backup job failed but It works fine when I execute the job manually by right clicking I have been using Ola Hallengreen maintenance plan . It has been working great for longtime  . Recently one of my nightly backup job failed repeatedly with an error:

  Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Unable to complete login process due to delay in opening server connection

I then execute the job manually by right clicking on it and start at step... and it runs fine. No problem.

The server is SQL Server 2008R2.

Does any one know why the job failed?

You can try to address the problem by increasing the timeout periods.

Increase the standard settings on your server for remote login timeout (s) and remote query timeout (s).

Or in your connection string you could include "Connect Timeout=120;General Timeout=120;" to increase the time allowed for the connections.


Trouble With Database Permissions for sp_send_mail I am trying to send database mail but I am getting EXECUTE permission denied on the object 'sp_send_dbmail' database 'msdb', schema 'dbo'.. I code I am running is as follows:

SELECT SUSER_NAME(), USER_NAME();
Create USER kyle_temp FOR LOGIN Foo
EXECUTE AS USER = 'kyle_temp';
SELECT SUSER_NAME(), USER_NAME();
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail Profile',
            @recipients = 'test@test.com',
            @subject = 'Test',
              @body = 'Test'
REVERT;
DROP USER kyle_temp

The Foo Login shows that it is mapped to the Foo User in msdb. When I look at the foo user in msdb I see that it has "DatabaseMailUserRole" Checked and has Execute on dbo sp_send_dbmail.

What am I missing?

You are running into the dreaded 'sandboxed' mode of the EXECUTE AS context, as described in Extending Database Impersonation by Using EXECUTE AS. In short code running under EXECUTE AS USER ... is trusted only inside the database context, not at the instance context.

There are three ways out:

the easy way: mark the current database as TRUSTWORTHY ALTER DATABASE [...] SET TRUSTWORTHY ON;
the correct way out: use code signing
the cheat: use EXECUTE AS LOGIN

If in your environment the dbo of the current database is trusted then you can go with TRUSTWORTHY. It will work, but if this property is set then any db_owner in the current DB can elevate himself to server admin.

If you want a 'correct' solution then:

move this code in an stored proc
sign the stored proc with a certificate
drop the private key (so that it can never be used again to sign anything)
export the public key, import it in [msdb]
create user in [msdb] derived from this certificate
grant necessary permissions (AUTHENTICATE, EXECUTE on sp_send_mail) to the certificate derived user

Trivial, heh? BTW, every time you modify the signed stored proc the signature is lost and the procedure has to be repeated. See Call a procedure in another database from an activated procedure for an example.

I totally don't recommend using EXECUTE AS LOGIN instead.


SET DATESTYLE TO 'European'; in MySQL I'm doing MySQL exercises and I found some of PostgreSQL, and there is a part where before entering the data to the newly created tables, enter that command, SET DATESTYLE TO 'European'; I tried to put it in MySQL but it does not recognize it, what would be its equivalent?

mysql store all the dates in the same style. Internally this is three bytes that are expanded to the YYYY-MM-DD. You can convert this format to the any desired form by DATE_FORMAT() function like that:

SELECT DATE(now()) AS sysformat
     , DATE_FORMAT( NOW(), '%W %M %D %Y, %j\'th day of the year') AS myformat


What is the standard way to make a invoice system database tables? I'm confused how to create tables for invoicing system, because when one invoice have more products, I can't repeat same invoice id for the each product in the invoice table as PK cannot repeat. so what is the concept followed to create tables in such scenario?

The route I have taken is to use a table for invoices, which cover the transaction data (customer, salesperson, date) and then a table for invoice items (itemID, quantity) which would have a foreign key to the invoice table. This would let you have as many items being attached to an invoice, and each invoice would still only have one record.


Oracle 10 xe; backing up oradata folder enough to recreate a duplicate later? Let us say, I backup of the the oradata folder.

Is that enough to recreate the exact same database?

E.g., I reinstall oracle xe 10 on the same pc, using the same installer, into the same folder, and the replace its oradata folder with one i have?

And also is it the exact duplicate in terms of the database?

List of files that you should backup:

select member from v$logfile;
select name from v$datafile;
select name from v$controlfile;
select value from v$parameter where name ='spfile';

If the last query returns nothing, backup %ORACLE_HOME%/database/initXE.ora (Windows) or $ORACLE_HOME/dbs/initXE.ora (Linux, Unix).

To make it a simple as possible, you can simply copy these files after the database was shut down.

An online backup requires ARCHIVELOG mode configured and using user-managed backup from SQL*Plus combined with OS commands, or RMAN.


Check for null columns in a table I have a table which has more than 200 columns, I want to check if any of the column is having only null/ blank values in the entire table. I tried to do it using

SELECT 'SELECT ' || STRING_AGG(   'max("'
                               || column_name
                               || '") "'
                               || column_name
                               || '"',
                               ',')
                 || ' FROM <table name>;'
  FROM information_schema.columns
 WHERE table_name='<table name>';

From here, it will give me max values of a column, and I understand that columns with values null is what I am looking for, but question is how do I display that. I am using PostgreSQL DB.

From the wording of your question, I guess something like:

SELECT substr(agg_stmt,1,LENGTH(agg_stmt)-10) FROM (
    SELECT STRING_AGG(stmt, '') as agg_stmt FROM (
        SELECT 'SELECT ' || '''' || column_name
            || ''' as col, COUNT(NULLIF(' || column_name || ','''')) as cnt FROM '
            || table_name || '' || ' union all ' as stmt
        FROM information_schema.columns
        WHERE table_name = 't'
    ) AS TT
) AS TTT;

should do. For a table T (x, y) it will generate a stmt like:

SELECT 'x' as col, COUNT(NULLIF(x,'')) as cnt FROM t union all SELECT 'y' as col, COUNT(NULLIF(y,'')) as cnt FROM t

You can add an outer stmt like:

SELECT col FROM (
    SELECT 'x' as col, COUNT(NULLIF(x,'')) as cnt FROM t
    union all
    SELECT 'y' as col, COUNT(NULLIF(y,'')) as cnt FROM t
) AS Q
WHERE cnt = 0

to get all columns that have only null or ''


SQL Server service broker related rights issue CREATE PROCEDURE [dbo].[stproc_ssb_send]
  @iname varchar(20)
WITH EXECUTE AS 'xyz'
AS
BEGIN

DECLARE @DIALOG_HANDLE UNIQUEIDENTIFIER
DECLARE @MSG varchar(max)

SET @MSG='<MESSAGE>
        <test>' + @iname + '</test>
        </MESSAGE>'

    BEGIN DIALOG CONVERSATION @DIALOG_HANDLE
    FROM SERVICE START_PROC_PROCESS_SERVICE
    TO SERVICE 'EXECUTE_PROC_PROCESS_SERVICE'
    ON CONTRACT [PROC_PROCESS_CONTRACT]
    WITH ENCRYPTION= OFF;

    SEND ON CONVERSATION @DIALOG_HANDLE
    MESSAGE TYPE PROC_PROCESS_MT(@MSG);

    END CONVERSATION @DIALOG_HANDLE
END

error:

  Msg 15517, Level 16, State 1, Procedure [stproc_ssb_send], Line 0
  Cannot execute as the database principal because the principal "xyz" does not exist, this type of principal cannot be impersonated, or you do not have permission.

XYZ is db_owner / db_ddladmin for this database.

Procedure is created using sa; error occurs while executing.

ALTER DATABASE  SET trustworthy ON

This query solved my prob. Thanks for efforts.


clean flash recovery area archive logs I have an Oracle 11g on RHEL server. My /oradata volume reach 95% disk usage :

[ora11g@bocc7-1-qrec-01 ~]$ df -h
/dev/sdb1             689G  571G   84G  95% /oradata

The Flash Recovery Area (FRA) is the larger folder :

[ora11g@bocc7-1-qrec-01 ~]$ du -skh /oradata/* | sort -h
50G     /oradata/qtest
175G    /oradata/qrec
346G    /oradata/flash_recovery_area

Inside the FRA I can see that the archive logs take approx 230Gb:

[ora11g@bocc7-1-qrec-01 ~]$ du -skh /oradata/flash_recovery_area/qrec/*
232G    /oradata/flash_recovery_area/qrec/archivelog
7.9G    /oradata/flash_recovery_area/qrec/onlinelog

I checked the v$recovery_file_dest table and my SPACE_LIMIT is 20Gb and my SPACE_USED is only 10Mb!

Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

qrec> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata/flash_recovery_area
 2.1475E+10   10984448                 0               1

So, I deduced that it had nothing to do with this table.

Now I'm checking with RMAN :

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1056_965658004.arc RECID=1057 STAMP=969146207
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1057_965658004.arc RECID=1058 STAMP=969149808
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1058_965658004.arc RECID=1059 STAMP=969153409
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1059_965658004.arc RECID=1060 STAMP=969157010
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1060_965658004.arc RECID=1061 STAMP=969160610
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1061_965658004.arc RECID=1062 STAMP=969164210
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1062_965658004.arc RECID=1063 STAMP=969167813
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1063_965658004.arc RECID=1064 STAMP=969171413
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1064_965658004.arc RECID=1065 STAMP=969175014
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1065_965658004.arc RECID=1066 STAMP=969178614
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1066_965658004.arc RECID=1067 STAMP=969182214
validation succeeded for archived log
archived log file name=/oradata/flash_recovery_area/qrec/archivelog/log_1_1067_965658004.arc RECID=1068 STAMP=969185814
Crosschecked 12 objects

RMAN> delete noprompt expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=259 device type=DISK
specification does not match any archived log in the repository

So the 1st question is: Why does RMAN not see my 2752 ARC files here :

[ora11g@bocc7-1-qrec-01 ~]$ ls -1 /oradata/flash_recovery_area/qrec/archivelog | wc -l
2752

And the 2nd question is: What should I do to free some disk space by removing old archivelog files? (the older ones are more than 1 year old!!)  :

[ora11g@bocc7-1-qrec-01 archivelog]$ find -type f -printf '%T+ %p\n' | sort | head -n 1
2017-02-22+16:54:41.0000000000 ./log_2_1_936636608.arc

0) You have FRA configured, but it is not your archivelog destination.

Setting log_archive_dest_N to the FRA directory is not the same as actually setting the FRA as an archivelog destination.

The configuration you most likely have:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata/flash_recovery_area
db_recovery_file_dest_size           big integer 20G
log_archive_dest_1                   string      location=/oradata/flash_recovery_area/qrec/archivelog

The proper configuration would be:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /oradata/flash_recovery_area
db_recovery_file_dest_size           big integer 20G
log_archive_dest_1                   string      location=use_db_recovery_file_dest

The naming of archivelog files also confirms this, as archivelogs in the FRA are use OMF format, and they are actually placed in seperate directories (named based on the date) each day.

So instead of this:

/oradata/flash_recovery_area/qrec/archivelog/log_1_1056_965658004.arc

The name of your archivelog file would be:

/oradata/flash_recovery_area/qrec/archivelog/2018_02_22/o1_mf_....arc

1) This is typically caused by inappropriate configuration of the control_file_record_keep_time parameter combined with a lacking backup method. The default value 7 means that records (of archivelogs or backups) are kept for 7 days, after that, they can be reused. If you do not backup/delete your archivelogs for 7 days, the database may reuse the controlfile records pointing to them, basically the database "forgets" these entries and leaves them in the filesystem.

You should regulary backup and delete your archivelogs and set control_file_record_keep_time appropriately based on your backup strategy.

If you use user-managed backups instead of RMAN, you need to take care of deleting these logs manually. RMAN can backup and delete these files in a single command.

2) Depends on your backup requirements.

If you use user-managed backups, make sure you have backups of these files, then delete them.

If you use RMAN, you can use catalog start with '/oradata/flash_recovery_area/qrec/archivelog/';, then use RMAN to backup and delete them.

If you do not need any backup of these files, simply delete them.


Update Postgres on server that already has version included with system Mac OS X (Lion) Server includes Postgres (9.0.5) that it uses for some system utilities (Wiki server, caldav, etc.). I'm about to start using this server for some Rails applications and am wondering how to get my "kit" set up correctly. I'd like the version of Postgres to be the same (9.1.3) from development into production...though I suppose this isn't absolutely mandatory.

Being as Apple probably won't update the system version of Postgres past 9.0.x until Mountain Lion this summer what's the best way to get 9.1.3 on the server? I've been warned against replacing the system version as it might screw up some of Apple's tools or, perhaps, just get overwritten when there's a Lion update.

Is it true that Postgres can happily run multiple versions of itself on the same server...under same OS instance? If so then I assume I could just install 9.1.3 into /usr/local/ and create new clusters there...leaving the system install alone? But I'm concerned about conflicts. Dunno. never tried this?!?

Maybe there's a another way to handle this?

Is it true that Postgres can happily run multiple versions of itself
  on the same server...under same OS instance?

I've run 8.3, 8.4, 9.0, and 9.1 at the same time on a single Windows server. You just need each PostgreSQL server listening on a different port. Choosing the port is part of the installation process.

I'm far to cynical to believe that PostgreSQL will never overwrite an existing data directory, but in my experience (none of it on OS/X), each version installs its own data directory.


filter out the records with same id having different values in a column Can anyone help me to solution for below issue.

create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,87214,1),
(357491,87214,1),
(343980,87216,1),
(357483,87216,1);

create table [Status1] (Type VARCHAR(100), Status_Id int);
insert into [Status1] values
('New','1'),
('Awaiting Approval','8'),
('New','1'),
('New','1'),
('New','1'),
('New','1');

    select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON dbo.A.Status_Id = dbo.B.Status_Id
--AND B.Type = 'New'
order by A.CartTransaction_Id

Below was the output I am  getting from above query.

Below was the expected result

I need to populate the data only if status type was 'New' for all order_id's in group of CartTransaction_Id

From the giving I need to filter out CartTransaction_Id (87214) as one of the order_id status_type was on Awaiting Approval

You could for example do this:

select
A.Order_Id, A.CartTransaction_Id, A.Status_Id, B.Type, B.Status_Id
FROM order1 A
JOIN dbo.Status1 B ON dbo.A.Status_Id = dbo.B.Status_Id
where
not exists (select 1 from order1 o1 where o1.CartTransaction_Id = A.CartTransaction_Id and o1.Status_Id != 1)
order by A.CartTransaction_Id

If you have a lot of data, then you might to want to check other alternatives for this, like max + over


It means that the session with pid 2036 belongs to a different user, and you don't have the privileges to see that data.

Consider that the queries run in that session might contain data that you have no right to see.

You must either be a superuser or (in recent versions) be a member of the pg_read_all_stats role to see these data.


How to migrate Oracle Database to MariaDB? Currently we have a server on Oracle version 18. We are planning to migrate to MariaDB. Is there any free service provided by MariaDB or oracle that helps migrate a database from Oracle to MariaDB?

There is certainly no such service by Oracle as they wouldn't want anyone to migrate away from their own database system.

MariaDB Corporation has a paid migration service to help migrating to MariaDB. This is run by experts in the field.

AWS has a fully automated service that appears to be able to migrate your schema and data, but presumably not much beyond that like stored procedures, functions and triggers. AWS is not your only option, though - other tools for this exist.

There are many articles around the web on how to do it yourself, but this will require a lot of knowledge and skills. See e.g. this article at Several Nines for a starter.


SP with Encryption Read/Writes Stats If a stored procedure in SQL Server 2005 is created with the syntax "WITH ENCRYPTION" ,are the reads and writes of the encrypted stored procedure recorded in the sys.dm_db_index_usage_stats? I am calculating reads by using the user_seeks + user_scans + user_lookups columns and the writes by using the  user_updates column of this DMV. Thanks

Tested it out myself and yes the stats are counted from an encrypted stored procedure if anyone else is looking for the answer.


PostgreSQL: restrict a GUC variable to only be set in proconfig? If a C extension defines a custom GUC variable, is there any combination of GUC "context","source","flags", etc., that can be used to make the system only allow it to be set from the SET (i.e. proconfig) attribute of a function declaration, and not from an ordinary SET command?

Or, is there any way for a check or assign hook for that variable to tell whether the assignment is coming from a proconfig and otherwise reject it?

There's no flag for it, though it's an interesting idea.

If proconfig is non-null then the security-definer function call path fmgr_security_definer is invoked. Interestingly this means that using function-level SET has a higher overhead, which I didn't expect. That path can be entered if a fmgr hook is needed or if the function is actually SECURITY DEFINER so you can't use that alone for testing how a var is set.

Applying proconfig creates a new GUC nesting level with NewGUCNestLevel then applies the GUC array with ProcessGUCArray, passing flags PGC_USERSET (for non-superusers) and PGC_S_SESSION. Those are the same flags passed to a normal SET so it doesn't look like there's any information cleanly available to GUC callbacks to tell if they're called in this context.

However:

It looks like you can hook function entry/exit in the fmgr. See FmgrHookIsNeeded, needs_fmgr_hook and fmgr_hook. A demonstration of use of the fmgr hook infrastructure can be found in contrib/sepgsql. This would let you apply custom GUCs that're otherwise defined as not settable at session level. You couldn't put them in proconfig though, since the fmgr will try to apply them and fail; you'd have to store them elsewhere. You can't apply them and remove them from the proconfig array because it's cached and you really don't want to go modifying things returned from the syscache. Anyway, the fmgr hook is fired just after GUCs are applied.

I'd look into doing it with a fmgr hook.


Cannot change MySQL root password I have run the following commands found here.

shell> mysql
mysql> FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'

But when I go to login as root, I can type any string as a password, and it will still let me login.

How can I enforce a password for root, and not allow anonymous users by simply typing mysql

The following worked for me:

I ran through the command line wizard to set this up:
shell> mysql_secure_installation

Then entered the following:
shell> mysql
ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

Credit goes to this question/answer.


postgresql: get last/newest value for each group of rows I have the following notifications table in postgresql:
- notification_id, bigint
- group_key, character varying(24)
- associated, jsonb object
- timestamp

The table now looks like this
+------------------------------------------------------+
| notification_id | group_key | associated | timestamp |
+------------------------------------------------------+
| 1               | key1      | {123}      | ...       |
| 2               | key1      | {456}      | ...       |
| 3               | key2      | {789}      | ...       |
+------------------------------------------------------+

The following query gives the following results:
SELECT
    a.group_key,
    MAX(a.notification_id) as max,
    COUNT(a.notification_id) as total
FROM
    data.notifications a
GROUP BY
    a.group_key
ORDER BY
    MAX(a.notification_id) DESC

+-------------------------+
| group_key | max | total |
+-------------------------+
| key1      | 2   | 2     |
| key2      | 3   | 1     |
+-------------------------+

Explanation:
the notifications with ID 1 and 2 belong to the same notification group (key1). I want to fetch the max notification ID for the group (in this case 2), the number of notifications in the group (in this case also 2).
What I want now is also the content of the column 'associated' of the last notification, so in this case from notification with ID 2 (so in this case '{456}').
This column contains a jsonb object with various keys. I want to do joins from other tables with those keys, but I don't know how to get the last JSONB object for each notification group.
I tried using LAST_VALUES but I constantly get errors in pgadmin, saying that the column "associated" should be in the group by clause, but when I do this, I don't get the correct resultset returned.
SELECT
    a.group_key,
    MAX(a.notification_id) as max,
    COUNT(a.notification_id) as total,
    LAST_VALUE(a.associated) OVER (PARTITION BY a.group_key ORDER BY MAX(a.notification_id) DESC) as associated
FROM
    data.notifications a
GROUP BY
    a.group_key
ORDER BY
    MAX(a.notification_id) DESC

This is a classic case where using the ROW_NUMBER() window function can be used to get the latest row within a grouping (PARTITION) like so:
WITH CTE_Notifications_Sorted AS
(
    SELECT notification_id, group_key, associated, timestamp,
    COUNT(notification_id) OVER (PARTITION BY group_key) AS notification_id_count,
ROW_NUMBER() OVER (PARTITION BY group_key ORDER BY notification_id DESC) AS PartitionSortId -- Generates a unique ID for each row within the grouping (partition) of group_key ordered by the notification_id descending
    FROM data.notifications
)

SELECT group_key, notification_id AS notification_id_max, notification_id_count AS total, associated, timestamp
FROM CTE_Notifications_Sorted
WHERE PartitionSortId = 1 -- Filter out everything but the latest row of each group_key partition

By using the COUNT() function in a window function as well in the above CTE, you're able to remove your GROUP BY clause in the final SELECT.
Now that you have the associated column for the correct row, if you need to parse out a specific value you can follow this StackOverflow answer or leverage one of these JSON Functions and Operators.


Standalone MongoDB to MongoDB Shard Setup I'm learning MongoDB currently so maybe the title of this question is worded poorly, but I'm looking to understand the scaling of Mongo better. I did some research on what DB would be best for my current needs and currently a standalone instance of Mongo fills that, but I'd like to plan for the future.

I'm trying to figure out the process of scaling up MongoDB. In my case the issue I foresee is an extremely large number of documents requiring bigger servers. I began to look up information on ways to solve this and the best way I could see was using Mongo's sharing capability. The only issue though is I don't understand the process of going from standalone to shards. Is there a document that can explain the process or is there a better way to handle such scaling?

Any help is appreciated while I'm learning this.

How it is done

Basically, it is easy:

Set up three(!) config servers
Start a mongos query router and have the --configdb option string point to the three config servers. Note: the string has to be identical on all mongos
Restart your going to be shard with the shardsvr option set. This step isn't strictly necessary, but best practise. What this basically does is that mongod will start on a different port (27108), preventing accidental (and potentially problematic) direct access to the shard.
Connect to the mongos instance.
Add the shard via the sh.addShard() command.
Done. Start sharding the collections as needed.

Some advice

If it is foreseeable that you will have to scale out, I would go into production with a sharded cluster consisting of a single shard right away. This makes scaling out much easier and faster when you reach the limits of your single shard – simply fire up another shard, add it using the addShard command and you are done.
Always, always, always let your shards consist of replica sets. And from personal experience with one of my customers: While using a replica set with only two data bearing nodes and an arbiter is possible, I would not advice doing so. The reason for this advice is that if you are doing maintenance on one of the data bearing nodes, there is no failover possibility in case the other node fails. So your shards should consist of replica sets with (at least) three data bearing, non-hidden and non-delayed nodes with default priority.
While MongoDB is pretty easy to fire up and maintain in a standalone and even replica set configuration, things tend to get a lot more complicated when you start to shard in a production environment. Make sure you have a MongoDB specialist at hand when going into production.


how to compare two date column from two different tables by extracting specific parts in both columns using trigger/function in postgresql 10 I have written program something like below as per my question:
CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare

username VARCHAR(40);

BEGIN
    --
    -- Create a row in emp_audit to reflect the operation performed on emp,
    -- make use of the special variable TG_OP to work out the operation.
    --
       IF (TG_OP = 'DELETE') THEN
    select userid into username
    from gui_audit_trail
    WHERE messageid='LS_REF_DATA_ROW_DELETED'
        and description like '%BASE_HOST%'
        and to_char(recorded,'YYYY-MM-DD HH:MI:SS')=to_char(OLD.last_update,'YYYY-MM-DD HH:MI:SS');
        RAISE NOTICE 'username is (%)',username;
        INSERT INTO HIST_BASE_HOST SELECT OLD.zone_id, OLD.site_id, OLD.org_id, OLD.start_date, OLD.end_date, OLD.reserved_capacity, now(),'D', username;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO HIST_BASE_HOST
           SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'U',user;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO HIST_BASE_HOST SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'I',user;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$hist_base_host_table_trig$ LANGUAGE plpgsql;

CREATE TRIGGER hist_base_host_table_trig
AFTER INSERT OR UPDATE OR DELETE ON base_host
    FOR EACH ROW EXECUTE PROCEDURE hist_base_host_table_trig_func();

But "username" is not populating in output, expectation is "administrator" as output.
The two table data is as below:
select * from gui_audit_trail;
 id  |    userid     | moduleid  |    classid    |     typeid     |        messageid        |        recorded
 718 | administrator | eventlink | CLASS IGNORED | METHOD IGNORED | LS_REF_DATA_ROW_DELETED | 2021-03-10 18:48:02.439

select * from hist_base_host;
 zone_id | site_id |   org_id   | start_date |  end_date  | reserved_capacity |        last_update         | chg_code |   user_id
       5 |       9 | PBTP       | 2002-01-01 | 2015-01-01 |              0.00 | 2021-03-10 18:48:02.413111 | D        |

in select query in above function, I want to cut time till "2021-03-10 18:48:02" from both columns recorded and last_update, but seems its not working, Is there any way to achieve the same functionality?

If you only want to remove milisecinds
CREATE OR REPLACE FUNCTION hist_base_host_table_trig_func() RETURNS TRIGGER AS $hist_base_host_table_trig$
declare

username VARCHAR(40);

BEGIN
    --
    -- Create a row in emp_audit to reflect the operation performed on emp,
    -- make use of the special variable TG_OP to work out the operation.
    --
       IF (TG_OP = 'DELETE') THEN
    select userid into username
    from gui_audit_trail
    WHERE messageid='LS_REF_DATA_ROW_DELETED'
        and description like '%BASE_HOST%'
        and date_trunc('second', recorded::timestamp)
        =date_trunc('second', OLD.last_update::timestamp);
        RAISE NOTICE 'username is (%)',username;
        INSERT INTO HIST_BASE_HOST SELECT OLD.zone_id, OLD.site_id, OLD.org_id, OLD.start_date, OLD.end_date, OLD.reserved_capacity, now(),'D', username;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO HIST_BASE_HOST
           SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'U',user;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO HIST_BASE_HOST SELECT NEW.zone_id, NEW.site_id, NEW.org_id, NEW.start_date, NEW.end_date, NEW.reserved_capacity, now(), 'I',user;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$hist_base_host_table_trig$ LANGUAGE plpgsql;

CREATE TRIGGER hist_base_host_table_trig
AFTER INSERT OR UPDATE OR DELETE ON base_host
    FOR EACH ROW EXECUTE PROCEDURE hist_base_host_table_trig_func();


MySQL Query running slow when using REPLACE function instead of CASE statement Is it better to use CASE statements instead of REPLACE functions in MySQL when mapping a comma-separated-string field?
The below query runs extremely slow using replace.
Note, the underlying user_roles table is of format [user_id (bigint), string_of_user_role_ids (varchar(200))]
-- this runs slowly
select      string_of_user_role_ids
            , replace(replace(replace(replace(replace(string_of_user_role_ids,
    '10', 'Scientist'), '9', 'Superhero'), '8', 'Teacher'), '7',
         'Journalist'), '6', 'Farmer')
            , count(1)
from        user_roles
group by    1,2
order by    3 desc

-- this runs quickly, but is more difficult to keep adding in multiple new when clauses whenever a new user role is added
select      string_of_user_role_ids
            , case  when string_of_user_role_ids= "6" then 'Farmer'
                    when string_of_user_role_ids= "7" then 'Journalist'
                    when string_of_user_role_ids= "8" then 'Teacher'
                    when string_of_user_role_ids= "6,7" then 'Farmer, Journalist'
                    when string_of_user_role_ids= "6,8" then 'Farmer, Teacher'
                    when string_of_user_role_ids= "7,8" then 'Journalist, Teacher'
                    when string_of_user_role_ids= "6,7,8" then 'Farmer, Journalist, Teacher'
                    -- ... etc.
                    else 'Unknown' end as app_user_type
            , count(1)
from        user_roles
group by    1,2
order by    3 desc

Ideally I would use the REPLACE function instead of a CASE statement, as it seems easier to scale out in terms of expanding the code and less risk to manage.
I can't understand why one query runs quickly and the other very slowly (seconds versus minutes, after a few mins I killed the slow query).
Ideas/questions are welcome please.
Explain statement output for both is the same:

Instead of trying to parse a string column, use the database's built-in relational capabilities as they were intended to be used.
First, we need three tables, Users, Roles, and UserRoles.  UserRoles is a cross reference table that joins Users to their Roles.
CREATE TABLE Users
(
    UserID int NOT NULL primary key
    , UserName varchar(100) NOT NULL
);

CREATE TABLE Roles
(
    RoleID int NOT NULL primary key
    , RoleName varchar(50) NOT NULL
);

CREATE TABLE UserRoles
(
    UserID int NOT NULL
    , RoleID int NOT NULL
    , FOREIGN KEY UserRolesUserIDFK (UserID) REFERENCES Users (UserID)
    , FOREIGN KEY UserRolesRoleIDFK (RoleID) REFERENCES Roles (RoleID)
    , PRIMARY KEY (UserID, RoleID)
);

In the UserRoles table above, you'll note we have two foreign keys, one each for the User entry and the associated Role entry.  This provides referential integrity that prevents deletion of a role that is in use by a user, and adding a user to a non-existent role.  I've also added a compound primary key to the table, on (UserID, RoleID), which means you cannot add a user to a single role more than once.  This eliminates the need to DISTINCT the output queries below, which for a system with a large number of Users and Roles would make a significant positive difference to performance.
Next, we'll insert some sample data:
INSERT INTO Users (UserID, UserName)
VALUES (1, 'Sarah')
     , (2, 'Hannah');

INSERT INTO Roles (RoleID, RoleName)
VALUES (1, 'Administrator')
     , (2, 'User');

INSERT INTO UserRoles (UserID, RoleID)
VALUES (1, 2)
     , (2, 1)
     , (2, 2);

Now, when we want to get the results, we do this simple query:
SELECT u.UserName
    , r.RoleName
FROM Users u
    INNER JOIN UserRoles ur ON u.UserID = ur.UserID
    INNER JOIN Roles r ON ur.RoleID = r.RoleID
ORDER BY u.UserName;

Results look like:
╔══════════╦═══════════════╗
║ UserName ║   RoleName    ║
╠══════════╬═══════════════╣
║ Hannah   ║ Administrator ║
║ Hannah   ║ User          ║
║ Sarah    ║ User          ║
╚══════════╩═══════════════╝
If you need the role membership on a single line, along with the count of the number of roles each user is a member of, then the following should work:
SELECT u.UserName
    , (
        SELECT GROUP_CONCAT(r.RoleName SEPARATOR ', ')
        FROM Roles r
            INNER JOIN UserRoles ur ON r.RoleID = ur.RoleID
        WHERE ur.UserID = u.UserID) AS Roles
    , (
        SELECT COUNT(1)
        FROM UserRoles ur
        WHERE ur.UserID = u.UserID) AS CountOfRoles
FROM Users u
ORDER BY u.UserName;

Output looks like:
╔═══════════╦══════════════════════╦══════════════╗
║ UserName  ║        Roles         ║ CountOfRoles ║
╠═══════════╬══════════════════════╬══════════════╣
║ Hannah    ║ Administrator, User  ║            2 ║
║ Sarah     ║ User                 ║            1 ║
╚═══════════╩══════════════════════╩══════════════╝
The queries above can efficiently use indexes without any headaches, and they easier to comprehend than a a large number of REPLACE or CASE statements.
Multiple REPLACE or CASE statements, where not necessary, only introduce complexity that may mask the real intention, and confuse the query optimizer, producing poor plans and possibly slow results.
A significant benefit of using discrete rows for each user/role relationship is you can have an unlimited number of roles per user, without needing an unlimited size string column to hold the membership details.  This also makes it super easy to determine if someone is a member of a specific role, or to see all the members of specific roles.
For example, to see who's a member of the User role, you could do this:
SELECT u.UserName
FROM Users u
    INNER JOIN UserRoles ur ON u.UserID = ur.UserID
    INNER JOIN Roles r ON ur.RoleID = r.RoleID
WHERE r.RoleName = 'User'
ORDER BY u.UserName;

Results:
╔══════════╗
║ UserName ║
╠══════════╣
║ Hannah   ║
║ Sarah    ║
╚══════════╝
This design also makes it easy to see who is not a member of a specific role:
SELECT u.UserName
FROM Users u
WHERE NOT EXISTS (
    SELECT 1
    FROM UserRoles ur
        INNER JOIN Roles r ON ur.RoleID = r.RoleID
    WHERE r.RoleName = 'Administrator'
        AND ur.UserID = u.UserID
    );

Results:
╔══════════╗
║ UserName ║
╠══════════╣
║ Sarah    ║
╚══════════╝
I've created a fiddle for the above code here.


postgres user is not superuser Im having some problems with my postgresql server. In particular, some databases cannot be dropped.

On further inspection, I see that the postgres user is not a SuperUser.

There are two other users that are super users:

pgsql
s6cefdco

However I do have the password for these.

How can I make postgres a SuperUser again?

The operating system is CentOs.

I have tried the following:

  sudo -u postgres postgres --single /var/lib/pgsql/9.6/data

Which results in

sudo: postgres: command not found

Try running this,

sudo -u postgres dropdb myDatabase


Match Against search not doing exact search? I'm using MariaDB version 10.2.22 with a InnoDB engine, utf8 charset, it has one column that is indexed FULLTEXT. The table itself is only 4.4 GB with avg row length at 255.  When I do a query such as:
    SELECT m.id as id
    FROM
    table1 m,
    table2 d
    where
    m.id= d.id AND
    MATCH ([column]) AGAINST ('"CE20-2019"' in boolean mode)

I get a "SQL Error [128] [HY000]:Table handler out of memory". I suspect that the "-2019" is being searched or not included, I'm not sure, and that the found matches exceeds the memory allowed. The [column] is a "LongText" datatype.
I've tried changing the innodb_buffer_pool_size to 4 GB and then 8 GB, but still get this error.
(System has 24 GB of RAM, not sure if my comfortable changing it to 70% of RAM listed on their documentation found here: https://mariadb.com/kb/en/mariadb-memory-allocation/), since I'm on version 10.2.22, this innodb_buffer_pool_size variable is suppose to be dynamic so I don't have to restart the MariaDB service to restart Mysqld.
Any ideas what's causing the Table handler out of memory?

Plan A:
As you put the data into the table, change - to, say, _ so that such items will be on "word", not two.  Do a similar change when constructing the AGAINST clause.
If necessary, make an extra column with that and do any other cleansing of the data -- Then index that column with FULLTEXT, but use the original table for output.
Plan B:
MATCH (col) AGAINST ('+CE20' in boolean mode)
AND col LIKE '%CE20-2019%'

Or, if you need a regexp to handle a letter/digit before:
MATCH (col) AGAINST ('+CE20' in boolean mode)
AND col REGEXP '\\bCE20-2019'

(That's for MySQL 8; use [[:<:]] instead of \\b for other versions.  That is "word boundary" and should eliminate most of the 20 extra rows.)
That will run nearly as fast as the original query should have run.
Alas, this requires understanding the incoming data and making programmatic decisions as to how best to construct a fast query.
Note that the use of + when IN BOOLEAN MODE may be the 'right' thing to do for your app.


From PostgreSQL to SQL Server (No User/Pass) Some of you MAY be able to crack a really tough nut...

I have to move all the data from a PostgreSQL (9.1 - Windows) database to SQL Server because the company I am contracting with is upgrading the old program with a newer .NET version.

The real nut is that the User/Password for Postgres are baked in the old program... Of course the guy who wrote it is gone. No source code or any way to contact him. (Smaaaart)

I was able to obtain the WHOLE Postgres directory (Bin, Data... The whole thing) and I hope it can be done.

Is there a way out of this mess, or should I just blow the contract and look for something else?

THANK YOU!

I think currently you are not able to log into the postgresql database? If this is the case you can get access of the database by changing the access authorization. Please open  pg_hba.conf and change the following line:

local   all             postgres                                md5

to:

local   all             postgres                                peer

or

local   all             postgres                                trust

Now restart the postgresql service. You should now be able to connect to the database by typing: psql

You can get list of databases by typing: psql -l


Mysql: How does circular replication avoid infinite looping of a transaction In case of circular replication A -> B -> C -> A.

How come a transaction started on A will be ignored from being replicated from C to A at the end of one complete cycle ?

I believe parent server-id will be logged along with the transaction and it prevents the transaction being executed on the parent server.

Please correct me if i am wrong.

Thanks in advance.

Yes, you are correct. This relies on the server ID being set correctly in the standard MySQL configuration. In MySQL 5.6 and above you can configure the server to use GTIDs (Global Transaction IDentifiers) and they are used on all servers to determine if transactions need to be executed or not


Finding number such as 111111,0000,4444, using REPLICATE I have been trying to find repeated number such as 1111, 22222, 55555, in my table. After some research, I found that this is said to work:

SELECT *
FROM tablename
WHERE columnname = REPLICATE(LEFT(columnname,1),LEN(columnname))

It did work, but I want to understand why it works. If anyone understands what this is doing please enlighten me.

columnname = REPLICATE(LEFT(columnname,1),LEN(columnname))

E.g.

columnname = '111111'
=>
LEFT(columnname,1) = '1'
LEN(columnname)    = 6

REPLICATE(LEFT(columnname,1),LEN(columnname)) =
REPLICATE(        '1'       ,       6       ) =
'111111'

You take the leftmost character of the column and replicate it to the length of the column.
If the result is equal to the column then you know that all the characters of the column are the same of the leftmost character.


Is this data duplication when two exact pieces of data have different lifespans? In my system, user has many bank accounts stored in bank_accounts table. Each bank account has a currency column. Now, users can receive payments to those bank accounts, stored in payments table. So, each payment is related to one row in bank_account table. In addition to bank_account_id, each row in payments table also has amount and currency columns. As you can see, the currency column on payments table causes data duplication, because the currency could always be derived from the related bank account. However, I'm afraid that in the future a bank account may be deleted from the system but the related payments will have to stay. Is it a valid argument to conclude that I don't have duplicated data in my schema?

Normalization rules are a useful guideline, not a strict law.

Even if you don't anticipate the currency field in the payments table to ever be different than the currency field for the related bank_account, I can think of any number of reasons to keep both:

What if the account record is ever deleted? (Accidentally or deliberately)
What if the account record changes currency type?
The field could be used as a point of immediate validation before committing the payment
The field could be used after the fact as an integrity/validation audit
It could improve performance for certain queries instead of requiring a join to the bank_accounts table.


Are database options backed up in a full backup Are database options backed up in a full backup?

For instance, I have a database which has AUTO_UPDATE_STATISTICS disabled (0) (AFAIK, default is 1).
When I restore a full backup of that database, does this option stay disabled(0)?

Can you point me to any reference that mentions that?

db options gets restored except the ones like service broker, cdc, as mentioned here which you have to explicitly tell when restoring the db.

I tested the autoupdate stats - setting it to false, backing up db and restoring it as different name.

For new db, you can just set the model db settings and it will get inherited.


Breakout Query Results By Year And Month I have two tables that house data and I need to perform calculations on both sets of data, so I thought a Union All query would be best.  Well that gets me the result set that I am after, but now i need to take this a step further and provide a breakout by year/month for the data.  So the equation I am after is (vramt-saleamt)/vramt and this is my DDL which provides the data and a date, but how would I split the data out to be a breakdown by month/year

Desired Output

Jan 16 Feb 16 Mar 16
XXXX   XXX    XXX

And this is sample DDL

    Declare @Store1 Table (cardname varchar(200), saleamt float, saledate date)
Declare @TF Table (cardname varchar(200), vramt float ,saledate date)

Insert Into @TF (cardname, vramt, saledate) Values
('Red', 200.00, '20160101'), ('Pink', 300.00, '20160201'), ('Orange', 400.00, '20160301')

Insert Into @Store1 (cardname, saleamt, saledate) Values
('Red', 400.00, '20160201'), ('Pink', 800.00, '20160204'), ('Orange', 900.00, '20160301')

SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
saledate As saledate
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
UNION ALL
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
saledate as saledate
FROM @Store1 t2
Group By t2.cardname, t2.saledate

This is very klugey and I've quite a few gymnastics here give your initial code, but the final data set still needs to be ported using a PIVOT if you want ONLY one row. I would suggest you look at the PIVOT operator to understand to use this.

    Declare @Store1 Table (cardname varchar(200), saleamt float, saledate date)
Declare @TF Table (cardname varchar(200), vramt float ,saledate date)

Insert Into @TF (cardname, vramt, saledate) Values
('Red', 200.00, '20160101'), ('Pink', 300.00, '20160201'), ('Orange', 400.00, '20160301')

Insert Into @Store1 (cardname, saleamt, saledate) Values
('Red', 400.00, '20160201'), ('Pink', 800.00, '20160204'), ('Orange', 900.00, '20160301')

;with datavr as (
SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
--saledate As saledate,
rtrim(cast(saledate as varchar(7))) as monyr
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
),
datasa as (
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
--saledate as saledate,
rtrim(cast(saledate as varchar(7))) as monyr
FROM @Store1 t2
Group By t2.cardname, t2.saledate
)

select
    isnull(v.cardname, s.cardname) as cardname,
    isnull(v.monyr, s.monyr) as monyr,
    sum(v.vr) as vrsum,
    sum(s.sa) as sasum,
    (sum(isnull(v.vr,0)) - sum(isnull(s.sa,0)) / sum(v.vr)) as margin
from datavr v
full outer join datasa s
    on v.cardname = s.cardname
    and v.monyr = s.monyr
group by
    isnull(v.cardname, s.cardname),
    isnull(v.monyr, s.monyr)

More kluge, but if you want to use the unpretty PIVOT to really get that one row:

;with datavr as (
SELECT
T1.cardname,
SUM(ISNULL(T1.vramt,0)) AS vr,
0 As sa,
--saledate As saledate,
cast(datename(month,saledate)as varchar(3)) + '-' + right(year(saledate),2) as monyr
FROM @TF AS T1
GROUP BY T1.cardname, T1.saledate
),
datasa as (
Select
t2.cardname,
0 As vr,
SUM(ISNULL(t2.saleamt,0)) As sa,
--saledate as saledate,
cast(datename(month,saledate)as varchar(3)) + '-' + right(year(saledate),2) as monyr
FROM @Store1 t2
Group By t2.cardname, t2.saledate
)
, penult as (
select
    --isnull(v.cardname, s.cardname) as cardname,
    isnull(v.monyr, s.monyr) as monyr,
    --sum(v.vr) as vrsum,
    --sum(s.sa) as sasum,
(sum(isnull(v.vr,0)) - sum(isnull(s.sa,0))) / sum(isnull(v.vr,0)) as margin
from datavr v
full outer join datasa s
    on v.cardname = s.cardname
    and v.monyr = s.monyr
group by
    isnull(v.cardname, s.cardname),
    isnull(v.monyr, s.monyr)
)
select p.*
from penult d
pivot (
    max(margin)
    for monyr in ([Jan-16], [Feb-16], [Mar-16])
) p


How to create automatic partitions on a non-time based column in Postgres? I want to create a partition on a column which is of type serial. A hash based partition seems appropriate here. But from what I understand I will need to create all the partitions myself (based on remainder). Is there a way to create all the partitions (say 10 partitions) using some automated extension like pg-partman?

There is nothing built into Postgres to do "automatic partition creation", so you either need to write a little script that automates that process or use a tool like pg_partman if it supports that.
In psql you could use a query like this to generate the create table statements and then run it using \g:
select format('create table %I partition of my_table for values with (modulus 10, remainder %s);', 'my_table_p'||to_char(g.i, 'FM00'), g.i)
from generate_series(0,9) as g(i)
\g

Otherwise just use a do block:
do
$$
declare
  l_sql text;
begin
  for i in 0..9 loop
    l_sql := format('create table %I partition of my_table for values with (modulus 10, remainder %s)', 'my_table_p'||to_char(i, 'FM00'), i);
    execute l_sql;
  end loop;
end;
$$
;

Not tested!
Of course you can put the PL/pgSQL loop into a function or procedure and call it with the number of partitions you want.


Is there any point in specifying the precision and scale of a number for a foreign key? I am running Oracle 11gR2 11.2.0.3. I have two tables:

table1 contains a column named SUBJECT_ID, whose datatype is NUMBER(7,0) (numbers range from 13 to 48480)
table2 contains a a column named SUBJECT_ID, whose datatype is NUMBER, and is a foreign key to table1.SUBJECT_ID.

Is there any point in specifying the precision and scale of table1.SUBJECT_ID (i.e. declaring it NUMBER(7,0) instead of simply NUMBER)?

The Oracle documentation advises to specify the precision and scale, as "it provides extra integrity checking on input". But in my case wouldn't the foreign key constraint take care of this integrity checking?

Connor MacDonald blogged about this over in NUMBER data type... what harm can it do? as well as Ask Tom: "How do I determine how much storage will be required for NUMBER(p, s)?". In short, it does matter.

Take this table:

CREATE TABLE T (
    x1 number,
    x2 number(6,3)
);

x1 will be 21 bytes while x2 will be 2 bytes. By not specifying the data type, you're taking up extra space that may not be needed.


Organizing Postgres Databases Is there a way in pgAdmin to organize Postgres databases into "folders" or groups? Each Django project I'm creating has it's own Postgres database, and I would like to find a way to organize them.

I asked this question on StackOverflow (https://stackoverflow.com/questions/22727629/organizing-postgres-databases), and my question was "put on hold" because it was off topic.

On StackOverflow, Erwin Brandstetter suggested I create a new Server Group and set the Group dialog box:

My question is: how to create new options for the "Group" dropdown box? When I use pgAdmin3 command "Add Server" on my MacBook Pro, I only see one option under "Group" called "Servers".

Do I need to create a new "Database Cluster"? I found this command in the Postgres Documentation in Section 17.2:

$ initdb -D /usr/local/pgsql/data

On my MacBook Pro, my default databases are located at /usr/local/var/postgres/base and /usr/local/var/postgres/global.

Should I create a new directory, such as /usr/local/var/postgres2, and then execute a command like this?

$ initdb -D /usr/local/var/postgres2

Just type it into the "Group" field. Besides the listed options you can type a new group name freely.

It's a setting of pgAdmin, completely independent of the database itself and not stored in the database.

The handling is flexible, but renaming a bunch of "servers" can get tedious.

As to your comment: I repeat what I already wrote in my last answer:

  Basically, each connection allows access to a whole database cluster

But if you follow my instructions over there, you connect to the right database automatically. I suggest, you read the manual about Managing Databases for basic understanding.


How do retrieve unique rows where all all columns are the same except id So it looks like for some entries to the table the data is being written multiple times from our system, so all the rows entered for a specific data item are written 3 times, with exactly the same data apart from the auto-generated ID is different (incremented by 1 each time).

How would I retrieve all the essentially unique rows from the table? A join on itself doesn't seem to be working as the IDs are different.

Omit the ID column from your SELECT ... statement and and a DISTINCT. As in:

SELECT DISTINCT COLUMN2, COLUMN3, .... FROM TABLE1

If you want to find how many rows exist for the same values then do a COUNT() combined with a GROUP BY. As in:

SELECT COUNT(ID), COLUMN2, COLUMN3, COLUMN4, ....
    FROM TABLE1
    GROUP BY COLUMN2, COLUMN3, COLUMN4, ....

Reference Material (MySQL Dev Site)

8.2.1.18 DISTINCT Optimization
3.3.4.8 Counting Rows
12.20.2 GROUP BY Modifiers


Decrypt T-SQL log backup header and read LSN For some reason I need to read the LSN from the T-SQL logs backups without restoring them or even their headers (I assume even restoring only their headers will change the LSN on the database side too, but I'm not sure).

So is the T-SQL log backup files encrypted or does they have special structure? Any information as to where should I start?

Could anyone confirm or denies that restoring the header only wouldn't affect the sys.fn_dblog or anything else?

Could anyone confirm or denies that restoring the header only wouldn't
  affect the sys.fn_dblog or anything else?

RESTORE HEADERONLY doesn't target any database in its syntax:

RESTORE HEADERONLY FROM <backup_device>

It reads the backup device and returns data to the client about what's in the backup.  It won't affect your databases in any way.


Surrogate key resolution, or what am I missing in the Surrogate v Natural argument? I've read a lot from both camps in the surrogate vs. natural key debate. What I am struggling to understand, and what seems to be glossed over, especially in explaining how surrogate keys are more efficient, is how do referencing tables resolve a surrogate key?

Say, I am using an ORM in an application, and have a userID in an application object. now we want to insert into a SelectedTask table and a SubTask table that are defined as follows (ignore the awkwardness of the real word example):

CREATE TABLE SelectedTask(
userID INT References User(userID)
taskNumber INT,
name TEXT ,
jobSiteID INT References Site(jobSiteID),
PRIMARY KEY (???)
);

CREATE TABLE SubTask(
assignee INT References User(assignee),
subTaskID INT References Subtasks(subTaskID),
timeAllowed INT,
reportTo INT References Group(reportTo),
-- how do we reference SelectedTask?
);

If we use a surrogate key for the SelectedTask table, how do we use that in the SubTask table? If we use natural keys, the primary key could simply be a compound primary key of userId and taskNumber, and we could insert into both at the same time assuming we have programmatically gathered all of the pieces.

A proper Postgres schema for your example could look like this:

CREATE TABLE selected_task(
  selected_task_id serial PRIMARY KEY  -- surrogate PK
, user_id          int REFERENCES users(user_id)
, task_number      int
, name             text
, jobsite_id       int REFERENCES site(jobsite_id)
);

CREATE TABLE sub_task(
  sub_task_id      serial PRIMARY KEY
, selected_task_id int REFERENCES selected_task  -- this is how
, assignee         int REFERENCES users(assignee)
, time_allowed     int
, report_to        int REFERENCES groups(report_to)
);

Use a serial column as surrogate PK.

PostgreSQL SELECT primary key as "serial" or "bigserial"
Auto increment SQL function
Use serial ID for multiple columns and return it from INSERT

You can't use reserved words like user or group as identifier.
Some other improvements. Detailed instructions here:

How to implement a many-to-many relationship in PostgreSQL?

INSERT in both tables at the same time?

Use a data-modifying CTE with the RETURNING clause, or two INSERT statements in a transaction with lastval() (which is the Postgres equivalent for SCOPE_IDENTITY() in SQL server @Aaron mentioned in his comment):

Related answer with code example for either method.

Inserting into related tables


SQL Availability Group Validation Error I am trying to add a replica in a different subnet to my availability group and keep running into an error. I have confirmed the default file locations are the same on both servers but can't figure out why I am getting this error.

This isn't saying the default file locations need to be the same, this is saying that you don't have identical paths for all files in the database(s) on the secondary server.

Example:

Primary Replica: Database files are in E:\Mount1\, E:\Mount2\, F:\Mount3

Secondary Replica: Existing directories E:\Mount1\, F:\Mount3

You'll notice that the secondary replica is missing E:\Mount2\

The fix is to make the secondary have identical paths for the databases involved.


Sqlite: Finding the next or previous element in a table consisting of integer tuples I have a sqlite table called tuples defined like
create table tuples
(
    a INTEGER not null,
    b INTEGER not null,
    c INTEGER not null,
    d INTEGER not null,
    primary key (a, b, c, d)
) without rowid;

filled with millions of unique tuples (>1TB). New tuples are inserted quite often, with "random" values. Rows are removed only in very rare occasions.
For an external process accessing the database, I need to find the "next" or "previous" existing 4-tuple in the table.
For example: Given tuples (1-1-1-1), (1-1-1-4) and (1-2-3-4), for the tuple (1-1-1-3) (which does not need to exist in the table) the "next" element is (1-1-1-4) and the previous is (1-1-1-1) (both which need to exist). For (1-1-1-4) (1-2-3-4) is the "next" element. Corner-case: If there there actually is no "next" or "previous" element the result is allowed to be empty. (1-2-3-4) has no "next" element.
At the moment I try to find the next tuple with ("center" is (1-1-1-3))
select a,b,c,d from tuple
where (a == 1 AND b == 1 AND c == 1 AND d > 3) OR
      (a == 1 AND b == 1 AND c > 1) OR
      (a == 1 AND b > 1) OR
      (a > 1)
order by a, b, c, d
limit 1;

which is really slow.
The short question here is: Is there a way to speed up the the process? Ideally the response should only take milliseconds, like searching for the exact value of a tuple (which is basically instantaneous). Using other/more indices, multiple and/or other queries, even changing the database structure is a valid solution.

Edit: Each element of a tuple may cover the whole allowed integer range.

At the moment I try to find the next tuple with ("center" is (1-1-1-3))

Test
SELECT *
FROM tuples
WHERE (1,1,1,3) < (a,b,c,d)
ORDER BY a,b,c,d LIMIT 1

fiddle


Can any one Explain me What is the Use of Cascading Replication in Postgresql? What are the advantages of cascading replication in PostgreSQL.

I have done Testing On this With three nodes.

M1---->S1----->S2

What is the Use if My master(M1) goes Down(failover). Where the secondary Slave(s2) Server Useful for us in this setup.

If Your application can get data from more than one database You can use cascading replication for load balancing(One App get data from multiple bases) but only in case when it's synchronous replication(in case of cascading rep. it's heavy).

In case of M1 fail You can do Python script that will place S1 as master(including updating port, config, and connecting with S2). It's not hard, with Python You can do it in about one hour. Other option is Python script that will restore M1 with data from S1, use Python library: PSYCOPG2


What are the possible considerations and drawbacks of storing binary image data directly in a table? Although related to this question, I have a slightly different angle to approach this issue from. Here is my situation:

I am writing a web application (either in PHP or Python) that manages addons for a desktop application. Users can browse addons, install them, upload them, etc.

I am planning the schema for the database and found myself with a decision to make:

  Is it better to store icons (for the addons) in the table itself or instead store them in the filesystem and simply store a filename in the table?

The icons are small (48x48 or close to that) and likely won't take up more than 5 or 6 KB at the most. Are there any serious drawbacks to storing the image data in the table? Are there other implications that I should be aware of? Will performance be a concern? Will storage be an issue?

Edit: I am currently looking at MyISAM tables in a MySQL database.

You haven't specified the database platform your considering but at this size/scale, it's unlikely to matter.

5kb per record is trivial. 1 million 5kb records is < 5GB, still trivial. 10 million 5kb records... still not something to lose any sleep about.

If we were to get platform specific, a typically exhaustively researched white paper by Paul Randall on SQL Server filestream storage suggests that it outperforms table storage where files are 1MB or greater in size. Below 1MB file sizes, the positives are primarily around filestream bypassing the buffer pool.

The positives for database storage:

Transacted. The binary data is tied to it's metadata, not exposed by a filesystem, safe from accidental deletion.
Reduced backup overhead. 1 million records in a 5GB file vs 1 million 5kb files.
The natural companion to 2), faster restore.

Edit: The negatives (as suggested by Aaron)

Requires programatic access.
Cost of storage. More of an issue if you have to deal with platform restrictions on size e.g. 50GB on Azure.


Select query for returning different data based on creation time I have a view, which will select data from table reports. The select will filter the data based on multiple dates.

reports table: id, key_id, position, created_at

My select:

pr.key_id,
COALESCE(CASE WHEN date(pr.created_at) = date(current_date - interval '1' day) THEN array_agg(DISTINCT pr.position) ELSE '{0}' END ) AS yesterday,
COALESCE(CASE WHEN date(pr.created_at) = date(current_date - interval '2' day) THEN array_agg(DISTINCT pr.position) ELSE '{0}' END ) AS last_week,
COALESCE(CASE WHEN date(pr.created_at) = date(current_date - interval '3' month) THEN array_agg(DISTINCT pr.position) ELSE '{0}' END ) AS last_month,

Even if I group by key_id and date the result is not grouped.

Example of result I get now:

key_id: 1 yesterday: {1} last_week: {0} last_month: {0} created_at: '2014-04-11'
key_id: 1 yesterday: {0} last_week: {1} last_month: {0} created_at: '2014-04-10'
key_id: 1 yesterday: {0} last_week: {0} last_month: {1} created_at: '2014-04-09'

What I expect:

key_id: 1 yesterday: {1} last_week: {1} last_month: {1} created_at: '2014-04-11'

With this query I get what I want. Is there a better way of doing it?

SELECT
    k.name,
    array_agg(DISTINCT position),
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '1' day)) as y_position,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '7' day)) as last_week,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '1' month)) as last_month,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '3' month)) as three_month,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '6' month)) as six_month,
    (SELECT array_agg(DISTINCT position) FROM project_reports prk WHERE prk.project_id = pr.project_id AND prk.keyword_id = pr.keyword_id AND date(prk.created_at) = date(current_date - interval '12' month)) as last_year,
    date(pr.created_at)
FROM project_keywords pk
INNER JOIN keywords k ON pk.keyword_id = k.id
INNER JOIN project_reports pr USING (project_id, keyword_id)
GROUP BY k.name, date(pr.created_at), pr.project_id, pr.keyword_id

If I guessed the missing pieces correctly, this query using a crosstab() function should do the job (and much faster than the original - except for small cardinalities where not much can be gained to begin with):
It requires that you install the additional module tablefunc first (once per database. Detailed instructions in this related answer on SO:
PostgreSQL Crosstab Query
SELECT name, project_id, positions, created_day
      ,yesterday, last_week, last_month, three_month, six_month, last_year
FROM  (
   SELECT k.name, pr.keyword_id, pr.project_id, pr.created_at::date AS created_day
        , array_agg(DISTINCT pr.position) AS positions
   FROM   project_reports  pr
   JOIN   keywords         k  ON k.id = pr.keyword_id
   GROUP  BY k.id, pr.project_id, created_day -- k.id being the pk
   ) sub
LEFT JOIN crosstab(
    $$SELECT ARRAY[keyword_id, project_id], created_at::date, array_agg(DISTINCT position)
      FROM   project_reports
      WHERE  created_at::date IN (
                    current_date - 1
                  , current_date - 7
                  , (now() - interval '1 mon')::date
                  , (now() - interval '3 mon')::date
                  , (now() - interval '6 mon')::date
                  , (now() - interval '12 mon')::date
                  )
      GROUP  BY 1,2
      ORDER  BY 1,2$$
   ,$$VALUES
       (current_date - 1)
      ,(current_date - 7)
      ,((now() - interval '1 mon')::date)
      ,((now() - interval '3 mon')::date)
      ,((now() - interval '6 mon')::date)
      ,((now() - interval '12 mon')::date)$$
   ) AS t (
        kp int[]
      , yesterday   int[]
      , last_week   int[]
      , last_month  int[]
      , three_month int[]
      , six_month   int[]
      , last_year   int[]
      ) ON t.kp[1] = keyword_id
       AND t.kp[2] = project_id;

To break it down, a simple demo of what I am doing in the crosstab:
SELECT * FROM crosstab(
 $$SELECT *
   FROM  (
      VALUES
        ('{1,2}'::int[], current_date - 1, '{1,2}'::int[])
      , ('{1,3}'::int[], current_date - 1, '{1,2}'::int[])
      , ('{1,2}', current_date - 7, '{5,6,7}'::int[])
      , ('{1,3}', current_date - 7, '{5,6,7}'::int[])
      , ('{1,2}', (now() - interval '1 mon')::date, '{9,10}'::int[])
       -- 3 mon missing
      , ('{1,2}', (now() - interval '6 mon')::date, '{6,6,6}'::int[])
       -- 12 mon missing
      ) sub
    ORDER BY 1,2$$

,$$VALUES
    (current_date - 1)
   ,(current_date - 7)
   ,((now() - interval '1 mon')::date)
   ,((now() - interval '3 mon')::date)
   ,((now() - interval '6 mon')::date)
   ,((now() - interval '12 mon')::date)$$
) AS t (
     kp int[]
   , yesterday   int[]
   , last_week   int[]
   , last_month  int[]
   , three_month int[]
   , six_month   int[]
   , last_year   int[]
   );

Result:
  kp   | yesterday | last_week | last_month | three_month | six_month | last_year
-------+-----------+-----------+------------+-------------+-----------+-----------
 {1,2} | {1,2}     | {5,6,7}   | {9,10}     |             | {6,6,6}   |
 {1,3} | {1,2}     | {5,6,7}   |            |             |           |

The rest should be obvious.
Comments

I de-coupled the two parts and fused the plethora of correlated subqueries into a single subquery, which should be much faster. Then I use cross tabulation pull individual columns from this query. The syntax for crosstab() is the tricky part.

I am not joining to project_keywords, it seems to be just noise. I am suspecting problems in your relational model.

I seems you should include the project_id in the result to make sense. Else you should not GROUP BY it ...

You can just subtract integer from date.


Unable to change innodb_log_file_size in MariaDB Galera Cluster 10.0 I have a MariaDB Galera cluster (v. 10.0.19) with 3 nodes. I want to increase the innodb_log_file_size (i.e. the size of the InnoDB redo log files) on one of the nodes to match what it is on the other two nodes, i.e. 750M.

On this particular node, just changing the variable in the .cnf file + restarting mysql does nothing. MariaDB appears to just ignore it, and there are no warnings or errors or anything in the MariaDB error log on the node. The size of the log files is stuck at 100M.

On the other two nodes I can change the value of this variable in the .cnf file and then do "service mysql restart", and the two InnoDB log files will resize accordingly. The size agrees with the result from

  show global variables like 'innodb_log_file_size';

I'm using the same version of MariaDB Galera cluster on all 3 nodes, and the cluster and all 3 nodes are otherwise healthy as far as I can tell.

There was an issue previously with importing a backup with a large BLOB / TEXT value, and I do have the below in the error log, although this is a while ago now:

160408 15:09:55 [ERROR] InnoDB: The total blob data length (60192222) is greater than 10% of the total redo log size (209715200). Please increase total redo log size.
160408 15:09:56 [ERROR] Slave SQL: Could not execute Write_rows_v1 event on table confluencedev.PLUGINDATA; The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size., Error_code: 1118; Got error 139 "Too big row" from storage engine InnoDB, Error_code: 1030; handler error HA_ERR_TO_BIG_ROW; the event's master log FIRST, end_log_pos 60193176, Internal MariaDB error code: 1118
160408 15:09:56 [Warning] WSREP: RBR event 2 Write_rows_v1 apply warning: 139, 286800
160408 15:09:56 [Warning] WSREP: Failed to apply app buffer: seqno: 286800, status: 1
     at galera/src/trx_handle.cpp:apply():351
Retrying 2th time

And then:

160408 15:09:57 [ERROR] WSREP: Failed to apply trx 286800 4 times
160408 15:09:57 [ERROR] WSREP: Node consistency compromized, aborting...

The node died and it was resurrected with SST. During this process I got this:

160419  9:56:46 [Note] InnoDB: The log sequence numbers 15287730891 and 15287730891 in ibdata files do not match the log sequence number 25623829121 in the ib_logfiles!
160419  9:56:46 [Note] InnoDB: Database was not shutdown normally!
160419  9:56:46 [Note] InnoDB: Starting crash recovery.
160419  9:56:46 [Note] InnoDB: Reading tablespace information from the .ibd files...
160419  9:56:48 [Note] InnoDB: Restoring possible half-written data pages
160419  9:56:48 [Note] InnoDB: from the doublewrite buffer...
160419  9:56:49 [Warning] InnoDB: Resizing redo log from 2*48000 to 2*6400 pages, LSN=25623829121
160419  9:56:49 [Warning] InnoDB: Starting to delete and rewrite log files.
160419  9:56:49 [Note] InnoDB: Setting log file ./ib_logfile101 size to 100 MB
InnoDB: Progress in MB: 100
160419  9:56:50 [Note] InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Progress in MB: 100
160419  9:56:51 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
160419  9:56:51 [Warning] InnoDB: New log files created, LSN=25623829516
160419  9:56:51 [Note] InnoDB: 128 rollback segment(s) are active.
160419  9:56:51 [Note] InnoDB: Waiting for purge to start

innodb_log_file_size is stuck at 100M:

MariaDB [(none)]> show global variables like 'innodb_log_file_size';
+----------------------+-----------+
| Variable_name        | Value     |
+----------------------+-----------+
| innodb_log_file_size | 104857600 |
+----------------------+-----------+
1 row in set (0.00 sec)

Yet /etc/my.cnf.d/server.cnf defines the value as 750M:

  [mariadb-10.0]
  ...
  innodb_log_file_size=750M

What am I missing?

Edited to add: I've found that I'm able to force the innodb_log_file_size by giving it as a parameter on the command line:

sudo /sbin/service mysql start --innodb-log-file-size=786432000

In my experience setting up MariaDB 10.1 cluster, this behavior happens due to an override cnf file.

I had the same problem, that changing a setting in my.cnf and restarting mysql but the change was ignored. I found on ubuntu there is /etc/mysql/conf.d/mariadb-enterprise.cnf which overrides many settings. I comment out in the override file the value I want to change and then the value I put in my.cnf is used.


How to tell if there are currently any long running queries executing? (Related to this question)

If I want to see if there are currently any long-running queries executing on my database, what's the easiest way of checking?  (I expect the answer will be some query on master..sysprocesses?)

WhoIsActive from Adam Machanic is an excellent tool/sp to see what's currently happening on your server.

The below tutorial from Brent Ozar shows how to periodically log sp_whoisactive to a table for later analysis.

https://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/


Site running with mysql replication, shall we shut down the site when replication broken? let me illustrate the situation here:
An online store is setup with two replicated server: A(Asia) and G(Germany)

The setup is aimed to improve speed for users from both location.

On apache side, file replication is setup to sync files on both site.
DB replication with MYSQL is setup to sync data.

Here comes to problem when DB replication is broken.
during replication is down
Item X , available quantity is 2

Mr G order one item from G

Mr A order two item from A.

At the point, the   remain quantity should be -1

However, because of the broken replication, both users ordered successfully.

Can anybody advise if this is the case and people should shut down related application once the DB replication behind broken?

Replication may fail because of different factors. You don't want to shot down the site each time replication fails. Instead, you should think of a solution for this situation: when replication fails, how to keep the site up.

It seems that you have master-master setup for the replication. I suggest to let users (application) connect to the closer server when they are reading, and perform all writing operations on one master, call it "active master". This way, you gain the speed of reading, and you know that the replication is going in one direction. If the replication fails, you fix it while the site is still up.

On the other hand, if the master fails, you switch masters: pending master becomes the active master, and you fix the failed one, then resume the replication.

Having said that, there are some situations where you may want to shut down the site.

HTH


Is replication a suitable method to move a SQL Server database to new hardware? I need to move a live database with about 100 GB of data to a new machine. As I understand the easiest way to do that would be to take it offline, move the files and bring it back online.

But: The old server is connected via a 200 mbit uplink only, which means it would take me more than 1 hour to move the whole thing. That's too long to be down for my situation so I'm wondering if it might be suitable to just set up transactional replication and on day x take the old one offline, change my DNS records to the new server and make that one the new writeable master.

Can I be sure that all my table settings, views, etc. are properly copied to the new machine?

Or is there another suitable way to avoid longer downtimes when moving databases?

Another option (similar to what happens in replication but more manual but less setup) is to do a FULL backup, restore over the network (with NORECOVERY) and continuously do Transactio Log backups with automatic restores. This would cut your downtime to only the last Tranlog Restore...


SQL Parallel Stored Procedure execution using CLR - Performance We have a main analytics SP that calls 100 other sub SPs, all of which act upon the same set of data (Claims), check it for some business rules, and output discrepancies.

The SPs need not all be run sequentially, they can be broken out into sections, which are dependent on previous sections (sequential), but independent within the section (parallel).

After looking at many options like Service Broker, Agent Jobs, Batch Files, SSIS etc., I used this CLR code to parallelize the sections, and it gave great performance improvement.

However, when I run multiple (5, 10, 15) main SPs concurrently (each of which analyzes different claims) , performance starts to taper as concurrency increases. I guess this is because of the overhead of creating multiple parallel threads through the CLR. I also see lot of XTP_THREAD_POOL sessions idle in sp_who2.

Has anyone used CLR for parallelizing Stored Procedures in Critical OLTP Production workloads ?
Are there any best practices for performance tuning SQL CLR ?
Is there a threshold for number of parallel threads that can be opened before the overhead makes things worse ?
If my system has 20 cores, does it mean creating > 20 parallel threads does not help ?

Has anyone used CLR for parallelizing Stored Procedures in Critical OLTP Production workloads ?

Parallelizing? Yes. For "critical OLTP Production" systems, not really. This is not to say that you can't do it, but you are entering rather dangerous territory here given that this goes beyond the typical caution of accessing external resources (usually web services) that might not return quickly and hence might "hijack" a thread until SQL Server forces that thread to yield.

  Is there a threshold for number of parallel threads that can be opened before the overhead makes things worse ?

I do not believe so. Also, I am not sure that the thread count by itself is the overriding issue here. Have you also checked for blocking as you increase the execution count? More thoughts below..

  However, when I run multiple (5, 10, 15) main SPs concurrently (each of which analyzes different claims) , performance starts to taper as concurrency increases.

So, here are some points to consider:

SQLCLR aside, isn't what you are seeing, to a degree, merely the nature of resource management / concurrency? As you start to increase concurrency you see a gain, then at some point the amount of each additional gain decreases until it becomes 0, and then increasing concurrency further actually has the opposite effect? (i.e. economies of scale / diseconomies of scale)

Yes, the additional operations are being done on different sets of rows, but each thread still requires its own connection / session. Have you checked the connection count in sys.dm_exec_connections? I'm not sure how many cores you have, but there is a DMV, sys.dm_os_schedulers, that shows the visible scheduler count and that is how many active query threads you can have at any given point in time. If, let's say, you have 32 schedulers, then it won't process 100 threads faster than 50.

Execute the following query to get the details, and review the documentation for the DMV, linked above, to better understand the values being returned by the various fields:

SELECT * FROM sys.dm_os_schedulers WHERE [status] = N'VISIBLE ONLINE';

As you exec that query while your process is running, how do the values change for the various "task" and "worker" count fields, especially as you increase the number of concurrent executions of the SQLCLR stored procedure?
Since you cannot use the Context Connection with multithreading, you are making regular / external connections. That would imply that the SQLCLR stored procedure does not need to be local (well, depending on how it is being used in this process). It might be worth exploring moving this SQLCLR stored procedure to a different server where it wouldn't be competing for threads. Adding to the point made above about only having so many threads to use, one needs to consider that each thread you request is really taking 2 from the CPU, right? One thread in .NET and one thread in SQL Server. Of course, looking at that Code Project page, I don't see any way to pass in a Connection String, so maybe it is just connecting to the (local) instance? (I don't have time at the moment to sign up just to look at the source code).
One thing I noticed while looking at the usage description on that Code Project page was that it seems to store properties in static variables, given that separate execution of stored procedures are able to maintain state between those executions. While it is possible that the Parallel_Declare proc could be used to manage the separation callers, SQLCLR uses a shared App Domain model: App Domains are created per each DB / Assembly owner combination. If you are using the same stored proc for all of this, then that is running in the same App Domain and ALL sessions are sharing the same memory and hence the same static variables. Are you sure that multiple executions of the same stored procedure with different SQL isn't overwriting concurrent executions that have already been started?

While it might be coded properly to handle this, you might want to consider creating separate databases just to hold copies of this Assembly to run each separate set of executions. Who knows, you might also get better thread management out of it as well. This is probably necessary whether you run it locally or on a remote instance.
Finally, given the limited description of the operation, have you considered implementing this setup in your own .NET app? You could write a console application that handles the multi-threading and handles the workflow as you need it to be: multiple, synchronous sections of asynchronous calls. This would remove the various risks you currently face trying to do this within SQLCLR, while ensuring that you can run it remotely. Then, you can run multiple instances of the console app, each to work on different sets of claims, without worrying about shared memory (as each instance of the console app would have its own App Domain) and without taking any threads away from SQL Server to manage it.