Disabled or enabled trigger from another trigger

You have three options:

Disable "nested triggers" using sp_configure. This is a server/instance -level option, so it might disable functionality in other areas where you might have triggers calling other triggers. But it should at least be mentioned. You can see the current setting using the following:

EXEC sp_configure 'nested triggers';

And you can disable this ability using the following:

EXEC sp_configure 'nested triggers', 0;
RECONFIGURE;

You can read more about this setting here: Configure the nested triggers Server Configuration Option.
Have each trigger detect how many levels deep they are using TRIGGER_NESTLEVEL function. Use this at the beginning of each trigger to simply exit if it is not the 1st or 2nd trigger execution in the stack. Something along the lines of:

IF (TRIGGER_NESTLEVEL() > 2)
BEGIN
  -- Uncomment the following PRINT line for debugging
  -- PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;

This will require a little bit of testing to see how it is affected by the initial insert being done by yet another trigger (in case that ever becomes an issue, but it might not). If it doesn't work as expected when called by another trigger, then try setting some of the parameters to this function. Please see the documentation (linked above) for details.
Set a flag in the session-based "context info" using SET CONTEXT_INFO. Context info is a VARBINARY(128) value that exists at the session level and retains its value until overwritten or until the session ends. The value can be retrieved either by using the CONTEXT_INFO function or selecting the context_info column from either of the following DMVs: sys.dm_exec_requests and sys.dm_exec_sessions.

You could place the following at the beginning of each of those triggers:

IF (CONTEXT_INFO() = 0x01)
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Exiting from recursive call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  RETURN;
END;
ELSE
BEGIN
  -- Uncomment the following PRINT line for debugging
  --PRINT 'Initial call to: ' + ISNULL(OBJECT_NAME(@@PROCID), '');
  SET CONTEXT_INFO 0x01;
END;

This option doesn't work so well if you are already using Context Info for some other reason. But, anyone using SQL Server 2016 can make use of SESSION_CONTEXT, which is a new session-based set of key-value pairs.


How is it possible that a key of char+bigint has better performance than key of bigint+bigint?

The size and makeup of an index, composite or not, does not make a lot of difference to performance.

However, the randomness of the accesses does.  This combination is a performance killer:

"Random" index (or part of index):  UUID / MD5 / SHA256 / etc.
Index (or table) is bigger than can be cached in RAM (in the buffer_pool).

Let's say the index (or table) is 4 times as big as the buffer pool.  Then the 'next' lookup (either for SELECT or for adding a row, hence updating the index), has a 3/4th chance of being a disk miss.  That is 75% of such operations will incur a disk hit.  If you are using an HDD, that is (a Rule of Thumb) 10ms.  (SSDs are faster, but still not free.

You can mitigate the problem by shrinking the table size.  This can be done by shrinking datatypes.  Don't use INT (4 bytes) for a Yes/No flag, use TINYINT (1-byte).  Don't store the MD5 as CHAR(32) (32 bytes -- or 96 if utf8); use HEX() and UNHEX() when fetching and storing into a BINARY(16) column.

Most other types of indexes have some degree of "locality of reference".  That is, two (or a thousand) consecutive references will be "near" each other, hence the caching of the buffer_pool can help avoid I/O, even if the table/index is much bigger than the buffer_pool.

More discussion on why UUIDs suck, plus a partial remedy for Type-1 UUIDs, which are time-based and it is the type that MySQL uses.

As for your benchmark...

In a 'real' table, there would be several other columns.
You did not increase the number of rows until it started getting a significant number of disk misses.
At that point, the 2-column lookup table could stay cached, but the bigger 'real' table could not.
So, if there was "locality of reference" (such as looking only at 'recent' rows), the MD5 would be jumping all around, but the BIGINT would be hitting more cacheable stuff.


SQL Server 2012 - password for services during silent installation

Just to preserve this as an answer for future readers:

I have seen this type of failure before. Somehow something still knows that that managed account had been created already and therefore now requires a password.

Try your install script against a newly setup VM.


Create function in central database or repeat in each database?

The way I prefer to do this: put the function in a utility database, and create a synonym to it in each regular database. This way you get the best of both worlds:

there is only one copy of the object to maintain
the developer doesn't have to provide three- or four-part names

e.g.

USE UtilityDB;
GO
CREATE FUNCTION dbo.LastIndexOf(...) ...
GO
USE otherDB;
GO
CREATE SYNONYM dbo.LastIndexOf FOR UtilityDB.dbo.LastIndexOf;
GO

This is especially powerful for CLR functions, since there is extra administrative overhead for changing/deploying those.

And this is way preferable to using master (and marking as a system object, which isn't guaranteed to be forward portable). I'd love to know how your developer expects to create his function in the sys schema, though.

I do understand that maintaining multiple copies of a function in 500 databases is no more difficult really than maintaining a single copy, but having multiple copies is really only a benefit when you do have exceptions (e.g. client A wants their function to handle NULLs differently, or something). In which case I would leave the synonym in all the other databases, and introduce a special version of the function only in that database.

(This assumes that the function doesn't rely on any data access within a client's database, of course - which can certainly complicate matters.)


Each node defines auto increment equal to the total number of nodes. This is for avoiding id collisions, because each node in the cluster have their unique sequence of numbers.

Like @jersten said, let galera handle it ;)


PostgreSQL B-tree, good monotonic values?

With B-Tree indexes, more importantly is the cardinality of the data you're indexing. The more unique the data is in the index, generally the better candidate it is for a B-Tree index because it'll result in a much more full tree with many branches and leaves. A very non-unique field, like a boolean based field, will always only have two distinct values, and therefore is very repetitive resulting in two short but wide branches and doesn't filter down the data as much as a more unique field.
Since you're planning to index a BIGINT field with randomized values, it should lead to very unique data and the B-Tree index should perform very well, regardless of the monotonicness of those values. Additionally, if you're only inserting 30 values a month, your table must be tiny so you should have no worries at all regarding using a B-Tree index. B-Tree indexes handle billions of rows just fine.
To answer your actual question, yes monotonic values in a B-Tree index reduce fragmentation initially and therefor are more efficient, but again this is probably a minimal concern based on the fact that your values will be very unique and your overall amount of data being tiny.


You could turn off automatic identity management and allocate your ranges manually.
Another option could be forcing a range re-allocation by issuing sp_msrefresh_publisher_idrange and specifying the range boundaries.

However, both options would not guarantee that the lTID column is populated with ever-increasing values: you will always have at least one range for the publisher and one range for the subscriber and if users are inserting at both sites you can't have ever-increasing IDs.

This is by design and there's no way around it.

If your goal is keeping the identity values generated at the subscribers ever-increasing, that's a whole different story and it can be achieved with one of the above options.


I chose "REAL" as data type and just stored "-Infinity" inside it. Seems to work.


Possible to make MySQL use more than one core?

I actually discussed innodb_thread_concurrency with a MySQL Expert at the Percona Live NYC conference back in May 2011.

I learned something surprising: In spite of the documentation, it is best to leave innodb_thread_concurrency at 0 (infinite concurrency). That way, InnoDB decides the best number of innodb_concurrency_tickets to open for a given MySQL instance setup.

Once you set innodb_thread_concurrency to 0, you can set innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) to the maximum value of 64. This should engage more cores.


MySQL v5.1.73 - can the binlog_format betweem Master & slave be different?

The Error Message you received

  Last_SQL_Error: Could not execute Delete_rows event on table test.pruning; Can’t find record in ‘pruning’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000002, end_log_pos 1448

This message can come up when doing a DELETE query with row-based replication. Unfortunately, it is one of the disadvantages to row-based replication:

  Disadvantages of row-based replication

  RBR can generate more data that must be logged. To replicate a DML statement (such as an UPDATE or DELETE statement), statement-based replication writes only the statement to the binary log. By contrast, row-based replication writes each changed row to the binary log. If the statement changes many rows, row-based replication may write significantly more data to the binary log; this is true even for statements that are rolled back. This also means that taking and restoring from backup can require more time. In addition, the binary log is locked for a longer time to write the data, which may cause concurrency problems.

I have addressed something similar (error 1032 in my answer to
MySQL master master replication, safe to delete rows on both masters?) where doing a DELETE query was the issue.

In your case, since the Slave is using STATEMENT, the row change is expected to be a precise match of PRIMARY KEYs between Master and Slave. On many occassions, it may not be the case. That is why error 1032 exists.

To play it safe,

use binlog_format=ROW on both Master and Slave
use binlog_format=MIXED on both Master and Slave
use binlog_format=STATEMENT on both Master and Slave
You could experiment with Master MIXED and Slave ROW. At the very least, avoid STATEMENT on the Slave if the Master is not STATEMENT.


MySQL's syntax for UPDATE does not have FROM (you probably got the syntax from a SQL Server example).

The syntax in MySQL is:

UPDATE
     <table_references>   -- this can be multiple tables joined
                         -- with the JOIN .. ON syntax
SET
     column_name_1 = value1,
     column_name_2 = value2
WHERE
     <conditions>
... ;

Try this:

UPDATE
        cities_extended
    INNER JOIN
        cities
    ON
        cities_extended.state_id = cities.state_id
    AND
        cities_extended.city = cities.city
SET
    cities_extended.city_id = cities.id ;

or with table aliases:

UPDATE
        cities_extended AS ce
    INNER JOIN
        cities AS c
    ON
        ce.state_id = c.state_id
    AND
        ce.city = c.city
SET
    ce.city_id = c.id ;


Subselect vs join: Are both SQL queries equal?

Both query will return same possible data sets as if you use in as sub query, it behaves like inner join in SQL query and where clause in both SQL statements should be same as in your where condition.


Unable to take backup of mysql database using Xtrabackup

Visit below mansion urls and hope these references will help you.

http://johanandersson.blogspot.in/2012/10/hotbackup-of-galera-cluster-for-mysql.html

http://www.percona.com/doc/percona-xtrabackup/howtos/setting_up_replication.html


MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

Assuming you have some tables for Persons, Animals:

CREATE TABLE Person
  ( PersonID INT UNSIGNED NOT NULL AUTO_INCREMENT
  , PersonName VARCHAR(255) NOT NULL
  , CONSTRAINT Person_PK
      PRIMARY KEY (PersonID)
  , CONSTRAINT PersonName_UQ
      UNIQUE (PersonName)
  ) ;

CREATE TABLE Animal
  ( AnimalID INT UNSIGNED NOT NULL AUTO_INCREMENT
  , AnimalName VARCHAR(255) NOT NULL
  , CONSTRAINT Animal_PK
      PRIMARY KEY (AnimalID)
  , CONSTRAINT AnimalName_UQ
      UNIQUE (AnimalName)
  ) ;

and results:

CREATE TABLE Result
  ( RaceID INT UNSIGNED NOT NULL
  , Position INT UNSIGNED NOT NULL
  , PersonID INT UNSIGNED NOT NULL
  , AnimalID INT UNSIGNED NOT NULL
  , Errors INT UNSIGNED NOT NULL DEFAULT 0
  , CompletionTime Time NULL DEFAULT NULL

  , CONSTRAINT Result_PK
      PRIMARY KEY (RaceID, Position)

  , CONSTRAINT Race_Person_UQ           -- assuming a Person cannot enter
      UNIQUE (RaceID, PersonID)                 -- a race twice

  , CONSTRAINT Race_Animal_UQ           -- assuming an Animal cannot enter
      UNIQUE (RaceID, AnimalID)                 -- a race twice

  , INDEX PersonID_IX (PersonID)                -- indexes for the Foreign Key
  , INDEX AnimalID_IX (AnimalID)                -- constraints:

  , CONSTRAINT Person_Result_FK
      FOREIGN KEY (PersonID)
      REFERENCES Person (PersonID)
  , CONSTRAINT Animal_Result_FK
      FOREIGN KEY (AnimalID)
      REFERENCES Animal (AnimalID)
  ) ;

I suggest you first bulk load the data (possibly with LOAD DATA from .txt or .csv files) in a table in MySQL (supplying race IDS. If you can't supply raceIDs but you have race names, the tables should be adjusted accordingly). You should have a Race table as well, this is just a sample procedure:

CREATE TABLE BulkData
  ( RaceID INT UNSIGNED NOT NULL
  , Position INT UNSIGNED NOT NULL
  , PersonName VARCHAR(255) NOT NULL
  , AnimalName VARCHAR(255) NOT NULL
  , Errors INT UNSIGNED NOT NULL DEFAULT 0          -- adjust datatypes according
  , CompletionTime Time NULL DEFAULT NULL           -- to your data
  ) ;

LOAD DATA INFILE '/results.txt'
    INTO TABLE BulkData
    FIELDS TERMINATED BY ','
           ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' ;

Then you can manipulate them and insert them into the 2-3 tables. For Person:

INSERT INTO Person
    (PersonName)
SELECT DISTINCT
    b.PersonName
FROM
    BulkData AS b
WHERE NOT EXISTS
    ( SELECT 1
      FROM Person AS p
      WHERE p.PersonName = b.PersonName
    ) ;

Similar for Animal:

INSERT INTO Animal
    (AnimalName)
SELECT DISTINCT
    b.AnimalName
FROM
    BulkData AS b
WHERE NOT EXISTS
    ( SELECT 1
      FROM Animal AS a
      WHERE a.AnimalName = b.AnimalName
    ) ;

And then in Result:

INSERT INTO Result
    (RaceID, Position, PersonID, AnimalID, Errors, CompletionTime)
SELECT
    b.RaceID, b.Position, p.PersonID, a.AnimalID, b.Errors, b.CompletionTime
FROM
    BulkData AS b
  JOIN
    Person AS p  ON p.PersonName = b.PersonName
  JOIN
    Animal AS a  ON a.AnimalName = b.AnimalName
WHERE NOT EXISTS
    ( SELECT 1
      FROM Result AS r
      WHERE r.RaceID = b.RaceID
        AND r.PositionID = b.PositionID
    ) ;

If the importing results are satisfying, then you can empty the BulkData table and repeat the procedure with more files. The NOT EXISTS conditions will take care and not allow duplicates even if you try to load same data twice.


What is limiting MySQL to write max. 10 Inserts per second?

So this is my SOLUTION. Imported my data of 500.000 rows in 3 minutes. In fact a lot of this settings is not for company grade systems. In a private LAN with 1-2 workstations it should be okay.

Here is my config file for the record. May it help somebody and my future me. =)

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /raiddrive/mysql
log-error       = /raiddrive/mysql/error.log
bind-address    = 192.168.2.104
port            = 3306
#hardcore performance https://dba.stackexchange.com/questions/29913/innodb-insertion-faster
skip-log-bin
skip-innodb-doublewrite
transaction-isolation = READ-COMMITTED
innodb_flush_log_at_trx_commit = 0
#if you lost root password
#init-file      = /raiddrive/mysql/setroot.sql
secure_file_priv= ""
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=2000
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=8388608000


How are the DBA, SYSDBA, SYSOPER roles and SYS, SYSTEM users related in Oracle?

I think that DBA and SYSDBA roles enable to do everything - if this is
true, then why are there two names for the same thing?

They aren't the same thing.  SYSDBA is not a role but a special privilege in and of itself.  It gives the grantee ultimate authority, including the ability to issue shutdown and startup commands, override some other limitations, and a few other items that escape me.  Unlike roles, when one is granted SYSDBA, they are added to the password file (external of the database), allowing them to connect over the network to an idle instance.
DBA is just a role. And while it is created by default, and assigned to user SYSTEM by default, it is fundamentally no different than any role that you might create and assign privileges to.  While it is not recommended that privs be added or removed, there is nothing to stop you from doing so.

the SYSOPER role enables to do everything except of accessing users'
data,

Like SYSDBA, SYSOPER is not a role but a special, hard-coded privilege. Off the top of my head, I cannot remember the limitations, but it's primary purpose is to allow a granted user the ability to shutdown and startup, without giving it a lot of other privileges.  It is not granted to anyone by default, and in my entire career (working in small shops with at most 3 DBAs) I've never had cause to grant it.

the SYS user has DBA and SYSDBA roles, so he can do anything,

Yes.

the SYSTEM user has DBA role, so it seems that he could do anything,
but I have read that he cannot do backups, recovery, and DB upgrade.

Without being granted SYSDBA or SYSOPER (and he shouldn't be) he cannot stop or start the instance.  If he cannot stop the instance, he cannot perform upgrades or recovery. He can perform online backups (IF the database is in archivelog mode) because that doesn't require a shutdown. But what would be the point?


The reason the first query does not work consistently has to do with how MySQL processes subqueries. In fact, subqueries will experience rewrites and transformations.

There are four(4) components explained here:

Item_in_optimizer
Item_in_subselect
Item_ref
Left_expression_Cache

From the examples posted, it would be impossible to allow an item_ref to become a self reference. In terms of your single DELETE query, the test table as a whole cannot fully self reference itself because some keys are available during transformation and some are not. Therefore, when a query performs a self-reference, a key (in this case id) can disappear in a transformation even though the actual self-referenced table has the key.

Mysql subqueries are only great for sub-SELECTs, even self-referencing a table multiple times. The same cannot be said for non-SELECT queries.

I hope this explanation helps.


SQL Server 2012 Standard Edition - multiple instances and memory utilization

Knowing that two instances are absolutely not related to each other, i guess that you can use two instances on one server with 64gig for each. At least to my understanding, OS is the one who assigns memory to apps, so it should be fine :)


A jsonb GIN index with the jsonb_path_ops operator class would be an option.

But to just index (multiple) application #>> '{*, tour_id, id}') values per jsonb field (pseudocode, the * is illegal), I would write a tiny, IMMUTABLE SQL function to extract and cast the UUID fields to an UUID array (uuid[]) - and create an expression GIN index on that. Should be considerably smaller and hence faster:

CREATE OR REPLACE FUNCTION f_extract_tour_ids(jsonb)
  RETURNS uuid[] AS
$func$
SELECT ARRAY(SELECT (jsonb_array_elements($1) #>> '{tour_id, id}')::uuid)
$func$ LANGUAGE sql IMMUTABLE;

What was missing in my original answer: there is no GIN operator class for uuid[], yet (up to Postgres 10). See this thread on pgsql-hackers.

Update: Starting with Postgres 11, the additional module btree_gin provides the missing operator class.

You have to create it yourself (once per database):

CREATE OPERATOR CLASS _uuid_ops DEFAULT
   FOR TYPE _uuid USING gin AS
   OPERATOR 1 &&(anyarray, anyarray),
   OPERATOR 2 @>(anyarray, anyarray),
   OPERATOR 3 <@(anyarray, anyarray),
   OPERATOR 4 =(anyarray, anyarray),
   FUNCTION 1 uuid_cmp(uuid, uuid),
   FUNCTION 2 ginarrayextract(anyarray, internal, internal),
   FUNCTION 3 ginqueryarrayextract(anyarray, internal, smallint, internal, internal, internal, internal),
   FUNCTION 4 ginarrayconsistent(internal, smallint, anyarray, integer, internal, internal, internal, internal),
   STORAGE uuid;

Note (quoting the manual):

  The user who defines an operator class becomes its owner. Presently,
  the creating user must be a superuser.

See:

PostgreSQL GIN index on array of uuid

Then create the index:

CREATE INDEX payments_application_tour_ids_gin_idx ON payments
USING gin (f_extract_tour_ids(application));

Match the expression and use an indexable array operator in your queries to be applicable, like:

SELECT *
FROM   payment
WHERE  f_extract_tour_ids(application) @> '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::uuid[];

As you can see, you can search for any number of UUIDs at once with this query.

Or, if you can't (or won't) create the operator class, you can work with the text representation of the UUID values - which is considerably bigger and hence more expensive.

Adapt f_extract_tour_ids(jsonb) like this:

CREATE OR REPLACE FUNCTION f_extract_tour_ids(jsonb)
  RETURNS text[] AS
$func$
SELECT ARRAY(SELECT (jsonb_array_elements($1) #>> '{tour_id, id}'))
$func$ LANGUAGE sql IMMUTABLE;

And the query:

SELECT *
FROM   payment
WHERE  f_extract_tour_ids(application) @> '{d61802ff-3eec-4a72-97ca-832f51b96bf0}'::text[];

Be sure to match the UUID format exactly.

Related:

Index for finding an element in a JSON array
Can PostgreSQL index array columns?
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


To test Linked Server connectivity, first reboot, and then test OleDb connectivity with 64-bit PowerShell.  EG
PS C:\> $con = new-object system.data.oledb.oledbconnection("provider=OraOledb.Oracle;data source=N'ORACLE_SERVER;user id=xxxxxxx;password=xxxxxxx")
PS C:\> $con.Open()


Isolating a Distributed Replay environment

Since the target SQL Server could have a stored procedure using a hard-coded SQL Server account and password to connect to some other server, I'd be unwilling to be 100% certain nothing could touch production unless I had a truly separate network setup to run the Distributed Replay batches against.

One could go to great lengths to try to ensure nothing hits production, but what if you miss something?

I'd setup a bunch of VMs for the controller, replay machines, and the target SQL Server(s), and configure them on their own virtual network, just to be sure.


Put the foreign keys on the database.  Even if you validate the data in the application before you save it the FK's are a good piece QA backup.  For a first approximation, applications always have data issues.  Leaving controls like this out of the system just invites failure modes where data gets corrupted silently.

There's nothing like working in data warehousing for a few years to see this in action.  You spend your time picking up the pieces after noddy mistakes by application developers who thought they could enforce data integrity in the application code.  Spend any time doing this and you will conclude that application managed data integrity is little more than a conceit.

In addition, the query optimiser can use foreign keys to infer things about table joins, so FK's will result in more efficient query plans.

There are plenty of other benefits to foreign keys as well.  Do everyone a favour - put the FK's on the database.


Create a view for A table with new columns JOIN(ed) on B table queries about the last maching records with certain parameters. How to simplify code?

(Getting rid of some redundant parens, plus wrapping and indenting:)

Create  VIEW products_view_joinA_and_JoinB AS
select  t1.code, t1.name, t2.stock, t2.average_price as cost_usd,
        t5.date as last_buy
    from  (products t1
    left join  ( SELECT  t3.stock, t3.code, t3.average_price
                    from  transactions t3
                    where  t3.date =
                          ( SELECT  t4.date
                                from  transactions t4
                                where  t4.code = t3.code
                                order by  t4.date desc
                                limit  1
                          )
               ) t2  on t2.code = t1.code
    left join  ( SELECT  t6.code, t6.date
                    from  transactions t6
                    where  t6.date =
                          ( SELECT  t7.date
                                from  transactions t7
                                where  t7.code = t6.code
                                  and  t7.mode = "Buy"
                                order by  t7.date desc
                                limit  1
                          )
               ) t5  on t5.code = t1.code
          )

transactions needs an index on (code, date), but you effectively have such since InnoDB tacks the PK on the end of any secondary key.

Other indexes needed:

transactions:  INDEX(code, mode, -- in either order
                     date)  -- (for t7)

If you are using an old version of MySQL, LEFT JOIN ( SELECT ... ) is problematic since it would repeatedly execute the derived table (that subquery).  5.6 improves on it.  Ditto for having two JOIN ( SELECT ... ) in a query.

Do you need LEFT?

VIEWs are syntactic sugar, and rarely (perhaps never) provide any performance.  Sometimes, they make performance worse.  So, experiment with the query outside a VIEW, then see if it still works as well in a VIEW.

If you are using MySQL 8.0 (or MariaDB 10.2), you could consider CTEs and use WITH to introduce the common code.

Other approaches to redundant code:

SELECT @var := ... FROM ...;  -- to compute a single value that is needed repeatedly.

or...

CREATE TEMPORARY TABLE t  SELECT ...;  -- to build a table that you need to use twice.


Improving performance of mapping standardized values with non equi joins - LIKE operator

Thanks to everyone's comments and suggestions, I tried all of the ideas @mustaccio posted, but in the end the fastest strategy was to abandon the mapping table altogether, and just go with a big ugly CASE statement to handle all of the situations, and insert that into another staging table. Execution time dropped to only a few seconds, and I can't argue with that. The situations to handle are mostly static, so if I have to re-deploy a SSIS package once or twice a year to add new situations if they come up, so be it.

Final example insert statement (not exact, just going off memory):

INSERT INTO STAGINGTABLE_TWO (TextColumn)
SELECT
  CASE
    WHEN TextColumn LIKE '%FOO BAR%' THEN 'Foo Bar'
    WHEN TextColumn LIKE '%FOO%' THEN 'Foo'
    WHEN TextColumn LIKE '%BAR%' THEN 'Bar'
    ELSE 'Unknown'
  END
FROM STAGINGTABLE;


How to design a graph database in this scenario?

Should I create 1 MandatoryData for each book?

Yes.

If you cannot get rid of MandatoryData object, you should have one such object per book.

Otherwise I would simplify the model and move any mandatory attributes to the Book object, which will reduce ne04j database size. Modeling relationships which are always 1:1 is possible, but not optimal. You can improve query performance by keeping the data in one object.


Study material for Advanced Databases module inc SQL, Relational Model, ERM & TM

I found the course at Standford to be helpful - if you sign up, you can view past lectures on many of these topics.

https://class.stanford.edu/courses/Engineering/db/2014_1/info


Large Database backup approx 2 TB and restore with limited disk space

I would answer your specific question

  I thought of using backup compression but later came to know it can be used only between same Sql server Editions. Is it TRUE ?

NO this is not true. From BOL document about Compression

  Every edition of SQL Server 2008 and later can restore a compressed backup.

You can take backup with compression on any version of SQL Server that supports backup compression and can restore to any version you like provided the version on which you are restoring does not gives error about enterprise features

  Also let me know if I use Sql compression, will there be any change between the original DB and compressed DB restored on the new DB ?

The database would not be changed at all, whatever would be changed is backup size. So if you have 2TB of database on source then when you take backup with compression it might be around 1 TB(A simple guess, size may vary) but when you restore the same backup on destination the size required would again be 2 TB.

Compression is just to make backup size small, it has NO affect on database original size


Why is sql server waiting for acknowledgment?

SQL Server by default adheres to ACID database rules.  The A in ACID stands for atomic, meaning that the whole transaction happens, or none of it does.  The I in ACID stands for isolation, meaning that transactions are isolated from one another, so either all the results from one transaction are visible to another transaction, or none of them are.  This is accomplished in SQL Server by using locks for both reads and writes (at least by default).  Reads (SELECTs) obtain a read lock, which prevents any write (UPDATE or INSERT) operations on those items (or pages, or the whole table depending on what SQL's black magic lock escalation algorithm decides on).  In order to preserve Isolation, read locks for queries must be held until all results have been sent or buffered.  This prevents the writes from obtaining their locks (or rather causes them to wait to obtain them), unless the writes are otherwise isolated (see other answer about snapshot isolation).  If not, some of the results from a subsequent write could end up in the results of the query that occurred before the write.

There are several solutions for this, each with trade-offs:

Buffer results on the client side (read all rows immediately as quickly as possible and do further processing later).  This may require lots of client-side memory if there are lots of results, and will still block updates until the client is done buffering (which is why it must be completely buffered before any subsequent processing).
Buffer results on the server-side.  You can do this by selecting the data into a temp table and then returning the temp table instead of the original query.  This will require server-side memory and/or disk and could therefore interfere with other server-side operations.
Use WITH (NOLOCK) on the SELECT of the read operation.  This tells SQL Server to ignore isolation for this read, which means that you could get the original version of a row at the beginning of the results and a modified version of another row later in the results when both rows were modified by the same transaction and the versions returned were never in the database at the same time (ie. the rows were modified during the query, after the first row was returned but before the second row was).  This answer has the least performance cost, but produces non-ACID results by breaking Isolation and/or Atomicity (depending on how you look at it).
Use snapshot isolation (see other answer--I'm not an expert with snapshot isolation).  Note that I've had significant performance issues with snapshot isolation, and it will require plenty of server-side resources as well.

There may be other solutions as well, but these are the most common.


When inserting one record each 15 seconds I would not be worried about using triggers.

Consider these two heap tables, with one rowversion column and 50 float columns each.

CREATE TABLE dbo.floattable1
(Rowver ROWVERSION
,Float1    float
,Float2    float
...);
CREATE TABLE dbo.floattable2
(Rowver ROWVERSION
,Float1    float
,Float2    float
...);

Both tables have ~2M rows (1940480).

Solution with a trigger

When we create an AFTER INSERT TRIGGER on dbo.floattable1 to also insert into dbo.floattable2

CREATE TRIGGER dbo.floattrigger
ON dbo.FloatTable1
AFTER INSERT
AS
INSERT INTO dbo.floattable2(Float1    ,Float2     ,Float3     ,Float4     ,Float5     ,Float6     ,Float7     ,Float8     ,Float9
,Float10  ,Float11  ,Float12  ,Float13  ,Float14  ,Float15  ,Float16  ,Float17  ,Float18  ,Float19  ,Float20  ,Float21  ,Float22
,Float23  ,Float24  ,Float25  ,Float26  ,Float27  ,Float28  ,Float29  ,Float30  ,Float31  ,Float32  ,Float33  ,Float34  ,Float35
,Float36  ,Float37  ,Float38  ,Float39  ,Float40  ,Float41  ,Float42  ,Float43  ,Float44  ,Float45  ,Float46  ,Float47  ,Float48  ,Float49  ,Float50 )
SELECT Float1     ,Float2     ,Float3     ,Float4     ,Float5     ,Float6     ,Float7     ,Float8     ,Float9     ,Float10  ,Float11  ,Float12  ,Float13
,Float14  ,Float15  ,Float16  ,Float17  ,Float18  ,Float19  ,Float20 ,Float21  ,Float22  ,Float23  ,Float24  ,Float25  ,Float26  ,Float27  ,Float28  ,Float29
,Float30  ,Float31  ,Float32  ,Float33  ,Float34  ,Float35  ,Float36  ,Float37  ,Float38  ,Float39
,Float40  ,Float41  ,Float42  ,Float43  ,Float44  ,Float45  ,Float46  ,Float47  ,Float48  ,Float49  ,Float50
FROM inserted;

Afterwards, we insert one whole row in dbo.floattable1 resulting in trigger execution:

And a very simple plan with 1 logical read.

Solution for the job / FlagColumn

Drop the trigger, add a flagcolumn, set it to 1 and add an index on it.

DROP TRIGGER  dbo.floattrigger;
ALTER TABLE dbo.floattable1
ADD FlagColumn bit;
UPDATE dbo.floattable1 SET FlagColumn = 1;

CREATE INDEX IX_FlagColumn on dbo.floattable1(FlagColumn);

Insert 40 test records with flagcolumn = 0

SET STATISTICS IO, TIME ON;
INSERT INTO dbo.floattable1(Float1    ,Float2     ,... ,FlagColumn)
SELECT TOP(40) Float1     ,Float2     ,... , 0
FROM dbo.floattable1

Insert into dbo.floattable2 and update the flagcolumns

BEGIN TRANSACTION
INSERT INTO dbo.floattable2
(Float1   ,Float2     ,...)
SELECT Float1     ,Float2     ,...
FROM dbo.floattable1
WHERE FlagColumn = 0;

UPDATE dbo.floattable1
SET FlagColumn = 1
WHERE FlagColumn = 0;
COMMIT TRANSACTION

The first insert can be improved to be equal to the trigger's insert, by removing the RID lookup (adding all floats to the included column list of the index).

But the update will give some extra overhead.

.

The advantage you have here is that you can schedule this when you want, and have some more information on your data via the flag. The extra index means extra overhead and Insert + Update means extra overhead than only issueing an Insert.

Both ideas are viable, but it will really depend on how complex / simple the table is used. If it is only used as a logging table, and the trigger will fire for one record each 15 seconds (and nothing else), the trigger should be fine.
If the data sync does not need to happen that fast / often, data load with an INSERT + UPDATE should be fine too.
YMMV, always test before implementing.


SIMPLE or FULL recovery model for databases?

When should I use the full recovery model and when should I use the simple recovery model for databases?

You should use the full recovery model when you require point-in-time recovery of your database.  You should use simple recovery model when you don't need point-in-time recovery of your database, and when the last full or differential backup is sufficient as a recovery point. (Note: there is another recovery model, bulk logged.  For more information on the bulk-logged recovery model see this reference)

Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction log for database 'DATABASE NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The reason you got that error (most likely) is because you haven't been backing up your transaction log.  When it isn't backed up, then it will continue to physically grow the transaction log file (provided autogrowth is enabled and maxsize allows) because it can't reuse any of the "portions" of the transaction log (virtual log files).  It can only mark those VLFs for reuse and allow the "wrap-around" nature of the transaction log when you do a transaction log backup (and a few other requirements, such as no active transactions, some replication aspects, etc.).

To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command
......
It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?

This helped you because by setting your database to the simple recovery model you told SQL Server that you no longer care about point-in-time recovery, and the requirement to ensure that virtual log files no longer need to be preserved and marked as active, now a checkpoint process marks these VLFs as inactive.
Excerpt/quote taken from this MSDN reference:

Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log. In contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation.

Then you did a physical database file shrink and because there was free space in your transaction log now it was able to physically shrink the NTFS file.
Reading worth spending some time on:

Recovery Models
Managing Transaction Logs (Gail Shaw)
Factors That Can Delay Log Truncation

EDIT after your Edit:

Is the new recovery model and databaseshrink going to be a conflict with this script?

That BACKUP DATABASE command will work with either recovery models.  As for the routine database shrink... DON'T DO IT!!!!  Seriously, size your database accordingly, and if you utilize the full recovery model then ensure that you are doing routine and frequent transaction log files, not just to keep the transaction log size at bay but to also meet recovery point objects.

We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?

If your database is utilizing the full recovery model, then yes you should be doing transaction log backups.  If your database is in simple recovery, then you physically can not do a transaction log backup.
As for what recovery model to use (simple vs. full), we can't make that decision for you.  Only you, your business team, and your SLAs can.


SQL Server 2005-2008 Compatibility Check

Is the 2005 instance still your production instance? If so, you could use a server side trace to capture a workload and replay it on the 2008 instance. You should try to capture your normal workloads. You have to decided what is normal in your system. This wouldn't guarantee that all stored procs will work, but you day to day activity should be fine.

As far as compatibility levels please review the BOL link:
http://msdn.microsoft.com/en-us/library/bb510680.aspx


Oracle strong ref cursor with dynamic sql

There are probably better ways to achieve your underlying goal, but if you really want values from dynamic SQL into a strongly typed refcursor I guess you could do something like this:

create table TESTTABLE(TESTID number not null);
create global temporary table TEMPTESTTABEL(TESTID number not null);

create or replace procedure testprocedure is
  type testtablerefcursor is ref cursor return TESTTABLE%rowtype;
  type testtabletable is table of TESTTABLE%rowtype;
  mycursor testtablerefcursor;
  mysyscursor SYS_REFCURSOR;
  mytesttable testtabletable;
begin
  open mysyscursor for 'select * from TESTTABLE';
  fetch mysyscursor bulk collect into mytesttable;
  close mysyscursor;
  forall i in mytesttable.first..mytesttable.last
   insert into TEMPTESTTABEL values mytesttable(i);
  open mycursor for select testid from TEMPTESTTABEL;
end;
/


Improving performance of query with `WHERE  ''`

Do those columns have indexes on them at all? That is rather vital information for questions like this. If there is no index covering either of those columns at all then there is nothing it can do except table scan to match those filters.

If there is at least one index covering one (or both) that it could use, it may be deciding against it anyway due to the SELECT *. If the selectivity of the index suggests to it that many rows will match then a table scan may actually be more efficient than an index seek/scan followed plus row lookups to get the rest of the data that is not contained within the index.

For instance if I run

SELECT parent_id FROM org WHERE parent_id <> ''

on one of our systems it performs an index seek only. Running

SELECT * FROM org WHERE parent_id <> ''

performs a table scan instead. If I force the index choice with

SELECT * FROM org (WITH(INDEX(ix_org_parent_id) WHERE parent_id <> ''

it performs an index seek to locate the start of the records, then the RID lookups to find all the other column data. According to the query plan output it judges this to be ~6 times as costly as just doing the table scan, the estimated costs of the three queries above being 0.22926, 1.81429, and 11.1612 respectively. In this case the number of rows matching the WHERE clause is ~55% of ~50,000.


Dealing with partitioned tables in Postgresql

Let's quickly check the COPY documentation:

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' ...}
    ...

Your friend will be the query part.  It means that you cannot only copy from a table, but also an arbitrary query.  And example can be:

COPY (SELECT * FROM ONLY your_table) TO '/path/to/dump.sql';

Notes:

you have to specify an absolute path with COPY
it is usually handier to use \copy from psql as it copies the dump to the client machine, not the server.  In your case this may be irrelevant.
in practice, keeping daily tables is usually too fine-grained.  If all your queries can use constraint exclusion, then it might be all good, otherwise you might notice some planning overhead.


Start SQL Server with the -m parameter, specifying the application name allowed to take the only available connection. For instance, to allow sqlcmd to take the connection:

sqlservr -m"sqlcmd"

If you want to work with SQL Server Management Studio, use this:

sqlservr -m"Microsoft SQL Server Management Studio - Query"

Remember that the "Connect..." button in Management Studio connects the object explorer, that would take your connection without letting you open another one to run the query. In this case, open a query window either disconnected or connected to another server and then use the "Connection, Change Connection" command from the context menu.

See the docs for more information: http://msdn.microsoft.com/en-us/library/ms188236.aspx

An interesting thing to note is that the Configuration Manager doesn't let you enter -m"Microsoft SQL Server Management Studio - Query" as a parameter (I suppose it's the spaces in the parameter), so this is an option only when starting SQL Server from the command line. If you decide to do this, remember to start cmd as an Administrator.


Ensure you read the VMware / SQL Server best practices guidelines.

This is taken from that document:

  Utilize the VMware Paravirtualized SCSI (PVSCSI) Controller as the virtual SCSI
  Controller for data and log VMDKs. The PVSCSI Controller is the optimal SCSI
  controller for an I/O-intensive application on vSphere allowing not only a higher I/O
  rate but also lowering CPU consumption compared with the LSI Logic SAS. In addition,
  the PVSCSI adapters provide higher queue depth, increasing I/O bandwidth for the
  virtualized workload.

  Use multiple PVSCSI adapters. VMware supports up to four (4) adapters per a VM and
  as many as necessary, up to this limit, should be leveraged. Placing OS, data, and
  transaction logs onto a separate vSCSI adapter optimizes I/O by distributing load
  across multiple target devices and allowing for more queues on the OS level. Consider
  distributing disks between controllers. vSphere 6.7 supports up to 64 disks per
  controller

VMDK disk files have their own queue, with 64 entries, by default.  PVSCSI adapters have a default queue length of 256 per PVSCSI adapter, however those settings can be modified.


Sort buffers and tables on disk, MySQL/InnoDB

If you are still seeing on-disk temporary tables with your max_heap_table_size and tmp_table_size set that high, then chances are you have a handful of queries with GROUP BY/ORDER BY (or a complex join) that are not using indexes effectively and are generating an enormous amount of intermediate data or you have TEXT/BLOB columns somewhere.  Since you said you don't have the latter, I'm guessing it's the former.

You have a few options

Find the queries causing the problem.  You can use pt-query-digest to help summarize the slow query log and make it easier to track down which queries are going to disk.
Ignore it.  This represents less than 1% of your queries.  Unless it's causing you an actual problem, don't worry about it.
If you can't ignore it, keep bumping up max_heap_table_size and tmp_table_size until the problem goes away.  I wouldn't recommend this.

Keep in mind that MySQL pushes large temporary tables to disk for good reason.  Eventually, you risk pushing other data out of memory (or worse, running the server out of memory) in order to process a handful of queries with very large data sets.  Those settings are per table, so if you have any level of concurrency, you could get into trouble very quickly.  I run in production with tmp_table_size/max_heap_table_size at 768MB, but I have 140GB of RAM to work with.  Your headroom is much smaller with only 12GB.


Join 1 column to multiple columns in another table

Try something like this:

SELECT p.ID, d1.hostname, p.port, p.timestamp, p.description, d2.hostname
FROM Devices d1, Portmap p, Devices d2
WHERE d1.id = p.networkdeviceID and d2.id = p.connecteddeviceID;


The performance numbers that someone else gets from their array compared to the numbers that you get are totally meaningless.  I could have 1000 disks in my array backed with 512 Gigs of cache or I could have 2 7200 RPM disks in a RAID 10.

Without knowning the exact configuration that your array is configured in the numbers are just numbers.

Another thing to note is that 4k IO are a worthless measure as are 512k IO.  SQL Server will be doing almost all of it's IO in 64k IO.


it at the bottom of alphabetically sorted list.
But as you can't see the list very well, i posted an image.
So you see it is direct about utf8mb4_vi
I find your size of the window quite small.


Does the MongoDB background flush (MMAP) update the entire document even if only a small portion has changed? i.e. set on an array position

The array contains 348 empty documents to begin with, and over the course of a week these array element will have sub-documents inserted (if empty) and then subsequently updated (if they already exist). The sub-documents are approximately 100 bytes in size and are not indexed.

One consideration with this use case is that your documents are consistently growing. MongoDB used a record allocation or padding strategy to allow documents to grow in-place. For example, if your document starts off as 1000 bytes MongoDB 2.6 or newer will round this up to a 1024 byte record allocation for MMAP (as per the Power of 2 Size default strategy). Updates that don't grow the size of the document beyond the current record allocation are more efficient for the server to execute.

However, if you added 100 bytes to a document which was initially 1000 bytes, the document would have to be moved to a new record allocation in storage (and associated index entries would also have to be updated). So in this example, the next allocation for a 1100 byte document would be 2048 bytes (allowing for ~9 more 100 byte fields to be added before a new record allocation was needed for this document). Indexes in MongoDB include the storage location of the document, so a document move will result in an update for every index entry referencing that document.

You can check the frequency of document moves by looking at the nmoved value for slow updates (or by enabling increased levels of logging / system profiling). Frequent document moves can definitely have a performance impact. Common strategies include either reconsidering the data model (eg. moving the growing portion of the document to a separate collection if appropriate) or adding manual padding to the initial document allocation. The default power of 2 allocation strategy is designed to avoid the need for manual padding in most cases, but if your documents start small and grow quickly you might be able to avoid some initial document moves.

  So my question is, when the document is flushed to disk, what actually gets written, the entire document or just the sub-document that has been updated?

The answer will depend on the size of your document and the nature of updates since the last background flush. I'll assume you are using a default configuration with MMAP storage engine and journal enabled.

By default data changes are written twice: once to fast append-only journal files (committed to disk every 100ms) and again to a private view in memory (flushed to data files every 60s). The background flush process is a periodic asynchronous write of all pages that have been "dirtied" in memory since the last flush. Journal commit and background flush intervals can be influenced by both server configuration and write concerns. For a good overview of the process see How MongoDB’s Journaling Works.

The MMAP storage engine will fetch the full document into memory before applying updates. The standard x86 page size is 4KiB so a single document may be represented by one or more pages -- or multiple documents may be part of a single page in memory.

So, if you are updating a single document the writes will include:

all changes written to the journal
all changes written to the oplog (if that node is part of replica set)
any pages dirtied for that document since the last background flush

An important caveat is "since the last background flush". Multiple updates affecting the same pages within a given sync interval will effectively be batched.

If you're trying to get to the bottom of performance issues then consistently high background flush times (particularly as a large or increasing percentage of the default 60s flush interval) are definitely of concern, but should be reviewed in the context of other metrics such as page faults, I/O stats, and lock percentage. I would also review the MongoDB Production Notes for general tips and upgrade to the latest MongoDB production release for your major version (i.e. latest 2.6.x or 3.0.x if there's a newer x than your current version).


Innodb composite primary key with 4 columns vs MyIsam

(For InnoDB)  All the rows with the same a will be next to each other; all rows with same a and b, will be adjacent, etc.  So, I guess "yes" is the answer to #1.
No.  An InnoDB "block" is 16KB.  If the entire table fits in one block, then everything will be in that one block.  Note:  The PRIMARY KEY is "clustered" with the table.  So talking about the layout of the PK and the layout of the table are one and the same.
(Q3 has been changed; this edit addresses the new Q3.)   For WHERE a='1' AND b='2', whether MyISAM or InnoDB, any index beginning with (a,b) or (b,a) will be optimal.  Note "beginning with"; since the PRIMARY KEY already begins with (a,b), adding another index not be useful.  See my cookbook on creating indexes.

MyISAM and InnoDB differ (for this discussion) in the following ways:

MyISAM does not "cluster" the PK with the data.  The PK is a separate BTree and the data is (mostly) appended to without regard to PK order.
MyISAM index blocks are 1KB.

Did you have other questions about the difference between MyISAM and InnoDB?

In order to determine the optimal indexes, first determine what the queries will look like.  WHERE c = 124 begs for INDEX(c), or at least an index that starts with c.  Rearranging the PK to PRIMARY KEY(c,a,b,d) would suffice, but might hurt other queries.


Unexpected HY001: Memory allocation error after MySQL upgrade

And you have large JSON or Geometry columns?  (Always provide SHOW CREATE TABLE when asking a question.)
Either increase or decrease sort_buffer_size and innodb_sort_buffer_size.
https://bugs.mysql.com/bug.php?id=103465
and
https://bugs.mysql.com/bug.php?id=103225


How to link Users and Jobs with Addresses in my database structure?

Business rules

We determined via comments that —regarding your business context and within the scope of your question— there are actually three different entity types of relevance, i.e.:

User
Job
Address

We also defined that there are two distinct relationships that involve said entity types, and you provided more details about such connections via your following comments:

  …[I]n theory two people living in the same place could use the system.
  …[A] User is tied to a single address.
  …[A] Job can only be associated with a single address at any given time. But two distinct Job's can have the same address.

Therefore, the significant business rules can be formulated as follows:

An Address houses zero-one-or-many Users
An Address locates zero-one-or-many Jobs

Logical model

So, I have derived a simplified IDEF1X1 logical model that depicts the aforementioned business rules in Figure 1:

In said logical model, you can see that I have represented the three discussed entity types separately, and established (a) the association between Address and User and (b) the conection between Address and Job by means of the migration2 of the Address PRIMARY KEY (PK) to both User and Job, which enforces two different one-to-many (1:M) relationships, as your scenario description demands.

Expository implementation

Consequently, I created an expository DDL structure based on the logical model presented above that can serve as a reference for a concrete implementation:

-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient INDEX strategies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Country
(
    CountryCode     CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Country      PRIMARY KEY (CountryCode),
    CONSTRAINT AK_Country_Name UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Region
(
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Region                      PRIMARY KEY (CountryCode, RegionCode),
    CONSTRAINT AK_Region_CountryCode_and_Name UNIQUE      (CountryCode, Name) -- ALTERNATE KEY.
);

CREATE TABLE Locality
(
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    LocalityCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Locality                                 PRIMARY KEY (CountryCode, RegionCode, LocalityCode),
    CONSTRAINT AK_Locality_CountryCode_RegionCode_and_Name UNIQUE      (CountryCode, RegionCode, Name) -- ALTERNATE KEY.
);

CREATE TABLE MyAddress
(
    AddressId       INT      NOT NULL,
    CountryCode     CHAR(3)  NOT NULL,
    RegionCode      CHAR(3)  NOT NULL,
    LocalityCode    CHAR(3)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress                  PRIMARY KEY (AddressId),
    CONSTRAINT FK_from_MyAddress_to_Locality FOREIGN KEY (CountryCode, RegionCode, LocalityCode) -- Composite FOREIGN KEY.
        REFERENCES Locality (CountryCode, RegionCode, LocalityCode)

);

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    HouseAddressId  INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY (UserId),
    CONSTRAINT AK_UserProfile_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
    (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT AK_UserProfile_Username          UNIQUE      (Username), -- ALTERNATE KEY.
    CONSTRAINT FK_from_UserProfile_to_MyAddress FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)
);

CREATE TABLE Job
(
    JobNumber       INT      NOT NULL,
    AddressId       INT      NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Job                   PRIMARY KEY (JobNumber),
    CONSTRAINT FK_from_Job_to_MyAddress FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)
);

ACID Transactions

As in any relational database implementation, you should seriously consider using ACID TRANSACTIONS in order to protect the integrity and consistency of the data you are working with.

Making data retrieval easier with the use of a VIEW

If you want to access user and address data points directly from a single resource, it would be convenient to create a VIEW (i.e., a derived or virtual table) that displays columns that belong to the base tables named UserProfile and MyAddress, e.g.:

CREATE VIEW UserAndAddress AS

    SELECT U.UserId,
           U.FirstName,
           U.LastName,
           U.BirthDate,
           U.GenderCode,
           U.Username,
           A.CountryCode,
           A.RegionCode,
           A.LocalityCode,
           A.Postcode,
           A.Etcetera
      FROM UserProfile U
      JOIN MyAddress   A
        ON U.HouseAddressId = A.AddressId;

In this way, you can SELECT directly FROM the VIEW without having to write the JOIN clause and the condition every time you have to retrieve related information. Of course, you can employ a similar approach for job and address data.

Many-to-many relationship between User and Address

In case that you face the need to implement a many-to-many (M:N) relationship between the Address and User entity types —which would not be unusual in other contexts—, some small modifications to the business rules formulations would be required, as shown bellow:

A User keeps zero-one-or-many Addresses
An Address is kept by zero-one-or-many Users

Thus, I drew a new logical model that portrays this situation in Figure 2:

As demonstrated, an associative entity that connects (a) User with (b) Address is included, and I named it UserAddress accordingly; its PK is made up of two different FOREIGN KEYs, and each of them makes a reference to the respective entity type.

I coded an illustrative DDL structure with this alternative arrangement:

CREATE TABLE MyAddress
(
    AddressId       INT      NOT NULL,
    CountryCode     CHAR(2)  NOT NULL,
    RegionCode      CHAR(2)  NOT NULL,
    LocalityCode    CHAR(2)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress                  PRIMARY KEY (AddressId),
    CONSTRAINT FK_from_MyAddress_to_Locality FOREIGN KEY (CountryCode, RegionCode, LocalityCode) -- Composite FOREIGN KEY.
        REFERENCES Locality (CountryCode, RegionCode, LocalityCode)
);

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    AddressId       INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY (UserId),
    CONSTRAINT AK_UserProfile_FirstName_LastName_Gender_and_BirthDate UNIQUE -- Composite ALTERNATE KEY.
   (
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT AK_UserProfile_Username UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE UserAddress
(
    AddressId       INT      NOT NULL,
    UserId          INT      NOT NULL,
    IsPhysical      BIT      NOT NULL,
    IsShipping      BIT      NOT NULL,
    IsBilling       BIT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_MyAddress              PRIMARY KEY (UserId, AddressId), -- Composite PRIMARY KEY.
    CONSTRAINT FK_UserAddress_to_User    FOREIGN KEY (UserId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FK_UserAddress_to_Address FOREIGN KEY (AddressId)
        REFERENCES MyAddress (AddressId)
);

If the cardinality of the relationship among Job and Address also happens to be (or, who knows?, at some point in time becomes) M:N in your business domain, you can follow a method comparable to the one just detailed.

Notes

1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) theoretical work authored by the originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown. It is worth noting that IDEF1X was formalized by way of first-order logic.

2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.


PostgreSQL - PUBLICATION tries to copy the data which already exists in another database?

By default, CREATE SUBSCRIPTION will first copy all the existing data from the source tables. The idea is that you usually start with an empty destination database created with something like pg_restore -s.

If you don't want to have the initial data copy, create the subscription like this:

CREATE SUBSCRIPTION ...
WITH (copy_data = false);

See the documentation for details.


How to design a database table in a context where the columns can potentially change with each new row

What you probably want to do is normalize your data. Please read up on it in this post: What is Database Normalization in SQL?
To summarize, you should actually restructure your table design by removing all the word columns and storing the words themselves into their own table that is referenced by your Titles table.
Your Words table would have a unique identifying column like WordId which would be referenced by your Titles table which itself would have a TitleId column.
You can then have a third table to relate the many-to-many relationship between the two tables (named something like TitleWords) and it would have TitleId and WordId as columns so you could link between the Titles and Words tables.
Implementing your database design this way not only helps your maintainability of the relational data structure, but it also reduces redundancy and saves space in your database leading to efficiencies as well.


Inserting Master database data into my database table

I very seriously question the use of a trigger here.  Your trigger is going to dramatically slow down the updates of your table.  That being said your query should look more like this:

INSERT INTO zzuomupdate(date_cu, Query_r)
SELECT top(15) getdate(), dest.text AS [Query]
FROM master.sys.dm_exec_query_stats AS deqs
CROSS APPLY master.sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

The way you had it you were trying to pull 15 rows worth of information into a single column and that just doesn't work.  The INSERT INTO SELECT syntax is the way to go here.


Get top 5 students for each class in many-to-many relationship

Student with GPAs is an aggregation
Class information is not an aggregation

Solution:

Use two queries
Query the GPAs (CTE and Window Function)
Query the class information

You will probably need two indices on student_id and class_id to boost the performance.
Example queries in MySQL 8.0.20:
WITH ordered_gpa AS(
    SELECT students.id,
        students.gpa,
        student_class_rel.class_id,
        rank() over (partition by student_class_rel.class_id order by students.gpa DESC) gpa_rank
    FROM student_class_rel
    INNER JOIN students
    ON student_class_rel.stu_id = students.id
)
SELECT *
FROM ordered_gpa
WHERE gpa_rank <= 5;

Here, we use Windows rather than GROUP BY, which is the PARTITION BY to split the rows.
SELECT class_id, name, room
FROM classes
WHERE class_id IN (
  SELECT DISTINCT class_id
  FROM student_class_rel
) tmp;

You could combine this query with the first one.


ibdata1 grows exponentially when innodb_file_per_table is configured

ibdata1 can still grow despite innodb_file_per_table being enabled. Why ?

Question: If InnoDB tables and associative indexes are written to individual tablespace files (file extension .ibd), what information still needs to be written to ibdata1 ?

Answer: Here are the following classes of information written to InnoDB's system tablespace:

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

I have discussed this before:

Dec 09, 2011 : What is the best way to reduce the size of ibdata in mysql?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?

EPILOGUE

Given enough transactions, rollback segments and undo logs to support REPEATABLE READs can make ibdata1 grow. INSERTs and UPDATEs to InnoDB tables with Secondary Indexes can pile up  in the Insert Buffer. The Double Write Buffer Provides a second level of data redundancy in the event of a crash and subsequent crash recovery on mysqld startup. The only thing that can never grow is the Data Dictionary (unless you have DDL statements to create new InnoDB tables).


assuming you have an index on time_track

move the offsets to the other side of the inequality so that the arithmetic is only done once and so that an index on time_track can be used.

SELECT (track, distance_1, aircraft_time)
  FROM eco.tracks
  WHERE time_track >= (%s) + INTERVAL '10 seconds'
    and time_track <= (%s) - INTERVAL '10 seconds'
ORDER BY distance_1 asc LIMIT 1,

etc...

or you could use BETWEEN instead of and.

SELECT (track, distance_1, aircraft_time)
  FROM eco.tracks
  WHERE time_track BETWEEN
        (%s) - INTERVAL '10 seconds'
    AND (%s) + INTERVAL '10 seconds'
ORDER BY distance_1 asc LIMIT 1,


MYSQL search for each name in 2 columns and add values associated for each player

Glorfindel's answer is good within the parameters of your question, but I'll talk a bit about how else you could have designed it, in case that's helpful.

Basically, having a single table with player1 and player2 isn't great because the two players have the same relationship to the game so it should be represented the same way. If it's important to keep track of which one was #1 and which was #2, then it's probably better to think of that as an attribute of the player's membership in the game.

CREATE TABLE `player` (`id` PRIMARY KEY, ...);

CREATE TABLE `game_results` (`id` PRIMARY KEY, ...);

CREATE TABLE `game_player` (
  `game_id`,
  `player_id`,
  `destroyed`,
  PRIMARY KEY (`game_id`, `player_id`),
  FOREIGN KEY (`game_id`) REFERENCES `game_results` (`id`),
  FOREIGN KEY (`player_id`) REFERENCES `player` (`id`),
  ...
);

Some open questions remain from there.

Where should the results be recorded?

You could just have a nullable foreign key winner from game_results to game_player (id).
You could have an attribute result of game_player. This could be an enum if you're in MySQL 5.7, or a foreign key to a small table of possible results. I'd prefer this because it would better accommodate draws and annulments.

The join-table design naturally allows for games with more than two players. Whether or not that's good depends on the situation. Assuming we want to limit the number of players, how would we do that?

Add another enum or foreign key to an enum table representing the idea of "player 1" and "player 2". Add a UNIQUE KEY game_player (game_id, player_number). This is what I would suggest.
Add a FOREIGN KEY game_player(partner) REFERENCES game_player(id). This would work for your current situation, but isn't an open-ended solution.
You can't use a CHECK constraint in MySQL. This is one of the reasons I dislike MySQL. Here's the reference.

In any case, the final query you're trying to build would look something like

SELECT gp1.player_id, SUM(gp1.destroyed) AS Fmov, SUM(gp2.destroyed) AS Fmov
  FROM `game_player` gp1
  [LEFT | INNER] JOIN `game_player` gp2
    ON gp1.game_id = gp2.game_id
      AND gp1.player_id <> gp2.player_id
  [WHERE gp1.result in (`win`, `loose`, `tie`)]
  GROUP BY gp1.player_id;


PostgreSQL list of integers separated by comma or integer array for performance?

It's highly likely that the best approach will be a side-table of sometable(main_id, value) where you have a composite index on (main_id, value). This allows very fast lookups to see "for this mainid, does this value exist". This will let you enforce foreign key relationships. Unless you have a good reason, use this conventional relational approach.

Failing that, you can and should use an array field instead of a comma separated list. Using a comma-separated list is just downright horrid from a design point of view. It makes queries harder to write, more error prone, and forces you to do lots of slow and inefficient string manipulation and number parsing just for simple operations, and prevents any kind of integrity checking without very inefficient CHECK constaints or triggers. I think Bill nailed it with:

  1,2,3,banana,5

With an array you can use the intarray extension to provide a GiST index that lets you quickly test if the array contains a given value using an indexable @> or <@ operation. You may want to add the btree_gist extension too, so you can create a composite GiST index of main_id, the_values_array, in case your queries are usually of the form:

WHERE main_id = blah AND the_values_array @> ARRAY[42]

(or have two separate indexes and see if it'll do a bitmap index scan).

You can't enforce a foreign key relationship into an array in PostgreSQL yet, though the feature seems to be on the way. You'd need to do it with somewhat complicated triggers in the mean time. Still, it's a lot better than a comma-separated list.


Data modeling for employee has multiple managers

That sounds like a many to many relationship where you need a bridge table. You would store everyone in an employee table with an ID and then have a separate hierarchy table with manager and employee combinations.


GSE1015N A spatial reference system named "NAD83_SRS_1" does not exist

I just realized that there was a bug when I enable the databases to support spatial function.

db2se enable_db sample

While running this command line, it was interupted, but DB2 still reports that it successfully enabled the spatial function. Actually, this command takes very long to finish (more than 5 mins in my case), which makes me thought it gets stuck and interupt it with Ctrl+C. So the solution is just to wait until it finish without touching any keyboard.


WSREP: failed to report last committed and stop

warning WSREP: Failed to report last committed

This is just a warning, and in itself isn't a cause for concern. I think it's unlikely that this caused the node to stop. See also Percona's blog post about it.

And then:

  mysqld_safe Number of processes running now: 0

Again, according to Percona (Support):

  Your mysqld process was killed, most likely by OOMkiller. Check the system log. Joining node will refuse to accept connections until it synchronizes with cluster.


As per MongoDB documentation here  The Ops Manager Backup Agent provides scheduled snapshots and point-in-time recovery of your MongoDB replica sets and sharded clusters.

How it Works

A lightweight Backup Agent runs within your infrastructure and backs up data from the MongoDB processes you have specified.

  NOTE  : Only sharded clusters or replica sets can be backed up. To back up a standalone mongod process, you must first convert
  it to a single-member replica set.

Backup Data

When you start backing up a MongoDB deployment, the agent performs an initial sync of the deployment's data as if it were creating a new, invisible member of a replica set. For a sharded cluster, the agent syncs each shard’s primary member and each config server. The agent sends the initial sync and oplog data over HTTPS back to Ops Manager.

The Backup Agent then tails each replica set's oplog to maintain on disk a standalone database, called a head database. Ops Manager maintains one head database for each backed-up replica set. The head database is consistent with the original primary up to the last oplog supplied by the agent.

The Backup Agent executes the initial sync and the tailing of the oplog using standard MongoDB queries. The cluster being backed up is unaware of the additional copy of the backup data.

The Backup Agent uses a MongoDB instance version equal to or greater than the version of the replica set it backs up.

The Backup Agent takes and stores snapshots based on a user-defined snapshot retention policy. Sharded cluster snapshots temporarily stop the balancer so that they can insert a marker token into all shards and config servers in the cluster. Ops Manager takes a snapshot when the marker tokens appear in the snapshot data.

For further your ref here , here and here


php/mysql merge duplicate records in table and create a new table

Here is the pure MySQL solution

create table finaltable select * from table1 where 1=2;
alter table finaltable add primary key (id);
insert into finaltable (username,storetype,id)
select * from
(select username,storetype,min(id) min_id
from table1 group by username,storetype) A;
update finaltable A inner join
(
    select username,storetype,
    MIN(IFNULL(filename,'ZZZZZZZZZZZZ')) filename,
    MIN(IFNULL(`date`  ,'ZZZZZZZZZZZZ')) 'date',
    MIN(IFNULL(`desc`  ,'ZZZZZZZZZZZZ')) 'desc',
    MIN(IFNULL(password,'ZZZZZZZZZZZZ')) password,
    MIN(IFNULL(email   ,'ZZZZZZZZZZZZ')) email,
    MIN(IFNULL(ftp     ,'ZZZZZZZZZZZZ')) ftp
    from table1 group by username,storetype
) B using (username,storetype) set
A.filename = B.filename,
A.`date`   = B.`date`,
A.`desc`   = B.`desc`,
A.password = B.password,
A.email    = B.email,
A.ftp      = B.ftp
;

First here is your sample data from the question

mysql> drop database if exists karto;
Query OK, 2 rows affected (0.01 sec)

mysql> create database karto;
Query OK, 1 row affected (0.00 sec)

mysql> use karto
Database changed
mysql> create table table1
    -> (
    ->     id int not null auto_increment,
    ->     username varchar(20),
    ->     filename varchar(20),
    ->     `date` date,
    ->     `desc` varchar(20),
    ->     storetype varchar(20),
    ->     password varchar(20),
    ->     email varchar(20),
    ->     ftp varchar(20),
    ->     primary key (id),
    ->     key username_storetype (username,storetype)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1
    -> (username,filename,`date`,`desc`,
    -> storetype,password,email,ftp) values
    -> ('ken',NULL         ,NULL        ,'good'  ,'google','1235'    ,'ken@me.com','Ken.me'),
    -> ('ken',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken','Product.txt','2012-1-1'  ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken',NULL         ,NULL        ,'good'  ,'amazon','4654'    ,'Ken1@me.com','Ken1.net'),
    -> ('ken',NULL         ,NULL        ,NULL    ,'amazon',NULL      ,NULL,NULL),
    -> ('ken','Amazon.txt' ,'2012-1-4'  ,NULL    ,'amazon',NULL      ,NULL,NULL),
    -> ('sam',NULL         ,NULL        ,'works' ,'google','477'     ,'Sam1@gm.com','sam'),
    -> ('sam',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('sam','Sales.txt'  ,'2012-10-10',NULL    ,'google',NULL      ,NULL,NULL),
    -> ('sam',NULL         ,NULL        ,'broken','ebay'  ,'3534534' ,'samlee@hm.com','Sam44'),
    -> ('sam',NULL         ,NULL        ,NULL    ,'ebay'  ,NULL      ,NULL,NULL),
    -> ('sam','june.txt'   ,'2012-2-12' ,NULL    ,'ebay'  ,NULL      ,NULL,NULL);
Query OK, 13 rows affected (0.02 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
| id | username | filename    | date       | desc   | storetype | password | email         | ftp      |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
|  1 | ken      | NULL        | NULL       | good   | google    | 1235     | ken@me.com    | Ken.me   |
|  2 | ken      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
|  3 | ken      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
|  4 | ken      | Product.txt | 2012-01-01 | NULL   | google    | NULL     | NULL          | NULL     |
|  5 | ken      | NULL        | NULL       | good   | amazon    | 4654     | Ken1@me.com   | Ken1.net |
|  6 | ken      | NULL        | NULL       | NULL   | amazon    | NULL     | NULL          | NULL     |
|  7 | ken      | Amazon.txt  | 2012-01-04 | NULL   | amazon    | NULL     | NULL          | NULL     |
|  8 | sam      | NULL        | NULL       | works  | google    | 477      | Sam1@gm.com   | sam      |
|  9 | sam      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
| 10 | sam      | Sales.txt   | 2012-10-10 | NULL   | google    | NULL     | NULL          | NULL     |
| 11 | sam      | NULL        | NULL       | broken | ebay      | 3534534  | samlee@hm.com | Sam44    |
| 12 | sam      | NULL        | NULL       | NULL   | ebay      | NULL     | NULL          | NULL     |
| 13 | sam      | june.txt    | 2012-02-12 | NULL   | ebay      | NULL     | NULL          | NULL     |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
13 rows in set (0.00 sec)

mysql>

Now, let's run the SQL I proposed

mysql> create table finaltable select * from table1 where 1=2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table finaltable add primary key (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into finaltable (username,storetype,id)
    -> select * from
    -> (select username,storetype,min(id) min_id
    -> from table1 group by username,storetype) A;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> update finaltable A inner join
    -> (
    ->     select username,storetype,
    ->     MIN(IFNULL(filename,'ZZZZZZZZZZZZ')) filename,
    ->     MIN(IFNULL(`date`  ,'ZZZZZZZZZZZZ')) 'date',
    ->     MIN(IFNULL(`desc`  ,'ZZZZZZZZZZZZ')) 'desc',
    ->     MIN(IFNULL(password,'ZZZZZZZZZZZZ')) password,
    ->     MIN(IFNULL(email   ,'ZZZZZZZZZZZZ')) email,
    ->     MIN(IFNULL(ftp     ,'ZZZZZZZZZZZZ')) ftp
    ->     from table1 group by username,storetype
    -> ) B using (username,storetype) set
    -> A.filename = B.filename,
    -> A.`date`   = B.`date`,
    -> A.`desc`   = B.`desc`,
    -> A.password = B.password,
    -> A.email    = B.email,
    -> A.ftp      = B.ftp
    -> ;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from finaltable;
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
| id | username | filename    | date       | desc   | storetype | password | email         | ftp      |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
|  1 | ken      | Product.txt | 2012-01-01 | good   | google    | 1235     | ken@me.com    | Ken.me   |
|  5 | ken      | Amazon.txt  | 2012-01-04 | good   | amazon    | 4654     | Ken1@me.com   | Ken1.net |
|  8 | sam      | Sales.txt   | 2012-10-10 | works  | google    | 477      | Sam1@gm.com   | sam      |
| 11 | sam      | june.txt    | 2012-02-12 | broken | ebay      | 3534534  | samlee@hm.com | Sam44    |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
4 rows in set (0.00 sec)

mysql>

All you have to do is run these SQL commands with each mysqli function in PHP and you are all set.

Give it a Try !!!


Calculate value differences of rows before and after bounded by multiple referenced rows

SELECT *
      ,CASE WHEN location = 'Loc_B' OR right_tree = 0 AND left_tree = 0 THEN NULL::int
            ELSE CASE WHEN @right_diff < @left_diff THEN right_diff ELSE left_diff END
       END AS min_diff
FROM  (
   SELECT *
         ,CASE WHEN right_tree > 0 THEN start_time - right_end ELSE 1000 END AS right_diff
         ,CASE WHEN left_tree  > 0 THEN end_time  - left_start ELSE 1000 END AS left_diff
   FROM  (
      SELECT *
            ,first_value(end_time)   OVER (PARTITION BY right_tree ORDER BY start_time) AS right_end
            ,first_value(start_time) OVER (PARTITION BY left_tree  ORDER BY start_time DESC) AS left_start
      FROM  (
         SELECT *
               ,count(location = 'Loc_B' OR NULL) OVER (PARTITION BY id ORDER BY start_time) AS right_tree
               ,count(location = 'Loc_B' OR NULL) OVER (PARTITION BY id ORDER BY start_time DESC) AS left_tree
         FROM   travel
         ) a
      ) b
   ) c
   ORDER  BY id, start_time;

Produces your result exactly.

1000 is just some "higher value than any other". Since your actual problem seems to operate with times and intervals infinity would be the perfect choice.

Related answer with detailed explanation how groups (right_tree and left_tree here) are formed:
Select longest continuous sequence

SQL Fiddle.


Error 2013 in MySQL Workbench when I perform any operation on a certain table

If you kill a restore mid flow, then it is very likely you will leave a table in an inconsistent state.

The first thing you should try is to rerun the restore and allow it to complete.


Capturing transactions opened / committed during backup

No, a pg_dump captures the point in time when it started.

You seem to want point-in-time-recovery, for which you need pg_basebackup and WAL archives.


Date is not sorted in reporting services consecutively and correctly

My default value was a datetime and converting it to text was the problem when

sorting.So I removed the convert in my expression and make sure in the

manufacturyDate text properties that it is a dd/MM/yyyy


SQLCMD

Next versions do NOT work:

Version 14.0.2027.2 NT (from: COMMAND LINE UTILITIES FOR MICROSOFT SQL SERVER 2017)
Version 15.0.1300.359 NT (from: SQL Server 2019 (15.x))

This version works:

Version 11.0.2100.60 NT x64 (from: Microsoft SQL Server 2012 Command Line Utilities)

(on my computer found in 'C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE')

This was tested by creating a file sqlcmd.txt which contains 1 line (:!! dir), and checking if this command was executed when performing sqlcmd -i sqlcmd.txt.

I think the version 11 that works can be downloaded here, but do NOT ask why this is named 'Command Line Utilities 13 for SQL Server'), and from a logfile i created during install is installen ina folder named 110....


The wal files are archived when they are no longer needed in the pg_xlog directory (usually when there are 16 segments and another one is needed - the one that is rolled over and reused is first archived).  You can force the system to archive some files to verify manually if you want - to do this run the following:

select pg_start_backup('testing_archiving');
select pg_stop_backup();

Once pg_stop_backup() is finished, you should see a few files in the archive directory.

Postgres uses the archive status folder to record "notes" about archiving attempts and so on with regard to the WAL files.


Mysql Performance tuning for myisam tables.

Increase size of tmp_table_size

The maximum size of internal in-memory temporary tables. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables.

Based on tmp_table_size value adjust max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.

This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables.

After setting proper tmp_table_size and max_heap_table_size restart your server and try those reporting queries and observe performance.

Please refer How to optimize Group By queries from here:

http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html


Mongodb replica config slaveDelay but data still sync immediately

If secondary do "initial sync", it will reach primary first, but then it will "sleep" (this case 600 seconds) to fall back and after that, it will keep syncing 600s behind.

You can see that (sleep) in mongod.log if you watch what is happening at end of initial sync.  After that sleep, if you look "last" document in opLog, it's time stamp is that 600s behind. Of course you can see that with rs.printSlaveReplicationInfo() too.


You must grant a user:

CONNECT on the database (if public doesn't have it by default)
USAGE on any schemas like the public schema (again, if the public role doesn't have it by default)
... and the rights you want on a given table.

I suspect you (or RDS) have revoked CONNECT on the database and/or USAGE on the schema(s) you're using, so the role doesn't have the rights to get to the point where it can use the table privileges.


Recover from Postgres FATAL: could not open file pg_tblspc/

The very first thing to do is to make a copy of whatever data files you still have, and to keep it and any backups safe until long after your recovery effort is complete.  Please read this (short) Wiki page:

http://wiki.postgresql.org/wiki/Corruption

Once you have done that, you can attempt various recovery strategies without fear that you will be worse off for the attempt, beyond the time required to try it.  In general I recommend carefully following one of the techniques described in the documentation -- attempts to cut corners or to be creative often lead to corruption.  Only a seasoned expert with a good understanding of PostgreSQL internals should attempt to deviate from the documented steps.

You didn't describe your backup strategy; details of what is available there may suggest alternatives you would not otherwise have.

Ultimately, if you have data of value which is not backed up, you may need to hand-edit the system tables to eliminate references to lost tablespace.  This is not for the faint of heart.  There are a number of companies with which you can contract for such services, many of whom have experience with recovery from catastrophic hardware failure like this.

http://www.postgresql.org/support/professional_support/

I am not affiliated with any of these companies.


Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY

The optimization is implemented by applying flow control on the workers trying to insert to reduce heavy contention and convoys. The idea is based on an unfair insert mutex per scheduler and it can help avoid latch waitlist buildup and convoys as a result. When users opt-in per index with the option, OPTIMIZE_FOR_SEQUENTIAL_KEY, and then we do traffic/flow control on insert workers. Essentially, using an unfair-mutex per scheduler (at a HoBt level where the optimization is on) the flow control will allow only one worker per scheduler to enter the insertion code path that contends for page latches, so that we can reduce convoys and improve the scalability of concurrent inserts. Pam Lahoud provides all this detail in her blog post and I can find nothing in the functional spec or design doc that indicates changes to the current B-Tree algorithms.  Other solutions that were considered (one of which was leveraging an optimized form of reverse-key indexes you referenced in your question) have potential drawbacks, and most would involve fundamental changes to the engine. These other solutions may be considered in the future, but this solution provided a significant improvement without fundamentally altering the engine.

You wouldn't want to deploy this where you are not expecting or experiencing last page insert contention.


How to transfer data from Sybase to SQL Server database?

Method 1: You can use ODBC connections to create an SSIS package which will fetch the data from SYBASE database onto SQL server. On how to use or create such SSIS Package read Extracting data from Sybase SQL Anywhere using SSIS through ODBC.
Then Schedule the above created package via SQL agent job to update data on SQL server as per schedule which meets the requirement.

Method 2: You can create a Linked server which fetches the required data from Sybase to SQL as described here.

And then create a job executing the task that refresh data from SQL Anywhere onto SQL Server using OPENQUERY


Here's one way to make them mutually exclusive:

select count(case X when 'A' then 1 end)
     , count(case X when 'B' then 1 end)
     , ...
from (
  select
    case   --'Before Admission'
    when ((EvalCheckboxDate < UnitArrivalTime)
     and (m_value<>'No' or m_value   is not null)) then 'A'
           --'Within 24 Hours'
    when ((EvalCheckboxDate >= UnitArrivalTime
     and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime))
     and (m_value<>'No' or m_value is not null)) then 'B'
    when ...
    [...]
    end as X
  from TFDataset
) as T

I.e. the idea is to use one case stmt to classify each row and then count the amount of each one. Thye code will be a bit simpler if you can live with something like:

select X, count(1)
from (
  select
    case   --'Before Admission'
    when ((EvalCheckboxDate < UnitArrivalTime)
     and (m_value<>'No' or m_value   is not null)) then 'A'
           --'Within 24 Hours'
    when ((EvalCheckboxDate >= UnitArrivalTime
     and EvalCheckboxDate <= dateadd(hh,24,UnitArrivalTime))
     and (m_value<>'No' or m_value is not null)) then 'B'
    when ...
    [...]
    end as X
  from TFDataset
) as T
group by X

and then transpose the result in the application


What permissions does the service account need to use database mail?

SQL Server Agent service account requires sysadmin permissions on the instance.

Microsoft KB


Multi-Table Groupwise Max Problem where I need a second field along with the max

I'm not a MySQL guy but what about something like the following (for end_hcp)?

Edit: based on syntax comment

SELECT
    p.division AS Division,
    u.display_name AS Name,
    (select max(end_hcp) from lwljhb_lwl_players where player_id = p.player_id and match_id =(select max(p.match_id) from lwljhb_lwl_players where p.player_id = u.id))
 AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses


Oracle schema import is not importing all the tables present in the schema dump file

Use following statement to see what is exactly in export file:

imp sreeni/sreeni@agentrics1:1524/ezmodc file=SreeniTrunkDump.dmp indexfile=SreeniTrunkDump.sql full=y

This statment will not try to import data, just dumps DDL to indexfile.
Check the generated file to see if table is in export.

Alternatively if you are on unix

strings SreeniTrunkDump.dmp|grep <missingtable>

If table is in export file something goes wrong during import. Missing tablespace for example. Check the logfile of import statement (BadriSchemaImport_Import.log) for that.


Is Primary Key Non Clustered recommended?

In a general sense, the Clustered Index should be what is used most often to access and sort the data. A major factor in deciding what to use for a Clustered Index is that the key field(s) will be copied into all Nonclustered Indexes on that table.

The main concern for choosing a PK is finding the field(s) that uniquely identify a row, never being NULL, and preferably never changing. And given that this field will be copied into related table so that the child tables can be Foreign Keyed back to this PK, you generally want something smaller as any large field will have a multiplying affect on disk space. An additional consideration to keep in mind for string fields is the Collation since you are not going to want to JOIN on a field while doing a case-insensitive comparison, as that will be far less efficient than using either a case-sensitive collation or even better would be a binary collation (though the best option is still usually one of the integer types: TINYINT, SMALLINT, INT, or BIGINT).

That all being said, this question is nearly identical to this one:

Primary Key choice on table with unique identifier

And my answer would generally be the same.

BUT, there is one very important difference here: an index can only hold 900 bytes, hence your NVARCHAR(1000) is invalid for being indexed. Unless, of course, the data is never more than 450 characters, but in that case, the field should be NVARCHAR(450) instead of NVARCHAR(1000).

So, create an INT NOT NULL IDENTITY(1, 1) {TableName}ID field and use that as the Clustered PRIMARY KEY.

If you are curious about the total impact of using a large field in either a Clustered Index or Primary Key (given that those values are copied into Nonclustered Indexes and/or child tables), I wrote an article detailing the down-stream effects of this decision:

Disk Is Cheap! ORLY?


Mostly yes, but with small correction. You can get the stale data if they aren't replicated yet to another node (replication happens almost at the same time, but still could be delayed because of garbage collection, etc.).  You can get strong consistency by tuning consistency levels...

The comment is about point 3c - Cassandra's partitions are replicated to all nodes that were selected as replicas for particular partition - the driver or coordinator node can come to any of them, depending on multiple factors.  There is no such thing as an additional sharding key that will route to specific node - all replica nodes are equal.


Join query with IN condition and Group_concat

I'm not 100% sure if I understand the question correct, but i'll try some solution!:

    SELECT a.product_id, group_concat(distinct b.name)
    FROM product_to_category a
    JOIN category_description b
    ON a.category_id = b.category_id
    GROUP BY a.product_id;

I hope this is what you were looking for...

Cheers,
Olaf


Connecting to a MySQL database on Mac from a VMWare fusion Virtual Machine

Finally figured this out. I used home-brew to install MariaDB and as part of the setup it was using launchctl to start the server. The file ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist had --bind=127.0.0.1 in it.


Can't stop "SQL Server Polybase Data Movement"

PolyBase needs TCP enabled to work. By default TCP is disabled in SQL Network Configuration.