ORA-04091: table ExpenseTable is mutating, trigger/function may not see it I have two tables and a trigger and am inserting values in ProjectsTable like this:

CREATE TABLE ProjectsTable
(
  ProjectID     NUMBER(6) NOT NULL,
  ProjectName   VARCHAR2(200) NOT NULL,
  Cost      NUMBER(10,2),
  ExpenseTotal  NUMBER(10,2),
  CostRemaining     NUMBER(10,2),
  PRIMARY KEY (ProjectID)
);

CREATE TABLE ExpenseTable
(
  ID            NUMBER(6) NOT NULL,
  ProjectID     NUMBER(6) NOT NULL,
  ExpenseAmount NUMBER(10,2),
  ExpenseDate   NUMBER(4),
  CONSTRAINT fk
  FOREIGN KEY (ProjectID)
  REFERENCES ProjectsTable(ProjectID)
);

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
    UPDATE ProjectsTable P
    SET ExpenseTotal =
    (SELECT SUM(ExpenseAmount) from ExpenseTable
    where ExpenseTable.ProjectID= P.ProjectID)
    where P.ProjectID = :New.ProjectID;
END;
/

INSERT INTO ProjectsTable VALUES (101,'AAA',5000,0,5000);
INSERT INTO ProjectsTable VALUES (102,'BBB',3000,0,3000);
INSERT INTO ProjectsTable VALUES (103,'CCC',2000,0,2000);

But when I tried to insert values into the ExpenseTable table, it gave me the following errors:

  ERROR at line 1: ORA-04091: table ExpenseTable is mutating, trigger/function may not see it
  ORA-06512: at ExpenseSum , line 2
  ORA-04088: error during execution of trigger ExpenseSum

As the trigger says, you cannot read data from the same table as the trigger is working on as it would throw consistency out of the window.
Have you tried to put ExpenseTotal column in a view like below?
CREATE VIEW ProjectsTable_V AS
SELECT
  P.ProjectID,
  P.ProjectName,
  P.Cost,
  (
     SELECT SUM(ExpenseAmount)
       FROM ExpenseTable
      WHERE ExpenseTable.ProjectID = P.ProjectID
  ) ExpenseTotal,
  P.CostRemaining
FROM
  ProjectsTable P

If CostRemaining is Cost - ExpenseTotal you can take it out of your ProjectsTable trigger, too :)
If this is sufficient with regard to the performance, I'd prefer it over the trigger based approach any time...


detect what columns to select depending on a column value of the same table I am trying to build a query which returns different columns of the table depending on a column of the same table. Say, a column represents a type of item.

This is my temporary solution, in which I just combine the results of multiple queries.

As you can see, I use "AS" keyword to rename the default column name.

SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 1
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", CAST([ip_address] AS nvarchar(15)) AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 2
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 3
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [mail_domain]  AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 4
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", CAST([ip_address] AS nvarchar(15)) AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 5
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [domain_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 6
union all
SELECT [custom].[dbo].[records].[id] AS "ID",[type_name] AS "Тип записи", [service_name] AS "Значение", [custom].[dbo].[records].[record_type_id] FROM [custom].[dbo].[records] INNER JOIN [custom].[dbo].[record_type] ON [custom].[dbo].[records].record_type_id = [custom].[dbo].[record_type].id WHERE [custom].[dbo].[records].record_type_id = 7

Sounds like you need to use CASE WHEN expression, which is well documented. So case when x = y then select this else select that etc.


DELETE Rows with or without LIMIT When deleting, should I use LIMIT, or not (MySQL INNODB tables)

Each customer has a unique hash.
Each customer can have one or more records in a single table.

If I know in advance a customer can have up to 100 records maximum, and my DELETE query has LIMIT 100, then I presume once 100 records were deleted, the query will end without going to end of table.

However, if my customer only has 50records, and my DELETE query has LIMIT 100, the delete will continue until end of table is found since it will spend effort chasing the 51st record which does not exist.

Thus... If I do not use LIMIT does MySQL INNODB know in advance that after having deleted 50 records it need not continue further?

The answer can either create or eliminate performance issues...

The limit is only going to help with exactly 100 so it is probably not worth the overhead.

If you put an index on the hash then it should know to stop (and where to start).


How to populate central management server registration from a list I need to delete users from all the instances in my domain.

In order to do that, I want to create a CMS group and execute queries on it.
Problem is I have more than 150 servers to add. How can I bulk add servers to the group and not do it manually?

I would probably recommend Powershell for this task, but I did find a post that allowed me to automatically generate CMS entries and it worked for me - Using T-SQL to populate a Central Management Server

Excerpting from that post:

Create an instance table with some information about the instances

CREATE TABLE Instance (Instance varchar(255), SQLVersion varchar(10),
    SDLC varchar(20));

INSERT INTO Instance VALUES
    ('(local)\sql2014cs','2014','Production'),
    ('(local)\sql2014ci','2014','Test'),
    ('(local)\sql2012','2012','Development'),
    ('(local)\SQLEXPRESS2012','2012','Production'),
    ('(local)\sql2008R2','2008 R2','Development');

Create CMS grouping

-- Version category
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal]
    VALUES ('Version','Instance Version',0,1,0);

-- Version subcategories
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal]
SELECT DISTINCT Instance.SQLVersion, Instance.SQLVersion, 0,
    Parent.server_group_id, 0
FROM Instance
CROSS JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
WHERE Parent.name = 'Version';

-- SDLC category
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal]
    VALUES ('SDLC','Instance SDLC level',0,1,0);

-- SDLC subcategories
INSERT INTO msdb.dbo.[sysmanagement_shared_server_groups_internal]
SELECT DISTINCT Instance.SDLC, Instance.SDLC, 0,
    Parent.server_group_id, 0
FROM Instance
CROSS JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
WHERE Parent.name = 'SDLC';
GO

Add the actual instances

-- Instances broken out by Version
INSERT INTO msdb.dbo.[sysmanagement_shared_registered_servers_internal]
SELECT Parent.server_group_id, Instance.Instance, Instance.Instance,
    Instance.Instance + ' - ' + Instance.SDLC, 0
FROM Instance
JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
    ON Instance.SQLVersion = Parent.name

-- Instances broken out by SDLC
INSERT INTO msdb.dbo.[sysmanagement_shared_registered_servers_internal]
SELECT Parent.server_group_id, Instance.Instance, Instance.Instance,
    Instance.Instance + ' - ' + Instance.SQLVersion, 0
FROM Instance
JOIN msdb.dbo.[sysmanagement_shared_server_groups_internal] Parent
    ON Instance.SDLC = Parent.name


Sybase ASE Auditing on sp_addlogin sp_droplogin sp_modifylogin We have requirement to enable auditing on Sybase ASE(Adaptive Server Enterprise/15.7/EBF 27351 SMP SP139 /P/ia64/HP-UX B.11.31/ase157sp138x/4041/64-bit/FBO/Tue Aug  8 07:33:11 2017) and I enabled the same on master database as below:

I was under impression that this would also enable auditing on any change in login/user(sp_addlogin, sp_modifylogin, sp_droplogin, sp_adduser, sp_dropuser) as create/drop/alter was handled at master level, but that was incorrect as this is applicable to only objects and not users/logins. Recently when I dropped one user, it was not logged in the audit event; after checking multiple documents on the Sybase documentation and different portals on the internet and also with the help of vendor, came to know that one more type of auditing(login_admin) needs to be enabled in order to capture these events in audit events.

I executed below command and was expecting that this would solve the underlying issue:

1> sp_audit "login_admin", "all", "all", "on"
2> go
Audit option has been changed and has taken effect immediately.
(return status = 0)
1> sp_displayaudit "login"
2> go
No logins currently have auditing enabled.
(return status = 0)
1>

As evident from the result of sp_displayaudit, logins are still not being audited. I am not really sure if this requires ASE reboot to take effect or I am missing something here.

Any help would be greatly appreciated.

After trying hard on this without finding any workable solution, raised a case with Sybase support and they have provided solution.

Below are the commands that can be used to satisfy the mentioned requirement:

--Audit any change at procedure level for login/user/alias

use sybsystemprocs
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_addlogin", "on"
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_droplogin", "on"
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_modifylogin", "on"
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_adduser", "on"
go
sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_dropuser", "on"
go

sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_addalias", "on"
go

sp_audit "exec_procedure", "all", "sybsystemprocs.dbo.sp_dropalias", "on"
go

--Audit any change at table level for login/user/alias

sp_audit "delete", "all", "master.dbo.syslogins", "on"
go
sp_audit "insert", "all", "master.dbo.syslogins", "on"
go
sp_audit "update", "all", "master.dbo.syslogins", "on"
go

sp_audit "delete", "all", "master.dbo.sysusers", "on"
go
sp_audit "insert", "all", "master.dbo.sysusers", "on"
go
sp_audit "update", "all", "master.dbo.sysusers", "on"
go

sp_audit "delete", "all", "master.dbo.sysalternates", "on"
go
sp_audit "insert", "all", "master.dbo.sysalternates", "on"
go
sp_audit "update", "all", "master.dbo.sysalternates", "on"
go

sp_audit "delete", "all", "master.dbo.syssrvroles", "on"
go
sp_audit "insert", "all", "master.dbo.syssrvroles", "on"
go
sp_audit "update", "all", "master.dbo.syssrvroles", "on"
go

sp_audit "delete", "all", "master.dbo.sysloginroles", "on"
go
sp_audit "insert", "all", "master.dbo.sysloginroles", "on"
go
sp_audit "update", "all", "master.dbo.sysloginroles", "on"
go

I am sure this would help to anyone who wants to enable auditing at Sybase ASE level and is not sure how to achieve the same.


`"C:\xampp\mysql\bin"' is not recognized as an internal or external command,operable program or batch file I am trying to access to mysql from windows command prompt by this command:

"C:\xampp\mysql\bin" mysql -u root - p

But it sayas:

  `"C:\xampp\mysql\bin"' is not recognized as an internal or external
  command, operable program or batch file.

But when I do these, it works:

cd C:\xampp\mysql\bin
mysql -u root - p

You might want to try the following instead:

C:\xampp\mysql\bin\mysql.exe -u root -p

assuming you are using a CMD / DOS Prompt

Depending on the path in which your mysql.exe resides, you might have to incorporate using double quotes as follows:

""C:\Program Files (x86)\mysql\bin\mysql.exe" -u root -p"

assuming you are using a CMD / DOS Prompt

You add double-quotes from "C:\... to the end of the executable ...exe" and then add double-quotes at the beginning of the whole command until the end. You end up with the above command.

Reference: Long Filenames or Paths with Spaces Require Quotation Marks (Microsoft Support)


Insert All Columns from one table into another table MYSQL I am trying to insert multiple columns with their data from one table into another table, without a common column (i would expect a cross product in the result). I am not sure how that's possible, because each of the two tables have varied number of columns, which rules out UNION on them. I tried a Create..Select to create a 3rd table with all columns from both the tables but unsure about the syntax (keeps throwing syntax error).

Both Tables' syntax:

CREATE TABLE `t_modul` (
  `ID` int(11) NOT NULL,
  `Seriennummer` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(128) DEFAULT NULL,
  `StandortID` int(11) NOT NULL,
  `Firmwareversion` varchar(8) DEFAULT NULL,
  `Optionen` int(11) DEFAULT NULL,
  `Feldstärke` int(11) DEFAULT NULL,
  `Platziert` bit(1) DEFAULT NULL,
  `Sendeintervall` int(11) DEFAULT NULL,
  `Batteriekapazitaet` int(11) DEFAULT NULL,
  `Modulzusatzinfo` varchar(255) DEFAULT NULL,
  `ModulUserinfo` varchar(128) DEFAULT NULL,
  `ReadOutDate` varchar(40) DEFAULT NULL,
  `ReduktionsIntervall` int(11) DEFAULT NULL,
  `Startdatum` varchar(40) DEFAULT NULL,
  `SAMTemperaturEin` double DEFAULT NULL,
  `SAMTemperaturAus` double DEFAULT NULL,
  `SAMCoolingOff` int(11) DEFAULT NULL,
  `SAMCoolingOn` int(11) DEFAULT NULL,
  `SAMCoolingOffMax` double DEFAULT NULL,
  `SAMCoolingOnMin` double DEFAULT NULL,
  `SAMParameterField` int(11) DEFAULT NULL,
  `SAMIsAlarmMelder` bit(1) DEFAULT NULL,
  `SAMNv27` int(11) DEFAULT NULL,
  `Hardwarekonfiguration` int(11) DEFAULT NULL,
  `Anwendungskonfiguration` int(11) DEFAULT NULL,
  `Logkonfiguration` int(11) DEFAULT NULL,
  `Archivsendezeit_0` int(11) DEFAULT NULL,
  `Archivsendezeit_1` int(11) DEFAULT NULL,
  `IsLogger` bit(1) DEFAULT NULL,
  `Datenpunktkennung` varchar(128) DEFAULT NULL,
  `TTRFtpARCHPollDoneDate` varchar(40) DEFAULT NULL,
  `Repeaternummer` int(11) DEFAULT NULL,
  `StammdatenID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID_Standort_idx` (`StandortID`),
  CONSTRAINT `ID_StandOrt` FOREIGN KEY (`StandortID`) REFERENCES `t_standorte` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sensors` (
  `sensorID` bigint(11) NOT NULL,
  `Number` bigint(20) unsigned NOT NULL,
  `ValueIDs` text NOT NULL,
  `MeasureTime` datetime NOT NULL,
  `StartTime` datetime NOT NULL,
  `Configuration` int(10) unsigned NOT NULL,
  `LogCycle` int(11) NOT NULL,
  `Alarms` text NOT NULL,
  `AlarmType` int(11) NOT NULL,
  PRIMARY KEY (`GesySenseID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

UNION query:

CREATE TABLE sensors_1
  SELECT * FROM `sensors`
    UNION
  SELECT * FROM `t_modul`;

have you tried this?

CREATE TABLE t_cross_result AS
SELECT *
FROM 't_modul' CROSS JOIN 'sensors'


Oracle 11g: Part Copy of Production data to Development Environment I have a Production database with the current size involving two principale schemas:

SQL> SELECT SUM(bytes)/1024/1024/1025 "Size in GB" FROM dba_segments where owner in ('SCHEMA1','SCHEMA2');

Size in GB
----------
201.927804

Out of these the mentioned tables and their indexes are taking up following space (175.326 GB):

SQL> SELECT SUM(bytes)/1024/1024/1024 "Size in GB" FROM dba_segments where owner IN ('SCHEMA1','SCHEMA2')
  2  and segment_name in  ('TAB1','TAB2','TAB3','TAB4','TAB5','TAB6','TAB7','TAB8','TAB9','TAB10','TAB11','TAB12');

Size in GB
----------
72.0917968

SQL> SELECT SUM(bytes)/1024/1024/1024 "Size in GB" FROM dba_segments where owner IN ('SCHEMA1','SCHEMA2')
  2  and segment_name in  (
  3  select index_name
  4  from dba_indexes
  5  where owner IN ('SCHEMA1','SCHEMA2')
  6  and table_name in ('TAB1','TAB2','TAB3','TAB4','TAB5','TAB6','TAB7','TAB8','TAB9','TAB10','TAB11','TAB12'));

Size in GB
----------
103.234375

SQL> select (72.0917968+103.234375) from dual;

(72.0917968+103.234375)
-----------------------
            175.3261718

Thus these 12 tables along with the indexes takes about 88% of the total space in the 2 schemas.

All 12 tables mentioned are subpartitioned based on a partition and subpartition key.

We have 7 development databases which needs to be refreshed on demand with the production database.

Currently, the refresh is done using RMAN, but going forward as the production database size increases we are anticipating space problems on development databases.

The Solution which I tried:

The 12 tables are partitioned in such a way that the data in one partition is functionally independent of other partition.

I created new tablespaces and moved partitions to these new tablespaces based on their business functionality.
Thus a single table had some partitions on one tablespace and rest of the partitions on the other tablespace.

The idea was to transport the necessary tablespces (and not the entire database) across production to development database.

Later on my DBA told me that for a tablespace to be transportable across databases they should be "self-contained".

And one of the violations for self contained tablespace according to oracle documentation is:

"A partitioned table is partially contained in the set of tablespaces.
The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables."

Thus my design failed.

Kindly note that the 12 big tables which I mentioned above contains data which is actually not needed for my unit testing on development database.
Is it possible to perform RMAN copy without including the data from these 12 tables?

Or could you think of any other solution so that I can do "part" refresh of production database to developement.

Let me know if you need any more information?

Database: Oracle 11g - 11.2.0.2.0

Thanks a lot in advance!!

Your initial idea was not bad at all. What you can do is store the unwanted partitions with their local indexes in separate tablespaces. Use rman for the cloning but use the SKIP TABLESPACE option to not clone the tablespaces with the unwanted partitions. (assuming online backup)
After the clone, the skipped tablespaces have datafiles with status RECOVER.

see RMAN DUPLICATE DATABASE: Options

In the end you just drop the unwanted partitions. To be able to do that you first have to get rid of things like constraints and indexes that need to be re-created later on. This worked in 10gR2. Make sure that you don't drop the last partition of a table, in that case drop the table.

It is a bit of work but certainly possible. If the difference in Volume is huge or there are lots of copies, it might be worth spending some time for it.


mysqli importer file - disable foreign keys I am using this script to import a mysql file but I want to set foreign key checks to 0 when he will do the import.

https://github.com/davcs86/php-mysqlimporter/blob/master/php-mysqlimporter.php

Calling the file below with this function

include("Mysqlimporter.php");
$mysqlImport = new MySQLImporter($host, $username, $password);
$mysqlImport->doImport($source . "/var/dump.sql", $dbname, true, true);
}

SET foreign_key_checks = 0;
    //import data
    SET foreign_key_checks = 1;


Rank students based on their average in MySQL I have been researching and trying various queries to get this to work.
What I want to happen is to rank each student based on their average which will be calculated by using AVG(grade) and this will be grouped by student_id. The table with the necessary info is as follows:

assessment_id | student_id | grade | subject_id

What I would like to see after the query is:

student_id | AVG(grade) | rank

I would also like to get the rank of a student using PHP, for example (mysql_query) WHERE student_id = '1001'

The closest I have come to getting this was by using the query below:

SELECT student_id, AVG(grade), (@rn := @rn + 1) AS rank
FROM grades CROSS JOIN (SELECT @rn := 0) CONST
GROUP BY student_id ORDER BY AVG(grade) DESC

The problem with the query above is that it ranks the students based on their student_id number.

I have searched for solutions all over but they just seem to not solve what I am after. I would really appreciate your help towards this.

I can't test this right now, but you probably want something like this:

SELECT student_id, avg, (@rn := @rn + 1) AS rank
FROM
 (SELECT student_id, AVG(grade) as avg
  FROM grades
  GROUP BY student_id ORDER BY AVG(grade) DESC) agg
CROSS JOIN (SELECT @rn := 0) CONST
ORDER BY avg DESC


GRANT command not working in MySQL Server I'm trying to execute the below command as root user to provide accessiblity from my application.

GRANT ALL ON communitycourts.* TO 'root'@'%' WITH GRANT OPTION;

But it returns 0 rows affected, but when I dont see this in result of  SHOW GRANTS. Pelase help me

Two points to add:

It is recommended to use another user other than root to connect to your DB in your application. Try to use root account for administration only. This can be good only on testing machines.
Don't forget to flush privileges for the changes to take effect. This is better than restarting mysql.


Finding Deleted / Updated record statement from SQL Server transaction Log backup file One of my production server's data was updated / deleted by someone.

I want to find out when the data was deleted or updated, and if it's updated/ deleted then through which statement it was updated (means the query statement details like through which query it is happened).
I have a transaction log file i.e . .trn file.

Method 1: If you want to find it from the transaction log backups you can use read the log viafn_dump_dblogto get the explicit information. On how to read and use this please refer Using fn_dblog, fn_dump_dblog

Note:

  functn uses this extensively that every time fn_dump_dblog is called,
  it creates a new hidden SQLOS scheduler and up to three threads, which
  will not go away (and will not be reused) until a server restart.

Method 2: Using the default trace which is enabled by default in SQL server and it can be used to find various such information on altered objects etc. as explained in this article The default trace in SQL Server - the power of performance and security auditing

Method 3: You can use third party tool like ApexSQL Log(You can give it a try with Trial version , else for full verison you can go for purchase) which is a SQL transaction log reader for forensic auditing and rollback of malicious or inadvertent changes


Find results based on other results In SQL Server 2008, I have a table called Info.Case, which lists all the cases we have, Info.Person which lists all the people in the database, and a table called Links.Case_Person, which lists how each person relates to each case.

Here is what I am trying to do:

Find all the cases John Smith (Person_ID = 123) is listed as Primary (Role_ID = 5) or Secondary (Role_ID = 11).

So far this is not an issue:

SELECT * FROM Info.Case IC
INNER JOIN Links.Case_Person LC
ON IC.Case_ID = LC.Case_ID
INNER JOIN Info.Person IP
ON LC.Person_ID = IP.Person_ID
WHERE LC.Person_ID = 123 AND LC.Role_ID IN (5, 11)

This generates 100 Cases.

Now I want know know if there was also an assistant (Role_ID = 22) on any of these specific 100 John Smith cases. So I want to search just the 100 cases for any Role_ID = 22.

How do I do this?

Without sample data and desired results, can't be too confident, but it sounds like you just need to add a check for the existence of assistants on the same cases:

AND EXISTS (SELECT 1 FROM Links.Case_Person AS cp
  WHERE cp.Case_ID = LC.Case_ID AND cp.Role_ID = 22);

Based on the comments below it seems your description doesn't quite match your stated requirements above. Perhaps, then:

SELECT *,
  HasAssistant = CASE WHEN LC2.Case_ID IS NOT NULL THEN 1 ELSE 0 END
FROM Info.Case IC
INNER JOIN Links.Case_Person LC
ON IC.Case_ID = LC.Case_ID
INNER JOIN Info.Person IP
ON LC.Person_ID = IP.Person_ID
LEFT OUTER JOIN Links.Case_Person AS LC2
ON LC.Case_ID = LC2.Case_ID
AND LC2.Role_ID = 22
WHERE LC.Person_ID = 123 AND LC.Role_ID IN (5, 11);

(Note that if it is possible for a case to have more than one assistant, that case will produce multiple rows here. There are ways around that, but your specific requirements are still not clear to me.)


1 million strings and exact match search I need to chose the best solution (software and algorithm) to solve my problem. I need to store up to million strings and data associated with them. Strings are search queries and data is cached search result. So the operations I'm going to perform are:

get a query from user
look for cached result in DB (exact match)
if found - return it
if not - perform a search and store the result

Some other facts:

As it is cache rows will expire over time.
Lookup and insert speed is a priority.
This string field is most probably the only one to search by.
I'm using PHP

Looks like hashing the string is a good idea, collisions are not a problem, if I get it I will just assume it's a cache miss and perform a new search. So I'm thinking of the following options:

MySQL with hash stored in binary field
MySQL with hash as string with index
MongoDB (maybe with capped collection instead of expiration time field)
some other solution (specialized key-value storage or something) I don't have experience with if it's good enough and worth learning for the project

I also have used Sphinx search when I needed almost the same thing but with partial match also. For exact search it seems to be an overkill.

So is hashing a good idea at all?
Which hash algorithm would you recommend?
Which option if better and why?
Does the answer change if I need to store 5 million rows?
What if I add hits field to get most popular rows from time to time?

PostgreSQL is perfectly fine for this.
You got a couple of options to make this work.

First of all:
PostgreSQL has a special index type for that called GIN (http://www.cybertec.at/gin-just-an-index-type/). It is perfect for full-text-search in general.

The cool thing is: In the latest version of PostgreSQL there is support for a thing called jsonb. You can put your string into a JSON document and search on EACH field in the JSON in a nice way using GIN and a couple of cool operators (see http://www.postgresql.org/docs/9.4/static/datatype-json.html).
JSONB is really really fast, very powerful and it definitely kills MongoDB on the performance side.
In addition to that a couple million of rows are no real big deal for PostgreSQL anyway.
There is one more cool thing: PostgreSQL can do fuzzy search on strings (through Nearest-Neighbour search).


PostgreSQL LWLock: lock_manager issue In our application we need to insert a lot of historical data that will be consumed later by some mobile devices.
These data is organized by a numeric incremental attribute that the device will use to download only the 'offset' it needs. So, if in the last connection a device downloaded records from 0 to 100, later it will download records starting 101 to last record available.
Let's call Producer the component that will process and populate (insert) data on these historical tables. For each historical table that the producer will populate, it will:

Open a transaction
Insert data
Update last available record for that table
Close the transaction

There is a lot of producers (about 15, each one with 8 threads) running on the database concurrently.
Now, let's say that the Consumer is the component responsible for reading the information saved by the producers. All it will do is check, table by table, if there is some record between a required offset a return it to the device.
When a device requests data, it will:

Create a transaction
Iterate each table querying it for new records based on the offset requested.
Close the transaction

Actually, the project runs a AWS db.r5.2xlarge (8 vCPU, 64 GB Ram) Oracle RDS Instance, without any problems.
Now, our team was requested to change from Oracle to PostgreSQL 12. So, we created an PostgreSQL RDS Instance with same size and start some tests.
The producers are working pretty well! The insertions are fast, memory and CPU consumptions under control, no issues.
BUT, when we start the Consumers, everything changes...
The CPU goes to 100%, Freeable memory drops to zero. Swap increases, the system freezes and "out of shared memory" with hint "YOU MIGHT NEED TO INCREASE MAX_LOCKS_PER_TRANSACTION" comes in place.
Apparently, the execution plan of the queries is OK! We are using prepared statements. Statistics are OK also. No sessions being locked by other sessions... Records are being returned as Cursors. Exactly as with Oracle.
Looking in pgAdmin, we a lot of sessions wait for "LWLock: lock_manager", but we can't understand why...
We tried the following approaches, with no success:

Increase MAX_LOCKS_PER_TRANSACTION (this eliminated the out_of_shared_memory error, but other behavior are the same)
Disable parallel queries
Increase work_mem
Reduce max_connections
Increase shared buffers
Increase wal_size

We can't figure out why this is happening. The PGSQL database load is smaller than the load Oracle supports on production. Does anybody have any hint that we could try?
Additional info:

PGSQL tables is currently partitioned, but partition prunning seems to be ok.
Multiple Producers and Consumers writes/reads the same table at the same time, but a consumer will never try to consume a record that before producer explicitly marks as available.
We read that out_of_shared_memory occurs when tables are being dropped and recreated within a long transaction... This is not our case.
PostgreSQL Version: PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Attached there are some pictures that we see when the problem happens.

Thanks a lot!

The full object locks which last (usually) for the duration of a transaction and which you can see in pg_locks have info about them stored in shared memory.  But access to that shared memory requires the protection of light-weight locks, which should last for only nanoseconds or microseconds while the memory access is actually occuring.  What you appear to have is contention on those light-weight locks which protect the memory used for the full locks.
I tried to reproduce this contention by having intense traffic through the lock manager, but I can't reproduce this contention.  So I think you are doing something which is not obvious based your description, like maybe using subtransactions.  Could you publish code which does reproduce this problem, by inserting, selecting, and updating the way you do it, but with the rest of your business logic stripped out?
But in any case, allowing there to be over 200 active connections in a machine with only 4 CPUs (for EC2 x86 machines, the number of usable CPU is generally half the number of vCPU) can't be good for contention.  The more active processes you have, the more time they spend fighting over locks rather than doing useful work.  Having a lot of connections can simplify the client book-keeping and it isn't always a problem (like if few are active at any one time), but there is ample evidence here that for you it is a problem.
You should adjust your connection pooler to restrict the number of connections.  If 90 of your 100 connections are usually idle, then they are not useful during the time when they are idle, and are actually harmful during the time they are in use as they cause contention. This creates a "the beatings shall continue until morale improves" situation--the database is slow so let's launch more connections, which create more contention which slow it further. (They are kind of harmful all the time, as they impede the usefulness of your Connections Count monitoring graph). Are the consumers connecting through the same pooler as the producers?


Howto do a hard upgrade from postgis-1.5 to postgis-2.0 I need to upgrade from postgis 1.5 to postgis 2.0 but i cant backup my data and restore it can someone give me a link where it is clear enough for doing this?

If you can't follow the correct procedures suggested by Mapperz and Andre Joost, I'd advise you to do an extraction through tools such as  or "Qgis" (ogr) in shp format, and then perform a batch import in a fresh postgis 2.0 installation with shp2pgsql-gui.

You just have to make sure that during the export value of the attributes (columns) of the various spatial layers may be slightly different because of shape format specification.

The length of the porting procedure depends strictly on the amount of data.


Is it possible to give the optimizer more or all time it needs? Given that the optimizer cannot take all the time it needs (it has to minimize the execution time and not contribute to it) to explore all possible execution plans it sometimes get cut off.

I was wondering if this can be overridden so that you can give the optimizer all the time in needs (or a certain amount of milliseconds).

I don't have a need for this (atm) but I can imagine a scenario where a complex query is executed in a tight loop and you want to come up with the optimal plan and cache it before hand.

Of course it you have a tight loop you should rewrite the query so it goes away but bear with me.

This is more a question out of curiosity and also to see if there is sometimes a difference between a short circuited optimization and a full one.

It turns out that you can give the optimizer more time with trace flag 2301.  It's not exactly what I was asking but it comes close.

The best information I found on this is in Query Processor Modelling Extensions in SQL Server 2005 SP1 by Ian Jose.

Use this trace flag with care! But it can be useful when coming up with better plans. See also:

Articles tagged "optimization level" by Grant Fritchey.
Before you upgrade to SQL Server 2008… by Brent Ozar.
Tuning options for SQL Server when running in high performance workloads by Microsoft Support.

I was thinking about queries with lots of joins where the solution space for join order explodes exponentially. The heuristics that SQL Server uses are pretty good but I was wondering if the optimizer would propose a different order if it had more time (in the range of seconds or even minutes).

Next to trace flag 2301, there is 8780 which really does make the optimizer 'work harder' since it just gives it more time (not unlimited, as described in detail here (russian) and less detailed here) to do its thing.

Detailed description in english of the original author of the russian article. which includes the author's own warning:

  it is not recommended ever use it in production.

Combining the two and applying them (very selectively via query hint OPTION (QUERYTRACEON 2301, QUERYTRACEON 8780) to a query of 4-level nested inline TVFs (where only the one at the bottom would do any real work and the upper levels would correlate results via EXISTS subqueries) resulted in a nice MERGE JOIN and several LAZY SPOOLs that cut down execution time by half.


stored procedures location influence their performance? I have a procedure SP_MYPRO that updates data in MYDB1.

1) the performance of SP_MYPRO would be different if I place it in MYDB2 instead of MYDB1?

2) what if the procedure SP_MYPRO only read data, does not update anything?

This is something you can easily test for yourself, but in my testing, no, there is no significant overhead in calling a stored procedure across a database boundary (I am sure you could make something noticeable though if you tried hard enough).

However, I would say that a stored procedure should live closer to the data that it manipulates than the object(s) that call it. This way, if you move the database, all that breaks is the remote calls to that procedure, rather than all of them.


PostgreSQL: managing the postmaster PID I am running PostgreSQL on my localhost on MacOS.  Every once in a while (~ every 5 times I reboot the computer), I face the following error when I try to connect to my local database instance:
could not connect to server: Connection refused
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

When this happens, I have gotten used to the practice of opening the terminal, then running
 rm -f /usr/local/var/postgres/postmaster.pid

which allows me to connect to the database.  According to the documentation, having an old .PID file in the data directory "confuses" postgres and so it must be removed.
My question is, why must be this done intermittently and can one automate the removal of old .pid files ?   Please note that brew services restart postgres does not resolve the issue - only the removal of the old .pid file works.   Any advice here would be greatly appreciated!

The postmaster.pid file is one of the measure that PostgreSQL takes to prevent starting multiple database servers on the same data directory at the same time (which would cause data corruption).
If you start the PostgreSQL server and there is a postmaster.pid file present, PostgreSQL checks if there is a process running with the process with the process ID from the PID file. If not, PostgreSQL will ignore the obviously stale file and start anyway. Otherwise, it will refuse to start.
The correct solution is to see that the database is shut down cleanly when you turn off the machine. A clean shutdown will remove postmaster.pid. This will also save you the time to perform crash recovery on the PostgreSQL database.


MongoDB sharding and zone tag that has a range of 1 key value Suppose I have a zone tag that has a range of exactly 1 key value, such as the letter A. Further, suppose I assign this zone to two shards. Will the data with a shard key of A be put on both shards?

My understanding leads me to say no because it will put the initial chunk on one of the two shards and since that chunk cannot be split because it encompasses only a single shard value, this means that a migration will never happen, so the other shard will sit empty.

Your understanding is correct: a chunk can only be associated with a single shard. A chunk range representing a single shard key value will be an indivisible chunk, so assigning this range to multiple shards will not result in data distribution across multiple shards.

If the data in this chunk eventually exceeds the chunk size limit (64 megabytes by default), the chunk will be flagged as a jumbo chunk and will be ignored for future migration activity.

Shard keys with limited cardinality (or outliers with high frequency) should generally be avoided as they will lead to data imbalance and performance challenges.


How to maintain unique sequence across table columns? I hava two tables registed_products and unregisted_products. Both have autoincrement primary key which can be overlapping which if 100 id exist reg table,
it can exist in unreg table as well. Insert can come from many places with in the application

There is third service expecting unique id for both type of products.

In oracle , I could have introduced one more column in each table. Create a sequence. Now could create the trigger before_insert on both the tables so that
whenever there is an insert in any table, trigerr will get the next_val from sequence and insert in to new column. This way I can ensure unique
number across table columns.

I am not sure how to achieve the same in mysql as there are triggers but not sequences that can be used across tables ?

Plan A:  Have a 3rd table that contains an AUTO_INCREMENT plus any common columns between the two existing tables.

Plan B:  Build a "sequence" table which has 2 columns:  an AUTO_INCREMENT and another column to key into the table.  Probably REPLACE is the command to get a new id without growing the table.

REPLACE is performed in 2 steps:

DELETE any row(s) that have match on any UNIQUE key.
INSERT a new row.

Here's the table and its initialization:

    CREATE TABLE sequence (
        seq INT UNSIGNED AUTO_INCREMENT NOT NULL,
        code CHAR(1) CHARACTER SET ascii NOT NULL,
        PRIMARY KEY(seq),
        UNIQUE(code) ) ENGINE = InnoDB;
    INSERT INTO sequence (seq, code) VALUES (1, 'a');

To get the new 'next' sequence number, either use REPLACE:

    REPLACE INTO sequence (code) VALUES ('a');
    SELECT seq FROM sequence WHERE code = 'a';

Or, and this is probably faster,

    INSERT INTO sequence (seq, code)
        VALUES (NULL, 'a')
        ON DUPLICATE KEY UPDATE
            seq = LAST_INSERT_ID(seq + 1);
    SELECT LAST_INSERT_ID();


How to Develop Union Reports from Single Tenant Databases? We have a database for each customer, for over 700 databases. All the schemas are the same. Now we want to conduct Reporting for : All Databases Customer Analysis. Many db professionals prefer single tenant databases. All database reside on the same server instance.
Single Tenant Database Discussion

CREATE TABLE CustomerOne.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL default(1),
    [QuantityBought] [int] NULL,
)

Does anyone know of any other ideas not listed below, which would work?
Our idea is to either create large Views, however, View performance is slow. The second option is stored procedures.

(1)

CREATE VIEW Reportingdb.dbo.CustomerTransaction
as
SELECT
    [Customertransactionid]
    ,[Customerid]
    ,[Quantitybought]
FROM [CustomerOne].[dbo].[customertransaction]

UNION ALL

SELECT
    [Customertransactionid]
    ,[Customerid]
    ,[Quantitybought]
FROM [CustomerTwo].[dbo].[customertransaction]

2) We can also create a stored procedure. Stored Procedure which will insert into this large table. However there will be a time lag between runs, we want instantaneous data.

insert into ReportingDB.[dbo].[customertransaction]
select * from CustomerOne.[dbo].[customertransaction]
UNION ALL
select * from CustomerTwo.[dbo].[customertransaction]

SQL How to Convert Single Tenant Databases to a Multitenant Database

Given that the data is in 700 separate databases, and likely to stay there, and that you want reporting to show the current state of the transactional database I see basically two choices. One is send the query to the data; the second is send the data to the query.

Your view would be an example of sending the query to the data. Each client DB performs some of the work and the reporting DB collates and formats the results. There will be a couple of challenges with this. Cross-database queries are slower than single database queries. I don't have numbers to quote, but this is the received wisdom. Likely the diverse databases will be accessed serially - first DB1 is read, then DB2 is read, then DB3 etc. - meaning the reporting query's execution time will be (at least) 700 times an individual DB's query time. Ouch. There may be clever ticks to force parallel processing in this circumstance. I don't know of any. Certainly a SQL CLR procedure could be written to spawn a thread for each client DB, then correlate the results. A sort of Hadoop Lite, if you will. If I were going to those lengths, however, I think I'd prefer to have BIML generate the necessary packages and run them on demand. It would be a close-run thing to decide which I disliked more - 700 extra threads running inside the SQL Server process or 700 SSIS packages running simultaneously.

By sending the data to the query I mean having a second copy of the data in a single reporting database. It is against this copy that report queries run. This strategy comes in two flavours - pull and push. Pull is the reporting DB sucking data from client DBs according to the reporting DB's schedule and requirements. Your second suggestion using a stored procedure would fall into this approach. BIML-generated SSIS packages could also be used for this. It will have the normal issues with polling (figuring out what's changed, wasted cycles when nothing has). SQL Server has features to help with this (Change Tracking and Change Data Capture), but still.

Push techniques are generally favoured because they avoid the polling overhead. A simple trigger may suffice, duplicating the data to the reporting DB or a staging table on each write to a client DB. This is extra work in a client-facing transaction, however, and extra opportunity for failure. For this reason asynchronous techniques would be preferable. One option is to have the application put a message on a reliable service which the reporting application picks up and processes. There is even a message service baked into SQL Server which could be used (Service Broker). Processing, coordination, failure and recovery are all you concern in client code, however.

SQL Server comes with a number of replication options. These reliably copy data from a source to a destination. Initiation, synchronisation and recovery are all available. I think this is likely to be your best option. Configure SQL Server to replicate changes in client DBs asynchronously but reliably to the reporting DB. Run queries off this reporting DB.


Can't add a node to PXC due to SST error I've successfully bootstrap a Cluster (mysqld  Ver 5.6.30-76.3-56 for debian-linux-gnu on x86_64 (Percona XtraDB Cluster (GPL), Release rel76.3, Revision aa929cb, WSREP version 25.16, wsrep_25.16)).

When starting a second node to join this cluster there is the following error:

2016-07-29 09:31:58 12359 [Note] WSREP: New cluster view: global state: 3f3843a3-54aa-11e6-af38-5b145f899741:195614, view# 4: Primary, number of nodes: 2, my index: 1, protocol version 3
2016-07-29 09:31:58 12359 [Warning] WSREP: Gap in state sequence. Need state transfer.
2016-07-29 09:31:58 12359 [Note] WSREP: Running: 'wsrep_sst_xtrabackup-v2 --role 'joiner' --address 'XYZ.40.39.13' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '12359'  '' '
2016-07-29 09:31:58 12359 [ERROR] execlp() failed: Permission denied
2016-07-29 09:31:58 12359 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address 'XYZ.40.39.13' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '12359'  ''
    Read: '(null)'
2016-07-29 09:31:58 12359 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address 'XYZ.40.39.13' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '12359'  '' : 1 (Operation not permitted)
2016-07-29 09:31:58 12359 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
2016-07-29 09:31:58 12359 [ERROR] Aborting

There is no special config, just:

wsrep_provider                  = /usr/lib/libgalera_smm.so
wsrep_cluster_name              = "legacy_cluster"
wsrep_cluster_address           = "gcomm://ZYX.4.123.242,XYZ.40.39.13"
wsrep_node_name                 = legacy6
wsrep_node_address              = XYZ.40.39.13

wsrep_sst_method                = xtrabackup-v2
wsrep_sst_auth                  = sst:secret

I've also tried rsync, but got the same error. I'm not sure how to further debug this error!?

Finally I figured out that AppArmor was blocking the SST. The most resources about setting up the Cluster I read were about SE Linux.

I put the mysql profile into the complain mode:
https://blogs.oracle.com/jsmyth/entry/apparmor_and_mysql


Temboard: when monitoring a server modifying postgresql.conf on the server is not effective? I've installed the Temboard tool (http://temboard.io/) to start monitoring my postgresql server. I've noticed that when I modify the postgresql.conf on the server and I restart it, the change is not taken into consideration. If I do it in the Temboard terminal it is. Is it normal? I was supposing that temboard should follow what is done in the postgresql.conf if I modify it on the server. Where temboard agent manages the server config?

Temboard presumably uses ALTER SYSTEM, which on the server side, writes into postgresql.auto.conf.

The contents of postgresql.auto.conf takes precedence over the contents of postgresql.conf, so if a setting is specified with both methods, the value in postgresql.conf is going to be ignored, no matter how you restart.

To get back to the situation where the setting in postgresql.conf is not ignored, consider doing

 ALTER SYSTEM RESET setting_name;

Then the next reload or restart should pick up the value from postgresql.conf.


Migrating from phsyical Mysql to virtual instance We currently have a physical db server on premise, running mySql version 5.6. We have another instance set up on an Azure virtual machine, as we are in the process of migrating our data center to Azure.

We don't have a dedicated DBA, and we are following the steps in this tutorial from the mySql site:

https://dev.mysql.com/doc/refman/5.7/en/replication-howto-additionalslaves.html

We keep failing on the 2nd step of copying the data directory, is there a workaround for this, or a better way to go about replicating?

Copying data directory would work if you are using the same version of MySQL. As per you question, you are copying from 5.6 to 5.7, so this way wouldn't work.

Now: you mentioned you are "migrating", and the steps you are following are for "creating new slave"!

If you want to "migrate", a better way is the following:

Make a full backup using mysqldump, or other "logical" backup tools (as opposed to physical copy of the files)
restore this backup on your destination server

HTH


How can I output a legible timestamp from "1583981745.335346" (stored in a 'double' field type) via a select statement? I have a table within a WordPress installation that stores timestamps in a format that I do not recognize, nor can I figure out how to translate it. In the MySQL database, it appears as "1583981745.335346". Within WordPress, in the plugin's log, this particular entry reads as follows:

03-11-2020
10:55:47.117 PM

Further examination of the table's structure via phpMyAdmin reveals that this particular column's type is 'double'.

I have done a ton of reading and testing and have even browsed the plugin's code, which can be found below, but I cannot for the life of me figure out how to translate this odd "1583981745.335346" format to a legible timestamp via a select statement.

https://github.com/WPWhiteSecurity/WP-Security-Audit-Log

Searching the files for the column name, 'created_on,' the closest thing I can find is the center of the section beginning on line 2012 of /classes/AlertManager.php:

    // Meta details.
    return array(
        'site_id'    => $site_id,
        'blog_name'  => $blog_name,
        'blog_url'   => $blog_url,
        'alert_id'   => $alert_id,
        'date'       => str_replace(
            '$$$',
            substr( number_format( fmod( (int) $created_on + $this->gmt_offset_sec, 1 ), 3 ), 2 ),
            date( $this->datetime_format, (int) $created_on + $this->gmt_offset_sec )
        ),
        'code'       => $const->name,
        'message'    => $occurrence->GetAlert()->GetMessage( $occurrence->GetMetaArray(), array( $this->plugin->settings, 'meta_formatter' ), $occurrence->_cachedmessage ),
        'user_name'  => $username,
        'user_data'  => $user_id ? $this->get_event_user_data( $username ) : false,
        'role'       => $roles,
        'user_ip'    => $ip,
        'user_agent' => $ua,
    );

Another clue is the following, found on line 62 of /classes/Loggers/Database.php:

$occ->created_on  = is_null( $date ) ? microtime( true ) : $date;

I have not been able to figure out what to do with this information, though, unfortunately. The ultimate goal is to be able to query this field (along with others, including in related tables) using a select statement and output an actual timestamp. Is this possible?

FROM_UNIXTIME can help.

select from_unixtime(1583981745.335346)

2020-03-12 02:55:45.335346

ref: fiddle

This is a datetime type so it can be handled as such in PHP.


MySQL Lock Wait Time Out Exceeded upon Delete I have a Delete Statement which fails with Below Exception

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

It waits for 50 Seconds for Lock and gives Up, I wanted to know which all SQL Statements can lock a table.

LOCK WAIT 6017 lock struct(s), heap size 376, 137376 row lock(s), undo log entries 22011
MySQL thread id 8242, OS thread handle 0x2b019e749700, query id 13811010 10.103.89.37 administrator updating
DELETE from CONFIGSTOREQASTAGINGREL.EVENT_DETAILS where UPDATE_DATE < 20191013 AND COMPONENT_NAME = 'health'
------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 194 page no 9414 n bits 17 index `GEN_CLUST_INDEX` of table `CONFIGSTOREQASTAGINGREL`.`EVENT_DETAILS` trx id 38176269 lock_mode X waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len=6; bufptr=0x2b01d5f29030; hex= 0000001ef5fb; asc       ;;
 1: len=6; bufptr=0x2b01d5f29036; hex= 0000024674c5; asc    Ft ;;
 2: len=7; bufptr=0x2b01d5f2903c; hex= af000004900110; asc        ;;
 3: len=30; bufptr=0x2b01d5f29043; hex= 333532613463313736333863613932393935336262653631646135306137; asc 352a4c17638ca929953bbe61da50a7; (total 32 bytes);
 4: len=30; bufptr=0x2b01d5f29063; hex= 316663346166393133353164633731393062363833393134386335326535; asc 1fc4af91351dc7190b6839148c52e5; (total 32 bytes);
 5: len=6; bufptr=0x2b01d5f29083; hex= 6865616c7468; asc health;;
 6: len=30; bufptr=0x2b01d5f29089; hex= 36436c74773144627644656948474472756e774d4f362020202020202020; asc 6Cltw1DbvDeiHGDrunwMO6        ; (total 32 bytes);
 7: len=4; bufptr=0x2b01d5f290a9; hex= 81341726; asc  4 &;;
 8: len=4; bufptr=0x2b01d5f290ad; hex= 800003aa; asc     ;;
 9: len=16; bufptr=0x2b01d5f290b1; hex= 4f494d2d534552564943452020202020; asc OIM-SERVICE     ;;
 10: len=4; bufptr=0x2b01d5f290c1; hex= 80000000; asc     ;;
 11: len=0; bufptr=0x2b01d5f290c5; hex= ; asc ;;
 12: len=30; bufptr=0x2b01d5f290c5; hex= 436f6e6e656374697669747920746f20536563757265204167656e742069; asc Connectivity to Secure Agent i; (total 168 bytes);
 13: len=4; bufptr=0x2b01d5f2916d; hex= 5da441f8; asc ] A ;;

I wanted to know what is Record Lock and when does that gets locked.I would very thankful if someone explains what is the meaning of above statements i got from SHOW INNODB ENGINE STATUS

Do you have INDEX(COMPONENT_NAME, UPDATE_DATE) with the columns in that order?  (Please provide SHOW CREATE TABLE.)

What else was running at the same time?  (SHOW PROCESSLIST before the DELETE times out.)

Let's see that other query; perhaps it can be optimized to run faster.

What is the value of autocommit?  Are you using BEGIN...COMMIT?


Replication in sql server 2019? I am reading 'Pro SQL Server 2019 Administration' book from Carter, and when detailing HA and DR options, 'replication' is not mentioned:

Why is it omited? Will Replication be deprecated soon?

Why is it omitted?

Because the replication NOT considered to be HA and DR solution, it is considered to be Migrate & Load Data feature within SQL Server, which mainly cover the requirement of distributing subset of data into different locations wherein HA and DR solution mainly focuses on whole database/SQL-Instance level Failover capabilities.

Will Replication be deprecated soon?

I don't think whole replication feature will be depreciated, perhaps some sub-features of replication. for your reference..


Alternative to UNION with indexing I'm on SQL Server 2012 and I have tables for different companies each one with its own customers:

CompanyA_Customers:

ID | Name
---------
11 | Foo
12 | Bar

CompanyB_Customers:

ID | Name
---------
11 | Baz
12 | Qux

And I need a single table to query them, so I did this:

CREATE VIEW Customers AS
    SELECT 'A' AS Company, ID, Name FROM CompanyA_Customers
UNION ALL
    SELECT 'B' AS Company, ID, Name FROM CompanyB_Customers

but I suffer poor performance since as far as I know on a UNION view I can't define any index.

Are there any alternatives to have better performance?

IMHO (and as Paparazzi has pointed out on comments) the easiest way to accomplish it and get better performance is using a single table by adding a CompanyID field.

CREATE TABLE Customers
(
    CompanyID   <some_type> REFERENCES Companies (CompanyID),
    CustomerID  <some_type> REFERENCES Customers (CustomerID),
    Name        text,
    PRIMARY KEY (CompanyID, CustomerID)
);

If you cannot modify your actual table schema, you could use two materialized views, one for each company, and add a clustered index on (company_id, id) or (id, company_id) depending on your queries.

I've set up a fiddle here

select company_id, id, name
from (
      select company_id, id, name from dbo.vcustA
      union all
      select company_id, id, name from dbo.vcustB
     ) x
where id = 2;

Execution plan for two materialized views using UNION:

But in fact, this is worst than create a new table with all records of both tables. Instead of use two materialized views, I'd suggest to create a new table and, using triggers, add/update/delete all records from customersA and customersB. Or set up a daily job that synchronize this information.


Need help figuring out correct permissions for SQL Server Users In most of my databases I create, I need to give permissions to do the following:

SELECT/INSERT/UPDATE/DELETE on all tables in the database
Execute all (user) stored procedures in the database

Will giving users datareader and datawriter be enough for the sproc's or what other roles/permissions do I need to give?

If I need to do more than just datareader and datawriter, could you point me in the right direction to creating a script to affect all users in the database.

Thanks in advance

With (1) you should be good giving them db_datareader and db_datawriter.

On (2) I would probably create a role and then grant that role execute permissions on all the stored procedures, in place of granting it to each individual user. Then just add those users to the role. If you have all the stored procedures under a schema name you should be able to grant execute to that schema. That will save time and not have to worry about new procedures being added to the database.


Select records based on earliest date in a linked record I am a retired teacher working on my first MYSQL database as a personal project; please let me know if I've inadvertently violated conventions. And thank you!

The database's domain is fiction stories, and I have these tables (among others):

  Stories: | story_id | title | genre | ...

  Venues : | venue_id | name | year | ...

  Story_in_Venue: | story_id | venue_id |

The Story_in_Venue table implements a many-to-many relationship, indicating that a given story appeared in a particular venue (such as a magazine or anthology). One of the common searches is for all stories within a given genre (say 'mystery') in which the earliest venue is in a particular year (say 1960).

If I made a generated column in the Stories table to hold the year of a story's earliest venue, then the search would be quick and easy, going straight to the stories I need (provided that I have an appropriate composite index). But it's been drilled into me to not duplicate information that's already available in another table.

Is there a good way to solve this problem:

without that earliest year column in the Stories table (and no other generated or trigger-constrained column either), and
without having to examine all mystery stories or a bunch of irrelevant venues.

Many thanks for sharing your knowledge on this beginner's question.

--Michael

Good question. First you need the full date rather than just a year to pick the earliest in year. So I'm assuming event_date in Venues

SELECT title, name, MIN(event_date)
FROM Stories s
JOIN Story_in_Venue sv ON s.story_id = sv.story_id
JOIN Venues ON v.venue_id = sv.venue_id
WHERE genre="Mystery" and event_date >= "1960-01-01" AND event_date < "1961-01-01"
GROUP BY s.story_id

The event_date and genre are the searched columns so they should be indexes/


Force SQL Engine to prepare Non-Null plan only for a query that use variable I have following two query approaches. The Approach 1 works in 20 seconds. The Approach 2 did not complete even after hours.

Approach 1

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX ('$', IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%'
GO

Approach 2

DECLARE @DelimitingCharacter CHAR(1)
SET @DelimitingCharacter = '$';

SELECT *,
        SUBSTRING(IDAndCode,CHARINDEX (@DelimitingCharacter, IDAndCode)+1, LEN(IDAndCode) - 2) AS ICDCode
From dbo.MyTable
WHERE F.Fact_Diagnosis_BK LIKE 'SKI-CE:'+'%'
GO

Note: The above two are simplified queries. The actual queries can be seen in the execution plan below.

Execution Plans

Actual Execution Plan – Approach 1
https://www.brentozar.com/pastetheplan/?id=rJlcWTk3m

Estimated Plan - Approach 2:
https://www.brentozar.com/pastetheplan/?id=SJddO3en7

Note: The plan for Approach 2 uses Nested Loop join instead of Hash Match join

Question

I know that this can be fixed by adding OPTION (RECOMPILE). If I understand it correctly, the slowness is happening since SQL Server is creating a plan to accommodate NULL possibility of the variable.

In my case, the variable will be Non-Null before the query is executed (guaranteed)

In SQL Server 2016, what are the possibilities to tell the SQL engine that the variable will be non-null and prepare a plan only for such condition, without using OPTION (RECOMPILE)?
Is the slowness called parameter sniffing? (There is no parameter here. It is a local variable in a stand alone query)

Note: I am trying to see options to get the query running faster without using RECOMPILE.

References:

SQL Server Query: Fast with Literal but Slow with Variable
Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues
Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
Local Variables vs. Parameterized Stored Procedures in SQL Server

Following are the three ways that can fix the issue. More approaches are welcome.

Solution 1

Instead of a variable(@DelimitingCharacter), use a temporary table.

CREATE TABLE #VariableHoldingTable (DelimitingCharacter CHAR(1) NOT NULL)
INSERT INTO #VariableHoldingTable
SELECT '<'

--And do a join

  INNER JOIN #VariableHoldingTable V
    ON 1 = 1

Execution Plan: https://www.brentozar.com/pastetheplan/?id=rJpehe5hQ

Solution 2

CREATE CLUSTERED INDEX CX_DXID on #MultipleDiagnosisDXs(DXID)

Execution Plan: https://www.brentozar.com/pastetheplan/?id=B1nKne9nX

Solution 3

OPTION (RECOMPILE)

Execution Plan: https://www.brentozar.com/pastetheplan/?id=BkRgalqnX


How to detect any changes to a database (DDL and DML) There are a lot of databases on my client's SQL server. These databases are under development, so developers can design, refactor, do data modifications and so on. There are some databases that change rarely. My client has to keep all of them safe (backed up) and spend some time managing the environment. (There is no DB administrator position at the company.) After lengthy discussion, the client has decided to use a daily full backup strategy, due to the ease of restoring.

So here is the summary of the situation:

Number of databases can vary every day.
Databases that were changed (meaning data and/or structure have been changed) shall be backed up.
Databases that were not changed shall NOT be backed up.
Solution shall not impact database structure (it's not restricted requirement)
This "backup engine" shall work automatically.

The main problem: how to detect that a database has been changed. The first part of the problem (DDL changes) can be resolved by using DDL triggers. But the data changes (DML changes) are a problem. It is impossible to apply DML triggers to all tables of all databases to track changes (performance, management of extended objects...). The backup engine has to track all changes to mark each database as ready to backup.

Change Data Capture is a solution but it seems too heavy (it requires SQL Server Enterprise Edition as well).
Another way is to track database file changes (size or last change time), but it does not work correctly: A database can change its size when it exceeds all reserved free space and sp_spaceused is not a solution.
Tracing is a solution but it causes performance issues and requires additional management.

Are there any solutions to calculate the actual database usage size without impact on other database management objects (like statistics..)? Granted that a change to a table's data that doesn't change the table's size would not trigger  (I think), but it's better than nothing. Really I am looking for a direct or indirect solution for SQL Server 2008.

Thank you for any comments, solutions, and thoughts.

ADDED:

Here is the solution (thanks to Marian):

Select
    NextLSN = MAX(fn.[Current LSN])
    ,Databasename = DB_NAME()
 from fn_dblog(NULL,    NULL) fn
     LEFT JOIN sys.allocation_units au
         ON fn.AllocUnitId = au.allocation_unit_id
     LEFT  JOIN sys.partitions p
         ON p.partition_id = au.container_id
     LEFT  JOIN sys.objects so
         ON so.object_id = p.object_id
    WHERE
    (
        (Operation IN
       ('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
            'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')
            AND so.is_ms_shipped = 0)
        OR
        ([Lock Information] like '%ACQUIRE_LOCK_SCH_M OBJECT%')
    )

One idea would be to make a snapshot every day and monitor the snapshot file size on the disk using a file monitor. The snapshot is increasing its size only when data is added there, so it would be a valid idea if you would find a tool to monitor the real size (reported size).
Now.. I didn't use this, so can't give you technical insights :-).
Another idea would be to verify the transaction log of each db (if you're using full recovery mode on them, of course) with some function I've seen on the forums (db_fnlog.. or something) that reads operations from the log, and see if you have any deletes/inserts/updates.
Those are no easy things to do.. but I hope you'll find them useful.
PS: found the article with the log read function (it's fndblog, by the way :-): Read the transaction log by Jens K. Suessmeyer.


Convert Oracle Query for Date to MySQL queries Need to convert the following oracle queries to MySQL queries.

1)

select to_char(to_date(?,'DD-MM-YY'))
from dual;

2)

select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY'))
from dual;

3)

select to_char(to_date(?,'DD-MM-YY'),'DAY')
from dual;

Your second query can be converted to MySQL in two ways.

First one is simple query, that adds interval of days to compared date, depending on which dayofweek it is. It's not the most elegant solution, and different days of week require different values (or selects).
Code below uses CURDATE as a starting value, query for each column can be used separately:

SELECT
 DATE(CURDATE()) AS now_date,
 DATE_ADD(DATE(CURDATE()), INTERVAL (9 - IF(DAYOFWEEK(CURDATE()) < 2, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_monday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (10 - IF(DAYOFWEEK(CURDATE()) < 3, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_tuesday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (11 - IF(DAYOFWEEK(CURDATE()) < 4, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_wednesday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (12 - IF(DAYOFWEEK(CURDATE()) < 5, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_thursday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (13 - IF(DAYOFWEEK(CURDATE()) < 6, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_friday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (14 - IF(DAYOFWEEK(CURDATE()) < 7, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_saturday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (8 - DAYOFWEEK(CURDATE())) DAY) AS next_sunday;

Code found and copied from old GizmoLA post's comment. Similar one, just for Sunday, was explained on StackOverflow.

Second one is more universal, but requires creating function. In this case it's code could look like this one, found on SQLines post:

CREATE FUNCTION next_day(start_date DATETIME, weekday CHAR(20))
 RETURNS DATETIME
BEGIN
 DECLARE start DATETIME;
 DECLARE i INT;

 -- Select the next date
 SET start = ADDDATE(start_date, 1);
 SET i = 1;

 days: LOOP
       -- Compare the day names
    IF SUBSTR(DAYNAME(start), 1, 3) = SUBSTR(weekday, 1, 3) THEN
     LEAVE days;
    END IF;

    -- Select the next date
    SET start = ADDDATE(start, 1);
    SET i = i + 1;

    -- Not valid weekday specified
    IF i > 7 THEN
     SET start = NULL;
     LEAVE days;
    END IF;

 END LOOP days;

 RETURN start;
END;

Usage (show next sunday):

SELECT NEXT_DAY(NOW(), 'Sun');

You can also use NEXT_DAY and SUBDATE functions to get the previous date:

SELECT SUBDATE(NEXT_DAY(NOW(), 'Sun'), 7);


SQL Server Bulk Insert properly interprets some Unicode characters but not others? For some reason the MS SQL Server 2016 bulk insert misinterprets/translates Unicode characters:

C9 (É) into 2B (+)
A1 (¡) into ED (í)
A0 ( ) into E1 (á)
AE (®) into AB («)
CB (Ë) into 2D (-)
D1 (Ñ) into 2D (-)
92 (’) into C6 (Æ)
96 (–) into FB (û)

i.e. Notepad++ and xxd show the flat file has 0xC9, but after bulk insert the table shows "+", and cast as varbinary in SQL Server shows it as 0x2B. The backup also has 0xC9.

I'm bulk inserting 25 flat files into MS SQL Server 2016. It's 15Gb data and I'm using a pipe (|) field delimiter and CRLF row delimiter.

I bulk insert into the truncated structure of a backup I am provided. And when I compare to the backup there are differences. NOTE: I have to wait 25 hours for the backup from the data source, but can get the flat files in 15 minutes.

Some differences are acceptable (find and replace I'm applying to the flat files), but many are due to Unicode characters being misinterpreted.

The structure of one example table is:

CREATE TABLE [dbo].[obfuscated_name](
    [ob_1] [int] NOT NULL,
    [ob_2] [int] NOT NULL,
    [ob_3] [int] NOT NULL,
    [ob_4] [nvarchar](300) NULL
) ON [PRIMARY]

The database collation is default: SQL_Latin1_General_CP1_CI_AS. No columns have a different collation. This collation should use code page 1252, which should properly interpret the chars I'm having trouble with.

My process is running against production data that is constantly in flux, so I'm worried other changes may pop up, and I'd like to know the source of the issue rather than try to isolate the issue and manually update the misinterpretations.

This is not a bug in SQL Server (or even in Windows), nor is it a situation that requires the additional step of converting the file into another encoding (i.e. into "Unicode", which in Windows-world means "UTF-16 Little Endian"). It is just a simple miscommunication.

The source of the communication breakdown (it's always the same, right ;-) is merely not agreeing on the nature of the source data. When moving character data from one place to another, it is important to specify the encoding on both sides. Yes, the SQL_Latin1_General_CP1_* Collations use Code Page 1252. However, if you don't tell BULK INSERT or BCP.exe what the Code Page of the source file is, then they will assume that the Code Page is the system default.

The documentation for BULK INSERT even states (for the CODEPAGE = argument):

  'OEM' (default) = Columns of char, varchar, or text data type are converted from the system OEM code page to the SQL Server code page.

The documentation for BCP.exe states (for the -C switch):

  OEM = Default code page used by the client. This is the default code page used if -C is not specified.

And the default Code Page for Windows is (at least for US English systems): 437. You can see this if you execute the following in a Command Prompt:

C:\> CHCP

It will return:

Active code page: 437

But your source file wasn't encoded using Code Page 437. It was encoded using Code Page 1252.

So here is what is happening:

Bytes are bytes. And bytes representing character data can only be interpreted via an encoding. Anything reading a file doesn't read characters from the file, it reads the bytes of the file and displays the characters based on the encoding that is specified.
BULK INSERT / BCP reads in byte 0xC9. 0xC9 displays as É when using Code Page 1252.
BULK INSERT / BCP is not given a source Code Page, so it checks the current Code Page for the process and is told: 437.
BULK INSERT / BCP now has a byte of 0xC9 for Code Page 437 (which displays as ╔, but BULK INSERT / BCP isn't displaying it so you won't see this)
BULK INSERT / BCP inserts this data into a column using a Collation that specifies Code Page 1252.
SQL Server sees that the incoming data is using a different Code Page than the destination uses and so has to convert the incoming data such that the characters appear to be the same (as much as possible), even if the underlying values change.
The Code Page 437 to Code Page 1252 mapping indicates that byte 0xC9 maps to byte 0x2B. Similarly, byte 0xAE (which is ® on Code Page 1252) on Code Page 437 (which displays as «) maps to byte 0xAB on Code Page 1252 (because it also displays as «).

The following example shows this conversion for all of the characters noted in the question:

DECLARE @CodePageConversion TABLE
(
   [ActualSource_CP1252] AS CONVERT(VARCHAR(10), CONVERT(BINARY(1),
                    [PerceivedSource_CP437])) COLLATE SQL_Latin1_General_CP1_CI_AS,

   [PerceivedSource_CP437] VARCHAR(10) COLLATE SQL_Latin1_General_CP437_CI_AS,

   [Source_Value] AS (CONVERT(BINARY(1), [PerceivedSource_CP437])),

   [Destination_CP1252] AS (CONVERT(VARCHAR(10), [PerceivedSource_CP437]
                  COLLATE SQL_Latin1_General_CP1_CI_AS)),

   [CP1252_Value] AS (CONVERT(BINARY(1), CONVERT(VARCHAR(10),
                  [PerceivedSource_CP437] COLLATE SQL_Latin1_General_CP1_CI_AS)))
);

INSERT INTO @CodePageConversion
VALUES      (0xC9), (0xA1), (0xA0), (0xAE), (0xCB), (0xD1), (0x92), (0x96);

SELECT * FROM @CodePageConversion;

which returns:

ActualSource_CP1252  PerceivedSource_CP437  Source_Value  Destination_CP1252  CP1252_Value
É                    ╔                      0xC9          +                   0x2B
¡                    í                      0xA1          í                   0xED
                     á                      0xA0          á                   0xE1
®                    «                      0xAE          «                   0xAB
Ë                    ╦                      0xCB          -                   0x2D
Ñ                    ╤                      0xD1          -                   0x2D
’                    Æ                      0x92          Æ                   0xC6
–                    û                      0x96          û                   0xFB

The characters for 0xC9, 0XCB, and 0xD1 do not exist in Code Page 1252, so the "best fit" mappings are used, which is why you end up with the + and - characters after the conversion.

Also, even if the destination column is using NVARCHAR, all of these mappings are the same, so you would be seeing the exact same behavior.

So, your choices are:

If using the T-SQL BULK INSERT command, specify the WITH CODEPAGE = option with one of the following values:

'ACP' (this is the same as '1252')
'RAW' (this uses the Code Page of the column's Collation if inserting into VARCHAR, or is the same as 'OEM' / Code Page 437 when inserting into  NVARCHAR)
'1252' (this is the same as 'ACP')

Or, if using BCP.exe, indicate that the incoming file uses Code Page 1252 via the -C command-line switch along with one of the following values (see notes in option #1):

ACP
RAW
1252

Please note that:

I tested with BULK INSERT, inserting into a VARCHAR column, using the set of characters noted in the question, and the ACP (which I believe stands for ANSI Code Page), RAW, and 1252 values all produced the correct results.
Not specifying WITH CODEPAGE = produced the same results that the O.P. reported in the question. This was the same as specifying WITH CODEPAGE = 'OEM'.
When inserting into an NVARCHAR column, both ACP and 1252 worked as desired, but RAW produced the same result as OEM (i.e. using Code Page 437 instead of Code Page 1252 which is specified by the Collation of the column).
I tested using BCP.exe, and not specifying the -C switch did not use the Code Page of the process. Meaning, using CHCP to change the command prompt's Code Page had no effect: Code Page 437 was still used as the source Code Page.

P.S. Since the data here is all 8-bit encoded, there are no "Unicode characters" since there is no Unicode being used.


Triggers on Slave database (in the context of replication) for auditing purpose in MySQL I have a requirement for auditing in MySQL database. It means I am interested in what data is changed in my database and when. For fulfilling this requirement I am thinking about the triggers. I am also using row based replication which ensures that data from master database is copied on slave machine. Since triggers might reduce the performance and it can introduce additional issues like row locking, I would like to avoid it on master database. Instead I would like to define triggers only on slave machine. Since data on slave will be identical with master, my requirement of auditing will be fulfilled also I can avoid performance and locking issues on master. Following are my environment details

Environment details

MySQL version 5.6 on windows
Master and Slave database with row based replication

So my questions are

Is it possible in MySQL to define the triggers only on slave?
Is this a correct approach?
Will it create any serious issue?

@parag Yes you can create triggers on the slave which won't be replicated. The only issue I see is if you're Master fails and you promote your slave to master you will need to keep track of what changes need to be applied to the new slave and what changes need to be applied to the newly promoted master.
If I'm not mistaken you can create the triggers on the master so that they can be replicated and then disable them and just enable them on your slave.  That way all your environments will have the triggers but only enabled on one slave.

Or, so you can avoid managing details like that, which can become overwhelming, you can make the slave, the master of another slave which will do the auditing and only have the triggers on that subsystem.

Thanks
John


How to do a simple SELECT/UPDATE/DELETE performance test? I have a table which might be causing excessive delays during insertion of new records.  I'd like to run a test against the database, measuring how long it takes to select, insert, and delete from the table.

Are there any built-in tools to do this against an SQL Server database table?

Simple down to 3ms accuracy:

SELECT GETDATE();
-- run query
SELECT GETDATE();

On SQL Server 2008 and above you can use SYSDATETIME() which has much greater accuracy.

You can also set a variable equal to the current time, run your query, and use DATEDIFF to determine the delta in whatever granularity makes sense (however this won't work across batches).

Other options:

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

You can also look at the execution plans to determine where a specific query might have a bottleneck. I might recommend the free SentryOne Plan Explorer for this. If you generate actual plans from within the tool, it will show you actual runtime metrics for each query (everything shown above, without the work).


SSMS login to different domain Our production database is hosted by another company on their domain.  I'm able to RDP into the box and login with the credentials they provided me but when I try to login from my local computer on my local domain using SSMS SSMS forces my local credentials.  I've tried but can find no way to change these credentials using the SSMS front end.

I attemtped using a shortcut with a target of

C:\...\runas.exe /netonly /user:DOMAIN\USERNAME "C:\...\ssms.exe"

But when SSMS comes up it appears to still be trying to use my local credentials and does not allow me to connect.  Is there something I'm missing.  How do I tell SSMS that its connecting to a different domain account?

That just looks that way because SSMS fills that in with the local credentials even if you passed it something else via command line.

Behind the scenes, it is using the username you specified in the /user: argument. It would be interesting to know what the actual error message is and if you tried - instead of using a shortcut - from running that command directly in a command line window (and not closing the CMD window while SSMS is still running).

I was just using this feature yesterday so I am 100% certain it works.


Row level restore with Oracle Recovery Manager (RMAN) We're approaching the end of 2012 and I still find myself relying on database exports to maintain the ability to do row level restore operations. While this is a proven method, it sure would be nice to get rid of all the export operations hogging database resources a significant number of hours each day.

A typical scenario would be that a customer accidentally deletes a project or document from an authoring system. To fix this I will need to locate and put back those deleted rows.

I'll pull up a database dump of the customer's schema from a date before the accidental delete, import the data to a new schema. Now I can identify the lost data, and select it back into the production schema.

I realize that you can do this directly with flash recovery, but there's a limit to how much data we can keep in the fast recovery area, and customers tend to not always figure out their mistakes within the allotted time.

Is there a good way to accomplish these kinds of tasks with the use of Oracle RMAN?

The answer to your question is no, however....

It sounds like a flashback query is what you need.  Query the data as of a time when it existed and when it returns the correct data, insert it into the current table.  This solution does require space in the UNDO tablespace sufficient to meet your UNDO_RETENTION requirements.  It also doesn't use RMAN, but is significantly simpler than importing the entire schema.

INSERT INTO T1 (
   SELECT * FROM T1 AS OF TIMESTAMP sysdate-1
   WHERE MyDateColumn = to_date('05/25/2011','MM/DD/YYYY')
);


Resuming Replication w/ Database Mirroring when Principle is unavailable I'm trying to setup a disaster recovery scenario and I'm stuck at this particular point.  Here's what I've done so far:

  A) Created principal database on Server A and and mirror on Server B

  B) Setup the Distribution database on Server C and modified the
  agent profiles with –PublisherFailoverPartner to Server B.

  C) Created two replication publishers on the principal.

  D) Setup a subscriber on Server D to the two subscriptions.

  E) Verified my subscriber was getting updates.

At this point, I shut down Server A hosting my principal database.  I then ran ALTER DATABASE  SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS on Server B to get my mirror database online.  With the old principal Server A still offline, I modified a record on a published table.  The change never made it to Server D, my subscriber.

I checked replication monitor and it shows no errors.  But on the Publisher to Distributor History it says 'Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.'

I understand that when I ran the FORCE_SERVICE_ALLOW_DATA_LOSS that my mirror became my principal and my old principal became the mirror (which will sync back to my new principal when brought online).

What I'd like to have happen is replication function like normal even while the Server A is down.  Then when it comes back, shut down the Server B and re-run the FORCE_SERVICE_ALLOW_DATA_LOSS command on Server A and have my original principal server back without tons of work.

Is this possible?  Does anyone have any advice outside of removing database mirroring after the initial failover (to get replication to work)?  Any alternate solutions?  What happens when Server A comes back online (regardless of whether mirroring was left in tact) with regard to the –PublisherFailoverPartner switch.  Does replication automatically go back to Server A?

Trace flag 1448 was the answer...

DBCC TRACEON (1448, -1) on Server B immediately sent the record to the subscriber.  No restart necessary.


keeping database columns consistent with documentation For a project, the client wanted to be able to add fields to any item and have them be searchable. To make them searchable, that would require the fields to take a column, but that would make the database design, in documentation, inconsistent with the columns present in the database.

So we ended up implementing a virtual column system; this made is so that 4 tables were needed for this feature:

main table containing the basic columns
a category table
a table to hold the virtual column information
a table used to define what the virtual column was called and what it holds.

When a column is added to a category, any row in with that category has an entry made in the virtual column table, which is foreign linked to the column type and the row it was made for.

Is this a bad idea for design or performance?

If the only reason not to add columns is "but that would make the database design, in documentation, inconsistent with the columns present in the database" then you need to look at your procedures.

If there is a business reason to add the fields and your systems support it then you need to have your documentation updated at the same time as the table. This should not be much more than adding a description fo the field to the documentation, perhaps having a place in documentation just for these user added fields.

Note that the business wants the system and the documentation is only a tool to help and not the end result.


Easiest Way to Migrate data from MySQL to MongoDB My latest IT project has me working with MongoDB for the first time. While I have a bit (very little) experience with NoSQL, I have always mostly worked with MySQL using Workbench.

I need to migrate the data from a MySQL database into MongoDB as my employer is switching over from the former to the latter.

Can someone suggest what might be the easiest way to do that?

Thanks a bundle!

Migrating between a RDBMS and a NoSQL DB is not the easiest thing.
If you can dump your data to JSON (or XML), I know that Navicat for MongoDB can import data in either of those formats.

I know that MySQL workbench can export in JSON format.  You might also want to try a Navicat for MySQL.  I use it and I know that it can export to either of those formats.

Navicat products aren't free, but both come with a 14 day trial.  That should be plenty long enough to get the job done.

Let me know how that works for you!

Dave


PostgreSQL : Where is index kept until check point occures ? In PostgreSQL data is first written to WAL files. When commit is done, data is written to WAL files and then return to client (synchronous commit). When  checkpoint reached or WAL buffer becomes full, these WAL files are written to data files. My question is where does the database keep indexes before writing to data files. Assume checkpoint occurs in every 15 min , then in this time window all the data is in WAL files and when user request for data, database read WAL files and provide data. In this time period does WAL also keep indexes ?

Your explanation here is not quite right:

  When checkpoint reached or WAL buffer becomes full, these WAL files are written to data files.

What happens when a CHECKPOINT hits is that dirty buffers held in shared_buffers are guaranteed to be written (i.e. fsync'ed) to disk. The WAL files must have already been fsync'ed to disk at COMMIT time, assuming synchronous_commit=on and fsync=on, etc.

Your question of:

  My question is where does the database keep indexes before writing to data files.

is answered here, in particular this bit:

  Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint.

(emphasis added). Also, your explanation of:

  then in this time window [before a CHECKPOINT] all the data is in WAL files and when user request for data, database read WAL files and provide data.

PostgreSQL reads its (index, heap, etc.) blocks either from shared_buffers, if they are available there, or from disk if not. Postgres does not need to read out of its WAL files other than for crash recovery (similarly, standby) purposes.


Availability Groups - Can async replica delay failover of sync replica? Availability Groups SQL Server 2012.

Node 1 and Node 2 in primary data centre - sync mode for HA
Node 3 in dr data centre - async mode for DR

If a manual failover is performed between node 1 and node 2 can the failover be delayed if there is high "log_send_queue_size" value to node 3?

I would hope not but also don't understand how SQL Server keeps track of the LSNs which still need to be sent when failing over.

When a Secondary (Node 3) is running in Asynchronous-Commit mode, Primary (Node 1) does not wait for a confirmation that Secondary has indeed finished writing the logs to its own log file.
It will still wait for a confirmation from a secondary in Synchronous-Commit mode (Node 2) though.

Right after Node 1 will be finished writing the log record to its own log file, it will consider that the transaction has been completed and the client will be notified.

With Asynchronous-Commit mode, Secondary is considered to never be synchronized with the Primary.
Secondary always remains unsynchronized and some lag with the Primary is to be expected although it will try to keep up and catch up with the Primary,

Therefore problems or lags on a Secondary in Asynchronous-Commit mode won't impact the Primary.

Much more information are available on this MSDN page: Availability Modes (AlwaysOn Availability Groups)


Using the same sequence twice in a PL/SQL statement I wrote the snippet of PL/SQL today:

declare
  first_id number;
  second_id number;
begin
  insert into table (sort_nr, text_id, unit_id) values(...,
  table_seq.nextval, table_seq.nextval) returning text_id, unit_id
  into first_id, second_id;
  dbms_output.put_line(first_id);
  dbms_output.put_line(second_id);
end;

and received a unique constaint violation. After futher examination, I discovered that first_id and second_id had the same value in it.

My question is: Is there any restriction calling the same sequence multiple times in the same statement and receiving a subsequent number? From my point of view it seems like the nextval is invoked only once in the scope of the query and cached.

Just a side note, I cannot change that crappy schema to avoid using the same sequence in two columns.

This is the expected behaviour of nextval as documented:

  Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once:

  For each row returned by the outer query block of a SELECT statement

  [...] If any of these locations contains more than one reference to NEXTVAL, then Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL.

This means that you won't be able to use plain SQL to overcome this limitation: you will need some PL/SQL. Either a trigger that populates both fields or a function that wraps the sequence call. Here's an example with such a function with 11g:

SQL> create sequence table_seq;

Sequence created.

SQL> create table test (sort_nr number, text_id number, unit_id number);

Table created.

SQL> create or replace function getid return number is begin return table_seq.nextval; end;
  2  /

Function created.

SQL> insert into test values (1, getid, getid);

1 row created.

SQL> select * from test;

   SORT_NR    TEXT_ID    UNIT_ID
---------- ---------- ----------
         1          1          2


Is there any performance gain due to not setting a default for a new column in Mysql? So, I need to add a new column of type tinyint to a database table with millions of records. Possible values for the column are 1 or 0. The default value I would want is 1. But, would setting a default value affect the database performance while the alter table query is running? Or is it better not to set any default value and then backfill by writing a script that backfills the column in batches?

When adding a TINYINT column:

If specified as NULL:  1-bit NULL set; 1 byte is allocated or not allocated (depending on Engine and ROW_FORMAT)
NOT NULL: no bit for nullness; 1 byte allocated and filled in with the specified DEFAULT (or the default DEFAULT, which is 0 for TINYINT).

If the TINYINT is to be only a true/false flag, then it does not help to make it NULL.

ALTER TABLE takes time and may impact other processes.  Is the ALTER TABLE what you are worried about?

Bottom line:  The ALTER needs to fill in the byte, so you may as well let it do all the work for you.


Updating a table based on condition I have a database having a staging_table.
From this table, entries are added to other table named Profile.
Whenever staging_table is updated, it checks whether all its entries are present in Profile, if any is not present, add it to profile.
Till, here its fine.
Now I want that even if an entry exists in Profile, there might be a condition where some fields in Profile have been changed. So also want those entries to get updated.

Requirement: I cannot use any flags or any other table to check for its update.

Should I compare all fields? But that is very in-efficient.
Please tell me, what should be the most efficient method that I should follow to achieve this?

Thanks

To make an efficient compare of all columns between 2 tables, you could use a UNION method with GROUP BY as described in Diff2Tables. Alternatively, if your profile table can have rows deleted from it without cascade delete, you could, drop indexes that are not for PK of the profile, Delete rows having the same key in both tables, and insert the rows from the other table into the profile then re-build the indexes on the profile table again. This may sound bad but in fact it is not that bad. This is a typical scenario in Data Warehousing.


sql problem with multiple linked tables I have an Oracle db and the following tables:

raportor:
id      id_pct_lucru
58382   327342

ses_rap
id  an      status
1   2012    I
21  2013    I
41  2014    D

ch_trat
id      fk_id_raportor  FK_ID_SR
20128   58382           41

taed
cod_deseu   cant    fk_trat_id
12.01.06    137     20128
12.01.13    50      20128
12.01.06    22      20128

tid
id      den     cod_tip
3557    dep3    HS
3555    dep1    B
3556    dep2    HZC

ticd
fk_inst_trat_id     cod
3557                12.01.06
3556                12.01.13
3555                12.01.06

and the following sql:

SELECT RAP.ID_PCT_LUCRU as id_pl,
          ses_rap.an_raportare as an,
          taed.COD_DESEU as cod,
          sum(taed.cant) as cant,
          LISTAGG (TID.COD_TIP, ',') WITHIN GROUP (ORDER BY TID.COD_TIP) AS tip_op,
     FROM ch_trat cht
          INNER JOIN raportor rap
             ON cht.fk_id_raportor = rap.id
          INNER JOIN ses_rap
             ON ses_rap.id = CHT.FK_ID_SR
          INNER JOIN taed
             ON taed.FK_TRAT_ID = CHT.ID
          INNER JOIN tid
             ON tid.FK_TRAT_ID = CHT.ID
          INNER JOIN ticd
             ON TICD.FK_INST_TRAT_ID = TID.ID
             and TICD.COD=taed.COD_DESEU
    WHERE SES_RAP.STATUS = 'D'
        group by RAP.ID_PCT_LUCRU,
            SES_RAP.AN_RAPORTARE,
            CHT.ID,
            taed.COD_DESEU
     order by RAP.ID_PCT_LUCRU

and the result is:

ID_PL   AN      COD         cant    TIP_OP
327342  2014    12.01.06    318     B,B,HS,HS
327342  2014    12.01.13    50      HZC

but I want the result:

ID_PL   AN      COD         cant    TIP_OP
327342  2014    12.01.06    159     B,HS
327342  2014    12.01.13    50      HZC

and I don't know how to do it. Please HELP!

For your SUM issue, please see this article for a detailed description of Oracle solutions that may be helpful --> https://community.oracle.com/thread/2137764?tstart=0

For the LISTAGG issue with duplicate values and values from other columns, check out this article for some solutions that may work for your need --> https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values

With some quick reading and not being an Oracle SQL guy, this seems as if there are a couple things in these that may help but I cannot easily provide an example query for you to use at the current moment unfortunately with this table data examples you provided.

Hopefully this will be helpful or point you in the right direction for the two issues I see with what results you get and what results you need.