MariaDB Fails to log in error log I am using MariaDB Version 5.5.56
The database was restarted 2 days ago but I am not seeing any log entries in the error log except an error importing from a dmp file. The data seems complete and all processes and transactions are occuring correclt just no error logging. Is there a way to turn this on? I have tried "flush error logs" command but still no log entries.

You can execute this to see where the error log file is supposed to be written:
show global variables like 'log_error';

This can (sometimes) show a relative path, relative to the datadir.
From the documentation:

The error log is active by default. The log-error=filename option determines where the output will be written. If no file name is specified, the log will be written to host-name.err. If no absolute path is specified, the file will be written to the data directory (determined by the value of the datadir system variable).
On Unix systems, if the --log-error option is not used, the errors are written to stderr (usually, the command line).
On Windows, if the --console option is specified, and --log-error is not used, the errors are written to the console. If --log-error is present, --console is ignored.

Additionally, for warning messages, the log_warnings variable need to be > 0. There are different warning levels, and the higher the value of log_warnings, the more kinds of warning messages are logged. For details, see the documentation for the log_warnings variable.
To see you current setting, run:
show global variables like 'log_warnings';


What is the LOB storage space? I have lot's of large objects ((n)test, varchar, image types) in my SQL-Server tables. I read the following article about sp_tableoption "In-Row Data" and "large value types out of row" but did not understand those tons of text.

It's explained that large object data types can depending of the settings be stored in the table or outside anywhere. It's called the "LOB storage space". Where is this space? What is the benefit of those settings? Is this suitable to raise read-performance?

In most cases, leave the default option (0).
Assuming you have default options, layman's version of the article you referenced:

Remember that SQL Server has a fixed page size of ~8K which means you cannot physically store more than ~8k per column in-row (it's possible to have bigger than 8K total rowsize but that's a different topic)
BLOB types support up to 2GB but actual usage is usually much lower, a recent investigation into found over 90% of databases we probed (using scripts) to have BLOB cells smaller than 2K.
If your total rowsize including BLOB data is less than the 8k max, SQL Server will try to store the whole thing (BLOB and other data) in the same row as the rest of your data for that row (I think this was introduced in 2008 or perhaps 2005, can't remember for sure)
If your total rowsize including BLOB data is greater than 8K, SQL Server will store the BLOB data in a separate location from the other data in your row. This other location is generically referred to as LOB storage space. This is highly simplified but just grok the concept for now.
If the BLOB is less than ~40K and you have space in the row for a 72b pointer string, SQL Server will store the string of pointers for the BLOB (sitting somewhere else) in the same row as the rest of the row data. This saves a bunch of things during insert and future access.
If the BLOB is more than 40K, SQL Server needs to store more pointers for the BLOB so instead of consuming lots of space in the row, it stores a shorter (24b) pointer string that points to a new page. That new pages stores as many pointers as needed for the BLOB. Don't know exact reason for picking 5 pointers specifically (for the 5x ~8K pages= ~40K) but the goal is to balance optimizing perf for the 80-90% of BLOB sizes and row space usage so there's a "reasonable" number of rows per page.
When accessing data, less movement is good, fewer operations is good, opposites are bad. SQL Server optimizes for this. For example:

if your BLOBs are small, you can read the entire row or multiple rows from a single request (however many rows fit into an 8K page, and however many pages you need in a 64K extent)
If your BLOB is bigger than the space available in-row but less than ~40K and the row has room for the pointer string, SQL Server can get all the pointers for the BLOB directly from the row and retrieve the BLOB with them.
If your BLOB is greater than ~40K or the row does not have room for the pointer string then SQL Server retrieves a pointer to the root node of a pointer tree on a different page, read that, then retrieve the actual BLOB chunks.

If that made sense then check out a really nice write-up on BLOB storage http://sqlmag.com/t-sql/varbinarymax-tames-blob


PostgreSQL CREATE TYPE using multiple relations? I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax.

It appears to me that this type does not allow to compose a type out of relations/tables. In other words, I can do this:

CREATE TYPE compfoo AS (f1 int, f2 text);

but I can't do

CREATE TYPE compfoo AS (f1 relation, f2 relation);

I was wondering if my understanding is correct, and if not, how one could achieve the latter?

I want to indicate that I don't have any particular application for it. I would actually be interested in starting a discussion on an application! I am just trying to expand my understanding of this feature which is often touted as a very powerful feature in the extensible DBMS world.

The old version with support is 9.4 today and i can achieve your case, and then in the recent version too.

for example:

create table a ( i int, j int);
create table b ( k text, l text);
CREATE TYPE compfoo AS (f1 a, f2 b);
create table c (m compfoo );
insert into c values ( row(row(1,2),row('hello','world'))   );
select * from c;

result:
("(1,2)","(hello,world)")

you can check this test in
https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=4499c5d52e876979d81fb3f2537c2439

check your version


Maintain Up-To-Date Copy of Very Large Table if Clustering/Partitioning/Moving Operation Needs to be Killed/Rollback I've appended the following information in a previous post but I asked the 'why' instead of the 'how'.

So here's the question...

SQL Server 2014, CU6. 4.5 TB database, Simple recovery. The most important/accessed table is 1.6 TB, located on the primary filegroup. It's currently about 95% fragmented and so most all queries need to do a full scan of its 600M+ rows. Not surprisingly, it's an IO bottleneck and I'm unable to defrag in any acceptable window of time. My goal is to move this table out of the .mdf and cluster/partition onto its own filegroup (ONLINE=ON & SORT_IN_TEMPDB=ON). This new filegroup would be physically isolated on a dedicated disk array.

But I'm very concerned about any issue that may cause (or necessitate) a rollback (things such as a power failure, deadlocking, corruption, unacceptably slow performance during the operation, etc.).

Further, given the length of time this clustering/partitioning/moving process may take, any backups taken during that time would be useless. They'd immediately go into recovery after being restored and, again, rollback/rollfoward time would be unacceptable. Same deal if I were to turn on full logging and use transactional replication or log shipping -- worthless secondary or subscriber.

How would you handle this process?

I've thought about making a copy of the table, triggers on the source to keep the copy up-to-date.

I've got large buffers of space on all volumes involved. I want to pull the trigger. Boss says 'not yet'.

Thanks for reading...

-Greg

I've done this a few times. My servers are only 2008R2, however. I've moved tables like this and 'partitioned' data both because data files were reaching the max size (16tb) and identity columns were reaching the integer maximum.

Index rebuilds on big tables can take some time to roll back. Reorganization does not, however it is much slower (single threaded). You can kill it immediately and it will just stop where it is, so you could maybe do it over the course of several nights if need be. I had to do this with some quite unwieldy full text catalogs.

Rebuilding clustered indexes to new file groups is not an online operation if you have a (MAX) data type in your table -- see comments below. That was something that surprised me when I was working on my process.
Also, be careful with sorting in tempdb. I once attempted a full statistics scan on a 3tb table and my 500gb tempdb LUN ran out of space overnight.

Let me know if you have any questions.

Thanks


Basic information splitting and table design in a database about people, accounts and transactions I am having trouble deciding which design would be considered a better practice.

Say, for example, I have two tables:

TABLE Person
pid int primary key
name varchar
age int
bank_account varchar

TABLE Transactions
tid int primary key
date datetime
amount float
from int references person
to int references person

This is just an example and not using any particular syntax.

Now, my Person table will have multiple entries that have all the same information aside from the bank_account, since a person can have multiple different accounts they make transactions from or receive to. Would it be better to do it like this, or would it be better to create a separate account table that holds all the different account numbers with a reference to the person that owns it, and then changing the references in the Transactions table to reference the new account table?

I know that duplicating information in person is in accordance with 1NF, but it doesn't seem like good design to me. Any insight would be appreciated.

It seems a cliché, but it all depends on the specific informational requirements of relevance, because a database has to be designed with the exact business needs in mind.

As for the circumstances at issue:

In case that in the applicable business environment each Transaction is performed from  Person to Person and the concept of Account does not exist, then leaving the tables as you decribe in the no-particular-sintax example seems to be mirroring the characteristics of the business context accurately.
In case that, on the contrary, in said business scenario every Transaction is carried out from  Account to Account, then, yes, creating an Account(s) table which would be referred to from two FOREIGN KEY constraints in the Transaction(s) table would be the best practice, because by doing so you would be reflecting the characteristics of the business domain with precision. The Transaction(s) rows would then be associated with the respective Person(s) rows via the corresponding Account(s) counterparts.

You state in the question itself that

  [...] a person can have multiple different accounts they make transactions from or receive to.

...therefore, proceeding as mentioned in case no. 2 would be more convenient in your very specific situation.

On the other hand, when designing a relational database, I highly advise that you start (i) analyzing the situation from a conceptual perspective —identifying entity types, properties, associations, drawing diagrams, etc.—, then (ii) continue establishing the logical aspects —laying out the corresponding tables, types, columns, constraints to ensure integrity, domains if possible, testing with the normal forms, etc.—, and then (iii) putting up the physical settings of convenience —optimizing database response by configuring indexes, partitions, hardware upgrades, network configurations, etc.—. In this manner you would be approaching each scenario with a clear separation of concerns.

Regarding first normal form (1NF) —which is brought up in the question—, it basically has to do with the definition of atomic domains (i.e., each column must contain exactly one value of the applicable domain/type per row), which, as well is entirely dependent on the informational requirements of significance. Further normalization (2NF and above), in turn, implies getting rid of dangerous redundancies (special types of undesirable data duplication) and is executed in terms of functional dependencies (interrelationships between sets of attributes/columns).

By the way, you may find of interest this Q & A, which involves a similar scenario and contains several notions about database design that may be applied from a general point of view


Insert into Table ignoring duplicate values I am having a brain fart on figuring this out.  I have the following two Tables:
Table: parts
    part_id          INT IDENTITY(1,1) NOT NULL,
    part_number      VARCHAR(50) UNIQUE NOT NULL,
    part_description VARCHAR(MAX) NOT NULL,
    information      VARCHAR(MAX) NULL,
    manufacturer_id  INT NOT NULL,
    subcategory_id   INT NOT NULL

Table: part_temp
    part_num  VARCHAR(50) NOT NULL,
    part_desc VARCHAR(MAX) NULL,
    info      VARCHAR(MAX) NULL,
    man_id    INT NULL,
    sub_id    INT NULL

part_temp is my temporary table that contains data from a CSV file, that is why only one column is set to be NOT NULL.  I need to insert the data from part_temp into parts.
I have properly cleaned the data in the table so there are no null values trying to be inserted into rows which require a value.  My issue however is with my UNIQUE constraint for my part_number columns in the parts table.  There are duplicate values within the part_temp table so I need a way to be able to skip over them during my insert.  This is what I have tried so far, but it does not work:
INSERT INTO parts
SELECT DISTINCT pt.part_num, pt.part_desc, pt.info, m.manufacturer_id, s.subcategory_id
  FROM part_temp                AS pt
       FULL OUTER JOIN man_temp AS mt ON pt.man_id = mt.man_id
       INNER JOIN manufacturers AS m  ON mt.man_name = m.manufacturer_name
       FULL OUTER JOIN cat_temp AS ct ON pt.sub_id = ct.category_id
       INNER JOIN subcategories AS s  ON ct.category_name = s.subcategory_name
 WHERE NOT EXISTS(SELECT part_number FROM parts WHERE part_number = pt.part_num)

These are the tables included in the joins not listed above
Table: man_temp
    man_id INT NOT NULL,
    man_name VARCHAR(100) NOT NULL

Table: manufacturers
    manufacturer_id   INT IDENTITY(1,1) NOT NULL,
    manufacturer_name VARCHAR(100) NOT NULL

Table: cat_temp
    category_id   INT NOT NULL,
    category_name VARCHAR(100) NOT NULL

Table: subcategories
    subcategory_id   INT IDENTITY(1,1) NOT NULL,
    subcategory_name VARCHAR(100) NOT NULL

What is it that is wrong with my INSERT query?
The specific error I am getting is:

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint.  Cannot insert duplicate key in object 'dbo.parts'.  The duplicate key value is (31335A11)

part_num 31335A11 appears in the csv file more than once.  As such it appears in the part_temp table more than once.  It would be easy if it was just this entry, but I have more than 1,000 repeat entries, so it would take forever to remove all the duplicates.  Nothing exists in parts as it is a brand new empty table I am trying to put values into.

If I run into a Violation of UNIQUE KEY constraint the first thing I do is ask myself the following questions:

Does the duplicate key already exist in the target table?
Is the key duplicated in any source table?

From answering the above you will have a good idea of where the duplicate value is sourced from and a reasonable idea of the cause. Example possibilities are:

Duplicate exists in source table
Duplicate does not exist at source, but a transform of the data causes duplicates to be returned

Ultimately you need to strip this problem back to basics, as you have the duplicate value in question you need to start digging into the data and pay close attention to the source query. Luckily in your example it is a simple query and you can easily strip out joins to see if any of those introduce the duplicate, or if indeed the duplicate exists due to non-key attributes being different therefore causing your DISTINCT to not do what you expect it to do.

I guess what I am trying to say is that there isn't necessarily a quick win for this kind of problem and you need to do some good ol' fashion detective work using the clues provided by SQL.

Moot point, but I like to explicitly list the columns on an insert so that nothing breaks if the order of columns on the target table is different to that of the source query.


Microsoft Help Viewer in ssms 2014 doesn't open local documentation I have Visual Studio 2013 installed
I have downloaded all documentation I need installed via visual studio. I can open it with the command

"C:\Program Files (x86)\Microsoft Help Viewer\v2.1\HlpViewer.exe" /catalogName VisualStudio12 /locale en-US /sku 3000 /launchingApp Microsoft,VisualStudio,12.0 /manage

And it works fine, but in SSMS, I want to be able to use the SHIFT + F1 shortcut to open local documentation quickly. (select keyword and press SHIFT + F1 to open it)

I have SSMS 2014, if I select a SQL keyword and press SHIFT + F1, I get an error that says that I don't have local documentation, it is also trying to open Help Viewer 1.0 instead of 2.1. I want to know what I have to do to make SHIFT + F1 open sql documentation in SSMS 2014.

Essentially 2014 is configured by Microsoft to use Help Viewer 1.0 so you have to redirect it yourself to use 2.x.

There is a post on MSDN social on how to do this here and the handy little tool you need is on CodePlex here.

In regards to why it is not finding your documentation when it loads Help 1.0, I am going to assume it is because you have installed the documentation in the Help 2.0 directory and not the 1.0 directory (big assumption as you didn't say otherwise).

Also, be aware that the 2.x documentation format differs from the 1.x format so even if you did put it in the correct directory it may not be able to read it anyway.


In which situations is TokuDB higher performance? I noticed that WordPress runs quite a bit faster when I use XtraDB (MariaDB's InnoDB replacement) instead of TokuDB. This is strange, because TokuDB is advertised as being 20x faster.

However, I noticed that YOURLS stat pages are a tiny bit faster when I use TokuDB for the gigantic (~22,000 rows) log table.

So the question is: What situations will TokuDB be a higher-performance database engine than others, such as XtraDB/InnoDB?

Unless your WordPress site is huge, you are unlikely to see much performance improvement in switching to TokuDB, if any at all. TokuDB's 20x performance advantage is for an indexed insertion workload, where the secondary indexes do not fit in memory.

You will be using less space on disk with TokuDB's compression.


What is the difference between hadoop, mongoDB, Big data? What is the difference between Hadoop, MongoDB, Big data ?

3 different things.
Hadoop is a framework. Something you use to develop an entire application.
MongoDb is a db. A nosql one. It is where you store data.
BigData is a concept. It is related to huge quantity of data. Where huge is not a fixed parameter. 4 mb was huge in 1960. Now huge means several terabyte.


My SSMS is bringing wrong object in "Script Stored Procedure as" > "CREATE To" > "New Query Editor Window" When I go to Object Explorer and expand down to Stored Procedures (or Tables), right-click on one of them and choose "Script Stored Procedure / Table as" > "CREATE To" > "New Query Editor Window", it takes a while (becomes unresponsive) and then brings another object (function / table).

If I do the same but choose other action (for instance, "ALTER" for Procedures or "DROP And CREATE") instead of CREATE, it brings the correct object.

It started to behave that way without any clear reason, except for, perhaps, the last SSMS update. I thought I had successfully used CREATE just a few minutes before noticing that error, but later I realized I've used ALTER and not CREATE.

I've tried different databases and even different servers, with the same results. I've already restarted the machine but it didn't change anything. I've also asked a coworker to do the same steps and he didn't get any errors in his machine.

Obs.: It has nothing to do with permissions; it is also happening with my local SQL Express installation (where I have full access).

Is there any template for those script generation? If yes, where are they stored? If not, how can I possibly fix that?

Version information:

Microsoft SQL Server Management Studio = 13.0.16100.1;
Microsoft Analysis Services Client Tools = 13.0.1700.441;
Microsoft Data Access Components (MDAC) = 10.0.14393.0;
Microsoft MSXML = 3.0 6.0;
Microsoft Internet Explorer = 9.11.14393.0;
Microsoft .NET Framework = 4.0.30319.42000;
Operating System = 6.3.14393;

I believe this is what you need. That's a link talking about changing templates in the folder / file location when creating stored procedures. It sounds like someone altered the file.


error when calling sqlservr.exe here is the error I get:

C:\WINDOWS\system32>"C:\Program Files\Microsoft SQL Server\120\LocalDB\Binn\sqlservr.exe" -m
2017-09-27 16:09:26.50 Server      Error: 17058, Severity: 16, State: 1.
2017-09-27 16:09:26.50 Server      initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 32(The process cannot access the file because it is being used by another process.).
2017-09-27 16:09:26.80 Server      Error: 17058, Severity: 16, State: 1.
2017-09-27 16:09:26.80 Server      initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG'. Operating system error = 32(The process cannot access the file because it is being used by another process.).

Any hint to help me would be great. Thank you!

Analysis

You receive the above error message if the sqlservr.exe program is already running or if your privileges are not correct.

Solution

Try stopping the SQL Server () service first and the you should be able to start sqlservr.exe -m without any further issues.

IF the service is not running, check the Processes tab in the Windows Task Manager for a process sqlservr.exe.

IF the the service is not running and no other process can be seen in the Task Manager ensure you start the Command Prompt with Runas Administrator... and you should be able to start the process.

IF you are still unable to start the instance, then you might have to add additional parameters when manually starting your instance.

SQL Server Startup Parameters (2014)

The parameters for manually starting SQL Server are as follows:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>sqlservr.exe /?
usage: sqlservr
        [-a,]                    (adding an L2 buffer pool file)
        [-c] (not as a service)
        [-d file] (alternative master data file)
        [-l file] (alternative master log file)
        [-e file] (alternate errorlog file)
        [-f] (minimal configuration mode)
        [-m] (single user admin mode)
        [-g number] (stack MB to reserve)
        [-k ] (checkpoint speed in MB/sec)
        [-n] (do not use event logging)
        [-s name] (alternate registry key name)
        [-T ] (trace flag turned on at startup)
        [-x] (no statistics tracking)
        [-y number] (stack dump on this error)
        [-B] (breakpoint on error (used with -y))
        [-K] (force regeneration of service master key (if exists))
        [-v] (list version information)

See documentation for details.
2017-09-27 10:21:14.23             SQL Server shutdown has been initiated


MYSQL slow down using UNION ALL I have to 2 table, the first is a MEMORY table with 3.000.000 record the second is at the moment empty. The structure of these table is identical, both have a int(11) primary key, the two table have all primary key field different (the same key is not possible in both tables)

If I query the first table I have an answer in 0.1 seconds, the query on second table is immediate 0.01 seconds.
But merging the 2 with the UNION ALL is dramatically slow (1 min 21 sec).

SELECT * FROM memtable1 LIMIT 1000

  (0.1 sec.)

SELECT * FROM table2 LIMIT 1000

  (0.01 sec.)

SELECT * FROM memtable1
UNION ALL
SELECT * FROM table2 LIMIT 1000

  (1 min 21 sec.)

How can I improve the speed of the query?

Your query is equivalent to this one:

(SELECT * FROM memtable1)
UNION ALL
(SELECT * FROM table2)
LIMIT 1000 ;

The query asks for all the rows from memtable1, without any limit. Then the UNION is applied and the LIMIT 1000 is applied to the result of the UNION. It seems that the execution plan is not very efficient, and getting the rows of both tables in memory first (so 100 thousand rows) and then applying the limit.

If you want only 1000 rows from each table, try this:

(SELECT * FROM memtable1 LIMIT 1000)
UNION ALL
(SELECT * FROM table2 LIMIT 1000) ;

And if you want up to 1000 rows from both tables:

(SELECT * FROM memtable1 LIMIT 1000)
UNION ALL
(SELECT * FROM table2 LIMIT 1000)
LIMIT 1000 ;


Oracle RAC and DataGuard configuration We have deployed an Oracle RAC cluster which consists of three Oracle instances. As we are considering the HA issue, and as far as I know, the only way of Oracle HA is DataGuard. So is it possible that we deploy DataGuard in another data center as backup for the master RAC cluster and if master RAC cluster down, We can switch the DataGuard to master which enable read and write operations. Someone told me that it is a very complicated process. Is that true? how much time does this process cost? Is there any other solutions on Oracle HA or any other load balance solution to replace RAC cluster? Does Mysql have the same capability?

(MySQL)

There are several MySQL Replication topologies.  For HA, deploying at least one server in each of 3 datacenters and using Galera Cluster, failover is essentially automatic if a datacenter goes down.  Some form of proxy or load balancer could take care of redirecting users to the still-alive nodes.  When the datacenter comes back up, that node will get back in sync.

If a node crashes and burns, wheel in a new server; the live nodes will pour a copy of the data set into it, and you are back on the air.

And you did not get paged.

(Now perhaps we will hear from users for which it was not that seemless.)

Group Replication is another HA product on top of MySQL.  I have seen 3 nodes being set up during a conference talk.  It took one person less than 5 minutes.

Caveat:  I cannot say whether either of these is easier or harder than Oracle's offering(s).


The multi-part identifier "xxx" could not be bound The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external);

HAVING (SELECT COUNT(*) FROM
(
    SELECT *
    FROM dbo.ContractDailyRoomAllocation da2
    WHERE da2.ContractId = DA.ContractId
    AND da2.RoomTypeId = DA.RoomTypeId
    AND da2.Date >= @FromDate AND da2.Date < @ToDate
    AND da2.IsSold = 0
    AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)) T) = @StayDates
    AND ( (@AllowOnRequestBookings = 'False'
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate)
      >= 0 ) )
      AND ( (@AllowOnRequestBookings = 'True'
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate)
      > 0 )
)

The last 2 AND's give me the following error messages:

  Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
  The multi-part identifier "da2.ContractId" could not be bound.
  Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
  The multi-part identifier "da2.RoomTypeId" could not be bound.
  Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
  The multi-part identifier "da2.ContractId" could not be bound.
  Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
  The multi-part identifier "da2.RoomTypeId" could not be bound.

Any ideas?

Reformatting your code shows the problem. All I have changed is whitespace and added a comment:

HAVING
    (
        SELECT COUNT(*) FROM
        (
            SELECT *
            FROM dbo.ContractDailyRoomAllocation da2 -- <-- da2 defined here
            WHERE da2.ContractId = DA.ContractId
            AND da2.RoomTypeId = DA.RoomTypeId
            AND da2.Date >= @FromDate AND da2.Date < @ToDate
            AND da2.IsSold = 0
            AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)
        ) T
    ) = @StayDates
AND
    (
        (
            @AllowOnRequestBookings = 'False' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) >= 0

        )
    )
AND
    (
        (
            @AllowOnRequestBookings = 'True' OR
            dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate) > 0
        )
    )

As can now easily be seen, da2 is an alias defined in the subquery in the first ANDd term, and so is not available in other terms.

It may be that you want to move the second and third ANDd terms here into the subquery, or it might be more complicated than that.


Is unique index combined with a "not null" constraint equivalent to a PK constraint? I have these legacy table definitions (from here):

CREATE TABLE [dbo].[JobItems] (
   [ItemId]            UNIQUEIDENTIFIER NOT NULL,
   -- lots of other columns
   CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
ON [dbo].[JobItems]([ItemId] ASC);

which effectively yields two identical indexes. I want to get rid of one of them to reduce insertion time and free up disk space.

The table is stored in a production SQL Azure database, so I cannot rebuild the table and I cannot drop the clustered index (SQL Azure requires a clustered index for every table).

My requirement is I keep all the data integrity guarantees which were provided by the original definition and get rid of the non-clustered index. It looks like having a NOT NULL constraint and a unique index gets me the same guarantees as a PK constraint. So the PK constraint is redundant and I can drop it and the underlying index.

Can I just drop the PK constraint here without expecting anything to break?

which effectively yields two identical indexes.

The indexes are not identical.  The CI includes all of the other columns, while the PK has only the ItemId key column on the leaf.  So the Clustered Index is probably 100x the size of the non-clustered index.

For instance COUNT(*) and paging queries will be significantly more expensive with only the clustered index, as they will have to use table scans.


Mathematically compare multiple 3 rows sets of table and record result in new column I need to isolate 3 row groups in my overall query and perform the same CASE statement on each group and return a result in the final output

I believe i may have to use a CTE (which i have not done before)
but im not sure if this is the right thing to do

I will reuse a graphic from a previous question to illustrate.
The maths is simple, i just am unsure as to how to compare the 3 rows distinctly from the main table.

SQL Server 2012

There are probably more efficient ways to do this, but here's my stab at it.
For each group of 3, I'm basically capturing the value of Result for Row1, Row2 and Row3 - sort of like a poor-man's PIVOT.  I then use a CASE expression to evaluate the differences between Row1, Row2 and Row3 to generate the 'Up' or 'Down' values and concatenate those values for each row

SET NOCOUNT ON
declare @Table table (Col1 Char(1), Col2 Char(2), Col3 Char(2), [Year] int, [Year+n] int, Result int)
insert into @Table
    (Col1, Col2, Col3, [Year], [Year+n], Result)
    values
        ('A', 'UK', '1Z', 2015, 2, 30),
        ('A', 'UK', '1Z', 2015, 1, 20),
        ('A', 'UK', '1Z', 2015, 0, 10),
        ('A', 'UK', '1Y', 2015, 2, 40),
        ('A', 'UK', '1Y', 2015, 1, 35),
        ('A', 'UK', '1Y', 2015, 0, 40)
;
WITH _CTE as
(
SELECT Col1
    ,Col2
    ,Col3
    ,[YEAR]
    ,[Year+n]
    ,Result
    ,CASE
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                Result
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                LAG(Result,1) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )

        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                LAG(Result,2) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
        END AS Row1Result
    ,CASE
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                LEAD(Result,1) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                Result
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                LAG(Result,1) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
                            END AS Row2Result
    ,CASE
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 1 THEN
                LEAD(Result,2) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 2 THEN
                LEAD(Result,1) OVER (PARTITION BY Col1, Col2, Col3
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] )
        WHEN
            ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3
                                ORDER by Col1, Col2, Col3, [Year], [Year+n]) = 3 THEN
                Result
        END AS Row3Result

from @Table
)
SELECT Col1
    ,Col2
    ,col3
    ,[year]
    ,[Year+n]
    ,Result
    ,Row1Result     --For debug/display purposes only - remove from final answer
    ,Row2Result     --For debug/display purposes only - remove from final answer
    ,Row3Result     --For debug/display purposes only - remove from final answer
    ,CASE
        WHEN Row2Result > Row1Result
            THEN 'Up'
        WHEN Row2Result = Row1Result
            THEN 'NoChg'
        ELSE 'Down'
        END + CASE
        WHEN Row3Result > Row2Result
            THEN 'Up'
        WHEN Row3Result = Row2Result
            THEN 'NoChg'
        ELSE 'Down'
        END AS Classification
FROM _CTE
ORDER BY Col1
    ,Col2
    ,col3 DESC

| Col1 | Col2 | col3 | year | Year+n | Result | Row1Result | Row2Result | Row3Result | Classification |
|------|------|------|------|--------|--------|------------|------------|------------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Z   | 2015 | 1      | 20     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Z   | 2015 | 2      | 30     | 10         | 20         | 30         | UpUp           |
| A    | UK   | 1Y   | 2015 | 0      | 40     | 40         | 35         | 40         | DownUp         |
| A    | UK   | 1Y   | 2015 | 1      | 35     | 40         | 35         | 40         | DownUp         |
| A    | UK   | 1Y   | 2015 | 2      | 40     | 40         | 35         | 40         | DownUp         |

After removing the extra 'RowResult' columns that I used to validate my results, I get the results you're looking for.

| Col1 | Col2 | col3 | year | Year+n | Result | Classification |
|------|------|------|------|--------|--------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | UpUp           |
| A    | UK   | 1Z   | 2015 | 1      | 20     | UpUp           |
| A    | UK   | 1Z   | 2015 | 2      | 30     | UpUp           |
| A    | UK   | 1Y   | 2015 | 0      | 40     | DownUp         |
| A    | UK   | 1Y   | 2015 | 1      | 35     | DownUp         |
| A    | UK   | 1Y   | 2015 | 2      | 40     | DownUp         |


When is a Dynamic Port "dynamic"? I was having a discussion today about Dynamic Ports with one of my co-workers and could use some help clarifying how they work.

First question: If the IPALL TCP Dynmaic Ports setting is a specific number (say 1971) does that signify that you have a static port of 1971 or a dynamic port that is currently 1971 and may change at some point in the future.

Second question: This is the one I'm most curious about.  We have an instance that has had the same port (the value in the IPALL TCP Dynmaic Ports setting) for several years through dozens of instance restarts.  What actually causes the dynamic port to actually change after an instance restart?

This KB article : How to configure SQL Server to listen on a specific port will clarify things for you :

Dynamic Port Allocation

If you configure an instance of SQL Server to use dynamic port allocation, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:

  TCPDynamicPorts = Blank

  TCPPort = 0

However, if you configure an instance of SQL Server to use dynamic port allocation, and you restart the instance of SQL Server, the registry values are set as follows:

  TCPDynamicPorts = Current port that is used

  TCPPort = Current port that is used

Static Port Allocation :

If you configure an instance of SQL Server to use a static port, and you have not yet restarted the instance of SQL Server, the registry values are set as follows:

  TCPDynamicPorts = Last port that is used

  TCPPort = New static port to be used after the next restart; new static port that you set by using the Server Network Utility

However, if you configure an instance of SQL Server to use a static port, and you restart the instance of SQL Server, the registry values are set as follows:

  TCPDynamicPorts = Blank

  TCPPort = New static port that you set by using the Server Network Utility

for your second question -

Every time you start the named SQLServer, it uses the port that is allocated. In case if the port is used by another program, then SQL Server chooses another port at the time of restart i.e. The dynamic port is chosen at first startup, and will generally remain the same through future restarts (stored in the Registry) - but if its used by another program then SQL server will choose a new port. note: For Prod Servers, I use only static ports - security and ease of manageability reasons.

Note: More cools thing to find out :

Check if Dynamic port is being used or not using T-SQL :

SELECT NAME
    ,protocol_desc
    ,type_desc
    ,state_desc
    ,is_admin_endpoint
    ,port
    ,is_dynamic_port
    ,ip_address
FROM sys.tcp_endpoints

You can use  netstat -ano to check using cmdline.

Additionally, you can check what got cached earlier in the registry on the client side as to what port it is using to connect to sql server :

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI11.0\LastConnect


Query tables with 400 million records fast Table: Customer Data
Rows: 400 million
Indexes: Non-clustered (as per queries)

Issue: Index Scan happening with most queries, 400 million scan take too long to complete
Need advise on how to address above problem:

Will partitioning the table by YEAR help improve performance or partitioning only helps in data management?
How to convert Index Scan to Seek (majority of reports using different columns, filters and aggregation)
Or simple approach: Keep current year in one table and rest all archived in historical table would be best
Any other feature in SQL Server 2019 which can be used when queries with such big data can return records faster.

Will partitioning the table by YEAR help improve performance or partitioning only helps in data management?

Yes. If your queries have date filters, Partition Elimination will allow queries to ignore some partitions.

How to convert Index Scan to Seek (majority of reports using different columns, filters and aggregation)

You don't really want that.  With a clever combination of indexes, filtered indexes, indexes with included columns, and indexed views it's possible to provide optimized query plans for many different query types.  But it's expensive, time-consuming, and and typically fails to account for 100% of the queries.  It's better to be able to support scanning, which brings us to:

Any other feature in SQL Server 2019 which can be used when queries with such big data can return records faster.

Yes.  Absolutely.  For large tables with a variety of queries that scan and aggregate large numbers of rows, you should use a Columnstore design instead of the default row-oriented Clustered Index or Heap.
To switch to columnar storage, just create a Clustered Columnstore index on the table.  It's designed for exactly this scenario, providing:

The fastest scanning speed.
The highest compression.
Queries can scan only the columns they need.
Data is stored and fetched from disk in larger chunks.
Unused columns are not fetched from disk or cached in RAM.
Can be combined with non-clustered indexes and partitioning if needed.

And once you can scan fast enough, you don't really need a lot of other indexes.


Conditional inner join I need to get allowed itemTypes for a user based on 2 different tables in a conditional way. If the users userGroup has any entries in the userGroupItemTypes table it should only show items that are in common between userGroupItemTypes AND clientItemTypes but if no entries exist in userGroupItemTypes for that users userGroup, then it should just get all the ones from clientItemTypes

itemTypes

itID    itemType
1       Item 1
2       Item 2
3       Item 3

clients

cID     client
1       Client 1

userGroups

ugID    userGroup
1       Group 1
2       Group 2

users

uID     user     cID     ugID
1       Fred     1       1
2       Sam      1       2

clientItemTypes

cID     itID
1       1
1       2
1       3

userGroupItemTypes

ugID    itID
2       1
2       2

Resaults should be:

uID     itID     user     itemType
1       1        Fred     Item 1
1       2        Fred     Item 2
2       1        Sam      Item 1
2       2        Sam      Item 2
2       3        Sam      Item 3

Basically i think the query should function like follows:

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
IF((SELECT COUNT(*) FROM userGroupItemTypes GROUP BY uID) > 0, INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID, DO NOTHING)

But I do not know how to actually achieve this. Any help would be greatly appreciated

EDIT:
Here is a dbfiddle

Based on Lennarts suggestion I have used a UNION to get the results I need:

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID
INNER JOIN itemTypes it ON it.itID = ugit.itID
INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID
WHERE c.total > 0
GROUP BY uID, itID
UNION
SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID
WHERE c.total = 0
GROUP BY uID, itID

dbfiddle

Basically running the same queries twice, one requiring a userGroup to have itemTypes, the other requiring the userGroup NOT to have itemTypes, then using the union to combine the results.

I'm not sure if this is the most efficient way of doing it but it will have to do unless I can find another way of doing it.

Alternative Solution (thanks to Lennart):

Using EXISTS and NOT EXISTS

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID
INNER JOIN itemTypes it ON it.itID = ugit.itID
WHERE EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID
UNION
SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
WHERE NOT EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID

dbfiddle


Deadlock on insert for a table with an insert trigger that updates column in same triggered table By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the customer that owns the resource (related via Resource -> Location -> Customer).

I have tried to set this 6 number using an after insert trigger. The logic works fine but causes deadlocks when I simulate many inserts coming in. I can eliminate this deadlock by adding a tablockx hint to the insert. However since Linq2SQL will be used to insert data I would like to avoid having to add a locking hint to the insert. I was hoping some combination of locking hints or index hints in the trigger might work instead.

The schema:

CREATE TABLE [Resource] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    [nIdentifier]           INT NOT NULL DEFAULT 0,
    [fkLocation]            UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Resource] PRIMARY KEY CLUSTERED ([pkGUID] ASC)
)
CREATE TABLE [Location] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    [fkCustomer]            UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED ([pkGUID]
)
CREATE TABLE [Customer] (
    [pkGUID]                UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([pkGUID]
)

Currently there are no indexes on the Resource table except the clustered primary key.

The trigger:

CREATE TRIGGER [dbo].[Trigger_Resource_Insert]
ON [dbo].[Resource]
FOR INSERT
AS
BEGIN
    SET NoCount ON

    ;with relevantCustomers as (
        select c.pkGUID
        from inserted i
        join Location l on i.fkLocation = l.pkGUID
        join Customer c on l.fkCustomer = c.pkGUID
        group by c.pkGUID
    ), maxNumbers as (
        select rc.pkGUID, max = case when max(x.nIdentifier) < 100000 then 100000 else max(x.nIdentifier) end
        from relevantCustomers rc
        join Location l on l.fkCustomer = rc.pkGUID
        join Resource x on x.fkLocation = l.pkGUID
        group by rc.pkGUID
    ), numbers as (
        select i.pkGUID, number = row_number() over (partition by n.pkGUID order by i.pkGUID) + n.max
        from inserted i
        join Location l on i.fkLocation = l.pkGUID
        join maxNumbers n on n.pkGUID = l.fkCustomer
    )
    update x
    set nIdentifier = n.number
    from Resource x
    join numbers n on x.pkGUID = n.pkGUID
END

An insert generating a bunch of data:

;with x as (
    select num = 1
    union all
    select x.num + 1
    from x
    where x.num < {0}
), l as (
    select top {1} fkLocation = pkGUID, sLocationName from Location order by sLocationName
)
insert into Resource with (tablockx) (pkGUID, fkLocation, ...)
select newid(), l.fkLocation, ...
from x
cross join l

Deadlock graph from SQL profiler when running inserts without tablockx hint:

I have to say that I don't quite agree with some of the comments that state that what you're seeing is necessarily a framework problem.  The Linq to SQL framework most certainly does allow you to specify a stored procedure as the means of input for rows.

That being said, I'd highly recommend you use that mechanism instead of a trigger if at all possible.  You'll be able to add your locking hints inside the stored procedure and your inserts will work fine.

Your spec, however seems highly suspicious to me, and IMO the problems you seem to be running into right now are good counterexamples as to why one might not want to pursue a design such as this.  So my (purely rhetorical) question is for the designer.  What is that problematic sequential number supposed to represent?  If it's a sequence in time, then why not just use a time and use the window functions to order the rows in something like a view instead (you can point Linq to SQL to views as well)?  Is the intent to have some sort of gapless sequential number?  Are you signed on to all of the complexity and locking you're going to have to deal with just to maintain that number?

Unfortunately, I think there is no real answer -- no magic combination of hints which will work across your frameworks and design -- which will solve your problem.  IMO my answer is take a step back and look critically at your design.


Why can't the transaction log shrink during a backup? I have a large database in Simple recovery model (vendor requirement) and it took 3 days to back it up to the new hosting company's server.

The morning of the third day, the DB went offline because the transaction log had filled up its drive.

My understanding was that SQL Server would just start recycling the space. What's more interesting is that I don't think the database started working again until I gave it a bit more space - it stayed busted after the backup was complete. I didn't take the time to properly debug, though, so I don't know exactly why it was stuck.

Question: Why can't SQL Server in Simple Recovery re-cycle its transaction log while it is backing up?

The transaction log is used during the backup process to store all the changes that were committed while the backup was being taken.  These change are applied to the backup file so that the backup is consistent as of the time it finished (only committed transactions).

But three days for a full?  I backup a Terabyte database every day and it only takes 90 minutes (compression, multiple files, SQL native).  Given your version, you should look into backing up to multiple files at the same time (or speeding up your backup storage).

Answer: the transaction log is in use by the backup process.

Because you were sending this to the vendor, I suspect that you sent it directly from your local SQL Server to the Vendor on a fileshare (especially since you say it only takes 90 minutes to backup locally).

For this scenario, I would recommend that you backup locally first (and if you were in FULL/BULK recovery mode, maybe with the COPY_ONLY flag) and then send it to the vendor.  You won't tie up your SQL Server with this backup and you gain the opportunity to compress the backup using 7zip or similar (which should easily cut it down by 50%).


MySQL Workbench doesn't automatically refresh the tables content I have a database running on localhost and the content of this DB is updated by an app (using Hibernate, for the record). In this order:

In Workbench I run a SELECT * to check to DB content
I add a row in a table through the app
I run the same SELECT in Workbench

The result is the same as the first query.
Actually if I hit the button "Commit the current transaction", running the request again will show the new row.
Why is it necessary to commit the Workbench session to see results committed by an external app ?

Is there a way to auto-refresh the table rows in Workbench?

Thanks for the help.

This has nothing to do with MySQL Workbench. If you have an open transaction then no other client than that which owns that transaction can see changes made until everything is committed. I recommend to read a bit about database transactions.


Loyalty reward - record for every day of a week I have a table that contains every login a user makes. I am trying to make a loyalty system that rewards users if they've logged in every day for a week.

So, the real question, how do I get every record in a set week by a user (user_id) and know that there was a login on each day? Would MySQL be able to do this or would I have to do a query for every user and then further processing on top of that?

The only thing I can think of doing is:

SELECT * FROM `users` WHERE `users`.`time_they_logged_in` = IN WEEK X

<?php

$result = $pdo->prepare('SELECT....');
$result->bindParam(1, etc etc);

$logged_in_on = array(0, 0, 0, 0, 0, 0, 0);

foreach($result->fetchAll() as $login_record) {
    if(LOGGED IN DAY = MONDAY -> $logged_in[0] = true)
}

Idea: record for each week, with a column for each day and a total - then it's a matter of updating it on viewing a page / login and then looking for items with a total of 7.

Maybe.

SELECT count(distinct date(time_they_logged_in))
FROM `users`
WHERE `users`.`time_they_logged_in` between now()-interval 7 day and now();

If the count is 7 they logged in at least once per day in the past week.


What can I do to monitor "out of shared memory" issues? I'm new to database administration and I would really like some advice on what tools I could use or what I could do to monitor "out of shared memory" issues.

I've been seeing these "out of shared memory" messages when I've been running rspec which runs tests for my application. I've also seen messages in the browser when I run my rails app  that I "might need to increase max_locks_per_transaction."

I think it would be better to find out exactly what is going on first either on the driver level or the PostgreSQL level.

My specs are:

PostgreSQL 9.4.5 on x86_64-apple-darwin15.0.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit

I have a Macbook Pro running OSX El Capitan 10.11.5

I'm using the gem pg version 0.18.4 in my Ruby-on-Rails application.

To monitor postgres logs you can navigate to cd "/usr/local/var/postgres" where you will find the server.log file.

If you want to watch the log in action you can pass the following command into the terminal tail -f server.log

Run your application in the background and if there is an issue occurring in postgres the server.log will catch it.

In my case, I ran RSpec in the background as that's where I saw the original "out of shared memory" message occur. The postgres log picked it up when it happen.


SQL Server Sch-S locks on unrelated tables I am a bit confused. I myself am MCSE and thought I would understand most parts of the SQL Server.

Now I have a stored procedure here, doing inserts and updates on two tables dbo.tabA and dbo.tabB. Let's call this SPID 1.

On a complete different table sales.invoices I am recreating a clustered index on a partition scheme. Let's call this SPID 2.

To my surprise SPID 1 is blocked by SPID 2.

Looking into sys.dm_tran_locks, it tells me that SPID 1 is waiting for a Sch-S lock on sales.invoices (OBJECT LOCK) but  sales.invoices is neither mentioned directly in the SPID 2 query nor is it part of any view or function.

Why is SPID 1 trying to achieve a lock on an object that is totally untouched by its query?

Potential Causes

There are two likely reasons for this

There's a foreign key on the table
There's a trigger on the table

There are less likely reasons, too

Computed column based on a scalar function
Indexed view maintenance

There's also the possibility that the blocking is due to metadata changing, where modifications to system views can block each other.

Figuring It Out

You can use sp_WhoIsActive.

If you run it like so: sp_WhoIsActive @get_locks = 1;, it should help you get to the root of your blocking issue.


Can I log psql time and statements to a text file (without the data)? I am currently comparing performance of PostgreSQL with several other SQL systems. I am aware of the \timing option to turn on timing queries. However, I would very much like to automate the process of copying the statements executed and the query speed below it. I imagine there is a simple way to log this?

Let's say I run:

CREATE TABLE t1 AS
SELECT itemID, prodCategory
FROM products
WHERE prodCategory = "footwear"

I want to automatically save into a text file:

CREATE TABLE t1 AS
SELECT itemID, prodCategory
FROM products
WHERE prodCategory = "footwear"

SELECT 7790
Time: 10.884 ms

If OS Specifications are needed, I am using MacOS.

No, it's not possible currently..

Look at PrintTiming, it is using printf. While the stuff that prints to the log file, is using fprintf. My assumption is that the patch that provides this would be a couple of lines.
You would have to have the ability to suppress the outputting of the result on --log-file, which we don't currently have. And in your case you want the summary logged SELECT 7790 but not the actual data.

.. maybe in the future.


MySQL upgrade on Ubuntu - any heads ups? I am needing to upgrade MySQL on Ubuntu, it is a production server and naturally cautious.

My many googles look to be essentially saying that I need to :

Backup my current mysql database and tables/data
Uninstall current mysql
Install new MYSQL 5+
Restore Databases/ tables and data
Hope and Pray I got it right ??

Something doesn't seem right, sounds like a lot of down time and risk

Am I missing something, or any simple solutions?

Upgrading from MySQL 4 to 5 on Ubuntu 10

Many Thanks,

Rob

The upgrade process depends on how you installed your mysql distribution.

If you installed the ubuntu mysql distribution you don't need to upgrade that way (but always create the backup copy of all your dbs!). You need to upgrade that way only if you installed the mysql.com mysql distribution (ex. tar.gz).

If you installed mysql packages distributed by ubuntu (you can check it running dpkg -l | grep -i mysql-server) you may just need to use the ubuntu package manager to upgrade mysql.


Output a table as well-formed, indented JSON I have a table dictionary and its structure is:

create table dictionary
(
  id integer,
  first_level_key character varying(50),
  key_name character varying(50),
  key_value_en character varying(1000),
  key_value_fr character varying(1000)
);

I want to output this table as well-formed indented JSON.

I have tried the following query:

COPY (select
   (select row_to_json(_) from (select c.first_level_key) as _) as first_last,
   c.key_name,c.key_name_value_en
from
   dictionary as c)
 TO '/tmp/label_en.json';

I got JSON output, but not in the correct format, as shown below.

{"first_level_key":"topheader"} screen  Screen Reader Access
{"first_level_key":"header"}    title
{"first_level_key":"header"}    subtitle
{"first_level_key":"nav"}   about   About

The output that I want is well-formed, indented JSON as shown below!

{
"topheader":{

        "screen":"Screen Reader Access"
        },
        "header":{
        "title":"title",
        "subtitle":"subtitletest"
        },
    "nav":{
        "about":"About"
}}

While rereading the documentation, I noticed that whereas the pretty_bool option for JSON doesn't have an indented option, there is a jsonb_pretty(from_json jsonb) function which does produce indented text (Returns from_json as indented JSON text.) which looked as if it is what you are looking for.

When you have a "normal" table (I used your DDL in the question as my template), what you have to do is first convert it to JSON using the ROW_TO_JSON function (documentation), but as discussed here and elsewhere, this only converts the table to JSON on a row-by-row basis. The JSONB_AGG function (docs here) aggregates values, including nulls, as a JSON array).

The final piece of the puzzle is the JSONB_PRETTY (discussed above) function which takes the JSON produced and outputs indented JSON text using your original query as the inner loop of the query.

Fiddle available here (and all DDL and DML is at the bottom of this answer).

SELECT JSONB_PRETTY(JSONB_AGG(tab)) AS i_json FROM
(
  SELECT
  (
    SELECT ROW_TO_JSON(x)
    FROM
    (
      SELECT c.first_level_key) AS x
    ) AS first_last,
    c.key_name, c.key_value_en
  FROM
  dictionary AS c
) AS tab;

Sample output (entire table at end of this answer):

[
    {
        "key_name": "ttttttt",
        "first_last": {
            "first_level_key": "afasaffasa"
        },
        "key_value_en": "zxccc"
    },

I believe that this is (finally!) the correct answer!

When asking a question, please always provide your version - it can be very important for providing an answer.

DDL and DML (also on the fiddle):

Create table. Note that I used the TEXT datatype instead of VARCHAR(n) for reasons discussed here and here.

CREATE TABLE dictionary
(
  id INTEGER,
  first_level_key TEXT,
  key_name TEXT,
  key_value_en TEXT,
  key_value_fr TEXT
);

Populate it:

INSERT INTO dictionary
VALUES
(4, 'afasaffasa', 'ttttttt', 'zxccc', 'zzzzzzz'),
(4, 'afasawweww', 'rrrrrrr', 'zxccc', 'zzzzzzz'),
(4, 'afayyyyyyy', 'eeeeeee', 'zxccc', 'zzzzzzz'),
(4, 'xxxxxxxxxx', 'wwwwwww', 'zxccc', 'zzzzzzz'),
(4, 'mmmmmmmmmm', 'qqqqqqq', 'zxccc', 'zzzzzzz');

Run query:

SELECT JSONB_PRETTY(JSONB_AGG(tab)) AS i_json FROM
(
  SELECT
  (
    SELECT ROW_TO_JSON(x)
    FROM
    (
      SELECT c.first_level_key) AS x
    ) AS first_last,
    c.key_name, c.key_value_en
  FROM
  dictionary AS c
) AS tab;

Result:

i_json
[
    {
        "key_name": "ttttttt",
        "first_last": {
            "first_level_key": "afasaffasa"
        },
        "key_value_en": "zxccc"
    },
    {
        "key_name": "rrrrrrr",
        "first_last": {
            "first_level_key": "afasawweww"
        },
        "key_value_en": "zxccc"
    },
    {
        "key_name": "eeeeeee",
        "first_last": {
            "first_level_key": "afayyyyyyy"
        },
        "key_value_en": "zxccc"
    },
    {
        "key_name": "wwwwwww",
        "first_last": {
            "first_level_key": "xxxxxxxxxx"
        },
        "key_value_en": "zxccc"
    },
    {
        "key_name": "qqqqqqq",
        "first_last": {
            "first_level_key": "mmmmmmmmmm"
        },
        "key_value_en": "zxccc"
    }
]


What sequence when changing the master MySQL server, but not IP? I change the replica to the master server. But the IP master remains the same.

* The second replica itself will continue to work, without changing the settings?
* On the old master, just change the settings and make
    * RESET SLAVE;
    * CHANGE MASTER TO ...
    * START SLAVE;

The second replica works itself. And old master started as replica.
It is normal? I did the right thing?

I'm interpreting your scenario as follows:

You start with one master database and two replicas; both replicas are replicating from the master.
During the promotion, you want to set the original master to replicate from the promoted replica.
The second replica you leave alone to continue replicating from the original master.

The command RESET SLAVE on a host that is currently a master would have no affect since this command works on replicas.

The Change master statement on the master needs to be run using the log coordinates of the promoted replica at the point in time of the promotion. The best way to ensure this, is to set read_only on the original master, validate replication has caught up, check the log coordinates (show master status on the host you are promoting), then promote.

In order for the second replica to continue replicating from the original master you need to have log-slave-updates enabled on the original master, otherwise it won't receive anything written by the new master. You can quickly test this by creating a new dummy db or table on the new master and validate if it replicates down to the extra replica.

If I were setting up for this

Prerequisites

All hosts should be running with log-bin, log-bin-index, log-slave-updates, relay-log, and relay-log-index. Replicas should be running with read_only set to true. Application users and standard database users should not have the SUPER privilege (e.g. ALL PRIVILEGES on *.*).

Promotion

Apply a maintenance page and stop background processing if applicable.
On the existing master mysql -e 'set global read_only=1'; select sleep(1); show master status;
On the existing master, set read_only = 1 in your mysql configuration file in case the host is restarted.
On the replica to be promoted mysql -e 'show slave status\G'| egrep 'Exec|Relay_Master_Log_File'
Compare the coordinates of the master to the replica coordinates to ensure they match. Until they do, your replica is not in sync, wait or investigate as needed.
On the replica being promoted mysql -e 'show master status; reset slave all; set global read_only=0'
Update the replica being promoted configuration file to read_only = 0.
On the original master run the start slave command with the coordinates from step 6, and the start slave command.
Redirect your application and background processing to write against the new master's IP address, and restart your services.

Hope this helps.


Whether Cold backup method can be used to "migrate oracle DB"? I have to migrate one database.

I have exact server replica. Same directory structure on target as well as source database.

Previously I have performed DB migration using export/import datapump as we don't have access to any tools.
I want to ask whether I can create replica using cold backup method?

DB Details:

Oracle 10.2.0.3 (On Both Source & target)
DB is currently running in no archive mode.

Steps I am going to follow:

create pfile from spfile
shutdown normal on source db
Copy pfile to target server $ORACLE_HOME/dbs
Copy all the dbf , ctl , rdo files to target server (Using binary mode FTP) (select name from v$datafile; select member from v$logfile; select name from v$controlfile)
startup pfile='<path>' on target server

Doubts:

Can cold backup can be used to create replica/ DB migration?
If I tried above process, shall i am able to start the db on target server without any errors?
As I don't have any archive logs, will that create any problem here?

Can cold backup can be used to create replica/ DB migration?

Yes of course

  If i tried above process , shall i am able to start the db on target
  server without any errors ?

If you had cloned the machine or installed Oracle binaries in destination machine, only you need another steps:

Copy passwords file for your database, file $ORACLE_HOME/dbs/orapwSID
Copy line of /etc/oratab describing the database you are cloning into
destination machine.

  As i don't have any archive logs, will that create any problem here?

Yes its a cold backup, you don't need archivelog for nothing.


Alternate to use LIMIT in subquery in mysql? I have 2 tables, Resource and views and I'm trying to get the list of all the items from Resource table whose ran_resId don't match with the ran_resId of last 2 records of views table. I'm trying to use LIMIT in my sub-query which is giving this error:

  This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'`

Here's my query -

select r.res_id, r.res_name
from Resource AS r
where r.ran_resId NOT IN
      ( select v.ran_resId
        from views AS v
        order by v.viewsid
        limit 2
      )
group by r.ran_resId

I've google it and found out that LIMIT can't be use in SubQuery in MySql, And as an alternate I could use JOIN however I'm not able to create the query to get the desired results.
A help is highly appreciated.
PS - I'm using MySql 5.6

If 2 is a fixed value and you'll never need to compare against 3, 4 or more items in the future, then you could convert the subquery to a derived table and find the 2 values with aggregation:

select r.res_id, r.res_name
from Resource as r
     join
     ( select min(lim.ran_resid) as ran_resid_1,
              max(lim.ran_resid) as ran_resid_2
       from
         ( select v.ran_resid
           from views as v
           order by v.viewsid
           limit 2
         ) as lim
     ) as x
     on r.ran_resid NOT IN (x.ran_resid_1, x.ran_resid_2)
 ;

A more general solution (where you can use any LIMIT and not just 2), is to write the antijoin with the LEFT JOIN / IS NULL method:

select r.res_id, r.res_name
from Resource as r
     left join
     ( select v.ran_resid
       from views as v
       order by v.viewsid
       limit 5
     ) as x
     on r.ran_resid = x.ran_resid
where x.ran_resid is null
 ;

An NOT EXISTS version should work as well but I'm not sure that it would be very efficient, due to the double nesting:

select r.res_id, r.res_name
from Resource as r
where not exists
  ( select 1
    from
      ( select v.ran_resid
        from views as v
        order by v.viewsid
        limit 5
      ) as x
    where r.ran_resid = x.ran_resid
  ) ;

Also note that I removed the last group by r.ran_resId from your query.

It makes no sense to group by a and select b, c in a query. And while MySQL allows it, it is a guaranteed way to get garbage results.


Battery backed write cache and Postgres I will be getting a SAN with a battery backed write cache for a new Postgres data warehouse and am not sure what settings to apply to make proper use of it. If the cache is enabled in the SAN is that enough or do I need to set wal_sync_method, synchronous_commit or others?

The warehouse will be handling 5-10 million row inserts a day once the current data sources are turned on. This will most likely grow in the future.

We will be using Postrges 9.3 and most likely Centos 6.4.

Thanks in advance.

Literature

I gathered you some links you should read:

The manual on reliability
The wiki pretty much sums up what's written in the manual, but maybe it helps you too
You can read a more detailed list of accessible sync methods on this page
Here are some more or less practical tips in what to choose

Possible Workflow

Basically your workflow might be the following, which is extracted as a combination of all tips of the above links:

Turn on postgres setting fsync if it is not.
Set synchronous_commit to "on" (or "local") if it is set to "off".
Run diskchecker.pl just for sanity to see if you bought something good and it is configured correctly.
See what sync method postgres selected automatically with show wal_sync_method;
Run pg_test_fsync
and use it's output to

check if you are affected by FLUSH CACHE EXT/SYNCHRONIZE CACHE problem.
decide on the basis of the additional information provided here (in paragraph wal_sync_method) and here (in paragraph test_fsync and below) what value to set wal_sync_method to.

Check if your BBU and file-system prohibit partial writes of 8kb pages, if yes you may disable full_page_writes (see last paragraph here).

Remark (why this might not be enough)

Note that this seems very reliable, but quoted from one of the links above:

  Critics of the battery backup approach suggest that if you run such a system under load, eventually you’ll have a failure in this relatively complicated cache method that will corrupt your database in a way that’s nearly impossible to detect or recover from.

So even having a BBU and a correctly configured server instance, this does not remove the burden to backup everything regularly, and does not remove the possibility to loose some data.

Legal Note

I am not responsible for any lost data ;) neither due to lack of advice here or a wrong tip, nor through anything else.


creating a report on the mysql jasperReport server I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf report or any report without using any tool except jasperReport server. Can I do ? HOw?

Note; report will be directly constructed on the whole data reside in the mysql database. (No filtering) I want to see the report on the jasperReport report page. If any change occur on the mysql database, it should be carried out on the report page of the JAsperReport server

You must first create a report using iReport (the report designer) i don't remember if it comes included in the Jasper Server, but if it doesn't you can download it from Jasper's web site; after that using the same iReport you must deploy the report to your Jasper Server. If you need more guidance on deploying a report you can look here: http://randomallsorts.blogspot.mx/2009/12/jasper-reports-how-to-deploy-report-to.html


Adding a sequence number to existing rows I have a table with an OrderSequence column. The schema looks roughly like this:

id
Name
OrderSequence

I have an array of ids ["1", "2", "3", "4"] from an application, and I want to set the OrderSequence for sorting purposes. I don't want to loop through the rows from the application, executing 'n' SQL queries one at a time. How can I do this using a stored procedure?

Sample data

id Name OrderSequence
1  A    0
2  B    0
3  C    0

Desired result

id Name OrderSequence
1  A    1
2  B    2
3  C    3

You can accomplish this using ROW_NUMBER() syntax, as mentioned by @Kin :

USE Tempdb;
CREATE TABLE dbo.testRowNum
(
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Name VARCHAR(255)
    , SequenceNum INT NULL
);
INSERT INTO testRowNum (Name) VALUES ('A'), ('B'), ('C');

SELECT *
FROM dbo.testRowNum;

UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum
    INNER JOIN (SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM dbo.testRowNum) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;

SELECT *
FROM dbo.testRowNum;

Output:

If you want only update certain rows, you can use a Table Variable (or a #temp table if you are updating a LOT of rows), as such:

DECLARE @IDsToUpdate TABLE
(
    ID INT
);

INSERT INTO @IDsToUpdate VALUES (1),(3);

UPDATE dbo.testRowNum
SET SequenceNum = x.RowNum
FROM dbo.testRowNum
    INNER JOIN (
        SELECT t.ID
            , ROW_NUMBER() OVER (ORDER BY t.ID) AS RowNum
        FROM dbo.testRowNum t
            INNER JOIN @IDsToUpdate i ON t.ID = i.ID
        ) x ON testRowNum.ID = x.ID
WHERE SequenceNum IS NULL;

The example above results in the following output:

An improvement (based on a suggestion by Daniel Hutmacher) avoids the join and results in a more efficient query plan (no join or Halloween protection):

WITH Numbered AS
(
    SELECT
        SequenceNum,
        RowNum =
            ROW_NUMBER() OVER (
                ORDER BY ID)
    FROM dbo.testRowNum
)
UPDATE Numbered
SET SequenceNum = RowNum;

Or, when using the @IDsToUpdate table:

WITH Numbered AS
(
    SELECT
        SequenceNum,
        RowNum =
            ROW_NUMBER() OVER (
                ORDER BY ID)
    FROM dbo.testRowNum
    WHERE EXISTS
    (
        SELECT *
        FROM @IDsToUpdate AS I
        WHERE I.ID = dbo.testRowNum.ID
    )
)
UPDATE Numbered
SET SequenceNum = RowNum;


Get data from previous years dynamically I have a table with columns id, fname, date_field. date_field's records are:

2011-03-01
2012-03-01
2013-03-01
2014-03-01
2015-03-01
2016-03-01

I want the data for the past 2 years for each record from that date which the table has.

For example:

2012 year having only one data before that is 2011
2013 year data having two data before 2 year data which are 2012 and 2011
2014 year data having two data before 2 year data which are 2013 and 2012
2015 year data having two data before 2 year data which are 2014 and 2013
2016 year data having two data before 2 year data which are 2015 and 2014

Note: Need to work dynamically for whatever data is in table, not by entering the date manually.

I want the query for MySQL.

You can do that with a simple join and intervals. Assuming the table with your data is named mydata and the table you mention in your question with a list of dates named mydates, you could issue the following query:

SELECT d.id, d.fname, d.date_field, dat.*
FROM mydates d
JOIN mydata dat
    ON (dat.date_field >= (d.date_field - interval 2 years)
    AND dat.date_field < d.date_field)
ORDER BY 3, 1;


How to reinitialise /var/lib/mysql files? Due to a mishap I deleted the entire /var/lib/mysql directory. Since the database did not contain anything important, I do not want to go through the hassle of restoring it from an old backup, but instead create the directory structure from scratch. How do I do it without reinstalling MySQL?

You should be able to just run mysql_install_db from the command line

mysql_install_db initializes the MySQL data directory and creates the system tables that it contains, if they do not exist. It also initializes the system tablespace and related data structures needed to manage InnoDB tables. As of MySQL 5.6.8, mysql_install_db is a Perl script and can be used on any system with Perl installed. Before 5.6.8, it is a shell script and is available only on Unix platforms.

Please read the MySQL Documentation in full for mysql_install_db, then ...
GIVE IT A TRY !!!
If it does not work on the first try, do this
mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql

then run mysql_install_db again


Is findOne() in mongoDB deprecated Is findOne() in MongoDB deprecated? I am asking this because when I use findOne() in WebStorm the IDE puts a line through findOne() and gives a message saying findOne() is deprecated and to find substitute.

I looked in the MongoDB documentation to try and find an answer but it did not mention anything about findOne() being deprecated.

If findOne() is deprecated what would be the best alternative to this method?

If it is not deprecated does anyone know why WebStorm gives this warning?

Any help with this is appreciated! Thanks!

Just got an answer from the maintainer of the nodejs driver, deprecation has been removed!


Snapshot Isolation vs Read Committed - OLTP and Reporting Databases I just finished reading an excellent article on isolation levels here. Our company will soon start development on a rewrite and expansion of our current product. My desire is to have an OLTP database and a separate, more denormalized, reporting database. Assuming we're somewhat disciplined and most of our ad-hoc and reporting type queries actually go to the reporting database, does it sound appropriate that our OLTP database have a default isolation level of Read Committed (we won't need a more stringent isolation level for OLTP) and our reporting database be Snapshot Isolation (probably RCSI)?

My thinking is that if our OLTP database is actually a true OLTP database and not serving double-duty as a reporting DB, we won't need snapshot isolation, and the associated overhead it entails. But snapshot isolation would be desirable on the reporting database so that readers are not blocked by the constant flow of data coming in, and reading the last saved version of a row would be acceptable.

Just to add to the other answer.

SQL Server supports two different flavors of READ COMMITTED, legacy locking READ COMMITTED and READ COMMITTED SNAPSHOT.  If you've ever built and supported a high-volume OLTP application on locking READ COMMITTED you know why RCSI was introduced (in addition to making it easy to port Oracle applications to SQL Server).

Locking READ COMMITTED is tricky to get concurrency and is prone to deadlocking, as readers block writers and writers block readers.  Deadlocks are a kind of bug in your application, but they're famously hard to find in testing.  So you have a chance to make a choice that reduces the number of hard-to-find-in-testing bugs.  That's worth a lot.  RCSI also increases the concurrency of your application, enabling you to scale up to use multiple cores more easily.

So, RCSI increases the scalability and reliability of your application, at the cost of a little extra bookkeeping for UPDATES and DELETES (and INSERTS if you have a trigger), and an extra 14 bytes per row.

RCSI is simpler to program to over all, the main thing is that you sometimes need to opt in to a locking read using the UPDLOCK table hint when you want to read a row and immediately update it and need to ensure that no other session does the same thing concurrently.


What could explain a 30 second MySQL SELECT query latency when profiling records an execution time of <1 second? I'm trying to figure out why a simple select with a LIMIT 1 clause (admittedly, on a really bloated table with a lot of rows and indices) is sometimes taking 30+ seconds (even 2 minutes, sometimes) to execute on an AWS RDS Aurora instance. This is on a writer instance.
It seems to occur for the first query from a client, only on a particular select that looks through hundreds of thousands of rows, and only sometimes.
The query is in the form:
SELECT some_table.col1, some_table.col2, some_table.col3, some_table.col4,
  MAX(some_table.col2) AS SomeValue
FROM some_table
WHERE some_table.col3=123456 LIMIT 1;

And 'explain' outputs:
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref   | rows   | Extra |
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+
|  1 | SIMPLE      | some_table    | ref  | col1          | col1    | 4       | const | 268202 | NULL  |
+----+-------------+---------------+------+---------------+---------+---------+-------+--------+-------+

I managed to reproduce the issue and captured the profile for the query in PhpMyAdmin. PhpMyAdmin recorded the query as taking 30.1 seconds to execute, but the profiler shows that execution itself takes less than a second:

So it looks like the execution itself isn't taking a lot of time; what could be causing this latency issue? I also found the same query recorded in RDS Performance Insights:

This seems to occur for the first query in a series of identical or similar queries. Could it be a caching issue? I've tried running RESET QUERY CACHE; in an attempt to reproduce the latency but with no success. Happy to provide more information about the infrastructure if that would help.
More info
SHOW VARIABLES LIKE 'query_cache%';

SHOW GLOBAL STATUS LIKE 'Qc%';

Rows examined and sent (from Performance Insights):

SHOW CREATE TABLE output:
CREATE TABLE `some_table` (
`col1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`col2` int(10) unsigned NOT NULL DEFAULT '0',
`col3` int(10) unsigned NOT NULL DEFAULT '0',
`col4` int(10) unsigned NOT NULL DEFAULT '0',
`col5` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`col6` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`col7` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`col1`),
KEY `col2` (`col2`),
KEY `col3` (`col3`),
KEY `col4` (`col4`),
KEY `col6` (`col6`),
KEY `col7` (`col7`)
) ENGINE=InnoDB AUTO_INCREMENT=123456789 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Consider creating this composite index,
CREATE INDEX some_table_indx_col3_col2 ON some_table(col3,col2);

You should remove the col3 index to avoid index redundancy and conserve storage space.
Considering these existing details,
Query_cache_size        2,556,593,152
less Qcache_free_memory 2,555,753,592
                       ----------------
Query_cache bytes in use      839,560
Qcache_queries_in_cache     /     288
You have avg bytes per query    2,915 in use

Consider query_cache_limit of 192K  for ~ 64 * avg bytes per query
query_cache_min_res_unit          512  the minimum to store more queries in your cache
query_cache_size   50M   for ~ 64 * Query_cache bytes in use - from ~2.5G size.

To conserve 98% of CPU cycles used each time qcache_lowmem_prunes are required.
Stalls can be observed if innodb tables are deleted that have involvement with flushing from innodb_buffer_pool change activity before the requested table delete can be completed.
Please let us know of your progress/frustration and consider awarding the bounty.
Happy New Year


why mysql binlog creating new log file daily I have started binary logging on MySQL server on my Ubuntu. It start creating log files in /var/log/mysql. My my.cnf configuration is as follows:
log-bin="/var/log/mysql/"
expire_logs_days=10
max_binlog_size=50M
binlog-ignore-db="phpmyadmin"

Problem:
When I check logs folder a new file appears daily. Which is created at around 6:45AM to 7:00AM IST i.e 1:30AM to 1:45 GMT. But According to official documentation of MySQL on Binary Logging says:

The server creates a new file in the series each time it starts or
flushes the logs. The server also creates a new binary log file
automatically after the current log's size reaches max_binlog_size.

Why new log file is created daily as:

My system is not rebooted from last 10days which I checked with uptime command in terminal.
The max_binlog_size is given 50M in configuration and my logs reached only 2-3M.
I have not used FLUSH logs.
I have no CRON which do some task auto magically.

I have googled this query but didn't find the reason.
Can anyone help me to know the reason. Thanks in advance

4. I have no CRON which do some task auto magically

I'll speculate that you actually do.  Linux systems often use the logrotate utility to manage rotation of log files from numerous disparate subsystems.

Distro builders love to coerce software installed from packages to Do Things The One True Way™ so it should not surprise you if they have "extended" MySQL server beyond the official documentation, to rotate its own binlog.

You may find syslog entries mentioning "logrotate" around the time that the binlog changes, and further explanation is likely to be found in a file somewhere around here:

/etc/logrotate.d/mysql-server


setup transaction replication in cross domain I'm trying to setup transaction replication between two domains under the same active directory.  The publisher server is in a DR site domain and the remote distribution server is in the main site domain with the subscriber. Here are the machine names

Publisher Server : publisher1.drsite.mycompany.com
Distribution Server : distributor1.mainsite.mycompany.com
Subscriber Server : subscriber1.mainsite.mycompany.com

When I try to add publisher1.drsite.mycompany.com to the remote distributor, it will not accept the FQDN and gives me the error below under Replication.Utilities

SQL Server replication requires the actual server name to make a connection to the server.  Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, "publisher1"

Does anyone experience the same problem and know how to solve it?

You need to add a CNAME in your DNS at  your primary SERVER putting the DR server's hostname.  This way publisher.mainsite.mycompany.com can be resolved.  Then use publisher as the hostname to use when connecting.


Inner Join and Right Join same query I'm using MySQL and InnoDB,
I have a table reserver with 2 columns : idReservation and idChambre
I have a table reservation and a table chambre

Now I want my query to return every "chambre.id" who is not between my dateDebut and dateFin.

Below is my actual query :

SELECT cre.id
FROM   reserver AS rer
       INNER JOIN reservation AS ron
         ON rer.idReservation = ron.id
       RIGHT JOIN chambre AS cre
         ON rer.idChambre = cre.id
WHERE (ron.dateFin < ?
    OR ron.dateDebut > ?
    OR (ron.dateFin = NULL AND ron.dateDebut = NULL))

My problem is that my return value only contains chambre when the id of the chambre is already in reserver.

My second problem is that "RIGHT JOIN" add every chambre to my query, but what I want is only chambre as : "ron.dateFin < ? OR ron.dateDebut > ?" or chambre who are not already on linked to reserver.

EDIT : here's some sample rows :

reserver :

id |idReservation | idChambre
---+--------------+----------
0  | 1            | 1

chambre :

id | idCategorie
---+------------
1  | 1
2  | 2

reservation :

id | date       | dateDebut  | dateFin
---+------------+------------+---------
1  | 03/04/2015 | 01/04/2015 | 06/04/2015
2  | 05/04/2015 | 08/04/2015 | 12/04/2015

I want my query to return id 1 and 2 for chambre but all I get actually is 1

P.S : Sorry, english is not my native language but stackexchange is such an amazing website.

When you use a column from a table on the "wrong"* side of an OUTER join, the join usually  becomes equivalent to an INNER join.

* "wrong": right side of a LEFT join and left side of a RIGHT join.

So, the solution is usually to move the condition from the WHERE to the ON of the outer join. Like this:

SELECT cre.id,
       rer.idChambre                 -- to actually see NULL
                                     -- where there is no match
FROM   reserver AS rer
       INNER JOIN reservation AS ron
         ON rer.idReservation = ron.id
       RIGHT JOIN chambre AS cre
         ON rer.idChambre = cre.id
         AND (ron.dateFin < ?
             OR ron.dateDebut > ?
             OR (ron.dateFin IS NULL AND ron.dateDebut IS NULL)) ;

Another issue is the = NULL that have to be converted to IS NULL. Nulls are tricky and are not equal to anything, not even to themselves.

It seems it wass not very clear what exactly you wanted. Your issue - as far as I can understand from the question and the following comments - is that you want:

Find all rooms (chambres) that are not reserved for the specific period (4 April 2015 to 4 May 2015)?

Then your query could be rewritten like this:

SELECT cre.id
FROM   chambre AS cre
       LEFT JOIN
                 reserver AS rer
                 INNER JOIN reservation AS ron
                 ON rer.idReservation = ron.id
                 AND (ron.dateFin > '2015-04-04 00:00:00'
                    AND ron.dateDebut < '2015-05-04 00:00:00')
       ON rer.idChambre = cre.id
WHERE
    rer.idReservation IS NULL ;

See the SQLfiddle-2 for various other ways to write it (Please avoid, RIGHT joins as much as you can. They confuse many developers who are used to work with LEFT outer joins only.


How can I use MySQL variables in subqueries? I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why.

Here's the query:

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

Here's the information underneath the variable and table.

TABLE:

mysql> mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

VARIABLE:
mysql> SELECT GROUP_CONCAT(QUOTE(name)) INTO @animal_names FROM animals WHERE name LIKE '%%';
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT @animal_names;
+-----------------------------------------------+
| @animal_names                                 |
+-----------------------------------------------+
| 'dog','cat','penguin','lax','whale','ostrich' |
+-----------------------------------------------+
1 row in set (0.00 sec)

ISSUE:
mysql> SELECT * FROM animals WHERE name IN(SELECT @animal_names);
Empty set (0.00 sec)

mysql> EXPLAIN SELECT * FROM animals WHERE name IN(SELECT @animal_names);
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | animals | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1249): Select 2 was reduced during optimization

MySQL refuses to tell me why the "SELECT @animal_ids" is not working! Is there some MySQL variable that needs to be adjusted for that subquery to work?

This is a test sample. I'm trying to do this on a larger scale with about 800 values in a list. I want the list in a variable so I don't have to do A SELECT query multiple times to DELETE, INSERT, SELECT the values I need across multiple databases and tables.

Obviously this can be easily done with a Python or Golang script, but I'm trying to do this in MySQL directly. While I appreciate it, please refrain from offering script-based solutions.

The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows.

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

is translated to

SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\'');

MySQL variable cannot hold table resultset so this is not directly possible. You would have to create a subquery to extract separate values to rows by joining some counter table and parsing the string. Or you may use dynamic sql which allows to take a string and parse it - that way you would define new variable containing entire select with the IN list filled by your current variable, and then prepare  a statement from that string representation (sort of SQL eval). But probably best would be to just use the query direcly as a subquery instead of filling the variable. Newer version should materialize such independent subquery automatically. For good performance on older versions, you might want to create a temporary table instead and use JOIN and not IN.

Just a note - GROUP_CONCAT() is not very safe as there is a limit (variable) to how long string it can return and some results may be lost (with a warning) when the limit is exceeded.


how to create backup for stored procedure,trigger and functions of a database in sql server 2008? I'm having 2 databases in 2 different servers(.246 and .252). I need to create backup for all stored procedures, triggers and function from 252 server and restore it in 246 server.database name in both server is transcredit. around 500SP are there in 252 server. can anyone help me?

I guess you have framed your question to somewhat misleading.

You want to script out all stored procedures, triggers and functions from one server and create them on the new server.

Option 1 : USING SSMS

OPTION 2 : USING TSQL - sys.sql_modules with sys.objects


Do I need separate indexes for each type of query, or will one multi-column index work? I somewhat know the answer to this question already, but I always feel as though there is more I need to pick up on the topic.

My basic understanding is that generally speaking, a single index that just includes all the fields you might be querying/sorting on at any given time isn't likely to be useful, yet I have seen this type of thing. As in, someone thought, "Well, if we just put all this stuff in an index, the database can use it to find what it needs", without having ever seen an execution plan for some of the actual queries being run.

Imagine a table like so:

id int pk/uid
name varchar(50)
customerId int (foreign key)
dateCreated datetime

I might see a single index including the name, customerId and dateCreated fields.

But my understanding is that such an index would not be used in a query like, for example:

SELECT [id], [name], [customerId], [dateCreated]
   FROM Representatives WHERE customerId=1
   ORDER BY dateCreated

For such a query, it seems to me that a better idea would be an index including the customerId and dateCreated fields, with the customerId field being 'first'. This would create an index that would have the data organized in such a way that this query could quickly find what it needs - in the order that it needs.

Another thing I see, perhaps as frequently as the first, is individual indexes on each field; so, one each on name, customerId and dateCreated fields.

Unlike the first example, this type of arrangement seems to me sometimes to at least be partially useful; the query's execution plan may show that at least it's using the index on the customerId to select the records, but it's not using the index with the dateCreated field to sort them.

I know this is a broad question, because the specific answer to any particular query on any particular set of tables is usually to see what the execution plan says it's going to do, and otherwise take the specifics of the table(s) and queries into account. Also, I know that it depends on how often a query might be run as opposed to the overhead of maintaining a particular index for it.

But I suppose what I'm asking is as a general 'starting point' for indexes, does the idea of having specific indexes for specific, frequently-pulled queries and the fields in the WHERE or ORDER BY clauses make sense?

You are right in that your example query would not use that index.

The query planner will consider using an index if:

all the fields contained in it are referenced in the query
some of the fields starting from the beginning are referenced

It will not be able to make use of indexes that start with a field not used by the query.

So for your example:

SELECT [id], [name], [customerId], [dateCreated]
   FROM Representatives WHERE customerId=1
   ORDER BY dateCreated

it would consider indexes such as:

[customerId]
[customerId], [dateCreated]
[customerId], [dateCreated], [name]

but not:

[name], [customerId], [dateCreated]

If it found both [customerId] and [customerId], [dateCreated], [name] its decision to prefer one over the other would depend on the index stats which depend on estimates of the balance of data in the fields. If [customerId], [dateCreated] were defined it should prefer that over the other two unless you give a specific index hint to the contrary.

It is not uncommon to see one index defined for every field in my experience either, though this is rarely optimal as the extra management needed to update the indexes on insert/update, and the extra space needed to store them, is wasted when half of them may never get used - but unless your DB sees write-heavy loads the performance is not going to stink badly even with the excess indexes.

Specific indexes for frequent queries that would otherwise be slow due to table or index scanning is generally a good idea, though don't overdo it as you could be exchanging one performance issue for another. If you do define [customerId], [dateCreated] as an index, for example, remember that the query planner will be able to use that for queries that would use an index on just [customerId] if present. While using just [customerId] would be slightly more efficient than using the compound index this may be mitigated by ending up having two indexes competing for space in RAM instead of one (though if your entire normal working set fits easily into RAM this extra memory competition may not be an issue).