Restore/import tables from unsuccessful database dump file

It depends, lets test it.

[oracle@server1 Desktop]$ expdp system/oracle directory=DATA_PUMP_DIR schemas=jay dumpfile=jay.dmp

Export process has started and I tried to shut the database down in a separate session.

SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.

And I noticed the following error in the export log.

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JAY"."MY_OBJECTS"                          98.58 MB 1017888 rows
. . exported "JAY"."TBL2OBJECTS"                         98.58 MB 1017888 rows
. . exported "JAY"."TBLOBJECTS1"                         8.225 MB   84824 rows
. . exported "JAY"."FOOD"                                6.875 KB       6 rows

UDE-01089: operation generated ORACLE error 1089
ORA-01089: immediate shutdown in progress - no operations are permitted
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1
Process ID: 28921
Session ID: 36 Serial number: 45

Now I tried to import one of the tables which was exported.

[oracle@server1 Desktop]$ impdp jay/jay directory=DATA_PUMP_DIR tables=my_objects dumpfile=jay.dmp

Import: Release 11.2.0.4.0 - Production on Wed Mar 22 04:47:15 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JAY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "JAY"."SYS_IMPORT_TABLE_01":  jay/password directory=DATA_PUMP_DIR tables=my_objects dumpfile=jay.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JAY"."MY_OBJECTS"                          98.58 MB 1017888 rows
Job "JAY"."SYS_IMPORT_TABLE_01" successfully completed at Wed Mar 22 04:47:50 2017 elapsed 0 00:00:33

And it succeeded.

But sometimes you may end up with the following errors.

  ORA-39002: invalid operation ORA-39059: dump file set is incomplete

  ORA-39246: cannot locate master table within provided dump files

Conclusion: You can restore exported tables as far as the dump file contains the  export job's master table - a table which is created during the startup of export process to keep the location of database objects within a dump file. You could see that master table called SYS_IMPORT_TABLE_01 is loaded before importing the table specified at the starting line of impdp log in the above section.


You can create a wrapper for the nvl2 call:

create or replace function add_comma(s varchar) return varchar as
begin
  return case when s is null then null else ','||s end;
end;
/

with t as (select 'key2' as val from dual union all select null from dual)
select val, nvl2( substr(val, 3, 2), ','||substr(val, 3, 2), null) as orig,
       add_comma(substr(val, 3, 2)) as wrapped
from t;

VAL  ORIG WRAPPED
---- ---- -------
key2 ,y2  ,y2

alternatively you can use a with clause (also know as a CTE ):

with t as (select substr('key2', 3, 2) as s from dual)
select nvl2(s, ','||s, null) from t;


After upgrade to SQL Server 2019 some stored procedures fail

This looks to be a problem with UDF inlining. As @ScottHodgin suggested, the first step is to install the latest CU to see if the problem persists. If that doesn't resolve the problem, your options are:

Turn off TSQL_SCALAR_UDF_INLINING for the database:
 USE YourDatabase;
 ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Add INLINE OFF to the problem function header. This will allow the benefits of scalar inlining for other functions.

Refactor the problem query as to avoid the error.


Try:

select ac.Account_Name, ac.Account_ID, count(t.ID) as 'Total Tickets'
from accounts ac
join tickets t on
ac.Account_ID = t.Account_ID
group by ac.account_ID, ac.account_name

I think the only error in your query is that you aliased "tickets" as "ticket", but your query is also more complex than it needs to be.


Yes you can define in pgbouncer your 2 servers (or more) masetr and slave and connect to each of them.
There is different kind of topology you can install pgbouncer on each www, as is the connection will be the fastest (www process can use unix socket to connect), on install a pgbouncer on each server (master and slave) or install it on a new host to be single.
PErsonnaly I recommend another topology, install pgbouncer everywhere, on each www (to increase the connection), and on each db server, it will help you when you'll want to do migrations for example. PgBouncer support PAUSE/RESUME on connection, as is you can reconfigure your conenction on move traffic to master to a new host when you'll upgrade verison for example.


Postgresql: Select a table from different database and different host

The second error message means, that your Postgres on db_name2 is not configured to allow connections from IP_db1. Unrelated to this problem: with modern Postgres versions it's usually better to use a foreign table, rather than the dblink module. – a_horse_with_no_name

You need to edit your pg_hba.conf file on the far host (x.x.x.x) to allow the connection. Don't forget to reload the server config files after for it to take effect. It is probably better to debug connection issues with "psql" rather than using dblink or postgres_fdw, those are just going to confuse the issue. – jjanes


Is there a way to analyze a MySQL query and determine whether it's read-only?

I think it will be fairly complicated to do both completely automatic as well as efficient. Imagine a transaction like:
BEGIN TRANSACTION
SELECT ... FROM T1 WHERE ...
UPDATE T2 SET ... = ...
SELECT ... FROM T3 WHERE ...
END TRANSACTION

Would you direct statement 1 and 3 to your replica whereas statement 2 would be asked against your master?
If not, you need to analyze at the transaction level. That, in turn, can be really complicated due to branching in the code.
A viable option would be to do read-only reporting against your replica


Mixing MyISAM and InnoDB in ordinary Replication is OK.
MyISAM can screw up in any form of replication.
MyISAM is not allowed in the latest HA techniques -- Galera and Group Replication.
Master-Master, but writing to only one at a time, will let you continue to have MyISAM, and give you quick (perhaps one-minute) failover.
MHA is another approach using standard Replication.

In the long run, you really need to choose between HA and MyISAM.


Identify table usage in sql server before we stop the load

I think your best approach would be to start with just tracking the counters in sys.dm_db_index_usage_stats.  Just store the results off to a table (and have that run on a schedule) for as long as you feel necessary.  Then look at the counters to see if there is significant movement on them.  There will always be a little, especially if you have maintenance going.
If you think they are done, then rename the table and see if anyone screams.  You can quickly rename it back if you need to.
Finally, if you want an extended events session to track, the below might work for you.  As you are aware, it has an impact, but it's not going to be as bad as you might think, especially for a table that you think is not being used.  Still, when you first enable it, test it to make sure it catches what you want, as well as that it's impact isn't hindering your users.  However, it's the only sure way I know to catch this type of thing.
The below is what I typically use when I want to catch usage of specific stored procedures or queries that contain certain text strings.  I send it to a ring buffer because I typically only run it until I've got what I'm looking for.  But you can alter the target to whatever you want.  It creates the XE turned off, so you can go back in and edit it before starting it up.
CREATE EVENT SESSION [SpecificSQLWatch] ON SERVER
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%<my string here>%')))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO


can I quickly create tables for each partition?

If you don't need the original data, you can exchange the partitions with empty tables that you create yourself.

Demo:

SQL> create table originaltable
(
  id number primary key
)
partition by range (id)
(
  partition original1 values less than (10),
  partition original2 values less than (20),
  partition original3 values less than (MAXVALUE)
);

Table created.

SQL> insert into originaltable values (9);

1 row created.

SQL> insert into originaltable values (11);

1 row created.

SQL> insert into originaltable values (55);

1 row created.

SQL> create table newtable1_lessthan10
(
  id number primary key
);

Table created.

SQL> ALTER TABLE originaltable
  EXCHANGE PARTITION original1
  WITH TABLE newtable1_lessthan10
  WITHOUT VALIDATION;

Table altered.

SQL> select * from newtable1_lessthan10;

        ID
----------
         9

SQL>


Multibyte replace not working in SQL Server

I can't explain why the first one isn't working (perhaps some subtlety of that particular Unicode character). This produces a rather unexpected result:

SELECT REPLACE(N'IAșI', N'șI', 'x') -- returns 'xAșx'

The second one will appear to work because SQL Server replaces multi-byte characters with '?' when casting down to varchar.

This small example demonstrates the behavior, as all arguments will be converted to varchar literals:

SELECT 'IAșI', REPLACE('IA?I', 'ș', 'x'), REPLACE('IAșI', '?', 'x') -- returns 'IAxI'

EDIT: Seems to be collation related.

SELECT REPLACE(N'IAșI' COLLATE Latin1_General_CI_AS, N'ș', 'x') -- doesn't match (returns 'IAșI')
SELECT REPLACE(N'IAșI' COLLATE Latin1_General_Bin, N'ș', 'x') -- returns 'IAxI'


Insert Into MySQL over linked server when a medium text is the last column breaks Int fields

I'm going to go out on a limb and say that there's probably a bug somewhere - especially given the mediumtext data type. Here's a little work I did, obviously there're a few additional tests that you can run. Here is a SQLFiddle for your reference.

DDL

CREATE TABLE `tblState` (
  `StateID` int(11) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblState2` (
  `StateID` int(11) NOT NULL,
  `Details` mediumtext NOT NULL,
  `State` varchar(50) NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblState3` (
  `StateID` int(11) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Details` mediumtext NOT NULL,
  PRIMARY KEY (`StateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DML

INSERT INTO tblState(StateID,State,Details) values (1,'alabama','test1');
INSERT INTO tblState2(StateID,Details,State) values (1,'test2','alabama');
INSERT INTO tblState3(StateID,Details,State) values (1,'test2','alabama');

INSERT INTO tblState(StateID,Details,State) VALUES (2,'It is nice in','Colorado');
INSERT INTO tblState2(StateID,State,Details)
    VALUES (3,'South Dakota','Is actually very nice');
INSERT INTO tblState3(StateID,State,Details)
    VALUES (3,'South Dakota','Is actually very nice');

INSERT INTO tblState(Details, State, StateID)
    VALUES ('It never rains in southern','California',7);
INSERT INTO tblState2(Details,StateID,State)
    VALUES ('It used to be so hard to spell, it used to make me cry',5,'Mississippi');
INSERT INTO tblState3(Details,StateID,State)
    VALUES ('It used to be so hard to spell, it used to make me cry',5,'Mississippi');

select * from tblState;

renders:

STATEID     STATE       DETAILS
--------------------------------------
1           alabama     test1
2           Colorado    It is nice in
7           California  It never rains in southern

while

select * from tblState2;

renders:

STATEID     DETAILS                                                 STATE
--------------------------------------------------------------------------------
1           test2                                                   alabama
3           Is actually very nice                                   South Dakota
5           It used to be so hard to spell, it used to make me cry  Mississippi

Finally,

select * from tblState3;

renders the output from table 2 in the order of table 1 (as expected):

NOW, having said all of this, this has been executed on a MySQL server directly (not through a linked) that is a recent version (MySQL 5.5). Perhaps you can run the content of the fiddle directly on your server through phpMyAdmin. This may help us understand if your MySQL version is the problem, or not.

You'll note that tblState3 preserves the original order of the problematic table while varying the order of inserts, just to add a bit of robustness to the experiment.


Date of birth saving to MYSQL DB with timestamp or date column type

You should just use Date as datatype to store Date field.

Additionally, Specify Timestamp over Datetime datatype if records are often updated every time the record is changed. If you want to store a specific value you should use a Datetime as datatype.


How much data can SQL Server full text indexing handle?

I've not implemented FTS anywhere near that scale personally but there are documented examples:

  In addition to being robust in its support of concurrent users,
  Metalife has found SQL Server 2005 full-text searches to be fast. “SQL
  Server 2005 is at least as fast as our custom code was,” says Pavlova.
  “We created a custom algorithm to return all full-text searches
  against our 1.4 terabyte database in less than a second, and that is
  what we’ve seen with SQL Server 2005.” [Source]

1.4TB is mentioned as the source database here but that doesn't necessarily translate to TB+ of full text indexed data.

This example is quite smart, extracting content from files before shuffling them off to cheap cloud storage:

  There are currently over 50 million files (over 10 Terabytes of data)
  that the customer is migrating into a custom project management
  application (for international customers) that can be accessed via the
  application and can be searched upon.  SQL Server 2008 full text
  indexing is used to index the content of these files which gives them
  rich searching capabilities within their application. [Source]

Newsgator is documented as running a 2.5 billion row, 4TB full text implementation and FileControl as having a 2 billion, 1TB system back in 2005. Given the other options available today, would they do the same now?

Given the licensing cost of SQL Server I'd be inclined to consider alternatives if they fit your use case. Lucene or Solr are the obvious open source choices.


What is difference between mongodump and mongoexport?

mongodump generates binary copies of data; it creates better, more efficient backups.

mongoexport can create JSON files; these can be used by other programs, and are basically human-readable as is.


Check all tables in a schema for X fields?

DECLARE @sch SYSNAME = N'my_schema_name';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) + '.' + QUOTENAME(t.name)
  + ' ADD createddate DATETIME
      NOT NULL DEFAULT GETDATE();'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = N'createddate'
  );

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) +'.' + QUOTENAME(t.name)
  + ' ADD updateddate DATETIME;'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = N'updateddate'
  );

PRINT @sql;
--EXEC sys.sp_executesql @sql;

To automate this a little further, you can use a table variable or #temp table (no such thing as an array in SQL Server):

DECLARE @cols TABLE
(
  col SYSNAME,
  ctype SYSNAME,
  def SYSNAME
);

INSERT @cols(col,ctype,def) VALUES
(N'createddate', N'DATETIME', N'NOT NULL DEFAULT GETDATE()'),
(N'updateddate', N'DATETIME', N'NULL'),
(N'somethingelse', N'VARCHAR(32)', N'');

DECLARE @sch SYSNAME = N'my_schema_name';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(@sch) + '.' + QUOTENAME(t.name)
  + ' ADD ' + QUOTENAME(cols.col) + ' ' + cols.ctype
  + ' ' + cols.def + ';'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
CROSS JOIN @cols AS cols
WHERE s.name = @sch
AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns
      WHERE [object_id] = t.[object_id]
      AND name = cols.col
  );

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Note that in both cases above, depending on the number of tables that don't have these columns, PRINT might truncate the output it shows you, but this is due to an inherent 8K limitation of PRINT (or less data depending on your settings in SSMS). I can assure you the command it will execute doesn't end abruptly like that.


Instead of using a script component, I recommend using a more database-centric approach.

Consider the following approach:

Read the data from the flat file using a flat file source.  Keep your flat file source simple.  It should have one column which contains the EntireRow.
(optional) add a script component or derived column to count rows and generate a InputRowNumber field.  The InputRowNumber is helfpul for debugging.
Write the two columns to a simple staging table called InputFlatFile containing the InputRowNumber int, and EntireRow varchar(max) fields.
Using ExecuteSQL Tasks, execute queries which insert the data from your InputFlatFile staging table into your real database.  Based on your example data, these queries should be fairly straight forward.

INSERT INTO HeaderTable (NAME_EDC, DUNS_EDC)
SELECT SUBSTRING(EntireRow, 231, 60) NAME_EDC
     , SUBSTRING(EntireRow, 291, 13) DUNS_EDC
FROM InputFlatFile
WHERE LEFT(EntireRow,2) = '01'

INSERT INTO DetailTable (columns..)
SELECT SUBSTRING(EntireRow, 1, 2) REC_TYPE_DETAIL
     , SUBSTRING(EntireRow, 3, 6) PMT_NUMBER
     (... more columns)
FROM InputFlatFile
WHERE LEFT(EntireRow,2) = '02'

use an ExecuteSQL task to permanently delete the data in the staging table so it is ready for the next load, or use an OUTPUT clause to move it into an archive table if necessary.

I think the database-centric approach is better for the following reasons:

From this base, you can easily create a queryable data lineage.  Want to know which file created which row in the final database?  Create a FileLog table and insert a row at the start of the control flow with logging information like file name, modified date, etc.  Retain a reference to the InputRowNumber and FileLogID in the destination database and you can trace the data from source file to destination easily.
In my experience, people who work with SSIS tend to have a stronger database background than OO programming background so the Set Theory that I used above comes naturally and is easier to maintain on an ongoing basis (also, there tends to be more helpful ideas for database-centric approaches on this and other forums)


Should database files be on different disk(s) than OS?

It's typically good practice to seperate your OS installation from your database installation to isolate OS disk issues from database disk issues.

The primary reason for this is to reduce the change that a problem with the OS could prevent recovery of the database, or vice versa.  If your database fills up the free disk space, it can crash both the database and the OS.

This also allows you to have different recovery options available for the different pieces.  We typically run RAID 1 (Mirroring) on our OS disks, and RAID 5 (or some other type of parity + striping) on our Database disks.  Since the OS disk has fewer writes, we can take the performance hit of mirroring, to reduce recovery time in a crash situation.  But on the Database side we want to maximize the performance of reads/writes at the expense of recovery time in a crash situation.


Postgres 9.6.5 - Can I use a query to calculate a value from two columns and insert the calculated value in a new column in the same table?

I found the below which seems to have done the trick...

Firstly, I added the Value3 column to Table1 using the below SQL:

ALTER TABLE "Table1" ADD COLUMN Value3 NUMERIC;

Then I ran the below update and set statement:

UPDATE "Table1" SET Value3 = Value2 - Value1;

This updates the Value3 column with the result of Value2 - Value1.


Upgrading MySQL 5.1 to 5.5 on Centos 6 without removing dependencies

You can remove MySQL without removing the dependencies with RPM:

rpm -e --nodeps mysql

Keep in mind that yum is going to complain next time you try to install a package about the rpm database being modified. You can correct that with yum clean all.


Are the MariaDB and MySql drivers interchangeable when connecting to a MySQL backend Aurora cluster?

Can MariaDB connector (driver) be used instead of the MySQL connector (driver) when connecting to AWS Aurora cluster MySQL instance?

Yes. I've personally used neither the MariaDB Connector/J nor the MySQL Connector/J against AWS Aurora cluster MySQL. However, according to the MariaDB documentation on its Connector/J - my emphasis:

  MariaDB Connector/J is used to connect applications developed in Java
  to MariaDB and MySQL databases using the standard JDBC API. The
  library is LGPL licensed.

So, MariaDB's Connector/J officially supports MySQL databases, and Amazon Aurora MySQL is supposed to be a drop-in replacement for MySQL.

Also, note that according to the same documentation, MariaDB Connector/J has a special 'aurora' failover parameter. See MariaDB's Knowledge Base article Failover and High availability with MariaDB Connector/J for more details.

I've not found anything specifically mentioning Aurora in the MySQL Connector/J documentation, so it would seem MariaDB's connector could actually be a better choice.

It should also be mentioned that the licences are different: MariaDB Connector/J is LGPL v2.1+ and MySQL Community Connector/J is GLPv2 with a few additions.


Best away to keep monthly records of loans

Real world financial transactions do not follow simple assumptions such as "the client will make one payment per month".

Make a TRANSACTION table that records money coming in and money going out along with the details of that transaction, such as where the money came from (which client), where the money went to (which loan), and importantly when this happened (the date).  You might want to consider adding a transaction type to provide extra information, with values like "loan", "payment", "adjustment" etc.


Transfer selected information from a SQL Server business server to a MySQL web server?

I've had to do this a couple of times, with varying complexity. My typical approach is to install the MySQL ODBC connector on the SQL Server host, create an ODBC DSN to the MySQL database, then set it up as a linked server in SQL Server.

Once that's out of the way, you can write T-SQL procedures/scripts to synchronize and replicate the necessary data.


The issue is that double wildcard '%string searches%' are slow in SQL Server because we don't have fancy trigrams searches natively.

Even with a an index leading on the string search column, we have to read every row.

CREATE NONCLUSTERED INDEX ix_displayname ON dbo.Users(DisplayName)

SELECT u.Id, u.Reputation, u.DisplayName
FROM dbo.Users AS u
WHERE u.DisplayName LIKE '%Christopher%'

If you're only ever searching for one string (which is never the case), or you have a limited set of known search strings an indexed computed column (or columns) might work in your favor.

ALTER TABLE dbo.Users ADD SearchString
    AS CONVERT(BIT, CASE WHEN DisplayName LIKE '%Christopher%' THEN 1 ELSE 0 END)

CREATE INDEX ix_yourmom ON dbo.Users (SearchString)

Otherwise, you're gonna have to come up with a better way to locate the data that doesn't involve searching the text, like an OrderLines table which tracks each item in the order that can be searched on an ID.


Is there a way to improve performance here using No Exists?

You can try to rewrite it as:

select *
FROM followers_info finfo
WHERE NOT EXISTS (
    SELECT 1 FROM user_followers uf
    WHERE uf.follower_id = finfo.follower_id
)
AND NOT EXISTS (
    SELECT 1 FROM user_following fing
    WHERE fing.follower_id = finfo.follower_id
);

or:

select *
FROM followers_info finfo
WHERE NOT EXISTS (
    SELECT 1 FROM user_followers uf
    WHERE uf.follower_id = finfo.follower_id
    UNION ALL
    SELECT 1 FROM user_following fing
    WHERE fing.follower_id = finfo.follower_id
);

Generally no better nor worse, but worth a try


using update query with subquery in Oracle sql

I would recommend to have a look into the merge into statement when youre deailing with oracle.

if you just want to update rows omit when not matched part of the statement

see http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/sql-merge/index.html and https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm


How to export specific columns from a SQL table

Using phpMyAdmin run a SQL query against the table that you want a column of data from and select only that column (something along the lines of SELECT cas_number FROM analytes).

Scroll to the bottom of the results and click on Export. The only thing that will be exported is the column of data that was selected.


The only alternative to performing consistency checking (DBCC CHECKDB) is to leave your data open to the possibility of corruption and data loss.

There are some ways to reduce the impact that CHECKDB has on your system. The best way to do this by far is to run your checks offline on a copy of your live systems. This should be done by restoring a full backup of your database onto another system and then running DBCC CHECKDB there.
This is good for two reasons:

It off loads the IO and perfomance issues your are experiencing onto another machine
It tests your backups restore successfully.

If you can't afford to have a secondary server for testing backups and running DBCC CHECKDB then you may have to break the check down and run it against a few individual tables each night to spread the load out accross the week. You can do this with the CHECKTABLE statement. Another way to break the job into smaller parts would be to use CHECKFILEGROUP to check each file group, only makes sense if you have multiple file groups.

You can also try trace flags 2549, 2562, and 2566. For more information on the trace flags, the methods mentioned above and more read Aaron Bertrand's post on the subject here


MySQL import csv only gets half the lines

You might have to check the last character of each line

Instead of this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'
INTO TABLE players
FIELDS TERMINATED BY ','
ENCLOSED BY '^'
LINES TERMINATED BY '\n';

Try this:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'
INTO TABLE players
FIELDS TERMINATED BY ','
ENCLOSED BY '^'
LINES TERMINATED BY '\r\n';


What is the load impact of the Zabbix DB size query?

This 'random person on the Internet' can shed some light...

The information_schema is not a 'real' database (until MySQL 8.0), rather it is derived from a bunch of data that may or may not be in RAM already.
If not all the data is in memory, then the query will have to get it from disk -- by reading lots of .frm files (etc).  "Thousands" in your case.  So, this could be quite costly.
The Data_length, etc are relatively stable; I hope your monitoring is not doing this query every minute; that may be a noticeable burden.  Once an hour should be sufficient.
What is the value of table_open_cache?  Is it more than the number of tables?  If not, then this random person on the Internet thinks that you could be in deep .
How lame!  Rather than generating a different query when you ask for "all", the package builds a complex boolean expression.

I have been involved in the monitoring of hundreds of machines.  A simple df is sufficient to alert a human to dig into what is causing the problem.  Checking the MySQL directory with a du is one of several secondary steps.

But, I disagree with every monitor I have seen.  I don't care how big the data is.  It does not matter if the disk is 90% full -- as long as it is not growing.  I do care if it is 20% full but growing so fast that it will fill up before the end of the week.

Sure graphing the size is handy, but that means that a human has to look at it (and not fall asleep).  I want an alert.  Furthermore, due to cyclical things, such as binlog purging, the graph may look like saw teeth -- up, down, up down!  This makes "trend analysis" difficult.

Back to your question of whether that query is "intensive".  If Zabbix is worth having it will give you the answer on a silver (or electronic) platter!  Does it monitor the "slowlog"?  If so, does this query show up?  QED.


Safe to use odbc link betw Filemaker 11 Server & SQL Server 2012?

Filemaker 11 was released in 2010. SQL Server 2012 was released in (surprisingly enough) 2012.

So, the Filemaker 11 documentation wouldn't list SQL Server 2012 as supported, because it wasn't released yet. When I looked for documentation online, I found the "Filemaker 11 ODBC and JDBC Guide", which has a 2010 copyright date.

By FileMaker 15, the documentation doesn't list explicit versions of other products it does or doesn't work with. It just days it works with products Like Oracle, SQL Server, and MySQL (no specific versions listed).

Since:

Every version of SQL Server from 2000 on that was out when FileMaker 11 was released was listed as working with it,
Later versions don't bother listing versions, but still show SQL Server as something they work with, and
You're successfully able to query it now

I wouldn't think there would be any unexpected risks in using them together.


Normalization/normal forms - May a field describe an other field?

I disagree with the other answers on this one and do not believe this violates 3NF.  This is because date_of_birth_precision does not imply date_of_birth nor does date_of_birth imply date_of_birth_precision.
It's important to note the definition of a functional dependency:

Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X → Y) if, and only if, each X value is associated with precisely one Y value; R is then said to satisfy the functional dependency X → Y. Equivalently, the projection  is a function, i.e. Y is a function of X.1 In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be determined by looking them up in any tuple of R containing x. Customarily X is called the determinant set and Y the dependent set.

So let's ask ourselves, for a given value of date_of_birth_precision, does that imply a date_of_birth?  Certainly not.  For a given value of date_of_birth, does that imply a date_of_birth_precision?  It doesn't, otherwise you wouldn't even be asking this question.  Since neither implies the other, there is no functional dependency between the two.  Therefore, this does not break 3NF because 3NF only qualifies the rules for functional dependencies; it says nothing about attributes which are not functionally dependent on each other.
I find the alternate definition of 3NF much easier to comprehend, personally:

A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if, for each of its functional dependencies X → A, at least one of the following conditions holds:

X contains A (that is, X → A is trivial functional dependency), or
X is a superkey, or
Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each column in A-X is contained in some candidate key)


In star schemas, what should I do when a dimension has a date attribute?

Old question & I know the original posters are inactive, but since it came up in a Google search, I thought I'd add my thoughts.

  1) Make Start_Date and End_Date regular, atomic attributes (either
  ISO8601 strings or the DB's datetime object) and only allow limitted
  filtering.

Putting a date attribute into an existing dimension makes sense if the main goal is informational (i.e. people want to include the start/end date of the promotion in reports). However, if the intention is to filter, then filtering on an atomic date attribute will be relatively painful.

  2) Make Start_Date and End_Date foreign keys to the Date table,
  breaking the star schema, but allowing the same filtering capabilities
  as the full Date dimension.

This type of snowflaking is called an Outrigger Dimension and can be acceptable in a star schema. (http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/outrigger-dimension/). I imagine this is what you did.

  3) Include attributes like Start_Date_Day_of_Week, Start_Date_Quarter,
  maintaining the star schema but increasing the dimension size and
  essentially duplicating the structure of the Date dimension in the
  Promotions dimension.

The duplication would get messy (maintaining multiple date fields, especially since you have multiple dates in the same dimension). It also makes future maintenance more complex: if you want to alter your date dimension, you have to repeat that alteration in dozens of places (or have an inconsistent user experience).

On the flip side, this might be a valid option if, for example, there are just one or two ways that you know people want to filter promotions on. For example, if you know end-users want to quickly filter to active promotions or "promotions closed in the last 30 days", you could calculate and add that as a specific attribute in the promotion dimension, rather than linking to a full date dimension for promotion start & end date. (Though what starts out as one or two attributes can expand quickly, as end-users find more and more time periods they want to filter to.)

There is a 4th and 5th option, depending on the specifics of what you intend to filter by promotion start/end dates:

4) Add the promotion start/end date fields as keys in the Sales table.

You'll be storing the same piece of information each time a promotion is used, so at first glance, it feels like a lot of duplication. But denormalization is the name of the game in a star schema. If your goal is to filter to sales based on the promotion start or end date (i.e. sales where the promotion start date was last month), I would recommend this over an outrigger dimension.

5) Create a new, factless fact table, for promotions

If your goal isn't to filter sales at all, then a factless fact table might be a better solution. This could tell you how many promotions were in effect on a given date (regardless of whether there were associated sales), for example. More information: http://www.kimballgroup.com/2011/04/design-tip-133-factless-fact-tables-for-simplification/

My experience with dimensional modeling is that the best solution often depends on what the end-user is trying to accomplish. If you (or they) are not yet sure what they're trying to accomplish (i.e. you know promotions have start & end dates and that's it), I'd start with option #1. This puts the information in your model without too much work.

If it later transpires end-users are trying to filter Sales by start & end dates, that's when I'd consider #2 or #4. If it transpires end-users are trying to analyze promotions independently of sales, I'd look at #5.


InnoDB has a transaction running, but "SHOW PROCESSLIST;" shows nothing

Is it possible that your InnoDB settings are way too small ?

The default innodb_buffer_pool_size is 8M. Could you increase it ?

The default innodb_log_file_size is 5M. Could you increase it ?

You should resize them as follows:

Goto this link to compute the proper size for the innodb_buffer_pool_size based on the amount of InnoDB you have or 75% of installed RAM, whichever is smaller.

Once you have this number, set innodb_log_file_size to 25% of the computed innodb_buffer_pool_size. In order to resize them, you will have to shutdown mysql, delete the innodb log files, and start mysql.

Give it a Try !!!

CAVEAT

IMHO The reason for this conjecture is that lots of MVCC activity + default InnoDB configs spells a lot of internal InnoDB maintenance we may never know is going on.

Here are other possibilities :

Could the .ibd file be corrupt ?
Is the .ibd on a degraded array ?
Is there an insufficient number for the innodb_open_files option ?
Is tmpdir folder too full (tmp tables log jamming in the tmp folder) ???
Are the my.cnf files of the two instances identical (less server_id) ???


I believe what you're looking for is psql, which is Postgres' command line tool.


How do i get a list of records from two tables that have different values based on a matching PK?

Note MySQL 8 is has been released for more then a year now
   which supports COUNT(DISTINCT ...) OVER(PARTITION BY ...) syntax..

There are multiple solutions possible,

Yes the solutions are hard, one of the problems is that MySQL does not (jet) support

COUNT(DISTINCT ...) OVER(PARTITION BY ...)

otherwise the solution would have been much more easy.

one of the solutions is.

Query

SELECT
 *
FROM (

  SELECT
   table1.*
  FROM
   table1

  UNION ALL

  SELECT
   table2.*
  FROM
   table2
) AS merged

INNER JOIN (

  SELECT
     total_data.IP
   , COUNT(*) = COUNT(DISTINCT total_data.Name) AS has_name_changes
   , COUNT(*) = COUNT(DISTINCT total_data.Gateway) AS has_gateway_changes
   , COUNT(*) = COUNT(DISTINCT total_data.Version) AS has_version_changes
  FROM (
    SELECT
     table1.*
    FROM
     table1

    UNION ALL

    SELECT
     table2.*
    FROM
     table2
  ) AS total_data
  GROUP BY
     total_data.IP
) AS search_filter
ON
   merged.IP = search_filter.IP
WHERE
   search_filter.has_name_changes = 1
 OR
   search_filter.has_version_changes = 1

Result

| IP  | Name | Gateway | Version | IP  | has_name_changes | has_gateway_changes | has_version_changes |
| --- | ---- | ------- | ------- | --- | ---------------- | ------------------- | ------------------- |
| 2   | B    | 2.0.0   | v2      | 2   | 1                | 0                   | 0                   |
| 2   | F    | 2.0.0   | v2      | 2   | 1                | 0                   | 0                   |
| 4   | D    | 4.0.0   | v4      | 4   | 0                | 0                   | 1                   |
| 4   | D    | 4.0.0   | v10     | 4   | 0                | 0                   | 1                   |

see demo

The other solution which is even harder and only works when you have two records, one in each table.

Query

SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT
   merged.IP
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 1
    )
    , ','
    , -1
  ) AS Name
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 2
    )
    , ','
    , -1
  ) AS Gateway
 , SUBSTRING_INDEX(
     SUBSTRING_INDEX(
       SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      )
      , ','
      , 3
    )
    , ','
    , -1
  ) AS Version

,  SUBSTRING_INDEX(
         SUBSTRING_INDEX(
           GROUP_CONCAT(
             CONCAT_WS(
                ','
              , merged.Name
              , merged.Gateway
              , merged.Version
             )
             SEPARATOR ';'
           )
            , ';'
            , sql_number_generator.number
         )
         , ';'
         , -1
      ) AS merged_record
FROM (
  SELECT
   *
  FROM
   table1
  UNION ALL
  SELECT
   *
  FROM
    table2
) AS merged
CROSS JOIN (
  SELECT
   1 AS number
  UNION
  SELECT
   2 AS number
) AS sql_number_generator
GROUP BY
   merged.IP
 , sql_number_generator.number
HAVING
   MIN(merged.Name) <> MAX(merged.Name)
 OR
   MIN(merged.Version) <> MAX(merged.Version)

Result

| IP  | Name | Gateway | Version | merged_record |
| --- | ---- | ------- | ------- | ------------- |
| 2   | B    | 2.0.0   | v2      | B,2.0.0,v2    |
| 2   | F    | 2.0.0   | v2      | F,2.0.0,v2    |
| 4   | D    | 4.0.0   | v4      | D,4.0.0,v4    |
| 4   | D    | 4.0.0   | v10     | D,4.0.0,v10   |

see demo


Is row based or column based raw input better?

From a database design point of view, #1 is better.  But...  In either case (#1 or #2) you should not populate your database with only one table.

You should have these tables (at least):

Students - one row per student: Student id, name, email
Courses - one row per course: Course id (created by you), course name
StudentCourses - one row per student-course pair that exists: student id (but no other student info), course id, status, completion date.


Join two tables based on Aggregate Function sum() result

You cannot use SUM in a WHERE clause. However, you can use it in a HAVING

SELECT C.NAME,C.ADDRESS,O.CUSTOMERID
FROM CUSTOMERS C
INNER JOIN
(
    SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders
    FROM ORDERS
    GROUP BY CUSTOMERID
    HAVING SUM(ORDERAMOUNT)>5000
)O ON C.CUSTOMERID = O.CUSTOMERID;


SQL Server CONTAINS ignores single quote before asterix

CONTAINS is used for full-text searching, and can search for:

for a word or phrase
the prefix of a word or phrase
a word near another word
a word inflectionally generated from another word, such as spoon and spooning
synonyms

Perhaps you should use:

DECLARE @String2 NVARCHAR(20) = '"SomeName''%"';
SELECT *
FROM dbo.NamesTable NT
WHERE NT.Name LIKE @String2;


I've created a quick model:

USE tempdb;
IF EXISTS (SELECT 1 FROM sys.schemas s WHERE s.name = 'wde')
BEGIN
    DROP TABLE wde.INTERACCION;
    DROP TABLE wde.TIPIFICACION;
    DROP TABLE wde.DICCIONARIO;
    DROP SCHEMA wde;
END
GO
CREATE SCHEMA wde;
GO
CREATE TABLE wde.INTERACCION (ID int);
CREATE TABLE wde.TIPIFICACION (ID int, CODIGO int);
CREATE TABLE wde.DICCIONARIO (CODEID int, [DESCRIPTION] varchar(100), [PARENT] varchar(100), [LEVEL] int, CODIGOID int);

INSERT INTO wde.INTERACCION (ID) VALUES (59);
INSERT INTO wde.TIPIFICACION (ID, CODIGO) VALUES (59,1);
INSERT INTO wde.DICCIONARIO(CODEID, DESCRIPTION,PARENT,LEVEL,CODIGOID)
VALUES (100, 'MARCA VEHICULO'       , 0  , 1, 1)
    , (101, 'SUCURSAL'              , 0  , 1, 1)
    , (102, 'CONTACTADO'            , 0  , 1, 1)
    , (105, 'FIAT'                  , 100, 2, 1)
    , (117, 'ECUADOR'               , 101, 2, 1)
    , (122, 'CONTACTO TELEFONICO'   , 102, 2, 1);

This query gets the results you want, but may only be valid for your limited set of test data shown.  Without more specific requirements from you, it's difficult to be concise.

SELECT i.ID
    , Level1 = STUFF((SELECT ', ' + d.DESCRIPTION
        FROM wde.DICCIONARIO d
            INNER JOIN wde.TIPIFICACION t ON d.CODIGOID = t.CODIGO
        WHERE t.ID = i.ID AND d.PARENT = 0 FOR XML PATH('')),  1, 2, '')
    , Level2 = STUFF((SELECT ', ' + d.DESCRIPTION
        FROM wde.DICCIONARIO d
            INNER JOIN wde.TIPIFICACION t ON d.CODIGOID = t.CODIGO
        WHERE t.ID = i.ID AND d.PARENT <> 0 FOR XML PATH('')),  1, 2, '')
FROM wde.INTERACCION i;

Results:

╔════╦══════════════════════════════════════╦════════════════════════════════════╗
║ ID ║                Level1                ║               Level2               ║
╠════╬══════════════════════════════════════╬════════════════════════════════════╣
║ 59 ║ MARCA VEHICULO, SUCURSAL, CONTACTADO ║ FIAT, ECUADOR, CONTACTO TELEFONICO ║
╚════╩══════════════════════════════════════╩════════════════════════════════════╝


Using GETDATE() in a variable returns 0 rows

The problem is that GETDATE() returns a DATETIME. So the WHERE condition checks if any of the values in that column is equal to that exact date and time (+3 days).

You need to strip the time part, either by casting or by using a date variable:

declare @_NextAppt as datetime ;        -- use same type as the column

set @_NextAppt = dateadd(day, 3, cast(getdate() as date)) ;
      -- strips the time from getdate(),
      -- adds 3 days and
      -- does an implicit conversion to the variable's type (datetime)

We also don't know the type of the column. It would be good to use inclusive-exclusive conditions when comparing datetime values. The query below will work independent of whether your column is DATE or DATETIME or DATETIME2:

select distinct PatID, StaffID
from dbo.Appointments
where PatID is not null
  and ApptDate >= @_NextAppt
  and ApptDate  < dateadd(day, 1, @_NextAppt) ;

If the column is DATE, you could of course use an equality condition:

---
where PatID is not null
  and ApptDate = @_NextAppt ;

Necessary links to blog posts about good (and bad) practices with dates (note how I replaced the shorthand dd to the full day datepart when using the DATEADD() function above):

Bad Habits to Kick : Using shorthand with date/time operations
Bad habits to kick : mis-handling date / range queries
What do BETWEEN and the devil have in common?


SQL Server Cluster and Windows Server Storage Replication

I finally managed to figure it out. I think information is missing, hard to find and no described in much detail. So for me I was a bit in the dark when figuring this out. Also, I think I had some tunnel vision at the start thinking that storage replica works in a certain way. MS has a different way of configuring it and the way it works/ you are supposed to use it.
Scenario A
Possibly an option, but is cumbersome and I don't think MS will support this. I also have not tested this. In order to fail over you probably need to:

Stop the cluster
Switch the replication direction
Add the iSCSI disks to the iSCSI target server at site B as an available iSCSI LUN
Add the LUNs to the SQL Servers again
Add the LUNs to the cluster as CSV, so they will be new as far as the cluster is concerned
Potentially detach and re-attach the databases, because the path could have changed if you have not renamed your volume folder names in C:\ClusterStorage\ when intially setting up the cluster

If someone could comment on this who has more experience on this, that would be great.
Scenario B:
This is the way this is intended to be used. In order to correctly setup the configuration you need to:

Create a multi-site cluster and make sure Windows Server Datacenter version is used for all nodes. Else it will not support replication.

Configure the storage. What is not in the mentioned documentation is that (per LUN you want to replicate) you need to have the data source disk configured as CSV and the other disks configured as Available Storage. Move all the available storage to the node that is the owner of the source disk on Site A (so that will include the available storage in the other site, Site B. This will bring some disks offline, no worries the Storage Replica Wizard in Failover Cluster Manager you will use later on uses logic that will detect which node has access to what storage

Configure fault domains, which is mentioned in the doc also mentioned by stephenmorris

I think that the fault domains in relation to Storage Replica, are used to determine which nodes are at what site and therefore what storage is accessible at what site. This is because you see some storage tests going on in the background during the Storage Replication wizard. However, I am not sure of this.
New-ClusterFaultDomain -Name SiteA -Type Site -Description "Primary" -Location "Site A"
New-ClusterFaultDomain -Name SiteB -Type Site -Description "Secondary" -Location "Site B"

Set-ClusterFaultDomain -Name Node1 -Parent SiteA
Set-ClusterFaultDomain -Name Node2 -Parent SiteA
Set-ClusterFaultDomain -Name Node3 -Parent SiteB
Set-ClusterFaultDomain -Name Node4 -Parent SiteB

(Get-Cluster).PreferredSite="Seattle"

What they probably forgot in this code is to turn the feature on, which you do by using:
#Enable fault domain awareness
(Get-Cluster).AutoAssignNodeSite=1

Reading the document again at a later time, I am not really sure if this is a requirement, since the only role for the Fault Domains in relation to Storage Replica seems to be to determine what storage is located at what site. It is not mandatory to have this awareness during normal operation? You would need to enable the auto behaviour if you have nodes and disks spread across racks on a single site for example and you want to use Storage Spaces Direct for your storage fault tolerance? Maybe someone could comment on that as well.

Configure Storage Replica

Now what is also not described in the document is that you need to do this via Failover Cluster Manager. Right click on the source disk -> 'Enable Replication'. In the documentation they use Powershell. This works as well, but you need to use the name of the cluster as the source AND destination computer. Not the nodes themselves. Also, for SQL Server you need to add the storage volume to a Consistency Group. You will be given this question in the GUI. The logic used by the wizard will automatically detect what volumes are accessible by which nodes. You will see this happening in the background.
Somewhere in the aforementioned document is a link, which is not easy to find, to a video that has more details: https://channel9.msdn.com/Events/Ignite/2015/BRK3487
Something regarding the consistency groups is not clear to me by the way. I don't see any way how I can add 2 source volumes to 1 consistency group. Or am I just no understanding the concept correctly?


How do you change the storage parameters of a already made table (sql developer)?

The extent parameters can not be changed of an existing table, they are specified when a table is created. You can change them by reorganizing the table.
SQL> select INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE
     from user_tables where table_name = 'T1';

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
         65536     1048576  2147483645

SQL>  alter table t1 storage(initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1);
 alter table t1 storage(initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1)
                        *
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

SQL> alter table t1 storage(next 2097152 maxextents 1073741822 pctincrease 1);
alter table t1 storage(next 2097152 maxextents 1073741822 pctincrease 1)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

SQL> create table t2 storage (initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1)
     as select * from t1;

Table created.

SQL> select INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE
     from user_tables where table_name in ('T1', 'T2');

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
         65536     1048576  2147483645
       1048576     2097152  2147483645

Notice how the MAX_EXTENTS and PCT_INCREASE parameter did not change. They are relevant only for dictionary managed tablespaces, but that was forgotten ages ago, we use locally managed tablespaces.


Moving SQL Server Database Files to New Location - Why permission error?

The ALTER DATABASE MyDB MODIFY FILE (Name = MyDB, Filename = 'N:\DATA\MyDB.MDF') statement doesn't actually move the files.  It merely moves the definition of where the files reside.  You'd need to actually move the files manually before continuuing to step 4.

When you move the files prior to step 4, you must ensure the target directory has the correct permissions for the SQL Serve service account.  This document details the requirements.


Page Lookups Percentage - SQL Server performance monitoring

The metric is trying to measure the number of logical IO operations performed by each request, in average. If your server is, lets say, serving a busy web site, then is probably a good metric (perhaps 100 is even too high for such!).

The witness will only have internal activity. In other words it will touch data files internally, due to various background processes, despite never seeing any actual batch. As such all internal logical IO will be reported as caused by the few batches this server has seen.

I would say that the metric Thomas is recommending has some value but only in a a workload similar to what Thomas had in mind. A server that has only background activity, like your witness, is not going to report anything meaningful with this ratio. A server that runs intensive DW reports may see only 1 batch request per hour and run millions of page lookups for that one request. An absolute number like 'must be below 100' is only valuable accompanied by smallprint disclaimers like must be bellow 100 if your workload has frequent enough batch request to establish significant statistical trends and each batch requests is expected to perform under 100 page lookups in average. You get the idea...


Save output of a Mongo Command to a Variable bash script

Mongo -command have multiple parameters that are handy in this case.
'--eval' let you run commands and '--quiet' keep all clutter away
out=$(mongo --quiet --eval="db.currentOp()") ; echo $out


XML is bonkers

When you add the concatenated string, you lose the "path element".

For example if you do this:

SELECT t.type + '/' AS type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML PATH('');

SELECT t.type + '/'
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML PATH('type');

You get this back:

<type>Green/</type>
<type>Blue/</type>
<type>Red/</type>

The column name or alias acts as the path element.

Some other examples that might help

Using RAW, ELEMENTS

SELECT t.type + '/'
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, ELEMENTS;

SELECT t.type + '/' AS type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, ELEMENTS;

In the first example, you get the generic "row" element name, but in the second you get row/type.

When using RAW, TYPE:

SELECT t.type + '/' AS type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, TYPE;

SELECT t.type + '/'
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML RAW, TYPE;

The first query returns valid-ish XML, the second throws an error because the path element lacks an identifier.

Using AUTO, the table alias and column name turns into the path:

SELECT type + '/' AS type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

SELECT type
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

But without an alias, you get a similar error:

SELECT type + '/'
FROM   ( VALUES ( 'Green' ), ( 'Blue' ), ( 'Red' )) AS t ( type )
FOR XML AUTO;

I'd gin up an example with FOR XML EXPLICIT but it would be irresponsible for me to start drinking right now.


I can only repeat what I tell everyone - Data Model Resource Book Volume 1 is maybe half about that topic and it is a serious goldmine of ideas and complications.

Seriously, depending what you want to cover that is a brutal topic. Some things are sized (3 x  2meter lone,  5x1.5 meter long), some have colors, some sizes, some colors AND sizes.

Price may depend on attribute, volume combined dimension (total meters). World of pain.

The question is how much of that you NEED - and what yo can cut.

I suggest reading the book for also in depth discussions about the topic.

Sadly, that is all I can say - without a requirement, a review of your schema is moot (did you forget something? How can I know... we only can check what is possible, not what is wanted, in product complexity).


MySQL (as well as Postgres) allows you to select from nothing, so you can do this:

select *
from (
  select 1 as some_value
  union all
  select 2
  -- etc.
) t

Since you intend to join this set to a table, you'll need to provide column aliases that you can reference later at least in the first inner select.


Amazon RDS remove read-only and allow defining table structures

In my case, I ran into this problem on one of my stacks—but not the first time the stack was built. It seems I was referencing an AWS::RDS::DBInstance's DBInstance.Endpoint.Address, but that instance was actually the master member of an Aurora RDS database cluster.

For some reason, accessing the instance directly sometimes caused the --read-only error. I updated my template to output an AWS::RDS::DBCluster's DBCluster.Endpoint.Address instead (so my app would point at the cluster endpoint, and not the instance directly), and everything worked fine.

It was puzzling to me why this happened sometimes when I built a stack and not others... but the lesson is to make sure you use the cluster endpoint if you have a multiple-member Aurora/RDS cluster, and not just a single instance.


Understanding linking tables in a many-to-many relationship

Actually you don't need a special value for people that aren't there
When you select a photo andand you don't know if there aren't any people in it, you make
SELECT p.*,pe.*
FROM Photo p
   LEFT JOIN reftable r ON p.id = r.p_id
   LEFT JOIN peope pe ON  r.pe_id = pe.id
WHERE p.id = 123

So even when there is no entry in the Relationship table, you get an photo.
If you have one person and want to find all photos that they are in
SELECT pe.*,p.*
FROM peope pe
   INNER JOIN reftable r ON r.pe_id = pe.id
   INNER JOIN Photo p  ON p.id = r.p_id
WHERE pe.id = 123

You need here a relationship table, because many persons could be on a photo or one person can be in many photos


As far as I know there is issue with SQL Server 2014 upgrade advisor, it sometime hangs when analyzing the workload for migration. What you can do is download SQL Server 2016 upgrade advisor which is now called as Data Migration Assistant V 3.1. As per Microsoft the upgrade advisor 2016 is deprecated and is replaced by Data migration assistant.

I am not quite sure but I believe MS folks knew about this that is why they moved to much better tool data migration assistant.

This does not comes inbuild with SQL Server installation media it is a separate download.


write csv file only after SQL table update, insert, delete

Check out Query Notifications. You could leverage this functionality and a SQL Server agent job to monitor for data changes and execute your Python script on change.

Basically, you create a query notification for the data in question and create a stored procedure that will receive the notification, execute the Python script then return to wait for the next notification.

Schedule a SQL Agent job to run on startup and start this procedure and it will run continuously (unless it is stopped or fails, but you should be monitoring for these events in SQL Server anyway) and execute your Python script whenever data changes.

It's not the same as a trigger, as in you still must poll the queue for notifications of data changes, but the Service Broker technology it is built on is very lightweight compared to polling the tables constantly or adding triggers to your tables.


Pulling data into Access from an external API

The most straightforward way to have Access VBA interact with a web service would be to use a MSXML2.ServerXMLHTTP object like this:

Option Compare Database
Option Explicit

Sub WebRequestExample()
    Dim httpReq As Object, response As String
    Set httpReq = CreateObject("MSXML2.ServerXMLHTTP")
    httpReq.Open "GET", "http://checkip.dyndns.com", False
    httpReq.Send
    response = httpReq.responseText
    Set httpReq = Nothing
    Debug.Print response
End Sub

In this case the response string contains

<title>Current IP Check</title>Current IP Address: 192.168.53.49

If the web service returns a relatively short and straightforward response then you could just parse it yourself.


Connecting to SQL Server database from a .net application

There are different ways to connect to Database.

1. ADO.NET

using System;
using System.Data;
using System.Data.OleDb;

class Sample
{
  public static void Main()
  {
    OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

    OleDbCommand catCMD = nwindConn.CreateCommand();
    catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";

    nwindConn.Open();

    OleDbDataReader myReader = catCMD.ExecuteReader();

    while (myReader.Read())
    {
      Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
    }

    myReader.Close();
    nwindConn.Close();
  }
}

2. Web.Config Connection String

 <connectionStrings>
    <add name="ConnStringDb1" connectionString="Data Source=localhost;Initial Catalog=YourDataBaseName;Integrated Security=True;" providerName="System.Data.SqlClient" />
  </connectionStrings>

3. Connect to a SQL Database and Use the LINQ to SQL Designer

This process involves multiple thing, you can read more here

https://blogs.msdn.microsoft.com/charlie/2007/11/19/connect-to-a-sql-database-and-use-the-linq-to-sql-designer/


Joining Oracle tables with 2 strings to be oa a single row (in a distinct SQL filter)

Simply do a GROUP BY instead of DISTINCT:

SELECT RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB,
    coalesce(MIN(A32.COD_REC),'') || coalesce(MIN(A32.COD_VAL),'')
FROM chest cht
    INNER JOIN raport rap
        ON cht.fk_id_raportor = rap.id
    INNER JOIN sesiune_rap ses_rap
        ON ses_rap.id = CHT.FK_ID_SESIUNE_RAP
    full outer join a3_t2_rows a3t2
        on A3T2.FK_ID_ANEXA = cht.id
WHERE A3T2.TIP_AMB ='LEMN'
GROUP BY RAP.ID_PL, SES_RAP.AN,  RAP.DENUMIRE,
    A3T2.COD_DESEU, A3T2.TIP_AMB


Performance-wise I think there are a few issues you will run into on MySQL.  The first one is that MySQL doesn't handle very large numbers of joins as well as one might like.  This can cause performance issues.   Also you do have extra table scans and that has a performance cost too.

Finally, and this is InnoDB specific, but every table is clustered on a primary key and the engine does not support scanning a table in physical order, so in this regard joins of large record sets is going to become a performance issue.  At some point you end up with a sequential scan but this is a sequential index scan, traversing every node in a tree, and that leads to a lot of extra disk I/O.


Look SHOW PROCESSLIST - the most time the process state is 'waiting for handler commit'.

If your server version is 8 use recursive CTE instead:

INSERT INTO `data` (`id`, `datetime`,`value`,`channel`)
WITH recursive
cte AS (select 1 i
        union all
        select i+1 from cte where i <= 600000)
select i,
       FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
       ROUND(RAND()*100,2),
       i
FROM cte;

If you have 5+ server, use synthetic number tables:

INSERT INTO `data` (`id`, `datetime`,`value`,`channel`)
select i,
       FROM_UNIXTIME(UNIX_TIMESTAMP('2014-01-01 01:00:00')+FLOOR(RAND()*31536000)),
       ROUND(RAND()*100,2),
       i
FROM (  SELECT 1+d1.num+d2.num*10+d3.num*100+d4.num*1000+d5.num*10000+d6.num*100000 i
        FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d1,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d2,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d3,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d4,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d5,
             (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) d6
      ) d0;

At the same system (server version 8.0.18):

your procedure - over 20 min. (killed after 5 min, 122k records were inserted)
insert with CTE - 3.10 sec.
insert with synth. tables - 4.08 sec.


What are the sp_send_dbmail return values?

Error code 101 is because your query result attachment is larger than the "MaxFileSize" Database Mail configuration option.
Investigation
My first attempt at figuring this out was to run sp_helpttext on the sp_send_dbmail procedure to look at the implementation:
EXEC sp_helptext 'sp_send_dbmail';

The main body of that procedure returns a bunch of different status codes, from 0 up to 21.  It doesn't mention 101.  However, it calls into a few other stored procedures, like sysmail_verify_profile_sp, so I glanced in that one:
EXEC sp_helptext 'sysmail_verify_profile_sp';

But that only returns codes from 0 to 4.
Later the proc calls into sysmail_verify_addressparams_sp, let's check it out:
EXEC sp_helptext 'sysmail_verify_addressparams_sp';

Shucks, that only returns 0 or 1.
Next up is sp_RunMailQuery:
EXEC sp_helptext 'sp_RunMailQuery';

It calls sysmail_help_configure_value_sp and sp_isprohibited, both of which only return 0 or 1.
Black Box
At the end of sp_RunMailQuery, a system extended stored procedure is called: xp_sysmail_format_query.
We can't grab the source code for this, but I had a hunch from earlier in the proc:
--Get the maximum file size allowed for attachments from sysmailconfig.
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'MaxFileSize',
                                            @parameter_value = @fileSizeStr OUTPUT

I noticed that you are using the attachment parameter, and the query results + max file size both get passed into the extended stored procedure as well.
Repro City
I set my Database Mail maximum file size to 10 bytes, and then ran:
DECLARE @result AS INT;

exec @result = msdb.dbo.sp_send_dbmail
    @profile_name = 'DBMail',
    @recipients = 'myemailaddress@something.com',
    @subject = 'This is the subject',
    @body = 'This is the body',
    @body_format='html',
    @query = 'SELECT * FROM sys.messages;',
    @exclude_query_output = 1,
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'big-file.csv',
    @query_result_separator = '===',
    @query_result_header = 1;

SELECT @result;

Note: the "sys.messages" table has a bunch of string data that I knew would be more than 10 bytes.
And sure enough, I got 101 as the return code:

Bugz
As CR241 helpfully pointed out, the documentation says that @@ERROR should contain the error id number that corresponds to the appropriate error message in the sys.messages table:

The error code for the statement that failed is stored in the @@ERROR variable. - sp_send_dbmail - Return Code Values

Instead it's 0 in my testing (which you noted in your original question).  This seems like a bug to me.


Is C collation suitable for my Postgres database?

From what you're describing, it doesn't sound like anything is contraindicated related to the C collation order. In fact, Robert Haas, has a great discussion on the subject here, which seems to come to the conclusion that if you don't need locale aware sorting, just don't use it: Perils of Collation Aware Comparisons

I did notice you are on Debian Wheezy. The stock kernel for Debian Wheezy is 3.2, which if you're using that, has abysmal performance on database workloads. I would investigate that along with the collation testing, if you are not happy with the performance of your machine.

The wheezy-backports kernel is 3.16, which does not have these issues. Discussion and performance tests are here: Benchmarking PostgreSQL with Different Kernel Versions

Hope that helps. =)


Trying to take a backup of a database, but drives are different than in file explorer

Are you using SMSS on the actual SQL server?

Or are you using SMSS on a local machine then connecting to SQL server over the network?

If you are connecting to a remote SQL Server over the network that would explain the differences in the drives you're seeing.


How to restore TDE encrypted DB on AWS (with certificate not stored in master)?

This is a bit of a gap with RDS.  Apparently the advice is to remove TDE on the source DB then back it up so that it can be restored to RDS.
This is a pretty poor scenario in my opinion - switching on and off TDE should not be taken lightly.  I really hope that AWS enable some functionality to backup/restore certificates for TDE


How to log within a stored procedure that is encapsulated within a transaction

It seem's that one viable way of solving this issue is using a table engine that does not support transactions. In this case changing the table engine of the log tables from InnoDB to MyISAM solved the problem.


How do I combine rows in sql query and remove duplicates

SELECT `Name`
     , `Account Number`
     , MAX(`Accout Role 1`) `Accout Role 1`
     , MAX(`Account Role 2`) `Accout Role 2`
FROM MyTable
GROUP BY `Account Number` -- , `Name`


I found YCSB, the Yahoo Cloud System Benchmark:
https://github.com/brianfrankcooper/YCSB

It has bindings for many DBMSs, including jdbc and rest interfaces, so it seems to have exactly what I needed.

Thanks!


Implementing a Circular Buffer (Sliding window) in MySQL

Instead of a Trigger, how about a Stored Procedure?
Here is some sample data:
DROP DATABASE IF EXISTS adam_matan;
CREATE DATABASE adam_matan;
use adam_matan
CREATE TABLE pixels
(
   id int not null auto_increment,
   type VARCHAR(30),
   timestamp timestamp DEFAULT CURRENT_TIMESTAMP,
   pixel_data BLOB,
   PRIMARY KEY (id),
   KEY type_timestamp_id_ndx (type,timestamp,id)
);
INSERT INTO pixels (type,timestamp,pixel_data) VALUES
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type1',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type2',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100)),
('type3',now() - INTERVAL FLOOR(RAND()*86400) SECOND,CONCAT('px',FLOOR(RAND()*899)+100));
SELECT * FROM pixels;

Here is it executed
mysql> SELECT * FROM pixels;
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
|  1 | type1 | 2012-07-05 00:47:02 | px338      |
|  2 | type2 | 2012-07-05 07:20:24 | px178      |
|  3 | type3 | 2012-07-05 09:03:07 | px337      |
|  4 | type1 | 2012-07-05 06:34:06 | px323      |
|  5 | type2 | 2012-07-05 15:58:22 | px403      |
|  6 | type3 | 2012-07-05 01:00:47 | px267      |
|  7 | type1 | 2012-07-05 16:08:21 | px541      |
|  8 | type2 | 2012-07-05 06:10:06 | px687      |
|  9 | type3 | 2012-07-04 16:35:29 | px994      |
| 10 | type1 | 2012-07-05 16:21:52 | px116      |
| 11 | type2 | 2012-07-05 14:14:51 | px449      |
| 12 | type3 | 2012-07-05 00:03:50 | px307      |
| 13 | type1 | 2012-07-05 13:37:46 | px890      |
| 14 | type2 | 2012-07-05 15:01:37 | px676      |
| 15 | type3 | 2012-07-05 15:29:27 | px334      |
| 16 | type1 | 2012-07-05 11:43:37 | px266      |
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 18 | type3 | 2012-07-04 19:47:46 | px771      |
| 19 | type1 | 2012-07-05 12:26:28 | px619      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 21 | type3 | 2012-07-05 15:03:14 | px575      |
| 22 | type1 | 2012-07-05 04:54:36 | px821      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 24 | type3 | 2012-07-04 22:56:23 | px236      |
+----+-------+---------------------+------------+
24 rows in set (0.00 sec)

mysql>

Here is the Stored Procedure
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
BEGIN

  DECLARE KeepPixels INT;

  SET KeepPixels = 5;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);

  DROP TABLE IF EXISTS pixel_window;
  CREATE TEMPORARY TABLE pixel_window
  (id INT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

  SET @sqlstmt= CONCAT('INSERT INTO pixel_window ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt;
  EXECUTE st;
  DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);
  DELETE A.* FROM pixels A LEFT JOIN pixel_window B USING (id)
  WHERE A.type=GivenType AND B.id IS NULL;
  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;
  DROP TABLE IF EXISTS pixel_window;

END $$

DELIMITER ;

Let's INSERT four rows of pixel data into type2
mysql> CALL AddPixel('type2',CONCAT('px',FLOOR(RAND()*899)+100));
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
|  2 | type2 | 2012-07-05 07:20:24 | px178      |
|  5 | type2 | 2012-07-05 15:58:22 | px403      |
|  8 | type2 | 2012-07-05 06:10:06 | px687      |
| 11 | type2 | 2012-07-05 14:14:51 | px449      |
| 14 | type2 | 2012-07-05 15:01:37 | px676      |
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
+----+-------+---------------------+------------+
9 rows in set (0.08 sec)

+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 14 | type2 | 2012-07-05 15:01:37 | px676      |
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
+----+-------+---------------------+------------+
5 rows in set (10.23 sec)

Query OK, 0 rows affected (10.27 sec)

mysql> CALL AddPixel('type2',CONCAT('px',FLOOR(RAND()*899)+100));
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 14 | type2 | 2012-07-05 15:01:37 | px676      |
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
+----+-------+---------------------+------------+
6 rows in set (0.08 sec)

+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
+----+-------+---------------------+------------+
5 rows in set (10.25 sec)

Query OK, 0 rows affected (10.29 sec)

mysql> CALL AddPixel('type2',CONCAT('px',FLOOR(RAND()*899)+100));
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 17 | type2 | 2012-07-05 08:02:11 | px261      |
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
| 27 | type2 | 2012-07-05 16:32:55 | px293      |
+----+-------+---------------------+------------+
6 rows in set (0.07 sec)

+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
| 27 | type2 | 2012-07-05 16:32:55 | px293      |
+----+-------+---------------------+------------+
5 rows in set (0.15 sec)

Query OK, 0 rows affected (0.19 sec)

mysql> CALL AddPixel('type2',CONCAT('px',FLOOR(RAND()*899)+100));
+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 20 | type2 | 2012-07-05 06:51:44 | px323      |
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
| 27 | type2 | 2012-07-05 16:32:55 | px293      |
| 28 | type2 | 2012-07-05 16:32:56 | px162      |
+----+-------+---------------------+------------+
6 rows in set (0.08 sec)

+----+-------+---------------------+------------+
| id | type  | timestamp           | pixel_data |
+----+-------+---------------------+------------+
| 23 | type2 | 2012-07-05 02:26:48 | px543      |
| 25 | type2 | 2012-07-05 16:31:59 | px638      |
| 26 | type2 | 2012-07-05 16:32:36 | px102      |
| 27 | type2 | 2012-07-05 16:32:55 | px293      |
| 28 | type2 | 2012-07-05 16:32:56 | px162      |
+----+-------+---------------------+------------+
5 rows in set (0.16 sec)

Query OK, 0 rows affected (0.19 sec)

mysql>

If you want to keep another number, change the line KeepPixels = 5; to whatever number you need.
Give it a Try !!!
UPDATE 2012-07-05 17:52 EDT
If you want to limit the DELETEs to every 100 INSERTs, here it is:
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN

  DECLARE KeepPixels,DeleteLimit,MaxID INT;

  SET KeepPixels = 5;
  SET DeleteLimit = 100;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;

  DROP TABLE IF EXISTS pixel_window;
  CREATE TEMPORARY TABLE pixel_window
  (id INT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

  SET @sqlstmt= CONCAT('INSERT INTO pixel_window ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);
  DELETE A.* FROM pixels A LEFT JOIN pixel_window B USING (id)
  WHERE A.type=GivenType AND B.id IS NULL;
  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;
  DROP TABLE IF EXISTS pixel_window;

END $$

DELIMITER ;

You can change DeleteLimit to throttle DELETEs at any interval you want.


AskUbuntu.com has this question: How to install Oracle 11g?.

Oracle Database 11g and Ubuntu 11.10 Oneiric Ocelot has some good information.

Here are some obligatory links to the relevant 11g pdf documentation.

Database Installation Guide for Linux
Database Quick Installation Guide for Linux x86
Database Quick Installation Guide for Linux x86-64
Release Notes for Linux


sql developer was taking a long time to open files dialog. I found out that it was because it is opening local temp folder which has a huge amount of files. Guess what, these files are being generated by sql developer. They are name amcXXXX.tmp.
I didnt find how to avoid its generation though.


SQL server Read Only permission automatically for new databases

Reference:
model Database

  The model database is used as the template for all databases created
  on an instance of SQL Server. Because tempdb is created every time SQL
  Server is started, the model database must always exist on a SQL
  Server system. The entire contents of the model database, including
  database options, are copied to the new database. Some of the settings
  of model are also used for creating a new tempdb during start up, so
  the model database must always exist on a SQL Server system.

Add the user to your model database db_datareader role.  All future database will mimic this privilege.

USE [model]
GO
CREATE USER [sqllogin1] FOR LOGIN [sqllogin1]
GO
USE [model]
GO
ALTER ROLE [db_datareader] ADD MEMBER [sqllogin1]
GO

Once you run this sqllogin1 user will be created in all new database and will be a member of db_datareader role.


Native tooling approach

Grab a newer copy of Management Studio (2012 SP2 and 2014 are both free, fully functional, and can co-exist with your 2008 R2 tools). Then you can do this.

Put your cursor right before the first leading ABC.
Hold Shift+Alt, then hit the down arrow three times.
Type '.

It's subtle, but you should see a faint blue vertical line here indicating that keystrokes will actually affect all 4 lines (or 150 lines).

Excel approach

If you don't want to use a more modern version of Management Studio, then just add the stuff in Excel. Insert a new column, put a single-quote in the first cell where you want it, hover over the bottom right of the cell until the cursor becomes a solid +, then click and drag to the bottom:

Repeat for the second single-quote, commas, etc. Copy all, paste into SSMS, then do a quick replace for '<tab>, <tab>',, etc.

Regular expression approach

Your third alternative is to use a Regular Expression, valid with all versions of SSMS

Find what: {.+}
Replace with: '\1',
Look in: Selection
Expand Find Option
Use: Regular expression (checked)

That regular expression indicates find everything and remember what we found
Replace everything we found \1 by wrapping it with with tic marks and a comma

If you have more complex requirements, the right chevron next to the drop down arrow on Find what lists the regular expression dialect SSMS/Visual Studio understands


1a. Warm standby is a "live", incremental backup fed with complete blocks of changes (wal segments) 16 mb each, that are send to the standby node once they're filled. You cannot query a warm standby node. 16 mb of changes (by default) can mean a lot of transactions, should the master fail, they'll be lost.

1b. Hot Standby. (also a "live" incremental backup) .small changes are send to the slave (wal records, which are  tiny parts of a wal segment). You can query (read only) the hot standby node. The window for lost transactions should the master fail is very tiny. There are synchronous and asynchronous hot standby nodes, a synchronous node will force the master to wait for it to confirms the application of changes and then the master will commit the transaction.In asynchronous replication the master sends the wal records and does not wait for confirmation. The former requires a very reliable and fast link between the master and the slave,also adds overhead to the master but guarantees no data loss.

Regarding incremental backups: 1. You take a base copy of your whole database installation. 2. Ship it to the slave. 3. Configure it to catch up for changes.

Streaming Replication (hot standby) is the winner here. I personally prefer asynchronous replication as it does not impose a considerable burden on the master and the replication lag is very small (a couple of seconds in many cases)

One complement to this setup is pg-pool. It acts as a proxy between the application and the servers participating in a replication configuration like the one described above, it has load balancing and parallel query capabilities. It's able also to provide automatic failover.
http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting/index.html


couchbase - multiple buckets or documents with different types

I'm also new to Couchbase but from what I've learned so far: generally, the reason you would want to use different buckets doesn't necessarily relate to the type of documents you want to put in them. That is, you may configure buckets to replicate differently, use XDCR, allocate memory differently, and so on. However, it's perfectly acceptable to have a wide variety of different document "types" in the same bucket.

It might be a good idea to give the different documents a "type" property, so that you can more easily query them.

So, for instance, in your examples, maybe the first one would have "type":"server", and the second one would have "type":"audit", and so on.


AlwaysOn Disaster Recovery Clarifications

1)How to revert back the AOAGs to original Synchronous mode -- Should I Perform a Forced Manual Failover of an Availability Group to PRODUCTION Servers(Sync) from DR/BCP (Asynchronous) node ?

You've failed over to asynchronous nodes. This means all of the database flows are paused and there is no current way (assuming it's a true disaster) how far behind your secondary replicas were. Now that they've come back up, we know that it isn't going to be 100% the same data (they are asynchronous). This was not mentioned in the question but I'm going to add it to the answer as it's extremely important as it's part of your SLA.

Take a database backup of the original primary databases. To do this you will need to kick them out of the AG. Alternatively you could take a database snapshot (inside of SQL, not SAN).
Resume data movement between the replicas at the database level.
Set the local primary replica to synchronous and a remote replica to synchronous.
Wait for them both to say 'Synchronized'. They will start out as 'Synchronizing'. This could take quite a while depending on multiple factors such as downtime, data generated, IO, Networking, etc.
Once Synchronized, find a good time to take a minute or less outage. Use that time to do a planned manual failover.
Set the local servers to be synchronous to each other.
Remove synchronous from the remote replica and set it back to async.

  2)We have one extra BCP node MSDTC and AOAGs Listener. Should I turn then online before I Perform a Forced Manual Failover of an Availability Group from PRODUCTION Servers(Sync) to DR/BCP (Asynchronous) node ?

How do you have an "extra AOAG listener" just chilling out? I don't understand that part of your question.

MSDTC is for distributed transactions, which aren't supported in 2012/2014 and only supported with certain restrictions (as of this moment) in 2016. Thus this is not required in a supported scenario. If you're going unsupported then this is still a moot point as the local MSDTC will be used.

Unless I'm missing something (completely possible) or not understanding, this is not needed.


How to calculate mysql server reads per second,writes per second?

There are two ways of determining this.

RECENT VERSIONS OF MYSQL

One is to use the performance_schema (assuming 5.6 or higher).

There are many tables that you can query to get at this information, particularly if you have innodb_file_per_table = 1 in your my.cnf.

If you do USE performance_schema there are several tables that can help you to find this information, notably (from DESC TABLES;)

| file_instances                                     |
| file_summary_by_event_name                         |
| file_summary_by_instance                           |

and

| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |

Then queries such as

mysql> SELECT SUM(sum_number_of_bytes_read) AS Total_Read
FROM file_summary_by_instance;

+----------+
|Total_Read|
+----------+
| 10470860 |
+----------+
1 row in set (0.00 sec)
mysql>

and

mysql> SELECT SUM(sum_number_of_bytes_read)
+ SUM(SUM_NUMBER_OF_BYTES_WRITE)
AS Total_IO
FROM file_summary_by_instance;

+----------+
| Total_IO |
+----------+
| 15161292 |
+----------+
1 row in set (0.00 sec)
mysql>

can help you get the information you want.

OLDER VERSIONS OF MYSQL

If you don't have a recent version of MySQL, then you can use the Unix utility iostat to help you obtain a (reasonably good) estimate of your overall usage.

[pol@localhost inst]$ iostat
Linux 3.11.10-301.fc20.x86_64 (localhost.localdomain)   11/05/15    _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.33    0.03    2.97    6.12    0.00   84.55

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
scd0              0.02         0.09         0.00        116          0
sda              13.34       544.46        55.79     742770      76109
dm-0              0.20         0.82         0.00       1116          0
dm-1             16.14       497.16        23.26     678245      31736
dm-2              5.69        42.97        32.52      58628      44364
[pol@localhost inst]$

Some of the readings are on a per/second basis, but others represent data from machine bootup. You should obtain a baseline figure (with no MySQL running) and then take your measurements with the RDBMS running and subtract the baseline from the MySQL numbers - that way you will have a reasonable estimate of how much I/O is going on in your server.

iostat is available through the sysstat package which may or may not be installed on your distro - use man iostat (and man sar &c.). The package is available from here. Check out the documentation for your particular system.

For completeness, I should also add that this information is available on an ad-hoc basis through the (excellent) program MySQL Workbench, as shown by the screenshot below:


postgres jsonb query performance is decreased by order by

Remove the GIN index delivery_expr_idx you created and make sure that created still has an index.

Then try

SELECT *
FROM (SELECT *
      FROM delivery
      ORDER BY created
      OFFSET 0 LIMIT 10000) AS q
WHERE document @>
      '{"links": [{"type": "receipt","externalId": "9990863500764120180212"}]}'
ORDER BY created
LIMIT 100;

I'm cheating with the LIMIT 10000, but since almost all rows satisfy the WHERE condition, I hope to get away with it.


Query gives correct answer on second run

There's so much wrong with your query.

In your subquery sort you do a left join, but your where clause actually converts it to an inner join.

You seem to rely on the order by in the sort subquery. You don't do an order by in the outer query. Maybe you can practically rely on it to work, but in theory you can not.

Your variables @curType and @curRow are nowhere initialized. Either do this before the query with a SET @variable = 'whatever' command or in a cross joined subquery in your query. This is most likely the cause, why it gives a "correct answer" on the second run. Variables have session scope! Not query scope. You can not rely on them to be NULL or whatever you want them to be when you don't initialize them.

The keyword AND is not used to do two commands! It's used in boolean expressions, which return either 1 (true) or 0 (false) or NULL (unknown, if one of the operands is NULL. So, better check your result, if it really is correct ;)

Since it's not totally clear to me, what you're trying to do, I can only have a guess. But something like this may be what you're looking for.

SELECT * FROM (
    SELECT a.*,
    @curRow := IF(concat(question_id,`type`) = @curType, @curRow + 1, 1) AS rank,
    @curType := concat(question_id,`type`)
    FROM
    ul_attempt_responses a
    INNER JOIN us_attempts b ON a.attempt_id = b.id
    JOIN (SELECT @curType := NULL, @curRow := 0) var_init_subquery
    WHERE b.user_id = 3
    AND response IS NOT NULL
    AND trim(response) != ''
    AND b.authenticated = 1
    ORDER BY question_id, type, id DESC
) final WHERE rank = 2;


how can I insert a long string into a blob using an insert statement?

It looks to me like the string you're passing in is too long for the UTL_RAW procedure you're trying to call. From the Oracle documentation:

  There is an error if the sum of the lengths of the inputs exceeds the maximum allowable length for a RAW, which is 32767 bytes.

found here: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_raw.htm#ARPLS072

I think you may need to utilize the DBMS_LOB package. Documentation found here: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm#BABDDFDH

HTH.


Modelling the structure of a database involving products with multiple barcodes

If you have a truly one-to-many relationship between your products and barcodes, it makes sense to keep it in a separate table for barcodes (don't repeat yourself-- your name and description would be identical for each repeated barcode).

IF however there is any variation in the product itself (name or description could vary) then you need to store each as a discrete record.


Enforce Data Integrity without a direct Foreign Key relationship

If I am understanding the situation correctly, then is there any reason why you can't extract that distinct list into a table to be a lookup table,  CountyCodes, create a PK on those "codes" (the CountryCode column), and then add a Foreign Key to the new table referencing that PK of the new CountryCodes lookup table?

CREATE TABLE dbo.CountryCodes
(
  CountryCode CHAR(5) NOT NULL
                CONSTRAINT [PK_CountryCodes],
  CountryName NVARCHAR(50) NULL
);

INSERT INTO dbo.CountryCodes (CountryCode)
  SELECT DISTINCT inst.Country_Codes
  FROM   dbo.Institutions inst
  WHERE  inst.Country_Codes IS NOT NULL;

ALTER TABLE dbo.NewTable
  ADD CONSTRAINT [FK_NewTable_CountryCodes]
  FOREIGN KEY ([CountryCode])
  REFERENCES dbo.CountryCodes ([CountryCode]);

-- optional FK for existing table:
ALTER TABLE dbo.Institutions
  ADD CONSTRAINT [FK_Institutions_CountryCodes]
  FOREIGN KEY ([CountryCode])
  REFERENCES dbo.CountryCodes ([CountryCode]);

And at that point, you could then add a Foreign Key on the existing table's County_Codes column to the PK of the new lookup table, right?

I am assuming that the values are 2 or 3 letter ISO country codes, and that each row of the existing table -- Institutions -- has one code in the Country_Codes column, even though the column's name is in the plural form.

IF for some reason the suggestion above will not work, then you can always create an AFTER INSERT, UPDATE Trigger on the new table to ensure that all values in INSERTED.CountryCode are IN (SELECT DISTINCT Country_Codes FROM dbo.Institutions WHERE Country_Codes IS NOT NULL) or are NULL. But doing that lookup each time can be expensive, hence the suggestion above to extract the list one time into a table.

The other benefit of extracting the distinct list of Country_Codes into a lookup table is that the values currently in the Institutions table might not be ALL possible values of valid Country Codes, so adding a new CountryCode to the new table could fail if only checking the values in the Institutions table. By having a separate lookup table, you can add values that are valid but have not yet been used. And if they are the 2 or 3 character ISO codes, you can get the full list from the ISO website and pre-populate the lookup table.


Why the concept of missing index groups is provided in SQL Server?

This was a planned feature that never was implemented (the group concept), but the meta-data (DMV structure) is still there.


Efficient way to keep a count of entries within a timeframe? e.g. How many actions did user do in the last 7 days

That's a pretty common way of doing things, and PostgreSQL is designed to handle aggregative queries for large datasets very well. Though adding an index on the user_id and timestamp columns would improve performance, if you have that ability.


Back in the days there was sp_who, sp_who2, but then came Adam Machanic and created sp_whoisactive. And he saw everything that he had made, and behold, it was very good. And there was documentation and downloads on the following days.