How to fetch last row order by date and group by id I am trying to skip duplicate history_id and take only last update row

SELECT products.products_id, products.products_date, products.products_link,
       history_products.history_products_name, history_products.history_products_update,
       history_products.history_products_stock, history_products.history_products_status

FROM products JOIN history_products
                ON products.products_id=history_products.history_products_id

WHERE products.products_author_id=:products_author_id
   && products.products_store_id=:products_store_id

GROUP BY history_products.history_products_id

ORDER BY STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p") DESC

If I'm following you correctly, for each row in products, you want the most recent matching row in history_products. This query should do that:

SELECT prod.products_id, prod.products_date, prod.products_link
       history_products.history_products_name, history_products.history_products_update,
       history_products.history_products_stock, history_products.history_products_status
  FROM history_products
         JOIN (
               SELECT products.products_id, products.products_date, products.products_link,
                      MAX(STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p")) as update_date
                 FROM products JOIN history_products
                                 ON products.products_id=history_products.history_products_id
                WHERE products.products_author_id=:products_author_id
                   && products.products_store_id=:products_store_id
                GROUP BY products.products_id, products.products_date, products.products_link
              ) prod ON (    history_products.history_products_id = prod.products_id
                         AND STR_TO_DATE(history_products.history_products_update, "%d-%m-%Y %h:%i:%s%p") = prod.update_date
                        )
;

Here's how it works:

In the subquery referred to as prod, we select the necessary columns from the products table, plus the maximum update date for that product.
Then, we connect back to history_products again, this time to pick up the details of the history_products row for the current products_id value, for the row where the history_products_update value matches the most recent update date.
NOTE: this query does assume that, for any given row in products, there will be one and only one history_products row with a given history_products_update date. If there is more than one history record for a specified date, then additional information would need to be available to determine which history_products record was actually the last one.

Since the ORDER BY in your original question appeared to have more to do with selecting the proper history_products row than with sorting the results, I have not included a sort order for the results. If the results should show the most recently updated product first, through the least recently updated product, you can add the following ORDER BY before the closing semi-colon:

ORDER BY prod.update_date DESC

NOTE: code not tested.


Trim whitespace (spaces, tabs, newlines) I'm on SQL Server 2014 and I need to clean whitespace from start and end of a column's content, where whitespace could be simple spaces, tabs or newlines (both \n and \r\n); e.g.

'    this content    '                          should become 'this content'
'  \r\n   \t\t\t this \r\n content \t  \r\n   ' should become 'this \r\n content'

and so on.

I was able to only achieve the first case with

UPDATE table t SET t.column = LTRIM(RTRIM(t.column))

but for the other cases it doesn't work.

For anyone using SQL Server 2017 or newer

you can use the TRIM built-in function. For example:

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~'
        + TRIM(NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A) FROM @Test)
        + N'~';

Please note that the default behavior of TRIM is to remove only spaces, so in order to also remove the tabs and newlines (CR + LFs), you need to specify the characters FROM clause.

Also, I used NCHAR(0x09) for the tab characters in the @Test variable so that the example code can be copied-and-pasted and retain the correct characters. Otherwise, tabs get converted into spaces when this page is rendered.

For anyone using SQL Server 2016 or older

You can create a function, either as a SQLCLR Scalar UDF or a T-SQL Inline TVF (iTVF). The T-SQL Inline TVF would be as follows:

CREATE
--ALTER
FUNCTION dbo.TrimChars(@OriginalString NVARCHAR(4000), @CharsToTrim NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH cte AS
(
  SELECT PATINDEX(N'%[^' + @CharsToTrim + N']%', @OriginalString) AS [FirstChar],
         PATINDEX(N'%[^' + @CharsToTrim + N']%', REVERSE(@OriginalString)) AS [LastChar],
        LEN(@OriginalString + N'~') - 1 AS [ActualLength]
)
SELECT cte.[ActualLength],
       [FirstChar],
       ((cte.[ActualLength] - [LastChar]) + 1) AS [LastChar],
       SUBSTRING(@OriginalString, [FirstChar],
                 ((cte.[ActualLength] - [LastChar]) - [FirstChar] + 2)) AS [FixedString]
FROM   cte;
GO

And running it as follows:

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~' + tc.[FixedString] + N'~' AS [proof]
FROM   dbo.TrimChars(@Test, NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A)) tc;

Returns:

proof
----
~this
              content~

And you can use that in an UPDATE using CROSS APPLY:

UPDATE tbl
SET    tbl.[Column] = itvf.[FixedString]
FROM   SchemaName.TableName tbl
CROSS APPLY  dbo.TrimChars(tbl.[Column],
                           NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A)) itvf

As mentioned at the beginning, this is also really easy via SQLCLR since .NET includes a Trim() method that does exactly the operation you are wanting. You can either code your own to call SqlString.Value.Trim(), or you can just install the Free version of the SQL# library (which I created, but this function is in the Free version) and use either String_Trim (which does just white space) or String_TrimChars where you pass in the characters to trim from both sides (just like the iTVF shown above).

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~' + SQL#.String_Trim(@Test) + N'~' AS [proof];

And it returns the exact same string as shown above in the iTVF example output. But being a scalar UDF, you would use it as follows in an UPDATE:

UPDATE tbl
SET    tbl.[Column] = SQL#.String_Trim(itvf.[Column])
FROM   SchemaName.TableName tbl

Either one of the above should be efficient for using across millions of rows. Inline TVFs are optimizible unlike Multi-statement TVFs and T-SQL scalar UDFs. And, SQLCLR Scalar UDFs have the potential to be used in parallel plans, as long as they are marked as IsDeterministic=true and do not set either type of DataAccess to Read (the default for both User and System data access is None), and both of those conditions are true for both of the SQLCLR functions noted above.


Full Text Search :SQL Server 2012 I want to enable full text search in sql server the feature already checked in the installation but in sql when i make this query

select serverproperty('IsFullTextInstalled')

The value 0 should be 1 ? How i can fix this issue?

from SQL server configuration manager it's Running ,
checked

and from installation it's

Check if i have enough permissions to run these procedures.

You said that you have already installed the feature,
but when you look at the sql server configuration manager do you see it there?

according to the documentation:

IsFulltextInstalled The full-text component is installed with the current instance of SQL Server.

0 = Full-text is not installed.

1 = Full-text is installed.

NULL = Invalid input, or error.

Here you can find a detailed way to install Full Text on SQL Server 2012:

Directions:

Open the Programs and Features control panel.
Select Microsoft SQL Server 2012 and click Change.
When prompted to Add/Repair/Remove, select Add.
Advance through the wizard until the Feature Selection screen. Then select Full-Text Search.

On the Installation Type screen, select the appropriate SQL Server instance.
Advance through the rest of the wizard.

Source (with screenshots): http://www.techrepublic.com/blog/networking/adding-sql-full-text-search-to-an-existing-sql-server/5546

This link is also a GREAT article, but a bit more comprehensive:

Step-by-Step: Enabling Semantic Search on SQL Server 2012

  Semantic Search is a powerful new addition to SQL Server 2012.
  Semantic Search builds upon the full-text index feature and adds the
  ability for SQL Server to determine key words in documents. Uses can
  include analysis of text to determine similarity between two
  documents, extract key words from documents, or find documents that
  contain key words.

  Step-by-step, enabling Semantic Search on a column in SQL Server 2012
  involves:

1.Install the Full-Text and Semantic Extractions for Search feature

2.Install the Microsoft Office 2010 Filter Packs and Service Pack 1

3.Install, attach and register the semantic language database

4.Create a full-text catalog

5.Create a full-text index with the Statistical_Semantics option enabled

Please visit this link for more details on the above instructions. There are pictures and more detailed info you can find there.

If you have this error message:

  Full-Text Search is not installed, or a full-text component cannot be
  loaded

when you run these procedures below:

EXEC sp_fulltext_service @action='load_os_resources', @value=1;
EXEC sp_fulltext_service 'update_languages';
EXEC sp_fulltext_service 'restart_all_fdhosts';

Then:

Check up what you find in the files below:

File    Path
Summary.txt   %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log

Summary__Date.txt  %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\YYYYMMDD_hhmmss

Detail.txt  %programfiles%\Microsoft SQL Server\nnn\Setup
Bootstrap\Log\YYYYMMDD_hhmmss

Datastore   %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\YYYYMMDD_hhmmss\Datastore

MSI Log Files   %programfiles%\Microsoft SQL Server\nnn\Setup
Bootstrap\Log\YYYYMMDD_hhmmss\.log

ConfigurationFile.ini   %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\YYYYMMDD_hhmmss

SystemConfigurationCheck_Report.htm %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\YYYYMMDD_hhmmss

For unattended installations    %temp%\sqlsetup*.log

and also check sql server error log

Based on the information you get from the logs, you might need to deal with it accordingly, hopefully avoiding having to reinstall anything.

If you see this error message:

  The specified schema name "sys" either does not exist or you do not
  have permission to use it.

Check if you have enough permissions to run these procedures.

In regards to permissions the minimum requirement is:

  On SQL Server, requires VIEW SERVER STATE permission. On SQL Database
  Premium Tiers, requires the VIEW DATABASE STATE permission in the
  database. On SQL Database Standard and Basic Tiers, requires the
  Server admin or an Azure Active Directory admin account.


Aurora MySQL 5.7 randomly fails This is the 5th time. It happens once a week (Tuesday or Wednesday within 03:00-07:00 UTC+0). On the console, it shows available but inaccessible. We try to wait if the instance will recover itself, after ~30 min nothing happens. So I reboot it manually, then it came online again after rebooting (~5 min).

It would be helpful to know what actually went wrong. This is only a dev server with few users and records.

Engine: Aurora MySQL 5.7.12
DB instance class: db.t2.small
Backup time: 16:00-16:30 UTC+0
Maintenance time: sun:17:00-sun:17:30 UTC+0

Below is the only list of available logs after rebooting the instance.

error/mysql-error-running.log.2018-07-24.03 Tue Jul 24 11:14:06 GMT+800 2018    11.8 kB
error/mysql-error-running.log.2018-07-24.04 Tue Jul 24 11:30:00 GMT+800 2018    285.5 kB
error/mysql-error-running.log.2018-07-24.05 Tue Jul 24 12:30:00 GMT+800 2018    31.1 kB
error/mysql-error-running.log.2018-07-24.06 Tue Jul 24 13:30:00 GMT+800 2018    31.8 kB
error/mysql-error-running.log.2018-07-24.07 Tue Jul 24 14:30:00 GMT+800 2018    32.9 kB
error/mysql-error-running.log.2018-07-24.08 Tue Jul 24 15:30:00 GMT+800 2018    29 kB
error/mysql-error-running.log.2018-07-24.09 Tue Jul 24 16:30:00 GMT+800 2018    32.1 kB
error/mysql-error-running.log.2018-07-24.10 Tue Jul 24 17:30:00 GMT+800 2018    27.5 kB
error/mysql-error-running.log.2018-07-24.11 Tue Jul 24 18:30:00 GMT+800 2018    31.7 kB
error/mysql-error-running.log.2018-07-24.12 Tue Jul 24 19:30:00 GMT+800 2018    27.1 kB
error/mysql-error-running.log.2018-07-24.13 Tue Jul 24 20:30:00 GMT+800 2018    22.4 kB
error/mysql-error-running.log.2018-07-24.14 Tue Jul 24 21:30:00 GMT+800 2018    22.8 kB
error/mysql-error-running.log.2018-07-24.15 Tue Jul 24 22:30:00 GMT+800 2018    24.7 kB
error/mysql-error-running.log.2018-07-24.16 Tue Jul 24 23:30:00 GMT+800 2018    24.7 kB
error/mysql-error.log   Wed Jul 25 00:34:45 GMT+800 2018    2.6 kB
external/mysql-external.log Wed Jul 25 00:30:00 GMT+800 2018    7.6 kB

external/mysql-external.log

/rdsdbbin/oscar/bin/mysqld, Version: 5.7.12 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time,ServerHost,User,UserHost,Command,Payload
/rdsdbbin/oscar/bin/mysqld, Version: 5.7.12 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time,ServerHost,User,UserHost,Command,Payload
/rdsdbbin/oscar/bin/mysqld, Version: 5.7.12 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time,ServerHost,User,UserHost,Command,Payload
----------------------- END OF LOG ----------------------

error/mysql-error-running.log.2018-07-24.03 shows: https://pastebin.com/ywmXLR5g.

error/mysql-error-running.log.2018-07-24.04 shows: https://pastebin.com/g1dkR6rj.

error/mysql-error-running.log.2018-07-24.18 shows: https://pastebin.com/g0aAXfaT.

All other logs shows nothing(see photo).

Event Logs

July 24, 2018 at 11:14:14 AM UTC+8  DB instance restarted
July 24, 2018 at 11:13:31 AM UTC+8  Error restarting mysql: Engine bootstrap failed with no mysqld process running...
July 24, 2018 at 11:12:01 AM UTC+8  Recovery of the DB instance is complete.
July 24, 2018 at 11:04:26 AM UTC+8  Recovery of the DB instance has started. Recovery time will vary with the amount of data to be recovered.

CPU Utilization (07-24-2018)

CPU Utilization (07-11-2018 to 07-24-2018)

Special thanks to @WilsonHauck. After 4 weeks of monitoring, Manually upgrading Aurora to the latest version solves the issue.

There have been several bugfixes addressing unexpected restarts on 2.01.1. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Updates.20Updates.html

To manually upgrade your Aurora:

Go to RDS - AWS Console
Navigate to Clusters
Select your cluster
Click Actions >> Upgrade now


string_split into multiple columns Sorry if this question is very basic, but I just can't figure it out, and couldn't find any good answers.

I have a very long list of files, folders and Sizes. I need to Seperate the folders to columns, that I have a column for each folder.

I have a FilePath as a String (eg folder1\folder2\folder3).
I want to seperate this into multiple Columns:

 First  |  Second  |  Third  |  Fourth  |  ...
folder1 | folder2  | folder3 | NULL     | ...
Foldera | folde rb  | folderc | folderd  |

using cross apply string_split(PATH,'\')as folder
I get Folder1.
using row_Number() I can define wich folder I have in my column, but it is always only 1 column.

Actual Example:

select [Parentpath], [Size], [spl].[value] from Files
cross apply string_split([ParentPath], '\') as [spl]

Parentpath                      || Size   ||  Value
Business\Packets\Data\Archive   || 29334  || Business

This is also not pretty, but it allows for as\many\sub\folders\as\you\may\have.

-- borrowing from Brent:
CREATE TABLE #Files (Parentpath varchar(100), Size int);

INSERT INTO #Files (Parentpath, Size)
  VALUES ('Business\Packets\Data\Archive', 29334),
  ('Coach\Loss\Wharf\Current\Blat\Splunge\More', 7337);

DECLARE @s char(1) = CHAR(92), @sql nvarchar(max) = N'SELECT ParentPath, Size';

SELECT ParentPath, Size, value, rn = ROW_NUMBER() OVER
  (PARTITION BY ParentPath ORDER BY CHARINDEX(value + @s, ParentPath + @s))
  INTO #x FROM #Files AS f CROSS APPLY STRING_SPLIT(f.ParentPath, @s) AS t;

DECLARE @max int = (SELECT MAX(rn) FROM #x);

SELECT @sql += N',  Folder' + RTRIM(rn) + N' = MAX(CASE WHEN rn = '
            +  RTRIM(rn) + N' THEN value END)'
  FROM (SELECT rn = ROW_NUMBER() OVER (ORDER BY name) FROM sys.all_columns) AS x
  WHERE rn <= @max;

SET @sql += N' FROM #x GROUP BY ParentPath, Size ORDER BY Folder1'
         +  CASE WHEN @max > 1 THEN N', Folder2' ELSE N'' END + N';';

EXEC sys.sp_executesql @sql;

DROP TABLE #x, #Files;

With this sample data I get the following results:


Oracle Datapump: modify fully qualified objects when cloning a schema I am confronted with a problem for which I have not found an elegant solution:

I need to clone a schema within the same database, so I use datapump's remap_schema import parameter.

The problem is that the PL/SQL Packages contain lots of fully qualified object names (such as select * from FROM_SCHEMA_A.table_name. These qualifiers are cloned. This makes the cloned package invalid.

I could obviously grant all on FROM_SCHEMA_A ... to TO_SCHEMA_A to make the problem go, but this is not what I want. I'd rather want the qualified names to change as well.

The export parameters are

content=metadata_only
directory=DATAPUMP_DIR
dumpfile=clone_schema.dmp
schemas=from_schema_a
exclude=trigger

The import parameters are

directory=DATAPUMP_DIR
dumpfile=clone_schema.dmp
remap_schema=from_schema_a:to_schema_a
transform=OID:n

if you grant privileges (as you mention above), the problem may seem to go away, but your code executing in SCHEMA_B may be accessing data from tables in SCHEMA_A, unless that is what you want.

Unfortunately there are no easy answers to this problem, since remap_schema option of datapump doesn't really support what you are trying to do. However you may try following option (I have done it before),

Extract all plsql code and/or view definition (datapump can do this for you using impdp with sqlfile option),
use sed or any other tool of your choice and replace SCHEMA_A. to SCHEMA_B. where you see objects prefixed. Be careful, occasionally unwanted lines might get changed, so you will have to track that and implement workarounds.
impdp tables,indexes,constraints to SCHEMA_B
using sqlplus connect to SCHEMA_B and run the modified scripts from step 2 to load all plsql code, create views and other dependent objects etc.

It is cumbersome, but short of fixing your source schema, I don't think you have too many choices. Luckily if you are in a situation where code doesn't change that often, you can hold on to converted files and avoid these manual steps until something breaks.

Oh btw, if your pl/sql code looks like "create or replace schema_a.package_name", bad luck, remap_schema won't support that either (until 11.2, I have not tested in 12c yet).


How to create a trigger that checks if a value in 2 fields is present and if it is change it to something else I am new to db, and trying to create a trigger that checks some fields in a table for a value and if the value is present than it updates it to something else. These are my thoughts so far.

CREATE TRIGGER check_Load
AFTER UPDATE ON PODIUM_CORE
FOR EACH ROW ( WHERE PODIUM_CORE.SNAME OR PODIUM_CORE.INTERNAL = "LOAD")
UPDATE PODIUM_CORE.SNAME = "LOAD_GENERAL";

EXECUTE PROCEDURE check_Load();

As documented in the manual a trigger in Postgres consists of a trigger function and a trigger definition.

String constants are written in single quotes in SQL, "LOAD" refers to a column or table name, 'LOAD' is a string (character) constant.

If you want to change something using a trigger, you should use a BEFORE trigger and simply assign the new value to the record. Running a separate DML statement for that is the wrong approach in Postgres.

You didn't really give us much to go with, but I assume you are looking for something like this:

Define the trigger function:

create function check_load()
  returns trigger
as
$$
begin
  new.sname := 'LOAD_GENERAL';
  return new;
end;
$$
language plpgsql;

Then create the trigger:

CREATE TRIGGER trg_check_Load
  BEFORE UPDATE ON podium_core
  FOR EACH ROW
  WHEN sname = 'LOAD' OR internal 'LOAD'
  EXECUTE PROCEDURE check_load();

It's more efficient to only fire the trigger when the specified values are provided, rather than firing the trigger always and check for the value inside the trigger.

But if you want to do that check inside the trigger function it would be something like this:

create function check_load()
  returns trigger
as
$$
begin
  if (new.sname = 'LOAD' or new.internal = 'LOAD') then
    new.sname := 'LOAD_GENERAL';
  end if;
  return new;
end;
$$
language plpgsql;

CREATE TRIGGER trg_check_Load
  BEFORE UPDATE ON podium_core
  FOR EACH ROW
  EXECUTE PROCEDURE check_load();


Select maintenance subplans via database_id I have a SQL Server 2012 maintenance plan called 'backup databases'. This plan has two subplans 'weekly' and 'daily'. I would like a list of databases by ID associated with each of the subplans.

Or, more accurately, I would like to associate database ids with a subplan on a true/false basis.

I can query msdb.dbo.sysmaint.. like so:

SELECT
    *
FROM msdb.dbo.sysmaintplan_plans smp
INNER JOIN msdb.dbo.sysmaintplan_subplans smsp ON smp.id = smsp.plan_id

But I don't see anywhere I can join to master.sys.databases. Is there a way that I can do this?

I can see on this page here: https://technet.microsoft.com/en-us/library/ms188062(v=sql.110).aspx, reference to views that contain information on past backups. But I'm specifically looking for information on what databases are currently scheduled to be backed up.

I can find a list of backups in the msdb.dbo.backupset table, however the backups are not associated to databases, but rather database_guids (https://msdn.microsoft.com/en-us/library/ms186299.aspx).

This is useful in that I know what databases have been backed up, but I would still like to know what databases are registered in the current maintenance plan to be backed up.

Maintenance Plans are actually SQL Server Integration Services packages stored in msdb.

You can see the binary package by looking at msdb.dbo.sysssispackages.

You cannot easily modify the package using T-SQL, nor can you easily inspect the package contents to determine which databases are being backed up.

Instead of using Maintenance Plans, it is advisable to use SQL Server Agent in combination with some pre-designed set of scripts, such as those provided by Ola Hallengren or MinionWare


Does MySQL Workbench lock database during migration process? I am doing a migration of my database from one server (RDS) to another (localhost) via the MySQL workbench tool. I want to know if during this process my database will be locked for write and read operations? The type of my tables is InnoDB.

EDIT:

I guess I deserved the -1 for my brevity and incomplete (i.e. wrong) answer. I was on the phone and had to keep it quick.

Let me try again.

I did a dry run for you on two test servers using the MySQL Workbench 6.3 CE migration wizard.

With the default options the migration is -non- locking. This vanilla method has a downside however. If you migrate while other DML transactions are going on, you might get an inconsistent data set on the destination, as it will not migrate a consistent snapshot as seen in the statement below. This risk is designed to achieve the non locking nature.

Here is an excerpt of the general log for the vanilla run:

151105  7:51:09 10398 Connect   root@my_host_name.com as anonymous on
    10398 Query SET NAMES 'utf8'
    10398 Query USE `test_db_01`
    10398 Query SELECT count(*) FROM `t2`
    10398 Query USE `test_db_01`
    10398 Query SELECT count(*) FROM `t1`
    10398 Quit
    10399 Connect   root@my_host_name.com as anonymous on
    10399 Query SET NAMES 'utf8'
    10399 Query USE `test_db_01`
    10399 Query SELECT count(*) FROM `t2`
    10399 Query USE `test_db_01`
    10400 Connect   root@my_host_name.com as anonymous on
    10400 Query SET NAMES 'utf8'
    10400 Query USE `test_db_01`
    10400 Query SELECT count(*) FROM `t1`
    10400 Query USE `test_db_01`
    10400 Prepare   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t1` ORDER BY `id`
    10399 Prepare   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t2` ORDER BY `id`
    10399 Execute   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t2` ORDER BY `id`
    10400 Execute   SELECT `id`, `intcol1`, `intcol2`, `intcol3`, `intcol4`, `charcol1`, `charcol2`, `charcol3`, `charcol4`, `charcol5`, `charcol6`, `charcol7`, `charcol8`, `charcol9`, `charcol10` FROM `t1` ORDER BY `id`
    10400 Close stmt
    10399 Close stmt
    10399 Quit
    10400 Quit

Another option would be to use mysqldump. This will be -non- locking but, also create a consistent snapshop of the database.

Mysqldump --databases [db name] --user [user] --password --opt --triggers --routines --events --single-transaction | mysql -u[user] -p -h[host_address]


How to find out if rows are refenced from other tables? I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an array and need to determine if these records could be deleted. This is just used for display, no actual deleting is taking place here.

Based on this answer, I can find out if one record can be deleted:

(SELECT 1 FROM case_contacts WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM case_payments WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM invoices WHERE contact_id = 5000 LIMIT 1)
-- etc
LIMIT 1;

I could run this query for every contact ID I'm given, but that seems very inefficient.

Is there a way to send a single query for multiple contact IDs (up to ~500 at once)? Any kind of result would be fine, including only the IDs that can be deleted, or only the IDs that cannot be deleted, or a result set of (contact_id, can_be_deleted).

EDIT: This is what I'm using until I find a more efficient solution:

SELECT  c.id
  FROM  contacts c
 WHERE  c.id IN (1,10,20,1557,5000,15057)
        AND NOT EXISTS (SELECT 1 FROM case_contacts WHERE contact_id = c.id)
        AND NOT EXISTS (SELECT 1 FROM case_payments WHERE contact_id = c.id)
        AND NOT EXISTS (SELECT 1 FROM invoices      WHERE contact_id = c.id);

You may need to alter this slightly for postgres (is is MS SQL syntax) but something like:

SELECT id
FROM   contacts
LEFT OUTER JOIN FROM case_contacts ON case_contacts.contact_id = contacts.id
LEFT OUTER JOIN FROM case_payments ON case_payments.contact_id = contacts.id
LEFT OUTER JOIN FROM invoices      ON      invoices.contact_id = contacts.id
WHERE case_contacts.contact_id IS NULL
AND   case_payments.contact_id IS NULL
AND        invoices.contact_id IS NULL

or:

SELECT id
FROM   contacts
WHERE  id NOT IN (SELECT contact_id FROM case_contacts)
AND    id NOT IN (SELECT contact_id FROM case_payments)
AND    id NOT IN (SELECT contact_id FROM invoices)

should list the IDs that are not referred to in those three tables. The query planner *should * see these as equivalent and optimise accordingly, but if not then the former will be more efficient (the latter risks running the three sub-queries once for every row in contacts).


Risks involved in upgrading to SQL Server 2008 R2 We have quite a number of SQL Servers that need to be upgraded from version 2005 to 2008 R2. Work is planned before the middle of the year as Microsoft is ending its support for the same.

The 2005 SQL Servers are all SP3 and SP4, running on Windows Server 2003 (whose support has already ended, but we have exception of extension for one more year), but where required we might go with a server OS upgrade as well.

These servers include replication (transactional), log shipping, reporting services, and an integration server running SSIS packages.

My question here is not how, rather I would like to know the risks involved or any pre-checks that can be done before planning this upgrade?

Also, will an in-place upgrade be a better plan than side-by-side for this migration/upgrade?

That's a really big question so let's break it up a bit.

What can I do in advance?

Start with some required reading.

2008 R2 Backwards Compatibility
2008 Backwards Compatibility

These links have links to further information such as

Deprecated SQL Server Features
Discontinued SQL Server Features
Breaking Changes
Behavior Changes to SQL Server Features

Read each of them to see what major stuff is changing. Pay particular attention to features you are using.

In addition you should use the Upgrade Advisor.  It checks for installed components and identifies those you will need to fix either before or after the install.

In-Place vs Side-by-side

Lots of pro's and con's here on both sides.

In-Place

Pros

MUCH easier. All of your configurations stay the same for example. Also the connection strings for your applications probably won't need to be changed.
Cheaper. No second set of hardware required.

Cons

Backout is difficult to impossible.  If something goes wrong you are going to have to power through and finish because backout involves creating a whole new server and reinstalling SQL then restoring backups of your tables.

Side-by-side

Basically the pros & cons are the opposite of In place.

Pros

Safer - If something goes wrong you kill the new version and just continue on with the old one.  Then you can try again later.

Cons

It's more expensive because you have to create a new set of instances probably on new servers.
It's more difficult because you have to change connection strings, make sure all of your configurations are the same, etc.

Now you can mitigate the expense of the side-by-side by creating a new instance on the same server, moving everything to it, then uninstalling the old instance. It works and depending on your situation might be the best idea.

General Risk

Honestly the move from 2005 - 2008 R2 isn't that bad.  It's nothing compared to 2000 - 2005 or 2008 R2 - 2012 (mostly SSIS changes).  I'd say with careful planning and reading you should be in good shape.


Converting a SQL Server 2000 Database to SQL Server 2016 I need to restore a SQL Server 2000 database to SQL Server 2016. Is there any possible way to do this without having to install a complete copy of SQL Server 2008, restore to 2008, change the compatibility level and then restore the 2008 backup to 2016? I know this would work but I really don't want to install 2008 R2 just for this single purpose unless I have no other choice.

It is NOT possible to restore a SQL Server 2000 database to SQL Server 2016 without restoring it to 2008 R2 first.

Here is an answer by Aaron Bertrand with details on alternate options to get the data out of the SQL 2000 database https://dba.stackexchange.com/a/99340/60898.


Server warnings on MongoDB startup I recently installed MongoDB on an AWS EC2 instance running Ubuntu.

When I'm starting the MongoDB service, I get some warnings:

$ mongo
MongoDB shell version: 3.0.11
connecting to: test
Server has startup warnings:
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten]
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten]
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2016-04-01T07:28:10.926+0000 I CONTROL  [initandlisten]
>

I think that this is what prevents the migration from Parse.com, as I posted on this question.

How can I ignore these warnings and disable it?

You need to apply what MongoDB Docs say:

https://docs.mongodb.org/manual/tutorial/transparent-huge-pages/

I installed MongoDB 3.0.8 as couple of months ago and got the same warnings. This setting was set by Amazon. First thing to do is create a script that can enable and disable Transparent Pages. Documentation gives this:

#!/bin/sh
### BEGIN INIT INFO
# Provides:          disable-transparent-hugepages
# Required-Start:    $local_fs
# Required-Stop:
# X-Start-Before:    mongod mongodb-mms-automation-agent
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Disable Linux transparent huge pages
# Description:       Disable Linux transparent huge pages, to improve
#                    database performance.
### END INIT INFO

case $1 in
  start)
    if [ -d /sys/kernel/mm/transparent_hugepage ]; then
      thp_path=/sys/kernel/mm/transparent_hugepage
    elif [ -d /sys/kernel/mm/redhat_transparent_hugepage ]; then
      thp_path=/sys/kernel/mm/redhat_transparent_hugepage
    else
      return 0
    fi

    echo 'never' > ${thp_path}/enabled
    echo 'never' > ${thp_path}/defrag

    unset thp_path
    ;;
esac

I use CentOS. I had this script made and I chkconfig'd it as a service so THP is disabled at startup.

In your case, you will have to run the script, restart MongoDB (service mongod restart), login to mongo and that warning should be gone. You need to follow the docs for Ubuntu as it says to install using:

sudo update-rc.d disable-transparent-hugepages defaults


Merge multiple InnoDB tables in MySQL I have a big InnoDB table (7 million records) and I need to speed up the insert/update operations.
For instance, this is what the table looks like:

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

I thought about partitioning the table by year:

CREATE TABLE product_2016 (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

CREATE TABLE product_2017 (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB;

I need to read from the union of all the partitioned tables, so I need to merge them in some way, like you can do with the MERGE engine:

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null
) ENGINE=MERGE UNION=(product_2016, product_2017) INSERT_METHOD=LAST;

Of course I cannot do it in this way because the MERGE engine only works with MYISAM tables.

Is the VIEW an alternative? I don't want to slow down the read operation (the read time should be the same). Are there other alternatives?

Mysql has a built-in partitionning system, why not using it instead of creating several tables then merging them ?

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB
PARTITION BY LIST(year)(
    PARTITION product_2016 VALUES IN (2016),
    PARTITION product_2017 VALUES IN (2017),
    PARTITION product_2018 VALUES IN (2018)
 );

Mysql will split table into 3 files. It will speed up search in index because each index is smaller. It will enhance concurrent access.

But for performance reasons, it is better to partition table in lots of files something like :

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(year, barcode)
) ENGINE=InnoDB
PARTITION BY KEY (barcode)
PARTITIONS 150 ;

I wonder why you have trouble to insert or update this table, 7 million rows, is not that much, I've got partitionned tables with 270 million rows and I don't have trouble with them.

Do you have concurrent inserts or updates ?

Also as year key is not very discriminant (only 2 values 2016 or 2017), it is better to put bar code first in your primary key like that :

CREATE TABLE product (
    year int not null,
    barcode varchar(8) not null,
    primary key(barcode, year)
) ENGINE=InnoDB
PARTITION BY KEY (barcode)
PARTITIONS 150 ;

Not only it will be quicker for Mysql innodb engine to recreate index when you insert a row but there won't be lock only on that file when an insert is done, so other inserts can be done at the same time on other files.

I see that you are talking about MyISAM engine, please note that MYISAM has traditionnaly locks problem, but if your table is partitionned properly, you won't have such lock problem on MYISAM table, so to consider if you have much more selects than inserts/updates and no transactions.


Possible reasons for slow BLOB updates in Oracle? We have an application that transfers .bmp images as BLOBs to an Oracle table. One of the users of the app has complained that it is taking 2-3 seconds to transfer each image. Each image is ~1.2Mb. I've run some tests on our database and there is some lag, but more like 0.8 seconds. The user has an Oracle 9i R2 database. I've run tests on 11g, 10g and 9i and there doesn't seem to be any performance difference between them for me, so I'm guessing it's not because they're running an older database.

Here is the query that's being used:

UPDATE RESULTS_DATA
     SET IMAGE_BLOB=:myBlob
WHERE MEASUREMENT_INDEX=:mIndex
  AND BATCH_ID=:batchId
  AND SYSTEM_ID=:sysId

There are indexes on SYSTEM_ID and BATCH_ID but not MEASUREMENT_INDEX. The problem doesn't appear to get worse when you have large amounts of data in the table.

Can anyone think of a reason that BLOB transfers would be so slow? Is there any way of optimising the table structure to make writing to BLOBs more efficient? Could it just be a case of a slow network? If so, how could I diagnose this?

Thanks!

Here's the table structure:

CREATE TABLE RESULTS_DATA (
SYSTEM_ID       VARCHAR2(15),
BATCH_ID        VARCHAR2(15),
USER_ID         VARCHAR2(40),
MEASUREMENT_INDEX   NUMBER,
IMAGE_BLOB      BLOB,
CONSTRAINT DATA_PKEY PRIMARY KEY (MEASUREMENT_INDEX,SYSTEM_ID,BATCH_ID))
TABLESPACE MEASUREMENT_DATA
LOB (IMAGE_BLOB) STORE AS (TABLESPACE IMAGE_DATA);

CREATE INDEX RESULTS_INDEX ON RESULTS_DATA(SYSTEM_ID,BATCH_ID) TABLESPACE MEASUREMENT_DATA_INDEX;

Here's the output of EXPLAIN PLAN from Oracle SQL Developer:

There are various tricks for some performance improvements in the OCI library(for example chunk size settings). But since you do not have direct access to OCI you should contact you DOA vendor.

If you want to trace user's session from begging you have to create a "LOGON TRIGGER ON SCHEMA" and then call something like dbms_support.start_trace(true) in this trigger.
Or you can set tracing in other session using dbms_monitor.

You should first "prove" that the root cause is BLOB handling. Server side log should give you some numbers. (Just beware - it's not easy to read).

PS: there is also a way how you can register your "interceptor" library in OCI.dll. This will give detailed tracing for the client's side and will tell you how your DAO is working. But this really requires some Oracle experience.


Batch process to delete table from SQL Server backup files I have a set of full backups from a same SQL Server database saved in different files. For example:

01.my-database.bak
02.my-database.bak
03.my-database.bak

I want to delete a specific table from those files to save disk space. There is a batch process to do that?

No, you can't edit the contents of backup files in this way.  You should either compress the backup files or archive them to a different server or storage medium if you require them to be kept.


Automatic index creation for primary vs. foreign keys in Postgresql For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that

  A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

and I wondered whether the author(s) of these lines were overlooking one major point: When I have a parent-child relation (e.g. a cart with cart positions) where the child (cart position) marks its relation to the parent (cart) using a foreign key to the parents (cart) primary key when I want to get all children for a given parent I use the non-indexed foreign key column.

So, does the overhead of not generating an index for foreign key columns by default (in contrast to primary key columns) and letting the user experience suboptimal performance on one hand overweigh the overhead of maintenancing an index which, at smaller table sizes, do only minor improvements of query performance on the other hand?

Perhaps someone has more insights in database implementation details and reasonings leading to this decision and/or can point me to reasons why a default index generation for foreign key columns (in Postgresql) could lead to unnoticed serious problems.

I don't think that - a default index generation for foreign key columns  - would lead to serious problems.

It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not.

If they had taken the opposite decision, then we'd be restricted. There would be an index for each and every foreign key constraint. If someone wanted to have some foreign key columns not indexed, they'd be limited or forced.

Now there is only the additional burden of having to add indexes to match the foreign key constraints - for those who want to.

Personally, I almost always add respective indexes for a foreign key constraint. But often, the index does not match exactly the constraint. For example the Fk may be TABLE c (..., FOREIGN KEY (a_id) REFERENCES a (a_id) ) and the index might be single (a_id) or composite (a_id, b_id) because it helps other queries as well, not only those that use a_id.

Or I might have (more rare cases of) 2 foreign key constraints that can be covered but 1 index - eg.: FK (a_id) REFERENCES a (a_id), ... FK (a_id, b_id) REFERENCES b (a_id, b_id).

When creating an index, there are also many options, like the type of index (btree, hash, Gin, Gist and other exotic types), so we're free to choose what is best for our situation.

Also note that Oracle, SQL Server, DB2 and Firebird also do not automatically create indexes for FOREIGN KEY columns, so it's not like that PostgreSQL developers were alone in this. And I'm sure there were many discussions on the matter and they considered both options, I'm only speculating on whey they came to the decision. Looking through the old Postgres discussion lists may reveal more about the actual reasons they considered.


Seemingly equivalent queries take vastly different execution times I am encountering an issue where two different ways of executing the same query are taking very different execution times. My guess is that in one way or another, the slow query is unable to utilize my index.
I have a large table of data points (Data)

TABLE(assetId INT, dataItemId INT,timestamp DATETIME(2),value REAL)

There is a full table unique clustered index here. assetId is the owner of the datapoint, dataItemId is other metadata for the datapoint, including a label. So there is a table DataItems

TABLE(dataItemId INT, labelId INT, unitId INT)

And finally a table of Labels (there is another table just like Labels for Units). I have FK constraints on the DataItems/Labels/Units tables.

TABLE(labelId INT, label VARCHAR(128))

I would like to make a query for data across a time range asking for all datapoints with a certain label for a particular asset. Something like:

--QUERY #1
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Label.label IN ('Eng_RPM','Eng_Load','Eng_OilTemp')
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

However, this query (returns 21000 rows) takes about 38 seconds to execute. So I tried an alternate query, where I use dataItemId instead of Labels:

--QUERY #2
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Data.dataItemId IN (
   SELECT dataItemId from DataItems
   JOIN Labels ON DataItems.labelId=Labels.labelId
   WHERE label in ('ENG1_Eng_Load','ENG2_Eng_Load','ENG3_Eng_Load')
   )
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

I didn't expect this to work any better, and it didn't. However, I then took the output of the nested select in the WHERE clause, and used it as a constant:

--QUERY #3
SELECT Data.timestamp,Labels.label,Data.value
FROM Data
JOIN DataItems on DataItems.dataItemId = Data.dataItemId
JOIN Labels on DataItems.labelId = Labels.labelId
WHERE Data.assetId=134
AND Data.dataItemId in (618,654,690)
AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'

This query runs like a dream with <1s query times. I don't understand why though - it seems to me that the nested query for finding the dataItemId from a label should execute first, and I verified that it is a trivial query. From there, why is there a difference between the way #2 and #3 execute, and why does it take so much longer? I am new to databases in general, and to SQL server/ Management studio, so my apologies if I have overlooked some wonderful feature for analyzing this problem.

Edit:

Indices/PKs are as follows:

 --Data:
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20160725-145924] ON      [dbo].[Data]
 (
[assetId] ASC,
[dataItemId] ASC,
[timestamp] ASC,
[value] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--DataItems
ALTER TABLE [dbo].[DataItems] ADD PRIMARY KEY CLUSTERED
(
[dataItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--Labels
ALTER TABLE [dbo].[Labels] ADD PRIMARY KEY CLUSTERED
(
[labelId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I have viewed the execution paths and I did find that the slow queries are not using the Clustered index on Data - they are using a different Non-clustered index that I did not intent to keep (on assetId,timestamp)

SELECT Data.timestamp, Labels.label, Data.value
FROM Data
JOIN DataItems
      on DataItems.dataItemId = Data.dataItemId
     and Data.assetId = 134
     AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'
JOIN Labels
      on DataItems.labelId = Labels.labelId
     AND Label.label IN ('Eng_RPM','Eng_Load','Eng_OilTemp')

maybe add a non-clustered index on Label.label


Creating a USER using the imp command I was told I can use exp to export a certain USER, then DROP the user and with the imp command I can recreate that USER with all of its tables.

Is it true?
How do I do it?

I already have an ADMIN user with just about all privileges (there are 204 of them if it matters).

I tried various options of exporting the USER with the ADMIN account or with the USER's account, but no matter what I do the imp command fails if I try to run it when the USER has been dropped.

Example command:

-- From CLI:
exp USERID=MYUSER/PASS@INSTANCE FILE=mydump.dmp FULL=y
-- From SQLPLUS with admin user
DROP USER MYUSER CASCADE;
-- From CLI:
imp ADMIN/PASS@INSTANCE FILE=mydump.dmp FULL=y
-- The above command will put the contents of MYUSER into the ADMIN account ...
imp ADMIN/PASS@INSTANCE FILE=mydump.dmp FROMUSER=MYUSER TOUSER=MYUSER
-- The above command fails with  the below message:

. importing MYUSER's objects into MYUSER
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
Import terminated successfully with warnings.

To answer my own question - the answer is NO - the imp command cannot create the user for me.


Memory problems with MySQLWorkbench I'm currently using MySQLWorkbench 5.2.40 on my PC (8GB rRM, Corei7). I have a table with almost 1.5 million records, which I need to visually look over. Surprisingly, the total size does not exceed 200 MB. The problem, when I try to select all the records to view them (which is necessary for me), I get an error:  Error Code 2008: MySQL Client ran out of memory. I could not solve this issue.
Questions

Are there any recommendation/options other than MySQLWorkbench to view my tables in a way that guarantees there will be no memory issues ?
Will there be any harm to install two DB systems in the same machine ?

If you really need to look at all the records, you should take the strain off of MySQLWorkbench. It already has enough on its plate micromanaging MySQL.
SUGGESTION #1 : Use MySQLWorkbench from Another Workstation
Install MySQLWorkbench on another server that is not running MySQL. Remotely connect to MySQL from the other Workstation. By divorcing MySQLWorkbench from MySQL, MySQLWorkbench can focus on what does best, micromanage the data and display at the user's whim. MySQL can focus on doing key caching, query caching, InnoDB, DB Connections, read/writing threading, and all other memory-consuming tasks. This will end the tug-of-war for RAM by both products. Even the OS will reap more of the spoils of war for RAM.
SUGGESTION #2 : Create a Text File and View it in some Editor
Install mysql on Another Workstation. Do not run the server on it, but use the mysql client program. To get data from the table mydb.mytable, from the second server, run this:
C:\> set DESTFILE=C:\subfolderofyourchoice\Display.txt
C:\> mysql -hIPOfMySQLServer -uroot -p --table -Ae"SELECT * FROM mydb.mytable" > %DESTFILE%

Once the file is created , use a text editor to display the file. I suggest the following:

Notepad
vi (yes I said, vi for Windows, I use it today)
Microsoft Word

Give it a Try !!!


MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table? In our system, we don't use MyISAM - only InnoDB (and occasionally, MEMORY).

Is it safe to set key_buffer_size to 0? Or there are unexpected side effects? Based on the manual, it seems it's safe.

In the MySQL Docs for 5.5, 5.6, 5.7, and 8.0 the minimum value is 8.
If you set it to 0 in my.cnf and restart mysqld, it may go to 8 anyway.
Since key_buffer_size is dynamic, login to mysql and run
mysql> SET GLOBAL key_buffeer_size = 0;
mysql> SELECT @@global.key_buffer_size;

Whatever the value is, so be it. The storage engine layer will handle it.
Please note that MyISAM caches index pages only. It was probably simpler to make the default minimum a very low nonzero value of 8 (which is exactly a byte) rather than code logic to see if the key cache is enabled with enough RAM.
UPDATE 2019-06-10 13:16 EDT
root@vt-mysql:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@global.key_buffer_size;
+--------------------------+
| @@global.key_buffer_size |
+--------------------------+
|                 16777216 |
+--------------------------+
1 row in set (0.05 sec)

mysql> set global key_buffer_size = 0;
ERROR 1438 (HY000): Cannot drop default keycache
mysql> set global key_buffer_size = 8;
ERROR 1438 (HY000): Cannot drop default keycache
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.6.33-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.05 sec)

mysql>

The documentation is wrong: You cannot resize the global buffer dynamically.
I added key_buffer_size = 0 and restarted mysqld. It ricocheted back to 16M.
I added key_buffer_size = 8 and restarted mysqld. It ricocheted back to 16M.
root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~#
root@vt-mysql:~#
root@vt-mysql:~#
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 0
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 1991
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 8
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 2112
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 2M
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 2234
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~#

I guess the only correct thing I said was : Whatever the value is, so be it. The storage engine layer will handle it.


What is cool-down time in Alibaba Cloud Auto Scaling? I am using Auto scaling on my Alibaba Cloud Elastic Compute Service Instances. I am not aware of what the cool-down time is and the implications?

I had a quick check of the cool-down time documentation  and it appears to refer to the time in which auto scaling will not occur after a previous auto scaling event has completed.


Return the first row of a group in MySQL This query gives me the 50 closest addresses around a point:

 SELECT
     company_has_address.companys_id,
     (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-      86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
 FROM
     geocodes
         LEFT JOIN
     company_has_address ON company_has_address.address_id = geocodes.address_id
 WHERE
     company_has_address.companys_id != 5884
 ORDER BY distance ASC
 LIMIT 50

Now, to make this useful, I need to join two tables: company_has_address, which is essentially a tuple of id, company_id, and address_id,

and then join (again) the company table.

This all works. Kind of.

The problem I have is that I want a list of the 10 closest DISTINCT company names even if a company has more than one address.

So, let's say company ABC has two addresses - a billing address and a shipping address. They are next door to one another. They show up TWICE in the first query.

Now, prior to MySQL 5.7, I could use GROUP BY to (randomly) pick one of the addresses and it would just give me one result. That worked great. But, now with 5.7, this no longer works, and produces a row for each of the addresses.

Question: how do I filter the first join (company_has_address) to only take the first company_id listed there so that when I join the company table, I can get unique listings? (I don't care if it's the shipping or PO address. They are - geographically - in the same place so which address it chooses is irrelevant).

The next query is not doing exactly what you ask for in your title, but it conceptually does what you're looking for.

The inner SELECT computes the distances for all geocodes (JOIN company_address). The outer SELECT GROUPs BY company_id, gets the "minimum distance", sorts through it; the LIMIT 50 is respected.

SELECT
    companys_id, min(distance) AS distance
FROM
(
    SELECT
         company_has_address.companys_id,
         (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
    FROM
         geocodes
         LEFT JOIN company_has_address
             ON company_has_address.address_id = geocodes.address_id
    WHERE
         company_has_address.companys_id != 5884
) AS s0
GROUP BY
     companys_id
ORDER BY
     distance ASC
LIMIT
     50 ;

This query might not be efficient because it will scan your whole geocodes (x company_has_address) tables, but it does the job.

I would change the LEFT JOIN to a JOIN (does it make sense to have a geocode (address) with no company?).

Check it at http://rextester.com/NCBT28574

Side note: I actually don't know of any simple way to accelerate your search if the table is big; but you could probably "prefilter" your search within a certain (lat+delta_lat, lng+delta_lng)-(lat-delta_lat, lng-delta_lng) "box", and have proper indexes on lat and lng; this may not be perfect, or "prefilter" too much, but may speed up a lot your queries if the number of points is big enough.

Addendum

If you want to retrieve (lat, lng), use:

SELECT
    companys_id, min(distance) AS distance, geocodes_latitude AS lat, geocodes_longitude AS lng
FROM
(
    SELECT
         company_has_address.companys_id,
         (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance,
         geocodes_latitude, geocodes_longitude
    FROM
         geocodes
         LEFT JOIN company_has_address ON company_has_address.address_id = geocodes.address_id
    WHERE
         company_has_address.companys_id != 5884
) AS s0
GROUP BY companys_id
ORDER BY distance ASC
LIMIT 50 ;

This is non-standard SQL (you cannot mix aggregates with values not GROUPed BY, however, mySQL allows for it, and may give you the (lat, lng) corresponding to the min distance. Other ways of getting to the same place involve the use of CTE (not yet on mySQL, already on MariaDB) or TEMP tables.


I am doing data archiving on some tables in my database.
  ...

  Then transfer records i want from original table to the new table.

  ...

  After that, I would add back those
  constraints,triggers,indexes,statistics associated with table A back
  into it. That's where some stats I tried to add, it would throw me the
  error, only happens with some stats i am trying to create.

All this is fine except for transfering old statistics.

Statistics are used to build optimal execution plan, they reflect data distribution in your columns.

When you'll query your new table, all the missing statistics will be recreated unless you disabled AUTO_CREATE_STATISTICS (the option is ON by default).

If you set AUTO_CREATE_STATISTICS to OFF and want to recreate all the statistics that existed on the old table you should just recreate them, all that you need for this is to know on what columns old statistics were created.

This way you'll get fresh statistics on your new table.

The undocumented option that you was trying to use serve another purpose:
it's used with update statistics to recreate the same statistics in another environment. Here "the same" means not to use the same column to create statistics, but set another, not actual statistics, statistics taken from another table with another data distribution, on your table.

I mean if your original table had 1000 rows and half of them had col1 = 0 and another 500 rows had col1 = 1, you could create the same table on another server, put there only one row, and transfer data distribution as 500 rows having 0 + 5000 rows having 1. And this is not what you want. You want real data distribution on your new table, so just create the statistics on col1 that will reflect what is now in this table without using WITH STATS_STREAM.


Does the Oracle database username exist for each Linux account? Does the Oracle database username exist for each Linux account?

The former employee left the company and can not find the details at this time.

I can log in to the Linux root account and run a DB query through sqlplus.
If I log in as the Linux oracle account, I can not log in with the username that was logged in as the root account.

I do not know which setting I made.

I have a few questions,

As the title suggests, does the Oracle database username exist for every Linux account?
I'm wondering if there is a database username that can only be logged in to the Linux root account.
I would like you to give me a general idea of what configuration should be changed to run as the root account in the Linux oracle account.

Does the Oracle database username exist for every Linux account?

No - Operating System accounts and Oracle database accounts are unrelated.


Is pgtune still valid for newer PostgreSQL versions? Since pgtune has not been updated since more than 4 years ago, should it still be used with newer PostgreSQL versions? (say, >=9.3)

What about the online version?

There is an active fork of PgTune maintained here. That said, I don't know of anyone that uses it.


Postgres - default date format when interpreting strings What is the default data format that postgres uses when confronted with a character representation of a date?

These SQL statements all work, and the character string is always correctly interpreted as 11th of December 2013. In these examples there is no scope for mis-interpretation. (mydate is defined as timestamp)

SELECT * FROM mytable WHERE mydate = '11-Dec-2013';
SELECT * FROM mytable WHERE mydate = '11-2013-Dec';
SELECT * FROM mytable WHERE mydate = '2013-11-Dec';

These versions get interpreted as different values

SELECT * FROM mytable WHERE mydate = '11-12-13';
SELECT * FROM mytable WHERE mydate = '11-13-12';
SELECT * FROM mytable WHERE mydate = '13-11-12';

I assume there's a default format setting somewhere which postgres use to decide whether dates are read as "dd-mm-yy", "mm-dd-yy" or "yy-mm-dd" (or any other combination)

What is the default format and how do I change it?

I'm aware that I can explicitly define the format like this....

SELECT * FROM mytable WHERE mydate = TO_DATE('11-12-13','DD-MM-YY');

But implementing that would require a lot of code changes for me, and I'm aware of the SET datestyle command, but that appears to limit me to a set of explicit formats rather than allowing custom formats.

There is the datestyle parameter which you can set to something like ISO, DMY or ISO, MDY to influence how such ambiguous dates are interpreted.

But if you allow free-form date input, PostgreSQL's heuristics are bound to get it wrong at some point. So to make your program robust, always force your dates into a certain format and either use the ISO format and rely on automatic conversion or use to_date to parse the strings.


SQL Server Change Data Capture order of records Does anybody know if change data capture functions return always data in the order of occurence?. In a CDC system is quite important to get the data modifications in the order they have been generated.

Looking to the cdc.fn_cdc_get_all_changes_XXX functions, there is no order by clause in any of the underlying queries.

Thanks

Let me quote from the manual:

Track Data Changes (SQL Server)

  Change tracking is based on committed transactions. The order of the changes
  is based on transaction commit time. This allows for reliable results to
  be obtained when there are long-running and overlapping transactions.
  Custom solutions that use timestamp values must be specifically
  designed to handle these scenarios.

and cdc.fn_cdc_get_all_changes_ (Transact-SQL) says:

  Commit LSN associated with the change that preserves the commit order of the
  change. Changes committed in the same transaction share the same commit LSN
  value.

for one of the returned columns (_$start_lsn) in get_all_changes.


Migration 100 TB of data from SQL Server 2008 to 2016 We are planning to migrate to 100 TB of data from one of our client to new SQL Server environment. We just started receiving information from client. It will take couple of months to complete the project. But, can anyone please provide detail plan for migration such a large number of SQL Servers and data. New environment will have 4 fail-over cluster node at 4 different location.

Current environment: SQL Server 2008 R2
New environment : SQL Server 2014/16

The most important part of your question is:

  can anyone please provide detail plan for migration

And I've got great news: Microsoft used to build a really detailed Upgrade Technical Guide (429-page PDF) that covered all kinds of details. They haven't updated it since 2014, but the information in there is still really useful for folks who are just starting from scratch and looking for guidance.

The bad news: SQL Server has only gotten more complicated since then. The 429-page length should help explain why it doesn't get updated frequently - it's expensive to build.


Representing an IS A relationship in a SQL database Let's say I want to store information about several persons in an database. Every person can have no, one or more roles. Each role has some attributes like a name, number and so on (the roles have different attributes).

In my opinion this relationship between a Person and a Role can be described as "IS A". Am I right?

I can see how colloquial English (or ESL) might indicate that a person indeed "is" a job role. However, you're dealing with a system of possibilities and ownership rather than the fact that, at least Americans, like to reduce a person's existence to their career role.

"She is a police officer" - works during standard conversation.

"He has a role of police officer" - reflects the possibility of no, one, or more roles that a person may own. You're dealing with composition not subtyping.

CREATE TABLE is one of the most basic aspects of SQL... you're only hurting yourself if you don't take the time to actually learn the language. Here's one good starting resource. Your next step will likely be to add a Foreign Key constraint.


Install Oracle Database 12c Error unable to check whether the location specified is on CFS ps://i.stack.imgur.com/0DX0d.png

I am trying to install the Oracle database 12c on the Windows 10 Laptop.

Have tried disk C and disk E, got the same error.

And I tried to change the file properties, change the security to allow the full control.. And still got the same error message.

Anyone could help me with this? Thank you!!!!

there is two solutions for this:
1- install Oracle as administrator
2- create a folder in C or E drive then right click it, go to security tab, and change permission for the user (grant him owner access on that folder)


Strange SQL Statement Behavior I have a simple SQL statement (executing on a SQL Server upgraded from 2000 to 2008 R2):

SELECT TOP (1)
    DocNox = CONVERT(integer, SUBSTRING(DocNo, 2, LEN(DocNo)-1)
FROM Tbl1
WHERE
    Fld1 = 19
    AND Fld2 = 1
    AND Fld3 = 1
    AND DocNo NOT LIKE '%-%'
ORDER BY
    CONVERT(integer, SUBSTRING(DocNo, 2, LEN(DocNo)-1) DESC;

Sample data (Serial is an IDENTITY column)

Serial   DocNo    Fld1   Fld2   Fld3
211      A1       19     1      1
212      A2       19     1      1
213      A003     19     1      1
214      X1-C1-1  20     1      1

Executing the query produces this error message:

Error converting nvarchar value '1-C1-1' to a column of data type int.

This is unexpected because the row containing that value ought to be filtered out by the WHERE clause before the conversion in the SELECT and/or ORDER BY clauses.

I have noticed that the following changes prevent the error occurring:

Changing the condition WHERE Fld1 = 19
Copying the data to another table with the same structure
Removing the TOP (1)
Removing the ORDER BY
Changing the ORDER BY to a different column (e.g. Serial)

But the error still occurs in the following circumstances:

Importing the whole table to another database
Running the same statement against a different database with the same data

I also made a change to the statistics that prevented the error but I cannot remember exactly what I did.

Can any one explain what is happening here?

This is normal. T-SQL does not generally specify an evaluation order. Intuitively you would evaluate the where clause first, but that does not have to be the case. SQL Server decided against that which is entirely valid.

Currently, SQL Server does not offer a construct to be 100% sure never to hit this problem. There is a 99% sure technique though: Wrap a case expression around anything that is dangerous:

case when DonNo like '%-%' then (/* convert */) end

This works almost always except in very few corner cases documented on Microsoft Connect. I don't have them at hand right now.


How to recover database with MDF file only I am trying to recover a couple dev-dbs where the data directory was on a spanned partition that was inadvertently deleted.  We were able to recover the folders/files using a recovery software, however the actual MDF file seems to have some issues.

Any attempt to attach or repair will fail with the following:

  SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x00000000; actual signature: 0x7bd02384). It occurred during a read of page (1:0) in database ID 9 at offset 0000000000000000 in file 'G:\data\database.mdf'.

Steps I've tried:

create database for attach
dbcc checkdb(database, repair_allow_data_loss) - after creating empty db and copying MDF file over the new one)

Note - the recovery process identified several different versions of the same file with different extension, I already tried with each files, same error.  There are no backups.

Anything else that could be tried?

A torn-page detection error on page (1:0) indicates the primary page of the primary database file is corrupted.  This page contains a multitude of important details about the database.  A sample output of DBCC PAGE for one of my databases shows:

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED

File Header Data:

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 157
Memory Dump @0x000000000CFEA371

0000000000000000:   30000800 00000000 17000000 0016004b 004b004d  0..............K.K.M
0000000000000014:   004f0053 0057005b 005f005f 00690069 0069006d  .O.S.W.[._._.i.i.i.m
0000000000000028:   00710075 00790079 00790079 0083008d 009d004c  .q.u.y.y.y.y......L
000000000000003C:   3f4c23ac 53f24583 29615c2f 5ddaba01 00010000  ?L#¬SòE)a\/]Úº.....
0000000000000050:   19000000 32000080 0c000000 0000007b 000000d8  ....2..........{...Ø
0000000000000064:   0000000a 00000200 00000000 00ffffff ff001000  .............ÿÿÿÿ...
0000000000000078:   00000000 00000000 000000da 0000000c 0a000049  ...........Ú.......I
000000000000008C:   0015eb49 62ffe8ab 43be1bdc 9847daaa 7d        ..ëIbÿè«C¾.Ü.GÚª}
BindingID = 234c3f4c-53ac-45f2-8329-615c2f5ddaba                         FileIdProp = 1
FileGroupId = 1                     Size = 6400                         MaxSize = 12800
Growth = 3200                       Perf = 0                            BackupLsn = [NULL]
FirstUpdateLsn = (123:216:10)       OldestRestoredLsn = [NULL]          FirstNonloggedUpdateLsn = [NULL]
MinSize = 512                       Status = 0                          UserShrinkSize = 65535
SectorSize = 4096                   MaxLsn = [NULL]                     FirstLsn = [NULL]
CreateLsn = (0:0:0)                 DifferentialBaseLsn = (218:2572:73)
DifferentialBaseGuid = 6249eb15-e8ff-43ab-be1b-dc9847daaa7d              FileOfflineLsn = [NULL]
FileIdGuid = [NULL]                 RestoreStatus = [NULL]              RestoreRedoStartLsn = [NULL]
RestoreSourceGuid = [NULL]          HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]
TxfBackupLsn = [NULL]               FstrContainerSize = [NULL]          MaxLsnBranchId = [NULL]
SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = [NULL]                ReadWriteLsn = [NULL]               RestoreDifferentialBaseLsn = [NULL]
RestoreDifferentialBaseGuid = [NULL]RestorePathOrigin = [NULL]          DatabaseEncryptionFileState = [NULL]
FCBFileDEK = [NULL]

If the first page is corrupted, that likely means you've not correctly fixed the deleted filesystem.

Clearly, having a well-tested recovery plan, even for development databases, might be considered A Good Thing™


Model data with many to many and overrides I'm having some trouble modeling the database for this particular scenario in Postgres:

We have a set of toys that contain several pieces. Each piece is associated with one or more materials. All of these relationships are many to many, because a piece can be placed in many different toys, and a material can be associated with many different pieces.

Here's where I lack the knowledge on implementation. There's another many to many relationship, where a toy can be made by many factories, and a factory can manufacture many types of toys. In the general case, for a particular toy, we want to know what pieces it has, and which materials are associated with those pieces. However, there's a case where the material can be different for a given piece depending on the factory the toy is made in.

In short, there's a "default" case where we know that, for a given toy, we have a set of pieces, and for each of those pieces, we have a set of materials. But, for an arbitrary factory, the material associated with a piece is different.

The furthest I've gotten is putting this information in the piece_material junction table:

| piece_id  | material_id  | factory_id |
|-----------|--------------|------------|
| 1         | 1            | <null>     |
| 1         | 2            | <null>     |
| 1         | 3            | 1          |

But this obviosuly doesn't work because I don't have information indicating which material is being replaced in a particular factory. I'm looking for a query that will get associated materials with a piece, and only the overrides if there are some present (i.e. in the last row of the above example, if it's overriding material 2, that would only get materials 1 and 3 back).

This doesn't seem complex, just normal relationships, just introduce a "Part" table which represents the physical aspects of your "Piece" (I imagine the part for the toy, while it can be made of something different, has to have the same physical parameters - length/width etc). The design I can imagine from your description:

Note the "ToyDefaultPiece" table which would resolve which piece is used by default. I have added in some extra fields which I can imagine would exist in a Toy design to hopefully make it more clear what the entity represents.

In addition, I can imagine you would query Toys/Parts/Pieces with something like:

SELECT ToyName
    , ToyPart
    , PartName
    , PartLength
    , COALESCE(def_pc.PieceID, pc.PieceID) as PieceID
    , COALESCE(def_F.FactoryID, pc.FactoryID) as FactoryID
    , COALESCE(def_F.FactoryName, pc.FactoryName) as FactoryName
    , COALESCE(def_M.MaterialName, pc.MaterialName) as MaterialName
FROM Toy t
INNER JOIN ToyPart tp ON t.ToyID = tp.ToyID
INNER JOIN Part p ON tp.PartID = p.PartID
INNER JOIN Piece pc ON p.PartID = pc.PartID
INNER JOIN Factory f ON pc.FactoryID = f.FactoryID
INNER JOIN Material m ON pc.MaterialID = m.MaterialID
LEFT OUTER JOIN ToyDefaultPiece def ON t.ToyID = def.ToyID
    AND p.PartID = def.PartID
LEFT OUTER JOIN Piece def_pc ON def.PieceID = def_pc.PieceID
LEFT OUTER JOIN Factory def_F ON def_pc.FactoryID = def_F.FactoryID
LEFT OUTER JOIN Material def_M ON def_pc.MaterialID = def_M.MaterialID

Using the COALESCE functions will enable you to override pieces with values you store in the ToyDefaultPiece table.


How can I successfully decrypt column data through the whole database safely? We're moving to Azure SqlDb but as part of that, we're needing to migrate away from  EncryptByPassPhrase as it's not supported.

As such, we tried the process on the weekend but we ran into the log filling up and the script stopping midway through.

What are my options here? There will be zero usage of the DB during the process, can/should I change the DB to simple from full recovery model temporarily? I don't really think we care about the individual transactions during the process, just the end result.

Also, given we're doing a compute intensive task - should I bump up the CPU resources temporarily to hopefully make it go faster? It took 9+ hours to fail, I'm wondering if the log interfered.

This is:

SQL 2019 (15.0.2000.5)
DB compat is 150
We've disabled all triggers/jobs during this process

Here's part of the script:

DECLARE @Password VARCHAR(255) = '<some super cool long very high entropy password here>';

SET NOCOUNT ON;

DECLARE @TestDecryption VARCHAR(255);
SELECT TOP(1)@TestDecryption = CONVERT (VARCHAR(50), DECRYPTBYPASSPHRASE (@Password, HealthNumber_Enc))
FROM Patients.Patient
WHERE ProvinceCode = 'ON'
      AND HealthNumber_Enc IS NOT NULL
ORDER BY CreateDate DESC;
PRINT 'Test: ' + @TestDecryption;

IF @TestDecryption IS NOT NULL -- this is to ensure that our password actually works
BEGIN
    ; DISABLE TRIGGER ALL ON DATABASE;

    -- systematically decrypt data in all affected tables
    UPDATE Users.Activity
    SET [Description] = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Description_Enc)), [Description]);
    UPDATE Users.Activity
    SET Description_Enc = NULL;

    UPDATE Contact.[Address]
    SET Address1 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address1_Enc)), Address1), Address2 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address2_Enc)), Address2), Address3 = ISNULL (CONVERT (VARCHAR(1000), DECRYPTBYPASSPHRASE (@Password, Address3_Enc)), Address3), City = ISNULL (CONVERT (VARCHAR(255), DECRYPTBYPASSPHRASE (@Password, City_Enc)), City), PostalCode = ISNULL (CONVERT (VARCHAR(20), DECRYPTBYPASSPHRASE (@Password, PostalCode_Enc)), PostalCode), Latitude = ISNULL (CONVERT (DECIMAL(9, 6), CONVERT (VARCHAR(10), DECRYPTBYPASSPHRASE (@Password, Latitude_Enc))), Latitude), Longitude = ISNULL (CONVERT (DECIMAL(9, 6), CONVERT (VARCHAR(10), DECRYPTBYPASSPHRASE (@Password, Longitude_Enc))), Longitude);
    UPDATE Contact.[Address]
    SET Address1_Enc = NULL, Address2_Enc = NULL, Address3_Enc = NULL, City_Enc = NULL, PostalCode_Enc = NULL, Latitude_Enc = NULL, Longitude_Enc = NULL

    -- Some 50 other tables after here ..

    ;ENABLE TRIGGER ALL ON DATABASE;
END;
ELSE BEGIN
PRINT 'Test Decryption returned NULL. Will not proceed!!';
END;

Errors looked like:

Msg 9002, Level 17, State 2, Procedure TRIG_Contact_Address_UPDATE_1, Line 26 [Batch Start Line 0]
The transaction log for database 'YourExampleDb' is full due to 'LOG_BACKUP'.

Take more frequent log backups and/or increase the disk space available for the log files.  Let it run for 48 hours over the weekend.

I don't know everything about your system but increasing CPU is unlikely to help.


Trace binding variables from UPDATE statement Is there a way to trace binding variables from UPDATE statement? I tried to look into v$sql_bind_capture but according to documentation this only works for WHERE or HAVING statements.

Set up environment:

SQL> create table t1 (c1 number, c2 number);

Table created.

SQL> insert into t1 values(1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> variable B1 number
SQL> variable B2 number

SQL> exec :B1 := 1;

PL/SQL procedure successfully completed.

SQL> exec :B2 := 2;

PL/SQL procedure successfully completed.

SQL> update t1 set c2 = :b2 where c1 = :b1;

1 row updated.

SQL> rollback;

Rollback complete.

Find my SQL and the captured bind values:

SQL> select prev_sql_id from v$session where sid = sys_context('userenv', 'sid');

PREV_SQL_ID
-------------
94abc43hj1btv

SQL> select sql_text from v$sql where sql_id = '94abc43hj1btv';

SQL_TEXT
--------------------------------------------------------------------------------
update t1 set c2 = :b2 where c1 = :b1

SQL> select name, position, datatype_string, value_string, was_captured
  2  from v$sql_bind_capture where sql_id = '94abc43hj1btv';

NAME         POSITION DATATYPE_STRING      VALUE_STRING         WAS
---------- ---------- -------------------- -------------------- ---
:B2                 1 NUMBER                                    NO
:B1                 2 NUMBER               1                    YES

Problem: the value of :B2 was not captured because it is not part of a WHERE/HAVING clause.

Enable SQL tracing in my session for this specific SQL statement and find the trace file:

SQL> alter session set events 'sql_trace[sql: 94abc43hj1btv]  level=12';

System altered.

SQL> select TRACEFILE from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2231.trc

Run the SQL again:

SQL> update t1 set c2 = :b2 where c1 = :b1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        C1         C2
---------- ----------
         1          2

And the content of the trace file:

PARSING IN CURSOR #140116042834336 len=37 dep=0 uid=85 oct=6 lid=85 tim=437775551 hv=3775967035 ad='63073648' sqlid='94abc43hj1btv'
update t1 set c2 = :b2 where c1 = :b1
END OF STMT
BINDS #140116042834336:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f6f4ef446c8  bln=22  avl=02  flg=05
  value=2
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f6f4ef446e0  bln=22  avl=02  flg=01
  value=1

:B2 is the first bind variable in the SQL text, so it is labelled as Bind 0, and you can see the actual value as value=2.

You can set the above systemwide (alter system instead of alter session), but each server process will have a seperate trace file and you will need to collect the required information from them.


mdw_purge_data_ long runtime I have a SQL Server 2008 R2 that is running several databases and has also been configured as our central MDW.

When the management data warehouse is created it creates a number of agent jobs one of which is mdw_purge_data_[DB NAME] that goes through and purges data that exceeds the retention period, as far as I am aware. This job is currently running and its duration has increased form a few hours to days. This was initially an issue in RTM but was supposedly fixed in Service Pack 1 (we are running SP2). I have tried following an amendment of the stored procedure it calls as per this link:

http://www.toddbaker.org/blog/2010/12/17/sql-2008-mdw-fixing-long-running-purges/

This does not seem to have helped. I then read another blog post suggesting creating two indexes on the sql_handles columns of two tables in the MDW, snapshots.query_stats and snapshots.notable_query_text. When I try and create these I end up with a timeout and the index fails to create. This suggests that there is far too much information in there.

Has anyone got any pointers about reigning in the mdw_purge_data_ job as I think it may well be impacting the performance of the server as a whole.

So finally sorted this out.  There was an orphaned instance within the MDW.  I removed this, which took at least a day to kill off.  Trying to create the indexes failed through the gui due to a time out.  Using activity monitor I could see that there were still huge waits on the server.  I stopped all data collection for the MDW and disabled all the jobs associated, including the purge job.  I then stopped this job as it was still running.  Created the indexes using t-sql.  Re ran the jobs and I am now back with the baseline for this server.  I have gone from over a day to purge data to about 15 minutes so all in all much happier.


Chunking data into a PostgreSQL bytea column? I have column of type bytea in PostgreSQL database.

If I update a row, is it possible to chunk the data into the bytea column in several small steps?

The size of one bytea entry will be mostly below 200MByte.

The chunks will be about 1MByte.

I use psycopg2 and Python to access the db.

This is a follow-up question of Store HTTP response in PostgreSQL

If I update a row, is it possible to chunk the data into the bytea column in several small steps?

Yes, it's possible. But it's a horrible idea. PostgreSQL, and all MVCC databases, writes a new row with every update so on the last chunk you'll be writing 200 MB not 1 MB.

You may consider large_objects to get around this, or better yet not using a database for this because databases are not file systems, and instead just using an IO buffer or the file system write cache. If you need to link that to data in a database, try a path. That's what they're there for (internally the file system uses inodes). Check out btrfs or zfs if you want the PostgreSQL of file systems.

PostgreSQL is the best tool for the job if you're doing the right job, but it's not the best tool for any job.


Reasons for db2 foreign key error (-667) despite all rows appearing sane? I have some csv formatted table dumps and loaded those into a new database with the tables defined by a DDL script from the source database (both acquired by somebody else so I cannot have a look at the original production database). Before loading the rows, I disabled all foreign key constraints (via ALTER TABLE xyz ALTER FOREIGN KEY fkname NOT ENFORCED) to avoid having to care for the load order. Now I want to reenable those constraints with the following statement

ALTER TABLE xyz ALTER FOREIGN KEY fkname ENFORCED

and some of them fail. The error message generated is

SQL0677N: The FOREIGN KEY "fkname" cannot be created because the table
contains rows with foreign key values that cannot be found in the parent key
of the parent table. SQLSTATE=23520

That reads straightforward and clear but I cannot locate the erroneous rows.

An example: T02 is the name of a parent table and T24 the name of a child table. The primary key is (T02.A, T02.B, T02.C) and the corresponding foreign key columns are (T24.A0, T24.B0, T24.C0), the constraint is called F02 and all the named columns are of type CHAR with lengths 2, 4 and 7, respectively.

I tried finding the breaking rows with the following statement:

(SELECT T24.A0, T24.B0, T24.C0 FROM T24)
EXCEPT
(SELECT T24.A0, T24.B0, T24.C0 FROM T24, T02
WHERE T02.A = T24.A0 AND T02.B = T24.B0 AND T02.C = T24.C0)

but it returns an empty result set.

Are there any other circumstances which may cause this error and how do I investigate them?

The database server in question is DB2 10.1.0 for Linux/Unix/Windows (running under Windows Server 2008 R2 64-Bit).

The DB2 optimizer relies on foreign key constraints, which by default are enabled for optimization even though they may not be enforced. The optimizer may choose not to read the rows that should not exist according to the constraint.

Try disabling query optimization on the RI constraint:

ALTER TABLE xyz ALTER FOREIGN KEY fkname DISABLE QUERY OPTIMIZATION


Number of virtual tables that specified user is able to write queries in PostgreSQL I have one question about PostgreSQL that I can't understand. As it seems so far, it needs some of bruteforce in tables. Well, I did that lots of time and I found nothing. So, firstly I will cover my goal.

What I need to do, is to get number of virtual tables that specified user is able to write queries in PostgreSQL. In other words, there could be user foo123 and there could be, let us assume, four tables: table1, table2, table3 and table4. And there could be possibility that user foo123 is able to write queries for table1 and table2, but not for table3 and table4. So, by query doing my task I should get 2 (there is no difference what column name would be - I can think any name for column).

Now I will cover what I tried to do and what I have achieved. For now, I can see that I have to use information from information_schema. But there is one problem. I don't know where I can find users in information_schema and lists of tables, associated with these user by any column. Well, until now I found three tables about granting privileges to do something: information_schema.table_privileges, information_schema.column_privileges, usage_privileges. I think that I have to use information from table information_schema.table_privileges (columns that I should be interested, I think, are grantee and table_name). And it could look like as a path for finding solution, but one small problem. If we would treat grantee as user who is able to do something with virtual table, then there is one thing. For example, database can have lots of users, who are able to use virtual tables in any way. But these users are just in one table: pg_user, i.e. there are no all users listed anywhere that I could take any user to check the of number virtual tables he is able to write queries for. In other words, in table information_schema.table_privileges I see just grantors and users online. For them I can check privilege types, but for no user that is offline.

So, extended version of my question, according to what I have done, could be such questions:

Is my path for finding number of virtual tables for user specified correct or not, i.e. am I doing right or wrong trying to analyse information_schema.table_privileges table?
Why is that so that I can see all database users listed anywhere else, except pg_user. And table pg_user doesn't have any information about privileges using virtual tables.
If my path for finding number of virtual tables is not correct, then in what way I should find that number?

I think that's all I've got. If there are some misunderstanding about my question, I will try to give more information.

I try to answer your question - to be correct, I try to answer the question I think you've asked (see the comments).  Until then I'll assume that a virtual table is simply a table and you want to count the tables a user has SELECT privilege on.

First, you have to collect all roles which the given user belongs to.  This will include the user itself, all roles that are granted to it, all roles that are granted to the roles granted to your user etc., and finally, but usually most importantly, the PUBLIC role.

You can collect these from the information_schema.applicable_roles view with a query like this:

WITH RECURSIVE privs(grantee, role_name) AS (
    SELECT grantee, role_name
    FROM information_schema.applicable_roles
    WHERE grantee = 'my_user'

    UNION ALL

    SELECT ar.grantee, ar.role_name
    FROM
        information_schema.applicable_roles ar
        JOIN privs ON ar.grantee = privs.role_name
)
SELECT role_name FROM privs

UNION

SELECT 'my_user'

UNION

SELECT 'PUBLIC'

Then you have to consult the information_schema.table_privileges view.  In order to see all the rows belonging to all grantees, you have to be logged in as a superuser.  With an ordinary user you will see only the roles granted to your user (including indirectly granted roles, ie. if the grants are user -> user1 -> user2, you will see user -> user1 and user1 -> user2 if logged in as user, and only user1 -> user2 if logged in as user1.)

Here you can look for tables with SELECT privilege like

SELECT table_name
FROM information_schema.table_privileges
WHERE
    privilege_type = 'SELECT'
    AND grantee IN (... the recursive query above goes here ...)
;

Turning this into a count is left as an exercise for the reader :)


"If not exists" using OBJECT_ID() doesn't work for views and triggers. why? For tables I can implement "if not exists" and "if exists" as folows:

--if table exists - drop
If OBJECT_ID('A','U') is not null
Drop Table [A]
--if table not exists - Create
If OBJECT_ID('A','U') is null
Create Table A([key] varchar(20), [value] varchar(max))

but it is not quite working the same on views and triggers

I can do:

-- if exists - drop
If OBJECT_ID('VA','V') is not null
Drop view [VA]

but when I'm trying the oposite:

-- if not exists - create
If OBJECT_ID('VA','V') is null
Create view [VA] as Select * from [A]

I'm Getting the following error:

  Incorrect syntax near the keyword 'view'

And the same is with triggers. when I do:

-- if not exists - create
If OBJECT_ID('Trigger_A_ins','TR') is null
Create trigger [Trigger_A_ins] On [A] instead of insert As
   insert into A select * from inserted

I'm getting error:

  Incorrect syntax near the keyword 'trigger'

But:

-- if exists - drop
If OBJECT_ID('Trigger_A_ins','TR') is not null
Drop Trigger Trigger_A_ins

is working.

Did I missed anything?

Can anyone explain this difference between tables to triggers and views?

Note: I'm using sql server 2012

Referencing the documentation from CREATE VIEW under REMARKS:

  The CREATE VIEW must be the first statement in a query batch.

Referencing the documentation from CREATE TRIGGER

  CREATE TRIGGER must be the first statement in the batch and can apply
  to only one table.

For VIEWS and TRIGGERS, I think you'll have to check for the existence of the object and drop in one batch and create them in another batch separated by a GO

Example:

IF EXISTS (SELECT * FROM sys.objects WHERE [name] = '[dbo].[trg]' AND [type] = 'TR')
      DROP TRIGGER [dbo].[trg] ON [dbo].[tbl]
GO
CREATE TRIGGER [dbo].[trg] ON [dbo].[tbl]
AFTER DELETE
AS
BEGIN
   //
END
GO


See whether a groupped item have more than one value in a database column I am working on SQL database where there are several thousands of lines in one table. This table (product_lines) looks like this:

sku price   Discount    FinalPrice
1   29      5           24
1   29      5           24
2   30      5           25
2   30      0           30
3   15      0           15
3   15      0           15

Each line contains SKU's information when used in a receipt. SKUs may repeat within the rows (eg sku:2) but discount vary each time. I am trying to collect the items that exist both with and without a value in the column discount -- to collect the SKUs that have sold with and without discount. The result I am looking for should look like:

  sku price   Discount    FinalPrice
  2   30      5           25
  2   30      0           30

How can I do this?

You can either use an IN clause with an intersect

select *
from product_lines
where sku in (select sku
              from product_lines
              where discount > 0
              intersect
              select sku
              from product_lines
              where discount = 0);

Using exists might be faster with SQL Server:

select *
from product_lines p1
where exists (select *
              from product_lines p2
              where p2.discount > 0
               and p2.sku = p1.sku)
  and exists (select *
              from product_lines p3
              where p3.sku = p1.sku
                and p3.discount = 0);


Does innodb_flush_log_at_trx_commit = 1 wear out SSDs more than innodb_flush_log_at_trx_commit = 2? I am running a Nextcloud instance and I suffer from a slow sync when a lot of small files are synchronized. I found out that innodb_flush_log_at_trx_commit = 1 is responsible for this so I was forced to set innodb_flush_log_at_trx_commit = 2. Now I am thinking about moving my database to an SSD. And I wanted to confirm that I am indeed running into an access time issue.

Is the same amount of data written to the disk when innodb_flush_log_at_trx_commit is set to 1 as if it was set to 2?

innodb_flush_logs_at_trx_commit
When 1:
for each Txn Commit {
    - Write log buffer to log file
    - flush log files to disk
    }

When 0:
Approx every second {
    - Write log buffer to log file
    - flush log files to disk
    }

When 2:
    {
    - For each commit: { Write log buffer to log file }
    - Appx every sec: { flush log files to disk }
    }

Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.

Well I just copied all of the above from documentation ;)

Anyways, now about your points.

innodb_flush_logs_at_trx_commit changed: Choice is between ACID vs speed. You changed it to 2 for performance.okay but you know the risk.
Moving to SSDs: They're proven to be faster than their ancestors... and considering that, you better benchmark your traffic with the preferable value of innodb_flush_logs_trx_commit of 1 (default) on SSDs.
Is the same amount of data...?: Well yes amount of data will be ofcourse same; though you might wanted to ask something else. We know  that how it improves the perfomance is by reducing number of disk ops.

So, "Does innodb_flush_log_at_trx_commit = 1 wear out SSDs more than innodb_flush_log_at_trx_commit = 2?" -> Regardless of disk type, value of 1 is bound to do more work (disk ops) than value of 2 and hence the performance difference.


Are there design patterns/best practices in writing dynamic SQL in stored procedures? I'm an experienced programmer but I'm new to SQL/databases so please bare with me :).

One of my current tasks involve editing (and refactoring, if needed) a dynamic generated SQL statement in a stored procedure. Are there any "best practices" or "design patterns" that I can follow for writing stored procedures in general or better yet, stored procedures that generate long (300 lines) dynamic sql statements?

Best is to read The Curse and Blessings of Dynamic SQL by  Erland Sommarskog.

Also read Using Dynamic SQL in Stored Procedures and Stored Procedures - Executing & Dynamic SQL