Selecting consecutive rows with same values and one that differs So there is this question that explains how to do it but what I want is to find the duration from idle to active for a given userID and status for example for Brown it should show something like this:

userID        status     Duration_in_this_status (min)
------  -------------------   ------
Brown         idle             40
Brown         idle             ?

The ? inidcated that it is the time from this

userID  tStamp                status
------  -------------------   ------
Jason   2017-10-18 03:20:00   idle
Brown   2017-10-18 03:20:28   idle
Brown   2017-10-18 03:25:28   idle
Brown   2017-10-18 04:00:28   active
Brown   2017-10-18 04:10:28   active
-> Brown   2017-10-18 04:35:28   idle
Brown   2017-10-18 04:45:28   idle

till now.

I tried to modify the answer from the above question but all I could managae was create a variable @next_time and increase it when the if goes to @groupNumber +1 but I couldn't work it out because all it does it increases the year by one.

   SELECT userID, status, TIMESTAMPDIFF(minute, MIN(tStamp), MAX(tStamp)) AS
duration
    FROM (
       SELECT
       t.*
        , @groupNumber := IF(@prev_userID != userID OR @prev_status !=
status, @groupNumber + 1 and @next_time=@next_time+1, @groupNumber) AS gn
    , @prev_userID := userID
    , @prev_status := status
    , @next_time   := tStamp
    FROM t
    , (SELECT @groupNumber := 0, @prev_userID := NULL, @prev_status := NULL) var_init_subquery
    ORDER BY userID, tStamp
) sq
GROUP BY gn, userID, statu

So can any one help me to get that next value using these user-defined variables? Thanks

MySQL version 8.0.16

If you are on version 8.x (or above), you can take advantage of window functions like LAG/LEAD. This functions return a value of previous or next next row inside a partition, following an specific order.

Using your data:

SELECT
    userID, tStamp, status,
    CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status
         THEN 0 ELSE 1 END AS rst
FROM
   t;

returns:

userID | tStamp              | status | rst
:----- | :------------------ | :----- | --:
Brown  | 2017-10-18 03:20:28 | idle   |   1
Brown  | 2017-10-18 03:25:28 | idle   |   0
Brown  | 2017-10-18 04:00:28 | active |   1
Brown  | 2017-10-18 04:10:28 | active |   0
Brown  | 2017-10-18 04:35:28 | idle   |   1
Brown  | 2017-10-18 04:45:28 | idle   |   0
Jason  | 2017-10-18 03:20:00 | idle   |   1

Notice rst flagged each status change. Now using this news values you can easily find time differences between those rows where rst=1.

WITH ct AS
(
    SELECT
        userID, tStamp, status,
        CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status
             THEN 0 ELSE 1 END AS rst
    FROM
       t
)
SELECT
    userID, CONCAT("From ", status, " to ",
                   LEAD(status) OVER (PARTITION BY userID ORDER BY tStamp)) status,
    timestampdiff(MINUTE, tStamp,
                  LEAD(tStamp) OVER (PARTITION BY userID ORDER BY tStamp)) tDiff
FROM
    ct
WHERE
    rst = 1;

userID | status              | tDiff
:----- | :------------------ | ----:
Brown  | From idle to active |    40
Brown  | From active to idle |    35
Brown  | null                |  null
Jason  | null                |  null

db<>fiddle here


SAP BO Freehand SQL date formatting (Oracle db) My original query worked perfectly via SQL Developer:

select * from [table] where DT_CURRENT_DT = '15-OCT-18'

However when ran via freehand SQL in SAP BO I got no results.
After running the query with no conditions I noticed that BO re-formats the date to 10/15/18

to_date function solved the issue and now the condition is recognized by BO's freehand SQL.

DT_CURRENT_DT = to_date('10/15/18', 'MM/DD/YY')


Unique insert performance: Primary Key vs Unique Index I have a table of unique values (domains_unique), with column domain varchar(255), with more than 20 mil records.

What's the fastest way to insert into the table, by keeping the domain unique constraint?

I've decided that the query should be :

INSERT IGNORE INTO domains_table (domain) VALUE ('domain.com')

Should I make domain the primary key or should I make it a unique index?

Primary key method:

CREATE TABLE `domains_unique`
(
     `domain` varchar(255) NOT NULL
     PRIMARY KEY (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci;

Unique index method:

CREATE TABLE `domains_unique`
(
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `domain` varchar(255) NOT NULL
    PRIMARY KEY (`id`),
    UNIQUE KEY unique_index (`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE = utf8_general_ci;

Also how would much would changing the CHARSET and COLLATION to ascii_bin affect performance?

obs:

I would use INSERT DELAYED IGNORE INTO, but I need to know if the specific row (domain) was inserted, that also rules out the bulk insert.

UPDATE:

As promised the benchmarks results:

INSERT 5k unique rows with 4.5k new rows into a 1 mil row table, one row at a time:

Primary Key method:   5.7 seconds
Unique Index method:   6.3 seconds

To test scaling I've also tested the Unique Index method on a 40mil rows table and it took 45.8 s

From your question, I'm not completely sure if you're inserting multiple values per query, but you definitely should be.  With MyISAM as the engine, there should not be a performance difference between the index being UNIQUE or PRIMARY; MyISAM doesn't treat them differently in this case.  If you were using InnoDB, however, there would be a difference since it stores the data in primary key order.  If you don't need the id column, removing it and making domain the primary key would help performance.

Changing the collation should help since ascii is much simpler than utf8, but you might want to use ascii_general_ci instead of ascii_bin since domain names are case-insensitive.

One other way to do the queries would be to get the number of rows, INSERT DELAYED, flush the delayed writes, and then get the new row count.  The difference in the counts would be the same as the affected rows.  However, I don't think this would be significantly faster, but it would make the process more complex.


Query 100x slower in SQL Server 2014, Row Count Spool row estimate the culprit? I have a query that runs in 800 milliseconds in SQL Server 2012 and takes about 170 seconds in SQL Server 2014. I think that I've narrowed this down to a poor cardinality estimate for the Row Count Spool operator. I've read a bit about spool operators (e.g., here and here), but am still having trouble understanding a few things:

Why does this query need a Row Count Spool operator? I don't think it's necessary for correctness, so what specific optimization is it trying to provide?
Why does SQL Server estimate that the join to the Row Count Spool operator removes all rows?
Is this a bug in SQL Server 2014? If so, I'll file in Connect. But I'd like a deeper understanding first.

Note: I can re-write the query as a LEFT JOIN or add indexes to the tables in order to achieve acceptable performance in both SQL Server 2012 and SQL Server 2014. So this question is more about understanding this specific query and plan in depth and less about how to phrase the query differently.

The slow query

See this Pastebin for a full test script. Here is the specific test query I'm looking at:

-- Prune any existing customers from the set of potential new customers
-- This query is much slower than expected in SQL Server 2014
SELECT *
FROM #potentialNewCustomers -- 10K rows
WHERE cust_nbr NOT IN (
    SELECT cust_nbr
    FROM #existingCustomers -- 1MM rows
)

SQL Server 2014: The estimated query plan

SQL Server believes that the Left Anti Semi Join to the Row Count Spool will filter the 10,000 rows down to 1 row. For this reason, it selects a LOOP JOIN for the subsequent join to #existingCustomers.

SQL Server 2014: The actual query plan

As expected (by everyone but SQL Server!), the Row Count Spool did not remove any rows. So we are looping 10,000 times when SQL Server expected to loop just once.

SQL Server 2012: The estimated query plan

When using SQL Server 2012 (or OPTION (QUERYTRACEON 9481) in SQL Server 2014), the Row Count Spool does not reduce the estimated # of rows and a hash join is chosen, resulting in a far better plan.

The LEFT JOIN re-write

For reference, here is a way that I may re-write the query in order to achieve good performance in all SQL Server 2012, 2014, and 2016. However, I'm still interested in the specific behavior of the query above and whether it is a bug in the new SQL Server 2014 Cardinality Estimator.

-- Re-writing with LEFT JOIN yields much better performance in 2012/2014/2016
SELECT n.*
FROM #potentialNewCustomers n
LEFT JOIN (SELECT 1 AS test, cust_nbr FROM #existingCustomers) c
    ON c.cust_nbr = n.cust_nbr
WHERE c.test IS NULL

Why does this query need a Row Count Spool operator? ... what specific optimization is it trying to provide?

The cust_nbr column in #existingCustomers is nullable. If it actually contains any nulls the correct response here is to return zero rows (NOT IN (NULL,...)  will always yield an empty result set.).

So the query can be thought of as

SELECT p.*
FROM   #potentialNewCustomers p
WHERE  NOT EXISTS (SELECT *
                   FROM   #existingCustomers e1
                   WHERE  p.cust_nbr = e1.cust_nbr)
       AND NOT EXISTS (SELECT *
                       FROM   #existingCustomers e2
                       WHERE  e2.cust_nbr IS NULL)

With the rowcount spool there to avoid having to evaluate the

EXISTS (SELECT *
        FROM   #existingCustomers e2
        WHERE  e2.cust_nbr IS NULL)

More than once.

This just seems to be a case where a small difference in assumptions can make quite a catastrophic difference in performance.

After updating a single row as below...

UPDATE #existingCustomers
SET    cust_nbr = NULL
WHERE  cust_nbr = 1;

... the query completed in less than a second. The row counts in actual and estimated versions of the plan are now nearly spot on.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT *
FROM   #potentialNewCustomers
WHERE  cust_nbr NOT IN (SELECT cust_nbr
                        FROM   #existingCustomers
                       )

Zero rows are output as described above.

The Statistics Histograms and auto update thresholds in SQL Server are not granular enough to detect this kind of single row change. Arguably if the column is nullable it might be reasonable to work on the basis that it contains at least one NULL even if the statistics histogram doesn't currently indicate that there are any.


Syntax Error - OUTFILE with Timestamp I have managed to come up wit the below script for exporting some files. This needs to be done on a number of datasets, and is done daily.

The problem I am having is the I am getting the following error message:

  Error Code: 1064. You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near ''20160913.csv'' at line 12

    SET @exportfile =
   CONCAT("SELECT
            'FIELD_1'
        ,   'FIELD_2'
        ,   'FIELD_3'
        ,   'FIELD_4'
        ,   'FIELD_5'
        ,   'FIELD_6'
        UNION ALL
        SELECT * FROM mydatabasetable INTO OUTFILE 'E:/Data/exports/DailyExport/myfilename_'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '''
        LINES TERMINATED BY '\r\n'"
       , DATE_FORMAT( NOW(), '%Y%m%d')
       , ".csv'"
    );

PREPARE processing FROM @exportfile;
EXECUTE processing;
DROP PREPARE processing;

Please can someone identify what I am doing wrong?

The immediate issue is that you are appending the formatted date string and .csv' to the end of the query string, rather than to the end of the file path as I am assuming is the intention.

It seems to me your CONCAT expression should actually go like this:

CONCAT("SELECT ... INTO OUTFILE 'E:/Data/exports/DailyExport/myfilename_"
  , DATE_FORMAT( NOW(), '%Y%m%d')
  , ".csv'
    FIELDS TERMINATED BY ','
    ..."
)

Note that there's no ' at the end of the E:/Data/exports/DailyExport/myfilename_, because the date is appended at that point, followed by .csv' which completes the name.

Once you have fixed that, you are going to face another issue, which will be caused by the ENCLOSED BY line of your dynamic query. The ' character must be escaped when itself enclosed in '. So, it should be either:

...
ENCLOSED BY ''''
...

or

...
ENCLOSED BY '\''
...


How can I link to query file that opens in SQL Server Management Studio showing database login window? Background:

I have many tasks at work everyday. I have a query at work that needs
to be performed everyday(number of queries may increase). I have
general(good enough) ability in computers, databases and SQL.

Goal:
I want to optimize my work flow, and I want to make it very easy so even if I am not at work somebody with very little knowledge can do it.

Now: SQL is written in Excel file. The SQL text is copied -> Management Studio is opened -> Database login is done -> SQL text is pasted in -> SQL is executed.

Wanted: SQL is stored in file(sql) -> If opened, the database login screen is shown OR login is done automatically -> SQL is executed.

Problem:
The database login screen doesn't seem to be shown when an SQL file is opened(opens if Management Studio is already open).
I either want the login screen to be shown or to be able to include the username and password in the file somewhere so it is done automatically.
(I know this is small, but assume I'm lazy and time is money, and newbies can waste a lot of time :) )

SSMS will not prompt for a login if you are already connected to an instance (e.g. Object Explorer), this is by design. If you want to change connections simply right-click in the query window and go to change connection.

Your request to include the username and password in the file somewhere is bad security. Especially if this particular account has elevated privileges to the environment.

If you are looking to automate the process, allow minimal involvement, allow anyone to execute the queries, and run them against any number of servers then I would suggest building the queries in PowerShell. A simple function that accepts say a server name and the script file can be written around using Invoke-Sqlcmd or .NET code if you are so inclined. Or write a function for each query and then just have them pass in the server name.

  somebody with very little knowledge

This is dictated by your documentation of your process. If your process is written to a 3rd grade level then any adult is assumed to be able to perform that process strictly by following your documentation.


Why do these foreign key constraints cause problems? (Azure SQL Server 12) Here is a simplified version of the database I am attempting to create:

create table Worker (
    ID int,

    constraint PKW
    primary key (ID)
)

create table Business (
    ID int,
    W int,

    constraint PKB
    primary key (ID),

    constraint FKBW
    foreign key (W) references Worker (ID)
    on delete set null on update cascade
)

create table Note (
    ID int,
    B int,
    W int,

    constraint PKN
    primary key (ID),

    constraint FKNB
    foreign key (B) references Business (ID)
    on delete cascade on update cascade,

    constraint FKNW
    foreign key (W) references Worker (ID)
    on delete set null on update cascade
)

Essentially each business has a worker assigned to it, and any worker has the ability to create a note regarding any business. I have 3 foreign key constraints to reflect these relationships, but this creates the error:

  Introducing FOREIGN KEY constraint 'FKNW' on table 'Note' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

There are no relationship cycles in this setup. I can see how having FKBW on delete cascade would create 2 delete paths to Note, but not with my current setup. Even if I remove on update cascade from both FKBW and FBNW, the error persists. I don't see how set null creates any problems.

Any clarity would be appreciated!

The problem seem to be the ON UPDATE CASCADEs, the ON DELETEs are fine. I don't get an error if I remove the ON UPDATEs.

According to the accepted answer in "Foreign key constraint may cause cycles or multiple cascade paths?" on Stack Overflow, SQL Server does no "deep" inspection of the cascade paths but just a shallow one. If there is any cascading constraint to a table A in a table B, that already induces an edge in the cascade graph between A and B, regardless of the targeted columns.

So here we have a path directly from Worker to Note and a path from Worker via Business to Note. Note appears twice in the graph.

Though here, this raises no conflict on a specific column, this is already enough as the Microsoft support article "Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths:" states:

  You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

Triggers are suggested to work around this. However, I see a major problem with triggers, as I don't know any way to map rows from the inserted pseudo table with them of the deleted pseudo table other than via the primary key. But if that has changed in the update, there is no possible match and no way to detect the pair of changed values.

So I guess there is no way other than not updating primary keys and use an ON UPDATE NO ACTION. And usually one doesn't really need to update primary keys anyway, even though it would be a nice to have as an option.


Mysql query larger than tmpfs (ramdisk) I recently set up a ramdisk tmpfs on our mysql server and the performance improvement has been quite noticeable.  I've allocated 24 out of 32 GB's of ram for the ramdisk.  My only concern is what happens when there's a terrible query that ends up requiring more space than the allocated tmpfs?  I've read in a few places that it depends on the type of query but wanted to confirm that in some instances such a query can effectively shut down mysql.

Grrr.  Neither of the answers so far quite says the right things, so I feel obligated to give yet another Answer.

@jkavalik gives a nice description of why the problem occurred.  (But not what to do next.)

@DaveRix starts out good by saying "use RAM for caches, not RAM disk".  I very much agree.  But then he goes on to advocate dangerously high values for max_heap_size and tmp_table_size.  Those settings limit the amount of RAM allocated multiple time per connection.  That is, a complex query might allocate N*64MB.  This risks running out of RAM.  I suggest no more than 1% of RAM for them.

Another common mistake is to put tmpdir on a small disk filesystem.  This also causes abrupt halts.

Once tmpdir has room, @Cosi's query should run.  But perhaps not efficiently.  The next thing to do is look at the query, its EXPLAIN SELECT ..., the schema of the table(s), and especially the indexes.  Some common mistakes:

Subqueries instead of JOINs.
Single-column indexes when "composite" indexes would be better.


powershell command enable-alwayson not work for SQL Server Named instance Hi all,

I installed a SQL server 2016 default instance and a SQL Server 2017 named instance (instance name is SQL17) on my Windows 2012 Server.

I opened a powershell windows with administrator mode, import module SQLPS.
Then I run invoke-sqlcmd to query from the default instance and named instance, it works.
but when I run enable-sqlalwayson, it only worked for my default instance(I checked from SQL Server configuration mamanger), and get error with named instance.
Why?

Please refer the screenshot for detail error message.

Any help will be appreciate!

Check out this documentation and tweak your command slightly.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/enable-and-disable-always-on-availability-groups-sql-server?view=sql-server-2017

The example shows this as the proper command for an instance

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\Computer\Instance


How to transform results of a select statement formatted as XML? When I run the command

mysql EvePlanataryExpenses --xml -uuser -p -e "SELECT sTransactionDateTime, SUM(sQuantity * sPrice) as 'profit' FROM sell GROUP BY sMonth;" |sed '/^\s*$/d'

I get the following output

<?xml version="1.0"?>
<resultset statement="SELECT sTransactionDateTime, SUM(sQuantity * sPrice) as 'profit' FROM sell GROUP BY sMonth" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="sTransactionDateTime">2016-10-13</field>
    <field name="profit">1177089967.25</field>
  </row>
</resultset>

Is it possible to remove the statement="text" from the root element, and set the field name to something else than <field name=""></field> from the SQL query itself?

I saw something here that I thought I could use, but can't make it work.

Or would I need to run the output through a parser (Java, PHP, something else) and get that to remove the statement from the root element and rename the field elements?

The query doesn't know (or care) how its results might be formatted by the client (the mysql program in this case), so no, you cannot influence the XML in the query itself. What you're looking for is called XSL transformation, for which there are command line tools (e.g. xmlstarlet) through which you can pipe your output.


#1036 - Table 'pma__recent' is read only in phpMyAdmin / xampp / Windows I have about 40 databases on localhost powered by xampp 5.6.15.
Some of them use MyISAM and others InnoDB.

The database has been corrupt and phpMyAdmin was permanently outputing error messages. I tried many things suggested on stackexchange's websites and I almost managed to get back to normal.

However, there is still this message in phpMyAdmin when clicking on a table name:

#1036 - Table 'pma__recent' is read only

From my readings, this seems caused by innodb_force_recovery = 1 in xampp\mysql\bin\my.ini file.

Unfortunately, if I comment out innodb_force_recovery, MySQL cannot be started, probably because there are still errors in the database.

I called mysqlcheck -c -u root -p --all-databases from a terminal.
The log tells that some tables are not existing in engine (whilst they should).

How should I proceed to get back to normal?

This a draft of my ongoing attempts to solve the issue.

The first steps I suggest are:

Stop the MySQL server
Backup the whole database folder mysql/data
Start the server again
Check the MySQL databases individually for possible errors: mysqlcheck -u root -p some_database_name


What big limitations should I expect from Linked SQL Servers? Our product is based on Microsoft SQL Server. Currently, we are using three databases, and have always deployed them on one SQL Server instance.
The three database are OLTP, OLAP, and audit. The OLAP database has massive inbound data on EOD from both OLTP and audit, using cross database queries.
Questions
If we were to deploy these three databases onto three separate Standard Edition instances inside a single physical server, and bind them together using the Linked Server feature of SQL Server:

How transparent will it be to the application code? How much change should I expect?
Inbound data to OLAP amounted in 50k-100k rows, 200-500MB payload per EOD. How much performance drop should I expect?
What other big limitations should I expect?

Background
Currently we are pitching our potentially first client with 500+ concurrent users.
We are drafting a server spec, which includes 64 cores and 256GB RAM.
For SQL Server to utilize all those abundant resources, the client would have to buy Enterprise Edition, which for SQL Server 2016 is only available in per-core based licensing.
We are afraid that the licensing cost alone (64 x $7400) will put them down. So I am thinking of splitting the database into three instances of Standard Edition, and have them linked together, hoping that the linkage feature will be transparent from the application code.

How transparent will it be to the application code? How much change should I expect?

Not transparent at all. Expect major changes.

You should be prepared for very substantial performance degradation.

Distributed Query (the framework for linked servers) uses a general OLEDB model whatever the server on the other end happens to be. It is true that a SQL Server target may be able to offer more complete information (metadata, statistics etc.), but the result is still nowhere near as tightly integrated or capable as a native cross-database operation.

Remote queries have a deserved reputation for slow performance, and poor plan choices by the optimizer. Statements that change data (delete, insert, update, merge) are particularly prone since the basic model is often that of a cursor.

If you never need to perform ad-hoc cross-instance queries, you might be able to hand-tune each stored query for acceptable performance, but this is a lot of work, and success is by no means guaranteed.

For cross-instance bulk operations, you would be much better off using real bulk operations (bcp, BULK INSERT, SSIS...etc.) between instances than using linked servers.

All that said, the basic idea seems much more trouble than it is worth to me. Specify hardware that will work within the constraints of Standard Edition; or, if the client requires higher performance, get a bigger server and use Enterprise Edition.


Detecting if bit set in numeric value I have a tinyint column that represents a set of flags. Assuming I wanted to know if a specific bit was set or not, how can I do bitwise AND/OR in a stored procedure?

From your previous questions you use SQL Server. So you can use the & operator.

e.g. to see if the bit for 4 is on (and assuming NULL should return NULL)

SELECT CASE number & 4 WHEN 4 THEN 1 WHEN 0 THEN 0 END


Difference between On Delete Cascade & On Update Cascade in mysql I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE and ON DELETE CASCADE

My Parent Table

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

My Question is: What is the difference between the following sql queries.

ON DELETE CASCADE

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

ON UPDATE CASCADE

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON UPDATE CASCADE
) ENGINE=INNODB;

ON UPDATE CASCADE ON DELETE CASCADE

CREATE TABLE child (
        id INT,
        parent_id INT,
        INDEX par_ind (parent_id),
        FOREIGN KEY (parent_id)
            REFERENCES parent(id)
            ON UPDATE CASCADE ON DELETE CASCADE
    ) ENGINE=INNODB;

Are there any errors in the queries? What do these queries (1,2 & 3) mean?? Are they same???

A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here.

In the SQL 2003 standard there are 5 different referential actions:

CASCADE
RESTRICT
NO ACTION
SET NULL
SET DEFAULT

To answer the question:

CASCADE

ON DELETE CASCADE means that if the parent record is deleted, any child records are also deleted. This is not a good idea in my opinion. You should keep track of all data that's ever been in a database, although this can be done using TRIGGERs.  (However, see caveat in comments below).
ON UPDATE CASCADE means that if the parent primary key is changed, the child value will also change to reflect that. Again in my opinion, not a great idea. If you're changing PRIMARY KEYs with any regularity (or even at all!), there is something wrong with your design. Again, see comments.
ON UPDATE CASCADE ON DELETE CASCADE means that if you UPDATE OR DELETE the parent, the change is cascaded to the child. This is the equivalent of ANDing the outcomes of first two statements.

RESTRICT

RESTRICT means that any attempt to delete and/or update the parent will fail throwing an error. This is the default behaviour in the event that a referential action is not explicitly specified.

  For an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT`.

NO ACTION

NO ACTION: From the manual. A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

SET NULL

SET NULL - again from the manual. Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. This is not the best of ideas IMHO, primarily because there is no way of "time-travelling" - i.e. looking back into the child tables and associating records with NULLs with the relevant parent record - either CASCADE or use TRIGGERs to populate logging tables to track changes (but, see comments).

SET DEFAULT

SET DEFAULT. Yet another (potentially very useful) part of the SQL standard that MySQL hasn't bothered implementing! Allows the developer to specify a value to which to set the foreign key column(s) on an UPDATE or a DELETE. InnoDB and NDB will reject table definitions with a SET DEFAULT clause.

As mentioned above, you should spend some time looking at the documentation, here.


Pitfalls when using Java stored procedure Recently I have worked on a project that I assume could be solved by using Java stored procedure (more precisely some recieving/sending email package).

Аfter a while I've found that there are very few number of Java classes and Java stored procedures in my company's database. I asked DBA and he said that using Java stored procedures is prohibited because "It can easily put the server down".

I would be greateful if someone could expain me: is it really dangerous to use Java stored procedures that use external packages? Are there any pitfalls? Is using Java stored procedures not a common practice?

A well-written Java stored procedure is unlikely to "put the server down", however, Java routines might have some performance implications, because invoking them means that Oracle needs to start a Java virtual machine (JVM) process, transfer control to it, and wait for the Java code to terminate. Since this is still happening within the scope of a SQL transaction, concurrency might suffer. This is especially true when Java is used to synchronously perform some potentially long running activities that depend on remote resources, such as sending and receiving email.

You probably want to avoid using Java stored procedure if possible; consider using the UTL_MAIL Oracle built-in package to handle email messages.


How to safely change MySQL innodb variable 'innodb_log_file_size'? So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:

mysql> show variables like 'innodb_buffer%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

When I went to change the innodb_log_file_size value (example my.cnf on mysql's innodb configuration page comments to change the log file size to 25% of the buffer size. So now my my.cnf looks like this:

# innodb
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M

When I restart the server, I get this error:

  110216  9:48:41  InnoDB: Initializing buffer pool, size = 128.0M
  110216  9:48:41  InnoDB: Completed initialization of buffer pool
  InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
  InnoDB: than specified in the .cnf file 0 33554432 bytes!
  110216  9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
  110216  9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

So my question: Is it safe to delete the old log_files, or is there another method to change the innodb_log_file_size variable?

Yes it is safe to delete the log file once mysqld has been shutdown

In light of this, just perform the following steps:

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop
mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start

Starting up mysqld will recreate ib_logfile0 and ib_logfile1

Give it a Try !!!

UPDATE 2011-10-20 16:40 EDT

It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option about 1 hour before shutdown:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:

[mysqld]
innodb_max_dirty_pages_pct = 0

UPDATE 2013-04-19 16:16 EDT

I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.


How do I manage SSL credentials for multiple Postgres connections? We're setting up Redash and need to connect to multiple Data Sources that are Postgres databases that require SSL connections. I have a postgres.key and postgres.crt file from each database. Redash is running as user redash on Ubuntu 16.04.

What should I do with these keys so that Redash (and the Python psycopg2 library, behind the scenes) can find the proper credentials to connect to the Data Sources?

The best I've been able to do is put one set of them in /home/redash/.postgresql, which is where psycopg2 looks for credentials by default. However, that doesn't allow me to connect to the second database.

use the sslcert and sslkey paramterer in the connection string to indicate the files you want.

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

eg:

 con=psycopg2_connect(dsn='host=host1 user=yourname sslcert=host1.crt sskkey=host1.key')

probably something using string::format would be more practical.

.

If that seems too messy

Using a service definition to store the parameters is another option
define the connection paramaters (like above) for each server in pg_services.conf (create that file) and use connection strings like

con=psycopg2_connect(dsn='service=host1')

https://www.postgresql.org/docs/10/static/libpq-pgservice.html

other clients (like psql) can also use the services thus defined, so this way will save you typing in the future.

but it seems redash is configured using environment variables  with the database as as a URL perhaps like this:

like this with a service definition

export REDASH_DATABASE_URL=postgres:///?service=database1

or

like this explicitly:

export REDASH_DATABASE_URL="postgres://db_user@host.name.or.ip/db_name?sslcert=host1.crt&sskkey=host1.key"

from a bries sfan of the docs redash seems to use postgres for db_user and db_name

so far as I know libpq does not use a different interface for database names, connection strings, and database urls, where one can be used any other form can be used instead, however the redash docs say to use a URL.

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-CONNSTRING


how to make sure pt-sync-table data is the latest? the first step is to checksum difference and record into percona.checksum table

pt-table-checksum h=xx,P=xx,u=xx,p=xx method=dsn=h=xx,D=xx,t=xx --databases=kobe

the second step is to print the repair SQL into a file

pt-table-sync --print --sync-to-master h=xx,P=xx,u=xx,p=xx >repair.sql

and I need to take this repairsql file to developers,show them how many records are difference, difference detail, and let them make sure。Ater that
I execute the repair sql

so my question is what I execute this old repair SQL, the new data is inserting ,and the new difference is creating, maybe this old repair SQL will cover the latest data !

can anybody help me ? thank you !

Use the --where option

Do only rows matching this WHERE clause. You can use this option to limit the checksum to only part of the table. This is particularly useful if you have append-only tables and don’t want to constantly re-check all rows; you could run a daily job to just check yesterday’s rows, for instance.
This option is much like the -w option to mysqldump. Do not specify the WHERE keyword. You might need to quote the value. Here is an example:

pt-table-checksum --where "ts > CURRENT_DATE - INTERVAL 1 DAY"

Source: https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#cmdoption-pt-table-checksum--where


Periodically very slow execution of simplest queries Inspecting MySQL slow query log I found out that in certain periods of time there are a bunch of queries in log which are supposed to be very fast but were actually very slow.

For example:

# User@Host: ***[***] @  [***]
# Query_time: 14.532574  Lock_time: 0.050162 Rows_sent: 18  Rows_examined: 18
SET timestamp=1483014348;
CALL get_games();
# User@Host: ***[***] @  [***]
# Query_time: 15.287524  Lock_time: 0.008114 Rows_sent: 18  Rows_examined: 18
SET timestamp=1483014348;
CALL get_games();
# User@Host: ***[***] @  [***]
# Query_time: 15.637633  Lock_time: 0.027461 Rows_sent: 18  Rows_examined: 18
SET timestamp=1483014348;
CALL get_games();
# User@Host: ***[***] @  [***]
# Query_time: 15.070246  Lock_time: 0.050137 Rows_sent: 18  Rows_examined: 18
SET timestamp=1483014348;
CALL get_games();

Where procedure get_games is just a simple SELECT from table with 18 rows and normally executes immediately.

MySQL [db_receipt]> show create table game;
MySQL [db_receipt]> CREATE TABLE `game` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` smallint(5) unsigned NOT NULL COMMENT 'Гейтовый идентификатор игры',
  `product_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Название игры для печати в квитанции',
  `arm_url` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Url в АРМ-е',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

MySQL [db_receipt]> show create procedure get_games;
+-----------+----------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure                                                                                | character_set_client | collation_connection | Database Collation |
+-----------+----------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| get_games |          | CREATE DEFINER=`receipt_adm`@`%` PROCEDURE `get_games`()
l_proc: begin

    SELECT  `product_id`, `product_name` FROM `game`;

end | utf8                 | utf8_unicode_ci      | utf8_unicode_ci    |
+-----------+----------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+----

There are also a lot of other slow simple queries such select a row by primary key etc.

What can cause such a performance degradation? How can I troubleshoot a problem to find a reason?

EDIT: MySQL Server has very good characteristics and normally queries run fast. It's a production server under high load.

EDIT2: Another slow query example (on another database), also very simple:

# Time: 161230 12:13:39
# User@Host: ***[***] @ *** [***]
# Query_time: 4.780936  Lock_time: 0.000069 Rows_sent: 0  Rows_examined: 1
use ***;
SET timestamp=1483089219;
update ShoppingCart set cartStatus='ACQUIRED' where shoppingCartId=23423;

Probably... Some other transaction hit that table in some locking way.  And it took 15+ seconds for it to finish.  Meanwhile, all of your 'users' were running this SP, but being held up until the lock was released.

If you can catch it when it happens, do SHOW PROCESSLIST, and look for the queries other than CALL get_games.  One of them is likely to be the villain.

Once you have found it, there may be several actionss to consider...

Not grabbing the table so forcefully; that is, moving it outside its transaction, or otherwise changing the SQL.  (This seems like the likely answer.)
Not having every user always call get_games.
Other

SHOW ENGINE INNODB STATUS is likely to show the villain, also, but you have to run it during or very soon after the problem.

I dislike using autocommit=0 because I might forget to eventually do COMMIT.  That is a hard-to-find bug.  I prefer to explicitly say BEGIN as a reminder that I need to place the COMMIT some time really soon.  I even insist  (for my code) of putting both in the same subroutine.

Simply moving the CALL to before the BEGIN may solve it (unless you are also modifying the table in the transaction).


Postgresql Recursion Query I have a table like so:

effective_from  archived      cal
2020-01-19      2020-01-20    15.3
2020-01-13      2020-01-19    42.2
2020-01-17      2020-01-18    13.6
2020-01-16      2020-01-17    11.2
2020-01-15      2020-01-16    7.2
2020-01-14      2020-01-15    7.2
2020-01-13      2020-01-15    8.6
2020-01-13      2020-01-14    4.2
2020-01-12      2020-01-13    3.7

I'd like to write a query that returns a table that has the most recent effective_from row, as determined by the archived row. The most recent effective_from row is the one with the most recent archived date. There are three rows with an effective_from date of Jan-13, but the one I'm interested in is the one where archived = 2020-01-19, cal = 42.2

I can do it in a brute force way, where I order the table by archived and then do a select and union on each row in turn based on the effective_from column:

SELECT effective_from, archived, cal FROM stack_question ORDER BY archived ASC;

(SELECT * FROM stack_question WHERE effective_from <= '12-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '14-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '15-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '16-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '17-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '19-Jan-2020' ORDER BY archived DESC LIMIT 1) ORDER BY effective_from ASC;

This gives me the expected result:

2020-01-12  2020-01-13 3.7
2020-01-13  2020-01-19 42.2
2020-01-19  2020-01-20 15.3

But of course that isn't actually a viable way of doing it. I could always write a function in pg/PLSQL or whatever, but I feel there's an efficient way to do this with just a SELECT. CTEs/recursion seem like the most promising approach, but I can't quite wrap my head around how it would look for my example based on what I've read.

Does anyone have any rough pointers on the best approach here?

I've put my test case on dbfiddle.

Thanks for the fiddle, I extended it a bit: fiddle

select distinct effective_from, archived, cal
from (
    select s1.effective_from
         , s1.archived
         , s1.cal
         , row_number() over (partition by s2.effective_from
                              order by s1.archived desc) as rn
    from stack_question s1
    join stack_question s2
        on s1.effective_from < s2.archived
) as t
where rn = 1
;

I'm in a bit of a hurry, so I don't time to investigate further, but it should give you a start. The idea is to enumerate the rows from s2 to get archive


Ansible: How to change MySQL server root password by reprovisioning the server I have provisioned my server with Ansible playbook. I've used the root/bedrock-Ansible playbook.

One of the tasks was to set up mysql server together with mysql root user password.

Now I urgently need to change this password. The steps I took:

I updated variables for Ansible roles
I executed the command ansible-playbook -i hosts/staging server.yml in order to reprovision the server

All tasks were executed as expected (no changes), but the script failed at
[mariadb | Set root user password] with this message:

msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials

My guess is that once MySQL root password has been set, reprovisioning the server cannot change this password.

Is it possible at all to change MySQL root password by reprovisioning the server with Ansible? What are my options?

The problem you have is that Ansible is trying to use the same root password to login as you want to change it to:

- name: Set root user password
  mysql_user: name=root
              host="{{ item }}"
              password="{{ mysql_root_password }}"
              check_implicit_admin=yes
              login_user="{{ mysql_user }}"
              login_password="{{ mysql_root_password }}"
              state=present

Obviously this is never going to work if you want to use this play to change it.

Instead you should change the above play to be something like:

- name: Set root user password
  mysql_user: name=root
              host="{{ item }}"
              password="{{ mysql_root_password }}"
              check_implicit_admin=yes
              login_user="{{ mysql_user }}"
              login_password="{{ mysql_old_root_password }}"
              state=present

And then update the relevant inventory files to add this new variable.

So your group_vars/production should now contain:

mysql_old_root_password: productionpw
mysql_root_password: newproductionpw

It looks like this playbook uses the root password in both the roles/mariadb/tasks/main.yml playbook and also roles/wordpress-setup/tasks/database.yml so you might want to run the whole server.yml playbook to make sure this is set up properly.


Does the Mysql install location affect write speeds? Up until lately, I used a 32 bit version of Mysql 5.7, bundled with a WAMP server (EasyPHP) on Windows for local development, all running on an SSD. I ran into database size issues, and had to upgrade to a 64 bit version which can handle bigger file sizes. Since no 64 bit version was available for the WAMP server, I opted for a separate install of the Mysql Community server 8.0. It now works fine with my bigger databases.

The default install location on Windows is in Program Files (the MSI installer did not offer to install anywhere else), which on my machine is a regular hard drive. To speed things up, I modified the Mysql configuration to store the data files on the SSD, which also works fine. However, importing the same 2GB SQL dump file takes a lot longer than the 32 bit version did before (30 minutes vs 8-10 previously).

The only major differences between the two SQL servers are:

The server version (5.7 vs 8.0)
The architecture (64bit vs 32bit)
The install location (SSD vs regular hard drive)

I am tempted to dismiss 1) and 2), since I could not find any specific related speed issues online.

Thus my question: could it be that although the data files are stored on the SSD, that the server itself being installed on a regular hard drive slows things down? My searches regarding Mysql cache files showed that InnoDB temporary files are stored in the data folder, so that should be okay.

I have a nagging feeling that I'm missing something obvious.

As a comparison, I uninstalled the Mysql 8.0 server, and replaced it with a 5.7 64-bit version, with the same setup: Server binaries on the regular hard drive, data files on the SSD. The speed went back to 8-10 minutes to import the SQL files.

I can only assume that the issue is with the v8.0 server, but I cannot speculate as to why. Since I have no particular reason to need the v8.0, I will stay with the v5.7 for now.


How to specify SQL startup parameters in quiet installation via command line I want to fix the identity gap bug in SQL Server 2012 via the -T272 startup parameter.

How can I automate this after installation? I found no install parameter for startup parameters.

If you go just over half way down on this link you will see the startup parameter for trace flags.

You can add them in the configuration manager here:

In this case it would be -T272 with the capital T.  Per the link the lower case t is for specific flags used by the support engineers.

EDIT  @MaxVernon pointed out I'd missed the point of the question :)
Shawn Melton has a post here were he discusses what you are trying to do.  He has a script that you can run to add startup parameters.  Mike Fal then wrote a followup here where he shares a function he wrote called Set-SQLStartupParameters that is supposed to be somewhat safer than Shawn's method.

Here is Shawn's code:  (Mike's requires a download from GitHub so you can follow the links if you'd like that one.)

$server = 'MyServer'
$sqlservice = "MSSQLSERVER"
$sqlagentservice = "SQLSERVERAGENT"
$flagsToAdd = ';-T1117;-T1118;-T3226"

Add-Type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
$sqlwmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $server
$wmisvc = $sqlwmi.Services | where {$_.name -eq $sqlservice}
$wmisvc.StartupParameters = $wmisvc.StartupParameters + $flagsToAdd
$wmisvc.Alter()

$wmisvc.Stop()
Start-Sleep -seconds 15
$wmisvc.Start()

$wmiAgent = $sqlwmi.Services | where {$_.name -eq $sqlagentservice}
$wmiAgent.Start()


Change owner of all schema objects I want to change the owner of all objects in a schema. To do this I use the following function:

CREATE OR REPLACE FUNCTION chown(in_schema VARCHAR, new_owner VARCHAR)
RETURNS void AS
$$
DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;

  r record;
BEGIN
  object_types = '{type,table,sequence,index,table,view}';
  object_classes = '{c,t,S,i,r,v}';

  FOR object_type IN
      SELECT unnest(object_types) type_name,
                unnest(object_classes) code
  loop
    FOR r IN
      EXECUTE '
          select n.nspname, c.relname
          from pg_class c, pg_namespace n
          where n.oid = c.relnamespace
            and nspname = ''' || in_schema || '''
            and relkind = ''' || object_type.code || ''''
    loop
      raise notice 'Changing ownership of % %.% to %',
                  object_type.type_name,
                  r.nspname, r.relname, new_owner;
      EXECUTE
        'alter ' || object_type.type_name || ' '
                 || r.nspname || '.' || r.relname
                 || ' owner to ' || new_owner;
    END loop;
  END loop;

  FOR r IN
    SELECT  p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
    FROM    pg_catalog.pg_namespace n
    JOIN    pg_catalog.pg_proc p
    ON      p.pronamespace = n.oid
    WHERE   n.nspname = in_schema
  LOOP
    raise notice 'Changing ownership of function %.%(%) to %',
                 r.nspname, r.proname, r.args, new_owner;
    EXECUTE
       'alter function ' || r.nspname || '.' || r.proname ||
       '(' || r.args || ') owner to ' || new_owner;
  END LOOP;

  FOR r IN
    SELECT *
    FROM pg_catalog.pg_namespace n
    JOIN pg_catalog.pg_ts_dict d
      ON d.dictnamespace = n.oid
    WHERE n.nspname = in_schema
  LOOP
    EXECUTE
       'alter text search dictionary ' || r.nspname || '.' || r.dictname ||
       ' owner to ' || new_owner;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

(https://www.garysieling.com/blog/postgres-change-owner-all-objects-in-schema)

Unfortunately, the function doesn't work as expected in some cases.

CREATE ROLE user_old LOGIN;
CREATE ROLE user_new LOGIN;

CREATE SCHEMA schema_a AUTHORIZATION user_old;

CREATE TABLE schema_a.test_a (
id serial NOT NULL,
CONSTRAINT test_a_pkey PRIMARY KEY (id)
);

ALTER TABLE schema_a.test_a OWNER TO user_old;

SELECT chown('schema_a', 'user_new')

NOTICE: Changing ownership of sequence schema_a.test_a_id_seq to user_new
ERROR: cannot change owner of sequence “test_a_id_seq”
DETAIL: Sequence “test_a_id_seq” is linked to table “test_a”.
KONTEXT: SQL statement “alter sequence schema_a.test_a_id_seq owner to user_new”
PL/pgSQL function chown(character varying,character varying) line 27 at EXECUTE

********** Error **********

ERROR: cannot change owner of sequence “test_a_id_seq”
SQL state: 0A000
Detail: Sequence “test_a_id_seq” is linked to table “test_a”.
Context: SQL statement “alter sequence schema_a.test_a_id_seq owner to user_new”
PL/pgSQL function chown(character varying,character varying) line 27 at EXECUTE

Does anyone know how to fix this?

to avoid mentionned error, try changing the order:

CREATE OR REPLACE FUNCTION public.chown(in_schema character varying, new_owner character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;

  r record;
BEGIN
  object_types = '{type,table,table,sequence,index,view}';
  object_classes = '{c,t,r,S,i,v}';

  FOR object_type IN
      SELECT unnest(object_types) type_name,
                unnest(object_classes) code
  loop
    FOR r IN
      EXECUTE format('
          select n.nspname, c.relname
          from pg_class c, pg_namespace n
          where n.oid = c.relnamespace
            and nspname = %I
            and relkind = %L',in_schema,object_type.code)
    loop
      raise notice 'Changing ownership of % %.% to %',
                  object_type.type_name,
                  r.nspname, r.relname, new_owner;
      EXECUTE format(
        'alter %s %I.%I owner to %I'
        , object_type.type_name, r.nspname, r.relname,new_owner);
    END loop;
  END loop;

  FOR r IN
    SELECT  p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
    FROM    pg_catalog.pg_namespace n
    JOIN    pg_catalog.pg_proc p
    ON      p.pronamespace = n.oid
    WHERE   n.nspname = in_schema
  LOOP
    raise notice 'Changing ownership of function %.%(%) to %',
                 r.nspname, r.proname, r.args, new_owner;
    EXECUTE format(
       'alter function %I.%I (%s) owner to %I', r.nspname, r.proname, r.args, new_owner);
  END LOOP;

  FOR r IN
    SELECT *
    FROM pg_catalog.pg_namespace n
    JOIN pg_catalog.pg_ts_dict d
      ON d.dictnamespace = n.oid
    WHERE n.nspname = in_schema
  LOOP
    EXECUTE format(
       'alter text search dictionary %I.%I owner to %I', r.nspname, r.dictname, new_owner );
  END LOOP;
END;
$function$

Also read about REASSIGN OWNED

Alse here is info on relkind if you wander why I changed the order:

  r = ordinary table, i = index, S = sequence, v = view, m =
  materialized view, c = composite type, t = TOAST table, f = foreign
  table


Should I create a child table without primary key? I'm designing a database with a child table that may contains billions of records in the future. So I try to use less fields (and smaller ones) as possible.

So I'm considering not using a primary key, as it would have to be a bigint, but I will never use it in my queries (this child table won't have ever a 1-N relationship).

Does a primary key useful for something else than query and join ? I suppose Mysql have an other system to distinguish a record from another, or am I wrong ?

Thanks a lot

Edit : my create statement

CREATE TABLE `receipt_line` (
  `id` bigint(20) unsigned NOT NULL,
  `receipt_id` mediumint(8) unsigned NOT NULL,
  `prod_id` smallint(5) unsigned NOT NULL,
  `coupon_id` smallint(5) unsigned DEFAULT NULL,
  `price` decimal(5,2) NOT NULL,
  `qty` decimal(4,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

ALTER TABLE `receipt_line `
  ADD PRIMARY KEY (`id`),
  ADD KEY `id ticket` (`receipt_id`),
  ADD KEY `id produit` (`prod_id`),
  ADD KEY `id coupon` (`coupon_id`),

Obviously, the parent table is Ticket, and I have other linked tables (product, coupon).
The id field is linked to nothing. The other keys aren't unique.

If the records needs to be unique, then it is up to you to either pick a composite key (multiple columns) to make them unique, or use a primary key (unique by definition). I don't think there is anything more efficient. If the concern is running out of bigint values, you won't. Knowing now that you will never query or join on this table seems shortsighted.

EDIT After More Info: If some combination of the receipt_id, prod_id and coupon_id are unique then you can just use that to establish uniqueness with a compound/composite primary key and dump the bigint id.


Running a simple logging program, how to diagnose spikes mongod's CPU activity? I'm running mongodb 1.4.7 - I've a node server set up which logs incoming requests in a logs collection:

{'u':'user123', 'l':'log data'}

In addition, once the request happens, the server will check if a users collection contains a user123 in this case via the .find({'u': 'user123'})

Running top I see this:

PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND
4709 mongodb   20   0 1568m 435m 303m R 176.3 11.0 370:22.58 mongod

This, however, only happens every so often, most of the time it sits at about 1-2% CPU and running mongotop shows that mongo isn't particularly stressed:

           mdb.users         1ms         1ms         0ms
           mdb.impressions   1ms         1ms         0ms

I'm getting a decent amount of traffic. What steps can I take to identify the source of resource usage spikes?

There is no one simple answer here, it will very much depend on your usage.  CPU can be consumed in many ways.  I'll list the most common (in my experience with MongoDB) and how you can try to determine if this is the case for your spikes.

Before I do though, I will say that a CPU spike is not necessarily a problem - people get obsessed about spikes in CPU, increases in the load metric, but what really matters is the performance of your application - is it actually suffering during these spikes?  If not, then it's probably not that important - by all means monitor it and be aware in case things get worse and you do see an impact, but spikes happen on busy systems without it being a problem per se.

Common Causes (and how to narrow it down):

1 - A periodic, intensive database operation

Table scans (non-indexed queries), massive index updates, in-memory sorts and probably a few others can all tax a database process from the CPU side of things.  To determine if that is the cause here, there are a few tools that you should be using:

MMS Monitoring

This is going to come up in this list more than once, but specifically you will want to look at the number of ops being executed at a given time, btree hits, and the hardware stats (munin-node required).  These will give you an insight into the work that the database is doing.  User CPU is what you have listed above, but is there a system CPU component too?  MMS will graph them over time and let you correlate the spikes in CPU with other events in general.

Examine the mongod logs

If you know the times that you are seeing the spikes, take a look in the logs - are there any slow operations around that time? Any index builds?  If the system is being generally slowed down there might be a lot of false positives in here, so look for trigger events, common themes etc.

The excellent mtools by Thomas Rueckstiess can make this analysis significantly easier, allowing you to graph events, look at specific slices of time etc.

mongotop

This will tell you what databases and collections MongoDB is spending time on.  Now, if you only have one collection and no replication, then this will not be terribly helpful, but if you have a multitude to pick from, then it can be very effective at narrowing things down, especially if you combine it with the data from MMS to get you specific time frames to analyze.  Unlike MMS though, you have to run this in advance of the issue so that the timeframe of your suspicious spike is covered.

mongostat

This tool provides data similar to that in MMS, but at a per-second granularity (MMS Monitoring is 60 seconds), so if the spikes are short lived, this can be a valuable piece of additional data.  Similar to mongotop, it must be run manually beforehand to cover an event and then the data gathered/examined.

2 - An IO Bottleneck

Slow IO can cause CPU usage to spike.  Again, MMS Monitoring with munin-node installed will give you an idea as to the nature of the CPU usage - if IOWait is high then you will want to pay attention to the whether the traffic is read or write bound (or both) and looking at the page fault levels can also be enlightening (high levels can mean that data is not fitting into memory, or "cold" data is being paged in at that time).

Essentially, all of the same tools are in play here as in the first scenario, but this time you can add:

IOStat

IOStat deserves an essay in and of itself, so I will be brief - you will want to look at %util as a broad first measure, then look for further information to determine the source of the slowness.  The await metric can tell you if queueing is an issue, svctime can tell you if the device is being slow, high reads or writes at the time of the spikes can tell you what particular IO limit you are running into.  Again, similar to the mongostat/mongotop tools, you can run this command to a much finer granularity than you will see in MMS.

3 - Connection Related Churn

This is somewhat more rare, but a badly configured driver, or an application that gets out of control can sometimes cause a massive amount of connections to be opened/closed in a small time frame while leaving the concurrent connection level fairly constant.  You can use a combination of MMS (connection graph) and analysis of the log files (as long as you are not running with --quiet) to track this down.

There are quite a few other things to look at here, but those are where I would start, and if you master the tools listed you will be well on your way to troubleshooting those other possibilities.


which sql server edition is good for practice Which SQL Server (free/trail) would be good to install, which requires minimum resource and have maximum features. I mean, I can practice mirroring,logshipping and replication. I found this link, but confuse with enterprise and standard edition.

The 180 day evaluation version from Microsoft is Enterprise Edition.  AFAIK there is no ability to "trial" Standard edition from Microsoft outside of a licensing agreement you might have.

From the point of view of overhead between editions, there's negligible difference.

If you want something longer than 180 days, get the developer edition as its feature complete compared to Enterprise but only $50 from Amazon


How do I select arrays that are not empty? Why is this so tricky, what is token set to that it isn't equal to null nor an empty string?

SELECT lexemes
FROM ts_debug('This is a title')
WHERE alias = 'asciiword';

 lexemes
---------
 {}
 {}
 {}
 {titl}
(4 rows)

Ok.. So I want to get rid of {},

SELECT lexemes
FROM ts_debug('This is a title')
WHERE alias = 'asciiword'
  AND lexemes <> '{}'
  AND lexemes <> ARRAY[]::text[]
  AND lexemes IS NOT NULL
  AND lexemes <> ARRAY[' ']
  AND lexemes <> ARRAY[null]::text[];

I knew most of these wouldn't work., but I'm totally confused why <> '{}' wouldn't work not <> ARRAY[]::text;. How do I filter this out?

The reason seems to be that empty strings in that column have array dimensions [1:0]. Should normally be NULL. See:

SELECT lexemes, array_dims(lexemes) FROM ts_debug('a title');

 lexemes | array_dims
---------+------------
 {}      | [1:0]  -- !!
         |
 {titl}  | [1:1]

Empty arrays normally have NULL as array dimensions.

SELECT '{}'::text[] AS test, array_dims('{}'::text[]);

 test    | array_dims
---------+------------
 {}      | <NULL>

Hence, the comparison lexemes = '{}'::text[] returns FALSE. Looks like a bug to me. I tested versions 8.4 - 10 Beta. It's in all versions.

As a workaround, to exclude all empty arrays including the odd case (and NULL):

SELECT *
FROM   ts_debug('This is a title')
WHERE  cardinality(lexemes) > 0;

Or compare the text representation:

...
AND    lexemes::text <> '{}';

I filed bug report #14826.


Should we use SQL Server partitions for a multi-tenant database design? My organisation is creating a multi-tenant data architecture to support analysis of data uploaded by multiple different clients. The database is Microsoft SQL Server.

It has been suggested that partitioning the database by Client ID might be a good way to manage the data volume (which could be considerable, certainly billions of rows over time)

Is this a good idea? Why or why not?

There are lots of ways for you to manage your data volume, but what you haven't really described here is the end result that you desire. Is it performance? Is it manageability? Is it security? Is it scalability? Is it your ability to add in new clients quickly and easily over time?

When you say 'manage the data volume', I am thinking you are looking to administer the data in a way that allows for decent performance. That makes me think of partitioning, but I don't know enough about the data to say that this is the right thing to do. Partitioning by client ID could be fine, but if you have thousands of clients, it may not be the right choice.

I think you should find the real pain points you are trying to solve and then look to architect a solution that mitigates those pain points.


Update taking too long I am updating a table with 250 million rows in SQL Server, and it's been running for 4.5 days. I'd like to use the computer it's running on for other memory-intensive work, so I'm torn about whether to cancel the query (which might also take days, right?) or let it keep going. Does anyone see anything in the code below to make you think it's in a never ending loop or something? Will canceling it take so long it's not worth doing?

Here is the code:

update A.Large_Table
set [Large_Table].var1
= [Segment_Info].var1
from A.[Large_Table] left join [A].[Segment_Info]
on [Large_Table].id = [Segment_Info].id
where [Large_Table].var1 is null;

Var1 and id in both tables are integers. I have successfully done this query on a 100 million row table in the past, and it took maybe 2 hours, but in that case the join was performed on an 8-character string column (instead of the 'id' integer). I would have thought that switching to an integer would greatly improve performance and I could get the results for a larger table in comparable time.

Once I get past this run and either cancel or it finishes, I am aware of a few things to make this go better in the future (though it's not an operation I have to run often):

Use insert into or select into a new table rather than update the existing one
Move my log file to a different drive than the database (I'm still learning such basic things...)
Restart SQL Server so the temp file clears

Any other things to try? Any of those above not likely to actually give a boost?

Thanks for your help!

Yikes, I'd be scared to look at your transaction log. This needs to be run in batches. You can experiment with that to determine the fastest runtime. What I like to do is strip out maybe a million or so records, then run updates in various batch sizes, like 1k, 2k, 5k, 10k, at a time to determine the quickest. I've done several hundred million row updates in an hour before. Then again, this also depends on the data size and I'm hoping you aren't messing around with blob fields.


SQL Server 2012 offers the 'LAG' keyword which enables a SELECT statement to display the previous row.  You could use this to compare the current row to the previous row in the WHERE clause.

Here is an example:

USE Test;
CREATE TABLE LagTest
(
    ID INT NOT NULL CONSTRAINT PK_LagTest PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Data NVARCHAR(255) NOT NULL
);

INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test1');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test2');
INSERT INTO LagTest (Data) VALUES ('test3');
INSERT INTO LagTest (Data) VALUES ('test3');
INSERT INTO LagTest (Data) VALUES ('test3');

SELECT *
FROM (
SELECT ID, Data, LAG(Data,1,0) OVER (ORDER BY ID) AS PreviousData
FROM LagTest
) AS L
WHERE L.PreviousData <> L.Data;

The returns:


How indexing on a date field that is constantly updated and or omitted affect performance? In MongoDB, I have a collection with a date field called last_updated I would like to index. Reason being, I am often querying on a date range of that field in addition to sorting by that field and feel as though I can speed the process up. I would like to add as a side note: this collection does see a lot of writes and updates.

However, I would like to know the implications of doing so. My understanding is that updates are the heaviest transaction in a b-tree index structure and would poorly affect performance. Would anyone mind explaining whether it would or wouldn't and if not why it would be just as normal as indexing on another date field (one that is not updated).

Hi It will affect but not greatly.what happens is whenever you made change in collection index will be updated and during index update your collection will be locked .So to overcome this you can use index in background  by using:
db.collection.createIndex( { last_updated: 1}, {background: true} )

it will let you use your collection while updating so you will never found any kind of disruption because of your index.

As you have mentioned that you do sorting on "last_updated".I strongly recommend you to use index .you will have high read performance at a minimal indexing cost.


Mongo: mongodump and the config database error I've setup a backup script on my Ubuntu box using mongodump. However this currently doesn't work because of an error when attempting to dump the built in config database:

(Apologies for the image, I couldn't copy paste from the terminal I used)

How can I fix this? I either need to add the right permissions to my user, or exclude the config database. Ideally I'd like mongodump to ignore errors on any particular database so I still get a useful backup.

Here is the user I'm using to login as, as defined in the admin database:

{
    "_id" : "admin.admin",
    "userId" : UUID("b1928c55-fbaa-4196-9e7c-93e9139501ed"),
    "user" : "admin",
    "db" : "admin",
    "roles" : [
        {
            "role" : "userAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "dbAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "readWriteAnyDatabase",
            "db" : "admin"
        }
    ],
    "mechanisms" : [
        "SCRAM-SHA-1",
        "SCRAM-SHA-256"
    ]
}

You don't have the required privileges for the config database.

See https://docs.mongodb.com/manual/reference/built-in-roles/ :

  readWriteAnyDatabase

  Provides the same privileges as readWrite on all databases except local and config.

  userAdminAnyDatabase

  Provides the same access to user administration operations as userAdmin on all databases except local and config.

  dbAdminAnyDatabase

  Provides the same privileges as dbAdmin on all databases except local and config.

For mongodump, use the backup role.

  The admin database includes the
  following roles for backing up and restoring data:

  backup

  Provides minimal privileges needed for backing up data. This role provides sufficient privileges to use the MongoDB Cloud Manager backup
  agent, Ops Manager backup agent, or to use mongodump to back up an
  entire mongod instance.

Specifics about mongodump https://docs.mongodb.com/manual/reference/program/mongodump/ :

  Required Access

  To run mongodump against a MongoDB deployment that has access control
  enabled, you must have privileges that grant find action for each
  database to back up. The built-in backup role provides the required
  privileges to perform backup of any and all databases.


Does a SQL server unique constraint partially or fully fail a multi-row write? If some rows of a writing query fails a unique constraint, will only those writes be rejected, or will the entire query fail?

Every individual transaction (statement) in SQL Server is atomic, meaning it passes or fails as a unit.

If the 999,999th record fails on a constraint violation in a 1,000,000 row insert, all of the other rows get rolled back and the table is exactly as it was before the failed statement was attempted.

The same applies for updates and deletes, as well as multiple statements in an explicit transaction.  For instance, if I have an explicit transaction that does a DELETE followed by an INSERT and an UPDATE, if I encounter an issue in the UPDATE and issue a manual ROLLBACK all of those actions get undone.


SQL Server 2019 column store indexes - maintenance I have a clustered columned stored index on table used for logging - only inserts (but not bulk inserts).
The current table stats are:

3541 millions rows
6.6 GB reserved space

I see this morning the following operation via sp_whoisactive:
ALTER INDEX [...] ON [...].[...]
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

I use the following query to check how many rows per row_group_id we have:
SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    partitions.partition_number,
    dm_db_column_store_row_group_physical_stats.row_group_id,
    dm_db_column_store_row_group_physical_stats.total_rows,
    dm_db_column_store_row_group_physical_stats.deleted_rows,
    dm_db_column_store_row_group_physical_stats.state_desc,
    dm_db_column_store_row_group_physical_stats.trim_reason_desc
FROM sys.dm_db_column_store_row_group_physical_stats
INNER JOIN sys.indexes
ON indexes.index_id =
    dm_db_column_store_row_group_physical_stats.index_id
AND indexes.object_id =
    dm_db_column_store_row_group_physical_stats.object_id
INNER JOIN sys.tables
ON tables.object_id = indexes.object_id
INNER JOIN sys.partitions
ON partitions.partition_number =
    dm_db_column_store_row_group_physical_stats.partition_number
AND partitions.index_id = indexes.index_id
AND partitions.object_id = tables.object_id

and we 3383 row groups with 1048576 rows and few at end like this:

The issue is that we are using standard edition (on premise) and the rebuild operation is not performed online and cause a lot of blocking.
I have never seen such issue before. A few weeks ago we have upgraded from SQL Server 2016 SP1 to SQL Server 2019.
My questions are:

if only inserts are applied, should be the operation reorganize and be faster
if not, if we apply partitioning, for example on year basis as the table is used for logging, will the automation process only rebuild the data for the last partition

I have a clustered columned stored index on table used for logging - only inserts

if only inserts are applied, should be the operation reorganize and be faster

You should not even bother.  Reorganize for a columnstore does:

Physically removes rows from a rowgroup when 10% or more of the rows
have been logically deleted. The deleted bytes are reclaimed on the
physical media. For example, if a compressed row group of 1 million
rows has 100K rows deleted, SQL Server will remove the deleted rows
and recompress the rowgroup with 900k rows. It saves on the storage by
removing deleted rows.
Combines one or more compressed rowgroups to increase rows per
rowgroup up to the maximum of 1,048,576 rows. For example, if you bulk
import 5 batches of 102,400 rows you will get 5 compressed rowgroups.
If you run REORGANIZE, these rowgroups will get merged into 1
compressed rowgroup of size 512,000 rows. This assumes there were no
dictionary size or memory limitations.
For rowgroups in which 10% or more of the rows have been logically
deleted, the Database Engine tries to combine this rowgroup with one
or more rowgroups. For example, rowgroup 1 is compressed with 500,000
rows and rowgroup 21 is compressed with the maximum of 1,048,576 rows.
Rowgroup 21 has 60% of the rows deleted which leaves 409,830 rows. The
Database Engine favors combining these two rowgroups to compress a new
rowgroup that has 909,830 rows.

Considerations specific to reorganizing a columnstore index
So all it will do is combine the open row groups into a new compressed rowgroup.  Then the next time you insert anything you'll get new open rowgroups.  So there's no real benefit to REORGANIZE in your scenario.
As J.D. advises, you might partition this table so if you want to apply archival compression only to older partitions.  But your compression is already quite good.


Raising an Alert with THROW It seems to me that SQL THROW is missing a crucial feature that RAISERROR has, and that is the abilty to use WITH LOG so that an Alert can be fired when the error number is logged in the Application log. Is there a simple way to do this that is escaping me or do I still have to rely on the more cumbersome RAISERROR and sp_addmessage SP? I would like to be able to notify an Operator through an Alert using THROW in a CATCH block.

No, THROW is missing some RAISERROR functionality, including WITH LOG. I blogged about this back when SQL Server 2012 was still in beta, and no changes have been made since:

SQL Server v.Next (Denali) : Exploring THROW

However, you don't need to use sp_addmessage to send RAISERROR messages to the log, unless you need a custom message number outside of 50000:

DECLARE @s VARCHAR(32) = 'friend';
RAISERROR(N'What happened %s?', 19, 1, @s) WITH LOG;

Results:

Reading the guts of the question again, you probably do want to use sp_addmessage to achieve your ultimate goal. I shouldn't have tried to separate the logging on its own from that (50000 doesn't help much if you have other errors getting logged and want different alerts).

Example of putting the error number into the text for the message raised by RAISERROR:

BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  DECLARE @err INT = ERROR_NUMBER();
  RAISERROR(N'What happened %d?', 19, 1, @err) WITH LOG;
END CATCH


Filter first then join or join first then filter? Consider 5 tables with more than 1 billion records each.

A query needs to join them. And I know that less than 10% of their records will be require. Say they all have a Date dimension, and only data from current month is needed.

What should be faster:

1) Use a simple SELECT: join all tables, then filter (WHERE) each table's dimension for current month.

2) create 5 temp tables, filtering each source table for current month records, here we can also take the opportunity to select only required columns, then join these temp tables.

Extra possibility:

3) Maintain secondary tables, having only current month/year worth of data. These tables are maintained by same ETL that feeds main ones.

Build up the query and see.
The query optimizer is often smart enough to filter early.
SQL is logical - in the where does not mean it will process last.

Clearly you want indexes on join and filter.

When you get to 5 or more joins the optimizer will often get defensive and go into a loop join.
Do I have a citation - no.  It is an observation.

When it gets to 5 or more pulling the conditions into the join can help the optimizer.
Do I have a citation - no.  It is an observation.

select *
from tableA
join tableB
      on tableA.fkB = tableB.id
     and tableA.date1 >= @date1start
     and tableA.date1 <  @date1end
join tableC
      on tableA.fkC = tableC.id
     and tableC.filter1 = 'do me early'

Build it up one join at a time and see when it goes stupid.
Optimize one join at at time.

If you are going to materialize then put a pk on the #temp.
Start with where you think you are going to get the most bang.

OR condition in where / join are the most trouble and often lead to a loop join.
Those should be the first to materialize.

You can force hash join but that is a slippery slope that can go bad.

It seems kind of odd all tables have a date dimension, you would typically have some lookup type tables with more static type data.

If you don't need output from the table then a where exists may preform better.


help in connecting the sql server 2017 developer edition I have installed the sql server 2017 for non production environment in my PC.
It has installed successfully.
when connect using sqlcmd it was throwing an error login failed for login sa. it didn't have the asked for the creating user or anythings...
please find the command used to connect

sqlcmd -S servername -U sa

Update:

I can't even log in using windows authentication.

Update2:

I have tried to access sqlcmd from command prompt i got the following error.

  Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Named Pipes
  Provider: Could not open a connection to SQL Server [2]. . Sqlcmd:
  Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout
  expired. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A
  network-related or instance-specific error has occurred while
  establishing a connection to SQL Server. Server is not found or not
  accessible. Check if instance name is correct and if SQL Server is
  configured to allow remote connections. For more information see SQL
  Server Books Online..

It sounds like one of the following things is happening:

The sa account is disabled

either explicitly, or because you're in Windows Authentication mode

You don't know the password to the sa account

Since this is installed locally on your PC, you should actually be able to connect with sqlcmd without any parameters to use "Trusted Connection" to authenticate you:

sqlcmd

Once you're in, you should be able to check the authentication mode using an approach like this one:

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

If that returns "Windows Authentication," then you'll need to go through the process of switching to mixed mode outlined here: Change Server Authentication Mode

If the query above returned "Windows and SQL Server Authentication" then it's possible that you have just mistyped or forgotten the sa account password, or it's disabled for some other reason.  In that case, you can run this code (also included in the docs link above):

ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;
GO


How can you output UPDATE / INSERT query results when using MySQL -e,--execute? When using MySQL to run an UPDATE or INSERT, I get query result info:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

When using MySQL from the command line (eg: mysql -e "UPDATE ...") I get no output for successful queries.

How can I get the query result info to output when using the --execute argument?

Run with -vv, that will print the query being ran as well as the results you're looking for


Event between server and clients Is it possible to achieve that a client sends an event to the MySQL server and the server sends this event to all clients? How can clients get this event?

Sometimes a user sends some data to another user that use the same application by setting the second user_id in the working table.

I want to tell the second user immediately what happens.

There is no feature to do this but it can be done with a slightly hack method of making a sleeping connection and killing it when the event occurs.

The client submits a long running sleep query:

SELECT /* WAITING_FOR_EVENT */ SLEEP(9999);

The client will now sit waiting for the query to return.

Now to signal the event, you just kill the thread. This could be set off by another client, a trigger, a scheduled event, etc.

SELECT * FROM information_schema.processlist WHERE `INFO` LIKE '%WAITING_FOR_EVENT%';

This gives the process id that is running the sleep query. Then simply kill it with the KILL command.

The client will see it was interupted and now knows the event has been triggered.

You can have multiple listeners using this method but cannot pass any data back to the listening client, only signal an event has occurred.


In SQL Server Replication, at what point is it recommended that one separate the distributor? Is there a general rule of thumb for when it becomes good practice to assign distribution responsibilities to a separate server that is not also a subscriber?

e.g.

"At n publishers/publications with m subscribers/subscriptions it is recommended that distribution be handled on its own instance"

or

"At j total rows replicated it is recommended ..."

I assume that this would also be dependent on SQL Server version so I'll just say that publishers are varied (versions 2000, 2000, 2008, and 2008R2), current distributor (also a subscriber) is 2008, and subscribers are also varied (2000, 2005, and aforementioned 2008).

It totally depends on the workload and the hardware in question.  Having the distributor on another server means that part (possibly all) of the buffer pool will be getting used by the distribution database and not the actual workload that the server should be doing.  Then there's the CPU and memory needed by all the agents that you are running.


Select Column data, from sys.column info Either this is impossible, or I can't write a good internet search.

I have a query to get the table and column information for our database:

Select t.name [table], c.name [column]
From sys.tables t
Left join sys.columns c on t.object_id = c.object_id

What I'd like to do is add in something like

(Select AVG(LEN(c.name)) from t.name) [AVG LEN]

I'd obviously build in some checks to only run on varchar for Len, and regular AVG for numerics, but that part would be easy. It is getting SQL to actually treat the names correctly that I'm struggling with.

You can use dynamic SQL, a cursor and a global temporary table to collect this information. Note that performance won't be great (cursors are generally a bad idea) but if you only need to run this as a once-off then it should be fine.

CREATE TABLE ##GlobalTemp
(
  TableName VARCHAR(255),
  ColumnName VARCHAR(255),
  Min INT,
  Max INT,
  Avg INT
)

DECLARE @SqlCmd NVARCHAR(MAX),
    @TableName NVARCHAR(255),
    @ColumnName NVARCHAR(255),
    @Type BIT -- 0 = integer, 1 = character

DECLARE C1 CURSOR FOR
    SELECT QUOTENAME(s.[name]) + '.' + QUOTENAME(t.[name]), c.[name],
        CASE
            WHEN ty.[name] IN ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar') THEN 1
            WHEN ty.[name] IN ('tinyint', 'smallint', 'int', 'real', 'money', 'float', 'bit', 'bit', 'decimal', 'numeric', 'smallmoney', 'bigint') THEN 0
        END
    FROM sys.columns c
    INNER JOIN sys.tables t ON t.object_id = c.object_id
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id

OPEN C1

FETCH NEXT FROM C1 INTO @TableName, @ColumnName, @Type

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SqlCmd = 'INSERT INTO ##GlobalTemp (TableName, ColumnName, Min, Max, Avg)
    SELECT ''' + @TableName + ''', ''' + @ColumnName + ''', ' +
        CASE @Type
            WHEN 0 THEN 'MIN(' + @ColumnName + '), MAX(' + @ColumnName + '), AVG(' + @ColumnName + ')'
            WHEN 0 THEN 'MIN(LEN(' + @ColumnName + ')), MAX(LEN(' + @ColumnName + ')), AVG(LEN(' + @ColumnName + '))'
        END + '
    FROM ' + @TableName + ';'

    PRINT @SqlCmd
    EXEC sp_executesql @SqlCmd

    FETCH NEXT FROM C1 INTO @TableName, @ColumnName, @Type
END

CLOSE C1
DEALLOCATE C1

SELECT *
FROM ##GlobalTemp

DROP TABLE ##GlobalTemp


How do I navigate PostgreSQL documentation to find a list of all functions that work on a specific type? Let's say I want to find a list of functions that can work on jsonb, what is the most effective way to do this from the terminal without using google? Assuming only access to

man pages
psql
postgresql tables

There are a few ways, but let's try and address all three of them..

man pages

There are no man pages that will address these questions. There is a lot of PostgreSQL documentation but currently there is nothing like that nor perldoc for PostgreSQL. The docs covering this are only distributed as HTML -- which you can still view with a terminal browser (like elinks, lynx, w3m etc). Most distributions package this up separately, in Ubuntu you'd do this.

sudo apt-get install postgresql-doc
elinks /usr/share/doc/postgres*/html/index.html

psql

To use PSQL for this, you'll need to use \dfS. You can see a listing of all the psql commands with \?. After you do \dfS just search for the type. In the event \dfS is using a viewer that wraps on terminal width, you can tell it not to wrap by setting using `\setenv, most pagers accept arguments by their environmental variable so to use LESS without wrapping you can use

\setenv PAGER less
\setenv LESS -S

All of this is buried in the man page for psql. Most of the output of \? the in-shell psql help is better explained in that man page too, for instance this is what \? shows

\df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions

And, this is what man psql shows,

  Lists functions, together with their arguments, return types, and function types, which are classified as “agg” (aggregate), “normal”, “trigger”, or “window”. To display only functions of specific type(s), add the corresponding letters a, n, t, or w to the command. If pattern is specified, only functions whose names match the pattern are shown. By default, only user-created objects are shown; supply a pattern or the S modifier to include system objects. If the form \df+ is used, additional information about each function is shown, including security classification, volatility, owner, language, source code and description.

  Tip To look up functions taking arguments or returning values of a specific type, use your pager's search capability to scroll through the \df output.

PostgreSQL catalog

First you need to have an idea of what to query. That's the complex party. In the above, we know that we're using \dfS, so we can get a starting point with psql like this:

psql -E -c"\dfS" -d myDB | less -S

This will start off with with the query,

********* QUERY **********
SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;
**************************

Now, we now we p.proname as "Name", and pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types" and we can put that together to get a simplified version of \dfS which shows just what you want,

SELECT p.proname AS "Name",
  pg_get_function_arguments(p.oid) AS "Argument data types"
FROM pg_proc AS p
WHERE pg_get_function_arguments(p.oid) like '%jsonb%';

A little more research into pg_proc and we can see

  proargtypes - oidvector - pg_type.oid - An array with the data types of the function arguments. This includes only input arguments (including INOUT and VARIADIC arguments), and thus represents the call signature of the function.

This would allow us to do a very specific query to pg_type over oid

SELECT proname AS "Name"
FROM pg_proc
  INNER JOIN pg_type ON ( pg_type.oid = ANY(proargtypes) )
WHERE typname = 'jsonb';

And, that gets us exactly what we want.

information_schema

While maybe not applicable here, generally speaking you can find some information in the information_schema. This generic interface doesn't support everything, it's slow, and psql doesn't use it internally. I did not see this information in information_schema.routines at all.

Some of this answer was shamelessly inspired from this question but aimed to be a more friendly resource.


How to stress test a SQL server? I've been tasked to stress test our MSSQL Server and MySQL Server. I would like to know if there are any tools or scripts which I could use on our current systems and the new system to compare performance?

I would like to measure read / write to disk and processor performance. Anything else that may be useful would be great as well.

Thanks!

Quest offers a free version of their Benchmark Factory that works for Oracle, SQL Server, and MySQL:

http://www.quest.com/benchmark-factory/

I recorded a video on getting started with it back when I worked for Quest:

http://www.toadworld.com/Videos/tabid/831/TID/559/cid/161/Default.aspx

You can call your own stored procs, replay traces, run TPC benchmark scripts, and more.  It's not exactly easy to use, though - thus the video.


Do I need to redesign my Database and get rid of foreign keys for successful replication? I plan to set up MariaDB replication either in master-master or master-slave mode. The main issue I have now are worries after reading this documentation.

We use foreign keys a LOT and we also use a lot of cascading effects, especially for ON DELETE. I really do not understand why this would be an issue if both slave and master have the same table definitions, with the same cascading ON DELETE triggers.

Can I safely assume that if both DB servers have exact same storage engine (InnoDB) and MariaDB version, then foreign keys and related DELETE statements will work as expected? Or should we remove all of these CASCADEs and rework software to work without them?

Thanks!

This looks like a non-issue, because, obviously, you will be using the same table definitions, including engine, on both servers. The example in the MariaDB Knowledge Base article you linked to has different storage engines: the slave server doesn't have InnoDB, so it defaults to MyISAM, which doesn't support foreign keys. MyISAM does tolerate foreign key constraints in the CREATE TABLE statements, but it silently ignores them, so any defined cascading deletes will not happen.


How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted? MySQL 5.1.x | InnoDB | Windows

My mysql data directory is starting to fill up with bin logs.

I currently have the following settings configured in my windows mysql server:

[mysqld]
log-bin
server-id=1
binlog-do-db=foodb1
binlog-do-db=foodb2
expire_logs_days=25

I'm planning to change the expire_logs_days setting to expire_logs_days=10 and bounce the mysql service.  How soon after making this change can I expect the old bin logs to get clear out.

Is this only done as part of a nightly scheduled task? Or should this be immediate?

It will be handled immediately on mysql startup.

You do not have to wait for a mysql restart.

First, set expire_logs_days to be 10 in /etc/my.cnf

[mysqld]
log-bin
server-id=1
binlog-do-db=foodb1
binlog-do-db=foodb2
expire_logs_days=10

Next, log in to mysql and run this

PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day);

Note the query's date and time

mysql> select date(now()) + interval 0 second - interval 10 day;
+---------------------------------------------------+
| date(now()) + interval 0 second - interval 10 day |
+---------------------------------------------------+
| 2012-12-11 00:00:00                               |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Thus, the PURGE BINARY LOGS command will delete all binlogs whose datetime stamp predates 2012-12-11 00:00:00.

Finally, run this command

SET GLOBAL expire_logs_days = 10;

That's it. No restart needed for installing expire_logs_days.

As for the replication parameters, yes restart is required.


Should the database service be restarted after performing a restore? Given:

a database backup of >100G uncompressed disk space
SQL Server 2012 Instance
Dev/Test system host (Non-production)

Is it better to restart the MS SQL Server, Agent, etc services after restoring the DB? Why or why not?

There is absolutely no reason to.

Once the database is restored, if over top a previous database or as a new one, there will be no information in cache (or very little). If you were to restart the services, the only thing accomplished is that SQL Server has to run recovery on all of the databases in the instance and re-acquire memory. This would actually cause a detrimental effect, assuming this isn't express edition and the buffer pool is actually being used (i.e. there is more than 1 databases on the system). Even if it were, there is still no good reason to do so.