What you've modelled is a snapshot of the user's total progress.

I think you might want to track the user progress a little differently.  Instead of letting them enter their distance covered directly, why not track their way-points?  If you make a kind of audit trail of where they went (lat/lon) and when (date) then you can easily calculate their distance covered using Vincenty or another numerical method.

By tracking the progress on a map (i.e. by lat/lon) by date you can have more interesting reporting of the data.  For example, what if two "contestants" want to race but can't start in the same place at the same time?  How do you compare their progress?


Designing a table for large amount of rows. No relationships

ID - I don't like GUIDs where GUIDs can be avoided.  They're 16 bytes and thrown randomly into your tables causing fragmentation and page splits.  A 4-byte INT or 8-byte BIGINT as a sequence or identity column would provide the same functionality, be smaller, and insert into the table sequentially.

Path - Does this really need to be NVARCHAR?  This will take twice the amount of storage as VARCHAR unless you use row-level compression.  Going from 500 bytes to 250 bytes doesn't seem like a lot for one row, but for a million rows you're at 500,000,000 to 250,000,000, which is 250 MB according to your friendly hard drive marketing department.

FileType - This is really a small nitpick.  Do you really need to have a 4-byte INT here?  This allows you to have billions of file types.  Chances are a 1-byte TINYINT limited to 255 choices would be more than enough.

The biggest thing from your concerns is getting the GUID out of there.  On an insert-intensive table you don't want SQL Server writing all over the place doing page splits.  I mention the size of the columns which is a nice-to-have, but its the sequential writes that are going to make you the happiest with your performance.

However, sequential numbers are a bit of a security risk if someone is allowed to view some files in the system but not others and you rely on them not being able to guess a GUID to avoid them finding other files.  If they bring up file 1000 then they can be pretty sure that files 999 and 1001 probably exist and view those as well.  I don't know if this is a concern based on what you wrote.


Columns specified in WHERE and JOIN clause predicates are the candidates for index keys.  ORDER BY columns are also candidates if it can avoid an expensive SORT operator for many rows.  Columns specified only in the SELECT clause are candidates for non-clustered index INCLUDED columns when the cost of the additional storage and maintenance outweigh the elimination of the key lookup in the plan.  This may be the case for critical queries that is executed frequently.

Selectivity depends entirely on the data (i.e. high COUNT(DISTINCT column) compared to number of rows in table). The generality about most selective columns first is true when all of the keys are used seek predicates in the same index seek operator.  You might find separate indexes more useful than a single composite index.

Generally speaking, indexing is most optimal when only those rows actually needed by the query are touched and expensive operators (e.g. large SORTs) are avoided.  Tradeoffs are often involved.


Understanding of schema and tablespaces - recommendation for a later seperation

I think you are over complicating this.  Grants and roles govern access by users not what table space and data files where the data is located in.  Yes, backup, recover and export are much easier if schemas have their own table space but that doesn't seem to be your question.

Separate your business domains by user/schema.
Give each user/schema their own group of tablespaces which contains their tables/indexes/lobs eg: User1Data, User1Indexes, User1LOBS
create roles to allow users/schemas to access data/code in other schemas

From your comment below I would add:

user/schema are the logical way to separate data and code.  Use different schemas from day one if your business domain works that way (and that seems to be what you are indicating).  Moving tables to different schemas after deployment can be done but it's a pain.
in your comment you still link table spaces and permissions.  There is no link between what table space a user's data is in and the permissions granted on that data.

If you will allow me a simplification:

users and schemas allow  you to control access to data and code through roles and grants
table spaces and data files allow you to manage the physical files used by the database.  Easier backup and recovery are one of the things that is easier to do when each user has their data in a clearly defined set of table spaces.


So this seems like largely a question about proper data normalization.

With data normalization in mind, we can easily rule out option 1. The data, like you suggest, will clearly be unnormalized and repeated.

Option 3 looks to be more of a hack to handle the current schema not capturing your domain. If you need to implement hacks like this, it's definitely time for a redesign.

Which brings us to Option 2. Of the three options, this seems to be the best. However, the schema seems like it could be capable of further proper normalization. Without knowing more about the domain, I would assume that there probably is some sort of functional dependency between Machine, Material, and RunTime. Or it could involve the product ID. Of course if all 4 of these values are free to differ without affecting the others then this area for schema optimization can be ignored.

If no functional dependencies exist in the procedure table, then the schema as suggested in option 2 with a new 'inactive' procedure to capture custom work appears to be the best overall choice


Thanks to @mustaccio's question got me thinking about datatype issues.

NVarchar2 seems to be the issue in this problem.

Creating a copy of the column in varchar2 fixed the issue. Data redaction on varchar2 is working as expected.


MySQL relay log rotates too soon and too often

You can verify by yourself as slave server creates (rotates) a new relay log file under the following conditions only:

Each time the I/O thread starts.
When the logs are flushed; for example, with FLUSH LOGS or mysqladmin flush-logs
When the size of the current relay log file becomes “too large,” determined as follows:

If the value of max_relay_log_size is greater than 0, that is the maximum relay log file size.

If the value of max_relay_log_size is 0, max_binlog_size determines the maximum relay log file size.

The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the SQL thread deletes them.


Oracle multiple databases only able to access one database

Database Windows Service Status "Running" does not necessarily mean the database is open and available. The database can be in all states "down|nomount|mount|open" while the Windows Service says "Running".
- C:\>lsnrctl status # will tell if the database service has been exposed/made available.

You should validate the state of each database by:
set ORACLE_SID=<SID>
sqlplus / as sysdba (OS auth. Make sure your user is member of local group ORA_DBA)
SQL>select * from v$instance;

Check the alert_.log for errors. You will find the alert log in your Oracle Software trace folder.
Best of luck.


SQL Server Occupies more than allocated memory. Possible memory leak

My questions are
  Is SQL Server leaking memory?

Highly unlikely but you should plan for SQL Server 2012 SP4 ASAP. From my past experience I could say that since SQL Server and other applications like SSAS and SSRS are all running on same machine SQL Server might be facing memory pressure and so might be the case for SSAS/RS. I have seen lot of systems like this and it all boils down to memory pressure.

You have 32 G of RAM and you have given just 16 G to SQL Server make it to 20 G and see if this help. Adding more memory would definitely help if you can go for it.

There can be plethora of reasons why queries are timing out and Troubleshooting SQL Server performance issues can help you to find the root cause of it.

  But when I select memory occupied by Percentage, it shows 80%-85% which is NOT RIGHT:

I would start by saying Task Manager is not a correct place to gauge SQL Server memory consumption, it will not tell you correct value when SQL Server service account has Locked Pages in Memory(LPIM) privilege. This is because normally task manager tracks Process Private bytes memory which is pageable and allocated via VirtualAlloc() function but with LPIM chunk of memory allocation is done by AWE API which is NON pageable so task manager does not tracks it and this can lead to incorrect value. For percentage thing which you are looking is actually Percentage of Process Private bytes not the complete memory and it does not provide any relevant information so just stop looking at it.

It is quite normal for SQL Server to utilize memory allocated to it and to know how much physical memory SQL Server is using please use query

select * from sys.dm_os_process_memory

PS: It is always recommended to move other applications on different machine (if possible) and let SQL Server run solely on it own system this will help SQL Server run faster and better


Screwed up replication by sharing server ids

From my standpoint, you may have potentially introduced data drift into replication.

Baron Schwartz presented this as a puzzle in his blog.

You may have to reload oldS and newS1 with fresh data.

At the very least, you should use pt-table-checksum to see if

the data on Master differs from oldS
the data on Master differs from newS1

If the differences are not identical, you can either

reload  oldS and newS1 fresh
run pt-table-sync

Before you touch anything, please fix the server-id situation

The spawning of relay logs is to be expected because sibling slaves take turns getting SQL entries from the Master. They simply cannot share the same server-id. The Master will somehow alert subsequent slaves that I gave server-id an SQL statement already. Thus, the I/O thread on subsequent slaves will disconnect and retry. Consequently, empty relay logs increase. (Trust me, I have shot myself in the foot with this years ago).

This methodology of the Master talking to Slaves for this info allowed MySQL (eh, Oracle) to  come up with semisync replication. This would break semisync replication as well. Even though MySQL 5.6 will soon introduce a Global Transaction ID into the mix, server-id will still be used in its method of checks-and-balances on the Master. After all, if an eagle had
 two eaglets, no eagle would spit into two mouths at the same time in order to feed them.


Why does CURRENT_USER() show the root@::1 when I login to MySQL?

I think the problem stems from how MySQL determines how to authenticate a user.
I mentioned this Jan 18, 2012 in MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
I quote from MySQL 5.0 Certification Study Guide

Pages 486,487 state the following on mysql's authentication algorithm:

When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:
In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

Now look at the strings ::1 and localhost. What is the first letter's ASCII value of each ?
mysql> select ORD('::1'),ORD('locahost');
+------------+-----------------+
| ORD('::1') | ORD('locahost') |
+------------+-----------------+
|         58 |             108 |
+------------+-----------------+
1 row in set (0.00 sec)

mysql>

Since ::1 is alphabetically first, you get authenticated as root@::1


Optimize join - group by Oracle SQL filter

Check https://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle. Here the LISTAGG function is explained. This functionn delivers what you need.


Error: change in schema version between compile time and run time - SQLNCLI11

As described at https://stackoverflow.com/questions/1143259/what-is-the-internal-representation-of-datetime-in-sql-server, SQL stores datetime values as an 8-byte field.

The compile time in your sample error, 186839690094804, when converted to hex, is 0x0000A9EE015BA8D4. The date part is 0x0000A9EE, and the time part is 0x015BA8D4. 0x0000A9EE, when converted back to decimal, is 43502, and using an epoch of 01 January 1900, that comes out to 06 February 2019.

The date part of the run time in your sample comes out to 09 February 2019. Those pass the sanity check.


Oracle 11g install on Debian Wheezy does not start

Edit your /etc/oratab file to read:

INFO:/u01/app/oracle/product/11.2.0/dbhome_1:Y

I guess it's just one of those things you need to know to look for!


I would think that Option 2 is more preferable because of the following:

Less overall writes to the database (no extra table required)
Less overall WAL activity (Option 1 involves both an INSERT/COPY and an UPDATE for each row involved)

In either case, you could encounter some table bloat, in which case you'd want to make sure you VACUUM after doing this operation.


Would Mongo's ObjectID work well in InnoDB's clustered index?

I would believe that it won't work as well and here's why.

it's larger then a int (4 bytes) or bigint (8 bytes). Remember, every other index references the primary key and so it bloats the other indexes. This might not be a concern, but it's something to be aware of.
While the timestamp is sequential, the machine id, process id, and random value are not always going to be, so anything that happens in the same second isn't going to always be in ascending order.

So in conclusion, I don't believe that the Mongo ObjectID is going to perform as well as a smaller, ascending key.

That said, you still can use it as a primary key. It doesn't need to be ascending, it just means that you might have index leaf splits on inserts, which if they're close enough to the end, won't be overly performance impacting. And you can always rebuild the table to compact the indexs.

So, more about the leaf splits. By default, innodb likes a 15/16 fill, leaving 1/16th free for future changes. When the data is inserted in a sequential order, the indexes end up always filled to 15/16.

When the data is inserted randomly/out of order, innodb keeps the pages between 1/2 to 15/16 full, depending on what it believes the end fill rate of the index page will be. This obviously wastes disk space and memory when the index is loaded into memory.


Postgres update a table (only) if it exists

You need a pl/pgsql block to do the IF:

DO $$
    BEGIN
        IF EXISTS
            ( SELECT 1
              FROM   information_schema.tables
              WHERE  table_schema = 'public'
              AND    table_name = 'recipes'
            )
        THEN
            UPDATE recipes
            SET lock = NULL
            WHERE lock IS NOT NULL ;
        END IF ;
    END
   $$ ;


How to select same number of data points over different time ranges

One simple solution is:

SELECT *
FROM (SELECT tstamp AS time,
             dvalue AS weight
      FROM tablename
      WHERE description = 'exp_weight'
        AND tstamp BETWEEN 'time1' AND 'time2'
      ORDER BY random()
      LIMIT 1000
     ) AS q
ORDER BY time;

Explanation: The dataset is shuffled randomly, tgen the first 1000 values are taken and the result ordered by time. The subquery is just a syntactical necessity.


SQL Server: Grant access to all views in schema, but not tables

You'll need to move the views to a different schema to utilize this security tom foolery.

Basically you can GRANT SELECT permissions on a Schema, but you are unable to limit the SELECT privileges to views only.

The workaround, as you identified, is to create a schema that only owns the views in question.  This schema is also owned by the same principal that owns the base schema where the tables reside so your permissions are a little easier to manage.

Below is an example script breaking it down:

-- Create Test Table
CREATE TABLE dbo.Test
(
      ID INT IDENTITY(1,1)
    , VALUE NVARCHAR(50)
)
GO

-- Test Data
INSERT INTO dbo.Test (VALUE)
VALUES
    ('blah'), ('blah'), ('blah'), ('yackity'), ('schmackity')
GO

-- Create Special Schema that will "Own" all the Views
CREATE SCHEMA viewSchema AUTHORIZATION dbo
GO

-- Create a new DB Role
CREATE ROLE db_viewreader
GO

-- Grant SELECT to all objects in the viewSchema schema to the new DB role
GRANT SELECT ON SCHEMA::viewSchema TO db_viewreader
GO

-- Create a View on top of the Test Table AFTER the role was
--  defined/created to show this applies to any new views made under this schema
CREATE VIEW viewSchema.testView
AS
SELECT    ID
        , VALUE
  FROM dbo.Test
GO

-- Create a new Test User
CREATE USER testuser WITHOUT LOGIN
GO
ALTER ROLE db_viewreader ADD MEMBER testuser
GO

-- Execute the following block of code as the testuser account
EXECUTE AS USER = 'testuser'

    SELECT *
    FROM dbo.Test

    SELECT *
    FROM viewSchema.testView

-- Revert security context back to sa
REVERT;

-- Cleanup
ALTER ROLE [db_viewreader] DROP MEMBER [testuser]
DROP ROLE db_viewreader
DROP VIEW viewSchema.testView
DROP SCHEMA viewSchema
DROP TABLE dbo.Test
DROP USER [testuser]
GO

Your output should look as follows, confirming you are unable to select from the base table while having no issues selecting from the view that references it:

(5 row(s) affected)
Msg 229, Level 14, State 5, Line 58
The SELECT permission was denied on the object 'Test', database 'TestDB', schema 'dbo'.

(5 row(s) affected)

The beauty here is any user that is part of the db_viewreader role will have SELECT privileges to any new view you place into the viewSchema schema automagically which is what I assume you're looking for.


Comparing multiple tables on two databases using dynamic PL/SQL

I've found a solution but it's probably not the best way of doing it.

CREATE OR REPLACE PROCEDURE GGCHECKER

IS
    cursor c1 is
        SELECT TABLE_NAME, TABLE_OWNER, PERCENTAGE_CHECK, LAST_UPDATED, PRIMARY_KEY FROM MODDIE.TEMP_TABLE;

    CTABLE_NAME          VARCHAR2(50);

    CTABLE_OWNER         VARCHAR2(50);

    CPERCENTAGE_CHECK    VARCHAR2(50);

    LU_CTABLE            VARCHAR2(50);

    PK_CTABLE           VARCHAR2(50);

    var2                varchar2(4000);

    var3                varchar2(4000);

    var4                varchar2(4000);

    var5                varchar2(4000);

    var6                varchar2(4000);

    VAR7                VARCHAR2(4000);

    var8                VARCHAR2(4000);

    var9                VARCHAR2(4000);

    NUMROWS             NUMBER(20);

BEGIN

OPEN C1;

LOOP

    FETCH C1 INTO CTABLE_NAME, CTABLE_OWNER, CPERCENTAGE_CHECK, LU_CTABLE, PK_CTABLE;

-- Create query to populate comparison table

var2:='CREATE TABLE system.COMPARISON_RANDOM AS (
    SELECT ' ||PK_CTABLE|| ' FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| ' SAMPLE(' ||CPERCENTAGE_CHECK|| ')
    WHERE ' ||LU_CTABLE|| ' < SYSDATE -1
    )';

    EXECUTE IMMEDIATE var2;

--Compare the two tables
VAR5:='CREATE TABLE System.TEMP_RESULTS AS (
SELECT DISTINCT '|| PK_CTABLE || '
    FROM (
            (
                SELECT * FROM ' || CTABLE_OWNER || '.' ||CTABLE_NAME|| '
                    WHERE ' || PK_CTABLE || ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM System.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@dblink
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
            UNION ALL
            (
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@dblink
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
        )
        )';
    execute immediate var5;

    var8:= 'SELECT COUNT(*) FROM system.TEMP_RESULTS';

    -- Find the number of results for the loop
    execute immediate var8 into NUMROWS;

    DBMS_OUTPUT.PUT_LINE('THE NUMBER OF DISCREPANCIES IN ' || CTABLE_OWNER || '.' || CTABLE_NAME || ' IS: '|| NUMROWS);

    -- For every result add a record to the results table
    IF NUMROWS > 0 THEN

        var6:='
        INSERT INTO system.RESULTS_TABLE
        (TABLE_OWNER, TABLE_NAME, COL_ID)
        VALUES
        ( ''' || CTABLE_OWNER || '''  , ''' || CTABLE_NAME || ''' , NULL)';

        FOR N IN 1..NUMROWS LOOP
            DBMS_OUTPUT.PUT_LINE(var6);
            EXECUTE IMMEDIATE var6;
        END LOOP;
    DBMS_OUTPUT.PUT_LINE('mid if');

        FOR M IN 1..NUMROWS LOOP
            DBMS_OUTPUT.PUT_LINE('mid for2');
            var7:=' UPDATE system.RESULTS_TABLE SET COL_ID = (SELECT * FROM system.TEMP_RESULTS WHERE ROWNUM = ' || M  || ' ) WHERE ROWNUM = ' || M || ' ';
            DBMS_OUTPUT.PUT_LINE(var7);
            EXECUTE IMMEDIATE VAR7;
         END LOOP;

     END IF;

    var3:='DROP TABLE system.COMPARISON_RANDOM';
    execute IMMEDIATE var3;

    var9:='DROP TABLE system.TEMP_RESULTS';
    EXECUTE IMMEDIATE VAR9;
     DBMS_OUTPUT.PUT_LINE('exit 1');
    EXIT WHEN c1%NOTFOUND;

    END LOOP;
        CLOSE C1;
    var4:='DROP TABLE system.TEMP_TABLE';
    execute IMMEDIATE var4;
     DBMS_OUTPUT.PUT_LINE('exit 3');

end;
/


Syncronizing process difference between replication and alwayson high availability

Transactional Replication uses the TDS protocol and applies changes using SQL.  The connections are established from the Distributor for Push Subscriptions, and from the Subscriber for Pull Subscriptions.

AlwaysOn Availability groups use TCP/IP connection between the replicas using the Database Mirroring Endpoint.  The connections are established from the instances hosting the Secondary replicas to the instance hosting the Primary replica.  But as the Primary replica can move, every server hosting a replica needs to be able to connect to every other one.


Having searched quite a bit and come up empty I decided to just go ahead and remove the registry keys for the IP address I wanted to delete from the configuration.

Steps:

Backup the registry key HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\
Stop SQL services
Go to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\ (must do this for each SQL instances on the server)
Find the IP# key that contains the IpAddress value of the address you want to remove where # is a number from 1 to the number of IP addresses SQL found on your machine during install
Delete the IP# key found in step 4
If the number in the keyname is not the last one, rename the remaining IP# keys to be sequential starting with 1.
Restart SQL Services

After doing this the old IP address was no longer available in SQL Server Configuration Manager and SQL Server no longer tried to listen on that IP address.  There were no longer any "Listening on [old address]" entries in the SQL Log and netstat -an showed no listeners as well.


Can I use the attach/detach database option to shut down Azure sql database until i need it

No. Attach/detach are not supported on Azure SQL DB. A few workarounds today:

Change the pricing tier (performance level) to the lowest that can support your database (each tier has a certain size limit), you can bring it up to the require performance level when you need to use it
Export to Azure blob (very low cost storage) when not needed, import when needed later (ok for smaller databases). More information on this at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-export
Delete the database when not needed, restore later. Not recommended but you'll see this suggestion come up quite a bit. Be careful not to delete the server (costs you nothing if you don't have DBs in it) and depending on the pricing tier of the database, backup retention varies from 7 to 35 days. If it's past the period, you're pretty much out of luck. See difference between export/import and backup/restore here https://azure.microsoft.com/en-us/blog/azure-sql-database-built-in-backups-vs-importexport-2/


SQL Server - Server name same as the instance name and most common use for instances?

Servers are like the physical location. Instances are the actual SQL Server functionality. They are what you connect to in order to use SQL Server.

Each Instance belongs to only one Server.
You can have multiple instances per Server (so T_COMPUTER\SQLEXPRESS and T_COMPUTER\SQLSERVER could be 2 instances on the same Server).
An Instance is a fully self contained SQL Server installation.
You can have a default instance configured, which is the Instance accessed when you connect to the Server directly (i.e. T_COMPUTER, actually connects to T_COMPUTER\INSTANCE)


Dropping rows based on the previous selection statmnet

To get a dynamically formed SQL statement you need to get it into a user variable, @drop, and then execute this:

SELECT CONCAT('DROP TABLE ',
              GROUP_CONCAT(CONCAT(TABLE_SCHEMA,'.',TABLE_NAME))) INTO @drop
FROM information_schema.TABLES
WHERE  table_schema = 'db' AND
       table_name like 'temp%' AND
       CREATE_TIME < (NOW()-INTERVAL 24 HOUR);
PREPARE dropstmt FROM @drop;
EXECUTE dropstmt;
DEALLOCATE PREPARE dropstmt;

To clean up cached_tbl, delete where there isn't match on the join:

DELETE c FROM cached_tbl c
LEFT JOIN information_schema.TABLES t ON t.TABLE_NAME = c.table_name
WHERE t.TABLE_NAME IS NULL


exceeding 'max_updates' resource when importing large database

Yes, the "switch or option" that would allow you to do this is the one your host doesn't want to change.  If there were a way for a user to simply work around resource limits, there wouldn't be much point in resource limits.

It's not obvious from the documentation, but the source code makes it fairly clear that this is a limit on the number of queries you can run, per hour, that change almost anything at all about the server.  The use of the word "update" is unfortunate, because this resource limit impacts any query that does something like...

INSERT UPDATE DELETE TRUNCATE LOAD OPTIMIZE ANALYZE REPAIR GRANT REVOKE

... as well as any valid pairing of these:

[ CREATE | DROP | ALTER ] [ TABLE | TRIGGER | VIEW | PROCEDURE | FUNCTION | EVENT ]

If you take a look inside the dump file, it should become readily apparent that you're doing quite a lot of all those things.

But aside from the dump, for a minute, what sort of tiny little service will you be able to host on a server that only lets you execute 36,000/hour ... 600/minute ... 10/sec queries that INSERT/UPDATE/DELETE max, averaged over an hour?  That's tiny... so you have either purchased something really small and inexpensive... or your host has already oversubscribed the server and is trying to mitigate that problem... or your data has already caused more loading than the host anticipated, so you earned a special extra "feature."

The most plausible workaround, to me, seems like it will be to pace your restore by imposing a small delay between each query that gets issued to the server, to stay within your time limit.

cat FILENAME | perl -MTime::HiRes -pe '$| = 1; Time::HiRes::usleep(100000)' | mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME

You can see this in action without actually applying it to the server by just trying this:

cat FILENAME | perl -MTime::HiRes -pe '$| = 1; Time::HiRes::usleep(100000)'

You should see the dump file coming at you, one line at a time, use control-C to break out.  The value 100000 is 100000 microseconds, or 1/10th of one second, which is approximately your limit.  The server won't be executing the queries instantaneously, as it will require some number of milliseconds to process each one, so setting the value at 100000 will probably work.  Also, there is some white space in mysqldump files, and statements like CREATE TABLE will be broken out on multiple lines, even though they'll only count as a single query, which will stretch out the time a bit, also.

Remember, though, that resource limits are per-user, so you can't be doing anything else on the server at the same time with the same account, or the two sessions, together, will combine to push you toward that limit.

If deliberately slowing the pace of your import seems counterintuitive, that's because it is.  Most of us try to get our imports to load faster, not slower, so you'll need to consider also whether this host's limits are going to work for you or whether you need a different plan.

You can get an idea of how many SQL statements are in the dump file with this:

grep -c . FILENAME

This will return a count of lines, which will be close to the number of statements... slightly higher, again, because of how mysqldump files are formatted.  This number divided by 36000 is approximately the fastest possible loading time, in hours, that you could achieve on this file, with this host, with these restrictions in place.  For reference, I ran this against a randomly-selected mysqldump file of 72GB, and the count is 475,309... so, we could estimate a restore time of maybe 13 hours for that particular dump file under these conditions. This number will, of course, vary significantly, so check it against your data source and see what you find.


How to audit database user password changes in oracle 11g?

Auditing can capture when a user changes their own password. If a sys or admin user changes the password of another user that can't be captured in auditing.

You can use this metalink doc (ID 419942.1) to create a system trigger and change it to add an entry in a temp table.


Cannot connect using tnsnames.ora but can connect by substituting its details

Thank you to Wernfried Domscheit for pointing me to sqlnet.ora.  This file was not referenced in the documentation I was using so I had no idea it existed.  After visiting an Oracle host and finding the file in $ORACLE_HOME/network/admin, I copied it to $TNS_ADMIN dir and minimized it through testing to the one setting (no others were needed) Wernfried referenced:

# this setting = my local domain
NAMES.DEFAULT_DOMAIN = DOMAIN

and my preferred connection method of

$SQLPLUS user@prod

succeeded.  I will speak with the Oracle DBAs about this file and it's proper inclusion in this shared dir.


Having the backup file, how to work out the total disk space required to do the restore?

If I think you want this in a SELECT statement for multiple databases, the below code is a good start

CREATE TABLE #FileListHeaders (
     LogicalName    nvarchar(128)
    ,PhysicalName   nvarchar(260)
    ,[Type] char(1)
    ,FileGroupName  nvarchar(128) NULL
    ,Size   numeric(20,0)
    ,MaxSize    numeric(20,0)
    ,FileID bigint
    ,CreateLSN  numeric(25,0)
    ,DropLSN    numeric(25,0) NULL
    ,UniqueID   uniqueidentifier
    ,ReadOnlyLSN    numeric(25,0) NULL
    ,ReadWriteLSN   numeric(25,0) NULL
    ,BackupSizeInBytes  bigint
    ,SourceBlockSize    int
    ,FileGroupID    int
    ,LogGroupGUID   uniqueidentifier NULL
    ,DifferentialBaseLSN    numeric(25,0) NULL
    ,DifferentialBaseGUID   uniqueidentifier NULL
    ,IsReadOnly bit
    ,IsPresent  BIT
    ,TDEThumbprint VARBINARY(32)
    ,SnapshotURL NVARCHAR(360)
)
INSERT INTO #FileListHeaders
EXEC ('RESTORE FILELISTONLY FROM DISK = ''C:\Program Files\Microsoft SQL Server\
       MSSQL14.MSSQLSERVER\MSSQL\Backup\test.bak''')
SELECT Size/1024/1024 AS [size in mb],* FROM #FileListHeaders

Got the script from here

One caveat is the created table differs in various versions of sql server in the above link he handles that also. change the script according to you.


How can I improve this query in PostgreSQL?

I immediately see a few things that confuse me here, which you should look into.

Unnecessary Join

In your query you have a portion

...  LEFT OUTER JOIN houses ON houses.id = listings.house_id ...

yet no values from the houses table are in your SELECT.  The good news is that this is not affecting your performance, because the query optimizer has wisely left the houses table out of the query entirely.  This does concern me a bit just in principle.

Odd Query Plan

In your query plan, it chooses to enforce the conditions of

subscription_id = ### AND listing_status = 'Active'

by performing two Bitmap Index Scans, followed by a subsequent BitmapAnd.  This seems like a highly inefficient way to perform this operation when the Bitmap Index Scan on listings_subscription_item_idx returns approximately 5637 rows, but the Bitmap Index Scan on index_listings_on_listing_status returns over 3 million!

Is there any chance that you have a very large number of entries in listings for which listing_status is not 'Active'?

In this case, I would think the optimal plan would be to use the Bitmap Index Scan on listings_subscription_item_idx to extract a set of candidate rows, and then simply filter this small set of results for listing_status = 'Active'.  I'm surprised the optimizer has chosen this plan, but I have a wild guess that it's because the listing_status = 'Active' is actually highly selective on that column (such that maybe only 10% or less of the listings are Active).

To try and address this, I suggest trying the following query:

WITH listing_by_subscription AS(
SELECT * FROM "listings"
WHERE "listings"."subscription_id" = 123)
SELECT COUNT(*) AS count_all, listing_by_subscription.id AS listings_id
FROM listing_by_subscription
WHERE listing_by_subscription."listing_status" = 'Active'
AND EXISTS (SELECT 1 FROM apps
            WHERE apps.listing_id = listing_by_subscription.id
            AND apps.subscription_id = listing_by_subscription.subscription_id LIMIT 1)
GROUP BY listing_by_subscription.id;

Optimization Barrier in CTE

Here, I've tried to make use of the optimization barrier found when using a Common Table Expression.  Any time you use the WITH() expression, the Postgres optimizer treats it as a separate block which it must calculate separately from the overall optimization, sort of like a 'forced materialization'.  In this case, I try to use that to force the most selective predicate, the subscription_id = ### portion, to whittle down the result set first.

Try out this query and see if it helps!

work_mem

Last but not least, there's been a lot of talk abou work_mem settings today.  As @CraigRinger mentioned, we really need to see an EXPLAIN (ANALYZE,BUFFERS) to get the complete picture, but I'm wondering if this setting is sufficient.

Check you postgresql.conf file, and report back your work_mem setting, along with that EXPLAIN (ANALYZE,BUFFERS), if you're still having trouble.

Hope this works!


Please, don't store comma-separated lists in a single column. This is just a disaster waiting to happen. If these are separate facts, they should be stored separately.

Table GroupMemberRoles
GroupID  FK
UserID   FK
RoleID   FK
(PK on all three, with perhaps other constraints)

Your queries (say, to find the admins of a certain group) should be of the form:

WHERE RoleID = 1 AND GroupID = @GroupID;

And NOT:

WHERE RoleID = 1 AND ',' + GroupIDList + ',' LIKE ',' + RTRIM(@GroupID) + ',';

Or to find which groups a user is an admin of should be similarly simple:

WHERE RoleID = 1 AND UserID = @UserID;


Oracle 11g setting for backwards compatibility?

Take a look at the COMPATIBLE initialization parameter both here and here. These links are more to do with when you upgrade a database rather than import into a higher version but it should help if, perhaps, you can import your data into a cloned 9i database (so as not to mess up your original database) then upgrade that with the compatibility setting on to 9i.


SQL Server Cumulative Updates - not visible in Windows Update

Windows Update will only tell you about service packs for SQL Server, and only when you've enabled the "update other Microsoft products too" option (sorry, the wording has changed across versions, and I don't recall all of the variations).

SQL Server service packs are recommended primarily because the RTM branch (and earlier service pack branches) are ultimately retired and placed out of support. They are also supposedly more reliable and better tested than cumulative updates. I don't know that they should be provided via Windows Update, though, because that just makes it possible for a service pack to be deployed automatically. I don't like that idea at all - and I don't get a warm and fuzzy about how often I have to disable the "force feed me updates" setting and change it to a more appropriate "download them and tell me about them" option.

Cumulative updates, on the other hand, are recommended by some of us (Glenn Berry and I are vocal proponents of keeping up with CUs, because generally we've found them to be just as solid as service packs - though recently there have been a few chinks in that armor). But the message from Microsoft is not so clear. While they do put the updates out and publicize them, each one of them has this text (this is taken from 2012 SP2 CU4):

  We recommend that you apply The most recent update release.

The link points to KB 2983249, which gives you links to all of the cumulative updates for that branch. But then, in the CU article, the very next piece of text is:

  This cumulative package is intended to correct only the problems that are described in this article. Apply it only to systems that are experiencing these specific problems.

This disclaimer is probably the reason you won't see cumulative updates being pushed through Windows Update. I know they've been working on bolstering the regression testing and therefore the stability that you can expect from cumulative updates, but until that disclaimer disappears, you're going to continue to have to seek out cumulative updates on your own. I try to keep people updated on all updates (service packs, cumulative updates, and out-of-band on-demand hotfixes) for SQL Server 2012 and SQL Server 2014, with some qualitative advice thrown in where appropriate:

Latest Builds of SQL Server 2012
Latest Builds of SQL Server 2014

From those two posts, you can probably guess that if people had received the most recent cumulative updates automatically, through Windows Update, they would be plenty upset, and justifiably so. I'm all for deploying cumulative updates but this rash of problems has made me adjust my timeline for doing so - like add a month, maybe, so that other people can shake these issues out and I stop being the guinea pig.


what is most efficient way to retrieve next and previous items based on ordering and current item only

In MySQL it would be simple. Given an @id value, you can easily find the next or previous rows - and efficiently assuming there is an index on (id) and another index on (created_at):

-- find next item
select t.*
from tablename as t
     join
     ( select created_at
       from tablename
       where id = @id
     ) as current
     on t.created_at > current.created_at
order by t.created_at asc
limit 1 ;   -- N next items

To find the previous items, you only have to change > to < and asc to desc.

If there can be rows with same created_at, then you need to modify:

-- find next item
select t.*
from tablename as t
     join
     ( select created_at, id
       from tablename
       where id = @id
     ) as current
     on t.created_at > current.created_at
     or t.created_at = current.created_at and t.id > current.id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items

The whole current subquery is not truly needed as the values @id and @created_at will likely be available from the previous run so the query can be simplified:

-- find next item
select t.*
from tablename as t
where   t.created_at > @created_at
     or t.created_at = @created_at and t.id > @id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items


Mysql Replication - How it works offline?

Replication sounds like what you need, as long as the slave server you envision will be used as read-only.

Connecting a slave (replica) server to a master imposes very little load on the master.  If the connection between the two is severed for whatever reason, the slave will continue to answer queries, and normally will reconnect to the master as soon as practical and pick up exactly where it left off.  Certain disruptions can cause you to have to take steps to get replication going again, but ordinarily this is automatically done.

Using a replica this way is common.

I produce several hundred scheduled reports every day, many of which evaluate many millions of rows... and the reports are run against slave servers, to relieve the master of this additional workload.  In some cases, the slave is many thousands of miles away from the master, so, naturally, the connections are periodically lost... but they restart themselves.


How to write a query to identify common combinations of parameters used to call a stored proc?

You need to find all of the distinct groups of parameter values for a given proc, and then sort by the count of those groups.  Something like this:

SELECT
    spc.Param1,
    spc.Param2,
    ...,
    spc.Param99,
    COUNT_BIG(*)
FROM dbo.StoredProcedureCall spc
WHERE
    spc.StoredProcedureName = 'sp_YourProc'
GROUP BY
    spc.Param1,
    spc.Param2,
    ...,
    spc.Param99
ORDER BY
    COUNT_BIG(*) DESC;

That would give the different parameters that sp_YourProc was called with, ordered from most common to least common.

If you don't care about the values, and you just want to know that certain combinations of parameters are being used, you could substitute static values like this:

SELECT
    CASE WHEN spc.Param1 <> '' THEN 1 ELSE 0 END,
    CASE WHEN spc.Param2 <> '' THEN 1 ELSE 0 END,
    ...,
    CASE WHEN spc.Param99 <> '' THEN 1 ELSE 0 END,
    COUNT_BIG(*)
FROM dbo.StoredProcedureCall spc
WHERE
    spc.StoredProcedureName = 'sp_YourProc'
GROUP BY
    CASE WHEN spc.Param1 <> '' THEN 1 ELSE 0 END,
    CASE WHEN spc.Param2 <> '' THEN 1 ELSE 0 END,
    ...,
    CASE WHEN spc.Param99 <> '' THEN 1 ELSE 0 END,
ORDER BY
    COUNT_BIG(*) DESC;


MySQL replication: how to safely propagate ignored table structure changes from master to slave?

Never mind. Tried it and it worked without breaking anything.


The decomposition that you have produced is in effect correct, in the sense that the decomposed schemas are in BCNF.

However, as you have already noted, it does not preserve the dependencies, in particular the dependency AB → C is lost.

So you have re-discovered an important point about the decomposition in BCNF: one can always decompose a relation in BCNF, but at the price of sometimes losing one or more dependencies.

What does this mean in practice? We can lose (possibly important) constraints. In this case, for instance, the constraint that for each couple of values AB there is always a single value of C cannot be enforced on the resulting schema.

Can we do something for this? Well, we could decompose instead in 3NF, because the synthesis algorithm used for 3NF is guaranteed to produce always lossless and functional dependency preserving decompositions. In this case, for instance, it will produce the decomposition R1(A B C) and R2(B D), that maintains all the dependencies.

But, wait a moment! we have now a decomposition that does not eliminates all the redundancies eliminated with the BCNF algorithm, since, because of the dependency C → A, we will have the same value of A each time we have a certain value for C.

So, we have now a dilemma: should we prefer the 3NF that preserves the dependencies at the expense of maintaining some redundancy, or should we prefer the BCNF that reduces the redundancies at the expense of losing some “meaning” of the data?

The opinion of many is that we should choose the 3NF, since data meaning is considered more important that data redundancy (and not only this, but because the 3NF algorithm is a polynomial algorithm, while the BCNF algorithm is an exponential one).


I'm not familiar with NoSQL solutions, but it seems that maybe that's the way to go.

I think you're way off in what requires NoSQL and big data. By at least a few orders of magnitude:

100 million is not much, and grows rapidly needs to be quantified.
Schema changes also need to be quantified, do you need schema?
Speaking for PostgreSQL, extending a table does not result in the table being rewritten.

Using PostgreSQL

Following my own advice and creating an email type here is an example,

CREATE EXTENSION citext;
CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

CREATE TABLE contacts (id,name,emailaddress)
AS
  SELECT
    id::int,
    id::text || ' First Last',
    (id::text||'first@last.com')::email
  FROM generate_series(1,100e6) AS gs(id);

Keep in mind, some of that time is spent validating the email addresses against the HTML5 spec. Anyway, that all finishes in 720882.332 ms or 12 minutes on my rusty laptop. Keep in mind, that's without the indexes. Now we add the indexes.

CREATE UNIQUE INDEX ON contacts (name, emailaddress);
ALTER TABLE contacts ADD PRIMARY KEY (id);
ANALYZE contacts;

Tada, test data done.

SELECT * FROM contacts WHERE id = 424242;
   id   |       name        |     emailaddress
--------+-------------------+----------------------
 424242 | 424242 First Last | 424242first@last.com
(1 row)

Time: 4.534 ms

SELECT * FROM contacts WHERE name = '424242 First Last';
   id   |       name        |     emailaddress
--------+-------------------+----------------------
 424242 | 424242 First Last | 424242first@last.com
(1 row)

Time: 5.224 ms

ALTER TABLE contacts ADD COLUMN has_apples bool;
ALTER TABLE
Time: 8.612 ms

ALTER TABLE contacts ADD COLUMN has_bananas bool;
ALTER TABLE
Time: 14.813 ms

Adding columns like that is probably not a good idea, why not use the jsonb type

ALTER TABLE contacts ADD COLUMN fruits JSONB;
ALTER TABLE
Time: 7.957 ms

CREATE INDEX ON contacts USING gin(fruits);
Time: 24928.756 ms

INSERT INTO contacts (id,name,emailaddress,fruits)
VALUES
  (100e6+1, 'Evan Carroll', 'foo@whatever.test', '{"bananas":true}');
INSERT 0 1
Time: 8.630 ms

SELECT * FROM contacts WHERE fruits @> '{"bananas":true}';
    id     |     name     |   emailaddress    | has_apples | has_bananas |      fruits
-----------+--------------+-------------------+------------+-------------+-------------------
 100000001 | Evan Carroll | foo@whatever.test |            |             | {"bananas": true}
(1 row)
Time: 5.188 ms

Etc., Not sure what you mean by "scalable storage", or "unique control of the name+email pair".

Sizes are also manageable. For $70, you can fit the entire index in RAM.

Table: 7301 MB
Index: Pkey: 2.1GB, name/email unique: 6.4GB, gin/json: 102MB.


Correct way to design a shopping cart websites database

The main problem with your schema is the OrderDetail table: you have mixed data which is atomic (the date, the address) along with repeating data - the products ordered.

Your Orders table should be as follows

OrderId (primary key)
OrderNumber
UserID (foreign key to users)
OrderDate

(address really belongs to the user)

whereas the OrderLines table should be as follows

OrderLine (primary key)
OrderId (foreign key to orders)
PartID (foreign key to products)
Quant
Price
Discount
Total (although this can be calculated as quant * price * (100 - discount) * 0.01)

Each order line has a total but there should not be a 'total' field in the orders table; instead, calculate this from the orderlines table.

The 'OrderLine' field might be redundant, if 'OrderId' and 'PartId' can be used as a composite primary key, which would mean that no part can appear twice in the same order. This might seem reasonable, but if you add fields such as 'supply date' (the date by which the customer wants to receive each part), you would have problems if a customer orders 100 widgets to be supplied by the end of December and another 100 widgets to be supplied by the end of January. The surrogate key (orderline) is probably the safest and easiest option.


Unrolling a self-referencing table referred to by another table...?

Use a recursive CTE to expand the folder names to full paths:

WITH
  folderpaths AS
  (
    SELECT
      folder_id,
      folder_name,
      parent_folder_id,
      folder_path = CAST(folder_name AS varchar(1000))
    FROM
      dbo.Folders AS f
    WHERE
      parent_folder_id = 0

    UNION ALL

    SELECT
      c.folder_id,
      c.folder_name,
      c.parent_folder_id,
      folder_path = CAST(p.folder_name + '\' + c.folder_name AS varchar(1000))
    FROM
      dbo.Folders AS c
      INNER JOIN folderpaths AS p ON c.parent_folder_id = p.folder_id
  )
...

For your example, the CTE will produce this output:

folder_id  folder_name  parent_folder_id  folder_path
---------  -----------  ----------------  ------------------
1          temp         0                 temp
2          subdir1      1                 temp\subdir1
4          subdir2      1                 temp\subdir2
3          subsubdir    2                 subdir1\subsubdir

As you can probably guess now, you can just join the Filename table to the CTE and use its folder_path column to get the path column of your output:

WITH
  folderpaths AS
  (
    SELECT
      folder_id,
      folder_name,
      parent_folder_id,
      folder_path = CAST(folder_name AS varchar(1000))
    FROM
      dbo.Folders AS f
    WHERE
      parent_folder_id = 0

    UNION ALL

    SELECT
      c.folder_id,
      c.folder_name,
      c.parent_folder_id,
      folder_path = CAST(p.folder_name + '\' + c.folder_name AS varchar(1000))
    FROM
      dbo.Folders AS c
      INNER JOIN folderpaths AS p ON c.parent_folder_id = p.folder_id
  )
SELECT
  path = fp.folder_path + '\' + fn.filename,
  fn.size
FROM
  dbo.Filenames AS fn
  INNER JOIN folderpaths AS fp ON fn.folder_id = fp.folder_id
;

This is the output:

path                             size
-------------------------------  ----
temp\toplevel1.txt               1
temp\subdir1\onedeep1.txt        2
subdir1\subsubdir\twodeep.txt    4
temp\toplevel2.txt               4
temp\subdir2\onedeep2.txt        3

A live demo of this solution is available at Rextester.


MySQL customized service script for zip installation

SHUTDOWN

MySQL 5.7 now features the SHUTDOWN command, which requiresthe  SHUTDOWN privilege.

SHUTDOWN METHOD #1: Within mysql client

mysql> SHUTDOWN;

SHUTDOWN METHOD #2: From OS using mysql client

MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysql ${MYSQL_CONN} -ANe"SHUTDONW"

SHUTDOWN METHOD #3: Using mysqladmin

This can be done with any MySQL 5.x version

MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysqladmin ${MYSQL_CONN} shutdown

STATUS

You can use mysqladmin

For example, to see if mysqld is alive, run this:

MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysqladmin ${MYSQL_CONN} ping 2>/dev/null | grep "mysqld is alive"

You can also do one of the following:

mysqladmin ${MYSQL_CONN} status
mysqladmin ${MYSQL_CONN} variables
mysqladmin ${MYSQL_CONN} uptime
mysqladmin ${MYSQL_CONN} processlist

AUTO START

Make sure you have chkconfig mysqld on or  chkconfig mysql on enabled

There are no mysql utility programs to auto-startup except for mysqld_safe, which is launched by the mysqld (or mysql) service. This is usually issued by sudo service mysqld start (MySQL 5.7) or sudo service mysql start.

If you would like to attempt to write a custom mysqld_safe script, please see my old posts for ideas

Sep 30, 2011 : Running multiple instances on the same host
Sep 17, 2012 : mysqlservice command syntax


OBSERVATION #1

You mentioned Ask developers put all the temporary tables into a separated database

If your developers are using CREATE TEMPORARY TABLE commands to create temporary tables, they need to use CREATE TABLE instead. Here is why:

With MySQL Replication processing a temporary table, this is what occurs

1) Master run CREATE TEMPORARY TABLE
2) Command inserted into binary log
3) Replication copies this over to the Slave's Relay Logs via I/O Thread
4) Slave SQL Thread runs CREATE TEMPORARY TABLE
5) Slave  processes data with that temp table

Once in a while, someone may run STOP SLAVE; to run a backup. If STOP SLAVE; is issued just after step 4, the temp created disappears and so does its data. When you run START SLAVE; Replication breaks instantly complaining the table does not exist. This is normal because when a DB Connections terminates deliberately or accidently, all temp tables opened using CREATE TEMPORARY TABLE in the DB session are dropped. Running  STOP SLAVE; kill the SQL thread who was holding opening the temp table.

The only workaround for this is to create the table using CREATE TABLE instead of CREATE TEMPORARY TABLE. When run STOP SLAVE;, the temp table you created normally does not disappear.

I have seen this happen maybe 10 times in my DBA career. Fixing it using the binary logs to find out the name of the temp tables, to create those tables using CREATE TABLE, then starting replication up was the only maintenance possible without just brute force copying the master.

OBSERVATION #2

mk-table-sync only works on tables with primary keys and/or unique keys. It works maybe 99% of the time. I have seen instances where the checksum of a table on the master and slave were different. I would run mk-table-sync, there were still differences (Of course, I was doing mk-table-sync in circular replication with 3 masters, which can be a little dangerous. Using it in Master/Slave is far more stable)

OBSERVATION #3

You mentioned There is some unsafe queries. Does it get some problems with MIXED based replication?

It depends. The most popular unsafe query is any UPDATE or DELETE that uses ORDER BY ... LIMIT. With SBR, this could possibly cause MySQL to UPDATE or DELETE rows from a table on the Slave in a different order tham that of the Master. With RBR, I believe the exact changes in a row are more identifiable to UPDATE or DELETE on the Slave.

SOLUTION : Avoid using unsafe queries. Then, you will not worry !!!

OBSERVATION #4

I just read your second link. ROFL !!! I am familiar with the poster of the answer.


Recursive CTE to find Total for all children

This recursive CTE (SQL Fiddle) should work with your sample:

WITH cte(ParentID) AS(
    SELECT ParentID FROM @Instances WHERE [Part] = 'Rivet'
    UNION ALL
    SELECT i.ParentID FROM cte c
    INNER JOIN @Instances i ON c.ParentID = i.InstanceID
    WHERE i.ParentID > 0
)
SELECT ParentID, count(*)
FROM cte
GROUP BY ParentID
ORDER BY ParentID
;

Output

ParentID    Count
1           6
2           3
3           2
4           1

Note: You mentioned in comments that the question only contains a simplified sample table and real data have proper indexes and handle duplicates and data adequately.

Data used (SQL Fiddle):

DECLARE @Instances TABLE(
    [InstanceID] int NOT NULL
    , [Part] NVARCHAR (50) NOT NULL
    , [ParentID] int NOT NULL
);

INSERT INTO @Instances([InstanceID], [Part], [ParentID])
VALUES
    (1, 'Assembly', 0)
    , (50, 'Rivet', 1)
    , (50, 'Rivet', 1)
    , (2, 'SubAssembly', 1)
    , (50, 'Rivet', 2)
    , (51, 'Bolt', 2)
    , (51, 'Bolt', 2)
    , (3, 'SubSubAssembly', 2)
    , (50, 'Rivet', 3)
    , (50, 'Rivet', 3)
    , (4, 'SubAssembly2', 1)
    , (50, 'Rivet', 4)
    , (51, 'Bolt', 4)
;


Modify all tables in a database with a single command

No, there is no such command. But what you can do is write a quick query to generate the SQL for you like so:

USE INFORMATION_SCHEMA;
SELECT
CONCAT("ALTER TABLE `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` CONVERT TO CHARACTER SET UTF8;")
AS MySQLCMD FROM TABLES
WHERE TABLE_SCHEMA = "your_schema_goes_here";

Then you can run the output from this to do what you need.

Sources:

http://forums.mysql.com/read.php?20,244395,244421#msg-244421


Slow query while using DISTINCT in MYSQL with various other conditions (10 million records)

IN ( long list ) often performs badly. Try rewriting using [NOT] EXISTS or [LEFT] JOIN. Example:

SELECT DISTINCT activities.post_id
FROM activities
JOIN <table with followed_user_ids>
   ON activities.user_id = ...
JOIN user_languages
   ON activities.user_id = ...
LEFT JOIN <table with blocked users> bu
   ON activities.user_id = ...
WHERE bu.<attribute> IS NULL -- no match
ORDER BY activities.id DESC
LIMIT 1 OFFSET 10;

Make sure you have appropriate indexes on JOIN predicates.


It is well explained in Paul Randal's article as you mentioned in the question.

Let me walk you through an example.

Server 1 I am taking backup of 3 databases into one file.

BACKUP DATABASE [itdba] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT,NAME = N'itdba_backup'
GO
BACKUP DATABASE [itdbatest] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'itdbatest_backup'
GO
BACKUP DATABASE [itdbatest2] TO  DISK = N'D:\multipleDatabaseBackup.bak' WITH NOFORMAT, NOINIT, NAME = N'itdbatest2_backup'
GO

Now if a copy the backup to Server 2 as you mentioned restoring itdba database is like any other restore but you are having trouble restoring other 2 databases.

If you run below tsql

RESTORE HEADERONLY FROM DISK = N'D:\multipleDatabaseBackup.bak'

you will see the position of backups corresponding to databases.  There are many other columns but I am showing few only.

Take a note of itdbatest database with position 2. I can restore this to another server which does not have the same drive/folder structure as Server 1 using MOVE.

itdbatest database file name and location in server 1.

I copied the backup to Server 2 before restoring.

RESTORE DATABASE itdbatest FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\multipleDatabaseBackup.bak'
WITH REPLACE, FILE = 2,
MOVE 'ITDBATest' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ITDBATest.mdf',
MOVE 'ITDBATest_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\ITDBATest_log.ldf'
GO


MariaDB slave replication server from many sources?

I don't know about "500", but there is "multi-source" replication in recent versions of MySQL, plus MariaDB.

So that is 500 inserts per minute on the Slave?  8/second is no problem.  Even 1 core would not be taxed.  Even if all inserts went into the same table.

Even if multi-source did not let you have 500, you could put, say, 25 instances of MySQL on a single server, each receiving data from 20 Masters.


Large scale, hard to model design advice needed

Store the data in flat text files which are uniquely named based on each system, not in a database.

Databases (particularly relational ones) excel at storing data in tables with a consistant schema, relating those tables to one another and querying them in arbitrary ways. What you are describing is (based on my reading) basically a web page to display text files. There is only one way to access the data (via what is effectively a unique key, the system name), there don't appear to be any relationships, and the data will vary considerably from system to system (hence your issue with lots of columns).

So I don't see much benefit in using a relational database (a document database such as RavenDB, CouchDB or MongoDB may be a better fit, but even they are likely to be overkill for what you describe).

If you're really keen on using MySQL, just store the contends of the file in a table you can lookup via a name:

system_name varchar(255) NOT NULL Primary Key
file_type char(2) NOT NULL = 'F' or 'IF' (for frequent or infrequent)
data Longtext NULL

And parse your textual data in PHP (or simply display it).

You could store your data exactly as you receive it, or process it into XML, JSON or YAML to standardise it.

I just noticed the show common properties requirement.

Perhaps you could have columns in your table for those common properties, but not all the properties. You can quickly query the common properties while still being able to view the full data. (I don't have direct experience with any of the above document databases, but I believe all of them will let you index arbitrary fields in your documents, which will be much more flexible than a fixed number of columns).

If you really want to stay with MySQL, you may want to look into the Entity-Attribute-Value pattern. (IMO, EAV is an anti-pattern, but it should be OK just for the common properties).

Of course, if display time isn't really a big worry, just opening and parsing 1000 text files isn't going to take too long (I'd expect ~10 seconds).


Leveraging an index on another column with the same ordering guarantee

You could create a temporary table containing just the two columns you're interested in, using the key column as a pointer into the "real" table.  Something like:

CREATE TABLE #t
(
    ID int NOT NULL
    , AuditTimestampUtc datetime NOT NULL
);

Insert rows from MyTable like this:

INSERT INTO #t WITH (TABLOCKX) (ID, AuditTimeStampUtc)
SELECT mt.ID
    , mt.AuditTimeStampUtc
FROM dbo.MyTable mt

Then create an index on the table like this:

CREATE CLUSTERED INDEX t_AuditTimeStampUtc
ON #t (AuditTimeStampUtc);

Now, you should be able to query the "real" table, making use of the index on the #temp table, as in:

SELECT <columns from mt>
FROM dbo.MyTable mt
    INNER JOIN #t t ON mt.ID = t.ID
WHERE t.AuditTimeStampUtc >= '2019-06-01 00:00:00'
    AND t.AuditTimeStampUtc < '2019-07-01 00:00:00'

The query above will, probably, do an index seek on the non-clustered index t_AuditTimeStampUtc, with a nested loops join into MyTable.  This may be faster than just querying the original table.  Especially if you need to do multiple queries like this against MyTable.

Copying data from a large table might seem like a bad idea. If the original table had only the two columns, then yes, I'd agree it's a dumb thing to do.  However, if MyTable has many columns, the temp table will only occupy a small fraction of the space of the main table, and will be much more efficient.


SQL query does not recognise derived table

Before answering the question, you are using WHERE instead of JOIN for joining the tables, and join the tables using the company name instead of the row ID / primary key. It's better use JOIN with row ID when joining tables (if your are using the company name as the primary key you really have to add to add a row ID and use it instead as the key).

Regarding your question, the problem is that the subquery w2 can't be used in the right part of the SELECT because is out of scope, but you can use a Common Table Expression to create a "temporary view" that can be used in any part of the query. Then you can use two methods for getting the result:

Using MIN():

with sums as (select companyname, sum(salary) AS sumsal
  from works
  group by companyname
)
select *
from sums
where sumsal=(select min(sumsal) from sums)

Using EXISTS:

with sums as (select companyname, sum(salary) AS sumsal
  from works
  group by companyname
)
select *
from sums
where not exists (select * from sums s2 where s2.sumsal<sums.sumsal)

I have created this db<>fiddle for testing it online, there I have used JOIN with the company Id instead of the name as an example of how you can use that.


There are too many differences to enumerate.  The choice between them is heavily based on your application, which you have not described.

That setting affects writes, not reads.

If you are not exceeding 100 queries per second on slow spinning drives, MySQL will do just fine.  Or, if it does not, then we should look at your indexes and queries.


sp_GetAppLock slow performance unusual behaviour

Try adding SET_XACT_ABORT ON to the stored procedure to ensure the transaction is rolled back following termination due to an attention request. This is a best practice for procs with explicit transactions.

A scenario that could cause the symptoms you describe is that the proc execution timed out during execution due to blocking on the table.  When a client timeout occurs, the client API cancels the proc by sending an attention event to stop execution.  This cancels the executing proc but does not roll back the transaction if SET_XACT_ABORT OFF.  The transaction will remain open until the connection is closed and reused (in the case of a connection pool), the connection is physically closed, or an explicit rollback or commit is issued on the same connection.

This is especially nasty with persistent (non-pooled) connections that don't properly clean up after an exception because all subsequent work performed on the connection is unwittingly done under the context of the open transaction.


Can't default date to CURRENT_TIMESTAMP in MySQL 5.5

From the MySQL 5.5 manual:

  You cannot set the default for a date column to be the value of a
  function such as NOW() or CURRENT_DATE. The exception is that you can
  specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

Therefore, what you want to achieve will work in MySQL 5.5 if you add a TIMESTAMP column instead of a DATE column.

The changes in 5.6.x that allow the functionality are documented here, and I'll quote the relevant summary for completeness:

  As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically
  initializated and updated to the current date and time (that is, the
  current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and
  for at most one TIMESTAMP column per table.


You can check out the Standalone SQL Agent open source project.  It uses the native SQL Agent tables and procedures (which still exist in SQL Express) to store the jobs replacing the SQL Agent itself.  The engine is pretty well done for T/SQL and Dos commands.  Things like SSIS, etc aren't done yet.  Also the editor is only about 1/2 done, but the engine to run the jobs is the important part.


Accessing Data after importing from file

Connect using mysql cli:

mysql -u <username> -p<pass> db_name

The following command will show you the imported tables:

show tables;

Then, access the data:

select * from <table_name>;

where *table_name* is one of the tables in the show tables' output.


SQL Server 2000 - 'Performance: Deadlock'

Update March 5th 2014

-- Removed the CTE and any options that could be incompatible with SQL SERVER 2000

if OBJECT_ID(N'tempdb..#process', N'U') is not null
    drop table #process
GO

if OBJECT_ID(N'tempdb..#tmp', N'U') is not null
    drop table #tmp
GO

select spid as victim, blocked as blocker
into #process
from master..sysprocesses p1
where spid > 50
and blocked > 0
and spid <> blocked

insert into #process
select spid as victim, blocked as blocker
from master..sysprocesses p
inner join #process
on #process.blocker = p.spid
where spid > 50

select  distinct 'dbcc inputbuffer(' + CAST( victim as nvarchar(4)) + ')' as cmd, 'sp_who2 ' + CAST( victim as nvarchar(4)) as spwho, *
into #tmp
from #process
where blocker = 0
order by victim

select *
from #tmp
order by victim

DECLARE @cmd nvarchar(400)
DECLARE @spwho nvarchar(12)

DECLARE curseur CURSOR
FOR
    select  cmd, spwho
    from #tmp
    order by victim

OPEN curseur
FETCH NEXT FROM curseur INTO @cmd, @spwho

WHILE @@FETCH_STATUS = 0
BEGIN
    print (@cmd)
    exec (@cmd)
    print (@spwho)
    exec (@spwho)
    FETCH NEXT FROM curseur INTO @cmd, @spwho
END

CLOSE curseur
DEALLOCATE curseur

GO

select
    db_name(dbid),
    'dbcc inputbuffer(' + CAST(a.spid as nvarchar(4)) + ')',
    a.spid,
    a.blocked,
    a.status,
    a.cmd,
    a.cpu,
    a.waittime,
    a.loginame,
    Base = DB_NAME(a.dbid),
    *
from master.dbo.sysprocesses a
where 1 = 1
    and a.spid > 50
    and spid <> @@spid
order by
    a.blocked desc,
    a.waittime desc,
    a.cpu desc

Update 3/6/2013

So we've seen that no sessions are being blocked. Now we can also look at all current locks on your system.

Show locks server wide MSSQL 2000

Monitoring Lock Activity

select convert(varchar(30), suser_sname(p.sid)) as login,
       convert (smallint, req_spid) As spid,
       convert(varchar(30), db_name(rsc_dbid)) As db_name,
       case rsc_dbid when db_id()
            then convert(varchar(30), object_name(rsc_objid))
            else convert(varchar(30), rsc_objid) end As Object,
       rsc_indid As indid,
       substring (lock_type.name, 1, 4) As Type,
       substring (lock_mode.name, 1, 12) As Mode,
       substring (lock_status.name, 1, 5) As Status,
       substring (rsc_text, 1, 16) as Resource
   from master..syslockinfo s
   join master..spt_values lock_type on s.rsc_type = lock_type.number
   join master..spt_values lock_status on s.req_status = lock_status.number
   join master..spt_values lock_mode on s.req_mode = lock_mode.number -1
   join master..sysprocesses p on s.req_spid = p.spid
   where lock_type.type = 'LR'
     and lock_status.type = 'LS'
     and lock_mode.type = 'L'
     and db_name(rsc_dbid) not in ('master', 'msdb', 'tempdb', 'model')
order by spid, lock_type.number
go

The output will you tell you
1. what type of lock is being held: database, table, page.
2. the mode of the lock: S shared, X exclusive, RangeS-S where a range of values is locked in an index

Update 3/7/2014

I have updated your screen shot with some basic explanations. What we are seeing, is just a bunch of shared database locks.

To be brief, the locks on your system are shared (in other words, they don't block other sessions from accessing the database) and those locks are meant to prevent the database from being dropped, detached or renamed while data is being read and/or written from and to that database and its contents.

Getting back to your original question; there are no deadlocks or blocked sessions on your server. I don't remember having seen this message before, but from what you are sending back, this is definitely not about deadlocks and more likely to be just an informational message.

Or use sp_lock

sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

Which will give you a more detailed view of all locks currently being held on your server.

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


Recover Mysql data from formated hard disk

Before you do anything make sure nothing can overwrite data on the disk. stop all writes to the disk ASAP. Unmount, take it out of a server - whatever is applicable and feasible. Plug the disk in a 64bit Linux machine.

Then you need to take an image from this disk. I prefer dd for the job. It's available everywhere, stores the image as it is.

 dd if=/dev/sdb of=/path/on/sda/faulty_disk.img  conv=noerror

Where /dev/sdb is the disk with data and /path/on/sda/faulty_disk.img is a path on another disk, big enough to store the image. conv=noerror will ignore disk read errors if you get any.

If you prefer to store the image remotely - check out https://twindb.com/take-image-from-corrupted-hard-drive/ , it describes more options.

Then download and compile Undrop-for-InnoDB.
Then follow steps from tutorial:

You need to find InnoDB pages on the image. The stream_parsertool does it:

./stream_parser -f /path/on/sda/faulty_disk.img

The result of the tool will be a directory pages-faulty_disk.img that contains InnoDB pages sorted by page type and index_id. InnoDB stores tables in indexes. The table itself is stored in the PRIMARY index, secondary indexes are stored separately. To recover the data we need to fetch records from PRIMARY indexes. Now, InnoDB identifies indexes by index_id while we, humans, know table names. To find index_id for each table you will need to restore InnoDB dictionary first. You can check the blog post or script recover_dictionary.sh from the undrop-for-innodb toolkit.

Now, let's say you found index_id for each table and there is a respective index file in pages-faulty_disk.img/FIL_PAGE_INDEX/<index_id>.page.

To extract records from the index you need to know the table schema, because InnoDB doesn't store the schema in the index. I hope you have the schema either from old backups, or maybe from the installation script. Worst case you can try to restore the schema from InnoDB dictionary.

When you have the schema and index_id you can generate text dumps from the index.

./c_parser -6f pages-faulty_disk.img/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql

Obviously, sakila.actor is just an example.
Then review the text dump dumps/default/actor and you can load it with an SQL command stored in dumps/default/actor.sql.
The tool's page also refers to some other tutorials which may be useful. Data recovery is quite unpredictable so you have to get familiar with the tool and process.

Hope this helps.


Configuring PostgreSQL for read performance

Data alignment and storage size
Actually, the overhead per index tuple is 8 byte for the tuple header plus 4 byte for the item identifier.
Related:

Use GIN to index bit strings
Calculating and saving space in PostgreSQL

We have three columns for the primary key:
PRIMARY KEY ("Timestamp" , "TimestampIndex" , "KeyTag")

"Timestamp"      timestamp (8 bytes)
"TimestampIndex" smallint  (2 bytes)
"KeyTag"         integer   (4 bytes)

Results in:

 4 bytes for item identifier in the page header (not counting towards multiple of 8 bytes)

 8 bytes for the index tuple header
 8 bytes "Timestamp"
 2 bytes "TimestampIndex"
 2 bytes padding for data alignment
 4 bytes "KeyTag"
 0 padding to the nearest multiple of 8 bytes
-----
28 bytes per index tuple; plus some bytes of overhead.

About measuring object size in this related answer:

Measure the size of a PostgreSQL table row

Order of columns in a multicolumn index
Read these two questions and answers to understand:

Is a composite index also good for queries on the first field?
Working of indexes in PostgreSQL

The way you have your index (primary key), you can retrieve rows without a sorting step, that's appealing, especially with LIMIT. But retrieving the rows seems extremely expensive.
Generally, in a multi-column index, "equality" columns should go first and "range" columns last:

Multicolumn index and performance

Therefore, try an additional index with reversed column order:
CREATE INDEX analogransition_mult_idx1
   ON "AnalogTransition" ("KeyTag", "TimestampIndex", "Timestamp");

It depends on data distribution. But with millions of row, even billion of rows this might be substantially faster.
Tuple size is 8 bytes bigger, due to data alignment & padding. If you are using this as plain index, you might try to drop the third column "Timestamp". May be a bit faster or not (since it might help with sorting).
You might want to keep both indexes. Depending on a number of factors, your original index may be preferable - in particular with a small LIMIT.
autovacuum and table statistics
Your table statistics need to be up to date. I am sure you have autovacuum running.
Since your table seems to be huge and statistics important for the right query plan, I would substantially increase the statistics target for relevant columns:
ALTER TABLE "AnalogTransition" ALTER "Timestamp" SET STATISTICS 1000;

... or even higher with billions of rows. Maximum is 10000, default is 100.
Do that for all columns involved in WHERE or ORDER BY clauses. Then run ANALYZE.
Table layout
While being at it, if you apply what you have learned about data alignment and padding, this optimized table layout should save some disk space and help performance a little (ignoring pk & fk):
CREATE TABLE "AnalogTransition"(
  "Timestamp" timestamp with time zone NOT NULL,
  "KeyTag" integer NOT NULL,
  "TimestampIndex" smallint NOT NULL,
  "TimestampQuality" smallint,
  "UpdateTimestamp" timestamp without time zone, -- (UTC)
  "QualityFlags" smallint,
  "Quality" boolean,
  "Value" numeric
);

CLUSTER / pg_repack / pg_squeeze
To optimize read performance for queries that use a certain index (be it your original one or my suggested alternative), you can rewrite the table in the physical order of the index. CLUSTER does that, but it's rather invasive and requires an exclusive lock for the duration of the operation.
pg_repack is a more sophisticated alternative that can do the same without exclusive lock on the table.
pg_squeeze is a later, similar tool (have not used it, yet).
This can help substantially with huge tables, since much fewer blocks of the table have to be read.
RAM
Generally, 2GB of physical RAM is just not enough to deal with billions of rows quickly. More RAM might go a long way - accompanied by adapted setting: obviously a bigger effective_cache_size to begin with.


How do these snapshot isolation level configurations interact on a SQL Server 2005 instance?

As @AlexKuznetsov noted, SNAPSHOT and READ_COMMITTED_SNAPSHOT are two different isolation levels. As such, snapshot_isolation_state indicates the former, while is_read_committed_snapshot_on the latter. Here are a few comments that I found that summarize the differences between the two:

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes.
In contrast, SNAPSHOT does optimistic reads and optimistic writes.
~Bill Paetzke here

and ...

[READ_COMMITTED_SNAPSHOT] differs from the SNAPSHOT isolation level in
that instead of providing a reader with the last committed version of
the row that was available when the transaction started
(SNAPSHOT ISOLATION), a reader gets the last committed version of the
row that was available when the statement started
(READ_COMMITTED_SNAPSHOT).
~Uri Dimant (emphasis mine), here


Can Availability Groups provide seamless failover (with no query failures)?

No, there's no way to do zero-downtime failover with AlwaysOn (or in SQL Server in general, as far as I'm aware). To do that, the SQL Server you're connected to would have to do state transfer to another node mid-query, and since many failovers are unexpected, that's not possible.

However, you can enable "read-only secondaries" in AlwaysOn, and then your readers would have zero-downtime when the primary server fails over - since they're connecting to a secondary copy anyways to do their SELECT queries, they wouldn't even notice the failover. There would still be an interruption for users with a "Write" connection open, but at least some part of your user base would be uninterrupted.


Session ID in SQLCMD running multi-batch script

You should be able to verify that all batches in a single SQLCMD script are run in the same session by creating a local #temp table at the top of the SQLCMD script and trying to reference that temp table in subsequent batches.  Since the scope of a local temp table ends when the session ends, if subsequent batches were indeed running in different sessions (even though the id was the same), you would get table not found errors.

create table #test(TestColumn varchar(50))
insert into #test(TestColumn) values('I''m still here!')
GO

SELECT @@SPID
SELECT * FROM #test
GO

SELECT @@SPID
SELECT * FROM #test
GO

SELECT @@SPID
SELECT * FROM #test
GO


MySQL: not quoting WHERE conditions slows query execution

First query should give you an error as you're filtering items with string,
the datatype would be CHAR or VARCHAR.

If you you integer, in same columns, the query will rub without quotes but it will be slow because of string datatype.

Second Query is proper written,

However for more performence,
you can also create combine index on (parm1, parm2) with

CREATE INDEX tbl11 ON tbl1 (parm1, parm2))


Yes, giving instance1 a higher priority will make the replicaset re-elect instance1 as the primary when it rejoins. See the docs on Adjust Priority for Replica Set Member and Replica Set Elections for more details:

Member Priority
After a replica set has a stable primary, the election algorithm will make a “best-effort” attempt to have the secondary with the highest priority available call an election. Member priority affects both the timing and the outcome of elections; secondaries with higher priority call elections relatively sooner than secondaries with lower priority, and are also more likely to win. However, a lower priority instance can be elected as primary for brief periods, even if a higher priority secondary is available. Replica set members continue to call elections until the highest priority member available becomes primary.


Can SQL Server (2012-2014) be configured to run "on-demand?"

The best fit for your requirements is probably SQL Server Express LocalDB (2012 and later).

It has a very small footprint, does not create a service, starts on demand, aggressively releases resources, and automatically shuts down a few minutes after the last connection to it is closed. LocalDB is specifically targeted to developers testing on a local machine. Things like external connectivity and SQL Agent are not available.

More information:

Introducing LocalDB, an improved SQL Express
SQL Express v LocalDB v SQL Compact Edition
Getting Started with SQL Server 2012 Express LocalDB


Mirroring multiple source databases to a single destination in SQL Server 2008

Mirroring doesn't work like that. You can't bring multiple databases into a single database. You need to look to Replication, specifically Merge Replication as a solution for this. It can be set up across domains and it's specifically designed to take multiple different databases and bring them back to a single location. Mirroring is for creating a copy, a mirror, of a database. If you have more than one database, you'll have to maintain more than one mirror.


What would I look for in SQL Server to determine if a user connected with Microsoft Access?

The sp_who2 stored procedure lists all active connections and includes the ProgramName.

You could also select from sysprocesses (joining in whatever other information you require such as sysusers) but it's just easier to use sp_who2.

(caveat, this will only work for applications which set the name, some applications may not)


You may have some LOB data (text/image/varchar(max)/nvarchar(max)) sitting in the filegroup still. I got caught up by this briefly not too long ago. Rebuilding a table/index on a different filegroup/partition does not move any of the LOB data.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'

If that's indeed what's happening, the simplest way to move the LOB data is by recreating the table. You can move the data either with bcp out/in, or by inserting directly into a new table and renaming afterward (or by any other preferred method of moving the data you have). Make sure you specify the correct filegroups for row and text data when creating the new table.


Postgres client copy (\copy) command doesn't have access to a temporary table?

\copy can use a temporary table.

First I tested and confirmed this with version 9.0 at the command line.
Then I created a file with SQL and psql meta command \copy using multiple temporary tables. That worked for me, too.

CREATE TEMP TABLE tmp as SELECT * FROM tbl;
\copy (SELECT * FROM tmp JOIN tbl USING (id)) TO '/var/lib/postgres/test1.csv';

Call:

psql -p5432 mydb -f test.sql

Note the terminating semicolon, which is optional at the end of a file (terminated implicitly), but required after any other SQL statement and also after the last one if executed in psql interactively.

Normally, psql meta-commands cannot be mixed with SQL on the same line in a file executed per psql -f. I quote the manual on psql:

  Parsing for arguments stops at the end of the line, or when another
  unquoted backslash is found. An unquoted backslash is taken as the
  beginning of a new meta-command. The special sequence \\ (two
  backslashes) marks the end of arguments and continues parsing SQL
  commands, if any. That way SQL and psql commands can be freely mixed
  on a line. But in any case, the arguments of a meta-command cannot
  continue beyond the end of the line.

Different rules apply after \copy, though. Essentially, psql switches back to SQL mode automatically after \copy See:

How to update selected rows with values from a CSV file in Postgres?

But you wrote you had all commands on separate lines. So that cannot be the explanation in your case.

All that aside, have you considered using COPY (the SQL command) instead of \copy (the psql meta-command)?

Of course, the target file would have to be local to the server not the client in this case. And different file privileges apply. The manual:

  Files named in a COPY command are read or written directly by the
  server, not by the client application. Therefore, they must reside on
  or be accessible to the database server machine, not the client. They
  must be accessible to and readable or writable by the PostgreSQL user
  (the user ID the server runs as), not the client.


Simple query on sys.dm_db_partition_stats is running for 30 minutes

I see where you are coming from. But I don't think you'll get anywhere without changing the query. This is the world we live in, with optimizers and the uncertainty regarding what query plan we get. Make the predicate seekable and you are likely to get a different plan, quite probably to the better. I tried a non-seekable predicate vs a seekable predicate on object_id when selecting from sys.dm_db_partition_stats, and did indeed get different execution plans:
SELECT * FROM sys.dm_db_partition_stats
WHERE object_id  = 123132

SELECT * FROM sys.dm_db_partition_stats
WHERE CAST(object_id AS varchar(20))  = '123132'

So, repeating Denis' suggestion in the first comment:
"Let's begin from changing your WHERE clause pstats.object_id = OBJECT_ID(N'SchemaName.TableName')"
(To avoid a long running comment thread (and Paul's wrath ;-), I put this as an answer which can be elaborated on and expanded on. )


What "users" exist in MySQL?  I hope you are not doing everything as root (or other SUPER user)!

Once you get past this problem, be sure to have a user that will let you get in, but is not being used otherwise for the application.

What I just said is good advice, but it may not be relevant to your symptom.  It seems that your host (at IP xxx.xxx.xxx.xxx) has been pounding on the shared account.  Could this be?  Could it be that a hacker got into your host, and is using it to get at the hosted MySQL?

Is there another machine from which you could try to do the FLUSH HOSTS?


Are there any objective reasons to prefer a prefix over a suffix for an environment-specific login?

I'd rather have the environment be the suffix assuming I had to pick one convention.  Otherwise, it's too difficult to navigate through lists of login names that all use the same first five characters.

Of course, if given my choice, I would prefer to use neither the prefix nor the suffix.  I'd rather keep the same username in all environments and vary my password and TNS alias to identify which system I'm connecting to.


Optimizing concurrent updates in Postgres

There is no ORDER BY in an SQL UPDATE command. Postgres updates rows in arbitrary order:

UPDATE with ORDER BY

To avoid deadlocks with absolute certainty, you could run your statements in serializable transaction isolation. But that's more expensive and you need to prepare to repeat commands on serialization failure.

Your best course of action is probably to lock explicitly with SELECT ... ORDER BY ... FOR UPDATE in a subquery or a standalone SELECT in a transaction - in default "read committed" isolation level. Quoting Tom Lane on pgsql-general:

  Should be all right --- the FOR UPDATE locking is always the last step
  in the SELECT pipeline.

This should do the job:

BEGIN;

SELECT 1
FROM   foo
WHERE  baz = 1234
ORDER  BY bar
FOR    UPDATE;

UPDATE foo
SET    bar = bar + 1
WHERE  baz = 1234;

COMMIT;

A multicolumn index on (baz, bar) might be perfect for performance. But since bar is obviously updated a lot, a single-column index on just (baz) might be even better. Depends on a couple of factors. How many rows per baz? Are HOT updates possible without the multicolumn index? ...

If baz is updated concurrently, there is still an unlikely corner case chance for conflicts (per documentation):

  It is possible for a SELECT command running at the READ COMMITTED
  transaction isolation level and using ORDER BY and a locking clause to
  return rows out of order. ...

Also, if you should have a unique constraint involving bar, consider a DEFERRABLE constraint to avoid unique violations within the same command. Related answer:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?


Can we copy data from 1 text file to 2 postgresql tables?

The SQL COPY command itself has no provision for that. But you can specify PROGRAM as input. The manual:

  When PROGRAM is specified, the server executes the given command and
  reads from the standard output of the program, or writes to the
  standard input of the program. The command must be specified from the
  viewpoint of the server, and be executable by the PostgreSQL user.

Then, assuming you are on a Linux system, you can COPY the first 10 lines with:

COPY tbl1 FROM PROGRAM 'head -n10 /path/to/file/my.csv';

To COPY the rest, starting with line 11:

COPY tbl2 FROM PROGRAM 'sed -n 11,\$p /path/to/file/my.csv';

Or, to COPY without comments with leading # / only comments (asked in comment):

COPY tbl3 FROM PROGRAM 'awk ''!/^#/'' /path/to/file/my.csv';
COPY tbl4 FROM PROGRAM 'awk ''/#/ { sub(/^#/, ""); print }'' /path/to/file/my.csv';

The last command removes the leading # from comments (but not additional whitespace).

Consult the man page or your preferred online source about the shell commands head, sed and awk ...

As always, you need the necessary privileges. Consider the psql meta command \copy if you are not superuser nor member of the pg_execute_server_program role.

Related:

Using Postgres COPY TO for data export without overwriting file


What might be the cause of regular periodic slow transactions on an OLTP system?

High Log File Sync wait events mean that throughput on writing to the online redo logs is not high enough. This blog gives a great explanation of how to diagnose and troubleshoot that event:

http://logicalread.solarwinds.com/oracle-log-file-sync-wait-event-dr01/#.V_TSTvkrKUk

GC Busy and other GC wait events indicate RAC cluster related waits. We'd need to see a lot more detail to troubleshoot this accurately. That said, if you are having Log File Sync waits on some or all nodes, most likely the other nodes will start showing GC wait events. The nodes have to cooperate with each other. When one node has problems, the whole cluster is affected.


IOPS needs of Root Volume vs Database/Log Files Volume

A 'gp2' EBS volume will have plenty of performance for use as a root volume. There won't be any problems.

See details on EBS gp2 volumes on Amazon's documentation.

  General Purpose (SSD) volumes [... have ...] the ability to burst to 3,000 IOPS for extended periods of time, and a baseline performance of 3 IOPS/GiB up to a maximum of 10,000 IOPS (at 3,334 GiB).

...

  Volumes earn I/O credits every second at a baseline performance rate of 3 IOPS per GiB of volume size. For example, a 100 GiB General Purpose (SSD) volume has a baseline performance of 300 IOPS.

You'll be fine. Your root volume shoudn't be doing tons of I/O anyway. Frequently used data like program binaries will be cached in the kernel buffer caches in instance RAM. You're mainly looking at write activity for log files, etc, and it's unlikely that'll be a problem.

Do make sure, if you log via syslog, that flushes are not forced after each log write though. That usually involves prefixing the log output line with a -.


mysql: show which users have permissions to a DB

Have a look at the Query

SELECT
 md.host `Host`,
 md.user `User`,
 md.db `Database`,
 REPLACE(RTRIM(CONCAT(
 IF(md.Select_priv = 'Y', 'Select ', ''),
 IF(md.Insert_priv = 'Y', 'Insert ', ''),
 IF(md.Update_priv = 'Y', 'Update ', ''),
 IF(md.Delete_priv = 'Y', 'Delete ', ''),
 IF(md.Create_priv = 'Y', 'Create ', ''),
 IF(md.Drop_priv = 'Y', 'Drop ', ''),
 IF(md.Grant_priv = 'Y', 'Grant ', ''),
 IF(md.References_priv = 'Y', 'References ', ''),
 IF(md.Index_priv = 'Y', 'Index ', ''),
 IF(md.Alter_priv = 'Y', 'Alter ', ''),
 IF(md.Create_tmp_table_priv = 'Y', 'Create_tmp_table ', ''),
 IF(md.Lock_tables_priv = 'Y', 'Lock_tables ', ''),
 IF(md.Create_view_priv = 'Y', 'Create_view ', ''),
 IF(md.Show_view_priv = 'Y', 'Show_view ', ''),
 IF(md.Create_routine_priv = 'Y', 'Create_routine ', ''),
 IF(md.Alter_routine_priv = 'Y', 'Alter_routine ', ''),
 IF(md.Execute_priv = 'Y', 'Execute ', ''),
 IF(md.Event_priv = 'Y', 'Event ', ''),
 IF(md.Trigger_priv = 'Y', 'Trigger ', '')
 )), ' ', ', ') AS `Privileges`
FROM
 mysql.db md WHERE db='DB_NAME';

It may give result as you desired.

For Reference have a look at How to Get a List of Permissions of MySQL Users.


No, I don't think it's safe to assume locks from dead/vanished clients are released in a bounded and deterministic amount of time with all DBMSes and drivers. You'll need to investigate each configuration separately.

In the case of PostgreSQL you're generally but not always OK if you have TCP keepalives set quite aggressively, because:

If the whole client application process dies but the client host stays up the host's kernel will RST the TCP connection as part of process cleanup;
If the client host dies entirely then it'll stop responding to tcp keepalives; and
If the client host remains alive but the network fails in one or both directions between client and server then it'll stop responding to tcp keepalives.

However, there are a few cases that will not be handled:

Connection pool bugs that result in a connection being returned to the pool with a transaction still open and holding locks;
Connection pools that don't DISCARD ALL and thus fail to release and reset session-level resources like advisory locks (if you use them);
App server based applications that 'leak' connections with open transactions so the connection pool can never reclaim them;
Badly written programs that intentionally hold a transaction open during user "think time" like a dialog box or data entry window, where the user might go away and make a coffee ... or go on holiday for a month;
Cases where the application process remains in existence but is totally non-responsive due to being SIGSTOPped, having been paused by a debugger, hitting an internal threading deadlock, etc. The OS will keep on responding to tcp keepalives but the app won't respond to Pg protocol messages or advance its work.

In the case of PostgreSQL you can use active lock monitoring to scan for and terminate long running transactions that haven't done anything in a while. In particular, you can deal with <IDLE> in transaction sessions by scanning pg_stat_activity (though it's only possible to do this RELIABLY and EASILY in 9.2). With a bit more effort you can use pg_locks to watch for queries blocked on a lock for more than x seconds and kill the session holding the lock, though this can make it hard to run some DDL like index creation.

What you really need is application level keepalives, where the app says "Yup, I'm alive and responsive". These are rather harder to implement, though.

One thing that will help is that both PgBouncer and PgPool-II (external connection pools for PostgreSQL) support controls for session and transaction timeouts. We've wanted to implement similar options in the core PostgreSQL for some time, but nobody's come up with a design that's robust enough to handle all the corner cases, so for now your best bet is to use an external pooler. You can do this even if you're also using an application-level connection pool.

On the good news front, PostgreSQL automatically detects and breaks deadlocks between transactions, so one thing you don't have to worry about much is deadlocks at the SQL level when using PostgreSQL.