Performance in replicating table between servers I'm using SQL Server 2014 and I have two databases, in different region of the world. One in Brazil and the another in China. The databases are exactly the same, except by the ID column, with they have different ranges.

These two databases, they need to communicate to each other the records that have been inserted, deleted and updated. For that, I created a column named UpdatedDate in each table, and after each operation this column is updated with the GetUtcDate(), this way, I can know with record I need to update.

Each minute, I run a task in each server that compared both databases.

The problem is, some tables have many records (Around 500k), and when the server is updating those tables, the application gets terribly slow.

The databases are totally separated and have no VPN connection, because of that that I'm using SQL Server tasks.

Here it's a example of the task that runs the update:

Update
    LocalTable
Set
    LocalTable.RecordA = RemoteTable.RecordA,
    LocalTable.RecordB = RemoteTable.RecordB,
    LocalTable.UpdatedDate = RemoteTable.UpdatedDate
From
    MyTable LocalTable
    Inner Join [RemoteServer].MyDatabase.dbo.MyTable RemoteTable On LocalTable.ID = RemoteTable.ID
Where
    LocalTable.UpdatedDate < RemoteTable.UpdatedDate;

It's there a way to improve the performance? I've thought in limiting the RemoteTableUpdatedDate to get only the changes from the last three days.

I've also though in creating an index with the UpdatedDate and the ID, including the rest columns... Bu I don't have very much expertise in SQL Server.

Thanks for the help!

if you want the records that have been changed you could use the OUTPUT Clause (Transact-SQL)

you can also see an example of this here

Regarding your update statement, you definitely need an index on the ID column in both tables. I would even consider creating it as clustered, but I don't know your tables structure and your application.

you could for example, use the output clause to save the changed records into an specific table(s) and replicate that/those table(s).


postgresql, duplicate database which is a replication of another I have original DB-original
and have a streaming-replication DB-replica

I want a writable copy of DB-replica and did

createdb -T DB-replica DB-copy-of-replica

  createdb: database creation failed: ERROR:  cannot execute CREATE
  DATABASE in a read-only transaction

How do I copy the DB?

It looks like you have a physical standby/replica. In physical standby, you cannot have a writeable copy of any database. If you want a writable copy of the database, you can follow either of the following:

Create a new cluster postgreSQL and export the database from a READ replica (physical standby) using pg_dump and pg_restore. (Refer the pg_dump/pg_restore command from PostgreSQL document)
You can use logical replication from the master
https://www.enterprisedb.com/postgres-tutorials/logical-replication-postgresql-explained


Does SQL Server TDE decrypts when it enters the buffer pool and encrypts when it leaves Been searching but no clear understand.  TDE encrypts at rest not in flight.  But does in flight refer to only between server and client or also between server and SAN storage.

When a database uses TDE, does a page, when it is written to the disk, leave the buffer pool encrypted.  So if somebody were to sniff the wire between the DB server and the SAN see only encrypted data.  Or does it flow unencryted to the SAN and then is encrypted as its being saved.  Same when is it decrypted, when it reaches the buffer pool or when it leaves the SAN? (note I understand when it goes to the client it is not encrypted)

This link was related to backups
Is network traffic encrypted when writing remote backups using SQL Server TDE?
Does it apply to data also?

Reference to docs would be great.

Yes, it is encrypted both "at rest" on disk, and also "in flight" between disk and SQL server.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

  TDE performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files.

In other words, the data isn't decrypted until it gets to the SQL server.

Think of it this way: for it to be decrypted in-flight, the SAN itself would have to decrypt it before fulfilling the request and sending the data to SQL. And we know it isn't doing that, it is SQL that is encrypting/decrypting.


advantages and disadvantages of using direct sql querying in application could people please give their opinion on and give advantages and disadvantages on the use of sql queries directly within an application as opposed to creating procedures or functions within the database and using them.

Raw SQL

A good inline SQL query, if made correctly using parameters  is as
good as a stored procedure.
Raw SQL is  more flexible to deal with.

Stored procedures

Stored procedures have the benefits of security , but just raw
performance isn't one of their major plus points.
Stored procedures are more maintainable.
Stored procedures are precompiled
The security and data layer separation benefits and increased ease of
performance tuning provided by strictly using stored procs generally
make them a better solution than ad-hoc queries regardless of any
relative performance enhancement that may or may not come with the
pre-compilation of the procs.
scalability and modularity

Conclusion

I'd personally like to utilize only Stored Procs for database access.
The only thing I see SQL doing that stored procs can't do (easily) is dynamically choosing the sort order. Other than that, it's about knowing how to code the stored procs the way you need.


Login / Password for Oracle Enterprise Manager in VM template? I want to setup Oracle Enterprise Manager to access our development Oracle databases.

I downloaded the following VM template from https://edelivery.oracle.com/EPD/Search/handle_go

Oracle VM VirtualBox for Oracle Enterprise Manager Cloud Control 12c Release 3 (12.1.0.3) Media Pack for x86 (64 bit)

I unzipped the OVA files, merged them and imported them into Oracle Virtualbox.

Then I started up the VM, logged into Sqlplus and changed the password of SYSMAN and unlocked that account. Then I pointed Firefox (inside the VM) to https://emcc.example.com:7799/em , where the Login screen of Enterprise Manager was shown.

However, I cannot log in to Enterprise Manager. I tried it with the following usernames/passwords:

SYSMAN / [the new SYSMAN password I entered before]
oracle / welcome1 (the Linux account)
cloudadm / cloud (these are credentials I found on the web)

It always says Authentication failed. If problem persists, contact your system administrator.

What are the credentials to log into Enterprise Manager in these VM templates by Oracle?

There is a Readme for each Oracle Template download. Here is the related part from the Readme for your template:

5. O/S passwords: The passwords for the "root" and "oracle" O/S users are "welcome1"

6. All Enterprise Manager Cloud Control passwords are "welcome1"


Can I query a tab-delimited file from SSMS? Is it possible to query a tab-delimited file from Sql Server Management Studio to view its data without saving it anywhere?

I know you can BULK INSERT from a tab-delimited file using something like:

BULK INSERT SomeTable
FROM 'MyFile.txt'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n');

however that requires you know the columns in advance and create a table to hold the data.

I also know you can query some other file types such as CSV or Excel without defining the columns in advance using OPENROWSET and the Excel drivers, such as:

-- Query CSV
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=\\Server\Folder\;HDR=Yes;',
    'SELECT * FROM MyFile.csv')

-- Query Excel
SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0',
    'Excel 8.0;Database=MyFile.xls',
    'SELECT * FROM [Sheet1$]')

Also, if I change the registry key Format under HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Text from CSVDelimited to TabDelimited on the SQL Server, the CSV query above will correctly read a tab-delimited text file, however it will no longer read a comma-delimited text file so I don't think I want to leave it like that.

Attempting to use Format=TabDelimited in the OPENROWSET does not work either

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Text;Database=\\Server\Folder\;HDR=Yes;Format=TabDelimited',
    'SELECT * FROM MyFile.txt')

I have made some attempts to copy the Text registry keys from both the Engines and ISAM Formats keys to something custom that defaults to TabDelimited, however it is still reading files with CSVFormat instead of TabDelimited format so I must be missing something here.

Is there a way to query a tab-delimited file to view its contents without having to create a table and BULK INSERT it?

I am using SQL Server 2005

You must create a schema.ini file containing the delimiter in the same directory as the text file you are opening. This is the only way to override the registry values on a per-file basis. See the file format documentation on MSDN. Example:

SELECT *
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text; HDR=YES; Database=C:\Text',
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:

[testupload2.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0

If this activity needs to be repeated frequently, I would suggest a script to create schema.ini. Multiple files can be referenced in the same schema.ini or a separate schema.ini can be included with each text file in its own directory.


MySQL - Flush Memory after loading files I am loading a great amount of data which are in CSV files using LOAD FILE command. It is working fine and after loading that files, it executes some data transformation...

The problem is that, after loading for example 12 files (400 MB each one), mysql process consumes almost all memory available, it is not flushing, and so, the machine slow down, and I need to restart mysql to continue to load data files.

MySQL 8.0.17

Any ideas how to solve this issue?

Added Store Procedure

BEGIN
DECLARE sequenceid INT;

DECLARE `_rollback` BOOL DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;

START TRANSACTION;

INSERT INTO filenamedt (filename, filedate, fileinitialid) VALUES (CAfilename, now(), (SELECT MAX(imp_sequence_id) + 1 FROM imp_siscori_sequence));

INSERT INTO imp (select imp, ordem, CONCAT(TRIM(data),'01')) FROM temp_imp);

INSERT INTO imp_data (imp_id, unid_comerc, incoterm) (SELECT imp_id, TRIM(unidadeComercial),  TRIM(incoterm),  TRIM(natInformacao) FROM temp_imp );

INSERT INTO imp_description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);

UPDATE imp_sequence SET imp_sequence_id  = (SELECT MAX(imp_id) + 1 FROM temp_imp_siscori);

TRUNCATE TABLE temp_imp;

SET @m = (SELECT MAX(imp_sequence_id) + 1 FROM imp_sequence);

SET @s = CONCAT('ALTER TABLE temp_imp AUTO_INCREMENT=', @m);

PREPARE stmt1 FROM @s;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

IF `_rollback` THEN

    ROLLBACK;

ELSE

    COMMIT;

END IF;

If you are not using MyISAM for anything (and you should not), then change key_buffer_size 999997440 to only 10M.  That is a wasted gigabyte.

Also, this seems quite large for your 8GB of RAM:  tmp_table_size  268435456.  Lower it to 16M.


Unable to set user/group as principal on a proxy I'm organizing permissions on our SQL Server instance. I want to stop using a SQL Server account, and instead grant permissions to Active Directory groups, and then add user and service accounts to these groups. I'm also trying to remove sysadmin role from users.

I've followed instructions from Grant Admin to an Active Directory account in SQL Server and Deny users permission to create tables in master.

After doing those changes, everything seemed working... until I tried to manually start a job. I got the message non-sysadmins have been denied permission to run dts execution job spets without a proxy account.

If I add the AD group to sysadmin it works. If I remove, it fails.

Following Running a SSIS Package from SQL Server Agent Using a Proxy Account I created a proxy. Then I started getting the message Unable to start execution of step 1 reason jobowner doesn't have permission to use proxy 1 for subsystem.

How to give SQL Server Job Owner permission to Proxy a Credential explains how to set a user as principal to the proxy. It didn't work. I tried adding the group and a user. But when I click OK and reopen the dialog, the one I added isn't listed anymore.

So, basically, how to grant a non-sysadmin user permission to execute jobs? Preferably granting it to an AD group instead of specific users.

A post for handling job permissions can be found at:

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

This post outlines:

Using standard SQL Agent roles such as SQLAgentOperatorRole.
 Note: Active Directory Groups are not able to login to start a job.
Erland Sommarskog's post http://www.sommarskog.se/grantperm.html#countersignatures outlines using countersignatures.  This is a more complex setup, but it does work.
Points to a custom StartAgentJob stored procedure to allow rights to be granted within the context of the procedure.  This includes support of AD Groups as well.  The custom StartAgentJob has code in it that explicitly allows members of an Active Directory Group (but not the Active Directory Group itself) to start a job that they do not own.


Why do shadow processes exist? Oracle architecture has two types of process: server process or foreground process and background process, but:

  Some  people use it to refer to foreground processes, others use it
  for background  processes

This is ambiguous. Please explain to me why do the concept of shadow process exist?

Background processes are processes that are part of the instance. They start when the instance starts, and exit when the instance shuts down. These processes are required for the instance to function or to do the work that would otherwise require starting these processes every time the work needs to be done (which would probably be ineffective). The examples of background processes are job coordinator background process (cjqN) which coordinates job-slave processes running jobs on schedule, and LGWR background process which writes redo entries to the redo log files.

Server processes, on the other hand, are mostly transient: they start when needed, do some work, and exit. Sometimes people refer to server processes as foreground processes, as opposed to background processes. The examples of foreground processes are the server processes that support user sessions, Scheduler job-slave processes (jNNN) which are started by the job coordinator process described earlier, do some work, and eventually exit if there are no other jobs to do.

Server processes are sometimes also referred to as shadow processes, but I would avoid this term, because it seems that it's not commonly used, and may confuse you – I found only 25 occurrences of "shadow process" in Oracle Database 11g Release 2 documentation (one being mentioned in "Shadow Server Architecture" section of Database Concepts), as opposed to 305 occurrences of "server process", and 182 occurrences of "background process".

As far as OCA exam is concerned, I didn't encounter "shadow process" term in any of the questions on the real exam, and this term appeared only twice in the guides I used to prepare for the exam, one of them explicitly stating that its use should be avoided.


SSIS Error :Cannot roll back .No transaction or savepoint of that name was found Hope you are Doing well.
I have 1 SSIS Package as you can see below :

In the "Insert" part of the Package which is an Execute sql task,
I'm executing a Stored procedure . this is the code inside the task

(Exec [dbo].[Main] ?,?,?,?,?)

Which has a transnational format like this Begin Try  Begin Tran..... Commit Tran End Try / Begin Catch  Roll Back Tran .. End Catch . And Then I configured the whole package as one "Transaction" by setting the transaction option of my entire package to "Required" . After executing the package I run into this error (The error is for Execute sql task) : "Cannot roll back Main. No transaction or savepoint of that name was found."

Guys , I'm sure that this error is because I'm  calling a stored procedure which has a transnational format inside another transaction which is the entire package but are there any ways that I can handle this?
The stored procedure must have the transnational format because I'm handling error inside it.

Thanks in advance

Update :
After removing the name "Main" From my transaction I ran into this error

:Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction

The general best practice is to perform transaction management in either the client application (SSIS), server-side T-SQL, but not both.
This allows the SSIS package to determine and control the transactional unit of work and manage transactions among tasks that support transactions.
Individual stored procedures shouldn't start transactions in this case since the outer transaction will ensure all-or-none behavior.

You could instead design the package such that it is idempotent (rerunnable) and not use transactions at all, perhaps also using checkpoints to avoid rework of expensive processing. In that case, the proc could be coded to insert only if the new row
doesn't already exist. This technique helps avoid issues caused by long-running transactions, such as blocking and large transaction logs.


Select first instance of a set of items I'm attempting to modify an existing query that pulls the most recent order from a set of customers and provided order_type.

The current query works fine (albeit a little slow, all columns below are indexed):

SELECT
    DISTINCT(td1.product_id),
    td1.customer_id,
    td1.date_test_added,
    tdmi.product_type_id
FROM
    order_history AS td1
    LEFT JOIN order_history td2
    ON td1.customer_id = td2.customer_id AND td1.date_test_added < td2.date_test_added
    LEFT JOIN order_types tdmi
    ON tdmi.product_id   = td1.product_id
WHERE
   td2.date_test_added IS NULL
   AND tdmi.product_type_id = 31
   AND td1.customer_id IN (91000, 91001, 91002, 91003)

If I change the less-than operator in the first join to greater than, no results are returned.

What is the best way to change this to pull the earliest order (based on date_test_added)?

You query says:

  Pull the most recent order from a set of customers and only if that order has product with type 31.

When you change the < to > it says:

  Pull the oldest order from a set of customers and only if that order has product with type 31.

So it is possible that the modified query to return no results, even the first query, with same parameters returns something. That would happen when the oldest order has product with type different than 31.

If that wasn't the intention of the query but you want the most recent (or oldest) order among the orders that have a product of type 31, you need to modify it, so the LEFT JOIN / IS NULL antijoin considers this restriction.

Some other minor notes:

the LEFT JOIN order_types tdmi works as an INNER join really, due to the tdmi.product_type_id = 31 condition in WHERE.
DISTINCT is not a function. It modifies the whole SELECT and not just one column, so the parentheses are redundant and can only bring confusion.
In a not so minor note, the DISTINCT looks suspicious. It can probably be removed.

The query becomes:

SELECT -- DISTINCT
    td1.product_id,
    td1.customer_id,
    td1.date_test_added,
    tdmi.product_type_id
FROM
    order_history AS td1
    LEFT JOIN order_history td2
        JOIN order_types tdmi2
            ON  tdmi2.product_id = td2.product_id
            AND tdmi2.product_type_id = 31
        ON  td1.customer_id = td2.customer_id
        AND td1.date_test_added < td2.date_test_added
    JOIN order_types tdmi
        ON  tdmi.product_id = td1.product_id
        AND tdmi.product_type_id = 31
WHERE
   td2.date_test_added IS NULL
   AND td1.customer_id IN (91000, 91001, 91002, 91003) ;


Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner? I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without creating the materialized view, it shows that said query is parallel aware, and executing said query by itself shows multiple processors active using standard linux tools like top. Conversely, executing the query within a REFRESH or CREATE materialized view wrapper, does not show multiple processes, and using EXPLAIN on CREATE MATERIALIZED VIEW shows a query that is not parallel aware. Is this the intentional behavior and why? I was not able to find any information on this. (PostgreSQL 10.5 on CentOS 7)

EDIT: Checked that this behavior is the same when using CREATE TABLE AS and SELECT INTO. Is there any way to write a table using parallelism?

EDIT 2: Is there any work-around I can use to CREATE TABLE using parallelism without first outputting the results of query to a file? Such as somehow splitting the query into several hundred equal parts defined by a aggregation variable in the data, followed by a specified number of cores in parts getting INSERT'ed in parallel?

Seems like this is a feature of Postgres 11: https://www.postgresql.org/docs/current/release-11.html#id-1.11.6.6.4


MySQL: which innodb parameters should I tune if disk I/O is slow Which InnoDB parameters should I tune if disk I/O is slow?

Please give your suggestions.

I have another question regarding innodb_log_file :-
What happen if innodb_log_file become full ? so what is mysql next action on this situation ?

That depends on why your disk I/O is slow and how many safeties you are willing to disable. Some examples:

sync_binlog=0 # if you use binlogs - you could lose up to typically 5 seconds of transactions that were written to the binlog

innodb_doublewrite=0 # you will end up with corrupted data if a page written to the transaction log is torn due to a crash or power failure

innodb_flush_logs_at_trx_commit=0 # you will lose up to typically 5 seconds of transactions that were written to InnoDB.

innodb_io_capacity and innodb_io_capacity_max - make sure these are set appropriately for your disk speed. Defaults are 200 and 2000 respectively, which is probably too high for regular SATA HDDs and certainly too low for SSDs, especially high end NVMe SSDs.


EXEC vs SP_EXECUTESQL Performance Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC.

I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference in performance when using EXEC vs SP_EXECUTESQL.

This is mostly a preference due to security and consistency, and has nothing to do with performance (though that may have been more of a concern in ancient versions of SQL Server).

Why use EXEC() some of the time when you should be using sp_executesql whenever you have parameters? EXEC() forces you to concatenate all of your variables into a single string, and this makes it ripe for abuse.

I wrote about this in more detail here:

Bad Habits to Kick : Using EXEC() instead of sp_executesql

I also wrote about protecting yourself from SQL injection here:

Protecting Yourself from SQL Injection in SQL Server - Part 1
Protecting Yourself from SQL Injection in SQL Server - Part 2

SQL injection is a pretty big deal, and plenty of other people have written about it too.

Finally, be sure when you call system procedures that you use the proper casing to match what's stored in sys.all_objects - it should be all lower case. Otherwise, if your code gets deployed to a case sensitive instance, it will all start failing.


An efficient way to compare two large data sets in SQL Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations.

The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and end May 2014 (result = 450K lines). The 2nd data set has the unique StoreKey/ProductKey combinations, for sales begin June 2014, until today (result = 190K lines).

I'm looking to find the StoreKey/ProductKey combinations that are in the 2nd set, but not in the 1st set - i.e. new products sold from the beginning of June.

Up until now, I've dumped the two data sets into temp tables, created indexes for both tables on both keys, and used the EXCEPT statement to find unique items.

What is the most efficient way of comparing such large data sets?
Is there a more efficient way of doing this type of large comparison?

Using EXCEPT is in my opinion the way to go here, but you might want to reconsider the use of the temporary table.  By doing so you are effectively duplicating your data in memory, which will slow you down. If the indexes you need exist on the source tables (as I suspect), just compare the appropriate SELECTS:

SELECT StoreKey,ProductKey FROM table WHERE sales BETWEEN date1 AND date2
EXCEPT
SELECT StoreKey,ProductKey FROM table WHERE sales BETWEEN date3 AND date4


General Database Optimizations - correct data types - why to avoid NULL? Oracle recommends:

Choose the correct data types and size to:
– Avoid NULLs
– Improve performance
– Protect data
– Use data compression where appropriate

I don't understand why I need to avoid NULL. Please explain.

While I do use NULL columns, there is overhead.  The Oracle documentation you retrieved this short list should explain if you read further.

There are case where NULL indicates issues with data types and/or just having the column.

Consider PHONE_NUMBER NUMBER(15):  This will likely have a formatted phone number column, and may be null for numbers like 1-800-CALL-NUM.
Other columns may be NULL because you are unable to gather the data require to fill them.  Consider dropping the columns if is unlikely you will be gather the data.
NULL columns may belong to a sub-type.  It may be appropriate to have a related table containing type related data. This would have an optional 1 to 1 (1 to (0,1)) relationship.
NULL columns are problematic for indexes.
NULL columns may be state related, such as DATE_SHIPPED on a order.  It would be appropriate for this to be NULL before the order is shipped.  However, it would be inappropriate for a has_shipped indicator column to be NULL.

It is always possible to normalize NULL columns out of the database.  It may not always be appropriate to do so.

I do try to put NULL columns last. I've posted on column order previously.


DBCC CHECKDB LastKnownGood I created the following query to list the DBCC CHECKDB LastKnownGood timestamp for all Databases on a Server.  Is there a better way to do this?

IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL
BEGIN
    DROP TABLE #Results
END

CREATE TABLE #Results (ParentObject varchar(100), Object varchar(100), Field varchar(100), Value varchar(100))
GO

INSERT INTO #Results
EXEC sp_msForEachdb @command1 = 'DBCC DBINFO (''?'') WITH TABLERESULTS'

ALTER TABLE #Results ADD ID INT IDENTITY(1, 1)
GO -- required here

SELECT r.Value as [Database], r2.Value as CheckDB_LastKnownGood
FROM #Results r
INNER JOIN #Results r2
ON r2.ID = (SELECT MIN(ID) FROM #Results WHERE Field = 'dbi_dbccLastKnownGood' AND ID > r.ID)
WHERE r.Field = 'dbi_dbname'
ORDER BY r.Value

I would use the PowerShell Command from the dbatools module Get-DbaLastGoodCheckDb which will enable you to check all of your servers in one go.

Here is a sample output from the command

You can get detailed information like this

and check a whole bunch of servers like this

It's really quick too. In my lab of 10 servers with 125 databases it completes in 5.3 seconds

You can read more about dbatools at https://dbatools.io


selecting more fields causes catastrophic failure I have a connection to a linked server from sql server 2012 to pervasive sql.

When I do select field1, field2, field3 from mytable everything works!

However, when I do select field1, field2, field3, field4, ...field10+ from mytable I get this erro message:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "KSLAP208" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "KSLAP208".

I looked at the logs and this is what it says:

2013-02-08 11:27:30.93 spid55      ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0049.txt
2013-02-08 11:27:30.93 spid55      SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2013-02-08 11:27:30.93 spid55      * *******************************************************************************
2013-02-08 11:27:30.93 spid55      *
2013-02-08 11:27:30.93 spid55      * BEGIN STACK DUMP:
2013-02-08 11:27:30.93 spid55      *   02/08/13 11:27:30 spid 55
2013-02-08 11:27:30.93 spid55      *
2013-02-08 11:27:30.93 spid55      *
2013-02-08 11:27:30.93 spid55      *   Exception Address = 00000000018F5BBE Module(UNKNOWN+0000000000000000)
2013-02-08 11:27:30.93 spid55      *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
2013-02-08 11:27:30.93 spid55      *   Access Violation occurred reading address 0000000009400090
2013-02-08 11:27:30.93 spid55      * Input Buffer 128 bytes -
2013-02-08 11:27:30.93 spid55      *             select * from [KSLAP208].[C003]..PA_Profile_BASE_1119

I am able to do this same exact select * from a different server without problems!

What's happening here? Is this some kind of 2012 bug or a memory issue?

So you are using Microsoft OLE DB Provider for ODBC (MSDASQL) which is presumably pointing to the ODBC DSN for your Pervasive data source. Since the error is an Access Violation (AV), I'm willing to bet that the AV is raised due to something going wrong in the Pervasive ODBC driver. Since you also mention that this query works from some other machine, can you double check the:

ODBC DSN configuration between the 'bad' machine and the 'good' machine?
The ODBC driver version for Pervasive - is it the same on both 'good' and 'bad' machines?


Backup with pgAdmin ordered by ID I want to backup a postgres table with pgAdmin and have the dump ordered by ID (or any other column). How do I do that?

[EDIT] The dump should contain INSERT queries, not the pure data! [/EDIT]

What I tested so far:

1) I checked pg_dump too and did not find any solution.

2) Another solution would be to export the results of a SELECT statement which is not possible too with pgAdmin.

3) With psql the only solution I found was psql -h localhost -U myuser -W -c 'copy (SELECT * FROM mytable ORDER BY id) to stdout' which only prints the data as csv.

Can anyone help me out? Thanks!

I use pgAdmin 1.14.2 on Ubuntu, but I imagine this will work anywhere else as well.

In pgAdmin, go to Tools -> Backup..., where you'll get a dialog with a few tabs.  On the first one (File Options) give a filename and choose the 'Plain' format from the dropdown list.  On the next tab (Dump Options #1) tick the 'Use Insert commands' option at the bottom and the 'Only data' option at the top (and don't tick the 'Use Column Inserts' one).  On the Objects tab you can choose whichever tables you need then press the Done button.

Now you'll get a file with the INSERT statements for every row - and some other commands as well.  You can remove the latter with your favourite editor.  Then the list can be sorted - if you want to sort by the first column in the table (that is, more often than not the ID) then you can use a simple sort.  If you need a different sorting then you have to apply some magic in an editor.


Timestamp Difference - Syntax Error - Condtitional Selects Here is what I have right now

SELECT name, admin, reason, unban,
FROM_UNIXTIME(date) AS datetime, HOUR(difference) AS HRemain,
MINUTE(difference) AS MRemain
FROM (SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned, banned)
WHERE name = '%e'

I get some syntax error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned, banned) WH' at line 3

Here is what I am trying to achieve.

Table banned

name   | admin     | reason | unban(TIMESTAMP) | date
Yashas | SomeAdmin | test   | 180              | 60

Required Results:

Player who was banned: Yashas (given)
Administrator who banned: SomeAdmin (need to fetch from the db)
Banned on: January 1st, 1971 (must be obtained from date) (Need the string as datetime) (I decided that I don't need the time, just the date as a string)
Unban Time left: 0 Hours 2 Minutes (I need the hours left as HRemain and minutes left as MRemain)

I need the following:

unban as a date-time string
time remaining for unban from current time. I need just hours and minutes remaining.
and rest of the content of the table as it is including unban

If possible optimize the query for the following:
If unban is set to 0 then it means its a permanent ban and therefore I don't need the time remaining until unban. So in every case where unban is zero, I don't need HRemain, MRemain.

Thanks

EDIT:

CREATE TABLE `banned` (
`name` varchar(21) NOT NULL,
`admin` varchar(21) NOT NULL,
`reason` varchar(81) NOT NULL,
`unban` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My old query which was working but isn't efficient.

SELECT name, admin, reason, unban, FROM_UNIXTIME(date) AS datetime,
HOUR(SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW())))) AS HRemain,
MINUTE(SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW())))) AS MRemain
FROM banned WHERE name = '%e'

SELECT name, admin, reason, unban, FROM_UNIXTIME(date, '%Y-%m-%d') AS datetime,
HOUR(x.difference) AS HRemain, MINUTE(x.difference) AS MRemain
FROM(SELECT SEC_TO_TIME(AVG(TIMESTAMPDIFF(MINUTE, unban, NOW()))) AS difference FROM banned) AS x, banned
WHERE name LIKE 'Yashas'

Solved it myself. Just had to get a textbook and study.

EDIT: Server returns datetime as NULL for some reason.


Passing each value from a temp table to a scalar function and sum values I am relativity newbie in SQL so please do not  judge strictly. I have a table valued function.Within that function I have a this code:

 DECLARE @totalPayment DECIMAL(18,4),
         @totalPaymentConverted DECIMAL(18,4),
         @fromCurrencyIdForPaymentSystemsId CHAR(3);

 DECLARE @GetTotalPaymentAmount AS TABLE
 (
   Amount     DECIMAL(18,4),
   CurrencyId CHAR(3)
 );

INSERT INTO @GetTotalPaymentAmount
  SELECT    SUM(Amount),CurrencyId
  FROM      [dbo].[fn_DepositWithdrawReport]()
  WHERE     OperationTypeId = 2
  GROUP BY  CurrencyId;

This insert return met table like this

 A      C
-----|------
550  | USD
650  | EU
     |

I want to pass each of this values Amount and CurrencyId to the Scalar valued Function as Input parameter like this:

[dbo].[function_Valued](@totalPayment,@fromCurencyId,@toCurrencyId)

And sum that values for instance if we consider the table above:

[dbo].[functin_scalar](550, USD, EU) = 450
[dbo].[function_scalar](650, EU, EU) = 650

Get total 1100

Put simply I want to calculate sum amount and I need to convert it in one currency. I have been told to use APPLY but I cannot understand how to use it in my code. Maybe someone could show in code example?

It is fairly straightforward with scalar functions, although your description is very confusing and hard to follow with regard to function names and parameter names. Anyway, when you have a scalar function and a table of values that you want to pass as arguments to the function one row at a time, you can go like this:

SELECT
  dbo.function_scalar(Amount, CurrencyId, 'EU')
FROM
  @GetTotalPaymentAmount
;

That will give you a column of values each of which is the result for a corresponding row of the table. At this point you can specify Amount and CurrencyId to return them along with the results to verify the results match the input values.

Now, as I have understood from your description, the results will be amounts in the same currency. You can get the total amount by aggregating the column returned by the function, for which you simply need to apply SUM() to the function result:

SELECT
  SUM(dbo.function_scalar(Amount, CurrencyId, 'EU'))
FROM
  @GetTotalPaymentAmount
;

That is basically it. If you need to return the result as a single-row single-column dataset or otherwise use it as a derived table in another query, you will probably want to assign an alias to the aggregated column by changing the SUM line to this:

  TotalAmount = SUM(...)

or this:

  SUM(...) AS TotalAmount

You can also just store the result in a variable if necessary:

  @TotalAmount = SUM(...)


Postgres window function to calculate summary position of orders I tried windows functions, CTE, RECURSIVE but I still can't get my head around it

If I have rows of stock orders buy/sell and I want to know what was position after each trade.

If each next order is increasing existing position - I want to have average price,

If the order is decreasing - I want to calculate ProfitLoss and leave price of position as it was

If the order is changing direction of the position  - I want to calculate ProfitLoss and new price of position

order_profit_loss = LEAST(ABS(qua), lag(ABS(position_qua))) * price
position_price = (LAG(position_price) * LAG(position_qua) - order_profit_loss) / position_qua

DDL

CREATE TABLE trades
(
    id VARCHAR(255) PRIMARY KEY NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    price NUMERIC NOT NULL,
    qua INTEGER NOT NULL,
    dt TIMESTAMP WITH TIME ZONE
);

INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('1', 'FB', 124.89, 116, '2016-07-29 23:01:46.334000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('2', 'FB', 123.56, -104, '2016-07-30 23:41:13.297000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('3', 'FB', 125.23, 20, '2016-07-31 00:20:41.092000');
INSERT INTO public.trades (id, symbol, price, qua, dt) VALUES ('4', 'FB', 123.56, -21, '2016-08-01 12:25:34.482000');

How to get such outcome

id | symbol | price  | qua  |             dt             | order_profit_loss | position_qua | position_price
---+--------+--------+------+----------------------------+-------------------+--------------+----------------
1  | FB     | 124.89 |  116 | 2016-07-29 23:01:46.334+03 |                 0 |          116 |         124.89
2  | FB     | 123.56 | -104 | 2016-07-30 23:41:13.297+03 |           -138.32 |           12 |         124.89
3  | FB     | 125.23 |   20 | 2016-07-31 00:20:41.092+03 |                 0 |           32 |       125.1025
4  | FB     | 123.56 |  -21 | 2016-08-01 12:25:34.482+03 |          -32.3925 |           11 |       125.1025

Solution:

CREATE OR REPLACE VIEW v_pos AS
WITH RECURSIVE pos AS
  ( SELECT * FROM (
          SELECT DISTINCT ON (symbol)
               t.*,  0::numeric  AS pnl,
                     qua         AS pos,
                     price       AS p_price
           FROM public.trades AS t
           ORDER BY symbol, dt
         ) AS starting
    UNION ALL
    SELECT
        n.*,
        c.pnl,
        p.pos + n.qua,
        CASE
          WHEN p.pos > 0 AND n.qua > 0 THEN (p.p_price * p.pos + n.price * n.qua) / (p.pos + n.qua)
          WHEN p.pos < 0 AND n.qua < 0 THEN (p.p_price * p.pos + n.price * n.qua) / (p.pos + n.qua)
          WHEN p.pos / n.qua < 0 AND p.pos > n.qua THEN p.p_price
          WHEN p.pos / n.qua < 0 AND p.pos < n.qua THEN n.price
          ELSE 0
        END
    FROM
        pos AS p,
        LATERAL
        ( SELECT t.* FROM trades AS t
          WHERE t.symbol = p.symbol AND t.dt > p.dt ORDER BY t.dt  LIMIT 1
        ) AS n,
        LATERAL (
          SELECT
            CASE
              WHEN p.pos < 0 AND n.qua > 0 AND -p.pos > n.qua THEN n.qua * (p.p_price - n.price)
              WHEN p.pos < 0 AND n.qua > 0 AND -p.pos < n.qua THEN p.pos * (p.p_price - n.price)
              WHEN p.pos > 0 AND n.qua < 0 AND p.pos > -n.qua THEN n.qua * (p.p_price - n.price)
              WHEN p.pos > 0 AND n.qua < 0 AND p.pos < -n.qua THEN p.pos * (p.p_price - n.price)
              ELSE 0
            END pnl,
            p.pos * p.price cb
        ) AS c
  )
SELECT symbol, qua::BIGINT, price FROM (
  SELECT
    DISTINCT ON (symbol)
    symbol,
    pos qua,
    p_price price
  FROM pos
    WHERE qua != 0
  ORDER BY symbol, dt DESC
) as tmp
WHERE tmp.qua != 0

Not sure if I have got all the logic right but you'll at least have a starting point:

WITH RECURSIVE pos AS
  ( SELECT *
    FROM ( SELECT DISTINCT ON (symbol)
               t.*,  0::numeric  AS order_profit_loss,
                     qua         AS position_qua,
                     price       AS position_price
           FROM public.trades AS t
           ORDER BY symbol, dt
         ) AS starting
    UNION ALL
    SELECT
        n.id, n.symbol, n.price, n.qua, n.dt,
        CASE WHEN n.qua < 0
            THEN n.qua * (p.position_price - n.price)
            ELSE 0
        END                     AS order_profit_loss,
        p.position_qua + n.qua  AS position_qua,
        CASE WHEN n.qua < 0
            THEN p.position_price
            ELSE (p.position_qua * p.position_price + n.price * n.qua)
                 / (p.position_qua + n.qua)
        END                     AS position_price
    FROM
        pos AS p,
      LATERAL
        ( SELECT t.*
          FROM public.trades AS t
          WHERE t.symbol = p.symbol
            AND t.dt > p.dt
          ORDER BY t.dt    LIMIT 1
        ) AS n
  )
SELECT *
FROM pos
ORDER BY symbol, dt ;


How to get data everyday's eight AM.? MySQL database, I have a table which contains some column. First column is datatime type, like "2016-03-01 08:00:00", "2016-03-02 08:00:00".

What should i do to get everyday's 8 AM. from this table?

WHERE TIME(dt) = '08:00:00' is another way to filter on exactly 8am.


SQL Server transaction log - shrinking and reattaching I have the next scenario in SQL Server 2008: one big database (production DB). I want to backup this db then restore it on another server.

Then I do some delete operation from some big tables. After delete I want to shrink the database.

Then detach shrinked db, copy it on first (production) server, detach the big database and then attach the small one.

Now comes my problem: on the big (production database) the activity continued, and new records was inserted, updates done, deletes etc.

Will I be able to restore those operations from the transaction log on the new created small database? Is there a checkpoint before the first backup action? Will there be conflicts between the small DB transaction log and the big one's?

Any help (ideas, articles) would be greatly appreciated.

Thank you.

No. After you restored the database in your development environment it had become a different database. Re-attaching it back to production will replace the production database and all transaction that occurred after your initial detach will be lost. Doing what you describe usually require very complex solutions, involving setting up replication from production to staging and contiguously replicating transactions in production to the staging server. Needless to say, developing/testing such a complex solution is only worth it for a ... worthy goal. Shrinking your database is not such a worthy goal.

Handling situation of runaway data that was never designed for delete (which is 99% of all projects, since the need for efficient delete of data is never evident during development, when the tables are empty) is quite hard actually. Partitioning is the best solution, by far, but is a huge undertaking with serious impact on the application. Other than that, deleting old data in small batches is the next best thing, provided an appropriate supporting index is provisioned.


Non clustered index on partitioned table Using SQL Server 2012. I have a huge table that is partitioned. I need to create a non clustered index on it.

After I created the index, a job that somewhere switches partition fails because the index I create. (The job works fine once I remove the index)

My question is what are the things I need to take in account while creating the index on partitioned table.

Your job most likely blew up because you added the non-clustered index to the base table, but did not add it to the staging table required for switching partitions in and out.
There are a significant number of things to consider when using partition switching - please read Transferring Data Efficiently by Using Partition Switching.  From that post.

Nonclustered indexes must be defined and must be identical. Any
nonclustered indexes that are defined on the target table are also
defined on the source table and are structured identically in terms of
uniqueness, subkeys, and the sorting direction (ASC or DESC) for each
index key column. Nonclustered indexes that are disabled are exempt
from this requirement.


ERROR: subquery must return only one column I have two tables one is Category and other one is Product.

Table Description are

Category

name
description
parent_id (self referencing key) (can be upto 3 levels)

Product

name
description
type
category_id (foreign key to Category table)

I want to show all the categories on a single along with the associated products. So for getting associated categories I use the below query. I am able to do the left join at the self referential tables but I am not able to get list of product data, as this is a subquery and subquery would only return a single column.

    select
               cat1.id, ARRAY(select name, type, description from product where
               product.category_id = cat1.id)
               as category_1_products_data,

               cat2.id, ARRAY(select name, type, description from product where
               product.category_id = cat2.id)
               as category_2_products_data,

               cat3.id, ARRAY(select name, type, description from product where
               product.category_id = cat3.id)
               as category_3_products_data
    from       category cat1
    left join  category cat2
    on         cat2.parent_id = cat1.id
    left join  category cat3
    on         cat3.parent_id = cat2.id
    where      cat1.parent_id is null;

  ERROR:  subquery must return only one column
   LINE 2:                        cat1.id, (select name, type, description from ...

All elements of an array must have the same type; when constructing an array with a subquery, the simplest way to enforce this is to demand that the query returns exactly one column.

But you can make the subquery return a single column whose type is a composite type by using a row constructor:

ARRAY(SELECT ROW(name, type, description) FROM ...)


How to build a database that stores huge data while no need of frequent query? We want to separate our data into fresh data and history data. History data is rarely used so we decide to move them to a specific database that stores huge data while no need of frequent query nor very good speed.

What kind of database is most suitable for this case?

I might consider below option if it is in Mysql.

Table will be partitioned by RANGE weekly.
On Master keep latest week's partition. And drop earlier partitions in below way.

  a). set sql_log_bin=0; #Set local session to not log into binlog.

  b). alter table drop partition OLD_PARTITION_NAME;

  c). exit;

On Slave keep old data.
Set this kind of automated/manual job every week to drop old partition on Master.
You might have to once verify the selects going on Master.


SQL - Set a Flag Based on a Conditions Based on Multiple IDs Working in SQL Server 2012 and trying to get the output below. I would like the Flag column (doesn't currently exist) to be 1 if the rows with the same primary ID have both Test A and B, and 0 otherwise. I manually entered the desired Flag values as an example. Does anyone know how to do this with SQL?

Any help would be greatly appreciated. Thanks!

Something like this may be

select t.*,
  case sum(case when test in ('a','b') then 1 else 0 end) -- count occurrences of tests 'a' and 'b'
       over (partition by primary_id) -- for each primary_id
  when 2 then 1 else 0 end flag  -- if there are two, we're OK
from test t

fiddle


Problem Installing 11.2.0 on RHEL 7 64 Bit I have problem in stalling Oracle ASM on RHEL 7, Please advice

 [root@localhost grid]# ./root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node lrma086 successfully pinned.
Adding Clusterware entries to inittab
ohasd failed to start
Failed to start the Clusterware. Last 20 lines of the alert log follow:
2015-10-19 10:19:43.306
[client(28082)]CRS-2101:The OLR was formatted using version 3.
2015-10-19 10:19:46.259
[client(28109)]CRS-1001:The OCR was formatted using version 3.
[client(28227)]CRS-10001:CRS-6021: No msg for has:crs-6021 [l][unlimited]
[client(28228)]CRS-10001:CRS-6021: No msg for has:crs-6021 [n][65536]

ohasd failed to start at /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl line 358, <ALERTLOG> line 6.
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I/u01/app/grid/product/11.2.0/grid/perl/lib -I/u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl execution failed

Cause

The security limits requirements were not set according the recommended values

The message
[client(15674)]CRS-10001:CRS-6021: No msg for has:crs-6021 [n][65536]
makes think about the value recommended for oracle hard nofile 65536 in /etc/security/limits.conf

Then these messages makes think that probably the other parameters in /etc/security/limits.conf were not set
[client(15669)]CRS-10001:CRS-6021: No msg for has:crs-6021 [l][unlimited]
[client(15672)]CRS-10001:CRS-6021: No msg for has:crs-6021 [c][unlimited]

Solution

Using root user make sure that /etc/security/limits.conf are set as follows
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240


Do unused columns in MySQL/MariaDB notably affect performance? In the WordPress MySQL/MariaDB database there is a table with some columns that in my own uses of WordPress are never used.
The exact columns I am talking about are:
comment_status varchar(20)
ping_status varchar(20)
to_ping text
pinged text
comment_count bigint(20)

None of them is part of an index.
I wonder if removing those columns would make a notable performance difference.
Of course I cannot just remove those columns without modifying the WordPress core. My real goal behind the question is to gain feedback to know if it's worth the effort to participate in the WordPress development and find an alternative way to manage that information for websites still using those columns.

Unused columns do carry a cost, but rarely one that most people will notice or care about in systems with well-written, targeted queries. If you are curious about testing performance and contributing in a meaningful way to the WordPress project, you might want to consider doing something like this:

Make a workable backup of your WordPress database. For example:
sudo mysqldump wordpress > wordpress.sql

Note: This assumes you would create backups via a command-line. If you prefer GUIs, make a backup in your preferred way.

Drop the columns that you think you can live without:
ALTER TABLE wp_posts DROP COLUMN comment_status;
ALTER TABLE wp_posts DROP COLUMN ping_status;
ALTER TABLE wp_posts DROP COLUMN to_ping;
ALTER TABLE wp_posts DROP COLUMN pinged;
ALTER TABLE wp_posts DROP COLUMN comment_count;

See what breaks over the next few days. If WordPress is unhappy about something, it is not shy about telling the world that something is wrong.
Then, if nothing bad happens...

Begin modifying the WordPress API to handle those fields differently (if at all) and tweak the database to be more performant.

Working on a large project like WordPress can bring a great deal of satisfaction, particularly if any of your modifications are adopted by the core project.


SQL application connection using one Windows user We are currently looking to update an old application security structure and would like to go for an approach where the application connects to SQL via one Windows login with the required Read/Write access.

We don't want to give access to the DB to all users otherwise they might connect via another application (SSMS or Excel) and run commands on SQL. I know we can use Application Roles to achieve this however, the application connects to many databases and we wouldn't like to enable the Guest user.

Do you have any other suggested approaches or in that case we would need to use an SQL encrypted login instead of a Windows login?

If you're using IIS manager for application pools to configure identity for your application and your servers are Windows 2012 or later, you can look into using either a Managed Service Account or a Group Managed Service Account to run your app.  Either approach will eliminate the need to deal with passwords yet maintain a secure application login.

The comment provided by sepupic above is spot on though in that you should manage user permissions directly against the database separately, and how that's done will heavily depend upon what level of permissions are needed.

Hopefully this provides an approach to your situation though.


SQL Server High Availability without Windows Clustering As per my understanding and information, there isn't any SQL Server High Availability(HA) feature without Windows Server Failover Clustering mechanism (WSFC) being used. SQL Server Mirroring used to be one such option where HA was possible without WSFC but with Microsoft stating that Mirroring would be removed soon from the product, are there any HA features which do not require WSFC?

One of the updates in vNext was that Microsoft would be releasing an option for Cluster Free Availability groups. But as of now, do we have any Cluster Free HA feature within the product(SQL Server 2012, 2014, 2016)?. LogShipping is more of a DR option, so excluding LogShipping and Mirroring any other feature?

As per my understanding and information, there isn't any SQL Server High Availability(HA) feature without Windows Server Failover Clustering mechanism (WSFC) being used.

Correct.

  SQL Server Mirroring used to be one such option where HA was possible without WSFC but with Microsoft stating that Mirroring would be removed soon from the product

Also correct.

  One of the updates in vNext was that Microsoft would be releasing an option for Cluster Free Availability groups.

Correct, but this does NOT provide HA, so you will still need to use cluster for HA even in vNext.

  But as of now, do we have any Cluster Free HA feature within the product(SQL Server 2012, 2014, 2016)?

Mirroring. If you can setup mirroring you can setup clustering and use AGs - I'm not sure why the big push to not have to use Windows Clustering.


Unique across tables I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users).

However, each user can also have additional emails.

In any case, an email address must be unique and I'd like to enforce this on the database level. For the primary email address that's easy. I can simply add a UNIQUE index with a WHERE not is_deleted restriction so a deleted user's email can be re-used.

However, for the secondary emails that's more tricky.

If I stored them in the same table in an array field I'd probably not only lose the ability to index them (we have >50k users and need to be able to search by email, so that's a no-go) but as far as I know I also cannot put an UNIQUE index/constraint which goes into the arrays.
If I use a separate table (which is obviously cleaner), I would have to replicate the is_deleted flag from users to that table, which is also quite ugly, but would allow me to simply use the UNIQUE index with WHERE not is_deleted.

Is there any better solution to achieve what I'm trying to do?

To enforce unique email addresses, I would remove all competing email columns and store them in one central email table for all active emails. And another table for deleted emails:

CREATE TABLE users (
  user_id  serial PRIMARY KEY
, username text UNIQUE NOT NULL
, email    text UNIQUE -- FK added below  -- can also be NOT NULL
);

CREATE TABLE email (
  email    text PRIMARY KEY
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE
, UNIQUE (user_id, email)  -- seems redundant, but required for FK
);

ALTER TABLE users ADD CONSTRAINT users_primary_email_fkey
FOREIGN KEY (user_id, email) REFERENCES email (user_id, email);

CREATE TABLE email_deleted (
  email_id serial PRIMARY KEY
, email    text NOT NULL  -- not necessarily unique
, user_id  int NOT NULL REFERENCES users ON DELETE CASCADE
);

This way:

Active emails are unique, enforced by the PK constraint of email.
Each user can have any number of active and deleted emails, but ...
Each user can only have one primary email.
Every email is always owned by one user and is deleted with the user.
To soft-delete an email (without losing it and its affiliation to its user, move the row from email to email_deleted.

The primary email of a user cannot be deleted this way, because the primary email must not be deleted.

I designed the FK constraint users_primary_email_fkey to span (user_id, email), which seems redundant at first. But this way the primary email can only be an email that is actually owned by the same user.
Due to the default MATCH SIMPLE behavior of FK constraints, you can still enter a user without primary email, because the FK constraint is not enforced if any of the columns is null.
Details:

Two-column foreign key constraint only when third column is NOT NULL

The UNIQUE constraint on users.email is redundant for this solution, but it may be useful for other reasons. The automatically created index should come in handy (for instance for the last query in this answer).

The only thing that's not enforced this way is that every user has a primary email. You can do this, too. Add NOT NULL constraint to users.email

UNIQUE (user_id, email) is required for the FK constraint:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?

You have doubtless spotted the circular reference in the above model. Contrary to what one might expect, this just works.

As long as users.email can be NULL, it's trivial:

INSERT user without email.
INSERT email referencing the owning user_id.
UPDATE user to set it's primary email if applicable.

It even works with users.email set to NOT NULL. You have to insert user and email at the same time though:

WITH u AS (
   INSERT INTO users(username, email)
   VALUES ('user_foo', 'foo@mail.com')
   RETURNING email, user_id
   )
INSERT INTO email (email, user_id)
SELECT email, user_id
FROM   u;

IMMEDIATE FK constraints (the default) are checked at the end of each statement. The above is one statement. That's why it works where two separate statements would fail. Detailed explanation:

How to deal with mutually recursive inserts

To get all emails of a user as array, with the primary email first:

SELECT u.*, e.emails
FROM   users u
     , LATERAL (
      SELECT ARRAY (
      SELECT email
      FROM   email
      WHERE  user_id = u.user_id
      ORDER  BY (email <> u.email)  -- sort primary email first
      ) AS emails
   ) e
WHERE  user_id = 1;

You could create a VIEW with this for ease of use.
LATERAL requires Postgres 9.3. use a correlated subquery in pg 9.2:

SELECT *, ARRAY (
             SELECT email
             FROM   email
             WHERE  user_id = u.user_id
             ORDER  BY (email <> u.email)  -- sort primary email first
             ) AS emails
FROM   users u
WHERE  user_id = 1;

To soft-delete an email:

WITH del AS (
   DELETE FROM email
   WHERE  email = 'spam@mail.com'
   RETURNING email, user_id
   )
INSERT INTO email_deleted (email, user_id)
SELECT email, user_id FROM del;

To soft-delete the primary email of a given user:

WITH upd AS (
   UPDATE users u
   SET    email = NULL
   FROM   (SELECT user_id, email FROM users WHERE user_id = 123 FOR UPDATE) old
   WHERE  old.user_id = u.user_id
   AND    u.user_id = 1
   RETURNING old.*
   )
,    del AS (
   DELETE FROM email
   USING  upd
   WHERE  email.email = upd.email
   )
INSERT INTO email_deleted (email, user_id)
SELECT email, user_id FROM upd;

Details:

Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

Quick test for all of the above: SQL Fiddle.


ALTER TABLE SWITCH statement failed ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables...

Does anyone knows how to fix that on already existing table full of data?

Well, I've found that it is simply possible to recreate same table with same ANSI_PADDING parameter, but the problem is that the table I'm trying to switch into a larger main table is already filled with data. I don't want to spend again 37 hours to reload that partition =(

I hate to say this, but there's a bug in Books Online. The Books Online page on partition switching says:

  Source and target tables must have the same column structure and
  order. The tables must have the same columns with the same names and
  the same data type, length, collation, precision, scale, nullability,
  and PRIMARY KEY constraints (if any), and also have the same settings
  for ANSI_NULLS and QUOTED IDENTIFIER.

But as you've discovered ANSI_PADDING is also involved - both the incoming & outgoing tables have to have the same setting. There is no way to fix this after the data is loaded.


Merge replication Identity columns for already existing databases I'm attempting to setup merge replication with Pull subscriptions (SQL Server 2014 for Subscribers and 2016 for the Publisher). I have a number of databases at remote sites which operate independently, and have been operating independently for years, and we have never had a central server (Publisher) where all remote site data rolls up.

The database schema is essentially the same at all sites (it's the same application after all). What this also means is that, since all DBs have been operating independently, there is definitely going to be duplication of values for Identity columns between Subscriptions (though the actual row data itself will be site specific). Say, Subscriber A Table X has ID column 25 with row data (a,b,c,d) and Subscriber B Table X has ID column 25 with row data (e,f,g,h).

I've been reading up on automatic identity range management, etc., which is all fine if I'm setting up brand new subscriptions. However, here I am inheriting a bunch of Subscriber databases and I have to setup replication so that the data is aggregated at a central location. Which means I am inheriting duplicate identity column values across subscribers. Auto range management is fine for new rows, but I'm not sure what is the best way to manage the identity conflicts that come with the existing databases.

Any help will be greatly appreciated!

Regards,
Vikram

Vikram,

If your remote sites are named unique, then you can utilize HOST_NAME() as a FILTER  while creating a Merge Publication, we are doing the same at our place.

We have 2000 stores across USA, which we replicate (via Merge Replication) to our home office. All these stores have same schema across the board, so table ID is going to be duplicated across the board. To overcome the duplication issue, we utilized followings columns in our Replicated Articles:

Table ID
RowGUID (It will be unique per server at your remote location)
SourceStore (this is filter for HOST_NAME(), which will make record unique at the home office (i.e. Central Location)

Example:

-Subscriber A Table X has ID column 25 with row data (a,b,c,d,RowGUID,Host_Name())
 -Subscriber B Table X has ID column 25 with row data (e,f,g,h,RowGUID,Host_Name())

Hope this helps with your issue.

Thank you,
HP


What is the impact of LC_CTYPE on a PostgreSQL database? So, I've few Debian servers with PostgreSQL on it. Historically, those servers and PostgreSQL are localized with the Latin 9 charset and back then it was fine. Now we have to handle things like Polish, Greek or Chinese, so changing it become a growing issue.

When I tried to create an UTF8 database, I got the message:

  ERROR: encoding UTF8 does not match locale fr_FR Detail: The chosen LC_CTYPE setting requires encoding LATIN9.

Few times I made some research on the subject with my old pal Google, and all I could find was some over-complicated procedures like updating the Debian LANG, recompile PostgreSQL with the correct charset, editing all the LC_ system variables and other obscure solutions. So for the time being, we let this issue aside.

Recently, it came back again, the Greeks want the stuff and Latin 9 don't want to. And while I was looking into this issue again, one colleague come at me and said “Nah, it's easy, look.”

He edited nothing, didn't do magic tricks, he just make this SQL query :

CREATE DATABASE my_utf8_db
  WITH ENCODING='UTF8'
       OWNER=admin
       TEMPLATE=template0
       LC_COLLATE='C'
       LC_CTYPE='C'
       CONNECTION LIMIT=-1
       TABLESPACE=pg_default;

And it worked fine.

I actually didn't know about LC_CTYPE='C' and I was surprised that using this wasn't on the first solutions on Google and even on Stack Overflow. I looked around and I only found a mention on the PostgreSQL documentation.

  When LC_CTYPE is C or POSIX, any character set is allowed, but for other settings of LC_CTYPE there is only one character set that will work correctly. Since the LC_CTYPE setting is frozen by initdb, the apparent flexibility to use different encodings in different databases of a cluster is more theoretical than real, except when you select C or POSIX locale (thus disabling any real locale awareness).

So it made me wonder, this is too easy, too perfect, what are the downside? And I've a hard time finding an answer yet. So here I come posting here:

tl;dr: What are the downside of using LC_CTYPE='C' over a specific localization? Is it bad to do so? What should I expect to break?

What are the downside of using LC_CTYPE='C' over a specific
localization

The documentation mentions the relationship between locales and SQL features in Locale Support:

The locale settings influence the following SQL features:

Sort order in queries using ORDER BY or the standard comparison operators on textual data

The upper, lower, and initcap functions

Pattern matching operators (LIKE, SIMILAR TO, and POSIX-style regular expressions); locales affect both case insensitive matching and the classification of characters by character-class regular expressions

The to_char family of functions

The ability to use indexes with LIKE clauses

The first item (sort order) is about LC_COLLATE and the others seem all to be about LC_CTYPE.
LC_COLLATE
LC_COLLATE affects comparisons between strings. In practice, the most visible effect is the sort order. LC_COLLATE='C' (or POSIX which is a synonym) means that it's the byte order that drives comparisons, whereas a locale in the language_REGION form means that cultural rules will drive the comparisons.
An example with french names, executed from inside an UTF-8 database:
select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
 AS l(firstname)
order by firstname collate "fr_FR";

Result:

 firstname
-----------
 béatrice
 bérénice
 bernard
 boris

béatrice comes before boris, because the accented E compares against O as if it was non-accented. It's a cultural rule.
This differs from what happens with a C locale:
select firstname from (values ('bernard'), ('bérénice'), ('béatrice'), ('boris'))
 AS l(firstname)
order by firstname collate "C";

Result:

 firstname
-----------
 bernard
 boris
 béatrice
 bérénice

Now the names with accented E are pushed at the end of the list.
The byte representation of é in UTF-8 is the hexadecimal C3 A9 and for o it's 6f. c3 is greater than 6f so under the C locale, 'béatrice' > 'boris'.
It's not just accents. There a more complex rules with hyphenation, punctuation, and weird characters like œ. Weird cultural rules are to be expected in every locale.
Now if the strings to compare happen to mix different languages, as when having a firstname column for people from all other the world, it might be that any particular locale should not dominate, anyway, because different alphabets for different languages have not been designed to be sorted against each other.
In this case C is a rational choice, and it has the advantage of being faster, because nothing can beat pure byte comparisons.
LC_CTYPE
Having LC_CTYPE set to 'C' implies that C functions like isupper(c) or tolower(c) give expected results only for characters in the US-ASCII range
(that is, up to codepoint 0x7F in Unicode).
Because SQL functions like upper(), lower() or initcap
are implemented in Postgres on top of these libc functions, they're
affected by this as soon as there are non US-ASCII characters in strings.
Example:
test=> show lc_ctype;
  lc_ctype
-------------
 fr_FR.UTF-8
(1 row)

-- Good result
test=> select initcap('élysée');
 initcap
---------
 Élysée
(1 row)

-- Wrong result
-- collate "C" is the same as if the db has been created with lc_ctype='C'
test=> select initcap('élysée' collate "C");
 initcap
---------
 éLyséE
(1 row)

For the C locale, é is treated as an uncategorizable character.
Similarly wrong results are also obtained with regular expressions:
test=> select 'élysée' ~ '^\w+$';
 ?column?
----------
 t
(1 row)

test=> select 'élysée' COLLATE "C" ~ '^\w+$';
 ?column?
----------
 f
(1 row)


SQL Server Log Shipping: Copy copies 0 files (This is SQL 2014, but applies to several recent versions)

I had the SQL Server log shipping wizard create the script for the primary and secondary. Since the primary already backs up logs every few minutes, we did not run the script on the primary.

We ran it on 2ndary, and set up all the proxy accounts, etc, so that files can be copied.

The jobs run. Issue is that the copy job... never copies any files... even though there are files there.

We confirmed that when we run a cmd prompt as the same user (the proxy user account) that we can see the needed files on the remote location (to copy to the local location).

Message
2015-02-11 01:50:08.00  Checking to see if any previously copied log backup files that are required by the restore operation are missing. Secondary ID: '72ac3286-f3cb-4f80-a8a4-368e3b38e502'
2015-02-11 01:50:08.00  The copy operation was successful. Secondary ID: '72ac3286-f3cb-4f80-a8a4-368e3b38e502', Number of log backup files copied: 0
2015-02-11 01:50:08.01  ----- END OF TRANSACTION LOG COPY     -----

How do we kick the secondary so it starts copying files?

The cause is not clear, but the solution is: get full connectivity between primary and 2ndary and use the sql server wiz to set up log shipping

that works.

Update:
I should add that this solution stinks. The log shipping wizard should be more flexible, and allow me to set up the pieces myself. (E.g. I may use some other method to move the log files, etc.) Given how easy it is to set up an RO mirror of Redis, Mongodb, etc, SQL should be much better than it is now.


does DBCC ShrinkDatabase do a Reorganize of files? does DBCC ShrinkDatabase doa  Reorganize of files?

Basically does it mimic the same process as if i checked Reorganize in the GUI?

No, actually it is notoriously bad for fragmenting the data. You should do a re-org after the shrink is complete.

FROM MSDN:

  A shrink operation does not preserve
  the fragmentation state of indexes in
  the database, and generally increases
  fragmentation to a degree. This is
  another reason not to repeatedly
  shrink the database.


MySQL 5.7 + Ubuntu 14.04 eating up my RAM I've recently installed MySQL 5.7.11 in my Ubuntu 14.04.3 system following these instructions: http://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

Being totally empty and not being called by any other program, it always ramps up RAM usage in a couple of hours until I can't start Node apps because of low memory (ENOMEM). I first assumed it to be because I'm running on a 768MB VPS, so I tweaked my.cnf, using some very low parameters, but it kept behaving like that, eating up more than 600MB of RAM after a couple of hours.

Please notice that it's really taking up all that RAM, it's not some buffer/cache voodoo, because it stops other programs from starting. Or am I wrong here?

Just uninstalled everything and reinstalled MySQL 5.6 and this problem isn't happening. Can anyone lend me a hand here? Am I doing something wrong or is this some kind of bug?

Thanks!

MySQL 5.7.13 is OK

I was suffering from this problem. Same as you described: MySQL 5.7.11 with almost empty database and few requests in hour was eating memory up to 450MB.

So I've recently tried new version and it is totally OK. MySQL 5.7.13 instance is running for a week already and it takes only 73MB.

Also I've turned off performance schema in my.cnf:

            [mysqld]
            performance_schema=0


how to get recordStats in mongodb 3.2? I want to monitor accessNotInMemory of mongodb, before I can use

db.adminCommand({'serverStatus': 1})['recordStats']

but according to mongodb doc,

  Changed in version 3.0: serverStatus no longer outputs the workingSet, indexCounters, and recordStats sections.

how can I get the recordStats in mongodb 3.2?

I want to monitor accessNotInMemory of mongodb

The database-level recordStats information was specific to the older MMAP storage engine and removed during the MongoDB 3.0 development cycle as part of the work toward collection-level locking and the new storage engine API.

A more general performance indicator you can monitor is db.serverStatus().extra_info.page_faults. This metric is available in MongoDB 3.2 as well as prior versions (2.2+, at least).

Given there are multiple storage engines available in MongoDB 3.0, there are also more detailed per-engine metrics that might be of interest. For example, WiredTiger is the default storage engine as at MongoDB 3.2, and includes a comprehensive set of metrics in the wiredTiger section of serverStatus output.


Does ORDER BY on a clustered key affect performance? Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey, so I additionally always ORDER BY PrimaryKey in all queries.
Does this ORDER BY affect performance in any way or is it ignored by the profiler as the rows are already in this order?
I am using a SQL Server 2005 database.

It almost certainly will affect performance.

If you just do a query like

Select *
From Table
Order by PrimaryKey

It likely won't affect anything at all.

Bear in mind, though, that this only determines the order of the rows at the leaf level of the clustered index.  If you do JOINs, or use other indexes that avoid key lookups, then the ORDER BY will generate additional work.

If every index and every table that you will ever JOIN or reference when querying this table are all ordered by the same key (in the same direction) then it probably won't impact performance.  Outside that very limited scenario, you will see a hit.

Only use ORDER BY when the order of the results actually matters.  In my experience it's seldom necessary.


Find the user with closest taste of mine I trying to write in SQL the following requirement:

Each user can rate as much films as they like. (1= I don't like, 5=I love it)
And the system should provide to the current user a list of films that he doesn't rate yet and that other users like too.

To simplify, I have this table and the following data:

create table wich (
  uid int,
  film varchar(50),
  rate int
);

insert into wich values
(1, 'usual suspect', 5), (1, 'gataca', 4), (1, 'goldeneye', 2),
(2, 'usual suspect', 4), (2, 'gataca', 5), (2, 'i am a legend', 4), (2, 'the hobbit', 1),
(3, 'usual suspect', 1), (3, 'gataca', 5), (3, 'goldeneye', 5),
(4, 'usual suspect', 5), (4, 'goldeneye', 5),
(5, 'usual suspect', 5), (5, 'gataca', 4), (5, 'goldeneye', 5),
(6, 'usual suspect', 4), (6, 'gataca', 3), (6, 'goldeneye', 3), (6, 'shrek', 4);

This can be read as: User 1 like 'usual suspect' and 'gataca' but not 'goldeneye'.

So what I want know is to find the user(s) that have the closest tastes of the current user and get a list of films that the current user could probably like.

Following, what I have done so far:

Step 1: for every films rated by user 1, if user1 and user2 rate the film in the same way, compute a score (should be 0 if the difference is greater than 2)
and it's great if both like a film or both doesn't like a film.

select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
        from wich w1
        inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
        where w1.uid=1

Step 2: compute a score by user

select scores.uid, SUM(scores.score) * 100 / sum(scores.nb) as score
from (
    select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
    from wich w1
    inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
    where w1.uid=1
) scores
group by uid
ORDER BY score desc

step 3: find films that user1 has not yet rated that he could like

select w3.uid, w3.film,w3.rate, matches.score
from (
    select scores.uid, SUM(scores.score) * 100 / sum(scores.nb) as score
    from (
        select w2.uid, 1 as nb, case when abs(w1.rate - w2.rate) <= 2 THEN 5-abs(w1.rate - w2.rate) ELSE 0 END as score
        from wich w1
        inner join wich w2 on w1.uid<>w2.uid and w1.film=w2.film
        where w1.uid=1
    ) scores
    group by uid
    ORDER BY score desc
) as matches
inner join wich w3 on matches.uid=w3.uid and w3.rate >= 3
left join wich w4 on w4.uid=1 and w3.film=w4.film
where w4.uid is null
order by w3.rate desc, matches.score desc;

This seems to work, but I am not sure that this will still respond in a small amount of time if each user rate of lot of films and the table become become bigger.

What do you think ?

Is there a better way to accomplish such a thing ?

A working fiddle: http://sqlfiddle.com/#!2/89c57/1/0

Edit:

In my real table, the film column is an integer and I have a second table with films data (title, description, year, ...)

create table wich (
  uid int,
  film int,
  rate int
);

The example given here is just to simplify the problem with just one table.

You can make a table which holds the users with similar taste for every user.

CREATE TABLE `similar_users` (
    `uid1` INT,
    `uid2` INT,
    `score` INT,
    PRIMARY KEY (`uid1`, `uid2`)
)

The algorithm for calculating the score can be anything, as long as a higher score means a closer taste.

You you should limit the amount of similar users to a fixed number and also exclude the ones with a score less than a certain value (the ones with a not so similar tastes).

The contents of the table will be calculated periodically (e.g. every day).

Then step 3 from your solution becomes something like this:

SELECT w.film
FROM similar_users su
JOIN wich w ON w.uid = su.uid2 AND w.rate > 2
LEFT JOIN wich wn ON wn.film = w.film AND wn.uid = {current_user}
WHERE su.uid1 = {current_user}
ORDER BY su.score * w.rate DESC


MySQL - forbidden database names I was wondering whether there were a list of forbidden (reserved) database names in mysql.

For instance, mysql is obviously one. But is there a determinated list? I don't want to cover 99% in my program (I can figure out a simple list by myself...), but I rather would like to be up to date and reliable at 100%.

Any official source coming up to your mind?

Run show schemas - the ones installed by default
are:

mysql> show schemas;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema         |
| mysql                      |
| performance_schema         |
| test                       |
+----------------------------+

Just don't use any of these and you
should be OK - is there any particular reason
for your query?

Further comment from the OP:

  My reason is that I allow my users to pick a name for their
  environment, and it implicitely creates a database with the same name.
  This is why I have to ensure my exclusion list is reliable.

But surely, even in the unlikely event that your users try and create a schema that already exists, the attempt will be rejected anyway?


WordPress MySQL writes during "Flush Tables With Read Lock" are rejected instead of being queued I'm running into an odd issue when setting MySQL into a brief read-only mode for backup.

Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to run after tables are unlocked.

I'm not seeing the attempted writes in mysql "show processlist" where I would expect to see them, they just seem to be discarded.

Using "Flush Tables With Read Lock", copying the data files (less than a minute), then "unlock tables".

MySQL and backup code:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb

SET GLOBAL read_only = OFF;
unlock tables;

Background:

Running WordPress MultiSite on MySQL 5.1, CentOS. 70,000 MyISAM tables.

Looking for the fastest way to back up the DB while keeping the DB online.

Both reads AND writes need to be possible during the backup. But the backup time is very short, so it's fine for the writes to be queued until the "unlock tables" command.

In the WordPress error_log, seeing MySQL errors like this:

  [20-Nov-2014 01:41:56 UTC] Wordpress database error The MySQL server is running
  with the --read-only option so it cannot execute this statement for query
  INSERT INTO wp_options...

So my question is...

Why are writes being quietly discarded instead of being queued, as they would be for a regular table lock?

And more importantly, how can I fix that so they DO get queued and are eventually run in the order they were requested? :)

Thanks!

It is a brave soul indeed that still uses an all-MyISAM database.

  Why are writes being quietly discarded instead of being queued, as they would be for a regular table lock?

Look at the error message again

  [20-Nov-2014 01:41:56 UTC] Wordpress database error The MySQL server is running
  with the --read-only option so it cannot execute this statement for query
  INSERT INTO wp_options...

WordPress is reporting this as the error, but it not a failure on the part of MySQL. You turned on read_only. That's what WordPress sees.

  And more importantly, how can I fix that so they DO get queued and are eventually run in the order they were requested? :)

Just remove read_only:

FLUSH TABLES WITH READ LOCK;
system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb
unlock tables;

The DB Connections should start to queue up all INSERTs, UPDATEs, and DELETEs.

You said something far more profound in the question

  Both reads AND writes need to be possible during the backup. But the backup time is very short, so it's fine for the writes to be queued until the "unlock tables" command.

SUGGESTION #1

You need to convert all your tables to InnoDB. Reads and Writes can continue and queue up just fine without FLUSH TABLES WITH READ LOCK;. You would dump the database like this

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} thedb > thedb.sql

SUGGESTION #2

If you want everything to remain MyISAM, your should get another DB Server and setup MySQL Replication. All your writes would happen on the Master. On the Slave, you would do the backups like this

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb
START SLAVE;

You can also split your reads between Master and Slave, or do all reads on the Slave.

EPILOGUE

If you go with SUGGESTION #1, convert everything to InnoDB with this script

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYISAM_TO_INNODB_CONVERSION_SCRIPT=/tmp/ConvertMyISAMToInnoDB.sql
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${MYISAM_TO_INNODB_CONVERSION_SCRIPT}
mysql ${MYSQL_CONN} < ${MYISAM_TO_INNODB_CONVERSION_SCRIPT}

GIVE IT A TRY !!!

UPDATE 2014-11-21 10:52 EST

In your comment, you just asked

  Is there any major reason to NOT install a 2nd MySQL instance (the slave) on the same server?

There is a huge reason why you do not want to setup a 2nd MySQL instance on the same box to do replication.

Everything is MyISAM
MyISAM only caches indexes, never data
MyISAM defers caching data to the OS
Master and Slave caching the same data in two different DB means twice as much data for the OS to cache
See my other reasons in my old post Is it unwise to run replication on same physical server?

If you convert everything to InnoDB (which caches data and indexes, has failsafes for crash recovery and transactions), then, yes, you could setup a 2nd MySQL instance for replication on the same server (provided you have sufficient RAM and diskspace for the 2nd instance).


AlwaysOn AG between 2 sql server failover clusters I have 2 windows failover cluster and each of them consist of 2 nodes (virtual machines). And on each cluster has its own ms sql role which uses seperate shared discs :
1. wincluster1 (wsfcnode1 and wsfcnode2) - sqlclust1
2. wincluster2 (wsfcnode3 and wsfcnode4) - sqlclust2

For example on the sqlclust1 side I have Testdb database.

Question : Can I setup alwayson available group for this database between sql failover clusters?

If you are using SQL Server 2016, you can use Distributed Availability Groups, which is what you are describing. See more here.

If you are using SQL Server 2014 or SQL Server 2012, the answer is no. You'd have to put all 4 nodes into the same cluster and then setup an AG with 4 replicas. If two of the nodes are in a different data center, you'd want those two replicas to be asynchronous for performance reasons.


Most efficient way to filter on two columns in WHERE clause? I need to check if a list of people's names are in our MySQL database.

SELECT * FROM tbltrainerinfo WHERE lastname = "Arpay"       AND firstname = "Belly Joy"
SELECT * FROM tbltrainerinfo WHERE lastname = "Bebelyn"     AND firstname = "Abulog"
SELECT * FROM tbltrainerinfo WHERE lastname = "Cabrera"     AND firstname = "Virgie"
SELECT * FROM tbltrainerinfo WHERE lastname = "Christopher" AND firstname = "Sajonia"
SELECT * FROM tbltrainerinfo WHERE lastname = "Edilberto"   AND firstname = "Galaura"
SELECT * FROM tbltrainerinfo WHERE lastname = "Embanecido"  AND firstname = "Efren"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gertrulyn"   AND firstname = "Natonton"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gina"        AND firstname = "Gindap"
SELECT * FROM tbltrainerinfo WHERE lastname = "Gindap"      AND firstname = "Gina"
SELECT * FROM tbltrainerinfo WHERE lastname = "Jeffrey"     AND firstname = "Ladiero"
SELECT * FROM tbltrainerinfo WHERE lastname = "JIMENEZ"     AND firstname = "JUNPORT"
SELECT * FROM tbltrainerinfo WHERE lastname = "Jocelyn"     AND firstname = "Paloma"
SELECT * FROM tbltrainerinfo WHERE lastname = "Ladera"      AND firstname = "Chared"
SELECT * FROM tbltrainerinfo WHERE lastname = "Lago"        AND firstname = "Michelle Joy"
SELECT * FROM tbltrainerinfo WHERE lastname = "Maratima"    AND firstname = "Untal"
SELECT * FROM tbltrainerinfo WHERE lastname = "Marciano"    AND firstname = "Iloreta"
SELECT * FROM tbltrainerinfo WHERE lastname = "Marianita"   AND firstname = "Gemina"
SELECT * FROM tbltrainerinfo WHERE lastname = "Pala"        AND firstname = "Sittie Norjanna"
SELECT * FROM tbltrainerinfo WHERE lastname = "Untal"       AND firstname = "Maratima"

How can I do one SELECT statement and get people's names if they are in the list?

Here is the FROM (VALUES... version, with an EXISTS thrown in to get some speedy search shortcuts happening:

SELECT *
FROM tbltrainerinfo
WHERE EXISTS(
    SELECT *
    FROM (VALUES
        ('Arpay','Belly Joy')
        ,('Bebelyn','Abulog')
        ,('Cabrera','Virgie')
        ,('Christopher','Sajonia')
        ,('Edilberto','Galaura')
        ,('Embanecido','Efren')
        ,('Gertrulyn','Natonton')
        ,('Gina','Gindap')
        ,('Gindap','Gina')
        ,('Jeffrey','Ladiero')
        ,('JIMENEZ','JUNPORT')
        ,('Jocelyn','Paloma')
        ,('Ladera','Chared')
        ,('Lago','Michelle Joy')
        ,('Maratima','Untal')
        ,('Marciano','Iloreta')
        ,('Marianita','Gemina')
        ,('Pala','Sittie Norjanna')
        ,('Untal','Maratima')
        ) AS List(LName, FName)
    WHERE LName = lastName
      AND FName = firstname
    )


Is it possible to speed up this PARTITION BY query? I've got a timeserie table. Each datapoint has also a statut field which
indicates its providing source. I'd like to request last available datapoint
as of date T but there could be several results, each with a different statut.

Therefore, I'm using a "statut preference list" to retrieve datapoint in
accordance with providers priority.

Here's the query : (in this example, my preferred ordered statuts would be
10 then 9 then 1)

DECLARE @MyStatuts TABLE (StatutOrder tinyint, StatutValue tinyint)
INSERT INTO @MyStatuts VALUES(1, 10)
INSERT INTO @MyStatuts VALUES(2, 9)
INSERT INTO @MyStatuts VALUES(3, 1)

SELECT TOP 1 Id, Date, Statut, Value
FROM (
    SELECT data.Id, data.Date, data.Statut, data.Value, s.StatutOrder,
           MIN(s.StatutOrder)
             OVER(PARTITION BY data.Date) As MinStatutOrder
    FROM [DATA].[TimeSerie] as data
      JOIN @MyStatuts as s on data.Statut = s.StatutValue
    WHERE Id = 'timeserieid' AND Date <= '24/04/2014'
    ) AS T
WHERE T.StatutOrder = T.MinStatutOrder
ORDER BY T.Date DESC

Do you have any ideas how I can speed that up ? I already used Index on [TimeSerie] table...

Here's table code :

CREATE TABLE [DATA].[TimeSerie](
[Id] [varchar](25) NOT NULL,
[Date] [date] NOT NULL,
[Statut] [tinyint] NOT NULL,
[Value] [decimal](19, 8) NOT NULL,
CONSTRAINT [PK_Price] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[Date] ASC,
[Statut] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Link to the execution plan

I think your query uses only the rows where the date is the maximum so it is equivalent to this:

SELECT TOP (1) data.Id, data.Date, data.Statut, data.Value
FROM [DATA].[TimeSerie] AS data
  JOIN @MyStatuts AS s ON data.Statut = s.StatutValue
WHERE data.Id = 'timeserieid'
  AND data.Date = ( SELECT MAX(m.Date)
                    FROM [DATA].[TimeSerie] AS m
                    WHERE m.Id = 'timeserieid'
                      AND m.Date <= '20140424'
                  )
ORDER BY s.StatutOrder ASC ;


SQL Server 2005 replication for a data warehouse We are trying to implement some kind of Data Warehouse at my current employer.

Most of our data is in a SQL Server 2005, but we might also integrate some Access databases and some databases in a SQL Server 2008 Runtime version (it might matter because of the functionality it lacks). We would like the data to be replicated around once every hour. I wonder if there are people out that already have worked out a solution for this.

Our users would like to make queries to the warehouse from Excel or other apps using ODBC.

Also our amount of data is around 150 GB.

You have "data warehouse" in quotes, which could mean one of two things:  you're either looking for an actual data warehouse or you aren't (yay, truisms!).

If you're looking for an actual data warehouse, there's an entire Microsoft BI stack available to you as part of your already-purchased SQL Server licensing:  SQL Server Integration Services for ETL, SQL Server Analysis Services for creating cubes, and SQL Server Reporting Services and PowerPivot for analyzing data.  Doing a data warehouse right is not a trivial activity, even for somebody with a great deal of experience designing and developing transactional databases.  The big problem is not so much learning the tools and languages (though MDX is so different from T-SQL that it's in its own world), but getting the right mindset.  If you do go down this route, start with Ralph Kimball's Data Warehouse Toolkit.  It's a fairly long book and was not written for any particular technology stack, but instead tries to give you an understanding of what makes for a successful data warehousing project.  Doing it right could possibly take years even if you already know the tools, because the single most important thing about data warehousing is understanding the end user.  You really have to know what the end users (the business people) want, how they want it, how they can understand what they're seeing, and how what data and systems you have can get translated into what they want in a single, consistent manner.

If you're trying to do something "on the cheap," there's a much higher likelihood of failure, to be honest.  This is why I'm a little concerned about why you put "data warehouse" in quotes.  Yeah, there are easy-to-develop non-warehouse models you could come up with (such as possibly replicating all of the tables over to another server), but they're typically going to fall into one (or more) of a few categories:

Too complex for end users.  End users typically don't understand normalized data and shouldn't be expected to know SQL or the details of a GROUP BY clause.  Your best outcome in that scenario is that IT people continue writing the reports against this new system; the more likely outcome is that IT people continue writing the reports against the old systems and everybody forgets that the warehouse exists at all...
Inconsistent data.  As you go through this, you might find System 1 says that Order 37 includes an order of a dozen ink pens, but System 2 says it was 14 pens.  And System 3 had a free-form text field where instead of putting in a number, somebody typed in "dog."  If they are all supposed to represent the same thing, which is correct?  Finding (and working with the business on creating rules to solve) these data inconsistencies is a key part of building a good warehouse model.
Inconsistent grain.  If one system has order lines and a second only has orders, there are ways to reconcile this, but if you simply throw the tables out there and leave it to the end user, they may come up with the wrong method of reconciliation (i.e., one which gives incorrect results) without knowing it.

These are among the many problems you'll run into in the world of business intelligence.  Good data warehousing (be it Kimball-style, Inmon-style, some hybrid, or something else) doesn't avoid these problems, but instead gives you a mindset where you can understand and solve them.  Rolling a cheap-and-easy solution may let you paper over the problems in development, but we don't measure success simply through getting out of development; people have to use it for a warehouse of any type to have value.

Hourly updates isn't necessarily that big of a problem for a good warehouse, as long as you have the hardware necessary to do the job.  You can create SSIS packages to run on SQL Agent jobs (or through any other kind of scheduler, honestly) which perform the necessary tasks (pulling data from source systems, doing any transformations, doing any necessary staging, and building Analysis Services cubes), so scheduling isn't a problem if the individual processes are fast enough.