First, you should never ever store user passwords! Read the following two articles before continuing:
http://blog.moertel.com/posts/2006-12-15-never-store-passwords-in-a-database.html
http://www.codinghorror.com/blog/2007/09/youre-probably-storing-passwords-incorrectly.html

Your user profile table design depends on extensibility and how common the user profile types are. In object oriented design you ould probably create a base class and derive one each for parents and kids. That's not possible in SQL though.

In general the best approach is to create a single table with columns for all profile types in this case. NULL values in columns are a common feature and cost next to nothing in terms of used table space. You can get away with multiple tables, but at the least it will make your queries very awkward, because you would need three tables: One for the common columns, and one for each profile. If you want to do a generic SELECT on the membership with added profile data, you have to join all three tables on it while with a single table you only have to join one.


Querying the most recent records with two fields: datestamp and timestamp

Create a proper timestamp from the two columns:

select *
from airquality
where validdate + validtime  = (select max(validdate + validtime ) from airquality)

This assumes that validdate is corrected defined with the data type date and validtime is correct defined as a time column.


Why do I get Invalid identifier error even though column exist

Code Review

line 3-6

Columns are not fully qualified

line 2 & 21-25

DISTINCT and GROUP BY doesn't make sense in this context.  drop one

line 26-28

I wouldn't trust an ORDER BY clause in a (refactored) sub-query. Move it to the main query or drop it.

line 35-41

It's 2019.  Please use ANSI JOINs

line 34

There are duplicate column names in a SELECT statement.  Give one of them an alias.

Most likely, your problem will be fixed by (5)


If you are on SQL-Server 2012 or above you can use FORMAT function in your stored procedure.
FORMAT ( value, format [, culture ] )

DECLARE @d DATETIME = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'

If you use an older version you can use CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SELECT CONVERT(varchar(10), @d, 103);

| DateTime Result |
| :-------------- |
| 31/05/2011      |

| (No column name) |
| :--------------- |
| 31/05/2011       |

db<>fiddle here


How do I group items separated by a 0 in a column using mysql?

WITH
cte1 AS
(
SELECT shelf_id, remaining, SUM(remaining = 0) OVER (ORDER BY id) group_num
FROM test
)
SELECT GROUP_CONCAT(shelf_id) shelf_id,
       GROUP_CONCAT(remaining) remaining,
       SUM(remaining) total
FROM cte1
WHERE remaining != 0
GROUP BY group_num;

fiddle


Database ID of a non-existent database - time-out waiting for buffer latch on page appearing in error log

This issue is a characteristic symptom of heavy I/O on the host.  Perhaps there are multiple backups, DBCCs, and other "maintenance" activities happening on the SAN at that time.

Work with your VMware guy to ensure the SQL Server VMs are using the PVSCSI adapter for the LUNs present in the VM.  VMware's SQL Server Best Practices documentation says this about SCSI adapters:

  Utilize the VMware Paravirtualized SCSI (PVSCSI) Controller as the virtual SCSI Controller for data and log VMDKs.

  The PVSCSI Controller is the optimal SCSI controller for an I/O-intensive application on vSphere. This controller has a queue depth of 64 (Per Device) and 254 (Per Controller) by default (double the size of an LSI Logic SAS controller). The PVSCSI controller’s per-device and per-controller queue
  depths can also be increased to 254 and 1024 respectively, providing even more increased I/O bandwidth for the virtualized workload. See Configuring disks to use VMware Paravirtual SCSI (PVSCSI) adapters (1010298).

  Also, see how to increase queue depths in a VMware vSphere environment.

  Note:
  While increasing the default queue depth of a virtual SCSI controller can be beneficial to an SQL Server-based VM, the configuration can also introduce unintended adverse effects in overall performance if not done properly.

  VMware highly recommends that customers consult and work with the appropriate storage vendor’s support personnel to evaluate the impact of such changes and obtain recommendations or other adjustments that may be required to support the increase in queue depth of a virtual SCSI controller.

Use multiple vSCSI adapters. Placing OS, data, and transaction logs onto a separate vSCSI adapter optimizes I/O by distributing load across multiple target devices and allowing for more queues on the operating system level.
Spread the I/O load across all PVSCSI adapters to help optimize the I/O from the guest. In cases where there is a requirement for many data and
transaction log disks, it will be beneficial to set the OS boot disk
to use PVSCSI as well.

I would check the SQL Server default trace to see if DatabaseID 11 shows up.

This reads the default trace into a temporary table:

DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path
FROM sys.traces
WHERE is_default = 1;

IF OBJECT_ID(N'tempdb..#trctemp', N'U') IS NOT NULL
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt;

This will show any trace events where DatabaseID = 11.

SELECT tt.DatabaseID
    , tt.DatabaseName
    , tt.StartTime
    , tt.HostName
    , tt.LoginName
    , tt.ApplicationName
    , te.name
FROM #trctemp tt
    LEFT JOIN sys.trace_events te ON tt.EventClass = te.trace_event_id
WHERE tt.DatabaseID = 11 --missing database ID
ORDER BY tt.StartTime DESC;

DBCC CHECKDB (db_name) will create an internal snapshot of the database.  You can see the snapshot files as an NTFS alternate data stream attached to the actual database files.  So, if you run dir C:\some\path /r where C:\some\path is the location of your database files, you'll see the internal database snapshot files listed like this:

2018-03-29  03:06 PM       402,653,184 Test_DB.mdf
                           402,653,184 Test_DB.mdf:MSSQL_DBCC25:$DATA

In the sample above, you can see I have a database with a file named Test_DB.mdf.  There is also an alternate data stream named Test_DB.mdf:MSSQL_DBCC25:$DATA.  By correlating the existing database IDs in sys.databases, I am able to deduce that 25 in the DBCC25 part of the name is the database id for the snapshot.


This information can be found in the DATA_DEFAULT column of [DBA|ALL|USER]_TAB_COLUMNS.

Example:

SQL>  create table t1(c1 number, c2 number generated always as (abs(ceil(c1)*2)) virtual);

Table created.

SQL> select column_name, data_default from user_tab_columns where table_name = 'T1';

COLUMN_NAM DATA_DEFAULT
---------- --------------------
C1
C2         ABS(CEIL("C1")*2)


Fetch data from same table using two group by clauses in mysql

SELECT
    SUM(IF(user_type='I',1,0)) Individual_Count,
    SUM(IF(user_type='G',1,0)) Group_Count,
    DATE_FORMAT(dt,'%M')       "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;

or

SELECT
    SUM(user_type='I')   Individual_Count,
    SUM(user_type='G')   Group_Count,
    DATE_FORMAT(dt,'%M') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt;

If you would like the counts summed up, include WITH ROLLUP

SELECT
    SUM(IF(user_type='I',1,0)) Individual_Count,
    SUM(IF(user_type='G',1,0)) Group_Count,
    IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;

or

SELECT
    SUM(user_type='I')   Individual_Count,
    SUM(user_type='G')   Group_Count,
    IFNULL(DATE_FORMAT(dt,'%M'),'Total') "Month"
FROM
(
    SELECT user_type,
    (MAKEDATE(YEAR(join_date),1) + INTERVAL (MONTH(join_date)-1) MONTH) dt
    FROM consultant WHERE join_date >= '2014-07-01 00:00:00'
) A GROUP BY dt WITH ROLLUP;


No data in Azure SQL after importing bacpac file

TL;DR: It wasn't finished importing yet.

I found out - it was impatience. As soon as I could access the DB and see the structure I thought the import process was done. But it turned out it was still importing. So good to know that you can access it before it's ready. (In hindsight there was sort of an indicator, while it was still importing it showed 1 additional table, something with Transaction... bla).

However, I could have known as I found out that there would still be an 'Importing...' item in the Notifications panel in the Azure portal during the import. It doesn't show that on any of the blades however.


Unable to open/connect to Oracle 12c Enterprise Manager Express

I had the same problem after installing Oracle 12.2.0.1 on Windows 2016 without ASM and without RAC.

First I found that TCP/IPv6 was on so I turned it off.

Then tried following Oracle Note Number (Doc ID 1608258.1) - 12c EM Express shows Invalid Certificate Error in web browser.

Also didn't work.

I fixed it by old school way ... :)

I discovered that the Windows user Oracle_DBA (that logged on the PC and used to install Oracle 12c) don't have any permissions on the 2 files under the folder "xdb_wallet".

So:

1- I changed the ownership for the folder "xdb_wallet" from whatever it was to Oracle_DBA :

2- changed the ownership of the 2 files ( ewallet.p12 , cwallet.sso ) under the folder "xdb_wallet" from whatever it was to Oracle_DBA :

3- I did step 1 again , and checked that ownership changed for the 2 file.

The website   https://hostname:5500/em   worked fine on FireFox , because IE11/Win2016 has funny issues with Flash Player.

I don't know from security point of view what that means (steps 1-3), or what threats might happens due to those changes.

Please advice if you know.


Is there a way to add "distinct" to SSMS IntelliSense?

No, I don't believe there is any way to manipulate the IntelliSense dictionary. And you'll notice other T-SQL keywords are missing from the list, too:

SELECT
DELETE
INSERT
FROM
GROUP
ORDER
JOIN
WHERE
HAVING

UPDATE is there, but only because it is overloaded (a function you can use inside a trigger to see if a specific column was updated).

Further, I'd have to wonder what is wrong with your data model that you're typing DISTINCT enough where auto-complete will have any impact on your productivity at all. DISTINCT really should be rarely used; almost every time I see it in a query, it's being used to remove duplicates caused by bad design or bad join logic.


Mysql update different fileds set depend on condition

I like the JOIN syntax, it's clearer to me than subqueries:

UPDATE a LEFT JOIN b ON (a.ib=b.i)
SET i1 = j1,
    i2 = if(isnull(b.i), j2 ,i2)
WHERE a.id = jid;

This will change the jid record: i1 to j1 always and i2 to j2 if no match exists, and to the same value if the match exists. You may want to index b.i (probably as a primary key) if there are a lot of records and you want performance.


You're paying the extra price of network IO and there's not a whole lot you can do if you need to remove the encryption (really the key storage/management) from the database. If your MySQL instance is on a Unix host, you can try connecting with a Unix socket file instead of TCP/IP. Just host the app on the same machine during the encryption process, finish the DB then move the app back to wherever it should be.

If your MySQL DB is running on Windows then you're out of luck. You could still put the app on the same machine or isolate them from the rest of the network so you minimize impact from network congestion or switch latency but those are likely to yield limited improvement unless your network was really, really busy or if you were using some old 100Mbps switch.


Find filtered indexes on an Oracle table

It is incorrect to call functional indexes partial, because the term means something else in Oracle (specifically, indexes on subsets of partitions of partitioned tables).
Also, as you note, creating a unique index, functional or not, does not create a named constraint, and you cannot create a named constraint referencing an expression afterwards. As a result, the only place you can find these "constraints" are the *_INDEXES and *_IND_EXPRESSIONS views:
select i.table_owner, i.table_name, i.index_name, e.column_expression
from dba_indexes i inner join dba_ind_expressions e
on i.index_name = e.index_name and i.owner = e.index_owner
where i.uniqueness = 'UNIQUE' and i.index_type like 'FUNCTION-BASED%'


SET ANSI_NULLS - Performance of Stored Procedures

This does not affect the performance of stored procedures directly, but in general it can affect performance indirectly, since SET settings used at execution time can influence the plan that is chosen. Two different users, one with the setting on, and one with the setting off, will get their own copies of the plans, and they may not even be the same plan (depending on other variables at runtime, like parameter values). See Erland Sommarskog's post, Slow in the Application, Fast in SSMS? On top of differently performing queries, this can lead to potentially different performance and will create two different plans in the cache, wasting memory.

Eventually, you won't even have the ability to set ANSI_NULLS to OFF; see the documentation:

  In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So, you should already be in the habit of always setting it to ON. If it's just always on, then it won't be an issue.


Oracle - How can I find the session id of a shell script (Linux)?

In order to find a specific session running a certain script, it is easiest if the scripts makes itself identifiable by using dbms_application_info and dbms_session. See Morgans Library for details. Also see track the parts of my application that are in use for sample code.

Doing so enables you to select on v$session and filter not only on username and machine but also on module, action, client_info and client_id. Username and machine are not mutable but the other columns are controlled by dbms_application_info and dbms_session. Using those makes your script recognizable. This of course, only works when the other session has select privileges on v_$session.

dbms_application_info is also great for finding where your code is running; a way to instrumentate your code to make performance analysis easier.

your code in SQLdeveloper would be as simple as

select sid
from gv$session
where module = 'what you specified'
order by sid
/

The code added in the script to make this work would be

exec dbms_application_info.set_client_info('some detail');
exec dbms_application_info.set_module('what you specified','action');
exec dbms_session.set_identifier('DDD');


Every UPDATE in PostgreSQL creates a new version of the row.  There is no in-place update.  So not only would a new JSON be created, but also all other columns in the table would be copied.
Updating part of a JSON is not common in relational databases, or at least it shouldn't be.  If you feel the need to do so, you have chosen the wrong data model,  and you would be much better off using table columns instead of JSON attributes.  If you then split the data across several tables using a process called “normalization”, an UPDATE doesn't hurt quite as much.


The offset 0 is invalid for log file .xel Specify an offset that exists in the log file

From this post https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-xe-file-target-read-file-transact-sql?view=sql-server-ver15

I have found out that initial_file_name and initial_offset should be null initially


I have zero to none experience in the world of NoSQL so I'll add some perspective from a RDBM:s side of view.

I believe assertions where introduced in SQL92, the following is an example from:  https://mariadb.com/kb/en/sql-99/create-assertion-statement/

CREATE ASSERTION constraint_1
    CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE;

Meaning that CONSTRAINT_1 is violated if the average of the TABLE_1.COLUMN_1 values is less than 41.

However, I'm not sure how many RDBM:s that actually implements ASSERTION. Two substitutes often used is CHECK constraints and validation triggers. Example:

ALTER TABLE Table_1
     ADD CONSTRAINT C1 CHECK (column_1 >= 0);

Such a constraint will prevent any negative values of column_1.

SELECT statements are allowed in CHECK constraints according to standard, but likewise ASSERTIONS I don't think that this is widely implemented. Triggers are commonly use to implement set assertions. Here is one example from the DB2 catalog:

CREATE TRIGGER SYSTOOLS.POLICY_DR
NO CASCADE  BEFORE DELETE  ON SYSTOOLS.POLICY
REFERENCING OLD AS OLD_ROW
FOR EACH ROW
MODE DB2SQL
SECURED
BEGIN ATOMIC
    IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY
           WHERE OLD_ROW.MED = MED
             AND OLD_ROW.DECISION = DECISION ) = 1 ) THEN
        SIGNAL SQLSTATE '85101' ('At least one policy is required.');
    END IF;
END

When we try to delete the last policy an exception is thrown.

Other constraints such as UNIQUE or FOREIGN KEY could also be thought of as ASSERTIONS of the state of the universe.

From a philosophical point an "ASSERTION/CONSTRAINT" in a DBMS world is a stronger "rule" than an ASSERTION in a procedural/oo world. Whereas the latter verifies the state for a particular action. Example from:

https://wiki.python.org/moin/UsingAssertionsEffectively

class MyDB:
[...]
    def by_name(self, name):
        id = self._name2id_map[name]
        assert self._id2name_map[id] == name
        return id

The assertion alone not sufficient to protect MyDB. In addition we also need OO-concepts such as encapsulation, meaning that we must ensure that all possible ways to change the state of MyDB is protected by similar ASSERTIONS. A CONSTRAINT on the other hand guarantees the state no matter what action we take.

ASSERTIONS in RDBM:S and OO both have there merits, but are slightly different concepts and there is not a 1-1 mapping between the two.


Best way to create a login with hashed password?

The HASHED syntax only indicates that the specified password is already hashed.  SQL Server stores all passwords as hashes, it never stores them in plain-text.

With that out of the way, the syntax for creating a login with a hashed password is:

CREATE LOGIN administratori
WITH PASSWORD = 0x01003BA91A73BB0B9C06A647FC81F3B221EDA8E74AF33C20252F HASHED
    , DEFAULT_DATABASE = MDAfondation
    , DEFAULT_LANGUAGE = english
    , CHECK_EXPIRATION = OFF;

The password is specified as a binary value - this value is the "hashed" version of the password.  The hash above was obtained by the PWDENCRYPT function, like so:

SELECT PWDENCRYPT('P@ssw0rd');

Specifying the password as a hashed value is useful for scripting logins from one server to be deployed on another server when you need to ensure the passwords for each login are precisely the same on the new server.  A starting point for code that generates logins from an existing server might be:

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT N'CREATE LOGIN ' + QUOTENAME(sp.name) + N'
WITH PASSWORD = ' + COALESCE(sys.fn_varbintohexstr(
    CONVERT(VARBINARY(MAX), LOGINPROPERTY(sp.name, 'PasswordHash')
    )), '') + N' HASHED
    , SID = ' + CONVERT(VARCHAR(40), sys.fn_varbintohexstr(sp.sid), 0) + N'
    ' + CASE WHEN sp.default_database_name IS NOT NULL THEN N', DEFAULT_DATABASE = ' + QUOTENAME(sp.default_database_name) ELSE N'' END + N'
    ' + CASE WHEN sp.default_language_name IS NOT NULL THEN N', DEFAULT_LANGUAGE = ' + sp.default_language_name ELSE N'' END + N'
    ' + CASE WHEN LOGINPROPERTY('SomeLogin', 'DaysUntilExpiration') IS NULL THEN N', CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF' ELSE N'' END + N'
' + CASE WHEN sp.is_disabled = 1 THEN N'ALTER LOGIN ' + sp.name + N' DISABLE;
' ELSE N'' END + N'
'
FROM sys.server_principals sp
WHERE sp.type_desc = N'SQL_LOGIN'
    AND NOT (sp.name LIKE N'##%')
    AND NOT (sp.name = N'sa')
ORDER BY sp.name;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR (@cmd, 0, 1) WITH NOWAIT;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

I'm including that to show how the hashed password is intended to be used.

Creating new logins using a hashed password is not recommended since there is no way for SQL Server to confirm the password matches policies you've set such as minimum password length and complexity.


SSMS18 - Default File Location (Changed ssms.Application.pkgdef)

If using SSMS with a full install of SQL Server then there is a good answer on Stackoverflow.

If using SSMS as a standalone then the following should work

Locate C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions\Application
Open ssms.application.pkgdef with a text editor(e.g. Notepad)
Change the value of DefaultProjectsLocation
"DefaultProjectsLocation"="C:\MYNEWPATH"
Save the file


When you Partition a table, physical tables are created and when you query table with where predicates on partition only partition tables queried, you can also query a particular partition.


Can you trigger an action when a view is created?

You can use DDL triggers in Oracle.

I haven't actually done this myself, so here are some links:

http://www.dba-oracle.com/t_ddl_triggers.htm
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2153503
http://justoracle.blogspot.com/2006/10/ddl-triggers.html


calculate duration between intervals of 15 minute

3 Step approach:

generate the intervals.
match calls against overlapping ranges
figure the duration of each call within that range

create table #calls
(id int not null identity primary key,
starttime datetime,
duration int)

insert into #calls (starttime, duration) values
('2018-05-14 8:33:00', 45) -- 1 period
,('2018-05-14 8:42:00', 460) -- 2 periods
,('2018-05-14 8:55:00', 3500) -- 5 periods

--Assume table [Number] (N int) with rows 1 to 1 mil.
;
with Intervals as
(select top (150000) dateadd(mi, n*15, '2018-01-01') interval_start,
dateadd(mi, n*15+15, '2018-01-01') interval_end
from Number order by N)
select * ,

case
--Full 15 min
when starttime <= interval_start and dateadd(ss, duration, c.starttime) >=  interval_end
then 15*60
-- partial, overlapping start
when starttime <= interval_start and dateadd(ss, duration, c.starttime) <  interval_end
then datediff(ss, interval_start, dateadd(ss, duration, c.starttime))
-- partial, overlapping end
when starttime > interval_start and dateadd(ss, duration, c.starttime) >= interval_end
then datediff(ss, c.starttime, interval_end)
-- partial, wholly contained
when starttime > interval_start and dateadd(ss, duration, c.starttime) < interval_end
then duration
else null
end  interval_duration

from #calls c inner join Intervals
on dateadd(ss, duration, c.starttime) >= interval_start
and c.starttime < interval_end

go

drop table #calls


MySQL: What is SQL mode and why we use it


Status of objects owned & permissions granted by a (now)-disabled Windows Account

You'll want to change ownership of databases that are owned by the disabled user.  I'd recommend creating a dedicated account, either SQL Server authentication, or Windows Auth if you're not using SQL auth, to own each individual database.

You can identify databases that are owned by the individual in question using this query:

SELECT DatabaseName = d.name
    , PrincipalName = sp.name
FROM sys.databases d
    INNER JOIN sys.server_principals sp ON d.owner_sid = sp.sid
WHERE sp.name = '<DOMAIN\USER>';

Modify ownership on a database using ALTER AUTHORIZATION like this:

ALTER AUTHORIZATION ON DATABASE::<database_name_here>
TO <new_owner_name_here>;

Any SQL Server agent jobs that are configured to run "as" the disabled user will not run.

You can identify SQL Server Agent Job Owners using this query:

SELECT JobName = sj.name
    , OwnerName = sp.name
FROM msdb.dbo.sysjobs sj
    INNER JOIN sys.server_principals sp ON sj.owner_sid = sp.sid
WHERE sp.name = '<DOMAIN\USER>';

Change the job owner using this T-SQL:

EXEC msdb.dbo.sp_update_job @job_name = '<job_name_here>'
    , @owner_login_name = '<new_owner_name_here>';

Any stored procedures or other objects that are owned by the disabled account, where no one else has rights will obviously be problematic.


Detect if `pg_crypto` in Postgres was built with strong random feature enabled

--disable-strong-random is a PostgreSQL configuration option available in the ./configure script in the source code. You could check to see if your version of PostgreSQL and the associated contrib modules were built with it by using pg_config --configure and looking to see if that particular flag was passed in as part of the config process. Though as it says in the thread that was linked, no reasonable production platform would use that flag, so odds are that you've got a cryptographically random build.


JSONB may be easy to read, but it's complicated and inefficient to write into.

See for example this question: PostgreSQL update and delete property from JSONB column, on how it looks like. It's an order of magnitude harder than an update/delete with classic EAV tables.

Possibly when you'll have written the parts to append/merge/delete key/value pairs, the elegance or simplicity of that JSON-based design are going to seem much less obvious. As for the performance, I'm betting on it being much worse.

At the storage level, any write into one property in a JSON structure will require a rewrite of the entire column (and in fact of the containing row), with the same cost as if all properties changed.
This is not optimal in terms of I/O size and  pressure on vacuum.

This problem is mentioned on S.O in another question with some more links and references:

How to perform update operations on columns of type JSONB in Postgres 9.4

There's ongoing work to ease JSONB updates for the programmer. jsonbx provides functions and operators that can help with 9.4; presumably these will be integrated into PostgreSQL core in future versions. But the large I/O cost of a small update inside a large JSON object will remain.

jsonbx was demonstrated in a recent PG conference (youtube links):

Update and Delete operations for jsonb (part 1 of 2)
Update and Delete operations for jsonb (part 2 of 2)


Data warehouse server. How do you calculate RAM/CPU specifications?

Great question, and I did a session about this at TechEd a few years ago called Building the Fastest SQL Servers:

https://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328

In it, I explain that for data warehouses, you need storage that can provide data fast enough for SQL Server to consume it. Microsoft built a great series of white papers called the Fast Track Data Warehouse Reference Architecture that goes into hardware details, but the basic idea is that your storage needs to be able to provide 200-300MB/sec sequential read performance, per CPU core, in order to keep the CPUs busy.

The more of your data that you can cache in memory, the slower storage you can get away with. But you've got less memory than required to cache the fact tables that you're dealing with, so storage speed becomes very important.

Here's your next steps:

Watch that video
Test your storage with CrystalDiskMark (Here's how)
With 4 cores, you'll want at least 800MB/sec of sequential read throughput
If you don't have that, consider adding memory until the pain goes away (and caching the entire database in RAM isn't unthinkable)

Say you've got a 200GB database that you're dealing with, and you can't get enough storage throughput to keep your cores busy. It's not unthinkable to need not just 200GB of RAM, but even more - because after all, SSIS and SSAS really want to do their work in memory, so you have to have the engine's data available, plus work space for SSIS and SSAS.

This is also why people try to separate out SSIS and SSAS onto different VMs - they all need memory simultaneously.


Is it possible to use ExpressMaint with SQL Server 2012 (Express)?

Turns out there's a stored-proc version of ExpressMaint, which does seem to work on 2012, and is just as simple to use. Not sure now why I didn't just use that all along! :-)


Postgres - copy table INCLUDING CONSTRAINT doesn't include constraint

The documentation says about INCLUDING CONSTRAINTS (emphasis mine):

  Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. Also, no distinction is made between column constraints and table constraints — when constraints are requested, all check constraints are copied.

Foreign keys obviously fall into this 'other constraints' group, this is why the two FKs of yours are not copied over.

Interestingly, the documentation for version 9.3 has a different wording, not stating the above so clearly.


Change Data Capture SSIS not picking changes

There are some issues with SP3 in sql server 2012. As soon as I installed SP4 this issue went away.


Why CTX_DDL.SYNC_INDEX procedure doesn't work?

To perform this operation, the user who executes the SYNC_INDEX procedure has to be the owner of the index. So, in my case, I had to give the execution rights on the package with the CTXSYS user:
GRANT EXECUTE ON CTXSYS.CTX_DDL TO myuser;

Now, I can synchronize my index with myuser:
EXEC CTX_DDL.SYNC_INDEX('idx_docs', '2M');
SELECT SCORE(1), id, text FROM docs WHERE CONTAINS(text, 'city', 1) > 0;

I got the same result as the documentation:
  SCORE(1)         ID TEXT
---------- ---------- -------------------------------------------------
         4          2 Paris is a city in France.
         4          4 Los Angeles is a city in California.
         4          5 Mexico City is big.


Execute immediate call causes 'variable not in select list' error

As  mustaccio pointed out, types must be the same. You either have to change TTabList definition to be collection of just one field (see example below), or select : vstrQuery := 'select * ...
Everything else should work after fixing misprint with rowrype (should be rowtype) .  For instance, if you prefer to select just field1, you can do

set serveroutput on;
declare
  type TAB1_FIELD1 IS RECORD (field1 table1.field1%type);
  type TTabList  is table of TAB1_FIELD1 index by pls_integer;
  vtabList       TTabList;
  vstrQuery      varchar2(32000);
  vNum           number(10)     := 100;
  vStrs          varchar2(100)  := '''45'',''11'',''20'',''71''';
begin
  dbms_output.enable;
  vstrQuery := 'select field1
                  from table1
                 where field1 =   '|| vNum ||'
                   and field2 in ('|| vStrs ||')';

  --works fine with collections (at least starting from 10g)
  execute immediate vstrQuery bulk collect into vtabList;

  ....


How to search in blob binary wise, and how to export data binary wise

One way...

SELECT ... FROM ... WHERE HEX(blob_col) REGEXP '(..)*0038A7';

The (..)* skips over any number of pairs of the hex digits.

For dumping, see mysqldump --hex-blob (not good for csv)

See SELECT ... INTO OUTFILE


User Windows login name has been changed in AD yet session in SQL 2008 Profiler shows the old Windows login name

I guess I can't comment on your question since this is the first time I've used stack exchange but is this an AD group that the user is in?  Sometimes people say group when they mean SQL role so I just wanted to get some clarification.

The reason I ask is because if the user has a login on the server of domain\olduser when you change the name in AD to domain\newuser you have to go into SQL and change the login to domain\newuser.  I've done this and it works perfectly fine and you don't have to do anything as drastic as restart SQL.  I do not know if the account name will eventually change to the new one over time but my guess would be no.

I do not know, however, what happens when the user is just part of an AD group that has permissions and doesn't actually have a login on the server.  Hence the need for clarification so I can look into that.


Aggregating spend by customer where accounts can be re-assigned to different customers

You need a kind of best-match join like @TypoCubeᵀᴹ's approach, but depending on the actual data (i.e. number of rows per Account_no) this might create a huge intermediate result before aggregation (and needs a uniqueness contraint on table2(Account_no, Date)).

The same logic logic can be achieved by a different approach using UNION ALL and LAST_VALUE:

SELECT Customer_ID, Sum(spend)
FROM
 (
   SELECT
      -- assign the "previous" Customer_ID to the current row
      Last_Value(Customer_ID IGNORE NULLS)
          -- within a date first rows from table1, followed by table2
      Over (ORDER BY Date, x) AS Customer_ID,
      Spend
   FROM
    (
      SELECT
         1 AS x, -- flag indicating source table
         Customer_ID,
         Account_no,
         dt,
         -- need to cast to datatype of table2.Spend
         Cast(NULL AS ...) AS Spend
      FROM cust
      UNION ALL
      SELECT
         2 AS x,
         -- need to cast to datatype of table1.Customer_ID
         Cast(NULL AS ...),
         Account_no,
         dt,
         spend
      FROM spend
    ) AS dt
   QUALIFY -- only rows from table2
           x = 2
       AND -- only if table1 and table2 Account_no match
           Last_Value(CASE WHEN x = 1 THEN Account_no END IGNORE NULLS)
           Over (ORDER BY dt, Spend) = Account_no

 ) AS dt
GROUP BY Customer_ID
;


Index and rows placement within database files

Technically speaking, there is no single answer for "MySQL" itself, because MySQL has a pluggable storage engine architecture and the server itself is essentially unconcerned with and unaware of how the physical storage of data is accomplished.  Those details are handled by the storage engines.
The most relevant storage engine in MySQL is InnoDB.

Can there be different rows from different tables in the same physical page?

No, there can't.  In fact, tables do not typically even store tables in the same file, if you use the recommended configuration, where each table has its own separate tablespace file; however, even if innodb_file_per_table isn't enabled, pages aren't shared among tables.  By default the innodb_page_size is 16K bytes, although 8k and 4k sizes can be used, instead.

How does MySQL decide where to place a row in a certain page?
What about indexes placement too ?

There's only one place any particular row or index entry can go, because rows are stored sorted by the primary key columns and index entries are stored sorted by the indexed columns.

The pages that make up each table are arranged in a tree data structure called a B-tree index. Table data and secondary indexes both use this type of structure. The B-tree index that represents an entire table is known as the clustered index, which is organized according to the primary key columns. The nodes of the index data structure contain the values of all the columns in that row (for the clustered index) or the index columns and the primary key columns (for secondary indexes).
— http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-overview.html

Row data that doesn't fit the page, int he case of BLOB AND sometimes VARCHAR columns are stored outside the b-tree page, in an overflow page, referenced by a pointer to the off-page location.  The column may be stored fully off-page or partially (so that some number of prefix characters are available on-page to reduce the need for off-page accesses in some cases).  This behavior varies by row format and whether you are using internal table compression.  Table compression can improve performance when your system is I/O-bound but has available CPU cycles, since it reduces the overall disk I/O.

If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full. If the fill factor of an index page drops below 1/2, InnoDB tries to contract the index tree to free the page.
— http://dev.mysql.com/doc/refman/5.6/en/innodb-physical-structure.html

Of course, when new data is inserted, the entire contents of the tablespace aren't "moved down" to make room for additional pages.  The pages are linked, so how consecutive on disk the pages are stored relative to the internal ordering on each page depends on whether the data was inserted sequentially or randomly.
Significantly, the InnoDB buffer pool makes the underlying storage implementation less relevant -- as it should be, in an RDBMS -- less relevant, by providing an intermediate buffer between the physical storage and the higher layers.  This buffer is a fixed amount of memory, allocated at server daemon startup, that makes the physical disk storage less significant.

InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. Knowing how the InnoDB buffer pool works, and taking advantage of it to keep frequently accessed data in memory, is an important aspect of MySQL tuning.
The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.

The pool insert/update operations are, of course, committed to disk immediately, but they are not written directly to their place on disk in the pages where they are stored.  The changes to be written are logged (using a sequential disk write) and updated in-memory, on-page, in the InnoDB buffer pool, and then the pages that were changed in the buffer pool are flushed (written out) to their place in the tablespace file by a background thread.


Solved.
Before command CREATE USER you have to put:

use mysql;

and then it is replicated to slave server


Why use foreign keys if cascade action is not need?

The basic reason in my opinion for using constraints - and not only foreign key ones -  is to enforce integrity at the database level and do not rely on the possibly several applications that will use the database in its entire lifetime.

Defining that FOREIGN KEY enforces that every photo - to be inserted - has to be related to an existing user.
Defining NO ACTION enforces that a user cannot be deleted if any related photos are not deleted first.
Defining ON CASCADE DELETE enforces that the photos are deleted along with the user's deletion.
Defining a UNIQUE constraint on username does not allow two users with same name, etc...

In all of the above, the DBMS is entitled to maintain the integrity according to the constraints defined and not allow the database to fall into an inconsistent state.


That's a typical case of case misuse.

When creating the view, notice that "objectifsBoutique" is written with a uppercase B plus double quotes. This implies that any future reference to this object must include the uppercase B.

Yet in the error message « objectifsboutique » relation is not found it's a lowercase b so that name does not refer to the view, it refers to nothing, hence the error.

The solution is to either not use double quotes at all, or use them consistently.


How to Correctly Estimate the Average Number of I/O Operations my RAID 10 disk can offer?

tl;dr Yes you can do a rough and ready calculation but benchmarking is advisable if you need accuracy. There are many factors beyond raw disk performance that can influence IOPs, in particular the RAID controller.

If you have the technical specifications for the disk, the quick calculation to determine IOPs is: 1 / (average latency + average seek time) = random IOPs.

The contributory factors:

Average latency is the time taken for the disk sector requested to rotate under the drive head.
Average seek time is how long it takes the drive head to locate itself over the disk track requested.
Rotational speed (quoted in revolutions per minute) isn't used directly in the calculation but affects the above. It is also the headline figure quoted in any drive specification.

While there is variation across drive models, you can apply a rough and ready figure to each of the common rotational speeds.

7200 RPM ~75 IOPs
10K RPM ~125 IOPs
15K RPM ~175 IOPs

With the majority of enterprise class RAID controllers, your read performance for common RAID levels (1/10/5/6) will broadly be in line with IOPs * number of disks. Cheaper controllers may impose a penalty. Write performance however is significantly effected by the chosen level:

RAID 1 & 10 = IOPs / 2
RAID 5 = IOPs / 4
RAID 6 = IOPs / 6

For example, a RAID 10 array of 10 15k disks equates to:

(175 * 10) = 1750 IOPs read
(175 * 10) / 2 = 875 IOPs write

Of course, most workloads are a mix of read and write so its necessary to consider the r/w ratio to determine the capacity needed.


For a InnoDB only DB, which of these elements can be removed?

Please remove or change  the following:

skip-locking
skip-bdb
binlog_do_db  = include_database_name
binlog_ignore_db = include_database_name
default_storage_engine
default_storage_engine = InnoDB
thread_concurrency=8
sync_binlog=256
bulk_insert_buffer_size = 8M

Here is why:

binlog_do_db and binlog_ignore_db are only for including/bypassing the recording of SQL  targeted for specific databases.
skip-bdb is obsoloete since the BDB storage engine is no longer include in MySQL 5 starting with MySQL 5.1
If you install MySQL 5.5, InnoDB is the default storage engine. No need to include it in my.cnf
thread_concurrency can go since it only works with Solaris
sync_binlog does an OS flush of binlog changes every 256 binlog writes. Increase this if any long write bottlenecks manifest themselves
Your bulk_insert_buffer_size is set to the default. That needs to be way bigger (at least 256M). This would accommodate bulk loading of rows from LOAD DATA INFILE or heavily grouped row from the loading of mysqldumps.

There is so much to add based on the info in Drupal 7 Uses InnoDB, so do I need other configs?


Do I need to enumerate the columns used in a materialized view when creating the MV log?

No, you do not need to enumerate the columns used in a materialized view when creating the materialized view log.  In fact you cannot create a materialized view log using the primary key method and include all the columns because you would be including the primary key column itself, which is not allowed.

The concept of a materialized view log is to store the rowid or primary key of the data that changed.  The refresh can then look up the entire record from the table.  Adding specific columns to the WITH clause of the log explicitly records the data in the materialized view log itself.  If your materialized view query is filtering on these columns or joining on them it could speed up the refresh.

Theoretically if all the columns used in the MV are in the log, then it should be able to refresh without referencing the table.  The documentation does not indicate that this is being done.  It would be interesting to trace this to see.  Even if it does, the additional storage requirements may not make this route worth the trouble.

You should probably just create the materialized view logs without specifying a column list, like this:

CREATE MATERIALIZED VIEW LOG ON SCHEMA.TABLE_A
   WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

More Information:

SQL Snippets
Oracle® Database SQL Language Reference
Oracle® Database Data Warehousing Guide


How to handle table design with variable columns

You have pretty much 4 choices:

NoSQL - definition Every record is stored as a set of Key/Value pairs. It is very flexible and fast. Not all of the report writers out there support this style of storage. There are many example database implementations of NoSQL. The one that seems to be most popular right now, is MongoDB.

EAV - definition This is where you turn either the whole table or a portion (in another table) on its side. This is a good choice if you already have a relational database in-house that you can't move away from easily. The custom info table example you gave is a good example of an EAV table.

Standard tables with XML columns - Think of this as NoSQL meets relational tables. The data stored in an XML column can be any format that XML supports, including multiple correlated sub-data. For the columns that you know are going to be "regular" columns, they can be built as the appropriate type of column to store the data (LastName, Address, City, State, etc.).

Standard tables with lots of extra columns - You have a relational database, you can't use either XML or EAV, and NoSQL is not an option. Add lots of extra columns of each type. I would guess 30 or more varchar, 30 or more integer, 15 or more numerics. And once you use a column for a value, don't re-use it. And don't delete the column either.

Out of all of these solutions, my own opinion is that you will find either the NoSQL or the EAV approach to be the most successful with the least amount of refactoring your code and your schema.

You will have a situation where you collect data one year, not the next, and then collect it again afterward. Trying to get the older data updated with the correct information is problematic and expensive. Storage is neither.


How improve the performance of the query

A - Makes no sense. employee_id is unique, the query retrieves at most 1 row based on an equality filter, this should be a dead simple INDEX UNIQUE SCAN.

B - I don't think so. employee_id is unique, the number of distinct values equals to the number of rows. With huge gaps in a unique column, a height balanced histogram can still provide extra information for better estimates for a full/range scan, but this is an index unique scan.

C - Maybe, but very unlikely in a real scenario. The above query should return its result after a few block reads and gets (lets say, <10, index height + 1 table access). Instead, it took 453 disk reads and 797 gets. Maybe the query read the whole table instead of an index unique scan. In that case, decreasing MBRC would increase the cost of a FTS maybe up to the point, where the index scan becomes favored. Still, in a real scenario, with proper statistics, I doubt this would happen.

D - Maybe, but again, unlikely in a real scenario. "Decreasing" the index may reduce the cost of it. The clustering factor and number of leaf blocks do not affect the cost of an index unique scan. This leaves us with the index height, and yes, that may decrease, which means the cost decreases as well, but this difference is so small, I find it difficult to believe the database would choose a FTS with 453 reads over an index unique scan with an index that has a much lower BLEVEL (typically between 0..3, it is rare to have indexes with higher BLEVEL).

E - Makes no sense. Same as A.


You need to fully understand how undo works to troubleshoot this error. I recommend this blog post:

http://blog.oracle48.nl/wordpress/oracle-database-undo-space-explained/

You can use this query to see the current profile of your Undo tablespace(s):

SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*)
    FROM DBA_UNDO_EXTENTS
    GROUP BY STATUS, TABLESPACE_NAME;

ACTIVE extents contain uncommitted or currently-rolling-back
transactions.
UNEXPIRED extents contain transactions required to be
kept in the Undo tablespace to meet the undo_retention parameter
EXPIRED extents are transactions still in the Undo tablespace that
are older than the undo_retention parameter.

The database will delete the oldest expired extents to make room for new active ones. If there are no expired extents, it will attempt to allocate a new extent. If that means extending the datafile, it will do that.

If the datafile cannot extend, the database will delete some Unexpired extents to make room, violating the undo_retention value. Thus, the undo_retention parameter is not a guarantee, but a guideline that the database will attempt to adhere to as best it can.

Now, there are two common failure modes here:

If a query tries to read from a deleted extent, it will fail with ORA-01555 Snapshot Too Old
If the whole undo tablespace becomes full of active undo, transactions will
fail with ORA-30036 unable to extend segment in Undo tablespace

  total percentage of the snapshot's undo tablespace usage until it needs to auto-extend or maxes out

If the sum of Active and Unexpired bytes in DBA_UNDO_EXTENTS is close to the size of the relevant undo tablespace, you have a chance of experiencing an ORA-01555.

If the sum of Active bytes is close to the size of the relevant undo tablespace, you are likely to experience an ORA-30036 error.

  total percentage of redo logs usage until it overwrites itself

I'm not aware of anything you can change in the redo log configuration that will effect Snapshot Too Old errors. Where did you get this info?

  time left until undo retention is reached by the snapshot

It is very difficult if not impossible to do this accurately, because it depends on every other transaction in the database. If you subtract the query running time from the undo_retention parameter, you will have a good idea, but that's only assuming that the undo tablespace was big enough to meet the undo_retention value. Once that line has been crossed, all bets are off.


Why does MySQL flag a duplicate key where none exists?

I tried running your code in MySQL 5.7.12 for MacOS. I can't seem to duplicate this, but I found something interesting.

SQL-Fiddle has MySQL 5.7.17. You got the error in MySQL 5.7.13. Was anything with regard to dropping and adding a secondary index fixed between those versions ? I believe it was. Please note the Release Notes for 5.7.14, bulletpoint 21 under the subheading Bugs Fixed:

  InnoDB: An assertion was raised during rollback of an ALTER TABLE operation that dropped and added a secondary index. (Bug #22005726)

Question: What is a secondary index ? According to the MySQL Documentation for Clustered and Secondary Indexes right under the heading How Secondary Indexes Relate to the Clustered Index:

  All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

This show that a UNIQUE INDEX is just a secondary index. A secondary index would have a copy of the PRIMARY KEY attached to every index entry.

My assertion is that a bug was introduced in 5.7.13 that did not exist in 5.7.12 when it comes to dropping and creating an index on the same column(s) at the same time.

Look back at your ALTER TABLE command

mysql> ALTER TABLE my_table
        DROP INDEX `idx_new_id`,
        ADD UNIQUE INDEX `idx_new_id` USING BTREE (`new_id `);

You dropped an index with a specific index name (idx_new_id) and with a specific column list (just one column, new_id) and then said add a new index with the same name and the same column list but make it the index UNIQUE. The net effect is that the index was never really dropped and the UNIQUE attribute was simply attached to the already existing non-unique index. Then, under the hood, mysqld attempted to load data into the table which is already populated and an index that's still populated, thus creating a duplicate key error. Please note I am only asserting this. I would have to look at source code for 5.7.13 to see if this is indeed occurring.

Notwithstanding, 5.7.12 and 5.7.14 cannot reproduce this problem. This indicates that this strange condition was addressed when making 5.7.14.

In light of all my conjecture, here are my suggestions:

SUGGESTIONS

SUGGESTION #1 : Upgrade to 5.7.14 or above

SUGGESTION #2 : Change the ALTER TABLE to use a different index name

mysql> ALTER TABLE my_table
       DROP INDEX `idx_new_id`,
       ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

SUGGESTION #3 : Do 2 ALTER TABLE commands

mysql> ALTER TABLE my_table DROP INDEX `idx_new_id`;
mysql> ALTER TABLE my_table ADD UNIQUE INDEX `uniq_idx_new_id` USING BTREE (`new_id`);

GIVE IT A TRY !!!


Is it possible to mysqldump a subset of a database required to reproduce a query?

mysqldump has the --where option to execute a WHERE clause for a given table.

Although it is not possible to mysqldump a join query, you can export specific rows from each table so that every row fetched from each table will be involved in the join later on.

For your given query, you would need to mysqldump three times:

First, mysqldump all table3 rows with name in ('fee','fi','fo','fum'):

mysqldump -u... -p... --where="name in ('fee','fi','fo','fum')" mydb table3 > table3.sql

Next, mysqldump all table2 rows that have matching table3_id values from the first mysqldump:

mysqldump -u... -p... --lock-all-tables --where="table3_id in (select id from table3 where name in ('fee','fi','fo','fum'))" mydb table2 > table2.sql

Then, mysqldump all table1 rows that have matching table1_id values from the second mysqldump:

mysqldump -u... -p... --lock-all-tables --where="id in (select table1_id from table2 where table3_id in (select id from table3 where name in ('fee','fi','fo','fum')))" mydb table1 > table1.sql

Note: Since the second and third mysqldumps require using more than one table, --lock-all-tables must be used.

Create your new database:

mysqladmin -u... -p... mysqladmin create newdb

Finally, load the three mysqldumps into another database and attempt the join there in the new database.

mysql -u... -p... -D newdb < table1.sql
mysql -u... -p... -D newdb < table2.sql
mysql -u... -p... -D newdb < table3.sql

In mysql client, run your join query

mysql> use newdb
mysql> select table1.id, table1.level, table2.name, table2.level
       from table1 join table2 on table1.id = table2.table1_id
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

Give it a Try !!!

WARNING : If not indexed correctly, the second and third mysqldumps may take forever !!!

Just in case, index the following columns:

ALTER TABLE table2 ADD INDEX (table1_id);
ALTER TABLE table2 ADD INDEX (table3_id);
ALTER TABLE table3 ADD INDEX (name,id);

I'll assume id is the primary key of table3.


Transposing repeated rows of datetime into columns

I have counted the rows in the columns of AdmDate1 and found the
  maximum admission frequency is 35. Since readmissions more than 4 are
  not many, I can remove the rows with rn>4 for AdmDate1 and DischDate.
  by doing this, the number of columns to be created will be reduced to
  manageable numbers

Based on this reply i started looking for a solution which uses 4 of admdates and 4 of Dischdates.

So this works, but I don't dare look at the query plan

Create test data

create table AdmissionDates(id int, AdmDate1 date,  DischDate date)
insert into AdmissionDates(id,AdmDate1,DischDate)
VALUES(  10001 ,  '2012-10-16' ,  '2012-10-26'),
(10001 ,  '2014-06-15'   , '2014-06-18')  ,
(10001 ,  '2014-12-21'   , '2014-12-29')  ,
(10002 ,  '2013-02-14'   , '2013-02-20')  ,
(10003 ,  '2013-01-23'   , '2013-01-31')  ,
(10004 ,  '2012-11-15'   , '2012-11-19')  ,
(10004 ,  '2014-09-26'   , '2014-10-06')  ,
(10005 ,  '2014-12-12'   , '2014-12-23')  ,
(10006 ,  '2013-10-23'   , '2013-10-28')

select id,row_number() over( partition by id order by admDate1 asc) as rownum, AdmDate1,row_number() over( partition by id order by DischDate asc) as rownum2, DischDate
into #temp
from AdmissionDates;

The Query

select DISTINCT id,
                t2.admDate1 as admDate1,
                t3.admDate1 as admDate2,
                t4.admDate1 as admDate3,
                t5.admDate1 as admDate4,
                T6.DischDate as DischDate1,
                T7.DischDate as DischDate2,
                T8.DischDate as DischDate3,
                T9.DischDate as DischDate4
FROM
#temp t1
OUTER APPLY
(select admDate1 from #temp T2 where rownum = 1 and T2.id = T1.id) as T2
OUTER APPLY
(select admDate1 from #temp T2 where rownum = 2 and T2.id = T1.id) as T3
OUTER APPLY
(select admDate1 from #temp T2 where rownum = 3 and T2.id = T1.id) as T4
OUTER APPLY
(select admDate1 from #temp T2 where rownum = 4 and T2.id = T1.id) as T5
OUTER APPLY
(select DischDate from #temp T2 where rownum2 = 1 and T2.id = T1.id) as T6
OUTER APPLY
(select DischDate from #temp T2 where rownum2 = 2 and T2.id = T1.id) as T7
OUTER APPLY
(select DischDate from #temp T2 where rownum2 = 3 and T2.id = T1.id) as T8
OUTER APPLY
(select DischDate from #temp T2 where rownum2 = 4 and T2.id = T1.id) as T9;

DROP TABLE #temp;


Found this on Stackoverflow:  https://stackoverflow.com/questions/2577168/postgresql-table-creation-time


CREATE TABLE tbl (customerID varchar(10), status int);
INSERT INTO tbl VALUES
('XXX', 1),
('XXX', 2),
('XXX', 3),
('YYY', 2),
('YYY', 2),
('XXX', 1),
('XXX', 4),
('YYY', 4);

If there are a well-known different status values, you can use a PIVOT solution in this way:

SELECT customerID, [1], [2], [3], [4]
FROM
(
    SELECT customerID, status FROM tbl
) src
PIVOT
(
    COUNT(status) FOR status IN ([1], [2], [3], [4])
) pvt;

Result:

customerID |  1 |  2 |  3 |  4
:--------- | -: | -: | -: | -:
XXX        |  2 |  1 |  1 |  1
YYY        |  0 |  2 |  0 |  1

dbfiddle here

Or you can use SUM(CASE WHEN status = ...)

SELECT customerID,
       SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) Status1,
       SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) Status2,
       SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) Status3,
       SUM(CASE WHEN status = 4 THEN 1 ELSE 0 END) Status4
FROM   tbl
GROUP BY customerID;

Result:

customerID | Status1 | Status2 | Status3 | Status4
:--------- | ------: | ------: | ------: | ------:
XXX        |       2 |       1 |       1 |       1
YYY        |       0 |       2 |       0 |       1

dbfiddle here


It could be related to the value of foreign_key_checks variable. Try the following statement, and check the value:

mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+


Replication compatibility between minor MySQL versions

Get the .rpm for 5.7.10 and install that! Or else test the bejesus out of your proposed setup! From here: the manual is self-contradictory:

Sentence 1:

  MySQL supports replication from one release series to the next higher
  release series. For example, you can replicate from a master running
  MySQL 5.5 to a slave running MySQL 5.6, from a master running MySQL
  5.6 to a slave running MySQL 5.7, and so on.

Sentence 2: (this **is** the biggie)

  However, you may encounter difficulties when replicating from an older
  master to a newer slave if the master uses statements or relies on
  behavior no longer supported in the version of MySQL used on the
  slave.

Sentence 3:

  The use of more than two MySQL Server versions is not supported in
  replication setups involving multiple masters, regardless of the
  number of master or slave MySQL servers. This restriction applies not
  only to release series, but to version numbers within the same release
  series as well. For example, if you are using a chained or circular
  replication setup, you cannot use MySQL 5.7.1, MySQL 5.7.2, and MySQL
  5.7.4 concurrently, although you could use any two of these releases together.

Check out my first idea - I firmly believe that it's (by far) your best option - no compatibility worries (that's the great thing about Open Source software).


MySQL Documentation has SQL_NO_CACHE option:

  Two query cache-related options may be specified in SELECT statements:

  SQL_CACHE

  The query result is cached if it is cacheable and the value of the
  query_cache_type system variable is ON or DEMAND.

  SQL_NO_CACHE

  The server does not use the query cache. It neither checks the query
  cache to see whether the result is already cached, nor does it cache
  the query result. (Due to a limitation in the parser, a space
  character must precede and follow the SQL_NO_CACHE keyword; a nonspace
  such as a newline causes the server to check the query cache to see
  whether the result is already cached.)

  Examples:

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

You can set SQL_NO_CACHE as the default for your session by setting query_cache_type

SET query_cache_type = 'DEMAND';

or

SET query_cache_type = 2;

Afterwards, you must say SELECT SQL_CACHE to make it cache the SELECT


FATAL: terminating walreceiver due to timeout

You need to tune the wal_receiver_timeout parameter to your needs.


Mysql utf-8 behaves differently into different environments

I had missing in the connection string CharSet=utf8mb4 in the bad env

Server=10.5.0.5;Database=db;Port=3306;Uid=root;Pwd=root;CharSet=utf8mb4


I have drop this diskgroup from target host and then with  " oracleasm deletedisk "

This was a mistake. You removed the metadata describing the content. You should have just simply detached the disks, then attach to the new host, leaving the disks intact.


Transaction publication with merge publication

For anyone who will encounter a similar issue here's what worked for me:

Run the following on your merge publication database :

    EXEC sp_changemergearticle

    @publication = 'MyPublication',

    @article = 'MyArticle',

    @property = 'published_in_tran_pub',

    @value = 'true';

For more info about the problem read this article :https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/publish-data-and-database-objects?view=sql-server-2017


Update MySQL with Solaris 11 IPS Package

There's a couple things that may be going on here (possibly both):

In a fresh install, the default package repository is set to http://pkg.oracle.com/solaris/release/ which contains the packages from the initial Solaris 11.3 release in 2015.  To get updates you need to switch to the /support repo, which is only available to customers with a current support contract.  See How to Update Oracle Solaris 11 Systems From Oracle Support Repositories for instructions.
The packages provided with Solaris, like MySQL, default to being updated only as part of an OS update, not individually.  You may be able to unlock them to allow changing versions independently when needed - this is described in More Tips for Updating Your Oracle Solaris 11 System from the Oracle Support Repository under "Updating an Individual Package" or in Relaxing Version Constraints Specified by Constraint Packages.


mongo user can not connect from bash on localhost

MongoDB auth setting is false as a default.
First of all, you should create the root user.
//create new user with the root role (also, named root):
use admin
db.createUser({
  user: "root",
  pwd: "pass",
  roles : [ "root" ]
})

After that, you should start your mongod service with the auth option, it means authentication is enabled.
mongod --auth

Also, you can add your mongod.conf file this parameter :
security:
    authorization: enabled

And start your mongod with the command which is below.
mongod --config /etc/mongod.conf //your mongod.conf path

Check this out for more info.


How does DBCC SHRINKFILE() affect backups?

Per Microsoft Support
http://support.microsoft.com/kb/272318, SQL Server shrinks the log file by removing as many VLFs as it can.  It does so while trying to hit a target size without messing up your backup chain.

However, if you do use TRUNCATE_ONLY, the sequence gets messed up, so you'll need to either do that right before your scheduled backup or do full backup right after that.


Is SQL Server Replication transactionally 'safe' across all published tables?

Well, there are three different types of replication, but they are all transactionally safe, to a degree.
There are three types of replication in SQL Server:  Transaction Replication, Merge Replication, and Snapshot Replication.
Transaction Replication
This type of replication transfers each modification that occurs in the master to each of the subscribing databases.  This type of replication insures that you will get each changes as it occurs (more or less) on the master system.
This will perform like you describe.  There may (will) be a time delay while the replication is moved from the master database to the subscriber database, though.
Merge Replication
This type of replication is essentially Transaction Replication, but it allows for conflicts.  (Obviously, there's a bit more to it than that, but that's the essence.)
So, just like Transaction Replication, you will get the data on each subscriber as it changes (plus a little time lag).
Snapshot Replication
This is different from the previous two in that the database does not replicate individual changes.  Instead, it takes periodic snapshots and sends down the snapshots.
This is transactionally safe in that you will only see completed transaction being replicated to the subscribers.  However, you will not see this in real time (or anything like real time).

So, yes, replication is transactionally safe in that you will only see completed transactions.  Any transactions that are partial or rolled back will not be applied to the subscriber databases.
However, they are not transactionally safe in that replication does not maintain perfect consistency between databases.  (Thus breaking ACIDity and therefore transactions.)  For this, you will need distributed transactions (which is an entirely different concept).


How to use WITH inside IF statement in PostgreSQL

IF does not exist in the SQL language (which is why you get a syntax error) but it's available in plpgsql, the procedural language.
With plpgsql, you could write something like
CREATE OR REPLACE FUNCTION public.get_something(myTextFlag VARCHAR(20)) RETURNS TABLE
(
rNum BIGINT,
Colmn1 Text
)
AS $body$
BEGIN
IF (myTextFlag = 'FirstPart') THEN
  RETURN QUERY <insert your 1st query here>
ELSE
  RETURN QUERY <insert your 2nd query here>
END IF;
END
$body$
LANGUAGE plpgsql
SECURITY DEFINER
 STABLE;

Your 1st and 2nd queries must return the same structure that also must match the function definition, but aside from that, they can be completely different inside.


pg_upgrade from 9.3.5 to 9.6 with PostGIS (Windows)

PostGIS 2.2 isn't built for PostgreSQL 9.6 on Windows
PostGIS 2.3 isn't built for PostgreSQL 9.3 on Windows.

You have two options

Do a hard upgrade.
Build for 9.6 PostGIS 2.2, then do a soft upgrade

I would do a hard upgrade. Seriously 230 GB is not much to bring in. You'll live. (I think).

  My guess is that it's monumentally unlikely that the entire PostGIS-enabled world can't update their PostgreSQL versions in a reasonably straightforward (i.e., non-pg_dump) way.

They do have a method for doing a quick and easy upgrade, but it requires the server to have both versions of PostGIS. Then you can run,

ALTER EXTENSION postgis UPDATE TO "2.3.2devnext";
ALTER EXTENSION postgis_topology UPDATE TO "2.3.2devnext";

That method isn't readily available to you because I can't find anyone who has compiled PostGIS 2.3 for MS Windows PostgreSQL 9.3, but strictly speaking it's possible.

If you were using Ubuntu/Debian this wouldn't be a problem as a precompiled version of PostGIS is already in the repository for all versions of PostgreSQL also in the repository.


How can I make PQExecPrepared() not hang if the network connection has been lost?

When the remote PostgreSQL server goes down, the remote OS knows that and tells your OS that the connection has closed, by sending a TCP FIN.  When the network goes down, there is no one who knows what happened (or at least no way for them to tell you what happened) so no TCP FIN.  So you have to wait for the TCP connection itself to give up.

If you want more control over this, you can use PQsendQueryPrepared instead.


on Amazon system you can use following steps to setup replication for MySQL

Choose a low/off production window
Note Master co-ordinate using show master status
You may consider using flush tables with read-lock, just to make sure you are sure that your co-ordinates doesn't change while you are creating snap shot
take snap shot and restore
Change server-id and relay log and issue change master

Hope it helps


Total Server Memory and Target Server memory Value same

But in my case the values are same :

You cannot and should not come to conclusion that SQL Server is facing memory pressure by just looking at one perfmon counter, this is just not correct. You have to rely on other perfmon counters which I am sure you are aware.

Now for your question, since both are same this means the SQL Server memory requirement is balanced as of now and its quite likely that SQL Server is not facing memory pressure. In most cases I have seen that on steady system target and total server memory are equal and I have always found out that when they are same it means currently there is no memory pressure.


Finding aggregated values based on dates in another table

Something like this?
SELECT emp.Employee_Id,
       emp.Absence_date,
       SUM(FTE) AS FTE
  FROM #EMPLOYEE_ABSENCES AS emp
  JOIN #employee_Positions AS pos ON absence_date
                             BETWEEN position_start_date AND position_end_date
                                 AND pos.Employee_Id = emp.Employee_Id
 GROUP BY emp.Employee_Id,
          emp.Absence_date
 ORDER BY Absence_date ASC


Can CLR execution continue after 'THROW' is encountered in T-SQL?

It appears that this behavior is specific to connections using "Context Connection = true;". I have tried to get around this by writing out the try-catch-finally structure instead of using the using macro, but that had no effect.

A Microsoft Connect bug was filed almost 3 months ago regarding this behavior. In that Connect bug it was speculated that THROW raises a ThreadAbortException that cannot be reset via the Thread.ResetAbort method. I tried explicitly catching this exception, and even calling Thread.ResetAbort when catching the generic Exception, but to no avail. So I am not sure if a ThreadAbortException is really being called, but regardless, the current process ends immediately. And, it even displays the error as a T-SQL error and not a .NET Framework error, which is odd.

The reporter of that Connect bug tested on SQL Server 2014, and I tested on SQL Server 2012. I cannot say for certain if this behavior still exists in SQL Server 2016, but I strongly suspect that it does given that there doesn't seem to be much effort (if any) put into fixing and/or improving SQL Server's CLR Integration (i.e. SQLCLR). So for now, and likely the foreseeable future, there are three possible work-arounds that have worked for me:

Replace THROW with RAISERROR(); RETURN; for procs that might be called by SQLCLR objects. The only downsides I can think of are:

Can't set a custom ERROR_NUMBER on-the-fly
Can't re-throw to send the original, system-defined ERROR_NUMBER to the caller

Wrap your query in a T-SQL TRY / CATCH that uses RAISERROR:

  String _Query = @"
BEGIN TRY
  EXEC dbo.TestThrowSql @CauseError;
END TRY
BEGIN CATCH
  DECLARE @ErrorMessage NVARCHAR(4000);
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage, 16, 1);
END CATCH;
";

The benefit here is that you can continue to use THROW and it will work as expected when called by non-SQLCLR app code, by SQLCLR app code that is not using the Context Connection, by other Stored Procedures, in SQL Agent jobs, etc. That and you don't have to go back and edit any existing Stored Procedures :-).
Switch to using a regular / external connection string. The downsides here are:

The Context Connection is much faster.
The Context Connection can be done in a SAFE Assembly.
The Context Connection has access to session-based items (i.e. CONTEXT_INFO, local temporary tables, etc.)


How do I navigate PostgreSQL documentation to find a list of all functions that work on a specific type?

There are a few ways, but let's try and address all three of them..

man pages

There are no man pages that will address these questions. There is a lot of PostgreSQL documentation but currently there is nothing like that nor perldoc for PostgreSQL. The docs covering this are only distributed as HTML -- which you can still view with a terminal browser (like elinks, lynx, w3m etc). Most distributions package this up separately, in Ubuntu you'd do this.

sudo apt-get install postgresql-doc
elinks /usr/share/doc/postgres*/html/index.html

psql

To use PSQL for this, you'll need to use \dfS. You can see a listing of all the psql commands with \?. After you do \dfS just search for the type. In the event \dfS is using a viewer that wraps on terminal width, you can tell it not to wrap by setting using `\setenv, most pagers accept arguments by their environmental variable so to use LESS without wrapping you can use

\setenv PAGER less
\setenv LESS -S

All of this is buried in the man page for psql. Most of the output of \? the in-shell psql help is better explained in that man page too, for instance this is what \? shows

\df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions

And, this is what man psql shows,

  Lists functions, together with their arguments, return types, and function types, which are classified as “agg” (aggregate), “normal”, “trigger”, or “window”. To display only functions of specific type(s), add the corresponding letters a, n, t, or w to the command. If pattern is specified, only functions whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If the form \df+ is used, additional information about each function is shown, including security classification, volatility, owner, language, source code and description.

  Tip To look up functions taking arguments or returning values of a specific type, use your pager's search capability to scroll through the \df output.

PostgreSQL catalog

First you need to have an idea of what to query. That's the complex party. In the above, we know that we're using \dfS, so we can get a starting point with psql like this:

psql -E -c"\dfS" -d myDB | less -S

This will start off with with the query,

********* QUERY **********
SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;
**************************

Now, we now we p.proname as "Name", and pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types" and we can put that together to get a simplified version of \dfS which shows just what you want,

SELECT p.proname AS "Name",
  pg_get_function_arguments(p.oid) AS "Argument data types"
FROM pg_proc AS p
WHERE pg_get_function_arguments(p.oid) like '%jsonb%';

A little more research into pg_proc and we can see

  proargtypes - oidvector - pg_type.oid - An array with the data types of the function arguments. This includes only input arguments (including INOUT and VARIADIC arguments), and thus represents the call signature of the function.

This would allow us to do a very specific query to pg_type over oid

SELECT proname AS "Name"
FROM pg_proc
  INNER JOIN pg_type ON ( pg_type.oid = ANY(proargtypes) )
WHERE typname = 'jsonb';

And, that gets us exactly what we want.

information_schema

While maybe not applicable here, generally speaking you can find some information in the information_schema. This generic interface doesn't support everything, it's slow, and psql doesn't use it internally. I did not see this information in information_schema.routines at all.

Some of this answer was shamelessly inspired from this question but aimed to be a more friendly resource.


I have a few general points.

Firstly, your diagram is a bit confusing! There are lines which merge into each other and the source and destination tables aren't clear.

Secondly (and this is a matter of opinion), you might want to use <table_name>_id as your PRIMARY KEY (e.g. ticket_id). It removes ambiguity, makes debugging easier and SQL easier to write and particularly read. My own personal convention is to use lower_case_with_underscores_id (and lower case for identifiers generally) and UPPER CASE FOR SQL. This makes code a pleasure to read and almost self-parsing.

Also, take a look here, it's from databaseanswers.org which can be a good first port of call for database modelling projects! Try and make your diagram look a little bit more like that!

Finally, you can download F/LOSS applications for bug-tracking - there's more of them than you can shake a stick at! Again, personal recommendation, you should look at Fossil - it's by D. Richard Hipp, the same guy who's given the world SQLite!

I've watched some podcasts by him and he's impressive! At least take a look at his schema - you can be sure that thought went into it! It's BSD licenced, but I'm not sure if your professor/lecturer would be happy about you claiming his work as your own, but if you clearly refer to it, that should be OK! :-)

Be prepared to make a lot of changes - as Helmuth Von Moltke said, "no plan survives contact with the enemy" - best of luck with your project!


Changing compatibility level from 2008 to 2016

For this number of jumps in compatibility level (or if you are being appropriately paranoid with a production application, for any jumps in compatibility level) a full application regression test is the only way to be absolutely sure.

Even if you have ensured that all the matters listed in the release notes either don't affect you or have been mitigated, you might find instances where code is relying on officially undefined behaviour which has changed (changes like this may not appear in release notes). For instance, when moving some of our work from 2005 to 2008r2 we experienced problems with a change in how some (admitedly odd) queries using FOR XML generated their output.


It actually looks like you're trying to get the nearest 500000.

Look here: https://stackoverflow.com/questions/1702080/round-to-nearest-5-in-sql-server

In your case, use ROUND(value1/500000.0,0)*500000


pg_xlog not recycling WAL files on slave server

The problem seems to have been resolved.

Apparently I had hardware issues on the master server.
I was able to perform full pg_dump and re-index my DB so I was pretty sure I did not have any data integrity issues.

But when looking at the master server logs after I've enabled log_checkpoints in the config - a few minutes before the slave server stopped performing checkpoints I saw the following message:

IDT ERROR:  failed to re-find parent key in index "<table_name>_id_udx" for split pages 17/18

After seeing that - I decided to switch hosting provider and moved my DB to a new server.
Since then (almost a week now) - everything has been running smoothly replication and checkpoints are running as expected.

I really hope this will help other people - but when something like this is happening - always be advised that this issue might be caused by data integrity/hardware issues.


I think you have gone too far for a transactional system, however if you are designing a data warehouse, your approach is correct.

If you are designing and OLTP system, I would recommend that you replace "PeriodID" in your "TaskRecord" table with "CreateDate" or something similar, so it implies what you are actually storing. You have lost the definition of what you are storing by having a column named "PeriodID" in your TaskRecord table in addition to potentially losing accuracy (as it is a date, rather than a datetime).

If you are designing and OLAP system however your approach is correct - your Model.Period table is a Dimension table and your Model.TaskRecord table is your fact table (though it appears to be lacking measures). I don't believe you are designing an OLAP system however so will not mention more.

In respect to normal forms, I would suggest you have decreased from 3NF rather than gone beyond. This is because you have introduced a dependency on insertions into the Model.Period table in order to maintain the ability to insert into your Model.TaskRecord table. A clear goal of normalisation according to Codd is "To free the collection of relations from undesirable insertion, update and deletion dependencies". Essentially date/time has no dependency if used in a field which stores a date/time value. However you have introduced a dependency on a suitable date record exiting in the Period table by using your original design.


SQL Server database backup status notification

Backups and Restores should be your Nbr 1 alerts, a failure here can trigger a "CV modification Event". There are several ways to send alerts on a job failure, here you have a screen shot of a job Notifications window. You can use email, pager (do someone still uses this?), net send, write to app log and even delete the job when it finishes (for one time job execution for example).

Email is very common, you just need to notify when the job fails, not every time it succeeds or alike, to avoid unnecessary emails. You will need to enable and setup your Database Mail (if not already done), add an operator (who will receive these emails?), then check Email option and select that operator.

Same with the Alert window, at the end you will end up using an email to notify. This other answer explains this option really well.

But you comment that email is not an option. If you have some kind of monitoring tool for your servers, you can use the Write to Windows Application Event Log and then let that app warn you through whatever means they have configured.


NTFS is, in my experience, typically pretty good about cleaning up fragmentation as files grow. I would wait until you actually have the large files and have run defragmentation on the volume before worrying about it.

Just because small files tend to be easily fragmented does not mean that large files will be a problem.


Rename foreign keys en masse on SQL Server 2017

Stealing from myself (please see the caveats and comments on that article):

CREATE PROCEDURE dbo.ImplementNamingStandard
    @SELECT_Only        BIT = 1,
    @ForeignKeys        BIT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql NVARCHAR(MAX), @cr CHAR(2);
    SELECT @sql = N'', @cr = CHAR(13) + CHAR(10);

    DECLARE @TableLimit TINYINT, @ColumnLimit TINYINT;
    SELECT @TableLimit = 24, @ColumnLimit = 10;

    IF @ForeignKeys = 1
    BEGIN
        SELECT @sql = @sql + @cr + @cr + N'/* ---- Foreign Keys ---- */' + @cr;
        SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N'''
          + QUOTENAME(SCHEMA_NAME(schema_id)) + '.'
          + REPLACE(name, '''', '''''') + ''', @newname = N''FK_'
          + LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit) + '_'
          + LEFT(REPLACE(OBJECT_NAME(referenced_object_id), '''', ''), @TableLimit) + ''';'
        FROM sys.foreign_keys
        WHERE is_ms_shipped = 0;
    END

    SELECT @sql;

    IF @SELECT_Only = 0 AND @sql > N''
    BEGIN
        EXEC sp_executesql @sql;
    END
END
GO


How to get a JSON array without JSON_ARRAYAGG in MySQL

Finally I've found a solution. I create a string array using CONCAT and GROUP_CONCAT functions and then cast it to JSON.
SELECT JSON_REPLACE(
    data,
    '$.numbers',
    IF(
      (
        SELECT COUNT(number) FROM numbers
        WHERE JSON_CONTAINS(data, CAST(numbers.id AS CHAR), '$.numbers') = 1
      ) > 0,
      (
        SELECT CAST(CONCAT('[', GROUP_CONCAT(CONCAT('"', number, '"')), ']') AS JSON) FROM numbers
        WHERE JSON_CONTAINS(data, CAST(numbers.id AS CHAR), '$.numbers') = 1
      ),
      NULL
    )
) AS json FROM foo;


mysqld is generating a lot of disk IO writes. How can I figure out why?

Take a look at the mysql processlist. mysql -p -e "show processlist". Or even a "show full processlist". It will give you a list of currently running queries.


MySQL 5 on Mac 10.5: "problem connecting to the database"

I have found the problem:
Somehow, MySQL decided to create mysql.sock in /private/tmp rather than in /var/mysql.

So, my solution was to create a symlink:

cd /var
sudo mkdir mysql
sudo ln -s /private/tmp/mysql.sock mysql.sock

Now everything works fine.


The reason why your query is so slow to execute, is because MySQL has to create a temporary table to sort t2 for grouping.

However if you create an index on the grouping column, MySQL doesn't need to create a temporary table, it can just use the index to access the rows in the correct order.

In this case I would suggest to use the primary key for this, saving the overhead of an additional index on disk. The data will be saved in the correct order already.

On t2 set the primary key to (col2, col1) (order is important here). t1 would just have the primary key as (col1).

To run your query try adding an ORDER BY NULL clause as well. This will prevent MySQL from doing an implicit order by during grouping.

You will probably still hit the 10 min timeout - depending on the hardware you use. You might try to use the SELECT ... INTO OUTFILE (docs) syntax to prevent sending the 1.5 million rows to the client. This query will then still finish even with your client connection timing out.

To save some disk space you should also think about adding an integer column as the primary key to t1 and reference this key only in both columns of t2, instead of saving the whole varchar twice there as well.


ON DUPLICATE KEY UPDATE faster than UPDATE

I know it is not easy getting an execution plan for an update from MySQL since it only provides those on SELECT statements. But the clue might be in the order in which the records are updated, the evaluation of a WHERE which contains a IN with a large amount of static data, as well as the amount of connected reads and writes, intermediate caching,  associated with that.

The statement

UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000);

is a type of statement we try to avoid when updating larger databases since the parser seems to go wild on them from time to time. IN ( a,b,c,...,ZZZZ ) for me has become a coding style only suitable for very small item numbers in the IN data.
I am working on a open source project where I frequently run into what I call "remote minded join", the second half usually looks exactly like your problem.

SELECT id FROM all_our_customers WHERE happytospam=1 AND LENGTH(email) > 6;
...
Storing result on client side as string like
LOOP over results
$all_ids += ",$next_result";
END_LOOP
$all_ids = SUBSTRING($all_ids,1);
ending up with a string like
"1,2,3,4,5,8,10,100,1000,...,100000" in $all_ids
...
UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN ( $all_ids )

While the first part usually executes lightning fast, the second part takes forever, which is what you describe as well.
These queries can usually be sped up EXTREMELY by rewriting them as:

UPDATE humans_we_respect,all_our_customers
SET humans_we_respect.expressed_interest=1
WHERE all_our_customers.id = humans_we_respect.id
AND all_our_customers.happytospam=1
AND LENGTH(all_our_customers.email) > 6

We also used

UPDATE humans_we_respect
SET expressed_interest=1
WHERE id IN (
SELECT id
FROM all_our_customers
WHERE happytospam=1
AND LENGTH(email) > 6
)

which performed better than the original but not as good as my suggested version.

This all is assuming that you use proper indexes with primary on ID and combined multi column indexes where multiple columns are frequently used together or have a good significance together and are usually present in your queries.

The Clue is that large amounts of static values in IN clauses increase the execution time nearly exponentially on queries with many matching records, since they basically do NOT USE ANY INDEXES OR OPTIMIZATIONS and usually end up in full table scans, in which IMHO the execution will examine every record/line in your table by comparing it with every item in your IN() list ONE by ONE.

The statement like

INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;

however is using the index to locate the record and then updates it, even so not intended for this use, it will run much better due to the use of a index on ID if there is one and will only do a index-lookup for the record rather than thousands of comparisons!
Working directly with two tables might however be faster if the list of IDs is derived from another table on the same server, there are more optimizations that can be used plus you do not have to transfer data out-of and into the mysql server process.

Just as some extra info:

UPDATE humans_we_respect SET expressed_interest=1 WHERE id='1' OR id='2' OR ...

is a good technique to optimize IN() queries with very low counts of elements since it will create a parallel index query for every element, which is great for the first elements but will decrease in performance with more elements a lot, and at some point hit the limit of the parser for optimization (IMHO it might be 255 elements in one query) at which point it will grind to a snails pace again...


What is the difference between an INNER  JOIN and an OUTER JOIN ?

An inner join will only select records where the joined keys are in both specified tables.
A left outer join will select all records from the first table, and any records in the second table that match the joined keys.
A right outer join will select all records from the second table, and any records in the first table that match the joined keys.

In your first example, you will only return a list of users and telephone numbers if at least one telephone record exists for the user.

In your second example, you will return a list of all users, plus any telephone records if they are available (if they aren't available, you'll get NULL for the telephone values).


How to copy a table during replication in MySQL?

replicate-ignore-table and the related configuration options are problematic at best.  Not because they don't do what they're supposed to do -- because they do -- but rather because they have side effects that require a deeper understanding of MySQL replication.
The problem you have stems from the way the events are replicating.  MySQL can replicate table data changes using two different mechanisms, statement-based and row-based, based on the setting of the global configuration variable binlog_format.
STATEMENT -- the actual queries are replicated from server to server, with each server executing the query and modifying its data set.
ROW -- the actual queries are not sent, but instead, tightly-packed row images of the changes actually caused by each query are sent.
MIXED -- the server chooses on a query-by-query basis how to replicate the events.
Importantly, when a query is replicated as a statement event, triggers on the affected tables fire on the slave in order to make the same changes to other tables that would have been made on the master.  When a query replicates as a row event, triggers do not fire on the slave.  Instead, the master server sends over additional row images from the affected tables so that the additional impacted tables also get updated to match the way they were on the master.  The same thing is true when procedures are called -- either the individual statements in the procedure are replicated and have to be valid and execute on the slave (statement-based replication), or the rows changed by the procedure will be replicated (row-based replication).
The scenario you described cannot work unless your master server's binlog_format is set to ROW, because the queries running on the slave have to have access to the "user" table data.
Once that is set, if it were me, I would remove the replicate-ignore-table from the configuration, and instead provision the user table on the slave with the BLACKHOLE storage engine, which discards data sent to it, returning "success" on inserts and 0 rows found/affected on updates and deletes.
However, keep reading, because you are doing this the hard way.
The solution is much simpler: alter the table to move the confidential data to the far right side of the table, and then drop those columns from the slave's copy of the table.  No, really:

Source and target tables for replication do not have to be identical. A table on the master can have more or fewer columns than the slave's copy of the table.
You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:
Columns common to both versions of the table must be defined in the same order on the master and the slave. (This is true even if both tables have the same number of columns.)
Columns common to both versions of the table must be defined before any additional columns.
— http://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

I have linked to the documentation from MySQL 5.6 since that's the current GA version, but this feature is available as far back as 5.1.21.  When doing things this way, you would still be advised to switch your master to binlog_format = ROW.


The answer may be referring to cardinality as in the number of rows within the table.  This link has an explanation of each definition of cardinality in a database:

http://www.programmerinterview.com/index.php/database-sql/cardinality-in-sql/


You could alter the trigger with brute force INSERTs as follows:

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  DECLARE insert_style INT;

  -- I'm interested in two fields only and if they were changed only

  SET insert_style = 0;
  IF new.y != old.y THEN
      SET insert_style = 1;
  END IF;
  IF new.country != old.country THEN
      SET insert_style = insert_style + 2;
  END IF;

  CASE insert_style
      WHEN 1 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y);
      WHEN 2 THEN
          insert into z_lot values
          ('','tbl1',new.id,'country',old.country,new.country);
      WHEN 3 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y),
          ('','tbl1',new.id,'country',old.country,new.country);
  END CASE;

END


All good, future-proofed databases are normalised to some extent. Normalisation is described by Louis Davidson ~ Pro SQL Server 2008 Relational Database Design and Implementation as

  The process of normalization is really
  quite straightforward: take entities that are complex and extract
  simpler entities from them. The process continues until every table in
  the database represents one thing and every column describes that
  thing.

So based on your example I would imagine that the Snap Chat database (assuming that we are talking about OLTP model and not using NOSQL / BIGData) would have multiple entities, with each entity containing relevant data and be related to other entities via the appropriate relationship i.e. 1..Many, Many to Many etc. For example, a person entity might contain UserName and Password and be related to a loginToSite entity via a 1..M relationship. I suggest a 1..M relationship because one person can log in to the site multiple times but each login to site record will only be related to one user. If all attributes were stored in the a single entity then in this example the UserName and Password information would be duplicated per Login record and break the rules of normalisation. This would also lead to a database usability nightmare!!

Depending on business rules, and these would be questions you would have to ask snap chat. One user record might be related to one or many photo records or maybe one user can be related to one or many photo records and one photo record can be related to one or many user records. In this case, there would be a many to many relationship.

When thinking about database design it's important to first work out the conceptual design by talking with end users, reviewing reports / spread-sheets (anything containing data used by the end user) and generating an ERD. Then once the relationships have been agreed, all data to be stored captured and the ERD modelled you can move onto the physical side of things and work on performance, choosing data types, deciding on indexes etc...