Service Master Key inaccessible, SSRS key only one backed up

For anyone who comes across this and has a similar issue...

I was able to successfully change the SQL Server Service login to the original account used when the service master key was created, then successfully back up the key.  Therefore I did not need to regenerate the service master key and restore the SSRS key.  I still don't have an answer - if the saved SSRS key could have been restored following the service master key regeneration.

If anyone learns the answer to that - when you have backups of secondary keys, but not the service master key - will you have to regenerate the secondary keys also, or be able to use your backups.... please post the answer here for future reference.


Postgres restore from WAL-E backup never finishes

standby_mode = on

Tharrr's yer problem. You've configured it as a continuous (warm or hot) standby.

standby_mode in the manual:

  standby_mode (boolean)

  Specifies whether to start the PostgreSQL server as a standby. If this parameter is on, the server will not stop recovery when the end of archived WAL is reached, but will keep trying to continue recovery by fetching new WAL segments using restore_command and/or by connecting to the primary server as specified by the primary_conninfo setting.


On Unix, when psql is used without the -h option or a PGHOST variable set, it uses Unix local domain sockets, which corresponds to local in the TYPE column in pg_hba.conf and no ADDR column at all.

That differs from putting localhost in the host field of pgAdmin's connection dialog. This one corresponds to host in the TYPE column and 127.0.0.1 in the ADDR column or whatever localhost is resolved to by the TCP stack (with IPv6 it could be ::1 for instance).

In general, this is what is confusing when psql and pgAdmin connection attempts do not trigger the same rules in pg_hba.conf. If that's the case in this question, try to leave the host field in pgAdmin empty.

On top of that, Windows does not have Unix local domain sockets so psql on Windows defaults on localhost as if -h localhost was used.


how to create invalid utf-8 characters in MySQL

Why not changing the text strings to a binary encoding or non-utf8 encoding, or to use binary strings (varbinary, blobs)? That will allow you to store all invalid values that you want.

Edit: To be more specific: change the type to varchar(255) charset binary, that may cause you all kind of charset problems .

@RLF, funny that you mention Wikipedia, text on the wikipedia is stored in binary format!


Create Nested XML in a Sql Server Indexed (Materialized) View

Short answer: You can't.

Long answer:

There's a few things going on here.

First, you might be underestimating the power of XML AUTO. It will provide a certain amount of nesting "automatically". Your provided example, in fact, could be handled without the nested XML generation.

Let's make some test tables and data:

USE tempdb;

DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;

CREATE TABLE T1
(
    Col1 int,
    Col2 varchar(50),
    [Col] char(1)
);

CREATE TABLE T2
(
    [Col] char(1),
    Col3 varchar(50),
    Col4 varchar(50)
);

INSERT INTO T1 (Col1, Col2, [Col]) VALUES
    (1, 'Test1', 'A'), (2, 'Test2', 'B');
INSERT INTO T2 ([Col], Col3, Col4) VALUES
    ('A','1x','1y'), ('A','2x','2y'), ('B','3x','3y'), ('B','4x','4y'), ('B','5x','5y');

Your query:

SELECT
    Col1,
    Col2,
    (
        SELECT      Col3, Col4
        FROM        T2
        WHERE       T2.Col = T1.Col
        FOR XML AUTO, TYPE
    )
FROM        T1
FOR XML AUTO, TYPE;

Produces the same results as one without any nesting:

SELECT
    Col1,
    Col2,
    Col3,
    Col4
FROM        T1
JOIN        T2
ON          T2.Col = T1.Col
FOR XML AUTO, TYPE;

They both produce:

<T1 Col1="1" Col2="Test1">
  <T2 Col3="1x" Col4="1y" />
  <T2 Col3="2x" Col4="2y" />
</T1>
<T1 Col1="2" Col2="Test2">
  <T2 Col3="3x" Col4="3y" />
  <T2 Col3="4x" Col4="4y" />
  <T2 Col3="5x" Col4="5y" />
</T1>

(That will break down if you have more complex or custom nesting requirements.)

Regardless of this particular example, I don't think there's an effective way to get (dynamic) XML into an indexed view.

First, an "indexed view" is really a view with a unique clustered index. You can't create "normal" indexes on an XML column.

Let's say you try:

CREATE TABLE T3
(
    x xml
);
CREATE UNIQUE CLUSTERED INDEX IX_T3 ON T3 (x);
GO

You will get an error:

  Msg 1977, Level 16, State 1, Line 73 Could not create index 'IX_T3' on
  table 'T3'. Only XML Index can be created on XML column 'x'.

What about an XML index? Well:

  You cannot create an XML index, either primary or secondary, on an xml
  column in a view, on a table-valued variable with xml columns, or xml
  type variables.

CREATE XML INDEX (Transact-SQL)

So in order to have the XML materialized using a view, it can't be the only column. You need a "normal" column to create the unique clustered index on. One way to get a normal column would be to have the XML be created with a subquery. But as you point out, if you try this:

CREATE VIEW V1
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    1,
    (
        SELECT 'Test' AS 'Col'
        FOR XML PATH, TYPE
    )
GO

CREATE UNIQUE CLUSTERED INDEX IX_V1 ON V1 (n);
GO

You will get this error:

  Msg 10127, Level 16, State 1, Line 83 Cannot create index on view
  "tempdb.dbo.V1" because it contains one or more subqueries. Consider
  changing the view to use only joins instead of subqueries.
  Alternatively, consider not indexing this view.

You might think you could cheat by creating an outer view which didn't have a subquery, calling the original view. But if you try:

CREATE VIEW V2
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    n,
    x
FROM    dbo.V1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V2 ON V2(n);
GO

You will get an error:

  Msg 1937, Level 16, State 1, Line 98 Cannot create index on view
  'tempdb.dbo.V2' because it references another view 'dbo.V1'. Consider
  expanding referenced view's definition by hand in indexed view
  definition.

So you can't nest these views, and you can't use subqueries. You can't use the APPLY operator either, so you can't use a table-valued user-defined function (nor can you effectively use the nodes XML function).

But you can use deterministic scalar-valued functions. So you can construct some rudimentary XML. For example:

CREATE FUNCTION F1
(
    @Col1 int,
    @Col2 varchar(50)
)
RETURNS xml
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        (
            SELECT
                @Col1,
                @Col2
            FOR XML PATH(''), TYPE
        )
END
GO

CREATE VIEW V3
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    Col1,
    dbo.F1(T1.Col1, T1.Col2)
FROM    dbo.T1 AS T1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V3 ON V3(n);
GO

While you could create complex, multi-level XML within the function, the function cannot reference any tables (or it wouldn't be deterministic). For example, this:

CREATE FUNCTION F2
(
    @Col1 int,
    @Col2 varchar(50),
    @Col char(1)
)
RETURNS xml
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        (
            SELECT
                @Col1 AS Col1,
                @Col2 AS Col2,
                (
                    SELECT      Col3, Col4
                    FROM        dbo.T2 AS T2
                    WHERE       T2.Col = @Col
                    FOR XML AUTO, TYPE
                )
            FOR XML PATH, TYPE
        )
END
GO

CREATE VIEW V4
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    Col1,
    dbo.F2(T1.Col1, T1.Col2, T1.Col)
FROM    dbo.T1 AS T1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V4 ON V4(n);
GO

Results in this error:

  Msg 10133, Level 16, State 1, Line 181 Cannot create index on view
  "tempdb.dbo.V4" because function "dbo.F2" referenced by the view
  performs user or system data access.

Therefore the function can't go get the information it needs. It must be fed the information. And there's simply no way (that I can think of) to collect the data from multiple rows and feed it to the function. You can feed a deterministic function XML to manipulate, but you can't create the base XML without a subquery.

So in short, I don't think there's any way using an indexed view to materialize XML which contains data from multiple rows in any table.


Creating encrypted backups inhouse to be restored at client

Created the certificate from the .cer-file […]

You created the certificate but you didn't include the import of the private key, so it makes the use of this certificate to decrypt the data impossible.

  why I have to transfer the certificates private key to my client.

In this case, the certificate (which at its heart is just an asymmetric key) uses the public key to encrypt the data and the private key to decrypt the data. See my other response above.

  Doesn't that defeat the whole purpose of encryption?

No, but it defeats the purpose of backup encryption through SQL Server for your use case. Since you want yourself (1st party) and some random 3rd party to be able to encrypt and decrypt the data, you either need to agree to a set of keys to be used (which would put your other internal items at risk) or use a different method outside of SQL Server backup encryption where you can specify other encryptor options.

The fact that they can't decrypt the data without the required information is the whole point of encryption. In this case, it's doing the job properly it's just your requirements aren't meant to use it in this manner.


Need help with a query for a helpdesk application

Simply add another join to the tblUsers table, to get the name of the other employee.

SELECT p.pID
    , EmpFullName = uEmp.fName + ' ' + uEmp.lName
    , p.sLocation
    , p.sRoom
    , p.pStatus
    , p.pTitle
    , RepFullName = uRep.fName + ' ' + uRep.lName
FROM dbo.Problems p
    INNER JOIN dbo.tblUsers uEmp ON p.employeeID = uEmp.empID
    INNER JOIN dbo.tblUsers uRep ON p.repID = uRep.empID
WHERE p.pStatus='OPEN';

As you can see, there are two instances of the dbo.tblUsers table referenced in the FROM clause, each with a different alias.  This allows you to return separate rows from tblUsers, one for the EmployeeID and one for the RepID

You'll likely need to add to the WHERE clause to pulls rows from the Problem table for a particular EmployeeID or a particular RepID depending on how you want to display results.  This might look something like:

WHERE p.pStatus='OPEN'
    AND p.empID = 42;

Notice, I've prefaced all tables in the FROM clause with the name of the schema where those tables reside.  This is a great habit to have since it will prevent issues down the road if you decide to have more than a single schema.

I also listed each column on its own row, and moved the column aliases in front of the actual column name in the SELECT clause; this makes it easier to find the particular column later when debugging.

You should also get in the habit of appending a semi-colon to the end of all T-SQL statements, since that can mitigate problems with the WITH CTE construct, among others.


Absolutely, that is an option. Do the full backup, and then log backups. At the receiving end, restore all but the last using NORECOVERY.

Automating regular log backups and then restoring at the other end is what we call "log shipping", and there's a feature to do that using Agent jobs in SQL Server. Might, or might now, be relevant for you depending on your setup. Possibly not because of your cloud environment (it uses a share to store the backup files, which the originating server needs access to.

Whether to then stripe one backup over several files (not sure if you had this in mind), depends on whether it is beneficial to have several files for the transfer process. I doubt you gain much with striping for the log backups.


Having problems with concurrency while trying to lock a record in custom messaging system (Queue)

The query looks so much simpler. There's an output keyword in SQL Server that works perfectly!

Here we go:

ALTER PROCEDURE [dbo].[LockAndPeek]
    @Count INT,
    @QueueTypeId INT
AS
BEGIN

SET XACT_ABORT ON; -- blow up the whole tran on any errors
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN

UPDATE Q
SET
    StatusID = 2, -- In Progress
    Processed = GETUTCDATE()
OUTPUT Inserted.*
FROM (
    SELECT TOP (@Count) *
    FROM
        Queues WITH (READPAST, ROWLOCK)
    WHERE
        StatusID = 1 AND -- New
        TypeID = @QueueTypeID AND
        AvailableTime IS NOT NULL AND
        AvailableTime <= GETUTCDATE()
   ORDER BY ID
) Q;

COMMIT TRAN;

END


What does standard SQL or any of the non-PostgreSQL SQLs do instead of RETURNING?

The Standard SQL Way™ of generating IDs is to

create a sequence for the table;
read the next value with SELECT NEXT VALUE FOR seq_name¹; and then
insert a complete row, including the ID value.

Before SQL:2003, there were no autogenerated IDs. And in SQL:2003, there appears to be no method to read the last GENERATED AS IDENTITY value.

¹ Postgres does not support this, and requires SELECT nextval('seq_name').


How to attach a sql db with blobs using a shared folder?

Apparently it is not supported to use "localhost"

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-with-smb-fileshare-as-a-storage-option?view=sql-server-2017

You may need to use another server instead of localhost.


What do most people use to work with SSIS packages since VS2015 UI is buggy?

You can post on connect with any version and I would encourage you to.

These issues aside I think the only sane way to work with ssis is to use biml to write your packages and compile that into ssis.

This means you don't need to keep going into the properties of each task ad-infinitum- if you want to see how a task is configured you scroll down in the biml and look.

It is a bit more work upfront but as soon as you need to make a change or look at an old package it will pay off dividends.

There are other advantages like easy source code management, comparing biml files for changes is much easier than the raw ssis xml that changes id's every time you press "build".

Forget writing ssis packages using the ide and use it to debug packages when you really really need to .

Just to add on that when you experience the pain of being in the script editor ide and needing to close it down, close down th script properties, go into the data flow and look at the properties of a destination task and then go into the mappings tab to then close it all back to go back to the script task you realise that native ssis development is horrible!


Hash Match Spills and Key Lookup causing major performance issue

Your query only uses about 2 seconds of CPU time. You can view that in the root node of the execution plan. It spent about 30 seconds waiting on various things.

7915 ms of that wait time is for the ASYNC_NETWORK_IO wait type. This wait type occurs when SQL Server is ready to send more data to the client but the client hasn't indicated that it's ready to receive more data yet. This can commonly be seen when viewing large result sets in the SSMS results grid. If you're seeing this same type of wait in your application then look at your code that processes the result set that's returned and check if there are unnecessary columns getting returned. Otherwise you may find it helpful to discard the result set to get a more accurate measurement of query performance. As is, you lose 8 seconds out of your 20 second goal.

You spill a small amount of data with those hash joins, yet query execution dramatically slows down as a result. I can arrange a similar result on my local machine:

The spill and join take about 0.289 seconds for me. That's about 33 times faster than what happens on your server. I understand that you may not have any control over what cloud platform that you use. However, the time sink of tuning queries by hand needs to be balanced against other solutions. The query only spends 2 seconds of CPU time. If you had better hardware then it could easily finish in under 20 seconds with no code changes required. To be completely frank with you, if someone gave me a VM with performance as poor as what you're seeing I would refuse to use it.

If you can't get better hardware then you should be able to test query performance without the spill by adding a MIN_GRANT_PERCENT = 99 query hint. If performance significantly improves then try to improve the cardinality estimates going into the hash joins. The reason that you see the tempdb spills is that the memory grant is too small because the actual number of rows is about 20X larger than the estimated number of rows:

This complicated predicate may be contributing to the problem:

@Now BETWEEN OPL.StartDateUTC AND COALESCE(DATEADD(MILLISECOND, -3, CAST(CAST(DATEADD(DAY, 1, OPL.EndDateUTC) AS DATE) AS DATETIME)), DATEADD(MILLISECOND, -3, CAST(CAST(DATEADD(DAY, 1, @Now) AS DATE) AS DATETIME)))

One thing to try is putting the results of the join of the Organization and the OrganizationProductLanguage tables into a temp table. With a temp table, the query optimizer will know that you're getting 12 rows from that step instead of 1.41312. That alone may increase the estimates enough to avoid the tempdb spills.

If performance continues to be unacceptable after fixing the tempdb spills then try getting rid of some of the key lookups by including additional columns in the nonclustered indexes already in use. I expect most of the impact to come from adding the
ActivatedDateUTC and InactivatedDateUTC columns to [ProductAsset].[UX_ProductAsset]. Less benefit should be expected from adding DataValue to [AssetCacheData].[IX_AssetCacheData]. This may improve query execution time more than typically expected because this server applies such a large penalty to I/O.


Can I use a mariadb/mysql dump from older version in newer version?

major   Release Candidate   General Availability
8.0     2017-09-21 8.0.3    2018-04-19 8.0.11
5.7     2015-04-08 5.7.7    2015-10-21 5.7.9
5.6     2012-09-29 5.6.7    2013-02-05 5.6.10
5.5     2010-09-13 5.5.6    2010-12-03 5.5.8
5.1     2007-09-24 5.1.22   2008-11-14 5.1.30
5.0     2005-09-22 5.0.13

(I don't have MariaDB's GA dates.)

You can try to use a mysqldump on a different major version, but there may be incompatibilities.

Note that an "old" mysqldump may not understand a "new" db, but it is more likely that a "new" mysqldump will correctly read "old" data.

Some notable incompatibilities:

TYPE --> ENGINE  (4.1?)
fractional seconds (new in 5.6.4 / 10.0)
VISIBLE indexes  (8.0)

The 8.0 mysqldump apparently insists on including the default, but "new" option of VISIBLE.  This makes a mess when moving a dump (of any version) taken by the mysqldump for 8.0 and loading that dump on any MySQL/MariaDB other than MySQL 8.0.

You may notice comments like this:  This allows the dumps to (mostly) work across any version:

/*!50100 PARTITION BY ... */

That says "5.1 and newer can handle it, but if the target version is older than 5.1, treat this as a comment."  Caveat:  since the MariaDB numbering diverged from MySQL, this style of comment somethings screws up.

  I'm asking if it would create a problem in the future, when I will need to move to a newer version. If that's the case, then I need to take it into account today and use a different approach, as specified in that linked question.

They will always allow dumping from one version to the next major version.  It is a major way to upgrade.  Skipping a version may cause hiccups, but even that rarely causes trouble.


Thanks Martin, using your example answer I have it working using the following code:

SELECT CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee,
    Locations = STUFF
    (
        (
            SELECT ', ' + LocationName FROM dbo.Locations
            WHERE LocationID IN
                (SELECT LocationID FROM dbo.CompanyNoticesLocations WHERE CompanyNoticeID = CN.CompanyNoticeID)
            FOR XML PATH ('')
        ),1,1,''
    )
    FROM dbo.CompanyNotices AS CN
    INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
    INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
    WHERE CN.CompanyID = @CompanyID
    GROUP BY CN.CompanyNoticeID, CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee


A quick way to resolve your issue would be simply to return a Null column in each of the select statements.

SELECT
    url, NULL AS Category, NULL AS SubCategory,'Web' As Type FROM Website WHERE (url='www.facebook.com')
UNION ALL
SELECT
     NULL, category, NULL AS SubCategory, 'Cat' As Type FROM Category WHERE (category='www.facebook.com')
UNION ALL
SELECT
    NULL, NULL, subCategory, 'Sub' As Type FROM SubCategory WHERE (subCategory='www.facebook.com')

This generate the following output which should match your requirement of output

       url       | Category | SubCategory  | Type
www.facebook.com |  NULL    |    NULL      |  Web


Postgres window function to calculate summary position of orders

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

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


join from different tables gives same result, but it shouldn't

the solution was to add a subquery :D
thanks!!

SELECT
    `disenos`.*,
    `usuario_contacto`.
    `nombres`,
    `usuario_contacto`.
    `apellidos`,
    `users`.`username`,
    `users`.`avatar` as usuario_avatar,
    `total_coment`,
    COUNT(votos.id_usuario) as votos
FROM (`disenos`)
LEFT JOIN `votos` ON `votos`.`id_diseno` = `disenos`.`id_diseno`
LEFT JOIN `users` ON `users`.`id_usuario` = `disenos`.`id_usuario`

LEFT JOIN
    (
        SELECT id_diseno, COUNT(id_comentario) as total_coment
        FROM `comentarios`
        GROUP BY `id_diseno`
    ) `comments`

ON `comments`.`id_diseno` = `disenos`.`id_diseno`
LEFT JOIN `usuario_contacto` ON `usuario_contacto`.`id_usuario` = `disenos`.`id_usuario`
WHERE `disenos`.`estado` = 1
AND `disenos`.`votacion` = 1
GROUP BY `disenos`.`id_diseno`
LIMIT 5

EDIT: correcting a typo, sorry


Compression setting not effective for differential backup maintenance plan?

Community wiki answer:
Checking compression
You can check if your backup is compressed or not. There are a few methods in How can I tell if a SQL Server backup is compressed?.
You can also check the value of compressed_backup_size and backup_size column of the backupset table in msdb. For example:
SELECT
    database_name,
    type,
    PctCompression =
        CONVERT(NUMERIC(20,2),100 - (compressed_backup_size / backup_size) * 100)
FROM msdb.dbo.backupset
WHERE ...

The update to your question shows that the differential backup was indeed compressed.
Backup larger than expected
That 10GB of data could be spread over a lot of pages, and those pages could total a great deal more than the 10GB you inserted (especially with poor clustered index choices). Note also that differential backups operate at the extent level, so any change to any page within an 8-page extent will result in the whole extent being backed up.
Check the log_reuse_wait_desc after doing log backup or use dbcc opentran. If there is an open transaction since yesterday, your full and differential backups will be larger due to the included active log.
There may be no open transaction now, but if a long-running transaction was active at the time of differential backup, the backup file size will be more than expected.
The fact that your log grew to 500GB (and you take regular log backups) means the log could not be cleared for long time due to something (probably an open transaction). All the log associated with that transaction was added to the differential backup.
If you want to check if it's so, try to restore your database into another (new) database, perhaps on another server. This way you will do 2 things:

You'll verify your full and your diff backups; and
You'll see how much time your differential will take to restore.

If the differential takes a long time (e.g. 3+ hours) it indicates a lot of time was spent undoing the effects of a long-running open transaction.


Access Data that is stored in Primary FileGroup On SQL Server through DBLinks on Oracle

Filegroup is completely irrelevant: a table doesn't know where it is and internal storage allocation or placement won't affect a query.

If you have a single MDF you most likely only have one PRIMARY (or default) filegroup anyway

image datatype is deprecated too: use varbinary(max).

Have you run SQL Profiler to see what Oracle is sending to SQL Server? Is there a call actually being made?

Edit:  why does the 2nd trace use a table called "P44730584C4264287A49ABEAF_ADJ"


Column with Default value as Sum of TIMESTAMP

If the goal is to order by two columns, then simply do that.

SELECT ...
    ORDER BY JobDate, RecordTime

This will sort the output rows by JobDate, but in the case of duplicates for JobDate, it will use RecordTime.

Performance?  Not a problem, especially if can manage to get the right 'composite' index.  But we need to see the actual query to discuss that.

Note that your initial thought of adding the columns could lead to a different sort order.  Consider simple numbers:

id  JD  RT  JD+RT
 1  14  15    29
 2  14  13    27
 3   6  11    17
 4   7   5    12

SELECT id FROM .. ORDER BY JD+RT   -- 4, 3, 2, 1
SELECT id FROM .. ORDER BY JD,RT   -- 3, 4, 2, 1


You cannot execute such statements unless you use a stored proc:
You can run this below. It should work.

DROP procedure IF EXISTS `test2`;
DELIMITER $$
CREATE  PROCEDURE `test2`(p_employee_number int, p_date datetime)
Begin

if  NOT EXISTS (SELECT 1
    FROM employee_attendance
        WHERE employee_number =p_employee_number  AND attendance_date_time = p_date) THEN

    INSERT IGNORE INTO employee_attendance (employee_number, attendance_date_time)  VALUES (p_employee_number, p_date);

End if ;
END$$

DELIMITER ;


What's the best solution for offline db syncing

Bucardo is the standard tool for this sort of job.  It's well tested to run when the two servers are disconnected from each other for a while.  You could use any master/master replication approach instead:  Londiste, Slony, or the early release of BDR from 2ndQuadrant.

With the network setup you've described, you will be hard pressed to avoid race conditions here in all conditions no matter what you do.  The only way to keep someone from spending money in both locations here is to setup a two-phase commit transactions or do some form of synchronous replication, basically only putting the record on one machine if it's coordinated on both.  One nice thing about Bucardo is that it's designed expecting this will happen.  You can setup a conflict resolution mechanism, to recognize and potentially resolve conflicts on nodes when things are changed on both.  The other multi-master solutions are not aiming as directly at the disconnected network / conflicting change problems.


Query 100x slower in SQL Server 2014, Row Count Spool row estimate the culprit?

Why does this query need a Row Count Spool operator? ... what specific optimization is it trying to provide?

The cust_nbr column in #existingCustomers is nullable. If it actually contains any nulls the correct response here is to return zero rows (NOT IN (NULL,...)  will always yield an empty result set.).

So the query can be thought of as

SELECT p.*
FROM   #potentialNewCustomers p
WHERE  NOT EXISTS (SELECT *
                   FROM   #existingCustomers e1
                   WHERE  p.cust_nbr = e1.cust_nbr)
       AND NOT EXISTS (SELECT *
                       FROM   #existingCustomers e2
                       WHERE  e2.cust_nbr IS NULL)

With the rowcount spool there to avoid having to evaluate the

EXISTS (SELECT *
        FROM   #existingCustomers e2
        WHERE  e2.cust_nbr IS NULL)

More than once.

This just seems to be a case where a small difference in assumptions can make quite a catastrophic difference in performance.

After updating a single row as below...

UPDATE #existingCustomers
SET    cust_nbr = NULL
WHERE  cust_nbr = 1;

... the query completed in less than a second. The row counts in actual and estimated versions of the plan are now nearly spot on.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT *
FROM   #potentialNewCustomers
WHERE  cust_nbr NOT IN (SELECT cust_nbr
                        FROM   #existingCustomers
                       )

Zero rows are output as described above.

The Statistics Histograms and auto update thresholds in SQL Server are not granular enough to detect this kind of single row change. Arguably if the column is nullable it might be reasonable to work on the basis that it contains at least one NULL even if the statistics histogram doesn't currently indicate that there are any.


I'm not a hardware expert, but why do you think a SAN should be comparable to a NVMe?

Sorry, there is no simple formula.

At what scale did you initialize the benchmark tables? that is, what number for pgbench -i -s 20? How many clients did you run the benchmark? (What numbers did you provide for -jand -c?)  Did you run pgbench on the same server as was running the database?  For how long did you run it?  The system can absorb write requests very fast for a while.  Then either checkpointing kicks in, or RAM becomes clogged with dirty pages.  For write-heavy benchmarks, I'd be hesitant to draw conclusions from any run less than 30 minutes.

I suspect that what you are benchmarking here is how fast your storage can complete (or claim to complete) a sync request.  Does it get much faster if you do the below?

PGOPTIONS='-c synchronous_commit=off' pgbench ....

If you want to strip out the database itself and just test the underlying sync speed, you can use "pg_test_fsync".  Make use the file it uses gets placed on the correct storage, either by changing directories or by using "-f" option.


'' is an empty string, it's not a NULL value.

Other DBMS would rightfully reject your update statement in the first place (because  it's invalid to assign a string to a number).

If you want to set something to null then use null not an empty string

UPDATE item
  SET item_publication_year = null
WHERE item_id = 5

You should also use the correct literal type for the data type. '5' is a string, it's not a number . 5 is a number. Not using the correctly typed literal might result in indexes not being used.


SELECT users.id,users.group_id,Approved_Courses.module
      ,Approved_Courses.order_number , Enrollments.id
FROM
Users full outer join
approved_courses
  on users.group_id=approved_courses.group_id
left outer  join Classes
  on Classes.id=Approved_Courses.group_id and Approved_Courses.module=Classes.module
left outer join Enrollments
  on Classes.id=Enrollments.class_id
 and Users.id=Enrollments.staff_id

order by users.id,users.group_id
        ,Approved_Courses.module ,Approved_Courses.order_number


sql backup restore converts createdAt to local TZ

From MySQL documentation

MySQL converts TIMESTAMP values from the current time zone to UTC for
storage, and back from UTC to the current time zone for retrieval.
(This does not occur for other types such as DATETIME.) By default,
the current time zone for each connection is the server's time. The
time zone can be set on a per-connection basis. As long as the time
zone setting remains constant, you get back the same value you store.
If you store a TIMESTAMP value, and then change the time zone and
retrieve the value, the retrieved value is different from the value
you stored. This occurs because the same time zone was not used for
conversion in both directions. The current time zone is available as
the value of the time_zone system variable. For more information, see
Section 5.1.15, “MySQL Server Time Zone Support”.


How do I demonstrate Transaction Log activity?

Pick a page containing a record in your table (use %%physloc%% to find one).
Run DBCC PAGE before the DDL, write down last_lsn of the page.
Run your DDL
Run again DBCC PAGE. Did last_lsn change?

If the last_lsn changed the DDL is a size-of-data updates-every-record kind of change. If the last_lsn did no change obviously the DDL did not update every record.

For a more elaborate way, you can track page write XEvents for a single statement (the DDL).


What is the best way to backup logs and shrink logs in SQL Server 2008 r2?

The best way is not to shrink it at all.

You are not losing any data when you shrink the log or when switching recovery models. You are losing recovery points though.

But there's a reason the file was sized that way.

Backup your transaction logs frequently, such as every minute, to minimize its size. If the size is still bigger than what you'd think, LEAVE IT ALONE or figure out which are your large transactions. Those transactions might be able to be broken up, though it is likely an index rebuild that can't be broken up.

To answer your question, I just run my backup log job and then use DBCC SHRINKFILE. I may have to free the procedure cache in tempdb though: https://www.brentozar.com/archive/2016/02/when-shrinking-tempdb-just-wont-shrink/


In MySQL, does the order of the columns in a WHERE clause affect query performance?

I do not think so. The query optimizer should be clever enough.

You can try rearranging the WHERE clauses and see that EXPLAINS tells you the same in each case.

About what can be done to optimize this query: Is there an index on ASI_EVENT_TIME ? (this is the most crucial I think for this query as you also sort the results using it).

Are there indexes on the other two fields (ASI_SEISMO_ID and ASI_ACTIVITY_ID)?

It would be helpful if you posted the table structure.


Set MySQL option to restore MySQL server after deleting ibdata1

If you got a mysqldump export (.sql) of your databases, you do not need to have stopped mysql in any special way.

Make sure mysql is down

pgrep mysqld
(no output)

Delete everything on your datadir (by default, /var/lib/mysql)

mkdir /tmp/mysql.bak && sudo mv /var/lib/mysql/* /tmp/mysql.bak

Run:

sudo mysql_install_db

Change the permissions od your datadir to the user mysql:

sudo chown -R mysql:mysql /var/lib/mysql

Start mysql:

sudo service mysql start

Import your databases back:

mysql -u root < your_dump.sql

You will have your databases back on a fresh installation of mysql, with a just created ibdata1.


MySQL Matching records across tables discrepancies

I think this could be a whitespaces issue. The problem is that MySQL ignores trailing whitespace when doing string comparison. See http://dev.mysql.com/doc/refman/5.6/en/char.html

  All MySQL collations are of type PADSPACE. This means that all CHAR,
  VARCHAR, and TEXT values in MySQL are compared without regard to any
  trailing spaces.

  ...

  For those cases where trailing pad characters are stripped or
  comparisons ignore them, if a column has an index that requires unique
  values, inserting into the column values that differ only in number of
  trailing pad characters will result in a duplicate-key error. For
  example, if a table contains 'a', an attempt to store 'a ' causes a
  duplicate-key error.

The section for the like operator gives an example for this behavior (and shows that like does respect trailing whitespace):

mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
|          1 |             0 |
+------------+---------------+
1 row in set (0.00 sec)

Unfortunately the UNIQUE index seems to use the standard string comparison to check if there is already such a value, and thus ignores trailing whitespace. This is independent from using VARCHAR or CHAR, in both cases the insert is rejected, because the unique check fails. If there is a way to use like semantics for the UNIQUE check then I do not know it.

I made a test so you could see if there's some fields with whitespace that you're trying to matching.

Tables:

CREATE TABLE `click_withcust` (
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `click_withoutcust` (
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data:

mysql> select * from test.click_withoutcust;
+--------------------+
| email              |
+--------------------+
| onaaree@gmail.com  |
| onare123@gmail.com |
| onare@gmail.com    |
+--------------------+
3 rows in set (0.00 sec)

mysql> select * from test.click_withcust;
+------------------+
| email            |
+------------------+
|  onare@gmail.com |
| onare@gmail.com  |
+------------------+
2 rows in set (0.00 sec)

mysql>

If you see there's a row with one whitespace onare@gmail.com.

SELECT click_withoutcust.*
FROM click_withoutcust
LEFT JOIN click_withcust
ON (click_withcust.`email`=click_withoutcust.`email`)
WHERE click_withcust.email IS NULL;

mysql> SELECT
    -> click_withoutcust.*
    -> FROM click_withoutcust
    -> LEFT JOIN click_withcust
    -> ON (click_withcust.email=click_withoutcust.email)
    -> WHERE click_withcust.email IS NULL;
+--------------------+
| email              |
+--------------------+
| onaaree@gmail.com  |
| onare123@gmail.com |
+--------------------+
2 rows in set (0.00 sec)

But if you count the tables rows:

mysql> select count(*) from test.click_withoutcust;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test.click_withcust;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Try using COUNT and LIKE to find if there are rows with whitespaces.

mysql> select count(*) from test.click_withoutcust where email like '% %';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test.click_withcust where email like '% %';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)


Unable to change engine to MyISAM in MySQL

MySQL 5.6.10 is quite new to me as well, so bear with me on this answer

I installed MySQL 5.6.10 on a Linux VM

I added default-storage-engine=MyISAM to my.cnf and restarted MySQL

It went from this:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

to

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

No problem.

If you placed

default-storage-engine=MyISAM

in my.cnf, restarted MySQL and still the default is InnoDB, then there is only one explanation: You placed default-storage-engine=MyISAM in the wrong part of my.cnf

I once caught a mistake like that in a ServerFault question : Set group_concat_max_len permanently (MySQL config)

Make sure the option is under the [mysqld] section of my.cnf

[mysqld]
default-storage-engine=MyISAM

SUGGESTION

I think MyISAM in MySQL 5.6.10 is run as a plugin. Check your error logs for any plugin errors.

Also, do this:

In one SSH session, tail -f /var/log/mysqld.log

In another SSH session, service mysql restart

Look for any plugin messages associated with MyISAM. For example, note that when I ran a shutdown I saw these messages:

2013-02-26 15:20:13 1725 [Note] InnoDB: Starting shutdown...
2013-02-26 15:20:15 1725 [Note] InnoDB: Shutdown completed; log sequence number 1600309
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'CSV'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MEMORY'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MRG_MYISAM'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MyISAM'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'sha256_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'mysql_old_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'mysql_native_password'
2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'binlog'
2013-02-26 15:20:15 1725 [Note] /usr/sbin/mysqld: Shutdown complete

130226 15:20:15 mysqld_safe mysqld from pid file /var/lib/mysql/chtn-web2v.pid ended

Note that MyISAM is a plugin. If you do not see anything like

2013-02-26 15:20:15 1725 [Note] Shutting down plugin 'MyISAM'

your MyISAM plugin may have an issue with the Mac OS X version of MySQL.


Straight to the point? The smallfiles option doesn't help. It doesn't automagically shrink existing files. All this option does is changing the behavior of how data files are preallocated.

Usually, the first datafile allocated has a size of 64MB. The next has 128MB. This doubling goes on until 2GB are reached. After that, every new datafile is allocated with 2GB.

The smallfiles option simply sets the initial size to 16MB, and the doubling starts from there.

You need either to increase disk space or start to shard.


View all 32 possible Binary combinations for 5 digits


Security Considerations for AG vs Log Shipping

I don't know from a security point of view what might be the implications and concerns for the organisation. [sic]

Nothing changes. It's still a separate OSE (operating system environment), still only has the logins you give it, etc., the only difference is now it's using a continuous log stream and will be coupled with the cluster (so availability may be impacted, but not the system, as compared to log shipping).

  Example: in case the primary node becomes infected from some malicious software and effects the logs, what will be the implications on all the other passive nodes.

None. We send logical changes, if those changes are corrupt or otherwise incorrect it'll be thrown out and you'll receive errors that the log stream is bad.


My database dramatically increased in size. Why?

The query below will show you an overview of the schemas, tablespaces and objects in your database and their respective sizes.

- Change the group by to decrease the level of detail (you might want to see if the size is being caused by tables, indexes or other objects first)
- Add a where clause to only list the schema/tablespace/object_type you want to investigate.

select
  owner SCHEMA,
  tablespace_name TABLESPACE,
  segment_type OBJECT_TYPE,
  segment_name OBJECT_NAME,
  round((sum(bytes)/1024/1024), 3) MB
from dba_segments
group by owner, tablespace_name, segment_type, segment_name
order by owner, tablespace_name, MB desc;

It does not show you what DML is causing it, but it already shows you what objects are causing it. You can then go trough the query history in Enterprise Manager to find queries that work with these problematic objects.

In Enterprise Manager, go to the performance tab, and at the bottom select "Search SQL". Then search for queries with, for example, the large table in their "SQL text". Like this, for example: %large_table_name%

Good luck!


It looks like a bug. Did the set work with multi-column values?

SET t:3 0 0 5
a\tb\tc

(\t is a tabulator)


Filegroup with multiple files: odd behaviour when moving stuff in

First of all thank you guys for helping me to get on track with your comments.

I have now worked through an example and have a better understanding what's going on.

The problem arises with moving LOB-Data (such as VARCHAR(MAX), XML and so on) to another filegroup.
When you rebuild a clustered index on another filegroup the LOB-Data stays at it's former place (set by the TEXTIMAGE ON command in the CREATE TABLE statement).

One classic way to move the LOB-Data is to create a second table with the same structure in the new filegroup, copy data over, drop the old table and rename the new one. However this brings in all sorts of possible issues like lost data, invalidated data (because of missing check constraints) and error handling is quite tough. I have done this for one table in the past but IMHO it doesn't scale well and consider the nightmare of having to transfer 100 tables and you got errors for table 15, 33, 88 and 99 to fix.

Therefore I use a well-known trick regarding partitioning: As described by Kimberly Tripp LOB-Data does move to the new filegroup when you put partitioning on it. As I do not plan to use partitioning in the long run but just as a helper for moving that LOBs, the partition scheme is quite dull (throwing all partitions into one filegroup): I don't even care, which partition the data is on as I just want to get them moved. Actually this technique and the implementation is not invented by myself...I use a formidable script by Mark White. My mistake was to not fully understand what this script does and what the implications are....which I have now:

For LOB-Data it is necessary to rebuild (or recreate) the table (mostly the clustered index) twice: first with putting partitioning on it and second with removing the partitioning. Whether you use SORT_IN_TEMPDB=ON or not this results in having to provide the space of the original data TWICE: if your original table has 100MB, you need to provide 200MB for the operation to succeed. At the beginning I was quite puzzled, ending up with my new data files which had a lot of free space after the operation was finished.

Now I accepted that I can't cheat around avoiding the free space. However I could avoid the necessity to shrink files afterwards. Therefore my solution is to do the first rebuild on a temporary filegroup and the second rebuild (removing partioning) on the destination filegroup. The temporary filegroup can be removed afterwards (if hopefully I don't hit the error message "The filegroup cannot be removed" (have a look at my question here) anymore.

Thanks for reading and your help

Martin

Here is a repro script for my problem which includes the solution I have come up for it:

    /*============================================================================
  Adapted the following file published by sqlskills to demonstrate filegrowth
  after partitioning for StackOverflow Question.

  Martin Guth, 02.02.2018

  File:     MovingLOBData.sql

  Summary:  Because 2012 supports online index rebuilds - even with LOB.
            You might think this means you can move LOB data around
            (one of the VERY cool things you can do with IN_ROW data to
            actually move it). However, the behavior of LOB data is NOT
            necessarily intuitive. This script will show you how/why/what!

  SQL Server Versions: SQL Server 2012
------------------------------------------------------------------------------
  Written by SQLskills.com

  (c) SQLskills.com. All rights reserved.

  For more scripts and sample code, check out
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

SET NOCOUNT ON
go

USE master
go

CREATE DATABASE [TestLOB]
 CONTAINMENT = NONE
ON  PRIMARY
(   NAME = N'TestLOBPrimary'
    , FILENAME = N'U:\DB_DATA\TestLOBPrimary.mdf'
    , SIZE = 100MB , FILEGROWTH = 1024KB ),

FILEGROUP [FG1]
(   NAME = N'FG1File1'
    , FILENAME = N'U:\DB_DATA\FG1File1.ndf'
    , SIZE = 40MB , FILEGROWTH = 20480KB ),
(   NAME = N'FG1File2'
    , FILENAME = N'U:\DB_DATA\FG1File2.ndf'
    , SIZE = 40MB , FILEGROWTH = 20480KB ),

FILEGROUP [FG2]
(   NAME = N'FG2File1'
    , FILENAME = N'U:\DB_DATA\FG2File1.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),
(   NAME = N'FG2File2'
    , FILENAME = N'U:\DB_DATA\FG2File2.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),

FILEGROUP [tempLOB]
(   NAME = N'tempLOB1'
    , FILENAME = N'U:\DB_DATA\templob1.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),
(   NAME = N'tempLOB2'
    , FILENAME = N'U:\DB_DATA\templob2.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB )

LOG ON
(   NAME = N'TestLOBLog'
    , FILENAME = N'U:\DB_DATA\TestLOBLog.ldf'
    , SIZE = 10MB , FILEGROWTH = 10MB)
GO

USE TestLOB
go

ALTER DATABASE TestLOB
MODIFY FILEGROUP FG1 DEFAULT
go

--DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go

INSERT INTO dbo.TestLobTable
(
    c2,
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000)
)
go 1000

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
go

sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'

go

-- size is roughly 40 MB: 1,000 rows; 39.2MB; 31.3MB LOB

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  2.875000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500

--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/

/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go

CREATE PARTITION SCHEME PS_TestLobTable
AS PARTITION PF_TestLobTable
TO ( fg2, fg2 )
go

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB= OFF)
ON PS_TestLobTable (c1)
go

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2688    21.000000   19.750000
FG2File2    2688    21.000000   19.687500

--> now Filegroup 2 has roughly 40 MB data...interestingly the create index would fail if having 2*20MB capacity available but would pass at 2*21MB
*/

-- try to recreate the index again to get rid of partitioning
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB= OFF)
ON [FG2]
go

/* error message 1105
Could not allocate space for object 'dbo.TestLobTable'.'TestLobTableCL' in database 'TestLOB' because the 'FG2' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

--> makes sense because no free space available in filegroup with partitioned clustered index already present
*/

ALTER DATABASE TestLOB MODIFY FILE (NAME = N'FG2File1', SIZE=41MB);
ALTER DATABASE TestLOB MODIFY FILE (NAME = N'FG2File2', SIZE=41MB);

-- rebuild again without sort in tempdb
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON,  SORT_IN_TEMPDB= OFF)
ON [FG2]
go

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5248    41.000000   19.750000
FG2File2    5248    41.000000   19.625000

--> now the files of FG2 have 50% free space left
*/

-- try to shrink with truncateonly
DBCC SHRINKFILE('FG2File1', TRUNCATEONLY);
DBCC SHRINKFILE('FG2File2', TRUNCATEONLY);

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5048    39.437500   19.750000
FG2File2    5040    39.375000   19.625000

--> no significant effect...still almost 50% free space
*/

-- recreate the table
DROP PARTITION SCHEME PS_TestLobTable;
DROP PARTITION FUNCTION PF_TestLobTable;

DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go

INSERT INTO dbo.TestLobTable
(
    c2,
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000)
)
go 1000

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
go

sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'
GO

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.125000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    5048    39.437500   0.062500
FG2File2    5040    39.375000   0.062500

--> data on filegroup 1 again... move them to filegroup2 this time with SORT_IN_TEMPDB
*/

/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go

CREATE PARTITION SCHEME PS_TestLobTable
AS PARTITION PF_TestLobTable
TO ( fg2, fg2 )
go

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON )
ON PS_TestLobTable (c1)
go

-- removing partitioning on table
CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON, SORT_IN_TEMPDB = ON )
ON [fg2]
go

-- now try to shrink with truncateonly
DBCC SHRINKFILE('FG2File1', 20,TRUNCATEONLY);
DBCC SHRINKFILE('FG2File2', 20,TRUNCATEONLY);

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    5376    42.000000   19.687500
FG2File2    5376    42.000000   19.687500

--> no significant effect...still almost 50% free space
*/

--- retry with separate filegroup

CREATE DATABASE [TestLOB]
 CONTAINMENT = NONE
ON  PRIMARY
(   NAME = N'TestLOBPrimary'
    , FILENAME = N'U:\DB_DATA\TestLOBPrimary.mdf'
    , SIZE = 100MB , FILEGROWTH = 1024KB ),

FILEGROUP [FG1]
(   NAME = N'FG1File1'
    , FILENAME = N'U:\DB_DATA\FG1File1.ndf'
    , SIZE = 40MB , FILEGROWTH = 20480KB ),
(   NAME = N'FG1File2'
    , FILENAME = N'U:\DB_DATA\FG1File2.ndf'
    , SIZE = 40MB , FILEGROWTH = 20480KB ),

FILEGROUP [FG2]
(   NAME = N'FG2File1'
    , FILENAME = N'U:\DB_DATA\FG2File1.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),
(   NAME = N'FG2File2'
    , FILENAME = N'U:\DB_DATA\FG2File2.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),

FILEGROUP [tempLOB]
(   NAME = N'tempLOB1'
    , FILENAME = N'U:\DB_DATA\templob1.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB ),
(   NAME = N'tempLOB2'
    , FILENAME = N'U:\DB_DATA\templob2.ndf'
    , SIZE = 20MB , FILEGROWTH = 0MB )

LOG ON
(   NAME = N'TestLOBLog'
    , FILENAME = N'U:\DB_DATA\TestLOBLog.ldf'
    , SIZE = 10MB , FILEGROWTH = 10MB)
GO

USE TestLOB
go

ALTER DATABASE TestLOB
MODIFY FILEGROUP FG1 DEFAULT
go

--DROP TABLE TestLobTable;

CREATE TABLE dbo.TestLobTable
(
    c1  int identity,
    c2  char(8000)      default 'this is a test',
    c3  varchar(max)    NULL
) -- will be created on FG1
go

INSERT INTO dbo.TestLobTable
(
    c2,
    c3
)
VALUES
(
    'this is a test',
    REPLICATE (convert(varchar(max), 'ABC'), 8000)
)
go 1000

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
go

sp_blitzIndex
    @databaseName = 'TestLOB',
    @schemaName = 'dbo',
    @tableName = 'TestLobTable'

go

-- size is roughly 40 MB: 1,000 rows; 39.2MB; 31.3MB LOB

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  2.875000
FG1File1    5120    40.000000   19.687500
FG1File2    5120    40.000000   19.687500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500

--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/

/* moving Lob data using partitioning trick */
CREATE PARTITION FUNCTION PF_TestLobTable (int)
AS RANGE RIGHT FOR VALUES (0)
go

CREATE PARTITION SCHEME PS_TestLobTable
AS PARTITION PF_TestLobTable
TO ( tempLOB, tempLOB )
go

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON)
ON PS_TestLobTable (c1)
go

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2560    20.000000   0.062500
FG2File2    2560    20.000000   0.062500
tempLOB1    2560    20.000000   19.687500
tempLOB2    2560    20.000000   19.687500

--> 2*19,687 MB are occupied in Filegroup 1 ---> approx 40 MB in total in Filegroup 1
*/

CREATE UNIQUE CLUSTERED INDEX TestLobTableCL
ON TestLobTable (c1)
WITH (DROP_EXISTING = ON)
ON [FG2]
go

SELECT
    f.name AS [filename],
    fu.total_page_count AS [pageCount],
    fu.total_page_count/128.0 [sizeMBTotal],
    (fu.total_page_count - unallocated_extent_page_count) /128.0 [sizeMBUsed],
    fu.*
FROM sys.dm_db_file_space_usage fu
INNER JOIN sys.database_files  f ON fu.file_id = f.file_id
INNER JOIN sys.filegroups fg ON fu.filegroup_id = fg.data_space_id
go

/*
filename    pageCount   sizeMBTotal sizeMBUsed
TestLOBPrimary  12800   100.000000  3.062500
FG1File1    5120    40.000000   0.062500
FG1File2    5120    40.000000   0.062500
FG2File1    2560    20.000000   19.625000
FG2File2    2560    20.000000   19.750000
tempLOB1    2560    20.000000   0.062500
tempLOB2    2560    20.000000   0.062500

--> data successfully moved to fg2, tempLOB empty
*/
DROP PARTITION SCHEME PS_TestLobTable
DROP PARTITION FUNCTION PF_TestLobTable

ALTER DATABASE TestLOB REMOVE FILE tempLOB1;
ALTER DATABASE TestLOB REMOVE FILE tempLOB2;
ALTER DATABASE TestLOB REMOVE FILEGROUP tempLOB;

/*
    summary:
    - Moving LOB Data with the help of partitioning results in twice the space needed of the original data for temporary rebuilds.
    - To avoid problematic and long running shrinking of database files it's best to use a different filegroup for the first rebuild as this can be easily removed afterwards.
*/


MySql timeouts waiting for table lock, no apparent deadlocks

If a transaction has been running 837 seconds, I suggest that its is poorly designed.  I don't like a transaction to last even 5 seconds, much less the default innodb_lock_wait_timeout=50.

Can't wait for user - A transaction should not include any user interaction.  Waiting for a user might mean waiting for him/her to take a phone call.  If you need something locked for some kind of user 'session', BEGIN...COMMIT is not the way to do it.

autocommit=0 is bad - Another possible mistake is autocommit=0 then forgetting to COMMIT.  For that reason, I recommend never using that setting.  Instead, be explicit about including BEGIN and let it be a reminder that you need a COMMIT.

If you would like to discuss this further, let's see your whole 'transaction', either at a high level, or with specific SQL statements.


To find out where the PostgreSQL data folder is located, just run SQL query:
show data_directory;

You can find more details here
However, note
File System Level Backup are faster but large in size. The worst thing is that to perform a database's backup, PostgreSQL Server required to be stopped. More information about the restrictions of this operation can be found in the official documentation. On my own I will add that from such backups, as a rule, there is more harm than good.
The main method for creating backups in PostgreSQL is the pg_dump utility.
pg_dump dbname > dbname.dump

Opposite file-level backup, a backup via the pg_dump tool does not require stopping the server, it is easy to automate and does not require access to the server file system (it can be performed remotely).


The SQL standard defines that identifiers are not case sensitive (unless double-quoted: "CamelCase"). Your desire for CamelCase (mixed-case) names conflicts with the standard and you'd better learn to live with it. Or you start double-quoting your identifiers and never forget to do so.
Most RDBMS actually implement standard(-like) behavior. Even MySQL tries, lately, but it's very hard to break with established syntax rules.
Resources

MySQL manual on this topic.
Related article running a test case against the most popular RDBMS.
A list of how the most prevalent SQL dialects deal with delimited identifiers in wikibooks.


Is it possible to build PostGIS extensions without building the entire project?

I think the answer to this is simply no. While this may get fixed at some point in time, and it should be fixed soon for the sanity and great glory, until then you may find this useful

What is the easiest way to install the latest postgis_tiger_geocoder?


MySQL utf8mb4 Migration - Replication Considerations

First make sure any latin1 columns have not been messed up.  Find an accented letter and do SELECT HEX(col) ... -- any accented letter should show as 2 bytes.  For example é is latin1 hex E9 and utf8mb4 hex C3A9.

If you see C383C2A9, you have "double encoding" and you need to worry about that now.

As for Master/Slave,...

If it is Master-Master (for ease of failing over), then consider doing them one at a time, thereby avoiding more than a brief downtime (to failover).  Be sure to use the SET to disable replication for the session.

Are you using ALTER TABLE ... CONVERT TO CHARACTER SET ...?  (That is probably the only valid way to be doing the conversion.)

With Master-Slave and plenty of downtime, that ALTER can simple go through replication.


Fastest way to alter column type in a 2-billion+ records innodb table

Take a look here at Percona's online schema change tool (anything from Percona is good - I'd advise you to look at all of the tools while you're at it). You could also try Shlomo Noach's online table change tool - test with both and see which one works for you (again, look at his entire toolkit).


Invalid use of a side-effecting operator 'INSERT' within a function - Multiple Inserts in Function

In T-SQL, you cannot modify any data in a function. There is no straightforward way around it. There are some obscure hacks, but I would not use them. Use a stored procedure.

The hack, quoting from Erland Sommarskog:

CREATE FUNCTION loophole(@i int) RETURNS varchar(20) AS
  BEGIN
     DECLARE @sql varchar(MAX),
             @cmd varchar(4000)
     SELECT @sql = ' UPDATE rsci ' +
                   ' SET b = CASE ' + ltrim(str(@i + 1)) +
                   ' WHEN 1 THEN ''Ett'' WHEN 2 THEN ''Två''' +
                   ' WHEN 3 THEN ''Tre'' WHEN 4 THEN ''Fyra''' +
                   ' WHEN 5 THEN ''Fem'' WHEN 6 THEN ''Sex''' +
                   ' WHEN 7 THEN ''Sju'' WHEN 8 THEN ''Åtta''' +
                   ' WHEN 9 THEN ''Nio'' WHEN 10 THEN ''Tio'' END' +
                   ' WHERE a = ' + ltrim(str(@i + 1))
     SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -d ' + db_name() +
                   ' -Q "' + @sql + '"'
     EXEC master..xp_cmdshell @cmd, 'no_output'
     RETURN (SELECT b FROM rsci WHERE a = @i)
  END


mysql rds read replica replication error

What would cause this?

Let's go into facts, then we will later have to go into speculation:

Table phppoint_koreannoona.phppos_items doesn't exist on query

You executed an ALTER TABLE query on a table that didn't exist. Your table existed on the master at the time the alter was ran (otherwise, it wouldn't have gone through the binlog and tried to be executed on the replica), but didn't exist on the replica. This makes replication break because it cannot continue in a consistent way.

What can make a table exist on the master and not on the replica? Several options:

The table was never there. In order to create a replica, RDS does a snapshot of the master and transfer it away. Depending on when or how it is done, the replica can be non-consistent (for example, I would be skeptical of a snapshot done at the same time than a running schema change). Maybe it was a memory only table and it wasn't properly transferred to disk? Maybe it was in a particular engine that has issues with transactionality (MyISAM).
The table was there, but it was deleted somehow.
Something else happened that caused the replicas to go out of sync. The exact reason will depend highly on the environement, configuration and the application. Some examples include:

A crash of the replica in a non-crash safe configuration
Replication coordinates were changed inappropriately or other maintenance introduced a mistake on replication
Unsafe write statements or configuration is used, which leads to inconsistencies
Filtering is used, leading to data inconsistencies
Queries were run on the master with replication/binlog disabled

How to fix that?

The safest way to fix inconsistencies on a replica is to recreate it from scratch. You can copy a single table and synchronize it relatively easily if it has a low amount of writes, but that will not prevent you from having other data drift on other tables.

How to prevent that from happening again?

The most important part is to have checks in places to detect issues early. Tools such as pt-table-checksum or simple dumps and compares may allow you to detect differences earlier. It is impossible, with the information given, to say exactly what caused that, as mysql configuration will be safe or not depending on the specific setup. Some general advice:

Try using ROW based replication, it is usually more resilient against bad configuration/queries
Check for "unsafe statements for binlog" warnings on the logs
Check the logs for replication changes due to improper maintenance
Make sure replicas are really in read only and they cannot be written or manipulated out of band
Use innodb on all your tables, as well as crash-resilient configuration for your replicas


postgres: list role grants for all users

You can see the users using the psql client with the option \du+(like @dezso already explained).

But, if you want it, you can make a query on pg_roles, eg:

  SELECT
      r.rolname,
      r.rolsuper,
      r.rolinherit,
      r.rolcreaterole,
      r.rolcreatedb,
      r.rolcanlogin,
      r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
ORDER BY 1;

Please, take a look at the documentation for more details:
https://www.postgresql.org/docs/current/static/user-manag.html


How can I represent discrete ages in a relational database?

The ages are discrete values

OK.

  a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account.

So it's a many-to-many relationship?

If so, you just decompose your data into third normal form as usual, expressing the cardinality by means of one extra relation.

Example follows. The SQL dialect is not necessarily PostgreSQL as I do not have an installation handy right now.

Schema

-- persons relation
CREATE TABLE persons (
    personId INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
    -- Any other attributes
);

-- classes relation
CREATE TABLE pupilClasses (
    age INT NOT NULL PRIMARY KEY,
    size ENUM ('small', 'medium', 'large') NOT NULL
);

-- "Tie" relation that expresses the many-to-many cardinality
-- between persons and classes
CREATE TABLE persons_pupilClasses (
    personId INT NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (personId, age),
    FOREIGN KEY (personId) REFERENCES persons (personId)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (age) REFERENCES pupilClasses (age)
        ON UPDATE CASCADE ON DELETE CASCADE
);

Data

-- Let us populate with some data

-- A few classes
INSERT INTO pupilClasses (age, size) VALUES (0, 'small');
INSERT INTO pupilClasses (age, size) VALUES (1, 'small');
INSERT INTO pupilClasses (age, size) VALUES (2, 'small');
INSERT INTO pupilClasses (age, size) VALUES (3, 'small');
INSERT INTO pupilClasses (age, size) VALUES (4, 'small');
INSERT INTO pupilClasses (age, size) VALUES (5, 'small');
INSERT INTO pupilClasses (age, size) VALUES (6, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (7, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (8, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (9, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (10, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (11, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (12, 'large');
INSERT INTO pupilClasses (age, size) VALUES (13, 'large');
INSERT INTO pupilClasses (age, size) VALUES (14, 'large');
INSERT INTO pupilClasses (age, size) VALUES (15, 'large');
INSERT INTO pupilClasses (age, size) VALUES (16, 'large');
INSERT INTO pupilClasses (age, size) VALUES (17, 'large');

-- A few persons
INSERT INTO persons (personId, name) VALUES (666, 'Alice');
INSERT INTO persons (personId, name) VALUES (667, 'Bertrand');
INSERT INTO persons (personId, name) VALUES (668, 'Carlos');

-- Who can teach to whom

-- Alice to 0−3 and 7−8 classes
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 0);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 1);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 2);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 3);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 7);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 8);

-- Bertrand to 7−9, 13, 16−17 classes
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 7);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 8);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 9);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 13);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 16);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 17);

-- Carlos can't teach anyone yet ☹

Logic

Let us get a report on those who can teach any classes

SELECT persons.name, GROUP_CONCAT(pupilClasses.age) classes
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupilClasses
        ON pupilClasses.age = persons_pupilClasses.age
GROUP BY persons.personId;

Result:

+----------+----------------+
| name     | classes        |
+----------+----------------+
| Alice    | 0,1,2,3,7,8    |
| Bertrand | 7,8,9,13,16,17 |
+----------+----------------+

Let us get a report of who can teach to whom, if anyone

SELECT persons.name, GROUP_CONCAT(pupilClasses.age) classes
FROM persons
    LEFT JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    LEFT JOIN pupilClasses
        ON pupilClasses.age = persons_pupilClasses.age
GROUP BY persons.personId;

Result:

+----------+----------------+
| name     | classes        |
+----------+----------------+
| Alice    | 0,1,2,3,7,8    |
| Bertrand | 7,8,9,13,16,17 |
| Carlos   | NULL           |
+----------+----------------+

Who teach class 3?

SELECT persons.name
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
WHERE persons_pupilClasses.age = 3;

Result:

+-------+
| name  |
+-------+
| Alice |
+-------+

Who can teach classes 7 or 9?

SELECT DISTINCT persons.name
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
WHERE persons_pupilClasses.age IN (7,9);

Result:

+----------+
| name     |
+----------+
| Alice    |
| Bertrand |
+----------+

Who can teach large sizes?

SELECT DISTINCT persons.name
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupilClasses
        ON pupilClasses.age = persons_pupilClasses.age
WHERE pupilClasses.size = 'large';

Result:

+----------+
| name     |
+----------+
| Bertrand |
+----------+

Although you do not mention it in your description, I'm willing to bet there is at least one more important relation on your actual business scenario: pupils. And they probably move from one class to the next. A simple way to represent that relationship would be:

-- pupils relation
CREATE TABLE pupils (
    pupilId INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    FOREIGN KEY (age) REFERENCES pupilClasses (age)
        ON UPDATE RESTRICT ON DELETE RESTRICT
);

-- A few pupils
INSERT INTO pupils (pupilId, name, age) VALUES (1313, 'Donald', 7);
INSERT INTO pupils (pupilId, name, age) VALUES (1314, 'Ernest', 7);
INSERT INTO pupils (pupilId, name, age) VALUES (1315, 'Frank', 9);
INSERT INTO pupils (pupilId, name, age) VALUES (1316, 'Gertrude', 0);
INSERT INTO pupils (pupilId, name, age) VALUES (1321, 'Hans', 13);

And some logic examples:

Who teaches whom?

SELECT persons.name teacher, pupils.name pupil
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupils
        ON pupils.age = persons_pupilClasses.age;

Result:

+----------+----------+
| teacher  | pupil    |
+----------+----------+
| Alice    | Donald   |
| Bertrand | Donald   |
| Alice    | Ernest   |
| Bertrand | Ernest   |
| Bertrand | Frank    |
| Alice    | Gertrude |
| Bertrand | Hans     |
+----------+----------+

Who are each person's pupils?

SELECT persons.name teacher, GROUP_CONCAT(pupils.name) pupils
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupils
        ON pupils.age = persons_pupilClasses.age
GROUP BY persons.personId;

Result:

+----------+--------------------------+
| teacher  | pupils                   |
+----------+--------------------------+
| Alice    | Donald,Ernest,Gertrude   |
| Bertrand | Donald,Ernest,Frank,Hans |
+----------+--------------------------+

Who are each pupil's teachers?

SELECT pupils.name pupil, GROUP_CONCAT(persons.name) teachers
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupils
        ON pupils.age = persons_pupilClasses.age
GROUP BY pupils.pupilId;

Result:

+----------+----------------+
| pupil    | teachers       |
+----------+----------------+
| Donald   | Alice,Bertrand |
| Ernest   | Alice,Bertrand |
| Frank    | Bertrand       |
| Gertrude | Alice          |
| Hans     | Bertrand       |
+----------+----------------+

From this a couple things should be clear:

age may be a bit of a misnomer it may not correspond with the actual natural age of pupils attending those classes.
It's probably a good idea to avoid the temptation of treating it as a continuous range. This is why I quoted your statement that these are discrete values.

For the rest, unless I misunderstood, seems a fairly straightforward normalisation problem.

By the way:

  Initially I considered creating 18 boolean fields,

Where? In the person relation?

  but this seems inefficient.

It's not inefficient. It's a red herring.

Normalise your design first. Once you've gained experience with the data you will be handling you can start thinking about introducing optimisations (which may involve denormalisation). Pre-emptive optimisation is just one of those terrible ideas though.


Create a function to generate a column value in a specific format

One way would be to add an ID column in your users table, with a default value based on a sequence object, then add a computed column to create the key using a date conversion to string.
To create the sequence, something like:
CREATE SEQUENCE [dbo].[userid_sq]
 AS [int]
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 500
 CYCLE

I wouldn't limit yourself to a value of 99 if you are not 100% sure you are going to stay under that number of users per day.
You want a fresh cycle every day, right? So you can force it with an evening job/script to ensure it is back at 1 the next day.
ALTER SEQUENCE [dbo].[userid_sq]  RESTART WITH 1;

So just to illustrate the point, say I'm creating a new table, UserDetails, and want to include this logic, I might do something like:
CREATE TABLE UserDetails (
    ID INT NOT NULL DEFAULT NEXT VALUE FOR userid_sq
    ,Created DATETIME DEFAULT GETDATE()
    ,UserKey AS
        'K' +
        CONVERT(VARCHAR(6), Created, 12) +
        RIGHT(CONVERT(VARCHAR(11), 1000 + ID), 3)
    ,UserName VARCHAR(50) NOT NULL
    )

INSERT INTO UserDetails (UserName)
VALUES ('TestUser')

My table content would be:

ID       - 1
UserKey  - K160531001
UserName - TestUser

You asked about indexing, so, ignoring the fact that the above example would be non-deterministic and therefore ineligible to be used a primary key, if you want to index the computed column, you'll need to PERSIST the column in the database. This stores the values to disk rather than as a calculated virtual value.
ALTER TABLE table_name ALTER COLUMN computed_column_name ADD PERSISTED;

Note that there are various restrictions with what you can do with computed columns so I would implore you to read the following information to get a proper understanding.
MSDN Link
Here's a relevant snippet from that article, which also relates to the above example:

A computed column cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition. However, if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns, a computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint. For example, if the table has integer columns a and b, the computed column a + b may be indexed, but computed column a + DATEPART(dd, GETDATE()) cannot be indexed, because the value might change in subsequent invocations.
A computed column cannot be the target of an INSERT or UPDATE statement.


PostgreSQL: Prevent SQL injection for like query with % input

Quoting the like expression

The percent or underscore characters in the user-supplied string must be quoted as documented:

  To match a literal underscore or percent sign without matching other
  characters, the respective character in pattern must be preceded by
  the escape character. The default escape character is the backslash
  but a different one can be selected by using the ESCAPE clause. To
  match the escape character itself, write two escape characters.

For instance:

If the user submits ab%, the code must do:

where title like '%ab#%%' escape '#';

Be aware that the user might submit a string with your escape character in it.
For this reason it has to be escaped itself. If for instance the user submits abc%def#, the code must do:

where title like '%abc#%def##%' escape '#';

Using strpos

Another solution is to check the result of strpos instead of using LIKE to find if a substring is in a larger string. strpos does not have wildcards characters and returns a non-zero number if the substring is found.

select strpos('abcdef', 'cd') > 0 ;
 ?column?
----------
 t

strpos also uses the Boyer Moore Horspool  algorithm which be faster in some cases, especially since PostgresQL 12 where it applies it directly to UTF-8 contents without converting them first to wide chars internally.


Only join to latest entry in subselect in a given range determined by the main select

As a possible answer, I've added a table value function to my view. But there might be much better solutions. I would be happy, if you could post your alternative ideas.

CREATE VIEW rpt.LoadingList WITH SCHEMABINDING AS
SELECT
    h.ID AS ID_Haul,
    h.DescriptiveID,
    ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY ccs.StoredOn ASC) AS Pos,
    cc.PickingNo,
    pin.PickingStorage,
    pin.TargetCountry,
    cc.[ObjID],
    cc.Customer,
    cc.Article,
    cc.Quantity,
    cc.QuantityUoM,
    cc.PackageLength,
    cc.PackageWidth,
    cc.PackageHeight,
    cc.PackageWeight,
    ccs.StoredOn,
    CAST(CASE WHEN h.Depature IS NULL THEN 0 ELSE 1 END AS BIT) AS Departured
FROM dbo.Haul h
CROSS APPLY dbo.GetAllChargeCarriersOnHaul (h.ID) ccs
INNER JOIN dbo.ChargeCarrier cc ON cc.ID = ccs.ID_ChargeCarrier
INNER JOIN dbo.PickingInfo pin ON pin.ID_ChargeCarrier = ccs.ID_ChargeCarrier

----------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[GetAllChargeCarriersOnHaul]
(
    @ID_Haul INT
)
RETURNS @returntable TABLE
(
    ID_ChargeCarrier INT NOT NULL,
    StoredOn DATETIME2(2) NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
    -- Declare local variables
    DECLARE @ID_Storage INT;
    DECLARE @Arrival DATETIME2(2);
    DECLARE @Depature DATETIME2(2);

    -- Load haul data
    SELECT
        @ID_Storage = ID_Storage,
        @Arrival = Arrival,
        @Depature = Depature
    FROM dbo.Haul
    WHERE ID = @ID_Haul;

    -- Exit, if no data was found
    IF @ID_Storage IS NULL
    BEGIN;
        RETURN;
    END;

    -- Select actual data
    INSERT INTO @returntable
    SELECT
        ID_ChargeCarrier,
        MAX(StoredOn)
    FROM dbo.ChargeCarrier_Storage
    WHERE
        ID_Storage = @ID_Storage
        AND (
            @Depature IS NULL AND StoredOn >= @Depature OR
            StoredOn BETWEEN @Arrival AND @Depature
        )
        AND dbo.GetLatestStorageLocationInDateRange (ID_ChargeCarrier, @Arrival, @Depature) = @ID_Storage
    GROUP BY ID_ChargeCarrier;

    RETURN;
END;

----------------------------------------------------------------------------------

CREATE FUNCTION dbo.GetLatestStorageLocationInDateRange
(
    @ID_ChargeCarrier INT,
    @FromDate DATETIME2(2),
    @TillDate DATETIME2(2)
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN;
    RETURN (
        SELECT TOP 1 ID_Storage
        FROM dbo.ChargeCarrier_Storage
        WHERE
            ID_ChargeCarrier = @ID_ChargeCarrier
            AND (
                @TillDate IS NULL AND StoredOn >= @FromDate OR
                StoredOn BETWEEN @FromDate AND @TillDate
            )
        ORDER BY StoredOn DESC
    );
END;


For single machine setups, you can also check out the Cluster Sandbox:
http://www.severalnines.com/resources/mysql-cluster-sandbox-pre-defined-packages
Good luck!
Vinay
http://www.severalnines.com/
Edit By RolandoMySQLDBA 2012-05-15 13:34 EDT
Check out the Training Slides : http://severalnines.com/mysql-cluster-training
IMHO it would be a fine starting point for beginners to install & learn MySQL Cluster.


The invoice number is characters and numbers combined but I want to change these to numerical only in SQL

To answer your question, I did the following (all of the steps shown below are in the fiddle here).

First part - if you wish to keep the old invoice numbers:

Construct and populate the table:

CREATE TABLE invoice
(
  invoice_id VARCHAR (100) NOT NULL
);

INSERT INTO invoice
VALUES
('CC/DDD/2018-2019/3'),
('CC/DDD/2018-2019/6'),
('CC/DDD/2018-2019/7'),
('CC/DDD/2018-2019/9'),
('CC/DDD/2018-2019/12'),
('CC/DDD/2018-2019/23'),
('CC/DDD/2018-2019/27'),
('CC/DDD/2018-2019/122222'),  -- The large numbers are for testing of sorting,
('CC/DDD/2018-2019/244444');  -- thanks to @DougDeden for pointing out problems in this area

I'll step through the logic I used.

SELECT CHARINDEX('/', REVERSE(invoice_id)) AS cutoff FROM invoice;

Result:

cutoff
2
2
2
2
3
3
3
7
7

Get the REVERSE of the string along with the CHARINDEX of / and then obtain the position of the first slash (/) which tells us where the number of interest lies.

Then, I ran:

SELECT
  LEFT(REVERSE(invoice_id), CHARINDEX('/', REVERSE(invoice_id)) - 1) AS the_num
FROM invoice;

Result:

the_num
3
6
7
9
21
32
72
222221
444442 -- note original is 24444

The LEFT function gives us the actual number to be suffixed (added) to the invoice.

Finally, I ran:

SELECT SUBSTRING(invoice_id, 1, 7)
        + REVERSE(LEFT(REVERSE(invoice_id),
          CHARINDEX('/', REVERSE(invoice_id)) - 1)) AS new_invoice_id
FROM invoice
ORDER BY -- CAST(new_invoice_id AS INTEGER);  -- try this more elegant but it doesn't work!
  CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/',
                    REVERSE(invoice_id)) - 1)) AS INTEGER);

Result:

new_invoice_id
CC/DDD/3
CC/DDD/6
CC/DDD/7
CC/DDD/9
CC/DDD/12
CC/DDD/23
CC/DDD/27
CC/DDD/122222
CC/DDD/244444

Note that in order to obtain the correct result, it is necessary to REVERSE back the original one, not very elegant, but these functions are quite performant. Thanks to @DougDeden for pointing out an error in the original answer!

As mentioned above, all of this is in the fiddle here.

Second part - if you wish to renumber the invoices:

@Lennart proposes a simultaneous renumbering, so for completeness, I'm adding a similar solution to my answer. The first steps are the same as above - the SQL does become a bit cumbersome, but it can be done as follows - first, to obtain the new numbers:

SELECT
  REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/', REVERSE(invoice_id)) - 1)) AS the_num,
  ROW_NUMBER()
    OVER (ORDER BY CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/', REVERSE(invoice_id)) - 1)) AS INTEGER))
    AS new_number
FROM invoice
ORDER BY
  CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/',
            REVERSE(invoice_id)) - 1)) AS INTEGER);

Result:

the_num new_number
3       1
6       2
7       3
9       4
12      5
23      6
27      7
122222  8
244444  9

Be very careful when sorting - if you sort INTEGERs as VARCHARs, the results are anomalous - basically, sorting INTs as strings means that the "numbers" are sorted by the first digit, so 32, 332 and 45 will sort as 32, 332 and 45 and not 32, 45, 332.

So, now we have our renumbering - a final step is necessary:

SELECT
  SUBSTRING(invoice_id, 1, 7) +
  CAST(ROW_NUMBER()
    OVER (ORDER BY CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/',
                        REVERSE(invoice_id)) - 1)) AS INTEGER)) AS VARCHAR)
  AS invoice_no
FROM invoice
ORDER BY
  CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/',
           REVERSE(invoice_id)) - 1)) AS INTEGER);

Notice the CAST of the ROW_NUMBER() - if this is not done, an error is thrown Msg 8114 Level 16 State 5 Line 1 Error converting data type varchar to bigint. You can't "add" an INTEGER to a string!

Result:

invoice_no
CC/DDD/1
CC/DDD/2
CC/DDD/3
CC/DDD/4
CC/DDD/5
CC/DDD/6
CC/DDD/7
CC/DDD/8
CC/DDD/9

As mentioned in the comments, this renumbering is probably not a good idea as it will require the maintenance of a correspondance between the old invoice numbers and the new invoice numbers. All of these steps above are shown in the fiddle here.


MySQL 5.7 - B-Tree indexes, performance and fragmentation

BTree blocks inherently go from 100% full (actually about 15/16) to about 69% full after a lot of random INSERTs.  This may account for the 40% you are seeing.  But note, it won't get any slower over time.  Hence, I see frequent OPTIMIZE TABLE as a waste of time, especially if you need the table to be accessible all the time.

And, your 40% faster will rapidly degrade.

I would expect FLUSH TABLES to actually hurt -- since it will remove cached info from RAM.

In order to discuss the query, please show us the query, its EXPLAIN SELECT ..., and perhaps SHOW CREATE TABLE bla.

foo is essentially two tables, one ordered (id, fk_id, datecolumn), the other ordered (datecolumn, id, fk_id).  Plus, I think, a secondary index of (fk_id, id) (no datecolumn).

If fk_id is part of the WHERE clause, none of them may be optimal.

Addenda

(From OP's comment:)

select  id, fk_id, datecolumn, bar.id, bar.name, bar.type
    from  foo
    join  bar  ON bar.id = foo.fk_id
    where  foo.datecolumn between '2016-01-01 05:00:00'
                              AND '2017-01-01 04:59:59'
      and  foo.fk_id in (1, 2, 4)

The optimal indexes are

foo:  INDEX(fk_id, datecolumn) -- 3 disjoint ranges
bar:  PRIMARY KEY(id) -- I assume it is already the PK; if not, then INDEX(id)

Note that you do not currently have the optimal index for foo.  Add it, and your need for OPTIMIZE TABLE should diminish.

As for why the optimize seemed to help -- probably caching.  How much RAM do you have?  What is the value of innodb_buffer_pool_size?

I prefer this pattern:

    where  foo.datecolumn between '2016-01-01 05:00:00'
                              AND '2016-01-01 05:00:00' + INTERVAL 1 YEAR

(No hassles with leap years, datatypes, etc)

More on indexing.


Good news: 150,000 forwarded records isn't actually that bad, depending on what kind of time span we're talking about. Forwarded records are tracked as long as the server is up (with some gotchas around a bug in specific 2012/2014 builds.)

Even when it does work online, your users can notice it depending on your IO throughput, size of the table, number of nonclustered indexes, and your workloads.

Here's how I tackle it:

List the tables with the forwarded records warnings from smallest to largest table size (not number of forwarded records)
Rebuild those tables in order, by hand, a few at a time. Don't automate this - you want to get a feel for how your system responds to rebuilding small tables. You'll learn fairly quickly if your server can keep up.
Keep an eye on your transaction log usage. If your log file is 10GB, and you're trying to rebuild a 50GB heap, you can run into problems. You can mitigate these issues by growing the log out ahead of time during maintenance windows and by doing frequent log backups while you're doing this - but I'll be honest, at 150k forwarded records across the span of a few days, I wouldn't bother going through this much work.
Going forward, work with the vendor to put a clustered key on those tables. Yes, there are times when heaps are appropriate, but if you're running into the forwarded record problem, you're probably not in one of those heap-friendly situations.


Creation of spatial index locks the table for hours

You start a transaction but don't commit the 2nd one, so the table will remain locked.
The SQL Server restart will rollback the transaction containing the CREATE INDEX

Remove both BEGIN TRANSACTION calls and theCOMMIT(or add a final COMMIT TRAN)


Turning off Append only and snapshot on Redis

Yes. If your primary instance crashes and restarts it will be empty; at this point your replica will also become empty, that would be a disaster.
Have a read of the replication docs https://redis.io/topics/replication . It suggests if you really want to avoid writing to storage in your primary (which really shouldn’t be a big deal with modern hardware), you must make sure you haven’t configured your primary instance to automatically restart and you’ll need to make sure you don’t manually restart it. You would make sure a secondary has taken over first and your old primary is now set to serve as a replica of that.


Is it possible to reuse an alias more than once in MySQL query?

Assume there is some query which produces the output:

SELECT {expression1} AS `Qty`,
       {expression2} AS `Option`,
       {expression3} AS `Make`,
       {expression4} AS `Model`
FROM {tableset}
WHERE {conditions}

For to obtain the result which you need you must simply usethis query as a subquery:

SELECT `Qty`, `Option`, `Make`, `Model`,
       CONCAT ( CASE WHEN `Qty` > 1
                     THEN CONCAT(`Qty`, 'x ')
                     ELSE ''
                     END,
                `Option`,
                ' / ',
                `Make`,
                CASE WHEN COALESCE(`Model`, '') != ''
                     THEN CONCAT(' ', `Model`)
                     ELSE ''
                     END ) AS `Ref`
FROM ( SELECT {expression1} AS `Qty`,
              {expression2} AS `Option`,
              {expression3} AS `Make`,
              {expression4} AS `Model`
       FROM {tableset}
       WHERE {conditions} ) AS subquery

fiddle