importing large SQL file phpmyadmin I am trying to import this SQL file in phpmyadmin and got this message

#1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near
'
 Fatal error: Allowed memory size of 134217728 bytes exhausted (tr' at line 1

What do I do to do this import?

Assuming you can't further increase the memory size then there are a couple of options.

If you aren't familiar with the command line and you really want to stick with a GUI style import then you can use BigDump (http://www.ozerov.de/bigdump/).  I used it once but it's been a while.  From what I remember you will download a file named bigdump.php (with some instructions) and put it on your webserver in the directory with the MySQL DB dump file that is too large to import via PHPMYADMIN.  Then navigate to it using your browser - something like http://your-website.com/bigdump.php.
If you are familiar with the command line and using a Linux based system then you can use code like like this:

mysql -u USERNAME -p DATABASENAME < FILENAME.sql

The database and user (with privileges to the database) will need to exist prior to running this command though.  Note I copied the above command from another source.  I always do my dumps and restores like this:
DUMP: mysqldump -u DB_USER -h DB_HOST -pDB_PASSWORD DB_NAME | gzip -9 > DB_NAME.sql.gz
RESTORE: gunzip < PATH_TO_DUMP/DB_NAME.sql.gz | mysql -u DB_USER -pDB_PASSWORD DB_NAME

Finally, and most painfully, you could choose to dump individual tables or groups of tables into small enough dumps.  Then restore these individual dumps one at a time via PHPMYADMIN.

FTP & RESTORE instructions:

Download FileZilla here: http://filezilla-project.org/download.php?type=client

Typically you can FTP into your server (especially a shared host) using the same login and password you would for SSH (you command line login).  Just make sure to set up the connection to your server properly in FileZilla.

Once connected your default path will likely be your $HOME folder (on nix machines).  If you aren't planning on doing a lot of FTPing just dump what you want in there.  You can always move it from the command line.

If you FTP'd a MySQL Dump into your $HOME folder then the RESTORE command for a DB named mydb and a db dump file named mydb.sql.gz (in the $HOME folder) would be:

gunzip < $HOME/mydb.sql.gz | mysql -u DB_USER -pDB_PASSWORD mydb

where you would obviously need change the DB_USER and DB_PASSWORD values to what user and password you setup for the DB.

Remember:

The DB must already exist (but be empty) before the RESTORE command is executed
The DB_USER and DB_PASSWORD must be setup for the DB before the RESTORE command (you can do this from PHPMYADMIN - Privileges tab)
When you set up DB_USER from PHPMYADMIN it will default to only allowing DB_USER to access the DB from localhost (the server the DB is hosted on).  I'm assuming this is fine for your situation grant.  When you get into more advanced DUMPs and RESTOREs you may want to do it from another machine entirely which isn't hard but requires a couple more pieces of information be provided and DB_USER with the proper privileges.

FTP the DB and RESTORE it... Step by Step

Locate zipped MySQL dump file on your desktop (Example: C:\Web\DB\Dumps\mydb.sql.zip)
Download and install FileZilla
In FileZilla go to File->Site Manager and click on the New Site button (lower left quadrant of GUI). Set the host to a domain (Example: dest.com) on that resides on your DESTINATION server. Set Protocol to FTP or SFTP (this depends on your hosting, ask them).  Set the User and Password (Example: User = grant, Password = grant's password) - if the FTP protocol is SFTP then any set of valid SSH login credentials should work.
(In FileZilla) Click Connect (bottom left most button)
(In FileZilla) Once connected to the remote server (should be your DESTINATION server) use the left side of FileZilla to navigate to the directory where the dump file you located in Step 1 resides.  (The path for the local machine is on the far left, about half way down and it is labeled Local site:.  In our example the textbox next to Local site: would read C:\Web\DB\Dumps.)
(In FileZilla) Locate the Remote site: label directly to the right of Local Site:.
(In FileZilla) There is no technical reason you can't put the DB DUMP in the folder tha FileZilla opens up to.  It will likely default to your $HOME directory.  If you don't have any reason to put the DB Dump in a specific directory - such as for your own organizational purposes - then leave the path on the Remote site alone.  Write down that path (Linux Example: /usr/grant).
(In FileZilla) Locate the file on your local machine on the left half of FileZilla just under the Tree shown beneath the Local site: label you found in Step 5.  Double click on that file and FileZilla will FTP it to your Remote Site.
Within PHPMYADMIN on your DESTINATION server, create a new database (type mydb into the Create new database textbox and Click the Create button).
(In PHPMYADMIN) Choose mydb from the menu on the left.
(In PHPMYADMIN) Click on the Privileges tab (upper right).  Once on that page click on Add New User.
(In PHPMYADMIN->Privileges->Add New User) Provide a username (Example: mydb_grant) and password (Example: mydb_password).  In the Host dropdown choose Local.  Make sure that the radio button next to "Grant all privileges on database" is selected.  Then click Go (bottom right of screen).
SSH into your DESTINATION server.
(In your SSH terminal) Navigate to the directory your wrote down in Step 7.  Using a command like - cd $HOME or cd /usr/grant.  (On Linux machines $HOME is an environment variable that will equal something like /usr/grant.)
(In your SSH terminal) Execute the following command: gunzip < /usr/grant/mydb.sql.gz | mysql -u mydb_grant -pmydb_password mydb (there is no space between -p and the password because that is the way it needs to be, that is not a typo).

Once that last command completes your DB has been RESTORED.  I don't recommend using the DB user and password I provided simply replace them with more secure choices of your own.

DUMP and RESTORE on different servers without FTP

If you wish to skip the FTP stuff and don't mind redoing the DUMP then you can do it this way:

Login into the DESTINATION server (where you want the DB to reside - we'll call it destination.com)
Create the DB, User, and proper privileges as mentioned above on the DESTINATION server
Make sure the privileges on the SOURCE server allows for SOURCE_DB_USER to access the DB remotely - either allow access from dest.com or from % (% - everyone and everywhere if they have the right username and password)
On the DESTINATION server run: mysqldump -u SOURCE_DB_USER -h source.com -pSOURCE_DB_PASSWORD mydb| gzip -9 > $HOME/mydb.sql.gz where source.com is the SOURCE server (where the DB currently resides)
Then (on the DESTINATION server) run: gunzip < $HOME/mydb.sql.gz | mysql -u DEST_DB_USER -pDEST_DB_PASSWORD mydb

That should be all you need to do to move the DB from one server to another.  Caveats are that I've only ever done this on my own dedicated Ubuntu (with standard LAMP) and Dreamhost's VPS servers (which I believe run CentOS but I could be wrong).


updating the entire column with datetime values based on other values in the row I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic:

Only add a value to 'ToDateTime' on rows where AID equals 123 and PID equals 2. (rows 2, 4, 5, and 7 below) - otherwise leave 'ToDateTime' as NULL.
If the above condition is met, set/leave 'ToDateTime' as NULL if the data (varchar) cannot be converted to datetime (rows 5 and 7 below have data that will fail datetime conversion)
But if it does contain a value that can be converted to datetime (rows 2 and 4), convert it and update the value in ToDateTime with that converted value.

BEFORE:

ID | AID | PID | ContainedData        | ToDateTime
_________________________________________________
1  | 123 |  1  | xxxx                 | NULL
2  | 123 |  2  | 7/21/2015 8:15:06 AM | NULL
3  | 234 |  2  | xyxy                 | NULL
4  | 123 |  2  | 19/07/2015 12:29:42  | NULL
5  | 123 |  2  | NULL                 | NULL
6  | 345 |  3  | zzzz                 | NULL
7  | 123 |  2  | badTimeString        | NULL

AFTER:

ID | AID | PID | ContainedData        | ToDateTime
_________________________________________________
1  | 123 |  1  | xxxx                 | NULL
2  | 123 |  2  | 7/21/2015 8:15:06 AM | 2015-07-21 08:15:06.000
3  | 234 |  2  | xyxy                 | NULL
4  | 123 |  2  | 19/07/2015 12:29:42  | 2015-07-19 12:29:42.000
5  | 123 |  2  | NULL                 | NULL
6  | 345 |  3  | zzzz                 | NULL
7  | 123 |  2  | badTimeString        | NULL

I have been trying a query similar to what is below but it is filling ToDateTime with the same values for every row (in the case of my actual data, NULL.) I'm not sure if something similar to this query is the right approach, or if I'm way off.

UPDATE MyTable SET ToDateTime =
CASE
WHEN ISDATE(myT.cd)= 1
THEN CONVERT(DATETIME, myT.cd)
ELSE NULL END
FROM (
 SELECT ContainedData cd
 FROM MyTable
 WHERE [AID] = 123 AND [PID] = 2
 ) myT

How about this:

UPDATE [dbo].[MyTable]
SET [ToDateTime] = [ContainedData]
WHERE aid = 123
and pid = 2
and isdate([ContainedData] ) = 1


Consolidate multiple rows into single row In PostgreSQL 9.5, I have a table named reports:

CREATE TABLE public.reports (
  id BIGSERIAL PRIMARY KEY,
  id_station character(11) NOT NULL,
  date date NOT NULL,
  element character(4) NOT NULL,
  value smallint NOT NULL
);

For each station (id_station column) and each day (date column), I may have multiple value types (element column) : TMIN, TMAX, TAVG (sometimes these values don't exist: I may have only a TMIN and a TMAX for a given day).

Here is a (fake) sample:

22;"FR069029001";"1925-01-01";"TMAX";130
23;"FR069029001";"1925-01-01";"TMIN";-25
24;"FR069029001";"1925-01-01";"TAVG";0

I would like to consolidate these values in one single row for each station and each day using this table:

CREATE TABLE public.reports_con (
id SERIAL PRIMARY KEY,
id_station character(11) NOT NULL,
date date NOT NULL,
tmin smallint,
tmax smallint,
tavg smallint
);

I would like to achieve this result:

454;"FR069029001";"1925-01-01";-25;130;0

How to consolidate the data this way in PostgreSQL? With CREATE TABLE AS?

I know it has to be a recursive query like this (human language) :

For each day:
    For each station:
        Find values for TMIN, TMAX, TAVG
Insert the results in reports_con in a single row with day and station

I begin learning SQL and would like to achieve this within PostgreSQL (not with Python or a programming language). Can you please help me?

Since the target table obviously exists, CREATE TABLE AS is unrelated to the solution. And you certainly don't need a recursive query, either.
For just three columns you might use conditional aggregates:
INSERT INTO reports_con (id_station, date, tmin, tmax, tavg)
SELECT id_station, date
     , min(value) FILTER (WHERE element = 'TMIN') AS tmin
     , min(value) FILTER (WHERE element = 'TMAX') AS tmax
     , min(value) FILTER (WHERE element = 'TAVG') AS tavg
FROM   reports
GROUP  BY id_station, date
ORDER  BY id_station, date;

The aggregate FILTER clause requires Postgres 9.4. Details and alternatives for older versions:

Return counts for multiple ranges in a single SELECT statement

For more columns of for lots of rows (and you need better performance), consider an actual crosstab() query. The special difficulty is that your key consists of two columns, but you need one for crosstab(). Generate a surrogate key with row_number() like in these related answers:

Pull data from multiple tables in a view or function
PostgreSQL Crosstab Query
Crosstab function in Postgres returning a one row output when I expect multiple rows


net start sql service with startup option From this article, I know to that slash (/) is used instead of a hyphen (-) when a net start is used with startup option,

net start mssqlserver /m

But if I want to have to use with startup option limit to an application, for example sqlcmd (-m"SQLCMD"), how do I do that with NET START?

For the article you linked it references the specific start up options used by the Database Engine ("sqlservr.exe"). So the "-" dash is used as stated in the article. This article points to the method used in a DOS prompt to start SQL Server in single user mode. I believe using /m"SQLCMD" would work the same way, but I normally use the commands noted below.

So from a DOS prompt, the command to start SQL Server default instance in single user mode and only allow SQLCMD to connect would be:
sqlservr.exe -m"SQLCMD"

If you have a named instance it would be:
sqlservr.exe -m"SQLCMD" -s MyServer\MyInstance


Control SQL Server pace of db shrink to split into multiple files (Manually moving this from StackOverflow, as it really belongs here.)

We have a production SQL Server Enterprise database that has a few TB of data in it. We have a need to split the data into multiple files. Tests in pre-Production environments have gone well with the exception that during the shrink the performance of the database is degraded. We would like to get ahead of potential issues with shrink/split of files in the production system, since we have large customers with SLA's that limit our ability to make changes that significantly affect performance.

Does the SQL Server shrink/split functionality have the ability to limit how much overhead the shrink/split incurs during the transition to multiple files? Or, do we have paths that enable the same limiting ability. Alternately, can an approach be taken that gradually splits/shrinks the files during given maintenance windows?

You don't need to shrink to migrate data to multiple files.
Simply add the new files tot the filegroup, then rebuild each table one-by-one to move the data around.
The proportional fill algorithm will take care of equally balancing the data between the files, assuming you size the files in an optimal way.  See this Microsoft Docs page for details.
Use the ALTER TABLE ... REBUILD; command to migrate the data across all available files in a filegroup.  You can even use that command to migrate a table to another filegroup.  Microsoft Docs for that command are here


Is it possible to restore a database from corrupted .mdf file? I know that the answer of the question may vary depending on what exactly is broken in each .mdf file - only looking for advice if there is another way to approach this.

So, I have received some bad news from the database administrator. Something like this happened last night (that's the only info I have from him):

a drive failed
the drive was replace but during the rebuild it failed again .. and again
it was new drive, so it was moved to a different drive slot on the chassis
doing the above, I mistakenly pulled the cachcade drive which in turn locked up any volume with cachecade protection
after re-inserting the cachecade drive, drive activity resumed (drives went to a “locked” state)

It seems the above steps lead to .mdf files corruption and not working properly. So, we started to force attached them to new SQL instance, following the steps below:

Create new database
delete its files
add a corrupted .mdf and ldf files and restore them with the script below:

USE master GO
ALTER DATABASE [] MODIFY FILE(NAME='', FILENAME= '')
ALTER DATABASE [] MODIFY FILE(NAME='', FILENAME= '')
GO

ALTER DATABASE [] SET ONLINE
GO
DBCC TRACEON(3604)
GO
ALTER DATABASE [] SET EMERGENCY
GO

ALTER DATABASE [] SET SINGLE_USER
GO
DBCC CHECKDB('[]', REPAIR_ALLOW_DATA_LOSS)
WITH ALL_ERRORMSGS
GO

ALTER DATABASE [] SET MULTI_USER
GO

He also told me that Repair errors are different per database and some database cannot be repaired at all.

So, have I any other options here or I need to check how the above goes for each database and move some of data (if possible) to database restored from older copy by hand?

If it were me?

  Nuke it from orbit and restore from backup.

Stop any processes that rely on this database and restore from your most recent backups.

After you're stable, you can go through the academic exercise of trying to repair as much of the corrupt media as you've got the energy & budget to put up with. Assuming you have a strong understanding of the upstream & downstream dependencies of this media, you may be able to logically repair restore some of the data you lost in the time between the end of your last stable backup and the outage.


Memory distribution across numa nodes within Sql server I need to understand following behavior of memory distribution across numa nodes within sql server

Current set up:

2 node cluster SQL server 2014 SP2 with virtualization disabled

We have 4 NUMA nodes with 4 sockets with each 20 cores making total of 80 logical processors available using ent core based licensing. Total memory on box is 1 TB with max memory to 656 GB to sql

When checking the memory usage as i blve each numa node will have its own based on max memory/ no of numa nodes:

Therefore all 4 numa nodes have total node memories as 164 GB each.

However when i check the available free across each node they vary by quite a that is numa nodes 0 has 256 MB available, node 1 has 127, node 3 has 111 and 4 has 28.

Similarly when i check the PLE it also responds similarly across all numa nodes- ie on avg node 1 is having ple of 687, 2 with ple 699, 3 with ple 333  and 4 with 122. I know these PLE is just avg and max min changes throughout the day.

But can this uneven distribution cause a performance issue or is there a change which can be implemented?  Thanks

For a system with 1TB of RAM, and over 600 GB dedicated for SQL Server, page-life-expectancy of 300 to 600 seconds seems very low.  That indicates, on average, every page in the buffer pool is being replaced every 5 to 10 minutes.  That means the server is experiencing significant I/O just to service queries - as in 100GB per minute (or more) of data being transferred from disk into memory, all the time.  I'd recommend looking at getting PLE higher, by:

Look for queries that perform an excessive amount of I/O.  Check if those queries might be optimized to avoid excessive reads by either adding appropriate indexes or breaking large queries up into smaller chunks with intermediate #temp tables.
Add physical memory.  On this note, why is max server memory set to 656GB when the machine has 1TB of memory? You're leaving 250GB on the table, that SQL Server would probably dearly love to use.  Make sure this server is dedicated to only run SQL Server, and up the max_server_memory to at least 900GB, perhaps more.  I'd set min server memory to something like 768GB to ensure Windows can "claw-back" some memory from SQL Server if it really needs to.

In extreme cases, you may want to configure thread affinity per the choices listed in this MSDN blog article.  Trace Flag 2479 can be used to enable "Connection Bound" thread placement, which place threads on one node.  SQL Server treats threads as if the connection node is the only node on the system, forcing threads for any given query to be processed by the NUMA node where the client connection started.  Per that page, parallelism is allowed as long as free threads on the node are >= (degree-of-parallelism * branches-in-query).


Postgresql 9.3 replication: Preventing wal-files from building up, if receiver goes offline I have a streaming replication running between two servers.

I just upgraded to postgresql 9.3 from 9.2. Earlier on in my 9.2 setup, I have tried that my slave server went offline due to a network outage. This resulted in unshipped wal-log files building up and taking up space VERY fast on my master server.

Can I prevent this in an easy way in 9.3?

I have seen the new wal_sender_timeout config, but I am unsure what exactly it does?

What's the best way of preventing wal-files from building up on sender if receiver goes offline in 9.3?

Set wal_keep_segments to a low enough value that WAL will be discarded before pg_xlog fills up.

If you're also using WAL archiving with an archive_command, Pg will keep on trying until it successfully sends the WAL or runs out of space. Don't have your master copy the WAL directly to the replica; instead put it on some reliable high availability storage that the replica also has access to.


How to find cores and logical processors in SQL Server I can see 4 processors when i right click on SQL server instance -> properties->General->Processors,

does it mean is the server had 2 core processor or 4 core processor? what is CORE? where do i check how many cores sql server has?

Very much appreciate your replies

See the answer to this question.

It quotes https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms143760(v=sql.105)

  SQL Server supports the specified number of processor sockets
  multiplied by the number of logical CPUs in each socket. For example,
  the following is considered a single processor for purposes of this
  table: A single-core, hyper-threaded processor with 2 logical CPUs per
  socket. A dual-core processor with 2 logical CPUs. A quad-core
  processor with 4 logical CPUs. SQL Server is licensed per processor
  socket, and not per logical CPU basis.


MySQL my.cnf performance tuning recommendations Most of the day, the performance of the server is good, but once per day it stops responding totally even I can't access via SSH, and I have to restart it to make it up again.
I have a dedicated server with the following configuration:
Server Information

16 Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz
64GB Ram
Linux Centos7 / cPanel/WHM + MySQL

MySQLTuner results and recommendations

Reduce your overall MySQL memory footprint for system stability
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses

How can I implement these improvements?

[!!] Maximum possible memory usage: 56.9G (90.76% of installed RAM)

Why the maximum possible memory was increased in that way? Is there a value that needs to be adjusted?
my.cnf file:
[mysqld]
performance_schema = ON
local-infile=0
skip-name-resolve

join_buffer_size=2M
read_buffer_size=2M
tmp_table_size=1G
max_heap_table_size=1G
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

innodb_buffer_pool_size=3800M
innodb_log_file_size=512M
innodb_buffer_pool_instances=4
innodb_flush_log_at_trx_commit = 2
innodb_flush_method= O_DIRECT
max_allowed_packet=100M
open_files_limit=40000
default-storage-engine=InnoDB
#innodb_file_per_table=1
#innodb-thread-concurrency=8
#innodb_lock_wait_timeout = 100
max_connections=200
query_cache_type=0
query_cache_size=0
query_cache_limit=1G
# thread_concurrency= 32
thread_cache_size= 8
key_buffer_size=256M
#Slow Query Log
slow_query_log = ON
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2
table_open_cache=3000
[mysqldump]
max_allowed_packet=4G

How could I improve or fix this configuration?
Any suggestions? Thanks folks.

Your innodb_buffer_pool_size is almost absurdly small for a server of that size.
innodb_flush_log_at_trx_commit=2  is dangerous.
innodb_file_per_table=1 you definitely don't want commended out. Rebuild any tables that aren't in separate tablespaces.
query_cache_limit=1G makes no sense with query cache disabled.
Various buffer sizes other than innodb_buffer_pool_size - you should probably leave them at default settings. Comment them out. Especially join_buffer_size and sort_buffer_size.
Do you use MyISAM? If not, reduce key_buffer_size down to something tiny (enough to cover the size of all .MYI files in your database.


SQL Server 2017 Availability Groups - Can a Long-running transactions on a primary replica block a secondary replica from seeing updates? The following two links are each very explicit regarding how long running transactions on the primary impact an asynchronous secondary replica.

https://technet.microsoft.com/en-us/library/dn135335(v=sql.110).aspx
https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/06/troubleshooting-redo-queue-build-up-data-latency-issues-on-alwayson-readable-secondary-replicas-using-the-wait_info-extended-event/

  Asynchronous Commit

  A long-running transaction on the primary replica prevents the updates
  from being read on the secondary replica.

  All read workloads on the secondary replica are snapshot isolation
  queries. In snapshot isolation, read-only clients see the availability
  database on the secondary replica at the beginning point of the oldest
  active transaction in the redone log. If a transaction has not
  committed for hours, the open transaction blocks all read-only queries
  from seeing any new updates.

My testing suggests otherwise.

With SQL Server 2017 Enterprise edition, I have created an AlwaysOn Availability Group with two Availability Replicas.
Both are configured with:

Availability Mode = Asynchronous Commit
Failover Mode = Manual
Readable Secondary = Yes

I have single table named "Test" with two columns [Id (IDENTITY+PK), Description]

On the primary, I insert 'beforelongtransaction'
On the primary, I start a transaction that inserts 'longtransaction'. I do not commit the transaction.
On the primary, I confirm with DBCC OPENTRAN that a long running transaction is executing.
On the primary, I insert 'whilelongtransaction'
On the secondary, I execute "SELECT * from Test"

I receive the results:
| Id | Description |
| 1 | 'beforelongtransaction' |
| 3 | 'whilelongtransaction' |

On the primary, I commit the long transaction from Step 2.
On the secondary, I execute "SELECT * from Test"

I receive the results:
| Id | Description |
| 1 | 'beforelongtransaction' |
| 2 | 'longtransaction' |
| 3 | 'whilelongtransaction' |

Has SQL Server 2017 (or 2014/2016) removed this limitation?
Or am I misunderstanding and not properly reproducing the limitation?

Can a Long-running transactions on a primary replica block a secondary replica from seeing updates?

Only if it blocks the REDO thread(s) which would require the use of DDL on the primary and a workload that is blocking said DDL from acquiring the needed Sch-M lock on the same object(s).

Long running transactions don't "stop" you from seeing updates but a plethora of other items will, such as:

Slow Redo
Slow network
Slow disk
CPU/Memory pressure
Synchronous/Asynchronous
Readable secondary replicas use SNAPSHOT isolation under the covers

  Has SQL Server 2017 (or 2014/2016) removed this limitation?

AFAIK there was never a limitation except that your log is going to grow which could cause other issues. It could also present issues with readable secondary replicas if this impacts or blocks ghost cleanup - that's generally what I've witnessed and in those cases the transactions were open on an extremely busy system for 24-48 hours. Since Snapshot isolation is used, your tempdb is going to take a hit due to version store and there is a limit. I'm not exactly sure what the quoted text you have is referring to as it doesn't explain the scenario(s).


SQL Agent reporting failure when all steps suceed The SQL Agent is reporting failure when all the steps under it has succeeded. Anyone else seen this behavior ?

You probably have a job step that has the On success action: set to Quit the job reporting failure as can be seen in the following screen shot:

This can be verified by querying the sys.sysjobhistory and sys.sysjobsteps tables and checking the on_success_action column of the sys.sysjobsteps table. If the column contains a 2 and the step finishes, then it will quit the job and report a failure (Quit the job reporting failure).

Query Job History and Steps

Here is a script for your convenience to query the job steps and history:

SET DATEFORMAT DMY
GO
SELECT sj.name AS Job_Name,
       sjh.step_name AS Step_Name,
       sjh.step_id AS Step_ID,
       sjh.run_status AS [Job_Status],
       STUFF(STUFF(CAST(sjh.run_date as nvarchar(10)),5,0,'.'),8,0,'.') as HistRunDate,
       STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sjh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') as HistRunTime,
       STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sjh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') as HistRunDuration
       --,sjh.run_status AS JobStatus
       ,sjs.last_run_outcome as [Job Step Outcome]
       ,sjs.on_success_action as [On Success: 1=success;2=failure;3=next step]
       ,sjs.on_fail_action as [On Fail: 1=success;2=failure;3=next step]
FROM   dbo.sysjobs           AS sj
       left join dbo.sysjobhistory AS sjh --- was sysjobschedule sjc
            on sjh.job_id = sj.job_id
       left join dbo.sysjobsteps as sjs
            on sjh.job_id = sjs.job_id
            and sjh.step_id = sjs.step_id
WHERE  1=1

ORDER BY
       sjh.run_date desc,
       sjh.run_time desc

Sample Output

...of syspolicy_purge_history job which can be found on most SQL Servers:

Job_Name                | Step_Name                            | Step_ID | Job_Status | HistRunDate | HistRunTime | HistRunDuration | Job Step Outcome | On Success:                     | On Fail:
                        |                                      |         |            |             |             |                 |                  | 1=success;2=failure;3=next step | 1=success;2=failure;3=next step
------------------------+--------------------------------------+---------+------------+-------------+-------------+-----------------+------------------+---------------------------------+--------------------------------
syspolicy_purge_history | Verify that automation is enabled.   | 1       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:00     | 1                | 3                               | 1
syspolicy_purge_history | Purge history.                       | 2       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:00     | 1                | 3                               | 2
syspolicy_purge_history | Erase Phantom System Health Records. | 3       | 1          | 2017.11.28  | 02:00:00    | 00:00:00:04     | 1                | 1                               | 2

You might have noticed that this job will report a success when the first step fails (On Fail = 1 ).

References

dbo.sysjobs (Transact-SQL)
dbo.sysjobhistory (Transact-SQL)
dbo.sysjobsteps (Transact-SQL)


What is the logic of a query when using like with and/or I have a table that contains subtitles ranging from subtitles_a to subtitles_z. I'm not sure why this query isn't working?

select * from subtitles
where field_name like '%subtitle_u%' or field_name like '%subtitle_v%'

This returns back everything in the range from subtitles_a to subtitles_z.

When I use the AND, I get values from subtitle_u to subtitle_v, shouldn't using an OR have given me these results instead?

select * from subtitles
where field_name like '%subtitle_u%' and field_name like '%subtitle_v%'

Underscore means "a single wildcard character". I'd suggest this is affecting your results. Try doing;

select * from subtitles
where field_name like '%subtitle\_u%' escape '\'
or field_name like '%subtitle\_v%' escape '\'


Can this SQL Query be improved Related to one of my previous questions: How to select a subset from a query result

I now have this Query:

CREATE TABLE #JourneyLogs
    (
        JourneyStartId INT,
        JourneyEndId INT,
        AssetId INT,
        [Event] INT,
        JourneyStart DATETIME,
        JourneyEnd DATETIME,
        GeofenceId INT,
        JourneyDistanceKM FLOAT,
        JourneyTime INT
    )

    INSERT INTO #JourneyLogs
        SELECT
              JourneyStartId
            , JourneyEndId
            , AssetId
            , [Event]
            , JourneyStart
            , JourneyEnd
            , GeofenceId
            , JourneyDistance = ROUND((SUM(DistanceCoveredK) / 1000), 3, 1)
            , JourneyTime
        FROM (
            SELECT
                 JourneyStartId = ignOn.iVehicleMonitoringId
                ,JourneyEndId = ignOff.iVehicleMonitoringId
                ,AssetId = ignOn.iAssetId
                ,JourneyStart = DATEADD(hour, @fGmtOffSet, ignOn.dtUTCDateTime)
                ,JourneyEnd = DATEADD(hour, @fGmtOffSet, ignOff.dtUTCDateTime)
                ,[Event] = ignOff.eEventCode
                ,DistanceCoveredK = p.sptGeoLocaitonPoint.STDistance(
                                        LEAD(p.sptGeoLocaitonPoint) OVER(PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY ignOff.dtUTCDateTime))
                ,GeofenceId = ignOn.iGeofenceId
                ,JourneyTime = DATEDIFF(SECOND, ignOn.dtUTCDateTime, ignOff.dtUTCDateTime)
            FROM VehicleMonitoringLog ignOn
            CROSS APPLY (
                SELECT top(1) iVehicleMonitoringId, eEventCode, dtUTCDateTime, sptGeoLocaitonPoint
                FROM VehicleMonitoringLog WHERE
                iAssetId = ignOn.iAssetId AND dtUTCDateTime > ignOn.dtUTCDateTime AND eEventCode = 2
                ORDER by dtUTCDateTime
            ) ignOff
            INNER JOIN VehicleMonitoringLog p ON p.iAssetId = ignOn.iAssetId AND p.dtUTCDateTime >= ignOn.dtUTCDateTime AND p.dtUTCDateTime <= ignOff.dtUTCDateTime
            WHERE
                ignOn.dtUTCDateTime > @utcStartDate AND ignOn.dtUTCDateTime < @utcEndDate
                AND ignOn.iAssetId in (Select iAssetId FROM #tblAssets)
                AND ignOn.eEventCode = 1
        ) g

        GROUP BY AssetId, [Event], GeofenceId, JourneyStart, JourneyEnd, JourneyTime, JourneyStartId, JourneyEndId

This takes 19 seconds and returns 4830 rows, with STATISTICS IO:

Table '#JourneyLogs________________________________________________________________________________________________________000000000148'. Scan count 0, logical reads 4864, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 146540, logical reads 857406, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VehicleMonitoringLog'. Scan count 9719, logical reads 64578, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#tblAssets__________________________________________________________________________________________________________000000000147'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warning: Null value is eliminated by an aggregate or other SET operation.

(4830 row(s) affected)

Execution Plan: Here

Is there a way to improve this query?

Off the top of my head, these are the improvements I would try:

Add a clustered (unique if possible) index to the temp table, #tblAssets (iAssetId). This will eliminate the Sort operator, and may potentially improve the type of join operator.
On the index IX_VehicleMonitoringLog_iAssetId_eEventCode_NonClustered, include the column iVehicleMonitoringId. This will eliminate the KeyLookup operator, which is potentially very expensive.

Not so much an optimization, but rather for correctness: Shouldn't this...

PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY ignOff.dtUTCDateTime

... rather be...

PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY p.dtUTCDateTime?

I'm thinking that for all the records of any given partition, the ignOff.dtUTCDateTime should be the same, and you probably want to loop through each "segment", i.e. the p.dtUTCDateTime, right?

You may already get the correct results by accident with the current query because the records may be correctly ordered from the Index Seek on p, but I would probably want to make sure by specifying the correct ordering. As a bonus, this might improve performance.


SQL Server TempDB files 1-> n and write stalls growing I have been kinda accidentally pushed to starting to manage our DB settings, performance analyzing etc. Also as it's commonly said that TempDB file should be spread into n files based on core numbers and performance stuff.

I ran some analyzis on one of our servers and spotted that tempDB Write stalls were averaging around 600ms with single file. With this find and with some more analyzis I decided to split TempDB into 4 same sized files (as recomended). At start it looked really first. For first day new tempDB stalls were around 1.5ms (SSD). After 24h though all the new TempDB files were above original write stall. This same thing is repeating on different enviroments also. Am I looking on wrong numbers in here? Am I missing some setting that I should check? Or is single TempDB file still working better.

Here are some environments that this thing has happened:
SERVER1
SQL2008R2, SSD drive for SQL, 2 main databases with around 10gb each.

Before: 1 autogenerated TempDB file with stats:

num_of_Writes: 18909792
Avg_Write_stall_ms: 645.554ms
Avg_Read_stall_ms: 2.207ms

After: Original one (changed to 2gb), + 3*2gb tempDB files

Avg_Write_stall_ms around: 2.15ms
Avg_Read_stall_ms around: 1.3ms

After 24hours:

Avg_Write_stall_ms around: 650-700ms
Avg_Read_stall_ms around: 2.2ms

SERVER2 (virtual):

SQL2012, heavy use and lot of database
Before: 1 autogenerated TempDB file with stats:
Avg_Write_stall_ms: 1576.154ms
Avg_Read_stall_ms: 12.788ms

After: Original one (changed to 2gb), + 3*2gb tempDB files

Avg_Write_stall_ms around: 19-39ms
Avg_Read_stall_ms around: 6.8-7.6ms

After 24hours:

Avg_Write_stall_ms around: 8700-9500ms
Avg_Read_stall_ms around: 18.5 - 19.1 ms

This same situation is on multiple of our servers with different setups (virtuals, newer and older, SSD and disk drives). What are we missing?

The information from sys.dm_io_virtual_file_stats is aggreagated since the database came online.

To get usable IO numbers from sys.dm_io_virtual_file_stats, you really need to capture a baseline, wait for a period of time (say 30 minutes) and take another snapshot of sys.dm_io_virtual_file_stats. The difference between the snapshots is the relative latency during that time.

This script take a baseline snapshot of sys.dm_io_virtual_file_stats, waits 30 minutes (by default) and takes another snapshot of sys.dm_io_virtual_file_stats.  The difference between the snapshots is the relative latency during that time.


Are there any caveats in my SQL migration plan? I would like to move several databases from one Microsoft SQL Server instance (2014) to another instance (2016) while still in production. Are there any obvious caveats or holes in my plan?

1) Take a full back up of each database that I want to move

2) Restore databases to the new instance

3) Sometime in the middle of the night switch each source database to single user mode.

4) Take a last final transaction log backup

5) Restore transaction log backup to the new instance databases

6) Turn off old instance and point relevant resources at the new instance

Any advice, caveats, etc.

Additional considerations for:

Backup Databases

WITH COPY_ONLY in order to retain the backup sequence of any third-party tool or backup maintenance plan currently running on your old SQL Server.

Restore Databases

You might have to RESTORE DATABASE ... WITH NO_RECOVERY in order to be able to apply further transaction log backups to your new database.
Or after restoring the database backup run the RESTORE LOG ... WITH NO_RECOVERY on the new SQL Server so that further transaction log backups can be applied.

Single User Mode

(placeholder)

Final Transaction Log Backup

(placeholder)

Restore Transaction Log Backup

(placeholder)

Turn Off Old Instance

(placeholder)

And possible the most important point:

Test Procedure

Test the procedure in your Development Environment


Can't see Create Database in Analysis Services SQL Server Management Studio When I right click on Databases in Analysis services there is no Create Database menu Item.

Another person with this problem 'reinstalled with all options' but when I run SSDT setup there is only 'do you want it or not' type options - for me anyway.

I've checked that my user login is an administrator. I've also started Management studio in administrator mode. No luck.

For what it's worth, this might have to do with the mode, in which SQL Server Analysis Services is installed. If it is installed in the Tabular mode (the default), then the New Database... menu item is not available. If Multidimensional and Data Mining mode is selected during the installation, then the New Database... menu item becomes available.
A much more experienced co-worker helped me to figure this out when I was struggling with the same problem.


Add replica from node 1 can't find node 2 while installing an Always ON AG lab on virtual machines I am trying to set up an Always ON availability group lab on virtual machines, with Windows Server 2016 and MS SQL Server 2017.

While creating a new Availability Group on node 1 by the wizard, I meet difficult that the system can not find the node 2 in the step of validation.

The errors show as:

  'The following required directories do not exist on replica WINN02\SQL_AG_02 : C:\Program Files\Microsoft SQL
  Server\MSSQL14.SQL_AG_01\MSSQL\DATA.
  (Microsoft.SqlServer.Management.HadrModel)'

and

  'The primary server 'WINN01\SQL_AG_01' cannot write to '\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'.
  (Microsoft.SqlServer.Management.HadrModel)

Additional information

  Backup failed for Server 'WINN01\SQL_AG_01'.
  (Microsoft.SqlServer.SmoExtended)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Cannot open backup device
  '\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'.
  Operating system error 5(Access is denied.). BACKUP DATABASE is
  terminating abnormally. (Microsoft SQL Server, Error: 3201) For help,
  click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3076&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476'.

Attached the error details:

The problem seems about connection, so I made some connection test.

When I try to connect instance node 2 via computer name, in node1, it shows connection error. Attached the screenprint:

.

If I change it the computer name of node 2 into IP address, it can be connected easily.
If I ping the address and computer name in cmd of node 1, both of them are successful. Which means the connecting condition is good. Attached the screenprint:

I am stuck. Please help me, thank you!

There are a couple of different questions here.

Question #1

  'The following required directories do not exist on replica WINN02\SQL_AG_02 : C:\Program Files\Microsoft SQL
  Server\MSSQL14.SQL_AG_01\MSSQL\DATA.

While I would not put any database files on the C:\ drive, the error is saying that the

C:\Program Files\Microsoft SQL Server\MSSQL14.SQL_AG_01\MSSQL\DATA

folder does not exist on the WINN02 VM, resulting in the database files not being found.

Resolving he issue

You should move these database files first to a non c:\ disk and then check that both folder structures are the same with

SELECT DB_NAME(database_id), physical_name FROM SYS.MASTER_FILES

If your C:\ drive is full, your VM's are done for. Resulting in a crash and possible corruption.

Question #2

  Cannot open backup device
  '\Winn01\nzfagsharefolder\BackupLocDb_cbbf8fb2-f6e8-4aa9-8995-675364932027.bak'.
  Operating system error 5(Access is denied.). BACKUP DATABASE is
  terminating abnormally. (Microsoft SQL Server, Error: 3201)

This error happens due to the ag creation wizard trying to take full & log backups of the databases to later restore on the secondary instance.

Resolving he issue

You should look into granting the service accounts for both instances the FULL control permission on the \Winn01\nzfagsharefolder\location. And make sure that your user account has enough rights (sysadmin).

Question #3

  The problem seems about connection. So I made some connection test. 1.
  When I try to connect instance node2 via computer name, in node1, it
  shows connection error, attached the screenprint

The connection issues from node1 to node2 are a different problem.

There are a few things to check, but if you can connect via IP you could look into DNS name resolving.

Resolving he issue

Using the FQDN to connect, looking into the tcp settings settings in the configuration manager such as the port used (E.G. changing dynamic ports to static ports).

Another solution could be enabling the browser service.


SQL code issues with having I'm helping a friend figure out some SQL code, but can't figure out why it isn't working like it should.

I have come up with many solutions that work, but I want to know why this one isn't working.

Any help would be appreciated.

SELECT test_id,
    question_id
FROM test
WHERE test_id = 6117
GROUP BY test_id
HAVING question_id = MAX(question_id);

He wants to get the max question id for a specific test.  I don't really understand why he wanted to do it this way - something along the lines of performance and functions and subquery.  So I'm not sure on that part, though it sounds like it will be used with other SQL code.

If you remove the where clause it works for all test ids, but when you try to specify the test id it isn't working.

Selected column in a query with GROUP BY can either be one of the "grouping" columns, functionally dependent on those or an aggregate. I covered the functional dependency just yesterday in this answer. question_id does not fulfill any of these so putting it in there leads to "undefined behavior".

To show what really happens I prepared sql fiddle where you can see that the first query does not return anything and second query shows the details of what actually happens - by removing the HAVING and adding some columns to the SELECT I show what values are assigned to which columns and expressions:

+---------+-------------+
| test_id | question_id |
+---------+-------------+
|       6 |          54 |
|       6 |         300 |
+---------+-------------+

SELECT test_id,
    question_id,
    max(question_id),
    group_concat(question_id)
FROM test
WHERE test_id = 6
GROUP BY test_id;

+---------+-------------+------------------+---------------------------+
| test_id | question_id | max(question_id) | group_concat(question_id) |
+---------+-------------+------------------+---------------------------+
|       6 |          54 |              300 | 54,300                    |
+---------+-------------+------------------+---------------------------+

For test_id = 6 we have two rows with question_id of 54 and 300, because of the GROUP BY we can only select one value per column for a given test_id

max() is trivial, it selects 300
group_concat() is aggregating function which gets all possible values for given column in a group and makes a comma separated list of them
question_id is the interesting case - it can show only one value but two exist for the given test_id - so one is picked "randomly" - as we can see, mysql picks the first one it gets - group_concat shows the internal "ordering", but it depends on many things, one of which is index used - thats why removing the WHERE changes the results such way, different index is used and so different number is picked for this column

The same values are very probably used when you write the same columns and expressions to the HAVING clause and as you can see question_id != max(question_id) because 54 != 300.

To solve this task properly some "groupwise-max" solution is needed. It is even covered in the MySQL manual which shows 3 possible solutions - correlated subquery, derived table and left join. Which one is the best depends on specific case - the use of subquery can be faster than the left join for some data and slower for others. From my experience if you have few groups with lot of items then subquery is generally better whereas the left join performs better with many smaller groups, but you can test them all and see for yourself.

Edit: As other answers show, if you select only test_id and question_id, then the max(question_id) and GROUP BY test_id are enough. I added the previous paragraph because you mentioned in some version of the question that your friend has some "problem" with subqueries etc, so I supposed the actual problem is not as simple as this.


Mixed editions in Availability Group? Looking to consolidate several different older SQL serves into one AG with an ent lic when Sql Server 2016 is released. Plan is to have a primary, a sync passive secondary, and an async third instance used for reporting. That third instance I want to configure using a Std lic w/CALs to save on costs. Any issue with mixing Ent and Std in the same Availability group?

There are 2 High Availability types:

Advanced HA on Enterprise edition
Standard or Basic HA on Standard edition (or BI edition)

Capabilities, prerequisites and restrictions

You can look for SQL Server 2014 Capabilities at the top of page 5: SQL Server 2014 Licensing Guide.

If you look at the 2nd row from the bottom of the Compare edition table (SQL Server 2016) on SQL Server Editions page, only the Enterprise edition includes this:

  Advanced high availability (AlwaysOn, multiple, active secondaries; multi-site, geo-clustering)

If you look at Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) you will find a similar restriction among others:

  Each server instance must be running the Enterprise Edition of SQL Server 2016.

Since you are consolidating several instances and servers, you should also look at other restrictions such as collation, etc.

SQL Server Standard

Now using Standard edition with only a Basic High Availability group is posible with some of the limitations found on Overview of AlwaysOn Basic Availability Groups (SQL Server):

Limit of two replicas (primary and secondary).
No read access on secondary replica.
No backups on secondary replica.
Support for one availability database.

Some of these restrictions are ~similar~ to the deprecated mirroring functionnalities.

Consolidation with Standard licence

Because you are consolidating servers and instances, you obviously have several databases.

You could create X Availability Groups for your X databases with a single database in each AG. However you will have to create them using the WITH BASIC option. You will then fall under the other restrictions of Basic AG and you won't be able to use more than 2 replicas, hence no instance 3.

Therefore a Standard licence does not seem to be an option in your case.

HA aside, if Instance 1 is using a single Enterprise option, then an Enterprise licence is needed on all 3 instances.

You should review SQL Server 2014 Licensing Guide and SQL Server 2014
Licensing Datasheet and then check and discuss it with you software vendor.

Please note that since SQL Server 2016 is not yet available, some of the links and information are based on SQL Server 2014 licencing model.

Instance 1 Licence:

This is your main instance with several databases. It requieres an Enterprise licence.

Instance 2 Licence:

If this instance is really passive, then it may already be covered by the licence on instance 1 as a free passive server:

  Beginning with SQL Server 2014, each active server licensed with SA coverage allows the installation of a single passive server used for fail-over support.
   The passive secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive. If it is serving data, such as reports to clients running active SQL Server workloads,
  or performing any “work” such as additional backups from secondary servers, then it must be licensed for SQL Server.

If this instance is used and accessed (reporting, backups, etc.) or is not covered then it also need it own Enterprise licence.

Instance 3 Licence:

This instance is used (reporting) and host several databases. It needs its own Enterprise licence.


MySQL: InnoDB data dictionary has unknown flags I tried to delete and recreate a MySQL service and when I tried to start the service again I can't start it and I'm getting this error in error logs:

InnoDB: Warning: table 'sampledb/table1' InnoDB: in InnoDB data dictionary has unknown flags 50.

What could be the possible cause why I'm encountering this error?

Here is the sample error logs generated when I'm trying to start the service I have recreated:

200121 13:32:48 [Note] Plugin 'FEDERATED' is disabled.
200121 13:32:48 InnoDB: The InnoDB memory heap is disabled
200121 13:32:48 InnoDB: Mutexes and rw_locks use Windows interlocked functions
200121 13:32:48 InnoDB: Compressed tables use zlib 1.2.3
200121 13:32:48 InnoDB: Initializing buffer pool, size = 2.0G
200121 13:32:49 InnoDB: Completed initialization of buffer pool
200121 13:32:49  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 48 MB
InnoDB: Database physically writes the file full: wait...
200121 13:32:49  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 48 MB
InnoDB: Database physically writes the file full: wait...
200121 13:32:50 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
200121 13:32:50  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 386, file name mysql_bin.000027
200121 13:32:53  InnoDB: Waiting for the background threads to start
200121 13:32:54 InnoDB: 5.5.60 started; log sequence number 77829315084
200121 13:32:54 [ERROR] Aborting

200121 13:32:54  InnoDB: Warning: table 'sampledb/table1'
InnoDB: in InnoDB data dictionary has unknown flags 50.
200121 13:32:54  InnoDB: Starting shutdown...
200121 13:32:55  InnoDB: Warning: table 'sampledb/table2'
InnoDB: in InnoDB data dictionary has unknown flags 50.
200121 13:32:55  InnoDB: Shutdown completed; log sequence number 77829315084
200121 13:32:55 [Note]

This is how I recreated the MySQL service:

mysqld --install MySQL56 --defaults-file=D:\MySQL\5.6.40\my.ini

you probably have multi different version binary of mysql installed in your server, make sure you are in the correct version path...


Query Access Question With one table tblBids containing BidId field with the relationship bidID has multiple contractors from tblContractors, I am trying to create a query which will list each contractor and email address related to their BidID.

BidID      Contractor Name     Contractor Email
1          Contractor A        Contractor A Email
1          Contractor B        Contractor B Email
2          Contractor A        Contractor A Email
2          Contractor C        Contractor C Email

My goal is to create a Send Object Command to send a PDF Report out for say BidID 1 to all contractors selected to that Bid ID.

Any ideas?

This answer presumes there is no many to many table with BidId related to ContractorId which should probably exist.  Examples of the tables from which you are querying are as helpful as seeing the example of expected result.

DECLARE @BidId int = 1

    SELECT BidID, ContractorName, ContractorEmail
    FROM tblBids B
    JOIN tblContractors C
    ON B.ContractorId = C.ContractorId
    WHERE B.BidId = @BidId


How to setup Master to Master Replication with Application auto failover I have a requirement as follows , need to setup a Multi-Master replication with automatic application failover ,if one master goes down, Can anyone share me some information regarding this.

Thanks and Regards
Jabir Baig Mirza

Generally Im not a fan of Multi-Master. Since native MySQL multi-master setup will not handle the conflict detection. Im not sure about your workload, but from my personal experience, a single MySQL master can handle 30k-50k QPS. If you stick with Multi-Master think about Percona XtraDB Cluster or MySQL Group Replication.
In your case, you need a load balancer on top of mysql nodes to server the traffic without downtime.
Setup Basic Multi-Master:
There are so many articles on the internet to setup Multi-Master. I request you to use enable primary key on all tables(Innodb works better with PK). Use auto increment is ODD for Master 1, and Even identity Numbers for Master2.

[NOTE: if you have more than 2 masters, then ignore this part, it
won't work out. Its a simple to avoid conflict detections(Not all the
time)]

ProxySQL:
This is my favorite option, This will work as a Load balancer and so many features. Put both masters in the same host group. So proxysql will balance the traffic to both the servers.
HA Proxy:
I didn't use this before, but this will act as a normal load balancer. It won't any magic like ProxySQL.
Network Load balancer[If you are in Cloud]:
If you are using AWS, GCP or azure, they have inhouse load balancers. We can use them as well.


Noob questions on tables in a sql model I'm a noob when it comes to SQL or PHP and I'm a creating a rather simple car-sharing web site. The user can engage a trip someone created and can himself create a trip. Now I'm currently designing the SQL model, and I came across some doubts that maybe someone can help me with.

I want to save the user that created a given trip. That's easy, the tables "Trips" and "Users" have to be related, and the table "Trips" has the foreign key of "User" table, containing the user's ID that's related to a given trip.

The thing is, I wanted the user to log in and be given the chance to see: a) the trips he's engaged with; b) the trips he created. So, how can I separate data in order to have not only access to the user that created the trip, but also to users that are engaged in that trip (so that I can use all the info in the site)? What's the best way to arrange this efficiently in a SQL model, to save both the user that created the trip and the users that engaged it.

Without a lot more info, etc. couldn't you just create an EngageTrips table, and create an CreatedTrips table and hook a foreign key into those and then join the data once the function is run to bring that data for view to the end-user?

Also meaning control how the data is entered into each table at the data entry level based on criteria when the data is imported, etc.

Just a quick thought.


Need a parallel database for reporting purpose As our team is planning to implement BI reporting in our application, they requested a separate but a parallel db for reporting purpose in which data should be as in our production db. I read about standby database and data guard. Which is the best way for this purpose? What should I prefer? Do it cause any performance degradation to my current db?

I am using oracle 11g DB in windows environment which is in archivelog mode.

Data Guard Configurations

  Physical standby database

  Provides a physically identical copy of the primary database, with on
  disk database structures that are identical to the primary database on
  a block-for-block basis. The database schema, including indexes, are
  the same. A physical standby database is kept synchronized with the
  primary database, through Redo Apply, which recovers the redo data
  received from the primary database and applies the redo to the
  physical standby database.

  As of Oracle Database 11g release 1 (11.1), a physical standby
  database can receive and apply redo while it is open for read-only
  access. A physical standby database can therefore be used concurrently
  for data protection and reporting.

Having a physical standby in read-only open mode while applying redo requires the Active Data Guard option.

  Snapshot Standby Database

  A snapshot standby database is a fully updatable standby database.

  Like a physical or logical standby database, a snapshot standby
  database receives and archives redo data from a primary database.
  Unlike a physical or logical standby database, a snapshot standby
  database does not apply the redo data that it receives. The redo data
  received by a snapshot standby database is not applied until the
  snapshot standby is converted back into a physical standby database,
  after first discarding any local updates made to the snapshot standby
  database.

  A snapshot standby database is best used in scenarios that require a
  temporary, updatable snapshot of a physical standby database. Note
  that because redo data received by a snapshot standby database is not
  applied until it is converted back into a physical standby, the time
  needed to recover from a primary database failure is directly
  proportional to the amount of redo data that needs to be applied.

You can convert a physical standby to a snapshot standby during the period required for running reports. Then after you can convert it to physical standby to catch up to the primary database. This does not require additional licensed options on top of EE.


Sizing MySQL Database Servers for Drupal What are some guidelines to follow when it comes to sizing MySQL Servers? CPUs, RAM, Disk Space, Clock Speeds, etc?

We're going to go with either the community edition or the enterprise edition, and use it to run Drupal for a www and other top-level sites. OS is RHEL 6.x, on VMWare. We'll also probably run a caching tool, like memCache or Varnish. At the moment we are looking at two load balanced web servers that connect to one or more MySQL servers. There is talk of possibly using a master-slave or multi-master configuration. DB engine is going to be InnoDB.

We're looking at about 6000 pages of content to start, and grow quickly. Our normal throughput is about 40Mbps (observed over one hour), although we've seen it as high as 70Mbps occasionally.

Thanks,

KM

I was waiting for more experienced people to answer, but one thing I will point out, since you're using innodb engine. You'll want sizeable amount of RAM to increase the innodb_buffer_pool_size so that it can hold your dataspace, if possible. So if your database takes up 6GB, it's feasible to set the buffer_pool_size to catch it in RAM (assuming you can address that much RAM...see below). But if the database takes up 64GB, it might be a stretch (at least for something like my small company's budget) to get a server with enough RAM.

You'll need to make sure your CPU architecture and OS is 64-bit. I ran into this recently, where an old xserve upgraded to Mac OSX 10.6 can't utilize >4GB of RAM per process. Probably not an issue with modern servers.


Multiple instances of same foreign key in a single table I have a table that references the same foreign key multiple times (4 times) in the same table. Can someone explain why/how this makes sense?

Pedantically, A table doesn't reference a foreign key, it references a table via a foreign key.

It makes sense if it's necessary for the data model.

Here's an example. A Sales Order is associated with multiple parties playing different roles, such as the Selling Party, Customer Party, Paying Party, and Order Taking Party.

create table sales_order (
  order_id int primary key,
  seller_party_id int not null references parties(party_id),
  customer_party_id int not null references parties(party_id),
  order_taken_by_party_id int not null references parties(party_id),
  payer_party_id int not null references parties(party_id),
  ...
);


Dump all binary columns from a table as files I have a table in a postgresql database with a column of type bytea. I want to drop each binary entry as a file using the id as filename.

What would a SQL query look like for this?

To save bytea to the disk server-side, it is necessary to be a database superuser. Normal users are not allowed to write to the filesystem.

Assuming superuser rights, the simplest way is to implement it as a function in one of the "untrusted" languages.

Example in pl/perlu:

CREATE FUNCTION bytea_to_file(bytea,text) RETURNS void AS $$
  open(my $fd, ">".$_[1]) or die $!;
  binmode($fd);
  print $fd decode_bytea($_[0]);
  close($fd);
$$ language plperlu;

Usage:

select bytea_to_file(bytea_column, concat('/path/to/destination/', id_column))
from tablename where...

If such languages are not available in your postgres environment, a  non-efficient version can be made in pl/pgsql. It's non efficient because it has to create a temporary large object and copy the entire data into it before exporting it as a file, and then purge the large object.

pl/pgsql version (inefficient)

CREATE FUNCTION bytea_to_file_with_lo(bytea,text) RETURNS void AS $$
declare
 o oid;
 fd integer;
 INV_WRITE int := 131072;
begin
 o:=lo_create(-1);
 fd:=lo_open(o, INV_WRITE);
 if (fd<0) then
   raise exception 'Failed to open large object %', o;
 end if;

 perform lowrite(fd, $1);
 if (lo_close(fd)<>0) then
   raise exception 'Failed to close large object %', o;
 end if;

 perform lo_export(o, $2);
 perform lo_unlink(o);
end;
$$ language plpgsql;

Usage: the same as pl/perlu version. You still need to be superuser, unless the superuser privileges for this function only are granted to other users (which in the present case would be only OK if 100% trusting the users, otherwise it's disastrous):

alter FUNCTION bytea_to_file_with_lo(bytea,text) SECURITY DEFINER;

psql client-side version (mildly inefficient)

When all you have at your disposal is a client-side psql interpreter, binary contents cannot be extracted directly (it boils down to psql being adverse to the '\0' byte), but an intermediate representation in hex or base64 can easily be obtained and post-processed outside of psql.

Example in shell, assuming existence of the base64 command from GNU coreutils:

$ psql -Atc "select encode(bytea_column,'base64') from tablename" | \
   base64 -d >/path/to/destination/filename

This outputs only one line and column at a time. Extracting another column at the same time (like an ID) would be significantly more difficult for the post-processing, so this simple example assumes some kind of outer loop going through the IDs that would have been obtained previously.


Application server uses single login to connect to a database Application server uses single login to connect to a database . There are multiple users connected to the database through the login in that case the information shows only the application login and the host name .

How do i track the individual user that is connected to database through the app server?

Since the dmv only shows the application login and the host name which is same for all the users that are connected.

You cannot in this case. SQL has no way to know the windows login using the application account and workstation connecting via application server.

See this answer especially read second answer by Aaron Bertand which explains in details.

Who is the person using a SQL login?


Can I configure Oracle such that a Windows client can connect to a Linux Oracle server without a password? I am attempting to configure Kerberos authentication to an Oracle 11.2 database from a Windows client.  The goal is to have an IIS Application Pool connect to the DB via its service account credentials without having to store a password in the connection string.

Environment information:

Windows 2008R2 Active Directory
Oracle 11.2 DB on Linux AIX host
Windows 2008R2x64 Client PCs

Using Oracle ODAC .NET driver to connect to DB via IIS Applicaiton Pool

Is this feasible?  I've seen several posts that indicate it is, but I am unable to get even sqldeveloper to authenticate with Kerberos from both the DB server itself and the Windows client.

I am able to retrieve a kerberos ticket on the DB server (okinit works).

When I attempt to connect in sqldeveloper on the DB server, I get a password prompt in the UI, which closes as if the password was accepted, but nothing opens afterwards.  Attempting to expand the DB node results in another password prompt that behaves similarly (no error, just goes away with no additional UI).

I fear if I cannot even get sqldeveloper to work, there is little hope for ODAC.

Kerberos is way too complicated for this task, I would not bother with it.

ODAC supports using the a Secure External Password Store:

Using Secure External Password Store

You can point to the wallet location in your configuration in the settings section .

An example:

https://dbaportal.eu/2015/11/26/odac-12c-release-4-odp-net-managed-driver-with-oracle-wallet-gotcha/


Regularly clone/backup 2 MySQL databases unidirectionally from PC to Laptop both running Windows I have a PC running Windows XP with a custom ERP system and MySQL Server 4.x (A) and a Laptop running Windows 10, same MySQL 4.x and same custom ERP (B). I'm afraid I can't upgrade PC nor MySQL, etc. Can't touch anything.

I installed SQLYog Ultimate (Workbench/Navicat don't like this MySQL version) on laptop and tried scheduling an unidirectional database syncing job from (A) to (B).
It works, but the problem is that SQLYog compares table by table, and when different, copies record by record and this takes hours to complete, because databases to be cloned from (A) are 1.5 and 2 gb each

I've been searching other ways I could setup something in laptop to regularly (once or twice a week through Windows Scheduler) clone a database directly from (A) to (B) without user intervention, but I've run out of ideas.

Any clues? Thanks

You need to set up MySQL replication.
In your case, can use the following documentation for version 4.1

http://ftp.nchu.edu.tw/MySQL/doc/refman/4.1/en/replication-howto.html

An additional recommendation, upgrade those servers to a newer version.


What are some technical reasons for not including N-ary multiplication in the SQL standard? If I want to add up two values in a query, I can use the + operator.
If I want to sum up all values returned by a query, I can use SUM.

If I want to multiply two values in a query, I can use the * operator.
If, however, I want to multiply together all values returned by a query, there does not appear to be a MULT or PRODUCT or the like.

I find myself using exp(sum(ln(X))), which is ridiculous. It's astonishing that this feature is not present since even more "esoteric" things like covariances and regression coefficients are conveniently available in some dialects.

Why is it that the SQL standard does not have this (I believe) "basic" feature? Surely it must be a conscious design decision. Are there any technical advantages to not having this operation or discouraging users from performing multiplication on large query results?

Yes, the PostgreSQL core team has spent days deciding that this should not be included in core.

Seriously:

a mul operator is not in the SQL standard.
With PostgreSQL's extensibility, it is trivial to define such an aggregate function yourself:

CREATE AGGREGATE mul(integer) (
   SFUNC = int4mul,
   STYPE = integer,
   INITCOND = '1'
);


SQL Server 2012 Not Using Parallel Cores for Parallel Connections - Scheduler Problem? I have a web system using SQL Server 2012 Enterprise Edition as a backend.

One of our queries is particularly heavy taking around 60 seconds. I have analysed this query heavily and know that the time is spent in a INSERT INTO tablevariable SELECT FROM table. This means that there should be a shared lock on the table but parallel processes should be able to run this query in parallel.

One web endpoint calls 3 of this procedures in parallel on separate processes. Since we have 4 cores I would expect that SQL server should run one process on a core so 3 run simultaneously giving us query times of approximately 60 seconds.

However most of the time it will switch between running 2 of the processes and then 1 of the processes around every 20-30 seconds. I can see the CPU jump between 25% and 50% usage (since a 4 core machine).

The processes that aren't running are in the RUNNABLE state so it seems they are just waiting on CPU time, no table locks causing the issue.

Occaisionally it will actually only run 1 at a time, and occasionally all 3 simultaneously so something is causing some decision making.

I have checked that the licensing sees all 4 cores (it does) and the processor affinity is set to use all. Since this isn't within a query I don't expect MAX_DOP to impact this but I have tried it at 0 and 2.

Any idea what influences SQL servers decision making here? and if we can change this behaviour?

First, inserting data into a table variable is single-threaded (not parallel). You can learn more about that in Paul White's epic post Fording a Parallel Execution Plan.

Second, when you're running multiple queries, you can't predict which queries will end up on which cores. An incoming query gets assigned to a worker, which is pinned to a scheduler, which runs on a CPU core. SQL Server doesn't know in advance how complex the query (or queries) will be, so it can't predictively load balance. If you truly want to get the CPU to 100%, you'll want to run a lot more than 3 queries, or you'll want to check DMVs to abort your queries if they end up on the same scheduler.

Or do what I would do - stop using table variables and switch to a temp table instead.


Any issue encountered after implementing SQL Server 2017 CU22? We all are aware of SQL Server 2019 CU7 issue (Microsoft recalled the patch). Now I am cautiously optimistic to apply SQL Server 2017 CU22. I wanted to reach out to the SQL Server Community to know if anyone implemented a new update (CU22) and encounter any issue. I tried to research online but end up having nothing. Thank you all for sharing.

Couple of weeks ago we patched to SQL2017 CU 22 in our all test and staging environment to begin with.
So far we did not see any issues. We were planning to go in prod by this weekend but from SQL 2019 CU 7 horror we prefer to make a change in our process to push updates to prod by at least a month now.
So as of now we have not see any issues on 2017. As a general guidance I will say patch to all lower environments and may be wait for some time , you decide that interval , and move ahead with the decision to patch.


Using the same table for entities that are almost alike when it breaks foreign key integrity? I'm designing a website with a database that should support events that a user can sign up for, fx. a concert. Users can sign up using our internal registration system, or using an external website (which we just redirect to).

So events can be internal or external. Both types of events have the same type of metadata such as date, title and place etc. If the event is internal then some can tables refer to the event ID using a foreign key, fx. the many-to-many table users_events, there's also some tables that handle billing-data related to internal events.

My current setup is a single event table that has the columns I mentioned before, date, title, place etc. It also has an external_signup_url column, which is NULL when an event is internal, and contains an URL for when an event is external. The problem with this design is that the tables that references an event ID by a foreign key can potentially reference an external event, which makes no sense at the application level.

So my question is, even though the two types of events are so similar (attribute-wise), does the small difference, and the potential non-sensical foreign key reference, really warrant separate tables, fx. external_event and internal_event?

Typically you would design the database such that someone coming into the database alone could get a handle on how the application worked. Had they seen such a foreign key they may not be aware of it's implications.

You could approach this in two ways:
First, you could do as you mentioned. Create two separate tables and handle them there.

Second, and the way I would recommend, you would create a table to handle all the common fields between the two, and then two other tables, one for internal-specific data, and one for external-specific data. Each of those tables would have a primary-foreign key to the common table. I.e. a Guid or ID.

The downfall of the second method is the application would be required to be aware of the two different tables, and depending on the method you are using to implement it (.NET, PHP, Python, etc) you may find one method easier than the other.

I personally use .NET and C# or VB, depending on the project, and in this situation I would go with the three-table design, as I would create an abstract class ("Event") and then two classes to inherit it and all the common properties ("ExternalEvent", "InternalEvent"). The Event class would handle the interaction of the shared properties, and the InternalEvent and ExternalEvent would handle the logic specific to them.

The upside to this approach is the polymorphism of it. If you add a common field to the two in the future (for whatever unprecedented reason) you only have to add/change it in one location.


MongoDB: Creating index very slow I'm currently creating an index on a mongo database and so far it has taken about 52 hours. I've worked with large(r) Mongo databases before but creating an index has never taken this long. My collection has 1 billion rows, and I'm creating the index on a single string field which always has a length of 12.

Should I just leave it running, or is it potentially broken and should just be stopped?

Thanks in advance!

Index creation will be show in the mongo error logs. You can monitor the progress of the index creation. 52 hours is very slow. Can you post the masked error log here?


Handling the foreign keys in data entry I have two tables (Order and equipment) and they should be linked together by the order No. the SQL script is as the following:
-- -----------------------------------------------------
-- Table `rr`.`order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rr`.`order` (
  `Order_No.` INT UNSIGNED NOT NULL,
  `Order_Type` CHAR(8) NOT NULL,
  `Equipment_Quantity` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`Order_No.`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = '\\n';

-- -----------------------------------------------------
-- Table `rr`.`equipment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rr`.`equipment` (
  `S_N` INT UNSIGNED NOT NULL,
  `OrderNo.` INT UNSIGNED NOT NULL,
   `V_N` INT UNSIGNED NOT NULL,
  `LS_N` INT UNSIGNED NOT NULL,
  `FK_EQ_Inspection_ID` INT UNSIGNED NOT NULL,
  `FK_EQ_Reinspect_ID` INT UNSIGNED NOT NULL,
  `FK_EQ_Scentencing_Notification_No` INT UNSIGNED NOT NULL,
  `FK_EQ_Repair_ID` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`S_N`, `OrderNo.`, `FK_EQ_Scentencing_Notification_No`),
  INDEX `OrderNo._idx` (`OrderNo.` ASC) VISIBLE,
  INDEX `FK_EQ_Notification_No_idx` (`FK_EQ_Inspection_ID` ASC) VISIBLE,
  INDEX `FK_EQ_Confirmation No._idx` (`FK_EQ_Reinspect_ID` ASC) VISIBLE,
  INDEX `fk_equipment_scentencing1_idx` (`FK_EQ_Scentencing_Notification_No` ASC) VISIBLE,
  INDEX `FK_EQ_Repair_ID_idx` (`FK_EQ_Repair_ID` ASC) VISIBLE,
  CONSTRAINT `FK_EQ_Reinspect_ID`
    FOREIGN KEY (`FK_EQ_Reinspect_ID`)
    REFERENCES `rr`.`re-inspect` (`Reinspect_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Inspection_ID`
    FOREIGN KEY (`FK_EQ_Inspection_ID`)
    REFERENCES `rr`.`inspection` (`Inspection_ID`),
  CONSTRAINT `OrderNo.`
    FOREIGN KEY (`OrderNo.`)
    REFERENCES `rr`.`order` (`Order_No.`),
  CONSTRAINT `FK_EQ_Scentencing_Notification_No`
    FOREIGN KEY (`FK_EQ_Scentencing_Notification_No`)
    REFERENCES `rr`.`scentencing` (`Scentencing_Notification_No`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_EQ_Repair_ID`
    FOREIGN KEY (`FK_EQ_Repair_ID`)
    REFERENCES `rr`.`repair` (`Repair_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

USE `rr`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `rr`.`before_equipments_insert`
BEFORE INSERT ON `rr`.`equipment`
FOR EACH ROW
BEGIN
 IF NEW.V_N = 1 THEN
     SET new.LS_N = 0;
 END IF;

END$$

USE `rr`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `rr`.`before_equipments_UPDATE`
BEFORE UPDATE ON `rr`.`equipment`
FOR EACH ROW
BEGIN
 IF NEW.V_N = 1 THEN
     SET new.LS_N = 0;
 END IF;

END$$

by checking the 'equipment' table, it includes 'Order_No.' as a foreign key from the 'Order' table in addition to other foreign keys from other different tables.
I entered the data of one row for the 'ORDER' table, I was expecting that the value of foreign key 'Order_No.' in the 'equipment' table would read automatically and that did not happen. so how to do that?
Also, when I enter row of data in the 'equipment' table, will I ignore the other foreign keys as they will be linked through their tables?

Foreign Keys don't automatically generate in the child referenced tables unless you use the CASCADE specifier in the declaration of the foreign key. See Why foreign key doesn't update? for more information on how to use CASCADE with foreign keys and alternative solutions.


Should Crystal Report be allowed for certain employees for production database? I am talking about production database. There are couple of people who use crystal on daily basis for various types of reports. Some use it to just peak at the data, other generate reports of it. Should this be allowed from security/performance/other point of view? There is no http access to our database/application, everything is internal on intranet.

What is the correct procedure here? The employee in current scenario have the password for the database. Should it be changed to Windows Authentication? Any suggestions.

It will depend on the type of data being access and your company's security requirements/policy. In general you do not want users sharing an account, especially if that account has more than read permissions on your database. I would verify with your security folks or legal group if there are any specific requirements for managing access to data, or with auditing. If you fall under any industry standards like HIPPA or PCI they have controls that require being able to map access to a particular user.

With regards to how you allow users to run reports that is usually driven by policy where I have worked. Do you experience any performance issues now with how they are pulling reports and "peak" at data? As a DBA, I'm a control freak to a certain degree, I like to know who and what is making calls to my data. I have been in one environment where reports were pointed to a database snapshot of the production database, so their was less of a performance concern. Another environment it was not until their report caused a performance issue in production that we would tell them if you want that information run it after business hours.


Multiple inserts across tables with serial primary keys I asked this question on stack overflow, but I'm thinking it's better on this stack. Apologies if I've got that wrong. The motivation for this question is to prepare some sql scripts I can run during unit tests and production to define a bunch of master data for a web-based application that I'm building.

I have three tables, which I've abbreviated into the following

create table top (top_id serial, top_name text);
create table mid (mid_id serial, top_id integer, mid_name text);
create table bot (bot_id serial, mid_id integer, bot_name text);

what I want to achieve is

insert a value into top, and capture it's ID
insert multiple values into mid, using the same top_id,
for each value in mid, insert multiple values into bot, using each mid_id.

So, the final result might look like

top
----
1, first_top
2, second_top

mid
---
1, 1, first_mid_for_first_top
2, 1, second_mid_for_first_top
3, 2, first_mid_for_second_top

bot
---
1, 1, first_bot_for_first_mid
2, 1, second_bot_for_first_mid
3, 3, first_bot_for_third_mid

My first approach was to try to have a bunch of nested CTEs, but I couldn't make that into valid syntax (and on consideration, I think that it's not the right approach).

My second approach was to try to store the values into some sort of variable, but I don't know how to do that outside of the pg command line tool.

What I'm after is something analogous to

top_id_one = insert into top(null, 'first_top') returning top_id
mid_id_one = insert into mid(null, top_id_one, 'first_mid_for_first_top) returning mid_id
insert into bot(null, mid_id_one, 'first_bot_for_first_mid')

in a "pure sql" that I could paste into PG admin for example.

I realise that there's a few problems in the example above (for example using 'top' as a table name). My explorations have avoided these issues, but I was trying to find table names that would make my example make sense.

The following is incorrect:

insert into top values (null, 'first_top')

You are explicitly passing null for the first column. Postgres will (try to) store that value in the column and the sequence (that the serial uses) will not be used! By supplying an explicit value for a serial column (or any column with a default) you are overwriting that automatic default.

Now for the actual question:

You can use lastval() to obtain the most recently generated sequence value:

insert into top (top_name) values ('first_top');
insert into mid (top_id, mid_name) values (lastval(), 'first_mid_for_first_top');
insert into bot (mid_id, bot_name) values (lastval(), 'first_bot_for_first_mid');

You can't however insert multiple rows into the mid table in a single statement with that.


Join tables where the table name changes every day I need some help in figuring out how to join tables to my query.  The table names change every day as the date is used in the table name and older tables are dropped as they are no longer needed.

My query looks for "scans" that have data in the database and are in the process of being processed (for a lack of a better word).

My current query is:

USE RetinaCSDatabase
SELECT S.ScanID [Scan ID], S.PolicyName [Scan], DATEADD(hh, +2, S.StartTime) [Scan Start], A.AssetName [Scanner], COUNT(AIPS.AssetIPID) [Hosts], S.TransactionGroup [Transaction Group ID]
FROM Scan S (NOLOCK)
LEFT JOIN Scanner SC (NOLOCK) ON S.ScannerID = SC.ScannerID
LEFT JOIN Asset A (NOLOCK) ON SC.AssetID = A.AssetID
LEFT JOIN Asset_IP_Scan AIPS (NOLOCK) ON S.ScanID = AIPS.ScanID
WHERE S.ProcessStatus < 2
GROUP BY S.ScanID, S.PolicyName, S.StartTime, A.AssetName, S.TransactionGroup
ORDER BY S.StartTime ASC, S.PolicyName ASC, A.AssetName ASC

What I need to join is the table(s) with the data still requiring processing.  Those tables start with the name "evt_scan_" and then the date.  So, a full table name would be "evt_scan_20171222" for example.  The data for a scan performed on the 22nd of December 2017 will have data in the table "evt_scan_20171222" and possibly in the following days table, "evt_scan_20171223".  So, I need my query first to check if the table exists and then join it to do a count and also to check if the following days table exists, join it to do another count and add up the 2 counts to show the outstanding records requiring processing.

If the table names were static I would join them with this query:

LEFT JOIN evt_scan_20171222 evt1 ON S.TransactionGroup = evt1.evtTransactionGroup

It would also require a WHERE statement returning only records where evtStatus = 0.

Thanks

Use a synonym.

USE YourDatabaseName;
DECLARE @sql nvarchar(max) = N'CREATE SYNONYM
  dbo.EventScan FOR dbo.evt_scan_'
  + CONVERT(CHAR(8), GETDATE(), 112);
EXEC sys.sp_executesql @sql;

Then your code can always reference dbo.EventScan.

Now, schedule a job to run every night at 12:00:01 AM (or create a DDL trigger on table creation and when the new table name matches the expected pattern, or put code in whatever creates those new tables each day) that does this:

USE YourDatabaseName;
DROP SYNONYM dbo.EventScan;
— repeat code from above


Will DB backup time and size be decreased after truncate large tables Two tables in our SQL Server DB equate to 500M rows and 350GB of data. Disk space is an issue where we keep backups and the time to execute backups is as well. We plan to truncate these 2 tables.

I understand that to reclaim the disk space, we'll need to shrink the DB after truncation, then do an index rebuild to fix fragmented indexes afterwards. But as we are not sure how long the rebuilds will take (we are spinning up a test server VM in Azure now to test), in the meantime we are looking for incremental gains. After just truncating (before shrink and rebuild), will the backup times and the resulting backup file size (after 7z compression) be reduced dramatically as a result?

While it's been some time since I dealt with SQL Server backups that weren't compressed by SQL itself, SQL backs ups your data, not your free space.

As stated here

  The data contained in a full backup is not necessarily all of the
  contents of all data files. The backup will only contain the allocated
  pages from the data files.

TRUNCATE deallocates data pages. If the space consumed by your data drops from 700GB to 350GB, then the size of the backups should be about half what it was as well, and the time to take the backup should indeed be similarly reduced.


Normalizing a database without access to source data? I've started a new role which deals with large amount of related data. Our source of all this data is various Excel dumps pulled from databases we do not have access to. The previous person filling this role used a dozen or so Excel files to gather these data files, manipulate them, and create reports.

I have started moving the dumps to an Access database. I have noticed a lot of the Excel data is related and should probably be normalized. What I have currently been doing is creating a table for every data dump, and importing them to Access, and using a number of queries to replicate the dozens of data manipulations and reporting.

Are there still benefits to normalizing data where my only source are Excel dumps out of a warehouse?

How would I normalize data, when I do not have the ability to change the format of how the dumps are sent to me?

Additionally, my plan (budget-depending) is to move from Access to a MS SQL Database.

Yes there are significant benefits to data normalization if you are willing to do the work to achieve and maintain it.  The two fundamental benefits to normalization are:

Data Integrity
Query Flexibility

The simple approach to normalization is to create a table for each person, place, thing, concept, or event.  By doing this, you have each characteristic of each person, place, thing, concept, or event of interest to you in one and only one place in the database.  The benefit of data integrity is achieved for when you insert and update data.  Because you have normalized you don't have redundant copies of the same characteristic in many places across the database, each with a different value, that you have to remember to update and keep in sync.  Second, you have only one place in the database to program checks when inserting or updating data to make sure the data is valid.  Since your data is coming in from many excel spreadsheets, normalizing your data gives you the opportunity to implement data integrity checks on the load to make sure you analysis is based on accurate data.  The benefit of query flexibility is achieved for when you want to read and analyze the data.  Because you have normalized the data you can connect up the tables in a flexible way based upon the question about the data you want answered, including only what you need to answer the specific question.  Second, this enables the database to return the answers to your questions much faster than if it had to scan through all the data, including the data not relevant to your question, in your un-normalized  tables .

Access is a simplified DBMS and does include a basic SQL processor that allows you to write queries and thus take advantage of the benefits of normalized data.  If you are eventually going to move to SQL Server, which is a full featured DBMS, then normalizing your data now will ease the transition and let you take advantage of the full capabilities of SQL Server and its very rich implementation of SQL.

As I mentioned in the beginning, to achieve these benefits you must be willing to do the up front programming to translate the data coming in from your various excel dumps and map the rows and columns in those spreadsheets to your normalized tables.  This is not a trivial exercise but doable using Access programming.  One approach would be to create tables that replicate the data as in the source and load the data into them.  These are known as stage tables.  Once you have the un-normalized data in Access tables you can then more easily write access code using SQL to extract the data from those stage tables, normalize it, identify data quality issues (say the same characteristic in two different excel dumps that should have the same value but do not), and load it into your normalized tables. This is the common method for normalizing data coming from an un-normalized source very common in subject area based data warehouses.

You will find this additional level of effort to be well worth it though once you have high quality, normalized data in your access database.  You report consumers will see that you are a real data professional when you show them examples where data quality was poor and you discovered that fact so it can be corrected in the sources.  Likewise, when they ask for a new report that analyzes the data in a very different way, you can rapidly create the new report using SQL to combine the data in the normalized tables in this very different way that wasn't originally anticipated.  They will be very impressed that you are able to do this quickly and easily!

I hope this helps explain why normalization would be of benefit to you.


In PHP MySQL how to sum or not sum multiple columns? I have three tables this is my codes:

        Subject
        Mark1
        Mark2
        Mark3
        Sum Marks

<?php
    $query2 = "SELECT  students_info.s_name, subjects.subject, marks.*
            FROM  subjects
            RIGHT JOIN  (students_info
            RIGHT JOIN  marks  ON students_info.s_id = marks.s_id
                )  ON subjects.sub_id = marks.sub_id
            WHERE  students_info.s_id = '".$s_id."'";

    $result2=mysqli_query($link,$query2) OR die(mysqli_error($link));

        while ($row=mysqli_fetch_array($result2))
        {
 ?>

            <?php echo $row['subject'];?>
            <?php echo $row['n1'];?>
            <?php echo $row['n2'];?>
            <?php echo $row['n3'];?>
            <?php echo $row['n1']+$row['n2']+$row['n3'];?>

<?php
        }
?>

 <?php
}
?>

Output my codes:

I want when each mark form(mark1,mark2,mark3) for every subject less than 49 the sum operation not work in the cell sum marks appear empty value or empty cell,
Please review my code and give me a help, to do work this code.

Could add them up in SQL by reverting to 0 for NULL columns like:

SELECT students_info.s_name, subjects.subject, marks.*,
       COALESCE(marks.n1, 0) + COALESCE(marks.n2, 0) +COALESCE(marks.n3, 0) as  sum_marks
FROM ....


MySQL Workbench Unable to Connect to Azure Databases for MySQL When  trying to connect to a MySQL database in Azure I get the following error:

  ERROR 2026: SSL connection error: socket layer receive error

At first I thought it was a firewall/security issue but it appears not. The certificates are correct - and I have also tried with SSL disabled on both ends.

This article hints at the issue, though it is slightly different. After updating the SQL Connector from version 6.x to 8.0.11 and workbench itself to 6.3.10 it has worked perfectly.