Attempting to identify transitive dependency

Can you get a different answer for subtotal for any specific pair of quantity and price_charged?  No, of course not.  Arithmetic is arithmetic.

Quantity and price charged depend on invoice_number and line_number, not on product_code at all.  In fact, product_code also depends on invoice_number and line_number.

EDIT:

Partial Dependencies are dependencies on part of a multi-part key.  Transitive Dependencies are things that depend on non-key attributes.  Step one is to look at your initial relation and determine what the key of the whole thing is, or if there are multiple candidate keys, what they are.

Here are the pieces that I'd guess make up your key, based on the limited sample data and some common sense:

Invoice Number
Line Number

Your table looks to me like an invoice detail table to me.  This means that if you know what invoice and which line on the invoice, you know which distinct record you're looking at.  If that's the case, what do each of the non-key columns depend on?  Let's look at each in turn:

CUTOMER_NUMBER: depends on just the INVOICE_NUMBER, therefore PARTIAL.
PRODUCT_CODE: depends on the whole key, therefore FULL
CUSTOMER_NAME: depends on CUSTOMER_NUMBER (non-key), therefore TRANSITIVE
INVOICE_DATE: depends on just the INVOICE_NUMBER, therefore PARTIAL.
COMMON_NAME: depends on PRODUCT_CODE (non-key), therefore TRANSITIVE.
SCIENTIFIC_NAME and CATEGORY (ditto)
QUANTITY: depends on the whole key, therefore FULL.
PRICE_CHARGED: depends on PRODUCT_CODE (non-key), therefore TRANSITIVE.

As noted previously, SUBTOTAL is calculated based on QUANTITY and PRICE_CHARGED so it depends on the full key as well.


How can I mask a CMD-Variable reference $(myVar)?

Within the script, use SETVAR:

:SETVAR myVar ColumnAlias
SELECT 1 AS [$(myVar)];

Alternatively, use the -v SQLCMD command-line parameter to pass variable values to the script.  See https://msdn.microsoft.com/en-us/library/ms162773.aspx SQLCMD for syntax.

EDIT:

Note that there are two parsers involved here, SQLCMD and T-SQL.  Double quote enclosures are required for SQLCMD variable values when the value
contains a space or a double quote.  Double quotes within the SQLCMD values are  escaped with an additional double quote:

--Double quote enclosure optional
:SETVAR myVar ColumnAlias
:SETVAR myVar "ColumnAlias"
:SETVAR myVar "ThisIsA'ColumnAlias'"

--Double quote enclosure required due to spaces or double quotes
:SETVAR myVar "Column Alias"
:SETVAR myVar "This Is A ColumnAlias"
:SETVAR myVar "This Is A 'ColumnAlias'"
:SETVAR myVar "This Is A ""ColumnAlias"""

Separately, there are additional considerations depending on how the SQLCMD variable value is used within T-SQL scripts.  In your example, the value is used
as an identifier (column alias) and is already enclosed in square brackets within the T-SQL script.  Right brackets within the value need to be escaped with and additional squure bracket for T-SQL parsing:

:SETVAR myVar [ColumnAlias]]
SELECT 1 AS [$(myVar)];
:SETVAR myVar "[ColumnAlias]]"
SELECT 1 AS [$(myVar)];

In the case of a T-SQL string literal, single quotes need to be escaped not for SQLCMD, but for T-SQL parsing:

:SETVAR myVar "This is a ''string literal''"
SELECT '$(myVar)' AS [String Literal];


Firstly, if you really want to use the name table for your table then you will need to enclose it in quotes:

create table table (column1 integer, column2 integer);
ERROR:  syntax error at or near "table"

create table "table" (column1 integer, column2 integer);
CREATE TABLE

but assuming your table name is something different (which I highly recommended), the error returned indicates that the parser is interpreting my_table as a column name:

create table my_table (column1 integer, column2 integer);
CREATE TABLE

insert into my_table (my_table.column1, my_table.column2) values (1, 2);
ERROR:  column "my_table" of relation "my_table" does not exist

This interpretation is sensible, as column1 may be a composite type, for example:

create type my_type as (column1 integer, column2 integer);
create table my_table (col my_type);
insert into my_table (col.column1, col.column2) values (1, 2);
select * from my_table;

  col
-------
 (1,2)
(1 row)

You probably just want the following as already suggested by DrColossos:

insert into table (column1, column2) values (1, 2);


How to restore a specific table from a big mysqldump file?

You could achieve that by two steps :
Step 1 : Locate position of your table(or tables) and it (them) in a file :
 grep -n "Table structure" your_dump_file > list_tables_NUM.TXT

This will create a list of all tables ranked by position, example :
cat list_tables_NUM.TXT
...
41743:-- Table structure for table `table_X`
41770:-- Table structure for table `table_Y`
42780:-- Table structure for table `table_Z`
...

Based-on that result you will create a secondary file containing that specific table, let's assume that you need to restore only table_X  and table_Y:
Step 2 : Extract from position 41743 to 42780 :
sed -n '41743,42780 p' your_dump_file > dump_table_XY.sql

At the final, you may find your tables (structure+data) in the dump_table_XY.sql
Tested for MySQL/Mariadb in Centos7


Mysqldump --flush-logs results in small backup files?

This is related to a bug with new installs of mysql on debian wheezy whereby the error.log file is owned by root.root, as per this:
http://tipstricks.itmatrix.eu/?p=1352

I fixed it with
chown mysql.adm /var/log/mysql/error.log


Postgres PgAdmin III Query Builder crash

We had the same problem 2 times on two different computer, in just a few days of interval.

PgAdmin started to crash each time we open query builder.

Restarting the computer, reinstalling pgAdmin with differents versions... Nothing resolved the problem. But we found the solution to this problem!

PgAdmin uses a file where it saves the last queries (kind of history) and this file was probably corrupted (or one of the queries was).

This file is in the Application Data of your Windows User, that's why reinstalling pgAdmin doesn't work: this corrupted file is common for every postgres install.

So you just got to rename or delete the file pgadmin_histoqueries.xml located in your AppData (for us, D:\Documents and Settings\XXXXX\Application Data\postgresql) and relaunch pgAdmin, this should be OK.


How to enable email notification for failover in SQL Server

You can set up a SQL Agent alert for event 1480 that filters on the string

"RESOLVING" to "PRIMARY"

The reason for the filter is that 1480 events cover all state transitions.  If a secondary loses connection for a bit, it will go into resolving, then back to secondary, so if you don't use a filter you'll get alerts on more than just failover.

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'HA Error Number 1480 - R2P filter',
        @message_id=1480,
        @severity=0,
        @enabled=1,
        @delay_between_responses=60,
        @include_event_description_in=1,
        @notification_message=N'Failover Detected!',
        @event_description_keyword=N'"RESOLVING" to "PRIMARY"',
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO


My question is this, is there a way to dynamically generate Object 3 through MySQL.

When I hear "generate," I tend to interpret that as "insert," but I think you're asking whether MySQL could dynamically select and return the appropriate related records to the application based on certain criteria.

If that's what you're asking, then it seems you have correctly surmised that one sensible way to implement that is indeed with stored procedures...

  Also I've read that I shouldn't use stored procedures, and instead do everything with code

Nonsense, I say.  Disregard that advice.  Stored procedures, used properly, can be used to build an excellent interface between the database tables and the application.

One aspect in particular seems to suit your request:

  MySQL supports a very useful extension that enables the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. -- http://dev.mysql.com/doc/refman/5.5/en/stored-routines-syntax.html

Of course, like any other tool, there are inevitably people who come up with ways of misusing them, but this doesn't reflect on the suitability of the tool when properly applied.

But a single call to the database from your application could retrieve the primary object and related data, with stored procedures.

  I was thinking of creating a separate table top hold primary keys of related objects

That sounds like you're describing a junction table...

  but I'm thinking there is probably a better way of doing that.

If the relationship among objects is many-to-many, then this is the appropriate way of expressing that relationship... but not when it isn't.


Why are CASE-expressions in the list documenting operator precedence?

The documentation is like that because it's an accurate reflection of this part of the source code:
%left   OR_SYM OR2_SYM
%left   XOR
%left   AND_SYM AND_AND_SYM

%left   PREC_BELOW_NOT
%left   NOT_SYM

%left   BETWEEN_SYM CASE_SYM WHEN_SYM THEN_SYM ELSE
%left   '=' EQUAL_SYM GE '>' LE '<' NE IS LIKE SOUNDS_SYM REGEXP IN_SYM
%left   '|'
%left   '&'
%left   SHIFT_LEFT SHIFT_RIGHT
%left   '-' '+' ORACLE_CONCAT_SYM
%left   '*' '/' '%' DIV_SYM MOD_SYM
%left   '^'
%left   MYSQL_CONCAT_SYM
%left   NEG '~' NOT2_SYM BINARY

That's despite how the parser behaves. Despite how, for example, 1 + NOT 0 raises a syntax error for following an operator of high precedence with one of lower precedence, 1 + CASE WHEN 1 THEN 1 END doesn't raise a similar syntax error for the same reason. Any expression involving CASE treats it as an operator of the highest precedence.
I'm left wondering now why the source code is expressed like that despite it not behaving like that. Unfortunately, I lack knowledge of bison to make sense of this discrepancy without investing more time in this curiosity.


Where can I get this nice phpMyadmin features for mongodb

Technically you don't have this feature in MySQL either - PHPMyAdmin is a third part tool just like rockmongo, but basically rockmongo does not have the features you want.  I am sure there are features people would like in PHPMyAdmin too that are not there yet.  When that happens you basically have 3 options:

Request the feature from the people running the project
Write it yourself (if the current tool is open source you could fork and extend etc.)
Use another tool that does have the feature you want

The effectiveness of 1 and 2 will depend on the feature you want, how easy it is to implement, what your skills are, how active the project is etc.

For number 3, there are other options for a GUI interface to MongoDB.  MongoVUE seems to have the view option you want, for example - however it is Windows based:

http://www.mongovue.com/features/

There are others too, MongoHub, PHPMoAdmin etc. - you can find them listed here:

http://www.mongodb.org/display/DOCS/Admin+UIs

There is a comparison of several of the UIs (and more in the comments on this blog post also):

http://timgourley.com/2010/03/16/tuesday-night-tech-mongodb-ui-edition.html

Finally, you should also consider MongoHQ and MongoLab as potential solutions, I know that HQ at least will let you add your own databases, and they both provide excellent admin interfaces for MongoDB.


Does STATISTICS IO output include Version Store reads?

STATISTICS IO does not include Version Store reads, at least for the version store in tempdb.

Here's a demo for proof:

--setup script
USE master
GO

CREATE DATABASE TestDB
GO

ALTER DATABASE TestDB
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE TestDB
GO

DROP TABLE IF EXISTS dbo.Test
GO

CREATE TABLE dbo.Test (ID int identity PRIMARY KEY, junk int)

INSERT dbo.Test
SELECT TOP (100000) 1
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

Start a 30s update loop in one SSMS tab

--UPDATE loop
SET NOCOUNT ON
DECLARE @stop datetime = DATEADD(SECOND, 30, GETDATE())

WHILE GETDATE() < @stop
BEGIN
    BEGIN TRAN

    UPDATE dbo.Test
    SET junk += 1

    COMMIT
END

UPDATE dbo.Test
SET junk = 1

And while the loop is going, run two identical queries in SNAPSHOT with STATISTICS IO ON, separated by 15s to allow versions to accumulate.

USE TestDB
SET STATISTICS IO ON
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT MAX(junk)
FROM dbo.Test

WAITFOR DELAY '00:00:15'

SELECT MAX(junk)
FROM dbo.Test

COMMIT

The IO stats show identical reads:

But the actual execution plan shows the scan for the second query taking far more time, due to reading the version store.

To prove to yourself that this query resulted in tempdb reads, you can use this Extended Events session (which is obviously better than Profiler), filtered to the session where the read queries are running:

CREATE EVENT SESSION [file_reads] ON SERVER
ADD EVENT sqlserver.file_read_completed(
    ACTION(sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(52)))
ADD TARGET package0.event_file(SET filename=N'file_reads')
GO

Viewing the "live data" for that XE session during the demo, you can see reads against database id 2 (tempdb), and it captures the query text of our read query as well:

Special thanks to Paul White for bringing up this issue with STATISTICS IO.


csv import fails without throwing an error

Encapsulate the LOAD in a Stored Procedure.  In there, capture the error and expose it to your script.

However, if LOAD DATA is not allowed in a Stored Proc, then you really need to write application code, not depend on on-line shell scripts.


This is a known problem with this particular DLL during the repair process.  Copying and renaming the file seems to be a suggested work around - see:

http://connect.microsoft.com/SQLServer/feedback/details/576412/-the-process-cannot-access-the-file-c-windows-syswow64-perf-mssql10-50-mssqlserver-sqlagtctr-dll-because-it-is-being-used-by-another-process


OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT
    [ICFilterID],
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT
    i.[ICFilterID],
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT
  [ICFilterID],
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];


There is no query hint to spool or materialize a CTE.  So use a temp table, and optionally index the temp table.
SELECT s.[DTL ID] AS ID, s.[NEW TEU]
into #splits
FROM   [POTS].[vwSPLITCARGO] AS s;

WITH
  splits(ID, [NEW TEU])
  AS
    (select * from #splits)

SELECT *
FROM   [MISNT].[CARGO_MANIFEST_DETAIL_VIEW] AS cmd
       LEFT JOIN splits AS s ON s.ID = cmd.ID


SQL statement to hide all system databases in Server from a server login

From this post hide system databases sql server management studio.

I think it solves my problem.
However, for normal login and user, we can't hide master and tempdb database.

The following information are in this document.
hide system databases sql server management studio

  After this permission is denied, a login can see only metadata for
  master, tempdb, and databases that the login owns.


Correct organization of the database structure and selection

First of all: It is necessary to create one table for each task?

In my opinion: No

You can create only one table "Tasks" where you define a name for your Task and define your standard properties of a task. Whenever you want to create more tasks, then you need to create new tables every time.

As an example: If you want to save humans, you don't need to create tables like "female_childs", "males", "females", "died_females" or whatever - Just create one table "persons" where you can store stuff like "gender", "alive", "birthDay", "deathDay"...

Your Question: Order of execution
In this case, what you need is some sort of sequence to order your tasks. This can be stored in a new table alongside with your device, like that:

DeviceId
TaskId
SequenceNr

I think I've got your confusion about these dynamic task properties but as Basil Bourque describes, you need a "child" property table to store information like you want to do it.

Your third question is now obsolete :)


How to get an exclusive connection in AutoRestore job ( sp_RestoreGene)?

To get exclusive database access (provided it is not used in replicaiton or logshipping or any other HA/DR), OFFLINE it.

ALTER DATABASE DB_name
SET OFFLINE WITH ROLLBACK IMMEDIATE

Make this as a first step and then run the restore script that you generated.

BTW, its  Paul Brewer's restore gene script that you are using.


SQL Server - Is there a limit to the number of Distributed Availability Groups that a primary replica can participate in?

You are misreading that document. In the document and image you are referring to - there are actually two distributed Availability Groups in the image you refer to. each one containing two Availability Groups.

A distributed Availability Group is distributed between two (and as far as I now - only two) Availability Groups.

You can have multiple secondaries in an Availability Group and they can span data centers, so depending on what you are trying to do, you may not need a Distributed Availability Group to do it.


Search NVARCHAR(MAX) string in other string

Considering that:

REPLACE, PATINDEX, CHARINDEX and even LIKE have not been updated to fully and properly deal with LOB/MAX types; and,
you need to repeat this process across hundreds of databases and against multiple instances

I would strongly recommend you look at doing this outside of SQL Server, probably in PowerShell or C# (not CLR inside SQL Server, which will be prone to similar data type limitations and restrictions). You can write a simple C# command line program that pulls in each row, performs the replace inline, and then updates the row wholesale. Then you can point the C# app at each instance/database combination. PowerShell may have some limitations with handling huge chunks of data like this, but I recommend looking into that as well simply due to the automation factor (you can simply feed it a text file of instance/database names and it will churn through all of them in one shot). Even if you could trust the CLR approach to do this correctly, I think the debugging effort will be higher, and you'll also have to deploy the assemblies everywhere (vs. a single app or script that can just be pointed at each instance in turn).

I'm not sure I'm the one you want giving specific guidance on the code you would write using either of these approaches, I just want to steer you away from trying to solve this in T-SQL, since the deck is kind of stacked against you there.


In short you can't.  You would have to restore the entire database to another machine, then copy the needed file from the restored machine and put it into place on the production server.  Single object restores and single row restores (which is what restoring a single file effectively is) have never been supported via the native tools.


Create a temp table, loop, add data, select from it? "Query has no destination for result data"

When I try to get the data back from the table I get an error.

That's because you cannot return anything from a DO statement. You would need a function where you define the return type in the RETURNS clause. (But you still wouldn't need a temporary table.)
Plus, PL/pgSQL does not allow SELECT without a target.
But you don't need any of this for the example. A plain query does it:
SELECT a.accountid::int, p.id::varchar AS product_uid, a.sampleproductId::text
FROM   product.product p
JOIN   product.accountproductmap a ON a.productId = p.id::int
WHERE  p.uid = ANY ('{abc1, abc2}'::text[]);

Some of the casts are probably unnecessary.
You might wrap it into an SQL function (PL/pgSQL not needed for this). See:

SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT?
Difference between language sql and language plpgsql in PostgreSQL functions

Aside: use legal, lower-case identifiers! See:

Are PostgreSQL column names case-sensitive?


InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6

I previously addressed this issue in: Cannot open table mysql/innodb_index_stats

These tables are created for you when you install MySQL 5.6. However, upgrading from MySQL 5.5 does not invoke the creation of these tables. Here are the scripts to create them manually:

innodb_index_stats

USE mysql;
CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

innodb_table_stats

USE mysql;
CREATE TABLE `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

slave_master_info

USE mysql;
CREATE TABLE `slave_master_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
  `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
  `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
  `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
  `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
  `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
  `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
  `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
  `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
  `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
  `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
  `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
  `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
  `Heartbeat` float NOT NULL,
  `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
  `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
  `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
  `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
  `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
  `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
  `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
  PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';

slave_relay_log_info

USE mysql;
CREATE TABLE `slave_relay_log_info` (
  `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
  `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
  `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
  `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
  `Number_of_workers` int(10) unsigned NOT NULL,
  `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';

slave_worker_info

USE mysql;
CREATE TABLE `slave_worker_info` (
  `Id` int(10) unsigned NOT NULL,
  `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Relay_log_pos` bigint(20) unsigned NOT NULL,
  `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
  `Checkpoint_seqno` int(10) unsigned NOT NULL,
  `Checkpoint_group_size` int(10) unsigned NOT NULL,
  `Checkpoint_group_bitmap` blob NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';

ALTERNATIVE

Another workaround would be to create the script from a MySQL 5.6 instance.

Step 01 : Goto a DB Server running MySQL 5.6, or install MySQL 5.6 on a test  machine.

Step 02 : mysqldump these 5 tables to a text file

INNODB_TABLES="innodb_index_stats"
INNODB_TABLES="${INNODB_TABLES} innodb_table_stats"
INNODB_TABLES="${INNODB_TABLES} slave_master_info"
INNODB_TABLES="${INNODB_TABLES} slave_relay_log_info"
INNODB_TABLES="${INNODB_TABLES} slave_worker_info"
mysqldump -uroot mysql ${INNODB_TABLES} > InnoDB_MySQL_Tables.sql

Then, you can run InnoDB_MySQL_Tables.sql on any DB Server running MySQL 5.5 before upgrading.


MySQL Group Replication - Offline Node with cluster Metadata 1.0.1

First of all, you are mixing some concepts. You are using MySQL InnoDB cluster which uses MySQL Group Replication as the replication technology.
I see that you have upgraded MySQL Shell version's and most probably you also upgraded the version of the MySQL servers of your cluster. The error you see when running cluster.rejoinInstance() is not related to Metadata but rather related to the incompatibilities of different MySQL versions in the Cluster. More info here: https://dev.mysql.com/doc/refman/8.0/en/group-replication-online-upgrade-combining-versions.html
How did you upgrade your instances? There's a procedure to follow. I'd recommend you to read the documentation regarding rolling upgrades: https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-upgrade.html
Regarding your scenario, my advice is to:

Use an older Shell version to remove the instance you're trying to remove, using cluster.removeInstance(). The version must be 8.0.18 or lower.

Using the latest MySQL Shell (8.0.21), upgrade the Metadata schema using dba.upgradeMetadata().

You will then have an operational cluster with the latest metadata schema version and you can then proceed to do the rolling upgrade of your instances and add them to the cluster as intended.
Cheers,
Miguel


Split table vs. same values in multiple rows

No. That would mean the table NAME contains DATA - you then have to modify the database structure (create new table) just to add one more category or type.

If your category/type column is tinyint or smallint (depending on reasonably expected number of possible values), then you actually have proper normalization in place. If there is no (functional) dependence between two keys to categories for two different rows then those two values are not a redundancy but a minimal way to store the actual data.

From performance standpoint the important part is proper indexing for your queries. If you often search for rows with specific category and/or type, you should have in place index on that column (or composite index on both columns, and possibly other) to optimize such queries. You should enable the slow query log and periodically check what queries are taking most time (you may use pt-query-digest to analyze the log) and optimizing those (adding proper indexes and/or rewriting the queries).

When you have proper indexes in place then in your proposed case of multiple tables actually selecting the right table to read will take about the same time as MySQL needs to "jump" to the right part of the index. (It is a bit oversimplification but the point is that selecting the right table to read is an overhead in itself).

From the "schema" standpoint I would be more concerned about those two columns criteria1 and criteria2 if these are their real names and usage then that seems like a possible problem - but that may depend on your use case and may be actually OK. But if there is any chance you will once add criteria3 and others then that would tell you to add separate table for those, one row per criteria and FK to the current table.


Cannot continue the execution because the session is in the kill state. while building clustered index

Just a pointer re: SQL Server 2017 (where I was hitting this error) and some versions of 2016 to potentially save others some time:

Before SQL Server 2017 CU8 (also 2016 SP1 CU9 and 2016 SP2 CU1), there was an issue where partitioned tables with incremental stats would generate this error if the DML operation caused an automatic stats update.

Minimal KB page is here:

https://support.microsoft.com/en-us/help/4163478/fix-access-violation-when-incremental-statistics-automatically-updated

Installing a later CU than those listed above fixes the issue.


group several columns together in materialized view

There are two ways to specify the name of a view column:

Do it with a column list:

CREATE VIEW myview (colname1, colname2) AS
SELECT appa.appa_id,
       font.font_id
FROM appa JOIN font ON ...;

Do it with aliases in the defining query:

CREATE VIEW myview AS
SELECT appa.appa_id AS colname1,
       font.font_id AS colname2
FROM appa JOIN font ON ...;


Yes. Just add the CREATE privilege:

GRANT CREATE ON `foobar%`.* TO 'foobaruser'@'%' IDENTIFIED BY 'foobarpass';

And just test it:

foobaruser$ mysql
mysql> create database `foobar_one`;
Query OK, 1 row affected (0.00 sec)

mysql> create database `barfoo_one`;
ERROR 1044 (42000): Access denied for user 'foobaruser'@'localhost' to database 'barfoo_one'

Be aware that you need to escape the _ (underscore), as it acts like one character in the pattern. So «`foobar_`» will match foobar1 or foobarZ.


I would turn on profiling to get an idea of what takes so long. An example using mysql's CLI:

SET profiling = 1;
CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY);
SET profiling = 1;

You should get a response something like this:

mysql> SHOW PROFILES;
| Query_ID | Duration   | Query |
+----------+------------+-------------------------------------------------------------+
|        1 | 0.00913800 | CREATE TABLE blah (id BIGINT UNSIGNED NOT NULL PRIMARY KEY) |
+----------+------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000071 |
| checking permissions | 0.000007 |
| Opening tables       | 0.001698 |
| System lock          | 0.000043 |
| creating table       | 0.007260 |
| After create         | 0.000004 |
| query end            | 0.000004 |
| closing tables       | 0.000015 |
| freeing items        | 0.000031 |
| logging slow query   | 0.000002 |
| cleaning up          | 0.000003 |
+----------------------+----------+
11 rows in set (0.00 sec)


How to perform conditional insert based on row count?

Typically, you have a team table (or similar) with a unique team_id column.
Your FK constraint indicates as much: ... REFERENCES teams(id) - so I'll work with teams(id).
Then, to avoid complications (race conditions or deadlocks) under concurrent write load, it's typically simplest and cheapest to take a write lock on the parent row in team and then, in the same transaction, write the child row(s) in team_joins (INSERT / UPDATE / DELETE).
BEGIN;

SELECT * FROM teams WHERE id = 3 FOR UPDATE;  -- write lock

INSERT INTO team_joins (team_id)
SELECT 3                -- inserting single row
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < 20;

COMMIT;

Example for single row INSERT. To process a whole set at once, you need to do more; see below.
One might suspect a corner case problem in the SELECT. What if there is no row with team_id = 3, yet? Wouldn't the WHERE clause cancel the INSERT?
It wouldn't, because the HAVING clause makes this an aggregation over the whole set which always returns exactly one row (which is eliminated if there are 20 or more for the given team_id already) - exactly the behavior you want. The manual:

If a query contains aggregate function calls, but no GROUP BY clause,
grouping still occurs: the result is a single group row (or perhaps no
rows at all, if the single row is then eliminated by HAVING). The same
is true if it contains a HAVING clause, even without any aggregate
function calls or GROUP BY clause.

Bold emphasis mine.
The case where no parent row is found is no problem either. Your FK constraint enforces referential integrity anyway. If team_id is not in the parent table, the transaction dies with a foreign key violation either way.
All possibly competing write operations on team_joins have to follow the same protocol.
In the UPDATE case, if you change the team_id, you would lock the source and the target team.
Locks are released at the end of the transaction. Detailed explanation in this closely related answer:

How to atomically replace a subset of table data

In Postgres 9.4 or later, the new, weaker FOR NO KEY UPDATE may be preferable. Also does the job, less blocking, potentially cheaper. The manual:

Behaves similarly to FOR UPDATE, except that the lock acquired is
weaker: this lock will not block SELECT FOR KEY SHARE commands that
attempt to acquire a lock on the same rows. This lock mode is also
acquired by any UPDATE that does not acquire a FOR UPDATE lock.

Another incentive to consider upgrading ...
Insert multiple players of the same team
Usefully assuming you have a column player_id integer NOT NULL. Same locking as above, plus ...
Short syntax:
INSERT INTO team_joins (team_id, player_id)
SELECT 3, unnest('{5,7,66}'::int[])
FROM   team_joins
WHERE  team_id = 3
HAVING count(*) < (21 - 3);  -- 3 being the number of rows to insert

The set-returning function in the SELECT list does not comply with standard SQL, but it's perfectly valid in Postgres.
Just don't combine multiple set-returning functions in the SELECT list before Postgres 10, which finally fixed some unexpected behavior there.
Cleaner, more verbose, standard SQL doing the same:
INSERT INTO team_joins (team_id, player_id)
SELECT team_id, player_id
FROM  (
   SELECT 3 AS team_id
   FROM   team_joins
   WHERE  team_id = 3
   HAVING count(*) < (21 - 3)
   ) t
CROSS JOIN (
   VALUES (5), (7), (66)
   ) p(player_id);

That's all or nothing. Like in a Blackjack game: one too many and the whole INSERT is out.
Function
To round it off, all of this could conveniently be encapsulated in a VARIADIC PL/pgSQL function:
CREATE OR REPLACE FUNCTION f_add_players(team_id int, VARIADIC player_ids int[])
  RETURNS bool AS
$func$
BEGIN
   SELECT * FROM teams WHERE id = 3 FOR UPDATE;         -- lock team
-- SELECT * FROM teams WHERE id = 3 FOR NO KEY UPDATE;  -- in pg 9.4+

   INSERT INTO team_joins (team_id, player_id)
   SELECT $1, unnest($2)                                -- use $1, not team_id
   FROM   team_joins t
   WHERE  t.team_id = $1                                -- table-qualify to disambiguate
   HAVING count(*) < 21 - array_length($2, 1);
   -- HAVING count(*) < 21 - cardinality($2);           -- in pg 9.4+

   RETURN FOUND;                                        -- true if INSERT
END
$func$  LANGUAGE plpgsql;

About FOUND.
Call (note the simple syntax with a list of values):
SELECT f_add_players(3, 5, 7, 66);

Or, to pass an actual array - note the VARIADIC key word again:
SELECT f_add_players(3, VARIADIC '{5,7,66}');

Related:

How to use an array as argument to a VARIADIC function in PostgreSQL?
Pass multiple values in single parameter


There are inefficiencies in the Query cache.

query_cache_size        = 192M

Change that to 50M.

That is one of the few cases where tuning can help with "high CPU".  Usually you need to check the indexes and query formulation.  So...

Find a couple slow queries, present to us SHOW CREATE TABLE for the table(s) involved, and EXPLAIN SELECT ....  Then we can continue this discussion.

Oh, good; I see that the slowlog is already on, with a useless value for long_query_time; change it to 1.  After a day, use pt-query-digest (or mysqldumpslow -s t) to find the 'worst' queries.


Not worth it for the reasons you gave, especially considering that you're not going to use it for production purposes. Reporting Services is a great platform for reporting, but I'm sure there are Open Source tools and frameworks to help you with that for PHP / MySQL. Ditto Integration Services, especially in 2012 (much better IDE for developing packages) it's a great tool for ETL, data staging and other import / export work, but probably overkill for you.

I'd definitely stick with the open source for now - it's always going to be cheaper for licencing ;-). Especially as the cost model for SQL Server 2012 shifted to a different per core model. We had to disable cores on recent deployments to bring down licencing costs, as we'd bought hardware before-hand (on assumptions of the same cost model as before). Funnily, enough that's how I started out on SQL Server - with a "we must port this to SQL Server, because Oracle licencing just got too expensive" project.

I look after about 16TB of high-transaction throughput SQL Server production databases for international clients. So definitely not anti-SQL Server and make plenty of use of all those tools you mention.


Insert a sequence of number where some numbers could exist already / fill gaps?

I often use a table containing integers from 1 to (let's say) 1.000.000 because it's always useful with mysql. Assuming such table exists and is defined as:

CREATE TABLE `integers` (
    `n` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`n`)
) ENGINE=InnoDB

Then with this setup:

CREATE TABLE dbat (
id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
num VARCHAR(25) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY num (num)
);

INSERT INTO dbat (id, num) VALUES
(19, 'A1000'),
(20, 'A1001'),
(21, 'A1002'),
(22, 'A1003'),
(23, 'A1010'),
(24, 'A1011'),
(25, 'A1012'),
(26, 'A1013'),
(27, 'A1020'),
(28, 'A1030'),
(29, 'A1031'),
(30, 'A1032'),
(31, 'A1033'),
(33, 'A1034');

SELECT t.num
FROM (SELECT CONCAT('A', LPAD(i.n, 4, '0')) AS num
        FROM mta_info.integers AS i
        WHERE i.n >= 1000 AND i.n <= 9999) AS t
LEFT JOIN dbat ON dbat.num = t.num
WHERE dbat.id IS NULL
ORDER BY t.num ASC
LIMIT 50
;

Yields

num
A1004
A1005
A1006
A1007
A1008
A1009
A1014
A1015
A1016
A1017
A1018
A1019
A1021
A1022
A1023
A1024
A1025
A1026
A1027
A1028
A1029
A1035
A1036
A1037
A1038
A1039
A1040
A1041
A1042
A1043
A1044
A1045
A1046
A1047
A1048
A1049
A1050
A1051
A1052
A1053
A1054
A1055
A1056
A1057
A1058
A1059
A1060
A1061
A1062
A1063

Instantly executed

Having such integers tables is something every DBA should have when dealing with MySQL!


What TCP Port is SQL Server running under? by Colin Stasiuk details several ways to determine the port number SQL is running on

go right to the registry and look at the TCP settings

SQL 2005

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
  MSSQL.<InstanceNumber>\MSSQLServer\SuperSocketNetLib\TCP\

SQL 2008

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
  MSSQL10.<InstanceName>\MSSQLServer\SuperSocketNetLib\TCP\

xp_readerrorlog.  This will show the current SQL Server log and you
can find the row "Server is listening on".
use the SQL Server Configuration Manager
if you are on the machine, look at Task Manager

Then you should be able to use 'IPAddress, Port' to connect.  Or you can create a client side Alias using CliConfg on the client that allows you to connect to an alias.


Is there any way to extract the full text of these messages?

You can achieve it in 2 ways - Go in job step and select the Advanced tab:

a. Output to a file (<== My preferred method)

b. "Log to table" and "Include step output in history" (<== You need to trim msdb..sysjobhistory in long-run as the messages are stored as nvarchar(max) instead of nvarchar(1024))

In order to see the additional logged information you need to use this stored procedure sp_help_jobsteplog or you could query the msdb.dbo.sysjobstepslogs table directly.

More info here


PostgreSQL WAL Archiving: Does having archive_mode set to 'on' without copying logs cause performance issues?

There would not be performance impact there.  Your only limitation will be disk space.

Note this is actually used for things like incremental backups and point in time restore.  It's pretty well tested and used.


The purpose of transaction log in Restore First

Restoring a full + differential backup restores you to the point in time that the differential backup started. If you just took the differential this second and nothing has happened in the database since it started, then yes, that is the latest state. But it's quite unlikely that you will have a disaster the minute you take a differential backup and that nothing has happened in the meantime.

Which means you can't restore to the point in time right before the disaster - everything that happened between the differential and <now> is gone. If you take a differential every hour, this means that disaster could strike at hh:59:59.9999999 and then you'd have lost almost an hour's worth of data.

This is the whole point of the full recovery model - to minimize data loss. If you're not going to bother taking log backups, then switch to simple.

If you take transaction log backups in between differentials (or just don't bother with differentials, since all they do is reduce the number of transaction log backups you need to restore in the event of a disaster), then your maximum potential data loss is your log backup schedule. Because with log backups, you can apply the following during a restore:

restore latest full backup b
restore latest differential backup d taken after b
restore n log backups taken after d up to point in time t

You need to decide what your tolerance is for data loss in order to determine how often you should be running transaction log backups. If you don't know what your tolerance is, you need to discuss this with management / stakeholders.

Could you just take a differential every minute? Or take full backups every five minutes? Sure. But buy some bigger disks.


calculate average of a class where students offer different number of subjects

SUM(..)/count(..) is close.  Instead:

SUM(sums) / SUM(counts)

That is, each subtotal (student?) has a sum of the scores and a count of the number of items (subjects)


Installation of SQL Server 2014 fails on Windows Server 2012 R2

Uninstalling SqlSupport.msi allowed the installation to proceed correctly.


How to obtain all distinct items but limit each distinct to max n and ordered by date?

Apparently below query solves the issue and gives me recent 10 records for each command_name ordered by command name and when it started.

select
    s1.*
from
    sync_logs as s1
left join
    sync_logs as s2
on
    s1.command_name = s2.command_name and
    s1.command_start <= s2.command_start
group by
    s1.uuid
having
    count(*) <= 10
order by
    command_name asc,
    command_start desc;


MySQL syntax for order by not accepted in SQL Server

Your [sub_tree] derived table is using an ORDER BY with no TOP operator.

Try something like the following:

    SELECT TOP (1) node.name
    [...]
    ORDER BY node.lft
) AS sub_tree

Granted that you are just following a MySQL book, I should probably mention the whole "no-no" on doing old style joins on SQL Server. Use INNER JOIN, LEFT JOIN, etc rather than FROM a,b,c WHERE a.id =b.id.


SQL Server Management Studio Error Access Denied Editing Job Step

I though the problem was with SQL Server, but it appears to have been caused by some bug in SSMS. Rebooting my PC solved the problem.


Seconds_Behind_Master alternates between 0 and a number x

Seconds_behind_master doesn't exactly measure the replication lag. It measures the difference between the timestamp of the last binlog event fetched by the IO thread, versus the timestamp of the last binlog event executed by the SQL thread.
This can become misleading for example if the master has more binlogs that the replica hasn't fetched yet. If the network between the master and replica is too slow for the rate of binlogs, the replica might check the timestamp difference and conclude incorrectly that it is caught up. Then the IO thread fetches another batch of binlogs, and suddenly the timestamp difference is great again, and it looks like the replica is not caught up.
In my environment, when we see this happen, it often affects the cross-country replication link when the network is degraded.
It can also happen if the master is writing a lot of bulky content to the binlogs in ROW-based binlog format. A good example is a database that has records with large BLOB/TEXT content, but it makes rapid updates to another column of the same row. By default, ROW-based binlogs write the entire row to the binlog, even columns you did not change. So a simple update to a status column may result in many megabytes being written to the binlog, and causes a delay in transfer over the cross-country link.
The replica downloads binlog events as fast as it can, but it naturally flip-flops between "I'm all caught up" versus "I'm way behind" if bulky binlog events keep arriving.
Solutions: set binlog_row_image=MINIMAL on the master. This causes ROW-based binlogs to store only the columns that have changed.
If you want to get a more accurate measure of replication lag, use a heartbeat tool like pt-heartbeat.


Can I change Fill Factor option for our database server?

As stated in the documentation "Fill factor 0 and 100 are actually interchangeable and fill factor does not apply to heaps, which are tables without a clustered index.

Brent Ozar has pretty good writeup on fillfactor settings which I recommend that you look at but it boils down to this:

Don't spend too much time on the exact fill factor unless you can pinpoint specific performance problems to page splits and then change those from the defaults, for instance indexes on GUID columns which are modified frequently

Use Ola Hallengrens maintenance solution which rebuilds those indexes which are fragmented and skips the others making maintenance run faster and with less load on the server.

If your application uses quite a lot of heaps Ola's solution does not help rebuilding those. You can run alter table <tablename> rebuild to rebuild the heaps ( Alter Table rebuild command applies to SQL Server 2008 and above) but Tibor Karaszi has more intelligent solution which you can run periodically.


You can use a script task to achieve that, you can benefit from the Microsoft.Interop.Excel library to achieve that, you can copy the image from a file using the following code:

Image ExcelImage = Image.FromFile(strFilePath);
System.Windows.Forms.Clipboard.SetDataObject(ExcelImage ,true);

And paste it to excel sheet using the following code:

Excel.Worksheet ThisSheet = (Excel.Worksheet)ThisWorkBook.Sheets[1];
Excel.Range oRange = (Excel.Range)ThisSheet.Cells[10,1];
ThisSheet.Paste(oRange,ExcelImage);

Detailed code provided in the following link:

How to programmatically add a Picture or Icon to an Excel WorkSheet using C#


Stored Procedure Not working as Expected

That's because you're looking for the string 'qry' instead of the value held by the parameter qry.

Replace:

REGEXP ('^.* qry.$'))

With

REGEXP CONCAT('^.* ',qry, '.$'))


It won't be fast, but this may be part of what you need:

SELECT t2.*
    FROM t1
    JOIN t2  ON t2.email LIKE CONCAT('%', t1.name, '%')

That only identifies the rows that should be censored in the email, and only if the alignment of t1.name, etc, is good.  That is, if t1.name is a substring of some other name, you could get some extra hits by this method.

Stick LIMIT 20 on that query and see what the output feels like.

Another approach might be:

FULLTEXT(email, text)  -- in t2

SELECT t2.* FROM t2 WHERE
    MATCH(email, text) AGAINST("all 16000 words from t1");

I have never seen such a big match-against, but perhaps it will work.

You could get a text copy of the list this way:

SET @@group_concat_max_len = 1234567;
SELECT GROUP_CONCAT(name SEPARATOR ' ') FROM t1;

and then copy the string of names into the above query.


Return text from PostgreSQL (9.6) to SQL Server from linked server query

ODBC Problem

I think this is a function of the PostgreSQL ODBC driver

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\PostgreSQL]
"MaxLongVarcharSize"="4094"

Changing that with regedit may work (no tabs or spaces around =). However, there may be other drawbacks.

This is also documented in the psqlODBC Configuration Options..

  Max LongVarChar: The maximum precision of the LongVarChar type. The default is 4094 which actually means 4095 with the null terminator. You can even specify (-4) for this size, which is the odbc SQL_NO_TOTAL value.

I believe setting this to SQL_NO_TOTAL (ie., -4) means that the ODBC driver has to malloc rather static allocate the col size.

GIS sub-optimal

From the PostGIS angle, you should be using Well-Known-Binary via ST_AsBinary and not storing the WKT as nvarchar, but instead storing the geometry from WKB in SQL Server.


Is it ever good practice to use a distinct database account for each user of an application?

If you need really tight control enforced at the data level.  For example extensive auditing.  Auditing is not much good if several users share the same account.   If you have some users that have a need to access the data database directly.

If security is that tight you typically don't even expose the database directly.  You have a service and the client must get data from the service.

In a web application the database (e.g. port 1433) is typically not exposed directly so you have a level of security.  Even if the web application accesses the database directly the users still does not have direct access to the database.

If the login and password is in the client application then it can be hacked.  If a domain you can use integrated security.

At the database you can have pretty fine controls. But row level control is a bit of work. A database is not a good tool for business rules. Business rules and detailed security are typically enforced at the application level.

You can have a mixed mode where there are some stored procedures used by admins and you want to track which admin.  And you may give read only access to users that generate report directly.


You need a simple MAX:

SELECT DATE_FORMAT(MAX(lastlogin), '%d-%b-%Y') AS lastlogin,
   user_id
FROM last_logins
WHERE user_id IN (100,110)
GROUP BY user_id
ORDER BY MAX(last_logins.lastlogin) DESC

Don't sort by the formatted lastlogin as this is not a date anymore.

Don't alias using an existing column name, this might lead to confusion and well-hidden bugs (the same condition in WHERE and HAVING meaning different things, for example).

See SQL Fiddle by jkavalik


Install specific older version of postgresql centos

You probably want to install from the PostgreSQL YUM Repository as it has replaced both of those repositories. The repo rpm for CentOS 5 i386 is here for download.

The difference in the version on that particular package are from changes to the repository URL, and not changes to the versions of the related packages.

The last version of PostgreSQL 8.3 (8.3.23) and related packages are available there for use but that version and its packages are no longer supported.

If you need to build a very specific version of those packages outside of the last versions in that repository, you can always checkout the pgrpms git repository and rebuild from the .spec files there.


Your approach is reasonable.  Put a check constraint on the ASSET table to ensure exactly one of your foreign keys is ever set at any point in time.

The flag is redundant since the same information is captured by which of the foreign keys is NULL.  Keeping it just adds maintenance overhead to the application.  Eventually it will become inconsistent and then you'll have a mess to sort out.

An alternative design is to combine some of the information from SHOP and JOB into a LOCATION table.  The schema then becomes

ASSET(ASSET_ID, LOCATION_ID)
LOCATION(LOCATION_ID, NAME)
JOB(JOB_ID, LOCATION_ID)
SHOP(SHOP_ID, LOCATION_ID)

Put one row in LOCATION for each "job site or .. warehouse or vendor facility," as you say in your question.


Distinct column in an inner join query sql

this will give image only

SELECT
   max(ArchiveCatalogues.IMAGE),
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog
FROM ArchiveCatalogues
INNER JOIN artist
 ON (ArchiveCatalogues.ARTIST    = artist.surname)
AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)
group by
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog


in general, you should use \copy where you can in psql instead of COPY since that wraps COPY FROM STDIN.  Where you want to copy from a file, you can wrap that yourself in your own application (specifics dependent on language but see the Pg docs).

The alternative is to put files somewhere that the Postgres user can access them, like /tmp/ and COPY from there.  Of course this means the files must be present on whatever system the db server is running on, rather than the system the client is running on if using the previous paragraph's methods.  Chances are your problem is either the immediate directory the file is in or a parent directory.  Keeping these in a spool directory which is more general will help manage this problem.


How To identify old and new value of a column in a same row while using GridDB

Answered by mustaccio in a comment:
This can only be done while the row is being updated, in a trigger.


Running a stored procedure across db Link

You will need to add the table to the insert statement...

Insert into schema.table@myLink SELECT * from testSchema;

and re-arrange the procedure call a bit...

schema.procedure@myLink();

You might also consider removing the first commit so that the insert and procedure call are done in the same transaction.


restore compressed dump query in postgresql asks password

Find the postgresql installation folder containing pg_hba.conf. Edit the authentication chapter at the end of this file, adding

local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

and restart. After that postgresql should not ask for the password to authenticate into any account if this is done from the same machine where the postgresql server is running.

Afterwards, change passwords to be sure you know them. Finally, remove the added lines from pg_hba.conf for more security. You can only bypass authentication because you already have root rights on that computer.


I don't think you're doing what you want to do. It looks like you're striping your backups across two paths, which means your backup is evenly split between the two locations.

I suspect that your actual backup size is not 3.5MB, but rather 7MB. The difference in size is most likely due to the local storage being quicker.

Per Ola Hallengren's documentation:

  If you specify multiple directories, then the backup files are striped
  evenly across the directories.

I would strongly suggest modifying this script to only write to one path, and then use an automated method to copy this backup set to another location.


How to open queries into a selected database in SQL Server Management Studio 2005

Some options that you have:

pointed already by gbn (change current connection)
click first in the databases tree and then click 'new query' or 'open file' - they will be open in the scope of the previously selected database
choose a preferred (default) database for the user (either in Security - user properties or in connection preferences)
always use USE in your queries and not rely on Management Studio :-)
add to the previous option the sqlcmd mode of Management Studio (tools - options - query execution - use sqlcmd mode) and this allows you to make use in your scripts of the :connect command which will allow you to run a specific script only in the context of a specific server


There are several options in the PostgreSQL world, most of them are listed in the PostgreSQL wiki:

http://wiki.postgresql.org/wiki/GUI_Database_Design_Tools

The next version of pgAdmin will include a ER designer as well as far as I know.

PowerArchitect works with PostgreSQL, though in the free version it is somewhat limited. In general it does not support DBMS specific features (or only a very limited set).

In the commercial world, nearly all of the "big names" support PostgreSQL as far as I know (including Sybase PowerDesigner and ERwin).

dbWrench is a not too expensive and seems to be quite nice.

TOAD data modeler is a bit more expensive but has an impressive feature list.

You might also want to check the options listed on this page: http://www.databaseanswers.org/modelling_tools.htm


Why does a many to one merge join change the sort order of the data set?

Generally speaking, merge join (including merge join concatenation) only preserves the sort order of the join keys.

The merge join keys are client, campaign, orderId. The required input sort order for the window function is client, orderId, campaign, id , datestamp.

The merge join cannot therefore provide the sort order required by your window function. You could avoid the sort with a nested loops join (e.g. using a hint).

I wrote about the details in Avoiding Sorts with Merge Join Concatenation.


To answer your question literally, yes, in MySQL, views do exist as occupied space on the disk. But of course they do: if the didn't, where would they exist? If you rebooted your server, how would the views persist?

I imagine what you really meant was "do MySQL views occupy physical space in proportion to the number of rows they contain?", in which case the answer is no, they do not.

In MySQL, well:

  Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

It's query that is stored, not the results.

The manual's section on view processing algorithms sheds more light on this: the normal method of handling a view is that the definition of the view is inlined into any query referring to the view (this is called the MERGE algorithm). In some cases, an alternative method is used, where the view's query is executed alone, and the results are stored in a temporary table, against which the referring query is then executed (this is called the TEMPTABLE algorithm).

The MERGE algorithm does not ever involve storing the results of the view on disk. The TEMPTABLE algorithm does, but only temporarily.

Views work the same way in other databases. This is how they work in PostgreSQL, for example.

However, some databases support another approach as well: materialised views. With these, the view's query is executed ahead of time, and the results are stored on disk. Materialised views views do indeed occupy physical space in proportion to the number of rows they contain. This consumes more space, requires work to be done ahead of time, and introduces a lot of complexity (shared by the user and the database to varying extents) around making sure the view is up-to-date, but offers potentially much faster queries, because the work involved in the view's query has already been done. Oracle supports materialised views, and has for a long time. PostgreSQL currently does not, but will do as of the next version (which will be 9.3). In databases without built-in support for materialised views, there are ways to emulate them using triggers.


Recover Postgres Streaming Replication Slave from Archived Wal Logs

You set the restore_command, in recovery.conf, to copy the requested file (%f) from the archive directory to the server's directory (%p).


You're looking at SQL Trace. Extended Events records duration in microseconds (though some data is reported in milliseconds, duration isn't affected). You can see that the event took less than 5 seconds:

<StartTime>2013-08-29T11:14:22.263</StartTime>
<EndTime>2013-08-29T11:14:26.437</EndTime>

And also just to confirm (though it's off a bit due to millisecond rounding in the datetime data type):

SELECT DATEDIFF(MICROSECOND,'2013-08-29T11:14:22.263','2013-08-29T11:14:26.437');

----------
4174000


How do you drop a corrupted INNODB database in MariaDB?

This may not work for everyone, but it did work for us on several occasions:

Stop the database (service mysql stop)
edit mysql.cnf so that under [mysqld] section it has the:

[mysqld]
innodb_force_recovery = 1

Start the database (service mysql start) and in the logfile
you will see it force loading the bad database/table
log into the
database server as root:  mysql -u root
run the command "show databases[ENTER]" to get a list of database
run he command "drop [dbname][ENTER]" where [thedb] is the database you need to drop. It will drop it but will complain that it cannot delete the directory ./[thedb].
Open a separate shell and go into /var/lib/mysql/[thedb]/ and remove the file(s) there.
Stop the database (service mysql stop)
Turn off the REPAIR mode by commenting out the line in
mysql.cnf

[mysqld]
#innodb_force_recovery = 1

restart the database with the command:

service mysql start[ENTER]

Voila.

Now recreate the database and restore from your backup.

Thanks,
David


Using Extended Event For Tracking Updates To A Table

When using SQL Server Enterprise Edition (or running any edition newer than 2016 SP1) I would recommend going with SQLAudit functionality. This will give you some great granualar information about who is touching your tables, and the commands that are being executed.

For something like this you would want to use a database audit specification along with a server audit (used to define where your audit will write to), and then scope it down to the table that you want to monitor. By then scoping changes to the public role you will capture any and all changes that happen.

This script should get you there (test in your dev environment, and replace the relevant pieces for the table(s) that you want to monitor).

USE [master]

GO

CREATE SERVER AUDIT [Audit-TblChanges]
TO FILE
(   FILEPATH = N'C:\SQLAudit'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)

GO

USE [YourDatabase]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable]
FOR SERVER AUDIT [Audit-TblChanges]
ADD (UPDATE ON OBJECT::[YourTable] BY [public]),
ADD (INSERT ON OBJECT::[YourTable] BY [public])

GO

USE [master]
GO
ALTER SERVER AUDIT [Audit-TblChanges] WITH (STATE = ON);

USE [YourDatabase]
GO
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-MyTable] WITH (STATE = ON);
GO

You can quickly read the data back using the GUI, or use sys.fn_get_audit_file to query the data directly from SQL Server.


IF IS NULL then SINGLE else keep the value of the field

Try:

NZ(MaritalStatus,"SINGLE")

  From Nz Function in the documentation:

  You can use the Nz function to return zero, a zero-length string (" "), or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Or you can use:

IIF(IsNull(MaritalStatus),"SINGLE",MaritalStatus)


No. There's nothing unorganized having 50 columns of statistics, if they are normalized.   But these columns may contain redundant information, if details are available concerning the player's career.


If you aren't seeing the activity in your trace, it may not be getting that far. You'll want to turn up some logging for your SSIS package.


Using AS OF TIMESTAMP or DBMS_FLASHBACK.ENABLE_AT_TIME across multiple schemas

Yes you can use dbms_flashback.enable_at_time. This changes your current sessions perception of the whole database (all schema).

But keep in mind that you would have to write changes in a different session, so you cannot use insert from those statements.

There is a current video from Connor as of AskTom fame on this: https://youtu.be/lmrWmH1vMPc

Another option is, if you want to have a consistent but current view you can set your transaction isolation to read only and begin a new transaction to get read consistency across multiple queries.

If you want to mix both read and write then you would need to use ‚as of` for the read subquery. The problem you have with that (changing  many statements) can be easily solved by actually generating those statements automated.


Oracle 11gR2 - Limits on Materialised View replication between databases

This sounds like a job for Change Data Capture (CDC), which allows you to (among other possibilities) ship your archivelogs from the OLTP database to the reporting one, mine them for the changes, then query the changes out, ignoring any you don't want (e.g., changes of type 'D' for DELETE), and using whatever process you might devise apply those changes to your reporting tables.

I have no idea how well CDC would do with a ruleset encompassing 4700 source tables from another database. I've never used it for more than about 50 tables myself.

FYI, there are licensing-related limits on CDC. The full feature set is only available on Enterprise Edition.


How to add FK on a column of composite type in PostgreSQL?

This avoids the syntax error:

create type bbb as (col2 int);

create table aaa (
  col1 serial primary key
);

create table ccc (
  id serial primary key,
  col3 bbb references aaa --will reference the primary key of aaa
);

Even without the syntax error, you still get:

"Schema Creation Failed: ERROR: foreign key constraint "ccc_col3_fkey" cannot be implemented
  Detail: Key columns "col3" and "col1" are of incompatible types: bbb and integer.: "

This works though:

create type bbb as (col2 int);

create table aaa (
  col1 bbb primary key
);

create table ccc (
  id serial primary key,
  col3 bbb references aaa
);

But now you don't have a sequence. Also, you can't create sequence as...

So you are going to have to live without sequences on those tables or just use an int instead of a bbb.


Advantage of exposing data model to customers using our product

One obvious disadvantage is that no matter how good your data model is, it better suit your needs for years to come because you will have just set it in concrete.

I try not to even give app developers access to the the underlying data model. There is a "wall of abstraction" between users and the physical data. That wall is build from a wide variety of views. Just about any format that best fits the user's need can be provided. View triggers do the conversion behind the scenes.

Give your customers whatever logical model best suits their requirement in getting their data to you. So you have to convert it to your current actual model. It sure beats not being able to fix flaws or implement new designs in your database.


How to resolve "InnoDB data dictionary has tablespace id N"

Creating a table in a new database with the same name as the "missing" table, and copying the corresponding file was the solution.

Hopefully someone can post a better solution, but here's the hack I put together to sort this out for myself. Really ugly, but still might benefit someone in the future.

grep "InnoDB: Error: table " /var/log/mysql/error.log
  | grep 141028 # Today's date
  | grep [problematic-database]
  | sort -u
  | awk '{ print $6 }'
  | awk -F/ '{ print $2 }'
  | sed "s/'$//"
  | while read table;
  do
    echo "use temp; create table if not exists $table (random varchar(2))"
    | mysql
    cp -v temp/"$table".frm [problematic-database]/;
    echo "drop table temp.$table; drop table [problematic-database].$table"
    | mysql
  done


Averaging 2 values based on an ID in SSRS

I managed to do this in the end by altering the SQL query on the Dataset

SELECT        txtSchoolID, intNCYear, intReportCycleID, intSubjectID, txtCurrentSubjectReportName, AVG(CAST(intGradeTransposeValue AS decimal(5, 2))) AS avg_attainment, txtReportCycleName, txtGradingName, txtGrade, txtReportTermName, intReportCycleTerm, txtReportCycleShortName, dtReportCyclePrintDate, intGradeTransposeValue
FROM            VwReportsManagementAcademicReports
WHERE        (intReportCycleAcademicYear = 2017)
AND (intNCYear > 6)
AND (intGradeID = 1)
GROUP BY intSubjectID, txtCurrentSubjectReportName, txtSchoolID, intNCYear, intReportCycleID, txtReportCycleName, txtGradingName, txtGrade, txtReportTermName, intReportCycleTerm, txtReportCycleShortName, dtReportCyclePrintDate, intGradeTransposeValue
ORDER BY intReportCycleID, intSubjectID


As explained in the documentation, there are rules on what makes a valid identifier. This rule is what affects your case:

  Identifiers may begin with a digit but unless quoted may not consist solely of digits.

So, as @ypercube says, you need to quote your table name, either with double quotes or backticks:

SELECT * FROM "2521118" ORDER BY id DESC LIMIT 1

or

SELECT * FROM `2521118` ORDER BY id DESC LIMIT 1


This sounds a lot like a problem I have experienced before at a client who was also running IIS and SQL Server 2008 R2 on the same server. The problem would also start with reports of very bad web performance and then slowly grind to a complete halt.

After many hours of trouble shooting we managed to attribute it to a single SQL Server setting found under the "Processors" heading in the Server Properties dialog:

When this setting was enabled we constantly experienced the degradation in performance. Although this setting is related to CPU resources we also observed increased memory usage on the server as you explained. It looked as if when this setting was enabled SQL became so hungry for resources that IIS was unable to allocate the resources needed to serve the incoming requests and this in turn caused a backlog of SQL operations which in turn made SQL want to consume more resources and so on and so forth. I have no empirical evidence to back up these claims but this was the observed behavior at the time.

I realize correlation does not equal causation, but we were able to confidently replicate and subsequently mitigate the issue on multiple servers by switching the setting on and off.

After seeing this repeated at a few clients we adopted the policy to have this setting switched of at all clients and we went so far as to report on any changes made to this setting. Suffice to say we have since then not yet experienced issues of this nature again.

I am not saying this is definitely the solution to your problem but given the similarities between the issues you experienced and the ours I would recommend you try it as a first resort since it is very easy to attempt.


will alwaysOn deal with certificates? do I have to do anything?

Since the certificate and permissions are all included at the database level, there should be no reason to re-create the certificate. Always On will take care of transferring the data to the secondary, it should just be there if it's synchronized or synchronizing but matching log block numbers in the DMVs.

If you wanted the certificate to "be" in the "C:\Certificates" folder and constantly use it (not clear as to why from the questions) then it'll need to be on each replica. I'd also say that this would be bad as the keys are on the same server as the databases, which sort of defeats the purpose.


Update multiple rows with different columns

Something like:

INSERT INTO user (id, name, address, telephone)
VALUES
    (1, 'Tim', NULL, NULL),
    (2, NULL, 'America', '000')
ON DUPLICATE KEY UPDATE
    name = IF(ISNULL(VALUES(name)), name, VALUES(name)),
    address = IF(ISNULL(VALUES(address)), address, VALUES(address)),
    telephone = IF(ISNULL(VALUES(telephone)), telephone, VALUES(telephone))

will work (leaving the NULL fields untouched), presuming the id field is the primary key and will trigger the DUPLICATE KEY UPDATE, although I can't comment on how efficient it would be.


Speaking for PostgreSQL, I found this question to be somewhat interesting.

Omitted FROM Clauses

This isn't at all permitted in the SQL spec. PostgreSQL calls this feature an "Omitted FROM clause", and says this about it

  PostgreSQL allows one to omit the FROM clause. [...] Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT. Note that if a FROM clause is not specified, the query cannot reference any database tables.

Here is what isn't explicit. PostgreSQL is simply introducing an implicit one-row dummy table for you. The example of the syntax is SELECT 2+2 so you can see it's only really made for trivial cases.

# SELECT count(*);
 count
-------
     1

This makes sense so long as you remember that it's operating on an implicit dummy table.

CREATE TABLE dummy_table ();
INSERT INTO dummy_table DEFAULT VALUES;

SELECT count(*) FROM dummy_table;


Which Table Will Get Locked With This Query

The way I interpret Transactions and Concurrency on the TokuDB wiki

  Insert select

  Serializable
  Grab read locks on all key ranges read by query
  Grab write locks on all keys written
  Repeatable read
  Grab read locks on all key ranges read by query
  Grab write locks on all keys written
  Read committed
  Grab write locks on all keys written
  Read uncommitted
  Grab write locks on all keys written

Inserts by the 3rd party application would need to update the keys and they may happen to be locked for certain rows that at the same time are selected-to-be-inserted by that query.

The way I solved it in my case which is make the query run for time slices slightly in the past so it cannot coincide with that application (which actually adds the data).


Deadlock killing update statement when collided with select

Yes, SELECTs need locks, and they can win deadlocks if they're involved in a transaction that's done a lot of work leading up to that point.

To see it, go into a database with RCSI turned on, and create a couple of tables - only one of them will have data in it:

CREATE TABLE dbo.Table1 (ID INT IDENTITY(1,1), Things VARCHAR(50));
GO
CREATE TABLE dbo.Table2 (ID INT IDENTITY(1,1), Things VARCHAR(50));
GO
/* Only Table1 will have data: */
INSERT INTO dbo.Table1 (Things)
  SELECT 'Meaningless Stuff'
  FROM sys.all_columns
GO

Then start a transaction that does a lot of work on the table with data:

/* WINDOW #1: Start a transaction that does a lot of work */
BEGIN TRAN
UPDATE dbo.Table1
  SET Things = 'Different Stuff';
GO

Now start a NEW TAB in SSMS so that you're in a different session - we're going to call this Window #2 - and run:

/* WINDOW #2: Start a transaction that does
just a little work on an empty table: */
BEGIN TRAN
ALTER TABLE dbo.Table2 ADD NewCol VARCHAR(10);
GO

Both transactions in the two different windows are working on two different tables. So far, so good. Now continuing in Window #2, run an update on Window #1's table:

/* WINDOW #2: Everything's cool. Now try an update that gets blocked: */
UPDATE dbo.Table1 SET Things = 'Even more different stuff';

That update just hangs - he's blocked. Don't cancel him - leave him running. He's blocked by the Window #1 transaction. In RCSI, writers still block each other when they're trying to work on the same rows.

Switch back over to Window #1, and run:

/* WINDOW #1: Run a select */
SELECT * FROM dbo.Table2;

And count to 5. Within 5 seconds, SQL Server wakes up the deadlock monitor, who looks around, finds that these two queries are blocking each other, and...the select wins! The select returns data, and the update's window says:

Msg 1205, Level 13, State 56, Line 9
Transaction (Process ID 54) was deadlocked on lock resources
with another process and has been chosen as the deadlock victim.
Rerun the transaction.

Why? Because SQL Server by default chooses the victim by whichever transaction is the easiest to roll back. In our case, Window #1 had done a lot of work already, updating thousands of rows, whereas Window #2 had done just a tiny amount of work.

This is a contrived example in order to teach the lesson quickly - your scenario probably doesn't involve an ALTER TABLE command. Still, the same theory applies.


SQL Server 2017 Availability Group Deployment - Some availability replicas are not synchronizing data

It turns out, the process was working normally, but I was missing a step at the end.  Once I had configured the availability group, I didn't know that I then had to connect to the remote server and add the database.  In the tutorials I had found online, none had included that step.  It was a small but significant item.
Once I added the database on the remote server, the synchronization process started and the failover cluster worked as intended.  Thanks to everyone who offered the suggestions on how to resolve this. I'll mark it as user error on my part.  Sigh....


Query Cost when filtering with varchar vs nvarchar

This is due to a Cardinality Estimate issue. Even though your first execution plan (when filtering on a VARCHAR) estimates returning more rows (when you hover over the clustered index seek operation), it's the correct estimate that matches your actual number of rows returned.
The second execution plan (when filtering on NVARCHAR) under-estimates the number of rows it believes will be returned which results in a less optimal execution plan, but even worse, it'll request less server resources (memory and CPU) than it actually needs to be allocated to process the query. This will cause the query to run slower since effectively the actual number of rows being returned is the same in both cases (same amount of data) but the query optimizer estimates only half the number of rows are being returned than what actually is.
The under-estimation of the number of rows being returned is due to the IMPLICIT_CONVERSION Warning which you can see if you hover over the SELECT operator of the second execution plan. This generally happens when the data types in your JOIN, WHERE, or HAVING clauses are of different data types than the fields they're being compared against, forcing the SQL engine to perform a cast operation. This messes with the statistics the server stores about the cardinality of the fields being compared to in the predicates, and ultimately results in a cardinality estimate issue such as the one you're currently facing.


Measuring timing over many commands in psql

Use Unix time:
time psql -q -f script.sql


Postgres - how to investigate a dependency before DROPing a field

Ok, got an answer from a related question I raised a year ago. Although the sequence is used by 'fixId' it is owned by 'fixId3'

So by running this command.....

ALTER SEQUENCE POSITIONFIX_FIXID_SEQ owned by positionFix.fixId;

I can then run the DROP command without any dependency issues.


The issue you'll run into here comes from the fact that SQL Server doesn't have the "FOR EACH ROW" triggers that Oracle does. You have to write your triggers to handle multi-row changes, i.e. the INSERTED or DELETED virtual tables can have more than one row in them.

If such an update were to happen, your trigger would fail, since (SELECT [marital_status] FROM inserted) would return multiple rows, and subqueries have to return a single value to be used in a direct comparison.

The trigger would probably have to look something like this:

CREATE TRIGGER [dbo].[marriage] ON  [dbo].[applicant_personal_info] FOR INSERT
AS
BEGIN
    SET NOCOUNT ON
    IF EXISTS (SELECT [marital_status] FROM INSERTED WHERE marital_status = 'married')
    BEGIN
        INSERT INTO [dbo].[applicant_marriage_info] ([dom])
        SELECT
            'abc' --Replace with whatever you're really inserting
        FROM INSERTED
        WHERE marital_status = 'married'
    END
END


Retrieve double data type value in non exponential form

It isn't exactly possible to do what you are attempting to do, because a double precision floating point value cannot contain the number 123456789123456789123456789 without a loss of precision.

A "double" is stored in 8 bytes of memory and is limited to around 15 significant digits of precision -- not by MySQL but by the nature of a "double."

When you perform the insert shown above, you're actually storing 1.2345678912345679 x 10^26 ... so MySQL is actually returning the value correctly.

It is possible to coerce the number back to the original format but not to the exact original value because you didn't store the exact value.  Cast it to a sufficiently large DECIMAL value and then cast the DECIMAL to a CHAR:

SELECT CAST(CAST(b AS DECIMAL(30)) AS CHAR) FROM abcd;

The value returned is 123457890123456790000000000... which is correct (despite appearances to the contrary) because that's what's being stored in the field... but the value expressed in that format gives an inaccurate sense of precision... which is presumably the reason it's being returned in exponential notation in the first place.

If you need the precision, you will want to store these values in a DECIMAL column, not a DOUBLE.


Query data via username - pair - best performance

Critique
1A cannot use indexes because of OR
1B -- user_1 = '%' prevents the use of INDEX(user_1, ...), so solution 1B is out.
1B can be salvaged by having a second index:  INDEX(user_2).  Then simply say
SELECT * FROM my_table WHERE user_1 = name_1
UNION ALL
SELECT * FROM my_table WHERE user_2 = name_1;

2-- FULLTEXT has limitations, such as "word" length.  So be cautious.
2A  (WHERE MATCH(users) AGAINST ('"name_1-name_2","name_2-name_1"') -- it is probably sufficient to say simply
WHERE MATCH(users) AGAINST ('+name_1 +name_2' IN BOOLEAN MODE); The +` says that both are required, but in any order.
2B -- OK (with the above caveats)
FULLTEXT, when it is applicable, is likely to be very performant.  Here are two tricks I have used:
WHERE MATCH(text) AGAINST("R +James" IN BOOLEAN MODE)

The short 'word' (R) will be ignored; it will search for James efficiently.  Note the selective use of '+'.
WHERE MATCH(text) AGAINST("R +Anders" IN BOOLEAN MODE)
  AND user LIKE "%R. Anders%"

This assumes that the name might be in the middle of a large column, and I need to check the initial.  The FT test will be first (and fast), followed by the LIKE (slow, but against few rows).
That still has problems because of "E. Anders and R. Anderson" would be wrongly caught.
(Etc, etc.  But I digress from your simple 2-word use case.)
Bottom line:
1B (extra index) is optimal.  (But I suspect you watered down the Question.)
FULLTEXT would be my next choice.
Let me add an option 3:
Sort the users before inserting and when querying.  That is
INSERT ... (user1, user2)
   VALUES
   (LEAST(?, ?), GREATEST(?, ?))

then you need (for case A) only INDEX(user1, user2) together with
WHERE user1 = LEAST(?, ?)
  AND user1 = GREATEST(?, ?)

Alas, that will probably mess up case B.  (Do all rows have 2 user names, but some queries have only 1 user?)
And Option 4
Have another table that maps user names to my_table by id.  Typically 2 rows in this new table for each row in my_table.  Optionally toss the user columns from my_table.
I won't go into the rest of the detail; they do get messy.


Does a whole-server backup guarantee a reliable backup of running databases?

For a filesystem backup to be consistent MySQL requires that the DB be stopped http://dev.mysql.com/doc/mysql-backup-excerpt/5.0/en/backup-methods.html but as you said there are ways of using the DB provided tools to create consistent online backups.

Other DB engines such as Oracle have a mode you can put the DB files into prior to a known time of a filesystem copy to ensure that what's on disk at that time is consistent but you have to know the exact time the backup will start and finish.

there are other DB formats that are also consistent by virtue of the fact that their files are 100% append only such as CouchDB.

But for the most part backups of a running DB require some kind of intervention using the DB engine itself.


I'm not immediately sure how to interpret the 'nativecode=08004' part of the message shown.  In decimal, error -8004 is about 'Illegal FLOAT constant' and is for one of the Informix SQL compilers.  In hex, it corresponds to 32772 decimal, but that isn't a known error number.  If it is an SQLSTATE value, then … hmmm, I'm not sure how to find out the interpretation of it.

I did a rather specialized search and came across:

  -1809 Server rejected the connection.

  Check that you have permission to connect to the server that you
  specified. If you specified both a database server and a database,
  check to see that you have permission to access the database.

  This error message can occur when you are trying to use a Pluggable Authentication Module (PAM) feature of IDS when one or both of the following conditions exists:

  The PAM configuration file is not set correctly. Refer to the OS vendor
  documentation for more information.
  The client or server computer is not configured to handle PAM messages
  that are generated from a connection request.

I suspect, but without much actual evidence at the moment, that the second bullet point might be applicable.

There is some information missing from the question which I regard as crucial:

Which platform is the ODBC running on?  Is the Informix server on the same machine?  If not, what platform is that running on too?
Which version of Informix ODBC are you using?  Which version of Informix server are you using?
You're using the unixODBC driver manager; which version of that (more for completeness than because it is likely to be critical).
You say that you see this error under load — what does that mean to you?  Is it a single process making many connections concurrently?  Or many copies of a single program connecting concurrently?  Or many copies of many programs connecting concurrently?  Or something else?  How many connections does it take before the problem shows up?
What sort of connection are you using?  There are shared memory or socket connections primarily, or perhaps IPC streams.

If you have support for your Informix product, you should probably contact IBM/Informix Technical Support.  If you don't, you might do better via the IIUG mailing lists.  It is free to join and you can choose which lists you receive.  You probably want the 'ids@iiug.org' mailing list.

Failing that, over time, and possibly with increasingly fiddly diagnostics, we might eventually get to the bottom of the trouble.  But to do so, I'll need answers to at least some of the questions above.


Add Gameweek Fixeture based on Datefield

If the gap between matches is always more than 3 days, you could use this:

;WITH CTE AS
(
SELECT  DateField,
        CASE WHEN DATEDIFF(day,LAG(DateField) OVER(ORDER BY DateField),DateField)> 3 THEN 1 ELSE 0 END as newround,
        Hometeam,
        AwayTeam
FROM dbo.Matches

)
SELECT Datefield, HomeTeam,AwayTeam, SUM(newround) OVER(ORDER BY DateField) + 1 as [Round]
FROM CTE;

DDL & DML

CREATE TABLE dbo.Matches(DateField DATE,HomeTeam varchar(100),AwayTeam varchar(100));
INSERT INTO dbo.Matches
VALUES

('2018-08-10',   'Man United',    'Leicester'),
('2018-08-11',   'Bournemouth',   'Cardiff'),
('2018-08-11',   'Fulham',       'Crystal Palace'),
('2018-08-11',   'Huddersfield',   'Chelsea'),
('2018-08-11',   'Newcastle',    'Tottenham'),
('2018-08-11',   'Watford',     'Brighton'),
('2018-08-11',   'Wolves',      'Everton'),
('2018-08-12',   'Arsenal',      'Man City'),
('2018-08-12',   'Liverpool',     'West Ham'),
('2018-08-12',   'Southampton',    'Burnley'),
('2018-08-18',   'Cardiff',         'Newcastle'),
('2018-08-18',   'Chelsea',       'Arsenal'),
('2018-08-18',   'Everton',       'Southampton'),
('2018-08-25',   'Round3Example_1',       'Round3Example_2');

Result

Datefield   HomeTeam              AwayTeam            Round
2018-08-10  Man United            Leicester           1
2018-08-11  Bournemouth           Cardiff             1
2018-08-11  Fulham                Crystal Palace      1
2018-08-11  Huddersfield          Chelsea             1
2018-08-11  Newcastle             Tottenham           1
2018-08-11  Watford               Brighton            1
2018-08-11  Wolves                Everton             1
2018-08-12  Arsenal               Man City            1
2018-08-12  Liverpool             West Ham            1
2018-08-12  Southampton           Burnley             1
2018-08-18  Cardiff               Newcastle           2
2018-08-18  Chelsea               Arsenal             2
2018-08-18  Everton               Southampton         2
2018-08-25  Round3Example_1       Round3Example_2     3

Db<>fiddle


Which event to look for User Mapping changes

I found what I was looking for. I needed to check for the trigger event types ADD_ROLE_MEMBER and DROP_ROLE_MEMBER in my trigger at the ALL SERVER level.

I'll leave this up in case anyone else has a better solution


Syntax error with concat in trigger near signal statement

Test this code:

drop table if EXISTS table_social;
create table table_social (
        sid varchar(250) primary key,
        auth char(32) not null,
        social integer,               -- to be checked by triggers
        female boolean,
        given varchar(250) not null,
        family varchar(250) null,
        photo varchar(1000) null,     -- to be checked by triggers
        place varchar(250) null,
        ip varchar(250) not null,
        uid integer not null references
            table_users(uid) on delete cascade
);

delimiter //

create trigger social_insert_trigger before insert on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                set @message_text = concat('WRONG SOCIAL: ', new.social);
                signal sqlstate '45000'
                set MESSAGE_TEXT = @message_text;
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                set @message_text = concat('WRONG PHOTO: ', new.photo);
                signal sqlstate '45000' set message_text = @message_text;
        end if;
end
//

create trigger social_update_trigger before update on table_social
for each row begin
        if new.social < 0 OR new.social > 6 then
                set @message_text = concat('WRONG SOCIAL: ', new.social);
                signal sqlstate '45000'
                set message_text = @message_text;
        end if;
        if not (new.photo is null OR new.photo regexp '^https?://') then
                set @message_text = concat('WRONG PHOTO: ', new.photo);
                signal sqlstate '45000'
                set message_text = @message_text;
        end if;
end
//

it not understand CONCAT() in SIGNAL SQLSTATE SET message_text


You seem to use the word "database" when you mean "table."  One database for all data is best.  Most modern databases (PostgreSQL, MySQL, Oracle, DB2, SQL Server, etc.) can handle a large amount of data in one database.  It sounds like you might want to access all customer and/or all invoice information together, so keep them together in one database.

Data should be split into separate tables to normalize it as needed.  Fully normalized data (each piece of information stored only once) is recommended by every database textbook I have read.  Read about (or at least Google) "database normalization" to understand that concept well.

One normalized approach would be to have one customer table indexed by a customer id with all fields common to every customer.  The customer table would also have a field identifying the customer type: customer1, 2, ... 10.  A separate table would hold customer-1-type fields, indexed by customer id.  Another table would have customer-2-type fields, etc.  The application using the database would be designed to look for data in the customer-1 table if the customer-type is customer1, look for data in customer-2 table if customer-type is customer2, etc.

Presumably, the relationship between customer and invoice is one to many.  A invoice table could have a foreign key linking to the customer table.  If invoice data varied by customer-type, then that data could be placed in separate tables: invoice-customer-type-1, invoice-customer-2, etc.  When gathering invoice data, find the customer id for the invoice from the invoice table, look up the customer-type from the customer table, then get data from the appropriate invoice-customer-# table.

Also, the tables in the database should be structured how the data will be used.  For data entry and updating, a normalized structure is good.  For printing bills or other reporting, you might want to create temporary de-normalized tables to speed the process.  Creation of a temporary table can be faster because you avoid making multiple table joins every time a bill is printed.

Get a textbook and study database design, but in the meantime, I hope this helps.


SSIS creates too many connections to a database

Set precedence constraints between your data flow tasks so that they execute sequentially. https://www.simple-talk.com/sql/ssis/working-with-precedence-constraints-in-sql-server-integration-services/


How to create a trigger that will insert values into another table twice?

So the following trigger works perfectly to prevent double bookings for the case stated in the question:

CREATE OR REPLACE TRIGGER Check_Double_Booking
BEFORE INSERT OR UPDATE OF Aircraft_ID, Departure_Date, Arrival_Date ON

Booking
FOR EACH ROW
BEGIN
INSERT INTO Flight VALUES (:new.Aircraft_ID, :new.Departure_Date);
INSERT INTO Flight VALUES (:new.Aircraft_ID, :new.Arrival_Date);
END;


Mysql reliable with 1000 new entries / minute?

Prrffzzt. No, this isn't exactly high write load.

You should test yourself with your own data, of course. It depends on the kind of write query, the size of the table, the type of keys you're using. Explain  is your friend.

We have used MySQL as a backend for a major social networking website (high write load) without reliability problems. You can build impressive setups (as in: racks of servers just running the database) with MySQL.

You should consider MariaDB since many of the original MySQL developers moved there. You should also consider hiring a consultant if you are really worried about optimization and if your current solution doesn't scale.


Issue with a where clause by using Oracle

It may probably no longer be a problem, but in the code it seems you forgot that the assignment operator is :=, not =, so typeEngine would be null and the query returns no rows.


After Insert Trigger never fires using Import Wizard

The SQL Server Import and Export Wizard does not expose a way to enable triggers directly.

You could save the result as an SSIS package, then edit that to enable the FIRE_TRIGGERS option in the component it uses.

Alternatively, and probably more simply, you could choose to use one of the other data import methods that more easily allow you to specify that triggers should be fired.

Related official documentation:

Bulk Import and Export of Data (SQL Server)
Controlling Trigger Execution When Bulk Importing Data


DBCC INPUTBUFFER shows Event Type as Language Event for an unused Database

Your session has a connection context to the database. In order to drop a database, you need to remove all connections (including your own query window & Object Explorer connections):

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;

(See also this question.)

This is much cleaner and immediate than playing KILL-a-spid (whack-a-mole) for individual sessions and hoping you get your alter in before another session establishes connectivity.

We can't tell you why this session is getting created, something that is calling dtb.compat... which isn't anything built into SQL Server, so it must be a user or some application. You can get more detail when you catch this query in the act, like host name, app name, and other details, by looking at sys.dm_exec_connections, sys.dm_exec_connections, sys.dm_exec_requests, etc.

Your query against sysprocesses is problematic for two reasons. (1) dbid there is unreliable. A session can definitely hold locks against a database with a different dbid indicated there (think cross-database joins). (2) sysprocesses is deprecated:

As for the Language Event question, this just means that the last command that session sent to SQL Server was query text. The other option (other than No Event) is an RPC Event, which in simplest terms means a procedure call. See the DBCC INPUTBUFFER topic in the documentation for more details.