Stop never ending backup in SQL Server 2012 Express Our production database log file has grown to consume all disk space on our server.

I know it's bad practice but I want to be able to shrink it in order to restore the service as soon as possible.

Still, when I go and launch my SHRINKFILE command,I get this error :

  Msg 3023, Level 16, State 2, Line 2
  Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on
  a database must be serialized. Reissue the statement after the current
  backup or file manipulation operation is completed.

It seems to me that the backup will never end. Is there a way to stop it manually ?

(I tried the SQL Server Agent thing, as told in the MSDN documentation, but it is not present in my SQL Server Express)

Restarting SQL Service will kill backup process and then you can shrink. Below query will show you spid of backup process, if any running on server.

SELECT *
       FROM master.dbo.sysprocesses
       WHERE --dbid = db_id('YourDatabase') AND --if you want to see status for database
       cmd LIKE 'BACKUP DATABASE%'

Below query will kill backup process if you don't want to restart SQL service.

Declare @SPIDU Varchar(10), @Query Varchar(50);
SELECT top 1 @SPIDU = spid
       FROM master.dbo.sysprocesses
       WHERE --dbid = db_id('YourDatabase') AND --if you want to see status for database
       cmd LIKE 'BACKUP DATABASE%'
Select @Query = 'KILL ' + @SPIDU + ';'
Exec( @Query)

Make sure you do clear trans log by taking backup or else you wont be able to reclaim space from trans log file.


In a replication setup, does corrupted data also gets replicated? Say one of the tables got corrupted, will that corrupted table still get replicated to the other DB? Or, will the replication stop to prevent the other DB from being corrupted too?

Note: Question is for both master-master or master-slave setup.

A crash often leaves MyISAM tables in need of "REPAIR".  Switch to InnoDB to avoid such.

Even then, it was rarely the "data" that was corrupted, just the index file (.MYI).  The index is maintained separately on each server; replication is not involved.

Bottom line: Don't use MyISAM any more.

If the Master discovers corruption, it will halt.  In this case, nothing will go to the Slave.

Replication involves sending changes (either in the form of write SQL statements or the effect of those statements), so corruption is not propagated.

If your definition of "corruption" includes a malicious DROP TABLE, that "corruption" will be propagated to the Slave.


Getting Skewed Results from LEFT OUTER JOIN with 3 TABLES I have 3 tables, users (user info), mghours (volunteer time), mgtraining (training time). I want a result that lists some user columns, sum of volunteer hours and sum of two types of training:

uid lastname firstname mgstatus trainingyear vol at ce

I use the following and as long as I have the two fields from the mgtraining table and the LEFT OUTER JOIN for those in the query, I get skewed results. If I take that out of the query, my user info and vol columns are fine.

SELECT users.uid AS 'uid',
users.lastname AS 'lastname',
users.firstname AS 'firstname',
users.mgstatus AS 'mgstatus',
users.trainingyear AS 'trainingyear',
sum(mghours.hourstime) AS 'vol',
SUM(IF(mgtraining.trainingtype = 'AT', mgtraining.trainingtime, 0)) AS 'at',
SUM(IF(mgtraining.trainingtype = 'CE', mgtraining.trainingtime, 0)) AS 'ce'
FROM users
LEFT OUTER JOIN mghours ON users.uid = mghours.uid AND year(mghours.hoursdate) = YEAR(CURDATE())
LEFT OUTER JOIN mgtraining ON users.uid = mgtraining.uid AND year(mgtraining.trainingdate) = YEAR(CURDATE())
WHERE users.coid = 33
GROUP BY users.uid
ORDER BY users.lastname ASC

The users.coid = 33 references a county ID.

I've been pouring over this for hours. What am I doing wrong?

JOINs together with aggregates (eg, SUM()) often give you bigger values than you expect.  To see this, remove the SUMs and GROUP BY and stare at the output.  You may see, for example, the same mghours.hourstime showing up multiple times.  Hence, SUM(mghours.hourstime) is inflated.

This is happening because JOIN mgtraining is coming up with multiple rows in mgtraining for each row in mghours.

The solution is to do the query in steps.  Notice how the inner query avoids inflating "vol":

SELECT  x.*,
        SUM(...) AS 'at
    FROM (
        SELECT  ...,
                SUM(mghours.hourstime) AS vol
            FROM ...
            GROUP BY ...
         ) x
    LEFT JOIN mgtraining ON ...
    GROUP BY ...
    ORDER BY ...;


Redo/Noredo in Oracle I have an Oracle 11.2.0.3 about 700GB in size running in noarchivelog mode in a pretty slow storage setup. It barely gives IOPS and there is resource crunch in terms of hardware resources. The transactions hitting the database is heavily read/write intensive and redo generation is quite high (near to 8MB/Sec) and naturally it is finding difficult to write to the disk.

What can be done in such a scenario? Can nologging in certain tables boost the performance? Data corruption because of nologging is a concern, I understand that and can be taken care of. But can someone give insight of how truly enabling nologging helps in this situation? or if it doesn't help, what are the other options I have?

Can nologging in certain tables boost the performance?

Not for a normal transactional load. nologging only affects some very specific cases of DML (essentially direct-path inserts) and some DDL (see logging_clause). So essentially only for bulk data-loading scenarios. If that's not what your database is doing, it won't help at all. If it is what your database does all day, then look at the most expensive statements and see if they can benefit from nologging.

  Data corruption because of nologging is a concern

No it's not. Recoverability is. But since you're in noarchive mode, you don't really seem to care about that.

  Can someone give insight of how truly enabling nologging helps in this situation?

As said above, it generally doesn't. It is certainly not something you should try and turn on everywhere for everything. Look at specific statements and objects. Test to make sure it does make a difference and doesn't break your application (direct-path operations have specific requirements in terms of when you need to commit in particular).

  What are the other options I have?

You already know one: get hardware that can handle the load you need to process. Sounds dumb, but if you're generating 8Mb/s writes and your drives can only handle 7Mb/s, performance will suck. There's no magic.

The other is: tune your application. Poorly written code can generate way more activity than necessary on the database - sometimes massively. You need to look at what's hurting (with AWR or whatever other tools you use) and figure out if there are better ways of doing things.


SQL Server - DBO without Backup Similar to my previous question : Prevent Non Copy Only Backups, how do I create a user that can do everything to my database <EXCEPT> take backups at all?

I've granted them every role except DB_backupOperator and DB_Owner. The issue is that the user will need to create objects (stored procedures, functions, tables, whatever) in the database.

With these roles, that is possible, however, they are not able to use the objects that they create.

I.E.

create proc test as select 1
go
grant exec on test to [OtherUser]

this completes successfully

grant exec on test to [TheCurrentUser]

throws an error

  Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

Also, if I just try to execute the stored procedure, I get an

  EXECUTE permission denied

I'm open for ideas.

Thanks.

Grant the user SELECT, INSERT, UPDATE, DELETE, EXECUTE and VIEW DEFINITION rights to the database (or the schema).  This will grant the user the rights to all the objects within the database (or the schema).

You'll probably also need to give them rights like ALTER ANY OBJECT within the database as well so that they can modify the objects.


Unable to connect to Amazon RDS instance I recently created an oracle instance on Amazon RDS. Unfortunately, I'm not able to connect to the instance using Oracle SQL Developer.

The (relevant) information I have from Amazon;

Endpoint - The DNS address of the DB Instance: xxx.yyy.eu-west-1.rds.amazonaws.com

DB Name - The definition of the term Database Name depends on the database engine in use. For the MySQL database engine, the Database Name is the name of a database hosted in your Amazon DB Instance. An Amazon DB Instance can host multiple databases. Databases hosted by the same DB Instance must have a unique name within that instance. For the Oracle database engine, Database Name is used to set the value of ORACLE_SID, which must be supplied when connecting to the Oracle RDS instance: ZZZ

Master Username - Name of master user for your DB Instance: org

Port - Port number on which the database accepts connections: 1521

From this information, the connection settings in SQL Developer are pretty obvious, so I don't really see what I could be missing...

The problem was solved by opening the "Firewall" under Security Groups in the AWS Dashboard to incoming connections from my computer's IP.


Check if all record columns of 2 Tables/Query are equal In SQL Server 2005, I would like to make one query that checks if all record columns of 2 tables are the same,
Example:

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' union select '22'
insert into @tbl2
    select '22'
--should return false

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11'
insert into @tbl2
    select '11' union select '22'
--should return false

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' union select '22'
insert into @tbl2
    select '11' union select '22'
--should return true

Using IN clause, or LEFT JOIN i'm only able to check if all record columns in one of the tables are present on the other.

Edit: Order is irrelevant

I think this will work:

SELECT CASE WHEN NOT EXISTS
         ( SELECT * FROM table1
           EXCEPT
           SELECT * FROM table2
         )
             AND NOT EXISTS
         ( SELECT * FROM table2
           EXCEPT
           SELECT * FROM table1
         )
         THEN 'True' ELSE 'False'
       END AS result ;


Losing the last column of data After Loading data into the table and running  a query on it, it tends to lose the last column of it. Like somewhere in import process it lost it. The file i was using was UTF-8 .csv format. A
Is there anything i need to add to this :

LOAD DATA LOCAL INFILE 'xxx/xxxx/Desktop/xxx/xxxx/file.csv'
INTO TABLE opendns_cc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

/Names in the path are changed due to security/

you could add an empty field at the end of each line, and remove the last column of your table.

Otherwise check for all the line terminations (\r\n vs. \n), or the presence of \n in your contents...

It should work at the end, good luck !


Display all days of the month with/out values I am trying to generate a display of time_in and time_out of employees. One pair in the morning and another pair in the afternoon. The query below only shows the dates that has values in it.

QUERY

SELECT empID, emp_day,
MAX(am_in1) as am_in,
MAX(am_out1) as am_out,
MAX(pm_in1) as pm_in,
MAX(pm_out1) as pm_out
FROM (
  SELECT empID, DATE_FORMAT(`time_stamp`,'%e') as emp_day,
  CASE WHEN am_pm='am' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as am_in1,
  CASE WHEN am_pm='am' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as am_out1,
  CASE WHEN am_pm='pm' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as pm_in1,
  CASE WHEN am_pm='pm' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%r') ELSE '' END as pm_out1
  FROM attendance
) as o
GROUP BY empID, emp_day

WORKING OUTPUT

empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out
152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00

How can I join the query below...

QUERY THAT GENERATES DAYS OF THE CURRENT MONTH

SELECT date_field
FROM
(
    SELECT MAKEDATE(YEAR(NOW()),1) +
    INTERVAL (MONTH(NOW())-1) MONTH +
    INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum FROM
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
        UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
    ) as AA
) as AB WHERE MONTH(date_field) = MONTH(NOW())

...to come up with the following output?

empID   |  emp_day    |    am_in    |    am_out    |    pm_in    |    pm_out
152     |  1          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  2          |             |              |             |
152     |  3          |  08:00:00   |   12:00:00   |  01:00:00   |  05:00:00
152     |  4          |             |              |             |

...until the end of the current month

152     |  31         |             |              |             |

Below are the tables and and sample values I am working with.

TABLE DEFINITION AND VALUES

CREATE TABLE IF NOT EXISTS `attendance` (
  `empID` int(11) NOT NULL,
  `time_stamp` datetime NOT NULL,
  `in_out` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `am_pm` varchar(2) COLLATE utf8mb4_unicode_ci NOT NULL
);

INSERT INTO `attendance` (`empID`, `time_stamp`, `in_out`, `am_pm`) VALUES
(152, '2017-01-01 12:00:00', 'out', 'am'),
(152, '2017-01-01 08:00:00', 'in', 'am'),
(152, '2017-01-03 12:00:00', 'out', 'am'),
(152, '2017-01-03 08:00:00', 'in', 'am'),
(152, '2017-01-01 05:00:00', 'out', 'pm'),
(152, '2017-01-01 01:00:00', 'in', 'pm'),
(152, '2017-01-03 05:00:00', 'out', 'pm'),
(152, '2017-01-03 01:00:00', 'in', 'pm');

Working Code: http://rextester.com/SVC13416

Here are the joined queries:

SELECT d.empID, DATE_FORMAT(d.date_field,'%e') as day_of_month, d.date_field, o.am_in, o.am_out, o.pm_in, o.pm_out
FROM (
SELECT e.empID, emp_day,
MAX(e.am_inA) as am_in,
MAX(e.am_outB) as am_out,
MAX(e.pm_inC) as pm_in,
MAX(e.pm_outD) as pm_out
FROM (
  SELECT empID, DATE_FORMAT(`time_stamp`,'%e') as emp_day,
  CASE WHEN am_pm='am' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as am_inA,
  CASE WHEN am_pm='am' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as am_outB,
  CASE WHEN am_pm='pm' AND in_out='in'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as pm_inC,
  CASE WHEN am_pm='pm' AND in_out='out'
    THEN DATE_FORMAT(`time_stamp`,'%T') ELSE '' END as pm_outD
  FROM attendance
  WHERE MONTH(`time_stamp`) = MONTH(NOW()) AND YEAR(`time_stamp`) = YEAR(NOW()) AND empID = 152
) as e
GROUP BY empID, emp_day) as o
RIGHT OUTER JOIN
(SELECT date_field, `employee`.empID
FROM
(
    SELECT MAKEDATE(YEAR(NOW()),1) +
    INTERVAL (MONTH(NOW())-1) MONTH +
    INTERVAL daynum DAY date_field
    FROM
    (
        SELECT t*10+u daynum FROM
        (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
        (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
        UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
    ) as AA
   ) as AB, `employee`
   WHERE MONTH(date_field) = MONTH(NOW()) AND YEAR(date_field) = YEAR(NOW()) AND empID = 152
) as d
ON o.emp_day = DATE_FORMAT(d.date_field,'%e') AND o.empID = d.empID
ORDER BY d.date_field;

And your fiddle: http://rextester.com/VRTYM92242


construct a sql , assign it to a variable and execute inside shell script I am trying to execute a mysql query inside my shell script, which is not executing .
Here is what i am trying to do

query="\"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';\""

echo $query

"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';"

Inside my shell script , i am executing below which is not working. It is not giving any error but simply not considering -e option and listing down all the valid options for mysql client.

mysql -u$username -p$password -e$query

I even validated below in my shell prompt

echo "mysql -uroot -p -e$query"
mysql -uroot -p -e"select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB';"

which is perfectly fine and executes without any issues. I am not sure what to do now. please help.Thanks

You need to do it like this:

query="select count(*) from information_schema.tables where TABLE_SCHEMA not in ('mysql','performance_schema','information_schema') and engine!='INNODB'"
mysql -uroot -p -e"${query}"

or

query="select count(*) from information_schema.tables"
query="${query} where TABLE_SCHEMA not in"
query="${query} ('mysql','performance_schema','information_schema')"
query="${query} and engine!='INNODB'"
mysql -uroot -p -e"${query}"

Double quotes do not need to be part of the query variable


How do I discover the Oracle Express workspace name? Is there a way to discover the WORKSPACE name if I've forgotten it?

I cannot login to the APEX admin console since I've forgotten what the workspace name was.

I do know the SYSTEM user password.

Could you please help me recovering my workspace name?

There are two tables you can get this info from.

APEX_WORKSPACES and APEX_APPLICATIONS.

The schema that these tables reside in depends on the APEX version. By way of example, my vanilla Oracle 11.2 install has them in the APEX_030200 schema. To list: select USERNAME from DBA_USERS where USERNAME like 'APEX%'.


Applying second and third Normal Form Let be a relation R(NameFile, Size, Directory, DateCreation,HourCreation,Login,AutAccess,DateAccess,TypeAcces

F={NF,D→S;
   NF, D →DC;
   NF, D→HC;
   L,NF,D→A;
   L,NF,D,DA,HA→TA;
}

  Part of the instances are given in the following array:

  1. What are the minimal keys of R? Why?

  2. In which normal type is R? Why?

I only know that:

a key is minimal when every attributes that appears in no functional
dependency is in every minimal keys
Every attributes that appears only on the left of the FD is in every
keys.
Every attributes that appears only on the right of the DF isn't in
any key.

Therefore we should have K1={NF,R,L,DA,HA} as the only minimal key.

What type of Normal Form is relation R?

I know that

something is 1NF if forall attributes of R, there is an atomic
value

Therefore, it is at least 1NF,

something is 2NF iif:

The scheme is at least 1NF
Forall An attribute, not in any key, A doesn't depends to part of the key. that is to say not in any functional dependency.

A scheme of relation R is 3NF iif

The scheme is 2NF
Not exists a transitive functional dependency

But I don't know what to do from there... I don't even understand the two last definitions

The only candidate key of your relation is {D DA HA L NF} (perhaps with R you mean D?)

You can verify this by calculating the closure of those attributes, {D DA HA L NF}+, and seeing that it contains all the attributes, while, if you remove any one of them, the closure of the remaining set does not contains all the attributes (this is the definition of a candidate key).

The relation is only in first normal form, since the second normal form requires the absence of partial dependencies, that is of dependencies in which non-prime attributes (i.e. attributes not belonging to any key) depends only on part of a key. In this case only the last functional dependency is not partial.


How to make a union view execute more efficiently? I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process every night.

In several places in our code we need to run queries that combine the active and archive tables, almost invariably filtered by one or more fields (which we've obviously put indexes on in both tables).  For convenience it would make sense to have a view like this:

create view vMyTable_Combined as
select * from MyTable_Active
union all
select * from MyTable_Archive

But if I run a query like

select * from vMyTable_Combined where IndexedField = @val

it's going to do the union on everything from Active and Store before filtering by @val, which is going to kill performance.

Is there any clever way of making the two sub-queries of the union view each filter by @val before they create the union?

Or maybe there's some other approach you would suggest that achieves what I'm going for, i.e. an easy and efficient way of getting the union record set, filtered by the indexed field?

EDIT: here's the execution plan (and you get to see the real table names here):

Oddly enough, the active table is actually using the correct index (plus a RID lookup?) but the archive table is doing a table scan!

The comments on the question show that the issue is that the test database the OP was using to develop the query had radically different data characteristics than the production database.  It had much fewer rows and the field being used for filtering wasn't selective enough.

When the number of distinct values in a column is too small the index may not be sufficiently selective.  In this case a sequential table scan is cheaper than an index seek/row lookup operation.  Typically a table scan makes exensive use of sequential I/O, which is much faster than random access reads.

Often, if a query would return more than just a few percent of rows it will be cheaper just to do a table scan than an index seek/row lookup or similar operation that makes heavy use of random I/O.


Can you use ODBC to extract data directly from SAP HANA S/4 1610? Trying to get a handle on what's possible and what's not with SAP Hana S/4 1610.

My understanding is that one can access the underlying SAP HANA DB via ODBC simply by utilzing the SAP HANA Client which installs the ODBC Driver required. Once installed one can, from what I read, access the vast data stores that power the SAP ERP via standard ODBC calls using tools such as SSIS.

However I'm being told conflicting information from other people on if this is even possible and that what is really required is for the vendor to be in control of that process and for them to write apps in ABAP that will interrogate the underlying DB and deliver unto us flat files which our ETL process will then use.

We'll be using a locally hosted SAP HANA installation so, not on prem but no cloudy either.

Clarification: All I need here is a yes, you can connect to SAP HANA S/4 1610 via ODBC or no, you can't and another method is required.

TBH not really interested in the how either since everything else I've read online tells me that. However when you've $10bn a hour consultants saying no, one has to question reality.

Your question is a bit confusing so I hope I'm deciphering it correctly.

Technically you can connect to a SAP HANA database using ODBC -- this is helpfully documented in the manual.

What you do after you connect is another question. The SAP ERP application schema is very complex and poorly (if at all) documented, and it is highly unlikely that you will be able to reverse engineer it and create a sufficiently reliable ETL process based on that.

So the answer I guess is "yes you can, but you don't want to".


Does mysql use B-tree,B+tree or both? I did some search on the matter and I found out that Mysql uses B+Tree index, but when I run "show index" the index type that I get is Btree. And I found in this article that Mysql uses both Btree and B+tree. If it is true that it uses both; why is it named Btree without mentioning B+tree, in which case each one is used. I know the difference between the two, and I want to do some queries to figure out the difference in performance between B-tree and B+tree indexes. Which leads to my second question, is there a big difference between the two in performing certain queries, if yes, please give an example.
Thank you in advance.

InnoDB uses B+Tree indexes, not B-Tree. All details about InnoDB data structures can be found here. You may also want to look at these diagrams. The author of both the resources, Jeremy Cole, was head of MySQL team at Google.

Why is the syntax BTREE instead of B+TREE? This question should be posed to some MySQL or MariaDB engineer, but I see at least two possible reasons:

B+TREE would be a very bad keyword, because it contains +, which is usually an operator.
That syntax is older than InnoDB. It is probably as old as the ISAM storage engine, which exists no more. It is very possible that B-TREE was used at that time.

Why does the documentation state that InnoDB uses B-Tree? Well, not all MySQL users are supposed to know what B+Tree is. This may be an oversimplification, but in that context it seems to me acceptable.

You wrote that you know the difference between B-Tree and B+Tree. Than the different performance characteristics should be clear:

B+Tree is faster for sorting;
B-Tree is faster when you insert values in the middle.

But in general, B+Tree is considered superior. How much? I don't know, but surely not orders of magnitude.


Mysql constraint problem I'm using MySQL5.5 and for this question I have 3 simple tables t1, t2 & t3 designed like this:

+------------+   +------------+    +------------+
| t1         |   | t2         |    | t3         |
+------------+   +------------+    +------------+
| t1_id (PK) |   | t2_id (PK) |    | t3_id (PK) |
+------------+   | some_value |    | t1_id (FK) |
                 +------------+    | t2_id (FK) |
                                   +------------+

PK = primary key, FK = foreign key, and all fields are of type INT

The problem I have is that I want to add a constraint to table t3 that involves the field t2.some_value. Basically I want the combination of t1.t1_id and t2.some_value to be unique within table t3. For example here is a situation I want to avoid:

  t1           t2                        t3
+-------+    +-------+------------+    +-------+-------+-------+
| t1_id |    | t2_id | some_value |    | t3_id | t1_id | t2_id |
+-------+    +-------+------------+    +-------+-------+-------+
| 10    |    | 20    | 1          |    | 30    | 10    | 20    | <-- t2.some_value = 1
| 11    |    | 21    | 2          |    | 31    | 10    | 21    |
+-------+    | 22    | 3          |    | 32    | 10    | 22    |
             | 23    | 1          |    | 33    | 10    | 23    | <-- t2.some_value = 1
             +-------+------------+    | 34    | 11    | 23    |
                                       +-------+-------+-------+

The problem here is that two records in t3 link the same t1 record (t1_id=10) to two different t2 records that have the same some_value. Is there any way to prevent this from happening? To my knowledge MySql does not allow constraints to be defined that span multiple tables.

I can think of one solution, but that involves duplicating the some_value field in table t3 so I can enforce a unique key directly on it. This obviously breaks the normalisation design rule, but is there any other way?

From what I can tell, you'll have to create the 'some_value' in table3. Here's a link to the manual page on foreign key constraints

There is an example with a 'complex' foreign key constraint that is similar to your setup, which duplicates the content in the third table. There is (minimal) comfort in knowing that hey, at least it's handled by a Foreign Key.


SQL Server Replication :: Dedicated Distributor Hardware Requirements Does anyone know any rules of thumb (or better) with regards to hardware requirements for a dedicated distributor server?  i.e. Based on estimate metrics (e.g. number of subscribers, publishers, tables, average table size, average update frequency, etc) is there some way to calculate a minimum RAM/CPU requirement for the distributor?

More Info

We'll likely be using SQL Server 2012 for the distributor, though the publishers and subscribers are currently a mix of 2008 and 2008 R2.

The distributor will be clustered to ensure high availability.  We'll go for active/active to make optimal use of resources, sending half the publishers to one and half to the other, but will ensure that each node in the cluster's spec satisfies the minimum spec so that should one node go down the solution can still cope.

Background Info

We've been using replication to allow our international BI system to pull data from our transactional systems without impacting performance, with the BI servers hosting the distributor. Going forwards we're hoping to move this distribution onto a separate server, so that we can provide replication as a service to the BI team and to other teams requiring this (i.e. local BI teams from various countries & our in house monitoring system).  We expect to see an increase in the number of tables being replicated as well as in the number of subscribers and publishers (as we'll be including our non-production systems also).

Thanks in advance.

Running on a VM is a good idea as it's hard to define resource requirements but in most cases distributors are quite lightweight. The main factors are number of subscribers, transactions count and the number of articles in replication.

Some tips to help:

Only replicate what you need to, this includes SPs, views etc.
Using pull will reduce CPU and memory requirements on the distributor.

This is harder to implement in Web edition but possible with scripts.
If you have a lot of push agents running, you should allow more memory outside of SQL.
Consider increasing the desktop heap size if using push and have a lot of agents. (this is a registry change)

Choose the right type of replication.

Snapshot is light on distributors as it doesn’t have the overhead of the log reader or clean-up jobs but heavier on the network and the subscriber when pushing out.

Enterprise edition doesn’t provide any real performance benefits on the distributor.

I hoped that merry-go-round reads would but no, it’s mostly in CPU when problems occur.

You may find it helpful to reserve some cores for the network as large distributors can get overwhelmed during imports or clean-up jobs and struggle to get the data out.

I’m talking 1000 subscribers and 100,000 articles (not my idea).

Throwing CPU at the distributor rarely fixes the high CPU issue. You need to treat the distribution databases like any other database and tune it (I believe Denny Cherry has an article which recommends some indexes).
CPU can also be caused by the locking and deadlocking during the clean-up job or large transactions failing to be delivered causing the subscriber agents to scan the entire table. You may find that reducing the frequency of the clean-up job actually help overall throughput but could impact latency.

In a nutshell, if replication is configured correctly, updates are batched properly (don’t update millions of rows in one transaction) and the subscribers are up to date then it’s happy.


How can I correct SQL Server Error 8928? I received the following error message in an application running against one of my databases:

SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0xfdff74c9; actual: 0xfdff74cb). It occurred during a read of page (1:69965) in
database ID 5 at offset 0x0000002229a000 in file 'e:\SQL Server\MSSQL.1\MSSQL
\[DB_Name].mdf'. Additional messages in the SQL Server error log or system event log may
provide more detail.
    This is a severe error condition that threatens database integrity and must be
corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This
error can be caused by many factors; for more information, see SQL Server Books Online.

That sounded bad so I ran the following:

DBCC CHECKDB ([DB_Name]) WITH NO_INFOMSGS, ALL_ERRORMSGS

And received the following report:

Msg 8928, Level 16, State 1, Line 1
Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID
    72057594055557120 (type LOB data): Page (1:69965) could not be processed. See other
    errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc
    unit ID 72057594055557120 (type LOB data), page (1:69965). Test (IS_OFF (BUF_IOERR,
    pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc
    unit ID 72057594055557120 (type LOB data). The off-row data node at page (1:69965),
    slot 0, text ID 89622642688 is referenced by page (1:69968), slot 1, but was not
    seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID
    72057594055622656 (type In-row data): Errors found in off-row data with ID
    89622642688 owned by data record identified by RID = (1:77754:1)
CHECKDB found 0 allocation errors and 4 consistency errors in table '[Table_Name]'
    (object ID 1326627769).
CHECKDB found 0 allocation errors and 4 consistency errors in database '[DB_Name]'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
    ([DB_Name]).

I looked through my old images, and it appears the error has existed for quite a while. I don't have a clean image. I'm not much of a DBA so I don't know the best way to proceed; I don't even understand how the error messages can point me to the data corruption.

Can anyone tell me what these messages indicate and how to proceed?

Msg 8928, Level 16, State 1, Line 1 Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 72057594055557120 (type LOB data): Page (1:69965)

There is corruption in your SQL Server database column is LOB and  may have VRCHAR datatype. Index ID 1 indicated problem with Clustered index so I guess minimum repiar options repair allow data loss might delete data to recover database. Dropping and recreating index will not help.Please dont run repair first. Do you have latest valid backup of SQL server database ? Best possible solution here is to restore from valid backup.

How big is your database. Please run restore verifyonly  before restoring the backup to check that backup you are restoring is consistent. Although restore only will guarantee that backup is complete and valid in all format.

Please read SQL Server errorlog and windows event viewer to find out reason why corruption happened and then take steps to fix it.

if you dont have valid backup you might as well try to repair the database using below command

ALTER DATABASE DB_NAME SET SINGLE_USER;
GO
DBCC CHECKDB (N'DB_NAME', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO


MySQL performance for aggregate function I'm using mysql 5.5

Here is my table:

CREATE TABLE `temperature_information` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `device` int(11) NOT NULL,
   `temperature` int(11) NOT NULL,
   `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   KEY `device` (`device`),
   KEY `date` (`date`),
   KEY `idx` (`device`, `date`)
) ENGINE=InnoDB AUTO_INCREMENT=25602738 DEFAULT CHARSET=latin1

This table has ~50m. records.

Here is my query:

SELECT date as ValueDate, MAX(temperature)
FROM (
     SELECT date, temperature FROM temperature_information WHERE device = 1111
     ORDER BY temperature DESC) c
GROUP BY DATE(ValueDate),HOUR(ValueDate)

This query returns Maximum temperature for each day. It's execution time is ~0.9 s. and subquery's time 0.003 sec.

I have separate indexes on the date and device columns and a multi-column index idx on device,date. The EXPLAIN says it uses device index for query, which is good. The sub query is very fast.

But to get MAX temperature for each day I need to use GROUP BY. I know that functions on indexed column disables the use of indexes, but I don't know a workaround to make it efficient and produce same results.

Here is EXPLAIN:

id  select_type     table           type    possible_keys   key     key_len  ref    rows    Extra
1   SIMPLE  temperature_information ref     device,idx    device      4      const  29330   Using where; Using temporary; Using filesort

My question:

Is it possible to write a query which would be more efficient and would produce same results or should I process rows returned by sub query and find MAX temperature for each day myself (This would be written in c)?

Sub query returns 20-40k rows on average.

PS. I know you can remove sub query, but I left it like this to clarify question

Creating index on those (3) columns improved performance ~20 times. But I have another concern regarding indexes. I provided, not full table. table have another columns like signal, etc. I'm planning to do same queries to find MAX for each day on these columns too. Does adding too many indexes make SELECT slower for other queries. I know that inserts and updates will be slower.

The subquery is not needed at all.
The ORDER BY inside a subquery like this (without a LIMIT) makes no sense.
The external query has SELECT date but GROUP BY DATE(date). HOUR(date). This, while allowed in MySQL older versions (i.e. before 5.7), is not valid SQL.

I suggest you rewrite:

SELECT
    DATE(date) AS value_date,
    HOUR(date) AS value_hour,
    MAX(temperature) AS max_temperature
FROM temperature_information
WHERE device = 1111
GROUP BY DATE(date), HOUR(date) ;

Regarding performance:

an index on (device, date, temperature) will make the query more efficient than the current indexes on (device) alone and on (device, date). If you add this 3-column index, you could drop the other two ("device" and "idx") indexes.
Another option would be to store the date and hour part in separate columns and add a 4-column index on (device, date_part, hour_part, temperature).
If you move to version 5.7, you coul dhave the date and hour parts as generated columns. See MySQL docs: Ganerated Columns.
After the comment/edit that there are many more data columns and since it looks like you'll be running analytic type of queries, another option would be to change the PRIMARY KEY to (device, date) - or (device, date_part, hour_part, min_sec_part). This effectively - for an InnoDB table - clusters the data in the way you want them for this query. Of course you should test first this alternative design and how it will affect other queries, too.


How do I include nulls during comparisons in SQL Server? So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results.

For example, the following query doesn't return rows where value IS NULL but I want it to:

SELECT *
FROM table
WHERE
  (value != 26)
  AND date IS NULL
  AND last_modified >= '5/21/2014'

I understand I can do the following as a workaround but seriously? Having to add parentheses and check if is null for every single field every single time I want to include it? Seems ugly, not intuitive and crazy.

SELECT *
FROM table
WHERE
  (value != 26 OR value is null)
  AND date IS NULL
  AND last_modified >= '5/21/2014'

I mean I know NULL is not a value and thus can't be compared but can't you infer that it is, in fact, definitely not 26? If 26 is something and NULL is nothing and nothing is not something then NULL is not 26. Seems logical to me.

Does anyone know how I can, in a cleaner way, include nulls in my results when using comparisons without having to include an explicit check each and every time. Also turning off nulls on my tables is definitely not an option.

Edit:

My real problem of why I don't want to do it the way I showed was not exposed. So here goes. I am writing a program that let's you build queries to database tables and let's the user dynamically create filters and such which essentially at the end of the day constructs a SQL statement and gets the results to display to the user. The fields chosen by the user can be any and/or all fields in a given database and if I have to literally put that ISNULL check on every single field that would be really inefficient and make looking at the SQL super ugly. My program is table-definition-agnostic meaning I don't care what's in your table and don't want to know what your table's definition is. I just want you to pick a table, choose some fields to filter on with equals, not equals, in, not in, etc ... and then click a button to view the results of that request.

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx

I get what you're trying to do, and to make a counter point I would ask if you've seen any queries generated by reporting services or something like Cognos? If so, you'll see exactly what you're describing you don't want to do. Why? Well because with a schema that allows nulls, that's the way to do it. I'm not saying it's a super awesome and great idea but it works all of the time.

What your designer could do is check to see if that column could even be null and if so the appropriate logic could step it and create the correct query. You could also have options such as "This column may be null, do you want those values?". I don't know the end-game per-se and writing your own dynamic querying tool is quite the deat when the logical consistencies are all factored in (such as this).

I would continue to do explicit null checking on columns that could possibly be null, sure it doesn't look the best but it works all of the time.

ANSI_NULL set option will work for now but NOT a good idea especially if you don't control the environment, plus it will be forced set ON later and cause errors where you'll need to re-write your application logic anyway. This is the way SQL Server works with NULLs.


Normalizing the Test Table I'm a web developer looking to leverage more of the power of my RDBMS. I'm currently learning about normalization of data and database design.

In the book that I am reading, we started off with one table that contained information about students, teachers, tests, grades, etc, and broke that down into separate tables for each entity.

However, I'm looking at the test table (see picture below), and I am wondering if this table also violates a few design principles.

In particular, I think that the Test column violates the "no redundant data" principle, and the "no partial dependencies principle". (On this second point, I am assuming that the primary key for this table is a composite key of TestID and TeacherID, in which case the Test column depends only on the TestID part of the primary key).

What I would do in this case is further break up the test table into a Tests table containing the a TestID as a primary key, Name as the name of the test, and TotalPoints. Then, I would create a AdministeredTests table that contains a TestId, a TeacherId, and a Date, and the primary key would be a composite of TestId and TeacherId.

Would I be on the correct track here? I'm asking because the author also shows the following table:

Again, this suffers from redundant data (although it has no partial or transitive dependencies). Based on my current understanding, I think the author simply has not introduced join tables yet, hence the reason why normalization "stops" at a certain point. Also, the author is quick to point out: "...we presented one possible database design as an illustration of how to normalize data. In truth, many possibilities exist for designing this database." Just wondering the possibility I presented here is reasonable.

The problem here is the approach. Looking at table values and guessing keys is not a way to go.

Simply write down the predicate and constraints for the table, everything else follows from this.

Here is one example. If your business rules (predicate and constraints) are different then keys are going to be different too, so table may or may not have to be decomposed.

Note:

[Px]   = predicate x
[cx.y] = constraint x.y

KEY = PK or AK

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY

[P1] Test number (TEST_ID) of test type (TEST) with total score of (TOTAL_POINTS) was administered on date (DATE) by teacher (TEACHER_ID).

[c1.1] Test is identified by test number.

[c1.2] Each test is of exactly one test type; for each test type it is possible that more than one test is of that test type.

[c1.3] Each test has exactly one total score, for each total score it is possible that more than one test has that total score.

[c1.4] Each test was administered on exactly one date; for each date it is possible that more than one test was administered on that date.

[c1.5] Each test was administered by exactly one teacher; for each teacher it is possible that more than one test was administered by that teacher.

test {TEST_ID, TEST, TOTAL_POINTS, DATE, TEACHER_ID}
 KEY {TEST_ID}

As far as the TEST column is concerned, it simply contains  valid values from the domain of this attribute. You may for example add a predicate.

[P2] Test type (TEST) exists.

test_type {TEST}
      KEY {TEST}

and populate with valid domain values

test_type (TEST)
{
  ('Pronoun Quiz')
, ('Solids Quiz')
, ('China Test')
, ('Grammar Test')
}

Use this to constrain values in the test table, so it would look like this:

test {TEST_ID, TEST, TOTAL_POINTS, DATE, TEACHER_ID}
  PK {TEST_ID}
 FK1 {TEST} REFERENCES test_type {TEST}

When considering physical design, you may want to use an integer for this foreign key -- so this may look like:

test_type {TEST_TYP_ID, TEST}
       PK {TEST_TYP_ID}
       AK {TEST}

test {TEST_ID, TEST_TYP_ID, TOTAL_POINTS, DATE, TEACHER_ID}
  PK {TEST_ID}
 FK1 {TEST} REFERENCES test_type {TEST_TYP_ID}

And so on; however, without explicitly defining business rules -- in form of predicates and constraints -- everything is open to misinterpretation and endless arguing.


Replace text with input data How can I achieve this?

ValidationMessage table:

ValidationMessageID | Text
1                   | Item(s) <ItemList> do not exist in project <ProjectID>.
5                   | This value <Value> is invalid.

ReplacementValues table:

ValidationMessageID | ReplacementValues | Index
1  | <614882,614889>,<8068637>          | 10
1  | <614882>,<8068634>                 | 11
1  | <614883>,<8068639>                 | 12
5  | <John Smith>                       | 22

Expected Result:

ValidationText                                              | Index
Item(s) 614882,614889 do not exist in project <8068637>.    | 10
Item(s) 614882 do not exist in project <8068634>.           | 11
Item(s) 614883 do not exist in project <8068639>.           | 12
This value John Smith is invalid.                           | 22

The native string processing functions in TSQL are not very sophisticated and this approach requires parsing of the validation message to find items delimited by angle brackets.

Then it requires splitting the ReplacementValue by comma (but not if the comma is inside an angle bracket) and finally removing the angle brackets and doing the replacement for each place holder by corresponding ordinal position.

This could be done in TSQL but would likely require some poor performing scalar UDF - or alternatively would need to call out to CLR/Java code for more advanced string processing capabilities.

I suggest restructuring as follows

CREATE TABLE ValidationMessage
(
ValidationMessageID int primary key,
Text varchar(2000)
)

insert into ValidationMessage
values
(1,'Item(s) %s do not exist in project %s.'),
(5,'This value %s is invalid.');

CREATE TABLE ReplacementValues
(
ValidationMessageID INT,
[Index] INT,
Param1 VARCHAR(50) NULL,
Param2 VARCHAR(50) NULL,
Param3 VARCHAR(50) NULL,
Param4 VARCHAR(50) NULL,
Param5 VARCHAR(50) NULL
)

INSERT INTO ReplacementValues(ValidationMessageID, [Index], Param1,Param2)
VALUES
(1,10,'614882,614889','8068637'),
(1,11,'614882','8068634'),
(1,12,'614883','8068639'),
(5,22,'John Smith', NULL);

With an eye on making the final query simple with available functions

SELECT VM.ValidationMessageID,
       FORMATMESSAGE(Text, Param1, Param2, Param3, Param4, Param5),
       [Index]
FROM   ValidationMessage VM
       JOIN ReplacementValues RV
         ON RV.ValidationMessageID = VM.ValidationMessageID

db <> fiddle


Establishing Baselines & Benchmarks When you are hired as a DBA in a new shop, what are the important tools that you would use for establishing baselines and implementing benchmarks for 50+ instances? You advice would be highly appreciated.

For Microsoft SQL Server, the lowest-intrusive tool is Performance Monitor, aka Perfmon.  Here's my tutorial on grabbing Perfmon counters for SQL Server and analyzing them:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/


MySQL Fast Database Data Validation Master/Slave I'm moving a MySQL database from the US to Australia and have a requirement where downtime is kept to an absolute minimum.

The database is 400GB is size, replication is working without any issues. However, performing data validation takes days with pt-table-checksum.

To ensure that the database integrity is maintained up until the point of cutting over, is there a better tool than pt-table-checksum to validate the data is consistent between the master and slave? Or can pt-table-checksum be optimised to run faster?

Please note that initial testing with pt-table-checksum across the different geographic regions was performed where the databases were both idle

Any advice is appreciated!

The Master is now in the US?  But will later be in Australia?

Rather than simply have Master (in US) + Slave, also set up the binlog (etc) so that it is Master-Master.  However, continue to write only to the US Master.

Once you have spent days on validation, you should be ready to point the clients at the Australia Master.  At this point, you have to trust that the days of replication makes across the big pond correctly.  (You could spend more days re-validating, but I expect it will say OK.)

I don't know for a fact, but I would expect pt-table-checksum to work almost as fast in a live system as an idle one.


Filter index slow initial use I have a filtered index like this:
CREATE INDEX idx1
   ON public.tbl1 USING btree
    (col1 COLLATE pg_catalog."default" ASC NULLS LAST, col2 ASC NULLS LAST)
    WHERE col3 IS NULL;

and a query like:
SELECT * FROM tb1 WHERE col3 is null and col4 = 12

This query scan idx1 which is fine as this index is only a few rows (max 400 or so) the underlying table is millions of rows.
The first time this query runs, it runs painfully slow (almost a minute) but the seconds time it runs it does so in 20ms.
Can someone help me understand what is happening? The index gets rebuilt every evening but seems to bloat (gets to a few hundred megs) during the day. From the behaviour it feels like it has to load data into memory and the next time its faster because the data is cached. The database is quite busy 2k tupples in and 4k tuples out.
QUERY PLAN
Index Scan using unsubmittedmsgs_1 on tbl1 s  (cost=0.38..1.88 rows=1 width=315) (actual time=145.335..39385.943 rows=101 loops=1)
  Index Cond: (created_dow = 1)
  Filter: (machine_id = 20)
  Rows Removed by Filter: 155
  Buffers: shared hit=803132 read=25838 dirtied=85580
Planning:
  Buffers: shared hit=255 dirtied=3
Planning Time: 1.759 ms
Execution Time: 39386.110 ms

Very selective partial indexes tend to get bloated quickly, because the table autovacuuming is triggered off the percent turn over in the table.  But 20% turn over in a table can mean many thousands of percent turn over in the partial index.  Nightly reindexing can also solve the bloat, but it won't stay solved for long if turn over is high.  You can crank up autovacuum so it happens multiple times throughout the day.
The first time you run this query in a while, it is probably marking a lot of obsolete index entries as killed.  That is probably one reason there are so many dirtied buffers, and also one reason it will be faster the next time, as it will no longer have to visit all the obsolete entries just to discover they are obsolete.  Of course the buffers it does still need to read are likely to be cached as well, so that would be another reason for it to be faster.
Note that this index-entry killing doesn't work in bitmap index scans, only on regular index scans.  Also, it doesn't work on hot standbys. Both of which can be a real problem for some setups.


SQL Server Backup Schedule Overlap I have a SQL SERVER DB that i want to backup via automated schedule (using GUI maintenance plan)  . I have 2 choices when configuring a schedule  :

1) do not bother about log backup , full backup and diffrencial bakcup event time , as if all the 3 backups occur on same time , it makes no difference .

2) make sure that log backup, full backup and differential backup never occur in same time by configuring carefully the time of each backup.

Below is the image describing the 2 choices (Schedule1 for choice 1 and chedule2 for choice 2 )

Do you know which one is the best ?

Edit : Sechdule1 may face some errors when backups are overlaping . Here the backups are of type log and differential hapening same time at 5:30 as the backup whatever the type is will be appended to the existing backup file. So the file was in use by log backup and differential failed . Even if no data was lost.

First step: Understand how backups really work. The main things to understand:

Log backups after SQL Server 2000 can be taken concurrently during full and differential backups. The only thing to remember is during that timeframe the log will not be cleared until that full or differential backup completes.
Full and differential backups that collide will cause whichever backup started first to take precedence, the second backup will wait in line until prior backup is completed. So if you have a FULL backup running, and a DIFF backup starts, that DIFF backup will wait until the FULL is complete. I do believe it should be the same for the opposite as well.

Second Step: Whichever backup plan you choose ensure it meets the recovery requirements for the application AND/OR the business.

So while either schedule will work the schedule 2 will likely lessen the amount of IO activity that is caused when the backups overlap. It would based on the size of the database and how much activity SQL Server has to backup during the differential and log backups. Depending on how you setup schedule 1 you could end up with all three backups trying to occur at the same time frame.


Set value of character_set_client to utf8mb4 I'm trying to convert my DB to utf8mb4 following this guide. I have set:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
init-connect='SET NAMES utf8mb4'
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4
skip-character-set-client-handshake

But the value of character_set_client and character_set_results still won't change to utf8mb4.

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

What am I doing wrong? How do I get those values set to utf8mb4?

Disclosure: I’m the author of How to support full Unicode in MySQL databases, the guide you’re following.

Where did you save the modified settings? Check where mysqld loads the default options from. It’s usually /etc/my.cnf (as mentioned in the guide) but it may be different on your system. Run the following command to find out:

$ mysqld --help --verbose 2> /dev/null | grep -A1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

Did you restart mysqld by running mysqld restart after making the changes?


Couchbase 4.5.1: What is beam.smp and why it is consuming too much memory We are having a single node Couchbase [4.5.1-2844 Community Edition (build-2844)].

For some reason, we are seeing high memory usage on the server. When we log into the server and view the stats we can see that memcached and beam.smp service is using the memory.
I need to know, what is the use of beam.smp process, and why it is consuming high memory.
Screenshot for the same is attached to the question. Also I have followed the following links to debug the issues:

https://forums.couchbase.com/t/beam-smp-memory-usage/11843/2

https://issues.couchbase.com/browse/MB-20521

beam.smp is the Erlang vitual machine used by CouchDB (as CouchBase) for serving and indexing your DB. So it's where the magic happens.


Drop a SQL Server database Taking too long to drop a 500gb+ SQL Server database. My question being what ways can we drop a database other than through GUI or T-SQL? Would deleting the .mdf and .ldf files from the server help?
I can detach and delete the mdf and ldf files. I just want to know the fastest way to drop it. It took me close to 11hrs without any results so I killed it and was hoping someone might know how to do it.

Let's say you have a database named wildlife, kick the users off and detach the database.

This can be done via the SSMS GUI, right click, under all tasks.

-- Kick off users, roll back current work
ALTER DATABASE [WILDLIFE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Detach database
sp_detach_db 'WILDLIFE'
GO

However, the *.mdf and *.ldf files are still there.  Shift delete them so that they do not go to the recycle bin.

Good luck!

PS:  Check out my blog for more information.
http://craftydba.com/?p=1753


MySQL create index with LOCK=NONE still locks a table I have the following MySQL RoR Migrations:

class ReindexRpushNotification < ActiveRecord::Migration
  def up
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed, processing, deliver_after), ALGORITHM=INPLACE, LOCK=NONE;")
  end

  def down
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed), ALGORITHM=INPLACE, LOCK=NONE;")
  end
end

during this migration I'm trying to perform some requests (GET, COUNT, DELETE, UPDATE) but nothing work, all these requests just wait

I found an info about index creation in background here

https://stackoverflow.com/a/36064200

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

but it doesn't work for us

Did anybody try LOCK=NONE?

We use MySQL 5.6.23 on AWS RDS

Looks like I have a clue why it doesn't work

Our table have the following constraint:

CONSTRAINT `rpush_notifications_event_id_fk`
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE

Next, I found here one interesting thing:
https://blogs.oracle.com/mysqlinnodb/entry/online_alter_table_in_mysql

  Online operation (LOCK=NONE) is not allowed in the following cases:

  when adding an AUTO_INCREMENT column,
  when the table contains FULLTEXT indexes or a hidden FTS_DOC_ID column, or
  when there are FOREIGN KEY constraints referring to the table, with ON…CASCADE or ON…SET NULL option.

so looks like we have a 3rd case


MySQL: Add column to unique index or add new index? For a MySQL database, I have an InnoDB table name user_metrics with the following columns:

   id INT(11) NOT NULL AUTO_INCREMENT,
   userid INT(11) NOT NULL,
   name VARCHAR(32) NOT NULL,
   value DECIMAL(20,9) DEFAULT NULL
   valueclass INT(11) DEFAULT NULL

For this table, I currently have a primary key (on id) and a unique index on (userid,name). For one particularly heavy select query, I need an index on (userid,name,valueclass).

I could create a new index or add the valueclass column to the unique index I already have. As far as performance of the select query is concerned, it doesn't matter. There also doesn't seem to be any significant difference in disk space or memory usage. Mutations to the table would be a bit slower, but it's essentially write-once-read-many.

Are there any good reasons why I should NOT just add a column to the unique index I already have?

The select query has some joins as such:

SELECT      `name`
        ,   AVG(`valueclass`) AS `avg`
        ,   STD(`valueclass`) AS `std`
FROM        `user_metrics`
WHERE       `valueclass`    IS NOT NULL
    AND     `userid`    IN ([list of userids])
    AND     `name` IN ([list of names we want])
GROUP BY    `name`';

Basically, it tries to get some statistical information and only needs the three columns (so the entire query could be run on the index).

For one particularly heavy select query, I need an index on
  (userid,name,valueclass).

This is a good reason to add an index. It would also be good to examine your query plan. BTW, underscores between words make variables more legible.

  Are there any good reasons why I should NOT just add a column to the
  unique index I already have?

A unique index enforces uniqueness. If you have a need for the two column uniqueness then most likely adding a third column to that will compromise the integrity of your constraint. To assist your heavy select query the best option would be to add an index to (userid,name,valueclass) without the unique constraint.


FK Issue with Fact Table I have a Datawarehouse star schema.
One Dim table is 'DimTweet'. This contains tweet details.

For a selected date I want to be able to display the number of tweets.

However, I have a problem in my current design. How do I use one TweetID as FK in the Fact tbl for multiple rows in the DimTweet tbl?

Code:

INSERT INTO [CarDW].[dbo].[FactCarDetail]
           ([CarID]
           ,[RegionID]
           ,[DateID]
           ,[TweetID]
           ,[SharePrice]
           ,[ShareQty]
           ,[SalesQty]
           ,[TweetCountPositive]
           ,[TweetCountNegative])
SELECT
    c.CarID,
    r.RegionID,
    d.DateKey,
    -- ????? AS TweetID
    scsp.Price,
    scsp.Quantity,
    scs.Quantity,
    -- COUNT NO. OF TWEETS Postive
    -- COUNT NO. OF TWEETS Negative
FROM
    dbo.DimCar c
INNER JOIN
    dbo.StagingCarSale scs
    ON scs.CarModel = c.Model
INNER JOIN
    dbo.DimRegion r
    ON r.Region = scs.Region
INNER JOIN
    dbo.DimDate d
    ON d.Date = scs.SaleDate
--INNER JOIN
--  dbo.DimTweet t
--  ON t.
INNER JOIN
    dbo.StagingCarSharePrice scsp
    ON scs.CarModel = scsp.CarModel
        AND scs.SaleDate = scsp.PriceDate

UPDATE: Added separate Twitter Schema
Is this design feasible?

I see a tweet as an event happening, so I would model it as a new fact table FactTweet. More specifically as a factless fact.
The dimensions for FactTweet would be DimDate, DimCar (if you can relate a tweet to a car), DimAuthor and I would probably keep URL and Description as degenerated dimensions. You could potentially add the sentiment of the tweet as well (positive, negative or neutral).

DimCar and DimDate would be  conformed dimensions for both FactTweet and FactCarDetail so you would be able to report on both fact in a single query.


Why is my dev server faster than a much more powerful RDS? I'm running a huge data migration. Probably ~100 million rows total across a few dozen tables.

There is a lot of reformatting. About 50% of it is done through PHP while the rest uses INSERT INTO... SELECT FROM statements with multiple joins.

When I run the script on my dev server (MySQL server is localhost), it takes about 2 hours and 14 minutes total. This machine has 8 GB of RAM, 200 GB storage, and a 4-core 3.1 GHz CPU.

I set up an EC2 instance and an RDS in the same zone (us-east-1a). The RDS is a db.r3.2xlarge instance (vCPU - 8 (cores?), ECU 26, 61 GB RAM, 100 GB storage). I SSH into the EC2 instance and run the script. The PHP portion took 70-80% longer. I expected that, because of network latency connecting to an RDS vs. localhost.

What's more disappointing is that the pure MySQL INSERT queries also took longer. Some of them were the same speed, some were a little faster, but overall they were 35% slower.

The IOPS seemed really low, and the InnoDB buffer usage as well. It really seemed like it wasn't using the resources it has.

Any suggestions? Is this something that Provisioned IOPS could help with?

Update

Different innodb_* variables:

Variable         | Dev server   | RDS
-----------------|--------------|------------
buffer_pool_size | 128 M        | 44 G
flush_method     | (blank)      | O_DIRECT
log_file_size    | 48 M         | 128 M
version          | 5.6.33       | 5.6.19

Looks like my initial theory was correct.

A standard RDS is capped at 3 IOPS per GB of storage, hence why my writes were hovering around 332 on a 100 GB instance. Not sure how they went over 300; maybe Amazon is not strict about it or maybe MySQL Workbench just had latency and calculated it slightly off.

Anyways we launched a new RDS with provisioned IOPS set to 1,000. Now the queries are running 3x faster than they were before.

It does seem strange to me that the limit is based on the storage size. Those two aren't necessarily directly related in my opinion.


Indexing Referenced Table Columns I would like to do a search across columns of joined tables. What would the best index configuration be for the query below?
Table A
------------
Id
ValueA
-- Table A has many more columns

Table B
------------
Id
AId
ValueB
-- Table B has many more columns

At the moment, I have clustered indexes on the Primary Keys and non-clustered indexes (which don't include any columns) for columns AId, ValueA, and ValueB.
SELECT *
FROM TableA a
INNER JOIN TableB b ON a.Id = b.AId
WHERE
    a.ValueA LIKE 'SearchTerm%' OR
    b.ValueB LIKE 'SearchTerm%'

Update
Table A is likely to run into the low millions while Table B will only have low tens of thousands of entries.
In the real world Table A will be a list of transactions, where the value is some sort of a transaction reference. Table B will be a list of users where the value could be anything such as a name or any other descriptive information.
At the moment we use an ORM which will essentially do a SELECT *. Naturally this could be changed, but there would be a fairly large impact on the application side.

Your query is a tricky one for the optimizer, for two reasons.

The optimizer doesn't currently have logic to transform a disjunction (OR) across tables into a union (related Q & A).
The optimizer doesn't reason about deferring lookups (dealing with keys only until the last moment). This relates to the SELECT * component of the given query.

Together, these limitations mean there is very little indexing can do to assist the query as written (ORM generated), leaving aside the possibility of a (nonclustered) column store index.
If performance is inadequate, you may have to bite the bullet and write a custom query. In that case, modifying the existing index on ValueB to include AId would be useful, coupled with a rewrite like the following:
SELECT TA.*, TB.*
FROM
(
    -- Disjunction as union
    SELECT A.Id, b.Id
    FROM TableA a
    INNER JOIN TableB b
        ON b.AId = a.Id
    WHERE
        a.ValueA LIKE 'SearchTerm%'

    UNION

    SELECT A.Id, b.Id
    FROM TableB b
    INNER JOIN TableA a
        ON a.Id = b.AId
    WHERE
        b.ValueB LIKE 'SearchTerm%'
) AS M (A_Id, B_Id)
-- Lookups
JOIN dbo.TableA AS TA ON TA.Id = M.A_Id
JOIN dbo.TableB AS TB ON TB.Id = M.B_Id;

The target plan shape is:

How much better this is in practice depends on several factors, including how selective the LIKE predicates are (and that they are always prefix searches).

Guessed schema:
DROP TABLE IF EXISTS dbo.TableB, dbo.TableA;

CREATE TABLE dbo.TableA
(
    Id integer NOT NULL PRIMARY KEY,
    ValueA varchar(256) NULL INDEX iValueA,
    Padding char(4000) NOT NULL DEFAULT '', -- other columns

    /*INDEX i (ValueA) INCLUDE (Id)*/
);

CREATE TABLE dbo.TableB
(
    Id integer NOT NULL PRIMARY KEY,
    AId integer NOT NULL REFERENCES dbo.TableA (Id) INDEX iAId,
    ValueB varchar(256) NULL /*INDEX iValueB*/,
    Padding char(2000) NOT NULL DEFAULT '', -- other columns

    INDEX i (ValueB) INCLUDE (AId)
);

UPDATE STATISTICS dbo.TableA WITH ROWCOUNT = 3000000, PAGECOUNT = 3000000;
UPDATE STATISTICS dbo.TableB WITH ROWCOUNT = 30000, PAGECOUNT = 30000;


PostgreSQL indexing on bit string What is the best way to create an index on a bit string column? Let's say I had the column of type bit(4) and I wanted to search for all entries that had had a specific bit set. So if I had the entries:

bitfield | ...
--------------
1001
1010
0110
0010
0000

If I was trying to search for all entries that 0010 set, I can easily do that. But can I use indexes to optimize the searching?

First of all: it would be more efficient to store 4 bits of information as separate boolean columns - in every respect: easier to handle, easier to understand and change, easier to index, even smaller storage size! Closely related answer:

Does it make sense to store a couple of Boolean values as array?

To answer your question asked: you can use a partial index like:

CREATE INDEX foo ON tbl (tbl_id)
WHERE bitfield & '0010' = '0010';

& being the bitwise AND operator.
tbl_id being the PK column. (The actual index column hardly matters here.)
In combination with a matching query:

SELECT *
FROM   tbl
WHERE  bitfield & '0010' = '0010';

But your question probably does not reflect your complete situation. There may be better options.


SSMS 2016 Job Schedule Wizard Starting At entry field I am using SSMS 2016. In the wizard for job schedules the field for Starting/Ending At shows only one character, and is unusable. Here is the screen shot.

SSMS version should be the latest at 13.0.16000.28.

Anyone else see or experience this?

This only occurs for me when the text size of my screen has been set to Medium or Large, or when the window is misalgined and needs to be minimized and maximized again.


PostgreSQL 9.1 vs MySQL 5.6 with InnoDB ? Which is better for scaling? PostgreSQL 9.1 vs MySQL 5.6 with InnoDB. I do know that PostgreSQL offers better SQL features.

I am currently using MySQL 5.6 lab release (the one with InnoDb full text search feature)(not in production). I still find some features missing especially WRT GIS features, activation of triggers for on cascade deletion etc.

I have been testing out PostgreSQl 9.1 and am impressed by the support it has for GIS(PostGIS 2.0).

Now, Which of the above is better for scaling? Scaling out as well as Scaling up?

I need to scale for :

around 100k users(the users will increase)
with about 500 concurrent users(will increase say 1000-10000 concurrent).
The DB has about 2 million entries(constantly accessed) right now which will increase.
With constant writes/reads to the table with 2 million entries right now.
SQL, right now, is not too complex with a join to a max of 3 tables.
I am thinking of horizontal as well as vertical scaling.

In scalability, part of the problem is that this is pretty heavily use-case dependent.  There is no scaling solution that hits every use case equally. For example, Slony is very helpful for scaling out in some cases, but is a bear in others.  For example, Slony lets you replicate only part of your database.  On the other hand, Slony lets you replicate only part of your database....  Overall, MySQL seems a little easier to scale out of the box in the workloads it supports but PostgreSQL seems to offer more advanced tools which you can take quite a bit further.

As far as which is more scalable in common use cases, now that Postgres-XC has been released, PostgreSQL is the clear winner here.   We have Slony, Bucardo, Streaming Replication, PGPool, etc. on the replication side.  We have Postgres-XC on the scale out clustering side.  We are extremely scalable now.

However, all these come with complexity costs.  These costs appear a bit higher on the Pg side, but the flexibility is there to scale however you need to.

Scaling up right now is also a major PostgreSQL goal.  9.2 makes some important gains in that regard.


How to isolate that network is issue of bad query performance I would like to know how to isolate SQL Server bad query performance is caused by NETWORK and not the SQL Server.

Are there any specific tips and tricks in SSMS, wait types, or specific PerfMon counter permissable values as far as network is concerned? How do I baseline the network related counters and what values should I consider as good/better/poor?

Waits and Queues is a good resource for this problem. Is a methodology for investigating SQL Server performance problems. It will teach you and help you find the bottleneck, which is better than to make assumptions and try to prove your assumption. Anyway, the paper has specific advice on how to identify Network as a bottleneck. A quick check is ASYNC_NETWORK_IO wait type stats, high values indicate frequent stalls waiting for Network IO sent to the client to complete (ie. marshaling the results is slow). Read the paper for more details.

Note that well designed applications seldom are affected by network bandwith. Short of special ETL scenarions, a high bandwith requirement is an indication of bad app design, not of network problems. High Network latency also affects chatty applications more, and chattiness (many round-trips) is again a smell of bad design. Both network badnwith and latency are trivial to test and measure, again read the paper linked for methodology.


What is a SQL Server "GDR"? Microsoft provides hosts two repos,

https://packages.microsoft.com/repos/mssql-server-2017-gdr-xenial/
https://packages.microsoft.com/repos/mssql-server-2017-xenial/

What does GDR stand for in that context.

You can find it on Microsoft sites but they don't explain it

  GDR update package for SQL Server 2016 SP1

You can see it here too.

A General Distribution Release (GDR) is simply a kind of hotfix, or patch that contains specific fixes, and is intended for installation on all SQL instances.  This is in contrast to a Quick Fix Engineering (QFE) hotfix, which is intended to be installed only if you experience a particular issue.

After a hotfix, it will also be included in the next Cumulative Update (CU) or Service Pack (SP).  But you can install the hotfix without taking all the changes included in the next CU/SP.

Going forward GDR hotfixes are only going to contain security fixes.

For SQL Server 2017+ a GDR is part of the Modern Servicing Model (MSM), which is replacing the older Incremental Servicing Model (ISM), which will continue to apply to SQL Server 2016 and before.  It's explained here.


Using pg_dump to create 144 backups per day Imagine the following scenario (this may not be a viable or sensible idea but please imagine that it is).

Using Ubuntu, a cron job calls pg_dump and backs up a Postgres database every 10 minutes. In other words: 144 times per day.

The goal is to ensure that data which is deleted from the original "live" database is maintained in the backups. All deleted data is preserved in the backups. Since the data is deleted regularly, there needs to be backups every 10 minutes.

Would the cron job calling pg_dump create 144 individual backups?

Can these 144 backups be merged together after every 24 hour period and information extracted with SELECT?

The issue also is that one might only want to check the data in a specific table yet the whole database is backed up.

What would be the best way to achieve this?

Thank you.

That is silly You should instead take one pg_basebackup per day and archive the transaction log with archive_command. That way. you can use point-in-time-recovery to restore the database to any given point in time.

See the documentation for more.


Sql. Calculating the size in KB of each row I'm building now a new sql 2005 database for my company, and we expect this database to be huge with 100's of millions of records. We already know we need to dedicate a separate server with new hard drives for this task. I need to know how big of hard drives to get. I don't want to just go now and buy big and simple hard drives. I think it's better to get the small but very fast ram drives or other solutions that cost a lot.
I would like to know in a specific sql table what will be the size of each record then i'll multiple it with the amount of records i expect, so i'll know what will be the size of my database in a year/2 years...
Is there a tool that will be able to estimate it?

Thanks.

If you already have some representative sample data, just run the following to find the average row size:

sp_spaceused 'Tablename'

Otherwise you can estimate its size without indexes with this function:

CREATE FUNCTION available_tablerowsize
(
-- Add the parameters for the function here
@tablename char(50)
)
RETURNS int
AS
BEGIN
-- variables to track fixed and variable column sizes
DECLARE @num_columns int
DECLARE @result int
DECLARE @num_fixed_columns int
DECLARE @fixed_data_size int
DECLARE @var_data_size int
DECLARE @num_var_columns int
DECLARE @max_var_size int
DECLARE @null_bitmap_size int
DECLARE @row_size int

-- Find the total number of columns
select @num_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype

-- Find the size occupied by fixed length columns (Note: not possible to exist outside the 8060 bytes limit)
select @num_fixed_columns = count(*)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0

select @fixed_data_size = sum(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=0

-- Find the size occupied by variable length columns within the 8060 page size limit

-- number of variable length columns
select @num_var_columns=count(*)
from syscolumns, systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- max size of all variable length columns
select @max_var_size =max(syscolumns.length)
from syscolumns,systypes
where syscolumns.id=object_id(@tablename)
and syscolumns.xtype=systypes.xtype and systypes.variable=1
-- calculate variable length storage
begin
if @num_var_columns>0
set @var_data_size=2+(@num_var_columns*2)+@max_var_size
--set @var_data_size = @num_var_columns*24
else
set @var_data_size=0
end

-- If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability.
select @null_bitmap_size = 2 + ((@num_columns+7)/8)

-- Calculate total rowsize
select @row_size = @fixed_data_size + @var_data_size + @null_bitmap_size + 4

-- Return the available bytes in the row available for expansion
select @result = 8060 - @row_size

RETURN @result

END
GO


Query WHERE clause and JOIN I'm having an issue using a WHERE clause and JOIN.

SELECT * FROM `CallDetailRecord`
  WHERE `StartTime` >=1357102799000
  AND `StartTime` <=1357880399000
  JOIN `CallEvent` ON `EventID` = `CallEventID`

  Error Code: 1064
  You have an error in your SQL syntax; check the manual that corresponds to your MySQL       server version for the right syntax to use near 'JOIN CallEvent ON EventID = CallEventID
  LIMIT 0, 1000' at line 1

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.004 sec
---------------------------------------------------

I'm just trying to limit data set by time "starttime', but I get query error and it refers to
join.

Any ideas? I will be happy to provide more info if required.

Your WHERE clause must be after your join, perhaps?

SELECT *
FROM CallDetailRecord
    JOIN CallEvent ON EventID = CallEventID
WHERE StartTime >= 1357102799000 AND StartTime <=1357880399000

Note: You may want to prefix the fields with table names or aliases...


Difference between an Entity and a Relation I am currently reading about entities and relations and I can't understand the difference between them? According to Wikipedia we have an example of a relation like in the pic:

This looks exactly like an entity set. What's the difference between an entity and a relation?

"Entity" is a real world concept, such as "a person" in your example.

"Relation" is a set of tuples (records), each representing a model of an entity. In your example each tuple represents attributes of a single person, and their collection is a relation.


storing data from different sources in a single column? I want to ask a general question. We are getting data from different stores like magento, woocommerce, amazon aws, shoppify and many others. Data like customers info, orders info, products info is being fetched. All the stores are returning data that is bit same, bit different. Here are two database designs that we are proposing:

Use different columns for every store like magento_customers, shoppify_customers but this do not seems right.
Make a single column for customers from all sources. fill the fields that has data and make others null, like magento returns group_id, but woocommerce do not, so fill group_id by '1' lets suppose and when it's null, keep it null.
I am a junior developer, and this is my first commercial project so I'm little hesitant.

It sounds like magento customers, shoppify customers, etc. are subclasses (subtypes) of the customer class.  This is the terminology in object modeling.

In ER modeling, the terminology is usually generalization/specialization.

There are two techniques often used when designing tables to fit this situation.

One is single table inheritance.  Store all the customers in one table, with a customer_type field, and all the fields for all the types.  Leave fields NULL when they don't apply to a particular customer.

Another is class table inheritance.  Have one table for customers with all the common data, and only that data.  Have separate tables for each subclass (subtype) of customer, with data that is specialized to that subtype.  In the subclass tables, don't generate a distinct id for the rows, because you don't need to.  Use a copy of the customer id as both the PK for the specialized table and an FK to join with the correct row in the generalized table.

Joining specialized data and generalized data is pretty fast, and fairly simple.

With either technique, the problem you originally posed goes away.  You can make an FK reference to the customer table, and access both the generalized data and the specialized data.

If you search on all the buzzwords I've given in here, you'll get lots of useful articles on the web that treat the subject in more depth.  You can also explore the subtypes tag right here.


AWS RDS Post Optimisation after a huge delete I'm using AWS RDS MySQL. All the tables are in InnoDB and enable innoDB_file_per_table.

One of my logging tables is growing too fast. I'd like to remove older than 1-week data. Even 1 week of data is 120GB. Every weekend this job will run and remove the old data.

I want to reclaim the disk space and update the statistics. OPTIMIZE table is really painful for us.

What are the optimizations I should perform after deleting a huge number of records?

You may try below options.

Using Partition with DateRange

Create table with date range each partition containing weekly data.
Things to consider are selects and updates which means you should use partition name to specify any where conditions other than PK. Which means that there must be a code change.
Have a job that does:

alter table drop partition 1WEEKAGO_PARTITION_PARTITION-NAME

This solves quick removal of older data.
Rotate Table

Let's say your big table is table_a. Create an empty table, call it table_b, with a similar structure to table_a.
Once a week, follow below operations in non-peak or night hours for the database.

Create 3 triggers on table_a after insert/update/delete. Have it apply the same changes to table_b.
Now take the data from last week from table_a and apply it to table_b in 1000-record batches until today.

insert into table_b
select * from table_a
where date > date_time(LASTWEEK'S DAY)
and date < date_time(TODAY_TIME_AFTER_ADDING_TRIGGERS);

You may break down the where condition in thousand chunk records do this for the above where clause.

Ensure that the insert from table_a to table_b is started once if triggers have started to populate the data.
rename table_a to table_old, and table_b to table_a.
Once operations are going fine after rename, you may go ahead and drop table table_b.
Don’t forget to drop the triggers.

This solves weekly defragmentation and space usage. But it is a lengthy process.

For option 2 (Rotate Table), I also had reservations when touching a table that had 200 DMLs per second, and per week data of more than 200 GB. It worked. But you may simulate this in test environment and see the throughput. You should have solid confidence in the response time to apps and commands to trigger. Anyway your call. It's just the direction I could suggest which I have crossed. All the best.


.WRITE clause performance optimization I have found that you can do high performance updates on varchar(max),nvarchar(max),varbinary(max) columns using the .WRITE clause(example here and here). Unfortunately, there is nothing mentioned about performance optimization in the official documentation.

Could anyone tell is there any optimization at all? Has anyone made some performance test? Also if there is optimization I guess it will affect only specific situations, too.

The WRITE method is minimally logged. If you use regular UPDATE statement, it would result in overwriting the entire string using FULL LOGGING. This would become inefficient when dealing with large updates.

To support update for large value data types, the UPDATE syntax supports .WRITE method. This will result in less Transaction log due to its nature of minimal logging - including insert or appending new data.

Note : The UPDATE using WRITE method will fail if the target LOB is NULL.

Below is a quick and dirty repro showing the transaction log generated using REGULAR UPDATE and UPDATE using .WRITE method.

--- create table

create table dbo.testWRITEUpdate (
    ID int identity(1, 1)
    ,COMMENTS varchar(MAX) not null
    )
go

insert into dbo.testWRITEUpdate (COMMENTS)
values (REPLICATE(CAST('KIN' as varchar(max)), 9000))

checkpoint
go

--- Regular Update:

set statistics io on
 begin tran
update dbo.testWRITEUpdate
set COMMENTS =  STUFF(COMMENTS, 9, 55, '$$$$$(((())))))))))))))____GarbageData____ Entered ____')
where ID  =1

set statistics io off
go

commit tran

-- stats IO output

 Table 'testWRITEUpdate'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 15, lob physical reads 0, lob read-ahead reads 6.

-- Transaction Log Generated --> 6 Log records with 54580 bytes:

Update using WRITE method :

begin tran
set statistics io on
update dbo.testWRITEUpdate
set COMMENTS.WRITE('$$$$$(((())))))))))))))____GarbageData____ Entered ____',9,55)
where ID  =1
set statistics io off

commit tran
go

-- Stats IO

Table 'testWRITEUpdate'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 1, lob physical reads 0, lob read-ahead reads 0.

-- Transaction Log Generated --> 3 log records with 464 bytes:

The above test proves that the WRITE method provides more benefit in terms of Transaction log generated as it is minimally logged operation + incurring less logical reads.

Note: This is just scratching the surface, there can be more tests done to actually prove in terms of performance if WRITE method is performing better than the regular UPDATE especially when dealing with VARCHAR, NVARCHAR OR VARBINARY datatypes.

Update (As requested by OP):

Below is the query for finding the sys.dm_tran_database_transactions

SELECT [database_transaction_log_bytes_used] FROM sys.dm_tran_database_transactions
WHERE [database_id] = DB_ID ('test_kin');
GO

--- or a more detailed version :

SELECT DTST.[session_id],
 DES.[login_name] AS [Login Name],
 DB_NAME (DTDT.database_id) AS [Database],
 DTDT.[database_transaction_begin_time] AS [Begin Time],
 -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Duration ms],
 CASE DTAT.transaction_type
   WHEN 1 THEN 'Read/write'
    WHEN 2 THEN 'Read-only'
    WHEN 3 THEN 'System'
    WHEN 4 THEN 'Distributed'
  END AS [Transaction Type],
  CASE DTAT.transaction_state
    WHEN 0 THEN 'Not fully initialized'
    WHEN 1 THEN 'Initialized, not started'
    WHEN 2 THEN 'Active'
    WHEN 3 THEN 'Ended'
    WHEN 4 THEN 'Commit initiated'
    WHEN 5 THEN 'Prepared, awaiting resolution'
    WHEN 6 THEN 'Committed'
    WHEN 7 THEN 'Rolling back'
    WHEN 8 THEN 'Rolled back'
  END AS [Transaction State],
 DTDT.[database_transaction_log_record_count] AS [Log Records],
 DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used],
 DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],
 DEST.[text] AS [Last Transaction Text],
 DEQP.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions DTDT
 INNER JOIN sys.dm_tran_session_transactions DTST
   ON DTST.[transaction_id] = DTDT.[transaction_id]
 INNER JOIN sys.[dm_tran_active_transactions] DTAT
   ON DTST.[transaction_id] = DTAT.[transaction_id]
 INNER JOIN sys.[dm_exec_sessions] DES
   ON DES.[session_id] = DTST.[session_id]
 INNER JOIN sys.dm_exec_connections DEC
   ON DEC.[session_id] = DTST.[session_id]
 LEFT JOIN sys.dm_exec_requests DER
   ON DER.[session_id] = DTST.[session_id]
 CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST
 OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;
-- ORDER BY [Duration ms] DESC;


How can I create readonly user for backups in PostgreSQL? Is it true that it is IMPOSSIBLE to create a readonly backup user in PostgreSQL?

I've been advised on an IRC channel that you simply can't have a backup only user with no ownership privileges.  I find it very strange so I want to make sure I'm not missing something.

Below is what I tried but it doesn't give me the results I'm looking for.  When I do pg_dump on a given table I'm getting Permission denied for relation...:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO backup;

Any help would be greatly appreciated!

No, it's easy (now anyway).

Grant the connect permission on a new user

GRANT CONNECT ON DATABASE mydb TO myReadolyUser;

Grant the permissions on all the current database objects. This is schema-specific, and you'll have to run one copy for every schema you wish for your user to use,

GRANT SELECT ON ALL TABLES IN SCHEMA mySchema TO myReadonlyUser;

From the docs, ALL TABLES includes everything you'd want.

  There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, and functions (but note that ALL TABLES is considered to include views and foreign tables.

Then ALTER DEFAULT PRIVLEGES to grant future SELECT privileges for objects not yet created.

ALTER DEFAULT PRIVILEGES IN SCHEMA mySchema
GRANT SELECT ON TABLES TO myReadonlyUser;


cumulatively calculate the distance along a line I am using Postgis to:

calculate the cumulative distance in metres along the line
store the cumulative distance in "m"

I have a table with points on this line and each row holds lat and lon coordinates. The "m" value is 0 for all rows.
The following code nicely gets me the distance between the first point and the next.
SELECT a.geom
FLOOR(
ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521))
AS dist
FROM line a
LEFT JOIN line b
ON a.id = b.id + 1

however, it does not add up to a total nor does it update the "m" value yet.
I tried a set of permutations of the following, but none of them store a cumulative sum of the previous row's value for "m" plus the calculation of the distance between this point and the previous.
WITH next AS (
    SELECT
    ST_Distance(ST_Transform(a.geom, 3857), ST_Transform(b.geom, 3857)) * cosd(42.3521)
    AS dist,
    a.id AS rowid,
    FROM line a
    JOIN line b
    ON a.id = b.id + 1
)
UPDATE line a
SET m = FLOOR(next.m + next.dist)
FROM next
WHERE a.id = next.rowid
RETURNING a.m, next.dist;

or
update line a
    set m = FLOOR(a.m + prev.dist)
    from (select
            l.*,
            ST_Distance(ST_Transform(
                lag(geom) over (order by l.id asc)
            , 3857), ST_Transform(geom, 3857)) * cosd(42.3521) as dist
            from line l
          ) AS prev
    where prev.id = a.id - 1
    returning prev.m, prev.dist, a.m

Any ideas?

Fiddling around with cosd() is silly, especially when you do not know the actual latitude. ST_Distance can compute the desired value directly if you give it geography values; if you have lon/lat geometries, you can simply cast them.
To add up the lengths step by step, use a recursive CTE:
WITH RECURSIVE cum(id, geom, dist) AS (
  SELECT id,
         geom,
         0::double precision
  FROM line
  WHERE id = 1

  UNION ALL

  SELECT line.id,
         line.geom,
         cum.dist + ST_Distance(cum.geom::geography, line.geom::geography)
  FROM cum
  JOIN line ON cum.id + 1 = line.id
)
UPDATE line
SET m = dist
FROM cum
WHERE line.id = cum.id;


Audit Login and Logout Good morning, please, has anyone used logins audit without using the Profile?

My client needs an audit that displays the logins that connected to the bank and after they disconnected.

In the case the profiler believe it is too heavy to keep active all the time collecting.

Do you have anything that might help us in this case?

thank you
Carol Lavecchia

Seems like a perfect use case for Login Auditing. This will require a restart of the SQL Server.

Then, I would use xp_readerrorlog to view the results. Something like:

xp_readerrorlog, @p1 = 1, @p3 = 'Login'


Which text-index I should create for xooops engine to achieve better search results? In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following :

SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, p.post_id, p.subject
            FROM xps33_bb_posts p,
            xps33_bb_posts_text pt,
                xps33_bb_forums f WHERE p.post_id = pt.post_id AND p.approved = 1 AND p.forum_id = f.forum_id AND f.forum_id IN (1,4,61,7,9,17,20,45,35,44,38,39,43,53,54,55,56,57,58,60,14,29,40,26,18,41,33,24,32,59,25) AND ((p.subject LIKE '%rivi%' OR pt.post_text LIKE '%orvi%') AND (p.subject LIKE '%care%' OR pt.post_text LIKE '%gor%'))  ORDER BY p.post_time DESC LIMIT 0, 5;

I can't change them as It would involve changing the engine which is not an option atm. But I can help the engine to search faster. As I understood as the table uses MyIsam engine I can create text indicies which should make search faster, am I right?

So in general for which indicies I should create to avoid following queries run for long time?

+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref                | rows   | Extra                       |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
|  1 | SIMPLE      | p     | ALL    | PRIMARY,forumid_uid | NULL    | NULL    | NULL               | 144090 | Using where; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY             | PRIMARY | 4       | diginew.p.forum_id |      1 | Using where                 |
|  1 | SIMPLE      | pt    | eq_ref | PRIMARY             | PRIMARY | 4       | diginew.p.post_id  |      1 | Using where                 |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
3 rows in set (0.00 sec)

Your understanding isn't quite correct.  No type of index on MySQL can be used to optimize LIKE '%string%' expressions.

LIKE 'string%' can use an ordinary b-tree index, but this only matches strings at the beginning of the column's value, which wouldn't help you.

You're correct that MyISAM (and InnoDB in MySQL 5.6) support full text indexes, but to query them, you have to use the a fulltext search query, which looks like this:

... WHERE MATCH (col1,col2,...) AGAINST (expr [search_modifier])

As things are now, if you do not have a multi-column index on (approved,post_time) in the xps33_bb_posts table, it seems like you should at least want that as a minimum, since the optimizer could then choose to read the only rows with approved = 1, ordered from newest to oldest, and the query would finish as soon as the first 5 rows were found (or whatever the LIMIT of the particular query required).

If you could take an example from your slow query log and post the output from EXPLAIN SELECT, you may get some additional helpful suggestions.