MySQL Read/Write Benchmark I'm trying to benchmark Read/Write speed in MySQL 5.5. Currently I have a stored procedure that insert rows with random numbers to a table and I'm measuring time of execution before and after configuration changes.

Here's my procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertRand`(IN nor INT, in minr int, in maxr int)
begin
        declare i int;
        declare lastid int;
        set i =1;
        start transaction;
        while i <= nor do
        insert into tbltest(Value) values (minr + CEIL(rand() * (maxr-minr)));
        set lastid = (select MAX(id) from tbltest);
        set i = i +1;
        end while;
        commit;
    end

Do you know any good way to benchmark this ? Maybe my procedure needs some improvement?

You should use MySQL's BENCHMARK() function

Here are my posts on how to use it

Apr 03, 2012 : How to use the 'select benchmark' command on a procedure
Apr 16, 2013 : MySQL: CAST + SUBSTR vs. FLOOR + MOD

BTW if you do configuartion changes, you should restart mysqld immediately after the configuration changes are in place. Then, run your I/O tests.


MySQL is giving weird result I don't have this kind of ID in my table, still MySQL is giving me a result.
This same result is giving when i place in my php code.

Some DBMS are omnipotent and merciless. They give numerous warnings and errors in all kinds of situations when a user is attempting the slightest deviation from the correct behaviour - defined by the standards and the DBMS documentation.

MySQL is merciful and all-loving. She is trying to help everyone. Even if someone is comparing a string to an integer, she's thinking that the user may know better what he is doing and that maybe he is right. Not wanting to let him go in despair, she is trying - against all consistency and integrity laws - to compare the 2 incomparable things.

And - oh, the miracle - sometimes the 2 things are the same after all! What if one is the number 182 (which comes from an id column, stored in 4 bytes) and the other is the string 182^%^&$$%$%$sjfhjs ^%^&%&*^$^%$^$ which needs 28. They look the same anyway, when the second is truncated from those silly characters!


Set up a postgres database with a specific role without password prompt Context

I have a PostgreSQL server (localhost:5432) which holds a database called my_database.

I have created long time ago, a simple role which owns this database; my_user, different from the postgres role;

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 my_user   | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

This manually created role does not have an OS equivalent user, like postgres does.

I have a bash script to reinitialize my_database with a set of schemas and tables (from the create_db.sql file);

#!/bin/bash
set -e
dropdb -U my_user my_database || true
createdb -U my_user my_database --owner=my_user
psql -U my_user -d my_database -h localhost -v ON_ERROR_STOP=1 -a -f create_db.sql

Issue

Since a few days, this script is asking me 3 times the password for my_user. I guess one time per line.
Before that, I was able to run it without any prompt for any password. That was really cool.

The only thing I changed in between was this;
I usually connect to this server using pgadmin3 with the postgres user to checkout what's inside my tables.
In pgadmin3, I once set up this server credentials to my_user and its password to checkout some differences  with the postgres login.
Then, always in pgadmin3, I switched back the credentials to postgres as they were before.

Question

I cannot understand why this would have broken something which had since then made my script asking for password...
It may be related to something other, but I really don't know what?

I also tried to set up a .pgpass file with this line;

localhost:5432:my_database:my_user:my_user_password

and changing its access to 0600 as stated in the documentation.

But it doesn't seem to be recognized; the bash script is still asking 3 times for my_user password.
And, as I (the ubuntu normal OS user, which is not the same as the role my_user which owns my_database) owns this file , I wonder if I should set up permissions to this file to the postgres OS user instead?

Anyway, this .pgpass file was empty before, when I was able to reset my_database without any password prompt. So if I can recover the previous behavior it would be nice, except if this is not a good practice.

Any clue would be appreciated as I'm not much used to database management.

Information

"PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit"

EDIT

I just noticed that if I set the .pgpass file with this content;

*:*:*:my_user:my_user_password

it works (it stops asking for a password).

But with the database name;

*:*:my_database:my_user:my_user_password

it doesn't (it keeps asking for a password).

You edited away the problem.

In your comments you say that the database is really projATX_db. At least that is what you think. But PostgreSQL translated all unquoted text to lower case, so when you run

CREATE DATABASE projATX_db;

the database is really called projatx_db

Now the entries in the password file are not subject to this lower case conversion (that is specific to SQL), so projATX_db in the password file does not match your database.


strange optimization of query results in phantom value conversions -- DB collation is German_PhoneBook_BIN
CREATE TABLE [dbo].[partition_tbl](
    [tbl_name] [varchar](255) NOT NULL,
) ON [PRIMARY]
INSERT INTO partition_tbl VALUES ('some_table_{num}')
INSERT INTO partition_tbl VALUES ('other_table_{num}')

assuming in your database are the following tables

some_table_123
some_table_444
some_table_hist
some_table_555
other_table_100
other_table_4321

the following statement

SELECT a.tbl_name, REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS number
FROM partition_tbl a
INNER JOIN dbo.sysobjects b
ON (
    b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
    OR
    b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
) AND b.xtype='U'

should return

tbl_name          | number
------------------|-------
some_table_{num}  | 123
some_table_{num}  | 444
some_table_{num}  | 555
other_table_{num} | 100
other_table_{num} | 4321

you can see, that some_table_hist is omitted. But why can't I convert the column number (which is a NVARCHAR at this time) to type INT with both following statements?

SELECT a.tbl_name, CAST(REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS INT) AS number
FROM partition_tbl a
INNER JOIN dbo.sysobjects b
ON (
    b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
    OR
    b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
) AND b.xtype='U'

and

SELECT tbl_name,CAST(number AS INT) AS number FROM (
    SELECT a.tbl_name, REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS number
    FROM partition_tbl a
    INNER JOIN dbo.sysobjects b
    ON (
        b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9]')
        OR
        b.name COLLATE German_PhoneBook_BIN LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[0-9][0-9][0-9][0-9]')
    ) AND b.xtype='U'
) x

Both statement results in an error: Failure on converting the nvarchar value hist to int data type (real error in german says: Fehler beim Konvertieren des nvarchar-Wertes 'hist' in den int-Datentyp). Huh? I can see no hist value in resultset above!? Does MSSQL doing a wrong statement optimization?

(BTW: I'm using now another statement which fixes this issue. I'm only interested in the reason why this strange effect occurs)

As expected.

SQL is declarative and there is no implicit ordering of execution. You will see that the CAST is happening before the filter, so you get the error. That is, you have assumed that only int values will get through the CAST. Y

You should assume that all values in the column will be converted to int. The value here is "hist" which has been parsed.

There are a few ways to ensure that only CASTable values will be presented to the CAST

Materialise the JOIN so it has to run before the CAST
Use CASE to manage the CAST (see below)
Use a table variable/temp table to cache results

Example. You can't rely on the CAST happening after the derived table.

SELECT
    tbl_name,
    CASE WHEN num NOT LIKE '[^0-9]' THEN CAST(num AS int) END
FROM
    (
    SELECT
        a.tbl_name,
        CAST(REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS num
    FROM
        partition_tbl a
        INNER JOIN
        sys.objects b ON
                 b.name COLLATE German_PhoneBook_BIN
                        NOT LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[^0-9]')
    WHERE
        b.xtype='U'
    ) T


Getting the syntax right for MySQL select with inner join Using MySQL version 5.1.73 and phpmyadmin 4.0.10.16.

I am unable to get this past the syntax checker:

select * from courses as co , course_type as ct
left join course_leaders as cl on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id
where co.course_type_id = ct.course_type_id
order by short,start_date ASC

It insists that there is no column "co.course_leaders_id1" which there is indeed.

Any ideas anyone?

ok, this is the result I came up (with the help from below - thanks)

select * from courses as co
left join course_leaders as cl on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id
inner join course_type as ct on co.course_type_id = ct.course_type_id
order by short,start_date ASC

Yes, problem one is that you are mixing explicit (SQL-92) JOIN syntax with implicit join, , (SQL-89) comma syntax. While this is allowed and with some care it may even work correctly, it's very easy to get this type of errors.

The FROM part of your query:

from courses as co , course_type as ct
left join course_leaders as cl
    on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli
    on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id

is parsed as:

from
    courses as co
  ,                        -- <-- that acts as a CROSS JOIN
    (
        (
            course_type as ct
        left join
            course_leaders as cl
        on
            cl.course_leaders_id = co.course_leaders_id1
        )
    left join
        course_links_to_innstyle as cli
    on
        cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id
    )

Now it's clear that inside the parentheses, co is not defined.

Problem two is that there is no joining condition for ct. You have 4 tables but only 2 (and not 3) joining conditions. This is either something you missed or the intention of the query is to do a CROSS JOIN.

Solution:

It's better to use explicit JOIN syntax everywhere and not mix the two (syntaxes).
Fix the code by either adding the missing condition or use CROSS JOIN if that is the intention of the query.


SQL Server - dump schema into separate files keeping installation order While dumping SQL Server's schema, I have these two options: single file or multiple files. Single file is good because is can be used to install schema on clean server - parents are installed before children so there are no problems during installation. On the other hand separate files are good because they can be versioned and changed objects (tables, view, procedures) can be easily identified - but dependency ordering is lost.

Is there a way to have best of both worlds, that is have all objects in separate files and at the same time preserve dependencies ordering? I am asking about existing schema that needs to be versioned after years of neglecting, not about best practices for starting new project.

The best way to get this done is to use a tool to manage the process of getting stuff in and out of source control and then deploying it.

If you have an MSDN license, Visual Studio has a database project that can do this. It's free with the license and works pretty well. It has some limitations, especially when it comes to dealing with code that can lead to data loss. In those cases you may find manual intervention is necessary. Also, you'll have to do some of your database work within Visual Studio because there's no way to use Azure Data Studio or SQL Server Management Studio. However, this approach solves the dependency issue.

There are also third party solutions. I work for a vendor, Redgate, that makes a few of them. We solve the data loss problem and we work within SSMS (and soon ADS). There are other vendors as well (I just think we're better). These tools also solve the dependency problem while still letting you have individual files so that you can track all changes independently through your source control management tool.


Create table in Oracle as select from SQL Server - varchar size I am trying to create table in Oracle 11g as select from a table in SQL Server 2012 via database link.

The table in SQL Server contains column: hslakkis varchar(3)

In Oracle it created the table with the same column but different size:
hslakkis varchar2(9)

This behavior causes me a lot of problems with the columns size in Oracle.
How can I prevent Oracle from increasing the column size?

This is a feature that's controllable by a parameter. From the documentation:

  Multi-byte Character Sets Ratio Suppression This feature optionally suppresses the ratio expansion from SQL Server database to
  Oracle database involving multi-byte character set. By default, Oracle
  gateways assume the worst ratio to prevent data being truncated or
  insufficient buffer size situation. However, if you have specific
  knowledge of your SQL Server database and do not want the expansion to
  occur, you can specify HS_KEEP_REMOTE_COLUMN_SIZE parameter to
  suppress the expansion.

Basically, Oracle is assuming that all of the data in the source table may take up 3 bytes per character, due to characterset conversion.


pgAdmin 4 - Can't Create Script on a database When I try to CREATE Script on a database I want to use, there is no CREATE Script item in the context menu.

But CREATE Script looks fine on another database. I can select it and it works fine.

Similarly, CREATE Script in the Object menu looks fine when I'm on the other database, but is disabled when I'm on the one that I want.

Is this just some permissions that I don't have on the main database?

My user role is:

CREATE USER [redacted] WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

GRANT [redacted], [redacted] TO [redacted];

I'm using pgAdmin 4 version 1.6 on Mac OS X El Capitan v 10.11.6.

This CREATE Script option won't display in the context menu if you are connecting to the server with a user that does not have the SUPERUSER
privilege or which does not have the CREATEDB privilege.


Creating an Oracle 11.2-Compatible Database in Oracle 12.2.0.1 I am using an Oracle Docker image of 12.2.0.1 in my development environment:

https://github.com/oracle/docker-images/tree/master/OracleDatabase/SingleInstance/dockerfiles/12.2.0.1

There does not seem to be an equivalent of this for 11.2.0.4. The database where my code will be deployed into production is 11.2.0.4, so I need to work with compatible=11.2.0.4.

I have tried many times and many ways to set compatible=11.2.0.4 but I cannot get it to work. It seems that, no matter what, due to the built in templates or whatever, I get an error that 12.2 control files are not compatible with 11.2.0.4.

The first method I tried was to set info.ora parameters prior to the first call to dbca and to use dbca responses (in an rsp file) to include compatible=11.2.0.4. This resulted in an error because the dbc template has control files that are incompatible (12.1).

I tried using a dbt template instead, so that control files would not be copied, but the dbca command seems to have created 12.2 control files even though I was requesting 11.2 compatibility.

  Is it possible to create a CDB or PDB with 11.2.0.4 compatibility
  using 12.2.0.1? Currently the question is whether or not it's even
  possible. If it is possible, some high-level guidance as to how to go
  about it would be much appreciated.

Unfortunately I did not save records from yesterday's 10-hour session. For now, knowing whether or not it is even possible to create an older-versioned database will suffice.

Here's what detail I can provide...

The most frequently encountered error was

ORA-00201: control file version 12.2.0.0.0 incompatible with ORACLE version 11.2.0.4.0
ORA-00202: control file: '/opt/oracle/cfgtoollogs/dbca/SIDGNRIC/tempControl.ctl'

This error occurs when my dbca responses included:
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v11.2.0
initParams=audit_trail=none,audit_sys_operations=false,compatible=11.2.0.4
templateName=General_Purpose.dbc

I also tried changing to templateName=New_Database.dbt with the same result.

Long story short:

If you want a 11.2.0.4 database, then use 11.2.0.4. Yes, I know, downloading 11.2.0.4 requires a valid support contract which most visitors here do not have, but you can still download a virtual machine with that version at Pre-built Virtual Machine for Oracle Data Integrator 12c Getting Started Guide.

And here is how to create a 12.2 database with compatible set to 11.2.0.4 (no, I did not even bother with DBCA):

[oracle@o71 ~]$ cat ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
*.audit_file_dest='/u01/app/oracle/admin/EX/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/oradata/EX/control01.ctl','/oradata/EX/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradata/EX'
*.db_name='EX'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=EXXDB)'
*.filesystemio_options='setall'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=256m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1024m
[oracle@o71 ~]$ echo ${ORACLE_SID}
EX

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 20:06:47 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile;

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
SQL> create database;

Database created.

The above creates the database without any dictionary views and packages. I performed the next steps in UPGRADE mode, because without that, my database ended up with several invalid objects that could not be compiled at all.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/catalog.sql
...
SQL> @?/rdbms/admin/catproc.sql
...
SQL> @?/rdbms/admin/utlrp.sql
...
SQL> alter user system identified by oracle;

User altered.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8628936 bytes
Variable Size             394265912 bytes
Database Buffers          662700032 bytes
Redo Buffers                8146944 bytes
Database mounted.
Database opened.

SQL> @?/sqlplus/admin/pupbld.sql
...

And done.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Dec 3 21:14:45 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter compat

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE
plsql_v2_compatibility               boolean     FALSE

SQL> select comp_name, version, status from dba_registry;

COMP_NAME                                VERSION                        STATUS
---------------------------------------- ------------------------------ -----------
Oracle Database Catalog Views            12.2.0.1.0                     VALID
Oracle Database Packages and Types       12.2.0.1.0                     VALID
Oracle XML Database                      12.2.0.1.0                     VALID

SQL> select count(*) from dba_invalid_objects;

  COUNT(*)
----------
         0

SQL> create table t1 (id number generated always as identity);
create table t1 (id number generated always as identity)
*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 12.0.0.0.0 or greater
ORA-00722: Feature "SQL identity columns"

Steps are from Creating a Database with the CREATE DATABASE Statement

I have seen people using 12.1.0.2 production data warehouses intentionally with optimizer_features_enable=9.2.0.8, but this was new...


How to include "Actual Row" counts in Execution plans pulled from the Query Cache or Query Store? Obviously seeing the actual execution plan is a big deal when tuning, and normally I enable the query plan output via SET STATISTICS XML ON and run whatever query needs some TLC, but how can I see actual record counts for historical runs of a plan or for a process I cannot easily run manually (or simulate in a test environment)?

When I pull this information from the query cache via either sys.dm_exec_query_plan or sys.dm_exec_text_query_plan only estimated row counts are shown.  The same behavior exists when using the Query Store DMV, sys.query_store_plan.  Since all of these DMVs are pulling actual plans that were used, I would expect to see actual row execution counts included within the graphical representations of the plan, but they're not there.

The information returned from the sys.dm_exec_query_stats DMV is only somewhat useful as it returns total counts for the statement, but detailed operator counts within the plan seem to be hidden with historical plans.  With 2014, we get the sys.dm_exec_query_profiles DMV to use, which helps with plans currently being executed, but this also doesn't help when looking at historical executions.

Is this information stored elsewhere?  Should I treat the Estimated Row counts as Actual Count numbers (I doubt it, but since I'm asking...)?  Is there a connect item requesting this feature out there I can upvote?

You are not going to see actual row counts from the plan cache as it stores estimated plans with estimatesd row counts and compiled parameter values.

If you truly believe you need actual row counts to come up with a performance solution you have to actually execute the query or trace it using Profiler or EE specifically using the Showplan XML Event Class.


How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle? C# developer encouraged by management to write SQL Server stored procedures often produce procedures like this

create table #t1 (...);
insert into #t1 Select ... from table_a where ...;
insert into #t1 Select ... from table_b where ...;
update #t1 Set ... = ... where ...
Select * from #t1;

The single statement are rather simple and this method makes them produce correct results.

Often my task is to migrate such procedures to Oracle.

Let's face the following facts.

To different temporary tables in SQL
Server are completely independent and
can have any ad hoc structure.
Oracle global common tables are
global objects and all uses much
share the same table structure.
Modifying this structure is
impossible, while it is used
anywhere.

One of the things I learned from an Oracle dba, was to avoid the use of temporary tables whenever it is possible. Even the performance on SQL server benefits from such modifications.

Replace the individual inserts by unions

In the most simple case, the above can be transformed into something like

select case when ... then ... end, ... from table_a where ...
union
select case when ... then ... end, ... from table_b where ...
Order by ...;

Use of functions

Both scalar functions and table valued functions can help to transform your procedure into a single query of the above form.

Common Table expressions aka Subquery Factoring

Subquery Factoring is nearly the best Oracle has to offer to avoid temporary tables. Using it the migration of SQL Server to Oracle is again rather easy. This requires SQL Server 2005 and above.

These modifications improve the SQL Server version and in many cases make the migration straight forward. In other cases resort to global temporary tables makes it possible to do the migration in a bounded time, but it is less satisfying.

Are there further ways to avoid the use of global temporary tables in Oracle?

One way to do this would be Object Types, in this case the type would be analagous to your #t1. So it would need to be defined somewhere but it would not need to be global, it could be per-schema or per-procedure even. First, we can create a type:

SQL> create or replace type t1_type as object (x int, y int, z int)
  2  /

Type created.

SQL> create or replace type t1 as table of t1_type
  2  /

Type created.

Now set up some sample data:

SQL> create table xy (x int, y int)
  2  /

Table created.

SQL> insert into xy values (1, 2)
  2  /

1 row created.

SQL> insert into xy values (3, 4)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

And create a function over this data returning our "temporary" type:

SQL> create or replace function fn_t1 return t1 as
  2  v_t1 t1 := t1();       -- empty temporary table (really an array)
  3  v_ix number default 0; -- array index
  4  begin
  5  for r in (select * from xy) loop
  6  v_ix := v_ix + 1;
  7  v_t1.extend;
  8  v_t1(v_ix) := t1_type(r.x, r.y, (r.x + r.y));
  9  end loop;
 10  return v_t1;
 11  end;
 12  /

Function created.

And finally:

SQL> select * from the (select cast (fn_t1 as t1) from dual)
  2  /

         X          Y          Z
---------- ---------- ----------
         1          2          3
         3          4          7

As you can see this is pretty clunky (and uses collection pseudo-functions, which is an obscure feature at the best of times!), as I always say, porting from DB to DB is not merely about syntax and keywords in their SQL dialects, the real difficulty comes in different underlying assumptions (in the case of SQL Server, that cursors are expensive and their use avoided/worked around at all costs).


SQL Server 2008R2 DBCC CheckDB never finishing I'm running SQL Server 2008 R2 on Windows server 2008 R2 and up until recently we were having no problems. After windows updates recently (no sql server updates) we cannot run step 1 of our maintenance plan DBCC CHECKDB It does not error out, it just runs indefinitely, longest time so far 36 hours. The DB is only about 500MB so I dont think size is an issue.

I was just wondering if anyone has seen this sort of issue in the past.

Edit:
For what it is worth I also noticed rebuilding index and updating statistics is having the same issue. I have no idea if this helps.

Edit2:
So if if it helps anyone after running SELECT * FROM sys.dm_exec_requests; I'm seeing the wait type CXPACKET on the following tasks: ALTER INDEX, DBCC;

There is a very interesting post on DBCC CHECKDB on SqlPerformance, Minimize impact of checkdb. This will give you several ideas, like testing your backup on another server (therefore offloading the charge), using several options (you can experiment with PHYSICAL_ONLY for example).

As CHECKDB is making a big use of tempdb, this you check how things are going on for this database? Is it on separate disk?


MySQL - Simple update is very slow and causing high load average with large amount of server Simple update is very slow  and causing high load average with large amount of server :

table name :

CREATE TABLE IF NOT EXISTS `LISTE_PROMOTION3` (
  `id_list_prom` int(11) NOT NULL AUTO_INCREMENT,
  `id_hotel` int(11) NOT NULL,
  `id_marche` int(11) NOT NULL,
  `lib_promo` varchar(250) NOT NULL,
  `etat_promo` enum('T','F') NOT NULL DEFAULT 'T',
  `type_promo` varchar(250) NOT NULL,
  `debut_vente` date NOT NULL,
  `fin_vente` date NOT NULL,
  `debut_sejour` date NOT NULL,
  `fin_sejour` date NOT NULL,
  `min_sty` int(11) NOT NULL,
  `max_sty` int(11) NOT NULL,
  `free_night` int(11) NOT NULL,
  `type_free_night` varchar(250) NOT NULL,
  `priorite_promo` int(11) NOT NULL,
  `typ_prom` varchar(250) NOT NULL,
  `pourcent_red_lpd` float NOT NULL,
  `pourcent_red_dp` float NOT NULL,
  `pourcent_red_dpplus` float NOT NULL,
  `pourcent_red_pc` float NOT NULL,
  `pourcent_red_pcplus` float NOT NULL,
  `pourcent_red_aisoft` float NOT NULL,
  `pourcent_red_allin` float NOT NULL,
  `pourcent_red_ultraai` float NOT NULL,
  `montant_red_lpd` float NOT NULL,
  `montant_red_dp` float NOT NULL,
  `montant_red_dpplus` float NOT NULL,
  `montant_red_pc` float NOT NULL,
  `montant_red_pcplus` float NOT NULL,
  `montant_red_aisoft` float NOT NULL,
  `montant_red_allin` float NOT NULL,
  `montant_red_ultraai` float NOT NULL,
  `id_agent` int(11) NOT NULL,
  `date_insertion` datetime NOT NULL,
  `date_modif` datetime NOT NULL,
  `id_agent_modif` int(11) NOT NULL,
  `typ_app_prom` varchar(250) NOT NULL,
  `sur_tarif` varchar(250) NOT NULL,
  `pourcent_red_ls` float NOT NULL,
  `montant_red_ls` float NOT NULL,
  PRIMARY KEY (`id_list_prom`),
  KEY `id_hotel` (`id_hotel`),
  KEY `etat_promo` (`etat_promo`),
  KEY `fin_vente` (`fin_vente`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9480 ;

The table contains 9480 rows.

Query:

UPDATE `LISTE_PROMOTION` SET `etat_promo` = 'T' WHERE `id_list_prom` = 9460;

This query update 1 row and takes around 25 seconds to complete and causing high load average with large amount of server.

Get rid of indexes on flags (etat_promo).  The Optimizer almost never uses such; it will prefer to do a table scan.  If you have some query that you think might be using that index, run the EXPLAIN and show us the query for further discussion.


Repreparation of prepared statements in MySQL/MariaDB, how to find a cause? On our server running MariaDB 10.1 (the latest Debian package), with low load (load average: 0.79, 0.88, 1.00), I often receive error message that "Prepared statement needs to be re-prepared".

It is difficult to reproduce, it appears randomly, approximately 40-50 times per day.

I have read documentation about repreparation, where is the following explanation for repreparation:

  Repreparation occurs after DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables.
  Repreparation also occurs after referenced tables or views are flushed
  from the table definition cache, either implicitly to make room for
  new entries in the cache, or explicitly due to FLUSH TABLES.

I'm not aware of any DDL statements running on the server. Our application does not use "FLUSH TABLES". Except our application only internally used Phabricator is running (used by 8 devs) and two other minimally visited applications written in PHP using MariaDB. Here are command statistics:

Do you have any idea, how to find the cause for repreparation?
I don't want to blindly increase table_definition_cache (currently 400) or table_open_cache (2000). Or do you think it is to small and it is safe to increase?

For debugging info: SHOW GLOBAL STATUS, SHOW VARIABLES and memory info from the server.

From the 5.1.25 Changelog:

  Metadata changes to tables or views referred to by prepared statements are detected and cause automatic repreparation of the statement when it is next executed.
  The server attempts repreparation up to three times. An error occurs if all attempts fail.
  Metadata changes occur for DDL statements such as those that create, drop, alter, rename, or truncate tables, or that analyze, optimize, or repair tables. Repreparation also occurs after referenced tables or views are flushed from the table definition cache, either implicitly to make room for new entries in the cache, or explicitly due to FLUSH TABLES.
  The default value of the table_definition_cache system variable has been increased from 128 to 256. The purpose of this increase is to lessen the chance that prepared statements will need repreparation due to referred-to tables/views having been flushed from the cache to make room for new entries.

So...

Com_alter_table = 2496 = 3.5/hour -- This is rather high, and could cause the need for reprepare.
CREATE/DROP TABLE are also rather frequent.
I suggest increasing table_definition_cache.

Further analysis...

Observations:

Version: 10.1.26-MariaDB-0+deb9u1
64 GB of RAM
Uptime = 30d 00:53:13
You are not running on Windows.
Running 64-bit version
It appears that you are running both MyISAM and InnoDB.

The More Important Issues:

innodb_buffer_pool_size = 50G   -- from 1G
     -- Or the dataset size, if significantly less than that.
table_open_cache = 3000
innodb_open_files = 3000
table_definition_cache = 800
join_buffer_size = 1M
query_cache_size = 32M
max_connections = 200  -- Currently 151, and you ran out of connections some time
thread_cache_size = 30

Convert the rest from MyISAM to InnoDB

USE dbname and several SHOWs occur awfully often; what is going on?

A lot of slow queries, table scans, etc.  Look for the slowest few queries and work on improving them: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Did you deliberately turn off some of the optimizer_switches?  If so, why?

Details and other observations:

( innodb_buffer_pool_size / _ram ) = 1024M / 65536M = 1.6% -- % of RAM used for InnoDB buffer_pool

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (16M / 0.20 + 1024M / 0.70) / 65536M = 2.4% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory

( Opened_tables ) = 6,996,394 / 2595193 = 2.7 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 5,643,679 / 2595193 = 2.2 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( innodb_buffer_pool_size ) = 1024M -- InnoDB Data + Index cache
-- woefully small.

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( join_buffer_size / _ram ) = 262,144 / 65536M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( query_prealloc_size / _ram ) = 24,576 / 65536M = 0.00% -- For parsing. Pct of RAM

( query_alloc_block_size / _ram ) = 16,384 / 65536M = 0.00% -- For parsing. Pct of RAM

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Qcache_lowmem_prunes ) = 54,439,645 / 2595193 = 21 /sec -- Running out of room in QC
-- increase query_cache_size

( Qcache_lowmem_prunes/Qcache_inserts ) = 54,439,645/193592210 = 28.1% -- Removal Ratio (pct of time need to prune)

( Qcache_hits / Qcache_inserts ) = 194,941,934 / 193592210 = 1.01 -- Hit to insert ratio -- high is good
-- Consider turning off the query cache.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 2696464) / 7979 / 16384 = 0.108 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size

( Created_tmp_disk_tables ) = 13,897,242 / 2595193 = 5.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Select_scan ) = 168,942,798 / 2595193 = 65 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 168,942,798 / 402363586 = 42.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( binlog_format ) = binlog_format = STATEMENT -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( Slow_queries ) = 169,941,950 / 2595193 = 65 /sec -- Frequency (Slow queries per sec)
-- Rework slow guys; improve indexes; watch disk space for slow log file

( Slow_queries / Questions ) = 169,941,950 / 712388796 = 23.9% -- Frequency (% of all queries)
-- Find slow queries; check indexes.

( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 1,016,245 / ( 1016245 + 217503779 ) = 0.47% -- Subquery cache hit rate
-- Consider SET optimizer_switch='subquery_cache=off';

( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON -- Whether to include such in slowlog.
-- This clutters the slowlog; turn it off so you can see the real slow queries.

( back_log / max_connections ) = 80 / 151 = 53.0%

( Max_used_connections / max_connections ) = 152 / 151 = 100.7% -- Peak % of connections
-- increase max_connections and/or decrease wait_timeout

( Com_change_db / Connections ) = 245,625,094 / 14750877 = 16.7 -- Database switches per connection
-- (minor) Consider using "db.table" syntax

( Com_change_db ) = 245,625,094 / 2595193 = 95 /sec -- Probably comes from USE statements.
-- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( Connections ) = 14,750,877 / 2595193 = 5.7 /sec -- Connections
-- Increase wait_timeout; use pooling?

( Threads_created / Connections ) = 1,856,073 / 14750877 = 12.6% -- Rapidity of process creation
-- Increase thread_cache_size

Abnormally large:

Com_savepoint = 5.9 /HR
Com_show_create_table = 0.31 /sec
Com_show_events = 14 /HR
Com_show_function_status = 14 /HR
Com_show_procedure_status = 14 /HR
Com_show_tables = 2.4 /sec
Com_stmt_close = 139 /sec
Com_stmt_execute = 139 /sec
Com_stmt_prepare = 139 /sec
Com_stmt_reprepare = 0.24 /sec
Com_unlock_tables = 43 /HR
Feature_fulltext = 54 /HR
Feature_subquery = 24 /sec
Handler_discover = 0.072 /HR
Handler_read_rnd_deleted = 62 /sec
Handler_savepoint = 5.9 /HR
Sort_priority_queue_sorts = 137 /sec
Sort_range = 86 /sec
Sort_scan = 57 /sec
Subquery_cache_miss = 84 /sec
innodb_max_dirty_pages_pct_lwm = 0.10%

Abnormal strings:

innodb_cleaner_lsn_age_factor = HIGH_CHECKPOINT
innodb_empty_free_list_algorithm = BACKOFF
innodb_fast_shutdown = 1
innodb_foreground_preflush = EXPONENTIAL_BACKOFF
innodb_log_checksum_algorithm = INNODB
myisam_stats_method = NULLS_UNEQUAL
opt_s__engine_condition_pushdown = off
opt_s__mrr = off
opt_s__mrr_cost_based = off


Beginner design of biological sampling database As a complete newcomer, I would like to practise designing a database based on the biological data my lab has been accumulating over the years. Here is an explanation (I am simplifying/abstracting what we are doing to make this first database simpler):

Each fieldwork season, we would go and measure trees (e.g. height, trunk diametre, etc.) at different geographical locations. Over the years, each tree and location would have been visited and measured multiple times. However, due to practical constraints we may not get to visit and measure all trees at all locations every season.

The purpose of the database are twofold:

(1) Track how each individual tree changed with time, asking questions like: "Did tree A grow taller over the past two years?"

(2) Track how populations of trees at each location changed with time, asking questions like: "Did the group of 12 trees at Acme Acres grow taller in general over the past three years?"

Here is my initial thoughts on what tables to make:

(a) Trees: Fields: TreeID (e.g. "tree A"), Location (linked to table c below).

(b) Visits: This keeps metadata for each visit, with information like the date of visit (for a location, which will be the same for all trees at that location), locations we covered during the visit.

(c) Locations: Basic information on each location, such as latitude, longitude, name of location (e.g. "Acme Acres"), etc.

(c) TreeData: This contains the actual measurements for individual trees. Fields may include: tree (linked to table a), height, diametre, visit (linked to table b). In the end there will be multiple entries any one tree here, with data from each visit.

Does any of this make sense as a first dab at database design? (I've only been thinking about databases for three days)

I would appreciate and humbly accept any pointers you can give me.

Thank you very much!

BTW, One reason I was considering a Visits table was because sometimes I might to compare the data between visits 3 and 4, or between visits 1 and 5, etc

I would rename TreeData to measurements.

Also, unless you really need the Visits table, I wouldn't use it.  If designed properly your database would look something like this if you included the visits table:

Using the visits table would complicate some of the queries that you're wanting answered.  For example, to answer number one you might use something like this in sql server:

select
     t.treeid,
     max(m.height)-min(m.height) as Growth
from trees t
     inner join measurements m
          on t.treeid=m.treeid
     inner join visits v
          on m.visitid=t.visitid
where v.date>=dateadd(y,-2,getdate())
group by t.treeid

without the visits table you could rewrite that like this:

select
     m.treeid,
     max(m.height)-min(m.height) as Growth
from measurements m
where m.date>=dateadd(y,-2,getdate())
group by m.treeid


Can't create/write to file MySQL Server Configuration:

MySQL 5.051a-community-nt-log Community Edition installed on Windows Server 2003 Standard Edition SP1.

This MySQL Server is part of a client's WAMP installation

Problem:

MySQL was originally using the C:\WINDOWS\TEMP directory for temp files. From time to time, the client would get the following error when trying to access data in a table in phpMyAdmin.

"#1 - Can't create/write to file 'C:\WINDOWS\TEMP\#sql_1760_0.MYD' (Errcode: 17)".

Which would require a restart of MySQL to fix.

Thinking this might be a problem with using the C:\WINDOWS\TEMP directory, I created another directory that the MySQL service had read/write access to and pointed tmpdir to that directory in my.cnf.

tmpdir=e:/Program Files/wamp/bin/mysql/mysql5.0.51a/tmp

I restarted MySQL and accessing tables from phpMyAdmin worked fine for one entire day and then the following morning, phpMyAdmin started complaning about the same error.

Investigation:

There is more than enough disk space on both the C: and E: drives

Not seeing any other errors from MySQL.

Window's application logs don't mention anything about the error.

No information in MySQL's logs

Database in question is a mix of MyISAM and InnoDB tables.

I have not yet veririfed, if the inaccessible tables are only the ones in MyISAM.

Questions:

Has anyone seen this problem before with a WAMP MySQL 5.0 installation and know what might be causing it and or how to resolve this error?

The most common cause of this is virus scanning, as virus scanners tend to lock files.

Try excluding the directory from scanning.


Oracle12c: Objects attempting to import to original tablespace (despite wanting to remap them) I've exported a schema from a DB server (Oracle 12.2.0.1.0) using exp with the following command.

exp USERID=FOO/FOO2019 FILE="D:\app\ORACLE\admin\orcl\dpdump\FOO.dmp" LOG=FOO.log

I now want to import the exact same database into the same environment but with a new user and using different tablespaces. So I created new tablespaces for the user (FOO2), created the user (FOO2). I set the default tablespace for the user to also be FOO2 and only gave the FOO2 user access to the new tablespace. However when I attempt to run the import using the following command.

imp USERID=FOO2/FOO2019 FROMUSER=FOO TOUSER=FOO2 FILE="D:\app\ORACLE\admin\orcl\dpdump\FOO.DMP" LOG="FOO2_implog.log"

I'm getting errors as it's attempting to import the database into the original "FOO" tablespace (which doesn't have enough free space). I've tried a bunch of different things and I'm honestly just baffled now as to how I can get everything importing into my new tablespace.

Any help would be appreciated.

Thanks!

Original import does not have any option to remap tablespaces. The tablespace metadata comes from the dumpfile, that is why import wants to create them in the original tablespace.

As a workaround, you can manually pre-create the tables in the new tablespace then load data into them.

But forget original export and import, just use Data Pump.

In the database:

create directory foo_dir as 'D:\app\ORACLE\admin\orcl\dpdump';
grant read, write on directory foo_dir to foo;
grant read, write on directory foo_dir to foo2;

Export:

expdp foo/foo2019 directory=foo_dir dumpfile=foo.dmp logfile=foo.log

Import:

impdp foo2/foo2019 directory=foo_dir dumpfile=foo.dmp logfile=foo2_imp.log remap_schema=foo:foo2 remap_tablespace=foo:foo2


Multiple like conditions for same column? How do I write a query for a Oracle database when I have several "like" values for the same column (in my example data "name")?

My example data for table "address":

name like "John"
name like "Jon%"
name like "Jonathan"
name like "Frank"

Is there something like IN() with LIKE in Oracle or do I have to write it for each value OR separated?


Changing a column data type from datetime to float I have a table with a float column. All values in this column for all records are NULL.
I want to change the data type to datetime.

I executed the query:

ALTER TABLE MyTable ALTER Column MyColumn DATETIME NULL

It works. But when I try to revert the changes by setting the data type to float:

ALTER TABLE MyTable ALTER Column MyColumn FLOAT NULL

I get the exception:

  Implicit conversion from data type datetime to float is not allowed.
  Use the CONVERT function to run this query.

Note that column MyColumn is null for all records.

With no data in the column you can just drop it and add it again.

alter table MyTable drop column MyColumn;

go

alter table MyTable add MyColumn float;

If you have data in your column and it makes sense to convert the values to a float value you can rename the column, add a new column, move the data using convert and then drop the old column.

exec sp_rename 'MyTable.MyColumn', 'Temp_MyColumn', 'COLUMN';

go

alter table MyTable add MyColumn float;

go

update MyTable
set MyColumn = convert(float, Temp_MyColumn)
where Temp_MyColumn is not null;

go

alter table MyTable drop column Temp_MyColumn;


backup the difference with sql server express between 2 timestamps I am newbie to sql and have sql server express edition installed on my PC and contains
a database 'Jdatabase' on 1sept and took a backup(by right clicking on database -> tasks->backup using 'sql server management studio') of it and backup is 20GB
after 1 month , I took the backup  for the same database and it increased to 35GB.
Likewise saving the backup is going to be huge after certain time and is a problem for me.

If I want to backup only the difference ie., 15GB that has grown in 1 month time.
what is the best way to do it using SQL server express.

My guess is that you ran this:

BACKUP DATABASE foo TO DISK = 'c:\backup\foo.bak';

... wait a week ...

BACKUP DATABASE foo TO DISK = 'c:\backup\foo.bak';

The result of this is that your backup file contains two backups, so the file itself will increase in size, whether that delay is a week or a minute.

To avoid this, you can choose one of two options:

You can reinitialize the file to remove any old backups and replace them with the new one:

BACKUP DATABASE foo TO DISK = 'c:\backup\foo.bak' WITH INIT;

This may or may not be desirable, since you only ever have a single backup, and if something should go wrong in between reinitialization and backup completion, you have zero backups.
You can write to a new file each time, typically people put a timestamp on the file or write to a folder made for that day.

BACKUP DATABASE foo TO DISK = 'c:\backup\foo201811010843.bak';

You can use a variable for the destination filename, so you can construct that based on GETDATE() as opposed to hard-coding it.

DECLARE @fn varchar(32) = 'c:\backup\' + CONVERT(char(8), GETDATE(), 112)
  + REPLACE(CONVERT(char(5), GETDATE(), 108),':','') + '.bak';

BACKUP DATABASE foo TO DISK = @fn;

The upside is that you don't have to be tied to a single backup file (and hence single point of failure). The downside is that you will have multiple files to manage... you may want some kind of cleanup task that automatically deletes everything except, say, the last two backups.

Now, there are a couple of issues that are tangential that I have to mention:

Are you only taking full backups? If so, then realize you will never be able to restore your database to a specific point in time, only to the point in time the last successful backup started. If that is a problem, you should use full recovery, not simple recovery, and determine a proper log backup schedule that meets your business requirements for how much data you can lose (RPO) and how long you can wait to get back up and running (RTO) in the event of a disaster.
If you are going to continue using simple recovery, you do have an option to take a backup of only deltas since the last backup, e.g.

BACKUP DATABASE foo TO DISK = 'c:\backup\foodiffwhatever.bak' WITH DIFFERENTIAL;

You'll need to manage files here, too, because all the differentials in the world are useless without the original full backup they reference. For more info, see Differential Backups.
If you are worried about your database growing to 35 GB but not having enough room to store a backup of that database, stop what you're doing right now and go find more disk space. You should not be making a decision between having data and being able to back it up and, frankly, you should not be storing your backups on the same disk as the database anyway (that kind of defeats the purpose). It's like keeping your car key in a jacket pocket and keeping the extra key (you know, in case you lose your jacket) in the other jacket pocket.


Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL I wanted to capture only status/result of Slave_IO_Running and Slave_SQL_Running column values, which I will be using to integrate into our application.

Is there any way to get only the values of those fields instead of getting values of all fields of SHOW SLAVE STATUS.

My OS is windows.

I am using MySQL 5.0.24

Executing

mysql> SHOW STATUS LIKE 'Slave_running';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON    |
+---------------+-------+
1 row in set (0.04 sec)

From the manual:

  This is ON if this server is a replication slave that is connected to a replication master, and both the I/O and SQL threads are running; otherwise, it is OFF.

Caveat: If you're running MySQL prior to version 5.1.46 or 5.5.4, it's possible the slave IO thread could not be running, and the status variable indicate the slave is running. (MySQL Bug 51089)

As of MySQL 5.7, the above solution will not work by default. This is because many of the status variables from information_schema are being moved into performance_schema:

  As of MySQL 5.7.6, the Performance Schema also contains system and status variable tables (see Section 23.9.13, “Performance Schema System Variable Tables”, and Section 23.9.14, “Performance Schema Status Variable Tables”). The Performance Schema tables are intended to replace the INFORMATION_SCHEMA tables, which are deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release.
  [src]

and

  These Slave_xxx status variables become unavailable through SHOW STATUS:

  Slave_heartbeat_period
  Slave_last_heartbeat
  Slave_received_heartbeats
  Slave_retried_transactions
  Slave_running

  Applications that use these status variables should be revised to obtain this information using the replication-related Performance Schema tables.

There are two options to get the slave_running status outside of SHOW SLAVE STATUS

The preferred method, as mentioned by the doc, is to update the application to pull from replication-connection-status.SERVICE_STATE and replication-applier-status.SERVICE_STATE in performance_schema:

  // this is the SLAVE IO THREAD status
  SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;

  // this is the SLAVE SQL THREAD status
  SELECT SERVICE_STATE FROM performance_schema.replication_applier_status;

NOTE: This is for single master replication. Multi-master replication should be in replication_applier_coordinator.SERVICE_STATE

The second option is to turn on show_compatibility_56. This is a temporary fix to get you by without modifying the application, but will go away in a future release.


Is Query Slower When There Are More Columns in one Table? I'm trying to build a login function and was wondering:

Does more columns in a table mean the longer (and slower) the query takes to return a result?

I'm asking because I'm debating on whether to

put only the essential columns to allow users to login (userID, username, password, email, etc.) and separate the other data (first name, last name, address, etc.) to another table with a FK constraint
just put everything in one table.

Would login processing time be affected if I choose option 2 (due to SQL query taking longer because of the many columns)?

I have a related blog post here: Do You Have Tables In Your Tables? that I'm going to quote a bit from.

  What Makes Them (wide tables) Bad?

  They’re nearly impossible to index
  efficiently:

  Queries will hit them in many different ways
  WHERE clauses will be
  unpredictable
  SELECT lists will vary wildly

  When indexes pile up to support all these different queries, locking and blocking will start to become larger issues.

Other things you may need to consider:

LOB data (MAX columns, etc.)
Query Memory Grants
Modification concurrency

Putting everything in one table sounds like a bad idea. If users are allowed to change any information related to a profile that you've listed (name, telephone, personal information, etc.), those queries are now competing to locate and modify rows alongside your app tracking login information.

It's a recipe for bad blocking and deadlocks down the road.

The "one big table" approach sounds quite convenient at first, but the convenience early on will never surpass the inconvenience you face later of needing to split the table up into a proper schema.


How to Variable table name in Insert into Table statement? I want to insert into Tables but error:

  Msg 207, Level 16, State 1, Line 1
  Invalid column name 'A'.
  Msg 207, Level 16, State 1, Line 1
  Invalid column name 'B'.

My table names is : TBL_PriceKeyBoard
Keyboard is variable and maybe hard or ram but TBL_Price is static.

Declare @sql as nvarchar(max)
Declare  @TBL as nvarchar(max)
Declare @Name_Edit  nvarchar(max)
Declare @Name1 as nvarchar(max)= N'B'
Declare @Price as nvarchar(max) = N'12'
Declare @NameTBL  as nvarchar(max)  = N'KeyBoard'

set @Name_Edit = N'A'
 set @TBL = N'TBL_Price'+ @NameTBL
Set @sql = N'INSERT INTO [DB1].[dbo].'+quotename(@TBL) +'
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             '+@Name_Edit+'
            ,'+@Name1+'
            ,'+@Price+'
            )'
            EXECUTE sp_executesql @sql

You're not quoting your strings properly.

When you insert a string, it has to be surrounded by single quotes, which in Dynamic SQL-ese, is three ticks. This should work.

DECLARE @sql AS NVARCHAR(MAX);
DECLARE @TBL AS NVARCHAR(MAX);
DECLARE @Name_Edit NVARCHAR(MAX);
DECLARE @Name1 AS NVARCHAR(MAX)= N'B';
DECLARE @Price AS NVARCHAR(MAX) = N'12';
DECLARE @NameTBL AS NVARCHAR(MAX)  = N'KeyBoard';

SET @Name_Edit = N'A';
SET @TBL = N'TBL_Price' + @NameTBL;
SET @sql = N'INSERT INTO [DB1].[dbo].' + QUOTENAME(@TBL) + '
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             ''' + @Name_Edit + '''
            ,''' + @Name1 + '''
            ,''' + @Price + '''
            )';
              PRINT ( @sql );
            --EXECUTE sp_executesql @sql

When I print the code, I get this.

INSERT INTO [DB1].[dbo].[TBL_PriceKeyBoard]
           (
            [Name_Edit]
           ,[Name1]
           ,[Price]
           )
       VALUES
            (
             'A'
            ,'B'
            ,'12'
            )


Big data table processing with RDBMS like MySQL? I have two tables Customers and Orders and each has 100 million records.

Now I have to join these two tables on the column customer_id in both Customer and Order table to generate a report. customer_id is the primary key in customer table and foreign key in order table.

I have heard from senior folks that it is close to impossible to do it MySQL(or any other RDBMS) because a single system has to perform this join on a huge volume of data. Is that correct RDBMS like MySQL(or Oracle) are not good for such a large volume of data even for simple operations like join with proper indexes ?

Also I would like to know what data size per table is considered good for RDBMS table so that simple operations like join, aggregation with proper indexes works well ? I know answer may be "It depends" or vary based on number of factors. But consider best possible tuning is done, still is there any broad level standard data size limit that is considered good ?

Basically you are talking about performance. There are so many factors/parameters responsible for performance. With no doubt 100 million records are feasible in RDBMS, it depends upon configuration you have made for your server. If we look for some parameters be like :

1) Hardware : Looking into your physical table size, proper RAM allocation is done or not. For feasible amount of RAM proper CPU cores are divided or not.

( Specifically of MySQL )

2) Sort Buffer : Whether optimal values are configured for Sort buffer global variables.

3) Temporary Table Size : Temporary table size should be suitable enough

4) Join Buffer : Join buffer should be configured with optimal value.

And many more parameters need to look in.

Hope this helps.


Data Warehouse -- Generating natural keys to be used in WHERE clauses I need to repeatedly retrieve a group of records from a data warehouse fact table based on multiple where conditions.

Not

WHERE (user_d=1 OR user_id=2) AND (branch_id=1 OR branch_id=2)

but

WHERE (user_id=1 AND branch_id=1) OR (branch_id=2 AND branch_id=2)

I was thinking of doing something like this:

... WHERE CONCAT(user_id,branch_id) IN ('11','22 )

and then creating a new column, turning the concat into a natural key which will be created on insert:

WHERE new_key IN ('11','22')

This will allow:

SELECT queries to avoid JOINS. I have input of user_id and branch_id, and can generate that in the application.
I can put a unique constraint on the key field, and then can use an UPSERT.

Is generating a natural key like this strictly for easier lookups bad practice?

CLARIFICATION

I want to use an IN clause because I will have a lot of combined values to lookup.

I won't have IDs to build the new_key or look it up, it will be values.

I think I answered my own question. It's bad practice because it's redundant data.

The CONCAT will be a better choice off a JOIN. To optimize, instead of storing values in the database, it's better to add a key-value store in front like redis.

Just use composite keys. If the table's natural key is (user_id, branch_id), use that.

CREATE TABLE whatever
(
    user_id ....,
    branch_id ...,
    PRIMARY KEY(user_id, branch_id);
);

In joins you can do it like you showed:

SELECT ...
FROM sometable s
INNER JOIN whatever w ON (s.user_id = w.user_id AND s.branch_id = w.branch_id)

or pairwise:

SELECT ...
FROM sometable s
INNER JOIN whatever w ON (s.user_id, s.branch_id) = (w.user_id = w.branch_id)

For use with IN, use lists of anonymous records:

SELECT ...
FROM whatever w
WHERE (w.user_id, w.branch_id) IN ( ('a','1'), ('b', '2'), ... )

or if you have a lot of values, where this starts getting inefficient, join on a temp table. Big IN lists are bad whether they're singular values or composite.


Migrating a cluster from SQL Server 2005 to SQL Server 2016 I am in the process of migrating a SQL Server Cluster. I would like to hear considerations for a successful migration.

The specs of production cluster are:

Windows Server 2003 SP2 32bit.
SQL Server 2005 Enterprise edition - 9.00.3152.00 (Build 3790: Service Pack 2)
Disk space is about 1.5TB including all databases. (IBM v7000)

The specs of destination cluster are:

Windows Server 2016 Standard 64 Bits
SQL Server 2016 Standard 64 Bits
Same disk as old cluster, but faster (IBM v9000 Flash system)

Special Considerations:

Cluster Name must be the same to avoid changes in applications, linked servers and other connections. I mean, we have to migrate with a different cluster name, then shut down old cluster and rename the new one using the old name (I hope this to be clear)

At this time what I have done and tested is the following:

Create and configure a Windows Failover Cluster using Windows Server 2016 (with a different name)
Configure Active Directory to allow cluster to create objects
Restoring a database from MSSQL 2005 to MSSQL 2016, run some queries and explore objects.

Also:

I have split the drives following the best practices.
We are cleaning "super users" and giving just the right permissions to users.
Setting and testing compatibility mode to 130
Backup strategy was defined about 1 week ago to ensure data availability

Things to do or consider:

Download all SSIS packages to new project and test them against the new cluster. Once the cluster name changes, change SSIS to original name
Migrate all Jobs and make sure they run successfully
Test application that uses the migrated database
Create ODBC connections for Linked Servers pointing to SQL Server 2000 Databases (yes, we still use MSSQL 2000)
Backup Active Directory to have a rollback point in case something fail regarding cluster names (we have to delete old object and create a new one)
Configure SQL Replication.

This is one of those questions that I love to see. There are a lot of things to add to do to add to migration and upgrade successes and it warms the hear to see the question.

You've already covered a lot of things to consider and I'll add a few thoughts but I am looking forward to seeing other answers with more ideas from others.

I'm not including things you've already included in the great list you have started and knowing me I'll be editing and adding to this answer as I go.

**One disagreement I may have with your approach: Regarding the cluster network name, why not make a new cluster name and just use CNAME to point connections to the old name to the new instance? This lets you make a new name, avoid messing with conflict potential, makes go live night smoother and gives you a clearer and easier rollback. I would do a CNAME approach and change the CNAME once you are sure the old can be taken offline. I may be alone here so I welcome comments supporting that idea or disagreeing.

I see a few areas of focus when it comes to migrating. If you make an "outline" of the phases and outline of the areas to focus on comes to mind: Planning, Pre-Migration Steps, Day of Migration, Post Migration. I'll cover some of those sections here and I am hoping others join in with ideas.

Planning Thoughts

Since you are starting with new hardware. This is a great chance to make sure you are considering all of the best practices that may have been overlooked before. Things like:

Split out your drives. At the very least I'd love to see separate drives for (Your Mileage May Vary here also, really depends on performance profile of your setup):

Operating System (C drive)
SQL Server binaries and maybe system DBs excluding TempDB
User Data Files
User Log Files
TempDB data and Log Files
Local backup and/or misc drive for file imports, staging of data, etc

When formatting your drives for Data files and TempDB specifically, consider doing the 64KB allocation unit size. (Source)
Now is your chance to maybe clean up some of those security holes and flaws that could exist. Look to build with best practices and least access.

Before Migration

Lots of things to do before the migration. Some that come to mind immediately are:

Run the upgrade advisor. SQL Server 2016 made some improvements there and it is a separate download. Look to see what issues may exist.
Do a more thorough test of your DB to see what breaks and what works. Do a test migration and put into 2016 (130) compatibility mode. And do thorough testing. If you don't have a test approach you can look at something like profiling activity and replaying to see what breaks. Test. Test. Test.
Grab the SQL Server and Windows Server 2016 documents about clustering and failover clustering. Look to the checklists and prerequisites carefully.
Ensure you have good transaction log management - why migrate an excessively large LDF file due to poor management.
Make sure you are doing maintenance properly on your databases - especially make sure you are running regular DBCC CHECKDBs - this is a good thing to do before migration or really as part of final migration, but you don't want the first time you do one to be on migration night and find surprises.
Determine success criteria and have a rollback plan (doing a migration to new is great, you can simply rollback any CNAME changes, connection strings and point back to old) in case of issues. And know what issues constitute a rollback and what issues you'll "stay and play" with post migration. Get buy in for this list.
Have a really good backup strategy. Know you can restore. Know where your backup files are, etc.
Look to all server objects. You covered a lot. But don't forget credentials, proxy accounts, alerts, maintenance jobs, SP_Configure settings, etc. A GREAT tool for you to look at that can actually help you remember things is the PowerShell DBATools scripts. These scripts can help you migrate all of these items - including SQL authenticated logins with the correct SIDs for your databases, but they also can help you remember what to migrate when you look through all of the modules. You can look into the scripts here.

Immediately After Migration

Lots more here. And a category of "On migration week" could be added and maybe someone will or I will edit and add later.

Some things here that come to mind straight away:

Have a smoke test / check to verify all came over well.
Change compatibility mode to SQL Server 2016 on all databases if testing proved that out to work. I see this getting missed a lot.
Make sure maintenance and backups are in place. Run a post migration DBCC CHECKDB on the databases.
Make sure your jobs are running.
Configure monitoring and alerting so you don't miss alerts about this server
Once you are satisfied with a limited test, turn the old off and make a CNAME change.


MySQL: What happen when an executing sql's client is terminated? Executing long-time sql like

UPDATE ACC SET A = 'EE' WHERE A IS NULL

then I found I made a mistake, so I kill my client. When I restarted client and executed another sql, like

UDPATE ACC SET A = 'EE' WHERE ID = 1888

but I got error:

  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

I searched this error and found it was caused by uncommitted transaction holding locks. My question is:

Can close client connection stop sql execution?
If quesion 1's answer is NO, when sql query finished but client disconnected, will MySQL commit the result?

I believe that killing the client won't be noticed by the server until the server sends data to the client.  That is, the server will finish the query.

COMMIT happens in different ways.

BEGIN; UPDATE...; <client dies> -- has not gotten to COMMIT, so no commit.
autocommit=OFF ... UPDATE ... -- same as above
autocommit=ON ... UPDATE...; <updated finished, so committed> (I think)


Postgresql simple query takes too much time like five minutes to execute a very simple postgres query, like SELECT takes five minutes to execute. It was working fine, taking less than one second and at some point the execution time increased significantly to five minutes. When I do SELECT query to another table in the same database - it works pretty fast. Only one table is affected and it seems to happen at some point, before it was also executing fast. Following query takes five minutes to execute:
SELECT * FROM some_table LIMIT 50 OFFSET 0;

another thing is that if I select by Id - it works instantly, very fast, less than one second:
SELECT * FROM some_table WHERE id = '33ae0b5f0d6a0435e36faf8d';

UPDATE 1
table definition has 34 columns,
Id primary key column is indexed, it is GUID not integer
table contains only 8455 rows
server_prod=# SELECT COUNT(*) FROM some_table;
 count
-------
  8455
(1 row)

UPDATE 2
server_prod=# select current_setting('shared_buffers') AS shared_buffers, pg_size_pretty(pg_table_size('some_table')) AS table_size;
 shared_buffers | table_size
----------------+------------
 128MB          | 62 GB
(1 row)

server_prod=# explain (analyze, buffers, timing) SELECT * FROM some_table LIMIT 10 OFFSET 0;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..9677.70 rows=10 width=1379) (actual time=363665.384..363668.365 rows=10 loops=1)
   Buffers: shared hit=5701604 read=1443335
   ->  Seq Scan on project_views  (cost=0.00..8109910.80 rows=8380 width=1379) (actual time=363665.381..363668.358 rows=10 loops=1)
         Buffers: shared hit=5701604 read=1443335
 Planning time: 0.099 ms
 Execution time: 363668.414 ms
(6 rows)

UPDATE 3
psql version is 10.7
server_prod=# SELECT version();
                                                                   version
---------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

please advise.

When you use the LIMIT clause, it is applied after all the data is loaded to filter down the data, which is evident by your EXPLAIN as it is the last operation (top of the operation chain). This is because LIMIT isn't a predicate filter, rather it just takes the top N number of rows from the total result set. While 8,455 rows is tiny, the actual Table size is 62 GB on disk which is a decent amount of data to SELECT all at once and then filter and return to the client.
When you use the WHERE clause, the filtering is applied upfront, and can be done efficiently because your id field is indexed, so very few rows need to actually be analyzed to serve the data (since the logical data structure is a B-Tree) and therefore much less data (in file size) to load and process.
If you want a more efficient way to get only 50 records back, you'll need to find a way to use predicate filtering with your WHERE clause to reduce the data first, and then apply your TOP if needed. E.g. if you know you which range of ids to filter on (though I'm assuming since you're using a GUID that'll be hard to do). If there are other fields you can use to help filter down the data with predicate filtering, then you should create indexes on those fields as well, but that will depend on your table definition.


Transact SQL using WITH in CREATE VIEW I want to create VIEW using WITH clauses, but really can't find any references on correct syntax.

I want smth like this

WITH TempTbl AS (SELECT ...)
CREATE VIEW SomeView
SELECT *
FROM TempTbl

And what is the correct syntax for using several WITH clauses?

Nothing useful on MSDN :(

The CTE goes inside the view.

Take a query with a CTE

WITH cte AS (...) SELECT ...;

Just add CREATE VIEW AS .. GO

CREATE VIEW
AS
WITH cte AS (...) SELECT ...;
GO

MSDN does describe multiple CTEs (See example j)

CREATE VIEW
AS
WITH
   cte1 AS (...),
   cte2 AS (...),
   cte3 AS (...)
SELECT ...
GO


Are file locations stored in PostreSQL catalog? I am trying to learn a bit of PostgreSQL, and I am currently trying to understand the catalog. I think that if it stores all the metadata of the DB, should it also store things like the file locations?

I mean, I can (not involving the catalog) do:

SHOW data_directory;

or

SHOW config_file;

etc etc. I think it would be quite interesting to retrieve the same information via a catalog query, but I am not finding information about it. At most, I could do:

SELECT pg_settings.name, pg_settings.sourcefile FROM pg_settings WHERE pg_settings.sourcefile LIKE '%.conf%';

and kind of see where the files should be stored. I wondered if there is a more direct way to get that information.

Maybe is not possible?

The config file and data directory location cannot be stored in the system catalogs, because the database cannot read the system catalogs until it knows where they are.

It's a bit like putting the map to your buried treasure into your treasure chest.

(Note that pg_settings is not technically a system catalog. It is a view that shows GUCs as configured by postgresql.conf / ALTER SYSTEM ... SET, by ALTER USER ... SET, by ALTER DATABASE ... SET, etc.)


difference between UPDLOCK and FOR UPDATE given the following code:

Set conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
conn.BeginTrans
...
//This is the line it is about
RS.Open "SELECT a,b FROM c FOR UPDATE", conn, adOpenDynamic, adLockPessimistic
...
RS.Close
conn.CommitTrans
conn.Close

What is the difference when I use:

RS.Open "SELECT a,b FROM c (UPDLOCK)", conn, adOpenDynamic, adLockPessimistic

UPDATE:

Right now we use FOR UPDATE but it looks like the table is not locked. The SQL Profiler shows us this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

declare @p1 int  set @p1=NULL  declare @p3 int  set @p3=229378  declare @p4 int  set @p4=163842  declare @p5 int  set @p5=NULL  exec sp_cursoropen @p1 output,N'SELECT a,b FROM c FOR UPDATE',@p3 output,@p4 output,@p5 output  select @p1, @p3, @p4, @p5

UPDATE c SET a = a + 1 WHERE b = 'Value'

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

IF @@TRANCOUNT > 0 COMMIT TRAN

The update statement we use:

SQL = "UPDATE c SET a = a + 1 WHERE b= 'Value'"

FOR UPDATE is not valid SQL Server syntax in a regular SQL statment, it is used when you create a cursor.

But you are probably using ADO with CursorLocation adUseServer and then your query actually works because ADO will use sp_cursoropen which accepts the syntax used for cursors.

The default behavior in SQL Server is that cursors can be updated so specifying for update does nothing for you unless you also specify a column list.

Specifying the updlock query hint on a cursor will only do things for you if you are running in a transaction. With updlock the locks is placed when you do fetch next from ... and without updlock the lock is placed when you do update ... where current of, still only if you are in a transaction.

So in your case, using updlock will place locks when you fetch data if you are in a transaction. If you don't use updlock, in a transaction, you will place the locks when you update the data. If no transaction is present there is no difference between the two and you could as well not use any of them.


SQL Update Efficiency and Blocking Concern Say I have some SQL similar to the below, which is an update with many joins. The number of rows actually being updated may be 1000's or potentially only a few. The actual join takes a long time as there are millions of rows involved, probably around 30s. Would the below only lock the trade table t on the rows that need to be updated or is it being locked as the joins are underway? So would it be better to use a CTE or temp table to gather this data and then update?

I'm having deadlock issues and wanted to make sure updates like this aren't part of the problem.

update
t
set
t.price = d.latestprice * f.rate
from
trade (nolock) t
inner join tradedetails (nolock) d on d.ID = t.ID
...
...
inner join fxdetails (nolock) f on f.CCY = t.CCY

Table locking is a good thing, it's done so your data doesn't get corrupted.

I wouldn't recommend using NOLOCK when writing data, you should have a look at the answers to Is the NOLOCK (Sql Server hint) bad practice?

An article linked from the question (SQL Server NOLOCK Hint & other poor ideas) has as it's first recommendation:

  Do not use any HINTs until your testing proves that you have an issue
  that can’t be solved any other way than by using a HINT.

As ypercube commented, I would work out why your SELECT query is slow before applying lock hints to you query.


Average Time That an App's Client is connected to a Database? I'm studying Tuning Aspects, and my professor told me that a good practice it's to expect some stuff, between them there is this fact of Exoext the Average time that a client will be connected.

Is there an average time that an apps is connect to a server? I heard something bout there are 2 type of client's connection, but nothing clear.

I want if it's possible something like "The average it's between 50 and 70 miliseconds" for example.

MySQL does not keep any metric like that.

I would instrument the client in order to get the info.  (In fact, I have done such.)  At the beginning of the client (or when the client connects), capture the time to sufficient precision to get milliseconds.  At the end of the client (or connection), capture the time again.  Save the result in a table for such.  Later do SELECT AVG(...)... to get the information you desire.  It is a more complex mathematical problem to say "Most connections are between 50ms and 70ms."  For that, you might want to deduce the 10th and 90th percentiles.  (This is best done in client code, not SQL.)


Multiple rows as columns in SQLite I have this three tables:

student table

| _id | name      |
| --- | --------- |
| 1   | Student 1 |
| 2   | Student 2 |
| 3   | Student 3 |
| ... | ...       |

subject table

| _id | name      |
| --- | --------- |
| 1   | Subject 1 |
| 2   | Subject 2 |
| ... | ...       |

relationship

| student | subject |
| ------- | ------- |
| 1       | 5       |
| 1       | 4       |
| 2       | 7       |
| 3       | 8       |

What I want is make a query that looks like this:

| student   | subject1  | subject2  |
| --------- | --------- | --------- |
| Student 1 | Subject 5 | Subject 4 |
| Student 2 | Subject 7 | NULL      |
| Student 3 | Subject 8 | NULL      |

Each student has 1 or 2 subjects at most.

Tried with

SELECT st.name AS student,
       su.name AS subject1,
       su2.name AS subject2
  FROM student AS st
       JOIN
       student_subjects AS ss ON ss.student = st._id
       JOIN
       subject AS su ON ss.subject = su._id
       JOIN
       subject AS su2 ON ss.subject = su2._id
 GROUP BY st._id;

but the result is

| student   | subject1  | subject2  |
| --------- | --------- | --------- |
| Student 1 | Subject 5 | Subject 5 |
| Student 2 | Subject 7 | Subject 7 |
| Student 3 | Subject 8 | Subject 8 |

Being sure there are a maximum of two subjects makes life a lot easier:

select st.*, su1.*, su2.*
   from student st
   left outer join subject su1
     on su1.id = (select min(sub_id) from rel where stud_id = st.id)
   left outer join subject su2
     on su2.id = (select max(sub_id) from rel where stud_id = st.id)
     and su2.id > su1.id

which gives us:

1|Student 1|4|Subject 4|5|Subject 5
2|Student 2|7|Subject 7||
3|Student 3|8|Subject 8||


Rebuild the Fulltext catalog I have a Database (80GB) that is able to store attachments. These attachments are full text indexed. For some reasons I had to rebuild the fulltext catalog and this took 30 hours.

Our business users noticed while rebuilding the new catalog as some searches didn't work anymore as expected, but some did.

I was wondering how SQL Server proceeds while rebuilding the fulltext catalog.

Does it delete all index and then start rebuilding it? Or is there some intelligent logic behind it, like delete just a part of it piece-by-piece?

I was wondering how SQL Server proceeds while rebuilding the fulltext catalog.

From BOL :

When you REBUILD a fulltext catalog, SQL Server rebuilds the entire catalog. When a catalog is rebuilt, the existing catalog is deleted and a new catalog is created in its place. All the tables that have full-text indexing references are associated with the new catalog. Rebuilding resets the full-text metadata in the database system tables.

I would suggest you to look into REORGANIZE depending on the fragment counts as described in my answer for Reorganize full text catalog is offline or online?


How to develop a database (workflow)? tl;dr: I know a few SQL semantics and I know how databases work (basically) but I never created one that had a considerable size. I lack of knowledge concerning workflow, SQL-code management and very basic SQL programming practices (write by hand or use e.g. pgAdminIII). I need some "instructions" on how to manage my source code and state of my database.

Okay, this sounds like a very basic question and in fact it is a very basic question.

Five years of computer science and I never really had to develop a database by myself and now I am here and have no idea how to do that. Now, as a software developer writing Java or C or whatever the workflow is pretty clear of course. Thinking about design, creating some files, write code, use version control and commit/push. Next day repeat.

My problem is that I can't get my head around that incremental nature of creating a database. What I mean is that I don't know how my SQL script should look like if I commit it to my repository and if I use e.g. something like FlywayDB.

At the beginning I'd have something like this e.g.

DROP TABLE IF EXISTS company_employee;
CREATE TABLE company_employee (
    id BIGSERIAL PRIMARY KEY
);

and I need that DROP TABLE IF EXISTS because while I am developing I might change that table all the time and therefore drop it each time before I recreate it. But do I want to commit that script?

I mean if somebody accidentally runs it on my production server it would drop all my tables and re-create them. So one question I'm asking is e.g. how do my scripts look like that I am actually committing to my e.g. git repository?

Do I have different versions of that file? One that is e.g. a "development"-version and another is a "I am confident it doesn't break anything"-version that I commit?

Another option could be that I e.g. just create the tables in the first step:

-- V1__create-company-tables.sql

CREATE TABLE company (
    id BIGSERIAL PRIMARY KEY
);

CREATE TABLE company_employee (
    id BIGSERIAL PRIMARY KEY
);

and in a second file I add the foreign keys:

-- V2__adding-company-tables-foreign-keys.sql

ALTER TABLE company_employee

    ADD COLUMN company_id BIGSERIAL,
    ADD CONSTRAINT fk_company_employee_company FOREIGN KEY (company_id) REFERENCES company(id),

But that would mean that I'll never have all in one file - just fragments of my database in different files V1 toVn.

Another thing I could do is to just use pgAdminIII and add everything I need, then reverse engineer my database, run it with FlywayDB and commit that generated file to my database. But I don't like that idea since pgAdminIII uses deprecated stuff like WITH ( OIDS=FALSE ).

So well, I would be glad if someone could bring me on track somehow and give me a scratch of how the actual development process of a database looks like.

Should I use pgAdminIII or write my whole database by hand?

How do I version my .sql files? Where do I store them on my local machine?

pgAdminIII doesn't even seem to have something like a "Database Project" - it just offers access to modify my database but there is no source code management. I am really confused about how my setup would/should/could look like.

I can only tell you what we do now and what I have done in the past. Our shop uses MSTFS (Microsoft Team Foundation Server), so any version we have the ability to get the latest version of the script.

When we create the scripts we include the customary header - Description, Date, Author, Title, and sometimes Build number. This helps us to maintain code versioning and build numbers with database code.

We branch SQL code with functional code so as we integrate branches from developers, their SQL code comes with. When we moved to production, it all moved together.

In other positions, the data group worked with developers to create the database ERD. Then the data group came in as another branch to the project and create database objects. When the functional code was complete and the move was going to production, their branch was deployed to the data tier while the code was deployed to the app tier. The members of the DEVOPS group tied it all together.

There are many ways you can go. You just have to pick one. I suggest reading further on Application Lifecycle Management (ALM).

Typical ALM treats DB versioning like an afterthought if at all. Many companies treat the DB in the same fashion.

The answer is, it depends. I use a tool (MySQL Workbench, VS) to script my database object creation and I version the scripts based on build number. Typically it's YYYYMMDD#. Most DB tools are rudimentary so your mileage may vary. Try some different methods and find what works for you.


Data from different tables into one table I've requirement where I need to store information from different sources in my system and the process that data. When I say from different sources it will be completely different table structure from all the sources and these sources will be infinite.

If I've to maintain the same table structure of source then I've to create multiple tables for each source and I'll have thousands of sources so my database will be bloated with lot of tables. All this information is accessed within my system so for each source I need to maintain mapping information with that I can query particular table to get that source data.

Hence I came with a single table structure to store that data and it looks like below - tables with column information:

TableMetaData
Id, TableName, SourceId

TableFieldMetaData
FieldId, FieldName, TableMetaDataId

TableFieldValues
Id, MetaDataFieldId, Value

Example source table

Customer
CustId, Name, Location
1, ABC, London

above table will be stored as below in my three tables:

TableMetaData
1, Customer, XYZSource

TableFieldMetaData
1, CustId, 1
2, Name, 1
3, Location, 1

TableFieldValues
1, 1, 1
2, 2, ABC
3, 3, London

I want to know whether this is the best approach to store data or not? I know this single table will have millions of records and retrieving the data will be very tedious too. Is there any better approach for storing this kind of data? Or should I go with multiple tables approach which I mentioned initially where every source will have replica of their tables in my system.

What is the industry standard for this?

What you are proposing is a variant of the so-called "Entity-Attribute-Value" or EAV design pattern.

Aaron Bertrand has an excellent article on the advantages and pitfalls of this design over on SQLBlog.org.

Many people trash-talk EAV, however if done correctly it can be the easiest way to manage diverse sets of data, and is in-fact designed particularly well for your scenario.


The order of INNER JOIN's I would like to know if there are any rules about the order of INNER JOIN's in T-SQL 2000.

I had 62x the performance on my query when my INNER JOIN that act like a filter is placed at first instead of the end.

In reference of http://sql-4-life.blogspot.com/2009/03/order-of-inner-joins.html

The optimal sequence (the query plan) is determined by the query executor, based on the SQL code (operators you use), data statistics (statistical distribution of values in columns, data volume, etc.) and database structure (availability of indexes, datatypes, etc.).
So, there are rules, but first not all of them are under your total control and second the result is detemined by a combination of many factors.

To evaluate performance, you should always take a look at the estimated query plan and compare the changes to the plan caused by the changes in your SQL code, to undertand why a query run faster or slower than another.


how to insert the remote table data into another table in my local computer in mysql? Can any body tell me how to accomplish this?

I have a remote table from which I need to import part of the data and put it into a table on my computer(local). I know select into command and its use but I don't know how to use it for this kind of situation.

Try this from your local machine:

mysqldump -u username -p password -h remote_host --databases remote_database --tables remote_table --where="id=757575" | mysql local_database -u username -p password


SQL 2016/SQL 2017 CE Performance on Information_Schema inferior to SQL 2014 out of the box I have a Database with 2300 Tables/Views. When i run this query to do a simple select on information_schema, i get over 2 minutes on SQL 2016/2017 but under 5 seconds in SQL 2014. When i enable the trace flag 9481. It works. Here is the query. The difference is the Cardinality Estimator, however my question is why would it not work out of the box like in SQL 2014.

SELECT
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_SCHEMA,
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_NAME,
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME as CONSTRAINT_KEYS,
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as REFERENCE_SCHEMA,
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME as REFERENCE_TABLE,
  unique_usage.COLUMN_NAME as REFERENCE_COLUMN
FROM
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
INNER JOIN  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
ON
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME =
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
INNER  JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS
ON
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME
INNER JOIN       INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE unique_usage
ON
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =
  unique_usage.CONSTRAINT_NAME

If any of you has a database with large number of tables.You are welcome to try it and see what you see. Our adhoc reporting tool depends on these queries and it is proving a roadblock to upgrade to SQL 2017.

Here is an excellent article that discusses all the aspects of the new cardinality estimator.
Bottom line - as with all changes, you win some, you lose some, and hopefully overall you win more than you lose.
With the new cardinality estimator, it is a huge win overall, in spite of the occasional hickups like you are experiencing.
You have several work arounds other than the TF, and they are discussed in the article. Easiest should be either using a query hint, not to lose the benefits of the new estimator.

USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')


SQL Server: Unable to save import export package, but am on Standard Edition I am confused.

Issue: SSIS ("SQL Server Import Export Wizard") should let me build a data import package and save it to a file, for easy re-running (converting to a task, etc.).

I want to save a package, but the tool always tells me "profile cannot be saved because your are not running SQL Standard Edition."

I know that the SSIS ("SQL Server Import Export Wizard") that, but I think I qualify.

Why is the save option not available?

I am connecting to an instance of standard (from connection properties in SSMS):

Microsoft SQL Server Standard Edition (64-bit)  (SQL 2005)

I have tried the SQL 2012 and 2014 client tools, and have the same experience on each.

How do I solve this?

(Is the tool detecting Express edition that I have locally?)

Community Wiki answer created from an answer in the question comments by billinkc

The version of the DTS Wizard that is launched is or isn't licensed based on the installation. Here you can see that I am licensed for SSIS on my 2008 instance but not for the 2014 (because I only installed client tools):

You can have a valid SSMS install and even a BIDS/SSDT install, and still not be licensed for creating SSIS packages. Also, you might be connecting to a licensed Standard Edition box but the tools on your local machine would need to be Dev/Standard/BI/Enterprise/DW edition.


Risk of changing clocktime on MySQL server host I have a MySQL server host that have a clock that is about 75 minutes wrong. Is there any risk in changing the hosts clock while MySQL is running?

Update: Obviously NOW() and other functions returning current time will return a different result. In my case I am thinking of spinlocks and other scary internals in MySQL.

Binary logs will record the events sequentially with whatever timestamp it has. Using any binary log around the time of a clock change makes the particular log unusable for PITR (Point-in-Time Recovery).
If the DB Server is a Slave, Replication may skip processing certain events.
If you have Binary Logging or Replication Enabled, I would do  the following

On DB1, stop writes to the database
On DB2, run SHOW SLAVE STATUS\G multiple times to make sure there are no new commands being processed and Seconds_Behind_Master is 0
On DB2, STOP SLAVE;
On DB1, RESET MASTER; (Erases all binlogs)
Stop MySQL Shutdown on DB1
Stop MySQL Shutdown on DB2
Set the Linux Time on DB1 and DB2 (Make sure times match)
Start MySQL Shutdown on DB2
Start MySQL Shutdown on DB1
On DB1, RESET MASTER; (Erases all binlogs again)
On DB2, CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4;
On DB2, START SLAVE;

The idea here is to make sure all binary logs and relay logs are time synchronized. The NOW() function could then be correctly interpreted from these logs.
CAVEAT
Be very careful about changing clock times when it may not be necessary when it comes to Daylight Saving Time. Since the clock time is written in binary logs in seconds since Jan 1, 1970, it may not be necessary. Since the clock is 75 minutes off, do this ASAP. Don't wait until DST kicks in.


Resources used by ExtendedEvents Does anyone know how to tell how many resources (CPU/MEM/Reads etc) have been used by SQL Server Extended Events?

I've went through the available DMVs here
But I dont see where this is tracked anywhere.

I'm asking b/c I worked on a poorly performing sql server for a few days before I realized there was an EE session running that, when killed, cut CPU utilization in half. (It was Idera Sql DM running the EE Session)

It should be more of a case support with idera. Generally these are good repuatated products in market which have a better documentation than we can explain here. I can share this based on my experience with sentry support where had similar if not exact issue. E.g if you are using idera to collect actual execution plan or events for tracking queries and their metrics comes with additional cost.

Please open a case with idera and go through their documentation for further troubleshooting.


SQL DB design to support user feeds (in application like facebook) I have a social network server with a MySql DB. I want to show the users feeds like done in Facebook. Example - UserX now Friend with userY, userX did like on postX etc.

Currently I have table: C1 : UserId C2 : LogType (now friend, did like etc) C3 : ObjectId (Can be userId or postId) - set depending on the LogType.

Currently to get all related logs to show to the user I do the following queries: 1. Get All user Friends userIds 2. Query all rows which C1 is in userIds (I query completed) 3. Scan the DB and see - if LogType equals DidLike, check if post's OwnerId is the userId - if yes add it to logs.

And so on.

Obvious this is not efficient at all.

I am looking for a better way. I thought I had in mind: Create a new table (in addition to the Log table) C1 : UserId C2 : LogId (from Log table) C3 : UserID of the one who did the action When querying logs - look in the table and get related Logs (by LogId) from LogTable.

Updating the table: Whenever user doing action that should be in the log: 1. Add the Log entry to LogTable. 2. Scan the DB and see which users are interested with the Log (Who my friends are, Who is the owner of the post) and add related entries to the new table. (must be done in BG). 3. If user UNFRIEND another user - then loko in the logs for all rows where C3 == UNFRIENDED user id and delete them.

Any opinions? Other suggestions?

Get rid of ambiguous columns like object_id.  Use log_type to determine what you are querying on.  So something like:

CREATE TABLE logs (
    log_id bigint AUTO_INCREMENT, -- not sure if this is the mysql syntax
    user_id int references users(id),
    post_id bigint references posts(id)
);

A second point is I would suggest changing the friend/unfriend to be immediate so it doesn't hit this table.  This should be only for logging actions to posts, not friendships and the like.  If you must log and propagate, create a separate table for this.