InnoDB, What would cause: "db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL" MySQL 5.5.24 .
We have InnoDB tables that grew almost by double (from 50GB to 100GB - file-per-table enabled) in a few weeks due to a project we are working on.
The tables were freshly dumped and restored just before that.
Suddenly I noticed this error in error.log a few weeks ago, it has not reocurred:

"Error "db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL"

Here is the 'show create table';

*************************** 1. row ***************************
       Table: tableABC
Create Table: CREATE TABLE `tableABC` (
  `UID` bigint(20) NOT NULL AUTO_INCREMENT,
  `VERSION` int(11) NOT NULL DEFAULT '0',
  `IMPORT_PAGE_NUMBER` int(11) NOT NULL DEFAULT '0',
  `MODEL_INTEGRATION_IMPORT_INSTANCE_UID` bigint(20) NOT NULL,
  `MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID` bigint(20) DEFAULT NULL,
  `MODEL_INTEGRATION_IMPORT_FIELD_UID` bigint(20) DEFAULT NULL,
  `FIELD_TYPE` int(11) NOT NULL,
  `TICKER_CODE` varchar(12) NOT NULL,
  `FIELD_CODE` varchar(255) NOT NULL,
  `ACCOUNT_NAME` varchar(255) DEFAULT NULL,
  `IMPORT_VALUE` longtext NOT NULL,
  `VALUE_DATE` datetime DEFAULT NULL,
  `IS_HISTORIC` tinyint(4) NOT NULL DEFAULT '1',
  `PERIOD_CODE` varchar(4) DEFAULT NULL,
  `YEAR` char(4) DEFAULT NULL,
  `CONSOLIDATION_PRINCIPLE` varchar(255) DEFAULT 'NA',
  `UNIT_MULTIPLIER` double DEFAULT NULL,
  `CURRENCY_ISO` varchar(3) DEFAULT NULL,
  `LINE_NUMBER` bigint(20) DEFAULT '0',
  `COLUMN_NUMBER` bigint(20) DEFAULT '0',
  `PERIOD_START_DATE` datetime DEFAULT NULL,
  `PERIOD_END_DATE` datetime DEFAULT NULL,
  `PARSE_ERROR` varchar(255) DEFAULT NULL,
  `VALIDATE_ERROR` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`UID`),
  KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
  KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
  KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
  KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
  KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),
  CONSTRAINT `FK_MIIInstanceData_MIIInstance` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`) REFERENCES `ModelIntegrationImportInstance` (`UID`),
  CONSTRAINT `FK_miii_data_miif` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_FIELD_UID`) REFERENCES `ModelIntegrationField` (`UID`),
  CONSTRAINT `FK_miii_data_miif_mapping` FOREIGN KEY (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`) REFERENCES `ModelIntegrationImportFieldMapping` (`UID`)
) ENGINE=InnoDB AUTO_INCREMENT=83483107 DEFAULT CHARSET=latin1

Any ideas?
Do I need to rebuild the indexes? Would running mysqlcheck -a (analyze table) or mysqlcheck -o (optimize/alter table on innodb) or both do this?

You have 6 indexes

  PRIMARY KEY (`UID`),
  KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
  KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
  KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
  KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
  KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),

The PRIMARY KEY is in the gen_clust_index (aka Clustered Index). All secondary index entries include a corresponding PRIMARY KEY entry.

I would mysqldump that table and reload it into a test DB server

Next, I would run CHECKSUM TABLE db1.tableABC; or mysqlchk against db1.tableABC in production and the test DB.

If the checksum values match, you should be OK.

If they do not match or you are not sure, run this on the production server

ALTER TABLE db1.tableABC ENGINE=InnoDB;

This will rebuild the table and its indexes.

If that error ever materializes after this, there may be a data dictionary problem inside ibdata1. Your final solution would be to dump all databases, shutdown mysql, delete ibdata1, ib_logfile0, ib_logfile1, start mysql, reload all data.

I posted this InnoDB Cleanup Process in StackOverflow back on Oct 29, 2010


What GUI tools can be used for PostgreSQL in Mac OS X? I have recently started using PostgreSQL with my rails app.

I was looking for a SQL GUI to the PostgreSQL database (for Mac).

Anything better than pgadmin3?

Check the wiki page, many different tools available.


MySQL large databases, unique index effect on efficiency? I'm working on a table which holds around 300k records. I'm using a 3 columns unique index to prevent duplicate records. For some reason the database is taking long to insert new rows.

I have a few questions regarding UNIQUE indexes and efficiency:

Will a UNIQUE index make a table much slower?
Would it be useful to store old rows in another table and reduce the table to around 1k records?
And most importantly is there any way to know how long my db takes to insert a row? In phpMyAdmin I can see the "time to process query" but only on SELECT queries

For write operations (e.g. INSERT and DELETE) having a composite index will slow things down. However, MySQL can handle tables with MANY more rows than that. If you need to keep the row count to 1k to maintain performance then I would suggest something is very wrong somewhere. Regardless, I would strongly suggest not breaking the data up in this way, you're just giving yourself a maintenance headache further down the line...

What datatypes are the columns that make up the composite index?


How to take physical backup of MYSQL database I am trying to find out some tool which can take physical backup of MYSQL database. Like mysqlbackup but it is not open source. There is mysqldump but i want to take physical backup. Can someone please suggest me some tool to take backup of MYSQL db on windows. And i am using Innodb as my database engine.

You literally do not need a third party tool.

STEP 01

Find out the path to the data by running

mysql> SHOW GLOBAL VARIABLES LIKE 'datadir';

For this example, let's suppose it is C:\Program Files\MySQL\MySQL 5.6\data

STEP 02

Pick a destination folder to copy the data directory

For this example, let's pick D:\MySQLBackup

STEP 03

Create batch file called C:\RunMySQLBackup.bat with the the following commands

@echo off
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS%
set MYSQL_DATA=C:\Program Files\MySQL\MySQL 5.6\data
set MYSQL_BACK=D:\MySQLBackup
mysql %MYSQL_CONN% -ANe"FLUSH TABLES; SET GLOBAL innodb_fast_shutdown=0;"
net stop mysql
xcopy /s %MYSQL_DATA%\*.* %MYSQL_BACK%\.
net start mysql

CONCLUSION

Just run C:\RunMySQLBackup.bat on demand or add it to the Windows Scheduler. This is just a rough backup script and it only keeps one copy.

This is not the first time I discussed this. I have another post MySQL backing up MyISAM tables by copying the physical files - and automating the process for copying an all-MyISAM database.

You should think about doing mysqldumps since you can get the data at a point-in-time. Should you want to go back to mysqldumps, I have another post I wrote on how to do mysqldumps in Windows with named backup filenames based on the data and time (mysqldump with automatically generated export name).


Adding columns to production tables What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:

  The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end.

(http://msdn.microsoft.com/en-us/library/ms190273.aspx)

On a large table with millions of rows, this can take a while. Is taking an outage the only option? What's the best way to handle this kind of situation?

"It depends"

If you add a column that does not require adding data to the rows, then it can be quite quick.

For example, adding an int or char requires physical row movements. Adding a nullable varchar with no default shouldn't (unless the NULL bitmap needs to expand)

You need to try it on a restored copy of production to get an estimate

Creating a new table, copying, renaming may take longer if you have to re-add indexes and keys on a billion row table.

I have changed billion row tables that took a few second to add a nullable column.

Did I say to take a backup first?


Service mysql shutdown The mysql service was stopped and in mysql log I saw these warning:

161125 16:41:10 [Warning] IP address 'x.x.x.x' has been resolved to the host name 'x.x.x.x.xxx.xxxxxx.com', which resembles IPv4-address itself.
161125 23:58:23 [Note] /usr/sbin/mysqld: Normal shutdown

Notice:

with iptable, only the server address is accepted for port 3306
In my.cnf I set the bind_address = server_adress_ip: bind_address = x.x.x.x

Why the mysql service was stopped?

It's a WARNING and a NOTICE, not an ERROR. Neither of them are responsible for the shutdown. I assume however you're getting this message because in your /etc/hostname (or dns) you have a polluted entry.

As for why mysql cares what the name resolves to, here is a post on the mailing list.

  This warning seems to come from MySQL ACL code. The reason is that
  reverse DNS lookup returns hostname which resembles IPv4 address
  (hostname starts with '98.158.'). One way to get rid of this warning
  and DNS lookups is to put

  skip-name-resolve

  in my.cnf. However, in this case you can use only IP addresses in
  MySQL grant tables. For more information see http://dev.mysql.com/doc/refman/5.5/en/dns.html.

If this is by intent, you may want to read this bug


What to do after deleting a million records We are implementing a new feature in our system that will cause about a million records (each record is tiny, basically a GUID, a date, and four smallint fields) to be purged from a table every night. Basically it's a caching table, and once the data is 7 days old we do:

DELETE FROM scheduleCache WHERE schDateCreated < '2013-08-26

This will run every night at 1am, and will purge about a million records every time it runs.

Is there anything I should be noting or doing for a table like this? Any properties I should put on the table, or any routines I should run regularly to "clean up"? I've never dealt with a table like this before.

The table has a single clustered index (GUID + one of the smallint fields), and we have a weekly index rebuild that runs Sunday mornings.

The problem of deleting large portions of a table is far from a trivial problem. The best approach, by far, is partitioning. A daily partition scheme with a sliding window is really a magic bullet for this problem, see How to Implement an Automatic Sliding Window in a Partitioned Table.

If you cannot afford partitioning (eg. non-enterprise license on site) then I would recommend clustering by schDateCreated. If you need primary key on the GUID+smallint then move it to non-clustered. Delete in batches (eg. TOP 10000), in a loop, to reduce pressure on the log. Consider updating stats after the operation.


Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency I've read a ton over 300 pages of db setups in the last day but I feel a little overwhelmed with all of the potential options and lack of documentation on how PostgreSQL triggers function across replicated databases.

Thus I'm reaching out to the world experience pool before I set db presence. If I've completely screwed this up I'm totally open to new ideas.  Thanks!

Refer to http://i.stack.imgur.com/HiewA.png while I try to explain what I'm trying to accomplish.
All of my on-site client locations and offsite db's are running PostgreSQL 9.0.5.
Arrows are all internet connections.

I have "location terminals" (red boxes) connecting to each database for their location.   Our database programming is pretty intense as we have lots of triggers that push data across sockets to all the connected terminals (red boxes).

I would like to be able to replicate (as close to real time as possible) all this data from our "client locations" to our "offsite DB".  Each "client location" would have it's own database on our main server.

There are times when "remote users" (orange boxes) need to connect to my "offsite db" to access/change data.  Changes that are taking place on "offsite db" can start database triggers that need to be reflected back to the "client location DB".  Triggers occurring n the "client DB" need to be pushed to these remote users as well.

Keeping all the data between the remote db and the site db consistent is important - keeping calculated fields like unique id's and primary keys the same on each database is extremely important for optimization/performance.

I would also like to have some kind of streaming replication running on the offsite database to have a backup in-case anything goes down.  Having HA isn't critical but if it's easy to implement I'd like to do that as well.

I'm trying to do this with the least amount of 'moving parts' to keep maintenance support costs as low as possible.

Thanks!

Streaming replication fires the triggers on the master only.  The slaves never see the actual table writes, but rather re-run the recovery logs from the master.  Because this is essentially a recovery situation, it means that the binary tables are guaranteed to be brought up to a consistent level.  No triggers are necessary.  Triggers may be possible with Slony or Bucardo but I am not 100% sure.  My initial thought suggests these should allow triggers in downstream databases but you don;t want to run the same triggers on both databases!

In current releases you can't cascading replication outside of Slony.  In 9.2 streaming replication will allow for cascading.

Hope this helps.


Postgresql pg_cron job never executed I added pg_cron extension to my database (postgresql 9.6) to schedule a job.
I created a job to be executed every day at 3:30 am:

SELECT cron.schedule('30 3 * * *', $$create table  tbl_new as select  * from temp$$);

I can see that this job stored in the database:

mydb=# select * from cron.job;
 jobid |  schedule  |                        command                         | nodename  | nodeport |    database    | username
-------+------------+--------------------------------------------------------+-----------+----------+----------------+----------
     7 | 5 17 * * * | create table  tbl_new as select  * from temp | localhost |     5432 | mydb           | postgres
(1 row)

The problem is that the job is never executed and I there is no error in the error log.

Please advice how to fix this problem.

The tasks initiated by pg_cron must be able to connect with the
parameters mentioned in cron.job, without a password.

If steps have not been taken in pg_hba.conf or through a .pgpass file
in the server's environment to allow for that, the task won't be able to run.

Concerning the log file, error reports would be expected there, but
pg_cron logs at the LOG level. Maybe it's below your current level
(see log_min_messages) or there's another problem.


Is "deadlock detected" really an error? Should I be suppressing them after handling them? A while ago, I spent a nightmarish number of weeks dealing with "deadlock detected" and trying to figure out how to handle it. I ended up finally handling it in such a manner that my code is able to detect when it happens and then retry the same query indefinitely with 50000 microseconds in between each retry, until it works.
Maybe that's bad practice, but so far (months), it has not caused any problems except for logging the "deadlock detected" so-called "errors".
Is it "OK" for me to now suppress the "deadlock detected" errors by marking them as "unimportant" and thus not showing up to me, even if they are still logged into my error log table?
Please don't tell me to "avoid them in the first place". This is simply not possible. They will apparently happen if you do concurrent (multiple processes/script instances) working on the same table/thing. I spent forever trying to "code them away", but it just doesn't seem possible.
Obviously, since I'm asking this instead of just adding the ignore rule and being done with it, I do care about the answers/responses. Still, I don't think I can be convinced at this point that they can be avoided completely. I'm not saying that I get thousands of them logged every hour or anything, but a few each day, seemingly always in the beginning when I do have lots of concurrent processes working on the same table/query.

A deadlock is a kind of serialization error: you didn't do anything forbidden, it just so happened that there was an interaction with other active transactions that prevented your transaction from being completed. Your reaction is the correct one: retry the transaction. There is absolutely no need to wait before retrying.
I concur that with a sufficiently complicated workload of bigger transactions it can be nigh impossible to rule out deadlocks completely. As long as they happen only rarely, they are not a real problem if as you handle them correctly.
Deadlocks start becoming a problem if they happen too frequently: it means that you have to redo much work, which is bad for performance and puts extra load on your database. Also the one second wait before the deadlock is resolved means that locks are held for a long time (a second is long), which is not great for concurrency.
Even if you cannot completely get rid of deadlocks, you can take measures to reduce them:

see that your transactions are short

try to reduce the number of data modifications per transaction

Both will reduce the likelihood of running into a deadlock.
It is safe to ignore deadlock errors in your log file, but then you should monitor pg_stat_database.deadlocks and take action of the deadlock count per hour increases beyond an acceptable number.
You seen to take objection to calling a deadlock an error. By definition, an error is a condition that aborts the execution of an SQL statement. So a deadlock is clearly an error.


PostgreSQL 11: Replace function body by users from the same group In the documentation of CREATE FUNCTION is stated that ''You must own the function to replace it (this includes being a member of the owning role).''

Two questions:

I don't understand what exactly the ''this includes being a member of the owning role'' part means.
Is it possible to have multiple users (non superusers) that can modify the body of the same set of functions? I have a user which creates the functions, tables etc (Liquibase user) and I want to add a debug user which should have the permission to modify functions created by the first user in order to test some assumptions or hotfixes?

Consider this example setup:

CREATE ROLE function_creator;

CREATE USER radu;
CREATE USER ion;

GRANT function_creator TO radu, ion; -- this is 'being a member of the owning role'

GRANT ALL ON SCHEMA here_live_the_functions TO function_creator;

Then, when you create functions:

SET ROLE TO function_creator;

CREATE OR REPLACE FUNCTION ...;

After all this, you and ion will be able to change (redefine) the function, which is owned by function_creator.

If you need the debug user, you can add one more level:

CREATE ROLE debugger;

GRANT function_creator TO debugger;

-- and instead of granting function_creator to the actual users, do
GRANT debugger TO radu, ion;


Executing a stored procedure on remote database I have 2 databases on the same server. DB-A and DB-B. DB-B has a user that has read and write access to database B. I would like to grant the user in DB-B execute permissions to 2 stored procedures in database A. I was able to do this when the databases were on seperate servers by setting up a linked server, and mapping a linked login. I am having trouble with this when the databases are on the same server. Can someone please tell me how I can accomplish this task.

Thanks,
James

You don't need to create linked logins or anything like that. For a local database you can just add the user to DB_A from the login, then grant exec to the two procedures. First:

CREATE DATABASE DB_A;
GO
CREATE DATABASE DB_B;
GO

-- create a login:
CREATE LOGIN DBB_Owner WITH PASSWORD = 'foo',
  DEFAULT_DATABASE = DB_B,
  CHECK_POLICY = OFF;
GO

-- make DBB_Owner the DB_B owner:
-- step is not necessary, just trying to match your scenario
ALTER AUTHORIZATION ON DATABASE::DB_B TO DBB_Owner;
GO

USE DB_A;
GO

-- create a local user from the server login:
CREATE USER DBB_Owner FROM LOGIN DBB_Owner;
GO

Now create three procedures. For the first two, give explicit exec rights to DBB_Owner, and don't allow them any permissions on the third.

CREATE PROCEDURE dbo.Proc1
AS
    SELECT 'Yes! - from Proc1';
GO
CREATE PROCEDURE dbo.Proc2
AS
    SELECT 'Yes! - from Proc2';
GO
CREATE PROCEDURE dbo.Proc3
AS
    SELECT 'No! - from Proc3';
GO

GRANT EXECUTE ON dbo.Proc1 TO DBB_Owner;
GRANT EXECUTE ON dbo.Proc2 TO DBB_Owner;

Now connect to the server as DBB_Owner with the password foo, and try to execute the three procedures:

EXEC DB_A.dbo.Proc1;
EXEC DB_A.dbo.Proc2;
EXEC DB_A.dbo.Proc3;

--or

USE DB_A;
EXEC dbo.Proc1;
EXEC dbo.Proc2;
EXEC dbo.Proc3;

Results:

Yes! - from Proc1
Yes! - from Proc2
Msg 229, Level 14, State 5, Procedure Proc3, Line 1
The EXECUTE permission was denied on the object 'Proc3', database 'DB_A', schema 'dbo'.


Grouping rows on field A or field B I have data structured as follows:

create table T
( field_1 char(1) not null
, field_2 int not null
,    primary key(field_1, field_2)
);

insert into T (field_1, field_2)
values ('A',1)
      ,('A',2)
      ,('B',3)
      ,('C',3)
      ,('D',4)
      ,('E',4)
      ,('E',5)
      ,('F',6)
      ,('G',1);

I would like to obtain sets that have either field_1 or field_2 in common, like this:

field_1     | field_2   | set
-------------------------------
A           | 1         | one
A           | 2         | one
G           | 1         | one
-------------------------------
B           | 3         | two
C           | 3         | two
-------------------------------
D           | 4         | three
E           | 4         | three
E           | 5         | three
-------------------------------
F           | 6         | four

Basically, rows having common values in field_1 OR field_2 should be assigned to the same ID. I feel like this is a very basic operation but for some reason I cannot wrap my head around the proper way to do it.

Any clues?

Here's one attempt that I'm unsatisfied with for several reasons, but it's a start. The idea is to calculate the transitive closure of the graph and then use the minimum field_2 for each field_1 as the group. The latter uses the fact that for two nodes either share the same closure, or the closures are disjoint. As an example:

{(a,1),(a,2),(b,2),(b,3)}

is an impossible transitive closure since if a and b share 2, they must also share 1 and 3:

I used Db2 (which does not support the keyword recursive in CTE as well as ansi join in the CTE)

CREATE TABLE t (x char(1) not null, y int not null, primary key (x,y));
INSERT INTO t (x,y)
VALUES ('A',1)
      ,('A',2)
      ,('B',3)
      ,('C',3)
      ,('D',4)
      ,('E',4)
      ,('E',5)
      ,('F',6)
      ,('G',1);

with tc (field_1,field_2,n) as (
    select field_1,field_2,0 from t
    union all
    select t1.field_1, t3.field_2,n+1
    from tc as t1, t as t2, t as t3
    where t1.field_2 = t2.field_2
      and t1.field_1 < t2.field_1
      and t2.field_1 = t3.field_1
      and t2.field_2 < t3.field_2
      and t1.field_2 < t2.field_2
      and n <= (select count(1) from t)
), min_closure(field_1,field_2) as (
    -- remove duplicates
    select distinct field_1,field_2 from tc
)
select t.field_1, t.field_2
     , ( select min(field_2)
         from min_closure mc1
         where t.field_1 = mc1.field_1 ) as grp
from t
order by grp;

A           1           1
G           1           1
A           2           1
B           3           3
C           3           3
D           4           4
E           4           4
E           5           4
F           6           6

The group can be further adjusted, but I'll leave it like that.

Major problem is that the CTE only sees the previous iteration, so it is very likely that we will add a lot of redundant edges to the tc. For a large graph that won't be very pleasant.

Edit: attempt to minimize impact by only looking in one direction in graph

Example of a longer chain:

delete from t;
insert into t (x,y) values ('F',5),('F',6),('G',6),('G',7),('H',7),('H',8);
with tc (x,y,n) as (select x,y,0 ...)
select distinct t.x, t.y, min(mc1.y) over (partition by mc1.x) as grp
from min_closure mc1 join t
    on t.x = mc1.x order by 3;

F           5           5
F           6           5
G           6           5
G           7           5
H           7           5
H           8           5

EDIT: adjusted for PostgreSQL dialect:

with recursive tc (field_1,field_2,n) as (
    select field_1,field_2,0 from t
    union all
    select t1.field_1, t3.field_2,n+1
    from tc as t1
    join t as t2
        on t1.field_2 = t2.field_2
     and t1.field_1 < t2.field_1
    join t as t3
        on t2.field_1 = t3.field_1
       and t2.field_2 < t3.field_2
    where t1.field_2 < t2.field_2
      and n <= (select count(1) from t)
), min_closure(field_1,field_2) as (
    -- remove duplicates
    select distinct field_1,field_2 from tc
)
select t.field_1, t.field_2
     , ( select min(field_2)
         from min_closure mc1
         where t.field_1 = mc1.field_1 ) as grp
from t
order by 1;


Oracle 10g: Is it possible to designate a undo point? I was wondering if it is possible to designate a point in time, then later "roll-it-back" to that state (undo DDL and DML changes). Maybe using a record of changes and then executing the reverse? Does Oracle have any features like this built in 10g or 11g?

Thanks in advance,

Kyle

John has pointed you at the Flashback Query options useful from a developers point of view. If I read you correctly then you are probably looking for the Flashback Database and Restore Point functionality documented here which allows you to do precisely this. Flashback Database does have some requirements over and above the flashback query functionality (for example it requires a flash recovery area to be configured and extra disk space compared to a non-flashback database ).


Optimal table design for a medical center rental service and recurrent shifts I'm trying to figure out the optimal design for the following situation I'm being presented. I'm developing a medical web application with MySQL as db engine.
A medical center has 14-16 offices/rooms that are rented (monthly rate) to doctors from
various medical specialties or areas: oncologists, physical
therapists, general practitioners, psychologists, etc.

All the areas have their session time assignment: oncol. -> 30 minutes, psycho. -> 45 minutes and so on.
All the patients do long treatments, so 99.9% of assigned shifts are recurring through time

So, the question would be:
How could I build the optimal design so I can store:

room assignment for each proffesional from monday to friday. eg:

John (oncologist): MON 15:00 to 20:00, TU: 09:00 to 12:00, WED: 08:00 to 13:00
Mary (psychologist): MON 09:00 to 12:00 and 16:00 to 19:00, THU: 08:00 to 13:00
Michael (general): FRI: 10:00 to 16:00
Henry (dentist): TU: 09:00 to 12:00 and 14:00 to 19:00, WED: 09:00 to 12:00

Each proffesional can change their assigned days (in case there's an office available at the desired day/time) through the months, so I'd be able to keep historical data like:
Which patients had Dr. Phillips treated in Nov. 2013, on which rooms he worked, and which days/hours had been rented to him on that time, etc.

Thanks a lot in advance

You could have a bookings table like this:

bookings
--------
  id (PK)
  user_id (FK to table with all medical professionals)
  room_id (FK to table with all rooms)
  start_date - datetime value
  end_date - datetime value

This structure will let you track which professional is using which room, and when:

bookings
--------
id  | user_id  | room_id  | start_date    | end_date
---------------------------------------------------------
1   | 001      | 123      | 20140101T1030 | 20140101T1045
2   | 001      | 123      | 20140101T1100 | 20140101T1115
3   | 002      | 120      | 20140101T1000 | 20140101T1100

To track historic data, you'll probably want to use "soft" deletes on rooms and professionals so that even if you decide to delete Dr. Phillips from the list of current renters, you can still look up when he used which rooms. Your application will have to be smart enough to not create new bookings with professionals/rooms that are marked as "deleted".

professionals
-------------
  id
  name
  deleted
  (other fields...)

rooms
-----
  id
  deleted
  (other fields)

Example:

professionals
-------------
id  | name       | deleted
----+------------+--------
1   | Dr. Philips| N
2   | Dr. Nick R.| Y

rooms
-----
id  | deleted
----+--------
123 | N
120 | N
837 | Y

To handle recurring appointments, you could start with something like this:

recurring_bookings
------------------
  id (PK)
  user_id (FK to med. professionals)
  room_id (FK to rooms)
  day_of_week (could be FK to table/enum of days of week)
  start_time
  end_time (or you could have a "duration", in minutes, if you prefer)
  start_of_recurrence
  end_of_recurrence

recurring_bookings
------------------
id   | user_id    | room_id   | day_of_week | start_time | end_time | start_of_recurrence | end_of_recurrence
-----+------------+-----------+-------------+------------+----------+---------------------+------------------
1    | 001        | 120       | MONDAY      | 1030       | 1100     | 2014-01-01          | 2014-12-31
2    | 001        | 120       | WEDNESDAY   | 1030       | 1100     | 2014-01-01          | 2014-12-31

The above example shows a user with a recurring booking every monday and wednesday in room 120, running from 10:30 to 11:00 AM. It also shows that the period over which both recurrences are defined runs from 2014-01-01 to 2014-12-31

The tricky part is handling exceptions. For example, suppose a user wants to eliminate only one instance of a recurring booking, because they'll be on vacation that week. One strategy might to think of the recurring bookings table as a rule-set which can be used to populate real bookings into the main bookings table. So that way, you use recurring_bookings to populate all appointments for a user in bookings, and then rmeove or modify them from bookings as needed. This requires you to pre-populate a lot of data into your main bookings table (based on start_of_recurrence and end_of_recurrence), which you may or may not want to do, for recurring appointments. To handle open-ended recurring appointments, it might require a bit more work. Also, the above example assumes all recurring bookings are weekly. Some of them may be daily or yearly or monthly, and may have conditions such as "first Wednesday of every month".

I will admit that I haven't tried to build a system with recurring bookings like this, so this design should be considered a suggestion for a starting point. It will probably need some work. To get an idea of one way this is done, try creating and then manipulating recurring appointments in Outlook (if you have access to it).


Why is 'LOAD DATA INFILE' faster than normal INSERT statements? I've read an article that mentioned we can achieve 60,000 inserts per second by using the LOAD DATA IN FILE statement, that reads from csv files and inserts the data into a database.

Why should it differ from normal inserts?

EDIT:
I reduced the round-trip by calling just one INSERT statement:

INSERT INTO tblname
VALUES (NULL,2,'some text here0'),(NULL,2,'some text here1')
    ,(NULL,2,'some text here2'),(NULL,2,'some text here3')
    .....,(NULL,2,'some text here3000');

What about this?

LOAD DATA INFILE and extended INSERTs each have their distinct advantages.

LOAD DATA INFILE is designed for mass loading of table data in a single operation along with bells and whistles to perform tings like:

Skipping Initial Lines
Skipping Specific Columns
Transforming Specific Columns
Loading Specific Columns
Handling Duplicate Key Issues

Less overhead is needed for parsing

On the flip side, if you are only importing 100 rows instead of 1,000,000 rows, extended INSERT is sensible.

Notice that mysqldump was designed around extended INSERTs for the sake of carrying table design along with data as it performs the injection of hundreds or thousands of rows per INSERT. LOAD DATA INFILE always creates a physical dichomoty between schema and data.

From an application point-of-view, LOAD DATA INFILE is also more insensitive to schema change than extended INSERTs.

One can go back and forth on the good, the bad, and ugly of using LOAD DATA INFILE. No matter which technique you use, you must always set the bulk_insert_buffer_size. Why?

According to the MySQL Documentation on bulk_insert_buffer_size:

  MyISAM uses a special tree-like cache to make bulk inserts faster for
  INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
  INFILE when adding data to nonempty tables. This variable limits the
  size of the cache tree in bytes per thread. Setting it to 0 disables
  this optimization. The default value is 8MB.

For years, I have seen client after client not set this and leave it at 8MB. Then, when they decide to use LOAD DATA INFILE or import mysqldumps, they can sense something wrong. I usually recommend setting this to a moderate 256M. In some cases, 512M.

Once you have a big enough bulk INSERT buffer, using either technique is rendered academic and boils down to personal choice. For applications where you bulk INSERT just 100 rows on demand, stick with extended INSERTs.

In all fairness, saying LOAD DATA INFILE is faster that normal INSERT statements is kind of a loaded statement mainly because configuration is not taken into account. Even if you setup a benchmark between LOAD DATA INFILE and extended INSERTs with a proper bulk_insert_buffer_size, the nanoseconds saved on parsing each row can only yield nominal results at best in favor of LOAD DATA INFILE.

Go ahead and add this to my.cnf

[mysqld]
bulk_inset_buffer_size=256M

You could also set it just for your session before launching extended INSERTs

SET bulk_insert_buffer_size= 1024 * 1024 * 256;

UPDATE 2012-07-19 14:58 EDT

To keep things in perspective, the bulk insert buffer is only useful for loading MyISAM tables, not InnoDB. I wrote a more recent post on bulk loading InnoDB : Mysql load from infile stuck waiting on hard drive


Deleting duplicate rows with condition I have posted a similar question before, but this one is a little modified.

I have a table with 4 columns as shown below.

ITEMCHARGE     CPT4       EFF_DATE    CPT4MOD
326.8         29075       20101204    GO
326.8         29075       20110104    GO
326.8         29075       20110204    GO
326.8         29075       20110406    GO
352.69        29075       20090611    GO
352.69        29075       20090917    GO
352.69        29075       20100614    GO
352.69        29075       20100722    GO

Now, if CPT4 and CPT4MOD match, I need to keep the rows with distinct ITEMCHARGE (with latest EFF_DATE). The output should be like below:

    ITEMCHARGE  CPT4    EFF_DATE    CPT4MOD
       326.8    29075   20110406    GO
       352.69   29075   20100722    GO

There are 2 distinct ITEMCHARGE for the same CPT4 and CPT4MOD. So we took the latest EFF_DATE row for both of them. It would be very helpful If I can get a linq (with lamda) and SQL version for this.

Would that work? SQL version only.

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CPT4, CPT4MOD, ITEMCHARGE ORDER BY EFF_DATE DESC) AS RowNo
    , *
FROM #table
) AS T
WHERE T.RowNo = 1

You can check it here:
http://data.stackexchange.com/stackoverflow/query/260027/delting-duplicate-rows-with-condition


PHPMyAdmin incredibly slow Does anyone else's phpmyadmin take an incredibly long time to load? like 68 seconds after i click a table it finally gets there...!

its also very slow when i click the "add data from text file into table" button...

is there anything that can be done to speed it up?

Are these slow tables under any kind of access from other applications?  If the tables are being placed under an explicit read lock that could be blocking phpmyadmin.  There could be an implicit lock by a large insert going on as well.

If it's taking that long you should be easily able to run a show processlist from a straight command prompt to see if you PMA user is sitting locked, sending data, nor maybe nothing at all on the database side.


EDIT TOP 200 ROWS - conversion error I have a user who is working with a new manufacturing SCADA platform. He has a simple table with the following design:

Column Name       DataType     AllowNulls
identkey (PK)        INT           NO
Move                 INT           YES
Wind                 FLOAT         YES
Traverse             FLOAT         YES

This is the T-SQL DDL for the table:

CREATE TABLE dbo.tbl_Rv8WindProfile
(
    identkey int IDENTITY(1,1) NOT NULL
    , [Move] int NULL
    , Wind float NULL
    , Traverse float NULL
    , CONSTRAINT PK_tbl_Rv8WindProfile
        PRIMARY KEY CLUSTERED (
             identkey ASC
             )
        WITH (
            PAD_INDEX = OFF
            , STATISTICS_NORECOMPUTE = OFF
            , IGNORE_DUP_KEY = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
            ) ON [PRIMARY]
) ON [PRIMARY];
GO

He currently has only 5 rows of data in the table. In the past, he has right-clicked and selected EDIT TOP 200 ROWS and manually changed the value of the column "Move" by entering an integer via the numeric keypad on his keyboard. It has worked (as he says) for years.

In the past few weeks, he has been getting an error which reads:

  No row was updated.
  The data in row 5 was not committed.
  Error Source .Net SQLClient Data Provider.
  Error Message: Conversion failed when converting the varchar value 'Move' to data type int.
  Correct the errors and retry or press ESC to cancel the change(s).

If I edit the data using this SQL:

UPDATE dbo.tbl_xyzProfile
 SET [move] = 4
 WHERE [identkey] = 5

the data is updated.

If I use this query (with a character instead of an INT):

UPDATE dbo.tbl_Rv8WindProfile
 SET [move] = '5'
 WHERE [identkey] = 5

It recognizes the conversion and it still works.

Things I've checked so far:

I've double-checked and it does not appear that MOVE is a reserved word.
It doesn't appear that he has updated his Microsoft SQL Server install in the past few weeks.
It works in some tables for him, but not others and I cannot see any pattern which would explain why.

He is editing the integer data in column named MOVE. In the example shown here, he is trying to change row 4 (identkey = 4) from a integer value of 4 to 6.

I created the table in another database and replicated the data. It also gave me the same error. Then I renamed the column MoveZ and immediately, it let me modify the integers through the EDIT TOP 200 ROWS option.

I did look at the T-SQL reserved words, ODBC reserved words, Future reserved words and C# reserved words and MOVE doesn't appear on any of the lists. I don't find it on any .NET reserved words list either.

Using XEvents Trace, I can see SQL Server Management Studio is getting confused around the [move] column.

The DML query it uses to update the row actually works, however the query it issues to retrieve the updated row looks like:

DECLARE @Param1 int, @Param2 float,@Param3 float;
SET @Param1 = 1;
SET @Param2 = 2;
SET @Param3 = 3;

SELECT TOP (200) identkey, Move, Wind, Traverse
FROM tbl_Rv8WindProfile
WHERE ('Move' = @Param1)
    AND (Wind = @Param2)
    AND (Traverse = @Param3);

This looks like a bug in SQL Server Management Studio (SSMS).  I tested this with SSMS 17.4, SSMS 2016, SSMS 2012; all of them exhibit this behavior.  SSMS is wrapping the [move] column with single-quotes instead of square-brackets or double-quotes as would be expected.

Interestingly, SSMS 2008 R2 won't even open the "Edit top 200 rows" window.  It raises an error-dialog containing the text "Invalid prefix or suffix characters (MS Visual Database Tools)".

FYI, the INSERT statement used by SSMS to insert new rows is:

DECLARE @Move int, @Wind float,@Traverse float;
SET @Move = 1;
SET @Wind = 2;
SET @Traverse = 3;

INSERT TOP (200)
INTO tbl_Rv8WindProfile(Move, Wind, Traverse)
VALUES (@Move, @Wind, @Traverse)

The INSERT and subsequent SELECT statement do not appear to be executed in the same transaction; in my tests, I ended up with multiple rows in the table, one for each error I received.  You may want to manually confirm the table contains the correct data.


Ola Hallengren's backups scripts, cleanup only mode Is it possible to run Ola Hallengren's backup scripts in cleanup only mode? I have a couple of servers where the cleanup time was set too long, and I now have to clean up hundreds of databases worth of backups. I could bang something together with PowerShell or similar, but I'd be more comfortable with some known good code handling it instead.

There are a few ways to try this, but from what I can tell they either won't work, or will still have to do at least part of the backup process, or will require modifications to Ola's stored procedures.
Don't back up any databases?
Looking at the code inside Ola's DatabaseBackup stored procedure, the cleanup portion is inside the loop that goes through all of the databases that are to be backed up. So passing a dummy parameter or just asking it to back up only a small token database isn't going to delete old backup files from the other databases.
Backup to NUL?
You can pass NUL as the directory, but the code has a check for that and won't perform cleanup if you are writing the backups to NUL.
  IF @CleanupTime IS NOT NULL AND EXISTS(SELECT * FROM @Directories WHERE DirectoryPath = 'NUL')
    BEGIN
      INSERT INTO @Errors ([Message], Severity, [State])
        SELECT 'The value for the parameter @CleanupTime is not supported. Cleanup is not supported when backing up to NUL.', 16, 4
  END

And even if you commented that check out, it will try to clean up from the same place you are writing the current backups to. So that won't help.
CopyOnly backups?
Using the CopyOnly parameter will prevent these one-off backups from affecting your backup chain. But the directory and/or filename will have a CopyOnly indicator in there, so the only old files that will get deleted are only going to be other CopyOnly backups.
Modify the code?
You might be able to comment out the line that executes the "Backup Database..." command (something like EXECUTE @CurrentCommandOutput =...), and use the Before_Backup value for the CleanupMode parameter. That way, the cleanup will happen and then the backup will be skipped. However, there are a lot of checks in the code to prevent things from going sideways, and I haven't read through it in enough details to know if commenting out that line would be safe. I wouldn't recommend it.
Let it work as designed?
Can you just correct the CleanupTime parameter that was too long, and let it clean everything up the next time it runs? Is there a reason you need to do the cleanup before the next scheduled backup?


Postgres: Put 1 database in separate HD partition? I have 2 HD partitions - one with SSD and another with regular hard drives.

I have multiple databases (as in logical database, not physical) with 1 data directory. I want the data for 1 of the databases to reside in the SSD partition because it's a database that will have LOT more reads/writes than the other databases. And since the SSD partition is limited in space, I can't store all the databases there, just that one.

Is there a way to configure this in Postgres - is it possible? Is there a tutorial online that I can read? I tried Googling but all the results talk about partitioning tables logically, not across different hard drive partitions.

You can create a table space that uses the second partition. Then just alter the database to use that partition.

CREATE TABLESPACE fastspace LOCATION '/mnt/ssdpart';
ALTER DATABASE yourdb SET TABLESPACE fastspace;

See also, http://www.postgresql.org/docs/9.3/interactive/manage-ag-tablespaces.html and http://www.postgresql.org/docs/9.3/interactive/sql-alterdatabase.html

Good luck!


SQL: transfer database schema If i want to transfer schema ownership to another schema i know that i need to use something like:

ALTER SCHEMA destinationschema TRANSFER sourceschema.objectname;

But how is it different from:

ALTER AUTHORIZATION ON OBJECT::sourceschema.objectname TO destinationschema ;

Thanks in advance!

ALTER AUTHORIZATION has a more general use for changing ownership of databases, types, keys etc. There is an ENDPOINT example n this MSDN link

  ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner

ALTER SCHEMA does exactly one thing

  Transfers a securable between schemas

In this special case you've highlighted, they do the same thing, There is no difference.

I'll guess that ALTER SCHEMA was added for completeness (eg there is CREATE and DROP why not ALTER) or in preparation for other features (such as renaming a SCHEMA, say)


Should I create a extra relation-table when I have two columns in one table referencing to one column in another table My problem is best explained by example:

Suppose I have a database about chess games and players.

Which one of the following structures should I choose

Option 1)

player (player_id,name... etc)
game (game_id,date... etc)
game_player (game_id,player_id,white)

where game_player.white is True/False depending if the player was playing black or white

Option 2)

game (game_id,black_player,white_player,date... etc)
player (player_id,name... etc)

I think Option 2) is the best because I know that every game has exactly 2 players and one of them is black and one is white.  However, somehow the queries in this setup don't feel so natural to me. Even in simple queries I have to join to player table twice and need conditions like game.black_player = player.player_id

Option 1) feels good because it's immediately clear where each column is  referencing. However, otherwise I don't like option 1) so much.

So which one is the best practice and why? Or is there even better Option 3)?
If I choose option 2) how should I name the black_player and white_player columns to be clear that they are referencing to the player table.

Hopefully my question makes sense.

I realize that this is a very basic question but I can't stop thinking about this and didn't find anything by searching.

I'd definitely choose option 2. A game of chess is between exactly two players.

Option 1 allows a game with 0, 1, 2, 3 or more players, while the data model in option 2 (assuming not null on the two player columns) means that you will never have these data inconsistencies. Just remember to also add the check constraint black_player != white_player.


Multiple Aliases in an SQL Query Would it be possible to give a table in MySQL query multiple aliases?

If so how would I do this?

You can if you are using the table multiple times in different clauses and/or sub-queries like

SELECT SeniorManager = sm.displayName, Manager = m.displayName, Worker = w.displayName
FROM   people AS w
LEFT OUTER JOIN people AS  m ON  m.id = w.bossID
LEFT OUTER JOIN people AS sm ON sm.id = m.bossID

But you can not give the same use of a table more than one alias - the following for instance is invalid syntax:

SELECT a1.name, a2.name FROM people AS a1 AND a2

Also, you can not (in most DBMSs) use a table by both its real name and alias, so the following is invalid too:

SELECT people.name, a1.name FROM people AS a1

If ause of an object is given an alias you must refer to that instance by that alias even if the real name is unambiguous within the query.


Any way to speed up reorganize partition of a very large table in MySQL? So we have this massive 1.5TB+ table with billions of records that has been neglected for the past few months and needs to have new partitions added.
The partitions are based off a timestamp which are created by month and the last partition is from March. So we have about 4 month's worth of data in the pMax partition which is nearing 700GB. This table is of course business critical and we can't have much downtime.

I have a smaller table that had about 80G in the pMax partition and it took about 6 hours to reorganize and pull out 13G of data for one month. So it would easily take around 3 days to fix up the big table.

This is what I'm using to reorganize and add partitions:

alter table db.table
reorganize partition pMax
into (partition p20150201 values less than ('2015-03-01'),
partition p20150301 values less than ('2015-04-01'),
partition pMax values less than (maxvalue));

Would it be faster to create a new table with partitions created for the next year, then insert data from the current table and rename once it's completed? I'm still thinking this would take forever given the size of the table... but don't know what other options I have, if any....

Has anyone use pt-online-schema-change to reorganize a partition? Sounds like I should be able to do it online.. just need to double our storage mount.

You may want to try Percona's pt-online-schema-change

pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.
pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.
The data copy process is performed in small chunks of data, which are varied to attempt to make them execute in a specific amount of time (see --chunk-time). This process is very similar to how other tools, such as pt-table-checksum, work. Any modifications to data in the original tables during the copy will be reflected in the new table, because the tool creates triggers on the original table to update the corresponding rows in the new table. The use of triggers means that the tool will not work if any triggers are already defined on the table.
When the tool finishes copying data into the new table, it uses an atomic RENAME TABLE operation to simultaneously rename the original and new tables. After this is complete, the tool drops the original table.

Source: https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html


Find who executed a stored procedure Is there a way/any functions/DMV's in SQL Server that will provide who executed a stored procedure of a particular database?

If the stored procedure already ran and you're looking for the smoking gun, and you're looking for history without setting something up ahead of time, you'll need to analyze the transaction log.

If you're asking because you're ready to change the server or the database to be prepared the next time it happens, you can:

Add a line to the stored procedure to log that information to a table
Run a trace with Profiler or Extended Events, focusing just on that stored procedure
Implement auditing
If the stored procedure runs for a long time, you could log sp_WhoIsActive to a table every few minutes via an Agent job, and then later go back through those logs to see who was running it


Normalization is the formal process for removing redundancy from relations by taking projections which when joined back form the original relational and thus eliminate some redundancy without data loss.  It is the science underlying database design.  The first three normal forms, and BCNF, deal specifically with eliminating redundancy due by ensuring that every non-trivial functional dependency is fully dependent only on candidate keys.  Higher normal forms deal with other kinds of dependencies to further eliminate redundancies.  Even when fully normalized (5NF is generally considered the "final" normal form although there are four others in the literature) redundancy can still remain as not all redundancies can be removed by taking projections.

Another tool to address eliminating redundancy is the principle of orthogonal design which states that two distinct relvars cannot have in them a tuple with the property that if it appears in the first relvar it must also appear in the second and vice versa.  But this principle only addresses redundancy across relvars whereas normalization addresses redundancy within them so it doesn't help with your example.

Ultimately Date contends we just need more science to guide database design as that which we have today as you show isn't quite enough.  One practical point to your example is that although there is redundancy, at least it can be controlled redundancy if a table is defined to hold the dancers, all key, with name and birth date.  Then, name and birth date become a foreign key to the dances table, and that foreign key can be defined to cascade updates.  Then, if a particular dancer's birthdate is found to be in error and corrected, the DBMS will automatically handle updating all the places in the dance table where that dancer was listed.  Moving the control of the redundancy from the user to the system is a big step forward that you can get with today's SQL DBMS'.

All of this information is paraphrased from Date's excellent book Database Design and Relational Theory which provides a significant amount of thinking and detail around just this issue.  It is indeed the case that we stand on the shoulders of giants.


converting then outputting varbinary(max) FOR XML PATH I have a vendor application that has stored some of its text data in a varbinary(max) column. I am exporting this data (from several tables with different data types), ideally using FOR XML PATH. What is the best strategy for converting this column's data prior/during (function? convert?) export to XML.

Thanks!

Don't know that you're going to get anything much simpler than this:

DECLARE @x TABLE(a VARBINARY(64));

INSERT @x(a)
SELECT 0x480069002100
UNION ALL
SELECT CONVERT(VARBINARY(64),(REPLICATE(N'x',32)));

SELECT CONVERT(NVARCHAR(32), a) FROM @x FOR XML PATH('tag');

Results:

<tag>Hi!</tag>
<tag>xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx</tag>

I'll leave all the other XML arrangement you may want to do with the output, but essentially IMHO you should convert to (the right!) string output before putting the data anywhere near XML.


PostgreSQL: Trigger for updating view doesn't work I'm trying to make the trigger for updating a view work.
If I'm doing and INSERT it works! But when I'm trying to do an UPDATE it just doesn't run.

CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS trigger AS
$$
    BEGIN
        RAISE NOTICE 'hi';
        RETURN NEW;
    END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON specific_view
        FOR EACH ROW
            EXECUTE PROCEDURE test_trigger_function();

Then in another place I execute it:

RAISE NOTICE 'before';
UPDATE specific_view SET gender='randomstuff';
RAISE NOTICE 'after';

The result:

psql:test_function.pgsql:134: NOTICE:  before
psql:test_function.pgsql:134: NOTICE:  after

On INSERT:

RAISE NOTICE 'before';
INSERT INTO specific_view (gender)
    VALUES ('randomstuff');
RAISE NOTICE 'after';

Returns:

psql:test_function.pgsql:134: NOTICE:  before
psql:test_function.pgsql:134: NOTICE:  hi
CONTEXT:  SQL statement "INSERT INTO specific_view (gender)
                VALUES ('randomstuff')"
PL/pgSQL function ... blahblahblah ... line 261 at SQL statement
psql:test_function.pgsql:134: NOTICE:  after

Because update trigger is invoked once per row being updated, it is not invoked at all if no rows are going to be updated.

See for yourself:

create table test(i int);
create view test_view as select i from test;

CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS trigger AS
$$
    BEGIN
        RAISE NOTICE 'hi';
        RETURN NEW;
    END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON test_view
        FOR EACH ROW
            EXECUTE PROCEDURE test_trigger_function();

insert into test_view(i) values(1);

SELECT * FROM TEST;
SELECT * FROM test_view;

UPDATE test_view set i=1;
-- trigger did not fire

insert into test(i) values(1);

UPDATE test_view set i=3;
-- trigger fired

SELECT * FROM test_view;


Mysql table structure I will have a "clients" table and a "users" table what i wanna do is restrict access to some clients for some users

My question is how should i do the table structure for this? One solution comes to my mind is defining a field in clients table and putting the user ids in that field who wont have access to that client (so i can use that ids to check/exclude from result) but i dont know if it is efficent way? I mean if i do such field i am thinking to define it as "blob" field as i wont know how long that field should be, but i have concerns using a blob field/putting ids in one field? Will query be slow?

Is there a better/common way? Or my way is completely wrong?

A suitable solution is to create third table to link users with clients and so give access:

users             lnk_user_client_access
-----             ----------------------                clients
id_user --------- id_user                              --------
...               id_client   ------------------------ id_client
                                                       ...


Recovery of a corrupted database using only the .frm in Windows Is it possible to restore mysql database using only .frm files in Windows

unfortunately it is impossible, maximum possible restore structure
InnoDB data stored in *.ibd files if set innodb_file_per_table=1 or in idbata1 if it 0.

for success repair even when innodb_file_per_table = 1, ibdata1 need as well.

if You have only from file = no chances restore data at all


Why should I save both markdown and the converted HTML in my database? First of all, forgive me if this is the wrong place to ask this or if this has been answered somewhere and I just can't seem to find it.

So you have a scenario where you have an article/news web app and your authors use markdown to generate content.

With regards to deciding database schema, everywhere you search, the recommended method is to save both the raw markdown text and the converted HTML in your database, but my question is WHY?

Internet: You never want to parse markdown every time you have to display the content.

Me: Yes, so parse the markdown and store the HTML into the database directly, so you don't have that performance hit when serving up the articles/content since you want speed here. Good, that's fine.

Internet: It makes it easier to edit the raw markdown later, so saving the markdown to the database is recommended.

Me: It sounds good in theory, but what happens when you have a very large database with tens of thousands of rows? You basically inflate the size of your database just to prevent parsing some HTML back to markdown when you edit the article/content? How often do you edit these articles? Not that much since most of the hits are just reads to display the content to your end users.

So my question is, where is the benefit of storing both the markdown AND the converted HTML in the database? The "later-edit" argument seems moot to me when just to save a minor conversion before editing, you're basically inflating your database size by needlessly storing the raw markdown in addition to the HTML. Your articles table can theoretically double in physical size just because of this. What's the point then?

It just sounds like pre-optimization that leads to a larger detriment later on when your infrastructure is forced to scale because of the duplicity of the markdown/HTML content. A conversion from HTML to markdown seems much more realistic to me, not like the authors/editors are going to notice the extra few milliseconds that the converter takes when they try to edit something.

Your argument is flawed.

You are concerning yourselves with doubling a 15kB per article  × 10k rows = 150MB table. (Excluding images)

And, you will be accessing individual rows via a PK.  You can have a trillion rows and the database won't care.  10k rows is tiny.

Additionally, the database is already going to be inflated due to Revisions and Text Indexing .  Adding the HTML version shouldn't hurt you.  Not even at 10M rows.

Oh, if the author is editing the Markdown version, you better record what she/he wrote.  (Ie always convert from the author's format to the printed format; never the other way around)


Database design for a group-buying site What is the best way to approach the database design for a group-buying site?

If you are not familiar with group buying, people purchase a deal, and when "n" amount of people purchase the deal, the deal is activated and the product is shipped to the people who ordered the deal. However, the deal does not ship until "n" people buy it. Moreover, the requirement is that a single deal can have many "group-buys", meaning that once "n" amount of people buy the deal, the deal does not close, but rather restarts for "k" amount of times.

What I thought of was to have a "deal" table that has many "group" tables (where each of the groups represents one of the "k" number of times the deal can be group buyed), and each "group" table has "n" amount of order tables. The group is "activated" when the group table has the correct "n" threshold number of orders for that deal.

An example
    Deal ---> Group ---> order
       |            ----> order
       |
       ------> Group

Some problems I have with this design:

Upon initialization of the deal, you have to create all the "n" group rows that will be related to the deal, even though some of the "n" groups may not have any orders (especially if nobody purchases the deal). Thus, someone could easily specify to be able to group-buy the deal 100,000 times (making us create 100,000 rows), even if nobody group buys their product. Plus, if that person wants to delete their deal, we have to delete 100,000 rows.
Keeping track of the groups is a pain. When you process an order how do you know which group to add the order to? What if somebody (or everybody) wants a refund within a group, then which group do we add a new order to, since their might be one group with some people, and another group with some people.

This is an idea I came up with that can help (see attached image).

The Deals table is simple. It has an ID that defines the details of a deal (like product name, origin, etc). That info through the ID is passed onto a deal_instance table thats designed to setup the individual sales to that deal. This is where your "K" comes in.

Then I added a customer deals table because any number of customers can buy any number of deals and vice versa, but in the customer deals table, the number bought is recorded so you know which customer bought which deal and how many times.

Then you use that information to find out when n people bought the deal and how many times. Once that value is met, then you go on and create another instance for the same deal just by adding another record to the deal_instance table using the same deal ID. If I'm not mistaken, the left most line needs to be an arrow, not a slash. (1 to many relationship).


PostgreSQL: Trouble accessing another server's Data via NFS Background:

I'm working on a 2-node High Availability PostgreSQL database cluster for my company. The system has a master/standby setup where the standby takes over the duties of the master when the master fails and both have up-to-date data.

Here is a previous question which describes the exact scenario I am trying to find a solution for:
PostgreSQL Failover - What tools should I use?

Every solution has an overhead of replicating the database between the master and the standby server. However, if I can share the same data location between both the servers, this need for replication will be removed.

I'm thinking of using a highly reliable SAS Storage shared between both the servers for PostgreSQL data. As only one instance of PostgreSQL will be running at a given time (using pacemaker failover) this should work, atleast in paper.

My Experiment:

I identified two free workstations at our test lab, Workstation-A and Workstation-B for the sake of discussion here.
I stopped the PostgreSQL 9.1 process on Workstation-A and Workstation-B
I shared over NFS /var/lib/postgresql and /var/log/postgresql folders of Workstation-A
I mounted the above directories at the same locations in Workstation-B
I ran

/etc/init.d/postgresql start

I got the following error:

IST FATAL:  could not access private key file "server.key": Permission denied

Any idea how to fix this issue ?

I need this working as a proof-of-concept that can be evaluated for the project.

I figured out the issue. It was due to improperly shared NAS folder. I fixed it by using this entry in /etc/exports

/var/lib/postgresql/ 192.168.1.0/24(rw,nohide,insecure,no_subtree_check,async)

to share.

And if it interests anybody, I managed to get shared database running successfully. The only folder that needed to be common was the above folder. And both PostgreSQL Server instances need to have identical configuration files for the data folder to be shared successfully.


SQL Server 2016 -> 2019 AlwaysOn Rolling Upgrade Migration I have a migration concept with an AlwaysOn configuration from SQL Server 2016 (on windows server 2016) to SQL Server 2019 (on Windows Server 2019) which are in different sub-networks.
So with internet searching (MSDN, mssqltips...) I oriented my config toward "Distributed Groups".
My configuration is: AG1 (2 SQL Server with 2016) and AG2 (2 SQL Server with 2019).

I do the whole tutorial from mssqltips, Failover and then I have every Databases names with Warning (Image for 2019) but nothing imported.. why? I didn't find answer.
By the way ... The workaround is to install same SQL Server 2016 on the new windows 2019.. Do the all process with Failover throught the Distributed Group and then ... Install manually SQL Server 2019 on the windows 2019. I find it weird that we have these steps and try to understand if I missed something or whatever?
Both editions I used are Developer.

This is from Microsoft books online :

Initialize secondary availability groups in a distributed availability group
Distributed availability groups were designed with automatic seeding to be
the main method used to initialize the primary replica on the second
availability group. A full database restore on the primary replica of the
second availability group is possible if you do the following:

Restore the database backup WITH NORECOVERY.
If necessary, restore the proper transaction log backups WITH NORECOVERY.
Create the second availability group without specifying a database name and
with SEEDING_MODE set to AUTOMATIC.
Create the distributed availability group by using automatic seeding.
When you add the second availability group's primary replica to the
distributed availability group, the replica is checked against the first
availability group's primary databases, and seeding catches the database up
to the source. There are a few caveats:

The output shown in sys.dm_hadr_automatic_seeding on the primary replica of
the second availability group will display a current_state of FAILED with the
reason "Seeding Check Message Timeout."

The current SQL Server log on the primary replica of the second availability
group will show that seeding worked and that the LSNs were synchronized.

The output shown in sys.dm_hadr_automatic_seeding on the primary replica of
the first availability group will show a current_state of COMPLETED.
Seeding also has different behavior with distributed availability groups. For
seeding to begin on the second replica, you must issue the command ALTER
AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE command on the replica.
Although this condition is still true of any secondary replica that
participates in the underlying availability group, the primary replica of the
second availability group already has the right permissions to allow seeding
to begin after it is added to the distributed availability group.

Source : https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-ver15


Return proper column based on other column's values Consider an EAV table with separate columns for each datatype:

CREATE TABLE eav_values (
    id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    entity_id INT NOT NULL,
    name VARCHAR(127) NOT NULL,
    type SET('varchar','int','datetime') NOT NULL,
    col_varchar VARCHAR(1023) NULL DEFAULT NULL,
    col_int INT NULL DEFAULT NULL,
    col_datetime DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB;

Now I'd like to store the username column for entity_id=5, which is a VARCHAR:

INSERT INTO eav_values (entity_id, name, type, col_varchar)
    VALUES (5, 'username', 'varchar', 'jeffatwood');

How would one construct the SELECT statement to select from the proper column with a single query?

SELECT (MAGIC HERE) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Might I be best off simply COALESCEing the values and carefully managing the data in application code? Is there no real performance penalty to such a SELECT?

SELECT COALESCE(col_varchar, col_int, col_datetime) FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Or perhaps duplicate the data into a redundant read VARCHAR column for reading?

INSERT INTO eav_values (entity_id, name, type, col_varchar, read)
    VALUES (5, 'username', 'varchar', 'jeffatwood', 'jeffatwood');

SELECT read FROM eav_values
    WHERE entity_id=5 AND name='username' LIMIT 1;

Note that I would like to use different datatypes for each value as in some cases I might need to index or perform datype-specific logic on certain columns, in those cases I'll know which datatype or I'll use a separate SELECT to get the column type.

CASE type
WHEN 'varchar'  THEN col_varchar
WHEN 'int'      THEN col_int
WHEN 'datetime' THEN col_datetime
END


index for OR operator with different columns What would be best index for this kind of query:

-- id is the PK
create table test3(id number,name varchar2(30) not null,lastname varchar2(30) not null);

select id from test3 where id=:1 and (name=:2 or lastname=:3);

Would that be depends on the number of distinct values of each column(in that case the index would be on the more distinct column)?
What if we cant tell what is the nature of the data..?

If id is the primary key, any query with where id=:1 and (..whatever..) can effectively use the primary key index and retrieve, lookup only one row (if there is one.) It will then have to do the additional checks, the (..whatever..) but that is not going to be slow, since it's only one row.

If id was not the primary key, the situation might be different, depending on the selectivity of the 3 columns involved. The best index, would probably be two indexes, on (name, id) and on (lastname, id)

Oracle's optimizer can then use both indexes, doing an "index join". See the SQL-Fiddle.


Can arbiterOnly replica in MongoDB become SECONDARY and what it means? The MongoDB documentation says that when a replica is added to the set as arbiterOnly=true it will not become Primary. But can Arbiter become a secondary and what does it when it does?

I have a replica set consisting of two data replicas and one arbiterOnly.

Some time ago my one of my data replicas which was secondary at a time crushed due to hard drive failure. After I fixed that problem and restarted secondary it went into “Recovering” state. But my arbiter is now “Secondary”. (That is shown by rs.status() command)

Furthermore, the data directory for this Arbiter shows bunch of files of total size >10GB. They indeed look to me like data files. Are they? What is going to happen to these files when Recovery completes?

If Arbiter can become truly a data node (even secondary) that opens up the whole new bag of worms: I need to have enough free disks available to Arbiter, right now I don't. Is there option to prevent  arbiterOnly from becoming secondary?

Some time ago my one of my data replicas which was secondary at a time crushed due to hard drive failure. After I fixed that problem and restarted secondary it went into “Recovering” state. But my arbiter is now “Secondary”

A MongoDB arbiter cannot automatically become a secondary or a primary node, as it does not have a copy of the data set.

If you try to manually reconfigure the arbiter as a regular node via rs.reconfig() you should get an exception similar to:

{
    "errmsg" : "exception: arbiterOnly may not change for members",
    "code" : 13510,
    "ok" : 0
}

  Furthermore, the data directory for this Arbiter shows bunch of files of total size >10GB. They indeed look to me like data files. Are they? What is going to happen to these files when Recovery completes?

Assuming this node is definitely an arbiter,
I would expect those files are unused (check the timestamps?) and are either:

a local database created if this node was incorrectly initialized with an oplog
unused copy of data directory if this node was copied from another secondary or used as a standalone before

You can always log into the arbiter mongod directly to see what data it appears to have.


Find times when table is least frequently used I would like to analyse performance of one of the big tables in the database. There are only two types of operations on it. Inserts and reads. Once data is entered it is never updated.

I need to find when the table is updated least frequently (for example Saturday between 3pm-6pm). I need to also find when the table is read least frequently.

Structure looks like this:

dbo.Table1
    [Column1] [INT] IDENTITY(1,1) NOT NULL,
    [Column2] [VARCHAR](50) NULL,
    [Column3] [DATETIME] NULL CONSTRAINT
    [Column4] [INT] NULL,
    [column5] [VARCHAR](255) NULL,
    [column6] [INT] NULL,
    [column6] [VARCHAR](MAX) NULL,
    [column7] [INT] NULL,

There is clustered index1 on column1 and non clustered index2 on column2 and 3 including column1

We use:
SQL Server 2012

SQL Server doesn't keep track of this information. You'd have to take snapshots from sys.dm_db_index_usage_stats frequently enough that you can graph activity, and you also need to account for service restarts, index rebuilds, etc. which can remove rows from the DMV and set the activity back to zero (this may look on your chart like no activity but it really means it has been reset).

This is not a straightforward task. I think you should add more information to your question explaining why you need this information, because maybe there are other ways to accomplish your real goal (knowing when a table is read less frequently is not a goal, it's the path you've chosen to your goal).


Allow remote access to Oracle 12c After finishing the installation of Oracle 12c on Centos 7, now I try to allow access on my PC (via SQL Developer) to the server. The problem is that when I do:

lsnrctl status

I get this error:

TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory

So, I try to start the listener with:

lsnrctl start

And I get this error:

TNS-01198: Listener failed to initialize valid node list
 NL-07007: internal error
  NL-00408: cannot find or read value
   NL-00408: cannot find or read value
    NL-00408: cannot find or read value

To finish I go to this website:
TNS-01198: Listener failed to initialize valid node list post upgrade from 11g to 12c

Then I added, as he tell the following line on the listener.ora file:

VALID_NODE_CHECKING_REGISTRATION_<LISTENER_SERVICENAME> = OFF

but nothing change.

To finish, this is my listener.ora file:

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

VALID_NODE_CHECKING_REGISTRATION_<LISTENER_SID> = OFF

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  ns330926.ip-37-187-120.eu)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

I added my hostname in the /etc/hosts file.

Does anyone have a solution?

I fixed the problem by adding this correct line on the listener.ora file:

VALID_NODE_CHECKING_REGISTRATION_LISTENER=OFF

now the listener is starting.


Reading 300% rows - Problematic execution plan Whole queryplan and query: https://www.brentozar.com/pastetheplan/?id=BkgbANxN4

I am struggling with a query that uses a lot of time.
The obvious reason for this is wrong estimates of amount of rows.
I have tried indexing and updated stats, however I do not seem to address the real problem since the query still goes amazingly slow (I guess wrong index or stats).

What I need are help with interpreting this information so I can help the query to be optimized. What index, which DBCC functions or other built in functions should I run between executions to ensure clean cache and whatnot to avoid using wrong stats and  the fresh new one?

Examples would be

DBCC FREEPROCCACHE
UPDATE STATISTICS *table*

Another bottleneck of the same query would be these hash matches. The Hash Match at the right most are finished within seconds, while the one to the left seems to struggle with the last 213 rows that uses many minutes. What can I do to find out where the problem lies in these hash matches?

I am also trying to solve multiple memory spills in our batch jobs, where I can only seem to optimize memory spill from a single table.

I have multiple sorts and hash matches where there are rather long probes and residuals including multiple tables or "Expressions" which I assume are either aggregated or set by SSIS packages.

Should I solve the 'first' or 'last' spills first? First are 'top of the three' and last would be closest to leaf nodes (operators). I do also wonder about some operators, mentioned below.

Can you explain what the terms mean regarding the execution plan descriptions:

Build residual
Probe residual
Hash keys probe

I believe I understand the following  terms:

Order by: The order of which the operator needs the data in

Output list: Which data the operator are retrieving for output

That’s a lot of questions.

I write about Hash Match with Probe Residual here: http://blogs.lobsterpot.com.au/2011/03/22/probe-residual-when-you-have-a-hash-match-a-hidden-cost-in-execution-plans/ - there’s a good chance the problem with your Hash Match finishing is more related to what’s pulling the rows from it than the Hash itself.

As for your stats, the problem is all your ORs. You may find your Seek works better with an index that has KVID_Kontotype as the first key, followed by PeriodeStartDato. Then instead of having a large range scan across all dates earlier than your predicate and checking every single row to see if the Kontotype is correct, it would seek for each Kontotype with the appropriate date range. It would probably estimate better, but fundamentally would read fewer rows to get what you need.


Is it possible to have 2 or more databases active, and synchronized between them? edit
I'm testing possible solutions for database redundancy and high availability,I tested mirroring it worked fine, but lacks scalability plus there will be a lot of work in one server. Now I discovered Merge Replication, and in paper looks great (although I still need to know what happens if a server goes down and them comes alive again, will it synchronize? can you answer me to this one too?)

edit

My objective is to have various applications working simultaneous and communicating with one database only. But having the databases synchronized between them and that in case of one database goes down, all its data is not lost.

Is it possible to do this with Merge Replication?? where all sql instances are both publishers and subscribers, and have an distributor inside them.

Any considerations I should take, or other possible solutions for my problem?

regards

SQL Server scales up not out
HA/DR is separate to scaling
Replication is not designed to publish entire databases
Replication or mirroring add an overhead that defeats your scaling out
Merge replication will have conflicts at some point

Personally, I'd buy a bigger server, go Enterprise x64 edition, and wait for SQL Server 2012 HADRON


Optimizing Slow Changing Dimension (SCD2) joins Right now I'm working on developing a DB schema for an application which requires versioning several different sets of numerical data which must be joined together for use in views. The application requires that we're able to 'look back' and find the state of what these multiple variables were like at an arbitrary point in time.

I've built up a few view definitions using subqueries to fetch the various inputs along arbitrary join criteria as well as checking the version against a time_created field. This isn't a strict SCD2 implementation, but it's close. (link to Slow Changing Dimensions page in Wikipedia)

You can see a similar ( albeit greatly simplified ) implementation to our schema here: DB Fiddle link. The real schema has 6 subqueries/joins.

This works very well for my purposes, except that when we start scaling it up into the thousands of 'purchase' records we incur a significant query cost. A query against 2000+ rows is taking ~1s, which is unsuitable for our application.

The first solution that springs to mind is creating a table which joins a 'purchase' to it's various versioned input tables, to be created by the application when a purchase is created. This should be much more efficient, but requires more schema complexity.

Can anyone offer me advice on how to best plan my schema/queries so as to be most optimal for this kind of setup?

Admittedly this is a cross-post from SO, but it received zero interest there over 24 hours, so I figured it may be better suited for the DBA stackExchange. Apologies in advance if this causes any offence.
The original can be found here.

I found a somewhat satisfactory solution to this issue. I feel a bit foolish for overlooking this, but you could put it down to a case of 'premature optimization' on my behalf. I had omitted the end_date columns, as per the academic example in Wikipedia, in my SCD tables. I figured this was not particularly necessary since all you need to do to 'rewind' your table and find the correct SCD row is find the first row with a start_date less than or equal to the date you're querying against when the table is sorted. But this requires a table sort to function correctly.

The major bottleneck in my query was caused by sorting a moderately large ( 2500+ row ) table by a timestamp. I managed to resolve this issue by adding an 'end_date' column and changing the query to use ( simplified example ):

LEFT JOIN LATERAL ( SELECT * FROM tax t
  WHERE p.time_created >= t.time_created AND p.time_created <= COALESCE(t.time_ended, '9999-12-30'::date)) current_tax ON true
....

This cuts the query down from ~1sec to <100ms. I tried adding indexes against fields in the 2500+ row table, but this didn't seem to have an enormous effect on the query time, but perhaps I had made some errors there. This seems more optimized at any rate.

An updated DBFiddle can be seen here.

I would love some advice on how to better represent this, or how to better account for null values in the time_ended column.


Which is more efficient for searches on JSON data in Postgres: GIN or multiple indexed columns? For example, say I have a table with a medium number of rows (~100,000 or so) that has a jsonb column with the following example data in one of the rows:
{"name":"Bob", "favoriteColor":"red", "someOtherObject": {"somethingElse": true}}

Is it more efficient to:

Use a GIN index on the jsonb column, then use Postgres' built-in JSON query functions to pull out data

Create several columns to represent things that can be searched for i.e. one column for name, one column for favoriteColor, etc; build b-tree indices on those columns and then run a select document from table where name = 'Bob'

Some other solution?
Keep in mind the solution needs to efficiently support like queries to be able to search for values that start with a given input string.

The advantage of JSON is versatility: you can add any keys without changing the table definition. And maybe convenience, if your application can read and write JSON directly.

Separate columns beat a combined json or jsonb column in every performance aspect and in several other aspects, too: More sophisticated type system, the full range of functionality (check, unique, foreign key constraints, default values, etc.), the table is smaller, indexes are smaller, queries are faster.

For prefix matching on text columns you might use a text_pattern_ops index:

Why would you index text_pattern_ops on a text column?

Or, more generally, a trigram index supporting any LIKE patterns:

Full Text Search With PostgreSQL
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

While you stick with JSON (jsonb in particular), there are also different indexing strategies. GIN or Btree is not the only decision to make. Partial indexes, expression indexes, different operator classes (in particular: jsonb_path_ops) Related:

How to get particular object from jsonb array in PostgreSQL?


I want to join the below mentioned queries into single query SELECT
      A.update_date,
      A.create_user,
      A.service_id,A.order_id,
      '10009' AS main_plan_id,
      'Prepaid Cash' AS main_plan_name,
      ' ' AS topping_plan_id,
      ' ' AS topping_plan_name,
      '0' AS Price
FROM
      ORDER_MASTER A
WHERE
      A.pending_order_type = 'Sim delivery'
AND
      A.order_status='Pending'
AND
      DATEDIFF(NOW(),update_date) > 30
GROUP BY
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      main_plan_id,
      main_plan_name,
      topping_plan_id,
      topping_plan_name;

SELECT
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      ' ' AS main_plan_id,
      ' ' AS main_plan_name,
      D.topping_plan_id,
      D.topping_plan_name,
      D.charge_amount AS Price
FROM
      ORDER_MASTER A
LEFT JOIN
         (
           SELECT
                 B.service_id AS service_id,
                 B.plan_id AS topping_plan_id,
                 B.plan_name AS topping_plan_name,
                 C.charge_amount charge_amount
           FROM
                 BILLING.BS_SERVICE_SUBSCRIPTION_MASTER B,
                 BILLING.BS_SUBSCRIPTION_CHARGE_DETAILS C
           WHERE
                 B.subscription_id = C.subscription_id
         ) D
ON
      A.service_id = D.service_id
WHERE
      A.pending_order_type='Sim delivery'
AND
      A.order_status='Pending'
AND
      DATEDIFF(NOW(),update_date) > 30
GROUP BY
      A.update_date,
      A.create_user,
      A.service_id,
      A.order_id,
      main_plan_id,
      main_plan_name,
      D.topping_plan_id,
      D.topping_plan_name;

Is this what you need?

( SELECT ... )
UNION ALL
( SELECT ... )

The GROUP BY on the first SELECT seems to be useless; please explain why you have it.  I am less certain about the second query.

Not sargable: DATEDIFF(NOW(),update_date) > 30; change to A.update_date > CURDATE() - INTERVAL 20 DAY.

Recommend:

A:  INDEX(order_status, pending_order_type, update_date)
C:  INDEX(subscription_id, charge_amount)

When JOINing, please use FROM B JOIN C ON ... instead of FROM B, C WHERE ...

(If you need further discussion, pleas provide SHOW CREATE TABLE.)


MySQL Replication: Insert generating VERY huge temporary tables our replication slave is generating ~300GB temporary files while running relatively simple inserts.

Our setup:
Master: 5.0.45-0.sles10.x86_64,

Slave: 5.0.85-win32, also tried with 5.1.57-win64, same DB

Slave configuration:

  max_connections=100
  query_cache_size=1M
  table_cache=2048
  tmp_table_size=32M
  thread_cache_size=8
  binlog_cache_size = 10M
  myisam_max_sort_file_size=100G
  myisam_sort_buffer_size=69M
  key_buffer_size=55M
  read_buffer_size=64K
  read_rnd_buffer_size=256K
  sort_buffer_size=8M
  innodb_additional_mem_pool_size=16M
  innodb_flush_log_at_trx_commit=0
  innodb_log_buffer_size=16M
  innodb_buffer_pool_size=512M
  innodb_thread_concurrency=10
  datadir=D:\MySQLrepl\
  innodb_data_home_dir=D:\MySQLrepl\
  innodb_data_file_path=ibdata1:33266M;ibdata2:31484M;ibdata3:52988M;ibdata4:123492M;ibdata5:100M:autoextend
  innodb_log_group_home_dir=D:\MySQLrepl\
  innodb_log_files_in_group=2
  innodb_log_file_size=128M
  log-bin=mysql-bin

Database information:
~360 GB total data size
~50 GB data size for affected database, rest in different database
Storage engine is innoDB for all tables
Database was transfered to slave with MySQL Enterprise Backup 3.5.2
sernumbers_results contains around 317 million rows, size is around 17,7 GB, rowid is the primary key.

One of the queries generating the problem:

  INSERT INTO sernumbers_results_2009 SELECT * FROM sernumbers_results WHERE rowid>(SELECT rowid FROM sernumbers_results_2009 order by rowid desc limit 1) ORDER BY rowid LIMIT 10000

What this should do, according to my colleagues logic, is to copy results from 2009 to a separate table bit by bit. He has a good excuse for this, he is french ;)
He also says that running the insert queries was fast on the server and indeed: the SELECT-part from that query runs in 0.18s without any problems, so it seems that there is a problem with the insert bit. It seems that all databases are converted to temporary MyISAM-tables before the query is applied. It also seems that there is little to none back-conversion because the server is starting with the next temporary table almost immediately when the one before has reached it's maximum size.

I'm really totally clueless here, so any help or suggestion is really appreciated.

Algorithmically, it is doing what your colleague says. But, do you see what it is doing ???

It is generating 10,000 temp tables each containing 1 row after traversing 317 million rows through in the InnoDB internal index. Each temp table is a complete regeneration of the rowids in sernumbers_results_2009 table along with executing handler_read_prev commands internally to sort the data by an index scan from the back of the internal rowid index. Also, please remember you are dealing with InnoDB. Who knows what Multiversioning (via MVCC) is going on so that the INSERT is completed without interference and with rollback capabilities.

Is there any reason why this query wouldn't work for you ???

INSERT INTO sernumbers_results_2009
SELECT * FROM sernumbers_results
ORDER BY rowid DESC LIMIT 10000;

This will definitely generate one temp table.

Give it a Try !!!


use of signal for insert statement in mysql stored procedure I want to create a procedure in which all types of possible errors can be handled using SIGNAL. Below is my stored procedure:

DROP PROCEDURE IF EXISTS `spItem`$$
CREATE PROCEDURE `spItem`(
       IN p_itemname VARCHAR(50),
       IN p_itemvalue smallint(6),
       )
BEGIN
      INSERT INTO item VALUES('',p_itemname,p_itemvalue);
END$$

How can we use the SIGNAL statement for each kind of error in the above stored procedure that checks for the following:

1. check for any error in the insert statement
2. check for duplicate item names
3. check for valid item values (as it should just accept numeric values).

What changes will be required in the above stored procedure to handle the above errors?

This is done using a proper UNIQUE index on the table itself. MySQL will automatically take care of it.
This is done using proper data types for the fields in the table itself. MySQL will automatically take care of it. (BTW: your item_value is already smallint(6) so it can only be numeric anyway.)
Seeing the obove two points, you probably don't need to check this anymore.

Meaning you probably not need your stored procedure anymore. At least not for this reason.


Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type? So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues.
Many Certificates to Many Approvers relationship.
1 Category to Many Certificates relationship.
1 Category to Many AdditionalFields relationship
1 AdditionalFields to Many AdditionalFieldsValues relationship
If there are different types of Certificates which will have different fields/columns, should I create a different unique table for each type of Certificate or just add the additional fields/columns in the Certificates table (but some of the entries will be blank)?
For example, a MedicalCertificate will have a field Specialization whereas a DrillCertificate will have a field DrillType.
Certificates Table

ID
CategoryID
ApproveeID
ValidFrom
ValidUntil
StatusID
Specialization
DrillType

1
2
4
1/4/2021
2/4/2021
1
Oncology

2
1
5
1/4/2021
2/4/2021
1

Traditional

OR
MedicalCertificates Table

ID
ApproveeID
ValidFrom
ValidUntil
StatusID
Specialization

1
4
1/4/2021
2/4/2021
1
Oncology

2
5
1/4/2021
2/4/2021
1
Haemotology

1:many -- Just an id in one of the tables to link to the other.
many:many -- Need a table to link them.  It will have (probably) exactly 2 columns, namely the two ids.
key-value pairs -- read about EAV and its evils and benefits.


How to get the dependent entities (referencing entities) for an User Defined Function (UDF) We have a database that has been copied, including stored procedures and some user defined functions.  I'd like to check whether a UDF is being used.  Is there a way to see which stored procedures call the UDF?

You can find out the referencing entity dependencies for your function or any other database entities by using DMV dm_sql_referencing_entities. An example statement is as below.

Select
referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
from sys.dm_sql_referencing_entities ('dbo.ufn_MyFunction', 'OBJECT');


Logging Questions for SSIS 70-463 I have been looking at sample questions on logging for 70-463 exam. For the below, the answers given do not make sense to me.

The first answer given does not even say how to implement logging. I would have selected C as the answer.

The second answer suggests using dtexecui, which is for legacy only? Again I would use the 'Create an OnError event handler' (F) as the answer.

Am I wrong?

Thanks!

Root question

Why would using the Project Deployment Model provide the lowest cost of logging assuming the desire is to have

Centralized
automated
SQL Server based

Answer

Because it's built in. The new project model automatically logs at the Basic level when executed on the server. For completeness, your available logging options are None, Basic, Performance, Verbose. Basic is going to have everything you need for a standard run. If it fails, you will know information about the failure - what failed and why. You'll have start and stop times for all your executables and as the first sentence mentions, you don't have to do a single thing to get it.

Prior to this, a new package is created, goes through all the SOX/SAS-70 deployment checks and what not and it blows up in production. Look at the logs to see what happened and oh snap, nobody turned logging on. The package must then be edited which requires running back through all the approval and testing process and generally is a PITA.

Finally, if you add on OnError event handler to the package great, but now what? You will need to write your own logic, maybe a stored proc, whatever, to do something with the error. That's probably not going to meet the automated requirement.

dtexecui is just a gui for dtexec which is used whether you're using the project deployment or the classic package deployment model. It's just a matter of whether you indicate a package lives inside an ispac/ssisdb or not.

Final thoughts

Study up on the Project Deployment Model. Really dig in and play through how all this works. The test is trying to ascertain whether you understand what comes part and parcel with it so you can deliver the most effective solutions.