Deleting bin logs in replication environment I have a question about deleting binary logs in the replication environment:

We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy processing times, whereby bin log directory gets full. Logs are expired every 3 days. I was wondering, is there a reason why logs should be kept for 3 days on all the boxes – master and both slaves? Would it make sense to, for example, keep logs for 3 days on a master, but for 1 day on slaves? What is the best way to go about it?

Thank you!

SLAVE
If your Slaves are not Masters, then Slaves do not need binary logging at all. You can put a cap on the amount of relay log space accumulated by a Slave. In order to throttle relay logs at 4G, add relay_log_space_limit to /etc/my/.cnf on every Slave
[mysqld]
relay_log_space_limit=4G

and restart mysql
If you cannot set this, at least you should have some kind of alerting that does SHOW SLAVE STATUS\G and check the value of Relay_Log_Space (total bytes consumed by relay logs).
MASTER
As for the Master, you could set expire_logs_days to 1, but there is a severe warning I have for you...
If replication breaks, you have 1 day to fix it. Otherwise, a binary log on the Master may rotate away and you cannot run any CHANGE MASTER TO command to realign replication. I would leave expire_logs_days at 3 on the Master.
SUGGESTION #1
If you have any overnight bulk processing to do, maybe should run the bulk processes on on the Master with SET SQL_LOG_BIN=0; at the Start of the Session. This, of course, will not replicate to the Slave. You can perform the Same Bulk Load in Parallel to both Slaves.
SUGGESTION #2
Another thing you could do to manage the Master binary logs accumulation is this.
Run SHOW SLAVE STATUS\G on both Slaves. Look at Relay_Master_Log_File. That represents the binary log on the Master whose last command was executed on the Slave.
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.4.92.250
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.009677
        Read_Master_Log_Pos: 855227755
             Relay_Log_File: relay-bin.000674
              Relay_Log_Pos: 757296783
      Relay_Master_Log_File: mysql-bin.009590
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 757296646
            Relay_Log_Space: 94274010765
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 80561
1 row in set (0.00 sec)

In this example, Relay_Master_Log_File is mysql-bin.009590. All binary logs before this one can be removed from Master. You could run this on the Master:
PURGE BINARY LOGS TO 'mysql-bin.009590';

This will erase older logs and still leave replication in tact.
CAVEAT
Binary Logs are files that serially compiles (like a FIFO queue) all completed SQL transactions as either a SQL statement or a row change. A relay log is a file that collects binary log entries from a remote server (aka Master).
In MySQL Replication

Master must have its binary logs enabled
Slave compiles relay logs
When all SQL in a relay log is processed, it is deleted
On a Slave, when there is more that one relay log on a DB Server, it may indicate replication is falling behind because the IO thread is collecting SQL from a Master faster that the SQL thread can process the relay logs.
Using relay_log_space_limit prevents replication from piling up and potentially filling up a disk. Relay logs rotate out based on rule #3
It is possible for a DB Server to be both a Master and Slave. That's the only circumstance under which a Slave must have binary logs enabled. In that scenario, a DB Server will have both Binary Logs and Relay Logs.

If you failover to a Slave, and you want to Make it a Master

service mysql stop
Add log-bin=mysql-bin to /etc/my.cnf on the Slave
service mysql start

You will have to setup replication of other Slaves to the newly promoted Master and make sure the data on the Slave match up with the newly promoted Master
UPDATE 2012-08-13 17:47 EDT
According to the MySQL Documentation on relay-log option, you should define it. Here is why:

Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.3, “Specifying Program Options”.


Query plan for a dynamically built `query_int` using `format()` To make things simple here is a statically built version of the query that runs perfectly fast:

SELECT * FROM media WHERE tag_ids @@ '123&321'::query_int ORDER BY rating DESC LIMIT 20;

The catch is that the tag IDs are kept in another table and of course the app's UI accepts tags as strings. So the actual query looks something like this:

SELECT * FROM media WHERE tag_ids
  @@ (SELECT format('%s', (
    SELECT ARRAY_TO_STRING((
      SELECT ARRAY(
        WITH tag_ids as (SELECT id FROM tags WHERE tag IN ('kittens', 'puppies')
      )
      SELECT id FROM tag_ids
      UNION
      SELECT 0 FROM tag_ids WHERE NOT EXISTS (SELECT 1 FROM tag_ids)
    )), '&')
  )))::query_int
ORDER BY rating DESC LIMIT 20;

So the problem is that the query planner always predicts that the tag_ids @@ ... clause will return around 32k rows. Considering that there is a LIMIT of 20 then an index scan on rating, followed by a sequential scan makes total sense. But! Some, indeed most, tag filters return way less than that, making an index scan problematically slow, eg:

->  Index Scan using media_rating_index on media  (cost=0.43..2741321.93 rows=32509 width=11) (actual time=70.816..24270.968 rows=20 loops=1)
         Filter: (tag_ids @@ ($5)::query_int)
         Rows Removed by Filter: 1565931
 Planning Time: 0.514 ms
 Execution Time: 24271.155 ms

Of course when using a statically built tag_ids @@ ... clause for tags that are known to have a low occurrence a tag-based index is chosen by the query planner, eg:

Limit  (cost=9163.00..9163.05 rows=20 width=11) (actual time=3.838..3.841 rows=3 loops=1)
   ->  Sort  (cost=9163.00..9168.96 rows=2384 width=11) (actual time=3.834..3.836 rows=3 loops=1)
         Sort Key: rating DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on media  (cost=38.48..9099.57 rows=2384 width=11) (actual time=2.780..3.796 rows=3 loops=1)
               Recheck Cond: (tag_ids @@ '654&321'::query_int)
               Heap Blocks: exact=3
               ->  Bitmap Index Scan on media_tag_ids_gin__int_ops_index  (cost=0.00..37.88 rows=2384 width=0) (actual time=2.023..2.023 rows=3 loops=1)
                     Index Cond: (tag_ids @@ '654&321'::query_int)
 Planning Time: 2.218 ms
 Execution Time: 3.952 ms

One way to solve this is to just send 2 queries to the DB from the application, then the format() can even be done in the application code. But that just feels a bit like giving up. The other thing is an SQL function, but that seems overkill. Is there another more idiomatic way to give the query planner the tag IDs so it can better predict the number of returned rows and thus plan a better query?

First of all, you can simplify the query you have. format() is not needed, among other things:

SELECT *
FROM   media
WHERE  tag_ids @@ array_to_string(ARRAY (
         WITH  tag_ids AS (SELECT id FROM tags WHERE tag IN ('kittens', 'puppies'))
         TABLE tag_ids
         UNION ALL
         SELECT 0 WHERE NOT EXISTS (TABLE tag_ids)
         ), '&')::query_int
ORDER  BY rating DESC
LIMIT  20;

While your query_int value only uses &, further simplify to:

SELECT *
FROM   media
WHERE  tag_ids @> COALESCE(NULLIF(ARRAY(SELECT id FROM tags WHERE tag IN ('kittens', 'puppies')), '{}'), '{0}')
ORDER  BY rating DESC
LIMIT  20;

Should be faster already. But the core problem remains - which is very similar to the one discussed in detail here:

Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

Basically this: Postgres (necessarily) chooses the query plan based on incomplete information. Column statistics cannot contain all frequencies for many rare values. So it's very hard to estimate which plan will be faster:

plan A: traverse the index media_rating_index and filter
plan B: bitmap index scan on media_tag_ids_gin__int_ops_index, sort by ratings, limit

Your case adds complication with a convoluted subquery, which makes it even harder to estimate what might come out of it.

Whatever else you do, make sure you run the most recent version of Postgres available to you, as your particular problem may well profit from a number of recent improvements around this.

Depending on undisclosed information, one of these may work:

1.

Rewrite your query with LEFT JOIN LATERAL as demonstrated in the referenced answer.

2.

Completely disable plan A by obfuscating the ORDER BY. Assuming it's a numerical column:

SELECT ...
ORDER  BY rating + 0 DESC
LIMIT  20;

Like Laurenz suggested:

Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

3.

Even more radical, delete the index media_rating_index - unless it's needed for other queries!

4.

Jeff suggested a manual setting for n_distinct in the related case, but I am not sure how to achieve the same for array elements ...

Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

5.

Here is a related case from the pgsql-performance list. Your case is simpler, you may improve your situation by just upping the statistics target for tag_ids:

ALTER TABLE media ALTER COLUMN tag_ids SET STATISTICS 1000;

And then run ANALYZE media.
This may be useful in addition to other options, especially the following.

6.

In a related case I had success with a fake-IMMUTABLE function:

CREATE FUNCTION f_tag_id_array(VARIADIC text[])
  RETURNS int[] IMMUTABLE PARALLEL SAFE LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN COALESCE(NULLIF(ARRAY(SELECT id FROM tags WHERE tag = ANY($1)), '{}'), '{0}');
END
$func$

Then use it in your query:

SELECT *
FROM   media
WHERE  tag_ids @> f_tag_id_array('kittens', 'puppies')
ORDER  BY rating DESC
LIMIT  20;

Since Postgres now assumes that f_tag_id_array() is immutable it may choose to evaluate it early and continue with the returned constant - thereby achieving the different query plan for known rare IDs you were after.

Note that we are lying about IMMUTABLE - it is not. So don't build an index on it. And it's not safe to use with concurrent write access on table tags.

And since we are lying there, the function cannot be inlined, so using a simpler SQL function won't buy anything in this case. Rather have the saved query plan feature of plpgsql.

The same worked with STABLE on a Postgres 11 instance, while a Postgres 10 instance required the IMMUTABLE hack ...


Postgresql roll backward recovery Im familiar with postresql WAL file and its purpose. Every transaction and the commit records of each transaction will be logged in this file .While recovery, it replays from the last checkpoint till the end to bring the database to a stable state. But there is a good chance that the uncommitted transactions would have made their way into the WAL files. How does postgres escape these uncommitted transaction records from being replayed? How is roll-backward recovery(UNDO ing the uncommitted transactions) implemented in postgresql?

The uncommitted transactions are not escaped.  They do get replayed.  However, any process running across the new data from these transactions know to ignore them as they are not committed.  And any data marked for removal by an uncommitted transaction is not ignored, because the removal has not yet been committed.  This is the same way that in-progress transactions are ignored during normal operation, i.e., when there has been no crash.

Once the transaction is known to be aborted, then anyone who stumbles upon data from that transaction can clean it up.  Once recovery has finished, any transaction which has not had its commit record replayed by the end of recovery is known to be aborted.


Postgres : Truncate if exists in psql function with parameter I am trying to get a psql function that would truncate a given table name if it exists. I've been trying multiple functions, but none of them worked so far. Here is the code :

CREATE OR REPLACE FUNCTION truncateIfExists(tableName TEXT)
returns void
as $$
BEGIN
EXECUTE format(
'IF EXISTS (
    SELECT *
    FROM information_schema.tables
    WHERE table_name =' || tableName || '
    )
THEN
TRUNCATE tableName;
END IF;
');
END;
$$language plpgsql

Now, I am able to get it to work in a simple procedure with a harcoded name :

do $$
begin
IF EXISTS (SELECT *
 FROM information_schema.tables
 WHERE table_name = genre_epf)
 THEN
 TRUNCATE genre_epf;
END IF;
end
$$;

But I can't wrap my head on how to mix both queries. What am I doing wrong here ?

Use the variable FOUND:

create or replace function truncate_if_exists(tablename text)
returns void language plpgsql as $$
begin
    perform 1
    from information_schema.tables
    where table_name = tablename;
    if found then
        execute format('truncate %I', tablename);
    end if;
end $$;

Note that I've used PERFORM instead of SELECT as I don't need an output of the query. I want to know whether the query returns any row (FOUND = true) or not (FOUND = false).


Why would CREATE INDEX with "online" mode suddenly slow my database down? Earlier I used CREATE INDEX ... WITH(ONLINE=ON) without any problems - the statement would run in parallel with user queries and not slow them down at any time. Then suddenly I see that once I start the statement the database slows down greatly - user queries pile up, usually with PAGEIOLATCH_SH wait mode and the database effectively becomes unusable until I cancel the statement. The slowdown begins immediately after I initiate the statement execution. It happens both when I try to create an index that differs from existing indices and when I try to create an index identical to one of existing indices (with a different name of course).

What could possibly cause such change?

The OIB is causing your DB to run over some SLO limit (most likely IO or Log IO), causing resource governor to kick in. See Azure SQL Database resource limits.


SQL Server Database Table Partitioning Consideration I have a SQL Server table with 42 million + records. This table acts as a staging area for transaction records loaded from a different system. Data from this staging table have to be transformed and moved into a data warehouse database, which is done using stored procedure and this involves complex join on other staging tables of similar size.

Below is the structure of staging table:

Id
SystemCode
TransactionDate
ProcessStatus
DataField1
DataField2
DataFieldN

I am thinking about partitioning the staging tables on the SystemCode field. I have around 40 distinct systems from which data will be loaded. Once the records are processed, 'ProcessStatus' flag will be updated to prevent further processing.

Does anyone think this is a bad idea? My aim is to have them loaded as quickly as possible into data warehouse. I have considered indexes and the result was not great. I can't partition on date as the load to warehouse won't be based on this criteria.

Appreciate your help. Thanks.

The real use case for table partitioning is fast load and unload of data.

If your warehouse tables and staging tables are in the same database, and have the same schema, you will be able to swap a partition from staging to warehouse very quickly.  Similarly, when data has reached its retention expiry date and must be purged, it is very quick to delete a whole partition of data.  This page and its ilk will give some pointers.

As each partition can be directed to a different filegroup your backup & recovery cycles could be shortened at the cost of increased complexity.  For example, load one company's data, process it, then take a backup of the file(s) that hold that company.  Perhaps several companies could be processed in parallel, knowing that file contention will be reduced through partitioning?

Indexes can be partitioned as well as the tables.  Index maintenance can be performed one partition at a time.  This could reduce contention and overall load on your system.

Using partitions solely for performance enhancement can be problematic. Every query will have to have the partition key in the predicate, as a minimum.  Sometimes the optimiser chooses not to perform partition elimination even then.

Partitioning is no free lunch.  It has costs as well as benefits.  You must consider both sides and test well.  As so often in DB design "it depends."


Round-tripping column datatype causes size of table to grow Table LogCount:

Column1 int       not null
Column2 int       not null
Column3 int       not null
[Date]  datetime  not null
[Count] float(53) not null

This table contains ~86 million rows and has the following indexes:

alter table LogCount add constraint PK_LogCount_Id primary key clustered
( [Date], Column1, Column2, Column3 )
go
create nonclustered index IX_Column2_Date on LogCount ( Column2, [Date] )
include ( [Count] )
go

sp_spaceused gives the following:

name        rows        reserved    data        index_size  unused
LogCount    85800181    8089216 KB  4226664 KB  3860968 KB  1584 KB

The Count column doesn't and never will store floating-point numbers, only integers, so I changed it to a smallint which (I expected) will save 6 bytes per row (float(53) = 8 bytes, smallint = 2 bytes):

drop index LogCount.IX_Column2_Date
go
alter table LogCount alter column [Count] smallint not null
go
create nonclustered index IX_Column2_Date on LogCount ( Column2, [Date] )
include ( [Count] )
go

Then I reran sp_spaceused:

name        rows        reserved    data        index_size  unused
LogCount    85800181    7670848 KB  5255528 KB  2414496 KB  824 KB

As expected, the index size has decreased drastically, but the data size has increased by a gigabyte!

I then reran the drop/alter/create statement above but using int (4 bytes) and got the following result:

name        rows        reserved    data        index_size  unused
LogCount    85800181    7848032 KB  5255528 KB  2591688 KB  816 KB

Then I tried float(1) (also 4 bytes):

name        rows        reserved    data        index_size  unused
LogCount    85800181    7848016 KB  5255528 KB  2591672 KB  816 KB

Finally I went back to the original float(53):

name        rows        reserved    data        index_size  unused
LogCount    85800181    10680584 KB 7726896 KB  2952464 KB  1224 KB

Compared to the original, the data size has increased by ~3.3 GB (almost doubled) while the index size has decreased by ~900MB.

A colleague suggested the culprit could be that MSSQL is allocating additional pages for the alter column statement and not freeing them afterwards, so I also tried executing dbcc shrinkdatabase after each step, but the results were the same.

So my questions:

Why does altering a column from a larger to a smaller datatype, cause more data space to be used?
Is sp_spaceused reliable? If not, what should I be using instead? If there isn't a better option, how do I determine if changing a column's datatype will have a positive or negative effect on disk space usage?

When changing the data type of a column, SQL Server will choose to either:

Change the metadata only;
Change the metadata and read all the existing values to ensure they fit; or
Change every row physically

Even if every row must be changed, SQL Server still take steps (where possible) to prioritize speed over final size, on the basis that we want DDL changes to complete as quickly as possible. Optimizing the storage space can wait for a maintenance window.

Changing float to smallint can be accommodated within the existing space allocated for the row, but it does leave some unused space. As has been mentioned, this can be reclaimed by fully rebuilding the changed structure.


database size limitations I am accidental DBA. One of my clients has been trying to upload a data file and he's been encountering  error message about database size limitation. He hasn't sent us the exact error information but has asked if the database is set to autosize. I have heard tables being set to auto resize, database autoshrink feature..but what exactly is database autosize? I checked the database properties and the data file is set an initial size and autogrowth is enabled and is set to unrestricted growth and the transaction log has a restricted growth feature enabled.
Could you help me here?

Database size is limited in the express edition only. In other editions the size is limited only to your storage subsystem restrictions. If it is not express edition, ask your client if it is the full transaction log file issue which he is interpreting as a database size limitation. In that case you either need to back up the log files or increase log file size or truncate the log file with dbcc, depending on your database environment, storage subsystem and recovery model.


Sharding Postgres Database I have a Postgres database that has grown to the size where it is no longer feasible to store everything on a single database node. There is a Customer table in my schema where each row represents a (surprise!) customer. Every other table in my database is foreign keyed on this customer table and I would like to shard my database along customer ranges. For instance, I want customers with an id of 1 - 100 to go to database node A, 101 - 200 to go to database node B, and so on.

I've found information on table partitioning but I've found little else that shows me how I would enable database partitioning in Postgres.

What are my options for sharding a database in Postgres? If sharding is not possible, what are my alternatives?

To the extent your bottleneck is in streaming realtime reads and writes, you may want to look into the open source PostgreSQL extension: pg_shard

It shards and replicates your PostgreSQL tables for horizontal scale and high availability. It also distributes your SQL statements, without requiring any changes to your application.

https://github.com/citusdata/pg_shard


When does SQL Server acquire locks? The list of isolation levels in SQL Server found here states that write locks acquired within a transaction are retained until the end of the transaction. However it doesn't mention anything about when these locks are acquired.

Are locks by default acquired at the beginning of a transaction, or just when they are needed? If the latter is true, would it therefore be advantageous in large transactions to perform write operations as late as possible in order to minimize the amount of time that X locks are held?

Are locks by default acquired at the beginning of a transaction, or just when they are needed?

Locks are acquired immediately before reading or writing occurs. Depending on the locking granularity selected by the storage engine, locks may be acquired at the row, page, partition, or object (table) level.

  If the latter is true, would it therefore be advantageous in large transactions to perform write operations as late as possible in order to minimize the amount of time that X locks are held?

Yes, it might be advantageous for concurrency, depending on the isolation level in use by concurrent transactions.

Related reading:

Lock Escalation (Database Engine) - Books Online
Understanding SQL Server Concurrency - Kalen Delaney (free ebook)


How can I select the latest record having one state where no later records exist with any other state? I want to select the last record (by datetime) having a given state where no later records exist with any other state for each given "widget".

widget     state    timestamp
------     -----    ---------
foo        up       2011-02-11 12:34:56
foo        down     2011-02-10 10:01:10
bar        down     2011-02-09 09:00:20
bar        up       2011-02-08 08:11:20
baz        down     2011-02-07 07:04:18
baz        up       2011-02-06 06:05:18

and many more such records - more than two per widget and more than the two states shown. The timestamps are not ordered (they records are inserted without regard to the timestamp). The unique key is an ID that is generated externally (by another application).

For example, I want to select records with state = "down" and the result should include the records for "bar" and "baz", but not for "foo" since there is a later state for "foo" that is not "down".

I'm using a natural self join to select the latest event for a widget for a different query and think that maybe building on that in some way would work for this one.

SELECT widget, MAX(`timestamp`) AS ts
FROM tableX AS t
WHERE state = 'down'
GROUP BY widget
HAVING NOT EXISTS
       ( SELECT *
         FROM tableX AS tt
         WHERE tt.widget = t.widget
           AND tt.state <> 'down'
           AND tt.`timestamp` > MAX(t.`timestamp`)
       ) ;

I think that you'll need two indices, one on (widget, state, timestamp) and one on (widget, timestamp, state) for efficiency.

This will work, too, and will be needing only one index, on (widget, timestamp, state):

SELECT t.widget, t.`timestamp`
FROM
        tableX AS t
    JOIN
        ( SELECT widget, MAX(`timestamp`) AS ts
          FROM tableX
          GROUP BY widget
        ) AS tm
            ON  tm.widget = t.widget
            AND tm.ts = t.`timestamp`
WHERE t.state = 'down' ;

Tested both at SQL-Fiddle: test


How important are transaction log VLFs for performance? How important are VLFs when considering database performance? What would describe an optimal situation for VLFs?

What is a virtual log file?

SQL Server divides the transaction log file for each database into smaller chunks, called 'virtual log files' (or VLFs for short). Their primary function is as truncation markers at a log backup, i.e. the SQL Server will only clear (and mark available for re-use) VLFs that are completely empty. MSDN has a piece on Transaction Log - Physical Architecture.

What determines the number of VLFs?

Each time a log file grows (whether via autogrowth or manual growth), the new section of the log file is divided into a number of VLFs, purely based on the size of the new section (the existing transaction log is left alone). So, small autogrowth settings (i.e. the 10% autogrowth that is the default) will cause a large number of VLFs to be created.

What are the implications of a large number of VLFs?

The primary issue a large number of VLFs causes are:

Slow recovery (recovery is a phase during database restore where completed transactions are written to the data pages and incomplete transactions are rolled back).
Slow log-reader performance in a database mirroring setup.
Slow performance during database snapshot creation (note that this also includes certain functions of DBCC since they use database snapshots in the background to facilitate consistency checks without blocking).

How can I find out how many VLFs my database has?

DBCC LOGINFO will return 1 row for each VLF in your database's transaction log. This question has a couple of useful scripts for applying that across all databases on a server.

How many VLFs is too many?

That's a judgment call you'll have to make for yourself. My personal rule of thumb is that under 50 isn't worth messing with, and over 100 (or so) and I fix autogrowth settings and  make a mental note to (in the next maintenance window) shrink and regrow the log (as below).

Help! I have eleventy billion VLFs and my database recovery takes all day!

Short outline (from Kimberly Tripp's blog):

Make sure you're the only connection active in the database (so, do this during a maintenance window)
Backup the transaction log to disk (using BACKUP LOG)
Run DBCC SHRINKFILE with TRUNCATEONLY to shrink the log file to the smallest size possible.
Run ALTER DATABASE [...] MODIFY FILE [...] SIZE=newsize to resize your transaction log back up in a single step**.

** Note - if you have a very large log file (tens of GBs or more), you may want to resize in multiple steps to get an appropriate number of VLFs with an appropriate size to avoid excessively 'chunky' log backups. Since VLFs are the unit of truncation they also determine the log backup sizes, as detailed in Kim's blog.


SQL Server 2016 - SSRS Unable to Upload Report Models - smdl Files SSRS 2016 has major improvements and we've decided to migrate our 2008 servers. However, we and ran into a issue where we can't upload our old Report Models (.smdl files) via the web interface. All of the documentation states that these old models are deprecated, but will continue to work in SQL 2016.

Has anyone been able to figure this out? Can we use the SOAP web service to upload them? If so, do you have an example script?

Thanks

This worked for me --> creating an SSRS 2008 project and add the Report Models in there. Then deploy the report models to the SSRS 2016 server.


What does the wait type PREEMPTIVE_XE_CALLBACKEXECUTE mean? I have two instances in the last week where a DROP command for an Extended Events session has taken over a day to complete, sitting with the wait: PREEMPTIVE_XE_CALLBACKEXECUTE.

Can anyone explain what this wait type means?

More Background Info:
In the first instance, I ran a T-SQL command to drop the session and it completed a day later. Using sp_whoisactive, I saw that the query was waiting on PREEMPTIVE_XE_CALLBACKEXECUTE.

During this time, Object Explorer's queries to gather metadata were being blocked and recieving lock-timeouts, but there were no other complaints (or so I thought at the time).

I tried to drop another session on Friday, and the same behavior occurred, except it didn't just go away after a day as the with the first event. Instead, I found out this morning that the client application could not connect. It was being blocked by the DROP EVENT SESSION query.

A restart of the SQL Service cleared out the blocking query.

I can't find anything that helps to diagnose what this wait type this is... and why my event sessions won't drop like they should. Can you help?

Server Info: SQL 2008 R2 Enterprise with SP2

I believe this issue was fixed in SQL Server 2008 R2 SP2 Cumulative Update #1 (10.50.4260):

KB #2511963 - FIX: Query stops responding in SQL Server 2008 or in SQL Server 2008 R2 if the query alters or stops an Extended Events session

They don't mention your specific wait type but I have seen others who have and stated that this fix resolved their issue. So, if you are literally on SP2 with no CU (10.50.4000), you should consider at least that CU, or a later one (the most recent CU for 2008 R2 SP2 is CU7 (10.50.4286)).


How to import Azure blob file into Sql Server 2008 Is there a way to import Blob files into sql server 2008 other than using CLR ?

The file size is around 300MB so CLR isn't efficient enough. Is there a possibility to use BULK INSERT or OPENROWSET in 2008 server?

Example in Sql Server 2017 we can do this

CREATE DATABASE SCOPED CREDENTIAL AzureDevBlobImportCred
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'st=2018-12-05T.....'

go

CREATE EXTERNAL DATA SOURCE AzureDevBlobImportExternalSrc
WITH  (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://myStorageAccount.blob.core.windows.net', -- storage account URL
    CREDENTIAL = AzureDevBlobImportCred -- External Data Source
);

then we can use the external data source in bulk import

BULK INSERT #Mytemptable
FROM 'filename'
WITH (DATA_SOURCE = 'AzureDevBlobImportExternalSrc'
);

So do we have something like above to import blob files in Sql Server 2008 ?

Use AzCopy, the Azure Storage Explorer, or even the Azure Portal to download the file to somewhere the SQL Server can read it, then use BCP or BULK INSERT to load it.


Good database structure for scenario with orders that have a state and the state has a date so we have a table orders

with say:

id, product_id, order_state_id, comment

1, 5, 2, 'thisisatest'

we have a table order_state
with

id, name

1, pending processing

2, payment accepted

for each of the order states of an order, i must save a date as of when the state was performed. The order can only be in one state at a time.

say we have an order that gets put into payment accepted state. Now I have to save a date/time along with it.

what is the cleanest / best way of doing that?

Since you already put order_state_id in your orders table why you don't put a timestamp or a datetime column along with it?

When you update order_state_id to the desired state update the timestamp along with it.

If you think that there is a high probability that your status list will expand over time then you may have a need to keep history of status changes for each order. In order to do that you'll need to throw out order_state_id and the timestamp from orders and create an association table like that will link orders with order_state :

orders_state_history
record_id (optional)
order_id
state_id
timestamp/datetime


Install old Cumulative Update SQL Server 2016 Standard We were running on SQL Server 2016 Standard Edition 2016 SP2-CU4 on our ALWAYS-ON HA cluster.
Due to some recent issues one of our nodes got corrupted and we had to replace it with a new node. Since this was a fresh image from AWS Marketplace it came with SP2-CU13 (latest) out of the box.
The client wants this to be rolled-back to the original SP2-CU4 (no plans/requirement of upgrading to CU13).
As a primary step I tried to install the SP2-CU4 KB article on the instance but that does not work with the attached error message. (No SQL Server instance or feature can be updated on this computer)

Q1 - How can SP2-CU4 installed on the server.
Q2 - What is the impact if the cluster runs on different CU's.

You said you are running a new instance of SQL 2016 SP2 CU13. In order to install CU4, you will need to uninstall later CUs. I just spun up a new EC2 instance with SQL 2016 STD and went to Control Panel > Programs > Programs and Features > Installed Updates and saw Hotfix 5820, 5698, and 5598 after SP2. They should be uninstalled in the order listed from the newest to the oldest. After that you will be on SP2 and you can install SP4.


Copy Database error Index was out of range. Must be non-negative and less than the size of the collection Trying to "Copy Database" from SQL Server 2016 to SQL Server 2012 through SSMS. It fails, and destination server's event log says

  Index was out of range. Must be non-negative and less than the size of
  the collection. Parameter name: index

Edit Nov. 24, 2016
Tried Copying from SQL Server 2016 to another SQL Server 2016 and got same error.

"Trying to "Copy Database" from SQL Server 2016 to SQL Server 2012
  through SSMS."

You cannot copy a database from a 'higher' version (2016) of Sql Server to a 'lower' version of Sql Server (2012).  That's called 'downgrading' and it not supported.  There are numerous Internet posts on 'options' (like scripting individual objects, etc.) for doing this type of activity.

UPDATE 2/11/2017

There is an open connect item that appears to be addressing the same problem you're having


how to handle so much multiple value strings Possible Duplicate:
  Multiple values in lookup fields

i have a table has about 27 fields are just strings
and every one of them have multiple values
for example itemType Field have more than 10 possible values and the user may insert more

i made a tables called properties with those fields (Id, Key, Value) to store those values
but now i'm confused wither i should use just the (Id)s and reference them in the original table (i think it's no good to put 27 relationships) or doing something else which i don't know what is it exactly

any better ideas or is it ok to continue like this

thanks

To answer this question best we would need to a whole lot more specifics about what you are doing and what the kinds of value are going into each of the fields.

In general though since more values can be added to each set of possible values, a seperate table is advisable.  Unless the fields have overlap in their possible values it would be better to have each set of possible values as a seperate table.  In Oracle these could be Index Organized Tables.


Replication between SQL Server 2005 - 2005 I'm hoping to get some help with a SQL Server 2005 replication issue.  In short, in replication monitor, (On the Publisher), on the 'Distributor to Subscriber History' tab, I see two lines under 'Sessions of the Distribution Agent'.  One is running fine, while the 2nd one is showing the action message 'The process is running and is waiting for a response from the server'.  I now have 338694 undelivered commands 'supposably' according to this query:

Select sum(UndelivCmdsInDistDB) FROM [distribution].[dbo].[MSdistribution_status];

But - I just executed counts on one of the replicated tables on both servers, and the are spot on.  Therefore, I do believe the agent that is hung simply needs to be killed.

Tammy

P.S. Replication stopped at 10pm last night ; I believe that when I paused and restarted the SQL Server agent this morning on the Subscriber, the 2nd distribution agent session started and picked up with delivering the commands to the subscriber.

P.S.S - I have also been working to try to create some sort of alert for this situation, any thoughts on that?

I have found a solution to my problem. Beware though, the steps outlined below broke regular replication (or so it seems). Nevertheless, I was able to get replication running, and now there are no undelivered commands building up in the distribution database:

I Command You To Deliver Those Commands

The solution provided there is to run:

exec sp_changepublication
    @publication = 'xxxxx',
    @property = N'allow_anonymous',
    @value = 'false'
exec sp_changepublication
    @publication = 'xxxxx',
    @property = N'immediate_sync',
    @value = 'false'


Can I losslessly decompose this table? I've stumbled across a database design problem that's out of my league, and my go-to DBA guru is off on fire drills.

In essence, I have a table with the following primary key (PK for brevity):

child_id   integer
parent_id  integer
date       datetime

child_id and parent_id are foreign keys to entity tables. The "child" table itself also contains a foreign key to the "parent" table, and lo, each child_id always references the same parent_id as expected by the table above. In fact, turns out there's some extra code keeping the two in sync.

Which makes this overenthusiastic normalization novice say "I should remove the redundancy instead!"

I decompose to the following:

Table_1 PK:
child_id   integer
date       datetime

Table_2 PK:
parent_id  integer
date       datetime

Table_3: (already exists)
child_id   integer PRIMARY KEY
parent_id  integer FOREIGN KEY

And lo, when I join these guys together in the natural way, I recover the original table. It's my understanding that makes this 5NF.

However, now I realize there's a hidden business rule.

Normally, the dates associated with a given child_id must be a subset of the dates associated with the corresponding parent_id. You can see that the first table enforces this rule.

My decomposition does not enforce the rule, because you can freely add to Table 1 until the dates get too large.

Which leads me here, with the following questions:

Is this decomposition 5NF? While I'd say it permits insertion anomalies, it also seems to follow the Wiki example, which itself follows this guide. The phrase (emphasis mine) "we can reconstruct all the true facts from a normalized form consisting of three separate record types" gives me special pause, since no matter how much garbage I pump into Table_1, the natural join still ignores it.
Supposing I don't like this decomposition (I don't). I freely acknowledge that the practical solution is to leave the table and code as they are. But, in theory, is there a way to decompose and/or add constraints such that I get away from the first table and preserve my business rules?

Normalization is based on functional dependencies. Functional dependencies have to do with semantics; they have to do with what the data means. When you simplify a real-world problem to the level of "parent_id, child_id, date", and you don't include any sample data, you really limit how much help a conscientious database designer can give you.

The fact that you have a key {child_id, parent_id, date} in one table, and that you have (it seems) a unique pair {child_id, parent_id} in the child table doesn't necessarily mean that part of the combination is redundant. It might mean that in the table that has {child_id, parent_id, date} as the primary key, the pair of attributes {child_id, parent_id} ought to reference the child table in the first place.

If that's the case, you might use FOREIGN KEY (child_id, parent_id) REFERENCES child (child_id, parent_id). To do that, you need a UNIQUE constraint on the pair of columns (child_id, parent_id) in the table "child", which shouldn't be a problem if child_id is its primary key.

But there's no way to tell without knowing what the data means, and you're the only one in this thread who knows that. (But we'd be happy to let you explain it to us.)

As far as the original table is concerned, you seem to be saying that child_id -> parent_id. If that's the case, why is parent_id in the original table in the first place? Why isn't the key just (child_id, date), with a foreign key reference to the "child" table? It looks to me like the kind of redundancy you're talking about might be resolved by dropping the column "parent_id".

SQL DDL and sample data in the form of INSERT statements helps us help you. DDL and INSERT statements are more precise than descriptions.


FATAL: database does not exist in postgresql for jdbc I create a database by installing Postgres on my ubuntu 20.04 machine and used the following commands to create it:
sudo su - postgres
psql
CREATE USER preopus WITH PASSWORD 'admin222';
CREATE DATABASE preopusDB WITH OWNER=preopus;

and received no errors. When I tried using slick-codegen by calling the database with this URL:
"jdbc:postgresql://localhost/preopusDB?user=preopus&password=admin222", it gave me the error:

[error] (run-main-0) org.postgresql.util.PSQLException: FATAL: database "preopusDB" does not exist

If anybody can help it would be greatly appreciated.

As a_horse_with_no_name mentioned, the database name (and possibly other parameter values) in the JDBC URL are case-sensitive. But any unquoted identifier like preopusDB in your CREATE DATABASE statement will be folded to lower case.
Use
jdbc:postgresql://localhost/preopusdb?user=...


How to link Users and Jobs with Addresses in my database structure? If I have two entity types in my system: User and Job (a particular Job being an available position of employment somewhere in the country). Both entity types are defined by Addresses.
For example, a User will have a Home Address, a Locality, a Country, a Postcode, etc. A Job will also have this information.
Implementation considerations
Should I use one Addresses table or split it in two tables as, for instance, User_Addresses and Job_Addresses?

The idea of a single table is simple, but I don’t know what to think of it when it comes to mixing foreign Ids of two entity types that have nothing in common. I wouldn’t be able to add a foreign key because that column could hold an Id that is either defining a User or a Job.

On the other hand, I am not sure about having two tables with the same column types.

Business rules

We determined via comments that —regarding your business context and within the scope of your question— there are actually three different entity types of relevance, i.e.:

User
Job
Address

We also defined that there are two distinct relationships that involve said entity types, and you provided more details about such connections via your following comments:

  …[I]n theory two people living in the same place could use the system.
  …[A] User is tied to a single address.
  …[A] Job can only be associated with a single address at any given time. But two distinct Job's can have the same address.

Therefore, the significant business rules can be formulated as follows:

An Address houses zero-one-or-many Users
An Address locates zero-one-or-many Jobs

Logical model

So, I have derived a simplified IDEF1X1 logical model that depicts the aforementioned business rules in Figure 1:

In said logical model, you can see that I have represented the three discussed entity types separately, and established (a) the association between Address and User and (b) the conection between Address and Job by means of the migration2 of the Address PRIMARY KEY (PK) to both User and Job, which enforces two different one-to-many (1:M) relationships, as your scenario description demands.

Expository implementation

Consequently, I created an expository DDL structure based on the logical model presented above that can serve as a reference for a concrete implementation:

-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient INDEX strategies.

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

CREATE TABLE Country
(
    CountryCode     CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Country      PRIMARY KEY (CountryCode),
    CONSTRAINT AK_Country_Name UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Region
(
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Region                      PRIMARY KEY (CountryCode, RegionCode),
    CONSTRAINT AK_Region_CountryCode_and_Name UNIQUE      (CountryCode, Name) -- ALTERNATE KEY.
);

CREATE TABLE Locality
(
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    LocalityCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Locality                                 PRIMARY KEY (CountryCode, RegionCode, LocalityCode),
    CONSTRAINT AK_Locality_CountryCode_RegionCode_and_Name UNIQUE      (CountryCode, RegionCode, Name) -- ALTERNATE KEY.
);

CREATE TABLE MyAddress
(
    AddressId       INT      NOT NULL,
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    LocalityCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress                  PRIMARY KEY (AddressId),
    CONSTRAINT FK_from_MyAddress_to_Locality FOREIGN KEY (CountryCode, RegionCode, LocalityCode) -- Composite FOREIGN KEY.
        REFERENCES Locality (CountryCode, RegionCode, LocalityCode)

);

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    HouseAddressId  INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY (UserId),
    CONSTRAINT AK_UserProfile_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT AK_UserProfile_Username          UNIQUE      (Username), -- ALTERNATE KEY.
    CONSTRAINT FK_from_UserProfile_to_MyAddress FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)
);

CREATE TABLE Job
(
    JobNumber       INT      NOT NULL,
    AddressId       INT      NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Job                   PRIMARY KEY (JobNumber),
    CONSTRAINT FK_from_Job_to_MyAddress FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)
);

ACID Transactions

As in any relational database implementation, you should seriously consider using ACID TRANSACTIONS in order to protect the integrity and consistency of the data you are working with.

Making data retrieval easier with the use of a VIEW

If you want to access user and address data points directly from a single resource, it would be convenient to create a VIEW (i.e., a derived or virtual table) that displays columns that belong to the base tables named UserProfile and MyAddress, e.g.:

CREATE VIEW UserAndAddress AS

    SELECT U.UserId,
           U.FirstName,
           U.LastName,
           U.BirthDate,
           U.GenderCode,
           U.Username,
           A.CountryCode,
           A.RegionCode,
           A.LocalityCode,
           A.Postcode,
           A.Etcetera
      FROM UserProfile U
      JOIN MyAddress   A
        ON U.HouseAddressId = A.AddressId;

In this way, you can SELECT directly FROM the VIEW without having to write the JOIN clause and the condition every time you have to retrieve related information. Of course, you can employ a similar approach for job and address data.

Many-to-many relationship between User and Address

In case that you face the need to implement a many-to-many (M:N) relationship between the Address and User entity types —which would not be unusual in other contexts—, some small modifications to the business rules formulations would be required, as shown bellow:

A User keeps zero-one-or-many Addresses
An Address is kept by zero-one-or-many Users

Thus, I drew a new logical model that portrays this situation in Figure 2:

As demonstrated, an associative entity that connects (a) User with (b) Address is included, and I named it UserAddress accordingly; its PK is made up of two different FOREIGN KEYs, and each of them makes a reference to the respective entity type.

I coded an illustrative DDL structure with this alternative arrangement:

CREATE TABLE MyAddress
(
    AddressId       INT      NOT NULL,
    CountryCode     CHAR(2)  NOT NULL,
    RegionCode      CHAR(2)  NOT NULL,
    LocalityCode    CHAR(2)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress                  PRIMARY KEY (AddressId),
    CONSTRAINT FK_from_MyAddress_to_Locality FOREIGN KEY (CountryCode, RegionCode, LocalityCode) -- Composite FOREIGN KEY.
        REFERENCES Locality (CountryCode, RegionCode, LocalityCode)
);

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    AddressId       INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY (UserId),
    CONSTRAINT AK_UserProfile_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
   (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT AK_UserProfile_Username UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE UserAddress
(
    AddressId       INT      NOT NULL,
    UserId          INT      NOT NULL,
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,
    IsBilling       BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress              PRIMARY KEY (UserId, AddressId), -- Composite PRIMARY KEY.
    CONSTRAINT FK_UserAddress_to_User    FOREIGN KEY (UserId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FK_UserAddress_to_Address FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)
);

If the cardinality of the relationship among Job and Address also happens to be (or, who knows?, at some point in time becomes) M:N in your business domain, you can follow a method comparable to the one just detailed.

Notes

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

2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.


Design database where each field needs to have a checkbox I got into a problem designing a database where I have a table, where every field needs to be validated by a human.
for example:

name
number
ID card number

Each one of these fields need to be validated (kind of a checkbox).
which one is the correct approach here? have boolean fields in the same tablet or a different table with each field as boolean?

If the data has to be persisted between collection and verification, and each value can be accepted or rejected individually then there has to be a _verified column to match each _value column.
These verification columns are information about the thing to which they refer as much as the name and ID are so belong in the same logical entity type. Whether they go in the same physical table is a separate matter. If split this would be considered a vertical partitioning of the logical entity. There are good reasons to do that and good reasons not to.
If split the design and queries are more complicated. Likely response time will be fractionally better, though, since each table will be narrower. Both these can be swamped or mitigated by many other implementation details such as views or covering indexes.
Generally I'd keep a single table until proven otherwise.


Slow MySQL InnoDB performance of table with 100 million rows I have the database in this format.

CREATE TABLE `dna` (
 `p_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `dna_bits` bit(32) NOT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`p_id`),
 KEY `dna_bits` (`dna_bits`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Currently, I have 100 millions rows and I need to query quickly by dna_bits which I have an index on.

My query looks like

select CONV(dna_bits, 16, 2) as bits, user_id from dna where dna_bits in (b'010101', b'0101011', ...)

Searching 10000 dna_bits in where clause took me over 8 seconds.

EXPLAIN on my shorten* query showed this.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  dna range   dna_bits    dna_bits    4   NULL    77  Using where

I'm running MySQL server on my iMac 3.2 GHz Intel Core i5 with 16GB RAM.

What can I do to speed up the process?

Thanks.

Are there multiple rows for each dna_bits?  If not, then that column could be the PRIMARY KEY?  If you can do that, then the speed might double because you won't be bouncing between the secondary index and the PK.  Getting rid of p_id and the secondary key would save space.

How much RAM?  How big is the table?  (I would guess 5GB data+index.)  What is the value of innodb_buffer_pool_size?  If that can be set to 70% of available RAM and still be bigger than the table size, then (eventually) all the data you need will be cached.  Again a speedup.  (Can't tell how much.)  This is because of avoiding I/O.

10K lookups will take some time.


How to order by then start at first non null value and select a limit for number of values I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to offset to find the first not null value?

My line of code is below everything is working except I cannot get the offset set.

SELECT * FROM (SELECT @row := @row+1 AS rownum, ttime,temperature,humidity,outsidetemp
FROM (SELECT @row :=0) r, tempHumiditydata) ranked
WHERE rownum % 5 = 1
ORDER BY rownum DESC LIMIT 2020 ??????OFFSET (WHERE outsidetemp IS NOT NULL)????????

Additional background. The outsidetemp is being logged once for every 60 of the other data points the values in between these points is Null.

My code above is being used to graph the data so it is important I start at the first not null value to capture the remaining not null values (starting at the wrong point and I could not get any data for outsidetemp).

I am skipping every 5th row to reduce the amount of data in a wider time frame graph.

Here is an example of what I need
Ttime     temp        humidity        outsidetemp
8:00      71      50          NULL
7:00     72      49          80
6:00     73      48          NULL
5:00     73      48          NULL
4:00     73      48          NULL
3:00     69      51          76

Using only every other record starting at the first non null outsidetemp yields
7:00     72      49          80
5:00     73      48          NULL
3:00     69      51      76

I can then use the above to graph temp, humidity and outside temp versus time. I just interpolate between non null outside temp values.

Step 1:  Write a query that fetches only the outsidetemp values:  Use WHERE outsidetemp IS NOT NULL.  Put the results in a TEMPORARY TABLE.

Step 2:  Tack on an AUTO_INCREMENT

Step 3:  Fetch from it with WHERE (id % 5) = 1 to get every 5th, starting with the first available.

Step 4:  If you want other columns, then JOIN back to the original table USING(datetime).


Is it necessary to run apply-log immediately after innobackupexec has run? i have been trying to read other questions, but i did not find a answer to suit me. So excuse me if i ask something that has been already asked.

I'm trying to organize my self and backup all my mysql servers with innobackupexec (since i only use Percona Mysql). I have read percona documentation and some other blog/posts but i do not understand if i need to run --apply-log immediately after full backup has been run.
If i need to schedule a script as a daily basis backup, should i only script this?

innobackupex /data/backups

and should i only use

innobackupex --use-memory=4G --apply-log /data/backups/2010-03-13_02-42-44/

only when i need to restore the database?

i'm pretty confuse, hope to get here some clarification.

thank you

A quick 101 on Percona Xtrabackup:

Basically, what the tool does is copying the data live in an inconsistent way, but making sure it gets all the necessary changes that are happening at copy time, so it can revert it to a proper state. In other words, it does an "unclean full copy" and then a "controlled crash recovery" with a custom InnoDB instance. That is exactly why you need the two phases (the copy first and then the application of the log, where committed changes are reapplied and uncommitted changes are discarded).

That second part can be done on a different machine, without a database connection -that is why it is a separate command.

So you have two options: running the --apply-log as soon as the copy has ended or just before the restore.

Advantages of the first case: your copy is 100% ready to be restored in case of an emergency (just --copy-back it). You can delete the saved transaction log as it is not needed any more.
Its disadvantages: Once you have deleted the uncommitted changes, you cannot roll forward your full backup with following incrementals. Also, you will not be generating the extra information that may be needed for partial imports.

If you only intend to create in the future full backups and perform full restores, you can safely do it just after the copy finishes. If your apply-log phase is fast (or you do not care about the extra time and space of the transaction log) and you want to have extra flexibility (for partial/incremental backups), just wait until the start of the recovery.


MySQL replication mystery May I ask if its possible to have a table on Slave server. But it does not exist on Master Servers? how come this one happens? Thank you.

If the data is currently being updated on the replica, it could be due to a trigger (show triggers from [database];) or a replication rewrite flag (look at the my.cnf for master and slave).


Insert into ... (...) (Select...) on duplicate key update Can you help me to solve a problem. The task is to insert a record in MySQL database.
terms:

If the record exists - to update the record.
If the record does not exist - to create a new one.

The Key columns in the table 'NETFLOW'.'Month' are:

'Year'
'Month'
'Day'
'Hour'
'srcaddr'
'dstaddr'.

The thing is query as quickly as possible and if possible in one query.

The text of the current query is:

    INSERT INTO `netflow`.`Month` (`Year`, `Month`, `Day`, `Hour`, `srcaddr`,`dstaddr`, `doctets`)
    (
      SELECT
        YEAR( FROM_UNIXTIME(  `unix_secs` ) ) AS `YEAR`,
        MONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `MONTH`,
        DAYOFMONTH( FROM_UNIXTIME(  `unix_secs` ) ) AS `DAY`,
        HOUR( FROM_UNIXTIME(  `unix_secs` ) ) AS `HOUR`,
        `srcaddr` ,
        `dstaddr` ,
        `doctets`
      FROM  `netflow`.`raw`
        WHERE `unix_secs` <= 1394179200
      GROUP BY
        `YEAR`,
        `MONTH`,
        `DAY`,
        `HOUR`,
        `srcaddr`,
        `dstaddr`
    )
    ON DUPLICATE KEY UPDATE

...

if you want to change the value with the new one, you can use:

ON DUPLICATE KEY UPDATE
    doctets = VALUES(doctets) ;

or if you want to add the new value to the existing one:

ON DUPLICATE KEY UPDATE
    doctets = doctets + VALUES(doctets) ;


Log in as another SQL Server user via SSMS to check their permissions? I am getting a little further into the database administration side of things and starting to set up security access to SQL server databases for users. I have a question:

Once I have given a set of permissions to an AD authenticated user via SSMS, is there a way I can 'log in' as that user to the SQL server to check and confirm that I've given them the correct privileges? I'm also logged in as an AD user with my own permissions so I wouldn't be able to log in as their AD account ID/password.

Thanks :)

You can run SSMS as a different user via runas /netonly as described here.

However for that method you would need to know the other user's password.

You can also use EXECUTE AS to impersonate that user from inside a query window logged in as you (as long as you have the required permissions to do so).

EXECUTE AS 'user_name';

/* Some SQL Statements */

REVERT;

Or you could adopt a policy of never granting permissions to users directly but always granting them to Windows groups or database roles. Then you just need to have another dummy user that you can grant the same role membership to.


How to organize a development environment using Oracle XE 11g? I started developing small applications using Oracle XE 10g/11g using APEX. They are intented to be run on desktop computers that acts as a server in small offices. Oracle XE is well suited for those scenarios.

The problem I am facing is in my developing environment where I am not sure how to set up. I have each different system organized as a set of schemas in Oracle XE and that is working fairly good. My problem is the following:

I need to make some serious customization to one of those systems for a new client, but I need to keep a copy of the system as it is right now (support/maintenance) because I have other clients which are using it.

I would like some advice in how to organize this development environment because I'll be facing this problem more than once.

Thanks

Any serious development project involves source control and a deploy process - SCM (Software Configuration Management). Make sure you put all your database objects (table, index, packages etc..) under revision control. Put your APEX export-files under revision control. Tag them together with the ddl. This way you have a consistent point in time representation of your "system". Make use of the "continous integration" paradigm - build early - build often. Jenkins CI will be a good choice.

When all of this is in place, you branch off your "serious customization"!


Differential Backup Issue - Why? Is this Possible? I am using SQL Server 2014 and this is the situation:

I have server A and server B.
Overnight ETL is processed on server A.
After the loading process completes, database X gets backed-up (with CHECKSUM and RESTORE VERIFYONLY to ensure reliability) and then get sent to server B.
Server B receives the bak file and then restore the database there.

I want to use differential backup strategy so that:

Full backup is only done only on Saturday
i.e. Full backup on server A on Saturday -> ship to server B -> Restore the full backup on server B
The rest of the days will be differential backup
i.e. Differential backup on server A -> ship to server B -> Restore the differential backup on server B

I have tried but I got an error, saying:

  the log or differential backup cannot be restored because no files are ready to rollforward.

Not sure why. I checked sys.database_files on server A and server B, and I can see that the differential_Base_LSN and differential_base_GUID are the same.
Anywhere/anything else to check?

By the way, on step 2 above, when I am restoring the diff backup on server B, do I always need to restore both the Full backup + the differential backup each time?

I only restored the differential backup WITH RECOVERY (and got that error message) because the full backup was restored already the day before.

To clarify: Yes, I want the db on server B to be readable between differentials. How can I get around that? Is my only option to RESTORE FULL (WITH NORECOVERY) + RESTORE DIFF (WITH RECOVERY) combo sequence each night?

Any guidance will be much appreciated.

You don't need to mess with RECOVERY and NORECOVERY here, all you need is the STANDBY option. Here's a quick demo on how to use it.

Create a database, set it to simple recovery, and create a table.

Insert data, take some diffs.

Fun, right?

USE master;

/*Create a dummy database*/
CREATE DATABASE DiffRestoreTest

/*We simple now*/
ALTER DATABASE DiffRestoreTest SET RECOVERY SIMPLE

/*Context is everything*/
USE DiffRestoreTest

/*If nothing changes, do we even need a diff backup?*/
CREATE TABLE dbo.t1 (Id INT)

/*Take a full backup, dummy*/
BACKUP DATABASE DiffRestoreTest
TO DISK = 'F:\Backup\DRT_FULL.bak'
WITH INIT, FORMAT, COMPRESSION

/*Make a change*/
INSERT dbo.t1 (Id )
VALUES ( 1 )

/*Take a differential backup*/
BACKUP DATABASE DiffRestoreTest
TO DISK = 'F:\Backup\DRT_DIFF_1.bak'
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 2 )

/*Take another diff backup*/
BACKUP DATABASE DiffRestoreTest
TO DISK = 'F:\Backup\DRT_DIFF_2.bak'
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

/*Make another change*/
INSERT dbo.t1 (Id )
VALUES ( 3 )

/*Take another diff backup*/
BACKUP DATABASE DiffRestoreTest
TO DISK = 'F:\Backup\DRT_DIFF_3.bak'
WITH INIT, FORMAT, COMPRESSION, DIFFERENTIAL

Yeah, I lied. That's the boring part.

You can restore your Full backup in STANDBY:

/*Exit stage left*/
USE master

/*Restore the full backup*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_FULL.bak'
WITH REPLACE, STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

You can restore Diffs in order with STANDBY:

/*Square one*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_1.bak'
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*Square 2*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_2.bak'
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*Square 3*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_3.bak'
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

And unlike those pesky Log files, you can skip ahead when restoring Diffs, too:

/*Restore the full backup*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_FULL.bak'
WITH REPLACE, STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

/*What happens if I try to jump the restores?*/
RESTORE DATABASE DiffRestoreTest
FROM DISK = 'F:\Backup\DRT_DIFF_3.bak'
WITH STANDBY = 'F:\Backup\DRT_STANDBY.tuf'

If you want to test readability, just run this in between restore commands. You should see IDs increment with each. If you don't, you did something horribly wrong.

Keep in mind that when you restore files, it will kick any end users out of the database, and it won't wait for their queries to finish.

This also leaves the database in a read only state, no changes can be made here.

SELECT *
FROM DiffRestoreTest.dbo.t1 AS t

And finally, clean up after yourself.

/*Bring'er online, lad*/
RESTORE DATABASE DiffRestoreTest WITH RECOVERY

DROP DATABASE DiffRestoreTest

Hope this helps!!


SQL Server 2005 Express in VMware causing very high CPU load I'm having the problems described in KB937745 - very high CPU usage and the Application Log is reporting something like this:

  The client was unable to reuse a session with SPID SPID, which had been reset for connection pooling.

I've downloaded the hotfixes and I can't run them - I suspect it is because SQL Server 2005 Express Edition is not in the "Applies to" section of the KB.

The machine is running on an ESX 3.5 host running Windows XP (patched).

Result of:

SELECT
    SERVERPROPERTY('productversion'),
    SERVERPROPERTY ('productlevel'),
    SERVERPROPERTY ('edition')

is

9.00.3068.00    SP2 Express Edition

Any ideas? I'm stumped why the CPU is getting pegged. This is a product from a vendor that has never seen this kind of problem with several installations.

I found an article that might be of help.

Edit: The link above is now dead, but the Wayback Machine has a copy here.

The blog post states that

  Finally we have identified the root cause of the issue is due to the activity on the database causing heavy usage of MARS [Multiple Active Result Sets]

which was a feature introduced in SQL Server 2005 (ref: here).


Two conversions of practically the same number giving two different results To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle...

This

Select convert(numeric(19,2),convert(float,9.76499999999999999999999999))

gives 9.76.

And this

Select convert(numeric(19,2),convert(float,9.7649999999999999999999))

gives 9.77.

Can someone explain why one truncates while the other rounds up?
This is causing a nightmare for me.

I wish I could just stop using floats but they are all over our application, which has been ongoing for over 30 years.

For what it's worth, this behaviour changes in SQL Server 2016. When I try against 2017 with COMPATIBILITY_LEVEL = 120 I see your observed behaviour. When COMPATIBILITY_LEVEL = 130 both input values produce the same rounded output value 9.77.

There is some documentation here but it is not conclusive. I do not have 2014 documentation to hand but it would be interesting to compare for differences.

You may find some helpful information in the Microsoft Support article SQL Server and Azure SQL Database improvements in handling some data types and uncommon operations.


Schema and Query design for table with multiple languages I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and another column(s) with the translated text in that language.
So I decided in the following schema:

CREATE TABLE IF NOT EXISTS Meowficer (
    meowficer_id SMALLINT PRIMARY KEY,
    skill_id SMALLINT UNIQUE NOT NULL,
    -- Cut out irrelevant columns.
);

CREATE TABLE IF NOT EXISTS MeowficerName (
    meowficer_id SMALLINT NOT NULL,
    language TEXT NOT NULL,
    name TEXT NOT NULL,

    FOREIGN KEY(meowficer_id) REFERENCES Meowficer(meowficer_id)
);

CREATE TABLE IF NOT EXISTS MeowficerSkill (
    skill_id SMALLINT PRIMARY KEY,
    next_id SMALLINT NOT NULL,
    level SMALLINT NOT NULL,
    experience SMALLINT NOT NULL,
    icon TEXT NOT NULL,

    -- Only one Skill for every Mewoficer.
    FOREIGN KEY(skill_id) REFERENCES Meowficer(skill_id)
);

CREATE TABLE IF NOT EXISTS MeowficerSkillNameDesc (
    skill_id SMALLINT NOT NULL,
    language TEXT NOT NULL,
    skill_name TEXT NOT NULL,
    skill_1 TEXT NOT NULL,
    skill_2 TEXT NOT NULL,
    skill_3 TEXT NOT NULL,

    FOREIGN KEY (skill_id) REFERENCES MeowficerSkill(skill_id)
);

And I can query the data I need this way:

-- Cut out part of the column selecting from the Meowficer table.
SELECT
    Meowficer.id, Meowficer.skill_id, MeowficerName.name, ..., MeowficerSkill.icon, MeowficerSkillNameDesc.skill_name, MeowficerSkillNameDesc.skill_1, MeowficerSkillNameDesc.skill_2, MeowficerSkillNameDesc.skill_3
FROM
    Meowficer
JOIN
    MeowficerName ON Meowficer.id = MeowficerName.id AND MeowficerName.language = 'ja' AND MeowficerName.name = 'ジャスティス'
JOIN
    MeowficerSkill ON Meowficer.skill_id = MeowficerSkill.skill_id AND MeowficerSkill.level = 2
JOIN
    MeowficerSkillNameDesc ON MeowficerSkill.skill_id = MeowficerSkillNameDesc.skill_id AND MeowficerSkillNameDesc.language = 'ja'

While the data sample I'm currently working with isn't large at all and when I tried the code above it worked without any issues, so: is this the best approach for a multi language database schema and querying? Can it be improved on?

Your data model is good.

The only addition I'd make is to add a primary key to the “language” tables that consists of (*_id, language). Every table should have a primary key, and there should be an index on the foreign key column.

Another thing you could do is to use an enum type or a lookup table for the language rather than using text. That avoids typos.


How we can design best optimized solution: Generate huge random numbers and check in database table whether this numbers are there or not We have one table like:

Table Name:

random_number_series

Its columns are as below:

number      [numeric(10)]
name        [varchar(10)]
status      [tinyint(1)]
type        [tinyint(1)]
createdDate [datetime]

This table have around 250-300 million rows.

Now our requirement is that we will generate random numbers in big quantity i.e. 1 million. We want to check that this generated numbers are there in above table (with number column) or not.

How best we can do this?

Note:

-> Random numbers will be generated in application logic.

-> I want to know strategy / pseudocode how best we can design?

Put the million numbers in a table.  Then JOIN the tables together to see which ones exist.  Or LEFT JOIN ... IS NULL to see which are missing.


Right way to delete the archive - Oracle So I have an Oracle database (standalone) running in RHEL and I have scheduled RMAN for backups on daily and weekly basis. Sometimes my file system (archive mount-point) becomes full because of high archive generation. Now what is the right way to delete the archives from the file system?

My RMAN script has this

delete archivelog all completed before 'SYSDATE-5'

but the above command isnt deleteing the archives properly as I see, some of the very old archives are still there..

How to delete them properly?

Should I run crosscheck archivelog all? and then run the above command once again? What is the right way?

Thanks!

In order to delete them you can do: RMAN>crosscheck archivelog all;
RMAN>delete noprompt expired archivelog all; , you can also include the delete input clause when you back them up, and they will be deleted after they have been backed up (it is up to you). You can try and run the command you have shown manually to see what will happen in RMAN (in other words, does it work or does it give an error).


SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails I am trying to install Zabbix by following this guide. I ran into the following problems when trying to run data.sql after running scheme.sql successfully

mysql -D zabbix -uzabbix -pPassword < /home/zabbix/zabbix-2.4.5/database/mysql/schema.sql

mysql -D zabbix -uzabbix -pPassword < /home/zabbix/zabbix-2.4.5/database/mysql/data.sql

  ERROR 1452 (23000) at line 2989: Cannot add or update a child row: a
  foreign key constraint fails ('zabbix'.'sysmaps_elements', CONSTRAINT
  'c_sysmaps_elements_2' FOREIGN KEY ('iconid_off') REFERENCES 'images'
  ('imageid'))

I cannot figure out why I would not be able to add or update a child row , it looks perfectly fine to me.

The scripts can be downloaded from here

You can update your data.sql with a header and a footer like the following :
-- Add these lines as a header to your MYSQL data script
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Add these lines as a footer  to your MYSQL data script
 UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

This will enable you to restore data without any foreign-cheking.

updated :
you can re-build you data.sql file by including the header and footer :

cat header.sql data.sql footer.sql > data_NEW.sql


PostgreSQL dblink password encrypted or not? I have two PostgreSQL servers:

server 1: 192.168.0.1
server 2: 192.168.0.2

I want to connect to the database on server 2 from server 1. I do it like this:

SELECT dblink_connect('hostaddr=192.168.0.2 port=5432 dbname=mydb user=postgres password=mypasswd');

My question is that when I do such connect my password is send as plaintext or its somehow encrypted during the transmission to the server 2?

I use PostgreSQL 9.3 on Linuxx Mint 16 (server 1) and Xubuntu 13.10 (server 2).

Basically, dblink uses a libpq-style connection info string.
Add sslmode=require to your connection string or a more restrictive setting to disallow unencrypted transfers. More details in the manual here.

I would also advice not to have the password in your SQL statement at all. SQL statements may get logged. Or backups are stored in an insecure location ...
I use the password file .pgpass for dblink connections.


postgres jsonb query performance is decreased by order by I have a table with jsonb column. When I execute query below it evaluates quite fast. But then I need to add order by clause (created column is timestamp column). Execution time decreases by 1000 times.

select * from delivery where (document #> '{links}') @> '[{"type": "receipt","externalId": "9990863500764120180212"}]' limit 100;

Limit  (cost=0.00..4198.36 rows=100 width=137) (actual time=0.100..2.351 rows=100 loops=1)
  ->  Seq Scan on delivery  (cost=0.00..6843.32 rows=163 width=137) (actual time=0.099..2.338 rows=100 loops=1)
        Filter: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
        Rows Removed by Filter: 1
Planning time: 0.184 ms
Execution time: 2.384 ms

select * from delivery where (document #> '{links}') @> '[{"type": "receipt","externalId": "9990863500764120180212"}]' order by created limit 100;

Limit  (cost=6423.11..6434.78 rows=100 width=137) (actual time=2243.874..2270.206 rows=100 loops=1)
  ->  Gather Merge  (cost=6423.11..6438.98 rows=136 width=137) (actual time=2243.872..2270.187 rows=100 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=5423.09..5423.26 rows=68 width=137) (actual time=2237.522..2237.617 rows=251 loops=3)
              Sort Key: created
              Sort Method: external sort  Disk: 8552kB
              ->  Parallel Seq Scan on delivery  (cost=0.00..5421.02 rows=68 width=137) (actual time=0.160..2152.447 rows=48670 loops=3)
                    Filter: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
                    Rows Removed by Filter: 5518
Planning time: 0.556 ms
Execution time: 2272.830 ms

I tried to create index on created field - nothing happened. How can I improve performance of this query?

UPD I've tried to use indexes (btree on created and gin on links) as @LaurenzAlbe adviced.

Limit  (cost=601.36..601.61 rows=100 width=139) (actual time=4833.531..4833.555 rows=100 loops=1)
  ->  Sort  (cost=601.36..601.76 rows=163 width=139) (actual time=4833.529..4833.544 rows=100 loops=1)
        Sort Key: created
        Sort Method: top-N heapsort  Memory: 74kB
        ->  Bitmap Heap Scan on delivery  (cost=41.26..595.37 rows=163 width=139) (actual time=29.329..4801.109 rows=146010 loops=1)
              Recheck Cond: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
              Heap Blocks: exact=4128
              ->  Bitmap Index Scan on delivery_expr_idx  (cost=0.00..41.22 rows=163 width=0) (actual time=28.531..28.531 rows=146010 loops=1)
                    Index Cond: ((document #> '{links}'::text[]) @> '[{"type": "receipt", "externalId": "9990863500764120180212"}]'::jsonb)
Planning time: 1.144 ms
Execution time: 4833.618 ms

Remove the GIN index delivery_expr_idx you created and make sure that created still has an index.

Then try

SELECT *
FROM (SELECT *
      FROM delivery
      ORDER BY created
      OFFSET 0 LIMIT 10000) AS q
WHERE document @>
      '{"links": [{"type": "receipt","externalId": "9990863500764120180212"}]}'
ORDER BY created
LIMIT 100;

I'm cheating with the LIMIT 10000, but since almost all rows satisfy the WHERE condition, I hope to get away with it.


Symmetric difference operation in Transact-SQL? I have always known about the UNION operator in SQL, but only recently discovered that there were other set operators, INTERSECT and EXCEPT. I haven't been able to find an operator that does the fourth big set operator, the symmetric difference (e.g. the opposite of INTERSECT.)

It looks like I can get the desired output by using something like

SELECT Field FROM A UNION SELECT Field FROM B
EXCEPT
SELECT Field FROM A INTERSECT SELECT Field FROM B

(assuming I got the precedence right), or by doing an anti-full-join:

SELECT A.Field, B.Field
FROM A
FULL JOIN B ON B.Id = A.Id
WHERE B.Id IS NULL OR A.Id IS NULL

But both of those look like rather intensive queries, especially compared to the other three basic set operations. Is there a symmetric difference operation in SQL and I just can't find it in the documentation? Or is there a "canonical" way to implement it in T-SQL?

All set operators are translated to joins or join-like operators. You can witness that in the query plan.

For that reason the full outer join that you have there is the most efficient you can do. Disregarding, of course, the hopefully rare situation in which the optimizer picks a bad plan and a rewrite happens to perform better by luck. This can always happen.


Cannot start psql with user postgres “Could not change directory to /home/user ” I did a silly mistake. I wanted to set postgres permissions to a folder for exporting data from my database. So I set something like sudo chmod 777 -u postgres /home/user and then my home partition only has root permissions and nothing worked without root permissions. I solved this problem by using sudo chown user /home/user and then I set the permissions manually in the file manager on linux. It was very stupid, I didn't knew very much about allocating permissions to directories and users.

The Problem is now, that I can't connect to my database anymore.

 ~ $ sudo -u postgres psql
[sudo] password for user:
could not change directory to "/home/user"
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I think I want to connect in a folder where the postgres user can't read.

Restarting the server with sudo service postgresql restart doesn't work as well.

I'm using PostgreSQL 9.1.

I solved the problem with the help of a relative.

The first problem was that my /home and /home/user folder didn't have the right permissions.

We set them correctly with chmod og+rX /home /home/user.

Then I forgot, that I created a new PGDATA directory in /home/user. And this directory had the wrong permissions. So we set them again for the user postgres with chown postgres:postrgres -R /home/user/PGDATA.

I decided to place the PGDATA directory in /home/user because of huge log-files that where produced during raster data queries with my PostGIS/PostgreSQL database. Sometimes I'm using Sublime Text 2 for queries and I quit them without stopping the database process and then the logfile became larger and larger and I was running out of space and nothing worked anymore.


After bulk load, how to ensure an updated visibility map? We have a number of tables that have an active version, an inactive version, and a view pointing at the active one.  We periodically truncate the inactive table, drop the indexes, load a bunch of data into the table, re-build the indexes and redefine the view to point to the newly loaded table.  This is reasonably standard data warehouse stuff.

The issue we're running into is that we are unable to use index-only scans on the freshly-loaded tables after our script runs due to the visibility map not being updated.  I tried adding a VACUUM step to our script, using pg_background.  I've tried both VACUUM ANALYZE and VACUUM FREEZE ANALYZE, but neither will update the visibility map if there's a transaction that started before the VACUUM.

The only workaround I have right now is to fail out of the script if pg_class.relallvisible = 0 for the backing table after the VACUUM FREEZE, but this is not ideal.  Are there any other ways to force the visibility map to update?

To be clear, there will be no user activity on the inactive table until the script updates the view definition, so I'm free to do pretty much anything that I want to it, but performance is somewhat of an issue (e.g, I would not want to VACUUM FULL) and my scripts can't hold any locks that would prevent selects on other tables.

You can set old_snapshot_threshold and then wait out that amount of time before retrying the VACUUM.

(Note that this is a last resort.  First resort would be just to identify the long running connections, and making them go away if they are not really needed, or downgrading their isolation level).


CPU,Memory and disk I/O utilization for each query in mysql How can we identify the exact CPU,Memory and disk I/O, when a query is executed. I am using mysql 5.7.20 community version. I am able to get only cpu details for session level by setting SET profiling=1. How can we identify all these details in global level for all the sessions.

Using IOTOP command you can see CPU,Memory and disk I/O utilization for each query. For each query mysql generates an ID. You can find CPU,Memory and disk I/O utilization against your query ID.

How ever you may find more command in detail at http://www.dbatodba.com/db2/useful-aix-commands/monitor-cpu-mem-and-i-o-usage/

SET profiling = 100
-- Now you can find query ID and running sql query by
SHOW PROFILES; IF you want TO CHECK IN detail FOR ANY QUERY, run this QUERY-
-- SHOW PROFILE FOR QUERY QueryID;
-- If you want to check CPU USAGE for any query, run this query-
SHOW PROFILE CPU FOR QUERY QueryID;


Virtual Oracle server for education? I am currently a College Student and am taking an introduction to Oracle 10G. I cannot seem to make it to class all of the time and am curious to ask if there is any Virtual Oracle Server or something along those lines that would allow me to have a (in a nutshell) Virtual Database in which I can connect to via SQL* Plus and enter commands that I am learning? I am running Windows 7 Home Premium.

Thank you so much!

You can download and install the Oracle Database XE. It's free and has a default database that will be useful for your tests. You could search for an online SQL simulator, but it's not as useful as your own machine.

Good luck with school and stop skipping classes. At least database classes, skip others :-).


Calculating balance sheet with a single query I have this schema for double entry bookkeeping system:

CREATE TABLE account (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  parent_id INTEGER REFERENCES account(id),

  UNIQUE (name, parent_id)
);

CREATE TABLE journal (
  id    SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  date  DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE posting (
  id          SERIAL PRIMARY KEY,
  account_id  INTEGER NOT NULL REFERENCES account(id),
  journal_id  INTEGER NOT NULL REFERENCES journal(id),
  amount      NUMERIC (12, 2) NOT NULL,

  -- debit and credit are described by positive and negative value, therefore
  -- zero value is not allowed
  CHECK (amount <> 0)
);

So, basically each transaction will be recorded in journal table and it's amount that is affecting account table will be recorded in posting table.

Since the structure of account is a hierarchical tree, the amount of each node must be calculated with all of it's descendant.

This is the best I can come up with:

WITH RECURSIVE
  -- recursively build tree table, assigning path for each row
  tree(id, parent_id, name, path) AS (
    SELECT account.id        AS id,
           account.parent_id AS parent_id,
           account.name      AS name,
           ARRAY[account.id] AS path -- path from root
      FROM account
     WHERE account.parent_id IS NULL

     UNION ALL

    SELECT account.id                     AS id,
           account.parent_id              AS parent_id,
           account.name                   AS name,
           tree.path || ARRAY[account.id] AS path
      FROM account, tree
     WHERE account.parent_id = tree.id
  ),
  -- make a flat table. each account will appear as whenever it's child is
  -- appeared
  flatten AS (
    SELECT unnest(tree.path) AS id,
           tree.id           AS account_id
      FROM tree
     ORDER BY unnest(tree.path), tree.id
  )

SELECT tree.id                               AS id,
       tree.name                             AS name,
       coalesce(abs(sum(posting.amount)), 0) AS amount
  FROM tree
  LEFT OUTER JOIN flatten ON tree.id = flatten.id
  LEFT OUTER JOIN posting ON posting.account_id = flatten.account_id
 GROUP BY tree.id, tree.name
 ORDER BY tree.id

My question is, Can this be improved? Will it perform well?

PS: Some data for testing code

INSERT INTO account VALUES (100, 'ASSET', NULL);
INSERT INTO account VALUES (101, 'Cash', 100);
INSERT INTO account VALUES (102, 'Bank', 100);
INSERT INTO account VALUES (200, 'LIABILITY', NULL);
INSERT INTO account VALUES (201, 'Loan', 200);
INSERT INTO account VALUES (300, 'EQUITY', NULL);
INSERT INTO account VALUES (301, 'Opening Balance', 300);

INSERT INTO journal VALUES (1, 'opening balance', '2018-12-05');
INSERT INTO posting VALUES (1, 101, 1, 1000000.00);
INSERT INTO posting VALUES (2, 301, 1, -1000000.00);

INSERT INTO journal VALUES (3, 'save to bank', '2018-12-05');
INSERT INTO posting VALUES (5, 102, 3, 1250000.00);
INSERT INTO posting VALUES (6, 101, 3, -1250000.00);

INSERT INTO journal VALUES (2, 'loan money', '2018-12-05');
INSERT INTO posting VALUES (3, 101, 2, 500000.00);
INSERT INTO posting VALUES (4, 201, 2, -500000.00);

The output (for running the query above) should be something like this:

 id  |      name       |   amount
-----+-----------------+------------
 100 | ASSET           | 1500000.00
 101 | Cash            |  250000.00
 102 | Bank            | 1250000.00
 200 | LIABILITY       |  500000.00
 201 | Loan            |  500000.00
 300 | EQUITY          | 1000000.00
 301 | Opening Balance | 1000000.00
(7 rows)

If your account table doesn't change often, you could take advantage of Postgres Materialized Views.

CREATE MATERIALIZED VIEW account_tree
AS
WITH RECURSIVE
  -- recursively build tree table, assigning path for each row
  tree
AS (
    SELECT account.id        AS id,
           account.parent_id AS parent_id,
           account.name      AS name,
           ARRAY[account.id] AS path
      FROM account
     WHERE account.parent_id IS NULL

     UNION ALL

    SELECT account.id                     AS id,
           account.parent_id              AS parent_id,
           account.name                   AS name,
           tree.path || ARRAY[account.id] AS path
      FROM account, tree
     WHERE account.parent_id = tree.id
  )
SELECT
    t.id,
    t.account_id,
    CASE WHEN t.parent_id = t.account_id
         THEN a.name ELSE t.name END AS name
FROM
    (SELECT id, parent_id, name, unnest(path) as account_id
     FROM   tree) t
JOIN
    account a
    ON a.id = t.parent_id;

Materialized views allows the use of index:

CREATE INDEX account_tree_ix
  ON account_tree (id, account_id);

This view returns next result:

 id | account_id | name
--: | ---------: | :--------------
101 |        100 | ASSET
101 |        101 | Cash
102 |        100 | ASSET
102 |        102 | Bank
201 |        200 | LIABILITY
201 |        201 | Loan
301 |        300 | EQUITY
301 |        301 | Opening Balance

Now you can use this view to obtain the balance sheet avoiding the recursive CTE:

SELECT    at.account_id,
          at.name,
          coalesce(abs(sum(p.amount)), 0) AS amount
FROM      account_tree at
LEFT JOIN posting p
          ON p.account_id = at.id
GROUP BY  at.account_id,
          at.name
ORDER BY  at.account_id;

account_id | name            |     amount
---------: | :-------------- | ---------:
       100 | ASSET           | 1500000.00
       101 | Cash            |  250000.00
       102 | Bank            | 1250000.00
       200 | LIABILITY       |  500000.00
       201 | Loan            |  500000.00
       300 | EQUITY          | 1000000.00
       301 | Opening Balance | 1000000.00

db<>fiddle here


SQL to merge related documents into 1 query row (MySQL) I'm working with the following data structure:

users

| field| value
| ---  | ---
| id   | 123
| email| example@example.com

fields

| field    |value
| ---      |---
| parentId |123 -> users.id
| name     |Bank
| value    |WBC

Users have a one-to-many relationship to fields, a single user will have multiple fields with different names.

I'm trying to construct a query that would ultimately output something like:

| field | value
|---    |---
| id    |123
| email |example@example.com
| bank  |ANZ

So there's a new column added to the query for each unique name value. I do know all the possible name values up front.

My query is pretty standard so far, it's grouping but obviously grouping discards all but 1 of the joined model. Is there a way around this?

SELECT users.id, fields.name
FROM users
LEFT JOIN fields ON (fields.parent_id= users.id)
GROUP BY users.id
ORDER BY updated_at DESC
LIMIT 100 OFFSET 0

Your question appears to be a case of on the fly fields ( dynamic attributes ), which means that within your application end users can add information about the user on the fly, if this is not the case you need to reconsider schema design.

Here is the query that can be used to achieve the result, here you must know all the fields in advance:

SELECT users.id, users.email, group_concat(IF(fields.name='Bank',fields.value,'')) Bank
FROM users
LEFT JOIN fields ON (fields.parent_id= users.id)
GROUP BY users.id
ORDER BY updated_at DESC
LIMIT 100 OFFSET 0

also consider field updated_at is not mentioned by you in your example. I am assuming it is available only in one table that should be the users.


PostgreSQL - Flatten nested JSONB object array Im my database with Postgres 11, i have this JSON in my rows stored in a JSONB field, and i need to flatten out the list of objects that are over "ports" key, so, instead of having a json output i need to have them in a "flattended table" way like below
port_name, port_description, port_status_int, port_status, port_speed_int, ports_speed
...
"GigabitEthernet0/0", "", 2, "Not Connect", 1000000000, "1G"
"Bluetooth0/4", "", 2, "Not Connect", 1000000000, "1G"
...

Note that the lenght of the "ports" object is not static and may vary.
As far i know, this it my solution...i have some doubts: is this solution ok? it can be a bottleneck? it can be optimized?
SELECT  mytable.measurement_id,
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_name' as "port_name",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_description' as "port_description",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_status_int' as "port_status_int",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_status' as "port_status",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'port_speed_int' as "port_speed_int",
(jsonb_array_elements(mytable.data->'ports')::jsonb)->'ports_speed' as "ports_speed"
from mytable where device_id = 20;

Any toughts?

Set returning functions should be called in the FROM.
SELECT mt.measurement_id,
       p.port ->> 'port_name' as "port_name",
       p.port ->> 'port_description' as "port_description",
       p.port ->> 'port_status_int' as "port_status_int",
       p.port ->> 'port_status' as "port_status",
       p.port ->> 'port_speed_int' as "port_speed_int",
       p.port ->> 'ports_speed' as "ports_speed"
from mytable mt
  cross join jsonb_array_elements(mt.data->'ports') as p(port)
where device_id = 20;

You can simplify this a bit, by using jsonb_to_recordset:
SELECT  mt.measurement_id,
        p.*
from mytable mt
  cross join jsonb_to_recordset(mt.data->'ports') as p(port_name text, port_description text, port_status_int int, port_status text, port_speed_int int, ports_speed text)
where device_id = 20;