MariaDB crashes on mismatched tablespace ID for InnoDB I'm having an issue with some corrupted InnoDB tables on a stand-alone MariaDB 10.1 server running on Debian Stretch. I've encountered this issue in the past, but never found a working solution, so I ended up just rebuilding the DB instance. I'm trying to see if there's a way to recover without a rebuild.

Here's the situation:

We have a Drupal site that uses InnoDB for pretty much all of the tables, and there are a handful of tables that are used for caching (named cache_). The contents of the tables are temporary, so the data within them doesn't matter. What happened is that multiple cache_ tables became corrupted somehow and cause MariaDB to crash whenever they are accessed (even something as simple as SHOW TABLES). My guess is the corruption occurred because of TRUNCATE operations that somehow caused a blip in MariaDB when they were rebuilt.

Some key MariaDB settings:

ignore_builtin_innodb
plugin_load = ha_innodb.so
innodb_file_per_table = 1

Sample log messages are below (trimmed for size):

[ERROR] InnoDB: tablespace id is 1479582 in the data dictionary but in file ./mydb/cache.ibd it is 1480371!
[ERROR] InnoDB: Trying to do i/o to a tablespace which exists without .ibd data file. i/o type 10, space id 1479582, page no 0, i/o length 16384 bytes
.. (above repeated 100 times) ..
[ERROR] InnoDB: Unable to read tablespace 1479582 page no 0 into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. You can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/5.6/en/ for more details. Aborting...
.. (skip the crash report lines) ...
InnoDB: Error: table 'mydb/cache'
InnoDB: in InnoDB data dictionary has tablespace id 1479582,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mydb/cache and id 1480371, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

From a technical standpoint, I understand why the error is happening (the internal tablespace ID in the .ibd file doesn't match what the global InnoDB data dictionary thinks it should be). From my research, a TRUNCATE operation on a table will recreate the table with a new tablespace ID. The MySQL manual states the TRUNCATE will not change the tablespace ID (I believe starting in 5.7, I'm having trouble finding the reference again). MariaDB doesn't seem to have this patch incorporated (you can verify by looking at the INFORMATION_SCHEMA.INNODB_SYS_TABLES table before and after a TRUNCATE).

Things I've tried which all result in a crash:

Pretty much anything on the trouble shooting page referenced in the logs (see above)
TRUNCATE cache
DROP TABLE cache
ALTER TABLE cache DISCARD TABLESPACE
Deleting the .idb and keeping the .frm file
Setting innodb_force_load_corrupted=1 in config
Doing all the above with innodb_force_recovery set anywhere from 1 to 6

Ultimately, the reason behind the corruption is not as important to me as is being able to clean up the corrupted tables. I have no problem removing them completely, but any operations on the table cause the DB server to crash. Most of the other solutions I've found either involve a different type of corruption that one of the above commands can fix, or they're using MySQL and it behaves slightly differently.

Suggest you turn off caching.

Often, having a "cache in front of a cache" provides little or no performance benefit.

Meanwhile, upgrade from 10.1; there are likely to be stability fixes in 10.2 or 10.3.


Alternative to FK for sys.objects reference I'm updating an audit/log table that is populated by trigger when a table is updated. My intention is to use the sys.objects.object_id for table name and column name columns in the log table but it's not possible to use a FK reference for the system views. Is there any useful alternative to maintain referential integrity on the table?

You could use a trigger on the log table that rolls back when the object_id being inserted is not found in sys.objects - since triggers are the typical mechanism we use when a foreign key doesn't make sense or isn't possible (like maintaining integrity across database or instance boundaries).
But do you really want to lose all of the data because one column represents an object that no longer exists, or never existed? (Maybe that's ok; I don't what you're auditing/logging.) What is the likelihood of this happening since the trigger produces the object_id, and the system isn't known for messing up?
I'm not sure I understand the point anyway; while a foreign key to sys.objects would prevent someone from logging information about an object that doesn't exist, the presence of just one row in the logging table would make it impossible to ever drop that object. (Maybe that's ok too, but it doesn't sound wise.)
If the fear is that someday a user reviewing log history will hit a dead end because the object has since been dropped, a trigger that made sure the object existed when the row was inserted isn't going to have saved you from that scenario anyway. You'd need a DDL trigger maybe, on DROP_TABLE, so that either the log history associated with that table is purged, or the object_id is moved somewhere else so that you can still track down what that table used to be.


Database cannot be opened. It is in the middle of a restore I have executed these two commands:

USE master
GO

RESTORE DATABASE test
FROM Kingston
WITH NORECOVERY
GO

USE master
GO
RESTORE LOG test
FROM Kingston
WITH STOPAT = '2020-05-13 14:05:25', RECOVERY;
GO

But now I get this error while accessing the database with USE test GO:

  Database 'test' cannot be opened. It is in the middle of a restore.

When it clearly isn't. Any suggestions how to fix it? So far found out that RECOVERY helps, but as you can see it doesn't.

Edit

Changing NORECOVERY to RECOVERY doesn't help, because then I'm unable to execute 2nd command (throws error), and I need that log at specific moment.

Edit

I have found the solution. You need additional command:

RESTORE DATABASE test
WITH RECOVERY
GO

It appears that RECOVERY is ignored in RESTORE LOG command, and it needs additional command to confirm that restoring has been finished. I appreciate the help though.

This

USE master
GO
RESTORE LOG test
FROM Kingston
WITH STOPAT = '2020-05-13 14:05:25', RECOVERY;

Will not perform recovery if the STOPAT time is outside the range of times in the log backup.  This allows you to specify the STOPAT for multiple log backups, and the database will only run recovery when you've applied the log backup containing the target time.

From the docs:

  The RECOVERY and STOPAT options. If the transaction log backup does
  not contain the requested time (for example, if the time specified is
  beyond the end of the time covered by the transaction log), a warning
  is generated and the database remains unrecovered.

Restore a SQL Server Database to a Point in Time (Full Recovery Model)


Case When over 2 columns return results into 1 output column I have an issue in SQL Server (using 2014 but not sure that's relevant).  I have 2 variables Q20 and Q46 which depending on result I want to output a value to the same column for each result.  For example (The promoter column being the one that is generated with the Case expression)

Rf........Q20.........Q46........Promoter

1.............0.............9.............1

2.............8.............0.............1

3.............6.............0.............0

The Query I am trying to goes something like this (but obviously this isn't working):

select *
,case Q20
    when '8' then 1
    when '9' then 1
    else 0 end
,case Q46
    when '8' then 1
    when '9' then 1
    Else 0
end as Promoter
    from ##csat_resp

I have tried various connotations of this but nothing works that will bring both the results into one column (Promoter).

I refuse to believe this isn't possible and must be down to my lack of knowledge.

Please help!!

Something like this (untested) should work for you:

select
  *,
  case q20
    when '8' then 1
    when '9' then 1
    else case q46
      when '8' then 1
      when '9' then 1
      else 0
    end
  end as promoter
from ##csat_resp;

You could also write the same logic as:

case when q20 in ('8','9') or q46 in ('8','9') then 1 else 0 end

or in SQL Server 2012 and later:

IIF(Q20 IN (8, 9) OR T.Q46 IN (8, 9), 1, 0)


Impact of an Asynchronous Mirror / Full Recovery Model on Performance For an asynchronous mirror in SQL Server 2008 R2, using the full recovery model is required.

Assuming that the network and disk IO on both sides of the Mirror can keep up with the transaction logs and mirroring, are there still performance penalties for this over not having a mirror and having the database in simple recovery? If so, what kinds of operations are effected and what causes them to be effected?

With an async mirror there should be little to no performance impact on the active system.  When you are in full recovery there is more transaction log activity on the system than if the database was in simple recovery mode, but that's about it.  Are you seeing specific waits or something that prompted the question?


Replication from log shipping database I have a secondary database on a standby and read only mode.
The question is .. Can I replicate this database ?

No. However, there are some capabilities supported with log shipping and replication on the primary. https://msdn.microsoft.com/en-us/library/ms151224.aspx

As Aaron stated, you could add an additional secondary. If you wanted to use replication to handle subsets of the primary data, or build out a better formatted reporting server (different indexes, subsets of data through filtering, etc), then I would recommend replicating from the primary.

Hope this helps!


Can Postgresql exploit "accidental" clustering in plans? Suppose I have an append-only table with columns customer_id (randomly generated string) and x, and lookups are always done on customer_id.
Say the data look like below, as if we get a batch of rows when a customer signs up for something initially, and then never again for that customer.
customer_id=XCVFY0001, x=...
customer_id=XCVFY0001, x=...
(continues for ~1 page with same customer_id)
customer_id=HUMBN0001, x=...
customer_id=HUMBN0001, x=...
(continues for ~1 page with same customer_id)
(and so on...)

So while customer_id's alphabetical ordering isn't correlated with physical rows, we could make statements like:

there are few distinct customer IDs per page
there are few pages per ID
there are long "runs" of IDs, or, if you need one customer_id, you're likely to find it on a few contiguous pages
in terms of information theory, I think they'd say there's no correlation, but there is high "mutual information"

Can the query planner use information like this in estimates, if one doesn't explicitly run CLUSTER? I'd assume that if there's low correlation as reported in pg_stats, it would guess that rows are uniformly distributed throughout pages, and might be pessimistic with various plans.
(In my real-world analogue, a plain non-clustered index made things nice and fast anyways, but I just got curious when I noticed the pattern in the data.)

The planner is unaware of this type of clustering and so can't make decision based on it.
The two-step sampling method used by ANALYZE can generate skewed samples in this situation, possibly leading to a drastic underestimate of n_distinct.  It is hard to predict what the consequences of that might be without digging into the details of individual queries.


Pattern matching and substring replacement in PostgreSQL I would like to do pattern matching and substring replacement in PostgreSQL with a "select-case-when-then-update-query".
Example:

tableA

field1               |field2          | field3
---------------------+----------------+-----------
(varchar)bla abla 123|(varchar)abla   |(varchar)123
(varchar)blub 456    |(varchar)blub   |(varchar)456

remaining string in field1 after performing the substring replacement should be "bla" in row 1 and "blub 456" in row 2 (see Note below):

tableA

field1               |field2          | field3
---------------------+----------------+-----------
(varchar)bla         |(varchar)abla   |(varchar)123
(varchar)blub 456    |(varchar)blub   |(varchar)456

So, I would like to update field1 if the following condition is true:

Pseudocode:

if string from field2 and/or string from field3  is substring of field1
then replace substring field2, field3 from field1 if remaining string in field1 > 0

Note: If there is no string or token left in field1 after substring removal the string should not be touched.

You haven't disclosed what to do with whitespaces or what to do with overlapping substrings.  Anyhow, something like this could help:

UPDATE tableA
SET field1 = replace(replace(field1, field2, ''), field3, '')
WHERE replace(replace(field1, field2, ''), field3, '') <> '';

There is no pattern to match, simply replace a string (in an other column) with nothing.  You don't have to care about substrings as if the value of field2 does not occur in field1 then it won't be replaced.

See this on SQLFiddle.


What to do when you need a cursor and you have a columnstore clustered index I have a very large SQL Server table with a ColumnStore clustered index.  The table is very fast to query, so this is good.  However I need to do some updates to a large portion of records in the table.  When I tried a conventional update statement, my transaction log filled up.  Normally I would handle this by using a cursor to use an additional part of the composite key so I can update batches and run the update statements in a loop.  However I have just learned via the error message that this won't work on a ColumnStore table with a clustered index.

Can someone please suggest an alternative method for updating the data?

Should I just turn transaction logging off?  That worries me.

One thing I can't do is add more space to my transaction log.  It's already quite large.

declare @ig as cursor;
declare @pname varchar(200)

set @ig = cursor for
select distinct injection_group from mytable where
ProductGroup = 'Widget'
and target_month = '1/1/2018'
open @ig;

fetch next from @ig into @pname;

while @@FETCH_STATUS = 0
begin
    print @pname;
    update
    mytable
    set injection_group = replace(injection_group, '  ', ' ')
    where productgroup = 'Widget'
    and target_month = '1/1/2018'
    and injection_group = @pname
    fetch next from @ig into @pname;
end

close @ig;
deallocate @ig;

  Msg 35370, Level 16, State 1, Line 5
  Cursors are not supported on a table which has a clustered columnstore index.

This answer is written from the point of view of SQL Server 2016, but I believe that most of the important details are the same.

Updates are extremely expensive from a transaction logging point of view. They are implemented behind the scenes as a delete + insert. The delete has a fixed transaction log cost per row. If I remember correctly it's about 200 MB per 1 million rows deleted. The insert can only go to the delta store, so the data is written as heaps. These heaps are uncompressed in 2016 and page-compressed in 2014.

It's a best practice to do delete + inserts instead of updates against columnstore indexes. If you're truly processing most of the rows in the table you could just insert all of the new data into a new copy and switch the tables when you're done. Nearly all of the inserted rows will bypass the delta store and be written to compressed rowgroups instead. Writing data to compressed rowgroups requires significantly fewer transaction writes compared to writing to the delta store.

In SQL Server 2016 you can significantly improve performance with such an approach. The same is true for SQL Server 2014 according to Niko's article.


Password of the oracle user in oracle linux? I am trying to install oracle 12c on my virtualbox oracle linux 6.5. I ran the command oracle-rdbms-server-12cR1-preinstall , which created a user oracle . But I can't seem to login to the user cause I don't know the password. What is the password ?

The oracle-rdbms-server-12cR1-preinstall package includes a bash script that creates the oracle user, if not already existing, using the useradd command.

In this case in log file /var/log/oracle-rdbms-server-12cR1-preinstall/results/orakernel.log you should see this entry:

  Please set password for oracle user

You can run the following command as root to assign a password:

# passwd oracle


Why does a table of varchar(255) columns take up less space than an identical table using the correct data types I have two tables that are identical and hold the same rows of data.

one uses a varchar(255) for every column of the table, the other is correctly typed for each column (using date, int, decimal etc.).

Using Management Studio's properties window, I can see that the table that stores all data as varchar is far smaller, which is not what I expected to see. My understanding was that an int would only use 4 bytes of space and therefore use much less space than an int stored as a varchar.

What should I look for to understand what is happening here? Both tables have a primary key on an int column and no other indexes. For testing purposes they have been populated with identical datasets from the same source query.

So this turns out to be because of the shape of the data I am storing, IN THIS INSTANCE a varchar genuinely takes up less space.

Many of the columns in the table are ints and numeric(38,12) (this is so huge because the data source uses this type). However, many of the values in these columns are 0 or whole integers. As a result a varchar(255) would store this value as 3 bytes (1 byte, plus 2 the two overhead), whilst a int would use 4 and the numeric would use 17. This results in using the correct types to be less storage efficient over the whole table.

To decrease the storage space used, I would need to check if I can change the typing (from int to smallint or numeric(32,12) to a numeric (19,8) etc.) alternatively it looks like sparse columns would be a good solution where there are lots of 0's or null expected.


What could justify a non explicit - non atomic primary key I'm working as a "guest" dev at a random company.

I'm working with a table that does not have a primary key by definition(Oracle).

The column named "SOMETHING_ID" is varchar2(10) and constructed at insert time
by the following operation. And it is used as a primary key and referenced by other tables.

'A_STRING' || LPAD(SEQUENCE_NAME.NEXTVAL, 7, '0')

Some examples of the primary key data are

CH-00004321
CH-00004322
CH-00004323

My questions are,

first, in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ?

Second, in what circumstances would it be justified not to have a primary key defined but you have a column that distinctively distinguish each row ?

Third, is my perception correct that I see the combination of the type string(CH, IN, MX) and a sequence number is against the 1st normalisation ?

Fourth, in the table, there is only one "type string" value (let's say CH).
Is it okay to add that string to the pk while it does not seem necessary since there is only one value ? (The nature of the table does not seem to allow any other value for the type string though unlike my example of food. Perhaps you can see the table name as "CHINESE_FOOD", hence no other type string value would seem logical.)

Note - 1. Actual column, sequence names are replaced with arbitrary names.
2. The aim of this software is prediction of the damages caused by natural disasters.

in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ?

Under no circumstances would it be justified.  A column containing two different data elements as one value is a violation of first normal form.  1NF requires, among other things, that each column contain a single value.  This practice also destroys the guaranteed logical access of every data element by table name, column name, and key value.  Now if each data element were in its own column, and the combination of columns made up a key, that is not a problem.  Ultimately this depends on the entity type for which the table holds entity occurrences.  The key chosen should be something used by people in the real world to identify occurrences.  Perhaps this makes sense if we are talking about a menu, and the menu has a section for Chinese food, and then gives each dish a number like #1, #2, etc.

  Second, in what circumstances would it be justified not to have a primary key defined but you have a column that distinctively distinguish each row ?

Again, under no circumstances.  If there is a column whose values uniquely identify each row that column must be declared as a unique constraint so as to ensure the occurrences entered are not duplicated and thus become inconsistent with the real world entities they represent.

  Third, is my perception correct that I see the combination of the type string(CH, IN, MX) and a sequence number is against the 1st normalization ?

Absolutely, as described above.

  Fourth, in the table, there is only one "type string" value (let's say CH). Is it okay to add that string to the pk while it does not seem necessary since there is only one value ?

No, for the same reason as this violates 1NF.  Again that column contains 2 different values and each column must contain only a single value to be in 1NF.

Fabian Pascal's Practical Database Foundation Series is a great reference to dive more deeply into these fundamentals.  Paper #4 addresses keys specifically.


MySQL Error: FUNCTION LEVENSHTEIN already exists I've got an ExpressionEngine database and I exported a couple of tables from it, then dropped those tables. When I try to re-import the tables in PHPMyAdmin, I get this error:

SQL query:

-- -- Database: `my_db` -- DELIMITER $$ -- -- Functions -- CREATE DEFINER=`my_username`@`%` FUNCTION `LEVENSHTEIN`(s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS int(11) DETERMINISTIC BEGIN DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT; DECLARE s1_char CHAR; DECLARE cv0, cv1 VARBINARY(256); SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0; IF s1 = s2 THEN RETURN 0; ELSEIF s1_len = 0 THEN RETURN s2_len; ELSEIF s2_len = 0 THEN RETURN s1_len; ELSE WHILE j <= s2_len DO SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1; END WHILE; WHILE i <= s1_len DO SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1; WHILE j <= s2_len DO SET c = c + 1; IF s1_char = SUBSTRING(s2, j, 1) THEN SET cost = 0; ELSE SET cost = 1; END IF; SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost; IF c > c_temp THEN SET c = [...]

MySQL said: Documentation

#1304 - FUNCTION LEVENSHTEIN already exists

I get this error even if I drop all tables from the DB and try to import anything. The only way I can get the error to go away is to totally delete the database and re-create it.

What's causing that error and how can I stop it from happening?

Check your import .sql script.
There is possibly a create function statement between the create tables.
You have dropped the tables, but the functions already exists because you didn't know it was there.

BTW if you would clear the tables, you must not drop an recreate them, you could also issue a truncate table tbl_name;


Modelling a database for products, categories and features Good day everyone. I am building a database for an application and I have thought of two different approaches for achieving the same result, and I would like your opinion.

Is basically the classical product database, so at the moment I have products, each of theme belonging to a category. Each product has different features but the products that fall in the same category all share some common features.

At the moment I thought of this two structures:

A products table, just to store the name.
A product_categories table, to store the categories, and an intermediate table to link both.
A features table, to store the different features.
A product_features to link the product with it's features and store their value.

Since the products that fall on the same category have some shared features, I also thought of extracting the common features and creating a table for each product, so the result would be something like this:

A table for each product, to store their name and common features.
A features table, to store the different features that are not common.
A product_features to link the product with it's features and store their value.

I believe the first structure will be easier to maintain if I need to add newer products down the line as I would not need to create any new tables, but I kind of feel that the second approach would be more correct.

Let me know your thoughts!

If a category has related features then you have to link them independently from product. If a feature is only present through a category then you can omit the link between feature and product. If not, you have to link feature with both category and product, or else you might have inconsistencies between a product category's features and the actual features for that product.

Assuming that a category has a set of features and a product might have specific features (aside from those from it's category), I would suggest the following scheme:

CREATE TABLE Feature (
    FeatureID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(1000))

CREATE TABLE Category (
    CategoryID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(1000))

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100))

-- Each product has a set of categories (N to M)
CREATE TABLE CategoryByProduct (
    ProductID INT,
    CategoryID INT,
    PRIMARY KEY (ProductID, CategoryID),
    FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
    FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID))

-- Each category has a set of features (N to M)
CREATE TABLE FeatureByCategory (
    CategoryID INT,
    FeatureID INT,
    PRIMARY KEY (CategoryID, FeatureID),
    FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID),
    FOREIGN KEY (FeatureID) REFERENCES Feature (FeatureID))

-- Each product might have additional features aside from the one's from it's category (N to M)
CREATE TABLE FeatureByProduct (
    ProductID INT,
    FeatureID INT,
    PRIMARY KEY (ProductID, FeatureID),
    FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
    FOREIGN KEY (FeatureID) REFERENCES Feature (FeatureID))

So if you want to retrieve a specific product's features you have to traverse through it's categories and particular features:

-- Features by product's category
SELECT
    P.ProductID,
    P.Name,
    F.FeatureID,
    F.Name
FROM
    Product P
    LEFT JOIN CategoryByProduct CP ON P.ProductID = CP.ProductID
    LEFT JOIN FeatureByCategory FC ON CP.CategoryID = FC.CategoryID
    LEFT JOIN Feature F ON FC.FeatureID = F.FeatureID
WHERE
    P.ProductID = 123 -- Particular product ID

UNION ALL

-- Stand-alone features
SELECT
    P.ProductID,
    P.Name,
    F.FeatureID,
    F.Name
FROM
    Product P
    LEFT JOIN FeatureByProduct FP ON P.ProductID = FP.ProductID
    LEFT JOIN Feature F ON FP.FeatureID = F.FeatureID
WHERE
    P.ProductID = 123 -- Particular product ID

Following this approach, you just have to add a category to a product to "inherit" it's features, and you have the possibility to add custom features to particular products. Also changing the features of a category will automatically impact all products related to that category.


How do I install SQL-Server Tools if I can't use curl? Attempting to install mssql-tools, I get

$ sudo apt-get install mssql-tools
Reading package lists... Done
Building dependency tree... 50%
Building dependency tree
Reading state information... Done
Package mssql-tools is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'mssql-tools' has no installation candidate

So, I go to add the repository,

curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

Command 'curl' not found, but can be installed with:

apt install curl

But now I get an error about curl, trying to install curl

$ sudo apt-get install curl
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libc++1 libc++abi1 libsss-nss-idmap0
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libcurl4
The following packages will be REMOVED:
  libcurl3 mssql-server
The following NEW packages will be installed:
  curl libcurl4
0 upgraded, 2 newly installed, 2 to remove and 0 not upgraded.
Need to get 373 kB of archives.
After this operation, 929 MB disk space will be freed.
Do you want to continue? [Y/n] n
Abort.

So in essence, you need curl to install mssql-tools, and installing curl uninstalls mssql-server because sql-server is build with libcurl3, and /bin/curl on Linux wants libcurl4. What do you do?

14.0.3038.14-2+

New versions of SQL Server (14.0.3038.14-2+) no longer conflicts with libcurl3.

  Depends: libunwind8, libnuma1, libc6, adduser, libjemalloc1, libc++1, gdb, debconf, hostname, libssl1.0.0, openssl, python (>= 2.7.0), libgssapi-krb5-2, libsss-nss-idmap0, gawk, sed, libldap-2.4-2, libsasl2-2, libsasl2-modules-gssapi-mit

Versus 14.0.3037.1-2

  Depends: libunwind8, libnuma1, libc6, adduser, libjemalloc1, libc++1, gdb, debconf, libcurl3, hostname, libssl1.0.0, openssl, python (>= 2.7.0), libgssapi-krb5-2, libsss-nss-idmap0, gawk, sed, libldap-2.4-2, libsasl2-2, libsasl2-modules-gssapi-mit


Issues installing SQL Server 2008 R2 or any other version on Windows 10 I have a serious problem by installing any SQL Server version (tried 2008 R2 and SQL Server 2014) on Windows 10.

First question: are the above mentioned versions compatible with Windows 10 or Windows 8.1?

Because that's how it was, when I bought a computer it was with Windows 8.1 first, then I had an icon showing me that I could update it to Windows 10. And that's what I did it. I updated it and now, I try to install Any SQL Server of the above and I get loads of the error, obviously you will get the Error that " This version is not compatible of this version on windows " but, on the videos in youtube, I saw that you can install the SQL server 2008 R2 or SQL server 2014 without that notice and it installs without errors.

When I try to follow their steps and downloading the same thing, I get lot's of errors and I don't know what to do. I will show few pictures what I'm getting by installing the 2 versions of SQL servers. I hope you can help me. Thanks.

And I'm geting these errors. So I don't know how to fix this.

In this It always stops here, I was waiting for hours but it never proceeded. So I had to close in task manager "msiexec.exe" to complete but I get the error you can see the other picture below.

I added everything with pictures. This should show you what kind of problem I have.

That looks like serious damage to your installation.

Have you considered uninstalling all SQL Server and Visual Studio (especially VS2010) software? If you have database files, you can probably leave them in place (but I would prefer to also make copies elsewhere).

Then delete the folders that may still have some SQL Server components from previous installs so as to have a 'clean' slate to work from. Then Install from the installation media for a brand new install.  Attach or restore the files and see if that resolves your problem.


How to speed up audit trigger? The task: write a trigger to log all updates within tbl1 table for future audit users' actions.

The tbl1 table:

id INT(11)
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some of them have to be monitored

I'm going to store both old & new values, one row for each field that was changed. The place for log (simplified for this example):

 CREATE TABLE z_log(
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   table_name CHAR(20) NOT NULL,
   row_id INT(11) UNSIGNED NOT NULL,
   field CHAR(20) NOT NULL,
   old_val VARCHAR(255) NOT NULL DEFAULT '',
   new_val VARCHAR(255) NOT NULL DEFAULT '',
   PRIMARY KEY (id)
 )

The trigger I have created:

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  -- I'm interested in two fields only and if they were changed only
  IF new.y != old.y THEN
    SET @y = "('','tbl1',new.id,'y',old.y,new.y)";
  END IF;

  IF new.country != old.country THEN
    SET @country = "('','tbl1',new.id,'country',old.country,new.country)";
  END IF;

  if @y != '' or @country != '' THEN
    set @my_sql = concat('insert into z_lot values ', concat_ws(',', @y, @country), ';');
    prepare stmt1 from @my_sql;
    EXECUTE stmt1;
  end if;

END

But I got error "dynamic sql is not allowed in stored function or trigger".
I can avoid 'execute'  by using a lot separated inserts: one for field 'y', one for field 'country' and ~10 inserts more for other fields but there will be performance penalty.

Is there any other fast way to log changes only?
Thank you.

You could alter the trigger with brute force INSERTs as follows:

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  DECLARE insert_style INT;

  -- I'm interested in two fields only and if they were changed only

  SET insert_style = 0;
  IF new.y != old.y THEN
      SET insert_style = 1;
  END IF;
  IF new.country != old.country THEN
      SET insert_style = insert_style + 2;
  END IF;

  CASE insert_style
      WHEN 1 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y);
      WHEN 2 THEN
          insert into z_lot values
          ('','tbl1',new.id,'country',old.country,new.country);
      WHEN 3 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y),
          ('','tbl1',new.id,'country',old.country,new.country);
  END CASE;

END


Getting error "Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7." But a database with Id 7 DOES NOT exist I have a testing environment where I have scheduled jobs running restore tests as well as DBCC checks. These jobs run to:
1) copy database back up from the prod.
2) DROP all existing databases except dba_utility
3) restore the backup
4) run dbcc checkdb

I frequently get:

Time-out occurred while waiting for buffer latch type 2 for page (1:3564879), database ID 7.

The weird thing is, there is no database with an Id of 7.
Maximum Id in sys.databases catalog view is 6. (Remember, the job DROP all databases before the restore operation except dba_utility whose Id=5. The newly restored database is assigned the Id of 6.)

Prod environment is 2008 R2, test environment is 2012 SP1.

What might be causing this?

run dbcc checkdb

CHECKDB creates a database snapshot behind the scenes. This is your DBID 7.

As for the timeout, perhaps KB919155 Error message when you run the DBCC CHECKDB statement on a database that contains one or more very large tables in SQL Server: "Timeout occurred while waiting for latch". "This behavior is by design.". Is page 3564879 an IAM chain page?


Why SQL Server replication related jobs do not show up under the jobs folder in SSMS? I found some SQL Server replication-related jobs that do not show up under the jobs folder in SSMS. Can anybody explain why this is, please?

Thanks.

In replication their are 3 Database/Servers you have:

Publisher  : where the data comes from
Distribution  : handles tracking and packaging of data to be sent to the subscriber
Subscriber  : receives the data

Depending on the type of replication you have setup, the replication jobs may be running at a different location than you expect, ie:

Under Pull replication, there will be jobs running on the Subscriber to pull the data from the distribution database vs Push replication, the job will be running on the Distribution server.

For more info see MSDN: Replication Agents Overview


How can I copy the MySQL EXPLAIN result into a table? I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis.

I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how to populate it from executing an EXPLAIN command.

I figured out how to export a .sql file in MySQL Workbench that contains INSERT commands, but I have to edit this manually to make it work.  Is the EXPLAIN information stored somewhere so I could write queries to do it automatically?  I can't find it in either the information_schema or performance_schema.

If you are running on Linux OS you could save it into a file.

Example:

User='root'
Pass='text'
Host='10.0.0.223' (If it's remote)

On command line run:

mysql -u $User -p$Pass --host=$Host -e "EXPLAIN SELECT * FROM mysql.user LIMIT 0,2" | tee -a test.txt

mysql -e will execute the query on the command line.

tee -a will read from standard input and write to file test.txt

And you'll get:

You can use perl to replace the death lines to any delimiter you want (I used comma in this example).

perl -wnlpi -e 's/\s+/,/g;' text.txt

Example:

Hope this help.


How to install Postgresql on Ubuntu from PPA with data-checksums enabled? We are currently installing Postgresql from the PostgreSQL apt repository. Is there some way to get "apt-get install postgresql-9.6" to use the --data-checksums option when setting up the cluster?

Or do we need to install Postgresql differently?

If you do not want to install and drop the cluster on Debian and related, you could:

Install postgresql-common;
Edit the /etc/postgresql-common/createcluster.conf file to allow the use of the --data-checksums option;
Install postgresql-9.6.


Privilege to set default database in MySQL It turns out that a user cannot set default database if it does not have global (mysql.user) or any database-level permissions (mysql.db):

MariaDB [(none)]> use mysql;
ERROR 1044 (42000): Access denied for user 'XXX'@'%' to database 'mysql'

However, using default database could be convenient, e. i. there is a lot old scripts relying on that.

The only workaround I've found so far, is to grant SHOW VIEW on the database: I do not want to grant even read-only access for all tables.

What do I miss? I don't know any SQL syntax that allows insert 'N' in all relevant columns in mysql.db, and don't want to do it with raw SQL (INSERT ...).

Well, thanks Rick James, it is a bit more complicated. The problem is with MariaDB.

Update: https://jira.mariadb.org/browse/MDEV-9614


Azure SQL DB vs Azure managed instance We are in the early stages of planning to move some of our on-premise database to cloud and wanted to go the PaaS way.  I understand that managed instance is in preview.

From what I have read, ignoring the cost, is there any reason why not to go with managed instance? Does it not make the migration easier while still being PaaS/Managed so that we do not have maintain it?  Looks like the best of both world to me.

The only question that I couldn't find is how we could handle disaster recovery for managed instance?  With Azure SQL DB we can have Geo-replication in another region, but couldnt find anything similar documented for managed instance.

In the initial stages we are planning to move an OLTP application which needs to be highly available and have very low latency queries.

It really does come down to what you need and why you need it. Managed Instance has a single, huge, strength over Azure SQL Database, cross-database queries. If you need to query between databases and you don't want to rewrite the code, then Managed Instance is the way to go. There are some other advantages such as having SQL Agent (easily replaced with Azure Automation) and some other behaviors, but the cross-database query is the huge driver.

I can't answer the geo-replication question because I haven't tested that from Managed Instance, so I'm not aware of the level of support there.


CROSS APPLY query with CASE and running totals I'm trying to make this business logic make sense, but I'm not having any luck.
The rule is I can have a sale with discounts from percentage and value. But the value is to be subtracted after discounted percentage, from the sequence column.

If this was only about percentages, I could make it work, the problem is the case in the middle when in the same sale there's a value to subtract from Current value.

+--------+-----------+------+----+---+
| SaleId | Discount  | Type | S  | q |
+--------+-----------+------+----+---+
|      1 |      0.15 | P    | rc | 1 |
|      1 |     10.05 | V    | l  | 2 |
|      2 |      0.05 | P    | rc | 1 |
|      3 |      0.05 | P    | rc | 1 |
|      3 |      8.00 | V    | l  | 2 |
|      3 |      0.25 | P    | rc | 3 |
+--------+-----------+------+----+---+

+--------+--------+
| SaleId | Amount |
+--------+--------+
|      1 | 100.00 |
|      2 | 200.00 |
|      3 |  50.00 |
+--------+--------+

+--------+--------+----------+------+-----+--------+--------------+
| SaleId | Amount | Discount | Type | Seq | T_Disc | CurrentValue |
+--------+--------+----------+------+-----+--------+--------------+
|      1 |    100 |     0,15 | Perc |   1 |   0,85 |           85 |
|      1 |    100 |    10,05 | Val  |   2 |      0 |        74,95 |
|      2 |    200 |     0,05 | Perc |   1 |   0,95 |          190 |
|      3 |     50 |      0,1 | Perc |   1 |   0,95 |           45 |
|      3 |     50 |        8 | Val  |   2 |      0 |           37 |
|      3 |     50 |     0,25 | Perc |   3 |      0 |        27,75 |
+--------+--------+----------+------+-----+--------+--------------+

Sale 3:

50 minus 10%
45 minus 8
37 minus 25%

After this I need to display the last row from each SaleId.

Can you please advise if this is possible?

I'm trying CROSS APPLY and a CTE (WITH clause):

WITH CTE_CalcTotalDisc
AS
(
    Select A.SaleId
    ,CONVERT(DECIMAL(8,3),B.Amount) Amount
    ,Discount
    ,Tipo
    ,A.Seq
    ,CASE WHEN Tipo='Val' THEN convert(decimal(8,3),Discount)
        ELSE convert(decimal(8,3),1-(1*Discount))END AS T_Disc
    ,CASE WHEN Tipo='Val' THEN convert(decimal(8,3),Amount-Discount)
          WHEN Tipo='Perc' THEN convert(decimal(8,3),Amount-(Amount*Discount))
        ELSE convert(decimal(8,3),0.00) --convert(decimal(6,5),1-(1*Discount))
        END
    AS CurrentValue
    From #disc A, #Sales B
    where Seq=1 AND A.SaleId=B.SaleId

    UNION ALL

    Select d.SaleId
    ,CONVERT(DECIMAL(8,3),0.00)
    ,d.Discount
    ,d.Tipo
    ,d.Seq
    --,convert(decimal(6,5),(1-(1*d.Discount))*T_Disc) T_Disc
    --,CASE WHEN d.Tipo='Val' THEN d.Discount ELSE convert(decimal(6,5),1-(1*d.Discount))END
    , CONVERT(DECIMAL(8,3),0.000) AS T_Disc
    ,CONVERT(DECIMAL(8,3),0.000) AS CurrentValue
    From #disc d --, #Sales B
        JOIN CTE_CalcTotalDisc ad on d.SaleId=ad.SaleId
                            AND d.Seq=ad.Seq+1
)
SELECT * FROM CTE_CalcTotalDisc I1
CROSS APPLY(
  SELECT
  --SUM(CurrentValue)   RunningTotal
  I1.CurrentValue-(I1.CurrentValue*I1.Discount)   RunningTotal
  FROM CTE_CalcTotalDisc I2
  WHERE I1.SALEID = I2.SALEID
  --AND I1.Tipo = I2.Tipo
  AND I1.Seq <= I2.Seq
)C

The following will work on sql-server 2012+. Note that there were a couple of inconsistencies between your supplied SQL and the sample schema that you provided and I think I have followed the names in your SQL.

With CTE_CalcTotalDisc As (
    Select d.SaleID, s.Amount, d.Discount, d.Tipo, d.Seq,
        T_Disc = iif(d.Tipo = 'V', d.Discount, (s.Amount * d.Discount)),
        CurrentValue = iif(d.Tipo = 'V', s.Amount - d.Discount, iif(d.Tipo = 'P', s.Amount - (s.Amount * d.Discount), 0))
      From #disc As d
      Join #sales As s On d.SaleID = s.SaleID
      Where d.Seq = 1
    Union All
    Select d.SaleID, Amount = cte.CurrentValue, d.Discount, d.Tipo, d.Seq,
        T_Disc = iif(d.Tipo = 'V', d.Discount, (cte.CurrentValue * d.Discount)),
        CurrentValue = iif(d.Tipo = 'V', cte.CurrentValue - d.Discount, iif(d.Tipo = 'P', cte.CurrentValue - (cte.CurrentValue * d.Discount), 0))
      From #disc As d
      Join CTE_CalcTotalDisc As cte
        On d.SaleID = cte.SaleID
        And d.Seq = cte.Seq + 1),
CTE_Ordered As (
    Select SaleID, CurrentValue,
        Discount = Sum(T_Disc) Over (Partition By SaleID Order By Seq Asc),
        RowNo = Row_Number() Over (Partition By SaleID Order By Seq Desc)
      From CTE_CalcTotalDisc)
--Select * From CTE_CalcTotalDisc
Select * From CTE_Ordered Where RowNo = 1;

Note the use of Row_Number(), partitioned by SaleID and sorted descending by Seq to get the final value, and the use of Sum() to get the running total of the discount.

You can uncomment the commented select statement to view all rows, not just the final row for each SaleID.


Issue with a conditional sub query I have the following table:

value    tag    main_tag
------------------------
    1    tag_A  ABC
   -1    tag_C  ABC
   -1    tag_A  BCD
    1    tag_C  BCD
   -1    tag_A  ABC
    1    tag_C  ABC
   -1    tag_A  BCD
    1    tag_C  BCD
   -1    tag_C  BCD

As demonstrated, there are three columns value, tag, main_tag. Under each main_tag there are several other tag s. I want to get the rows where sum of value s of each tag is equal to zero under each main_tag.

This is the query I tried,

SELECT *
FROM foo
WHERE ( WHERE sum(value) = 0 GROUP BY tag)
GROUP BY main_tag ;

First I am trying to find the sum of values GROUP BY tag and then I am using GROUP BY main_tag. But it is not working.

Output Should be :

value    tag    main_tag
------------------------
    1    tag_A  ABC
   -1    tag_C  ABC
   -1    tag_A  ABC
    1    tag_C  ABC

Not sure that I got it right, but:

select tag, main_tag
from foo
group by tag, main_tag
having sum(value) = 0;

retrieves tag, main_tag where sum(value) is 0. You can join this result with the original table as:

select f.*
from foo f
join (
    select tag, main_tag
    from foo
    group by tag, main_tag
    having sum(value) = 0
) as t
    on t.tag = f.tag
   and t.main_tag = f.main_tag;


Database design suggestions for a data scraping/warehouse application? I'm looking into the database design for a data warehouse kind of project which involves a large number of inserts daily.  The data archives will be further used to generate reports.  I will have a list of users (for example a user set of 2 million), for which I need to monitor daily social networking activities associated with them.

For example, let there be a set of 100 users say U1, U2, ..., U100.

I need to insert their daily status count into my database.

Consider the total status count obtained for a user U1 for period June 30 - July 6, is as follows

June 30 - 99
July 1 - 100
July 2 - 102
July 3 - 102
July 4 - 105
July 5 - 105
July 6 - 107

The database should keep daily status count of each user, like for user U1:

July 1- 1 (100-99)
July 2- 2 (102-100)
July 3- 0 (102-102)
July 4- 3 (105-102)
July 5- 0 (105-105)
July 6- 2 (107-105)

Similarly the database should hold daily details of the full set of users.

And on a later phase I envision taking aggregate reports out of these data, like total points scored on each day, week, month, etc; and to compare it with older data.

I need to start things from scratch.  I am experienced with PHP as a server side script and MySQL. I am confused on the database side.  Since I need to process about a million insertions daily, what are all the things that should be taken care of?

I am confused on how to design a MySQL database in this regard.  Which storage engine to use and which design patterns should be followed, keeping in mind the data could later used effectively with aggregate functions?

Currently I envision the DB design with one table storing all the user id's with a foreign key and separate status count table for each day.

Does MySQL fit my requirement?  2 million or more DB operations are done every day.  How are the server and other things to be considered in this case?

EDIT:

Queries Involved:

INSERTION QUERIES

Insertion queries should be capable of inserting 1-2 million inserts every day.  (We don't have update here.)

RETREIVAL QUERIES

1.Sum of statuses for the whole set of users.

2.Sum of statuses for set of users under a geographic location.

3.Comparing status counts with days/weeks/months.

-->I believe some kind of indexes are needed in this case, but I read indexes could slow down insertion.

-->Also I have heard MyISAM would be a better choice than InnoDB considering speed aspects.

Please advise?

These are general recommendations, as you do not show the full extent of your queries to be performed (which kind of analytics you plan to do).

Assuming you do not need real time results, you should just denormalize your data at the end of the period, precalculate once your aggregated results on all necessary timeframes -by day, by week, by month-, and work only with summary tables. Depending on the queries you intend to do, you may not even need the original data.

If durability is not a problem (you can always recalculate statistics as raw data is elsewhere), you can use a caching mechanism (external, or MySQL 5.6 includes memcache), which works great for writing and reading key-value data on memory.

Use partitioning (can also be done manually), as with these kind of applications, usually the most frequently accessed rows are also the most recent. Delete or archive old rows to other tables to use our memory efficiently.

Use Innodb if you want durability, high concurrent writes and your most frequent accessed data is going to fit into memory. There is also TokuDB- it may not be raw faster, but it scales better when dealing with insertions on huge, tall tables and allows for compression on disk. There are also analytic-focused engines like Infobright.

Edit:

23 insertions/second is feasible in any storage with a bad disk but:

You do not want to use MyISAM- it cannot do concurrent writes (except on very specific conditions) and you do not want to have huge tables that become corrupted and lose data
InnoDB is fully durable by default, for better performance you may want to reduce the durability or have a good backend (disk caches). InnoDB tends to get slower on insertion with huge tables. The definition of huge is "the upper parts of the Primary key/other unique indexes must fit into the buffer pool" to check for uniqness. That can vary depending on the memory available. If you want scalability beyond that you have to partition (as I suggested above)/shard or use any of the alternative engines I mentioned before (TokuDB).

SUM() statistics do not scale on normal MySQL engines. An index increases performance, again, because most of the operations can be done on-memory, but one entry for each row has to still be read, in a single thread. I mentioned design alternatives (summary tables, caching) and alternative engines (column-based) as a solution to that. But if you do not need real-time result, but report-like queries, you shouldn't worry too much about that.

I suggest you to do a quick load test with fake data. I've had many clients doing analytics on MySQL of social networks without problems (well, at least, after I helped them :-) ), but you decision may depend on your actual non-functional requisites.


Help with Vehicle Service database I am using the Desktop Vehicle Maintenance template in access, but i am trying to cater it towards our farms needs.

We will be keeping track of the service's done on Trucks, Tractors, Equipment, etc.
Each Vehicle will have different fields and different data tracked. But i can't figure out how to create a table for each entity and pull it into one centralized table to display service's done on all vechiles/equipment.

But i can't figure out how to create a table for each entity and pull it into one centralized table

It sounds like you want to read up on Joins: http://en.wikipedia.org/wiki/Join_%28SQL%29

A "join" basically lets you have a query that returns data from multiple tables, based on some criteria, such as records in two tables having a certain matching value. If you want to give the appearance of having this combined data all in one table, you could create a View ( http://en.wikipedia.org/wiki/View_%28database%29 ) of the query with all the joins. MS Access lets you use views to drive reports as easily as it lets you use tables for that purpose.


Sorting a list of records alphabetically: is there any reason NOT to let the database do it I have a list of records that contain a column of arbitrary strings. I would like the application to display them sorted in some order (preferably alphabetically, since humans have to read the data).

I can choose to have the database sort the data before returning it to me, or I can choose to have the database give me the raw search results and let the application deal with it.

Currently the database is sorting it cause I don't think I can write a very efficient sort anyways, but I'm wondering whether there are any situations where I want a sorted list, but it would be better to have the application do it?

This is a way to broad question to give a detailed answer. However, there is one general rule that holds true in development:

  Don't reinvent the wheel

That is, unless you have a good reason. In the case of sorting, the developers of the database engine spend a lot more time writing an efficient sorting algorithm than you will be able to. So unless you need to sort by something like a field that is encrypted and cannot be decrypted on the database server, I would always sort on the database side.

However, if you have changing sorting requirements, like a display were the user selects which column to sort by, you are better of getting the data once and then sorting it in memory. The alternative of transferring the data again and again every time the sort order changes might be just too chatty on the network. Also a fully flexible query tends to be hard to optimize by the rdbms.

But then, if you are returning only the top few rows, sorting on the database site becomes a lot faster again, as the amount of data going over the network is greatly reduced.

That really brings us to the second general rule in database development:

  It depends.

It hope this was at least a little helpful. For a more detailed answer please give us more detail on your actual problem.


DBA's Scope in datawarehousing Projects As a DBA trainee, what needs to be known in order to be part of datawarehousing projects?

I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used for Datawarehouse projects?

From a general perspective (not platform-specific), here's what I'd recommend mastering for data warehouse projects:

Know how to load data fast.  BI projects usually involve nightly loads of large amounts of data.  The ETL guys need to shove data in fast with a minimum of concurrency issues.  This means knowing when to disable indexes, when to perform tasks in temporary staging databases rather than the production environment, and how to offload processing to the ETL server instead of the database server.

Know how to handle large table scans. BI environments usually have multiple terabytes of data, beyond what can fit in memory.  Index tuning can only take you so far.  You need to know how to get as much throughput out of the SAN as possible.

Know how to segment archive data. BI environments usually have a small percentage of live, changing data and a much larger percentage of read-only (or read-biased) archive data.  You have to know how to recognize those patterns and how to separate that data out into different tables or different storage targets with a minimum of work required by your ETL people.

Know how to handle maintenance tasks.  Defragmenting or rebuilding indexes is easy on a 100GB database, but not so easy on a 1TB or larger database.  Maintenance windows have to be carefully planned.  Backups are another story altogether.

Know when to design reporting tables. If your users constantly access the same aggregate data (like grouping sales data by month or by salesperson, or constantly recalculate a profit percentage) then you need to recognize those trends in the end user queries, design a pre-calculated set of reporting tables, and train the users to access the data that way.


Stop NLS settings in init.ora getting overridden by environment variable We have a databases server that is used for development which has hundreds of database instances for different branches/builds of our application. And we needed an special instance that has NLS_CALENDAR='Thai Buddha' to test a particular bug.

We have set up a database and included NLS_CALENDAR='Thai Buddha' setting in init.ora. However, we still get 'GREGORIAN' for NLS_CALENDAR, since environment variables overrides the setting in init.ora.

NLS_CALENDAR value is set as follows:

NLS_SESSION_PARAMETERS: 'GREGORIAN'

NLS_INSTANCE_PARAMETERS: 'Thai Buddha'

NLS_DATABASE_PARAMETERS: 'GREGORIAN'

ALTER SESSION is not an option because then we have to modify our application code to issue an ALTER SESSION whenever a user logs in (This is not possible because we have multiple clients already deployed on various machines). Modifying the environment setting is not an option either because there are other database instances running on the same host.

I have tried ALTER SYSTEM, but it gives following oracle error:

  ORA-02096: specified initialization parameter is not modifiable with this
  option

How can we make a single database instance to ignore the environment setting? Or is there any other way we can make our instance default to 'Thai Buddha' when it comes to NLS_CALENDAR setting?

The documentation clearly describes the precedence for NLS settings: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG188

1 (highest) Explicitly set in SQL functions
2           Set by an ALTER SESSION statement
3           Set as an environment variable
4           Specified in the initialization parameter file
5           Default

You can choose the first two from these.
A logon trigger can also issue the ALTER SESSION, and you don't need to modify the application code for that:

CREATE OR REPLACE TRIGGER SET_NLSCALENDAR_ONLOGIN AFTER LOGON ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV','SESSION_USER') = 'SCOTT' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CALENDAR=''Thai Buddha''';
  END IF;
END;
/

Check setting for SCOTT and other user:

SQL> connect scott/tiger
Connected.
SQL> select value from nls_session_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
Thai Buddha

SQL> select value from nls_database_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
GREGORIAN

SQL> conn hr/hr
Connected.
SQL> select value from nls_session_parameters where parameter = 'NLS_CALENDAR';

VALUE
-----------
GREGORIAN


Which is more efficient, filtering the SSRS dataset or filter using a query parameter People at my organization use SQL Queries within SSRS reports: for instance

SELECT name, age
FROM egTable

Then, this query is run on the database and then they use a FILTER within SSRS to get rid of unwanted rows. For instance, there is a filter in the SSRS called AGE, and this will be something like Age = 11

My proposition was that, this is bad: this way of doing things means that we are querying the ENTIRE table, and then from the GIANT resultset we are just getting rid of unwanted rows. Instead, we should be introducing a parameter: @age, and then write the query as follows:

    SELECT name, age
    FROM egTable
    WHERE age = @age

Am I correct in saying that the first method pulls the entire table, whereas mine is far more efficient because it only returns a small result set?

How can I verify/prove this?

TL/DR

Yes you are correct, you can prove it by showing the result of ExecutionLog in the SSRS database.

Longer Answer

I created 2 identical reports based on the Adventureworks database, one with a filter on the dataset for City and one with a parameter in the query for City.

Report 1

The query for this report is:

SELECT  Person.Address.*
FROM    Person.Address

And the filter is added like this:

The result of this report is:

Report 2

The query for this report is

SELECT  Person.Address.*
FROM    Person.Address WHERE city=@city

There are no filters on this dataset

The result of this report is:

Where you can see the same data is shown, but the behaviour is different since the user has to put in a value for the filter (marked in yellow)

If this is undesired this can be overcome by adding a default value to the parameter and setting the visibility to hidden like this:

and this:

Proof of efficiency

The efficiency of both reports can be proven by querying the executionlog table and views like this:

SELECT [ItemPath], [Parameters], [TimeDataRetrieval], [TimeProcessing], [TimeRendering], [RowCount]
FROM ExecutionLog3;

Which for these 2 reports returns:

+--------------------------+--------------+-------------------+----------------+---------------+----------+
|         ItemPath         |  Parameters  | TimeDataRetrieval | TimeProcessing | TimeRendering | RowCount |
+--------------------------+--------------+-------------------+----------------+---------------+----------+
| /Report Project3/Report1 | NULL         |               669 |           1878 |           880 |    19614 |
| /Report Project3/Report2 | city=Bothell |                 8 |             42 |             4 |       26 |
+--------------------------+--------------+-------------------+----------------+---------------+----------+

So the second method not only fetched a lot less rows (26 versus 19614) but also consumed less time processing and rendering the report.


Names of temp tables I created a temp table which resides in the temp table folder. The problem is the name is unacceptably long which make it almost impossible to efficiently work on querying some field from it. Is it any way to have a shorter name like db.#SPR1 instead of the long one as below?

dbo.#SPR1_______________________________________________________________________________________________________________000000003A3E

This appears to be Sql Server.

When you created the temporary table, you gave it a meaningful name:

Create Table #Sales (id int, sales decimal(11,2))

Perhaps you created the temporary table using SELECT INTO #Sales FROM...

That is the name you use to reference the table (#Sales).

Due to the fact that other users in the instance can also create a local temporary table with the exact same name, Sql Server has to use an internal 'generated' name to differentiate between YOUR #Sales and another persons #Sales.

You can observe this by creating a temporary table in one session called #Sales with some columns.  Under SSMS object explorer, you can see that entry under TEMPDB temporary tables.  Now, go to a brand new query window and create the same table.  Refresh the TEMPDB temporary tables node and you'll see the newly added temp table.  But they are completely independent from each other.

Don't worry about the long weird table name under TempDB – just use the name you gave it at creation time.

And speaking of temporary tables, I'd recommend reading the information in Temporary Tables: Local vs. Global so you'll have a better understanding of how temporary tables work and how long they exist.

Additional information can be found on the Internet by searching for

  Difference between local and global temporary table in sql server


MySQL Workbench: I need to update values in a table based on values from a csv Newbie Problem, but I've tried creating a new table and importing the csv to that then updating, but I have been having trouble with that, any guidance would be greatly appreciated.

I have a csv file that contains about 1500 entries, with an "ID" column and an "Unit" column. I am trying to figure out the best way to update, in my mySQL workbench table, the "unit" of only the IDs in my csv file.

CSV File

id_inv  unit
1,      aaa
2,      aaa
3,      aaa
5,      aaa
10,     aaa

DB Table

id_inv       unit     ... other columns
1            bbb
2            bbb
3            bbb
4            bbb
5            bbb
6            bbb
7            bbb
8            bbb
9            bbb
10           bbb

Import CSV file to temporary table and then run the UPDATE. For example:

DROP TEMPORARY TABLE IF EXISTS tempTable;
CREATE TEMPORARY TABLE tempTable
(id_inv int,
unit varchar(255),
PRIMARY KEY (id_inv)
);

LOAD DATA INFILE 'C:\\PathToYour\\FileName.csv'
INTO tempTable (id_inv, unit)
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

UPDATE DBtable t1
JOIN tempTable t2 ON t1.id_inv = t2.id_inv
SET t1.unit = t2.unit;

DROP TEMPORARY TABLE tempTable;

You may need to use different symbols for fields and lines terminators and file path of course. Also if you are working with remote server use "LOAD DATA LOCAL INFILE" command.


SDO_NN the second nearest neighbor Oracle SQL geometry allows me to find the nearest neighbor using

SDO_NN(neighbors.GEOLOC,p.GEOLOC,'SDO_NUM_RES=1 UNIT = MILE',1) = 'TRUE')

How can I get the select the second nearest point?

From the docs

  sdo_num_res Specifies the number of results (nearest neighbors) to be returned. If neither sdo_batch_size nor sdo_num_res is specified, this is equivalent to specifying sdo_batch_size=0. See the Usage Notes and Examples for more information. Data type is NUMBER. For example: 'sdo_num_res=5'

So just set sdo_num_res to whatever number of results you want returned.


SQL Server 2008 sp_send_dbmail I'm using SQL Server's

sp_send_dbmail

to distribute mail.  Everything is working fine; BUT I tried to test a bad email address and it always reports success; and nothing is in the failed sent items table.

Will this procedure report IF the email isn't sent?  Or is success just getting the mail to the exchange server?

Thanks in advance!

The procedure will not report if the email is not received. It will be upon you and your support team to troubleshoot why the email was not received. SQL Server just cares about being able to execute the call. Thomas Stringer pointed out sysmail_allitems in a comment and that will show failed status:

USE msdb;
select recipients, sent_status
FROM sysmail_allitems
GO

If it actually failed the sent_status field will show failed

Edit: Found some more stuff. sp_send_dbmail does not provide a function for "feedback" on the success or failure of the email. You can check the status using the sysmail_xxx tables and in the database mail log. The best I have been able to do on an AD exchange server relaying through anonymous SMTP is a failed status in sysmail_allitems and an error msg in the Database mail log stating unknown user

Links Jack Corbett


MariaDB Master / Slave failover / failback automation Good day all, I'm hoping someone might be able to point me in the right direction.

I'm working on a new dual data center project, and I'm trying to come up with a plan for failover (for patching and other maintenance) and failback. The databases / data centers will act as Primary / DR. Of course DR would fall into this plan. There is no viable 3rd pop for an arbitrator, so clustering isn't in the cards. Each database will be running MariaDB (10.3 or columnstore).

The plan includes running ProxySQL on each application to handle communication with the database, and failover from that perspective. It's my understanding that it can leverage "READ-ONLY" to recognize which database is primary, which seems intuitive.

On the database side I hope to configure Master/Master replication to ease the transition between data centers, and hope to utilize the read only options to control data flow as mentioned previously. And this is where I'm stuck as I need the process to be as easy as possible so that non-DBA's can facilitate the failover without much interaction.

Is there an out of the box, community (free), utility that can perform the necessary steps to effectively "swap" masters and allow for unattended recovery? If not, can anyone point me toward a sample process/script that might achieve the same results?

Thanks in advance!

Get a package like "MHA".  It is "free".

See also Galera and Orchestrator.


What are the advantages and inconveniences of generalization or specialization of SQL tables? This is a "theoretical" question linked to quality of table's definition.

I work on tires and, currently, I have a big table that, for each tire's label, contains a row with label's values. For more information go to this site:
DUNLOP Tire Label Explained.

The used tire's labels are Rolling Resistance ('RR'), Wet Grip ('WG') and Noise ('NOISE').

Some columns in this big table are common to 'RR', 'WG' and 'NOISE' labels, but some other columns are specific to only one label. For example

CTC     LABEL GRADE G-SYMBOL G-VALUE AVERAGE
------  ----- ----- -------- ------- -------
345674  RR    B                      11,2
459864  WG    A                       2,1
893023  NOISE       ))       71      71,2

... where:

CTC column is a term used to group all tires having same characteristics and belonging to same tire's line's name. Example: 'Goodyear EAGLE 155/60R20 95H Extra Load'.
LABEL contains the shortname of the label.
CTC+LABEL represents the table's primary key.
GRADE contains the grade (level) of the label. For 'RR' and 'WG' the grade can vary from 'A' to 'G' where 'A' is the best grade. For 'NOISE', the grade is defined by a symbol and not a letter.
G-SYMBOL has been added to represent the grade of 'NOISE'. Acceptable values are ')' or '))' or ')))', where ')' is the best value for 'NOISE'.
G-VALUE has been added to represent the grade of 'NOISE'.
AVERAGE is the average of all measures taken on some tires that are used to fix the grade of all tires belonging to same tire's line's name.

As you can see, GRADE is only used by 'RR' and 'WG' and G-SYMBOL/G-VALUE are only used by 'NOISE'. AVERAGE column are used by all labels but magnitude of value for each label are distinct. For 'RR', values are between '6' and '15' kg/T, and for 'NOISE' values are between '60' and '80' dB.

Question(s):

How many tables must be defined to manage all these fields?

Solution A: 1 big table?

BIG-TABLE: CTC / LABEL / GRADE / G-SYMBOL / G-VALUE / AVERAGE

Solution B: 3 tables (one in label)?

RR:    CTC / AVERAGE / GRADE
WG:    CTC / AVERAGE / GRADE
NOISE: CTC / AVERAGE / G-SYMBOL / G-VALUE

Solution C: 4 tables (one with specific field in label + 1 for common fields)?

RR:     CTC / GRADE
WG:     CTC / GRADE
NOISE:  CTC / G-SYMBOL / G-VALUE
COMMON: CTC / LABEL / AVERAGE

Solution X: another solution?

What are the advantages and inconveniences of each solution?

Some other considerations:

It is very uncommon to mix ALL labels.
In the future, some new labels (as 'SNOW') will be added.

Solution A is a bad solution if you have to add columns in future.
Using alter table in production environment is discouraged. Can hold the
database because of locks needed. This could not be a problem, but it really depend on the number of rows and concurrent transactions.
On the other hand is very simple to retrieve data, is a sort of denormalization,
so no join imply fast response time.

Solutions B and C are two similar desings. I dont like them. You wont be able to have cumulative
data (count(1), group by..) in a single statement. You still have the problem above, if you need to
add columns. But now this problem is repeated 3 o 4 times, compared to solution A. Not good.

There is a 4th solution, using a table for key/value. In this scenario you have a big table whith
only the common columns, and a second table whith the following fields:

id / bigtable_id (references bigtable primary key) / key / value

Key column will store fields which apply only to certain type of tire coupled whith its value.
For example there could be rows like:

id ==> 1 /bigtable_id ==> whatever needed /key ==> grade   /value ==> A

id ==> 2 /bigtable_id ==> whatever needed /key ==> g-value /value ==> 71

There is an one-to-many relationship between tables.
This is the solution I have seen most of the time. Is simple to manage, no need to alter table:
if you need a new field just add new record/records.

In my opinion the fact that mix label is uncommon, is a secondary issue in design. If the problem
is the number of rows, you can use partitions and subpartitions, but I prefer have the DB desing in
order. Of course this is only my way to see order   ;)

my 2 cent


How to scale MySQL transaction wrapped queries? So I'm working on an app that wraps many of it's SQL queries in a transaction for it's SQL usage and I'm trying to think of some ways to scale the DB layer up....

Up until now we've been throwing hardware at the problem, slowly upgrading our Rackspace cloud servers...but we're reaching the upper echelons of their cloud server offerings.

I'm thinking the standard R/W split won't work for us because the transaction gets stuck to a single server eliminating the benefits of the R/W split. (Is that right?)

I've looked at dbShards and ScaleBase both of which look like interesting solutions to the problem, but as a cash strapped startup we'd prefer to use something OSS or devote some man hours creating a sharding strategy instead.

So I suppose the question boils down to, are Galera/MySQL Cluster and sharding my only two options for scaling this setup?

EDIT

To answer some more questions...

We manage a great deal of financial and inventory related data, for this reason we wrap most of our SQL traffic in a transaction so it's all or nothing.

We have ~100 tables in our DB with ~30 of them getting a great deal of use. The most commonly used tables have anywhere from from 15MM to 35MM rows.

Our transactions span across 20-30 tables with joins between ~10 of them. The queried tables are rather large and we have made some inroads with respect to query optimization and proper indexing, but no one is a fully fledged DBA in house....closest is me haha

A lot of our DB traffic is machine generated, we do a lot of syncing with third party data sources. As such, most of the SQL queries are wrapped in a transaction to ensure data consistency when performing all the calculations and syncing with the third parties (and to allow for a rollback if the link to the third party gets disrupted)

Based off of our cacti monitoring it looks like a great deal of our SQL traffic is reads (~85%R to ~15%W)

Just as a note, we haven't hit too much of a bottleneck with our current cloud server but I'm doing my due diligence ahead of time to make sure we don't run into the wall without a plan.

Read/Write splitting doesn't move all reads to a replica server, it only moves the reads away from the master that are sensible to move... so any reads, for example, done by your processes that are integrating third party data would remain on the master, along with any reads related to transactions, but many other reads, such as report generation or compiling of aggregate/summary views could probably be offloaded to another machine.

How much this would help vs. how complicated it is to implement is very situation-specific.  The 85%/15% split you're observing seems promising, but it doesn't tell us how many of the reads are directly related to the writes and thus need to stick with the master server.

Another concern with read/write splitting is the read-after-write consistency seen by your users.  If I'm browsing my bank account transactions, that information could easily be read from a replica; on the other hand, if I just did a transfer between accounts, then go back to look at my transactions, the safest thing for the system to do is to read my transactions from the master to ensure that what I just did is immediately visible to me.  Again, the nature of your application is the driving factor in determining the implications of this.

Neither of these points should be taken as advice "against" read/write splitting.  You almost certainly should do some of it... the questions are "how much can you safely do?" followed by "how much will that help?"

When you are integrating the third-party data, something you want to keep in mind is that ideally, database transactions should generally be as quick as possible and as small as possible. From what you've described about using transactions to ensure consistency in the event of a lost connection from a third-party data source, it sounds like you might have room for improvement, here.  The best bet -- probably -- is to retrieve the data from the external source, save it locally, verify its integrity, and then proceed with the integration.

If a large portion of your load is machine-generated, you probably also have options of making those processes less aggressive in their actions, more deterministic in their scheduling, and more cooperative with each other.  Some of those processes, possibly, could be staged on another server, the calculations done, and the final result sent to your master server... but like everything else, it depends on the nature of the processes.


Pivots giving too many results I'm trying to learn how this pivot thing works. I don't think I'm understanding it quite right.
I just want to do a simple count for a bunch of item types that have some statuses. The table I'm working from has columns: [Status], [ItemType], and a bunch of others.

Here is what I'm doing currently

SELECT [NEW]
    ,[IN]
    ,[OUT]
    ,READY = [READY FOR DISPOSAL]
    ,TRANSIT = [IN ROUTE TO DISPOSAL]
    ,[DISPOSED]
    ,[TYPE] = [ItemType]
FROM [EVAULTTEST].[dbo].[Items] AS ei

PIVOT(
COUNT([Status])
FOR [Status] IN ([NEW], [IN], [DISPOSED]
    , [IN ROUTE TO DISPOSAL], [OUT], [READY FOR DISPOSAL])
) AS piv

And the result

1   0   0   0   0   0   FIREARMS
0   1   0   0   0   0   FIREARMS
1   0   0   0   0   0   FIREARMS
0   1   0   0   0   0   FIREARMS
1   0   0   0   0   0   MONEY
1   0   0   0   0   0   Automobiles
0   1   0   0   0   0   Aircraft
1   0   0   0   0   0   Automobiles
1   0   0   0   0   0   Automobiles
1   0   0   0   0   0   ALCOHOL
0   0   1   0   0   0   MONEY
0   0   1   0   0   0   AUTOMOBILES
0   0   1   0   0   0   AUTOMOBILES

Why don't these collapse into something like this:

2   0   0   0   0   0   FIREARMS
0   2   0   0   0   0   FIREARMS
1   0   0   0   0   0   MONEY
3   0   0   0   0   0   Automobiles
0   1   0   0   0   0   Aircraft
1   0   0   0   0   0   ALCOHOL
0   0   1   0   0   0   MONEY
0   0   2   0   0   0   AUTOMOBILES

It's getting upset about your other columns. So try:

SELECT [NEW]
,[IN]
,[OUT]
,READY = [READY FOR DISPOSAL]
,TRANSIT = [IN ROUTE TO DISPOSAL]
,[DISPOSED]
,[TYPE] = [ItemType]
FROM (SELECT ItemType, Status FROM [EVAULTTEST].[dbo].[Items]) AS ei

PIVOT(
COUNT([Status])
FOR [Status] IN ([NEW], [IN], [DISPOSED]
, [IN ROUTE TO DISPOSAL], [OUT], [READY FOR DISPOSAL])
) AS piv

... and this is because the implicit grouping is on any column that's not in the PIVOT clause. So removing the extra columns in the first part of the query should fix it.


PostgreSQL: Copy Table Functions to another database schema as the title said. Can we move or copy existing table functions to another database schema?

I can get all the functions from a database schema using command below (Taken from here):

SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema='public';

And I also can get the function code details using command below (Taken from here):

\df+ public.<function_name>

As I need to move the database to the new server, so I need to copy all table functions inside a specific schema. Say I have a functions called update() inside a schema analytics.

Can we copy this functions to another database ?
and is there any command to see when this functions will be run?

Note that I am using PostgreSQL 11 and Ubuntu 18.04

You can't tell pg_dump to dump functions only.  You can exclude tables, but you still have to deal with other objects (like a_horse_with_no_name said).

However, you can make a dump without data (-s) and filter it upon restoring.  Note the -Fc part: this will produce a file suitable for pg_restore, which is able to consume a custom-made table of contents.

First take the dump:

pg_dump -U username -Fc -s -n public -f dump_test your_database

Then create a list of the functions:

pg_restore -l dump_test | grep FUNCTION > function_list

And finally restore them (-L specifies the list file created above):

pg_restore -U username -d your_other_database -L function_list dump_test

Note that this is a nearly verbatim copy of my earlier answer on stackoverflow.


Slave (SQL thread) replicates very slowly See this video: https://mckay.media/Yxjp2

I have two MySQL replication slaves which are replicating from the same master. Both slaves are running on identical hardware. Each has its data directory stored on an SSD, which has comparable throughput. There is no I/O lag as you can see, only SQL lag.

The terminal on the left is the new slave. The one on the right is the existing slave from which the one on the left was cloned. I cloned them by copying all configs (changing server-id), and rsyncing the MySQL data directory.

I can't for the life of me figure out why the new slave is replicating so slowly. The video was taken during an off-peak time. During peak time, the new slave is just unable to keep up at all.

I don't know 100% what was done when the old slave was set up. The individual who set it up is no longer available. The MySQL configs are 100% identical besides the server-id.

I'm running Debian 8 on both slaves. The old slave has MySQL v5.5.43-0+deb8u1-log. The new one has MySQL v5.5.47-0+deb8u1-log. There is nothing noteworthy in the slowlog for the new slave.

I did notice that the new slave has a much larger %iowait in iostat, but I'm unsure what to make of this, since both are running on SSDs. sda is an HDD, and sdb is the SSD where the MySQL data directory is stored (on both machines).

Old slave:

Linux 3.16.0-4-amd64 (mysql-cluster-5)  03/03/2016      _x86_64_        (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          34.11    0.00    0.93    0.22    0.00   64.74

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               3.85         0.25      1351.60    5234043 28302154992
sdb              73.54         2.41      1684.24   50383141 35267640148

New slave:

Linux 3.16.0-4-amd64 (mysql-cluster-6)  03/03/2016      _x86_64_        (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           1.53    0.00    0.23    8.75    0.00   89.49

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda               2.49        23.51        25.60     186982     203664
sdb             145.48       581.01      5359.82    4621437   42632848

Here is the new slave's my.cnf, which is identical to the old one with the exception of server-id:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /ssd/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
max_connections        = 10000

innodb_buffer_pool_size = 20G
innodb_thread_concurrency=8
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=500
query_cache_type        = 1
query_cache_limit       = 64M
query_cache_size        = 512M
sort_buffer_size=74M
read_rnd_buffer_size=16M
tmp_table_size=1G
max_heap_table_size=1G

log_error = /var/log/mysql/error.log

slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log      = 1
long_query_time = 2
log_queries_not_using_indexes

server-id               = 11
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

There is nothing notable in /etc/mysql/conf.d/.

All of my tables are InnoDB.

Why is it replicating so slowly?

(Too many questions for 'comments'.  Anyway something here might help locate the naughty.)

Backup occurring?

sync_binlog - Probably 0 is OK for Slave.

innodb_flush_log_at_trx_commit - may as well be 2 on a Slave.

Differences in OS config, especially relating to I/O?  Swappiness?

Are clients hitting both Slaves?  In the same proportion?

How much RAM?

query_cache_size        = 512M -- This is bad for any system.  Limit it to something like 50M.

tmp_table_size=1G -- This is dangerous.  Some complex queries could chew up RAM and cause swapping, which is really bad for performance, especially I/O.  Keep that down to, say, 1% of RAM.

You have the slowlog turned on -- Look for clues there.  If you don't find anything, then turn ON log_slow_slave_statements also.

Is Seconds_behind_master increasing or oscillating?


SQL Server Installation Error: Object reference not set to an instance of an object I am trying to install Microsoft SQL Server, but whenever I try to do so, the Database Engine Services feature fails and I get the following error:

  Object reference not set to an instance of an object

This has happened on Enterprise & Developer editions, both 2016 and 2017. The summary log isn't particularly helpful (at least that I can see), and the only other advice I've found is to make sure .NET Frameworks 3.5 and 4 are enabled on the PC.

Please see here for summary, detail, and what I believe are the error logs. If anything else is needed I'll drop it in that folder as well.

Any help would be greatly appreciated.

The Details.txt log shows an error of:

(01) 2018-02-07 01:38:44 Slp:     Inner exception type: System.OverflowException
(01) 2018-02-07 01:38:44 Slp:         Message:
(01) 2018-02-07 01:38:44 Slp:                 Arithmetic operation resulted in an overflow.
(01) 2018-02-07 01:38:44 Slp:         HResult : 0x80131516
(01) 2018-02-07 01:38:44 Slp:         Stack:
(01) 2018-02-07 01:38:44 Slp:                 at SNIOpenSyncExWrapper(SNI_CLIENT_CONSUMER_INFO* , SNI_ConnWrapper** )

As the first error that occurs, when it initially attempts to connect to the database engine. This might be the better error to chase down, since it seems to indicate something interfering with the connection. A quick search shows some similar with people running Lavasoft (Adaware, etc) Software - any chance you're running this?


Why is using a CTE so much faster than using a #Temp Table in this query? I have 2 different queries for identifying dups (below). The only difference between the 2 queries is that one uses a CTE and the other uses a #Temp table.

Does anyone know why the CTE is so much faster than the #Temp table (0:20 seconds VS. 1:22)?

I would rather use the CTE but I need to run 2 STATEMENTS using the CTE (DELETE from CTE and then INSERT INTO table FROM CTE) but SQL Server only allows you to write one STATEMENT on the CTE.

Query 1:

WITH DUPS AS(
   Id,
   Column1,
   Column2,
   Column3,
   RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
   FROM mytable
)
  Select top 1 * FROM DUPS WHERE RN > 1

Query 2:

 SELECT
           Id,
           Column1,
           Column2,
           Column3,
           RN = ROW_NUMBER()OVER(PARTITION BY ID ORDER BY id)
       INTO #DUPS
       FROM mytable

       Select top 1 * FROM #DUPS WHERE RN > 1

I believe if you look at the execution plans you'll see that the CTE gives you a better plan because it can take better advantage of the TOP. The Temp table will have to create the table in TempDB, persist all of the data to disk, and then select the top record. If you remove the TOP from both queries I bet you'll get much closer performance. That being said I would generally expect the CTE to out perform the temp table in a single use scenario like you have here since the CTE is doing less work than creating/inserting into a temp table.

This is probably just sample code but it is worth mentioning your results are non-determinate because you're using TOP without an ORDER BY clause.

For further reading you should check out this question. This answer to that question seems to apply to your situation in particular.

For the implied second half of your question:

Since you say you need to use the data from the CTE at least twice then you have four options:

Copy and paste your CTE to both places
Use the CTE to insert data into a Table Variable, and use the data in the table variable to perform the next two operations.
Use the CTE to insert data into a Temp Table, and use the data in the temp table to perform the next two operations.
Write a better tailored CTE. I assume you're doing different things so the queries must be slightly different.

I tend to prefer the option 2 (table variable) or option 4 (tailored CTE's) approach. I don't like the duplication and extra maintenance of copy/pasted CTE's. I also like the explicitly reduced scope of the table variable over a temp table. For an authoritative treatment on the differences between table variables and temp tables check out this answer. As far as choosing between options 2 and 4; that will depend on whatever your profiling dictates and/or what constraints you have to work within.


Good idea to include summary of frequently accessed data from another foreign-keyed table? Sorry for poor title, I'll try to explain it in more detail here.

I have an application that often gets a row from table A, then looks up a column from that in a foreign key mapping table (table B), and finally looks up several rows with the corresponding foreign key in table C.

My question is: would it be better for table A to have a column containing data in the particular column we often want from table C? This would be anti-normalization, but would save on a lot of queries.

As an example, let's say we have a table "french words" (table A), with columns like:

id, word, pronunciation, frequency

We also have a table (table B) that maps the "french words" id to any number of "german words" id (table C)

And then in "german words" (table C) we also have id, word, pronunciation,  frequency.

When we look up a French word, we often want to get the highest frequency German word that it's associated with via table B. Would it be better to just include a highest_frequency_german_word column in the French words table?

Is there some particular reason you're worried about how much work your computer has to do to join three tables?

The purpose of normalizing your schema is to make it much harder to corrupt your data when you insert, update and delete it.  Normalizing your schema means that reading your data will be more work (generally), but your code for maintaining your data will be much simpler and therefore much less prone to data corruption errors, as well as being cheaper to build and maintain, of course.

If your data is pretty static, i.e. you write it once and then read it a lot, then the benefits of normalization are less important.

If your performance or resource utilization is very critical, and you can't get the performance you need from normalized data, then you can consider denormalizing your schema.  However, whenever you use denormalization you should do it with full knowledge of all of the potential traps you've set for yourself with respect to data corruption.

Normalize by default and denormalize as necessary, and with caution.


How to create unique index for both cases I have table in PostgreSQL with sender_id and receiver_id and a unique index:

add_index :my_table, [:sender_id, :recipient_id], unique: true

Table will created, if sender_id: 1, and recipient_id: 2.

I can create another table, with sender_id: 2, and recipient_id: 1. But table with this ids, (but in different order) already exists.

How can I make unique for both cases?

sender_id: 1, recipient_id: 2
sender_id: 2, recipient_id: 1

For Postgres and Oracle:

create unique index unique_combinations
    on my_table (least(sender_id, recipient_id), greatest(sender_id, recipient_id));