What fillfactor for caching table? I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around 300MB. I have spotted it is very, very often VACUUMed and wonder if changing the fillfactor would help?

The key words here are:

"heavily updated"
"in the table for 2-3 hours".

Point 1. is indication for a lower fill factor, while 2. is the opposite. It helps performance if multiple row versions are stored on the same data page. H.O.T. updates would achieve that. Read here or here. They need some wiggle room on the data page - like dead tuples or space reserved by a fillfactor < 100. But they can only do their thing, if no index involves any of the updated columns, which should be true for your case.

Another important factor here would be the tuple size (in comparison to your page size (which is most commonly 8 kb). More details in this related answer:

Measure the size of a PostgreSQL table row

If the tuple size is 4 kb or more, reducing the fill factor would be futile, since there can never be more than one tuple on a data page. You might as well leave it at 100 (which is the default anyway). However, some data types are "toasted" and stored out-of-line if they exceed a size limit, so tuples requiring that much in the main relation fork are rare.

Whatever you do, VACUUM will be run often. And that's generally a good thing, I wouldn't worry about that. You create lots of dead tuples. VACUUM identifies dead rows that are not visible to any open transaction any more. The manual:

  The standard form of VACUUM removes dead row versions in tables and
  indexes and marks the space available for future reuse.

Bold emphasis mine.
You can play with per-table settings for autovacuum to trigger it less (or more) often for this table only:

  The default thresholds and scale factors are taken from
  postgresql.conf, but it is possible to override them on a table-by-table basis;

Bold emphasis mine. In particular with autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. Running VACUUM a lot might actually be a good idea, instead of a very low fillfacter. That depends on access patterns. If all tuples live, say, 3 hours and each is updated several times, I would still lower the fillfactor to something like 50. You'll have to test and find the sweet spot.

Alternatives

All this aside, since your data seem to be volatile to begin with: use an UNLOGGED table:

  Data written to unlogged tables is not written to the write-ahead log
  (see Chapter 29), which makes them considerably faster than
  ordinary tables. However, they are not crash-safe: an unlogged
  table is automatically truncated after a crash or unclean shutdown.
  The contents of an unlogged table are also not replicated to standby servers.

Bold emphasis mine. Don't use this if your server might crash and you still need the data afterwards. But if we are talking about session data for web applications, this might be an acceptable price to pay.

Or, even more radical: Use a key-value store like Redis if you can do without the features and security provided by an RDBMS altogether.


SYMMETRIC key & certificates role in sql server? 1)

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

I'm trying to figure out about the SQL Server encryption.

once I've executed the code above , is there any way to find out later what is the password value for the SecureSymmetricKey ?

2)

If now i'm doing this with Certificates :
I'm ( the admin)  created

CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

only I know the password.

later I :

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'

CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

until now , Its all ok.

now , When a hacker comes to the computer  , all he have to do is :

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and then :

SELECT
       convert( NVARCHAR(max), decryptbykey(namePAss))
FROM  tbl1

so where is the protection in certificates ? no one asked him for a password ( like in password encryption (as in my first question)...?he only needed to know the certificate name

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
    BY CERTIFICATE MyCertificate

and its not a problem to find out what is the certificate name ...So where is the protedtion in decypher data from the Hacker ?

3)

When im using

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';

DECLARE  @str NVARCHAR(100)
SET @str = 'lala';

OPEN SYMMETRIC KEY SecureSymmetricKey
    DECRYPTION BY PASSWORD = N'StrongPassword';

Whom am I trying to protect the data from ?
the data being sent from client to server ? ( the data is being sent by plain text - I can't activate SQL commands before sending the data...) or people who has access to the SQL server?

You are protecting agains accidental media loss (a lost laptop with the database, your drive showing up on a flea market with a copy of the database or of a backup) etc etc. Any scheme in which the processing itself (the database engine or the application) requires to access the data without the user providing a password only offer access agains media loss. The encryption key hierarchy is rooted on the system DPAPI encrypted key, in other words on the password of the service account. Such a scenario never protects agains a hacker that gets access to your SQL Server, and is not meant to protect such.

The alternative is to ask the user for a password each time it uses the application and use this password to open the key at the top of the encryption key hierarchy (usually a certificate in the database). This scheme is very seldom deployed in such cases as some multi-tenant scenarios when the tenants do no trust the hosting operations/administrators.


Any way to provide Sqlplans to end users who don't have a DBMS I work on a cloud application using SQL Server (2008R2 or 2012 depending on the server). Recently some clients have requested the ability to view query plans themselves.

I can get the query plan xml without difficulty, but I've just realised that the majority of these clients aren't going to have a SQL Server available to them to open the *.sqlplan file that we've normally used inhouse for examining them.

Is there any way to provide the sqlplan files to these clients without them requiring SQL Server to view them? It's preferred if I can do it directly via Powershell and SQL, but I'm open to anything.

They don't require SQL Server full installation to view the execution plans. They just need the tools/client software, which they should realize this if they want to look at them.

They can use the following to view a plan file:

Download SSMS
SentryOne Plan Explorer


Opencart mysql query optimize I am using Opencart 2.x with mysql. I have created a custom module for excel export. My query took more than 2 minutes to execute. So I want to optimize my query.

EXPLAIN shows the following image

This is my query

SELECT o.order_id,
       Concat(o.firstname, ' ', o.lastname)            AS customer,
       pov.sku,
       oo.value,
       pov.segment,
       pov.colour,
       op.quantity,
       o.shipping_address_1,
       o.shipping_address_2,
       o.shipping_city,
       o.shipping_zone,
       o.shipping_country,
       o.shipping_postcode,
       (SELECT os.NAME
        FROM   oc_order_status os
        WHERE  os.order_status_id = o.order_status_id
               AND os.language_id = '1') AS order_status,
       (SELECT Count(order_product_id)
        FROM   oc_order_product
        WHERE  order_id = o.order_id) AS product_count,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.code = 'coupon') AS coupon,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title LIKE '%Shipping Fee%') AS shipping_cost,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title LIKE '%COD Fee (30 INR)%') AS cod,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title = 'CGST') AS cgst,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title = 'SGST') AS sgst,
       (SELECT ot.value
        FROM   oc_order_total ot
        WHERE  o.order_id = ot.order_id
               AND ot.title LIKE '%Sub-Total%') AS sub_total,
       o.total,
       o.currency_code,
       o.currency_value,
       o.payment_method,
       o.date_added,
       o.date_modified
FROM   oc_order o
       LEFT JOIN oc_order_option oo
              ON ( oo.order_id = o.order_id )
       LEFT JOIN oc_product_option_value pov
              ON ( pov.product_option_value_id = oo.product_option_value_id )
       LEFT JOIN oc_order_product op
              ON( o.order_id = op.order_id )
WHERE  o.order_status_id >= 0
GROUP  BY oo.order_option_id
ORDER  BY o.order_id DESC

The reason for slow down of query is clearly visible in the query plan. There are columns need to be indexed, to tune the query performance. In your query you have used oc_order_option table which does not have order_id column indexed. Index it using following command:

ALTER TABLE `oc_order_option` ADD KEY `OrderID` (`order_id`)

Same thing is happening with oc_order_product and it needs to be tweaked with following command:

ALTER TABLE `oc_order_product` ADD KEY `ProductOrderID` (`order_id`)

Also you need to tweak oc_order as well, use following:

ALTER TABLE `oc_order` ADD KEY `OrderStatusID` (`order_status_id`)

After implementing the tweaks run your query and see the performance difference.


PostgreSQL - Replication I have several questions about the (streaming) replication in PostgreSQL 10:

In case of a failover, how should I rebuild the standby ? Do I
have to take a backup of the new primary and rebuild the new standby (in this case, I'm sure to have the latest version of all the file in my standby) ? Or do I have to take the backup of the old primary, restore it and apply some WAL (the restore operation will be shorted in this case) ? Both solutions work and both solutions have theirs pros and cons.
The modifications in the configurations files
aren't replicated (postgresql.conf, pg_hba.conf). Do you have any
solutions/feedbacks to keep the configuration up to date in the
standby database ?

Thanks for your help,

For converting a failed master into a new replica, you could look into pg_rewind.  I have not used it, but my understanding is that this is its entire reason for being.  I think it would be simpler and safer to simply throw away the failed master and reclone from the new master with pg_basebackup, but that could certainly be a bit hard on the network bandwidth.

For keeping configuration files in sync, I don't think it would be reasonable to create an elaborate tool just for PostgreSQL.  There are already-built elaborate tools for configuration file management, such as Chef, CFEngine, and Puppet, and they should work for PostgreSQL config files the same way as they work for apache, sendmail, etc.  Personally, every time I deploy a config change to production master (maybe twice a year?) I just ask myself "Should this change also go to the replica?" and if the answer is "yes" I go make the change.  If I wanted to go beyond that, I would use an established configuration management tool.  But there is one trick I use for backing up the config files.  When you use pg_basebackup -Ft -z -D backup_dir for a single-tablespace database, it creates a directory backup_dir with just a single base.tar.gz file in it.  If I change a config file and don't feel like taking a new base backup, I just copy the config file into that directory to live beside the base.tar.gz file.  If you have a restore/clone script, it is pretty easy to have it copy any 'loose' files it finds in the backup directory to overwrite any that it just unpacked from tar.


Partitioned view with dedicated "routing" column I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure.

I can not change that behavior, so I built VIEWs that make it easier to handle that data for reporting purposes, like this:

CREATE VIEW [ALL COMPANIES$Employee]
AS
SELECT 'Company 1' as Company, * FROM [Company 1$Employee]
UNION ALL
SELECT 'Company 2' as Company, * FROM [Company 2$Employee]
UNION ALL
SELECT 'Company 3' as Company, * FROM [Company 3$Employee]
UNION ALL
SELECT 'Company 4' as Company, * FROM [Company 4$Employee]

That works well for the read case.

Now I want to also use these views for easier interfacing with other applications, that is, inserting and updateing data.

I do know about partitioned views, but this is not exactly the same, as I don't have specific columns with CHECK constraints, but "routing" to the underlying tables would have to be done with only the information of the newly introduced column Company.

Is there anything like that? Otherwise, I'm happy about all other proposed solutions that save me from using dynamic SQL.

One approach if you can't use a partitioned view is INSTEAD OF triggers like the examples below.  Note that the UPDATE trigger assumes that one never changes a primary key value after the initial insert.

CREATE TRIGGER [TRI ALL COMPANIES$Employee] ON
    [ALL COMPANIES$Employee]
    INSTEAD OF INSERT
AS

INSERT INTO [Company 1$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 1';

INSERT INTO [Company 2$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 2';

INSERT INTO [Company 3$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 3';

INSERT INTO [Company 4$Employee] (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM inserted
WHERE Company = 'Company 4';
GO

CREATE TRIGGER [TRU ALL COMPANIES$Employee] ON
    [ALL COMPANIES$Employee]
    INSTEAD OF UPDATE
AS

UPDATE target
SET
      Col2 = inserted.Col2
    , Col3 = inserted.Col3
FROM [Company 1$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 1';

UPDATE target
SET
      Col2 = inserted.Col2
    , Col3 = inserted.Col3
FROM [Company 2$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 2';

UPDATE target
SET
      Col2 = inserted.Col2
    , Col3 = inserted.Col3
FROM [Company 3$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 3';

UPDATE target
SET
      Col2 = inserted.Col2
    , Col3 = inserted.Col3
FROM [Company 4$Employee] AS target
JOIN inserted ON inserted.Col1 = target.Col1
WHERE inserted.Company = 'Company 4';
GO

CREATE TRIGGER [TRD ALL COMPANIES$Employee] ON
    [ALL COMPANIES$Employee]
    INSTEAD OF DELETE
AS

DELETE target
FROM [Company 1$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 1';

DELETE target
FROM [Company 2$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 2';

DELETE target
FROM [Company 3$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 3';

DELETE target
FROM [Company 4$Employee] AS target
JOIN deleted ON deleted.Col1 = target.Col1
WHERE deleted.Company = 'Company 4';
GO


Duplicating rows based on a count column SQL Server I have the following table:

MyTable
PlaceID NameID  Code    CodeCount
1       1       1234A   2
1       2       1234A   3
1       3       1234A   1
2...
2...
3...
3...

I want to select 1234A where PlaceID = 1, the number of times equal to the sum of the CodeCount. So, the result would be six rows like this:

MyResult
Code
1234A
1234A
1234A
1234A
1234A
1234A

Eventually, this selection will have a bunch of different codes for a single PlaceID. YES I know I'm duplicating data, in the end it's not going to be a lot and I'll explain why I'm doing this below.

First, I needed to get the sum of the counts:

DECLARE @cdcnt INT = (SELECT SUM(CodeCount) FROM MyTable WHERE PlaceID = 1 AND Code = '1234A')

Then I can use this value as a control in a while loop to select the value that many times:

DECLARE @i INT = 0;
WHILE @i < @cdcnt
BEGIN
    SELECT DISTINCT Code
    FROM MyTable
    WHERE PlaceID = 1 AND Code = '1234A'
    SET @i = @i + 1
END

The problem is I get 6 separate selections, instead of one selection with 6 rows. How do I create MyResult shown above, instead of 6 separate selections?

Why I am doing this: I want to use a random number generator to pick a Code with the frequency at which it appears in aggregate for each PlaceID (there will be other Codes added, I'm still figuring out how to do just one). I am open to suggestions for different ways of doing this, but it's the only way I could figure out how. If there's a function already built to pick from a list according to a set frequencies, I'm all ears.

Below is an example using a join to a tally CTE. It would be better to create a materialized tally/numbers table to facilitate this sort of task going forward and improve performance too.

WITH
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,codes AS (SELECT Code, SUM(CodeCount) AS CodeCount FROM dbo.MyTable WHERE Code = '1234A' AND PlaceID = 1 GROUP BY Code)
SELECT Code
FROM codes
JOIN tally ON tally.num <= codes.CodeCount;


CPU Time Slower in SQL Server 2014 than in SQL Server 2008 We’re upgrading a virtualized database server from an old SQL Server 2008 R2 Standard (vmware) to a new shiny SQL Server 2014 Standard server (hyper-v).  We were doing some performance testing and to our surprise it seems that CPU times are worse in the new server system compared to the old, crackling one.
These are the systems:

Old Server:

Runs in Virtual Machine VMWare ESX 5.1
Windows Server 2008 R2 32 bits
SQL Server 2008 R2
Quad Core Xeon ES-2620 2Ghz
5600MB RAM

New Server:

Runs in Virtual Machine Hyper-V UEFI Release v1.0
Windows Server 2012 R2
SQL Server 2014
Quad Core Xeon ES-2640 2.6Ghz
32766 MB RAM

A little history of what we did:

Two different benchmark tests shows that CPU is about 80% faster, memory access is about 100% faster and IO disc access is about 1000% faster in the new server.
Testing queries are using multiple joins with non-clustered indexes.
In both servers the testing database is exactly the same.
In both servers energy power option is set as High Performance and also checked with CPU-Z that the processor is always at max speed.
In both servers “locked pages” are enabled.
In both servers the statistics are properly updated and query plans are the same when executing the test queries.
To make sure that the plan are always the same we’re executing the queries in SQL Server 2014 with compatibility mode 100 (SQL Server 2008), but as well without this compatibility-mode the outcome is the same.
Max memory options is set to default value in both servers (2147483647) and we have also checked the parallelism (max degree is set the same for both and also the cost)
The new server is dedicated (there is nothing else running there)

After all these checks we’re getting better time results for cached queries (caused only by CPU time) still in the old server.

Test query:

SELECT ROW_NUMBER() OVER(ORDER BY c.field1, c.field2) AS RowNumber, a.t1_t3ID, c.field1, c.field2, a.t1_t2ID, b.field1, a.BeginDate, a.EndDate, c.field3
FROM table1 a
       INNER JOIN table3 c ON a.t1_t3_ID = c.t3_PKID
       INNER JOIN table2 b ON a.t1_t2ID = b.t2_PKID
WHERE '20140101' BETWEEN a.BeginDate AND a.EndDate

Query Execution Plan (is the same for both servers, with the same percentages):

Query Execution Plan Here

The Results for CPU time on both servers:

Old server:

CPU time = 93 ms,  elapsed time = 65 ms.
CPU time = 94 ms,  elapsed time = 99 ms.
CPU time = 94 ms,  elapsed time = 70 ms.
CPU time = 93 ms,  elapsed time = 63 ms.
CPU time = 93 ms,  elapsed time = 67 ms.

New server:

CPU time = 125 ms,  elapsed time = 309 ms.
CPU time = 141 ms,  elapsed time = 304 ms.
CPU time = 139 ms,  elapsed time = 288 ms.
CPU time = 156 ms,  elapsed time = 277 ms.
CPU time = 142 ms,  elapsed time = 323 ms.

  Note: we have also checked that the CONVERT_IMPLICIT is not affecting
  performance in this case.

  What we observed is as well if we force to use only one processor-core
  in the old server CPU time keep roughly the same. If we do same in the
  old server the execution-plan is switching to table-scans and CPU time
  increase up to 800ms.

Somebody have an idea what still can be checked/tested or at least have an explanation on this observation? Or is it for sure the virtualization systems (vmware vs. hyper-v) which cause such a difference in CPU time for databases?

If you have not done so, I would highly recommend:

1.> Installing the right version of Integration Services for Hyper-V on the host and guest(like VMWare Tools). This alone can be a performance boost.

2.> Within Hyper V manager, disable compatibility mode on the processors.

3.> Consider using a SCSI controller for data volumes.

4.> Consider disk sizes that are fixed rather than expanding.

Should you implement any of these techniques, I'd be interested in knowing if any performance improvement was gained.


How do you change the storage parameters of a already made table (sql developer)? Summarize the problem
I have been given 6 tables that have all been created already and I was told to change the storage parameters of those tables. the issue I run into is that I can only change pctfree, pctused, initrans and max trans and not the others (initial, next, pctincrease and maxextents)
Provide details and any research
I have done a lot of research, yet some of them do not work with sql developer at all. Whilst the others do work but as stated only for the 4 storage parameters.
When appropriate, describe what you’ve tried
alter table CUSTOMERS
pctused 20 pctfree 80;

This works perfectly for those two, but I am unable to add the others. From what I found online, this was in fact the only thing that worked for me.
I appreciate all input!

The extent parameters can not be changed of an existing table, they are specified when a table is created. You can change them by reorganizing the table.
SQL> select INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE
     from user_tables where table_name = 'T1';

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
         65536     1048576  2147483645

SQL>  alter table t1 storage(initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1);
 alter table t1 storage(initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1)
                        *
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed

SQL> alter table t1 storage(next 2097152 maxextents 1073741822 pctincrease 1);
alter table t1 storage(next 2097152 maxextents 1073741822 pctincrease 1)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted

SQL> create table t2 storage (initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1)
     as select * from t1;

Table created.

SQL> select INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE
     from user_tables where table_name in ('T1', 'T2');

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
         65536     1048576  2147483645
       1048576     2097152  2147483645

Notice how the MAX_EXTENTS and PCT_INCREASE parameter did not change. They are relevant only for dictionary managed tablespaces, but that was forgotten ages ago, we use locally managed tablespaces.


Blocked Process Report SQL Server 31 hour duration I have an application (.NET EF 6 ) which is getting timeouts when submitting/reading data from the database. Whilst trying to diagnose the problem I used Blocked Process Reports from SQL server profile to see if I was getting any long blocks. I ran the trace and came across some peculiar very long blocks - 31 hour blocks. I want to know what these are and if I should be worried about them. I filtered the trace by Database name that I was concerned with.

example 1:

    <blocked-process-report>
 <blocked-process>
  <process id="process703b88" taskpriority="0" logused="0" waitresource="OBJECT: 33:485576768:0 " waittime="115168485" ownerId="64139099452" transactionname="PurgeAllEmptyExtents2" lasttranstarted="2015-06-13T04:52:31.830" XDES="0x6b1731270" lockMode="X" schedulerid="6" kpid="4996" status="background" spid="10" sbid="0" ecid="0" priority="0" trancount="0">
   <executionStack/>
   <inputbuf>
   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="444" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2015-06-14T12:51:46.637" lastbatchcompleted="2015-06-14T12:51:46.637" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="63627468968" currentdb="33" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack/>
   <inputbuf>
FETCH API_CURSOR0000000000043F19   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

Example 2:

<blocked-process-report>
 <blocked-process>
  <process id="process34a20f288" taskpriority="0" logused="0" waitresource="OBJECT: 33:485576768:0 " waittime="107460436" ownerId="64211456029" transactionname="implicit_transaction" lasttranstarted="2015-06-13T07:00:07.107" XDES="0x1dc183340" lockMode="IX" schedulerid="4" kpid="6712" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-13T07:01:00.090" lastbatchcompleted="2015-06-13T07:00:07.110" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="64211456029" currentdb="33" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
   <executionStack>
    <frame line="240" stmtstart="16612" stmtend="16774" sqlhandle="0x03002100f8128430bcc115016fa200000100000000000000"/>
    <frame line="1" sqlhandle="0x01002100f0e6ac0d90d6a500010000000000000000000000"/>
   </executionStack>
   <inputbuf>
exec sp_LogPurge &apos;2015-06-13 07:01:00&apos;;   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="suspended" waitresource="OBJECT: 33:485576768:0 " waittime="114659439" spid="78" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-06-13T05:01:00.883" lastbatchcompleted="2015-06-13T05:00:59.767" hostpid="5056" loginname="trend" isolationlevel="read uncommitted (1)" xactid="64144056143" currentdb="33" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
   <executionStack>
    <frame line="240" stmtstart="16612" stmtend="16774" sqlhandle="0x03002100f8128430bcc115016fa200000100000000000000"/>
    <frame line="1" sqlhandle="0x0100210009260608307d4565030000000000000000000000"/>
   </executionStack>
   <inputbuf>
exec sp_LogPurge &apos;2015-06-13 05:01:00&apos;;   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>

The first one seems to be FETCH API_CURSOR0000000000043F19 and the second one is some sort of log purge: exec sp_LogPurge &apos;2015-06-13 05:01:00&apos;;

What are these and should I be worired?

Thanks

Not a huge fan of reporting profiler for checking blocking, but to get proper analysis would suggest you to either:

1) Use Adam Mechanic's SP_WHOisActive which is indeed very simple way of analyzing what and where could the blocking be a scenario with detailing on database name, app name along with its process.

2) If possible you can use SqlDiag utility to analyse same.

Ultimately, you need to find out who is doing the blocking first – why is their transaction taking so long? If it’s due to inefficiencies in the query – can you rewrite it? If it’s due to inefficiencies in the plan – can you add an index? If it’s modifying a large amount of data – can you break it down into smaller chunks so that each set is locked for a shorter period of time? These are ALWAYS the thing to try first.

However, if you still would like to go with reports to analyse blocking i would suggest to read this article from Jonathan Kehayias on how to use the block process report in sql server 2005/8


Is storing archive logs on local disk and backups on vendor storage possible? I am using DB2 9.7.

My LOGARCHMETH1 is set to VENDOR library. Example:

LOGARCHMETH1=VENDOR:/usr/openv/netbackup/bin/nbdb2.sl64

Is it possible to call the following backup command:

db2 backup database EAIBDB2I online \
    load '/usr/openv/netbackup/bin/nbdb2.sl64' \
    without prompting

if I change LOGARCHMETH1 to be on local disk and not on VENDOR library? How should LOGARCHMETH1be specified for that, and do I need other settings?

Yes you should be able to have backups and archived logs go to different places (ie, backups could go to your VENDOR tool and archived logs to disk).

To send archived logs to disk you can do one of two things:

The first would be to set LOGARCHMETH1 to DISK:

db2 "update db cfg using LOGARCHMETH1 'DISK:/dbarch/dblogs'"

The second method would be to have two forms of archiving logs. You can leave LOGARCHMETH1 to your VENDOR and set up LOGARCHMETH2 to point to DISK.

db2 "update db cfg using LOGARCHMETH2 'DISK:/dbarch/dblogs'"

I've not played with two forms of log archiving before, but I know that it is available.

In our company we write our backups and archived logs to disk and then sometime after midnight we have IBM's Tivoli Storage Manager (TSM) come along and take a file system snapshot so it will have a copy of the backups and archived logs. So then we have a backup of our backups. We probably could have worked in LOGARCHMETH2 in our company, but so far this has been simplest for us.

Just giving you ideas of what you can do. :)


Service Credentials to use in AlwaysOn Availability Groups? When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials?

We are setting up a new SQL2014 deployment and will be using AlwaysOn. If I have 2 SQL servers (call them ServerA and ServerB) then can I use 2 active directory accounts called Domain\ServerA_SQLServerService and Domain\ServerB_SQLServerService (one on each server)?

Or do they need to use the same account so I should just create Domain\SQLServerService

The accounts would be in the same domain and can have access to both servers. Previously we've had the SQL Server Service account locked out which has obviously caused problems. What I'd like to do is have 2 accounts so that the likelihood of both being locked out is reduced.

Is there a requirement and/or what is Microsoft's best practice?

When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials?

No, SQL Server service accounts can be different. We have a 3 node cluster running AlwaysON and on all the servers SQL Server is running with a different account.
e.g. If you have one server in NY and other in LD, then

Active Directory Accounts to be created (Below will  be sql server service accounts)
NYDB1_sql
LDDB1_sql
Add both these accounts to the group DB1-SQL (below)
Active Directory Group to be created : DB1-SQL (Both the above accounts to be members of this group).

One important thing to note, from BOL :

If two server instances run as different accounts, the system administrator must use the CREATE LOGIN Transact-SQL statement to create a login for the startup service account of the remote instance in the syslogins table of the master database of each server instance.

Also, grant the CONNECT permission on the endpoint to the service accounts.

Previously we've had the SQL Server Service account locked out which has obviously caused problems.

You can use group managed service account - which are new in Windows Server 2012. The only requirement is you need at least one Windows 2012 domain controller, and use 2012 or 2012 R2 member servers.


How does one select the histogram / scatterplot / etc. Data Viewers in SSIS 2012 I have a data flow task in SSIS 2012 and have created a flat file source and an OLE DB connection destination.  I can select the data flow path between them as in earlier versions of SSIS and I can right click and select the Data Flow Path Editor.

The Data Viewer option is available as in past versions, but there is no add button and I cannot determine how to add histogram/ scatterplot /etc. data viewer.

All I can seem to configure is a sample of the data that actually moved across.

Discontinued Integration Services Functionality in SQL Server 2012

The options to configure a data viewer to display data in a histogram, scatter plot, or column chart have been discontinued in this release.

Data Viewer :

Previous versions of SSIS included four types of data viewer—the Grid, Histogram, Scatterplot, and Column Chart data viewers. Because the Grid data viewer is the most commonly used, the SSIS team removed the other types of data viewers from SSIS 2012.


Measure Performance for INSERT, UPDATE and DELETE commands on DB2 I have a DB2 database with a table for daily transactions, which is cleared to a history table at the end of the day, every day.

I insert rows to this table via java-JDBC INSERT commands and they usually take less than a second to complete but sometimes (I'm still not sure what is the pattern) it can take up to a minute to complete the INSERT command and since I don't have a timeout configured on the JDBC driver, it waits until the command is completed and then carries on.

These INSERT commands are executed thousands of times per day but this only happens on about 5 or 10 of them almos every day. The table has some indexes and a trigger attached on AFTER INSERT event.

Is there any tool or technique I can use to measure what is taking so long to execute on my INSERT command? I want to know if it is the indexes, the trigger or, for any reason, the query itself.

I guess it's a locking issue.

While such inserts take so long use admin view below. The column HLD_CURRENT_STMT_TEXT shows the blocker statement (what cause the insert took so long):

db2 select * from SYSIBMADM.MON_LOCKWAITS


Selecting only columns where sum(column) > 0 I have a table like this:

Date     Bob     Joe     Will
1/1/15    1       0        1
1/2/15    0       0        1
1/3/15    0       0        0
1/4/15    1       1        1

Preferred solution

With one statement, I want to return only the columns where SUM(Column) > 0

For example:

SELECT SUM(Bob) as Robert, SUM(Joe) as Joseph, SUM(Will) as William
  FROM table
 WHERE Date BETWEEN '1/1/15' and '1/3/15'

I want my result to look like this:

Robert     William
  1           2

In other words, since SUM(Joe) = 0 for my selected date range, I don't want the column Joseph to appear in the result set. What is the magic that would cause Joseph to not appear?

Alternate Solution

I can work with the data if it comes back slightly differently, so I have considered writing a table function to return a 2-column table like this:

Name        Value
Robert      1
William     2

My thought is that the function code would look similar to this:

--Declare table here
IF SELECT SUM(Bob) > 0
  INSERT INTO temp (Name, Value)
    ('Robert', SELECT SUM(Bob))

Lather, rinse, repeat for each of my columns. I know this isn't ideal since it would need maintenance should there ever be a new column that needs to considered, but there's other maintenance in that situation anyway, so I could handle it.

In Summary

Is there a way to create a query that would give me my preferred solution? If not, are my thoughts on my alternate solution reasonable?

This solution gives you your "Alternate Solution" answer.  The only way to achieve what you are looking for as a primary solution would be a dynamic pivot operation on the result set after you have unpivoted it.  This solution does require maintenance but is straightforward and no function is required.

Sample data:

DECLARE @Names TABLE
    (
      DateValue DATE NOT NULL
    , Bob INT NOT NULL
    , Joe INT NOT NULL
    , Will INT NOT NULL
    );

DECLARE @MinDate DATE = '1/1/2015';
DECLARE @MaxDate DATE = '1/3/2015';
DECLARE @MinValue INT = 1;

INSERT  INTO @Names
        ( DateValue, Bob, Joe, Will )
VALUES  ( '1/1/2015', 1, 0, 1 )
        , ( '1/2/2015', 0, 0, 1 )
        , ( '1/3/2015', 0, 0, 0 )
        , ( '1/4/2015', 1, 1, 1 );

Query:

WITH    CTE_Unpivot
          AS ( SELECT   DateValue
                      , ScoreName
                      , Scores
               FROM     ( SELECT    DateValue
                                    , Bob
                                    , Joe
                                    , Will
                          FROM      @Names
                        ) AS cp
               UNPIVOT  ( Scores FOR ScoreName IN ( Bob, Joe, Will ) ) AS up
             )
    SELECT  ScoreName
          , SUM(Scores) AS ScoreTotal
    FROM    CTE_Unpivot
    WHERE   DateValue BETWEEN @MinDate AND @MaxDate
    GROUP BY ScoreName
    HAVING  SUM(Scores) >= @MinValue;

Edit:
Ideally, you wouldn't have the table stored like that, it looks like it was copied from an Excel spreadsheet.  You would have a table with three columns, date, name and value, then query based on that.


How to monitor InnoDB Disk I/O using Server Status Variables? This question is extension of my previous question "How to monitor InnoDB Disk I/O Required for MySQL? "

As mention in my previous question i want to count total number of disk i/o per query, but while testing i got confused with some result ,

i am using below example ::

mysql> explain user;    ------->[user table from Website database]
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| username | varchar(50) | YES  | UNI | NULL    |                |
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| password | varchar(50) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

I have created a index on username.

My analysis is

before running any query system variable status was

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '26'

when i tried

insert into user (`username`,`password`) values ("xyz1","xyz1");

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '27' -------> (shows one write operation in Innodb_log )

when i tried

Select * from user where username ="xyz1";

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '27'

and when i tried

UPDATE user SET `username`="xyz1", `password`="hello"
WHERE `username`="devlad1";

'Created_tmp_disk_tables', '1'
'Created_tmp_files', '12'
'Created_tmp_tables', '8'
'Key_reads', '0'
'Key_writes', '0'
'Innodb_buffer_pool_reads', '425'
'Innodb_log_writes', '29'------>[shows two write operation to log]

My first question are

  1). I am calculating disk i/o correctly or i am missing something here?

  2). I have create index on 'username' so while doing insert or update index >    count should be modify but varible "Key_writes" keep showing me '0' ,what >    does it means? is index is caching some where ?

thanks in advance!!!!

Innodb_log_writes monitor only writes to the innodb transaction log. InnoDB also writes to the double write buffer, data (aka primary key) and secondary indexes on the tablespace, change buffer, undo space, ... Some of these can be buffered on memory, at least for some amount of time.

Additionally, MySQL may write to the mysql binary, general or slow log, or use certain temporary structures for pending transactions, binlog, tables for intermediate results, etc. if they do not fit in memory. Also, bear into mind that if a query involves a temporary table, it can show I/O activity in other engines, as it may create such tables in MyISAM format.

Depending on the configuration and change, a write to InnoDB may cause 0 writes to the transaction log (for example, if it has not yet been synced, or no page was modified) or several (at least one per page modified). Even if you only update 1 record, it may case the modification of several pages (e.g. because a blob is involved, or an index have to be updated). You can check the size of the writes to the transaction log by monitoring the InnoDB Log Sequence Number. Under a strict "flush log at transaction commit", the writes to the log are the only thing that is done to disk, all other changes are tried to be buffered on memory and are written at any future time.

While somehow inaccurate, InnoDB has a section in File I/O in its SHOW ENGINE INNODB STATUS command, for a more global view. As @Ela says, innotop is a good summary tool for monitoring InnoDB operations, including the former command.
Yes, index changes can be buffered, as any writes. Not only the index pages, but index updates are usually heavily buffered through the change buffer and merged later (more than normal updates, as they involve reading data that may not be on cache). Check the INSERT BUFFER AND ADAPTIVE HASH INDEX section on the previous INNODB STATUS command.

In a nutshell, InnoDB is a complex "set of buffers and asynchronous reads and writes", and there is never a guarantee that we can stop all I/O operations (some of them only kick when the server is idle). This is a good introduction material to InnoDB architecture and operations: https://www.percona.com/live/london-2013/sessions/innodb-architecture-and-performance-optimization


Unexpected results in query with rownum condition I have found old query in application that behaves unexpectally after Oracle update. I have found out that problematic part of this query is condition:

rownum >= 1

When I apply this condition, resultset contains 0 rows, otherwise it returns 1 row (which is same in older version of Oracle).

If I change condition to rownum = 1 it returns one row. It also returns one row for AND(rownum > 1 OR rownum = 1) and oddly for AND(rownum >= 1 OR rownum >= 1)

How is it possible?

EDIT, added SQL:

SELECT DISTINCT
    *
FROM
  NI_TABLE ni
LEFT JOIN NI_TO_BOPER nibo
ON
  ni.ni_id     = nibo.ni_id
AND ni.company = nibo.company
LEFT JOIN CHOPER co
ON
  nibo.operation_id = co.operation_id
AND nibo.company    = co.company
WHERE
  (
    ni.IDENTITY_NUMBER  = '123456789'
  OR co.IDENTITY_NUMBER = '123456789'
  )
AND NI.company   ='K'
AND rownum >= 1
AND 200 >= rownum
AND ni.NI_TYPE   ='PRIV'
ORDER BY
  ni.identityNo;

Note I haven't created this code, so I changed names for security reasons.

The pseudocolumn rownum is assigned AFTER all the result rows from the query are retrieved, and so the correct way using rownum to get say 10 rows, is to use <= as pointed out in the comments.  The rownum can never be greater than the number of rows returned.  Example:

SYS@ecs03> select table_name from dba_tables where rownum <= 10;

TABLE_NAME
--------------------------------
CCOL$
FILE$
CLU$
SEG$
ICOL$
OBJ$
TAB$
CON$
UNDO$
TS$

10 rows selected.

SYS@ecs03>

And better yet as Kevin pointed out, use FETCH FIRST *n* ROWS clause, as you can add additional parameters to get deterministic results.

  For consistent results, specify the order_by_clause to ensure a deterministic sort order.

That old code may have worked years ago, but not in the last several years. I got bit on an upgrade from Oracle 9i to 10c when we assumed that group by implied ORDER BY by the same columns in the group by clause.  Wrong!


Synchronous SQL Server Replication only for specified subscribers I would like to create database replication between multiple localisation. There will be one publisher and multiple subscribers. Is it possible to define that synchronisation is synchronous only for one subscriber and for the other is asynchronous operation. Is it possible to dynamically modify which subscriber will get data synchronously? Are there any external tools allowing to do that?

SQL Server supports Transactional, Snapshot and Merge replication. They are inherently asynchronous.

Mirroring on the other hand does support synchronous/asynchronous commit however you can only mirror one database to one other location.

SQL Server doesn't support what you are looking for. I'm not aware of any other tools which would enable this either.

The closest thing to what you are talking about is AlwaysOn, which I believe in SQL Server 2014/2016 supports multiple asynchronous/synchronous replicas.


PostgreSQL not using the index on queries that return large number of records I have a table called my_table which contains about 30M records. The table has an index set on column_a. However, I'm noticing that when number of returned queries are high, the index is not used.

Index not used:

explain (buffers, analyze) select * from my_table where column_a in (1);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on my_table  (cost=0.00..1306095.85 rows=7772384 width=648) (actual time=0.009..6231.312 rows=7720995 loops=1)
   Filter: (column_a = 1)
   Rows Removed by Filter: 7398657
   Buffers: shared hit=463612 read=653528
 Planning time: 0.840 ms
 Execution time: 7717.923 ms
(6 rows)

Index Used:

explain (buffers, analyze) select * from my_table where column_a in (8);
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on my_table  (cost=1378.58..233236.51 rows=73567 width=648) (actual time=14.258..67.795 rows=74756 loops=1)
   Recheck Cond: (column_a = 8)
   Heap Blocks: exact=36425
   Buffers: shared hit=36632
   ->  Bitmap Index Scan on my_table_column_a_idx  (cost=0.00..1360.19 rows=73567 width=0) (actual time=8.595..8.596 rows=74756 loops=1)
         Index Cond: (column_a = 8)
         Buffers: shared hit=207
 Planning time: 0.855 ms
 Execution time: 82.253 ms
(9 rows)

I have the table vacuumed:

select relallvisible, relpages, relallvisible/relpages as ratio from pg_class where relname='my_table';
 relallvisible | relpages | ratio
---------------+----------+-------
       1117140 |  1117140 |     1
(1 row)

That is just as it should be.

If you return a significant part of the table, the overhead of the bitmap index scan part is not worth paying, because you have to visit most of the heap blocks anyway.

So PostgreSQL just skips that part and goes to the heap scan directly. The result is a sequential scan.


PAGELOCK Conversion Deadlock From Update to Intent Exclusive A customer of ours is experiencing frequent deadlocks.  The deadlocks are mostly on the same UPDATE statement.  The deadlocks follow a pattern in which both SPIDs have acquired an update (U) lock on a page, and both try to up-convert the U page lock to an intent exclusive (IX) lock.  Sometimes there is only one page involved; sometimes several.

We captured a deadlock trace using the trace flag 1222.  The SQL Server log shows many, many deadlocks with the following pattern (in bottom-to-top order):

waiter id=processe0dc2088 mode=U requestType=convert
waiter id=process2f9db2478 mode=U requestType=convert
waiter-list
owner id=processe0dc2088 mode=IX
owner id=process2f9db2478 mode=IX
owner-list
pagelock fileid=1 pageid=5794 dbid=2 objectname=tempdb.dbo.Item_Package_Site_Costs_Work id=lock1b22de480 mode=IX associatedObjectId=72057594131775488
resource-list

Both processes are running the same UPDATE statement to set a flag on this tempdb table.  This tempdb table holds information that needs to persist between client calls until the client is done.  The table has a fairly long index that starts with a GUID representing a unique process ID.

I am having difficulty understanding and simulating this deadlock.  I have tried various amounts of records with simulated data.

My questions:

Why are these processes acquiring U locks and then converting to IX?  I would expect the DELETE to acquire IX locks to begin with.

How can I prevent the deadlock?

The statement causing the deadlock is below.  The process has just done a lookup of costs for a list of items at a single store.  It is trying to note that there was a cost found.

Note that there is a deprecated (NOLOCK) on an UPDATE statement.  Would this be a contributing factor?

UPDATE tempdb..Item_Package_Site_Costs_Work
SET ItemPkgSiteCost_VINCostFound = 1,
    ItemPkgSiteCost_VendCost_Key = SiteCosts_VendCost_Key
FROM tempdb..Item_Package_Site_Costs_Work (NOLOCK)
    INNER JOIN #SiteCosts
        ON ItemPkgSiteCost_GUID = @ProcGUID
        AND SiteCosts_Item_Type = 0 -- Standard
        AND ItemPkgSiteCost_Site_Key = SiteCosts_Input_Site_Key
        AND ItemPkgSiteCost_Item_Key = SiteCosts_Item_Key
        AND ItemPkgSiteCost_ItemPkg_Key = SiteCosts_Input_Sel_ItemPkg_Key
        AND ItemPkgSiteCost_VendItem_Key = SiteCosts_VendItem_Key
        AND ISNULL(ItemPkgSiteCost_Qty_Recv, 1) = SiteCosts_Input_Qty_Recv

The customer's server @@version is:

  Microsoft SQL Server 2005 - 9.00.4035.00 (X64)   Nov 24 2008 16:17:31
  Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition
  (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

So far I have not been able to capture the query plan used at the time of the deadlock, and the normal ways I try to retrieve the query plan are not returning anything(sys.dm_exec_query_plan and sys.dm_exec_text_query_plan both return NULL).

UPDATE 2013-08-29

The customer installed SQL Server 2005 SP 4, but they are still seeing this deadlock.  I will pursue removing the deprecated (NOLOCK) on the tables being modified and see if this fixes the deadlocks.

First of all, the process is holding IX and want to convert to U. This is expected with the hint in the query.

Both queries will take the IX lock during the select and then convert to U when the change (if any) needs to happen.

This can be solved by adding: WITH (XLOCK) as a hint to the temporary table.


Merging tables with a 1 to 1 relationship in a database Is it a good idea to merge two tables that have a 1..1 relationship together?

I have inherited a database with too many tables linked together in a 1..1 relationship. I'd like to evaluate the benefits of merging these tables where possible to have more tidy model with higher query performance.

For example table t1 has 150 records and table t2 has 150 records, they each contain 5 columns and these two tables have a 1..1 relationship. Would it be a good idea to merge them into one table, say t3 which contains all the columns from t1 and t2?

Also does it make any difference if these tables are reference tables (less than 200 records) or an operational tables?

Any idea is appreciated.

If they are small reference tables then I would be more inclined to merge them than if they are not. If reference tables, then they are likely separated due to over the top normalisation and merging them makes séance from query performance perspective.

If they were large fact tables I would be less inclined to wade in before checking ramifications, as it could be that they were separated onto separate file groups with files on different spindles for parallel operations. If this is the case however, I would still be inclined to merge them, and consider table partitioning.

Either way, be sure to thoroughly check for Database and client application dependencies before Changing the table structure.


Multi master replication with MySQL and Django I am working on scaling out a webapp and providing some database redundancy for protection against failures and to keep the servers up when updates are needed. The app is still in development, so I have chosen a simple multi-master redundancy with two separate database servers to try and achieve this. Each server will have the Django code and host its own database, and the databases should be as closely mirrored as possible (updated within a few seconds).

I am trying to figure out how to set up the multi-master (master-master) replication between databases with Django and MySQL. There is a lot of documentation about setting it up with MySQL only (using various configurations), but I cannot find any for making this work from the Django side of things.

From what I understand, I need to approach this by adding two database entries in the Django settings (one for each master) and then write a database router that will specify which database to read from and which to write from. In this scenario, both databases should accept both reads and writes, and writes/updates should be mirrored over to the other database. The logic in the router could simply use a round-robin technique to decide which database to use. From there on, further configuration to set up the actual replication should be done through MySQL configuration.

Does this approach sound correct, and does anyone have any experience with getting this to work?

Master-Master has potential problems.  If you have AUTO_INCREMENTs, they need to be configured correctly.  If there is a chance of "duplicate key" for other reasons, that will stop replication.

Hence, I recommend not writing to both Masters.  Instead write to only on, keeping the other one as a 'hot backup'.

Consider Galera.


Sybase - How to get logsegments off my data devices? I'm running Sybase ASE 15.7 and coming up to speed to clean up a database server installed by a non-DBA.

I have a database that has the following device configuration:

I want to have data1 and tlogs1 devices hosting only Data and Log respectively so that I don't encounter any anomalies in the event of a database restore due to a device failure.

How can I correct this configuration?

Note: The remove button is always grayed out.

I was unable to fix the database in place, but I did manage to solve this misconfiguration by:

Creating a new database with my optimal device layout
Migrating the schema via DDL generation scripts using ddlgen.bat
Using bcp to migrate the table data

Note: You could use Sybase Central's DDL generator, but it doesn't include table's indexes for some reason.

When done, I have well documented database schema scripts to place in source control.


In Postgres, what should be the autovacuum strategy for big tables (hundreds of millions rows) that get added/removed thousands of rows every day? I have quite a big table with a lot of daily traffic for reads, inserts and deletions. Currently, it has 392 million of live tuples and 27 million of dead ones. Vacuum settings (autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor, etc) are set to the defaults.
Occasionally I do get some performance issues that make queries last >2 minutes when they usually take couple of seconds.
At first, I'd have thought about lowering the vacuum scale factor from the current 0.2 to 0.05 or even 0.01. But, because the autovacuum runs already several times a day (and they may run for a while, >1 hour), I'm not sure if lowering the scale factor would make it worse as it would run even more often, despite meaning that less number of dead tuples the autovacuum will work on.

Your table seems fine at first glance. If autovacuum gets done and you have less than 30% of dead tuples, I see no need to worry.
You might want to use the pgstattuple extension to check if the table has a lot of free space; if yes, that would be an indication to make autovacuum faster.
The keyword here is faster: you'd have to lower autovacuum_vacuum_cost_delay or increase autovacuum_vacuum_cost_limit for that. Making autovacuum run more often won't do any good.
But if you are already experiencing performance problems with autovacuum being as fast as it currently is, I'd leave the settings alone.


How to model medium.com paragraph comment If you have used medium.com, I am interested to know more about how to model readers' paragraph comments in database.

Specifically, how connections between these comments and paragraphs are modeled?

My initial thought is (1) using the order of paragraphs in article, e.g. paragraph 1, 2... It won't work if the author deletes/adds/moves paragraphs, which messes up the order.

Then I think about (2) assigning id to paragraphs, e.g. SHA hash the paragraph as its unique id. But it won't work if the author edits the paragraph.

Could anyone help me?

Btw, could the solution extends to sentences/phrases comments as well?

You can insert a comment marker in the text itself. This marker is not displayed. As text is being changed, the marker stay embedded in the text itself and moves around as text is being edited. If the paragraph is removed then the comment can be orphaned and garbage collected later. Of course, this has nothing to do with DBs, is just pure text processing.

If you want a DB centric approach then the paragraphs are assigned primary keys during creation and the comments reference this primary key. As PK, of course, they never change. Order of paragraphs within text can be persisted as a relation previous-current-next (implemented as a separate table, or as next_id and prev_id fields. You can also throw in versioning (history). I'm not a editable text storage expert so I don't know what is the state of the art.

Your problem, as you state it, is not a problem of text processing but a problem arrise from the use of a natural primary key (paragraph position or paragraph hash). Of course, we all know that natural keys are bad because they change. In your example they change very frequently. Using surrogate keys solve the problem of PK volatility. the rest is just a modeling exercise on storing the relations (order of paragraphs in text, paragraph changes as text evolves etc). I'm letting as an exercise to you the problem of paragraph split during edit (which shard inherits which comments) and the problem of paragraph merge.


Select catalog role - Oracle Does select_catalog_role if granted to a schema provide access only to dba tables and v$ views only and not to other schemas created for any application?

The reason i'm asking this is I need to grant select_catalog_role to some administrators but not to the application schemas where in sensitive information resides.

It only allows access to data dictionary views, so the only security implication is that the users(s) would be able to see which objects existed in the other schemas. They would not, however, be able to actually view the data in the other schemas.

I said there was only one security implication, but another is that they would be able to view the source of any stored procedures/functions in all schemas, by viewing DBA_SOURCE etc.


I need to find what tables have been changed in the last period is it possible to get the xmin of all tables in a schema to find what has been changed or created in the last period in postgres, I have been trying with little success

with tnames as (select tablename from pg_catalog.pg_tables
                 where schemaname = 'public' limit 1)
select max (xmin::text::bigint)
      ,tnames.tablename
from (select tablename from tnames)  as foo

You will need dynamic SQL for that. For example a table function, that loops through the table names in the schema, build each query, execute them and return their results.

CREATE
 OR REPLACE FUNCTION xmins
                     (_schemaname name DEFAULT 'public')
                     RETURNS TABLE (tablename name,
                                    maxxmin xid)
AS
$$
DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT t.tablename
                   FROM pg_catalog.pg_tables t
                   WHERE t.schemaname = _schemaname) LOOP
    RETURN QUERY EXECUTE '
SELECT ''' || r.tablename || '''::name,
       max(xmin::text::integer)::text::xid
       FROM ' || r.tablename || ';';
  END LOOP;

  RETURN;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM xmins();


MySQL: update instead of delete if foreign key constraint? I have a bit of a strange question. I know about insert on duplicate key update. My question is, is there something similar for deletes that fail because of foreign key constraints?

For example:

delete from table1 where value='something';

But, table2 has a foreign key that depends on the the value I want to delete in table1, so the delete fails. I'd like to do something like this:

delete from table1 where value='something' on foreign key fail update some_other_value='something else';

I know that looks really weird, but I have a good reason for doing it (without getting into details, it has to do with versioning historical data that can't be destroyed in the event that a value is referenced elsewhere.) I can figure out how to do this with more than one query, of course, but I'd like to do it in a single query if I can. I'm pretty sure it's not possible, but I'd like to ask before giving up :)

Thanks!

You can't directly do it since there's no way (that I can think of) to alternately UPDATE or DELETE from the same query... generally a query can only do one type of CRUD operation (ON DUPLICATE KEY UPDATE being an exception to this).

You could, however, make it "feel like" a single query, with a stored procedure, where you'd set up a CONTINUE HANDLER for the foreign key error you anticipate.  The handler will trap the error and can be used to set a variable which you can test to see whether you hit the error and therefore need to also try the update query.

DELIMITER $$

DROP PROCEDURE IF EXISTS delete_or_update $$
CREATE PROCEDURE delete_or_update (IN input_value INT)
BEGIN

  DECLARE hit_fk_error TINYINT DEFAULT 0;
  DECLARE deleted_row_count INT DEFAULT NULL;
  DECLARE updated_row_count INT DEFAULT NULL;

  -- 1451 is probably the error code that will be generated
  -- Cannot delete or update a parent row: a foreign key constraint fails (%s)
  -- otherwise, you may need to substitute the correct error code in the next line

  DECLARE CONTINUE HANDLER FOR 1451 SET hit_fk_error = TRUE;

  DELETE FROM parent_table WHERE tested_column = input_value; /* arg to the stored proc */
  SET deleted_row_count = ROW_COUNT();

  IF (hit_fk_error = TRUE) THEN
    UPDATE parent_table SET some_other_value = 'something_else'
     WHERE tested_column = input_value;
    SET updated_row_count = ROW_COUNT();
  END If;

  SELECT deleted_row_count, updated_row_count, hit_fk_error;

END $$

DELIMITER ;

This will try the delete; if it hits a foreign key error it will then try the update (and the foreign key error will be suppressed).  The procedure will return a record set with the number of rows affected by each query and whether the foreign key error occurred.  If the "input_value" isn't an INT or the WHERE clause is more complex, you'll need to modify the structure, accordingly.

The potential problem I see here is that if the where clause is too broad, and matches some rows that would cause a foreign key error and other rows that wouldn't, then the query will not treat the rows individually.  That could be accomplished by a more sophisticated procedure that used a cursor to find the rows in the where clause and tried to delete or update them individually by primary key after identifying them.   That approach would be less efficient but more precise.


Improve sqlite mapping query I have an SQLite DB that has couples of tables. one of them looks like this:
(id integer primary key, location integer, name text, desc text, version integer)
select id,other_colum from table where location in (?,?,?) and name=? and desc=? and version=?

The order is not important and there are more columns that not relevant.
I have a query that mapping data from an external system to inner id. The mapping is done with all the above fields. I’m using an "equal" operator with all fields except location which I have a list of possible values (so I am using operator "in"). The list is not constant in values and length.
I receive data from the external system at a rate of a minute so latency is important.
This query runs 550 times on average each time (throughout range between 500 to 600 depends on the hour).
I started without any index and the query took 400 mili on average, so I added index only on location and it drops to 30 mili. I tried to add columns to that index but it didn’t help.
I created the index using the following query
create index mapping_index on table(location)

I tried to add more columns to the index to improve performance but nothing helped.
The data is mostly read-only, with few insert (20 per day) and few updates (50 per day including soft delete). the updates never touch the columns used for mapping.
There are almost 25000 rows in the table. Also always should be one and exactly one row that matches the mapping but I need to be able to identify errors such as no row or more than one row.
This is the heaviest part of my system and almost everything operation goes through this and I need to improve this.
I’m open to new technologies but prefer to stay with SQLite for simplicity
Edit:
The complete relevant DB schema:
CREATE TABLE t(id INTEGER PRIMARY KEY, otherId INTEGER, location INTEGER, name TEXT, desc TEXT, version INTEGER, createDate INTEGER, removeDate INTEGER);
CREATE INDEX mapping_index ON t(location,name,desc,version);

Result of EXPLAIN QUERY PLAN:
0|0|0|SEARCH TABLE t USING INDEX mapping_index (location=? AND name=? AND desc=? AND version=?)
0|0|0|EXECUTE LIST SUBQUERY 1

I just understand that I wrote something confusing. The whole transaction take something like 30 millisecond. the query itself take between 1 to 2 millisecond (with strange spikes of 100 millisecond). The query run in average 15 times per transaction (500 each time).

To find matching rows, the database must first search in the index. If the WHERE clause also uses columns not in the index, the database also has to read candidate rows from the actual table to be able to check those other columns.
Therefore, you would get the largest further speedup by adding all columns from the WHERE clause to the index.
You can also create a covering index by adding all columns from the SELECT clause to the index. Then no rows have to be read from the table. This might halve the query time.
Adding columns makes the index larger, which might slow down updates, which is probably not a problem for you.


Extract return type of functions from metadata I want to identify all my stored functions which return REFCURSOR.

Is there a way to do that?

I can't find this information anywhere.

I searched in dba_functions,dba_objects,dba_arguments...

What is the (best) way to achieve it?

Based on a quick and simple test:

ALL_ARGUMENTS

select * from
all_arguments
where
position = 0
and data_level = 0
and argument_name is null
and data_type = 'REF CURSOR';

position 0 is the first argument of a program unit. If it does not have a name/level (data_level = 0 and argument_name is null), then it is the return argument of the function, and you are looking for sys_refcursor type, which is displayed as REF CURSOR in this view.


What does it mean to have many rows for a SPID in sp_who2 I run sp_who2 active on box box and get multiple rows per single SPID.  For example, see below.  Does this mean that SQL Server has broken the query into 23 parallel sub-queries?  If that's the case, why is it ignoring MaxDegreeOfParallelism setting of 8?  Or is this something else?

why is it ignoring MaxDegreeOfParallelism setting of 8?

That is because MAXDOP is per operator and not per execution plan. Have your MAXDOP setting to a good value.

  the MAXDOP setting is used to limit the number of threads per operation in the execution plan and does not limit the number of threads used to execute the query. So it is normal to see  threads per SPID in sysprocesses greater than MAXDOP setting.

Also see How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s)

As a side note: use sp_whoIsActive - there are many options and uses DMVs

-- sp_who2 is legacy, uses old deprecated tables and was written during ice age 1995/11/03 10:16 :-)


SELECT  LEFT(startTime, 13) AS 'start of Hour',
        COUNT(*)  AS 'Total Jobs',
        SUM(TO_SECONDS(endTime) - TO_SECONDS(startTime) <= 300) AS '0to5m',
        SUM(TO_SECONDS(endTime) - TO_SECONDS(startTime) BETWEEN 301 AND 600) AS '5to10m',
        ...
    FROM tbl
    GROUP BY LEFT(startTime, 13)

Optionally have WHERE jobID = 12.

Problems:

Does the startTime determine which hour bucket it is in?
If you want a row of zeros for missing hours, then you need a table with all hours and a LEFT JOIN from it.


Performance impact of adding a foreign key to a 1M rows table I have an ecommerce database running MariaDB + InnoDB, which has a table to store the transactions (T) and another which stores quotes (Q).

I'd like to make a connection between T and Q by adding a foreign key on T referencing Q.

Considering that Q has over 1 million rows (constantly growing) and T about 100k, do you think that adding a foreign key in table T to Q would cause a significant performance impact during daily operation?
The alternative would be to add a simple INT column without a foreign key. Or anything else, I'm open to suggestions :)

For the details :

T has about 20 rows, mainly (SHORT) INTEGERs, some other foreign keys and a couple DateTime fields
Q has 2 SHORTINT fields and a couple VARCHAR fields (255b each).

Thanks in advance for your insights.

You are far better off maintaining a foreign key (FK) constraint if your data warrants it.

MySQL will automatically create an index - the FK must reference a UNIQUE KEY (obviously, can be the PRIMARY KEY (PK) - from here).

See here for a host of reasons to as to why applying data constraints in the database is a VERY good idea. The index on the T table will increase lookup speed at the price of a small hit for inserts.

Believe me, you will save yourself an immense amount of grief by making use of the database capabilities rather than trying to do this yourself.


Difference between OLTP and Inmon Data Warehouse What is the general difference between OLTP and Inmon-style Datawarehouse? It seems like they are the same. OLTP just copies all the tables into a Read-Only Datawarehouse DB.

I know what a Kimball datawarehouse is (denormalized, wide with surrogate identifier).
Inmon is more normalized.

The difference is more to the subject of the database than anything else. By the way, Inmon approach focus on normalizing as much as possible to make the ETL process easier and less error-prone; but non-normalized data are acceptable on data marts, even to Inmon.

OLTP: it is a full normalized database (commonly on 3NF) focused on a specific application. Usually, the data changes are small and happen on the row level.

Inmon DW: this is the central information database for the whole organization. The modeling is focused on business needs and this database centralize all the information from every system in your organization. IE: you can have Customer information in many of your systems, where each system brings you a complementary information. Following the Inmon approach, you have to have only one Customer entity on the DW, and this entity will have all the information from every system, in a conformed way (so it is not only copying tables, you should efficiently relate data from different systems inside your DW). Comparing with the Kimball approach, where classifications (hierarchies) can be simple columns in a dimension table, Inmon would create separate tables to "normalize" this info and make the ETL process easier.


mySQL data table seems to stuck at 16777215 rows I am creating a MySQL table and loading data from a local file.

CREATE TABLE `patent` (

  `seq_number` mediumint(9) unsigned NOT NULL auto_increment,
  `seq_id` varchar(24) default NULL,
  `date` date default NULL,
  `patent_number` varchar(16) default NULL,
  `pat_seq_number` smallint(6) unsigned default '0',
  `organism` text,
  `assignee` text,
  `seq_length` int(8) default '0',
  `seq` text,
  PRIMARY KEY  (`seq_number`),
  Index `seq_id` (`seq_id`),
  Index `patent_number` (`patent_number`),
  Index `pat_seq_number` (`pat_seq_number`),
  Index `seq_length` (`seq_length`)

) Engine = MyISAM MAX_ROWS=536870912;

load data local infile '/data/p_table.txt' into table  patent ignore 0 lines
(seq_id, date, patent_number, pat_seq_number, organism, assignee, seq, seq_length);

It seems that I am stuck at the number of rows:

mysql> select count(*) from patent;

+----------+
| count(*) |
+----------+
| 16777215 |
+----------+

1 row in set (0.00 sec)

Although my txt data table has about 20 million lines (records).

I tried Engine=InnoDB

I also tried Engine = MyISAM MAX_ROWS=200000000 (200 million)

mysql> show table status like 'patent'\G

*************************** 1. row ***************************

           Name: patent

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 16777215

 Avg_row_length: 244

    Data_length: 4098768552

Max_data_length: 281474976710655

   Index_length: 715227136

      Data_free: 0

 Auto_increment: 16777216

    Create_time: 2013-04-14 12:46:10

    Update_time: 2013-04-14 12:50:43

     Check_time: 2013-04-14 12:54:08

      Collation: latin1_swedish_ci

       Checksum: NULL

 Create_options: max_rows=536870912
        Comment:

1 row in set (0.00 sec)

The manuals and online advices all say I should be able to create large tables.

her is my uanme linux output.

Linux ... 2.6.32-279.el6.x86_64 #1 SMP Wed Jun 13 18:24:36 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

Here is the verison for mySQL:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 318
Server version: 5.1.67 Source distribution

How can I create a larger table to increase the number of rows to larger than 2**24=1677215 rows?

The maximum value for your auto_increment 'seq_number' field is 16777215.  You should make this field use an int or a bigint if you need more that 16,777,215 records.

See the MySQL Documentation for info about the mediumint(9) format.

https://dev.mysql.com/doc/refman/5.0/en/integer-types.html


Cannot remove orphaned InnoDB Temp Table - missing .frm file For the second time, I have encountered an issue, where our MySQL server (MariaDB v10.1) suddenly threw an error - Could not remove temporary table: './database_name/#sql-4593_791', error: 120.

Following the guids like https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/ does not work, I still get an error that the table is not known (ERROR 1051 (42S02): Unknown table 'database_name.#mysql50#sql-4593_1e9')

What makes this issue different from other questions on the removal of orphaned InnoDB temp tables is that the database server seems to have deleted the .frm file, both, from disk and from memory (running lsof | grep sql-4593_1e9 shows only the .ibd file open)

Can that be a source of the problem? If so, is there any way to recreate the .frm file of a virtually inaccessible table of unknown structure?

The only mention of it I can get in the database itself is by running:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

Which outputs the following:

+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+
| TABLE_ID | NAME                        | FLAG | N_COLS | SPACE   | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |
+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+
|  1576128 | database_name/#sql-4593_1e9 |    1 |    118 | 1576114 | Antelope    | Compact    |             0 |
|  1576129 | database_name/#sql-4593_78d |    1 |    118 | 1576115 | Antelope    | Compact    |             0 |
|  1576130 | database_name/#sql-4593_791 |    1 |    118 | 1576116 | Antelope    | Compact    |             0 |
+----------+-----------------------------+------+--------+---------+-------------+------------+---------------+

I do not feel comfortable removing the database files from disk manually, as I am not entirely sure it wouldn't lead to issues down the road. Even if the chances of tablespace id collision are near zero.

You have a disconnect between what you have on disk and what you have on the index internal data dictionary. MySQL 8.0 has improved how the data dictionary is handled, but up to 10.4 mariadb has not implemented it.

You cannot just drop it, because it won't be found on disk, but innodb still see it. For older versions of MySQL and MariaDB you will find several guides online on how to synchronize the data dictionary and the disk. However, this is the way I found more effective and secure:

Create a temporary database and move all "good" tables, views, etc. from the original table to the new one (this is very fast as it only is a rename table, as long as the new database is on the same partition)
Drop the now empty database (so the innodb index will drop all its remaining contents. Drop content on disk if necessary too.
Recreate the old database, now completely empty in the InnoDB internal dictionary and data
Move back the tables through rename

I have used this several times and I think it is the easiest, fastest and more secure way to synchronize the partially deleted tables.

Important, #sql-4593_1e9 is the format for temporary tables, please don't drop those unless you think they are really leftovers- there could be transactions ongoing that need those internally for queries. In other words, I would not touch this unless they appear on a new server start without any connections.


Can't figure out how to update a column in one table based on multiple boolean values in another table in postgresql What I want to happen is boolean milestone.milestonecompleted will turn true only if ALL of the linked boolean task.taskcompleted values are True
I've looked around and come up with this.
Any help would be great :)

UPDATE milestone
SET milestonecompleted =
    CASE task.taskcompleted WHEN false then false ELSE true END --CASE
FROM task, milestonedependency
WHERE milestone.milestoneid = milestonedependency.milestoneid
      AND task.taskid = milestonedependency.taskid
      AND milestone.milestoneid = 1

If a milestone can have multiple tasks (which is highly likely), then your statement will only update the taskcompleted for the "last" task processed in the query (the "last" is undefined here though).

If you want to check all tasks for a milestone to be completed you need consider all taskcompleted combined with an AND condition. This can be done using the boolean aggregate function bool_and().

So get evaluate the "all completed", you would need something like this:

select md.milestoneid,
       bool_and(t.taskcompleted) as all_completed
FROM task t
  JOIN milestonedependency md ON t.taskid = md.taskid
GROUP by m.milestoneid

This will return a flag for each milestoneid if all tasks have been completed. null values will be ignored (as with all aggregate functions).

To use this in an update statement, you can use something like this:

UPDATE milestone
   SET milestonecompleted = tmp.all_completed
FROM (
  select md.milestoneid,
         bool_and(t.taskcompleted) as all_completed
  FROM task t
    JOIN milestonedependency md ON t.taskid = md.taskid
  GROUP by md.milestoneid
) tmp
WHERE tmp.milestoneid = milestone.milestoneid
  AND milestone.milestoneid = 1;

You can make this more efficient by pushing the condition on the milestoneid into the derived table so that only rows with that milestoneid are aggregated:

UPDATE milestone
   SET milestonecompleted = tmp.all_completed
FROM (
  select md.milestoneid,
         bool_and(t.taskcompleted) as all_completed
  FROM task t
    JOIN milestonedependency md ON t.taskid = md.taskid
  GROUP by md.milestoneid
  WHERE md.milestoneid = 1
) tmp
WHERE tmp.milestoneid = milestone.milestoneid;


What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES? In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app purpose or something that I'm missing?

mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

That column exists for compatibility with other databases.

Interestingly, here is information_schema.tables in PostgreSQL

postgres=# \d information_schema.tables
                       View "information_schema.tables"
            Column            |               Type                | Modifiers
------------------------------+-----------------------------------+-----------
 table_catalog                | information_schema.sql_identifier |
 table_schema                 | information_schema.sql_identifier |
 table_name                   | information_schema.sql_identifier |
 table_type                   | information_schema.character_data |
 self_referencing_column_name | information_schema.sql_identifier |
 reference_generation         | information_schema.character_data |
 user_defined_type_catalog    | information_schema.sql_identifier |
 user_defined_type_schema     | information_schema.sql_identifier |
 user_defined_type_name       | information_schema.sql_identifier |
 is_insertable_into           | information_schema.character_data |
 is_typed                     | information_schema.character_data |
 commit_action                | information_schema.character_data |
View definition:
 SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'::text
            WHEN c.relkind = 'r'::"char" THEN 'BASE TABLE'::text
            WHEN c.relkind = 'v'::"char" THEN 'VIEW'::text
            ELSE NULL::text
        END::information_schema.character_data AS table_type, NULL::character varying::information_schema.sql_identifier AS self_referencing_column_name, NULL::character varying::information_schema.character_data AS reference_generation, NULL::character varying::information_schema.sql_identifier AS user_defined_type_catalog, NULL::character varying::information_schema.sql_identifier AS user_defined_type_schema, NULL::character varying::information_schema.sql_identifier AS user_defined_type_name,
        CASE
            WHEN c.relkind = 'r'::"char" OR c.relkind = 'v'::"char" AND (EXISTS ( SELECT 1
               FROM pg_rewrite
              WHERE pg_rewrite.ev_class = c.oid AND pg_rewrite.ev_type = '3'::"char" AND pg_rewrite.is_instead)) THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_insertable_into, 'NO'::character varying::information_schema.character_data AS is_typed,
        CASE
            WHEN nc.oid = pg_my_temp_schema() THEN 'PRESERVE'::text
            ELSE NULL::text
        END::information_schema.character_data AS commit_action
   FROM pg_namespace nc, pg_class c
  WHERE c.relnamespace = nc.oid AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));

In PostgreSQL, the table_catalog field actually means something. In fact, it is part of the SQL-92 standard. The catalog of a database groups related metadata for a database. See, in PostgreSQL, the information_schema is only for the database you are connected to. In MySQL, this concept is blurred and the information_schema contains all metadata for all databases, especially if you have ALL PRIVILEGES on *.* as with root@localhost.

It makes sense that it is there in MySQL but blank. The information_schema in MySQL will collapse for you if your DB user has ALL PRIVILEGES on db.* instead of *.*. In PostgreSQL, the collapse is automatic when you connect to a database schema. So, the table_catalog column is just there for MySQL to be SQL-92 compatible and nothing more.


SQL Server Full Text Search - .rtf files incorrectly indexed by rtf tags I've set up a full-text index on my SQL Server DB on a varbinary(max) column.  I have a type column specified, which contains the extension of the file, such a ".doc", ".pdf" etc.

However, I have noticed that when any .rtf files are indexed, SQL is including all of the meta information in the file (such as the RTF tag "listoverridecount0").

This is bloating the indexes quite a bit, and also means that searches will match on these tags (i.e. I can search on "listoverridecount0" and get EVERY .rtf returned).

Is there any reason that the iFilter for .rtf would not be removing the RTF Tags?

When I run this:

SELECT * FROM sys.fulltext_document_types WHERE document_type = '.rtf';

I get this:

document_type  .rtf
class_id       C7310720-AC80-11D1-8DF3-00C04FB6EF4F
path       c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\msfte.dll
version        12.0.6828.0
manufacturer   Microsoft Corporation

I have filed a Microsoft Connect bug as I don't seem to be able to find any workaround.  Presumably this is either a) Error with the RTF iFilter not removing tags, or b) Full Text index issue.

My SQL Server version is:

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64)
Oct 25 2013 19:04:40
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)

I can reproduce this but I think you have a few options:

not worry too much about full-text index bloat.  Not many people are going to be searching for 'rtf1', 'pard' or 'wmetafile0' and I don't think it will affect your performance that much.  You might get the odd clash if someone is searching for 'red' or 'blue' but I'd say this is fairly low risk.
Clean the string up eg with RegEx I copied from here.  Seemed to make a good go of it.  Store it in a separate table with the stream_id ( which is the unique id for the document ) and full-text index that table instead:

-- Convert rtf to plain text
SELECT
    CAST( file_stream AS VARCHAR(MAX) ) original
    , MDS.mdq.RegExReplace(
        CAST( file_stream AS VARCHAR(MAX) ),
        '\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?', '', 1 )
FROM dbo.Documents

Similar approach with a CLR function I adapted from here:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Linq;  // for TakeWhile

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlChars rtfToText( SqlChars inputRtf )
    {
        // RTF function lifted from here and adapted for SQL CLR:
        // https://stackoverflow.com/questions/23277178/richtextbox-throws-outofmemory-on-azure

        bool slash = false; //indicates if backslash followed by the space
        bool figure_opened = false; //indicates if opening figure brace followed by the space
        bool figure_closed = false; //indicates if closing brace followed by the space
        bool first_space = false; //the else spaces are in plain text and must be included to the result

        if (inputRtf.Length < 4) return new SqlChars ( string.Empty );

        int i = 0;
        i = inputRtf.ToString().IndexOf("\\pard");
        if (i < 1) return new SqlChars(string.Empty);

        var builder = new StringBuilder();
        for (int j = i; j < inputRtf.Length - 1; j++)
        {
            char ch = inputRtf[j];
            char nextCh = inputRtf[j + 1];

            if (ch == '\\' && nextCh == 'p') // appends \n if \pard, except for first
            {
                if (j > i && j < inputRtf.Length - 4)
                {
                    string fiveChars = inputRtf.ToString().Substring(j, 5);
                    if (fiveChars.Equals("\\pard"))
                    {
                        builder.Append("\n");
                    }
                }
            }

            if (ch == '\\' && nextCh == 'u') // to deal correctly with special characters
            {
                string fourChars = inputRtf.ToString().Substring(j + 2, 4);
                string digits = new string(fourChars.TakeWhile(char.IsDigit).ToArray());
                char specialChar = (char)int.Parse(digits);
                builder.Append(specialChar);
                j += digits.Length + 5;
                continue;
            }

            if (ch == '\\' && nextCh == '{') // if the text contains symbol '{'
            {
                slash = false;
                figure_opened = false;
                figure_closed = false;
                first_space = false;
                builder.Append('{');
                j++;
                continue;
            }
            else if (ch == '\\' && nextCh == '}') // if the text contains symbol '}'
            {
                slash = false;
                figure_opened = false;
                figure_closed = false;
                first_space = false;
                builder.Append('}');
                j++;
                continue;
            }
            else if (ch == '\\' && nextCh == '\\') // if the text contains symbol '\'
            {
                slash = false;
                figure_opened = false;
                figure_closed = false;
                first_space = false;
                builder.Append('\\');
                j++;
                continue;
            }
            else if (ch == '\\') // we are looking at the backslash
            {
                first_space = true;
                slash = true;
            }
            else if (ch == '{')
            {
                first_space = true;
                figure_opened = true;
            }
            else if (ch == '}')
            {
                first_space = true;
                figure_closed = true;
            }
            else if (ch == ' ')
            {
                slash = false;
                figure_opened = false;
                figure_closed = false;
            }

            if (!slash && !figure_opened && !figure_closed)
            {
                if (!first_space)
                {
                    builder.Append(ch);
                }
                else
                {
                    first_space = false;
                }
            }
        }

        // Return
        return new SqlChars(builder.ToString());
    }
};

I'm sure this isn't the world's most efficient CLR function but it did an ok job on some 100 documents I tested, with a few fails.

Let us know if you hear anything back on the connect item.


SSDT Not Dropping all Foreign Keys before Drop Table SSDT V11.1.50730.0
Using Schema compare to make changes from my development environment to a test environment (a copy of Production).

Part of the changes is the addition of columns to an existing table [Items]. The script that SSDT creates first drops foreign keys, creates and populates a temp [Items] table, then attempts to drop [Items] table. This produces the error:

Could not drop object 'dbo.Items' because it is referenced by a FOREIGN KEY constraint.

Is there some setting or reason that would result in the script generator to not include dropping and later recreating some of the foreign key constraints? Just wondering if it is something simple before I start manually modifying the script.

check under Tools->options->SQL Server Tools->Data compare if  "Disable foreign keys" is checked


ORA-00119 & ORA-00130 when trying to startup Oracle I'm having a problem with an Oracle database. Yesterday everything was working fine, I had an application that was using the database and there was no problem. At the end of the day I installed MySQL on the same server (I don't think it can be related but anyway..) and had to restart the server.
Today when I was using my application to access the Oracle database I had a connection error:

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Then I went to the server. All the services are up and the database is down.
When I try to login normally, I get an error saying that Oracle is not available, and can only connect as sysdba. Then when I try to start the database up I get the following errors:

Any help is apreciated. Thanks in advance!

Well, my teammate found the answer to the problem.

He changed some details in the file 'tnsnames.ora' at ORACLE_HOME\NETWORK\ADMIN. He just replaced “hostname” by “localhost”.

WP5 =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = WP5)
    )
  )

LISTENER_WP5 =

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

A new startup (without leaving sqlplus) made it work!

PS: We still have no ideia why/how it was working before, since the file updated date dates from about 2 weeks ago.


Is it possible to find the user whose aggregated score reaches X value first? Given this sample fiddle containing sales value and date for two users: http://sqlfiddle.com/#!2/8148be/3

How can I find the first user who has reached a total saleValue of 30 since the beginning of 2001?

The background: assume there is a contest running from date X, which ends the moment someone passes a specific threshold of sale value, and that person becomes the sole winner. There can be multiple contest running at the same time, so it is not feasible to check for winner in all contest when inserting new sales into the database.

First you will need a table with a column representing how many sales a seller has accumulated at a certain point in time:

SELECT
  `sellerId`,
  `saleDate`,
  ( SELECT SUM(`saleValue`)
    FROM `mzarzycki_test_sale` b
    WHERE b.`sellerId` = `a.sellerId`
    AND b.`saleDate` >= '2001-01-01 00:00:00'
    AND b.`saleDate` <= a.`saleDate`
    GROUP BY `sellerId`
  ) AS `saleSum`
FROM
  `mzarzycki_test_sale` a
WHERE
  `saleDate` >= '2001-01-01 00:00:00'
GROUP BY
  `sellerId`,`saleDate`
ORDER BY `sellerId`,`saleDate`

The rest is easy:

SELECT SaleDate,SellerId FROM
(...) c
WHERE c.Salesum >= 30
ORDER BY c.SaleDate
LIMIT 1

SQL Fiddle


How do I stop refresh conflicts between Proactive Caching of facts and timed refresh of Dimensions in a SQL Server Analysis Service 2005 OLAP cube? I have a SSAS 2005 OLAP cube which we've very nearly made into a ROLAP cube using a mixture of proactive caching for the fact partitions and timed refreshes for the dimension data.

There are two fact partitions - one containing yesterdays snapshot and one containing intra-day changes to that snapshot.  That's how we're able to achieve near real-time cube updates within SSAS (which Microsoft has stated is not suitable for real-time).

So, proactive caching updates the fact data (with sub-second timing) but is not set to update the dimension data.  About 10% of the dimensions receive updates intraday so we are continually refreshing them using a SQL Server process that runs every 2 minutes.

We are seeing cube corruption, periodically, requiring a full-reprocess of the cubes (taking several hours in some cases).  We believe this corruption is caused when proactive caching happens to update a fact while a dimension update is happening (perhaps when a new fact, requiring a dimension value that doesn't exist yet, is being added).  We don't know for sure because SQL Server and SSAS 2005 provide little no useful trace information for these exceptions.

Our short-term solution has been to turn off the dimension updates entirely.  Proactive caching still updates the facts and keeps our near real-time updates flowing until a new fact requiring a new dimension value comes in.  At that point, the cubes stop updating and we manually determine the faulty dimension and manually re-process it.

So, what we are looking for is a way to avoid having to manually update the dimensions (which causes an outage to users).

Sounds like you're getting orphaned fact rows a.k.a. early-arriving facts, meaning you have facts records that don't yet have a corresponding dimension member in one of the dimensions.

There's not really a way to solve this with the approach you're using because it's sounds like you'll always been a in  situation where your dimensions are lagging your facts. But you can work around it by changing your dimension key error options in your fact processing. There is an option to ignore dimension keys not found. What you want to do is set that to "Ignore" and you might have to play around with some of the error limit options as well.

This should do the trick. Not the prettiest of solutions, but it will work and prevent these outages.