SSIS - Fixed width flat file mapping tool/technique I am wondering if there is a tool (or a special technique) that anyone has used to map fixed width flat files in SSIS Flat file connection managers?

Usually I just use the Flat File Connection Manager Editor (Advanced tab) and start adding the column info (name, width, datatype etc.) for all the columns 1 by 1.

This has worked fine for me in the past but now I have to map a fixed width file that has close to 500 columns...

I am thinking (hoping) that there should be a tool or technique I can use that will allow me to accomplish the mapping in a more stream lined fashion...

Using the data dictionary I was provided for the file I could easily create an excel (or text) document for example with a few columns that includes the necessary information SSIS needs (Column Name, Delimiter,InputColumnWidth, OutpuColumnWidth, DataType) to map the file.

I am wondering if there is an SSIS tool or component that can then read this file and automatically create the mappings in the Flat File Connection Manager for me...

If there is no such tool, does anyone have any tricks or tips that might be able to help me map the file in the most efficient manner?

I am wondering if maybe I can modify the SSIS package xml file in a way where I could just create some code that scripts out the column info for all the columns in xml format and then I could just manually copy and paste it into the package xml file... not sure if this would work....

Note: After mapping the file in SSIS, it will then be loaded into a SQL Server table

Two options come to mind, both are features of the can't-say-enough-good-things-about-this-free-tool BIDS Helper.

Create Fixed Width Columns

The first is the Create Fixed Width Columns feature. It's as simple as Create a Flat File Manager pointing at the correct file and define 1 column.

Once that's done, then you right click back on the Connection Manager and use the context sensitive feature of "Create Fixed Width Columns..."

In the next window, you paste the tab delimited list (Excel) into the editor

Oh yeah, it's that's simple. If you need to do some tweaking for column types and such, then you go in and change it as normal with edit.

Biml

I like the Business Intelligence Markup Language for my SSIS development. There's lots of benefit to it but at a most basic level, use it for things like godawful flat files, especially when they provide no header rows (I'm looking at you, you mainframes).

It has often been my experience when dealing with legacy systems, that they have their file definitions in COBOL copybooks. Those devs will send me Excel files with the column layout like

DATABASE FIELD NAME    START LOC   LENGTH
SEND.DT                 1          STRING(08)
SEND.TIME               9          STRING(08)
DT                     17          STRING(08)
TERM                   25          STRING(04)
%ZONE                  29          STRING(01)

It ain't fancy, but Excel formulas made short work of it. So I'd create 4 new columns based on the above.

Sanitized Name (removed invalid characters for column names)

=SUBSTITUTE(SUBSTITUTE(B3, ".", "_"), "%", "PCT_")

Length (extracted the numbers, note this still has a leading 0 but it doesn't hurt)

=MID(E3,LEN("STRING(")+1,LEN(E3)-LEN("STRING(")-1)

XML (this builds out the actual XML for a flat file definition)

=CONCATENATE("<Column Name=""",F3,""" Length=""",G3,"""  DataType=""AnsiString""  ColumnType=""FixedWidth""  CodePage=""1252"" />")

DDL (Might as well build the target table while I'm at it)

=CONCATENATE(",   ", F3, " varchar(", G3, ")")

The first column of the DDL gets the lead comma dropped and then wrapped with CREATE TABLE dbo.FOO()

Ultimately, that XML results in something that gets slapped into a FlatFileFormat tag and then it's a simple matter of creating the Connections collection and finally the package itself. Looks a lot but it's actually pretty simple, once you have a pattern.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

    <FileFormats>
        <FlatFileFormat
            Name="FFF Pickup"
            CodePage="1252"
            RowDelimiter="CRLF"
            IsUnicode="false"
            FlatFileType="RaggedRight">
            <Columns>
                <Column Name="SEND_DT" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="SEND_TIME" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="DT" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <Column Name="TERM" Length="04"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
                <!--
                ad nauseum
                -->
                <Column Name="RPRTD_PU_PCS" Length="5"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>

    <Connections>
        <FlatFileConnection
            Name="FF Pickup"
            FileFormat="FFF Pickup"
            FilePath="C:\ssisdata\Operations\Input\Pickup Report Pickups.txt"
            CreateInProject="false"
        />
    </Connections>

<Packages>
    <Package Name="PickupLoad" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
        <Tasks>
            <Dataflow Name="DFT Load Pickups" >
                <Transformations>
                    <FlatFileSource
                        Name="OLE_SRC Pickup"
                        ConnectionName="FF Pickup"
                        RetainNulls="true">
                    </FlatFileSource>
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>

Oh and a similar question from SO with a disturbingly similar answer. Wish I had looked at Skinner's comments before I rewrote this answer.


How to simulate postgresql recovery mode? I would like to simulate putting postgresql into "recovery mode".
Few times i have situation where postgresql puted himself into recovery mode. In logs i had:

Oct 18 06:37:35 xx postgres[5738]: [19-1] 2014-10-18 06:37:35.441 CEST|5738|xx|yy|FATAL:  the database system is in recovery mode

It was caused by oom which killed one of process.
But when i try to simulate it in test environment - postgresql restarts.

How to prevent postgresql from restart and let him stay in recovery mode?

If you want to force PostgreSQL into recovery, you can create a recovery.conf with no restore_command or streaming replication configuration set. Leave hot_standby set to off in postgresql.conf.

Personally I recommend taking a base backup (pg_basebackup -X stream) and using that, rather than doing it to the original DB.


Controlling how tempdb activity is split across drives (tempdb on both solid state and spinning drives) I'm seeing the following tempdb I/O behavior over a one hour period:

There is a fair amount of disk I/O generated by multiple DW workloads executing on the machine, some of which cannot fit in the ~280GB of memory allocated to SQL.  One aspect that is interesting is that so much of the I/O is concentrated on the spinning disk drives (E) rather than the solid state drives (F and G) that are handling I/O much more efficiently.

We have pre-allocated the full 300GB each (600GB total) on the F and G drives to tempdb (using 12 files), and we have pre-allocated 1.3TB to tempdb on the E drive (currently 1 file).  The I/O data above suggests that tempdb usage is distributed across files based on the current size of the file.  I wasn't able to find documentation on this, but I also ran a query like the following to investigate further:

-- While running this query, writes to tempdb are distributed to E/F/G drives
-- in proportion to their current size.  This was shown by both
-- sys.dm_io_virtual_file_stats and the space used on the tempdb files before and after
SELECT TOP 100000000 *
INTO #temp
FROM [A_Really_Big_Table]

The ideal behavior would be that F and G are used exclusively unless they are both full, in which case the spinning disk drive should provide additional tempdb space so that occasional very large workloads can be serviced without running out of tempdb space.

Are we on the right track in observing that tempdb usage is distributed across files based on the current size of the files?  It was a little bit surprising to see this type of distribution rather than to see an equal usage of each file (which likely was the assumption of whoever set up this hardware and decided to allocate just one to tempdb on the spinning disk).

Based on Paul White's comment in response to this question, we are thinking about the following approach:

Shrink the tempdb files on spinning disk.  Based on our inital testing, this should shift the current distribution of work more to the solid state drives
Configure the solid state tempdb files to pre-allocate their space (as we already do now)
Configure the spinning disk tempdb files to start with no allocation.  Make sure that instant file initialization is on.  Tempdb will grow on the spinning disk only as needed (which will likely be at most once per week).
Create a maintenance plan that shrinks the tempdb files on spinning disk after times of peak load, putting the distribution back in favor of the solid state tempdb files.

Does this seem reasonable?  Are there any alternative approaches or potential problems to consider?  We will obviously test the approach to the extent possible, but won't be able to do so on completely equivalent testing hardware.

File writes are distributed across files in the same file group proportionally based on the current size of each file in the file group.  This is referred to as the "proportional fill algorithm" - look at http://sqlserver-performance-tuning.net/?p=2552 for some interesting details around that.

tempdb can have only a single filegroup.  If you attempt to create a filegroup in tempdb you get the following:

Msg 1826, Level 16, State 1, Line 1
User-defined filegroups are not allowed on "tempdb".

If you have trace flag 1117 turned on, files within a group will autogrow simultaneously across files in the filegroup for each file that is not currently at its maximum size, and where space exists on the disk.

Does your instance have trace flag 1117 turned on?  You'd probably want it off in this particular instance even though "best practices" often indicate having this turned on.  There is an item on Microsoft Connect asking to have a setting like this that can be enabled/disabled on a per-database basis, here:  https://connect.microsoft.com/SQLServer/feedback/details/781198/trace-flag-1117-autogrowth-of-data-files-is-instance-wide-would-like-a-flag-for-just-tempdb

Assuming the SSDs are devoted to tempdb, I agree with your assertion and suggest making the tempdb on the SSDs as big as possible (not 100% of the drive, perhaps leave 10% free).  Make the tempdb files on disk as small as possible, say 1MB, with autogrowth, and maximum file size as big as you need.  Monitor for tempdb filegrowth on the HDDs, and make the case for getting bigger SSDs if you think the company would benefit from it.

According to the documentation, SQL Server files can be created on raw partitions (partitions that haven't been formatted) by using just the drive letter of the partition in the ALTER DATABASE ... ADD FILE syntax.  This apparently removes the need to grow or shrink the file since it inherently uses the entire raw partition, as needed.  I'm uncertain if this would help your situation or not; just thought I'd throw it out there as an interesting factoid.  See "If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be put on each raw partition."


Using Oracle 12c database (SCAN) with Oracle 8 client I have successfully connected to my 12c database using Oracle 8 client. The connection is based on using the database-scan as host in tnsnames.ora.

There are 3 IP addresses reserved in DNS for scan...

However I read in oracle documentation that:

If using a pre-11g Release 2 client (Oracle Database 11g Release or Oracle Database 10g Rel. 2, or older) you will not fully benefit from the advantages of SCAN. Reason: The Oracle Client will not be able to handle a set of three IPs returned by the DNS for SCAN. Hence, it will try to connect to only the first address returned in the list and will more or less ignore the others. If the SCAN Listener listening on this specific IP is not available or the IP itself is not available, the connection will fail. In order to ensure load balancing and connection failover with pre-11g Release 2 clients, you will need to change the TNSNAMES.ora of the client so that it would use 3 address lines, where each address line resolves to one of the SCAN VIPs.

...so even though my application is connecting it might not be running optimally and using only 1 IP address in the SCAN.

My questions are:

What would cause the SCAN listener listening on this IP to be unavailable? How likely is it to happen?

What would cause the SCAN IP to be unavailable? How likely is this to happen?

Oracle will try to have at least one active scan listener on every node of the cluster. They are running on a vip and that vip can be relocated to any of the nodes.
As long as your app does not care in what instance it connects, it does not matter where your connection takes you. Problem comes when your app tries to connect to a specific service that is not on the node where the first returned listener address is located. In that case you are better off with a regular address list.
The scan address is unavailable during maintenance and while it is reassigned.


How to join sparse events for individual users on a "filled in" date ranges in Redshift? Let's say I have a table that has a record of every change a customer has made to their subscription for an internet service:

+-----+------------+-----------+------------+
| _id | account_id | plan_name | created_at |
+-----+------------+-----------+------------+
|   1 | a          | basic     | 10-01-19   |
|   2 | b          | standard  | 10-15-19   |
|   3 | c          | free      | 10-16-19   |
|   4 | a          | standard  | 10-16-19   |
|   5 | b          | free      | 10-20-19   |
+-----+------------+-----------+------------+

I'd like to perform an analysis on this data so that I can see the count of plans for every day, from 10-01-19 through 10-20-19.

To perform this analysis (among other things) I'd like to create a query that will give me a table with a row for every date for every user from their first recorded plan_name through present day (let's say this is 10-20-19).  It might look something like this:

+-----+----------+------+-----------+
| _id |   date   | user | plan_name |
+-----+----------+------+-----------+
|   1 | 10-1-19  | a    | basic     |
|   2 | 10-2-19  | a    | basic     |
|   3 | 10-3-19  | a    | basic     |
|   4 | ...      | ...  | ...       |
|   5 | 10-15-19 | a    | standard  |
|   6 | 10-16-19 | a    | standard  |
|   7 | 10-17-19 | a    | standard  |
|   8 | 10-15-19 | b    | standard  |
|   9 | 10-16-19 | b    | standard  |
|  10 | 10-17-19 | b    | standard  |
|  11 | ...      | ...  | ...       |
|  12 | 10-20-19 | b    | free      |
+-----+----------+------+-----------+

My first test attempt at this was the following:

WITH dates AS (
        SELECT (GETDATE()::DATE - ROW_NUMBER() OVER (ORDER BY TRUE))::date AS date
      FROM pg_catalog.pg_operator
      LIMIT 500)

SELECT date,
     (case when account_id is not null then account_id else lag(account_id ignore nulls) over (order by date) end) as account_id,
     (case when plan_name is not null then plan_name else lag(plan_name ignore nulls) over (order by date) end) as plan_name,
     (case when start_date is not null then start_date else lag(start_date ignore nulls) over (order by date) end) as start_date
 FROM dates
LEFT JOIN (
    select * from customer_subscriptions
    where account_id = '98asd7gf98a7') AS a
    on dates.date = trunc(a.start_date)
ORDER BY date DESC

With the caveat that I tried it on a single account_id from my subscriptions table -- and it worked!  My thought process was generate a date range and LEFT JOIN the subscriptions table on it at the "change over" dates and then use a lag function to fill in the NULLs

However, when i remove the where account_id = '98asd7gf98a7' it no longer works and I am left with a table that simply is a joining the start dates to a date from the range, without any of the lag filler in between.

Any thoughts on another way to approach this or changes I can make to my current approach?

Non-optimized solution:

WITH RECURSIVE
cte1 AS ( SELECT MIN(created_at) "date"
          FROM test
          UNION ALL
          SELECT ("date" + INTERVAL '1 DAY')::DATE
          FROM cte1
          WHERE "date" < ( SELECT MAX(created_at)
                           FROM test ) ),
cte2 AS ( SELECT DISTINCT account_id
          FROM test ),
cte3 AS ( SELECT cte1."date", cte2.account_id, MAX(test.created_at) created_at
          FROM cte1
          CROSS JOIN cte2
          LEFT JOIN test ON cte1."date" >= test.created_at
                        AND cte2.account_id = test.account_id
          GROUP BY cte1."date", cte2.account_id )
SELECT cte3."date", cte3.account_id "user", test.plan_name
FROM cte3
LEFT JOIN test ON cte3.account_id = test.account_id
              AND cte3.created_at = test.created_at
ORDER BY 2, 1

fiddle


Mariadb check tables on startup Is there a way to have mariadb check tables in the background and not lock the tables after a crash? We have a number of fairly large databases and suffered a hardware failure that resulted in most of them checking tables on a restart which unfortunately blocked access to the databases for hours.

I get that a check is a good thing, however of all the databases, only one ended up needing to be fixed.

Ta.
Peter.

Avoid the problem by switching your table from MyISAM to InnoDB.


Sort by Start Date Desc if End Date is null, if not Sort by End Date I have a SQL Server (RDBMS) table with a list of Activities. Each activity has a Start and End Date.

The requirement is to sort them is  order such that the ones with a NULL end date display first (which suggest "current" - start date descending), but the ones with values for both start and end to be sorted at the end of the "current" rows and sorted by end_date descending.

Lastly, all values with null for start and end dates must be at the end of the result set.

Values with an end date but a null start date should be sorted in the second section of the sort among the END date desc piece.

You can use a CASE expression in the ORDER BY:

ORDER BY CASE WHEN EndDate IS NULL 0 ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC

Sort criteria in order (link):

Values with a NULL EndDate will get a 0 and others will get a 1. This is used as the first sort criteria.
Then the second criteria is EndDate.
Finally ties will be sorted by StartDate.

You can play with ASC and DESC and column order if the global order is not what you expect.

Sample data and query:

DECLARE @data TABLE(id int identity(0, 1), StartDate date, EndDate date)
INSERT INTO @data(StartDate, EndDate) VALUES
    ('20160101', '20160105')
    , ('20160101', null)
    , ('20160101', '20160107')
    , ('20160102', '20160103')
    , ('20160102', '20160105')
    , ('20160102', null)
    --, (null, '20160105')
    --, (null, null);

SELECT *
FROM @data
ORDER BY CASE WHEN EndDate IS NULL THEN 0 ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC

Output:

id  StartDate   EndDate
5   2016-01-02  NULL
1   2016-01-01  NULL
2   2016-01-01  2016-01-07
4   2016-01-02  2016-01-05
0   2016-01-01  2016-01-05
3   2016-01-02  2016-01-03

This ORDER BY clause (link) will put double NULL (Start + End) at the end:

ORDER BY CASE
          WHEN EndDate IS NULL AND StartDate IS NULL THEN 2
          WHEN EndDate IS NULL THEN 0
          ELSE 1 END ASC
    , EndDate DESC
    , StartDate DESC;

id | StartDate            | EndDate
5  | 2016-01-02 00:00:00  | NULL
1  | 2016-01-01 00:00:00  | NULL
2  | 2016-01-01 00:00:00  | 2016-01-07 00:00:00
4  | 2016-01-02 00:00:00  | 2016-01-05 00:00:00
0  | 2016-01-01 00:00:00  | 2016-01-05 00:00:00
6  | NULL                 | 2016-01-05 00:00:00
3  | 2016-01-02 00:00:00  | 2016-01-03 00:00:00
7  | NULL                 | NULL


what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again? what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again ?

I know the below points exist means, there won't be any reset happen in binlogs.

I would like to know whats the maximum value for the bin-log files.
In case, the expire_log_days not provided, then it should not replace the first file, throws 'file-exist' error.  The logs will be kept forever unless you specify an expiry.
If the binary log master  index files having the entry, I don't think it will replace.

Then what will happen eventually?

If the binlog file number reaches its maximum value, it appears the server will throw an error.

However, the maximum value is not 999999 as it would appear ... it's 2,147,483,647.

From the source file sql/log.h (MySQL 5.6 source tree):

#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF

Other relevant files are sql/binlog.cc and sql/log.cc.  The code that generates log file names zero-pads values to 6 digits after incrementing the current largest number by 1, but sprintf(ext_buf, "%06lu", next) will not truncate larger numbers, it just won't pad them... so mysql-bin.999999 would be followed by mysql-bin.1000000.

And, according to the Google Machine, somebody apparently tried it just to make sure.


INSERT INTO statement with select I am trying to insert a new record into a table, but only if another record of the same table meets some conditions. Sadly it does not work I alwys get the following error: ERROR:  duplicate key value violates unique constraint "stackacos_pkey" Even though the id is not yet present in the db. Any idea how to avoid this issue ?

INSERT INTO stackacos (id, created_at, updated_at, owner_id, stack_id, admin_full, admin_create, admin_read, admin_update, admin_delete, "create", read, update, delete)
SELECT 'notyetindbisforsureunique', '2018-09-18 14:34:57', '2018-09-18 14:34:57', 'test_user_222', 'KZROLZYQbSeHbMtZNFCpMlaKJyfaKFMdcbwGklCiUQBuIiZSptBSlVcuUGofnjMM', 'false', 'false', 'false', 'false', 'false', 'false', 'true', 'false', 'false'
FROM stackacos
WHERE stackacos.owner_id = 'test_user_1' AND
    stackacos.admin_full = true OR (
        stackacos.admin_create >= 'false' AND stackacos.admin_create >= 'false' AND
        stackacos.admin_read >= 'false' AND stackacos.admin_read >= 'true' AND
        stackacos.admin_update >= 'false' AND stackacos.admin_update >= 'false' AND
        stackacos.admin_delete >= 'false' AND stackacos.admin_delete >= 'false'
        ) RETURNING *

use nextvalue(idstackacos_find_the_right_sequence_name ) instead of

OLD:

SELECT 'notyetindbisforsureunique', '2018-09-18 14:34:57', '2018-09-18 14:34:57'

NEW:

SELECT nextvalue(idstackacos_find_the_right_sequence_name) , '2018-09-18 14:34:57', '2018-09-18 14:34:57'

If it's not working please copy here the definition of the table and of the sequence.


Hi disk I/O and crash when turning on sync_binlog in MySQL I have a MySQL master server (MariaDB 10.0.17) that have a very odd behavior. It has one slave fetching data from it.

When I set global sync_binlog = 1, the server I/O goes very high and after some hours the server crashes. In the graph bellow, at 15:20 the server crashes. Then from 15:20 til ~15:40 it is recovering a MyISAM table I have with more then 120 million rows. Then at 15:40 it gets back to normal operation. The boot configuration for sync_binlog is 0

At 16:15, I set global sync_binlog = 1, and the I/O goes up again. At 16:45, I set global sync_binlog = 1 and the I/O goes down.

Why this is happening? Is there some way to avoid this high I/O and also the crash?

Please keep in mind what MySQL Documentation says on sync_binlog:

If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Apparently, you are writing lots of commit groups in one write operation to the binary log.

Setting sync_binlog to 0 lets the OS flush commit groups to disk (on its own time)
Setting sync_binlog to 1 lets mysqld flush commit groups to disk on each write

You could set sync_binlog > 1 to throttle mysqld's flush of the binlog.
You should expect setting sync_binlog to create more disk I/O, not less.
If your DB Server is a bare-metal server, you may need to check your disk controller and either change the batteries or get a better disk controller.
UPDATE 2015-04-02 10:57 EST
You just mentioned in your comment that you are using SSDs. I wrote a post over a year ago (MySQL on SSD - what are the disadvantages?) stating that SSDs are not all that good for sequential writes. In your case, the sequential writes are happening on the binlogs. SSD write performance is the same regardless of sequential writes and random writes. I would consider moving your binary logs to HDD rather than on SSD.


Restoring PostgreSQL 9.1.9 backup with 9.3.0 results in error How can I resolve the following errors in 9.3.0:

psql mydb < ~/mydb_2013-10-25.backup.tar

results in

...
ALTER TABLE
ALTER TABLE
ALTER TABLE
ERROR:  could not open file "$$PATH$$/2309.dat" for reading: No such file or directory
 setval
--------
      1
(1 row)
...

I see the file 2309.dat in the tar, so I am slightly confused what can be the reason. I run 9.3.0 on OS X and made backup with 9.1.9 on Ubuntu.

Edit: I managed to restore data with pgAdmin3-1.18.1.

Update 10/28

Using pg_dump it is possible to backup a database as a tar archive

pg_dump -v -o -c -Ft -f test_backup.tar testdb

Here are the contents of the test_backup.tar file

-bash-4.1$ tar -tvf test_backup.tar
-rw------- 2048/1024      1605 2013-10-28 14:50 toc.dat
-rw------- 2048/1024         5 2013-10-28 14:50 1769.dat
-rw------- 2048/1024      1654 2013-10-28 14:50 restore.sql

To restore the backup.tar file, I used this command

pg_restore -c -Ft test_backup.tar -d testdb

-c is to drop objects being restored from the database

-Ft is to use the tar archive format

-d is the name of the database

-v is for verbose

So in conclusion, psql can't be used to restore or create files in the tar format. You must use pg_dump to create them using the -Ft option and then again with pg_restore to restore using the same file.

Craig


How to make a trigger work after a certain UPDATE I would like to know if it's possible to create a trigger on Oracle that is invoked only when a certain UPDATE is done. For example:

UPDATE colors
SET tastes_good = y
WHERE color_name = 'Apple';

should call the trigger, while

UPDATE colors
SET tastes_good = n
WHERE color_name = 'Apple';

should not.

Something like this will suit your needs:

create or replace trigger colors_utrig
after update on colors
for each row
begin
  if :new.tastes_good = 'y'
    then
      -- put your code here
      NULL;
  end if;
end;
/


Log Shipping "out of sync" but none of the jobs are failing I've configured a log shipping from a server, to the same server, only with a different instance.

Primary server is configured this way:

LSBACKUP_MyDatabase - every 25min

Secondary server:

LSCOPY_MyDatabase - every 1 minute

LSRESTORE_MyDatabase - every 10 minutes

Whats happening is, the primary backup job is running fine ( there is a lot more history, im just showing the last 2 ).

in the folder, I can see the TRN files.

In the secondary instance, LSCOPY and LSRESTORE is ok too. It's copying files, but the problem is here. The restore job is reporting this "message" ( the job runs succesfully, so I don't think it's a error):

  Message

  2015-12-29 09:10:02.41     Skipped log backup file. Secondary DB:
  'MyDatabase', File: '\ServerIP\instancia
  g\BACKUP\Log_Sp_Secundario\MyDatabase_20151229104500.trn'

  2015-12-29 09:10:02.41     Could not find a log backup file that could be
  applied to secondary database 'MyDatabase'.

  2015-12-29 09:10:02.42 The restore operation was successful. Secondary
  Database: 'MyDatabase', Number of log backup files restored: 0

  2015-12-29 09:10:02.42     Deleting old log backup files. Primary
  Database: 'MyDatabase'

  2015-12-29 09:10:02.42 The restore operation was successful. Secondary
  ID: '5a0a361c-039c-40a3-9c39-af5e338c7f72'

And then, when I click to see the history of the LSALERT_ Job, its reporting errors with this message:

  Message Executed as user: CMDO\gdladmin. The log shipping secondary
  database VMWGDLPRD04\GDLIC2014.GDL_IC has restore threshold of 45
  minutes and is out of sync. No restore was performed for 8323 minutes.
  Restored latency is 0 minutes. Check agent log and logshipping monitor
  information. [SQLSTATE 42000] (Error 14421).  The step failed.

According to one of the Microsoft's support pages, there is this query to show if there are gaps between logs. there are none:

SELECT
    s.database_name,s.backup_finish_date,y.physical_device_name
FROM
    msdb..backupset AS s INNER JOIN
    msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
    msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
    msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
    (s.database_name = 'MyDatabase')
ORDER BY
    s.backup_finish_date DESC;

I've been searching all over the internet, but I could only find those dba blogs with lack of information, and some posts saying that the primary database was dropped ( obviously was not ).

The primary instance is 2012. The secondary is 2014. the secondary database is in recovering mode.

To fix this, should I recreate all log shipping?

Well it didn't fixed. I think I will recreate the log shipping again

Before , you give a try with that, why don't you go for looking the most recent differential backup and restore it on the secondary.

We also had situations as mentioned above and found that:

This happened due to NW glitch due to which the folder shared(on primary as common backup location with secondary) was no more shared ( due to some issues on cluster resource) and as a result few log backups never went/copied to secondary and since there was a gap, even though restore job completed but LS kept saying out of sync.

Well, in our case we went ahead and restored the latest full back up to bring the missing LSN chains in sync and later the restore job picked the next log backup file and LS was back in sync.


Normal Form question Hey guys I am trying to study for an exam and I cant seem to get my head around normal forms.

here is one question:
R1 [a b c]
fd1: {a,b} -> {c}
fd2: c -> b

I know the candidate keys are
{a,b}
{a,c}

But how do i determine what normal form this is in?

Surely, choosing {a,b} as key, R(X) is in 3NF

Using an alternative definition of 3NF:

3NF Relation

A Relation Schema R(X) is in 3NF if for every non-trivial FD Y->Z

Y contains a candidate key (Y is superkey)

Or

Z is a prime attribute (Z is part of a key)


unknown variable 'log_slow_verbosity=query_plan' What is the use of log_slow_verbosity = query_plan parameter in my.cnf ?

I got an error when installing MySQL in my machine because of this parameter.

log_slow_verbosity is for MariaDB, not MySQL

Perhaps your OS has MariaDB repos installed instead of MySQL. If you downloaded MySQL and installed it, mysqld encountered an already existing my.cnf that was for MariaDB.


SQL Server history of growth and shrink events Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014 ? (New instances to support, noticed from the ticketing system that they have previous history of "log drive full", so would like to dig into the root cause before it happens again.)

This was useful: Identify File Growth Events . Came up with below query, but it doesn't show any of the "manual" log shrink events, just the "auto" events. Am I doing this the wrong way, any other place to look for past info ?

select
    te.name as event_name,
    tr.DatabaseName,
    tr.FileName,
    tr.IntegerData,
    tr.IntegerData2,
    tr.LoginName,
    tr.StartTime,
    tr.EndTime
--select *
from
sys.fn_trace_gettable(convert(nvarchar(255),(select value from sys.fn_trace_getinfo(0) where property=2)), 0) tr
inner join sys.trace_events te on tr.EventClass = te.trace_event_id
where
tr.EventClass in (93, 95) --can't identify any other EventClass to add here
order by
EndTime desc;

Is there a way to look at the complete history of growth and shrink events for the last weeks/months on SQL Server 2014 ?

There is easy way to get information about Data/log file autogrowth and Auto shrink events from database reports. The report fetches data from default trace. I am not sure how recent would be your report that depends on when was default trace rolled over. The default size is 20 MB after which it will roll over and also note that after SQL Server restart the trace will be rolled over

Connect to SQL Server instance and then right click on database and then select reports and then standard reports and then click disk usage.

In the report which comes look for Data/log file autogrowt auto shrink events and expand the + sign this is just below the circular usage report. The report would look like below.

In my case it only reported autogrowth event as I don't have auto shrink or any shrink activity running, which I guess you know is utterly bad.


what is an InnoDB Page checksum? What is an InnoDB Page checksum exactly? Does a Page checksum only detect issues with underlying storage when writing or reading to a page/block?

(I think this is what you are asking about.)

InnoDB "blocks" are 16KB.  But most disk subsystems work in smaller units -- 4KB or 512 bytes.  For InnoDB data to remain intact, the disk needs to write all 16KB as a unit -- either all written or none.  What can happen is that the pieces of the 16KB are written one after another, and the power fails part way through.  This causes a "torn page" ("page" referring to the 16KB block).

To recover from a torn page, InnoDB does two things.  It checksums each block in order to discover it, and it uses a "double write" for recovery.  The block (or at least certain critical blocks) are written twice - to some relatively constant spot, then to the desired location on disk (in the data or index).

When recovering from a crash, the "double write buffer" is checked to see if it leads to a "torn page"; in which case, it is repaired.

The double write, as its name implies, is costly.  (I have no metrics on how costly; I suspect the cost depends heavily on HDD vs SSD and RAID controllers.)  Turning it off is a way to gain some more speed, but with risk.  FusionIO was (it's been bought out) the only drive manufacturer who guaranteed 16KB atomic writes; I hope others have added this feature.

RAID with Battery Backed Write Cache should make the double write virtually zero cost.

A different checksum...  The Percona Toolkit uses a "checksum" for data.  Since rows are not necessarily laid out identically between Master and Slave, checksumming the files is not useful to seeing if the tables match.  I suspect it involves reading the rows in a repeatable order, and checksumming each row or clump of rows.

"rsync" does a similar thing, but at the file level.


Difference between db_owner and having all other database roles except the 2 containing the deny keyword What is the difference between having only the db_owner database role and having all other except db_denydatareader and db_denydatawriter?

db_securityadmin +
db_accessadmin +
db_backupoperator +
db_ddladmin  +
db_datareader +
db_datawriter

Versus

db_owner

I've read that when you have db_accessadmin and db_securityadmin roles, you can change database role of another user into the database but I tried and it seems that I don't have the permission to change any permission while logged with this user. But if I give to this user the db_owner role, now I can change permission to any other user.

As per the Docs article, db_owner can perform functions that no other role or combination of roles provides, including but not limited to:

Adding users to fixed-database roles
Dropping the database
The CONTROL DATABASE permission

There is a diagram on the linked article that shows the fixed-role permissions for each role. Any permission not listed in that diagram is granted to the db_owner role only by default.


Export pipe-delimited data with progress db I've been handed a task where we need to dump specific tables in a progress database (10.2B) to pipe-delimited flat files (50+ tables) and send them to a third party for analysis every week.

Looking at the sqldump command it seems it only supports comma delimited or quoted... but I have data in these tables with both commas and quotes.

How can I do this?

If you're ok with writing some code:

OUTPUT TO VALUE("filename.d").

FOR EACH table-name:
EXPORT DELIMITER "|" table-name.
END.

OUTPUT CLOSE.

It's possible to write something that's more automated than this if you're interested.


TNS errors using Oracle instant client I'm struggling to get my Oracle application working. I'm using the Oracle 11.2.0.4.0 instant client with a C++ application that is compiled using Visual Studio 2010. The machine is running Windows Server 2012 R2. The database is running on another machine.

The application is as basic as possible where I set up the environment and try to get the connection.

When trying to run it I'm faced with two different TNS errors:

ORA-12543: TNS:destination host unreachable (when using EZConnect string)

and

ORA-12154: TNS:could not resolve the connect identifier specified (when using the TNS name)

I'm running the application from the instant client directory, with ORACLE_HOME set as the same directory. I also export the directory of the oraocci11 dll files in the PATH variable.

What is strange is that sqlplus can connect without problems using both EZconnect and the TNS name (I have a tnsnames.ora in /network/admin).

What am I doing wrong?

Edit:
Here is tnsnames.ora:

G2CLIENT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.77)(PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVICE_NAME = web)
  )
)

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  (CONNECT_DATA =
    (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

WEB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.77)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = web)
  )
)

and sqlnet.ora:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
SQLNET.AUTHENTICATION_SERVICES = (NTS)

The call that triggers the error is createConnection on a StatelessConnectionPool object which I have tried calling with different variations:

createStatelessConnectionPool("username","pwd","WEB")
createStatelessConnectionPool("username","pwd","10.0.1.77/WEB")
createStatelessConnectionPool("","","username/pwd@//10.0.1.77/WEB")

All of these seem to throw ORA-12154.

And as I mentioned above, sqlplus works fine.

suppose Orcl service already created  but you can't communicate with them then do follow :
 you need to run your database service like "oracleServiceorcl" as a Administrator so that client server can communicate.

If any doubt then plz put error like ora- Any Digiy


SQL Server 2008 eval expired I have (more than one instance) some of my experiments with SQL Server 2008 eval (why eval --> brutal bureaucracy about using licenced version for testing or short life-cycle experiments), and after expiration I saved image of virtual, and now I want to start up some of them by using DataCenter licence, but this version doesn't allow me to reinstall or upgrade expired instance with options to hold system setting, users, linked servers or etc....

my questions:

Is there some version of SQL Server 2008 (ms-whatever didn't gave me an answer) that be able to start up expired eval?
Is there comment about uninstall and attach from File?
(maybe really stupid) Is it possible to un-install eval and install Datacenter by using some parameters from command line, or some 3rd. party tools for that?

You should be able to run the installer and specify the /SKUUPGRADE switch which will allow you to upgrade the existing instance.  There's a couple of reg keys that you'll need to blow away to get SSMS up and running on the machine, but those are documented in the MSKB somewhere.


MySQL: Optimize query with a range and distinct count I have a query on a large table with millions of rows that looks like this:

SELECT
    COUNT(
        DISTINCT clicks.tx_id
    ) AS unique_clicks_count ,
    clicks.offer_id
FROM
    clicks
WHERE
    clicks.offer_id = 5
    AND created_at > '2014-11-27 18:00:02'
;

Created_at is a timestamp. I have a compound index on (offer_id, created_at) which gets used. The following is the explain:

|  1 | SIMPLE      | clicks | range | clicks_created_at_index,clicks_offer_id_created_at_index | clicks_offer_id_created_at_index | 8       | NULL | 215380 | Using index condition; Using MRR |

Keeping in mind the range, what kind of index would I need to be able to count the distinct tx_id's efficiently, most likely which covers tx_id as well?
What would the index look like without specifying clicks.offer_id = 5, and instead doing GROUP BY offer_id?

You have the best index there is.  It is in the right order, and the EXPLAIN says "Using index", which means that it read the index to get the answer, and did not have to reach into the data.

(To further address all the comments...)

Note that it needed to read about 200K rows (of the index) to do the count.  That many rows takes time.

INDEX(offer_id, created_at) versus INDEX(offer_id, created_at, tx_id) -- Apparently you are using InnoDB and tx_id is the PRIMARY KEY.  The PK is included in every secondary key, so these two index specifications are virtually identical.

Order of the columns in an INDEX usually matters.  And it does matter here.  The fields must be in this order:  (1) all the the "=" conditions (offer_id), (2) one range (created_id), and (3) all the other fields to make it "Using index", in any order (tx_id).

If you did not have offer_id = 5, follow the above pattern and get (1) (empty set), (2) (created_id), and (3) (tx_id) -- That is, INDEX(created_at, tx_id).  Note that neither index works well for the other query.

No kind of PARTITIONing would help performance at all.  You don't need a 2-dimensional index (as in two ranges); you have "=" and "range", so a 'compound index' works best.

I suspect that "Using MRR" (Multi-Range Read Optimization) effectively replaces "Using temporary" and "Using filesort" would might normally be used for DISTINCT.


Which is the best approach to "suspend" the activity of a whole AOG cluster thinking on start it again later? My company is going to migrate some services from one on-prem site to another but the database servers will remain in the same location (for now).
I've been asked for "suspend" all the activity of the three AOG cluster nodes (all secondary instances are readable without routing), during a long maintenance window.
Two main options are coming to my mind:

Maybe not the fanciest. Suspend data movement and then stop SQL Engine service at the secondary replicas first and then stop the primary at the end. When the time to start services comes, start the primary first and the secondaries later in inverse order and resume data movement at the end.
Disable jobs and intended logins at all instances to enable them again later keeping all AOG activity running but without user access.

I know the "best option" is completely relative to the context, but I would like to read some opinions.
Thank you,

You don't need to suspend data movement, but otherwise what you've written for option #1 is a good approach!  Before doing anything, I would disable automatic failovers on all replicas, to prevent any unexpected failovers when you bring the AG back up.
Then, as you said, shut down the secondaries first, then shut down the primary.
When you bring the primary back up, it should come online as the primary again.  Then bring up the secondaries.
Make sure to turn automatic failovers back on once everything is stable, if you had it on in the first place.


IMHO your question requires a PIVOT solution.

CTE statment get the sum of seconds grouped by developer and day.

SELECT   account,
         m_idPlayer AS player,
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec,
         DATEPART(DAY, Start_Time) as Dy
from     logs
GROUP BY account, m_idPlayer, DATEPART(DAY, Start_Time)
GO

account      | player  |  Sec | Dy
:----------- | :------ | ---: | -:
developer    | 0000001 | 1984 |  2
infinity13   | 0000005 |  190 |  2
kevlaire05   | 0000004 |   56 |  2
runewars21   | 0000002 | 1230 |  2
speedyturtle | 0000003 |  514 |  2

PIVOT part, simply shows this data in columns. Keep in mind you must add all days of one month or deal with a dynamic query.

WITH grp AS
(
SELECT   account,
         m_idPlayer AS player,
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec,
         DATEPART(DAY, Start_Time) as Dy
FROM     logs
GROUP BY account, m_idPlayer, DATEPART(DAY, Start_Time)
)
    SELECT account, player, [1], [2], [3], [4]
    FROM
    (SELECT account, player, Sec, Dy
     FROM   grp
    ) src
    PIVOT
    (SUM(Sec) FOR Dy IN ([1], [2], [3], [4])) as Pvt
;

account      | player  |    1 |    2 |    3 |    4
:----------- | :------ | ---: | ---: | ---: | ---:
developer    | 0000001 | null | 1984 | null | null
runewars21   | 0000002 | null | 1230 | null | null
speedyturtle | 0000003 | null |  514 | null | null
kevlaire05   | 0000004 | null |   56 | null | null
infinity13   | 0000005 | null |  190 | null | null

dbfiddle here

In reference to the second (or main) question:

How to check if logs has 7 consecutive days?

I've used a STD solution, you can find it more by googling a bit.

SQL and contiguous data ranges.

create table logs
(
m_idPlayer varchar(20),
Start_Time datetime,
End_Time datetime,
TotalPlayTime int,
account varchar(20),
SEQ int);
insert into logs values
('0000001','2017/02/02 13:17.59','2017/02/02 13:23:11',  312,'developer',1),
('0000001','2017/02/03 13:23:51','2017/02/03 13:26:15',  144,'developer',2),
('0000001','2017/02/04 13:58:34','2017/02/04 14:13:42',  908,'developer',3),
('0000001','2017/02/05 14:16:45','2017/02/05 14:18:14',   89,'developer',4),
('0000001','2017/02/06 14:23:41','2017/02/06 14:25:59',  138,'developer',5),
('0000001','2017/02/07 14:27:00','2017/02/07 14:27:56',   56,'developer',6),
('0000001','2017/02/08 14:29:15','2017/02/08 14:32:25',  190,'developer',8),
('0000001','2017/02/10 14:26:09','2017/02/10 14:32:25',  376,'developer',9),
('0000001','2017/02/11 14:18:20','2017/02/11 14:37:21', 1141,'developer',10),
('0000001','2017/02/12 14:28:21','2017/02/12 14:37:42',  561,'developer',11);
GO

10 rows affected

DECLARE @StartDate datetime = '2017/02/02';
DECLARE @EndDate   datetime = '2017/02/12';

WITH gpr AS
(
SELECT   account,
         m_idPlayer AS player,
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec,
         DATEADD(DAY, 0, DATEDIFF(DAY, 0, [Start_Time])) as Dt
from     logs
GROUP BY account, m_idPlayer, DATEADD(DAY, 0, DATEDIFF(DAY, 0, [Start_Time]))
), grp2 AS
(
    SELECT account,
           player,
           Dt,
           DATEDIFF(DAY, '17530101', Dt) - ROW_NUMBER() OVER (PARTITION BY account ORDER BY Dt) AS GroupID
    FROM gpr
    WHERE Dt BETWEEN @StartDate AND @EndDate
)
SELECT     grp2.account,
         grp2.player,
         MIN(grp2.Dt )AS StartDate,
         MAX(grp2.Dt) AS Enddate
FROM     grp2
GROUP BY grp2.account,
         grp2.player
HAVING    COUNT(*) >= 7;

GO

account   | player  | StartDate           | Enddate
:-------- | :------ | :------------------ | :------------------
developer | 0000001 | 02/02/2017 00:00:00 | 12/02/2017 00:00:00

dbfiddle here


Database logs growing during maintenance plan I have read several threads and sites about SQL logs but I don't feel I have found the answer. Or if I did I did not understand it enough to address my issues. My issue: When I run my SQL maintenance plans my SQL logs grow during the backup and fill the drive completely.

What I am trying to understand is why this is occurring, and how to prevent it. Again the backup is filling the drive\logs and not user transactions.

I am not a DBA, but know something about SQL, but not enough and need some help.

My scheduled Maintenance Plans:

Maintenance Job 1: Daily Job - Tasks are to first remove old backup files, remove old log files that have expired. The remove old History. Now it re-orgs indexes followed by updating statistics. Now it backups up the DB (FULL, w overwrite existing, expires in 2) then backup the transaction log (append existing, expires 1 day).

During this process the DB log fills up. The thought is during any business day we can recover back to the top of the last hour in the event of corruption - From a DB restore perspective We should never need to go back more than an hour. Additionally, with sending the backups to tape we can still mount and recover the DB even after it expires, but it is lost from the chain - which should be fine as we can copy data from tables as\or if needed.

Maintenance Job 2: Hourly TLOG backup: During the day at the top of every hour I run a transaction log backup, so we can recover at the top of every hour.
Maintenance Job 3: End of week  Rebuild Indexes for DBs
Maintenance Job 4: Backup DBs (Expires 7 days; Full; Append Existing) runs 2x per month.

What I am trying to understand is why this is occurring, and how to prevent it.

Maintenance Plans do not have much logic that you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes.

You are better off implementing Ola's backup solution as well as Index maintenance  solution .

Also, refer to Why Does the Transaction Log Keep Growing or Run Out of Space? - especially answers from @MikeWalsh and @AaronBertrand


How to know if anybody change the db_owner in SQL server I just want to know is that anyway we can check in SQL server if anybody change the db_owner of a database to other login ID

Are you running any DMV capture tools like sp_whoisactive, redgate, DPA, etc or had tracing enabled? That will make it easier to see if it catches the SQL that ran making the change.

What I would do now is set up a trigger that will track the aspects of who makes permission changes to your database and log those transactions for future review.

Example of DDL Trigger.


Secondary standby server log shipping backup job error I am getting the following error while my log shipping job is trying to copy from the primary server:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>sqllogship.exe -copy C83E29 23-5131-4767-A902-7E06A2572EA8 -server 831873-DB5

Microsoft (R) SQL Server Log Shipping Agent [Assembly Version = 11.0.0.0, File Version = 11.0.2100.60 ((SQL11_RTM).120210-19 17 )] Copyright (c) 2012 Microsoft. All rights reserved.

2016-11-30 04:24:19.53  ----- START OF TRANSACTION LOG COPY   -----
2016-11-30 04:24:19.61  Starting transaction log copy. Secondary ID: 'c83e2923-5 131-4767-a902-7e06a2572ea8'
2016-11-30 04:24:19.61  Retrieving copy settings. Secondary ID: 'c83e2923-5131-4 767-a902-7e06a2572ea8'
2016-11-30 04:24:19.61  Retrieved copy settings. Primary Server: '642165-DB4', P rimary Database: 'TestDB1', Backup Source Directory: '\\642165-DB4\Incoming', Ba ckup Destination Directory: '\\831873-DB5\Outgoing', Last Copied File: ''
2016-11-30 04:24:19.62  Copying log backup files. Primary Server: '642165-DB4', Primary Database: 'TestDB1', Backup Source Directory: '\\642165-DB4\Incoming', B ackup Destination Directory: '\\831873-DB5\Outgoing'
2016-11-30 04:24:19.65  *** Error: Logon failure: unknown user name or bad password. (mscorlib) ***
2016-11-30 04:24:19.67  ----- END OF TRANSACTION LOG COPY     -----

Exit Status: 1 (Error)

It is clear the credential used for the copy job cannot be validated by the server that stores the T-Log backups or copied T-Log backups.

What account have you configured for log shipping copy job?

To access the destination disk, the admin there has to create an NT user and grant permissions. Ask for the help of your Windows administrator.

Verify that the account in use by SQL Server Agent has a login at the secondary server with sufficient permissions.

Make sure a login is created at Windows level with proper access to destination server.


How to use OR operator in CASE THEN statement I have a situation like this

select col1, col2
from table1
where col1 = case when (booleanexp1) then a OR col1 = b
                  when (booleanexp1) then c OR col1 = d end

I mean I need to get where col1 = a or col1 = b when booleanexp1 is TRUE,
but I get a syntax error? How is the right way to perform something like this?

Assuming two booleanexp1s is a misprint:

select col1, col2
from table1
where ((booleanexp1) AND (col1 IN (a, b)))
   or ((booleanexp2) AND (col1 IN (c, d)))

PS. not (booleanexp1) is one possible option of (booleanexp2)...


In SQL Server and using only client side encryption requests, can I use a different certificate for each client? Based upon the UI dialogue box in SQL Server Configuration Manager, if I right click 'Protocols for server_name', it appears that I can only select one certificate to use.

My experience so far tells me that this certificate option is not for server-side 'force encryption', the flag option one tab over. This is because server-side force encryption does not require that the client have a copy of the public key/ certificate. Any client that wishes to connect can connect provided that they are authorized to connect and a temporary certificate will be used for the encryption. It is only when a client makes the request that a connection be encrypted (client-side encryption) does it matter whether the client has a copy of the certificate selected in the SQL Server Configuration Manager.

The UI appears to limit the number of certificates to one but perhaps this is simply poor UI. Is it possible, say by powershell commands, to establish a pool of certificates, with different durations, by which various kinds of clients could connect?

SQL Server SSL encryption that is initiated from the server does not require that the client have a copy of the certificate in advance. The certificate and private key are stored in the certificate store of the service account on the server. If the certificate is selected using SQL Server Configuration Manager and Force Encryption is set to true, then whenever a client requests a session, the server replies with the encryption requirement and sends a copy of the certificate, which contains the public key. To establish an SSL or TLS session, the client randomly selects a symmetric session key and transmits it back to the server encrypted by the public key. This random key is then used as the basis for secure communication. It is recommended to use a domain certificate or CA certificate and not a self signed certificate as a self signed certificate would leave you vulnerable to a man in the middle attack.

There is also a way to request from the SQL Server Management Studio client that a session be encrypted. I don't see any certificate configuration, so it may use a self signed certificate generated at the client to initiate a secure connection. Either way, you can see if any connections are encrypted using the sql statement:

    select encrypt_option, * from sys.dm_exec_connections


Concurrent Update of Array (Postgres) I have an array that I can update from multiple workers as follows:
UPDATE my_table SET arr = array_append(my_table.arr, element).
The problem pops up when two workers try to update the array at the same time.
For instance, both workers see that at the moment of the update, the array is empty and try to append an element as follows:
array_append(empty_arr, element_a) and array_append(empty_arr, element_b). In the end, the final content of the array will be: arr[element_a] or arr[element_b] because one of the workers will finish updating the array earlier and its value will be overwritten with the value from the other worker.
How can I ensure that both values are written in the array: arr[element_a, element_b]?

There is nothing you need to do, because this kind of anomaly cannot occur in PostgreSQL.
When the second transaction gets the lock on the row, it will re-read it and see the value modified by the first transaction.
See the documentation for details.


Incremental backups - differences What is the differene between:

BACKUP INCREMENTAL LEVEL 0 DATABASE;

and

BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

I read some manuals, but I don't get the idea.

An INCREMENTAL LEVEL 0 is a full backup of all used blocks in the database.
An INCREMENTAL LEVEL 1 CUMULATIVE is a backup of only the blocks that have changed since the last incremental backup.

... the definitions above paraphrased from the Glossary of Oracle Database Backup and Recovery User's Guide 11g Release 2.


Does RAM taken up Shared Memory connection counts against Max Server Memory? I have an app that's local to the SQL Server and thus has a Shared Memory connection to it.  I was wondering whether the RAM taken by the connection (including data transfers) counts against the max memory limit set for the SQL Server.

The reason I am asking is that the SQL Server is maxed out on memory (e.g. Target Server Memory = Total Server Memory and other metrics).  If the RAM taken up by Shared Memory connection counts against it, wouldn't I be better off using TCP connection to the SQL Server?

The "max server memory" option only applies to the buffer pool, not to memory reserved for connections.


postgres subquery uses ungrouped column I have a query in which I am trying to add a column which contains a percentage of records which have a certain value versus the total number of records. I can get the subquery to work on its own, but when I plug it into the main query it fails with an error :
ERROR:  subquery uses ungrouped column "ipam_addresses.host_id" from outer query

my query is:

select
    ipam_networks.ipam_id,
    ipam_networks.net_name,
    ipam_networks.net_cidr,
    zone,
    count(*),
    to_char(100*(select count(*) where ipam_addresses.host_id = 0) / (select count(*)), '999D99%') v
from
    ipam_addresses
left outer join
    ipam_networks
    on ipam_addresses.parent_id = ipam_networks.ipam_id
where
    ipam_addresses.ip_state != 'DHCP_FREE'
group by
    ipam_networks.ipam_id, ipam_networks.net_name, ipam_networks.net_cidr;

Why is it throwing this error even if I specify the table name in the subquery, and what can I do to fix this?

You can do that using a filtered aggregate, no need for subselects at all

to_char(100 * (count(*) filter (where ipam_addresses.host_id = 0)) / count(*)::numeric, '999D99%') v

The cast ::numeric is needed to avoid integer division


Does sql server calculate views every time when someone query the views? I wonder whether sql server calculate views every time when someone query the views. It is because everytime I query the views, it take such a long time.

And when I tried to shorten the time by selecting only top 10 rows from the view, it takes the same amount of time. I suspect sql server need to calculate the view for the whole table even if I only want a few rows from the view, am I right?

Yes, a view is just a macro (unless indexed). The behaviour is well known.

See Tony Rogerson's article. You can see it yourself in the query plans too. The view doesn't exist on the query plan because it can't exist as a macro

Also note the "Predicate Pushing" phenomenon when you filter or TOP on views


Relation to original tables or to existing linking table In my database I have a table with different settings for my app. Each setting is in relation to a guest (table guests) and an event (table events). So basically each guest has specific settings for each event he is linked to.

Every guest which has settings is are allready linked to the events for other reasons so there is an existing event_guest table with the necessary links.

So I'm not exactly sure about how I should link the settings table with the others.

Option 1

I link the settings with the table event_guest which links guests and events.

Option 2

I link the settings with the "original" tables guests and events.

Spontaneous I would go with option 1 but I'm a little bit confused about it...

My concern with option 1 is, that if I have a lot of deep relations, maybe even another table after settings, I need more complex sql queries to get for example data from settings, guests and events. Could this become a performance issue?

Which is the better solution and what are its advantages and disadvantages?

Andriy already said most of it, so I'll just be short.
After your description, settings come to existance upon the condition that a guest and an event are already linked, therefore option 1 would be choice, for that model will convey that concept to anyone viewing it.
There is some talk about surrogate vs composite keys. Anyway, Smaller PK => smaller indexes, but as in your case you'll be going with int surrogate keys for guests and events tables, then the composite key will also be small, meaning that performance loss potential against surrogate key is small, especially if you'll be searching by guest/event.
The composite key, on the other hand, will give more meaning to the key.


Is it possible to update a field value in one table based on data inserted into various other tables? Suppose I have a table representing clients. There is a field called last_active in the table which contains a time stamp.
When a client uses the database they can insert data into one of four different tables.
Is there an efficient way to update the time stamp based on the user adding/updating data to one of the other four tables?

Currently I'm using a prepared statement to update the field and I run it directly after the prepared statements that add to or modify the other tables. Is this an efficient way to do it?

Consider switching the problem around...

Have a TIMESTAMP with suitable DEFAULT in each table.  Have it automatically set on UPDATE or INSERT.

Then, during the SELECT, JOIN the 4 tables and get GREATEST(t1.ts, t2.ts, ...) to find out the latest TIMESTAMP of the 4.

(I have been bitten by TIMESTAMP DEFAULTs -- they get updated when I am not expecting it.  I would rather take control and change/set the ts in the code.)


How to force subscriber to have foreign key WITH CHECK? I know by default SQL Server replication adds WITH NOCHECK and NOT FOR REPLICATION to the foreign keys after transactional replication. But is there are way, where I can avoid these code changes made by replication. We use a compare tool and these small changes get highlighted. I have an understanding, why they are placed, but some requirements have come up in which I want to have WITH CHECKs constraint at subscriber side as well. I just want to know if somehow we can avoid these few additions and changes to articles scripts both on publisher and subscriber.

The orignal script looks like this:

ALTER TABLE [dbo].[Tablename] WITH CHECK ADD CONSTRAINT [FK__E__06] FOREIGN KEY ([eId]) REFERENCES [dbo].[table] ([Id])
GO
ALTER TABLE [dbo].[Tablename] WITH CHECK ADD CONSTRAINT [FK__E_S__05] FOREIGN KEY ([nId]) REFERENCES [dbo].[table] ([Id])
GO

Altered script at subscriber appeared as this:

ALTER TABLE [dbo].[Tablename] WITH NOCHECK ADD CONSTRAINT [FK__E__06] FOREIGN KEY ([eId]) REFERENCES [dbo].[table] ([Id]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Tablename] WITH NOCHECK ADD CONSTRAINT [FK__E_S__05] FOREIGN KEY ([nId]) REFERENCES [dbo].[table] ([Id]) NOT FOR REPLICATION
GO

I want to avoid these changes and in article properties pane, I have seleced True for contraints to be copied at subscriber

I know by default SQL Server replication adds WITH NOCHECK and NOT FOR REPLICATION

Not quite.  By default they are CHECK or NOCKECK to match the constraint on the publisher, but set to NOT FOR REPLICATION, so updates from the distribution agent do not fire the constraint.

You can choose

1) Whether or not the constraints are copied in the first place in the article properties, by setting or 0x200 flag (2nd byte, 2nd bit) in the schema_option bitmask in sp_addarticle

  0x200 Replicates foreign key constraints. If the referenced table is
  not part of a publication, all foreign key constraints on a published
  table are not replicated

2) Whether they are created with the NOT FOR REPLICATION option at the subscriber, by setting or clearing the 0x20000 flag (3rd byte, 2nd bit)

  0x20000   Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so
  that the constraints are not enforced during synchronization.

By default this nibble is 3, as the 0x10000 flag that controls check constraints is also set.  So set this nibble to 0x10000 to create FK constraints without the NOT FOR REPLICATION option and to 0x00000 to create both FK constraints and check constraints without the NOT FOR REPLICATION option.

See generally Control Behavior of Triggers and Constraints in Synchronization


How to repair, or drop/create a corrupted table in mysql? Situation: I have a MySQL database with 104 tables, running locally on XAMPP on windows 7. All the tables have InnoDB engine. XAMPP MySQL is running as windows service. As front end I have PHP (CodeIgniter).

Problem: Last night as usual I stopped working on the project and shutdown the windows normally. Today in the morning I got error table MY_TALBE does not exists in the browser. show tables on console shows the table name, but desc MY_TABLE and select * from MY_TABLE says unknown table. I tried create table MY_TABLE(...) but I had error as table MY_TABLE already exists, so I tried drop table MY_TABLE but it said Unknown table MY_TABLE! I tried mysqlcheck.exe for repair and check, they failed both, but after that, show tables does not show the table name any longer. I need to mention I did not change/move/delete/rename any of mysql files.

Question: Is it possible to recover/repair MY_TABLE, or at least how to drop and create it again (without dropping the database and re-crteating it again)?

This situation sounds a lot like a post I answered two years ago (InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage))
First, here is the InnoDB Architecture

In that post, I basically point out that the metadata of the table is still present inside the system tablespace file ibdata1.
When you ran SHOW TABLES;, all mysqld did was report the .frm files. It is possible to create the table as MyISAM because it has no attachments to ibdata1 or any other metadata. Yet, you cannot convert it to InnoDB because the metadata somehow imagines that the .frm and some tablespace_id is attached to the table name. Based on my old post, I would just mysqldump all the other tables and call you table a lost cause. However, Percona has the Data Recovery Toolkit for just such an occasion.
In the webpage about the Data Recovery Toolkit says

The Percona Data Recovery Tool for InnoDB is a free toolkit for recovering lost or corrupted data from InnoDB tables. It provides a way to extract InnoDB's data from files or raw devices in a tab-separated format when standard InnoDB recovery cannot be used for some reason such as a dropped table or severe corruption that prevents InnoDB from recognizing the data. It can be used for MySQL recovery to retrieve deleted tables, recover previous versions of updated rows, or fix InnoDB file corruption after a hardware failure such as a memory fault or bad cabling.
Percona has used these tools to perform MySQL data recovery for many customers, including extremely large datasets. Although the tools are powerful, performing InnoDB data recovery correctly requires expert knowledge of the InnoDB storage format and source code plus experience, and intuition. Percona can help with our data recovery service.

I have not used it, but if Percona made it, you should give it a try.
If you have a backup of that lost table (in the form of a mysqldump), you should carry out the steps from my old post and then load the backup of the lost table.
SUGGESTION
Once you get this situation squared away, you should tune InnoDB to flush changes to disk.
[mysqld]
innodb_fast_shutdown=0
innodb_max_dirty_pages_pct=0
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

That way, when you shutdown mysql, InnoDB can more thoroughly flush changes.
If you want even better assurance of InnoDB being flushed to disk, you should use MySQL in Linux.


Help with SQLPSX output for replication I am using the CodePlex project SQL Server PowerShell Extensions (SQLPSX) to generate script files for replication.  Everything is working as I want except for one thing.  I have looked through the PowerShell code to see if I could suppress this and could not find it.  I have tried to create secondary process to remove this item, but my PowerShell skills are not that great.

The output has this line that I do not want.

    exec [DatabaseName].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
    GO

We do not use qreader and I don't want this in my output files.

My question is two fold.
1.)  Does anyone know how I can modify the SQLPSX code to not write this output. (Preferred)
2.)  Add to PowerShell script below to loop through all output files created and remove the two lines listed above.

PowerShell script I found that loops through and generates output files.

param ($sqlServer,$path,[switch]$scriptPerPublication)
Import-Module Repl

if ($sqlServer -eq "")
{
$sqlserver = Read-Host -Prompt "Please provide a value for -sqlServer"
}

if ($path -eq "")
{
$path = Read-Host -Prompt "Please provide a value for output directory path"
}

$scriptOptions = New-ReplScriptOptions
$scriptOptions.IncludeArticles = $true
$scriptOptions.IncludePublisherSideSubscriptions = $true
$scriptOptions.IncludeCreateSnapshotAgent = $true
$scriptOptions.IncludeGo = $true
$scriptOptions.EnableReplicationDB = $true
$scriptOptions.IncludePublicationAccesses = $true
$scriptOptions.IncludeCreateLogreaderAgent = $true
$scriptOptions.IncludeCreateQueuereaderAgent = $true
$scriptOptions.IncludeSubscriberSideSubscriptions = $true

$distributor = Get-ReplServer $sqlserver

if($distributor.DistributionServer -eq $distributor.SqlServerName)
{
$distributor.DistributionPublishers | ForEach-Object {
    $distributionPublisher = $_
    if($distributionPublisher.PublisherType -eq "MSSQLSERVER")
    {
        $outPath =  "{0}\from_{1}\{2}\"  -f    $path,$distributionPublisher.Name.Replace("\","_"),$((Get-Date).toString('yyyy-MMM-dd_HHmmss'))
        New-Item $outPath -ItemType Directory | Out-Null
        Get-ReplPublication $distributionPublisher.Name | ForEach-Object {
            $publication = $_
            $fileName = "{0}{1}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", "")
            if($scriptPerPublication)
            {
                $fileName = "{0}{1}_{2}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "")
            }
            Write-Debug $("Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName)
            Get-ReplScript -rmo $publication -scriptOpts $($scriptOptions.ScriptOptions) | Out-File $fileName
        }
    }
}
}
else
{
$outPath =  "{0}\from_{1}\{2}\"  -f     $path,$distributor.SqlServerName.Replace("\","_"),$((Get-Date).toString('yyyy-MMM-dd_HHmmss'))
New-Item $outpath -ItemType Directory | Out-Null
Get-ReplPublication $distributor.SqlServerName | ForEach-Object {
    $publication = $_
    $fileName = "{0}{1}.sql" -f $outPath,$publication.DatabaseName.Replace(" ",  "")
    if($scriptPerPublication)
    {
        $fileName = "{0}{1}_{2}.sql" -f  $outPath,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "")
    }
    Write-Debug $("Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName)
    Get-ReplScript -rmo $publication -scriptOpts $($scriptOptions.ScriptOptions) | Out-File $fileName
}
}

In the $scriptingoptions collection, you are explicitly setting on the Queue reader agent export.  Change this line:

$scriptOptions.IncludeCreateQueuereaderAgent = $true

to

$scriptOptions.IncludeCreateQueuereaderAgent = $false

If you want help with the module you are using, you need to show the code you are using to generate the scripts.


How to convert .rda database to excel workbook I have downloaded a database with .rda extension, unfortunately I don't have oracle program.

How can I convert .rda database to excel?

Just because a file is names ".RDA" does not guarantee it is Oracle.
It could be Oracle, but then it would/should come with a whole infrastructure, not just a datafile.
And if it is Oracle, then you'll need Oracle to get the data out.
You may be able to use the EXPRESS EDITION depending on how official this is.

You need to speak to the 'owner' of the RDA file.
Where did it come from, how did you get it, what is  supposed to be in it?

Personally I would just use an Editor with HEX extension (Notepadd++, Textpad,...) to eyeball the contents.

Hein


SQL Server Execution Plan on server level Case: I have application which is doing search from database. That is going very slowly (more than 40 seconds). With trace I caught a query. When I run query in SSMS, it takes 2-3 seconds, but when I turn on execution plan it goes to more than 40 seconds. Application by design offers 2 different languages (collations). After investigation, I found out that when language is English (default collation) application works "as quick as possible" (6-7 seconds) but when it language is changed, obviously, SQL server doing recompile and everytime response is more than 40 seconds.

Questions: Can I somehow avoid recompile after collation changing?

SQL Server 2014.

There is some confusing info given here. For example, this does not make sense:

  I turn on execution plan it goes to more than 40 seconds.

What do you mean by "I turn on execution plan"? If you are talking about the "Include Actual Execution Plan" option in SSMS, then yes, it does take a moment longer for SSMS to prepare and draw the graphical execution plan. And it certainly takes even longer the first time this option is enabled. Did you run the query several times?

Also, you say:

  When I run query in SSMS, it takes 2-3 seconds

but then say:

  I found out that when language is English (default collation) application works "as quick as possible" (6-7 seconds)

wouldn't 2 - 3 seconds be "as quick as possible"? Or is the application adding 3 - 5 seconds for its processing?

Either way:

  when it language is changed, obviously, SQL server doing recompile and everytime response is more than 40 seconds.

No, not "obviously". A recompile would not explain a 30+ second difference per each execution. But, how are they changing the Collation? Is this dynamic SQL and they are using either nothing or COLLATE Latin1_General..., and then using COLLATE other_lang... for the other language(s)? Not sure how else one could change the Collation for a query. Still, one thing that could explain this difference), if in fact the "collation" is being changed in the way that I just described), is if the column is indexed, in which case I would guess that the column's Collation is [SQL_]Latin1_General_... and so the index is also ordered based on English. If a query is submitted that forces a different Collation by using COLLATE French_..., for example, then the query can't use the index as the rows might be in a different order.

If I am correct about what is going on (O.P. has since confirmed that the situation is as I have described here), then the only way to index the column with multiple Collations is to create non-persisted computed columns to force the Collation for each language being offered. So you can have one column be:

ALTER TABLE [dbo].[SomeTable]
  ADD [ColumnNameFrench] AS ([ColumnName] COLLATE French_100_...);

And then:

create a non-clustered index on [ColumnNameFrench]
update the app code / query so that if someone selects "English", then it selects from [ColumnName], but if the user selects "French", then it selects from [ColumnNameFrench] instead. Then it will be able to use the index on the [ColumnNameFrench].

Hopefully you don't offer too many language options, because I think 10 copies of the index might be a lot.

EXAMPLE

The table:

-- DROP TABLE #Test;
CREATE TABLE #Test
(
  [TestID] INT IDENTITY(1, 1) PRIMARY KEY,
  [SomethingEnglish] NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC,
  [SomethingFrench] AS ([SomethingEnglish] COLLATE French_100_CI_AS_SC),
  [SomethingHebrew] AS ([SomethingEnglish] COLLATE Hebrew_100_CI_AS_SC)
);

CREATE INDEX [IX_#Test_SomethingEnglish] ON #Test ([SomethingEnglish]);
CREATE INDEX [IX_#Test_SomethingFrench] ON #Test ([SomethingFrench]);
CREATE INDEX [IX_#Test_SomethingHebrew] ON #Test ([SomethingHebrew]);

Simple stored procedure:

GO
CREATE PROC dbo.Search
(
  @Search  NVARCHAR(500),
  @Language INT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'
SELECT tmp.[TestID]
FROM   #Test tmp
WHERE  tmp.[Something' +
CASE @Language
  WHEN 1 THEN N'Hebrew'
  WHEN 2 THEN N'French'
  ELSE N'English'
END + N'] = @SearchTerm;';

EXEC sp_executesql
  @SQL,
  N'SearchTerm NVARCHAR(500)',
  @SearchTerm = @Search;
GO


How can I compare the schema of two databases? Is there a way to find the differences in two SQL Server databases (schema only).  One is local and the second is at a customer's site.  We are experiencing problems with crystal reports running some reports and some code not executing and it would appear that the schemas don't match.

Can I run the same command on both databases and compare the results to tell where the differences are?

If you cannot use one of the many tools out there because of connectivity problems and want an "offline" compare, you can use SSMS to generate scripts for all database objects by right clicking on the database and using the "Tasks.../Generate Scripts" function, and make sure you select to create one file per object.

When you have done that for both databases, get the two sets of scripts onto a local machine in two separate folders and use WinMerge (or similar) to compare the two.


DROP database doesn't appear to drop all database objects I am kind of new to Postgres and am using recent version on Mac.  I'm writing a .sql script that will create a database and tables and load data.  (I need to be able to automate this, so I will be running it in a cron job, I will NOT be running it from within psql shell.)  Every time I run, I want to start from a clean slate, wiping out the database and all its objects.

When I run the below commands from bash in a .sql file with this content:

DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
CREATE TABLE progress (
foo character varying(10)
);

Then

psql -U postgres -d some_db -a -f db_etl.sql

results in:

DROP DATABASE IF EXISTS mydb;
DROP DATABASE
CREATE DATABASE mydb;
CREATE DATABASE
CREATE TABLE progress (
    foo character varying(10)
);
psql:db_etl.sql:17: ERROR:  relation "progress" already exists

How could the relation still exist if the DROP of its database was successful?  Do I need to be using fully qualified object names like my_db.public.progress?

You can't drop the database you are currently connected to.  You are connected to some_db, not mydb.  You drop mydb, then create mydb agsin, but you don't change your connection from some_db to mydb. Creating a database does not automatically change your connection to the newly created database.  So the table you created is attempted to be created in some_db, where it evidently already exists.

You can't create objects in a database you are not connected to, so fully qualifying the object name will not help.  You can use the psql meta-command \c to change your connected database in the .sql script.  Of course you have to go through authentication again.


Recovering from a stuck query I have a big DELETE query (deleting 20M rows from a 600M row table with condition according to indexed column) that is stuck - running already for 17hrs. Because it's an InnoDB table, killing it will result in a rollback, which as far as I understood will at least double the time I've waited so far. I'm trying to figure a way to recover from this situation. I don't care if the query will only update some of the rows it was supposed to, or if it will actually rollback, just that the table won't be corrupted.

The Table:

CREATE TABLE `conv_with` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id_from` int(11) NOT NULL,
 `user_id_to` int(11) NOT NULL,
 `counter` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `user_id_to` (`user_id_to`,`user_id_from`),
 KEY `from` (`user_id_from`)
) ENGINE=InnoDB AUTO_INCREMENT=643019605 DEFAULT CHARSET=utf8

The Delete Query: DELETE FROM conv_with WHERE user_id_from IN (uid1, uid2, ..., uid100000)

You should let the table rollback fully. If you kill the mysqld process, crash recovery will happen anyway.

As far as the DELETE query goes, you may want to execute it in chunks or all-in-one. I would like to suggest an alternate method for deletion. Here is a DELETE JOIN method:

Create a Table to Collect Primary Keys for Rows to DELETE from conv_with
Perform a JOIN and DELETE simultaneously

Here is the SQL for it

CREATE TABLE conv_with_delete SELECT id FROM conv_with WHERE 1=2;
ALTER TABLE conv_with_delete ADD PRIMARY KEY (id);
INSERT INTO conv_with_delete
    SELECT id FROM conv_with
    WHERE user_id_from IN (uid1, uid2, ..., uid100000);
DELETE B.* FROM conv_with_delete A INNER JOIN conv_with B USING (id);

You can either load conv_with_delete with all 20M ids, or cycle through one user_id_from  at a time. Perhaps a stored procedure might work for you.

First let's make some sample data:

DROP DATABASE IF EXISTS noam;
CREATE DATABASE noam;
USE noam
CREATE TABLE conv_load (user_id_from int not null) ENGINE=MyISAM;
INSERT INTO conv_load (user_id_from) VALUES
( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),
(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
(20),(21),(22),(23),(24),(25),(26),(27),(28),(29);
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
SELECT COUNT(1) FROM conv_load;
CREATE TABLE conv_with
(
    id int not null auto_increment,
    user_id_from int not null,
    primary key (id),
    key `from` (user_id_from)
) ENGINE=InnoDB;
INSERT INTO conv_with (user_id_from) SELECT * FROM conv_load;

Here is the creation of the sample data:

mysql> DROP DATABASE IF EXISTS noam;
Query OK, 2 rows affected (0.33 sec)

mysql> CREATE DATABASE noam;
Query OK, 1 row affected (0.00 sec)

mysql> USE noam
Database changed
mysql> CREATE TABLE conv_load (user_id_from int not null) ENGINE=MyISAM;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO conv_load (user_id_from) VALUES
    -> ( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),
    -> (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
    -> (20),(21),(22),(23),(24),(25),(26),(27),(28),(29);
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 30 rows affected (0.00 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 60 rows affected (0.00 sec)
Records: 60  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 120 rows affected (0.00 sec)
Records: 120  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 240 rows affected (0.00 sec)
Records: 240  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 480 rows affected (0.00 sec)
Records: 480  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 960 rows affected (0.00 sec)
Records: 960  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 1920 rows affected (0.00 sec)
Records: 1920  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 3840 rows affected (0.00 sec)
Records: 3840  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 7680 rows affected (0.00 sec)
Records: 7680  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 15360 rows affected (0.01 sec)
Records: 15360  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 30720 rows affected (0.01 sec)
Records: 30720  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 61440 rows affected (0.02 sec)
Records: 61440  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 122880 rows affected (0.04 sec)
Records: 122880  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 245760 rows affected (0.07 sec)
Records: 245760  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 491520 rows affected (0.15 sec)
Records: 491520  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_load (user_id_from) SELECT user_id_from FROM conv_load;
Query OK, 983040 rows affected (0.30 sec)
Records: 983040  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(1) FROM conv_load;
+----------+
| COUNT(1) |
+----------+
|  1966080 |
+----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE conv_with
    -> (
    ->     id int not null auto_increment,
    ->     user_id_from int not null,
    ->     primary key (id),
    ->     key `from` (user_id_from)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO conv_with (user_id_from) SELECT * FROM conv_load;
Query OK, 1966080 rows affected (25.41 sec)
Records: 1966080  Duplicates: 0  Warnings: 0

mysql>

OK, the table has 1,966,080 rows. Let's go about deleting anything whose user_id_from is 13 or 23.

mysql> CREATE TABLE conv_with_delete SELECT id FROM conv_with WHERE 1=2;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE conv_with_delete ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO conv_with_delete
    ->     SELECT id FROM conv_with
    ->     WHERE user_id_from IN (13,23);
Query OK, 131072 rows affected (2.39 sec)
Records: 131072  Duplicates: 0  Warnings: 0

mysql> DELETE B.* FROM conv_with_delete A INNER JOIN conv_with B USING (id);
Query OK, 131072 rows affected (8.22 sec)

mysql>

OK so the DELETE JOIN works in practice manually executed.

OK now for the Stored Procedure to Encapsulate This

DELIMITER $$

DROP PROCEDURE IF EXISTS `noam`.`BulkZap` $$
CREATE PROCEDURE `noam`.`BulkZap` (ListOfIDs VARCHAR(1024))
BEGIN

    DROP TABLE IF EXISTS conv_with_delete;
    CREATE TEMPORARY TABLE conv_with_delete SELECT id FROM conv_with WHERE 1=2;
    ALTER TABLE conv_with_delete ADD PRIMARY KEY (id);

    SET @sql = CONCAT('INSERT INTO conv_with_delete SELECT id ',
    'FROM conv_with WHERE user_id_from IN (',ListOfIDs,')');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    DELETE B.* FROM conv_with_delete A INNER JOIN conv_with B USING (id);

END $$

DELIMITER ;

Let's Test it with :

Count before the DELETE JOIN
Execute the DELETE JOIN
Count after the DELETE JOIN

Here we go:

mysql> SELECT COUNT(1) FROM conv_with;
+----------+
| COUNT(1) |
+----------+
|  1966080 |
+----------+
1 row in set (0.50 sec)

mysql> CALL BulkZap('13,23');
Query OK, 131072 rows affected (5.49 sec)

mysql> SELECT COUNT(1) FROM conv_with;
+----------+
| COUNT(1) |
+----------+
|  1835008 |
+----------+
1 row in set (0.48 sec)

mysql>

Hey, not bad. Deleted 131,072 rows in 5.49 seconds. You need to use the stored procedure  passing in a comma-separated list of used_id_from values.

Give it a Try !!!


Using stored procedures I have a task which receives an input and performs a series of tasks on the SQL server, which some depend on the content of the server and on the input. For that I use a lot of selects and then logic based on input and then insert or update. The network activity between the server and the client is high while performing those tasks because they depend on the content of the server.

Will I gain any significant boost or degrade in performance if I move this to a stored procedure while the sql server and client are on the same machine?

Yes you will get a performance boost if you do this entirely on the server as the IO will be contained entirely inside the database server.  However, at the same time you will introduce a performance hit on the server potentially.

It's a trade-off but I'd say it's worth it if you have spare capacity.


Error installing pgcrypto extension I want to install the pgcrypto extension on my postgresql installation on Windows. I found that I just have to call CREATE EXTENSION pgcrypto; but I've got an error :

Syntax error on « CREATE »
LINE 1: SELECT COUNT(*) AS total FROM (CREATE EXTENSION pgcrypto) AS...

(I'm on PostgreSQL 9.2.4 and I executed the sql line from phpPgAdmin with postgres user)

As dezso said in the comment, the problem was that CREATE can't be part of a subquery. And phpPgAdmin transform the query before executing it so it had put the CREATE as a subquery.

So the solution was to execute the CREATE query in PgAdmin for example.


Trigger firing when it shouldn't I have the following trigger:

CREATE TRIGGER SalaryCannotDecrease ON Employees
AFTER UPDATE
AS
IF EXISTS (
            SELECT *
            FROM inserted, deleted
            WHERE inserted.Salary < deleted.Salary
            )
BEGIN
    RAISERROR 30002 'You cannot decrease salary';
    ROLLBACK TRANSACTION;
    RETURN
END;

And this trigger is firing when I try to:

UPDATE Employees
SET Salary = Salary * 1.05

What am I doing wrong here?

Join Employees table's unique ID with inserted in trigger.

--- Employees table
CREATE TABLE Employees
( EmpID INT,  --- unique id for employee
  Salary INT
)

--- Dummy data insertion
INSERT INTO Employees
SELECT 11001,5000
UNION ALL
SELECT 10003,6500
UNION ALL
SELECT 10004,6900
UNION
SELECT 10006,300

-- Salary Trigger
CREATE TRIGGER SalaryCannotDecrease ON Employees
AFTER UPDATE
AS
IF EXISTS (
            SELECT *
            FROM inserted INNER JOIN deleted ON inserted.empid=deleted.empid
            WHERE inserted.Salary < deleted.Salary
            )
BEGIN
    RAISERROR 30002 'You cannot decrease salary';
    ROLLBACK TRANSACTION;
    RETURN
END;

-- Update Statement
UPDATE Employees
SET Salary = Salary * 1.05
WHERE empid=10006

--- Verification
SELECT * FROM Employees


fast bulk incrementing in MySQL I have one big table foobar describing a many-to-many-relation and containing millions of foo's, millions of bar's and every bar having several hundereds of foo's -> billions of rows.

CREATE TABLE `foobar` (
`foo_id` INT(10) UNSIGNED NOT NULL,
`bar_id` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`foo_id`, `bar_id`),
INDEX `bar_id_idx` (`bar_id`))

I have another table counting the foo_id's in foobar:

CREATE TABLE `foo_amount` (
`foo_id` INT(10) UNSIGNED NOT NULL,
`amount` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`foo_id`),
INDEX `amount_idx` (`amount`))

The counting could be done like this:

INSERT INTO foo_amount (SELECT foo_id, COUNT(*) AS amount FROM foobar GROUP BY foo_id);

But I would have to recompute the table with every inserted/deleted row in foobar.

An insert usually adds a new bar-object with several hundered foo's. For example inserting a bar with bar_id 42 having foo's with foo_id's 3, 8, 26, 44, .... would look like:

INSERT INTO foobar VALUES (3,42), (8,42), (26,42), (44,42), ...;

My second attempt was to update the foo_count table after every inserted bar object:

INSERT INTO foo_amount (SELECT foo_id, 1 FROM foobar WHERE bar_id = 42)
ON DUPLICATE KEY UPDATE amount = amount + 1;

But this is very slow. Do you have any ideas on how to optimize this? An option might be to accumulate new bar's in a temporary foo_count_tmp and merging it with foo_count every now and then. The foo_count table wouldn't be up-to-date all the time, but that's ok. But how would I trigger the updating then?

How about a GROUP BY count on foobar from scratch ???
First, insert any new data into foobar
Then, do a fresh GROUP BY count on foobar into the temp table:
CREATE TABLE foo_amount_new LIKE foo_amount;
INSERT INTO foo_amount_new
SELECT foo_id,COUNT(1)
FROM foobar WHERE bar_id = ...
GROUP BY foo_id;

Finally, swap the temp table in and drop the old foo_amount
ALTER TABLE foo_amount RENAME foo_amount_zap;
ALTER TABLE foo_amount_new RENAME foo_amount;
DROP TABLE foo_amount_zap;

However, with a table in the billions, this is an uphill battle because you have an index to rebuild. Since the following happens on every INSERT ... ON DUPLICATE KEY:

the amount would have to incremented
the amount would have to shift with in the amount index

Try removing the amount index so as to speed up INSERTs and UPDATEs.
ALTERNATE SUGGESTION
Try out your temp table solution using another method
STEP 01) CREATE TABLE foobar_new LIKE foobar;
STEP 02) Do your bulk INSERTs into foobar_new
STEP 03) CREATE TABLE foo_amount_new LIKE foo_amount;
STEP 04) Perform GROUP BY count on the latest bulk INSERT batch
INSERT INTO foo_amount_new
SELECT foo_id,COUNT(1) FROM foobar_new WHERE bar_id = ...
GROUP BY foo_id;

STEP 05) Perform a bulk INSERT into foobar from foobar_new
INSERT INTO foobar SELECT * FROM foobar_new;

STEP 06) Perform a bulk UPDATE of foo_amount from foo_amount_new
UPDATE foo_amount A INNER JOIN foo_amount_new B
USING (foo_id) SET A.amount = A.amount + B.amount;

STEP 07) Drop the temp tables
DROP TABLE foobar_new;
DROP TABLE foo_amount_new;