Trouble in Multi-Language DB I'm working on a DB design for medicines in many countries with different languages. I created a table for the different languages I want and link it to another table of chemicals.  But I find that only one table will contain all medicines for all countries with all languages and the same applies for other tables linked to the language table.  Ths means that the software in any country will contain unneccessary data about other countries and unneccessary overhead on the server.

How can I solve this design problem?

See my question to understand what I mean.

Previously I assumed that all users would connect to a central database. Here it sounds like you want to have a copy of the database local to each team.

The decision to have one or many instances of the database might affect which data is stored in each instance, but it should not affect the logical model that relates languages, chemicals, and the names of chemicals.

SIngle-instance solution

It should be clear that a central database would have to store all languages, all chemicals, and all names so that each team could use it. For example, the database would store a code for every chemical that is studied by each team, a code for every language spoken by each team, the name of every chemical in every language.

I assume that the database has a table like this to store the names of chemicals in each language:

CREATE TABLE ChemicalNames (
  ChemicalCode VARCHAR(12) NOT NULL,
  LanguageCode CHAR(2) NOT NULL,
  ChemicalName NVARCHAR(50) NOT NULL,
  CONSTRAINT PK_ChemicalNames PRIMARY KEY (ChemicalCode, LanguageCode)
);

INSERT INTO ChemicalNames (
  ChemicalCode,
  LanguageCode,
  ChemicalName
)
VALUES
  ('7732-18-5', 'en', 'Water'),
  ('7732-18-5', 'fr', 'Eau'),
  ('7732-18-5', 'es', 'Agua'),
  ('50-00-0', 'en', 'Formaldehyde'),
  ('50-00-0', 'fr', 'Méthanal'),
  ('50-00-0', 'es', 'Formaldehído'),
  ('8007-40-7', 'en', 'Mustard oil'),
  ('8007-40-7', 'fr', 'Huile de moutarde'),
  ('8007-40-7', 'es', 'Aceite de mostaza');

I assume that the user interface of the client application will display the names of chemicals. For a Spanish-speaker, the application would load the names of chemicals in this way:

SELECT ChemicalCode, ChemicalName
FROM ChemicalNames
WHERE LanguageCode = 'es';

For a French speaker, the application would load the names using the same query with a different language code:

SELECT ChemicalCode, ChemicalName
FROM ChemicalNames
WHERE LanguageCode = 'fr';

Multiple-instance solution

If you have multiple instances, you can simply treat each of them as if they were a central database. Each instance would have all the data needed by any team, even though only one team would use the instance.

The benefit of this approach is that you don't need to worry about which teams need which data - just give all the data to each of them.

They queries to extract data from the database would be the same. The American team would use the American database to get the English names. The French and Spanish names might not be useful to them, but their presence should do no harm.

You haven't made it clear what data you would consider to be 'unnecessary' in a multiple-instance solution. Do you want to limit the languages available to each user? Are you sure that you will never have a French-speaker working with the American team? Do you want to limit the chemicals available? Does each team work with a strict set of chemicals and never cooperates with other teams?

Can you demonstrate that the share-all approach would be impractical for the amount of data you have?

If you really have so much data, you could provide each team with an empty instance of the database and load only the data that each team requires. Load only the languages spoken by the team, load only the chemicals they study, and load only the names of the chemicals in the spoken languages.


High CPU usage from MySQL with no queries at all running I noticed that MySQL is using way more CPU than usual on my workstation (usual is 1% or less, because this is my workstation, not a server).

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14004 mysql     20   0  536m  44m 7136 S  141  1.2  65:24.72 mysqld

I looked in my slow log and there are no queries at all. I ran tail -f on the general log to see what was running and there was nothing at all there either. The MySQL processlist is empty as well.

+-----+------+-----------+----+---------+------+-------+------------------+
| Id  | User | Host      | db | Command | Time | State | Info             |
+-----+------+-----------+----+---------+------+-------+------------------+
| 165 | root | localhost |    | Query   | 0    |       | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+

Every other post I've seen is related to high CPU usage due to a specific query, but I've got no queries running at all. Does anyone know how to determine why MySQL is using all that CPU?

In case it matters, here's more info about my system:

Linux kubuntu 3.2.0-26-generic

Server version          5.5.24-0ubuntu0.12.04.1-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 57 min 8 sec

Threads: 1  Questions: 406  Slow queries: 0  Opens: 567  Flush tables: 1
Open tables: 140  Queries per second avg: 0.118

had the same problem, here is a solution:

http://blog.mozilla.org/it/2012/06/30/mysql-and-the-leap-second-high-cpu-and-the-fix/

Everything happens because of "1" second.


Show sql statements from mysql binlog (GTID) I have mysql binlogs  for replication through GTID.

I'm trying to show executed update/insert statements like this:

mysqlbinlog --base64-output=DECODE-ROWS mysql-bin.000024

But all I see is something like this, no traces of update or insert stmt:

SET TIMESTAMP=1431681617/*!*/;
BEGIN
/*!*/;
# at 746987321
# at 746987392
# at 746987484
#150515 11:20:17 server id 1  end_log_pos 746987515 CRC32 0xeb874754    Xid = 997501767
COMMIT/*!*/;
# at 746987515
#150515 11:20:22 server id 1  end_log_pos 746987563 CRC32 0xc5ece64a    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'a4ade293-c63a-11e4-94cf-005056944a56:2059057'/*!*/;
# at 746987563
#150515 11:20:22 server id 1  end_log_pos 746987650 CRC32 0x92296355    Query   thread_id=71622 exec_time=0 error_code=0

I'm not sure about format/configuration of GTID replication and where to look for it...

It seems that the option  --verbose must be added:

mysqlbinlog  --base64-output=AUTO --verbose mysql-bin.000005

In the result you see:

### UPDATE `customer`
### WHERE
###   @1=388442
###   @2=382023
###   @3='2015:05:30'
###   @4='2015:06:02'
###   @5=3
###   @6=1
###   @7=0

@x are the table columns in their order


Long running delete query I have a table with roughly 11 million rows, defined as:

CREATE TABLE [sko].[stage_närvaro](
    [datum_fakta] [datetime] NULL,
    [person_id] nvarchar NULL,
    [läsår_fakta] nvarchar NULL,
    [termin_fakta] nvarchar NULL,
    [period_fakta] nvarchar NULL,
    [vecka_fakta] nvarchar NULL,
    [veckodag_fakta] nvarchar NULL,
    [ämne_id] nvarchar NULL,
    [ämne] nvarchar NULL,
    [frånvaro_min] [float] NULL,
    [närvaro_min] [float] NULL,
    [frånvaroorsak_id] nvarchar NULL,
    [frånvaroorsak] nvarchar NULL,
    [beskrivning] nvarchar NULL,
    [personal_id] nvarchar NULL,
    [försystem] nvarchar NULL
)

With the following non-clustered index:

CREATE NONCLUSTERED INDEX [stage_skola_närvaro_ix1] ON [sko].[stage_närvaro]
(
    [person_id] ASC,
    [termin_fakta] ASC,
    [läsår_fakta] ASC
)

When I run the following delete query, it takes atleast 2+ hours to complete.

DELETE  sko.stage_närvaro
FROM    sko.stage_närvaro e
WHERE   försystem = 'Extens'
AND EXISTS (
    SELECT  *
    FROM    ext.v_imp_närvaro v
    WHERE   e.person_id = v.person_id
    AND     e.termin_fakta = v.termin_fakta
    AND     e.läsår_fakta = v.läsår_fakta
)

Is my delete-query using my index? Would it help to disable the index before deleting, and enable it afterwards?

Edit:
The view ext.v_imp_närvaro has the same amount of rows as the table sko.stage_närvaro.

Edit2:
I suspected that it was an I/O issue, so I ran the following query as suggested by DaniSQL here:

SELECT TOP 10
        wait_type ,
        max_wait_time_ms wait_time_ms ,
        signal_wait_time_ms ,
        wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits ,
        100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,
        100.0 * ( wait_time_ms - signal_wait_time_ms )
        / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM    sys.dm_os_wait_stats
WHERE   wait_time_ms > 0 -- remove zero wait_time
        AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH',
  'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK',
  'SLEEP_BPOOL_FLUSH', 'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT',
  'FT_IFTSHC_MUTEX', 'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
  'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
  'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
  'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
  'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
  'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'WAITFOR',
  'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN', 'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

With the following result. I'm not sure how to interpret them though.

I don't know fully details of your environment, i.e. if the tables in question are mostly used for writing or reading.

How often you do this delete?

what is the primary key and clustered index of [sko].[stage_närvaro]?

If I wanted to optimise this delete there are a few things I would consider:

1) an index on the underlying tables of the view  ext.v_imp_närvaro with the columns used in the select (person_id, termin_fakta,[läsår_fakta]) you want an index seek there most likely (no need to include any columns because you are just going there for the EXISTS)

2) I have been using a lot filtered indexes and I would consider the following:

CREATE NONCLUSTERED INDEX IDXF_STAGE_NARVARO_FORSYSTEMS_EXTENS
ON [sko].[stage_närvaro] ([försystem])
INCLUDE ([person_id],[termin_fakta],[läsår_fakta])
WHERE [försystem] = 'Extens'

3) I can see some LCK_M_S going on there, not sure if related to this query in particular but nevertheless I try to always use ROWCOUNT and do big deletes and updates in batches, something like the following:

    USE DATABASENAME
    GO

    DECLARE @RC INT
    SELECT @RC = 0
    SET ROWCOUNT  5000

    WHILE (1 = 1)
      BEGIN
        BEGIN TRANSACTION

                        DELETE  sko.stage_närvaro
                        FROM    sko.stage_närvaro e
                        WHERE   försystem = 'Extens'
                        AND EXISTS (
                            SELECT  *
                            FROM    ext.v_imp_närvaro v
                            WHERE   e.person_id = v.person_id
                            AND     e.termin_fakta = v.termin_fakta
                            AND     e.läsår_fakta = v.läsår_fakta
                        )

                     SELECT @RC = @@ROWCOUNT

                     print CAST ( DB_NAME()  AS VARCHAR(500) ) +
' -- ' + CAST ( @RC  AS VARCHAR(10) ) + ' -->  ' +
CAST( GETDATE() AS VARCHAR(25))

                     WAITFOR DELAY '00:00:01';

        IF @RC = 0
          BEGIN
            COMMIT TRANSACTION

            BREAK
          END

        COMMIT TRANSACTION
      END

    SET ROWCOUNT  0

 --==============================================================
 --SET ROWCOUNT 10000 -- define maximum updated rows at once

-- DO THE UPDATE

-- don't forget about bellow
-- after everything is updated
--SET ROWCOUNT 0

-- Setting ROWCOUNT to 0 turn off limits - don't forget about it.
--===============================================================

This may not be a comprehensive solution, because there are bits missing on the question too, however, it will surely give you some ideas as possible ways to improve big delete operations.


Add database to existing MySQL master-master replication which uses binlog_do_db? We have 2 Mysql 5.7 servers running in master-master replication mode.

And there is a database replicating between these 2 servers:

binlog_do_db = database1

Now we need to add a new database into replication, I have read Add database to existing replication which uses binlog_do_db?, but its for master-slave. Is there any other things should be considered for master-master situation?

Master-Master is simply Master-Slave in both directions.  So all the settings work the same.

Data does not endlessly loop between the two Masters as long as they don't have the same server_id.

I'm pretty sure you simply have multiple lines.  See https://forums.mysql.com/read.php?26,255998,255998

M-M is fraught with perils, but I guess you have gotten past them.  So I won't harp on it.


Filtering One column into Different Columns in one statement I need to

SELECT amount FROM table WHERE type='1' AND type='2' AND type'3'

Depending on the types, I need them to be pulled into different columns.

For example,

IF type=1 THEN amount1
IF type=2 THEN amount2
IF type=3 THEN amount3.

Is it possible to this in one statement?

This is one of the ways

SELECT

CASE WHEN `amount`='type 1' then `amount` end as `amount1`,

CASE WHEN `amount`='type 2' then `amount` end as `amount2`,

CASE WHEN `amount`='type 3' then `amount` end as `amount3`

FROM db.tablename;

Ex:

SELECT

CASE WHEN amount=1000 then amount end as amount1,

CASE WHEN amount=2000 then amount end as amount2,

CASE WHEN amount=3000 then amount end as amount3

FROM db.test;


Data base structure of a subscriber list I am building a application that allow different user to store the subscriber information

To store the subscriber information , the user first create a list
For each list, there is a ListID.

Subscriber may have different attribute : email phone fax ....

For each list,  their setting is different , so a require_attribute table is introduced.
It is a bridge between subscriber and List

That store Listid ,subid , attribute, datatype

That means the system have a lot of list, each user have their own list, and the list have different attribute, some list have email , phone , some may have phone, address, name mail.. And the datatype is different, some may use 'name' as integer , some may use 'name'
as varchar

attribute means email phone, it is to define for which list have
which subscriber attribute
datatype means for each attribute, what is its datatype

Table :subscriber :
Field :subid , name,email

Table :Require Attribute:

Field : Listid ,subid , attribute, datatype

The attribute here is {name, email}

So a simple data is

Subscriber: 1 , MYname, Myemail

Require Attribute :

 Listid , 1 , 'email', 'intger'

 Listid , 1 , 'name', 'varchar'

I found that this kind of storage is too complex too handle with,  Since the subscriber is share to every body, so if a person want to change the datatype of name, it will also affect the data of the other user.

Simple error situation:

Subscriber:

list1,  Subscriber 1 , name1, email1

list2, Subscriber 2  , name2 , email2

Require Attribute :

     List1 , Subscriber 1 , 'email', 'varchar',

     List1 , Subscriber 1  , 'name', 'varchar',

     Listid , Subscriber 2 , 'email', 'varchar',

     Listid , Subscriber 2, 'name', 'integer',

if user B change the data type of name in require attribute from varchar to integer, it cause a problem. becasue list 1 is own by user A , he want the datatype is varchar, but list 2 is won by user B , he want the datatype to be integer

So how can i redesign the structure?

No RDBMS system I know of supports mixing a data-type within a single column. So I think you've got two solutions:

Separate tables - each user's list gets their own Database table. This would allow you the flexibility to customize the data type at the column level.
Flexible data type in the "name" column. You could use a varchar or even a binary blob in the name column and allow the user to store whatever they want in that column. Then, at the application level, you could interpret that data in whatever format you like by, for instance, casting it to an integer.

It sounds to me, since you're hoping to store different types of data with different structures to it, that you should probably just be using separate tables.


Is it possible to audit query results in SQL Server? I want to find out if it is possible to audit the results of any query that gets executed on an instance of SQL Server. We currently have C2 auditing enabled, but this only showed what query was executed, not what was returned.

Since this project is health-information related, we want to ensure that we are auditing to the most granularity we can obtain to meet HIPPA requirements. Previously this was not required, but the nature of our project is changing.

The goal is: if a user logs into our system and executes a query, we want to know every patient that was returned from that query.

Is there either a built-in feature (triggers?) or a third-party application that we can use to achieve this? We looked into ApexSQL, but their customer service relayed that their software does not audit at that granularity.

  Could this audit be restricted to the Patients table only?

Unfortunately no, due to the fact that if you were to query a different table, there is patient-related information; so if you happen to know that patient's foreign key, you could "get around" the audit by looking up individual clinical records.

We're not concerned about space or speed at this point in time.

You could (a) force data access through a stored procedure, then (b) that stored procedure would dump the results to a #temp table before returning them to the user, then (c) the procedure would log the results to some background auditing table.

You'd want to rotate that though because it's going to get very big very fast, not to mention this will not exactly make queries faster. - aaron-bertrand


Setting for default column type Is there a setting in SQLServer 2008 and newer for default data type of date-time columns, i.e. datetime or datetime2? Actually more like something that would tell SQLServer to use datetime2 instead of datetime when creating a table/view even if the latter is requested explicitly.

UPDATE: Thank you everyone. With this issue I am actually on the pitching side, that is creating tables from Java application that are supposed to have datetime columns, but end up having datetime2. Thought it might be due to DBMS configuration, but, probably, it's due to intricacies of one of our frameworks. Oh well, back to digging code :)

P.S. Special thank you goes to @srutzky for detailed response with multiple options, but accepted @John M's answer as being the most spot on.

No, there isn't a way to automatically change a datetime field to a datetime2 field if the former is specified in a table creation.

To do it manually:

ALTER TABLE [table] MODIFY COLUMN [column] DATETIME2;

If the issue pertains to end users, education is your best bet to make the creation of tables more uniform and compliant with using datetime2.


What would be a optimal index for this query in mysql I'm trying to optimize this query.

SELECT * FROM sales_reps
WHERE account_id = 1
AND (state = 'enabled')
AND (clients >= 5 OR revenue >= 10000)
ORDER BY name, platform, client_group, from_date

All the field in the ORDER BY clause are string except from_date which is a date. (account_id, name, platform, client_group, from_date) is a unique key combination.

I tried creating indexes with combination or those fields but when I do explain it doesn't seem to be using it. The table would have about 2 million rows for each account.

Any thoughts on how I could go about optimizing it?

Update 1: Here is the explain and result:

EXPLAIN SELECT *
FROM `sales_reps`
WHERE `sales_reps`.`account_id` = 1
AND state = 'enabled'
AND (clients >= 1 OR revenue >= 100)
ORDER BY name,platform,client_group,from_date

Here is the result

id select_type table      type possible_keys           key  key_len ref  rows   Extra
1  SIMPLE      sales_reps ALL  account_id_4,account_id NULL NULL    NULL 33215  Using where; Using filesort

account_id_4 is the index with the following keys account_id, state(10), name,       platform, ad_group, from_date, clicks, impressions

Update 2: Here is the full table structure:

CREATE TABLE `sales_reps` (
  `account_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `platform` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `client_group` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `max_cpc` decimal(8,2) NOT NULL DEFAULT '0.00',
  `match_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `first_page_cpc` decimal(8,2) DEFAULT '0.00',
  `top_page_cpc` decimal(8,2) DEFAULT '0.00',
  `quality_score` decimal(8,2) DEFAULT '0.00',
  `revenue` int(11) DEFAULT NULL,
  `clients` int(11) DEFAULT NULL,
  `conversions` int(11) DEFAULT NULL,
  `cost` decimal(8,2) DEFAULT '0.00',
  `average_position` decimal(8,2) DEFAULT '0.00',
  `campaign` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `phone_view` int(11) DEFAULT '0',
  `website_view` int(11) DEFAULT '0',
  `request_submit` int(11) DEFAULT '0',
  UNIQUE KEY `account_id_4` (`account_id`,`state`(10),`name`,`platform`,`client_group`,`from_date`,`clients`,`revenue`),
  KEY `account_id` (`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I have a few suggestions that may help
SUGGESTION #1 : Use index with different column order
ALTER TABLE sales_reps ADD UNIQUE INDEX
(state,account_id,name,platform,client_group,from_date);

Note that state and account_id are reversed. That way, there is no need to traverse all rows for a given account_id that would separate enabled from disabled. Only enabled entries for account_id are read.
SUGGESTION #2 : Refactor the Query
Instead of
SELECT *
FROM `sales_reps`
WHERE `sales_reps`.`account_id` = 1
AND state = 'enabled'
AND (clients >= 1 OR revenue >= 100)
ORDER BY name,platform,client_group,from_date

Try this experiment
First run this query
SELECT enabled,accountid,name,platform,client_group,from_date
FROM sales_rep WHERE account_id=1 AND state='enabled'
ORDER BY enabled,accountid,name,platform,client_group,from_date

then run this query
SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
AND clients >= 1 OR revenue >= 100

If the performance of those two queries are fast, then JOIN them as subqueries and see
SELECT B.* FROM
(
    SELECT enabled,accountid,name,platform,client_group,from_date
    FROM sales_rep WHERE account_id=1 AND state='enabled'
    ORDER BY enabled,accountid,name,platform,client_group,from_date
) A LEFT JOIN
(
    SELECT * FROM sales_rep WHERE account_id=1 AND state='enabled'
    AND clients >= 1 OR revenue >= 100
) B USING (enabled,accountid,name,platform,client_group,from_date)
WHERE B.enabled IS NOT NULL;

Give it a Try !!!


How to design databases? Describe the process in steps, please. What tools do you use for it?

Start with "database design" on Wikipedia.

I'd also recommend using Object Role Modelling to capture your conceptual model

Otherwise, your question is far too broad.

What you don't do is start knocking out some CREATE TABLE statements, or design as go along writing client side code


Dependent Transaction in separate Database-Connections I am quite new to database and I am in the following situation.

I have two tables defining a 1:n relation, let's say Company and Employee which I access in two separated database connections in postgres.

I am now writing a new company and some of its employees to the respective tables, in two separate transactions.
I then commit the company transaction and then the employee transaction.
But what should I do if the employee transaction failed, AFAIK I can't unroll the committed company transaction?

I have the feeling that my approach of handling dependent transaction in separate Database-Connections is not from the book.

If you really need to split data in multiple databases, you will need a PREPARED TRANSACTION[1] (A.K.A Two fase commit). Your process will be much simpler if you put all tables in the same database.

If you want, you can map the tables of one database into the another using a FDW[2] like postgres_fdw[3]. FDW can map a remote table to a postgres or another database (like oracle, mysql, mongo, etc).

If you provide more details, perhaps I can help you more.

[1] https://www.postgresql.org/docs/current/static/sql-prepare-transaction.html

[2] https://wiki.postgresql.org/wiki/Foreign_data_wrappers

[3] https://www.postgresql.org/docs/current/static/postgres-fdw.html


Select data at row number in warning When running show warnings in mysql, sometimes the results give a row number, such as "Warning | 1265 | Data truncated for column 'identifier' at row 12343".

I'm interested in doing something like:

select identifier from tablename where rownum = 12343

but of course this fails because there is no column named rownum. The table does not have an auto_increment column. I tried:

alter table tablename add column rownum int auto_increment unique first

but when I ran the select query again, the result did not seem to match the warning (it was a 9-character value in a varchar(12) column).

What's the best way to see the data referenced by mysql warnings? In this question I'm not so much asking about why the error is occurring, but am asking about how to view the data that is triggering the warning.

LIMIT 12342,3 -- to get the row and the adjacent rows.  No need to worry about the PRIMARY KEY, etc. the query will probably be in the right order.


Get maximum, minimum values across columns I want to get the minimum and maximum across multiple columns in a row. I found various working queries using CROSS APPLY, UPIVOT and VALUES, e.g. like this one:

select column1, column2, code1,
       (qty2), (qty3), (qty4), (qty5),
       MinVal, MaxVal
from dummydata
    cross apply (select MIN(NULLIF (d ,0)) MinVal,
                        Max(d) MaxVal
                 FROM (VALUES (qty2),
                              (qty3),
                              (qty4),
                              (qty5)
                      ) AS a(d)
                )  A

These all are working in SQL Server 2005 or above, but I need a solution for SQL Server 2000 (database compatibility level 80). How can I do that?

DDL:

CREATE TABLE [dbo].[dummydata](
[column1] [nvarchar](255) NULL,
[column2] [nvarchar](255) NULL,
[code1] [nvarchar](255) NULL,
[qty2] [float] NULL,
[qty3] [float] NULL,
.......
[qty20] [float] NULL)

Query output:

It's not going to be pretty (and I have no 2000 installation to test) but this should work.

The pk is the PRIMARY KEY of the table (or a column with UNIQUE NOT NULL constraints):

SELECT d.column1, d.column2, d.code1,
       d.qty2, d.qty3, d.qty4, d.qty5, ..., d.qty20,
       m.MinVal, m.MaxVal
FROM
    dummydata AS d
  JOIN
    ( SELECT q.pk,
             MIN(NULLIF(q.qty, 0)) AS MinVal,
             MAX(q.qty) AS MaxVal
      FROM
        ( SELECT pk, qty2 AS qty FROM dummydata UNION ALL
          SELECT pk, qty3 FROM dummydata UNION ALL
          SELECT pk, qty4 FROM dummydata UNION ALL
          SELECT pk, qty5 FROM dummydata UNION ALL
          ...
          SELECT pk, qty20
        ) AS q
      GROUP BY pk
    ) AS m
    ON m.pk = d.pk ;

Alternative is to use correlated subqueries in the SELECT list:

SELECT d.column1, d.column2, d.code1,
       d.qty2, d.qty3, d.qty4, d.qty5, ..., d.qty20,
       ( SELECT MIN(NULLIF(q.qty, 0))
         FROM
           ( SELECT d.qty2 AS qty UNION ALL
             SELECT d.qty3 UNION ALL
             ...
             SELECT d.qty20
           ) AS q
       ) AS MinVal,
       ( SELECT MAX(q.qty)
         FROM
           ( SELECT d.qty2 AS qty UNION ALL
             SELECT d.qty3 UNION ALL
             ...
             SELECT d.qty20
           ) AS q
       ) AS MaxVal
FROM
    dummydata AS d ;

Tested in rextester.com


Has the ID column configured as PK, NN and AI, be the first column on my table definition? I have a .csv file with a bunch of data which I am using for load all the data into a DB table. The file lacks the ID column (because that's is an internal column managed through our software and they don't care about it).

To avoid issues with the data my "solution" was to move the column id to a position where doesn't interfere with the data from the file and that's right after ActiveFlag column or just at the end.

Check the following image for a graphical representation of the table:

Currently the id is my first column and I haven't any problem but I am trying to move to the end because the issue I am having with the data but I got the following error:

Operation failed:
Executing:
ALTER TABLE `sedb`.`volume_pricing_agreement`
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT AFTER `ActiveFlag`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`AgreementNumber`,`CustomerSiteID`, `CFProgramLevelID`, `Source`, `AgreementTypeID`, `id`);

ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key

I don't know if the column has to be the first one on the table definition and if there is anyway to let it be wherever I want to or the solution maybe would be import the data by skipping the id column (supposing it's the first one).

I was checking the syntax here but I don't have a clue in how to skip a column during load:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

This is the output of the command SHOW CREATE TABLE sedb.volume_pricing_agreement;:

volume_pricing_agreement, CREATE TABLE `volume_pricing_agreement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `AgreementNumber` int(11) DEFAULT NULL,
  `AgreementName` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
  `CustomerSiteID` int(11) NOT NULL,
  `CFProgramLevelID` int(11) DEFAULT NULL,
  `Discount` decimal(5,4) NOT NULL DEFAULT '0.0000',
  `Source` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `ActiveFlag` int(1) NOT NULL,
  `AgreementTypeID` int(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `AgreementNumber` (`AgreementNumber`),
  KEY `CustomerSiteID` (`CustomerSiteID`),
  KEY `ActiveFlag` (`ActiveFlag`)
) ENGINE=InnoDB AUTO_INCREMENT=2763 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Any help? How should I deal with this?

No.  The order of columns in a table definition does not matter, with one old exception.  In older versions the "first" TIMESTAMP column had a special default DEFAULT value.

It is not required to have an AUTO_INCREMENT.  However it is (essentially) required that you have a PRIMARY KEY.  It smells like AgreementNumber would be such?  Keep in mind that a PK must be UNIQUE (a MySQL requirement).

As a side note, an A_I does not have to be the PK.  The only requirement is that it be the first column in some index.  And remember that a PK is a unique key is a key.

But, that is not your 'real' problem.  That is, you need to do LOAD DATA without the id being provided.

Plan A:  Load into another table, then copy it over.  When copying the data over, specify all the columns except the id; A_I will take care of the rest.

Plan B:  Use LOAD DATA, but specify all the columns except id.  Again A_I will take care of defaulting it correctly.

Other tips, while I have you on the line:

int(1) is a 4-byte number; the (1) means nothing.  Perhaps you want a 1-byte TINYINT?
Use INT UNSIGNED where appropriate
Use datatypes smaller than INT where appropriate; look up MEDIUMINT, and again make it MEDIUMINT UNSIGNED if appropriate.
Use NOT NULL when appropriate.
Don't index flags; they won't be used.
Learn about "composite" indexes.

More

If you choose to have a 6-column UNIQUE key that includes some VARCHARs, having an A_I is probably better than making that the PK.

A UNIQUE KEY is two things:  An index, and a uniqueness constraint.  You can use it for either or both.

A PK is a UNIQUE KEY that identifies the row.  It also is "clustered" with the data, making lookups more efficient than with a "secondary key" (any non-PK).

When the 'cardinality' of an indexed column is very low, such as for a flag, the Optimizer will dynamically look at the value you provide and see if less than 20% of the table has that value.  In this case, it will use the index; else it will not.  The "20%" varies with the phase of the moon.  The reason for eschewing the index is that using the index involves bouncing back and forth between the index's BTree and the data's BTree via the PK.  This can be slower than simply scanning the data, tossing 80% of the rows.  A secondary key is a BTree containing the indexed column(s) plus a copy of the PK.


Is there a way to generate table create script in TSQL? Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a table name and returns a string that contains the create script for the given table?

Now let me describe the situation I'm facing, as there may be a different way to approach this. I have an instance with several dozen databases. These database all have the same schema, all the same tables, index and so on. They were created as a part of a third party software installation. I need to have a way to work with them so that I can aggregate data from them in ad-hoc manner. Nice people at dba.se have already helped me here How to create a trigger in a different database?

Currently I need to find a way to make a select from a table across all the databases. I have recorded all the database names into a table called Databasees and I wrote the following script to execute a select statement on all of them:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) =
  N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'

DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0

DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int

SELECT TOP 1 @DatabaseNameToHandle = Name,
@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No

WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN

  DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
  EXEC @sql @statement

  SET @LastDatabaseID = @DatabaseIDToHandle
  SET @DatabaseIDToHandle = NULL

  SELECT TOP 1 @DatabaseNameToHandle = Name,
  @DatabaseIDToHandle = Database_Ref_No
  FROM Databasees
  WHERE Database_Ref_No > @LastDatabaseID
  ORDER BY Database_Ref_No
END

select * from #tmp
DROP TABLE #tmp

However the script above fails with the following message:

  An explicit value for the identity column in table '#tmp' can only be
  specified when a column list is used and IDENTITY_INSERT is ON.

Adding this:

SET IDENTITY_INSERT #tmp ON

does not help, since, I can't specify the column list and keep it generic.

In SQL there is no way to switch the identity on a given table off. You can only drop a column and add a column, which, obviously changes the column order. And if the column order changes, you, again, need to specify the column list, that would be different depending on the table you query.

So I was thinking if I could get the create table scrip in my T-SQL code, I could manipulate it with string manipulation expressions to remove the identity column and also add a column for the Database name to the result set.

Can anyone think of a relatively easy way to achieve what I want?

Back in 2007, I asked for an easy way to generate a CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.
However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha:
CREATE TABLE dbo.whatcha
(
  id INT IDENTITY(1,1),
  x VARCHAR(MAX),
  b DECIMAL(10,2),
  y SYSNAME
);

The following script uses the new sys.dm_exec_describe_first_results_set dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No entirely :-)).
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
  -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
  INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
  FROM @dbs;

SET @sql += N'
  SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

The resulting PRINT output:
CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
  INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
  INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
  SELECT id,x,b,y FROM #tmp;

When you are confident it's doing what you expect, just uncomment the EXEC.
(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)


How to Concat Datetime without Losing Second Precision What is best way to Concat on datetime column with other fields, without losing/truncate millisecond precision?

Example: We have a column in datetime precision.

1988-02-27 00:00:04.000

We are creating rowhash on CONCAT

select hashbytes('SHA2_256',concat(SalesRecord,SalesDatetime))

The problem when doing Concat, we are losing second values.
You can test here,

create table dbo.SalesOrder
(
    SalesOrderId int primary key,
    SalesRecord varchar(255),
    SalesDatetime datetime
)
select concat(SalesRecord,SalesDateTime) from dbo.SalesOrder
select concat('ABC ',[SalesDateTime]) from dbo.SalesOrder

ABC Feb 27 1988 12:00AM

What is the optimal way to solve this?

Background: Rowhash was causing duplicates by removing seconds, pipe deliminator | not working either.

The following was not working:

declare @testdate datetime = '1988-02-27 00:00:04.000'
select concat('ABC',convert(binary(16),@testdate))
select concat('ABC',convert(varchar(25),@testdate))

You are losing seconds because the default format for converting datetime to string is the same as:

SELECT CONVERT(char(19), GETDATE(), 0);

Which outputs (for me, right now):

Oct 30 2018  2:02PM

You can try something more like this:

SELECT CONCAT
       (
         'ABC ',
         CONVERT(char(19), GETDATE(), 25)
       );

Which outputs (again, for me, right now):

ABC 2018-10-30 14:02:55

If second aren't enough, just change char(19) to char(23). If that's still not enough, you can consider changing that column to datetime2 to get more precision.


Is there a well-known name for this "poor man's ref. integrity" schema design pattern? Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin down the term (if any exists) for this kind of thing:

Fruit (id, farm)

Apple (fruit_id, color)
    [fruit_id => Fruit.id]

Banana (fruit_id, length)
    [fruit_id => Fruit.id]

Orange (fruit_id, is_seedless)
    [fruit_id => Fruit.id]

FruitPack (id, destination)

FruitPackFruits (fruitpack_id, fruit_id, fruit_type)
    [fruit_id => Fruit.id, fruit_type => VARCHAR]

Where fruit_type would be a varchar column filled with values like "Apple, Banana, Orange, Cherry".  It's some kind of "poor man's referential integrity".  Obviously, one the failures of this kind of design is being able to insert values that don't resolve out to a useful join (ie: there are no cherries to speak of here).

Here's another example of such a pattern: A single "log (id, table_name, record_id, timestamp)" table that acts as a sort of tracker for modification-times in various other tables.  Strictly speaking, it's got no ref integrity, but, the (table_name, record_id) part is supposed to refer to some record in another table, requiring a join to actually get the full data.

I'm going to take for granted that the schema is a sufficient caricature of some sort of collection of groups of items for the people here.

The question is: What's this kind of "poor man's referential integrity" called?

I'm not trying to learn about referential integrity. I want to identify this poor design's name and look further into the "let's design a database schema" aspects (ex: pros, cons, opinions, teachings, etc) that have to do with this commonly seen disaster of a schema.

Your design looks a bit like the "supertype/subtype" pattern. Search for that and for "table inheritance". It needs quite a lot of work to be able to enforce integrity constraints though.

You are missing a generic Fruit table (that's the "supertype") and a FruitType table to store the alllowed fruit types:

FruitType
    fruit_type PK

Fruit
    fruit_type PK, FK -> FruitType (fruit_type)
    fruit_id   PK

Then the 3 (or 4 or more) tables would be (the "subtype" tables):

Apple
    fruit_type
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Apple')

Banana
    fruit_type PK
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Banana')

Orange
    fruit_type PK
    fruit_id PK
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)
    CHECK (fruit_type = 'Orange')

And any other table can reference the Fruit table:

FruitPack
    fruitpack_id PK
    destination

FruitPackFruits
    fruitpack_id FK -> FruitPack (fruitpack_id)
    fruit_id
    fruit_type
    (fruit_type, fruit_id) FK -> Fruit (fruit_type, fruit_id)

It doesn't look very nice and one column in every "fruit" table seems redundant as it has one and only one allowed value. And every time you need to add a new fruit (say Cherry), you have to add a row in the table FruitType and a new table (Cherry), similar to the other ones. So, it works better if your design is more or less stable. If you find that you may need to add a new "fruit" every few days or if you have a thousand (or more!) different fruits, it's not the best way.

On the other hand, it enforces integrity and you can't insert cherries into the Bananas or oranges into the Apples.


Do I use INSERT w/ ON DUPLICATE KEY or INSERT/UPDATE separately? Is it better to use

$Query = "INSERT INTO table (col1, col2) VALUES (:col1, :col2)
ON DUPLICATE KEY UPDATE col1 = VALUES(:col1), col2 = VALUES (:col2);"
    . . . .
$stmt->execute();

for a Submit and Update Buttons

Or is it better to use different INSERT/UPDATE Query for each button?

##SUBMIT_BUTTON
$Query = "INSERT INTO table (col1, col2) VALUES (:col1, :col2);"
    . . . .
$stmt->execute();

##UPDATE_BUTTON
$Query = "UPDATE table SET col1 = :col1, col2 = :col2;"
    . . . .
$stmt->execute();

Depending on Performance while having UNIQUE KEY on col1.

Simplified: Does

INSERT INTO . . . ON DUPLICATE KEY UPDATE . . .

Use an Extra step If I know that the INSERTed ROW is Duplicated, But instead of using the Traditional

UPDATE . . . SET . . . WHERE . . .

I used the INSERT with ON DUPLICATE KEY UPDATE?

Is the Performance affected Even if I had the correct INDEXES?

Your question mixes two things.

On the one hand, you have two parts to the UI, wherein the user decides, perhaps incorrectly, whether the action is just an insert or just a delete.

On the the other hand, you could have a single button, thereby simplifying the UI, and the button does IODKU under the covers.

But...  Maybe you want the user to be reprimanded when he asks to INSERT something that is already in the table?  (Etc)

The real goal should be to make the UI simple, concise, obvious, etc, to the user.  The amount of work to achieve the requested task is so insignificant that you should not be considered in the equation.  It may take the user 10 seconds to read, then press, the appropriate button, but but only 10 milliseconds to perform the action.  That's 1000:1 !

Anyway, back to your question...  IODKU has to check all UNIQUE (including PRIMARY) keys mentioned, then decide to INSERT or what row(s) to UPDATE.  (Yes, multiple mentioned unique keys can lead to multiple rows updated.  By why mention more than one unique key?)

An INSERT or a UPDATE is inherently faster than IODKU.  But it is probably sub-millisecond.  On the other hand, it will take seconds to report an error to the user and wait for him to deal with it -- if he presses INSERT when he should press UPDATE.

So, I go back to the important principle -- design the UI right.


Proper Shutdown of SQL Servers in a Cluster I was just wondering what the best approach should be for shutting down an Active/Active Cluster where both nodes need to be shutdown for disk maintenance.

I was thinking of this process:

Open Failover Cluster Manager
Right-click on the cluster name, select ‘More Actions’, then “Shut Down Cluster…”
When prompted if you are sure you want to shut down the cluster, click “Yes”
Shutdown both Nodes
Do Disk Maintenance
Power on both Nodes
Fail Cluster resources back to their preferred node

Does anyone see any issues with that? How do you ensure the Cluster Resources come back up on the correct node considering they'll come up at a different time?

Does anyone see any issues with that?

You are correct in the steps but your approach is way too much for a little work.

For disk maintenance, why would you shutdown the entire cluster ?

Just suspend that disk (which requires maintenance) and then once it is done resume the node.

Basically, SHUT DOWN CLUSTER will stop all the roles and services on all the nodes of the cluster. The Cluster UI will ensure that all the roles and services are shutdown gracefully.

Seems like you have posted the same question at SQLServerCentral.com

Refer to How to Properly Shutdown a Failover Cluster or a Node


Add data from one table to another when data in two other specific fields are identical I'm working on populating a cadastral table with codes from a database that comprises historic information about farms. Since both tables contain thousands of rows I'm looking for ways to automate the process. The two tables don't always have a direct correlation but there are ways to update the cadastral table automatically.

The database is PostgreSQL 9.4

The relevant fields in table 1 are:

county_name
farm_name
new_field_for_fk

For the second:

county_name
farm_name
pk

Neither the county_name nor the farm_name are unique identifiers, but for 99% of the time the combination of the two is, and I want to update new_field_for_fk with the data in pk where county_name and farm_name are identical. Any thoughts?

You could try an UPDATE with a FROM clause, from your description of the problem.

Something like:

UPDATE table1 t1
SET new_field_for_fk = t2.pk
FROM table2 t2
WHERE t1.county_name = t2.county_name
  AND t1.farm_name = t2.farm_name ;

Should update all rows based on the matching of the two keys across the tables.


MySQL: Does the default value checked by the `CHECK` constraint? Say I have the following schema, would the default value https://sample.com be checked by the CHECK (Website LIKE "https://%")?
CREATE TABLE Company (
    AccountID varchar(25),
    Name varchar(20)        NOT NULL,
    Website varchar(255)    DEFAULT 'https://sample.com',
    Categories varchar(255) NOT NULL
                            DEFAULT '[]',
    PRIMARY KEY(AccountID),

    CHECK                   (CHAR_LENGTH(AccountID) BETWEEN 4 AND 25),
    CHECK                   (CHAR_LENGTH(Name) BETWEEN 2 AND 20),
    CHECK                   (Website LIKE "https://%")
);

Checking default value doesn't make sense, as you have declared the static value which must be correct only. But the values which are going to be inserted manually are not checked using CHECK constrainst.
CREATE TABLE Company (
    AccountID varchar(25),
    Name varchar(20)        NOT NULL,
    Website varchar(255)    DEFAULT 'https://sample.com',
    Categories varchar(255) NOT NULL
                            DEFAULT '[]',
    PRIMARY KEY(AccountID),

    CHECK                   (CHAR_LENGTH(AccountID) BETWEEN 4 AND 25),
    CHECK                   (CHAR_LENGTH(Name) BETWEEN 2 AND 20),
    CHECK                   (Website LIKE "https://%")
);

See here : the value like  hps://sdfs.com , asdfas://sdfs.com are not supposed to insert but they are inserted successfully.
insert into Company(ACCOUNTID , Name )  values ('12','asdf');
insert into Company(ACCOUNTID , Name , Website)  values ('13','asdf',"hps://sdfs.com");
insert into Company(ACCOUNTID , Name , Website)  values ('1','asdf4',"asdfas://sdfs.com");

select * from Company ;

Output :

# AccountID, Name, Website, Categories
1, asdf4, asdfas://sdfs.com, []
12, asdf, https://sample.com, []
13, asdf, hps://sdfs.com, []

Apart from using CHECK constraint I would suggest to use Triggers for validating this values or using JAVASCRIPT to valid this values.


PostgreSQL Query output to JSON I would like to get the output of a particular Query from the postgresql server having joins of a multiple Tables with one to many relations in the json format to a particular file.
The Query output should be an actual json tree like below and not a flat join of master + child :-
Master Record 1 (master field 1, master field 2)
              { array
                             Child Record 1 {child field 1, child field 2, ….)
                             Child Record 2 { child field 1, child field 2, ….)
               }
Master Record 2 (....)
                Child Record 1
                Child Record 2

Please let me know how to achieve this in the Postgresql 11.
SQL Script
CREATE TABLE public.book
(
    pk_book_id integer NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    isbn character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT book_pkey PRIMARY KEY (pk_book_id)
);

CREATE TABLE public.author
(
    pk_author_id integer NOT NULL,
    fullname character varying(255) COLLATE pg_catalog."default",
    mobileno character(10) COLLATE pg_catalog."default",
    fk_book_id integer,
    CONSTRAINT author_pkey PRIMARY KEY (pk_author_id),
    CONSTRAINT author_fk_book_id_fkey FOREIGN KEY (fk_book_id)
        REFERENCES public.book (pk_book_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);

INSERT INTO public.author(
    pk_author_id, fullname, mobileno, fk_book_id),
    (100, 'John Matthew', '98927828', 1),
    (101, 'Mark Knight', '99875528', 1),
    (103, 'Lara Croft', '99872628', 2),
    (104, 'Brad Show', '567982', 2)

INSERT INTO public.book(
    pk_book_id, name, isbn)
    VALUES (1, 'Hot Deals', 'ISBN89644'),
    (2, 'Summer Tales', 'ISBN405987');

It's a bit hard to follow your sample, but something along the lines should do it:
select to_jsonb(main)||ch.data
from main_table main
  join (
     select main_id, jsonb_agg(to_jsonb(child)) as data
     from child_table child
     group by main_id
  ) ch on ch.main_id = main.id

If you want everything as one gigantic JSON array, use select jsonb_agg(to_jsonb(main)||ch.data) in the outer select.


How do I create a SQL Agent Alert on a custom error message in SQL Server 2012? I want operators to be notified when I throw a custom error message. My understanding is that I need to add a message to sys.messages, then I can either RAISERROR or THROW that error ID. Creating an alert on the message ID and setting it to send to operators should create those notifications.

I've created a SQL Agent alert on the message ID of 50005, and am using the following T-SQL to create the message:

EXEC sp_addmessage @msgnum = 50005,
          @severity = 16,
          @msgtext = N'%s';
GO

Then, if I execute this:

RAISERROR (50005, -- Message id.
       16, -- Severity,
       1, -- State,
       N'My custom message');
THROW 50005, 'My custom message', 1;
GO

I get the following output as expected from both RAISERROR and THROW:

  Msg 50005, Level 16, State 1, Line 68
  My custom message

When I view the history of the alert though, it shows that it has not been triggered, and the operator does not receive an email update.

My agent alert looks like this:

Scripting the alert generates the following:

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Alert DBA on custom errors',
    @message_id=50005,
    @severity=0,
    @enabled=1,
    @delay_between_responses=0,
    @include_event_description_in=1,
    @database_name=N'',
    @notification_message=N'',
    @event_description_keyword=N'',
    @performance_condition=N'',
    @wmi_namespace=N'',
    @wmi_query=N'',
    @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert DBA on custom errors', @operator_name=N'My Operator', @notification_method = 1
GO

For your convenience, if you try to recreate my issue this will help you drop from sys.messages:

sp_dropmessage @msgnum = 50005;
GO

Please refer to Create a User-Defined Event

  By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.

So your message should be

EXEC sp_addmessage @msgnum = 50005,
      @severity = 1,  -- Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
      @msgtext = N'%s';
GO

When you use THROW, it defaults to severity 16 which does not fire the alert.

RAISERROR (50005, -- Message id.
       1, -- Severity,
       1, -- State,
       N'My custom message') with log;

USE [msdb]
GO

/****** Object:  Alert [Alert DBA on custom errors]    Script Date: 1/22/2016 3:17:22 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N'Alert DBA on custom errors',
        @message_id=50005,
        @severity=0,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=0,
        @database_name=N'AdventureWorks2012_test',
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO

Also, make sure you use @delay_between_responses to a sensible value so that your inbox is not flooded with emails.

Also, refer to my answer for Monitoring free space in SQL Server data files using user defined message and alerts.


Collapsing a column to yes/no on query I have a table with two columns name and value.

There are many rows with the same name, but with different values.

I want to create a function that returns a table with 2 columns.  The first column is the name and the second is a character, either yes or no, which refers to whether a name has a specific value or not.

If a name  has the specific value at least at one of its values to return yes and if it does not have the specific value to return no.

With the answers so far, I return the same name many times with the corresponding values like

('BOB'-'NO','BOB'-'NO','BOB'-'NO','BOB'-'NO','BOB'-'YES','JAME'-'NO','JAME'-'NO','JAME'-'NO','JAME'-'NO','JAME'-'NO') for example.

I want to return a table like that:

('BOB'-'YES','JAME'-'NO')

because bob has a specific value that I want at least one time at his values and JAME has not the specific value in a row.

Here are three simple methods to do it,

Returning bool: Just use a simple CASE statement.

SELECT name, CASE WHEN value = 'foo' THEN true ELSE false END
FROM table;

Returning bool compact: You can actually make it more terse, so long as value is NOT NULL. That CASE statement is essentially equal to

SELECT name, value = 'foo'
FROM table;

If value is ever null though you'll get null rather than false.
Returning text: If you insist on yes and no instead (I wouldn't do that), you can use strings instead of the special-made boolean type (there is no more compact syntax to write this).

SELECT name, CASE WHEN value = 'foo' THEN 'yes' ELSE 'no' END
FROM table;


MySQL 5.5.58 Installing Multiple Instances on RHEL7 I am Avinash.

I am willing to install an additional MySQL instance on a UAT server for testing purposes. I am using MySQL Community Server 5.5.58 installed on RHEL7.2.

I am going through the MySQL Docs https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html , which tells me that I would require certain unique operating parameters such port, socket, pid, datadir and log files related parameters. To achieve better performance, it also recommends to use different tmpdir. This is well understood.

I can either use different binaries or the same binary for this purpose.

If I am using same binary, I'll have to use mysqld_multi and use the same config file. If I use mysqld_multi, I'll have to put different sections for mysqld daemon such as mysqld1, mysqld2 and so on. All using unique values for above mentioned operating parameters.

Please help me out here. Am I correct about mysqld_multi?

My second question is about bind-address variable.
https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address
Here, it explains that "The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces."

My question is that will I need an additional NIC and an additional IP address for the second MySQL instance?

For the existing instance, I have disabled the bind-address option.
Do I need to add the bind-addresses for both the instances.

Also, the second instance will be used only by me (for now).

Thanking you.

why dnt you start new instance with different cnf file (keeping diffrent PORT and diffrent socket file)

for example :

cd /opt/mysql_installation/u01/mysql_5.7.17/bin/
./bin/mysqld_safe --defaults-file=/opt/mysql_installation/my.cnf &


View the role assigned to each user (Report Server) Is there anyway I could list down the roles assigned to each user on the different folders on ReportServer Database and in that SQL statement I should be able to declare something like
    @User_Name of the reporting user

Found My Answer from a Blog Almost similar :) !!!

DECLARE @UserName VARCHAR(200) = 'UserName'

SELECT Rol.RoleName,Us.UserName ,Cat.PATH,Cat.[Name] ReportName,
CASE Cat.Type WHEN 1 THEN 'Folder' WHEN 2 THEN 'Report'
WHEN 3 THEN 'Resource' WHEN 4 THEN 'Linked Report'
WHEN 3 THEN 'Data Source' ELSE '' END AS CatalogType
,Cat.Description
FROM Catalog Cat     --Parent
INNER JOIN Policies Pol
ON Cat.PolicyID = Pol.PolicyID
INNER JOIN PolicyUserRole PUR
ON Pol.PolicyID = PUR.PolicyID
INNER JOIN Users Us
ON PUR.UserID = Us.UserID
INNER JOIN Roles Rol
ON PUR.RoleID = Rol.RoleID
WHERE   Cat.Type in (1,2)
AND  ( Us.UserName LIKE @UserName  )
ORDER BY Cat.PATH


Registered Server list and groups disappeared from SQL Server 2012 Management Studio I recently installed SQL Server 2012. Due to some issue, the databases services did not install successfully, but Management Studio did, which was OK to start with, as I usually access remote servers.

I imported my groups of servers and worked happily.

Yesterday, I restarted my PC, and lo and behold, the list of groups and server is not there at all - all I have is an empty Object Explorer without any way to access my saved server and connect. See Screenshot below.

Through File > Connect Object Explorer I get the dialog where I can open what is listed there  but that's a pain, as I had many servers listed there and it was very user-friednly to have them listed and grouped nicely.

So my question is how can I get them listed there again.

Thanks!

The list you're talking about wouldn't be present in Object Explorer (unless you connected to all of them manually every time you started Management Studio). Your active connections will disappear from Object Explorer when you close down SSMS or reboot, so you can't persist them there (as convenient as that might be).

What you might be talking about, though, is Registered Servers, which you might have inadvertently closed before closing SSMS last (it should pop up again if it was active when you shut down SSMS or rebooted). If it's missing, no problem, you can restore this panel using View > Registered Servers or through the key combination Ctrl+Alt+G.

If your servers still don't show up there, it's possible that you lost your registered server configuration due to a roaming profile, logging in as a different user, or messing with SqlStudio.bin.


Entity set that has a relationship with itself I have this ER model that I am having difficulty understanding, since there is an entity set that has a relationship with itself. I am trying to understand two questions:

  1) Can a child be born before the parent?  2) Can a person to be a child of themselves?

For 1) I don't think this is possible, because the parentID field would be null

For 2) Since this is a many to one relationship, I don't see why this can't be true, as long as the person isn't a child of someone else.

There's nothing in the ERD to constrain the model either way. Without suitable constraints, a child could be recorded as being born before his/her parent, a person could be their own child or parent, and cycles over any number of generations could exist.

For example, assuming the ERD is implemented as:

Person (personId PK, dateOfBirth, name, gender)
ChildOf (personId PK/FK, parentId FK)

we could record the following sets of rows:

Person (1, 2000-11-01, John, Male)   ChildOf (1, 2)
Person (2, 1970-05-23, Jane, Female) ChildOf (2, 4)
Person (3, 1985-01-11, Jack, Male)   ChildOf (3, 3)
Person (4, 1950-11-01, Joan, Female) ChildOf (4, 1)

There's nothing in the ERD to prevent any constraints either. As it stands, I would have to answer "yes" or "it depends on what unstated constraints you assume or implement" to both questions. If such constraints were required, I would recommend stating them explicitly.


Mysqldump treats some table as View When doing a mysqldump, some tables are being skipped because mysqldump treats them as View rather than Table although they're 100% tables.

So for some tables it looks like

-- Retrieving table structure for table services...
-- Sending SELECT query...
-- Retrieving rows...

and for some ones:

-- Retrieving view structure for table users...
-- It's base table, skipped

I saw this issue was already mentioned a few times however there's no solution.

  SHOW CREATE TABLE users;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8915 DEFAULT CHARSET=utf8

  SHOW TABLE STATUS LIKE 'users' \G;

*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6638
 Avg_row_length: 713
    Data_length: 4734976
Max_data_length: 0
   Index_length: 2899968
      Data_free: 469762048
 Auto_increment: 8923
    Create_time: 2015-09-04 10:00:26
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

MySQL 5.5.41-0ubuntu0.14.04.1

So for some tables it looks like

Well, it should be that way for all the tables.

That message is generated by the --verbose option, and you should see it on all the tables, when mysqldump makes its second pass through the database.

Because views can't reference nonexistent objects, mysqldump iterates through all of the tables in each database, twice, if views are encountered.  On the first pass, it creates a dummy table for views so that, if it turns out a view references another view, the dummy table stands in place of the target view so that the referencing view can be valid while the restoration of the backup takes place.  If views were encountered, it makes a second pass, dropping each dummy table and replacing it with the actual view.

It does this by calling mysql_list_tables() in the c-client API, which sends "show tables" to the server (after switching the current database).  Since this contains a list of base tables and views, mysqldump has to iterate through each one and decide what the current identifier represents -- a table, in which case, it does nothing because the table was already dumped -- or a view, in which case it drops the dummy table and creates a proper view.  (Why it doesn't discover this information more intelligently is probably related to backwards compatibility with ancient versions of MySQL Server).

In short, this is normal.  The --verbose messages are just telling you something you already knew.  When you see these, mysqldump is iterating through the list of all tables in the database, checking whether they are views or not (for the second time).

The base tables are dumped and dummy view "placeholder" tables are created during the first pass; the base tables are skipped, placeholder tables dropped, and view definitions are dumped during the second pass.

The message -- Retrieving view structure for table users... is poorly worded, since in fact mysqldump is, at that point, checking whether the table is a view, by requesting its CREATE statement from the server, and the message appears to have given the impression in the original question that the table was first mischaracterized as a view, then omitted from the dump because it was not a view. This was an inaccurate perception, as the table had already been dumped during the first pass.


Autogrowth and Initial Size with T-Log Please help me in understanding the below issue :

SQL Server Version 2014 with AOAGs. We have separate Data , Log , temp DB , SQLFS Drives individually.

Our Backup Strategy is as below:

Weekly Full Backup
Daily Differential
Every 15 Minutes Log backup

My Total Log drive space is 300 GB.
When I checked the DBCC SQLPERF(LOGSPACE) only tempdb is highest consumer.

AutoGrowth Settings :

NOTE : Currently I have only 20% free space left. Autogrowth is in '%'.

Also we have Filestream data enabled . Will Filestream have separate Log file ?

Result of T-Log utilization:

Please help me with below concerns as I am new to SQL Server DBA.

In what case I should go for increasing my Log drive space . For ex: E:\ is for my SQLLOG, in what case I should expand or increase my drive. What is the end point I should consider before expanding drive ?
Sometimes DBCC Shrink does not free up much space ! what could be the valid reason.
And I read some articles where it is mentioned not to use Autogrowth in '%' instead we should use in 'MB's'. I have mentioned my case above . Kindly correct me if I am in good lines or false affect. Please correct me and help me on going for good considerations.

Thanks

I think you can calculate each 15 min time slot before doing transaction backup, because after transaction backup, the previous transaction logs will be rewriteable, and that will be the maximum of the transaction log file size.

DBCC Shrinkfile sometimes could not work well because there are some transaction not write down to database already (transaction will be wrote to log file first and    then write to database), you can wait a while and DBCC again.


MIN / MAX values with Date I am trying to get the MAX and MIN values for the current month and the dates for these values.

I am getting the MAX and MIN values but I am not getting the dates for these values. Instead I get the first day of the month.

Here is my query:

SELECT  DATE(CREATED) AS date
      , MIN(ROUND(AMBIENT_TEMPERATURE,1)) AS min_temp
      , MAX(ROUND(AMBIENT_TEMPERATURE,1)) AS max_temp
FROM WEATHER_MEASUREMENT
WHERE (MONTH(DATE(CREATED)) = Month(NOW()));

And this is what I get:

date        min_temp  max_temp
----------  --------  --------
2016-05-01  17.8      43.2

I would like to have a date for the max_temp and a date for the min_temp.

How can I do it?

Your query is invalid SQL, but MySQL accepts it with the default settings. It will give you a random result. You can change this behavior by adding 'ONLY_FULL_GROUP_BY' to sql_mode.

EDIT: Similar to @MickyT's solution but using a join instead:

SELECT M.*
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);

It is not clear what time that should be used in case of a draw, the query outputs all times where a min or max temp occurred. Assuming that you would like the first occurrence, something like:

SELECT MIN(M.CREATED), M.AMBIENT_TEMPERATURE
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);
GROUP BY M.AMBIENT_TEMPERATURE

can be used.

I left out the predicate for the current month, but that is easily added. Since MySQL does not support LATERAL join, it is probably better to add that predicate to the derived table as well. I.e.

SELECT MIN(M.CREATED), M.AMBIENT_TEMPERATURE
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   WHERE (MONTH(DATE(CREATED)) = Month(NOW()))
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);
WHERE (MONTH(DATE(CREATED)) = Month(NOW()))
GROUP BY M.AMBIENT_TEMPERATURE

As @RolandoMySQLDBA points out the WHERE clause can be rewritten in a index friendlier way.


Wildcard subnet mask for IPv6 To allow any host to connect to PostgreSQL we can use the IPv4 wildcard subnet mask: 0.0.0.0/0

I need this to setup my PostgreSQL's pg_hba.conf file correctly.

What is the IPv6 equivalent?


Save output of a Mongo Command to a Variable bash script I have been writing a script to find and kill slow mongodb queries, how can I store output of db.currentOp() in a variable?
current I am using this but failing
#! /bin/bash

out=""
mongo << EOF

out=db.currentOp();
EOF
echo"----------------------------"
echo"$out"

Updated Script
#! /bin/bash

out=$(mongo --eval 'db.currentOp({op:"query"})')
echo"-----------------------"
echo"$out"

Output
./kill_slow_mongo_queries.sh: line 4: echo-----------------------: command not found
./kill_slow_mongo_queries.sh: line 5: echoMongoDB shell version v3.6.8
connecting to: mongodb://127.0.0.1:27017
Implicit session: session { "id" : UUID("ce96f3ee-f5e1-49b5-aeef-b5766328dda3") }
MongoDB server version: 3.6.8
{ "inprog" : [ ], "ok" : 1 }: No such file or directory

Mongo -command have multiple parameters that are handy in this case.
'--eval' let you run commands and '--quiet' keep all clutter away
out=$(mongo --quiet --eval="db.currentOp()") ; echo $out


A good way to verify if the update has no problems? I don't know if this is a stupid question, but I'm upgrading my test instances to SQL Server 2014. I've installed SP1, after I will install Cumulative Updates.

It's working fine, but is there a way to test it? Like some queries or procedures that would fail with a bad upgrade?

From sqlserverbuilds, I can see that I have the last SP1 ( the only one for SQL2014 ).

As TheGameiswar has mentioned, the upgrade adviser is incredibly helpful for pointing out issues that can arise with the upgrade. However, it will only check for things IN the database. So if you have ad-hoc queries being ran, or worse yet sql files scattered all over the place, those will not be tested.

When I upgraded our SQL 2000 servers to 2014, we had ad-hoc queries and sql files all over the place that were being executed and we needed to test to make sure they would not break as best we could. So what I ended up doing was copying our production databases and upgrade them to 2014. After that, I started doing replay traces on production for a week to capture all queries being ran. This allowed me to replay those traces on the 2014 servers to see if there would be any issues. It helped me find a number of issues that needed to be fixed.

One very important thing to note when doing replay traces is you need to make sure the database IDs match between the production and dev environments as that's how the traces know what database to run each query on.


How to create an index used by a WHERE extractValue(…) LIKE '…'? Setup

Let's say you have a table with some XML data. For the sake of argument, say:

CREATE TABLE t_xml (xml XMLTYPE NOT NULL);
INSERT INTO t_xml VALUES ('<name>Alice</name>');
INSERT INTO t_xml VALUES ('<name>Bob</name>');
INSERT INTO t_xml VALUES ('<name>Carol</name>');
INSERT INTO t_xml VALUES ('<name>Chuck</name>');

A function index works with =

Consider the following query:

SELECT * FROM t_xml WHERE lower(extractValue(xml, '/name')) = 'alice'

If I want this query to use an index, I can create a function index:

CREATE INDEX t_xml_f ON t_xml(lower(extractValue(xml, '/name')));

With this an explain plan tells me:

SELECT STATEMENT
 TABLE ACCESS BY INDEX ROWID T_XML"
  INDEX RANGE SCAN T_XML_F

So far so good.

What index to use with LIKE?

But instead of =, what I really want is a LIKE. What index can I create so that the following query doesn't do a full table scan?

SELECT * FROM t_xml WHERE lower(extractValue(xml, '/name')) LIKE '%a%'

Now, Oracle won't use the above index with a LIKE, and this for a reason. If I wasn't using an extractValue(), I could use a text index. But I am using an extractValue().

A generic XMLIndex should do the trick:

CREATE INDEX t_xml_x ON t_xml(xml) INDEXTYPE IS XDB.XMLIndex;

Your explain plan will be:

SELECT STATEMENT
 FILTER
  TABLE ACCESS FULL T_XML
  TABLE ACCESS BY INDEX ROWID SYS15471_T_XML_X_PATH_TABLE
   INDEX RANGE SCAN SYS15471_T_XML_X_PIKEY_IX

You should be good with this, as long as your XPath expression doesn't use any of the constructs not indexed by XMLIndex.


Oracle 12C peformance issue - waiting time increased drastically Recently we upgraded oracled DB from 11G to 12C. During first week after upgrade we did not notice any performance issue. However, suddenly decrease in performance (not even gradual decrease) popped up. Here are the details observed. Request your guidance to debug the issue. Let me know if any details needed further

1. AWR report shows a sudden increase in wait time. Report was taken for 2 hours period

2. AWR  before problem:

There are some known performance issues after upgrading to 12c that are related to the new optimizer features. Try the following to see if it corrects your issue. Note that this will turn off these features for now.

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES=false scope=both;
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=0 scope=both;
ALTER SYSTEM SET "_fix_control"='12914055:Off' scope=both;


Hits to Insert Ratio and Insert to Prune Ratio I'm confused with Insert to Prune Ratio.

As I understood, for example Hits to Insert Ratio:

Hit:Insert     0.47:1

is very bad (more inserts, then hits, hence - server waste resources to insert in to QCache, but - it's rarely give results back to clients) - is this one correct?

But about next one - Insert to Prune Ratio... I'm really confused.
On same server I have this ratio:

Insrt:Prune    1.96:1

Inserts more then deletes from cache?.. But - is it bad? Or good? What is adequate value?

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 212     |
| Qcache_free_memory      | 460208  |
| Qcache_hits             | 1292474 |
| Qcache_inserts          | 2773956 |
| Qcache_lowmem_prunes    | 1417292 |
| Qcache_not_cached       | 9557843 |
| Qcache_queries_in_cache | 872     |
| Qcache_total_blocks     | 2251    |
+-------------------------+---------+
8 rows in set (0.09 sec)

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.00 sec)

Hit:Insert     0.47:1

means "every inserted entry was requested 0.5 times, before it was removed". (Yes, that's not good.)

Insert:Prune    1.96:1

means "for every second insert, I had to prune/remove one existing entry". An old rule of thumb states "a value between 10 and 20 percent is acceptable", while your value is 50%.

Another helpful number is the query cache hit rate, which is

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

in your case thats roughly 9%, meaning only 9% of the queries can be satisfied by the query cache.

Recommendations reach from "give the query cache more RAM" over "play around with query_cache_min_res_unit" to "give the query cache less RAM"... which is not a really helpful advice.

Since you have set query_cache_size=8M I would recommend to raise this value. 8 Megabytes seems very low to me.


PostgreSQL - Variables on transaction-level Do PostgreSQL have variables at transaction level? If not, is it possible to implement something like that relatively easily? Unfortunately, I am still relatively inexperienced with PostgreSQL and have only found out that you can define settings at session level.

I am currently trying to implement a historization like Hibernate has directly in PostgreSQL. A short info for which they do not know Hibernate: Hibernate has a table for revisions.  It contains an ID, the time and other information. For each table there is a "_AUD" table. Every change within the same transaction gets the same revision ID.

To implement this directly in PostgreSQL, I would need a variable that I can use within a transaction and whose value can only be used within the same transaction. My idea is that every time a transaction is historized, a function queries if an ID exists for the transaction, if not, it creates one, otherwise it returns the already created one.

I use the latest PostgreSQL version.

From today's point of view it was rather a stupid idea or bad design and I would pass this parameter directly to the database.


MySQL gives me:“Can't open and lock privilege tables: Table 'host' is read only” I am facing problem restoring a MySQL database. My primary database was MySQL 5.1 and now I am trying to copy it to MySQL 5.5. The database was backed up by using Xtrabackup.
I am using Ubuntu 12.04.3 LTS on this server, MySQL version is: 5.5.32-0ubuntu0.12.04.1-log.
I have followed all the steps to restore using Xtrabackup, this created database files, which I have copied to a tmp directory.
I have modified my.cnf to point to this tmp directory. I have changed the tmp directory permissions and changed the ownership of the files to mysql user.
drwxr-xr-x 12 mysql mysql 4096 Sep 10 10:04 base

Now when I start the MySQL server I get this error:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only

I have given a try as follows:

Even tried installing MySQL 5.1 to see if that was the issue.

tried chcon mysql_db_t to change the context but it gives me:

can't apply partial context to unlabelled file

I have used --skip-grant to get into database, but using this I can only access InnoDB tables only, MyISAM tables throw read-only error.

After --skip-grant, I have used upgrade_mysql. This throws me errors stating that many tables are read-only.

I have removed AppArmor too. And restarted too.

I have restored a different database (5.1 to 5.5) previously on Ubuntu 12.04.2 LTS without any issues.

Can some please point me in right direction? I am not sure whats wrong with permissions.

custom mysql datadir? removed apparmor and still having mysql process failing to start issues? try this. may help some of you depending on your config. worked for me:

when using a custom mysql (5.5) data dir as described above-
ie., if the abs. path to datadir (my.cnf) is
/opt/some/other/mysql

even with apparmor removed/purged, you must make certain that permissions on ALL of the folders in the mysql datadir path are perm'd correctly.  this is a mysql convention- not related to apparmor.

look at /var/lib/mysql- the default datadir (my.cnf) on most installations: perms are 755 for /var and /var/lib.

in my custom datadir example above, when I set any/all of the dirs called 'opt' or 'some' or 'other' without an execute permission for allusers (ie., 750), mysql will not start.  once i add an execute perm to allusers (ie. 755, 771, etc.), BINGO. mysql starts.


Improving the performance of a long query I have a query that i have let run for a few hours.  I am forced to kill the query because it is taking up 60% of the CPU.  Is there anything i can do to improve its performance?

select distinct(random_selection.randnum),
    random_selection.dropper_id,
    random_selection.ozip3
from random_selection
where random_selection.dropper_id is not null
and random_selection.quarter = 121
and (random_selection.dropper_id, random_selection.randnum, random_selection.quarter) in
    (select forecast_entry.dropper, forecast_entry.rand_num, production_weeks.yyq
    from forecast_entry, production_weeks
    where forecast_entry.week = production_weeks.production_week
    and production_weeks.project_cd = 'EXFC'
    and production_weeks.yyq >= 121)

union

select distinct(random_selection.randnum),
    dropper_city_brk_2.dropper_id,
    random_selection.ozip3
from random_selection, dropper_city_brk_2, dropper
where random_selection.ozip3 = dropper_city_brk_2.zip3
and dropper.dropper_id = dropper_city_brk_2.dropper_id
and dropper.active = 1
and dropper_city_brk_2.dropper_id <> 10002
and random_selection.quarter = 121
and random_selection.dropper_id is null
and (random_selection.dropper_id, random_selection.randnum, random_selection.quarter) in
    (select forecast_entry.dropper, forecast_entry.rand_num, production_weeks.yyq
    from forecast_entry, production_weeks
    where forecast_entry.week = production_weeks.production_week
    and production_weeks.project_cd = 'EXFC'
    and production_weeks.yyq >= 121)

Query explained:

the main objective is to get all of the randnum, dropper_id, and ozip3 from random_selection table that are not in the forecast_entry table and are in yyq 121 and have a project code of EXFC.  yyq is retrieved from the production_weeks table by associating week and production_week. Some dropper_id are null so we need to pull that data from the dropper_city_brk_2 table by associating ozip3 and zip3.  We dont want dropper_id that are inactive so they must have active equal 1, this is by associating the dropper table.

The performance bottleneck very likely arises from the use of the IN keyword. You should rewrite it with an EXISTS. See the accepted answer of my question on Stackoverflow for how to do it. I'm pasting the relevant part here (credit goes to Quassnoi and you have to adapt it to your SQL dialect if needed):

One of the most common things to do is replacing this query:

SELECT  *
FROM    mytable
WHERE   col IN
        (
        SELECT  othercol
        FROM    othertable
        )

with this one:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    othertable o
        WHERE   o.othercol = mo.col
        )


How does SQL Server Merge Replication maintain referential integrity? Note: This is a curiosity question about SQL Server.

I've been reading about SQL Server's Merge Replication feature, and there's one thing I just don't understand about it: How does Replication guarantee referential integrity without locking the database while replicating?

I know how msmerge_contents and msmerge_tombstone are used to enumerate changes. Let's assume row 1 of table A is listed in msmerge_contents, and has a foreign key pointing to row 2 of table B, which is also listed in msmerge_contents.

Now, both rows have been enumerated and replication has begun. What's to stop a user from creating a new row (row 3) in table B, and pointing the foreign key from row 1 of table A to this new row?

The new row will be saved in msmerge_contents as a new generation that has not been enumerated for this session and will therefore not be replicated. However, row 1 of table A will be replicated, because it belongs to the enumerated generations - and by the time replication gets to it, it may already be updated with the value of row 3 in table B!

Does anyone know how this problem is addressed, if it is addressed?

The following TechNet article does not answer the question, sadly.

How Merge Replication Tracks and Enumerates Changes

Review How Merge Replication Detects and Resolves Conflicts

A lineage column is used which contains the generation and the node that made the change.  The actual winner decision during the conflict depends upon the conflict resolver used for the article. Using the default prority-based resolver, a priority value per node should be assigned per subscriber to determine the winner for all conflicts.

With regards to referential integrity and the concept of conflict resolution in mind, the processing order with which tables (articles) are processed is your only means of guiding the conflict resolution before your child tables.  You can modify the order of the article processing in the merge publication via the @processing_order parameter for sp_addmergearticle


Is it better to populate variables using SET or SELECT? What is the better way (with regards to performance) to set a value to variable?

By SET command:

DECLARE @VarString nvarchar(max);
SET @VarString = 'john doe';
SELECT @VarString;

By SELECT command:

DECLARE @VarString nvarchar(max);
SELECT @VarString = 'john doe';
SELECT @VarString;

Don't pick one or the other strictly for performance.

Pick based on what fits your style and development needs, as is advised in the conclusion to this excellent comparison between SELECT and SET (emphasis and minor formatting added):

  Best practice suggests not to stick to one method. Depending on the
  scenario you may want to use both SET or SELECT.

  Following are few scenarios for using SET:

  If you are required to assign a single value directly to variable and no query is involved to fetch value
  NULL assignments are expected (NULL returned in result set)
  Standards are meant to be follow for any planned migration
  Non scalar results are expected and are required to be handled

  Using SELECT is efficient and flexible in the following few cases:

  Multiple variables are being populated by assigning values directly
  Multiple variables are being populated by single source (table , view)
  Less coding for assigning multiple variables
  Use this if you need to get @@ROWCOUNT and @ERROR for last statement executed


Single field instead of several fields There is a relational database with the following Users table

User
Id
Name
Age
Parents
Friends
Colleagues
...

And there are tables Parents, Friends, Colleauges, etc. Due to specifics of database this part of User info is actually a single document that is always used as whole piece

Parents
Friends
Colleagues
...

Are there any advantages to remove all this tables and use the following Users table instead

User
Id
Name
Age
People

Where people are document with a special structure, for example, JSON or XML? This database is not truely relational. Will it be faster, easier to develop and maintain, etc.?

"Are there any advantages to remove all this tables and use the
  following Users table instead"

It depends.  Are you talking about a lot of rows?  Or just a few?  How about the number of different data fields in a "People" document?  Parsing something like that (for a lot of fields) is not going to be easy or performance-friendly.  Especially if the fields are not consistent across the documents.

  "Where people are document with a special structure, for
  example, JSON or XML?  This database is not truely relational"

Given these statements (and others) it almost sounds to me like this particular instance would be better served with a (NoSQL) MongoDB solution.  Essentially, you could have a "People" database and save collections of "Parents", "Friends" and "Colleagues."  MongoDB stores documents in collections as BSON, or a binary-encoded serialization of JSON.

Here's a link to the MongoDB site which explains a little about BSON and MongoDB.  Hope this helps.


Fetch first 9 records and make one more record which will be as "Others" in MySQL I am fetching records from one table with count of one field with other field as name.
I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as "Others" with all remaining fields with count. This is something like wrapping records.
Something like below is table contents.

emp_id | designation

 1   |   software Engg.

 2   |   software Engg.

 3   |   Project Manager

not less than 10 designation.

And I want to show first 10 records as
Software Engineers  20
Project Manager     5
....
....
....
Others    50

Is there any way to make SQL Query for mysql db so that it will be fast and save time in application level where I am adding up record counts for "Others" ?

Please suggest how I can make it possible in effective way.

Suggestion 1

How about (untested)

SELECT designation, count(*)
  FROM designations
 GROUP BY designation ORDER BY COUNT(*) DESC LIMIT 9

UNION SELECT "Others" designation, COUNT(*)
        FROM designations
       WHERE designation NOT IN (
               SELECT designation
               FROM designations
               GROUP BY designation
               ORDER BY COUNT(*) DESC
               LIMIT 9 )

Suggestion 2

This one is tested. It uses MySQL-specific variables. It's efficient.

SET @n=0;

SELECT IF(row_number<10, designation,'Others') name,
       sum(c) AS occurances
FROM
  (SELECT designation, c, @n:=@n+1 row_number
   FROM
     (SELECT designation,
             count(*) c
      FROM designations
      GROUP BY designation
      ORDER BY count(*) DESC) t1) t2
GROUP BY IF(row_number<10,row_number,10);


Moving to innodb_file_per_table? I have just noticed (unfortunately while trying to restore from backup), that on my production server there is a 35GB ibdata1 file!

As I understand I can reduce its size by moving to innodb_file_per_table.
However following the answers I have seen here (link below), you have to dump and drop all databases before you can start the conversion.

How long would it take to do this convert?

Is it possible to stagger the convert and do it one database at a time?

Thanks.

What is the best way to reduce the size of ibdata in mysql?

You already linked rolando's post on how to do the migration. You can change the setting online to make use of per table files, but this will not fix your huge ibdata file size, even though single files are used afterwards.

To discern the time needed, try doing it on another installation you recreate from your backups. That way you will get reasonable numbers (as long as the systems have similar specs and hardware being used) how long this process will take, also you can try it out before laying hands onto the production system.

If you have hard uptime requirements another way to do this online might be through replication. To create a slave, you have create an initial mysqldump with special parameters, but this shouldn't be a problem since I guess you create your backups via dumps anyway?

When setting up the replication, you can let the slave run with innodb_file_per_table even though the master does not. Once all things are set, put the masters' ip onto the slave.

If all this is not possible due to the lack of hardware, then your system with the harsh uptime requirements was maybe sold too cheaply and you now have the ungrateful job to make something work with risks that should have been adressed in the initial planning by getting an adequate test setup besides your production setup.

For getting your mysqldumps to speed up, try piping them directly into parallel bzip, to get the cores to do more work and save time by reducing the needed disk accesses. Rougly like this:

# DUMP
mysqldump ... | pbzip2 -c > MYSQLDUMP.sql
# REPLAY
pbzip2 -dkc MYSQLDUMP.sql | mysql (...)


Table for actual values I have a database where logging happens and is processed in a data-warehouse styled environment. This means there are staging cycles where the data comes in and then is processed. For a reporting system, some log entries for certain parameters need to read, although it can happen that there are no entries in the staging cycle. Therefore there is a table which contains a value for every parameter - the last one which has been read. After the processing finished, the table is updated via

UPDATE jobcontrol.actual_values a
JOIN jobcontrol.processing p
SET a.value = p.value
WHERE p.t_ns = (select max(p.t_ns) from jobcontrol.processing p WHERE a.para_id = p.para_id)
AND a.para_id = p.para_id;

This seems improvable to me, although my try to simplify it via a real join didn't quite work.

Do you have any suggestions on how this could work more easily (and faster)?

-> Explain Statement (This was run on a test table with 36 rows for 8 different parameters)

+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                  | rows | Extra       |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
|  1 | PRIMARY            | p     | ALL    | PRIMARY       | NULL    | NULL    | NULL                 |   36 | NULL        |
|  1 | PRIMARY            | a     | eq_ref | PRIMARY       | PRIMARY | 4       | jobcontrol.p.para_id |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | p     | ref    | PRIMARY       | PRIMARY | 4       | jobcontrol.a.para_id |    2 | Using index |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+

-> SHOW CREATE TABLE

CREATE TABLE `processing` (
  `para_id` int(10) unsigned NOT NULL DEFAULT '0',
  `t_ns` bigint(20) unsigned NOT NULL DEFAULT '0',
  `id_inst` smallint(6) NOT NULL DEFAULT '1',
  `value` varchar(255) NOT NULL DEFAULT '',
  `isanchor` tinyint(4) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`para_id`,`t_ns`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

MySQL Version is 5.6

I edited the Query to the following:

EXPLAIN UPDATE jobcontrol.actual_values a
    JOIN jobcontrol.processing p ON a.para_id = p.para_id
    SET a.value = (select p.value from jobcontrol.processing p WHERE a.para_id = p.para_id ORDER BY t_ns DESC LIMIT 1)

The JOIN ON makes the recordset smaller (DB checks only the parameters which are also in processing, not every single one. The EXPLAIN however indicates that we at least use an index now)
In the SELECT : Replacing the MAX() with a ORDER BY ... DESC LIMIT 1 should boost the performance a bit, especially for big tables. If MAX() uses a filesort (like ORDER BY ... DESC does) nothing should change.

+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                  | rows | Extra                       |
+---+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+
|  1 | PRIMARY            | p     | index  | PRIMARY       | PRIMARY | 12      | NULL                 |   36 | Using index                 |
|  1 | PRIMARY            | a     | eq_ref | PRIMARY       | PRIMARY | 4       | jobcontrol.p.para_id |    1 | NULL                        |
|  2 | DEPENDENT SUBQUERY | p     | ref    | PRIMARY       | PRIMARY | 4       | jobcontrol.a.para_id |    2 | Using where; Using filesort |
+----+--------------------+-------+--------+---------------+---------+---------+----------------------+------+-----------------------------+


What is the best way to find out the sum of a field from a table? I am making a accounting software. I have a table debit_credit that contains double entry accounting records
Here is my example
data.Table is:
debit_credit_id | tran_id | ledger_head_id | debit | credit | dr_cr | commission | commission_type
--------------------------------------------------------------------------------------------------
1               | 2       |  5             | 500   | 0      | Dr    | null           | null

1               | 2       |  5             | 0     | 500    | Cr    | null           | null

This tables contains many rows and I have to run the following query several times:
'SELECT SUM(debit) - SUM(credit) FROM debit_credit WHERE ledger_head_id=$id'

But my project manager proposed, to make a  default view to making the sum. After that, when I need the sum just query from that view as
'SELECT debit - credit FROM debit_credit_view WHERE ledger_head_id=$id';

But I would prefer to use first method with stored procedure instead of the method proposed by my project manager.
Sql fiddle: http://sqlfiddle.com/#!9/cc594/1
What is the best way?

VIEWs do not improve performance.  (You are talking about CREATE VIEW, correct?)

Either of these is perfectly good:

SELECT SUM(debit) - SUM(credit) FROM debit_credit WHERE ledger_head_id = $id
SELECT SUM(debit - credit)      FROM debit_credit WHERE ledger_head_id = $id