Logging only mySQL logins on heavily used system I have an audit requirement where they want all database access (logins) logged.  We have a very large distributed system of hundreds of mySQL servers (shards, slaves, slaves of slaves) that are serving a thousand queries per second.  As a result, I can't just turn on general query logging as recommended in Audit logins on MySQL database, it'll be multiple GB/day per instance and kill our disk IO.

I've looked and see two options to do discretionary logging with filtering - an Oracle plugin and a McAfee plugin.  The Oracle one requires you to be paying them $ for enterprise mySQL, and with hundreds of db servers I'm not really prepared to start doing that today.  The McAfee one is brand new and I'm worried about stability on a large scale system like we have.

Any other good solutions to log only logins to our mySQL databases?  It doesn't have to be integral to mySQL, we'd consider UNIX level port sniffing shenanigans if they'd scale.

I really wish I could take credit for this, but I can't.  I chanced across this while thinking about your question:

http://www.fromdual.com/mysql-logon-and-logoff-trigger-for-auditing

It never occurred to me to call a stored procedure from init_connect... but I (somewhat enthusiastically, I might add) tested this solution before posting it here, and found that it works exactly as described, on off-the-shelf MySQL 5.5.30.

It logs every login of every user who lacks the SUPER privilege.  This is a bit of a limitation, except, of course, for the fact that a SUPER user could manipulate the audit table anyway.

To capture the logouts with similar logic, you have to modify the server source, since init_connect does not have a built-in counterpart that gets executed at the end of the session.


how to generate a sequence of values with differents formats in PostgreSQL I want to find rows missing from a sequence of values in my table with differents formats.
For an example, look at this.
I want to find the missing lines via c_order_id and documentno. the one with '_p' at the end, and the other without '_p', just the numbers.
CREATE TABLE documents (
  "order_id" VARCHAR(22),
  "documentno" VARCHAR(20)
);
INSERT INTO documents
  ("order_id", "documentno")
VALUES
  ('100001120', 'PROFS/2021/02050_P'),
  ('100001125' ,'PROFS/2021/02055_p'),
  ('100001127', 'PROFS/2021/02056'),
  ('100001135' ,'PROFS/2021/02060'),
  ('100001139' ,'AGB/2021/02040'),
  ('100001172' ,'AGB/2021/02047')

Query used:
WITH cte as (
SELECT LEFT("documentno",12) lpart , MIN(RIGHT("documentno",5)) minpart, MaX(RIGHT("documentno",5)) maxpart  FROM documents
groUP BY LEFT("documentno",12))
sELECT lpart || generate_series(minpart::INTEGER, maxpart::INTEGER)  as missing_documentno FROM cte
except
select documentno from documents
ORDER bY missing_documentno;

This query generates
ERROR:  invalid input syntax for type integer: "055_p"

You can use regexp_replace() to cleanup the numbers so that you can cast it to an integer value.
with cte as (
  select left(documentno,12) lpart,
         regexp_replace(split_part(documentno, '/', 3), '[^0-9]', '', 'g')::int as num
  from documents
), minmax as (
  select lpart, min(num) minpart, max(num) maxpart
  from cte
  group by lpart
)
select m.lpart, t.missing_document_no
from minmax m
  cross join generate_series(m.minpart, m.maxpart) as t(missing_document_no)
order by t.missing_document_no

I have split up the "cleaning" of the data and the group into two CTEs to make the steps a bit more readable. Given your sample data, this returns:
lpart        | missing_document_no
-------------+--------------------
PROFS/2021/0 |                2050
PROFS/2021/0 |                2051
PROFS/2021/0 |                2052
PROFS/2021/0 |                2053
PROFS/2021/0 |                2054
PROFS/2021/0 |                2055
PROFS/2021/0 |                2056
PROFS/2021/0 |                2057
PROFS/2021/0 |                2058
PROFS/2021/0 |                2059
PROFS/2021/0 |                2060

To find the missing values, you can extend the final select to:
with (
...
), minmax as (
...
)
select lpart||t.doc_no as missing_doc_no
from minmax m
  cross join generate_series(minpart, maxpart) as t(doc_no)
where not exists (select *
                  from documents d
                  where regexp_replace(split_part(d.documentno, '/', 3), '[^0-9]', '', 'g')::int = t.doc_no)


AlwaysOn Listeners and Multiple Groups I've just setup multiple availability groups within the same group of servers, simply for separation more than anything else, the databases in the new one are for Skype for Business so it comes with a handful of databases.

Anyway, it all works fine, I set everything up, I set up a new listener for it and then something happened I didn't expect. Using both the old and new listeners gives me all the databases on that instance, from both groups. I wasn't expecting this, I thought in my head (and I will hold my hand up and admit I'd totally made this up myself) that by assigning a listener to each of the groups, I would only see the databases in that group when using that listener.

Anyway, my question is... what would be the usual best practice around this? If each listener will give you all the databases anyway, should I create one for each group? or just one for the first group? or have I fundamentally missed something and there should be a way of me setting up each listener to only see the databases in the group associated with it?

I'm happy to be getting the "There's no point creating multiple blah blahs for doing that" messages if that's the case, no worries, but please only if you actually answer my main question.

TLDR version: the availability group listener points to the instance where the database sits, not the database itself.

Right now that that's out the way.
If I'm reading this right you have multiple databases set up with Always On High Availability(AOHA) across two (or more)instances

There are a few settings to consider here. Firstly the unchangeable, you never connect to a database directly, you connect to an instance and then to the database within that instance, if you want to be restricted to only access the one database you need to do that with user/group level permissions and have different accounts to access each database separately.

The setting you have control over is what happens with the non primary instance, there are a few options:
Connections in Primary Role (Allow all, Allow read/write)
Readable Secondary (Yes, Read Intent Only, No)

If you turn readable secondary to No, then you'll only ever be able to use the database if it is the primary node (note this will save on licensing as a server with all of the databases in this mode does not require to be licensed(few conditions))
If this is set to 'yes' or 'read intent only' then users connected to that instance will be able to see and read data from that database (obviously read intent only will only allow read intent connections).
The connections in primary role is used to re-direct read intent connections to the secondary node without having to know about the read-replica

On the note of separate listeners that depends on how you are using the databases, if they're completely separate and can exist within different instances (or their operations can be done with linked server connections) then being able to move the primary node to separate servers may allow you to unburden a server under heavy load. If the databases are dependant on being active on the same box and instance then there isn't much point in having multiple listeners as they will all always point to the same place which is ultimately the instance itself, it would be better to use different users (if possible) to lock off different databases (unless you wanted to do some really really complicated stuff with login triggers granting / removing permissions on the fly any time someone connects (which I would heavily not recommend and don't know if its actually possible)

Hope that helps


MySQL Server 5.6 won't start on Windows I'm having an issue getting MySQL Server 5.6 (community edition) running. It was working just fine until I restarted my server. Now it won't start at all.

After doing a bit of research, I discovered there is a log file that it writes to, which has helped a little, but I'm not entirely sure how to fix the issue it's reporting.

After doing more research I discovered trying to start mysqld manually using the options "--standalone" and "--debug" might help, but they don't seem to make a difference, and I've looked everywhere for the mysterious debug log, but was unable to find it. I checked in the C drive root, and in MySQL server folders, but nothing. Here is the most recent .err file report:

  2014-09-18 18:47:29 2240 [Note] Plugin 'FEDERATED' is disabled.

  2014-09-18 18:47:29 2240 [Note] InnoDB: Using atomics to ref count buffer pool pages

  2014-09-18 18:47:29 2240 [Note] InnoDB: The InnoDB memory heap is disabled

  2014-09-18 18:47:29 2240 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions

  2014-09-18 18:47:29 2240 [Note] InnoDB: Memory barrier is not used

  2014-09-18 18:47:29 2240 [Note] InnoDB: Compressed tables use zlib 1.2.3

  2014-09-18 18:47:29 2240 [Note] InnoDB: Not using CPU crc32 instructions

  2014-09-18 18:47:29 2240 [Note] InnoDB: Initializing buffer pool, size = 128.0M

  2014-09-18 18:47:29 2240 [Note] InnoDB: Completed initialization of buffer pool

  2014-09-18 18:47:33 2240 [Note] InnoDB: Highest supported file format is Barracuda.

  2014-09-18 18:47:33 2240 [Note] InnoDB: The log sequence numbers 1625977 and 1625977 in ibdata
  files do not match the log sequence number 28347554 in the ib_logfiles!

  2014-09-18 18:47:33 2240 [Note] InnoDB: Database was not shutdown normally!

  2014-09-18 18:47:33 2240 [Note] InnoDB: Starting crash recovery.

  2014-09-18 18:47:33 2240 [Note] InnoDB: Reading tablespace information from the .ibd files...

  2014-09-18 18:47:34 2240 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous
  tablespace osticket/ost_ticket__cdata uses space ID: 54 at filepath: .\osticket\ost_ticket__cdata.ibd. Cannot open tablespace osticket_2/ost_api_key which uses space ID: 54 at filepath: .\osticket_2\ost_api_key.ibd

  InnoDB: Error: could not open single-table tablespace file .\osticket_2\ost_api_key.ibd

  InnoDB: We do not continue the crash recovery, because the table may become

  InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.

  InnoDB: To fix the problem and start mysqld:

  InnoDB: 1) If there is a permission problem in the file and mysqld cannot

  InnoDB: open the file, you should modify the permissions.

  InnoDB: 2) If the table is not needed, or you can restore it from a backup,

  InnoDB: then you can remove the .ibd file, and InnoDB will do a normal

  InnoDB: crash recovery and ignore that table.

  InnoDB: 3) If the file system or the disk is broken, and you cannot remove

  InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf

  InnoDB: and force InnoDB to continue crash recovery here.

Any clue as to what might fix this problem? I'd prefer a solution that doesn't involve losing my MySQL database files/entries, as I've already had to redo over half of my SQL-connected web services, and would prefer not to have to do it again.

[closed] Reinstalled MySQL, removed old DB's. starting over. [closed]


For SQL Server, What is the proper way to update a table? I don't have a mentor to ask so I am seeking direction from the DBA community. I am in charge of a SQL SERVER 2008 R2 and SQL Azure databases that are the resources for a website and an ERP system for a small company. I've already made the mistake of updating without having a WHERE statement (luckily it only affected a table w/ 30 rows). But with some table containing 20K+ rows, what is the proper way to update tables in SQL Server? Do I create a test DB copy and perform the update on that first (but this doesn't have the website or ERP connected to it, so I can see the
results in regards to those), or is the TRANSACTION function the direction I should study and learn wit it's ROLLBACK feature that seems might be the right way, Or maybe there is some other process all SQL SERVER DBA follow. Any and all advice will be appreciated.

Study the following:

BEGIN TRANSACTION, ROLLBACK TRANSACTION, COMMIT TRANSACTION

Also read Paul Randall's A SQL Server DBA myth a day: (26/30) nested transactions are real
Also read XACT_ABORT

@@ROWCOUNT
IF ...ELSE
GOTO
@@TRANCOUNT
More advanced topics, optional until they're not:

SET TRANSACTION ISOLATION LEVEL
Gail Shaw's intro to deadlocks

Then re-read this general technique:

At the start of the code, GOTO :TheEnd

partial protection against a few "only run part of the code, not all the code" mistakes

At the end of the code, GOTO :TheBeginning

partial protection against a few "only run part of the code, not all the code" mistakes

SET XACT_ABORT ON and start a transaction
Code a single DML statement with appropriate WHERE and JOIN clauses
Code a check how many rows it affected; if it's not exactly as many rows as you expected, roll back the transaction.

repeat the above coding as many times as you have statements

default to rolling back the transaction.

code the COMMIT only after you're sure it's all correct.
run the COMMIT only after getting a cup of coffee, in case you have a moment of fridge horror

Check to verify transaction count is 0


Deny a Sql Server user from a specific hostname Is it possible to block a sql server user not to execute DDL and DML operations from a specific hostname (client)?

I have a CRM web application. That app connects to my database with a sql server auth user. There are several different developers working on that CRM web app. They know the user name and password from the web config file they develop. Some of them can connect to my database with that user and execute some DDL and DML commands and I want to block them by their client hostname. If a developer connects to db from his/her machine with the app user credential, I want to block them.

And, there several different developers which developes that CRM web
  app. They know the user name and password from the web config file
  they develope. Some of them can connect to my database with that user
  and execute some DDL and DML commands. And i want to block them by
  their client hostname. If a developer connects to db from his/her
  machine with the user credential, i want to block them.

I don't think you should try to fix it that way.

Both pc names and IPs can be changed at will, even static IPs could be changed if wanted/needed. Which means you'd have to maintain an up-to-date list all the time.
As a dev, if you really wanted to do something, remote to another VM, execute there.
I mean, there's probably plenty of security loopholes they can find if they really wanted to do something.

Finding a solution to do what you want, would probably only make it a hassle for them, but not fool proof.

I think you'd be better off trying to find a security solution for this. If one of the devs should not be able to do DML statements, don't give them a login that has access to it. Does the user in the config really need all the permissions it has now? Perhaps you can give less permissions to that user, and give the people who need more permissions an extra role, on their own login.


Cannot reclaim Index Unused Memory in In-Memory OLTP Steps to reproduce the problem
Create a database with memory-optimized filegroup and container
Create schema only in-memory table with nonclustered pk
Simulate insert and delete activity.
My result is that I have high index unused memory that won't go down.
 USE master
 go
 DROP DATABASE IF EXISTS MemoryOptimizedTest
 CREATE DATABASE MemoryOptimizedTest
 GO
 USE MemoryOptimizedTest
 GO
 ALTER DATABASE MemoryOptimizedTest
 ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
 GO

 ALTER DATABASE MemoryOptimizedTest ADD FILE (name='imoltp_mod1', filename='c:\imoltp_mod1') TO FILEGROUP imoltp_mod
 GO

 DROP TABLE IF EXISTS dbo.MyCache
 CREATE TABLE dbo.MyCache
 (
    PK int NOT NULL,
    SecondInt int NOT NULL,
    ThirdInt int NOT NULL,
     CONSTRAINT PK_MyCache PRIMARY KEY NONCLUSTERED (PK)
 ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

 go

/* Generate activity and monitor table size */
USE MemoryOptimizedTest
go

SELECT
    object_id,
    OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) AS Table_Name,
    memory_allocated_for_table_kb,
    memory_used_by_table_kb,
    memory_allocated_for_indexes_kb,
    memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_ID = OBJECT_ID('dbo.MyCache')

;WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
, tally AS (SELECT TOP (10000) n FROM Nums ORDER BY n)
INSERT INTO dbo.MyCache (PK, SecondInt, ThirdInt)
SELECT
    n
    , n+1
    , n+2
FROM tally

WAITFOR DELAY '00:00:02'
DELETE FROM dbo.MyCache

GO 50

When I run it on my localmachine Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Developer Edition
with 16 GB Max Memory and 1.5 GB Available memory, the memory_allocated_for_indexes_kb fluctuates normally.
When I run it on our DEV environment Microsoft SQL Server 2019 (RTM-CU7) (KB4570012) - 15.0.4063.15 (X64) Enterprise edition
2 TB Max Memory, 220 GB Available memory
The memory_allocated_for_indexes_kb only grows. I've simulated activity for a table for few hours and have index used memory = 0.24 MB, Index Unused Memory = 385 MB and it won't go down.
The garbage collector ran according to PerfMon Sweep expired rows removed/sec in XTP Garbage collection.
I read somewhere that the garbage collector doesn't free up space until it faces memory pressure but it seems weird that it would hold so much unused memory.
EDIT: I used Resource pool for the database holding in-memory tables. One percent is the lowest I could go. I filled the memory with an other table taking 99% of the resource pool and the memory_allocated_for_indexes still won't go down. Whether there is a transactional activity or not. No active trasnactions are blocking GC and I've waited for more than 30 minutes.
I can't accept any of the current answers as my problem is still not answered.
Note that the table rows are cleaned up, but not for the index.

I read somewhere that the garbage collector doesn't free up space until it faces memory pressure but it seems weird that it would hold so much unused memory.

Is it weird, though? Why do garbage collection now when it can be put off until later :)
From the documentation, a confirmation of your thought (emphasis mine):

After a user transaction commits, it identifies all queued items associated with the scheduler it ran on and then releases the memory. If the garbage collection queue on the scheduler is empty, it searches for any non-empty queue in the current NUMA node. If there is low transactional activity and there is memory pressure, the main garbage-collection thread can access garbage collect rows from any queue. If there is no transactional activity after (for example) deleting a large number of rows and there is no memory pressure, the deleted rows will not be garbage collected until the transactional activity resumes or there is memory pressure.

Your test workload seems quite small and I wouldn't be surprised if it isn't enough to trigger any collection, especially with how robust that system's memory resources are.
With 2TB of memory, I wouldn't sweat < 500MB of memory usage for this purpose, nor would I worry about this until it became an actual problem.


Why does Type Resolution fail when using SELECT DISTINCT instead of SELECT on PostgreSQL? Here is a minimal reproduction:
SELECT 1
UNION ALL
SELECT DISTINCT NULL

Fails with:
UNION types numeric and text cannot be matched

According to the PostgreSQL documentation on Type Resolution for UNION, CASE, and Related Constructs and the following chapter on SELECT Output Columns, the type of null should be derived from the preceding select.
In fact it works, if I remove the DISTINCT:
SELECT 1
UNION ALL
SELECT NULL

Why does the DISTINCT modifier interfere with type resolution?

Without the DISTINCT, the type of the second part of the UNION ALL is unknown:
SELECT pg_typeof(NULL);

 pg_typeof
-----------
 unknown
(1 row)

This gets resolved to integer.
If there is a DISTINCT, PostgreSQL has to resolve the type of NULL earlier on: to eliminate duplicates, it has to know which equality operator to use. Lacking other information, it chooses text as the preferred type of the string category.
Then you get the error because integer and text cannot be reconciled.
The documentation describes the type choice for the equality operator as follows:

If any input arguments are unknown, check the type categories accepted at those argument positions by the remaining candidates. At each position, select the string category if any candidate accepts that category. (This bias towards string is appropriate since an unknown-type literal looks like a string.)


Indexing binary data in PostgreSQL? I am converting an Oracle 11G DB to PostgreSQL v11 DB.

I am super new to the whole database thing and I have encountered a query using index type of ctxsys.context to create an index.

I have no idea what ctxsys index type is doing as I tried reading oracle documents related to it but got confused.

I wish to know what exactly this indextype does and

Is there any equivalent solution in PostgreSQL ?

Below is the query for all of your references,

CREATE INDEX IDX1 ON TBL1(BLOB Column) indextype is ctxsys.context;
CREATE INDEX IDX2 ON TBL2(BLOB Column) indextype is ctxsys.context;

I am told you can't index bytea columns (which is the equivalent to blob) in Postgres. Can I move only the lob column to a user defined tablespace ?

Oracle text search filter is capable of automatically parsing non-plain text documents, such as MS Word or PDF, while Postgres' text search can only handle plain text documents. You'll have to convert whatever is stored in your BLOB column to plain text before loading those data into a Postgres TEXT column, then build the Postgres text search index on that.


MySQL replication stopped for over 10 days, will replication work correctly when expire_logs_days=10? I'm foggy on exactly how replication works.  Here's my scenario.

Today (April 6th) I realized that our replication slave had an error caused by a failed "CREATE USER" command on March 20.  Since then, no replication has happened.

This leaves a six day or so gap where expire_logs_days would have expired logs on the master, and the slave was not replicating.

I was able to skip the error and get replication going again. It all caught up according to SHOW SLAVE STATUS\G.

How can I check that things are replicated correctly, beyond "spot checks?"  For example, the number of posts on master/slave are identical, that's encouraging.

But when I run, for example, this query on each database, I do not get the same answers:

select count(*) from table where datestamp >= '2016-03-24 00:00:00' AND datestamp <= '2016-03-24 23:59:59';

Should I be concerned that data is missing/incorrect?  Is there a reason why the timestamps would be off between databases?

I'd prefer not to have to re-set up the replication, but am prepared to do so.

Thank you.

The reason you were able to issue a single skip slave statement and it caught up, is because the SQL thread stopped, but the IO thread continues.

As long as you see:

Slave_IO_Running: Yes

when you run SHOW SLAVE STATUS, then the slave is able to continually pull the binary logs from the master and store them into the relay logs.

Regardless of expire_logs_days setting on the master and if the binlogs disappear, as long as they have been pulled and stored into the relay logs on the slave, they can be replayed.

One caveat being that on very busy servers, replaying over 10 days worth of relay logs would be much longer than doing a fresh restore.

Regarding the differences in your counts, you could be having other issues unrelated to this replication event. It is tough to know precisely without access to binlogs, schema and my.cnf.

Some rough debugging steps:

Do count of days prior to the replication event differ at all? Do other days after differ?
Use a tool to get differences in slave and master. I recommend pt-table-checksum, but be aware of the Limitation of servers running in RBR/MIXED binlog_format:

  pt-table-checksum requires statement-based replication, and it sets binlog_format=STATEMENT on the master, but due to a MySQL limitation replicas do not honor this change. Therefore, checksums will not replicate past any replicas using row-based replication that are masters for further replicas.

Debug the root cause of any differences found by the checksum process. This could be many reasons. A couple that come to mind is using statement-based replication in some situations, or different timezone settings on master and slave.
To resolve immediate differences, you can use pt-table-sync or restore a fresh copy of the master.


What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE? What's best performance wise, two separate UPDATEs:

UPDATE table1
    SET enabled = 0
    WHERE code NOT IN (
        SELECT id
        FROM table2
    );

UPDATE table1
    SET enabled = 1
    WHERE code IN (
        SELECT id
        FROM table2
    );

Or a single UPDATE with a CASE?

UPDATE table1
    SET enabled = (
        CASE
            WHEN code IN (SELECT id FROM table2) THEN 1
            ELSE 0
        END
    );

(and let's consider there are no NULL values in the relevant columns).

What would perform best, supposed code and id columns have are indexed?

I can't really tell because I don't have significant tables, and the only difference EXPLAIN gives is that the former version uses the WHERE (but I analyzed only one UPDATE) - I'd say that a plus, but I'm unsure if that's enough.

I'm working with MySQL.

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL

UPDATE table1 A
LEFT JOIN table2 B
ON A.code = B.id
SET A.enabled = 1 - ISNULL(B.id);

Why should this work ?

If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2
If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) because it is not in table2

@ypercube suggested

UPDATE table1 A
LEFT JOIN table2 B
ON A.code = B.id
SET A.enabled = (B.id IS NOT NULL);

GIVE IT A TRY !!!


Why is NOLOCK implemented like that? Recently I have searched for NOLOCK option in SQL Server. What I've discovered is that when a transaction is active on a table, SQL Server does not allow even reading from a specific table until the transaction is either committed or rolled back (as far as I understood from When should you use “with (nolock)”).

At the same time, other RDBMSes, such as PostgreSQL, allow you to read values from a row that has an active writing transaction on it. They would just give you the values that were there prior to the writing. Particularly that seems to be the behaviour with PostgreSQL MVCC (as I have learned from the Introduction to Chapter 13. Concurrency Control).

And I have this burning question. Why is there a possibility for deadlocks in SQL Server while in other RDBMSes you just get the old value before the new one is written?

NOTE: I am asking because I may have understood incorrectly.

SQL Server by default operates under Read Committed isolation.

If you want SQL Server to behave more like other implementations, what you're looking for is either Read Committed Snapshot Isolation, or Snapshot Isolation.

You can read more about them here.

Full disclosure, I am a Brent Ozar Unlimited employee

Using NOLOCK or Read Uncommitted is generally frowned upon for serious production use, as it can allow dirty reads and other unintended artifacts while modification queries are in progress.


UPDATE vs INSERT INTO... ON DUPLICATE KEY UPDATE Alright so I have this table in which until now I was using the following queries to UPDATE else do an INSERT:

$db->query("UPDATE ulogs SET invalid = invalid + 1 WHERE uid = 666 AND date = '2018-04-18'");
if ($db->affectedRows() < 1) {
    $db->query("INSERT INTO ulogs (uid,date,invalid,unique,nonu,ea,ref,bo) VALUES (666,'2018-04-18',1,0,0,0,0,0)";
}

I had a regular combined index (non-unique) on uid&date columns. The update query was using the INDEX. All is fine but I was wondering if switching to ON DUPLICATE KEY UPDATE would be better, especially as the two columns' INDEX can be switched to UNIQUE. So I went ahead and modified the INDEX to an UNIQUE one, then rewrote the query as follows:

$db->query("INSERT INTO ulogs (uid,date,invalid,unique,nonu,ea,ref,bo) VALUES (666,'2018-04-18',1,0,0,0,0,0) ON DUPLICATE KEY UPDATE invalid = invalid + 1";

However, mysql explain is showing that the INDEX is not used in this case. Also for some reason it shows the query as an INSERT operation, even though an UPDATE is performed:

Also, by running the query in phpmyadmin, for some reason it shows that 2 rows have been INSERTed, while in fact only 1 row was UPDATEd with the data:

Bottom line is, which operation would be the fastest in terms of code optimization?
Thank you.

The fastest operation will be the one that has the most chances of succeeding immediately.  You should find out what happens more often: inserting new rows or updating old ones.   Code accordingly.


Syntax Error On A Procedure ORA-01422 As a beginner, I am not able to see any error logically. All I am doing here is saving data into a variable and comparing that in if statements. However, at some point it returns more than requested rows and causes error.

Thanks in advance for your help.

CREATE OR REPLACE PROCEDURE Late_Payment_Days(Inv_ID Varchar2)
AS
    Payment_Exist Number;
    Late_Payment Number;
BEGIN
    DBMS_OUTPUT.Enable();
    SELECT Payment_Total
    INTO Payment_Exist
    FROM Invoices Where Invoice_ID = Inv_ID;

    SELECT
        ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID)
          - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
        )
    INTO Late_Payment
    FROM Invoices;

    IF Payment_Exist=0 THEN
        DBMS_OUTPUT.PUT_LINE('No Payment!');

        IF Late_Payment > 0 THEN
            DBMS_OUTPUT.PUT_LINE(Late_Payment || 'Overdue Days');
        ELSE
            DBMS_OUTPUT.PUT_LINE('Zero overdue days');
        END IF;
    END IF;
End;
/

Show Error;

EXEC Late_Payment_Days(5);

I think that this part is responsible for the error;

SELECT
    ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID)
      - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
    )
INTO Late_Payment
FROM Invoices;

If there is more than 1 row in Invoices, the result of the query will be many rows (with the same value) and we can only insert one value into the variable Late_Payment.

You can change the FROM Invoices to FROM dual (a special Oracle table with a single row):

SELECT
    ( (SELECT PAYMENT_DATE FROM INVOICES WHERE INVOICE_ID = Inv_ID)
      - (SELECT INVOICE_DUE_DATE FROM INVOICES WHERE INVOICE_ID= Inv_ID)
    )
INTO Late_Payment
FROM dual ;

or - since both subqueries use the same FROM and WHERE - simplify to:

SELECT PAYMENT_DATE - INVOICE_DUE_DATE
INTO Late_Payment
FROM INVOICES
WHERE INVOICE_ID = Inv_ID ;


How to backup a secondary log shipped standby mode database We have a secondary transaction log shipped database configured for standby mode. Its primary purpose is for data recovery and for select only queries for reports etc.

As the database is very big we were hoping to be able to do a READ_WRITE file backup (the larger tables like audit trail history etc. are on a secondary file group) for us by developers who need a copy of live when investigating recent issues.

After trial and error and researching so far the only options I can see are:

Do a restore with recovery and take a backup - but this breaks log shipping and we have to set it up again
Take the secondary DB offline and physically take a copy the actual underlying databases data and log files - but then I still have to run a truncate table on the data we don't want/need and shrink to get it down to a manageable size

Has anyone had any luck with other options, 3rd party tools etc?

You could restore the same backup that was used to create the log shipped database on another server, and then apply all the transaction log backups.  There is no limit to the number of log-shipped databases you can have.  I don't know if Management Studio supports this option, but it is fairly straightforward process to implement manually (T-SQL, powershell, etc.) as you are simply copying files and restoring a database.  The database will need to stay read-only mode if you plan on restoring any more transaction logs.


Splitting a table based on enum value I am re-designing our company database, and am wondering whether or not to split this table up into two (or even three) different tables based on the type field. Prospects can become clients, and in rare cases, clients can revert to being prospects again (usually correction of a user error). Currently there are no suppliers who are clients, but theoretically there's nothing to stop that from happening in the future. As you can see, several fields are only needed in one case or another.

CREATE TABLE `companies` (
  /* book-keeping fields every data table should have */
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` TIMESTAMP NULL DEFAULT NULL, /* would love to specify DEFAULT CURRENT_TIMESTAMP here but MySQL won't let me */
  `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  /* fields for all company types */
  `type` ENUM('prospect','client','supplier') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'prospect',
  `name` VARCHAR(25) DEFAULT NULL,
  `abbr_name` VARCHAR(8) DEFAULT NULL,
  `formal_name` VARCHAR(255) DEFAULT NULL,
  `web_site` VARCHAR(255) DEFAULT NULL,
  `email` VARCHAR(320) DEFAULT NULL,
  `logo` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `key` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
  `bust` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
  `head_office` INT UNSIGNED DEFAULT NULL,
  /* only used for clients */
  `accounting_code` VARCHAR(5) CHARACTER SET ascii DEFAULT NULL,
  /* used for prospects, and clients obtained by marketing */
  `marketing_user` INT UNSIGNED DEFAULT NULL,
  `marketing_datasource` CHAR(2) CHARACTER SET ascii DEFAULT NULL,
  /* only used for prospects */
  `marketing_dead` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
  /* only used for suppliers */
  `supplies` SET('materials','plant') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `account_holding_branch` INT UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`head_office`) REFERENCES sites (`id`),
  FOREIGN KEY (`marketing_user`) REFERENCES users (`id`),
  FOREIGN KEY (`account_holding_branch`) REFERENCES sites (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What do people recommend, and importantly, why?

A similar question, Splitting a table based on business logic provides one curt answer with two relevant reasons to help decide whether splitting is worth it:

Partition hot and cold data for cache efficiency
Many columns almost always null

As to data 'temperature', key clients and key suppliers are hotter than other clients or suppliers, and hotter than prospects. Any company which has gone bust or for which the marketing trail has died are pretty frigid.

As to NULL, it varies, but prospects almost always do not have a logo uploaded, and suppliers presently do not have data in any of the four accounting_code and marketing_* fields, though as I say there's no technical reason why a parts supplier could not also become a client of our business.

If you never or rarely need to access more than one type at a time, then consider splitting.  However, personally I wouldn't do so.  I've been involved in a number of projects where this has been done, and result in a lot of additional complexity with little benefit.

The companies table appears to be a good choice for the common data.  Migrating data when a company changes type will increase complexity.  To enable this to work well you will need a table to generate ids from, and rules to ensure the same id doesn't appear in more than one sub-table.

I would consider splitting type specific columns into separate tables.  This will require different queries by type.  Again this may make the application more complex.  As there are only a few smallish columns, keeping them in the main table likely has relatively little overhead and simplify the overall design.  If you have many or larger fields which are type specific and only of use when the type is known, then consider splitting the sub-type data into another table.  It can share the same key as the relationship will be an optional identity relationship.

Another case where I would consider splitting a record is where there is a mix of tombstone data and highly transactional data.  I've seen it work well with parts, and inventory data.

Having worked in a company where suppliers were also clients, revisiting how suppliers and clients are differentiated might be a worthwhile exercise.  I would consider splitting suppliers into a separate table based on the following criteria.

Systems dealing with suppliers are separate from those dealing with customers.
There is little prospect of reusing access routines between the systems.
Volumetric data indicates that a least one system could get substantially better performance from splitting the tables.
Security/access requirements for the two type of companies are significantly different.

Reviewing what happens to prospect data when a prospect becomes a client may be a worthwhile exercise.  If it is retained, it may have value in determining how clients are being generated.  It will also make converting clients back to prospects after keying errors simpler.

Partitioning a table like a customer table is likely to be of little use.  Hot clients will likely be cached at the database or o/s block buffer level.  If you have the ID it is pretty well a direct look-up.

Transactional data is more suitable for partitioning.  Order details can age out in hours depending on order processing times.   Customer data will likely take years to age out.   I don't see any data in the table which could be used to effectively partition the data base on 'hotness'.   I have generated an archiving system for customers and their data.  Determining the customers which could be archived was not trivial.


Get row with the biggest value from each group I have this query:

SELECT  VIAGEM.IDVOOREGULAR COD_VOO_REGULAR,
        VIAGEM.DATAPARTIDA DATA,
        VOO.ORIGEM,
        VOO.DESTINO,
        (((EXTRACT (DAY FROM (DATACHEGADA-DATAPARTIDA))*24*60*60 +
           EXTRACT (HOUR FROM (DATACHEGADA-DATAPARTIDA))*60*60 +
           EXTRACT (MINUTE FROM (DATACHEGADA-DATAPARTIDA))*60)/60)-VOO.DURACAO) ATRASO
 FROM   VIAGEM,
        VOO_REGULAR,
        VOO
WHERE   VIAGEM.IDVOOREGULAR=VOO_REGULAR.ID AND
        VOO_REGULAR.IDVOO=VOO.ID;

and this result:

And I want to get just the biggest ATRASO from each group of COD_VOO_REGULAR.

How can I do it?

I think something like that should work

SELECT b.* FROM
(
    SELECT a.*,
     ROW_NUMBER() OVER (PARTITION BY a.COD_VOO_REGULAR ORDER BY a.ATRASO DESC ) AS rn
    FROM
    (
      SELECT  VIAGEM.IDVOOREGULAR COD_VOO_REGULAR,
            VIAGEM.DATAPARTIDA DATA,
            VOO.ORIGEM,
            VOO.DESTINO,
            (((EXTRACT (DAY FROM (DATACHEGADA-DATAPARTIDA))*24*60*60 +
               EXTRACT (HOUR FROM (DATACHEGADA-DATAPARTIDA))*60*60 +
               EXTRACT (MINUTE FROM (DATACHEGADA-DATAPARTIDA))*60)/60)-VOO.DURACAO) ATRASO
     FROM   VIAGEM,
            VOO_REGULAR,
            VOO
    WHERE   VIAGEM.IDVOOREGULAR=VOO_REGULAR.ID AND
            VOO_REGULAR.IDVOO=VOO.ID
    )a
) b
WHERE rn = 1 ;


Creating and designing Air Trafic Control System I'm having a class about Database management system, and the prof. gave us a project regarding Air traffic controller system, exact question is "Design and create a system which can manage informations of Air Traffic Control System, summarize entities and how system works?"

im not asking you to do this for me, but im desperate, because i dont know how air traffic control system work and what my database should include.

i have limited time, Thanks in advance.

That is a tough one, if only there was a way to find things like that online.

http://tinyurl.com/q7zxplh

Make a list of the high level objects that need, such as planes, airports, routes, people, roles, etc. Then figure out how they are related, how you identify each object and what attributes they would have.


Is there a way to group the output of a query based on two rows I'm not sure my question is asking the right question, so i will be as specific as possible in here.

I have the following data which i have changed the values of.

Below is the code I am using to pull this data through.

/*this is used for the open order book*/
SELECT co.[slsman]               AS Salesman,
       co.[cust_num]             AS "Customer Number",
       ca.NAME                   AS "Customer name",
       co.[co_num]               AS "Customer Order",
       ci.item,
       ca.curr_code,
       ci.price * ci.qty_ordered AS value
FROM   [co] co
       LEFT JOIN custaddr ca
              ON co.cust_num = ca.cust_num
                 AND co.cust_seq = ca.cust_seq
       LEFT JOIN coitem ci
              ON co.co_num = ci.co_num
WHERE  co.co_num LIKE 's%'
       AND co.stat = 'o'
       AND co.cust_seq = '1'
       AND item NOT LIKE '%carriage%'
       AND ci.stat NOT LIKE 'f'
       AND ci.stat NOT LIKE 'c'
ORDER  BY co.slsman,
          co.cust_num

What I am trying to do is merge all rows where the Customer order and the item part number are the same, and sum all the costs of these rows. I have tried adding a group by before the order by, but i understand after some reading that this does not work how i thought it did.

GROUP BY  co.co_num,
          ci.item

which gives the following error

  Msg 8120, Level 16, State 1, Line 3 Column 'co.slsman' is invalid in the select list because it is not contained in

either an aggregate function or the GROUP BY clause.

Below is what i want to be outputting. As you can see, i don't want to merge any cell text values, just the value one where customer order and item are the same.

SELECT MAX(co.[slsman])               AS Salesman,
       MAX(co.[cust_num])             AS "Customer Number",
       MAX(ca.NAME)                   AS "Customer name",
       co.[co_num]                    AS "Customer Order",
       ci.item                        AS item,
       MAX(ca.curr_code)              AS curr_code,
       SUM(ci.price * ci.qty_ordered) AS sum_value
FROM   [co] co
       LEFT JOIN custaddr ca
              ON co.cust_num = ca.cust_num
                 AND co.cust_seq = ca.cust_seq
       LEFT JOIN coitem ci
              ON co.co_num = ci.co_num
WHERE  co.co_num LIKE 's%'
       AND co.stat = 'o'
       AND co.cust_seq = '1'
       AND item NOT LIKE '%carriage%'
       AND ci.stat NOT LIKE 'f'
       AND ci.stat NOT LIKE 'c'
GROUP BY  co.co_num,
          ci.item
ORDER  BY Salesman,
          "Customer Number"


Why is the index not used? When I run the following querys in MySQL:

SELECT count(*) FROM Inventory AS i WHERE i.lastModified > 1389920146804

or

SELECT i._id FROM Inventory AS i WHERE i.lastModified > 1389920146804;

The EXPLANE command will show that the index on lastModified is used.

But when I run

SELECT * FROM Inventory AS i WHERE i.lastModified > 1389920146804;

or any other query with more then two rows we got a full table scan.

Why? How can I avoid a full table scan when selecting more then one row?

Your index probably only contains the lastModified column, as well as your _id value as a way of finding the other column values for each row.

So if your predicate (the WHERE clause) isn't filtering your index data down to few enough rows, the effort in looking up the other details over and over is too much and it will decide that it's easier to just scan the whole table.

If your index included all the columns you were interested in, you would see an ordinary seek.


Sql restrict insert records for the same client based on 2 columns I have a simple table

CREATE TABLE Records
(
    Id INT,
    Client INT,
    IsActive BIT,
    HasSession BIT
)

I tried using OR statement but it only considered the 2nd argument.

CREATE NONCLUSTERED INDEX F
    ON ClientsDB.Records (Client)
    WHERE IsActive = 1;

CREATE NONCLUSTERED INDEX F
    ON ClientsDB.Records (Client)
    WHERE HasSession = 1;

I tried doing some research and seems you cannot use OR statement on these

As per trigger suggestion referred, one of my requirements is to avoid them at all so i cannot use anything related to triggers.

It appears to me that this was asked with a similar context in a StackOverflow Question.

Using that as a foundation I think a Create Trigger will give you what you are looking for.

CREATE TRIGGER dbo.UniqueClient ON dbo.Records
AFTER INSERT
AS
IF EXISTS
(
    SELECT *
    FROM dbo.Records r
        INNER JOIN inserted AS i
            ON r.Client = i.Client
    WHERE r.IsActive = 1
    OR r.HasSession = 1
)
BEGIN
RAISERROR ('This Client Already Exists with an Active account or Session', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;

The Key to this process is if the incoming row (represented in the inserted table) matches an existing client and that existing client has IsActive = 1 OR HasSession = 1, SQL will roll back the transaction and stop the insert.  An error message is sent back to the application prior to the rollback.

Does this give you what you are looking for?


If all variables are a subset of the candidate key, is the database design 5NF? I have a table called LogMessages, which has the following columns:

Level

A numeric value which represents Trace, Debug, Info, Warning, Error or Fatal

Time

A UTC time

Message

Foreign key to a Messages table

Source

Foreign key to a Sources table

User

Foreign key to a Users table

From what I can see, all of these columns are a part of the candidate key; if any single value differs to an existing row, a new row can be created.

My question is, does this design comply to fifth normal form? I am unsure as some groups of data will be repeating, however I don't believe this violates 5NF? (correct me if I'm wrong)

Update

It is possible to create a new row in this table if any single one of the columns changes, e.g. It is possible to have the same message at a different level ('User does not exist' could be logged as a warning at one point in time and an error at a different time).

If all variables are a subset of the superkey, is the database design
  5NF?

In any 1NF table (does not have duplicate rows) the whole header is a superkey. Any set of attributes (columns) from that table is a subset of that superkey. If your reasoning were right that would mean that any 1NF table is in 5NF. So, this is not correct. But, you are probably just misusing terminology.

What you are probably asking is what if the whole header is the (candidate) key -- keep in mind that a key is irreducible.

General answer to this question is again no; just because a table is all-key it does not make it automatically in 5NF -- all key tables are in BCNF (3.5). They are likely to be in 5NF too, but not as a rule.

Given the info you have provided, I am not sure if your table is all-key (in BCNF). For example, is there a FD {Message} --> {Level} ?

So to answer this properly, you would have to provide sample data and some spelled-out "business" rules.

Take a look at

https://stackoverflow.com/questions/18029821/how-to-understand-the-5th-normal-form


MySQL to SQL Server - Unixtime being major problem I'm trying to convert this query to SQL Server.

It already has a lot of updates. Still, I don't know what to do from here ( i'm not the best when it comes to programming in MySQL )

 SELECT wo.WORKORDERID,
dpt.DEPTNAME "Department",
aau.USER_ID,
aau.FIRST_NAME "Requester",
sadt.RATING "Rating Value",
spre.COMMENTS "Comments",
    FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",
    #FROM_UNIXTIME((wo.RESPONDEDTIME/1000)) "Resposta da pesquisa",
            MONTH(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Mes Pesquisa",
            YEAR(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Ano Pesquisa",
#wo.RESPONDEDTIME "RespondedTime",
aau1.FIRST_NAME "Technician"
    FROM SurveyPerRequest spr
INNER JOIN SurveyPerRequestExt spre ON spr.SURVEYID=spre.SURVEYID
LEFT JOIN SurveyDetails sdt ON spre.SURVEYID=sdt.SURVEYID
LEFT JOIN SurveyAnswers sadt ON sdt.SURANSID=sadt.SURANSID
LEFT JOIN WorkOrder wo ON spr.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser sdu1 ON wos.OWNERID=sdu1.USERID
LEFT JOIN AaaUser aau1 ON sdu1.USERID=aau1.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
    WHERE (spre.CREATEDTIME >= UNIX_TIMESTAMP('2014-04-01 00:00:00')*1000) AND (spre.CREATEDTIME <= UNIX_TIMESTAMP('2014-08-31 23:59:59')*1000)
AND aau.FIRST_NAME LIKE '%Pumputis%'
        ORDER BY spre.CREATEDTIME

Thanks a lot for the help

You mentioned Unixtime being major problem

Please note how FROM_UNIXTIME() is being used

FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",

From this line, here is what I can tell:

spre.CREATEDTIME is number of milliseconds since 1970-01-01 00:00:00 UTC
FROM_UNIXTIME() requires the number of seconds, not milliseconds
spre.CREATEDTIME has to be divided by 1000 before calling FROM_UNIXTIME()

Based on someone else's post in StackOverflow (Truncate Datetime to Second (Remove Milliseconds) in T-SQL), you will have to change it to something like:

CONVERT(datetime,char(19),spre.CREATEDTIME/1000,126) "criacao da pesquisa",

Please read T-SQL Documentation on CONVERT

  what is this # in front of some sentences??

The # character as the first character makes that whole line a comment

When you execute this script, only the first SELECT is executed

The other SELECT queries and the one UPDATE query are not executed

The first query has comments as well. If you ignore those two lines, this is what really runs:

 SELECT wo.WORKORDERID,
dpt.DEPTNAME "Department",
aau.USER_ID,
aau.FIRST_NAME "Requester",
sadt.RATING "Rating Value",
spre.COMMENTS "Comments",
    FROM_UNIXTIME((spre.CREATEDTIME/1000)) "criacao da pesquisa",
            MONTH(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Mes Pesquisa",
            YEAR(FROM_UNIXTIME((spre.CREATEDTIME)/1000)) "Ano Pesquisa",
aau1.FIRST_NAME "Technician"
    FROM SurveyPerRequest spr
INNER JOIN SurveyPerRequestExt spre ON spr.SURVEYID=spre.SURVEYID
LEFT JOIN SurveyDetails sdt ON spre.SURVEYID=sdt.SURVEYID
LEFT JOIN SurveyAnswers sadt ON sdt.SURANSID=sadt.SURANSID
LEFT JOIN WorkOrder wo ON spr.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser sdu1 ON wos.OWNERID=sdu1.USERID
LEFT JOIN AaaUser aau1 ON sdu1.USERID=aau1.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
    WHERE (spre.CREATEDTIME >= UNIX_TIMESTAMP('2014-04-01 00:00:00')*1000) AND (spre.CREATEDTIME <= UNIX_TIMESTAMP('2014-08-31 23:59:59')*1000)
AND aau.FIRST_NAME LIKE '%Pumputis%'
        ORDER BY spre.CREATEDTIME


Parameterized Queries in PostgreSQL using PHP fail to handle null values I'm using PHP and PostgreSQL on OpenBSD 6.1, with pg_prepare and pg_execute to effect parameterized queries.

$rs = pg_prepare($con, base64_encode($query), $query);
$rs = pg_execute($con, base64_encode($query), $query_parameters);

It's working for quite a lot of queries, but raises PHP errors in some cases where I'm passing, or attempting to pass, a null value to a UUID field. In attempt to have some data validation, I'm specifying for each value its datatype.

insert into dummy(text_1,ipv4_2,uuid_3,int_4) values($1::varchar(32),$2::inet,$3::uuid,$4::integer);

This works just fine for all cases, except null values. The schema does allow for nulls in all fields. Integers were causing me problems until I cast the input to an integer, and actually assigned a null when there was no value. PHP:

$p_int_4 = (int)$api_arguments['int_from_form'];
if ($p_int_4 == '' || is_null($p_int_4)) $p_int_4 = null;

The parameters are passed as an array with variables being used:

$query_parameters = array($p_text_1,$p_ipv4_2,$p_uuid_3,$p_int_4);

When I try to set the UUID variable to null, I still get a pg_execute error:

pg_execute(): Query failed: ERROR:  invalid input syntax for uuid: &quot;&quot;

How can I still have the benefits of data validation, while also sending a null to the database? Two ways way I can see to get it to pass is to take away the casting to UUID, which seems to be a pretty good layer of defense against SQL injection attacks, and casting to varchar instead and hoping PostgreSQL can "handle it" and get the casting correct on its side.

** UPDATE **

I've been "paraphrasing" production code, which I cannot paste here obviously, and it works for everything except UUIDs. This is code I'm using for the UUID, with just the variable names changed:

$p_uuid = (string)$this->api_arguments['ADD_UUID'];
if ($p_uuid == '' || is_null($p_uuid)) $p_uuid = null;

The SQL statement is this simple:

insert into dummy(myuuid) values($1::uuid);

* Final Update *

It's more accurate to say that PHP doesn't handle empty values well for integer and UUID datatypes when passing them as parameters to pg_execute.

The following code sets all parameters to NULL where they are empty.

for ($x = 0; $x < count($query_parameters); $x++) {
  if ($query_parameters[$x] == '' || is_null($query_parameters[$x]))
    query_parameters[$x] = null;
}

Thanks Jasen. Constructing a working example led me to the bug :)

if ($p_int_4 = '' || is_null($p_int_4)) $p_int_4 = null;

The equality operator in PHP is ==. Your code uses =, and will always set $p_int_4 to an empty string as a result of this mistake.


Restoring a backup gives "Failed exit code 1" I'm migrating from Heroku's Postgres to AWS RDS. I've generated a pg_backup.dump file from Heroku, installed Postgres on my local machine, launched PGAdmin, configure remote AWS server, and used the Tools > Restore feature to restore my database.
The process completed with failed (exit code: 1)
When I look throuh the progress window I see lots of errors like these:
pg_restore: creating SEQUENCE "public.auth_group_id_seq"
pg_restore: from TOC entry 203; 1259 24981038 SEQUENCE auth_group_id_seq uohhysqaagrjqj
pg_restore: error: could not execute query: ERROR:  role "xxxxx" does not exist
Command was: ALTER TABLE public.auth_group_id_seq OWNER TO xxxxx;

Browsing through my database I can see that everything has restored. Anything to worry about? Looks like the role name is different in the RDS database and that's all?

This just means that the owner of the table public.auth_group_id_seq was set to the default owner in your RDS instance. This likely happened because you did not have that owner pre-created in the RDS instance prior to migrating so it utilized the default. This isn't particularly an issue unless you had specific security policies set in your PostgreSQL instance dependent on that owner that you need to replicate in your RDS instance.


which one is faster: "insert on duplicate update" OR "update with case" Table:

CREATE TABLE t5 (
 col1 VARCHAR(36) NOT NULL PRIMARY KEY,
 col2 int(11) DEFAULT NULL,
 col3 int(11) DEFAULT NULL,
 UNIQUE (col2)
);

Records already inside:

insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 1, 1),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', 2, 2);

Which of these two queries are expected to perform better:

Query-1:

insert into t5(col1, col2, col3) values
 ('872c18ba-35ae-11e9-91a5-88d7f65253d0', 2, 888),
 ('823c3d3a-35ae-11e9-91a5-88d7f65253d0', NULL, 999),
on duplicate key update
 col2=if(values(col2) IS NULL, col2, values(col2)),
 col3=if(values(col3) IS NULL, col3, values(col3));

Query-2:

update t5 set
 col2=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 2 END),
 col3=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 888 when '823c3d3a-35ae-11e9-91a5-88d7f65253d0' then 999 END)
where col1 in ('872c18ba-35ae-11e9-91a5-88d7f65253d0', '823c3d3a-35ae-11e9-91a5-88d7f65253d0');

Please take note in query-2, the statement is tailored from client side by pruning the NULL value first.

Condition of the table and the frequency the above query is performed as below:

number of records is in hundreds of thousand
query is performed once every x-seconds, each with ~150 keys

The objective is to reduce the I/O by not hammering the db with one single query on loop, as well as preventing a deadlock due to query taken too long time (yes, there was some deadlock earlier)

col2=if(values(col2) IS NULL, col2, values(col2)) is equal to col2=COALESCE(values(col2), col2).
If ODKU causes UNIQUE (col2) violation, the whole query will fail (see fiddle, first query).
The queries are not equivalent (second query will set col2=NULL for 1st record, see fiddle).

fiddle

Finally:

  which faster: “insert on duplicate update” OR “update with case”

If no errors in queries and data INSERT .. ODKU is faster.


Adding group rights in db2 for a stored procedure I have a list of user accounts on AIX server, where the db2 database resides in. The user accounts all belong to a specific group which I can confirm from aix shell.

User accounts work in DB2. However, I cannot seem to be able to add execution rights to the group so I wouldn't have to grant execution rights to individual user accounts.

I have tried this:

GRANT EXECUTE ON PROCEDURE MYSCHEMA.MYPROCEDURE TO GROUP MYGROUP WITH GRANT OPTION;

using a user that has created the stored procedures, and it succeeds. However, when trying to execute that procedure using another account that belongs to that group in aix server, I get

"useraccounthere" does not have the required authorization or privilege to perform
operation "EXECUTE" on object "MYSCHEMA.MYPROCEDURE".. SQLCODE=-551, SQLSTATE=42501,
DRIVER=4.14.113

Any ideas what could be wrong, or what I could check?

I ran into a similar problem and while this wasn't what happened here, this answer may help others running into this.

Make sure the OS group mygroup is all lowercase. DB2 on AIX does not recognize uppercase letters in the group name and will fail to validate a user belongs to a group if that group name has uppercase letters. eg:

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(mygroup) <- this works

# id useraccounthere
uid=xx(useraccounthere) gid=yyy(MyGroup) <- this does not work

This is documented here: DB2 UDB Security


Mysql Server Login and Permission Deny Error I have a problem with MySQL server. After upgrading MySQL server from 5.3 to 5.4, I am not able to log-in using my MySQL root password (Yes, I am able to log-in without password!).

Also not able to grant permissions to users as well. Not able to change in databases using root account, while taking backup of database due to same.

When I looked into MySQL databases using root account, it doesn't show anything. While on other server, I am able to find all entries.

Kindly please help me to resolve this issue as it is critical.

Log of mysql is as below.

130906 18:46:13 [Warning] Found invalid password for user: 'root@x.x.x.x'; Ignoring user
1843 130906 18:46:13 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
1844 130906 18:46:14 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
1845 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
1846 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
1847 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
1848 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
1849 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
1850 130906 18:46:14 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
1851 130906 18:46:14 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
1852 130906 18:46:14 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
1853 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
1854 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
1855 130906 18:46:14 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
1856 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
1857 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
1858 130906 18:46:14 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
1859 130906 18:46:14 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
1860 130906 18:46:14 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
1861 130906 18:46:14 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
1862 130906 18:46:14 [ERROR] Column count of mysql.db is wrong. Expected 22, found 13. Created with MySQL 50045, now running 50532. Please use mysql_upgrade to fix this error.
1863 130906 18:46:14 [ERROR] mysql.user has no `Event_priv` column at position 29
1864 130906 18:46:14 [ERROR] Cannot open mysql.event
1865 130906 18:46:14 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
1866 130906 18:46:14 [Note] /usr/libexec/mysqld: ready for connections.
1867 Version: '5.5.32-cll-lve' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Atomicorp

First of all before performing an upgrade you should have taken a backup of all important databases (or simply all databases) using the mysqldump command.

Anyhow, to fix this please run:

shell> mysql_upgrade

To understand more what mysql_upgrade does see here.

If you get permissions errors:

mysql_upgrade -u root -p (give the root password here)

If still it fails, rename /etc/my.cnf to /etc/my.cnfbku and then rerun the command.

This will fix all the issues regarding the upgrade.


Simple indexed select query takes 350 seconds (?!) even though Filtered 100.00 "Using index condition" My logs show that the query being explained here took 352.19 seconds (and similar times for other similar queries where the contact_id and execute_at were different).

EXPLAIN SELECT
    *
FROM
    `automations`
WHERE
    (`contact_id` = 22638
        AND `job_class_name` = 'App\Jobs\MyJob'
        AND `execute_at` = '2018-12-15 16:43:00')
        AND `automations`.`deleted_at` IS NULL
LIMIT 1

Result of EXPLAIN:

| id | select_type | table       | partitions | type | possible_keys                  | key                            | key_len | ref                     | rows | filtered | Extra                 |
|----|-------------|-------------|------------|------|--------------------------------|--------------------------------|---------|-------------------------|------|----------|-----------------------|
| 1  | SIMPLE      | automations | NULL       | ref  | cId_job_executeAt_delAt_unique | cId_job_executeAt_delAt_unique | 780     | const,const,const,const | 1    | 100.00   | Using index condition |

I am inexperienced at optimizing MySql, but my guess would be that the Explain looks great, right?

Why on earth would queries like this be taking 350+ seconds? How can I diagnose and fix?

P.S. This is related to the E_WARNING: Error while sending STMT_PREPARE packet. PID=* error I frequently see.

CREATE TABLE `automations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `contact_id` int(10) unsigned NOT NULL,
  `job_class_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `execute_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cId_job_executeAt_delAt_unique` (`contact_id`,`job_class_name`,`execute_at`,`deleted_at`),
  CONSTRAINT `automations_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1519 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The optimal index for this query would be a composite index with 4 columns, in any order:

INDEX(contact_id, job_class_name, execute_at, deleted_at)

And, depending on the order you pick, you can get rid of one of the existing KEYs.

Watch out for backslashes.  'App\Jobs\MyJob' may be treated as App[LF]obs[CR]yJob or AppJobsMyJob.

352 seconds is really unreasonable.  Perhaps something else was going on, and blocking access to the rows and/or the table? (The 352s was bogus.)

"Using index condition" is not the same as "Using index".  The latter indicates a "covering index", which is not practical for this query and table.


SQL Server, how to execute a job against all databases on server? I'm trying to find the best way to execute a query against all databases on the server using SQLServerAgent or maintenance plan.

I have a stored procedure that I'd like to run on these databases, but I cannot create the stored procedure in these databases.

I tried experimenting with using exec sp_msforeachdb and DECLARE @command = '<my sp>' but I ran into the problem with escaping too many single quotes '.

So in the end, I'd like to say something like:
For all databases, run this block of code. Then schedule this job with SQLServerAgent.

    BEGIN
    SET NOCOUNT ON

    --DECLARE VARIABLES
    DECLARE
    @max INT,
    @min INT,
    @table_name NVARCHAR(256),
    @table_schema NVARCHAR(256),
    @sql NVARCHAR(4000)

    --DECLARE TABLE VARIABLE
    DECLARE @table TABLE(
    id INT IDENTITY(1,1) PRIMARY KEY,
    table_name NVARCHAR(256),
    table_schema NVARCHAR(256))

    --CREATE TEMP TABLE THAT STORES INFORMATION FROM SP_SPACEUSED
    IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
    BEGIN
    DROP TABLE #results
    END

    CREATE TABLE #results
    (
    [database_id] [int] NULL,
    [server_name] [nvarchar](256)  NULL, --- new, server name
    [database_name] [nvarchar](256)  NULL, --- new, db name
    [table_schema] [nvarchar](256) NULL,
    [table_name] [nvarchar](256) NULL,
    [table_rows] [int] NULL,
    [reserved_space] [nvarchar](55) NULL,
    [data_space] [nvarchar](55) NULL,
    [index_space] [nvarchar](55) NULL,
    [unused_space] [nvarchar](55) NULL
    )

    --LOOP THROUGH STATISTICS FOR EACH TABLE
    INSERT @table(table_schema, table_name)
    SELECT
    table_schema, table_name
    FROM
    information_schema.tables

    SELECT
    @min = 1,
    @max = (SELECT MAX(id) FROM @table)

    WHILE @min <= @max
    BEGIN
    SELECT
    @table_name = table_name,
    @table_schema = table_schema
    FROM
    @table
    WHERE
    id = @min

    --DYNAMIC SQL
    SELECT @sql = 'EXEC sp_spaceused ''[' + @table_schema + '].[' + @table_name + ']''' --get dataA

    --INSERT RESULTS FROM SP_SPACEUSED TO TEMP TABLE
    INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
    EXEC (@sql) --set dataA^

    --UPDATE SCHEMA NAME
    UPDATE #results
    SET table_schema = @table_schema WHERE table_name = @table_name
    UPDATE #results
    SET
    database_id = (DB_ID()),
    server_name = (select @@SERVERNAME),
    database_name = (SELECT DB_NAME() AS [Current Database]);

    SELECT @min = @min + 1
    END

    --REMOVE "KB" FROM RESULTS FOR REPORTING (GRAPH) PURPOSES
    UPDATE #results SET data_space = SUBSTRING(data_space, 1, (LEN(data_space)-3))
    UPDATE #results SET reserved_space = SUBSTRING(reserved_space, 1, (LEN(reserved_space)-3))
    UPDATE #results SET index_space = SUBSTRING(index_space, 1, (LEN(index_space)-3))
    UPDATE #results SET unused_space = SUBSTRING(unused_space, 1, (LEN(unused_space)-3))

    --INSERT RESULTS INTO TABLESIZEGROWTH
    INSERT INTO TableSizes.dbo.TableSizeGrowth (database_id, server_name, database_name, table_schema, table_name, table_rows, reserved_space, data_space, index_space, unused_space)
    SELECT * FROM #results

    DROP TABLE #results
    END

Create a SQL Agent Job with a Job Step of type "Powershell" on the SQL Server instance where your databases reside.

# Define the query to be run against each database
Set-Variable -Name MyQuery -Value "SELECT @@SERVERNAME"

# Access SQL Server information by using a drive and file system analogy
Set-Location "SQLSERVER:\SQL\localhost\DEFAULT\Databases"

# Get the list of databases
Get-ChildItem | Set-Variable -Name DBList

# Iterate through the list, execute your query
ForEach ( $DB in $DBList) {
    Invoke-Sqlcmd -query $MyQuery -database $DB.Name -ServerInstance 'localhost' -ErrorAction Stop
}

Schedule it like any other job.


Oracle IMPDP job ORA-30036 unable to extend segment by 8 in undo tablespace 'UNDOTBS1' I'm using IMPDP with the "network_link" for perform export/import in a single-pass.

It runs fine until I get this error.

  ORA-39171: Job is experiencing a resumable wait.
  ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

Here are the datafiles for the UNDO tablespace:

oracle@ORA3> select bytes/1024/1024/1024, maxbytes/1024/1024/1024
             from dba_data_files
             where tablespace_name like '%UNDO%';

Output:

BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024
-------------------- -----------------------
          31.9999847              31.9999847
          31.9999847              31.9999847
          31.9999847              31.9999847
                   2              31.9999847
                   2              31.9999847
                   2              31.9999847

So I have around 196G of undo space. I added the last three data files after the error.

The largest tablespace is 674G. Do I need to make the UNDO tablespace larger than this? Or is there something else I should do?

Ensure that the default undo tablespace has enough capacity.

Based on your output, I see 3 tablespaces with 2GB of size.
Perhaps one of them is the default assigned UNDO and auto extend is off.

Another prediction is that the default undo could be another tablespace with a different name, the query executed does not indicate the default undo but rather checks the tablespaces named with 'UNDO'.

You can check the default undo tablespace using the below query

select value from v$parameter where name like 'undo_tablespace';


Can I do vacuumlo,vacuumdb and reindex in live production system without any downtime in postgres database We are using a Postgres database (9.3) as our production database. It is generating huge lo objects. Due to this, the size is growing very fast. So we need to do database cleaning frequently. To remove these lo objects we are having long downtimes.

To clean the DB, we are performing the below steps in sequence:

vacuumlo database_name
vacuumdb database_name -t pg_largeobject
vacuumdb database_name -t pg_largeobject_metadata
reindexdb -t pg_largeobject database_name
reindexdb -t pg_largeobject_metadata database_name

Can we do the above online (during the night) without having any downtime?

"reindexdb" takes the most stringently lock, and is the only thing likely to be causing downtime.

Unless you have evidence that it is necessary, just stop doing the "reindexdb".  It might be necessary to do it once, in order to get you out of the hole, but shouldn't be necessary to do each time you do "vacuumlo".


Case Statement With Group By I think it is my group by in this syntax that is skewing my desired result set.  I ONLY want to see the data I am requesting, however my query returns all the data like this:

salesman    Q1 2015 Q1 2016
Blue    NULL    NULL
Blue    NULL    NULL
Blue    200     NULL
Blue    300     NULL
Blue    NULL    400
Blue    NULL    600

What I (thought) my syntax would return would ONLY be the below

salesman    [Q1 2015]   [Q1 2016]
Blue          500          1000

This is my DDL (well garbage data, but gets point across)

Create Table Barnacles
(
  warrantypurchasedate date
  ,salesman varchar(500)
  ,itemprice float
)
Insert Into Barnacles (warrantypurchasedate, salesman, itemprice) Values
('01/01/2014', 'Blue', 100)
,('02/01/2014', 'Blue', 300)
,('01/01/2015', 'Blue', 200)
,('02/01/2015', 'Blue', 300)
,('01/01/2016', 'Blue', 400)
,('02/01/2016', 'Blue', 600)

This is the query syntax I attempted:

Select
salesman
,case when DATEPART(Year, warrantypurchasedate) = '2015' AND DATEPART(Quarter, warrantypurchasedate) = '1' then SUM(itemprice) else NULL end As [Q1 2015]
,case when DATEPART(Year, warrantypurchasedate) = '2016' AND DATEPART(Quarter, warrantypurchasedate) = '1' then SUM(itemprice) else NULL end As [Q1 2016]
FROM Barnacles
GROUP BY salesman, warrantypurchasedate

What should I alter in my query so that I only see the SUM() for Q1 15 and the SUM() for Q1 16 not a row returned for each entry in the table?

Untested, but I believe your query should look like:

SELECT salesman
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2015'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice
           end) As [Q1 2015]
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2016'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice
           end) As [Q1 2016]
FROM Barnacles
WHERE DATEPART(Year, warrantypurchasedate) in ('2015', '2016')
  AND DATEPART(Quarter, warrantypurchasedate) = '1'
GROUP BY salesman

I moved  sum outside of case, and added a WHERE clause to filter out relevent rows. I also removed date from the GROUP BY clause, otherwise you will get 1 row per date.


Creating a "database link" from Oracle to SQL Server Is there a way for Oracle to read from SQL Server?

We are basically trying to read from a SQL Server table and update the information in Oracle. We used to have a database link in Oracle to another Oracle database.

Now the application is replaced by a new application that runs SQL Server, so we're trying to replicate the same process but now it's Oracle to SQL Server. Hope that makes sense.

The Oracle database server accepts SQL statements that query data stored in several different databases. The Oracle database server with the Heterogeneous Services component processes the SQL statement and passes the appropriate SQL directly to other Oracle databases and through gateways to non-Oracle databases. The Oracle database server then combines the results and returns them to the client.

Database Heterogeneous Connectivity Administrator's Guide

Oracle provides heterogeneous services to query non-Oracle databases like SQL Server. If you need some guidance here are some useful links.

 Connecting to Non-Oracle Data Sources Through Gateways

 How to Set up Third Party Databases Drivers in Oracle SQL Developer

 Heterogeneous Services - Generic Connectivity


Getting ORA-00947: not enough values error while inserting date into table I have created a table

    create table Times (
departure date not null,
arrival date not null,

)

and I need to insert date and time in both columns

    insert into Times(departure, arrival)
values (to_date ('2018-05-02 12:40', '2018-05-02 15:08'))

I get the following error

ORA-00947: not enough values

Is there a way to solve this problem?

You are only inserting one column value, not both. The second parameter in to_date should be the format used by the first parameter, not another date. Your insert should look like this:

insert into times (departure, arrival) values
  (to_date('2018-05-02 12:40','YYYY-MM-DD HH24:MI'),
   to_date('2018-05-02 15:08','YYYY-MM-DD HH24:MI'));


Postgres Vacuum in Function After googleing a bit, it's plainly clear you cannot run vacuum from a function:

Postgres mailing - Vacuum behaviour in plpgsql function

  You can't run VACUUM from inside a function because of memory-management
  limitations.  In current sources there is an error check to prevent you
  from trying.

Stackoverflow - VACUUM cannot be executed from a function or multi-command string

  Once the data is loaded, I would like to "vacuum analyze" the affected tables, both to recover the space from the deleted records and to accurately reflect the new contents. [...] When I run this, I get:

  ERROR:  VACUUM cannot be executed from a function or multi-command string

But I really need to "automatically" run vacuum whenever a certain function is run: this function updates a lot of records (if not all of them) so the number of dead rows increases quite fast and other query performance decrease a lot.
The problem is the function is called from another program.
Any best practice?
Should I manually add the vacuum statement into a separeted piece of code after the function execution, someting like

sql = SELECT my_function()
<program.execute(sql)>
sql = VACUUM [FULL | ANALYZE] my_updated_table
<program.execute(sql)>

From the documentation page:

  Tip: Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. If you have such a table and you need to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it. All these options require exclusive lock. Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are complete.

If you can't get away with an exclusive lock on the table, you also might be fine with a simple ANALYZE <tablename>;, as that will only take a read lock and should update the statistics for the table and reduce ( if not entirely eliminate ) performance degradation for the other queries.  Then you can let the regular autovacuum daemon do its thing ( or whatever your current custom vacuum solution might be ) normally.

If your table doesn't even have a clustered index, you will first need to do an ALTER TABLE <tablename> CLUSTER ON <indexname>;.  You should probably have some kind of clustered index anyway: Having a massive heap table and poor performance against it often goes hand in hand.

As far as best practices go, I would be highly inclined to recommend the ANALYZE approach insofar as doing an automatic statistics update is by far preferable to an automatic index rebuild or virtually any form of VACUUM FULL being run without an admin actively watching it.


Database software solution Our company has user data (name, email, phone number, systems of access, AD username) in a large Excel workbook. The Excel workbook is becoming very slow because of the 1000s of entries in it, and we're looking for an alternative solution.
I was think Microsoft Access because of the ease of use and ability to quickly make forms and reports, but my boss is worried about Access becoming end of lifed soon. Anyone have another solution for this? I know a little SQL, so that's an option, but not a preferred option.

SQL Server express is a good choice.  You can also create a very low cost (a few bucks a month) database only on Azure.  In that scenario there would be no maintenance.  I would argue SQL is what you are dealing with when working on data and Excel can easily connect to a table.  This may be your best solution.


Get count of processed queries I have been searching a while to find a way to retrieve the amount of queries my postgres database is processing and responding to. I found various statistics about disk I/O, insert/update statistics but nothing specifically reflecting queries.

Why do I need it?
I am developing a client application and I want to find out if I missed any sections in my program where a lot of small queries are executed in bulk that could possibly be done in one big query to speed up queries from very far away clients (signal propagation delay).

To monitor this I wrote myself a tool displaying a "count per second" graph by polling

SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database

SELECT sum(tup_fetched) FROM pg_stat_database

and displaying the difference to the last poll.

However the first statement does not seem to update propery, or not display "select" statements at all as it is sometimes showing 0, which is impossible because it should at least show 1 even on an idle server due to the poll-query.
The second query does not seem to work either, it is showing 0 most of the time.

Any ideas?

Also if anyone knows if there is a way to get the actual network read/write statitic that would help as well. (not in packages or traffic, but in "recieved: query, sent: 1000 rows, received: next read request, sent: (last) 20 rows -> 2 operations recorded", so basically I can derive with a ping of 150ms the total time needed would be 600ms minimum, which could be reduced to 300ms by increasing the fetch size)

If you have any ideas as to how to test this with a different approach I am also open to suggestions.

You'll want to use the built-in extension pg_stat_statements, or the external pg_stat_plans enhanced approach.

There aren't any network I/O statistics kept by PostgreSQL yet. There's someone working on it for 9.4, though I don't know if it'll get accepted into 9.4.


Replication Lag on Postgres AWS RDS Service We have a single master/single streaming replica Postgres 9.3 db on AWS. The load is not terribly high - this is a development/staging environment. (The production shows similar metrics). Point is the "ReplicaLag" shown in Cloudwatch oscillates wildly during the day between 0 and 200 seconds. I've changed max_wal_senders from 5 to 10 with no change.

Any suggestions for diagnosing this?

(This is a t2.small master and t2.small replica, however the production instance is large, and exhibits the same issue. CPU is < 2%, connections < 60, iops only about 5-15 count/second).

I had the same issue with our staging RDS. After quite a while I found a blog post that says:

  In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing.

To test this I wrote a simply script which updates a random record every 0.5s and the lag went to 0.

From the AWS docs under Read Replica Limitations with PostgreSQL -

  A PostgreSQL Read Replica reports a replication lag of up to five minutes if there are no user transactions occurring on the source DB instance


Store SSRS Report export directly to database As a requirement, I have to check for a certain condition on some data in the database, if the condition is true, then I have to create a pdf report and store it on a table.

I have everything set up as a T-SQL SP except the pdf report part.

Is there anyway to execute an SSRS Report, export it to PDF and store the pdf in a varbinary column directly from T-SQL ?

Otherwise the only solution would be creating a Windows Service to do this (I Know how to do this but the T-SQL way would give me more flexibility).

Thanks!

You should be able to write any sort of code in .Net and connect it into SQL Server.  See http://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server-2005 for an example.


Automating failover in PostgreSQL 9.1 How does one setup two identical servers for automatic failover in PostgreSQL 9.1.

OS

  Centos 5
  PostgreSQL 9.1 compiled from source
  The postgres user account exists on both machines and has a ssh passwordless key to connect to both machines.

My Current Setup :

Master server configuration:

postgresql.conf:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 16
wal_keep_segments = 8
archive_mode = on
archive_command = 'cp "%p" /opt/pgsql91/archive/"%f"'

pg_hba.conf:

 host  replication   all   10.0.66.1/32      trust
 host  replication   all   10.0.66.2/32      trust

Standby Server

postgresql.conf and pg_hba.conf are identical to what is configured on the master server.

recovery.conf:

 standby_mode = 'on'
 primary_conninfo = 'host=10.0.66.1'
 trigger_file = '/opt/pgsql91/data/trigger.txt'

Thanks to hzRoot, I now understand how to switch the server from standby to master.

Using the following commands, I can synchronize the new slave with the new master and then get replication backup and running.

On the new master (10.0.66.2)

su - postgres
touch trigger.txt in /opt/pgsql91/data/
recovery.conf becomes recovery.done
psql -c ";SELECT pg_start_backup('backup', true)";
rsync -a -v -e ssh /opt/pgsql91/data/ 10.0.66.1:/opt/pgsql91/data/ --exclude postmaster.pid
psql -c ";SELECT pg_stop_backup()";

On the new slave (10.0.66.1)

create the recovery.conf : cp recovery.done to recovery.conf
vi recovery.conf change ip address : primary_conninfo = 'host=10.0.66.2'
start postgresql

So my questions are now :

Is this the correct way to switch roles?
Has anyone automated this process, if so what did you do?
If synchronous replication is enabled, I noticed the new master server won't commit any transactions because it is waiting for the slave to respond.  There is no slave however because the other server, the old master is down.  Is this correct or do I need to temporarily disable synchronous replication while the new slave is down?

Check out repmrg:

  repmgr is a set of open source tools that helps DBAs and System
  administrators manage a cluster of PostgreSQL databases..

  By taking advantage of the Hot Standby capability introduced in
  PostgreSQL 9, repmgr greatly simplifies the process of setting up and
  managing database with high availability and scalability requirements.

  repmgr simplifies administration and daily management, enhances
  productivity and reduces the overall costs of a PostgreSQL cluster by:

  monitoring the replication process; allowing DBAs to issue high
  availability operations such as switch-overs and fail-overs.

It does two things:

repmgr: command program that performs tasks on your cluster and then exits
repmgrd: management and monitoring daemon that watches the cluster and can automate remote actions.

For automatic failover, repmgrd does the trick and is not a SPOF in your network, like pgPool. However, it is still important to monitor all deamons and bring them back up after failure.

Version 2.0 is about to be released, including RPM's.


Need help importing SSIS packages in SSMS I am in charge of creating a documentation for an ETL Process which involves a Remote SQL Server which is linked to a Postgre DB. In order to periodically update the data, Jobs have been setup using Server Agent. All this was done by someone who is no longer in contact. But now I want to go through/viewing what exactly is happening inside the SSIS packages.

This ETL Process is running on a remote SQL Server.

I have SQL Server 2014 DB Engine and SQL Server Mgmt Studio installed in my local machine and yes I did select the Integration Services feature while in the installation stage.

After some research I found that in order to view SSIS packages, I first needed to connect using Server Type as "Integration Services", but when I try do that I get an "Access is Denied" error message. In order to tackle this problem I tried to change Access Rights in the DCOM configurations but still the same result.

If I can import these packages to my local machine then I can use Visual Studio 13 to open and know what exactly each package is doing. (Correct me if I'm wrong)

Can anyone help me to import a package which is remotely stored in a server?

Please note that the Remote Server and my local computer do not have the same name and password.

Quirk or Perk?

Try this. Close SQL Server Management Studio. Then, right-click it's icon and Run as Administrator. Then, try viewing those packages once more.

That being said...

Use DTUTIL

I find DTUTIL the easiest way to import/export packages to and from SQL Servers.

https://technet.microsoft.com/en-us/library/ms162820(v=sql.110).aspx

Example:

Update, the code from this author looks promising for mass-import of packages using DTUTIL though I have not tried it.

https://canserhat.wordpress.com/2012/08/23/export-and-import-all-ssis-packages-in-sql-server-2008/


Get the last ID after INSERT the PostgreSQL database with PgBouncer Previously we used PostgreSQL 9 with PgBouncer in session mode, and with that was able to easily create a new record and get the last ID with pg_get_serial_sequence and CURRVAL.
INSERT INTO "table" ("column") VALUES ('ABC');
SELECT pg_get_serial_sequence('table', 'tableid') AS seq;
SELECT CURRVAL('public.table_tableid_seq') AS ins_id;

But the use of the Session mode of PgBouncer proved to be extremely problematic for us, with several operations per second, the Pool easily reached the capacity of connections, so we switched to PostgreSQL 12 with PgBouncer in Transaction mode.
One problem that has begun to occur with this mode is that using operations that require being in the same session does not work, not allowing to use CURRVAL.
What is the best alternative to continue working in Transaction mode and get the ID after INSERT, without the risk of returning the ID generated by the same operation by another user?
An important detail is that we use a PHP Framework that does not allow  to add an INSERT ... RETURNING for each operation.

PostgreSQL creates a sequence by default per table. e.g actor_id_seq for the actor table on the column id.
So, this will return the last inserted id value:
select currval('actor_id_seq'::regclass)

The sequence generator operation is not transaction-safe. It means that if two concurrent database connections attempt to get the next value from a sequence, each client will get a different value. If one client rolls back the transaction, the sequence number of that client will be unused, creating a gap in the sequence.


Should we centralize databases or have a database with each application? My company develops different web applications for different clients.
For each new client (or important project), we use a new server with a new MySQL database.

This way, an application can access its database only and the database is directly linked with the project (as it is in the same server so basically in the same directories). Until now it has not shown any particular problem.

As we are changing our methods, my colleagues wonder if we could use instead only one database with all projects in it and use some master/slave replication.

Our different databases have some tables in common, but with different data as they belong to their respective apps.

I did some research on the subject :
I find mostly posts that recommend using a database for each application for these reasons :

Distinct log files and backups for each project
Better scalability as we can inspect the needs for each database
Simple to use from application without having to change data or create views, etc.

Most of what I read about this is between 5 and 10 years old so I'm not sure if the disadvantages of the other solution are still relevant.

I struggle to find precise information and advantages about the unique-database solution and the use of Master/Slave replication for this kind of cases.

I'm new to this idea : I understand the concept of master/slave here but I don't have technical knowledge about it.

(Could using a unique database be beneficial ?)

Should we have :

The database of an app on the same server than the app,
A single server for all databases, and keep the apps in other hosts ?

Note that we are using MySQL but PostgreSQL is a possible option if we chose to use a single database with different schemas for each app.

What you have read (even though it's oldish) is still very relevant!

Your problem is called multi-tenancy - to do it or not - the consensus appears to be "not", but there is a wide divergence of views on this issue - you can "partially multi-tenant" - i.e. keep virtually unchanging reference tables in common for example - however the same issues as outlined below apply to "partial" multi-tenancy.

My advice - use separate databases if at all possible!

If one app goes down, at least the others will still work, with a central database, if one app brings the server down, you're bunched (nothing will work).
Also, for migrations, if you need to, say, migrate one of your apps (size/speed/whatever problems - or you wish to dip your toe into the cloud testing with one app), then you will have difficulty separating out your apps, whereas if you use separate systems, you won't have any difficulties on that score!

If you'd care to explore this in more detail, I have answered questions on this issue before - check them out (1, 2 and 3 and links therein). Obviously cost and the nature of the expertise readily available to you is an issue so it's worth checking out your options before coming to a decision - you

Sometimes the wise heads are indeed on old shoulders! :-)

Another recommendation - use PostgreSQL if at all possible! It is vastly superior to MySQL in a myriad of ways - SQL compliance, JSON, Geospatial, CHECK constraints, SET operators ... long list... you won't regret it!


Optimize PostgreSQL for a lot of INSERTS and bytea updates What we have (software):

PostrgeSQL 9.3 with base configuration (no changes in postgresql.conf)
Windows 7 64 bit

Hardware:

Intel Core i7-3770 3.9 Ghz
32 Gb RAM
WDC WD10EZRX-00L4HBAta Drive (1000Gb, SATA III)

So, we have to load into DB aprox. 100.000.000 rows with bytea column, and more simple 500.000.000 rows (without LOBs). There are 2 varchar indexes on 1st table (with 13, 19 length) and 2 varchar indexes on 2nd table (18, 10 lengths).
There are also sequences for id generation for each table.

By now these operations are doing with 8 connections in parallel with 50 JDBC batch size. The picture below demonstrates system load: it is zero-load on postgresql processes. After 24 hours of loading we have loaded only 10.000.000 rows which is very slow result.

We are asking for help in tuning PostrgreSQL configuration in purposes of:

1) for ultra fast loading this amount of data, it is once-only operation, so it could be temporary configuration

2) for production mode for doing moderate number of SELECTs into these 2 tables by their indexes without join and without sorting.

For insert performance, see speeding up insert performance in PostgreSQL and bulk insert in PostgreSQL.

You're wasting your time with JDBC batching for insert. PgJDBC doesn't do anything useful with insert batches, it just runs each statement. <-- This is no longer true in newer PgJDBC versions, which can now batch prepared statements to reduce round-trip times considerably. But it's still better to:

Use COPY instead; see PgJDBC batch copy and the CopyManager. As for number of concurrent loaders: Aim for a couple per disk, if the operations are disk I/O bound. Eight is probably the most you'll want.

For your "production mode" I suggest loading a sample of data, setting up the queries you expect to run, and using explain analyze to investigate performance. For testing purposes only, use the enable_ params to explore different plan selections. Set the query planner cost parameters (random_page_cost, seq_page_cost, effective_cache_size, etc) appropriately for your system, and make sure shared_buffers is set appropriately.  Continue to monitor as you add a simulated production workload, using the auto_explain module, log_min_duration_statement setting, the pg_stat_statements extension, etc.

For details, see the PostgreSQL user manual. I suggest popping back here when you have a more concrete problem with explain analyze query execution details, etc.