How can I make an automatic copy of a MySQL database to another MySQL database on the same server? I have two MySQL databases called LIVE and REPORTS on the same server.

I want to copy the LIVE database and all its contents into the REPORTS database everyday at a specific time frame between 1 am and 5 am.

How could I do this automatically?

You can setup second mysql instance with replication. If you need to "copy" only during certain time period, you may choose to cron a script which will start and stop replication according to the time.
Again you can only choose to replicate-do-db certain databases as well.


Steps to upgrade MySQL from version 5.0 to version 5.5 I have been developing under MySQL v5.0 for a while now and need to upgrade to v5.5 to be in step with the hosted production server.

After having searched for precise steps to do this migration, I am still unclear as to how to go ahead.

Can anyone provide a clear list of actions needed to upgrade to version 5.5 ?

Thank you

Thank you for these answers.
You are right, I should have given fuller info in my question.
Notably - I am under Windows XP SP3.

Yes I did search through Google beforehand, hence my statement

  "After having searched for precise steps to do this migration,
  I am still unclear as to how to go ahead."

To RolandoMySQLDBA : Thanks for the script, but I cannot see how I could adapt it.

Step 01 : Run this script to Dump Everything from MySQL 5.0

cd /root
MYSQL_CONN="-uroot -p..."
SQLSTMT="SELECT schema_name FROM information_schema.schemata WHERE"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
MYSQL_OPTIONS="--skip-column-names -A"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > /tmp/dblist.txt
DB_OPTIONS="--databases"
for DB in `cat /tmp/dblist.txt` ; do DB_OPTIONS="${DB_OPTIONS} ${DB}"
MYSQLDUMP_OPTIONS="--routines --triggers ${DB_OPTIONS}"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql
SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ''',user,'''"
SQLSTMT="${SQLSTMT}@''',host,''';') FROM mysql.user WHERE user<>''"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > GetGrants.sql
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < /root/GetGrants.sql | sed 's/$/;/g' > Grants.sql
rm -f GetGrants.sql

Step 02 : Backup config file

cp /etc/my.cnf /etc/my.cnf.50

Step 03 : Backup /var/lib/mysql

mv /var/lib/mysql /var/lib/mysql50

Step 04 : Uninstall MySQL 5.0

Step 05 : Install MySQL 5.5

Step 06 : service mysql start

Step 07 : Login to mysql

mysql -uroot

Step 08 : At the mysql prompt enter the following:

mysql> source /root/Grants.sql
mysql> source /root/MySQLData.sql

That's it.

Give it a Try !!!

I have suggested this before : MySQL upgrade 5.0.88 to latest


Can we expdp using JOINS in QUERY clause I have a task to export data from database using data pump. I have successfully exported database using subquery in QUERY clause. But I want to know can we expdp using joins in QUERY clause?

The QUERY parameter is used to restrict data in the export job. If you want to export number of rows that are exist in another table then you can use subquery in QUERY parameter.

If you want to join two tables and export you can create table by joining two tables. As shown below.

SQL> create table export_tbl as select e.first_name FN, d.department_name DN from employees e join departments d on(e.department_id=d.department_id);

Table created.

Then can export it.

[oracle@testsrv Desktop]$ expdp hr/hr directory=user_dir dumpfile=export.dmp tables=export_tbl logfile=export.log

Alternatively,  you can create a view by joining tables and export views(INCLUDE=VIEWS). You have to filter views during import.


MongoDB Connectivity Issue From last few days, I am facing connectivity issues with MongoDB Replica Sets deployed in google cloud. Interesting fact is these issues are appeared at Development machines and not on the production. Most common issues are:

Cannot connect to replica set "xxx"[xxx:27017]. Set's primary is
unreachable.
A primary with different host name [:27017] found in
server side. Different members found under same replica set name
"XXX"

These error are appeared frequently. How to resolve this?

Here is the screenshot of the error appeared:

As per Robomongo Tool error shows like that

  A primary with different host name [:27017] found in server side.
  Please double check if same host name and ports are used as in
  server's replica set configuration. If same set name is used for
  different replica sets, this configuration is supported only on
  different instances of Robomongo. Please open a new Robomongo instance
  for each replica set which has the same set name.

As per github blog documentation here it seems like the problem is Robomongo cannot learn the set name which is a critical information for connections to replica sets.

For Example Workaround

Find a way to get your replica set name, something like rs-..., it
shows it in the URL
Close Robomongo
Open this file in your editor

~/.config/robomongo/1.0/robomongo.json on macOS
C:\Users[your name].config\robomongo.json on Windows
~/.3T/robo-3t/1.2.1/robo3t.json on Linux

Search for the empty setName and add the replica set name there and
save the file
Start Robomongo again and it should work

For further your ref here


Updating a table with millions of records, its been 4 days I am currently updating a table with millions of records, its been 4 days and query is still executing.

I checked the activity monitor its shows that query is running.

In event log there is no errors at all.

Performance wise:

Tempdb in disk A (850 gb free space)
database file in disk B (750 gb free space)
16 GB ram

Please suggest me what should i do?

The query

UPDATE
    dbo.table1
SET
    costPercentage = ISNULL(t2.PaymentIndex, 1.0),
    t2.TopUp_Amt = (ISNULL(t2.PaymentIndex, 1.0) - 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00),
    Total_Tariff_Inc_t2 = ISNULL(t2.PaymentIndex, 1.0)
    * ISNULL(dbo.table1.Initial_Tariff_Amt, 0.00)
FROM
    dbo.table2 t2
WHERE
    LEFT(dbo.test1.procodet, 3) = LEFT(t2.ProviderCode, 3) COLLATE database_default

This query requires you to scan every row in the table because

I guess procodet or ProviderCode are not indexed
Even if they were indexed, you have a LEFT which is a function on a WHERE predicate
And you have COLLATE too which is effectively a function on a WHERE predicate

"a function on a WHERE predicate" means indexes won't be used

If you batch it (say on UPDATE TOP (10000) ... AND costPercentage IS NULL) then you need an index on costPercentage and this assume you are setting it.

The only solutions I see are

populate a new table in batches, based on, say, the primary key
create indexed, computed columns to hide the LEFT and COLLATE expressions, then run the update


How Can i Update only one Column of SQL Table via store procedure with passing rest column value null Here is What i Have tried.
I Have created a Table [tblUser] which allow null value and a Store procedure [spUpdateUser] with All Parameters which can be passed null except ID parameter

Whenever this procedure executes it will only update the passed (Not Null) Parameters

CREATE TABLE [dbo].[tblUser]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar](50) NULL,
[Name] [varchar](50) NULL,
[FatherName] [varchar](50) NULL,
[DOB] [date] NULL
)

CREATE PROCEDURE [dbo].[spUpdateUser]
@ID int,
@UserID varchar(50) = null,
@Name varchar(50) = null,
@FatherName varchar(50) = null,
@DOB date = null
AS
BEGIN
update tblUser set
    UserID = @UserID,
    Name = @Name,
    FatherName = @FatherName,
    DOB = @DOB
    where ID = @ID
END

How can i update table [tblUser] with only passing one parameter

exec [spUpdateUser] @ID = 1, @DOB = '27 Mar 2017'

You can do a COALESCE between the passed in parameters and the existing values.

When the passed in value is NULL, the query will retain the existing values.

This will, however, make it difficult to update to NULL if you do actually require the Nulling of these values.

CREATE PROCEDURE [dbo].[spUpdateUser]
@ID int,
@UserID varchar(50) = null,
@Name varchar(50) = null,
@FatherName varchar(50) = null,
@DOB date = null
AS
BEGIN
update tblUser set
    UserID = COALESCE(@UserID, UserID),
    Name = COALESCE(@Name, Name),
    FatherName = COALESCE(@FatherName, FatherName),
    DOB = COALESCE(@DOB, DOB)
    where ID = @ID
END


Signal wait always high I'm not sure if this is the right place to ask this question, so apologies in advance if not.

Problem I'm facing is: One of my server's signal wait floats at around %35 and won't go down.

Top 3 waits are:

  SOS_SCHEDULER_YIELD (55%)

  OLEDB (10%)

  ASYNC_NETWORK_IO (6%)

CPU utilization of the instance is at around 22%.

What might be causing this to happen?. Where should I look at to investigate further?
I'm on SQL Server 2012.

Thanks all.

Based on your comment about the "unwieldy" SP I'd almost guarantee that is at least part of your problem.  Try running the following query replacing DBName and SPName as appropriate.

USE DBName
GO

SELECT SUBSTRING(text, statement_start_offset/2,
        CASE WHEN statement_end_offset = -1 THEN 999999 ELSE
            (statement_end_offset-statement_start_offset)/2-1 END)
    ,*
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) query
WHERE query.dbid = db_id('DBName')
  AND query.objectid = object_id('SPName')

This will break down your SP into pieces and you can sort by one of the elapsed_time, logical_reads columns, or total_worker (cpu) columns to decide what specific commands you want to work on first.  The actual command will be the first column.

By breaking it down like this you can start picking off the low hanging fruit and speed the over all SP a bit at a time.  This doesn't of course take the place of reviewing the whole thing at once.  For example it will help to reduce the speed of a query that's being run a dozen times in the SP, it will help even more if you can figure out a way to run it only once (personal recent experience).

Now to be fair this doesn't answer your original question and it is more meant as a response to the comment and a helpful way to deal with a "problem" sp.


Moved a MySQL Database to another server, but socket keeps pointing to localhost I know I shouldn't bother with this, maybe I should disable the old mysql, but this is a Magento virtual appliance from Bitnami thats work as a whole (application and database on the same server).

I had to follow the client's design, though, and take the mysql database to an Amazon RDS instance. (the magento/application server is an EC2 at amazon as well).

The application server has a script that starts up mysql, apache and some other stuff. In this process, it always tries to update the mysql database with any new data the server has (like DNS, hostname, IP Addresses). But since the database isn't there anymore, it fails on doing so.

I never found a way to change the configuration of mysql.sock. I would just reconfigure it and point to the new server with appropriate credentials but I can't find anywhere in the internet how to do so.

I could just forget the old mysql, but any changes I make will have to be manually updated in the remote server, and I fear this script is controlling a lot of other stuff I don't control.

In short, my issue is: mysql.sock points to wrong database. How could I change this?

Thanks for the interest!

It is a socket file used for inter-process communication, you have to stop the local MySQL server and make sure the mysql.sock doesn't exist. Then you have to re-create the socket file to send the connection to the correct destination (in your case, it is Amazon RDS).

There are many ways to create a socket file, for example you can use socat, and the command is (you may have to modify to your settings) -

socat UNIX-LISTEN:/path/to/mysql.sock,fork,reuseaddr,unlink-early,user=mysql,group=mysql,mode=777 TCP:<Amazon RDS Server>:3306 &

Source: http://linux.die.net/man/1/socat

Note: This isn't the right way to fix this, so you may want to check the bnconfig script to get a proper fix.


Why does a comparison between CITEXT and TEXT fail? As expected, when doing an equality test like this, the comparison succeeds:

CREATE TABLE citext_test (
    value citext PRIMARY KEY
);

INSERT INTO citext_test VALUES ('one');
INSERT INTO citext_test VALUES ('two');

SELECT * FROM citext_test WHERE value = 'One';

However, if the value being compared against is of type 'text', the comparison fails:

SELECT * FROM citext_test WHERE value = 'One'::text;

Using EXPLAIN, it appears the raw value in the first example is cast to CITEXT:

Index Only Scan using citext_test_pkey on citext_test  (cost=0.15..8.17 rows=1 width=32)
  Index Cond: (value = 'One'::citext)

I'd like to compare against TEXT columns from joined tables. Do I really need to cast these columns to CITEXT to get comparisons to work? I thought part of the advantage of using CITEXT was not having to remember to add things like this (e.g. LOWER(some_value));

Do I really need to cast these columns to CITEXT to get comparisons to work?

Yes, that's what you have to do. That's not how the operator works. You're supposed to set this type on the table so you do not have to this. If one says it's case insensitive text, and the other says it's case-sensitive text, the case-sensitive text wins. The reason why the first example works is because

SELECT * FROM citext_test WHERE value = 'One';

is essentially the same as

SELECT * FROM citext_test WHERE value = 'One'::unknown;

Internally, that gets promoted by the operator to citext. You can disambiguate with the cast,

SELECT * FROM citext_test WHERE value = 'One'::citext;

or the functional form,

SELECT * FROM citext_test WHERE citext_eq(value, 'One');

But the apropos solution would be to ALTER TABLE for the table you're joining to and set its type to citext too.


T-SQL Create Query - Compare and Calculate I got a mini fantasy league (NBA) and would like some help with a query that turns averages into "ranking points".

Basically I got two tables.

Source table:
This is a table with statistical per game averages for a category (category = column).

Destination table or result set:
This is a points table. If I have the highest average in a category I get maximum points which is same as number of teams.
So if I have 10 teams and I have the highest average in 3PM category I get 10 points, and so on down to 1 point for the worst team in the category.
Last column is a total of all points per team.

The help I need:
I would like to create a query that compares every teams value in a given column/category in the source table, ranks the results and gives the right amount of points. When finished it adds up the points per team to the Total PTS table.

Appreciate it if someone would help me or show me the path to fantasy glory.

Did CTE with RANK/OVER:

Rank3PM = RANK() OVER (ORDER BY [3PM per Game])

and in the select I ended with:

TotalPoints =  Rank3PM + RankREB + RankAST..

Thanks.


How to switch between Oracle 11g and 12c on same linux I have oracle11g and 12c installed on same machine and few test cases that need to be run on both versions.
Can power up both database simultaneously and run test paralally.
Do you know any document that describe this scenario of multiple oracle on same machine.

Note: I am not from DB background and just using it for something so please suggest accordingly

Check the /etc/oratab file. In there the ORACLE_HOME is defined in the second field. Make sure that both instances point to the right ORACLE_HOME. If you do an  . oraenv to change from one instance to another this scripts sets the correct ORACLE_HOME so you connect with the right binaries to start, stop, and access the instance. The file is normally also used to stat-up the instances at boot time.

Example of a line in the /etc/oratab file:

DEVA:/u01/app/oracle/product/11.2.0/db_2:Y

DEVA is the name (SID) of the instance, /u01/app/oracle/product/11.2.0/db_2 is the ORACLE_HOME, and Y indicates that the instance must be started at boot time.

You say that you have no DB background so be aware that you cannot run the same instance in both versions of Oracle.


What is a semaphore wait? I got the following error in my MariaDB 10.x hostname.err log:

InnoDB: ###### Diagnostic info printed to the standard error stream
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.

Can someone please explain (as simply as possible ;-D) what a "semaphore wait" is within the context of MySQL/MariaDB?

All I could gather from Mr. Google is that they are "related to internal synchronization between threads in mysqld"

Semaphores throttle access to a resource.  If it's not available a process has to wait until it is.  Very long waits can signal that you have a deadlock situation and something needs to be killed to break it up.

https://stackoverflow.com/questions/2332765/lock-mutex-semaphore-whats-the-difference

  A lock allows only one thread to enter the part that's locked and the
  lock is not shared with any other processes.

  A mutex is the same as a lock but it can be system wide (shared by
  multiple processes).

  A semaphore does the same as a mutex but allows x number of threads to
  enter, this can be used for example to limit the number of cpu, io or
  ram intensive tasks running at the same time.


PAGEIOLATCH_SH and LCK_M_IX wait type I'm seeking for a resolution into wait types I observe on one of my servers.
I've been keeping wait statistics for a long time and can positively say that the top two are PAGEIOLATCH_SH and LCK_M_IX with 29% and 20% respectively of total waits of the instance.
There is only one database in the (only) instance.
I have searched into the resolution online but what I need is some solid steps to take and advice on where and what to look.
Thank you all.

There are a few causes of this, the top ones are: storage subsystem, bad t-sql, missing/bad indexes.  Paul Randall talks about this on his blog and at the sqlperformance blog.  I like the scripts from Glenn Berry to find missing indexes and also SP_BlitzIndex.

How do you know if you can trust the recommendations?  Well I examine the existing indexes first to see what I have and if they can be altered to accommodate the missing columns.  I also like to look in the plan cache to get an idea of what some of the top queries are on that table to see if it would actually make a difference.  If you have a test/dev instance, you can implement there first which will give you an idea of the size any possibly usage pattern if you can generate a workload.  If you are going right into prod, monitor sys.dm_db_index_usage_stats (check the index reads/writes query in Glenn Berry's DMV queries)and check the reads/writes to ensure it is used.  You could also use SQL Sentry's Plan Explorer, but I think you need the pro version for the indexes.

I'm sure someone has a better way to do it, but this has been working for me so far.


What are the best practices regarding lookup tables in relational databases? Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column.
For example, suppose we have a lookup table called party (meant to store information about political parties) that has two columns:

party_code_idn, which holds system-generated numeric values, and (lacking business domain meaning) works as a surrogate for the real key.
party_code, is the real or “natural” key of the table because it maintains values that have business domain connotations.

And let us say that such table retains the data that follows:
 +----------------+------------+
 | party_code_idn | party_code |
 +----------------+------------+
 |              1 | Republican |
 |              2 | Democratic |
 +----------------+------------+

The party_code column, which keeps the values 'Republican' and 'Democratic', being the real key of the table, is set up with a UNIQUE constraint, but I optionally added the party_code_idn and defined it as the PK of the table (though, logically speaking, party_code may work as the PRIMARY KEY [PK]).
Question
What are the best practices for pointing to lookup values from transaction tables? Should I establish FOREIGN KEY (FK) references either (a) directly to the natural and meaningful value or (b) to surrogate values?
Option (a), for example,
 +---------------+------------+---------+
 | candidate_idn | party_code |  city   |
 +---------------+------------+---------+
 |             1 | Democratic | Alaska  |
 |             2 | Republican | Memphis |
 +---------------+------------+---------+

has the following properties1:

Readable for the end user (+)
Easy to import-export across systems (+)
Difficult to change the value as it needs modification in all referring tables (-)
Adding new value is not costly (=)

I think it is almost like “pass by value”, to draw an analogy from function call in application programming jargon.
Option (b), for instance,
 +---------------+----------------+---------+
 | candidate_idn | party_code_idn |  city   |
 +---------------+----------------+---------+
 |             1 |              1 | Alaska  |
 |             2 |              2 | Memphis |
 +---------------+----------------+---------+

has the properties below:

Not readable for the end user (-)
Difficult to import-export as we need to de-reference it (-)
Easy to change values, as we are only storing references in transaction tables (+)
Adding new value is not costly (=)

It is very similar to “pass by reference”, if comparing to function call in app programming parlance.
Import-Export can also be done in a different way, i.e., just by populating the look-up table again and then re-seed the surrogate column. I hope I am getting this right, this is something I have just heard as a possibility.
1. Note that +, - and = indicate the benefit of those properties.
Question
Quite importantly: Is there a difference between a lookup (or code) table and a FK reference if we are just going to use the latter approach? I think they work just the same.
Related resources

How Important Are Lookup Tables?

Best Practices With Code Or Lookup Tables

Lookup Tables Best Practices DB Tables Or Enumerations

There is a third approach which has some of the advantages of your two options - put an actual code in the code table. By this I mean a short character sequence that captures the essence of the full value and is unique. For your given example it may be
Idn: 1
Name: Democrats
Code: D      (or DEM)

The Code is carried into transactional tables as a foreign key. It is short, intelligible and somewhat independent of the "real" data. Incremental changes to the a name would not suggest a code change. Should Republicans decamp en masse, however, a change of code may be necessary, with its attendant problems that a surrogate id would not incur.
This style has been termed an abbreviation encoding. I can recommend Celko's writing on this. Google books holds several examples. Search for "Celko encoding".
Other examples:  2 or 3 letter encodings for countries, 3-letter encoding (GBP, USD, EUR) for currency codes. Short, self-explaining and not changing (and there is an ISO for them).
Each of Idn, Code and Name will be unique so each is a candidate key and any one could be chosen as the primary key. So for the example given Idn could be removed from the table definition and Code used instead. Different DBMS handle integers and strings in their own way, so there may be performance considerations. It may be useful to have Idn as the FK in some tables and Code in others.


Understanding mysqltuner Recomendations w/ Unused Catalogs I have a MySQL 5.0.77 instance running with three catalogs in it.

Two of the catalogs contain all InnoDB tables and are actively used.

The third is all MyISAM and is basically unused, and is only still in the server as a precaution.  Basically, it is an old version of one of the other catalogs that used to be referenced to ensure that data migration was correct.

I have been running mysqltuner periodically so I can get a decent baseline configuration before I begin real profiling on whole system.

Even though I am not querying or even opening connections to the MyISAM catalog, is its presence throwing off the mysqltuner recommendations?

For example, can I truly ignore the warnings about key_buffer_size, or will bad values for this negatively impact the whole server even when I am not really using MyISAM?

Is having this unused catalog negatively impacting the memory footprint of the server?  If so, is there any way to mitigate this, other than moving the catalog somewhere else and doing a DROP?

Thanks.

If you know that the MyISAM tables are not being actively queried, not only can you ignore recommendations for key_buffer_size, you could actually lower it.

What can you do with the extra RAM you just reclaimed? One or more of the following options:

OPTION 1) Add that number to innodb_buffer_pool_size
OPTION 2) Slightly increase max_connections
OPTION 3) Slightly increase by 1M (given to each DB Connection) one or more of the following

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd buffer_size

After restarting mysqld, rerun mysqltuner and evaluate the resulting recommendations. Taking key_buffer_size out of the equation is enough in your instance.


Migrate MySQL to another partition in same server I have a instance of MySQL in a local server. To improve the performance, I put a SSD just for MySQL.

Now I need migrate the MySQL instance, with my databse (a data has 25GB) for the SSD.

What the best way to do that?
I thinked in export the data and after reinstall the MySQL in SSD and import the data again. But how is a big database, I will have a large downtime.

Are there a another way?

For linux (as root user)

Stop mysql service through systemctl
rsync or cp -a the whole of /var/lib/mysql to your preferred destination.
mv /var/lib/mysql /var/lib/mysql-old
edit mysql configuration file and change datadir= under mysqld
Start mysql service through systemctl.


SQL Server - create user for a local Windows login from another instance (AlwaysOn) I have two SQL Server instances working as the AlwaysOn AG. There is a Windows login in each instance created from a local Windows account and mapped to respective users in the AG databases. Say I'm adding a new database to the AG.
Is there a way  to pre-provision a user mapped to the local Windows login from another (secondary) node without having to failover the AG - so that when the actual failover occurs, that user is fully operational?

To pre-provision the local Windows database accounts, add the local Windows database users to the database before you add the database to the AG. Steps below.

create the new database on the secondary
create the secondary node local login and database user
backup the database and drop from the secondary
restore the database to the primary
create the primary node local login and database user
add the database to the AG
backup the database and transaction log
restore the database and log backups to the secondary WITH NORECOVERY


PostgreSQL 11: how to partition 1000 tenants Setup

We have a multi-tenant app that has about 1000 customers more or less. When a customer churns we delete all their data after a period. We have a few tables that are pretty big and we're considering using partitioning to split them per customer.

Problem

1000 tenants (customers) are a lot of partitions - is it reasonable to do this on PostgreSQL?

More details

Currently, the separation between our tenants is via an account_id column on all tables in the DB. There are a few tables that are pretty big. For example, there is an event table (the one I'm interested in partitioning) that contains audit logs and other events for everything that happens in our app.

Here are a few facts about the event table:

It contains about 300M rows + a few of composite/partial indexes.
The count of events by account_id is very uneven, 5% accounts have 50% of the data.
There is a timestamp field and a few others (JSONB, author_id, etc..)
Write operations: inserts mostly and deletes (per account_id). Deletes can potentially be millions of rows. No updates. Deletes of big accounts are rare and not a big performance issue at the moment.
Selects are either for a specific event (by account_id + id) or all events in a given period of time. Period of time is not always set. account_id is always present in the query.

Possible solutions

Partionining by account_id:

Pros:

Deletes will be fast because of DROP TABLE.
Queries should also be pretty decent since all queries contains WHERE account_id = 123

Cons:

Are 1000 partitions too much for Postgres?
Uneven distribution of events per account creating a few super big partitions and a few small ones.

Partitioning by timestamp:

Pros:

Recent data is usually mostly accessed and will make queries with a timestamp faster.
More predictable/even distribution of events.

Cons:

Deletion of a single account might touch a lot of partitions - not a big concern.
Always needs to contain a filter by timestamp - which is not always possible.

Partitioning is mostly about speeding up deletes and sequential scans.

If your biggest pain is the big deletes by account_id, list partitioning on that column would be the best solution.
If your biggest pain is getting rid of all data older than a certain date, range partitioning by time would be the solution.
If you have may queries that have to perform sequential scans of large tables, but have a WHERE condition on account_id or a time constraint, partitioning on that WHERE condition would allow PostgreSQL to perform the sequential scan only on some of the partitions.

You have to decide if any of these things is painful enough for you to consider partitioning. Partitioning doesn't come for free: it will increase query planning time and sometimes execution time as well.

Partitioning won't make index scans faster, often the opposite is the case. Only partition if you expect a real benefit from it.

1000 partitions is almost too many to be efficient. You might consider having separate partitions for the bigger accounts and bundling the rest, perhaps using a default partition.


How to properly restore database from file groups backups? I have very (from my point of view) strange error while trying to restore SQL server DB from a series of filegroup backups.

Here is my corrent config:

I have DB with 2 files 2 filegroups and 1 log file.
1st group (called PRIMARY is not read-only and contains my current data).
2nd is (called HISTORICAL) is read-only.

I also have partitioning.

Because of lack of space I make following:

After some  service operations (moving data from PRIMARY to HISTORICAL FG) I make FULL Database backup (each month).
After that make FULL backup of both groups and then I make log backup.
I make PRIMARY FG full backup each night.
I make PRIMARY FG differential backup each hour.
I make t-log backup every 15 minutes.

My strategy is to minimize space required for backups.

In case of faliour I wil restore FULL backup from beginning of the month. After that I will restore HISTORICAL backup (which is only one for each month). After that I will restore full PRIMARY FG backup from desired time (I am not sure if I have to restore PRIMARY FG backup after FULL DB backup). As last step I will restore t-log backup and return DB in operational state (if needed).

I do that operations in the past. Right now I receive error that the LSN that I tried to restore is too recent. Why this happends?

The sequence may be a bit off for a piecemeal restore you're attempting, in particular when to apply the tlogs. This is what TechNet has documented.

  Piecemeal Restore Under the Full Recovery Model

  Under the full recovery model or bulk-logged recovery model, piecemeal
  restore is available for any database that contains multiple
  filegroups and you can restore a database to any point in time. The
  restore sequences of a piecemeal restore behave as follows:

  •Partial-restore sequence

  The partial restore sequence restores the primary filegroup and,
  optionally, some of the secondary filegroups.

  The first RESTORE DATABASE statement must do the following:

  ◦Specify the PARTIAL option. This indicates the start of a piecemeal
  restore.

  ◦Use any full database backup that contains the primary filegroup. The
  common practice is to start a partial restore sequence by restoring a
  partial backup.

  ◦To restore to a specific point in time, you must specify the time in
  the partial restore sequence. Every successive step of the restore
  sequence must specify the same point in time.

  •Filegroup-restore sequences bring additional filegroups online to a
  point consistent with the database.


Why can not I get any row in a MySQL query from SQL Server? The problem I have is that I can not visualize any row with the following query:

SELECT * FROM OPENQUERY ([LINK_MYSQL], 'SELECT * FROM mysql.vw_bd_date;')

The query did not show any error except that it did not throw me any lines either. The consulted view found in MySQL is the following:

create view vw_bd_date
as
SELECT `information_schema`.`TABLES`.`TABLE_SCHEMA` AS `base_de_datos`,MIN(`information_schema`.`TABLES`.`CREATE_TIME`) AS `FECHA_CREACION`
FROM `information_schema`.`TABLES`
WHERE ((`information_schema`.`TABLES`.`TABLE_SCHEMA` NOT IN ('information_schema','sys','mysql','performance_schema','test','test\\_%'))
AND (`information_schema`.`TABLES`.`DATA_LENGTH` IS NOT NULL) AND (`information_schema`.`TABLES`.`INDEX_LENGTH` IS NOT NULL))
GROUP BY `information_schema`.`TABLES`.`TABLE_SCHEMA`

This is the use in a much larger query where I do the join with other views to perform a database inventory, but also as commented, the fields in this particular view throw me NULL.

We tried to do all kinds of cast without success.

Any idea would be very helpful.

Thank you

As I'm obviously not even close to proficient in MySQL, my answer won't really focus on that side of your question.  If the view returns what you are expecting from within MySQL, I suspect the issue is on the SQL Server side.  What driver are you using?  If it's the built-in Microsoft driver for MySQL, I would strongly recommend downloading the MySQL Driver from their site and installing that as a new provider on your SQL Server.  When connecting to other RDBMS systems, I have had significantly better luck using the driver provided by the vendor of that application rather than using the generic driver provided by Microsoft.


Can't run PostgreSQL locally I am working to run PostgreSQL is running locally but I cannot connect.
I installed postgress using brew, then I downloaded http://postgresapp.com

When I try to create a database

  createdb: could not connect to database postgres: could not connect to
  server: Permission denied     Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

I tried to troubleshot this by using :

sudo sysctl -w kern.sysv.shmall=65536
sudo sysctl -w kern.sysv.shmmax=16777216

But nothing work out.

Any ideas how to solve this

If you have installed postgresql with brew there's one createdb that comes with it and that will connect to brew's postgresql.

Then if you have also installed Postgres.app there's another createdb at a different location that will connect to Postgres.app's postgresql.

Since OS X also ships with its own createdb in /usr/bin, that's a third createdb that expects to connect to Apple's postgresql, which is not running unless it's OS X Server Edition. According to the error message, this is the one that you're trying to use and which fails.

The solution is to use the createdb command that comes with the PostgreSQL you're actually running. Locate its directory and type the full path of createdb, as in /Applications/Postgres.app/Contents/MacOS/bin/createdb, and/or change your $PATH as suggested in postgresapp's doc.

This is a common problem for PostgreSQL on Mac OS X due the existence of many concurrent packages and of client-side programs pre-installed by Apple.


structuring media uploads (users, images, videos) using sequelize.js (using pseudocode to simplify)

say I have a User (users table)

User {
  id: PK
}

and I want to have them upload images and videos (media). I want to be able to differentiate between images or videos but they have overlapping fields beyond their media types. so that I can query for all of their media, or just videos or images.

I am unsure of the best way to structure this. here are two options I came up with

Option 1: use media table with a media_type column (enum values video and image)

Media {
  id: PK
  media_type: enum (image, video)
  uploader_id: FK (users.id)
  url: VC
  length: Int (null for images)
}

sequelize:

user.getMedia(); // get all media
user.getMedia({ where: { media_type: 'image' } }); // get images
user.getMedia({ where: { media_type: 'video' } }); // get videos

I could also use the scope (on media_type) and alias (Images, Videos) options on the Model.associate to create getMedia(), getImages(), getVideos() methods.

Option 2: separate images and videos tables

Image {
  id: PK
  uploader_id: FK (users.id)
  url: VC
}

Video {
  id: PK
  uploader_id: FK (users.id)
  url: VC
  length: Int
}

sequelize:

user.getVideos();
user.getImages();

So my questions are:

which option (of the above 2 or another you have) is preferred and why?
if option 1 is chosen does it become unmanageable if I add more media types?
how do I do getAllMedia() for the second option? The only thing I can think of is to write a custom prototype method that queries each table and reduces them (in order of upload date). this seems incorrect.
say I also keep track of all the User's likes from the media they view. option 1 would allow me to keep the media.id FK on the likes table. option 2 appears to lead me to image_likes and video_likes which seems redundant.

I am leaning towards Option 1 for this because I actually think that as you add more media types option 2 is going to get unmanageable when it comes to querying, especially when you are trying to get all of a user's media, it would just require too much eager loading of the other tables, or a lot of join tables.

With option one, each piece of media has it's own unique id, so if you set up the association between Users, Media, and Likes so that:
Likes belongTo users
Users can hasMany Likes
Media can hasMany Likes
Likes hasOne media (so each individual like is a representation of it's userId and the mediaId it was associated to)

then you would be able to query your Likes table for all Likes with a certain userId (to show all the media a user has liked), or for all Likes for a certain mediaId (ex. to show the amount of likes for a specific image).

You wouldn't even necessarily have to specify the media type when querying likes because you'd have your mediaId or UserId depending on what you are trying to query.


pt-online-schema-change's triggers fail with "DELETE command denied" on insert We recently tried pt-online-schema-change to add a column to a table. It worked mostly as expected but one thing puzzles me: in case the account we use to do the migration doesn't have the DELETE permission, our application (which keeps running simultaneously) gets errors that say "DELETE command denied to user 'pt'@'localhost' for table '_xxx_new'" when the app is performing an insert on the xxx table.

My understanding is that the triggers are to do inserts to the destitation _xxx_new table when a new record is inserted in the source xxx table. How come it may fail with not having a DELETE permission??

Percona docs are pretty generic on this and no googling helped, so will be thankful for any ideas!

The issue turned out to be that the triggers that the pt-online-schema-change tool creates for insert and update use the REPLACE DDL command of MySQL, which requires the DELETE permission to execute.


What is seed in RAND function in SQL Server I am not able to understand as to what is the use of seed in the RAND function of SQL Server?

I read the below link but i am not able to understand what it is doing?
Link 1
Link 2
Link 3

After trying out few random seed number i found the below results:-

Select RAND(1) - 0.713591993212924
Select RAND(2) - 0.713610626184182
Select RAND(3) - 0.71362925915544
Select RAND(4) - 0.713647892126698
Select RAND(5) - 0.713666525097956

Whenever i provide the seed from 1 to 5 i always get the number that i have mentioned above.

Why is that so?

Microsoft docs explains this in details here: apparently RAND() returns a really random result only if it is called without specifying a seed value (because in this case seed itself is assigned randonly).

So calling RAND() without seed should always return a different value, while calling RAND(seed) should always return the same result if called with the same seed.

Here is an excerpt from Microsoft docs:

  Arguments seed Is an integer expression (tinyint, smallint, or int)
  that gives the seed value. If seed is not specified, the SQL Server
  Database Engine assigns a seed value at random. For a specified seed
  value, the result returned is always the same.

  Remarks
  Repetitive calls of RAND() with the same seed value return the
  same results.


MySQL utf8mb4 Migration - Replication Considerations We're planning to migrate a MySQL database with a mix utf8 and latin1 tables to the new utf8mb4 character set per this article: https://mathiasbynens.be/notes/mysql-utf8mb4.

Our server is 5.7.22 and the live database has several hundred InnoDB tables, is several GB large and is setup in a master/slave replication.  I've successfully ran the conversion scripts on a copy of the database on a test server.  It takes about 3 hours to complete.

When this is done live, should replication be disabled and the conversion scripts be ran against the master and slave individually or is it OK to run against master with replication active?

We are planning a maintenance window for this update - all updates/queries from our applications will be suspended during this process.

I'm unsure about this because I've been reading about the MySQL 8 Upgrade (a separate, future project for us) and MySQL mentioned performing large operations requiring table rebuilds with replication disabled:
https://dev.mysql.com/doc/refman/8.0/en/replication-upgrade.html.  However, this may be relevant only in the context of the version 8 upgrade, which we aren't doing simultaneously.  Thanks.

First make sure any latin1 columns have not been messed up.  Find an accented letter and do SELECT HEX(col) ... -- any accented letter should show as 2 bytes.  For example é is latin1 hex E9 and utf8mb4 hex C3A9.

If you see C383C2A9, you have "double encoding" and you need to worry about that now.

As for Master/Slave,...

If it is Master-Master (for ease of failing over), then consider doing them one at a time, thereby avoiding more than a brief downtime (to failover).  Be sure to use the SET to disable replication for the session.

Are you using ALTER TABLE ... CONVERT TO CHARACTER SET ...?  (That is probably the only valid way to be doing the conversion.)

With Master-Slave and plenty of downtime, that ALTER can simple go through replication.


It's possible to skip duplicate keys (i.e. MERGE) in a BULK INSERT in MySQL? I have a INSERT...SELECT statement that I want to run several times from TABLE1 to TABLE2 as the data in TABLE1 will change overtime.

I don't expect the past data from select to change overtime, the select will just return more new data, and the same old data of previous queries.

But I cannot insert old data again because of the unique key constraint on the primary keys.

What I would like to achieve is a kind of INSERT ...ON DUPLICATE KEY UPDATE... but for a INSERT...SELECT.
Skipping the duplicate keys, will be enough, but I'm not sure how to do it

There is no MERGE statement in MySQL. You can do this quite easily as follows (all DDL and DML shown at bottom of post and also in the fiddle here):

I created and populated two tables foo_1 and foo_2 with identical data and identical PRIMARY KEYs, the fields (foo_x, foo_y);

I then added a record to table foo_1.

Then I performed the query:

INSERT INTO foo_2
SELECT * FROM foo_1 WHERE (foo_x, foo_y) -- foo_x, foo_y are the PK!
NOT IN (SELECT foo_x, foo_y FROM foo_2);

And then SELECT * FROM foo_2 and the new record from table foo_1 had been copied over to table foo_2. This is a simple and effective method of doing what you want as far as I can see. Doing this with a single field PRIMARY KEY is even easier!

=============== DDL and DML ============

table foo_1:

CREATE TABLE foo_1
(
  foo_x INTEGER NOT NULL,
  foo_y INTEGER NOT NULL,
  foo_2 INTEGER NOT NULL,
  foo_3 VARCHAR (25) NOT NULL,
  PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_1
VALUES
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');

and table foo_2:

CREATE TABLE foo_2
(
  foo_x INTEGER NOT NULL,
  foo_y INTEGER NOT NULL,
  foo_2 INTEGER NOT NULL,
  foo_3 VARCHAR (25) NOT NULL,
  PRIMARY KEY (foo_x, foo_y)
);
INSERT INTO foo_2
VALUES
(1, 1, 2, 'foo_3_val_1'),
(1, 2, 7, 'foo_3_val_2'),
(1, 3, 8, 'foo_3_val_3'),
(1, 4, 9, 'foo_3_val_4');

Insert new record into table foo_1:

INSERT INTO foo_1 VALUES (1, 5, 24, 'foo_3_val_5');

And then:

INSERT INTO foo_2
SELECT * FROM foo_1 WHERE (foo_x, foo_y)
NOT IN (SELECT foo_x, foo_y FROM foo_2);

And then check that table foo_2 now contains the single new record from table foo_1:

SELECT * FROM foo_2;

Result:

foo_x   foo_y   foo_2   foo_3
1       1           2   foo_3_val_1
1       2           7   foo_3_val_2
1       3           8   foo_3_val_3
1       4           9   foo_3_val_4
1       5          24   foo_3_val_5


Select column to be inserted based on input column value Assume the following two tables (and ignore the quality of the database design):

Table 1 has columns id, sequence_number, value: it contains many rows.
Table 2 has columns id, seq1, seq2, seq3, seq4, seq5: it is empty.

The goal is to fill Table 2 with Table 1's value, such that if in Table 1 one row is sequence_number = 3; value = 1564.1, then in Table 2 one should insert a row with seq3 = 1564.1. (the other columns aside from id should be empty).

How to do that in SQL? I don't want to hard code all 5 options (because in my actual use case there are over 50.), but I don't know how to condition the column name that should receive the value based on the value.

By using CASE statements..

INSERT INTO table2 (id, seq1, seq2, seq3, seq4, seq5)
SELECT
  <SOME ID VALUE>,
  CASE WHEN sequence_number = 1 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 2 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 3 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 4 THEN value ELSE NULL END,
  CASE WHEN sequence_number = 5 THEN value ELSE NULL END
FROM table1

If you consider that hard coding, then (as far as I'm aware) your only other option would be dynamic SQL within a plpgsql block.

DO $$
DECLARE
  row table1%ROWTYPE;
  colName VARCHAR(10);
BEGIN
  FOR row in SELECT * FROM TABLE1 LOOP
    colName := 'seq' || row.sequence_number;
    EXECUTE '<SQL HERE THAT USES colName FOR INSERT>';
  END LOOP
END;
$$ language plpgsql;


PostgreSQL CREATE TABLE creates with incorrect owner I'm using PostgreSQL 9.2.4.  When I create a table as a non-superuser in a database owned by that non-superuser, it is owned by the postgres user, so I can't put any data into it unless I explicitly grant myself permission.

I created the non-superuser like this:

admin_user=# create role "test1" NOINHERIT LOGIN ENCRYPTED PASSWORD 'wibble' CONNECTION LIMIT 10;

Then I created a database owned by test1 like this:

admin_user=# create database "test1$db1" with owner "test1";

Then I started a new psql as user test1, and created a table:

test1$db1=> create table test_table (column_name varchar(50));

But I couldn't insert into it:

test1$db1=> insert into test_table values ('some data');
ERROR:  permission denied for relation test_table

Checking the permissions shows that the table is owned by the postgres user:

test1$db1=> \dt
             List of relations
 Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
 public | test_table     | table | postgres

However, I can grant myself permissions and do stuff:

test1$db1=> grant insert, select on test_table to test1;
GRANT
test1$db1=> insert into test_table values ('some data');
INSERT 0 1
test1$db1=> select * from test_table;
 column_name
-------------
 some data
(1 row)

What's going on?  I'm pretty sure this used to work.  And the PostgreSQL docs for CREATE TABLE say

  CREATE TABLE will create a new, initially empty table in the current
  database. The table will be owned by the user issuing the command.

Having to grant permissions to myself on my own tables doesn't sound like it's what I should have to do.

Any help much appreciated!

[UPDATE]

It looks like this might be caused by some kind of change to the PostgreSQL package at http://apt.postgresql.org/pub/repos/apt/

We have three machine images, all of which installed PostgreSQL from that source:

Install at 23 July 2013, 18:47 UTC -- correct table ownership
Install at 23 July 2013, 19:39 UTC -- correct table ownership
Install at 24 July 2013, 13:16 UTC -- error

So perhaps something has been changed in the packaging.  I'll drop a line to the mailing list for that.

That the owner of your new table turns out to be postgres is very odd.

Either way, you can make Postgres grant or revoke any privileges to / from any role by default with ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT INSERT, UPDATE, DELETE ON TABLES TO test1;


Does adding a surrogate key get rid of an identifying relationship between two entities? In the following example, we have a Comments table with a composite primary key (PK)

CREATE TABLE Comments (
  video_id INT NOT NULL,
  user_id INT NOT NULL,
  comment_dt DATETIME NOT NULL,
  PRIMARY KEY (video_id, user_id, comment_dt),
  FOREIGN KEY (video_id) REFERENCES Videos(video_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

There appears to be an identifying relationship, as the primary key of Comments includes its foreign keys.

However, watch what happens when you give this Comments table a surrogate primary key of its own:

CREATE TABLE Comments (
comment_id SERIAL PRIMARY KEY,
video_id INT NOT NULL,
user_id INT NOT NULL,
comment_dt DATETIME NOT NULL,
FOREIGN KEY (video_id) REFERENCES Videos(video_id),
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

Does that get rid of the identifying relationship? Logically, it still seems to be there, so does this really come down to the physical model and whether or not you want to include a composite key? Are identifying relationships even necessary?

For reasons of accuracy, it is indispensable to know the informational characteristics of the business environment under consideration thoroughly so as to supply a proper database design answer but, in order to answer the main inquiry and the other subquestions, I will work on some points of this post in terms of a several assumptions based on the information you did disclose.

Note: An important omission is that the question does not made known the property or properties whose values uniquely identify the entities of type Video in your particular business domain —provided that (i) Videos.video_id is an additional column set up to contain system-controlled surrogates and (ii) there is one or more conceptual properties whose values uniquely individuate each Video instance, which would entail a natural KEY in the respective logical-level table—.

Of course, supplying a detailed description of the entity types User, Video and Comment would have been of immense assistance in understanding the case at hand in a fitting manner and elaborating a more rounded answer.

  Does adding a surrogate key get rid of an identifying relationship between two entities?

First of all, identifying relationship types, entity types, properties, entities (i.e. instances of an entity type), etc., are aspects that are part of the conceptual level of abstraction of a given database. In order to create a suitable conceptual schema (i.e., one that mirrors the real world faithfully), the database modeler/designer must work very closely with business experts to unveil and define these constructs with high precision, due to the fact that it must be the foundation of the database structure and constraints.

Once the modeler/designer has developed a stable conceptual schema, it has to be represented with exactitude in the respective logical SQL-DDL design. As for your example, you have added an extra column, Comments.comment_id, for system-controlled surrogates to the Comments table, which I assume stands for an entity type called Comment. That kind of column is an artifact that is not part of the significant conceptual schema, you added it and declared it as the PRIMARY KEY of said table, but having proceeded so does not mean that the conceptual schema disappears or that it loses importance.

So, assuming that

(a) the addition —and the declaration as the PRIMARY KEY— of the Comments.comment_id column is actually beneficial;
(b) in the real world, an instance of the Comment entity type is in fact uniquely identified by the values of the combination of the properties VideoId, UserId and CommentDateTime; and
(c) the remaining columns [and constraints] of your Comments table mirror the properties [and business rules] of the Comment entity type correctly…

…then you also have to

(d) declare the combination of (Comments.video_id, Comments.user_id, Comments.comment_dt) as a composite ALTERNATE KEY, by means of one multi-column UNIQUE constraint accompanied by three NOT NULL counterparts.

In this way, you would not be “getting rid of” the identifying relationship type, and one should not get rid of any conceptual representation at the logical level unless the conceptual schema has undergone a modification produced by a real-world informational change.

  Logically, it still seems to be there, so does this really come down to the physical model and whether or not you want to include a composite key?

Conceptually speaking, an identifying relationship type —if and only if discovered and delimited properly— is there, it does not matter if you represented it or not in a logical-level design. The problem with not representing it properly is that (1) the logical design does not reflect (2) the conceptual schema with (3) accuracy.

On the other hand, if a SQL table is carrying the meaning of a certain entity type whose instances are uniquely identified by the values of two or more properties, then the database modeler/designer must declare the applicable multi-column key (be it PRIMARY or ALTERNATE), this is not a matter of wanting or not to include such a constraint. If the multi-column key is not declared, then the rows of the table will eventually become inconsistent with the entity type instances of the real world that they are supposed to represent (a row in a relational table is a fact asserted about a certain entity of an exact type).

In addition, composite keys help, e.g., in:

Ensuring that the connections between the rows values remain consistent with the cardinality ratios that come about between the entity types of significance.
Making the queries meaningful (in contrast with columns enclosing system-controlled surrogate values that are meaningless).
Easing data browsing since they allow, e.g., going from TableA to TableC without passing first through TableC, which speeds up data retrieval.

In the same way, attaching extra columns for system-controlled surrogates should not depend on what the modeler/designer wants or not; to express it differently, it is not a matter of personal preference. Appending this kind of column to a table ought to be well-justified (because, e.g., it will very likely require another supplementary index), and should be decided in terms of the overall characteristics of the database at hand, although, yes, that is a different —yet relevant— topic.

For its part, the physical level of abstraction has to do with indexes, pages, extents, records, partitions, the hard drive itself, other hardware devices, or whatever physical structure the database management system of choice employs to support the logical-level declarations of a database, so the conceptual and logical level definitions must remain independent with respect to physical constructs. The tools of the physical tier serve as well to optimize the performance of a database.

  Are identifying relationships even necessary?

As pointed out above, an identifying relationship type is a conceptual construct, and it comes about in terms of the informational characteristics of the organization of interest. If a certain business environment presents identifying relationship types, then it involves several requirements: (i) to discover them, (ii) to capture them in the applicable conceptual schema and (iii) to convey all their aspects in the respective logical design.

With regards to identifying relationship types —just like with other conceptual factors—, a database modeler/designer should not

(a) invent them;
(b) overlook them;
(c) omit them; or
(d) misrepresent them.

It is the responsibility of a database/modeler designer to (1) find and (2) define each identifying relationship type of interest in order to be able to (3) represent it accurately in the corresponding logical layout. It is carrying out this set of tasks properly what is, in actual fact, necessary.


Hows to make a DB2 table read-only? We need to test how an application will fail when the DB2 database cannot be written to.

What is the easiest way to make a DB2 table read-only? Is this considered locking?

Thanks in advance,
Bert

There are a couple of ways to test it:

Use LOCK TABLE and put it in "exclusive" mode
REVOKE the privilege to INSERT or UPDATE on that table from the test user
REVOKE the privilege to connect to that database
Change or remove TCP/IP access to the database
...

There are many testing scenarios and most of them will result in a different error message.


How to normalize a table that contains a CK not included in the PK? I have the following table. The PK is model_ID.
Assuming that the modelName field is unique(that is, no two brands can have an identical modelName), there is a transitive dependency between modelName and brand.

What is the optimal solution for the table to comply with 3NF, without having the modelName field as part of the PK?

Model name does not need to be part of the PK.  The problem with this table is brand. "Scott" is repeated. Model relies on Brand, but brand does not rely on model.

Create a brand table. Turn the Model.brand to Brand_FK.


Synchronization using triggers I have a requirement similar to previous discussions at:

Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
Trigger in combination with transaction

I have two tables, [Account].[Balance] and [Transaction].[Amount]:

CREATE TABLE Account (
      AccountID    INT
    , Balance      MONEY
);

CREATE TABLE Transaction (
      TransactionID INT
     , AccountID    INT
    , Amount      MONEY
);

When there is an insert, update or delete against the [Transaction] table, the [Account].[Balance] should be updated based on the [Amount].

Currently I have a trigger to do this job:

ALTER TRIGGER [dbo].[TransactionChanged]
ON  [dbo].[Transaction]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF  EXISTS (select 1 from [Deleted]) OR EXISTS (select 1 from [Inserted])
    UPDATE [dbo].[Account]
    SET
    [Account].[Balance] = [Account].[Balance] +
        (
            Select ISNULL(Sum([Inserted].[Amount]),0)
            From [Inserted]
            Where [Account].[AccountID] = [Inserted].[AccountID]
        )
        -
        (
            Select ISNULL(Sum([Deleted].[Amount]),0)
            From [Deleted]
            Where [Account].[AccountID] = [Deleted].[AccountID]
        )
END

Although this seems to be working, I have questions:

Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail?
My IF and UPDATE statements look strange. Is there any better way to update the correct [Account] row?

1. Does the trigger follow the relational database's ACID principle? Is there any chance an insert might be committed but the trigger fail?

This question is partly answered in a related question you linked to. Trigger code is executed in the same transactional context as the DML statement that caused it to fire, preserving the Atomic part of the ACID principles you mention. The triggering statement and trigger code both succeed or fail as a unit.

The ACID properties also guarantee the whole transaction (including the trigger code) will leave the database in a state that does not violate any explicit constraints (Consistent) and any recoverable committed effects will survive a database crash (Durable).

Unless the surrounding (perhaps implicit or auto-commit) transaction is running at the SERIALIZABLE isolation level, the Isolated property is not automatically guaranteed. Other concurrent database activity could interfere with the correct operation of your trigger code. For example, the account balance could be changed by another session after you read it and before you update it - a classic race condition.

  2. My IF and UPDATE statements look strange. Is there any better way to update the correct [Account] row?

There are very good reasons the other question you linked to does not offer any trigger-based solutions. Trigger code designed to keep a denormalized structure synchronized can be extremely tricky to get right and test properly. Even very advanced SQL Server people with many years of experience struggle with this.

Maintaining good performance at the same time as preserving correctness in all scenarios and avoiding problems like deadlocks adds extra dimensions of difficulty. Your trigger code is nowhere close to be robust, and updates the balance of every account even if only a single transaction is modified. There are all sorts of risks and challenges with a trigger-based solution, which makes the task deeply unsuitable for someone relatively new to this technology area.

To illustrate some of the problems, I show some sample code below. This is not a rigorously-tested solution (triggers are hard!) and I am not suggesting you use it as anything other than a learning exercise. For a real system, the non-trigger solutions have important benefits, so you should carefully review the answers to the other question, and avoid the trigger idea completely.

Sample tables

CREATE TABLE dbo.Accounts
(
    AccountID integer NOT NULL,
    Balance money NOT NULL,

    CONSTRAINT PK_Accounts_ID
    PRIMARY KEY CLUSTERED (AccountID)
);

CREATE TABLE dbo.Transactions
(
    TransactionID integer IDENTITY NOT NULL,
    AccountID integer NOT NULL,
    Amount money NOT NULL,

    CONSTRAINT PK_Transactions_ID
    PRIMARY KEY CLUSTERED (TransactionID),

    CONSTRAINT FK_Accounts
    FOREIGN KEY (AccountID)
    REFERENCES dbo.Accounts (AccountID)
);

Preventing TRUNCATE TABLE

Triggers are not fired by TRUNCATE TABLE. The following empty table exists purely to prevent the Transactions table being truncated (being referenced by a foreign key prevents table truncation):

CREATE TABLE dbo.PreventTransactionsTruncation
(
    Dummy integer NULL,

    CONSTRAINT FK_Transactions
    FOREIGN KEY (Dummy)
    REFERENCES dbo.Transactions (TransactionID),

    CONSTRAINT CHK_NoRows
    CHECK (Dummy IS NULL AND Dummy IS NOT NULL)
);

Trigger Definition

The following trigger code ensures only necessary account entries are maintained, and uses SERIALIZABLE semantics there. As a desirable side-effect, this also avoids the incorrect results that might result if a row-versioning isolation level is in use. The code also avoids executing the trigger code if no rows were affected by the source statement. The temporary table and RECOMPILE hint are used to avoid trigger execution plan problems caused by inaccurate cardinality estimates:

CREATE TRIGGER dbo.TransactionChange ON dbo.Transactions
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF @@ROWCOUNT = 0 OR
    TRIGGER_NESTLEVEL
    (
        OBJECT_ID(N'dbo.TransactionChange', N'TR'),
        'AFTER',
        'DML'
    ) > 1
    RETURN;

    SET NOCOUNT, XACT_ABORT ON;

    CREATE TABLE #Delta
    (
        AccountID integer PRIMARY KEY,
        Amount money NOT NULL
    );

    INSERT #Delta
        (AccountID, Amount)
    SELECT
        InsDel.AccountID,
        Amount = SUM(InsDel.Amount)
    FROM
    (
        SELECT AccountID, Amount
        FROM Inserted
        UNION ALL
        SELECT AccountID, $0 - Amount
        FROM Deleted
    ) AS InsDel
    GROUP BY
        InsDel.AccountID;

    UPDATE A
    SET Balance += D.Amount
    FROM #Delta AS D
    JOIN dbo.Accounts AS A WITH (SERIALIZABLE)
        ON A.AccountID = D.AccountID
    OPTION (RECOMPILE);
END;

Testing

The following code uses a table of numbers to create 100,000 accounts with a zero balance:

INSERT dbo.Accounts
    (AccountID, Balance)
SELECT
    N.n, $0
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND 100000;

The test code below inserts 10,000 random transactions:

INSERT dbo.Transactions
    (AccountID, Amount)
SELECT
    CONVERT(integer, RAND(CHECKSUM(NEWID())) * 100000 + 1),
    CONVERT(money, RAND(CHECKSUM(NEWID())) * 500 - 250)
FROM dbo.Numbers AS N
WHERE
    N.n BETWEEN 1 AND 10000;

Using the SQLQueryStress tool, I ran this test 100 times on 32 threads with good performance, no deadlocks, and correct results. I still do not recommend this as anything other than a learning exercise.


Storing bus routes in a database I have done some research and found that I should store a route as a sequence of stops. Something like:

Start -> Stop A -> Stop B -> Stop C -> End

I have created three tables:

Routes
Stops
RouteStops

...where RouteStops is a junction table.

I have something like:

Routes

+---------+
| routeId |
+---------+
|    1    |
+---------+
|    2    |
+---------+

Stations

+-----------+------+
| stationId | Name |
+-----------+------+
|     1     |   A  |
+-----------+------+
|     2     |   B  |
+-----------+------+
|     3     |   C  |
+-----------+------+
|     4     |   D  |
+-----------+------+

RouteStations

+-------------+---------------+
| routeId(fk) | stationId(fk) |
+-------------+---------------+
|     1       |       A       |
+-------------+---------------+
|     1       |       C       |
+-------------+---------------+
|     1       |       D       |
+-------------+---------------+
|     2       |       A       |
+-------------+---------------+
|     2       |       D       |
+-------------+---------------+

Route 1 goes through

Station A -> Station C -> Station D

Route 2 goes through

Station A -> Station D

Is this a good way to store routes?

According to Wikipedia:

  [...] the database system does not guarantee any ordering of the rows unless an ORDER BY clause is specified [...]

Can I rely on such a database schema or maybe this should be done differently?

This is actually my university project, so I'm just wondering if such schema can be considered as a correct one. For this case, I would probably store only several routes (approx 3-5) and stations (approx 10-15), each route will consist of about 5 stations. I would be also glad to hear how this should look like in case of real and big bus company.

For all business analysis leading to database architecture, I recommend writing rules:

A route has 2 or more stations
A Station can be used by many routes
Stations on a route come in a specific order

The 1st and 2nd rules as you noticed implies a many to many relationship so you concluded rightfully to create routeStations.

The 3rd rule is the interesting one. It implies that an extra column is needed to fit the requirement. Where should it go? We can see that this property depends on Route AND Station. Therefore it should be located in routeStations.

I would add a column to table routeStations called "stationOrder".

+-------------+---------------+---------------
| routeId(fk) | stationId(fk) | StationOrder |
+-------------+---------------+---------------
|     1       |       1       |       3      |
+-------------+---------------+---------------
|     1       |       3       |       1      |
+-------------+---------------+---------------
|     1       |       4       |       2      |
+-------------+---------------+---------------
|     2       |       1       |       1      |
+-------------+---------------+---------------
|     2       |       4       |       2      |
+-------------+---------------+---------------

Then querying becomes easy:

select rs.routeID,s.Name
from routeStations rs
join
Stations s
on rs.stationId=s.StationId
where rs.routeId=1
order by rs.StationOrder;

+-------------+---------------+
| routeId(fk) | stationId(fk) |
+-------------+---------------+
|     1       |       C       |
+-------------+---------------+
|     1       |       D       |
+-------------+---------------+
|     1       |       A       |
+-------------+---------------+

Notes:

I fixed the StationId in RouteStations in my example. You are using the StationName as the Id.
If you don't use a route name, then there's not even a need for routeId since you can get that from routeStations
Even if you would link to the route table, your database optimizer would notice it doesn't need that extra link and simply remove the extra steps.

To develop on note 3, I've built the use case:

This is Oracle 12c Enterprise.

Note that in the execution plan below that table routes isn't used at all. the Cost Base Optimizer (CBO) knows it can get the routeId directly from routeStations's primary key (step 5, INDEX RANGE SCAN on ROUTESTATIONS_PK, Predicate Information 5 - access("RS"."ROUTEID"=1))

--Table ROUTES
create sequence routeId_Seq start with 1 increment by 1 maxvalue 9999999999999 cache 1000;

CREATE TABLE routes
(
  routeId  INTEGER NOT NULL
);

ALTER TABLE routes ADD (
  CONSTRAINT routes_PK
  PRIMARY KEY
  (routeId)
  ENABLE VALIDATE);

insert into routes values (routeId_Seq.nextval);
insert into routes values (routeId_Seq.nextval);
commit;

--TABLE STATIONS
create sequence stationId_seq start with 1 increment by 1 maxvalue 9999999999999 cache 1000;

create table stations(
   stationID INTEGER NOT NULL,
   name varchar(50) NOT NULL
);

ALTER TABLE stations ADD (
  CONSTRAINT stations_PK
  PRIMARY KEY
  (stationId)
  ENABLE VALIDATE);

insert into stations values (stationId_seq.nextval,'A');
insert into stations values (stationId_seq.nextval,'B');
insert into stations values (stationId_seq.nextval,'C');
insert into stations values (stationId_seq.nextval,'D');
commit;
--

--Table ROUTESTATIONS
CREATE TABLE routeStations
(
  routeId       INTEGER NOT NULL,
  stationId     INTEGER NOT NULL,
  stationOrder  INTEGER NOT NULL
);

ALTER TABLE routeStations ADD (
  CONSTRAINT routeStations_PK
  PRIMARY KEY
  (routeId, stationId)
  ENABLE VALIDATE);

ALTER TABLE routeStations ADD (
  FOREIGN KEY (routeId)
  REFERENCES ROUTES (ROUTEID)
  ENABLE VALIDATE,
  FOREIGN KEY (stationId)
  REFERENCES STATIONS (stationId)
  ENABLE VALIDATE);

insert into routeStations values (1,1,3);
insert into routeStations values (1,3,1);
insert into routeStations values (1,4,2);
insert into routeStations values (2,1,1);
insert into routeStations values (2,4,2);
commit;

explain plan for select rs.routeID,s.Name
from ndefontenay.routeStations rs
join
ndefontenay.routes r
on r.routeId=rs.routeId
join ndefontenay.stations s
on rs.stationId=s.stationId
where rs.routeId=1
order by rs.StationOrder;

set linesize 1000
set pages 500
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2617709240

---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |     1 |    79 |     1 (100)| 00:00:01 |
|   1 |  SORT ORDER BY                 |                  |     1 |    79 |     1 (100)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                  |       |       |            |          |
|   3 |    NESTED LOOPS                |                  |     1 |    79 |     0   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS    |     1 |    39 |     0   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | ROUTESTATIONS_PK |     1 |       |     0   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | STATIONS_PK      |     1 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | STATIONS         |     1 |    40 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("RS"."ROUTEID"=1)
   6 - access("RS"."STATIONID"="S"."STATIONID")

Now the fun part, let's add a column name to the route table.
Now there's a column we actually need in "routes".
The CBO uses the index to find the rowID for route 1, then accesses the table (table access by index rowid) and grabs the column "routes.name".

ALTER TABLE ROUTES
 ADD (name  VARCHAR2(50));

update routes set name='Old Town' where routeId=1;
update routes set name='North County' where routeId=2;
commit;

explain plan for select r.name as routeName,s.Name as stationName
from routeStations rs
join
routes r
on r.routeId=rs.routeId
join stations s
on rs.stationId=s.stationId
where rs.routeId=1
order by rs.StationOrder;

set linesize 500
set pages 500
select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3368128430

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     1 |   119 |     1 (100)| 00:00:01 |
|   1 |  SORT ORDER BY                  |                  |     1 |   119 |     1 (100)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                  |       |       |            |          |
|   3 |    NESTED LOOPS                 |                  |     1 |   119 |     0   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                  |     1 |    79 |     0   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| ROUTES           |     1 |    40 |     0   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | ROUTES_PK        |     1 |       |     0   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| ROUTESTATIONS    |     1 |    39 |     0   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | ROUTESTATIONS_PK |     1 |       |     0   (0)| 00:00:01 |
|*  9 |     INDEX UNIQUE SCAN           | STATIONS_PK      |     1 |       |     0   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | STATIONS         |     1 |    40 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("R"."ROUTEID"=1)
   8 - access("RS"."ROUTEID"=1)
   9 - access("RS"."STATIONID"="S"."STATIONID")


Queries and updates extremely slow after IndexOptimize Database SQL Server 2017 Enterprise CU16 14.0.3076.1

We recently tried switching from the default Index Rebuild maintenance jobs to the Ola Hallengren IndexOptimize. The default Index Rebuild jobs had been running for a couple of months without any issues, and the queries and updates were working with acceptable execution times.
After running IndexOptimize on the database:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

performance was extremely degraded. An update statement that took 100ms before IndexOptimize took 78.000ms afterwards (using an identical plan), and queries were also performing several orders of magnitude worse.

Since this still is a test database (we're migrating a production system from Oracle) we reverted to a backup and disabled IndexOptimize and everything returned to normal.

However, we would like to understand what IndexOptimize does differently from the "normal" Index Rebuild that could have caused this extreme performance degradation in order to make sure we avoid it once we go to production. Any suggestions on what to look for would be greatly appreciated.

Execution plan for the update statement when it is slow. i.e.
After IndexOptimize
Actual execution plan (coming asap)

I haven't been able to spot a difference.
Plan for the same query when it is fast
Actual execution plan

I suspect you've got a different sample rate defined between your two maintenance approaches.  I believe Ola's scripts use default sampling unless you specify the @StatisticsSample parameter, which it doesn't look like you're currently doing.

At this point, this is speculation, but  you can check to see what sampling rate is currently being used on your statistics by running the following query in your database:

SELECT  OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) AS TableName
    ,   col.name AS ColumnName
    ,   st.name AS StatsName
    ,   sp.last_updated
    ,   sp.rows_sampled
    ,   sp.rows
    ,   (1.0*sp.rows_sampled)/(1.0*sp.rows) AS sample_pct
FROM sys.stats st
    INNER JOIN sys.stats_columns st_col
        ON st.object_id = st_col.object_id
        AND st.stats_id = st_col.stats_id
    INNER JOIN sys.columns col
        ON st_col.object_id = col.object_id
        AND st_col.column_id = col.column_id
    CROSS APPLY sys.dm_db_stats_properties (st.object_id, st.stats_id) sp
ORDER BY 1, 2

If you see this is coming through a 1s (e.g. 100%) chances are this is your issue.  Maybe try Ola's scripts again including the @StatisticsSample parameter with the percentage getting returned by this query and see if that fixes your problem?

As additional supporting evidence for this theory, the execution plan XML shows vastly different sampling rates for the slow query (2.18233 %):

<StatisticsInfo LastUpdate="2019-09-01T01:07:46.04" ModificationCount="0"
    SamplingPercent="2.18233" Statistics="[INDX_UPP_4]" Table="[UPPDRAG]"
    Schema="[SVALA]" Database="[ulek-sva]" />

Versus the fast query (100 %):

<StatisticsInfo LastUpdate="2019-08-25T23:01:05.52" ModificationCount="555"
    SamplingPercent="100" Statistics="[INDX_UPP_4]" Table="[UPPDRAG]"
    Schema="[SVALA]" Database="[ulek-sva]" />


What is a secure strategy for managing login/passwords used in a powershell script I have several Powershell scripts that need to write data to an Oracle database.  I do not want to hard-code the login credentials.

When connecting to SQL Server I rely on windows authentication and Active Directory, but that is not an option here because the Oracle database is on a separate network (Unix).  The scripts will be executed on a SQL Server via SQL Server Agent.

I am hoping to come up with a strategy that will be secure and will minimize the work involved when it comes time to change the password.

Your best option would be to create the passwords in a file (or table) using ConvertTo-SecureString and ConcertFrom-SecureString. You will need to utilize the "-Key" or "-SecureKey" parameters to allow the service account with SQL Server Agent to read the password into your script.

The best write-up I've come across for this process is found here and here. Which the code snippets of interest from the articles:

#Encrypting password on machine 1
$File = "\\Machine1\SharedPath\Password.txt"
[Byte[]] $key = (1..16)
$Password = "P@ssword1" | ConvertTo-SecureString -AsPlainText -Force
$Password | ConvertFrom-SecureString -key $key | Out-File $File

#Accessing the password on machine 2
$File = "\\Machine1\SharedPath\Password.txt"
[Byte[]] $key = (1..16)
Get-Content $File | ConvertTo-SecureString -Key $key

The general premise being that you can use the first process to save your passwords to a file, or table. Then in your SQL Agent job read the password back in a semi-secure manner. It is not the most secure because if anyone finds the key, they can obviously read the password as well.

As well, in the end you are likely going to be passing it as plain text to the destination, unless it accepts use of a PSCredential object.


How can I get SQL to do the sensible thing without resorting to a query hint? I have two tables, Items and Grouping. Items has about 7 million rows whereas Grouping only has 300,000.

Of the two conditions on my query, the 'human obvious' thing is to evaluate G.StatusID IN (1,2) first. It seems obvious to do this first because:

only 10% of rows fulfil the criteria about StatusID (I have checked, and SQL's statistics know this)
Grouping has relatively few rows
there is an index on StatusID

Those things combined mean that if the G.StatusID IN (1,2) condition is looked at, far fewer rows from Items have to be examined.

Select
    GroupingID,
    Sum (Amount) FullAmount
From
    Grouping G
Join
    Items I
        -- With (INDEX(IX_Items_GroupingID))
        On G.GroupingID = I.GroupingID
Where
    I.ThingID Is Null
And
    G.StatusID IN (1,2)
Group By
    G.GroupingID

However SQL Server refuses to do this. It is reading the entirety of Items into memory, and suggests that I implement an index on ThingID. Implementing such an index does speed everything up, but I don't see why I should need to.

If I uncomment the index hint in the code above, SQL Server does as I want it to.

I'd prefer not to put the index hint into production. What is causing SQL Server to pick a slow plan?

(For background, around 25% of Items fulfil I.ThingID Is Null.)

Slow plan on Paste The Plan

Fast plan on Paste The Plan

Amount is a column from Items

It looks like you need to edit your index on Grouping_ID to include ThingID and Amount as per the comments, this will eliminate the Key Lookup in the second plan (76%).


1062 Duplicate entry but there are no duplicates? I keep getting this error:

  failed to INSERT: [1062] Duplicate entry 'Upping Your Type
  Game-http://jessicahische.is/talkingtype' for key 'PRIMARY'.

The problem is, is that there are no duplicate entries. Which is why I'm confused when receiving that error.

Is there any way to check using InnoDB?

I have tried exporting the table and reading the SQL code and ran a search for any duplicates but there wasn't any.

It isn't saying that there is a duplicate entry in the table already, it is saying that there is already one entry in there with that value for the primary key and it is refusing to insert a second for that reason.

I suspect if you run:

SELECT *
FROM   <table>
WHERE  <keyfield> = 'Upping Your Type Game-http://jessicahische.is/talkingtype'

or if that key is a composite

SELECT *
FROM   <table>
WHERE  <keyfield1> = 'Upping Your Type Game'
AND    <keyfield2> = 'http://jessicahische.is/talkingtype'

You will find one matching row, and that your code at the point of the error is trying to insert a second.

Dealing with duplicates on insert:

If you try INSERT a duplicate values for a primary key (or a unique index) you will always get that error. There are a couple of ways around it: check before you insert and either do an update (if something might have changed) or just don't do anything.

There is also the mysql specific ON DUPLICATE KEY UPDATE option (see https://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) if you are happy to sacrifice compatibility with other RDBMSs.

As of version 9.5 Postgres supports a similar feature with slightly different syntax (ON CONFLICT DO UPDATE/NOTHING - see https://wiki.postgresql.org/wiki/UPSERT).

MS SQL Server, Oracle and some other systems support MERGE statements as defined in the SQL:2003 standard (see https://en.wikipedia.org/wiki/Merge_(SQL)) which is intended to achieve the same functionality and more. Be careful with any of these options if cross database compatibility is (or is likely to be in future) one of your goals.


How to optimize count(*) using only foreign key index on PostgreSQL I have a web app that extensively uses data tables that connect to the database to fetch data.
The problem though, it always needs first to count the total results before it could paginate the data by means of limit and offset

Is there any type of special indexes or some configurations to always force the count to be calculated purely by index without relying on the end table? because now, it seems, it ignores the index when querying for the count.

PostgreSQL can only use an index to count the number of rows if the table has been vacuumed recently so that most table blocks are marked "all visible" in the visibility map. Otherwise it has to inspect the table to check ifbthe row is visible or not, and then an index scan is more efficient.

I think that the solution is not to count the rows. For a cheap estimate, use pg_class.reltuples. For a more in-depth treatment of the topic, read my blog.


Request shown in slow logs In my MYSQL configuration I activated logging of request not using indexes.

slow_query_log=1
slow_query_log_file = /home/mysql/sqlprive.log
long_query_time=7
log_queries_not_using_indexes =1

But in my log file I find some requests that are not slow and are using indexes:

# Query_time: 0.001160  Lock_time: 0.000188 Rows_sent: 10  Rows_examined: 20
SELECT `t`.`id` AS `t0_c0`, `t`.`nom` AS `t0_c2`, some others fields whitout indexes
FROM `my_table` `t` GROUP BY t.id ORDER BY t.name ASC
LIMIT 10 OFFSET 10;

Here is the structure of my table

CREATE TABLE IF NOT EXISTS `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  #some other fields whitout any indexes
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1060 ;

Someone has an idea why I find this kind of request in the slow log file?

The option log_queries_not_using_indexes is probably the cause.

From the MySQL Server Option

  If you are using this option with the slow query log enabled, queries
  that are expected to retrieve all rows are logged. See Section 5.2.5,
  “The Slow Query Log”. This option does not necessarily mean that no
  index is used. For example, a query that uses a full index scan uses
  an index but would be logged because the index would not limit the
  number of rows.

So, your query is a "not using index query" and a "full scan query"...

mysql> explain select * from yourTable LIMIT 10 OFFSET 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: yourTable
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 124386
        Extra:
1 row in set (0.01 sec)


Oracle: How to get list of objects accessible to the user? I want to get list of all objects which is accessible to the login user. The list should include Table, Sequence, Function, Procedure, View, Sequence, Index, Trigger etc.

I am using Oracle 11g and Oracle 12c. My requirement is that the owner of the db objects grant privileges to other user, so that user can access those objects by running select or other query. I did try earlier dba_objects but DBA does not allow to grant permission for dba_objects to the user. So I am looking for different solution.

I did try all_objects that list of all objects accessible to the user, but It lists the Table, View, Index and Sequence only.

Either I am missing something or its returns it only.

Can anybody suggest me correct way to get all accessible objects to the user?

Community wiki answer:

ALL_OBJECTS lists all the object types, at least those which you need, except triggers, for which you can query ALL_TRIGGERS.


Exporting data from Mysql into Sqlite i want to export from mysql into sqlite. currently i can export from mysql into a dump file. the only problem is importing to a sqlite db. the import feature on sqlite does not currently support the dump from mysql. is there any other way.

Yes, there is a way, it's a very cool AWK script, which just needs the MySQL dump. Find it here - https://github.com/dumblob/mysql2sqlite


MySQL connection fails with one database and not the other I have 2 databases on mysql. ge and ge_sc001.

I can access both of these through my asp.net mvc application locally.

Web.config

 <connectionStrings>
<add name="GEContext_sc001" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge_sc001" providerName="MySql.Data.MySqlClient" />
<add name="GEContext_sc002" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge" providerName="MySql.Data.MySqlClient" />
<add name="GEContext" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge" providerName="MySql.Data.MySqlClient" />

Later on when i deployed my application on the web server, and tried to access them from a remote machine. I changed server=localhost to my machine's external-facing IP address. Now I can access only one ge. when i try to access the second it gives me error.

  Access denied for user 'root'@'' to database 'ge_sc001'

Notice there is no localhost above i.e. 'root'@'localhost'

SHOW GRANTS

'GRANT ALL PRIVILEGES ON . TO \'root\'@\'localhost\' IDENTIFIED BY PASSWORD \'*xxx\' WITH GRANT OPTION' 'GRANT PROXY ON \'\'@\'\' TO \'root\'@\'localhost\' WITH GRANT OPTION'

UPDATE

I ran

GRANT ALL ON ge_sc001.* TO 'root'@'<external_ip>';

Now the error has change to

  The provider did not return a ProviderManifestToken string.

This is now happening on both of my schemas

There was an extra entry in mysql.user table with host as '' that was interfering. Deleted it manually and running FLUSH PRIVILEGES worked for me.


Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table? I understand that views are implemented using the rule system but I'm unclear if this has any advantages/disadvantages when running transactional DDL against them. Does issuing CREATE OR REPLACE VIEW ...; or DROP VIEW ...; CREATE VIEW...; in a transaction take out an ACCESS EXCLUSIVE lock similar to DDL against a table? Would all queries issued before the DDL have to complete before the DDL could execute? Would queries issued after the DDL block until the DDL is complete?

Direct answers to your three questions would be;

Yes it does. When you replace your view, the lock of view object will be AccessExclusiveLock.
Yes, even the 'select' queries that uses that specific view will wait until the 'create or replace' statement is committed.
Yes they do. After the "view changer" query is committed the queries that are waiting will start


function to receive a character input and return date format (with incorrect input) I need to write a function to receive a string character and return the date format . For example the input is 20120101 and i need this 2012-01-01. The problem is that there might be some incorrect inputs like this "2012ABCD". In that case, I want the function to return a fix date such as 2020-01-01.  What I've written so far is:
Create Function ReturnDate
(@date varchar(8))

Returns date

  as

    begin
       declare @result date

          set @result = (select convert(date , @date,111))
                if(@@ROWCOUNT>0) return @result
                 else return '2020-01-01'
       return @result
    end

This doesn't work and I just don't know how to handle the second part (when the input is incorrect).

On SQL Server 2012 and later you can use TRY_CONVERT to check to see if the input can be converted.  If it can't, a NULL value is returned so then you can then do an COALESCE to get either the converted value or the fixed date.

begin
   declare @result date
   set @result = COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01')
   return @result
end

You could also use a TRY CATCH block and return the fixed date in the CATCH block, but it's best practice to use TRY_CONVERT so that SQL Server doesn't have to handle an error as that requires more time and resources.

A function for this type of code will incur more overhead than simply using the same logic in the query, so if it is being called many times every second you might chew up significant resource by using a function for it.  I understand that this may be called from numerous pieces of code so there is a desire to make it a function in case the default date needs to be changed--then it's no compiled code changes and just update this function.

If this code is going to be run a lot, you should consider other options that will provide better performance than a user-defined function.  Please see  Solomon's answer  for an overview of your options and further explanation of why you might choose one over the other.

For example, the following shows the same logic implemented as an inline table-valued function, which needs to be used with CROSS APPLY if not supplied with a static value, but performs much better than a scalar UDF:

USE [tempdb];

GO
CREATE
OR ALTER -- comment out if using pre-SQL Server 2016 SP1
FUNCTION dbo.ReturnDate (@Date VARCHAR(8))
RETURNS TABLE
AS RETURN
  SELECT ISNULL(TRY_CONVERT(DATE, @Date, 111), '2020-01-01') AS [TheDate];
GO

SELECT *
FROM   (VALUES (1, '20120101'), (2, '2012ABCD')) tab(ID, Input)
CROSS APPLY dbo.ReturnDate(tab.[Input]) dt
/*
ID    Input       TheDate
1     20120101    2012-01-01
2     2012ABCD    2020-01-01
*/


What's the best method to refresh only a few tables within a test database from production? I have a very large production database and a very large test environment database in SQL Server 2008R2. Both databases have similar table structure but different users/logins/permissions/roles.

I need to refresh only a few tables in the test database periodically from production, about once a month.

The current way I am planning on doing this is

Use the BCP utility to take an export of the tables I need from Production.
Copy the bcp export file onto test server
Disable indexes and constraints on all tables I am refreshing in Test
Truncate the Test database tables
Load Data back into Test database tables using BCP.
rebuild indexes and re-enable constraints in Test

This all seems a little too complicated for such a small task. It also seems like it would generate a lot of redo (in the t-log) Is there a better way to do this?

Another way I thought of doing this is to restore a backup from Production onto the test environment--but the issue I have is that a full backup would be quite large and I don't need all tables to be refreshed, only a few--and also the users and security in the production database is different from test. That would be overwritten by the security settings in the production database if I restore the whole database.


Accept use in sqlplus I want to ask in SQL script for a start and finish date with accept but I get an error.

accept v_FechaInicio date format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de inicio ('DD/MM/YYYY-HH24:MI:SS'): '
accept v_FechaFin date    format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de fin    ('DD/MM/YYYY-HH24:MI:SS'):'

SQL> SQL> SQL> SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'): '
SQL> SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'):'
SQL> accept v_FechaInicio date format 'DD/MM/YYYY-HH24:MI:SS' prompt 'Fecha de inicio ('DD/MM/YYYY-HH24:MI:SS'): '
SP2-0003: Ill-formed ACCEPT command starting as DD/MM/YYYY-HH24:MI:SS'): '

What is the problem?

It looks like your ACCEPT prompt includes quotation marks. But quotation marks are also used to delimit the prompt string, so if you want to include those characters into the prompt, you need to double them:

… prompt 'Fecha de inicio (''DD/MM/YYYY-HH24:MI:SS''): '
… prompt 'Fecha de fin    (''DD/MM/YYYY-HH24:MI:SS''):'

Although I think you could simply omit them, since this is just a prompt:

… prompt 'Fecha de inicio (DD/MM/YYYY-HH24:MI:SS): '
… prompt 'Fecha de fin    (DD/MM/YYYY-HH24:MI:SS): '