Two master databases for two separate apps

MULTIMASTER SETUP
I would recommend a multimaster setup only with the idea that you restrict Asia's data to one master, and Europe's data to the other master. As for the shared databases, make sure auto_increment_increment and auto_increment_offset are in full use. In addition, make sure you only write Asia's data to the shared databases at the Asian DC and likewise with Europe.
STORAGE ENGINE CONSIDERATIONS
If there is no specfic reason to keep MyISAM (such as FULLTEXT indexing or heavy volume reads), I would recommend converting everything to InnoDB. That way, if a server crashes, then crash recovery can bring databases to a consistent state. With MyISAM, tables can be left in a crashed state and repairing those tables may result in the disappearence of one or more rows of data.
HIGH AVAILABILITY
You should strengthened your HA Setup by implementing DRBD. DRBD should be setup as at each DC. What are the benefits?

You maintain a disk-level replica of the mysql data within both data centers
You can have automatic failover within each data center
You only worry about MySQL Replication between DCs

REPLICATION
To minimize data loss with MySQL Replication over geographic distance, I would recommend using smaller binary logs. By default, a binary log/relaylog is 1G (max_binlog_size and max_relay_log_size). You can make these values much smaller. I got this idea from PostgreSQL because WAL files are 16M by default. This creates more log files, but those log files will be closed and complete faster on the acting slave. If you are not using it, you should upgrade to MySQL 5.5 because it has Semisynchronous Replication. It can be tuned to detect heartbeat timeouts with some granularity.
I wrote about some of these concepts in an earlier post back on March 29, 2011.


Overview of leaked/unclosed cursors/statements on DB2 LUW server?

SELECT
  application_handle
, uow_id, activity_id, activity_type, activity_state
, timestampdiff(2, char(current timestamp - entry_time)) elapsed_sec
, stmt_text
FROM TABLE(MON_GET_ACTIVITY(null, -2))
order by elapsed_sec desc;

MON_GET_ACTIVITY table function - Return a list of activities


An unexpected token in TDS indicates a bad network packet was sent to the TCP Port Sybase is listening to.

This indicates one of two things:

an intermittent network connectivity problem where TCP TDS packets are being corrupted
you have some process attempting to connect to the Sybase instance without using TDS.

I've seen #2 happen with security scanning processes that are looking for open ports.

From your symptoms it sounds like a network connectivity issue.  Since the Sybase Anywhere server is not on the same network segment as your client machine, I'd tend to think the client needs to gracefully handle connectivity problems.


Facing error while trying to create a function using 'ACCESSIBLE BY' clause in PL/SQL

ACCESSIBLE BY is a 12c feature. Based on your previous questions, you have a 11g database.


Where mysql writes its log seqeunce numbers and last checkpoint at?

Well, that is handled in memory and transactionally stored on disk on the transaction log (by default, the ib_logfile0 and i_blogfile1. But if you just want to know its value, you can check SHOW ENGINE INNODB STATUS or simply:

mysql> SHOW GLOBAL STATUS like '%lsn%';
+----------------------------+-------------+
| Variable_name              | Value       |
+----------------------------+-------------+
| Innodb_lsn_current         | 25808994907 |
| Innodb_lsn_flushed         | 25808994907 |
| Innodb_lsn_last_checkpoint | 25808994907 |
+----------------------------+-------------+
3 rows in set (0.00 sec)

If you want MySQL to output that periodically to its error log, you can enable one of the InnoDB monitors.


For those who find this later in life..

This was a catastrophic mistake. There is no way to fix this problem. A RESET MASTER / CHANGE MASTER TO must be performed farm wide. Why is it so catastrophic? Because, in a Multi-Master circular replication, ALL the MySQL servers need to have the RESET MASTER / CHANGE MASTER TO performed.

In MySQL 5.6, the GTID sets are stored in the master BINLOG files - hence why the RESET MASTER must be done. However, when you do a RESET MASTER the command also resets the servers last used GTID to 0. Now the slaves have GTID's larger than the master server. The slave will skip transactions until the GTID exceeds the slaves GTID_EXECUTED_SET value for the master server. That is why you have to perform RESET MASTER / CHANGE MASTER TO on all the slave servers too.

After performing the RESET MASTER / CHANGE MASTER TO we should only need to dump/restore the databases that were being updated on the affected master server (aka master databases) so that they slaves can be in sync.


MySQL query took extremely long time to finish and it was in 'Sending data' state

the combination of answer_id and insert_time is unique. I already created that index for the combination

Create this UNIQUE index on destination table (zhihu_answer_meta_reconcile).
Perform the action you need with 2 separate queries:

-- TRUNCATE `zhihu_answer_meta_reconcile`;
INSERT INTO `zhihu_answer_meta_reconcile`
    SELECT * FROM `zhihu_answer_meta_staging`;
INSERT IGNORE INTO `zhihu_answer_meta_reconcile`
    SELECT * FROM `zhihu_answer_meta`;

These queries may be combined into one line. While using from some code check that the inserting function/method allows multi-query in that case.


Oracle table transfer between databases in two different hosts

Use the SneakerNet

The SneakerNet may be old, but it is still useful for some extreme cases like yours.

Buy a 1 TB hard drive
Create a Transportable Table Space (TTS) or Dump file (expdp) on the HD
Manually transfer the hard drive.

USPS/UPS/FedEx also works

Load it onto remote server

EDIT

The below section was added due to the comment from the original poster.

Still want to use your internet to transfer?

Increase the bandwidth at both sites.

This is extremely location specific.  If you can, this would be recommended.
I believe 4G LTE (cell phones) can transfer at rates that are faster than 10 Mbps.

Maybe, you are at some extreme location that makes SneakNet unviable (eg 10 day hike in the amazon).  Or, that is the fastest speed available for any ISP.

Extreme example: you are trying to transfer 500GB of data from Opportunity and its modem has a transfer rate of 10Mbps.

I'm assuming you have some sort of restriction due to your terrestrial position.

Data can be made READ ONLY

The READ ONLY requirement is to ensure that the data does not change over the 5 - 10 days it will take to copy everything

Create a database link
Use DBMS_PARALLEL_EXECUTE (DPE) with a PARALLEL_LEVEL => 1

The SQL statement you will use is a simple INSERT ... SELECT statement. (with the appropriate WHERE clause for DPE)

Your bandwith is going to be maxed out for those 5+ days.

You will need to adjust the chunk size to match some personal preference for "ok, I can wait x minutes to redo this CHUNK that failed".

DPE is used to help you easily manage those failed chunks.

Table is part of an OLTP

In this scenario, the table is guaranteed to change throughout the 10+ days (2* calculated) to transfer the data.

Your OS of choice could be your blessing or your curse.

Also, you will need a lot of disk space.  A lot more than 500GB (more like 1.5TB to 2TB).

Similar to DPE, you break up the data into smaller, more manageable chunks.  However, you need to do all of the work.

Steps involved

create expdp file (oracle documentation)
compress it. (OS specific)

this might reduce your data. Your Millage May Vary (YMMV)

split file into multiple chunks (OS Specific)
gather an md5/sha-256 checksum on all of the pieces (OS Specific)
ftp each chunk (OS Specific)

restart the FTP as necessary
make sure you are ready to wait 5+ days.
if internet connection is unreliable, write a shell script to automate the process (OS Specific)
you are probably using a shared connection.  Plan for 10+ days

gather the md5/sha-256 checksum for all of the pieces on the remote site (OS Specific)
compare (OS Specific)

resend missing pieces that are missing
resend pieces whose MD5/sha-256 checksum do not match.
you did put the FTP in BINARY mode first.  right?

join the files together (OS Specific)
uncompress the file(OS Specific)
impdp the file (oracle documentation)

The low end estimate of 115 hours is based on the given uncompressed data size.  If your data can be compressed, you can see significant reduction in size. Your mileage may vary (YMMV).


Postgres Point-In-Time Recovery with a limited point in time

There isn't a magic way of doing that, the options I see is:

At a defined interval, do another basebackup and remove old backups. You can use pg_archivecleanup contrib to remove the old archives (read the contents of backup_label file contained in your basebackup to check which files each basebackup needs). This is a broadly used approach, and recommended for its simplicity.
Another approach is to extract the basebackup into a directory, add a recovery.conf file and start PostgreSQL to consume the archives.

Following an example of recovery.conf to achieve that:

# restore the archives
restore_command = 'cp /path/to/archives/%f %p'
# remove archives already restored (you must install pg_archivecleanup contrib)
archive_cleanup_command = 'pg_archivecleanup /path/to/archives %r'
# select a target time of last month (you must create this by hand)
recovery_target_time = '2014-01-10 00:00'
# make PostgreSQL pause when it reaches the target time
pause_at_recovery_target = true
# make the PostgreSQL stop itself
recovery_end_command = 'pg_ctl -w -D /path/to/data stop -mf'

The problem is that you'll need to do it all by hand. I had no time to test it myself, but you can try and see if the following shell script works for such thing:

#!/bin/sh
# create data dir
mkdir -p /path/to/data
cd /path/to/data
# extract base backup
tar xvf /path/to/base1.tar.gz
# create recovery.conf (as the above)
cat > recovery.conf <<EOL
    restore_command = 'cp /path/to/archives/%f %p'
    archive_cleanup_command = 'pg_archivecleanup /path/to/archives %r'
    recovery_target_time = '`date -d '1 month ago'`'
    pause_at_recovery_target = true
    recovery_end_command = 'pg_ctl -w -D /path/to/data stop -mf'
EOL
# start PostgreSQL
pg_ctl -D . start

You can also use the recovery_end_command to recreate the .tar.gz file or just keep the backup as a directory.

Sorry for the not tested solution, but you can try it and I can edit if any mistake is found.


If you are willing to just handle cases where the number of single quotes is 15 or less, you can use this pattern:

update contacts
    set address1 =    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(address1,'''''''''''''''''',''),  -- Replace any instance of eight quotes
                                '''''''''',''),                           -- Replace any instance of four quotes
                            '''''',''),                                   -- Replace any instance of two quotes
                        '''','')                                          -- Replace any single quote
where address1 like '%''''%'

If someone has entered more than 15 quotes into one field in sequence, you probably need to verify if they accidentally died while holding down the key.


Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record

ok I think I got it. I was able to get the results I was looking for using the following code, but it is slow when executing it to get the results. One thing I forget, was the location column, so I was sure to account for this in my code. I did end up breaking the uid and instance into two separate fields, but I know I could have merged them into one. I figured this would give me greater flexibility when pulling data against it in the future. Feel free to improve, I know I would appreciate it. I'm also happy to be posting my first answer. Thanks!

ALTER PROCEDURE [dbo].[ProjectTracking]
    AS
    BEGIN
        -- Insert statements for procedure here
    SET NOCOUNT ON;
    DECLARE @max int
    DECLARE @i int
    Declare @tinstance int
    Declare @tuid varchar(20)
    DECLARE @puid varchar(20)
    Declare @pinstance int
    DECLARE @pid int
    DECLARE @pproject varchar(10)
    DECLARE @plocation varchar(10)
    DECLARE @pstart datetime
    DECLARE @pend datetime
    DECLARE @cuid varchar(20)
    Declare @crecord int
    DECLARE @cid int
    DECLARE @cproject varchar(10)
    DECLARE @clocation varchar(10)
    DECLARE @cstart datetime
    DECLARE @cend datetime

    select @max = count([Record])+1 from [ProjetHistory]
    print @max

    -- Insert the first record so there will be a previous record for the first cycle of while
    SELECT * INTO #PTEMP
    FROM (SELECT CONVERT(varchar(15),[id])+[project]+rtrim([location]) as Uid,1 as instance, [id],[project],
                        [location],[Start],[End]
      FROM [ProjectHistory]
      WHERE [RECORD] = 1) As x

    select @tuid = [Uid],@tinstance = [instance] from #PTEMP where [instance] = 1
    set @i = 1

    While @i < @max
    BEGIN --Begin while
        SELECT @puid=[uid],@pinstance = [instance], @pid = [id],@pproject = [project],
                        @plocation = [location], @pstart=[ProjectStart], @pend = [ProjectEnd] FROM #ptemp
        Where [instance] = @tinstance and [uid]=@tuid
        Select @crecord =[record], @cid = [id], @clocation = [location], @cproject = [project], @cstart = [Start], @cend=[End]
        from [ProjectHistory] where [record] = @i
            SET @cuid = CONVERT(varchar(15), @cid)+@cproject+@clocation
        IF @pid = @cid
        Begin --First check if its the project instance
            IF(@cuid = @puid and DATEDIFF(day,@cstart,@pend)<=7)
            Begin --this happens if its the same project period
                update #PTEMP set [ProjectStart] = @cstart, [ProjectEnd] = @cend where [Uid] = @puid and [instance] = @pinstance
                set @i=@i+1
                set @tinstance = @pinstance
                set @tuid = @puid
            END -- done with updating existing project instance
            ELSE
            BEGIN  -- Since it didnt match pervious record must be new project instance
                select @tinstance = COUNT([instance])+1 from #ptemp where [uid] = @cuid
                insert into #PTEMP ( uid, instance,id, project, location, ProjectStart, ProjectEnd)
                values(@cuid,@tinstance,@cid,@cproject,@clocation, @cstart,@cend)
                set @i=@i+1
                set @tuid = @cuid
            End -- Done with the new project instance for same id
        END -- Done with same id
        ELSE -- this is a new id so insert uid for id project combo
        begin -- new id so need to insert first record
            select @tinstance = COUNT([instance])+1 from #ptemp where [uid] = @cuid
            insert into #PTEMP ( uid, instance,id, project, location, ProjectStart, ProjectEnd)
            values(@cuid,@tinstance,@cid,@cproject,@clocation, @cstart,@cend)
            set @i=@i+1
            set @tuid = @cuid
        END -- Done inserting new record for next id
    End -- End While

    select * from #ptemp

    drop table #PTEMP

    END


You can use a generated column in your CREATE TABLE statement to always have an up-to-date value:

CREATE TABLE TestTable(
    id INT AUTO_INCREMENT PRIMARY KEY,
    col1 INT NOT NULL,
    col2 INT NOT NULL,
    col3 INT AS (col1 - col2)
);

The column can be virtual or stored (virtual is default) and each has their own use cases as detailed in the link above.

Note: only applicable for MySQL 5.7.6 & higher.


How should I design a relationship table for friendship?

I would store AB and BA.  A friendship is really a two-way relationship, each entity is linked to another.  Even though intuitively we think of the "friendship" as one link between two people, from a relational point of view it is more like "A has a friend B" and "B has a friend A".  Two relationships, two records.


you can use instr function to get the position of last /
-1 = you are looking from the end of the string
than you add 1, so you start position will be the first number after /
you do not need the third parameter in substr if you need substring till the end of a string

select substr(Emp_Num_ID,instr(Emp_Num_ID,'/',-1)+1) as Emp_ID from Employee_M


How to change a MySQL previous slave to be a master and remove slave status information?

In MySQL 5.5.16 and later, you can use RESET SLAVE ALL to do all that RESET SLAVE does and reset the connection parameters from the memory, this way it doesn't require a mysqld restart.


Rather than two steps you could define the initial values using a DEFAULT constraint on the new column.


Does SQL Server Clustering Enhance Performance?

SQL Server cluster is a fault tolerance solution it has nothing to do with performance.

If you want your load on sql server to be shared , you should be looking at Replication or maybe log shipping, direct reporting calls to that replicated /log shipped server and keep application calls to the main server.

If your data gets too large that no matter what you do, performance is still a problem, consider archiving data to a data warehouse, in your application database (OLTP database) only keep recent records (last 6 months).  Anything older than 6 months gets shipped to your data warehouse (OLAP database).

The moral of the story is clustering your server isn’t going to give you any performance boost, consider an alternative option.


impdp syntax for schema that is not available on other database

impdp SCOTT/password directory=DUMPDIR dumpfile=fromuser.dmp logfile=impdp.log remap_schema=SCOTT:DEV_LOCAL FULL=YES

DEV_LOCAL will be the local empty schema that you should create!


For this answer, let's use /root/MySQLData.sql as the SQL File.

Go into mysql via the client and run it like this:

mysql> source /root/MySQLData.sql

Give it a Try !!!

UPDATE 2011-12-16 21:08 EDT

I just caught on to your problem: Your program is not running because you did not supply the password. It is stuck in the background waiting for a password. That's your actual problem. Kill the job and run it again with the password on the command line:

mysql -u root -ppassword DB_NAME < .SQL FILE

or just use my original submitted answer up above.

UPDATE 2011-12-19 11:40 EDT

I just re-read the question. You said single table. Then it hit me !!!

You need to ramp up your bulk_insert_buffer_size. The default is 8M.

To see what your current setting is, run this:

mysql> show variables like 'bulk%';

Just add this to /etc/my.cnf

[mysqld]
bulk_insert_buffer_size=512M

So as to not restart mysql, run this SQL command:

SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;

Give it a Try !!!


Windows 10, SQLServer Express (2019), Excel 2016 import to SQLServer error Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

Microsoft SQL Server Management Studio launched the 32-bit version of the import wizard.
I found the 64-bit version of the import wizard already installed as a standalone App. Launching that standalone App worked.


It appears you are putting the foreign key on the wrong table.  The column PersonID on the Jobs table should reference the PersonID on the Employee table.

If you are assigning persons to jobs, then your original foreign key should be on JobId.  This would require adding the JobId to the Employees table and removing the PersonId from the Jobs table.

If Employees can have many Jobs and Jobs can have more than one Employee, then you will want a join table.  It will have foreign keys to both the Jobs and Employees table.  The primary key of the join table will include both the PersonId and JobId.


Relaying changes to a slave from another master with Tungsten on MySQL

I don't have direct experience with a similar setup, but you probably need to set this option on the master services:

--log-slave-updates           Should slaves log updates to binlog [false]

By default, changes made by appliers are not written to the binlog. As Tungsten watches the binlog to detect changes, the master extractor will not see those changes made by appliers, unless you specify the options above.


MS-SQL stored procedure to insert into one-to-many-to-many relationship

You can use the OUTPUT clause of the INSERT to capture the identity values that were generated like so:

DECLARE @output TABLE (
   line_id int,
   qty int,
   descript nvarchar(100),
   req_id int
);

INSERT INTO dbo.reqitems
OUTPUT INSERTED.line_id, INSERTED.qty, INSERTED.descript, INSERTED.req_id
  INTO @output
SELECT qty
    , descript
    , @req_id
FROM @lines;

Then you can use the @output table variable to perform the insert into your third table:

INSERT itemcharge
SELECT /* whatever values you need go here */
  FROM @output;

OUTPUT Clause (Transact-SQL)


Logical page error while attempting to insert data

I think you have a database corruption so try the command

DBCC CHECKDB

And The command

 dbcc checktable ( 'dbo.TableName', REPAIR_REBUILD )

Before that Refer to BOL to see about the commands .


One scenario in which this would fail depends on how busy (or unlucky) you are in timing.

Session A does the select on the value, sees that it should INSERT
Session B does a select on the value before session A does the required insert or update of the value constraint
Session A inserts row, updates value
Session B inserts row, updates value

It's all a matter of timing (as suggested by your 98% works). You could acquire a WRITE LOCK for Session A on the table you are inserting AND on the table you are updating the value, which will block Session B until Session A releases the lock.

Alternatively, you could go ahead and let the DB handle the locking automatically by creating a UNIQUE INDEX and issuing a INSERT .. ON DUPLICATE UPDATE (recommended)


How to recover/restore corrupted Innodb data files?

Check the MySQL error log.  It looks like trying to read table 44 is actually crashing the server.

"Lost connection to MySQL server during query" is (or should be) heart-stopping time for a MySQL DBA because it often means that whatever your query just did has actually crashed the server.

The subsequent messages seem to bear this out:

mysqldump: Got error: 2003: Can't connect to MySQL server on 'localhost' (10061) when
trying to connect
Operation failed with exitcode 2

A quick test of mysqldump on Windows 7 against a MySQL server that is not running returns exactly that same error and exits with %ERRORLEVEL% set to 2.  The nature of error 10061 can be found with the perror utility.

C:\>perror 10061
Win32 error code 10061: No connection could be made because the target machine actively refused it.

...so, check the MySQL error log.  What's likely happening is that you're hitting corruption severe enough that in spite of innodb_force_recovery, the MySQL Server is crashing.  I will then usually restart on its own, but this takes time so may not complete before mysqldump gets an IP connection refused on the subsequent connection attempts.  This should all show up in the error log.

  I assume that when the dump does not yield any error, the table's data is non-corrupted.

Incorrect assumption.  If the dump does not yield any error, it could also mean that that innodb_force_recovery allowed the server to retrieve some of the data.  A "successful" dump of a table only means it's not corrupted so severely that innodb_force_recovery can't survive it.

  1 (SRV_FORCE_IGNORE_CORRUPT)

  Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

  http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

Again, you should see chatter from InnoDB in the MySQL error log.

Final suggestion, don't use workbench.  Use the mysql command line client to explore the server schemata and use mysqldump directly from the command line to try to restore individual schemas or individual databases.

update/additional:

  how do you recover stored procedures? I get an error because they access corrupt tables

It shouldn't be because the procedures access corrupt tables, because stored procedures and functions aren't validated against their referenced tables in the way views are (drop a table that a view references, and then SHOW CREATE VIEW doesn't work -- the same isn't the case with procs or functions)... MySQL doesn't care whether tables referenced by stored procedures and functions even exist (except at runtime)... however, unlike view definitions, which are stored in individual files, the definitions of procedures and functions are stored in the proc table in the mysql schema, which is a MyISAM table... so I would expect that you'd see error messages referencing that table and CHECK TABLE mysql.proc; and REPAIR TABLE mysql.proc; might get you going again.  Note that the version of MySQL Server that you're using for your recovery needs to be the same release series (e.g. 5.5) or the table definition of mysql.proc will be wrong... that shouldn't be the issue here, since if I read the question correctly, the crashed server and the recovery server are both 5.5.31.

If you can SELECT * FROM mysql.proc then you can retrieve the definitions that way if for some reason the SHOW CREATE PROCEDURE statement doesn't work.

  And what options for a (partial) rescue are there for the tables that yielded an error in the dumps?

It depends on the nature and scope of the errors encountered.  One option, discussed here, involves repeated INSERT ... SELECT ... LIMIT ... OFFSET, extracting blocks of rows from readable pages into a MyISAM table (because you can't write to an InnoDB table while innodb_force_recovery is on).  There's also the Percona Data Recovery Tool for InnoDB, though I have never had occasion to use it.


postgresql v8.4 function runs but doesn't work

When trying the original function code under Postgres 9.3, it would not compile.  I had to change 2 minor things, which I have commented and labelled 1 and 2.  Testing the function with several small tables worked like a charm - I have used Postgres 9.3 and 8.4 (see dbfiddle).
Example code:

create table a_2017 (id int primary key, start int);
create index a_2017_start_idx on a_2017 using btree(start) ;
create table b_2017 (id int primary key, start int);
create index b_2017_start_idx on b_2017 using btree(start) ;
create table c_2017 (id int primary key, start int);
create index c_2017_start_idx on c_2017 using btree(start) ;
create table d_2017 (id int primary key, start int);
create index d_2017_start_idx on d_2017 using btree(start) ;
create table e_2017 (id int primary key, start int);
create index e_2017_start_idx on e_2017 using btree(start) ;

insert into a_2017 values (1,1),(2,2),(3,3),(4,4),(5,5);
insert into b_2017 values (11,11),(22,22),(33,33),(44,44),(55,55);
insert into c_2017 values (111,111),(222,222),(333,333),(444,444),(555,555);
insert into d_2017 values (1111,1111),(2222,2222),(3333,3333),(4444,4444),(5555,5555);
insert into e_2017 values (11111,11111),(22222,22222),(33333,33333),(44444,44444),(55555,55555);

Function: 1 returns (instead of: return), 2 no semicolon.  select in round brackets.

create or replace function update_2017()
returns void -- <--_1_
as $$
DECLARE
  row RECORD;
BEGIN
   FOR row IN (
     SELECT tablename
     FROM pg_tables
     WHERE schemaname = 'public' and tablename like '%_2017%'
     order by tablename asc ) -- <--_2_
   LOOP
      RAISE LOG '-> removing index for %', row.tablename;
      -- remove indices
      EXECUTE 'DROP INDEX ' || row.tablename || '_start_idx;';

      RAISE LOG '--> starting update on %', row.tablename;
      -- update table
      EXECUTE 'UPDATE ' || row.tablename || ' set start = start*1000;';

      RAISE LOG '---> restoring index on %', row.tablename;
      -- put indices back
      EXECUTE 'CREATE INDEX ' || row.tablename || '_start_idx on ' || row.tablename || ' USING btree(start);';
   END LOOP;
   RETURN;
END; $$
LANGUAGE plpgsql;

Then, for testing the function:

select update_2017();

select * from a_2017
union
select * from b_2017
union
select * from c_2017
union
select * from d_2017
union
select * from e_2017
;

-- output: see last section of the dbfiddle.

With bigger tables (one million rows), the execution of the function was slightly slower, but worked nonetheless.  Maybe you should have another shot at this ...


How to split a string to and get info from another table in mysql

Bascially yes, there's a way to do that.

Here's a select statement that gets you each sentence with it's score

SELECT
PARSED_SENTENCES.sentence, SUM(COALESCE(WordScores.score, 0)) score
FROM (
     SELECT sentence,
     SUBSTRING(
          sentence
          FROM CASE
               WHEN INDEX_TABLE.POS = 1 THEN 1
               ELSE INDEX_TABLE.POS + 1
               END
          FOR CASE LOCATE(' ', sentence, INDEX_TABLE.POS + 1)
              WHEN 0 THEN CHARACTER_LENGTH(sentence) + 1
              ELSE LOCATE(' ', sentence, INDEX_TABLE.POS + 1)
              END
              - CASE
                WHEN INDEX_TABLE.POS = 1 THEN 1
                ELSE INDEX_TABLE.POS + 1
                END
     ) AS word
     FROM SentenceScores
     INNER JOIN (
          SELECT @rownum:=@rownum+1 POS
          FROM (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) a, (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) b, (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) c, (SELECT @rownum:=0) r
     ) INDEX_TABLE
     ON INDEX_TABLE.POS <= CHAR_LENGTH(SentenceScores.sentence)
     AND (
          INDEX_TABLE.POS = 1
          OR SUBSTRING(SentenceScores.sentence FROM INDEX_TABLE.POS FOR 1) = ' '
     )
) AS PARSED_SENTENCES
LEFT OUTER JOIN WordScores
ON PARSED_SENTENCES.word = WordScores.word
GROUP BY PARSED_SENTENCES.sentence;

You should be able to convert that to an update statement so that you could calculate the scores and apply them to the table at the same time.

Per the comment discussion below, if you wanted to replace the numbers-table subquery with a static numbers table you could replace this part of the query:

 INNER JOIN (
      SELECT @rownum:=@rownum+1 POS
      FROM (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) a, (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) b, (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) c, (SELECT @rownum:=0) r
 ) INDEX_TABLE

with just:

INNER JOIN INDEX_TABLE

where your numbers table and number column are assumed to have the same names as in the subquery, i.e. INDEX_TABLE and POS


MySQL hang during "show collation" on Amazon RDS

Not sure if you found a solution to this but for us it seems to be related to the size of data leaving the VPC.

If you run a query with a small result set:

show collation like '%utf8_swedish%';

The result returns fine, but if you take away the like and get a large result set back it fails. We're still investigating the problem but not sure you found a solution.


gcache.page_sizes and gcache.size are set at the same time. When will gcache.pagebe used?

The gcache is a ringbuffer and the size of it is set with gcache.size and write-sets are appended to this file and the file can be reused.

If the transaction write-set is large enough not to fit in the the gcache ringbuffer then an physical disk file, a page, is allocated to cache the write-sets. Note that there are two types of pages:

A page with a standard size as defined by gcache.page_size (default=128M as above).
A page with non-standard page size is created if the transaction is large enough not to fit into a standard page. With a gcache.page_size of 1M and transaction write-set of 1.5M will result in a separate page (in turn on-disk file) with a size of 1.5M.


PostgreSQL 11: how to partition 1000 tenants

Partitioning is mostly about speeding up deletes and sequential scans.

If your biggest pain is the big deletes by account_id, list partitioning on that column would be the best solution.
If your biggest pain is getting rid of all data older than a certain date, range partitioning by time would be the solution.
If you have may queries that have to perform sequential scans of large tables, but have a WHERE condition on account_id or a time constraint, partitioning on that WHERE condition would allow PostgreSQL to perform the sequential scan only on some of the partitions.

You have to decide if any of these things is painful enough for you to consider partitioning. Partitioning doesn't come for free: it will increase query planning time and sometimes execution time as well.

Partitioning won't make index scans faster, often the opposite is the case. Only partition if you expect a real benefit from it.

1000 partitions is almost too many to be efficient. You might consider having separate partitions for the bigger accounts and bundling the rest, perhaps using a default partition.


SQL Server 2014 data integration between servers

If You already install SQL BI Pack for VS 2013 - http://www.microsoft.com/en-nz/download/details.aspx?id=42313

for create SSIS package You just need:

  "Create New Project" -> "Business Intelligence" - > "Integration Services
  Project"

Main idea of SSIS (and similar tools) - not only load data from server to server, but transformation and  check on the fly, and some of this transformations hard (or impossible) made by SQL only tools.

Bulk insert in SSIS mean load from text file, when You not need any transformation

Linked Server is good solution if Your servers have good network connections, it allow You not worry about 3rd party tools (SSIS as well 3rd party relate to SQL Server engine).

Other way - unload result of complicated SELECT into text file and use bulk load resulted flat file into destination server.


What system privilege is needed for a user to manage user accounts only

Create the procedures with AUTHID DEFINER option (this is the default if you skip this clause) in a schema who has the ALTER USER privilege.

Them simply grant execute on these procedures to NOC.

NOC does not need any other privileges, as the procedures will run as if their owner would have called them.


Relational modeled design to store versioned files

Your Altered_Files design is absolutely fine.  In fact I would award bonus points for separating the files per se from the hierarchy.  @chillworld's suggestion is workable and very common but yours is better because of the NULL issues you mention.  I can't think of any update which is better in one schema or the other.  Change a single row - either the file data or the files' relationship - and you have a single update on a single table.  No risk to integrity there.  Altered_files contains only primary keys (PK) and pointers to PKs.  By definition these should never change.  On the off chance you should have to change one then, yes, you have to be very, very careful to maintain consistency.  For INSERTs and DELETEs wrap the statements in a transaction.  This is precisely why transactions exist.

I would suggest you store the full directory path for an altered image, not just its new sub-folder.  What happens if you derive image 4 from image 3, from image2, from image 1?  Will contact('-') handle that?  Will the continuous constructing of image 4's ancestry be a burden on the system?  You know your data, workflow and hardware best.  If the volumes are small this may not be an issue.

The file-in-database-or folder is an age-old one.  SQL Server has functionality called FILESTREAM where the image passes through the database on the way to the file system.  It is held on disk as a JPG or PNG or whatever and can be read by normal applications.  The database retains ownership of it and its related data.  The best of both worlds.  I don't think MySQL has anything similar, however.  I think you may want to grant other applications read access to the image filestore and reserve write access for the DB.


Why do you need to create new table on selection?

Can user select any sub category directly ?
i.e. Instead of clothing, Men's Clothing

Regards,
Maulin.


SQL Server 2000 : List of tables ordered from by number of referenced tables

I think this function is a good starting point. The original source is lost in the sands of time, but I picked it up from Uri Dimant.

Normally I don't copy linked content wholesale, but this looks like it will benefit everyone by being here (I've edited a few typos).

  Some time ago I was asked to truncate lots of tables in client's
  database. As such, all those tables have FK and PK dependency. You get
  the error like that if you run TRUNCATE TABLE Categories in Northwind
  database as an example.

--Cannot truncate table 'Categories' because it is being referenced by a FOREIGN KEY constraint.

  So , there is no such great problem as you know the database structure
  and can easily indentify parent-child tables. But what if you don't?
  I'd like to show the function (sadly, I don't remember by whom it is
  written) that returns the tables ordered by its level. Level 0 means
  that it is a referenced table and should be truncated last.

CREATE VIEW VFKs
AS

SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
GO

CREATE FUNCTION dbo.fn_get_tree_of_tables()
RETURNS @tree TABLE
(
table_schema SYSNAME NOT NULL,
table_name SYSNAME NOT NULL,
lvl INT NOT NULL,
PRIMARY KEY(table_schema, table_name)
)
AS
BEGIN

DECLARE @lvl AS INT
SET @lvl = 0

-- top level tables
INSERT INTO @tree
SELECT DISTINCT
parent_table_schema,
parent_table_name,
@lvl
FROM VFKs AS P
WHERE NOT EXISTS
(SELECT *
FROM VFKs AS C
WHERE C.child_table_schema = P.parent_table_schema
AND C.child_table_name = P.parent_table_name)

WHILE @@rowcount > 0
BEGIN

SET @lvl = @lvl + 1

-- non top level tables
INSERT INTO @tree
SELECT DISTINCT
child_table_schema,
child_table_name,
@lvl
FROM VFKs AS C
JOIN @tree AS P
ON P.table_schema = C.parent_table_schema
AND P.table_name = C.parent_table_name
AND lvl = @lvl - 1
WHERE NOT EXISTS
(SELECT *
FROM @tree AS T
WHERE T.table_schema = C.child_table_schema
AND T.table_name = C.child_table_name)

END

SET @lvl = 0

-- tables with no fks
INSERT INTO @tree
SELECT TABLE_SCHEMA, TABLE_NAME, @lvl
FROM INFORMATION_SCHEMA.TABLES AS TB
WHERE NOT EXISTS(
SELECT *
FROM @tree AS TR
WHERE TB.TABLE_SCHEMA = TR.table_schema
AND TB.TABLE_NAME = TR.table_name)
AND
TB.TABLE_TYPE = 'BASE TABLE'

RETURN

END
GO

  Here's the results of an invocation of the function in Northwind:

SELECT * FROM dbo.fn_get_tree_of_tables() AS T
ORDER BY lvl

table_schema table_name lvl
------------ ----------------------------------- ---
dbo Categories 0
dbo CustomerDemographics 0
dbo Customers 0
dbo Employees 0
dbo Region 0
dbo Shippers 0
dbo Suppliers 0
dbo Products 1
dbo Territories 1
dbo Orders 1
dbo CustomerCustomerDemo 1
dbo Order Details 2
dbo EmployeeTerritories 2


How does the database decide which Index to use

First two queries are self-explanatory - they're performing a seek on the leading column in the chosen index.

Third one is a coin-flip since, without a clustered index, both indexes have the same cost.

You might observe that the most recently-used index (from query 2) will be used for query 3 - but you shouldn't rely on it.


According this previous post on Stackoverlfow

Enter your server’s IP address in the “Hostname/ Address” field. Default is localhost.

Specify the “Port” as “5432”.

Enter the name of the database in the “Database Maintenance” field.

Enter your username as postgres and password (use the same password you used when previously configuring the server to accept remote connections) for the database.

Click “Save” to apply the configuration.


Insert up to 10 missing products into a related product discounts table

This might do what you want:

SELECT 'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

It generates statements for all top-10 products that don't have a product discount.  No need to do this in Excel.  Just copy-and-paste the results into a new query window in SSMS and run it.  Having said that, you'll likely want to automate this procedure, which could be accomplished using either a cursor like this:

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT 'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_executesql @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

Or in a set-based approach, like this:

DECLARE @cmd nvarchar(max);
SELECT @cmd = CASE WHEN @cmd IS NULL THEN N'' ELSE @cmd END
    + N'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

EXEC sys.sp_executesql @cmd;


First of all- you should create different database users for different access roles of applications- even they use the same data. That will make easier to monitor each individual app. Even if you have to share the user name, you can create different accounts with different source ips (or ip ranges) to differentiate user activity.

You have several options for query auditing that can work better, faster and more fine-grained than the general query log, for example:

Install an audit plugin. This is basically the ability to filter the general log in an efficient way so you write the results you want. MySQL/MariaDB provide a general api, and there are several plugins that allow you some flexibility on that filtering, normally using for security auditing:

https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/#excluding-or-including-users
https://github.com/mcafee/mysql-audit

Enable and use the integrated profiling utility performance_schema. While this is usually used to monitor performance and problems, it is so flexible that you can filter by any way imaginable. By default it will only get you user usage summaries and digests, but you can enable extended logging for a single user/database/etc. https://dev.mysql.com/doc/refman/5.6/en/performance-schema-pre-filtering.html
Use the slow query log. You can change the slow_query log limit to log only queries that take more that certain amount of seconds, or to log only certain percent of all queries. It contains the user name. This has a bit more control over what exactly you want to log, and then you can use tools like pt-query-digest to summarize what was going on.
Use tcpdump. In extreme cases, you can sniff your own traffic (if it is not encrypted), to identify queries happening between 2 servers. As any network connection, you can set where you sniff, and what kind of traffic/port you use, so that can quickly tell you what is going on: https://www.percona.com/blog/2008/11/07/poor-mans-query-logging/ There are some utilities to convert traffic to a more usable format, including the above pt-query-digest tool.


Inorder to get rid of the error you have got, you may follow below command.

    CREATE TABLE posicaorastreadores (  Id CHAR(36) COLLATE utf8_bin NOT
NULL DEFAULT '',  ComandoInicialString VARCHAR(1024) COLLATE
utf8_general_ci NOT NULL,  Serial BIGINT(20) NOT NULL,  DataEnvio DATETIME
NOT NULL,  DataGps DATETIME NOT NULL,  Latitude DOUBLE NOT NULL,  Longitude
DOUBLE NOT NULL,  Velocidade DOUBLE NOT NULL,  TensaoBateriaPrincipal
DOUBLE NOT NULL,  TensaoBateriaBackup DOUBLE NOT NULL,  DataInclusao
DATETIME NOT NULL,  DataAlteracao DATETIME NOT NULL,  Ativado TINYINT(1)
NOT NULL,  EnderecoArmazenado TINYINT(4) NOT NULL DEFAULT 0,
ModeloRastreador INTEGER(11) NOT NULL,  PRIMARY KEY USING BTREE
(Id,DataGps),  KEY enderecoArmazenado_idx USING BTREE (EnderecoArmazenado),
KEY serial_idx USING BTREE (Serial),  KEY datagps_idx USING BTREE
(DataGps),  KEY ativado_idx USING BTREE (Ativado),  KEY
ModeloRastreador_idx USING BTREE (ModeloRastreador),  KEY
posicaorastreadores_idx1 USING BTREE (ComandoInicialString),  KEY
posicaorastreadores_idx2 USING BTREE (DataEnvio),  KEY
posicaorastreadores_idx3 USING BTREE (Ativado, Serial, DataGps)  )
ENGINE=InnoDB  ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8' COLLATE
'utf8_general_ci';

And then issue

  ALTER TABLE posicaorastreadores PARTITION BY HASH(
  DAYOFMONTH(DataGps) ) PARTITIONS 31;

You should add pk and partition key to be PK for the table.

PRIMARY KEY USING BTREE (Id,DataGps)

But looking at your table definition you have secondary indexes almost on all. You might want to verify on performance of all your selects. Or tune them inorder to add the partition name or partition key as a mandate clause in where clause. So its better you take perf against selects and updates you use and then think whether is it feasible to go for partitioning or archiving the table.

Also if you are anyway going for partition, you need to define the partition in such a way older partition can be removed. So I would suggest to go for

  PARTITION BY RANGE ( DAYOFMONTH(DataGps) ) ;

And define range each partitions.


Partition by ascending and descending performance

Example table and index

CREATE TABLE dbo.Test
(
    a integer NOT NULL,
    b integer NOT NULL,
    c integer NOT NULL,
    d integer NOT NULL,
    e integer NOT NULL
);

CREATE INDEX i1 ON dbo.Test (a, b, c, d, e);

1. Apply solution

SELECT
    DT.a,
    DT.b,
    DT.c,
    FL.d,
    FL.e
FROM
(
    -- Could be an indexed view
    SELECT
        T.a,
        T.b,
        T.c
    FROM dbo.Test AS T
    GROUP BY
        T.a,
        T.b,
        T.c
) AS DT
CROSS APPLY
(
    (
        -- First
        SELECT TOP (1)
            T2.d,
            T2.e
        FROM  dbo.Test AS T2
        WHERE
            T2.a = DT.a
            AND T2.b = DT.b
            AND T2.c = DT.c
        ORDER BY
            T2.d ASC,
            T2.e ASC
    )

    UNION ALL

    (
        -- Last
        SELECT TOP (1)
            T3.d,
            T3.e
        FROM  dbo.Test AS T3
        WHERE
            T3.a = DT.a
            AND T3.b = DT.b
            AND T3.c = DT.c
        ORDER BY
            T3.d DESC,
            T3.e DESC
    )
) AS FL;

Execution plan:

2. Row Numbering Solution

An alternative, possibly better if there are a small number of rows per group on average: (improved based on a suggestion by Martin Smith)

Query

SELECT
    TF.a,
    TF.b,
    TF.c,
    TF.d,
    TF.e
FROM
(
    SELECT
        T.*,
        rn = ROW_NUMBER() OVER (
                PARTITION BY a,b,c
                ORDER BY d ASC, e ASC)
    FROM dbo.Test AS T
) AS TF
WHERE
    TF.rn = 1

UNION ALL

SELECT
    TL2.a,
    TL2.b,
    TL2.c,
    TL2.d,
    TL2.e
FROM
(
    -- TOP (max bigint) to allow an ORDER BY in this scope
    SELECT TOP (9223372036854775807)
        TL.a,
        TL.b,
        TL.c,
        TL.d,
        TL.e
    FROM
    (
        SELECT
            T.*,
            rn = ROW_NUMBER() OVER (
                    PARTITION BY a,b,c
                    ORDER BY d DESC, e DESC)
        FROM dbo.Test AS T
    ) AS TL
    WHERE
        TL.rn = 1
    ORDER BY
        -- To allow the optimizer to scan the index backward
        -- (Workaround for PARTITION BY being assumed ASC)
        TL.a DESC,
        TL.b DESC,
        TL.c DESC,
        TL.d DESC,
        TL.e DESC
) AS TL2;

Execution plan:

3. Using LEAD

This is based on the idea that the first row is row number 1, and the last row is the row before the row numbered 1:

SELECT
    L.a,
    L.b,
    L.c,
    L.d,
    L.e
FROM
(
    -- Add LEAD(1) on numbering
    SELECT
        N.*,
        next_rn = LEAD(N.rn, 1, 1) OVER (
            PARTITION BY N.a, N.b, N.c
            ORDER BY N.d, N.e)
    FROM
    (
        -- Numbered
        SELECT
            T.*,
            rn = ROW_NUMBER() OVER (
                PARTITION BY T.a, T.b, T.c
                ORDER BY T.d, T.e)
        FROM dbo.Test AS T
    ) AS N
) AS L
WHERE
    -- This row is first, or the next one is
    L.rn = 1
    OR L.next_rn = 1;

Execution plan

To summarize the comment discussion:

If you need additional columns returned, simply add them to the queries in the appropriate places and ensure they are included in the index.
Once created, queries can potentially benefit from indexes many times. With an explicit sort in the execution plan, the sort happens on every execution. Also, index creation sorts are allowed to dynamically acquire more memory as needed; that is not the case for regular sorts - they get a fixed allocation based on optimizer estimates, and that's it. Exceed the allocation, and the sort spills to disk.
The apply approach is optimal for relatively large groups. The estimate is 2 rows per iteration but the 'actual' is over all iterations (SSMS design decision). A very large number of iterations is bad for the apply.


Input (A, then B):

set pages 12

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
),
B as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9
)
select A.i, A.j, A.val from A union all
select B.i, B.j, B.val from B
;

         I          J        VAL
---------- ---------- ----------
         1          1          1
         1          2          2
         1          3          3
         2          1          4
         2          2          5
         2          3          6
         3          1          7
         3          2          8
         3          3          9

         I          J        VAL
---------- ---------- ----------
         1          1         11
         1          2         10
         1          3          9
         2          1          8
         2          2          7
         2          3          6
         3          1          5
         3          2          4
         3          3          3

And their product (AB):

with A as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9
),
B as
(
  select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9
)
----------------------------------------------------------------
select
  A.i as i,
  B.j as j,
  sum (A.val * B.val) as val
from
  A, B
where A.j = B.i
group by A.i, B.j
order by
  1, 2
;

         I          J        VAL
---------- ---------- ----------
         1          1         42
         1          2         36
         1          3         30
         2          1        114
         2          2         99
         2          3         84
         3          1        186
         3          2        162
         3          3        138


MySQL Master-Slave Replication additional columns on slave are being reset to their default value

If the table definitions are different, you are asking for trouble and need to be careful.

In particular, if replication is using Row Based Replication, I suspect the missing columns would get defaults, but I would not trust it.

If you are writing to the Slave, you are asking for trouble.

But this may be a workaround -- instead of having extra columns, have an extra table for the extra columns.  The two table would have the the same PRIMARY KEY, except that the extra table would not say AUTO_INCREMENT.


MySQL Client can Connect but Replication Slave Can't

A 2003 error is normally some form of connectivity error, you may want to check firewall settings, routing and SELinux status.
For the latter, you can either go and fix the SELinux permissions properly but as a quick and dirty test you can do the following
sudo setenforce 0

Hope the above helps.


Join Fact table to SCD Type 2 ... how to write query? (SQL Server)

Setup some sample data:

create table fact_picked
(Emp_Name         varchar(30)
,[Date]           date
,Apples_Picked    int);

insert into fact_picked values
('John','10/17/15',175),
('John','05/01/17',100);

create table dim_company
(Emp_Name         varchar(30)
,Company          varchar(30)
,Effective_Since  date);

insert into dim_company values
('John','Blue_Apples',  '06/01/2015'),
('John','Apple_N_Stuff','01/01/2016'),
('John','Da_Big_Apple', '03/17/2017'),
('John','Big_Tech',     '10/20/2017');

Proposed solution:

select fp.Emp_Name,
       fp.[Date],
       fp.Apples_Picked,
       dc.Company,
       dc.Effective_Since

from   fact_picked fp

left
join   dim_company dc
on     dc.Emp_Name        = fp.Emp_Name
and    dc.Effective_Since = (select max(Effective_Since)
                             from   dim_company dc2
                             where  dc2.Emp_Name         = fp.Emp_Name
                             and    dc2.Effective_Since <= fp.[Date])

order by 1,2,5;

Emp_Name | Date                | Apples_Picked | Company      | Effective_Since
-------- | ------------------- | ------------- | ------------ | -------------------
John     | 17/10/2015 00:00:00 |           175 | Blue_Apples  | 01/06/2015 00:00:00
John     | 01/05/2017 00:00:00 |           100 | Da_Big_Apple | 17/03/2017 00:00:00

sub-query is used to find the dim_company record with the latest (ie, max()) Effective_Since date that is less than a given fact_picked.[Date]
use the sub-query result to determine which dim_company record to join with in the top-level query

To make sure the left join is working properly, assume we have only the following rows in the dim_company table:

truncate table dim_company;
insert into dim_company values
('John','Da_Big_Apple','03/17/2017'),
('John','Big_Tech',    '10/20/2017');

Run the proposed query again:

select fp.Emp_Name,
       fp.[Date],
       fp.Apples_Picked,
       dc.Company,
       dc.Effective_Since

from   fact_picked fp

left
join   dim_company dc
on     dc.Emp_Name        = fp.Emp_Name
and    dc.Effective_Since = (select max(Effective_Since)
                             from   dim_company dc2
                             where  dc2.Emp_Name         = fp.Emp_Name
                             and    dc2.Effective_Since <= fp.[Date])

order by 1,2,5;

Emp_Name | Date                | Apples_Picked | Company      | Effective_Since
-------- | ------------------- | ------------- | ------------ | -------------------
John     | 17/10/2015 00:00:00 |           175 | null         | null
John     | 01/05/2017 00:00:00 |           100 | Da_Big_Apple | 17/03/2017 00:00:00

And here's a dbfiddle for the above.


Is there a way to get SQL Server capcity constraints dynamically

No there's nothing built in that is exposed to us.

Your app would need to maintain some sort of configuration table itself.

You can't even try and parse it out of sys.messages as it isn't hardcoded into the text for message_id=1702.

  CREATE TABLE failed because column '%.*ls' in table '%.*ls' exceeds
  the maximum of %d columns.

Your app could of course try and dynamically discover the limit by simply trying CREATE TABLE statements with ever greater amounts of columns but that is not a serious suggestion.


How to determine which statement is blocking an update in SQL Server

Before you run your update:

SELECT @@SPID;

Now, run the update, if it is not completing quick enough, then in another window run:

SELECT status, command, wait_type, last_wait_type, blocking_session_id
  FROM sys.dm_exec_requests
  WHERE session_id = <@@SPID from above>;

If you get a value in blocking_session_id, then you can run the same query for that SPID. You can also say:

DBCC INPUTBUFFER(<blocking spid>);

(Well, as long as you're not on SQL Azure.)

If this doesn't yield a clue, then it's possible the blocking statement was actually at a different point in a larger transaction. You'll have to track that down by figuring out where that spid is executing from and whether it's a batch of ad hoc T-SQL, a stored procedure, etc. There's not really any magic "look here!" we can provide you.

If it's not getting blocked, you may find relevant info in the other columns that can help determine why it's slow (e.g. perhaps it is waiting on a ginormous log autogrow event).


first of all, please have a look at this recent response about multi-master replication, where I remark how likely is this setup to create problems and some alternatives. This was probably a direct or indirect cause of your desync.

Bidirectional sync is supported in pt-table-sync, but not in a replication environment, and only between 2 servers. You must unlink your slaves from the replication process in order to apply it, separately for each pair. This will likely create downtime, at least for your writes. The reason for this is because it is implemented by checking rows that do not exist on the other node, and they are added. It does not handle deletes and complex situations.

Bidirectionally syncing 3 servers automatically is technically impossible, because if the 3 of them have the same row with different data, which one is right? You can still use the tool to help you define your own rules (e.g. tableX in server A is the reference one), or detect differences and apply the changes conditionally (pt-table-sync can log the changes before being applied).

The normal procedure for pt-table-sync is to use it in a single-direction replication, as you usually only have one master "reference" database (again, a problem with multi-master replication), and more reliable sync process is available. It also allows syncing multiple nodes at the same time. I recommend you to explore the options on my first link to avoid these kind of issues in the first place.


MySQL: LEFT JOIN not working as expected

The WHERE date(attIn) like '2016-07-02%' is converting the LEFT join to an INNER join. The condition should be moved to the ON clause.

Also:

It's not good practise to use LIKE for dates comparison
Using functions on columns (like the date()) before comparing it makes indexes useless. It's better to make the condition sargable.

The query corrected:

SELECT e.eno, e.name, e.dept, a.attIn
FROM employees AS e
  LEFT JOIN attendanceIn AS a
    ON  e.eno = a.eno
    AND a.attIn >= '2016-07-02'
    AND a.attIn  < '2016-07-03' ;

Tested at SQLfiddle.


I use this form for delete obsolete data monthly day by day, but for update it will work also:

adopted for UPDATE procedure example:

DELIMITER $$

CREATE DEFINER = `root`@`localhost` PROCEDURE `geo_opt_2017`.`udp_batch_update`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER

BEGIN

SET @event_id = 1;

WHILE EXISTS (SELECT event_id FROM geo_opt_2017.event WHERE event_id > @event_id AND event_id <= @event_id + 1000) DO

    UPDATE geo_opt_2017.event SET event_lat_int = event_lat_int * 1 WHERE event_id > @event_id AND event_id <= @event_id + 1000 ;

    SET @event_id = @event_id + 1000;

END WHILE;

END $$
DELIMITER ;

SET portion - is dummy, just for illustration, You need replace it for real procedure logic

The logic will proper work with many numeric or dates - simple increment by  numeric or date time interval.

with tables where PK - combined from different columns without any visible logic, like telecom tables - (prefix, context, priority) it also possible, but little more complicated.


Although you limited the memory to 28 GB out of 32 GB, this is not the only memory that SQL Server uses.  The running programs and needs within Windows take some additional space.

If you want to closely tune the memory use, I recommend reading this, which works well for my servers.  See Jonathan Kehayias at:
http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

It basically says "reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM."  This suggests that your Maximum Memory should be tuned to 25 GB.

You can dynamically alter the min/max by setting a new value for those measures.  The OS will adjust in a relatively short time without a reboot.

EDIT: Of course, as both Jonathan and Shanky mention, you still need to monitor your performance counters, since any general answer will likely need tuning.


How to restore/create linked server logins from one server to another

You can use Copy-DbaLinkedServer from dbatools to migrate linked servers.

For login migration, you can use copy-dbalogin

Just install dbatools and you will get all the cmdlets.

Also, see my answer if you want to dump the scripted output into file.


Query agent jobs for a given notification operator name

Please try following T-SQL query:
 USE [msdb]
GO

   SELECT j.[name] AS [JobName]
    FROM [dbo].[sysjobs] j
    LEFT JOIN [dbo].[sysoperators] o ON (j.[notify_email_operator_id] =o.[id] )
    WHERE o.[id]=1 ----or o.[name]='your operator name'
     and j.[enabled] = 1
        AND j.[notify_level_email] IN (1,2)

This should be able to query the jobs for success or failure notification to a specified operator.


History table design for supertype/subtype

It sounds like what you want is an implementation of transaction-time temporal tables. It's harder than it sounds.

  Valid time, capturing the history of a changing reality, and
  transaction time, capturing the sequence of states of a changing
  table, are orthogonal, and can thus be separately utilized or applied
  in concert. A table supporting both is termed a "bitemporal table".

Reference: Developing Time-Oriented Database Applications in SQL, by Richard Snodgrass, p 20.

This book was the only book I could lay my hands on when I was working on this stuff some years ago. Now you can download a PDF of the book and the accompanying code from the author's web page.

Newer material might be more useful. There are links on his web page.


auto populate common columns of all tables using triggers

You didn't post what you tried for a simple row level trigger so it isn't obvious how you got an infinite recursion error.  My assumption is that you tried to insert into the table in your trigger rather than simply setting the column values.  Creating a view and an instead of trigger is vastly overcomplicating the problem.
My assumption is that you want something like this.  I'm not clear why modified_by and modified_date are not null since there is logically no modification date or user if a row has just been inserted.  If you want to populate modified_by and modified_date on the initial insert, you can add that to the inserting branch.
create or replace trigger course_trg
  before insert or update on course
  for each row
declare
begin
  if( inserting )
  then
    :new.created_by := user;
    :new.created_date := sysdate;
  elsif( updating )
  then
    :new.modified_by := user;
    :new.modified_date := sysdate;
  end if;
end;


Commands out of sync; you can't run this command now

I think this Stack Overflow question is related to your query:

PHP Commands Out of Sync error

It's because results are not completely fetched before another call. PHP docs has a solution read this too:

http://www.php.net/manual/en/mysqli.query.php#102904


ERROR: invalid input syntax for type money

The manual about the money type has advice for your case exactly:

  Since the output of this data type is locale-sensitive, it might not
  work to load money data into a database that has a different setting
  of lc_monetary. To avoid problems, before restoring a dump into a new
  database make sure lc_monetary has the same or equivalent value as in
  the database that was dumped.

To check:

SHOW lc_monetary;

The manual on lc_monetary.

The example value in the error message ('$0.00') would work with the default setting (among others):

lc_monetary = 'C';

You can set it in postgresql.conf, reload and try again ...

This peculiar behavior is one of the reasons why the money type is unpopular.


Is it possible to index or cache non-immutable function (based on date)

You could use materialized views, but you would still need some external tool (cron, etc.) to refresh them on a regular basis.  You could install some PostgreSQL-based scheduling system, but that would also count as an external tool as they don't ship with PostgreSQL.  (And would probably rely on cron internally anyway).

You could have the querent (an already existing "external tool") itself do the refresh.  It is a bit tricky for it to know when to, though. You would have to store the last refresh time someplace, then trigger a refresh if that is too old, then update that stored time.  If it needs to refresh before returning a result, then it will kind of suck for the unlucky user to whom that task falls.


Mysql GROUP BY is really slow on a simple view

First things first: Does the query continue to perform slowly if you bypass the view and run the underlying query with the GROUP BY? In my experience, MySQL is notoriously bad with views that are used with anything except SELECT statements.

If that fixes the problem, then just create a second view with the actual information you want to query (that is, with the GROUP BY included in the view).

If the problem persists, then there's a few things that can be improved.

Add primary key

You have no primary key on the movement_items table. Add one. If each combination of movementId and itemId can only have on entry, then make the PK a composite key out of those columns. Otherwise, you can add a surrogate autoincrement primary key.

Add index

Try the following index:

movement_items: (itemId,movementId,qty)

Provide the actual query you want optimized

Query optimization is not a piecemeal process. You cannot improve one query, and then expect that improvement to necessarily carry through to related queries.

You say you want to do a SUM, well, show us that SUM query. What exactly you are SUMming will require different optimizations than a SELECT * will.

Also, provide your EXPLAIN plans. You mention that you've run them, but don't provide that extremely useful information to us.


Query on char with selector with N prefix is very slow

It's bad enough that they are using a GUID as a Clustered PK, but they had to make it worse by making it a string and then doing a case-insensitive, locale-aware comparison? Amazing!

Well, if you see that huge of a difference between passing in a string without the N and with the N, then that indicates that the Collation of the silly CHAR(36) column is a SQL Server Collation (one starting with SQL_), most likely SQL_Latin1_General_CP1_CI_AS (the default for US installs).

But you are in luck. There are three options that should help greatly, given that you cannot change the queries themselves:

Ideally: Change the datatype of the id column to be UNIQUEIDENTIFIER: this will be faster not only because it will convert the N'xxx-xx-xx... value into a GUID first and then do a binary comparison (due to Data Type Precedence), but each row will be something like 22 bytes smaller (36 bytes for VARCHAR(36) down to 16 bytes for UNIQUEIDENTIFIER, minus another 2 or so for getting rid of a variable-length column).
If you can't do #1: Change the Collation of the id column to a Windows Collation, and preferably a binary one (ending in _BIN2), such as Latin1_General_100_BIN2. This won't save any space, but won't be nearly as expensive to convert the incoming NVARCHAR literal to CHAR(36) (or vice-versa), AND will be a binary comparison (much faster than case-insensitive, locale-aware) since it is a GUID and not subject to linguistic rules.
If you can't even do #2: Change the Collation of the id column to a Windows Collation that isn't a binary one, most likely Latin1_General_100_CI_AS. This doesn't save space or even do a binary comparison, but does allow for sloppy coding / ad-hoc queries with mixed-case values AND is still much faster than the conversion used by the SQL Server Collation.

You can see this behavior yourself via the following test:

SET NOCOUNT ON;

CREATE TABLE dbo.GuidPkAsUI (
  ID UNIQUEIDENTIFIER NOT NULL CONSTRAINT [PK_GuidPkAsUI] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsUI_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCci (
  ID CHAR(36) COLLATE Latin1_General_100_CI_AS NOT NULL CONSTRAINT [PK_GuidPkAsCHARci] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARci_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCbin (
  ID CHAR(36) COLLATE Latin1_General_100_BIN2 NOT NULL CONSTRAINT [PK_GuidPkAsCHARbin] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARbin_InsertTime] DEFAULT (GETDATE())
);

CREATE TABLE dbo.GuidPkAsVCsql (
  ID CHAR(36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [PK_GuidPkAsCHARsql] PRIMARY KEY,
  InsertTime DATETIME NOT NULL CONSTRAINT [DF_GuidPkAsCHARsql_InsertTime] DEFAULT (GETDATE())
);

INSERT INTO dbo.GuidPkAsUI ([ID])
    SELECT NEWID()
    FROM   master.sys.objects
    CROSS APPLY master.sys.all_columns;

INSERT INTO dbo.GuidPkAsVCci ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

INSERT INTO dbo.GuidPkAsVCbin ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

INSERT INTO dbo.GuidPkAsVCsql ([ID], [InsertTime] )
    SELECT [ID], [InsertTime]
    FROM   dbo.GuidPkAsUI;

SELECT * FROM dbo.GuidPkAsUI;
-- Pick a value from half-way down and paste into
-- the 4 queries below

SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsUI
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3

SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCci
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3

SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCbin
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 3

SET STATISTICS IO, TIME ON;
SELECT [InsertTime]
FROM   dbo.GuidPkAsVCsql
WHERE  [ID] = N'998CCC99-269C-4B53-A8B6-77B8475EFEF7';
SET STATISTICS IO, TIME OFF;
-- logical reads: 7157 (yikes!)


What are good resources on data modelling for business intelligence?

I recommend the Data Warehousing Toolkit by Ralph Kimball.  This is a solid book on dimensional modeling and provides many "soup to nuts" case studies.


What is the best approach to mapping between two tables with many to many relationship?

An intersection table such as you've described is the standard way to implement a many to many relationship in a physical relational database.

The intersection table has foreign keys to each of the parent tables that it's joining.  These may be simple or compound foreign keys.  That doesn't matter.

The primary key of the intersection table is the combination of the two foreign keys to the parent tables.  For this reason, the primary key of an intersection table is always a compound foreign key.


MSDTC on SQL Server 2012 with mount point for data disk

When parametrized, the dependency disk is the disk on which the msdtc will create its directories. This disk must NOT be a mount point.

Hope it can help someone


To pick a random row, see:

quick random row selection in Postgres

SELECT *
FROM words
WHERE Difficult = 'Easy' AND Category_id = 3
ORDER BY random()
LIMIT 1;

Since 9.5 there's also the TABLESAMPLE option; see documentation for SELECT for details on TABLESAMPLE.


Try this free book from redgate:
http://www.sqlservercentral.com/blogs/aloha_dba/archive/2009/01/21/mastering-sql-server-profiler-e-book-available-for-free.aspx

Brad McGee also has some videos on his website:
http://www.bradmcgehee.com/videos/

This video on technet:
http://technet.microsoft.com/en-us/sqlserver/ee861095

This site also has more general SQL Server video tutorials (and a few profiler ones)
http://www.sqlshare.com/channel.aspx?cat=96b0a352-6498-dd11-bebd-001143eb4af9


PostgreSQL Slave has more files in pg_xlog than /wal_archive

how is it possible for the Slave to have more pg_xlog/ log files than the Master?

The whole point of archiving WAL on the master to some external location is to let the master then delete it to free space in its pg_xlog, while replicas might still need it.

A replica can have more archives in pg_xlog than the master, and older ones, if it's lagging behind the master due to failure to keep up with replay. However, with pg_standby that shouldn't happen - the archive might contain more xlogs, but the replica should only be reading them on-demand.

It's hard to be specific, because you've given a broad description of the issue rather than actual directory listings, and haven't explained the exact steps you followed to set up the replica. Or shown the exact log file output from the replica. So the best I can do is "it sounds like the replica setup is broken somehw".

  to resync the servers in warm standby mode: do I have to do pg_basebackup again (to essentially copy Master's /data and /pg_xlog directory) to the Slave?

Assuming that here /data is the main datadir, containing global, base, pg_clog, etc, and that pg_xlog is the transaction logs from a different disk: Yes, that's right.

You must use the pg_basebackup command, though, or follow the instructions in the manual for correct file system level copies using pg_start_backup() and rsync/cp.

You also have to make sure you've stopped the replica first. Overwriting its datadir while it's running will make it quite upset.

Streaming replication vs warm standby

Hot vs warm standby is orthogonal to streaming vs log shipping replication.

What you're trying to do is use log shipping instead of streaming replication. It doesn't matter for this purpose if the replica is a hot standby or a warm standby, i.e. whether or not it's accepting queries.

Personally I recommend using both methods - use streaming, and fall back to log shipping if there's a problem with streaming. PostgreSQL does this automatically if both are configured.


It depends on your engine. Common wisdom is that reads are cheap, a few bytes here and there will not significantly impact the performance of a small to medium size database.

More importantly, it depends on the uses to which you will put the primary key. Integer serials have the advantage of being simple to use and implement. They also, depending on the specific implementation of the serialization method, have the advantage of being quickly derivable, as most databases just store the serial number in a fixed location, rather than deriving it with Select max(ID)+1 from foo on the fly.

The question becomes: how does a 5 character key present a "meaningful value" to you and to the application? How is this value created, and does it take more or less time than finding an incrementing serial number. While there is a trivial amount of space saved in some integers, the vast majority of systems will ignore this space savings.

There are no performance implications, save that the character scheme requires that there never be an automatic engine, as your "keys" are underivable. For your specific domain, don't bother with artificial keys, and just use Chinese, Japanese and Thai as key names. While you cannot guarantee uniqueness over any possible application, in your scope it is much more reasonable to use them instead of horrible and forced 5-character abbreviations. There are no significant performance impacts until you get to the millions of tuples.

Alternatively, if you're just tracking by country of origin, and not specific regional cuisines (Cantonese, Sichuan, Sicilian, Umbrian, Calabrian, Yucatecan, Oaxacan, etc.), you could always just use ISO 3166 codes.

  If I have 10,000 recipes doesn't the difference between a 5-character and 20-character key start to add up?

Space is cheap. When you're talking 10,000,000 recipes that you're doing OLAP operations on, then, maybe. With 10k recipes, you're looking at 150k of space.

But again, it depends. If you have many millions of records, and are doing joins on them, then it makes sense to denormalize the lookup for something this trivial (into a materialized view). For all practical purposes, the relative join efficiency on a modern machine between a 5 character key and variable length key is so similar to be identical. Happily, we live in a world of plentiful CPU and plentiful disk. The nasty ones are too many joins and query inefficiency, rather than character-by-character comparison. With that said, always test.

P&T things of this level are so database-dependent that generalizations are extremely difficult. Build two sample models of the database, populate them with the estimated numbers of records, then see which one is faster. In my experience, character length doesn't make a huge difference compared with good indexes, good memory configurations, and other critical performance tuning elements.


MongoDB replica sets and data duplication

In this case, the following will happen:

When a member rejoins the replica set, the point in time on which the oplog is identical on the cluster is figured out.
If there are oplog entries beyond said given point in time, those entries are tried to be reverted and saved for human analysis. This is called a rollback in MongoLand.
If the node was down for a longer time than the oplog window, it becomes stale as usual, and a manual resync is required. In this case all the entries of the oplog are rolled back when the node comes up again.
The maximum size for a rollback is 300MB. While this sounds little, it is actually quite some, as those 300MB only need to hold the data in which a primary has to figure out that it is not longer part of a replica set (may be through network partitioning).

Please read more on this at Rollbacks During Replica Set Failover and Resync a Member of a Replica Set in the docs.


SQL Server 2008 R2 DB grows overnight and slow

I don't have answer but would like to clear few things that would help

  DB is 34GB, TL is 1.2 GB, 10% for both

There was surely something happened in database it cannot grow on its own. The first thing you should do is change autogrowth setting you have set autogrowth in percentage(10 %)  for both data and log files this IMO would have forced database to grow unnecessarily. You can refer to Autogrowth settings and how to configure it and configure optimum value for this preferably in MB.

  It's typical to see it run at around the 6-8 GB range but all of sudden its taking up to 24 GB of the 32 GB the server has. Again unexplained.

What is current max server memory setting and please dont change it to lower value it will cause issues for you. Its normal for SQL server to utilize all memory if allowed to use via max server memory setting.


postgres removing user from role membership

With REVOKE e.g.:

REVOKE postgres FROM tester;


SQL Server Standard Basic Availability Group - only 10 Listeners limit?

The limitation is only number of replicas.

Limit of two replicas (primary and secondary).
he was able to set up 12 basic availability groups. He was able to set up 10 listeners

You can create many AGs and listeners. The error you are getting is self explanatory

The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.

Also refer to Top 7 Questions about Basic Availability Groups and Troubleshooting AlwaysOn availability group listener creation in SQL Server 2012.


Large query with subquery runs indefinitely after mysql update

This does not work in MYSQL?
Give it a try - what do you have to lose?

SELECT people.id, max(results.date)
FROM people
JOIN results
      on people.id = results.person_id
     and people.boss_id = 123
GROUP BY people.id;

Would benefit from indexes on:
1) results.person_id
2) people.boss_id

Index on date may help but start without


How to set MySQL binlog retention in hours?

Actually, you can now. Since MySQL 8.0.1, while expire_log_days still works, the canonical way to setup expiration is by changing binlog_expire_logs_seconds, which, as you may guess, has second resolution. The default value since is 8.0.11 is 2592000 (= 30 days), but can be configured as usual on the configuration file to any other period in seconds.
If you cannot use MySQL 8.0 yet, the way to fix this is to use the PURGE BINARY LOGS syntax, with BEFORE (PURGE BINARY LOGS BEFORE now() - INTERVAL 1 HOUR;' and setting the max-binlog_size to an appropiate value (you cannot delete currently-being-written binlog, although you can always force the rotation with FLUSH BINARY LOGS). You can setup an event to do that regularly, or externally, on a cron/programmed task.
Alternatively, forks of MySQL, like Percona, used to (and still does) allow to setup a max binlog size in bytes -by combining max_binlog_size and max_binlog_files-, in addition to the days limit.


How to get MySQL to iterate a subquery in an aggregate select?

(sum(amount * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50)))) as cost
Is the answer, as provided by ypercube.

I was victim to order of operations--MySQL was taking the sum of gallons pumped, then multiplying that against the first/last result found from the gas prices sub query. As Michael quoted and noted:

  "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

So to get what I was after, the sum of each entry multiplied against the price of gas during that week, you simply wrap that exact expression in the comfortable embrace of a sum().


Refactoring sql for tables with similar columns but different table names

This might help you to create simple dynamic query for partitioned tables using COALESCE function of SQL Server

Step1: Create function to split tokens

CREATE FUNCTION [dbo].[fnSplit]
(
@List VARCHAR(8000),
@Delimiter CHAR(1) = ','
)
RETURNS @Temp1 TABLE
(
ItemId INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY,
Item VARCHAR(8000) NULL
)
AS BEGIN

DECLARE @item VARCHAR(4000),
@iPos INT

SET @Delimiter = ISNULL(@Delimiter, ',')
SET @List = RTRIM(LTRIM(@List))

-- check for final delimiter
IF RIGHT(@List, 1) <> @Delimiter
-- append final delimiter
SELECT @List = @List + @Delimiter

-- get position of first element
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)

WHILE @iPos > 0
BEGIN
-- get item
SELECT @item = LTRIM(RTRIM(SUBSTRING(@List, 1, @iPos - 1)))
IF @@ERROR <> 0
BREAK
-- remove item form list
SELECT @List = SUBSTRING(@List, @iPos + 1, LEN(@List) - @iPos + 1)
IF @@ERROR <> 0
BREAK
-- insert item
INSERT @Temp1
VALUES ( @item )
IF @@ERROR <> 0
BREAK
-- get position pf next item
SELECT @iPos = CHARINDEX(@Delimiter, @List, 1)
IF @@ERROR <> 0
BREAK
END
RETURN
END

GO

Step 2: Set parameter value @varyears and execute query

DECLARE @varyears VARCHAR(200)
SET @varyears = '2012, 2011, 2013 ,2014'

DECLARE @combinedString VARCHAR(MAX)

SELECT  @combinedString = COALESCE(@combinedString + ' UNION ALL ', '')
        + CHAR(13) + ' Select ID, sum(amount) TotalAmount, ''' + item
        + ''' as [Year] FROM CustomerTransaction' + item
        + '_tbl  group by ID ' + CHAR(13) --group by clause added to your query for using aggregate function
FROM    dbo.fnSplit(@varyears, ',')

EXEC ( @combinedString )


Thanks for the test data, makes it much easier. First reflection is that almost all columns are nullable, I wonder whether that's deliberate because it makes querying more difficult. In the sketch below I'll assume that columns are mandatory. You may also want to add the following constraints to game

CHECK( player1 <> player2 )            -- can't play against self
CHECK( results in (player1, player2) ) -- winner must participate

select p.id, p.name
     , count(case when g1.results = p.id then 1 end) as win
     , count(g1.player1) as games -- any column from g1 will do
from players p
left join game g1
   on p.id = g1.player1
   or p.id = g1.player2
group by p.id, p.name
order by win DESC, games DESC;

If you make a left join between player and game (given the join condition), you will get all players and all games where player is either player1 or player2.

To count all games we count the rows where either player1 or player2 is not null. Note that we can not count the rows (say with count(*)) since we then would count rows where the player does not participate.

To count the wins we count the rows where the winner match the player.


When all you indexes have included all the other columns, do you still have the penalty for a wide clustered index?

If you have in every index all columns and you have a PK , and another 2 indexes , one on Value and one on LastChanged , you really multiplied the table by 4. This will make the table really slow for all operations.

Add an ID column , make it PK  and build indexes as you need based on the statements that run on the table.

Don't create an index on Invalid, it's a bit column , it's usually not useful .

Try to keep a rule that all indexes should be smaller in size than the table.


MySQL: Privileges to view Views/Stored Procedure for user Regardless of Host

You may check grants for user and see if it differs for hosts.
In addition to

show grants for username@host

You may check grants for specific databases:

use dbname;
show grants for username;

Also, there are few more places to check for extensive list of privileges:

SELECT * FROM FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE GRANTEE LIKE '%userame%';
SELECT * FROM FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE LIKE '%userame%';
SELECT * FROM FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
WHERE GRANTEE LIKE '%userame%';

Most likely  INFORMATION_SCHEMA.USER_PRIVILEGES will shed a light on your issue.


Two SQL Servers cross-mirroring each other?

What you are telling is possible. Since you are running Standard Edition of SQL Server you have to bear following things :

Standard edition supports mirroring ONLY in full safety mode.
You have to be careful about number of databases your are configuring mirroring for, since it is recommended not to have more than 10 databases mirrored to secondary server. Remember that - On primary server you would need 1 global thread and 2 threads per each of the mirrored databases. On Mirrored server - 1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores. 2001270 KB article talks about the worker threads requirement.
You cannot use database snapshots on the secondary server - since you are on standard edition.

  I'm trying to purely have a failover system without significant additional licensing/resources.

I would suggest you to look into Log-shipping as an alternative solution for your scenario. It is easy to set up and is a good old proven DR technology. Minimum it allows is 1 min to take log backups. Logshipping does not have any hard limitations on the number of databases being logshipped.

Logshipping allows you to logship your databases to more than one destination which is a very good advantage over database mirroring. Also, logshipping has functionality to delay restoring of log files on the secondary server to protect against logical errors. Mirroring does not allow this.

Logshipping as a part of process provides you full backup and subsequent log backups (depending on the frequency you configured - minimum is 1 min) to allow you to have backups so that you can easily setup your environment form them. Mirroring does not.

At the end, a good Disaster Recovery strategy is totally dependent on -

How much downtime  can your business survive? and
How much data loss is acceptable ?


MongoDB replication: going into maintenance mode with 10333 other maintenance mode tasks in progress

What are maintenance mode tasks?

The "maintenance mode tasks" message is referring to a counter of successive calls to the replSetMaintenance command and (as at MongoDB 3.4) isn't associated with specific queued tasks. The replSetMaintenance command is used to keep a secondary in RECOVERING state while some maintenance work is done. A RECOVERING member remains online and potentially syncing, but is excluded from normal read operations (eg. using secondary read preferences with a driver). Each invocation of replSetMaintenance either increases the task counter  (if true) or decreases it (if false). When the counter reaches 0 the member will transition from RECOVERING back into SECONDARY state assuming it is healthy.

As at MongoDB 3.4, changes in maintenance mode are currently only noted in the MongoDB log. This command is generally only used internally by mongod, but you can invoke it manually as well.

Here's an annotated set of log lines and the associated mongo shell commands showing the task counter changing:

// db.adminCommand({replSetMaintenance: 1})
[ReplicationExecutor] going into maintenance mode with 0 other maintenance mode tasks in progress
[ReplicationExecutor] transition to RECOVERING

// db.adminCommand({replSetMaintenance: 1})
[ReplicationExecutor] going into maintenance mode with 1 other maintenance mode tasks in progress

// db.adminCommand({replSetMaintenance: 0})
[ReplicationExecutor] leaving maintenance mode (1 other maintenance mode tasks ongoing)

// db.adminCommand({replSetMaintenance: 0})
[ReplicationExecutor] leaving maintenance mode (0 other maintenance mode tasks ongoing)
[ReplicationExecutor] transition to SECONDARY

// db.adminCommand({replSetMaintenance: 0})
[ReplicationExecutor] Attempted to leave maintenance mode but it is not currently active

  Why there are 10333 queued?

In MongoDB 3.2 a replica set member that becomes "too stale" (i.e. doesn't have any oplog entries in common with another healthy member of the replica set) will remain in RECOVERING mode and periodically check if a new valid sync source is available. Each check currently increments the "maintenance task" counter, so this doesn't actually indicate a meaningful number of tasks if the member has become stale.

In theory "too stale" is not a terminal state as conceivably a member with a larger oplog may temporarily be offline; in practice a "too stale to catch up error" generally means a manual resync is required.

2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] our last optime : (term: 20, timestamp: Oct  4 07:41:29:1)
2016-11-07T11:59:23.354+0000 I REPL     [rsBackgroundSync] oldest available is (term: 20, timestamp: Oct 17 02:13:33:5)

In this case the replica set member in question went stale almost two weeks earlier, so the maintenance mode counter has continued to creep up over time. There's a related issue in the MongoDB Jira you can watch/upvote: SERVER 23899: Reset maintenance mode when transitioning from too-stale to valid sync source.


Redundant mirroring of two mongodb servers

You'll need to find a different approach as there are a few major assumptions that won't work.

  The solution I am going to implement for my client is that I will set up a Raspberry Pi that will run the same web application locally at their shop

Raspberry Pi 1/2 (with 32-bit ARM CPU) is not an officially supported platform for MongoDB, nor is it likely to be. The MMAP storage engine (which is the only 32-bit storage engine available for MongoDB) also has a limitation of ~2GB of data on 32-bit deployments as per the underlying MMAP API. There are some community builds which might be passable for development use, but I wouldn't recommend for client/production work.

There is progress toward experimental 64-bit ARM support in the MongoDB 3.3 development branch, so support for Raspberry Pi 3 or newer is an eventual possibility. If you want to run a full-featured web application with minimal compatibility hassle, a small form factor 64-bit x86 computer is a better bet for current deployments.

  So basically, I need the Pi to be a slave, which becomes master when no inet, and writes to the master when the inet is back up

This isn't a supported replication model as at MongoDB 3.2; a replica set can only have a single primary which accepts writes. An offline/disconnected secondary would be read-only.

  "What happens when I lose my internet connection?".

Some other approaches to consider:

Set up a backup internet connection. This will minimise complexity since you don't have to worry about conflicting versions of data or applications.
Look into a different local storage solution for offline writes which is also more suited for embedded / low power devices (for example, SQLite). You'll have to work out how to resolve data editing conflicts between local & web data, though.


Is there a way to automate checking stored procedures, functions, etc. written for sql server 2008 will work in 2016?

One of my personal fav. checklists is from Thomas LaRock SQL MVP.

The first 3 are highly relevant to your question. Also note the Perfmon metric for 'Deprecated Features' that you can use to see if any are running.

  1- The SQL Server 2016 Upgrade Advisor (UA) is just that: an advisor.
  Much like a consultant, it doesn’t fix everything that is wrong, it
  merely advises you on what actions you should take when upgrading to
  SQL 2014. The actions the UA recommends will come in two forms: those
  actions to be done prior to a migration, and those actions to be
  completed post-migration. The UA is really good at finding what I call
  the “stub-your-big-toe” things that need fixing prior to a migration.
  But it is not foolproof, it will not identify every last detail. You
  will need to play the role of an actual DBA when migrating to a new
  version. Many of the items below will help you to do just that.

  REVIEWING THE “BREAKING CHANGES” SECTION IN THE BOOKS ONLINE Did you know that Microsoft publishes a list of breaking changes for each
  version of SQL Server? Well, you do now. You should review them to the
  point that they are familiar to you. You don’t have to memorize them
  all, just be familiar with them so that if something odd happens you
  can think to yourself “…hey, is this odd behavior listed in the
  breaking changes section of the Books Online (BOL)”? I would like to
  believe that the UA will alert you to many of these breaking changes
  but the truth is the UA is not as dynamic as the BOL. That means the
  BOL may have an entry or two that doesn’t make it into the UA
  checklist, and that is why you should review this section.
  REVIEWING THE “BEHAVIORAL CHANGES” SECTION IN THE BOOKS ONLINE Similar to the breaking changes, the behavioral changes are changes
  that could still affect you in an adverse way. They are definitely
  worth reviewing, and they are also things that the UA is likely to
  never report back to you about because they aren’t things that will
  break, but merely things that could break. Also worth noting is that
  the BOL appears to have two entries for behavioral changes, one for
  SQL Server features, and one specific for the database engine.  I’d
  advise you keep your eye on both going forward.


Is the ADO.NET Entity Framework known for generating poorly performing queries?

It depends on what the queries are. ORMs are usually really good at CRUD, as they are usually simple. The more complex the query, the greater the chance of a bad query. You can tweak the generated queries by tweaking the LINQ statements. Sooner or later though, you'll get tired of fighting and use SQL queries or stored procedures for anything that is complex.


Efficient SQL database design for recording examples of a word

I would have 2 tables: Word: word_id, word

Then Example: word_id,example_sentence

This will likely be more efficient in terms of storage since you only need 4 bytes to store an id and possibly many more to store duplicates of a word.


Use a CASE if you want, but you can always check if COUNT() > 0

SELECT usl.*,
    CASE WHEN COUNT(blip.id) > 0 THEN TRUE ELSE FALSE END as blacklisted
FROM user_session_logs as usl
LEFT JOIN blacklisted_ips as blip ON blip.ip_address = usl.ip_address
GROUP BY usl.id;


On product what you have is a Type 2, Slowly Changing Dimension.

A More in-depth discussion on Slowly Changing Dimensions here

A model for this would look something like the following:

Customer stays as is.

Product has a hierarchy (self referencing to the parent [previous version]
Product has a different ProductId for each version.

You did not mention whether multiple versions are valid at the same time.
Let us assume not.

Valid_From:Valid_To are one way to identify when a product is valid. The 'current' product version has the valid_from set, but valid_to NULL.

When it is time to move to the next 'Version':

Update the current records Valid_To date timestamp to now()
Get the next ProductId (nextval on a sequence ususally)
Get the next Version number (derived from the current version
number)
Insert new record into Product, valid_From to now(), use new version
number, reference the old parentId in the Parent_prodid
Commit

You will get new ProductIds for each version, but you will be able to give details of the product/customer relationship over time.

'ownership' of a product may be assigned at any version number. You can cascade the ownership from by maintaining the appropriate CUST_PRODUCT entries, they can be singular (for a specific version) or multiple for all or a subset of the versions.

You can find all versions by cascading thru the self referencing structure until you get the first (oldest, smallest version number, ...)