Replace MySQL table with data from csv I have a CSV file on about 200 MB/1 mill rows, which is updated every night. I need the values from this file available in a MySQL table.

I get this working by using LOAD DATA INFILE filepath REPLACE INTO TABLE tablename.
This is fast and it keep my indexes.
The problem is that values which are no longer in the csv will not be deleted.

Another attempt has been to TRUNCATE the table, and then LOAD DATA INFILE filepath INSERT INTO TABLE tablename afterwards. This seems to be about 25% faster. But by doing this I've had a lot of trouble with indexes being disabled(?) or stopped working. I've been reading on the MySQL docs and SO for some days now, but I can't figure that out.

My next attempt has been similar to the first. I added a column "update", and set every row to update=0 before doing a REPLACE INTO with a SET update=1, and then delete all remaining rows with update=0. This is my current method.

My question is this: Which method is considered better for completely replace a table with info from csv, but keep/refresh indexes. I'd appreciate any advice on the subject.

I am using the MyISAM engine.

When populating the data there are a number of issues you may run in to. The data size itself is not huge, but more inconvenient... you could choose any of the mechnisms for populating it including regular selects.

The right way to be doing it is with the TRUNCATE followed by the LOAD. If you are having problems with indexes then you should take that up with the MySQL folk, it is a bug, and it should be working.

If you temporarily need a work around for it then I recommend that you add a column to your table which will be unique for each LOAD cycle. A date may be a good candidate. You have already chosen a column 'update', which is a fine column to use.... but, instead of changing the data back to 0 each time, just increment it for each load, or use a number that matches the date like 20140110 for example. Then, after you LOAD the data you can:

DELETE from TABLE where update <> 20140110


Is it safe to initially rsync without pg_start_backup, provided you snapshot and rsync afterwards? Trying to think of ways to speed up slave creation without master downtime.

The normal way of doing is:

pg_start_backup
rsync database files
pg_stop_backup
rsync wal files and start slave

Since 2 can take a long time over the network, is it safe to:

rsync
pg_start_backup
rsync again
pg_stop_backup
rsync wal files and start slave

?

Yes, completely safe. The subsequent rsync performed under snapshot conditions will ensure that the data is properly aligned using the delta method.

One might want to ensure that there isn't an accumulation of irrelevant data files by using the --delete option (think carefully about what you are excluding) but this is a mostly separate issue.


How do I REPLACE values in column after querying I have a column with numeric values

I am querying

SELECT column
FROM table
WHERE column='1' OR
column='2' OR
column='3' OR
column='4'

THEN

REPLACE (column, "1", "ABC");
REPLACE (column, "2", "DEF");
REPLACE (column, "3", "GHI");
REPLACE (column, "4", "JKL")

I am not sure if I may be using the semicolons wrong.

Please help!

You can use CASE for what you're using the replace for. Also, I've modified the WHERE to be more streamlined;

SELECT
CASE
    WHEN column = '1' THEN 'ABC'
    WHEN column = '2' THEN 'DEF'
    WHEN column = '3' THEN 'GHI'
    WHEN column = '4' THEN 'JKL'
END AS column
FROM table
WHERE column IN ('1','2','3','4')

As a note, if column is an int then you don't need to wrap the values in ''

SELECT
CASE
    WHEN column = 1 THEN 'ABC'
    WHEN column = 2 THEN 'DEF'
    WHEN column = 3 THEN 'GHI'
    WHEN column = 4 THEN 'JKL'
END AS column
FROM table
WHERE column IN (1,2,3,4)

This won't modify the table at all, it will just change the way that the data is displayed.


MySQL replication - slave is continuously lagging behind master I am using MySQL-5.1.50 with a Master-slave replication setup.

Most of the time the slave is lagging behind the master.

When I run show processlist;, there is no query that's taking a long time.  I enabled slow_log as well. However, it does not find any slow running query.

The slave is continuously giving alerts that replication is seconds behind the master.  Sometimes, the lag time increases.

How do I diagnose the cause of the problem?

I need urgent help, since this problem has persisted for the last 20 days.

The Seconds_Behind_Master is really like viewing the past via time travel.

Think of it this way:

The Sun is 93,000,000 miles away from the Earth
The speed of light is 186,000 miles/sec
Simple division shows that it takes roughly 500 sec (8 min 20 sec) for the Sun's light to reach Earth
When you look at the Sun, you actually don't see the Sun. You see where it was 8 min 20 sec ago.

In like manner, it seems that the Master is processing a lot of queries at the same time.

You look back at the Slave, run SHOW SLAVE STATUS\G and it says 200 for Seconds_Behind_Master. How is that number calculated? Slave's Clock Time (UNIX_TIMESTAMP(NOW()) - TIMESTAMP of the Query when it was completed and recorded in the Master's Binary Log.

There is another metric to look at besides Seconds_Behind_Master. That metric is called Relay_Log_Space. That represents the sum of all bytes for all relay files on the Slave. By default, the largest single relay log is limited to 1GB. If Relay_Log_Space is less than 1GB, this indicates that many long running queries executed on the Master in parallel. Unfortunately, due to the single-threaded nature Replication's SQL thread, queries are executed one behind the other.

For example, suppose you have the following scenario on the Master:

Slow Query log is enabled
20 queries executed in parallel on the Master
Each query took 3 seconds
Each query get recorded in the Master Binary Log with the same timestamp

When the Slave reads those queries from its relay log and processes them one by one

the Slave's Clock will be moving
the TIMESTAMP for each of the 20 queries will be identical
the difference will increasing 3 seconds be completed query
this results in 60 seconds for Seconds_Behind_Master

Concerning the Slow Log, the default for long_query_time is 10 seconds. If all your queries in the relay logs are less than 10 seconds, you will never catch anything in the Slow Query Log.

I have the following recommendations for both Master and Slave servers

RECOMMENDATION #1 : Upgrade to MySQL 5.5. Under MySQL 5.5 and Percona Server 5.1.38+, you can tune InnoDB to access multiple CPUs. I have written past posts about this

Apr 26, 2012 : Is the CPU performance relevant for a database server?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

RECOMMENDATION #2 : Use InnoDB for all tables. InnoDB caches data and indexes in RAM, MyISAM only caches indexes.
RECOMMENDATION #3 : Increase RAM. You must cache more data and indexes on the Slave and Master alike
RECOMMENDATION #4 : Tune all queries. Reducing milliseconds from queries that run hundreds of times goes a long way to reduce Seconds_Behind_Master.

FURTHER TROUBLESHOOTING

If you want to see the queries causing the replciation lag, do the following:

SHOW SLAVE STATUS\G
Get name of relay log from Relay_Log_File
STOP SLAVE;
START SLAVE;
In the OS, cd /var/lib/mysql or wherever the relay logs are written
Dump the relay log to a text file

For example, Let's do SHOW SLAVE STATUS\G

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.64.51.149
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 1024035856
               Relay_Log_File: relay-bin.000030
                Relay_Log_Pos: 794732078
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: search_cache
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1024035856
              Relay_Log_Space: 794732271
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 106451149

If I run STOP SLAVE; START SLAVE;, the relay log closes and a new one is open. Yet, you want relay-bin.000030.

Dump the contents as follows:

cd /var/lib/mysql
mysqlbinlog relay-bin.000030 > /root/RelayLogQueries.txt
less /root/RelayLogQueries.txt

You can now see the queries the Slave is currently trying to process. You can use those queries as the starting point for tuning.


MySQL: Skip INSERT / UPDATE statement that triggers error but show them in output I have a million of rows of customers to be imported via SQL dump. Customer data will be inserted into main table customer and child table customer_address

The dump contains the following SQL statements:

File: customer-dump.sql

BEGIN TRANSACTION;
INSERT INTO customer (firstname, lastname) VALUES ('John', 'Doe');
INSERT INTO customer_address (customer_id, city, country) VALUES (LAST_INSERT_ID(), 'New York', 'USA');
COMMIT;

BEGIN TRANSACTION;
INSERT INTO customer (firstname, lastname) VALUES ('Lorem', 'Ipsum');
INSERT INTO customer_address (customer_id, city, country) VALUES (LAST_INSERT_ID(), 'Chicago', 'USA');
COMMIT;

# So on for N no of customers

I am importing via CLI as:

mysql -u [user] -p [database] < customer-dump.sql > /tmp/customer-import-error.log

My Queries:

How to continue the script execution even if there is a failure in some rows?
How to show/log errors for failed rows?
Any advice on such massive import?

it is 2 different problem

1st is possible errors, for this You can use --force option

mysql -u user -p --force database <

and 2nd is speed

for speed You can check server configuration and adjust it if necessary:

memory (innodb)
size of redo log file, default is too small, You can increase it

because You are already have dump file - no reason discuss about single transaction or group transactions.


How do I create a table with a column that uses a sequence? I have the following

CREATE TABLE [MyTable]
(
    [ID] [bigint] PRIMARY KEY NOT NULL,
    [Title] [nvarchar](64) NOT NULL
)

CREATE SEQUENCE MyTableID
    START WITH 1
    INCREMENT BY 1
    NO CACHE
    ;
GO

I want to insert new records on MyTable and set the ID to the next value of the sequence. How can I do it? A trigger perhaps, or is there some other way? How?

As I am using SQL Server 2012, I don't want to use Identity because of the gap bug.

Assign it as the default property for the column

CREATE TABLE [MyTable]
(
    [ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.MyTableID),
    [Title] [nvarchar](64) NOT NULL
);

Future readers, a Sequence can have a gap if the service stops unexpectedly, the value of the entities in CACHE can be lost. Here, they are specifying no cache to mitigate that with the tradeoff of slower performance for the sequence object.

CREATE SEQUENCE reference


Removing sp_execute_external_script Start-Up Lag On a freshly-restarted instance of SQL Server, the first time Python is executed using sp_execute_external_script, there's a lag before results are returned. After the initial Python script execution, future script executions executed shortly thereafter return much faster. However, if several minutes elapse before another script execution is attempted, the lag reappears.

I'm guessing that the lag is due to some aspect of the external script environment being started. The delay can't be due to query execution time as it is present even when the script invocation does not involve data access.

EXEC sp_execute_external_script
  @language = N'Python',
  @script = N'print("hello")';

If I'm correct that the lag has to do with script environment start up, is there an option to configure that environment to be pre-started when the SQL Server instance starts so that the lag is removed?

As of writing, there isn't a feature (that I've ever heard of) and I imagine anything in this area would just be implemented as a performance fix as the external scripting feature matures.

Two options in the meantime would be:

Run a dummy script at startup.
Quit rebooting your server! If this is a testing environment or one being spun up dynamically, speed probably isn't a priority. If it is production, you you might need to ask yourself why you're rebooting with any amount of regularity.


How to recover data from corrupted innoDB table from a crashed mySQL server? Some background:

I had a MySQL server running on Ubuntu Server. I don't remember which versions they were running but it was likely a Ubuntu 12.04 with MySQL 5.5. During the course of a day, connections from the client application to the server kept dropping. On the next day, the server crashed and mysql wouldn't start again. I was also getting lots of disk errors so I decided to attempt to recover the data on a new machine.

What I tried:

I tried to recover the data with a Ubuntu 12.04 running MySQL 5.5 but I couldn't get the service running. Before troubleshooting it, I tried the same steps on a Ubuntu Desktop running mySQL 5.1 that I had available, and they worked. Here's what I did:

I copied /var/lib/mysql from the crashed server, to the new one
I ran chown -R mysql:mysql /var/lib/mysql
I ran service mysql start

The service started and everything seemed to work, except on one table. This is a table with BLOB data.

After some trial and error, I narrowed the problematic queries to record ids above a certain number (roughly, the last 100 records of a total of 7000 records).

Sample of some queries and outputs:

> select field1 from table1 where field1 = 6900
> (completes successfully)
> select field1 from table1 where field1 <= 6900
> (completes successfully)
> select field1 from table1 where field1 = 6901
> Error 2013 (HY000) at line 1: Lost connection to mySQL server during query

Then, I started trying to dump the data in an attempt to recover it:

> mysqldump -u root -p database1 table1 > table1.sql
> Lost connection to mySQL server during query when dumping table 'table1' at row 6642

I looked into mysqldump's options and decided to try to add -f and the output was different:

> Couldn't execute 'UNLOCK TABLES': MySQL server has gone away (2006)

To make sure the problem's in this table, I tried:

> mysqldump -u root -p --ignore-table=database1.table1 database1 > database1.sql

... and the command completed successfully.

I tried to delete one of the offending records and the delete command completed successfully. I guess I could delete them all, from the first problematic one and minimize losses but is there any hope of recovering them?

What would you suggest I do from here?

Sidenote: I was using Navicat to manage the server and make backups, and strangely enough, restoring that particular table with a recent backup also fails with the connection lost error, which leads me to believe that this table has been having problems for a while now, maybe due to disk errors and not specifically due to the crash.

You could try to use MySQL's CHECK TABLE and REPAIR TABLE functions (but make sure you grab a copy of the table first):

mysql -uroot -p database1
CHECK TABLE table1

If that doesn't work, try using:

CHECK TABLE table1 EXTENDED

According to the CHECK TABLE documentation:

  EXTENDED is to be used only after you have run a normal check but still get strange errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

If any errors are reported by either of the above, try:

REPAIR TABLE table1

If that doesn't work, you can try:

REPAIR TABLE table1 EXTENDED

Finally, if that still doesn't work, you may be able to use myisamchk while MySQL is offline - according to the documentation:

  REPAIR TABLE does not implement all the options of myisamchk


Is it possible to load data into Postgres using COPY, where the data includes a SELECT statement? (I think the answer is probably "no", but I haven't managed to find anything where anyone tried to do this. Which maybe is a sign in itself).

So, I have a parent and child table than I'm generating data for in a script. The PK on parent table is an autosequence, the FK on child table into the parent table is the autosequence PK which isn't populated until the parent table's data is loaded.

When I load (much smaller datasets) as SQL in PGAdmin, I can match the records together by using another column as a lookup (eg, I have a column Reference on parent table which I fill with a unique pre-defined value for each record, effectively as a surr)

I load the parent table through COPY, and that works fine. However, in the child table, what I want to be able to do is inculde (select parent_id from parent_table where reference = predefined value) as the value for the Parent ID field.

The problem I'm trying to address is that, at the point the child table's values are created, I don't know what the FK values need to be, because the parent table's value haven't been loaded yet.

To be honest, this probably isn't even a good way to go about this - I have 2 million records in the parent table and 3-4 million in the child, so I don't really want to be making 4 million time 1 record calls into another million record table, but I can't see any obvious way around this without splitting the data generation so that the child table data is generated after the parent table has been loaded.

Better ideas gratefully received!

You do it like bellow:

psql -c '\copy (SELECT * FROM mytable where col1=something) to mytable.dat'

Then load dat file using COPY

psql -c '\copy mytable from mytable.dat'


Can PostgreSQL results include the query? I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment.

psql -U <username> -h <servername> -d <databasename> -f
20140701_Queries.sql > 20140701_Results.txt

Here are the queries:

select max(day) from tbl1;
select max(day) from tbl2;

Here are the results I get:

    max
------------
 2014-06-02
(1 row)

    max
------------
 2014-06-06
(1 row)

I would rather it come out something like this:

select max(day) from tbl1;

        max
    ------------
     2014-06-02
    (1 row)

select max(day) from tbl2;
        max
    ------------
     2014-06-06
    (1 row)

But the exact formatting isn't important right now. Just that someone can look for their query and see their results.

Thanks in advance :)

-- Edit

If I just paste the two queries into psql, I get this mix of results and my input which would be fine, if I could just output it to a file:

db=# select max(day) from tbl1;
    max
------------
 2014-06-02
(1 row)

db=# select max(day) from tbl2;
    max
------------
 2014-06-06
(1 row)

Use the -e flag when you start psql.
For example:

psql -e mydatabase

Or, in case of using a script, try the -a flag.
Details in the manual.


How to verify data integrity of data folders after data recovery in MySQL and PostgreSQL? I wonder if there is a way to verify data integrity of data folders from a data-recovered hard disk, both MySQL and PostgreSQL?

As far as I know, the only way in PostgreSQL is to run something like

pg_dumpall >/dev/null

which will scream if it encounters table corruption.

However, that won't test index integrity. For that, PostgreSQL v11 has the amcheck extension.


Vertica grant user create table I have users (developers) in Vertica who need to create tables for testing purposes, which will later be deployed under specific production schema.
I do not want them to have the ability to create tables in the production schema though.

Im looking for the grants necessary to allow a user the ability to create tables.
Do most environments create a schema for each user and then grant authorization for the user on that schema or what is the best practice?

Implement roles in you DB.
Manage grants on the schemas using roles definition.(single point)
Grant these roles to your users.

in regards users development, create a schema for each one of them with full rights (ownership) where they can play as much as they want.


What is the point of a SRID in a geometry type? From my understanding, SRID is a projection code; how the 2d coordinate system is fit over a spheroid.

The difference between Geometry and Geography types, from my understanding, is that a geometry type is two dimensional, and a geography type is 3 or four dimensional.Therefore what's the point of an SRID in a geometry type?

You are correct in your interpretation of a SRID.  It identifies the projection used to locate the Geometry or Geography.  Even if the database in question (I'm assuming SQL Server) doesn't make use of the SRID directly, the point of it is to identify how to work with the coordinates in relationship to others.  I suppose you could look at it like having a country code on a phone number.  Not always necessary, but important none the less.

Your understanding of Geometry vs Geography isn't correct.  Geometry is used to store objects which are projected to a Cartesian or Planar coordinate system.  They are not restricted to just 2 dimensions.  They use distance units to (metres, feet, etc) to represent points.
Geography is used to store objects that are projected on a Spherical coordinate system.  These systems use degrees (Latitudes and Longitudes) to represent points.

It is important to use the correct data type for your information.  If your objects have Latitude and Longitude coordinates, store them in a Geography.  This means when you use distance and area functions you will get a sensible result.

While some systems (SQL Server) mostly ignore the SRID in geometries, it is still important metadata and should be set correctly.  Other databases and software can and do use the SRID to determine how to treat the coordinates in the object, allowing for the coordinates to be reprojected to other coordinate systems and compared correctly to other objects that are projected differently.


Object Level Security I have a database that will be shared across multiple related types of applications for different users. I want to be able to restrict each database user to essentially have full control over their own objects, with only read access to the other objects of the database that they didn't create.

Is this possible and what's the best way to accomplish this?

Look into using Schemas. Each User can have (and own) their own Schema for their objects that they have full control over. Then you can grant SELECT access for them to other Schemas.

When doing this, you need to make sure that both of the following conditions are met:

Each Schema "owner" (i.e. AUTHORIZATION) is for the intended User
Each User has their default Schema set to their Schema

As the schema owner, each user will have CONTROL permission over the schema and all objects within. SELECT permission can then be granted on other schemas as needed:

CREATE SCHEMA User1 AUTHORIZATION User1 ;
GO
CREATE SCHEMA User2 AUTHORIZATION User2;
GO
CREATE SCHEMA User3 AUTHORIZATION User3;
GO
ALTER USER User1 WITH DEFAULT_SCHEMA = User1;
ALTER USER User2 WITH DEFAULT_SCHEMA = User2;
ALTER USER User3 WITH DEFAULT_SCHEMA = User3;
GO
GRANT SELECT ON SCHEMA::User2 TO User1;
GRANT SELECT ON SCHEMA::User3 TO User1;
GRANT SELECT ON SCHEMA::User1 TO User2;
GRANT SELECT ON SCHEMA::User3 TO User2;
GRANT SELECT ON SCHEMA::User1 TO User3;
GRANT SELECT ON SCHEMA::User2 TO User3;
GO

Please see:

CREATE SCHEMA
Security: Securables
Schemas for application boundaries (written about SQL Data Warehouse, but pertains to your scenario)
Ownership and User-Schema Separation in SQL Server


Display unique column when not all values are distinct Name Status Contact EpisodeNumber
AA   C      E       1
AA   C      F       1
AA   C      G       1
AA   D      H       1
BB   D      I       2
CC   C      J       3
DD   C      K       4

If D is present on any of the 'status' column of the given Names i don't want to see them. each 'contact' is different which is fine, i need to make sure the EpisodeNumber is the same. What happens is everytime "Status" is changed it creates a new row for that name with the contact that changed it so i can't do a simple select * where status != 'D' because it will show me the other statuses for that given episodenumber. We are running SQL server 2008 R2 with sql management studio version 10.50.6560.0 and report builder 3.0

Name Status Contact EpisodeNumber
CC   C      J       3
DD   C      K       4

If I understand your question correctly, I think NOT EXISTS should work.

DECLARE @T TABLE (
    NAME VARCHAR(30)
    ,STATUS VARCHAR(5)
    ,Contact VARCHAR(5)
    ,EpisodeNumber INT
    );
INSERT INTO @T
VALUES
    ('AA', 'C', 'E', 1),
    ('AA', 'C', 'F', 1),
    ('AA', 'C', 'G', 1),
    ('AA', 'D', 'H', 1),
    ('BB', 'D', 'I', 2),
    ('CC', 'C', 'J', 3),
    ('DD', 'C', 'K', 4)
;
SELECT *
FROM @T t
WHERE NOT EXISTS (
        SELECT *
        FROM @T
        WHERE NAME = t.NAME
            AND EpisodeNumber = t.EpisodeNumber
            AND STATUS = 'D'
        );

| NAME | STATUS | Contact | EpisodeNumber |
|------|--------|---------|---------------|
| CC   | C      | J       | 3             |
| DD   | C      | K       | 4             |


Do I need a partition scheme for each partitioned table Sorry if this is a stupid question, but I'm setting up table partitioning for the first time.  It will be on multiple existing tables all within the same database.

I know I need a partition function and a partition scheme, but do I need one of each for each table or a combination of 1 function and multiple partition schema or just one of each?

I'm using SQL Server 2014 Enterprise edition.

I tried Google but couldn't find the answer.

Hope someone can help.

Thanks

Alex

A separate partition function/scheme for each table/index provides the most flexibility and granularity but can increase complexity and redundant maintenance when boundaries are added or removed, such as when aligning or partitioning related tables.

I generally recommend a single function for all tables/indexes that are partitioned similarly and boundaries are always added/removed at the same time. You'll need separate partition schemes related to the function only if your file group mappings differ. You'll of course require separate partition functions if you need to maintain boundaries at different times.


postgresql random function returns duplicate results when order by random used in query I'm using postgresql 11.1-1 on Windows 10. On my system, if I run a query like the following, I will get a different number for each random column:

postgres=# SELECT random() as sortOrder, random() as col2;
     sortorder     |       col2
-------------------+-------------------
 0.607938482426107 | 0.121234225574881
(1 row)

But if I add an ORDER BY clause, as shown below, then random returns the same random number each time for every row.

postgres=# SELECT random() as sortOrder, random() as col2
               FROM generate_series(0,1)
               ORDER BY sortOrder;
     sortorder     |       col2
-------------------+-------------------
 0.100375576410443 | 0.100375576410443
 0.170669795479625 | 0.170669795479625
(2 rows)

I'm assuming there is a technical reason for this, but is it possible in this last query to get a different random value for each column?

Edit:
I may have oversimplified in my original question. The actual query I was attempting was more like the following:

SELECT column1, random(), column2, random()
               FROM table1
               ORDER BY random();

So, having an answer that can be combined with a table query is also important.

random() is volatile, and the doc says about volatility:

  A query using a volatile function will re-evaluate the function at
  every row where its value is needed.

Based on this definition, the engine is correct in evaluating the function only once per row, not once per column for the same row.

To turn off this optimization, you might tweak the query for the optimizer to miss the fact that the two columns carry the same expression. For instance:

postgres=> SELECT random()+0 as sortOrder, random() as col2
               FROM generate_series(0,1)
               ORDER BY sortOrder;
     sortorder      |        col2
--------------------+---------------------
 0.0412265718914568 | 0.00992022315040231
  0.841067179106176 |   0.818967831321061
(2 rows)

Now, that simple solution may seem a bit fragile. In some future version, the optimizer might theorically become smart enough to recognize that these two expressions have a common sub-expression that it can reuse.

A really robust solution would be to generate the columns in different rows to start with, in order to force the optimizer to use distinct function calls no matter how hard it tries to avoid the multiple evaluations. For instance:

SELECT v1,v2 FROM
(select random() as v1, row_number() over() as r1
 FROM generate_series(0,1)) as s1
JOIN
(select random() as v2, row_number() over() as r2
 FROM generate_series(0,1)) as s2
ON s1.r1=s2.r2
ORDER BY 1;
        v1         |        v2
-------------------+-------------------
 0.145532250870019 | 0.963712519966066
 0.277611976023763 | 0.252654927782714
(2 rows)


Is this in the 3NF or not? I have a table called Transactions

transactions ( TransactionID, CustomerID, DeviceTypeID, TransactionQuantity, TransactionDate, ManufacturerID)

TransactionID → CustomerID

TransactionID → DeviceTypeID

TransactionID → ManufacturerID

TransactionID → TransactionQuantity

TransactionID → TransactionDate

TransactionID → TransactionValue

Where transactionID is a primary key.

CustomerID
DeviceTypeID
ManufacturerID
are all foreign keys.

DeviceTypeID and ManufacturerID are needed to tell what "product" a customer is buying.

However, the is another table "inventory" which basically has

DeviceTypeID

ManufacturerID

These two keys are a composite primary key, and a number or quantity.

inventory ( DeviceTypeID,  ManufacturerID, InventoryQuantity )

(DeviceTypeID, ManufacturerID) → InventoryQuantity

Basically, in the inventory, there isnt a device for every manfucaturer. Say,there arent LG boilers in the inventory. But samsung ones are there.

It seems that Transaction Quantity is dependend on DeviceTypeID, ManufacturerID. And manufactuerID is dependend on DeviceTypeID and vice versa but I could be wrong.

It looks like this is in 3NF but i am not sure. I havent seen cases anywhere discussing what happens when you include composite/compound keys in a transaction table and a primary key.

Contextually speaking none of the keys have any realtion with each other in the table.

If there is only one item of a device type from a manufacturer in inventory, then this would be 3NF.  This would be the case for the information provided.

However, it is likely that you might have more than one type of LG boiler in inventory table.  As you dig into the design more details may arise which break the 3NF conditions.

Typically inventory items mighte have a natural key of manufacturer and manufacturer's part number.  This would be complicated if inventory items which can be sourced from different manufacturers are sold or used interchangably.  In this case, you would have an inventory (item) id for the inventory table.  This would link to a table with a tri-part key (inventory-id, manufacturer-id, manufactuers-part-number).


mysql master and slave has different schema alter table add column I have added column in large table it was having some unique index which was causing issue,So i drop index and add column and after adding column i have added unique index.
But all of my slave is broke they don't have master schema,
I am not sure how to solve this problem

ALTER TABLE User ADD COLUMN foo_bar VARCHAR(255);
Table also consist of email which has unique index

MySql Version :  5.6.30,
Master Create Table

CREATE TABLE `gobe_User` (
  `uuid` bigint(32) unsigned NOT NULL DEFAULT '0',
   `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `encrypted_password` varchar(255) DEFAULT NULL,
   UNIQUE KEY `uuid` (`uuid`) USING BTREE,
    UNIQUE KEY `emai_2` (`email`),
     KEY `email` (`email`),
     ) ENGINE=InnoDB DEFAULT CHARSET=ucs2 ROW_FORMAT=COMPACT;

Slave Create Table

    CREATE TABLE `gobe_User` (
      `uuid` bigint(32) unsigned NOT NULL DEFAULT '0',
       `name` varchar(255) NOT NULL,
      `email` varchar(255) DEFAULT NULL,

       UNIQUE KEY `uuid` (`uuid`) USING BTREE,
        UNIQUE KEY `emai_2` (`email`),
         KEY `email` (`email`),
         ) ENGINE=InnoDB DEFAULT CHARSET=ucs2 ROW_FORMAT=COMPACT;

Well, I see no reason why the ALTER would not propagate to the Slave(s) and work there.  Suggest you apply the ALTER to each Slave.  You might need a SLAVE START to get them running after that.  Then proceed with the ADD UNIQUE, etc.

PS, A UNIQUE KEY is a "key", so you have a redundant index on email.


How do I view operators in a Notify Operator task in a Maintenance Plan? In the past, we have added each DBA to a Notify Operator task on failure in Maintenance Plans ranging from scheduled backups to indexing and occasionally one-off operations. One of our DBAs recently left the company and we removed her email account. We also removed her from the list of operators in SQL Agent. However, we have a number of SQL Server servers and instances installed and it would be tedious to go through each maintenance plan on each of those instances to find if she is on the list manually. Is there a way to query each maintenance plan from either within SSMS or PowerShell? We're using SQL Server 2008R2 thru 2014.

Two ways come to mind, there may be more. I prefer the second but that's just my opinion

Import-Module SQLPS -DisableNameChecking
Get-ChildItem SQLSERVER:\SQL\ServerNameHere\DEFAULT\jobserver\Jobs |Select Name, OperatorToEmail

The first line below loads the 2012 assembly since that's the version I'm running on my laptop, adjust to suit your needs.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$srvObject = new-object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'ServerNameHere'
$srvObject.JobServer.Jobs| Select Name, OperatorToEmail


Indexing every WHERE column? I tried to turn on MYSQLI_REPORT_ALL | MYSQLI_REPORT_STRICT and now MySQL tells me:

  No index used in query/prepared statement...

in some of my app's queries.

Is it really best practice to index every WHERE column I'll possibly use so that MySQL will not complain? Seems a bit overkill to me as there are probably some columns where I'll only use the WHEREclause occasionally.

Turn on those flags only for initial development.  They are too noisy with false alarms otherwise.

There are valid cases for queries that do not use indexes.

There are cases (such as EAV) where it is impractical to have indexes for every possible query.

I prefer to turn on the slowlog with a low value for long_query_time, and later gather what queries are the "worst".  This is a much better list of queries to work on improving.  It may include some slow queries with the best possible index, and exclude some very fast queries that don't involve any index.

Do not blindly index every column.  Learn about "composite" indexes.

See my Cookbook on creating optimal indexes for basic queries.


Is it possible to restore a MySql database only using files from Data directory? I have a mysql installed inside a windows virtual machine. The hard drive was crashed and the system is not able to restore all again. But I can have access to entire c: ROOT. So I went to

c:/ProgramData/Mysql/Mysql Server 5.5/data/myDatabaseName

There I can see different files like: .frm, .opt, .trg and .trn.
In a regular way, I could run a mysqldump to backup and restore all database from command line, but the system is not able to be restored. So I had to copy all these files and past inside inside the same  directory inside same database in another physical instance.

The issue is some tables are not been recognized by this new instance. So I think copy and past is not enough to restore the database.

What am I missing?

Note: Database has innoDb and Myisam tables.

InnoDB makes it impossible to just cut and paste a database because each .ibd files has tablespace_id that are referenced in the data dictionary within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1.

Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko)

You cannot recover just a database. You must restore the entire data folder c:/ProgramData/Mysql/Mysql Server 5.5/data. Please see my old post restore table from .frm and .ibd file? on reattaching .ibd files to the data dictionary.

If the database folder has no .ibd files but only .frm files, then all the data are contained within c:/ProgramData/Mysql/Mysql Server 5.5/data/ibdata1.

You may have mangled the data dictionary. You have to do a complete restore of your datadir or mysqldump from an original database, shutdown mysqld, delete all of datadir, restart mysqld, and reload the mysqldump.


Modern SQL Profiler against SQL 2000 In my current work position I've been given a desktop with SQL2014 tools installed including SQL Profiler. For some time we are still however supporting production SQL2000 machines and when using Profiler against these the following error is received:

  Client unable to establish connection
  SQL Server Native Client 11.0 does not support connections to SQL
  Server 2000 or earlier versions.

What is a simple way to fix this issue?

You have to install management tools from sql server 2005/2008 or 2008R2 to manage sql server 2000.

Profiler from sql server 2014 wont work. There were problems from 2014 using profiler to 2005 - this is fixed in 2014 CU2.


SQL Server Transaction Log VLFs not being released (Status 2) on log shipped Database Have seen a couple of posts similar to my scenario, but nothing that actually resolves my issue.

Scenario:

Routine index rebuild happens, once a week, 2 AM Saturday morning.
Log file grows about 15x the usual size.
Transaction log backup is taken (every 10 minutes, 24 x 7).
FULL backup is taken every day at 3 AM.
VLFs are remaining "active" (status 2) when i look at dbcc loginfo
log_reuse_wait_desc is reporting "LOGBACKUP" in sys.databases
dbcc opentran is reporting no active transactions
@@version: Microsoft SQL Server 2005 - 9.00.5069.00 (X64)   Aug 22 2012 18:02:46   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Problem:

So, my issue is simple, because of hardware restrictions and time parameters, i need to keep transaction log size as small as possible. i believe the reason it is not releasing the space is because the VLFs are active and it thinks it needs to take a LOGBACKUP to release, but after countless log backups, the log_reuse_wait_desc is still reporting awaiting a log backup!

I could change the recovery model to simple, shrink and change back, but this breaks my LSN chain and my Log Shipping implimentation, so its not really a viable solution!

TIA.

After some more investigation i found that when a large log backup was required to be taken, the time taken produce the trn backup file was elapsing a timeout period of 5 minutes set by the lanmanserver service.

The error being reported was error 64 (error not found) which is actually quite a common issue apparently as described here; so even though the log backup was being created, the SQL engine couldn't verify that the backup had been successful.

In turn, the VLF's where not being set to 0, hench seeing the issues that were described in the Question.

thank you for your help guys, but i think the root of the issue was the error being reported (i didn't mention that as i could see the backup files and logshipping was still processing them, so i didn't think it was part of the same problem!)


MySQL under what scenario suitable to use WHERE EXISTS I recently just noticed that WHERE EXISTS can slow down the operation badly. E.g.

select SQL_NO_CACHE count(*) as aggregate
from `table_1`
where exists (select *
              from `table_2`
              where `table_1`.`fk_id` = `table_2`.`id`
                and `name` = 'john');

79 seconds

VS

select SQL_NO_CACHE count(*) as aggregate
from `table_1`
inner join `table_2` on `table_1`.`fk_id` = `table_2`.`id`
where `table_2`.`name` = 'John';

72 milliseconds

table_1 has total 4,723,710 records
table_2 has total 1,236 records

Just wanted to know, since the performance of WHERE EXISTS so bad, under what scenario do we need to use this?

Not a fair test.  Your example is about as close as you can get between using JOIN to using EXISTS.

Also, it probably gave a different result -- What if there are 2 Johns?  The JOIN would count both; the EXISTS would count only once.

EXISTS is a "semi-join".  It is better than JOIN in cases where it can stop short of scanning for all rows that match.  Instead, it stops with the first match.  If there is a useful index, semi-join does not necessarily help much.  If not, it could be a big speed up.

In the past, IN ( SELECT ... ) was notoriously inefficient.  EXISTS (SELECT * ) is often a better choice.

Search around stackoverflow.com for more instances of EXISTS.  I think you will find a variety.


Cassandra multi-DC:write on LOCAL and read from any DC We use a multi-data center (DC) cassandra cluster. During write on to the cluster, I want only LOCAL DC to perform writes on its nodes as we are already routing the write requests to the desired DC only based on the source from where write is initiated. So, I want only LOCAL DC to process the write and no other DC to perform the writes on its nodes. But later on by virtue of replication among nodes across DCs, I want the written data to be replicated across DCs. Is this replication across DCs possible when I am restricting the write to only one DC in the first place.If I do not open connections to REMOTE hosts lying in different DCs during my write operation, is data replication possible amongst DCs later on. Why I definitely need replicas of data in all DCs is because during data read from cluster, we want the data to be read from any DC the read request falls on, not necessarily the LOCAL one.

Do anyone has solution to this?

The data is always written to all DCs, the LOCAL consistency levels are just saying that confirmation should come from the local nodes, not from other DCs...
From the DSE Architecture guide (also look to image there):

In multiple datacenter deployments, DataStax Enterprise (DSE) optimizes write performance by choosing one coordinator node. The coordinator node contacted by the client application forwards the write request to one replica in each of the other datacenters, with a special tag to forward the write to the other local replicas.
If the write write consistency levels is LOCAL_ONE or LOCAL_QUORUM, only the nodes in the same datacenter as the coordinator node must respond to the client request for the request to succeed. Use either LOCAL_ONE or LOCAL_QUORUM to reduce geographical latency and lessen the impact on response times of client write requests.

P.S. I recommend to read this guide to understand how Cassandra/DSE works - it makes things much simpler if you have this information.


complex normalisation problem I am transfering a managment application from Access to SQL Server and from vba to .net. Still in study and learning phase. I am wondering if one of the solutions I developped previously can not be improved using a more normalised technique but could not find out if and how to improve.

I have a unique centralised documents table (and conected form) containing a row for every document (word, pdf or image) refered to by the application with besides document specific data it contains two columns referring to the application's element linked to that document = where it was added like Bank statement, invoice, budget, relation, contact, Project, Investment, Account with as purpose allowing a shortcut to the origin of the document i.e. enabling to open directly the form from where the document was originally added to the database.

I did it dedicating two columns one (a one char FK) "Origin" refers to the PK of a table containing the data to access the table or open the form where the document was originaly created the other "OriginID" contains the Origin table Primary Key (they can all be refered to through a unique integer clustered primary key).

That way of working although efficient does not comply with normalisation but I did not find a way to normalise those relations as there are 9 entities that can create a document record.

Now that I am reviewing and rewriting the application for SQL server I wonder if there could be a better way to handle those relations.

This may not be a normalization problem. Almost every table has metadata or audit information -- the date the record was inserted and/or updated and by whom, app data such as page id, etc. This is not entity data, just other information that is needed when working with the entity data. As such, they don't really fall under the normal design rules, including normalization.

It sounds like your data concerning documents and other app info fits into this metadata role. If so, you don't have to worry about how it relates to entity data. Keep it as it is.


How to schedule emails out with SQL Server I am creating an easy "service desk" web application with SQL Server as database.

I have a table where the web application is inserting data which is used to send emails for alerting the users about tickets opened, closed, etc. This part is working perfectly fine using sp_send_dbmail and a trigger on insert.
However, there are cases where the email is scheduled to be sent later on (for example preventive maintenance).

I know I can create schedule jobs for this however I would like to know if there is any more effective method rather than creating a scheduled job everytime.

First of all, are you really sure you need to reinvent the wheel? There are open source service desk software such as http://www.otrs.com/software/

Secondly, sending e-mail using database engine is not a good idea. Given that SQL Server box is really expensive, try to avoid spending CPU time on tasks that can be done outside of SQL Server. For example, you can send emails using your application.

But if you really want to stick with your idea, like you said, there is no better way than to create a scheduled job.


SQL Server 2008 R2 Replication not working - my 1st try I am having difficulties when I attempt my first shot at SQL Server 2008 R2 Replication.
Looks like I have login issues.
How do you modify settings without having to start a new plan again?
Please be kind - this is all new to me ....... lol

It looks like I have a security problem:

  Access to path is denied

Your Snapshot Agent process account does not have sufficient permissions to access the snapshot folder.

In the section Permissions That Are Required by Agents in Replication Agent Security Model, the Snapshot Agent process account requires db_owner in the distribution and publication databases and needs read, write, and modify permissions on the snapshot share.

Have a look at Lesson 2: Preparing the Snapshot Folder for more information.


Table redefinition ends with ORA-02448 I'm trying to redefine a table to be partitioned.

What I did:

CREATE TABLE WERTEVAR_TMP(
    "WVTEIL" NUMBER(10,0),
    "WVMERKMAL" NUMBER(5,0),
    "WVUNTERS" NUMBER(5,0),
    "WVWERTNR" NUMBER(10,0),
    "WVWERT" FLOAT(126),
    "WVATTRIBUT" NUMBER(5,0),
    "WVPRUEFER" NUMBER(10,0),
    "WVPRUEFMIT" NUMBER(10,0),
    "WVMASCHINE" NUMBER(10,0),
    "WVNEST" NUMBER(10,0),
    "WVDATZEIT" DATE,
    "WVSTIANF" NUMBER(3,0),
    "WVCHARGE" VARCHAR2(20 BYTE),
    "WVAUFTRAG" VARCHAR2(20 BYTE),
    "WV0005" VARCHAR2(35 BYTE),
    "WV0009" VARCHAR2(255 BYTE),
    "WV0011" VARCHAR2(250 BYTE),
    "WV0014" VARCHAR2(40 BYTE),
    "WV0015" NUMBER(10,0),
    "WV0016" VARCHAR2(32 BYTE),
    "WV0017" VARCHAR2(32 BYTE),
    "WV0031" RAW(16),
    "MONITORING_VERSION" NUMBER DEFAULT 0,
    "WV0034" NUMBER(10,0),
    "WV0054" VARCHAR2(32 BYTE),
    "WV0055" VARCHAR2(32 BYTE),
    "WV0056" VARCHAR2(32 BYTE),
    "WV0057" VARCHAR2(32 BYTE),
    "WV0058" VARCHAR2(32 BYTE),
    "WV0059" VARCHAR2(32 BYTE),
    "WV0060" VARCHAR2(32 BYTE),
    "WV0061" NUMBER(10,0),
    "WV0062" NUMBER(10,0),
    "WV0063" NUMBER(10,0),
    "WV0080" VARCHAR2(64 BYTE),
    "WV0081" NUMBER(5,0)
)
STORAGE (INITIAL 65K NEXT 65K)
PARTITION BY RANGE (WVDATZEIT) INTERVAL (NUMTODSINTERVAL(1, 'day'))
SUBPARTITION BY HASH (WVMASCHINE) SUBPARTITIONS 8
(
    PARTITION P_BEFORE_2000 VALUES LESS THAN (TO_DATE('01-01-2000', 'DD-MM-YYYY'))
);

BEGIN
    DBMS_REDEFINITION.start_redef_table(
        uname         => 'CHYSTAT_STAGING_MONITORING',
        orig_table    => 'WERTEVAR',
        int_table     => 'WERTEVAR_TMP',
        options_flag  => DBMS_REDEFINITION.CONS_USE_ROWID
    );
END;

SET SERVEROUTPUT ON;
DECLARE
    l_errors  NUMBER;
BEGIN
    DBMS_REDEFINITION.copy_table_dependents(
        uname            => 'CHYSTAT_STAGING_MONITORING',
        orig_table       => 'WERTEVAR',
        int_table        => 'WERTEVAR_TMP',
        copy_indexes     => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
        copy_triggers    => TRUE,
        copy_constraints => TRUE,
        copy_privileges  => TRUE,
        ignore_errors    => FALSE,
        num_errors       => l_errors,
        copy_statistics  => FALSE,
        copy_mvlog       => FALSE
    );

    DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;

-- Complete the redefinition process
BEGIN
    DBMS_REDEFINITION.finish_redef_table(
    uname      => 'CHYSTAT_STAGING_MONITORING',
    orig_table => 'WERTEVAR',
    int_table  => 'WERTEVAR_TMP');
END;

But DBMS_REDEFINITION.finish_redef_table ends with:

Error report:
ORA-02448: constraint does not exist
ORA-06512: at "SYS.DBMS_REDEFINITION", line 105
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3520
ORA-06512: at line 2
02448. 00000 -  "constraint does not exist"
*Cause:    The named constraint does not exist
*Action:   Stop trying to do something with a nonexistant constraint

I would appreciate any hint.

ORA-02448 during DBMS_REDEFINITION.finish_redef_table ERROR at line 1:
  ORA-02448: constraint does not exist

CAUSE

  Bug 18258770 : ORA-2448 ON DBMS_REDEFINITION.FINISH_REDEF_TABLE

Here is the document link(MOS account required)
ORA-02448 During DBMS_REDEFINITION.finish_redef_table (Doc ID 2021843.1)


Dynamic Schema Creation and Switching Am creating a schema dynamically like:

SELECT @xxx:=LAST_INSERT_ID();

SET @schemaname = concat('CREATE SCHEMA ','TrialDB_','',@xxx);

So once after creating the Schema I would like to dump some tables into that schema. So for switching the schema I am trying:

use @schemaname ; // Which is not Working

//Also tried the below scenario which is not working

SET @useSchema =concat('USE','TrialDB_','',@cloudclientid);

SELECT @useSchema ;

PREPARE statement1 from @useSchema;

EXECUTE statement1;

After dumping the tables my regular flow needs to continue.

First one is how to create database dynamically.

set @db_name:= 'db_name';
set @statment = CONCAT('CREATE DATABASE ', @db_name);
prepare stmt_create FROM @statment;
execute stmt_create;
deallocate PREPARE stmt_create;

Now you want to dump some table into it.

set @db_name="db_name";
set @start_syntax="CREATE TABLE ";
set @table_syntax=".`table_name` (id int(4)) ENGINE = InnoDB;";
set @stat_execute=concat(@start_syntax,@db_name,@table_syntax);
prepare stat_ment from @stat_execute;
execute stat_ment;
deallocate prepare stat_ment;

In this way you can create database and dump the table.


Bulk insert top 1000 rows from a big flat file I have a massive flat file having hundreds of millions rows. I only want to insert the top 1000 of them. How can I do this?

bcp Import

You can import data from your huge data file using Microsoft SQL Server's bcp utility from the command prompt.

bcp Description

  The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns

Example Syntax

C:\> bcp YOUR_TABLE -d YOUR_DB -i INPUT_FILE -S YOUR_SERVER -F 1 -L 1000 -U YOUR_LOGIN -P YOUR_PASSWORD -f FORMAT_FILE

This translate to:

bcp
bulk copy program
YOUR_TABLE
The table you will be importing your data to
-d YOUR_DB
The name of the database you are connecting to
-i INPUT_FILE
The full pathname and filename to your huge file (e.g. C:\temp\bigfile.txt)
-S YOUR_SERVER
The name of the server you are connecting to
-F number
The first line to import
-L number
The last line to import
-U YOUR_LOGIN
The SQL Server Login (alternatively you can use -T for a trusted connection)
-P YOUR_PASSWORD
The password for the SQL Server Login
-f FORMAT_FILE
Depending on your table definition you will probably require a format file which defines the structure of the table you are importing data to and the delimiters in the INPUT_FILE. Further information for the format file can be found here and here.

Your command should then look like this:

bcp MEGA_TABLE -d Test_DB -i C:\temp\huge_data.txt -S localhost -F 1 -L 1000 -U sa -P secretnottelling -f c:\temp\huge_data.fmt

bcp format file

Depending on the complexity of your table and input file, you might have to create a separate bcp format file to accommodate missing columns in either the input file, target table or both. This can be achieved by exporting your table without a format file:

bcp YOUR_TABLE out c:\temp\sample_data.txt -S localhost -T -d YOUR_TABLE

bcp will ask you some questions about your fields and terminators for the format file and then will ask you to provide a name:

Enter the file storage type of field ID [int-null]:
Enter prefix-length of field ID [1]:
Enter field terminator [none]:

[multiple iterations]

Enter the file storage type of field Fnct_ID [int-null]:
Enter prefix-length of field Fnct_ID [1]:
Enter field terminator [none]: ;

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: C:\temp\huge_data.fmt

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

You now have a format file for your target table which can be used in the bcp import.

The format file will look a bit like this one:

12.0
8
1       SQLINT              1       4       ";"    1     ID                   ""
2       SQLNCHAR            2       200     ";"    2     Question             Latin1_General_CS_AS
3       SQLNCHAR            2       200     ";"    3     Link                 Latin1_General_CS_AS
4       SQLNCHAR            2       100     ";"    4     Tbl                  Latin1_General_CS_AS
5       SQLNCHAR            2       2000    ";"    5     Info                 Latin1_General_CS_AS
6       SQLINT              1       4       ";"    6     Tbl_ID               ""
7       SQLINT              1       4       ";"    7     Src_ID               ""
8       SQLINT              1       4       ";"    8     Fnct_ID              ""

This is just a basic primer on how to use the bcp utility to import data from a file.

Answer

Create a format file (*.fmt) for your target table usign bcp
Import the data using the -F and -L parameters
Check the import

References

bcp Utility (Microsoft Technet)
Create a Format File (SQL Server) (Microsoft Technet)
Use a Format File to Map Table Columns to Data-File Fields (SQL Server) (Microsoft Technet)

  In absence of a SQL Server version in your question I referenced the SQL Server 2012 documentation.


MySQL DB structure with Main category and several nested sub categories I am looking to create a set of fields on my webpage identical to what eBay uses on their category selection page when you are selling something. For my purposes I am doing motorcycles, power sports, parts & accessories. So for example I would like to have three very broad categories:

Motorcycles
Powersports
Parts & Accessories

If a user selects Motorcycles it will then ask Brand:

Yamaha
Honda
Kawasaki
Harley-Davidson

Upon selecting a Brand it will then ask for model for that specific brand, so for Yamaha:

YZ
YZF
YFZ
WR
PW

So

Motorcycles -> Brand -> Model

Now if I wanted to add something to parts and accessories it would be something like

Parts & Accessories -> Brand -> Model -> Type of part -> Sub type of part

Same for Powersports:

Powersports -> ATV -> Brand -> Model

I have tried a structure like this

Main Categories(categories):

+-------+---------------------+-----------------------+
| CatID | Category            | CatDBTable            |
+-------+---------------------+-----------------------+
|     1 | Motorcycles         | motorcycle_brands     |
|     3 | Parts & Accessories | part_acc_categories   |
|     2 | Powersports         | powersport_categories |
+-------+---------------------+-----------------------+

Motorcycles(motorycle_brands):

+-------+-------+-------------------------+
| CatID | SubID | SubName                 |
+-------+-------+-------------------------+
|     1 |     1 | American Classic Motors |
|     1 |     2 | American Ironhorse      |
|     1 |     3 | Aprilia                 |
|     1 |     4 | Benelli                 |
|     1 |     5 | Big Dog                 |
+-------+-------+-------------------------+

Motorcycles Model(motorcycle_models):

+---------+--------------+------------+
| ModelID | BrandID      | BrandModel |
+---------+--------------+------------+
|       1 | Custom Built | Bobber     |
|       2 | Custom Built | Chopper    |
|       3 | Custom Built | Pro Street |
|       4 | Custom Built | Other      |
|       5 | BMW          | F-Series   |
+---------+--------------+------------+

Now, this is really simple to maintain keeping everything in separate tables but a nightmare for me to figure out how to query it properly. I have posted this question on another site and a user suggested using one large table which I think is a terrible suggestion but then again I am not a DB admin. I am hoping some of the experts here that do this on a daily basis can help me come up with a structure that is easy to query and maintain at the same time. I hope all of this makes sense. I have search around but can not find anything on doing several nested sub categories only a main category and a sub category.

I would generalize the design, I.e. instead of a motorcycle_brand table, You would want a category_brand table, and instead of a motorcycle_model table, you would have a category_brand_model table with an fk on the category_brand pk. This allows for several advantages:

Adding new product categories in the future requires only adding records, not the addition of new tables.
If formalizes the relationship hierarchy of category-brand-model instead of relying on shaky and maintenance-intensive query design to do so.
The queries would be very, very simple to write.

To do this, you would combine each of the three category brand tables you have now and create a new table categoy_brand with an fk on catid.


Possible to use first CTE as filter for second CTE? I'm trying to optimize a query on a large database that essentially fetches 50 images for a site and the URL's for the pages they appear on. The current query works okay for smaller sites but larger ones, such as the one I'm testing with that has 50,000 images, the query effectively times out.

I've narrowed down the slowness to the second CTE (y) because it's fetching all matching records in that table that consists of over 150M rows. What I'd like to do is use the first CTE (x) as an input to the second so that only records tied to resources fetched in x are fetched in y, since x is where the filtering occurs and results are limited to 50 per query. Is this possible?

WITH x AS
(
    SELECT vi.resourceid
    FROM vw_image vi
    WHERE vi.siteid = 2294
    AND vi.childtype = 'i'
    ORDER BY vi.id
    OFFSET 1 ROWS
    FETCH NEXT 50 ROWS ONLY
),
y AS (
    SELECT rop.resourceid as resourceid, r.url as url
    FROM resourceonpage rop
    INNER JOIN resource r ON rop.pageid = r.id
    WHERE rop.siteid = 2294
)
SELECT i.*, string_agg(y.url, ',') as urllist
FROM x
INNER JOIN vw_image i ON x.resourceid = i.resourceid
LEFT JOIN y ON y.resourceid = x.resourceid;

Looking into my crystal ball, your query might work faster by orders of magnitude like this:

SELECT i.*, rr.urllist
FROM   vw_image i
LEFT   JOIN LATERAL (
   SELECT string_agg(r.url, ',') AS urllist
   FROM   resourceonpage rop
   JOIN   resource r ON r.id = rop.pageid
   WHERE  rop.siteid   = 2294  -- or: = i.siteid
   AND    r.resourceid = i.resourceid
   ) rr ON true
WHERE  i.siteid = 2294
AND    i.childtype = 'i'
ORDER  BY i.id
OFFSET 1 ROWS
FETCH  NEXT 50 ROWS ONLY;  -- or just: OFFSET 1 LIMIT 50

You don't need CTEs here, those will only slow you down.

I use a LATERAL join to use indexes on resourceonpage and resource and aggregate in the subquery right away. About LEFT JOIN LATERAL ... ON true:

What is the difference between LATERAL and a subquery in PostgreSQL?

Looking even deeper into my crystal ball, the best indexes might be:

CREATE INDEX ON vw_image (siteid, id) WHERE childtype = 'i';
CREATE INDEX ON resourceonpage (siteid, pageid);
CREATE INDEX ON resource (resourceid, id, url);

Appending url is only useful if you get index-only scans out of it.

An alternative approach would be to use parentheses in the FROM list like demonstrated here:

How to include missing data for multiple groupings within the time span?

But, according to my crystal ball (and an educated guess) the LATERAL version will be faster. There is not enough information in the question to give you more than educated guesses.


Cannot create SP in Azure SQL Database I am trying to create a stored procedure on my Azure SQL Database, which has joins to other database table in Azure SQL Database.

I am getting the following error while creating the Stored Procedure:

  Msg 40515, Level 15, State 1, Procedure sp_select, Line 16 [Batch Start
  Line 0] Reference to database and/or server name in 'database.dbo.table'
  is not supported in this version of SQL Server.

I understand we can make cross database queries through Elastic Database Query, but how to create the stored procedure for it?

Azure SQL DB doesn't allow ad-hoc 3-part names (database.schema.table) in queries.

Azure SQL DB Managed Instances do - but that's a different product. If you have a lot of existing stored procedures (say, hundreds) referring to a lot of existing objects in different databases (say, thousands), then it may be easier to migrate to Managed Instances than do what I'm about to describe.

To get cross-database queries working in Azure SQL DB, then in each database where you want to run the query, you need to:

Create an external data source - which is kinda like a linked server
Create an external table - which is kinda like a view that points to the table you want to query, going through the external data source

The instructions are fairly straightforward, but here's the painful part: you have to do this for every database you want to query, for every table/view/etc that you want to query. Plus, if the table changes (like new columns are added or a data type changes), then you have to go back and revisit all of the external table references that point to it, and change their structure as well.

This is one of those things that "technically works" in Azure SQL DB, but really only works for new from-scratch applications, not migrating an existing application to the cloud. Like I said - if you've got a lot of these queries referencing a lot of objects, now you understand why Managed Instances might be a better fit for you.


Best practices for generating unique multi-column keys for weak entities? How should one generate non-unique, non-natural identifiers for weak entities?

For example, if order_id is the primary key for an order table, and (order_id, item_number) is the primary key for an order_item table with a foreign key on order_id, how best to generate item_number?

A few of possibilities come to mind, but none seem ideal:

Autoincrement item_number: the order_item entity is no longer weak, and the composite key is redundant.
Use a trigger to search for the current max item_number for a given order_id, then increment: if a row is deleted this could lead to reassigning a PK to a different record - that doesn't seem like a good idea? (edit: this can also be done without using triggers as explained in joanolo's answer)
Use a trigger to create a new sequence for every order_id, and somehow draw item_numbers from the appropriate sequence: this is functionally the desired behaviour, but seems like it would be a mess to implement. Is it even possible to reference a sequence by an order_id?

Edit - closely related (if not duplicate):

Create unique sequence per set of rows sharing the same foreign key value
Serial numbers per group of rows for compound key

1. is the least error-prone, simplest and fastest.
Trigger solutions like in 2. or 3. are subject to subtle race conditions under concurrent write access.

Make item_number a serial column and also the PK for order_item in this case. Stick with the default values drawn from the underlying sequence and never update the column.

Auto increment SQL function

Create a multi-column index on (order_id, item_number) for performance of typical queries. (Might as well be UNIQUE, but does not have to be.) In a typical setup (order_id and item_number can both be plain integer), the multicolumn index happens to be just as small and fast as an index on just order_id:

Is a composite index also good for queries on the first field?

(Like I commented:) Typically, the only important role of an item number is to be unique (and immutable). If you need a stable sort order among items, you might just rely on the serial value of item_number. Be aware that those numbers are not necessarily in order of transaction commits. It may be useful to add the transaction timestamp current_timestamp (or possibly statement_timestamp() or clock_timestamp()) to the row. Depends on requirements and access patterns.

You can add a VIEW for the human eye, with item-numbers per order_id starting from 1, dynamically generated with row_number(), ordered by above criteria. But operate with the unique, immutable item_number internally.

Create unique sequence per set of rows sharing the same foreign key value


Use event schedule and stored procedure to automatically average every hour MySQL I have a database in Mysql Workbench about weather that updates every second. It contains temperature and wind speed. This is my database:

CREATE TABLE `test`.`new_table` (
`id` INT(10) NOT NULL,
`date` DATETIME NOT NULL,
`temperature` VARCHAR(25) NOT NULL,
`wind_speed` VARCHAR(25) NOT NULL,
`humidity` VARCHAR(25) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

I want to find the average temperature every hour and insert the result into a new table like this

CREATE TABLE `test`.`table1` (
`idsea_state` INT(10) NOT NULL,
`dateavg` DATETIME NOT NULL,
`avg_temperature` VARCHAR(25) NOT NULL,
PRIMARY KEY (`idsea_state`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;

this is my coding to find average and insert the result into new table

INSERT INTO `table1`
(`dateavg`, `avg_temperature`)
SELECT `date` , avg(`temperature`)
FROM   `new_table`
GROUP BY DATE( date ), HOUR( date );

and this is my code for stored procedure

USE `test`;
DROP procedure IF EXISTS `new_procedure`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
  INSERT INTO `table1`
   (`dateavg`, `avg_temperature`)
  SELECT `date` , avg(`temperature`)
  FROM   `new_table`
  GROUP By DATE( date ), HOUR( date );
 END$$

 DELIMITER ;

the problem is I want this coding average run automatically every hour, should I use stored procedure and event scheduler?
Please help me, I don't know how to use event scheduler in Mysql Workbench.

Run this in MySQL

SET GLOBAL event_scheduler = ON;

Add this to my.cnf so the scheduler remains enabled upon mysql restart

[mysqld]
event_scheduler = ON

Start the Event on the Next Hour, Make it Recurring Every Hour, Adjusting WHERE clause

CREATE EVENT hourly_temp_avg
    ON SCHEDULE
    EVERY HOUR
    STARTS DATE(NOW()) + INTERVAL (HOUR(NOW())+1) HOUR
    ON COMPLETION PRESERVE
DO
INSERT INTO `table1` (`dateavg`, `avg_temperature`)
SELECT `DATE` , avg(`temperature`)
FROM `new_table`
WHERE `date` >= NOW() - INTERVAL 1 HOUR;

Masoud came up with the proper concept first. So, he gets +1 from me.


Help with configuring pgAudit I'm using PostgreSQL 10.5 on Linux (RHEL).  I recently installed pgAudit and was trying to configure it to capture DLL statements.

1) The first thing I tried was to edit the postgresql.conf file directly.  I didn't see any commented out default entries to edit, so near where I have the entries:
log_destination = 'csvlog'
log_collector = on
I entered my own entry of:
pgaudit.log = 'ddl'
Then I restarted the database.  After doing this I created and dropped a table, but no entry of that in the log file postgresql-2019-11-19-141901.csv.

2) So then tried to create the entry using the ALTER command:
ALTER SYSTEM SET pgaudit.log TO 'ddl';
SELECT pg_reload_conf();
After doing this, I noticed a second file was created (postgresql.auto.conf).  That file had the entry pgaudit.log = 'ddl'.  From what I read, this file get read after the main postgresql.conf file.
However after creating and dropping a table, still no entry in the log file.  I did notice though when I run the command: show pgaudit.log;  It came back with 'Role' (and not ddl).

3) So the final thing I tried was to enter the command: set pgaudit.log = 'ddl'; Now when I run the command show pgaudit.log; it returned 'ddl'.  This time I tried to drop a table again.  After the table was dropped , my psql client displayed:
NOTICE: AUDIT: SESSION, 1,1,DDL, DROP TABLE, TABLE....blah,blah....[not logged].  This looks like a pgaudit entry but was on my screen and not in the log file.  At this point I restarted the database again and now when I run the command: show pgaudit.log; it defaulted back to 'Role' (vs. ddl).

Can someone please help me see what it is i'm doing incorrectly?

Thanks in advance

Before you can use pgAudit, you need to add it on the postgresql.conf file. You need to uncomment the line (or add pgaudit if you already have another libraries) to the shared_preload_libraries, like this:
shared_preload_libraries='pgaudit'

After this you must start the server and add the pgaudit extension:
CREATE EXTENSION pgaudit

From then on you can configure what is going to be logged either directly on the postgresql.conf file or by set commands. pgAudit can register by session or by oject created, that depends on you.
Only after all of that your set pgaudit.log = 'ddl' will register what you want.


Why would DBMS_METADATA.GET_DDL Fail when i have the SELECT_CATALOG_ROLE Role? I am trying to get the DDL using METADATA_DBMS.GET_DDL for an object in a database, and I get the following error:

ORA-31603: object "MY_PACKAGE" of type PACKAGE not found in schema "S1"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 3149
ORA-06512: at "SYS.DBMS_METADATA", line 4787
ORA-06512: at line 1

This is how I am calling the METADATA_DBMS.GET_DDL:

SELECT DBMS_METADATA.GET_DDL (object_type, object_name, owner)
       FROM dba_objects
       WHERE OBJECT_ID = (select OBJECT_ID from dba_objects where object_name ='MY_PACKAGE' and object_type = 'PACKAGE' and owner = 'S1')

I wrote it with the subquery to make sure the object exists in the schema S1. I am certain S1.MY_PACKAGE exists, I can even look at it in TOAD Schema Browser.

I have the following roles granted:

select * from USER_ROLE_PRIVS

USERNAME  GRANTED_ROLE         ADMIN_OPTION  DEFAULT_ROLE  OS_GRANTED
MYUSER    CONNECT              NO              YES          NO
MYUSER    DBA                  NO              YES          NO
MYUSER    SELECT_CATALOG_ROLE  NO              YES          NO

I had DBA Role first, and since it was throwing that error I granted myself SELECT_CATALOG_ROLE.

I checked against another Database Instance where the query works and I have the same roles, what else do I need to make DBMS_METADATA.GET_DDL work?

Both databases are a clone from the same source database, and I later got the privileges granted.

It seems there was some issue with TOAD. I opened TOAD after they granted me DBA Role, yet it was like it did not take effect.

I could query the USER_ROLE_PRIVS table and see I had the role, I could GRANT myself the SELECT_CATALOG_ROLE, but when I called DBMS_METADATA.GET_DDL it did not work.

Since I had open TOAD after the access had been granted, I didn't think that was the issue. At last, as a desperate measure, I tried closing everything and Opening it again and it worked.

I was going to delete this question, but I guess it could be useful for someone else in the future.


Grant permissions on views, deny select on tables I have a MySQL user and I want it to view ONLY the views I want and not any other table in the database. I've granted this user permissions only on certain views as following:

GRANT SHOW VIEW ON `myDatabase`.`awesome_view` TO 'thisUser'@'%'

If I do a show grants; statement I can only see this permissions as expected.
However I'd like this user to query JUST the views and not the tables that are related to these views, but I can't find a way to do this. It seems to be that if I want the user to do a select on the view, the select must also be granted for the table, or am I wrong?

If I deny the selectstatement in the rest of the tables, and in the command line I try to do a select I got the following:

SELECT * FROM myDatabase.fordibenForYouTable;
ERROR 1142 (42000): SELECT command denied to user 'thisUser'@'localhost' for table 'fordibenForYouTable'

That's what I want indeed, but I also got denied if I select the view data.

Is there a way I can make available to the user just the views and not the tables?

You will have to treat the view as a table. The information_schema already does

If you run

SELECT table_name FROM information_schema.tables
WHERE engine IS NULL;

you get all the views.

Just grant SELECT on the view to the user as follows

GRANT SELECT ON `myDatabase`.`fordibenForYouTable` TO 'thisUser'@'localhost' ;

Once you do this, you should have SELECT access to the table.

To make sure, run SHOW GRANTS FOR 'thisUser'@'localhost';

You should also be able to see what table-level access is granted to 'thisUser'@'localhost'

SELECT * FROM mysql.tables_priv
WHERE user='thisUser' and host='localhost'\G

You can also see what user has table-level access to myDatabase.fordibenForYouTable

SELECT * FROM mysql.tables_priv
WHERE db='myDatabase' and table_name='fordibenForYouTable'\G

GIVE IT A TRY !!!


Is an anomaly free relation necessarily normalized? A normalized relation can still suffer anomalies. Going the other way, which (if any) normal forms can be violated by a relation that's free from anomalies? If such exist,

For each such normal form that can be violated, what is an example of an anomaly free relation that violates the normal form?
Are there any normal forms that an anomaly free relation is guaranteed to comply with?

A relvar that is considered free of all update anomalies will satisfy 6NF and by implication 5NF and BCNF (other normal forms defined many years ago are no longer deemed useful).


Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE? As the question states, what (if any) is the effect of max_allowed_packet on LOAD LOCAL INFILE?

Does setting it to a large value load data files of large size such as 700MB into the db quickly?

LOAD DATA INFILE can be thrown off if read_buffer_size is larger than max_allowed_packet. This is the case when it comes to replication.

See the following bug reports

http://bugs.mysql.com/bug.php?id=15937
http://bugs.mysql.com/bug.php?id=30435
http://lists.mysql.com/mysql/168070

Outside of MySQL Replication, I can't really tell.

One thing is for sure: Whatever mysql had as the setting for max_allowed_packet on a server that performed SELECT ... INTO OUTFILE, the server into which you import data via LOAD DATA INFILE better have the same setting for max_allowed_packet or greater.

The max value for max_allowed_packet is 1G. It couldn't hurt to just set that in my.cnf.

[mysqld]
max-allowed-packet=1G

You do not have to restart mysql. Just run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 1024;

then you can perform the LOAD DATA INFILE.


First thing is that SQL_CALC_FOUND_ROWS is evil. Just do not use it as indexes can't always be used together with it.

As for the scond part of the issue, mysql can not use an index for two ordered queries. And I got 3 of them here - lat and lon range scans and a time sorting.

See https://stackoverflow.com/questions/4083632/can-mysql-use-index-in-a-range-query-with-order-by


How to read mysqld.log files? can someone tell me how to read the mysqld.log?

I'm curious about the third column.
Because every time i start/stop the mysql it give different output. I hope someone can explain to me :).

Below is the example:

What is 18614?

2018-08-09 23:08:03 18614 [Note] InnoDB: Using CPU crc32 instructions
2018-08-09 23:08:03 18614 [Note] InnoDB: Initializing buffer pool, size = 80.0G
2018-08-09 23:08:07 18614 [Note] InnoDB: Completed initialization of buffer pool
2018-08-09 23:08:08 18614 [Note] InnoDB: Highest supported file format is Barracuda.
2018-08-09 23:10:36 18614 [Note] InnoDB: 128 rollback segment(s) are active.
2018-08-09 23:10:36 18614 [Note] InnoDB: Waiting for purge to start
2018-08-09 23:10:36 18614 [Note] InnoDB: 5.6.36 started; log sequence number 6889901435830
2018-08-09 23:10:36 7fc3935fe700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
2018-08-09 23:10:36 18614 [Note] Server hostname (bind-address): '*'; port: 33060
2018-08-09 23:10:36 18614 [Note] IPv6 is available.
2018-08-09 23:10:36 18614 [Note]   - '::' resolves to '::';
2018-08-09 23:10:36 18614 [Note] Server socket created on IP: '::'.
2018-08-09 23:10:36 18614 [Note] Event Scheduler: Loaded 0 events
2018-08-09 23:10:36 18614 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.36-log'  socket: '/var/lib/mysql/mysql.sock'  port: 33060  MySQL Community Server (GPL)

What is 20239 ?

2018-08-09 23:12:23 20239 [ERROR] Plugin 'InnoDB' init function returned error.
2018-08-09 23:12:23 20239 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-08-09 23:12:23 20239 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-08-09 23:12:23 20239 [ERROR] Aborting

What is 100508?

2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'MyISAM'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'CSV'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'MEMORY'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'MRG_MYISAM'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'sha256_password'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'mysql_old_password'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'mysql_native_password'
2018-08-19 23:35:32 100508 [Note] Shutting down plugin 'binlog'
2018-08-19 23:35:32 100508 [Note] /usr/sbin/mysqld: Shutdown complete

What is 2283?

2018-08-19 23:38:15 2283 [Note] InnoDB: 5.6.36 started; log sequence number 6952861241977
2018-08-19 23:38:15 7f5dbffff700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
2018-08-19 23:38:15 2283 [Note] Server hostname (bind-address): '*'; port: 33060
2018-08-19 23:38:15 2283 [Note] IPv6 is available.
2018-08-19 23:38:15 2283 [Note]   - '::' resolves to '::';
2018-08-19 23:38:15 2283 [Note] Server socket created on IP: '::'.
2018-08-19 23:38:16 2283 [Note] Event Scheduler: Loaded 0 events
2018-08-19 23:38:16 2283 [Note] /usr/sbin/mysqld: ready for connections.

Thank you.

It's a thread_id as per https://dev.mysql.com/doc/refman/8.0/en/error-log-format.html :

This log writer produces the traditional error log output. It writes messages using this format:

  timestamp thread_id [severity] [err_code] [subsystem] message

The [ and ] square bracket characters are literal characters in the message format. They do not indicate that fields are optional.


Mysql - which cnf file to update I have mysql 8 running on a ubuntu 20.4 server. I want to apply some setting to optimize the database so that it uses less ram for a web application. typically switching of the performance_schema.
Which cnf file I should be adding my settings? When I run the following command I have get the following information:
$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

/etc/my.cnf file doesn't exist
/etc/mysql/my.cnf contains the following:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

~/.my.cnf doesn't exist.
Then I have the following file /etc/mysql/mysql.conf.d/mysqld.cnf
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql
...

I've added the setting to /etc/mysql/mysql.conf.d/mysqld.cnf file but would be helpful if someone can advise which would be the correct file to add such settings?

By default I always do my changes at /etc/mysql/mysql.conf.d/mysqld.cnf to do not split my changes too much, although, both of those should work /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf, just don't forget restarting you mysql service.


Restore all IDENTITY seeds (out of sync since database restore) We have recently done a full restore on a database from a backup (schema & data).

When trying to insert new rows we are getting the "Cannot insert duplicate key in object" error message, it is trying to insert duplicate keys into an identity column. We are not manually specifying the values for those columns.

We can fix the issue for a specific table by running

DBCC CHECKIDENT ('Table', RESEED, 10);

(changing 10 to be the next available identity value)

However, this issue appears to affect every single table on the whole database. (We use identity PK columns on every table)

Do you know

a) What might have caused this to happen in the first place?

b) If there is an easy way to reseed all tables to use the next available identity value.

Next script generates a DBCC CHECKIDENT command for each table in your server adding 1 to the current IDENT_CURRENT value.

Notice I've commented:

--EXEC (@SCRIPT);

within the CURSOR, I strongly suggest you to check each generated COMMAND before to execute it.

Additionally you can un-comment within the SELECT statement

--AND      T.TABLE_CATALOG = 'YOUR_CATALOG'

just to execute it for one catalog.

Instead of execute each command, you can get results as text, and execute commands from SSMS.

CREATE TABLE T1 (ID INT IDENTITY, FOO INT);
CREATE TABLE T2 (NID INT IDENTITY, BAR INT);
INSERT INTO T1 VALUES (1),(2),(3);
INSERT INTO T2 VALUES (1),(2),(3),(4),(5);
GO

8 rows affected

DECLARE @TABLE_CATALOG NVARCHAR(128),
        @TABLE_SCHEMA NVARCHAR(128),
        @TABLE_NAME NVARCHAR(128),
        @COLUMN_NAME NVARCHAR(128);

DECLARE @SCRIPT NVARCHAR(MAX);
SET @SCRIPT = '';

BEGIN TRY

    CREATE TABLE #SCRIPTS
    (
        COMMANDS NVARCHAR(500)
    );

    DECLARE CURIDENT CURSOR READ_ONLY LOCAL FAST_FORWARD FOR
        SELECT   C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
        FROM     INFORMATION_SCHEMA.COLUMNS C
        JOIN     INFORMATION_SCHEMA.TABLES T
        ON       C.TABLE_CATALOG = T.TABLE_CATALOG
        AND      C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND      C.TABLE_NAME = T.TABLE_NAME
        WHERE    TABLE_TYPE = 'BASE TABLE'
        AND      COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1
        --AND      T.TABLE_CATALOG = 'YOUR_CATALOG'
        ORDER BY C.TABLE_NAME;

    OPEN CURIDENT;

    FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SCRIPT = N'DBCC CHECKIDENT('''
                        + (@TABLE_SCHEMA + N'.' + @TABLE_NAME)
                        + N''', RESEED, '
                        + CAST(COALESCE(IDENT_CURRENT(@TABLE_SCHEMA + N'.' + @TABLE_NAME) + 1, 0) AS VARCHAR(30))
                        + ');'

        INSERT INTO #SCRIPTS VALUES (@SCRIPT);

        --UNCOMENT TO EXECUTE FOR EACH ROW
        --EXEC (@SCRIPT);

        FETCH NEXT FROM CURIDENT INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME;
    END

    CLOSE CURIDENT;
    DEALLOCATE CURIDENT;

    SELECT * FROM #SCRIPTS;

    IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL
        DROP TABLE #SCRIPTS;

END TRY
BEGIN CATCH

    IF CURSOR_STATUS('LOCAL','CURIDENT') > -1
    BEGIN
        CLOSE CURIDENT;
        DEALLOCATE CURIDENT;
    END

    IF OBJECT_ID('tempdb..#SCRIPTS') IS NOT NULL
        DROP TABLE #SCRIPTS;

END CATCH

GO

| COMMANDS                              |
| :------------------------------------ |
| DBCC CHECKIDENT('dbo.T1', RESEED, 4); |
| DBCC CHECKIDENT('dbo.T2', RESEED, 6); |

dbfiddle here

If don't want to execute the whole script, you can get the same result by executing next query:

SELECT   N'DBCC CHECKIDENT('''
         + (C.TABLE_SCHEMA + N'.' + C.TABLE_NAME)
         + N''', RESEED, '
         + CAST(COALESCE(IDENT_CURRENT(C.TABLE_SCHEMA + N'.' + C.TABLE_NAME) + 1, 0) AS NVARCHAR(30))
         + ');'
FROM     INFORMATION_SCHEMA.COLUMNS C
JOIN     INFORMATION_SCHEMA.TABLES T
ON       C.TABLE_CATALOG = T.TABLE_CATALOG
AND      C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND      C.TABLE_NAME = T.TABLE_NAME
WHERE    TABLE_TYPE = 'BASE TABLE'
AND      COLUMNPROPERTY(OBJECT_ID(C.TABLE_SCHEMA + '.' + C.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1
--AND      T.TABLE_CATALOG = 'YOUR_CATALOG'
ORDER BY C.TABLE_NAME;
GO

| (No column name)                      |
| :------------------------------------ |
| DBCC CHECKIDENT('dbo.T1', RESEED, 4); |
| DBCC CHECKIDENT('dbo.T2', RESEED, 6); |

dbfiddle here


Copy Data from Two different databases with FK relation So the situation is as follow:

I have production DB called DB1 which has huge data, around 250 GB.
I have Test DB called DB2 on my local machine which has the same schema as the production DB but only the first two tables are partially filled(UUT_RESULT, STEP_RESULT).

What I want:
Copy the only related data from the Production DB tables (PROP_RESULT) to my local DB table.

Very important note: I can copy the entire table, However, it's huge. That's why I only want to copy the data from PROP_RESULT table in the production DB which has relation to the already existed data in my local DB.

Note: Only UUT_RESULT and STEP_RESULT are partially filled in the Local DB, other tables are empty and I need to populate them and that's why I am posting this.

The following image will make is easier to understand

This could probably be done with some ETL tool but
A manual way comes to mind:
 - just join the test tables together ( I'm guessing the join condition would be STEP_RESULT.UUT_RESULT = UUT_RESULT.ID) to get all the relevant keys.

SELECT a.ID
FROM STEP_RESULT a join UUT_RESULT b
on a.UUT_RESULT = b.ID

So lets say you get a 1000 values of b.ID. Then manually create a select which will have all the values returned from the TEST sql in an IN condition and run it on PROD:

SELECT * FROM PROP_RESULT WHERE STEP_RESULT IN ('ID1','ID2', ...)

Not sure how exporting data in MSSQL works but I'm guessing you could just save that select as a separate table and then just load it into your TEST environment


Could once `max_standby_archive_delay` and `max_standby_streaming_delay` with value -1 can cause replication stop on heavy loads? In my replication servers on a postgresql master-slave replication scheme on aws RDS I get the following error:

SQLSTATE[40001]: Serialization failure: 7 ERROR:  canceling statement due to conflict with recovery

The cause, as far as I understood is that the replication happens simirary like a database migration. A sequence of queries is written something called WAL and then are being executed in a FIFO sequence.

Also, I understood that once you execute queries whilst wal is being executed it can cause conflicts because sometimes the query that is currently is being executed can cause fetching stale data.

So according to documentation there are delays that allow first the current queries to be executed and then apply wal changes. Theese are:

max_standby_archive_delay
max_standby_streaming_delay

But setting these values as -1 on heavy queries (query execution time >30s) will cause the replicas to have stale data for extended period of times?

Yes, that is the idea. In the case of a replication conflict PostgreSQL has only two options:

cancel the query
delay the application of replicated changes.

Setting max_standby_streaming_delay to -1 will delay replication indefinitely long.

There are ways to reduce replication conflicts:

Set hot_standby_feedback = on to remove replication conflicts caused by VACUUM. The price you are paying is that long running queries on the standby can bloat your tables.
Don't have any statements like DROP TABLE, TRUNCATE, ALTER TABLE and similar in your workload that cause ACCESS EXCLUSIVE locks.
In PostgreSQL v12, you can set

ALTER TABLE atable SET (vacuum_truncate = off);

to disable autovacuum truncation (which also causes a short ACCESS EXCLUSIVE lock). In older versions, there is only the crude and undocumented workaround of setting old_snapshot_threshold to something else than the default value.

This is all complicated and may have undesired effects, so the best advice is this: If you want a standby that does not lag more than necessary, but you also want to run long running queries on a standby server, you should use two standby servers, one for each of these conflicting purposes.


Retrieve Names of Check Constraints Specific to a Column PostgreSQL 12
When I rename a column in a table, I would like to dynamically rename the matching check constraints as well.
I have a hypothetical table:
CREATE TABLE hypothetical_table (
  id SERIAL PRIMARY KEY,
  some_col text,
  some_col_also text
);

There are constraints:
ALTER TABLE hypothetical_table ADD CONSTRAINT some_col_length_>_5 CHECK(char_length(some_col) > 5);

ALTER TABLE hypothetical_table ADD CONSTRAINT some_col_also_length_>_5 CHECK(char_length(some_col_also) > 5);

I do not know ahead of time what constraints a column may have. I do know that they are prefixed with the column name. But, like some_col constraint above, that prefix may match another column's (some_col_also).
When I rename a column in this table, I would like to rename the constraints as well, to match the new column name.
So, if I were to rename some_col, how can I select all of the check constraints associated with that column?
I have tried joining pg_catalog.pg_constraint with pg_catalog.pg_attribute ON con.conkey = pga.attnum (with conkey unnested), but that returns many constraints that are not associated with that column.

how can I select all of the check constraints associated with that column?

pg_constraint.conkey is an array column, so we join on a.attnum = ANY(c.conkey) and the oid of the relation additionally. Like:
SELECT conname -- pg_get_constraintdef(oid), *
FROM   pg_constraint c
JOIN   pg_attribute  a ON a.attrelid = c.conrelid     -- !
                      AND a.attnum   = ANY(c.conkey)  -- !
WHERE  c.conrelid = 'hypothetical_table'::regclass
AND    c.contype = 'c'  -- c = check constraint
AND    a.attname = 'some_col';

But I am doubtful of your overall objective:

When I rename a column in a table, I would like to dynamically rename the matching check constraints as well.

CHECK constraints are always table constraints in Postgres and can reference any number of table columns. Even when formulated as column constraints! The manual:

Column Check Constraints
The SQL standard says that CHECK column constraints can only refer
to the column they apply to; only CHECK table constraints can refer
to multiple columns. PostgreSQL does not enforce this restriction; it
treats column and table check constraints alike.

So some_col and some_col_also can be referenced at the same time (multiple times). And both can be in the constraint name. I don't see how a completely dynamic solution can be bullet-proof, unless you can rely on a strict naming convention. (Can you really?). Is the following a reference to some_col or some_col_also?
some_col_also_is_a_fruit  -- oh, wait!

Rewriting the constraint expression itself also demands attention.


How do I rewrite the below Oracle trigger so it will work on SQL Server? I am migrating from Oracle to SQL Server using SSMA and I get thrown an unparsed SQL error on the below code. Can anyone help me convert this to T-SQL? Thanks in advance.

CREATE OR REPLACE TRIGGER "ISTRAINING"."INSERT_COURSE_DELEGATES" BEFORE
INSERT ON "COURSE_DELEGATES" FOR EACH ROW declare
 row_locked exception;
 pragma exception_init (row_locked, -54);
begin
 begin
  select next
  into :new.COURSE_DELE_ID
  from ISTRAINING.sequence
  where tname='COURSE_DELEGATES' and tcolname='COURSE_DELE_ID'
  for update of next nowait;
  exception
  when row_locked then
      raise_application_error (-20002,'Database temporarily locked');
 end;
 update ISTRAINING.sequence
 set next=next+1
 where tname='COURSE_DELEGATES' and tcolname='COURSE_DELE_ID';
end;

The bad news is that SQL Server doesn't support FOR EACH ROW triggers, so you'd have to take extra care to convert the trigger code.

The good news is that this appears to just be doing what can be achieved with an IDENTITY column in SQL Server, and you won't need the trigger at all.

In your CREATE TABLE COURSE_DELEGATES... statement, declare the COURSE_DELE_ID column something like this (use a more appropriate data type if it isn't int):

CREATE TABLE COURSE_DELEGATES (
    COURSE_DELE_ID int NOT NULL IDENTITY(1,1),
    ....
)

Then any rows inserted in COURSE_DELEGATES will get an auto-generated value for COURSE_DELE_ID, starting with 1, and incrementing by 1 for each new row.


Windows Cluster can't update DNS record Setup:
Windows server 2016 standard edition.
SQL server 2016 standard edition.
2 nodes configured in a cluster without witness quorum.
all member of the same Active Directory domain.
1 Availability group for 1 Database only.
1 listener.
Creation went well, and any manual SQL or Cluster fail-over are working properly.

dns domain: mydom.local
Node1 machine Name: Mynode1
Node2 machine Name: Mynode2
Cluster name: mycluster
Listener name: mySQLlistener
Availability Group: MySQLAG

But since then I have regularly this error message in my Cluster logs:
Source: Microsoft-Windows-FailoverClustering

Event ID: 1257

Cluster network name resource failed registration of one or more associated DNS names(s) because the access to update the secure DNS Zone was denied.

Cluster Network name: 'MySQLAG_mySQLlistener'
DNS Zone: 'mydom.local'

Ensure that cluster name object (CNO) is granted permissions to the Secure DNS Zone.

I found this ressource and this ressource which propose to recreate the CNO DNS record, but in the error message it is not the CNO for which it raise an error it is a Network name I don't use at all…  Built with the Availability Group + ListenerName.

Which is even more strange is that this network name is created with an "_" which is not "legal" for host names as per my understanding.
so I'm wondering if I'm not having another issue…

Any idea why it raise this error would be much appreciated.

I finally fixed my issue by re-creating both DNS A record:
So in my example it is those two hostnames:
Cluster name: mycluster
Listener name: mySQLlistener

And when creating those records I have checked "allow any authenticated user to update DNS record with the same owner name".

Hope that helps.


Why can't I create temporary tablespace? I'm trying to create dictionary managed tablespace with the TEMPORARY clause and TEMPFILE clause of CREATE TABLESPACE  statement. But oracle says I can't use the DEFAULT STORAGE clause doing that... What is the error here ?

CREATE TABLESPACE myTempTableSpace
TEMPFILE '/home/folder/tempfile.dbf' size 1M
DEFAULT STORAGE
(
INITIAL 1M
PCTINCREASE 0
)
TEMPORARY
EXTENT MANAGEMENT DICTIONARY;

The error is :

  ORA-02180: invalid option for CREATE TABLESPACE

To create a temp tablespace, the syntax is CREATE TEMPORARY TABLESPACE, not CREATE TABLESPACE.

CREATE TEMPORARY TABLESPACE myTempTableSpace
  TEMPFILE '/home/folder/tempfile.dbf' SIZE 1M
;

I wouldn't set any storage clauses, unless you have a good reason to do so.

Documentation link here.