MYSQL Huge Data migration (5TB) from old server to new server We have an old MYSQL server  contains huge data ~5TB and wanted to migrate to the new server in order to minimize the costs and get rid of very old hardware.
My one and only idea in my mind is using mysqldump for the migration but I’m pretty sure that it’s a poor and risk option with that huge data.
Then someone in my team came up with the idea for using ETL tools but we haven’t go into the deeper details and not really sure if this ETL way can literally help us.
Any idea are always welcomed
thanks

$ mysqldump -h source ... | mysql -h dest ...

I'm assuming:

You have the tow machines (source and dest) set up.
The two machines are "close" (not on opposite sides of the globe) so that network delays, etc would need to be considered
That script would be run on one machine -- either one.
If the versions are different, I cannot predict whether there will be some hiccups based on inconsistencies between versions.  If it fails running on one server, try the other.  If that fails come back with the error message(s).
It will take time because 5TB takes time to read and write.

If the versions are radically different, there could be major changes that we need to discuss.


How to reset a postgres database that has used all of the available disk space? I have a small ~10G PostgreSQL 8.4 database used for development that has filled up the /var directory and is now unusable. I am unable to connect to the database in this state to delete any data or drop any tables.

I am not concerned about keeping any of the data in the database, but I am unable to run a DROP DATABASE or similar command because I am unable to connect.

What is the best method for 'resetting' my database given this situation?

First I'd have a look around /var/log to see if there are any files I could get rid of. If that's the case, you could then start your service and remove the appropriate databases.

If you do not care of your data, you could drop your cluster with the pg_dropcluster command. Look at the man file for the appropriate options.

And by the way, 8.4 support has ended a while ago (https://www.postgresql.org/support/versioning/) and you shoudl really consider upgrading to a supported version.


PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client? I am using Certificate Authentication for clients on a PostgreSQL 9.1 database server. Several certificate common names are mapped to a single database user in pg_ident.conf.

I do not want to create a database user for each certificate because it is easier to manage accepted certificates with pg_ident.conf. But now I need some query to map a backend process to the common name used for the connection. Is there a way to get the common name (CN field) of the certificate with which a user connected to the database?

EDIT

Thanks araqnid for pointing me at the sslinfo extension (source). The function ssl_client_dn_field('CN') provides exactly the information I need.
But the sslinfo functions work only for the connection/backend of the current session, I would rather need this information for other connections/backends (query it by backend process id).

The sslinfo extension uses the global variable MyProcPort (defined in globals.c) of type struct Port* to get the required SSL information.
Maybe someone can give me a hint how the MyProcPort variable of other backends could be accessed in an extension (aka contrib module).

Add the extension sslinfo: http://www.postgresql.org/docs/9.1/static/sslinfo.html

This will give you the ssl_client_dn function for the full subject name, or I believe you can do ssl_client_dn_field('CN') to extract just the common name.


postgresql recovery.conf file location in 11.5 I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing method for incremental + PITR for postgres 11.5. However, I couldn't find much and found https://www.scalingpostgres.com/tutorials/postgresql-backup-point-in-time-recovery/

meaningful. I started working around the steps mentioned there. However at #add recovery.conf

    # add recovery.conf
    nano /var/lib/postgresql/10/main/recovery.conf

The article says to create the recovery.conf in main directory. However, as per 11.5 installation, there is no main directory. The directory structure is like:

    /var/lib/pgsql/11/data
    [root@local-pos-52 data]# ls -ltr
      total 96
      -rw-------. 1 postgres postgres 23971 Aug 30 16:05 postgresql.conf
      drwx------. 6 postgres postgres    50 Aug 30 16:07 base
      -rw-------. 1 postgres postgres  4370 Aug 30 16:12 pg_hba.conf
       drwx------. 2 postgres postgres     6 Aug 30 16:12 pg_replslot
       drwx------. 2 postgres postgres    17 Aug 30 19:04 pg_notify
       -rw-------. 1 postgres postgres    58 Aug 30 19:04 postmaster.opts
       -rw-------. 1 postgres postgres    30 Aug 30 19:04 current_logfiles
        drwx------. 2 postgres postgres     6 Aug 30 19:04 pg_stat
        drwx------. 4 postgres postgres    65 Aug 30 19:04 pg_logical
        drwx------. 3 postgres postgres  4096 Aug 30 19:04 pg_wal
       -rw-------. 1 postgres postgres    96 Aug 30 19:04 postmaster.pid
        drwx------. 2 postgres postgres  4096 Aug 30 19:04 global
        drwx------. 2 postgres postgres    60 Aug 30 19:19 pg_stat_tmp
        -rw-------. 1 postgres postgres     3 Aug 30  2019 PG_VERSION
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_twophase
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_tblspc
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_snapshots
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_serial
        drwx------. 4 postgres postgres    34 Aug 30  2019 pg_multixact
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_dynshmem
        drwx------. 2 postgres postgres     6 Aug 30  2019 pg_commit_ts
        -rw-------. 1 postgres postgres 23891 Aug 30  2019 postgresql.conf-
       bak
       -rw-------. 1 postgres postgres    88 Aug 30  2019
       postgresql.auto.conf
       -rw-------. 1 postgres postgres  1636 Aug 30  2019 pg_ident.conf
      -rw-------. 1 postgres postgres  4269 Aug 30  2019 pg_hba.conf-bak
       drwx------. 2 postgres postgres    17 Aug 30  2019 pg_xact
       drwx------. 2 postgres postgres    17 Aug 30  2019 pg_subtrans
       drwx------. 2 postgres postgres    31 Aug 30  2019 log

Also, there is no folder named "postgresql" got created in this installation at /var/lib location.

I tried to go with creating recovery.conf at this path "/var/lib/pgsql/11/data" only. However, after that when I restarted postgres services,it didn't work.

It will be appreciated ,if someone specifies where to create  recovery.conf, so that it will actually work or share the doc/link to implement incremental backups as well as PITR for postgres 11.5

Thanks for any help in advance!

That's what you get by following tutorials. It is better to understand the process (as explained in the documentation), then problems like this won't happen.

You have to place recovery.conf in the data directory. The data directory is the directory where postgresql.conf is, unless the data_directory parameter in postgresql.conf says differently.


Why should I apply a CU for SP2 that was released after the release of SP3? Checking SQL Server 2014 build versions to identify the right update for my server I got a little confused as what update to use. Did some research and found out I should Apply the latest Service Pack and Cumulative Update. Ok, but one question remained:

I currently have SP2 installed and there's a CU for SP2 with the release date of July 29, 2019, but SP3 has been released October 30, 2018.

The way I see it, the CU15-CU18 for SP2 shouldn't exist. (I know I'm wrong, but I don't know why)
What's there in a service pack that one might not want to install it but will install other CUs that were released after the most recent SP?

If you plan to remain on SP2 for now, then you can install any SP2 CUs that you determine are needed.  To specifically answer why you should install an SP2 CU at this point, that is somewhat of a murky answer.  You don't really need to install a CU unless it has a fix you need, so if you don't have any issues that the CUs fix, then you might consider just not installing them.

If you upgrade to SP3, then you can start installing SP3 CUs.

As to why the SP2 CUs in question even exist, Microsoft continues to release CUs and security updates for older service pack levels for some time so that you are not forced to update to the latest SP immediately in order to be able to install the fixes they addresses.

CUs generally have fixes for issues that affect a limited number or customers, so it's not critical to upgrade every time a CU is released.  You certainly don't want to upgrade production systems as soon as a CU is released unless there is something in it that addresses an issue on your system.

More importantly, though, are the security updates, and it is advisable to get those installed and tested in non-production environments and then to production sooner rather than later.  If there is a data breach, you want to be able to say that all security updates are installed, even if the breach was unrelated to those updates.

You can check how long a given service pack will be supported/updated at Search Product Lifecycle, where you'll see the following:

SQL Server 2014 Service Pack 1 has a support end date of 10/10/2017 (i.e., you should not be running SP1 as you've missed all security updates since 10/10/2017)

SQL Server 2014 Service Pack 2 has a support end date of 1/14/2020 (i.e., you need to plan to upgrade to SP3 soon because security updates for SP2 will not be released after 1/14/2020).

SQL Server 2014 Service Pack 3 has no support end date published.


Optimized query to fetch a record from 10 million entries A table has 2 fields
| UniqueKey |  TimeStamp |
-------------------------
| xfsddddq  | 1024125412 |
| xfstttdx  | 1024125413 |<
| xfsdxxau  | 1024125415 |

Estimated, there are 10 million records. Need to verify if UniqueKey is present from within (CURRENT_TIME - 5MINS).

Currently

Flushing my table every hour (cannot be decreased further).
Inserting more than 10 million records in an hour, as time increases the number of rows executed to fetch one record is also increased further leading to increase in execution time.
How to limit query to check one record from (CURRENT_TIME - 5MINS) or effectively fetch the result so that, the time of execution is same at 5th minute and 59th minute.

The query to accomplish this would be of the form:

SELECT t.UniqueKey
  FROM mytable t
 WHERE t.UniqueKey = ?
   AND t.timeStamp >= NOW() - INTERVAL 5 MINUTE
   AND t.timeStamp <= NOW()
  LIMIT 1

(This query assumes that the timeStamp column is defined as datatype TIMESTAMP.)

The query will either return one or zero rows, which will indicate either that the specified UniqueKey does "exist" in the past 5 minutes, or that it "doesn't exist".

That query cries out for an index:

... ON mytable (UniqueKey,timeStamp)

If UniqueKey is truly UNIQUE within the table, then an index on just UniqueKey is satisfactory. Can't really determine what's best without knowledge for the column datatypes, storage engine, existing keys/indexes, data distribution, et al.

UPDATE

(the answer above was copied from StackOverflow, where the question first appeared.)

OP has updated question with sample data, illustrating that the timeStamp column is probably NOT a MySQL TIMESTAMP, but appears to be an integer value.

If we assume that it's stored as an integer datatype (e.g. INT, BIGINT, etc.), and that the integer value that is ascending in datetime order... then the most efficient query would be of the form:

SELECT t.UniqueKey
  FROM mytable t
 WHERE t.UniqueKey = ?
   AND t.timeStamp >= ?
   AND t.timeStamp <= ?
  LIMIT 1

The values are supplied in the predicates (to compare to timeStamp are assumed to be integer values. That's just an assumption, we don't have knowledge here that they actually ARE integer values, and we don't have knowledge of how the datetime is encoded.

It's very likely that timeStamp value represents the integer number of seconds since the beginning of the epoch (midnight Jan 1, 1970 UTC). That's a widely adopted convention, but it's still just a convention, not a "rule" that all columns with a name of timeStamp have values that are encoded this way.

So, I hesitate to make any further recommendations based on assumptions. MySQL does provide functions that make conversions possible, some very convenient. But without knowledge of what values are actually being stored in the timeStamp column. Those recommendations would be based on assumptions.


How I create backup with backup by dump and WAL files in PostgreSQL? I have a backup and WAL files, I like update my backup with WAL files. Anyone know how to do it?

Thanks

You cannot restore a pg_dump (via psql or pg_restore) then update it with WAL archives. It just isn't possible, sorry.

WAL archives are block-level records of changes to individual relation files. They only make sense when applied to an identical filesystem-level copy of the original database as created from a pg_basebackup, via rsync, etc.

If you try to apply WAL archives to a DB that's restored from a pg_dump they'll be rejected because as far as PostgreSQL is concerned they're different, unrelated databases. At the block/filesystem level they are; the blocks are different, the relation IDs are different, etc. The WAL archives make no sense when applied to this database, even though at the logical level the row data looks the same.

You must use a pg_basebackup copy to restore WAL, and it only works if you have a complete and uninterrupted sequence of WAL from the moment the base backup was taken until the final segment.


Need Help Normalizing a Database for Media Website I've created a media website where visitors can watch trailers, view pictures, etc. Along the way, I've found that I've needed additional images for sharing, etc. I went the "easy" route and grew the "Media" table to something that's become somewhat ugly. I can continue to grow the Media table, but I know it's bad design to have a table that has too many columns.
Here's the gist/diagram of the current schema (the Media table is shortened to include only relevant columns, it has about ~40 columns total): https://dbdiagram.io/d/5fbbcf413a78976d7b7d1252
I thought about "peeling" away at the Media table by creating a MediaVideos table as I'd like to have a trailer and also a short clip (that would be available just for registered users). However, I stopped as I quickly realized that If I were going to essentially rearchitecht things, I should do it the right way.
So, I'm thinking of creating two new tables: MediaAssets and MediaAssetTypes. Something like this: https://dbdiagram.io/d/5fbbd2513a78976d7b7d12e8
The issue, I feel, is that means the table would have some columns that wouldn't be used for some assets. Is that ok? For example, I only need the height & width for some images/assets, not all.
I just want to get a second (or more) opinion on this before I essentially tear apart my application to get it right.
Any suggestions & ideas are welcomed!
Thank you!
UPDATE:
After thinking it further and thinking about J.D.'s reply, I've thought of a third scenario where I keep the MediaImages table but add a Height, Width, and ImageTypeId to support images that I need to know the height/width of. And then create a MediaTrailers table with a TrailerTypeId: https://dbdiagram.io/d/5fbc21363a78976d7b7d1d87

You should read up on Star Schema and Database Normalization. While it's not always the solution for all database designs by any means, I believe following a loose implementation of it is generally good practice in a lot of cases, including yours. (It's even a derivative of what the whole Snowflake database management system is based off of.)
Here's a pretty good document on Star (and Snowflake) schema design: Understand star schema
(While this is targeted towards PowerBI, I think Microsoft does a good job explaining the concepts of the schema designs, so just focus on those sections.)
This is a good straightforward article on Normalization by Microsoft: Description of the database normalization basics
Database Normalization is even more relevant to your design question and more important between the two to understand.
From looking at your first design with the Media table that has 40+ columns, it might make sense to break it into different tables for the things that begin with the same prefix in your column names.
E.g. TrailerAttributes and SocialMediaAttributes could be their own tables. Then things in your Media table that don't have a trailer for example, won't have an empty and unneeded column. You can further normalize things away into their own tables this way too, say for example some common property belonged to both some Trailers and some Social Media rows. You'd make a further decoupled table that stored those attributes shared among the other tables. (This is another lever of normalization, which will make sense after you read my link above.)
It seems like your updated schema is a step in the right direction, especially with aiming to implement a more generic solution.


Rename SQL log and master database file I am working on SQL Server 2014. I want to know how can I rename the .ldf and .mdf files of my database after creating it? If it is possible what are the steps?

You can't rename the files while they are in use, and don't make them "not in use" by stopping the SQL Server, it won't pick up on the change automatically.

The quickest way is as follows:

Detach the database with EXEC sp_detach_db 'database_name' or the appropriate dialogue in SQL Server Management Studio.
The database must have no current connections otherwise SQL Server won't allow you to detach it - if you have trouble with this force that database into single user mode.
Rename the files as needed
Reattach the database which assuming you ave only a single data file and a single log files is done with EXEC sp_attach_db 'database_name', 'full_path_to_mdf', 'full_path_to_ldf', for example: EXEC sp_attach_db 'my_renamed_db', 'd:\dbs\data\my_renamed_db.mdf', 'd:\dbs\logs\my_renamed_db.ldf'
Again, the appropriate dialogue in SQL Server Management Studio can be used instead.

As you are specifying the database name in the call to sp_attach_db, this does the rename step also.

Update:

As Travis reminds me in the comments, sp_attach_db has been officially deprecated for a while and you should use the newer DDL method instead. The above example becomes:

CREATE DATABASE my_renamed_db
    ON (FILENAME = 'd:\dbs\data\my_renamed_db.mdf')
     , (FILENAME = 'd:\dbs\data\my_renamed_db.ldf')
FOR ATTACH;

Slightly safer even, you can avoid completely detaching the database at all by setting it offline and modifying the file locations instead (you still have to manually move or rename the files though):

ALTER DATABASE my_renamed_db SET OFFLINE;
ALTER DATABASE my_renamed_db MODIFY FILE (NAME='data_file_name', FILENAME='d:\dbs\data\my_renamed_db.mdf');
ALTER DATABASE my_renamed_db MODIFY FILE (NAME='log_file_name', FILENAME='d:\dbs\data\my_renamed_db.ldf');
ALTER DATABASE my_renamed_db SET ONLINE;

You can move/rename the files any time after the SET OFFLINE but before SET ONLINE.

If you don't know the logical filenames you can look them up in SSMS or via

SELECT f.name, f.physical_name
FROM   master.sys.databases d
JOIN   master.sys.master_files f
       ON d.database_id = f.database_id
WHERE  d.name = 'my_renamed_db'

If moving tempdb things are different: you can't offline it and it is recreated every time the server starts. Instead make the ALTER DATABASE MODIFY FILE calls and restart the SQL Server instance.


Can I remove #sql_ .MYD files? I found some old files in the mysql datadir:

-rw-rw----  1 mysql mysql     0 2012-10-09 09:39 #sql_263c_0.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-10-09 09:39 #sql_263c_0.MYI
-rw-rw----  1 mysql mysql     0 2012-10-09 09:40 #sql_263c_1.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-10-09 09:40 #sql_263c_1.MYI
-rw-rw----  1 mysql mysql     0 2012-10-09 09:40 #sql_263c_3.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-10-09 09:41 #sql_263c_3.MYI
-rw-rw----  1 mysql mysql  338M 2012-08-21 15:54 #sql_3e03_0.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-08-21 15:52 #sql_3e03_0.MYI
-rw-rw----  1 mysql mysql  694M 2012-08-21 15:54 #sql_3e03_12.MYD
-rw-rw----  1 mysql mysql  1.0K 2012-08-21 15:46 #sql_3e03_12.MYI
-rw-rw----  1 mysql mysql  756M 2012-08-21 15:54 #sql_3e03_13.MYD

I have no idea how does it created, maybe temp tables ? Is it possible to check the files still used by mysqld or can be remove ?

My version is: Server version: 5.0.45-log Source distribution

By all means, remove them. Those are just temporary tables mysqld had written to disk.

In all likelihood, mysqld probably crashed when those temp tables were made.

Look at the timestamps:

Max is 2012-10-09
Min is 2012-08-21

The sum of these files are over 1G. They are just taking up room.

I know they are temp tables because

MySQL uses MyISAM as the Storage Engine for Temp Tables
Temp Tables have no .frm, which prohibit DB sessions from accessing them directly in SQL

You can remove the corresponding .MYI files as well. Those are the index file components of teach temporary MyISAM tables. They are never populated because temp tables are never indexed.


List of Expired Merge Subscribers I have a setup of merge replication.

Sometimes, subscribers will not be able to sync within the retention period set in the publication.

How can I get a list of expired subscribers to a merge replication?

Open SQL Server Management Studio
expand instance of SQL server
expand Replication Folder
expand Local Publications
Right click publication that you want to check for any expired subscriptions and choose 'Launch Replication Monitor'
expand My publishers, right click on publisher in question and click 'Configure replication alerts'
Choose 'Replication Warning: Subscription expiration (threshold: expiration) and click Configure
select Response and check Notify Operator
Add new operator

This will alert you when you have expired subscriptions. It will show all expired subscriptions in Replication Monitor on the Subscription Watch List Tab. If you set it up correctly, will also give you email alerts.

EDIT:

In order for you to receive emails, database mail must be configured.

Connect to instance
expand the management folder in object explorer
right click on Database Mail and choose configure database mail
add profile name and click Add to configure email address and email server

if you don't have an email server you can use google / yahoo /etc.

Hope this helps!!


Restored database copy larger than original I've created a copy of a database for testing. It's on the same server as the original. However, tables are listed as being larger on the restored copy than on the original.

My original database is 148 MB, the copy is 232 MB. Table 'message' is 15 MB on the original, 28 MB on the copy. (I'm using queries like https://stackoverflow.com/a/1733523/492455 to get these values).

The CREATE TABLE statements (SHOW CREATE TABLE) are identical on both databases (except for the AUTO_INCREMENT value). Here's one for a table (33 MB original size, 76 MB copied size:

CREATE TABLE `mdl_log` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `time` bigint(10) NOT NULL DEFAULT '0',
  `userid` bigint(10) NOT NULL DEFAULT '0',
  `ip` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `course` bigint(10) NOT NULL DEFAULT '0',
  `module` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cmid` bigint(10) NOT NULL DEFAULT '0',
  `action` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `url` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `info` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `mdl_log_coumodact_ix` (`course`,`module`,`action`),
  KEY `mdl_log_tim_ix` (`time`),
  KEY `mdl_log_act_ix` (`action`),
  KEY `mdl_log_usecou_ix` (`userid`,`course`),
  KEY `mdl_log_cmi_ix` (`cmid`)
) ENGINE=InnoDB AUTO_INCREMENT=231557 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='Every action is logged as far as possible'

Can anyone suggest what's going on here?

MySQL Server v5.6.24 on CentOS 6.2.

Thanks,

Leon

You may find this surprising but this is something I have come to expect often. I have mentioned in other posts how data loaded in primary key order can populate BTREE indexes in a lopsided manner:

May 01, 2014 : Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?
Dec 28, 2014 : What are the optimal mysqldump settings? (See the CAVEAT about --order-by-primary)

This behavior is seen for MyISAM and InnoDB alike. Don't be too alarmed by this. If the data being copied is in order by an auto-incremented primary key when dumping, the clustered index is populated in a lopsided manner from inception.
When it comes to InnoDB, bulking loading data into a table with an index will have a degree of fragmentation introduced. You just have to bite the bullet on this.
To minimize this, you should do the following for table mytable:

Copy the table schema for mytable
Create empty mytable on target server
Drop indexes for mytable on target server
Load data into mytable on target server
Create all indexes

MyISAM already has this paradigm in place for non-unique indexes when reloading MyISAM tables

ALTER TABLE mytable DISABLE KEYS;
Load the data
ALTER TABLE mytable ENABLE KEYS;

InnoDB does not :-(
CONCLUSION
Your main concern should be the content of data.
You should run pt-table-checksum for the table for both source and target servers. Even with mismatches here, do not worry too much with VARCHAR columns (See my post Native checksum table says there's a difference, pt-table-checksum does not).
You could also just dump the data in a specific into CSV files on source and target servers and compare the text just to inspect content.
In closing, even though the table is small, once the copy is done and the table is not corrupt, try what you reasonably can to defragment your tables and just live with it.


SQL Server - Copy data from one database to another - identical tables, new identity values needed Here's the thing, I have been running identical copies of my website for two different geographical regions. The site now supports multi-currency pricing so I want to merge both sites together.

All tables that I need to merge are Identity based and foolishly the matching tables in each copy of the database were seeded at the same number.

Is there a way to copy the data from one database to another at the same time giving the copy data new identity values that also are reflected in the foreign key values of referenced tables?

i.e.

Account
- Id
- Email
- FirstName
- etc

AccountImage
- Id
- Account_id
- FileName
- etc

And so on..

Unless I'm missing something your problem is not moving the data it's dealing with the identity values that are already set up.  If that is the case then try this.

Pick a value greater than your current ident values on either DB.  I would pick a round value, say 1,000,000.
Pick the ident values you want to change (for example if you have lookup tables that are the same for both DBs then you probably want to leave them the same.)
When you move your data add the value you selected in step 1 to the idents value you want to change as you move them.

As long as you are consistent in the value you are adding all of your relationships will stay the same into the combined DB.  So for example

You have an employee table with a current max id of 200,000 in DB A and 1,400,000 in DB B.  You decide to move the data from DB A to DB B because it means moving less data.  As you move your employee table you add 2,000,000 to the Employee_Id column.

USE DatabaseB
GO

SET IDENTITY_INSERT Employee ON

INSERT INTO Employee (Employee_Id, Other_Columns)
SELECT Employee_Id + 2000000, Other_Columns
FROM DatabaseA.dbo.Employee

SET IDENTITY_INSERT Employee OFF
GO

And last piece of advice back up everything before you start in case you make a mistake :)


import CSV into mysql table via command prompt (MySQL 5.6.25) I'm trying to load CSV's into a mysql database with this command:

LOAD DATA LOCAL INFILE '\Users\userName\Downloads\tableName.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

But I recieve this error:

ERROR 2 <HY000>: File 'UsersuserNameDownloadstableName.csv' not found <Errocode: 2 - No such file or directory>

I log onto mysql with the command (as described here MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)):

mysql -u root -p --local-infile

doing what was described in the link above I was able to import the CSV's on a different MySQL Server (On a seperate machine running Ubuntu).  However the same process doesn't seem to be working for a differente MySQL Server instance on a Windows machine using Command Prompt.

Since this is Windows we are dealing with, either use the double backslash

LOAD DATA LOCAL INFILE 'C:\\Users\\userName\\Downloads\\tableName.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

or the forward slash

LOAD DATA LOCAL INFILE 'C:/Users/userName/Downloads/tableName.csv'
INTO TABLE tableName
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

It says so in the MySQL Documentation on LOAD DATA INFILE

  The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes. The character_set_filesystem system variable controls the interpretation of the file name.


Multiple Functional Dependency to the same dependency I am looking for the 3NF form of the following relation:

1. emp_no -> phone_no, office_no, dep_no, proj_no
2. emp_no, date -> job, salary
3. phone_no -> dep_no, office_no, area
4. proj_no -> dep_no, p_buject
5. dep_no -> mgr_emp_no, d_budget
6. mgr_emp_no -> dep_no

I can only derive the first dependency to:

1. emp_no -> phone_no, office_no, proj_no

because proj_no and phone_no are depended by dep_no. Then the rest is not decomposable, therefore they are copied over on my sheet.

As you can see, 5 and 6 are dependent on each other. I looked through resources on the Internet, I have never seen such relationship before (being dependent and parent of each other). How can I make these functional dependencies to 3NF format?

To bring a schema in 3NF, first you have to find a canonical cover of the dependencies. Then, you can apply either the “analysis” algorithm to find the Boyce-Codd Normal Form (which is more strict that the 3NF) or you can apply the “synthesis” algorithm to find the 3NF. In this case, both the algorithms gives the same results.

Here is a canonical cover of the dependencies:

date, emp_no → job
date, emp_no → salary
dep_no → d_budget
dep_no → mgr_emp_no
emp_no → phone_no
emp_no → proj_no
mgr_emp_no → dep_no
phone_no → area
phone_no → dep_no
phone_no → office_no
proj_no → dep_no
proj_no → p_buject

Let's find the 3NF with the synthesis algorithm. The first step is to collect together all the dependencies of the canonical cover with the same left part, that is:

date, emp_no → job
date, emp_no → salary

dep_no → d_budget
dep_no → mgr_emp_no

emp_no → phone_no
emp_no → proj_no

mgr_emp_no → dep_no

phone_no → area
phone_no → dep_no
phone_no → office_no

proj_no → dep_no
proj_no → p_buject

The second step creates a relation for each group, with the key given by the common left part:

R1 (date, emp_no, job, salary)          key: (date, emp_no)
R2 (dep_no, d_budget, mgr_emp_no)       key: (dep_no)
R3 (emp_no, phone_no, proj_no)          key: (emp_no)
R4 (mgr_em_no, dep_no)                  key: (mgr_em_no)
R5 (phone_no, area, dep_no, office_no)  key: (phone_no)
R6 (proj_no, dep_no, p_buject)          key: (proj_no)

The third step remove relations that are contained in another: in this case the relation R4 is contained inside R2, so R4 is removed.

The last step checks if any relation contains one of the keys of the original relation, and if this is not true add a new relation with one of the keys: since the original relation has (only) the key (date, emp_no), and the relation R1 contains it, no new relation must be added.

So the 3NF is the following:

R1 (date, emp_no, job, salary)          key: (date, emp_no)
R2 (dep_no, d_budget, mgr_emp_no)       keys: (dep_no), (mgr_em_no)
R3 (emp_no, phone_no, proj_no)          key: (emp_no)
R5 (phone_no, area, dep_no, office_no)  key: (phone_no)
R6 (proj_no, dep_no, p_buject)          key: (proj_no)

and reasoning from these relations you can infer the entities represented by each of them and the foreign keys towards other relations:

R1 represents the history of jobs of the employee, with key: (date, emp_no),
    and emp_no as FK
R2 represents a department, which is identified by dep_no but also by the number
    of the manager (which is a FK for employees)
R3 represents an employee, with key emp_no, with FKs phone_no and dep_no
R5 represents the “place” of an employee, with key phone_no and FK dep_no
R6 represents information about a project, with key proj_no and FK dep_no

Finally, note that the dependencies 4 and 5, that you have called “Multiple Functional Dependency to the same dependency”, are in effect particular, in the sense that they are transformed in a relation (department) with two primary keys, the number of the department and the number of the manager of the department. And this is managed by the algorithm in the third step by merging the two relations previously obtained (and for this reason the relation has two primary keys).


Add users to multiple databases I have 980 DBs on 1 server and I am supposed to grant readonly access to a couple of users.

I certainly don't want to run alter DB add user... statement since it is definitely time consuming.

I don't know if exec sp_addrolemember 'db_datareader', 'user' works?

Can someone let me know if something else can be done?

Here's how I would do it in Powershell:

$instance = 'localhost'
$dbs = dir SQLSERVER:\SQL\$instance\DEFAULT\Databases
$sql = 'CREATE USER [foo] FROM LOGIN [foo]; ALTER ROLE db_datareader ADD MEMBER [foo];'
$dbs | ForEach-Object {Invoke-Sqlcmd -ServerInstance $instance -Database $_.Name -Query $sql}

This assumes the login foo has already been created. It also assumes running on the localhost instance.


Should I delete the mysql database user id created for my site by the developer? I am a newbie when it comes to anything related to databases. I cut ties with my developer who had set up my site and was hosting it too about six months ago. I moved my site to a new host thinking that that was the end of that.

I realised recently that he also had access to the database as he'd created a privileged user id originally(a user by his name kept showing up in my sql processes). Can he still access my database on my new host or is this user showing only because it is the only default user?

I asked my hosting company if this was a concern but they said it was not an issue. I have my doubts though. Can anyone please tell me how to educate myself about this and if this is a concern. I just want to learn enough to be able to run my content based wordpress site.

Recently my site experienced a brute force attack so I want to take all precautionary measures when it comes to security.

If this is a concern will simply deleting this user from the cpanel take care of things?
Please help

You can change the password in wordpress account as shown in
Resetting_Your_Password

and change the cPanel user password also for secuirty concern.


How are the RowCounts in SQL Server Profiler calculated? I have a problem where the counts returned by the RowCounts column in a SP:StmtCompleted are different from the actual rows returned for execution of a select statement.

I have tried a select @@RowCount after the select query and it returns the same counts as count(*). I'm curious now what could be causing the profiler to return incorrect row counts.

I did read related threads suggesting fragmentation can yield wrong counts and a DBCC UPDATEUSAGE might solve the issue, but I want to understand what I'm doing before applying this. I would appreciate some elaboration.
Also, do indexes play a role in the counts?

Related Q & A: SQL Server Row Count sp_spaceused rows discrepancy

I am using SQL Server 2014 Profiler.

This is how you can see row counts in Profiler, in case any one is wondering: Show Row Count in SQL Profiler

Generally, you can expect that the row count reported will correspond to the number of rows returned to the caller (and to the (x row(s) affected) message in SQL Server Management Studio in the case of a top-level query).

However, where the statement contains nested calls, such as to a scalar user-defined function, row counts generated in the (repeated) calls to the function's execution plan will be added to the total. This is an unfortunate side-effect of where the counting is done*, and the way non-inline functions are currently implemented. It has been considered not important enough to fix.

In the linked Connect item example (better formatted in the parent blog post by Guy Glanster), the total row count for the query that returns 100 rows is reported as 300, due to the single row affected by each of the two statements in the function, on each invocation.

If the function is reduced to a single statement:

RETURN CAST(@DateTimeValue AS date);

...the count is 200 (100 rows for the outer query, plus 1 row a hundred times for the function).

DBCC UPDATEUSAGE will not affect this. Indexes and fragmentation have no bearing either. It is simply an architectural consequence, and another reason to avoid non-inline functions.

* The details are rather esoteric. Broadly, for a SELECT query, it is the number of rows seen at the root node of the plan. There is a shared memory structure involved, and nothing you can see directly in an execution plan. When a scalar function executes, it does so in a subcontext that happens to share the structure. Perhaps this is by design, it is hard to know for sure without source code access. My feeling (as stated) is that it is an unintended side effect. For DML queries, a particular plan node is assigned the job of maintaining the rows-affected count. This can differ significantly depending on the shape of the plan (for example if an OUTPUT clause is used, or Split-Collapse update processing is employed).


Is EXECUTE AS OWNER in msdb a security risk? I've been tasked with creating a system that allows non-privileged users to start and stop SQL Server Agent jobs.  I'm creating stored procedures in the dbo schema in the msdb database using the WITH EXECUTE AS OWNER construct to run EXEC dbo.sp_start_job <JobID>;, etc.

The Books Online topic about EXECUTE AS says this is a "best practice":

  Specify a login or user that has the least privileges required to perform the operations defined in the module. For example, do not specify a database owner account unless those permissions are required.

In my case, EXECUTE AS OWNER indicates [sa], since those modules are in the dbo schema, in the msdb database, which is always owned by [sa].

Am I inadvertently creating a security risk by doing this?

I'm not passing in any parameters that could be used in a SQL injection attack since the only parameter ever being passed in is a UNIQUEIDENTIFIER representing the job affected.

The user running the stored procedures that have EXECUTE AS OWNER have no access to the msdb database aside from membership in a database role I've explicitly created which GRANTs them the EXECUTE permission on the stored procs.

One of the conditions of this project is the users in question should not be able to run the SQL Server Agent GUI tools in any way.  This rules out giving them membership in the built-in SQLAgentUserRole role.  I'd also rather not modify the permissions on dbo.sp_start_job, etc, since I'm trying to not modify anything that SQL Server itself depends on (other than obviously adding items to the msdb database itself).

The code in question is here.

In and of itself, no, you are probably not adding a security risk.  As long as no one but a sysadmin can alter the procedure and as long as the ONLY thing the procedure can do is start a job I don't see a problem.

The risks come in with the fact that MSDB is (and is supposed to be) marked as TRUSTWORTHY.  That means that by using EXECUTE AS OWNER you are able to impersonate the SERVER level permissions.  In this case since it's sa that means you could create a procedure to grant another user sysadmin.

That does mean you do want to limit permissions to MSDB though.


query help in joining tables I'm quite green in mysql, but I have been assigned a task for which I completely lost. I have a unique codes table in mysql and then I have another 6 tables for individual clients. I would like to extract those codes which have a value in cat_code = 'wine' from all 5 clients and item_grp_code ='00101' from 1 client since the last client has no cat_code?

Any idea how to do around this?

This is what I tried so far:

SELECT DISTINCT
  items.code, nxr.departmentcode, nxr.itemgroupcode, las.departmentcode,
  las.itemgrpdesc, las.desc1, nxr.desc1
FROM (
retail_append.items items
left outer JOIN
retail_append.nxr nxr
  ON (items.code = nxr.code))
left outer JOIN
retail_append.las las
  ON (items.code = las.code)
WHERE (las.itemgroupcode = '00101') OR (nxr.categorycode = 'wine');

What about adding the where clause to the join itself?  Something like

left outer JOIN
retail_append.nxr nxr
ON (items.code = nxr.code AND nxr.categoryCode = 'wine')) and similar for the other with the item group code?


How to store user selected value, single choice or all choices I have a form where a user is able to select a single value from a dropdown. The first option in the dropdown is "All", followed by all of the individual options that they can pick. So, they can either pick an individual thing, or they can pick "All" which will effectively mean that they have picked everything in the list.

All Options
Option 1
Option 2
Option 3

The database is storing this currently with an ID for the selection, or when they pick all, we store a value of -1. Is this anywhere close to best practice, or should we have something like a boolean column "all_options", and another column "option" which would be forced to be null if "all_options" is true?

user  option
1     1
2     3
3     -1 (or null?)

vs

user  all_options  option
1     false        1
2     false        3
3     true         null

I can't find anything about this, and I think it has to do what not knowing what to search for.

A more concrete example

The options are say, items for sale on a website. The user is setting up alerts on price drops. One use case is that the user wants to know when a particular item is 50% off or more. They select the individual item from the dropdown of say 50 items. Another use case is, the user wants to know anything that is on sale in general. They pick the "All Items" option. The list of items changes, so if I create the relationship between this alert and every single item then:

That could be an extremely large number of relationships for just one alert, depending on the size of my list of items.
New items wouldn't be in the alert. It would only be items that existed at the time of the creation of the alert.

The "All options" should be part of the option list with an ID (-1 is OK to be recorded in the options master table).
I wouldn't include a NULL value, because it is commonly used to note lack of data.

Your second option is totally worthless from my point of view: you can easily calculate the 2nd field using the data in the 3rd field.


Deleting millions of records on SQL Server 14.0 I have two tables containing 200 Millions of records each.
I have to delete from them, approximately 70 Millions of records, based on an integer value in a column.

I'm deleting them in chunks of 4000, using the following script:

DECLARE @BATCHSIZE INT, @ITERATION INT, @TOTALROWS INT, @MSG VARCHAR(500)
DECLARE @STARTTIME DATETIME, @ENDTIME DATETIME

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000
SET @ITERATION = 0
SET @TOTALROWS = 0

WHILE @BATCHSIZE>0
BEGIN
    SET @STARTTIME = GETDATE();

    BEGIN TRANSACTION
    DELETE TOP(@BATCHSIZE)
    FROM [mydb].[dbo].tableA
    WHERE [mydb].[dbo].tableA.Code not IN (
            SELECT Code
            FROM [mydb].[dbo].TableB)

    SET @BATCHSIZE=@@ROWCOUNT
    SET @ITERATION=@ITERATION+1
    SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
    COMMIT TRANSACTION;

    SET @ENDTIME = GETDATE();

    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + '  >> ' + CAST(DATEDIFF(millisecond, @STARTTIME,@ENDTIME) AS VARCHAR)
    RAISERROR (@MSG, 0, 1) WITH NOWAIT
END

TableA contains 6 columns, 5 of integers and one NVARCHAR(64).
There is an index on the column Code, and clusterIndex on the PK.
TableB contains only one column, Code,it's a PK.

After running the script for a couple of hrs, it becames very very slow.

At the beginning each iteration was executed in 250ms, then it increase to 2 minutes after running for some hours.

The database is in simple recovery mode. It's not used by anyone, and it's running on a dedicated machine with 256GB of RAM.

I've tried to rebuild the indexes every hour, Shrink the database (not the file because my user can't) but it's always slow.

If I start to delete the records on another table it has the exact same behaviour, starting very very fast and then increase to slow down after every iteration.

How can I restore the initial conditions? What can I do to improve the delete?
I've tried to

For large deletes in batches, consider specifying a clustered index key range instead of using TOP so that a clustered index seek can be used in the plan. Below is an example.

DECLARE
      @BATCHSIZE INT = 4000
    , @ITERATION INT = 0
    , @TOTALROWS INT = 0
    , @MSG VARCHAR(500)
    , @STARTTIME DATETIME
    , @ENDTIME DATETIME
    , @StartValue int = 0
    , @EndValue int = 0
    , @MaxValue int = (SELECT MAX(PK) FROM [mydb].[dbo].tableA);

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;

WHILE @StartValue <= @MaxValue
BEGIN

    SET @EndValue = @StartValue + @BATCHSIZE;

    SET @STARTTIME = GETDATE();

    DELETE FROM [mydb].[dbo].tableA
    WHERE [mydb].[dbo].tableA.Code NOT IN (
            SELECT Code
            FROM [mydb].[dbo].TableB
        )
        AND [mydb].[dbo].tableA.PK >= @StartValue
        AND [mydb].[dbo].tableA.PK < @EndValue;

    SET @TOTALROWS=@TOTALROWS+@@ROWCOUNT;
    SET @ITERATION=@ITERATION+1;

    SET @ENDTIME = GETDATE();

    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + '  >> ' + CAST(DATEDIFF(millisecond, @STARTTIME,@ENDTIME) AS VARCHAR);

    RAISERROR (@MSG, 0, 1) WITH NOWAIT;

    SET @StartValue = @EndValue;

END;
GO


Restore Database from Network Drive I not able to restore DB from maped Network Drive, Only from HDD initialized in Native OS,

my enviroment is MsSql2008 on Win2008R2 Standard (as virtual in VMware ESX4.1),

Win2008R2 Standard doesn't supports adding Networks drive(s) as HDD, RAM, Procesors on Runtime, these resources are accesible after enviroment restarted,

I could be able to import DB from another instance, but there are some another internal rulles that doesn't allows me that (middle and huge DB)

1) backup in OmniBack form or Full_Backup_File

2) no available space on HDD to copy dump on local enviroment for middle and huge DB

3) another crazy rulles about Network trafics from local end_user_PC, reduced DiskArea, for moving files betwees luns....

is there someone that solved this issue, or this issue is strictly rellated with resources from WinOS

EDIT:

Answer from Microsoft Q&A

SQL Server Management Studio -> Server Objects -> Backup Devices -> Add

We just had a similar problem and noted that we could use UNC-pathes to specify the source of a restore.

The restore is done by some account with rather restricted permissions and therefore it does not see the drive mappings you see.

To make it work in domain context, allowing read access to the backup file to the computer.

In other cases, when the machines aren't in the same domain giving read permission to the file to everyone might be the last resort.

Edit:

Good to know that there is a build in option to add backup devices.
For DBAs it might be the preferred way, developers in restricted environments have sometimes to try other options depending on the rights they where given.

For the German version of SSMS. You find the tool mentioned by mKorbel at

SQL Server Management Studio -> Server Objects -> Sicherungsmedien -> Neues Sicherungsmedium...


Getting Ranked Position - SQL - Simple Query I have a very simple database structure and am struggling to write a command to find the ranked position of the user.

------------------
id     |  average
------------------
1      |  79
------------------
2      |  99
------------------
3      |  22

How would I be able to find the ranked position of the user with id 1? #2 would be ranked 1st. I want the rank to be descending with the highest number in 1st place.

If you are in version 8 (or above), you can use the RANK() function for this purpose:

SELECT
    id,
    average,
    RANK() OVER (ORDER BY average DESC) as rk
FROM
    t;

Note I've ordered the result by average DESC, that assigns rank=1 to the maximum average.

id | average | rk
-: | ------: | -:
 2 |      99 |  1
 1 |      79 |  2
 3 |      22 |  3

db<>fiddle here


Import XML data while creating IDs with relationships I need to parse XML from a file into tables while retaining their relationships through custom-generated ID values.

For example, if I had the following XML:

<root>
   <construction>
      <constructionName>randomname1</constructionName>
      <project>
         <projectname>another randomname</projectname>
         <businesspartners>
            <partnername>bilbo bagginses</partnername>
         </businesspartners>
         <employees>
            <employee>
               <empname>frodo</empname>
               <empaddress>etc...</empaddress>
            </employee>
         </employees>
      </project>
      <info>
         <randElement></randElement>
      </info>
      <constructionType>houses</constructiontype>
   </construction>
   <construction>
      <...(etc, same as above, times n^10)/>
   </construction>
</root>

From that, I need to generate data to the following tables:

CONSTRUCTION (CONSTRUCTION_ID INT PRIMARY KEY
   , CONSTRUCTIONNAME VARCHAR..
   , CONSTRUCTIONTYPE VARCHAR.. )

PROJECT (PROJECT_ID INT PRIMARY KEY
   , CONSTRUCTION_ID INT FOREIGN KEY REFERENCES CONSTRUCTION
   , PROJECTNAME VARCHAR.., )

BUSINESSPARTNERS (BUSINESSPARTNERS_ID INT PRIMARY KEY
   , PROJECT_ID INT FOREIGN KEY REFERENCES PROJECT
   , PARTNERNAME VARCHAR..)

etc...

Basically, the idea is to construct tables with their references intact to represent the XML. The table structures already exist and cannot be changed to accommodate this script. This is simply a matter of being able to do the same kind of XML parsing and then adding the data to the tables while generating the correct referencing ID values, as we did before with integration. Only doing this exclusively with SQL Server, and without using SSIS.

Now, the actual scenario and the file in question is rather massive, so I do by no means expect a full answer. Just tips on where to start looking. I'm quite inexperienced with dealing with XML.

For now, my preferred solution would be just importing the data with OPENROWSET, and then using dynamic SQL with OPENXML to parse the document into the tables by basically looping the elements and their sub-elements one at a time. But that seems a lot more trouble than some other, smarter way.

  How are the ID values generated?

That's kind of part of the question. For now, they aren't. The idea is that each CONSTRUCTION element would be assigned an ID increasing from 1. Then all the children of CONSTRUCTION would then refer to the same ID assigned to the parent, and so on. Basically, it's just about dividing the data in the XML into multiple tables while keeping the referential integrity intact.

I would have no problem using an identity column as a primary key, so long as the relations aren't mixed up as a result. I don't know how to go about doing this, so I assumed one would have to somehow manually define the logic for creating the ID's, as opposed to identity?

You can use a variant of the technique described in Using merge..output to get mapping between source.id and target.id and by Adam Machanic in Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE.

You use merge and capture the generated ID with the XML fragment that belongs to that ID in a table variable and then use that table variable when adding rows to the child tables.

declare @C table
(
  CONSTRUCTION_ID int primary key,
  PROJECT xml
);

merge CONSTRUCTION as T
using (
      select T.X.value('(constructionName/text())[1]', 'varchar(30)') as CONSTRUCTIONNAME,
             T.X.value('(constructionType/text())[1]', 'varchar(30)') as CONSTRUCTIONTYPE,
             T.X.query('project') as PROJECT
      from @xml.nodes('/root/construction') as T(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (CONSTRUCTIONNAME, CONSTRUCTIONTYPE)
  values (S.CONSTRUCTIONNAME, S.CONSTRUCTIONTYPE)
output inserted.CONSTRUCTION_ID, S.PROJECT into @C;

declare @P table
(
  PROJECT_ID int primary key,
  BUSINESSPARTNERS XML
);

merge PROJECT as T
using (
      select C.CONSTRUCTION_ID,
             T.X.value('(projectname/text())[1]', 'varchar(30)') as PROJECTNAME,
             T.X.query('businesspartners') as BUSINESSPARTNERS
      from @C as C
        cross apply C.PROJECT.nodes('/project') as T(X)
      ) as S
on 0 = 1
when not matched by target then
  insert (CONSTRUCTION_ID, PROJECTNAME)
  values(S.CONSTRUCTION_ID, S.PROJECTNAME)
output inserted.PROJECT_ID, S.BUSINESSPARTNERS into @P;

insert into BUSINESSPARTNERS(PROJECT_ID, PARTNERNAME)
select P.PROJECT_ID,
       T.X.value('text()[1]', 'varchar(30)')
from @P as P
  cross apply P.BUSINESSPARTNERS.nodes('/businesspartners/partnername') as T(X);

SQL Fiddle


502 error after restarting mongo db in remote server using fork command I was trying to upgrade mongo 3.4.20 to 4.0.14 , but it lead to the system crash, so i tried reverting it back to 3.4.20. But after doing db restart using fork , there 502 error. Please help.I tried the following command
sudo mongod --fork --auth --logpath /var/log/mongodb/mongod.log

I could finally get it to work by executing following command  :

sudo mongod --fork --auth --port 27017 --dbpath /var/lib/mongodb --logpath /var/lib/mongodb/mongod.log


Postgres pgpool increase num_init_children I am trying to tune postgre to allow many concurrent queries from clients as i am checking how many parallel connections with there queries it can handle. I read A good PgPool II configurataion
  , but what really can help to handle many concurrent connections with queries is to set up (num_init_children = 1000 , for example 1000 parallel queries )

If i set up num_init_children = 2000 server fails to start.
My pgpool config is:

num_init_children = 1000
max_pool = 4
child_life_time = 120
child_max_connections = 100
connection_life_time = 120
client_idle_limit = 10

How can i increase num_init_children or tune some parameters to allow 2, 5 or 10k parallel queries at the same time, cause it's really frustrating that postgre stucks with more that 1000 queries?

You're increasing num_init_children, but there's no sign you've set the PostgreSQL server backend's max_connections high enough to allow that number of connections from PgPool.

Please see the PgPool-II documentation to learn more about configuring PgPool-II. You might find this article useful.

You really don't want thousands of parallel queries to the backend database. What you need is for PgPool to queue them up and send them a few hundred at a time to PostgreSQL. Otherwise you'll tend to see terrible performance; see number of database connections in the Pg wiki. So you need to limit the number of back-end connections PgPool-II creates, while allowing lots of front-end connections. That means you want a low num_init_children. Personally I'd use something like:

num_init_children = 100
max_pool = 1

because, per the documentation:

  If more than num_init_children clients try to connect to pgpool-II, they are blocked (not rejected) until a connection to any pgpool-II process is closed.

This is what you want for good performance.

That said, I use PgBouncer for this. I find it much simpler to work with, and it tends to perform very well. It also offers transaction-pooling mode, which can be really useful when you have applications that don't close connections, instead leaving them idle for ages. If you're not using PgPool's cluster features I strongly recommend just using PgBouncer, it's way simpler.


Sum of table sizes don't match with mdf size I just ran a disk usage report on my database and found it's grown a lot over the past month and is currently using 3.98 GB on data (it was around 300 MB on last month)

However, when I run the disk usage by tables report I see that the space usage from the tables are currently very far from 3.98 GB

How can I find what is using the remaining space so that it adds up to 3.98 GB?

EDIT

This is the screenshot for the Shrink file dialog:

There are "internal" tables, queues, etc that don't show up in certain reports / queries as they are system objects. If you run the query below, which counts the number of actual pages defined in the data files in the current Database, you should see that one of the "Reserved" fields (match the appropriate "KB", "MB", or "GB" field to your report) matches the "Space Used" value in the Data File portion of that report.

;WITH cte AS
(
  SELECT COUNT(*) AS [Reserved],
         SUM(CASE WHEN pa.[is_allocated] = 1 THEN 1 ELSE 0 END) AS [Allocated]
  FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
)
SELECT  [Reserved] AS [ReservedPages],
        [Allocated] AS [AllocatedPages],
        '---' AS [---],
        ([Reserved] * 8) AS [ReservedKB],
        ([Allocated] * 8) AS [AllocatedKB],
        '---' AS [---],
        ([Reserved] * 8) / 1024.0 AS [ReservedMB],
        ([Allocated] * 8) / 1024.0 AS [AllocatedMB],
        '---' AS [---],
        ([Reserved] * 8) / 1024.0 / 1024.0 AS [ReservedGB],
        ([Allocated] * 8) / 1024.0 / 1024.0 AS [AllocatedGB]
FROM    cte;

And below is the query to get the per-object details along with a grand total line at the top:

SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
       COUNT(*) AS [ReservedPages],
       (COUNT(*) * 8) AS [ReservedKB],
       (COUNT(*) * 8) / 1024.0 AS [ReservedMB],
       (COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
        ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;


Specify a frequency range for a SQL agent schedule I want to create a SQL agent schedule that occurs during a date range. I can't use "recurs every n weeks" because the process that it will fire off will change with each month. I need to set the frequency to be akin to occurs monthly, 1st Monday through the 2nd Friday of every 1 month. That way I am assured to be only running the process starting with the first full week of every month

Am I crazy for thinking of a schedule like this? Shirley(sic) I can't be the only one. I have looked at sp_add_schedule for any hints, none to be found there. I have tried Googling, but I can't come up with the right query syntax thus keep coming up empty.

Perhaps if one could help me come up with the correct question, that could lead me in the right direction.

::Update1: I am going to add date logic to the script.

How about  mutiple, recurring Monthly Schedules where you have selected the first Monday of every 1 month on Schedule 1, the first Tuesday of every 1 month on Schedule 2, ... the second Friday of every 1 month?  See the following pic to get the idea of what I'm thinking:


SQL Server 2017 Distributed Availability Groups My Environment is SQL Server 2017 Distributed Availability Groups, details as follows:

Configured SQL Distributed AG from DC1 to DC2.

DC1 (Production): TEMPDB1 AND TEMPDB2 (HOSTED ON WSFC1)
DC2 (DR): TEMPDB3 AND TEMPDB4 (HOSTED ON WSFC2)
DC1 and DC2 located in two different Geo location
DC1 is unavailable (shutdown).

Please suggest on following scenarios:

What are the steps/commands need to execute on DC2 to bring as a Production?
Once DC1 is up/running, what are the steps/commands need to execute in DC2 and DC1 to bring DC1 as a Production?

Here are the screenshots of status from 2 DC's

DC1-AG Status before DC1 shutdown:

DC2-AG Status before DC1 shutdown:

DC2-AG Status After DC1 shutdown:

After DC1 shutdown, tried to access DC2-DB1, got blow error.

Changed DC2-AG2 properties as mentioned below (Allow R/W Connections and Readable Secondary=Yes)

After changing properties, I am able to access DB1 but not able to perform any DML operations in DC2-DB1.

This DB should allow for all operations because app is pointing DC2-DB during DC1 shutdown.

Please suggest/advise on this.

After DC 1 is down, since your secondary availability group [distributedag] is asychronous and is in manual failover mode, it is still in secondary role, so you will need to connect to [distributedag] listener and execute command:

USE master
ALTER AVAILABILITY GROUP [distributedag] FORCE_FAILOVER_ALLOW_DATA_LOSS

This will force your secondary AG at DC2 to become primary, and bring it into a read-write state


MySQL Replication after a set period of time I have a question about MySQL replication: can I set the slave DBs to only poll the master DB for information once every 10 seconds?

Our MySQL master DB is under fairly heavy load. By default, MySQL replicates in almost real time, which means that each slave DB is polling the master DB for changes many times in one second. I don't want the master DB to be bogged down by the update / delete load as well as the constant requests of slaves to get the latest changes.

In our situation, we don't need the slave DB to be synced in real-time to the master. The slaves can each afford to be 10 seconds behind the master and the clients who are using the slave DBs won't even notice. Is there a way I can set the slaves to poll the master every 10 seconds instead of in real-time?

Note: setting the MASTER_DELAY=N setting will not work

The MASTER_DELAY setting tells the slave to intentionally stay N seconds behind the master. However, it doesn't affect how often the slave polls the master for changes. In my case, setting the MASTER_DELAY setting won't do any good bc I want the slaves to poll the master less, not more.

I suggest it is not a real problem.  I have worked with a Master that had about 20 slaves, each pulling upwards of a million queries per day.  The polling and network issues were minor.

But if you must...  Have a job that does this forever:

repeat forever
    START SLAVE;
    sleep 0.1 sec
    STOP SLAVE;
    sleep 10 sec
end-loop

To make it more robust, adjust the sleeps if Seconds_behind_master gets "too high".


What's the most efficient way to batch UPDATE queries in MySQL? I'm writing an application that needs to flush out a large number of updates to the database for an extended period of time, and I've gotten stuck at how to optimize the query. Currently I'm using INSERT INTO ... VALUES (..), (..) ON DUPLICATE KEY UPDATE, which works to batch all of the values into one query, but executes excruciatingly slowly on large tables. I don't ever actually need to insert rows.

Other approaches I've seen are to update using SET value = CASE WHEN... (which would be hard to generate due to the way I'm building the queries, and I'm not sure about the performance of CASE for hundreds/thousands of keys), and simply multiple concatenated updates. Would either of these be faster than my current method?

It baffles me that, as far as I can tell, there's no idiomatic, efficient way to do this in MySQL. If there really isn't a way that's faster than ON DUPLICATE KEY, would it be worth it to switch to PostgreSQL and use its UPDATE FROM syntax?

Any other suggestions are also greatly appreciated!

Edit: here's one of the tables that gets updated frequently. I've removed column names due to them being irrelevant.

CREATE TABLE IF NOT EXISTS `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` bigint(20) unsigned NOT NULL DEFAULT '0',
  `b` bigint(20) unsigned NOT NULL DEFAULT '0',
  `c` enum('0','1','2') NOT NULL DEFAULT '0',
  `d` char(32) NOT NULL,
  -- trimmed --
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`),
  KEY `c` (`c`),
  KEY `d` (`d`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Since you're using InnoDB tables, the most obvious optimization would be to group multiple UPDATEs into a transaction.

With InnoDB, being a transactional engine, you pay not just for the UPDATE itself, but also for all the transactional overhead: managing the transaction buffer, transaction log, flushing the log to disk.

If you are logically comfortable with the idea, try and group 100-1000 UPDATEs at a time, each time wrapped like this:

START TRANSACTION;
UPDATE ...
UPDATE ...
UPDATE ...
UPDATE ...
COMMIT;

Possible downsides:

One error will collapse the entire transaction (but would be easily fixed in code)
You might wait for a long time to accumulate your 1000 UPDATEs, so you might also want to have some timeout
More complexity on your application code.


Configure SQL Server to associate a user login with a specific port We have SQL Server instances that sit in our DMZ, and associated firewall rules to allow access to them from our corporate network.

The server is correctly listening on the configured ports, but now I need to configure the RDBMS to grant access to specific users on a specific port.

Apparently there is a T-SQL script that will do what I need, but I can't find it anywhere. Can anyone point me in the right direction or alternatively, show me how to do this using Management Studio's GUI?

See the instructions at:

Configure the Database Engine to Listen on Multiple TCP Ports

To set up a new TDS endpoint a code sample is:

USE master;
GO
CREATE ENDPOINT [CustomConnection]
STATE = STARTED
AS TCP
   (LISTENER_PORT = 1500, LISTENER_IP =ALL)
FOR TSQL() ;
GO

The side-effect of creating a Custom Connection is that you will need to reestablish the Default Connection:

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] to [public];

Then to grant access to the CustomConnection endpoint to the SQLSupport group (as an example), you could use something like this:

GRANT CONNECT ON ENDPOINT::[CustomConnection] to [corp\SQLSupport] ;
GO

Read the other details in that post that may need configuring.


Log debug messages in a SQL CLR C# Stored Procedure I have developed a SQL CLR stored procedure in C#, for SQL Server 2012.

I want to log messages to SQL Server registers. On a normal stored procedure I use xp_logevent

What can I use to log a message in a SQL CLR stored procedure?

Why not just execute xp_logevent via SqlContext.Pipe.ExecuteAndSend() in your catch block?


MySQL - [Note] Got an error reading communication packets I have deployed MySQL on OpenShift (kubernetes) with GlusterFS as persistent storage and i am getting following error after every 2 seconds in MySQL Container logs

2020-01-17T18:00:04.787586Z 1541 [Note] Got an error reading communication packets
2020-01-17T18:00:06.787610Z 1543 [Note] Got an error reading communication packets
2020-01-17T18:00:08.787548Z 1544 [Note] Got an error reading communication packets
2020-01-17T18:00:10.787586Z 1546 [Note] Got an error reading communication packets
2020-01-17T18:00:12.787575Z 1547 [Note] Got an error reading communication packets

I tried by changing below variables but no success

set @@global.max_connections = 400;
max_allowed_packet=256M
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M
SET GLOBAL thread_cache_size=50;

i tried to set SET GLOBAL log_warnings=3; for details logging but nothing is showing

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using  EditLine wrapper

Internet is full of tips about how to solve this error message, but the usual solutions are tweaking the various timeout and max_allowed_packet settings. However, changing these settings made no difference to me, the error kept happening infact now i shut all application and only MySql is running but still error is repeating. Although my services are not hampering but i have implemented Centralized logging and this log is flooding it.

I also run mysqltuner but its suggestion are for query cache only and tunning it has no effect

https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/

So if there is not client why MySQL is showing this message as no reading or writing happening??

Extra Details:

MySql Starting logs as they start popping as soon as mysql start
https://gist.github.com/imranrazakhan/d5ccca11c840b3229bf2cedb1a2deebb
SHOW GLOBAL STATUS
https://gist.github.com/imranrazakhan/5b0f06529916435e5df71aebdb8062c0
SHOW GLOBAL VARIABLES;
https://gist.github.com/imranrazakhan/2bf9cadd5d1ae0e350feeecd3a984cef
SHOW FULL PROCESSLIST;

mysqltuner output;
https://gist.github.com/imranrazakhan/71e4638b638364a939ac1410765188ee
ulimit -a;

https://gist.github.com/imranrazakhan/cadf78df1d09e4ad11676d51ec02839b
MySQL Conf
https://gist.github.com/imranrazakhan/5ec96905048758eb49cf7ac6140bdfff

After spending lot of time I am able to resolve my issue and reason is that in Openshitf/kubernetes we have section to verify readiness of mysql like

  readinessProbe:
    tcpSocket:
      port: 3306
    initialDelaySeconds: 20
    periodSeconds: 2
    timeoutSeconds: 1

So it was pinging every two seconds on 3306 port, i changed it to below and its resolved the issue,

 readinessProbe:
        exec:
          command:
          - sh
          - -c
          - "mysqladmin ping -u root -p${MYSQL_ROOT_PASSWORD}"
        initialDelaySeconds: 5
        periodSeconds: 2
        timeoutSeconds: 1


Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL I had to write a simple query where I go looking for people's name that start with a B or a D :

SELECT s.name
FROM spelers s
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1

I was wondering if there is a way to rewrite this to become more performant. So I can avoid or and / or like?

Your query is pretty much the optimum. Syntax won't get much shorter, query won't get much faster:
SELECT name
FROM   spelers
WHERE  name LIKE 'B%' OR name LIKE 'D%'
ORDER  BY 1;

If you really want to shorten the syntax, use a regular expression with branches:
...
WHERE  name ~ '^(B|D).*'

Or slightly faster, with a character class:
...
WHERE  name ~ '^[BD].*'

A quick test without index yields faster results than for SIMILAR TO in either case for me.
With an appropriate B-Tree index in place, LIKE wins this race by orders of magnitude.
Read the basics about pattern matching in the manual.
Index for superior performance
If you are concerned with performance, create an index like this for bigger tables:
CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops);

Makes this kind of query faster by orders of magnitude. Special considerations apply for locale-specific sort order. Read more about operator classes in the manual. If you are using the standard "C" locale (most people don't), a plain index (with default operator class) will do.
Such an index is only good for left-anchored patterns (matching from the start of the string).
SIMILAR TO or regular expressions with basic left-anchored expressions can use this index, too. But not with branches (B|D) or character classes [BD] (at least in my tests on PostgreSQL 9.0).
Trigram matches or text search use special GIN or GiST indexes.
Overview of pattern matching operators

LIKE (~~) is simple and fast but limited in its capabilities.
ILIKE (~~*) the case insensitive variant.
pg_trgm extends index support for both.

~ (regular expression match) is powerful but more complex and may be slow for anything more than basic expressions.

SIMILAR TO is just pointless. A peculiar halfbreed of LIKE and regular expressions. I never use it. See below.

% is the "similarity" operator, provided by the additional module pg_trgm. See below.

@@ is the text search operator. See below.

pg_trgm - trigram matching
Beginning with PostgreSQL 9.1 you can facilitate the extension pg_trgm to provide index support for any LIKE / ILIKE pattern (and simple regexp patterns with ~) using a GIN or GiST index.
Details, example and links:

How is LIKE implemented?

pg_trgm also provides these operators:

% - the "similarity" operator
<% (commutator: %>) - the "word_similarity" operator in Postgres 9.6 or later
<<% (commutator: %>>) - the "strict_word_similarity" operator in Postgres 11 or later

Text search
Is a special type of pattern matching with separate infrastructure and index types. It uses dictionaries and stemming and is a great tool to find words in documents, especially for natural languages.
Prefix matching is also supported:

Get partial match from GIN indexed TSVECTOR column

As well as phrase search since Postgres 9.6:

How to search hyphenated words in PostgreSQL full text search?

Consider the introduction in the manual and the overview of operators and functions.
Additional tools for fuzzy string matching
The additional module fuzzystrmatch offers some more options, but performance is generally inferior to all of the above.
In particular, various implementations of the levenshtein() function may be instrumental.
Why are regular expressions (~) always faster than SIMILAR TO?
The answer is simple. SIMILAR TO expressions are rewritten into regular expressions internally. So, for every SIMILAR TO expression, there is at least one faster regular expression (that saves the overhead of rewriting the expression). There is no performance gain in using SIMILAR TO ever.
And simple expressions that can be done with LIKE (~~) are faster with LIKE anyway.
SIMILAR TO is only supported in PostgreSQL because it ended up in early drafts of the SQL standard. They still haven't gotten rid of it. But there are plans to remove it and include regexp matches instead - or so I heard.
EXPLAIN ANALYZE reveals it. Just try with any table yourself!
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO 'B%';

Reveals:
...
Seq Scan on spelers  (cost= ...
  Filter: (name ~ '^(?:B.*)$'::text)

SIMILAR TO has been rewritten with a regular expression (~).
Ultimate performance for this particular case
But EXPLAIN ANALYZE reveals more. Try, with the afore-mentioned index in place:
EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ '^B.*;

Reveals:
...
 ->  Bitmap Heap Scan on spelers  (cost= ...
       Filter: (name ~ '^B.*'::text)
        ->  Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ...
              Index Cond: ((prod ~>=~ 'B'::text) AND (prod ~<~ 'C'::text))

Internally, with an index that is not locale-aware (text_pattern_ops or using locale C) simple left-anchored expressions are rewritten with these text pattern operators: ~>=~, ~<=~, ~>~, ~<~. This is the case for ~, ~~ or SIMILAR TO alike.
The same is true for indexes on varchar types with varchar_pattern_ops or char with bpchar_pattern_ops.
So, applied to the original question, this is the fastest possible way:
SELECT name
FROM   spelers
WHERE  name ~>=~ 'B' AND name ~<~ 'C'
    OR name ~>=~ 'D' AND name ~<~ 'E'
ORDER  BY 1;

Of course, if you should happen to search for adjacent initials, you can simplify further:
WHERE  name ~>=~ 'B' AND name ~<~ 'D'   -- strings starting with B or C

The gain over plain use of ~ or ~~ is tiny. If performance isn't your paramount requirement, you should just stick with the standard operators - arriving at what you already have in the question.


Change the database from restoring state to standby mode I have a SQL Server 2012 database that must be always in 'standby' mode because I must restore transaction logs from a vendor database to my local database on stand by restoring option.

It has been around 3 months since I started to download log backups from vendor's sFTP server and restore everyday to my database (in standby mode). Today, in the middle of restoring, SSMS crashed and the application closed immediately, which left the database in restoring mode.

I don't want to work all over again from the beginning. How can I change the database to standby mode and keep the sequence of LSNs; from the last LSN restored to next LSN of log backup file?

My database has no log shipping configuration between two servers, it is just a target database.

The way I solved is (in case it helps others): I first identified the last log file restored from the database properties (shows by date) and continued to restore on standby mode starting from the last log file then works perfect!


Is there any drawback in altering an existing column datatype from TEXT to VARCHAR(n)? I have an existing column whose data type is TEXT. Now I realized that the values are just single strings. So, the data type VARCHAR(n) should have been proper in my case. So, I wanted to change the data type from TEXT to VARCHAR(n).

Now my question is:

I came to know that in case of TEXT data type, internally, the data will be stored in another place and a pointer will be saved in the table.
Now if I want to change the data type from TEXT to VARCHAR(n), will the data be copied into the table?

Basically,I am interested how data migration will take place internally and its possible side effect.

Note: The column is not used as index.

The only risk is truncation of data.

Effectively the ALTER will

Create a new table with VARCHAR instead of TEXT.
Copy all the rows over.
Rename to put the new table in place of the original.

So, the question is, will copying a TEXT into a VARCHAR of the same CHARACTER SET do any harm?  The only possible thing is truncation.


Is MongoDB index creation single threaded? I have rather big dataset - 2TB of text data, 18.000 millions of lines.

Each document is same: 5 fields: 4 strings + 1 integer.

All fields should be indexed and searched by.

That is: array of 3 string tokens, 1 separate string, 1 integer.

"t" : ["param","pam","tadam"],
"p" : "haha",
"f" : 3062632

I tried two ways:

to import, then create index
to create index, then import

Mongo journal is disabled, XFS, CentOS7.6, MongoDB 4.0.6
Tried very beefy server (72core, 144GB RAM, 6X SSD RAID)

Speed was above million inserts in second. After insertion, which took less then a day, I started foreground indexing. I've set this parameter maxIndexBuildMemoryUsageMegabytes=10000

Command is:

db.runCommand( { createIndexes: "records", indexes: [
                { key: { "f" : 1 }, name: "find" },
                { key: { "p" : 1 }, name: "pind" },
                { key: { "t" : 1 }, name: "tind" }
    ]
  }
)

it applies fine, because mongo reports that "using bulk method; build may temporarily use up to 3333 megabytes of RAM"

speed is terrible, saturation of hardware is close to zero.
1 cpu core at 100%
SSD at 3%

If I create index prior to inserting, speed starts from 100k inserts, but fast drops to single thousands of records per second at 200-300 million documents.

Can this problem be fixed with MongoDB?
Or another DBMS?

One cave: I need all dataset on one server, no clusters.

There is an OPEN issue already created in MongoDB JIRA for the same.

As per the issue, MongoDB index creation is not Multi-Threaded.

use multiple cores for index sort-phase

Multi-threaded index creation

UPDATE

Try increasing the maxIndexBuildMemoryUsageMegabytes parameter value.

The default value is 500 MB.

What does it do?

Limits the amount of memory that the simultaneous foreground index builds on one collection may consume for the duration of the builds.

So by increasing this limit, may increase the performance of index creation. For Example:

db.adminCommand( { setParameter: 1, maxIndexBuildMemoryUsageMegabytes: 70000 } )


Trigger - Add to a date using a subquery (postgresql 9.2)
I'v 2 tables

Table 1 (extinguisher)

extinguisher_id (serial)
type_designation_extinguisher_type(text)
manufacturing_date (date)
life_date(date)

Table 2 (extinguisher_designation)

extinguihser_type(serial)
extinguisher_designation(text)
lifespan(int)

lifespan can take values like: 10 or 20 and it represents years

My objective is to do a trigger that will automatically populate table1.life_date based on the extinguisher_type using table2.

The problem is that lifespan is an int and can change.
The usual way to add year to a date is using
date + interval '10 year'

The problem is that I want a variable instead of the 10.

At this point i'v this:

CREATE OR REPLACE FUNCTION ext_life_span()

RETURNS trigger AS

$BODY$

DECLARE
new_date timestamptz;
manu_date char(10);
intervalo char(10);
BEGIN
IF (TG_OP = 'INSERT') THEN
intervalo := to_char((SELECT lifespan FROM extinguisher_type, extinguisher where type_designation = type_designation_extinguisher_type), '99999');
manu_date := to_char(New.manufacturing_date, 'YYYY-MM-DD');
new_date := (date 'manu_date' + integer 'intervalo' );
update extinguisher set life_date = new_date;
RETURN NULL;
ELSIF (TG_OP = 'UPDATE') THEN
IF new.manufacturing_date IS DISTINCT FROM old.manufacturing_date THEN
intervalo := to_char((SELECT lifespan FROM extinguisher_type, extinguisher where type_designation = type_designation_extinguisher_type), '99999');
manu_date := to_char(New.manufacturing_date, 'YYYY-MM-DD');
new_date := (date 'manu_date' + integer 'intervalo' );
update extinguisher set life_date = new_date;
END IF;
RETURN NULL;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION ext_life_span() OWNER TO postgres;

CREATE TRIGGER insert_lifespan
AFTER INSERT OR UPDATE OF manufacturing_date
ON "extinguisher"
FOR EACH ROW
EXECUTE PROCEDURE ext_life_span();

I'm getting an error: invalid input syntax for type date: "menu_date"

Am I doing the best approach? Is there an easier way to do it?

You can easily use a variable when doing something like this:

menu_date := menu_date + interval '1' year * num_years

Where num_years holds the number of years that should be added.

You get the error because date 'manu_date' is an incorrect date literal because the string (!) 'manu_dat' is not a valid date. The expression date 'xxxx' requires a date definition in ISO format e.g. date '2013-09-27'

Regarding your trigger:

The statement update extinguisher set life_date = new_date; will update all rows in the table extinguisher. I'm pretty sure that is not what you want. You are missing a where condition for the update.

You also cannot run an update on the table on which the trigger is fired. You simply assign the value to the new record.

I although I absolutely don't understand what you intend with your trigger, I think what you want is much simpler:

CREATE OR REPLACE FUNCTION ext_life_span()
  RETURNS trigger AS
$BODY$
DECLARE
    l_lifespan integer;
BEGIN

  -- note that this can fail, if type_designation is not unique!!!
  SELECT lifespan
    into l_lifespan
  FROM extinguisher_type
  WHERE type_designation = new.type_designation_extinguisher_type;

  new.life_date := new.manufacturing_date + interval '1' year * l_lifespan;

  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Edit: depending on your data volumn, you could also get rid of the trigger and the life_date column alltogether and simply calculate that value "on-the-fly" using a view:

select ex.extinguisher_id,
       ex.manufacturing_date,
       ex.manufacturing_date + interval '1' year * ed.life_span as life_date
from extinguisher ex
  join extinguisher_designation ed
       -- I'm not sure I understand your data model correctly.
       -- you need to setup a proper FK here e.g. by using the generated ID
    on ed.extinguihser_type = ex.extinguihser_type

That way the value will always be up-to-date and you never need to worry about triggers.

In a relational database, values that can be derived from existing values should not be stored.


Can You Replicate Sequences In SQL 2012 can you replicate sequence objects, and if so do changes to the sequence (including consumption of values) get replicated?

If not is there a good reason why this was not included?

No, sequences are not valid as articles for any type of replication.

I don't have a good reason for you (yet). But I'm curious what the purpose of replicating the sequence itself would be. If your intention is to utilize the same sequence on the subscriber to populate other tables there that aren't replicated, this wouldn't really work unless you had some way to notify the source sequence that you've used some values. You could always call a stored procedure on the publisher that generates a new sequence value there, and use it on the subscriber.


xml parameter to distributed query - difference between SQL2005 & SQL2012 I have inherited an old SQL2005 Enterprise database (on Windows 2003) that allows an xml parameter to a distributed query such as this:

exec sp_executesql 'exec [server].[database].dbo.sproc @Id=@Id,@PItems=@PItems output','@PId bigint, @PItems xml output', valueOf@PId, valueOf@PItems output

This no longer works in SQL2012, the error says:

  Msg 9512, Level 16, State 1, Line 1 Xml data type is not supported as
  a parameter to remote calls.

My question: is there a permission or tickbox (facet?) to allow xml parameters to distributed queries or does 2012 no longer support it?

Note: the tables do not have any columns of datatype xml. Only the parameter is xml.

It's not supported, but you can get around it..

select * from openquery(
 LinkedServerName,
 'exec [database].dbo.sproc @Id=@Id,
                       @PItems=@PItems output'',''
                       @PId bigint,
                       @PItems xml output'',
                       valueOf@PId,
                       valueOf@PItems output'
)

There are many pitfalls. which are well explained here, as you are on 2012 you will want to look into executing the remote query with results set. some version of this.

SELECT * FROM OPENQUERY(LinkedServerName,
          'EXEC msdb..sp_helpindex sysjobs
           WITH RESULT SETS ((index_name        sysname,
                              index_description nvarchar(500),
                              index_keys        nvarchar(500)))')


Disable mysql_tzinfo_to_sql during MariaDB/MySQL installation MariaDB run in docker container hangs during initial startup while mysql_tzinfo_to_sql running because of the issue.

I've disabled mysql_tzinfo_to_sql by setting MYSQL_INITDB_SKIP_TZINFO env. var to 1.

Afterwards i manually ran:

mysql_tzinfo_to_sql /usr/share/zoneinfo | sed 's/Local time zone must be set--see zic manual page/FCTY/' | mysql -uuser -ppass --database=mysql

It takes ~12 minutes and script causes errors in MariaDB error log if MariaDB cluster is set.

I'm wondering what's the purpose of running mysql_tzinfo_to_sql during MariaDB installation and consequences of disabling it.

Found this link:

  MySQL provides developers with a rich set of Date and Time functions.
  One of the functions is CONVERT_TZ which converts a datetime value
  from one time zone to another. This function can use named time zones
  such as 'MET' or 'Europe/Moscow' from the time zone information tables
  in system database called mysql.

  Named time zones can be used only if the time zone information tables
  in the mysql system database have been created and populated. The
  MySQL installation procedure creates the time zone tables in the mysql
  database, but does not load them with data. You need to load data
  manually for these time zone tables.

It does answer the question.

However, i still don't fully understand practical implications of disabling populating mysql timezone system tables.

Why does timezone system tables population may take so long during initial MariaDB startup ?

Manual is pretty clear: loaded TZs enables a TZ-related functions. Say, with TZs loaded you can transform result's datetimes due to the client's TZ automatically, or calculate FROM_UNIXTIME() with respect to the leap seconds or daysaving times etc.


MySQL: Updating or closing existing rows in a table and/or inserting new rows? I want to update a specific table but in the following manner. When a record is completely the same (so not only the primary key, but all fields) then I don't want any changes (or a new record inserted, etc). It should ignore the record, because nothing has changed. But, when one of the fields of  in a record IS changed, then I want the existing record to be closed with an end date in the field end_date and an new record with a start date and a new follownumber (eg. 02). The end_date of the old record is the start_date of the new record. How do I get this done within MySQL? By hand (manually) and automatically (trigger?). Both would be nice to know. In the end automatically would be the best thing. But I want to understand what happens and why. I really hope someone can help me or show me the right direction. Thanks.

With 'by hand' I mean when I have a existing table and get a .csv file with possible updates. Put this in a temporary table of it's own and then with a sql statement I insert new records, close existing records (and then insert end_date, etc) that have an update record in the temporary table and then last insert the update records from the temporary table with a new subsequent follownumber. After all is done then I delete the temporary table.

If it's not completely clear, I've created a file of how the results should look in Excel: mysql_test_expert_updating.xlsx

I only use Excel as a presentation tool and use MySQL code to get the data from the table. So the Excel I've added is only to show how the content of the table should look...

Kind regards,

Vincent

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


How to connect to a Database made by Oracle SQL Database? So I am fairly new at this, so if you could keep that in mind in your answers, it would be much appreciated. I installed Oracle SQL Database on my Windows PC. It came in two zip files. I installed it and the online portion of it works fine. I can login with Username: sys and Password: **. What I am trying to do is connect to this newly created database on another computer through SQL Developer. I have read that in order to do this, you need to change the hostname of the Database from "localhost" to an IP Address. How do I do that and is there anything else I need to do to make this work?

I also found this LINK. Is this something I should do? I do not have a Domain though.

listener.ora

# listener.ora Network Configuration File:
C:\app\hyderz\product\11.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
   (SID_NAME = CLRExtProc)
   (ORACLE_HOME = C:\app\hyderz\product\11.2.0\dbhome_2)
   (PROGRAM = extproc)
   (ENVS = "EXTPROC_DLLS=ONLY:C:\app\hyderz\product\11.2.0\dbhome_2\bin\oraclr11.dll")
 )
)

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
  )
)

ADR_BASE_LISTENER = C:\app\hyderz

tnsnames.ora

# tnsnames.ora Network Configuration File:
C:\app\hyderz\product\11.2.0\dbhome_2\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
(CONNECT_DATA =
  (SID = CLRExtProc)
  (PRESENTATION = RO)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = orcl)
)
)

Try to connect with

 sqlplus SYS/PASSWORD@ORCL

If you can connect with this from command line you can connect with Sql Developer.

You have two options

Connection Type : Basic

SID = orcl
Username = SYS
Password = password
Hostname = YOUR_MACHINENAME_OR_IP

See Image

Connection Type : TNS

Choose SID from dropdownlist. It should read your TNS File and auto complete your dropdown list.
Username = SYS
Password = password

The catch here. Oracle Connection string  for OCI expect same argument in your tnsnames.ora file. If you use basic connection you should be able to connect from everywhere without modification. But if you need to connect with other connection types, you may need to enter your IP address in your tnsnames.ora file.


Mysql and money: precision problems with FLOAT(10,2) I have a table like this:

mysql> describe transactions;
+-----------------------+---------------+------+-----+---------+----------------+
| Field                 | Type          | Null | Key | Default | Extra          |
+-----------------------+---------------+------+-----+---------+----------------+
| id                    | int(11)       | NO   | PRI | NULL    | auto_increment |
| cost                  | float(10,2)   | YES  |     | NULL    |                |
+-----------------------+---------------+------+-----+---------+----------------+

I (naively?) thought that would be a good way to store amounts.
I am 3 days from release, and I discovered this:

mysql> UPDATE transactions SET cost = 162295.99 WHERE id = 14891;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from transactions where id = 14891;
+-------+---------------+
| id    | cost          |

+-------+---------------+
| 14891 |     162295.98 |
+-------+---------------+

...?!? I entered .99, not .98!
So:

Why is this doing it?
How do I get out of this problem?
Would decimal work? I tried with:

mysql> alter table transactions modify cost decimal(12,2) NULL DEFAULT NULL;
Query OK, 1120 rows affected, 119 warnings (0.14 sec)
Records: 1120  Duplicates: 0  Warnings: 119

mysql>

However I am getting scary warnings. With "show warnings" I get:

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1265 | Data truncated for column 'cost' at row 10  |
| Note  | 1265 | Data truncated for column 'cost' at row 369 |
| Note  | 1265 | Data truncated for column 'cost' at row 371 |
| Note  | 1265 | Data truncated for column 'cost' at row 378 |
| Note  | 1265 | Data truncated for column 'cost' at row 402 |
| Note  | 1265 | Data truncated for column 'cost' at row 403 |
+-------+------+---------------------------------------------

How do I see which one "row 10" is anyway?
KEY QUESTION: How do I get out of this hole safely?
(Rhetorical question bonus question) Why didn't this happen like 3 months ago, when fixing would have been easy?

Known problem with IEEE floats.  Use DECIMAL

To fix

add new column of correct data type
copy data over (check for conversion errors here)
drop bad column
rename new column to correct name

Why?  Murphy's Law.


What makes for a good index in this scenario? I have a pretty big temp table (~5 million rows) that I need to update a few fields on.

Here's an example update statement:

UPDATE T
SET CarModel =
    CASE
        WHEN CarModel = '350z' AND ModelYear = 2008 AND CarMake NOT LIKE '%DATSUN%' THEN '370z Nismo'
        ELSE CarModel
    END,
IsMidTierCar =
    CASE
        WHEN NumberOfCarsSold >= 500000 AND <= 1000000 THEN IsMidTierCar
        ELSE NULL
    END,
IsRareCarMakeModel =
    CASE
        WHEN CarModel = '350z' AND SpecialEditioName != '' AND ModelYear = 2008 THEN 1
        ELSE 0
    END
FROM #CarsTempTable AS T
WHERE IsRareCarMakeModel IS NULL

The catch is the IsRareCarMakeModel field is NULL for every single row in the temp table before this update runs. Right now the fastest time I see it run in is when I don't create any index on my temp table. Any other index combination I've tried runs in at least 5x as long (and usually I just end up killing it instead of waiting for it to finish.)

Is no index going to be the best I'll ever get because of the cardinality of the temp table's IsRareCarMakeModel field is extremely low therefor a table scan is always going to be the best course of action for the optimizer?

Ronaldo's comment got me thinking and the more I think about it, I would answer my own question as: if every row of a table needs to be read, then there is no way to order the way that data is stored in order to improve the read performance. In other words, there's no index that would make the update statement in my scenario any faster because every row needs to be read to be updated in that query (regardless of the fact that the query has a filter because the filter doesn't actually return any less rows than everything in the table.)


Automatically rotate mongo logs in mongod.conf file cureetly we are not having log rotater in my mongod.conf file.my version is mongodb3.4.3  how can i give log rotater in my mongod.conf file please check my conf file and give me suggestion where to add the log rotator in my conf fileand what to add

If you are using *nix system, you have few choices. You can use logrotate service and send SIGUSR1 signal to mongod/mongos process. Or you can change your config systemLog.logRotate: rename and again send SIGUSR1.

To rotate log you can give command db.adminCommand( { logRotate : 1 } ) too.

How you do it on the windows side, that I don't know.