Why I cannot execute this auto generated script with mysql workbench -- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema motivian
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema motivian
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `motivian` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `motivian` ;

-- -----------------------------------------------------
-- Table `motivian`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `motivian`.`user` ;

CREATE TABLE IF NOT EXISTS `motivian`.`user` (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NULL,
  `password` VARCHAR(100) NOT NULL,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
  PRIMARY KEY (`user_id`));

-- -----------------------------------------------------
-- Table `motivian`.`calculation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `motivian`.`calculation` ;

CREATE TABLE IF NOT EXISTS `motivian`.`calculation` (
  `category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `calc` VARCHAR(100) NOT NULL,
  `user_id` INT NULL,
  `date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY ()
    REFERENCES `motivian`.`user` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

This is auto generated with mysql workbench but the constraint could not be applied. I tried like this:

CONSTRAINT user_id FOREIGN KEY (user_id)
        REFERENCESmotivian.user(user_id) ON DELETE NO ACTION ON UPDATE NO ACTION);

but also don't work.....The problem is with the constraint in the calculation table. How it should be written.
This is the error message:

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
    REFERENCES `motivian`.`user` ()
    ON DELETE NO ACTION
    ON UPDATE NO A' at line 8
SQL Code:
        CREATE TABLE IF NOT EXISTS `motivian`.`calculation` (
          `category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
          `calc` VARCHAR(100) NOT NULL,
          `user_id` INT NULL,
          `date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`category_id`),
          CONSTRAINT `user_id`
            FOREIGN KEY ()
            REFERENCES `motivian`.`user` ()
            ON DELETE NO ACTION
            ON UPDATE NO ACTION)

SQL script execution finished: statements: 8 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

the problem is with the constraint. And now I am thinking is it needed at all. I just want to have a relation with the users table but maybe the constraint is not needed when I create the table??

Your dump is somewhat weird, but your "repaired" part seems good (only missing one space, I suppose typo in question) - did it give different error? Column types do not match:

`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` INT NULL,

One is UNSIGNED and the other is not, that is one problem. Make them both either signed or unsigned (usually unsigned is the right one for autoincrement)

I just added missing space and some backticks to be safe:

CONSTRAINT `user_id` FOREIGN KEY (`user_id`)
    REFERENCES `motivian`.`user`(`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION);

Check it at http://sqlfiddle.com/#!9/9dca1/1


SQL Server 2014 - Strange SP behavior during compilation Today I noticed a strange behavior during the creation of a stored procedure in SQL Server 2014.
Below is my test procedure. Please explain why SQL Server disallows compilation with the first SELECT and allows compilation with second one.

CREATE PROCEDURE [dbo].[test]
AS
BEGIN

    SET NOCOUNT ON;

    IF 1=0  -- this will never occur
    BEGIN
        select x from Table -- TABLE EXIST BUT COLUMN NOT - STORED PROCEDURE IS NOT CREATED
        --Msg 207, Level 16, State 1, Procedure test, Line 25
        --Invalid column name 'x'.

        -- But if i change
        select x from TableWhichNotExistInDatabase  -- This table not exist in database, but stored procedure has been created without any problems and I can exec it without errors (    because of 1=0 in if )

    END
    ELSE
    SELECT ''
END

In my real procedure, I'm creating temporary tables with various schema (from XML passed as parameter). Next I'm trying to validate some data in the stored procedure. Validation blocks are controlled by IF statements.

The creation of temporary tables and the stored procedure call is executed by a dynamiclly created procedure.

I think the problem lies somewhere during the creation of the execution plan or something like that. If the table does not exist in the database, SQL Server allows the creation of the stored procedure when a table does not exist.  However, when the table exists, the creation of the stored procedure fails because of invalid schema - no column.

I was looking for a solution which disables schema checking but did not have any success.

Are there any ideas regarding how can I bypass this limitation?

The phenomenon you are experiencing is called Deferred Name Resolution.

  When a stored procedure is created, the statements in the procedure
  are parsed for syntactical accuracy. If a syntactical error is
  encountered in the procedure definition, an error is returned and the
  stored procedure is not created. If the statements are syntactically
  correct, the text of the stored procedure is stored in the
  sys.sql_modules catalog view.

  When a stored procedure is executed for the first time, the query
  processor reads the text of the stored procedure from the
  sys.sql_modules catalog view and checks that the names of the objects
  used by the procedure are present. This process is called deferred
  name resolution because table objects referenced by the stored
  procedure need not exist when the stored procedure is created, but
  only when it is executed.

  > Deferred name resolution can only be used when you reference
  nonexistent table objects. All other objects must exist at the time
  the stored procedure is created. For example, when you reference an
  existing table in a stored procedure you cannot list nonexistent
  columns for that table.

The only way I know around this problem would be to use dynamic SQL.


SQL Server Management Studio 2012 - horrible blue background I've just installed SSMS 2012 and when I loaded it up I was presented with a nasty blue background - it looks so bad that I honestly thought something was broken, but apparently it's supposed to look like that!

Short of sending the SQL Server Design Team a set of vouchers to have their eyesight tested, is there anything that I can do to change this?  I've looked around and found a couple of other people whose sight has been similarly assaulted, but I cannot find a solution.

Are you talking about the below appearance?

If that's the case, then I don't think that blue background is able to be changed.  I'm not sure why that'd be such a hang-up, as if you're staring at that background in SSMS, then you're probably not really doing anything.  Like Martin said above, the query window or other placeholders in that area will cover that completely.

When you have a query window open, it should look similar to the below screenshot:

As you can notice, you don't really see that blue background at all.  99% of the time (at least for me when I'm in SSMS), this will be the visual representation of SSMS.


Recovering from: A transport-level error has occurred when sending the request to the server in SSMS Every now and than I loss the connection in SSMS and get the message

Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

The annoying thing is, that the button to change the connection ("Verbindung ändern" for me) is inactive, and I have to open a new tab, open the connection and copy the contents of the inactive window to it.

Is there a better way to handle this situation?

In 2005 at least, hit F5 to re-run your query. SSMS will determine it is no longer connected and then prompt you to reconnect. If I recall correctly, 2005 will put you back into the same database while 2008 puts you into your default catalog. A second execution of F5 will actually run the command. I've never had a semaphore timeout but it works for other disconnect scenarios.


max_stack_depth not changed even stack changed in limits.conf The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log:

DETAIL:  "max_stack_depth" must not exceed 7680kB.
HINT:  Increase the platform's stack depth limit via "ulimit -s" or local equivalent.

actually, the below line exists in limits.conf. Have tried updating postgresql process using prlimit and even reboot the server(OS: Ubuntu). It still reports the same error.

*         soft stack   524288

if switch to the user postgres and verify the stack size with ulimit -s it is already set to 512MB.

This shows there is no ulimit -s to change the stack size under /etc.

$ sudo grep -r ulimit /etc/*
/etc/init.d/lxd:        ulimit -n 65536
/etc/vmware-tools/vm-support:runcmd "/tmp/ulimit-a.txt" ulimit -a

Not sure why postgresql still not seeing this change?

This solution works on Fedora Linux, but should work on Ubuntu as well.  The issue is, that in addition to replacing initd, systemd aims to take over security functions that were traditionally handled by things like /etc/security/limits.conf.  (There are other areas where it interferes as well).  When systemd starts PostgreSQL, it ignores /etc/security/limits.conf, and applies its own default limits.  To change them, do the following:

mkdir /etc/systemd/system/postgresql.service.d
cat > /etc/systemd/system/postgresql.service.d/postgresql.conf << XYZZY
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
LimitSTACK=infinity
XYZZY

systemctl daemon-reload

This method also has the added benefit that will not interfere with default changes to service unit files, should they be updated upstream, but will permanently maintain your settings override.  This helpful post lists the other limits that may be set through systemd configuration.

I should note that, at first, I tried to set the limit to 131072, but when I attempted to start PostgreSQL, I got this error:

DETAIL:  "max_stack_depth" must not exceed -384kB.

Changing it to infinity allowed PostgreSQL to start.


Install MariaDB 10 Generic Binaries + TokuDB I used to install MariaDB from Generic Binaries on a Debian Wheezy 64bits:

mariadb-10.0.13-linux-x86_64.tar.gz
(from downloads.mariadb.org)

I would like to use TokuDB as Storage Engine.

However, TokuDB is available in "glibc_214" generic linux package as well as .deb packages, but it is NOT available in "non glibc_214 generic linux package"...

I can't use "mariadb-10.0.13-linux-glibc_214-x86_64.tar.gz" because Debian Wheezy glibc is 2.13:

$~: ldd --version
ldd (Debian EGLIBC 2.13-38+deb7u4) 2.13

Why is TokuDB not available in standard 64bits version of Generic Binaries?

Is it safe to copy ha_tokudb.so from an debian wheezy apt install (/usr/lib/mysql/plugin/ha_tokudb.so) to a generic linux "manual" installation (in ./lib/plugin)?

If I do so, I can enable TokuDB and all seems to work (I hope so anyway...):

(none)=# INSTALL SONAME 'ha_tokudb';
Query OK, 0 rows affected (0.30 sec)
(none)=# SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
...
| TokuDB             | YES     | Tokutek TokuDB Storage Engine with Fractal Tree(tm) Technology             | YES          | YES  | YES        |
...
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)

The right place to ask this question in by filing a ticket in the MariaDB Jira or on the #mariadb IRC channel on freenode.


Data Validation before entry into Couchbase server? Is it possible to validate the data before it is entered into couchbase? I did not find any documentation confirming this. But I believe there must be some way to do it.

You can validate the input by adding another entity "validate_doc_update" to the _design/ document.The validate_doc_update function gets executed for each document you want to create or update. If the validation function raises an exception, the update is denied; when it doesn’t, the updates are accepted.
It is optional i.e. if you do not write one, no checking will be done.

More details can be found on http://guide.couchdb.org/draft/validation.html


CONCAT_WS causes "Data too long" which works partially? I've got a simple table with several columns and I'm trying to merge two of them into one. I'm working with MySQL 5.5 and I can reproduce the error one a specific data set by running the following (anonymized) query through MySQL Workbench:

UPDATE person SET a = CONCAT_WS('\n', a, b)

It clearly produces the following response message:

Error Code: 1406. Data too long for column 'a' at row 34

Because both a and b are VARCHAR(200) the response is fair, but I would expect and hope that since it produced an error, no changes would be commited. However, apparently it does. I'm not able to reproduce it on a smaller dummy dataset. I am using auto commit.

Am I simply wrong in my assumption that erronous queries don't change any values? Could it be some feature of either auto commit, or other setup of my MySQL server?

Please ask if any obvious detail is missing from my specification.

I would expect and hope that since it produced an error, no changes would be commited

The reason is in Strict SQL mode.

When error (instead of warning) occures, only the rest of rows is ignored, rows already updated do not ROLLBACKed.

See Strict SQL Mode:

  For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, because the earlier rows have been inserted or updated, the result is a partial update. To avoid this, use single-row statements, which can be aborted without changing the table.

  what do they mean by "single-row statements"?

It is a statement which affects strictly one row. By WHERE conditions and/or by LIMIT borders. If warning converted to error occures, the row in update is not altered.

  both a and b are VARCHAR(200)

This means that concatenated value can be up to 402 symbols. So use

UPDATE person
SET a = LEFT(CONCAT_WS('\n', a, b), 200); -- truncate if too long

or

UPDATE person
SET a = CONCAT_WS('\n', a, b)
WHERE LENGTH(CONCAT_WS('\n', a, b)) <= 200; -- do not update if too long


Shell: How to time a script running in SQLPlus and kill it after x amount of minutes? I have a little tool which automatically runs a series of SQL scripts when and outputs to .XLS when an appropriate request is made. However, some SQL scripts need to be ran on a live database, and I don't want them to run for over 5 minutes.

Is there a way to - within my shell script - time the script's run time and kill it after 5 minutes?

Thanks in advance.

In Oracle Database, you can use Database Resource Manager (DRM) to manage memory, CPU, time and other resources.

Usually your workflow is as follows:

Create a resource plan
Create the resource consumer groups
Create the resource consumer group mappings
Create the resource-plan directives

You create a plan with the DBMS_RESOURCE_MANAGER.CREATE_PLAN procedure. Plan name is the only required parameter:

DBMS_RESOURCE_MANAGER.CREATE_PLAN('myplan');

Then you create the resource consumer groups as needed with DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP:

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'limit_5_min'
  , comment => 'users with 5 minute execution time limit');

After the necessary resource consumer groups are created, you can define resource consumer group mappings with DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING based on the session attribute so that the new and existing sessions are automatically assigned to appropriate resource consumer groups:

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute => DBMS_RESOURCE_MANAGER.CLIENT_OS_USER
  , value => 'spongebob'
  , consumer_group => 'limit_5_min');

This mapping will assign all the session made from the OS user spongebob to the resource consumer group created earlier. There are a number of other attributes you can use to map the session to consumer groups, for example, ORACLE_USER. See DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI procedure parameters table for a full list of available attributes.

Now is the most important part. You will create the resource-plan directives that will govern the resource usage by the users. You use the DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE procedure to create the directives. Let's create the directive which limits the session execution time:

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'myplan'
  , group_or_subplan => 'limit_5_min'
  , switch_time => 300
  , switch_group => 'KILL_SESSION');

This directive means that every session created from the OS user spongebob and executes more than 300 seconds (5 minutes) will be automatically killed by DRM.

You will also need to grant the users privileges to switch to the appropriate resource consumer groups with DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP procedure, otherwise the mapping will not work:

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP(
    grantee_name => 'scott'
  , consumer_group => 'limit_5_min'
  , grant_option => false);


Can't change _log database physical location in SQL Server I just installed Microsoft ServiceBus and it created 5 databases. Now I want to move them to some specific location so I can join them to SQL Server AlwaysOn Availability group.

I stopped service bus farm and tried to execute the following queries one by one:

ALTER DATABASE [SbManagementDB] SET OFFLINE;
ALTER DATABASE [SbManagementDB_log] SET OFFLINE;

ALTER DATABASE [SbManagementDB] MODIFY FILE ( NAME = [SbManagementDB], FILENAME = 'C:\Program Files\Microsoft SQL Server\MyLocation\SbManagementDB.mdf' );
ALTER DATABASE [SbManagementDB_log] MODIFY FILE ( NAME = [SbManagementDB_log], FILENAME = 'C:\Program Files\Microsoft SQL Server\MyLocation\SbManagementDB_log.ldf' );

ALTER DATABASE [SbManagementDB] SET ONLINE;

I brought SbManagementDB offline successfully, but when I tried to do that to SbManagementDB_log I've got the following error:

Msg 5011, Level 14, State 5, Line 2
User does not have permission to alter database 'SbManagementDB_log', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.

I executed the following query SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files and made sure that this DB exists and I tried sa login, but got the same error.

Any ideas what am I doing wrong?

You need to do several steps to modify the location of a database's files.

Alter the catalog entries for the database in question using the `ALTER DATABASE' command
Take the database itself offline.
Move the physical files to their new location
Bring the database back online.

For instance, for the SbManagementDB database, you'd do something like:

ALTER DATABASE SbManagementDB
MODIFY FILE (
    NAME = 'SbManagementDB'
    , FILENAME = 'F:\path\to\new\location\SbManagementDB.mdf'
);

ALTER DATABASE SbManagementDB
MODIFY FILE (
    NAME = 'SbManagementDB_Log'
    , FILENAME = 'F:\path\to\new\location\SbManagementDB.ldf'
);

ALTER DATABASE sbManagementDB SET OFFLINE;

Now you physically move the files, probably using Windows Explorer, into F:\path\to\new\location, and bring the database back online.

ALTER DATABASE sbManagementDB SET ONLINE;

In your question, you are attempting to take database files offline, which is not necessary, and causing you grief.


Why is it not possible to "SELECT" Data as parameters for certain clauses like LIMIT? EXAMPLE:

CREATE TABLE t (A INT);
INSERT INTO t VALUES(1),(2),(3),(4),(5);

This works:

SELECT A FROM t WHERE A=(SELECT 1)

This does not:

SELECT A FROM t LIMIT (SELECT 1);

This works:

SELECT A FROM t WHERE A=substring((SELECT 123),1,1);

This does not:

SELECT A FROM t WHERE A=1 PROCEDURE ANALYSE((SELECT 1),10000);

What makes some clauses and functions support SELECT statements and others not support it. I would think that running (SELECT 1) returns a 1 as a parameter and as long as the parameter is the expected type it should work. This seems to be true in some cases, but not others. Why is that?

this is the way LIMIT can be done in MySQL using variable parameters:

SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;

In the place where it says SET @skip=1, One can also write SET @skip=(SELECT 1).


PostgreSQL Replication for small, low-usage database I have PostgreSQL 8.4 installed on a headless Ubuntu 10.04 server. Sometimes the server is not available. I would like to continue working on the database, so I thought of installing PostgreSQL onto my laptop. I'm familiar with PostgreSQL, but not with replication. Would I setup replication so that the two databases are kept synchronized when the server becomes available again? This does not need to be automated. What is the easiest and simplest approach? The database is not used extensively by others at this stage and is small, so a simple, lightweight solution would be adequate.

For reference, one of these may be suitable:

Bucardo
Londiste
RubyRep
Slony

A general discussion of replication and clustering in PostgreSQL is here.

There are also many questions on StackOverflow and this useful one on ServerFault.


Does mariadb `NO_TABLE_OPTIONS`, `NO_FIELD_OPTIONS`, `NO_KEY_OPTIONS SQL_MODE` change database content? From https://mariadb.com/kb/en/library/show-create-table/,
I read

  MariaDB and MySQL-specific table options, column options, and index options are not included in the output of this statement if the NO_TABLE_OPTIONS, NO_FIELD_OPTIONS and NO_KEY_OPTIONS SQL_MODE flags are used.

I am not sure NO_TABLE_OPTIONS, NO_FIELD_OPTIONS and NO_KEY_OPTIONS SQL_MODE change only the grammar or change database content.
Is database resource from mysqldump --NO_TABLE_OPTIONS --NO_FIELD_OPTIONS --NO_KEY_OPTIONS SQL_MODE --databases db1 same as resource from mysqldump --databases db1?

mysqldump reads the database, but does not write to it.  It writes a text file you can look at.

You will find that those options remove certain settings relating character set, engine, auto_increment, etc.  These may make the generated SQL more compatible with some other database vendor or MySQL/MariaDB version.

The syntax I found was more like

mysqldump --compatible=no_table_options,no_field_options,no_key_options ...


Automatically create ER diagram from database schema I have a database with 193 tables and I need to create an entity-relationship diagram from them. There are no formal relationships between the tables with respect to FKs. The relationships are mapped with 1-* tables.

The database in question is the MusicBrainz database found here.

SchemaSpy doesn't detect the relationships.

Other than myself creating it manually does anyone have any suggestions. Or is anyone familiar with this database that could offer some assistance?

The relationship between tables in any relational database is done using Foreign Keys.

From the Wikipedia definition:

  Foreign keys play an essential role in database design. One important part of database design is making sure that relationships between real-world entities are reflected in the database by references, using foreign keys to refer from one table to another.

Without FKs there will be no way for any schema modelling tool of knowing that two tables are related.

A solution might be for you to script out FKs by parsing the names of the tables (if the related tables have similar names), then names and data types of columns that define the relationship. But that's no trivial task.


SQL Server Advice for an old hack Evening guys and gals, I am hoping some of you legends might be able to be help me out here :)

I last used SQL Server back on version 7 back in the dark ages (we rubbed two sticks together to get it going). Now here I am in in the 21st century and would like to come back to DBA work again.

I would like to setup a 'cheap' virtual machine & instance to play with, then work towards the newest certification (I see the newest will commence April 2017 which is good timing for me I think). I am particularly interested in the BI side of things.

Can anyone recommend a good hosting provider for a virtual machine? Do I simply get a Windows Server and go from there or can I get a package somewhere? Azure I assume or elsewhere? I suspect there are a number of options, hoping you can share some good ones :)

Plus any other helpful tips are most welcome, cheers!

The easiest solution is actually free: Microsoft's Virtual Labs. You get access to SQL Servers in the cloud, plus step-by-step instructions on what to experiment with. (You aren't required to do their tutorials, either - you can use the VMs for anything you like.)

Keep in mind that there's a few restrictions: you can't surf the web from there, and you can't copy/paste things in. Because of that, it's a little limited - but you can't beat free.

If you're going to run your own SQL Server, I wouldn't recommend doing it in the cloud since you pay by the hour. You're better off using a free virtualization tool like Hyper-V, ESXi, or VirtualBox, and then installing the totally free SQL Server Developer Edition. Good luck on your learning journey!


postgres wal sender replication timeout during pg_basebackup Let me start with the caveat that I am still green with Postgres.

I am working on a postgres 9.2 Active/Standby cluster on Debian wheezy for an application, based off of the ClusterLabs pgsql cluster documentation.

In the lab I am able to get this working without a problem.  But on the production cluster I'm building, I keep running into a problem.

I brought the database files over from the current single production postgres server.  By this I mean I shutdown postgres and tar-ed up the data directory and copied it over the the cluster's Master node. I put the files in place, set the permissions, and was able to start-up postgres on the Master via corosync just fine.

In preparing the slave, I used the pg_basebackup tool to bring the database over from the Master and this is where I keep having issues. As it is transferring, at about 57% I see the error:

$ pg_basebackup -h db-master -U u_repl -D /db/data/postgresql/9.2/main/ -X stream -P
pg_basebackup: could not receive data from WAL stream: SSL connection has been closed unexpectedly
176472/176472 kB (100%), 1/1 tablespace
pg_basebackup: child process exited with error 1`

And on the server, I see:

2016-04-06 21:05:31 UTC LOG:  terminating walsender process due to replication timeout

But the transfer doesn't stop and keeps going to completion.

I found this question here on stackexchange about setting "ssl_renegotiation_limit" to 0, but this didn't make much difference.

Anyone have any ideas?  I am completely baffled as to why this would error, but keep on going just fine.  It is the same procedure I used in the lab setup... the only difference is that the production database is much bigger in size.

Thoughts??
Thank you kindly!  -Peter.

Many thanks to Albe Laurenz from the pgsql-admin mailing list.

The server error message means that the client did not send a status update within wal_sender_timeout milliseconds, see documentation.

The basebackup needs to complete before this wal_sender_timeout period, else the server resets the connection.

Side note, I am running 9.2 so this parameter is called replication_timeout in the older version.


SSMS 2016 takes forever to bring up context menu for an index Go to a random table in the Object Explorer
Expand it down to indexes
Right-click on an index - it takes around 10 seconds to come up

If I attempt to create a DROP & CREATE INDEX script off the context menu, it takes  a similar amount of time.

I've looked at the Profiler trace, and it has about 4 pages of largely repeating queries.  If I look at the Performance/Ethernet tab of the Task Manager, i see that it is bringing information at a rate of 800kb-1mb and sending at about 200kb or so.

Is this a bug?  Feature?  Am I missing some simple setting?

Version of SSMS: 13.0.15900.1.
Updated SSMS to 13.0.16000.28 with no effect.
Version of SQL Server: 2016 with cumulative update 2.
I had SQL Prompt 7 installed.  Uninstalled it, but nothing changed.
SELECT COUNT(*) FROM sys.indexes; returns 1979.
SELECT COUNT(*) FROM sys.partitions; returns 1973.

I have the box all to myself, so there is no contention.  Plus this box is the fastest, best spec'd box I've ever worked on.  Everything is snappy on it except this specific operation.

I fixed this by changing my authentication method from "Windows Authentication" to "SQL Server Authentication".

I have no idea why.

Before this change i had to wait up to 1 minute for context menu's to appear.

After change context menus are instant.


Postgres psql requires " FROM database.table" in sql statement As a beginning learner of of Postgres I have created two different installations of a database where one requires a select statement to use ...FROM database.table... and the other only requires ...FROM table.... This was discovered in a Symfony project that could point to either installation. The project fails when pointing to the installation requiring ...FROM database.table... How can the requirement to include the database name with a table name be removed, and where might that requirement have come from?
Background:
The project began with a MySQL database. Since I wanted to learn Postgres I followed some great material found online to convert a MySQL database to Postgres. The MySQL data was in Windows, Postgres was installed on an Ubuntu 18 VM. (The conversion created the Postgres version on the VM.) The VM's database was dumped with pg_dump, the resulting sql file copied to Windows and restored there. All of this in MySQL 8 & Postgres 12.
When the Symfony project points to the VM's database, all is fine and dandy. Pointing to the Windows copy fails because it cannot find any given table.
On the VM I can use psql to query using ...FROM table.... In Windows with psql, even though SELECT current_database(); returns the name of the database, I must use ...FROM database.table... in a query.

....FROM database.table

That is not a database, that is a schema.  (With the weird and confusing name of "database").
You can refer to things with 3 name components, databasename.schemaname.tablename, but this is rarely done as then the first component must be the same as the name of the database you are connected to.  Which makes it pretty useless, other than as a way to maintain compatibility with other database software.
In one database, your tables are in a schema which is in the search_path, and in the other database they are not.  Maybe that is because the search_path differs between databases, or maybe because the tables are actually in differently named schemas.  You can use show search_path to see what search_path is in effect in each database.


select document based on date range Structure

CREATE TABLE `template` (
    `name` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `valid` DATE NULL DEFAULT NULL,
    UNIQUE INDEX `name` (`name`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

Data

I am trying to select right template name based on date. "Valid" column represents date till template is valid. If Template have 0000-00-00 or NULL valid date, that means that this template is current (latest). Template names does not suggest order (they can be random string).

Scenario:

If I have user, that will be created let's say 2020-01-01, then select should pick template with name "yes_4".

Scenario:

User was created in 2018-01-01, then template selected should have name "agreement_x"

Scenario:

User created in 2019-01-15 will select row with name "template_2"

So far:

:date = - user created

query

SELECT * FROM `template`
WHERE DATE(`valid`) < DATE(:date)
ORDER BY DATE(`valid`) DESC
LIMIT 1

Something like

(   SELECT *
    FROM `template`
    WHERE DATE(`valid`) >= DATE(:date)
    ORDER BY DATE(`valid`)
    LIMIT 1
)
UNION ALL
(   SELECT *
    FROM `template`
    WHERE DATE(`valid`) <= '1970-01-01' OR `valid` IS NULL
)
ORDER BY `valid` DESC
LIMIT 1

or

SELECT *
FROM `template`
WHERE CASE WHEN DATE(`valid`) > '1970-01-01'
           THEN `valid`
           ELSE '2038-01-19'
      END >= DATE(:date)
ORDER BY CASE WHEN DATE(`valid`) > '1970-01-01'
              THEN `valid`
              ELSE '2038-01-19'
         END
LIMIT 1

and so on...


Is "many database writes" an anti-pattern? I am designing a database api. I have two options:

Have a complex aggregation that queries the data as desired or,
Create a materialized view that logs all data changes in an easy to query manner and potentially makes querying this data faster than querying data from option 1.

I was wondering is having many writes an anti-pattern in database design and administration? Because if not I would love to use option 2.
I am using a no-SQL database; MongoDB.

There are no free lunches. If there is work to be done it has to be done at some point. The question is when is it best for the system as a whole to perform the work. This depends on the total workload and access patterns.
For write-mostly / read-seldom patterns, such as logs, it makes sense to optimize for write throughput. Since most data is never read it would be a waste of resources to perform additional processing to optimize for future reads which likely will never happen.
For write-once / read-many patterns, such as e-commerce, it makes sense to do the additional work once on write rather than perform it redundantly for every read.
There are many gradations between these two extremes. Perhaps the initial write is latency sensitive? It may be better to enqueue a request to perform the additional processing asynchronously. And so on.
Be aware that duplicating values (and aggregates do duplicate data) introduces risks. There will be a finite latency between the detailed row being written and the aggregates being updated. Other processes may be sensitive to this and report inconsistent results. The aggregates can become a concurrency bottleneck as they are touched may many activities, by definition. Every program ever written against either source has to consider the existence of the other values, too.


How much should I care about prepared statements for simple queries? I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements.

The question originates from the fact that I recently noticed ActiveRecord (a Ruby ORM) writes different SQL queries depending on the way I chain the commands. Here's an example.

2.1.1 :004 > Account.first.domains.explain
  Account Load (0.5ms)  SELECT "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT 1
  Domain Load (0.8ms)  SELECT "domains".* FROM "domains" WHERE "domains"."account_id" = $1  [["account_id", 1]]
 => EXPLAIN for: SELECT "domains".* FROM "domains"  WHERE "domains"."account_id" = $1 [["account_id", 1]]
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on domains  (cost=0.00..1.35 rows=1 width=222)
   Filter: (account_id = 1)
(2 rows)

vs

2.1.1 :005 > Domain.for_account(Account.first).explain
  Account Load (0.7ms)  SELECT "accounts".* FROM "accounts" ORDER BY "accounts"."id" ASC LIMIT 1
  Domain Load (0.9ms)  SELECT "domains".* FROM "domains" WHERE "domains"."account_id" = 1
 => EXPLAIN for: SELECT "domains".* FROM "domains"  WHERE "domains"."account_id" = 1
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on domains  (cost=0.00..1.35 rows=1 width=222)
   Filter: (account_id = 1)
(2 rows)

As you can see, the first statement generates a prepared statement, whereas the second one does not.

Is there a truly benefit in using prepared statements for such simple queries, or generally speaking the benefits I get in these cases are small enough to not justify major refactoring or workaround at code level?

The difference is negligible much of the time. You'll see significant benefits if:

There are lots of joins or lots of predicates; or
The query contains long IN lists or similar large data elements

In general, unless it's a "hot" code path where you really hammer a particular query   I wouldn't make much effort optimising to focus on re-usable prepared statements.

(You should still parameterise your SQL rather than substituting parameters directly into the text, and people often confuse that with prepraed statements, but you seem to understand the difference.)


Boolean check on fields returning any of them except if all have same value Assuming we have 4 boolean fields a,b,c,d

How can we return any of the fields that are true, but not nothing when all 4 are true?

where a=true or b=true or c=true or d=true

will return a row where even all 4 fields are set tot true. How can I exclude this row from the query?

Same would apply if searched value is false

You can use something like this:

where (true in (a,b,c,d)
       and (a,b,c,d) <> (true,true,true,true));

Which can be simplified (suggested by ypercubeᵀᴹ) to:

where true in (a,b,c,d) and false in (a,b,c,d)

Note that it will not handle NULL values properly. If you want that as well, you will need to use coalesce().


Login Schema Role User Setup Environment:

Windows Server 2012
SQL Server 2014 Standard
Public website (Site-A) on Server
Private website (Site-B) on Server
Windows App running on workstations

SQL Server 2014 Standard:
2 databases - DB-A & DB-B

Class Library:
Handles get/set of all data via stored procedures. Data is on DB-A & DB-B

Site A:
Public facing eCommerce ASP.NET website (http://www.rackattack.com) using a class library to interface with DB-A & DB-B. Contains additional calls (via Stored Procedure) to both DBs

Site B:
Private ASP.NET website using a class library to interface with DB-A & DB-B. Contains additional calls (via Stored Procedure) to both DBs

Windows App:
In house app that uses class library to interface with DB. Contains additional calls (via Stored Procedure) to both DBs.

Question: How do I want to set up the Server Logins, Database Schemas, Database Roles, Database Users to comply with best practices for our Environment?

My Thoughts:

Server Login for Site-A, Site-B, WinApp
DB Role for ClassLib - permission to call stored procs for Lib
DB Role for Site-A - permission to call stored procs for Site-A on DB-A & DB-B
DB Role for Site-B - permission to call stored procs for Site-B on DB-A & DB-B
DB Role for WinApp - permission to call stored procs for WinApp on DB-A & DB-B
DB User for Site-A (member of Role for ClassLib & Site-A)
DB User for Site-B (member of Role for ClassLib & Site-B)
DB User for WinApp (member of Role for ClassLib & WinApp)

Question:

Does this set up make sense?
Do I want separate roles for the ClassLib and the Site users?
Where do I add the premissions to execute the list of SPs in need, in
the role or should I be using schema for this as well? If so, how?

Disclaimer: I am a developer and not a DB admin. I have a basic understanding of the concepts but please "Explain like I'm 5". Thank you!

OK, so I'm just going to go off general best practices here and give you some advice on what I would do and what I have seen implemented in the past. I am also going to assume that you have sufficient security protocols in place to protect your public-facing website.

Firstly, be willing to lose everything in the public database if you get hacked (obviously back it up regularly but don't be surprised if it gets taken down).

Secondly, the database that runs your website needs be isolated. Everything that you need to run it needs to be independent and generally should not be linked via a library directly to a private database. If there are components in the private database that the public website requires then you have two options:

Duplicate these components so that they exist in both databases. (Preferred)
Create a "shared" database that contains only these components.

Once you have your public database isolated you immediately reduce the amount risk that you need to mitigate. If you need information from the public database, or need to get information into your public database from your private database then you could set up replication or some other sort of schedule to perform this task.

If you cannot separate your databases then you will need to be absolutely militant about what permissions are granted to your public facing website/data library. Be prepared to document a lot!

If you create roles, then you need to ensure that each role has the minimum permission required to complete the task assigned to it. For instance, your class library that is shared between the two sites, give it the least permission possible that enables it to run.

Any user accounts created for the public website should have access to the public database and nothing more. If the public website has direct access to your private database then it should be considered public as well even if that was not your intention.

Again, your windows application, should not call directly into your public facing database. This is to reduce the surface area of attack and also to ensure that someone does not unintentionally do something that takes your public website down (like deleting all content).

So to summarize:

Public website should only connect to public database and nothing that is deemed internal or private.
Private website and applications should not connect to the public database.
Shared components/tables should be present in both databases and replicated between them.
Permissions should be the absolute minimum to complete the task they are performing, nothing more nothing less.
Don't use code components in your public website that can access and amend private data.
Be prepared to lose everything.
Never, ever give the public website SA or equivalent. Everything it needs to do should be explicitly granted to it.

If you isolate your public database then assigning permissions to your internal applications become trivial and easy to do.


Known vulnerabilities for when a guest executes a stored procedure on SQL Server I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How secure is it if I let the other company's IT department execute stored procedures on our database server? They would have to know the database name, address, procedure name and of course I could set them a new login. My worries are, are there "easy" ways of bypassing security and accessing other data in the database?

I would first question why they need direct access to the database. You might ask your manager or legal department if the security policy for the company allows granting this type of access. Is it really needed if they are just going to execute a stored procedure on a regular basis.

As you stated this is going to be on a regular basis I would setup an SSIS package to export the information to a file. Then either have the package email the file (if not to large) or put it on a UNC directory for them to come and download themselves. If they want to determine when the data is pulled down setup a script for them to execute outside of SQL Server that simply runs the package.


Query challenge: Creating even sized buckets, based on a measure not row count I'll describe the problem in terms of loading a fixed number of trucks with orders, as evenly as possible.

Inputs:

@TruckCount - the number of empty trucks to fill

A set:

OrderId,
OrderDetailId,
OrderDetailSize,
TruckId (initially null)

Orders are composed of one or more OrderDetails.

The challenge here is to assign a TruckId to each record.

A single order cannot be split across trucks.

Trucks should be as evenly* loaded as possible, measured by sum(OrderDetailSize).

* Evenly: The smallest achievable delta between the least loaded truck and most loaded truck. By this definition, 1,2,3 is more evenly distributed than 1,1,4. If it helps, pretend you are stats algorithm, creating even height histograms.

There is no consideration for maximum truck load. These are magic elastic trucks. The number of trucks however is fixed.

There’s an obviously solution which is iterative - round robin allocate orders.

But can it be done as set based logic?

My main interest is for SQL Server 2014 or later. But set based solutions for other platforms could also be interesting.

This feels like Itzik Ben-Gan territory :)

My real world application is distributing a processing workload into a number of buckets to match the number of logical CPUs. Hence each bucket has no maximum size. Stats updates, specifically. I just thought it was more fun to abstract the problem into trucks as a way of framing the challenge.

CREATE TABLE #OrderDetail (
OrderId int NOT NULL,
OrderDetailId int NOT NULL PRIMARY KEY,
OrderDetailSize tinyint NOT NULL,
TruckId tinyint NULL)

-- Sample Data

INSERT #OrderDetail (OrderId, OrderDetailId, OrderDetailSize)
VALUES
(1  ,100    ,75 ),
(2  ,101    ,5  ),
(2  ,102    ,5  ),
(2  ,103    ,5  ),
(2  ,104    ,5  ),
(2  ,105    ,5  ),
(3  ,106    ,100),
(4  ,107    ,1  ),
(5  ,108    ,11 ),
(6  ,109    ,21 ),
(7  ,110    ,49 ),
(8  ,111    ,25 ),
(8  ,112    ,25 ),
(9  ,113    ,40 ),
(10 ,114    ,49 ),
(11 ,115    ,10 ),
(11 ,116    ,10 ),
(12 ,117    ,15 ),
(13 ,118    ,18 ),
(14 ,119    ,26 )

--> YOUR SOLUTION HERE

-- After assigning Trucks, Measure delta between most and least loaded trucks.
-- Zero is perfect score, however the challenge is a set based solution that will scale, and produce good results, rather
-- than iterative solution that will produce perfect results by exploring every possibility.

SELECT max(TruckOrderDetailSize) - MIN(TruckOrderDetailSize) AS TruckMinMaxDelta
FROM
(SELECT SUM(OrderDetailSize) AS TruckOrderDetailSize FROM #OrderDetail GROUP BY TruckId) AS Truck

DROP TABLE #OrderDetail

My first thought was

select
    <best solution>
from
    <all possible combinations>

The "best solution" part is defined in the question - the smallest difference between the most loaded and least loaded trucks. The other bit - all combinations - caused me pause for thought.

Consider a situation where we have three orders A, B and C and three trucks. The possibilities are

Truck 1 Truck 2 Truck 3
------- ------- -------
A       B       C
A       C       B
B       A       C
B       C       A
C       A       B
C       B       A
AB      C       -
AB      -       C
C       AB      -
-       AB      C
C       -       AB
-       C       AB
AC      B       -
AC      -       B
B       AC      -
-       AC      B
B       -       AC
-       B       AC
BC      A       -
BC      -       A
A       BC      -
-       BC      A
A       -       BC
-       A       BC
ABC     -       -
-       ABC     -
-       -       ABC

Table A: all permutations.

Many of these are symmetric. The first six rows, for example, differ only in which truck each order is placed. Since the trucks are fungible these arrangemets will produce the same outcome. I shall ignore this for now.

There are known queries for producing permutations and combinations. However, these will produce arrangements within a single bucket. For this problem I need arrangements across multiple buckets.

Looking at the output from the standard "all combinations" query

;with Numbers as
(
    select n = 1
    union
    select 2
    union
    select 3
)
select
    a.n,
    b.n,
    c.n
from Numbers as a
cross join Numbers as b
cross join Numbers as c
order by 1, 2, 3;

  n   n   n
--- --- ---
  1   1   1
  1   1   2
  1   1   3
  1   2   1
 <snip>
  3   2   3
  3   3   1
  3   3   2
  3   3   3

Table B: cross join of three values.

I noted the results formed the same pattern as Table A. By making the congnitive leap of considering each column to be an Order1, the values to say which truck will hold that Order, and a row to be an arrangement of Orders within trucks. The query then becomes

select
    Arrangement             = ROW_NUMBER() over(order by (select null)),
    First_order_goes_in     = a.TruckNumber,
    Second_order_goes_in    = b.TruckNumber,
    Third_order_goes_in     = c.TruckNumber
from Trucks a   -- aka Numbers in Table B
cross join Trucks b
cross join Trucks c

Arrangement First_order_goes_in Second_order_goes_in Third_order_goes_in
----------- ------------------- -------------------- -------------------
          1                   1                    1                   1
          2                   1                    1                   2
          3                   1                    1                   3
          4                   1                    2                   1
  <snip>

Query C: Orders in trucks.

Expaning this to cover the fourteen Orders in the example data, and simplifying the names we get this:

;with Trucks as
(
    select *
    from (values (1), (2), (3)) as T(TruckNumber)
)
select
    arrangement = ROW_NUMBER() over(order by (select null)),
    First       = a.TruckNumber,
    Second      = b.TruckNumber,
    Third       = c.TruckNumber,
    Fourth      = d.TruckNumber,
    Fifth       = e.TruckNumber,
    Sixth       = f.TruckNumber,
    Seventh     = g.TruckNumber,
    Eigth       = h.TruckNumber,
    Ninth       = i.TruckNumber,
    Tenth       = j.TruckNumber,
    Eleventh    = k.TruckNumber,
    Twelth      = l.TruckNumber,
    Thirteenth  = m.TruckNumber,
    Fourteenth  = n.TruckNumber
into #Arrangements
from Trucks a
cross join Trucks b
cross join Trucks c
cross join Trucks d
cross join Trucks e
cross join Trucks f
cross join Trucks g
cross join Trucks h
cross join Trucks i
cross join Trucks j
cross join Trucks k
cross join Trucks l
cross join Trucks m
cross join Trucks n;

Query D: Orders spread over trucks.

I choose to hold the intermediate results in temporary tables for convenience.

Subsequent steps will be much easier if the data is first UNPIVOTED.

select
    Arrangement,
    TruckNumber,
    ItemNumber  = case NewColumn
                    when 'First'        then 1
                    when 'Second'       then 2
                    when 'Third'        then 3
                    when 'Fourth'       then 4
                    when 'Fifth'        then 5
                    when 'Sixth'        then 6
                    when 'Seventh'      then 7
                    when 'Eigth'        then 8
                    when 'Ninth'        then 9
                    when 'Tenth'        then 10
                    when 'Eleventh'     then 11
                    when 'Twelth'       then 12
                    when 'Thirteenth'   then 13
                    when 'Fourteenth'   then 14
                    else -1
                end
into #FilledTrucks
from #Arrangements
unpivot
(
    TruckNumber
    for NewColumn IN
    (
        First,
        Second,
        Third,
        Fourth,
        Fifth,
        Sixth,
        Seventh,
        Eigth,
        Ninth,
        Tenth,
        Eleventh,
        Twelth,
        Thirteenth,
        Fourteenth
    )
) as q;

Query E: Filled trucks, unpivoted.

Weights can be introduced by joining to the Orders table.

select
    ft.arrangement,
    ft.TruckNumber,
    TruckWeight = sum(i.Size)
into #TruckWeights
from #FilledTrucks as ft
inner join #Order as i
    on i.OrderId = ft.ItemNumber
group by
    ft.arrangement,
    ft.TruckNumber;

Query F: truck weights

The question can now be answered by finding the arrangement(s) which have the smallest difference between most-loaded and least-loaded trucks

select
    Arrangement,
    LightestTruck   = MIN(TruckWeight),
    HeaviestTruck   = MAX(TruckWeight),
    Delta           = MAX(TruckWeight) - MIN(TruckWeight)
from #TruckWeights
group by
    arrangement
order by
    4 ASC;

Query G: most balanced arrangements

Discussion

There are a great many problems with this. First it is a brute-force algorithm. The number of rows in the working tables is exponential in the number of trucks and orders. The number of rows in #Arrangements is (number of trucks)^(number of orders). This will not scale well.

Second is that the SQL queries have the number of Orders embedded in them. The only way around this is to use dynamic SQL, which has problems of its own. If the number of orders is in the thousands there may come a time when the generated SQL becomes too long.

Third is the redundancy in the arrangements. This bloats the intermediate tables increasing runtime hugely.

Fourth, many rows in #Arrangements leave one or more trucks empty. This cannot possibly be the optimum configuration. It would be easy to filter out these rows upon creation. I've chosen not to do so to keep the code simpler and focused.

On the up side this handles negative weights, should your enterprise ever start shipping filled helium baloons!

Thoughts

If there were a way to populate #FilledTrucks directly from the list of trucks and Orders I think the worst of these concerns would be managable. Sadly my immagination stumbled on that hurdle. My hope is some future contributor may be able to supply that which eluded me.

1 You say all items for an order must be on the same truck. This means the atom of assignment is the Order, not the OrderDetail. I have generated these from your test data thus:

select
    OrderId,
    Size = sum(OrderDetailSize)
into #Order
from #OrderDetail
group by OrderId;

It makes no difference, though, whether we label the items in question 'Order' or 'OrderDetail', the solution remains the same.


Starting a database on a replica server We had a oracle database instance on a server A. We created a replica of server A named B. Where all the files, directory structure and  environment variables were replicated on server B. I wanted to ask if I can directly start the database on the replica server or I need to do some pre-requistes to start the database on the replica server B.

There should be no problem to start the database. There are however some things to consider:

The databases have both the same name, unique name, and DBID. This can cause problems if you want to back them both up by RMAN.
The configuration of sqlnet will have the name server A. Therefore the database might not be accessible from another server.

So change the DBID and unique name (Check the documentation on these subjects) and check the configuration files of sqlnet (normally in the $ORACLE_HOME/network/admin directory) for references to server A.


A join aggregate behaviour I can't understand Query 1:

 SELECT c.class_id,
       SUM
       (
        CASE
         WHEN s.student_id IS NULL THEN 0
         ELSE 1
        END
       ) as total_students
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id;

result:

453 100
461 10
462 19
464 0
471 0
499 133

great !

Query 2: (added where condition to filter student types)

 SELECT c.class_id,
       SUM
       (
        CASE
         WHEN s.student_id IS NULL THEN 0
         ELSE 1
        END
       ) as total_students
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id WHERE s.student_type='instate'
GROUP BY c.class_id;

result:

453 89
461 5
462 19
499 43

All the figures are right, except the ones with 0 counts vanish :!

why is that ?

The WHERE clause is checking s.student_type='instate'.

In a LEFT JOIN, everything on the right side is NULL, so the WHERE clause would not evaluate to anything.

If I may, I'd like to suggest a slight rewrite of your query:

Instead of this

SELECT c.class_id,
       SUM
       (
        CASE
         WHEN s.student_id IS NULL THEN 0
         ELSE 1
        END
       ) as total_students
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id;

write it more compact like this

SELECT c.class_id, SUM(1-ISNULL(s.student_id)) as total_students
FROM classes c LEFT JOIN students s USING (class_id)
GROUP BY c.class_id;

Now, if you would like to filter on student_type and have the zeros to come back, you must rewrite the query like this:

SELECT
    A.class_id,
    IFNULL(B.total_students,0) total_students
FROM classes A LEFT JOIN
(
    SELECT c.class_id, COUNT(s.student_id) as total_students
    FROM classes c INNER JOIN students s USING (class_id)
    WHERE s.student_type='instate'
    GROUP BY c.class_id
) B USING (class_id);

UPDATE 2012-03-08 13:11 EST

Now I am curious

This is your query

SELECT class_id,
(
    SELECT count(*) FROM students a
    WHERE a.class_id=e.class_id
    AND a.student_type='instate'
) FROM classes e WHERE class_account='92';

Please Try This One

SELECT
    COUNT(DISTINCT e.class_id)
FROM
    (SELECT class_id FROM classes WHERE class_account='92') e
    INNER JOIN
    (SELECT class_id FROM students WHERE student_type='instate') a
    USING (class_id)
;

Make sure you have the following indexes before running this new query:

ALTER TABLE classes ADD INDEX (class_account,class_id);
ALTER TABLE students ADD INDEX (student_type,class_id);


Reporting Services will not connect to SQL Server I have set up a new SQL Server 2014 default instance.  In SQL Server 2014 Configuration manager I see my 32 and 64 bit SQL Native Client 11.0 configurations and all protocols, pipes, tcp/ip,shared memory  are enabled for them all.  When I try to make a new data source to my SQL Server 2012 instance, everything is fine.  When I try to make a connection to my SQL Server 2008 R2 server, I get the generic:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have used Microsoft SQL Server driver with connection strings:

data source = "cha-sql-01"; initial catalog = Paymentbanc
data source = 'cha-sql-01'; initial catalog = Paymentbanc
data source = "cha-sql-01"; initial catalog = Paymentbanc;
data source = 'cha-sql-01'; initial catalog = Paymentbanc;

I checked the SQL Server 2008 R2 server and all protocols were enabled.  The browser was off, so I turned on the browser, made sure that sqlcmd -L saw the server, which it did, then tried and got the same results.

I then tried to connect via IP address:

data source =x.x.x.x,1433;Initial Catalog=xxxxx;User ID=xxxxx;Password=xxxxxxx;
Also tried a domain account

Next, I thought the issue was that SQL Server 11.0 would not be backwards compatible, so I loaded the SQL Client 10.0 client on the reporting server.  I went into ODBC sources, added the client 10.0 and configured it for my SQL Server 2008 R2 server.

In ODBC administrator, I can use both SQL Server and SQL Server Native Client 10.0 to connect to the server.  So, what am I missing to get the reporting service SQL Server client to connect?  No errors other than the teradata & SQLPDW

This is a brand new server on Windows 2012 R2 running SQL Server Standard Edition 2014 SP1.

Community Wiki answer to document the solution added by the question author in a comment:

The old SQL Server was listening on a port other than 1433.

Once I used the IP connection string with the right port, all was well.


Extract time from date into new column I have a varchar column named [Dato Time] which contains values like:

2011-05-12 00
2011-05-12 01
2011-05-12 02

So, a date and a Hour-specific time.

I would like to split the date and the time up in two columns, a column named [Dato] with datatype date and a column named Time with datatype time(7).

I extracted the date-part with the following substring:

UPDATE Dato_Time SET [Dato] = SUBSTRING([Dato Time], 1, 10)

This worked without any problem. But when I try to run the same code for the time-part:

UPDATE Dato_Time SET [Time] = SUBSTRING([Dato Time], 12, 13)

It gives me the following error:

  Conversion failed when converting date and/or time from character string.

Any ideas?

Since Char 12 and 13 seem to be hours, with SQL Server 2012, you can build a new time using this part and 0 for missing parts:

  TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Select TIMEFROMPARTS(SUBSTRING([Dato Time], 12, 13), 0, 0, 0, 0)

MSDN: TIMEFROMPARTS (Transact-SQL)

With SQL Server prior to 2012, you can use such queries:

Select DATEADD(HOUR, Cast(SUBSTRING(@x, 12, 13) as int), CAST('00:00' as time))
Select Cast(SUBSTRING(@x, 12, 13)+':00' as time)


single table or different tables I am very new to PHP & Mysql. I am currently working on database structure of website. I have been a keen follower of this website. I have found that many posts have discouraged use of multiple tables. Now, suppose i have a product that has about 10 pictures associated with it and about 50 reviews. Now, should I have a separate tables for pictures and reviews or should i let all of them stay in the same table ?

Please have a look at the basics of Database normalization. In a very general way, 1:N relationships usually end up being represented as 2 tables. That is, if we have several products, and each product can have several reviews, we would create two tables, one for products and another for reviews, and we would link the reviews by using a product_id key (containing the primary key of the product). Same would apply for images, assuming there is a 1:N relationship with product and not a 1:1 relationship with review.

When querying for data, we need to join both tables for obtaining full results.

Please note that sometimes a non-normal form can be used, merging several tables in one. This sacrifices maintainability (potential duplicate data problems, among others, forcing extra work outside of the database scope) for performance, but that is a question of optimisation, not design phase.


select subquery condition in where statement I am getting this error:

  ORA-06550: line 43, column 16: ORA-00904: "STOCK_BALANCE": invalid identifier

...for the following query:

select PARTY_ID,
       PARTY_NAME,
       IT_ID,
       IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
       (select SUM(BALANCE)
          FROM (
            select
             WEAVING_YARN_TR.TR_ID,
             WEAVING_YARN_TR.GP_NO,
             WEAVING_YARN_TR.YR_ID,
             WEAVING_YARN_GATEPASS.TO_FROM as PARTY,
             WEAVING_YARN_GATEPASS.GP_DATE,
             WEAVING_YARN_TR.BAGS_IN,
             WEAVING_YARN_TR.BAGS_OUT,
             NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
            from WEAVING_YARN_TR,WEAVING_YARN_GATEPASS
            where WEAVING_YARN_TR.GP_NO=WEAVING_YARN_GATEPASS."GP_NO." and WEAVING_YARN_TR.YR_ID=WEAVING_YARN_ITEMS.IT_ID and WEAVING_YARN_GATEPASS.TO_FROM=WEAVING_PARTIES.PARTY_ID

          UNION ALL

          select
           null as TR_ID,
           WEAVING_WARP_SET.SET_ID,
           WEAVING_WARP_SET.YARN_ITEM,
           WEAVING_WARP_SET.PARTY,
           WEAVING_WARP_SET.RECEIVED_DATE,
           WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,
           null as BAGS_OUT,
           NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
          from WEAVING_WARP_SET
          where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
          ) t
        ) AS STOCK_BALANCE
FROM WEAVING_PARTIES
  CROSS JOIN WEAVING_YARN_ITEMS;
WHERE STOCK_BALANCE is not null

Even when I remove the AS as suggested by Vérace I still receive an error message:

...
    from WEAVING_WARP_SET
    where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
    ) t

    ) STOCK_BALANCE

    FROM WEAVING_PARTIES
    CROSS JOIN WEAVING_YARN_ITEMS
....

Error message:

  ORA-06550: line 41, column 19: ORA-00904: "STOCK_BALANCE": invalid identifier

Verace Still getting this error. ORA-20999: Failed to parse SQL query! ORA-06550: line 41, column 19: ORA-00904: "STOCK_BALANCE": invalid identifier

select
         PARTY_ID,
         PARTY_NAME,
         IT_ID,
         IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
    (select

     SUM(BALANCE)

    FROM (

    select
     WEAVING_YARN_TR.TR_ID,
     WEAVING_YARN_TR.GP_NO,
     WEAVING_YARN_TR.YR_ID,
     WEAVING_YARN_GATEPASS.TO_FROM as PARTY,
     WEAVING_YARN_GATEPASS.GP_DATE,
     WEAVING_YARN_TR.BAGS_IN,
     WEAVING_YARN_TR.BAGS_OUT,
     NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
    from WEAVING_YARN_TR,WEAVING_YARN_GATEPASS
    where WEAVING_YARN_TR.GP_NO=WEAVING_YARN_GATEPASS."GP_NO." and WEAVING_YARN_TR.YR_ID=WEAVING_YARN_ITEMS.IT_ID and WEAVING_YARN_GATEPASS.TO_FROM= WEAVING_PARTIES.PARTY_ID

    UNION ALL

    select
     null as TR_ID,
     WEAVING_WARP_SET.SET_ID,
     WEAVING_WARP_SET.YARN_ITEM,
     WEAVING_WARP_SET.PARTY,
     WEAVING_WARP_SET.RECEIVED_DATE,
     WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,
     null as BAGS_OUT,
     NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
    from WEAVING_WARP_SET
    where WEAVING_WARP_SET.YARN_ITEM=WEAVING_YARN_ITEMS.IT_ID and WEAVING_WARP_SET.PARTY= WEAVING_PARTIES.PARTY_ID
    ) t

    ) STOCK_BALANCE

    FROM WEAVING_PARTIES
    CROSS JOIN WEAVING_YARN_ITEMS

    WHERE STOCK_BALANCE is not null;

You can't reference a column alias on the same level where you introduced it. You need to wrap your query into a derived table. Note that the columns in the SELECT list of your sub-query are pretty useless as you are only using the balance column in the outer query:

select *
from (
  select PARTY_ID,
         PARTY_NAME,
         IT_ID,
         IT_ID || ' - ' || IT_DESC || ' - ' || IT_BRAND,
         (select SUM(BALANCE)
          FROM (
            select NVL(WEAVING_YARN_TR.BAGS_OUT,0)-NVL(WEAVING_YARN_TR.BAGS_IN,0) as BALANCE
            from WEAVING_YARN_TR,
                   WEAVING_YARN_GATEPASS
            where WEAVING_YARN_TR.GP_NO = WEAVING_YARN_GATEPASS."GP_NO."
              and WEAVING_YARN_TR.YR_ID = WEAVING_YARN_ITEMS.IT_ID
              and WEAVING_YARN_GATEPASS.TO_FROM = WEAVING_PARTIES.PARTY_ID
            UNION ALL
            select NVL(null,0)-NVL(WEAVING_WARP_SET.TOTAL_BAGS_CONSUMED,0) as BALANCE
            from WEAVING_WARP_SET
            where WEAVING_WARP_SET.YARN_ITEM = WEAVING_YARN_ITEMS.IT_ID
              and WEAVING_WARP_SET.PARTY = WEAVING_PARTIES.PARTY_ID
           ) t
          ) AS STOCK_BALANCE
  FROM WEAVING_PARTIES
) x
  CROSS JOIN WEAVING_YARN_ITEMS
WHERE x.STOCK_BALANCE is not null;


mysql.gtid_executed is an internal table used to support some specific use cases; it's not meant to be monitored/used like @@global.gtid_executed (and other ways to access it, such as performance_schema.global_variables).

And it's only updated at certain times, which is what you were seeing. You can read more about it here.


What is the max network latency between synchronous-commit AlwaysOn Availability Groups Is there a maximum supported network latency (ms) between two synchronous AlwaysOn replica?
I need to give this information to network administrators for configuring the link between the two nodes of the cluster.
I know that syncronization between replicas is influenced by disk speed and other things: The picture in this article explain the process involved.
The AlwaysOn instance will host the sap database (1 TB of data) and I estimate that the workload will be considerable with large transactions.

There is no maximum for the supported latency for synchronous replicas.  You will sometimes opt for a higher-latency deployment in return for a RPO=0 for a broader range of failures.

The most common mission-critical configuration is to have a pair of synchronous replicas on separate racks in the same datacenter, and a third, asynchronous replica in a separate datacenter.

But guidance for your scenario should probably come from SAP, or from the Microsoft/SAP Engineering Alliance.  See eg https://techcommunity.microsoft.com/t5/running-sap-applications-on-the/bg-p/SAPApplications/label-name/AlwaysOn


Why isn't postgres prompting me about a new user? I'm following these instructions to get postgres working with rails on Windows 7:

  Run "Start Command Prompt with Ruby" then...

  createuser -U postgres -P NewUserName
  When it prompts for a password for new role, assign it a new password.
  No, the new role should not be a superuser.
  Yes, the new role should be allowed to create databases.
  No, the new role should not be allowed to create more new roles.
  Enter the postgresPWD you made when we installed PostgreSQL.

But this isn't happening. After I run #1, it asks me for a password, then skips straight to 5. This is completely screwing me over! Where did 2 through 4 go?

So then I tried doing it manually with createuser -s -U postgres -d -r (I have to do -U postgres because otherwise it asks for a password for a user that seems to be based on my Windows account, and no password I give it is successful...I tried fixing this by changing pg_hba.conf and then NOTHING worked)....so anyway I tried doing it manually and it was successful, but then I tried createdb newdb_name and it said createdb: could not connect to database template1: FATAL: password authentication failed for user "{my windows account}".

So I guess I could createdb as postgres, but then my rails commands to rake db:create would still fail. I need the active account to have full rights so that I can keep doing rails work.

I'm becoming really miserable about this. Please help...

First, let's understand how createuser works, run this command and take a look at the results:

> createuser --help

Will you see a lot of information, by those the most important for you now is the following...

This one is what makes PostgreSQL ask for a password for this new user:

-P, --pwprompt            assign a password to new role

This one is the one that will make this user able to create a new database (notice you didn't used it):

-d, --createdb            role can create new databases

At the bottom, you will have a group of arguments:

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to create)
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

These ones are used to connect to PostgreSQL's instance, which means that, to create a new user you will have first to connect to PostgreSQL using an already existent user that is able to create another. On a default installation the only user you have is postgres, so to connect you have to use it as -U postgres. Depending on your pg_hba.conf configuration, the createuser utility will ask for the password of postgres user.

With all these information, you should be able to know how to create your new user:

> createuser -U postgres -e -P -d NewUserName

OBS: -e is not necessary, but is good to learn what is happening behinde the scenes.

Afeter that all, you probably can create a new database using rake db:create. Perhaps you will need to edit pg_hba.conf, but I think this is a new topic.


Scripting the setting up of a MySQL database - best practices? I am a relative newbie to database administration. I am setting up my first database on my localhost in MySQL and want to understand best practices. This is my current workflow:

# do_everything.sql

SELECT 'CREATING DATABASE STRUCTURE' info;

DROP DATABASE IF EXISTS my_first_db;
CREATE DATABASE IF NOT EXISTS my_first_db;
USE my_first_db;

SET default_storage_engine = innodb;
SELECT CONCAT('storage engine: ', @@default_storage_engine) info;

SELECT 'CREATING TABLE A' info;
-- Code to create table from source_data_A.csv

SELECT 'CREATING LOGGER FOR TABLE A' info;
-- Code to create log table for table A
-- Code to create insert, update, & delete triggers for table A

SELECT 'LOADING SOURCE DATA A' info;
-- Code to load source_data_A.csv into table A

SELECT 'CREATING TABLE B' info;
-- Code to create table from source_data_B.csv

SELECT 'CREATING LOGGER FOR TABLE B' info;
-- Code to create log table for table B
-- Code to create insert, update, & delete triggers for table B

SELECT 'LOADING SOURCE DATA B' info;
-- Code to load source_data_B.csv into table B

SELECT 'DATABASE CREATION COMPLETE' info;

From my Terminal, I then run:

mysql --local-infile=1 -uroot -p < /absolute/path/to/do_everything.sql

Should I be splitting up do_everything.sql into different .sql files according to logical chunks? If so, would I then have a single master shell script that executes all of them?
If I keep the same workflow, am I following best practices?

Should I be splitting up do_everything.sql into different .sql files according to logical chunks? If so, would I then have a single master shell script that executes all of them?

IMHO, yes, you should split up your do_everything.sql script into smaller logical chunks - modularisation is one of the key tools that we software engineers have at our disposal to help organise our work, and this will lead to fewer bugs and fewer mistakes. You can test module by module rather than having to always deal with a behemoth (or "juggernaut" as someone from the Indian subcontinent might say! :-) ).

If I keep the same workflow, am I following best practices?

See my remarks about modularisation above - I believe that it would be considered best practice to break your script up into logical parts - I used to always do it table by table - that way it was easy to see where any problems arose, and the "sub-scripts" could be either tested one by one or the master script could be checked.

You asked in comments:

Can I modularize the scripts and run them from a single "master" .sql file? If so, having trouble looking up the command to do so.

It's very simple - you simply use the SOURCEkeyword (i.e. SOURCE /path/to/file.sql) in your scripts - you can nest this to many levels (but not too many).

What I typically did was have a master_script.sql which was really just a "skeleton" - it was just filled with SOURCE statements calling other scripts. Some of those might themselves have had SOURCE statements (within reason). I found this worked very well.


Selecting multiple values from a column and put them into separate columns I have a table like this:

MainUser | PartyUser
--------------------
George   | Tom
Michael  | Tim
Michael  | James
Michael  | Monica
Jim      | Nick
Jim      | Connie

And I need to get something like this:

MainUser | PartyUser1 | PartyUser2 | PartyUser3 | PartyUser4 ...
----------------------------------------------------------------
George   | Tom        |            |            |
Michael  | Tim        | James      | Monica     |
Jim      | Nick       | Connie     |            |

Can someone help to find a way doing this in sql?
I'm having a hard time figuring this out, so any help would be greatly appreciated.

The simple way (assuming you already know all the party users) would be to use pivot() and row_number() statements.

select  MainUser,
        [1] as PartyUser1,
        [2] as PartyUser2,
        [3] as PartyUser3,
        [4] as PartyUser4,
        [5] as PartyUser5,
        [6] as PartyUser6
from (
    select *, row_number() over(partition by MainUser order by PartyUser) as rn
    from party_table
    ) t
PIVOT (max(PartyUser) for rn in ([1], [2], [3], [4], [5], [6])) as u


HADR high worker thread usage Why would an availability group's number of worker threads in an HADR pool increase well beyond minimum usage of "typically, there are 3–10 shared threads" per replica?

In one case we've observed usage of 300+ threads with 3 availability groups and 10 databases total. SQL Server 2014 SP1.

Our leads are backup on secondary replica, high activity on primary replica, reports on secondary replica.

The AGs are in a datacenter on VMware. 16 schedulers total, usual worker threads are under 200 range. max_dop on server is 2.

3 AG, 10 DB, 4 replica each - primary, 2 readonly, 1 not readable.
1 secondary is synch, 2 async
16 vcores on 32 cores physical on large multi host cluster.
No overprovision.
Other smaller VMs 4-8 cores are colocated, but they don't press on CPU

We observed a spike in worker threads resulting in denial of service. Attribution of worker threads to AG is our assumption, as only those worker threads can cross the limit.

Below links from the SQL Server Premier Field Engineer Blog read in context don't give a complete answer to me:

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption
Are my actual worker threads exceeding the sp_configure ‘max worker threads’ value?

Since your DC is on VM, I suspect you are experiencing poor disk performance. Poor disk performance can result in slower log-write times on the secondary which can result in a slower acknowledgment back to the primary replica from the secondary replica (exhausting worker threads).

Disk latency on Secondary Replica can cause an increase in HADR Sync Commit process, resulting in the Primary holding open threads while waiting for the Secondary to acknowledge the transaction.

Please check the error log for Deadlocked Schedulers and collect some IO metrics from PerfMon to see the disk latency and the disk queue length.


Can MySQL log lock waits? Is it possible to have MySQL log queries or transactions where lock waits occur? One can debug specific application errors generated from lock wait timeout errors from the DB but without the ability to become aware of the occurrences of lock waits that don't hit the timeout, it seems difficult to anticipate where the next timeout error may be lurking.

Is there a way to have MySQL log these?

I have some good news and some bad news.

The good news is that this will work for MySQL. The
bad news is that it won't work for 5.5. In any case,
I would advise moving to 5.6 anyway, in no small part
due to the vast improvements made to the performance
schema (see below).

I compiled and installed 5.6 (am currently running
5.7 - bit of a bleeding edge type of guy :-) ).

After some frustrating Googling &c. and barking
up a forest of wrong trees, I finally came up
with this.

SELECT Table_schema, Table_Name, Column_Name
FROM Information_Schema.Columns
Where (Table_Schema = 'information_schema'
or Table_Schema = 'sys')
AND Column_Name like '%lock%';

But, if you check Result 1 (at end of post), you'll
see I didn't have much luck.

But then I remembered Mark Leith's (Software
Development Senior Manager, Oracle) MySQL
sys schema (and ps_helper). These are available
from here

https://github.com/MarkLeith.

I ran the same SQL as above with

or Table_Schema = 'sys'

added, and I came up with Result 2 (at end
of post).

Innodb_Wait_Locks looked promising, so I ran a DESC.

mysql> DESC sys.innodb_lock_waits;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| waiting_trx_id      | varchar(18)         | NO   |     |         |       |
| waiting_thread      | bigint(21) unsigned | NO   |     | 0       |       |
| waiting_query       | longtext            | YES  |     | NULL    |       |
| waiting_lock_id     | varchar(81)         | NO   |     |         |       |
| waiting_lock_mode   | varchar(32)         | NO   |     |         |       |
| waiting_lock_type   | varchar(32)         | NO   |     |         |       |
| waiting_lock_table  | varchar(1024)       | NO   |     |         |       |
| waiting_lock_index  | varchar(1024)       | YES  |     | NULL    |       |
| blocking_trx_id     | varchar(18)         | NO   |     |         |       |
| blocking_thread     | bigint(21) unsigned | NO   |     | 0       |       |
| blocking_query      | longtext            | YES  |     | NULL    |       |
| blocking_lock_id    | varchar(81)         | NO   |     |         |       |
| blocking_lock_mode  | varchar(32)         | NO   |     |         |       |
| blocking_lock_type  | varchar(32)         | NO   |     |         |       |
| blocking_lock_table | varchar(1024)       | NO   |     |         |       |
| blocking_lock_index | varchar(1024)       | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

So, there you have pretty much all the info you could
want about locks, (I hope!).

No, as to logging these, there doesn't appear to be a logging function
for this table - many of the p_s tables have accompanying history
tables. I think that there's a good case for keeping a history of
this one - but you can "roll your own" using MySQL events to
append the contents of this table to a logging table.

Say every one second (or period of your choice) and then check to
see which of your queries are regularlary appearing in the
logging table. It's perhaps not up to Oracle's functionality,
but it's getting there :-)

Result 1

+--------------------+------------------------+----------------------------+
| Table_schema       | Table_Name             | Column_Name                |
+--------------------+------------------------+----------------------------+
| information_schema | PROFILING              | BLOCK_OPS_IN               |
| information_schema | PROFILING              | BLOCK_OPS_OUT              |
| information_schema | INNODB_LOCKS           | lock_id                    |
| information_schema | INNODB_LOCKS           | lock_trx_id                |
| information_schema | INNODB_LOCKS           | lock_mode                  |
| information_schema | INNODB_LOCKS           | lock_type                  |
| information_schema | INNODB_LOCKS           | lock_table                 |
| information_schema | INNODB_LOCKS           | lock_index                 |
| information_schema | INNODB_LOCKS           | lock_space                 |
| information_schema | INNODB_LOCKS           | lock_page                  |
| information_schema | INNODB_LOCKS           | lock_rec                   |
| information_schema | INNODB_LOCKS           | lock_data                  |
| information_schema | INNODB_TRX             | trx_requested_lock_id      |
| information_schema | INNODB_TRX             | trx_tables_locked          |
| information_schema | INNODB_TRX             | trx_lock_structs           |
| information_schema | INNODB_TRX             | trx_lock_memory_bytes      |
| information_schema | INNODB_TRX             | trx_rows_locked            |
| information_schema | INNODB_TRX             | trx_autocommit_non_locking |
| information_schema | INNODB_LOCK_WAITS      | requested_lock_id          |
| information_schema | INNODB_LOCK_WAITS      | blocking_trx_id            |
| information_schema | INNODB_LOCK_WAITS      | blocking_lock_id           |
| information_schema | INNODB_BUFFER_PAGE_LRU | FREE_PAGE_CLOCK            |
| information_schema | INNODB_BUFFER_PAGE     | BLOCK_ID                   |
| information_schema | INNODB_BUFFER_PAGE     | FREE_PAGE_CLOCK            |
+--------------------+------------------------+----------------------------+
24 rows in set (0.02 sec)

Result 2

+--------------------+-------------------------------------+----------------------------+
| Table_schema       | Table_Name                          | Column_Name                |
+--------------------+-------------------------------------+----------------------------+
| information_schema | PROFILING                           | BLOCK_OPS_IN               |
| information_schema | PROFILING                           | BLOCK_OPS_OUT              |
| information_schema | INNODB_LOCKS                        | lock_id                    |
| information_schema | INNODB_LOCKS                        | lock_trx_id                |
| information_schema | INNODB_LOCKS                        | lock_mode                  |
| information_schema | INNODB_LOCKS                        | lock_type                  |
| information_schema | INNODB_LOCKS                        | lock_table                 |
| information_schema | INNODB_LOCKS                        | lock_index                 |
| information_schema | INNODB_LOCKS                        | lock_space                 |
| information_schema | INNODB_LOCKS                        | lock_page                  |
| information_schema | INNODB_LOCKS                        | lock_rec                   |
| information_schema | INNODB_LOCKS                        | lock_data                  |
| information_schema | INNODB_TRX                          | trx_requested_lock_id      |
| information_schema | INNODB_TRX                          | trx_tables_locked          |
| information_schema | INNODB_TRX                          | trx_lock_structs           |
| information_schema | INNODB_TRX                          | trx_lock_memory_bytes      |
| information_schema | INNODB_TRX                          | trx_rows_locked            |
| information_schema | INNODB_TRX                          | trx_autocommit_non_locking |
| information_schema | INNODB_LOCK_WAITS                   | requested_lock_id          |
| information_schema | INNODB_LOCK_WAITS                   | blocking_trx_id            |
| information_schema | INNODB_LOCK_WAITS                   | blocking_lock_id           |
| information_schema | INNODB_BUFFER_PAGE_LRU              | FREE_PAGE_CLOCK            |
| information_schema | INNODB_BUFFER_PAGE                  | BLOCK_ID                   |
| information_schema | INNODB_BUFFER_PAGE                  | FREE_PAGE_CLOCK            |
| sys                | host_summary_by_statement_latency   | lock_latency               |
| sys                | host_summary_by_statement_type      | lock_latency               |
| sys                | innodb_lock_waits                   | waiting_lock_id            |
| sys                | innodb_lock_waits                   | waiting_lock_mode          |
| sys                | innodb_lock_waits                   | waiting_lock_type          |
| sys                | innodb_lock_waits                   | waiting_lock_table         |
| sys                | innodb_lock_waits                   | waiting_lock_index         |
| sys                | innodb_lock_waits                   | blocking_trx_id            |
| sys                | innodb_lock_waits                   | blocking_thread            |
| sys                | innodb_lock_waits                   | blocking_query             |
| sys                | innodb_lock_waits                   | blocking_lock_id           |
| sys                | innodb_lock_waits                   | blocking_lock_mode         |
| sys                | innodb_lock_waits                   | blocking_lock_type         |
| sys                | innodb_lock_waits                   | blocking_lock_table        |
| sys                | innodb_lock_waits                   | blocking_lock_index        |
| sys                | processlist                         | lock_latency               |
| sys                | statement_analysis                  | lock_latency               |
| sys                | user_summary_by_statement_latency   | lock_latency               |
| sys                | user_summary_by_statement_type      | lock_latency               |
| sys                | x$host_summary_by_statement_latency | lock_latency               |
| sys                | x$host_summary_by_statement_type    | lock_latency               |
| sys                | x$innodb_lock_waits                 | waiting_lock_id            |
| sys                | x$innodb_lock_waits                 | waiting_lock_mode          |
| sys                | x$innodb_lock_waits                 | waiting_lock_type          |
| sys                | x$innodb_lock_waits                 | waiting_lock_table         |
| sys                | x$innodb_lock_waits                 | waiting_lock_index         |
| sys                | x$innodb_lock_waits                 | blocking_trx_id            |
| sys                | x$innodb_lock_waits                 | blocking_thread            |
| sys                | x$innodb_lock_waits                 | blocking_query             |
| sys                | x$innodb_lock_waits                 | blocking_lock_id           |
| sys                | x$innodb_lock_waits                 | blocking_lock_mode         |
| sys                | x$innodb_lock_waits                 | blocking_lock_type         |
| sys                | x$innodb_lock_waits                 | blocking_lock_table        |
| sys                | x$innodb_lock_waits                 | blocking_lock_index        |
| sys                | x$processlist                       | lock_latency               |
| sys                | x$statement_analysis                | lock_latency               |
| sys                | x$user_summary_by_statement_latency | lock_latency               |
| sys                | x$user_summary_by_statement_type    | lock_latency               |
+--------------------+-------------------------------------+----------------------------+
62 rows in set (0.04 sec)


What are the type of drivers for Communication with Java? I am developing one application using core java and Mysql Database.
As i read in Most of the site, they suggest that use type 4 driver for communication using JDBC.

In JDBC Connection i have use mysql-connector-java-5.1.24-bin.jar from Connector J 5.1.20.0 folder of Mysql 5.6.

My questions are.

1) mysql-connector-java-5.1.24-bin.jar this one is which type of Driver.?

2) I want to use only for development purpose not to deploy so which one more suitable for me?

3) from where i'll get that drivers for development?

In order to connect the Java application with the database you need the connector. You need to the mysql-connector.jar to connect the application with mysql database. It need to add both for standalone as well as for web application.
You can download the .jar file from here.
https://dev.mysql.com/downloads/connector/j/3.1.html


why does mysqldump restore to a smaller size? I am using MySQL and mysqldump v8.0.19 on Windows 10. I have an InnoDB database, old. I use mysqldump (and the --no-create-db option) to dump its tables. I then create a new database, new, and import the dumped tables with
mysql -u myName -p new < old.sql

This procedure seems to work in the sense that the tables in old and new seem to have exactly the same data and exactly the same keys. CHECK TABLE reveals no problems with the tables in new, and commands like SELECT * FROM old.table1 UNION SELECT * FROM new.table1 reveal no discrepancies between the old and new tables.
But for all of that, the new tables are smaller. Specifically, this command shows that they're smaller in both data_length and index_length:
SELECT concat( table_schema, '.', table_name ) table_name,
  concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
  concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length,
  concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
  FROM information_schema.TABLES
  ORDER BY ( data_length + index_length ) DESC;

Why are the restored tables smaller (and should I be concerned)?
A [https://stackoverflow.com/questions/24707770/mysql-database-dump-restores-to-smaller-size](question like this) was asked six years ago on Stack Overflow, but it never got any replies.

InnoDB stores records inside pages (by default each page size is equal to 16 KB). For different reasons you may have partially filled pages (for example, after you deleted some records). When you restore database using mysqldump your rebuild tables from scratch and receive less fragmented version of your database. You can use OPTIMIZE TABLE command to defragment table in your old database and release some space.


Mysql unable to store emoji in utf8mb4 collation I can't store emoji characters in mysql datatbase.

I searched a lot, but again fails. I have followed this tutorial and changed these variables from Variables tab in phpmyadmin.

| Variable_name          | Value
----------------------------
character_set_client     | utf8
character_set_connection | utf8mb4
character_set_database   | utf8mb4
character_set_filesystem | binary
character_set_results    | utf8
character_set_server     | utf8mb4
character_set_system     | utf8
collation_connection     | utf8mb4_unicode_ci
collation_database       | utf8mb4_unicode_ci
collation_server         | utf8mb4_unicode_ci

NOTE: set_client and set_result are also utf8mb4 in Variables tab in phpmyadmin.

I even added these lines in my.ini file:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

But again no result!! I just get ???? instead of emoji. Any idea?

Edit: SHOW CREATE TABLE emoji result

 CREATE TABLE `emoji` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `emj_text` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `emj_text` (`emj_text`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Paraphrasing https://stackoverflow.com/a/38363567/1766831 :

Question Marks (regular ones, not black diamonds) (Se?or for Señor):

The bytes to be stored are not encoded as utf8mb4. Fix this.
The column in the database is CHARACTER SET utf8mb4. Fix this.
Also, check that the connection during reading is UTF-8.  (That is, check phpmyadmin for utf8mb4.

Once ???? is stored, the Emoji is lost.  That is, the INSERT side has the problem.  The SELECT side may also have a problem.


Postgres 9.5 xlog fills all disk space in replication setup with replication slot I have replication set up on Postgresql 9.5 using replication slot. I have one primary and one slave machine, both Ubuntu 14.04 with a cluster called main that is in a directory other than the default. On both servers separately the database worked as expected (before setting up replication), including access from another Ubuntu server.

On the primary, I have in the postgresql.conf:

wal_level = hot_standby
archive_mode = on
max_replication_slots = 8
archive_timeout = 60
max_wal_senders = 8
wal_keep_segments = 100
logging_collector = on

I added a replication user to pg_hba.conf:

host    replication     repuser         [iprange]         md5

I restarted the db and created a replication slot like so (as sudo -u postgres psql postgres):

SELECT * FROM pg_create_physical_replication_slot('repslot001');

On the slave machine, I started with the pg_basebackup:

sudo -u postgres pg_basebackup -v -h [ipofprimary] -U repuser -D [mydatadir] -R -Xs -P

I added to postgresql.conf:

hot_standby = on

My recovery.conf:

standby_mode = 'on'
primary_conninfo = 'user=repuser password=geheim host=[ipofprimary] port=[port] sslmode=prefer sslcompression=1 krbsrvname=postgres'
primary_slot_name = 'repslot001'
trigger_file = '[datadirectory]/finish.recovery'
recovery_target_timeline='latest'

I restarted the db and everything seemed fine. I went home at this point.

The next day my master server had crashed because the disk was full:

PANIC:  could not write to file "pg_xlog/xlogtemp.11700": No space left on device

I googled around and found that this happens if the archive_command, as set in postgresql.conf on the master, is incorrect or missing.

But it is my understanding that when using replication slots I don't need an archive_command (and so, I didn't set an archive command). What mistake did I make?

If you need more information to be able to answer this, please let me know. But note that I need to continue my work, I am now trying if I can promote slave to master and clean up the old broken master. I'll make a backup of the data directory, which also has the logs. Also note that this is a test setup, I will not lose any data. Only when I'm confident I know how it works and how to set it up correctly will I start using replication in a production environment.

Thank you.

Edit.
The mistake I made was that I mixed up using archiving and using replication slots (thanks Frankie for your patience). I now have the following in my postgresql.conf - and it is working as expected:

wal_level = hot_standby
max_wal_senders = 8
max_replication_slots = 1
hot_standby = on

I removed the archiving settings archive_mode and archive_command.

If you just want a hot standby master/slave setup, without, eg point in time recovery, just set archive_mode to false or remove it from your postgres.conf. Otherwise you'll have to come up with a storage plan so you have enough space which might involve setting up archive_command to copy the archive to a disk with enough space.