php/mysql database redesign and migration: changing databases to tables

Instead of Migration to another System, try doing it in place, then mysqldump DATA_ONE and import that mysqldump into the new server.
First, let's create DATA_ONE
DROP DATABASE IF EXISTS DATA_ONE;
CREATE DATABASE DATA_ONE;

Next, Create DATA_ONE tables using the template from database aa123
CREATE TABLE DATA_ONE.personal LIKE aa123.personal;
CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;
CREATE TABLE DATA_ONE.games    LIKE aa123.games;

Now, Create a UserInfo Table
CREATE TABLE DATA_ONE.UserInfo
(
    userid INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(32),
    last_update DATETIME NOT NULL,
    PRIMARY KEY (userid),
    KEY (username)
);
INSERT INTO DATA_ONE.UserInfo (username,last_update)
SELECT database_name,last_update FROM users.userinfo;

Then, add the database_name column (and index it) to the DATA_ONE tables
ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);
ALTER TABLE DATA_ONE.personal ADD userid INT;
ALTER TABLE DATA_ONE.accounts ADD userid INT;
ALTER TABLE DATA_ONE.games    ADD userid INT;
ALTER TABLE DATA_ONE.personal ADD INDEX (username);
ALTER TABLE DATA_ONE.personal ADD INDEX (userid);
ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);
ALTER TABLE DATA_ONE.games    ADD INDEX (userid);

OK, that makes the new DATA_ONE database.
Now comes the adventurous part: Loading all those tables into DATA_ONE.
Run these three queries to formulate the SQL needed for migration
SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;

Here is everything mentioned above shell-scripted and executed:
SQLFILE=/root/MakeDATA_ONE_Database.sql
echo "DROP DATABASE IF EXISTS DATA_ONE;" > ${SQLFILE}
echo "CREATE DATABASE DATA_ONE;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.personal LIKE aa123.personal;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.games    LIKE aa123.games;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.UserInfo (userid INT NOT NULL AUTO_INCREMENT,username VARCHAR(32),last_update DATETIME NOT NULL,PRIMARY KEY (userid),KEY (username));" >> ${SQLFILE}
echo "INSERT INTO DATA_ONE.UserInfo (username,last_update) SELECT database_name,last_update FROM users.userinfo;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (username);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD INDEX (userid);" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -A < ${SQLFILE}

Now open mysql-workbench or phpmyadmin or whatever you browse data with and look through the DATA_ONE database.
If you are satisfied with what is there, just do this:
mysqldump -u... -p... --databases DATA_ONE > /root/DATA_ONE.sql

Move the DATA_ONE.sql file over to the new server and execute the script.
That's it. Pure SQL solution. No PHP.  If anything does not work, it is probably because I do not know the layout and current contents of the personal, account, & games tables and made assumptions on data. Please post the following in your question:
SHOW CREATE TABLE aa123.personal\G
SHOW CREATE TABLE aa123.accounts\G
SHOW CREATE TABLE aa123.games\G

Give it a Try !!!
UPDATE 2012-05-23 13:57 EDT
Oh, you have 2500 databases? My above solution should still work.
I do have an alternative suggestion: Try to mysqldump all databases into individual files.
Here is a shell script that will dump all 2500 databases into separate files and gzipped
MYSQL_CONN="-uusername -ppassword"
SQLSTMT="SELECT DISTINCT table_schema FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt
MYSQL_CONN="-uusername -ppassword"
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
COMMIT_COUNT=0
COMMIT_LIMIT=50
for DB in `cat /tmp/ListOfDatabases.txt`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Once you execute this, all databases will be mysqldump'd. For example, the database aa123 will be dumped and gzipped to aa123.sql.gz. This and all the other 2499 mysqldumps can be copied over to the new DB server. Place the dumps in /root/mysqldata on the new DB server. Once they are on the new DB server, you can then unzip all files like this:
cd /root/mysqldata
for X in `ls *.sql.gz` ; do echo ${X} ; gzip -d ${X} ; done

OK this will unzip all the files.
Now, run this to load the SQL scripts into mysql on the new server
cd /root/mysqldata
for X in `ls *.sql` ; do echo ${X} ; mysql -u... -p... -A < ${X} ; done

That's it. No PHP involved. All iteration is handled in the shell script I provided. You can then combine them in the new DB Server as DATA_ONE like I mentioned before.
Give it a Try !!!


Is it acceptable to use deadlocks as a strategy?

Snapshot and Read Committed Snapshot isolation levels do not prevent deadlocks from happening. They make the engine using row versioning, so readers do not block writers and writers do not block readers as SQLServer does by default. That improves concurrency , but requires changes to the applications that rely on  "readers block writers and writers block readers" . I guess you misunderstood your manager , and your system relies on that SQLServer default blocking behavior, not on deadlocks which in most cases can be avoided by application design.

If I happen to work with SQLServer on a new system, I'll surely switch it to use row versioning instead of locking.

However, for an existing system, it is usually safer and cheaper not to fix what is not broken (especially when it's not broken).


MySQL replication between VPS and shared host

if you have MySQL root access on the shared host, there is a change that it works, just make sure that you:

Use the VPS as master
Enable the shared host ip to connect on vps host
run a SELECT @@server_id on shared host and set the vps to use a different one.
follow the steps described on tutorial
run the CHANGE MASTER TO on SQL tab on PhpMyAdmin


Right,

For anyone else who might come across this post - I am just adding this information for the sake of completeness.

It took me a long time to get this sorted. I booted off of a SystemRescueCD and ran testdisk. It managed to recover my LVM partitions, which I couldn't mount due to damage to the disks. I tried numerous file recovery tools. R-Studio was probably the most helpful, it managed to recover file fragments.
I also ran Scalpel version 2.0. This version allows for specification of minimum file size, which was important in my case since I knew how big the files were. Scalpel took roughly 3 days, but we managed to recover our 5 data files. However without any of the other files (controlfiles, archived redo logs etc) recovery was still a challenge. I managed to create new controlfiles and rebuild the spfile from a previous copy of the pfile. I ended up starting the database with resetlogs. So no far we haven't had any reports of missing data.

We were incredibly lucky. While I would say that even in the worst imaginable scenario there might be hope for recovery, it is in no way an excuse to eschew prescribed backup methods. I cannot overstate how lucky we were that we managed to salvage our data, after having to deal with a malfunctioning RAID controller, corrupt partitions and missing files. My employers have now taken a more serious and pragmatic view towards offsite backups. but I wouldn't want to see anyone in this position. Like they say - prevention is better than cure.

For reference :
Oracle Data File Headers in Hex : 00 A2 00 00 FF C0 ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? 7A 7B 7C 7D
Oracle Data File Footer : 00 00 00 01
The question marks represent characters which will be different for every data file. Scalpel allows you to specify wildcards.
R-Studio also allows defining a specific file type and searching for that file type, but I didn't try it.

Thanks to everyone who took time to share their advice. I am grateful to you all.

Regards

Alex


How can I import data from a NetCDF file into SQL Server?

My solution was to create a SSIS Package, which has a C# script task which used Microsoft Scientific Data Set. (SDS)

The C# script then loops through the data and passes it back to SSIS, when then inserts it into SQL server.

Once I had the basics running I asked on other SE sites for help and my example code is there.

My data files contained Meterological data for all of New Zealand, but i was only interested in a small specific area. As the data was geospatial with reliable coordinates, my script could be more selective about what it returned, saving time and a lot of unnecesary I/O.

I am extremely grateful for the help people gave me. And I would like to be able to help others in the same way. If you have question DM me or add a comment here and I will try and help as best i can.


Can an application connect to databases from on-premise SQL Server Instance and Azure SQL together?

Yes, you can use so many connections you need.

For instance, if you're programming for .Net framework simply instantiate two or more SqlConnection class and set up the ConnectionString property according the DB you are trying to access.

You can find some examples about ConnectionString's here.

And MS Docs about Azure ConnectionString's here.


Is there a way to have PostgreSQL consider the Interpunct character · as a letter?

Phew... took ages, but I finally figured it out! It's a bit of a hack, but it was the only way I could find to do it.

My train of thought went like this:

SELECT ASCII('·'); Result: 183

After a lot of experimentation (see exhaustive fiddle here), I came up with this:

WITH cte AS
(
  SELECT CHR(112) || CHR(104) || CHR(183) || CHR(97) AS my_string
)
SELECT my_string FROM cte;

Result:

my_string
ph·a

which, as you can see, contains an interpunct character. I was forced to use the ASCII number representations because dbfiddle.uk doesn't appear to treat · - interpunct - as a "proper" letter.

So, then I did this:

WITH cte AS
(
  SELECT CHR(112) || CHR(104) || CHR(183) || CHR(97) AS my_string
)
SELECT 'ph·a' ~* (SELECT my_string FROM cte) AS test_result;

Result:

test_result
t

Which is the correct answer.

I know that it's a lot of trouble to go to (believe me, I was beating my head off the table betimes! :-) ), but it was the only way that I could figure it out. It is possible that with the right character set (utf8mtb4) or similar, interpunct may be treated as a proper letter - but it's the best I could do for the moment! HTH & A+! p.s. +1 for the question BTW, v. interesting and I learnt a lot! :-)


Resolving Cyclical Reference in Referential Relationships

Since you are using SQL Server Compact Edition, which doesn't support triggers that could solve this issue, my recommendation is either:

write your own manual DELETE statements in the proper order, rather than use CASCADE
change the design
use Express / LocalDB instead of Compact Edition, which is extremely limited and has been deprecated


Can I let PostgreSQL report the offending rows when a multi-row INSERT fails?

When inserting multiple rows

Not sure what exactly you mean. It should already report the content of the offending rows.

# CREATE TABLE foo ( x int );
-- Two rows that work.
# INSERT INTO foo VALUES (1), (2);
INSERT 0 2
-- Three rows that explode.
# INSERT INTO foo VALUES (1), (2), ('a');
ERROR:  invalid input syntax for integer: "a"
LINE 1: INSERT INTO foo VALUES (1), (2), ('a');
                                          ^

What more content are you looking for?


create one private database link for two users oracle

When you select from a table with dblink first time in current session, then oracle needs to connect from one server to another.

So your current oracle instance is SRV1, and other is MYDB. When you issue first time in current session

select * from t@mylink

then oracle create session from SRV1 instance to MYDB instance. How oracle do it? Sends the connect command with username, password and TNS name (MYDB here). This information we provide when create dblink - user, password, TNS name (db name).

You need TWO private database links, first for one user (A), and the second for another (B).

SQL> connect a/password@SRV1
SQL> create database link mylink connect to user_from_MYDB identified by some_password...

SQL> connect b/password@SRV1
SQL> create database link mylink connect to user_from_MYDB identified by some_password...


How to check which database roles can see `masked` columns?

How to check which database roles can see masked columns?

Normal query which checks for users permissions seems to do the trick

    select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

I tested it on below sample script from msdn

CREATE TABLE Membership
  (MemberID int IDENTITY PRIMARY KEY,
   FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
   LastName varchar(100) NOT NULL,
   Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
   Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);

INSERT Membership (FirstName, LastName, Phone#, Email) VALUES
('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'),
('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'),
('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net');

CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON Membership TO TestUser;

GRANT UNMASK TO TestUser;
EXECUTE AS USER = 'TestUser';
SELECT * FROM Membership;
REVERT;

now querying gives me below result


Here's an article I wrote for how to get one installed on Ubuntu.

Installation instructions for the pldebugger for PostgreSQL

pg_config --version
"9.3beta2"

Look here for source code matching your version of PostgreSQL:
ftp://ftp.postgresql.org/pub/source/

For me, it was:

wget ftp://ftp.postgresql.org/pub/source/v9.3beta2/postgresql-9.3beta2.tar.bz2
tar xvjf postgresql-9.3beta2.tar.bz2
cd postgresql
USE_PGXS=1 ./configure
USE_PGXS=1 make
cd contrib
git clone git://git.postgresql.org/git/pldebugger.git
make
cd pldebugger
USE_PGXS=1 make
sudo USE_PGXS=1 make install

These instructions assume that you have the development packages for your version of PostgreSQL already installed on your system.


getting error when i use merge statement

Try using the keywords TARGET and SOURCE.

MERGE products AS target
  USING updatedproducts AS source
  ON (target.productid = source.productid)
  WHEN MATCHED and target.productname <> source.productname OR
    target.rate <> source.rate
    THEN UPDATE
     SET
      target.productname = source.productname
     ,target.rate = source.rate
  WHEN NOT MATCHED BY TARGET
    THEN INSERT
      (productid
      ,productname
      ,rate)
     VALUES
      (source.productid
      ,source.productname
      ,source.rate)
  WHEN NOT MATCHED BY SOURCE
    THEN DELETE
  OUTPUT
    $action
   ,deleted.productid AS trgtproductid
   ,deleted.productname AS trgtproductname
   ,deleted.rate AS trgtrate
   ,inserted.productid AS srcproductid
   ,inserted.productname AS srcproductname
   ,inserted.rate AS srcrate;
SELECT
  @@rowcount;

Should work like a charm


How do I add composite MySQL Indexes with denormalization and JOINS?

Do you need all 20 columns from transaction_events? If not, then getting rid of * and specifying only the columns you need not only reduces the amount of data you're pulling back at one time but also reduces the chances of a sub-optimal query plan. It's possible the query plan generated will vary based on the columns in your SELECT clause.
You could test adding an index on the customer_id field and another index on the merchant_id field for your JOIN clauses and see if it improves performance and produces a better query plan. But this will require testing and comparison of the EXPLAIN for each case.


If an invitations is just an invitation, then it seems simplest to store all of the users for an invite in their own row in a separate table.

Users
 - UserID, PK
 - other details about the users

Invitations
 - InvitationID, PK
 - OwnerID or OwningUserID or just UserID, FK to Users
 - other details about the invitations

InvitationUsers
 - InvitationID, FK to Invitations
 - UserID FK to Users
   [PK on (InvitationID, UserID)]

A lot of people are tempted to store all of the users of an invitation in a comma-separated list or as XML. Resist this temptation; it leads to nothing but trouble.

If there is only a single type of invitation (like "be my friend on Facebook and we can hug and all that") then a simpler model might be:

Users
 - UserID, PK
 - other details about the users

Invites
 - SenderID, FK to Users(UserID)
 - RecipientID, FK to Users(UserID)
   [PK on (SenderID, RecipientID)]

If an owner can send an invitation to various things (a dating service, a boat show and a movie) then we'll need more details about what those relevant things are.


ODBC and ADO connection to SQL Server: What protocol is used when connecting to \?

If you don't specify the protocol in the connection string, then it will use the protocol specified for the database driver. The protocol order, by default, is Shared Memory, TCP/IP and then Named Pipes. You could also set a default protocol.

To specify which protocol to use in a connection string, add tcp: before the server name. Examples: tcp:server1\instance1, tcp:server2, tcp:server3,1433

You can use SQL Server Configuration Manager to see the protocol order or regedit to see the default and protocol order.

The regkey is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0 for 64-bit applications and HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\SNI11.0 for 32-bit applications.


Primary file after table partitioning - SQL Server 2014

This might not be a completed answer but I cannot put all these in comment section.  Once you edit your question with more information I will edit the answer if required.

I am sure you did not get the exact error message you mentioned in the question. Transaction log disappeared since there was no room due to ACTIVE_TRANSACTION --Did you mean you transaction could not be completed because log file ran out of space?

  the partition is successful and that this is an indexing issue.

Use below query to find out details about your partitions.

SELECT
    OBJECT_NAME(SI.object_id) AS PartitionedTable
    , DS.name AS PartitionSchemeName
    , PF.name AS PartitionFunction
    , P.partition_number AS PartitionNumber
    , P.rows AS PartitionRows
    , FG.name AS FileGroupName
FROM sys.partitions AS P
JOIN sys.indexes AS SI
    ON P.object_id = SI.object_id AND P.index_id = SI.index_id
JOIN sys.data_spaces AS DS
    ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
    ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
    ON PF.function_id = PS.function_id
JOIN sys.destination_data_spaces AS DDS
    ON DDS.partition_scheme_id = SI.data_space_id
    AND DDS.destination_id = P.partition_number
JOIN sys.filegroups AS FG
    ON DDS.data_space_id = FG.data_space_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.type IN(0,1)
ORDER BY PartitionedTable, partitionnumber

  the Primary is still taking up the full 230GB or is it just allocated
  space that appears used on my drive. If so, can I safely shrink this
  without fragmentation?

Run below query to find out how much of 230GB is being used/free.

SELECT  NAME = LEFT(a.NAME, 64) ,
    a.FILEID ,
    [FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)) ,
    [SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,
                                                          'SpaceUsed')
                                                    / 128.000, 2)) ,
    [FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND(( a.size
                                                      - FILEPROPERTY(a.name,
                                                          'SpaceUsed') )
                                                    / 128.000, 2)) ,
    [PERCENT_FREE] = CONVERT(DECIMAL(12, 2), ( CONVERT(DECIMAL(12, 2), ROUND(( a.size
                                                          - FILEPROPERTY(a.name,
                                                          'SpaceUsed') )
                                                          / 128.000, 2))
                                               * 100 )
    / CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))) ,
    FILENAME = LEFT(a.FILENAME, 66)
    FROM    dbo.sysfiles a

  there is an issue with the missing transaction log. If not, where is
  it going?

If your database is online (as you mentioned you are able to query the database) your transaction log file is not missing.


What you're seeing is mostly one user interface's way of displaying the structure of a PostgreSQL database.

If you were using pgAdminIII, which is just another administrative interface, each database would usually show two "catalogs": information_schema and pg_catalog. It would also have a schema named "public".

Database objects named "pg_*" are system objects. The pg_toast schema holds TOAST storage for large tables.

The information_schema views are part of the SQL standard. They're supposed to provide a vendor-independent way to determining information that's stored in vendor-specific system tables.

You can make a good case for not exposing every namespace as if it were a user-level schema. (Internally, these are namespaces. Try select * from pg_namespace;.)  The only schema in a newly created database that really matters to users is "public".


I prefer Galera for HA.  Master-Master, especially if you write to both masters, has a lot of problems.  Read other Questions on this topic.


Who changed/modified stored procedure in SSMS 2005 or 2008?

I know this doesn't solve your immediate problem, however the best way to pro-actively manage this type of event moving forward may be to create a DDL Database Trigger that logs events into a design log table such as:

CREATE TABLE dbo.DesignLog(
    DesignLogID int NOT NULL PRIMARY KEY CONSTRAINT PK_DesignLog IDENTITY(1,1),
    DateStamp datetime NULL CONSTRAINT DF_DesignLog_DateStamp  DEFAULT (getdate()),
    HostName nvarchar(255) NULL CONSTRAINT DF_DesignLog_HostName  DEFAULT (host_name()),
    UserName nvarchar(255) NULL CONSTRAINT DF_DesignLog_UserName  DEFAULT (suser_sname()),
    ActionType nvarchar(255) NULL,
    StatementExecuted nvarchar(max) NULL
);
GO
CREATE TRIGGER [DesignLogTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
    /*
        Authored By:                    Max Vernon
        Author Date:                    2012-07-05
        Purpose:                        Capture DDL Statements executed against database
    */
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @ActionType nvarchar(max);
    DECLARE @Statement nvarchar(max);
    DECLARE @msg nvarchar(max);
    SET @data = EVENTDATA();
    SET @msg = cast(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)') as nvarchar(max));
    SET @msg = dbo.xmltostring(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)'));
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @ActionType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)');
    SET @Statement = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');
    IF @msg NOT LIKE 'UPDATE STATISTICS' + char(37) + ''
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REORGANIZE' + char(37) + ''
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REBUILD' + char(37) + ''
    BEGIN
        BEGIN TRAN;
        INSERT INTO DesignLog
            (
             ActionType
            , StatementExecuted
            )
        VALUES
            (
             @ActionType
            , @Statement
            );
        COMMIT TRAN;
    END
END;
GO
ENABLE TRIGGER [DesignLogTrigger] ON DATABASE;

This particular DDL Trigger does not log certain events such as UPDATE STATISTICS, INDEX REBUILD, AND INDEX REORGANIZE.  We are not interested in logging these events since these events happen during our nightly database maintenance tasks, and therefore crowd out the meaningful changes made by developers.

The advantage of this particular trigger is that it captures the actual SQL text of the event, thereby creating a log of changes made to all objects in the database.  This allows you to revert back to a prior version of any given object - a sort of manual revision control system.


How do I import the _id index from a MongoDB collection as a hash index for a PostgreSQL table (from a CSV export from MongoDB)?

MongoDB uses a BSON object 24 character hash for its index (_id)

Values are stored in columns in a relational database, not "in an index". To store such a character (aka "string") value, create a column of type varchar(24) to hold the _id value from MongoDB.
If you want to ensure uniqueness, then create a unique B-Tree index on the column or defined it as the primary key.
Something along the lines:
create table mongo_data
(
  id varchar(24) primary key,
  ... other columns ...
);

The fact that the value is computed by a "hash algorithm" in MongoDB has nothing to do with a "hash index" in Postgres.


Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name

It seems a bug (MySQL 5.5 and 5.6). A temporary solution that can be useful is:

SELECT `t1`.`c1`, `der`.`_c1`
FROM `t1`, (SELECT `c1` `_c1`, `c2` `_c2`
            FROM `t2`) `der`
GROUP BY `t1`.`c1`, `der`.`_c1`
HAVING `t1`.`c1` > 1 AND `der`.`_c1` > 1;

SQL Fiddle demo

In MySQL 5.7.11, the query:

SELECT `t1`.`c1`, `t2`.`c1`
FROM `t1`, `t2`
GROUP BY `t1`.`c1`, `t2`.`c1`
HAVING `t1`.`c1` > 1 AND `t2`.`c1` > 1;

runs smoothly.


mysql 8.0, updates are sometimes stalling

That's an 8.0 optimizer issue. Can easily be fixed either doubling the range_optimizer_max_mem_size (and, if this is not helping, doubling it further and further) or just by setting it to zero, this way it can use up to all the amount of available memory.

Did the trick for me. Unfortunately, I wasn't the guy to figure it out, but rather guys from paid percona support desk. I was told this is likey to happen on huge tables.


If you don't care which row gets updated, you can use ROW_NUMBER with an arbitrary order:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY nrs ORDER BY (SELECT NULL))
    FROM @Test1
)
UPDATE a
SET a.amtonhand = b.amtonhand
FROM CTE a
INNER JOIN @Test2 b
    ON a.nrs = b.nrs
WHERE a.RN = 1;

The results are:

╔═════╦═════════╦═══════════╗
║ nrs ║ amtrqst ║ amtonhand ║
╠═════╬═════════╬═══════════╣
║ abc ║      10 ║ 10        ║
║ abc ║      12 ║ NULL      ║
║ abc ║      13 ║ NULL      ║
║ def ║       5 ║ 5         ║
║ def ║      12 ║ NULL      ║
╚═════╩═════════╩═══════════╝


You can use a RECURSIVE query for this job:

CREATE TABLE accounts(account varchar(10), account_parent varchar(10), updated_field int);
INSERT INTO accounts VALUES
('10000', NULL, 0),
('10001','10000', 0),
('10002','10001', 0),
('10003','10002', 0),
('10004','10003', 0),
('10005','10004', 0),
('10006','10005', 0),
('10007', NULL, 0),
('10008', NULL, 0),
('10009', NULL, 0),
('10010', NULL, 0),
('10011', NULL, 0);

GO

12 rows affected

;WITH tree AS
(
    SELECT c1.account, c1.account_parent, Level = 1
    FROM dbo.[accounts] c1
    WHERE c1.account = '10000'
    UNION ALL
    SELECT c2.account, c2.account_parent, Level = Level + 1
    FROM dbo.[accounts] c2 INNER JOIN tree ON tree.account = c2.account_parent
)
UPDATE accounts
SET    updated_field = Level
FROM   tree
INNER  JOIN accounts
ON     accounts.account = tree.account
OPTION (MAXRECURSION 2000)
;
GO

7 rows affected

SELECT * FROM accounts;
GO

account | account_parent | updated_field
:------ | :------------- | ------------:
10000   | null           |             1
10001   | 10000          |             2
10002   | 10001          |             3
10003   | 10002          |             4
10004   | 10003          |             5
10005   | 10004          |             6
10006   | 10005          |             7
10007   | null           |             0
10008   | null           |             0
10009   | null           |             0
10010   | null           |             0
10011   | null           |             0

Keep in mind you must set a MAXRECURSION value greater enough to get all updated records.

Updating records where parent is null

To update the rest of the records where account_parent is null I've used a CTE function with LAG().

Note the CTE uses WHERE    account >= '10006' to flag the last record of the previous tree structure. The following records must have account_parent = null

WITH upd AS
(
    SELECT   account, account_parent,
             LAG(account) OVER (ORDER BY account) AS prev_account
    FROM     accounts
    WHERE    account >= '10006'    -- this is last element of the tree
)
UPDATE     acc
SET        account_parent = upd.prev_account
FROM       accounts acc
INNER JOIN upd
ON         upd.account = acc.account
WHERE      upd.prev_account IS NOT NULL
;
GO

5 rows affected

This is the final result:

SELECT * FROM accounts;
GO

account | account_parent | updated_field
:------ | :------------- | ------------:
10000   | null           |             1
10001   | 10000          |             2
10002   | 10001          |             3
10003   | 10002          |             4
10004   | 10003          |             5
10005   | 10004          |             6
10006   | 10005          |             7
10007   | 10006          |             0
10008   | 10007          |             0
10009   | 10008          |             0
10010   | 10009          |             0
10011   | 10010          |             0

dbfiddle here


Auto Increment Primary ID inserting random values

I'm having a very difficult time thinking of any possible misbehaving mechanism inside MySQL or InnoDB that could cause this behavior as the result of a bug, without throwing errors or causing a crash.  It seems much more likely that the table is being updated by the application.  Instead of writing new rows, something is finding and updating old ones.

If that's not supposed to be happening, I would put a trigger on this table to block those updates.

mysql> CREATE TRIGGER txn_bu BEFORE UPDATE ON txn FOR EACH ROW
       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'this table does not support updates';
Query OK, 0 rows affected (0.14 sec)

mysql> UPDATE txn SET `timestamp` = NOW() WHERE id = 1;
ERROR 1644 (45000): this table does not support updates

mysql>

This makes directly updating rows impossible.  Note that you don't need the usual DELIMITER change and BEGIN/END blocks because the trigger is comprised of a single simple statement.

If the table is never updated then the trigger would never fire, so there's no significant overhead associated with having it defined.  If it sees an update query then it's going to throw an exception back to the application which should generate exception reports or support tickets.  Arguably, if your application has a defect that's stomping data, you want it to fail hard, instead of soft.


How can I determine how many IOPs I need for my AWS RDS database?

You'll have to test it.

You could do some back of the envelope calculations to approximate the number of I/Os per insert, multiply it by the number of transactions per second, add in some buffer room etc, but it's much easier to just test it.

The easiest thing to do is to allocate a best guess, then go back and increase or decrease it to match you're real world testing. This is one of the luxuries of using a cloud based environment, hardware changes are low in capital costs and such changes usually require only config updates. With EBS volumes you can't just increase the number of IOPS, you'd have to scale up the size of the volume as well1. You can always just create a new volume and copy your data over. There will be some downtime but if you're data isn't huge it shouldn't be much as it'd be a raw copy.

Here's a guess at the number of I/Os necessary. Again it's just a guess though as the specifics depend on the number of indexes and whether your traffic flow will be smooth or spikey. At 25K tx/hour you've got ~7 tx/sec. The size of each row isn't particularly relevant as it's less than the size of a single I/O (4K). Each transaction will do somewhere between 1-5 IOP (primary insert plus a couple index tree inserts) so let's say ~35/s.

I say start out with the bare minimum of  100 IOPS and scale up if necessary.


What happen if innodb_log_file become full?

When innodb_log_file ib_logfile0 is full it start to write innodb_log_file ib_logfile1.

When this is full, it start to over with innodb_log_file ib_logfile0.

The only thing is to have enough space for both files.

The size is determined by `innodb_log_file_size=64M, but be careful mysql when you want to change the size, you need stop the server and delete the old ones. when you restart they will be created new.


If you're willing to forgo writing to STDOUT, you can get the number of rows that COPY exports with a PL/pgSQL function along the lines of:

CREATE OR REPLACE FUNCTION copyout(query text, output_path text) RETURNS integer AS $fn$
DECLARE
    result integer;
BEGIN
    EXECUTE 'COPY (' || query || $$) TO '$$ || output_path || $$'$$;
    GET DIAGNOSTICS result = ROW_COUNT;
    RETURN result;
END
$fn$ LANGUAGE plpgsql;

Execute with something like this (note the path is on the server not the client):

> SELECT copyout('SELECT * FROM mytable', '/tmp/myfile.csv');
 copyout
---------
     981
(1 row)

However - just found out this was added in 9.3 (see 9.3 Release Notes). Sorry.


Linked server to remote sql server failed

As I can see from the your error messages you are connecting to the server from different domain. So your cuurent windows credentials cannot be used as Be made using the login’s current security context implies.

Option Be made using this security context implies that during authentication SQl Server will use the credentials specified in the text boxes (SQL Server authentication).

So the answer is - you can use the method Be made using this security context with the credentials you specified, or, alternatively you can specify how every local user will authenticate on the remote server (specifying that no authentication is made for any login not in the list above):


Running several versions of a MySQL database in parallel?

One must separate: What do you want to "version"? The interface or the data?

Versioning the interface

If you only want to version the interface, you should (must) manage one single base of data and provide it in different formats at different interfaces. Otherwise you will run into consistency issues, if your data should change (and even geo data changes -- the rarity of changes makes it even more vulnerable to consistency issues, because this is done so infrequent that humans tend to do mistakes).

In this case I would use one single database and VIEWs for different interfaces. This is harder to setup, but saves headache in the long run.

An alternative would be to replicate the data periodically by a cronjob, which might be a waste of resources if this changes very infrequently.

You could also implement a script and make somehow sure it is always executed if some data changes. (Vulnerable to human errors this is.)

Versioning the data

If you want to version everything including the data, your approach seems fine to me. Although it might be more efficient to just use another database instead of a completely different server instance.


Oracle 11g Fast Add Column Taking 11 Hours

Ok, I sacrificed one of my 10.2 sandbox databases for a good cause, and upgraded it to 11.2.

As I suspected, the above DDL optimization does not work with compatible parameter still set to '10.2.*'.

After increasing compatible parameter, it works as intended:

SQL> show parameter compa

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction              string      ADAPTIVE
compatible                           string      10.2.0.5.0
plsql_v2_compatibility               boolean     FALSE

SQL> alter table t1 add C_DDL number default 42 not null;

Table altered.

Elapsed: 00:00:52.85
SQL> alter system set compatible='11.2.0.0.0' scope=spfile;

System altered.

Elapsed: 00:00:00.00

SQL> shu immediate
startup
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             289407880 bytes
Database Buffers          771751936 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.

SQL> alter table t1 add D_DDL number default 42 not null;

Table altered.

Elapsed: 00:00:00.04


Login failure when running a SSIS package from a SQL Server job

I had a similar problem while importing data from an Excel file using SSIS packages. I found this tutorial and solved the problem.

EDIT: Important steps:
1 - When saving the SSIS package to the file system, select the "Package protection level" to "Do not save sensitive data".

Then, follow these steps:
1 - Open the SSIS package in Visual Studio
2 - Select SSIS -> Package Configurations
3 - On the Package Configurations Organizer click Add
4 - On the window that'll open click Next
5 - Set the configuration type to XML configuration file
6 - On the Configuration file name click Browse, select the folder in which you want to store the configuration file, choose a name to this file and then hit Save
7 - When you're done here, click Next
8 - Choose the right connection manager and click on the + sign to expand it, then expand Properties and check the checkbox correspondent to Password. Then click Next.
9 - Choose a name to your configuration file and click Finish.

The following XML configuration file will be created on the location you specified previously (here I'm using the code from the tutorial as an example):

<?xml version="1.0"?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

The only thing you'll have to do here is edit the XML file and change this:
<ConfiguredValue></ConfiguredValue>
into this:
<ConfiguredValue>YourPasswordGoesHere</ConfiguredValue>

Then save the package.

When creating your SQL job, don't forget to add the configuration file to the job in the tab Configurations.


CREATE TABLE LIKE another table but with additional columns

You can use the syntax below;

CREATE TABLE old_table_name (
  id serial,
  my_data text,
  primary key (id)
);

CREATE TABLE new_table_name (
  like old_table_name including all,
  new_col1 integer,
  new_col2 text

);

The fiddle is here


Where is the default location for the SQL Agent Job step output file? How do I update it?

If the Job Step runs a Maintenance Plan, then the logging is perhaps being done in the "Maintenance Plan" which is somewhat common. Choose the specific Maintenance Plan (from SSMS -> Management -> Maintenance Plans) that is referenced by the Agent Job, click on the circled icon for "Reporting and Logging" and check the logging location on it as shown in the dialog below:


Can't solve MongoParseError: Invalid connection string

Try printing the result of mongodb:\\/\\/${process.env.DB_USER}:${process.env.DB_PASSWORD}@db:27017\\/${process.env.DB_NAME} in console and use the result to connect to mongodb through mongo shell.

Also, post the final result with masked details for review.


Restored DB to Dev and test environments, now user can not login

Have you tried running EXEC sp_change_users_login @action='Report'; to see if they are orphaned for that database?

If there is a result, you can run with the Auto_Fix option to remap the user.


Get last ID of repeated values based on type and most recent sequence

In this case you should generate a sub-query with the aggregated values:

SELECT
    ID,
    TYPE,
    MAX(SEQ) SEQ,
    COUNT(*) CNT
FROM
    T
GROUP BY
    ID,
    TYPE;

ID | TYPE | SEQ | CNT
-: | :--- | --: | --:
 3 | A    |   2 |   2
 1 | A    |   4 |   4
 2 | B    |   2 |   2

And then join it with your table ON SEQ = MAX(SEQ):

SELECT
    T1.ID,
    T1.TYPE,
    T1.SEQ,
    T1.GRADE,
    T2.CNT
FROM
    T T1
JOIN
    (SELECT
         ID,
         TYPE,
         MAX(SEQ) MAX_SEQ,
         COUNT(*) CNT
     FROM
         T
     GROUP BY
         ID,
         TYPE) T2
     ON T2.ID = T1.ID
     AND T2.TYPE = T1.TYPE
     AND T1.SEQ = T2.MAX_SEQ
WHERE
    T1.TYPE = 'A';

ID | TYPE | SEQ | GRADE | CNT
-: | :--- | --: | :---- | --:
 3 | A    |   2 | W     |   2
 1 | A    |   4 | C     |   4

db<>fiddle here


Will running a large query on a secondary database in an availability group affect transaction performance in the primary database?

It depends on the sync mode you have used when configuring Availability group - Sync or Async !

On Secondary replica, all the transactions use Snapshot Isolation level ONLY and all locking hints are ignored as well. That's why it's important to test your workload when embracing AlwaysON.

From : Minimizing blocking of REDO thread when running reporting workload on Secondary Replica

  While mapping reporting workload to snapshot isolation eliminates blocking between DML workload as applied by the REDO thread on the secondary replica and the read or reporting workload, it does not eliminate the potential blocking of REDO thread when it is executing a DDL operation.

If using

Synchronous mode

Blocking issue on your secondary replica will impact performance of your queries on Primary replica. So a read workload (select) ran on secondary might blocked the redo thread from applying the changes that comes from primary replica. This means that the primary replica has to wait for changes to be applied to all secondary SYNC replica before it commits locally & might end-up in timeouts or blocking or deadlocking.

The REDO thread can be seen on the Readable Secondary as the DB STARTUP command in sys.dm_exec_requests. If that thread is being blocked, then your read workload on the secondary could be causing an impact on the primary.

For more details check - Scenario 1:  Blocked REDO due to large query on the secondary replica

Asynchronous mode

The primary does not wait for acknowledgement from secondary. A blocking issue on secondary is just isolated to secondary wherein the redo queue will grow on the secondary until the locks are clear and the redo thread is able to apply the log blocks. This won't affect the primary replica.

Your definition of "real-time or almost real-time" needs more thought keeping in mind the sync method used, the network latency and how busy is primary replica and the log activity that needs to be transported secondary.

SQL Server 2016 has done some major enhancements in AlwaysON realm e.g.

Improved compression algorithms & Parallel compression of log block data
4x to 5x improvement in log block transport throughput
and many others - It Just Runs Faster !


How do I find the next saturday in MySQL

You can use WEEKDAY and DATE_ADD function to calculate the next weekday incoming.

Here what you have to do:

SELECT
  DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,
                             (6-WEEKDAY(NOW())),
                             (5-WEEKDAY(NOW()))) DAY);

The query meaning:

With DATE_ADD you will add an interval between the parameter NOW() and one condition assuming my weekday for Saturday is 5, you will have to evaluate if NOW() is greater than 5 or equal, you have to rest it with 6 (Sunday) and if NOW() is less than 5 you have to rest it 5 with the weekday of NOW().

Test:

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-07-22 07:51:33 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY);
+------------------------------------------------------------------------------------------+
| DATE_ADD(NOW(),INTERVAL IF(WEEKDAY(NOW())>=5,(6-WEEKDAY(NOW())),(5-WEEKDAY(NOW()))) DAY) |
+------------------------------------------------------------------------------------------+
| 2015-07-25 07:51:34                                                                      |
+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

EDIT:

If you need to calculate to get the next Saturday if the actual Saturday is at 9:30PM:

New syntax:

SET @ACTUAL_DATE=NOW();
SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
                                            7,
                                            IF(WEEKDAY(@ACTUAL_DATE)>=5,
                                                (6-WEEKDAY(@ACTUAL_DATE)),
                                                (5-WEEKDAY(@ACTUAL_DATE))))
                                        DAY);

Today test:

mysql> SET @ACTUAL_DATE=NOW();
Query OK, 0 rows affected (0.01 sec)

mysql> SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
    -> 7,
    -> IF(WEEKDAY(@ACTUAL_DATE)>=5,
    -> (6-WEEKDAY(@ACTUAL_DATE)),
    -> (5-WEEKDAY(@ACTUAL_DATE))))
    -> DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @NEXT_SATURDAY;
+---------------------+
| @NEXT_SATURDAY      |
+---------------------+
| 2015-07-25 21:30:01 |
+---------------------+
1 row in set (0.00 sec)

mysql>

But if we're on July 25th at 21:30:01 (Saturday):

mysql> SET @ACTUAL_DATE='2015-07-25 21:30:01';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @NEXT_SATURDAY=DATE_ADD(@ACTUAL_DATE,INTERVAL IF(WEEKDAY(@ACTUAL_DATE)=5 && TIME(@ACTUAL_DATE)>'21:30:00',
    -> 7,
    -> IF(WEEKDAY(@ACTUAL_DATE)>=5,
    -> (6-WEEKDAY(@ACTUAL_DATE)),
    -> (5-WEEKDAY(@ACTUAL_DATE))))
    -> DAY);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @NEXT_SATURDAY;
+---------------------+
| @NEXT_SATURDAY      |
+---------------------+
| 2015-08-01 21:30:01 |
+---------------------+
1 row in set (0.00 sec)


Help with an entity-relationship diagram re Manager entity type and its relationships

One issue with this design is allowing for a user to have more than one course at a time. A way to work this is to have a join table between the User table and the Courses table, with only three columns: Join_id, Course_id, User_id. The relationship between users and managers looks good to me, with many users being assigned only one manager each.
Regarding one of the comments; adding to the Course_Assigned table a few fields that include a session_id, start_date, and end_date could be used to directly connect a user to a particular session.


How to write double Natural Join in SQL?

Your question is a bit unclear - does the 'Type' field
specify 'PC', 'Laptop' or 'Printer' - if so, it's relatively
easy - no need to join at all - just

SELECT * -- or whatever
FROM Product
WHERE Type = 'PC' OR Type = 'Printer'

If 'Type' is not the above, then it's trickier, but
not too difficult. BTW, 'Type' is a keyword in some
RDBMS products - I'd advise changing it to 'p_Type' or
similar.

To solve this, I did the following (SQLFiddle here):

I didn't put in referential constraints - I leave that as an
exercise for yourself :-) (especially since you haven't provided
your particular RDBMS). I also excluded the non-joining fields
(less typing for me :-) )

CREATE TABLE Product
(
  Constructor INT NOT NULL,
  Model VARCHAR(5) NOT NULL,
  Type VARCHAR(5) NOT NULL
);

CREATE TABLE PC
(
  Model VARCHAR(5) NOT NULL
);

CREATE TABLE Laptop
(
  Model VARCHAR(5) NOT NULL
);

CREATE TABLE Printer
(
  Model VARCHAR(5) NOT NULL
);

INSERT INTO Product VALUES( 1, 'm_1', 't_1');
INSERT INTO Product VALUES( 2, 'm_2', 't_1');
INSERT INTO Product VALUES( 3, 'm_3', 't_2');
INSERT INTO Product VALUES( 4, 'm_4', 't_3');

INSERT INTO PC VALUES('m_1');
INSERT INTO PC VALUES('m_2');

INSERT INTO Laptop VALUES ( 'm_3');

INSERT INTO Printer VALUES ('m_4');

The query:

SELECT * -- or whatever
FROM Product
  JOIN PC ON PC.Model = Product.Model
UNION
SELECT * -- or whatever
FROM Product
  JOIN Printer ON Printer.Model = Product.Model

Result:

Constructor Model   Type    Model
1   m_1 t_1 m_1
2   m_2 t_1 m_2
4   m_4 t_3 m_4

Et voilà - the correct result - unless I've misunderstood you?


How do I model data from arbitrary CSV's in a database?

The question is so wide open that it is hard to say much.

And why are you using a database at all? Excel does everything you've described so far.

Seriously though, avoid making an inner system in your database that can store any kind of data. You have one already, called a DATABASE. Make code that creates the tables you need. What's so bad about that? I say option 1.

You know, if you want infinite flexibility how about a square-mile white board?


Best way to determine Dev/Test/Prod environment in MS SQL Server 2012+?

You could create a utility database on each server with a table(s) to hold metadata about that server (environment, purpose, build date etc). As each utility database is tied to a server/instance, it won't get refreshed along with any databases that get refreshed so you don't have to worry about it being inaccurate if a refresh fails.
Include the deployment of this DB in your standard build process and then you can simply reference this DB in your query to determine environment. If you keep the same name throughout your SQL estate, then your queries don't need to change between servers either.


Import data into MySQL using Talend Open Studio

What parameters are You use for Talend tMySQLOutput? What MySQL settings?
It could not be faster then INFILE, but still really depend from settings.

You can post Your question on TelndForge forum with screen short of Job components, will try to help

For example one of my regular Talend-MySQL Jobs (billing prepare) - transfer from->to mysql 2M of records, 400Mb, it take 6-15 minutes depending from servers (we have 2 configuration)

edit, because always better 1 time to show, than 100 times explain

2.3M rows, 300Mb file, speed of tMySQL with 100 rows per insert:

same with 10 000 rows per insert

tBulkOutputExec:

but then total time:
original 10 000 rows:

bulk:

just because after execution it wait commit transaction, single transaction


Increase sga_max_size on Oracle 10g Windows

Normally Oracle no longer uses the init.ora file anymore. It uses the spfileSID.ora in the $ORACLE_HOME/dbs directory. This is a binary file. Tio change the parameter(s) you should use:

alter system set sga_max_size=XXXM scope=spfile;

Now restart the database and the SGA_MAX_SIZE should be set at XXXM. Now you can alter the SGA_TARGET:

alter system set sga_target=XXXM scope=both;


Sequence of dates - months from January to December with SQL

If you are using MariaDB, use something like this:

SELECT '2015-01-01' + INTERVAL seq MONTH FROM seq_0_to_11;

More discussion:  https://mariadb.com/kb/en/library/sequence-storage-engine/


How can I merge duplicate rows in a single table?

This is what's known as an aggregate query,

SELECT cart, barcode,
       SUM(counted_in) AS counted_in,
       SUM(counted_out) AS counted_out
FROM tbl
GROUP BY cart, barcode;

Using GROUP BY like this returns one record for every unique occurrence of (cart, barcode) and the totals of counted_in and counted_out for each.


Tracking the last execution date of a query

When were your servers last booted? Unless you have some form of auditing running, then that is a far back as you will be able to query the sys.dm_exec_query_stats view and sys.dm_exec_sql_text() table-valued function.

You can then use the following statement to see when a statement was last executed:

SELECT sdest.TEXT , sdeqs.last_execution_time, *
FROM   sys.dm_exec_query_stats AS sdeqs
   CROSS APPLY sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest

However, there are some restrictions with this approach:

Size of Memory reserved for SQL Server instance
Execution of DBCC FREEPROCCACHE
Execution of DBCC FREESESSIONCACHE
Execution of DBCC FREESYSTEMCACHE('ALL')
other possible reasons for SQL Server to free up cache including, but not limited to:

Server reboots
Instance restarts


Answer, because you're not reversing it in the query, and that's not implicit.

SELECT * FROM my_object_times where reverse(name) like reverse('% LastName');

However, that's a horrible idea. Instead delete both of those indexes. If you have to have non-anchored matching on something like "LastName" try the pgtrgm extension which will just work on LIKE and %. Tell us how it goes.

Install pg_trgm,

CREATE extention pg_trgm;

Create either a gist or gin index.

CREATE index test_trgm_gin ON test USING gin (some_text gin_trgm_ops);
CREATE index test_trgm_gist ON test USING gist (some_text gist_trgm_ops);

See this blog post on depesz for more information.

Why is two text_pattern_ops an inferior idea,

Two text_pattern_ops will never support '%foo%'
Not likely any library even supports this manual reverse() of all conditions.

What do I mean? Most ORM's support assembling a where clause from a hash. Here is an example for Sequalize,. {LastName: { $like: '% LastName' } } It's unlikely that this is going to ever generate, reverse(LastName) LIKE reverse('% LastName'). So the index simply won't be used unless you write it all manually: at best, that will obfuscate your use of a third party tool, at worst it'll be impossible or very difficult to make work. Where as the pg_trgm option just works.

Requires the user to know something of the index-implementation.
I'm not sure it's more space-efficient than GIST, or faster than either.

Also, as another note, if it just comes down to size you can always get another hard drive, set it up as a TABLESPACE and store the GIST index there. That doesn't solve the /space/ problem, but it should mitigate it.


Should a table be partitioned before or after an INSERT?

You can't partition an existing non-partitioned table.  So if you want a partitioned table, you would need to create a partitioned table before inserting any data.

Assuming that you are not using interval partitioning (in which case you're telling Oracle how to automatically add new partitions based on the data), you would almost certainly want to create the appropriate partitions in advance.  If you are range partitioning the table, you could, in theory, create a MAXVALUE partition for the table, load all the data, and then split the MAXVALUE partition into the individual partitions that you want.  But that would involve moving all the data around multiple times which would be much less efficient than simply loading the data into the appropriate partition from the outset.

Inserting the data "in the order of the partitions" probably won't make a difference from a performance perspective.  Oracle still has to determine for each row which partition to insert it into.  Assuming your indexes are local, it may well be more efficient to load a staging table whose structure matches the structure of a partition, create the appropriate indexes on the staging table, and then doing a partition exchange to exchange an empty partition with the staging table.


The best solution will likely depend on your database server.  In PostgreSQL, the best way would be something like this:

 BEGIN; -- start transaction
 UPDATE myrows SET exported = true WHERE exported = false
 RETURNING *;
 -- do some work in the app to process these rows
 COMMIT; -- if successful, else ROLLBACK;

If that's not possible, then create a temp table, put the info you want to export in there, update exported based on the records in the temp table, and then commit once the transaction is complete.  Remember to clean up your temp table.


From the docs

  The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

This dmv is therefore reset for each stored procedure recompile, which can happen, for example, after index and statistics maintenance


How can i know the relationship in the database in sql server 2008?

When I have encountered similar problems to this in the past, I have used the query that marc_s provides here: Know relationships between all the tables of database in SQL Server

It should give you a good place to start?

Here it is replicated in case the other thread is removed in the future (credit - marc_s):

SELECT
fk.name 'FK Name',
tp.name 'Parent table',
cp.name, cp.column_id,
tr.name 'Refrenced table',
cr.name, cr.column_id
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id =      cp.object_id
INNER JOIN
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
tp.name, cp.column_id


Duplicate records referenced across different databases

There are some great data cleansing products out there, one in particular that is actually top notch and affordable. I've come across DataMatch by Data Ladder, which is an excellent fuzzy matching and address standardization/address parsing tool used across business and would work really well for this situation. They offer a complimentary trial for new users.

In fact, an independent verified evaluation was done of the software comparing it to major software tools by IBM and SAS. There was a study done at Curtin University Centre for Data Linkage in Australia that simulated the matching of 4.4 Million records. It identified what providers had in terms of accuracy (Number of matches found vs available. Number of false matches)

1.  DataMatch Enterprise, Highest Accuracy (>95%), Very Fast, Low Cost
2.  IBM Quality Stage , high accuracy (>90%), Very Fast, High Cost (>$100K)
3.  SAS Data Flux, Medium Accuracy (>85%), Fast, High Cost (>100K)


Slow scalar function that used to be fast

You are requerying the Account_Profiles table for every single row in OrderItems.  If OrderItems has 50,000 rows then you are going to hit Account_Profiles 50,000 times.  If OrderItems grows larger you will query it more times.  If Account_Profiles grows larger then each of those times will take longer.  You will see a large difference by inlining the concatenation. (making assumptions on the join column names here)

Select Firstname + ' ' + Lastname FullName --other data here
FROM OrderItems oi INNER JOIN Account_Profiles ap
ON oi.userid=ap.userid


Is possible export to json file? Something like csv export.

Yes. Try this:

select row_to_json(tablename.*,true) from tablename;

or

copy (select row_to_json(tablename.*,true) from tablename) to stdout;

if you need copy.

See row_to_json along with other JSON functions in the documentation: JSON Functions and Operators.

I wouldn't recommend CSV on top of JSON because double quotes are special for both CSV and JSON, and they will be doubled for CSV, which makes the output less readable.

The same goes more or less for the text format of COPY. A plain SELECT without any additional quoting may be simpler.

Update: tablename can be a subquery. For the large subquery in the updated question, you may write

select row_to_json(v.*) FROM ( ...subquery here... ) AS v;

Alternatively, a temporary view may also be helpful to improve readibility or ease of use.

CREATE TEMPORARY VIEW tmpview AS ...subquery here...;

SELECT row_to_json(v.*,true) from tmpview;

The temporary view will be automatically removed at the end of the session.


Search a string across all columns of a query result

A database is not a fancy mechanism to grep. This is a horrible idea don't do this ever. That said you can convert the row object to a tsvector using Full Text Search

SELECT to_tsvector(to_jsonb(t)) @@ 'bar'
FROM ( VALUES ('foo','bar','baz') ) AS t(a,b,c);

or you can search it as if it was text.

SELECT t::text LIKE '%bar%'
FROM ( VALUES ('foo','bar','baz') ) AS t(a,b,c);


Why wouldn't you try

EXEC MetricDB.Baseline.dbo.sp_WhoIsActive

Indeed, the name of the stored procedure is sp_whoisactive, not whoisactive, unless it is renamed, of course.
It could work...
Though it brings distributed query issue with XML for me.


Postgresql installation, avoid creating “postgres” user

The full answer is in this post: https://serverfault.com/questions/929677/postgresql-installation-avoid-creating-postgres-user

The only way is to build PostgreSQL from source.


How to use FOR UPDATE with a view that is built using LEFT JOIN?

Untested, as I don't currently have postgres working, but the concept should be clear:

Try using the view in another inner join against the base table, then update the base table.

select *
from task as t
inner join outstanding_tasks as o on t.id=o.id
for update of task


Is it safe to stop mysql maintenance check in progress?

Please note what the MySQL Documentation on CHECK TABLE says about running on InnoDB tables, bulletpoint 7 under the heading CHECK TABLE Usage Notes for InnoDB Tables

  When running CHECK TABLE on large InnoDB tables, other threads may be blocked during CHECK TABLE execution. To avoid timeouts, the semaphore wait threshold (600 seconds) is extended by 2 hours (7200 seconds) for CHECK TABLE operations. If InnoDB detects semaphore waits of 240 seconds or more it starts printing InnoDB monitor output to the error log. If a lock request extends beyond the semaphore wait threshold, InnoDB aborts the process. To avoid the possibility of a semaphore wait timeout entirely, you can run CHECK TABLE QUICK instead of CHECK TABLE.

CHECK TABLE never implies doing writes, just blocking other connections in the worst. CHECK TABLE should abort itself after 4 minutes as a courtesy to other connections. In your case, if there are other processes, wait 4-5 minutes. Then kill the process. If there is nothing else running, kill the process now. Doing a shutdown with it still running may block the shutdown for 4 minutes or more, but will eventually shutdown.

Please run SET GLOBAL innodb_fast_shutdown = 0 beforehand.


Can anybody tell me what's wrong with this query?

There are two things wrong

First, use DELIMITER $$ so semicolons are interpreted inside the stored procedure and not the command line. After defining the stored procedure, switch the delimiter back to semicolon (;).

Next, UPDATE clickactivity SET Clicks = Clicks + 1; will update every row and you don't want that.

SUGGESTION #1

Add WHERE IP = ipaddress; to the UPDATE clickactivity

DELIMITER $$
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
    DECLARE ex int;
    SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
    IF ex = 0 THEN
        INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 0);
    END IF;
    UPDATE clickactivity SET Clicks = Clicks + 1 WHERE IP = ipaddress;
END $$
DELIMITER ;

SUGGESTION #2

Why do two or three queries to register a single click ?

Change the code to do the query so that it INSERTs and UPDATEs at the same time

DELIMITER $$
CREATE PROCEDURE Click(in ipaddress varchar(45))
BEGIN
    INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
    ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;
END $$
DELIMITER ;

In that event, you don't need a stored procedure. Just do the INSERT

INSERT INTO clickactivity (IP, Clicks) VALUES (ipaddress, 1)
ON DUPLICATE KEY UPDATE Clicks = Clicks + 1;

Of course, doing this works if ipaddress is the PRIMARY KEY or is a UNIQUE KEY in clickactivity.

GIVE IT A TRY !!!


associate different credit cards with every purchase

I think it's fine to do your first idea with adding the id of customer_cards as a field to the Payment_entries table. This then makes it a linking table between Order and customer_cards so you can answer the question "which customer cards have been used to place an order?".
For scalability, I would advise calling the new column something like payment_method_id in Payment_entries that way it's generically re-usable for other payment_types you may add as you scale.


So I believe what you read is dated information specifically pertaining to the first version of Availability Groups when it was released with SQL Server 2012. I found what I'm guessing is the RedGate Article written in 2014 that mentions that quote "Each Secondary replica uses 1 redo thread for each secondary database". This was known as the serial redo model.
The source article from Microsoft which that RedGate article references has since been updated (in 2020) and no longer states that's how the secondary replica works.
Furthermore I found the following Microsoft article which mentions how they've switched to multiple parallel redo worker threads per database by default, aka parallel redo model, starting with SQL Server 2014. This must be what you're experiencing.

When availability group was initially released with SQL Server 2012,
the transaction log redo was handled by a single redo thread for each
database in an AG secondary replica. This redo model is also called as
serial redo . In SQL Server 2016, the redo model was enhanced with
multiple parallel redo worker threads per database to share the redo
workload.

This is a feature that is probably not often tweaked, so I would personally recommend leaving it as is until you run into a problem you're sure is related to the parallel redo model. If you do run into an issue, then it is possible to switch back to the original serial redo model as the above article states:

To switch to serial redo model, TF 3459 needs to be enabled. But after
the SQL Server instance is running in serial redo model, the only way
to change it back to parallel redo is to restart the SQL Server
service.

Nice question by the way. :)


What are the two protocols used by SQL Server Windows authentication?

They were probably looking for an answer that discuss the "Kerberos" and "NTLM" Windows authentication protocol/schemes.

Once you know what to search for, there are lots of articles online about how these schemes relate to SQL Server:

Questions About Kerberos and SQL Server That You Were Too Shy to Ask
Understanding Kerberos and NTLM authentication in SQL Server Connections

  In the NTLM protocol, the client sends the user name to the server; the server generates and sends a challenge to the client; the client encrypts that challenge using the user’s password; and the client sends a response to the server.

...and

  Kerberos authentication (protocol) provides a mechanism for mutual authentication between a client and a server on an open network.

Comment: emphasis/meaning added
How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005


Does using PK and FK effect performance in negative way?

You should tag which database system and version you're referring to, as this could affect the answer that is appropriate. But assuming you mean one of the main modern relational database systems, there's a couple of different objects you're asking about, so here's an answer for each of them:
Primary Keys:
They do not affect performance in a negative way at all, and in fact can slightly help performance in certain database systems because they guarantee uniqueness on a set of fields in the table. It is bad relational design in most cases to not define a primary key on a table. Primary keys are not the same thing as clustered indexes, though some database systems will create the clustered index on the primary key fields by default, out of assumption the primary key fields will be used in predicates (JOIN, WHERE and HAVING clauses).
Foreign Keys:
Similar to primary keys these also have no measurable negative performance impact, and can also potentially help performance by guiding the database system to generate a more efficient execution plan in specific cases. (I find this to be a rare occurrence though and most times there's no performance difference one way or the other.)
Clustered Indexes:
The clustered index on a table can definitely improve performance when the fields of that index are commonly queried on in a predicate, and in most cases it's recommended to have one on a table. The clustered index is the logical ordering of the data in the table itself and therefore doesn't take up any additional space. Without a clustered index the table is logically stored in a heap (or similar data structure, depending on database system) which can be inefficient to read data from. A clustered index on a table can result in INSERT operations taking slightly longer, but the difference of generally milliseconds for INSERT operations is usually worth the tradeoff for the major performance improvements of SELECT, UPDATE, and DELETE queries.
The edge cases where there might be a net loss in performance from using a clustered index is when you use a staging table to temporarily store the data before it's moved elsewhere, and you don't plan to do any querying against that table other than INSERT operations or selecting the entire table.


Can a AWS PostgresSQL RDS instance be provided with a stop words file?

PostgreSQL 10

It does just that, you can see the code here,

void
readstoplist(const char *fname, StopList *s, char *(*wordop) (const char *))
{
    char      **stop = NULL;

    s->len = 0;
    if (fname && *fname)
    {
        char       *filename = get_tsearch_config_filename(fname, "stop");
        tsearch_readline_state trst;
        char       *line;
        int         reallen = 0;

**stop could be modified easily to read from the yet-nonexistant parameters, but currently it just pulls from the file. A few lines above and you'll find get_tsearch_config_filename(fname,extension) which looks in just the place you were referring too,

snprintf(result, MAXPGPATH, "%s/tsearch_data/%s.%s",
         sharepath, basename, extension);

There is nothing else in the code base that writes to a StopList pointer. As of PostgreSQL 10, it's not possible.

Amazon RDS

There is an unresolved issue from 2014 on AWS Developer for this,

Amazon confirms we're right in saying there is no way

  Unfortunately, because RDS doesn't provide filesystem access, you won't be able to upload your custom files to $SHAREDIR.

And Amazon follows up with

  I have raised a feature request with the RDS team.


Restore or reset Oracle database (No Flashback features)

If takeing a rman backup, remember that is has to be an offline backup. Expdp/impdp is an option only if your data is not too big.
You could do filesystem/VM snapshoting depending on your environment.

If your data is of any value to you, please activate the archivelog mode and take consistent backups of your database. The database does it‘s best to safeguard your data please don‘t sabotage the.effort by not takeing backups.


A simple mnemonic:

The key (1NF - no data items repeated in more than one place within the row)
The whole key (2NF - nothing relates to just a part of a composite key)
Nothing but the key (3NF - nothing is related to a non-key attribute)

. . . So help me Codd.


Getting the "ORA-00911" error when I am trying to insert more than 1 row

I suspect you want to put the whole thing in an anonymous PL/SQL block and run that, i.e.

BEGIN
  INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
  INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
  INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
  INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');
END;

Depending on the tool (SQL*Plus, TOAD, iSQL*Plus, SQL Developer, and the APEX query tool may have slightly different syntax requirements), you could also add a '/' character after each INSERT statement

  INSERT INTO E_PRODUCT VALUES ('PCD2', 'PC Dual Core', 499, 22, 475, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('PCL4', 'Laptop PC', 599, 9, 225, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('PCQ5', 'PC Quad Core', 699, 25, 41, 'PC', NULL);
  /
  INSERT INTO E_PRODUCT VALUES ('RAM5', '512 Meg RAM Chip', 49.95, 0.25, 625, 'STO', 'No');
  /
  INSERT INTO E_PRODUCT VALUES ('RAM9', '1GB RAM Chip', 109.95, 0.3, 513, 'STO', 'No');
  /
  INSERT INTO E_PRODUCT VALUES ('VCD2', 'Video Card', 59.95, 1.25, 1210, 'IO', 'No');
  /


This has been fixed in v14 as a reaction to this question. The rest of the answer refers to the pre-v14 behavior:
This is (sort of) documented:

If filename is specified, the file is edited; after the editor exits, the file's content is copied into the current query buffer. If no filename is given, the current query buffer is copied to a temporary file which is then edited in the same fashion. Or, if the current query buffer is empty, the most recently executed query is copied to a temporary file and edited in the same fashion.
The new contents of the query buffer are then re-parsed according to the normal rules of psql, treating the whole buffer as a single line.

If you quit the editor without saving, it is the current query buffer or the most recently executed query that are executed.
I agree with you that this is annoying, and you are not the first person I heard complain.
When editing a script, the behavior clearly violates the “principle of least astonishment”:
test=> \! cat q.sql
SELECT 99;

test=> SELECT 42;
 ?column?
----------
       42
(1 row)

test=> \e q.sql
 ?column?
----------
       42
(1 row)


what is a good use case for splitting user table by role

What you are describing is an example of the general situation entity sub-typing.

There are two basic ways to handle entity sub-typing:

One method places common attributes in a table and distinct attributes in separate tables, linked using 1:1 relationships to the common table.  Some other considerations:

The common table may have a partitioning attribute, which is a field that indicates which sub-type of thing each record is.
The primary key of each sub-type table is a foreign key to the common table.
You can create views which merge the columns from the common table and each sub-type table, so that for querying purposes you appear to have a "one stop shop" for each sub-type of thing.  This can simplify your queries since it leaves you one less join to write out each time.
This approach makes it easy to apply referential integrity if you have child tables that apply to only a single sub-type (or all sub-types).

Another method is to place all attributes in a single table, and then use declarative constraints and/or triggers and/or application logic to enforce sub-type specific business rules.  In this case:

A partitioning attribute must be used to indicate which sub-type of thing each record is.
This makes queries easier, but it spreads your business logic out in a way that many people will find confusing and undesirable.
This approach can lead to many nullable columns, which makes some people itchy.
This approach doesn't help you if you have child tables that apply to individual sub-types, but it is the only sane approach if you have child tables that apply to multiple, but not all sub-types.

In your case you could pick either approach.  Which might be better depends on your preferences and on exactly how many distinct columns there are per sub-type.


phpMyAdmin Error After Upgrade to 3.5.8.1

I had this problem as well, when downgrading phpMyAdmin. When I deleted my cookies, the problem went away.


MySQL performance for aggregate function

The subquery is not needed at all.
The ORDER BY inside a subquery like this (without a LIMIT) makes no sense.
The external query has SELECT date but GROUP BY DATE(date). HOUR(date). This, while allowed in MySQL older versions (i.e. before 5.7), is not valid SQL.

I suggest you rewrite:

SELECT
    DATE(date) AS value_date,
    HOUR(date) AS value_hour,
    MAX(temperature) AS max_temperature
FROM temperature_information
WHERE device = 1111
GROUP BY DATE(date), HOUR(date) ;

Regarding performance:

an index on (device, date, temperature) will make the query more efficient than the current indexes on (device) alone and on (device, date). If you add this 3-column index, you could drop the other two ("device" and "idx") indexes.
Another option would be to store the date and hour part in separate columns and add a 4-column index on (device, date_part, hour_part, temperature).
If you move to version 5.7, you coul dhave the date and hour parts as generated columns. See MySQL docs: Ganerated Columns.
After the comment/edit that there are many more data columns and since it looks like you'll be running analytic type of queries, another option would be to change the PRIMARY KEY to (device, date) - or (device, date_part, hour_part, min_sec_part). This effectively - for an InnoDB table - clusters the data in the way you want them for this query. Of course you should test first this alternative design and how it will affect other queries, too.


You have to build a bridge, which you should already have, so you should think about your design
The only useful and not redundant information that has the table payment is LFN and to be honest put this into the payment_positions, every information you want you can get from payment_positions then:
CREATE TABLE [payment] (
  [LFN] INTEGER,
  [OUR_RECNO] INTEGER,
  [TOTAL] FLOAT
);

INSERT INTO [payment]
  ([LFN], [OUR_RECNO], [TOTAL])
VALUES
  ('1234', '36622016', '71.89');
GO

1 rows affected

CREATE TABLE [payment_positions] (
  [OUR_RECNO] INTEGER,
  [CUSTOMER] INTEGER,
  [TOTAL] FLOAT
);

INSERT INTO [payment_positions]
  ([OUR_RECNO], [CUSTOMER], [TOTAL])
VALUES
  ('36617800', '2566303', '47.34'),
  ('36622016', '2566303', '10.51'),
  ('36625936', '2566303', '14.04');
GO

3 rows affected

SELECT [CUSTOMER],[OUR_RECNO]
FROM [payment_positions]
WHERE [OUR_RECNO] IN (SELECT [OUR_RECNO] FROM [payment] )

CUSTOMER | OUR_RECNO
-------: | --------:
 2566303 |  36622016

SELECT r.[LFN], ac.[OUR_RECNO], ac.[CUSTOMER], ac.[TOTAL]
FROM [payment] AS r
INNER JOIN
(SELECT [CUSTOMER],[OUR_RECNO] FROM [payment_positions] WHERE [OUR_RECNO] IN (SELECT [OUR_RECNO] FROM [payment] )) AS t1
ON r.[OUR_RECNO] = t1.[OUR_RECNO]
INNER JOIN [payment_positions]  AS ac
ON ac.[CUSTOMER] = t1.[CUSTOMER]

 LFN | OUR_RECNO | CUSTOMER | TOTAL
---: | --------: | -------: | ----:
1234 |  36617800 |  2566303 | 47.34
1234 |  36622016 |  2566303 | 10.51
1234 |  36625936 |  2566303 | 14.04

db<>fiddle here


Oracle Trace File Interpretation: enq: TX - index contention

OBJ#=-1 means either means that the object number is missing or it's not related to an object.
The best idea is to go into V$active_session_history and find the SQL_ID then get the SQL text via the SQL_ID and look at the object sin the SQL and what the SQL was doing.

As far as people suggesting INIT TRANS, that is unlikely as there is a separate wait event for INIT TRANS.

USN, SLOT and SEQUENCE  map the UNDO entry for the transaction.
They could be used to map back to activity in the UNDO but it's probably easier to get the transaction id the XID and use VERSIONS_XID on the objects involved.

see http://www.slideshare.net/khailey/ukoug-oracle-transaction-locks

Example ASH query

col object for A15
col otype for A10
select
      substr(event,0,20)    lock_name,
      ash.session_id        waiter,
      mod(ash.p1,16)        lmode,
      --ash.p2                p2,
      --ash.p3                p3,
      o.object_name           object,
      o.object_type           otype,
      CURRENT_FILE#           filen,
      CURRENT_BLOCK#          blockn,
      ash.SQL_ID             waiting_sql,
      BLOCKING_SESSION        blocker
      --,ash.xid
from
        v$active_session_history ash,
        all_objects o
where
          event like 'enq: TX - index contention'
  and o.object_id (+)= ash.CURRENT_OBJ#
/


What is the 131072 in the call to lo_open()

This variable is called the "mode parameter" and it's the int representation of INV_READ (x'20000')

-- lo_open(lobjId oid, mode integer) returns integer
-- The mode parameter to lo_open uses two constants:
--   INV_READ  = 0x20000
--   INV_WRITE = 0x40000

You can see the integer representation like this,

SELECT CAST( x'20000' AS integer);
  int4
--------
 131072
(1 row)

Personally, I would rather use x'20000' which I find to be more explicit. You can see INV_READ mentioned in a comment in largeobject.source, and you can see it defined it in the code at libpq-fs.h.

In the event you ever see 262144 that's the numified representation of INV_WRITE (0x40000).


Merging two Select queries with different where statements

Since the two parts are identical except for the WHERE conditions where the first is just a restriction of the second, you can use one query, with the 2nd condition and an altered condition for the 1st sum, i.e. a CASE expression:

SELECT T3.AcctCode, T3.AcctName,
       SUM(CASE WHEN T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
             THEN T1.Debit - T1.Credit
             ELSE 0 END)* -1          AS Balance,
       SUM(T1.Debit - T1.Credit)* -1  AS [YTD Balance]

FROM OJDT T0
  INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
  INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
  INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
  AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName ;


MySQL cluster - how many hosts are needed for full HA?

You really need to define what level of "HA" you are looking for quantitatively -- one man's "can-sleep-at-night' is another man's "this-thing-is-a-house-of-cards".

The minimum number of systems is two (2) -- An active and a standby replica (with something like heartbeat or custom-grown scripts to handle the failover).

With a MySQL cluster this means at least two SQL nodes and two Data nodes (to continue serving requests in the event of a failure of any one node). (If you also need redunancy on the management server's functions you would need two of those as well).

The key part here is testing the failover in a development environment -- which means you need at least two more machines (or a virtual machine). You also want to test upgrade and maintenance processes to ensure they won't trigger unintended consequences (Ideally you should do nothing to production that hasn't been tested and proven in Development).

If you fail to properly test you may trigger failover, which means you incur the procedural (and possibly business) cost of a failover -- typically having to rebuild the former active server to be a new standby server.

This protects against hardware failures (power supply, NIC, disk, switch (if they're on separate switches).

Note that this doesn't just apply to your DB servers -- You need two of *EVERYTHING: Web servers, DBI servers, Firewalls, DNS servers...
Redundancy of one component is meaningless if you still have a bunch of single-points-of-failure in your stack.

The next level of protection is network failures ("What if my ISP goes down?") - this requires replicating your whole redundant environment above to a remote datacenter.

What's important here is that you diversify network connections, power, etc -- You don't want your standby datacenter across the street where it's fed by the same power and fiber as your main facility.
A company I consulted for had a requirement that any remote facility used for DR be "at least 15 degrees of longitude away" (i.e. "In the next time zone").  A common practice in the US is East-Coast/West-Coast, or NY/Chicago LA/Texas.

The next level above that is truly distributed resources (think Google) which requires a database system that supports replication and sharding (think MongoDB).
If implemented properly there's almost no chance of a true "outage", though service may occasionally be degraded and recovery can take a while.


What permissions do you need to take a mysql database backup via SSH?

Depending on the options you use with the dump you may need more (or less). The one you definitely need is SELECT.

If you use triggers you need TRIGGER
If you use views you need SHOW VIEW
If you don't use --single-transaction in the dump you will need LOCK TABLES

For more info on the subject you can go to: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html


Best practice for when the ideal number of columns is unknown

Will you ever search by those parameters accross different products? It does not seem very probable to me.

If you do not need to manipulate those in sql, only retrieve them for any given product, then store it as xml/json/anything in one blob column (or dynamic column on MariaDB). You then can get one product with all variants and maybe "filter" them by user input in application code (there won't probably be hundreds of them).

But if you have to search/aggregate etc, then do the second table for them and use join or another query to retrieve them as needed. Join is fast, it is a core function of relational DB.


What are the things need to be considered while creating tablespace?

While creating a tablespace, you need to consider few things like

i) Type of the tablespace either permanent or temporary or undo (default is permanent)

ii) Extent management whether it is managed locally or dictionary based

iii) Segment space management whether it is automatically managed or manually managed

Always you can use temporary tablespace since it is the best practice. You can create multiple files and mainly it depends on your application.

We have two types of tablespaces like small file and big file. Small file tablespaces can have multiple datafiles. Each file with maximum size of
1024 bytes. Big file tablespaces should contain only one file with maximum size of 8 Hexabyte.


Those columns only become available on SQL Server 2012 if it's patched up to Service Pack 3.

Specifically:

  Update for Dm_exec_query_stats DMV to track memory grants information
  in SQL Server 2012

And KB 3107398.

Just as an FYI, if you're not on Service Pack 4, you're out of Microsoft support.


Update audit database name in triggers and views of main database?

You can use the text column from sys.all_sql_modules to create alter scripts with your new database names. Below is an example where I use AdventureWorks2014 to replace any text where there is a CREATE with an ALTER while changing database name HumanResources to HR.  Again this will only generate your scripts.

EDIT: Replaced syscomments with all_sql_modules.  Thanks Kenneth Fisher!

USE AdventureWorks2014
GO

SELECT
    o.name,
    o.type,
    o.type_desc,
    sc.definition,
    REPLACE(REPLACE(sc.definition, 'CREATE ', 'ALTER '),'[HumanResources]','[HR]')
FROM sys.all_sql_modules sc
    inner join sys.objects o
        on sc.object_id = o.object_id
WHERE o.type IN ('V','TF','P','TR') --Views, Table Function, Proc, Trigger. You can include/exclude additional types if you choose.

Hope this helps!


Representing a Superclass/Subclass (or Supertype-Subtype) relationship in a MySQL Workbench diagram

This is a case of what's called "generalization/specialization" in ER lingo.  It's really the same thing as what object models call "superclass/subclass" as you have done.  There are two separable issues.  The first is how do you want to draw the diagram, and the second is how do you want to design the tables.

As far as drawing the diagram goes, I would draw it the same way you have, with one exception.  The lines leading out of Managers and Drivers would not go separately to Employees.  Instead, they would go to something I'll call a gen/spec box.  The following sample shows one way to depict a gen/spec case.  Then a single line connects the gen/spec box with the Employees table.
It all depends on which way seems simpler and clearer to you and to others who look at the diagram.

As far as tables go,  you could have three tables, just as you have shown.  This is sometimes called "class table inheritance".  There is a technique you could benefit from, called "shared primary key".  In this technique, the manager table and the driver table do not have an id field of their own.  Instead, there is a copy of Employee_id in each subclass table, and employee_id is declared to be both the primary key of this table and also a foreign key that references Employees.

Using shared primary key buys you a few things and it costs you something.

It buys you enforcement of the 1:1 limit on manager-employee or driver-employee. It may end up speeding the resulting joins.  And it allows a fourth table that uses employee-id as a foreign key to be joined directly to Drivers or Managers.   The irrelevant employees drop out of the join.

What it costs you is that you have to obtain and use a copy of the right employee_id every time you go to insert a driver or a manager.

You could also just pack driver attributes and manager attributes into the employee table, leaving irrelevant fields NULL.  That results in fewer joins, but it does have a downside.  It makes the employee table slightly bigger and slower.  And if you use nullable fields in WHERE clauses, you'll have to learn how SQL does three valued logic  (True, False, and Unknown).  For some people, this is awfully difficult to grasp.  I try to avoid three valued logic myself.


Does indexed column with millions of different values make sense?

For b-tree index , the more distinct values the better.

Index skip scan doesn't seem to be an efficient access method in your case because the first column in the index has high cardinality. It works opposite to "regular" index usage like index range scan or index unique scan  where you want the first column to be the most discriminating.  It's hard to say if in this particular case full table scan would behave better without trying (FULL or NO_INDEX hints can do the job) .

No doubt, for the query in  question index on event_id,event_date is much better than on event_date,event_id.

It's hard to tell without seeing your other queries, but I think you may not necessarily need to have composite indexes.  Each column, event_date and event_id, appear to be have very few duplicates (if any) .  Then having two indexes, one on EVENT_DATE and another on EVENT_ID will work better than having multiple concatenated indexes - even if a composite index is covering for some queries, the engine will have to do only one or two extra lookups .


Prepare MySQL for big UPDATE query on production server

The Query cache is a hindrance.  (In general, it should be OFF for production systems.)  All entries in the QC for that table will be purged by the Update.

Millions of rows -- all of the table?  A small fraction of a billion row table, but no good index?  What?

Let's dissect the query...

Scan through the entire table.  This takes time for the following reasons:

Read the blocks
Write the "undo" records to disk (in case of ROLLBACK or crash)
Write the blocks back to disk
Toss the undo blocks

The size of "undo" may necessitate a less efficient method, adding to the overall time.

The affected rows will be locked; well you may as well plan on the 'entire' table being blocked from other usage.

Batching...

Avoids blocking as long
Probably takes longer overall
Complexity in the code
Not atomic (if that matters)

Why do you need to do such a big Update?  Perhaps the column you are changing should be in a separate, parallel, table to avoid this much impact?  Or 'computed' by subsequent SELECTs?

Config changes?  Assuming the innodb_buffer_pool_is big enough, nothing useful to do.

For chunking, see advice on chunking -- that talks about deleting, but can be adapted to updating.


Cannot Generate SSPI Context when connecting to the remote Computer

There is a problem with the SPN of the SQL Server. Look at the Microsoft Kerberos Configuration Manager tool.
https://www.microsoft.com/en-us/download/details.aspx?id=39046

This will look at the SQL Server and tell you what the SPN issue is with the machine and provide you a script to resolve. The script will need to be run by someone with Domain Admin permissions. This tool essentially automates the manual steps found in this article.
https://support.microsoft.com/en-us/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message


Restoring a database back to a point in time before full backup was made

Its very difficult to reverse engineer and interpret SQL T-log records. As mentioned there are 3rd party tools like APEX SQL Log , RedGate's LOG Rescue, Log Explorer

Also, there is an undocumented command fn_dblog that can be used as below, but you wont have the flexibility like 3rd party tools.

SELECT
    [Previous LSN],
    [Current LSN],
    [Operation],
    [AllocUnitName],
    [Page ID],[SPID],
    [Begin Time],
    [Transaction Name],
    [Lock Information]

FROM ::fn_dblog(null, null)
GO

Paul Randal has described how to read from T-log with excellent examples -- Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN .


Query over text column takes ages to run

Not sure if you added another predicate, or if you changed the existing one, but you could try like:

SELECT TOP 1 userid
FROM myTable
WHERE ISNULL(userID,0) > 0
  AND NULLIF(SUBSTRING(myDescriptionField,1,1),'') is not null;


SQL Server Table Design for Report - Is OLAP Schema Suitable

For databases of a significant size, the normalised logical design is typically created first. Following this, sample queries are run and the database is then denormalised for performance if need be (the physical design). If the gains are minimal, you may elect to stick with the logical design for the simple reason that it is easier to understand and is more efficient from a storage point of view.

It would be somewhat presumptuous to go for a denormalised design from the off without any sort of performance testing - even if this is clearly an OLAP system.


PostgreSQL Export to CSV. Each element of array in new line

Test:

\copy (
  SELECT CASE WHEN users.name = LAG(users.name) OVER (ORDER BY users.name,
                                                               users.email,
                                                               addresses.address)
              THEN ''
              ELSE users.name
              END,
         emails.email,
         addresses.address
  FROM users
  LEFT JOIN emails USING(id)
  LEFT JOIN addresses USING(id)
  ORDER BY users.name,
           users.email,
           addresses.address
) TO 'path/to/file.csv' WITH csv header;


Same query taking 0.2 sec in Oracle database but 4 mins in SQL Server

There's a chance that the SQL server table or index statistics are out of date, aren't as accurate as they could be (both of which can affect how the SQL query plan is put together) or SQL could be reusing an old query plan which was based on a smaller data set.

If the statistics are out of date, or aren't accurate, then SQL will generate the query plan based on flawed data, so you'll get poorly performing code (looking specifically at your SQL Query plan and the 98% on the nested loop, it might just be a single index, or one of the tables affected by that part of the query plan).

If the statistics are up to date and you're still getting poor performance and the query plan is pushing everything through a Nested Loop, you could try forcing it to use something else like using an OPTION query hint, although try it out on a dev system first.
MSDN - Query Hints article

This quote posted on SQLServerCentral by Kevin Boles is a something to keep in mind when looking at inefficient query plans.

  One thing to watch out for here is when the optimizer THINKS there will be few rows (check the estimated rows in the popup for the estimated query plan graphic) but in reality there are LOTS of rows (thousands or even millions).

Specifically regarding the nested joins, Gail Shaw wrote this article on execution plan operations joins, which is a good reference on the physical join types and where/how they're normally used.