PHP / MYSQL connection failures via socket under moderate load I've done quite a bit of reading before asking this, so let me preface by saying I am not running out of connections, or memory, or cpu, and from what I can tell, I am not running out of file descriptors either.

Here's what PHP throws at me when MySQL is under moderate load:

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (11 "Resource temporarily unavailable")

This happens randomly under moderate load - but the more I push, the more frequently php throws this at me. While this is happening I can always connect locally through the console and from PHP through 127.0.0.1 instead of "localhost" which uses the faster unix socket.

Here's a few system variables to weed out the usual problems:

cat /proc/sys/fs/file-max = 4895952
lsof | wc -l = 215778 (during "outages")
Highest usage of available connections: 26% (261/1000)

InnoDB buffer pool / data size: 10.0G/3.7G (plenty o room)

soft nofile 999999
hard nofile 999999

I am actually running MariaDB (Server version: 10.0.17-MariaDB MariaDB Server)

These results are generated both under normal load, and by running mysqlslap during off hours, so, slow queries are not an issue - just high connections.

Any advice? I can report additional settings/data if necessary - mysqltuner.pl says everything is a-ok

and again, the revealing thing here is that connecting via IP works just fine and is fast during these outages - I just can't figure out why.

Error 11 in unix is a segmentation fault error. Either the server has bad memory chips installed or you are making other configuration values too much for the server to handle.

I once experimented with setting the nofile value in linux to a value like yours and my whole server crashed. Now I make nofile set to maybe 40000 tops. Try that, then reboot the server to see if the problem goes away.


centos container, installing mariadb server I am trying to run a mariadb server on a docker image(CentOS Linux release 7.3.1611 (Core)).

I install mariadb with command yum install mysql. after installing complete, i try to execute command mysql but i got this error:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

the directory in /var/lib/mysql is empty

the my.conf file in /etc contains:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

with running command systemctl start mariadb I got this error:

  Failed to get D-Bus connection: Operation not permitted

Did I make any mistake?

It sounds like you're new to Docker. You may want to spend some time reading through documentation and examples to get a better feel for how things work.

First, simply installing software doesn't necessarily make it run, and that is certainly the case with CentOS and other Red Hat variants.  On a bare metal host, you would simply run systemctl start mariadb to start it, but this only works because on a regular system, the /sbin/init component of systemd is started as part of the boot process.

Inside a docker container, nothing is running by default.  For example, if you were to run ps -fe on your host, you would probably see hundreds of running processes.

If you were to run:

docker run centos ps -fe

You would see:

UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 11:12 ?        00:00:00 ps -fe

Because of this, you can't start services using the normal mechanism.  You will instead need to (a) start the service manually and (b) ensure that you have started it such that it will stay in the foreground (because a Docker container exits when the foreground process exits or puts itself into the background).

If you take a look at the official mariadb image, you will see that it ultimately just calls mysqld to start the service.

It sounds like you want to start mariadb inside a container and then try interacting it from within the same container.  While this isn't the normal way of working with docker, you could...

$ docker run -it centos bash
[root@c1d6adf5c8bb /]# yum -y install mariadb-server
[root@c1d6adf5c8bb /]# /usr/libexec/mariadb-prepare-db-dir mariadb.service
[root@c1d6adf5c8bb /]# /usr/bin/mysqld_safe --basedir=/usr &
[root@c1d6adf5c8bb /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

More typically, you would use a Dockerfile to build a mariadb image (or just use the official one), start it, and then use mysql on your host or in another container to interact with that mariadb instance.


In SQL Server, is it possible to have a PRIMARY KEY on a table without either CLUSTERED or NONCLUSTERED indexes on the same key? In Sql Server (2008), is it possible to have a PRIMARY KEY on a set of columns without either CLUSTERED or NONCLUSTERED indexes on the same set of columns?

I am aware of the fact that PRIMARY KEY and CLUSTERED INDEX key are separate concepts and that we can create PRIMARY KEY without CLUSTERED INDEX on it (see below).

ALTER TABLE dbo.Sample
ADD CONSTRAINT PK_Sample_SeqGUID_Col1 PRIMARY KEY NONCLUSTERED (SeqGUID_Col1)

But my question is to see if it is possible to create PRIMARY KEY on a table without a CLUSTERED or NONCLUSTERED index on it.

In short, no. A primary key by definition requires uniqueness, an index on the primary key field is the database engines route to enforcing this constraint.

From BOL:

  When you specify a PRIMARY KEY constraint for a table, the Database
  Engine enforces data uniqueness by creating a unique index for the
  primary key columns. This index also permits fast access to data when
  the primary key is used in queries. Therefore, the primary keys that
  are chosen must follow the rules for creating unique indexes.


Using IF statement after a WITH clause I want to create an alias for a SELECT statement, but then I want to limit the resultset with a TOP clause. If the procedure TopCount parameter is bigger than 0, I want to put limit result set with TOP clause.

But after the WITH clause, the IF statement is producing an error. Can I continue with IF or different than the SELECT statement after the WITH clause?

WITH
   alias AS (...)
IF @TopCount>0
   SELECT TOP (@TopCount) * FROM alias;
ELSE
   SELECT * FROM alias;
GO

No, you can't do that. Your best bet would be to give your top variable a very high default value.

CREATE PROC dbo.if_branch (@top INT = 2147483647)
AS
BEGIN

WITH d AS
    (
        SELECT *
        FROM sys.databases AS ds
        WHERE ds.name = 'tempdb'
    )
SELECT TOP (@top) *
FROM d;

END


multiple requests shut down mysql I have some problems with my mysql database.

I configured a server with the database and other services (apache, php) and put run multiple applications.

There are two problems.

Every day at the same time mysql fails, and I must go and restart it.
Every time I reload the website many times continuously mysql falls, and again and need to restart it.

Mysql logs show:

140107 0:09:29 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use$
140107 0:09:29 [Note] Plugin 'FEDERATED' is disabled.
140107 0:09:29 InnoDB: The InnoDB memory heap is disabled
140107 0:09:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140107 0:09:29 InnoDB: Compressed tables use zlib 1.2.3.4
140107 0:09:29 InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
140107 0:09:29 InnoDB: Completed initialization of buffer pool
140107 0:09:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140107 0:09:29 [ERROR] Plugin 'InnoDB' init function returned error.
140107 0:09:29 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140107 0:09:29 [ERROR] Unknown/unsupported storage engine: InnoDB
140107 0:09:29 [ERROR] Aborting
140107 0:09:29 [Note] /usr/sbin/mysqld: Shutdown complete

What's wrong with my database?

The comment from Barmar is correct.

You do not have enough memory in the server for the InnoDB Buffer Pool.

Since you got the message

140107 0:09:29 InnoDB: Initializing buffer pool, size = 64.0M

followed by

140107 0:09:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool

you should lower innodb_buffer_pool_size below 64M in /etc/my.cnf and restart mysql.

SUGGESTION: If it is withn your budget, you should setup Apache and PHP on a separate server. Connection all webserver requests to the DB thereafter.


Restoring postgres backup with WAL-E fills up disk I am running Postgres and WAL-E in a Docker container built from a custom image based on postgres:9.4. WAL-E works great and I have restored from backups a number of times. However, the backups take a while to run and they consume a TON of disk space. The space seems to be consumed by segments and log files, and they all get cleared out and the space is restored as soon as the restore completes. But is there any way to do the backup in batches, so that my disk doesn't fill up during restore?

It was operator error. My process to run daily base backups was not being run, so I had weeks of WAL segments, and that's what was causing it to fill up the disk and take forever. Once I got them all imported, I ran a base backup and then did the import again, and it was done in less than a minute. Do frequent base backups!


01/01/1753 date is corrupt in Excel after export from SSRS The date 01/01/1753 appears fine in the SSRS 208R2 report preview, but when the report is exported to Excel 2010, it appears as ########### and the value in the cell is -53689. All other dates come across just fine.

Apparently Excel accepts 01/01/1753 input and displays the dates correctly when manually entered.

Is there any method to display the SSRS reports containing the 01/01/1753 dates in Excel correctly as well?

PS: I was not able to find any relevant information on this using google. It seems to ignore the numbers and return mostly the articles on how to sort the worksheets by date.

Excel specifically provides a message: Dates and times that are negative or too large are displayed as ######.

So, that is a Excel limit.  Further, you can type in strings that look like dates, but Excel may not think of them as dates. For example, if I make a column of date type and enter into the columns -53689, 01/01/1753, and 01/01/1900 (using my settings) I get this:

####################
01/01/1753
            1/1/1900

So, it is easy to see that these are not all being interpreted the same by Excel.


Do any document databases (noSQL) allow you to do cross database joins? I am currently looking at MongoDB and ArangoDB as the database for our web application, but neither appear to support cross-database joins, does anyone know if there is such a document database that supports this please?

You can do it postgres

Update as of 9.3

You can now use the new postgres_fdw (foreign data wrapper) to connect to tables in any Postgres database - local or remote.

also you can use dblink functionality but it isn't part of the default PostgreSQL install, but you can add it in.-  dblink.


Installing default MySQL Tables in Windows (mysql_install_db) In Linux, the default MySQL grant tables can be created with the mysql_install_db script, but that does not work on Windows.

How can the default grant tables be installed on Windows?

(No, I’m not looking for the response that Google’s results are packed full of about how they are automatically installed on Windows, because that is only the case with the installer distribution, not the ZIP package. Besides, that does not help after MySQL is installed and the data directory is damaged or is being replaced or such.)

I knew I had faced this before and managed to figure it out, so I searched my drives and found a batch file I had written last time.

For anyone else who experiences this issue, what you need to do is to run the server daemon with the --bootstrap parameters.

Here is the script I used to dump all the databases out to an SQL file and re-import them (that is, manually—and inconveniently—implementing a MySQL counterpart to SQLite’s vacuum command).

::Start the server, change to the MySQL bin directory, and dump all databases
net start MySQL
cd       /d "%serverdir%\MySQL\bin"
mysqldump  -uroot -p --all-tablespaces --all-databases --add-drop-database --add-drop-table --add-locks --comments --complete-insert --compress --create-options --events --routines --quick --quote-names --set-charset --triggers > %temp%\all.sql

::Stop the server and rename the data directory (as backup)
net stop mysql
ren         "%datadir%\MySQL"        MySQL_

::Delete data folder in MySQL directory, optionally copy old mysql tables
rd    /s /q "%serverdir%\MySQL\data"
md          "%serverdir%\MySQL\data"
xcopy /s /e "%datadir%\MySQL_\mysql" "%serverdir%\MySQL\data"

::Bootstrap the server (creates grant tables if they don’t exist)
mysqld --bootstrap

::Run server, optionally upgrade tables and move upgrade log to data directory
start mysqld --skip-grant-tables
mysql_upgrade --force
move "%serverdir%\MySQL\bin\mysql_upgrade_info" "%serverdir%\MySQL\data"

::Import all databases, shutdown, delete logs, then move to old data dir
mysql      -uroot -p < %temp%\all.sql
mysqladmin -uroot -p shutdown
md  "%datadir%\MySQL"
del "%serverdir%\MySQL\data\ib_logfile?"
xcopy /s /e "%serverdir%\MySQL\data\*" "%datadir%\MySQL"
rd    /s /q "%serverdir%\MySQL\data"

::Start the server, if it works, then all should be well, so del
net start mysql
rd    /s /q "%datadir%\MySQL_"


PostgreSQL 12: column proisagg does not exist I created a PostGIS database using the following images here (i.e. 9.3-2.1, 11.0-2.5 and 12.0 tags), however I'm getting the following errors when I try to open the 'public' schema:

An error has occurred:

11:43:59: Error: ERROR:  column "proisagg" does not exist
LINE 9:  WHERE proisagg = FALSE AND pronamespace = 2200::oid
HINT:  Perhaps you meant to reference the column "pr.prolang".

An error has occurred:

11:46:24: Error: ERROR:  column rel.relhasoids does not exist
LINE 1: ...t_userbyid(rel.relowner) AS relowner, rel.relacl, rel.relhas...

I found possible solutions here and here. I tried to ask how should I update the query, but I need at least 50 reputation to comment.

Would anyone know how should I fix this question? Or how should I change the definition of the query on pgAdmin?

Thanks in advance.

System:

PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg16.04+1)
pgAdmin III
Ubuntu 16.04 LTS

The query is hard-coded into pgAdmin III, and you cannot change it.

These columns have been removed in v12 because they are no longer useful.

pgAdmin III has been out of support for years.

Use pgAdmin 4 or another supported client tool.


designing user friendly relational databases I want to create a database system that is user friendly with the right relationships. My system should be in such a way that we have a writer's table, orders table, and payments table such that I am capable of knowing which writer did certain orders and shows the amount the writer should be paid for doing an order. The system should also indicate whether the payment has been made for each order done by the writer.

I tried my best and came up with the following tables:

payments table: pay_id (PK), date, mode of payment, amount, balance, writer_id (fk), order_id (fk);
writer’s table: id (pk), name, email, phone numbers, disciplines;
order’s table: order_id (pk), name, order_status (completed, under warranty, completed), writer_id (fk)

Kindly assist me in making the necessary corrections so that all relations can be created and the database becomes human friendly.

I have absolutely no idea what a user friendly database could possibly look like. Your apps are like the dashboard and controls of an automobile. This is where you might hear the expression user friendly. The database is like the engine and/or transmission. They have important functions to perform and here is where you would expect to hear expressions like reliable.

Your top concern when designing a database is data integrity. That is what gives it the aspect of reliability. Without that, the most user friendly interface in the world would be useless.

There is, however, one section of a database that we might concern ourselves with user friendliness, if by user we mean app developer. That is the api or the interface to the database. The app developer doesn't really care about relationships and really shouldn't be concerned with the physical layout of the database. Sure, in their code one entity may relate to another, but the physical implementation of that relationship in the code may be (and generally is) very different from the physical implementation of the relationship in the database. No computer language has "foreign key" as one of its features.

What the app developer needs are:

Reliable data
Fast retrieval and storage of the data
Data in a format easiest to handle within their coding framework

The first need is delivered by designing the database with data integrity foremost in mind.

The second need is delivered by a good combination of software (efficient queries and stored procedures) and hardware. Very rarely will efficiency require a change in the design of the data, such as denormalization.

The third need can be delivered by a variety of methods which can vary according to the particulars of DBMS and front-end platform but chief among them are a library of stored procedures (heavily overridden to supply an interface for data in a wide range of forms) and views (exposing the data for direct access also in a wide range of forms).

One rule that seems to apply over all three of these needs is this: do not allow developers direct access to the tables. Find out what data they need and in what form they need it, then supply the stored procedures and/or views that will give it to them. An actual api written in the app language would be a great addition. Then the developer doesn't have to think of the database at all, just the api.

This rule helps in several ways but by far the most important is that you will not need to change the design of the database to satisfy the needs of the application developers. Database designers who find themselves in the situation where a database design change is forced by developer needs almost inevitably do so by sacrificing data integrity. When you find yourself going down that road it is all down hill -- and not in a good way!

With a wall of abstraction between the data and the application, when the database needs to change because of changes to entities or relationships, those changes can be made in a way that maintains the all important reliability and then the stored procedures and views changed to maintain the expected interface with the now-changed data. When the application adds or modifies functionality, this can change what data it needs or the form it needs it in, but this does not require a change in database design to fulfill those changing requirements.

Life is good.

In your particular situation, you have Writers, Orders and Payments. This seems like a fairly straight-forward design which you have made a pretty good start. With just a few tweaks (the Payments table doesn't need a writer field -- that is contained in the Order), you should have it nailed down in short order.

Then answers to such questions as "whether the payment has been made for each order done by the writer" can be supplied by stored procedure or view.

If you get to that point and run into trouble, ask another question.


Two tables with same name in MySQL I got this weird issue today when I dropped a temporary table. I dropped the temporary table and desc the table just to verify. But, the table was not dropped. After some searching I found that:

MySQL allows to create a temporary table with the same name as a permanent table. So the temporary table got dropped and not the permanent table. I got really confused with which table I am working.

MySQL version:  5.1.36-enterprise-gpl-pro-log

This is what I'd tested:

mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

 mysql> create temporary table test(id int);
 Query OK, 0 rows affected (0.00 sec)

mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

mysql> drop table test;
 Query OK, 0 rows affected (0.00 sec)

 mysql> desc test;

| Field | Type    | Null | Key | Default | Extra |
--------------------------------------------------
 id       int(11)   YES           NULL

Is this a bug or is there an alternate way to overcome this?

MySQL allows you to create a temp table with a existing name because they don't have the same "scope". A temporary table is visible in the session only, and it is dropped at session ending. If you have the same name, MySQL "hide" the original table until you drop your temp table.

You can refer to the Temporary Tables section in the MySQL documentation

Max.


Parameterized Procedures in SQL Server First, this is for a major assignment. I am not asking any of you far more experienced SQL users to do the assignment for me, but I need your help. SO this project runs similarly to a Netflix model, where you have a customer, a queue, a rental plan, DVDs with directors/producers and genres. What I need help with is twofold. First, I need help figuring out what tables I am missing, and which are missing attributes. I know I need a new table for the site's inventory but am not sure where it should go (one of the requirements is a procedure that adds new movies to inventory, and another needs to find the movies that are out of stock). I attached my ERD and the data I used to create the tables I have so far.

CREATE TABLE customer (
customer_id DECIMAL(12) PRIMARY KEY,
cust_first VARCHAR(50),
cust_last VARCHAR(50),
cust_phone DECIMAL(10),
cust_email VARCHAR(50));

CREATE TABLE rental_plan (
plan_id DECIMAL(12) PRIMARY KEY,
customer_id DECIMAL(12),
description VARCHAR(50));

CREATE TABLE account (
account_id DECIMAL(12) PRIMARY KEY,
customer_id DECIMAL(12),
plan_id DECIMAL(12));

CREATE TABLE queue (
account_id DECIMAL(12),
disc_id DECIMAL(12));

CREATE TABLE dvds (
disc_id DECIMAL(12) PRIMARY KEY,
disc_title VARCHAR(255),
genre_id DECIMAL(12),
producer_id DECIMAL(12)
copies DECIMAL(12));

CREATE TABLE producer (
producer_id DECIMAL(12) PRIMARY KEY,
producer_firstname VARCHAR(255),
producer_lastname VARCHAR(255));

CREATE TABLE genre (
genre_id DECIMAL(12) PRIMARY KEY,
genre_name VARCHAR(255),
description VARCHAR(255));

ALTER TABLE account
ADD CONSTRAINT acc_cst_fk
FOREIGN KEY(customer_id)
REFERENCES customer(customer_id);

ALTER TABLE rental_plan
ADD CONSTRAINT rent_cst_fk
FOREIGN KEY(customer_id)
REFERENCES customer(customer_id);

ALTER TABLE queue
ADD CONSTRAINT queue_acct_fk
FOREIGN KEY(account_id)
REFERENCES account(account_id);

ALTER TABLE queue
ADD CONSTRAINT queue_dvd_fk
FOREIGN KEY(disc_id)
REFERENCES dvds(disc_id);

ALTER TABLE dvds
ADD CONSTRAINT dvd_genre_fk
FOREIGN KEY(genre_id)
REFERENCES genre(genre_id);

ALTER TABLE dvds
ADD CONSTRAINT dvd_prod_fk
FOREIGN KEY(producer_id)
REFERENCES producer(producer_id);

This procedure now works. Thanks!

CREATE PROCEDURE RENT_MOVIE
@account_id_arg DECIMAL(12),
@disc_id_arg DECIMAL(12)
AS
BEGIN
INSERT INTO queue(account_id, disc_id)
VALUES (@account_id_arg, @disc_id_arg);
END;

The requirements I am focused on are parameterized procedures. The next is another procedure that adds a movie to a customer's queue as the next movie to be delivered. The second is a procedure that enrolls customers into specific rental plans. The 3rd is a procedure that adds a certain number of movies to a title's inventory, which is now part of the DVDs table. I can probably handle this one myself now. If anyone wants to assist with these, that would be great, but I just wanted you to be aware of the requirements I need to meet if you do choose to assist me with the two issues listed above.

WRT the procedure - take a second look at the fields and the table you're trying to populate in the INSERT statement; there's a problem there.

I'm trying to figure out why you have an acct_queue_bridge table. This normally is used to maintain a many-to-many relationship. I'm assuming that a queue belongs to one account, not many? Even if an account can have many queues, you'd only need the bridge if one queue ties to multiple accounts.

However, it's true that each account can have many DVDs on its queue, and each DVD could be on many queues. I would eliminate acct_queue_bridge, add account_id to the queue table, and use that as a bridge between account and dvds

I see a few other similar things that I'd question:

Can a customer have multiple accounts? (this is not unusual - allowing an account for each member of the family. However, only include it if the project seems likely to need it).
If the customer has multiple accounts, would there be a separate rental plan for each? I would think not: I would expect there to be a reference table of the different rental plans in use and/or available, and for each customer to sign up for one instance of those plans; in which case, it's possible to store the rental_plan_id in the customer table directly. You might need to track the customer's rental plan history; then you would probably have a separate table with the customer, the plan, and the date range when the plan was active. The current plan could be included there, or it could live in customer, and be copied into a cust_plan_hist table when it changed.
You currently have a queue_id in the dvds table. I would expect one DVD to be able to be in many queues (however, see my next point). I don't see why you'd link a DVD to a specific queue; perhaps I'm missing something here.
I believe that dvds and your inventory table will be closely related, if not one and the same. I'm assuming a row in dvds represents the idea of a movie/show on DVD, not a specific physical disk. in an actual Netflix business, dvd_inv (or whatever) would be where the actual physical disks are represented. You would need that there because each disk will have some sort of inventory number on it, and you'd track the status of each disk: available, shipped to , missing, whatever. You would probably want to track things like the condition of each disk (so if something leaves pristine and comes back scratched up, you can approach your customer about the change), and of course which specific disk is in the hands of which specific customer. However, as this is not necessarily intended to be a "real world" system, I'm not sure if you need to implement that level of detail - you might be able to get away with just having "bucket" fields to hold those numbers in the dvds table itself (3 available, 5 shipped to customers, 1 missing = 9 total).
Note that some instructors will actual object to you designing a more realistic system than they're asking for, because it becomes more difficult for them to assess and grade. Other instructors will applaud the initiative to go above and beyond the minimum requirements. However, once you start that way, if you run into problems later on, you may have to back out and re-implement parts of your solution to meet future parts of the assignment. I'd recommend sticking as close as possible to the actual requirements in school. In the real world, thinking a step or two ahead of your customer is quite valuable during the design phase, and can save your time and their money (something of a double-edged sword, at times, I suppose :-).


Extract some columns data from one table and insert into multiple tables I have a table with 16,000 records and need to get out some columns and then insert values in multiples tables. For example, suppose I have the following tables

tbl_1: id, name, lastname
tbl_2: id, name
tbl_3: id, lastname

I need to get the values from tbl_1 and then insert in tbl_2 and tbl_3 meaning tbl1.name goes to tbl_2.name and tbl_1.lastname goes to tbl3.lastname. Is this possible using SQL queries?

I'm using MySQL latest

Assuming that you are preserving the id assocaited with each name and lastname, run this:

INSERT INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
INSERT INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If tabless tbl_2 and tbl_3 already have data and you wish to overwrite it, run this:

REPLACE INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
REPLACE INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If you are not preserving the ID, then run this:

INSERT INTO tbl_2 (name) SELECT name FROM tbl_1;
INSERT INTO tbl_3 (lastname) SELECT lastname FROM tbl_1;


Does an index continue to be used after it is dropped (Database Polling investigation) The folly of developers who use database polling is shown below.

I have identified a query below which did not have an index in place on a particular column

In addition to polling the database every second with the below query, the developer did not utilise indexes on the CloseDateTime, so a scan of about 50,000 rows was hitting the database constantly. (Keep in mind this was in Development, so the dataset is smaller)

After giving the developer an inkling of why we were experiencing high CPU utilisation on the DB, by showing them a query - they came back with miscellaneous excuses for the cause such as a virus, etc.

I then had to investigate this further, which involved correlating the CPU utilisation using Perfmon, using SentryOne Plan Explorer to look at the impact of the query closer, and then finally - watching what CPU utilisation did when I added a new index that appeared to be missing.

Output of SPBlitzFirst helped me identify the query.

I then investigated it further in Sentry One Plan Explorer. I noticed firstly that there was no Index on 'CloseDateTime' (in the where clause of the inner query)

I tested my hypothesis by creating an index on this column, and watched the CPU utilisation suddenly drop. I then tried to drop this newly created index, expecting to see CPU utilisation increase again, but it did not.

My question is, will the database continue to use this index even after we remove it?

  MemberId,
    Sum(RemovedAmount) as Amount,
    Sum(RemovedQty) as Qty,
    PromotionId,
    CheckNo,
    ProcessedDT
    from vm_config..HospitalityTransaction HT with (NOLOCK)
    where exists (Select * from vm_warehouse..TransHeader TH with (NOLOCK) where AssociatedCheckNo = HT.CheckNo and  CloseDateTime between HT.ProcessedDT -1 and HT.ProcessedDT + 1)
      Group By     MemberId, CheckNo, PromotionId, ProcessedDT

As Peter mentioned, the query may still be using a far better execution plan - and since you know how to check that, I'd suggest doing so - but it is not using the index.

When you drop an index, it is gone. There isn't any way it can linger around once your DROP command returns successfully.

I feel like I should write more, but its really just that simple. Check your execution plan and you should find your answer pretty easily.


How to return rows from multiple tables selected from information_schema I need to return rows from a set of tables found in information_schema. I can find the table names, and I have a function that compiles and executes, but it returns an empty set, so there must be something wrong with my logic.

CREATE OR REPLACE FUNCTION get_prices()
  RETURNS TABLE(price FLOAT) AS $func$
    DECLARE
      tn TEXT;
      BEGIN
        FOR tn in SELECT table_name
          FROM information_schema.tables
          WHERE table_name ~~ '%price%'
          AND table_schema ~~ 'stg0' LOOP
            EXECUTE 'SELECT price FROM stg0.' || tn;
          END LOOP;
      END;
  $func$ LANGUAGE plpgsql;

When I execute the following

SELECT * FROM get_prices();

I get a table with no rows and a single column, price.

The SELECT in the FOR loop is returning all the needed table names. There are many rows in those tables in the price column. What should change to return those rows?

UPDATE: I should have made it more clear that I have a large, undetermined number of source tables for the query at compile time, so I was looking to query the information_schema at runtime in order to pull the source table names dynamically.

The given query works and actually returns data by replacing the EXECUTE line with

RETURN QUERY EXECUTE 'SELECT price FROM stg0.' || tn;

as suggested in the comments.

That's 0-fun. Just generate one statement using UNION ALL and now you don't need cursors.

CREATE TABLE foo_1(price) AS VALUES (1);
CREATE TABLE foo_2(price) AS VALUES (1);
CREATE TABLE foo_3(price) AS VALUES (1);

SELECT STRING_AGG(
  FORMAT(
    'SELECT price FROM %I.%I.%I',
    table_catalog,
    table_schema,
    table_name
  ), ' UNION ALL ') ||';'
FROM information_schema.tables
WHERE table_name like '%foo_%';

You can put it in a function like this (first we get the result as a query string, and then we execute it)

CREATE FUNCTION get_prices()
RETURNS setof int AS $func$
  DECLARE
    sql text := string_agg(
      FORMAT(
        'SELECT price::int FROM %I.%I.%I',
        table_catalog,
        table_schema,
        table_name
      ), ' UNION ALL ') ||';'
    FROM information_schema.tables
    WHERE table_name like '%foo_%';
  BEGIN
    RETURN QUERY EXECUTE sql;
  END;
$func$ LANGUAGE plpgsql;

As a special note, if the list of table_name ~~ '%price%'  AND table_schema ~~ 'stg0' is immutable (that means you're not still adding to it) you're better of doing this one time and saving it as a VIEW


Create read only user without being root I want to create read only user in MySQL without any intervention of root user.

CREATE USER ‘$user‘@’127.0.0.1′ IDENTIFIED BY ‘$password‘;

Works for me only If I am a root user. But I want it to happen from a non root user. Can this be done?

From the manual

  To use this statement, you must have the global CREATE USER privilege
  or the INSERT privilege for the mysql database.

So first you must have the root account grant you access to CREATE USER. Then your account will be able to create other users.


What are possible ways of handling multiple users accessing the same database via custom web API? I am very new to databases and Web design, but I understand enough to know that designing a good database is crucial, so I would like to prepare for that conceptually. However, I was unable to find any answers on my level on the web/in tutorials.

I am working towards a developing a database (MariaDB, to be exact) that will be accessed by my website (written in Django). I want the website to serve as an API for several people to work on the database. Naturally, I want to set some use- or group-specific privileges.

What are the possible ways of doing that, and what are their upsides and downsides?

I can only think of two generals approaches:

Connecting the website to the database as root, and letting the website API limit user capacity to edit certain tables.
Using some sort of server-side user system directly in a web API.

This is important to me both in the context of limited access to certain tables within a database and syncing multiple people editing the same table. Is there an approach generally considered as a good practice?

Don't connect as root/super user

Read this: Multi-Tenant Data Architecture

Since it's a web app, you're going to want to use connection pooling, for performance

This means you need to use impersonation, i.e., login as one user then switch to a different user. Because connection pooling requires that everyone in the pool connect with the same connection string.

For Maria/MySQL, read up on https://mariadb.com/kb/en/mariadb/grant/#proxy-privileges

MySQL/Maria doesn't yet do row security as far as I know, so you'd need to do your access control (via SQL) in the application layer (your web app)

In terms of multiple people editing the same rows at the same time, read up on Optimistic Concurrency Control

Personally, I'd consider Postgres, as it does row security


Is it possible to have one server acting as secondary for two primaries on AG? Can I have a server acting as secondary replica for two primary servers on an availability group?

If possible, are there some concerns about performance in an async environment? Are listeners going to work that way?

Thanks!

As I understand your question, the answer is Yes.  1 SQL instance can act as a secondary for multiple Availability Groups.  Listeners will point to the appropriate AG.  You may have some security concerns to address if the user base of the different AGs is a different set of users...make sure they can't access each other's databases in the case that they are read only, or acting as primary on the DR(Secondary) server.  Also, all instances will need to be a part of the same WSFC.


How to Delete Restoring Database I am running log shipping with SQL server 2008 R2.

I have a situation where the secondary database drive ran out of space and was not applying log shipping transaction logs.

The way I want to fix this is delete the databases at the secondary and configure log shipping from scratch.

The problem I have now is that my secondary databases are in the restoring state, and I cannot delete them. How can I proceed?

For instance if I try to take them offline I get the error,

ALTER DATABASE is not permitted while the database is in the Restoring state.

RESTORE DATABASE dbname
FROM DISK = 'dbname .bak'
WITH REPLACE, RECOVERY --force restore

or just

RESTORE DATABASE dbname WITH RECOVERY

the REPLACE Overwrite the existing database, do it only if you are sure you want to override your existing database as you mentioned you dont care to delete it

RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.

That should bring the database online. Then you can delete it & try again.


Procedure permission issues with a SQL state query We have a login/user with DBO to a single database, and now we're adding a query that's providing some basic monitoring information requiring permissions extending somewhat to the sysadmin side.

Basically, the query needs to be able to run (read only) the following commands:

DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
dbo.sysfiles
sys.master_files
tempdb.sys.database_files
tempdb.sys.dm_db_file_space_usage
sys.dm_exec_requests
sys.dm_tran_locks
sys.partitions
sys.indexes
sys.dm_os_waiting_tasks
sys.dm_os_tasks
sys.dm_exec_sessions
sys.dm_exec_sql_text

Most of that stuff entails permissions like VIEW SERVER STATE, VIEW ANY DEFINITION, etc, all of which can be used to grant the permissions required without going to the sysadmin server role. But we only want to limit these permissions to this single query.

So what we're looking for is options. My very last preferred option is to add the various permissions to the login/user, since then it would have access to more than it needs (various sub-permissions of the sysadmin server role), so I tried creating a procedure with the EXECUTE AS clause, only apparently because of the dynamic SQL inside (we need to output the results of DBCC SQLPERF into a table variable), the ownership chaining gets difficult. Even regardless of that, it seems it's quite tricky to allow a procedure to execute with borderline sysadmin credentials.

I've browsed more about this subject than I can understand, and I still can't get this to work. Basically, can you guys tell me how to do this without creating security holes.

Just a batch, that runs this one query, can only be altered by the sysadmin, and can be read / executed by anyone given the permissions to run it? Thanks!

EDIT:

I'm currently examining the possibility of creating a proxy logins and / or signing the procedure with a certificate. Seems quite the interesting an option.

I think that your simplest option is a job.

You can grant the permissions to start a job through the SQLAgentOperatorRole role in msdb. Members of this role can start jobs owned by a sysadmin login, which can execute the commands you need.

Here's an example based on your requirements:

USE master
GO

CREATE LOGIN testUser WITH PASSWORD = 'strongPassword' , CHECK_POLICY = OFF
GO

USE msdb
GO

CREATE USER testUser FOR LOGIN testUser
GO

EXEC sp_addrolemember 'SQLAgentOperatorRole', 'testUser'
GO

DECLARE @jobId uniqueidentifier

EXEC sp_add_job @job_name=N'test',
        @enabled=1,
        @owner_login_name=N'sa',
        @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobserver @job_id=@jobid, @server_name = @@SERVERNAME

EXEC sp_add_jobstep @job_id=@jobId, @step_name=N'step1',
    @subsystem=N'TSQL',
    @command=N'DBCC SQLPERF(logspace)
EXEC master..xp_fixeddrives
SELECT * FROM dbo.sysfiles
SELECT * FROM sys.master_files
SELECT * FROM tempdb.sys.database_files
SELECT * FROM tempdb.sys.dm_db_file_space_usage
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.partitions
SELECT * FROM sys.indexes
SELECT * FROM sys.dm_os_waiting_tasks
SELECT * FROM sys.dm_os_tasks
SELECT * FROM sys.dm_exec_sessions
'
GO

EXECUTE AS LOGIN = 'testUser'

EXEC msdb.dbo.sp_start_job @job_name = 'test'

REVERT
GO

-- CLEANUP:
-----------
-- EXEC msdb.sys.sp_executesql N'DROP USER testUser'
-- DROP LOGIN testUser
-- EXEC msdb.dbo.sp_delete_job @job_name = 'test'

While granting the SQLAgentOperatorRole is stil a (minor) security concern, I find it less worrying than other solutions.


Differences between EDU and thread In DB2, there is this command that shows active EDUs (engine dispatchable units):
db2pd  -edus

There are two fields in the respective output: EDU ID and TID.
According to this db2pd page from the DB2 knowledge center, each is defined as follows:

EDU ID: The unique identifier for the engine dispatchable unit (EDU). Except on Linux operating systems, the EDU ID is mapped to the thread ID. On Linux operating system the EDU ID is a DB2 generated unique identifier
TID: Thread identifier. Except on Linux operating systems, the thread ID is the unique identifier for the specific thread. On Linux operating systems, this is a DB2 generated unique identifier

I wanted to know what is considered EDU or thread on a Linux/Unix operating system? Are they the same? What is the difference?

EDU is an abstraction over the underlying OS scheduling unit, a sort of a surrogate key assigned to whatever is doing actual work in the OS. If the OS has true threads, distinct from processes, such as Windows, an EDU will map to a thread. On modern Linux, which doesn't have threads but rather lightweight processes (LWPs), an EDU would map to one of such LWPs and the thread ID value would be artificial.

On some operating systems there is an extra level of abstraction in the form of separately managed user and kernel threads; in such cases an EDU will be mapped to a distinct kernel thread ID (KTID) in addition to the user TID.


T-SQL DATEDIFF_BIG - 469ms diff with currentmills and documented start-end constraints incorrect? (NB I don't count as a db admin... I'm just using one, so please assume zero other knowledge)

My reference date is '02/04/2017 18:00:00.124', and for no particular reason, whilst I need the time difference in milliseconds since some reference point, I picked ntp epoch of 1/1/1900 00:00.

I compare the result with the calculation of CurrentMills

SQL gives (code to follow) 3700144800124, Current Mills (with the extra 124ms added by hand) gives 3700144800593; the difference is 469ms.

Why is there a 469ms difference and why does DATEDIFF_BIG work at all when  the documentation says it is limited to ~24 days?

I also did a comparison with Unix epoch conversion, respecting the stated limits (as shown) and the difference is also 469ms s CurrentMills.

Can someone explain these results?

SET DATEFORMAT dmy;
GO
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124';
DECLARE @ntpEpochBase datetime2 = '01/01/1900 00:00:00.000';
SELECT DATEDIFF_BIG(millisecond, @ntpEpochBase, @datevar1);
GO
-- result 3700144800124
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124';
DECLARE @unixEpochBase datetime2 = '01/01/1970 00:00:00.000';
SELECT DATEDIFF_BIG(millisecond, @unixEpochBase, @datevar1);
GO
-- result 1491156000124
DECLARE @datevar1 datetime2 = '02/04/2017 18:00:00.124';
DECLARE @unixEpochBase datetime2 = '01/01/1970 00:00:00.000';
SELECT DATEDIFF_BIG(second, @unixEpochBase, '02/04/2017 00:00:00.000')*1000 + DATEDIFF_BIG(MILLISECOND,'02/04/2017 00:00:00.000','02/04/2017 18:00:00.124');
GO
-- result 1491156000124

Has there been an (un)documented improvement in DATEDIFF_BIG capabiliites?

Setup is:

    Microsoft Sequel Server Express (64 bit), v14.0.2002.14
    Microsoft SQL Server Management Studio                      14.0.17277.0 Microsoft
    Analysis Services Client Tools                      14.0.1016.262 Microsoft Data
    Access Components (MDAC)                        10.0.17134.1 Microsoft MSXML                        3.0
    6.0  Microsoft Internet Explorer                        9.11.17134.0 Microsoft .NET Framework                       4.0.30319.42000 Operating System                        6.3.17134

The documentation for datediff_big is wrong. The limits specified is for datediff.

The millisecond value of 593 that you see is an artefact of the site CurrentMills that get the datetime value of now with millisecond precision and the keeps that value.

Use this link https://currentmillis.com/?now multiple times and navigate to 1970-01-01 and see that you get different rest values each time.


Restore server instance using logical backup and wal files Is it possible to restore a database instance using logical backup and wal files?

A senior SQL Server DBA asked me to implement below scenario in PostgreSQL

took the logical backup using pg_dumpall of the master then do failover after some time.
Now restore database instance using the logical backup of primary + wal files of primary + wal files of secondary.

Yes it is possible the logical backup will be having data before failover say u took it on 3pm it will have complete data till 3pm

And suppose you fail-over the master say on 4 pm the wal-files will have records of changes done from 3pm to 4pm.

So just create a postgresql instance with help of pg_createcluster
and inserted the dump file first.

then copy the wall files to /var/lib/postgresql/9.1/main/pg_xlog/
path which is default path for most postgresql installation.
then finally
restart the services service postgresql restart


Keywords and reserved words: What's a poor boy to do? We have been using MySQL since its beginnings. Back then we thought that descriptive column names were a good idea. So we created columns like date, open, high, low, close, volume, signal and so on that reflected the terms we actually used in our work. Some of which were keywords or reserved words and some of which became reserved words, like signal. As the years rolled by many programs in a wide variety of languages were written to use those tables. I gather you can see where this is going. In the step up from 5.0 to 5.5 MySQL included signal as a reserved word, which effectively means that we can no longer upgrade as many programs access the column signal and recoding would be a HUGE effort. We'd really like to upgrade and, now that Oracle has its hands on MySQL, we would also like to consider a move to MariaDB. So, what's a poor boy to do? Any thoughts/ideas greatly appreciated.

The best answer is what @akuzminksy mentioned: escape the fields.
Since you indicate doing so would be prohibitive in terms of amount of work required, there might be another solution: a pre-parse query rewrite plugin

A preparse query rewrite plugin has these characteristics:

The plugin enables rewriting of SQL statements arriving at the server before the server processes them.

The plugin receives a statement string and may return a different string.

Of course, that requires upgrading to 5.7! And the effort required to build this custom plugin might also be prohibitive.
Other tools that offer query rewriting capabilities are:

ProxySQL
MaxScale
ScaleArc

Since these tools are proxies, all rewriting is done 'preparse'.
Disclaimer: I make no claims that this will actually work, but is the only path forward I see besides code overhaul.


Assistance creating SQL query in Oracle to split by weeks I am building a report to show order statistics by week within our company.

Here is the SQL query that I've created:

Select Sum(ORDER_TOTALS.ORDER_TOT_NET)  As "ORDER_TOT_NET",
       Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT",
       Sum(ORDER_TOTALS.ORDER_TOT_QTY)  As "ORDER_TOT_QTY",
       Sum(ORDER_TOTALS.ORDER_TOT_SQM)  As "ORDER_TOT_SQM"
From ORDER ORDER,
     ORDER_TOTALS ORDER_TOTALS
Where ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NO
And   ORDER.DATE Between '14-DEC-15' And '18-DEC-15

As you can see this will show results for orders that were created last week however I do not understand now how to make it more advanced so that we can get each week of the current year for example.

Here are the current results:

ORDER_TOT_NET  |  ORDER_TOT_WGHT  |  ORDER_TOT_QTY  |  ORDER_TOT_SQM
1020304050.00  |  4567890         |  92328493       |  3923948

What I want is something around this idea:

          ORDER_TOT_NET  |  ORDER_TOT_WGHT  |  ORDER_TOT_QTY  |  ORDER_TOT_SQM
Week 1    1020304050.00  |  4567890         |  92328493       |  3923948
Week 2    1020304050.00  |  4567890         |  92328493       |  3923948
Week 3    1020304050.00  |  4567890         |  92328493       |  3923948

How could/should I create it so that I have these results but for Week 1 through to the current week? Is this something that I should build directly in to the SQL query?

I am using DBxtra reporting program.

To get the week number of a date use:

to_char(sysdate, 'WW')

You can group on this:

select   to_char(ORDER.DATE, 'WW') as WEEK
         Sum(ORDER_TOTALS.ORDER_TOT_NET)  As "ORDER_TOT_NET",
         Sum(ORDER_TOTALS.ORDER_TOT_WGHT) As "ORDER_TOT_WGHT",
         Sum(ORDER_TOTALS.ORDER_TOT_QTY)  As "ORDER_TOT_QTY",
         Sum(ORDER_TOTALS.ORDER_TOT_SQM)  As "ORDER_TOT_SQM"
From     ORDER ORDER, ORDER_TOTALS ORDER_TOTALS
Where    ORDER.ORDER_NO = ORDER_TOTALS.ORDER_NO
group by to_char(ORDER.DATE, 'WW');

Instead of WW you can also use IW. The last one gives the ISO Week. Here the beginning or the end of the year might end up in the week of the previous/next year. Look for it in Wikipedia (https://en.wikipedia.org/wiki/ISO_8601) for more information on this.


varchar(255) field or hash field? To consider a table with millions records and the table schema:

CREATE TABLE `foos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `foo` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `foo_UNIQUE` (`foo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Ton of this query executes every second

SELECT 1 FROM foos WHERE foo=?

If no record finds, this query will be executed:

INSERT INTO foos(foo) VALUES(?)

The average length of foo less than 20. To optimize performance, a hashing field is considered to add with md5(foo) and drop the unique key.

ALTER TABLE foos DROP INDEX foo_UNIQUE;
ALTER TABLE foos ADD INDEX `foo_IDX` (`hash`);

And the query will be changed as:

SELECT 1 FROM foos WHERE hash=?

If no record finds, this query will be executed:

INSERT INTO foos(foo, hash) VALUES(?, ?)

The Question is:  Will the SELECT query run faster, since average length of foo less than 20 but hashing field length is always 32?

The answer is NO.  Today, Jehad's answer is wrong.  It may have been correct for some older version of MySQL and for some particular Engine (such as MEMORY before Version 8.0).

Today, Data and indexes are packed VARCHAR, they do not use the max length.  I verified with 5.6 with InnoDB and MyISAM.

That is, a VARCHAR column that averages 20 bytes will take only that much space.  However, if you experiment with this in InnoDB, the numbers will be quite confusing.  Here are some reasons why the sizes you get will be more than (20 * num-rows) in InnoDB:

Overhead for each row (in data and in index): 20-30 bytes
Overhead for each block: 10-20%
Overhead for BTree block splits: 40%.  This may not be needed if you insert rows in order or if the Change Buffer effectively sorts for you.
Allocation units:  At first, InnoDB allocates 16KB, but it quickly switches to 16*16KB, then 4MB.  So SHOW TABLE STATUS will show much bigger numbers for Data_length and Index_length, and the excess will not be included in Data_free.

A negative for Hashes:  They are notoriously "random".  That is, caching (the buffer_pool or key_buffer) becomes useless once the data (or index) grows much beyond the cache size.

If you are going to use an MD5 (or SHA1), pack it into BINARY(16) (or (20)) using HEX(). to save space.  Even doing that is not enough for me to recommend hash over an avg of 20 bytes in a VARCHAR.

Another tip:  Consider dropping the AUTO_INCREMENT and promoting the VARCHAR (or hash) to be the PRIMARY KEY.  In many (not all) situations, performance and space consumption both improve.


What is the difference between SET NOEXEC ON and SET PARSEONLY ON? Currently, SQL server supports the following statements: SET NOEXEC and SET PARSEONLY.
From the documentation:
SET NOEXEC ON:

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them.

SET PARSEONLY ON:

When SET PARSEONLY is ON, SQL Server only parses the statement.

I guess it's "compiles" vs. "parses". "Compiles" should include "parses".
If I want to verify the correctness of SQL Scripts which one should I use? I am inclined to answer: SET NOEXEC ON.
But what else does compile do? Does "compile" mean the execution plans are created/modified? Is the existence of objects checked?
What happens if you have:
SET NOEXEC ON
SET PARSEONLY OFF

I find having both options available confusing.
Thanks

The order of the tasks in executing a SQL query are parsed, compile and execute.
PARSE
SQL Server parses the syntax of a T-SQL. For a successful query execution. It should have valid syntax. It also checks for the variable declaration, query identifiers.
COMPILE
SQL Server checks for the objects, whether they exist or not. It also checks user permissions. During the compilation phase, SQL Server also optimizes the query execution and comes with an optimized execution plan using available indexes and statistics
EXECUTE
Once SQL Server completed parse and compilation, SQL Server executes query batch and returns output to the client
So if you have the following settings the engine do parse and compile tasks but not execute.
SET NOEXEC ON
SET PARSEONLY OFF


visual studio database project - publish did not include drops but schema compare did I just observed a difference in the result of publish vs schema compare.  I made changes to visual studio database project - some of which involved deleting tables.

Pushed changes to VSTS, build successful, release successful to Azure DB
Inspected said Azure DB.  Tables were not dropped that were deleted in the project.  Double checked the publish script - this didn't include any of the drops.
Ran SchemaCompare - this exposed all of the missing drops, and when executed gave me the result I expected (the applicable tables were dropped).

The same results were observed on local DB when manually publishing vs schema compare.  What am I doing incorrectly?  I thought publish would drop the tables.

Publish>Advanced Settings>Drop>"Drop objects in target but not in source"

https://stackoverflow.com/questions/1799802/dropping-a-table-with-visual-studio-2010-database-project


CASE with CHARINDEX and CONVERT I have a column of characters in SQL Server 2014 that I have to convert to numeric and give them the opposite value (- to + and + to -).
The data looks like this:

 -0000000000002999
000000000000002999
000000000000002459
 -0000000000002459
000000000000000437

I need it to look like this:

29.99
-29.99
-24.59
24.59
-4.37

This is the query i'm trying to use but it makes them all positive!
select

CASE
  WHEN CHARINDEX('-', o.PMT_AMOUNT) > '0'
     THEN CONVERT(numeric(18), Right(o.PMT_AMOUNT,17))* -.01
  ELSE CONVERT(numeric(18), (o.PMT_AMOUNT)) * .01
END from OPS_PMT_RECON_CONV o

Here is a more concise way to write this query without conditionals (SQLfiddle demo, but I guessed on the data type):

SELECT
  PMT_AMOUNT = -1 * CONVERT(NUMERIC(20,2), STUFF(o.PMT_AMOUNT,LEN(o.PMT_AMOUNT)-1,0,'.'))
FROM dbo.OPS_PMT_RECON_CONV AS o;

Or even simpler, as @ypercube suggests:

SELECT
  PMT_AMOUNT = -0.01 * CONVERT(NUMERIC(20,2), o.PMT_AMOUNT)
FROM dbo.OPS_PMT_RECON_CONV AS o;

A couple of suggestions:

Store the number in the database, instead of this awkward string representation. What is the point of persisting the number this way, even if you can't control how it comes to you? Converting to a number first, and storing it that way, will prevent you from storing junk in your database and end users getting burned for it.
Specify both precision and scale when defining numeric. This post is primarily about varchar, but applies equally to other types that accept parameters.
Always specify the schema.
Always terminate statements with semi-colons.


PostgreSQL 10: deleting foreign key locks selects I'm trying to delete a table (called incoming) in a busy database with 100s of transactions per second. The table itself is empty (performed a truncate table before). If I try to do drop table incoming a lot of transactions on various other tables are waiting for drop table to finish - obviously not good.

I began deleting indexes, constrains and columns 1 by 1 to identify what is blocking the table deletion and got to a foreign key constraint that was causing the lock. Here's the 'final' incoming table schema:

create table incoming
(
    account_id integer
        constraint incoming_account_id_fkey
            references account
);

From the blocked queries (taking the ticket table below as an example) it doesn't look like the account table queries are blocked but the queries on ticket - a busy table that has a foreign key to the account table. Why would deleting a FK in incoming block queries in ticket?

Here's the outline again on the 3 tables:

incoming - this is where the foreign key I want to delete lives (see schema below).
account - foreign key reference table.
ticket - busy table (select, insert, update, etc..) that has a foreign key constraint to account.

What I tried doing:

I tried disabling the triggers on the table: ALTER TABLE incoming DISABLE TRIGGER ALL; and making the FK DEFERRABLE: ALTER TABLE incoming ALTER CONSTRAINT incoming_account_id_fkey DEFERRABLE;. Didn't help.

Here's the output of the pg_constraint:

SELECT conname, conindid::regclass, confrelid::regclass, * FROM pg_constraint WHERE conrelid = 'incoming'::regclass:

Foreign key constraints are implemented as system triggers in PostgreSQL.
This query shows you the triggers behind your foreign key constraint:
SELECT t.tgrelid::regclass AS table_name,
       t.tgname AS trigger_name,
       concat(
          CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE ' WHEN 64 THEN 'INSTEAD OF ' ELSE 'AFTER ' END,
          CASE WHEN t.tgtype & 4 > 0 THEN 'INSERT' END,
          CASE WHEN t.tgtype & 8 > 0 THEN 'DELETE' END,
          CASE WHEN t.tgtype & 16 > 0 THEN 'UPDATE' END
       ) AS fires,
       f.proname AS trigger_function
FROM pg_trigger AS t
   JOIN pg_proc AS f ON t.tgfoid = f.oid
   JOIN pg_constraint AS c ON t.tgconstraint = c.oid
WHERE c.conname = 'incoming_account_id_fkey';

 table_name |         trigger_name         |     fires     |   trigger_function
------------+------------------------------+---------------+----------------------
 account    | RI_ConstraintTrigger_a_62074 | AFTER DELETE  | RI_FKey_noaction_del
 account    | RI_ConstraintTrigger_a_62075 | AFTER UPDATE  | RI_FKey_noaction_upd
 incoming   | RI_ConstraintTrigger_c_62076 | AFTER INSERT  | RI_FKey_check_ins
 incoming   | RI_ConstraintTrigger_c_62077 | AFTER UPDATE  | RI_FKey_check_upd
(4 rows)

You see that two of these triggers are defined on the target table, account.
If you drop table incoming, that will also drop the foreign key constraint and consequently drop the triggers on account, which requires a short ACCESS EXCLUSIVE lock on account.
That should not be disruptive, but it seems that you are running long transactions that involve account. Now the DROP TRIGGER on account has to queue behind the currently active transactions that hold locks on account, and all later transactions have to queue behind the DROP TRIGGER, which is what you observe.
You will have to find a time when there are no long running transactions involving account, then your DROP TABLE will finish quickly without blocking other sessions.
Side remark: choose bigint rather than integer for artificial primary key columns.


PostgreSQL 11 Windows 10 Truncates All Tables on Shutdown i have a new Windows 10 laptop with beefed up processors, 16GB RAM and 2TB SSD storage. I have installed PostgreSQL 11.2 on this laptop, and use it to compile large datasets from various sources and run various analyses and reports against this data.

I had been running PostgreSQL 9.x on my older Windows 7 laptop for years with no real issues. But on my new laptop, it seems that every time I shut down the laptop and go back into the database, every one of the tables have been truncated. All of the tables are still there, but just plumb empty.

Is there a configuration setting that needs to be changed somewhere?

Solution

  Remove the UNLOGGED option from all table create commands

Result

  The data no longer disappears during system shutdown or reboot

I had been using CREATE UNLOGGED for a long time, as I found that in earlier versions of PostgreSQL, it made a HUGE impact in the execution time of my SQL scripts. In one case, it dropped from 15 minutes to around 45 seconds. Every once in a great while, like once or twice a year, I would find the tables had emptied out. I would just go ahead and reload the datasets, never really thinking more about it.

But when I migrated my datasets to PostgreSQL 11.x, I found that the tables emptied out every time I shut down the laptop. Thanks to the comments above, I learned that the UNLOGGED option was indeed the culprit here. I removed that option from all the table creation commands, and the data no longer disappears during reboot.

Added Benefit

  I did not find any appreciable decline in performance while doing this i PostgreSQL 11.x!


Checking for duplicate entries for Column A - Table A based on user_id from Table B I have two tables which have a 1 - 1 relationship.

Table A - Skill_id
Tabel A - Entry_id

Table B - Entry_id
Table B - Author_id

The two entry_id columns are a primary key - foreign key.

I need to check if a user has been allocated the same skill more than once, i.e. if there are duplicates. How can I achieve this?

Do a GROUP BY aggregation and see if the aggregated count is greater than 1

SELECT B.Author_id,A.Skill_id,COUNT(1) SkillEntryCount
FROM tableB B INNER JOIN tableA A USING (Entry_id)
GROUP BY B.Author_id,A.Skill_id
HAVING COUNT(1) > 1;

or collect all entry_id values for each (Author_id,Skill_id) and see it is has commas

SET group_concat_max_len = 1048576;
SELECT * FROM
(
  SELECT B.Author_id,A.Skill_id,GROUP_CONCAT(A.Entry_id) EntryIDs
  FROM tableB B INNER JOIN tableA A USING (Entry_id)
  GROUP BY B.Author_id,A.Skill_id
) AA WHERE LOCATE(',',EntryIDs) > 0;


Connecting to Google Cloud SQL via SSH The network I'm using doesn't allow port 3306 so I can't connect to Google Cloud SQL. How do I set up a proxy server (on a different network that allows 3306) that I could connect to via SSH to communicate with GCS directly using Navicat/MySQL Workbench?

To expand on Dan's answer with some detailed info:

If you have the option setup a small Linux server at home. You might have to modify the sshdconfig to allow for setting up SSH tunnels depending on your release. You might also need to modify the firewall config to allow port 22 connections, though most distributions do this automatically now when you install openssh.

You can use a dynamic DNS service in case your home IP address changes and you can still access remotely. For that I actually prefer to use freedns.afraid.org and setup a sub-domain off of a publicly available domain they host. Then added a cron job on my Linux server to run once per hour and hit a special link that keeps the IP for that sub-domain updated. Many home WiFi routers actually support one or more dynamic DNS services directly within the config.

You'll need to setup a port forward on your home router as well. Setup an external connection port forwarded to your Linux server's IP address on port 22. The specifics of doing this is highly dependent on which router you're using.

IMPORTANT
Whatever you do don't setup the port forward to expose port 22 externally. Hackers/Script-Kiddies constantly scan for port 22. When they find it they'll start trying to hack root's password. You'll notice this happening if you look in /var/log/secure and see the file is huge. I did this once and within just a few hours I saw connections and hack attempts already occurring.

So use a non-standard port (like 5828 or something else equally as random). Just keep it above 1024 and below 32767.

On Windows a very good SSH client is PuTTY (www.chiark.greenend.org.uk/~sgtatham/putty). In the connection setup put your internet hostname (from either the dynamic DNS service you chose or through the freedns service I mentioned). Set your port to the one you chose in your port forward.

Then go to the SSH/Tunnels section.
On that screen leave the top two check boxes unchecked (no need to allow remote connections).
Enter a source port (3306 is fine if you don't have MySQL installed on your client machine).
For the Destination enter "gcs-host:3306".  "gcs-host" meaning the Google Cloud Services hostname.
Then for the two radio button options below that, Local and Auto.

Save your connection info and connect.

Now you should be able to start MySQL workbench and use "localhost:3306" as the server name.


sp_Blitz does not warn "No Alerts for Corruption" if one of 3 is set I was just setting up the Alert for Corruption.

Error 823
Error 824
Error 825

Before setup:

I just by chance added alert for Error 825 and ran sp_Blitz again. And the message for Finding "No Alert for Corruption" was not displayed for Error 823 and 824 !!

After Alert setup:

And I verified by only setting up 823 and ignoring 824 and 825, still the sp_Blitz does not report for other 2 missing (824 and 825) !!

Correct. The finding is "No Alerts for Corruption". If you've set up alerts for any of those, we assume you know what you're doing. (That's not always the case, of course, heh.)

If you'd like to change that, here's the guide on contributing to sp_Blitz.

(Disclaimer: I'm Brent Ozar, the script author.)


Grant postgres superuser to linux account On my development (Ubuntu linux) laptop I have a postgres OS account, which owns the Postgres installation.

When I want to perform any postgres activities, create/drop databases etc, I must first su to the postgres account.

$ sudo su postgres

How can I alter my own OS account to have the OS-level privilages of postgres, so I will not need to su ?

Create the user on the OS

# Identify yourself as root
su -

# Create the user who will have access to a postgres database
useradd mypostgresuser

# Add a password
passwd mypostgresuser

Give local users access to postgres

You need to locate the data directory for your postgresql install, i.e. where you have created the database files. They are typically located in /var/lib/pgsql/data
The value for your install might be available in the environment variable $PGDATA

# Make sure that local users can access postgres
cat /${PGDATA}/pg_hba.conf

# this was the default setting on my 8.4 install
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all

if you make any changes, reloading postgres will be necessary

/etc/init.d/postgresql reload

Or as postgres

pg_ctl reload -D ${PGDATA}

Now connect to psql as postgres

# Create the user in postgres
postgres=# create user mypostgresuser;
CREATE ROLE

# Give that user access to a database
postgres=# grant all privileges on database mytestdb to mypostgresuser;
GRANT

Test the connection

# Identify yourself as mypostgresuser
su - mypostgresuser

# Connect to the database
psql -d mytestdb


Implications of a second index on a timestamp field in a sequential table I have a MySQL table of a few gigabytes with roughly ~100M rows. I store data sequentially, such that the timestamps increase as the ID also increases. Because ordering and filtering by the timestamp is so tremendously slow, I often simply use the ID to filter date ranges. If I know that Wednesday's data started at ID=87000000 and that Thursday's data started at ID=90000000, I can find all of Wednesday's data by filtering between those two IDs.

Everything works fast enough, except for the part where I need to find the ID range. This part requires filtering by the timestamp, and that is the slow part.

My question is this: If I add a second index on the timestamp field, what would be the implications? Would my insert rate be severely impacted? Would the size of my table increase dramatically? Would this improve performance enough to justify the action? If I were to add this index, would I even need to use the ID range trick anymore?

I've also been toying around with the idea of creating a function that performs a binary search on the table to find an ID near a target time. My assumption is that MySQL scans and compares the most of the table contents with the target time. This means, on average, 50000000 rows scanned. A binary search would take, at worst, Log2(100000000)=~27 queries for a single ID.

Any comments would be appreciated. I'll get to work on my binary search algorithm and post the results.

EDIT: My schema is:

CREATE TABLE `tracker_snapshot` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tracker_id` int(11) NOT NULL,
  `time` datetime NOT NULL,
  `value1` decimal(13,4) NOT NULL,
  `value2` decimal(13,4) NOT NULL,
  `value3` decimal(13,4) NOT NULL,
  `value4` decimal(13,4) NOT NULL,
  `value5` decimal(13,4) NOT NULL,
  `value6` decimal(13,4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tracker_snapshot_c9837659` (`tracker_id`),
  CONSTRAINT `tracker_id_refs_id_68c52750` FOREIGN KEY (`tracker_id`) REFERENCES `tracker_tracker` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=77107026 DEFAULT CHARSET=utf8

Implication is that you need ~1144Mb extra storage to index column time for ~100M rows...

Because the InnoDB engine wiil store the data off an PRIMARY or UNIQUE index within an non PRIMARY or UNIQUE index, as result your secondary index will be become larger

How much larger?? you can calculate it with this formula

int = 4 bytes
datetime = 8 bytes

100000000 records * (4 + 8 bytes) =
100000000 * 12 bytes ~ 1200000000 bytes ( 1144.40918 Mb ) extra storage (note index records/page overhead are not in the calculation)

An larger index size will slow down inserts, delete and only updates when you update an value whats indexed..
An larger index size in thoery can slow down selects because off the InnoDB index page off 16K (read http://www.ovaistariq.net/733/)
But still it depends on innodb configuration and cached data within the innodb buffer pool..

Or maybe you can use your approach by using an lookup table

CREATE TABLE tracker_snapshot_lookup (
    tracker_date DATE NOT NULL
  , tracker_snapshot_start_id INT UNSIGNED NOT NULL
  , tracker_snapshot_end_id INT UNSIGNED NOT NULL

  , PRIMARY KEY(tracker_date)
  --   Covering index below is overkill...
  -- , PRIMARY KEY(tracker_date, tracker_snapshot_start_id, tracker_snapshot_end_id)
) ENGINE = InnoDB;

insert into tracker_snapshot_lookup values('2013-11-13', 1, 10000);
insert into tracker_snapshot_lookup values('2013-11-14', 10001, 20000);

If you use an JOIN or deliverd table the MySQL optimzer can use in worse case

1 index key (Random disk I/O) lookup on tracker_snapshot_lookup.date (assuming with WHERE tracker_date = '2013-11-13' )
1 table (Random disk I/O) record key for tracker_snapshot_start_id and tracker_snapshot_end_id (not necessary when you make it an covering index)

Based on tracker_snapshot_start_id and tracker_snapshot_end_id MySQL will most likly choose an range scan (sequential disk I/O what is low costing with I/O waittime) on the tracker_snapshot table.

Your savings

DATE            3 bytes
INT NOT NULL    4 bytes

So in one year you lose on storage...

Table data

356 days * (3 + 4 bytes)
356 * 12 = 4272 bytes ( 0.004 Mb )

Index data

356 days * (3 bytes) = 1068 bytes ( 0.001 Mb )

It's magic because you use that ~1143Mb storage space for more important data


Nested json response from 3 one to many tables I am designing data model in following way:

  Table 1:
          id: number
         somedata: String
         somedata: String

  Table 2:
         id: number
         Table1_id: number
         somedata: String
         somedata: String

  Table 3:
        id: number
        Table2_id: number
        somedata: String

1st table is joined to 2nd table with one to many relationship. 2nd table is joined to 3rd table with one to many relationship. I am using java, JPA. I have to return JSON via rest api in following format:

{
  "table1_id": 1,
  "somedata": "somedata",
  "table2_data": [
    {
      "table2_id": 1,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 1,
          "somedata": "somedata"
        },
        {
          "table3_id": 2,
          "somedata": "somedata"
        }
      ]
    },
    {
      "table2_id": 2,
      "somedata": "somedata",
      "table3_data": [
        {
          "table3_id": 3,
          "somedata": "somedata"
        },
        {
          "table3_id": 4,
          "somedata": "somedata"
        }
      ]
    }
  ]
}

If I use mysql and above table structure I will need to use minimum 3 database calls to fetch from 3 tables separately and will have to construct my json. Is there a way where I can reduce database calls and also reduce effort to construct my json? I am open to change database(mysql) also. I just need fast, optimised and best solution. Note that table1, table2 and table3 has different fields so I can not denormalize them to single table.

I already have seen this question and my question is different than this: https://stackoverflow.com/questions/11047151/nested-json-from-3-one-to-many-tables

In PostgreSQL:

SELECT
    row_to_json (r1.*)
FROM
    (
        SELECT
            table_1_id,
            somedata,
            array(
                SELECT
                    row_to_json (r2.*)
                FROM
                (
                SELECT
                        table_2_id,
                        somedata,
                        array(
                            SELECT
                                row_to_json(r3.*, true)
                            FROM
                            (
                                SELECT
                                    table_3.table_3_id,
                                    table_3.somedata
                                FROM
                                    table_3
                                WHERE
                                    table_3.table_2_id = table_2.table_2_id
                                ORDER BY
                                    table_3_id
                            ) r3

                        ) AS table_2_data
                  FROM
                        table_2
                 WHERE
                        table_2.table_1_id = table_1.table_1_id
                 ORDER BY
                        table_2_id
                ) r2
           ) AS table_2_data
        FROM
            table_1
    ) AS r1

(with a few caveats, because your first level response is not an array; and I think it should).

The syntax is a bit convoluted and can probably be simplified

Test it at http://rextester.com/SEZ32413


How to SELECT with two CSV files/... on PostgreSQL? I have two CSV files which you can insert into the following PostgreSQL schema.
However, I cannot \COPY the data into the tables.
Pseudocode

Let data_id=1
Show name and event_value from data.csv and log.csv, respectively
Do this request from R with connection to PostgreSQL

Pseudocode in other words but not correct syntax

SELECT name, event_value
    FROM data, log
    WHERE data_id=1;

CSV files

I enclose the header in double quotes in CSV files (thread PG COPY error: invalid input syntax for integer).
data.csv where two integer/SERIAL + integer

"data_id", "event_value"
1, 777
1, 666
2, 111
4, 123
3, 324
1, 245

log.csv where integer/SERIAL + string so I put enclosed the latter one in double quotes

"data_id", "name"
1, "leo"
2, "leopold"
3, "lorem"

PostgreSQL

Schema and workflow

DROP TABLE IF EXISTS data, log;
CREATE TABLE data (
        data_id SERIAL NOT NULL, -- no primary key possible here, because possible have many events per data id
        event_value INTEGER NOT NULL
);
CREATE TABLE log (
        data_id SERIAL PRIMARY KEY NOT NULL,
        name INTEGER NOT NULL
);

-- https://stackoverflow.com/a/2987451/54964
-- https://soleil4716.wordpress.com/2010/08/19/using-copy-command-in-postgresql/
-- joanolo, HEADER
\COPY data FROM '/home/masi/Data/data.csv' DELIMITER ',' CSV HEADER;
\COPY log FROM '/home/masi/Data/log.csv' DELIMITER ',' CSV HEADER;

Work flow pseudocode

Start from clean table (drop old tables) and Create new tables
Insert two CSV files into the tables
Do SELECT now with connection to PostgreSQL from R

Output

masi@masi:~$ psql -f schema.sql
psql:schema.sql:1: NOTICE:  table "data" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE TABLE
COPY 6
psql:schema.sql:14: ERROR:  invalid input syntax for integer: " leo"
CONTEXT:  COPY log, line 2, column name: " leo"

I should not have mistakes in CSV files, based on the thread PG COPY error: invalid input syntax for integer.

OS: Linux Debian 8.7
PostgreSQL: 9.4
Related: R sqldf/... approach in the thread How to select on CSV files by R sqldf/data.table/dplyr?

Error is obvious since you're intending to add characters to integer defined column. Just change datatype for name column in log table from integer to varchar(10)


Why are some write requests throttled whereas the average consumed write capacity is below the provisioned write capacity? I see the following graphs in one of my DynamoDB table:

Between 2/15 9:30 and 2/15 10:00 some write requests were throttled while the average consumed write capacity was below the provisioned write capacity. How comes?

It doesn't have to be a hot key or partitioning issue where the total provisioned write capacity is spread across the partitions.

You have a 5 minute average chart there. Technically you could have a case where you do have zero write activity for a 4:50 minute period and then have 10 seconds of ridiculous amounts of writes. These will most likely exceed you provisioned writes while the average still is below the limit.


SQL Server 2008: The database [dbName] is not accessible I got this error from another person and I've run out of ideas. I can access this database without any problems, the database is in MULTI_USER mode, the person receiving the error is a user, etc.

The database is READ_ONLY and the user in question has previously used this database and only has database read permissions, so there's no chance of settings being tampered with.

Yesterday, I added a few functions and had to set to SINGLE_USER and READ_WRITE, but I've triple checked that the db has been set back to MULTI_USER. I can't decide if that's coincidence or causality. Either way, I'm at a dead end.

Here's the error report:

The database [dbName] is not accessible. (Object Explorer)

----------------------------------------
Program Location:

    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.DatabaseNavigableItem.get_CanGetChildren()
    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest Request)
    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

Any experience with this kind of error?

Typically this is caused when the user's default database is set to a database they don't have permissions in, or it's offline.

For example, say I set you up a login in the Sales database, and then I right-click on your login in SSMS and change your default database to master - but you don't have any permissions in Master. When you try to connect, even though you want the Sales database, you'll go into Master by default, and get this error.

This can also happen if someone takes away your permissions in Sales and doesn't change your default database to something else.

This is why I love setting all users' default databases to TempDB. User databases can come and go, but TempDB will always be there. Plus if somebody accidentally creates an object, at least it's in a throwaway database instead of something like Master or Sales.


Data type for protobuf objects in mysql I would like to store some protobuf objects in a MySQL database. The objects are created in a Java application. The object is accompanied with a textual type identifier and a timestamp.

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| type       | varchar(30)  | NO   | MUL | NULL    |                |
| timestamp  | date         | NO   | MUL | NULL    |                |
| pixel_data | ?            | NO   | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

What should be the datatype of pixel_data?

My best guesses are either BLOB or TEXT.

What's the difference between TEXT and BLOB? Which one is better for Java serialization of protobuf objects? Since Java serializes using byte streams, I assume that BLOB is better, but I didn't find any reference for that.
Assuming that I can only estimate object sizes, which quantifier should I choose (TINY, MEDIUM or LONG)? How much space is wasted for the larger sizes?

BLOB is correct, as that is a binary string. TEXT is a character string, but protobuf is not character data; so use some kind of BLOB

As for TINY/MEDIUM/LONG; how big is your data?

TINYBLOB : max 255 bytes
BLOB : max 65,535 bytes
MEDIUMBLOB : max 16,777,215 bytes
LONGBLOB : max 4,294,967,295 bytes

In some small minority of cases TINYBLOB may suffice, especially if there aren't any strings (names/addresses in particular use up space). In most cases, MEDIUMBLOB is probably a fair guess.


Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table Looking at 3 Tables - Recipient, HouseholdScores, Sales.

I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date information in the Recipient Table so i can only join the two tables on iCustomerID.

What I have Tried (Using - 'Microsoft SQL Server Management studio 17':

SELECT DISTINCT

iRecipientId as RecipientID,NmsRecipient.iCustomerId as Cust_Num,sFirstName as FirstName,sLastName as LastName,sEmail as Email,
iBlacklistPostalMail as NoMail,iBlacklistEmail as NoEmail,iBlacklistMailGCCL as DontMailGCCL,iBlacklistGCCL as DontEmailGCCL,
iBlacklistMailOAT as DontMailOAT,iBlacklistOAT as DontEmailOAT,sLastBrand as LBT,sFrequencyDetail as Frequency,sRecency as Recency,GccTbl_HouseholdScores.iNumTripsBkd as Trips,
GccTbl_Sales.tsDepart as Departure_Date

FROM ((neolane.NmsRecipient

Inner Join [neolane].[GccTbl_HouseholdScores] ON NmsRecipient.iRecipientId = GccTbl_HouseholdScores.iHouseHoldId)
Inner Join [neolane].[GccTbl_Sales] ON NmsRecipient.iCustomerId = GccTbl_Sales.iCustomerId)

WHERE iIrateFlag= 1
    AND iOperationalFlag= 1
    AND iEmployeeFlag= 0
    AND iDeceased= 0

Order By iblacklistPostalMail,iBlacklistEmail,iBlacklistMailGCCL,iBlacklistGCCL,iBlacklistMailOAT,iBlacklistOAT;

Results are bringing back Duplicate iCustomerID where i only want 1 instance for each with the Most Recent Departure date

If the only two references from the table GccTbl_Sales are the columns iCustomerId and tsDepart, this should work:

SELECT DISTINCT

iRecipientId AS RecipientID, NmsRecipient.iCustomerId AS Cust_Num, sFirstName AS FirstName, sLastName AS LastName, sEmail AS Email,
iBlacklistPostalMail AS NoMail, iBlacklistEmail AS NoEmail, iBlacklistMailGCCL AS DontMailGCCL, iBlacklistGCCL AS DontEmailGCCL,
iBlacklistMailOAT AS DontMailOAT, iBlacklistOAT AS DontEmailOAT, sLastBrand AS LBT,sFrequencyDetail AS Frequency, sRecency AS Recency,
GccTbl_HouseholdScores.iNumTripsBkd AS Trips, GccTbl_Sales.tsDepart AS Departure_Date

FROM (neolane.NmsRecipient

INNER JOIN neolane.GccTbl_HouseholdScores ON NmsRecipient.iRecipientId = GccTbl_HouseholdScores.iHouseHoldId)
INNER JOIN (
            SELECT GccTbl_Sales.iCustomerId, MAX(GccTbl_Sales.tsDepart) AS tsDepart
            FROM neolane.GccTbl_Sales
            GROUP BY GccTbl_Sales.iCustomerId
            ) AS GccTbl_Sales ON NmsRecipient.iCustomerId = GccTbl_Sales.iCustomerId

WHERE iIrateFlag= 1
    AND iOperationalFlag= 1
    AND iEmployeeFlag= 0
    AND iDeceased= 0

ORDER BY iblacklistPostalMail,iBlacklistEmail,iBlacklistMailGCCL,iBlacklistGCCL,iBlacklistMailOAT,iBlacklistOAT;

And if the DISTINCT was put there in order to remove the duplicates of iCustomerID, I think you're safe to remove it now.


Azure IaaS VM - Use D:\ Drive for tempdb - NTFS Formatting 64k This was a question I was battling with earlier today and eventually figured out an answer.  Wouldn't mind something better, but wanted to provide this to whomever also needed.

First, on an Azure VM you get a D:\ drive for free which is SSD.  The caveat is that it usually gets destroyed when a VM is restarted.  MS best practice for high write volume is to use this drive for tempdb.  What they don't go into is that it's not formatted 64kb.  The hidden pagefile.sys resides here, so you need to keep that into account if you try to reformat (it'll fail).

From this:
https://cloudblogs.microsoft.com/sqlserver/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/

You'll want to alter their startup powershell script to something more like below, where I'm first removing the pagefile entirely, then formatting to 64k, creating the file as the script usually would, then putting the pagefile.sys back before starting the startup services.

$SQLService=”SQL Server (MSSQLSERVER)”
$SQLAgentService=”SQL Server Agent (MSSQLSERVER)”
$tempfolder=”D:\SQLTEMP”
if (!(test-path -path $tempfolder)) {
    (Get-WmiObject -Class Win32_PageFileSetting).Delete()
    Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage"  -Confirm:$false
    New-Item -ItemType directory -Path $tempfolder
    Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = 'D:\pagefile.sys';}
}
Start-Service $SQLService
Start-Service $SQLAgentService

Does anyone have anything better or see any holes in this process?

For a little more automation/help for others, I also script the above script out as well as the triggers to create it and run it 30s after startup using below.  This basically allows someone to automatically do the steps in the cloudblogs article I reference.

#1 - Set Services to manual startup so that windows scheduler will start after tempdb adjusted
Set-Service -Name MSSQLSERVER -StartupType Manual
Set-Service -Name SQLSERVERAGENT -StartupType Manual
IF (Get-Service MsDtsServer130 -ErrorAction SilentlyContinue)
{
Set-Service -Name MsDtsServer130 -StartupType Manual
}

<#2 - using below powershell script to create the startup script#>
$script=
'
$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMP"
if (!(test-path -path $tempfolder)) {
    (Get-WmiObject -Class Win32_PageFileSetting).Delete()
    Format-Volume -DriveLetter D -FileSystem NTFS -AllocationUnitSize 65536 -NewFileSystemLabel "Temporary Storage"  -Confirm:$false
    New-Item -ItemType directory -Path $tempfolder
    Set-WMIInstance -Class Win32_PageFileSetting -Arguments @{ Name = "D:\pagefile.sys";}
}
Start-Service $SQLService
Start-Service $SQLAgentService
IF (Get-Service MsDtsServer130 -ErrorAction SilentlyContinue)
{
Start-Service -Name MsDtsServer130
}
'

Out-File -FilePath C:\SQL-startup.ps1 -InputObject $script -NoClobber

<#3 - using below script to create a scheduled task to call a powershell script that creates a folder on the d drive.#>
$trigger = New-JobTrigger -AtStartup -RandomDelay 00:00:30
Register-ScheduledJob -Trigger $trigger -FilePath C:\SQL-startup.ps1 -Name CreateSqlFolderOnDSsd

**Edit, above assumes SQL 2016 and at least powershell 5 (though I'm unaware of specific dependencies, should work on v4 at least).

Does anyone have anything better or see any holes in this process?

As for holes, the blog post contains several, but your script appears to plug them.

As for something better, TempDB on the local SSD scratch drive is now available out-of-the-box for the SQL Server on Azure VM, along with separate storage pools for data and log.  Here's a screenshot of the storage configuration pane from the Azure Portal:

The VMs drives will be formatted as follows:

PS C:\> Get-Volume  | Format-List AllocationUnitSize, FileSystemLabel

AllocationUnitSize : 4096
FileSystemLabel    : Temporary Storage

AllocationUnitSize : 65536
FileSystemLabel    : SQLVMLOG

AllocationUnitSize : 65536
FileSystemLabel    : SQLVMDATA1

AllocationUnitSize : 0
FileSystemLabel    :

AllocationUnitSize : 0
FileSystemLabel    :

AllocationUnitSize : 4096
FileSystemLabel    : System Reserved

AllocationUnitSize : 4096
FileSystemLabel    : Windows

The recommendation here to use a 64KB allocation unit size for TempDb may only be intended for when TempDb is on remote storage, not on the local NVMe flash disk, and in any case shouldn't have a large performance impact.


Is there a way to replicate certain part of collection in Mongodb Is there away to stream certain part of collection in MongoDB or TokuMX from master server to another server? I need to duplicate only certain collection

Or if it isn't possible, are there any other alternative for database that doesn't enforce schema?

MongoDB at least to current version 2.6+ for sure does not support that as build-in functionality. I guess because a replica set using selective replication won't be consistent.

There is a feature called filtered replication in Couch but never tried it:
http://wiki.apache.org/couchdb/Replication#Filtered_Replication


Convert OLTP database to OLAP Datawarehouse (http://imgur.com/p8Gy67E).  Basically I need help to convert that OLTP Model to Data Warehouse in order to be used for further OLAP Cubes. I understand well the difference between the Star Schema and the Snowflake Schema in design , but can't figure out what tables to keep in and what tables to exclude . *Note: This OLTP Model is devoted in the Retail field and it must answer questions like the following:

How many customers are spending more over time? Less over time? Describe these customers.

Of those customers who are spending more over time, which categories are growing at a faster rate?
Of those customers who are spending less over time, with which categories are they becoming less engaged?
Which demographic factors (e.g. household size, presence of children, income) appear to affect customer spend? Engagement with certain categories?
Is there evidence to suggest that direct marketing improves overall engagement?

So , based on the above questions as I read on various data-warehouse sites and blogs , I must keep some tables that will be useful and merge other tables as possible . If anyone can help I can share the dataset description for each table and the function of each column , I would be thankful for advices and participation .

First in this case I suggest you to adopt a star schema. the  transaction_data table obviously will be you reference to construct your fact table because it contains 'measures' like quantity,sales_value and other numeric attributes , the fact table also will contain substitution-keys of dimensions tables like Time, Product, Customer,demographic, etc.


Simple select query hangs I have a simple query:

SELECT a.name AS a_name,
       b.description AS b_description
    FROM a
    JOIN b ON a.id = b.a_id
    WHERE b.table_name IS NULL;

(Names changed to protect the innocent)

This usually takes ~3ms to run. Sometimes, this query hangs. It has currently been hanging for 3 days. Luckily, I'm in a position where I can try and debug this in its current state, for a while. It appears there's nothing blocking it (found by querying pg_locks and pg_stat_activity), though it is blocking other things.

Even with the other query in a hung state, I can run the same query again, and it completes successfully in the expected time period.

Things that are likely to be involved: we currently have two cron jobs (don't ask) that dump the database. I can find the exact commands being run if it's likely to be relevant.

Any suggestions on how to debug this gratefully received!

Check the wait events in pg_stat_activity to see if your query is running or hanging.

In the latter case, examine pg_locks or use the pg_blocking_pids function to see which session blocks you.

If no locks are involved, see if the backend process consumes CPU or not. Optionally use strace to see what the backend is doing.


How to disable the logging of parameter values in PostgreSQL Is there a way to disable the logging of the parameter values used in a query, while still logging the actual query itself?

I'd like to set log_min_duration_statement = 0 to use with pgBadger. I'm getting two lines per query like:

LOG: Select * from table where id = $1

DETAIL: parameters: $1 = 55

I'd really like to suppress the DETAIL lines because it's bloating my logs and saving sensitive information.

Is it possible to disable this from within Postgres?

You can set log_error_verbosity = TERSE

The manual:

  Controls the amount of detail written in the server log for each
  message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE,
  each adding more fields to displayed messages. TERSE excludes the
  logging of DETAIL, HINT, QUERY, and CONTEXT error information.

Bold emphasis mine.


Snooping-safe Vote tables I have a table with both user_id and vote. They are stored together so each user can only give one vote. However it's also problematic because admins can simply read who voted what. Is there a way to make it so that only the system(minus all humans) and the user knows what was voted by h(im|er)self?

How about encrypting the vote using a key that only the user and/or the system knows?

Adding some more information:

Use a symmetric encryption so that you can decrypt it.
Encrypt outside the database. Do not use database encryption functions if you want to protect it from DB admin because admin has several ways to snoop into the queries and read the keys. Instead, encrypt the data in your programming language of choice, and just store the encrypted data in the database. Admin never gets access to the actual data.
You can either have one secret key per user, or one common secret key for the entire system. However, you have to keep the key secret from the DB admin.I prefer going for one key for the entire system because that requires me to maintain only one key.
If you use a single key, you should add some random data to the votes before encrypting those. This will ensure that encrypted values for votes would look different for each user. One way to do it is to prepend 5 (>0) random characters before the vote data every time before encryption, and discard first 5 characters after decryption. Other option is to add the userid before the vote data before encryption. In the absence of such randomization, the admin may be able to 'guess' and 'alter' votes.

Hope this helps.