Comparing previous/next record to calculate an average speed I have a simple table ina MySQL db recording the speed in m/s of a production machine every n seconds.

ID    SPEED    TIME
----------------------------------
1     2.5      2017-01-27 08:23:03
2     1.9      2017-01-27 08:22:02
3     2.0      2017-01-27 08:21:02
4     2.1      2017-01-27 08:20:01
5     2.3      2017-01-27 08:19:03

ID is an auto increment field.

Due to some lacks in signal transmission, the time interval between two rows is not always the same.
What I would do is:

Calculate the difference of time between two rows (assuming that in
that timeframe speed is constant);
Multiply the calculated timeframe for the speed (so I get a distance);
Sum the results (I get the total distance);
Calculate the difference in seconds between the first and last records selected (I get the total time);
Divide total distance for total time getting my average speed.

My problem is to understand how to get previous and next record and how to do that iteratively.

This is now in MySQL, but will be moved to Postgres soon.

Getting the previous row is very easy in Postgres:

select id,
       speed,
       "time",
       extract(epoch from lag("time") over (order by "time") - "time") as diff_seconds,
from the_table
order by "time";

The whole calculation would be:

select extract(epoch from max("time") - min("time")) as total_seconds,
       sum(diff_distance) / extract(epoch from max("time") - min("time")) as avg_speed
from (
  select id,
         speed,
         "time",
         extract(epoch from lag("time") over (order by "time") - "time") as diff_seconds,
         speed * extract(epoch from lag("time") over (order by "time") - "time") as diff_distance
  from the_table
  order by "time"
) t;

Subtracting a timestamp from a timestamp yields an interval. Using extract(epoch ...) converts the interval to the total number of seconds in that interval.

The column diff_distance isn't really necessary, I just added that for debugging purposes.

This however is currently not possible in MySQL.


Microsoft SQL Server MERGE deadlock issue I am using Microsoft SQL Server version:

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016 23:23:58   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 6.3  (Build 14393: ) (Hypervisor)

I have a stored procedure using a MERGE statement that is named [UpdateReports]

ReportType is a user-defined type like below:

When I run the UpdateReports procedure, it works good in normal situations.  But when it is executed while a full backup is running on the database, Update Statistic (I think many reads of DB), this UpdateReports procedure deadlocks and it takes about 10 minutes to complete.

Is it because I am using HOLDLOCK ?

I searched this issue and many people saying MERGE is not stable....

Could you please let me know if there is any good alternative instead of MERGE?

If I can use IF/ELSE, UPDATE/INSERT instead of MERGE.... I am worried about concurrency

I think what you are talking about is not "deadlock issue", it's not even blocking issue.

Full backup locks nothing, but when it happens you have intesive I/O operations, it's just increasing server load. Full backup should be done with the less possible activity on the server, but your activity is not even readonly, it's data modification. If your db is in simple recovery model, making merge during full backup means fully logging, so you have more writelog on inserts.

Update statistics really can deadlock with merge when it reads indexes in opposite order, I saw the cases. But it's surely not your case:

  The system task Deadlock Monitor wakes up every five seconds and
  checks if there  are any deadlocks in the system. When a deadlock is
  detected, SQL Server rolls back one  of the transactions with the
  error 1205. That releases all locks held in that transaction  and
  allows the other sessions to continue.

Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners
By Dmitri Korotkevitch

So when you say that your task completes in 10 minutes it's definitely not a case of a deadlock: even if it could be UPDATE STATISTICS to be rollbacked, it happened in some seconds, then it was killed and your merge continued as usual.

To find out the exact cause of slowness you should monitor sys.dm_os_waiting_tasks during the execution to detect for what your merge is waiting for.


How to duplicate the database in postgresql? I need to duplicate the existing database including its schema and structure to another new database. I need this in shell command environment and not in pgadmin. Please kindly help me.

nohup pg_dump exampledb > example-01.sql
createdb -O postgres exampledbclone_01

my user is "postgres"

nohup psql exampledbclone_01 < example-01.sql

$ pg_dump mydb > db.sql
$ psql -d newdb -f db.sql

If you want to duplicate it within the same PostgreSQL install and you don't have actively connected users there's a handy shortcut:

CREATE DATABASE my_new_database TEMPLATE my_old_database;

or from the shell

createdb -T my_old_database my_new_database;

Otherwise you'll need to use pg_dump, createdb and pg_restore, e.g.

pg_dump -Fc -f olddb.pgdump -d olddb &&\
createdb newdb &&\
pg_restore -d newdb olddb.pgdump

If you're using nohup so the command doesn't die if you lose your ssh session, consider using screen instead.


Where/how do I save extra data per column? Next to the actual data I have in a table, I need to save some data about those fields. I'm hesitant to call it metadata, because while it is exactly that, metadata has a specific meaning within databases sometimes, and this is not exactly what I mean.

Example: I have a table, and for each field I want to know some sort of state. Lets say 'unchecked', 'ok', 'faulty' etc. I could just double the columns, add a new 'status-field' per column, but that feels wrong.
A different example would be to safe how the data currently came to be, for instance calculated, automatically retrieved of manually entered.

Some extra info: I might want to save all changed data later: a sort of history table. While this might be a completely different problem, I suppose that it might actually help in this case: if I for instance have a key-value storage for the history, I could maybe add the metadata there?

Is there any default solution to add this sort of data to a database? The final goal is to add these fields to an existing model, one that contains several tables/relations. I'm not even sure if a one-solution-per-table, or a solution-for-all is possible?

In the simple case of "ok/not ok" statusses, the logical query belonging to this would be "get me all records that are ok (meaning: with all rows begin ok). With all the statusses on a distinct location one could do a count on those. When adding an extra field-per-column, I can't seem to think of quicker query then just adding a whole bunch of WHERE x_status=ok AND y_status=ok AND... etc.  The difference in information-type is lost, so there is a hidden property that I feel might need it's own... place. The fact that it is data-about-data shouldn't be lost, but I'm not sure how exactly.

I have a table, and for each field I want to know some sort of state. Lets say 'unchecked', 'ok', 'faulty' etc. I could just double the columns, add a new 'status-field' per column, but that feels wrong.

To me, this feels right ;-) In order to prevent typos, I think that this could (and should) be implemented as a foreign key to another table with just the possible states.

  A different example would be to safe how the data currently came to be, for instance calculated, automatically retrieved of manually entered.

If, necessary, I'd again use foreign keys to other tables for this. These tables would cover the available methods or instruments.

Update 1
Quite often, the number of different states (unchecked, faulty, ok, etc.) is rather small. A foreign key of the type TINYINT would probably suffice and minimizes the memory overhead.


Select a CSV string as multiple columns I'm using SQL Server 2014 and I have a table with one column containing a CSV string:

110,200,310,130,null

The output from the table looks like this:

I want to select the second column as multiple columns, putting each item of the CSV string in a separate column, like this:

So I created a function for splitting a string:

create FUNCTION [dbo].[fn_splitstring]
(
    @List nvarchar(2000),
    @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
    Id int identity(1,1),
    Value nvarchar(100)
)
AS
BEGIN
    while (Charindex(@SplitOn,@List)>0)
    begin
        insert into @RtnValue (value)
        select
            Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

        set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
    end
    insert Into @RtnValue (Value)
    select Value = ltrim(rtrim(@List))

    return
END

I would like to use it similar to this:

select Val , (select value from tvf_split_string(cchar1,','))  from table1

But the above code obviously won't work, because the function will return more than one row causing the subquery to return more than one value and breaking the code.

I can use something like:

select Val ,
(select value from tvf_split_string(cchar1,',') order by id offset 0 rows fetch next 1 rows only ) as col1,
(select value from tvf_split_string(cchar1,',') order by id offset 1 rows fetch next 1 rows only ) as col2,
................
 from table1

but I don't think it's a good approach.

What is the correct way to do it?

A Word on the Performance of Different Types of Functions

Generally speaking, Scalar Functions and mTVFs (Multi-Statement Table Valued Functions) are a bit of a built in performance problem.  It's better, if you can, to use iTVFs (Inline Table Valued Functions) because their code is actually included in the execution plan (much like a VIEW but parameterized) instead of being executed separately.  Rather than replicate an entire article here, please see the following for some proof even when an iTVF is used as an iSF (Inline Scalar Function).  In many cases, Scalar functions can be 7 times slower than iTVFs even when the code is identical.  Here's the link, which you can now get to without having to sign up to be a member.  To summarize, if your function has the word "BEGIN" in it, it's NOT going to be a high performance iTVF.

How to Make Scalar UDFs Run Faster (SQL Spackle)

Test Data for SQL Server 2008 or Later:

Shifting gears to the original problem posted by @Reza and borrowing heavily from the code that @stefan provided to generate some sample rows of data, here's the test data we'll use.  I do this in a Temp Table so that we can easily drop the test table without having to worry about accidently dropping a real table.

--===== If the test table exists, drop it to make reruns in SSMS eaiser
     IF OBJECT_ID('tempdb..#CSV','U') IS NOT NULL
   DROP TABLE #CSV
;
--===== Create the test table.
 CREATE TABLE #CSV --Using a Temp Table just for demo purposes
        (
         ID         INT IDENTITY(1,1) --or whatever your PK is
        ,AString    VARCHAR(8000)
        )
        ;
--===== Insert some test data
 INSERT INTO #CSV
        (AString)
 SELECT AString
   FROM (
        VALUES   ('123,456,88789,null,null')
                ,('123,456,99789,1234,null')
                ,('123,456,00789,1234,null')
                ,('1,2222,77789,null,null')
                ,('11,222,88789,null,')
                ,('111,22,99789,,')
                ,('1111,2,00789,oooo,null')
        ) v (AString)
;

Test Data for SQL Server 2005 or Later:

If you're still using SQL Server 2005, don't despair.  All of the code will still work there.  We just need to change the way we generate the test data because the VALUES Clause couldn't handle the way we generated the data above until 2008.  Instead, you need to use a series of SELECT/UNION ALL statements.  There's virtually no performance difference between the two if you need a larger volume of data and the SELECT/UNION ALL method still works just fine through 2016.

Here's the test table generation code modified to work for 2005.

--===== If the test table exists, drop it to make reruns in SSMS eaiser
     IF OBJECT_ID('tempdb..#CSV','U') IS NOT NULL
   DROP TABLE #CSV
;
--===== Create the test table.
 CREATE TABLE #CSV --Using a Temp Table just for demo purposes
        (
         ID         INT IDENTITY(1,1) --or whatever your PK is
        ,AString    VARCHAR(8000)
        )
        ;
--===== Insert some test data
 INSERT INTO #CSV
        (AString)
 SELECT AString
   FROM (
         SELECT '123,456,88789,null,null' UNION ALL
         SELECT '123,456,99789,1234,null' UNION ALL
         SELECT '123,456,00789,1234,null' UNION ALL
         SELECT '1,2222,77789,null,null'  UNION ALL
         SELECT '11,222,88789,null,'      UNION ALL
         SELECT '111,22,99789,,'          UNION ALL
         SELECT '1111,2,00789,oooo,null'
        ) v (AString)
;

Picking a Splitter

First of all, SQL Server isn't the greatest tool to use for string manipulations.  It's been proven many times that a properly written SQLCLR will smoke just about any attempt at string splitting compared to pure T-SQL solutions.  The problems associated with SQLCLR solutions are 1) finding one that is properly written (properly returns the expected result set) and 2) it's sometimes difficult to convince the resident DBAs to allow SQLCLR to be used.  If you do find one that's properly written to return the expected results, it's definitely worth spending the time trying to convince the DBA because it will usually handle MAX data-types and cares little if you pass it VARCHAR or NVARCHAR data not to mention being faster than any T-SQL solution I've ever seen.

If you don't have to worry about using NVARCHAR and you don't have to worry about multi-character delimiters (or can change the delimiter "on the way in") and you don't have to worry about MAX datatypes (limited to 8K strings), then then DelimitedSplit8K function may be for you.  You can find the article for it along with the proper "high cardinality" testing for it at the following URL.

Tally OH! An Improved SQL 8K “CSV Splitter” Function

I'll also tell you that the high cardinality test data generation code in that article broke when SQL Server 2012 came out because of changes to how XML can be used to concatenate.  Wayne Sheffield did some separate testing to compare it to the new splitter function in 2016 and made a repair to the test data generation code at the following article.  Note that there are also 3 other splitters that are available in the article, 2 of which are only available in 2016.  He also tests against an SQLCLR and lists some of the functional differences (shortcomings in some cases) of each splitter.

Splitting Strings in SQL Server 2016

There's also an article by Eirikur Eiriksson that not only explores a serious performance improvement but also explores splitting "True CSVs" that can be included delimiters between "text qualifiers" (although at considerable expense of performance).  Here's the link to that article.

Reaping the benefits of the Window functions in T-SQL

All that, notwithstanding, even he demonstrates that an SQLCLR is a whole lot faster than anything you can do using only T-SQL.  I recommend that method instead of any T-SQL method but DO extensive functionality testing to ensure it's going to return what you expect in some of the "odd" cases.

Solving the Posted Problem

Ok.  Sorry for the long winded diversion about splitters.  Let's get back to the original problem.

We need to do two things:

Split the CSVs in such a fashion where we know which row each split
out element came from.
Reassemble the split out elements into separate columns all in the
same row for each row we split.

First, we need to create a splitter function.  Here's a modification of DelimitedSplit8K that I'm working on (well, not lately ;-) ) that will still support SQL Server 2005.  The big difference is using a binary collation for the extra performance.  This rendition isn't yet fully tested (I've personally been using it since Sep 2013) but, considering the simplicity of the changes and the fact that none of them actually change the functionality, I believe that you'll find it satisfactory for your needs.

CREATE FUNCTION [dbo].[DelimitedSplit8K]
/**********************************************************************************************************************
 Purpose:
 Given a string containing multiple elements separated by a single character delimiter and that single character
 delimiter, this function will split the string and return a table of the single elements (Item) and the element
 position within the string (ItemNumber).

 Notes:
 1. Performance of this function approaches that of a CLR.
 2. Note that this code implicitly converts NVARCHAR to VARCHAR and that conversion may NOT be faithful.

 Revision History:
 Note that this code is a modification of a well proven function created as a community effort and initially documented
 at the following URL (http://www.sqlservercentral.com/articles/Tally+Table/72993/). This code is still undergoing
 tests. Although every care has certainly been taken to ensure its accuracy, you are reminded to do your own tests to
 ensure that this function is suitable for whatever application you might use it for.
 --Jeff Moden, 01 Sep 2013
**********************************************************************************************************************/
--===== Define I/O parameters
        (@pString VARCHAR(8000) , @pDelimiter CHAR(1)) --DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000).
  WITH E1(N) AS (--==== Itzik Ben-Gan style of a cCTE (Cascading CTE) and
                     -- should not be confused with a much slower rCTE (Recursive CTE).
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                                      --10E+1 or 10 rows
       E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d), --10E+4 or 10,000 rows max
 cteTally(N) AS ( --=== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS ( --=== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT CASE WHEN SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN THEN t.N+1 END --added short circuit for casting
                   FROM cteTally t
                  WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter COLLATE Latin1_General_BIN
                ),
cteLen(N1,L1)AS ( --=== Return start position and length (for use in substring).
                     -- The ISNULL/NULLIF combo handles the length for the final of only element.
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter ,@pString COLLATE Latin1_General_BIN,s.N1) ,0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO

Once that bad boy is in place, then problems like those in the original post become trivial to solve using either a PIVOT or a CROSS TAB.  I prefer the rather ancient CROSS TAB method because it's frequently faster (sometimes significantly) than an actual PIVOT.  Please see the following article on testing done there (is pretty old and should probably be updated for performance measurements, though it does explain how to use both methods).

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns

Here's the code that uses the function above and a CROSS TAB to solve the original problem posted on this thread.

--===== Do the split for each row and repivot to columns using a
     -- high performance CROSS TAB. It uses the function only once
     -- for each row, which is another advantage iTVFs have over
     -- Scalare Functions.
 SELECT  csv.ID
        ,Col1 = MAX(CASE WHEN ca.ItemNumber = 1 THEN Item ELSE '' END)
        ,Col2 = MAX(CASE WHEN ca.ItemNumber = 2 THEN Item ELSE '' END)
        ,Col3 = MAX(CASE WHEN ca.ItemNumber = 3 THEN Item ELSE '' END)
        ,Col4 = MAX(CASE WHEN ca.ItemNumber = 4 THEN Item ELSE '' END)
        ,Col5 = MAX(CASE WHEN ca.ItemNumber = 5 THEN Item ELSE '' END)
   FROM #CSV csv
  CROSS APPLY dbo.DelimitedSplit8K(csv.AString,',') ca
  GROUP BY csv.ID
;
GO

Here's the result set from the code above using the given test data.

If you have any questions or need clarification on any of this, please don't hesitate to ask.


MySQL incorrectly saying serverID non unique when adding an instance to the InnoDB cluster I Get the following error when adding the second instance to a MYSQL Innodb Cluster

ERROR: Cannot add instance 'XXX' to the cluster because it has the same server ID of a member of the cluster. Please change the server ID of the instance to add: all members must have a unique server ID.
Cluster.addInstance: Access denied for user 'XXX'@'XXX' (using password: YES) (RuntimeError)

However if I check the two serverid's I can confirm that they are different

Server 1

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

Server 2

show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

Please advise?

Did you restart the server after changing the server-id? Despite server-id being dynamic, there is the following caveat on the manual:

If the server ID is set to 0, binary logging takes place, but a master
with a server ID of 0 refuses any connections from slaves, and a slave
with a server ID of 0 refuses to connect to a master. Note that
although you can change the server ID dynamically to a nonzero value,
doing so does not enable replication to start immediately. You must
change the server ID and then restart the server to initialize the
replication slave.

I am giving a wild guess that may be your issue, because it is a very common problem that people suffer once when setting up replication.
Edit: Re "Interestingly mysql is ignoring the server_id in the my.cnf file and choosing its own."
Then you have a configuration problem in which your server id is not properly read, please use --print-defaults to debug which files and options are being applied live, and check the paths for correct permissions, file existence and avoid duplicate config definitions/unintended includes.


Select single id from NULL We are having a database full with set ID's. To fill the database, we have to select one single ID every time. This may only be an ID where the rest of the values are set with NULL (empty).
How to do this?

SELECT `id` FROM `ids` WHERE `datetime` IS NULL

returns all empty ID's. I just have to select one of them at random.

In SQL Server, you can use TOP 1 and order by newid() to get a random row returned. As in:

SELECT TOP 1 id FROM ids WHERE datetime IS NULL ORDER by newid()


Azure Virtual Machine - local temp storage (D: drive) - how much IOPS it can handle? Virtual machine size DS3 (under older generation sizes)
Azure Portal (when deploying VM) shows that DS3 supports up to 16 data disks / up to 12800 IOPS - thats fine,
But what I am interested in is it's local/temp storage 28 GB D: drive
Documentation shows that this local/temp drive is SSD, but what I can't seem to find is information on how many IOPS this drive can handle ?
More specifically, if my TempDB has requirement of IOPS up to 1900, can D: drive on DS3 VM handle that requirement ?
From this source https://docs.microsoft.com/en-us/azure/virtual-machines/sizes-previous-gen I can guess that D: is 3200 IOPS but not sure I understand it correctly...

For VM Series with premium storage a portion of the local SSD is reserved for caching of premium disks.  That's why the D3 has 200GB of Temp Storage, but the DS3 has only 28GB of available Temp storage, with 172GB reserved for disk caching.
The IOPS are similiarly shared between the Temp storage (D:) and the disk cache.  The total IOPS is 16,000, but your effective IOPS and throughput will depend on the utilization of the disk cache.  If you turn off read caching on your data disks, or they are lightly-utilized, you'll have more IOPS available for temp storage.
That's why caching and temp storage are together in the grid:
Max cached and temp storage throughput:
16,000 IOPS
128 MBps
172GB cache size
28GB temp storage


Replica Set in MongoDB by using IPs instead of domains? I want to use MongoDB in replica-set mode over 2 servers by IPs.But when I deployed the secondary one on other server, I faced the issues about connecting between 2 instance. For example, the primary located on server 192.168.1.1:27018 with hostname COM1 and the second located on server 192.168.1.2:27018 with hostname COM2. Despite of configuring like this

net:
    bindIp: 0.0.0.0, COM1|192.168.1.1, COM2|192.168.1.2
    port: 27018

But in MongoDB's log, it responses that cant hear from COM1:27018 or can find COM2:27018, I don't know how to map these addresses with these IPs. Please help me, thanks!

From your primary machine run the following command

mongo --host COM2 --port 27018

You should be able to connect to the MongoDB running in the secondary node using the above command. If it fails to connect, check the following configuration settings

Check the bindIp settings of the secondary node configuration file, it should allow connection from 192.168.1.1:27018.
Check the port number 27018 is accepting inbound connections in the secondary node. If not add an entry in IP Table.
Check if secondary nodes hosts file is updated with primary machines DNS name
and IP Address.

Follow the same for Primary node as well and try to connect to each machine from other machine using the first command. If everything working fine you should be able to set up your replica set.


Running two MySQL servers that use the same data directory The situation

I have two MySQL instances, deployed on two different remote machines.
Both MySQL instances access the same data directory, which is stored on a persistent volume (this volume is provided by Gluster, but that's not really relevant, I'm only saying this for the sake of completeness).

I'm aware that it is strongly discouraged to run two MySQL servers using the same data directory, but it's not an impossible thing to do, as long as one takes the right precautions. In order to do this, I followed the steps suggested by the official documentation ( https://dev.mysql.com/doc/refman/5.6/en/multiple-data-directories.html ), more specifically in the paragraph named Warning. To sum it up:

I'm using MySQL 5.6.
I'm using MyISAM as default engine, as required.
I specified log file names that are unique to each server.
One server is going to be read-write, while the other server is going to be read-only.

So, I set everything up, I imported a test database and everything worked flawlessly. Both servers could read data from the same data directory.

The problem

Now comes the problem. As soon as one server performs a write (by inserting a row into a table), the other server marks the table as corrupted for itself.
After looking into the problem for a while, I found out why this happened. Basically each server holds its own metadata describing each table. So, as soon as the first server updated a table, it also updated its own metadata, but it didn't update the metadata of the second server. As the second server checked the table, it noticed a mismatch between the current number of rows in the table and the number of rows it had previously saved in its metadata, therefore it marked the table as corrupted.
After some research, it turns out that the metadata is saved inside a default database named information_schema. At first, I thought of making the two server share the same information_schema, but this was nowhere to be found. As I later discovered, it's saved inside the memory of the program and there's no way you can manually update or access it.

The questions

Since the documentation tells you exactly what precautions to take, I was expecting this to actually work...or else, what's the point of that guide in the official documentation? If this couldn't be done, why would it tell you how to do it in the first place?
Most importantly, do you know how to make this work, using MySQL?
If not, do you know of any other DBMSs that allow you to obtain this result (that is, running two servers that use the same data directory)?

Just keep in mind that I don't want to set up master-slave replication.
All other suggestions are welcome, thanks everyone for the help.

EDIT: I ended up not doing it, as everyone discouraged me from doing it. Thanks everyone for your input!

Since the documentation tells you exactly what precautions to take, I was expecting this to actually work...or else, what's the point of that guide in the official documentation? If this couldn't be done, why would it tell you how to do it in the first place?
  Most importantly, do you know how to make this work, using MySQL?

You said something that might have a bearing on the first two question

  Both MySQL instances access the same data directory, which is stored on a persistent volume (this volume is provided by Gluster, but that's not really relevant, I'm only saying this for the sake of completeness).

Interestingly, someone has claimed to have pulled this off.

On Sep 24, 2010 (nearly either years ago), Richard Holloway answered a question he himself posted in Server Fault : Can I run mysqld on top of glusterfs?

Quoting Richard Holloway's answer

Disable the query cache (Set query_cache_type=0, query_cache_size=0)
Switch on external locking

Richard cites MySQL 5.1 Doc on external locking
Michael - sqlbot just recommended 5.6 Doc in his comment.

disable delays to writing.

This is a very daring answer ( Godspeed, Spiderman !!! )

Just to throw in my 2 cents

Do not use GlusterFS in async mode !!!
Configure skip-innodb on both mysqld instances

  If not, do you know of any other DBMSs that allow you to obtain this result (that is, running two servers that use the same data directory)?

The answer is Oracle RAC. It is designed to operate multiple Oracle instances sharing only one set of datafiles. Each instance has its own log buffer. Oh yeah, you will need boat loads of money (Oracle RAC is not open source).


How to merge two databases (each with different updates) on the same server I have an almost regular WordPress setup including a mySQL database (Server A). On March 1st 2018 this installation and mySQL database was copied to a new server (Server B). On the new server (B) a lot of things got improved and a new domain name was assigned. Also the prefix of the database has changed.

Between March 1st and now this database (B) did not get any user registrations, however as said above it did get several updates and improvements for pages and general behaviour.

I have successfully used phpmyadmin to export the database from the current installation (A) again (on May 5th) and import it into the new server (B) as a second database.

What should happen now, is that the latest changes of (B) [March 1st + improvements etc.] should be merged together with the latest changes of (A) [March 1st + user registrations and user activities that happend on the current page between March 1st and May 5th].

Can I merge the two databases as a whole (with one command), if the tables are the same (I have not really compared each of the tables and it's columns, but as I have not changed any plugins and all the table names are identical - no table has been added or deleted - I guess I can say the structure is identical?)?
Is there a way of using phpmyadmin to do that?
Should I be using "REPLACE" or "INSERT" or "INSERT ... ON DUPLICATE KEY UPDATE" or something completely different? I do not understand what would be correct here.
Once I have managed to mergen the data, what is a good way of updating all URL's to the new domain? In the new installation both databases (A) & (B) still have the wrong URL's.

Many thanks for your help.

On the new server (B) I'm running:

Server: Localhost via UNIX socket
Server-Typ: MySQL
Server-Version:  5.6.39 - MySQL Community Server
(GPL)
Protokoll-Version: 10
nginx/1.12.2
Datenbank-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
PHP-Erweiterung: mysqli curl mbstring
PHP-Version: 7.1.13

(Disclaimer: I'm not familiar with WordPress and I haven't used phpmyadmin very much.)

If there is new data in each database since the "split", i.e. each database has some unique new data not found in the other one, then I don't think there is an easy path for merging them just using standard MySQL commands or tools. There will be records in one database with the same primary key values as in the other database, but with different data in the other columns in those records. If there aren't a lot of such records, then you may be able to merge this "manually", record by record, but it will likely be labour intensive and you need to know what you're doing. E.g. when you give a record a new primary key value to avoid a collision with the other database, you also need to make sure to update every foreign key that refers to it.

That said, there are special WordPress plugins for exporting and importing WP databases, and I'd be surprised if these didn't allow you to merge two databases. See e.g. wp-exporter and wordpress-importer. But that is probably outside of the scope for this site. See also this question on stackoverflow: How do I merge two Wordpress SQL databases in phpmyadmin?


What is the best way to create Primary Key on a large table in Postgresql 9.5? I have to create a primary key on a large table (~100Million records) in Postgtres database. What is the best and fastest way to create pkey? This column is a sequence column and i don't want to lock the table because this is highly transactional database.

You can create a unique index with the option concurrently which will allow read and write access to the table while the index is created. However, building an index concurrently will take much longer then adding the index without that option.

create unique index concurrently unique_id on the_big_table (id);

Once the index is created, you can use that as a primary key:

alter table the_big_table
   add primary key using index unique_id;

That will only lock the the table for a very short time.


Creating an array from multiple columns without NULL elements I'm trying to build a query to aggregate together multiple columns in a legacy table stored in a similar structure as below:

CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
  (1,'Router','Networking','Sale',NULL),
  (2,NULL,'Router','Networking','Sale'),
  (3,NULL,NULL,'Networking','Sale'),
  (4,NULL,NULL,NULL,NULL)
) AS t(id,tag_1,tag_2,tag_3,tag_4);

An example of NOT WHAT I WANT

This is an example of the query I want to build:

SELECT ID, json_build_array(Tag_1, Tag_2, Tag_3, Tag_4) AS tags
FROM table

The problem is that the above query adds the NULL values from the rows to the array:

ID  Tags
--------------------------------------------------
1   ['Router', 'Networking', 'Sale', null]
2   [null, 'Router', 'Networking', 'Sale']
3   [null, null, 'Networking', 'Sale']
4   [null, null, null, null]

I want to avoid having to write an overly complicated CASE WHEN statement to filter out the NULLs and I'm still new to working PostgreSQL's JSON datatypes. Is there anyway I can avoid including NULLs when building a JSON array in Postgres?

I would suggest not using a JSON array, and instead using the native SQL array syntax which is likely much faster and more efficiently stored. It's also stronger typed. The JSON array is "possibly-heterogeneously-typed" per the docs.

I also wouldn't do this routinely. I would alter the table's schema to have an ARRAY (preferably SQL) on the table itself to store the tags without ever storing null in columns. This can put you down the path of correcting the schema.

Building arrays

Strictly-typed PostgreSQL array

Just use the ARRAY literal constructor.

 SELECT id, ARRAY[tag_1,tag_2,tag_3,tag_4] FROM foo;
 id |             array
----+-------------------------------
  1 | {Router,Networking,Sale,NULL}
  2 | {NULL,Router,Networking,Sale}
  3 | {NULL,NULL,Networking,Sale}
  4 | {NULL,NULL,NULL,NULL}

A JSON array

SELECT id, json_build_array(tag_1,tag_2,tag_3,tag_4) FROM foo;
 id |            json_build_array
----+----------------------------------------
  1 | ["Router", "Networking", "Sale", null]
  2 | [null, "Router", "Networking", "Sale"]
  3 | [null, null, "Networking", "Sale"]
  4 | [null, null, null, null]
(4 rows)

Filtering Nulls without manual coalesce

Strictly-typed PostgreSQL array

You can easily filter nulls in a single pass by wrapping the above in array_remove.

SELECT id, array_remove(ARRAY[tag_1,tag_2,tag_3,tag_4], null)
FROM foo;

 id |       array_remove
----+--------------------------
  1 | {Router,Networking,Sale}
  2 | {Router,Networking,Sale}
  3 | {Networking,Sale}
  4 | {}

JSON array

SELECT id,jsonb_agg(elem)
FROM (SELECT id, ARRAY[tag_1,tag_2,tag_3,tag_4] FROM foo) AS g
CROSS JOIN LATERAL unnest(g.array)
  WITH ORDINALITY AS t(elem,ord)
WHERE elem IS NOT NULL
GROUP BY id
ORDER BY id;

 id |            jsonb_agg
----+----------------------------------
  1 | ["Router", "Networking", "Sale"]
  2 | ["Router", "Networking", "Sale"]
  3 | ["Networking", "Sale"]


Use PGXN to install pg_repack on postgres 9.6 on top of Ubuntu 14.04 So I have Ubuntu 14.04 with postgres 9.6 and I wanted to install pg_repack so I did

sudo apt-get install pgxnclient`
sudo pgxn install pg_repack

but I got an error (sorry I forgot the error) and I found a site saying I need

sudo apt-get install postgresql-server-dev-all
sudo apt-get install postgresql-common

I suspect that one or both of those came in as Postgresql 10 because when I did

sudo pgxn install pg_repack

it seems to work except for that it is putting the files in /usr/lib/postgresql/10/bin when I'm still running 9.6 server.

Can I just move that to /usr/lib/postgresql/9.6/bin or is there something specific about it to postgres10 that won't work if I just move it?

Secondly, how do I configure pgxn to use 9.6 and not 10?

The website here is dead so I imagine there's documentation out there that I can't get.

pgxn will install using the PostgreSQL associated with whatever pg_config it finds first in $PATH.  If you set your PATH (either permanently or temporarily) to list first the directory containing that pg_config then that should fix the problem

sudo su
PATH=/some/version/postgres/bin:$PATH
pgxn install pg_repack


MySQL #1005 - Can't create table 'startet' (errno: 150) Can anyone figure out why phpMyAdmin won't create the last table? It works fine if I drop the last foreign key, but I need it in there to get my assignment approved...

    CREATE TABLE nasjon (
    nasjonskode INT NOT NULL,
    nasjon VARCHAR(25) NOT NULL,
    leder VARCHAR(25) NOT NULL,
    deltagernr VARCHAR(25) NOT NULL,
    PRIMARY KEY (deltagernr)
    );

    CREATE TABLE passering (
    avstand INT NOT NULL,
    starttid INT NOT NULL,
    slutttid INT NOT NULL,
    passeringspunkt INT NOT NULL,
    høydeforskjell VARCHAR(25) NULL,
    bilde VARCHAR(25) NOT NULL,
    mellomtid VARCHAR(25) NOT NULL,
    PRIMARY KEY (slutttid, starttid)
    );

    CREATE TABLE vinner (
    startnr INT NOT NULL,
    alder VARCHAR (25) NOT NULL,
    kjønn VARCHAR (25) NOT NULL,
    øvelseid VARCHAR (25) NOT NULL,
    slutttid INT NOT NULL,
    PRIMARY KEY (startnr, slutttid),
    FOREIGN KEY (slutttid) REFERENCES passering (slutttid)
    );

 CREATE TABLE øvelser (
  øvelseid INT NOT NULL,
  øvelse VARCHAR(45) NULL,
  lengde VARCHAR(45) NULL,
  dato VARCHAR(45) NULL,
  starttidspunkt VARCHAR(45) NULL,
  PRIMARY KEY (øvelseid)
  );

CREATE TABLE registrering (
  deltagernr VARCHAR(25) NOT NULL,
  fornavn VARCHAR(45) NULL,
  etternavn VARCHAR(45) NULL,
  fødselsdato VARCHAR(45) NULL,
  kjønn VARCHAR(45) NULL,
  PRIMARY KEY (deltagernr),
  FOREIGN KEY (deltagernr) REFERENCES nasjon (deltagernr)
  );

CREATE TABLE påmelding (
  deltagernr VARCHAR(25) NOT NULL,
  startnr INT NOT NULL,
  øvelseid INT NOT NULL,
  PRIMARY KEY (startnr, øvelseid, deltagernr),
  FOREIGN KEY (startnr) REFERENCES vinner (startnr),
  FOREIGN KEY (øvelseid) REFERENCES øvelser (øvelseid),
  FOREIGN KEY (deltagernr) REFERENCES registrering (deltagernr)
  );

  CREATE TABLE startet (
  starttid INT NOT NULL,
  startnr INT NOT NULL,
  dato VARCHAR(45) NULL,
  status VARCHAR(45) NULL,
  PRIMARY KEY (startnr, starttid),
  FOREIGN KEY (startnr) REFERENCES vinner (startnr),
  FOREIGN KEY (starttid) REFERENCES passering (starttid)
   );

Been trying for hours to get this to work, but I get the same error message each time, and I'm not an administrator over the DB so can't get any further.

You need a key on passering.starttid in order to link it as a foreign key.

Like this:

   CREATE TABLE passering (
     avstand INT NOT NULL,
     starttid INT NOT NULL,
     slutttid INT NOT NULL,
     passeringspunkt INT NOT NULL,
     høydeforskjell VARCHAR(25) NULL,
     bilde VARCHAR(25) NOT NULL,
     mellomtid VARCHAR(25) NOT NULL,
     PRIMARY KEY (slutttid, starttid),
     KEY (starttid)
     );


Eliminating effectively-redundant entries with Inner Join The problem that we are trying to solve can be explained with the following illustrative example.
We have the following two tables:
CREATE table moniker_tbl
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(20),
  nickname VARCHAR(20),

  PRIMARY KEY(id)
);

CREATE table student_tbl
(
  id INT UNSIGNED AUTO_INCREMENT,
  name VARCHAR(20),
  stipend INT,

  PRIMARY KEY(id)
);

These tables are populated as follows:
INSERT INTO moniker_tbl(name, nickname) VALUES ('John Doe', 'Jonny'),
('John Doe', 'The Doe Man'), ('Richard Stevens', 'Rickie'),
('William Smith', 'Billy'), ('William Smith', 'Smithie');

INSERT INTO student_tbl(name, stipend) VALUES ('John Doe', '5000'),
('Richard Stevens', '7000'), ('William Smith', '6000');

We perform the following INNER JOIN operation:
SELECT t1.nickname, t2.stipend
FROM moniker_tbl AS t1
INNER JOIN student_tbl AS t2
ON t1.name = t2.name;

We get the following results:
+-------------+---------+
| nickname    | stipend |
+-------------+---------+
| Jonny       |    5000 |
| The Doe Man |    5000 |
| Rickie      |    7000 |
| Billy       |    6000 |
| Smithie     |    6000 |
+-------------+---------+

We would like the results to show only one entry per nickname e.g.
+-------------+---------+
| nickname    | stipend |
+-------------+---------+
| Jonny       |    5000 |
| Rickie      |    7000 |
| Billy       |    6000 |
+-------------+---------+

SELECT ANY_VALUE(t1.nickname) nickname, t2.stipend
FROM moniker_tbl AS t1
INNER JOIN student_tbl AS t2 ON t1.name = t2.name
GROUP BY t2.name, t2.stipend;


Intellisense not working for one connection but works for others I can connect to multiple database servers. Intellisense works on all of the connections except one. It used to work on this connection also until recently.

I did the following:

Enable Intellisense (on the toolbar and edit > intellisense)
Disable and then enable intellisense
Install SP3
Reboot SQL (and PC)

Have you installed Visual Studio 2010 SP1?

If so, look at this Microsoft Support article:

FIX: The IntelliSense feature in SSMS 2008 R2 may stop working after you install Visual Studio 2010 SP1

The fix was first released in Cumulative Update package 7 for SQL Server 2008 R2 RTM. It is included in all later updates and service packs. If you have indeed applied Service Pack 3 for SQL Server 2008 R2, you already have this fix.


Dealing with partitioned tables in Postgresql Due to historical reasons we used to use one really big table in our Postgresql database to store time-based series of measurements. After a long usage, as the table become 200+ GB in size, we decided we can clean it up a bit, say to keep only 90 days of data to speed things up a bit and also to save some disk space.

The problem is, doing delete from ... where clock_column < ... won't reclaim any space on disk, so we needed both to have VACUUM FULL on it after the delete.

Since we can't afford to stop the DB for such a long time, we decided to go with a partitioning approach and created per-day tables so we'll be able to delete them one by one as time expires. So now we have table (the initial, huge one), and named after date table160101, table160102 etc.

Now I'd like to do:

backup of initial table (only the initial table)

2.1 make some clearing in it (only in it)
or

2.2 maybe simple truncate it (only it)

But as I did the tests, I see COPY table TO 'file' creates dump of the whole table (both table and all of table160101, table160102 etc). The very same fashion, doing TRUNCATE clears the whole table, not the initial one (tested on test server).

When I work with partitioned tables in SELECT, I can simple state ONLY to choose which table to use. But I can not find a way to do that in COPY or TRUNCATE.

So the question is: how can I archive my goals without sacrificing data in the table?

P.S. What I need to archive is when all 'per-day' data are in the 'per-date' tables I simple don't like to keep initial huge table with very old data. In fact the TRUNCATE only on it would be just fine idea.

Let's quickly check the COPY documentation:

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' ...}
    ...

Your friend will be the query part.  It means that you cannot only copy from a table, but also an arbitrary query.  And example can be:

COPY (SELECT * FROM ONLY your_table) TO '/path/to/dump.sql';

Notes:

you have to specify an absolute path with COPY
it is usually handier to use \copy from psql as it copies the dump to the client machine, not the server.  In your case this may be irrelevant.
in practice, keeping daily tables is usually too fine-grained.  If all your queries can use constraint exclusion, then it might be all good, otherwise you might notice some planning overhead.


Sybase9, not using full system capacity The SSD I/O capacity is around 450MB. When I copy files from one folder to another I can see in the Windows Resource Monitor that the sum of read/write speed gets around 450MB. Why the same wont happen with dbeng9 or dbsrv9, while I am executing a read, transform and insert procedure?

These are the parameters:

C:\Program Files\Sybase\SQL Anywhere 9\x64\dbsrv9.exe
C:\Tributos\Dados\Asa9\Almirante\Integra.db -xtcpip -gl all -c 5000M -cl 5000M -ch 5000M -gp 8K -gb high -gt 4 -cs -n cnv_tributos

The process is not using more than 25% from each one of the 4 cores, so I don't know where is the bottleneck.

The Sybase manual informs that the per-seat license(the one i am using), is suposed to use all cores for processing requests. In adiction the "-gt 4" purpose was to force the use of 4 cores.
It looks like a default behavior as if the server process was expecting more conections or more querys to be executed, but that wont happen, only i am using the database during this process.

I need to use the maximum capacity from the machine, because I work with a ETL (Extract Transform and Load) scenario all day.
These procedures runs for hours, if i could make then run 4 times faster it would make my day.

Copying Files ->
Procedure Running ->

SSD -> Samsung SSD 840 EVO 500GB ATA Device

Sybase document reference.

Function (-gn)
Set the maximum number of active requests (both user and system) that the database server can handle concurrently.

Syntax
{ dbsrv9 | dbeng9 } -gn integer ...

Applies to
All operating systems and servers.

Description
Set the number of active user and system requests that the database server can handle concurrently. If the maximum number of active requests are already in use when the database server receives an additional request, the new request must wait until another request is completed.

Each connection uses a thread for each request, and when the request is completed the thread is returned to the pool for use by other connections. As no connection can have more than one request in progress at one time, no connection uses more than one thread at a time. An exception to this rule is if a Java application uses threads. Each thread in the Java application is a database server execution thread.

The default is 20 threads for the network database server and for the personal database server, except on Windows CE where the default is 3 threads.

On Windows NT/2000/XP, you may want investigate the Performance Monitor readings for Requests: Active and Requests: Unscheduled. If the number of active requests is always less than -gn, you can lower -gn. If the number of total requests (active + unscheduled) is often larger than -gn, then you might want to increase -gn. The Performance Monitor is not available for UNIX or Linux platforms.

Function (-gt)
Set the maximum number of requests that can be run simultaneously by the database server. This option is only useful on multiprocessor systems.

Syntax
{ dbsrv9 | dbeng9 } -gt integer ...

Applies to
All operating systems and servers except NetWare.

Description
With per-seat licensing, the network database server uses all CPUs available on the machine (the default). With CPU-based licensing, the network database server uses only the number of processors you are licensed for. In addition, the personal database server and runtime database server are both limited to a single processor.

I tried to set both parameters to 4 and to 1 , the results had no change.

Two things sprint to mind:

The process could be partly CPU bound on a single thread. You don't state what you CPU resource is, but if you have a 4-core arrangement then it coudl be that the ~25% use you are seeing is a single thread bouncing between the cores. Similarly with 8 cores it could be using 2x parallelism so two cores worth of CPU resource which is 25% of the total available. Hyperthreading may confuse this further as the virtual cores exposed by HT do not perform completely independently.
In this case your CPU is the bottleneck and trying to improve the concurrency of your operation may be the solution.
The process may not be streaming effectively so during the "read something, do something, write something" process there will be no disk activity during the "so something" phase. Often processes do not try to buffer I/O to improve this situation because adding the extra buffering layer is extra complication for (sometimes) minimal gain, it requires more memory, and it is less friendly to other concurrent processes. In comparison to your file copy test: there is almost nothing by way of "do something" stage there.
Again increasing the concurrency of your process may help a lot here.

A lot of processes like this are still optimised for spinning-metal in mind, so they will avoid concurrency where it would create I/O contention because with disc+head drives this can harm performance a lot more than it helps - addding the extra processing threads woudl increase CPU utilisation but can make the discs much more of a bottleneck due to head thrashing. I/O contention is much less of a problem with solid state drives (particularly for read operations) with SSDs because you don't have the flapping heads problem so you can increase the concurrency of I/O operations for benefit rather than detriment.

If your ETL tool can't be told to increase concurrency, or the dependencies in your overall process mean further concurrency is not even possible, the if you have several such processes to run try running more than one instance of your ETL tool at once.


Function returning table : access individual fields I have a plpgsql function declared like this :

CREATE OR REPLACE FUNCTION dynamiccondition_byid(idcond INTEGER, OtherFilters TEXT)
RETURNS TABLE (id INTEGER, weight INTEGER)

I can get the individual values like this :

SELECT * FROM dynamiccondition_byid(1,'');

Now I have a table with several conditions and I want to get all the values for all conditions.

SELECT iddyncondition,dynamiccondition_byid(iddyncondition,'')
FROM dynconditions

Now I'm getting two columns : one with the iddyncondition and one with the result of the function in which the 2 values are combined with parens :

iddyncondition   dynamiccondition_byid
--------------   ---------------------
1                (1234,5)
2                (5678,10)

I want to use the individual values (these are not arrays, I don't know what they are really).

The only way I have found is the following but I'm pretty sure the function is called twice for each line and performance is an issue :

SELECT iddyncondition
      ,(dynamiccondition_byid(iddyncondition,'')).id
      ,(dynamiccondition_byid(iddyncondition,'')).weight
FROM dynconditions

I would like to retrieve the data in a CTE like this (I'm using an array syntax so you get the idea but I know it is wrong) :

WITH dc AS (
  SELECT iddyncondition,dynamiccondition_byid(iddyncondition,'') AS dcrec
  FROM dynconditions
)
SELECT r.amountpaid,dc.weight
FROM rights r
JOIN royaltiesad ra USING(idoeu)
JOIN dc ON dc.iddyncondition=ra.iddyncondition and dc.dcrec[0]=r.idright;

You are right, the function will be called for each column if it used in the select clause even if it declared as immutable. The simple example:

drop function if exists foo(int);
create function foo(x int, out a int, out b text) returns record immutable language plpgsql as $$
begin
  raise info 'foo() called';
  a := x*2;
  b := 'x';
  return;
end $$;

explain (analyse, verbose)
  select *, (foo(t.x)).*
  from (select 2 as x) as t;

INFO:  foo() called
INFO:  foo() called
┌────────────────────────────────────────────────────────────────────────────────────┐
│                                     QUERY PLAN                                     │
╞════════════════════════════════════════════════════════════════════════════════════╡
│ Result  (cost=0.00..0.51 rows=1 width=0) (actual time=5.189..5.190 rows=1 loops=1) │
│   Output: 2, (foo(2)).a, (foo(2)).b                                                │
│ Planning time: 0.087 ms                                                            │
│ Execution time: 5.239 ms                                                           │
└────────────────────────────────────────────────────────────────────────────────────┘

There are several notes:

If your function is not SRF, you could to declare it as returns record and use out parameters to give the names to the returning fields;

I used shorthand to retrieve all fields from the function.

The right way is to use cross join:

explain (analyse, verbose)
  select *
  from (select 2 as x) as t cross join lateral foo(t.x);

INFO:  foo() called
┌──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                              QUERY PLAN                                              │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Function Scan on nd.foo  (cost=0.25..0.26 rows=1 width=36) (actual time=0.534..0.535 rows=1 loops=1) │
│   Output: 2, foo.a, foo.b                                                                            │
│   Function Call: foo(2)                                                                              │
│ Planning time: 0.114 ms                                                                              │
│ Execution time: 0.611 ms                                                                             │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘

Or for your case:

WITH dc AS (
  SELECT iddyncondition, id, weight AS dcrec
  FROM dynconditions CROSS JOIN LATERAL dynamiccondition_byid(iddyncondition,'')
)

Update: following to the comment instead of cross join lateral you also can use: t cross join foo(t.x), t, lateral foo(t.x) or just t, foo(t.x) - all will works in same way. But I recommend to use at least cross join form for that case if some other tables will be involved in the query which makes it more clear.


How can it take longer to replay a captured trace than to capture it I am attempting to use trace captures to determine how much of a performance issue was database related and how much was code related. However, the trace is taking longer to replay than it took to capture in the first place.

What I was after was the total time it would take to run our SQL statements alone, without the overhead of our actual code. If the replay took half the total time of the capture, I was going to assume that the fault was both code and database, if the replay took almost the same time as the capture, I was going to assume it was database, if the replay took almost no time compared to our capture, I was going to assume it was our code. I was assuming the replay was a very efficient way of executing all the statements.

Instead when I replay my trace it takes at least twice as long to run as the original capture took. This is true whether I allow multiple threads or require it to be sequential. I am new to this, so I could be doing something dumb (possibly the whole idea here being dumb), but it seems to me that replay ought to be faster, not slower.

My capture trace is the replay template + SQL:statement completed (so that it doubles as a tuning capture).

SQL Server can handle multiple concurrent requests simultaneously from multiple machines and threads. Unfortunately, replay can only spit them out one at a time, serially, from a single machine - introducing multiple layers of bottlenecks. There is probably also some overhead in replay to read and prepare the statements to send, that doesn't exist in your application.

If you want the replay to be more realistic, you should look into SQL Server 2012's Distributed Replay, which is able to distribute the replay across multiple machines. That said, you're not likely to ever match the original load time perfectly, and there is likely still some inherent overhead in the preparation of each statement during replay, even if you are able to eliminate some of the bottlenecks on concurrency.


How do I remove duplicate records in a table? I have a table table1 with  1 millions rows of data.

I want to remove all the duplicate records in the table1

I was looking at this link:

  How do I remove duplicate records in a join table in PostgreSQL?

Can you tell me how to do this below query in SQL server?

 DELETE FROM questions_tags q
WHERE EXISTS (
   SELECT 1
   FROM   questions_tags q1
   WHERE  q1.ctid < q.ctid
   AND    q.question_id = q1.question_id
   AND    q.tag_id = q1.tag_id
    );

The syntax is just slightly different:

DELETE q
FROM dbo.questions_tags AS q
WHERE EXISTS
(
   SELECT 1
   FROM   dbo.questions_tags AS q1
   WHERE  q1.ctid < q.ctid
   AND    q.question_id = q1.question_id
   AND    q.tag_id = q1.tag_id
);

Personally, I prefer to use a CTE. Then I can easily swap in a SELECT to validate what I am about to delete, and easily change the where clause to validate what I am going to keep:

;WITH q AS
(
  SELECT question_id, tag_id, ctid,
    rn = ROW_NUMBER() OVER (PARTITION BY question_id, tag_id ORDER BY ctid)
  FROM dbo.question_tags
)
--DELETE q
SELECT * FROM q
WHERE rn > 1; -- to show keepers, change to = 1

  I believe these semantics match yours, but please test.

Then, of course, add a proper key constraint before you let anybody insert any new nonsense into this table.


PostgreSQL: count datasets / lines containing NULL values I'm looking for a easy function to get the number of datasets (of any table) containing NULL values. It's not important how many columns are involved.

For example:

CREATE TABLE tab1 (
id INTEGER PRIMARY KEY,
dat1 VARCHAR,
dat2 VARCHAR);

INSERT INTO tab1 VALUES
(1, NULL, 'abc'),
(2, 'abc', NULL),
(3, 'abc', 'abc');

SELECT count(*)
FROM tab1
WHERE id IS NULL OR dat1 IS NULL OR dat2 IS NULL;
-- 2

(I know that Primary Keys are not likely to habe NULLs ;)

My current solution uses R to build the query.
Any suggestions?

You will need to use dynamic SQL to acheive this, perhaps like this:

testbed:

create role stack;
create schema authorization stack;
set role stack;

create table my_table as
select generate_series(0,9) as id, null::integer as val;

create table my_table2 as
select g as id, case when g<=5 then null::integer else 1 end as val
from generate_series(0,9) g;

function:

create function get_with_nulls_rowcount(p_schema in text, p_table in text)
                returns integer language plpgsql as $$
declare
  n integer;
  s text;
begin
  --
  select array_to_string(array_agg(column_name::text||' is null'), ' or ')
  into s
  from information_schema.columns
  where table_schema=p_schema and table_name=p_table;
  --
  execute 'select count(*) from '||p_table||' where '||s into n;
  return n;
end;$$;

query:

select table_schema, table_name,
       get_with_nulls_rowcount(table_schema, table_name)
from information_schema.tables
where table_schema='stack';

result:

 table_schema | table_name | get_with_nulls_rowcount
--------------+------------+-------------------------
 stack        | my_table   |                      10
 stack        | my_table2  |                       6
(2 rows)


varchar datatype accepting integer values below is the test table created. As data type is set to varchar it shouldn’t accept int values.

desc c\G
*************************** 1. row ***************************

  Field: t
   Type: int(6)
   Null: NO
    Key:
Default: NULL
  Extra:

*************************** 2. row ***************************

  Field: h
   Type: varchar(5)
   Null: NO
    Key:
Default: NULL
  Extra:

*************************** 3. row ***************************

  Field: e
   Type: varchar(6)
   Null: NO
    Key:
Default: 0
  Extra:

MariaDB [test1]> insert into c values  (3,098765,654365);
Query OK, 1 row affected (0.00 sec).

My question is how to ask mariadb/mysql to prompt for error or stopped accepting the int values in varchar datatype.I already have strict mode set to

sql_mode = STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES

Your question relates to implicit type conversions.

I searched the MariaDB documentation but their description is not very detailed except to show examples of this happening (see https://mariadb.com/kb/en/mariadb/type-conversion/). MySQL has a similar page which definitively states it implicitly converts to/from strings as required (see http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html).

  For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

The ISO-92 standard allows for implicit conversions but doesn't define all of them, it's vendor specific. However you can't stop this happening in SQL Server, or Oracle, I suspect it's by convention and can't be avoided.


Oracle: How to create secondary index based on every word in a CLOB I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX searches on the CLOB field. The primary key is just used to prevent duplicate insertions. To speed up searching, I'd like to build a secondary index of regular words found in the CLOB field.

I was thinking that maybe another table would act as an index. The primary keys of this new table are every unique word found in the CLOB field, of every record, of the original table. The other field would be a list of pointers to the records where that word exists in the original table.

Is there a more conventional way to do this? The original table will grow to over 10 million records. How would I build a secondary index that significantly speeds up REGEX searches by first searching for any regular words found in the expression, then, after taking that smaller list of returned records, it would search using the real REGEX search?

You probably want to use a contextual index. Then when you search in the clob column you would do something like

myclob contains ( 'aword') > 0

This would then be a substitute for regexp. I have not used this
feature in a few years, but it does take some work to create and
maintain the indexes. You should look for specific examples that
people created and used the index.

Indexing with Oracle Text


Is `GRANT CREATE SESSION to "MYROLE"` working in Oracle 12 database? In a Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production I'm trying to assign the CREATE SESSION privilege to a custom role MYROLE and then assign MYROLE role to MYUSER user.

GRANT CREATE SESSION to "MYROLE"
GRANT "MYROLE" to "MYUSER"

When I try to connect I received:

  ORA-01045: user MYUSER lacks CREATE SESSION privilege; logon denied

What am I missing?

If I give CREATE SESSION grant directly to MYUSER it works.

EDIT:

I think that my issue was related to the fact that MYUSER hadn't a DEFAULT ROLE.

You might be connected to wrong container database, probably CDB.

Here is a demo which is working fine for me.

I am connected to orclpdb, a pluggable database as sysdba.

[oracle@oracdb ~]$ sqlplus sys@orclpdb as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 20 15:26:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create role myrole;

Role created.

SQL> grant create session to myrole;

Grant succeeded.

SQL> create user testuser identified by testuser;

User created.

SQL> grant myrole to testuser;

Grant succeeded.

SQL> conn testuser/testuser@orclpdb
Connected.
SQL>


What table to put my foreign key in? I have two entity relations called Record_Label and Band.
I currently have:

Record_label = /\ --- band (1:M)
               \/

Where the cardinality ratio is 1:M and record_label has a total participation and band has partial participation.

I read on-line that when I have a many to partial participation, I put the table primary key from the partial participation(band) on to the many table (record_label).

But I also read that when it is 1:M then I put the record_label primary key to the band table as a fk. My question is which one should I do?

I'm gonna lose the theory for a minute and remind you that you're lacking a table. Bands don't have labels; record(ing)s do. A band can, throughout its career, publish recordings on many labels.

If, when Metallica left Megaforce to sign with Elektra, you simply changed the value in the 'label' foreign key column, you'd have no record (no pun intended) of the fact that they were ever with Megaforce. Actually, the albums Ride The Lightning and Kill 'Em All are what should be associated with the label Megaforce, while Master Of Puppets is associated with Elektra.

BandID:
  pk in tblBand
  fk in tblAlbum

LabelID:
  pk in tblLabel
  fk in tblAlbum


Two separate column counts grouped by one of those columns I have a Voters table, which has fields including party and email.

party takes just a few acceptable values. I have a query which counts party membership and groups by the party:

SELECT party as Party, COUNT(*) AS Party_COUNT
FROM Voters
GROUP BY party
ORDER BY Party_COUNT DESC;

Works nicely.  I would now like to show additional data: how many of those party members have an email address recorded in the DB?

I should end up with something like the following:

Party   Party_COUNT EMail
DEM     90802       10000
REP     41383       5000
NPP     40000       4750
...etc.

I get the aggregate email count easily with:

SELECT COUNT(*) email from Voters WHERE email <> ''

but I'm having trouble mashing my two count queries together to get the desired tabular result.

This is in MariaDB 10.1.18.

I am counting WHERE email <> '' because I receive the dataset from a governmental agency which can't even provide the DDL for the database, just a CSV dump.

The field seems to have only NULLs or actual e-mail addresses, but I can't be sure, especially as this data is updated somewhat frequently.  Furthermore, CSV will not properly reflect NULLS, so empty strings are imported . . .

To count rows:

COUNT(*)

To count non-null values in a column:

COUNT(email)

To count non-null values, without counting the empty strings:

COUNT(CASE WHEN email > '' THEN email END)    -- or
COUNT(CASE WHEN email > '' THEN 1 END)        -- or
SUM(email > '')                               -- or
COUNT(email) - SUM(email = '')


How to install postgresql on a server? Background: I am busy with an MSc in GIS. It involves using postgresql 9.2 with the postgis extension as the spatial database and QGIS to view, edit and analyse the data. I know postgis is also capable of editing and analyzing spatial data. However, the end users do not have experience with SQL, therefore the point and click QGIS GUI. Part of my thesis involves a guide on installing Postgres/postgis on a server. The problem is, I have very little experience with databases and servers. Although, I have been able to succesfully install and use postgresql/postgis on my win 7 pc, I'm not sure if it is the same for servers.

*Question:*Is postgresql/postgis installed on a server in the same manner as on a pc? If not, please explain (in as much detail as possile) or point me in the right direction. Also, what configurations has to be done for client pc's to connect to postgresql/postgis on the server?

Thanks in advance.

Except in Windows-land there's no difference between a "Server" and a "PC" other than typical hardware choices and installed software.

PostgreSQL follows that, and doesn't care in the slightest what you're installing it on. Install it on Win2k8 the same way you would on any Windows desktop OS.

The only possible wrinkle is Group Policy and Active Directory domain membership, but those are also factors in domain-member PCs. So long as your group policies don't restrict the NETWORKSERVICE, and so long as you install PostgreSQL to use the NETWORKSERVICE (default in 9.2 and later) you'll have no issues, it'll be just like any other local machine install.

If you want to run PostgreSQL under an Active Directory user account, or an account with more restrictive group policies, then you might have to set up the service yourself, but you'll have a capable sysadmin to do that if you're in the kind of environment where it might be necessary.

Client configuration depends on what you're connecting with. You may want the ODBC driver psqlODBC, you may want PgJDBC, you might want the Python driver psycopg2, etc etc. The general idea is: install desired driver, use application. The only one that takes more setup is ODBC if used with a predefined DSN, and that's the same for PostgreSQL as any other ODBC app.

To configure the server to accept connections from clients you must:

Ensure the server's firewall allows inbound connections to postgres.exe and/or port 5432 (default)
Set up pg_hba.conf to allow PostgreSQL to accept remote connections (see the documentation)
Create PostgreSQL user accounts for the users


What are the concepts that need to be gone through for MySQL DBA? I am a MySQL Developer, besides I am handling some DBA activities like
backup, restore, mysql installation, etc at a top level.

Now I wanted to shift to DBA, So I wanted to know in depth, like which and concepts should be gone through as a DBA.

Also, what and all to be monitored on a daily basis.

Please help me in this.

Storage Engine
You should be very fluent with the use of two main storage engines: MyISAM and InnoDB
You must learn :

how data and index pages are laid out
how table and indexes are cached
how table formats can make significant differences in reads and writes
how table-level and row-level locking are handled
how queries that mix both storages engines affect performance
how to backup data from each storages engine

MySQL Replication
You should understand such things as

What are the I/O and SQL Threads
When log-slave-updates is necessary for a slave
What allows you to write on a read-only slave
When it's appropriate to use MyISAM/blackhole on a slave for tables that are innodb on the master
How relay logs are populated
How to recognize slave errors
How to filter out/fileter in SQL for specific databases and tables
Why run backups for slaves rather than masters

Troubleshooting and Tuning Queries

Learn how to run EXPLAIN on queries
Learn how to create indexes to properly speed up queries
Learn how to tune queries
Learn how to activate the slow log
Learn how to convert the slow log into MyISAM

INFORMATION_SCHEMA
You should learn how to query the INFORMATION_SCHEMA database for table info, row counts, last access info, and things like these.
Third Party Tools
The best tools that you should embrace are the following:

MAATKIT
Percona Toolkit (fork of MAATKIT also from Percona)
XtraBackup
MONyog / SQLYog

This is not a comprehensive list. However, it should be enough for anyone to be proficient as a Developer/DBA.
Here is Oracle's Certification Exam Descriptions that provide lists of skills within MySQL to have and know: EXAM1 and EXAM2
IMHO Here is a good set of MySQL Books to learn from


Stuff function in T-SQL select STUFF(
STUFF(RIGHT('000000' + CAST([run_duration] AS VARCHAR(6)),  6)
            , 3, 0, ':')
        , 6, 0, ':')
    AS [LastRunDuration (HH:MM:SS)]
 from sysjobhistory

The query I have given above works fine but I am trying to understand it more clear. My observations are:

First we cast the run duration into varchar and with a length 6 and append six 0's to right of it which becomes if its is 10 then 00000010
After we use the stuff function to insert :  in between this here it is at the 3rd position so the above one becomes 00:000010
Again we use stuff  to insert the : symbol to the sixth position so it becomes 00:00:0010

But when I run the query if there is a values 10 it shows as 00:00:10.

How that can be?

In your first bullet you say the value becomes 00000010. That is incorrect. It actually becomes 000010 because of the RIGHT( , 6).

To clarify, compare the difference:

SELECT LEFT('00000010', 6), RIGHT('00000010', 6);

Results:

------  ------
000000  000010


mongodb - different keyfiles for different replica sets? I am building a cluster using mongodb (3.4). Can I use separate keyfiles for my configuration replica set and my shard replica sets? Judging by the documentation (https://docs.mongodb.com/v3.2/tutorial/enforce-keyfile-access-control-in-existing-replica-set/), it seems possible but I'm not sure.

Thanks

The keyfile is used to secure intra-cluster communication, so all components of a sharded cluster (mongod and mongos) must use the same keyfile.

The documentation you referenced is for a single replica set deployment. For a sharded cluster see: Enforce Keyfile Access Control in Sharded Cluster.


Loop inside stored procedure doesn't stop after query cancellation I have a few nested stored procedures, which have a while loop at the end. When I stop the query, the loop keeps running, though. Every child sp is independant from another and has a duration of less than 1s. Any ideas why it keeps running or how to properly cancel it?

You could have a look at running requests with either sp_whoisactive or with the below query and kill the session associated with the query / stored procedure.

select r.session_id
    , s.text
from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(sql_handle) AS s

Then you just execute the KILL command, like below, with the appropriate integer value from the above result, session which is associated with your stored procedure / query:

KILL <session_id>


MySQL proxy to replication configuration Is there any solution (say a proxy) that makes MySQL replication cluster behave like one database towards developers? Developers then won't need to worry about using master to write and slaves to read etc.?

The new MySQL Router is one possible solution. Please take a look and let us know what you think!

I'd love to hear what features you most want in future versions.

http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/

http://mysqlhighavailability.com/easy-load-balancing-and-high-availability-using-mysql-router/


Deleting folders after Oracle upgrade I upgraded from Oracle 10g to Oracle 11g in Windows Server 2003.
New Oracle home : C:\app\oracle\product\11.2.0\db_1
Old Oracle 10g was installed on: C:\oracle\product\10.2.0\
Questions

Is the 10g folder is still useful?
Could I delete the 10g folder without a problem?

Old oracle home is no more useful after upgrade.
To safe remove it you should do either:

Start > Installation Products > Oracle Universal Installer.

from here you can select deinstall and remove the old oracle home.
One alternative is to manually perform the process running the command:

setup.exe -silent -detachHome ORACLE_HOME="C:\product\DBHome" ORACLE_HOME_NAME="DBHome"

You have to replace ORACLE_HOME_NAME and ORACLE_HOME with the one on your system. To find them, in Windows, first get the value of

\\HKEY_LOCAL_MACHINE\\Software\Oracle\inst_loc.

in your windows registry. Then navigate in the inventory location and open the file:

inventory.xml

here you'll find some lines like:

<HOME NAME="OUIHome1" LOC="C:\oracle\product\10.2.0\" TYPE="O" IDX="4" />

This is your line. Location is the one you know, write down the name and perform the command. When oracle home is detached you can manually delete it from windows explorer.

IN addition, manual delete without detaching does not cause any problem but leave some annoying pointer in the inventory


What are the limitations of Toad for SQL Server freeware? I just downloaded the freeware version of Toad for SQL Server. Toad's download site says: "the Freeware edition has certain limitations, and is not intended to be used as a TRIAL for the Commercial edition of Toad for SQL Server." It also says that no more than 5 people from my organization can use the freeware version, and that I'm not entitled to hard-copy documentation, phone assistance,  tech support or upgrades.

Is the product fully functional besides that?

The Toad site has documentation on the limitations of Toad Data Modeler but not for Toad for SQL Server or Toad for Oracle. I found a blog post "I love the new Toad freeware!" on their site as well claiming that the new freeware is no longer "crippleware" the way it used to be.

I've used the tool for a few days now, and there are lots of menu options disabled, so no, the product is nowhere close to fully functional.


Possible career paths for Oracle Certified Associate (OCA) In order to be confident in databases as a entry level job candidate, I completed the OCA certification.  Now I want to know what career paths follow this.  Can one go into data-mining,  BI, or R&D via this certification?

And I heard DBA is quite boring and risky for experimental people.

I imagine you can go anywhere you want to go. But real-world experience counts too -- not just what's on the exam, so don't expect a high-level position from the start. You'll probably have to start as low man on the totem pole, so-to-speak.

Having been a DBA, I can tell you several things:

It's freakin' hard work
When things are going well, life is good. When things are going bad, life is hell.
Unless you happen on a place where you're a pure DBA, chances are good you'll have programming opportunities. Relish them -- this is where you can express your creativity.
When things go really bad, experimentation is sometimes the only way out. (Not reckless abandon, mind you, but oft-times it becomes a matter of trying one solution, finding it doesn't do the trick, and then trying something else.)
There are boring parts: upgrades, backups, maintenance. But sometimes that's a good thing, because otherwise you'll be so busy that your brain can't wait for the boring times.

Hope it helps and good luck with your career.


sqlite3 foreign keys ignored I am working on a small sqlite3 database for a small simple mailserver. I have created a new sqlite3 database and add executed this to pürepare it for data:

BEGIN TRANSACTION;
CREATE TABLE `mailboxes` (
    `username`  TEXT NOT NULL,
    `domain`    TEXT NOT NULL,
    `mailbox`   TEXT NOT NULL,
    FOREIGN KEY(`domain`) REFERENCES domains(domain)
    UNIQUE (username, domain),
    UNIQUE (mailbox)
);
CREATE TABLE `domains` (
    `domain`    TEXT NOT NULL UNIQUE
);
CREATE TABLE `aliases` (
    `aliasname` TEXT NOT NULL,
    `domain`    TEXT NOT NULL,
    `destination`   TEXT NOT NULL,
    FOREIGN KEY(`domain`) REFERENCES domains(domain)
    UNIQUE (aliasname, domain)
);
COMMIT;

Foreign keys are enabled with PRAGMA and no errors are shown. As you can see, mailboxes and aliases are nearly the same. I have a list of available domains. Every mailbox/alias entry must use a valid entry of "domains". In addition to this, a "username - domain" pair should only appear once. BUT: the same mail can appear in mailboxes and aliases.

PROBLEM

I have no entries in my domains table, but executing this works (but it should not, because it is not a valid domain):

INSERT INTO aliases (aliasname, domain, destination)
VALUES ('test', 'myacc.de', 'other@worldwide.com');

Where is my fault?

I was partially able to reproduce your bug when having PRAGMA foreign_keys = ON;, but setting the domain column in the domains table as PRIMARY KEY removed any problem in my case. (Still keep the pragma on).


unicode symbols for entity-relationship What are the Unicode symbols that most closely relate to the symbols used in entity-relationship diagrams, such as the ones in the screenshot of the OP here: https://stackoverflow.com/questions/1755591/many-to-many-relationships-with-additional-data-on-the-relationship?
When displaying data, I want to prefix a Unicode representation of entity-relationship that its table represents.
The "many-to-one" entity relationship uses symbols that look almost like ">o-to-⧺" , respectively, but...

the actual many symbol, like in the screenshot and in some database books, has an extra glyph that looks like a strikethrough of the ">" symbol, almost like ->, but with the - attached to the > like a 3rd prong, not separated like it is here ->.
The actual one symbol appears to be the ⧺ (U+29fa =  Double Plus) character, which I found while writing this post, but I do not know if this is the actual correct symbol, but it looks very close to it.  Close enough, I guess.

I've gleaned through several Unicode charts, like this one for the many symbol, but have not found it.

From my experience, simpler characters are more commonly used, e.g. 1-1 to mean one-to-one, and 1-* for one-to-many, and *-* for many-to-many.
You can also use the infinity symbol (U+221E) for the "many" symbol if you really wanted but I prefer the previous simpler examples.
By the way, that website is helpfully searchable and you can find other similar arrow characters to what you're looking for as well such as the following:

Rightwards Two Headed Arrow (U+21A0)
Rightwards Double Arrow (U+21D2)
Rightwards Triple Arrow (U+21DB)
Rightwards Arrow with Double Vertical Stroke (U+21FB)
Rightwards Two-Headed Arrow with Double Vertical Stroke (U+2901)
Rightwards Double Arrow-Tail (U+291C)


Oracle grouped row counts to columns I have a table which contains this data:

- ID - PRIMARY KEY, INTEGER
- PC_ID - INTEGER
- USER_ID - INTEGER
- PAYMENT_DATETIME - TIMESTAMP
- FIO - VARCHAR2(255)

When I do a SELECT I get this (example):

1, 1, 1, 08.06.2018 17:33:26, Test
2, 1, 1, 08.06.2018 17:34:52, Test
3, 1, 1, 08.06.2018 18:01:33, Test
4, 1, 1, 08.06.2018 18:03:17, Test2
5, 1, 1, 08.06.2018 19:26:41, Test
6, 1, 1, 09.06.2018 13:22:58, Test2

So if I group them by PC_ID, USER_ID, Distinct FIO, DATE (DD. MM. YYYY), TIME (HH24) as rows to show amount of different FIO for hours 07 to 22 for each day for each user for each pc, I get:

1, 1, 08.06.2018, 17, 1
1, 1, 08.06.2018, 18, 2
1, 1, 08.06.2018, 19, 1
1, 1, 09.06.2018, 13, 1

The last column is the Count of distinct FIO.

Now I would like to transform it to:

Columns: PC_ID, USER_ID, DATE (DD. MM. YYYY), TIME (HH24) for 07 to 22 and TOTAL FOR DATE

If the count for hour is 0 then show 0:

So the structure should look like:

PC_ID, USER_ID, DATE, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, TOTAL_FIO

I was thinking of CTEs, can this be achieved using them?

Possible with a CTE, but PIVOT is the easier implementation.

If for total_fio you still wanted distinct, this would work:

SELECT * FROM
 (SELECT pc_id,
         user_id,
         to_char(payment_datetime,'DD.MM.YYYY') AS "date",
         To_char(payment_datetime, 'HH24') AS "hour",
         fio,
         count(DISTINCT fio) OVER ( PARTITION BY  to_char(payment_datetime,'DD.MM.YYYY') ) AS total_fio
  FROM   foobar
  WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22' )
PIVOT
 (Count(DISTINCT fio)
   FOR "hour" IN ('07', '08', '09', '10', '11', '12',
                  '13', '14', '15', '16', '17', '18',
                  '19', '20', '21', '22'));

Or if you wanted a sum of all hour columns, I'm not sure of an elegant way to do it without getting into PL/SQL:

SELECT x.*, "07" + "08" + "09" + "10" + "11" + "12"
             + "13" + "14" + "15" + "16" + "17" + "18"
             + "19" + "20" + "21" + "22" AS total_fio
FROM
(SELECT pc_id,
        user_id,
        to_char(payment_datetime,'DD.MM.YYYY') AS date_day,
        To_char(payment_datetime, 'HH24') AS date_hour,
        fio
 FROM   foobar
 WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22')
PIVOT
(Count(DISTINCT fio)
  FOR date_hour IN ('07' AS "07", '08' AS "08", '09' AS "09", '10' AS "10", '11' AS "11",
                    '12' AS "12", '13' AS "13", '14' AS "14", '15' AS "15", '16' AS "16",
                    '17' AS "17", '18' AS "18", '19' AS "19", '20' AS "20", '21' AS "21",
                    '22' AS "22")) x;

I'm new on here so don't have enough rep to comment on your implementation, but it's a solid go at it for sure. I don't see anything wrong where you'd get inconsistent results. As for optimization, `PIVOT' does the grouping for you. So by using the CTE with the additional group by to start out, it is almost doubling the work the query has to do. My second query above is essentially an optimized version of yours (they are very similar).


Export Postgres table as json Is there a way to export postgres table data as json to a file? I need the output to be line by line, like:

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

EDIT: postgres version: 9.3.4

Try here for a basic intro to PostgreSQL and JSON.

Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option.

Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

I didn't have a running instance of PostgreSQL so I downloaded, compiled and installed 9.4.

To answer this, I first CREATEed a table (fred)

CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20));

INSERT INTO fred VALUES (2,    43, 'asfasfasfd'      );
INSERT INTO fred VALUES (3,   435, 'ererere'         );
INSERT INTO fred VALUES (6, 43343, 'eresdfssfsfasfae');

Then, to check:

test=# select * from fred;

 mary | jimmy |      paulie
------+-------+------------------
    2 |    43 | asfasfasfd
    3 |   435 | ererere
    6 | 43343 | eresdfssfsfasfae

Then I issued this command

test=# COPY (SELECT ROW_TO_JSON(t)
test(# FROM (SELECT * FROM fred) t)
test-# TO '/paulstuff/sware/db/postgres/inst/myfile';
COPY 3
test=#

I then quit psql and listed the file myfile.

test=# \q
[pol@polhost inst]$ more myfile
{"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
{"mary":3,"jimmy":435,"paulie":"ererere"}
{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
[pol@polhost inst]$

(you can experiment with the output from

COPY (SELECT ROW_TO_JSON(t, TRUE)  -- <-- Note addition of "TRUE" here!

at your leisure).

It was pointed out by @offby1 that the output (while corresponding to the OP's question) is not correct JSON. @EvanCarroll pointed out that \o is also a way of outputting to a file, so I combined the solutions to these two niggles in this statement (with help from here):

test=# \o out.json
test=# SELECT array_to_json(array_agg(fred), FALSE) AS ok_json FROM fred;
                                     -- <-- "TRUE" here will produce plus
                                        ("+) signs in the output. "FALSE"
                                        is the default anyway.
test=# \o

gives:

[pol@polhost inst]$ more out.json
                                                                   ok_json
----------------------------------------------------------------------------------------------------------------------------------------------
 [{"mary":2,"jimmy":43,"paulie":"asfasfasfd"},{"mary":3,"jimmy":435,"paulie":"ererere"},{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}]
(1 row)
[pol@polhost inst]$

FINALLY, there is the backslash (\) problem alluded to by @AdamGent in his post. This was a bit tricky, but it is possible without resorting to post-query processing. Voilà:

INSERT INTO fred VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred VALUES (3, 44545, '\sdfs\\\sfs\\gf');

And using REGEXP_REPLACE thus (note the cast ::TEXT) removes the excess blackslashes.

test=# \o slash.json
test=# SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g')
test=# FROM (SELECT * FROM fred) AS t;  -- I found that using a CTE was helpful for legibility
test=# \o
test=# \q

gives:

[pol@polhost inst]$ more slash.json
                    regexp_replace
------------------------------------------------------
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
(5 rows)
[pol@polhost inst]$

(p.s. As for @Zoltán 's comment - this may be a version thing - unable to reproduce!).


MySQL View from different database with the same table schema I am in a situation right now where I have to create a MySQL view from different databases on the same host. The team that created the data used different databases for every year (like sales_2014, sales_2015) etc.

All databases have the same tables with almost the same schema. Only some columns are different, but i only need those that are the same so I don't have to care about those that are different.

For my project I need to have that data in one view or table. What is the best way to do this? Should I create a view for every database and connect all views via a single view on a different database, should I just use one big view for all tables from the different databases or should i use something different than views?

Simply reference whatever databases you want.  Note the "db.tbl" syntax:

CREATE VIEW ...
    AS
    SELECT ...
        FROM sales_2014.blah AS x
        JOIN sales_2015.blah AS y ON ...

Similarly, you could use UNION ALL.

CREATE OR REPLACE VIEW
    AS all_sales
        SELECT * FROM 2014.sales
    UNION ALL
        SELECT * FROM 2015.sales;

For 'sanity', you might want to create a new database to store the VIEWs in.  You might call it sales_all.


Impact of using mirroring to migrate between SQL Server 2005 and SQL Server 2008 R2 I've got a DB which sits on a machine (SQL Server 2005) due for replacement very soon.

I'm planning on having the database mirrored to the replacement machine (SQL Server 2008 R2)  so that when the time comes to switch everything over, I can fail it over and quickly get things back up and running on the new machine.

If I then mirror the DB on the replacement machine (now the principal) to ANOTHER machine (SQL Server 2008 R2), will there be any impact or issues which could appear, which wouldn't if I'd have backed up and restored cleanly onto the new server?

(Assume that I've copied all the users/configurations/jobs etc over to the replacement machine, and that once the replacement machine is in use, the SQL 2005 machine will be removed  so it won't be failed back to)

Everything will move over just fine.  Just make sure that you don't fail over by accident ahead of schedule (I've seen someone do that) as there's no way to fail back.


Should I use MongoDB or a relational database management system for this scenario? I have a different item types, each item has different number of attributes some attributes might contain an arbitrary number of values. For example, a building which has amenities attribute, this attribute has many different possible values a user should be able to select these values for the item Building from a list. Some examples of these values are Balcony, Pet Allowed, WiFi. Another item is Car this car have different attributes like Model, Speed, Class each of them has a single possible value.
In addition to Attributes I have a different nested Categories for each item, for example a Vehicle category, is a parent of Car category which is also a parent of BMW Car category.
As you can see, the second requirement can be modeled easily for a Relational Database but the first one is very hard to model for Non-relational Database like MongoDB. My app will be used by huge number of users, and both categories and attributes are subject to Insert/Update/Delete operations also they may be involved in some reports generation process.

I think about using MongoDB as DBMS, but I'm not sure yet about this decision.

To answer your base question, why not. Value lists are easy to make, child - parent can be made with references. You even mix those two to the single key.


Backup SQL Server 2017 to remote directory There is SQL Server instance with 4 DBs.
There is remote directory '\u12160.your-backup.com\backup\SQL' which can be accessed ONLY with User/Password (no user auth or anything other).

I want to backup DBs on schedule with SQL Server Agent job like this

EXEC sp_BackupDatabases @backupLocation='\\u12160.your-backup.com\backup\SQL', @backupType='F'

but it throws error

Operating system error 1326(The user name or password is incorrect.).
Msg 3013, Level 16, State 1, Line 1

Is it possible somehow to pass username/password? Or are there any other ways to backup straight into remote directory&

Thank you.

Can you turn the network path into a drive letter using windows explorer or workplace

This allows to "use different user credentials" - and "remember the settings" + "automatically connect upon startup"(or similarly named) - so it should automatically connect to the network path using the username+password combo you got...
Then use your database backup with the created drive letter from the above step


Data that are sometimes entered and sometimes computed Suppose I have a database of StorageComplex objects. StorageComplex has a guid Id, plus two data columns: Capacity (a number), and Location (a string).
My data also contain a table of StorageTank objects. A StorageTank also has an Id, Capacity and a Location. Application code will frequently treat a collection of StorageTanks at the same Location as an aggregated StorageFacility. The Capacity is the sum of the capacities of the individual StorageTanks.
It is guaranteed that there will never be a StorageTank at a location that also has a non-aggregated StorageFacility.
If it's relevant, the applications in question access the database via EntityFramework.
My question is about the advantages and disadvantages of different strategies to manage this situation. One could imagine maintaining aggregated StorageFacility objects in the StorageFacility table. Whenever a StorageTank at a location is added, updated, or deleted, one would have to apply a corresponding change to the aggregated StorageFacility at that location. Alternatively, one could imagine not maintaining such entries. Then every query for StorageFacilities would also need to check the StorageTank table.
In the real-life applications, everything is more complicated than in this toy example. There are a lot of fields that have to match in order for StorageTanks be aggregated together, there are multiple fields that need to be aggregated, and the rules for the actual aggregation are more complicated than just adding up some numbers. The StorageTanks have fields that StorageFacilities lack. Additionally, there are a lot of different queries that access the StorageFacilities. For some purposes, an aggregated StorageFacility should be treated the same as a non-aggregated one. For other purposes, they are different.

There's no hard-and-fast rule here.
This pattern is very common, and here you could either:

Have application logic update the StorageFacility.Capacity (etc) every time a StorageTank is added, removed, or perhaps undergoing maintenance.

or

Store the data only at the StorageTank and roll it up as needed.

Things like the frequency of changes, the level of complexity of the roll-up rules, frequency of query, etc should make your decision.
In either case I don't believe it'helpful that "Application code should treat a collection of StorageTanks at the same Location as an aggregated StorageFacility".  You should simply have two seperate entities.