MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations I had a table with ~0.5M rows and tried to execute a simple query, something like:
UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999

but the result was

ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER

I have MaxNoOfConcurrentOperations=100000 and slightly more than 100000 rows which satisfy the where condition, so I have changed to this query:
UPDATE table
SET col1=-90 + RAND()*90, col2=-180+RAND()*360
WHERE col1=9999 ORDER BY ID LIMIT 100000

...and got the same error. And same error with LIMIT 1000.
I have updated rows using a query with LIMIT 100 in loop, but I think that it is a bad solution.
Questions

Why does MySQL Cluster behave this way?
How can I update many rows in a single table?

To solve the error message ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER you can use one of the possible solutions:

1) Increase MaxNoOfConcurrentTransactions even more until it works (note it take more resources (RAM))

2) Select the data from innodb into an outfile and load infile into
cluster (will be faster than 2).

Note: you faced this error message  since there is not enough resource on Dedicated for the cluster evnironment.


Simple insert statements are taking so long I have a scenario where one main stored procedure calls a nested procedure and that nested procedure contains simple insert statements for multiple tables and almost every insert is taking so long

I know there are lots of possibilities for instance Disk, CPU, Memory, indexes and triggers

How do I find Clustered and NON-Clustered indexes are not the reason of this slowness?

Insert Statement:

INSERT INTO PEDetail with(rowLock) (CID,CustID,PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID)
SELECT CID,@PID,@PpeID,PeID,SubSys1ID,SubSys2ID,CharacteristicID,cmbID,PETemplateID
FROM PEDetail with(nolock)
where PpeID = @OldPpeid

Table Structure:

 SET ANSI_NULLS ON
    GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PEDetail](
    [CID] [int] NOT NULL,
    [CustID] [int] NOT NULL,
    [PpeID] [int] NOT NULL,
    [PeID] [int] NOT NULL,
    [SubSys1ID] [int] NOT NULL,
    [SubSys2ID] [int] NOT NULL,
    [CharacteristicID] [int] NOT NULL,
    [cmbID] [int] NOT NULL,
    [PETemplateID] [int] NOT NULL,
 CONSTRAINT [PK_PEDetail] PRIMARY KEY CLUSTERED
(
    [CID] ASC,
    [CustID] ASC,
    [PpeID] ASC,
    [PeID] ASC,
    [SubSys1ID] ASC,
    [SubSys2ID] ASC,
    [CharacteristicID] ASC,
    [cmbID] ASC,
    [PETemplateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[PEDetail]  WITH CHECK ADD  CONSTRAINT [FK_PEDetail_PEHeader] FOREIGN KEY([PpeID], [PeID])
REFERENCES [dbo].[PEHeader] ([PpeID], [PeID])
GO

ALTER TABLE [dbo].[PEDetail] CHECK CONSTRAINT [FK_PEDetail_PEHeader]
GO

ALTER TABLE [dbo].[PEDetail] ADD  CONSTRAINT [DF_PEDetail_cmbID]  DEFAULT ((0)) FOR [cmbID]
GO

NON Clustered Indexes:

CREATE NONCLUSTERED INDEX [ix_PEDetail_CID_PeID_6] ON [dbo].[PEDetail]
(
    [CID] ASC,
    [PeID] ASC,
    [SubSys1ID] ASC,
    [SubSys2ID] ASC,
    [CharacteristicID] ASC,
    [PETemplateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CID_CharacteristicID_CID_CharacteristicID] ON [dbo].[PEDetail]
(
    [CID] ASC,
    [CharacteristicID] ASC
)
INCLUDE ( [PpeID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_PpeID_PeID] ON [dbo].[PEDetail]
(
    [PpeID] ASC,
    [PeID] ASC
)
INCLUDE ( [CID],
[CustID],
[SubSys1ID],
[SubSys2ID],
[CharacteristicID],
[cmbID],
[PETemplateID]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 85) ON [PRIMARY]
GO

The performance problem it's because of your clustered index. When you use clustered, and you add a new record, it needs to physically rearrange all records in the table to make space for it.

Drop the clustered index and create an unique non-clustered index instead. That should fix your performance problem.

As a general rule, I'd also recommend having a clustered index, but with only a numerical autoincremental id.


SQL Server SELECT column not in Group By clause? As you can see my query, this is the working one

SELECT m.MerchandiseId, m.MerchandiseName, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

What if i want to SELECT one more field as i.InboxMessage with the Last record of each group?

SELECT m.MerchandiseId, m.MerchandiseName, i.InboxMessage, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

I've tried some ways that I found in stackoverflow, but I still not manage to do it. Thousand of thanks if anyone would help on this.

FYI, I just solve my issue by using the subquery

SELECT m.MerchandiseId, m.MerchandiseName, (SELECT TOP 1 e.InboxMessage FROM Inbox e WHERE (e.ToMerchantId = m.MerchandiseId AND e.FromCompanyId = 10) OR (ToCompanyId = 10 AND FromMerchantId = m.MerchandiseId) ORDER BY e.CreatedBy ASC) AS InboxMessage, createdAt =  MAX(i.CreatedAt) FROM Inbox i
INNER JOIN [User] u
ON i.FromUserId = u.UserId
LEFT OUTER JOIN Merchandise m
ON u.MerchandiseId = m.MerchandiseId  OR i.ToMerchantId = m.MerchandiseId
WHERE i.ToCompanyId = 10 OR i.FromCompanyId = 10
GROUP BY m.MerchandiseId, m.MerchandiseName

Here's the code that I worked on


MongoDB Cluster - Can't access member of shard with credentials I have a Sharded Cluster and I am trying to access a specific member of a replica set within a shard.

When connecting to the mongos it is fine, but when using the same credentials on a member of the replica set I am unable to gain access even though my user has the role of root.

The error is:

  SCRAM-SHA-1 authentication failed for root on admin from client
  127.0.0.1:39356 ; UserNotFound: Could not find user root@admin

The mongos are started with:

mongos --configdb cfg/mongo-cfg0:27019,mongo-cfg1:27019,mongo-cfg2:27019 --bind_ip_all --keyFile "/etc/mongodb-keyfile"

The members of the replica set are started with:

mongod --auth --bind_ip_all --shardsvr --replSet a --smallfiles --oplogSize 50  --keyFile "/etc/mongodb-keyfile"

When connecting to the mongos I use the following locally:

mongo --port 27017 -u user -p password --authenticationDatabase admin

When connecting to the memnber of the replica set locally i try and use:

mongo --port 27018 -u user -p password --authenticationDatabase admin

I feel like there is something obvious that I am missing but I can not figure out what, any help would be greatly appreciated.

When you are using mongoS authentication is done against config servers admin database. When you connect directly to replica set, you are authenticating against replica set's admin database, where you don't have that root user set.

What can you do? Start replica set's (one by one) without --auth parameter (or equivalent config parameter) to maintenance mode, connect that RS with mongo command, create that root user (and other needed users). After that you can restart RS with authentication and you can do login.

shard members authentication requires local credentials. As what JJussi has said, you need to create local users on the sharded replica sets.


phpMyAdmin configuration storage is not completely configured message Today i have updated my PhpMyAdmin to 4.1.2
Now when i log in to PMA i am getting the message:

The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated. To find out why click here.

When i click on the link:

PMA Database ... not OK [ Documentation ]
General relation features Disabled

Do i need this features? Or i can ignore that message? Thanks in advance

You do not need the features to use phpMyAdmin. You can read more about what they include in the phpMyAdmin documentation.

To disable, just remove the corresponding lines from your config.inc.php (you can start here in the documentation; each entry up to and including $cfg['Servers'][$i]['designer_coords'] relates to the phpMyAdmin Configuration Storage features; if you have any of those lines in your config.inc.php you can simply remove them). You also may have $cfg['Servers'][$i]['controluser'] and $cfg['Servers'][$i]['controlpass'] entries, which generally are only used for the Configuration Storage features as well and in most cases can be removed.

VestaCP Team made and successfully tested error fixer for phpMyAdmin (no more annoying message "The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated"). We hope that fix will be useful and will help users.

Page on Github: https://github.com/skurudo/phpmyadmin-fixer

What the script does:

works from user root;
changes inside /etc/phpmyadmin/config.inc.php, fixes value;
makes a backup of /etc/phpmyadmin/config.inc.php in /root folder;
adds in a mysql pma user / and the table phpmyadmin (if the user "pma" or the table "phpmyadmin" already exists, the script will delete those!);
download and add table for database phpmyadmin;
clean temp files, leftover pma.txt (contains password for pma@localhost) and old config;


SSIS To Move Schemas I'm having one heck of a time trying to compare and move schemas for a new database. I've tried Red-Gate, ApeXSQL etc. and every time I turn around it's not working. I have a customer who has 5 databases in which all 5 have similar schemas. Some have additional stored procs etc. Let me take a step-back and state that Red-Gate does work, however, because I have 73 schemas to filter by I cannot enter every single schema into Red-Gates filtering. So, I thought about trying SSIS, which I've never used before. My objectives and end goals are outlined below. Note: I'm using Visual Studio 2017 and SSDT for VS 2017

If using SSIS I need a way to skip\continue the process if an object is already present.

Objectives:
* To compare\copy the schemas between Database-1 to Database-2 in which only the 73 pre-determined schemas will be created on Database-2.
* To create the tables, stored procedures, functions etc. on Database-2 but, only the ones that belong to the 73 schemas.

End Goal:
* To have a new database that contains all objects for the 73 pre-determined schemas.

Use Data tier Application to upgrade the database.


Generate a sequence of timestamps but skip given periods I have a table things_to_do in PostgreSQL. For example:

thing_id
---------
 1
 2
 3
 4
 5
 6

And I want to add a timestamp to every row:

SELECT thing_id, '2016-02-10 19:57:00'::timestamp
                  + '0:01:00'::interval * row_number() OVER () AS estim_date
FROM things_to_do
ORDER BY 1;

 thing_id |    estim_date
----------+---------------------
 1        | 2016-02-10 19:57:00
 2        | 2016-02-10 19:58:00
 3        | 2016-02-10 19:59:00
 4        | 2016-02-10 20:00:00
 5        | 2016-02-10 20:01:00
 6        | 2016-02-10 20:02:00

Basically, I'm doing one thing every minute. But I don't want to work after 20:00 and not before 09:00. I would like my request to skip the night.

The question is:

Is it possible to give my request a period of time and skip that time? Some sort of modulo, maybe?

The final result I would like to have:

 thing_id |    estim_date
----------+--------------------
 1        | 2016-02-10 19:57:00
 2        | 2016-02-10 19:58:00
 3        | 2016-02-10 19:59:00
 4        | 2016-02-11 09:00:00
 5        | 2016-02-11 09:01:00
 6        | 2016-02-11 09:02:00

You can assign any numbers with a derived table. generate_series() is instrumental for a simple solution.

With a table like this:

CREATE TABLE things_to_do AS
SELECT * FROM generate_series (1,6) AS thing_id;

A simple solution for the given example

SELECT *
FROM   things_to_do
JOIN (  -- derived table with prepared timestamps
   SELECT row_number() OVER () AS thing_id, *
   FROM  (
      SELECT *
      FROM   generate_series(timestamp '2016-02-10 19:57'
                           , timestamp '2016-02-10 19:59'
                           , interval  '1 min') estim_date
      UNION ALL
      SELECT *
      FROM   generate_series(timestamp '2016-02-11 09:00'
                           , timestamp '2016-02-11 09:02'
                           , interval  '1 min') estim_date
      ) sub
   ) t USING (thing_id)
ORDER  BY 1;

Just append two sets ..

For a more general solution:

SELECT row_number() OVER (ORDER BY d, ts) AS id
     , ts + interval '1 day' * d AS ts
FROM   generate_series(timestamp '2016-02-10 09:00'
                     , timestamp '2016-02-10 19:59'
                     , interval  '1 min') ts
     , generate_series(0,6) d
ORDER  BY 1;

Produces a table with one row per minute from 09:00 to 19:59 for seven consecutive days starting at 2016-02-10 and an id number starting from 1 ...

Or:

SELECT row_number() OVER (ORDER BY d, ts) AS id
     , d + ts::time AS ts
FROM   generate_series(timestamp '2000-01-01 09:00'
                     , timestamp '2000-01-01 19:59'
                     , interval  '1 min') ts
     , generate_series(timestamp '2016-02-10 0:0'
                     , timestamp '2016-02-17 0:0'
                     , interval  '1 day') d
WHERE  d > '2016-02-10' OR ts > '2000-01-01 19:56'  -- start at '2016-02-10 19:57'
ORDER  BY 1;

Or:

SELECT row_number() OVER (ORDER BY ts) AS id
     , ts
FROM   generate_series(timestamp '2016-02-10 19:57'
                     , timestamp '2016-02-17 19:59'
                     , interval  '1 min') ts
WHERE  ts::time BETWEEN '08:00' AND '19:59'
ORDER  BY 1;

Way 1 :
I would suggest to have case embedded in your select.
Way 2 :
Have a function which does all the maths and call that.


How to create a table in a database on a different server I need to create Table B in database B on SQL Server B by querying Table A in database A on SQL Server A. Table B does not exist in database B. SQL Server A and B are linked. I wonder how could this be done?

Thanks

You can easily achieve this using OPENROWSET.

Connect on SQL Server B;

SELECT *
INTO TABLE_B
FROM OPENROWSET('SQLOLEDB', 'server=ServerA;uid=Login;pwd=Password;database=Database_A',
'set nocount on
SELECT * FROM TABLE_A;
')

Make sure your linked server has RPC and RPC Out enabled:

EXEC master.dbo.sp_serveroption @server=N'<linked server>', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'<linked server>', @optname=N'rpc out', @optvalue=N'true'

Then use EXEC AT and dynamic SQL:

EXEC ('CREATE TABLE [master].dbo.Test (ID INT IDENTITY, CharData VARCHAR(3))') AT [<linked server>]

More info can be found here.


How do I copy a table with SELECT INTO but ignore the IDENTITY property? I have a table with identity column say:

create table with_id (
 id int identity(1,1),
 val varchar(30)
);

It's well known, that this

select * into copy_from_with_id_1 from with_id;

results in copy_from_with_id_1 with identity on id too.

The following stack overflow question mentions listing all columns explicitly.

Let's try

select id, val into copy_from_with_id_2 from with_id;

Oops, even in this case id is an identity column.

What I want is a table like

create table without_id (
 id int,
 val varchar(30)
);

From Books Online

  The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.

Down the page:

  When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

  The SELECT statement contains a join, GROUP BY clause, or aggregate function.
  Multiple SELECT statements are joined by using UNION.
  The identity column is listed more than one time in the select list.
  The identity column is part of an expression.
  The identity column is from a remote data source.

  If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.

So... you could theoretically get away with:

select id, val
into copy_from_with_id_2
from with_id

union all

select 0, 'test_row'
where 1 = 0;

It would be important to comment this code to explain it, lest it be removed the next time someone looks at it.

You don't. select * into preserves the identity.


MySql Order by isnull() Performance Problem my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.

AddDate and Price has an index.

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
order by isnull(Price), Price asc limit 50

Explain the sql shown that it is not using the Price index.
So i tried to improve the query and has come out with below sql

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
and Price is not null
order by Price asc limit 50

The new sql works a lot faster and explain shown it uses Price index but the problem is that Price with null values will never selected.

Looking for any comments or suggestions on how to solve this problem. Thanks.

Your original clause:

order by isnull(Price), Price asc limit 50

Uses a function on the Price column. In your case, the function is isnull(); but it does not matter - any function on your column, e.g. COALESCE(Price, ...), Price + 1, etc., would make for the same result.

You see, once there's a function on a column, MySQL is then unable to use that column. Consider: the index uses a BTREE on Price, indexing your millions of rows in ascending order. But your request, to order by isnull(Price) requests an altogether different thing: you just want to provide with NULLs or not-NULLs; well, MySQL will not use the index for that.

I suppose it would be nice of MySQL to recognize a set of "safe" functions, which are monotonic with the index; but MySQL gives up an index the moment it sees a function. It just does not predict how the result of the function looks like.

Other databases support Index functions; the MariaDB fork supports virtual columns and indexes on virtual columns -- both solution can help you out with your query. Plain MySQL just can't eat it.

Why do not just sort in descendant way ?

SELECT id,
       price
FROM   tblstock
WHERE  adddate >= Date_sub(Curdate(), INTERVAL 10 day)
ORDER  price DESC
LIMIT  50;

NULL values are shown, in bottom of your list and index is used = Your three conditions :)

Max.


Best practice design schema for implementing form approval tracking? I'm in the middle of creating an ASP.NET MVC 5 web app using windows SQL server as my DB setup.

The app has a very simple purpose: Allow a user to submit a form to be signed by multiple people, traveling "up" the chain of command to be approved by each person. After the final approved the form travels back "down" to the requester.

Now I understand of course that within a database context, the form isn't going anywhere at all; variable are changing making the form alternately visible/invisible to different people.

But what I want to know is: what is the best way to implement this? Specifically, the best way to "track" forms that are traveling the chain.

As I have it right now, I have a model of a request form (containing info like date of request, reason for it, etc.), and that's all dandy. But I'm hung up on the BEST WAY to implement the chain. I was thinking of having two extra columns: CurrentApprover and Approved. CurrentApprover is a foreign key representing the primary key of the person that needs to approve your form next, and Approved represents whether the form has been fully approved.

This is simple, and it would work in practice. However it may limit me down the road in manipulating the pipeline or such. Maybe I could have a "manifest" table which contains all active requests and their details? I like this idea better now that I type it out.

Does anyone have a suggestion?

Also, 2 quick questions if someone would be so kind:

1) This app will be processing thousands of forms each year, and though I know tables do fine with millions of entries, I was wondering where there would be any advantage/best practice reason to have different tables for different company divisions (there are 3 distinct divisions, but the forms are the exact same and the process for approval is the exact same).

2) If a form is foreign keyed to its submitter, and the user data table for the submitter contains info that is being passed up with the request form, do I still need to/ should I include those fields explicitly in the request form table, or is it sufficient/better to just query those values when needed. I feel like the latter is better, because a form has a submitter ID (employee ID per-say), and I can just use that to look up the employee's user info when I need it.

Thank you very much!

I am working on a similar process and found this very useful site that proved helpful for my processing issues.  This details a request and approval process that can be changed to fit other applications.


I don't know how to fix ORA-04091 mutating trigger error I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table.

The error is:

  error ORA-04091: table USUARIO.LINEAS is mutating, trigger/function may not see it ORA-06512: at "USUARIO.TRG_ACTUALIZARPEDIDO", line 5 ORA-04088: error during execution of trigger 'USUARIO.TRG_ACTUALIZARPEDIDO'

I've learning about it, but can't find a solution or understand by myself what's the problem.

CREATE OR REPLACE TRIGGER trg_actualizarpedido
AFTER INSERT OR UPDATE OR DELETE ON LINEAS
FOR EACH ROW

DECLARE
    sumaImporteLineas NUMBER := 0;

BEGIN
    SELECT SUM(IMPORTE) INTO sumaImporteLineas FROM LINEAS WHERE NUMPEDIDO = :NEW.NUMPEDIDO;
    UPDATE PEDIDOS ped SET ped.TOTAL = sumaImporteLineas WHERE ped.NUM = :NEW.NUMPEDIDO;
END;

Thanks!

ORA-04091

This is Oracle's way of saying:

  you really don't want to do that.

Here is my short-list of workarounds.

Option 1

Use a VIEW.

This is the preferred method.

Option 2

Use a Materialized View.

Yes, you can Fast Refresh SUM() on commit.

Option 3

Use a TAPI (Table API). (a Package)

All DML operations must go through this Package; never against the actual table.

Option 4

3-Trigger solution.  (This could be a single Complex trigger.)

Without proper locking, you could have "lost updates".

Even with proper locking, Dead Locks could occur.

You can not query the table that caused a trigger to fire inside the trigger itself.
But in this case, I don't see any need to. You can use :NEW to access the values you're selecting from the table instead:


Unexpected Always On Availability groups failover happening--- databases changing to resolving I recently had done a SQL patch on a two node SQL availability group of SQL Server 2012. Everything went well as far as I can tell. Now two days after the patch unexpected failover happened. Then today as well unexpected failover happened.

For a temporary period, the primary of the availability group goes in to resolving state and forces it into failover to resume data movement as normal.

Now, I am not sure if this is anything related to the patch. Because the error logs show other issues. Here are the logs from the two times this happened.

  Date      6/11/2018 4:40:06 AM Log        SQL Server (Current - 6/11/2018
  2:25:00 PM) Source        Logon Message Error: 983, Severity: 14, State: 1.

  Date      6/11/2018 4:40:06 AM Log        SQL Server (Current - 6/11/2018
  2:25:00 PM) Source        Logon Message Unable to access database
  'accounting' because its replica role is RESOLVING which does not
  allow connections. Try the operation again later. Date        6/11/2018
  4:40:06 AM Log        SQL Server (Current - 6/11/2018 2:25:00 PM)

  And today Date        6/14/2018 3:13:47 PM Log        SQL Server (Current -
  6/14/2018 3:14:00 PM) Source      spid6s Message SQL Server has
  encountered 1 occurrence(s) of I/O requests taking longer than 15
  seconds to complete on file [C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EveriT31_Park.mdf] in database
  [EveriT31_Parx] (21).  The OS file handle is 0x0000000000000B00.  The
  offset of the latest long I/O is: 0x00000002750000

  Date      6/14/2018 3:14:04 PM Log        SQL Server (Current - 6/14/2018
  3:14:00 PM) Source        spid127 Message Error: 18056, Severity: 20, State:
  46.

  Date      6/14/2018 3:14:04 PM Log        SQL Server (Current - 6/14/2018
  3:14:00 PM) Source        spid127 Message The client was unable to reuse a
  session with SPID 127, which had been reset for connection pooling.
  The failure ID is 46. This error may have been caused by an earlier
  operation failing. Check the error logs for failed operations
  immediately before this error message. Date       6/14/2018 3:14:05 PM
  Log       SQL Server (Current - 6/14/2018 3:14:00 PM)

The AG is set for automatic failover but the alwayson group is not really changing roles. After these episodes, the primary remains primary and the secondary remains secondars.

Every log I look at the first error I get is either the I/O issue or connection pool error (Error: 18056, Severity: 20, State: 46.). I hesitate to say the problem is really that. I don't think it is related with the SQL patch I just did. I don't see anything that is related to it and I want to rule that one out.

Can anyone suggest what might be causing these? I looked at some articles, but none gave me a clear answer.

it is set for automatic failover but the alwayson group is not really changing roles. After these episodes, the primary remains primary and the secondary remains secondars.

I've seen this pattern (PRIMARY -> RESOLVING for a brief period -> PRIMARY, and the same for the SECONDARY) before.  In my case, we were using a file share witness as a third node for quorum.  When this "not failover" happened, it was because the server where the file share witness lived had been taken down for maintenance.

Windows Server Failover Clustering (WSFC) uses a process called the Resource Hosting Subsystem (RHS) to monitor the health of resources in the cluster.  This includes both AG nodes, and in my case the file share witness resource.

While the AG (and cluster) can stay up and running with just the two AG nodes, after the file share witness has been down for 5 minutes, a process called RHS Recovery is started.  You can read more about that here: Understanding how Failover Clustering Recovers from Unresponsive Resources

If all your resources are hosted in the same RHS process (which is the default behavior), this will cause that "blip" in AG availability while the RHS.exe process is restarted.

Normally after one failure, the problematic resource should be automatically marked as "SeparateMonitor" started up in it's own RHS.exe process (so that it doesn't continue to cause problems for your other cluster resources).  So I'm surprised this happened more than once, but maybe someone changed the setting back, or the behavior is different for different resources or Windows versions.

You can check if that setting is on, or set it manually, in the Cluster Manager.  Here's a screenshot:

Anyway, this problem, and especially your comment, sounded very familiar to me so I thought I'd share the experience in case it helps you or others.

I blogged in detail about the troubleshooting process I used for this situation here: Troubleshooting an AG Failure

You should see evidence that RHS Recovery is the root cause of your problem by pulling the cluster log (run the Get-ClusterLog PowerShell command) and checking for messages around the time of your AG blip that mention "DeadlockMonitor", which is what checks "down" resources and initiates the recovery process.

In case you need a failover to happen if your primary SQL Server instance experiences a slow disk read or something, you can try the approach explained here: Configure a flexible automatic failover policy for an AAG. Exactly, you can set the FAILURE_CONDITION_LEVEL AAG parameter higher than default (3, corresponds to "any critical server error"), most likely to 5 which corresponds to "any failover-able error" as described in the link.
ALTER AVAILABILITY GROUP AG1 SET (FAILURE_CONDITION_LEVEL = 5);

The root cause of that problem you describe is possibly a malfunction of your C: drive hardware, because long reads usually are a sign of disk problems before data loss starts happening.


Avoiding "Waiting for table metadata lock" when `ALTER TABLE DROP PARTITION`? I have some tables that many users need to access to:

mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
       Table: v3_cam_date
Create Table: CREATE TABLE `v3_cam_date` (
  `campaignid` mediumint(9) NOT NULL DEFAULT '0',
  `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `totalview` int(11) unsigned NOT NULL DEFAULT '0',
  `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `clickcharge` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `viewcharge` int(11) unsigned NOT NULL DEFAULT '0',
  `uv` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `uc` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `dt` date NOT NULL DEFAULT '0000-00-00',
  `ctr` decimal(5,3) NOT NULL DEFAULT '0.000' COMMENT '=-1: meaning not available(N/A)',
  `moneyc` int(11) unsigned NOT NULL DEFAULT '0',
  `moneyv` int(11) unsigned NOT NULL DEFAULT '0',
  KEY `ix_campaignid_dt` (`campaignid`,`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION p0 VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p01 VALUES LESS THAN (734502) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (734683) ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN (734863) ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN (734959) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (735141) ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN (735210) ENGINE = InnoDB,
 PARTITION MERGER_2013227 VALUES LESS THAN (735291) ENGINE = InnoDB,
 PARTITION pcurrent_2013227 VALUES LESS THAN (735292) ENGINE = InnoDB) */

When an user want to drop a partition ALTER TABLE v3_cam_date DROP PARTITION pcurrent_2013227, it may cause many transactions to be in the Waiting for table metadata lock state:

     Id: 31560182
   User: alice
   Host: 192.168.3.40:36132
     db: db
Command: Query
   Time: 806
  State: Waiting for table metadata lock
   Info: SELECT COUNT(DISTINCT A.`campaignid`)  INTO _campaigncomplete
        FROM `ox_campaigns` A
        INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
        INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
        WHERE A.`revenue_type` = 5 AND A.`deleted` = 0 AND A.`expire` = DATE_ADD(    (SELECT `sys_date_cpc` FROM `000_sys_params_v4`) , INTERVAL 1 DAY)
                AND A.`isExpired` = 0 AND IF( NAME_CONST('_permitid',3) = -1, 1=1, IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN (SELECT C.`user_id` FROM `selfserving_users` C WHERE C.`groupid` =  NAME_CONST('_groupid',17) ) ,A.`uid` =  NAME_CONST('userid',5770)))

What is the efficient way to accomplish this without locking?

What you are asking for is impossible. Regardless of storage engine, DDL of any kind will lock a table. If you must remove a partition from a table that is active, you should:

Setup MySQL Replication (if you haven't done so already)
Perform all SELECTs involving v3_cam_date against the Slave
STOP SLAVE; on the Slave
Perform ALTER TABLE ... DROP PARTITION on the Master.
Perform all SELECTs involving v3_cam_date against the Master
START SLAVE; on the Slave (Replicates ALTER TABLE ... DROP PARTITION to the Slave)

This is probably your only recourse. The only other recourse is to simply wait out the ALTER TABLE ... DROP PARTITION.
This situation requires some intervention in the application. Within your application, you would have to create a Write DBVIP on the Master and use the Read DBVIP with one of the following three(3) options:
OPTION #1

Keep the Read DBVIP on the Slave

OPTION #2

Keep the Read DBVIP on the Master
When you must do an ALTER TABLE, move the Read DBVIP to the Slave
When ALTER TABLE has been completed, move the Read DBVIP to the Master

OPTION #3

Setup Read DBVIP on a LoadBalancer
Remove Master from LoadBalanced DBVIP on demand

Option #1 seems to be the simplest way in the long run.

I've solved this issue by just restarting the MySQL service, and then try to drop the partition. After this the drop partition will take effect. This happens because the binlog apparently has something that hasn't finished with that table.

I hope this information could be helpful for you.


Best practice design when expecting large data in a table I have come across a way that kind of works for me, and was just wondering if it would have any side effects, or, if I'm just thinking about this the wrong way.

I first had this idea when I was building a relatively simple location database. It would hold a table of 249 countries, and a table of 143000 cities with their longitude and latitude.

--------------------------
-         Country        -
--------------------------
-   id  -    name        -
--------------------------
-    1  -   England      -
-    2  -    Wales       -
--------------------------

-----------------------------------------------------------
-                City                                     -
-----------------------------------------------------------
- id -     name   -      lng    -    lat     - COUNTRY_id -
-----------------------------------------------------------
-  1 -  London    -  -0.127758  - 51.507351  -    1       -
-  2 - Canterbury -   1.078909  - 51.280233  -    1       -
-----------------------------------------------------------

Now, when I was using the Haversine equation to find the closest city given a longitude and latitude, it would only do about 10 results per second. My thought behind this was because it had to do this calculation on 143000 cities ...

So, instead of go out and buy a supercomputer to do these calculations, I thought I could narrow down which cities it had to do the calculations on.

I done this by basically dividing the world into 2448 grid squares, and putting those cities in a table of their own, effectively now having 2448 tables. I then use PHP to find which grid square the given longitude and latitude resides in, and then query that table, and it's surrounding 'grid squares', or tables.

This resulted in a 10 fold speed increase, returning over 100 results per second.

I was wondering if the same concept could be used in say, a user database, where the tables may be split depending on the first 2 characters of the persons username. So, if you had 1,000,000 users, (And they were only allowed a-Z for their usernames), you could effectively have these spanned over 676 tables, averging about 1500 users per table, and then increasing the speed at which a user could log on?

Ha ... Notice the question mark at the end ...

So, I'm expecting a lot of 'Nope ... Thats just wrong' ... But I kinda want to know if my brains just having a stupid week, or if someone has seen something along these lines.

Yes, the same concept could be used.  What you have done is re-implement table partition, but in user space.  Most industrial-strength RDBMSs will have this built in.  The provided functionality often includes additional abilities, such as efficiently adding and removing partitions at run time without applicaiton changes.  By choosing to roll your own you miss out on these additional features.  Additionally you complicate some things, such as surrogate ID uniqueness checking, aggregate queries across your whole user community and DRI referencing the "user" table.

Be aware that your sub-tables are very unlikely to be well balanced.  There aren't many Mr. Aardvark or Ms. Zymology in the world but a lot of Smiths and Jones.

The reason key lookups are faster on smaller tables is because the indexes have fewer levels, assuming you have B-Tree indexes.  Therefore the DBMS has to read fewer pages to get from the index's root node to its leaf & data pages.  The index on your 676 sub-tables is likely to be only one or two levels deep and so incur only one or two page reads to read a key's row.  In contrast a full B-Tree built on 1M rows may be three or four levels deep and require that many page reads per lookup.  Built-in partitioning can give you similar benefits if you define your index as partitioned, too.

This is a good reason to keep your index keys compact, if you have a choice.  For example, if your user name is 30 characters long this will take 30 bytes to store and you will get a certain number on a page.  If instead you calculate an integer hash (4 bytes) of the user name and index that, there will be 30/4 = 7-ish times more rows per index page and the index will likely have fewer levels. (Of course you will have to account for potential hash collisions.)  Similarly limiting the amount of free space you have in the index to allow for inserts will help increase density.

I don't think the two problems you state are equivalent.

For faster logon with a table as you suggest would be using table partitioning and that would help by limiting disk I/O on the requisite tables.   But you might be able to get equivalent results by using a good index.


SQL Server Management Studio 2016 I'm having a strange problem with my SSMS 2016. Within the query window backspace, del, up, down, left, right keys have stopped working. When I reset my environment settings with "Import and Export Settings" to default setting it works but the changes don't persist when I exit the SSMS client. Every time I launch SSMS 2016 I have to do this exercise.

I also have SSMS 2014 on the same system, which works with out any problem, I tried to uninstall and install the SSMS 16 client but no luck.

Any one having the same problem?

Help please, how can I uninstall SQL Server Management Studio 2016 completely?

This happened in SSMS 2017.  There was a pop-under "Add table to view" dialog that I didn't know about.
When I went to close SSMS it warned me about the dialog (which I could not close/cancel), then gave me an ugly exception about protected memory.
After restarting SSMS it all worked again.

You need to make sure to follow all of the pre-install instructions as there are critical items that need to be followed for all SQL Server 2016 features to work correctly. You can see the list here.

https://msdn.microsoft.com/en-us/library/dn876712.aspx#bkmk_ga_instalpatch

You may not need to uninstall 2016 to get this patch to work, but your mileage may vary.


SQL server databases stuck in restoring state I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state!

Is it possible to stop the restoring process? and also, How can I make sure the database integrity has not been compromised?

This is likely caused by the restore script adding the WITH NORECOVERY parameter, to make the database ready for a transaction log apply after the restore.

The database is now waiting for the latest transaction log file.

You can either:

Apply the latest transaction log, using RESTORE LOG database_name FROM backup_device WITH RECOVERY; ... or
Restore the database again, but this time using ... WITH RECOVERY; ... or
Force the database out of restoring mode by executing: RESTORE DATABASE YourDb WITH RECOVERY;

Before you do this, please make sure you understand the implications of these options. You may cause data loss if you are not careful.

See this for details:

http://technet.microsoft.com/en-us/library/ms177446.aspx
https://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state

I just had this situation, and the cure was quite surprising:

ALTER DATABASE DBName SET ONLINE;

Apparently the NetBackup restore that broke left it in a weird state. No other solution worked (though I hadn't yet tried restarting the SQL Server service)

I'd be careful with the database, though, as theoretically once a restore starts, then fails, you could have corrupted data. I'm just going to restore the database again anyway, so it doesn't matter to me.


selecting where two columns are in a set This might be a silly question, and my suspicion is that I can't do this, but is there a construct in SQL that would allow me to do something like the following:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

I want to select data where two columns are in a set of pairs.

I'd like to avoid using a subquery, if possible.

Is there a construct in SQL that would allow me to do something like the following:

Yes, there is, almost exactly as you wrote it. Just put col1, col2 inside parentheses:

-- works in PostgreSQL, Oracle, MySQL, DB2, HSQLDB
SELECT whatever
FROM t                               --- you missed the FROM
WHERE (col1, col2)                    --- parentheses here
       IN ((val1a, val2a), (val1b, val2b), ...) ;

If you try it however in a DBMS, you may find that it doesn't work. Because not all DBMS have implemented all the features of the (evolving) SQL standard. This works in latest versions of Oracle, MySQL, Postgres, DB2 and HSQLDB (it was not well optimized in MySQL and not use indexes, so it should be avoided there unless they fixed it in 5.7).

See MySQL documentation about IN operator and Postgres documentation about Row constructors. The two* (or more) values in parentheses is called a row-constructor.

Other ways that express the same idea:

-- works in PostgreSQL, DB2
SELECT whatever
FROM t
WHERE (col1, col2)
       IN ( VALUES (val1a, val2a), (val1b, val2b), ...) ;

SELECT t.whatever
FROM t
  JOIN
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON (x.col1, x.col2) = (t.col1, t.col2) ;

Both work in Postgres and DB2 (afaik). The last one can be modified to work in SQL Server, too:

-- works in PostgreSQL, DB2, SQL Server
SELECT t.whatever
FROM t
  JOIN
    ( VALUES (val1a, val2a), (val1b, val2b), ...) AS x (col1, col2)
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

It can also be modified to work everywhere, by placing the values into a (temporary or permanent) table first:

-- works everywhere
CREATE TABLE values_x
( col1  ...,
  col2  ...) ;

-- use appropriate for the DBMS syntax here
INSERT INTO values_x (col1, col2)
VALUES (val1a, val2a), (val1b, val2b), ... ;

SELECT t.whatever
FROM t
  JOIN values_x  x
      ON  x.col1 = t.col1
      AND x.col2 = t.col2 ;

DROP TABLE values_x ;

And there is always the long way or converting the IN to a long expression with OR that should work everywhere:

-- works in all SQL DBMS
SELECT whatever
FROM t
WHERE col1 = val1a AND col2 = val2a
   OR col1 = val1b AND col2 = val2b
   ---
   ;

*: It can actually be just one value, with ROW(v), see Postgres docs.

SELECT *
FROM   dbo.Table1 A
WHERE  (CAST(Column1 AS VARCHAR(max)) + '-' + CAST(Column2 AS varchar(max)))
NOT IN (SELECT (CAST(Column1 AS VARCHAR(max))
                + '-'
                + CAST(Column2 AS varchar(max)))
        FROM Table2)


BUILTIN\Administrators accidently removed Hi, so recently when trying to fix another bug within my sql server 2008 R2 db I removed the BUILTIN\Administrators login from sysadmin. Now it will not let me change anything winthin the database. Also I do not have the SA account password to log in as that user to change/ add any permission.

Can anyone help me add the BUILTIN\Administrators login back into sysadmin or find a way to alter the database using my windows account?

Start the SQL Server service in single-user mode (by using the -m startup parameter).  When you do this, all members of the local administrators group will have sysadmin rights.  Provided you are a member of that group, you should be able to do whatever sysadmin actions you need.

Take a look at Database Engine Service Startup Options for documentation.

Edit:  To start SQL Server in Single-User Mode, please see this documentation.

Stop all SQL services, then go into SQL Server Configuration Manager and select SQL Server Services in the tree.  Then go into Properties for the database engine service of interest and select the Advanced tab. Click the dropdown arrow on the Startup Parameters value, and add ;-m (semicolon, dash, m) to the end of the text. Click OK to exit the dialog box. Restart the service for the SQL instance of interest.

If you have SQL Management Studio open, close it and then right click on the program and select run as administrator.  Fix the Security options you need, save and then close the program.  Go in SQL Server Configuration Manager and stop the SQL instance of interest and then go in and remove the ;-m from the Startup Parameters.  Restart the SQL instance of interest and start SQL Management Studio(without running as administrator) as the user or user of the group you made changes for and ensure the changes you made worked.  If so, restart all of the other services needed to bring the SQL server online.

Props to Jon Seigel and Glen Robson for this solution


How to Add A User To A Database In Standby / Read-Only Mode In Sql Server I have a database which is in standby / read-only mode in my reporting server. But, I can not add a new user to this database. How can i add a new user to this database?

Check if the database is part of logshipping. As the database in secondary server will be in Read-only/standby state. ALtering the database directly will not be allowed.

Identify the primary server and add the user there. This will move to your read-only secondary.

Edit : 1

Make sure the logins exist in both the servers.

Use bellow commands:

ALTER DATABASE database_name ONLINE

Or try this will also works.

RESTORE DATABASE  WITH RECOVERY


MySQL 1264 warning from libmysql I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader).

I managed to connect and things are going almost fine.  I use mysql_real_connect and mysql_errno for connecting and capturing possible errors.  I'm using the InnoDB engine and STRICT_TRANS_TABLES in SQL mode.

Questions / Doubts:

I thought mysql_errno only captures errors and not warnings, but I'm getting a 1264 MySQL warning in metatrader (1264 SQLSTATE: 22003 (ER_WARN_DATA_OUT_OF_RANGE)), however it doesn't say where. How can I resolve this?
If I try to manually UPDATE or INSERT against my database I get no warnings and/or errors.

I would like to know if this is a libmysql bug or find out what's going on.  Perhaps there is a specific table/field responsible?

Any pointers?

check my.inior my.cnf if its contain sql-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" if so you need to change it to sql-mode = "NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" then restart the SID service.
Hope that will fix the issue for you

I think there is a stored procedure which is defining the range, and, when triggering, is triggering is a not corret way, for example like an error and not like a normal warning. Is it possible ?


How to convert Rows to Columns I've a table Columns

and a second table Response in which all data is saved.

Now I want to create a SQL View in which the result should be like this

I tried using pivot

select UserId ,FromDate, ToDate, Project, Comment
from
(
  select R.UserId ,R.Text , C.ColumnName
  from [Columns] C
  INNER JOIN Response R ON C.Id=R.ColumnId
) d
pivot
(
  max(Text)
  for ColumnName in (FromDate, ToDate, Project, Comment)
) piv;

but that didn't worked for me, I also referred this https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server but was not able to implement it. Any ideas how to achieve the same in SQL View?

Scripts for Tables:

CREATE TABLE [dbo].[Columns](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](1000) NULL,
    [IsActive] [bit] NULL,
 CONSTRAINT [PK_Columns] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into [Columns] values('FromDate',1)
insert into [Columns] values('ToDate',1)
insert into [Columns] values('Project',1)
insert into [Columns] values('Comment',1)

CREATE TABLE [dbo].[Response](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [bigint]  NOT NULL,
    [ColumnId] [bigint]  NOT NULL,
    [Text] [nvarchar](max) NULL,
    [IsActive] [bit] NULL,
    CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into [Response] values(1,1,'1/1/2012',1)
insert into [Response] values(1,2,'1/2/2012',1)
insert into [Response] values(1,3,'p1',1)
insert into [Response] values(1,4,'c1',1)
insert into [Response] values(2,1,'1/1/2013',1)
insert into [Response] values(2,2,'1/2/2013',1)
insert into [Response] values(2,3,'p2',1)
insert into [Response] values(2,4,'c2',1)
insert into [Response] values(2,1,'1/1/2014',1)
insert into [Response] values(2,2,'1/2/2014',1)
insert into [Response] values(2,3,'p3',1)
insert into [Response] values(2,4,'c3',1)
insert into [Response] values(3,1,'1/1/2015',1)
insert into [Response] values(3,2,'1/2/2015',1)
insert into [Response] values(3,3,'p4',1)
insert into [Response] values(3,4,'c4',1)

You are not explaining why your PIVOT query did not work for you, although it is not too hard to guess if your Response sample is representative enough of the data in that table in general. You have two sets of responses for User 2 but your query picks up only one.

As your expected output shows you want the query to return both sets, you need to teach it to distinguish between various sets of answers by the same user. One way is to use the ROW_NUMBER analytic function:

SELECT
  UserId,
  FromDate,
  ToDate,
  Project,
  Comment
FROM
  (
    SELECT
      r.UserId,
      r.Text,
      c.ColumnName,
      SetNo = ROW_NUMBER() OVER (PARTITION BY r.UserId, r.ColumnId ORDER BY r.Id)
    FROM
      dbo.Columns AS c
      INNER JOIN dbo.Response AS r ON c.Id = r.ColumnId
  ) AS derived
  PIVOT
  (
    MAX(Text)
    FOR ColumnName IN (FromDate, ToDate, Project, Comment)
  ) AS p
;

The query above makes a few assumptions:

every set of answers consists of the same set of ColumnIds;
related responses are arranged together (based on the order of Id), or, at least, if one FromDate response comes later than another FromDate response, then its related ToDate response also comes later than the ToDate response related to the previous FromDate.

If there are special cases in the real data (e.g. some sets may be incomplete), a different approach may be required.

I've done something similar in MySQL but I'm sure you could make some changes to make it work for your need.
Take a look at
http://knowittoall.blogspot.com/2011/09/converting-data-attribute-to-table.html
and
http://knowittoall.blogspot.com/2011/09/converting-data-attribute-to-table_18.html


Rebuilding Indexes, DB now 10x the size I have a SQL Server database (2008 R2 SP1) that was about 15 gigs. It turns out maintenance hadn't been running in a while, so I created a maintenance plan to rebuild all the indexes, they were very fragmented.

The job finished and the fragmentation is gone, but now the database is over 120 gigs! I understand that it would have used extra space to do all the rebuilds, but now that the job is done, I would think all that space would be free space, but the free space only shows as 3 gigs, so 117 gigs is being used even though the index rebuild job is finished.

I'm very confused and could use some guidance, I have the get the db back to a reasonable size, we don't have the disk space for this.

Thanks in advance!

Here's the results of both the queries posted:

  log_reuse_wait_desc NOTHING

name    TotalSpaceInMB  UsedSpaceInMB   FreeSpaceInMB
LIVE_Data   152             123             28
LIVE_Log    18939           89              18849
LIVE_1_Data 114977          111289          3688

The 3rd file is an .ndf file, that's the one showing only 3688 in unused space, but 111289 in used for about 15 gigs of data.

In the meantime I just figured this out, total brain burp. I had indicated what I thought was a fill factor of 90 in the rebuild job, but it's worded as "change free space percentage to" so by using a value of 90 in there, I was actually using a fill factor of 10!! DOH. No wonder it grew 10x as big. I'm going to rebuild with the correct fill factor then shrink. Thanks to everyone for the input though.

Without any other details I suspect you need to do a transaction log backup before you can reclaim its space.

See what select name, log_reuse_wait_desc from sys.databases tells you.  If the log_reuse_wait_desc column says LOG_BACKUP then it can't reclaim space until you've done a backup of the tran log.


Get Primary records where N most recent Secondary records match condition I want a list of Authors (primary records) whose 2 most recent Books (secondary records) are best sellers.

INSERT INTO Authors
    (`author_id`, `author_name`)
VALUES
    (41, 'Heida Palffrey'),
    (42, 'Michael Grist'),
     ...

INSERT INTO Books
    (`book_id`, `author_id`, `book_title`, `published_date`, `best_seller`)
VALUES
(156, 41, 'Red Heat',      '2007-01-21', 'false'),
(161, 41, 'Far From Home', '2003-12-27', 'true'),
(859, 41, 'Storm Warning', '1992-08-19', 'true'),
(914, 41, 'Dead Heat',     '1981-01-04', 'false'),

(780, 42, 'The Last',      '2017-01-31', 'true'),
(198, 42, 'Mr Ruin',       '2016-01-18', 'true'),
(166, 42, 'Mr. Troop Mom', '1982-06-27', 'false'),
(796, 42, 'Ben Bootin',    '1972-12-01', 'false'),

    ...

For the above data, I just want author "Michael Grist" whose two most recent books were best sellers.

I have been able to get authors with consecutive best sellers, but not authors whose most recent two books were best sellers.

SELECT t.author_id, a.author_name, t.consecutive, t.best_seller, COUNT(1) consecutive_count
FROM (
  SELECT b.* ,
    @r:= CASE WHEN @g = b.`best_seller` THEN @r ELSE @r + 1 END consecutive,
    @g:= b.`best_seller` g
  FROM books b
  CROSS JOIN (SELECT @g:='true', @r:=0) t1
  ORDER BY author_id, published_date
) t
JOIN `authors` `a` ON (`a`.author_id = t.author_id)
GROUP BY t.author_id,t.consecutive,t.`best_seller`
HAVING consecutive_count >= 2 AND  t.`best_seller` = 'true';

For the data in the sqlfiddle below, only Chaddie Dreakin and Michael Grist fit the requirements of two most recent best sellers.

http://sqlfiddle.com/#!9/8808649/8

SET @@group_concat_max_len = 9;

SELECT  a.author_name
    FROM
    (
        SELECT  author_id,
                GROUP_CONCAT(best_seller ORDER BY  published_date DESC) AS bs
            FROM  Books
            GROUP BY  author_id
            HAVING  bs = 'true,true'
    ) AS x
    JOIN  Authors AS a USING(author_id) ;

Notes:

9 is based on true,true being 9 characters, and that is enough to catch the first two.
I'm assuming best_seller is a VARCHAR.
ORDER BY  published_date DESC -- the "latest"
HAVING -- can't use WHERE in this situation

select author_name from
(
select * from
(                                      -- emulate row_number()
select ta.*,
if(@typex=ta.author_id,
@rownum:=@rownum+1,
@rownum:=1+least(0,@typex:=ta.author_id)) rown
from Books ta, (select @rownum:=1, @typex:='_') zz
order by author_id, published_date DESC
) yy
where rown < 3                          -- get first 2 records
) zz join authors using(author_id)
group by author_id
having sum(best_seller = 'true') = 2    -- check both are best sellers
;

fiddle


Unnest multiple arrays into rows I have been shown a great way to bulkinsert with this example:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a)
SELECT p.id, a
FROM   p, unnest($2::text[]) AS a

However, I need to insert multiple rows from multiple arrays, so I tried this syntax:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a, column_b)
SELECT p.id, a, b
FROM   p, unnest($2::text[]) AS a, unnest($3::bigint[]) AS b

I have a primary key on parent_table_id and column_a, and when I try to execute this query, Postgres complains of a duplicate key violation.

How should the arrays be unwrapped so that they form individual rows?

In other words, if $2 and $3 both have two entries, how can the first entry of $2 only be inserted with the first entry of $3 and the same for the respective second entries?

If this is not possible, can I construct a multidimensional array?  If so, how should it be passed with multiple array types, and what is the multidimensional array syntax?

This would do what you desire:

WITH p AS (
    INSERT INTO parent_table (column_1)
    SELECT $1
    RETURNING id)
INSERT INTO child_table (parent_table_id, column_a, column_b)
SELECT p.id, t.a, t.b
FROM   p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t

The subtle difference here is that unnest() calls in the same SELECT list are unnested in parallel if the number of elements is identical. Careful though: In Postgres 9.6 or older, if the number is not the same, it results in a Cartesian product instead. The behavior was sanitized in Postgres 10. See:

What is the expected behaviour for multiple set-returning functions in select clause?
Is there something like a zip() function in PostgreSQL that combines two arrays?

You could use a cleaner form with generate_subscripts() or other techniques, but those would be much more verbose. Details in this related question:

How to preserve the original order of elements in an unnested array?

Postgres 9.4

The new WITH ORDINALITY in Postgres 9.4 allows a much cleaner (and only moderately more verbose) form for this:

WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, ta.a, tb.b
FROM   p
     , unnest($2::text[]) WITH ORDINALITY AS ta(a, rn)
JOIN   unnest($3::bigint[]) WITH ORDINALITY AS tb(b, rn) USING (rn);

And this special case can be even simpler with the new form of unnest() that accepts multiple arrays:

WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, t.a, t.b
FROM   p, unnest($2::text[], $3::bigint[]) AS t(a, b);

Example in this related answer.

I solved with:

INSERT INTO foo(Val,i,j)
SELECT myArr[i][j] ,i,j
FROM Generate_Series(1,lbound) i
JOIN Generate_Series(1,ubound) j
    ON true


Error on import of mysqldump file - Illegal double value found during parsing I am currently getting the following error while trying to import / restore a mysqldump file:

ERROR 1367 (22007) at line 445: Illegal double '1.79769313486232e+308'
value found during parsing

I looked at various places online and could not find anything that was similar.

Some background:

I am trying to create a new replication box and the mysqldump is new from another replica box. Both boxes are:

  CentOS 5.7 with MySQL 5.0.77-4

Used the following to create the mysqldump:

$ mysqldump --lock-all-tables --all-databases | gzip -9 > mysqldump.sql.gz

The new replication box has nothing on there right now and all the databases / tables are supposed to be created by mysqldump. Any insights?

Thanks!

There is current open bug report on this one.

The bug report has a suggested work around at the bottom entry [22 Jan 2010 6:46]:

replace 1.79769313486232e+308 \'1.79769313486232e+308\' -- filename

Give it a Try !!!


CPU clock speed versus CPU core count - higher GHz, or more cores for SQL Server? We are beginning to provision a set of physical servers for a virtual cluster of SQL Server 2016 nodes within VMware. We will be utilizing Enterprise Edition licenses.

We plan on setting up 6 nodes, but there is a bit of a debate on what the ideal way to provision the physical servers with regards to CPU clock speed versus CPU core count.

I know this is largely dependent on transaction volume and number of databases stored among other software-specific factors, but is there a general rule of thumb that is advised?

For instance, is a dual 8-core, 3.2 GHz physical server (16 cores) more preferential to a dual 16-core, 2.6 GHz server (32 cores)?

Has anyone come across a white paper that further delves into this type of topic?

The general rule of thumb is keep the core count as low as possible, and the processor speed as high as possible.  The licensing math on that proves the point at ~$7,500 USD per core for Expensive Edition.

Buying the correct hardware can pay for itself in reduced licensing costs. See Processor Selection for SQL Server by Glenn Berry. It's a great resource on how to choose a processor for SQL Server.

Once you take into consideration SQL Server's per-core licensing structure, it makes sense to always go with the fastest processor speed available, regardless of workload type, whether OLTP or analytics.  Having the fastest possible core speed is never going to be a problem.  Increase the core count as required, but never do that by reducing core speed.

In other words, don't think of 16 x 2.2Ghz processors being the same as 8 x 4.5Ghz processors.  The cost savings of using the 2.2Ghz processors over the 4.5Ghz processors is likely to be a maximum of about $10,000 USD (for a typical Xeon-based two processor machine).  Jumping from 8 cores to 16 cores with SQL Server Enterprise Edition is likely to cost over $60,000 USD in licensing fees.  In other words, you might save $10,000 in hardware costs, but you'll lose an extra $50,000 in licensing.

If you decide you need a lot of parallel processing muscle, and decide you need 32 cores for the task at hand, going with the fastest cores will pay dividends in reduced processing time.  Nobody will fault you for that.

Having said all that, if the choice is one CPU or more than one CPU, always go with more than one.  Running SQL Server (or any DBMS) on a single CPU can cause all kinds of problems since the capability for concurrent operations is vastly limited.

The answer comes down to this: it depends on your use case.

Are you processing multiple small requests at once or a few large requests?
Is the program you run even optimized for multi-core?

For example, I have a quad core computer but the ESP8266 compiler only uses 25% of my CPU because it's only designed to use one core. If I had 1 fast core then that would be more optimal.


Converting old SQL 2000 Non-Ansi code to SQL 2012 compatible We have a legacy application that is only used for a read only reference.

It is using SQL Server 2000.
We want to migrate its database to SQL version 2012, however we can't upgrade the software and the vendor doesn't exist anymore.

The problem is that the views/stored procedures on the database are using old NON-ANSI SQL code that isn't supported anymore.

When we tried to simply restore it on SQL 2008R2 (as there isn't a direct restore from 2000 to 2012), and increase the compatibility level to 100, we got validation errors on the views/stored procedures, as they're using old join syntax.

  The query uses non-ANSI outer join operators ("=" or "="). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

As there are ~200 objects that need to be updated with a newer and proper join syntax code, I was wondering whether a tool/script/code-generator exists somewhere that will help me convert the code.

I did notice that if I right click the view object and select Design, the query of the view IS converted to the proper syntax.
But that isn't automated enough for me.

Any thoughts?

The best match to your requirement that I can think of is the Query Designer in SSMS.

You can open the designer by right-clicking any table in your database and selecting "edit top 200 rows".

Then, open the SQL pane by clicking the button in the toolbar:

Now you can paste your statement in the SQL pane and use the syntax checker to convert the code:

The converted code goes like this:

As you may have noticed, many things are wrong here:

all formatting is gone
wildcards are expanded. This may actually be a good thing.
Unnamed expressions are named with wonderful random names such as "Expr1".
The syntax checker may rewrite your query in ways that don't match the initial code (see this connect item, for instance)

This method (and any other automated method) is not 100% bulletproof and can be used as a starting point to help you in the process, but it definitely needs manual intervention and extensive tests on the converted code.

I had a similar problem, but I managed to fix it using the SSMS inbuilt Query Designer Tool.
From my answer to a related question on Stack Overflow:

It simply refactored all my queries and put in the correct Join, Either Left or Right, and the Where condition as an AND condition on the Join itself, so I was getting the correct data result set for both pre and post, only sometimes the data sorting/ordering was a little off.
Still a little bit unsure though why the ordering/sorting was a little off if the Join condition was the same and the filters as well, because data was a 100 % match.

To get the query Designer to Work , just select your legacy SQL, and open the Query Designer by pressing Ctrl + Shift + Q or Goto Main Menu ToolBar => Query => Design Query in Editor.

That's it. You will get the converted query with the new Joins that you can copy and test. Worked 100% of the time for me, except in some cases where the sorting was not matching, which you can check by adding a simple order by clause to both pre and post to compare the data.
For reference, I cross checked with Using the Query Designer to convert non-ANSI joins to ANSI by John Paul Cook.


postgresql multi column unique or check constraint First a word of pleading.  To say I am a newbie to postgresql would be an insult to the newbies!  I have been working on this for only a day or so.

I have found several questions on this site, and found references in the postgres 9.1 documentation which allude to this issue, but I am new enough to this that my understanding of the various complex examples is insufficient for me to grasp the concept.  At least I think that is the problem...

I have the following table:

CREATE TABLE cc
(
 idx integer NOT NULL DEFAULT nextval('cc_idex_seq'::regclass),
 card_num character(16), -- credit card number
 sec character(4), -- credit card security number from reverse
 card_holder_fname character(20), -- first name of card holder
 card_holder_lname character(20), -- last name of card holder
 card_holder_mi character(1), -- middle initial (if given)
 card_type character(1), -- Visa, Master Card, or Discover
 CONSTRAINT chk_cc_card_type CHECK
  (card_type = ANY (ARRAY['V'::bpchar,'M'::bpchar, 'D'::bpchar]))
)

In an effort to help us combat fraud, we want to ensure that the combination of data for a given table row is the only way that the card in question is ever entered.  Since a given card can wind up in the table more than once, but should never do so with different card holder data, multiple entries need to be allowed IF THEY ARE EXACTLY THE SAME!  But if a card number appears more than once, with different holder data, it should raise an error.

(Note:  This isn't the full table, but it is enough to demonstrate the issue with, I hope...)

I am as yet unable to find a way to do this with sql, and am new enough to postgresql that the functions and triggers elude me.

Any help would be wonderful.

Edit:  Per ypercube's suggestion I am ading what I should have said in the beginning.

1) The information is being entered from an employee operated form (web based script of some kind, I will look into this further)

2) The table / constraint should refuse to allow the insert if the conditions are detected.  Since a new entry would be the entire transaction involving suspect information the entire transaction should be refused.  Obviously the script engine adding the data will have to be modified as well, but I should be able to handle that part.  Writing the data to another table as suspect would be allowable since no actual financial transaction would have been allowed.

3) This data is stored in no other table (this was my initial idea to place it in another table and use a foreign key lookup on the card number + data and reject it if not the same...I am pushing the boss to do this but he has to confer with some one about legality...apparently there is an issue of having it in more than one location)

4) And just for the record, the boss knows he needs a pro to do this, I am simply a stop gap!  (Hopefully a short stop for a small gap!)

ALTER TABLE  table_name ADD CONSTRAINT  constraint_name CHECK (
   column_price > 0
   AND column_discount >= 0
   AND column_price > discount
);


Indexing a SHA hash in place of a VARCHAR In Is it a good idea/approach to index a VARCHAR column? the concept was using VARCHAR as the column.

I was wondering, if we store the SHA-1 hash or SHA-256 hash (if I am paranoid) as a BINARY(20) column and index on that column.

Performing a SHA-1 computation on a short string is fast enough on the application side and we just query by the SHA value instead.

Likely I am thinking the length of the VARCHAR would hover around 10 to 30 characters with some going longer but lower probability.

This question has the [Performance] tag so I suspect that you may be thinking of a hash index. In SQL Server, the maximum key length for nonclustered indexes is 1700 bytes. It is not possible to create a nonclustered index with a long string column as a key column. For example, for the following table:

DROP TABLE IF EXISTS #HASH_INDEX_DEMO;

CREATE TABLE #HASH_INDEX_DEMO (
    ID BIGINT NOT NULL,
    BIG_COLUMN_FOR_U VARCHAR(8000) NOT NULL,
    SMALL_COLUMN VARCHAR(10) NOT NULL
);

INSERT INTO #HASH_INDEX_DEMO WITH (TABLOCK)
SELECT RN, REPLICATE(CHAR(65 + RN % 26), (RN % 43) * (RN % 119)), 'SMALL'
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

Attempting to create this index:

CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U);

Fails with this error:

  Msg 1946, Level 16, State 3, Line 19 Operation failed. The index entry
  of length 1701 bytes for the index 'I' exceeds the maximum length of
  1700 bytes for nonclustered indexes.

If you need to do an equality search on that column then the query optimizer must do a table scan. For example, the following query takes about 0.6 seconds on my machine:

SELECT ID, SMALL_COLUMN
FROM #HASH_INDEX_DEMO
WHERE BIG_COLUMN_FOR_U = 'A'
OPTION (MAXDOP 1);

One alternative is to create a hash index on the column and to perform the equality search on both the hash index and the column itself. CHECKSUM() is probably the best choice because you don't need any cryptographic security for this and a small number of collisions is acceptable. You primary want something small and fast. The code below adds a computed column and creates an index on that column:

ALTER TABLE #HASH_INDEX_DEMO ADD BIG_COLUMN_FOR_U_CHECKSUM AS CHECKSUM(BIG_COLUMN_FOR_U);

CREATE INDEX I ON #HASH_INDEX_DEMO (BIG_COLUMN_FOR_U_CHECKSUM);

The query below returns the same results as the initial query but SQL Server is able to use the index. It finishes in 0.01 seconds on my machine.

SELECT ID, SMALL_COLUMN
FROM #HASH_INDEX_DEMO
WHERE BIG_COLUMN_FOR_U_CHECKSUM = CHECKSUM('A') AND BIG_COLUMN_FOR_U = 'A'
OPTION (MAXDOP 1);

Hash indexes are a good choice when the key length is too long to allow for a nonclustered index or disk space is at an absolute premium. In your question you estimate that the length of the column will be about 10 to 30 characters to the added complexity probably isn't worth it for your scenario.

I can think of one additional benefit but it is mostly due to limitations of JPA, Spring and MySQL.

MySQL defaults to case insensitive unless you use MySQL specific constructs like BINARY in columnDefinition or utf8mb4_bin thus causing portability / sorting issues.  In which case the workaround was to create an index column containing the SHA-1 which will not be translated by JPA/Spring/Hibernate/MySQL.


Does one of the normal forms require separation of repeating clusters of values into another table? Should the repeating values in the "Sales" table below be separated out into a second table as part of the process of database normalization? Note that none of the values are functionally dependent on each other; those values that are functionally dependent are already normalized out to other tables.  I'm just not sure if there's any benefit to separating the "clusters" of values in different domains out into a separate table since the table contains tens of millions of rows, but only a few thousand distinct combinations of these values:

LOCATION     PRODUCT         RECIPIENT     DATE       AMOUNT
New York     Cheesecake      Bill          1/1/2000   $25.00
New York     Cheesecake      Bill          2/1/2000   $42.00
New York     Cheesecake      Bill          3/1/2000   $17.00
Dallas       Cheesecake      Bill          4/1/2000   $15.00
Dallas       Cheesecake      Bill          5/1/2000   $17.00
Dallas       Cherry Pie      Bill          6/1/2000   $14.00
Dallas       Cherry Pie      Bill          7/1/2000   $13.00
Dallas       Cherry Pie      Sam           8/1/2000   $16.00

The table would be separated into a "Sale Types" table:

SALETYPE     LOCATION    PRODUCT       RECIPIENT
1            New York    Cheesecake    Bill
2            Dallas      Cheesecake    Bill
3            Dallas      Cherry Pie    Bill
4            Dallas      Cherry Pie    Sam

So that the original "Sales" table would look like this:

SALETYPE     DATE        AMOUNT
1            1/1/2000    $25.00
1            2/1/2000    $42.00
1            3/1/2000    $17.00
2            4/1/2000    $15.00
2            5/1/2000    $17.00
3            6/1/2000    $14.00
3            7/1/2000    $13.00
4            8/1/2000    $16.00

Since each unique combination of values appears approximately 1,000 times each on average, I believe that would save storage and potentially memory pressure, but my understanding of the underlying RDBMS memory optimizations is admittedly pretty limited.  Would that be considered database normalization though?

There is no normal form which would have you move those columns to a separate table. Normalization is a process in the logical design of tables that discusses functional dependency between a table's columns. If there is no un-resolved functional dependency there is no more normalization to perform.
Further, a logical table represents a class of objects, events or ideas from our business domain. There is a real-life thing that the table holds data for. What is that thing that your proposed "Sales Types" table would hold? Likely your business users would shrug at this question.
That said, during the physical implementation (distinct from the logical design before) you may choose to move columns between tables for reasons other than functional dependency. This can be confusing because the end results look the same - columns moved to new tables. The difference is why we move these columns. This will be important later when change happens. We need to know why we did someting so we will know why we may choose to undo it. We undo normalization if the business rules change. We undo physical implementation if the desired performance profile changes.
In the context of physical implementation your proposed table looks like a dictionary encoding scheme. There is nothing wrong with doing this. If your application benefits overall from this extra development work then go fot it.
Many DBMS offer compression natively. Column-oriented storage is particulary effective at this if your product offers this choice. By using native features you gain the performance benefits you seek without taking on the development and maintenance cost. I'd recommend you stress-test any native feature to see if it can deliver your desired performance.

Welcome.

If you notice, after your creation of "Sales Types", there are too many duplicate values within. So,...one essential improvement is

Let the Sales table have

LocationId ProductId RecipientId Date Amount

Location table

LocationId Location

Product table

ProductId Product

Recipient table

RecipientId Recipient

Other than this, instead of sticking to normalization theory strictly, you may have to consider your requirements too.


All My Databases Are Gone? Today I changed my username and password in MySQL and I logged into phpmyadmin to see that all my databases were gone.

I've tried changing the datapath but then I realized I didn't see any of my databases in the data folder either!

Can I get back my databases? Are they stored somewhere else?

Data is present in data folder of MySQL which is usually in ProgramData for windows installations. look into "C:\ProgramData\MySQL\MySQL Server 5.6"..as programdata usually have the files on by default

All the data used to be in default location of pc in fact wherever we are located doesn't matter so the default location is Ubuntu 9.10

Moving the database from /var/lib/mysql to /data/databases/mysql you can go and check your data there. It will be stored there and sometimes data may be hidden we can unlock it for seeing as a guest. If we are admin then we can see easily and if we are some unknown user for the system then we have to get some privileges for seeing the data.


Freeing Unused Space SQL Server Table I have a table in SQL Server 2012 Express with a lot of unused space.

I need to free up space in the database.

|        NAME |   ROWS |     RESERVED |         DATA | INDEX_SIZE |       UNUSED |
|-------------|--------|--------------|--------------|------------|--------------|
| MyTableName | 158890 |  8928296 KB  |  5760944 KB  |   2248 KB  |  3165104 KB  |

How do I get SQL to release the 3165104KB?

I've already tried:

Alter table MyTableName Rebuild
DBCC CLEANTABLE (MyDbName,"MyTableName ", 0)
ALTER INDEX ALL ON MyTableName REORGANIZE ;
ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF)

Here is the table:

CREATE TABLE [dbo].[MyTableName](
    [ImageID] [int] IDENTITY(1,1) NOT NULL,
    [DateScan] [datetime] NULL,
    [ScanImage] [image] NULL,
 CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED
(
    [ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The only thing we have done is replaced ScanImage on every row with a much smaller image (this is how so much unused space is there).

The only thing we have done is replaced ScanImage on every row with a
  much smaller image (this is how so much unused space is there)

From doing some experimentation the most space effective method would be to drop the allocation unit and repopulate it (if you have a maintenance window to do this in).

Example code that achieved the best space reduction for me with the table structure in the question is:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

BEGIN TRAN

SELECT [ImageID],
       [ScanImage]
INTO   #Temp
FROM   [dbo].[MyTableName]

ALTER TABLE [dbo].[MyTableName]
  DROP COLUMN [ScanImage]

/*Allocation unit not removed until after this*/
ALTER INDEX PK_Image ON MyTableName REBUILD

ALTER TABLE [dbo].[MyTableName]
  ADD [ScanImage] IMAGE NULL

UPDATE [dbo].[MyTableName]
SET    [ScanImage] = T.[ScanImage]
FROM   [dbo].[MyTableName] M
       JOIN #Temp T
         ON M.ImageID = T.[ImageID]

DROP TABLE #Temp

COMMIT

Everything is in a transaction so if the machine crashes it will be rolled back. Could probably do with some error handling or at least SET XACT_ABORT ON. I used SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; to prevent any concurrent modifications from happening during or after the copy and being lost.

The number of LOB pages reserved after reducing the size of an image in all rows was as follows:

+--------------------------------------------------+---------------------+-------------------------+
|                      Event                       | lob_used_page_count | lob_reserved_page_count |
+--------------------------------------------------+---------------------+-------------------------+
| Inserted 10,000 rows with 100,000 byte data each |              135005 |                  135017 |
| Updated all rows to 10,000 byte image data       |               31251 |                  135012 |
| Reorganize                                       |               23687 |                   25629 |
| Drop and re-add image data                       |               13485 |                   13489 |
+--------------------------------------------------+---------------------+-------------------------+

Reorganize the clustered index - that one has the data at the nodes, so.... it is likely fragmented.


Create index if it does not exist I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?

This is a similar issue to the column creation one that is solved with this code:
https://stackoverflow.com/a/12603892/368511

Index names in PostgreSQL

Index names are unique across a single database schema.
Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema.
Two tables in the same schema cannot have an index of the same name. (Follows logically.)

If you do not care about the name of the index, have Postgres auto-name it:
CREATE INDEX ON tbl1 (col1);

is (almost) the same as:
CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);

Except that Postgres will avoid a naming collisions and automatically pick the next free name:
tbl1_col1_idx
tbl1_col1_idx2
tbl1_col1_idx3
...

Just try it. But, obviously, you would not want to create multiple redundant indexes. So it wouldn't be a good idea to just blindly create a new one.
Test for existence
Postgres 9.5 or newer
Now available:
CREATE INDEX IF NOT EXISTS ...
Also works for CREATE INDEX CONCURRENTLY IF NOT EXISTS.
However, the manual warns:

Note that there is no guarantee that the existing index is anything
like the one that would have been created.

It's a plain check for the object name. (Applies to variants for older versions below, too.)
To find existing indexes on the same table for the same column(s):
SELECT pg_get_indexdef(indexrelid)
FROM   pg_index
WHERE  indrelid = 'public.big'::regclass
AND   (indkey::int2[])[:] = ARRAY (
   SELECT attnum
   FROM   unnest('{usr_id, created_at}'::text[]) WITH ORDINALITY i(attname, ord)
   JOIN  (
      SELECT attname, attnum
      FROM   pg_attribute
      WHERE  attrelid = 'public.big'::regclass
      ) a USING (attname)
   ORDER BY ord
   );

Restrictions:

Only works for columns, not other index expressions.
Also reports partial indexes (with WHERE clause) and covering indexes (with INCLUDE clause).
Reports any type of index, not just B-tree indexes.

Study the results (if any) before proceeding, or refine the query to your needs ...
Further reading:

Find tables with multiple indexes on same column
Normalize array subscripts for 1-dimensional array so they start with 1

Postgres 9.4
You can use the new function to_regclass() to check without throwing an exception:
DO
$$
BEGIN
   IF to_regclass('myschema.mytable_mycolumn_idx') IS NULL THEN
      CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
   END IF;

END
$$;

Returns NULL if an index (or another object) of that name does not exist. See:

How to check if a table exists in a given schema

This doesn't work for CREATE INDEX CONCURRENTLY, since that variant cannot be wrapped in an outer transaction. See comment by @Gregory below.
Postgres 9.3 or older
Cast the schema-qualified name to regclass:
SELECT 'myschema.myname'::regclass;

If it throws an exception, the name is free.
Or, to test the same without throwing an exception, use a DO statement:
DO
$$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relname = 'mytable_mycolumn_idx'
      AND    n.nspname = 'myschema'
   ) THEN

        CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
    END IF;
END
$$;

The DO statement was introduced with Postgres 9.0. In earlier versions you have to create a function to do the same.
Details about pg_class in the manual.
Basics about indexes in the manual.

It will be available in 9.5.
Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947

Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html


Tools to monitor Oracle Database Performance on Standard Edition Here is what I am looking for:

Works on Standard Edition i.e. No Diagnostic Pack.
Provides both current and historical performance data.
Good visuals for easy spotting of trends and presentation to management.

If you have used the tool, I would be interested in pros and cons you have discovered.  I can come up with a list, but the insight of actually having used the tool is worth far more.

Note: I've never used this, so please edit this answer if you have.

Lab128

  Lab128 provides additional savings by implementing ASH functions and
  not requiring the purchase of ODP.

Pros:

Response Time Analysis
RAC support
Agentless
Only $299 for a named user connecting to two instances and only $499 for a named user connecting to an unlimited number of instances.

Cons:

Interface isn't very polished.
Lack of alerts.
One writer/maintainer.
No web interface.
Not server based.

dbTrends for Oracle STATSPACK provides cost-effective database monitoring and tuning solution for companies which do not have very expensive Oracle tuning and diagnostic packs licenses required to access AWR. dbTrends is based on free STATSPACK engine that does not require additional licensing. dbTrends provides much more insights into AWR and STATSPACK performance data comparing with standard Oracle Enterprise Manager or third party vendors and allows to quickly identify performance trends and issues by comparing "good" and "bad" intervals of database operation. dbTrends is an ideal software tool for Oracle consultaning companies which manage remote customers' databases. dbTrends provides unique functionality to compare and analyze standard text performance reports generated by Oracle AWR and STATSPACK tools.


SQL Server Collation for Arabic, Hebrew, English and French I have new SQL Server database which need to have support in 3 different languages.

What character sets and collations do I need to choose if I want my database to support the following:

Hebrew
Arabic
English
French

Language support comes from datatype: use nvarchar/nchar (which stored unicode basically)
Collation is sorting + comparing + non-unicode code page

So, one column can store all languages, but it can have only one collation. You can "add" extra collations using computed columns, or coerce it during a sort/compare using the COLLATE clause:

Example

ORDER BY
    LatinCollationCol COLLATE Arabic_CI_AI_KS = N'عربي/عربى';

You should use UTF8

On this link you will see hebrew, arabic, and french language encoded with UTF8 :
http://www.unics.uni-hannover.de/nhtcapri/multilingual1.html


azure sql failover duration I  have a azure sql database in North Europe Region. It is almost 5 MB and it has Basic Pricing Tier. That database is not in Azure VM. I am using PaaS environment.
I configured Geo-Replication between North Europe and West Europe. When I tried to failover from West Europe to North Europe, it took 15 minutes. It is unacceptable, 15 minutes for 5 MB. Do you have any idea to reduce failover time?

Thank you

As Igor Pagliai(MSFT) Blog Here of July 3, 2014 has as very well mention about
Deep Dive: SQL Server AlwaysOn Availability Groups and Cross-Region Virtual Networks in Azure.

SQL Server AlwaysOn Availability Groups Supported between Microsoft
Azure Regions Here
VNet-to-VNet: Connecting Virtual Networks in Azure across Different
    Regions Here
Configure AlwaysOn Availability Groups in Azure VM (GUI) Here
Configure an ILB listener for AlwaysOn Availability Groups in Azure Here

As per Igor Pagliai , Azure Virtual Network Bandwidth and Latency is main factor in failover scenario.

This is always my primary concern, even on-premise, When I have to build a multi-site AlwaysOn consideration: the connectivity between different datacenters will provide enough network bandwidth and low latency? Remember that we are in Azure and the connectivity between datacenters is provided by connecting two (at least) Virtual Networks using a VPN tunnel that comes with capacity limits, then let me recap some basic considerations:

Today, for each Virtual Network, you can have only one VPN Gateway;
Each VPN Gateway is able to provide, at the very maximum, a
theoretical limit of 100Mbits/sec;
If you connect a VPN Gateway to    multiple Virtual Networks,
including on-premise local networks, each    channel will share the
same theoretical bandwidth limit of    100Mbits/sec;
VPN Gateway is backed up by two “hidden” Azure VMs of a    certain
size in active-passive configuration, able to provide 99,90%
availability SLA:

Cloud Services, Virtual Machines and Virtual Network SLA
Here

ExpressRoute or Virtual Network VPN – What’s right for me?
Here

NOTE: In this blog post, I will not cover ExpressRoute, I didn’t test for AlwaysOn AG scenarios.


Difference between functional dependency and join dependency By definition, a functional dependency is defined as

  Let X and Y be subsets of attributes of a relation R. A functional dependency X → Y holds over R if and only if for any instance r of R,
  whenever two tuples t1 and t2 of r agree on the attributes X, they
  also agree on the attributes Y. That is,

  t1.X = t2.X ⇒ t1.Y = t2.Y

A join dependency, meanwhile,

  A table T is subject to a join dependency if T can always be recreated by joining multiple tables each having a subset of the attributes of T.

I could be wrong, but I was wondering if there are any relation between the concept of functional dependency and join dependency.

Join Dependencies can be considered a generalization of Multivalued Dependencies, following the fact that a Multivalued Dependency X →→ Y in a relation R can be seen as another way of writing a binary Join Dependency ⨝[XY, X(U − Y)], where U is the set of all the attributes of the relation R.

For instance, in a relation describing employees together with their salary history as well as other attributes R(empNo, salary, year, name, role), we can say that there is a MultivaluedDependency empNo →→ salary, year or, equivalenty that there is a Join Dependency ⨝[{empNo, salary, year}, {empNo, name, role}] (note that this makes clear that in R holds also the “dual” Multivalued Dependency empNo →→ name, role).

You are asking which relations there are between Functional Dependencies and Join Dependencies. The question is reasonable, since there is a relation between Functional Dependencies and Multivalued Dependencies.

In Chapter 8 of Abiteboul, S. “Foundations of Databases.” Reading, Mass: Addison-Wesley, 1995. you can find an answer which is also proved:

  Proposition 8.3.3. Let U be a set of attributes, {X, Y, Z} be a partition of U, and Σ be a set of Functional Dependencies over U. Then Σ ⊨ ⨝[XY, XZ] if and only if either Σ ⊨ X → Y or Σ ⊨ X → Z.

In other words, if a Functional Dependency X → Y holds in a relation schema R, then this implies that the binary Join Dependency ⨝[XY, XZ] holds, and viceversa. Note that this is equivalent to say that you can perform always a lossless decomposition of a relation R{X,Y,Z} with a functional dependency X → Y in R1{XY}, R2{XZ}.

So, for instance, in the previous example, since empNo → name, the Join Dependency ⨝[{empNo, name}, {empNo, salary, year, role}] holds and {R1(empNo, name), R2(empNo, salary, year, role)} is a lossless decomposition.

For those who are reading on this topic, I thought the following elaboration might be helpful.

From C.J. Date's SQL and Relational Theory-How to Write Accurate SQL Code, it is mentioned in Appendix B that

  It’s also immediate that every FD is a JD, because (as Heath’s theorem tells us) if R satisfies a certain FD, then it can be nonloss decomposed into certain projections (in other words, it satisfies a certain JD).

Essentially, Heath's theorem states

  Heath's theorem. • A relation R(X,Y,Z) that satisfies a functional dependency X → Y can always be non-loss decomposed into its projections R1=πXY(R) and R2=πXZ(R)


Import Wikipedia .sql dump very slow I'm having troubles to import one of the SQl dumps of Wikipedia into a local database. I've downloaded the categorylinks.sql from https://dumps.wikimedia.org/enwiki/latest/ which is around 17GB when unpacked and I tried to load it into a local MySql database via the command line with mysql -uroot > path/to/file
I monitor the progress my using pipeviewer in front of the command and it begins with ok speed (1-2MB/s) but it gets slower and slower over time, currently sitting at 18KiB/s after a weekend of running and it gives me an ETA of 4 days and that ETA is rather rising then going down.

The first few INSERT statements run at this speed:

Query OK, 7352 rows affected, 64 warnings (0.48 sec)
Records: 7352  Duplicates: 0  Warnings: 64

Query OK, 7145 rows affected, 64 warnings (0.71 sec)
Records: 7145  Duplicates: 0  Warnings: 64

Query OK, 7139 rows affected, 64 warnings (0.66 sec)
Records: 7139  Duplicates: 0  Warnings: 64`

And currently, after 3 days it slows down to:

Query OK, 7166 rows affected, 64 warnings (5 min 25.89 sec)
Records: 7166  Duplicates: 0  Warnings: 64

Query OK, 7013 rows affected, 64 warnings (5 min 8.13 sec)
Records: 7013  Duplicates: 0  Warnings: 64

The warnings are all just "Invalid utf8mb4 character string", so those should not be the problem, right? And the duplicate checks are turned of by setting unique_checks to 0.

I've already tried to tweak many options, most importantly setting autocommit, unique_checks, and foreign_key_checks to 0. Also have set innodb_flush_log_at_trx_commit = 2 as well as playing around with the values of innodb_buffer_pool_size, innodb_log_buffer_size, innodb_log_file_size, innodb_write_io_threads.

Neither RAM (16GB), nor I/O write speed are a problem as the process does not use nearly the available resources according to htop and iotop.
I've read of other people importing >60GB of data in less than 4 hours, so it should be possible to speed it up somehow?

Do you have any recommendations on what other options to change?

Would it be an option to split the SQL file into many smaller files and import them separately? Can you do that parallel?

Thank you very much in advance!

Just in case someone else comes looking for the same type of information. I decided that I'd like to import only a subset of wikipedia pages (the most popular subset) and built a couple of tools that build the list of most popular wikipedia pages (from the monthly logs) and filter the sqldump data files before import.