Query that displays backup information (successful and failed) SQL Server I have two jobs that backs up two different databases.
Job 1 backs up DB1
Job 2 backs up DB2
DB1 fails to back up due to low space on Drive 1 resulting to a Job 1 failure. To fix that issue, I just had to add space. No biggie. I was told about this today when the issue has been occuring for a month . Yeah I know it's crazy but it's dev

I want to get a complete backup history for DB1. I know that I can retrieve successful backup information from the msdb.dbo.backupset table but I want to know if there is a query that displays failed backups for a database.

My query below displays backup history for a particular database from 12/31/13-1/27/14. Info includes server, database name, Backup start and end times, Total time it took for the dbs to be backed up, db size and backup set name.

SELECT
   distinct CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
 CAST((DATEDIFF(second,  msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date)) AS varchar)+ ' secs  ' AS [Total Time] ,

   Cast(msdb.dbo.backupset.backup_size/1024/1024 AS numeric(10,2)) AS 'Backup Size(MB)',
   msdb.dbo.backupset.name AS backupset_name
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
--Enter your database below
--and database_name = 'db_name_here'
and msdb.dbo.backupset.backup_start_date>'2013-12-31' and msdb.dbo.backupset.backup_start_date<'2014-01-27 23:59:59'
ORDER BY
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date

Is there any way to get that information by modifying my code? I'm able to retrieve JOB1's history by executing a sql statement that runs against the sysjobhistory and sysjob table. This may be a long shot Is there a way I can use the sysjobhistory, sysjob, backupset and backupsetmediafamily tables in msdb to produce the results I want?

Sadly, backupset does not contain failed backups, and I don't know of anywhere else in msdb these may be stored, unless you can rely on sysjobhistory, which doesn't contain all of time (depending on your retention settings), and which would ignore any backup attempts that were made outside the context of a job, and which - in the case of a job that backs up many databases - would not provide differentiation about which database actually failed, unless it happened to happen early on in the job - this is because the messaging is quite verbose but gets truncated.

If you absolutely know that Job n only backs up the one database, and that every failure of that job means that the database wasn't backed up (since the job could also fail after the backup succeeded, e.g. trying to shrink or perform other maintenance), then you could use a query like this:

DECLARE @job sysname, @db sysname;

SELECT @job = N'Job 1', @db = N'db_name';

SELECT
   bs.database_name,
   bs.backup_start_date,
   bs.backup_finish_date,
   [Total Time] = CAST((DATEDIFF(SECOND, bs.backup_start_date,bs.backup_finish_date))
     AS varchar(30))+ ' secs',
   CAST(bs.backup_size/1024/1024 AS decimal(10,2)) AS 'Backup Size(MB)',
   h.[message]
FROM msdb.dbo.sysjobhistory AS h
INNER JOIN msdb.dbo.sysjobs AS j
ON h.job_id = j.job_id
AND h.step_id = 0
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON bs.database_name = @db
AND
 ABS(DATEDIFF(SECOND, bs.backup_start_date, CONVERT(DATETIME,convert(char(8),h.run_date)
   + ' ' + STUFF(STUFF(RIGHT('0'+CONVERT(char(6),h.run_time),6),3,0,':'),6,0,':')))) < 5
WHERE j.name = @job
ORDER BY bs.backup_start_date;

Yes, it's really ugly, because sysjobhistory still, in SQL Server 2014 even, stores run_date and run_time as separate integers. I bet whoever made that decision is still on the background of dartboards all over building 35. It also assumes that the backup is the very first step in the job, hence the rather less than scientific date/time comparison to make sure we've properly correlated the right instance of the job to the right instance of the backup. Oh, how I wish I could redesign the schema for backups and jobs.

If you want broader scope outside of the job, you can look for failed backups in the SQL Server error log (if they haven't been cycled away):

EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current
EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)
EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)
....

(But I don't know of a nice and easy way to incorporate that output into your existing query.)

You can also correlate "missing" successful backups from the default trace, e.g.

DECLARE @path nvarchar(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT dt.DatabaseName, dt.StartTime, bs.backup_start_date, bs.backup_finish_date,
  [Status] = CASE WHEN bs.backup_start_date IS NULL
    THEN 'Probably failed'
    ELSE 'Seems like success'
  END
FROM sys.fn_trace_gettable(@path, DEFAULT) AS dt
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON dt.DatabaseName = bs.database_name
AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5
WHERE dt.EventClass = 115 -- backup/restore events
AND UPPER(CONVERT(nvarchar(max),dt.TextData)) LIKE N'BACKUP%DATABASE%'
--AND dt.DatabaseName = N'db_name' -- to filter to a single database
--AND bs.database_name = N'db_name'
ORDER BY dt.StartTime;

Of course this also relies on the data from the default trace cycling away, the database name not having changed, etc. And unfortunately, the default trace doesn't differentiate between successful and failed backups, and the start time will not precisely match the MSDB data, but as long as you're not running backups in a loop, this should be okay for eyeballing. I've tried to incorporate these issues into the query.

Finally, you may want to use a FULL OUTER JOIN there, in case backupset has longer history than the default trace. This changes the semantics of [Status] slightly.

You also might want to give this nasty thing a try, though I didn't have much luck with it. I was only able to see the current or most recent status, so that only helped when the job failed the last time it ran, and - like sysjobhistory - wasn't able to obtain information about any backups that were attempted but not through a job.


How can I get the whole execution log of stored procedure? I know that how many times it was executed from execution_count in sys.dm_exec_procedure_stats.But how these values are Calculated? Is there any way to know how these values get calculated? I want whole log when the stored procedure was first time executed to till last_execution_time logs.

But how these values are Calculated? Is there any way to know how
  these values get calculated?

Answer to this can be refer from MSDN stating below

sys.dm_exec_procedure_stats

  Returns aggregate performance statistics for cached stored procedures.
  The view returns one row for each cached stored procedure plan, and
  the lifetime of the row is as long as the stored procedure remains
  cached. When a stored procedure is removed from the cache, the
  corresponding row is eliminated from this view. At that time, a
  Performance Statistics SQL trace event is raised similar to
  sys.dm_exec_query_stats

You can run something like below code to collect the data from this dmv as per the load to analyse the usage of resources during the execution of that SP:

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
ORDER BY AVG_LOGICAL_READS DESC

You can modify the query above and gather the data to collect the metrics for the SP by scheduling it via SQL agent job. After a certain interval you will have sufficient data to make analysis. An example of such is illustrated here.

Hope this helps!


How to restore a specific table from a big mysqldump file? From a very big backup file (created by mysqldump) I need to bring back only one (or a few) specific table(s), without loading all tables.
Are there any fast alternatives?

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

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

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

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


How to upgrade MySQL to newer version on existing database I am having data of 20 GB with MySQL version 5.0.24.

Now I want to update MySQL to newer version 5.5.16.

We can do it by taking complete database backup and uninstall old MySQL and install new MySQL and restore database.

But, Is there any way to upgrade MySQL directly to newer version, as it is a production setup and wanted very less down time of the server.

Please help me on this.

I am running on Windows.

Well, first off, if you are upgrading from 5.0 to 5.5, you really need to do this on a test server prior to making the upgrade in a production environment. This will get you comfortable with the steps of upgrading and let you figure out any trouble areas beforehand, which will dramatically reduce your downtime of upgrading.

So to upgrade, I would upgrade from 5.0 to 5.1, then 5.1 to 5.5 as the manual suggests. There will be downtime, but if you do the upgrade steps on a test setup first, you will reduce your downtime dramatically. Make sure you have all the installers downloaded beforehand, don't wait to download them while you're in downtime.

You could do the replication setup as Rolando points out, but please don't do the upgrade on production without testing out the steps first.


Finding rows with same id but different date Consider below table tbl:

id    date
1     2016
1     2017
2     2017
2     2017
3     2016
3     2017
4     2018
5     2018

How can I get only rows with same id but different date.

Right now I can only get ids with more than 1 count like so:

select id, date
from tbl
group by id
having count(id)>1

How can I only get id 1 and 3 along with date?

Use an AGGREGATE function like MIN or MAX.

If you only want one record per ID, you have to choose which date you want.
The first (MIN) or the latest (MAX).

https://docs.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-2017

^^Above information is not what was requested.

Edited per clarification in comments:  Just use the HAVING to identify which ID values have more than one date, then apply that to a new SELECT.

SELECT id, Date
FROM tbl
WHERE id IN
    (
        select id
        from tbl
        group by id
        having count(distinct date)>1
    )


Why a transaction retrieve the same row more than one time? I understand that nonrepeatable read problem occurs when a transaction t1 retrives a row, transaction t2 then updates that row and transaction t1 then retrieves the same row again. T1 retrieved the same row, but saw two different values. I wonder why a transaction read the same row again. Why it did not use the old value? If it retrieve again to get updated value, getting different values should not be problem. Can you please explain me? When repeatable read isolation level should be used?

I wonder why a transaction read the same row again. Why it
  did not use the old value? If it retrieve again to get updated value,
  getting different values should not be problem.

My guess is that the developer simply didn't consider the possibility of concurrent updates and the complications around that.

  When repeatable read isolation level should be used?

Assuming T1 is a explicit transaction (i.e. it has Begin Tran, Commit Tran), I believe running T1 under repeatable read isolation level would protect you from this particular problem. However it may cause blocking or deadlocks more than is necessary.

If the one problematic row is the only possible problem, then it may be sufficient to select it just once using the WITH (UPDLOCK) hint, which will prevent other transactions modifying it until T1 completes. However other transactions will still be able to read the row (which may then be updated by T1), unless they also use WITH (UPDLOCK).


Universally converting UUID to IDs what are the best practices in Postgres for globally converting UUIDs into IDs. I'm currently using both UUIDs and IDs in my schema. The intent is for UUIDs to be exposed to the public (for URLs et.) and IDs for internal systems.

I've looked at the following methods but am unsure of the best practices.

a custom function that does a UUID/ID lookup on the target table
using a BEFORE trigger that does the conversion and keeps all postgres functions clean

Can someone provide some wisdom/suggestions here?

Thanks!

For exposing it to public, we kept ID field. Its user friendly to see an ID vs UUID in URL. We replaced ID field from primary key and introduced UUID primary key. ID and UUID both exists for different reasons. For searchabilty, showing in URL, we still continue showing ID field.

This suggests something similar: https://stackoverflow.com/questions/52414414/best-practices-on-primary-key-auto-increment-and-uuid-in-rdbms-and-sql-databas

Some additional reading: https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439


Mysql Command required for getting values from my.cnf I am new to MySQL DBA, i have a doubt, Is there any command for finding my.cnf configuration file variables and its respective value which is mention in my.cnf file in mysql prompt itself. like we will see the global variable which are there in mysql in below format
mysql> show variable like'xxxx';

Similar kind of command is there for seeing all parameter present in my.cnf.

You could use this reference: MySQL Documentation.

For example:

mysql> SHOW VARIABLES;
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| auto_increment_increment        | 1                                   |
| auto_increment_offset           | 1                                   |
| automatic_sp_privileges         | ON                                  |
| back_log                        | 50                                  |
…

  To obtain the row for a specific variable, use a LIKE clause as shown:

  SHOW VARIABLES LIKE '%size%';
  SHOW GLOBAL VARIABLES LIKE '%size%';


Mysql Select Subquery where clause subquery equal alias ( AS subquery named ) I'am tired looking for this issue on internet, I don't know what a keyword for search for that, and I can`t find same issue for my issue.

Ok to the point.

select id, name,

(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as haveBonA,

(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as haveConA,

IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC

from tableA;

If this query run output error : Unknown column 'haveBonA' in 'field list'

I don't know how to express it. and I'm sorry, can't speak english.
Thank Your.

SOLVED

select id, name,

(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as haveBonA,

(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as haveConA,

(select (IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC)

from tableA;

I change to (select (IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC)
Thank you for all answering.

You can try this

with t1 as (
select id, name,
(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as
haveBonA,
(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as
haveConA
from tableA
)

select t1.*,
   case
   when t1.haveBonA = t1.haveConA then "Equal"
   else "Not Equal"
   end equalTableBTableC
from t1
where 1=1


Designing a tracking system for ordered workouts I've taken a handful of introductory database courses, so I thought I'd design a relational database to help me keep track of my workout routines. It turned out to be a more difficult task than I'd anticipated.

So say I record my workout session with a pen and paper something like this:

Monday

 1. jogging | 1.5 miles
 2. plank   | 3 minutes
 3. lift_A  | 5 pounds  | 15 reps
 4. lift_A  | 5 pounds  | 15 reps
 5. lift_A  | 6 pounds  | 17 reps

Tuesday

 1. plank    | 3 minutes | 3 pounds
 2. wall sit | 2 minutes
 3. jogging  | 1.6 miles
 4. lift_B   | 10 pounds | 2 reps

I need to keep track of the date of each series of exercises as well as the order I do them in. I had trouble figuring out how best to store the three "types" of activities (distance, duration w/ optional weight, weight+reps) and how to manage their recorded order in a day's workout.

How can I design a database (with or without an entity-relationship diagram, which would be nice) to help me record such information? What is the most logical/intuitive approach? Is a relational database even an appropriate approach at all?

Well, there's a simple 1 table solution right here:

| exercise_type (not null) | amount (not null) | other_field (null) | date (not null) |

To get the exercises for a particular date, you'd run a simple select statement (psuedo code):

select * from exercises where date in between (start, finish)

And you can refine to particular exercise types by adding:

and exercise_type = 'plank'


Accidental Turn On of Replication We had a log file that wouldn't stop growing even though we do backups.  Turns out that replication had been turned on for that database.  None of us DBAs did that (at least as far as we know).  So, two questions.

Can you tell after the fact who or what turned on replication.  (We noticed that when we turned off replication there were no entries in the log.)
What operations can turn on replication WITHOUT the executioner (there's a better word for that) of the operation to know that they are turning on replication.

Thanks in advance.  We have fixed the problem but want to make sure it doesn't repeat itself.

As for question 1, I don't think there's any way to know who enabled replication once you've torn it down. Even if it was still up, it'd be difficult (there is a login_name column in syssubscriptions that you might be able to use, for instance).

Speaking to point the second, the documentation for sp_addpublication says you need either sysadmin or db_owner to enable replication.


How do I handle a database for a form who's structure will likely change in the future? I need to build a database to record form data from a custom tool. This form consists of a fairly basic set of checkbox/radio button fields so the data itself doesn't present much of a problem.

User Table:

userID
firstName
lastName
phone
email

Form Table currently contains:

userID
question1
question2
question3

I'm struggling with the fact that, due to the marketing nature of this tool, the form structure and fields are almost guaranteed to change sometime in the future. There will likely be fields added/subtracted in the future. Any changes in the future are completely unknown now but could become something to the extent of:

Form Table could change to:

userID
question1
question3
questionA
questionB

Where question2 was removed and two completely new fields were added.

Should each "version" of the form have its own table or just add columns to the existing table as the form changes? Are there better ways to handle this?

My approach would be to make the table with a VARCHAR(MAX) field for the questions, and another field for the answers, and just dump each into those fields. That table can have date and version information too to make things easier. Everything should be structured within the varchar field, so it can be parsed out later.

A second approach that a co-worker of mine took was to have a table of just questions, and then a table for the form that links the questions to a certain form. Then have a table for responses, which would only link a person's response to a question.

So the forms table would have a series of records that link form 127 to questions 5,3,24,58. The response table would link person 626's responses to those questions, with a date. The worst part of it would be having to make cross joins just to see a single person's responses to a single form, but that's what views were made for.


Load big file in MySQL table I've read some posts related to issues similar to this, but without success, so I'm trying to post my specific problem.

I have to load a big CSV file (25Gb) into a MySQL table. I'm doing this with this environment:

MacOS Mojave 10.14.3.
16 GB DDR3 RAM Memory.
75 GB free space in my hard disk.
MySQL 5.7 for MacOS 10.14 with default configuration.

At first I tried with this:

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50));
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;
CREATE INDEX index_3 ON mytable (field3);

But my computer run out of memory and free hard disk space and the process crashed after more than 30 hours!!

I tried this with the same results:

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50));
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;
CREATE INDEX index_3 ON mytable (field3);

Finally, after some investigation I tried with this MySQL configuration file:

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
secure_file_priv="/usr/local/mysql/data/"
innodb_buffer_pool_size = 6G
innodb_log_file_size = 4G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity_max = 2000
innodb_io_capacity = 1000
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

And this code (using MyISAM engine):

CREATE DATABASE mydatabase CHARACTER SET utf8;
CREATE TABLE mytable (field1 TEXT, field2 INTEGER, field3 VARCHAR(50)) ENGINE = MYISAM;
CREATE INDEX index_3 ON mytable (field3);
LOAD DATA INFILE 'hugefile.csv' IGNORE INTO TABLE mytable CHARACTER SET utf8;

The process has a much better performance, it doesn't crash but it takes 4 hours to complete.

The issue is that I've asked one of my coworkers to try this with the exact same process, the same environment and the same configuration (the only difference is that he has 150GB of free hard disk space) and the process takes only 1 hour!

So, my doubt is:

What could be the difference between his Mac and mine to have such a different performance? What could I check?
Is there anything I could change in MySQL configuration to improve the performance of this process?
Is it normal that the process requires all of my free hard disk space? During the process I monitor the free space and it keeps decreasing from 75GB to only 500MB (aprox), when I start getting memory warnings.

EDIT to add answers to Rick's questions:

Both of us have SSDs.
I haven't set key_buffer_size, so I guess it has its default value (8388608 according to the documentation of MySQL 5.7).
That TEXT column has all of its values between 5 and 12 characters
Things go sour during create index: with load+create index the load command takes 10 minutes approximately and the create index takes hours; with create index+load, the create index is automatic and load takes hours (normal).

The data in a CSV file is encoded one way; the table in MySQL is encoded another way.  The difference could be 2x either way.  That is, the 25GB csv file might, for example, expand to 50GB, then have trouble adding on the indexes.

When MySQL runs out of disk space, it waits for you to free some space.  This might explain 40 hours.  Or it dies.

Does one of you have SSDs, the other have HDDs?

What was the value of key_buffer_size when using MyISAM?  Keep in mind that innodb_buffer_pool_size and key_buffer_size are competing for RAM, so you should be careful when switching between engines.

InnoDB wants a PRIMARY KEY; you don't have one.

How big is the TEXT column typically?  InnoDB stores them off-record in some situations.

When did things go sour?  During the LOAD?  Or during the CREATE INDEX?

When adding an index to a MyISAM table, it will either "repair via key buffer" or "repair by copying" (or something like that).  The clue is visible in SHOW PROCESSLIST during the CREATE INDEX.  If it is by "key_buffer", then 8M is much too small.  1G may be suitable for your machine.  If it is the other one, then I am surprised that it took so long.

Another trick (depending on the indexes you have or will have) is to first sort the data by the main key (field3? in your case) before doing the LOAD DATA.

If field1 is always 12 characters or fewer, then declare it VARCHAR(12).  (If it might get a bit bigger, pick some other limit.)  TEXT has some drawbacks compared to VARCHAR.  I am not sure whether it is relevant for this case, but it should not hurt.


Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB I need to design database to store information contained in millions of log files generated by devices on trial on network.

I have designed 5 different tables to store log file information. But, as the files are very large in number I may fall short of memory on maximum table size. Does anyone know maximum table size limit. I am using MYSQL database on Windows 7 (NTFS) All tables are of type InnoDB.

Thanks for your time

If you enable MySQL with innodb_file_per_table, each InnoDB table will have its own dedicated tablespace. The system tablespace file, bwetter known as ibdata1, will grow very slowly.

If innodb_file_per_table is disable, everything and its grandmother goes into ibdata1. That will grow quickly.

If you are not using innodb_file_per_table, you need to rearchitect InnoDB's infrastructure. This will give you a limit of 2TB per table instead of 2TB for all InnoDB. This is the case for ext3. On ext4, the table size can be up to 16TB.

Please see my past posts on how to do that

https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261 (Oct 29, 2010)
https://stackoverflow.com/questions/5248420/at-what-point-does-mysql-innodb-fine-tuning-become-a-requirement/5250306#5250306 (Mar 09, 2011)
https://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space/246111#246111 (Mar 11, 2011)
https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400  (Feb 04, 2011)
https://serverfault.com/questions/253059/mysql-innodb-optimisation/253233#253233 (Mar 29, 2011)


How do I completely remove MySQL from a 64-bit windows 7 machine? I'm new to MySQL and struggling to find a version and workbench which is stable on my 64 bit Windows 7 machine.

I've decided to attempt to completely remove MySQL from my machine and to restart the installation process from scratch.

However, after uninstalling all software linked with MySQL using the conventional control panel uninstaller, I see there are some MySQL windows services still remaining on my machine.

I can't see any obvious method to remove these services and they have since been causing me difficulties when trying to install different versions of MySQL.

Could anyone please advise how I can remove these services?

You can run the command sc delete [MySQL] where [MySQL] is the name of the service.


How do I create a CLR UDF with external name specifying namespace, class, and subclass? I am creating a CLR UDF using external name and second level nested class names seem not to be permitted. Is this the case? When using the following syntax and including a first level class name and associated method, I am able to bind a UDF to the method, but not if I included additional subclass levels.

    CREATE FUNCTION [dbo].[udf_test](@variable [nvarchar](MAX))
    RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [assembly].[ns.first.second].[method]
    GO

Thanks

As far as I can tell (I did some testing of my own), this is not supported.

The documentation hints at this:

  If the class has a namespace-qualified name that uses a period (.) to separate namespace parts, ...

Since there's no mention of a class-qualified name, I'm assuming SQL Server will traverse the hierarchy only by namespaces, with the last name being the class itself. There wasn't any way I could find to delimit the namespace and class name portions of the fully-qualified class name either.

What I wonder, though, is why your code is structured this way? The method you need to specify must be static, so nesting it within an extra class scope seems like you're (ab)using a class in place of a namespace.


Performance problem using mysql fulltext search I have a fulltext index on a column that contains an absolute file path (file_path), for a file indexing application.

There are 500 000 rows in the table. The MySQL version is 5.7.19.

Most, if not all rows begins with 'c:\data\' (ie: 'c:\data\clients\microsoft\private\emails.xls')

When I perform a search for 'microsoft private emails', ie;

MATCH(file_path) AGAINST('microsoft private emails' IN BOOLEAN MODE)`

everything is fine and I get the results in less than a second.

When I perform a search with the root of the file path, ie;

MATCH(file_path) AGAINST('"c:\\data\\clients"' IN BOOLEAN MODE)
-- note that it's an exact match with quotes ""

... the query runs forever (443 seconds last time I waited long enough), and the CPU hits 100% during the time of the query.

I use a LIMIT 0, 10 on both queries. The problem happens both with MyISAM and Innodb.

In fact, I have to execute $ service mysql restart to get things back to normal, otherwise the whole server becomes more-or-less responsive.

My question is:

Why is this happening and is there a way to fix it?

My theory, and I have very little knowledge about how the fulltext index works, is the fact that c:\data is in every row, the index fails and it's somehow doing a full table scan but with so many '\' in every row, it causes mysql to just loose it.

If I am right about the '\' thing, how is it possible to search for absolute file paths with mysql's full text index?

I hope I provided enough details.

Solved it by using MariaDB 10.2

MariaDB reduced the query execution time from 300 seconds to 30 seconds.

I then profiled and optimized the query down to 0.156sec.


Dropping rows based on the previous selection statmnet I have a database with following tables: temp_1, temp_2.... and cached_tbl. Inside cached_tbl I have column table_name and in corresponding rows I store all the temp table names I mentioned above. SO what I wanted to do is to create event scheduler that would drop temp tables that were created more than a day ago...For that purpose I am writing something like

SELECT (DROP)
    *
FROM
    information_schema.TABLES
WHERE
    table_schema = 'db' and CREATE_TIME < (NOW()-INTERVAL 24 HOUR)

But I also wanted to delete the rows containing just removed table names inside cached_tbl table... Please advise how I can do it...
In the regular programming I would just through all the selected table_names from previous script into the array and go through the loop comparing this table names to the table names in cached_tbls... But I have no idea how implement it in mysql

To get a dynamically formed SQL statement you need to get it into a user variable, @drop, and then execute this:

SELECT CONCAT('DROP TABLE ',
              GROUP_CONCAT(CONCAT(TABLE_SCHEMA,'.',TABLE_NAME))) INTO @drop
FROM information_schema.TABLES
WHERE  table_schema = 'db' AND
       table_name like 'temp%' AND
       CREATE_TIME < (NOW()-INTERVAL 24 HOUR);
PREPARE dropstmt FROM @drop;
EXECUTE dropstmt;
DEALLOCATE PREPARE dropstmt;

To clean up cached_tbl, delete where there isn't match on the join:

DELETE c FROM cached_tbl c
LEFT JOIN information_schema.TABLES t ON t.TABLE_NAME = c.table_name
WHERE t.TABLE_NAME IS NULL


With Postgres, can I make a rule that will not recurse? The rule is already in place, and I'm hoping to fix the rule itself rather than change the whole process on how this works due to some bureaucracy I'd rather not deal with. Or in other words, I know there are other ways to achieve my goal, but I specifically want to know if it can be done somehow with a rule.
The current rule is that when an update occurs, another update fires to concatenate a few fields together. That update performed by the rule seems to call the rule again and generates an "infinite recursion" error.
Rule example:
ON UPDATE TO people DO
UPDATE people
SET "fullname" = concat(new."firstName", ' ', new."lastName")
WHERE people.id = new.id

Is there any way to tell the update that the rule does to not then call the rule again? Or to flag the rule with some sort of runOnce? It's my understanding rules alter the query, so I can't do something simple like AND people.fullname != new.fullname

If fullname column is never altered manually (i.e. it must match firstname and lastname values always) then simply use generated column. It will be (re)calculated automatically during insert/update:
ALTER TABLE people
    DROP COLUMN fullName,
    ADD COLUMN fullName VARCHAR(200) GENERATED ALWAYS AS (firstName || ' ' || lastName) STORED;

DEMO


Resizing and reclaiming free space on Tablespaces I would like to reclaim some free space from our tablespaces. I can see that UNDOTBS1 and USERS for example have 30GB and 10GB free.

these are the tablespaces:

| tablespace    | used mb   | free mb   | total mb  | pct. free     |
|------------   |---------  |---------  |---------- |-----------    |
| SYSAUX        | 3268      | 152       | 3420      | 4             |
| UNDOTBS1      | 991       | 31777     | 32768     | 97            |
| USERS         | 186866    | 10766     | 197632    | 5             |
| SYSTEM        | 3360      | 30        | 3390      | 1             |
| INDEXES       | 32920     | 2920      | 35840     | 8             |

I could see on ASKTOM, and other questions that I need to:

PURGE TABLESPACE MyTs
aLTER TABLESPACE MyTs COALESCE

and then:

ALTER DATABASE DATAFILE 'Datafile_here' RESIZE 3g

But I would like to know how much can I reclaim. when i try to redize UNDOTBS1 for example, it says I cannot trim space that is used. but it has more than 30GB free.

On SQL Server we can simply " shrink file" adn it shrinks the free space.

I know I am late for the party, but this script was gold for me at the time I found it:
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/

It will tell you smallest and current size of each datafile and it will tell you how much
storage you may gain by shrinking a datafile(s combined).
Once you find the smallest possible size of a datafile, you may simply shrink it with:
ALTER DATABASE DATAFILE 'Datafile_name_bla_bla' RESIZE 20M;


You can use the SYS_CONNECT_BY_PATH hierarchical function to generate a list of parent IDs from the tree root to the current record, and the level pseudo column to count the number of members in the list (+1).  In the next stage, the regexp_substr function extracts the Nth path element from the ath for display as the Nth parent_Id:

var nth number
exec :nth := 2;

with t1(id, lev, parent_id) as (
select 1, 1, null from dual union all
select 2, 1, null from dual union all
select 3, 2, 1    from dual union all
select 4, 2, 1    from dual union all
select 5, 2, 2    from dual union all
select 6, 3, 5    from dual union all
select 7, 3, 3    from dual union all
select 8, 4, 7    from dual union all
select 9, 4, 6    from dual
), t2 as (
select id, lev, parent_id, level
     , ltrim(SYS_CONNECT_BY_PATH(parent_id,','),',') path
  from t1
connect by prior id = parent_id
start with parent_id is null
)
select id, lev, parent_id, path
     , :nth n
     , to_number(regexp_substr(path,'[^,]+',1,:nth)) nth
  from t2
  order by id;

        ID        LEV  PARENT_ID PATH                N        NTH
---------- ---------- ---------- ---------- ---------- ----------
         1          1                                2
         2          1                                2
         3          2          1 1                   2
         4          2          1 1                   2
         5          2          2 2                   2
         6          3          5 2,5                 2          5
         7          3          3 1,3                 2          3
         8          4          7 1,3,7               2          3
         9          4          6 2,5,6               2          5


Automatically import .bak file on a daily basis I have a piece of industrial equipment running SQL Server with a database named, let's say, "primary". The equipment creates on a daily basis a .bak file (of the full database) and it transfers it to a server running SQL Server. On the server, I need to have an ARCHIVE of "primary".
My idea is to create a daily job to import only the new records from the .bak file, but I have no idea where to start from.
I have looked online for "importing .bak into SQL Server" but all I find is about the RESTORE command which I'm afraid doesn't work in my case.
Can anyone point me in the right direction?
UPDATE 1 - According to the warranty given to us by the manufacturer of the equipment, we can't interact in any way with the embedded SQL Server. The only way to access the data is via the .bak file.

backup files (BAK files) can't be read to take only some records inside. You can only restore them. Then you can import from the restored database what you like.


What happens when InnoDB hits its tablespace autoextend max? The latest version of High Performance MySQL (3rd ed.) recommends that one:

  ...place an upper limit on the tablespace's size to keep it from growing
  very large, because once it grows, it doesn't shrink.

They give the example of:

...ibdata3:1G:autoextend:max:2G

But it isn't clear to me what happens if your dataset keeps growing and hits this maximum. Unfortunately the MySQL docs aren't much help either.

Can someone shed some light on this? It seems like this advice is kind of dangerous on the face of it.

Thanks!

IMHO It is dangerous to place a max on an autoextend. A transaction's worth of data could be lost simply because there is no room to post any new data. I have seen this happen recently with a monitoring system whose ibdata2 has a max of 1024G imposed on it. All I did to fix it was bump up the max to 16384G (That was a band-aid until my employer will let me clean up its InnoDB layout [politics with outside vendors getting in the way])

My advice to you is to use the default path for the system tablespace file (a.k.a. ibdata1). The reason? This is the default innodb path

innodb_data_file_path = ibdata1:10M:autoextend

What does ibdata1 contain? Four(4) classes of info

Table Data Pages
Table Index Pages
Table MetaData (List of TablespaceIDs + other related info)
MVCC (to support Transaction Isolation and ACID compliance)

With innodb_file_per_table disabled by default, everything InnoDB and its grandmother lands in ibdata1. You can segregate the Table Data and Indexes Pages from ibdata1 by enabling innodb_file_per_table in /etc/my.cnf

[mysqld]
innodb_file_per_table

There is a way to shrink ibdata1 by means of reloading all MySQL Data. I have written a posts about this:

How do you remove fragmentation from InnoDB tables?
My original post in StackOverflow

Once you have performed this InnoDB Infrastructure Cleanup, you should never change the innodb_data_file_path. There is no need to create ibdata2, ibdata3, and so forth, when all Table Data and Index Pages live outside of ibdata1.


Selecting, ranking and applying a point score based on rank I am writing an MVP system based on votes from users. The table will look like

vote     | game | year
--------- ------ ------
player 1 | 2    | 2015
player 1 | 2    | 2015
player 2 | 2    | 2015
player 2 | 2    | 2015
player 2 | 2    | 2015
player 3 | 2    | 2015
player 1 | 2    | 2015
player 3 | 2    | 2015
player 4 | 2    | 2015

An entry here equates to a vote. So out of this we would group and get a count of

Player 1 = 3
Player 2 = 3
Player 3 = 2
Player 4 = 1

The MVP system is a "3-2-1" score system. So points are awarded to the players in reverse order. So it should only apply winning scores to the top 3 positions. i.e.

1st gets 3 points
2nd gets 2 points
3rd gets 1 point

This gets really complicated when there is a tie but top 3 should get MVP points.

In the case above the point spread would be

Player 1 = 2.5 points
Player 2 = 2.5 points
Player 3 = 1 point

This is worked out because the top 2 players are effectively 1st and 2nd, there is no second so the next person gets 1 point for placing 3rd.

Effectively (points = points_available / players_in_rank)

however that equation is wrong. It gets extra complicated if we have a tie for 3rd, where you could possibly end up with lots of people in 3rd spot and therefore they get 1 point divided by them.

It is really complicated when you have ties in 1st or second. I have worked around this poorly in PHP code but would like to be able to do this in SQL and do away with PHP.

Just wondering if anyone has any simple methods for this?

These scores points are then later added to an overall standings table

Regards
Dan

Sample data:

CREATE TABLE v
    (`vote` varchar(8), `game` int, `year` int)
;

INSERT INTO v
    (`vote`, `game`, `year`)
VALUES
    ('player 1', 2, 2015),
    ('player 1', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 3', 2, 2015),
    ('player 1', 2, 2015),
    ('player 3', 2, 2015),
    ('player 4', 2, 2015)
;

Query:

SELECT
vote,
no_votes,
rank,
3 - rank + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points
FROM (

    SELECT
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT
        vote,
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC

) q

Result:

|     VOTE | NO_VOTES | RANK | POINTS |
|----------|----------|------|--------|
| player 1 |        3 |    1 |    2.5 |
| player 2 |        3 |    1 |    2.5 |
| player 3 |        2 |    2 |      2 |
| player 4 |        1 |    3 |      1 |

see it working live in an sqlfiddle

Explanation:

This

        SELECT
        vote,
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

is simple enough, I hope :)

This

, (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery

is just a subquery to initialize some variables.

Here

    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes

in the first row @prevNoVotes holds the value of the previous row (or the value from the initialization subquery). @rc1, @rc2 and @rc3 are just rank counters. They count for each rank, how many people are on the same rank.
In the last row, the value of the current row is assigned to the variable. When the next row is processed, the variable will hold the value of the previous row in the first line of this part of the code.

And finally in the most outer part of the query we calculate the points distribution.

Feel free to ask any questions :)

UPDATE:

Added this table:

CREATE TABLE points
    (`r` int, `p` int)
;

INSERT INTO points
    (`r`, `p`)
VALUES
    (1, 3),
    (2, 2),
    (3, 1)
;

Added more logic :)

SELECT
vote,
no_votes,
rank,
@rc := @rc + 1 AS rowcounter,
((SELECT p FROM points WHERE r = IF(rank = @prevRank, rank, @rc)) - 1) + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points,
@prevRank := rank
FROM (
    SELECT
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT
        vote,
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC
)q
, (SELECT @rc := 0, @prevRank := 1) row_counter_init
ORDER BY rank

see it working live in this sqlfiddle


What should we do when same column fields occurring multiple time in many tables? I have more than 20 tables in my database. There are some columns which occur in almost all of the tables. In that case what should I do? Do I put all those columns in every table or is there a better way to solve this problem?

Country Table Example

CountryID, CountryName, AddedBy, AddedTime, LastUpdatedBy, LastUpdateDTime, DeletedBy, DeletedTime, DeleteFlag

As we can see in the table structure above there are 9 columns. Except for CountryID and CountrynName all occur in most tables. I don't want to keep above repeating columns in all tables. So if there is any solution then please let me know.

You can achive this by creating History table. In that table you can store all changes from those tables. You have to use tiggers to populate data in History table. Take into account that if the history table stores all changes you need engine to populate it.
Another approach is to have History table for each table that you want to observe. In your case Countries_History.
You have to decide when you make design what amount of such info to keep.
Of course you can just remove all that columns if you don't care about the info they store.


How do we determine data_length + index_length as database size in MySQL I am calculating my InnoDB database's size with the following syntax:
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

As per MySQL documentation, data_length column gives the clustered index length in pages. But I have not created an index on that table. How to calculate exact total space, used space and free space for a table?

This won't be accurate for InnoDB tables. The size on disk is actually bigger than that reported via your query, take a look at :

select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES

file_size  (which is the logical file size on disk) and
allocated_size (which is space allocated for this file and can be significantly smaller)
more details here : https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/


Is it bad to create many mysql temporary tables simultaneously? I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better (extremely more efficient and faster than PHP arrays; obviously due to their mechanism).

My tentative solution is to form a temporary table upon each request (i.e. connection) to import and process the data. However, I am not sure if there is a drawback for creating many temporary tables simultaneously?

Can this make problem for the mysql server? Or I can use it as an alternative to PHP arrays in numerous simultaneous requests?

You want to prevent making temp tables as much as possible : Prevent copying to temp table (sql)

They do not work well in MySQL Replication : How are binlogs updated for MySQL temporary tables?

Since temp tables are a fact of life in the DB world, you may have to make some unorthodox changes to accommodate their existence.

Normally, mysqld has the habit of placing tmp tables in /tmp or wherever tmpdir is configured. That's usually on some poor unsuspecting disk.

An interesting alternative would be to setup a RAM disk and reconfigure tmpdir to use it

STEP 01 : Create a Mount Point for a RAM Disk

mkdir /var/tmpfs

STEP 02 : Add the RAM disk to /etc/fstab (16GB)

echo "none   /var/tmpfs              tmpfs   defaults,size=16g        1 2" >> /etc/fstab

STEP 03 : Add this line to /etc/my.cnf

[mysqld]
tmpdir=/var/tmpfs

STEP 04 : Enable the RAM Disk

You can do one of the following:

Just reboot the DB Server
mount -t tmpfs -o size=16g none /var/tmpfs

Before you do this, make sure you have enough RAM

Give it a Try !!!


Pass parameters to a procedure with SQLcmd I use this SQL command:

 sqlcmd -v first_date="30/09/2015" last_date="15/09/2016"

And I would like to pass that parameters to a stored procedure:

ALTER PROCEDURE [dbo].[_testProcedure]
    WITH EXECUTE AS CALLER
AS
BEGIN

    DECLARE @F_FIRST_DATE_DT VARCHAR(50)
    DECLARE @F_LAST_DATE_DT VARCHAR(50)

    SET @F_FIRST_DATE_DT = N'$(first_date)'
    SET @F_LAST_DATE_DT = N'$(last_date)'

But it return me an error:

  Incorrect syntax near 'first_date'

What is the correct way to pass variables to stored procedure?

There is no special way to pass SQLCMD variables to a Stored Procedure. Stored Procedures are encapsulated code and cannot be passed SQLCMD variables as you are attempting to do in the code posted in the Question.

Stored Procedures take input parameters to pass in values. And those input parameters should be of the correct datatype for the data being passed in; you do not need to accept VARCHAR for date values simply because SQLCMD variables do not specify their type.

CREATE PROCEDURE [dbo].[_testProcedure]
    WITH EXECUTE AS CALLER
(
  @FirstDate DATETIME,
  @LastDate DATETIME
)
AS
SET NOCOUNT ON;

SELECT DATEDIFF(DAY, @FirstDate, @LastDate) AS [NumberOfDays];

Then you call it in one of the following ways:

EXEC [dbo].[_testProcedure] first_date, last_date;
EXEC [dbo].[_testProcedure] @FirstDate = first_date, @LastDate = last_date;

Now, IF you are executing the above T-SQL from SQLCMD, then you can pass in SQLCMD variable into that T-SQL, which is the current query:

-Q "EXEC [dbo].[_testProcedure] $(first_date), $(last_date);"

or:

-Q "EXEC [dbo].[_testProcedure] @FirstDate = $(first_date), @LastDate = $(last_date);"

Also, you need to pass in SQLCMD variable values using single-quotes, not double quotes. Try the following to see why:

SQLCMD -Q "PRINT $(FirstDate);" -v FirstDate='12/05/2006'

Returns:

12/05/2006

But:

SQLCMD -Q "PRINT $(FirstDate);" -v FirstDate="12/05/2006"

Returns:

0


How to return last used record? I have simple table called ips with 2 field ip and address, which schema is following

CREATE TABLE IF NOT EXISTS `ips` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ips_address_unique` (`address`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2;

in table there are some IP inserted

INSERT INTO `ips` (`id`, `address`) VALUES (1, '123.123.123.123');
INSERT INTO `ips` (`id`, `address`) VALUES (1, '124.124.124.124');
INSERT INTO `ips` (`id`, `address`) VALUES (1, '125.125.125.125');

How to create query that return 123.123.123.123 first time, 124.124.124.124 second time, 125.125.125.125 third time, 123.123.123.123 fourth time and so on...?

The only one solution that comes to my mind is idea of storing id of last returned IP in memory and then query for ip with id greater than stored. Anyway, I'm going to repeat that query multiple times in a second. It will be inefficient.

It looks like you are looking for SEQUENCE, simple usage:

CREATE SEQUENCE ip_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

and then in select:

where id = ip_seq.nextval % ip_table_size


Slave Missing Data From Master, Manual Insertion Issues? My transaction logs became corrupted and I had to skip 50 insert records from master. I've exported the data and am planning to update the slaves manually, with the slave stopped. The auto-increment value on the slave stayed in sync with master to my surprise so I think I can just re-populate the missing rows. Are there any potential issues with this plan?

So for example on master I run:

SELECT count(*) FROM `users` WHERE userid between 20 and 69;

and I get back 50. When I run it on slave I get "Empty set". So I did:

SELECT * FROM `users` WHERE userid between 20 and 69;

Then constructed the resulting CSV from PHPMyAdmin into an insert query:

insert ignore into users (userid, name, passwordhash, active)
values
(....,...,...,...)

Prior to insert on the slaves I will run:

stop slave;

and then after

start slave;

I can't think of issues with this may cause but I don't have a testing replication environment so can't test this thoroughly.

My recommendations would be to invest some time in learning how to use the pt-table-checksum and pt-table-sync tools from the Percona toolkit. This takes much of the work out of your hands in a much more reliable way.

https://www.percona.com/downloads/percona-toolkit/LATEST/


Using SQL Server native backup and Veritas Backup Exec to backup the database and transaction log on the same database I have a customer who use the Microsoft SQL Server native backup and Veritas Backup Exec to backup the database and transaction log on the same database.
The native backup runs at 5 AM while the Veritas Backup Exec runs at 12 AM and 12 PM. Can the same database be backup by two different backup software and restored correctly?
Sorry if I have missed out some facts.

i suggest you to configure one of the two backups in copy only:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-ver15
It is not strictly disallowed to use two different backup solutions but it exists only one single chain of backup so restore would be a nightmare.
Better to do full and ltog backup with one solution and use the other solution for full backups with copy only option.


How to list Extended Properties for all databases I am currently working on task where we use Central Management Server  and Server Group for easy access to our SQL server boxes.

I am trying to query the extended properties for all databases on multiple SQL Servers as can be seen below:

  Is there a way that can be done using TSQL script?

I am using the code snippet from this social.msdn post that is pretty close to what I want to achieve, but my powershell skills are zero to none: Please find the script below :

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "computer\instance"

$dbs = $s.Databases

$query = @"
SELECT objtype,
        objname,
        name,
        value
FROM fn_listextendedproperty(default, default, default, default, default, default, default);
"@

foreach($db in $dbs) {
    $ds = $db.ExecuteWithResults($query)

    $table = $ds.Tables[0];

    foreach($row in $table) {

        Write-Host $row.Item("objtype") `
            $row.Item("objname") `
            $row.Item("name") `
            $row.Item("value");
    }
}

To add to the complication I am using the above Powershell script  within in Jenkins.

You could run the command in a cursor over the databases:

DECLARE @DbName nvarchar(500), @SQL nvarchar(1000);
DECLARE C CURSOR LOCAL FAST_FORWARD FOR
SELECT QUOTENAME(name) from sys.databases order by database_id;
OPEN C;
FETCH NEXT FROM C INTO @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL =
    N'
    USE '+@DbName+'
    SELECT '''+@DbName+''' as databasename ,name, value
    FROM fn_listextendedproperty(default, default, default, default, default, default, default)'
    EXEC SP_EXECUTESQL @SQL;

    FETCH NEXT FROM C INTO @DbName;
END
CLOSE C;
DEALLOCATE C;


Need assistance shrinking MDF File Verbose logging was accidentally left on for one of our database ballooning our database MDF file from roughly 90GB to 192GB.

We've disabled verbose logging, truncated the logging table but now we want to shrink the MDF file to reclaim disk space as the drive the MDF file is on is only around 200GB. I don't have the luxury of increasing its size. Our disk space monitoring tool is alerting me that drive space is less than 8% over and over and over.

Given this is the first instance this has ever happened, we don't expect to ever need that extra 102GB of disk space again. We'd like to reclaim it.

I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size. I've also tried using DBCC SHRINKFILE and DBCC ShrinkDatabase with NoTruncate and TruncateOnly. No matter what I do the file doesn't seem to budge.

One potential issue is that somehow the database initial size is 188GB. Deleting and re-creating the database isn't a very practical option for us so how can I shrink the MDF file and reclaim our disk space?

Running dbcc shrinkfile('My_Data'), I get the following results:

DBID: 5
Field: 1
CurrentSize:24030464
MinimumSize: 128
UsedPages: 17770472
EstimatedPages: 17770472

The database MDF file is still the same size (192,243,712 KB).

The table has a clustered index:

Name:TRACES
ObjectID: 394588594
Type: Clustered Index/b-tree

The query:

SELECT
    name,
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

...for the database I am trying to shrink, returns:

Name: My_Data AvailableSpaceInMB: 48693.375000
Name: My_Log  AvailableSpaceInMB: 67028.437500

I've also tried shrinking in small chunks using a PowerShell script that shrinks in 20MB increments. That also doesn't change the size of the MDF file.

I've tried dbcc shrinkfile(My_Data) without any other options from SQL Management Studio. It runs for a long time, finishes, and the MDF file is still the same size. Specifying a target size, it runs for a good 30-40 minutes (finishes) and nothing changes file size wise.

I ran this: DBCC SHRINKFILE (N'My_Data' , 139045) and get the following results:

DbId: 5
Field: 1
CurrentSize: 24030464
MinimumSize: 128
UsedPages: 17808744
EstimatedPages: 17808744

I've tried to use the GUI to shrink the database and the files but it doesn't actually change in size.

You said that you truncated large table, in such case you might not be able to shrink "immediately". From Dropping and Rebuilding Large Objects

  When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both autocommit and explicit transactions in a multiuser environment, and applies to large tables and indexes that use more than 128 extents.

It also says that

  Because the physical phase occurs after a transaction commits, the storage space of the table or index might still appear as unavailable. If this space is required for the database to grow before the physical phase is completed, the Database Engine tries to recover space from allocation units marked for deallocation. To find the space currently used by these allocation units, use the sys.allocation_units catalog view.

Other reason why you would not be able to shrink immediately because records are marked ghosted. And shrink will actually give you space backup after background Ghost Cleanup operation has completed, some more information about Ghost Cleanup Task

Initial size as such is not initial size actually, its the current size. You can shrink below initial size. What I suggest you is wait for some time till background operation finishes. Use dbcc shrinkfile tsql command to shrink the files and shrink in small chunks.

Shrinking is single threaded and takes time.

PS: If you are not aware, please note that shrinking causes massive logical fragmentation so don't forget to rebuild fragmented indexes after shrinking is done


How to use a select pivot query to update two different tables I would like to update data in two different table however based on select query using pivot I'm unsure how to go about to do the update query.
See query I have below:
UPDATE [FuelManagementSystem].[dbo].[OpenCloseTank] SET OpenDip = 0.00, CloseDip = 0.00 WHERE WhenDate = GETDATE() AND TankID = 'BW-TK-DSL1'
UPDATE [FuelManagementSystem].[dbo].[OpenCloseNozzle] SET OpenReading = 0.00, CloseReading = 0.00 WHERE [Date] = GETDATE() AND TankID = 'BW-TK-DSL1' AND NozzleNo = 1
UPDATE [FuelManagementSystem].[dbo].[OpenCloseNozzle] SET OpenReading = 0.00, CloseReading = 0.00 WHERE [Date] = GETDATE() AND TankID = 'BW-TK-DSL1' AND NozzleNo = 2

SELECT QUERY:
SELECT
    [Date], T.TankID, [OpenDip], [CloseDip], [ORNozzle1], [CRNozzle1], [ORNozzle2], [CRNozzle2]
FROM [FuelManagementSystem].[dbo].[OpenCloseTank] T
INNER JOIN
(
    SELECT [TankID], [Date], SUM([1]) AS [ORNozzle1], SUM([2]) AS [ORNozzle2]
    FROM [FuelManagementSystem].[dbo].[OpenCloseNozzle]
    PIVOT
    (
        MAX (OpenReading)
        FOR [NozzleNo] IN ([1], [2])
    ) AS P
    WHERE CONVERT(DATE,[Date])='2019-11-09'
    GROUP BY [TankID], [Date]
) AS A
    ON [T].[TankID] = [A].[TankID]
INNER JOIN
(
    SELECT [TankID], SUM([1]) AS [CRNozzle1], SUM([2]) AS [CRNozzle2]
    FROM [FuelManagementSystem].[dbo].[OpenCloseNozzle]
    PIVOT
    (
        MAX (CloseReading)
        FOR [NozzleNo] IN ([1], [2])
    ) AS P
    WHERE CONVERT(DATE, [Date])='2019-11-09'
    GROUP BY [TankID]
) AS B
    ON [T].[TankID] = [B].[TankID]
WHERE CONVERT(DATE, Whendate) = '2019-11-09'

QUERY RESULTS:

TABLE DATA:

I'm not 100% sure to understand your question but if you are looking at doing and UPDATE from a SELECT, then you may want to look at this.


Detect data spikes (loops) in MySQL database and ignore these i must create some statistics out of a WWW access log database, but i found out that there are some redirect loops after a login if the user has disabled cookies. So there are 10-1000 mostly identical rows like the following:

...
/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00
/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:00
/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00
/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:01
...

Is there a way to detect unusal count grouped by the ip/url in an interval? I think i need to select all counts for each minute an detect a spike and ignore that... Does anyone had the same difficulties?

Acoording to your pattern, you could do something like this (change your column names and url pattern accordingly):

mysql> SET @max_redirects := 10;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*) as `# redirects`, ip, min(url) as url,
              time_event - INTERVAL SECOND(time_event) SECOND AS minute
       FROM log WHERE url like '/login?redirect=%'
       GROUP BY ip, minute
       HAVING `# redirects` > @max_redirects;
+-------------+-----------------+----------------------+---------------------+
| # redirects | ip              | url                  | minute              |
+-------------+-----------------+----------------------+---------------------+
|          53 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:03:00 |
|          21 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:05:00 |
|         128 | 123.123.123.123 | /login?redirect=/app | 2014-01-01 10:00:00 |
+-------------+-----------------+----------------------+---------------------+
3 rows in set (0.01 sec)

Expect this to be slow on a tall table, so add an index on your timestamp column and filter by date to avoid huge sorts.


SQL Server :: how to move country code column to another table? I have 2 tables:

tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG).

Like this:
CountryName    TwoCharCountryCode ThreeCharCountryCode
Afghanistan    AF                 AFG
Aland Islands  AX                 ALA
Albania        AL                 ALB
Algeria        DZ                 DZA
American Samoa AS                 ASM
Andorra        AD                 AND
Angola         AO                 AGO

and

population_by_country_2020: 235 values which has the column Country_or_dependency (Afghanistan) but is missing the 2 and 3 CHAR country code.

Like this:
CountryName    TwoCharCountryCode ThreeCharCountryCode
Afghanistan    NULL               NULL
Albania        NULL               NULL
Algeria        NULL               NULL
American Samoa NULL               NULL
Andorra        NULL               NULL
Angola         NULL               NULL
Anguilla       NULL               NULL

...yeah, because they have different values they will never match perfectly but I can do those who miss by hands.
What I want to do overall is to move AF and AFG from tbl_Countries to population_by_country_2020 in the row 'Afghanistan'.

How to archive that?

Is this what you are looking for?
--demo setup
drop table if exists tbl_Countries;
drop table if exists population_by_country_2020;
go
CREATE TABLE tbl_Countries (
  CountryName VARCHAR(17),
  TwoCharCountryCode varchar(20),
  ThreeCharCountryCode VARCHAR(3)
);

INSERT INTO tbl_Countries
  (CountryName, TwoCharCountryCode, ThreeCharCountryCode)
VALUES
  ('Afghanistan', 'AF', 'AFG'),
  ('Aland Islands', 'AX', 'ALA'),
  ('Albania', 'AL', 'ALB'),
  ('Algeria', 'DZ', 'DZA'),
  ('American Samoa', 'AS', 'ASM'),
  ('Andorra', 'AD', 'AND'),
  ('Angola', 'AO', 'AGO');

CREATE TABLE population_by_country_2020 (
  CountryName VARCHAR(19),
  TwoCharCountryCode varchar(2),
  ThreeCharCountryCode VARCHAR(4)
);

INSERT INTO population_by_country_2020
  (CountryName, TwoCharCountryCode, ThreeCharCountryCode)
VALUES
  ('Afghanistan', NULL, NULL),
  ('Albania', NULL, NULL),
  ('Algeria', NULL, NULL),
  ('American Samoa', NULL, NULL),
  ('Andorra', NULL, NULL),
  ('Angola', NULL, NULL),
  ('Anguilla', NULL, NULL);

-- the solution
UPDATE p
SET p.TwoCharCountryCode = c.TwoCharCountryCode
    ,p.ThreeCharCountryCode = c.ThreeCharCountryCode
FROM population_by_country_2020 p
JOIN tbl_Countries c
    ON c.CountryName = p.CountryName

--verfy
SELECT *
FROM population_by_country_2020

|                |                    |                      |
|----------------|--------------------|----------------------|
| CountryName    | TwoCharCountryCode | ThreeCharCountryCode |
| Afghanistan    | AF                 | AFG                  |
| Albania        | AL                 | ALB                  |
| Algeria        | DZ                 | DZA                  |
| American Samoa | AS                 | ASM                  |
| Andorra        | AD                 | AND                  |
| Angola         | AO                 | AGO                  |
| Anguilla       | NULL               | NULL                 |


Grant to one; then revoke from all: does it work in Oracle SQL? Consider the following:

grant select on books to frank.
revoke select on books from public.

At the end of executing the above SQL script, will frank have 'select' access to the 'books' table?

Yes, frank will be able to perform a select on books. Basically Oracle will use the most permissive privilege, which is the one allowing selection.

Reference: Documentation (under roles).

Note that this is different from you granting privileges with admin option to UserB, who in turn grants privileges to UserC. In this case revoking privileges from UserB would automatically "cascade" revoke them from UserC.


Duplicate entry error for unique key that doesn't exist I have a model that had the following fields:

+----------+----------+
| username | password |
+----------+----------+
| AAA      | ***      |
| BBB      | ***      |
| CCC      | ***      |
+----------+----------+

But then I deleted the last row (CCC) and tried to insert a new one with the same username:

INSERT INTO users (username, password) SELECT 'CCC', '***'

But I got this error:

  SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'CCC' for key 'username_UNIQUE'

And if I query for all the existent rows, I get only two rows:

SELECT * FROM users

+----------+----------+
| username | password |
+----------+----------+
| AAA      | ***      |
| BBB      | ***      |
+----------+----------+

I suspect this has to do with the data not syncing to disk, since in the first days, when I first created the model using MySQL Workbench, it wouldn't appear on Adminer only until after a reboot, but I have no idea as to how to force a sync on the data to disk, or even if it's the issue.

I'm using MariaDB 5.5.39 on Fedora 20 64 bit on linux 3.17.4-200 as the DBMS and InnoDB as the DB storage engine.

Update:

It looks like this happens with all and every username I try, so 'askaksjk' will also trigger the same error, but if I insert a new row without using a SELECT statement, it adds the record correctly:

INSERT INTO users (username, password) VALUES ('CCC', '***')

Does it has anything to do with the fact that the SELECT already returns a record with the same username as the one I'm trying to add?

Your update is the correct way to insert data. Using
INSERT INTO ... SELECT ... requires a source table and is not meant to be used with static values.

Using INSERT INTO ... VALUES ... is for inserting static data, such as CCC, into the table. The two are not interchangable and will cause the issues you've been seeing.

You can check the documentation on INSERT INTO ... SELECT ... here and note that it only talks about using a table source.


How to find out what caused the database(DB2) in a locking state I know how to check the DB is in a lock state or not, but my question is how to find out which query caused the lock on table /db.

IS there any log file getting created if something unexpected happens to the database?

List the current database activities:

db2top -d [dbname]

This command will show you database locks:

db2pd -d [dbname] -lock wait


Update stats takes significantly longer with a 50% sample rates versus a full scan We have a home grown update stats job for large tables that essentially issues an UPDATE STATS command. Historically we had been defaulting to FULL SCAN, but recently we switched to SAMPLE 50 PERCENT. Oddly, the update stats command run times are drastically higher.

To give you an example, we have table 1 which has 6 statistics (3 index, 3 auto generated). Clustered index is 1.2 TB; NCI 1 is 2.7 GB; NCI 2 is 2.6 GB

The statistics were updated on the table 1 month ago with FULL SCAN and the command took 96 minutes. The statistics were updated last night with SAMPLE 50 PERCENT and the command took 593 minutes! Table row counts between the 2 runs were roughly the same.

I can see from sys.dm_db_stats_properties that the clustered index took all but 4 minutes of that time.  My question is, why would reducing the sample rate by 50% caused the command to run almost 5 times longer?

There wasn't any blocking occuring while the command ran (according to SQL Sentry) and there weren't any resource bottlenecks that jump at me (CPU < 40%, IO latency < 10 by-in-large).

One thing I'm wondering about is if parallelism is in play - with a full scan, can SQL use parallelism, but with a sample % it is single threaded?

We're running SQL 2012 SP2 CU7

When you run stat updates WITH FULLSCAN, it goes parallel.  I believe it has since SQL 2005.

From TechNet:

"Parallel statistics gathering for fullscan: For statistics gathered with fullscan, creation of a single statistics object can be done in parallel for both non-partitioned and partitioned tables."

Source: https://technet.microsoft.com/en-us/library/cc966419.aspx

When you don't use FULLSCAN the engine leverages the TABLESAMPLE option which is a parallelism inhibitor.  In SQL 2016, sampled stat updates can also go parallel.

Source: Parallel Statistics Update


How can I see what query is emitted by the query parser? Consider this snippet:

create table tbl_1(name text);
create table tbl_2(name text);

create function my_insert(_name text) returns text
    language sql
as $$
   insert into tbl_2(name)
   values (_name);
   select _name;
$$;

explain analyse
with x as (select name from tbl_1)
   ,y as (select my_insert(x.name) from x)
select 0
;

returns

Result  (cost=12.72..12.72 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
  CTE x
    ->  Seq Scan on tbl_1  (cost=0.00..12.72 rows=1360 width=32) (never executed)
Planning Time: 0.053 ms
Execution Time: 0.013 ms

Now I can understand why the clause x isn't being executed (and also y of course), but the plan doesn't mention anything about what happened to clause y.

This leads me to suspect that the query parser is emitting a query that doesn't contain the clause y.

How can I confirm this? How can I see what the parser is emitting here? Alternatively, how can see what query the planner evaluated?

This leads me to suspect that the query parser is emitting a query
  that doesn't contain the clause y.

The query parser emits query trees and the query planner emits plans. Both can be shown in more detail with the debug_print_parse and debug_print_plan settings.
From the documentation:

  Now what is a query tree? It is an internal representation of an SQL
  statement where the single parts that it is built from are stored
  separately. These query trees can be shown in the server log if you
  set the configuration parameters debug_print_parse,
  debug_print_rewritten, or debug_print_plan

In the case of the query in the question, y is apparently eliminated. It's the work of the planner, not of the parser.
The log output with debug_print_parse would show it, because that's before it gets eliminated, contrary to the output with debug_print_plan, in which it should not be present.

However, the result of EXPLAIN with no mention of the evaluation of y should be a sufficient proof that the planner has eliminated it.


is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication? I have a table that has an clustered unique index and a non clustered primary key with the same structure as the index.

IF OBJECT_ID('[dbo].[tblBAccountHolder]') IS NOT NULL
DROP TABLE [dbo].[tblBAccountHolder]
GO
CREATE TABLE [dbo].[tblBAccountHolder] (
[lngParticipantID]  INT                              NOT NULL,
[sdtmCreated]       SMALLDATETIME                    NOT NULL,
[strUsername]       VARCHAR(20)                          NULL,
[strPassword]       VARCHAR(20)                          NULL,
[tsRowVersion]      TIMESTAMP                        NOT NULL,
CONSTRAINT   [PK_tblAccountHolder]
PRIMARY KEY NONCLUSTERED ([lngParticipantID] asc),

CONSTRAINT   [IX_tblBAccountHolder__lngParticipantID]
UNIQUE CLUSTERED    ([lngParticipantID] asc)
WITH FILLFACTOR = 100)

Only one column as you can see on the definition:

 CREATE  UNIQUE CLUSTERED INDEX IX_tblBAccountHolder__lngParticipantID
 ON [dbo].[tblBAccountHolder] (  [lngParticipantID] ASC  )

I would like to drop the unique index, and alter the primary key so that it is CLUSTERED.
I will keep the same primary key, just change it from non clustered to clustered.

This table is part of transaction replication
I would get this done on the subscriber database only.Not in the publisher.

It is a table with over 9,293,193 rows.

  Will I mess up the replication?

the problem is that I have to drop the primary key constraint and re-create it as clustered.

this is what I would like to get done in the subscriber database:

drop INDEX IX_tblBAccountHolder__lngParticipantID
        ON [dbo].[tblBAccountHolder]
GO

ALTER TABLE [dbo].[tblBAccountHolder]
       drop CONSTRAINT [PK_tblAccountHolder]
GO

 ALTER TABLE [dbo].[tblBAccountHolder]
   ADD  CONSTRAINT [PK_tblAccountHolder]
 PRIMARY KEY CLUSTERED (  [lngParticipantID] ASC  )
  WITH (  PAD_INDEX = OFF,
          FILLFACTOR = 95,
          SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF ,
          STATISTICS_NORECOMPUTE = OFF , ONLINE = ON ,
          ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON  ) ON [PRIMARY ]
GO

No, It is not possible.

when the table is involved in replication it does not allow you to drop the primary key.

below is an example as how I have changed the primary key of a replicated table:

table is dbo.CategoryImportMap, database is Product_Staging.

---------------------------------------------------------------------------------------------------
-- remove the table from the replication
---------------------------------------------------------------------------------------------------

USE [Product_Staging]
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

exec sp_dropsubscription @publication = N'Product_Staging', @article = N'CategoryImportMap', @subscriber = N'all', @destination_db = N'all'
GO
exec sp_droparticle @publication = N'Product_Staging',      @article = N'CategoryImportMap', @force_invalidate_snapshot = 0
GO

---------------------------------------------------------------------------------------------------
-- do the schema changes
---------------------------------------------------------------------------------------------------

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

SELECT @@TRANCOUNT

ALTER TABLE [dbo].[CategoryImportMap]
 DROP CONSTRAINT [PK_CategoryImportMap]

DROP index [UC_SegmentCategory]
on  [dbo].[CategoryImportMap]

ALTER TABLE [dbo].[CategoryImportMap]
ADD CONSTRAINT   [PK_CategoryImportMap]  PRIMARY KEY CLUSTERED   (  [MPlanSegmentCode] ASC  , [MPlanCategoryCode] ASC  )

SELECT @@TRANCOUNT

COMMIT TRANSACTION

---------------------------------------------------------------------------------------------------
-- add the table back to the replication
---------------------------------------------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL read committed;

EXEC sp_addarticle @publication = N'Product_Staging',
                   @article = N'CategoryImportMap',
                   @source_object=N'CategoryImportMap',
                   @destination_table =N'CategoryImportMap'
GO

--========================================================================
-- REFRESH THE SUBSCRIPTIONS
--========================================================================
EXEC sp_refreshsubscriptions @publication = N'Product_Staging'
GO

--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
EXEC sp_startpublication_snapshot @publication = N'Product_Staging'
go

-- test number of rows (source and destination)
sp_count 'CategoryImportMap'


MySQL Insert into table, where name = id, if not exist, then insert name and use that ID I am producing a MySQL database that will store results for a particular sport. I have a table containing people, (PERSONID, FIRSTNAME, LASTNAME), another table containing animals names (ANIMALID, ANIMALNAME).

Within the results table, I have the position, personid, animalid, and time. I want to create a query that will insert into the results table a large number of 'results'.
I have managed to create a simple query to look up a persons ID based on their firstname and last name, however I want to take this further and say 'If this person doesn't exist based on their firstname and lastname, insert this into the persons table, and then use their ID for the results'. The same will be the case for the animals.

Also, as a side note, would it be possible to create a sort of procedure that will run through a list of peoples names and animal, and insert it into the results table? (preferable with the above 'if not exist, then insert and use id' scheme)

Thanks,

Graham

Assuming you have some tables for Persons, Animals:

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

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

and results:

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

  , CONSTRAINT Result_PK
      PRIMARY KEY (RaceID, Position)

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

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

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

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

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

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

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

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

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

Similar for Animal:

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

And then in Result:

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

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


How is an AAG listener implemented? Where does it run? I'm fairly new to SQL Server but generally well versed in RDBMS and network topics. I'm trying to wrap my head around AlwaysOn Availability Groups. I've read a handful of articles on MSDN and Googled liberally.

I think I understand that the AAG listener appears like a service on the network, it has a hostname and/or IP address and a port number.  And if you create it using SQL Server Management Studio (at least), you create it "in" the server instance which hosts the primary replica.

What I don't understand is what happens under the covers.  Is the listener a thread in the server instance which hosts the primary replica? Is it a separate process?  If so, does it run on the same server or elsewhere in the WFC? If it runs on the same server as the primary instance, how does the listener survive failure of that node?  (The data may well be replicated to a synchronous secondary, but if I can't reach the listner, I can't be redirected to that data.)

The Availability Group Listener (AGL) is part of the AG's Windows Server Failover Cluster (WSFC) Resource. It is a Virtual Network Name with n number of IP addresses. Being in a cluster resource it will only run on a single node of the WSFC and that node will be the primary node of the AG. It is not a thread within SQL Server. The WSFC handles the failover of the AG resource and the AG databases will not come online without the AG resource residing on its node of the WSFC.

The listener just handles connection forwarding. When connecting to the read/write replica the connection will hit the listener and then the listener will tell the client to connect to the primary replica's IP address. In the case of read-only routing it will follow the below work flow.


close existing connections during a restore is there an option to close existing connections when doing a database restore in transact sql (equivalent to the box that we can check in SSMS)?

can we use with restricted user to do it an if so, what should we do to remove this option once the restore operation finish

Thanks in advance

You will have to set the database to single user with roll back immediate, do the restore , then set to multi user in the same batch

ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [test] FROM  DISK = N'V:\MSSQL2016\Backup\test.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [test] SET MULTI_USER

You could get this script by setting all your options in SSMS and instead of clicking OK, Click Script at the top of the window.


8k row overflow error when updating row of size 5k I'm trying to update a target table which has one row of size 5k to a row of size 5k also.

Since it's one row it's easy to know the row actual size:

select *
from sys.dm_db_index_physical_stats(DB_ID('RODS_HSD_ES'),
OBJECT_ID(N'TBL_BM_HSD_SUBJECT_AN_148_REPRO'), NULL, NULL, 'DETAILED')

Reproduce

Table wasn't altered since creation. don't see any reason why it should fail. Ideas?

The update fails for broadly similar reasons to those I explained in answer to your previous question.

In this case, because you are potentially updating multiple rows where a key column of a unique index is changed*, SQL Server builds a plan that includes Split, Sort, and Collapse operators to avoid intermediate unique key violations (see this article for details).

The Sort operator thus introduced encounters an intermediate row (including internal overheads) of a width that exceeds the limit, so an error is raised. Adding an OPTION (ROBUST PLAN) hint to the update query shows this is unavoidable:

  Msg 8619, Level 16, State 2, Line 681
  The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. Resubmit your query without the ROBUST PLAN hint.

The source/target data relationships are unclear to me from a brief look, but if you can guarantee that each update operation will affect at most one row, you can avoid the need for the Split/Sort/Collapse by adding TOP (1) to the update statement:

UPDATE TOP (1) [TBL_BM_HSD_SUBJECT_AN_148_REPRO_TARGET]
SET ...

This is a bit of a hack, though. Ideally, the update statement construction and indexes should provide enough information to the optimizer so it can see that at most one row will be updated. In particular, it is best practice to write update statements that are deterministic.

Given the odd design and lack of clarity in the question, I'm not even go to try to decipher the data relationships, or query & index changes that would be necessary to achieve this in detail.

* As Martin Smith pointed out in a comment, this would not be a problem in this particular situation if the table were not partitioned. Where the update sets the key to the same deterministic value in every row, Split/Sort/Collapse is not required, unless the table is also partitioned on that key. So, an alternative solution for this query is to not partition the table on sampletime.


Foreign key double inheritance SQLITE I'm designing a domotic system database and I'm trying to figure out how to represent two foreign keys.

This is the cardinality I want. Consider 1...N Areas (rooms, halls, kitchens, etc.):

Area 1 - has - N PeripheralUnit (where N ∈ ℕ)
PeripheralUnit 1 - contains - N Device (that can be either sensors or actuators)

My question is if the table Device needs to inherit as foreign key the Area id from PeripheralUnit or maybe from Area, directly.

This is my design:

Maybe I don't need at all to introduce the Area id as a FK in Device, because I can find it in PeripheralUnit.

Need your advice please.

In theory and in the general case in practice for OLTP it would be bad practice to have the extra key: you are duplicating information that can be derived from elsewhere. You have the extra key value to store (and presumably index) and if a PeripheralUnit could move from one area to another then unless the area values on the relevant device records are updated your data is inconsistent, meaning you need extra logic (either in your business layer or in the DB) to ensure that this inconsistency does not creep in.

In some cases denormalising in this way is useful for efficient reporting. Here you would be trading off that extra coding (to maintain the extra relationship data consistently) and insert/update time (as said code is run on each create/change, and results in extra I/O activity) against greater reporting ease. IMO this does not look like a situation where the data is large enough or the model complex enough that this trade off is going to be beneficial.


Find user who kicked off a SQL Agent job at runtime I have inherited a breakglass procedure that allows certain users to kick off a SQL Agent job that inflates their privileges on the instance.

It currently sends an email to say that it has been invoked, waits for 4 hours then revokes the privileges, sending an email out to say that it has been revoked.

But I just can't find a way to capture who kicked the Agent job off.

Is there a way that the user id details can be captured or is it always going to take the SQL Agent users details?

This seems to be available in the msdb.dbo.sysjobhistory table under the message column.

We can use some code to extract the text based on the "Executed as user" text:

SELECT          [j].[name] AS [JobName],
                CASE
                    WHEN LEFT([h].[message], 17) = 'Executed as user:' THEN
                        SUBSTRING([h].[message], 19, CHARINDEX('.', [h].[message], 1) - 18)
                    WHEN [h].[message] LIKE '%invoked by%' THEN
                        SUBSTRING(
                                     [h].[message],
                                     CHARINDEX('invoked by ', [h].[message]) + 11,
                                     CHARINDEX(
                                                  '.',
                                                  SUBSTRING(
                                                   [h].[message],
                                                   CHARINDEX('invoked by ', [h].[message]) + 11,
                                                   99
                                                   )
                                              ) - 1
                                 )
                    ELSE
                        [h].[message]
                END,
                [h].[message]
FROM            [msdb].[dbo].[sysjobs]       AS [j]
    INNER JOIN  [msdb].[dbo].[sysjobhistory] AS [h]
                ON
                [j].[job_id] = [h].[job_id]
WHERE           [j].[enabled] = 1   --Only Enabled Jobs
ORDER BY        [JobName];


Cannot read from /tmp with PostgreSQL COPY, but able to read the same file from another directory with the exact same permissions I have a file /tmp/foo.csv that looks like this:

1,2
10,20
100,200

I have the same exact file in another directory /mydata/foo.csv. Both directories and files have the same exact permissions, owner, and were created by the same user, yet when I run

COPY foo FROM '/tmp/foo.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE);

I get this error

ERROR:  could not open file "/tmp/foo.csv" for reading: No such file or directory

When I run

COPY foo FROM '/mydata/foo.csv' (FORMAT CSV, DELIMITER ',', HEADER TRUE);

I see COPY 3.

What am I missing here?

Here's the relevant file and directory information:

╭─foo@bar  / ‹py27›
╰─$ ll -d /mydata /mydata/foo.csv /tmp /tmp/foo.csv                                                                                1 ↵
drwxrwxrwt  2 root root 4.0K Sep  9 08:26  /mydata
drwxrwxrwt 17 root root  420 Sep  9 08:57  /tmp
-rw-r--r--  1 root root   17 Sep  6 12:03  /mydata/foo.csv
-rw-r--r--  1 root root   17 Sep  9 08:36  /tmp/foo.csv
╭─foo@bar  / ‹py27›
╰─$ diff /mydata/foo.csv /tmp/foo.csv
╭─foo@bar  / ‹py27›
╰─$

I'm running Arch Linux

NOTE: I've also tried this on another Linux machine running CentOS and I was unable to reproduce the problem.

On Arch, the systemd script for PostgreSQL (/usr/lib/systemd/system/postgresql.service) enables private /tmp, which means the PostgreSQL server process can't communicate with the user using /tmp.

Two possible solutions:

Create a new folder somewhere else and give user postgres read privileges on it: # sudo mount -t tmpfs -o users,rw tmpfs /data
(NOT RECOMMENDED) Set PrivateTmp option in postgresql.service to false.


SQL Server - Snapshot is useful in production environments? I'm reading about SQL Server high availability solutions and disaster recovery and among the available resources the SQL Server have snapshot feature.  In theory all seems like beautiful.
I also read that a snapshot will copy a database at a point in time and you can use this to restore a database.
In this answer there is a comment (by Peter Schofield, 2013) about SQL Server snapshots not having support, and is useful in a development environment for quick rollbacks.

[...] Perhaps the biggest hindrance to adoption is that Management Studio didn't offer support[...]
[...] It sounds like an ideal use of snapshots in a dev environment just for quick script deployments and quick roll backs.[...]

I would like to know if snapshots are really useful in production environments. What are some examples of usage in production, and please include personal examples about when you've used snapshots to provide a solution on production systems.
The principal objective is provide some examples of real usage and through these examples get some useful ideas for me and for everyone who will be reading this post.
In my case I use SQL Server 2017 Enterprise Edition in production environment.

As an opinion based question and answer, I suspect that this will get closed, but here's my two cents.

I have used and seen Database Snapshots in my production environments, although rarely.

Scenario A) We used database snapshots to provide a static image of a database for reporting (ETL to a datawarehouse) purposes.  Daily a script would run at the specified time and create a snapshot for reports to use.  This database was transactionally consistent as of it's date of creation so useful for that.

Scenario B) A terrible maintenance job that ran and did some analysis of a very deep table and then would start deleting appropriate entries. We created a snapshot at the start of this process to prevent any blocking from taking place.  So the maintenance job was changed to get the list of "what needed work" from the snapshot, then drop the snapshot and then "do the work" on the live database.

The use case for database snapshots is niche for anything except CHECKDB.  SQL Server does some lock isolation tricks in the background that make it a good choice if you need a quick and dirty fix for certain classes of operations.