Collection of different types of data in a relational database Let's say I have a table Cars (with columns such as model, make, year, etc.), a table Books (with columns such as name, author, ISBN, etc.) and a table Wines (with columns such as name, appellation, region, vintage, etc.)

Now, even if these tables don't seem related at all, I want to make a collection of all these things called "My favorites things in the world". In this list, I will find cars, books and wines.

If I only wanted to have lists of cars, I would create a table called Car_Lists with id and name + a table called Car_List_Items with id, car_list_id (foreign key to the id in the table Car_Lists) and car_id (foreign key to the id in the table Cars).

Here, as I want to have a list of different types of things, I don't really see how this could be done.

So my question: How would I represent that with a relational database?

There are many ways to do it, depending on the requirements. That being said, this sounds like a homework question which is designed to make you think about it.

However, one way to do it would be to create a table that contains columns that occur in all the different types. Then create a table for each unique type that contains information specific to that type.

Then in your list table you can just create a foreign key to the base table (in this example "things")

create table "things"
(
    id int identity not null primary key
    -- add shared columns here
);
create table "cars"
(
    id int not null,
    -- add car details here
    constraint fk_cars_id foreign key (id) references "things" (id)
);
create table "myfavoritethings"
(
     id int not null,
     constraint fk_myfavoritethings foreign key "things" (id)
);

I prefer this approach because you can maintain referential integrity. Also, you are able to create a nice view from it like so knowing that the id's are unique across all type tables:

create view "vw_myfavoritethings"
as
select t.id
     , -- add car fields here
  from "things" as t
  join "cars" as c on c.id = t.id
  -- additional joins to type tables
  join "myfavoritethings" as mft on mft.id = t.id;

Another way you could do it would be to just create your individual tables and then just store the id's and type in your list table like so:

create table "myfavoritethings"
(
    id int not null primary key
);

If you use this design it would be recommended to create a sequence and then use that to generate your primary key id's across all type tables to ensure that the id value is unique. You can of course use auto increments/identities on each table but then you would need to work out what id = 1 refers to in your list yourself.


Why is a 'Select distinct limit 100' using tmp tables? I hope someone can teach me why MySQL is using temporary tables on a super simple select with a limit of 100 and how to get it do query more efficiently. I'm running MySQL 5.1.53-community.
The following query should check row by row, save it to memory if it's unique from the other's already selected or skip if it is a repeat. Once results 100 are in memory it should terminate the query and output the result. Instead it goes through every single result, loads them into a temporary table on the hard disk sorts them and pulls the first hundred sorted ascending. It should take a fraction of a second, not several hours.
Query
SELECT DISTINCT `some field` FROM `myTable` LIMIT 100

EXPLAIN SELECT DISTINCT `some field` FROM `myTable` LIMIT 100:
id|select_type|table   |type|possible_keys|key|key_len|ref|rows      |Extra
1 |SIMPLE     |myTable | ALL|             |   |       |   |223193820 |Using temporary

There's no reason for it to sort anything aside from that's how it's programmed. It only needs to return 100 distinct values which I could do much faster manually parsing the raw text.
If it only needs 100 distinct values it doesn't have to check 220 million rows to make sure that a value isn't duplicated in the 100 rows that it is supposed to return.

There are multiple ways that SELECT DISTINCT col FROM ... LIMIT 100 can be performed:

Case 1:  Create a temp table with all rows of col, sort it, then peel off the first 100 (de-dupping as it goes).

Case 2:  Build a hash in memory, read col putting it into the hash, stop after 100.  (Fast.)

Case 3 (if there is INDEX(col)):  Leapfrog through the index.  This effectively takes 100 BTree dives into the index.  (Fast, even if few distinct values.)

Which will it do?  It depends on the version, the data, the existence of an index, etc.  Note that 5.1 is antique; you should plan to upgrade to 5.5, then 5.6, then 5.7.  (8.0 is also available, but not "GA".)  The Optimizer has changed a couple of times in this area, so I cannot say what your version will do.

If you are going to do this for all columns, consider, instead,

SELECT GROUP_CONCAT(DISTINCT col1),
       GROUP_CONCAT(DISTINCT col2),
       ...
    FROM TABLE;

(No LIMIT.)  This won't give you exactly 100 of each, and it will probably give you only the alphabetically/numerically first several (1024 bytes' worth) of each list, but maybe that is good enough.  It is probably faster than column-by-column.

See also ANALYSE() (spelled that way).


Queries hang when accessing Oracle via ODBC and Python I have Python scripts running on a Windows 2003 server that access an Oracle 11g database in the US via VPN. I am in the UK. We have recently moved offices and upgraded our broadband speed. Some of these scripts hang when accessing Oracle views now, whereas they didn't before. I have isolated two queries, one of which hangs, and one which doesn't, and the only difference between the two is that the one that hangs pulls out one more column from the database. I don't know the underlying structure of the view, but from what I know of the database the column that "causes" the hang is from a different table to the ones that come out OK, and so the underlying query is doing a join when it hangs.

I'm guessing the problem is related to timeouts somewhere along the way. I've tried changing the ODBC settings for the Oracle driver, e.g. unticking the "query timeout" option, but nothing changes.

How can I prevent these queries hanging?

Another bit of info: when I run the same two queries on a Linux box based in the US, using the isql utility they both complete in the same time - 43 seconds.

The Python ODBC library I'm using is the one from Egenix.

Update: using Wireshark I've been following the packets that are exchanged. When I use the query that causes a hang I get a response from the server, and the first row of data within half a second, and then nothing ever again. When I use the query that doesn't hang it takes 40 seconds to get the first row, and then returns all the others.

I had a very similar problem where I had a query that would run, return 541 rows (every time) and then just hang. Wireshark looked like I was sending a packet and just not getting any response.

While troubleshooting the issue I added an order by clause to the query trying to see if I could find a potentially problematic record, but then the query executed just fine.

I don't know why the order by clause fixed it and I don't care too much to find out. Hopefully this simple solution will work for others too!


Is it possible to search within a MySQL JSON array in a case insensitive manner? e.g. with the table:

create table test (id int primary key, tags json);
insert into test (id, tags) values (1, '["Lorem ipsum", "dolor", "sit"]'), (2, '["foo", "bar"]');

This will match:

select id from test where json_search(tags, 'one', 'Lorem%') is not null

But this will not:

select id from test where json_search(tags, 'one', 'lorem%') is not null

Ideally I'd like to be able match both cases.

Running on 5.7.16 Percona XtraDB Cluster

Other case, when You can not change data and must have proper result:

select
    id
from test
where json_search(UPPER(tags), 'one', UPPER('lorem%')) is not null

will be slower of course, so need test on real data-size


which postgres do i have installed? what directory needs to be in my path to run initdb? I need to use postgresql with ruby on rails.

I have these directories

/Library/PostgreSQL

and

/usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2

This 2nd directory name is confusing. In finder, when I'm in the directory

/usr/local/Cellar/postgresql,
usr:local:Cellar:postgresql:9.2.2

is actually one directory with forward slashes in the name.  usr/local/Cellar/postgresql.  It's in terminal where I see the :'s

"brew info postgres" returns the following:

postgresql: stable 9.2.2
http://www.postgresql.org/
Depends on: readline, ossp-uuid
/usr/local/Cellar/postgresql/usr:local:Cellar:postgresql:9.2.2 (2819 files, 39M)
https://github.com/mxcl/homebrew/commits/master/Library/Formula/postgresql.rb

So do I have postgres installed properly?  Do I have 2 versions?  One in Library/PostgreSQL and one in /usr/local/Cellar (via HomeBrew)?

Regarding trying to use initdb to create my first postgres database:

I have ohmyzsh installed and I am trying to run the initdb command but getting command not found.  Which path should I put in my path statement so that it can find the command?

/usr/local/var/postgres/server.log did not exist, so I created it.

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start says server is starting and when I do
    ps auxwww | grep postgres

I get:

postgres   260   0.0  0.1  2522180   8084   ??  SNs  Tue07PM   0:58.79
/System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker MDSImporterWorker com.apple.Spotlight.ImporterWorker.504

so I think that means it is running, right?

Well I found initdb script file in both Library and brew locations.  I navigated to the brew directory containing initdb and tried to run the initdb command and got zsh: command not found.

I then navigated to the Library containing initdb, tried to run it, and got the same results.

Isn't the current directory part of the path because I'm IN IT?

Summary of my questions:

Which postgres do i have installed? Is it installed twice and should
I uninstall a version? If so, how?
What directory needs to be in my path to run initdb and do I really
need to run it?
Should the postgres server start when I start my computer?  I heard
it should.  How do I set that up?

Based on your situation, it would make sense I think to disable whatever PostgreSQL you have and start over with a fresh install. We are dealing with completely different applications that have nothing to do with eachother. In reality, PostgreSQL is only needed to serve up data. It makes sense to me, to simplify your life by installing PostgreSQL seperately and treating it as a totally independant application.

The simplest way to go about this, would be to take the following steps.

Determine if PostgreSQL is currently running
A. If it is, locate it and disable it
B. If it isn't move onto step 2
Download PostgreSQL 9.2 from PostgreSQL's website
Install dependancies
Configure, compile and install postgresql
Initalize your database and start postgres
What's next

1. Determine if PostgreSQL is currently running

From the shell, type the following

[root@someserver ~]# netstat -tlpn | grep postmaster
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      1119/postmaster
tcp        0      0 :::5432                     :::*                        LISTEN      1119/postmaster

Postmaster on my machine is the PostgreSQL service. It is listening on port 5432 and has a process id of 1119.

Where is it?

Now that we have the process ID we can locate the files being used. lsof is handy because it will show you all the files and sockets a given process is using.

[root@someserver ~]# lsof -p 1119
COMMAND    PID     USER   FD   TYPE             DEVICE SIZE/OFF   NODE NAME
postmaste 1119 postgres  cwd    DIR              253,3     4096 131093 /var/lib/pgsql/9.1/data
postmaste 1119 postgres  rtd    DIR              253,0     4096      2 /
postmaste 1119 postgres  txt    REG              253,4  5205008  27145 /usr/pgsql-9.1/bin/postgres

On my server, it is using /usr/pgsql-9.1/bin/postgres to run the service and the /var/lib/pgsql/9.1/data is the data directory.

From this point you know where its running and what directories its using.

On your server, it looks like its installed in /usr/local/Cellar/postgresql. If you have to start it manually, then you can just leave it alone. When you install PostgreSQL from source, it will install in /usr/local/pgsql by default.

It is only important to be sure that the postgresql currently installed on your system is not using port 5432. This is because two services can not use the same port. If it is, you will need to kill the process and keep it from starting again. Based the contents of your question, it looks like that won't be a problem.

2. Download postgresql

From the shell

[root@someserver ~]# cd ~
[root@someserver ~]# wget http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.gz
[root@someserver ~]# tar -zxvf postgresql-9.2.2.tar.gz

3. Install dependencies

I didn't notice you mentionning what flavor of Linux you were running. You can identify your OS by running cat /etc/issue.

PostgreSQL depends on several libraries.

On CentOS, I would run this to install the libraries I needed

[root@someserver ~]# yum install readline-devel zlib-devel make gcc g++

On Debian, you would use apt-get to install and apt-cache search to locate. I included build-essential because you'll need it to compile postgres.

Something like apt-get install libreadline6 libreadline6-dev build-essential

Not sure about OSX though

4. Configure, Compile and Install PostgreSQL

[root@someserver ~]#
cd postgresql-9.2.2
The --bindir option will put the the postgresql binaries like pg_ctl, initdb, createdb all in the /usr/bin directory. This will be handy for you since you mentionned having trouble finding them.
./configure --bindir=/usr/bin
make
make install

You can check to see if the binaries are available

[root@someserver ~]# psql -V
psql (PostgreSQL) 9.2.2

Once this is done, normally you would need to add the postgres user, but he's already on your system
You do however need to create a data directory and give postgres read/write access to it

[root@someserver ~]# mkdir -p /var/lib/pgsql/data
[root@someserver ~]# chown postgres:postgres -R /var/lib/pgsql/data
[root@someserver ~]# su - postgres

PostgreSQL is installed
You have created a data directory
Now you can initalize your database

5. Initalize and start postgres

initdb -D /var/lib/pgsql/data

This will the cluster and your configuration files such as postgresql.conf, pg_hba.conf which you will need to modify later based on the users that need access, their ip addresses and what ip address and port postgresql will listen on

Now you can start PostgreSQL with the following command :

/usr/local/pgsql/bin/postmaster -D /var/lib/pgsql/data > /var/lib/pgsql/logfile 2>&1 &

When you run netstat -tlpn again, you should see

[root@someserver ~]# netstat -tlpn | grep postmaster
tcp        0      0 127.0.0.1:5432                127.0.0.1:*                   LISTEN      8721/postmaster

It is only listening to local connections for now, because by defaut that's what postgresql.conf is configured to do.

6. What's next

From here, you would need to configure postgresql.conf and pg_hba.conf appropriately. pg_hba.conf defines who can connect, from where and to what. postgresql.conf is used to configure the entire service, what ip address or addresses it listens on, what port number, maintenance cyles, etc. For your purposes, you will probabaly only be interested in changing this variable

listen_addresses = '*'

You may also want to create an init script in the /etc/init.d directory, so it will start automatically. How you go about that also depends on what flavor of Linux you are running.

If you run into any problems let me know. Since you are probably not using the exact same system, you may run into problems I haven't discussed here.


Connection Error while connecting SQL Developer with DB2 I am trying to connect Oracle SQL Developer with DB2 Database.

I have added db2jcc4.jar, db2jcc.jar and db2jcc_license_cu.jar Libraries in "Third Party JDBC Driver"
DB2 tabs appears
Entered Username and connection Details.

While testing connection, I encounterd below error.

  Status : Failure -Test failed: [jcc][t4][2034][11148][4.24.92] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
  A DRDA Data Stream Syntax Error was detected.  Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

Versions:

db2level: v11.1

./java com.ibm.db2.jcc.DB2Jcc -version

IBM DB2 JDBC Universal Driver Architecture 3.72.44

Jars installed:

echo $CLASSPATH

/db2/coru1db2/home/coru1db2/sqllib/java/db2java.zip:
/db2/coru1db2/home/coru1db2/sqllib/java/sqlj.zip:
/db2/coru1db2/home/coru1db2/sqllib/function:
/db2/coru1db2/home/coru1db2/sqllib/java/db2jcc_license_cu.jar:
/db2/coru1db2/home/coru1db2/sqllib/tools/clpplus.jar:
/db2/coru1db2/home/coru1db2/sqllib/tools/jline-0.9.93.jar:
/db2/coru1db2/home/coru1db2/sqllib/java/db2jcc.jar:

Can someone help me in resolving it?

An error like this might be returned if you will try to connect to a port which is actually not the port DB2 server listens on.
E.g. if I would, by mistake, try to connect to port 22 (ssh) I will get the same error:

$ java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:22/SAMPLE -user db2v111 -password passw0rd

[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://localhost:22/SAMPLE -user db2v111 -password ********
[jcc][10512][13714]Failed to create connection.
  SQLCODE: -4499
  SQLSTATE: 58009
  Message: [jcc][t4][2034][11148][3.72.52] Execution failed due to a distribution protocol error that caused deallocation of the conversation.
A DRDA Data Stream Syntax Error was detected.  Reason: 0x3. ERRORCODE=-4499, SQLSTATE=58009

You can check the port Db2 listens on with:

 db2 get dbm cfg | grep SVCENAME
 TCP/IP Service name                          (SVCENAME) = 60111

Db2 will listen on this port if you have DB2COMM=TCPIP set. On there server side you can verify that e.g. with lsof:

$ lsof -p <pid_of_db2sysc> -iTCP -sTCP:LISTEN -a

(all sockets in listening state and -a to AND selections)
e.g.

$ lsof -p 10661  -iTCP -sTCP:LISTEN -a
COMMAND   PID    USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
db2sysc 10661 db2v111    3u  IPv4 39727390      0t0  TCP *:60111 (LISTEN)

If the server listens only for SSL connections (only SSL_SVCENAME set in dbm cfg) then you can test that connection to Db2's SSL port does work e.g. with OpenSSL client:

$ openssl s_client -connect localhost:61111
CONNECTED(00000003)
depth=0 C = CA, ST = ON, L = myLocation, O = myOrganization, OU = myOrganizationUnit, CN = DB2INSTANCE
verify error:num=18:self signed certificate
verify return:1
depth=0 C = CA, ST = ON, L = myLocation, O = myOrganization, OU = myOrganizationUnit, CN = DB2INSTANCE
verify return:1

If it is OK then test from Java:

~/sqllib/java/jdk64/bin/java -cp ~/sqllib/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true\; -user db2v111 -password XXXX

[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true; -user db2v111 -password ********

[jcc][10512][13714]Failed to create connection.
  SQLCODE: -4499
  SQLSTATE: 08001
  Message: [jcc][t4][2030][11211][4.25.1301] A communication error occurred during operations on the connection's underlying socket, socket input stream,
or socket output stream.  Error location: Reply.fill() - socketInputStream.read (-1).  Message: com.ibm.jsse2.util.h: PKIX path building failed: java.security.cert.CertPathBuilderException: unable to find valid certification path to requested target. ERRORCODE=-4499, SQLSTATE=08001

not quite there - you need also sslCertLocation that points to certificate of the database:

~/sqllib/java/jdk64/bin/java -cp ~/sqllib/java/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url "jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true;sslCertLocation=/home/db2v111/db2ssl/mydbserver.arm;" -user db2v111 -password XXXX

[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://127.0.0.1:61111/SAMPLE:sslConnection=true;sslCertLocation=/home/db2v111/db2ssl/mydbserver.arm; -user db2v111 -password ********

[jcc][10516][13709]Test Connection Successful.

DB product version = SQL110144
DB product name = DB2/LINUXX8664
DB URL = jdbc:db2://127.0.0.1:61111/SAMPLE
DB Drivername = IBM Data Server Driver for JDBC and SQLJ
DB OS Name = Linux


Handling exceptions from inside a function I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update statement may throw an error (updating creates a duplicate index on the table), then I need to take a different approach, meaning I need to delete instead of update.

The function, so far, is this:

CREATE OR REPLACE FUNCTION prod_profiles_check_grouped_item()
    RETURNS trigger AS $BODY$
DECLARE found_group bigint;
BEGIN
    SELECT group_id INTO found_group
      FROM inv_items_group_members
     WHERE item_id = NEW.item_id;
     IF found_group IS NOT NULL THEN
        UPDATE public.prod_profiles_steps_items
           SET item_id = found_group
         WHERE item_id = NEW.item_id;

        -- TODO : on error for the last update, this should get executed:
        -- DELETE FROM public.prod_profiles_steps_items
        -- WHERE item_id = NEW.item_id;

    END IF;

    RETURN NEW;
END; $BODY$

How can this be done? All I read everywhere are RAISE. Which is irrelevant here.

In a PL/pgSQL function (which you are using, but the language declaration is missing), use an EXCEPTION clause in your block.

I also consolidated your two SQL statement with variable declaration and assignment into a single, equivalent statement, which is typically much faster and less susceptible to race conditions in a multi-user environment. Details in the manual about UPDATE.

CREATE OR REPLACE FUNCTION prod_profiles_check_grouped_item()
    RETURNS trigger AS
$func$
BEGIN

   UPDATE public.prod_profiles_steps_items i
   SET    item_id = m.group_id
   FROM   public.inv_items_group_members m
   WHERE  m.item_id = NEW.item_id
   AND    i.item_id = NEW.item_id;

   RETURN NEW;

EXCEPTION WHEN UNIQUE_VIOLATION THEN
-- This catches *any* unique violation in the function. Use a nested block,
-- if you have more candidates in the same function and want to narrow it down.
   DELETE FROM public.prod_profiles_steps_items
   WHERE  item_id = NEW.item_id;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;  -- language declaration required!

Related answers with more code examples:

PostgreSQL stored procedure to return rows or empty set
Issue with composite type in an UPSERT function


Special Considerations for SQL Server with 512 GB RAM / 64 Cores? Apart from some Windows Hotfixes are there any special considerations (i.e. "things to worry about") when running SQL on a server with 512 GB RAM and 64 logical cores?

I'm aware of some guidance when operating above one or both of these thresholds but not right at them.

OS: Windows Server 2008 R2
SQL Server 2008 R2 SP3 (clustered)

Max Server Memory should be no higher than 445 GB. This gives Windows enough memory to manage itself. Yes, it really does need that much.

Max Degree of Parallelism should be set to the number of logical cores in one NUMA node, up to a maximum of 8.

If it's running an OLTP environment, make sure Optimize for Ad Hoc Workloads is enabled.

Ensure power saving on the OS is set to High Performance.

TempDB should be on your fastest storage, followed by transaction logs.

There's a whole lot more you can look at, but these are the main items.

As some comments have noted, SQL Server 2008 R2 is out of mainstream support. Extended support ends in July 2019 (not this year), so there's just about enough time to start a project to move to a newer version. I would be looking firmly at SQL Server 2017 or later.


Join tables up together to get different counts of account type per postcode are I am trying to deterine the amount of account types per postcode range. I have the following code that works but I would like help in refactoring it as only 1 line of code changing between each

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'New Connections'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 1
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Domestic Metered'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 2
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commercial Metered Low'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 3
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commerical Metered High'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 4
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Domestic Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 5
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Generator'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 6
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 7
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

Any help appreciated.

I will try something like this:

select substring(PostOfficeBox,1,4) as 'Postcode'
    , COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
    , COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
    , COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
    , COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
    , COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
    , COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
    , COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) = 'BT'
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
--having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)


Deny view definition at DB level and symmetric keys I don't want a restricted database role to be able to view table and stored procedure definitions and code.

So I do

deny view definition to some_db_role

It does appear though that this is not capable of being overridden.

grant view definition on dbo.some_proc to some_db_role

seems to have no effect.

Worse

grant view definition, references on symmetric key::some_key to some_db_role

seems to have no effect. Basically if you deny view definition at the db level then symmetric keys are unusable by that role\user.

Am I missing something? Is there any way to grant rights to objects that override the rights of the db level deny?

From BOL:

  any permission denial overrides all grants

The implication is that when you DENY permissions to an object those permissions override any GRANTed permissions applicable to that object.

Having said that, you don't need to explicitly deny "view definition" permissions on procedures and other code.  If you don't grant VIEW DEFINITION the definitions are not visible, even if the code can be executed.  Tables work slightly differently; you'll need to deny view definition to any tables the role has SELECT rights on.

You can test this using the code below.

Create a test database:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
BEGIN
    DROP DATABASE TestPerms_20160921;
END;
GO
CREATE DATABASE TestPerms_20160921;
GO
USE TestPerms_20160921;
GO

Create a table, and a stored proc:

IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test;
CREATE TABLE dbo.Test
(
    TestID INT NOT NULL
);
GO
IF OBJECT_ID('dbo.TestProc') IS NOT NULL
DROP PROCEDURE dbo.TestProc;
GO
CREATE PROCEDURE dbo.TestProc
AS
BEGIN
    SELECT 1;
END
GO

Create a test user, and a test role:

IF EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = 'TestU'
    )
BEGIN
    DROP USER TestU;
END
CREATE USER TestU WITHOUT LOGIN;

IF EXISTS (
    SELECT *
    FROM sys.database_principals dp
    WHERE dp.name = 'dbtestrole'
    )
BEGIN
    DROP ROLE dbtestrole;
END
CREATE ROLE dbtestrole;

ALTER ROLE dbtestrole ADD MEMBER TestU;

Grant SELECT and EXECUTE to the test objects:

GRANT SELECT ON dbo.Test TO dbtestrole;
GRANT EXECUTE ON dbo.TestProc TO dbtestrole;

Impersonate the user so we can see the effect of the GRANT statements above:

EXECUTE AS USER = 'TestU';

/* column names and types **are** visible */
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* access to view the definition is denied */
EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL;

/* we can run the proc! */
EXEC dbo.TestProc;

REVERT;

DENY VIEW DEFINITION on the table, and test again:

DENY VIEW DEFINITION ON dbo.Test TO dbtestrole;

EXECUTE AS USER = 'TestU';

/* no rows returned as we can no longer view the table definition */
SELECT s.name
    , t.name
    , c.name
    , ty.name
FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE s.name = 'dbo'
    AND t.name = 'Test'
ORDER BY s.name
    , t.name
    , c.name;

/* we can still run the proc */
EXEC dbo.TestProc;

/* we can still see the content of the table */
SELECT *
FROM dbo.Test;

REVERT;

Cleanup:

USE master;
GO
IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921')
DROP DATABASE TestPerms_20160921;


Problem connecting to the Database This is my Oracle DB :

Server :      hp805
Listener :    7633
SID :         idopd
ServiceName : serv01
Login :       ikr_adm
Passwd :      ikr_adm_01

How do you connect to this DB ?

The correct URL for a JDBC based connection would be:

jdbc:oracle:thin:@hp805:7633:idopd

But that information should be available in the DbVisualizer manual and is definitely available in the Oracle manual.

Edit:
The description on how to setup a connection with the wizard is available here:
http://www.dbvis.com/products/dbvis/doc/main/doc/ug/getConnected/getConnected.html


Oracle - Is a separate database more secure than using the same database and a separate schema We have very few developers and the ask from management is insane (computer vision, AI, IoT, etc.). Management wants to split the production database which uses Oracle Apex because the Apex application will be on the public internet and therefore security is an issue. Is a separate database more secure than using the same database and a separate schema when using the Oracle database? I hate REST and all the complications like authentication that comes with it, so I would much rather be on the same database and just do a straight SQL select. Can I just grant select on specific tables between two schemas and thereby keep sensitive financial data equally secure as when having it on a separate database and sharing data using REST or a slow database link?

Management wants to split the production database which uses Oracle Apex because the Apex application will be on the public internet and therefore security is an issue.

If Management don't want the database server that hosts the Apex application on the "public internet" then that's what they want.  That database server will move to somewhere "inside" the Firewall.

Is a separate database more secure than using the same database and a separate schema when using the Oracle database?

From an Information Security stand-point?  Almost certainly.
Machines tend to be compromised, not Applications.
If this database server were to be compromised then the attacker would presumably target the root account and thereby gain access to any other account on the machine.  If, like most other DBAs, you've allowed O/S authentication for your "oracle" account, then the whole database is vulnerable.
From a cross-application perspective?  Not if you've set up your security model properly.
You can (and should) set up Roles that grant specific permissions on specific objects and grant these Roles to the Application Accounts that need to use them.  In that way, Application A can access the tables in Schema B that it needs to use but nothing more.
REST and other API-based strategies move this into the Application layer, where Developers are move "comfortable", but even these still need access to database objects so the creation/granting of Roles still applies.


Inserting Master database data into my database table I put a trigger on one table on update, and I want to insert the last 15 executed queries into a table. Last executed queries are in the MASTER database as

SELECT top(15) dest.text AS [Query]
    FROM sys.dm_exec_query_stats AS deqs

   CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
      --inner join  sys.sysprocesses
     -- on sys.sysprocesses.sql_handle=deqs.sql_handle

      ORDER BY deqs.last_execution_time DESC

How can I insert these 15 queries into my database table as string? The trigger I am using is like this.

ALTER TRIGGER [dbo].[uom_cu]
ON  [dbo].[saleExpStockID] after update

AS

INSERT INTO zzuomupdate(date_cu, Query_r)
VALUES(getdate(), (SELECT top(15) dest.text AS [Query]
    FROM master.sys.dm_exec_query_stats AS deqs

   CROSS APPLY master.sys.dm_exec_sql_text(deqs.sql_handle) AS dest

      ORDER BY deqs.last_execution_time DESC))

I very seriously question the use of a trigger here.  Your trigger is going to dramatically slow down the updates of your table.  That being said your query should look more like this:

INSERT INTO zzuomupdate(date_cu, Query_r)
SELECT top(15) getdate(), dest.text AS [Query]
FROM master.sys.dm_exec_query_stats AS deqs
CROSS APPLY master.sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

The way you had it you were trying to pull 15 rows worth of information into a single column and that just doesn't work.  The INSERT INTO SELECT syntax is the way to go here.


Constraint Information - PostgreSQL 9.2 I was trying to delete some data from the table dm.billables:

1 - Select the id - PASS

SELECT * INTO junk.clientid_10306 FROM dm.billables WHERE account_id = 10306;

2 -  Delete the data from the table below - PASS

DELETE FROM dm.billables_links WHERE billable_id IN (select billable_id FROM junk.clientid_10306);

3 -  Delete data from the table below - FAIL

DELETE FROM dm.billables WHERE billable_id IN (select billable_id FROM junk.clientid_10306);

ERROR:

ERROR:  update or delete on table "billables" violates foreign key constraint "fk_billable_must_exist" on table "billables"
DETAIL:  Key (billable_id)=(14188857) is still referenced from table "billables".

Constraint:

ALTER TABLE dm.billables_links
  ADD CONSTRAINT fk_billable_must_exist FOREIGN KEY (billable_id)
      REFERENCES dm.billables (billable_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

There is no data on dm.billables_link anymore. Why am I getting the error though?

Table dm.billables:

CREATE TABLE
    billables
    (
        billable_id BIGINT DEFAULT "nextval"('"dm"."billables_billable_id_seq"'::"regclass") NOT
        NULL,
        account_id BIGINT NOT NULL,
        code CHARACTER VARYING(64) NOT NULL,
        info "TEXT",
        m_unit USER-DEFINED,
        m_unit_custom CHARACTER VARYING(64),
        unit_cost NUMERIC(16,4),
        tax_aggregate_id_cost BIGINT,
        unit_price NUMERIC(16,4),
        tax_enabled_price BOOLEAN DEFAULT true,
        tax_aggregate_id_price BIGINT,
        ts_created TIMESTAMP(6) WITH TIME ZONE DEFAULT "transaction_timestamp"() NOT NULL,
        ts_modified TIMESTAMP(6) WITH TIME ZONE DEFAULT "transaction_timestamp"() NOT NULL,
        ts_last_used TIMESTAMP(6) WITH TIME ZONE,
        is_demo BOOLEAN DEFAULT false NOT NULL,
        CONSTRAINT pk_billables PRIMARY KEY (billable_id),
        CONSTRAINT fk_price_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_price)
        REFERENCES tax_aggregates (tax_aggregate_id),
        CONSTRAINT fk_cost_task_aggregate_must_exist FOREIGN KEY (tax_aggregate_id_cost) REFERENCES
        tax_aggregates (tax_aggregate_id),
        CONSTRAINT uc_billable_code_unique_per_account UNIQUE ("account_id", "code"),
        CONSTRAINT cc_m_unit_either_ref_or_custom CHECK (ARRAY[("m_unit" IS NOT NULL),
        ("m_unit_custom" IS NOT NULL)] <> ARRAY[true, true])
    );

Table dm.billalbes_link:

CREATE TABLE
    billables_links
    (
        billable_link_id BIGINT DEFAULT "nextval"('"dm"."billables_links_billable_link_id_seq"'::
        "regclass") NOT NULL,
        billable_id BIGINT NOT NULL,
        customer_id BIGINT,
        role_id BIGINT,
        mobiuser_id BIGINT,
        CONSTRAINT pk_billables_links PRIMARY KEY (billable_link_id),
        CONSTRAINT fk_billable_must_exist FOREIGN KEY (billable_id) REFERENCES billables
        (billable_id),
        CONSTRAINT fk_customer_must_exist FOREIGN KEY (customer_id) REFERENCES
        dbname_live.public.ja_customers (id),
        CONSTRAINT fk_role_must_exist FOREIGN KEY (role_id) REFERENCES dbname_live.public.ja_role
        (id),
        CONSTRAINT cc_one_and_only_one_target CHECK ((((("customer_id" IS NOT NULL))::INTEGER + (
        ("role_id" IS NOT NULL))::INTEGER) + (("mobiuser_id" IS NOT NULL))::INTEGER) = 1)
    );

The problem was that I was using PGADMIN to see the constraints and references.

When I used the \d+ command, I was able to see the other references better.

So, There was another table that had the same data, and I had to delete them first.

Once I did it, I was able to delete the data I wanted.


DPM transaction log backups of SQL Server partially contained databases I am trying to use Microsoft System Center Data Protection Manager (DPM) 2016 (5.0.342.0) to protect SQL Server 2017 Standard CU8 (14.0.3029.16) on Windows Server Std 2016.

I have a named instance with the standard built-in databases (master, msdb, model) and a couple of custom databases that were created by doing a restore from a full backup of SQL Server 2012.

I have switched the custom databases to partially contained and to database compatibility level 140.  Their Recovery Model is Full.  They are not in an Always-On Availability group, they are not configured for mirroring, they are not configured for transaction log shipping.

When I set up a DPM protection group and choose the entire instance, then configure 15-minute synchronizations along with daily Express full backups, DPM sets up Recovery Point jobs (effectively, transaction log backups) every 15 minutes for model, but my custom databases only get jobs for the daily full-database backup.

I can't find any specific error messages that seem relevant in Event logs (System or Application) on the DPM Server or the SQL Server, in the DPM logs (C:\Program Files\Microsoft Data Protection Manager\DPM\Temp) on the DPM server or the SQL Server, or in SQL Server's own error log.  I've granted both the DPM Server's domain computer account and the local account running the DPM agent (NT Service\DPMRA) a local user in the custom database with the db_owner role, but that doesn't help.

Any suggestions?

thanks!

Martin

I eventually figured out that the problem was not with all contained databases.  It was fixed by removing and re-adding the problem databases to the DPM protection group. I have no explanation.

Martin


Select consecutive rows that are in a date range of each other Having a list of associated dates & names and I want to select: all rows with names having multiple dates when the difference between their dates is more than 1 month.
e.g.: only the entries indicated below marked with \*this*\
CREATE TABLE IF NOT EXISTS myTab (
    id          SERIAL PRIMARY KEY,
    dateID      DATETIME DEFAULT 0,
    name        VARCHAR(512)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO myTab
    (dateID, name)
VALUES
    ("20140811","Emmy"),    /*this*/
    ("20140922","Emmy"),    /*this*/
    ("20150920","Emmy"),    /*this*/
    ("20150922","Emmy"),
    ("20140722","Dave"),
    ("20140613","Stan"),
    ("20140622","Stan"),    /*this*/
    ("20151020","Stan"),    /*this*/
    ("20140305","Lora"),
    ("20140310","Lora");

In other words the criteria is:

Partition by name
Order by date
Compare 2 consecutive rows: IF diff > 1 MONTH THEN select both, ELSE skip

Here's a working example as well as my attempt based on another answer on SO:
Rextester working example and attempt
Additional conditions/hints/...

Rows having the same name are not necessarily inserted once after the other making them spaced with +1 id from eachothers. Nor they are inserted oredered by date. In the example above it's done so just for readability. In my real problem it's not the case!
After applying your suggestions on real data I noticed an extra condition to be added tagged /*this_EXRA*/ in the example above. The 3rd stan row is in less than 1 MONTH from the 2nd but validates it with the 4th. Thus, it should only be selected if it validates them both. So I guess this implicates looping row by row and compare with previous and next one each time.

The logic looks simple at first, but it's quite complicated to get it right.

Let's have a working solution first, and worry about performance later. Tested at rextester.com:

SELECT t.id, t.dateID, t.name
FROM  myTab AS t
WHERE
       ( SELECT b.dateID
         FROM myTab AS b
         WHERE t.name = b.name
           AND b.dateID < t.dateID
         ORDER BY b.dateID DESC
         LIMIT 1
       ) + INTERVAL 1 MONTH  <=  t.dateID
    OR
       t.dateID + INTERVAL 1 MONTH <=
       ( SELECT b.dateID
         FROM myTab AS b
         WHERE t.name = b.name
           AND t.dateID < b.dateID
         ORDER BY b.dateID ASC
         LIMIT 1
       )
 ;

Regarding efficiency: the query will perform rather poorly. An index on (name, dateID, id) will help but the query will still need to do 2 subqueries for each row of the table.


Postgresql performed a "fast shutdown" by itself. Why? How was it able to do that? I've already googled and manual read as much as I could before posting this here.

Version info:  Postgres8.4 on Ubuntu10.x

I have a personal laptop that I run a postgres server on, and a Java script using JDBC was running for 1600~ mintues (that I have had running for weeks prior) before dying saying an administrator restarted the server.  Here is what I found in the pgsql logs:

Feb 29 07:46:55 krufe-laptop postgres[3578]: [2-1] 2012-02-29 07:46:55 EST LOG:  incomplete startup packet
Feb 29 07:46:56 krufe-laptop postgres[1113]: [2-1] 2012-02-29 07:46:56 EST LOG:  received fast shutdown request
Feb 29 07:46:56 krufe-laptop postgres[1113]: [3-1] 2012-02-29 07:46:56 EST LOG:  aborting any active transactions
Feb 29 07:46:56 krufe-laptop postgres[24095]: [2-1] 2012-02-29 07:46:56 EST FATAL:  terminating connection due to administrator command
Feb 29 07:46:56 krufe-laptop postgres[24095]: [2-2] 2012-02-29 07:46:56 EST STATEMENT:  SELECT high FROM onemin_interval WHERE ticker = 'QQQ' AND interval_end <= 1329926940013 ORDER BY interval_start DESC LIMIT 46
Feb 29 07:46:56 krufe-laptop postgres[22214]: [2-1] 2012-02-29 07:46:56 EST FATAL:  terminating connection due to administrator command
Feb 29 07:46:56 krufe-laptop postgres[1252]: [2-1] 2012-02-29 07:46:56 EST LOG:  autovacuum launcher shutting down
Feb 29 07:46:56 krufe-laptop postgres[1250]: [1-1] 2012-02-29 07:46:56 EST LOG:  shutting down
Feb 29 07:46:56 krufe-laptop postgres[3582]: [4-1] 2012-02-29 07:46:56 EST FATAL:  the database system is shutting down
Feb 29 07:46:56 krufe-laptop postgres[3583]: [4-1] 2012-02-29 07:46:56 EST FATAL:  the database system is shutting down
Feb 29 07:46:56 krufe-laptop postgres[3584]: [4-1] 2012-02-29 07:46:56 EST FATAL:  the database system is shutting down
Feb 29 07:46:56 krufe-laptop postgres[3585]: [4-1] 2012-02-29 07:46:56 EST FATAL:  the database system is shutting down
Feb 29 07:46:56 krufe-laptop postgres[1250]: [2-1] 2012-02-29 07:46:56 EST LOG:  database system is shut down
Feb 29 07:47:10 krufe-laptop postgres[4318]: [1-1] 2012-02-29 07:47:10 EST LOG:  database system was shut down at 2012-02-29 07:46:56 EST
Feb 29 07:47:10 krufe-laptop postgres[4319]: [1-1] 2012-02-29 07:47:10 EST LOG:  incomplete startup packet
Feb 29 07:47:10 krufe-laptop postgres[4309]: [1-1] 2012-02-29 07:47:10 EST LOG:  database system is ready to accept connections
Feb 29 07:47:10 krufe-laptop postgres[4322]: [1-1] 2012-02-29 07:47:10 EST LOG:  autovacuum launcher started

I live by myself and noone else could ssh in from the outside to do this.  I was not home at the time this happened.  I viewed the auth logs for commands that were run during this time, nothing outside of normal cron commands.  Why would this happen?  What would cause this?  Can it be fixed/prevented?

If you have unattended security updates enabled, that would be a reason. A new version of postgreSQL (security update) was released today.


How expensive is it to rename column name on a big table? Say we have table in an Oracle DB with millions of rows - how expensive is it rename it? My guess (and hope) is that it's just a metadata change, so the size of the table has no impact, but I'd like to verify (couldn't find any useful results).

Yes, it is just a metadata update.

SQL> create table t1 as
     with g as (select * from dual connect by level <= 1000)
     select 'HELLO' as c1 from g,g,g where rownum <= 1e7;

Table created.

SQL> select bytes/1024/1024 from user_segments where segment_name = 'T1';

BYTES/1024/1024
---------------
            128

SQL> select
       s.value/1024/1024
     from v$mystat s
     natural join v$statname sn
     where sn.name = 'redo size';

S.VALUE/1024/1024
-----------------
       131.848621

SQL> alter table t1 rename column c1 to c2;

Table altered.

SQL> select
       s.value/1024/1024
     from v$mystat s
     natural join v$statname sn
     where sn.name = 'redo size';

S.VALUE/1024/1024
-----------------
       131.851715

Above statement produced just a tiny amount of redo.

But there are other aspects: the required locks and invalidation of related PL/SQL objects.


OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table.  In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code.  This version honours the time component and will scale well.  The calendar table is one I use in my dev.

Please note this is intended as a demo, so please double-check the results:

USE tempdb
GO

IF NOT EXISTS ( SELECT * FROM sys.schemas WHERE name = 'calendar' )
EXEC('CREATE SCHEMA calendar')
GO

-- DROP TABLE calendar.main
IF OBJECT_ID('calendar.main') IS NULL
BEGIN

    CREATE TABLE calendar.main (
        calendarId      INT IDENTITY(1,1) NOT NULL,
        calendarDate    DATE NOT NULL,
        calendarDay     INT NOT NULL,
        calendarMonth   INT NOT NULL,
        calendarYear    INT NOT NULL,
        [dayOfYear]     INT NOT NULL,
        [week]          INT NOT NULL,
        [quarter]       INT NOT NULL,
        datenameDay     VARCHAR(9) NOT NULL,
        datenameMonth   VARCHAR(9) NOT NULL,
        academicMonth   INT NOT NULL,
        academicYear    INT NOT NULL,
        isWeekday       BIT NULL,
        isWeekend       BIT NULL,
        lastDayOfMonth  BIT NULL,

    CONSTRAINT PK_calendar__main PRIMARY KEY ( calendarDate ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT UK_calendar__main UNIQUE NONCLUSTERED ( calendarId ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    -- Add indexes
    CREATE INDEX idx_calendar__main__calendarYear ON calendar.main ( calendarYear )
    CREATE INDEX idx_calendar__main__calendarMonth ON calendar.main ( calendarMonth )
    CREATE INDEX idx_calendar__main__calendarDay ON calendar.main ( calendarDay )
    CREATE INDEX idx_calendar__main__calendarYearMonthDay ON calendar.main ( calendarYear, calendarMonth, calendarDay )

    CREATE INDEX idx_calendar__main__academicYear ON calendar.main ( academicYear )
    CREATE INDEX idx_calendar__main__academicMonth ON calendar.main ( academicMonth )
    CREATE INDEX idx_calendar__main__academicYearMonth ON calendar.main ( academicYear, academicMonth )

    --!!TODO add any indexes you need

    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR isWeekday
    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR isWeekend
    ALTER TABLE calendar.main ADD  DEFAULT ((0)) FOR lastDayOfMonth
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((academicMonth>=(1) AND academicMonth<=(12)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((academicYear>=(1999) AND academicYear<=(2030)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarDay>=(1) AND calendarDay<=(31)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarMonth>=(1) AND calendarMonth<=(12)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  ((calendarYear>=(2000) AND calendarYear<=(2030)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([dayOfYear]>=(1) AND [dayOfYear]<=(366)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([quarter]>=(1) AND [quarter]<=(4)))
    ALTER TABLE calendar.main  WITH CHECK ADD CHECK  (([week]>=(1) AND [week]<=(54)))

END
GO

-- Populate calendar table once only
IF NOT EXISTS ( SELECT * FROM calendar.main )
BEGIN

    -- Populate calendar table
    WITH cte AS
    (
    SELECT 0 x
    UNION ALL
    SELECT x + 1
    FROM cte
    WHERE x < 11323 -- Do from year 1 Jan 2000 until 31 Dec 2030 (extend if required)
    )
    INSERT INTO calendar.main ( calendarDate, calendarDay, calendarMonth, calendarYear, [dayOfYear], [week], [quarter], datenameDay, datenameMonth, academicMonth, academicYear, isWeekday, isWeekend, lastDayOfMonth )
    SELECT
        calendarDate,
        DAY( calendarDate ) cDay,
        MONTH( calendarDate ) cMonth,
        YEAR( calendarDate ) cYear,
        DATENAME( dayOfYear, calendarDate ) cdayOfYear,
        DATENAME( week, calendarDate ) cWeek,
        DATENAME( quarter, calendarDate ) cQuarter,
        DATENAME( dw, calendarDate ) cdw,
        DATENAME( month, calendarDate ) cMonthName,
        MONTH( academicDate ) aMonth,
        YEAR( academicDate ) aYear,
        CASE WHEN DATENAME( dw, calendarDate ) Not In ( 'Saturday', 'Sunday' ) THEN 1 ELSE 0 END,
        CASE WHEN DATENAME( dw, calendarDate ) In ( 'Saturday', 'Sunday' ) THEN 1 ELSE 0 END,
        CASE WHEN DAY( DATEADD( day, 1, calendarDate ) ) = 1 THEN 1 ELSE 0 END

    FROM
        (
        SELECT
            DATEADD( day, x, '1 Jan 2000' ) calendarDate,
            DATEADD( month, -7, DATEADD( day, x, '1 Jan 2000' ) ) academicDate
        FROM cte
        ) x
    WHERE calendarDate < '1 Jan 2031'
    OPTION ( MAXRECURSION 0 )

    ALTER INDEX ALL ON calendar.main REBUILD

END
GO

IF OBJECT_ID('dbo.records') IS NOT NULL DROP TABLE dbo.records
CREATE TABLE dbo.records (
    Id              INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    period          VARCHAR(10) NOT NULL,
    startDateTime   DATETIME NOT NULL,
    endDateTime     DATETIME NOT NULL
)
GO

INSERT INTO dbo.records ( period, startDateTime, endDateTime )
VALUES
    ( 'day', '2014-01-01 06:10:10', '2014-01-05 18:20:30' ),
    ( 'day', '2014-01-10 04:40:20', '2014-01-15 02:50:10' ),
    ( 'day', '2014-01-11 04:10:10', '2014-01-11 08:00:10' ),

    ( 'month', '2014-01-05 14:20:30', '2014-06-05 12:30:40' ),
    ( 'month', '2014-10-10 06:50:40', '2014-12-15 04:10:20' ),
    ( 'month', '2014-10-10 06:10:50', '2014-10-10 06:30:10' ),

    ( 'year', '2015-03-05 14:10:30', '2018-02-06 05:20:40'),
    ( 'year', '2019-02-04 12:32:23', '2022-03-09 22:34:12'),
    ( 'year', '2020-04-05 06:05:04', '2020-06-03 09:07:45')

GO

DECLARE @period VARCHAR(10) = 'year'    -- 'day'|'month'|'year'

IF @period = 'day'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY ( SELECT TOP 1 * FROM calendar.main c WHERE c.calendarDate Between r.startDateTime And r.endDateTime ) c
    WHERE r.period = 'day'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarDate > c.calendarDate ) c2
    WHERE r.period = 'day'
    ORDER BY Id, startDateTime, endDateTime

END
ELSE IF @period = 'month'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY (
            SELECT TOP 1 *
            FROM calendar.main c
            WHERE c.calendarDate Between r.startDateTime And r.endDateTime
              AND c.calendarDay = 1
              ) c
    WHERE r.period = 'month'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarDate > c.calendarDate AND c2.calendarDay = 1 ) c2
    WHERE r.period = 'month'
      AND c.calendarDay = 1
    ORDER BY Id, startDateTime, endDateTime

END

ELSE IF @period = 'year'
BEGIN

    SELECT
        r.Id,
        r.startDateTime,
        ISNULL( CAST( c.calendarDate AS DATETIME ), r.endDateTime ) AS endDateTime
    FROM dbo.records r
        OUTER APPLY (
            SELECT TOP 1 *
            FROM calendar.main c
            WHERE c.calendarDate Between r.startDateTime And r.endDateTime
              AND c.dayOfYear = 1
              ) c
    WHERE r.period = 'year'

    UNION ALL

    SELECT
        r.Id,
        CAST ( c.calendarDate AS DATETIME ) startDateTime,
        CASE WHEN c2.calendarDate > endDateTime THEN endDateTime ELSE c2.calendarDate END
    FROM dbo.records r
        INNER JOIN calendar.main c ON c.calendarDate Between r.startDateTime And r.endDateTime
            CROSS APPLY ( SELECT TOP 1 * FROM calendar.main c2 WHERE c2.calendarYear > c.calendarYear
                AND c2.dayOfYear = 1 ) c2
    WHERE r.period = 'year'
      AND c.dayOfYear = 1
    ORDER BY Id, startDateTime, endDateTime

END

ELSE
    RAISERROR( 'Invalid period specified (%s)', 16, 1, @period )
GO


How do I monitor bandwidth used by SQL Server on port 1433? I have a single server running a .NET web application and a SQL Server database (2008 Standard). I'm planning to move the database onto a separate server but in order to provision the network hardware I'd like to benchmark the data throughput between the web application and the database. Can port 1433 be monitored internally? If so is there any tool native to Windows 2008 R2 that can do this, or would I need some 3rd party application like WireShark? My connection string is referencing the database using server=localhost, is it still possible to tap into 1433 to see the bandwidth used on this port?

Essentially I'm trying to determine if I need a Gbit or 100 Mbit connection between the web server and database server. Any thoughts on this would be much appreciated.

UPDATE 8th July

I hashed out the above as I realise it's largely irrelevant. For some reason I thought there would be a big cost difference between a 100 Mbit and 1 Gbit switch. Only the cheapo home user switches are 100 Mbit. As others have pointed out, Wireshark will not pickup activity between IIS and the SQL server on the same box. I'm putting in a 1Gbit managed switch for now and I'll use either Wireshark or the built in monitoring on the switch to see what's going on later. I don't think it will be anywhere close to the physical transfer limits imposed by the hardware.

Not aware of any built-in method to record bytes over a particular port so if you really need that level of detail, Wireshark is a good bet.

If the server is dedicated to SQL Server I'd be reasonably confident that the majority of traffic over the network was related, so the perfmon counter Network Interface\Bytes Total/Sec should give you a broad guideline.

To be honest it's a mute point in my book. Unless there is a large and nonsensical additional cost for the gig port where your servers are hosted, take it.


Because it is a kind of bug in the old MySQL version. UNION effect using indexes.
In the versions older than the 5.7.3 version of MySQL, UNION statement works with a temporary table. It means, first of all, your data move to the temporary table and read them from the temporary table before that implement filtering.

The server no longer uses a temporary table for UNION statements that
meet certain qualifications. Instead, it retains from temporary table
creation only the data structures necessary to perform result column
typecasting. The table is not fully instantiated and no rows are
written to or read from it; rows are sent directly to the client. The
result is reduced memory and disk requirements, and smaller delay
before the first row is sent to the client because the server need not
wait until the last query block is executed. EXPLAIN and optimizer
trace output will change: The UNION RESULT query block will not be
present because that block is the part that reads from the temporary
table.

Check out version 5.7.3 optimizer fixes.


UUID type for NEWID() Does the NEWID() function generate UUID version 4?

Link to Wikipedia with versions

What is the situation with the NEWSEQUENTIALID() function?

Does the data always provide us with the UUID standard and version? Maybe it should be generated on the application side?

Following to the Wikipedia page that you mentioned above (in the Database key section):

The NEWID function in Microsoft SQL Server version 4 Transact-SQL returns standard random version-4 UUIDs, while the NEWSEQUENTIALID function returns 128-bit identifiers similar to UUIDs which are committed to ascend in sequence until the next system reboot.


Optimizing a query with ORDER BY in a derived table The query below takes too long to execute (58 seconds). If I run two more queries like this with different nid values, the second and third query take much more time to execute. How can I optimize it?

SELECT consoleId,
       servertime,
       servertime AS servertimeUNIX,
       nocName,
       eventIdx,
       nocStatus  AS status,
       nid,
       site,
       machine,
       clientversion,
       timeExecuted,
       count(*)   AS eventCount
FROM   (SELECT consoleId,
               servertime,
               nocName,
               eventIdx,
               nocStatus,
               nid,
               site,
               machine,
               clientversion,
               timeExecuted
        FROM   temptest
        ORDER  BY tid DESC) AS x
WHERE  servertime >= 1367902800
       AND servertime <= 1370581199
       AND nid = 1124
GROUP  BY FROM_UNIXTIME(servertime, '%Y-%m-%d'),
          site,
          machine,
          nid;

The table definition is

CREATE TABLE `temptest`
  (
     `tid`                INT(10) NOT NULL AUTO_INCREMENT,
     `eventIdx`           INT(11) NOT NULL DEFAULT '0',
     `servertime`         INT(11) DEFAULT NULL,
     `nocName`            VARCHAR(50) DEFAULT NULL,
     `site`               VARCHAR(50) DEFAULT NULL,
     `machine`            VARCHAR(64) DEFAULT NULL,
     `clientversion`      VARCHAR(20) DEFAULT NULL,
     `nid`                INT(10) DEFAULT NULL,
     `nocStatus`          VARCHAR(250) DEFAULT NULL,
     `consoleId`          INT(11) DEFAULT NULL,
     `timeExecuted`       VARCHAR(100) DEFAULT NULL,
     `machineManufacture` VARCHAR(100) DEFAULT NULL,
     PRIMARY KEY (`tid`),
     KEY `consoleindex` (`consoleId`),
     KEY `nitification` (`servertime`, `nid`)
  )
ENGINE=InnoDB
AUTO_INCREMENT=8229683
DEFAULT CHARSET=latin1

SELECT consoleId,
       servertime,
       servertime AS servertimeUNIX,
       nocName,
       eventIdx,
       nocStatus  AS status,
       nid,
       site,
       machine,
       clientversion,
       timeExecuted,
       count(*)   AS eventCount
FROM   temptest
WHERE  servertime >= 1367902800
       AND servertime <= 1370581199
       AND nid = 1124
GROUP  BY FROM_UNIXTIME(servertime, '%Y-%m-%d'),
          site,
          machine,
          nid;


Get a listing of snapshot data files I need to programmatically drop a database with all of its snapshots and then recreate the database and create a new snapshot.  I'm having trouble locating where the snapshot files are located from within a query.

Essentially we have several environments and they are not consistent on where they place these files so I need my code to be smart enough to put these files back where they were.

Just to clarify: When I say the snapshot files I mean the .ss files.

You can identify database snapshots in sys.databases by the column source_database_id being not null.

Join it to sys.master_files and you're done:

SELECT mf.database_id,
    dbs.source_database_id,
    mf.type_desc,
    mf.name,
    mf.physical_name
FROM sys.master_files AS mf
INNER JOIN sys.databases AS dbs
    ON mf.database_id = dbs.database_id
WHERE source_database_id IS NOT NULL

In case you need to filter for one specific source database, you can add that predicate to the WHERE clause.


How to avoid typecasting leading to full table update? Is there a way by which update to ALL rows of a Mysql table could be denied or avoided?

Background:

I have a table 'Orders' having 'OrderID' (Type VARCHAR) and 'VendorID' (Type Integer) as two of the columns. If I run the below query on this table, it simply resets VendorID in all the rows:

SET `VendorID` = 0 WHERE `OrderID` = 0;

OrderID should not be 0 but because of some bug in the system, it escaped all the checks and created havoc. Had OrderID been of Integer type, this query would not have done anything. But since OrderID has alphabets, it could not be set as Integer type.

Can Mysql be configured in such a way that it rejects Updates to all rows? Would result of this query change if underlying database is not Mysql?

My environment:

nginx/1.4.6 Codeigniter (2.x) / Mysql (5.5) / InnoDB table type

Please suggest how this issue could be avoided in future, thanks.

If you want you can use small trick

update urtable set  `VendorID` = 0 where `OrderID` = 0 and
(select count(*) from urtable)<>(select count(*) from urtable where `OrderID` = 0)


Defrag or Backup+Restore I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime.

I have a plan that I am not sure will work:

Normally defrag the partition while the service is still running until the only fragmented files are the database files.
Enter single user mode.
Make complete backup to a different partition.
Delete the database with fragmented MDF file.
Create/restore new database from the recently created backup.

Will dropping database and then restore from backup remove the file fragmentation?

Or should I just turn off the service and run defrag tool until it finishes?

There is a great discussion of this topic here at ServerFault in which the 3 forms of fragmentation are discussed.  I always have to remind myself to search for the answer before I post a question as it saves me so much time.

Short answer (tl/dr):

It will remove physical fragmentation but not logical or VLF fragmentation within the log file.

Physical Fragmentation:

Backing up and restoring will remove any physical fragmentation.  That is, fragmentation that occurred on the NTFS partition as a result of your database growing incrementally over time and contiguous space not being available as a result.

Other Fragmentation:

You will still have index fragmentation and VLF Fragmentation within the database.  Paul Randall stressed not using Windows Defrag at his SQLSkills Immersion Events, and how it could lead to database corruption so I think you're on the right track.

Background:

Physical fragmentation will operate on the disk where the MDF/NDF/LDF files occupy.  Since SQL Server does not track this information as it would be useless.  We want to be able to restore databases to any hard drive as long as it has space, we're not concerned with where it gets located on the disk so much unless you're short writing...but that's a exception to the rule.


How to know DB size using backup file without restoring it Is there anyway so that we can estimated/know size of database using backup file without restoring it?

Yes, there is, though you got to use RESTORE command anyway. Instead of RESTORE DATABASE, use RESTORE FILELISTONLY to get a detailed view of files in the backup. The Size column tells the file size in bytes.

RESTORE FILELISTONLY FROM DISK = N'v:\MyBackup.bak'


Help understanding the password file I have one database and password file
Now I'm creating a new database, do I need to create another password file or not?

I'm a beginner in Oracle, maybe a bit stupid question.
Thanks in advance

The password file is an option for authenticating remote users with special privileges. A database can operate without a password file. So no, it is not mandatory.

Typically all databases have their own seperate password file. If you create the new database with DBCA, that automatically creates the password file for the new database.

Some companies simply do not create password files for their databases and disable password file authentication in order to lower security risks. Think of it as disabling root login through SSH.

Features like Data Guard rely on password file authentication. You can create the password time now or later, it is up to you.


Emulating Oracle flashback functionality I'm considering going from Oracle Enterprise Edition (EE) to SE/SE1 but want to retain the flashback database and flashback table features from EE.

Does anyone know how to emulate these features in SE/SE1?

Thanks in advance

You could try with replicating you changes to change tables. Dbvisit replicate could be of good value here. See Real-time Oracle data and database replication It is not a free tool but it saves a lot compared to running EE.


How can I design Person table in MySQL? I have two person type: Users and Clients.
Users can login the panel but clients can't login the panel.
My current database: (but this design not good for me. I need to connect these tables to the Persons table.)
My database:
You can see these tables storing same data like: firstName, lastName... but the difference is that the customer does not have a password.

How can I connect these tables to Persons table.

Users can login with email and password.
Client can't login but have email for contact.

I would be happy, If you answer it with a database diagram.

simply, use only one table with a column isclient, which determines if you can log in.
Or if you have lots of more columns for a client a column clientid, which is Null if user is no Client and else use the  unique id to the table with clientmoreinformation.
Generally don't have redundant columns or tables.


Cannot grant execute to stored procedure When I try to GRANT EXECUTE ON SP_NAME TO [DOMAIN\USER_NAME] on my database, I'm getting the error message below:

Msg 8115, Level 16, State 1, Procedure grant_safety, Line 5
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.

This is happening in any procedure where I try to GRANT EXECUTE permissions; I have tried to grant to other users as well, but same error.

ALTER PROCEDURE TEST
AS
BEGIN
   SELECT 1
END

Try putting a GO on a newline between your procedure DDL and your grant statement and after the grant.


Using Solr/Lucene for searching non-text tables? I am creating a web application to retrieve subsets of one large (4m rows) table. The 4m rows only change once a year. The table has 200+ columns of types boolean and numeric. It has no text columns.

Users will query subsets of this table for download.

I am rather familiar with the PostgreSQL 9.1 database, and my plan is:

The webapp will help the user to create queries like "WHERE a=3 AND b=true AND c>300"
The DB will have proper indexes for the most-used columns.

NOW.. I read here:
https://stackoverflow.com/questions/10053050/why-is-solr-so-much-faster-than-postgres:

  I recently switched from Postgres to Solr and saw a ~50x speed up in our queries. The queries we run involve multiple ranges, and our data is vehicle listings. For example: "Find all vehicles with mileage < 50,000, $5,000 < price < $10,000, make=Mazda..."

So now I wonder: Will/can Solr, Lucene, ElasticSearch, Amazon Cloud Search searches be faster than PostgreSQL even if no full-text search is involved?

Will/can Solr/Lucene searches be faster than PostgreSQL even if no
  full-text search is involved?

Yes. As per your quoted example, it can be many times faster than a relational database for certain use cases. Not surprising really.

Solr is a search engine. PostgreSQL is a relational database engine.

Solr is built from the ground up to do one thing well, search. It cannot do the work of a transactional database system. Solr cannot guarantee ACID compliance, it does not efficiently manage concurrency, it is not a viable candidate for OLTP workloads.

PostgreSQL is built from the ground up to do one thing well, transactional processing. It can also do a reasonable job of providing search type facilities but that is not its primary role. It does guarantee ACID compliance, it does manage concurrency, it is not a search engine.

  The 4m rows only change once a year.

In which case you probably don't need the key features of a relational database engine.

You need a search engine. Could be Solr, Lucene, ElasticSearch, Amazon Cloud Search or one of a dozens others but first and foremost it will be a search platform, not a relational database.


Possibility of fixing corrupt MySQL database Obviously this wouldn't be an issue if there was a backup, but this database started out as just a test.

Soon it started to be used for more important data and then I made a plan to move it to a production environment and set up some type of regular backups.

Currently I have the prod environment set up and was planning on making the move today. But (as luck would have it) when I tried to get into the server where the DB lives I found I had no connectivity. I opened up a console and saw that there were disk errors. I tried to fix these with fsck but now, although it boots, I can't login and I see all types of errors during boot.

So as a last resort I tried to use an ubuntu live cd to get in and copy some files to another destination.

I noticed when I copy the files in the MySQL datadir, it can't copy the ibdata1 or ib_logfile1. I get the errors:

cp: error reading 'ibdata1': Input/output error
cp: error reading 'ib_logfile1': Input/output error

So before I write this off as a loss (and gain valuable experience with the need for backups) I figured I'd check here to see if there is any way to recover the MySQL data.

"if there is any way to recover the MySQL data." - short answer to this question - it depends on how badly ibdata1 (and rest of important files) are corrupt.

It's a hardware error, IO system call returns Input/output error, so ibdata1 is definitely corrupt.

At minimum you need to copy ibdata1, *.ibd and *.frm files. If simple cp doesn't succeed copy a files with dd ignoring errors.

# dd if=/var/lib/mysql/ibdata1 of=/some/good/disk/ibdata1  conv=noerror

Once you copy all files try to start MySQL with innodb_force_recovery. Try all values from 1 to 6 until MySQL starts. If you are lucky and it starts, take mysqldump. MySQL may crash in the process. Try to dump individual tables.

for d in `mysql -NBe "SHOW DATABASES"`
do
    for t in `mysql -NBe "SHOW TABLES" $d`
    do
        mysqldump --skip-lock-tables $d $t > $d/$t.sql
        if [ $? -ne 0 ]; then sleep 30; fi
    done
done

If MySQL doesn't start - you can upload MySQL datadir in a zip/tar.gz archive on https://recovery.twindb.com/ (DISCLAIMER: I'm the author), it will try to recover the databases and will show you recovered data. (it will ask for a payment if you want to download a dump).

If you don't feel comfortable uploading your data google undrop-for-innodb (DISCLAIMER: I'm the author) and use https://twindb.com/recover-corrupt-mysql-database/ as a guide.

Percona's data recovery toolkit will also work (https://launchpad.net/percona-data-recovery-tool-for-innodb) (keep in mind stream_parser == page_parser, c_parser == constraints_parser). If you use Percona's toolkit make sure you use the latest revision from the trunk (bzr branch lp:percona-data-recovery-tool-for-innodb). Do not use version 0.5 available from downloads!


Exporting Stored PDF into an Email Just a quick question on whether something is possible to do.

Database: MSSQL

We have a customer that we generate emails for based on sales (e.g. Sales Person X sold Y amount of stock). Against these orders the main system generates a PDF stored as a VARBINARY field in a table.

Is it possible for this VARBINARY to be converted into a physical file (as an attachment or similar) within an email?

I have done similar things with images stored in the database but that is a a lot different than PDF.

This is a Third party application i'm working with so I can't make changes their system.

Thanks for Reading,
Joshua

I have done similar things with images stored in the database but that is a a lot different than PDF.

Actually, for this type of operation, there is no difference between images, PDF, or anything. If you have a binary representation of a file and can save it to disk, then that will work for all file types. Bytes are bytes, and as long as the same bytes are written in the same sequence that they were read in (written as binary, not text), then it doesn't matter what those bytes end up being, even if it is a plain text file or image or DLL or mp3, etc.


MySQL. Capture Cis program makes incorrect query: SELECT DISTINCT "PartType" FROM "Capacitor" ORDER BY "PartType" I am having problems to get one database running. I have a MySQL in my server, the server is running "5.5.48-MariaDB - MariaDB Server" with "UTF-8 Unicode (utf8)"

I have a MySQL ODBC 64 bits 3.51 Driver to be able to connect to this database. The ODBC is configure to use utf8 character set.

If I use for example MySQL workbench to connect to this database for example:

SELECT * FROM leomaker_prueba_01.Capacitor;

All is perfect, I get the data. Also work with:

SELECT DISTINCT PartType FROM Capacitor ORDER BY PartType

I think that this is the correct form to make query of this kind.

The problem start with my program: "OrCAD Capture Cis". This program make a connection to a Database to fetch data and use it in the program.

In the console of the program I get this error:

  ODBC Error Description: State:S0001,Native:1050,Origin:[MySQL][ODBC 3.51 Driver][mysqld-5.5.48-MariaDB] SELECT DISTINCT "PartType" FROM "Capacitor" ORDER BY "PartType" ERROR(ORCIS-6245): Database Operation
  Failed  Please Check Session For More Details ERROR(ORCIS-6250): ODBC
  Error Code: -1 Description: You have an error in your SQL syntax;
  check the manual that corresponds to your MariaDB server version for
  the right syntax to use near '"Capacitor" ORDER BY "PartType"' at line
  1

As we see the query that it makes is

SELECT DISTINCT "PartType" FROM "Capacitor" ORDER BY "PartType"

Which is incorrect. I also try it in MySQL Workbench and i get the same kind of errors. I am sure that is problems of the " symbols.

The default database connect to a local SQL server. (but i can't see the querys)

Which could be the problem? I try to use MariaDB ODBC and i get the same error. Also if I try to change of character set.

It could be any form to change the querys to the database?

Thanks

Solved, the problems was the ODBC connector. I have to use the ODBC MySQL 5.1 instead of 3.5, due to my MySQL server uses 5.5.

This solve the problem with the "


Is it worth to separate columns into multiple tables for one-to-one relational table I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table.

The number of columns would be around 45 and I need to sort data on different columns on different query (one sort per query).

I will be using MyISAM storage engine.

Furthermore, there will be millions of data in the table(s).

Another thing to consider in deciding to split the tables or not is the width of the table if you put them all in one table. Many databases will allow you to define a table where the total length of all the fields is wider than the total record length allowed. You cannot however, put data into a record that would exceed the width. Therefore, if you are going over the limit, it might be wise to split the table. Further, most databases can store and retrieve data more efficiently in tables that are less wide. So if the fields you want to split out are not always going to be queried with the fields in the orginal table and the table will be large, it might be in your best interests to split the tables out.

Another thing to consider is if you are in a 1-1 relationship now, is it likely or possible that you will want to change to 1-many later? For instance suppose the fields you want to move are out are address fields. Maybe now you only need one address, but having them in a separate table makes it much easier later to have multiple addresses.

If you split the tables, make sure you define a unique index on the FK to preserve the one-to-one relationship.


Create SP for inserting data from a table to a table I have a table that has 1 record where one field may have 2 values separated by commas (in that cases I have to create 2 records with those values separated in 2 row and the other field values are the same). To do these I had to make some selects but how can I do the stored procedure to do that in a easier way??? When there are 2 telephone number separated with ',' with the first SELECT I insert to table3 the row with the phone number before the ',':

SELECT DISTINCT SUBSTRING(Telefono, 1, CHARINDEX(',', Telefono, 0) - 1) AS Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov
INTO table3
FROM #table1
WHERE Telefono LIKE '%,%'

INSERT INTO table3
--the record with the phone number after the [,]
SELECT DISTINCT SUBSTRING(Telefono, CHARINDEX(',', Telefono, 0) + 1, LEN(Telefono)) AS Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov,Fax
FROM #table1
WHERE Telefono LIKE '%,%'

INSERT INTO table3
--insert all the remaining rows
SELECT DISTINCT Telefono
    ,Nome,Indirizzo,CAP,Comune,Prov,fax
FROM #table1
WHERE Telefono NOT LIKE '%,%'

Before you start thinking about a solution, try to evaluate if your requirements make sense.

Is it really the best solution to create duplicate rows if multiple phone numbers exist or would it be better to create a new table PhoneNumbers with a foreign key?

And splitting data can be done more elegantly in the tool that loads the data into the database. If you have to do it in SQL, the solution depends on the database system you use.
For instance, here is a solution for SQL Server: https://stackoverflow.com/q/5493510/1595345


Performance issues of `fetch first n rows only` Consider a query of the form

insert into new_table (new_column)
select column as new_column
from table
where column like '%pattern'
fetch first 1000 rows only;

table has a sufficient number of entries such that at least 1000 ones satisfy the criterion. What irritates me is that with the criterion commented out, the query runs much more slower (using DB2) than with the criterion enabled. I would have expected it to run faster with less restrictions since less rows would have had to be considered. Where am I mistaken?

not sure i understand correctly. do you mean this is SLOWER or FASTER?

insert into new_table (new_column)
select column as new_column
from table
-- no WHERE clause
fetch first 1000 rows only;

in general, if there is a where-clause, any database engine must read records, and 'drop' those that don't match the where clause. the 'read-loop' must read from disk until 1000 matches are found. the best case will encounter NO non-matches; the worst case will read the complete dataset

without a where-clause, the database engine only has to read 'the first 1000 records', thus every IO operation can be used for the resultset. so, that should be faster.


VARGRAPHIC vs VARCHAR CODEUNITS32 I'm in the process of migrating a number of databases from ISO8859-1 to UTF-8. To fit the old string in new tables I figured there are 3 options:

Multiply the number of bytes with a number the corresponds to the distribution of multibyte characters in the domain, and add check constraints for length validation. I.e CHAR(3) might hypothetically become CHAR(5).
Declare column as CHAR(3 CODEUNITS32). As far as I can tell this will occupy 12 bytes
Declare column as GRAPHIC(3). As far as I can tell this will occupy 6 bytes.

I don't really consider 1 an option, that leaves us with 2 and 3. When would 2 be preferable over 3? I think I've seen some info where some characters may not map correctly from CHAR to GRAPHIC, but this seems to work fine for characters from ISO8859-1. Other than that, am I missing something or would GRAPHIC always be preferably over CHAR CODEUNIT32?

The GRAPHIC data type seems to be a remnant from the times when there was no widespread Unicode support. It was there to facilitate storage of double-byte characters. In essence GRAPHIC(3) is equivalent to CHAR(3 CODEUNITS16).

Apart from the length semantics (1 byte vs. 2 byte characters) (VAR)CHAR and (VAR)GRAPHIC are functionally equivalent. Now that you have an option of specifying the length units along with the data type, I really see no reason to use the GRAPHIC types.

Since a UTF-8 character can occupy anywhere between 1 to 4 bytes, using any fixed-number-of-bytes character assumption would cause either risk of truncation or wasted space. To avoid the latter you might consider using VARCHAR instead of CHAR if your application can tolerate that.


Update existing data with excel import I have user table in my DB. I have 1 excel sheet having list of some users (not all) with changes. How can I Update the values given in excel sheet to my user table? is it possible with import data option?

I only learned this in the past year.  You can query an excel sheet as if it's a table.  As long as your keys are also in excel you can join the excel sheet to the user table.

You can have a look at this Microsoft Article that discusses options for importing from Excel to SQL Server.

http://support.microsoft.com/kb/321686


ORA-27102: out of memory We're facing an Out Memory Problem with an Oracle database SE 12c Release 1 (deployed as a Docker container) ...

The only information that I have is this one:

Caused by: java.sql.SQLRecoverableException: ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory Additional information: 2673
Additional information: 360449 Additional information: 1627389952

I try to google the ORA-27102 but the forum discussions are to broad (to implement any solution will be like shotting in the dark) ... I don't know how to narrow my problem? Does anyone know what those additional information codes  are (I tried to google them without success) ??

Any suggestion will be appreciated!

I'm presuming you don't have metalink as this was the first hit:

SYMPTOMS

Instance not reachable and process dying.

Background processes are dying and oracle is unable to spawn new processes. Clients cannot connect and they receive error:

java.sql.SQLRecoverableException: ORA-01034: ORACLE not available
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
Additional information: 2673
Additional information: 488341508
Additional information: 1744830464

When this happens local connections are not possible, but already existing connections continue to work. The problem appears when running backups or complex queries then it disappears.

No specific memory related error is reported on the server log just logs like this one:

Errors in file /u01/app/oracle/diag/rdbms/alma/ALMA2/trace/ALMA2_cjq0_6234.trc:
Process J000 died, see its trace file
Thu Apr 06 14:25:40 2017
kkjcre1p: unable to spawn jobq slave process   <=============  important symptom
Thu Apr 06 14:25:40 2017
Errors in file /u01/app/oracle/diag/rdbms/alma/ALMA2/trace/ALMA2_cjq0_6234.trc:
Process PPA2 died, see its trace file
Process J000 died, see its trace file
Thu Apr 06 14:25:42 2017
kkjcre1p: unable to spawn jobq slave process

CAUSE

This is was investigated in

  Bug 24921392 CJQ FAILED TO SPAWN SLAVE PROCESS KKJCRE1P: UNABLE TO
  SPAWN JOBQ SLAVE PROCESS

The problem may be seen if MEMORY_TARGET is used and memory or swap space is under pressure.

SOLUTION

Apply patch 24921392 if available

OR

As a workaround, increase memory and/or swap on the affected system to avoid the issue.


How to count repeated rows taking into account other rows in the middle For SQL server 2008 R2
It seems challenging to come up with a single query to the following:
Example given columns a & b:

a |   b
-------------
0 |  2000
1 |  2001
1 |  2002
1 |  2003
2 |  2004
3 |  2005
1 |  2006
1 |  2007
4 |  2008
1 |  2009

Goal: Mark rows with repeated column a and give them unique number taking into account other values in between. Result should be in column c. Note the most difficult part here is to populate column c with 2 & 5 & 7.

a |  b   |  c
-------------
0 |  2000 | 1
1 |  2001 | 2
1 |  2002 | 2
1 |  2003 | 2
2 |  2004 | 3
3 |  2005 | 4
1 |  2006 | 5
1 |  2007 | 5
4 |  2008 | 6
1 |  2009 | 7

This is a gaps-and-islands problem. One (of the many) ways to solve it (this requires 2012+ versions):

WITH
  t AS
    ( SELECT a, b, x = CASE WHEN a = LAG(a) OVER (ORDER BY b)
                           THEN NULL ELSE 1
                       END
      FROM table_name
    )
SELECT a, b, c = COUNT(x) OVER (ORDER BY b)
FROM t
ORDER BY b ;

This should work in 2005 and above:

WITH
  t AS
    ( SELECT a, b, dx = ROW_NUMBER() OVER (ORDER BY b)
                        - ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)
      FROM table_name
    ),
  tt AS
    ( SELECT a, b, mb = MIN(b) OVER (PARTITION BY a, dx)
      FROM t
    )
SELECT a, b, c = DENSE_RANK() OVER (ORDER BY mb)
FROM tt
ORDER BY b ;


MySQL: Sum if rows are equal I have a table with 4 columns: Client , thickness , material and amount.

I want to make a SQL select-query to select everything, but when the client, thickness and material are the same their value of 'amount' should be added.

How would I go about creating such a query?

This is straightforward (a simple GROUP BY):

SELECT Client, thickness, material, SUM(amount) as amount
FROM your_table
GROUP BY Client, thickness, material;

Change your_table for the name of your table. If only 1 row exists of one combination of Client, thickness and material, it will select it as is.

Check the GROUP BY MySQL page for more information.


REST webservice as a Foreign Data Wrapper in postgresql I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find any resources on how (or if it's possible at all) to embed the XML output of a REST API in a foreign table.

Anyone know if this is possible and if so has any leads on how to implement it?

I actually had a similar question and found a simple solution that worked for me.
I took Multicorn and wrote a couple of lines of python in order to import a REST API:

Given a REST API

curl  -H "authentication:ZQzBedExV8YGEztzUJdfqe0nsGNGdstZ" -H "Content-Type: ap/json;charset=UTF-8"  -X POST  http://127.0.0.1:3000/system_module/list.json  -d '{}'

I wrote the following simple class:

from . import ForeignDataWrapper
from .utils import log_to_postgres
from logging import INFO
import json
import urllib2
def anubis(apikey):
    req = urllib2.Request('http://127.0.0.1:3000/system_module/list.json')
    req.add_header('apikey', apikey)
    req.add_header('Content-Type', 'application/json;charset=UTF-8')
    resp = urllib2.urlopen(req)
    results = resp.read()
    results = json.loads(results)

    items = results['items']
    for item in items:
        yield {'name': item['name'].encode("utf-8"),
               'description': item["description"].encode("utf-8"),
               'apikey':apikey}
class AnubisFdw(ForeignDataWrapper):
    def execute(self, quals, columns):
        if not quals:
            return (None, None, None)
        for qual in quals:
            if qual.field_name == "apikey" or qual.operator == "=":
                return anubis(qual.value)

Download Multicorn http://multicorn.org
have a look in the subdirectory python/multicorn
In my case a had to retrieve the data form a REST API returning JSON arrays. Thus I had to parse the josn data returned by the REST API. But in your case you'll have to parse your XML data which should not be a big deal in python.

Best Regards

Babak


mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

I am able to loging properly and able to take full backup from SQLyog Tool but when i am trying to take backup from MySQL Enterprise Workbencg then getting error message.

  mysqldump: Got error: 1044: Access denied for user 'myuserid'@'%' to
  database 'mydatabasename' when doing LOCK TABLES

Operation failed with exitcode 2

And MySQL Connection status is ok.

  mysql> status;

  mysql.exe Ver 14.14 Distrib 5.6.19, for Win32 (x86)

  Connection id: 23921 Current database: databasename Current user: user
  id is ok here SSL: Not in use Using delimiter: ; Server version:
  5.6.17-log MySQL Community Server (GPL) Protocol version: 10 Connection: connection is ok here Server characterset: utf8 Db
  characterset: utf8 Client characterset: cp850 Conn. characterset:
  cp850 TCP port: 3306 Uptime: 21 days 21 hours 11 min 37 sec

  Threads: 24 Questions: 20500671 Slow queries: 3212 Opens: 121998 Flush
  tables: 1 Open tables: 2000 Queries per second avg: 10.842

I know it's really late, but I found this question when I faced the same problem. So, in case anyone need, I found two possible solutions, either:

your user is missing the LOCK privilege, so you should ask your database administrator to grant it to you
run the same mysqldump command, simply adding the --single-transaction flag, eg. mysqldump --single-transaction -u user -p ...


MYSQL Master-Slave replication not working with PHP My data gets replicated from the master database when I do it manually but when it gets inserted from PHP into the master database it doesn't get replicated to the slave database.

My Master database - my.cnf

server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_do_db        = database

My slave database - my.cnf

server-id       = 2
log_bin         = /var/log/mysql/mysql-bin.log
relay-log               = /var/log/mysql/mysql-relay-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_do_db        = database
replicate_do_db     = database
slave_net_timeout = 30
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1

My database table setup

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tmdbid` varchar(9) NOT NULL,
  `poster` varchar(256) NOT NULL,
  `title` varchar(128) NOT NULL,
  `tagline` varchar(128) NOT NULL,
  `plot` text NOT NULL,
  `year` year(4) NOT NULL,
  `release` varchar(18) NOT NULL,
  `runtime` char(6) NOT NULL,
  `genres` varchar(256) NOT NULL,
  `actors` text NOT NULL,
  `votes` varchar(5) NOT NULL,
  `mpaa` varchar(6) NOT NULL,
  `file2` text NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `link` text NOT NULL,
  `file` text NOT NULL,
  `link1` text,
  `link2` text,
  `uploaded` text,
  `been_sent_to_folder` text,
  `poster_backup` varchar(256) NOT NULL,
  `saved_poster_name` text NOT NULL,
  `poster140x100` varchar(256) NOT NULL,
  `poster65x95` varchar(256) NOT NULL,
  `poster_orig` varchar(256) NOT NULL,
  PRIMARY KEY (`tmdbid`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

PHP to insert to DB

mysql_query("INSERT INTO `database`.`tmdb` (`id`,`tmdbid`,`poster`,`title`,`tagline`,`plot`,`year`,`release`,`runtime`,`genres`,`actors`,`votes`,`mpaa`,`file2`,`link`,`file`,`poster_backup`,`saved_poster_name`,`poster140x100`,`poster65x95`,`poster_orig`) VALUES (NULL,'".$tmdbid."','".$poster."','".$title."','".$tagline."','".$plot."','".$year."','".$imrelease."','".$runtime."','".$genres."','".$actors."','".$votes."','".$mpaa."','".$file2."','".$link."','".$file."','".$poster_backup."','".$saved_poster_name."','".$finalthumb."','".$imgfinal."','".$imgfinal225."');")...

You have an syntax error in your php code, dbname.tblname and atlast semicolon needed, you had that inbetween

INSERT INTO `database`.`tmdb` (`id`...