Calculating percentage over sub query SQL I have a table that records events regarding email campaigns. I want to figure out the percentage of campaigns where there was more than one event happening for the campaign.

First I calculated the number of events happening in each campaign:

select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email

Then I grouped the campaigns in the condition whether more than one campaign happened:

select count(counter) as occurences, IF(counter > 1, 2, 1) as grouper
         from (select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email) as counters_table
         group by grouper

Sample result:

occurences ¦ grouper
132        ¦ 1
360        ¦ 2

Now I want to calculate for each row the percentage of total occurrences. So something like this:

occurences ¦ grouper ¦ percentage
132        ¦ 1       ¦ 132/(132+360)
360        ¦ 2       ¦ 360/(132+360)

I tried this, but it does not work, it does not properly calculate the sum total:

select *, occurences/(select sum(occurences))
from (
         select count(counter) as occurences, IF(counter > 1, 2, 1) as grouper
         from (select count(*) as counter
               from campaigns_log
               where event IN ('send', 'open')
                 and campaign_id is not null
               group by campaign_id, email) as counters_table
         group by grouper
     ) as occurences_table group by occurences, grouper

Any idea where is my mistake in the last step?

Part of the secret is

SELECT  (...)/total, ...
    FROM campaigns_log
    JOIN ( SELECT SUM(...) AS total FROM ... ) x

That is, compute the total separately and make it available in the expressions.

Similarly:

SELECT @total := SUM(...) FROM ...
SELECT (...)/@total, ...


Can I use SQL Server Management Studio 2012 on a 2008 server? My school is using SQL Server 2008. Last time I used it I only installed (at home) SQL Server Management Studio 2008 part, and used VPN to connect to school's server.

Now I want to install SQL Server Management Studio again, but this time the 2012 version is available.

Will SQL Server Management Studio 2012 work on the school's SQL Server 2008? Or do I need the 2008 version?

SSMS2012 is backwards compatible:

http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/0151c2e8-2281-4a78-86b0-20cc1bfd57ac

Edit
Giving out Aaron's link instead of the old one! Thanks Aaron.

microsoft.com/en-us/download/details.aspx?id=35579

As Kenneth mentioned, you could copy the 2008 BOL (Books online) so when you search the help it'll be relevant to 2008 instead of 2012.


postgresql: grouping with json columns, eliminating nulls I have a postgres query that returns a result set simplified as follows, with an id column and two json columns.

my_id     |  col_a       |  col_b
(integer) |  (json)      |  (json)
----------------------------------------
 5001        ["a", "b"]     <NULL>
 5001        <NULL>         ["c", "d"]

My question is: what is the best way in postgres to aggregate this result? e.g.:

my_id     |  col_a       |  col_b
(integer) |  (json)      |  (json)
----------------------------------------
 5001        ["a", "b"]     ["c", "d"]

Unless I don't know your actual query, you can JOIN rows where col_a is not null with rows where col_b is not null.

create table test(my_id int, col_a json, col_b json);
insert into test values
(5001, '["a", "b"]', null),
(5001, null, '["c", "d"]');

    select t1.my_id, t1.col_a, t2.col_b
    from test t1
    inner join (select my_id, col_b
                from test
                where col_b is not null) t2
    on t1.my_id = t2.my_id
    where t1.col_a is not null;

my_id | col_a      | col_b
----: | :--------- | :---------
 5001 | ["a", "b"] | ["c", "d"]

dbfiddle here


Query to send mail to multiple users with their corresponding tickets I have a table which has a list of ticket owners and and tickets which are assigned to them which are of high priority.

For example:

I need to send mail from SQL Server using sp_send_dbmail to John with all the tickets assigned to him in a single mail. This is a huge table so I want send mails to users with their assigned tickets in single mail instead of sending one mail per ticket. Any help would be appreciated.

Below is how you do it ...

Create table and add processed column

    create table dbo.Tickets (Ticket bigint, [Owner] varchar(50))
go
insert into dbo.Tickets
select 14675, 'John'
union all
select 38759, 'John'
union all
select 45879,'Catheline'
union all
select 23980, 'Mark'
union all
select 16754, 'John'

--- add a processed column so that next time the db mail job runs, it wont send out dupe emails
alter table dbo.Tickets add processed bit not null default(0)
go

Use below query

-- query to get comma seperated values
select [Owner], Tickets = stuff ((select  N', '+ convert(nvarchar(max),innerT.Ticket)
                                    from dbo.Tickets innerT
                                    where innerT.[Owner] = outerT.[Owner]
                                    group by innerT.Ticket -- get rid of dupes
                                    order by innerT.Ticket -- just in case you want order
                                FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
from dbo.Tickets outerT
group by [Owner]

Use above query to send HTML email using database mail.
Update the dbo.Tickets table and set processed = 1 for the owners that you emailed the tickets.

Note: you can use case statements based on the owners and email them seperately.

This is a pseudo code that will help you 80% in what you want to achieve.


Azure SQL Data Migration Assistant is Stuck on "Migrate Data" I am in the process of using the Data Migration Assistant to move a local DB to Aszure SQL.  The schema transfer went well, and 243 of the 246 tables all were able to transfer their data with no errors.

However, the transfer has been running for 13+ hours, and over the last three hours, three of the tables have not budged on their percent complete.  One is at 92.3%, one is at 93.9%, and the last is at 98.5%.

Some questions:

At this point, can I safely assume that this is a failed transfer, at least of these three tables?
I don't see a "stop" button.  If I wanted to, how would I abort this process?
If I were to abort the process, is there some way to manually finish the transfer of data from just these three tables?

A Microsoft Engineer examined the logs you shared with us and found the following error:

  Dma.exe Information: 0 : 'Exception type: System.IO.IOException
      Message:
          There is not enough space on the disk.

It seems like the workstation that DMA is running on doesn’t have enough disk space to do schema extract.

The following is the explanation the Microsoft engineer shared with us about why DMA requires disk space:

  The Extract service connects to a database, reads all of its objects
  and their properties, and then creates an in-memory model of the
  database. Similar to how the Build service validates the defined
  objects, the Extract service also checks for consistency and
  containment. The validation done here will also result in failures if
  you have a view that refers to a non-existent table same as with
  T-SQL! Additionally, unsupported or uncontained objects are blocked
  because these objects are not yet allowed in SQL Azure or the DAC
  in-memory model. Finally, once the in-memory model is complete and
  validated, a DACPAC is written to disk. Looking at the stack it’s a
  well-known Windows exception on not enough disk space.


Below a complete solution for sql-server.

I was trying analytical functions, but didn't succeed. I'm tempted to repost the question to SO requiring a solution using analytical functions. I think there must be one.

And here is my working solution. I'm proud not to have used cursors.

set language us_english
Set Nocount ON

create table #t( PROJ_NBR char(4),  STATUS char(2), START_DT datetime);

insert into #t values ('AL20',     'AC', '1/14/2010 4:31');
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');
insert into #t values ('AL20',     'DE', '1/14/2010 4:34');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'AC', '3/4/2010 4:31');
insert into #t values ('AL20',     'AC', '3/4/2010 4:39');
insert into #t values ('AL20',     'AC', '3/21/2010 13:24');
insert into #t values ('AL20',     'AC', '2/4/2011 13:54');
insert into #t values ('AL20',     'AC', '2/4/2011 14:14');
insert into #t values ('AL20',     'AC', '2/4/2011 14:27');
insert into #t values ('AL20',     'DE', '2/11/2011 16:24');

select
    distinct PROJ_NBR, STATUS, [start], [end]
from(
select
*,
(select MIN(START_DT)
    from #t t1
    where t.STATUS = t1.STATUS
    and t.START_DT >= t1.START_DT
    and not exists ( select * from #t t2
                    where t.STATUS <> t2.STATUS
                    and  t.START_DT >= t2.START_DT
                    and t1.START_DT <= t2.START_DT)
) [start],
(select Max(START_DT)
    from #t t1
    where t.STATUS = t1.STATUS
    and t.START_DT <= t1.START_DT
    and not exists ( select * from #t t2
                    where t.STATUS <> t2.STATUS
                    and  t.START_DT <= t2.START_DT
                    and t1.START_DT >= t2.START_DT)
) [end]

from #t t
) t1
order by [start]

I think besides column and table names this can easily adapted to Oracle too.


How To Migrate From MSDE 2000 to SQL Server 2008R2 Express We need to migrate a MSDE 2000 database running on a Windows Server 2003 Standard to a Windows Server 2008 R2 running SQL Server 2008 R2 Express (fresh install). The database is small, about 300MB.

Since Management Studio is not installed on the source server (Windows Server 2003), can we just stop the database instance via services and then copy the .MDF and .LDF files to the Windows 2008 R2 Server and attach the database using Management Studio?

Relying on the service to shut down gracefully and detach your database files cleanly is risky. I have often seen this result in corrupted files that can not be used elsewhere, and which fail to start up even when starting back up the source instance which corrupted them. Moving the files after detaching them poses a similar risk - if they get damaged in or after transit, what are you going to fall back to?

If you don't have a backup of the database, either approach could result in disaster, since you are left with zero copies of your database.

If you do have a backup of your database, copy that, and restore it. If the backup isn't recent enough, create a new one. This is a much safer approach because your original database is still intact. If something goes wrong with the file during transit, or there are issues trying to restore it on the destination server, you can always start over - having lost nothing but time.


SSRS Line Graph formatting Environment: Windows 8.1, SQL Server 2008 R2 Express database, SQL Server 2008 R2 BIDS
Database: Performance counter data collected on client servers. The particular values involved in this question are % Processor Time and Batch Request/sec.

I did not start this report from scratch. I am actually just modifying one Allen White provided from his TechEd 2010 session that you can find here. I am using the same table structure with a few modifications to column names and such for my liking, pretty much the same other than that. I also added some filter/parameters into the report because I have multiple servers in this database.

I take the RDL file and import it into my SSRS project. Modify the procedures and column names to match my setup. I noted the format of the graph to be a simple line graph:

The border for each series is set to a solid line style with 2pt line width, no shadow. I don't see why I don't get a pretty line versus this. Is there some format setting I am missing? It has been sometime since I have tried setting up graphs in BIDS.

Always enjoy getting help from this site... Thanks to @billinkc.

I had the thought in the back of my mind that maybe the graph had to much data to display but guess it never clicked.

I modified the graph's Category Groups to be an expression for grouping by the hour not just the full date value: =Hour(Fields!CapturedTime.Value)

That allowed the graph to be prettified into this:


mysqld is generating a lot of disk IO writes. How can I figure out why? I have a Ubuntu 14.04 server running half a dozen wordpress sites. Total traffic to the server is on the order of 30k page views a day.

Over the past couple months, with page views and network traffic roughly flat, my disk I/O has been trending up significantly. It has doubled over the past month. When I run iotop, I can see that mysqld (v. 5.5) is constantly writing to disk at a rate of around 5MB per minute. I can't think of anything happening on any of the sites that would generate so much writing.

I have spent a lot of time reading mysql tuning tutorials and adjusting variables (mostly to do with tmp table size, and query cache parameters) and nothing I have tried has made any difference. My current guess is that there is probably a bad query in one of the wordpress plugins in use. But there are a lot of plugins spread over half a dozen wordpress installs and I don't know where to look.

Is there some way that I can pinpoint what exactly is causing mysqld to be writing to the disk so much?

UPDATE : This question was explored in better detail and answered thoroughly here.

Take a look at the mysql processlist. mysql -p -e "show processlist". Or even a "show full processlist". It will give you a list of currently running queries.


Find all ancestor nodes of a HierarchyId using a single SQL statement? I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID.  Every solution I've seen using HierarchyID seems to use either a CTE or a variable.  Is there a way to do this using a single select statement?

To make things simpler:

CREATE TABLE Employee
(
    EmpId INT PRIMARY KEY IDENTITY,
    EmpName VARCHAR(100) NOT NULL,
    Position HierarchyID NOT NULL
)

INSERT INTO Employee (EmpName, Position)
VALUES ('CEO', '/'),
    ('COO', '/1/'),
    ('CIO', '/2/'),
    ('CFO', '/3/'),
    ('VP Financing', '/3/1/'),
    ('Accounts Receivable', '/3/1/1/'),
    ('Accountant 1', '/3/1/1/1/'),
    ('Accountant 2', '/3/1/1/2/'),
    ('Accountant 3', '/3/1/1/3/'),
    ('Accounts Payable', '/3/1/2/'),
    ('Accountant 4', '/3/1/2/1/'),
    ('Accountant 5', '/3/1/2/2/'),
    ('DBA', '/2/1/'),
    ('VP of Operations', '/1/1/')

To get "all parent nodes of a given node":

select *, position.GetAncestor(1), position.GetAncestor(1).ToString()
from employee
where position=hierarchyid::Parse('/3/1/')

EmpId  EmpName       Position  (No column name)  (No column name)
5      VP Financing  0x7AC0    0x78              /3/

but there will only ever be one due to the nature of hierarchies.

If you really want to get all immediate children nodes of a given node:

select *
from employee
where position.IsDescendantOf(hierarchyid::Parse('/3/1/'))=1
      and position.GetLevel()=hierarchyid::Parse('/3/1/').GetLevel()+1

EmpId  EmpName              Position
6      Accounts Receivable  0x7AD6
10     Accounts Payable     0x7ADA

-- EDIT

I see that you want all ancestor nodes. Perhaps try an approach like this:

select *
from employee
where hierarchyid::Parse('/3/1/2/1/').IsDescendantOf(Position) = 1

or

select * from employee
where ( select position
        from employee
        where empname='Accountant 4' ).IsDescendantOf(Position) = 1

here is a CTE method for comparison:

with w as ( select * from employee where empname='Accountant 4'
            union all
            select e.*
            from employee e join w on(w.position.GetAncestor(1)=e.Position) )
select * from w;


Fast general method to calculate percentiles I want to find n>1 percentiles of an unsorted column in PostgreSQL. For example the 20th, 40th, 60th, 80th and 100th percentile.

An obvious solution is to count and sort the column and then do a look but I'm hoping for a better solution. Any ideas?

P.S. I have found a good solution for MySQL but can't translate it to psql

I have come up with the following:

select cume, max(var) AS max_var
from (
   select var
        , ntile(5) over (order by var) as cume
   from table
   ) as tmp
group by cume
order by cume;

It selects the maximum of each group that is divided using ntile().


barcode inventory design - transaction or master? I am building a inventory software for mobile phone retailers. There are several things confuse me.

Ok, we have:

Product
Product Attributes
Barcode
Stock In Batch (Purchases/Return, etc)
Stock Out Batch (Samples/Sales, etc)
Stock Transfer Batch (Transfer stock from A to B warehouse)

I plan to do a transaction table for stock in/out/transfer.
So my product & attributes & barcode table should not have a qty field, right?

Below is my business rules

    1 product, 0 or many attributes;
    1 attributes, 1 to many product

    1 product, 0 to many barcode;
    1 barcode, 1 product

    1 attributes, 0 to many barcode;
    1 barcode, 1 attributes

    1 barcode, 0 to many quantity

How many table should I created for this?

My table design is like this:

    - warehouse (warehouse id[pk])
    - product (product id[pk])
    - attributes (attribute id[pk])
    - product attributes (product id[pfk], attribute id[pfk])
    - barcode (barcode id[pk])

    - stock in batch (sib_id[pk], warehouse id[fk])
    - stock in batch item (item_id[pk], sib_id[fk], product id[fk], qty, unit cost)
    - stock in batch item attributes (item_id[pfk], attribute id[pfk])
    - stock in batch item barcode (item_id[pfk], barcode id[pfk])

    - stock out batch (sob_id[pk], warehouse id[fk])
    - stock out batch item (item_id[pk], sob_id[fk], product id[fk], qty, unit cost)
    - stock out batch item attributes (item_id[pfk], attribute id[pfk])
    - stock out batch item barcode (item_id[pfk], barcode id[pfk])

    - stock transfer batch (stb_id[pk], from warehouse id[fk], to warehouse id[fk])
    - stock transfer batch item (item_id[pk], stb_id[fk], product id[fk], qty, unit cost)
    - stock transfer batch item attributes (item_id[pfk], attribute id[pfk])
    - stock transfer batch item barcode (item_id[pfk], barcode id[pfk])

Should I have qty field?
Where should I put qty?

I am thinking of using sum() in the stock in and and minus() stock out transaction to get product quantity, but after thinking I am going too complicated and stuck at here.

I also thinking to use qty field, for every transaction it will auto adjust the qty field, but afraid that may be the final qty doesn't tally with the transaction calculation.

Please advise.

Your sum idea works to a point, and then you will end up with a slow transaction whenever you add or remove items from stock due to scalability issues.

The way I did it for a similar problem, was to maintain a running total table, which would work in your case.

I had an audit table as well for accounting purposes, which had original values in case the accountants got twitchy, but the running total option is more scalable.


Ubuntu / MySQL / Percona XtraBackup gives error "sh: 1: xtrabackup_55: not found" Hello I installed Ubuntu 12.04.2 LTS on a fresh box.

This installed MySQL 5.5.29.

I installed percona-xtrabackup-2.0.6-521.tar.gz, and moved the executables to /usr/bin/

But innobackupex is giving an error: "sh: 1: xtrabackup_55: not found"

david@server1:/usr/bin$ sudo innobackupex --user=root --password=123 /home/david/dbb/
[sudo] password for david:

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Ireland Ltd 2009-2012.  All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

130401 12:17:04  innobackupex: Starting mysql with options:  --password=xxxxxxxx --user='root' --unbuffered --
130401 12:17:04  innobackupex: Connected to database with mysql child process (pid=1488)
130401 12:17:10  innobackupex: Connection to database server closed
IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

innobackupex: Using mysql  Ver 14.14 Distrib 5.5.29, for debian-linux-gnu (x86_64) using readline 6.2
innobackupex: Using mysql server version Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

sh: 1: xtrabackup_55: not found
innobackupex: fatal error: no 'mysqld' group in MySQL options

The files are present and executable:

david@server1:/usr/bin$ ls -l inno*
-rwxr-xr-x 1 root  root  108823 Mar 19 14:24 innobackupex
lrwxrwxrwx 1 david david     12 Mar 19 14:24 innobackupex-1.5.1 -> innobackupex
-rwxr-xr-x 1 root  root   10096 Jan 16 17:24 innochecksum
-rwxr-xr-x 1 root  root  400192 Jan 16 17:24 innotop
david@server1:/usr/bin$
david@server1:/usr/bin$ ls -l xtra*
-rwxr-xr-x 1 root root  9799993 Mar 19 14:21 xtrabackup
-rwxr-xr-x 1 root root  8361704 Mar 19 14:24 xtrabackup_51
-rwxr-xr-x 1 root root 12699861 Mar 19 14:15 xtrabackup_55
david@server1:/usr/bin$

Path is correct:

david@server1:/usr/bin$ echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games

I get the same results if I run

innobackupex
./innobackupex
sudo innobackupex
sudo ./innobackupex

Any ideas?

Check your server bit if it is 32 bit and if you try to install 64bit package percona's xtrabackup . It would end up throwing such error messages.


Transaction Log Shipping across multiple SQL Server versions I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server.

What I'm trying to do is to maintain a reporting (read only) version of a production transactional SQL Server 2005 database (180 Gb) on on a SQL Server 2012 server (and in the case of other clients 2008 R2). I can do simple back up and restore at any time but to improve latency between refreshes of the database, I'd like to see if I can do something like transaction log shipping.

If TLS is not available in this scenario what do the panel suggest as an alternative strategy.

I'm lead to believe that TLS is only appropriate/supported across the same versions of SQL Server.

Let me tell you transaction log shipping is nothing but trasfer of log records from one server to another its quite light and you can create your own 'logshipping' by creating backup,copy and restore jobs. What you are trying to do is one way possible may be using TSQL. You are making SQL Server(SS) 2005 as primary and SS2012/2008R2 as secondary this can be achived but think of a scenario where 2005 database server fails due to any reason and you bring SS2012/2008R2 online all is fine now BUT would you be able to bring SS 2005 online with updated data from SS2012/2008R2, you cannot because higher version backups cannot be restored on lower version database. So this would lead to data loss which I guess you dont want. So I would advise you not to go for this scenario.

I would say upgrade your SQL Server 2005 to 2008 R2 or SQL Server 2012 this is the best thing to do. In all cases of High availability version mismatch can cause you problem. Having said that if this is immediately not possible, as per RTO,RPO, schedule a proper backup of SQL Server 2005 database. Of course configuring logshipping with 2005 as secondary is also good.

EDIT: After User asked below

  I want to avoid large backup and restore cycles which will be left to weekends and overnight if I can't achieve this.

Well in such case you have differential backups a very nice way to lower down RTO. This will surely decrease your total time taken to restore all backups after disaster. The restore sequence would be full backup followed by LATEST differential backup followed by all log backups in sequence taken after latest differential backup. Suppose you do daily full backup at 12 AM diff backup every 4 hr and every 1 hr log backup. Lets say database faied at 3:50 PM. You would restore

1.Full backup at 12 AM.

2.Differential backup at 12 PM

3.Log backup taken after 12 PM differential backup ( 1PM, 2PM and 3PM log backups) I dont think it would be big deal.

This is minimum you have to do Logshipping is not a solution here. You can create your own script to automate the restore process.

There are file system backups which can also do this job, they take file system backup of mdf anf ldf file and after disaster you just need to attach those mdf and ldf files but poiint in time recovery is not possible with file system backups


Can I see the RAID group name for my SQL Server Database files? Is it possible to see the RAID group name that my database (and log) files are sitting on, in one of the DMVs (or some other system table) ?

SQL Server doesn't have any access to anything that low level.  Typically Windows can't even see the RAID group information given that each hardware vendor stores that differently and typically exposing that information to the OS doesn't have any benefit.  You'll need to work with your hardware team to make the drive labels match, but even then that isn't exposed within SQL Server.  You would need to use PowerShell or WMI to pull that information from the OS.


How do I kill a non-running impdp job? I am trying to run an import operation into an Oracle 11G R2 database from a .dmp file using SQL Developer 4.1's Data Pump Import Wizard.  I set everything up with IMMEDIATE start, but then the job first said it started executing and then said it is NOT RUNNING.  The job's log file was not created, so I think it stopped upon starting, perhaps due to a bad parameter.  How can I just delete this job altogether, so that I can start the wizard again without creating problems with a duplicate job?

I typed select * from SYS.DBA_DATAPUMP_JOBS; and found the job as the only one listed:  JOB_NAME='IMPORT_JOB_SQLDEV_46'

And I google searched on the topic, but the only articles were many years old and did not give instructions which seem to work in modern SQL Developer.  For example, I tried the steps in this link, but Ctrl-C does not seem to produce anything.

You can drop export/import job using the following command
First of all find the name of the job table under 'DataPump>Import Jobs or Export Jobs'.

Use the drop command to drop that table by the user who has created that import/export job. In my case it was created by SYSTEM user.

SQL>conn system/password
SQL>DROP TABLE EXPORT_JOB_SQLDEV_41;
Table dropped.


How to match string in varchar field from one table with other table? I have two tables,

table1 having a single column:

id     |   sentence(varchar 255)
1      |   I am a boy; a good boy.

and table2 having two columns:

word    |   meaning
I       |     مين
am      |     هون
a       |     أيك
boy     |     لرقة
good    |     اشا

Now I want to query both tables in such a way that I will get each word with its translation.

For the sample data the query result must be:

sentence                  |    word_meaning (virtual column)
I am a boy; a good boy.   |  {"I":"مين", "am":"هون", "a":"أيك", "boy":"لرقة", "good":"اشا"}

As you can see in word_meaning column, each word has its meaning with json.

What client language are you using?  That is where you need to split a sentence into "words" and where you will need to put the output back together after fetching the individual meanings.  Do not try to do it in SQL.


Like predicate to match a whole word only I have a SQLite database with a table named minecraft.

+----+----------------------+
| id |         name         |
+----+----------------------+
|  1 | Pocket Mine MP       |
|  2 | Open Computers       |
|  3 | hubot minecraft skin |
|  4 | Terasology           |
|  5 | msm                  |
+----+----------------------+

I need to find all the records which have 'e' and 'o' in their 'name' field. Here is my Select query:

select * from minecraft where name like '%e%o%'

Here is the result of the above query:

+----+----------------+
| id |      name      |
+----+----------------+
|  2 | Open Computers |
|  4 | Terasology     |
+----+----------------+

The problem is that the Like predicate matches the entire value, not words. The row with id = 2 should not be matched, because all the criteria didn't happen in a single word ('e' is found in the first word and 'o' in the other word): Open Computers.

How should I change my Select query so it matches the only row with 'e' and 'o' in a single word?

+----+------------+
| id |    name    |
+----+------------+
|  4 | Terasology |
+----+------------+

Thanks

Your best bet is probably to use REGEXP, which needs some special installation prior to being usable.

In that case, you'd use:

WHERE
    name REGEXP 'e\w*o'

This regular expression means: (a literal 'e') followed by (zero or more word characters) followed by (a literal 'o').

This approach won't shy away from cases like 'This hello will work'

References:

How do I use regex in a SQLite query?
Online Regular Expression tester - Regex101.com

Alternative:

If you can't install the required packages to have REGEXP available, you can probably use a user-defined function to perform the "word-by-word" LIKE check. Let's imagine it's called check_words_like(sentence, like_predicate), I'd use in the following fashion:

SELECT
    whatever
FROM
    (SELECT
        name, whatever
    FROM
        tbl
    WHERE
        name like '%e%o%'
    ) AS q1
WHERE
    check_words_like(q1.name, '%e%o%') ;

This still requires some C programming and interfacing, and might be as complicated as getting the right REGEXP package.


MySQL Tuner returns: MySQL's maximum memory usage is dangerously high Can someone help me optimize my MSQL configuration.

I run Zabbix and Grafana on a Ubuntu 18.4. MySQL Tuner shows me that my memory usage is dangerously high, but when I use the free command it shows that I have more than enough memory.

Here is the full mysqltuner rapport: MySQLTuner

Here is the output of: SHOW GLOBAL VARIABLES and SHOW GLOBAL STATUS
and SHOW FULL PROCESSLIST

And the output of: SHOW ENGINE INNODB STATUS since Zabbix uses excursively innodb tables.

The output of the top command
And the htop command
I also have the output of innotop: Google Drive

And the output of ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 31652
max locked memory       (kbytes, -l) 16384
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 31652
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

and free -h

              total        used        free      shared  buff/cache   available
Mem:           7,8G        7,0G        146M         37M        689M        554M
Swap:          2,0G        417M        1,6G

How can I increase the mysql memory usage? How can I optimize my MySQL configuration, so that I have maximum CPU and Memory usage?

Thank you for any help!

RAM consumption for mysql/mariadb (and probably for other engines) can be represented this way:

+----+----------------+-------------------+-----------------------------+
| OS | Other services | DB common caching |      DB clients buffers     |
|    |                |                   +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|    |                |                   | | | | | | | | | | | | | | | |
+----+----------------+-------------------+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Common DB caching is a sum of key_buffer and InnoDB_buffer_pool values.
Buffer size per each client is a sum of read_buffer, read_rnd_buffer, join_buffer and sort_buffer. This amount is multiplied by max_connections value. Both values together shouldn't be bigger than reasonable part of the whole RAM. Some RAM is required by host's OS itself and some amount of RAM should be leaved for 3d-party services on the host.

Maximum possible memory usage: 9.0G (116.34% of installed RAM) means that you have dedicated to the mysql service even more RAM than your host physically has installed. This doesn't mean some problems until you'll get too many simultaneous connections to the DB. And even then you'll meet a slowdown first because of swapping.

If you want to tune your mysql perfectly you should know how many connections can be established at the same time. This value is shown here:

Highest usage of available connections: 40% (61/151)

Then you have to decide how much RAM each client needs. This value depend on your DB data and queries. If your DB isn't very critical you can start from some reasonable values and then decrease them until the overall performance goes down significantly. Then increase them 2x. But in general 1-2MB for each client's buffer and 4-8MB in total is good enough.
Now multiply total client's buffers by max_connections value. By default it is 151 but you can change it to the 1.2-1.5x of the real peak number of connections.

Say, for max 61 actual connections I'll reserve max_connections = 80 with 8MB buffers per connection. That mean that I reserved 80x8=640MB of RAM.

Now I leave 1GB for OS itself and 512MB for other services. The remainder is:

8GB - 1GB - 512MB - 640MB = 6016MB

Let's round it to the 6GB. This RAM you can split between MyISAM key_buffer and innodb_buffer_pool. If you have no MyISAM tables in the DB you can set key_buffer_size = 256k because mariadb 10.1.x is still used MyISAM/ARIA for internals. And all the rest could be dedicated to the innodb caching: innodb_buffer_pool_size = 6G

Now you'll get something like that:

Total buffers: 6.G global + 8M per thread (80 max threads)
Maximum possible memory usage: 6.7G (84% of installed RAM)


What is SQL/PSM and how does it differ from other versions of SQL? I have searched SQL/PSM and I have learned that it stands for 'Structured Query Language/Persistent Stored Modules', but what I want to understand is what that really is and how it differs from T-SQL and any other versions of SQL?

PSM is just an addition to the standard SQL language. It's the part that adds procedural extensions: IF, WHILE, ... all structures that are also met in other procedural languages.

From Wikipedia, about SQL/PSM:
"SQL/PSM stands for Structured Query Language/Persistent Stored Modules, and was developed by the American National Standards Institute (ANSI) as an extension to SQL.1 It was first adopted in 1996,[2] and it provides procedural programmability in addition to the querying commands of SQL.

The SQL/PSM extension is defined by ISO/IEC 9075-4:2003. SQL/PSM standardizes procedural extensions for SQL, including flow of control, condition handling, statement condition signals and resignals, cursors and local variables, and assignment of expressions to variables and parameters. In addition, SQL/PSM formalizes declaration and maintenance of persistent database language routines (e.g., "stored procedures")."

In SQL Server the native implementation is called T-SQL stored procedures and triggers, in Oracle it's PL-SQL.


Multi-row insert vs transactional single row inserts On Postgres (>10) is there any performance gain on using multi-row insert:

insert into tbl (c1, c2) values
(v1, v2)
(v3, v4);

over:

begin;
insert into tbl (c1, c2) values (v1, v2);
insert into tbl (c1, c2) values (v3, v4);
commit;

I've shamelessly copied the code snippet from the below question, but this question is different as it is about transactions Multi-row insert vs multiple single row inserts

Yes, it can make a huge difference.

For example, I did a single-statement insert with one million value lists, and it took 7 seconds.  I did one million single-row inserts all in one transaction, and it took 110 seconds.  That is a 15-fold difference.

This is for a case where the table had no constraints or indexes.  Having a single index narrowed the gap down to only be 10 times faster, by slowing both approaches down by few seconds.  Once the index is large enough that it can't be cached in RAM, then both methods will get so much slower that the difference between them may become relatively insignificant.

Note that the accepted answer to the related question you linked is not correct.  Index maintenance is done the same way whether it is a multi-valued insert or a bunch of single-valued inserts--at least for BTree indexes.  There has been talk of optimizing the index maintenance, but if the optimizations are implemented they will probably be done in such a way that it applies to both types of INSERT.


Where is SQLCMD.EXE in SQL Server 2014 LocalDB or how to install it? On a machine with SQL Server LocalDB 2014 installed I am looking for SQLCMD.EXE file but cannot find it anywhere. Is there a way how to install it separately?

Unfortunately I am not local admin of the machine, so I prefer no MSI...

Found this link https://www.microsoft.com/en-us/download/confirmation.aspx?id=36433, but cannot use MSI...  Perhaps copying executable files and dlls can do the trick.

After copying files SQLCMD.EXE, BATCHPARSER.DLL and SQLCMD.RLL from C:\Program Files\Microsoft SQL Server\110\Tools\Binn  from another computer with SSMS installed I still get the error:

  Microsoft SQL Server Native Client 11.0 : Data source name not found
  and no default driver specified.

running sqlcmd -S np:\\.\pipe\LOCALDB#F8040C8F\tsql\query.

I have figured out that during SQL Server LocalDB MSI installation the ODBC driver is not installed

So to use SQLCMD it is necessary to install
Microsoft® ODBC Driver 11 for SQL Server® - it is a part of ODBC driver as per here.

When I tried to install Microsoft® Command Line Utilities 11 for SQL Server® it prompted me to install the ODBC Driver first.

Then it is possible either to copy the files SQLCMD.EXE, BATCHPARSER.DLL and SQLCMD.RLL or to install Microsoft® Command Line Utilities 11 for SQL Server®.


One-To-One relation is this a good practice? I have a table "Place" (id_place, name, address,coor_x, coor_y)

I would like to add for a place their "facebook page" "twitter page" "wesbite" and telephone

My first idea was to create a table let's say social_contact that contains these fields and add an extra column in the place table (social_contact_id) which would have been a 1 to 0..1 relation (these 4 fields are optional and could be blank)

Is this good practice? Or Am I better to stuff these 4 new files directly in the place table?

thank you!

I hope I don't get marked down for this simplistic answer, but I think the answer is "it depends".

Are you always going to join the tables? Bad
Does your main table have ONLY columns that are fixed-width (not VARCHAR
or TEXT) and you are going to separate the variable columns from the
fixed columns? Good (unless you are joining on every query)

AT least in the MySQL world (and it would make sense to be true everywhere), a table with ONLY fixed-width columns are faster to find, so for the main table to be fixed width and putting the less-frequently needed and variable-width columns in another table...it can be a good design for speed.

On the other hand, if this is never going to be a big table, and you're usually going to grab the extra data anyway...this may just be increasing your maintenance cost for no reason...so might be best to keep it simple.

[UPDATE 2015-02-13]

At 20 fields, your table is still tiny. But, in your defense, I wish someone had thought to split some of the tables that I work with because 80% of the data (a couple hundred columns) aren't needed on every query, and we probably could have made the main table fixed-width if we had put some forethought into it. So, it's a good thing to always ask the question of whether you should split. But, at the same time try to keep YAGNI in mind so that you don't split tables when you don't need to.

If you ever expand your table to have a separate 'resume' for your users, and then a 'personal profile', and a complex mapping of user's 'view settings'...each of these might have a lot of data on their own, and each would be loaded completely separately. The resume only loads when people are viewing it, and the same with the personal profile, and the 'view settings' only load when the user is navigating the site. So, these would make 3 logically separate tables because you would query them at separate times and never together. Splitting the table isn't wrong, you just have to ask if it's worthwhile.


RIGHT() function returning blank result I have a single column that contains information that I need to have split up into three separate columns. I currently have a SELECT statement that works partially, but my ChangeTo column is not returning any data and I cannot figure for the life of me why. Here is what the current column being returned looks like:

| bp_comment                                             |
----------------------------------------------------------
| changed status from "new" to "in progress"             |
| changed ORT status from "in progress" to "Code Review" |
| changed MileStone from "1.1" to "1.2"                  |

Here are how I need the results to return:

| ChangeType              |  ChangeFrom  |   ChangeTo  |
-----------------------------------------------------
| changed status from     | new          | in progress |
| changed ORT status from | in progress  | Code Review |
| changed MileStone from  | 1.1          | 1.2         |

Here are how the results are returning right now:

| ChangeType              |  ChangeFrom  |   ChangeTo  |
-----------------------------------------------------
| changed status from     | new          |             |
| changed ORT status from | in progress  |             |
| changed MileStone from  | 1.1          |             |

Here is the query I am using:

SELECT
    LEFT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5) AS 'ChangeType'
    ,REPLACE(SUBSTRING(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5, CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - 5), '"', '') AS 'ChangeFrom'
    ,REPLACE(RIGHT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' to ', REVERSE(CONVERT(VARCHAR(MAX), bp.bp_comment)))), '"', '') AS 'ChangeTo'
FROM
    bug_posts bp

Thank you in advance!

The REVERSE is clearing our your string, not entirely sure why.

EDIT: Here's why. In this sequence the string ' to ' is actually ' ot '. (Ken sniped it mid edit!)

Fixed:

SELECT
LEFT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5) AS 'ChangeType'
,REPLACE(SUBSTRING(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) + 5, CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - CHARINDEX(' from ', CONVERT(VARCHAR(MAX), bp.bp_comment)) - 5), '"', '') AS 'ChangeFrom'
,REPLACE(RIGHT(CONVERT(VARCHAR(MAX), bp.bp_comment), CHARINDEX(' ot ', REVERSE(CONVERT(VARCHAR(MAX), bp.bp_comment)))), '"', '') AS 'ChangeTo'
FROM
bug_posts bp

Here's an alternative approach.

SELECT LEFT( CONVERT( varchar( max ) , bp.bp_comment ) , CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                   + 5 )AS 'ChangeType' ,
   REPLACE( SUBSTRING( CONVERT( varchar( max ) , bp.bp_comment ) , CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                 + 5 , CHARINDEX( ' to ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                     - CHARINDEX( ' from ' , CONVERT( varchar( max ) , bp.bp_comment ))
                                                                     - 5 ) , '"' , '' )AS 'ChangeFrom' ,
   REPLACE(REPLACE(RIGHT(bp.bp_comment, LEN(bp.bp_comment) - CHARINDEX(' to ', CONVERT(VARCHAR(MAX), bp.bp_comment))), '"', ''), 'to ', '') AS 'ChangeTo'
 FROM bug_posts bp;


Full text search on multiple tables in PostgreSQL I have several tables that have related data. I want to have a search input box on the dashboard of the system that will search for anything (client, invoice, delivery note, ...).

Is it possible to have a FTS implementation in PostgreSQL that will get the item searched for in more than one table and get the results?

For example, if the user typed 45, the query should look in the clients table and return the clients with id 45 or name has 45, also look in the invoices table and return the invoices with number 45, and also look into the delivery notes table and return the delivery notes with number 45.

Or is there a better way of solving this problem without using full text search?

General idea of Full-Text Search is to provide more functionality for naitive language searches. it allows using stop words (words, that do not contribute to subject of the search), search for synonims, specify distance between words etc.

To accomplish,  what you need a procedure with several unions is enough. Design layout of the output first. Than do a query for each table you need to search in. If you need to show value found, and number af columns varies too much, then have several queries to same table returining different column of interes.

If column data types cannot be converted implicitly, then explicitly convert them to text in SELECT and WHERE.

CREATE FUNCTION find_everything(searchTerm TEXT)
RETURNS RETURNS TABLE(
   ID integer,
   Description text,
   Source_table text) AS
$$

Declare
    searchIn text;
BEGIN
    searchIn = '%' || searchTerm || '%';

    For results in
      SELECT
        Client_Id as ID,
        Name as Description,
        'Clients Table' as Source_Table
      FROM Clients
      WHERE Client_Id ILIKE searchIn OR Name ILIKE searchIn

      UNION ALL

      SELECT
        Invoice_Id as ID,
        Invoice_Description as Description,
        'Invoices Table' as Source_Table
      FROM Invioces
      WHERE  Invoice_Id ILIKE searchIn OR Invoice_Description ILIKE searchIn

      UNION ALL
      ...

      return next results;
    end loop;
END;
$$ language plpgsql;

P.S. Sorry if there are errors in code. It is to show the idea.


Count number of writes to different tables I have recently setup Continuous Archiving for Point-in-Time Recovery in a postgresql DB, but the archive is growing at an unexpectedly high pace (tens of gigabytes per day). The DB is a couple of GB, most of it static. It seems to me that we may have some part of the application which is writing a bit too aggressively to the database, possibly updating large number of rows frequently, causing needless diskload.

Is there some way to either ask the DB, or to analyze the WAL files, to find out which tables most writes (row delete, add or updates) are made to, preferably counted in bytes? That would be a big help in figuring out the source of the disk load.

This information is available through the Statistics Collector.

The information you are looking for is stored in pg_stat_bgwriter or pg_stat_database. Maybe you are also interested in the values from pg_stat_all_tables that shows how many tuples have been inserted, updated or deleted.

Pay attention to the configuration properties listed in the section Statistics Collection Configuration

All those views show values since the instances has been started or since the last time pg_stat_reset() has been called.

If you want to monitor the change over time, you need to sample those views and store the values somewhere (e.g. a text file or just another table that has a timestamp to indicate when the sample was taken).


Best way to determine if a MySQL InnoDB table is in its own file, not in system tablespace I have a MySQL development server that uses InnoDB tables in some of the databases. It was originally setup with the defaults so all data went into the system tablespace (e.g the main ibdata1 file). Along the way, I enabled innodb_file_per_table so new database tables get into their own files.

I now want to reclaim disk space from dropping some development databases. I know I have to mysqldump and re-import those databases that are in the system tablespace. What is the best way to determine which ones to focus on?

I don't see anything obvious in the information_schema database. I could manually poke around the server with an SSH shell, but I was looking for a more automated or elegant way to do that.

In some older databases, I only see .frm files in the database directory, in newer ones I see .frm and .ibd files. That seems to be the key to know where the data is ...

Sanity Check: Is it even possible to mysqldump half the databases to reclaim space, or do you really have to do every database? There are 54 databases inside this server that use InnoDB tables.

Note: Using MySQL v5.1 on UNIX.

Not relevant to readers using very old versions of MySQL, but on more recent versions of MySQL you can use:
SELECT i.name FROM information_schema.INNODB_SYS_TABLES i WHERE i.space = 0;
to see which tables are in the shared table space and use:
SELECT i.name FROM information_schema.INNODB_SYS_TABLES i WHERE i.space <> 0;
to see which ones are in their own files.


Will {fn concat} work without issue in MySQL, SQL Server and Oracle? According to this (quite old) article the principle behind ODBC escape sequences is that

  The ODBC driver reads the escape sequence and translates it into the
  DBMS-specific syntax before sending the query to the database

However if I try the following

SQL Server

CREATE PROCEDURE foo_bar
AS
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

My SQL

DELIMITER $$

CREATE PROCEDURE foo_bar()
BEGIN
  SELECT  {fn concat ('foo', 'bar')};
END

The escape sequences remain untranslated in the object definition and calling the routines works fine. That indicates to me that the RDBMSs themselves natively understand the syntax rather than relying on translation so will work independent of connection method. Is that understanding correct and if so does this also apply to Oracle?

This seems to work only through a JDBC connection. Your SELECT works with Oracle, PostgreSQL and DB2 when using a Java/JDBC based query tool.

Using the native tools this statement throws an error:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> SELECT  {fn concat ('foo', 'bar')} from dual;
SELECT  {fn concat ('foo', 'bar')} from dual
        *
ERROR at line 1:
ORA-00911: invalid character

The same is true for PostgreSQL:

psql (9.1.1)
Type "help" for help.

postgres=> SELECT  {fn concat ('foo', 'bar')};
ERROR:  syntax error at or near "{"
LINE 1: SELECT  {fn concat ('foo', 'bar')};

and DB2:

(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 9.7.0

db2 => SELECT  {fn concat ('foo', 'bar')} from sysibm.sysdummy1;
SQL0104N  An unexpected token "SELECT  {" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".
SQLSTATE=42601


mysql trigger not working with if statement CREATE TRIGGER updateNewEmp
AFTER UPDATE ON BasicDetails
FOR EACH ROW  BEGIN
    if(NEW.MiddleName<>OLD.MiddleName) then
      insert into BasicDetails_audit(MiddleName,`State`,Country,Community)
      VALUES(NEW.MiddleName,NEW.`State`,NEW.Country,NEW.Community)
end if;
end

It throws a syntax error near the END IF statement. Can anyone please help?

You have to change DELIMITER to something else than ;

DELIMITER $$
CREATE TRIGGER updateNewEmp AFTER UPDATE ON BasicDetails FOR EACH ROW
BEGIN
  IF(NEW.MiddleName<>OLD.MiddleName)
  THEN
    INSERT INTO BasicDetails_audit(MiddleName,`State`,Country,Community)
         VALUES (NEW.MiddleName,NEW.`State`,NEW.Country,NEW.Community);
  END IF;
END$$
DELIMITER ;


MySQL TEXT Disk Space I have a plain text file with 3 million articles divided with \n.
The file is 58G

When I'm adding the articles to an InnoDB table one per row

INT id
TEXT article_content

the .ibd file of this table (innodb_file_per_table=1) takes up 98G

Can someone explain if such increase in disk space of a table comparing to a text file is normal ? Is there a way to store such content more efficiently ?

If the table is that simple (INT and TEXT), you cannot change the design any more.
Suppose your table is called mydb.myarticles. If you run this TableSize Query,
SELECT
    FORMAT(data_length,2)  / POWER(1024,1) DataPagesKB,
    FORMAT(index_length,2) / POWER(1024,1) IndexPagesKB,
    FORMAT(data_length,2)  / POWER(1024,2) DataPagesMB,
    FORMAT(index_length,2) / POWER(1024,2) IndexPagesMB,
    FORMAT(data_length,2)  / POWER(1024,3) DataPagesGB,
    FORMAT(index_length,2) / POWER(1024,3) IndexPagesGB,
    (data_length+index_length) DataAndIndexesInBytes
FROM information_schema.tables
WHERE table_schema='mydb' and table_name='myarticles'\G

You will see how much space the data pages are taking up. If you feel DataPagesGB is significantly lower than 98GB, then run:
OPTIMIZE TABLE mydb.myarticles;

which will actually execute
ALTER TABLE mydb.myarticles ENGINE=InnoDB;
ANALYZE TABLE mydb.myarticles;

This will shrink the table as much as possible. Then, run the TableSize Query again. Chances are, you may not get much shrinkage.
Keep this in mind: The default size of an InnoDB Page is 16KB (InnoDB_page_size). Any TEXT fields over 16KB will cause InnoDB to split the row up into two or more pages. In other words

Text field > 32 KB may need up to 3 pages for the one row
Text field > 48 KB may need up to 4 pages for the one row
...
Text field > 16n KB may need up to (n+1) pages for the one row

At least one of those pages will have lots of wasted space.
CAVEAT
I would strongly advise against changing the InnoDB_page_size for compression purposes because I wrote a post back on Mar 02, 2012 on how InnoDB creates additional wasted space in the Buffer Pool changing to the Barracuda Format, requiring a much larger Buffer Pool. This would be in addition to the chaining of the increased number of InnoDB pages.
Your Original Question

Can someone explain if such increase in disk space of a table comparing to a text file is normal ? Is there a way to store such content more efficiently ?

Yes, it is normal for such an increase especially if many rows have TEXT values bigger than 16KB. If you add any additional indexes (such a FULLTEXT index or a prefix index), you will bloat the Index Pages.
You can estimate the bookkeeping info by subtracting DataAndIndexesInBytes from the size of the .ibd and dividing that difference by 3 million. That answer will give you the bookkeeping info needed per row.
SUGGESTIONS

Use a different character set and collation (One byte per character)
Limit the characters of the TEXT fields to the most commonly used characters
Avoid using multibyte characters
Use TEXT field lengths less than 16KB


After piping a file with SQL statements to mysql, how can I keep the command line session open? A file, in.sql, contains a CREATE TEMPORARY TABLE statement followed by some INSERTs that populate the above table. Temporary tables are purged after the session closes, so 'mysql -u user -p pass < in.sql' has no net effect once the mysql process is done with in.sql and terminates. I would like to keep the session open to continue working on the temporary table interactively. Does such an option exist?
(A simple workaround, of course, would be to create a non-temporary table and drop it when I'm done with it)

Start an interactive shell, then just do:

source in.sql

... at the mysql prompt. The session will still be open after the file is executed, unless the file has a command in it that causes the mysql shell to exit.


Postgresql WAL Archiving, How to do it properly? Basically I have one (for now) mission critical postgresql database that I will need up to the minute recovery for, I've never implemented a working WAL archival before so I would like to know what is the best way to do this.

The machine we are going to backup to is remote, and we have ssh keys set up, so I was going to have the WAL's rsync'd over. Is that wise with that archive_command option?

A Step by Step guide with examples on how to set these things up would be amazing as I couldn't find one I understood (I'm not a DBA)

Using pgBarman, This was really easy as it walked me through it all, plus it's open source! I stumbed upon this while googling
link to pgBarman tutorial (pdf)
link to pgBarman site


When I convert the linked tables to local tables I got this error? Referential integrity cannot be enforced between linked tables and local tables.

Enforcement of referential integrity will be dropped on all relationships between tables converted to local and tables that remain linked, then I get the options ok cancel and help

I clicked ok and it does nothing.

I am trying to make them all Local so I can edit them without the main database that everyone else is using to be changed in any way.

Foreign keys can't be connected to non-local objects - they have to reference local tables.


Split datetime records that fall between 10pm to 7am into new record I am very new to SQL Server. I am trying to split time in time out records that fall between night shifts from a employee shifts table but I am having a difficult time doing so. Night Shift runs from 10pm to 7am.

Example below might help make things clearer.

E.g

EmployeeId   InTime                     OutTime
----------   -----------------------    -----------------------
1            2018-08-10 21:00:00.000   2018-08-11 09:00:00.000

Desired Output

    EmployeeId   InTime                     OutTime                   Type
    ----------  -----------------------    -----------------------   -------
    1            2018-08-10 21:00:00.000    2018-08-10 22:00:00.000   day
    1            2018-08-10 22:00:00.000    2018-08-11 07:00:00.000   night
    1            2018-08-11 07:00:00.000    2018-08-11 09:00:00.000   day

code for current table:

DECLARE @tbl TABLE
    (
        EmployeeId INT,
        InTime DATETIME,
        OutTime DATETIME
    )

INSERT INTO @tbl(Employee_Id,Shift_Start,Shift_end) VALUES (1,'2018-08-10 21:00:00.000','2018-08-11 09:00:00.000')

Try using a recursive Common Table Expression (CTE) to generate a list of hours between the minimum and maximum date/times. Then you can use DENSE_RANK to group the records.

Example:

WITH
source_data
AS
(
    SELECT tbl.* FROM (VALUES
      ( 1, '10-Aug-2018 21:00', '10-Aug-2018 22:00')
    , ( 2, '10-Aug-2018 20:00', '11-Aug-2018 07:00')
    , ( 3, '11-Aug-2018 07:00', '11-Aug-2018 09:00')
    , ( 4, '10-Aug-2018 20:00', '11-Aug-2018 09:00')
    ) tbl ([EmployeeId], [InTime], [OutTime])
)
,
source_data_dates
AS
(
    SELECT [InTimeMin] = MIN([InTime]), [OutTimeMax] = MAX([OutTime]) FROM source_data
)
,
source_data_times([n], [InTime])
AS
(
    SELECT 1, CAST([InTimeMin] AS DATETIME) FROM source_data_dates
    UNION ALL
    SELECT n + 1, [InTimeMin] = CAST(DATEADD(hh, n, [InTimeMin]) AS DATETIME)  FROM source_data_times, source_data_dates WHERE n <= DATEDIFF(hh, [InTimeMin], [OutTimeMax])
)
,
source_data_times_type
AS
(
    SELECT
          [InTime] = [InTime]
        , [OutTime] = DATEADD(hh, 1, [InTime])
        , [Type] =
            CASE
                WHEN CAST([InTime] AS TIME) >= '22:00:00' OR CAST([InTime] AS TIME) < '07:00:00'
                THEN 'night'
                ELSE 'day'
            END
    FROM
        source_data_times
)
,
source_data_times_type_rank
AS
(
    SELECT
          sd.[EmployeeId]
        , tt.[InTime]
        , tt.[OutTime]
        , tt.[Type]
        , [GroupRank] = DENSE_RANK() OVER (PARTITION BY sd.[EmployeeId] ORDER BY tt.[Type], CAST(tt.[InTime] AS DATE))
    FROM
        source_data AS sd
        INNER JOIN source_data_times_type AS tt ON tt.[InTime] >= sd.[InTime] AND tt.[InTime] < sd.[OutTime]
)
SELECT
      [EmployeeId]
    , [InTime] = MIN([InTime])
    , [OutTime] = MAX([OutTime])
    , [Type]
FROM
    source_data_times_type_rank
GROUP BY
      [EmployeeId]
    , [Type]
    , [GroupRank]
ORDER BY
      [EmployeeId]
    , MIN([InTime])

Results:


Animals hierarchy use-case: multiple farms, multiple species Let's talk about an online service where goat/sheep/cattle/llama farmers would register and keep track of their animals.

One of the features of such a service is tracking parent/child relationship. However, it's not as trivial as adding a parent_id field. Here are the reasons why:

There are many farmers, each with their own account; they're selling animals to each other. This means that not every parent can be entered into the system with all its properties. So there is no parent_id primary key to link to.
There are two IDs, national and internal. None of them, one of them or both of them can be entered. There should be a priority in determining the animal's parent. Internal IDs have priorities. Then comes national IDs. This means that if both are specified, the parent should be determined by an internal ID.
Not all animal cards are made public by their owners. This means that we cannot rely national IDs and look for parents through the whole database and fetch parents' data to display the tree.
Furthermore, the National ID is a NOT a unique field among the database (but is unique for each user and species). This is made so to allow farmer1 enter national ID "ABC" even if farmer2 entered national ID "ABC" for another animal as well (e.g. by mistake).

As you can see, this all makes impossible to rely on a single parent_id column. That's where come various approaches to solve the issue, and I ask your advice.

Approach #1

Introduced the following columns:

mom_national_id (nullable)
mom_internal_id (nullable)
dad_national_id (nullable)
dad_internal_id (nullable)

Unique indexes (user_id, species_id, mom/dad_national_internal_id)

Approach #2

mom_id BIGINT UNSIGNED (nullable)
is_mom_id_national BOOL
dad_id BIGINT UNSIGNED (nullable)
is_dad_id_national BIGINT UNSIGNED (nullable)

So, here are my questions

Q1. Is either of the approaches above correct, or can you recommend a better one?

Q2. If both of them correct, which one would be better in terms of querying speeds for building parent/child trees? Does it matter at all if all is needed is just up to ~50 generations up or down?

Q3. Which indexes are needed for best performance?

Despite the solution should not be specific to database type, the service is being built with using PostgreSQL.

The best approach would be neither of the two options you've proposed.  Instead you should create your own proprietary surrogate key using either a monotonically increasing integer or a GUID if that is how you like to do these things.  This key column would be NOT NULL on your animal table (obviously).

You need to keep all of the external IDs that are provided to you (internal/national) and use these to establish the mother_id and father_id of each new animal that you add to your database according to the complex rules that you've described.

This approach allows your system to take advantage of declarative referential integrity and ANSI SQL joining while preserving the mixed bag of external IDs that are provided to you for reference/reporting etc.


MySQL Performance Issues Over the past 3 weeks we have been having bottle-necking issues at our MySQL server. I have been using MonYOG to watch the processlist in an attempt to get a grasp on the issues. We are aware that some of the queries and processes we are running in our code is less than optimized, but I am not completely convinced that is the main source of our issues. I feel our server should be able to overcome these issues.

Our tables are a mix of innodb and myISAM. I am by no means a DBA and do not pretend to be one so I am not sure what engine is the best in our current environment. We are pretty read heavy, but mix in a great deal of updates and inserts as well. I was seeing a lot of locked tables which made me think innodb might be better since it does row lock instead of table lock. I converted a few of our tables from MyISAM over to innodb to take advantage of some of the settings that are available. The developers are using a great deal of complex joins as well.

Here is what we are running in our my.cnf:

[mysqld]
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock
user=mysql

key_buffer_size=512M

innodb_file_per_table
innodb_buffer_pool_size=6GB
#the following line is causing some odd errors when doing db dump
#innodb_log_file_size=128M
innodb_log_buffer_size=8M
innodb_additional_mem_pool_size=32M

max_allowed_packet=16M
join_buffer_size=8M
sort_buffer_size=8M
max_connections=500
wait_timeout=500
skip-name-resolve
thread_cache=256
table_cache=256
tmp_table_size=48M
max_heap_table_size=48M

query_cache_size=64M

#logging of slow queries
log-slow-queries=/var/log/mysql-slow-query.log

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

I changed the current "innodb_buffer_pool_size" to 6GB last week and that made a huge improvement to the bottle-necking. We have actually only really started seeing issues again today. Traffic has increased this week since we have schools starting and football season starting as well.

I have all kinds of statistical information I can supply you if needed. I am not sure what else to supply at this time.

If I cannot get this figured out I will be forced to call in a consultant. I am hoping someone will be able to give me a hand with some of these settings.

Thanks in advance.

------------------ EDIT--------------------

Here is some additional information requested:

MySQL Version - 5.0.95
OS version - CentOS 5.6 (will be upgrading to 6.4 in the coming weeks)

Server - Dell PowerEdge R710
CPU - Dual Quad Core 2.8GHz
RAM - 12GB
This is a dedicated machine. The only thing running on this box is MySQL

The table locks on MyISAM can be a killer and migrating to InnoDB is probably one of the best things you can do to continue to improve scalability.  Of course, your change to innodb_buffer_pool_size won't impact tables that aren't InnoDB.

One problem, however, is that the version of InnoDB in MySQL 5.0 is still quite primitive compared to later releases, especially when it comes to multicore machines and internal scaling.  This is discussed at length in High-Performance MySQL, which was last updated a little over a year ago but is still quite useful even though MySQL 5.6 was discussed in a mix of present- and future-tense due to its release status at the time.  I don't have any affiliation with the book or its authors, I just think it's a great reference.  If you don't have it, I'd recommend it, because it goes into a lot of detail about what to do, what not to do, and why.

But, if the system under consideration were my system, I'd be planning to upgrade to MySQL 5.5, at a minimum, and possibly MySQL 5.6, because of the significant improvements in the internals of InnoDB as well as elsewhere.

Looking at your config, the query cache is always something to consider when you're looking at performance issues.  It could be that a larger cache would help (perhaps 128M to 256M) but it's also possible that a smaller or disabled query cache might be beneficial, since it does represent a global choke-point that every SELECT query has to pass through.  The appropriate setting is almost entirely workload-specific.

Nothing jumps out at me in your configuration as being particularly sub-optimal, but I would add that if you've been tempted to use any of the tuning "scripts" you find online... try to resist that temptation.  "Tune" only what you have a specific reason to tune, and only one parameter at a time.  After you successfully upgrade, try to remove as much of the customized values as possible (except innodb_buffer_pool_size) and let the behavior of the new version with its default values dictate what needs to be tweaked.

The officially recommended path when upgrading across versions is always to do a full mysqldump from the old, and restore on the new installation, though it is possible to do a "binary" upgrade where you simply start the new version code against the old version's datadir and do mysql_upgrade.  The official path would be to go from 5.0 to 5.1, and then from 5.1 to 5.5.

http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-previous-series.html
http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html

There's a lot to digest but the bottom line is that 5.0 is at end-of-life and hasn't had so much as a bugfix release in over a year... and the newer versions represent a substantially-improved product that should not require major changes in your application.


Avoiding a full re-initialization when making changes to transactional replication When I use the GUI to make any changes to transactional replication on SQL server, I live in fear of the "the changes you have made require a reinitialization of all articles" dialog.

I have had this happen even when doing something simple like adding or removing individual tables.

How do I avoid this?

Are there replication settings that impact when a full reinitialization is required? I found this blog post that claims that publication properties @immediate_sync and @allow_anonymous should be set to 0, I've done that but I've still sometimes had it prompt to reinitialize the whole thing.

Should I be scripting the add/remove instead of using the GUI?

Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier?

I've done that but I've still sometimes had it prompt to reinitialize the whole thing.

The snapshot will only contain the new table that has been added.

  Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier?

Script out replication - right click - script to file. Change the parameters @immediate_sync and @allow_anonymous should be set to 0.

Run the script post completion of your work.

Also, check my answer for Transactional replication altering tables and adding stored procedures that will help when you are adding columns to existing replication.


MySQL 5.5 vs 5.6 with WordPress We have a moderately busy WordPress blog that runs currently on MySQL 5.5, and I'm wondering if I would experience any significant performance upgrades by moving it to MySQL 5.6?  Anyone know?

I'd also appreciate any thoughts on using MyISAM over InnoDB for the engine. Any benefits or drawbacks with respect to WordPress?

Let's do a comparison
MyISAM Caching vs InnoDB Caching
MyISAM only caches index pages from the .MYI files into a global buffer called the MyISAM keycache (sized by key_buffer_size). MyISAM Data does not cache data global. It only does so per DB Session (sized by read_buffer_size and read_rnd_buffer_size)
InnoDB has a very elaborate framework for managing data and index pages cached in memory (Pictorial Representation of InnoDB by Percona CTO Vadim Tkachenko).

The memory side of the framework caches data and index pages in the InnoDB Buffer Pool (sized by innodb_buffer_pool_size).
See my earlier posts on this

Apr 22, 2011 : Wordpress Database Slow - should I switch to InnoDB?
Apr 14, 2011 : What are the main differences between InnoDB and MyISAM?
Dec 22, 2011 : How does Wordpress handle MySQL row lock errors?

Table Write Behavior
MyISAM performs a full table lock with each DDL and and DML statement. InnoDB allows multiple transactions to access and update an InnoDB table. To increase write throughput for many transactions, you would need to increase the log buffer size of InnoDB (sized by innodb_log_buffer_size)
Read Speed
There are rare occasions where MyISAM can be faster to read than InnoDB. Such an occasion would be a high read/low write. For high read/high write systems, my money would be on InnoDB. Please read my answer to the May 03, 2012 post Which is faster, InnoDB or MyISAM?
CPU Utilization
Only InnoDB can take advantage of tuning for more CPU engagement

Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

MySQL 5.5 vs MySQL 5.6
While I could write many things about this, you are better off reading What's New in MySQL 5.6 to see those differences. You will even better off reading What Is New in MySQL 5.7 and going to MySQL 5.7 instead.


Why doesn't simple recovery model support log backup or tail log backup? Lets say there is a crash then we can use the full backup and a tail log backup - because if there was checkpoint then data would already be written to the database data file and flushed from the transaction log. Where as if there was no checkpoint yet then logs will still be there in the transaction log file. So is recovery possible in this recovery model?

The logs written between the FULL backups and the crash will be overwritten at some point
They are not saved with BACKUP LOG.

My bold describing Simple

  Every transaction is still written to the transaction log, but once
  the transaction is complete and the data has been written to the data
  file the space that was used in the transaction log file is now
  re-usable by new transactions.

From SQL Server docs

  Automatically reclaims log space to keep space requirements small,
  essentially eliminating the need to manage the transaction log space.

In theory, a crash happening just after a full backup that has just a few transactions might work, if the LSNs match.

In practice, this is almost never guaranted to happen so for consistency (and false hope) better to disallow this in simple recovery model


Error while taking backup in SQL Server Before few month box of SQL has been crashed. We have almost database back with some tools and running application smoothly on it. But when i try to take backup of database at that time it gives error as below,

  The log scan number (3533468:412:0) passed to log scan in database '****' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup. (Microsoft SQL Server, Error: 9003)

Would anyone help me to out of this?

I have even try to copy database but i am failed.

Thanks in Advance.

First, why did you use these tools instead of simply restoring from a backup? My guess is, you didn't have a backup, or you didn't have one that was recent enough to be usable. Okay, we'll deal with that later.

In the meantime, if I were in your shoes, I would update my resume and sprinkle fairy dust all over it create a new, empty database, and start moving all of your tables, objects, users and code over to the new database (much of that can be done with wizards built right into Management Studio, or with 3rd party tools like Red Gate's SQL Compare / SQL Data Compare). Once that's done, you can drop the Frankenstein database you somehow managed to get running (not sure whether "some tools" deserve credit or not - I don't think they did you any big favors at all). Once that's dropped you can rename the new database, and everything should be back to normal.

While it is possible that you may fix whatever problem your current database has with something like DBCC CHECKDB ... WITH REPAIR_ALLOW_DATA_LOSS, that error message you're getting sounds a lot more heinous than a few bad pages. I would get your data out of those files as quickly as possible.

Next, and I mean immediately next, establish a sensible backup and recovery strategy. This doesn't mean just taking backups, but taking backups and regularly testing that you can restore them. This question isn't about corruption or recovering from a crashed database, but the answers there certainly detail the ins and outs of the different recovery models. Typically, taking a nightly or weekly full backup is not enough - you want differential or log backups in between to minimize the potential for data loss in the event of a server crash. And just taking backups, as I've said, isn't enough - you need to validate that the backups you are taking can be restored on different hardware - you know, should your current hardware completely melt down.

After that, contact the vendor of these "tools" and ask for your money back. This is not the way to recover data, and they have obviously done some irrecoverable things to your database that require even more manual work on your part. It's a shame you didn't ask a question about what to do when the crash originally happened, because surely folks here would have been able to guide you on more reliable approaches to recovery. I find that typically people who use these tools that promise complete recovery of a suspect or corrupt database, without having any idea what you might have done to it, only try them because of a misguided and/or panicked Google search. These vendors are great at SEO, but not so great at actually delivering on their far-fetched promises.


SQL output formatting I want this:

"ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID"
"abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos"

But I get this:

'"'||"ACCOUNT_ID"||'"'||','||'"'||"MAJOR_VERSION"||'"'||','||'"'||"MINOR_VERSION"||'"'||','||'"'||"COUNTRY"||'"'||','||'"'||"ACCEPTANCE_TIMESTAMP"||'"'||','||'"'||"AGREEMENT_ID"||'"'
"abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos"

Using the following SQL:

select '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN' and rownum < 10;

How can I get the first output ?

I am going to go out on a limb here and try and answer even though you haven't provided a lot of details.

It looks like you want to create a comma-separated list of the columns and then the values below it but in sqlplus you are getting the column header as the actual SQL string.  If I were to use the following in a SQL Fiddle I will get the same result:

select
  '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN'
and rownum < 10;

See Demo.  And the result is two rows, one with the column name but in this case it is the actual sql that we ran and then the row of data comma separated:

| '"'||"ACCOUNT_ID"||'"'||','||'"'||"MAJOR_VERSION"||'"'||','||'"'||"MINOR_VERSION"||'"'||','||'"'||"COUNTRY"||'"'||','||'"'||"ACCEPTANCE_TIMESTAMP"||'"'||','||'"'||"AGREEMENT_ID"||'"' |
|                                                                                "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" |

Now if I want to create the column headers as a row, I would alter the query to use a UNION ALL, the first query just creates a string of the column headers:

select
  '"ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID"' mycol
from dual
union all
select
  '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN'
and rownum < 10;

See Demo. This gives me a result:

|                                                                                MYCOL |
| "ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID" |
|             "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" |


Recursive CTE performance Need help with recursive CTE performance. Below CTE is running very slow as it is trying to pull heirarchical
data recusively. Table is big with every root id having upto 3 recursive itemid. There could be around 200000 or more root id's.
I know recursive CTE's are slow for huge dataset as for every rootid in anchor it would itemid recursively.

Schema :

Create table RootItem (ItemId int primary key, RootIt int , insertdate datetime)

Above table have more than 1 million rows.

CTE query :

; With rootcte as

( select itemid from RootItem where rootid is null

union all

  select r.itemid as RootId , i.itemid from RootItem i join rootcte r
    on i.rootid = r.itemid
)

We can't modify table schema and use heirarchyid. I tried while loop too but that is slow too.

Is there any other way I can optimize this query ?

 ; With rootcte as

( select itemid from RootItem where rootid is null

 union all

 select r.itemid as RootId , i.itemid from RootItem i join rootcte r
 on i.rootid = r.itemid
)
  SELECT
     Cust.CustomerID
    , Cust.BusinessName
    , sCust.RegionCustomerID
    , ord.OrderID
    , ord.OrderItemID
    , prd.ProductCode
    , rc.itemid
    , rc.rootid
    , mf.FileID
FROM
    vw_Customer Cust
    INNER JOIN SrcCustomer scust ON Cust.CustomerID = sCust.RegionCustomerID
    INNER JOIN OrderItem ord ON Cust.MasterCustomerID = ord.MasterCustomerID
    INNER JOIN Product ON ord.ProductID = Product.ProductID
    INNER JOIN rootcte rc ON ord.RootOrderId = rc.Rootid
    INNER JOIN MFolder mf ON mf.mfolderid = rc.itemid
    INNER JOIN MVersion mv ON mv.mfolderversionid = mf.mfolderid
    WHERE ord.IsActive = 1  and product.IsSelling = 1 and mf.fileid in (23,45,29)
     and mv.isdeleted = 'N'

I am also working with BI group to change query logic and filter data in cte itself of move couple of joins and criteria to cte.. Thanks for all the comments.

You say that the hierarchy gets modified.  Presumably while this operation is running, there is some amount of blocking which is taking place then?

Even if the hierarchy is changing, are the roots for items changing?

Have you looked at the time it would take to just make the mapping table from root to item and index it?

I would like to see the execution plan to see what is happening - the CTE should get spooled, but as a manually materialized and indexed table it might perform better in the later steps.

Even with heavy activity, it would seem to me that someone has to be blocked if DML operations are changing data which this process is reading.

So I'd strongly consider taking a snapshot of the hierarchy.

In addition, you have a number of other INNER JOINs - you should review whether it is, in fact, the CTEs at all and whether there are any indexes which are missing to make those joins effective.  The execution plan should tell you that.

You appear to have quite a few things in the WHERE clause which might help reduce some operations (and determine which indexes might be the best)), but it's hard to tell without looking at the execution plan or the indexes.


Why isn't Postgres doing an index-only scan? I am using Postgres 9.4. This is my table:

 id                | integer                 | not null default nextval('frontend_prescription_id_seq'::regclass)
 presentation_code | character varying(15)   | not null
 total_items       | integer                 | not null
 actual_cost       | double precision        | not null
 quantity          | double precision        | not null
 processing_date   | date                    | not null
 pct_id            | character varying(3)    | not null
Indexes:
    "frontend_prescription_pkey" PRIMARY KEY, btree (id)
    "frontend_prescription_5283da53" btree (pct_id)
    "frontend_prescription_528f368c" btree (processing_date)
    "frontend_prescription_cost_by_pres_code" btree (presentation_code, pct_id, actual_cost)

There are about 450m rows in the table. I have just run VACUUM ANALYZE, so the query planner should be up to date.

Here is my query:

EXPLAIN (ANALYZE, BUFFERS)
  SELECT SUM(actual_cost) AS cost,
    pct_id AS id, processing_date
  FROM frontend_prescription
  WHERE presentation_code='0501013B0AAAAAA'
  GROUP BY pct_id, processing_date;

And here is the output:

 HashAggregate  (cost=1537066.69..1537066.87 rows=18 width=16) (actual time=189604.446..189608.532 rows=9725 loops=1)
   Group Key: pct_id, processing_date
   Buffers: shared hit=17 read=418783
   ->  Bitmap Heap Scan on frontend_prescription  (cost=14473.73..1533678.60 rows=451746 width=16) (actual time=543.825..187857.510 rows=416328 loops=1)
         Recheck Cond: ((presentation_code)::text = '0501013B0AAAAAA'::text)
         Heap Blocks: exact=416287
         Buffers: shared hit=17 read=418783
         ->  Bitmap Index Scan on frontend_prescription_cost_by_pres_code  (cost=0.00..14360.79 rows=451746 width=0) (actual time=317.102..317.102 rows=416328 loops=1)
               Index Cond: ((presentation_code)::text = '0501013B0AAAAAA'::text)
               Buffers: shared hit=2 read=2511
 Planning time: 0.329 ms
 Execution time: 189610.440 ms

Why does Postgres need to do a heap scan? Isn't all the information needed for the query in the composite frontend_prescription_cost_by_pres_code index?

Readable version of the output: http://explain.depesz.com/s/oV4

processing_date is not in the index, so it cannot use an index only scan.

Create the index on (presentation_code, pct_id, processing_date, actual_cost) so that it can use an index only scan, and in that order so that it can efficiently use the sort order of the index to do the grouping.


What function quotes an identifier in dynamic-sql with SQL Server? What is the SQL Server method of safe-quoting identifiers for dynamic sql generation.

MySQL has quote_identifier
PostgreSQL has quote_ident

How do I ensure given a dynamically generated column name for a dynamically generated statement that the column itself isn't a SQL-injection attack.

Let's say I have a SQL Statement,

SELECT [$col] FROM table;

which is essentially the same as

'SELECT [' + $col + '] FROM table;'

What stops an injection attack where

$col = "name] FROM sys.objects; \r\n DROP TABLE my.accounts; \r\n\ --";

Resulting in

SELECT [name] FROM sys.objects;
DROP TABLE my.accounts;
-- ] FROM table;

The function that you're looking for is QUOTENAME!

Through the practical use of square bracket technology, you can safely encapsulate strings to aid in the prevention of hot SQL injection attacks.

Note that just sticking square brackets around something does not safely quote it out, though you can avoid your code erroring with invalid characters in object names.

Good code

DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql = 'SELECT ' + QUOTENAME(d.name) + ' FROM your_mom'
FROM sys.databases AS d

Bad code

DECLARE @sql NVARCHAR(MAX) = N''
SELECT @sql = 'SELECT [' + d.name + '] FROM your_mom'
FROM sys.databases AS d

To give a specific example...

The following works fine for the initial input

DECLARE @ObjectName SYSNAME = 'sysobjects';

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT COUNT(*) FROM [' + @ObjectName + ']';

EXEC (@dynSql);

But with malicious input it is vulnerable to SQL injection

DECLARE @ObjectName SYSNAME = 'sysobjects];SELECT ''This is some arbitrary code executed. It might have dropped a table or granted permissions''--'

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT  COUNT(*)  FROM [' + @ObjectName + ']';

EXEC (@dynSql);

Using QUOTENAME correctly escapes the embedded ] and prevents the attempted SQL injection from happening.

DECLARE @ObjectName SYSNAME = 'sysobjects];SELECT ''This is some arbitrary code executed. It might have dropped a table or granted permissions''--'

DECLARE @dynSql NVARCHAR(MAX) = 'SELECT  COUNT(*)  FROM ' + QUOTENAME(@ObjectName);

EXEC (@dynSql);

  Invalid object name 'sysobjects];SELECT 'This is some arbitrary code
  executed. It might have dropped a table or granted permissions'--'.


What are the top 5 design decisions which need to be made when implementing a database? I am implementing an Oracle System to store event alerts from an application so I guess all the schema, database design will be handled by the application through a sript.

I am new to Database design, what are the top 5 design decisions which need to be made when implementing a database?

As @SQLChicken states, a good data model is a very important start to have for stable, consistent, extensible and scalable databases.

After you have a logical model, the next step is implementation of the logical model in the target RDBMS. There are many important considerations and actions that are required to do this. For example, depending on the anticipated size of your database, you may decide that certain structures should be de-normalized for performance reasons but this decision should be made carefully, with the implications fully considered. Other considerations:

If you haven't done so already, decide on a naming standard/convention. Name your objects in a consistent way. Take time to define each object such as tables and columns in the database by adding a comment to each.
Define your table primary keys, foreign keys, data constraints, and indexes. Indexes can be added later, so don't worry about getting them all up front. The need for them tends to become apparent over time ;-)
Define views of data that may be useful for reports, ad-hoc queries, etc.
Minimize trigger use - these can be expedient, but have many pitfalls.

By no means is this list exhaustive. Become familiar with the (voluminous) Oracle documentation.


What drawbacks are there in scheduling an event to run every too often,say after every 60 seconds? I have this event i want to run every 60 seconds but i wonder if this will take up many resources in my database.

 CREATE EVENT myevent
    ON SCHEDULE EVERY 60 SECOND
    DO
       UPDATE yourdb.yourtable SET yourcolumn = RAND();

If make my event to run every too often,will it impact on the performance of my database?.

A server has very many events happening every minute.  Having an event that happens every 60 seconds is not automatically too frequent

If the event runs quickly and affects a small amount of data, then there should be no particular worry.  The bigger the impact of the event (CPU,I/O,memory,etc) the more cautious you need to be about scheduling.


What is the best way to import this non-standard text file into SQL Server? I have a text file that is generated daily. It is a fixed width format with one frustrating exception. The third column (NAME) has a variable width. The column is always exactly as long as the longest name in the data set + 1 space. This makes each day's files have different column start and end points.

Does anyone have a clever way to handle importing this file on a daily basis? Changing the source program is not an option.

I am willing to modify the file before import.

I am willing to use almost any tool to include, but not limited to, SSIS, C#, PowerShell.

I am looking for the least "messy" solution with high-reliability.

Examples

A/R                                      Patient                                  PICASI, LINDA  00691685             90409843             Inpatient

Revenue                                  Patient                                  SIMPSON, RHASIRA B 00000000             00000000             Emergency

Based on your sample data it looks like there are 6 columns in total.

COL1, COL2, COL4, COL5, COL6 = All fixed width .

COL3 = This is a fixed width for a given day, but it is variable day to day (based on my understanding).

You should be able to calculate COL3's width each day based on the length of any given line. You know COL1,2,4,5,6 are all fixed width. Take the line total and subtract COL1,2,4,5,6 to get COL3's width each day.

COL3 = LINE_WIDTH - (COL1 + COL2 + COL4 + COL5 + COL6)

After you get COL3's width you will be able to figure out all of your start and end points.


Postgres database encoding problem I'm striving to convert badly encoded data from my table. For instance, I have a field with NadÃ¨ge which should be Nadège.

I tried using Postgres's functions convert, convert_from, convert_to without much success.

db=# SHOW client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

db=# SHOW server_encoding;
 server_encoding
-----------------
 UTF8
(1 row)

db=# SELECT "firstName", encode("firstName"::bytea, 'hex') FROM contact;
 firstName |       encode
-----------+--------------------
 Nadège    | 4e6164c3a86765
 NadÃ¨ge   | 4e6164c383c2a86765
(2 rows)

db=# SELECT "firstName", convert_from("firstName"::bytea, 'latin1') FROM contact WHERE "lastName" ILIKE 'crochard';
 firstName |  convert_from
-----------+----------------
 Nadège    | NadÃ¨ge
 NadÃ¨ge   | NadÃ\u0083Â¨ge
(2 rows)

db=# SELECT "firstName", convert("firstName"::bytea, 'utf8', 'latin1') FROM contact;
 firstName |     convert
-----------+------------------
 Nadège    | \x4e6164e86765
 NadÃ¨ge   | \x4e6164c3a86765
(2 rows)

Using python I'm able to get the correct encoding with:

data.encode('latin1').decode('utf8')

Any hint on how to convert these wrongly encoded data in postgres ?

As you have correctly identified, NadÃ¨ge is the UTF-8 representation of    Nadège incorrectly decoded as ISO-8859-1 ("latin-1"). Then, in your case, re-encoded to UTF-8 for storage in the DB.

To fix it you need to:

Take the current representation and decode the UTF-8 to latin-1 as a byte string
re-interpret the byte string, decoding it as utf-8

So:

test=> SELECT convert_from(convert_to('NadÃ¨ge', 'latin-1'), 'utf-8');
 convert_from
--------------
 Nadège
(1 row)

The Python equivalent would be close to what you wrote, but starts with a unicode representation to illustrate that PostgreSQL stores everything in the database encoding. Something like:

>>> print u"NadÃ¨ge".encode("latin-1").decode("utf-8")
Nadège

The problem with all your attempted solutions is that the cast from text to bytea uses the database encoding. So you're starting with the bytes for the utf-8 representation of utf-8 mis-decoded as latin-1. With the cast you'd have to write:

test=> SELECT convert_from(convert_to(convert_from((TEXT 'NadÃ¨ge')::bytea, 'utf-8'), 'latin-1'), 'utf-8');
 convert_from
--------------
 Nadège
(1 row)

because you have to explicitly decode the utf-8 representation produced by the cast before re-interpreting as latin-1 and decoding again.

You just needed to use convert_to(mycol, 'latin-1') instead of mycol::bytea


How to narrow down records in SQL based on conditions Customer   Rank      Joining_date       salary
    A         2        2017-10-12         500
    A         1        2017-10-10         800
    A         1        2017-10-20         400
    B         2        2017-05-20         200
    B         2        2017-05-15         100
    c         3        2017-06-10         600
    c         4        2017-06-05         600

Logic: For a given customer

if the Rank is 1 ,then retain all those records with rank 1 and
         drop rest

if customer has records with different rank ,select the record
  based on latest rank (order by rank desc) and drop the other

if the customer has records with same rank (other than 1),then
  select the
  record based on the lowest salary (order by salary asc)**

Expected result

 Customer   Rank      Joining_date       salary
    A         1        2017-10-10         800
    A         1        2017-10-20         400
    B         2        2017-05-15         100
    c         4        2017-06-05         600

The rank 1 customers and the other customers behave differently, so just use different queries.
The last two logic conditions can be computed with a simple ORDER BY per customer; to relate that order back to the entire row requires a window function:

WITH Rank1Customers AS (
  SELECT DISTINCT Customer
  FROM MyTable
  WHERE Rank = 1
)

SELECT Customer, Rank, Joining_date, Salary
FROM MyTable
WHERE Customer IN (TABLE Rank1Customers)
  AND Rank = 1

UNION ALL

SELECT Customer, Rank, Joining_date, Salary
FROM (SELECT *,
             row_number() OVER (PARTITION BY Customer
                                ORDER BY Rank DESC, Salary ASC) AS OrderNr
      FROM MyTable
      WHERE Customer NOT IN (TABLE Rank1Customers)
     ) AS _
WHERE OrderNr = 1;

(SQL Fiddle)


Does MySQL 5.5 support shared database with separate schema? I am developing a SaaS based product using MySQL 5.5 as the backend database. I want to keep shared database for all the tenants, but separate a database schema of each tenant. There would be a single database for all tenant but different schema for each tenant.

I am not sure whether MySQL does support it.

Your answer would help me a lot.

Thanks in advance!

My understandng of MySQL is that what they call a "database" is a schema on other db's.  So the answer to your question is "yes" or "no" depending on the specifics you are asking.  In fact "create schema" just maps to "create database" on MySQL.

So yes, you can do one schema per tenant but that is not any different on MySQL than one database per tenant.  So not sure if that is a yes or no.