SQL Count function returning inaccurate results

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 speed up SSIS data load from Oracle database?

You should get some significant performance improvement using the FetchSize connection string property. The Oracle documentation for the max value is here.

  FetchSize - specifies the number of rows the provider will fetch at a
  time (fetch array). It must be set appropriately depending on the data
  size and the response time of the network. If the value is set too
  high, this could result in more wait time during the execution of the
  query. If the value is set too low, this could result in many more
  round trips to the database. Valid values are 1 to 429,496,296. The
  default is 100.

But mainly I would recommend upgrading to SSIS Enterprise edition and using this connector. Here is the one for SSIS 2014 but you can search for other versions easily. The performance of that connector is much improved.


duplicate key error creating unique index on temp, SQL Server 12.0.5589 bug?

When using the READ UNCOMMITTED isolation level, it is possible for SQL Server to read the same data twice - causing you to get duplicate results, even if there is a unique constraint ensuring there are no duplicates of that data in the table.

Looking at some of the comments on the question:

  Thanks, the general consensus seems to be the iso level, but keep in mind, i've probably used that same basic query above in hundreds of places, and have never seen this behavior before

This won't happen all the time, and can depend a lot on whether the source table is being updated, the physical operations SQL Server chooses to use to read the data, and the nature of the query being run.

  I will change the iso level and watch it (for possibly a year) to see if we get the same error in the future

That's great, changing the isolation level is your best option for preventing this problem!

When you have time, there is some great information out there on the issues around using the READ UNCOMMITTED isolation level:

Paul White's post (in a series about isolation levels): The Read Uncommitted Isolation Level
A blog post from Brent Ozar with a very clear demo of how results can change under READ UNCOMMITTED: Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.


Optimize architecture for SQL Server projects

You could create a script outside SQL Server (Powershell, VBScript, console app) which does away with your text files. The script would read input data from SQL, call the external utility and write the result directly back to the database.

If it was something written in C# then you have all the handy source control, automated tests stuff. Unfortunately, using SQL to perform external processes is possible, but painful. Sometimes you just need to chuck it in and write some middleware (in the middle of SQL and the external tools) to do the job for you.

For example, I wrote a VBScript that extracts a list of all tables from the DB then searches through .dtsx files to find them, then writes the result directly back to the database.

You may also be able to use SSIS and SQL Agent to simplify your process. SSIS is good for defining orchestration and data transfer in a visual way, though for more complex external things you often need to resort to C# script tasks. SQL Agent can be used to run SQL and command lines in order with basic workflow etc.

Community Wiki answer created from question comments by Nick McDermaid


Force MATCH to return a certain number of results

Full Text Indexes don-'t work that way.

They find whole Words and combinations of those
Use instead the normal comparison LIKE to find your rows

The ORDER BY and the LIMIT have to be in the (),because mysql interprets them as belonging to the complete query (SELECT UNION SELCT)

SELECT
    *
FROM
    Entries
WHERE
    `Title` LIKE ('foo%')
UNION
(SELECT
    *
FROM
    Entries
WHERE
    ID NOT IN (SELECT ID
               FROM
                 Entries
               WHERE
                `Title` LIKE ('foo%'))
ORDER By Rand()
LIMIT 4)
;

ID | TITLE
-: | :-----------
 3 | Foo
 5 | Foobar
 2 | I am a title
 4 | Bar
 1 | Carl
 6 | Title

SELECT
    *
FROM
    Entries
WHERE
    `Title` LIKE ('foo%')
UNION
(SELECT
    *
FROM
    Entries
WHERE
    ID NOT IN (SELECT ID
               FROM
                 Entries
               WHERE
                `Title` LIKE ('foo%'))
ORDER By Rand()
LIMIT 2)
;

ID | TITLE
-: | :-----
 3 | Foo
 5 | Foobar
 4 | Bar
 1 | Carl

db<>fiddle here


"Could not insert a backup or restore history/detail record in the msdb database" While restoring msdb

Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB0E391C95'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (60979).

This might happen if someone have done a reseed on the dbo.backupset.

From table definition :

PK__backupse*  --> clustered, unique, primary key located on PRIMARY --> backup_set_id

To fix that error :

--- get the max backup set id from the backupset table

    SELECT MAX(backup_set_id) + 1 FROM msdb.dbo.backupset

 --- then reseed it

   dbcc checkident('dbo.backupset', RESEED, VALUE_FROM_ABOVE)

Now run DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS to see if everything returns fine.

  Msg 8921, Level 16, State 1, Line 1
  Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

Try running DBCC CHECKDB with TABLOCK hint + make sure that the drive tempdb resides has enough space and tempdb is not having restrictive growth (autogrowth OFF).

Lastly,

  Msg 2515, Level 16, State 1, Line 1
  The page (1:1044), object ID 60, index ID 1, partition ID 281474980642816, allocation unit ID 281474980642816 (type In-row data) has been modified, but is not marked as modified in the differential backup bitmap.
  CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysobjvalues' (object ID 60).

If you run DBCC CHECKDB('msdb') WITH TABLOCK, NO_INFOMSGS, ALL_ERRORMSGSand still it gives error, then its most likely a problem with your IO subsystem. Try to check windows event logs.

Have Page checksums enabled. Also, take a full backup of msdb (which will reset differential bitmap) and then run DBCC CHECKDB('msdb') WITH TABLOCK, NO_INFOMSGS, ALL_ERRORMSGS

In an unlikely event, you should try going backward and find a good backup to restore.


how to check if a stored procedure has been called from another stored procedure in mysql?

ok .I found it with select query

SELECT COUNT(1)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%procedureName%'
AND ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA = 'dbName';


Why is column value going negative even with checks

The error cause is a race condition that is happening, because your query was not immune to it.
There are ways to code your queries to avoid it (atomic update, transactions, etc.).
Because you did not use table locking or transactions, you ended up with an error when your query read an updated value from another thread. This produced a negative value.

Proposed solution:

Issue both SELECT and UPDATE in a single statement to avoid race condition, as you have tried, however, select from underlying table into a session variable.
That way, you can have control of the time when you select a value, and not get value from another thread.

I have rewritten your code to use user-defined, session variables.

Update 1 rewritten:

-- @blnc and @lckd are user-defined variables
UPDATE tablename
SET locked = (@lckd + x)
WHERE id = 1
    AND ((@blnc := balance) - (@lckd := locked)) >= x;

Update 2 rewritten:

-- @blnc and @lckd are user-defined variables
UPDATE tablename
SET balance = (@blnc := balance) - x
    , locked = (@lckd := locked) - x
WHERE id = 1;


MySQL: Adding innodb_log_file_size in my.cnf causes not starting MySQL service

To change innodb_log_file_size do following

Stop MySQL gracefully. Check the error log and make sure it was "Normal shutdown", no crashes, no errors etc.
Move existing ib_logfile* to safe place. You will need them if you want to roll back
Change innodb_log_file_size in my.cnf to the new value
Start MySQL. InnoDB will notice there are no ib_logfile* and will create new ones


It is easy to do this by user, but not by application name.  Does/can patroni log in as it's own user?

alter user patroni set log_statement to none


Execute `sp_spaceused` for all tables in all databases

Use dynamic SQL with a cursor (for these cases it is ok)

Here is the code:

DECLARE @DBName varchar(200)
DECLARE @CMD varchar(max)
DECLARE @TV TABLE ( name_table varchar(500),ROWSCOUNT int, reserveder varchar(50), datasize varchar(50), indexsize varchar(50), unused varchar(50))
DECLARE @TV2 TABLE ( DBName varchar(200),name_table varchar(500),ROWSCOUNT int, reserveder varchar(50), datasize varchar(50), indexsize varchar(50), unused varchar(50))
DECLARE @TV3 TABLE ( DBName varchar(200),SchemaTAble varchar(500), TableName varchar(200))
DECLARE C Cursor for select quotename(name) as dbname from sys.databases where name <> 'tempdb'  and state = 0

OPEN C

FETCH NEXT FROM C INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE FROM @TV3
    SET @CMD =
    @DBName+'..sp_msforeachtable ''exec sp_spaceused [?]'''
    insert into @TV
    EXEC (@CMD)

    SET @CMD = 'select '''+@DBName+''' as DatabaseName, quotename(s.name)+''.''+quotename(t.name) as SchemaTable, quotename(t.name) TableName from '+@DBName+'.sys.tables t inner join '+@DBName+'.sys.schemas s on s.schema_id = t.schema_id '
    insert into @TV3
    EXEC (@CMD)

    insert into @TV2
    select @DBName ,t.name_table,t.ROWSCOUNT,t.reserveder,t.datasize,t.indexsize,t.unused from @TV t
    inner join @TV3 s on quotename(t.name_table) = s.TableName and s.DBName = @DBName

    FETCH NEXT FROM C INTO @DBNAME
END
CLOSE C
DEALLOCate C

select * from @TV2

There is a difference in usage for sp_spaceused in SQL Server 2016 and SQL Server 2012. Note that this code will only work for SQL Server 2012.


How to return resultset from MySQL Stored Procedure using prepared statement?

I will suggest you a solution create a log table like this

CREATE TABLE log_records
(
Panel_Id INT,
Project_Number_val VARCHAR(255)
);

In your code add lines as follows here i will insert those record into log_table which you was selecting please look the line /**Added by abdul */

    IF FoundCount = 1 THEN
        SET @Project_Number=Project_Number_val;

        SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');

    /*--Added by Abdul*/
        SET @insert_sql = CONCAT ('INSERT INTO log_records' ,'SELECT Panel_Id,', Project_Number_val,'    FROM ', @Project_List_val,' Where status=1');
        PREPARE stmt_insert FROM @insert_sql;
        EXECUTE stmt_insert;
        DEALLOCATE PREPARE stmt_insert;
    /**--End**/

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;

And then where loop end add line

SELECT * FROM log_records;

This will give you the desired result.

Also Write one line in your code before you open the cursor otherwise each procedure call will be appending data in that table.

TRUNCATE TABLE log_table;


Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output

What language is the client written in?  You should test for errors after every statement (including the COMMIT) in every transaction.  That is the way to locate the problem.  SHOW ENGINE InnoDB STATUS should not, as you see, be depended upon.  More significantly, it won't necessarily catch all deadlocks.


Rename all databases when doing a mongorestore?

Should be this one:
mongorestore --nsInclude="PetHotel.*" --nsFrom='PetHotel.*' --nsTo='PetHouse.*' dump/

mongorestore --nsInclude='*.*' --nsFrom='$database$.*' --nsTo='$database$2.*' dump/

First test it with option --dryRun
See Change Collections' Namespaces during Restore


How to properly implement relationship between a user and multiple other users

It seems what you're describing for Table_2 is a linking table for a self-reference for Table_1. In the case where there is only one possible reference for each UserID, you would store this information on Table_1 itself (UserID and ReferenceUserID with a foreign key back to UserID).

What you have described, however, is a many-to-many relationship, where a UserID can have many related UserIDs and vice-versa. In this scenario, Table_2 should not use a string holding all of the related userIds for your principal. There are several reasons for this, but the two most important are:

Performance will be terrible when querying because you will have to do string manipulation to split the CSV string into rows, then join on those extracted IDs
You will not be able to store information about individual relationships.

To illustrate #2 (using hypothetical use cases for the table), say this database holds information about family relationships. Users can be associated with 0 to many other users in their family tree. If you want to store information about each relationship (not DB relationship, but IRL) then each relationship should be a separate row.

For example, Dave has a UserID of 1. Pete is Dave's brother and has a UserID of 5. In Table_2, you would store Dave's UserID in the UserID column and Pete's in the OtherID column. You can then add fields to record the relationship (Brother) and other information (maiden name for marriages etc).

With the CSV column for OtherID, you could not efficiently store this information for the potentially many relationships a single UserID might have.

Therefore, consider this as the base design for the second table:

CREATE TABLE Table_2
(
  UserID          int FOREIGN KEY REFERENCES Table_1 (UserID),
  ReferenceUserID int FOREIGN KEY REFERENCES Table_1 (UserID),
  PRIMARY KEY (UserID, ReferenceUserID)
);

You can add other columns to this setup to store additional details about each relationship, as discussed above.


Question is why there is no key lock on clustered index in second query ?

Because the only other column in that table is the clustered index key.  And a non-clustered index always contains the clustered index key columns.

So your non-clustered index on col2 is a covering index for the SELECT * query.


Windows user automatically gets db_owner?

From Comments:

This issue could occur when there is no owner listed under properties --> files. Once you populate the owner with a valid login, this issue is gone and db_owner is not granted automatically.

Same can be done using sp_changedbowner.

Please mark it as accepted answer so this could help someone else facing this or similar issue on DBA.SE forum.


You could probably achieve #1 with "\i" directives to import snippets. I wouldn't advise it, though, I'd use a Makefile to concatenate the desired chunks.

It's as simple as something like:

myext--1.0.sql: myext-types.sql myext-tables.sql myext-views.sql
    cat $< $@

Note that the indent must be a tab, not spaces.

There's lots of good info out there on writing basic Makefiles, so I won't go on further.


Will an update in my trigger trigger itself? Oracle PLSQL

My proposal would be this one:

CREATE TABLE UNIQUE_PHYSICIAN (
    PARENT_PHYS_ID NUMBER,
    PHYS_ID        NUMBER CONSTRAINT PHYSICIAN_PK PRIMARY KEY);

ALTER TABLE DPISYS.UNIQUE_PHYSICIAN ADD CONSTRAINT UNIQUE_PHYSICIAN_PARENT_FK
   FOREIGN KEY (PARENT_PHYS_ID) REFERENCES DPISYS.UNIQUE_PHYSICIAN (PHYS_ID);

CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG
    BEFORE UPDATE OF PHYS_ID ON UNIQUE_PHYSICIAN
    FOR EACH ROW

BEGIN
    :NEW.PHYS_ID := :OLD.PHYS_ID;
END;

Then the root element(s) is/are the record(s) where PARENT_PHYS_ID IS NULL.

For PHYS_ID, resp. PARENT_PHYS_ID you should use a surrogate key, preferable generated from a sequence. In this case there is no reason (and no possibility) to change them ever.

The root Id you could get with this query:

SELECT p.*, CONNECT_BY_ROOT PHYS_ID AS ROOT_ID
FROM UNIQUE_PHYSICIAN p
START WITH PARENT_PHYS_ID IS NULL
CONNECT BY PRIOR PHYS_ID = PARENT_PHYS_ID;


You can point pgAdmin4 at your PostgreSQL server, it has a dashboard you can use. You can then drill down in a session to see the query.
If thats not what you are looking for then I guess a logging solution would be what you need like pg_stat_statements.


Remote mysql connection fails with accessed denied exactly every other attempt

Had to use ip of host instead of hostname. Problem was dns related but the way that it manifested still confuses me.


Predicate not resulting in expected scan

Your query is looking for values between 2015-04-25 AND 2015-05-30.

Of which it looks like you have about 30 million.

Unfortunately there is a bug with the cardinality estimations where DATEDIFF is involved where the components get reversed.

SELECT DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, -30) ,
       DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, 0) + 5

Returns a range of 1784-05-15 to 1784-06-19. Extremely likely you have no dates in that range in the table (or statistics histogram) so SQL Server will estimate that no rows will be returned out of the scan, and certainly no need for 30 million lookups.

This is a fixed bug but requires trace flag 4199 enabled.

If that isn't possible you could try rephrasing the predicate in a different way that avoids the use of DATEDIFF or assigning the values to variables and using OPTION (RECOMPILE)


You said you didn't want to add an extra column to a number of tables that need to link back to Test1 in this way (ie on ThingA, 3).

How about adding one persisted computed column to TestA that shows the value ThingA if ThingB is 3, and null otherwise?

Then your foreign key references the new column only, based on ThingA in the referencing table.

alter table Test1 add SpecialThingA as
    (case ThingB when 3 then ThingA else null end) persisted;

and

FOREIGN KEY (ThingA) REFERENCES Test1 (SpecialThingA)

In other words - one new column on Test1, rather than a new column on Test2 (to hold '3') and Test3, and ....


Large archive table with "unimportant" data, how to improve/redesign?

It would be good to know where that 5 seconds goes. Is it disk, network, memory or CPU?

Guessing it's disk there are ways to speed this up. One is to get faster disk: flash or NVMe would be nice! Another, more plausible solution, is to compress the data so less IO is required for the same information content. SQL Server supports several options. Also look into clustered columnstore indexes. These are specifically designed to efficiently store and process large amounts of data.

Looking outside SQL Server it sounds like you have time-series data. There are many DBMS specifically tailored to this use-case.

Before you go that far you may look into refactoring your application architecture. Since the data is write-once you can pre-process, or at least cache, any query against it. What visualisations do you use? Often data is aggregated so, say, min, max and average per hour/ day/ minute are shown. If this is your case you can pre-calculate these aggregates, store them and remove the detailed data. If you use n-tiles, candlesticks or line charts, calculate the corresponding SVG, PNG or JPEG once for each interval, and stitch them together to make larger charts. A lot of this store-and-stitch can be done in the file system or CDN avoiding load on the DB server at all.


You have probably encountered a bug caused by your default server charset

BUG #74192


SQLite can resize field values and records dynamically, so there are no restrictions on updating strings (except for the normal limits).


From

  An Azure storage account to use for storing Automated Backup files in
  blob storage. A container is created at this location to store all
  backup files. The backup file naming convention includes the date,
  time, and database GUID.

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/automated-backup

If you turn that feature off in the portal, you can still use the underlying engine feature SQL Server managed backup to Microsoft Azure, which allows you to specify the container.


250 billion rows - computing interconnectedness on a really huge scale

Short answer:  it depends.

Long answer:  the answer depends on several factors.  Here are a few:

Does your 700K row table get updated infrequently?  If so, I would lean toward pre-calculating the means.  If they are not static but change in a regular, predictable interval (e.g., monthly or yearly), pre-calculating may still be a solid answer.  If they change often or unpredictably, pre-calculating becomes harder to justify.  You do mention that the number of rows added per year is relatively small compared to the total.
Do the existing item numbers ever change?  If not, that is a factor in favor of pre-calculation.  If they do change, pre-calculation becomes a little harder to justify, depending upon the frequency of change.
Do you have the disk space available?  Figure that you have 250,000,000,000 rows.  Your association table would have two bigints (8 bytes apiece) and one decimal(8,7) (5 bytes apiece), or 23 bytes per row.  For 250 billion rows @ 23 bytes per row, you're looking at a bit over 5 TB.  You'll want to factor in disk space for the transaction log, database backups, possibly having development/QA/staging environments, (probably) having non-clustered indexes on the table, etc.  Depending upon your database product, you could have compression reduce that by a fairly significant amount, but I'd say you would probably want at least 15-25 TB of disk space available before materializing; if you can't get that much disk space, then materializing the relationship just doesn't work.  Even if you generate a surrogate 32-bit integer key for each of the 700K products, you're looking at ~13 bytes per row, or 3 TB for that table (plus additional bits).
How time-sensitive is this process?  I don't know how long a regular run would take when you calculate it as part of the script, but if these jobs are something that can run in the background or overnight, then that would be a factor against pre-calculating.  Even if you do pre-calculate, you're likely going to scan the 250 billion row table to get your answer, so performance won't be zippy in either event.
How often do these requests come in? If it's once a month, that argues against pre-calculating.  But if requests come in daily (or several at once), pre-calculation might be the better option.
What else is this server used for?  If you're running this from an active machine, you probably do not want to pre-calculate and select from a big table.  The reason is that scanning 5 TB of data would drive pretty much everything else out of memory, hurting the performance of everything else.  If, on the other hand, this is a research server, development server, or you don't mind slow performance from the server in the aftermath of running one of these queries, then pre-calculation wouldn't be a big deal.
What is the frequency of item repeats in requests?  In other words, does item 23920390293 (for example) show up in a lot of reports, or are the separate requests essentially distinct?  If you see common elements, that argues in favor of pre-calculation (because then you only have to calculate once), but if the requests tend rarely to repeat numbers, you don't gain as much from pre-calculation.

If you do keep this in a SQL table, I'd recommend against having a blob.  I don't think you would get much (if any) size gain over a compressed table, and you might take a performance hit.  Even if you use a separate server to perform application-level calculation (instead of doing it in the database), I think it still makes sense to keep the table normalized.

Also, if you have all the disk space in the world, you might want to keep both sides (distance between A and B, and distance between B and A).  You double the amount of disk space required, but make the query that much simpler:  one join between your item list and the master item relationship table, rather than two separate queries (and the additional difficulty of ensuring that you have all of the relationships between the tables).


Tell your sys admin to search for "RAID is not a backup", whether it will provide a usable backup is irrelevant, since it doesn't protect you from database corruption, human error, and many other things.


Percona xtradb cluster node2 won't start

The log entries

2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib/galera3/libgalera_ssm.so: cannot open shared object file: No such file or directory
2015-06-08 18:43:03 5977 [ERROR] WSREP: wsrep_load(/usr/lib/galera3/libgalera_ssm.so) failed: Invalid argument (22). Reverting to no provider.

show that MySQL cannot find the file /usr/lib/galera3/libgalera_ssm.so so it cannot use it as a WSREP provider.  Check that this file exists and is accessible.  If your system is 64-bit, it might be in /usr/lib64/.

Also please post the error message you get when starting the node -- without reading the exact error message it's a shot in the dark.


The Microsoft definition of this wait type is:

  Occurs when a task is waiting on a latch for a buffer that is in an
  I/O request. The latch request is in Shared mode. Long waits may
  indicate problems with the disk subsystem.

As mentioned above, excessive PAGEIOLATCH_SH wait types don’t mean necessarily that the I/O subsystem is the root cause. It can often be some other reason, such as: bad index management, memory pressure, synchronous mirroring and AlwaysOn AG, logical/physical drive misconception, network issues/network latency, overloaded I/O subsystem by another processes that are producing the high I/O activity.

You may want to try some of the following to resolve having excessive PAGEIOLATCH_SH wait type values:

Keep in mind that in case of high safety Mirroring or synchronous-commit availability in AlwaysOn AG, increased/excessive PAGEIOLATCH_SH can be expected
Check your SQL Server queries and indexes as very often this could be found as a root cause of the excessive PAGEIOLATCH_SH wait types
Check the memory pressure before jumping into any I/O subsystem troubleshooting

If more details on this wait type are needed, including real-word situations that are causing this wait type values to be excessive, take a look at the Handling excessive SQL Server PAGEIOLATCH_SH wait types article.


MySQL Cluster supports storing non-indexed columns on disk-only with an LRU cache of recently accessed data.  However indexed columns are always held in-memory.

MySQL Cluster preallocates all memory, according to the DataMemory and IndexMemory parameters.  It will not ask the underlying OS for more memory dynamically.

This means that you need to have configured enough memory across your cluster to hold all indexed columns in memory.  If your dataset is large enough that the indexed columns are larger than the available cluster memory, then you cannot load that dataset into the cluster.  At some point you will run out of space, and your inserting transactions will be aborted.

When configuring DataMemory and IndexMemory, it is best to limit yourself to somewhat less than the physical memory in each system.  Some physical memory should be reserved for the OS and other processes.

Theoretically MySQL Cluster can be configured so that it uses virtual memory via a swap device (e.g. more than physical memory), but as the other answers state, this is not a designed-for use case.  Having in-memory structures swapped to disk is usually sub-optimal as in-memory random access patterns result in random access to the disk, resulting in swap thrashing and slowdown across the system.  With MySQL Cluster, the most likely outcome is heartbeat failure and cluster failure due to a swapping data node not responding to signals quickly enough.

To efficiently support larger-than-aggregate-memory indexes, MySQL Cluster would need to support on-disk index formats (perhaps a B tree etc) with caching and access patterns aligned with disk access properties.


I think the issue is due to the choice of the clustered index. From MySQL docs, Clustered and Secondary Indexes:

  Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.

Also check the answer by @marc_s in this SO question: How to choose the clustered index in SQL Server?, where he mentions:

  According to The Queen Of Indexing - Kimberly Tripp - what she looks for in a clustered index is primarily:

  Unique
  Narrow
  Static

  And if you can also guarantee:

  Ever-increasing pattern

  then you're pretty close to having your ideal clustering key!

Now, your clustered index is the (Primary Key):

hash varchar(5) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,

which (lets go through the check-list) is:

Unique  (yes, OK)
Narrow  (yes, OK)
Static  (perhaps, you know that)

but is probably not:

Ever-increasing pattern (No, it probably isn't)

So, what happens when you use a non-ever-increasing clustered index?

I can't answer better than Kimberly L. Trip: Ever-increasing clustering key - the Clustered Index Debate..........again!

  If the clustering key is ever-increasing then new rows have a specific location where they can be placed. If that location is at the end of the table then the new row needs space allocated to it but it doesn't have to make space in the middle of the table. If a row is inserted to a location that doesn't have any room then room needs to be made (e.g. you insert based on last name then as rows come in space will need to be made where that name should be placed). If room needs to be made, it's made by SQL Server doing something called a split. Splits in SQL Server are 50/50 splits - simply put - 50% of the data stays and 50% of the data is moved. This keeps the index logically intact (the lowest level of an index - called the leaf level - is a douly-linked list) but not physically intact. When an index has a lot of splits then the index is said to be fragmented. Good examples of an index that is ever-increasing are IDENTITY columns (and they're also naturally unique, natural static and naturally narrow) or something that follows as many of these things as possible - like a datetime column (or since that's NOT very likely to be unique by itself datetime, identity).

Note that despite the mention of SQL-Server, the same concept applies to InnoDB clustered indexes as well. I suppose that the clustered index has 2 issues:

When you are inserting a new row (the "random" hash guarantees that) it gets inserted in a random location of the index. This means that it sometimes will find no space there available to be inserted (note that InnoDB always leaves some space free in the index but when that free-available space is filled) there has to be some rearrangement of the index - and that takes time.
What the rearrangement is also causing over time is fragmentation of the index. Which will eventually make other queries and statements slower.


Do i need streaming replication slots as many of replicas in postgres?

Each replication slot requires a unique primary_slot_name in the recovery.conf, so you will need to have 4 connections for a streaming replication slot.


Is there a standard language/interface for programmatic ETL in SQL Server?

There is a tool that enables this - http://www.varigence.com/products/biml.html

There's a commerical version, but we also include some of the BIML functionality in BIDS Helper, a free tool. http://bidshelper.codeplex.com/

I'm happy to answer any questions that you might have about it.

This is a tool that my company provides.


How to force MySQL master to overwrite the slave

I use nightly lvm snapshots of production databases that are made static by removing the files needed for replication (master.info, relay-logs), and making that snapshot writable. You'll need to shut mysql down for a few minutes to make the snapshot and have room on the machine to hold that snapshot, but it's an easy and elegant solution.


MySQL: Is there a better way to do this query?

if add indexes:

CREATE TABLE `table_b` (
  `S_ID` int(11) DEFAULT NULL,
  `C_ID` int(11) DEFAULT NULL,
  KEY `S_ID` (`S_ID`,`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `table_a` (
  `id` int(11) DEFAULT NULL,
  `S_ID` int(11) DEFAULT NULL,
  `C_ID` int(11) DEFAULT NULL,
  KEY `S_ID` (`S_ID`,`C_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and little modify the query:

set @v_npref = 50;

SELECT ca.id, ca.S_ID, ca.C_ID
FROM TABLE_B AS clc
JOIN TABLE_A AS ca ON (clc.S_ID = ca.S_ID AND clc.C_ID = @v_npref )
WHERE
ca.C_ID IS NOT NULL OR NOT EXISTS (SELECT id FROM TABLE_A ca2
                                 WHERE ca2.S_ID = ca.S_ID AND ca2.C_ID = @v_npref )

group by ca.S_ID;

we can compare plan of new and original queries,
original:

modified:

this is just my variant, what better 2 FULL scan (as You can see total number of scanned rows bigger) or file sort at the final possible decide only on real data (depend from size of sorted data)


Why doesn't pg_dump include all database SETtings?

Database (and also role) settings are stored in pg_db_role_setting. Like pg_database, there is only one copy of pg_db_role_setting per cluster. So those settings are not stored in database. They are stored globally.

Try to add --create parameter to your command. With this, database create  script will be added at the beginning of the dump file with the configuration.


You are asking some good questions here but you are at an important crossroads and undoing an architectural mistake after implementing it is extremely difficult and costly.

The first question you should ask yourself is do you really need a multi-master topology? Most organizations and applications function well with MySQL asynchronous replication with a failover script in the event failover is necessary.

If you decide a multi-master topology is the right choice, you need to confirm that your application and requirements are a fit. I see a red flag already with this.

  I have 3 Geographically distributed servers which I want to sync them.

WAN replication with multi-master solutions like Group Replication (MySQL) or Galera (which is used by Percona XtraDB Cluster and MariaDB Cluster) have similar limitations. There are workarounds if your application is tolerant of additional latency and your application also does not process large, long-running write transactions. Which brings us to our next point...

  I want something hidden to the application layer, I want to INSERT/SELECT and etc... like before, without changing any line of my codes.

This is not always possible with multi-master database solutions. The way your application interacts with the database is critical to the success of the database layer. If your application code is written poorly, the database will behave poorly.

I highly suggest you reach out to an expert to help you design an ideal solution for your situation.

FULL DISCLOSURE: I work for Percona, but other vendors could assist here as well.

Good luck!


how to set default charset while create new field

You can specify this option in a field definition - CREATE TABLE Syntax, ALTER TABLE Syntax.
Try this ALTER statement -

ALTER TABLE table1
  ADD COLUMN column2 VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_hungarian_ci DEFAULT NULL AFTER column1;


Best way to implement Hierarchical data in SQL Server with multiple parents

Parent ID       Child ID
1               2
1               3
2               4
3               4
1               5
4               5


Best approach to delete and update a big amout of rows in MySQL

Simple and easy:

DELETE FROM tbl
    WHERE id IN ( 11,22,... 999 );

But...  If you have thousands of ids to delete, let's discuss where they the ids came from.  If they are sitting in another table, or can be found via some form of SELECT, then a "Multi-table DELETE" is probably a better way to do it.

Or if you know that the rows are consecutive in some way, then there may be other options.  For example, to delete "old" rows:

DELETE FROM tbl
    WHERE datetime < NOW() - INTERVAL 7 DAY;

will delete rows older than a week.


Processes dissappear from processlist (MariaDB)

Processes, in the MySQL PROCESS LIST, are representations of connections from outside the server. A mysql -h {host} will create a client connection. They are transient in that, if a client disconnects, the server process no longer exists.

As such a process showing in a process list can no longer be there on subsequent access if the connection was disconnected from the client.


How can I get this "rolling total" query to sum correctly?

The problem with your query is simply that you're not actually processing the records in the order you want to.

Your query picks up all records with an idestado_cliente less than the current row. This would work fine if all records were entered in order strictly by date. however, as we see in your test data, they are not; the last three entries are for earlier dates than the previous entries for each cliente.

In order to deal with that, we need to modify the INNER JOIN to the t2 version of estado_cliente, as follows:

INNER JOIN estado_cliente t2 ON (   t1.fecha > t2.fecha
                                 OR (    t1.fecha = t2.fecha
                                     AND t1.idestado_cliente >= t2.idestado_cliente
                                    )
                                )

Here, we're grabbing the current record, any records with an earlier date, and any records with the same date, but an earlier idestado_cliente.

This will give you the results you want.

That said, as pointed out by Rick James, this isn't necessarily the best way to do it. In your query, you are selecting a second set of rows from estado_cliente for every row you want to show. You don't need to do that; instead, you can just touch each row once in order to get the totals you need, using variables.

You can store the current row's idcliente and sum in variables. Then, when you go to process the next row, you can check if the idcliente has changed (in which case you need to start over), and then calculate this row's sum (using the old sum if the idcliente didn't change, and starting from 0 again if it did).

This version of the query looks like this:

SET @idcliente = 0;
SET @sum := 0;

SELECT c.idcliente,
        c.nombre,
        c.apellido,
        summed.fecha,
        summed.haber,
        summed.debe,
        summed.`SUM`
FROM (SELECT  t1.fecha,
              t1.haber,
              t1.debe,
              t1.idestado_cliente,
              @sum := (  CASE WHEN @idcliente = t1.idcliente THEN @sum ELSE 0 END
                       + t1.haber - t1.debe )
              as `SUM`,
              @idcliente := t1.idcliente as idcliente
      FROM estado_cliente t1
      ORDER BY t1.idcliente,t1.fecha,t1.idestado_cliente
      ) summed
INNER JOIN cliente c ON summed.idcliente= c.idcliente
ORDER BY c.idcliente,summed.fecha,summed.idestado_cliente
;

This updated sqlfiddle link includes all three versions of the query:

the original query, as you posted it (left in primarily to ensure that all values are indeed in memory for the following two queries);
the fixed version of the original query (using the self-join); and
the final version of the query, using variables.

I've run it several times, and even considering the small amount of data involved, the version of the query using variables always seems to run at least as fast as the version that uses a self-join, and usually runs 1 millisecond faster.

Follow-up:

  In another use case, I will need to run that query for a specific date range only. For best performance, should I make a temporary table filtering estado_cliente by date, and then use that temp table in the query?

First, and most important: Your rolling sum will be based on the rows used in the query only. You may already have realized that, but just in case, I thought I should cover it.

If you processed the sample data from the question, but only included rows dated September 1-25, 2017, you won't get:

idcliente  nombre  apellido  fecha       haber  debe   SUM
   6       Matias  Blanco    2017-09-24  4312       0  -18788
   6       Matias  Blanco    2017-09-25  0      10000  -28788

Instead, you'll get:

idcliente  nombre  apellido  fecha       haber  debe   SUM
   6       Matias  Blanco    2017-09-24  4312       0    4312
   6       Matias  Blanco    2017-09-25  0      10000   -5688

That's because the rows from July are excluded, so their haber and debe are not calculated into the SUM.

To get the first set of results (with an absolute SUM, where you get the same value for each row even when you exclude older rows from your output), you have to include all records prior to your date range in the full process, and filter out the ones from before the target dates in the final query.

If the second set of results are what you're after (where the SUM is relative to the set of dates being displayed), then you can fully filter out your full date range before calculating the SUM.

In either case, the upper bound of a date range should be applied before calculating the SUM - records past the dates you want have no impact on the SUM for earlier dates, regardless.

This sqlfiddle link shows the same three queries as the previous link, plus versions of the self-join and variable queries that restrict the output to rows from September 1-25, 2017 and still show the "absolute" sums. I've added an initial column to the results of each, to make it clear which query is which.

So, there are a few ways to do the filtering before processing. Let's just consider two: simply filter out the rows by adding WHERE clauses to the existing queries, or write the estado_cliente data for the only the rows we need to process to a temporary table. The 4th and 5th queries in the link just above are using WHERE clauses.

Ultimately, I can only recommend that you try both, and see which performs better for you.

I can provide some guidance as to what I would expect:

Writing to disk is generally the most time-consuming operation you can perform in a database (reads from disk are a close second, normally - solid state drives tend to reduce the cost of both of these operations). So, writing the data you need to a temporary table can actually make a query take longer; if the database engine was able to process everything in memory, and didn't need to write any intermediate data to disk, then I would expect writing to the temp table would be worse.
If you would be using the temporary table for other queries as well, then creating it could result in a net improvement overall. If there are a large number of estado_cliente columns you won't be using, the temporary table would fit more data into each page, and you'd have fewer pages to bring into memory. If estado_cliente doesn't have many columns beyond the ones you're using, then there is less benefit. The fact that every page has only the rows you want to work with might still mean fewer page to bring into memory. Note that, in the version of the query with the self-join, you are accessing estado_cliente twice, so a temporary table might benefit that version of the query as well.
I don't use MySQL heavily, but in MS SQL Server (where I do most of my work), I have sometimes seen queries that completed faster broken into two queries (one that populates a temp table, and one that performs the final steps using the temp table) than as a single query. I believe this is simply a matter of the complexity of the query making it difficult for the database engine to find an optimal execution plan within the time limits it has for that step of things. It does find optimal plans for the two separate queries, so they go fast; it picks the best plan it can for the more complex query in the time allotted, and that plan can turn out to be bad.
My "gut instinct" says that the query that uses variables is pretty unlikely to benefit from the temp table (say, 25% chance), and the query using a self-join is somewhat unlikely (say, 45% chance).

But like I said earlier - It's probably worth the time to try both options (filter the unwanted date rows in the query, and write the desired date rows to a temporary table), and see which way seems better with your data and system.


Recovery_model for backupset table null sqlserver

Yes, it is nullable and you can run update statement on these tables. There is system view called sys.databases(Keeps attributes of database) which contains one row for each databases, you can't update it. If you do so, you will get error as below:
Msg 259, Level 16, State 1, Line 3
Ad hoc updates to system catalogs are not allowed.

So, only way to set recovery model is by running alter database and its also mentioned on the link you have provided. These options are driven by drop-down at recovery model for each database and there you have only three options i.e. Full, Simple and Bulk-logged.
This change can be done either by UI or through below command:
ALTER DATABASE DB_NAME SET RECOVERY SIMPLE  -- You may change last keyword.

There is no blank option which could set the value as null in sys.databases view.
recovery_model in table backupset can take only three values as mentioned at Official documentation:

Recovery model for the database:
FULL
BULK-LOGGED
SIMPLE

I hope this answers your question.


Find duplicate subscriptions in SQL Server

You can use LEAD function that will 'give' access to the next record value

LEAD(ActionID,1,NULL) OVER(PARTITION BY CustomerID ORDER BY Date ASC) as next_ActionID - this is the key point here. For more details , please read on BOL
https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017

Test values;

INSERT [dbo].[Actions] ([Name])
    VALUES ( N'Subscription')
            ,(N'Cancellation')

INSERT [dbo].[Customers] ([Name])
    VALUES ( N'test')

INSERT [dbo].[CustomerActions] ([Date], [CustomerId], [ActionId])
    VALUES ( CAST(N'2018-04-24T17:44:20.000' AS DateTime), 1, 1)
    ,(CAST(N'2019-01-11T10:37:45.000' AS DateTime), 1, 2)
    ,(CAST(N'2019-01-12T10:37:45.000' AS DateTime), 1, 1)
    ,(CAST(N'2019-01-13T10:37:45.000' AS DateTime), 1, 1)

Query:

SELECT DISTINCT c.Name as CustomerName --,ca.id
FROM
    (
        SELECT
            id,customerid,actionID
            ,ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Date ASC) as RN
            ,LEAD(ActionID,1,NULL) OVER(PARTITION BY CustomerID ORDER BY Date ASC) as next_ActionID
        FROM dbo.CustomerActions
    ) as CA /* customerActions  with next Action field */
    INNER JOIN dbo.Customers as c
    ON CA.CustomerID = c.ID
WHERE
    actionID = next_ActionID

or you can do it with ROW_NUMBER

;WITH CA AS
(
        SELECT
            id,customerid,actionID
            ,ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY Date ASC) as RN
            --,LEAD(ActionID,1,ActionID) OVER(PARTITION BY CustomerID ORDER BY Date ASC) as next_ActionID
        FROM dbo.CustomerActions
)
SELECT Distinct m.Name  --,c.CustomerID, c.ID
FROM CA as C -- current set
    INNER JOIN CA as N -- next set
        ON c.CustomerID = N.CustomerID
        AND c.rn + 1 = n.rn
    INNER JOIN Customers as m
        ON m.ID = c.CustomerID
WHERE
    c.ActionID = n.ActionID

output:

Name
test


It doesn't seem to make sense to fully automate structural changes.  Presumably, there is some probability that changes are applied to B and testing reveals problems.  So a human ultimately needs to make a decision about whether to promote, right?

Assuming that your scripts are stored in source control, a process that pulls the scripts from source control and runs them in each of your environments (presumably you have at least dev and test instances in addition to production and staging) is generally the best solution.  That ensures that scripts are applied consistently in every environment.  You still need humans to do something to indicate that a particular set of changes are ready for the next environment but since that reflects a human's decision that testing of those changes is complete, that's hard to avoid.  And, of course, specific implementation details depend on what source control tool you're using and how your organization uses the tool.


How to troubleshoot COPY FROM inserting less rows then expected?

It could be normal if there are newlines in certain text fields. Newlines are allowed when the value in the field is enclosed by double quotes.
And obviously that makes the number of lines in the file greater than the number of records.

Example :

$ cat file.csv
1,"ab
cd",2
3,"efgh",4

$ wc -l file.csv
3 file.csv

=> create table csvtest(a int, b text, c int);
=> \copy csvtest from 'file.csv' with csv

=> select * from csvtest;
 a |   b   | c
---+-------+---
 1 | ab    | 2
   : cd
 3 |  efgh | 4
(2 rows)


Hosting Multiple MongoDB Databases on the same MongoDB Instance/Cluster

Q1. what about the question of having multiple production databases on the same instance?

A:  It is very normal to have multiple databases on same instance.

Q2. Creating a User/Role that is limited in access to someDatabase
the above considered a normal and standard way of providing a logical & security barrier between tenants?

A: Database level authorization, works very effectively for limiting the user access rights on databases, it is safe to use.

Q3. If I needed to migrate one of these logical DBs to its own MongoDB instance, would that be possible?

A. You cannot have two databases with same name on same instance or cluster.

(If more details required, do ask)


Percent decrease in value each month with SQL


Linux command to kill all MySQL sessions from a specific User?

Try this :

mysql -uuser -ppassword -e 'show processlist' | grep USER_NAME | awk {'print "kill "$1";"'}| mysql -uuser -ppassword

The command will list the processes running in the database and return a kill 'query id'; command and pipe it into mysql again.
 Be carefull with this command as there is no rollback form it :)


Calculate percentage of false records to total records within a aggregate function

Note the standard GROUP BY clause.

This gives "true" percent because I assume that's what you mean (not false)

SELECT
  p.*,
  count(CASE WHEN c.essential = 1 THEN 1 END) / count(*) AS essential_percentage
From
  products p
  LEFT OUTER JOIN
  components c ON p.id = c.product_id
GROUP BY
  p.*;

or

  count(nullif(c.essential, 0)) / count(*) AS essential_percentage


9 digit zip codes are reporting LEN of 12

Zip codes are strings, not numbers. Some of them have 1 or even 2 (but not more than 2) leading zeroes. The datatype in the import table should be VARCHAR(10) so that it can hold 5 digit and 9 digit + hyphen zip codes. Even if you never have to store postal codes of other countries, and even if the values will only ever have numeric digits (i.e. 0 - 9), this data is still string data, just like phone numbers.

Based on the import query shown in your other question (automate import and export of process EXCEL -> SQL SERVER - > EXCEL without using SSIS ):

SELECT * INTO XLS_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\RSG_ETL_Tool\Ohio\OH.xls; HDR=YES; IMEX=1',
'SELECT * FROM [OH$]');

I would suggest not relying upon the SELECT INTO construct to create the XLS_IMPORT table, but instead create the import table(s) manually, and then use the INSERT INTO ... SELECT FROM OPENROWSET() construct. Doing this would allow you to do the following things to improve this situation:

Create the ZipCode field as VARCHAR(10)
Convert the values on the way in using the STR function which, for an initial FLOAT value of 432013256, will return  432013256 instead of 4.32013e+008 (which is what you get when you convert to VARCHAR).
Fix any missing leading zeroes using something like the following:

CASE
   WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 3 AND 4
          THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 5)
   WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 7 AND 8
          THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 9)
   WHEN LEN(LTRIM(STR(@ZipColumn))) IN (5, 9) THEN LTRIM(STR(@ZipColumn))
   ELSE 'BadZipCode'
END

Example:

DECLARE @ZipColumn FLOAT = 032013256.000000;

SELECT CASE
          WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 3 AND 4
                 THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 5)
          WHEN LEN(LTRIM(STR(@ZipColumn))) BETWEEN 7 AND 8
                 THEN RIGHT('0000' + LTRIM(STR(@ZipColumn)), 9)
          WHEN LEN(LTRIM(STR(@ZipColumn))) IN (5, 9) THEN LTRIM(STR(@ZipColumn))
          ELSE 'BadZipCode'
       END;

Returns:

032013256

Ideally, you would fix the column definition in the spreadsheet to be a string. But even if you do that, it might still be a good idea to keep this code around.

  My end goal is to simply get the 9 digit zip code to have a hyphen in the middle.

With that goal in mind, the following inline TVF can be used to both convert the FLOAT value to VARCHAR, and to add in the hyphen for ZIP + 4 values.

Code for iTVF:

CREATE FUNCTION dbo.FormatZIPCode(@NumericZIPCode FLOAT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN

WITH string AS
(
    SELECT  LTRIM(STR(@NumericZIPCode)) AS [Value],
            LEN(LTRIM(STR(@NumericZIPCode))) AS [Size]
), converted AS
(
SELECT  CASE
                WHEN st.[Value] IS NULL THEN NULL
                WHEN st.[Size] BETWEEN 3 AND 4
                    THEN RIGHT('0000' + st.[Value], 5)
                WHEN st.[Size] BETWEEN 7 AND 8
                    THEN RIGHT('0000' + st.[Value], 9)
                WHEN st.[Size] IN (5, 9)
                    THEN st.[Value]
                ELSE 'BadZipCode'
            END AS [ZIP],
            st.[Size] AS [OriginalSize]
    FROM        string st
)
SELECT  IIF(cnv.[OriginalSize] >= 7, STUFF(cnv.[ZIP], 6, 0, '-'), cnv.[ZIP])
               AS [FormattedZIPCode]
FROM        converted cnv;

Test:

SELECT  *
FROM    (VALUES (CONVERT(FLOAT, NULL)), (1), (12), (123), (1234), (12345),
                (123456), (1234567), (12345678), (123456789)) src(val)
CROSS APPLY dbo.FormatZIPCode(src.[val]) frmt;

Returns:

val         FormattedZIPCode
---------   ----------------
NULL        NULL
1           BadZipCode
12          BadZipCode
123         00123
1234        01234
12345       12345
123456      BadZipCode
1234567     00123-4567
12345678    01234-5678
123456789   12345-6789

In order to be clearer about what is being suggested, the following shows all of the suggestions noted above put together:

CREATE TABLE dbo.XLS_IMPORT
(
  Col1     DataTypeForCol1,
  Col2     DataTypeForCol2,
  ZIPCode  VARCHAR(10),
  ...
);

INSERT INTO dbo.XLS_IMPORT (Col1, Col2, ZIPCode, ...)
  SELECT xls.Col1, xls.Col2, zip.[FormattedZIPCode], ...
  FROM   OPENROWSET('Microsoft.ACE.OLEDB.12.0',
         'Excel 12.0; Database=C:\RSG_ETL_Tool\Ohio\OH.xls; HDR=YES; IMEX=1',
         'SELECT * FROM [OH$]')
  CROSS APPLY dbo.FormatZIPCode(xls.[ZIP]) zip;

Some of the spreadsheets are "aware" of there being leading zeroes, and so prefixed the field in Excel with a single apostrophe so that Excel would treat the value as a string instead of as numeric (e.g. '01234). In that case, you can use the REPLACE function to strip out that apostrophe.

-- Test incoming string data (potentially prefixed with a single apostrophe)
SELECT  src.[val], frmt.[FormattedZIPCode],CHARINDEX(N'''', src.[val])
FROM    (VALUES (NULL), (N'''01234'), (N'''123456789'), (N'123'), (N'12345678')) src(val)
CROSS APPLY dbo.FormatZIPCode(REPLACE(src.[val], N'''', N'')) frmt;

However, you can't use REPLACE on all spreadsheets because the spreadsheets that have the ZIP column as numeric will do a CONVERT_IMPLICIT as it passes the value to the REPLACE function and the converted value will be in scientific notation (e.g. 1.23457e+008). So if you can't be certain which datatype is going to be returned by OPENROWSET, then you can use IIF (or CASE if using a version of SQL Server prior to 2012) and CHARINDEX to test for the presence of an apostrophe. If the data is being returned as FLOAT, then the implicit conversion to VARCHAR that will occur when passing the values into CHARINDEX will not be an issue since there is no apostrophe in 1.23457e+008 and the converted value is only going to CHARINDEX and not to dbo.FormatZIPCode.

-- Test handling incoming data as both numeric and string
SELECT  src.[val], frmt.[FormattedZIPCode],CHARINDEX(N'''', src.[val])
FROM    (VALUES (CONVERT(FLOAT, NULL)), (1), (12), (123), (1234), (12345),
                (123456), (1234567), (12345678), (123456789)) src(val)
CROSS APPLY dbo.FormatZIPCode(
       IIF(CHARINDEX(N'''', src.[val]) > 0, REPLACE(src.[val], N'''', N''), src.[val])
                             ) frmt;

SELECT  src.[val], frmt.[FormattedZIPCode]
FROM    (VALUES (N'''01234'), (N'''123456789'), (N'123'), (N'12345678')) src(val)
CROSS APPLY dbo.FormatZIPCode(
       IIF(CHARINDEX(N'''', src.[val]) > 0, REPLACE(src.[val], N'''', N''), src.[val])
                             ) frmt;


PHP -> Mysql persistent connection pooling WITHOUT mysql_pconnect - Possible?

After much searching, I have finally found a solution.

I am not much of a writer, so I will do my best to make this as concise as possible.

So as far as I could find, there are 2 possible solutions:

SQL Relay

http://sqlrelay.sourceforge.net/

This does exactly what the question asked for, and a bunch more. I wont go into too much detail on what I was able to find out about this but will mention that it was not a viable solution as it is not transparent. Meaning that the flow is as follows:

PHP -> Queries -> SQL Relay Extension -> SQL Relay -> Externally hosted MySQL

So this would have involved rewriting all of our code from mysql to sql relay. Not an option in our case.

All that being said, if someone is planning a fresh large scale project that requires any of the numerous features that SQL Relay has, it sounds beautiful.

Mysql Proxy

http://forge.mysql.com/wiki/MySQL_Proxy

This is the solution we ended up using.

The key to making this do what we want it to do is the pooling LUA script for mysql proxy.

This LUA extension can be found at:

https://github.com/cwarden/mysql-proxy/blob/315ab806bb95b8223f5afd3d238eff2a40af03d8/lib/ro-pooling.lua

Without going into too much detail, here are some basic stats... Bare in mind, this is tested at LOW usage time:

[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
   6433   38598  572537

After switching to mysql-proxy, and letting things settle:

[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
     32     192    2848

As you can clearly see, the TIME_WAIT ports to mysql have dropped to almost none.

The connections are now in fact persistent WITHOUT using mysql_pconnect / mysqli_connect( ... p:hostname ... ).

Worth mentioning there appear to be a few configurable settings near the top of the pooler lua script.

local min_idle_connections

and

local max_idle_connections

These appear to be pretty self explanatory. Except that: It would appear that each username (and password? untested... most likely not tho.) combination creates its own set of persistent connections.

So multiply max_idle_connections by the number of unique mysql users that will be connecting to the database. And that should give you an idea of how many idle connections you will end up having.

So, let me reiterate for so this little blurb hits some keywords for those searching via google:

When using PHP is it possible to have persistent mysql connections WITHOUT mysql_pconnect?

Yes, this can be done via SQL Relay if you dont mind rebuilding most of your code to pipe your queries thru their extension OR transparently using mysql-proxy with the ro-pooling.lua script.

We have been wanting something like this for about a year now.

ENJOY!


tl;dr

Don't do calculations in SQL language

Longer

The result scale and precision is well defined here on MSDN. It isn't intuitive, really.
However, in simple terms, precision is lost when the input scales are high because the result scales need to be dropped to 38 with a matching precision drop.

To confirm things

Your extra CAST in the first example simply add zeroes
The truncation happens as per my MSDN link (2nd example)
The 3rd example with constants has implied decimal values that are just enough (5,1) and 18,14).This means the result scale and precision have no truncation (see blow)

More on the 1st and 3rd cases..

The result scale for a division is max(6, s1 + p2 + 1):

First example, this is 77 which is dropped to 38. Precision is forced down similarly, subject to a minimum of 6 (see this)
Third example, this is 24 so precision does not need adjusted

You have some options

calculate in the client code eg .net
use CLR functions to do .net calculations
live with the loss of accuracy
use float and live with 15 significant figures as best

FInally, see this on SO https://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy/424052#424052


Error migrating a big table from mySQL to SQL Server 2012

I solved the issue. The problem was the value of the Data migration timeout in minutes property.

Go to Tools > Project Settings > General and in the left pane click on Migration and then in the right pane, in the section Misc, change Data migration timeout in minutes property to something larger (the default value was 15; I changed it to 300).


MySql Moving from development to production into a different user

In my opinion, the logic is right in your way. I will add a suggestion to make it more practical.

Get the backup without routins. --skip-triggers, and don't add -R is it is false by default
Make a backup of routines only: --no-create-info --no-data --no-create-db -R
Edit the backup file of the routine, and create the user in the new DB
Restore the data backup file
Restore the routines backup file.


DB design to avoid versioning inconsistencies between the application layer and the database layer

The approach you're think of is called Entity-Attribute=Value (EAV). If you Google around for it you'll find very quickly that it is almost universally reviled.  In short, it's generally a bad idea because it doesn't let your relational database management system do what it was designed to do.

As an alternative, you should think about schema changes as something that you need to keep backward compatible.  This often means that new columns will be nullable or have defaults set.  Depending on what you want to do with your application, though, sometimes a database change is simply a breaking change and you can't release the database change without releasing the corresponding code change.

I've seen some people keep a short code table in their database that contains version information in it (e.g. minimum and maximum supported code versions). The idea with this is that when your application starts it does a quick lookup in the code table and if the database version isn't compatible with the code the code exits gracefully.


Database management tool for compact edition (.sdf) database

I'm not sure what it's the best, but one of the most popular is SQL Server Compact Toolbox. Also many alternatives are listed at ErikEJ's blog.


TSQL to find if server is listening on dynamic or static port

You can determine which port an instance is using from the error log but not whether its static or dynamic.

EXEC xp_readerrorlog 1, 1, 'Server is listening'

Alternative would be to read from the registry via xp_regread.

DECLARE
    @StaticPort NVARCHAR(512)
    , @DynamicPort NVARCHAR(512)

EXEC xp_regread
    'HKEY_LOCAL_MACHINE'
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpPort'
    , @StaticPort OUTPUT

EXEC xp_regread
    'HKEY_LOCAL_MACHINE'
    , 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'
    , 'TcpDynamicPorts'
    , @DynamicPort OUTPUT

SELECT
    @StaticPort AS StaticPort
    , @DynamicPort AS DynamicPort


What are the sharding heuristics for MySQL - number of records per table & number of tables per instance?

Sharding of individual items is not too difficult -- you just need a layer to figure out what machine to go to to get the one item.

Sharding of something that you need to scan or search can be messy.  You would have to hand off the query to each possible machine, let them do the scan/search, get back the results, and stitch together them.  GROUP BY, ORDER BY, and LIMIT all have hiccups.  You end up doing some things both in the sharded machines and in the central aggregator.

You mentioned storing 10B metadata rows.  That sounds like a few TB.  That is easily handled in a single machine these days.  The table could be PARTITIONed, but do not rush into that until you understand what PARTITION does and does not give you.

Meanwhile, your "objects" could be scattered around dozens, even thousands, of machines.  Now you get two more problems -- knowing where to look, and dealing with crashes.

Knowing where to look -- a hash is simple, until you need more shards.  Having a "lookup" adds another column to the metadata, and perhaps an index.  I prefer a compromise between the two.

Crashes -- with a thousand machines, you should plan on one dying every week.  Where's its backup?  Or do you have another copy online?  Is two copies safe enough?  Are all thousand machines in the same location, thereby at risk of a single tsunami, tornado, or whatever?

Oh, back to the back of the envelope -- 10B INSERTs, one row at a time, could take a year to finish.  Please consider LOAD DATA and/or batched INSERTs.

INDEXing the monster metadata table versus caching.  Let's say you have 5 indexes other than AUTO_INCREMENT or CURRENT_TIMESTAMP -- that will be 5 random disk hits to update the indexes.  With terabytes of stuff, there is no way to cache it effectively.  Oops, you are now limited to about 20 rows inserted per second, no matter how you do the INSERTs.  With about 30M seconds/year, that leads a decade to insert your 10B rows.  Watch out for indexing!  Adding RAID striping helps my a small factor.  SSDs ($$$!) helps by maybe a factor of maybe 5.  (And it would be hard to get much past 1TB of SSDs on a single machine.)


What is the difference between views and materialized views

A VIEW is a stored query. When you select from it, you essentially run the query.

CREATE VIEW foo
AS
  SELECT col1,col2
  FROM bar;

Is essentially

SELECT foo.*
FROM (
  SELECT col1,col2
  FROM bar
) AS foo;

A materialized view is more or less a view that is cached (or materialized) to disk,

CREATE IF NOT EXISTS TABLE foo
AS
  SELECT col1,col2
  FROM bar;

With the added bonus that, at least in PostgresSQL, there is a command REFRESH MATERIALIZED VIEW that you can feed to it the name of the view, and it'll regenerate it from scratch: REFRESH MATERIALIZED VIEW foo;


How can I reduce OLEDB wait type in a SSAS linked server connection?

The OLEDB wait type is what SQL Server uses to report the amount of time it's waiting for a response from code running outside the control of SQL Server via an OleDB provider.  Typically, this is from a linked server, and in your particular example is the amount of time SQL Server is waiting for SSAS to provide its response to that query.

There is nothing you can do to SQL Server to speed up this query.  You need to ensure SSAS has the dedicated memory required to ensure it never gets paged out of memory to the paging file.  This Microsoft Docs page might be helpful.


Limiting the input to a field as some pattern

This is what you're after:

delimiter $
create trigger student_validate before insert on student
for each row
begin
    if new.admission not rlike '^[0-9][/][0-9][0-9][0-9][0-9][/][0-9][0-9]$'
    then
        signal sqlstate '45000' set message_text = 'Bad Format Admission Number';
    end if;
end$
delimiter ;


Is it possible to find when the last insert took place?

Not giving away any code or anything just yet but ways to detect whether a DB is still in use by some app can be as follows:
- turn the DB off (set to single user mode, set as read-only, etc) many variations to effectively get the result of someone shouting at everybody else because one of their processes stopped unexpectedly and its because of the server you just "turned off"
.. personally this is my favourite approach - but I do understand that it may cause some real business issues and some people just can't deal with emotions ;).

you could also monitor the database size and see if that changes
you could have a small process that counts all rows from particular tables (all tables) and logs that into some other database for you.. then simply see if the row count changes
you could monitor the log file (if anything happens in terms of INSERT, UPDATES, DELETES, then you will see that there.
you could run sp_who(2) at regular intervals to check who's connected

These are just quick and easy to implement methods to see if someone, somewhere is still using the database.

I hope this helps,

B


Get new ID for inserted ID that comes from another table?

It incremented by 50 because you added "ordID" to @idmax and ordID = 50.

Maybe you wanted an incrementing number using row_number?

DECLARE @idmax INT
SELECT @idmax = MAX(OrderNumber) FROM OrderNumbers

INSERT INTO OrderNumbers
(OrderNumber,
 OrderGUID)

 SELECT
 row_number() over(order by ordID) +@idmax,
 NEWID()

 FROM orders

 SELECT * FROM OrderNumbers


You should be able to achieve this using plain SQLCMD from the production server, but have it pointing to your other server

sqlcmd -S "servername" -Q "execute msd.dbo.sp_start_job @job_name='MyJobOnRemoteServer'"


mysql apply regexp_replace for whole column/multiple rows


How to backup/restore SQL Server database from command line similar to mysql

Create a .sql file with your backup / restore statement(s) and use SQLCMD

About halfway down that article there is an example of doing a backup.

You can refer to the following pages with information on the T-SQL for doing backups and restores:

Backup
Restore


How to convert decimal number to IP address?

I am using these two functions in my application:

CREATE OR REPLACE FUNCTION Ip2Decimal(IP IN VARCHAR2) RETURN INTEGER DETERMINISTIC IS
    DecimalIp INTEGER;
BEGIN
    SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
    INTO DecimalIp
    FROM dual
    CONNECT BY LEVEL <= 4;
    RETURN DecimalIp;
END Ip2Decimal;

and

CREATE OR REPLACE FUNCTION Decimal2Ip(IpDecimal IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS

    NUMERIC_OVERFLOW EXCEPTION;
    PRAGMA EXCEPTION_INIT(NUMERIC_OVERFLOW, -1426);

    IP VARCHAR2(16);
    Octet INTEGER;
    v_IpDecimal INTEGER := IpDecimal;
BEGIN
    IF IpDecimal > 2**32 -1 THEN
        RAISE NUMERIC_OVERFLOW;
    END IF;
    FOR i IN 1..4 LOOP
        Octet := TRUNC(v_IpDecimal/256**(4-i));
        v_IpDecimal := v_IpDecimal - Octet * 256**(4-i);
        IP := IP ||'.'||Octet;
    END LOOP;
    RETURN SUBSTR(IP, 2);
END Decimal2Ip;


In addition to what @Josh already clarified, this quote from the manual should answer the question in the title:

  [...] an inheritance link can be removed from a child using the NO
  INHERIT variant of ALTER TABLE.

ALTER TABLE child NO INHERIT parent;

Either that or just delete the child table.

Also, you can include all inheriting tables when dropping the parent table using the CASCADE key word:

  A parent table cannot be dropped while any of its children remain.
  [...] If you wish to remove a table and all of its descendants, one
   easy way is to drop the parent table with the CASCADE option.

You can invert this to take advantage of the behaviour and debug your situation. Start a transaction and try to drop the parent table:

BEGIN;
DROP TABLE a;

ERROR:  cannot drop table a because other objects depend on it
DETAIL:  table b depends on table a
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The error message lists inheriting tables that block the DROP command.

Unless you get an error (aborting the transaction automatically), be sure to issue a ROLLBACK if you don't actually want to delete the table:

ROLLBACK;


DB2 ROLLFORWARD option "TO END OF LOGS" - does it read archive logs?

Whoever told you "When rolling forward to end of logs without specifying overflow log path only active logs will be read" was wrong.

When you have LOGARCHMETH1 set to DISK, TSM, USEREXIT or VENDOR, the DB2 log manager EDU (db2logmgr) is responsible for moving log files to and from the archive location.  When performing a ROLLFORWARD operation, DB2 will determine which log files it needs, and db2logmgr will retrieve files for processing if they aren't already in the active log path.

Now, if you have LOGARCHMETH1 set to LOGRETAIN, and you are manually moving log files from the active log path to somewhere else, only then will you have to specify OVERFLOW LOG PATH (or move/copy the transaction log files back to the active log path).

So: to end of logs means just what it sounds like:  DB2 will replay all transactions present in log files between the backup time and when the restore took place.


Connecting to an SQL server instance without using square brackets to avoid backslash

Create a Server Alias to point to your named instance.

  In SQL Server Configuration Manager, expand SQL Server Native Client
  Configuration, right-click Aliases, and then click New Alias.

  In the Alias Name box, type the name of the alias. Client applications
  use this name when they connect.

  In the Server box, type the name or IP address of a server. For a
  named instance append the instance name.

  In the Protocol box, select the protocol used for this alias.
  Selecting a protocol, changes the title of the optional properties box
  to Port No, Pipe Name, or Connection String.

If you're having trouble getting the client to connect via an alias, I'd suggest detailing the error message in a separate question.


Change path for binary logs and scheduling

You should be able to set the binary logs to a new partition by providing the absolute path:

log-bin=/my/other/dir/mysql-bin

You might need to copy the old binary logs to the new directory and update the .index file with the new path information (Do this only when mysql is not running).

Why would you only want to binary log during certain intervals of the day? Doing this could really mess with your ability to restore at any given time.


I/O is frozen on database DBName. No user action is required

It sounds like you're using Azure Backups for SQL Server or Azure SQL Managed Backups. This is causing the I/O freeze and resume messages as it uses VSS and the SQL VSS Writer to perform the backups.
This type of backup causes issues with native SQL Differential backups (see here), with the solution being to either stop performing your native backups or stop the Azure backups.


Merging into a self referential table with identity columns

This is not a single operation, but a loop that will run n number of times where n is the number of levels in your staging hierarchy.

This uses recursive common table expressions to build the hierarchy path for both a given target table (tree) and a given staging table (staging). The first example uses views containing the recursive code, and the second uses inline ctes if views are not an option.

This solution requires that the entire ancestor path be in the staging table (i.e. from the root node up), and that the combination of parentid,description is unique for each table.

The full code is available in the demos, but here is the recursive cte view for the staging table:

create view dbo.staging_path as
with cte as (
  select id, description, parentid
      , path = convert(varchar(512),description)
      , parent_path = convert(varchar(512),null)
  from dbo.staging t
  where t.ParentId is null
  union all
  select c.id, c.description, c.parentid
      , path = convert(varchar(512),p.path+'|'+c.description)
      , parent_path = convert(varchar(512),isnull(p.parent_path+'|','')+p.description)
  from dbo.staging c
    inner join cte p
      on c.parentid = p.id
  )
select id, description, parentid, path, parent_path
from cte;

which returns:

+----+-------------+----------+---------------+-------------+
| id | description | parentid |     path      | parent_path |
+----+-------------+----------+---------------+-------------+
|  1 | Foo         | null     | Foo           | null        |
|  2 | Bar         | 1        | Foo|Bar       | Foo         |
|  3 | Fizz        | 1        | Foo|Fizz      | Foo         |
|  4 | Buzz        | 3        | Foo|Fizz|Buzz | Foo|Fizz    |
+----+-------------+----------+---------------+-------------+

dbfiddle.uk with two recursive views

declare @rc int = 1;

while @rc > 0
begin;
  insert into tree (description, parentid)
    select sp.description, parentid=tp.id
    from dbo.staging_path sp
      left join dbo.tree_path tp
        on sp.parent_path = tp.path
    where not exists (
      select 1
      from dbo.tree_path i
      where sp.path = i.path
      )
      and (tp.id is not null or sp.parentid is null);

  set @rc = @@rowcount;
end;

If you can not create views to simplify the code as above, you can use inline recursive ctes like so:

dbfiddle.uk without views

declare @rc int = 1;

while @rc > 0
  begin;
    with tree_path as (
      select id, description, parentid
          , path = convert(varchar(512),description)
      from dbo.tree t
      where t.ParentId is null
      union all
      select c.id, c.description, c.parentid
          , path = convert(varchar(512),p.path+'|'+c.description)
      from dbo.tree c
        inner join tree_path p
          on c.parentid = p.id
    )
    , staging_path as (
      select id, description, parentid
          , path = convert(varchar(512),description)
          , parent_path = convert(varchar(512),null)
      from dbo.staging t
      where t.ParentId is null
      union all
      select c.id, c.description, c.parentid
          , path = convert(varchar(512),p.path+'|'+c.description)
          , parent_path = convert(varchar(512),isnull(p.parent_path+'|','')+p.description)
      from dbo.staging c
        inner join staging_path p
          on c.parentid = p.id
      )
    insert into tree (description, parentid)
    select sp.description, parentid=tp.id
    from staging_path sp
      left join tree_path tp
        on sp.parent_path = tp.path
    where not exists (
      select 1
      from tree_path i
      where sp.path = i.path
      )
      and (tp.id is not null or sp.parentid is null);

    set @rc = @@rowcount;
  end;

dbfiddle.uk with recursive view for target table, inline cte for staging

Each option results in tree having the following rows (identity was set to start at 41 for this table to differentiate the id from staging):

+----+-------------+----------+
| Id | Description | ParentId |
+----+-------------+----------+
| 41 | Foo         | null     |
| 42 | Bar         | 41       |
| 43 | Fizz        | 41       |
| 44 | Buzz        | 43       |
+----+-------------+----------+


Update fields that contains certain prefix by lookup table

Typically the pattern would be something like this:

UPDATE t
  SET t.ColumnWithName1 = 'DOMAIN\\' + new_old.NAMENEW
  FROM dbo.Table1 AS t
  INNER JOIN #NAMEOLD_NAMENEW AS new_old
  ON t.ColumnWithName1 = new_old.NAMEOLD
  WHERE <some filter on a pattern>;

But your question doesn't really explain how to identify the specific columns that match. Is [/1] a real string value, a pattern, or something else? Is \(.*) supposed to be a regular expression (which doesn't exist in native T-SQL)? Is the User column a typo or do you have multiple columns that have this domain prefix?


FDW, replication and CREATE SERVER - will it work?

You cannot really use postgres_fdw to connect from a replica server to the master, because you cannot create the foreign server on the replica, it is read only.

You'd have to run the create on the master, then rely on it replicating to the read-replica.

Even if you were to do this, it would make no sense. There is no point connecting from a replica to the master, because the replica has the same data as the master. There is no purpose to doing this.


Exporting MySQL table data: "into outfile" fails, directory stays NULL after editing variables in cnf files

It could be you missed the [mysqld] section for the configuration file however here is a complete example:

$ mkdir conf
$ mkdir home
$ mkdir datadir
$  echo -e '[mysqld]\nsecure_file_priv= "/home/"' | tee conf/native.cnf
[mysqld]
secure_file_priv= "/home/"

$ cat conf/native.cnf
[mysqld]
secure_file_priv= "/home/"

$ docker run -v $PWD/conf:/etc/mysql/conf.d \
             -v $PWD/home:/home  \
             -v $PWD/datadir:/var/lib/mysql \
             -e MYSQL_ROOT_PASSWORD=my-secret-pw \
             --name mysql \
             mysql:5.7

Another terminal:

$  docker exec -ti mysql  mysql -pmy-secret-pw -e 'select @@GLOBAL.secure_file_priv'

mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /home/                    |
+---------------------------+


MySQL: Updating or closing existing rows in a table and/or inserting new rows?

This is beyond what SQL can do.  Your application (which needs to be something more of a programming language than Excel) should orchestrate the task.


module_end extended event enough for stored procedure logging?

To answer my own question - when a stored procedure execution is aborted then this is logged as an event, in which case I don't see the need to capture both module_start and module_end events for my purpose.


PostgresSQL: Get single attribute of UDT in SELECT statement

Use:

SELECT (ctyp).f1 FROM tbl;

The parentheses are necessary to disambiguate tables from composite types as detailed in the manual on composite types.


MySQL: Given a list of row values, how do you only return the changed rows?

One solution could be to add a last_updated timestamp on update current_timestamp column to your orders table. So when an update happens, this column is automatically updated by MySQL to contain the current time.

When you query the database, use the current time minus 5 seconds to compare against the last_updated column i.e.

where last_updated > now()-5

This will give you only the orders updated in the last 5 seconds.

Make sure you add an index on the new last_updated column.


When should I start using connection pooling?

IHMO, using of connection pool hasn't a minimum. You can use it when you are sure there are a lot of accesses on your DB and they are very frequently, so you're using the CP guaranteed to use in better way the available connections that caught by users when they are idles.

The numbers of connections must consider the dimension of DB server. There's no a default number that's OK for all configurations.


What is the meaning of filtered in MySQL explain?

To filter here means to apply a condition on a set of rows that were selected by a type-search as potential rows, and to only keep rows that fulfill the condition:

MySQL will first try to use an index, e.g. do a range scan on your table a using the search-key. It estimates to get 174 rows out of using that index, which is the number in rows. This step is not yet called filtering.

After that, these 174 rows have to be checked against additional conditions (usually in your where-clause). MySQL now estimated that only 32 rows, so 18% of these 174 rows, will remain after that filter has been applied. This 18% is the value in filtered.

While it is obviously better to have 32 rows instead of 174 (if you e.g. have to later join them with another table), a "perfect" index would have given you these 32 rows directly from the initial search, saving you the time to look at and filter out 82% of all potential rows.

So a low value might indicate that there could be a better index: e.g. a full table scan with rows=1000 and filtered=0.1% could become an index lookup with rows=1 and filtered=100% if you add a good index.

On the other hand, you can very well completely ignore this filtered-value (which is in most cases a really bad estimate anyway), and focus on the other more important columns (especially type, key and extra) to optimize your query. It can e.g.  be better to get rid of a filesort (e.g. by using an index that satisfies the order by), even if it results in a lower filtered value. And a better type can result in a huge performance improvement, even if it might not change or even lower filtered. In the example above with filtered=0.1%, type=all would have already be enough to indicate that you might be able to improve that query by adding an index, without looking at filtered at all.

So don't take that value too seriously: neither does 100 mean your indexes are good, nor does a lower value necessarily indicate bad indexes. type is a much better indicator for that.


Connecting to Oracle Database with ODBC/Python

Are you installing the 32 bit drivers or the 64 bit drivers on Windows 7?

I have to install the 32 bit drivers on all of our Windows 7 machines (even those running 64 bit).

After that I have to set up ODBC using:   c:\windows\syswow64\odbcad32.exe  instead of through the control panel.

Your issue may very well be different.


The SSIS Catalog and SQL Server Agent are both part of the Failover Cluster Instance, and failed-over when a Failover Cluster Instance fails-over.  So my recommendation is to store all your packages in the SSIS catalog and schedule them all with SQL Agent.  And simply ignore the SSIS Service completely.

See
"The topics in this section discuss the Integration Services service, a Windows service for managing Integration Services packages. This service is not required to create, save, and run Integration Services packages. SQL Server 2012 supports the Integration Services service for backward compatibility with earlier releases of Integration Services. "
https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service


BLOB is correct, as that is a binary string. TEXT is a character string, but protobuf is not character data; so use some kind of BLOB

As for TINY/MEDIUM/LONG; how big is your data?

TINYBLOB : max 255 bytes
BLOB : max 65,535 bytes
MEDIUMBLOB : max 16,777,215 bytes
LONGBLOB : max 4,294,967,295 bytes

In some small minority of cases TINYBLOB may suffice, especially if there aren't any strings (names/addresses in particular use up space). In most cases, MEDIUMBLOB is probably a fair guess.


Syncing Uncontained Objects with AlwaysOn Availability Groups

Native functionality, no.

AlwaysOn Availability Groups have the containment of database.  Server objects (Agent jobs, logins, alerts, etc.) just simply don't play a HADR role with availability groups.

Whenever you run into the need to ensure that server-level objects exist on all replicas, or that they only execute on [particular_replica] then you will have to have this custom logic.


AWS aurora (postgres) proper value for the shared_buffers parameter

Standard PostgreSQL runs on a file system and depends on the file system cache in addition to the PostgreSQL buffer cache. Aurora PostgreSQL runs on top of Aurora Storage, which is not a file system and doesn't have a separate cache on the instance. So, with Aurora PostgreSQL we set the default for shared_buffers to 75% of the total memory on the instance. We recommend you look at your specific workload requirements to determine memory requirements for caching your working set, for managing the connections needed, and for other PostgreSQL memory requirements.

Full Disclosure: I am the Product Manager for Aurora PostgreSQL.

-Kevin Jernigan


INSERT INTO remote table on different server with OUTPUT clause gives error

(re-wrote, misunderstood)

If you want the remote ID, then the best way is to use a stored procedure on the remote server and return a result set.

Or don't redirect into a temp table/table variable. This isn't in scope on the remote server

See these on SO:

https://stackoverflow.com/questions/4537560/get-identity-from-another-serverlinked-server/4537600#4537600
https://stackoverflow.com/questions/2621887/sql-server-identity-issue/2621940#2621940


Are there any disadvantages to shrinking a database?

The backups was failed and the time of failure huge transactions were logged which rapidly grows the DB.

If this was a one-time occurrence and your log file (LDF) is grotesquely huge, take a log backup and then shrink the log file (only) to its pre-bloat level just this once.  Otherwise, leave it the way it is.

The reason you don't want to shrink your data file is because it fragments your indexes, which can affect performance. In addition, because your indexes get fragmented when you shrink your database. the next time your re-indexing kicks off, your datafile will grow for rebuilds and your log file will grow for reorgs.

The reason you don't want to shrink your log file to a size smaller than its usual working size is because instant file initialization works for data files, but not for log files, so if the log file is going to grow to that size again it'll be a performance hit while it grows.

There's a lot of information on why shrinking is bad.  Only do it if you have a good reason.


Recover Wrong encoded string values in MySQL database

A possible solution:

Dump your table data with mysqldump or any other favorite tool
Fix encoding in dump file using one of the utilities (there are plenty out there)
Load data back


I have a question regarding inner workings of UPDATE statements with regards to SQL Server.

The statement describes a desired logical change to the database. What happens physically at runtime depends on the execution plan, the state of the database, and what other concurrently running statements/queries are doing.
I mention this because your question relates very much to implementation details.

From what I understand, update will first select the row which needs to be updated with a shared lock which means that both update statements can select specific row.

This is generally not what happens.
Under most isolation levels (including READ UNCOMMITTED as specified in the question) SQL Server will take update (U) locks when locating rows to update. This is an implementation detail added to offer some protection against a common form of conversion deadlock (it is not always sufficient, but it does help).
Note: SQL Server may choose any convenient access method to locate rows to update. For example, it might choose to use a nonclustered index. The update will still ultimately update the base table and any applicable secondary indexes, but the order of operations may differ.
This means U locks do not necessarily serialize as one might expect. For example, one update statement in the question might choose to locate records via a secondary index, in which case an entry in that index has a U lock applied. There is nothing to stop the second update statement choosing a plan that locates records using a scan of the base table (heap or clustered).
In that case, the second update could acquire a U lock on a row in the base table while the first update holds a U lock on a row in a secondary index that links to the same base table row.
In yet another scenario, SQL Server might be able to choose a single operator update plan (for example if a clustered index exists on (ID, Value)). In this case, an exclusive X lock is taken on the clustered index immediately - there is no prior U lock.

It then asks for an exclusive lock for updating the column values.

Data-changing operations always take an X lock before the change is made. This lock is held to the end of the transaction. It may or may not involve converting a currently-held U lock to X.
For example, if the row to update was located using the base table, a U will be held and converted to X at the base table update operator. If the row was located using a secondary index, U is held on that index and a new X lock is taken on the base table. Both U and X are held at the same time, on different resources.

So, it seems that the result would the Value being set to 20.

There are a number of possible outcomes depending on timing and the execution plans chosen by each of the two update statements. Choosing just a few of the more interesting ones:
Scenario 1:

Session 1 obtains a U lock on the base table row while reading.
Session 2 blocks waiting to acquire U on the same row in the base table.
Session 1 sets Value to 10 and commits.
Session 2 acquires its U lock and finds nothing to do, since Value != 0 now.

Outcome: Value is set to 10.
Scenario 2:

Session 2 obtains a U lock on the base table row while reading.
Session 1 blocks waiting to acquire U on the same row in the base table.
Session 2 sets Value to 20 and commits.
Session 1 acquires its U lock and finds nothing to do, since Value != 0 now.

Outcome: Value is set to 20.
Scenario 3:

Session 1 obtains a U lock on a secondary index (locating a row to update).
Session 2 obtains a U lock on the base table (locating a row to update).
Session 1 needs to acquire X on the base table row to perform the update, but is blocked by the U lock held by session 2.
Session 2 converts its U lock on the base table to X and sets Value to 20.
Session 2 now needs to maintain the secondary index (assuming that index contains the Value column).
Session 2 needs to acquire X on the secondary index row to update it, but is blocked by the U lock held by session 1.
Deadlock. One of the sessions rolls back with an error; the other completes successfully.

Outcome: Indeterminate. Value may end up as either 10 or 20.

These are just some of the possibilities. It is generally a mistake (in my opinion) to try to predict detailed engine locking behaviours. Focus instead on writing a correct logical specification of the change you want to make, and use the isolation level that provides the guarantees you need.
Something about the way the question is written makes me think you might really be asking about "lost updates". If that is case, please review existing Q & A like the following before asking a more specific new question:

Lost Update Understanding
Are lost updates completely handled in SQL Server or is manual intervention still required?
Why are U locks required when using Read Committed Snapshot Isolation
Where is Read Committed insufficient?


Use only one Junction Table per Database?

The first solution is the best.

The second solution will give you less tables but in case that you need to join 3 tables with 2 junctions you need to use the big junction table twice in your SQL.
In case that you want to create constraints between a table and it's junction (is practically mandatory) then this is not possible in the second solution. Your data integrity will not be checked by the database.
The ER diagram for the second solution will look complicated for somebody that needs to get data out of your database.


Transaction log is full due to 'CHECKPOINT' for DB in simple recovery model

(since this is longer than a comment ... so putting it as answer)

  For one of our DB's on SQL server 2012 RTM.

This is your problem. There were many fixes introduced in the consecutive CUs / SPs after the RTM build.

Can you patch the sql server with the latest SP2 ? After patching, check if the issue still happens or not.

Your problem might be because your MODEL database might be in simple recovery.

If the model database is set to SIMPLE recovery model and user database is created with the simple recovery model from model database template, SQL Server does not truncate its log automatically like it suppose to (after a full backup). It appears that somehow SQL Server is treating it as if it is in full recovery model.

you can execute below log backup (even though your database is in simple recovery - as since model is in simple recovery - due to a bug in RTM, sql server treats it as being in FULL recovery)

BACKUP LOG dbName
TO DISK = 'dbName_log_backup.trn'
GO

Check this KB: Database does not follow simple recovery model behavior in SQL Server 2012 after you set the recovery model of the "model" database to "Simple"


The two aren't necessarily mutually exclusive, I have built apps which use MySQL for the production database, but used a SQLite database for testing, because of SQLite's ability to run a database in memory.
i.e. prior to every test, you can build the schema from scratch, seed the database with data and then run your test, once the test finishes the SQLite database is flushed from memory and you can start from scratch with your next test.
You obviously need to be careful that you use code and features that are portable between the 2 systems - MySQL has lots of features that are specific to it for example, ALTER COLUMN - and re-positioning a column after an existing column wont transfer to SQLite.
Because its hard to anticipate the future, you could start out on SQLite and stick to portable SQL as much as possible and then if you find you find you can't achieve your goals on SQLite, move to MySQL.


database with same schema and same AWS instance type, but different performance

There are several things which are needed to know here.
Some EC2 Instances on AWS are using shared CPU, this could be one reason why your instances are performing different.
Also the same EC2 instances in different regions might be different from each other from hardware perspective.
Also interesting is to know when you did the performance test, if you setup your server for the first time and run some tests, these will perform not so well compared to a machine which already calculated the same queries before.
The reason is the caching of course, caching of raw data, caching of query plans and re-using instead of compiling them and deciding which one to take.

Now we have to counters about memory grant, I think we are talking about memory grants pending.

Memory Grants Pending

This counter shows you the number of processes waiting on a workspace memory grant.
Of course the lower the number, the better your performance.

But for you the new server has a lower value, so it is performing at least better for this specific value.

Brent Ozar, the king of performance, has a nice list of counters which are worth looking at:

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

Monitoring only one performance counter will in most cases not give you enough vision.