Error 134 in a MySql table I have a file (mysql error file) full of this message

  [ERROR] Got error 134 when reading table './xxxx/wp_options'

I don't really know how to fix this problem

MySQL error code 134 = Record was already deleted (or record file crashed)

Solution : You need to run a table(s) repair on the database

Identifying table corruption

Checking tables

There are three ways to check tables. All of these work with MyISAM tables, the default, non-transactional table type, and one with InnoDB, the most mature of the MySQL transactional table types. Fortunately, MySQL now allows you to check tables while the server is still running, so corruption in a minor table need not affect everything on the server.

The CHECK TABLE SQL statement (obviously the server must be running
for this)
Running the mysqlcheck command-line utility (the server can be
running)
Running the myisamchk command-line utility (the server must be down,
or the tables inactive)

Checking tables with CHECK TABLE

The first method for checking tables is to run the CHECK TABLE SQL statement while connected to the server. The syntax is:

CHECK TABLE tablename[,tablename2...] [option][,option2...]

QUICK

The quickest option, and does not scan the rows to check for incorrect links. Often used when you do not suspect an error.

FAST

Only checks tables if they have not been closed properly. Often used when you do not suspect an error, from a cron, or after a power failure that seems to have had no ill-effects.

CHANGED

Same as FAST, but also checks tables that have been changed since the last check.

MEDIUM

The default if no option is supplied. Scans rows to check that deleted links are correct, and verifies a calculated checksum for all keys with a calculated a key checksum for the rows.

EXTENDED

The slowest option, only used if the other checks report no errors but you still suspect corruption. Very slow, as it does a full key lookup for all keys for every row. Increasing the key-buffer-size variable in the MySQL config. file can help this go quicker.

Note that CHECK TABLE only works with MyISAM and InnoDB tables. If CHECK finds corruption, it will mark the table as corrupt, and it will be unusable. See the Repairing tables section below for how to handle this

Checking tables with mysqlcheck

The second method is to run the mysqlcheck command-line utility. The syntax is:
mysqlcheck [options] dbname tablename [tablename2... ].

The following options pertain to checking (mysqlcheck can also repair, as well as analyze and optimize.

--auto-repair

Used together with a check option, it will automatically begin repairing if corruption is found.

--check, -c

Checks tables (only needed if using mysqlcheck under another name, such as mysqlrepair. See the manual for more details)

--check-only-changed, -C

Same as the CHECK TABLE ... CHANGED option above.

--extended, -e

Same as the CHECK TABLE ... EXTENDED option above.

--fast, -F

Same as the CHECK TABLE ... FAST option above.

--medium-check, -m

Same as the CHECK TABLE ... MEDIUM option above.

--quick, -q

Same as the CHECK TABLE ... QUICK option above.

Note that you can specify multiple tables, and that mysqlcheck only works with MyISAM tables.

Checking tables with myisamchk

Finally, there is the myisamchk command-line utility. The server must be down, or the tables inactive (which is ensured if the --skip-external-locking option is not in use). The syntax is: myisamchk [options] tablename.MYI, and you must be in, or specify, the path to the relevant .MYI files (each MyISAM database is stored in its own directory). These are the available check options:

--check, -c

The default option

--check-only-changed, -C

Same as the CHECK TABLE ... CHANGED option above.

--extend-check, -e

Same as the CHECK TABLE ... EXTENDED option above.

--fast, -F

Same as the CHECK TABLE ... FAST option above.

--force, -f

Will run the myisamchk repair option if any errors are found

--information, -i

Display statistics about the checked table

--medium-check, -m

Same as the CHECK TABLE ... MEDIUM option above.

--read-only, -T

Does not mark the table as checked

--update-state, -U

This option stores when the table was checked, and the time of crash, in .MYI file.

You can also use wildcard to check all the .MYI tables at the same time, for example:

% myisamchk *.MYI

Note that myisamchk only works with MyISAM tables. For those of you still using the old ISAM table types, there is also isamchk, though there is really little reason not to upgrade to MyISAM.

Repairing tables

In most cases, only the index will be corrupted (the index is a separate, smaller, file with records that point to the main data file) - actual data corruption is extremely rare. Fixing most forms of corruption is relatively easy. As with checking, there are three ways to repair tables. These all only work with MyISAM tables - to repair corruption of the other table types, you will need to restore from backup:

The REPAIR TABLE SQL statement (obviously the server must be running
for this)
The mysqlcheck command-line utility (the server can be running)
The myisamchk command-line utility (the server must be down, or the
tables inactive)

Repairing a table requires twice as much disk space as the original table (a copy of the data is made), so make sure you are not going to run out of disk space before you start.

Repairing a table with REPAIR TABLE

The syntax is, as would be expected, REPAIR TABLE tablename[,tablename1...] [options]. This method only works with MyISAM tables. The following options are available.

QUICK

The quickest, as the data file is not modified.

EXTENDED

Will attempt to recover every possible data row file, which can result in garbage rows. Use as a last resort.

USE_FRM

To be used if the .MYI file is missing or has a corrupted header. Uses the .frm file definitions to rebuild the indexes.

Repairing tables with mysqlcheck

The mysqlcheck command-line utility can be used while the server is running, and, like all the methods of repair, only works with MyISAM tables. The syntax is:

%mysqlcheck -r sports_results fixtures -uuser -ppass

Repairing tables with myisamchk

The server must be down, or the tables inactive (which is ensured if the --skip-external-locking option is not in use). The syntax is myisamchk [options[ [tablenames]. Remember again that you must be in, or specify, the path to the relevant .MYI files. The following options are available:

--backup, -B

Makes a .BAK backup of the table before repairing it

--correct-checksum

Corrects the checksum

--data-file-length=#, -D #

Specifies the maximum length of the data file, when recreating

--extend-check, -e

Attempts to recover every possible row from the data file. This option should not be used except as a last resort, as it may produce garbage rows.

--force, -f

Overwrites old temporary .TMD files instead of aborting if it encounters a pre-existing one.

keys-used=#, -k #

Can make the process faster by specifying which keys to use. Each binary bit stands for one key starting at 0 for the first key.

--recover, -r

The most commonly used option, which repairs most corruption. If you have enough memory, increase the sort_buffer_size to make the recover go more quickly. Will not recover from the rare form of corruption where a unique key is not unique.

--safe-recover, -o

More thorough, yet slower repair option than -r, usually only used only if -r fails. Reads through all rows and rebuilds the indexes based on the rows. This also uses slightly less disk space than a -r repair since a sort buffer is not created. You should increase the key_buffer_size value to improve repair speed if there is available memory.

--sort-recover, -n

MySQL uses sorting to resolve the indexes, even if the resulting temporary files are very large.

--character-sets-dir=...

The directory containing the character sets

--set-character-set=name

Specifies a new character set for the index

--tmpdir=path, -t

Passes a new path for storing temporary files if you dont want to use the contents of the TMPDIR environment variable

--quick, -q

The fastest repair, since the data file is not modified. A second -q will modify the data file if there are duplicate keys. Also uses much less disk space since the data file is not modified.

--unpack, -u

Unpacks a file that has been packed with the myisampack utility.

An example of its usage:

% myisamchk -r fixtures
- recovering (with keycache) MyISAM-table 'fixtures.MYI'
Data records: 0

  All above information was taken from the following reference

Repairing Data Base Corruption
How to Repair MyISAM Tables - MySQL

Pls refer the articles if anything that I have mentioned above is unclear.Hope my answer helps!


Why is SQL Server 2012 memory usage limited to 4 GB I have an SQL Server Installation which suffers very bad performance due to lack of memory (queries runnig very slow, RESOURCE_SEMAPHORE...). The memory usage never goes above ~4.1 GB, although the machine has 16 GB of physical memory and only 44% is used.

Resource Monitor shows a working set of 4,289,000 KB for sqlservr.exe.

I'm wondering where this limitation is coming from, although we increased "maximum server memory" to 10 GB.

Both Os and DB Server are 64-bit and the editions used have much higher limitations than 4 GB.

I've used sysinternals TestLimit to see if there is a general memory limitation for processes, but a testprocess could use more memory, so it seems to be related to SQL Server itsself.

Any help is appreciated!

OS: Windows Server  2012 R2 Standard 64-bit

SQL Server: SQL Server Standard 64-bit, Version 11.0.5058.0

Server is virtualized on VMWare ESXi 5.1.0

Short update: I could solve the problem myself.
Problem was actually not related to the memory. We had high traffic with many very CPU expensive queries which created a constantly high CPU load, which were the reasons for RESOURCE_SEMAPHOREs.

After reducing the quantitiy of queries significantly and tuning the queries itself to be less expensive, the load decreased and there were no more RESOURCE_SEMAPHOREs.

Memory usage is still only 4 GB, but obviously system doesn't need to use more!


SQL Server Agent: QUOTED_IDENTIFIER The scenario is rather simple: A Job fails in production, giving off an error "ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER' ...".

The question is, why does this problem appear in production, and not in any of our testing environments? Checking DBCC USEROPTIONS, the results are exactly the same across all databases. Also, the script itself contains no quotes at all. It simply rebuilds a few indexes and updates a few statistics with fullscan.

Using the exact same job on any other database, I can set QUOTED_IDENTIFIER ON, or OFF, or just not set it at all and let it run on connection defaults. And it'll always work regardless. But in production, it appears it either has to be set ON or OFF.

I'm asking this because time-wise I can't afford to test this once a day, I need to know why this error is happening and how to predict which setting is needed and when. The only visible difference between the test and production databases is collation, but even creating a testing database to match the collation in production, the Job still works regardless of the QUOTED_IDENTIFIER setting.

Thanks!

I just ran into this same issue. By default the first statement in a job step is to SET QUOTED_IDENTIFIER OFF - you can see this in a Profiler trace. The solution is to add a line at the beginning of your code to SET QUOTED_IDENTIFIER ON.

As to your script not containing quotes - I believe when creating or rebuilding certain types of indexes that setting is required is to be on.

Here's a couple links:

https://stackoverflow.com/questions/15218893/first-statement-issued-by-sql-server-agent-sets-quoted-identifier-off


Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR I have the following [fairly meaningless, just for the purpose of demonstration] query in the StackOverflow database:

SELECT  *
FROM    Users u
        LEFT JOIN Comments c
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'

The only index on the Users table is a clustered index on ID.

The Comments table has the following Non-Clustered indexes as well as Clustered Index on ID:

CREATE INDEX IX_UserID ON Comments
(
    UserID,
    PostID
)

CREATE INDEX IX_PostID ON Comments
(
    PostID,
    UserID
)

The estimated plan for the query is here:

I can see the first thing the optimizer will do is perform a CI scan on the users table to filter only those users where DisplayName = Alex, effectively doing this:

SELECT  *
FROM    Users u
WHERE   u.DisplayName = 'alex'
ORDER BY Id

and retreiving results as such:

Then it will scan the comments CI and for every row, look to see if the row satisfies the predicate

u.Id = c.UserId OR u.Id = c.PostId

Despite the two indexes, this CI scan is performed.

Wouldn't it be more efficient if the optimizer did a separate seek on each of the indexes in the Comments table above and join them together?

If I visualise what that would look like, in the screenshot above we can see the first result of the Users CI scan is ID 420

I can visualize what the IX_UserID Index looks like using

SELECT      UserID,
            PostID
FROM        Comments
ORDER BY    UserID,
            PostID

so if I seek to the rows for user ID 420 as an index seek would:

for every row where UserID = 420, I can look if  u.Id = c.UserId OR u.Id = c.PostId of, course they all match the u.Id = c.UserId part of our predicate,

So for the second part of our index seek, we can seek through our index IX_PostID which can be visualised as follows:

SELECT      PostID,
            UserID
FROM        Comments
ORDER BY    PostID,
            UserID

If I seek to Post ID 420 I can see nothing is there:

So we then go back to the results of the CI scan, move to the next row (userId 447) and repeat the process.

The behaviour I have described above is possible using in a WHERE clause:

SELECT      UserID,
            PostID
FROM        Comments
WHERE       UserID = 420 OR PostID = 420
ORDER BY    UserID,
            PostID

Plan here

My question therefore is, why isn't an OR condition in a JOIN clause able to perform an index seek on appropriate indexes?

Rather than focusing on how to improve a query like this, which is what the other answers are doing, I'm going to try to answer the question being asked: why doesn't the optimizer produce a plan like the one you've described (that scans the Users table, and then seeks into the two indexes on the Comments table).

Here's your original query again (note I'm using MAXDOP 2 just to simulate what I saw in your execution plans):

SELECT  *
FROM    Users u
        LEFT JOIN Comments c
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'
OPTION (MAXDOP 2);

And the plan:

Scan of dbo.Users with residual predicate to get just the "alex" users
For each of those users, scan the dbo.Comments table and filter matches in the join operator
Estimated cost: 293.161 optimizer units

One attempt to get the plan you want would be to try and force a seek on the dbo.Comments table:

SELECT  *
FROM    Users u
        LEFT JOIN Comments c WITH (FORCESEEK)
            ON u.Id = c.UserId OR
               u.Id = c.PostId
WHERE   u.DisplayName = 'alex'
OPTION (MAXDOP 2);

The plans looks like this:

scan of the dbo.Users table (with a residual predicate to only get users named "alex"),
seek into each of the two indexes to get the requested Id values (which are unioned together)
followed up by a key lookup to get the rest of the columns (since we selected *)
Estimated cost: 5.98731 optimizer units

So the answer is that the optimizer is definitely capable of producing such a plan.  And it doesn't seem to be a cost-based decision (the seek plan looks much cheaper).

My best guess is that this is just some kind of limitation in the optimizer's exploration process - it doesn't seem to favor converting a left join with an or clause into an apply.   This is really unfortunate in this particular case, as performance is dismal in the scan plan (the query takes 45 seconds on my machine)  vs the apply plan (less than 1 second).

Side note: You can override the heuristic that disfavors index union plans with undocumented trace flag 8726. See https://dba.stackexchange.com/a/23779 for additional details on that front!

As Rob Farley helpfully pointed out, using APPLY directly (potentially with a UNION as well) is a better approach to get the plan you're looking for - both of those produce the "better" version of this plan (the FORCESEEK version).  I would say that "OR in a JOIN" is kind of a known anti-pattern, and should be avoided since it doesn't seem like the optimizer has great support for that type of query directly.


MySql replication with Raw Data and InnoDB tables I want to start replication on DB that has many InnoDB tables and MyISAM tables.
I did everything according to manual Creating a Data Snapshot Using Raw Data Files.

Firstly, I tried copied all tables files with relay log  and etc. without ib_logfile and ibdata. Replication didn't work it couldn't find InnodDB tables, after that I tried to do the same with ib_logfile and ibdata, it failed with error message Error 'Unknown table engine 'InnoDB'' on opening tables.

When I tried to copy my.cnf from original server mysql failed to start at all (version 5.1.41).

I am not sure if it's possible to start replication with raw data and InnoDb files. Please, if you have experience, share it with me. How can I deal with it.

Thanks!

When you get 'Unknown table engine INNODB', it means something when wrong with startup. If you look in your error logs, you should be able to get more information.

My first instinct is that your ib_logfile is a different size on your master than what is configured in the slave. If you stop the slave server, delete the ib_logfile from the snapshot when setting up the slave, then restart mysql, it should correct itself.

NOTE: Do not delete the ibdata file! Only the ib_logfiles


SQL Parameter Sniffing Possible Workaround Our system has roughly 500 "Customers" that have extreme differences in record counts. Here is a (greatly simplfied) sample query that could return 0 - 100000 rows depending on the parameter passed. This query runs find but I am fairly certain it suffers from parameter sniffing depending on which parameter gets cached.

exec sp_executesql N'
               SELECT *
                 FROM Widgets
                WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'

If I modify the query to include the parameter as a selection it seems to cache the query for each individual customer.

exec sp_executesql N'
               SELECT ''bda43162-2d98-4e79-8e81-7056f6df5e51'', *
                 FROM Widgets
                WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'

The performance is greatly improved because each customer gets it own version of the query cached. Are there any side effects of this approach?

Assumptions:

It must be dynamic SQL for this portion of the system
This query is run frequently
The number of customers will not grow quickly

EDIT: I considered using OPTION (RECOMPILE) but I did not want to recompile every time if I could get the benefits of the compiled query with this approach.

Parameter sniffing means that one set of parameters produces a dramatically different execution plan than the other, and that if the wrong plan is cached, you get adverse performance effects.

This answer is based on your simplified query - to get accurate advice for your query, you'll need to post the query and the two different plans that resulted from parameter sniffing. (I'd always rather get to the accurate root cause rather than troubleshooting a simplified example, but I've gotta work with the code you posted, so here it goes.)

Your query only has one table in it (assuming that Widgets isn't a view):

SELECT * FROM Widgets WHERE CustomerId=@0

That means if you have a nonclustered index on CustomerID, some CustomerID values might produce a plan with a nonclustered index seek followed by a key lookup, whereas other parameters will do a clustered index scan across the Widgets table.

There are a few ways you could fix this scenario, and I'm going to list them in a generally safest-to-most-risky way:

Use OPTION (RECOMPILE) on the query. This does require a code change to add the line to the query, but then every execution of this query should get the most appropriate plan. The risk is higher CPU use for plan execution (although that generally won't matter in a single-table, single-predicate query like this where the plan will be ultra simple to generate).

Cache every variety of the plan. You noted passing the query in as a string will get each parameter to cache its own individual plan. While that will work today, it does bloat the plan cache (taking up more of SQL Server's memory). The risk here is that someone will turn on Forced Parameterization, a database-level option that will parameterize all of your queries whether they're sent in as strings or not, and suddenly you're back to troubleshooting this issue again.

The rest of these are valid solutions, but not for your single-table, single-predicate query. I'm only listing them here for posterity and clarity:

Use the OPTIMIZE FOR UNKNOWN query hint, or as we like to call it, optimize for mediocre. Requires a query change, and gives you a generally-good-enough plan. This will avoid random changes of the query plan due to parameter sniffing, but the risk is that it still won't be the most performant plan.

Use the OPTIMIZE FOR query hint with a specific CustomerID. This also requires a code change, and you would optimize the query for one of your larger customers. This will get a query plan that's great for big customers, and not-so-good for small customers. Small customer performance will go down, but the big customers won't cripple the system. The risk is that your customer distribution will change, and this will no longer be the right plan for the app as a whole.

Use a query plan guide. You can get exactly the query plan you want, and then pin the plan guide to memory. Here's the Books Online section on plan guides. I'm not usually a big fan of this because if your indexes change, the query plan won't take advantage of the new indexes. If your query changes, the plan guide will no longer be in effect. Suddenly the system might perform terribly, and people will have forgotten that a plan guide was helping before.

Use a stored procedure with manual logic. Have branches that call different stored procedures, one for large customers and one for small customers. This is only used for much larger, more complex queries that can have variations between minutes and hours (or not completing at all).


Can I migrate 2 tables (with one table foreign key referencing the other) to in-memory OLTP SQL 2016 - I have 2 tables, Foo & Bar - Bar references Foo. I'd like to turn Foo into an in-memory optimized table, however as Bar references this, I can't do it.

I also can't make Bar in-memory due to its dependence on Foo.

Seems like I need to do this to both tables at the same time - is this possible without recreating both tables from scratch?

Seems like I need to do this to both tables at the same time

Correct.  If you try to recreate the FK after migrating one table you will get:

Msg 10778, Level 16, State 0, Line 20
Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.

  is this possible without recreating both tables from scratch?

Migrating to in-memory tables always requires recreating the tables from scratch.  SSMS will rename your old tables, create the new in-memory tables, load them from your old tables, and drop your old tables.


Update Lowest ID Only We have two tables that are joined on Social Security #'s, but for the sake of this example, I have altered it to join on name only.  One table holds each individual sale, and the other table holds the employee's annual salary.  I need to compile this data into one table, and only update ONE row from the individual sale table with the annual salary.  How can I update the smallest saleid for each employee?

Below is sample DDL:

Declare @Helper Table (empname varchar(50), saleid int, sal float)

Declare @Helper1 Table (empname varchar(50), sal float)

Insert Into @Helper (empname, saleid) Values
('Number One', 818181),
('Number One', 19213),
('Number One', 919131),
('Number One', 131311313)
,('Number Two', 131313),
('Number Two', 9823),
('Number Two', 24)

Insert Into @Helper1 (empname, sal) Values
('Number One', 44000.00), ('Number Two', 55000.00)

This is my desired output:

empname     saleid  sal
Number One  19213   44000
Number One  818181
Number One  919131
Number One  131311313
Number Two  24      55000
Number Two  9823
Number Two  131313

you could try something like:

update h
set sal = t.sal
from @Helper h
join (
    select h.empname, h.saleid, h1.sal, row_number() over(partition by  h.empname order by h.saleId ) as n
    from @Helper h
    join @Helper1 h1 on h.empname = h1.empname
    ) t on h.empname = t.empname and h.saleid = t.saleid
where n = 1

(assuming the primary key of @Helper in on empname + saleid)


char or smallint for a column with numeric codes? I have columns in my application that have values that always have 4 digits, e.g. '0100' or '1230'. One of examples could be Merchant Category Code.
For the time being, I have set the type of this column to char (4). I have many columns of this type. One of these types of columns is also included in nonclustered UNIQUE index. I would like to establish one approach to this type of column.
Is it better to use char (4) or maybe smallint, padding in the application by adding ' ' or  0 to the beginning?
The table in which I have these columns is partitioned and will contain about 300 million records. UPDATE will probably not be executed on columns with this type. They will also not appear in WHERE (maybe sometimes in ad hoc reports).
Which data type should I choose?

It's like a zip code, it can be numeric but you must store leading zeros

Exactly. I am thinking what is better approach in terms of performance. Also have to take into consideration that if I store it as smallint, the application code has to parse it to string and add leading zeros.

The standard clearly says "four-digit code", which in my view means it should always contain 4 numeric characters, which in turn means the appropriate data type is char(4).
If you want to ensure the code value adheres to ISO 18245, store it as char(4) in the database along with a check constraint.


Replacing a member of a replica set with the same host name I am replacing a member of a replica set (the new member has a different IP address).

What I am looking to do is as follows:

RSYNC the data directory of the existing secondary node to the new node.
Stop the existing node.
Map the existing hostname to the IP of the new node.
Start MongoDB on this new node with the replica set.

Mongo Version 3.2 and storage engine MMapV1

it is 3 member replica set.

Primary
Secondary
Arbiter
My plan is to first replace arbiter with a new secondary node then. Do the procedure with on existing secondary node to replace this also.

Could someone please verify if the above steps make sense?

I think you can get what you want if you first rs.add() the new secondary, and when it's done you rs.remove() the arbiter.

There will be a brief period of thime with 4 nodes, howerever.


Why is multiple independent index always better than concatenated index Index Merge: Combining Multiple Indexes

  It is one of the most common question about indexing: is it better to
  have one index for each column or a single index for all columns of a
  where clause? The answer is very simple in most cases: one index with
  multiple columns is better—that is, a concatenated or compound index.
  “Concatenated Indexes” explains them in detail.

I can totally understand this assertion under this scenario:

TABLE( A,B,C )
Indexing 1: Index A, Index B, Index C
Indexing 2: Index (A,B,C)
Query: JOIN ON X.B=B AND X.C=C

because under this scenario, Indexing 2 cannot used at all ( I know about the exceptions )

but under this scenario:

TABLE( A,B,C )
Indexing 1: Index A, Index B, Index C
Indexing 2: Index (A,B,C)
Query: JOIN ON X.A=A X.B=B AND X.C=C

I think Indexing 2 is better, it only needs to traverse 1 index tree instead of 3 right ?

Original author here.

The quote is meant as following:

In your first query, just filtering on B and C, what would be better:

Indexing 1: Index B, Index C
Indexing 2: Index (B,C)

Clearly, Indexing 2 is better.

The context was meant to be: if I index all columns, is it better to have one index with all of them, or one separate index for every column. In that case, it is almost always better to have one with all columns.

It's a design advice, not for assessment.


Database Log file Backup I was wondering if the Database Backup .bak Contain the Log file.

i found that the space different between the database and backup is form the locked space for the database.
but i didn't found any related posts that says if the log file is in the backup file.

No it isn't. Or at least not in its entirety.

Part of the transaction log will be included though reflecting concurrent activity during the backup. This is to allow the database to be restored to a transactionally consistent state.

Much more detail in this article by Paul Randal Understanding SQL Server Backups


Can both primary and secondary servers be online simultaneously in Log Shipping? I am new to log shipping and I want to know can both primary and secondary servers be online at the same time like in replication, or only the primary one can be online while others get the log backups but are in a passive state?

I am new to log shipping and I want to know can both primary and secondary servers be online at the same time like in replication,

No, not like replication where the records in the tables at the subscriber can both be read and updated. The secondary server can be in read_only/standby and restoring mode. When in read_only/standby mode the read only queries can access the database but would either block the restore operation or be disconnected when restore happens so that is not a truly online in the sense you are asking.


Sharding standalone MongoDB I've got a standalone MongoDB server with a lot amount of data on it. Let's say, it's srv1 server. I've decided to make a sharded cluster, so I've created additional servers srv2 and srv3.

We have:

srv1 - existing mongodb server with data, that will be shard0001.
srv2 - new mongodb server, that will be shard0000.
srv3 - also new mongodb server, that will be config server with running mongos instance. Mongos instance running only on that server.
vk - database I want to be sharded.

On srv3 I've started a mongos with --configdb rs0/127.0.0.1:27002 option and a mongod with --replSet rs0 --configsvr --dbpath /data/config --port 27002.
After that, via the config instance i've added srv1 and srv2 as a shards with sh.addShard("srv1-2:27017") command.
Also, on srv2 I've created an empty database and collections with the same names as on srv1. After that on config server i've executed sh.adminCommand({enableSharding: "vk"}); command.

What I have now:

Surprisingly, it's working. When i'm trying to read/write something to my database, using srv3 as connection point, it's working perfectly! When i've did something before (using srv1 standalone), average response time was about 2 seconds, now it's 0.02 seconds.

The problem I have:

Well, the sharding is not working. Database on srv2 is empty. Here is the output of the db.printShardingStatus() command from srv3:

--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("5d81f91bdde81f72ac4d1a0a")
  }
  shards:
        {  "_id" : "shard0000",  "host" : "srv2:27017",  "state" : 1 }
        {  "_id" : "shard0001",  "host" : "srv1:27017",  "state" : 1 }
  active mongoses:
        "3.6.3" : 1
  autosplit:
        Currently enabled: yes
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  5
        Last reported error:  Connection refused
        Time of Reported error:  Wed Sep 18 2019 14:41:13 GMT+0200 (CEST)
        Migration Results for the last 24 hours:
                No recent migrations
  databases:
        {  "_id" : "config",  "primary" : "config",  "partitioned" : true }
                config.system.sessions
                        shard key: { "_id" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                shard0000       1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : shard0000 Timestamp(1, 0)
        {  "_id" : "shard0000",  "primary" : "shard0000",  "partitioned" : true }
        {  "_id" : "vk",  "primary" : "shard0001",  "partitioned" : true }
        {  "_id" : "vk:",  "primary" : "shard0000",  "partitioned" : true }

Additional info:

The balancer says Connection refused, but i've opened 27017 and 27002 port on every machine in cluster. It's still not working.
In mongos (srv3) log there are a lot of successful connections both to srv1 and srv2. Telnet says the same - everything is fine and opened.
I've added shards without --replSet options. Mongod instances on srv1 and srv2 working without that options too.
I didn't created any chunk keys or something like this.

Thank you for the reading, will be very grateful for any help.

About MongoDB 3.6 shards, from the official release notes:

  Starting in 3.6, shards must be replica sets.

When you add shards, should be something like:

sh.addShard( "rs1/mongodb3.example.net:27017,mongodb4.example.net:27017,mongodb5.example.net:27017" )

With a status like:

--- Sharding Status ---
sharding version: {
   "_id" : 1,
   "minCompatibleVersion" : 5,
   "currentVersion" : 6,
   "clusterId" : ObjectId("5be0a488039b1964a7208c60")
}
shards:
   {  "_id" : "rs0",  "host" : "rs0/mongodb0.example.net:27017,mongodb1.example.net:27017,mongodb2.example.net:27017",  "state" : 1 }
   {  "_id" : "rs1",  "host" : "rs1/mongodb3.example.net:27017,mongodb4.example.net:27017,mongodb5.example.net:27017",  "state" : 1 }
active mongoses:
   "3.6.8" : 1


Oracle RMAN encryption I would like to know if it is possible given a certain backup made with RMAN in Oracle 12c is encrypted without having to restore said backup, is there any or some queries that verify if an RMAN backup is encrypted or not?

To launch the backup I use the option SET ENCRYPTION ON IDENTIFIED BY qazwsx ONLY;

Thank you very much.

Sure, you can query V$BACKUP_PIECE in target database or RC_BACKUP_PIECE in RMAN catalog:

SQL> select encrypted, tag from v$backup_piece;

ENC TAG
--- --------------------------------
NO  TAG20180926T211157
NO  TAG20180926T211159
YES TAG20180926T211224
YES TAG20180926T211227


Why would I download CA certificate when starting a managed Postgres cluster on DigitalOcean? I am trying for the first time a Postgres cluster provided as a managed service by DigitalOcean.com.
As part of configuring the new cluster, their web interface gives me a user name, password, port number and such. On that web page is a tiny link saying Download the CA certificate. I assume CA means Certificate Authority.
➥ Why would I use or not use that link to download the certificate?
When I click the link, I end up with a file on my machine named ca-certificate.crt. How would I use this file?

You can use it with TLS to verify that you are connecting to the correct server.
The connection string for that would look like:
host=... port=... dbname=... user=... sslmode=verify-full sslrootcert=/path/to/ca-certificate.crt

If you cannot specify the connection string that way, you can use the environment variables PGSSLMODE and PGSSLROOTCERT.
Then the PostgreSQL client library will verify that the server's certificate is actually signed by the CA certificate and that the server name is correct.
Of course that requires that your PostgreSQL client library was configured --with-openssl.


Line Comment in MongoDB config file How to mark a line comment in mongodb configuration file?

cpu=true #temporary for dev env

Is this the right way?

There is no clue about it in their website page about setting the configuration options.

If you wish to mark the line a comment, then prepend the hash mark like so:

# cpu=true

Any line beginning this way will be ignored completely.

Although it is not Windows (which I believe you are running), you can see examples in the Linux config file examples.


How to "cascade delete" children without deleting parent? I have a table, let's call it parent:

CREATE TABLE parent (
    id          TEXT PRIMARY KEY,
    name        TEXT,
    description TEXT,
    -- other parent attributes
    created_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

It has a number of child tables, let's say child_1, child_2, ... child_n. They all look something like:

CREATE TABLE child_n (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parent_id TEXT NOT NULL REFERENCES parent (id) ON DELETE CASCADE,
    -- other columns of child_n attributes
);

Some of these children may even have child tables of their own.

When I update a row in parent, I often want to also replace all of its descendants (ie: its children, their children, and so on recursively), as they are only relevant to the older version of that parent row.

My plan was to rely on that REFERENCES parent (id) ON DELETE CASCADE, but that only works if I actually delete the parent. I know I can delete and insert in a transaction, but this makes preserving the values of columns (like created_at) more awkward.

Is there a way to trigger the cascading delete of children without actually deleting the parent row?

Simply re-insert deleted records:

WITH cte AS ( DELETE
              FROM parent
              WHERE {conditions}
              RETURNING * )
INSERT INTO parent
SELECT *
FROM cte;

fiddle


Query tables with information_schema in pgsql I am trying to query the tables contained in a schema from an insert trigger on another table.

My process is :

Manually create the schema with DB Manager inside QGIS.
Load tables foo, bar, baz.
Insert a reference to that schema inside a table which trigger a function to perform queries on these tables.

I am fine to create a trigger/trigger function, but unable to create the query to grab the information inside the tables

EDIT :

WITH info_schema_subset_table as (SELECT table_schema,table_name,
array_to_string((regexp_split_to_array(table_name,'_'))[4:array_length(regexp_split_to_array(table_name,'_'),1)-1],'_')) END as new_table
FROM information_schema.tables
where table_schema = NEW,schema_name
ORDER BY new_table ASC)
EXECUTE 'CREATE TABLE $2 as (SELECT * FROM $1);'
USING info_schema_subset_table.table_schema || '.' ||info_schema_subset_table.table_name,info_schema_subset_table.new_table;

I am unable to run a simple execute. What am I missing?

Sounds like you need dynamic SQL for this. First build your query using string concatenation with the desired values (e.g. schema name, table name), then execute it with EXECUTE.

Example:

    -- create example objects
    CREATE SCHEMA example_schema;
    CREATE TABLE example_schema.example_table1 (example_column integer);
    CREATE TABLE example_schema.example_table2 (example_column integer);
    CREATE TABLE example_schema.example_table3 (example_column integer);

    -- example anonymous block using dynamic queries
    DO
    $$
    DECLARE
      dynamic_query text;
      catalog_row record;
      counter integer = 0;
    BEGIN
      -- insert an integer, that's incremented for every iteration into the example tables
      FOR catalog_row IN SELECT table_schema || '.' || table_name qualified_table_name
                                FROM information_schema.tables
                                WHERE table_schema = 'example_schema'
                                      AND table_name LIKE 'example_table%' LOOP
        counter := counter + 1;
        -- building the query;
        dynamic_query := 'INSERT INTO ' || catalog_row.qualified_table_name || ' VALUES (' || counter || ')';
        -- executing the query;
        EXECUTE dynamic_query;
      END LOOP;
    END;
    $$
    LANGUAGE plpgsql;

    -- check if it has worked
    SELECT * FROM example_schema.example_table1
    UNION ALL
    SELECT * FROM example_schema.example_table2
    UNION ALL
    SELECT * FROM example_schema.example_table3;


Applying data changes from one database to another I have an application running on Oracle 9i database and I was looking for something more practical to deploy data changes (not schema changes) from the development database to production.
Currently I work as follows:

Set all the parameters needed on development database (this might take months), the user validates everything and then I have to apply everything again (which might take some more months). I can't just import the development database into production, because all the data from production will be lost.
Is there something that can be done to avoid this "reworking"?

I tried Database Copy and Data Diff tools available on SQL Developer, but didn't work, since it has a lot of problems with dependencies when inserting new data on the tables..

The point is to keep the new/changed data made by the users in production while I work on the development database. Then import what I did without losing what the users did meanwhile.

Thanks in advance.

One way to do this is to ensure that all tables of properties, or metadata, have a date_created and date_last_modified date.  Then you can filter where the date created is in a time period and export the changes as inserts or updates.

What I do is harder. All changes to development properties are saved as a script of inserts/updates and I use a project/bug tracker to keep track of all the collection of changes.  I refresh development weekly so you see right away if you forgot something like a sequence or a grant.  A typical change could involve data inserts, package updates, grants, sequences and triggers so you have to keep it organized.

Edit: Luke asks how do you keep changes as scripts?
I do two things:

all properties and types are added by a package of procedures and functions.  This checks that the input is valid, updates any logging/audit tables and validates the changes against any business logic you may have in the database.  This is a lot of work to set up but allows you to have a consistent practice for adding new metadata.  Each time you use a procedure or function you can save your call as an sql statement.
I use Toad and this has the ability to filter a table for almost any criteria and export the results as Inserts.


"phpMyAdmin" for Cassandra Is there a tool like phpMyAdmin for RDBMS-MySQL for a Cassandra DB?

I know that there is less "runtime" config possible (column families can not be edited at runtime etc.). Nevertheless, it would be very helpful to have a GUI to inspect the different column families, browse data, and so forth.

Sounds like my Cassandra Cluster Admin is exactly what you want! Take a look at it here: https://github.com/sebgiroux/Cassandra-Cluster-Admin


are there significant differences with 500+ million row tables in Oracle? I am in a database designer in a data warehouse environment.  I am used to dealing with tables with a maximum of 1 millions rows and am now faced with tables with more than half a billion rows.  Are there any significant differences with the tools in the "efficiency toolbox"?  Can I trust my previous knowledge of indexes, partitions, and the like or are some of these specific tools more of a hindrance than a help with such large data?  Any other tips for dealing with the tables?

(Already found a great post on Updating 700 million rows to same value)

The fundamentals of indexing etc. all work in exactly the same way, so strictly speaking the only difference is the cost of getting this wrong!

That said, here's a (not necessarily complete) list of things worth bearing in mind:

B-tree indexes are likely to have an extra level in them, so the cost of using them is slightly higher. However, in a DW you should be using bitmap indexes (assuming you've got enterprise edition)
It'll take a lot longer to calculate stats for the whole table - to the point where it may not be possible in the normal overnight window. This can be overcome by

Using a smaller estimate_percent when gathering stats so less of the table is sampled.
Using incremental stats gathering (only relevant if you have global indexes on partitioned tables however)

Histograms for indexes are limited to 254 buckets. More rows likely means more distinct values, meaning that "nearly popular" values can be a larger issue for skewed data.
The chances your whole table will fit in the buffer cache approaches nil, meaning you're more likely to have more physical (disk) reads. Your normal working set may also be too large to be cached.
Partitioning can be your friend - if you get it right! If you're usually modifying and query data across multiple partitions then it can cost you more than plain tables.
Materialized views can be very useful for reducing your working set. e.g. if you have 10+ years worth of data, but the vast majority of user queries are just against the past 2 years, then creating an MV limited to just this data can be a big help.
The bigger the dataabase, the less likely it is the business will (be able to) fund a test database that is a full duplicate of the live environment. This makes it harder to reproduce performance issues in test as slow queries may be due to the scale and/or physical storage of the data. You can't count on being able to extrapolate query results from a much smaller test DB to corresponding performance in live.

If you're not already familiar with reading and understanding execution plans I'd spend some time learning these: your bound to run into performance issues at some point so knowing how to diagnose the problem correctly will become more important as it's harder to add new indexes or make schema changes when your row counts are larger.


List with all visible meta data in AFTER UPDATE trigger I have the following issue - something is updating particular table columns when such update is not allowed. I was not able to find the object (searching in T-SQL and ASP code, server jobs), so I decided to create AFTER UPDATE trigger on the given table and try to log everything that is possible and will help me to find what is updating the table.

I am wondering what metadata can I access in the trigger - for example: T-SQL statement performing the update?

Also transaction log back up are made on each 15 minutes, so I guess it is possible to detect the change from the backups having particular information like time and transaction id?

You could start with ORIGINAL_LOGIN(), SUSER_SNAME(), HOST_NAME() and 'APP_NAME()'.

However as @JeroenB said you may have better luck creating a trace or extended events session.  You can get the above information and more that way.  Make sure to restrict down the trace/EE session to just the one table and you shouldn't see a lot of overhead.  You could certainly stick with the trigger though and see if that gets you the information you need.  The main thing you could get out of the other options (that I would want to see) is the actual command being used.  I haven't found a way to get that out using a trigger I'm afraid.


Mysql table crash, what do I need to do to fix this Table '.\mysql\proc' is marked as crashed and should be repaired.

Can anyone help me figure out how to fix this?

This is a MyISAM table. It is easy to fix. Login to mysql and run this

mysql> REPAIR TABLE mysql.proc;

and you should be good to go.

BTW this is where stored procedure are stored.


I am unable to join tables in correct manner I have created Four Tables-

1) Registration Table - it has registration id(rid),First Name of user(fname), Last Name of user(lname), gender(gender),birth date(dob).

2) Post Table - it has post id(pid), post content(pcontent), user who published post(rid as foreign key from registration table), time.

3) Plikestats Table - (where it has status of which post is liked by which user)- plikeid(unique id), post id which is liked or not(pid), user's id who have either liked the post or not(rid as foreign key), likestatus(0 for liked, 1 for unliked).

4) comment Table - (where details of all the comments will be stored.)- it has comment id(cid), post id on which comment is posted(pid as  foreign key), user's id who is commenting(rid as foreign key), comment content(ccontent), time.

I have the following query: View all of a users’ posts and display the number of likes and number of comments to that post.

I am trying to get this query by using two count functions and two group bys. I have created three separate queries, which I am listing, but I am not getting complete logic for the query.

1) SELECT post.pid
     , CONCAT(registration.fname, ' ', registration.lname) AS NAME
     , post.pcontent
FROM registration JOIN post
WHERE post.rid = registration.rid
//user’s all post

2) SELECT post.pid
        , post.pcontent
        , COUNT(plikestats.pid) AS totallikes
   FROM post, plikestats
   WHERE post.pid = plikestats.pid
        AND plikestats.likestatus=1
   GROUP BY plikestats.pid
   //number of likes to that post

3) SELECT post.pid
        , post.pcontent
        , COUNT(comment.pid) AS commentcount
   FROM post, comment
   WHERE post.pid = comment.pid
   GROUP BY comment.pid
   //number of comments to that post.

So far I have tried using Joins in many way, variables, but nothing works out. If anyone can help with it please.

Try this out,

This should work, or at least get you on the right path of getting the results you are looking for... pay attention to how the tables are alias and how the joins are used as that is key for getting the proper results...

1)
SELECT P.PID
     , CONTACT(R.FNAME,' ',R.LNAME) AS FULL_NAME
     , P.PCONTENT
FROM REGISTRATION R
JOIN POST ON R.RID = P.RID

2)
SELECT P.PID
     , P.PCONTENT
     , COUNT(PL.PID) AS TOTAL_LIKES
FROM POST P
JOIN PLIKESTATS PL
ON P.PID = PL.PID
WHERE PL.LIKESTATUS = 1
GROUP BY P.PID
       , P.PCONTENT

3)
SELECT P.PID
     , P.PCONTENT
     , COUNT(C.PID) AS COMMENT_COUNT
FROM POST P
JOIN COMMENT C ON P.PID = C.PID
GROUP BY P.PID
       , P.PCONTENT


Is it worth indexing a varchar(32) column of UUIDS where half the UUIDS are v1 and half are v4? I have a MySQL InnoDB table with tens of millions of rows. Each row has uuid column. They are stored in the standard VARBINARY(16). I was recently asked to add a column to this table of VARCHAR(32) that stores the non-dashed UUID hex.

While doing this, I discovered that about half our UUIDs are v1, and the other half are v4. This means that for the first half, since they were all generated on a single machine, the randomness is a bit lacking (only the left-bytes timestamps are different). On the other hand, the newer half are basically completely random.

Is it worth putting an index on this column? If so, I'm struggling to decide how large that index should be (or maybe even what type of index).

Both v1 and v4 (or the mix) UUIDs will build a good B+ tree index for faster SELECTs.
From normalization point of view duplicating the column is a bad idea. But if we put this aside, and any SELECTs will benefit from the new index, and impact on writes is acceptable - why not?


ORACLE: performance of query running across two schemas We have a current production schema that stores all of our data. We're in the process of trying to integrate a different system to our system. They (vendor from 2nd system) proposed creating a 2nd schema on the same database that hosts our production schema. In that 2nd schema, they are going to migrate the data from 2nd system over and try to transform data so it's similar to our production system. Nothing changes in our production schema.

The plan is to create some reports that will query both the production schema as well as the tables from the 2nd schema (that contains transformed data from the other system). The report is going to be running from our original production system.

Here's one scenario:

Table STUDY is in both production schema (Schema A) and the 2nd schema (schema B). STUDY table in schema A contains live production data from our current system. STUDY table in schema B contains different data that were migrated from the other system. We create a report that should look into both STUDY table in schema A as well as Schema B.
Similar to scenario 1, but this time we're trying to join the STUDY table in both schema A & schema B. Generate a total STUDY report with data from both schemas.

Assuming STUDY table contains thousands of rows and its the header table, and there is multiple child / detail tables that links to STUDY table. Both schemas resides on the same oracle database instance, storage on the same server pretty much share the same infrastructure.

Questions I have:
- What is the performance hit / negative impact on our current production system (schema A)? should we expect a big performance hit if we're going about our business like normally (before having the 2nd schema)

What is the pros and cons of this approach? (don't touch the current production schema, put data from 2nd system into a 2nd schema)
What are some of the other things we have to consider when we plan to go with this approach? do we need to increase the memory used by Oracle, increase share pool, SGA etc?
What are the ongoing maintenance issues we could have for the Oracle database (with the addition of the 2nd schema)? We have oracle replication to DR database to sync a dark database every 10 minutes for current production system.
Any other downsides you can think of? (as I'm technical but certainly not a DBA or an oracle expert)

Briefly, such an approach appears to me as a valid first step in integration process.  As long as the second schema is used for querying only, all its objects can be moved to readonly tablespace thus minimizing impact on backup time.

You may or may not need to tune the instance, it all depends on how much load it adds to the database. For running reports, it should be safe enough to create resource profile (and use it) which limits CPU/Session time. Depends on your license, you can use Database Resource Manager which gives many more options to control resource consumption compared to profile.


DB2: Column-Aliases with Line-Breaks Using DB2 9.7, is it possible to produce a result set where the column-headers contain line-breaks? Something like:

select field_name as "Line1<br>Line2" from table_name

I want the column-header to look like this:

Line1
Line2

However, this is not for HTML. Instead I'm wanting it to look this way when I run the query (raw) in my sql editor (Dbeaver) and in the CSV file I export from Dbeaver (so that the columns are narrower -- to prevent horizontal scrolling while viewing the result-set).

DB2 will allow you to use a new line character in a quoted identifier:

$ db2 'select ibmreqd "foo
> bar" from sysibm.sysdummy1'

foo
bar
-------
Y

  1 record(s) selected.

The question is, will Dbeaver let you enter a new line character where you want it.


Denied access sending dbmail while impersonated under a login with sysadmin privilege This question is similar to but not the same as this.

I created a stored procedure and set it to execute under the context of a sysadmin login (let's called it sys1). For testing purposes, I only included an execute sp_send_dbmail statement in there:

create procedure temp_impersonation_test with execute as 'sys1'
as
exec msdb.dbo.sp_send_dbmail
    @subject = 'test',
    @body = 'test',
    @recipients = 'james.jiao@someplace.com'

Now here are some facts. Both sys1 and I are sysadmins on the server. Neither login has trouble running the standalone sp_send_dbmail statement. Neither login has trouble running the stored procedure when it is not set to execute under a different context.

However when the sp is set to execute under a different context, it fails with the following error. Note that this even happens when sys1 tries to execute it (effectively, it will try to impersonate itself).

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I have tried the usual things like re-attaching the database user to the server login for the current database context and for msdb (I actually had to create the user for msdb as it wasn't there before.), recreate the mail profile and putting both users in the databasemailuserrole in msdb.

I think it's worth it to note that this issue started happening after we moved to a new server with SQL 2008 R2. The code worked fine on the old server which ran SQL 2005.

I am baffled. How could this happen? Both accounts are sysadmins!

Update: I just tried this on some of our other database servers and this issue occurs on all of them regardless of the server version. I think there must be a database setting I've missed out somewhere!

When EXECUTE AS is used within a stored procedure to switch security contexts, it is sandboxed to the current database. This can be extended by using the database property TRUSTWORTHY or the server option cross database ownership chaining but there are security implications to those decisions.


What is the PREEMPTIVE_REENLIST wait type in Microsoft SQL Server 2008 R2 This is not urgent, just curiosity.

QUESTION:

I was trying to troubleshoot a slow server today, and I came across a high figure for a wait type called PREEMPTIVE_REENLIST. I'm not able to find this documented anywhere.
TOADWORLD seems to at least know it exists, but doesn't have any actual info.
Nothing on MSDN search or Stack Exchange search.
Nothing on Google except the TOADWORLD links.

Was just wondering if anyone here had encountered it and knew what it was all about.
Thanks.

PLATFORM:

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)   Jun 11 2012
  16:41:53   Copyright (c) Microsoft Corporation  Standard Edition
  (64-bit)  on  Windows NT 6.1  (Build 7601: Service Pack 1)
  (Hypervisor) (Windows Server 2008 R2 virtualized on VMWare)

This is not definitive proof but just my opinion based on what I learned from a PluralSight course Paul Randal did on wait stats.

PREMEPTIVE waits are indicative that SQL Server Operating System (SQLOS) has to change a thread to preemptive mode to handle something outside of SQL Server, at the OS level.

Now as you have already determined this types of waits are not documented much at all. There are a few you can see documented in BOL under the sys.dm_os_wait_stats DMV but it is not extensive. In most cases the calls being made to the OS are via Windows API calls. So what I ended up learning from the course is that if you take off the PREMEPTIVE portion of the PREEMPTIVE_REENLIST you end up with the name of the possible API being used: REENLIST. So if you take that and start searching around on the web you can try and deduce what it means.

So my deducing would be that I came across this MSDN article on IResrouceManager::Reenlist there are also other MSDN articles on similar named methods that all discuss a TransactionManager. It is not the content that catches my eye but the section of MSDN where this article is located: DTC Interfaces. So you don't specify this level of setup for your server but I would make a hunch that you are using distributed transactions. This can come from making remote calls to other instances/databases or if you are working on a clustered instance. If you actually read a statement from the MS DTC Service, although it is from SQL Server 2000 you might catch the key word that caught my eye:

  One or more servers running SQL Server can then be instructed to enlist in the distributed transaction and coordinate the proper completion of the transaction with MS DTC

Just my opinion...


How to filter a JOIN on *all* items of a given list (filter articles with all given tags for example) I have this use case which seems quite usual but I don't find any efficient and obvious solution.

I have an article table, a tag table and a intermediate article_tag table (each article can have multiple tags, and each tag can be linked to multiple articles).

Given a list of tag ids 1, 2, 3, I want to retrieve articles that have all these tags at the same time.

I easily managed to retrieve articles that have at least one tag in this list:

SELECT * FROM article a
JOIN article_tag at ON at.article_id = a.id AND at.tag_id IN (1, 2, 3)

This query can return articles with only 1 tag (id 2 for example). But this is not what I want. I want only the articles with the 3 exact tags 1, 2 and 3.

How to do it in an efficient way? There are quite a lot of articles, I do care about performances.

Thanks!

IMHO the best way to ensure it is by using EXISTS to check one by one every condition:

select a.*
from   __articles a
where  exists (select 1 from __articles_tags where artid = a.artid and tagid = 1)
and    exists (select 1 from __articles_tags where artid = a.artid and tagid = 2)
and    exists (select 1 from __articles_tags where artid = a.artid and tagid = 3);

Bu depending on your table schema, you could use GROUP BY and COUNT to select which articles have the desired tags.

select   artid
from     articles_tags
where    tagid in (1,2,3)
group by artid
having   count(*) = 3

Then use the result list to fetch articles records.

select  *
from    articles
where   artid in (select   artid
                  from     articles_tags
                  where    tagid in (1,2,3)
                  group by artid
                  having   count(*) = 3);

Check it here: http://rextester.com/LOB71207


How to update Column related to Row Number Is there any easy way to update Column by Row number not a PK
ex:

UPDATE contact m
SET    ContactNumber = sub.rn + 500
FROM  (SELECT Id, row_number() OVER (ORDER BY Id) AS rn FROM contact) sub
WHERE  m.Id = sub.Id;

UPDATE contact
SET ContactNumber = (@rownum := 1 + @rownum) + 500
WHERE 0 = (@rownum:=0)
-- for a given client, for example, Client_Id=123, use
-- WHERE Client_Id = 123 + (@rownum:=0)
ORDER BY id;


Calculating average with AVG for a list of numbers This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL
like:

SELECT 2+3+4

How can I calculate the average of 2, 3 and 4 without inserting them to a table first?

I have tried different kinds of variations like:

SELECT 2 AS foo UNION 3 AS foo UNION 4 AS foo
  foo
  integer
1 2
2 3
3 4

but:

SELECT AVG(SELECT 2 AS foo UNION 3 AS foo UNION 4 AS foo)

  ERROR:  syntax error at or near "select"

I just can't seem to find the right syntax. I'm on PostgreSQL at the moment if it matters.

The best solution depends on where and in what form the values come from.

unnest()

Use the set returning function unnest() with an array in the FROM clause. That's shortest for long lists, but it's a Postgres specific solution.

SELECT avg(n) FROM unnest('{2,3,4}'::int[]) n;

Which is short syntax for:

SELECT avg(n) FROM unnest('{2,3,4}'::int[]) AS n(n);

VALUES

Alternatively, use a VALUES expression as subquery. This is standard SQL.

SELECT avg(n)
FROM  (VALUES (2),(3),(4)) t(n);

UNION ALL

You can do the same with a much more verbose chain of SELECT ... UNION ALL SELECT .... Also standard SQL.

SELECT avg(n)
FROM  (
   SELECT 2
   UNION ALL
   SELECT 3
   UNION ALL
   SELECT 4
   ) t(n);

Use UNION ALL, not just UNION, which would fold duplicates (which you did not specify!) and is therefore also considerably more expensive.

SQL Fiddle.

Note that all numeric literals in the example are cast to integer automatically (just digits). For other data types you may have to add explicit type casts. avg() returns (per documentation):

  numeric for any integer-type argument, double precision for a
  floating-point argument, otherwise the same as the argument data type


Transfer of data (lots) from NAS based MariaDB to Postgres at AWS I have a bunch of remote autonomous data harvesting systems running, each collects approx 28,800 records over an 8 hour run.  The remote systems are Rasp Pi based, running Python, logging to MariaDB on a Synology NAS.  I need to pump the data up to my Postgres server that is collating all the data. To add to the mix, the remote systems are running off 4G connections that drop in and out on an irregular basis (sometimes 8hrs offline...) so I can't rely on a scheduled job to do the trick.  I have python scripts running that transfer each record on an individual basis as a straight insert (these check and handle loss of connection etc) but that is way way too slow.

The data is massaged on its way to Postgres at the minute, to add in local identifiers etc but they could be incorporated into the local db to make the tables compatible.  My obvious bottleneck is the 4G but there is nothing I can do about that, I figured there must be a much more efficient method than inserting each row, would a parameterised SP give a significant gain ? or a bulk table load from a local csv (with batched records e.g. 1000 at a time)...

Any pointers to routes to explore much appreciated, thank you

Update:
I had googled previously and couldn't find a solution that seemed appropriate but more digging yielded some ideas..
Well on the way to a solution: using Python StringIO to build a 'file' and the postgres copy_from functionality I can bulk load 500 records in under a second. Still need to 'tune' the batching and other bits but seriously faster, the data massaging can still happen in the file build so didn't have to mod the base tables.

Still interested in other routes as I'm sure this one may yet build me a brick wall to bang my head against... again.

Trying to do a fast and reliable insert over an intermittent connection will be a headache. I would:

Use something like rsync with compression to get your endpoint files up to a centralized, reliable server.
Once you believe the files are good, do a bulk load from that centralized location to the DB. You could then use the COPY command, a flat file foreign table wrapper, or get fancier with an ETL tool.


Log/Show number of (select) queries per table I used the following view to summarize some metrics in my db. Is there any way to log the select queries per specific table instead?

create or replace view _dba_query_stats as
select
  SUBSTRING(VARIABLE_NAME, 5) as query_type,
  VARIABLE_VALUE as total_count,
  round(VARIABLE_VALUE / ( select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status'), 2) as per_second,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60)))       as per_minute,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60)))    as per_hour,
  round(VARIABLE_VALUE / ((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status') / (60*60*24))) as per_day,
  FROM_UNIXTIME(round(UNIX_TIMESTAMP(sysdate()) - (select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status'))) report_period_start,
  sysdate() as report_period_end,
  TIME_FORMAT(SEC_TO_TIME((select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME = 'Uptime_since_flush_status')),'%Hh %im') as report_period_duration
from
  information_schema.GLOBAL_STATUS
where
  VARIABLE_NAME in ('Com_select', 'Com_delete', 'Com_update', 'Com_insert');

Slight optimization:

SELECT ...
    round(VARIABLE_VALUE / usfs.VARIABLE VALUE)
    ...
FROM information_schema.GLOBAL_STATUS
JOIN ( select VARIABLE_VALUE from information_schema.GLOBAL_STATUS
               where VARIABLE_NAME = 'Uptime_since_flush_status') AS usfs

That way, it will fetch Uptime_since_flush_status only once.

Turning on the "general log" is probably the only way to get more details.  And that would be quite messy, since you would have to deduce the "current database" for each query.


Modify MySQL foreign key related column without dropping foreign key relation I need to modify a foreign key related column. When I tried to alter it I got "Error on rename of 'x' to 'y' (errno: 150)" error. So I googled and found foreign key relation is the 'villain'. I dropped the foreign key relation, altered the column and created foreign key relation.

Is there any way to alter foreign key related columns without dropping the relation?

Yeah i think you have a SQL command like :

SET foreign_key_checks = 0;

which should be session based. Then you set =1 once you're done.


Oracle regular expression regexp_like I have simple question.
why would :

select regexp_like( (select 'https://www.hotmail.com' from dual) ,'(f|ht)tps?:') from dual ;

not work?

What do you expect this query to do?

regexp_like is a regular expression version of the LIKE statement so it makes sense to use it in the same sorts of places that you would use a LIKE.  You wouldn't try to directly SELECT the result of a LIKE statement.  You could, however, embed the regexp_like in a case statement.  For example

SQL> ed
Wrote file afiedt.buf

  1  select (case when regexp_like( 'https://www.hotmail.com' ,'(f|ht)tps?:')
  2               then 'true'
  3               else 'false'
  4           end) does_it_match
  5*   from dual
SQL> /

DOES_
-----
true

regexp_like is a function that returns a boolean.  Oracle SQL, however, does not support the boolean data type so you cannot directly SELECT the result of the function just like you couldn't SELECT a function you write that returns a boolean.


How to modify a Postgres tablespace location I am using a workstation that has 3 different hard drives. Some time ago I got a large database to reverse engineer that would not fit in the system drive. Therefore I created a new table-space in one of the the other hard drives, that at the time was mounted on the /media/Work1 folder.

Everything was working fine, until today at startup the system decided to mount the drive on the /media/Work folder. Naturally, Postgres can no longer access the databases in that table-space.

How can I change the location of a table-space in Postgres? I am aware of the pg_tablespace_location function, but not of somthing to modify it.

The tablespace location is defined by a symbolic link in the pg_tblspc directory (this is why, by the way, you can only use custom tablespaces on systems that have symlinks, as stated in the documentation):

~/9.5/main/pg_tblspc$ ls -l
total 0
lrwxrwxrwx 1 postgres postgres 13 Mar  5 10:33 132568 -> /tablespace2/

As user postgres, you can change the location this symlink points to:

postgres@local:~/9.5/main/pg_tblspc$ ln -snf /tablespace/ 132568

This will fix your tablespace location.  As a precaution, I'd do this only when the database is not running.

However, you have to be sure why the location changed in the first place and that it won't change back or to anything else.


Temporarily disable indexes before updating whole table For testing purposes I periodically restore production backup into a new database (AWS RDS PostgreSQL instance) with pg_restore. In the new database I run SQL queries that anonymize sensitive data. However, a query like below updates over 20M records in a table that has ~10 indexes (created during pg_restore).

UPDATE users SET email = users.id || '@example.com',
phone_number = NULL;

This takes a few hours on current setup.

To speed things up, I wanted to disable the table's indexes just for running the update and then enable them again and reindex the table. How can I do this? Any other ways to speed up this UPDATE?

Notes:

I've tried

UPDATE pg_index
SET indisready=false
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='users'
);

but got the error below.

ERROR:  permission denied for relation pg_index

Apparently you can't do this as superuser in AWS RDS PostgreSQL instance.

Dropping the indexes and then creating them again wouldn't be convenient, because I would have to know what columns they cover in advance. Ideally I would have a command that store existing index configuration in some temporary variable and then recreate them again using this info.

When you restore the data, use pg_restore --section=pre-data --section=data.  This will leave off the indexes.

Then run the UPDATEs you want to, then run pg_restore --section=post-data


Difference between fetched and returned tuples in postgresql I'm facing a hard time trying to figure it out some performance problems in my database. I'm using a bunch of resources online to learn what to monitor and how to interpret that information.

From the above, I'm unable to find a clear explanation of what is the difference between pg_stat_database.tup_returned and pg_stat_database.tup_fetched.

In pgAdmin4, there is a beautiful chart called "Tuples out" where these two concepts are contrasted, but I don't know how to interpret the info. In the official documentation only says that:

tup_returned: Number of rows returned by queries in this database
tup_fetched: Number of rows fetched by queries in this database

What exactly does "fetched" and "returned" mean?

I'm using postgresql 10.

If you do select count(*) from million_row_table, one million row wills be returned, but only one row will be fetched.

I can't see I've ever found these fields useful for diagnosing performance problems.  Find your slow query and do an EXPLAIN (ANALYZE, BUFFERS) of it.


Restore databases from prod to test server with data over X days We've got a list from app owners to restore say 20 databases from a total of 100 available production server databases onto a test server on weekly basis.

Moreover, they require it in a way that backup that would be occurring should contain data of last 60-90 days for all the above 20 databases.

I am not sure if that can be achieved. If yes, kindly give suggestions how can I go around this to automate the backups as said above.

For automating the restore process, should I go with Log Shipping or Transactional replication? We have space issues on test and the client does not want to invest much!

As already suggested, you cannot achieve this using backup/restore. You can also not achieve it using log shipping.

I would look at using transactional replication perhaps from views, which would define the 60 to 90 day window.

Be aware (or make your stakeholders aware) that replication is not free and comes with its own (usually small) performance overhead.


Formatting a DATE type in an Oracle database to only accept MMDD Is there a way to limit the inputs into a DATE type in an Oracle database to only accept inputs formatted with only the day and month (not including the year)?

No.  A date in Oracle will always have a year, month, day, hour, minute, and second.  You can choose to display only certain components of the date in your front end and to give a consistent default to the other components of the date.  It's pretty common, for example, to let the time component of the date default to midnight.  You could force the year to be some default as well that is never shown.  I'm not sure, though, where that would ever be useful.

Depending on what you are actually trying to represent, you may be better off storing the data as an interval.


How to return null values from a joined table if the matching row is missing? Here is what I need to achieve:

person_id   last_name   first_name  region_id   region name
  1         barnum        phineas      1        maricopa
  2         loman         willy        2        pima
  2         loman         willy        3        pinal
  2         loman         willy        4        santa cruz
  3         kay           mary         5        cochise
  3         kay           mary         6        gila
  3         kay           mary         7        graham
  4         lillian       vernon       NULL     NULL

Here are my tables:

Create database sales
use sales

create table if not exists
`Sales_People`
(`person_id` int primary key,
`last_name` char(16) not null,
`first_name` char(16) not null);
INSERT INTO Sales_people (`person_id`, `last_name`, `first_name`)
values
('1', 'barnum', 'phineas'),
('2', 'loman', 'willy'),
('3', 'kay', 'mary'),
('4', 'lillian', 'vernon');
create table if not exists
`Sales_Region`
(`region_id` int primary key,
`name` char(16) not null);
INSERT INTO Sales_Region (`region_id`, `name`)
Values
('1', 'maricopa'),
('2', 'pima'),
('3', 'pinal'),
('4', 'santa cruz'),
('5', 'cochise'),
('6', 'gila'),
('7', 'graham');

create table if not exists
`Sales_People_Region`
(`person_id` int not null,
`region_id` int not null,
constraint spr_pk primary key(person_id, region_id),
constraint spr_fk1 foreign key(person_id)
references Sales_People(person_id),
constraint spr_fk2 foreign key(region_id)
references Sales_Region(region_id));

INSERT INTO Sales_People_Region (`person_id`, `region_id`)
Values
('1', '1'),
('2', '2'),
('2','3'),
('2', '4'),
('3', '5'),
('3', '6'),
('3','7');

create table if not exists
`Sales` (`year` int not null,
`month` int not null,
`region_id` int not null,
`amount_sold` decimal(11,2),
constraint s_pk primary key(year, month, region_id),
constraint s_fk foreign key(region_id) references
Sales_Region(region_id));

INSERT INTO Sales (`year`, `month`, `region_id`, `amount_sold`)
Values
('2016', '01', '1', '800000'),
('2016', '02', '1', '850000'),
('2016', '03', '1', '990000'),
('2016', '01', '2', '425000'),
('2016', '02', '2', '440000'),
('2016', '03', '2', '450000'),
('2016', '01', '3', '200000'),
('2016', '02', '3', '210000'),
('2016', '03', '3', '220000'),
('2016', '01', '4', '50000'),
('2016','02', '4', '52000'),
('2016', '03', '4', '55000'),
('2016', '01', '5', '40000'),
('2016', '02', '5', '41000'),
('2016', '03', '5', '42000'),
('2016', '01', '6', '3000'),
('2016', '02', '6', '31000'),
('2016','03', '6', '32000'),
('2016', '01', '7', '20000'),
('2016', '02', '7', '21000'),
('2016', '03', '7', '22000');

Here is my query:

select sales_people.person_id, `last_name`, `first_name`, sales_region.Region_id,
trim(sales_region.`name`) AS 'Region Name'
 from `sales_region`
 inner join sales_people_region on sales_people_region.region_id = sales_region.region_id
  inner join sales_people on sales_people_region.`person_id` = sales_people.`person_id`
group by  sales_region.region_id, sales_people.person_id
having sales_people.person_id >= ''
order by sales_people.person_id, sales_region.region_id asc;

What I get:

person_id   last_name   first_name  Region_id   "Region Name"
1       barnum      phineas 1       maricopa
2       loman       willy       2       pima
2       loman       willy       3       pinal
2       loman       willy       4       "santa cruz"
3       kay     mary                5       cochise
3       kay     mary                6       gila
3       kay     mary               7        graham

I can't get it to let me see the last person_id because they have no region_ids assigned to them. In the table sales_people_region region_id is a primary key and therefore not able to be null.
If I query sales_people and group by person_id she does come up.

did you try outer join?

select sales_people.person_id, last_name, first_name, sales_region.Region_id, coalesce(trim(sales_region.name),'NULL')  AS 'Region Name'
from sales_region
right outer join sales_people_region on sales_people_region.region_id = sales_region.region_id
right outer join sales_people on sales_people_region.`person_id` = sales_people.person_id
group by  sales_region.region_id, sales_people.person_id
having sales_people.person_id >= ''
order by sales_people.person_id, sales_region.region_id asc;

`


About creating a View With additional coumns (that are not in the table) I have this view with following definition.

create view VIEW_IRS1 as Select
MBR.PRSN_MBRSH_ID
, Atr.Ssn_Tx
, Names.First_Na
, Names.Middle_Na
, Names.Last_Na
, Reff.Ref_Cd_Desc_Tx Suffix_Na
,'FALSE' AS [Request Title II Monthly Income Verification]

From Hix.T_Enrt En
Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')

Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id

Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id

Left Outer Join
(
    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
    where na.prsn_name_id = nAc.prsn_Name_Id
) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id

Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id

Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id

Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID=Pe.PRSN_ENRT_ID;

I want to create a new view (not alter this view) with an additional column name say "ABC" whose value should always be FALSE. How can I do that?

Can you not just create a new view that is

SELECT *, 0 AS "ABC"
FROM VIEW_IRS1

Or if you mean the text 'False'

SELECT *, 'FALSE' AS "ABC"
FROM VIEW_IRS1

Or to edit the existing view:

SELECT
    MBR.PRSN_MBRSH_ID
    , Atr.Ssn_Tx
    , Names.First_Na
    , Names.Middle_Na
    , Names.Last_Na
    , Reff.Ref_Cd_Desc_Tx Suffix_Na
    , 'FALSE' AS "My Really Long Name"
FROM
    AllYourOtherTables


getting "table is full" error even if the table size is much smaller than tablespace Currently I am using MySQL NDBCluster 7.3.5.
I have created one UNDOFILE hhme.dat with INITIAL_SIZE=200m.

Also one tablespace hhmefep_tbs with DATAFILE hhmefep_tbs.dat having INITIAL_SIZE=500M ENGINE=NDB.

Also the output of query is:

mysql> select FILE_NAME,TOTAL_EXTENTS,FREE_EXTENTS,EXTENT_SIZE, extra  from information_schema.FILES where TABLESPACE_NAME='hhmefep_tbs' and FILE_TYPE="DATAFILE";
+-----------------+---------------+--------------+-------------+----------------+
| FILE_NAME       | TOTAL_EXTENTS | FREE_EXTENTS | EXTENT_SIZE | extra          |
+-----------------+---------------+--------------+-------------+----------------+
| hhmefep_tbs.dat |           500 |           21 |     1048576 | CLUSTER_NODE=1 |
| hhmefep_tbs.dat |           500 |           21 |     1048576 | CLUSTER_NODE=2 |
+-----------------+---------------+--------------+-------------+----------------+

Database total size is 32MB and all tables are using ENGINE=NDB.

Still I am getting "table is full" error when I perform inserts.

Can anyone point out the issue here since 500MB of tablespace is not yet exhausted.

Why am I getting free_extents = 21 ?

Edit:
The DDL for creating this DB schema is as given below:

mysql> CREATE LOGFILE GROUP hhme ADD UNDOFILE 'hhme.dat' UNDO_BUFFER_SIZE=16M INITIAL_SIZE=200m ENGINE=NDB;
Query OK, 0 rows affected (9.70 sec)

mysql> CREATE DATABASE hhmefep;
Query OK, 1 row affected (0.05 sec)

mysql> CREATE TABLESPACE hhmefep_tbs ADD DATAFILE 'hhmefep_tbs.dat' USE LOGFILE GROUP hhme INITIAL_SIZE=500M ENGINE=NDB;
Query OK, 0 rows affected (24.30 sec)

[root@mgmtserver ~]$ ndb_mgm -e "all report mem"
Connected to Management Server at: localhost:1186
Node 1: Data usage is 16%(2723 32K pages of total 16384)
Node 1: Index usage is 9%(1605 8K pages of total 16416)
Node 2: Data usage is 16%(2723 32K pages of total 16384)
Node 2: Index usage is 9%(1605 8K pages of total 16416)

Edit2: Output of query suggested by @user3420095:

mysql> select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra
    -> from information_schema.FILES
    -> where TABLESPACE_NAME='hhmefep_tbs'
    -> and   FILE_TYPE="DATAFILE";
+-----------------+------------+----------------+
| FILE_NAME       | bytes_free | extra          |
+-----------------+------------+----------------+
| hhmefep_tbs.dat |   22020096 | CLUSTER_NODE=1 |
| hhmefep_tbs.dat |   22020096 | CLUSTER_NODE=2 |
+-----------------+------------+----------------+

Hope it clears confusion.

First of all, are you creating your tables with

CREATE TABLE table_name TABLESPACE hhmefep_tbs STORAGE DISK ENGINE NDBCLUSTER;

or just

CREATE TABLE table_name ENGINE=NDBCLUSTER;

First one is to create table to stores table data on disk, the second, which is default mysql cluster theme and the reason why mysql cluster exist, stores data in memory.

So if you store your data in memory, you should run the command on management node, ( use ndb_mgm command to enter ndb_mgm console)

ALL REPORT MEMORY USAGE

and check if your memory usage is full or not.

Also note that when you use disk tablespaces on mysql, a varchar(100) variable will reserve all the space like you have 100 characters in all rows of the column.

This will be more accurate for you to use to check if TS is full or not.

select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra
from information_schema.FILES
where TABLESPACE_NAME='hhmefep_tbs'
and   FILE_TYPE="DATAFILE";


count aggregate between three date ranges I have a mysql query where I want 3 columns of data. day0 to be between now() and - INTERVAL 24 hours
day1 between end of day0 - 48 hours
and day2 between end of day1 and 72 hours

This is what I have so far.

select distinct nc.flowstepname,
       count(distinct(nc.id)) as day0,
       count(distinct(nc.id)) as day1,
       count(distinct(nc.id)) as day2
from sparq.nc nc
where nc.flowstepname like "1%"
    and nc.created > NOW() - INTERVAL 72 HOUR
group by nc.flowstepname ASC;

Like this:

select distinct nc.flowstepname,
       (select count(distinct(nc.id))
        from sparq.nc nc
        where nc.flowstepname like "1%"
        and nc.created = now() - interval 24 hour) as day0,
       (select count(distinct(nc.id))
        from sparq.nc nc
        where nc.flowstepname like "1%"
        and nc.created = interval 24 hour - interval 48 hour) as day1,
       (select count(distinct(nc.id))
        from sparq.nc nc
        where nc.flowstepname like "1%"
        and nc.created = interval 48 hour - interval 72 hour) as day2
from sparq.nc nc
group by nc.flowstepname ASC

You could use COUNT(DISCTINCT(CASE ... WHEN

I've set up next example:

CREATE TABLE nc (id int, flowstepname varchar(20), created datetime);

INSERT INTO nc VALUES
(1,  '1152', NOW() - interval 20 hour),
(1,  '1152', NOW() - interval 21 hour),
(2,  '1152', NOW() - interval 22 hour),
(1,  '1152', NOW() - interval 31 hour),
(1,  '1152', NOW() - interval 32 hour),
(1,  '1152', NOW() - interval 34 hour),
(2,  '3154', NOW() - interval 35 hour),
(2,  '1152', NOW() - interval 40 hour),
(1,  '1152', NOW() - interval 49 hour),
(1,  '1152', NOW() - interval 52 hour),
(2,  '4152', NOW() - interval 60 hour),
(3,  '1152', NOW() - interval 65 hour),
(4,  '1152', NOW() - interval 66 hour);

select nc.flowstepname,
       count(distinct(case when nc.created >= (now() - interval 24 hour) then nc.id else null end)) as day0,
       count(distinct(case when nc.created < (now() - interval 24 hour)
                            and nc.created >= (now() - interval 48 hour) then nc.id else null end)) as day1,
       count(distinct(case when nc.created < (now() - interval 48 hour)
                            and nc.created >= (now() - interval 72 hour) then nc.id else null end)) as day2
from   nc
where  nc.flowstepname like "1%"
and    nc.created > NOW() - INTERVAL 72 HOUR
group by nc.flowstepname ASC;

flowstepname | day0 | day1 | day2
:----------- | ---: | ---: | ---:
1152         |    2 |    2 |    3

dbfiddle here


Faster way of upgrading postgres to 10 from 9.5 I am trying to test the migration of postgres to 10 from 9.5

On trying in a test DB of size 490 GB in one postgres node it is taking about 18 mins. The command I used

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.5/bin \
   -B /usr/lib/postgresql/10/bin \
   -d /var/lib/postgresql/data/postgresql0 \
   -D /var/lib/postgresql/10/data/postgresql0 \
   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
   -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k

Is there any faster way of doing this?

I used a script for loading junk data in postgres. It created a lot of databases (about 500). So this was causing pg_dump to take so much time.

After populating only the relevant database (about 100 GB) I am seeing good performance (about 5 second with -k option).


Is it possible to grant role privileges to a user by document (not collection) in MongoDB? I am working on a database for investigation purposes. I want to make some of that information public but other pieces of information are highly confidential.

I want to create a user and grant access only to the public information inside a document of a collection. For example in my collection "people", i want to show the name but not the phone number:

{
   "_id" : ObjectId("5910e7abce41fe759ebb1720"),
   "name" : "name",
   "phone" : "999999999"
}

How can i achieve this? I only found how to grant User-Defined Roles by collection.

db.createRole(
{
 role: "myClusterwideAdmin",
 privileges: [
   { resource: { cluster: true }, actions: [ "addShard" ] },
   { resource: { db: "config", collection: "" }, actions: [ "find", "update", "insert", "remove" ] },
   { resource: { db: "users", collection: "usersCollection" }, actions: [ "update", "insert", "remove" ] },
   { resource: { db: "", collection: "" }, actions: [ "find" ] }
 ],
 roles: [
   { role: "read", db: "admin" }
 ]
},
{ w: "majority" , wtimeout: 5000 }
)

Should i create a "public information" collection or is there a better way?

No, you cannot grant rights for document... But you can do views what shows only that public information.


Rowversion database lock performance As the documentation says, rowversion data type is auto-updated every time a row is updated using a global auto-incremented number within the entire database scope.

My question is the following: Say I have 10000 concurrent updates on tables that have a rowversion column. Since all try to access and update atomically that global number, I'd imagine that all of them are processed in a 'serial' way which may reduce performance compared to tables that doesn't need to access that number.

Is this correct?.

I'd imagine that all of them are processed in a 'serial' way which may reduce performance compared to tables that doesn't need to access that number.

In theory, yes. In practice, you would have to measure the effect to know if it is important.

The current rowversion value is cached in memory and atomically updated using a cmpxchg instruction (in DBTABLE::GetNewDbTimestamp). Any collisions due to multiple sessions requesting a new rowversion value concurrently are handled by a spinlock (type DBTABLE in sys.dm_os_spinlock_stats). Monitoring backoffs and sleeps in that DMV is probably the first step to see if it might be causing a problem.

Compared with the extensive work needed to actually insert or update a row, maintaining the timestamp value is probably pretty trivial. The only way to really be sure though would be to test it on a suitable workload inserting or updating many tables with rowversion columns concurrently.

As a quick test, I ran two sessions inserting 500,000 rows with a single integer value into a table variable with a rowversion column. The batch was repeated 100 times on each connection for a total of 50,000,000 inserts each.

SET NOCOUNT ON;
DECLARE @T AS table (i integer, rv rowversion);
INSERT @T (i) SELECT N.n FROM dbo.Numbers AS N WHERE N.n BETWEEN 1 AND 500000;
GO 100

Typical results were that execution took 45 seconds with:

With a binary(8) column default 0x, the same test runs for 15 seconds.

So there is definitely a cost associated with rowversion. I do doubt this would often be a major cause of problems though. Maybe if a large number of tables has rowversion columns and were all being inserted/updated at a high rate concurrently.

I suppose if natively compiled memory optimized tables ever support rowversion, we might get closer to the time when this becomes a real bottleneck with non-durable tables, but maybe not even then. Identity and sequences use a similar mechanism, and I have not heard any reports about that becoming a problem specifically.

Some related reading:

Why does @@dbts increase after backup/restore?
Diagnosing and Resolving Spinlock Contention on SQL Server
sys.dm_os_spinlock_stats.backoffs keeps increasing with no sqlos.spinlock_backoff event


Is it possible to reuse an alias more than once in MySQL query? Is there a way to reuse an alias more than once in MySQL? I need to CONCAT several aliases into a new column (Ref). The values from columns Option, Make, and Model have been formulated earlier in my query using CASE statements. The Ref column would CONCAT Qty, Option, Make and Model. I'm running MySQL version 5.7.29.
Thank you in advance!

`Qty` | `Option` | `Make`| `Model` | `Ref`
******************************************************
1     | Red      | VW    |         | Red / VW
2     | Blue     | POR   |  G4     | 2x Blue / POR  G4

The goal is to re-use an alias (e.g. Make) from the CASE statement

CASE
  WHEN `order_product`.`name` LIKE '%Volkswagen%' THEN 'VW'
  WHEN `order_product`.`name` LIKE '%Volvo%' THEN 'VOL'
  ELSE `order_product`.`name`
END AS `Make`,

Assume there is some query which produces the output:

SELECT {expression1} AS `Qty`,
       {expression2} AS `Option`,
       {expression3} AS `Make`,
       {expression4} AS `Model`
FROM {tableset}
WHERE {conditions}

For to obtain the result which you need you must simply usethis query as a subquery:

SELECT `Qty`, `Option`, `Make`, `Model`,
       CONCAT ( CASE WHEN `Qty` > 1
                     THEN CONCAT(`Qty`, 'x ')
                     ELSE ''
                     END,
                `Option`,
                ' / ',
                `Make`,
                CASE WHEN COALESCE(`Model`, '') != ''
                     THEN CONCAT(' ', `Model`)
                     ELSE ''
                     END ) AS `Ref`
FROM ( SELECT {expression1} AS `Qty`,
              {expression2} AS `Option`,
              {expression3} AS `Make`,
              {expression4} AS `Model`
       FROM {tableset}
       WHERE {conditions} ) AS subquery

fiddle


What happens when there's no tempdb space for version store operations? If tempdb runs out of space, do transactions that need version store space fail, or do they wait for space to be freed?

A transaction may get rolled back if the version store needs space and runs out. It won't wait for space in the version store, and really you wouldn't want it to wait.

Typically the version store is used by transactions with isolation levels that use optimistic locking (Snapshot isolation or read committed snapshot isolation for example). The intent in using these is usually to prevent readers from blocking writers in highly transactional databases, but if a writer has to wait for version store space to be freed it could cause extensive blocking of other writers and could conceivably become endlessly blocked. Instead, these transactions are rolled back.

The following errors arise from running out of tempdb space for the version store:

3959 - Version store is full. New version(s) could not be added. A transaction that needs to access the version store may be rolled back. Please refer to BOL on how to configure tempdb for versioning.
3966 - Transaction is rolled back when accessing version store. It was earlier marked as victim when the version store was shrunk due to insufficient space in tempdb. This transaction was marked as a victim earlier because it may need the row version(s) that have already been removed to make space in tempdb. Retry the transaction
3967 - Insufficient space in tempdb to hold row versions.  Need to shrink the version store to free up some space in tempdb. Transaction (id=%I64d xsn=%I64d spid=%d elapsed_time=%d) has been marked as victim and it will be rolled back if it accesses the version store. If the problem persists, the likely cause is improperly sized tempdb or long-running transactions. Please refer to BOL on how to configure tempdb for versioning.

As described in the error message, 3967 indicates that the version store has run out of space in tempdb and a specific transaction has been marked as the victim. This transaction will be rolled back if it hits the version store. If the open transaction does not modify any additional rows, requiring rows to be added to the version store, then it isn't rolled back.

3966 indicates a marked transaction (from 3697) has tried to access the version store and was subsequently rolled back.

3959 indicates that tempdb doesn't have enough space to hold the version store but there hasn't been a transaction marked as a victim as yet (meaning tempdb is out of space but no transactions tried to access the version store). Existing transactions would be unaffected but new transactions that need the version store would be rolled back.


How can i improve my count(distinct id) query in mysql laravel 4.2 I am using three join and fetching more than 4 million records for now it is 1.5 million.

Below is my query, can you please take a look?

SELECT count(distinct enrollments.userid) AS aggregate
FROM users
INNER JOIN enrollments ON users.userid = enrollments.userid
INNER JOIN classes ON enrollments.classid = classes.classid
INNER JOIN assigned_courses ON assigned_courses.courseid = classes.courseid
WHERE assigned_courses.`oneroster_permission_id` = 147

Here is the explanation of my current query.

See if this gives you the right answer:

SELECT  COUNT(DISTINCT userid) AS aggregate
    FROM  enrollments AS e
    WHERE  EXISTS
      ( SELECT  *
            FROM  classes AS c
            JOIN  assigned_courses AS ac ON ac.courseid = c.courseid
            WHERE  ac.oneroster_permission_id = 147
              AND  c.classid = e.classid
      ) AS x

Note:  I tried to avoid the DISTINCT by avoiding any JOINs.  JOINs tend to explode the number of rows, only to force you to use GROUP BY or DISTINCT to get back to what you wanted.  But, I guess there could be multiple occurences of a userid in enrollments.

No matter how it is written, I think the performance will be poor.  Notice how the query needs to come at it from two sides, one to filter on 147, the other to connect to enrollments.classid.