SQL Server 2008 Multi Page Allocation When a query or stored procedure has an execution plan greater than 8000 bytes, instead of using plan cache in buffer pool, it will use MPA (multi page allocator) which used memory outside buffer pool. This behavior is changed in SQL 2012 and MPA and SPA are part of buffer pool. But I am using SQL 2008 R2 standard edition.

Does multi page allocation means two or more 8K page will be allocated at once or page larger than 8K in size will be allocated to save execution plan for the query?

In this context, multi-page allocations means SQL Server needs more than 8K of memory, and it will allocate multiple 8K pages to hold that data.


Can't connect to EC2 Postgresql instance unless I change the default port 5432 I just created a brand new EC2 instance with a default security group allowing All Traffic from Anywhere.

My listen_address is set to *

I can SSH to it but I can't connect to the pgsql instance running inside it, UNLESS I change the port to something other than port=5432.

This Setup Fails:

postgresql.conf:
port=5432
psql -h <public-ip> -p 5432

ERROR:

  psql: could not connect to server: Operation timed out`

This works:

port=6000
psql -h <public-ip> -p 6000

I tried this twice in two different AWS accounts, same behavior.

no iptables rules are present either, it's a brand new EC2 instance:

ubuntu@<public-ip>:~$ sudo iptables -L -n
Chain INPUT (policy ACCEPT)
target     prot opt source               destination

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

This doesn't make sense to me at all.

This is the version of Ubuntu I'm running:

ubuntu@<public-ip>:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.2 LTS
Release:    14.04
Codename:   trusty

Postgres version:

psql (PostgreSQL) 9.3.6

The weirdest thing it was working yesterday, no changes has been made except that I upgraded to Yosemite 10.10.3 (14D136.

EDIT:

I checked with another EC2 instance in the cloud as client, it connect, it seems to be my OSX, again, since I updated to Yosemite 10.10.3

EDIT 2:

Not just psql, none of my client apps are able to connect.

EDIT 3:

"host=<public-ip> port=5432 sslmode=disable" didn't worked neither.

Any tips?

Doing an nmap over the EC2 instance:

nmap -Pnv -p 5432 <public-ip>

revealed that my ISP is intentionally blocking the port 5432(third-world country FTW...):

Host is up.
PORT     STATE    SERVICE
5432/tcp filtered postgresql

It was never a problem with EC2 nor Postgresql in first place.


Getting the name of the column that was used in a join? This may be a bit of a weird question:

Our project's datamodel has multiple tables with multiple columns any one of which can be used to join with one settingtable. The question here is how to get the name of the column that was used to connect to the settingtable in an OR join?

For instance, let's say the query looks like this:

SELECT FIRST.ID, SEC.ID, TRD.ID, FRTH.ID, SETS.ID, SETS.VALUE
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECDONTABLE_ID = FIRST.SECONDTABLE_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRDTABLE_ID = FIRST.THIRDTABLE_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTHTABLE_ID = TRD.FOURTHTABLE_ID
-- etc...
LEFT JOIN SETTINGTABLE SETS
   ON (SETS.ID = FIRST.ONESETTING_ID OR SETS.ID = FIRST.SECONDSETTING_ID
      OR SETS.ID = FIRST.THIRDSETTING_ID OR SETS.ID = TRD.RANDSETTING_ID
      OR SETS.ID = FRTH.ONESETTING_ID
-- etc...
   )

Now, that's a very parsed example of what's really going on. We may have dozens of tables with several joinin columns on each one to the SETTINGTABLE.

Obviously, merely making a separate join for each table.column would show you the column used. Or alternately, you can use CASE clause in the select. But either one is tricky and requires a lot of manual input. Even just using all the joining ID-columns in the select would enable you to compare the ID's, but since the results need to be readable by non-technical people, that wouldn't help much.

So is there a way to use some sys objects or somesuch to simply return the column name that was actually used to connect to SETTINGTABLE? The design is what it is, and will not change. So I'm simply trying to find the best way to work with what we have. This would significantly speed up our diagnosis of some of our scripts, queries and data.

Thanks!

There is no sys object or DMV that will give you such information.  You'll have to build it yourself, for which you can have a look at the example below.

Let's prepare some tables and dummy data.

CREATE TABLE ROOTTABLE (FIRST_ID INT, SECOND_ID INT, THIRD_ID INT, ONESETTING_ID INT);
CREATE TABLE SECONDTABLE (SECOND_ID INT, SECONDSETTING_ID INT);
CREATE TABLE THIRDTABLE (THIRD_ID INT, FOURTH_ID INT, RANDSETTING_ID INT, THIRDSETTING_ID INT);
CREATE TABLE FOURTHTABLE (FOURTH_ID INT, ONESETTING_ID INT);
CREATE TABLE SETTINGTABLE (ID INT, VALUE Varchar(10));

INSERT ROOTTABLE VALUES (1,2,3,15),(1,1,1,12),(11,5,6,12);
INSERT SECONDTABLE VALUES (1,11),(2,15),(5,13);
INSERT THIRDTABLE VALUES (1,5,6,15),(6,7,8,15),(3,7,8,15);
INSERT FOURTHTABLE VALUES (5,15),(6,16),(7,17),(1,18);
INSERT SETTINGTABLE VALUES
  (11,'one'),
  (12,'two'),
  (13,'three'),
  (14,'four'),
  (15,'five'),
  (16,'six'),
  (17,'seven');

Here's the query with the MATCHES column to show where the JOIN condition was fulfilled from (SETS.ID = ?).

SELECT FIRST.FIRST_ID, SEC.SECOND_ID, TRD.THIRD_ID, FRTH.FOURTH_ID, SETS.ID, SETS.VALUE,
       STUFF((SELECT ','+SOURCE_TABLE
              FROM (VALUES
                    ('ROOTTABLE',   FIRST.ONESETTING_ID),  -- 1
                    ('SECONDTABLE', SEC.SECONDSETTING_ID), -- 2
                    ('THIRDTABLE',  TRD.THIRDSETTING_ID),  -- 3
                    ('THIRDTABLE',  TRD.RANDSETTING_ID),   -- 4
                    ('FOURTHTABLE', FRTH.ONESETTING_ID))   -- 5
                   X (SOURCE_TABLE, SOURCE_ID)
              WHERE X.SOURCE_ID = SETS.ID
              FOR XML PATH('')),1,1,'') MATCHES
FROM ROOTTABLE FIRST
LEFT JOIN SECONDTABLE SEC ON SEC.SECOND_ID = FIRST.SECOND_ID
LEFT JOIN THIRDTABLE TRD ON TRD.THIRD_ID = FIRST.THIRD_ID
LEFT JOIN FOURTHTABLE FRTH ON FRTH.FOURTH_ID = TRD.FOURTH_ID
LEFT JOIN SETTINGTABLE SETS
     ON (SETS.ID = FIRST.ONESETTING_ID    -- 1
      OR SETS.ID = SEC.SECONDSETTING_ID   -- 2
      OR SETS.ID = TRD.THIRDSETTING_ID    -- 3
      OR SETS.ID = TRD.RANDSETTING_ID     -- 4
      OR SETS.ID = FRTH.ONESETTING_ID);   -- 5

Note that I have laid out the sources that could make the match (1-5) in both the VALUES rows constructor and ON clauses so that you can follow the pattern.

Results:

| FIRST_ID | SECOND_ID | THIRD_ID | FOURTH_ID | ID | VALUE |                          MATCHES |
-----------------------------------------------------------------------------------------------
|        1 |         2 |        3 |         7 | 15 |  five | ROOTTABLE,SECONDTABLE,THIRDTABLE |
|        1 |         2 |        3 |         7 | 17 | seven |                      FOURTHTABLE |
|        1 |         1 |        1 |         5 | 11 |   one |                      SECONDTABLE |
|        1 |         1 |        1 |         5 | 12 |   two |                        ROOTTABLE |
|        1 |         1 |        1 |         5 | 15 |  five |           THIRDTABLE,FOURTHTABLE |
|       11 |         5 |        6 |         7 | 12 |   two |                        ROOTTABLE |
|       11 |         5 |        6 |         7 | 13 | three |                      SECONDTABLE |
|       11 |         5 |        6 |         7 | 15 |  five |                       THIRDTABLE |
|       11 |         5 |        6 |         7 | 17 | seven |                      FOURTHTABLE |

Some things to note:

The multiple ORs in the JOIN mean that SETS.ID could be matched by any of the sources - the query shows them all comma separated.
Rows 3-5 with the leading columns (1, 1, 1, 5) match SETS.ID three times.

SQL Fiddle

For clarity, this other SQL Fiddle shows the additional *SETTING_ID columns that are used to match against SETS.ID.


Is it a good idea to split MySQL DB into two servers I have a MySQL DB server that accepts a lot of mobile application data for analysis. I would like to know if for optimization reasons the following architecture makes sense and is appropriate:

I would like to split the MySQL DB server into two separate servers as follows:

Server A (Master) would receive the statistic data for computation.
Server B (Master) would keep

Aggregated (pre-calculated data) the Master on-demand
application
user data.

The relation b/w servers would be Master-Master.

Server A -> Server B : sending aggregated (calculated) data
Server B -> Server A : sending new application and user data so that aggregation will also be performed for newly tracked applications.

If my reasoning is correct, can you shed some light on how I might accurately do this?

OPTION #1 : Use Master/Master

You could use Master/Master only under one condition: If you write to a DB on ServerA, DO NOT ISSUE WRITES TO ServerB, and vice versa. In this way, you split writes cleanly. Splitting writes to the same DB in Master/Master can be a little clumsy if you depend on looking up rows by IDs that have the AUTO_INCREMENT property. If you look up rows by unique keys that never change from server to server, such as Social Security Number, Driver License, a HashKey and so forth, the splitting writes to the same DB between two Masters in Master/Master is fine.

OPTION #2 : Use Slave Servers

@DTest already described this, so I will add nothing additional to his suggestion (He get's a +1 for it).

OPTION #3 : Use MyISAM in the Slave Servers

When using read-only slaves that are not being used as a master for other servers, you should do two things to the data in that MySQL Instance

Convert all tables to MyISAM
Run ALTER TABLE tblname ROW_FORMAT=Fixed on all MyISAM tables

This should add 20% increase in speed for reads.

OPTION #4 : Use MySQL 5.5 for Semisynchronous Replication

Before MySQL 5.5, when a Master has Multiple Slaves, this is how an SQL statament is processed among the slaves (Example will be a Master with two slaves):

SQL Statement is

Executed in Master
Recorded in Master's Binary Logs
Passed to Slave1 Relay Logs
Executed in Master in Slave1
Passed to Slave2 Relay Logs
Executed in Master in Slave2

In MySQL 5.5, when a Master has Multiple Slaves, this is how an SQL statament is processed among the slaves (Example will be a Master with two slaves) using Semisynchronous Replication:

SQL Statement is

Executed in Master
Recorded in Master's Binary Logs
Passed to Slave1 Relay Logs
Acknowledged to Master of Receipt By Slave1
Passed to Slave2 Relay Logs
Acknowledged to Master of Receipt By Slave2

Replication is a little more robust and at least 1 Slave is More Closely Sync'd to the Master.


How to take backup of multiple DB in a specific point of time My test setup is having 3 databases in postgresql-9.6 instance, 2 DBs consumed by 2 live Web apps.
I am using pg_dump to take hot backup of all databases. In order to keep CPU load under threshold, backup script perform pg_dump in sequence.

pg_dump -U postgres -Fc <db-1> > <db-1>.dump
pg_dump -U postgres -Fc <db-2> > <db-2>.dump
pg_dump -U postgres -Fc <db-3> > <db-3>.dump

All three database share information like User details, Subscription, Task state, etc.
As the backup is taken in different time period, in the event of Data recovery, the database will end up with inconsistent data across all three DB. One of the DB size is 180GB+ and the dump time for this DB alone would take 30 min.

How to take backup of all databases from a specific time period.

I am not sure how to use the Snapshot feature in postgresql.

You can try using the --snapshot method (https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/), but it might be a bit more complicated than you'd like.

My personal recommendation is to create a cluster-wide backup with pg_basebackup (or a third-party app that basically does the same thing, like barman, pg_backrest, etc.)


Tables with the same name in the same DB (very strange bug) Have you ever seen something similar to this (picture)?

A table appears multiple times!!
I don't know exactly how this began.
Whenever I make a change (alter) to a table, a new instance appears
I've used 2 different clients (HeidiSQL, DBeaver)
and my web applications work without problems.
Any ideas
Thank you

This is the result of a known bug on Azure for MySQL. A fix was deployed last month, but you'll need to restart your server before it takes effect. After restarting, you will see the correct table information.


Agregate data from multiple rows in one row I have a SQL server DB for credit card processing system with 5 Tables

MerchantContract

PaymentKey   NVARCHAR(50)
ProcessingID INT

DealTrace

Status       NVARCHAR(250)
Tag          NVARCHAR(MAX)

DealSummmary

RefNumber    NVARCHAR(100)
DealAuthCode NVARCHAR(100)

Processing

Name         NVARCHAR(50)

And Deal with whole bunch of fields of which the most important one are

OriginalOrderId  NVARCHAR(50)
MerchantContract INT
Amount           INT

Currently I'm querying these tables with such query:

SELECT  d.id,
        d.date,
        d.ip,
        d.panmask,
        d.merchantorderid,
        d.amount,
        d.cardholder,
        d.bankhumanname,
        d.cardtypeid,
        d.bankcountrycode,
        d.usercountrycode,
        mc.paymentkey as merchantname,
        dt.status,
        d.merchantcontract,
        dt.tag,
        d.originalorderid,
        ds.refnumber,
        ds.dealauthcode,
        mc.processingid,
        pc.Name as processing,
        d.customparams
FROM Deal as d
LEFT JOIN MerchantContract as mc ON mc.Id = d.MerchantContract
LEFT JOIN DealTrace as dt ON d.Id = dt.DealId
AND dt.id = (SELECT MAX(id) FROM DealTrace WITH (nolock) WHERE DealId = d.id)
LEFT JOIN DealSummary ds ON d.Id = ds.DealId
AND ds.id = (SELECT MAX(id) FROM DealSummary WITH (nolock) WHERE DealId = d.id)
LEFT JOIN Processing pc on mc.ProcessingId = pc.id WHERE (d.MerchantContract IN ('13'))
ORDER BY ID desc OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY

However for each MerchantContract there might be several rows with OriginalOrderId but different values in other columns. For example

MerchantContract|OriginalOrderID|Status|Amount|...
3|13_Qrta|charge|101|...
3|13_Qrta|refund|94|...
4|15_Utq1|charge|56|...

I would like for each unique pair of MercahntContract OriginalOrderId aggregate data in one row, so above resuklt would look like

MerchantContract|OriginalOrderID|Status|Amount|...
3|13_Qrta|charge,refund|101,94|...
4|15_Utq1|charge|56|...

I was looking around but unfortunately couldn't find solution. I was trying to modify first FROM to

SELECT ...
(SELECT * FROM Deal d1 WHERE d1.Id=(SELECT MAX(Id) FROM Deal d2 "+
    "WHERE d2.OriginalOrderId=d1.OriginalOrderId)) d
LEFT JOIN ...

But it didn't give the required result. Would really appreciate if anybody could give any hints as to how go about my problem.

Use For XML Path..

select
Merchant_contract,
OriginalOrderid,
Stuff(
(Select ','+ status from yourtable t2 where t2.Merchant_contract=t1.Merchant_contract
and
t2.orderid=t1.orderid
For Xml Path(''))
,1,2,'') as status
---do similarly for other columns
from
yourtable t1
group by
Merchant_contract,
OriginalOrderid


SQL - How to return first encountered row that matches specific criteria I have a table that has the following format:

id | price | category
---------------------
1   22    hardware
2   11    software
3   null  hardware
4   null  hardware
5   55    software
6   null  hardware

I want to fetch any row whatsoever that has price = null and category = hardware. I only want to know if a row with a null price exists. So whether the query fetches row ID 3 or 4 or 6 doesn't matter.

I'm currently using

Select Distinct id where price is null and category = hardware

but on a very large table with many null values the Distinct will require internal sorting by the database which will take extra time.

Other options that come to mind are

Select max(id) where price is null and category = hardware

But all those options require some internal sorting, grouping or indexing.

Is there a simpler SQL command to just fetches any row whatsoever that matches price=null and category = hardware without grouping, sorting, or indexing?

Answering for SQL Server. I don’t know that there will be a single answer that will work on all three platforms you’ve tagged. If that was unintentional, please only tag the engine you’re using. If you want an answer per platform, might be better to ask separate questions.

You can leave which id is returned up to SQL Server by using an arbitrary TOP (1) (no order by):

SELECT TOP (1) id
  FROM dbo.table
  WHERE price IS NULL
    AND category = 'hardware';

I would comment the code to say “don’t care which one and I know this is arbitrary” so a future reader doesn’t think you made any assumptions about deterministic order.


MySQL fulltext selection performance I have 1.5M rows in a table. Following is the table create code:

CREATE TABLE `jobs` (
    `id` INT(8) NOT NULL AUTO_INCREMENT,
    `job_id` VARCHAR(50) NOT NULL DEFAULT '',
    `title` VARCHAR(255) NOT NULL DEFAULT '',
    `company` VARCHAR(255) NOT NULL DEFAULT '',
    `city` VARCHAR(50) NOT NULL DEFAULT '',
    `state` VARCHAR(50) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `job_id` (`job_id`),
    FULLTEXT INDEX `search` (`title`, `company`, `city`, `state`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM

The query below takes about 0.3 seconds, which is very high.

SELECT id
     , title
     , company
     , state
     , city
FROM `jobs`
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')
LIMIT 0,10

How can I decrease execution time and still provide relevant results? Any suggestions?

So far I tried followings but there is no improvement at all.

Searching in a single field that contains 4 field of data but it did not matter.
Using in Boolean mode>1 or >2, but then it gives me unrelated results
Repairing the table, increasing key_buffer_size to 1GB from 16MB, changing table type to Innodb, changing character set to latin1 from utf8.
Setting ft_max_word_len=1 and ft_stopword_file='' from default values.
I searched online for many hours but no luck so far.

"Explain select..." output:

id;select_type;table;type    ;possible_keys;key   ;key_len;ref;rows;Extra
1 ;SIMPLE     ;jobs ;fulltext;search       ;search;0      ;\N ;1   ;Using where

EDIT: SHOW PROFILE OUTPUT:

|| *Status*                || *Duration* ||
|| starting                || 0.000087   ||
|| checking permissions    || 0.000011   ||
|| Opening tables          || 0.000036   ||
|| init                    || 0.000030   ||
|| System lock             || 0.000013   ||
|| optimizing              || 0.000010   ||
|| statistics              || 0.000021   ||
|| preparing               || 0.000008   ||
|| FULLTEXT initialization || 0.276820   ||
|| executing               || 0.000010   ||
|| Sending data            || 0.000155   ||
|| end                     || 0.000008   ||
|| query end               || 0.000004   ||
|| closing tables          || 0.000016   ||
|| freeing items           || 0.000703   ||
|| cleaning up             || 0.000018   ||

I just answered this recent question : Full text search results in a large amount of time spent in 'FULLTEXT initialization'

Since you are using MyISAM, I would recommend refactoring your query

Here is my proposed query

SELECT id,
title,
company,
state,
city
FROM `jobs`
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')
LIMIT 0,10

SELECT B.id,
B.title,
B.company,
B.state,
B.city
FROM
(SELECT id FROM`jobs`
WHERE MATCH (title, company, state, city) AGAINST
('senior software engineer in san fransisco california')
LIMIT 0,10) A
LEFT JOIN `jobs` USING (id);

GIVE IT A TRY !!!


Logging of DBCC Commands Is there are way to trace if DBCC commands are being run against a SQL Server database?

Specifically I am looking to see if the following 2 commands have been run at any point?

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

The reason is that the cache keeps emptying and I am pretty sure there isn't a great amount of memory pressure on the server.

I would really like to  rule out the fact that this is being done by someone and really is a genuine issue I need to investigate and/or add more memory to the server.

Thanks in advance
David

Here is an extended event session to find any DBCC calls, that have not already occured.  From your question it looks like you may have wanted to find ones in the past? Like others have alluded to there should be info in the logs on cache clears, and should read as follows (at least on SQL2014):

Date,Source,Severity,Message
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.
08/10/2016 20:03:51,spid66,Unknown,SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations.

For the future you could use this Extended Event definition.

CREATE EVENT SESSION [DBCC] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(0)
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'DBCC%'))), ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([statement],N'DBCC%')))
GO
-- Uncomment if you want to output to a target file and change the file path
--ALTER EVENT SESSION [DBCC] ON SERVER
--ADD TARGET package0.event_file(SET filename=N'K:\Backup\DBCC_XE',max_rollover_files=(2))
--GO

ALTER EVENT SESSION [DBCC] ON SERVER STATE = START
GO

-- Don't forget to turn it off
ALTER EVENT SESSION [DBCC] ON SERVER STATE = STOP
GO

Will get you some information like this (my user and server name are redacted):

If you want a trace (SQL Profiler) definition I could probably throw one of those together too.

Hope this helps.

Cheers


SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs' I have got this error message in Vesta Control Panel, in phpMyAdmin:

  SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs'

SELECT `prefs`
FROM `phpmyadmin`.`pma_table_uiprefs`
WHERE `username` = 'admin_db1'
AND `db_name` = 'admin_db1'
AND `table_name` = 'testtable'

I have already tried to solve with this command:

GRANT SELECT,INSERT,UPDATE,DELETE ON phpmyadmin.*
TO 'admin_db1'@'localhost' IDENTIFIED BY 'mypassword';

I wonder if your config.inc.php has a properly configured controluser. The error message (indicating that permission was denied for user '') suggests some problem with your configuration file or the controluser itself.

Please look for the two controluser lines which should read as:

$cfg['Servers'][$i]['controluser']   = 'admin_db1';
$cfg['Servers'][$i]['controlpass']   = 'mypassword';

If they are there and correct, please try connecting as that account from the command line client (on the same machine running phpMyAdmin) to see if you can manually USE the phpmyadmin database and that the table pma_table_uiprefs exists.

If all that fails, you may wish to post your config.inc.php here, obscuring any sensitive information such as usernames, passwords, and hostnames.

Or, alternatively, if you have no need for the advanced configuration options you can just remove the controluser, controlpass, and pmadb lines from your config.inc.php and it will bypass that part completely.


Total number of matches played by player Udacity database SQL free course final project:-

I made 3 tables:

Players(pid, name, contact)
Matches(mid, pid_a, pid_b, won)
Matches_won(mid,pid)

I want to make a query so i can also be able to count the total matches played by player id.

I am able to make table with id, name, (number of matches won) with this query..

SELECT    players.pid,
          players.name,
          count(m_won.pid) as won
FROM      players
LEFT JOIN m_won
ON        players.pid = m_won.pid
GROUP BY  players.pid
ORDER BY  players.pid;

I want to show playerid, playername, total wins and total matches played in 1 table. help me.

PostgreSQL code so you can also test yourself.

CREATE TABLE players(
  pid serial PRIMARY KEY, -- pid = PlayerID
  name text,
  contact bigint
);

CREATE TABLE matches(
  mid serial PRIMARY KEY, -- mid = MatchID
  pid_a int REFERENCES players (pid),
  pid_b int REFERENCES players (pid),
  winner int
);

-- In this table,
-- I am recording matchID and playerID
-- So i can keep track of players who won matches by matchID

-- m_won = matches_won
CREATE TABLE m_won(
  mid int REFERENCES matches (mid),
  pid int REFERENCES players (pid)
);

--RAW DATA TO INSERT INTO TABLES:-(dont change the values)
INSERT INTO players (name, contact) VALUES ('Dishank', 9803050043);
INSERT INTO players (name, contact) VALUES ('Farhan', 8943789678);
INSERT INTO players (name, contact) VALUES ('Rajat', 9742434546);
INSERT INTO players (name, contact) VALUES ('Ajay', 7865875467);

INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 2, 2);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 3, 3);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (1, 4, 4);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (2, 3, 2);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (2, 4, 4);
INSERT INTO matches (pid_a, pid_b, winner) VALUES (3, 4, 3);

INSERT INTO m_won (mid, pid) VALUES (1, 2);
INSERT INTO m_won (mid, pid) VALUES (2, 3);
INSERT INTO m_won (mid, pid) VALUES (3, 4);
INSERT INTO m_won (mid, pid) VALUES (4, 2);
INSERT INTO m_won (mid, pid) VALUES (5, 4);
INSERT INTO m_won (mid, pid) VALUES (6, 3);

UPDATE_ UPDATE UPDATE UPDATE UPDATE UPDATE _UPDATE

I solved the answer 50%. (Thanks to @EVAN).

This query is counting total matches played by player.

SELECT p.player AS player,
       p.name AS name,
       count(*) AS total_played
FROM (
SELECT matches.pid_a AS player,
       players.name AS name
FROM   players
JOIN   matches
ON     players.pid=matches.pid_a
UNION ALL
SELECT matches.pid_b AS player,
       players.name AS name
FROM   players
JOIN   matches
ON     players.pid=matches.pid_b
) AS p
GROUP BY player, name
ORDER BY player;

And this query is showing total matches won by player

SELECT    players.pid AS id,
          players.name AS name,
          count(*) AS won
FROM      players
LEFT JOIN m_won
ON        players.pid = m_won.pid
GROUP BY  id
ORDER BY  id;

Now how should i combine them? so i get a table with coulums
"ID","Name","Total_Matches_Won","Total_Matches_played"

UPDATE_ UPDATE UPDATE UPDATE UPDATE UPDATE _UPDATE

Solved 100%(i am getting what i want to achieve), but still my code might be not reliable or can be further improved so keep the discussion active.

SELECT    C2.id, C2.name, C2.total_won, C1.total_played
FROM
(SELECT   p.id AS id,
          p.name AS name,
          count(*) AS total_played
FROM (
SELECT    matches.pid_a AS id,
          players.name AS name
FROM      players
JOIN      matches
ON        players.pid=matches.pid_a
UNION ALL
SELECT    matches.pid_b AS id,
          players.name AS name
FROM      players
JOIN      matches
ON        players.pid=matches.pid_b
) AS p
GROUP BY  id, name
ORDER BY  id
) AS C1,
(SELECT   players.pid AS id,
          players.name AS name,
          count(*) AS total_won
FROM      players
LEFT JOIN m_won
ON        players.pid = m_won.pid
GROUP BY  players.pid
) AS C2
GROUP BY  C2.id, C2.name, C2.total_won, C1.total_played
ORDER BY  C2.id;

First this is a horrible idea,

team_a int REFERENCES players (pid),

This will make everyone hate you. If there is no difference between a team and a player -- use the same name. Pick or have a difference.

  I want to make a query so i can also be able to count the total matches played by player id.

You need only query your matches table.

SELECT team, count(*)
FROM (
  SELECT team_a AS team
  FROM matches
  UNION ALL
    SELECT team_b AS team
    FROM matches
) AS t
GROUP BY team;

That is to say, these relationships are bidirectional but the schema isn't. If team foo plays team bar you can write that two separate ways. That is to say both of these are valid,

TeamA | TeamB | Wuteva
foo   | bar   | baz
bar   | foo   | quz

Update

In the future, try to put all of the things you want to do in the first question, but I've updated this to include the matches won

SELECT pid, count(*) AS played, count(is_won) FILTER (WHERE is_won)
FROM (
  SELECT pid_a AS pid, pid_a = winner AS is_won
  FROM matches
  UNION ALL
    SELECT pid_b AS pid, pid_b = winner AS is_won
    FROM matches
) AS t
GROUP BY pid;

As a separate note, good job on changing the names.


Mongodb authentication database best practice In mongodb you can store user credentials in different databases.  E.g. Say you have a products db, you could store the user products_worker in either database admin or products.

Is there a best practice here?  I don't really like having users all over the place.  Is there anything wrong with storing all users in admin?

Best practice is keep all users at admin database. However, some people don't like that they need to use --authenticationDatabase admin (or equivalent) when they login. Especially, if they use only one database.


Dynamic where condition in a stored procedure I am using Oracle 11g. I am trying to get the table data with this procedure:

CREATE OR REPLACE PROCEDURE test(p_table IN varchar2)
IS
v_sqltxt varchar2(4000);
BEGIN
v_sqltxt:='select count(*) from '||p_table;
dbms_output.put_line(v_sqltxt);
END;
/

But I am unable to give the where condition dynamically. How can I do that?

You'll need to execute your SQL statement. Use execute immediate for this. And because you're selecting a value you'll need to execute immediate ... into it, something like this (replacing 3 > 2 with your own where condition:

CREATE OR REPLACE PROCEDURE test(p_table IN varchar2) IS
  v_sqltxt varchar2(4000);
  v_count integer;
BEGIN
  v_sqltxt:='select count(*) from '||p_table||' where 3 > 2';

  execute immediate v_sqltxt into v_count;

  dbms_output.put_line(v_count);
END;
/


Compare Microsoft SQL server performance between virtual and dedicated server I have a dedicated windows 2008 r2 server at the moment and I am thinking about migrating to a virtual server because of the costs. Now I know that SQL should be on a dedicated server, but the flexibility and costs are a great advantage for our situation.
I would like to compare both servers. I know it is hard because one is a production server with actual load and the other is a test server with one user.

Both run Microsoft SQL Server 2008.
dedicated has 12 GB ram, virtual has 12 GB ram
dedicated has a dual quad core CPU and virtual has 4 CPU's

both have the exact same database (I restored a backup from production on the virtual test)

I executed a heavy select query with a heavy view and made the Microsoft SQL Server Management Studio include the Client statistics.
That displays the Total execution time

Somehow the virtual shows a zero there now (since we added 2 virtual CPU's) but in the bottom right of the management studio it shows that the virtual server took 3 seconds and the dedicated server(production) took 2 seconds for the query to complete.

How can I make a good comparison between the virtual and production?

Edit: I have been told that the paravirtual setting in VMWare makes it better to compare the performance of the two instances.

Testing isolated queries isn't going to help you make this decision. As you have an existing database live, use it to capture a  representative workload.

Capture a profiler trace from your live, dedicated server
Use the RML toolset to create a replay file
Execute against the virtual server using ORCA

I posted a brief summary of the RML tools in an earlier question which you may find useful. To compare results across both runs, capture a trace of the first trace being run against the virtual server and run both through ClearTrace.


Cannot choose Storage option on a table in SSMS for standard edition I am looking into partitioning of tables for SQL Server on two different machines. One machine runs Microsoft SQL Server (RTM) 2016 Developer Edition, and the other is running Microsoft SQL Server 2016 (SP1) Standard Edition.

As far as I can see from this entry, SQL Server 2016 (SP1) Standard Edition should support partitioning.

However, from the SSMS on the Developer Edition, I can partition a table using the build-in wizard by choosing the "storage" option on a table, but this option is missing from the SSMS on the machine with the Standard Edition. What might be the cause of this? Does SSMS change appearance depending on which SQL Server edition it is running against?

Here is the menu in the SSMS for the Developer Edition:

And here is the menu in the SSMS for the Standard Edition:

Does SSMS change appearance depending on which SQL Server edition it
  is running against?

Yes

We cannot track everything ssms is executing or making decisions on but we can get a general idea.

Tracing the right click on a table on a SQL Server 2014 standard edition instance will give us more of an idea on the lower amount of checks.

In total, 11 rows/statements found while tracing the action:

With the options shown:

And when doing the same on a SQL Server 2016 standard edition instance (build version: 13.0.5081.1)

In total, 59 rows/statements:

And options shown:

In short, on my ssms version (14.0.17119.0) and my SQL Server 2016 instance
(build version: 13.0.5081.1), the storage option was shown.

I would suggest updating your ssms to the latest version, or using T-SQL instead of  the GUI.

Patching your sql server instance(s) is also recommended as a general best practice. You could opt to go for SP2 & CU8.

Only for reference, the query that ran on both instances is this one:

        declare @HkeyLocal nvarchar(18)
        declare @ServicesRegPath nvarchar(34)
        declare @SqlServiceRegPath sysname
        declare @BrowserServiceRegPath sysname
        declare @MSSqlServerRegPath nvarchar(31)
        declare @InstanceNamesRegPath nvarchar(59)
        declare @InstanceRegPath sysname
        declare @SetupRegPath sysname
        declare @NpRegPath sysname
        declare @TcpRegPath sysname
        declare @RegPathParams sysname
        declare @FilestreamRegPath sysname

        select @HkeyLocal=N'HKEY_LOCAL_MACHINE'

        -- Instance-based paths
        select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
        select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'
        select @FilestreamRegPath=@InstanceRegPath + N'\Filestream'
        select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'
        select @RegPathParams=@InstanceRegPath+'\Parameters'

        -- Services
        select @ServicesRegPath=N'SYSTEM\CurrentControlSet\Services'
        select @SqlServiceRegPath=@ServicesRegPath + N'\MSSQLSERVER'
        select @BrowserServiceRegPath=@ServicesRegPath + N'\SQLBrowser'

        -- InstanceId setting
        select @InstanceNamesRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

        -- Network settings
        select @NpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Np'
        select @TcpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Tcp'

        declare @SmoAuditLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'AuditLevel', @SmoAuditLevel OUTPUT

        declare @NumErrorLogs int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'NumErrorLogs', @NumErrorLogs OUTPUT

        declare @SmoLoginMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'LoginMode', @SmoLoginMode OUTPUT

        declare @SmoMailProfile nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'MailAccountName', @SmoMailProfile OUTPUT

        declare @BackupDirectory nvarchar(512)
        if 1=isnull(cast(SERVERPROPERTY('IsLocalDB') as bit), 0)
        select @BackupDirectory=cast(SERVERPROPERTY('instancedefaultdatapath') as nvarchar(512))
        else
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT

        declare @SmoPerfMonMode int
        exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'Performance', @SmoPerfMonMode OUTPUT

        if @SmoPerfMonMode is null
        begin
        set @SmoPerfMonMode = 1000
        end

        declare @InstallSqlDataDir nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLDataRoot', @InstallSqlDataDir OUTPUT

        declare @MasterPath nvarchar(512)
        declare @LogPath nvarchar(512)
        declare @ErrorLog nvarchar(512)
        declare @ErrorLogPath nvarchar(512)
        declare @Slash varchar = convert(varchar, serverproperty('PathSeparator'))
        select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'master'
        select @LogPath=substring(physical_name, 1, len(physical_name) - charindex(@Slash, reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
        select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
        select @ErrorLogPath=substring(@ErrorLog, 1, len(@ErrorLog) - charindex(@Slash, reverse(@ErrorLog)))

        declare @SmoRoot nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @SmoRoot OUTPUT

        declare @ServiceStartMode int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'Start', @ServiceStartMode OUTPUT

        declare @ServiceAccount nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'ObjectName', @ServiceAccount OUTPUT

        declare @NamedPipesEnabled int
        exec master.dbo.xp_instance_regread @HkeyLocal, @NpRegPath, N'Enabled', @NamedPipesEnabled OUTPUT

        declare @TcpEnabled int
        EXEC master.sys.xp_instance_regread @HkeyLocal, @TcpRegPath, N'Enabled', @TcpEnabled OUTPUT

        declare @InstallSharedDirectory nvarchar(512)
        EXEC master.sys.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @InstallSharedDirectory OUTPUT

        declare @SqlGroup nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLGroup', @SqlGroup OUTPUT

        declare @FilestreamLevel int
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'EnableLevel', @FilestreamLevel OUTPUT

        declare @FilestreamShareName nvarchar(512)
        exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'ShareName', @FilestreamShareName OUTPUT

        declare @cluster_name nvarchar(128)
        declare @quorum_type tinyint
        declare @quorum_state tinyint
        BEGIN TRY
        SELECT @cluster_name = cluster_name,
        @quorum_type = quorum_type,
        @quorum_state = quorum_state
        FROM sys.dm_hadr_cluster
        END TRY
        BEGIN CATCH
        --Querying this DMV using a contained auth connection throws error 15562 (Module is untrusted)
        --because of lack of trustworthiness by the server. This is expected so we just leave the
        --values as default
        IF(ERROR_NUMBER() NOT IN (297,300, 15562))
        BEGIN
        THROW
        END
        END CATCH

SELECT
@SmoAuditLevel AS [AuditLevel],
ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],
(case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],
ISNULL(@SmoMailProfile,N'') AS [MailProfile],
@BackupDirectory AS [BackupDirectory],
@SmoPerfMonMode AS [PerfMonMode],
ISNULL(@InstallSqlDataDir,N'') AS [InstallDataDirectory],
CAST(@@SERVICENAME AS sysname) AS [ServiceName],
@ErrorLogPath AS [ErrorLogPath],
@SmoRoot AS [RootDirectory],
CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive],
@@MAX_PRECISION AS [MaxPrecision],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
convert(sysname, serverproperty(N'collation')) AS [Collation],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath],
SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile],
SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog],
SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString],
SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime],
SERVERPROPERTY(N'CollationID') AS [CollationID],
SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle],
SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet],
SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName],
SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder],
SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName],
SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString],
@ServiceStartMode AS [ServiceStartMode],
ISNULL(@ServiceAccount,N'') AS [ServiceAccount],
CAST(@NamedPipesEnabled AS bit) AS [NamedPipesEnabled],
CAST(@TcpEnabled AS bit) AS [TcpEnabled],
ISNULL(@InstallSharedDirectory,N'') AS [InstallSharedDirectory],
ISNULL(suser_sname(sid_binary(ISNULL(@SqlGroup,N''))),N'') AS [SqlDomainGroup],
case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state  where target_query_expression_with_id like 'Server%' ) then 1 else 0 end AS [PolicyHealthState],
@FilestreamLevel AS [FilestreamLevel],
ISNULL(@FilestreamShareName,N'') AS [FilestreamShareName],
-1 AS [TapeLoadWaitTime],
CAST(SERVERPROPERTY(N'IsHadrEnabled') AS bit) AS [IsHadrEnabled],
SERVERPROPERTY(N'HADRManagerStatus') AS [HadrManagerStatus],
ISNULL(@cluster_name, '') AS [ClusterName],
ISNULL(@quorum_type, 4) AS [ClusterQuorumType],
ISNULL(@quorum_state, 3) AS [ClusterQuorumState],
SUSER_SID(@ServiceAccount, 0) AS [ServiceAccountSid],
CAST(SERVERPROPERTY('IsPolyBaseInstalled') AS bit) AS [IsPolyBaseInstalled],
CAST(
        serverproperty(N'Servername')
       AS sysname) AS [Name],
CAST(
        ISNULL(serverproperty(N'instancename'),N'')
       AS sysname) AS [InstanceName],
CAST(0x0001 AS int) AS [Status],
SERVERPROPERTY('PathSeparator') AS [PathSeparator],
0 AS [IsContainedAuthentication],
CAST(null AS int) AS [ServerType]


mysql works, mysqldump doesn't I have mysql 5.5 on my ubuntu 12.04 server. This command:

mysql -u root -p

works perfectly, but this gives me error:

mysqldump -u root -p mydb_name > a.sql
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

In my.cnf file, the socket is set to:

socket = /home/mysql/mysql.sock

so I have no idea where /var/run/mysqld/mysqld.sock is coming from. Thanks.

When reading the configuration file, the mysql command line client only reads configuration directives found in the [client] and [mysql] sections, while mysqldump only uses configuration directives found in the [client] and [mysqldump] sections.

If your socket directive in the config file is in the [mysql] section but not the [mysqldump] section then you should move it to the [client] section, and this should solve your problem.

So where does /var/run/mysqld/mysqld.sock come from?  I remember very distinctly, once upon a time, beating my head against the wall repeatedly over that one.

It turns out, Ubuntu compiles their MySQL components from source, and when they do that, they compile them to use /var/run/mysqld/mysqld.sock unless the configuration file specifies otherwise or the socket is overridden on the command line... so if your socket directive isn't in [client] that would be one explanation of this behavior.


UPDATE STATISTICS and User-Defined Types (UDT) from Assemblies I have a problem with UPDATE STATISTICS where the column is a user-defined type (UDT) from an assembly.

Step by step:

Assembly:

CREATE ASSEMBLY [UDT.TParams] FROM 0x4D...(stripped) WITH PERMISSION_SET = SAFE

Type:

CREATE TYPE [dbo].[TParams] EXTERNAL NAME [UDT.TParams].[Sql.UDT.TParams]

Table:

CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
    [Id] [tinyint] NOT NULL,
    [Group_Id] [tinyint] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Code] [varchar](25) NOT NULL,
    [PersonTypes_IDs] [varchar](50) NOT NULL,
    [Unique] [bit] NULL,
    [SortIndex] [tinyint] NOT NULL,
    [FieldName]  AS (replace([Code],':','_')),
    [GUI:Edit:Type] [varchar](64) NULL,
    [GUI:Edit:Params] [dbo].[TParams] NULL,
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Statistics (it's auto-created by SQL Server):

CREATE STATISTICS [weirdStatistics] ON
      [dbo].[Dic:Table:With&Weird:Name]([GUI:Edit:Params])

Then we have scheduled weekly updates on all statistics with FULLSCAN:

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [weirdStatistics] WITH FULLSCAN

Getting this error after above command:

  Msg 1038, Level 15, State 4, Line 3
  An object or column name is missing or empty.
  For SELECT INTO statements, verify each column has a name.
  For other statements, look for empty alias names.
  Aliases defined as "" or [] are not allowed.
  Change the alias to a valid name.

How do I fix this error, or do I just skip the statistics update?

UPDATE: Opening properties windows of [weirdStatistics] in SSMS, will show error: DataReader.GetFieldType(0) returned null. (SqlManagerUI). Any other statistics for table opens normally.

I do not believe there is any issue here with regards to the User-Defined Type (UDT) as I am able to execute this successfully:

Please note that I am using the Free version of the SQL# library (which I wrote) to test with:

CREATE TABLE [dbo].[Dic:Table:With&Weird:Name](
    [Id] [tinyint] NOT NULL,
    [Group_Id] [tinyint] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Code] [varchar](25) NOT NULL,
    [PersonTypes_IDs] [varchar](50) NOT NULL,
    [Unique] [bit] NULL,
    [SortIndex] [tinyint] NOT NULL,
    [FieldName]  AS (replace([Code],':','_')),
    [GUI:Edit:Type] [varchar](64) NULL,
    [GUI:Edit:Params] [SQL#].[Type_HashTable] NULL
);

CREATE STATISTICS [weirdStatistics] ON [dbo].[Dic:Table:With&Weird:Name]([GUI:Edit:Params]);

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [weirdStatistics] WITH FULLSCAN;

No errors. However, if I remove the stats object name (the error did mention a missing object name) but keep the square brackets, then I am able to reproduce the error:

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [] WITH FULLSCAN;

returns:

  Msg 1038, Level 15, State 4, Line 28
  An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

And no square brackets at all works just fine (since it updates all stats on the table):

UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] WITH FULLSCAN;

So it really needs to be empty square brackets. My guess is that your weekly Update Stats job (I am assuming it is a SQL Agent Job since you said "weekly") is somehow leaving out the stats object name.

AND, since the question actually mentions auto-created statistics, that should be tested as well, just to be sure that there isn't an issue with those (there shouldn't be, but it's still best to test and know for sure):

-- get rid of user-created statistics object:
DROP STATISTICS [dbo].[Dic:Table:With&Weird:Name].[weirdStatistics];

-- add one row so that we can query it:
INSERT INTO dbo.[Dic:Table:With&Weird:Name]
VALUES (1, 2, N'ddfg', 'df:uiui', 'dfgdfgd', 0, 44, 'type', N'key1=SomeVal');

-- get SQL Server to auto-create the statistics object on this column:
SELECT *
FROM   dbo.[Dic:Table:With&Weird:Name]
WHERE  [GUI:Edit:Params] IS NOT NULL;

-- get the dynamically generated statistics object name and use it for UPDATE STATS:
DECLARE @StatName sysname;

SELECT @StatName = st.[name]
FROM sys.stats st
INNER JOIN sys.stats_columns sc
        ON sc.[object_id] = st.[object_id]
       AND sc.[stats_id] = st.[stats_id]
INNER JOIN sys.columns col
        ON col.[object_id] = sc.[object_id]
       AND col.[column_id] = sc.[column_id]
WHERE st.[object_id] = OBJECT_ID(N'dbo.Dic:Table:With&Weird:Name')
AND   col.[name] = N'GUI:Edit:Params';

SELECT @StatName AS [StatisticsName];

EXEC (N'UPDATE STATISTICS [dbo].[Dic:Table:With&Weird:Name] [' + @StatName
      + N'] WITH FULLSCAN;');

The code above also completes without errors :-).

The following test should be done to separate the UDT from this particular usage of it (i.e. this table). The example below again uses SQL#, but you should replace [SQL#].[Type_HashTable] with [dbo].[TParams] and then replace the string value in the first INSERT statement with the appropriately formatted initialization text. If this does not error then the issue is with that table, possibly with the column name itself, such as having a hidden character in it. If it does error then we need to look at the definition of that UDT.

CREATE TABLE [dbo].[tmpTestStats](
    [Id] [tinyint] NOT NULL,
    [UDT] [SQL#].[Type_HashTable] NULL
);

INSERT INTO [dbo].[tmpTestStats] ([Id], [UDT]) VALUES (1, N'k1=v1&k2=v2');
INSERT INTO [dbo].[tmpTestStats] ([Id], [UDT]) VALUES (2, NULL);

CREATE STATISTICS [tmpTestStats_UDT] ON [dbo].[tmpTestStats]([UDT]);

UPDATE STATISTICS [dbo].[tmpTestStats] [tmpTestStats_UDT] WITH FULLSCAN;


Informix Server Rejected Connection What are possible causes for the following error?

  DB Error: connect failed [nativecode=08004 [unixODBC][Informix]
  [Informix ODBC Driver [Informix]Server rejected the connection.]

I have doubled the number of allowed connections thinking was that it was running out of connections in the pool but that has made no change to getting this error. It only happens when server is under load.

I'm not immediately sure how to interpret the 'nativecode=08004' part of the message shown.  In decimal, error -8004 is about 'Illegal FLOAT constant' and is for one of the Informix SQL compilers.  In hex, it corresponds to 32772 decimal, but that isn't a known error number.  If it is an SQLSTATE value, then … hmmm, I'm not sure how to find out the interpretation of it.

I did a rather specialized search and came across:

  -1809 Server rejected the connection.

  Check that you have permission to connect to the server that you
  specified. If you specified both a database server and a database,
  check to see that you have permission to access the database.

  This error message can occur when you are trying to use a Pluggable Authentication Module (PAM) feature of IDS when one or both of the following conditions exists:

  The PAM configuration file is not set correctly. Refer to the OS vendor
  documentation for more information.
  The client or server computer is not configured to handle PAM messages
  that are generated from a connection request.

I suspect, but without much actual evidence at the moment, that the second bullet point might be applicable.

There is some information missing from the question which I regard as crucial:

Which platform is the ODBC running on?  Is the Informix server on the same machine?  If not, what platform is that running on too?
Which version of Informix ODBC are you using?  Which version of Informix server are you using?
You're using the unixODBC driver manager; which version of that (more for completeness than because it is likely to be critical).
You say that you see this error under load — what does that mean to you?  Is it a single process making many connections concurrently?  Or many copies of a single program connecting concurrently?  Or many copies of many programs connecting concurrently?  Or something else?  How many connections does it take before the problem shows up?
What sort of connection are you using?  There are shared memory or socket connections primarily, or perhaps IPC streams.

If you have support for your Informix product, you should probably contact IBM/Informix Technical Support.  If you don't, you might do better via the IIUG mailing lists.  It is free to join and you can choose which lists you receive.  You probably want the 'ids@iiug.org' mailing list.

Failing that, over time, and possibly with increasingly fiddly diagnostics, we might eventually get to the bottom of the trouble.  But to do so, I'll need answers to at least some of the questions above.


Query to retrieve aggregated data using variables Question:
Summarise events using the MIN, MAX and COUNT functions

Help:
To do this, create variables to hold each of the aggregates:

Variable          What it will hold eventually
---------------   -----------------------------
@EarliestDate   = The earliest date
@LatestDate     = The latest date
@NumberOfEvents = The number of events
@EventInfo      = The title Summary of events

Now use a single SELECT clause to set the value for all of these variables (alternatively you could use a series of subqueries), write another SELECT clause to show the value of the variables, to show the summary output shown above.

Result:

I'm having trouble getting this exercise done.

Is there anywhere I can get the solution?

Thanks in advance!

Something like this?

declare @EarliestDate datetime
declare @LatestDate datetime
declare @NumberOfEvents int
declare @EventInfo nvarchar(100) = 'Summary of events'

SELECT   @EarliestDate = (SELECT MIN(Dtm) FROM [YourTable])
        ,@LatestDate = (SELECT MAX(Dtm) FROM [YourTable])
        ,@NumberOfEvents = (SELECT COUNT(*) FROM [YourTable])

SELECT   @EventInfo [Title]
        ,@EarliestDate [Earliest Date]
        ,@LatestDate [Latest date]
        ,@NumberOfEvents [Number of Events]


Am I overengineering MySQL? On my project, I have to make difference between registered users, and simple visitors. Both of them can set their own properites, specified in accounts and visitors table.

I identify them by cookie, if there is no cookie, a user can log in, and create a session (and a "remember me" cookie if she wishes) and the simple one time visitor also creates a cookie and a session.

I split the current session to either accountSession (logged in person), and to visitorSession (simple visitor).

I insert the visited pages, userAgents, IPs by the accountSession OR the visitorSession.

Am I overengineering?

Here is the diagram:

So much of accounts and visitors is common, that it pains me to see the extra tables for sessions and cookies.  I'd rather see visitors with NULL values for the extra 3-4 columns.


Optimize Query: Group By while tracking Count Is there a better way to write this query? The query is taking too long (aprox 4sec)  The box table has about 8k records per day.

I've tried different Indexes but none seem to improve it so I'm thinking it's my query.

SELECT
boxType.id, sum(box.numBoxes) as totalBoxes,
count(DISTINCT box.employeeId) as totalEmployees
FROM box
INNER JOIN user as u ON u.id = box.userId
INNER JOIN jobBox as jobBox ON box.jobBoxId = jobBox.id
INNER JOIN boxType as bType ON bType.id = jobBox.boxTypeId
WHERE (u.clientId=?) AND (box.createdOn >= ?)
GROUP BY boxType.id

-- box
CREATE TABLE `box` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`employeeId` int(11) DEFAULT NULL,
`numBoxes` float NOT NULL,
`createdOn` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`jobBoxId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `User id_idx` (`userId`),
KEY `JobBoxFK_idx` (`jobBoxId`),
KEY `FK_5pomtj2u3uh6xq3qf7vmokc5k` (`employeeId`),
KEY `IDX_CreatedOn` (`createdOn`,`jobBoxId`),
CONSTRAINT `UserFK_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)   ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `packFK_1` FOREIGN KEY (`jobBoxId`) REFERENCES `jobBox`   (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `EmployeeFK_1` FOREIGN KEY (`employeeId`) REFERENCES   `employee` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='.';

-- jobBox
CREATE TABLE `jobBox` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`jobId` int(11) DEFAULT NULL,
`boxTypeId` int(11) DEFAULT NULL,
`createdOn` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `JobBoxFK_idx` (`jobId`),
KEY `BoxTypeID_idx1` (`boxTypeId`),
CONSTRAINT `jobBox_ibfk_1` FOREIGN KEY (`boxTypeId`) REFERENCES    `boxType` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
CONSTRAINT `jobBox_ibfk_2` FOREIGN KEY (`jobId`) REFERENCES `job`  (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1937 DEFAULT CHARSET=utf8;

-- boxType
CREATE TABLE `boxType` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;

-- user
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`clientId` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`),
KEY `clients id_idx` (`clientId`),
CONSTRAINT `clients id` FOREIGN KEY (`clientId`) REFERENCES `client`   (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8 COMMENT='  ';

Explain statement results

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'box', NULL, 'range', 'EmployeeFK_1,packFK_1,IDX_CreatedOn', 'IDX_CreatedOn', '5', NULL, '33034', '100.00', 'Using index condition; Using temporary; Using filesort'
'1', 'SIMPLE', 'u', NULL, 'eq_ref', 'PRIMARY,id_UNIQUE,clients id_idx', 'PRIMARY', '4', 'box.userId', '1', '14.04', 'Using where'
'1', 'SIMPLE', 'jobBox', NULL, 'eq_ref', 'PRIMARY,jobBox_ibfk_1', 'PRIMARY', '4', 'box.jobBoxId', '1', '100.00', 'Using where'
'1', 'SIMPLE', 'boxType', NULL, 'eq_ref', 'PRIMARY,UnitTypesFK_idx', 'PRIMARY', '4', 'jobBox.boxTypeId', '1', '100.00', NULL

Any thoughts are appreciated, thank you.

Add to box:  INDEX(userId, createdOn).

Then provide EXPLAIN SELECT ... if that is not sufficient.


Datatype bigger than bigint I want to use dataype which can hold more than 19 digits. So is there any datatype available which can hold larger than 19 digits?

Numeric/Decimal, Float/Real. There's a great answer here, some official documentation here and here, and I also posted a few opinions ages ago, but here is a quick demonstration:

-- overflow error:

DECLARE @x BIGINT        = 9999999999999999999;

GO

-- works:

DECLARE @x DECIMAL(38,0) = 99999999999999999999999999999999999999;


Is copy/rename a good way to improve temp table performance? An application I am working with contains this sequence of queries at the end of constructing a large temp table; I believe it is an attempt to improve performance by pruning dead tuples -

create temp table tmp_foo_new as select * from tmp_foo;
drop table tmp_foo;
alter table tmp_foo_new rename to tmp_foo;

Some rudimentary benchmarks do indicate this sequence does improve performance substantially, so it is worthwhile.  It feels somewhat hacky to me though.  Is there a more canonical way to do this?

If the table has an index, you may want to consider the cluster command instead of re-creating it or using vacuum full. This will:

Have the same effect on dead tuples - it physically re-writes the whole table
Retains any existing indexes
Might improve performance more than just removing dead tuples, depending on whether you will benefit from the clustering too (though of course you could achieve the same with an order by clause when re-creating)


mysqldump including deleted rows I'm trying to move a database from a Windows2003Server with mysql 5.0.27-community-nt to a new server with CenTOS. When using mysqldump on the database on the windows server, one table (i haven't started checking how widespread the problem is) is being dumped with quite some deleted records in it.
After restoring the dump on the new server, I can easily spot records that exist on the new server but not on the old one. However, when I do a select count(*) both tables show the same record.
The dump is created with --opt, and I also tried dropping the new server's table before a restore. Reading through the dump (I did a table exclusive dump) I can spot the inserts for the records that exist in one database but not the other one.
I also tried doing a flush tables before dumping the table, and tried using SQLyog's export to SQL function, all with the same result.

What can be going on?

In case anyone reaches this, and to tag it as finished, the problem was a corrupted index file. MySQL threw the error a couple of hours after and after a REPAIR TABLE it was clear the data was exactly the same.


How should I model a database structure to retain calculation results? I am trying to improve the performance of the queries related to one table that I consider "weird", which could be created by the next script:

CREATE TABLE `CalcTable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `result` double DEFAULT NULL,
  `calc_dt` datetime DEFAULT NULL,
  `project_id` int(11) NOT NULL,
  `calculation_type_cd` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`));

Currently, we have 17 different calculation types. A calculation type could be:

"current kilowatt per hour",
"expected kilowatt per hour",
"forecast kilowatt per hour",
etc.

The data could grow to 250 different calculation types in the future. Also, a situation that may arise is that a customer could create his own calculation types, as much as he/she wants...

So I tried to understand how can I design a table structure to work with this data on the best way now, and to not crash the database in the future (with 250+ new calculation types).

Below you can find current data sample:

id       result  calc_dt              project_id  calculation_type_cd
-------  ------  -------------------  ----------  -------------------
1906901  0.1028  2016-01-18 15:00:00         188  TypeA
1906902  336.68  2016-01-18 15:00:00         188  TypeB
1906903  341.55  2016-01-18 15:00:00         188  TypeC
1906904  0.4531  2016-01-18 16:00:00         188  TypeD
1906905  368.39  2016-01-18 16:00:00         188  TypeA
1906906  38.080  2016-01-18 16:00:00         188  TypeB
1906907  420.34  2016-01-18 16:00:00         188  TypeF

So you can imagine what would be here in the future... Dozens of rows with some columns containing duplicated data...

I'm thinking about two options:

Create 250 columns for storing results for different calculation types
Create different tables for some groups of calculation types

What do you think about it? Does it make sense?

Maybe you have better approaches - please let me know!

Thanks a lot in advance.

I'm using MySQL 5.7 on AWS.

If you don't know how many columns it will have, it may vary all the way up to 250, and the entries might be sparse, I would:

Use the EAV design to store the calculation types on separate rows.
For reporting/analysis purposes, create a view or summary table that pivots rows to columns and "flattens" them out.

One day of data you could probably get fast with indexes. Three years, likely not. For the second case, you might want to look into something more analytically-focused, like Greenplum (on-prem) or Redshift (AWS).


postgresql: How to disable all indexes of a table There are more than 100 indexes on my table. i want to disable all the indexes temporary and want to re-enabled them once the indexing is done.

It is not feasible to go and type every index name to disable it. is there any PostgreSQL statement which can disable all the indexes temporary and then re-enabled them again.

Regards,

From the Comment, you are doing a bulk import using select..

PostgreSQL has a guide on how to best populate a database initially, and they suggest using the COPY command for bulk loading rows.  The guide has some other good tips on how to speed up the process, like removing indexes and foreign keys before loading the data (and adding them back afterwards).

  If you are adding large amounts of data to an existing table, it might
  be a win to drop the indexes, load the table, and then recreate the
  indexes. Of course, the database performance for other users might
  suffer during the time the indexes are missing. One should also think
  twice before dropping a unique index, since the error checking
  afforded by the unique constraint will be lost while the index is
  missing.

You can Check the system catalogue to disable an index:

update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass

This means that the index won't be used for queries but will still be updated. It's one of the flags used for concurrent index building.
If you set indisready to false, that will disable updates.

If you want to disable all for a single query

BEGIN;
DROP INDEX foo_ndx;
EXPALIN ANALYSE SELECT * FROM foo;
ROLLBACK;

You can do this in a transaction to make recovering from it dead simple. You can also disable indexscan to disable all indices.
Also, make sure you are doing EXPALIN ANALYSE on your queries.


oracle 12c alert log error I am using Oracle 12c Standard Edition Release 12.2.0.1.0. Everything works perfect. But while I was checking the alert log file I saw the following message several times.

Error Message

An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.

Any idea about this? I don't understand what it says so can somebody please help me out?

In response to @MichaelKutz' comment I can confirm that no ORA-nnn errors occur before or after the entry in the alert log file.

An Internal Routine Has Requested A Dump Of Selected Redo - Message Appears In Alert Log (Doc ID 2295361.1)

  ...

  SOLUTION

  You can ignore the message related to redo log if there is no error
  along with this information. Please note that for now, there is no way
  to disable the message by setting any event,  because these
  informative messages are useful in some cases if something goes wrong.

  ...


SQL Server varbinary column modifying inserted data We have a table in our database for storing photos, the datatype of the actual photo data is varbinary(max).

When inserting a photo from the application, the data is correctly stored and the photo can be viewed as normal.

However, I am updating another record to have the same photo and when I update/insert the existing data, an extra leading 0 seems to be applied to the data thus breaking the decoding of the image.

When the image is inserted by the application, the binary data is correct and starts with: 0xFFD8

When I copy that binary data, and use an update/insert statement to apply this photo to another record, the data changes and starts: 0x0FFD

This means that the data cannot be converted back to an image.

Google has let me down on this occasion, though it may be that I'm not using the correct search terms.

Any assistance greatly appreciated, and if I can add any more info to assist you, in assisting me, please let me know and I'll add whatever I can.

Link to a pastebin showing data before/after insert/update: http://pastebin.com/7jrtivZ8

The data you pasted is exactly 65533 hex characters.  Including the "0x" prefix, this is 65535 which is exactly 2^16 - 1.  This strongly hints that the code that generates the hexadecimal string representation of the binary data is limited to 65535 characters.  Very likely, it is using a buffer of that length.

If you are using SQL Server Management Studio to retrieve a varbinary value to use in your update query, please note that the value will be truncated according to this SSMS setting:

  Tools / Options / Query Results / Results to Grid / Max Characters
  Retrieved / Non XML Data

This setting happens to have a maximum value of 65535.  If you set this max value to, say, 31, you would get the same odd-numbered string from your example:

select * from photo -- 0xFFD8FFE000104A46494600010101 -- 29-digit hex string

This StackOverflow answer explains that the when there is an odd number of hex characters, SQL server will assume there is a leading 0.  So if you write this:

update Photo set photo_data = 0xF -- a 1-digit hex string

It is functionally equivalent to writing this:

update Photo set photo_data = 0x0F

And in fact selecting it out of the table will show you it is 0x0F.


Reading Data from SQL Server database I want to read data from each cell of database. If the corresponding value is encountered then I have increment a variable count for that corresponding variable.

Consider the following table

Ques1   Ques2   Ques3   Ques4   Ques5
   1      2       3       1        3
   3      4       1       1        2
   2      2       2       1        2
   1      1       3       4        1
   3      1       4       1        1

So from the above table data, I want to extract the count of each rating. For example the
count of 1 in the above table is 11 and the count of 2 is 6 and the count of 3 is 5 and so
on.. So I have to get the following table as my output

Count1  Count2  Count3  Count4
  11       6       5      3

I have some basic knowledge for incrementing count for that variable but I don't know how to navigate from one cell to another and check its value.

In order to get the count across multiple columns, I'd suggest looking at unpivoting the data.  Since you are using SQL Server 2008+, you can use CROSS APPLY to convert your multiple columns into multiple rows. Once the data is in the rows, you can easily count the occurrences.

select
  sum(case when value = 1 then 1 else 0 end) Count1,
  sum(case when value = 2 then 1 else 0 end) Count2,
  sum(case when value = 3 then 1 else 0 end) Count3,
  sum(case when value = 4 then 1 else 0 end) Count4,
  sum(case when value = 5 then 1 else 0 end) Count5
from yourtable
cross apply
(
  values
    (1, Ques1),
    (2, Ques2),
    (3, Ques3),
    (4, Ques4),
    (5, Ques5)
) c (col, value);

See SQL Fiddle with Demo.

Note, this could also be written using the UNPIVOT function:

select
  sum(case when value = 1 then 1 else 0 end) Count1,
  sum(case when value = 2 then 1 else 0 end) Count2,
  sum(case when value = 3 then 1 else 0 end) Count3,
  sum(case when value = 4 then 1 else 0 end) Count4,
  sum(case when value = 5 then 1 else 0 end) Count5
from yourtable
unpivot
(
  value
  for col in (Ques1, Ques2, Ques3, Ques4, Ques5)
) un;

See SQL Fiddle with Demo

If you don't want to UNPIVOT the data, you could also CROSS JOIN to a virtual table similar to:

select
  sum(case when DATA = 1 then 1 else 0 end) Count1,
  sum(case when DATA = 2 then 1 else 0 end) Count2,
  sum(case when DATA = 3 then 1 else 0 end) Count3,
  sum(case when DATA = 4 then 1 else 0 end) Count4,
  sum(case when DATA = 5 then 1 else 0 end) Count5
from
(
  select
    CASE s.col
      WHEN 'Ques1' THEN Ques1
      WHEN 'Ques2' THEN Ques2
      WHEN 'Ques3' THEN Ques3
      WHEN 'Ques4' THEN Ques4
      WHEN 'Ques5' THEN Ques5
    END AS DATA
  from yourtable
  cross join
  (
    select 'Ques1' AS col UNION ALL
    select 'Ques2' UNION ALL
    select 'Ques3' UNION ALL
    select 'Ques4' UNION ALL
    select 'Ques5'
  ) s
) s;

See SQL Fiddle with Demo

All versions give the result:

| COUNT1 | COUNT2 | COUNT3 | COUNT4 | COUNT5 |
|--------|--------|--------|--------|--------|
|     11 |      6 |      5 |      3 |      0 |


How to force feed offset keyword a number calculated from table I am trying something as shown here.

As in this sql fiddle http://sqlfiddle.com/#!9/8d217/4 I created table

CREATE TABLE something (`some_column` int);
INSERT INTO something (`some_column`) VALUES (10),(20),(30),(40),(50);

I am trying to find median value so following is the query

select some_column from something order by some_column limit 1 offset
(select floor(count(some_column) / 2) from something);

MySQL complains about the query part after offset; and it does accept hard coded values like any integer instead of sub-query like (select floor(count(some_column) / 2) from something).

I am guessing the offset expects to get hard coded value and not a column with a value. I did try other options like using variables @ but cannot make it work.

My question is; if I want to use a value from query operation in offset or limit and not a hard coded value, how should I go about it?

Plan A:  Write a stored procedure to construct (via CONCAT()) the query you need, then use prepare and executed.

Plan B:  Create a temp table with an AUTO_INCREMENT column in order to number the rows.  Then look in that table for the answer.

Plan C:  Use @variables to count through the table.

Plan D:  http://www.artfulsoftware.com/infotree/queries.php#444


Unrelated tables and time intervals I am going to comment on the scenario that I currently have when designing my database.

I have the following tables:

user (id, name, lastname)

role (id, name)

user_role (user_id, role_id, from_date, to_date)

period (id, name, from_date, to_date)

The period table serves as a data master to know the name of the periods that I define in my application.

In this case I mention only the user, role and user_role tables, but there are many more tables that have periods.

The intention of the period table is to act as a labeling system for the different periods that exist in the tables of my model. This table is not related in any way by any foreign key with other tables. To do the searches and relate other tables to this, a join is made using dates from and dates to.

For example, a tuple of the user_role table can have a date from and a date until that covers several periods defined in the period table, so it would be labeled with the names of the periods it comprises.

Is it okay for the period table to be an unrelated table? It only serves to give names to the periods, that's why I put it this way. Can you think of another better way to raise it? Should the period table be related to the other tables in some other way?

Thanks in advance.

Thank you for your response on my queries.

My thoughts on this:

Periods are only to label your data
It is not labeling every data of your database
Since as per your clarifications "When inserted, it is not checked if there is a period to insert the information", periods may overlapped each other
Query is not essentially labeling every data with a periods
Database is very small in size,

Linking a table to others needs some sort of consistency which is not available in this case.  Seeing these inputs, IMHO, we can safely keep this table as separate entity.

From Performance aspects, we can make better indexes based on our application's need on period table to avoid any bottleneck.

Thank you,

Sanjeeva


Local CouchDB on Mac Opens New Browser Tab Every 5 Seconds I am doing some testing with CouchDB. I downloaded the CouchDB application on my Mac and ran it - it opened in my browser, I verified, created a database, added docs ... then I closed the application.

Next time I opened the CouchDB application, it opened a screen with the side menu but no other content (see attached image) and proceeds to open a new and identical tab every 5-10 seconds. The only way I can stop it is by closing the CouchDB application.

Any ideas about what is going on here?

If you have another CouchDB running, then close it first (you might like to uninstall to stop it restarting on the next boot).  It seems like the CouchDB application fails when it can't get its expected port, but then restarts.  I uninstalled my CouchDB 1.6 (installed via homebrew) and am now able to run CouchDB 2.0 without this browser tabs problem!


Should I create multiple tables for different entity states, statuses or stages? I have a tasks table in my database and, in the business domain of interest, tasks can have multiple states: “open”, “begun”, “in review” and “completed”.
Despite having “open” and “begun” in the same table, identified using flags, I decided for some reason early on to have “in review” and “completed”  tasks in their own separate tables. While this seemed like a good idea to start off with, making it easy to query for specific things, when I thought about implementing a comment feature for tasks I realised that the task_id value would be changing across the three tables.
Have I gone about this all wrong?
Current considerations
Some solutions I thought up would be to redo the organisation and put everything in one table, using flags to differentiate purely between the states (this would lead to a fair amount of reworking); to create some kind of UUID that is transferable across the tables - though I think there might issues with performance if this is done; and finally, to set the IDs across the tables to be no-longer auto incremented and to simply “inherit” the original value from the first task table (which auto increments).
What's the done thing in this situation? I'm not the most versed in database design and I'm kind of making this up as I go along.
Any help would be greatly appreciated.
Responses to further information requests made by @MDCCL via comments

So, the state (or status) of a task can change over time, right? How many times can the same concrete task present the same state, let us say, “open”? Will it vary depending on the kind of state presented by the relevant task?

Yes, a task.state could theoretically change up to four times. I won't go into specifics of the logic but the longest chain would be “begun” → “completed” → “review” → “completed”. And each time, currently, they're being put into another table.
The exception is whether it's “begun” or not, a user may abandon and then re-undertake the task infinitely - in theory at least. Which is at least helped by the fact that the “begun” status is a flag.

Ok, but a certain task will present, at all times, exactly one particular “present” state, right? For example, when a task row is INSERTed, it should be accompannied by a certain state value, which I assume would be “open”. Later, the state of such a task will evolve to only one of the other possible options, that is, either “begun” or “in review” or “completed”.

Yea, that's exactly right. A task will always be in a stage, and can't be in multiple ones.

Once a particular task is deemed as “completed”, can it change to another state?

Yes, it can be sent back to “review” if certain parameters are reached. And from “review” it can either go back to “begun” or back to “completed”.

The way I see it, the fact that (a) a concrete Task can present distinct States at different points in time entails (b) the creation of a table that is going to contain a time series, as I will detail below.
Business rules
In order to define the structure and constraints of a relational database, it is paramount to first identify and formulate the corresponding business domain rules (i.e., delineate the conceptual schema) with precision, taking into account the relevant entity types, properties and the corresponding  interconnections. In this way, some of the formulations that are especially significant in the scenario under consideration are:

A Task presents one-to-many StateSpecifications
A Task cannot present more than one StateSpecification at the same Instant
A StateSpecification is considered Current

from the particular Instant in which it is entered
until the exact Instant when the successive
StateSpecification is entered

A State defines zero-one-or-many StateSpecifications
A Task receives zero-one-or-many Comments

Consequently, based on the aforementioned formulations, I created the IDEF1X† diagram shown in Figure 1:

As you can see, Task and State are depicted as individual entity types, each with its own group of properties (or attributes) and its own (direct) associations (or relationships), which are expressed via the corresponding verb phrases, lines, cardinalities and FOREIGN KEY (FK) marks.
The entity type denominated StateSpecification, associated with the two brought up before, is a central aspect in the solution I will explain below.
Expository logical design and sample data
I shaped an expository logical design, based on the IDEF1X diagram above, by means of the DDL structure that follows:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient physical implementation settings; e.g.,
-- a good indexing strategy based on query tendencies.

-- As one would expect, you are free to use your
-- preferred or required naming conventions.

CREATE TABLE Task (
    TaskNumber      INT      NOT NULL,
    Description     CHAR(90) NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Task_PK  PRIMARY KEY (TaskNumber)
);

CREATE TABLE State (
    StateCode   CHAR(1)  NOT NULL,
    Name        CHAR(30) NOT NULL,
    Description CHAR(60) NOT NULL,
    --
    CONSTRAINT State_PK  PRIMARY KEY (StateCode),
    CONSTRAINT State_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT State_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE StateSpecification (
    TaskNumber        INT      NOT NULL,
    SpecifiedDateTime DATETIME NOT NULL,
    StateCode         CHAR(1)  NOT NULL,
    --
    CONSTRAINT StateSpecification_PK         PRIMARY KEY (TaskNumber, SpecifiedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT StateSpecification_to_Task_FK FOREIGN KEY (TaskNumber)
        REFERENCES Task  (TaskNumber),
    CONSTRAINT StateSpecification_to_State   FOREIGN KEY (StateCode)
        REFERENCES State (StateCode)
);

CREATE TABLE TaskComment (
    TaskNumber      INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    Body            TEXT     NOT NULL,
    --
    CONSTRAINT TaskComment_PK      PRIMARY KEY (TaskNumber, CreatedDateTime),
    CONSTRAINT TaskComment_to_Task FOREIGN KEY (TaskNumber)
        REFERENCES Task (TaskNumber)
);

--
--

The State table
The State table previously illustrated fulfills a look-up role, and is meant to contain five rows (as per the five State samples you provided):

 +-—————————-+-—————————-+-———————————————-+
 | StateCode | Name      | Description     |
 +-—————————-+-—————————-+-———————————————-+
 | O         | Open      | Indicates that… |
 +-----------+-----------+-----------------+
 | B         | Begun     | Indicates that… |
 +-----------+-----------+-----------------+
 | R         | In review | Indicates that… |
 +-----------+-----------+-----------------+
 | C         | Completed | Indicates that… |
 +-----------+-----------+-----------------+

Note the definition of a PRIMARY KEY (PK) constraint on a column retaining values that are, at the same time, meaninfgul (regarding interpretation by end users and technicians) and small (in terms of bytes at the physical implementation level), which makes it both readable and fast regarding, e.g., data retrieval.
If new business domain States arise, you can of course INSERT the corresponding rows INTO the State table.
The State Specification table
Now, suposse that Task no. 1750 presents the following State History in the form of (unordered) rows held in the StateSpecification table:

 +-——————————-+-———————————————————————-+-—————————-+
 | TaskNumber | SpecifiedDateTime       | StateCode |
 +-——————————-+-———————————————————————-+-—————————-+
 | 1750       | 2016-12-01 16:58:12.000 | O         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-02 09:12:05.000 | B         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-04 10:57:01.000 | R         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-07 07:33:08.000 | C         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-08 12:12:09.000 | R         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-08 19:46:01.000 | B         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-09 06:24:07.000 | R         |
 +------------+-------------------------+-----------+
 | 1750       | 2016-12-11 07:24:07.000 | C         |
 +------------+-------------------------+-----------+

As demonstrated, each value in the SpecifiedDateTime column indicates the precise point in time in which a given Task (via the TaskNumber) started presenting a particular State (by virtue of StateCode).
Seeing that the StateCode column keeps values that have a quite clear intention, it helps to save the usage of a JOIN clause. It is paramount to make clear that this does not imply that JOINs are something detrimental (they are, in fact, fundamental and powerful instruments in any relational database) but, whenever suitable, reducing the consumption of the valuable resources of the system is decidedly practical.
The Comment table
The Comment table, as exemplified above, can easily be related to the Task table via a FK constraint definition, because the PK value of a Task will remain the same, no matter if its State undergoes zero, one or more modifications.
I assume that, in the business context of relevance, a Comment (a) will always depend on the existence of a Task occurrence and (b) will always belong to the same indidivual Task occurrence; therefore, I defined the Comment table with a composite PK that incorporates the PK column of Task and the exact DateTime in which a particular Comment was created. Apart from that, you might like to evaluate the attachment of a column for system-controled surrogates (e.g., a column with the AUTO_INCREMENT attribute in MySQL) to this table, it may or may not offer a better execution performance (probably not), so carrying out some thorough testing sessions would clarify the case (a non-data addition, as is the case of columns holding system-controled surrogates, should always be well assessed and justified).
Data derivation code samples
There are some important data points that will very likely be required with respect to the scenario in question, but they should be obtained as derived (or calculated) values.
For example, if you need to obtain the CurrentState for Task no. 1750, you can derive it with the next SELECT statement:
SELECT T.TaskNumber,
       SS.StateCode AS CurrentStateCode,
       SS.SpecifiedDateTime
     FROM Task T
     JOIN StateSpecification SS
       ON T.TaskNumber = SS.TaskNumber
    WHERE T.TaskNumber = 1750 -- You can provide a parameter instead of a fixed value.
      AND SS.SpecifiedDateTime = (
                                    SELECT MAX(SpecifiedDateTime)
                                          FROM StateSpecification InnerSS
                                         WHERE T.TaskNumber = InnerSS.TaskNumber
                                 );

When you face the necessity to get the CurrentState for all Tasks, you can declare a statement like the one that follows:
SELECT T.TaskNumber,
       SS.StateCode AS CurrentStateCode,
       SS.SpecifiedDateTime
     FROM Task T
     JOIN StateSpecification SS
       ON T.TaskNumber = SS.TaskNumber
    WHERE SS.SpecifiedDateTime = (
                                    SELECT MAX(SpecifiedDateTime)
                                          FROM StateSpecification InnerSS
                                         WHERE T.TaskNumber = InnerSS.TaskNumber
                                 );

If you have to derive the StartDate and EndDate of all the StateSpecifications that correspond to Task no. 1750, you may utilize:
SELECT T.TaskNumber,
       T.Description,
       SS.StateCode,
       SS.SpecifiedDateTime AS StartDateTime,
       (
          SELECT MIN(SpecifiedDateTime)
                FROM StateSpecification InnerSS
               WHERE T.TaskNumber = InnerSS.TaskNumber
                 AND InnerSS.SpecifiedDateTime > SS.SpecifiedDateTime
       ) AS EndDateTime
    FROM Task T
    JOIN StateSpecification SS
      ON T.TaskNumber = SS.TaskNumber
   WHERE T.TaskNumber = 1750 -- You can provide a parameter instead of a fixed value.
ORDER BY StartDateTime DESC;

In case you must obtain the StartDate and EndDate of all the StateSpecifications, i.e., the whole period in which they were current:
SELECT T.TaskNumber,
       T.Description,
       SS.StateCode,
       SS.SpecifiedDateTime AS StartDateTime,
       (
          SELECT MIN(SpecifiedDateTime)
                FROM StateSpecification InnerSS
               WHERE T.TaskNumber = InnerSS.TaskNumber
                 AND InnerSS.SpecifiedDateTime > SS.SpecifiedDateTime
       ) AS EndDateTime
    FROM Task T
    JOIN StateSpecification SS
      ON T.TaskNumber = SS.TaskNumber
ORDER BY StartDateTime DESC;

VIEW definitions
Naturally, one or more of the SELECT statements displayed above can be set up as VIEWs, so that you can arrive at, e.g., the pertinent information directly FROM one single table (a derived one, yes, but it would still be a table).
Demos
I created live demos of the code here deliberated in this SQL Fiddle (running on MySQL 5.6) and in this db<>fiddle (running on Microsoft SQL Server 2014, if someone is interested).
Responses to comments

I think I shall have to go about performing this modification, and think the system will benefit from the additional functionality of the StateSpecification table. It goes to show that good database design can accommodate all our needs without making things messy.

I could not agree more with your second assertion. A database that is designed properly (conceptually, logically and physically), usually becomes a key element in a software development project. If a relational database reflects the business context of interest with accuracy, it can —to be brief— ease its maintenance substantially and offer vast versatility. That is why it is very useful to analyze carefully the structure of the relevant things, to observe them in terms of what they are in the real world, and then represent them with precision in a database implementation.
The data, in and of itself, is a highly valuable organizational asset and, a consequence, it should be administered as such. A reliable manner to achieve said objective is employing technical means that are supported on sound theory, and in the field of data management there is nothing more sound than the relational model. This is in agreement with the Dr. E. F. Codd’s 1981 Turing Award Lecture, entitled Relational Database: A Practical Foundation for Productivity.

Also, just to clarify, the StateSpecification table has to be updated manually on every change of state, correct?

Basically yes, the StateSpecification table has to be brought up to date each time that a Task presents a State change. This procedure should be performed by way of an INSERT operation that introduces a new StateSpecification row that should include a FK value that points to the Task row that applies.
It is opportune to stress that when a Task row is INSERTed, it will have to present a StateSpecification indicating the State deemed as ‘Open’ (by dint of an ‘O’ value retained in the StateCode column), hence you should make use of well defined ACID TRANSACTIONS so that both the Task row and its StateSpecification counterpart are treated as a single unit. In this manner, the two respective INSERT operations can either succeed or fail as a whole, maintaining the involved data consistent with the identified business rules.
Comparable scenario
You might find of help

this Q & A,

as it includes a case that has similar features.

Endnotes
† Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.


Constraints between tables on a variable im aiming to add a constraint on the number of bicycles allowed to be stored in each terminal - the number differing between terminals.

These are my original tables

create table terminal(
terminal_id serial primary key,
terminal_town varchar(30) not null,
terminal_street varchar(30) not null,
terminal_max_capacity smallint not null,
sponsor_payment_id serial references sponsor_payment(sponsor_payment_id)
);

    create table bicycle(
bicycle_id serial primary key,
bicycle_make varchar(30) not null,
bicycle_model varchar(30) not null,
bicycle_colour varchar(30) not null,
bicycle_type varchar(14) not null,
terminal_id serial references terminal(terminal_id) on delete cascade
);

When adding this:

constraint terminal_max_capacity_check check (references bicycle(bicycle_id between 1 and terminal_max_capacity)

It didnt work.

any ideas.
Thanks

I think that I would do something like the following.

Have a (sort of) joining table (or Associative Entity in the technical jargon) called slot.

If it's a manual system and you don't actually have a physical slot for individual bicycles (i.e. bicycles are stored behind a counter and allocated on an ad-hoc basis - but I'm also assuming that you keep track of individual bikes by bicycle_id), that's OK. Just "pretend" that you have individual slots and fill them virtually in sequence until there are no more either for filling or emptying. This solution also has the advantage of being ready to go in the event of slots being unsupervised as they are in my city - just out on the street!

So, do the following:

CREATE TABLE slot
(
  slot_id SERIAL PRIMARY KEY,
  terminal_id INTEGER NOT NULL,
  bicycle_id  INTEGER NOT NULL,  -- could have 0 here for unoccupied slots if you dont' want to deal with NULLs!
  CONSTRAINT slot_terminal_bike_uq UNIQUE (terminal_id, bicycle_id), -- assumes that a given bike has a "home" terminal - YMMV
  CONSTRAINT slot_terminal_fk FOREIGN KEY (terminal_id) REFERENCES terminal (terminal_id),
  CONSTRAINT slot_bicycle_fk  FOREIGN KEY (bicycle_id)  REFERENCES bicycle  (bicycle_id)
);

Then, have the same number of slots per terminal as there are total bikes at that terminal. All you have to do to see the number of available bikes is:

SELECT COUNT(*) FROM slot WHERE bicycle_id != 0

No three-value logic to worry about.

I think that experimenting with this idea might be helpful. Obviously, I don't know your exact circumstances, but hopefully it will get you going and/or thinking! Schema design is an iterative process (see the links here). Design a first pass of your system, run some code, fix the bugs (and there will be bugs), rinse and repeat.


How to prevent users other than DBAs from creating database links To begin, please keep in mind that I am a very green DBA. I have done prior research and have not really found anything too promising, or maybe it just seems that way because I am still struggling in my understanding of the monster that is Oracle. So please forgive me and kindly point me in the proper direction if my question sounds arbitrary.

Scenario:

some users create database links into several heavy data warehouses.
these users neglect to close or drop DB link.
users process continues to run and bogs down the DW's performance.

Goal:

prevent these users from creating database links, BUT still allow DBAs to create DB links

How could this be done? Could the answer be within OID capabilities? It seems that there could be to me, but my understanding of Oracle does not seem sufficient enough to be able to determine for sure.

UPDATE:
After investigating into user privileges, I discovered that privileges are not the problem. A remote database can be accessed with just a select statement and without a dblink. Revoking the CREATE DATABASE LINK privilege would not stop a user from accessing database B from database A.

However, this is only true when the DB_DOMAIN = "blank" and the global name = "fooDB"

A link is required if the DB_DOMAIN = foo.com and the global name = "fooDB.foo.com"

I could not find any documentation similar to this scenario or that explains why this happens. Why do I need to create a link when the global name = "fooDB.foo.com" but not when the global name = "fooDB"?

I think you are approaching this from the wrong end. A database link includes the user ID and password to connect to the remote server; if the user ID does not exists on the remote server or is not allowed to connect, the problem does not exist. If you don't want certain users to run queries against your data warehouse, don't authorize them there.

If you check the SQL reference you will see that users require the CREATE DATABASE LINK privilege to create those links. You can revoke the privilege from some users, but that would not prevent them from connecting directly to the remote database and running their queries there.