what are the different ways to specify the values in PostgreSQL? I have a question regarding the value initialization

In query tool:

Insert into tablename(field1, field2, field3..)
values (value1, value2, value3..);

In my JavaScript code, I am using:

db.query({ text: 'INSERT INTO tablename(field1, field2, field3)
    VALUES ($1, $2, $3)', values: [value 1 , value 2 , value 3'] }).then(...)

is this a good practice?

select * from tablename where _id="+[value]+" AND field1 = "+[value]+";

I have listed 3 query above. As per my knowledge 1 & 2 are right as I tried them in many of my projects. Can someone clarify is the option 3 is also a correct method? Does it match the standards?

If the values are obtained from a user input, 2) and 3) are a bad idea.

You shouldn't put user supplied values directly into the SQL string, but use a prepared statement with bind variables. Not doing that, makes your application vulnerable to SQL Injection

How you use a prepared statement with bind variables depends on the programming language you are using.


Index for ORDER BY on aggregate in MySQL I'm working with the following query:

SELECT *, SUM(entries_count) AS entries_count FROM leads
WHERE promo_id IN (1, 2, 3)
GROUP BY user_id
ORDER BY entries_count DESC
LIMIT 25 OFFSET 100;

It's quite fast without the ORDER BY clause, but very slow with it. I have a few compound indexes, but it only seems to want to use the index on user_id and that's it. Here's the EXPLAIN output:

| 1 | SIMPLE | leads | index | index_leads_on_promo_id_and_user_id,index_leads_on_promo_id | index_leads_on_user_id | 4 | NULL | 219647 | Using where; Using temporary; Using filesort |

I find it a bit odd that it's using a key which isn't even listed under "possible keys". Anyways, is there any way to provide a better index to improve this query, or will I need to denormalize with pre-calculated rollups?

EDIT: Here's the CREATE TABLE statement:

CREATE TABLE `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `promo_id` int(11) NOT NULL,
  `entries_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_leads_on_promo_id_and_user_id` (`promo_id`,`user_id`),
  UNIQUE KEY `index_leads_on_user_id_and_promo_id` (`user_id`,`promo_id`),
  KEY `index_leads_on_user_id` (`user_id`),
  KEY `index_leads_on_promo_id` (`promo_id`),
  KEY `index_leads_on_user_id_and_entries_count` (`user_id`,`entries_count`),
  KEY `index_leads_on_user_id_and_promo_id_and_entries_count` (`user_id`,`promo_id`,`entries_count`)
) ENGINE=InnoDB AUTO_INCREMENT=413235 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

As you can see, I've added several indexes just to experiment and see if they'd improve the query's performance, but so far no dice.

I have one suggestion, and if that doesn't work, then you're probably going to have to denormalize it.  There's no way to optimize a sort of the result of an aggregate function, because the entire set has to be calculated and sorted by the ORDER BY before a LIMIT ... OFFSET can be applied.

The database is theoretically free to return any of the possible valid rows to satisfy the LIMIT ... OFFSET when there's no explicit ordering in the query.  Some would argue that the existence of OFFSET isn't even a legitimate concept in a purist sense of the relational model, since tuples aren't ordered in a relation... but I digress.

You're limited on how an index can help you, primarily because you're doing GROUP BY one thing followed by ORDER BY something different -- you're already twisting the data in two directions, so even if the query were less complicated but had these two conflicting requirements, you'd have this same problem.

To explain your EXPLAIN (you might want to add the headers from the explain, to your question).

type index this is the first potentially-confusing piece of info; this is called the join type, and index here doesn't mean the query is using an index in the way you might expect; in fact, it has two different meanings, both of which are essentially full table scans with more or less help from an index, but not for lookups.  In this case, since Extra doesn't say Using index, the optimizer has chosen 'index_leads_on_user_id' (as shown in key) so that it can do the full table scan in index order -- reading the rows from the table in the order that the rows appear in that index... so it's reading from your table in "group by" order with the help of that index (on user_id).

possible_keys are the keys the optimizer found potentially viable for finding the records.  It notices both "index_leads_on_promo_id_and_user_id" and "index_leads_on_promo_id" because "promo_id" is the leftmost column in each of those indexes, so they could have been used to filter out only the 3 values for promo_id that you needed.  The optimizer, though, has abandoned this route, having apparently determined that the ability to read the rows in GROUP BY order would be more efficient than plucking out the matching rows and then having to sort the result for the purpose of grouping.

key is the index MySQL actually selected, for reasons I mentioned above.  Since the selected one couldn't be used to filter rows, it's not listed among possible_keys.

key_len 4 is number of bytes from the left side of the selected index that the optimizer will actually consider.  With a multi-column index, the first non-useful column counting from the left elimiates the use of any columns to the right; for a single column index on an INT, here, all 4 bytes are used.

ref NULL indicates that no column or value is being compared to the selected key.

extra: Using where because the WHERE clause will have to be used to remove all of the rows because we're doing a full table scan that will return more rows than are needed; Using temporary because a temporary table is needed to hold the GROUP BY result so that it can be sorted into a different order; Using filesort is how the server will store all of the useful records not filtered by the where clause.

The bottom line here is the that query seems about as well-optimized as it can be.

Also, the query itself is also not entirely logically valid.  It's syntactically correct enough that MySQL will run it, but it's ambiguous and may be causing extra work for the server and it could be distracting the optimizer.

SELECT *, SUM(entries_count) AS entries_count FROM leads
...
ORDER BY entries_count DESC

You can't SELECT * ... which includes a column called entries_count ... and then another column that you're aliasing AS entries_count... and then ORDER BY the ambiguous name entries_count and expect to get an unambiguous result.

Tested on MySQL 5.1 and 5.5, I find that this query sorts by a random value from the physical column and not by the SUM() result, and returns two different columns, both called "entries_count" in the result set, which means, at minimum, the server is having to manipulate extra data that you can't use.  This is not valid and you should definitely try explicitly listing the columns you want, excluding the physical column entries_count, which is going to be a meaningless value in light of the GROUP BY, and then, keep it unambiguous at the end:

ORDER BY SUM(entries_count);


This is a gaps-and-islands problem, and in this specific case you can solve it using a method that involves two ROW_NUMBER calls to identify distinct "islands" of consecutive rows belonging to the same Fruit:
WITH
  partitioned AS
  (
    SELECT
      Fruit,
      FruitTime,
      ROW_NUMBER() OVER (                   ORDER BY FruitTime ASC) -
      ROW_NUMBER() OVER (PARTITION BY Fruit ORDER BY FruitTime ASC) AS IslandID
    FROM
      YourTable
  )
SELECT
  Fruit,
  MIN(FruitTime) AS StartTime,
  MAX(FruitTime) AS EnfTime
FROM
  partitioned
GROUP BY
  Fruit,
  IslandID
ORDER BY
  StartTime ASC
;

Demo at dbfiddle.uk: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=f7402a206a9f12afec40ce242b2bca92


Why is there a difference between these transaction DMVs in SQL Server 2008 R2? When I execute the two queries below,

SELECT
    session_id, transaction_id
    FROM sys.dm_tran_session_transactions;

AND

SELECT
    session_id, request_id, at.transaction_id
    FROM sys.dm_tran_active_transactions at
        JOIN sys.dm_exec_requests r
            ON r.transaction_id = at.transaction_id;

I've read the BOL for both 1 and 2 but don't see any clear explanation as to why the difference would occur.

I get different results. The former query returns no results, but the latter returns active transactions with session and transaction ids. The request_id is 0 which, I think, means that it's the only request made by the session. Could someone help me understand why there is a difference between the two concepts I've queried above?

EDIT

I just reran the queries and now I get a result for the first DMV which has a session_id that is not actually contained in the second result set.

It appears that sys.dm_tran_session_transactions does not include auto-commit transactions:

-- No result
SELECT
    session_id,
    transaction_id
FROM sys.dm_tran_session_transactions;

Using an explicit transaction does return results:

BEGIN TRANSACTION;

    -- Row returned
    SELECT
        session_id,
        transaction_id
    FROM sys.dm_tran_session_transactions;

ROLLBACK TRANSACTION;

The DMVs are views on internal structures, and the documentation is not often as comprehensive as in other areas. Some of that may be because it would be inconvenient to go through a complete deprecation cycle each time a change in DMV behaviour occurs, but it's likely just an oversight in this case. You could report the documentation deficiency on Connect.

Adam Machanic found all sorts of odd little behaviours with the DMVs while writing his sp_WhoIsActive tool. If it suits your purposes, you could use that rather than trying to write your own monitoring queries.


Logical Drives - I/O aside, does SQL Server care? I'm one of those Accidental DBAs. Engineer at a medium sized company who worked for years to work without a DBA. We've since hired some (at my recommendation) and they're great but I'm trying to find the answer to a conversation I'm having with one of them regarding storage.

Here's the high level question: We're on an enterprise level EMC SAN a large shared pool of mixed drives for SQL Server and we tend to have good I/O performance. Data, Logs, and tempdb all exist on "S:".

So, does SQL Server "care" or benefit in some way from having multiple logical drives for it's different data types? I'm currently operating under the assumption that the answer is "No" but the DBA seems to think that SQL Server makes "better decisions" with separate drive letters, though he can't give me an example at the moment.

I can see the benefit from a storage space management perspective and of course if you give a logical volume it's own spindles on the San you might see some performance ... but simply giving SQL Server a new drive letter to work with?

If you are able to segregate drives so they use dedicated communication channels to the drive (D: has it's own channel, E: has a separate channel, etc.), then I believe you may be able to achieve some gains.  If the different drives all go through the same channel to the storage then I don't think it will have an impact.

Whether or not you can segregate the drives depends on your environment (VM/Physical, storage, host, etc.)


ORA-1033: ORACLE initialization or shutdown in progress I am stuck with a little Oracle problem. My PC was shut down improperly after my graphics card freaked out, which caused Oracle 11g Express to not properly close down. This means that I get the above error when I try to connect. My PC has been on for 3 hours, and I still get the error, so it is safe to assume that there's something wrong.

The usual way of remedying the problem is to use the following sequence of commands from the command line:

sqlplus /nolog
SQL> connect / as sysdba
SQL> shutdown abort
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;

However, when I give the shutdown command, I get the error

  ORA-12560: TNS:protocol adapter error

So now, I can't mount my db. ANy help would be appreciated.

For your database to open and mount, first ensure that you are logged in as the user oracle
Then start by checking whether your listener has been started, using the command
lsnrctl status

Also, please provide the details of the environment in which you are running Oracle, i.e as an active/standby in dataguard, or standalone, and also what version of Oracle you are using. Also check your alertlog /opt/oracle/admin/databasename/bdump/alert_databasename.log if there are any clues here.


SQL Server 2008 DBCC Problems We have a database Ms
DBCC CHECKDB (MS)

Or
ALTER DATABASE MS SET SINGLE_USER
DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MS SET MULTI_USER

Error message

Msg 0, Level 11, State 0, Line 0:
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0:
A severe error occurred on the current command.  The results, if any, should be discarded.

After spending hours and testing each table, the problematic table was found "LC"
DBCC CHECKTABLE(LC)

Error Message

Msg 211, Level 23, State 51, Line 1:
Possible schema corruption. Run DBCC CHECKCATALOG.

Now,
DBCC CHECKCATALOG

Message

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I also tried to have a look on indexes
SELECT * FROM sysindexes

Error Message

Msg 211, Level 23, State 51, Line 1:
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0:
A severe error occurred on the current command.  The results, if any, should be discarded.

Again,
SELECT * FROM LC

Or
DROP TABLE LC

Error Message

Msg 211, Level 23, State 51, Line 1:
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0:
A severe error occurred on the current command.  The results, if any, should be discarded.

I would not mind data loss for that particular table, I want to have the table dropped. Any ideas, please help!

You could RESTORE and never use REPAIR_ALLOW_DATA_LOSS (the article is worth reading)! Seriously, no amount of DBCC voodoo will bring your database back to a pristine, working state.


I would like to query a range of criteria on multiple columns in MySQL I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :

We have 3 columns as "Fast" "Smart" "Sharp" and they are of type boolean/int
I have 1000 records in the table, as an example, I list 5 here.

Fast  |  smart  |  Sharp
------------------------
0         1          1
1         1          1
0         0          1
1         1          1
1         0          0

and let's say we wanna search for people who are smart and sharp as 0  1  1  but if there
is no record with the value of 0  1  1 we want to get the closest to it which is  1  1  1
or 1  0  1 or etc. So now when I search for it, I get the exact value, if the value doesn't
exist, I won't get anything back from DB. So how can I achieve this and if I get a bunch of
result, how can I sort them from the closets to my query to the feartest ?

Assuming you want to match against 1 1 1 you could do something like this:

set @fast:=1;
set @smart:=1;
set @sharp:=1;

SELECT if(`fast`=@fast,1,0) + if(`smart` =@smart,1,0) + if(`sharp`=@sharp,1,0) as score FROM `table` order by score DESC

That should return the exact matches with score 3, people with 2 matches with score 2 and so on.


User can't see database in SQL Server 2008 R2 I took a backup of a SQL Server database and restored it as a new database on a different server.

On this new instance I have done the following:

recreated the login,
associated the login with the db user using sp_change_users_login 'UPDATE_ONE', 'username', 'username'
ensured the users default schema was dbo.
ensured the users was a member of the db_readdata role.
checked to make sure the user showed up in sys.dabase_principals
granted the user View Definition
granted connect to the user.

When I log in as the user I still do not see the database.  They can see other databases but not this one.

What else is there to check?

I ran EXEC sp_change_users_login 'Report' and this shows no output however running autofix, or update_one appear to run successfully.  The user also shows up under security>Users.

I am using SQL Server 2008 R2 Developer Edition.

I went through the sames steps as you described.  Restoring a database from one server to another and then fixing the user.  I didn't give any additonal permissions though, as you did with GRANT VIEW DEFINITION.

If however, I execute DENY VIEW ANY DATABASE to PUBLIC on Instance B, myuser can no longer see mydatabase in SSMS, but he can connect to mydatabase and run queries either way.

Without DENY VIEW ANY DATABASE , myuser on Instance B, was able to see the restored database.  I then used the opposite statement GRANT VIEW ANY DATABASE TO public to give that permission back.

Is there anything here in this test that you did that I didn't?

Is the user on the server with the restored database still able to connect to that database and run queries?

On Instance A

-- Create the mydatabase database

CREATE DATABASE mydatabase
CREATE TABLE mytable(t INT)
INSERT INTO mytable (t) VALUES (1),(2)

  create the login myuser, with default schema dbo and is member of
  db_datareader.

USE [master]
GO
CREATE LOGIN [myuser] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[mydatabase], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [mydatabase]
GO
CREATE USER [myuser] FOR LOGIN [myuser]
GO
USE [mydatabase]
GO
ALTER USER [myuser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [mydatabase]
GO
EXEC sp_addrolemember N'db_datareader', N'myuser'
GO

-- Backup the database

BACKUP DATABASE mydatabase to disk = 'C:\MSSQL\Backup\mydatabase.bak'
GO

On Instance B

-- restore the database to instance B

RESTORE DATABASE mydatabase FROM DISK = 'C:\MSSQL\BACKUP\mydatabase.bak'
WITH FILE=1,
MOVE 'mydatabase' TO 'C:\MSSQL\mydatabase.mdf',
MOVE 'mydatabase_log' TO 'C:\MSSQL\mydatabase_log.mdf',
RECOVERY, NOUNLOAD, STATS=10

-- recreate the login,

CREATE LOGIN [myuser] WITH PASSWORD=N'test123', DEFAULT_DATABASE=[mydatabase], CHECK_POLICY=OFF, CHECK_EXPIRATION=OFF

-- fix the user

USE [mydatabase]
sp_change_users_login 'UPDATE_ONE', 'myuser', 'myuser'

-- connect to instance B in SSMS as myuser

use mydatabase
select * from mytable

t
1
2

Connect as myuser to InstanceB using mydatabase

Show all databases connected as myuser to InstanceB

I found this on answer on stackoverflow which provides a query that returns users and their permissions for a given database:

https://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database


In what situation can snapshot restore be blocked after SET SINGLE_USER? In my tests, I have the following script:
USE master;
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [TestDB] FROM DATABASE_SNAPSHOT = 'TestDBSnapshot'

In some case that I am not able to understand, I get the following error:
Database state cannot be changed while other users are using the database 'TestDB'
RESTORE DATABASE is terminating abnormally.
Changed database context to 'master'.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

It seems the messages are not in the correct order (.NET issue?), but aside from that, what is the situation when I can get RESTORE blocked after SET SINGLE_USER? I would expect SINGLE_USER to give me exclusive access (and it works most of the time).
After this error, the DB is indeed in SINGLE_USER state (but not restored).

When you set the database to single user, it allows a single user into the database.  If there's someone else trying to get into the database, and you don't have a connection to the database someone else could get into the database.
A single script like:
use master;
alter database MyDb set single_user;
use MyDb

...is still a single connection.  Something else would need to log into the SQL Server and into that database.  It could be a user, a job, an admin.
Setting the database into restricted user will block anyone who isn't a member of the db_owner fixed server role from connecting to the database.  I'd recommend that, or shutdown the app/web servers while you do the database restore.


High tempdb disk I/O during merge replication of BLOBs Having a merge publication for replicating BLOBs (type image), got very high tempdb disk I/O for my size of data. Publication is download-only and has no filters.

High disk I/O is caused by synchronization (when no subscribers are synchronizing, everything is ok), strongly correlated with number of subscribers. It happens even when no data is changed at Publisher between synchronizations, and that bothers me.

Size of replicated table: 7MB (total count of rows is about 100)
tempdb I/O : ~30 MB/sec for write (log and data files)
Number of subscribers: slightly over 100, each synchronizing every 30
minutes (more or less evenly).
Retention period set to 14 days

Using SQL Server 2008 at Publisher, SQL Server 2005-2008R2 at Subscribers.
All subscribers use Web Synchronization.

Additionally, synchronization at subscriber takes a lot of time, with multiple occurrences in replmerg.log like these:

DatabaseReconciler, 2015/04/21 12:13:40.348, 3604, 25088,  S2,
INFO: [WEBSYNC_PROTOCOL]
Sending client ReconcilerPhase WebSyncReconcilerPhase_RegularDownload

DatabaseReconciler, 2015/04/21 12:13:47.063, 3604, 25194,  S2,
INFO: [WEBSYNC_PROTOCOL]
Received server ReconcilerPhase WebSyncReconcilerPhase_LastRegularDownload

Tried setting @stream_blob_columns on and off with no effect.

The question is: Is it a good idea to use merge replication to send these blobs to subscribers? We have other publications (though they have no BLOB columns) with a lot of data without tempdb problem. Is it an SQL Server flaw, or bad setup?

Publisher and Distributor are on the same instance, SQL Server 2008 SP4, cannot be sure about Subscribers, some of them maybe not up-to-date. Anyway, I can prepare a test environment with few subscribers having controlled versions, if it seems to help.

Confirmed, that excessive tempdb usage caused by execution of sys.sp_MSenumgenerations90. Checked MSMerge_genhistory table, found over 1.2 millions of records where pubid is null.

Found this conversation with replication guru:

Executed sp_mergemetadataretentioncleanup with no effect.

Found a remark on a case like this (too much rows in MSmerge_genhistory) where deleting rows where pubid is null and genstatus=1 helped to fix replication.

Deleted rows where pubid is null (implying that all Subscribers are synchronized, and which are not - will be reinitialized in "manual mode") and disk IO is back to normal again!

I have a feeling, that this situation could be caused by the fact, that all of my Subscribers are anonymous via WebSync and most of them have the same hostname. I'll try to check, if -hostname key helps not to multiply records in MSmerge_genhistory.

There is a TechNet blog that discusses some merge replication issues with blobs on a SQL Server 2008 or higher server.

http://blogs.technet.com/b/claudia_silva/archive/2011/10/31/replication-watch-out-for-stream-blob-columns-when-setting-up-replication-on-your-sql-2008-server.aspx

Note that the author cautions about which settings to use when there are SQL Server 2005 clients, such as you have.


Should I denormalize lat & lon on area searches? I run a hobby web site for tabletop gamers.  They can search based on postal code in a radius round their location.  As I consider adding new countries, my best option seem to be silo'ing the geo postal codes tables for each country instead of one large table in order to keep the length down.  Canada alone is 91,8587 rows.  By the time I add a few more english speaking countries, the table length could be a few hundred thousand.

However I was wondering if it would be smart to store the lat & lon in the user-list instead of normalizing the data where it would have to go through several JOINS.  I can't imagine the user list would ever reach more than 50k. The user-list only has about 30 columns and is indexed.

Which is the better option?

edit:  Forgot to mention that the web site does the radius search, not a 3rd party like google.

I don't see why would't you just store the coordinates for every user. You can keep them either in the same table as users or in some kind of user details table, depending on how your current schema is designed. There is no point in having all geo to postcode locations mapped.

To get the locations you can use something like google maps API.


Count missing values in each column of the table in PostgreSQL Question: Which column of fortune500 (a table) has the most missing values? To find out, I have checked each column individually mentioned below, which is very tedious.
SELECT
    SUM(CASE WHEN ticker IS NULL THEN 1 ELSE 0 END) AS ticker_null_num,
    SUM(CASE WHEN profits_change IS NULL THEN 1 ELSE 0 END) AS profits_change_null_num,
    SUM(CASE WHEN industry IS NULL THEN 1 ELSE 0 END) AS industry_null_num
FROM fortune500;

And also performed the following query for each column individually:
SELECT count(*) - count(ticker) AS missing
  FROM fortune500;
 etc....

My Question:
Is there any better/dynamic way of doing it because this approach is very cumbersome and it will take a lot of time as I have like 50 - 60 columns in a table then what should I do in that case instead of this manual approach. Can somebody help me find the missing values of each column sorted in a descending order with a good method? Like for example:
COLUMN_NAME    MISSING_VALUES_COUNT
col1               60
col2               50
col3               45
col4               40
etc.....

You can convert the rows into JSON to dynamically generate one row for each column:
select colname,
       count(cols.value) as non_null_values,
       (select count(*) from the_table) - count(cols.value) as missing
from the_table t
  cross join jsonb_each(jsonb_strip_nulls(to_jsonb(t))) as cols(colname, value)
group by colname;

But this isn't going to be fast on large tables.


CHECK constraint to enforce ###.###.#### pattern match I would like to have a check constraint that enforces this regex pattern for a multi-part code:

^\d{3}\.\d{3}\.\d{4}$

...three numbers, a period, three numbers, a period, four numbers.

Do I need to create a CLR function to enforce the pattern matching, and can the CLR function be referenced in DDL?

Is there a way to enforce the pattern in another way, using LIKE?

The regex in your question is not entirely unambiguous

  In most flavors that support Unicode, \d includes all digits from all
  scripts. Notable exceptions are Java, JavaScript, and PCRE. These
  Unicode flavors match only ASCII digits with \d.

So in many flavours it would match ١١١.١١١.١١١١ (that character being Arabic-Indic Digit One)

I'm assuming that you just want to match latin 0-9. You can use

DECLARE  @T TABLE
(
Col CHAR(12) CHECK (
                   Col LIKE REPLACE('ddd.ddd.dddd','d','[0-9]')
                                  COLLATE Latin1_General_100_BIN2
                   )
)

I'm using the binary collate clause so strings like ¾¾¾.¾¾¾.¾¾¾¾ or 10².10².1000 don't pass the check (as can happen on some collations).

Neither of these would match the desired CLR regex and although using the set syntax ([0123456789]) rather than the range syntax ([0-9]) resolves the first one  it is still collation dependent whether 2 matches ² or not.

I'm using REPLACE rather than hard-coding the LIKE pattern because I find it more readable what the pattern is checking. It is easier to see three digits and a dot followed by three digits another dot then four digits in that format than parsing [0-9][0-9][0-9].[0-9] [0-9][0-9].[0-9][0-9][0-9][0-9].

It also makes it easier to swap out implementation.

I you do decide that you want the full blown semantics of the CLR regular expression after all you could just change '[0-9]' above to the below (and likely change the column to NCHAR(12) in that case)

'[' +
 /*48-57 Basic Latin*/
N'0-9'+
 /*1632-1641 Arabic-Indic*/
N'٠-٩'+
 /*1776-1785 Extended Arabic-Indic*/
N'۰-۹'+
 /*1984-1993 Nko*/
N'߀-߉'+
 /*2406-2415 Devanagari*/
N'०-९'+
 /*2534-2543 Bengali*/
N'০-৯'+
 /*2662-2671 Gurmukhi*/
N'੦-੯'+
 /*2790-2799 Gujarati*/
N'૦-૯'+
 /*2918-2927 Oriya*/
N'୦-୯'+
 /*3046-3055 Tamil*/
N'௦-௯'+
 /*3174-3183 Telugu*/
N'౦-౯'+
 /*3302-3311 Kannada*/
N'೦-೯'+
 /*3430-3439 Malayalam*/
N'൦-൯'+
 /*3558-3567 Sinhala*/
N'෦-෯'+
 /*3664-3673 Thai*/
N'๐-๙'+
 /*3792-3801 Lao*/
N'໐-໙'+
 /*3872-3881 Tibetan*/
N'༠-༩'+
 /*4160-4169 Myanmar*/
N'၀-၉'+
 /*4240-4249 Myanmar Shan*/
N'႐-႙'+
 /*6112-6121 Khmer*/
N'០-៩'+
 /*6160-6169 Mongolian*/
N'᠐-᠙'+
 /*6470-6479 Limbu*/
N'᥆-᥏'+
 /*6608-6617 New Tai Lue*/
N'᧐-᧙'+
 /*6784-6793 Tai Tham Hora*/
N'᪀-᪉'+
 /*6800-6809 Tai Tham Tham*/
N'᪐-᪙'+
 /*6992-7001 Balinese*/
N'᭐-᭙'+
 /*7088-7097 Sundanese*/
N'᮰-᮹'+
 /*7232-7241 Lepcha*/
N'᱀-᱉'+
 /*7248-7257 Ol Chiki*/
N'᱐-᱙'+
 /*42528-42537 Vai*/
N'꘠-꘩'+
 /*43216-43225 Saurashtra*/
N'꣐-꣙'+
 /*43264-43273 Kayah Li*/
N'꤀-꤉'+
 /*43472-43481 Javanese*/
N'꧐-꧙'+
 /*43504-43513 Myanmar Tai Laing*/
N'꧰-꧹'+
 /*43600-43609 Cham*/
N'꩐-꩙'+
 /*44016-44025 Meetei Mayek*/
N'꯰-꯹'+
 /*65296-65305 Fullwidth*/
N'０-９'+
N']'  COLLATE Latin1_General_100_BIN2


Need urgent help with recovering data from dropped database I just made a huge mistake by doing a rails command on my production web  server which reset the postgres database - I believe the database is dropped and reset with just the schema

I have a backup from a day ago, but there's still a lot of critical data I'll be missing from the last 24 hours including transaction data. I'm really confused on what to do. I can't recover everything by just going through the HTTP logs as there are some background jobs that ran as well.

I'm running postgres 9.6, I've looked at past stackoverflow posts, but I don't know if the data is still there somewhere.

I stopped postgres as soon as I found out(maybe 10 mins after I issued the command)

There are some WAL files in the "pg_xlog" directory, is it possible to recover from them?

I also saw this post: https://dba.stackexchange.com/a/49118 that says there's a way to inspecting the pageinspect module - is that worth looking into?

Is there any way to get a sql transaction history or something from the last 24 hours?

I wanted to ask this question first before restarting postgres and maybe killing whatever chance I had.

I already asked the question here https://stackoverflow.com/questions/42962303/dropped-database-with-rake-dbschemaload-any-way-to-recover-data and suggested to post here.

There is no pg_base_backup. Simply a pg_dump from 24 hours ago.

Hire a professional.

If this is important production data that needs to be recovered quickly, your best strategy is to get an expert in as soon as possible to get the work done.

The work you will be focusing on is creating processes and procedures to make sure this doesn't happen in the future. And convincing management that the likely hood of it happen again based on those processes and procedures are as low as possible.


How to recover data from a corrupted /base/ directory? I have deleted all directory by mistake except from the given link
/var/lib/postgresql/9.5/main/base/18686 and so on.

I have 4 such directories, now I want to get data back from those folder as they are very important.

I am using ubuntu 16.04.

Can you please help us?

It seems like you have deleted folders like

pg_xlog
pg_tblspc
pg_serial

With only base directory :   /var/lib/postgresql/9.5/main/base/18686
You can't recover data.
You can refer to the documentation.

If you have a dump file recovering from it might be the only solution.


My SQL Server Upgrade from 5.6.12 to 5.6.22 I have a MySQL Server 5.6.12. I have this server as a windows service.

When i upgraded this, i did the following.

I stopped the service
I copied 5.6.22 server folder, removed the data folder.
From this folder(without Data Folder), I pasted into
existing 5.6.12 Server Folder.

I can see the server upgraded to 5.6.22.

But when i query the data, it shows only few rows. My Front End(web URL) shows.
500 rows. So I am not pointing to the correct place? Please advise.

What I am missing? The Disk Space in Data Dir says: unable to retrieve.

Thanks
kp

That's a very uncommon way to upgrade.  Instead, leave the data folder in place.

backup the data folder.
install the new version
run mysql_upgrade

(No, I can't explain what went wrong with what you did.)


Removing secondary data files. DBCC SHRINKFILE: Page could not be moved because it is a work table page I have too many secondary data files (.ndf) created for tempdb. To remove the excess files, I need to empty the file (content will be moved to other files):

DBCC SHRINKFILE('tempdbfile8', EMPTYFILE);

and then delete the file:

ALTER DATABASE tempdb REMOVE FILE tempdbfile8;

But EMPTYFILE command returns the error:

DBCC SHRINKFILE: Page 8:41920 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 2
Cannot move all contents of file "tempdbfile8" to other places to complete the emptyfile operation.

Not to worry, I just need to locate the object that's using this page to do something about it:

DBCC TRACEON (3604)
DBCC PAGE(2,8,41920) --dbid=2, fileid=8, pageid=41920

The command returns a lot of information, object_id among them. But:

Metadata: ObjectId = 0

I have no idea what to do about it. What cat be preventing this page from being moved? How to locate that object, process, session or whatever it is?
Any help will be appreciated, but please note that leaving everything as it is or removing other file instead is not a valid solution to this problem ;).

EDIT:

I'm removing the files, because we used to follow "best practice" of creating one file per processor core (same initial size, same growth rate). But as far as I know, until you run into contention problems, there's no point to create additional tempdb files on the same device. In our case it makes sense, because we have MPIO turned on, and storage device can handle 4 paths.
But there was a mistake, and we ended up with total 5 files with 6-core cpu. It's more than MPIO paths, less than CPU cores, and it's not an even number. It may not cause any problems, but just doesn't seem right :).

I was finally able to empty and remove the file without restarting the server by setting one of the datatabases (that I suspected of causing the problem) to single user mode (rollback immediate). It worked, but I got lucky.
What I really want, is to be always able to track the page down :).

Restarting the server should be enough - those worktables should clear out. But I'd probably start it up in single user mode (-m) to prevent other processes from creating worktables before you successfully remove those files. Then redefine the files required for tempdb; perhaps deleting unnecessary files, changing sizes, etc. You should also ensure you have an even number of files, that they're all set to the same size, and that they all have the same autogrowth settings (in MB, not %). And it might be a good time to consider TF 1117 and TF 1118 as well (starting point).

I'd be very wary about the suggestion to just delete the files from the file system before starting SQL Server again - it might not start at all.

(I'd also be curious about what the actual problem is, though. Having too many files doesn't hurt you, really.)


At the risk of stating the obvious, you have a cardinality problem with col1.

Looking at the two queries you posted in the question:

SELECT * FROM table_1 WHERE col1 = 1
AND id NOT IN (79869) ORDER BY date DESC LIMIT 0, 10;

SELECT * FROM table_1 WHERE col1 = 1 AND id <> 18983
AND date > '2012-10-26' AND (col2 LIKE '%word1%' OR col2 LIKE '%word2%')
ORDER BY date DESC LIMIT 5;

you need good indexes that will accommodate these two queries. In your particular case, you will need just one index.

If the table is MyISAM, here is that index

ALTER TABLE table_1 ADD INDEX col1_date_ndx (col1,date,col2,id);

If the table is InnoDB, here is that index

ALTER TABLE table_1 ADD INDEX col1_date_ndx (col1,date,col2);

How will this index help? Although you have to live with the lopsided cardinality of col1, you improve each query's chances of being effectively searched.

You could also take a chance on refactoring the queries. How ???

Perhaps you could collect the ids only then join the ids back to the original table.

QUERY #1

Here is the first query

SELECT * FROM table_1 WHERE col1 = 1
AND id NOT IN (79869) ORDER BY date DESC LIMIT 0, 10;

Collect the ids and join back to table_1

SELECT B.* FROM
(SELECT id FROM table_1 WHERE col1 = 1
AND id NOT IN (79869) ORDER BY date DESC LIMIT 0, 10) A
LEFT JOIN table_1 B USING (id);

QUERY #2

Here is the second query

SELECT * FROM table_1 WHERE col1 = 1 AND id <> 18983
AND date > '2012-10-26' AND (col2 LIKE '%word1%' OR col2 LIKE '%word2%')
ORDER BY date DESC LIMIT 5;

Collect the ids and join back to table_1

SELECT B* FROM
(SELECT * FROM table_1 WHERE col1 = 1 AND id <> 18983
AND date > '2012-10-26' AND (col2 LIKE '%word1%' OR col2 LIKE '%word2%')
ORDER BY date DESC LIMIT 5) A
LEFT JOIN table_1 B USING (id);

I cannot make promises on the queries, but, at the very least, the index suggestion should help.

Give it a Try !!!


Under what circumstances would these 3 queries return different number of results? Hello I have the following question:
Query 1:

SELECT b.id,
    (
    SELECT SUM(cb.invoice_amount) AS sum
    FROM payment_lines cb
    WHERE cb.batch_id = b.id
    ) AS batch_total_invoice_amount
FROM batches b;

Query 2:

SELECT b.id, SUM(cb.invoice_amount)
FROM batches b
 LEFT JOIN payment_lines cb ON b.id = cb.batch_id
GROUP BY b.id
ORDR BY b.id desc;

Query 3:

SELECT batch_id, sum(invoice_amount)
FROM payment_lines
GROUP BY batch_id
ORDER BY batch_id desc;

The first query may return rows from batches where no payment-lines exist, ie. an outer join.


How to get a last DML operation in Oracle 10g-11g I can get a number of last INSERT's/UPDATE's/DELETE's from SQL%ROWCOUNT var. Also there exists a way to get a last DML time of each row.

But is there a way to get a last DML operation performed on a table - INSERT/UPDATE/DELETE?

With this query you will see your last DML event by loonking on last change , most recent date is the last operation

  SELECT TABLE_OWNER,
   TABLE_NAME,
   INSERTS,
   UPDATES,
   DELETES,
   TIMESTAMP AS `LAST_CHANGE`
FROM  ALL_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY')
ORDER BY LAST_CHANGE DESC;

But the only way to use it its separating the DML events in differents querys or adding three differents last_change per dml event.
In any case you tell me your doubts


Detect Convert_Implicit usages I suspect that my Sql Server is using CONVERT_IMPLICIT a lot because many of my fields are set as string in NHibernate while they are set as varchar in database.
Is there any option to detect when server is using The CONVERT_IMPLICIT function?

Thanks

Stolen unceremoniously from Jonathan Kehayias, this will find all the plans relevant to the current database that contain implicit converts (be aware that this is not fast):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'),
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'),
   ic.DATA_TYPE AS ConvertFrom,
   ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
   t.value('(@Length)[1]', 'int') AS ConvertToLength,
   query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')
  AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
   ON QUOTENAME(ic.TABLE_SCHEMA)
    = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
   AND QUOTENAME(ic.TABLE_NAME)
    = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
   AND ic.COLUMN_NAME
    = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE
t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]')
 = 1

You might also find these useful:

http://www.sqlperformance.com/2013/04/t-sql-queries/implicit-conversion-costs

http://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/


MySQL using 100% of CPU with 1500 active connections I'm having a problem with MySQL. I have a system that runs some cron jobs and these create lots of database connections. When it reaches about 1500 active connections the cpu usage goes to 100% but memory stays fine. My server config is:

CentOS release 6.6 (Final)
Model   Intel(R) Xeon(R) CPU E5-1660 v3 @ 3.00GHz
Cores   16
Speed   1278.984 MHz
Cache   20480 KB
Memory 130GB

file my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_connect_errors=100
open-files=64

interactive_timeout=60
wait_timeout=10

max_connections=2000

max_allowed_packet=5M
tmp_table_size=100M
max_heap_table_size=100M

#query cache desativado
query_cache_type = 0
query_cache_limit = 50M

sort_buffer_size=       1M
read_buffer_size= 128K
read_rnd_buffer_size=1M
#join_buffer_size=208M
join_buffer_size=8M
key_buffer_size=20M
myisam_sort_buffer_size=10M

thread_cache_size = 30

key_buffer=100M
open_files_limit=64
default-storage-engine=MyISAM

innodb_file_per_table=1
innodb_buffer_pool_size=35G
innodb_additional_mem_pool_size=80M

query_cache_size=50M

back_log=75
expire_logs_days        = 1
max_binlog_size         = 30M

tmpdir = /var/mysqltmpdir

long_query_time=1
log_slow_queries=/var/log/mysql_slow_queries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
bind-address   =  *
port = 3306

You may have encountered a Von Neumann Bottleneck.

CPU usage is expected to increase as the number of connections increases.  As the connections increase the number of active queries should also increase.  Each query will use some CPU.  Eventually, you will have enough queries to use all available CPU.

There are some additional factors that while use more CPU as the number of concurrently running queries increase:

Time spent waiting for resource locks.  This may involve a lock spin which will consume additional CPU.
Context switching when the O/S switches the currently running query.  This is a side effect of the multi-processing that allows you to run more than one query at a time.

Running a program like sar may provide more details on where the CPU is being used.


Why does FROM multiple tables default to cartesian product? What was the idea behind doing a cartesian product (cross join) when i did a query like -
SELECT * FROM agents, orders

I would think that they would concatenate (like pandas). It feels more natural to add tables instead of multiplying them.
Just curious and did not find on the internet the rationale behind defaulting to cartesian product. I am assuming that syntax FROM table1, table2 according to SQL would probably be more correct with cross join but why?

The syntax of comma-style joins (SQL-89) versus using the JOIN keyword (SQL-92) is not the point. You should use the more modern syntax, but it doesn't address the question of why the default would be a Cartesian product if you do not specify a condition.
The answer to that is that it makes relational algebra work.
A Cartesian product is the set of all combinations of elements of two sets. Each element of the first set is paired with every element of the second set.
A relation is a subset of a Cartesian product. It's a Cartesian product, plus a condition which tests if any given pair of elements belongs to the relation.
But the default condition is a fixed true value, so every pairing passes the test. So the default relation ends up being the Cartesian product.
The alternative, I suppose would have been to make the default condition a fixed false value, so the default relation would be an empty relation.
That would have made some scenarios easier, for example if you run a DELETE but accidentally forget the WHERE clause, it would save you from deleting your whole table.
But then we would be getting questions on Stack Overflow from different people who ask why the default join is an empty set, because that makes their heart skip a beat when they forget their WHERE clause for a SELECT query and it appears that their database is empty.


Testing query speed in two different RDBMS I'm planning to compare the speed of queries in PostgreSQL and SQL server (on the same datasets). I was planning to run the same queries multiple times in both databases and use average response time in miliseconds as a rough estimate.

This seems a little too basic. Is there a better way to compare a speed of a query in two different database systems running on the same hardware? Any tools that I can use?

You could use HammerDB. With HammerDB you perform a TPC benchmark test. TPC benchmark testing can be used by vendors for official certification, but you can also use it as 'non-offical' for your personal benchmarking. The TPC-E (OLTP) and TPC-H (OLAP) are the more modern version of the TPC benchmark tests. With HammerDB you can emulate an OLAP or OLTP workload with the same characteristics (you can configure this) and run it on different RDBMSes.

You have 2 types of measurements; TPM and NOPM. TPM cannot be compared between different database types, but NOPM can. This tool is fairly easy to use, although you can make things a little more complex if you want to. :-) You can get HammerDB simpy from hammerdb.com.

Although this is not intended to test with a specific query, it could be usefull depending on your scenario.


What is the impact of setting "max text repl size (B)" to -1? I have a table replicated via transactional replication that is throwing this error message:

  Length of LOB data (68983) to be replicated exceeds configured
  maximum 65536.

This can be resolved by adjusting the "max text repl size (B)" SQL Configuration value from the default of 65536.

I see two options:

Adjust the value higher than required for this situation
Set the value to "-1" which indicates no limit

My question is:  What is the downside of setting "max text repl size (B)" to -1?

I appreciate any feedback!

What is the downside of setting "max text repl size (B)" to -1?

I would not set the value to -1 (limitless). I would instead find out the max datalength of your lob data and set it accordingly with a 10% buffer. This way you have more control of what and how much gets into distribution.

I can see that in an unlikely situation, setting of -1 can cause high network latency if the blob data to be replicated is of a much larger size.


Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on a new server and slowly migrate across to this instance. All of the applications have fairly straightforward migration plans for changing the database location normally revolving around stopping services, detaching, attaching and rerunning installers.

All looks good so far. My only concern is with SQL Server Agent jobs and logins. Initially I was under the impression that these were all stored in the system databases and that I could move these. I understand now that this is not the case.  Microsft KB 918992
suggest running a script to generate a stored procedure and then calling this and running the generated script on the new instance. There seem to be a lot of caveats to it though.  Another option would be to use SSIS to generate a "Transfer Logins" and "Transfer Jobs" package and run that (both editions are Standard) but I am unversed in SSIS so am a touch scared to use that.

I suppose my question boils down to this:

Is the Microsft KB the best way to migrate logins to the new SQL instance?
Is the only way to migrate tasks to the new instance via SSIS?

I am trying to get it as clear in my head as possible for my planning documentation so am very appreciative of any replies/guidance.

For logins, this is better IMHO:

http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.

I'd do the logins first, in case any of the jobs involve any of the logins.


SQL Database mirroring \ replication capabilities? I've looked through the similar questions, but they are a little further down the line, more into details already.

While I'm still researching the concept of mirroring and what can and can not be achieved.

So I wanted to ask an opinion\suggestion\direction:

We have a web app which is backed by an SQL DB (2014)
We also have remote (far..) office which uses that web app
The remote office uses our local HQ web server so the network delay is getting on their nerves

Ideally we would like to replicated the whole app environment (web server + db) to their region and have a "live sync" between these two environments.

Within the app itself - there is no provisioned functionality for this.

So I am looking into default SQL toolset or other 3-rd party solutions maybe which could help us set a mirror of the main DB in the remote region with live synchronization (if possible). We can survive a data replication delay (user in region 1 creates something while it gets to the region 2 within next 5 minutes).

It's fairly common situation and I want to understand if this can be achieved by SQL tools themselves or with some other 3rd party solution which will manage the replication\syncing?

Mirroring won't do this...its one way.   You need to investigate either Transactional or Merge Replication for the two-way communication, if you want to stay in the SQL Server capabilities.   merge helps if you have bad bandwidth or network drops.   Both Tx and Merge have their own unique requirements that are well documented on Technet :)


Full outer joins, coalescing, indexes and views I have two tables, let's call them TableA and TableB.

TableA               TableB
------               ------
ID                   ID
Date                 Date
Foo                  Bar

Both tables have an index on their date field.
Now, I want to create a view that has a full outer join between the two tables on the Date field, to show me the Foo value for that date (if there is one) along with the Bar for that date.

create view vFooBar as
select isnull(a.Date, b.Date) Date, a.Foo, b.Bar
from TableA a
full outer join TableB b on a.Date = b.Date

Then I run a query:

select * from vFooBar where Date = '20141201'

This works... but the coalescing of a.Date and b.Date fools the compiler into not using the indexes, and I get a horrible execution plan involving table scans.

How can I create this view in such a way that querying it will still use my indexes?

Try this

select a.Date, a.Foo, b.Bar
from TableA a LEFT JOIN TableB b ON a.Date = b.Date
UNION
select b.Date, a.Foo, b.Bar
from TableB b LEFT JOIN TableA a ON a.Date = b.Date

Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left join union all anti-semi-join:

SELECT
    TA.TheDate,
    TA.Foo,
    TB.Bar
FROM dbo.TableA AS TA
LEFT JOIN dbo.TableB AS TB
    ON TB.TheDate = TA.TheDate
UNION ALL
SELECT
    TB.TheDate,
    Foo = NULL,
    TB.Bar
FROM dbo.TableB AS TB
WHERE NOT EXISTS
(
    SELECT NULL
    FROM dbo.TableA AS TA
    WHERE TA.TheDate = TB.TheDate
);

This second rewrite will also facilitate the use of an index on TheDate columns.


SQL logs showing login that I can't find Within my SQL server logs, I am showing a successful login to a database from an account (SERVER\loginname) that I cannot find in either Security > Logins OR Database > Security > Users. This login is being used by a program to execute a task (which it does successfully), so it's not a security compromise, but I cannot for the life of me figure out where it is and I need to find it to make an adjustment.

A couple more notes: I do remember adding this specific login a while back and there was something special about how I had to do it because it is not a domain account. Instead, it uses pass through authentication (same local account name on the app and SQL server with the same password). I cannot use it to login via SSMS. It is not a part of a group membership. It is not showing when I do a SELECT from the master syslogins table.

Does anyone have any ideas for my brain-fart?

Can you try running the following:

EXEC xp_logininfo @acctname = 'SERVER\loginname', @option='all';

This should return the SQL login the Windows user is being authenticated as.

Reference here.


Does it make sense to create a new database for every user? (MySQL) I want to make a website which will have approximately 10M+ users. My MySQL database will be huge! Because the users will chat, post, share etc.

I will use dreamhost. They can provide me unlimted databases but they told me to keep the database size below 2-3GB. I'm afraid if I use a single database it will exceed 2-3GB in a week or two.

So the question is - should I create a new database per user?

Humour form for begin:

  if You are plan have 10M active users, which will chat, send messages
  and pay for You at least couple of dollars per month - You must open
  Your own hosting provider, rather than try to use anybody.

If talking seriously:

Size of database limited only by plan (mean cost) - so 2-3Gb it just for smallest plans
10M users - most important, how many of them active and what approximate but realistic data they will generate
what historic period they really need? as example - store 1 week history in active database and all other in archive instance
and 10M separate databases - will be exactly killer of Your server and application (and by resource usage, and by code complicity and by logic)


SQL Count function returning inaccurate results I am trying to write a query in oracle to list the number of records contained in a single table filtered by multiple columns in the tablespace.  Here is the query syntax I have come up with:

SELECT COUNT(CASE WHEN unique_id like '%OU=Users%' and employee_type > '-1' THEN 1 ELSE NULL
         END) AS USERS1
   ,COUNT(CASE WHEN unique_id like '%OU=Vendors%' and employee_id is null and employee_type is null THEN 1 ELSE NULL
          END) AS USERS2
   ,COUNT(CASE WHEN unique_id like '%OU=Temp Users%' and employee_type ='-1' THEN 1 ELSE NULL
          END) AS USERS3
   ,COUNT(CASE WHEN unique_id like '%OU=Service%' THEN 1 ELSE NULL
          END) AS USERS4
   ,COUNT(*) AS USERS5
FROM table_USERS
WHERE is_terminated = 'False'

Here is the resulting output:

users1=1192
users2=38
users3=25
users4=240
users5=1548

The result is not correct it is showing "users5" as the total number of users that exist in the database (1548), when it should show me only 53 users which don't match any of the user categories (users1 - users4).

What am I missing and is there a more simplistic way to write this query?

It looks like you just have "COUNT(*) AS USERS5", I'm not seeing where you're filtering out the other types of user categories.


How to delete Specific row http://sqlfiddle.com/#!9/df6245
How to delete all rows Where brand not equal to 107
for example in above fiddle GRPCD 44111 have 107 so it should not delete both rows which have GRPCD 44111 but it will delete both two rows of GRPCD 33111 because there is no brand 107 value.
Please help
Edit
This query is working perfect someone can give me better query.
DELETE FROM `products_cross` WHERE GRPCD NOT IN (SELECT GRPCD FROM products_cross WHERE brand=107)

You need a subquery like this for the products_cross , so that Mysql has a temporary table to select the wanted ids

CREATE TABLE IF NOT EXISTS `products_cross` (
  `id` int(6) unsigned NOT NULL,
  `GRPCD` int(10) unsigned NOT NULL,
  `brand` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `products_cross` (`id`, `GRPCD`, `brand`) VALUES
  ('1', '33111', '107'),
  ('2', '33111', '103'),
  ('3', '44111', '105'),
  ('4', '44111', '101');

SELECT * FROM products_cross

id | GRPCD | brand
-: | ----: | ----:
 1 | 33111 |   107
 2 | 33111 |   103
 3 | 44111 |   105
 4 | 44111 |   101

DELETE FROM `products_cross`
WHERE GRPCD NOT IN (SELECT GRPCD FROM (SELECT * FROM products_cross ) pc WHERE brand=107)

SELECT * FROM products_cross

id | GRPCD | brand
-: | ----: | ----:
 1 | 33111 |   107
 2 | 33111 |   103

db<>fiddle here


What database and setup can handle several million inserts per minute? We have come to a point where for a few of our customers' systems, at peak times, events are generated at that crazy rate. That is, millions of new events per minute (between 1M and 2M).

The records/docs size are 1.5Kb on average.

Is there a database that can handle such radical write speed out there (16K - 32k inserts per second)?

There are several candidates out there, Cassandra, Hive, Hadoop, HBase, Riak and perhaps a few others, but I would like to know from someone else's experience and not just quoting from each database system's website self testimonials.

If you have handled such load at your work, your advice is highly appreciated.

Ok, let me start simple: My old trusted 5 year old main db server (2x quad core AMD Opteron 2378) handles around 80,000 inserts per second, though small ones. That is without SSD for log and data on a SQL Server.

There are plenty of databases that can meet your requirement - it is MOSTLY a question of appropriate hardware. Heck, if you would go Oracle, then the smallest Exadata in a performance configuration (pure SSD) likely can do 10 times as much - we got in a project 5 gigabyte/second stable IO out of it, with a paltry 3 storage nodes.

That said, you WILL have to test it yourself. A lot depends on your programming or lack thereof. If you hit 1 insert per transaction, that is VERY different - and a lot harder to do (not at all because your log partitions need to handle 5 million IOPS then or fake the flush).


Efficient way to write query for multiple table which depends on column value of a table in mysql? In the given schema, there is a table A having column A1,A2,A3,A4

There are three more tables i.e. B,C,D

If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup table C. If the value in A4 is d, i have to lookup table D.

Currently, we have implemented using switch case i.e.

select * from A
where
CASE
  WHEN SUBSTRING(A4,4,3) IN ( '023','009','011','013','015','017','019','021')
    THEN A3 NOT IN ( select Z from B)
  WHEN SUBSTRING(A4,4,3) IN ( '006','024','028','031')
    THEN A3 NOT IN ( select Z from C)
  WHEN SUBSTRING(A4,4,3) IN ( '004','025')
    THEN A3 NOT IN ( select Z from D)
END

Is there any better and more efficient way to resolve this problem?

First of all, append a computed column, say A4Key, set to SUBSTRING(A4, 4, 3).

This column shall be kept up-to-date either by your application code or by triggers, such as:

CREATE TRIGGER ... BEFORE UPDATE ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

CREATE TRIGGER ... BEFORE INSERT ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

first solution

Then, rewrite your query to:

SELECT * FROM A
WHERE A4Key IN('023', '009', '011', '013', '015', '017', '019', '021')
  AND A3 NOT IN(SELECT Z FROM B)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('006', '024', '028', '031')
  AND A3 NOT IN(SELECT Z FROM C)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('004', '025')
  AND A3 NOT IN(SELECT Z FROM D)

Now, evaluate your query plans to devise the best indexes.

second solution

Create a table such as:

CREATE TABLE A4Exceptions(
  A4Key VARCHAR(3),
  Z     `whichever_type_of_Z`
)

With initial data set by:

INSERT INTO A4Exceptions SELECT '023', Z FROM B
INSERT INTO A4Exceptions SELECT '009', Z FROM B
...
INSERT INTO A4Exceptions SELECT '006', Z FROM C
...

And keep it up-to-date with the changing (?) data from B, C and D.

Done that, now you may, with blazing performance (after creating some indexes):

SELECT *
FROM A
WHERE NOT EXISTS(
  SELECT *
  FROM A4Exceptions exc
  WHERE exc.A4Key = A.A4Key
    AND ext.Z     = A.A3
)


Scheduled Database Backup job fails due to lack of disk space Two of my scheduled SQL Server backups failed over the weekend because of a lack of space.

The C: drive has 25.3 GB of free space out of 400 GB. The primary space taker is SQL.

149.9 GB C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP
139.7 GB C:\Program Files\Microsoft SQL Server\MSSQL\DATA

Any suggestions as to how to fix this problem? Please help! I'm a rookie DBA confronted with this issue

(Can post this as comment due to lack of rep)

This link could be helpful to you:
http://www.mssqltips.com/sqlservertip/2531/sql-server-compressed-backups-disk-space-needs/


Can AWS Aurora High Availability allow different Schema on Replicas? Our company is thinking of migrating from SQL Server to Amazon AuroraDB.

In SQL Server, there are AlwaysOn and Transactional Replication - two different things.

AlwaysOn is utilized more for disaster recovery, it sends whole database to another cluster, providing database level protection. The data is sent directly with a  transaction log redo thread. Microsoft AlwaysOn
In Replication, data is first sent to a distribution database then to a subscriber. Replication allows for only certain tables to be sent, with different schema and indexes on subscriber, which can be different from Publisher. Microsoft Replication

Is AWS High Availability similar to MS AlwaysOn Disaster Recovery or Replication? It seems more similar to AlwaysOn.

If so, does it have a Replication type technology? We want to make schema changes on the subscriber. (add indexes, remove certain tables not required in reporting env)

Stack

AWS Aurora's replication is more akin to Always On Availability Group. The primary pushes storage changes to other replicas. You don't get to make schema changes on the subscriber.

More details are available in several re:Invent deep dive sessions.


How to speed up query with joins I have a couple of tables that I need to join. I have an employees table (~ 400K rows), a companies table (~10 million rows) and a employee_companies table which stores where someone works.

Basically, I need to get all the employees that match some conditions (they work on a company that has a website, are located in a certain country, etc). I made a query to get this, but it's taking too long. I need to speed it up.

SELECT  DISTINCT "employees".*
FROM "employees"
INNER JOIN "employee_companies" ON "employee_companies"."employee_id" = "employees"."id"
INNER JOIN "companies" ON "companies"."id" = "employee_companies"."company_id"
WHERE (employee_companies.employee_id IS NOT NULL)
AND (companies.website IS NOT NULL)
AND (employees.country = 'Uruguay')
ORDER BY employees.connections DESC

This is the plan for that query:

Unique  (cost=877170.24..880752.72 rows=62304 width=1064) (actual time=24023.736..26001.876 rows=73318 loops=1)
  ->  Sort  (cost=877170.24..877326.00 rows=62304 width=1064) (actual time=24023.733..24305.989 rows=77579 loops=1)
        Sort Key: employees.connections DESC, employees.id, employees.name, employees.link, employees.role, employees.area, employees.profile_picture, employees.summary, employees.current_companies, employees.previous_companies, employees.skills, employees.education, employees.languages, employees.volunteer, employees.groups, employees.interests, employees.search_vector, employees.secondary_search_vector, employees.email_status, employees.languages_count, employees.role_hierarchy
        Sort Method: external merge  Disk: 85816kB
        ->  Nested Loop  (cost=2642.38..843246.15 rows=62304 width=1064) (actual time=139.870..23056.234 rows=77579 loops=1)
              ->  Hash Join  (cost=2641.95..221744.50 rows=77860 width=1068) (actual time=139.841..22617.587 rows=77579 loops=1)
                    Hash Cond: (employees.id = employee_companies.employee_id)
                    ->  Seq Scan on employees  (cost=0.00..212178.88 rows=409672 width=1064) (actual time=8.145..22369.166 rows=393725 loops=1)
                          Filter: ((country)::text = 'Uruguay'::text)
                          Rows Removed by Filter: 1075
                    ->  Hash  (cost=1666.42..1666.42 rows=78042 width=8) (actual time=44.675..44.675 rows=78042 loops=1)
                          Buckets: 131072  Batches: 1  Memory Usage: 4073kB
                          ->  Seq Scan on employee_companies  (cost=0.00..1666.42 rows=78042 width=8) (actual time=0.007..22.901 rows=78042 loops=1)
                                Filter: (employee_id IS NOT NULL)
              ->  Index Scan using companies_pkey on companies  (cost=0.43..7.97 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=77579)
                    Index Cond: (id = employee_companies.company_id)
                    Filter: (website IS NOT NULL)
Planning time: 1.957 ms
Execution time: 26025.045 ms

And these are the relevant indexes that I have on my table:

employees:

"employees_pkey" PRIMARY KEY, btree (id)
"ix_employees_country" btree (country)

companies:

"companies_pkey" PRIMARY KEY, btree (id)
"empty_websites" btree (website) WHERE website IS NULL
"index_companies_on_website" btree (website)
"not_empty_websites" btree (website) WHERE website IS NOT NULL

employee_companies:

"employee_companies_pkey" PRIMARY KEY, btree (id)
"index_employee_companies_on_company_id" btree (company_id)
"index_employee_companies_on_employee_id" btree (employee_id)
"index_employee_companies_on_employee_id_and_company_id" btree (employee_id, company_id)
"not_empty_employee_id" btree (employee_id) WHERE employee_id IS NOT NULL

Is there any other better way to do what I want that is more efficient/performant?

Thanks!

Based on some guess-simulations, I think you can slightly improve your query by:

Avoiding the outer DISTINCT clause (although there will be an implicitly DISTINCT).
Sub-selecting a part of the data so that less is needed to JOIN.

The query is as follows:

SELECT
    employees.*
FROM
    employees
WHERE
    employee_id IN
    (SELECT
        -- Choose all employees from companies with website
        employee_id
     FROM
        employee_companies
        JOIN companies ON companies.company_id = employee_companies.company_id
     WHERE
        companies.website IS NOT NULL
    )
    -- Now filter only employees from 'Germany'
    AND employees.country = 'Germany'
ORDER BY
    employees.connections DESC ;

The data used to produce the simulation is the following one:

Table and index definitions:

CREATE TABLE employees
(
    employee_id integer PRIMARY KEY,
    country text,
    connections integer,
    something_else text
) ;

CREATE INDEX idx_employee_country
   ON employees (country) ;

CREATE TABLE companies
(
    company_id integer PRIMARY KEY,
    website text,
    something_else text
) ;

CREATE INDEX not_empty_websites
    ON companies(company_id, website) WHERE website IS NOT NULL ;

CREATE TABLE employee_companies
(
    employee_id integer NOT NULL REFERENCES employees(employee_id),
    company_id integer NOT NULL REFERENCES companies(company_id),
    PRIMARY KEY (employee_id, company_id)
) ;

CREATE INDEX company_employee
    ON employee_companies(company_id, employee_id) ;

1.000.000 companies (changing to 10M doesn't make a big difference). I assume 90% have a website.

INSERT INTO
   companies
   (company_id, website)
SELECT
   generate_series(1, 1000000),
   CASE WHEN random() > 0.1 THEN 'web.com' END AS website ;

80k employees (about 10% are Germans)

INSERT INTO
   employees
   (employee_id, country, connections)
SELECT
    generate_series(1, 80000),
    case (random()*10)::integer
    when 0 then 'Germany'
    when 1 then 'United Kingdon'
    when 2 then 'United States'
    else 'Angola'
    end AS country,
    (random()*10)::integer AS connections ;

200K employees x companies (this means that people have worked in about 3 companies, on average):

INSERT INTO
    employee_companies
    (employee_id, company_id)
SELECT DISTINCT
    (random()*79999)::integer + 1,
    (random()*999999)::integer + 1
FROM
    generate_series (1, 200000) ;

You can check a downsized version of this simulation at dbfiddle here.  If this simulated data is sufficiently similar to your scenario, changing the query makes a 3x improvement with regard to server-execution time. I'd suggest you give it a try.

Simulating data (scaled down by a factor of 25) a scenario more similar to your real one doesn't offer such a nice increase in performance... Nevertheless, it improves by a 1.5 factor.

Check it at this dbfiddle


MYSQL: update field with random fixed char would like to update all record on a field which i only want it to fill it with random either Y or N

currently using:

  update table1 set field1=( RAND() FROM Y FOR N)

which is not working, any idea?

solved with this:

update table1 set field1 = case floor(1 + rand()*2) when 1 then 'Y' else 'N' end;


Select full record with minimum value for column grouped by joined value I have a table images with various columns, and two join tables images_units and images_sites that contains pairs of ids (image_id, unit_id), (image_id, site_id). One of the columns in the images table is sort.
I want to return each distinct record from the images table that has the minimum value of sort for each unit_id, with all records returned matching a given site_id. (for every unit_id there will be multiple image records.)
I thought I would be able to do something like this:
select images.*, unit_id from images
join images_units on images.id = images_units.image_id
join images_sites on images.id = images_sites.image_id
join (select min(sort) as minSort, unit_idas matchID from images join images_units on images.id=images_units.image_id group by matchID) as sq
where images.sort = minSort and a.unit_id = matchID and site_id = xyz;

In order to get all the values that I need, but I'm getting a syntax error near where so I presume I can't use it like this.
I ultimately want to return distinct rows and only the columns from images and nothing else (i.e the columns 'select distinct images.* from images' would give) but I think I should just be able to select those from the results of the query I've given - I'm including this note in case it's relevant to an alternative solution.

you are missing on {condition} for your last join :
however I'm not sure
select images.*, unit_id
from images
join images_units
    on images.id = images_units.image_id
join images_sites
    on images.id = images_sites.image_id
join (
    select min(sort) as minSort, unit_id as matchID
    from images
    join images_units
        on images.id=images_units.image_id
    group by matchID
    ) as sq
    on sq.matchId = ??  -- <-- here add the proper join condition
where
images.sort = minSort
and a.unit_id = matchID
and site_id = xyz;


Insert Timestamp on value change from other table I have an EvidenceTimeStamp table that is triggered with data when the case table has data inserted into it.

EvidenceTimeStamp:

CaseID
EvidenceNum
EvidenceType
Evidence_Initiated_Timestamp
Evidence_Returned_Timestamp

I created a trigger to fill the first four columns of data whenever a new case is inserted in the case table and it creates a timestamp on Evidence_Initiated_Timestamp when this happens.

However, I need to create a closing timestamp when the process is complete. I wanted to do this by my Outbound Ship Table- everytime the Attribute (bit value) Outbound_Item_Recevied is changed from false to true (or 0 to 1) I want it to initate a timestamp on the Evidence_Returned_Timestamp column in my EvidenceTimeStamp table that matches the corresponding CaseID and EvidenceNum.

Note: the EvidenceTimeStamp table does not have a primary key or foreign key technically... the CaseID is primary key in the Case table and EvidenceNum is an attribute in the Evidence Table. This is a "trigger" table so it really doesn't have its own specific data, it's just filling data from other tables.

Any idea of how to do this?

Also, other quick question, how do I get bit values to show "True" or "False" instead of 1 or 0? Thank you!!

Logically (based on the names of your tables), a particular item of evidence cannot be checked out to more than one person at once. We'll assume, then, that no more than one EvidenceTimeStamp table record for a given CaseID and EvidenceNum should have a NULL Evidence_Returned_Timestamp.

In that case, you'd need a trigger on your Outbound Ship Table with something like the following in it:

UPDATE ets
   SET ets.Evidence_Returned_Timestamp = GETDATE()
  FROM EvidenceTimeStamp ets
         INNER JOIN inserted I ON (    ets.CaseID = i.CaseID
                                   AND ets.EvidenceNum = i.EvidenceNum
                                  )
 WHERE i.Outbound_Item_Recevied = 1
   AND ets.Evidence_Returned_Timestamp IS NULL
;

(SQL Server syntax; modify as needed for other platforms)

This will work whether you're modifying/inserting one record or many in the Outbound Ship Table. However, this will update all rows for the given CaseID and EvidenceNum pair(s) that currently have NULL in Evidence_Returned_Timestamp, so if you can have more than one open row for a specific CaseID and EvidenceNum, you'll need some other way to find the right row(s) to update.

(To clarify: If you've got open entries for case 123, evidence numbers 2 and 7, and case 456, evidence numbers 3 and 7, and you updated an Outbound Ship item for case 123, evidence num 7, then the other three open entries are fine.  However, if you had two EvidenceTimeStamp rows with case 123, evidence number 7, then both of them would have their Evidence_Returned_Timestamp set.)

NOTE: Every table should have a column (or group of columns) that is guaranteed to uniquely identify every row in the table and it (or they) really should be the primary key. You may not want to set that up as a clustered index, but you enforce uniqueness by making that the primary key.


Sudden increase in log_file_sync waits I'm on Oracle 11gR2 with a 2 node RAC system.  It's shared fiber storage to an EMC Clariion.

Last friday things went bad..fast.  All of the sudden processes that normally ran fine for years became very, very slow.  I noticed a sudden increase in log_file_sync waits and the LGWR process is listed as a blocker for several processes.  Nothing changed on that Friday that we're aware of.  Also, it appears to be just on one node.

Statspack reports confirm that log_file_sync wait time went from around 1ms to 47ms !
Additionally statspack shows this - meaning some of them are waiting a lot:

                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
log file sync               100K    .0    .3   1.7   9.0  25.4  31.0  32.4    .1

And before it was this:

                           Total ----------------- % of Waits ------------------
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
log file sync              1589K  72.3  20.4   5.4   1.2    .6    .1    .0

What can cause this?  What should I be checking for?

Log file sync occurs when a commit is made and the redo buffer needs to be flushed to disk. The session has to wait for that to happen.

An increase in the number of log file syncs generally means that one of your developers has gone commit-happy, and is committing far too frequently -- every row, for example.

Here you probably have a process that performs around 1.5 million DML statements with a commit being issued after each one, so look out for some process that loads a few million rows of data.


How to know what server and port to select in MongoVue I am unsure about which server and port to select when trying to start up MongoVue.
FYI, MongoVue is the windows GUI for using mongodb (for those macusers who may want to answer this question).
I have a windows laptop and have already installed mongodb.

I have received a data dump with a lot of json and bson files.
The files are on my computer.

I don't know how to connect and upload all these json and bson files onto my MongoVue interface.

I am asked to fill in a server and port name. When I tried to type "localhost" for server name, it didn't work.
I would appreciate any help in this matter.

Try using;

Server: 127.0.0.1
Port: 27017

That should work.


In MySQL, how can I have non-nullable timestamp column without a default value? I want a non-nullable timestamp column where the inserter is required to specify what the value should be.

The following configuration

CREATE TABLE test (
  t timestamp not null
);

results in the default CURRENT_TIMESTAMP value, while

CREATE TABLE test (
  t timestamp not null default null
);

results in

  ERROR 1067 (42000): Invalid default value for 't'

I want the same behavior that, for example, the int column below has:

CREATE TABLE test (
  i int not null
);

I think that the only way is setting the server variable explicit_defaults_for_timestamp to ON (default is OFF).
This will disable the non-standard behaviour of timestamp columns regarding NULL values:

If explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMP columns as follows:

It is not possible to assign a TIMESTAMP column a value of NULL to set it to the current timestamp. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().

...

TIMESTAMP columns declared with the NOT NULL attribute do not permit NULL values. For inserts that specify NULL for such a column, the result is either an error for a single-row insert or if strict SQL mode is enabled, or '0000-00-00 00:00:00' is inserted for multiple-row inserts with strict SQL mode disabled. In no case does assigning the column a value of NULL set it to the current timestamp.


Why is table opened every time a query is executed against it? I execute this SQL command twice and profile

select * from sbtest.sbtest2 limit 100;

The first time profile result is

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000124 |
| checking permissions | 0.000015 |
*| Opening tables       | 0.163660 |*
| System lock          | 0.000029 |
| init                 | 0.000056 |
| optimizing           | 0.000017 |
| statistics           | 0.000073 |
| preparing            | 0.000228 |
| executing            | 0.000007 |
| Sending data         | 0.001327 |
| end                  | 0.000019 |
| query end            | 0.000008 |
| closing tables       | 0.000013 |
| freeing items        | 0.004998 |
| logging slow query   | 0.000015 |
| cleaning up          | 0.000009 |
+----------------------+----------+
16 rows in set (0.00 sec)

This is the first time6 ,so it needs to "open tables" from hard disk, and above is the second time profile.

mysql> show profile for query 5;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000072 |
| checking permissions | 0.000012 |
*| Opening tables       | 0.000026 |*
| System lock          | 0.000013 |
| init                 | 0.000025 |
| optimizing           | 0.000006 |
| statistics           | 0.000016 |
| preparing            | 0.000013 |
| executing            | 0.000003 |
| Sending data         | 0.000296 |
| end                  | 0.000008 |
| query end            | 0.000006 |
| closing tables       | 0.000010 |
| freeing items        | 0.000018 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000004 |
+----------------------+----------+
16 rows in set (0.00 sec)

The second time "open tables time" is 0.00026, which is much faster than the first time "open tables time" 0.163660.

What I don't understand is when I use innodb buffer pool and my open_table_cache is using, why I still need to open tables from hard disk ?

Perhaps the caption of that step isn't entirely accurate and what it is doing is "obtaining handle to access tables"? In that case the first call performs the relevant IO to open the table (read its metadata and prepare anything that is needed in memory) before returning the handle(s) so the next step can start accessing the resource, and the second time the routine will see that it already has all that done.

It is common (and generally good design, keeping stages decoupled from each other's detailed logic) for routines not to know when the sub-routine they call actually does some real work or just returns a cached resource. Good development avoid early optimisation (make it work, then make it work fast) so initially the code would go to disk every time but later versions include the caching logic.

Another alternative: maybe it really is redoing everything every time, but the database's page cache and/or the OS disk cache mean it is faster because there is less physical IO?


Does the SQL-1992 standard restrict naming identifiers to 18 characters? In this draft of SQL:1992, I found…

Section 5.2

  Leveling Rules

  ....

  The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions:

  a) No <regular identifier> or <delimited identifier body> shall contain more than 18 <character representation>s.

…and…

  Annex A.1

  Subclause 5.2, "<token> and <separator>":

  a) In conforming Intermediate SQL language, a <regular identifier> or a <delimited identifier body> shall not comprise more than 18 <character representation>s.

  b) A <delimiter token> shall not be a <bit string literal> or a <hex string literal>.

  c) Conforming Intermediate SQL language shall contain no <identifier body> that ends in an <underscore>.

For implementors of SQL, or for users of SQL?

I searched the document multiple times, but could never get a sense of the meaning of identifier.

Is the spec referring to labeling within an implementation of SQL (such as PostreSQL or Oracle)? So labels used within the SQL product cannot contain identifiers that are over 18 characters or end in an underscore.
Or is the spec referring to users of a SQL implementation? So we who are naming our schemas, tables, columns, and other such database objects cannot use names over 18 characters or end in an underscore?

Unless you're implementing SQL-1992's "Full SQL" (which is the 1992 spec), you should not have identifiers with more than 18 characters.

SQL 1992 defines three levels of compatibility,

Entry SQL includes the statements for defining schemas, data ma- nipulation language, referential integrity, check constraints, and default clause from ISO/IEC 9075:1989, and options for module lan- guage and embedded SQL interfaces to seven different programming languages, as well as direct execution of the data manipulation statements. It also includes features related to deprecated fea- tures from ISO/IEC 9075:1989 (commas and parentheses in parameter lists, the SQLSTATE parameter, and renaming columns in the ), features related to incompatibilities with ISO/IEC 9075:1989 (colons preceding s, WITH CHECK OPTION constraint clarifications), and aids for transitioning from ISO/IEC 9075:1989 to this International Standard (s).  Finally, it contains changes to correct defects found in ISO/IEC 9075:1989 (see Annex F, "Maintenance and interpretation of SQL").
Intermediate SQL includes major new facilities such as statements for changing schemas, dynamic SQL, and isolation levels for SQL- transactions. It also includes multiple-module support and cascade delete on referential actions, as well as numerous functional en- hancements such as row and table expressions, union join, character string operations, table intersection and difference operations, simple domains, the CASE expression, casting between data types, a diagnostics management capability for data administration and more comprehensive error analysis, multiple character repertoires, interval and simplified datetime data types, and variable-length character strings. It also includes a requirement for a flagger facility to aid in writing portable applications.
Full SQL increases orthogonality and includes deferred constraint checking and named constraints. Other technical enhancements in- clude additional user options to define datetime data types, self-referencing updates and deletes, cascade update on referen- tial actions, subqueries in check constraints, scrolled cursors, character translations, a bit string data type, temporary tables, additional referential constraint options, and simple assertions.

At the top you're quoting "Entry SQL" which though stricter is a "low" level of SQL conformance with the 1992 spec. This mode was for backwards compatibility with SQL:89 and the spec is pretty explicit

  ... [boring stuff] aids for transitioning from ISO/IEC 9075:1989 to this International Standard (s).

While I couldn't personally dig up the SQL-89 standard, I assume it does have an 18 character limit on identifiers.

The other part is alarming to me, because I believe this shouldn't apply to Intermediate SQL from the spirit of the spec which is defined as,

But none the less, the spec is the letter of the law. Clearly even in Intermediate SQL the <regular identifier> and <delimited identifier body> should not be more than 18 characters.


MySQL export user with semi-colon ";" on the end I am trying to set up automatic export of user accounts from one server to import into another. I am running the following command;

$ mysql -u root -p -B -s -e "show grants for 'myuser'@'localhost';" > user_export.sql

$ cat user_export.sql

GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*IHIHIHOOEHOEHOH'
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'myuser'@'localhost'

The problem here is that these SQL statements don't end with ; so I can't import it on another remote server using:

$ mysql -u root -p -D mydb < user_export.sql

I know I could write bash one-liner to wrap this and add a semi-colon but I am hoping there is  away to get these statements with the semi-colon on the end to keep this nice and simple, and clear.

In addition to the fact that there's not any "official" way to do this, there's another potential problem with the approach: there's no mechanism that will ever revoke any privilege for a user.

Today, user 'foo' at host 'bar' has SELECT, UPDATE, and DELETE.

mysql> SHOW GRANTS FOR 'foo'@'bar';
+-------------------------------------------------------------------------------+
| Grants for foo@bar                                                            |
+-------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE ON *.* TO 'foo'@'bar' IDENTIFIED BY PASSWORD '*' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Oops, they shouldn't have delete, so you fix that.  Tomorrow's file contains the correct grant statement:

mysql> GRANT SELECT, UPDATE ON *.* TO 'foo'@'bar';
Query OK, 0 rows affected (0.00 sec)

After that's processed, what privileges does the user have?

mysql> SHOW GRANTS FOR 'foo'@'bar';
+-------------------------------------------------------------------------------+
| Grants for foo@bar                                                            |
+-------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE ON *.* TO 'foo'@'bar' IDENTIFIED BY PASSWORD '*' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The GRANT statement is additive, so it only specifies what privileges should be added if not present.

I would think, then, that a better approach might be what @Mat was (I think) implying... you should script the privilege changes you want to make, then execute that script against all of the servers you want to update, rather than trying to treat one server as permissions master.


Fastest way to update column value I need to set all values of a given column foo to a constant varchar bar. Is there a faster way to do that than with the vanilla UPDATE mytable SET foo = 'bar'?
Facts:

This is an Oracle MySQL 8.0.20 server (via SELECT @@VERSION)
This table uses the InnoDB engine
The column is not part of any index
The column datatype is varchar(2000).
I can't change the datatypes
I have full access to the server and could manipulate the .idb files directly, if necessary.
The largest table has ~1.7mi records and the regular update takes ~30mins
Memory, CPU and storage doesn't seem to be a bottleneck, so far (checking iotop and htop itself)

Note: I need to do similar updates to multiple columns in multiple tables, but what I describe here applies to them all, except for the datatype, which is the worst case (all the other columns are also varchar, but not that long).

Changing 1.7M rows in a table requires making copies of all the rows that are changed.  (This is in case there is a crash or ROLLBACK.)  The system is not designed to handle huge changes like that.
It may be faster to do the UPDATE in chunks, COMMITting after each chunk.  This way the undo stuff is not huge.
I discuss techniques for walking through a table via the PRIMARY KEY in http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks .  That blog is worded in terms of DELETEs, but UPDATEs can use the same techniques.
Side notes.  Some of these may lead to other solutions.
Do not even think of touching the .ibd file.
You do have a PRIMARY KEY, don't you?  If not, that is bad.  And it makes the "walking" that I describe less efficient.
Please provide SHOW CREATE TABLE and the actual UPDATE if you want to discuss things further.
In general, it is "bad schema design" to need to change every row of a large table.  There are much more efficient techniques.  One that comes to mind is to have a second table that contains only one row -- with the current value of foo.  However, it may or may not work for your use case.
What is your use case?  Do you change all the rows?  Or does that UPDATE usually change only a few rows?  How many different values?  Is it a flag, a date, or what?  Etc.
You have VARCHAR(2000); is the contents typically large?  How big is the typical record?  These questions lead into discussing "off-record" storage of large columns.
How much RAM do you have?  What is the value of innodb_buffer_pool_size?  What is the table size (SHOW TABLE STATUS LIKE 'mytable';)


Best way to copy data to other location without blocking source I have a production database with some data I need to copy to other location, either another database on the same server or another server.

The DB size is huge and I don't need all the data on the DB, but for instance in 1 of the tables with ~300 million records I need just ~60 million out of it.

The issue is that might not be possible to restore the entire DB in the same/another server because of space restrictions, so we might be forced to only copy what is needed.

And the other restriction is that these tables are used by production machines, so they need to be available 100% of the time, so I can't block any write and read access to them.

So, what would be the best way to copy partial data from one DB to anoter?

To run a query that inserts N records until it is done? kind of INSERT INTO ... SELECT TOP N ... FROM ... WITH(NOLOCK) WHERE ...
Use the Import and Export data? Will this tool block the source tables when running?

This depends a lot on your goals / requirements for the data in the destination.  Specifically you need to weigh the pros and cons between blocking and data correctness.
Avoid Blocking — READ COMMITTED with SELECT...INTO
For instance, the most naive solution under the default READ COMMITTED isolation level would solve your problem with very little blocking on the source server:

[Optional, if the database is on another server] Create a linked server on the destination (SQL Server 2008) instance that connects to the source (SQL Server 2016) instance
Run a SELECT...INTO statement to copy the desired 60mm rows

SELECT /*column list*/
INTO [dbo].[BigTable]
FROM [SourceDatabase].[dbo].[BigTable]
WHERE /*predicates to get the 60mm rows you need*/

Note: you'll need to specify the linked server name in the FROM clause if the linked server approach is used
Shared locks will be taken and released as pages are scanned in the clustered index (or heap, as the case may be), so there will be brief moments of blocking specific pages, but nothing that should be incredibly noticeable for your production system.  Of course, there might be other points of resource contention (storage throughput, for instance).
The main problems with this approach is that it makes basically no guarantees about the correctness of the data that will arrive at the destination.  There's a Great Post™ by Paul White about The Read Committed Isolation Level that you can check out for the details, but the main problem is that this doesn't guarantee a point-in-time view of the data.
In other words, DML statements (INSERTs, UPDATEs, and DELETEs) can successfully execute during the scan, which could result in rows being read more than once, rows being missed entirely, etc.
A potential problem for the destination database would be log file growth.  If the size (not just number of rows) of the data being moved is very large, you may need to consider batching the inserts rather than doing them all in one go.
Favor Correctness - More Restrictive Isolation Levels
If you want the data that arrives in the destination to provide data that is consistent with a specific point in time, you can use the SNAPSHOT or SERIALIZABLE isolation levels during the entire insert process (the actual insert process could be the same as or similar to the one described above).
The SNAPSHOT isolation level is a viable option.  It will cause an increase in tempdb usage as row versions are maintained to provide a consistent view of the database, so you need to make sure you system can handle that workload.  It will also cause some types of blocking (more than READ COMMITTED, but less than the SERIALIZABLE approach).  How much this will affect your production workload would be dependent on you to test.
The SERIALIZABLE isolation level will be the most disruptive to your production workload, as it will block all access to the table until the transaction is complete.  This doesn't meet your requirements, so it's not really a viable option.
Correctness AND No Blocking - Replication / Backups
If you're able to restore a full database backup somewhere, that's the ideal solution here.  You have a point-in-time view of the database, without contention, from which you could then copy the needed rows to the destination.  Since you mentioned database size / space is an issue, I imagine this isn't a viable option.
Due to the special requirements for your situation, you might want to consider buying a third-party tool that supports "object level restores."  This would allow you to restore just that needed to table to the destination directly from a backup.  I think that Quest Litespeed supports this, although it's hard to tell behind the paywalls on their site.
Transactional Replication might also be a viable option if you need the data to be continuously up to date, although the version restrictions might prevent you from being able to leverage this option.  Additionally, the maintenance and setup overhead might be impractical.  I haven't used transaction replication in practice, so I'm just mentioning it as a possibility.
About NOLOCK
Don't use that.


MongoDB scripts triggered by cron - how to achieve reasonable password security We would like to do nightly purging of old data out of our mongo cluster. First thought is to write shell script which connects to one of the mongos and issues a remove instruction.

evalString='db.fs.files.remove({uploadDate: {$lte : ISODate("2017-01-01")}})'

mongo localhost:1234/databaseName -u USER -p PWD --eval $evalString

This works fine but of course I don't like to have plaintext PWD in scripts.  At a minimum could chmod 500 on it but still feels sub-optimal to have plain text pwd in script.

What are the best alternatives?

I'm running Mongo 3.4.4 on RHEL 7.3. It's the non-Enterprise version of Mongo so no LDAP/Kerberos.  we are not using x509 security either and I'm looking for alternatives that don't involve upgrading to Enterprise or going down x509 route, although I'd be fine with hearing why doing those things really is the right thing to do and also without doing those things what the gaps are that get left open.

First.. It's better to use --eval='command' and not echo a string to mongo... What comes to that password, you could use -p $(cat /etc/secret/password) and files chmod is like 0400