What is the correct way to specify a record as the *current* record? The two options I have thought of is either to add a column boolean column called Current onto the table; but I'm sure that would violate some level of normalisation due to more than one record being able to be set as current.

Alternatively having another table that specifies the ID of the current record, but this also doesn't seem like a great way of doing it.

Is there a better way or convention for achieving this kind of thing?

Edit:
I probably should of said there can only be one current record, so when it is changed the previously current record needs it's 'current' status removed.  The system isn't complicated enough to need to worry about concurrency or anything like that.  The main reason for the question is that I wanted to do things in the correct/conventional way if there was one.

I believe the best way is to have a settings table that will hold the ID of the current record.

I've seen several approaches to this:

a boolean (if the DBMS of choice supports that) or character (e.g. 'Y','N') indicator column;
a more general "status" column, containing values like 'ACTIVE','HISTORY','EXPIRED' etc.
a pair of timestamp columns (e.g. START_DT, END_DT), indicating the range of dates when a particular record is (was) active. The current record would have the END_DT value in the future, like '9999-12-31', which makes querying the current record simple: ...where current_date between START_DT and END_DT
a history table where all non-current records would be moved as soon as they become non-current. If you're only interested in current records, you would query the current table, otherwise you'd query a view that UNION ALLs both current and history tables.

In the first three cases you would need to ensure consistency via some sort of a constraint, if your application is unable to do that.

Which approach you choose depends on what constitutes the current record in your particular case.


How can I display twice each stored row without using UNION ALL or a temporary table? The relevant table, named emp, holds the following data:

CREATE TEMPORARY TABLE emp AS
SELECT * FROM ( VALUES (1,'A'), (2,'B'), (3,'C') );

 ID  Name
 --  ----
 1    A
 2    B
 3    C

And the output or result-set of the data manipulation operation should be as shown bellow:

 ID  Name
 --  ----
 1    A
 1    A
 2    B
 2    B
 3    C
 3    C

Requirements

The output must be obtained complying with the following conditions:

No use of the UNION ALL operator in association with the employed SELECT statement(s)
No use of temporary table(s)
No use of an UPDATE operation to the existing table

Note: This scenario was brought up to me by an interviewer.

SELECT ta.id, ta.name
FROM emp ta
CROSS JOIN ( VALUES (1), (2) ) tb (id) ;


SSIS Ensure a sequence container is not marked as failed if a specific task fails Consider the following scenario for an SSIS package:

A Sequence Container contains two jobs for loading data from an Excel file:

Job A: Attempts to load data from an Excel file following a specific file definition.
Job B: In case JOB A fails, Job B will be executed instead.

Outside the container, a final task is executed to reset data.

The issue I have, however, is that if JOB A fails, even though JOB B takes over and manages to process the file correctly, the container is marked as a failure.

Although I can circumvent this issue with the use of some variables and "Precedent Constraint" (this can be seen in the second screenshot above), I wonder if there is a more elegant way, in which I simply ensure that the Sequence Container is not marked as failed is the first task of loading data fails.

Now, the first thing that comes to mind for me is the use of MaximumErrorCount property on the container. However I have other tasks within the container and if they fail it could mess with the logic.

Perhaps nesting yet another container for just JOBS A and B within the existing container would be the way forward?

But really (I'm a hopeless idealist) what I was hoping to find is some sort of property for JOB A that says if job A fails, don't mark the container as a failure in the simplest way possible. Thoughts?

Have you looked at the Propagate variable for the OnError Event Handler of the task within the sequence?

Check this link for info. Basically, you disable propagation of the OnError event so the ancestor tasks (or sequence in this case) don't receive the error.


Replicate data between MySQL servers with a different structure As part of a system upgrade I need to sync a database between 2 different servers (master-master). The catch is that the structure is slightly different between the 2.

Most of the differences are:-

Columns that exist in 1 but not the other and need populating based on other fields/tables
Type changes where some integer fields are now enums with text values that will need mapping from a static list
Multiple tables have been merged into 1

So far I've been thinking about setting triggers up which would catch all INSERT/UPDATE/DELETE statements and insert them into a "queue" table. A PHP script would then convert the data and write the changes to the other server.

All queries in the system are executed through a central database object so I could potentially add some code to parse each query, create a new query that works with the other server and execute both queries together.

There's also the possibility to only use the new database and use query parsing code to re-format the data on each read/write to make it compatible with the old system.

Data has to be copied both ways (master-master) and it has to be as close to realtime as possible. Has anyone got any better suggestions or advice on how to do this?

Not possible.

Master-Master allows writing to both, and, hence, provides no way to make changes during the replication, at least not bi-directionally.

Master-Slave does allow limited flexibility, such as different engine, different indexes, and, in a few cases, different datatypes.  However, you could lose data.  And it could fail depending on whether you are using binlog_format = ROW versus STATEMENT.  ROW is less forgiving.

Would you like to explain the need you have?  Perhaps we can devise a workaround.

OK, maybe it is possible with Triggers, since they don't have to be the same on both Master and Slave.  (M-M is M-S in both directions.)  So, if you can devise a pair of triggers that completely modify the table(s) at the time of writes, and if selects don't stumble over extra/missing columns/datatypes/tables, then maybe you could do what you want.


Changing hostname IP address in mongodb configuration My mongodb sharded cluster is set with 1-member replica set for config server with 6 shards, 1 mongos. All these components are hosted on a single server with mongos on 0.0.0.0:27017 and rest on 127.0.0.1. All addresses have been configured through hostnames in /etc/hosts i.e 127.0.0.1 as configrs , 0.0.0.0 as mongos and bindIP in mongo config holds these hostnames and not IP addresses.

I want to host a mongos on separate server. Since config server was hosted on 127.0.0.1:27018, to make it reachable I stopped the config mongo, updated /etc/hosts contents from 127.0.0.1 configrs to 0.0.0.0 configrs and restarted the config mongo. But now, rs.status() returns

  "state" : 10,
    "stateStr" : "REMOVED",
    "uptime" : 12,
    "optime" : {
        "ts" : Timestamp(1521449062, 2),
        "t" : NumberLong(3)
    },
    "optimeDate" : ISODate("2018-03-19T08:44:22Z"),
    "ok" : 0,
    "errmsg" : "Our replica set config is invalid or we are not a member of it",
    "code" : 93,
    "codeName" : "InvalidReplicaSetConfig"

How do I move my config server from 127.0.0.1 to 0.0.0.0? I went through hostname change documentation for mongo, but it lists down steps to change hostnames whereas I am not changing hostname.

Address 0.0.0.0 (itself) don't exists.

It is used at config files as value what means "listen all network devices".

If you want to have mongos at different server, you need to configure your cluster to use address what is connected to network device what point outside world (maybe eth0). All components of that cluster (config server, all shards) must be using that "external" IP-address what is reachable from that machine where you are going to put that mongos.

How to change all components to use external IP address? It is possible to do "under the hood", but to you it would be easier to export all data to HD, rebuild your cluster with right configuration and then import data back to cluster.


Is this a new common pattern in Oracle Where exists ( Select NULL FROM...)? Years ago, it was common to write

where exists (Select * from some_table where some_condition)

Last year I noticed that many t-sql scripts switched to using the number 1 instead of the star

where exists (Select 1 from some_table where some_condition)

just on SO I saw this  Oracle example

WHERE EXISTS (SELECT NULL FROM ...

Is this a common  pattern with Oracle?
And which are the performance arguments to use something like this.

Short answer: I would hope not!

Even the SELECT 1 idiom may have outlived its usefulness, as database engines have advanced enough to recognize that you don't really want the rows, you just want to know if the rows exist. I've looked at execution plans in SQL 2005, and have generally seen these re-written as joins, even where SELECT * is used.


How to log MySQL downtime If I wanted to see the date, time and length of downtime on a MySQL instance/database, how would I go about doing that?

For example, if I wanted to see if the MySQL database went down at all last week, I could see when it when down and when it came back up – therefore, with a little subtraction I could figure out the downtime.

I am not particular about the method that I get this information, that is just what I thought would be the easiest way to do it. However, I am new to MySQL and am not really sure.

I have made a script that digs through the error log to find the shutdown and startup times for a MySQL server:

######################
##BY: Michael       ##
##Created: 4-1-2016 ##
##Version: 1.01     ##
######################

#####################
######Variables######
#############################################################

#LogFile is where the ErrorLog dumps its filtered error log data
LogFile=/sw/pkg/mysql/admin/logs/shutdowns.log
#Error log holds the raw information from the MySQL Error log filtered by grep.
ErrorLog='grep 'Starting' /var/lib/mysql/'`hostname`'.err'
#OutputFile holds a comma delimited set of values of hostname,shutdown date, shutdown time, startup time
OutputFile=/sw/pkg/mysql/admin/logs/shutdowns_`date +"%Y-%m-%d"`.csv

#############################################################
######Variables######
#####################

##################
######Header######
#############################################################

#Write (Not append) to $LogFile
$ErrorLog > $LogFile
#Writes (Not append) the table headers
echo "hostname,Shutdown_date,Shutdown_time,Startup_time" > $OutputFile

#############################################################
######Header######
##################

################
######Body######
#############################################################

#Do not change variables!
q=0
intholder=-1

#Go line by line in $LogFile. '$i' is the current line (string).
while read i; do
        ((q++))

        #If the line ($i) is a line containing shut-down information then gathers in shut-down information
        if [[ $i =~ "Starting shutdown..." ]]
        then
                shutdowndate=`echo "$i" | grep -Eo '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}'`
                shutdowntime=`echo "$i" | grep -Eo '[[:digit:]]{2}:[[:digit:]]{2}:[[:digit:]]{2}'`
                ((intholder=$q + 1))
        fi

        #If the line ($i) is a line containing start-up information then gather that information
        if [[ "$intholder" == "$q" ]]
        then
                startuptime=`echo "$i" | grep -Eo '[[:digit:]]{2}:[[:digit:]]{2}:[[:digit:]]{2}'`
                echo "MySQL instance:`hostname` went down on $shutdowndate at $shutdowntime and started at $startuptime"

                echo "`hostname`,$shutdowndate,$shutdowntime,$startuptime" >> $OutputFile
        fi

#End loop
done <$LogFile

#############################################################
######Body######
################

##################
######Footer######
#############################################################
#       Nada
#############################################################
######Footer######
##################


Find order that has deliveries in multiple weeks I thought I had a simple run of the mill problem, but I can't manage to figure out how to slice it.  Assume I have a table of Deliveries that has the OrderNumber, the DeliveryNumber and the DeliveryDate.  The question I want to ask is "Which OrderNumbers have DeliverDates that all aren't in the same week"

Here is a test script.

DECLARE @DeliveryTable Table
(
    OrderNumber INT,
    DeliveryNumber INT,
    DeliveryDate Date
)
;

INSERT INTO @DeliveryTable
(OrderNumber, DeliveryNumber, DeliveryDate)
VALUES
(1,300, '2020-04-27'),
(1,301, '2020-04-28'),
(1,302, '2020-04-30'),
(4,730, '2020-04-26'),
(4,731, '2020-04-28'),
(4,732, '2020-05-05')
;

My query would return OrderNumber={4} because the '2020-05-05' is not in the same week as '2020-04-28' and '2020-04-26'.  OrderNumber 1 would not be returned because all the deliveries are in the same week.

I've thought about using a LEAD/LAG function, but there can be an arbitrary number of Deliveries so I wouldn't know how far to LEAD/LAG.  I was thinking about using some type of join, but I don't know what I would join on other than ( ..DATEPART(WK, first.DeliveryDate) <> DATEPART(WK, second.DeliveryDate)..) but that seems hamfisted to do over 1.4 million rows.

Try following query

SELECT OrderNumber
FROM @DeliveryTable
GROUP BY OrderNumber
HAVING DATEPART(WEEK, MIN(DeliveryDate)) < DATEPART(WEEK, max(DeliveryDate))

Calculating date-diff can lead to incorrect results because if we compare difference of Sunday and next Monday it will return 1, but these days are in different weeks.


How does Database Snapshots Work (MySQL)? I have a MySQL database hosted on an AWS RDS instance. The database has many tables and I observed the following:

I picked a table with about 1-2 million records that is indexed on a created_at datetime column. When I execute a SQL query something along the lines of picking records between a specific start date and end date on this column, the query takes a long time to complete(about 10 mins).

However, when I take/restore an AWS RDS instance from a snapshot, it hardly takes 5-10 mins. I'm curious how taking/restoring from snapshots are so fast? Does is not have to read through the whole data in the database to take/restore snapshots?

Forgive me if my question is naive. I'm a newbie. Thanks for the input :)

In this specific case, it's because the sequential restore of a blob of data is faster than the traversal in order that MySQL does it in, and because MySQL could be CPU bottlenecked during some parts of the operation. Additionally, snapshot restores in AWS may not be constrained by the storage IOPS you are paying for.

In general, restoring a snapshot can be incredibly quick - with ZFS a rollback to a snapshot doesn't require restoring any data at all, it involves only marking newer data blocks as discarded. This takes milliseconds to seconds, rather than minutes.


Multiple Primary-Foreign Key connections between Tables Redundant? I have two tables, Reviews and Critic

I'm pretty new to RDBMS and I was wondering if the connection between Review.rID and Critic.Review is redundant?
My reasoning is that a single critic can have many reviews but each review is unique so I need a way to enforce the uniqueness of the Reviews. I did this via Review.rID which is unique. I also need to express that multiple critics can leave multiple reviews.
However, I am already connecting the critic to the review by including a criticID within Review, so is it necessary for me to also connect a reviewID to a critic within Critic?

A foreign key can only refer to a primary or unique key of another table.  The 1-M relation you drew between Critic.Review and Review.rID is impossible.


On EC2 Why do I need individual EBS volumes for journal, log, and data? According to this MongoDB tutorial which explains how to manually deploy MongoDB on EC2, one of the steps states that you should have:

"Individual PIOPS EBS volumes for data (1000 IOPS), journal (250 IOPS), and log (100 IOPS)."

Why do I need individual EBS volumes for journal, log, and data?
Can I just combine these into one EBS volume?

You can combine it on 1 disk, if you wish. Not obligated to split.

The reason is to optimize the disk for the purpose. Journal is a capped collection that just writes in sequence. So less IOPS needed. And logs are logs, just adding information.  And then Data, well, reading jumps around a lot, and writing sometimes also, filling up gaps that were freed. And while journal and log are written away on other disks, the data disk doesn't lose time on that. Every little bit can help on intensive systems. The next step then is Replication and Sharding to spread the load.

On the https://university.mongodb.com, you can get more information about this if you are interested. Following M202 MongoDB Advanced Deployment and Operations now, what offers some specific information to optimize.


Production Database went into restoring mode Yesterday one of my production database went into restoring mode,but fortunately i recovered it using below query

USE MASTER
EXEC SP_CONFIGURE 'Allow updates',1
RECONFIGURE WITH OVERRIDE
GO

EXEC SP_RESETSTATUS 'DatabaseNAME'
DBCC DBRECOVER('DatabaseName')
EXEC SP_CONFIGURE 'Allow updates',0
GO

RECONFIGURE WITH OVERRIDE
GO

It brought my database online, but I want to know why it went in restoring mode, I read SQL error log but I didn't get much information about it. Is the query I used to bring the database online correct? It is used to recover the database from suspect mode.

(Restoring...) is typically seen under two circumstances.

You're restoring a database, and have specified the WITH NORECOVERY option to allow, typically, log backups to be restored.  The last restore operation would not use the WITH NORECOVERY option, thereby directing SQL Server to run the recovery process on the database, and allow client access to it.
You've taken a backup of the database logfile, and have specified the WITH NORECOVERY option to ensure you have backed up the tail of the log.  This option might be used to ensure no one can access the database in anticipation of migrating the database to a different server.  You may inadvertently use this option when running a transaction log backup via the GUI:

While a database is being restored, it will show up as RESTORING.

You can test the tail log recovery scenario mentioned above in option 2 using this MCVE:

First, we create a database, and set it to FULL recovery:

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'RestoringModeTest')
BEGIN
    ALTER DATABASE RestoringModeTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RestoringModeTest;
END
GO
CREATE DATABASE RestoringModeTest;
ALTER DATABASE RestoringModeTest SET RECOVERY FULL;
BACKUP DATABASE RestoringModeTest TO DISK = 'NUL:';
BACKUP LOG RestoringModeTest TO DISK = 'NUL:';
GO

This shows the state of the database:

SELECT d.name
    , d.state_desc
FROM sys.databases d
WHERE d.name = 'RestoringModeTest';

╔═══════════════════╦════════════╗
║       name        ║ state_desc ║
╠═══════════════════╬════════════╣
║ RestoringModeTest ║ ONLINE     ║
╚═══════════════════╩════════════╝

Here we backup the tail of the log.  (NOTE, this is NOT a real backup, since the target is the NUL: device.  DO NOT USE THIS IN PRODUCTION!)

BACKUP LOG RestoringModeTest TO DISK = 'NUL:' WITH NORECOVERY;

Now, when we look at the state of the database:

SELECT d.name
    , d.state_desc
FROM sys.databases d
WHERE d.name = 'RestoringModeTest';

╔═══════════════════╦════════════╗
║       name        ║ state_desc ║
╠═══════════════════╬════════════╣
║ RestoringModeTest ║ RESTORING  ║
╚═══════════════════╩════════════╝

This statement will cause SQL Server to run the recovery process on the database to return it to the ONLINE state:

RESTORE DATABASE RestoringModeTest WITH RECOVERY;


how to search this field in Postgres for an array of characters I have a users table which is an id, name, and tags. I'd like to search tags for a specific piece of text. How would I do do this?

id                      | integer                     | not null default nextval('users_id_seq'::regclass)
name                    | character varying           |
tags                    | character varying[]         | default '{}'::character varying[]

How would I search for a tag like "skiing"?

select *
from the_table
where 'skiing' = any (tags);

Online example: http://rextester.com/BDF4617


curious case of two log files in sql database I was doing a POC(proof of concept) for which I created two log files in a test database. I created a table and inserted 10000 rows. I observed that both my log files were getting used.
I know that logs are written serially, and in my case both log files have space on disk with autogrowth enabled, then why my second log file was getting used? Why not use a single log file and move on to second log file when space is not available. Isn't it supposed to be like this.?

Standard warning about not using multiple log files etc etc.

You have two log files and are wondering why the first one didn't grow to fill all of the available disk space (or limit on the file size) before moving on to the second file. Correct?

A little bit of pre-information.  Log files are broken up into multiple virtual log files (VLFs).  If you search for VLF you can find a lot of information on them and when you have too many or too few, but that's a different subject.

If it helps you can think of the multiple log files as a single big circle.  The log information will be written round and round in that circle.  As each VLF is filled up the next is checked until it hits a VLF that is already in use.  Only at that point will there be an autogrowth.  I believe the currently used file will be the one autogrown.  If it can't be (file size limit or out of disk space) then the next file is checked and so on.  Only if none of the files can be grown will you get an out of space error.


Error in update. I did not find the error. Why is max(C) not found? (Translated from Portuguese with Google Translate)

I created a table and made an update that should update column B equally to the percentage that C represents in relation to the maximum value of column C.

create table z(
a serial primary key ,
b numeric(7,2),
c numeric(7,2)
);

insert into z values(default, 0,20);
insert into z values(default, 0,10);
insert into z values(default, 0,40);
insert into z values(default, 0,80);

But it shows an error while executing.

update z set b = c / max(c) * 100;

  aggregate functions are not allowed in UPDATE

The expected result is:

a   b      c
1   25     20
2   12.5   10
3   50     40
4   100    80

You just need a select within your update:

update z
set b = c / (SELECT max(c) FROM z) * 100;

Check it at db<>fiddle here

max(c) without more context is not part of the SQL standard. However, you can always use a SELECT returning a single row with a single column (of the appropriate type) where a value is expected.

That's what you'll get:

SELECT
    *
FROM
    z;

  a |      b |     c
 -: | -----: | ----:
  1 |  25.00 | 20.00
  2 |  12.50 | 10.00
  3 |  50.00 | 40.00
  4 | 100.00 | 80.00

And this works not only in PostgreSQL. See SQL Server, or MariaDB, or Oracle, or SQLite, or DB2


How can i merge 3 sql select queries in single query? I am using this query

SELECT
  (SELECT tr.object_id, p.post_title, p.comment_count
   FROM wpug_term_relationships AS tr
   LEFT JOIN wpug_posts AS p ON p.post_parent = tr.object_id
   WHERE tr.term_taxonomy_id =122
   ORDER BY tr.object_id DESC
  ) AS Column1,

  (SELECT tr.object_id, p.guid, p.post_type
  FROM wpug_term_relationships AS tr
  LEFT JOIN wpug_posts AS p ON p.post_parent = tr.object_id
  WHERE tr.term_taxonomy_id =122 AND p.post_type='attachment'
  ORDER BY tr.object_id DESC
  ) AS Column2,

  (SELECT tr.object_id, p.meta_value, p.meta_key
   FROM wpug_term_relationships AS tr
   LEFT JOIN wpug_postmeta AS p ON p.post_id = tr.object_id
   WHERE tr.term_taxonomy_id =122 AND p.meta_key IN ('rehub_views', 'rehub_offer_product_price')
   ORDER BY tr.object_id DESC
  ) AS Column3

If you move part of the WHERE statements into the join you can accomplish the same thing:

SELECT tr.object_id,
    p.post_title, p.comment_count,
    p2.guid, p2.post_type,
    pm.meta_value, pm.meta_key
FROM wpug_term_relationships AS tr
    LEFT JOIN wpug_posts AS p ON p.post_parent = tr.object_id
    LEFT JOIN wpug_posts AS p2 ON p2.post_parent = tr.object_id
        AND pm.post_type='attachment'
    LEFT JOIN wpug_postmeta AS pm ON pm.post_id = tr.object_id
        AND pm.meta_key IN ('rehub_views', 'rehub_offer_product_price')
WHERE tr.term_taxonomy_id=122
ORDER BY tr.object_id DESC

I noticed that on the first join to wpug_posts you don't filter post_type but on the second one you do.  I assume this means it will return multiple rows.  Do you want the first one to return post_type='attachment' as well?  If not you'll need to add AND p.post_type<>'attachment' to the first join


How can I query for terms like "@foo" with Postgres full text search? I would like to be able to query for documents containing @foo without matching the naked word foo. I'm using the simple dictionary but I notice @ characters are stripped:

SELECT to_tsquery('simple', '@foo');  -- produces 'foo'

Is there a way to force Postgres to treat the @ as significant?

The problem is the default text parser, in that it splits out the '@' symbol

You can see it here:

select
  ts_lexize('simple', '@foo'), -- {@foo}
  ts_parse('default', '@foo'); -- { (12,@), (1,foo) }

lexize is fine and keeps the @, but the parser splits it out

You'd need to write your own parser or change the source, which isn't fun if you don't know C!

One work-around would be to change @foo to something like ATSYMBOLfoo using some wrapper function for to_tsvector and plainto_tsquery


ibdata1 grows big again after using innodb_file_per_table and having shrunk it After having set innodb_file_per_table=1, dumping and dropping all db's, deleting ibdata1, and restarting mysql ... My ibdata1 file is small at last...

However, directly after reading in my dump, and seeing that all tables now all have their own big ibd files (approx 15GB), but still the ibdata1 is over 2.6 GB again...

The way I dump my data is with:

mysqldump --tab="D:\Mysql Databases\dumps\dump\%%a" --opt --single-transaction --no-autocommit -u root -p

This dumps to .sql (DDL / table structures) and .txt (actual data) files. These files I can use to recover with with:

create database myDb;
use myDb;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SOURCE *.sql; -- for each table
COMMIT;
SET autocommit=0;
LOAD DATA INFILE '*.txt'; -- for each table
COMMIT;
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;

Why is the ibdata1 file growing again after enabling innodb_file_per_table and having succesfully shrunk ibdata1? And is there a way now to reclaim again this data, seen as all ibd data is already present per table?

Can I for example copy the small ibdata1 after recreating the database with the *.sql files, and placing back that ibdata1 file after reading in the data from the *.txt files?

Here are the non-default items in my.ini:

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=151
tmp_table_size=256M
thread_cache_size=20
myisam_max_sort_file_size=1G
myisam_sort_buffer_size=32M
key_buffer_size=64M
innodb_log_buffer_size=8M
innodb_buffer_pool_size=6G
innodb_log_file_size=1G
innodb_thread_concurrency=16
innodb_open_files=1000
join_buffer_size=2M
max_allowed_packet=16M
group_concat_max_len = 10M
bulk_insert_buffer_size=512M
collation-server = utf8_unicode_ci

Ibdata1 file holds a lot of information and allocates space for different innodb functionality (double write buffer, undo logs, tablespace header, etc.). Therefore even with innodb_file_per_table you cannot drop/replace the ibdata1 file.

One common reason why ibdata can grow is because it has the undo space. If you have long running transactions the previous version of rows can pile up in the undo log easily. More about this can be read on person's blog:
https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

In MySQL 5.7 there are a lot of improvements around undo tablespace management including truncation and separate files to hold this.


Difference between master site and materialized view site in oracle I'm learning the replication concept in oracle database. I have a confusion of these two terms - master site and materialized view site. Please help on this...

Let's say I have two databases, DB1 and DB2. I want to replicate data from DB1 to DB2. I created materialized views on DB2 and these materialized views gets data (periodically) from tables stored on DB1.

So which is master site here?
The database from which replicated sites(DB2 which has materialized views) gets data. In other words the database which stores master tables , which is DB1 in our case.

then materialized view site?
The database which stores the materialized views, which is DB2.

Documentation:
About Master Sites, Master Tables, and Materialized View Sites


How to get goup concat of count value using sub query I'm trying to get group concat  of count value
here table below I want this structure:

| y    | m          | monthName                               | RowCount  | model |
| 2019 | 2,6,8,9,11 | February,June,August,September,November | 2,1,1,1,1 | Magnum |
| 2019 | 5          | May                                     | 2         | Stella |

But I got this structure :

| y    | m          | monthName                               | RowCount  | model |
| 2019 | 2,6,8,9,11 | February,June,August,September,November | 6         | Magnum |
| 2019 | 5          | May                                     | 2         | Stella |

Here my Query :

    set @p_dealer = 0;
    set @p_employee = 0;
    select YEAR(cast(c.entryDate as date)) AS y,
        group_concat(distinct MONTH(cast(c.entryDate as date))) AS m,
        group_concat(distinct MONTHNAME(cast(c.entryDate as date))) as monthName,
        COUNT(1) as RowCount,
        (select st.sModel from tbl_stock st where st.sId=c.sId limit 1) as model
        from tbl_customer c
        where YEAR(cast(c.entryDate as date))=2019 -- YEAR(cast(now() as date))
        and ifnull((case @p_dealer when 0 then @p_dealer else c.did end),0)=@p_dealer
        and ifnull((case @p_employee when 0 then @p_employee else c.eid end),0)=@p_employee
        GROUP BY model;

My table information is:
stock table (tbl_stock)

and customer table (tbl_customer)

If you use

GROUP BY model;

the result will be one line per each model.

You shall make a sub query (eliminating the group concat of month and monthname in your query) with this

GROUP by left(entrydate, 7), model;

and then group concat the month, monthname and count columns in another select.


Poor usage of timestamp index with a small table (about 2 M of rows, 5 classic columns, id, description, title, created_at, coordinates (point)) doing a WHERE created_at BETWEEN ... AND ... ORDER BY created_at LIMIT 50 is very very slow (about 15 seconds)

created_at is timestamp with index.

But when I use EXPLAIN, the column filtered shows 10, which is bad.
What could be the cause of this 10 ?

Query is simple, table is innodb, no partition

SELECT * FROM posts
WHERE created_at BETWEEN '2016-10-28 18:25:00'
                     AND '2016-11-02 18:25:00'
ORDER BY created_at DESC
LIMIT 50;

Explain plan

1   SIMPLE  posts   NULL    range   created_at  created_at  5   NULL    5317    10.00   Using index condition; Using where

Mysql 5.7 clustered version

(Added from comments)

CREATE TABLE posts (
    id int(11) unsigned NOT NULL,
    title varchar(120) NOT NULL DEFAULT '',
    description text NOT NULL,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status tinyint(4) NOT NULL DEFAULT '1',
    context tinyint(4) NOT NULL DEFAULT '0',
    coordinates point DEFAULT NULL,
    PRIMARY KEY (id),
    KEY created_at (created_at),
    KEY status (status),
    KEY context (context)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The EXPLAIN is about as good as it will get.  "Range" is good for the WHERE.  The ORDER BY, though not mentioned, should be handled as well.  And it should stop at 50 (again, no clue in EXPLAIN).

10.00 percent of the table is an estimate based on virtually no information; ignore it.

"5.7 clustered" is a contradiction.  Do you mean "NDB Cluster"?  Or a Galera-based cluster?

Indexing a flag (status) is usually useless.

More on indexing .

Back to the question of 15 seconds.  I agree that the time is unreasonably large.  Here are some partial explanations...

Something else was busy in the system.
The bulky TEXT column was store off-record, necessitating an extra disk hit.  Granted, 50 disk hits should take less than a second.
The Posts may be scattered around; that is, not stored consecutively.  Again, maybe 50 disk hits and another fraction of a second.

I am surprised that it took more than one second, even after allowing for uncached blocks.

Run the query a second time (to counteract the caching); how long does it take?

Another thing to check -- how big is innodb_buffer_pool_size and how big is RAM?  A tiny VM could be part of the problem.  The buffer_pool being so big that swapping occurs could actually explain 15 seconds.


When is the best time to use RMAN vs when to use Data Pump? And also... if I backup a database with RMAN, is it not possible to use impdp to restore small parts of that database?

The rman utility is for making a physical backup of the database that can be used to restore the database back to a consistent image for the entire database generally at the point of failure when combined with the archived redo log backups.  Datapump is for making logical point in time copies of data.  Forward recovery of the data to a point in time more current than the time of the export is not provided.  In an export row data may be at different points in time for different tables resulting in some minor data inconsistencies upon import completion.  Export may be good enough to use as a backup for test databases.  Both rman backups and export should probably be ran on production databases.


Move ibdata1, set innodb_data_file_path I am in a bit of an awkward situation right now. I have a database with a few large InnoDB tables, one of which is about 145 GiB. I have been running a lot of maintenance on this table in particular, not realizing that a lot of that compounded in the ibdata1 file. Now I am in a situation where the ibdata1 file has grown so much that it has used up all of my disk space on my dedicated SQL SSD drive. I have read up on the matter and I know that I have to reload the database into the sql server for it to shrink the ibdata1 file. However, the mysqldump I took of my database is suspiciously small (it is about 2/3rds of my database size and is uncompressed), so what I want to do is load the dump file in a test database to check if there are any issues with it. The issue is, as I do not have any disk space left on my drive (this is a vicious cycle). So, what I would like to do is to move the ibdata1 file to my main HDD, which has plenty of space, and then add a line in the mysql configuration file telling the new location of the ibdata1 file, so I can free up enough space to load up a dummy database. Can I do this? And if so, how do I do this?

Mysql's location; /mysql,
ibdata1 location; /tmpsql/ibdata1 (I can move this to let's say /var/lib/mysql or so if that would be better).

I already set innodb_file_per_table in the configuration file before it used up all my disk space. I have moved the ib_logfile* files as well to the same directory as ibdata1. The ibdata1 file was still used and extended regardless of this setting.

Please let me know if you need any additional information. Thanks in advance!

You should be very happy the mysqldump is two-thirds the size of the data. Why?

You should want to move data and indexes without copying unused space at the same time. What aspects would you have to consider by simply importing a mysqldump?

Aspect #1

InnoDB indexes tend to get very bloaty because Secondary Indexes have keys into the Clustered Index. This always results in a double index lookup. This can be overlooked with large, write-heavy datasets.

Aspect #2

InnoDB tablespaces tend to get very bloaty because of MVCC being created and discarded without an automatic reclaiming of disk space:

With innodb_file_per_table disabled, the system tablespace file ibdata1 would steadily grow with 0% chance of reclaiming of disk space. You would have to convert InnoDB storage engine to use innodb_file_per_table.
With innodb_file_per_table enabled, you could go to the individual InnoDB and run     of the the following to physically shrink the InnoDB table mydb.mytable:

OPTIMIZE TABLE mydb.mytable;
ALTER TABLE mydb.mytable ENGINE=InnoDB;

In light of this, you should keep in mind that a mysqldump is a logical representation of the data and indexes. There is no wasted space to be concerned with. You should not have to scp or rsync bloated InnoDB data or logs. Importing a mysqldump into a fresh installation of MySQL should be what you what want.

I have written about this many times:

MySql - Clean ibdata1 (April 19, 2012)
How do you remove fragmentation from InnoDB tables? (April 11, 2012)
Is innodb_file_per_table advisable? (April 01, 2012)
How to Clean a mysql innodb storage engine (October 29, 2010)

Once you have loaded the test database with the mysqldump, you should run CHECKSUM TABLE on random tables of various sizes. If all tables have the same checksum value, you could certify as good. Make a disklevel backup of the data on the prod server as well.

You can then do this in production

shutdown mysql
hose everything (including ibdata1, ib_logfile0, ib_logfile1) in /var/lib/mysql except for /var/lib/mysql/mysql
Set innodb_data_file_path=ibdata1:10M:autoextend in /etc/my.cnf
restart mysql (this recreates ibdata1, ib_logfile0, ib_logfile1)
reload mysqldump

Your InnoDB architecture and data will then be lean and mean.


Correlating hostname/IP to user I work in an environment where logins are shared among applications, and occasionally developers - in a situation like this, I am having trouble determining who is connected to the database, or who is running particular queries.

The most specific information I can find seems to be host_names, or an IP address.

I am wondering, if there is a way to correlate this information with a user?

If you know which workstation belongs to which human, you can create a logon trigger that logs all instances of the specific shared user(s) over a period of time, and you can review the table to see which workstations are logging in. I would put the logging table in a stable place like msdb or a utility database if you have one.

USE msdb;
GO
CREATE TABLE dbo.ThatLoginLog
(
  EventDate datetime2 NOT NULL DEFAULT sysdatetime(),
  HostName sysname,
  IPAddress varchar(15)
);

Now, the logon trigger:

USE master;
GO
CREATE TRIGGER LogTheLogin
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
  IF ORIGINAL_LOGIN() = N'that login name'
  BEGIN
    INSERT msdb.dbo.ThatLoginLog(HostName, IPAddress)
      SELECT HOST_NAME(), client_net_address
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;
  END
GO

Then, periodically, or as soon as you think you've given enough time in your business cycle for everyone to have logged in at least once:

SELECT HostName, IPAddress, COUNT(*), MAX(EventDate)
  FROM msdb.dbo.ThatLoginLog
  GROUP BY HostName, IPAddress;


How can I select data from two differents servers? I have two databases: data1 and data2 in two different servers (server1 and server2).

I am trying to join two tables of my two databases in MySQL like this:

SELECT left(a.code,9) as fourn,
       SUM(b.price) as total
FROM data1.product as a
JOIN data2.my_items as b ON a.fourn = b.left(p_code)

and this is the error I get when I run the query in server1:

  SELECT command denied to user 'slave'@'' for table 'my_items'

Federated storage engine can help you.

In mysql, you can achieve this by using federated storage engine. It allows you to access tables from different server. Once you reference the table from specific server, you can use it. You can get more details about
federated storage engine. Now you can join two tables from different servers. how to use federated engine

ETL tools can help you like Talend data integration

Talend is open source tool which is ETL tool uses in data ware housing. It have nice UI, you can learn it in short duration. Here you can create many Mysql or database connections  and use the different connections for different tables, can join whatever as you need.


How to generate a random character string of length 8 in Netezza? I am trying to generate a random character string of length 8 in Netezza, any suggestions?

If you're happy with a hex string, you could do:

select to_hex(round(random() * 2^32 - 1)::BIGINT) AS randchars;

You could also write a user-defined function if you want a very specific type of string. For this, you would need SSH access on the Netezza appliance and some basic knowledge of C++. It may be simpler for you to write these functions in Lua if you're not comfortable with C++ or if you don't have an account on the appliance, but this requires Netezza Analytics to be installed.


Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB We are migrating from Google-Cloud PGSQL managed database to a managed PGSQL server on Digital ocean.
The dilemma we are facing is that both Google and Digital ocean are managed so we have no access to directories so we can't dump then restore. So how would we go about this? Is there a command that copies the data directly from Google-Cloud PGSQL to Digital ocean?
Digital oceans resources assume we have access to a directory.

Install pg_dump and pg_restore or install entire PostgreSQL Binaries on your local machine or EC2, Droplet compute.
Create a dump file using pg_dump that contains the data to be loaded from GCP PG:
pg_dump -Fc -v --host=<IP or hostname> --username=<dbadmin> --dbname=<DB name> -f  dbname.dump

Restore the data into the target Database for PostgreSQL using pg_restore :
After you've created the target database, you can use the pg_restore command and the -d, --dbname parameter to restore the data into the target database from the dump file.
pg_restore -v --no-owner --host==<IP or hostname> --port=<port> --username=< dbadmin > --dbname=<target database name> dbname.dump

--no-owner parameter causes all objects created during the restore to be owned by the user specified with --username. For more information , click on pg_restore.


How to monitor or do vacuum without stop all? I have postgres9.1

I try to run full vacuum on a specific table , but it run for more than 5 hours.
I stop all process against this table and i cannot have the process down any longer.

Is there a way to run vacuum in background?
Is there a way to monitor vacuum?

VACUUM FULL takes an ACCESS EXCLUSIVE lock on the table, then rewrites the whole table. This will take a long time for a large table, especially since it must also rebuild all indexes. You haven't said anything about the table size or the server's I/O subsystem so it's hard to say if 5 hours is unexpected or not.

You shouldn't generally need to use VACUUM FULL. Just use ordinary VACUUM - and turn autovacuum up so it works harder if you need to do a manual VACUUM more than very occasionally. It is safe to cancel the VACUUM FULL; doing so will undo all it's work, though.

You generally only need to run VACUUM FULL if you're trying to recover from severe table bloat caused by a long-running <IDLE> in transaction connection, an abaondoned prepared transaction, insufficiently aggressive autovacuum, etc. If you can't afford the downtime required for this you can look into pg_reorg as an online, reduced-locking alternative.


SQL Server Merge Replication Dummy Update issue We have SQL Server 2005 SP4, using merge replication.

We started getting this error when the subscribers try to replicate (not all of them but most):

  The process could not deliver dummy update at the 'Publisher'. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200993)

We tried to 'reinitialize' one of the subscribers but that doesn't help. It starts applying the schema and seems do that fine, but when it comes time to download new changes it gets the error.

We have found a lot of records in MSmerge_metadataaction_request pointing to the same article (table), in action = 5.

They look like garbage. When profiling the database those records are returned to the working process even though the article was not included in the subscription that was running (another subscription).

Deleting the records from MSmerge_metadataaction worked out. However, I am not sure if that will generate issues in the long run, at least now the subscriptions are synchronizing.


Recommended Way to Store Logical Expressions Say, I have the following SQL table:
| ID | GameId   | PlanId | MinAgeLimit | Amount |
|:--------------|--------|-------------|-------:|
| 1  | Cricket  |   P1   |   10        |   500  |
| 2  | Cricket  |   P2   |   15        |  1000  |
| 3  | Football |   P1   |   10        |   750  |
| 4  | Football |   P2   |   20        |  1000  |
| 5  | Football |   P3   |   22        |  2000  |
| 6  | Chess    |   P2   |   20        |   800  |

Now, I can have some logical expressions for different 'ReferrerId', like:

If Referrer=ABC, then do not display items where PlanId=P2.
If Referrer=PQR, display only items where GameId=Cricket.
If Referrer=XYZ, display items where (GameID=Cricket OR GameID=Chess) AND MinAgeLimit>10 AND AMOUNT>500

What is the recommended approach to store these custom rules?
PS: I do not want to hard code these rules in my Java program, as I have plans to further provide an interface where the Referrer can update these rules through a self-service portal.

This could be implemented as a set of tables. There will be one column for each logical operator against each source column. As this can lead to a great many combinations I'd suggest deploying only the columns needed at the time. The design allows for new columns to be included later.

The decision table:

Referrer
PlanIdNotEqual
GameIdIn              -- this is just a flag to say whether this condition applies to this referrer
MinAgeLimitGreaterThan
AmountGreaterThan
...
further columns to meet additional conditions

Since there are multiple GameId values for one Referr's conditions it gets a separate table:

DecisionGameIdIn
    Referrer
    GameId

The query then becomes (in pseudo-SQL)

select
  <columns>
from Game
inner join Decision
on  (Decision.PlanIdNotEqual is NULL
     or Decision.PlanIdNotEqual <> Game.PlanId)
and (Decision.GameIdIn is NULL
     or Game.GameId in (select GameId from DecisionGameIdIn where Referrer = <current value>))
and (Decision.MinAgeLimitGreaterThan is NULL
     or Game.MinAgeLimit > Decision.MinAgeLimitGreaterThan)
and (Decision.AmountGreaterThan is NULL
     or Game.Amount > Decision.AmountGreaterThan)
... continue for further conditions
where Decision.Referrer = <the current value>

This can rapidly become out-of-control. Keeping Decision's columns aligned with the query logic is one problem. The combinatorial explosion is another. The plethora of Boolean conditions can overwhelm any indexing strategy resulting in table scans.


SQL Server 2008 R2 Reporting Services: "Exception of type System.OutOfMemoryException was thrown" When I run a report on SSRS reporting services to fetch 600000 row.. I get a error "Exception of type System.OutOfMemoryException was thrown".

I read so many blog's for solving this issue but still I can't to solve it.

I am working on 32 bit OS(window server 2008) and SQL Server 2008 R2 32 bit.

Is it possible to solve this issue?

Upgrading the server to a 64 bit version of Windows, and a 64 bit version of SQL Server might help.

However, what are you doing with a report with 600,000 rows?  If you are using this to export data, you should use SSIS or some other tool designed for the job.  If you are summarizing the data inside the report, re-write the source query to summary the data at the server prior to it arriving at the SSRS instance.


How to reduce size of PostgreSQL table after dropping columns? I have a PostgreSQL database. One table is very large. I want to extract a TEXT column into a separate table and see how much I can reduce the size. The problem is that the size appears to stay the same no matter what I do.

I'm obtaining the size by issuing \dt+ in psql.

I've tried VACUUM FULL and dp_dumpall followed by deleting the database and restoring it.

The size of the table did not change.

I added a second TEXT column, watched the size increase by a few hundred MB, deleted the column, and I cannot get the size to go down again.

How can I get the size of the table to go down after deleting these columns?

TEXT type columns arent stored with the table data. PostgreSQL treats them like a blob and use a toast table to store the actual data. Maybe thats the reason that you are not seeing the table size shrink.


How to install cstore_fdw inside RDS postgres 9.6 cstore_fdw  is foreign data wrapper for columnar storage for postgresql. I want to install it inside RDS postgre 9.6 instance. Is it possible to install inside RDS instance? Thanks

No. It's not supported on RDS at this point. See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.

If you really need cstore_fdw, you'll have to manage your own PG instance.


force to forget logins How can I force SSMS to forget the recently used logins?

Of course I use "SQL Server Authentication" with Login/Password fields.

(It get's annoying, as everytime I open SSMS it shows the "Connect to Server"-Dialog, with a login preselected that is not valid anymore.)

Didn't find any option, and no info here.

Thank you.

If you move to SQL Server 2012 Management Studio (and no reason not to, now that it's free and you can still manage the same set of down-level servers that 2008 R2 lets you manage), you can simply delete old servers right from the connection dialog using the standard Delete key.

Deleting Old server names from "Connect to Server" dialog in SSMS


Create View For All Table Names Listed In Table I know this data structure is morbidly incorrect, but I am playing with the cards I have been dealt as I can not go back and re-write the structure.  I have a table that holds table names, I need to iterate each table name and create a view with the data from each of those table names.  Like below is what I need to do, BUT my issue is that I iterate each table name and the view is dropped, not appended.

Declare @employee varchar(100), @sql varchar(4000), @location varchar(300)

select RTRIM(LTRIM(name)) As employee
INTO #TempTable1
from alphawhiskeycharlie
where name LIKE 'AL%'
AND (terminationdate IS NULL
OR CAST(terminationdate As Date) >= GetDate()-90)
ORDER BY RTRIM(LTRIM(name)) ASC

Declare c1 Cursor For

Select employee
FROM #TempTable1

Open c1

Fetch Next From c1 Into @employee

Set @location = (Select fulltablelocation from servername.databasename.dbo.tableinformation where employeename = @employee)

While @@FETCH_STATUS = 0
  Begin
    Set @sql = 'IF OBJECT_ID('TestView', 'V') IS NOT NULL '
                + 'DROP VIEW TestView '
                + 'Create View [TestView] As '
                +'Select '''+@employee+''' As ''employee'', '
                +'COUNT(employeeID) As [TotalEmployees] '
                +'From '+@location+' '
                +'where employeename is not null'
    Print(@sql)
    exec(@sql)

    Fetch Next From c1 Into @employee

End

Close c1

Untested... but something like this:

Declare @employee varchar(100), @sql varchar(4000), @location varchar(300)

select RTRIM(LTRIM(name)) As employee
INTO #TempTable1
from alphawhiskeycharlie
where name LIKE 'AL%'
AND (terminationdate IS NULL
OR CAST(terminationdate As Date) >= GetDate()-90)
ORDER BY RTRIM(LTRIM(name)) ASC

Declare c1 Cursor For

Select employee
FROM #TempTable1

Open c1

Fetch Next From c1 Into @employee

Set @location = (Select fulltablelocation from servername.databasename.dbo.tableinformation where employeename = @employee)

Set @sql = 'IF OBJECT_ID('TestView', 'V') IS NOT NULL '
                + 'DROP VIEW TestView '
                + 'Create View [TestView] As '
While @@FETCH_STATUS = 0
  Begin
    Set @sql = @sql
                +'Select '''+@employee+''' As ''employee'', '
                +'COUNT(employeeID) As [TotalEmployees] '
                +'From '+@location+' '
                +'where employeename is not null

                UNION '

    Fetch Next From c1 Into @employee

End

-- remove the last UNION
Set @sql = Substring(@sql,1,len(@sql)-10)

Print(@sql)
exec(@sql)


Is there a way to skip errors in procedures and resuming procedure When there is a unique key in a table (not PRIMARY KEY) and procedure is running, on duplicate key error the whole process will be halted. I want to resume on error and call the procedure again.

The procedure:

DELIMITER $$

CREATE PROCEDURE `injatest`.`LoadData` ()
BEGIN

    DECLARE x INT;

    SET x = 1;
    WHILE x <= 14400 DO
        INSERT INTO junc_question_course_iq(q_id,iq_id,ct_id) VALUES
        (CEIL(RAND()*1000), CEIL(RAND()*48), CEIL(RAND()*10));
        SET x = x + 1;
    END WHILE;

END $$

DELIMITER ;

I want to insert about 14000 records in a junction table, but the problem arise when there is a duplicate key for unique(iq_id,q_id)? what do do?

You can use DECLARE CONTINUE HANDLER FOR SQLSTATE to ignore the key violation by declaring an empty block as an exception handler, as follows:

DELIMITER $$

CREATE PROCEDURE `injatest`.`LoadData` ()
BEGIN

    DECLARE x INT;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' BEGIN END;
    SET x = 1;
    WHILE x <= 14400 DO
        INSERT INTO junc_question_course_iq(q_id,iq_id,ct_id) VALUES
        (CEIL(RAND()*1000), CEIL(RAND()*48), CEIL(RAND()*10));
        SET x = x + 1;
    END WHILE;

END $$

DELIMITER ;


Create Range Partition on existing large MySQL table I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 days can be deleted.

I'm looking to partition this table by range (preferably by day), then run an event to keep things tidy by dropping partitions outside of the retention period.

The data in my table goes back around 6 months, so the first thing I'm going to do is run a manual delete on anything older than 60 days so I''m starting with a clean slate.

Then I need to create the partitions, but do I really have to list all my partitions in the alter table statement? i.e.

ALTER TABLE AuditData
PARTITION BY RANGE( TO_DAYS(date) ) (
PARTITION part1 VALUES LESS THAN( TO_DAYS('2018-01-01 00:00:00') ),
PARTITION part2 VALUES LESS THAN( TO_DAYS('2018-02-01 00:00:00') ),
PARTITION part3 VALUES LESS THAN( TO_DAYS('2018-03-01 00:00:00') ),
PARTITION part4 VALUES LESS THAN( TO_DAYS('2018-04-01 00:00:00') ),
PARTITION part5 VALUES LESS THAN( TO_DAYS('2018-05-01 00:00:00') ),

... and so on

Is there a way to create the partition just by saying, partition the existing data by day?

I understand I will need to run a daily job to create a partition.

Thanks

Unfortunately, no shortcut.

Do have an empty partition for "future" data; REORGANIZE to tomorrow and a fresh future.  More discussion.

The ALTER will copy the entire table over, so be prepared for not being able to use the table for some time.


Different results rebuilding an index online and offline I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.

When I run the same rebuild index offline, the average fragmentation is 25%, with 4 fragments and 28 pages.

I think the FILLFACTOR is 90. The database is 77GB.  I'm not a DBA or similar, I'm a C# dev, so I'm not fully familiar with all the terms. There is no activity on this table, this is occurring in our Dev environment.

This is the query, with names redacted.

ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON,
    ONLINE = ON,
    SORT_IN_TEMPDB = ON
);

What could be causing this difference? The same situation occurs on multiple tables.

This is by no means a full answer but may move things along a bit if you were to try something similar and report your results.

I couldn't reproduce them. With the following test table

CREATE TABLE [dbo].[Table]
(
Col BIGINT
)

CREATE NONCLUSTERED INDEX IX ON [dbo].[Table](Col)

INSERT INTO [dbo].[Table]
SELECT top 12000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values v1, master..spt_values v2

And multiple runs of the following script

USE FragTest;

DECLARE @DbccPage TABLE (
  ParentObject VARCHAR(255),
  Object       VARCHAR(255),
  Field        VARCHAR(255),
  VALUE        VARCHAR(255))

DECLARE @sp_index_info TABLE (
  PageFID         TINYINT,
  PagePID         INT,
  IAMFID          TINYINT,
  IAMPID          INT,
  ObjectID        INT,
  IndexID         TINYINT,
  PartitionNumber TINYINT,
  PartitionID     BIGINT,
  iam_chain_type  VARCHAR(30),
  PageType        TINYINT,
  IndexLevel      TINYINT,
  NextPageFID     TINYINT,
  NextPagePID     INT,
  PrevPageFID     TINYINT,
  PrevPagePID     INT,
  PRIMARY KEY (PageFID, PagePID));

DECLARE @I INT = 0

WHILE @I < 2
  BEGIN
      DECLARE @Online VARCHAR(3) = CASE
          WHEN @I = 0 THEN 'OFF'
          ELSE 'ON'
        END

      EXEC('ALTER INDEX [IX] ON [dbo].[Table]
REBUILD WITH
(
    PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON,
    ONLINE = ' + @Online + ',
    SORT_IN_TEMPDB = ON
);')

      INSERT INTO @sp_index_info
      EXEC ('DBCC IND ( FragTest, ''[dbo].[Table]'', 2)' );

      ; WITH T
           AS (SELECT *,
                      PagePID - ROW_NUMBER() OVER (PARTITION BY PageType, IndexLevel ORDER BY PagePID) AS Grp
               FROM   @sp_index_info)
      SELECT PageType,
             MIN(PagePID) AS StartPID,
             MAX(PagePID) AS EndPID,
             COUNT(*)     AS [count],
             IndexLevel
      FROM   T
      GROUP  BY Grp,
                PageType,
                IndexLevel
      ORDER  BY PageType DESC,
                StartPID

      DECLARE @DynSQL NVARCHAR(4000)

      SELECT @DynSQL = N'DBCC PAGE (FragTest, ' + LTRIM(PageFID) + ',' + LTRIM(PagePID) + ',3) WITH TABLERESULTS'
      FROM   @sp_index_info
      WHERE  PageType = 10

      INSERT INTO @DbccPage
      EXEC(@DynSQL)

      SELECT VALUE AS SinglePageAllocations
      FROM   @DbccPage
      WHERE  VALUE <> '(0:0)'
             AND Object LIKE '%IAM: Single Page Allocations%'

      SELECT avg_page_space_used_in_percent,
             avg_fragmentation_in_percent,
             fragment_count,
             page_count,
             @Online                                                   AS [Online],
             (SELECT COUNT(*)
              FROM   @DbccPage
              WHERE  VALUE <> '(0:0)'
                     AND Object LIKE '%IAM: Single Page Allocations%') AS SinglePageAllocations
      FROM   sys.dm_db_index_physical_stats(db_id(), object_id('[dbo].[Table]'), 2, NULL, 'DETAILED')
      WHERE  index_level = 0

      DELETE FROM @sp_index_info

      DELETE FROM @DbccPage

      SET @I = @I + 1
  END

I consistently got results like

Online = OFF

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       119         119         1           NULL
2        2328        2351        24          0
2        2352        2352        1           1
2        2384        2392        9           0

SinglePageAllocations
----------------------

(0 row(s) affected)

avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
98.8139362490734               0                            2                    33                   OFF    0

Online = ON

PageType StartPID    EndPID      count       IndexLevel
-------- ----------- ----------- ----------- ----------
10       115         115         1           NULL
2        114         114         1           0
2        118         118         1           1
2        2416        2449        34          0

SinglePageAllocations
-----------------------
(1:114)
(1:118)

avg_page_space_used_in_percent avg_fragmentation_in_percent fragment_count       page_count           Online SinglePageAllocations
------------------------------ ---------------------------- -------------------- -------------------- ------ ---------------------
97.4019644180875               2.85714285714286             2                    35                   ON     2

At least in the test I did the differences between the two balanced out fragmentation wise (though similarly to your test I did find that rebuilding the index online led to a higher page count.).

I found that the Online = OFF version always used uniform extents and had zero single page allocations whereas the Online = ON always seemed to put the index root page and first index leaf page in mixed extents.

Putting the first index leaf page in a mixed extent and the rest in contiguous uniform extents causes a fragment count of 2.

The Online = OFF version avoids the fragment caused by the lone index leaf page but the contiguity of the leaf pages is broken by the index root page that shares the same extents and this too has a fragment count of 2.

I was running my test on a newly created database with 1 GB of free space and no concurrent activity. Perhaps the Online = OFF version is more vulnerable to concurrent allocations causing it to be given non contiguous uniform extents.


SLAVE off when loading data into the MASTER from another database I have an active MASTER/SLAVE replication (Mariadb 10.2) with a single database (see scenario 1)

I decided to add in IMPORT database to populate the MASTER database as described in scenario 2. However, the SLAVE instance go OFF and “Unknown database import” error is traced back.

To overcome this, I created then a database with same name in SLAVE server (import) and the error is gone (see scenario 3).

However, the created database (slave import) was without tables (in purpose) so to be sure that data is inserted in the slave I tested by creating a test table in the MASTER database and I did insert data from the Master's import database.

As a result, data was obviously available in the slave database even if the slave's import database has no tables.

So basically, we need just the same db name in slave section to get worked without the need of tables.

Could you please explain to me how it works ?and why we need the same db in slave if its a row-based format ?

There are several things going on here, and you will need to look into your configuration on both master and slave to establish where there are any of the following options set on the master:

binlog_do_db
binlog_ignore_db

and whether any of the following are set on the slave:

replicate_do_db
replicate_ignore_db

The thing that you should be aware of is that what matters is the current database on the connection where you are performing the database/table creation because DDL is still logged as statements even in row-based binlog.

So if you do:

mariadb> use replicated_database
mariadb> CREATE TABLE ignored_database.table_name ...

this will not get replicated across.

The solution you are looking for is:

1) On the master, set your binlog_ignore_db = ignored_import_database

2) Make sure the data import process connects to the ignored_import_database when it is performing the data loading so that any DDL doesn't make it across, and make sure that there is no database switching ("use database some_unignored_db") inside the import payload.


How to investigate "aggressive indexes" Having run sp_BlitzIndex on my database I've found a number of "Aggressive Indexes" (https://www.brentozar.com/blitzindex/sp_blitzindex-aggressive-indexes/). Not only but I am finding that the number of "Aggressive Indexes" is slowly increasing.

I'm a dev who dabbles in the DBA side of things and I can't work out how to go from the information provided by sp_BlitzIndex about aggressive indexes to improving them.

I'm OK at performance tuning an individual query, but I don't know how to find out what query might be happening when the index gets aggressive, and I am assuming the locks are happening because the server is under load - which wouldn't be the case if I tune an individual query.

Any tips on how to investigate further would be appreciated.

We've got a ton of learning resources on that Aggressive Indexes page (I just updated it with more, coincidentally) and I can't even begin to do justice to it overall. Having said that, here goes.

Imagine that you've only got a clustered index on a table. Inserts will be pretty doggone fast. However, when you try to update a row in that table, and you don't specify the clustering key in your where clause, then you'll end up with a lot of table locks (aggression). In that case, the fix is to add indexes for the fields you frequently query on.

On the other hand, imagine that you've got dozens of indexes. Whenever you want to insert or delete rows, you're going to need locks all over the place to get your work done. The high number of indexes will slow you down because nobody can get their insert or delete done quickly - and again, you'll see aggressive index warnings. In that case, the fix is to prune down the number of indexes to a more managable number.

Generally, for transactional tables (as opposed to overnight data warehouse loads), I tell people to aim for:

5 or less nonclustered indexes per table,
5 or less fields per index

That rule of 5 and 5 can be violated - the less write activity you have, and the faster your hardware is, and the better you tune your queries, the more you may be able to get away with more indexes. On the flip side, if you have crappy hardware and crappy queries, you might need to drop those numbers lower.

The 5 and 5 rule stems from the fact that I've gotta start people somewhere, and I have 5 fingers on one hand, and 5 fingers on the other - so the rule is easy to communicate.


Ensure percentages are between 0 and 1, inclusive (using a single function) I have percentages in a condition table:

create table condition (percent_decimal number(3,2));
insert into condition values (-0.01);
insert into condition values (0.1);
insert into condition values (1);
insert into condition values (1.1);
commit;

PERCENT_DECIMAL
---------------
          -0.01
             .1
              1
            1.1

I want to select the values, but modify them to present them as percentages between 0 and 1 (inclusive):

Convert -0.01 to 0
Leave .1 as is
Leave 1 as is
Convert 1.1 to 1

I can successfully do this using the greatest and least functions:

select
    percent_decimal,
    least(1,greatest(0,percent_decimal)) as percent_modified
from
    condition

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

However, I'm wondering if there is a more succinct way of doing this--with a single function.

I'm often pleasantly surprised by the clever techniques that DBA-SE members come up with. When I think I've come up with the simplest way to do something, someone comes up with something even simpler.

Ok, this can't be a serious question, reminds me of https://codegolf.stackexchange.com/

I can do better, no function at all.

select
  percent_decimal,
  case
    when percent_decimal <= 0 then 0
    when percent_decimal >= 1 then 1
    else percent_decimal
  end as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

But we need a function. Finally Oracle 12.2 allows long (128 bytes) identifiers, so we can give more meaningful names:

with function
  "Convert -0.01 to 0, Leave .1 as is, Leave 1 as is, Convert 1.1 to 1" (p number)
return number
as
begin
  if (p = -0.01) then return 0; end if;
  if (p = 1.1) then return 1; end if;
  return p;
end;
select
  percent_decimal,
  "Convert -0.01 to 0, Leave .1 as is, Leave 1 as is, Convert 1.1 to 1"(percent_decimal)
    as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

I also happen to have a super secret function that coincidentally does exactly what you need:

create or replace function "." wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
97 aa
30RI5qP/Li6VhppxgO2AXQw62mYwg1xfrZ6pfI6iO8cYr/L9s3tguDonZ52omRm1G6sfUCen
xkcEU/4jCxrp7Ii4VNrCrVY6cDr0UNz1Id5vZ5p7e0S0F+AEd55sz9JyGokPaBbDJHFVdW+A
IAHg6Xpxmglu3BtbDtBmCA==

/

select
  percent_decimal,
  "."(percent_decimal) as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1

Must use a built-in function? No problem.

select
  percent_decimal,
  round(percent_decimal/1.05, 1) as percent_modified
from condition;

PERCENT_DECIMAL PERCENT_MODIFIED
--------------- ----------------
          -0.01                0
             .1               .1
              1                1
            1.1                1


Find out which client IP and Query using highest network Bandwidth from SQL Server I want to know which clients (IP or Hostname) are taking highest bandwidth from server and by what query they are taking high bandwidth.

Field Names:

Client Name/IP, Query, KB transferred, Date&Time of query executed, Time took to execute

Is there anyway to get it done in SQL Server?

Thanks Sanket

The DMVs sys.dm_exec_connections and sys.dm_exec_sql_text have most of what you need.  Try something like this:

SELECT TOP 10 session_id, connect_time, num_reads, num_writes, last_read, last_write, client_net_address, most_recent_sql_handle, x.*
FROM sys.dm_exec_connections
    OUTER APPLY sys.dm_exec_sql_text( most_recent_sql_handle ) x
ORDER BY num_reads + num_writes DESC

You might also wish to review some of the other DMVs, eg sys.dm_exec_requests and sys.dm_exec_sessions for useful information.


Database Schema Access I have a database with multiple (10) schemas. I need a user to have access to two of them. If I grant access to the database and then GRANT SELECT to 2 schemas, is access to the other schemas DENY-ed by implication? Or must I grant permissions to those schemas and then DENY SELECT to the rest of them?

You should create roles and grant access to roles rather than granting users access directly to schema.

Once you assign permissions to the role, you can just add users to the role. This way you dont have to manage permissions for individual users. The users inherit permissions granted to role.

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the database
GRANT
      SELECT, INSERT, UPDATE, DELETE, ALTER

ON SCHEMA::dbo
      TO TableSelector
GO

-- Add an existing user to the new role created
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO

-- Revoke access rights on a schema from a role
DENY ALTER  -- you can customize here ...
ON SCHEMA::dbo
      TO TableSelector

see my answer to : Setting user permissions for different SQL Server schemas


How to convert primary keys into clustered primary keys for a great number of tables keeping referential identity After discovering, that I better change the primary keys from non clustered to clustered. Cf this question about clustered indexes on identity columns, the next step is how to do this in a prcatical way.
I was surprised that I didn't found this question on SO.
Having referential references between my tables, I can't simply do
if object_id('MyChild') > 0 drop table MyChild
if object_id('Mytable') > 0 drop table Mytable
create table Mytable(
    id int Not NULL
)
go
create table MyChild(
    id int Not NULL,
    masterid int NULL
)
go
alter table Mytable add constraint PK_Mytable primary key  (id)
alter table MyChild add constraint FK_MyChild_MyTable foreign key (masterid) references Mytable(id)
go
-- Now I want to change the primary key into a clustered primary key
-- this throws an error
ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable
GO
ALTER TABLE Mytable ADD CONSTRAINT PK_Mytable  PRIMARY KEY CLUSTERED (id)
GO

Here I get the following error
Msg 3725, Level 16, State 0, Line 1
Auf die 'PK_Mytable'-Einschränkung wird von der 'MyChild'-Tabelle, FOREIGN KEY-Einschränkung 'FK_MyChild_MyTable' verwiesen.
Msg 3727, Level 16, State 0, Line 1
Die Einschränkung konnte nicht gelöscht werden. Siehe vorherige Fehler.

OK I know that it works, when I drop the foreign key constraint before the change and reestablish it afterwards.
alter table MyChild drop constraint FK_MyChild_MyTable
go
ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable
GO
ALTER TABLE Mytable ADD CONSTRAINT PK_Mytable  PRIMARY KEY CLUSTERED (id)
GO
alter table MyChild add constraint FK_MyChild_MyTable foreign key (masterid) references Mytable(id)
go

That sounds like, I have to script all the foreign key relations from the database, drop them, do my primary key changes and reestablish the foreign keys from the generated script.
Are there any better ways for this task.
BTW. Finally we want to do this on production databases. (Downtime is an allowed option). All databases are SQL Server 2005 or SQL Server 2008.
Edit:
A coworker proposed to drop all indexes before this operation and to rebuild them afterwards. Sounds reasonable to me.
2. Edit:
Looking at this dba.stackexchange  question, I'm starting to ask myself whether it would suffice to just add clustered indexes to the tables primary key columns.

Your approach is correct and pretty much the only way, but I would consider using a tool like Red Gate to help me.

drop all FKs without scripting them
change the indexes (as above)
generate an "FK only" script using the compare tool to re-apply the FKs

The 3rd step saves writing something to script our your FK definitions, or fiddling with SSMS/SMO


How to separate MongoDB journal I'm trying to use NFS as db path for my MongoDB (which I read is not recommended, but a client demands it that way) and I'm having trouble separating the journal files.

How would I do that? I've tried setting the dbpath to /mnt/nfs and it works, but the journal directory is there as well...

Any hints?

You have to create a symbolic link that points to the new mountpoint:

  If your server runs out of disk space for journal files, the server
  process will exit. By default, mongod creates journal files in a
  sub-directory of dbpath named journal. You may elect to put the
  journal files on another storage device using a filesystem mount or a
  symlink.

  Note

  If you place the journal files on a separate storage device you will
  not be able to use a file system snapshot tool to capture a valid
  snapshot of your data files and journal files.

Updated link about: Journal files


how can i rename all the tables in a database so the first character of each table's name is removed how can i rename all the tables in a database so the first character of each table's name is removed. thanks! I'm using mysql 5.6.

Easy enough to do with SQL. You can send the output to a file then execute it once it's verified or just copy and paste the DDL statements back into your client to execute manually.

mysql> show tables;
+---------------------+
| Tables_in_mysqlslap |
+---------------------+
| tb1                 |
| tb2                 |
| tb3                 |
| tb4                 |
+---------------------+
4 rows in set (0.00 sec)

mysql> select
  concat("RENAME TABLE ",table_name, " TO ", substring(table_name,2), ";") INTO OUTFILE '/var/lib/mysql-files/rename_tables.sql'
  FROM information_schema.tables
  WHERE table_schema = 'mysqlslap';
  Query OK, 4 rows affected (0.00 sec)

+--------------------------------------------------------------------------+
| concat("RENAME TABLE ",table_name, " TO ", substring(table_name,2), ";") |
+--------------------------------------------------------------------------+
| RENAME TABLE tb1 TO b1;                                                  |
| RENAME TABLE tb2 TO b2;                                                  |
| RENAME TABLE tb3 TO b3;                                                  |
| RENAME TABLE tb4 TO b4;                                                  |
+--------------------------------------------------------------------------+

mysql> RENAME TABLE tb1 TO b1;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+---------------------+
| Tables_in_mysqlslap |
+---------------------+
| b1                  |
| tb2                 |
| tb3                 |
| tb4                 |
+---------------------+
4 rows in set (0.00 sec)


How do I determine what is activating my SQL service broker proc? I have several queues.  One of which has suddenly started acting oddly.  It activates repeatedly while nothing is in it's queue.  I even manually removed old conversations and this thing is still spinning up new spids to pull on the empty queue.  I can see in my logger that it runs the transaction to check the queue,  finds it to be empty, then closes.  A new spid will immediately appear and check the queue again even if nothing has changed.  Another weird thing is that it's only 1 spid at a time.  This Activation is set to allow 10 readers.

Check the queue monitor state in sys.dm_broker_queue_monitors for troubleshooting:

SELECT QUOTENAME(OBJECT_SCHEMA_NAME(queue_id, database_id))  + N'.' + QUOTENAME(OBJECT_NAME(queue_id, database_id)),*
FROM sys.dm_broker_queue_monitors;

Rusanu has a good blog post (Understanding Queue Monitors) that describes the internals of the monitor state machine that seems to be in an unexpected state. Although the troubleshooting section in his post is mostly about the more common problem of activation not occurring rather than visa-versa, you may find the information helpful in researching the cause.

Try toggling the activation status on the queue to see if that fixes things.  Add the code for the activated proc to your question if you need more help.


DB2: Why does translate function also replace spaces? I would like to remove control characters (HEX \x00 until \x7F) from a DB2 data base field. For this I tried to apply the following translate-function:

TRANSLATE(field, 'X',  x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')

It does work for control chracters, but the function also translates question marks, and I don't understand why? Interestingly question marks are not replaced by "X" like the control characters but by a space \x20.

Why does it behave like that?

Original values:

Text View: mhlkm
Hex: 6D 68 6C 6B 6D 3F
Text View in Hex Viewer: mhlkm?

Translated values:

Text View: mhlkm
Hex: 6D 68 6C 6B 6D 20
Text View in Hex Viewer: mhlkm

The "to-string" in your TRANSLATE is a single "X", so only the first character found in "from-string" (X'00' in your case) will be translated to "X". All other characters found in "from-string" will be replace by the "pad" character. You don't specify one, so a blank is used by default. This is why the X'15' at the end of your inut field is translated to X'40', which is an EBCDIC blank.


Create phone call with Mockaroo for Neo4j I'm following this guide to create a dataset with a call log.
But i don't understand the datatype used for the Date fields and the condition for Start_Date, End_Date and Duration to have coerent values (for example date_start = 10/10/10:1050 date_end=10/10/10:10.55 DURATION = 5)

In that guide, START_DATE and END_DATE are expressed in UNIX epoch time, which is the number of seconds that have elapsed since the beginning of 1970 (in the UTC time zone).

By the way, you will get much better support for neo4j questions at stackoverflow.com.


Database design - People and Organisations The software we are building has "Customers".
The Customer could either be a Person or an Organisation.

I really want to create an optimal schema for this.

I have these considerations.

A Person can have one or more Contacts (eg. phone, email)
A Person can have one or more Addresses
An Organisation can have one or more Contacts (eg. phone, email)
An Organisation can have one or more Addresses
An Organisation can have one or more Persons related to it.

I would like the App to scale decently, so the schema should fit this choice.

I am trying to achieve something like the following.

SELECT * FROM Customers + a few joins.

1. Person | NULL | John Doe | Primary Organisation Contact | Primary Address

2. Organisation | Acme Ltd | Jane Doe | Primary Organisation Contact | Primary Address

How should I create an optimal schema for relating the above?

I have attached a rough Visual Schema hierarchy - I know im way off!! and Im sure im making errors.

Is it possible to get a married set of results depending on whether its a Person or Organisation?

Joining a Person and Contact/Address to Customer is simple, but How do you join Organisation's primary Contact/Address ?

Is there any easier way to achieve my spider-diagram looking Schema.??

Some insights from having actually built a couple of apps with contacts and companies.

Firstly, you're missing several use-cases in your outline. Among the colorful ones I've run into over the years which you don't necessarily cover:

Some organizations have sub-organizations, be them divisions, subsidiaries, you name it.
Some people belong to several organizations. As in VP whatever of XYZ, chairman of ABC, and self-employed CEO/consultant -- each on a part time basis, hopefully.
Some people can be contacts for an organization without actually belonging to it. E.g. when a high profile consultant is temporarily hired as a project lead, he could possibly become the primary contact for company XYZ without actually belonging to it.
People can have sub-people too. And the latter can be in the same companies, or not. For instance, our VP of XYZ might prefer that you go through his main primary secretary over there, unless you contact him regarding ABC in which case that would be his assistant; or personally for consulting work -- a very busy person indeed, but there are people like that.
Some organizations have no contacts associated with them. This can happen, for instance, in an app that outputs lists prospect companies (for sales people) in which contacts have yet to be identified.
Conversely, contacts can have no organizations. For instance consumers.

Secondly, since your outline mentions customers, be wary that customers can be a company or a (physical) person. You seem to have that part covered based on your diagram, so let's move on.

If your next step is to add anything related to accounting, such as an "orders" table, be wary that anything accounting related is tied to a company, a contact, products, prices, etc. at a given point in time. These details can evolve in all sorts of colorful ways, and a frequent design mistake is to create the perfect normalized design and assume that you'll just update the tables as needed. Big no-no. If the taxman asks you to print out your invoices, and your IT says company XYZ at price P when accounting booked company ABC at price Q, you're very, very screwed. And don't get me started on archived/closed yearly accounts and reports that change due to similar design mistakes.

Thirdly, be very, very, very wary of UI/UX issues that you might introduce by normalizing too much. If it does not work like the apps that typical users have in their hands (read: Outlook), you'll be a poorly trained secretary away from changing the company of every employee at XYZ to ABC when Joe gets a new job. I've actually seen this happen and it was not a pretty sight. (She kept her job, in case you're wondering.)

Lastly, don't get me started on merging the inevitable duplicate companies, contacts, and so many other colorful things that couldn't possible turn up. Keep those in mind and make sure that your schema is very, very forgiving because it will happen.

Now... in practice...

Personally, I've come to actually turn a blind eye on normalization in this case. Contacts/Companies is one of those cases where DB design and beautifully normalized data as taught in school is a recipe for trouble in the form of resource hogging, over-complicated queries and ludicrously complex UI. It is, imho anyway, not the right thing to do.

Start by creating a contacts table, complete with fields such as first and last name, display_name if needed, company_name, address, phone, cell_phone, email, email2, secretary_name, secretary_phone, etc.  If it's available when you create a contact in Outlook, it probably belongs in there.

You'll note that I didn't mention a companies table. That's because you usually don't want any strong ties between your contacts table and your companies table. If you need one, add one, and add a copmany_id in addition to the company_name. But then, make it a foreign key on delete set null. And be sure to keep it very, very loosely tied to company_name at the database level. Maintain it at the front-end level -- not anywhere else. This will keep a secretary from inadvertently changing the company of multiple contacts.

Keep things sane. Anything that might reasonably show up in a list (i.e. select * from contacts where ... limit 10), be queried against, or be useful frequently ought to be in the table. No joins, nada. Query, iterate through the result, done.

If you've really want additional garbage in there, you've two options.

One is to create an extra_contact_details table. It can be an EAV table, or the full load of company_name, address, phone, etc. fields, or a hogwash of normalized bits and pieces. Whichever option you take, don't over do it. Either will create additional (potentially complex) queries, and heaps of thorny programming issues down the road when you create the UI. What's absolutely key here is, if you go this route, a secretary who has always worked with Outlook needs to make sense of it.

The other, which I'll actually recommend upfront, is to add a text field called "extra_contact_details" in the contacts table and begone with it. No normalization at all. One set of primary details. Frequently used secondary details. Anything extra as plain text. Secretary gets it. End users get it. Done.

Lastly, if you need to store a version of any data at a given point in time, be sure to duplicate the value of any key details -- read: whatever you need to print it exactly as it was when it got inserted.


MySQL set column value if another column's new value is different than current one I have a table that has a column mycolumn and a column that has a boolean flag mycolumn_is_new. I want to set the second one to true when an UPDATE query changes the value of the first one. The user can type the same data or new data.

I have come up with this query, but mycolumn_is_new is always set to false :

UPDATE mytable SET
    mycolumn = '1234',
    mycolumn_is_new = IF(mycolumn <> '1234', TRUE, FALSE)
WHERE id = 321

Is it possible check the new and old value in a single query?

All expressions in the SET section are evaluated in the order in which they are written. So mycolumn in the second expression is equal to the value set in the first expression. Simply switch expressions - mycolumn_is_new = first.

  Is it possible check the new and old value in a single query?

Yes, using user-defined variable. Save old, assign new, use old from variable and new from field. The simplest task need those method - switch the values of two fields in one UPDATE.