Referential Integrity - Re-using table This is a simplified example of a design issue that I am facing: I have 3 tables: Car, Ship and Bicycle. I need to add  an "activity logging" table that records user actions such as deletion and user comments etc. I was thinking of creating just one table. Rather than 3 tables. The problem is with ensuring referential integrity. Should I create 3 separate columns that link to these tables? Should there be one column and I use it when needed? What is the general recommendation in such case? or should I just create 3 separate tables?

Arguments can be made either way depending on whether you want to handle populating these tables using triggers or the application(s).

I think this depends on what exactly you want to store in these logging tables, if you're logging anything specific to your 3 "data" tables then you'd be better off with 3 logging tables (1 logging table per "data" table which will be easier to populate using triggers), otherwise you can store everything in 1 table (with 1 field to reference each data table) which will make reporting easier.

As regards referential integrity, if you're actually deleting the original data record (rather than just marking it as deleted) then your Foreign Key will have nothing to link to anyway.

This is one of these situations where I de-normalize to gain centralized reporting and application logic (though triggers could be used for this, depending what you're storing).


Will installing a second SQL Instance cause an outage on existing instances? Like the title says, I just want to verify if we have the need to install a secondary SQL instance on a server that already has one SQL Instance, will the installation of the secondary instance cause an outage on the first?

You may require to restart before the install to pass prerequisites or after the install to complete the installation.
You should be prepared to reboot the server.


How to create a Unique (Index) constraint across multiple tables? Is there a way to create a unique index across tables in a SQL Server database?
I have two tables, Table A and Table B. In that I have a Column named ID, I want to make these Id columns as unique (combining Table A and B's Ids )

How can I do this ?

You could use a sequence for generating the values for ID fields. The documentation explicitly mentions your use case. The example C on said page has a sample implementation about this.

  Use sequences instead of identity columns in the following scenarios:

  The application requires sharing a single series of numbers
  between multiple tables or multiple columns within a table.

Be aware that there are a few catches. For example, sequence values are reusable, are not unique by default and can contain gaps. Pay attention to the Limits part in the docs.


How does this database design look? This is my first official design after having read a coursebook in preparation for the upcoming semester.  The goal is to have a user-based form creation/publication and submission web application.

A user should be able to create/publish a form on the site (my web server).  Then users (both the creator of the form and others) should be able to submit the form.  After that, a report could be generated and show which user submitted which answers.  I tend to over-complicate statements when simple is better.

Here is how I envision the relationships working:

A USER belongs to many GROUPS
A GROUP consists of many USERS
Due to the many-many association, a composite entity is created.
A USER can create many FORMS
Each FORM belongs to one USER
A FORM provides many QUESTIONS
Each QUESTION belongs to one FORM
A QUESTION has many ANSWERS
Each ANSWER goes with one QUESTION
A FORM has many SUBMISSIONS
Each SUBMISSION is tied to one FORM
A SUBMISSION has many SUBMISSION_ANSWERS
Each SUBMISSION_ANSWER is part of one SUBMISSION

Your design doesn't record "which user submitted which answers".

Should there not be a relationship between SUBMISSION and USER?  You have the attribute submission_user but you haven't indicated that it is a foreign key to USER.

Should there not be a link between SUBMISSION_ANSWER and ANSWER?  How else do you know which question is being answered and what the value of the answer is?

SUBMISSION_ANSWER.attribute_name seems like you left something incomplete.


InnoDB: mmap failed; errno 12 I am getting following error after modifying the innodb buffer pool size values.

InnoDB: mmap(137428992 bytes) failed; errno 12
InnoDB: Cannot allocate memory for the buffer pool

137428992 is innodb_buffer_pool_chunk_size.

I am having a machine with 256 GB RAM.

Following is what I added to my.cnf

innodb_buffer_pool_size=200G
innodb_buffer_pool_instances=40

All the other values are default. Resolution for similar questions are either increase RAM or decrease buffer pool size. If I monitor the memory usage when mysqld is starting, I see that it increases to 8-9 GB before the failure occurs.

Following is free -g output

              total        used        free      shared  buff/cache   available
Mem:            251           2         247           0           1         248
Swap:             3           0           3

What could be causing this issue?

You are probably running the service on Linux. I've had similar problems with allocating large amounts of memory on Linux (as opposed to BSD) even if the amount of memory I was trying to allocate was reasonable in proportion to the system's total memory.

MySQL suggests having innodb_buffer_pool_size / innodb_buffer_pool_chunk_size < 1000. https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size

Right now you are at about 1455. Try setting innodb_buffer_pool_chunk_size = 274857984. Or even 549715968.

This will also change the allocation pattern on the OS level which might help the operation to succeed. Also adding some swap could help the allocation to succeed by providing even more addressable virtual memory - not that the swap would ever actually be used.


How do I prevent my clients viewing my database definition when it is installed on their server? I want to give my database to the client to install on their server.

However, I want to deny the client rights to see the tables and stored procedures and also want to restrict that database to run from a single IP address.

How do I stop the user viewing the database tables even though database is present on their server?

We want to give the application to run on their own servers, but we don't want to show the tables and stored procedures. They are also not supposed to make any modifications to the database. Is this possible?

Perhaps can we encrypt database credentials in the web.config file?

Short Answer: you can't.

If the database is being run on the customer's infrastructure, then the customer must have control over their own infrastructure.  Without it, they cannot operate.

If you really don't want them to have that control, then you cannot give them the database.  This is the "Software-as-a-Service" Operating Model wherein you licence the application but don't get to play with the "back-end" of things.

I would suggest that this is not a Technical problem.  It's a Licencing one.

I assume that the licence agreement for your software includes a provision that prevents any Customer from "Reverse Engineering" any part of your product.  That clause should be extended to include your database.

Any "mucking about" with the database should invalidate [at least] your support agreement with that Customer.  If they want to take that Risk, well, that's up to them.


Stored procedure to validate xml content against dtd I have a stored procedure which creates xml content and then is stored to a xml file. Is it possible to validate the generated xml content against a dtd file?

Thank you in advance!

It is not possible to validate XML against a DTD file using SQL Server.

You can validate the XML with a XSD stored in a XML Schema Collection using a strong typed XML variable

DECLARE @x xml(YourXMLSchemaCollectionName);
SET @X = 'YourXML';


Need advice on database normalization I am working on a basic blood bank database on Oracle. I would like to create a completely basic normalized table and then I will add more details to it. I have done it to the 2NF and I will further proceed on to the 3NF afterwards, but first can someone take a look at my normalization and help me improve it (if needed) or any advices regarding it.

Excellent points by @joishi-bodio.

Here's a simple diagram explaining the different entities, attributes, and their relationships in your model.

The first step in modeling any database is to identify the real-world entities; the attributes that define each entity; and then their relationships and cross references.

Here are different observations and conclusions that you may draw from the example -

Entities and Attributes -

The most obvious real-world entity looking at your example is PERSON.
The entity Person can be defined with attributes like Name, Age, Gender, DOB, Blood group type
A PERSON can be either a 'Blood Donor' or 'Blood Receiver' or both. 'Donor' and 'Receiver' sound like the different roles that a Person takes, but are not different entities by themselves.
Blood Group can be another entity and can be defined by the name of that blood group among other things.
In a hospital, blood is stored in blood bags, which could be another entity, defined by who the donor for that blood bag is.
Transactions is another table which is the store for all blood transactions. A transaction can be defined by a blood bag being assigned to a person (receiver) on a date.

Relationships:

Every person has exactly 1 blood group. You can have 0 or many people with that group.
Each bloodbag has blood from 1 person. A person may or may not have blood in many blood bags.
You can assign 0 or many blood bags to a receiver. A person may or may not be a donor to one or many blood bags.

We get something similar to -


"ENUM" is not vallid at his position, expecting ')' I'm keeping this error and I'm very new to mySQL so I don't know what is going on. The only way I found to remove this error  message is to put ')' before the ENUM and then the message change - "ENUM" is not valid at this position, expecting EOF, ';'.
USE labo3_partie2;
CREATE TABLE fournisseur(
        numero SMALLINT UNSIGNED PRIMARY KEY,
        nom VARCHAR(40) NOT NULL,
        prenom VARCHAR(40),
        chiffreAffaire DECIMAL(7,2) CHECK ((chiffreAffaire >= 20000) && (chiffreAffaire <= 1000000)),
        province CHAR(2) DEFAULT 'QC' ENUM('QC','ON','NB')
);

MySQL (and SQL in general??) is very picky about the order of things1.
province CHAR(2) DEFAULT 'QC' ENUM('QC','ON','NB')

-->
province  ENUM('QC','ON','NB')  DEFAULT 'QC'  -- if you want ENUM

But also, ENUM and CHAR are two different datatypes.  So, maybe you wanted this:
province CHAR(2) DEFAULT 'QC'                 -- if you want CHAR

1 - You should try to become comfortable reading syntax diagrams, such as this for the create table statement.


After Exporting Database should be empty I am exporting data from database for that I have written following query:

SELECT ParameterID, Name,Type,Datatype,LogDate
FROM Param_Ems
WHERE DataType = 10
INTO OUTFILE 'C:/tmp/ParamData.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Is there any way to export and delete these records within the same query?

Use pt-archiver tool. It can delete data that it moves. Alternatively use drop & create after the select into outfile.


MYSQL count based on conditions in same column l have the following data sample

CREATE TABLE IF NOT EXISTS `tbl_result` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(10) NOT NULL,
  `subject_id` int(5) NOT NULL,
  `score` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

INSERT INTO `tbl_result` (`id`, `student_id`, `subject_id`, `score`) VALUES
(3, 'R135722F', 7112, 43),
(4, 'R135722F', 6118, 55),
(5, 'R135722F', 2076, 45),
(6, 'R135722F', 4402, 76),
(7, 'R135722F', 2234, 34),
(8, 'R134567Y', 6118, 65),
(9, 'R134567Y', 2076, 87),
(10, 'R134567Y', 4402, 43),
(11, 'R134567Y', 2234, 65),
(12, 'R134567Y', 1111, 34),
(13, 'R134567Y', 3454, 65),
(14, 'R134567Y', 3456, 87),
(15, 'R137634H', 7112, 98),
(16, 'R137634H', 6118, 54),
(17, 'R137634H', 2076, 45),
(18, 'R137634H', 4402, 33),
(19, 'R137634H', 2234, 65),
(20, 'R137634H', 2234, 65),
(21, 'R137634H', 1111, 12),
(22, 'R137634H', 3454, 54);

How do l retrieve the following how many students passed the following subjects ie

Those who passed 3 or subjects including subject code 7112 , 6118
Those who passed 4 or more including subject code 1111 , 3454

l have tried to use the in function but it  did not produce desired result

    SELECT COUNT( * )
FROM  `tbl_result`
WHERE  `subject_id`
IN (
 '7112',  '6118'
)

ANd the following but failing . also tried this one

SELECT *
FROM  `tbl_result`
WHERE  `subject_id` =  '7112'
OR  `subject_id` =  '6118'
AND  `score` >49
GROUP BY  `student_id

As far as you don't show us your desired result let me show some approaches to your question.

Your first example:

SELECT COUNT(student_id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118');

give a result of 5, it doesn't matter if you use COUNT(*) or COUNT(student_id).

| count |
|:-----:|
|   5   |

If you want to know which students then you can use GROUP BY student_id, but then you should add student_id to the SELECT statement:

SELECT subject_id, COUNT(id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
GROUP BY subject_id;

Result:

| subject_id | count |
|:----------:|:-----:|
|    6118    |   3   |
|    7112    |   2   |

For the second one use the same syntax adding more conditions:

SELECT subject_id, COUNT(id) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
AND    score > 49
GROUP BY subject_id;

Result:

| subject_id | count |
|:----------:|:-----:|
|    6118    |   3   |
|    7112    |   1   |

SELECT COUNT(*) count
FROM   tbl_result
WHERE  subject_id IN ('7112',  '6118')
AND    score > 49;

| count |
|:-----:|
|   4   |

Rextester here


Analyse MySQL General Query Log in Real-time? We want to use mysql general query log to do real-time monitoring and auditing.

Currently our approach is:

set general_log=on;
sleep 15m;
set general_log=off;
scp & rm xxx.log;
set general_log=on;...

But the main problem is when turn on/off general log it'll cause a peak of slow query.

I also thought of another approach: turn on genlog; tail -f it and send out the log; periodically truncate the logfile (with "> xxx.log" or "cat /dev/null >xxx.log").

I'm wondering whether it's practical.

If only mysql would provide some built-in general log message queue stuff...

If you are really interesting in monitoring via the general log, there are two major approaches you can try:

APPROACH #1 : Rotating Logs

Let's say you have the following configuration in my.cnf:

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log

You could rotate the general log with the following script:

MYSQL_CONN="-uroot -prootpassword"
DT=`date +"%Y%m%d"`
OLDLOG=mysql_general_${DT}.log
cd /var/log
mysql ${MYSQL_CONN} -e"SET GLOBAL general_log = 'OFF';"
cp mysql_general.log ${OLDLOG}
echo -n > mysql_general.log
mysql ${MYSQL_CONN} -e"SET GLOBAL general_log = 'ON';"

As for your monitoring, you would scan the text file as you already do. You can always look back at previous copies of the general.log as archives.

APPROACH #2 : Make the General Log a MyISAM table

Try setting up the general log as MyISAM.

First let's configure the table for logging to tables

[mysqld]
general-log
log-output=TABLE

Next, MySQL supplies the table mysql.general_log. It looks like this:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'

Of course, the general log as a CSV table is hardly useful. You can do two things:

Turn it into a MyISAM table
Index it however you need, starting with the event_time

Here is that code:

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;

When the smoke clears, mysql.general_log now looks like this:

mysql> show create table general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)

Events get recorded in this table instead of the text file. Naturally, you want to rotate this as well. Here is that script

SCRIPT=/tmp/rotate_general_log_table.sql
MYSQL_CONN="-uroot -prootpassword"
DT=`date +"%Y%m%d"`
OLDLOG=general_log_${DT}
echo "SET GLOBAL general_log = 'OFF';" > ${SCRIPT}
echo "ALTER TABLE mysql.general_log RENAME mysql.${OLDLOG};" >> ${SCRIPT}
echo "CREATE TABLE mysql.general_log LIKE mysql.${OLDLOG};" >> ${SCRIPT}
echo "SET GLOBAL general_log = 'ON';" >> ${SCRIPT}
mysql ${MYSQL_CONN} < ${SCRIPT}

As for your monitoring, you would query the mysql.general_log table like this:

SELECT * FROM mysql.general_log WHERE argument LIKE '...';

Perhaps yo u may want to check the last 4 hours

SELECT * FROM mysql.general_log
WHERE event_time >= (NOW() - INTERVAL 4 HOUR)
AND argument LIKE '...';

CAVEAT

If you want both the text file and the table, then configure them both like this:

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log
log-output=TABLE,FILE

Of course, you would have to implement the rotation of both.

I have written other past posts about mysql.general_log:

Apr 04, 2013 : Stress test MySQL with queries captured with general log in MySQL
Feb 11, 2012 : MySQL general log
Jan 08, 2012 : How to enable MySQL general log?
Jun 30, 2011 : How do I output MySQL logs to syslog?

Give it a Try !!!


Xp_delete deletes old backup even when the backup job failed I run the following scripts for a backup job. After the backup job completes, (xp_delete) deletes the old copy of full backup.

Typically, if the backup fails the BACKUP DATABASE statement on the 2nd line fails which breaks out of the entire script. Now the problem is that the backup job fails and xp_delete command still deletes the old copy of the backup. I don't have any other copy of the backups.

DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10), SYSDATETIME(), 112 ) + N'.bak';

BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10

DECLARE @backupSetId AS INT
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'MYDB' AND backup_set_id = ( SELECT MAX( backup_set_id ) FROM msdb..backupset WHERE database_name=N'MYDB' );

IF @backupSetId IS NULL BEGIN
    RAISERROR( N'Verify failed. Backup information for database ''N'MYDB'' not found.', 16, 1 )
END

RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId

DECLARE @cutoff NVARCHAR(50) = CONVERT(NVARCHAR(25), DATEADD(HOUR, -20, SYSDATETIME()), 120 );

EXECUTE master.dbo.xp_delete_file 0, N'V:\Backup', N'bak', @cutoff

Note: I am using SQL Server 2008R2 standard edition .

Raising an error does not necessarily stop the execution of the script and this is why your files are being deleted. See this Connect request for Microsoft's response to a similar question and this Question on StackOverflow for more details.

Overcoming this issue in your particular example is actually pretty simple by using program flow like so:

DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10), SYSDATETIME(), 112 ) + N'.bak';

BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10

DECLARE @backupSetId AS INT
SELECT @backupSetId = position
  FROM msdb..backupset
 WHERE database_name=N'MYDB'
   AND backup_set_id = ( SELECT MAX( backup_set_id )
                           FROM msdb..backupset
                          WHERE database_name=N'MYDB' );

IF @backupSetId IS NULL
BEGIN
     RAISERROR( N'Verify failed. Backup information for database ''N'MYDB''' not found.', 16, 1 )
END
ELSE
BEGIN

RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId

DECLARE @cutoff NVARCHAR(50) = CONVERT(NVARCHAR(25), DATEADD(HOUR, -20, SYSDATETIME()), 120 );

EXECUTE master.dbo.xp_delete_file 0, N'V:\Backup', N'bak', @cutoff
END

If you has a more complicated example I would recommend a try-catch block but I don't think that is needed here.


Using COPY in Postgres I know COPY is the best utility to export the data from a table or a query to output files. But if we are doing an export like
\COPY (select * from table) to '/tmp/file.csv' WITH CSV;

How its different from running the select query and save the results later(like in a GUI tool) or psql -c'select * from table'> output.csv
Any advantage of using COPY in terms of performance and caching?
The overall idea is, I'm trying to understand the performance benefits of exporting the data via read from select vs COPY from python psycopg

With COPY, the server formats the data, and there is no formatting effort on the client.
You should test it – that should be simple to benchmark.


If the DBAs restored a single table from backup, would there be evidence anywhere in the data dictionary? In Oracle 12.1, if the DBAs restored a single table from backup, would there be evidence anywhere in the data dictionary?  If so, what would it be?

Depends on how the DBA restored it; if flashback is enabled for the database, there could be evidence that it was restored using the

flashback table scott.mytab to before drop;

command.  Here are stats on a personal table, before dropping, then after restoring it via the above command; I imagine other recovery techniques might also fill in the same data:

SYS@dev01> select CREATED, LAST_DDL_TIME, TIMESTAMP from dba_objects where object_name='MYTAB';

CREATED             LAST_DDL_TIME       TIMESTAMP
------------------- ------------------- -------------------
2018-07-09 19:18:29 2018-07-09 19:18:29 2018-07-09:19:18:29

SYS@dev01> drop table "MARK.STEWART".mytab;

Table dropped.

AtlasDev:SYS@dev01> flashback table "MARK.STEWART".mytab to before drop;

Flashback complete.

SYS@dev01> select CREATED, LAST_DDL_TIME, TIMESTAMP from dba_objects where object_name='MYTAB';

CREATED             LAST_DDL_TIME       TIMESTAMP
------------------- ------------------- -------------------
2018-07-09 19:18:29 2018-12-15 03:09:31 2018-12-15:03:09:31

SYS@dev01>

Now the DBA could have defined triggers to monitor table creation and deletion, and auditing could capture such activity if enabled.


SQL Server starts normally from Windows Services screen, but shows error through SQL Server Configuration Manager. Why? I have a SQL Server 2005 instance that has worked fine for years. There have been no changes that I am aware of. Recently, I found that if I stop and then start the instance in SQL Server Configuration Manager, I get the error:

The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details

However, the service has indeed started, regardless of what the error says. If I dig through Event Viewer and the SQL Server ERRORLOG file, everything looks normal. There are no errors anywhere. In fact, if I start the service from the normal Windows Services screen (services.msc), it starts up immediately. Should I be concerned that it generates a false error through SQL Server Configuration Manager?

Also, is the Services section in SQL Server Configuration Manager just a "shell" of the full  Services screen, which is limited to SQL Server related services? Is there any reason not to stop and start SQL Server services from the full Windows Services screen?

SQL Server Configuration Manager performs additional operations that the Windows Services does not. Please check Technet SQL Server Configuration Manager particularly if you have changed the service account and/or password recently or use startup options.


Can you create an unrecoverable SQL Agent back log? I have created and am using an SQL Agent for the first time. I have searched google but either the info isnot there or I am not getting the keywords correct (SQL agent + back log/callstack overflow etc !).

My agent reads in and does some processing on a (variable) volume of data. I observed an error recently where i deadlocked a database i think due to an issue with the SQL agent and it started me thnking about the following: If I set the schedule frequency to be shorter than the duration that it took to compete the action that is performed by the agent, such that the 2nd action is scheduled while the first is still processing ...and so on. Will this generate an unrecoverable situation by accumulating jobs that it will never have time to process or if the agent starts while a previous job is still running does it just abandon the attempt and check again at the next scheduled interval.

Obviously one wouldnt want to do the above on purpose but it just got me thinking about whether i need to handle for the above possibility.

Cheers

If you're job is running at the time the next scheduled execution comes along it will be skipped

https://stackoverflow.com/questions/865042/will-a-sql-server-job-skip-a-scheduled-run-if-it-is-already-running


Optimization: insert only not existing rows with additional conditions I have two databases:

'target' database looks like

CREATE TABLE parent (
  erow_id  integer PRIMARY KEY,
  -- these two columns is a composite key (uid)
  uid_p1   integer,
  uid_p2   integer,
);

CREATE TABLE child (
  erow_id  integer PRIMARY KEY,
  parent   integer, -- pointed to parent.erow_id
  value    text,
  vtype    integer
);

-- only this one index is presented
CREATE INDEX idx_child_parent ON child (parent);

'patch' (it's attached database) looks like

CREATE TABLE source (
  value   text,
  -- composite uid pointed to target.parent
  uid_p1  int,
  uid_p2  int
);

Both databases are 'fixed' and it's really difficult to modify'em, even add indexes.

I need to insert new rows (values) to child from patch only if parent has no rows of type = 100 (for example).

At this moment I use such an ugly query:

INSERT INTO target.child (value, parent, vtype)
  SELECT
    p1.value, target.parent.erow_id, 100
  FROM
    patch.source p1
    INNER JOIN target.parent
      ON (target.parent.uid_p1 = p1.uid_p1
      AND target.parent.uid_p2 = p1.uid_p2)
  WHERE NOT EXISTS (
    SELECT
      1
    FROM
      patch.source p2,
      target.child,
      target.parent
    WHERE
      (p1.rowid = p2.rowid) AND
      (target.child.vtype = 100) AND
      (target.child.parent = target.parent.erow_id) AND
      (target.parent.uid_p1 = p2.uid_p1) AND
      (target.parent.uid_p2 = p2.uid_p2)
  );

EXPLAIN QUERY PLAN:

SCAN TABLE patch.source AS p1
EXECUTE CORRELATED SCALAR SUBQUERY
SEARCH TABLE patch.source AS p2 USING INTEGER PRIMARY KEY (rowid=?)
SCAN TABLE target.child // looks like a bottleneck
SEARCH TABLE target.parent USING INTEGER PRIMARY KEY (rowid=?)
SEARCH TABLE target.parent USING AUTOMATIC COVERING INDEX (uid_p1=? AND uid_p2=?)

Is it possible to optimize this incredibly slow query w/o new indexes? It's a big problem - I cannot modify at least target db at this moment.

Thank you.

Besides adding indexes (which you say is not allowed), the query is unnecessarily complex. The 2 of the 3 table references in the correlated subquery are not needed as they join to the same tables in the main query and on primary keys. You can simplify it to:

INSERT INTO target.child (value, parent, vtype)
  SELECT
    p1.value, p.erow_id, 100
  FROM
    patch.source AS p1
    INNER JOIN target.parent AS p
      ON  p.uid_p1 = p1.uid_p1
      AND p.uid_p2 = p1.uid_p2
  WHERE NOT EXISTS (
    SELECT
      1
    FROM
      target.child AS c
    WHERE
      c.vtype = 100 AND
      c.parent = p.erow_id
  );

An index on child (vtype, parent) would help I think for performance. If that index is UNIQUE, the query can be simplified further using OR IGNORE clause:

INSERT OR IGNORE
INTO target.child (value, parent, vtype)
  SELECT
    p1.value, p.erow_id, 100
  FROM
    patch.source AS p1
    INNER JOIN target.parent AS p
      ON  p.uid_p1 = p1.uid_p1
      AND p.uid_p2 = p1.uid_p2 ;

What OR IGNORE does:

  IGNORE

  When an applicable constraint violation occurs, the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is used.


Select youngest record I have a MariaDB table "mdata" with the following schema:

+-----+-----------+---------------------+------+
| id   | room      | date                | temp |
+------+-----------+---------------------+------+
| 1    | a2        | 2019-08-09 11:04:30 | 34.0 |
| 2    | a4_01     | 2019-08-09 11:04:30 | 32.0 |
| 3    | a3        | 2019-08-09 11:04:32 | 0.0  |
| 4    | a1_4      | 2019-08-09 11:04:32 | 27.0 |
| 5    | a2        | 2019-08-09 11:09:30 | 33.0 |
| 6    | a4_01     | 2019-08-09 11:09:30 | 34.0 |
| 7    | a3        | 2019-08-09 11:09:32 | 0.0  |
| 8    | a1_4      | 2019-08-09 11:09:32 | 29.0 |
+------+-----------+---------------------+------+

Every 5 minutes new temperature values will be written into the database.
I don´t want to overwrite the old values, because a timeline will be implemented later on.
Now I´ve created a web interface to display the youngest values.

Currently I´m struggling to find the right SELECT query.
It is important that the output of the query is in the same order as in the table, because it is put into an array and processed further.

I tried something like this:

SELECT *
FROM mdata
WHERE temp IN
(SELECT temp FROM mdata WHERE date = (SELECT MAX(date) FROM mdata))

WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY room ORDER BY `date` DESC) rn
             FROM mdata)
SELECT id,room,`date`,`temp`
FROM cte
WHERE rn=1

If a table contains huge array of data you can slightly restrict the amount of scanned records:

WITH
cte1 AS (SELECT room, MAX(`date`) `date`
         FROM mdata
         GROUP BY room),
cte2 AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY room ORDER BY `date` DESC) rn
         FROM mdata
         WHERE `date` >= (SELECT MIN(`date`)
                          FROM cte1))
SELECT id,room,`date`,`temp`
FROM cte2
WHERE rn=1


ibdata1 increases when firing update queries I have read a lot of posts which explain that the only solution to stop the growth of an ever increasing ibdata1 file is to:

Take a dump of all databases
Set innodb_file_per_table in the mysqld section of my.cnf
Clean the data directory (except mysql)
Restart the MySQL Server and load the dumps using mysqldump.

However my problem starts once I have done all these steps. I still see a scenario where the size of my ibdata1 file increases. The scenario is explained below:

I start MySQL Server 5.5 with a clean data directory (only the mysql directory is present), the size of the ibdata1 file reads 18 MB. Next steps follow:

I have the innodb_file_per_table entry already mentioned under the mysqld section of my.cnf.
Now I load some tables from mysql dump files (all of them have InnoDB storage engine specified) using mysqldump. I observe that ibdata1 size has not changed and separate ibd files have been created which is expected according to the innodb_file_per_table setting
I add a new column say, ColumnA (int, NOT NULL, UNSIGNED, DEFAULT 0) to a table, say Table A. The storage engine is InnoDB and contains 9 million records. So far so good, the size of ibdata1 file remains the same (18 MB)
I now update the values in ColumnA using the following query: update TableA set ColumnA = crc32(someothervarcharcolumnfromthistable);
I observe that during the update the size of ibdata1 consistently grows and grows past 300 MB.

Why is this happening? Does this mean I have to do the cleanup-restart-restore activity mentioned at the beginning every 'x' months, or am I missing something?

Edit 1:
This question is not a duplicate of this question answered by RolandoMySQLDBA. Reasons follow:

It says what ibdata1 stores in general. However it does not clarify when all precautions are taken against its increase, why would it still do so when performing operations on a particular table.
Though it is very informative, however it outlines the corrective measures that can be taken, does not highlight preventive measures post the activity is done.

In case it is not clear from my question, I wanted to get the opinion of experts on preventive measures once I have set innodb_file_per_table for ibdata1 not to grow.

Lets try to understand whats stored inside ibdata1:
When you have innodb_file_per_table enabled, the tables are stored in their own tablespace but the shared tablespace is still used to store other InnoDB’s internal data:
* data dictionary
* change buffer
* doublewrite buffer
* undo logs

since there is trancations, rollbacks that support repeatable read that will make ibdata1 files grow even when you update any innodb table.

reference:
http://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

ibdata1 grows exponentially when innodb_file_per_table is configured

https://serverfault.com/questions/272146/why-ibdata1-is-still-growing-when-i-have-used-innodb-file-per-table-in-mysql-5-1


Shared Memory with Microsoft Hekaton Does the Microsoft SQL Server 2014 In-Memory Engine supports Shared Memory access like Oracle TimesTen? I only found that there was a protocol with this name in SqlServer2008  but i found nothing for SqlServer2014

thanks

Hekaton does not support direct memory access from client applications. In theory an application could access the same memory space that SQL Server uses using OS memory sections. That is not implemented, though.

The shared memory transport has nothing to do with this. Indeed, it uses a shared memory region to transfer data. But it still runs the normal TDS protocol (including the data conversion and serialization that this involves).


Disable Binary Logging During MySQL Dump I do apologize if this has been asked before but my search skills were not able to turn up a definitive answer.

SITUATION:
I have two Windows Server 2012 R2 servers running WAMP 3.06 (which contains MySQL 5.7.14).  They are setup for replication with a master-slave configuration using binary logging.  I have a PowerShell script that makes a dump of the database each night.  The PowerShell code looks like this...

# Variables
. (Join-Path $PSSCriptRoot MySQL_Vars.ps1)

# Backup the database
cmd /C " `"$mysqlDump`" $mysqlRoutineFlags -u $mysqlUser -p`"$mysqlPass`" $mysqlDB > $mysqlBkp"

QUESTION:
How could I do this so that when the MySQL dump occurs, it is not written to the binary logs?

Thank you in advance for your help!

Only "writes" go to the binlog.  A backup only does "reads".

Hence "How ... when the MySQL dump occurs, it is not written to the binary logs?" is a non-issue.

(Or, do I not understand the perceived problem??)


Is it possible for heroku postgres follower database to follow non-heroku postgres master? I am considering to move our site's database from our dedicated db server to heroku postgres. as we have a few gigs of data in the db and don't want to take it down for a long migration downtime it appeared to me that it might be possible to set up replication(following in heroku terms) from our current database server to a heroku 'follower' database. We could then make our current db read-only for a short time while the heroku comes up to date and then make the switch very quickly.
the heroku documentation(https://devcenter.heroku.com/articles/heroku-postgres-follower-databases#database-upgrades-and-migrations) mentions replication between databases of different heroku apps, but it does not touch on the scenario i want to set up where the master db is not on heroku.
Anyone thinks or knows this is doable?

It's not supported in either direction, with the most pressing problem being the platform and architecture dependence of that replication method.

In the case of migrating to the service, the problem is doubly so, because slurping random, physical base backups from the internet as would be required to set up replication as it is seen today is a somewhat ticklish sounding proposition.

It sucks, and I'd like to lift that restriction some day, if that and the other problems (some not) can be solved.

Please feel free to write into postgres@heroku.com if you want to discuss your problem in more detail, although I'm most unsure if we have the technology to make this a super-easy proposition right now.  Still, the main way we can make it better is by studying it, if you'd be so kind to share.


Secondary nodes in mysql 8.0.15 innodb cluster are not reading group_replication_member_expel_timeout variable from my.cnf I have setup a 3 node mysql 8.0.15 innodb cluster. I have been trying to set group_replication_member_expel_timeout variable to 60 via the /etc/my.cnf file. Although the primary node has read the variable properly, secondary nodes are not picking up this variable and are still using the default value 0.

Here are the contents of the /etc/my.cnf file

[mysqld]
default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
group_replication_member_expel_timeout=60
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
collation-server = utf8_unicode_ci
character-set-server = utf8

After restart, here are the variables in the mysqld instance.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.15    |
+-----------+

mysql> select @@group_replication_member_expel_timeout;
+------------------------------------------+
| @@group_replication_member_expel_timeout |
+------------------------------------------+
|                                        0 |
+------------------------------------------+

I've tried replacing group_replication_member_expel_timeout with group-replication-member-expel-timeout in the /etc/my.cnf, with no success.
I've also noticed that all other variables are being read properly, it is only a problem with group_replication_member_expel_timeout variable.

When using the MySQL Shell and the AdminAPI to set up and manage MySQL InnoDB clusters there's no need, and it's not recommended, to manually change MySQL Server system variables and other configurations. The AdminAPI provides the functionalities to do any required configuration changes.

Regarding your need to change the value of group_replication_member_expel_timeout, what you need to do is as simple as:

cluster.setOption("expelTimeout", 60)

This command ensures the option is set on all the cluster members.

To verify the options being used by your cluster, you can use the following function:

cluster.options()

For more information please consult the InnoDB cluster userguide:

https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-working-with-cluster.html#mysql-innodb-cluster-setting-options

And for some examples and further details you can check the blog posts series published during the 8.0.14 release:

https://mysqlserverteam.com/mysql-innodb-cluster-whats-new-in-the-8-0-14-ga-release/

The series includes posts explaining the usage of the 'expelTimeout' option and also how to change cluster options 'live'.

Cheers,

Miguel


Zero conversion in sql Char convertion in Oracle have a pattern: to_char(number,'pattern') and provide option "s" to take sign to result, for ex: select to_char(10,'S999') from dual; will return +10, but when i check select to_char(0,'S999') from dual; it return +0
Anyone could explain why :-)

It's happens because s specifier makes presence of sign mandatory and places it on specified position.  Even you can put sign as last symbol in string, look at number format documentation or just try:

select
  to_char(10,'S999')  "10 S999",
  to_char(-10,'S999') "-10 S999",
  to_char(0,'S999')   "0 S999",
  to_char(10,'999S')  "10 999S",
  to_char(-10,'999S') "-10 999S",
  to_char(0,'999S')   "0 999S"
from dual

SQLFiddle

According to documentation:

  Negative return values automatically contain a leading negative sign
  and positive values automatically contain a leading space unless the
  format model contains the MI, S, or PR format element.

you always get a "minus" char for negative values at start of a string, so there are no need for S format specifier at most of the cases.
But if you need for some purpose to always place a sign before or after digits, then you can use S format specifier.

If you ask "why +0 and not -0" - it's just "by design" :)

Update
And some words about "design":

A long time ago  (far far away ... :) ) Oracle decide to use special values of data types to indicate null values. For character data it's empty string (that's also source of two string types - varchar and varchar2) and for numeric types it's -0 value.

If you look at specification of computer number format you can find, that for a signed number one bit always reserved to store sign. Zero value of this bit treated as '+' and value 1 as -.
Two representations of zero -0 and +0 are same from the point of view of math and one of them can be eliminated without loosing any functionality. So, Oracle designers decide to always store zero value as +0 and treat -0 as null.
May be behavior changed in modern versions of Oracle, but for compatibility reasons zero value normalization remains unchanged till today and you always got +0 in string representation.


Retrieve title from rows with a maximum count in other columns Given the following table archived:

CREATE TABLE archived (
   sid       serial -- row id
   showurl   text   -- can be: btg, tmwwt, gcaf, emda
   title     text
   reference text   -- title id number
);

Sample Input:

sid | showurl | title       | reference
----+---------+-------------+-----------
1   | btg     | 01-Preface  | 01
2   | btg     | 01-Preface  | 01
3   | emda    | 26-Friction | 26
4   | tmwwt   | 13-Helper   | 13
5   | btg     | 05-Finale   | 05
6   | gcaf    | 07-Help     | 07

SQLfiddle to work with.

Desired Output:

1st selection:  showurl: 'btg'

2nd selection: reference: '01'

3rd and final selection: title: '01-Preface'

I am trying to determine from table archived which showurl has the greatest number of rows.

Then, for that 'showurl', I need to identify which reference has the greatest number of rows. Obviously, that 'reference' will be duplicated, but the 'sid' associated with each duplicated 'reference' will be unique.
Once that is determined, I wish to select/display its title.

My initial attempt (using php, postgresql 9.2: queried each showurl, then using max to identify the showurl with the greatest number of hits.):

$query = "SELECT * FROM archived WHERE showurl = 'btg' order by sid ASC";
$query = "SELECT * FROM archived WHERE showurl = 'tmwwt' order by sid ASC";
$query = "SELECT * FROM archived WHERE showurl = 'gcaf' order by sid ASC";
$query = "SELECT * FROM archived WHERE showurl = 'emda' order by sid ASC";

$max = max($numrows1,$numrows2,$numrows3,$numrows4);

Using the sample data above, $max would be 3, but showurl, title, reference, and sid are unavailable for further processing.

I have tried using pg_numrows() but I cannot find a way to refer back either to 'title' or 'reference' for the max rows.

I gather some form of UNION would be in order, but have no idea how to implement this.

Depending on your exact requirements, something along these lines should work:
Subqueries to determine most common attributes
SELECT DISTINCT title
FROM   archived
WHERE  showurl = (
   SELECT showurl
   FROM   archived
   GROUP  BY 1
   ORDER  BY count(*) DESC, 1
   LIMIT  1
   )
AND    reference = (
   SELECT reference
   FROM   archived
   GROUP  BY 1
   ORDER  BY count(*) DESC, 1
   LIMIT  1
   );

I added DISTINCT to the outer SELECT to fold duplicate titles. It's undefined what you need exactly.
Shorter with window functions
A more modern / sophisticated / elegant approach would be with two window functions in the ORDER BY clause, allowing for a very terse query:
SELECT title
FROM   archived
ORDER  BY count(*) OVER (PARTITION BY showurl)   DESC
         ,count(*) OVER (PARTITION BY reference) DESC
LIMIT 1;

Not necessarily faster than the first solution, though.
Again, guessing what you might want exactly, I only retrieve a single winning title.
The trick here is to partition the rows by showurl and reference, run a count over each partition and order descending by that count. This way, you can determine a winner in a single query level.
This will not work, if there is no row with the most common showurl and the most common reference at the same time. Again, exact requirements would be needed ...
-> SQLfiddle demo.


Sort buffers and tables on disk, MySQL/InnoDB I have the following type of query frequently running:

# Time: 120322 13:03:29
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 17.039286  Lock_time: 0.000141 Rows_sent: 24  Rows_examined: 1471904
SET timestamp=1332417809;
select cast(cast(someplaceen0_.datePlaced as date) as datetime) as col_0_0_,     count(distinct someplaceen0_.user) as col_1_0_, lower(someplaceen0_.deviceId) as     col_2_0_, someplaceen0_.country as col_3_0_ from SomePlaceEntry someplaceen0_ where someplaceen0_.datePlaced between '2011-12-17 00:00:00.0' and '2012-03-16 00:00:00.0' group by col_0_0_ , lower(someplaceen0_.deviceId) , someplaceen0_.country;

This particular database is pretty much doing that query after a bunch of inserts at given intervals, and Im now trying tweak my.cnf accordingly.

Engine: InnoDB

RAM: 12G, single purpose.

No TEXT or BLOB, still temporary tables on disk. My understanding is that this should never have to happen, given that max_heap_table_size and tmp_table_size are high enough. Is that correct?
Perhaps joins and sort buffers shall be increased, how can I see how filled they have ever been?

mysqltuner output:

<snip>
[!!] Sorts requiring temporary tables: 175% (49 temp sorts / 28 sorts)
[OK] Temporary tables created on disk: 0% (132 on disk / 20K total)

Variables to adjust:
    query_cache_limit (> 4M, or use smaller result sets)
    sort_buffer_size (> 4M)
    read_rnd_buffer_size (> 1M)
    table_cache (> 128)

Relevant my.cnf config:

sort_buffer_size = 4M
read_rnd_buffer_size = 1M
max_heap_table_size = 128M
tmp_table_size = 128M

What's your general recommendation in terms of config here?

If you are still seeing on-disk temporary tables with your max_heap_table_size and tmp_table_size set that high, then chances are you have a handful of queries with GROUP BY/ORDER BY (or a complex join) that are not using indexes effectively and are generating an enormous amount of intermediate data or you have TEXT/BLOB columns somewhere.  Since you said you don't have the latter, I'm guessing it's the former.

You have a few options

Find the queries causing the problem.  You can use pt-query-digest to help summarize the slow query log and make it easier to track down which queries are going to disk.
Ignore it.  This represents less than 1% of your queries.  Unless it's causing you an actual problem, don't worry about it.
If you can't ignore it, keep bumping up max_heap_table_size and tmp_table_size until the problem goes away.  I wouldn't recommend this.

Keep in mind that MySQL pushes large temporary tables to disk for good reason.  Eventually, you risk pushing other data out of memory (or worse, running the server out of memory) in order to process a handful of queries with very large data sets.  Those settings are per table, so if you have any level of concurrency, you could get into trouble very quickly.  I run in production with tmp_table_size/max_heap_table_size at 768MB, but I have 140GB of RAM to work with.  Your headroom is much smaller with only 12GB.


SQL Server restore replicated database I have setup a transactional replication on a sql 2008 database.
Then I backup the database.I got following error when I try to restore the database.
'Exclusive access could not be obtained because the database is in use'

Do I have to remove the replication before restore database?

If it's a full backup, and there's no real-time application being in use in the database. I would suggest stopping and deleting the database for a complete clean up. After that the restore can be done and the replication could be setup automatically from the restore.


How to configure PG text search dictionary through sql I want to add terms to a text search dictionary without access to the file system shares/tsearch_data (specifically for google cloud SQL).

Is there a syntax to do this via SQL?

All I have found is "ALTER TEXT SEARCH DICTIONARY.." which can configure how a dictionary works, but cannot be used to add or remove lines in the dictionary .rules/.syn/.dict file.

By design (it's hardcoded) tsearch_data directory is in SHAREDIR. If you want to change SHAREDIR you should use your own compiled version of PostgreSQL. If you are interested, you can read the documentations' Installation from Source Code section and If you still want to change tsearch_data, well PostgreSQL is opensource, you can change Makefile and ts_utils.c and again have your own version of PostgreSQL by compiling it.

You can also use symbolic link.


Db2 timestampformat for load statement I've got a file with timestamps like 20130807183000000. I'm trying to load using timestampformat="YYYYMMDDHHMMSSUUU" and I'm getting the error

  SQL0181N  The string representation of a datetime value is out of range.

I'm pretty sure the issue is that db2 is interpreting the minutes as months. I've been unable to find any documentation on how to explicitly format it as a minute for the load statement. I tried using 'MI' since that works for the timestamp_format function, but no luck. Does anyone know of any hidden documentation or have any suggestions that don't involve editing the file? Thanks.

That should work. I created a file like:

[lelle@dustbite src]$ cat apa.csv
20130807183000000

and a table like:

[lelle@dustbite src]$ db2 "create table tt (ts timestamp not null)"

and loaded like:

[lelle@dustbite src]$ db2 "load from apa.csv of del modified by timestampformat=\"YYYYMMDDHHMMSSUUU\" insert into tt"
SQL3501W  The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file
"/home/lelle/src/apa.csv".

SQL3500W  The utility is beginning the "LOAD" phase at time "07/29/2016
20:35:42.233607".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "1" rows were read from the
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "1".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "07/29/2016
20:35:42.482947".

Number of rows read         = 1
Number of rows skipped      = 0
Number of rows loaded       = 1
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 1

Could it be the reason that you did not quote the " so that the shell got rid of those?

If I do:

[lelle@dustbite src]$ db2 "load from apa.csv of del modified by timestampformat="YYYYMMDDHHMMSSUUU" insert into tt"

I get the error:

SQL3192N  In the filetmod a user specified format "TIMESTAMPFORMAT" beginning with the string "YYYYMMDDHHMMSSUUU" is not valid.

If that is not the problem, can you provide a small sample of rows that fail during load?


Load Numerical Column with Variable Comma I'm using sqlldr to load a file which has a numerical value as one of its fields.

The problem is that in some of the records the number has commas whereas in other, it doesn't.

So doing something like

num "to_number(:num, '999,999,999.99')",

loads the records with commas but not those without commas and doing

num "to_number(:num)",

loads only those without commas.

Is there a way to tell Oracle (sqlldr) that the comma is optional?

Here you go:

SQL> desc loadertst;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> !cat loadertst.ctl
load data
infile *
into table loadertst
fields terminated by ',' enclosed by '"'
(
col1 "to_number(replace(:col1,',',''))"
)
begindata
"123456"
"1,2,3,4,5,6"

SQL> !sqlldr phil/phil control=loadertst.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Thu Nov 29 23:33:17 2012

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

Commit point reached - logical record count 2

SQL> select count(*) from loadertst;

  COUNT(*)
----------
         2

SQL>

Obviously you may need to fiddle with the fields terminated by ',' enclosed by '"' part to suit your data.


Sometimes getting quotes in Oracle spool sometimes not Using Oracle SQL Developer, I do:

spool c:\temp\file1.csv
select /*csv*/ id, version, key, pattern, name from mydatabase.edition;

spool c:\temp\file2.csv
select /* csv */ id, version, amount, item FROM mydatabase.mtci;

In the first statment the export is like this

"string1", 98, "key1", "pattern", "name"

In the next statement it is like thhis

string2,       104,         988.99,         yuikk

i.e. it has no quotes and has tabs after the commas.

Anyone know why this is the case?

Note: I am using Oracle 11.

The problem was in on case I was doing

/*csv*/ -- this works

/* csv */ -- this does not work.

Thanks


Error when installing JServer JVM in Oracle XE on Linux AMI I'm trying to install OJVM on Oracle 11g (11.2.0.2.0) on Amazon Linux AMI (20016.09).  And I'm getting the following error after running initjvm.sql:

PL/SQL procedure successfully completed.
create or replace java system
begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then
*
ERROR at line 1:
ORA-29538: Java not installed
ORA-06512: at "SYS.INITJVMAUX", line 23
ORA-06512: at line 5

Running
SQL> select comp_name, version, status from dba_registry;

shows this:
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle Application Express
4.0.2.00.09                    VALID

Oracle XML Database
11.2.0.2.0                     VALID

Oracle Text
11.2.0.2.0                     VALID

COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle Database Catalog Views
11.2.0.2.0                     VALID

Oracle Database Packages and Types
11.2.0.2.0                     VALID

JServer JAVA Virtual Machine
                               LOADING

COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle XDK
11.2.0.2.0                     VALID

The install is started and up until that point shows no errors, and everything seems to run and complete successfully, and the dba_registry shows an in progress install.  Anyone have any ideas on what the issue could be?

A basic overview of feature availability for different database editions:

Products and Services - Database - Enterprise Edition - Comparisons

Java is not available in Express Edition.


why has Lock_time in slow query log when execute select sql? Why mysql has Lock_time in slow query log when execute select sql?

  Time: 2018-11-22T15:06:32.654503Z
   User@Host: root[root] @ localhost
   []  Id:     3
   Query_time: 0.058138  Lock_time: 0.000287 Rows_sent: 0  Rows_examined: 3115 SET timestamp=1542899192;
  select * from COLUMNS where COLUMN_TYPE ='int';

From the looks of the query, my guess is that you are looking up all the defined columns in INFORMATION_SCHEMA.COLUMNS that are defined as INT.

The reason why there was a lock on INFORMATION_SCHEMA.COLUMNS ???

The table INFORMATION_SCHEMA.COLUMNS is defined as a temporary memory table. Memory tables undergo full tables locks when being accessed for change (such as when one does ALTER TABLE or CREATE TABLE) from anywhere in the database,

Here is an excerpt from my old post How is INFORMATION_SCHEMA implemented in MySQL?

  All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.

Please note what is in bold

  It is a temporary table in memory. It is manipulated using storage engine protocols.

When you look up the MEMORY storage engine in the Docs, it shows that Lock Granularity is Table. So, every table in the INFORMATION_SCHEMA, including INFORMATION_SCHEMA.COLUMNS will received a lock at some point, EVEN FOR A SELECT (just like a MyISAM table).


User Password Expiry date is null in user_users? Is it possible to find my password expiry date without asking the DBA?

My user does not have privileges to view the DBA_USERS table. Also, I tried select * from USER_USERS table, but expiry_date is empty.

It might be that your account has been assigned the profile with PASSWORD_LIFE_TIME set to UNLIMITED, and thus your password will never expire. You can query the USER_PASSWORD_LIMITS view to determine what password limits are currently in effect for your account:

SQL> select * from user_password_limits;

RESOURCE_NAME                    LIMIT
-------------------------------- ---------
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7


Delete from table rows where any of the column field is null Is there a way to delete a row from a  table where any of the column field is null without specifying explicitly which column is null?

I am using postgreSQL.

Here's my relation schema:

  Column    |  Type   |                              Modifiers
  --------------+---------+----------------------------------------------------------------------
  id           | integer | not null default  nextval('aurostat.visitor_center_id_seq'::regclass)
  date         | date    |
  persons      | integer |
  two_wheelers | integer |
  cars         | integer |
  vans         | integer |
  buses        | integer |
  autos        | integer |

Thanks

I see two ways of doing that:

With plain standard SQL, simply list all columns and combine that with an OR:

delete from the_table
where date is null
   or persons is null
   or two_wheelers is null
   or cars is null
   or vans is null
   or buses is null
   or autos is null;

Another (Postgres specific) solution is the comparison of the whole row with NOT NULL

select *
from the_table
where the_table is not null;

will return only rows where all columns are not null. You want the opposite, so you need to negate that where not (the_table is not null) The condition where the_table is null is something different - that only matches rows where all columns are null.

delete from the_table
where not (the_table is not null);


User is not showing for abc database under security folder but when we query from sys.databases it showing as a db owner Below Steps I Did

EXEC sp_changedbowner 'domainname\user'

... to change the principal owner.

It was executing for a long time so i cancelled it. While closing the tab it asked me to save the uncommitted transactions, so I saved them.

Later when I checked under the database security folder the old user name was not showing any more.

But when I ran the query

select database_id, name, suser_sname(owner_sid) from sys.databases
where suser_sname(owner_sid) = 'domain\olduser'

...it returns one row which shows that the old user is still the db owner.

If I check the database properties from SSMS it shows the old user as the owner as well. But under the database users I am not able to see the old user any more.

Please suggest how I can change the db_owner...

The database owner displayed in the files section of the database properties is not displayed in the Security branch of the database. This is by design.

Explanation

Database Owner

The database owner in the file setting of the database properties is the owner of the database and does not have to be explicitly listed in the security tab. This is normally the SQL Login that created the database.

If a Windows Account (e.g. DOMAIN\ACCOUNT) was logged in and using SSMS to create the database, then this will be the database owner.
If sa or another SQL Login was logged in and using SSMS to create the database, then this will be the database owner.

db_owner Role

The db_owner role can be assigned to individual database users. These can be Windows Accounts or SQL Server Logins.

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and SQL Data Warehouse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)

Reference: Database-Level Roles (Microsoft Docs)

These are similar to the database owner in that they have the same permissions, but are explicitly defined in the database. That is why they are displayed in the security branch of the database.

Solution

To change the database owner ...

Assign new database owner

To remove a Database Owner (not the db_owner role) you have to assign a new database owner. This can be achieved using the mentioned sp_changedbowner stored procedure (deprecated) or the ALTER AUTHORIZATION... syntax.

Reference: ALTER AUTHORIZATION (Transact-SQL) (Microsoft Docs)

ALTER AUTHORIZATION ON DATABASE::<DB_name> TO <new_owner>;

a. replace <DB_name> with the name of the database
b. replace <new_owner> with a Windows Account (e.g. DOMAIN\ACCOUNT) or a SQL Login (e.g. sa).
sp_chnagedbowner '<new_owner>'
b. replace <new_owner> with a Windows Account (e.g. DOMAIN\ACCOUNT) or a SQL Login (sa).

Remove old SQL Login

Now that the database has a new database owner, you can drop the SQL Login.

DROP LOGIN <old_account>

That's it. You're finished.


How to search records based on date range for other matching data I have below records in table

APP_No Stage Start_Date End_Date
A123   L1    01-01-19 02-01-19
A123   D1    01-03-19 05-03-19
A345   L1    01-01-19 02-01-19
A345   D1    01-03-19 05-03-19

I want to search records for stage D1 for the month of Mar19 but this should include data for L1 stage also, since related app ID fall between same Mar19 date range.
I have used condition in where clause as : trunc(Col_Name) BETWEEN trunc (sysdate, 'mm')/current month/ AND SYSDATE

Please help with solution

Try ans use EXISTS to get all records where another (or the same for that matter) exists with the same app ID, a stage of 'D1' and the start date or the end date in march.

Assuming you're on Oralce, this could look like this:

SELECT *
       FROM elbat t1
       WHERE EXISTS (SELECT *
                            FROM elbat t2
                            WHERE t2.app_no = t1.app_no
                                  AND stage = 'D1'
                                  AND (t2.start_date >= to_date('2019-03-01', 'yyyy-mm-dd')
                                       AND t2.start_date < to_date('2019-04-01', 'yyyy-mm-dd')
                                        OR t2.end_date >= to_date('2019-03-01', 'yyyy-mm-dd')
                                           AND t2.end_date < to_date('2019-04-01', 'yyyy-mm-dd')));


where clause after limiting results I have a question about getting results after limiting the rows.

I don't have much experience with MySQL, but I'm trying to get results from a database where I need to do the where clause after limiting the results.

SELECT
  COUNT(measurement) as 'measurement errors'
FROM measurelog
WHERE (measurement < (baseline - tolerance))
ORDER BY Id DESC
LIMIT 100

I only need the measurement values of the last 100 rows, so I expect to get about 5~10 results, but now the programm counts untill it has found 100 faulty measurements.

I've tried to switch the where clause to the bottom, but then I get an syntax error from the MySQL client.

Is there something I'm missing?
Is there another way to do this?

Thank you Akina for the tip, got it working using the following query

SELECT
      COUNT(measurement) as 'Measurement Errors'
     FROM (
        SELECT *
        FROM measurelog
        ORDER BY Id DESC
        LIMIT 100
        ) sub
    WHERE (measurement < (baseline - tolerance))

which now returned 7 results


MariaDB | Sync Master with Slave in another Datacenter We have a web application hosted on a dedicated server at Datacenter A. I am in the process of setting up a disaster recovery protocol and am considering the following.

My current plan is to purchase another dedicated server (or vps) at Datacenter B. This server will host a slave copy of the database located at Datacenter A. This will be my first time configuring replication and am curious about the following:

Will there be a performance drop for the server hosted in Datacenter A due to network latency during the replication with server in Datacenter B?
Database on server in Datacenter A is currently only accessible to applications running on the server (or via ssh tunnel). In order to configure replication with server hosted in Datacenter B, will I need to open port 3306 or can the replication take place via ssh?

The Master (in A) does very little work to provide the replication stream to the Slave (in B); don't worry about it.

There will, however, be a slight delay between when the data is available on the Master and when it is usable on the Slave.  But, since you are talking only about Disaster Recovery, I don't need to lecture on "Critical Read".

I think that the replication is opened by the Slave, so the port being opened needs to be on A.  Normally this is the same 3306 used for other clients.  (But your tunneling may lead to something else.)


Non-Unique Clustered Indexes Administration I have been recently assigned to administer a database in SQL Server 2016 and I have discovered that there are many tables in the database with Non-Unique Clustered indexes resulting in Non-Clustered Primary Key indexes.

I know that the above is apparently allowed but is not ideal (specially for large tables) and maybe for some few cases, that would make sense, but the number of such tables tells me that this was more like a result of careless table definitions.

For example, a table named Transaction will have a Clustered Index with the following keys: date, is_deleted. Also, the table will have a Non-Clustered Primary key on a column named id.

Now, some of these tables are pretty large and have many foreign key references on them, so I came up with the below steps to change all these tables with the help of Aaron's answer in this thread Unable to drop non-PK index because it is referenced in a foreign key constraint:

Drop the Clustered Index of the table
Drop all foreign key constrains that reference the table
Drop current Primary Key constraint of the table
Create Primary Key Clustered constraint using the id column
Create all foreign key constraints that were previously deleted
Optionally - Create the previously Clustered index as a Non-Clustered index

I know that for the large tables I need to find a maintenance window to perform the change but do you find any problems or gotchas with the above solution what I didn't think of? Will this method cause fragmentation on the rest of Non-Clustered Indexes?

There are a lot of tables involved and I want to make sure that I'm not triggering any side effects.

This one is hard to answer without knowing your query patterns, but you will most definitely cause side effects when dropping all clustered indexes and replacing them with something else.

I also don't think you are necessarily correct in assuming that having a non-unique clustered index is sloppy table design or by definition sub-optimal.

When you add a non-unique clustered index, SQL Server will add a 4-byte uniqueifier  to the index to make sure the key is unique if there are duplicate values in the key. Calculating this uniqueifier adds a bit of overhead but nothing too dramatic. This uniqueifier also has to be in the non-clustered index since the index bookmark has to point to a single row.

On the other hand, the example you give could be a good clustered index depending on the situation:

  For example, a table named Transaction will have a Clustered Index
  with the following keys: date, is_deleted. Also, the table will have a
  Non-Clustered Primary key on id column.

This could be a very good design decision if there are a lot of transactions on the same date, and almost every query is a  SELECT * FROM transactiontable WHERE date = <somedate> AND is_deleted=0.

A query like that could hurt badly if it has to use a non-clustered index and resort to key lookups.

It's impossible to say what side effects you will trigger, but you'll surely trigger some, and your reasons for this endeavour might be the wrong ones.

If you insist on doing this, make sure you test it first in a testing environment, and if you do you should be able to tell if this method works and if it will cause fragmentation or not (but that will probably be the least of your worries).


How are joins Commutative and Associative? I have read all over the place that joins are associative and commutative.

So A join (B join C) should be the same as (A join C) join B.

But I have a really hard time understanding how this can be so. Assume that A has a property in common with B and B has a property in common with C, but A and C share no common properties to join on.

It seems that in that case, in the second instance (A join C) join B, when the operation took place to join A and C it would result in an empty set, making the join with B impossible.

Whereas in the first case it would work because B join C would result in the expected joined table that would have a property to join with A.

You've made an incorrect assumption.

If three's a relationship between A and B, and between B and C but no relationship between A and C, joining A and C doesn't give you no rows. It actually gives you m * n rows, where m is the number of rows in A, and n is the number of rows in B.

A JOIN B on (a.id = b.A_id)
  JOIN C on (b.id = c.B_id)

becomes, if we join A and C first:

A JOIN C on (1 = 1)
  JOIN B on (a.id = b.A_id AND b.id = c.B_id)

NOTE: College was over 30 years ago - hopefully you understand conceptually, and can rewrite into the correct syntax for actual relational algebra.

If you have 20 rows in A, 30 in B, and 40 in C, and every row in B matches one and only one row in A, and every row in C matches one and only one row in B. When you Join A to B, you'll get 30 rows back; when you join those 30 rows to C, you'll get 40 rows back.

If you first join A to C, with no relationship to define which pairs of rows are valid, you'll get back every possible pair of rows - 800 rows. When we join B to the 800 rows, the connection between B and A means that a row in B can only match the AC rows that include one specific A row - there'll be 40 of those (one for each C row). Out of those 40 the only actual matches are the ones where the connection between B and C is also valid. And, we know that the C rows that match the current B row cannot match any other B row. So, for one B row we might have 2 AC rows that match, for another 4, for yet another just 1.

That said, we know every C row does match one (and only one) B row, so the total number of matches will come out to 40 again - the same 40 matches as if we had first matches A and B, or B and C.

So - the condition on a join doesn't tell you which rows do match - it really tells you which rows don't match. Take it away, and you've got a CROSS JOIN - the cross product of the rows in the two tables.


Query stuck in mattermost postgresql communication Ubuntu 18.04
PostgreSQL 10.10
Mattermost 5.15

On mattermost startup process stay forever on

  {"level":"info","ts":1570609055.2422519,"caller":"sqlstore/supplier.go:223","msg":"Pinging
  SQL master database"}

Running this query

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Gives me

 pid  |      age       | usename |         query         | state
------+----------------+---------+-------------------------------
 8700 | 00:08:54.84669 | mmuser  | SELECT * FROM Systems | idle

From there, how do I diagnose what makes this query never end ?

The query has already ended 9 minutes ago. PostgreSQL is sitting idle and waiting for the next statement on this connection, which isn't coming.

The block must be somewhere else in your startup process.


Rename the database name through terminal My database name is studenttRecord. I would like to correct the spelling to studentRecord. How can I change it if possible?

Firstly, create the new database with the command;

mysql> CREATE DATABASE studentRecord;

Then transfer the contents from the old database into the new database;

shell$ mysqldump --opt studenttRecord | mysql studentRecord

(add in the username and password bits as appropriate)

The --opt part will add a number of standard flags to the data dump, and I refer you to the MySQl documentation for more information.

Once the transfer is complete, and you have verified it, you can drop your old database.

If your database is particularly huge, this can take some time to perform, so an alternative is to RENAME TABLE for each of the tables in the old database, and move them into the new database.

Create the database as above, then for each table do the following;

mysql> RENAME TABLE studenttRecord.table_name TO studentRecord.table_name;

Replacing table_name with each of your tables in turn (create a script of this in a text editor to help prepare a complete list!).

Hope that helps,
Dave


How to break SQL script execution I am working on sql script and I am having a requirement of stop continuing the script if some conditions are not satisfied.

When I Google it, I found the RaisError with 20 severity level will terminate it.
But for some reasons I cannot use that option.

Can please provide me what are the possible alternatives to stop SQL script execution.

From the RAISERROR documentation (emphasis mine):

  Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.

It's highly likely the principal you're executing the script as does not meet these criteria.

There's nothing wrong with using RAISERROR; you're just using a severity level that's excessive. I use level 16 as a default for an error that is raised and the sequence will be terminated. If you want to be more accurate, you can follow the levels given by Microsoft itself:

Now, having said all that, depending on the context of the script, using RAISERROR may not be enough, as it doesn't "exit" the script by itself (using normal severity levels).

For example:

RAISERROR(N'Test', 16, 1);

SELECT 1;   /* Executed! */

This will both raise an error and return a result set.

To terminate the script immediately, I prefer to use RETURN (using GOTO-type constructs are generally discouraged in most programming circles where alternatives exist):

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */

Or handle the error using TRY/CATCH, which will cause execution to jump to the CATCH block if the severity is 11 or higher:

BEGIN TRY
    RAISERROR(N'Test', 16, 1);
    SELECT 1;   /* Not executed */
END TRY
BEGIN CATCH
    SELECT 2;   /* Executed */
END CATCH

BEGIN TRY
    RAISERROR(N'Test', 10, 1);
    SELECT 1;   /* Executed */
END TRY
BEGIN CATCH
    SELECT 2;   /* Not executed */
END CATCH

A separate problem is if the script spans multiple batches -- RETURN will only exit the batch:

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

SELECT 2;   /* Executed! */

To fix this, you can check @@ERROR at the start of every batch:

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

IF (@@ERROR != 0)
    RETURN;

SELECT 2;   /* Not executed */

Edit: As Martin Smith correctly points out in the comments, this only works for 2 batches. To extend to 3 or more batches, you can cascade raising errors like so (note: the GOTO method does not solve this problem as the target label must be defined within the batch):

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

IF (@@ERROR != 0)
BEGIN
    RAISERROR(N'Error already raised. See previous errors.', 16, 1);
    RETURN;
END

SELECT 2;   /* Not executed */
GO

IF (@@ERROR != 0)
BEGIN
    RAISERROR(N'Error already raised. See previous errors.', 16, 1);
    RETURN;
END

SELECT 3;   /* Not executed */

Or, as he also points out, you can use the SQLCMD method if that's appropriate for your environment.


How I compact WAL files? I like compact WAL files. Actually I do:

archive_command = 'cp "%p"C:\\%f"'

What is the best way to do it?

Going by your other questions, it sounds like you have a short archive_timeout so your WAL archives are mostly empty, but are still the full pre-allocated 16MB file.

On most platforms you just gzip them, e.g.

archive_command = 'gzip -c < "%p" > /archive/path/%f"'

On Windows this won't work natively due to the lack of the gzip command. You'll need to install a compression utility that's usable from the command line, since the Windows zip support isn't directly usable from the commandline.

I suggest installing 7-zip, then using it in your archive_command, with something like (untested):

archive_command = '%PROGRAMFILES%\7zip\7z.exe a "C:\\TheWALArchive\%f" "%p"'

Note that your restore_command will need a corresponding decompression step.


PostgreSQL recovery file syntax, ver 8 vs ver 9 In postgreSQL version 8.4 it seems that you can place the db server into warm standby mode with the following setting in recovery.conf

restore_command = 'pg_standby archiveDir %f %p %r'

In postgreSQL version 9.5 I see the same option, but I also see what appears to be an alternative syntax in recovery.conf:

standby_mode = (boolean)
trigger_file = (string)

So is this new syntax an alternative to the old syntax (both are accepted by PGSQL)?  Any benefit to the new syntax?

Does the new syntax mean that I can use the restore_command setting as well (to use my own copy command)?

One benefit of the new syntax is that your restore_command doesn't need to implement its own polling for newly arrived log files, and doesn't have to check for failover triggering, because the core code now does that for you.  That means your restore_command only needs to focus on finding and copying the file, which makes it much simpler and easier to get right.  For example, if you need to fetch your archive from AWS S3 storage, for example, you don't want to also need to implement your own polling and failover.

Another benefit is that it integrates cleanly with streaming replication.  So it can use the WAL archive to get caught up, but then switch to streaming so that it stays caught up in near-real-time.  And then goes back to using the WAL archive automatically if it ever falls too far behind.


Mysql query grouping by two columns for max date takes too long I have table that looks like this:

l_p  l_a     l_timestamp          l_n   l_act   l_name
123  321     2011-1-1T01:00:00Z   B     q       das
21   23      2012-1-1T01:00:00Z   C     q       sd
123  23      2013-1-1T01:00:00Z   D     q       sad
21   321     2008-1-1T01:00:00Z   D     q       sad
21   23      2007-1-1T01:00:00Z   E     q       sad

The timestamp field is a binary. All other fields are varbinary. I am not interested in the l_name field but it is relevant for the indexes.

What I want to get back is for each unique combination of l_p and l_a the row with the max timestamp. I also have some additional conditions: the timestamp should be smaller than 2013-1-1 and the l_act field should be q. The table I want to get would look something like this:

l_p  l_a   l_timestamp          l_n   l_act
123  321   2011-1-1T01:00:00Z   B     q
21   23    2012-1-1T01:00:00Z   C     q
21   321   2008-1-1T01:00:00Z   D     q

The table contains about 50million records. My current query is extremely slow. It takes hours and still I haven't gotten a result yet. This is the query:

SELECT a.l_p,a.l_a,a.l_timestamp,
case a.l_new
when 'B' then 2
when 'C' then 3
...
end as GN
FROM tmp a
INNER JOIN
(SELECT l_a,l_p,MAX(l_timestamp) as max_date
FROM tmp
WHERE l_act = 'q' AND (l_n != 'I' and l_n != 'Z')
AND l_timestamp < cast('2013-01-01T00:00:00Z' as binary)
GROUP BY l_p,l_a) b
on (a.l_p = b.l_p AND a.l_a = b.l_a AND
a.l_timestamp = b.max_date AND
a.l_act = 'q' AND  (l_n != 'I' and l_n != 'Z')

I've also tried this variation with the same results:

SELECT a.l_p,a.l_a,a.l_timestamp,
case a.l_new
when 'B' then 2
when 'C' then 3
...
end as GN
FROM tmp a
(SELECT l_a,l_p,MAX(l_timestamp) as max_date
FROM tmp
WHERE l_act = 'q' AND (l_n != 'I' and l_n != 'Z')
AND l_timestamp < cast('2013-01-01T00:00:00Z' as binary)
GROUP BY l_p,l_a) b
WHERE (a.l_p = b.l_p AND a.l_a = b.l_a AND
a.l_timestamp = b.max_date AND
a.l_act = 'q' AND  (l_n != 'I' and l_n != 'Z'))

The indexes on the table are:

Keyname     Type         Unique    Packed
PRIMARY     BTREE        Yes       No
Field       Cardinality            Collation
l_project   18           A
l_name      18           A
l_a         24799255     A
l_act       49598510     A
l_timestamp 49598510     A

AND
Keyname     Type          Unique Packed
l_a         BTREE         No     No
Field       Cardinality          Collation
l_a         16532836      A
l_name      16532836      A

Any ideas on why it takes so long? The table is innodb.

EDIT 1: The query below is quite like the original query. It very much depends on how strict are the other conditions, i.e. how many rows can be ignored with those conditions. I think the index should contain the group-max-triple (l_a, l_b, timestamp), but if l_act='q' is a strong reduction and the condition is always there then l_act should come before l_a.

Origin:
I would write it as

select tmp.*
from tmp inner join (
    select l_a, l_b, max(timestamp) as max_time
    from tmp where timestamp < cast('2013-01-01T00:00:00Z' as binary)
    group by l_a, l_b
) as selmax
on selmax.l_a = tmp.l_a
   and selmax.l_b = tmp.l_b
   and selmax.max_time = tmp.timestamp
;

and give tmp an index on (l_a, l_b, timestamp).

And also: what says explain?

EDIT 2:

Seeing the explain and looking at the indices clears the case. l_project starts the primary key but you give no restriction on that in your inner query.

The second index needs name as a second field.

If l_act is a strong restriction (All of the queries give a l_act - condition which meets less than  1/3 of the table), you should make an index

l_act, l_a, l_b, timestamp

otherwise

l_a, l_b, timestamp

If the 4-part-index is used, it should be very fast.

I wish much fun adding the index. ;-)

If you use the 4-part-index, then you must add the l_act-condition in the inner AND the outer join.


MySQL Equivalent Query or JOIN query The query below works pefectly in SQL Server

update userrole
set userid = 6 where userid = 5
and id not in
(
  select b.id from userrole a inner join userrole b
  on a.roleid = b.roleid and a.region = b.region
  where a.userid = 6 and b.userid = 5
)

When I use the same query in MYSQL, I get the exception 1093 which says Error Code: 1093. You can't specify target table 'userrole' for update in FROM clause.

How to change the query to MySQL equivalent?
How to use JOIN query instead of sub-query in the above query

Thanks in advance.

If you don 't try to understand the meaning of the request and simply adapt it to MySQL then:

UPDATE userrole ur
LEFT JOIN ( SELECT b.id
            FROM userrole a
            INNER JOIN userrole b ON a.roleid = b.roleid
                                 AND a.region = b.region
            WHERE a.userid = 6
              AND b.userid = 5 ) sq ON ur.id = sq.id
SET ur.userid = 6
WHERE ur.userid = 5
WHERE sq.id IS NULL

If you try to understand the task then it looks like (it seems) "alter userid from 5 to 6 if the record with the same (roleid,region) and userid=6 not exists yet". If so the task may be solved by:

UPDATE userrole t1
LEFT JOIN userrole t2 ON (t1.roleid, t1.region) = (t2.roleid, t2.region)
                     AND t2.userid = 6
SET t1.userid = 6
WHERE t1.useris = 5
  AND t2.userid IS NULL


postgres query performance I can't find a good way to rewrite this query or improve the performance:
query:
update company_holding c
set issuer_stim_buy_quarter = sq.estim_quarter_buy
from (select h.company_id
            ,h.issuer_cusip
            ,var_quarter as quarter
            ,min(h.quarter) as estim_quarter_buy
      from company_holding h
      where h.quarter <= var_quarter
        and h.issuer_tot_share > 0
      group by h.company_id, quarter, h.issuer_cusip
     ) sq
where c.company_id = sq.company_id
  and c.issuer_cusip = sq.issuer_cusip
  and c.quarter = sq.quarter

company_holding is a partitioned table.
This is the explain analyze: https://explain.depesz.com/s/5rjU

Most of the time is spent aggregating and sorting the data.
I don't know how the tables are partitioned, but if the partitioning key is part of the join condition or the aggregate can be calculated partition by partition, you might benefit by setting enable_partitionwise_aggregate and enable_partitionwise_join to on.
There is not much more I can say with that little information.


Database design for large volumes of data My application needs to support multiple users. Each user has a large volume (millions) of data entries. The application needs to select entries based on the current logged-in user.

What is the best SQL database design for such a requirement? To put everything into a single table with an additional user field? Or to create a table for each user?

Is NoSQL better in this case?

Table for each user would be wildly inefficient as whenever a user leaves or joins you need to re-arrange your table structure.

having an owner_ID field in the table and then allowing the general users to be identifiable meaning that when someone is logged in the query can run AND owner_id = @active_user_ID

Be worth having a table to link the ID to the logged in user, and then you can also set up managers (Table User_ID, Manager_ID,name,detail...) which means you can allow managers to view all data of their underlings (if this is needed)

Its worth noting doing a structure like this to consider indexing the table in a structure that organises the data according to the owner_id field to improve performance for each user as they will unlikely overlap


Unable to create an user using mysql_embedded I am creating a docker mariadb image and wanted to create a database and user name before bootstrapping mysqld daemon. So as per the documentation I tried below queries.

mysql_embedded -e "CREATE DATABASE IF NOT EXISTS test ;"

mysql_embedded -e "CREATE USER admin@localhost IDENTIFIED BY 'password';"

mysql_embedded -e "select user, host, password, plugin from mysql.user;"

The above query successfully create a database and display user information. But it didn't create the user i.e the middle query didn't get executed. Even it didn't show any error. Can't I create an user using mysql_embedded ?

What is the alternative to mysql_embedded. Is it mysqld --bootstrap ?

Here is a blogpost on mysql_embedded https://mariadb.com/resources/blog/using-mysql_embedded-and-mysqld-bootstrap-to-tinker-with-privilege-tables/

As you are using the docker mariadb image the best way is to set the environment variables when running the image:

MYSQL_DATABASE
MYSQL_USER
MYSQL_PASSWORD

ref: https://hub.docker.com/_/mariadb/

These will case the database and user to be created with grants (ref)


Cannot see Green Button with white arrow in object explorer; DTC config problems? I have SQL Servers across locations setup for replication. One of the SQL Server instances that I installed, running locally I can see the green button with the white arrow in the object explorer when I connect to it. However, when I connect to it from any other location, I cannot see that or neither can I start or Stop the SQL Serer Agent; even though locally I am able to do that.

Additionally, the MDTC doesn't has only 2 items sent both with were rejected, where as other servers have 100's committed and sent. Is there something wrong with the DTC settings?

Please help.

Your best bet is to download DTCPing (http://www.microsoft.com/en-us/download/details.aspx?id=2868) and run it from both machines.  If I recall correctly, the servers initially talk over the RPC port (135) and then negotiate a random, high port (above 1024) to continue the transaction on.  There is a registry entry that will allow you to limit the ports RPC chooses, but it will apply to all RPC calls not just DTC calls.  http://support.microsoft.com/kb/908472


How to monitor mysql percentage disk utilization? I am new to mysql world and not sure if there is a way to calculate mysql TableSpace/DiskSpace utilization in percentage. I am aware mysql does not provide upper limit or free space like Oracle DB, but just space consumed by Index tables and Data Tables. I have decided to add these two values and divide with total space available for mountpoint mentioned in "datadir" attribute of mysql. My queries are :

Is this right way of doing this ??
What if databases/tables are created on different mountpoints. Is there a way to monitor available space for mysql instance then ?

Four years ago, I addressed this type of situation in StackOverflow : How can you determine how much disk space a particular MySQL table is taking up?. My post supplies queries to find out how much logical space a table consumes.

For InnoDB, data and index pages for an individual are lumped together in a single tablespace file (if innodb_file_per_table is enabled) or lumped together with the data and index pages of other InnoDB tables inside ibdata1 (if innodb_file_per_table is disabled). For the sake of optimism, I'll assume you have innodb_file_per_table enabled.

In order to look for percentage usage of a table, you would have to check the physical usage of the table in OS, query the database for the logical usage, and divide the logical by physical and multiply by 100.

Let's assume the following situation

Your datadir is /var/lib/mysql
You have a table called mydb.mytable
You have innodb_file_per_table enabled
You don't know if the table is MyISAM or InnoDB
You don't use other storage engines

Here is what you need for an individual table's space usage

GIVEN_DB=mydb
GIVEN_TB=mytable
DATADIR=/var/lib/mysql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Get Logical Space of the Table
#
SQL="SELECT data_length+index_length FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${GIVEN_DB}' AND table_name='${GIVEN_TB}'"
LS=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
#
# Get Physical Space of Table
# Whether InnoDB or MyISAM
#
cd ${DATADIR}/${GIVEN_DB}
PS=0
for X in `ls -l ${GIVEN_TB}.[Mi][Yb][DId]|awk '{print $5}'` ; do (( PS += X )) ; done
(( DF = PS - LS ))
NUM="${LS}00"
(( PCT = NUM / PS ))
echo "Unused Bytes for ${GIVEN_DB}.${GIVEN_TB} : ${DF}"
echo "Pct(%) for ${GIVEN_DB}.${GIVEN_TB} : ${PCT}"

UPDATE 2015-08-14 13:14 EDT

Your second question

  What if databases/tables are created on different mountpoints. Is there a way to monitor available space for mysql instance then ?

My script can monitor an individual table's space usage regardless of its mount point.

What you are asking for requires two things

List of databases
Script to cycle through each database and get physical and logical sizes

Let's say you have a file called /root/dbmounts that looked like this

db1
db2
db3

Each entry in this file is assumed to be on a different mount point.

You need the script to cycle through them:

DBMOUNT_FILE=/root/dbmounts
DATADIR=/var/lib/mysql
SQL="SELECT SUM(data_length+index_length)"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
for DBMOUNT in `cat ${DBMOUNT_FILE}`
do
    FOLDER=${DATADIR}/${DB}
    cd ${FOLDER}
    AVAILABLE_SPACE=`df -B 1 .|tail -1|awk '{print $3}'`
    PCT_USED=`df -B 1 .|tail -1|awk '{print $4}'`
    PHYSICAL_SPACE=`df -B 1 .|tail -1|awk '{print $2}'`
    LOGICAL_SPACE=`mysql ${MYSQL_CONN} -D${DB} -ANe"${SQL}"`
    FRAGMENTATION_PCT=`echo ${LOGICAL_SPACE}00/${PHYSICAL_SPACE}|bc`
    echo "-------------------------------"
    echo "Database ${DB}"
    echo "Logical : ${LOGICAL_SPACE}"
    echo "Physical : ${PHYSICAL_SPACE}"
    echo "Fragmentation Pct: ${FRAGMENTATION_PCT}"
    echo "Pct Space Used : ${PCT_USED}"
    echo "Available : ${AVAILABLE_SPACE}"
    echo "-------------------------------"
done

If every database is on a different mount point, you do not need the dbmounts file

DBLIST=/tmp/dblist.txt
DATADIR=/var/lib/mysql
SQL="SELECT schema_name FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql',performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLIST}
SQL="SELECT SUM(data_length+index_length)"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema=DATABASE()"
for DBMOUNT in `cat ${DBLIST}`
do
    FOLDER=${DATADIR}/${DB}
    cd ${FOLDER}
    AVAILABLE_SPACE=`df -B 1 .|tail -1|awk '{print $3}'`
    PCT_USED=`df -B 1 .|tail -1|awk '{print $4}'`
    PHYSICAL_SPACE=`df -B 1 .|tail -1|awk '{print $2}'`
    LOGICAL_SPACE=`mysql ${MYSQL_CONN} -D${DB} -ANe"${SQL}"`
    FRAGMENTATION_PCT=`echo ${LOGICAL_SPACE}00/${PHYSICAL_SPACE}|bc`
    echo "-------------------------------"
    echo "Database ${DB}"
    echo "Logical : ${LOGICAL_SPACE}"
    echo "Physical : ${PHYSICAL_SPACE}"
    echo "Fragmentation Pct: ${FRAGMENTATION_PCT}"
    echo "Pct Space Used : ${PCT_USED}"
    echo "Available : ${AVAILABLE_SPACE}"
    echo "-------------------------------"
done


Update mysql DB when data has a single quote with Dynamic values I am writing data to mysql DB from my linux machine using sqlcmd. My script is updating the data Dynamically based on the employee number. I have a situation where the the CN attribute might have single quote and the update fails for that employee record. How can the below command modified to ignore the single quote. I know by using a double single quote can do the job for a single entry and there are many solution for this that has two or three line sql command to achieve the task, please note I am using sqlcmd to update my data and the data is Dynamic, so am looking for a one line command to update the data

Below I have an employee where the CN has value "Testing 'O User", so SQL fails to update. I know I can use a single quote in front of O and that should do the job "Testing ''O User".....But think of Dynamic Values I may not able to go to each record and do that...Need your help please...

sqlcmd -S servername1,8888 -d TESTDB -Q "update test_data SET status = 'HIRED', CN = 'Testing 'O User' where employeenumber = '12345';"

Below is the error:

  Msg 102, Level 15, State 1, Line 6
  Incorrect syntax near 'O'.
  Msg 105, Level 15, State 1, Line 6
  Unclosed quotation mark after the character string ';
  '.

NOTE: Several questions/comments that wouldn't fit into a comment block ...

What language is your linux script written in? bash? ksh? perl? something else?

Where is the CN value coming from and how are you handling it, eg, is the CN value initially stored in a variable which is then used to build the actual query?

Have you tried passing in the CN value by way of a variable (either sqlcmd/-v or direct OS variable reference like "update ... CN = '${CN}'...")? [I'm guessing it still fails but want to confirm this.]

Could you pre-process the CN value, replacing any occurrences of a single quote with either a) two single quotes ('') or b) a backslash plus single quote (\'); then feed this resulting value into your dynamically created query? [Depending on your scripting language, and how you're handling the CN value, this may be doable with littler overhead.]

While your example demonstrates the issue, it doesn't show us how you're building the query itself ... so I'm wondering if there could be a (relatively) easy solution that, in effect, allows us to escape the single quote while building the query ... ?