Can someone guide me how to compare tables with SSIS I have two tables in two different databases in two different servers.

Both tables have an orderid column, and a given value for orderid references the same entity in both tables.

I want to check whether each value for orderid in either table has a matching entry in the other table, and write a record to table3 based on this information. table3 has the same basic columns as table2, with the additon of a status column.

Server1
Database1
Table 1
orderidproductpricequoted pricediscounted price

Server2
Database 2
Table 2
orderidcreatedonmodifiedon

Table 3
orderidcreatedonmodifiedonstatus

The status can be matching,not matching,only in table 1,only in table 2,error. If in both orderid is same the status will be matching, if it's there only in table 1 it becomes only in table 1, if found only in table
 2 it's only in table 2 not sure whether not matching & error are relevant in this scenario.

I have crafted an sql for it, just let me know how to use it in SSIS

insert into Table3 (orderid,createdon,modifiedon,status)
select
    COALESCE(t1.orderid,t2.orderid) as orderid,
    COALESCE(t1.CREATE_DATE,t2.CREATE_DATE) as createdon,
    COALESCE(t1.UPDATE_DATE,t2.UPDATE_DATE) as modifiedon,
    case when t1.orderid is null then 'only in table 2'
         when t2.orderid is null then 'only in table 1'
         when t1.orderid = t2.orderid then 'matching'
         when t1.orderid != t2.orderid then 'not matching'
         else 'error'
    from Table1 t1
    full outer join Table2 t2
    on t1.orderid = t2.orderid

If you really want to do this in SSIS, add 2 sources with the SQL from each table and link them to a Merge transformation, which will do a FULL JOIN (select it from the drop down) operation on your columns orderid between the 2 data sets. SSIS needs both data sources to be ordered by the column you are joining so either add an ORDER BY orderID on both your sources and mark the column orderID as SORT on the advanced editor (so the merge knows that data is sorted by that column), or add a custom Sort transformation between the source and the merge on each flow to explicitly order by this column.

Then you can add a Derived Column transformation right after the merge with an expression to determine if either:

OrderID1 is NULL (record is from data set 2).
OrderID2 is NULL (record is from data set 1).
OrderID exists on both data sets.

If you also need to know if data changed when both order ID's match, then you will have to write an awful expression comparing each column against each other, making sure to consider NULL values. SSIS isn't good to build expressions (they are hard to read and even harder to maintain), that's why I always recommend to do this directly on SQL.

Finally link the derived column to your destination database.

It's worth mentioning that from your FULL JOIN example, the following CASE scenario:

case
    when t1.orderid is null then 'only in table 2'
    when t2.orderid is null then 'only in table 1'
    when t1.orderid = t2.orderid then 'matching'
    when t1.orderid != t2.orderid then 'not matching'
    else 'error'
end

Can be simplified into:

case
    when t1.orderid is null then 'only in table 2'
    when t2.orderid is null then 'only in table 1'
    else 'matching'
end

Since you are joining by orderid, either they don't join on each side (option 1 & 2) or they match exactly (else), there is no way that they can join but differ in value.

This youtube one worked for me. I had the same issue years back. Its not so easy from the online instructions books. It they all seem to assume to much. I used a Youtube tutorial. That took you through the steps.

https://www.youtube.com/watch?v=UVDBmD6Vsok

However be sure to add sorts if your not using order by.. But you can't use this to compare any new columns being added unless you compare sys tables using a specific sql query.


Is it possible to COPY tables into PostgreSQL without setting structure first? I'm just getting to grips with PostgreSQL 9.6.

I'm wondering if there is a simple way to add a .csv table to a database without first creating the table structure in psql or pgAdmin?

Or...I've not used scripts before but if someone could suggest one and give me a quick run through, I'd be very grateful.

If you can live with all columns being text and if the CSV file has at least a header line with column names, then one ugly solution could be:

Create a table with a single column. Then import the first row of the CSV file into that table. You can then get a list of the names and generate a CREATE TABLE statement from that.

Something like:

create table header_names (header_line text);

Then import the first row into that table.

Something like (untested!):

\copy header_names from (program 'head -n 1 data.txt')

\copy and COPY do not have a way of limiting the number of rows that are imported, so you need to use head to just get the first line.

Then use:

select 'create table import ('||string_agg(col_name||' text', ', ')||');'
from header_names
   cross join lateral unnest(string_to_array(header_line,',')) as x(col_name);

The second parameter to the string_to_array() function is the delimiter of your CSV file.

Assuming you have a header like:

id,first_name,last_name

The above will generate:

create table import (id text, first_name text, last_name text);

CSV does not contain data type information, so it's impossible to create the table structure from CSV files.


mysqlimport locks all tables system wide I recently developed a script that spawns multiple processes to import tables in parallel using mysqlimport and a --tab type mysqldump export. On the development server it works very well and compared to a standard mysql db_name < backup.sql type of import it cuts the time from around 15 minutes to 4 or 5 minutes.

The problem is on our production server this script seems to be locking tables system wide. That is to say, I'm importing a backup to a completely different database but our live application tables still end up locked. A SHOW PROCESSLIST confirms that tables on our live db are indeed locked but no INSERT or UPDATE queries are running on any tables in that database.

Why is this happening? Is there a configuration variable / setting that I can adjust to prevent this lock from happening?

If you start mysqlimport with --lock-tables=0 then there will be no locks.

mysqlimport uses LOAD DATA rather than INSERT/UPDATE.

(The Question is about mysqlimport, and has been answered.  This 'Answer' is about the underlying goal, which I will restate.)

How to rapidly clone a single database (to a database with another name) on a single instance of MySQL?

One thought is have dev_db created and empty, then do something like

mysqldump  prod_db  |  mysql  dev_db

Caveats:

Make sure it does (or does not do) CREATEs and DROPs and "locks".
For copying two databases, consider running them in parallel.
For multiple tables, consider splitting up the tables across different dump|load pairs.
But watch out for FOREIGN KEY issues.

By using a pipe, the intermediate file does not need to touch the disk (at least not for *nix OS.)


Why use both TRUNCATE and DROP? In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them.

Many of my colleagues (almost all of whom are much more experienced than I am) typically do this:

TRUNCATE TABLE #mytemp
DROP TABLE #mytemp

I typically use a single DROP TABLE in my scripts.

Is there any good reason for doing a TRUNCATE immediately before a DROP?

No.

TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary.

Note: I wrote this answer from a SQL Server perspective and assumed it would apply equally to Sybase. It appears that this is not entirely the case.

Note: When I first posted this answer, there were several other highly rated answers -- including the then-accepted answer -- that made several false claims like: TRUNCATE is not logged; TRUNCATE cannot be rolled back; TRUNCATE is faster than DROP; etc.

Now that this thread has been cleaned up, the rebuttals that follow may seem tangential to the original question. I leave them here as a reference for others looking to debunk these myths.

There are a couple of popular falsehoods -- pervasive even among experienced DBAs -- that may have motivated this TRUNCATE-then-DROP pattern. They are:

Myth: TRUNCATE is not logged, therefore it cannot be rolled back.
Myth: TRUNCATE is faster than DROP.

Let me rebut these falsehoods. I am writing this rebuttal from a SQL Server perspective, but everything I say here should be equally applicable to Sybase.

TRUNCATE is logged, and it can be rolled back.

TRUNCATE is a logged operation, so it can be rolled back. Just wrap it in a transaction.

USE [tempdb];
SET NOCOUNT ON;

CREATE TABLE truncate_demo (
    whatever    VARCHAR(10)
);

INSERT INTO truncate_demo (whatever)
VALUES ('log this');

BEGIN TRANSACTION;
    TRUNCATE TABLE truncate_demo;
ROLLBACK TRANSACTION;

SELECT *
FROM truncate_demo;

DROP TABLE truncate_demo;

Note, however, that this is not true for Oracle. Though logged and protected by Oracle's undo and redo functionality, TRUNCATE and other DDL statements can't be rolled back by the user because Oracle issues implicit commits immediately before and after all DDL statements.
TRUNCATE is minimally logged, as opposed to fully logged. What does that mean? Say you TRUNCATE a table. Instead of putting each deleted row in the transaction log, TRUNCATE just marks the data pages they live on as unallocated.  That's why it's so fast.  That's also why you cannot recover the rows of a TRUNCATE-ed table from the transaction log using a log reader.  All you'll find there are references to the deallocated data pages.

Compare this to DELETE.  If you DELETE all the rows in a table and commit the transaction you can still, in theory, find the deleted rows in the transaction log and recover them from there. That's because DELETE writes every deleted row to the transaction log. For large tables, this will make it much slower than TRUNCATE.

DROP is just as fast as TRUNCATE.

Like TRUNCATE, DROP is a minimally logged operation.  That means DROP can be rolled back too. That also means it works exactly the same way as TRUNCATE.  Instead of deleting individual rows, DROP marks the appropriate data pages as unallocated and additionally marks the table's metadata as deleted.
Because TRUNCATE and DROP work exactly the same way, they run just as fast as one another.  There is no point to TRUNCATE-ing a table before DROP-ing it. Run this demo script on your development instance if you don't believe me.

On my local machine with a warm cache, the results I get are as follows:

table row count: 134,217,728

run#        transaction duration (ms)
      TRUNCATE   TRUNCATE then DROP   DROP
==========================================
01       0               1             4
02       0              39             1
03       0               1             1
04       0               2             1
05       0               1             1
06       0              25             1
07       0               1             1
08       0               1             1
09       0               1             1
10       0              12             1
------------------------------------------
avg      0              8.4           1.3

So, for a 134 million row table both DROP and TRUNCATE take effectively no time at all. (On a cold cache they take about 2-3 seconds for the first run or two.) I also believe that the higher average duration for the TRUNCATE then DROP operation is attributable to load variations on my local machine and not because the combination is somehow magically an order of magnitude worse than the individual operations. They are, after all, almost exactly the same thing.

If you're interested in more detail about the logging overhead of these operations, Martin has a straightforward explanation of that.

The point of the truncate is to simply and irrevocably remove everything in the table (some technical specifics based on data store engines may differ slightly) -- skipping heavy logging, etc.

drop table logs all changes as the changes are being made. So, to have minimal logging and reduce useless system churn, I would suspect a very large table could be truncated first, then dropped.

truncate may be wrapped in a transaction (which would be the safest option) which, of course, will allow you to roll back the operation.


Stored procedure working when run manually, not running in SQL Agent Job When I execute a Stored Procedure through a SQL Agent Job, I get the following error message:

  Executed as user: NT AUTHORITY\SYSTEM.
  XML parsing: line 10, character
   33, unexpected end of input [SQLSTATE 42000] (Error 9400).
  The step failed.
  Sql Severity 16, Sql Message ID 9400

However, when I run it manually, it is successful.

This is the procedure which I am executing:

decalre @URL VARCHAR(max)
 set @URL='http://www.spa.gov.sa/english/rss.xml'

 declare  @xmlT TABLE ( yourXML XML )
DECLARE @Response nvarchar(max)
DECLARE @XML xml
DECLARE @Obj int
DECLARE @Result int
DECLARE @HTTPStatus int
DECLARE @ErrorMsg nvarchar(MAX)

EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT

EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false
EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT

INSERT @xmlT ( yourXML )
EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT

    INSERT into Tlb(discp , tit ,   datee,linkk)
    SELECT   N.C.value('description[1]', 'nvarchar(max)') discp,   N.C.value('title[1]', 'varchar(999)') tit,
    N.C.value('pubDate[1]', 'varchar(99)') datee,N.C.value('link[1]', 'varchar(999)') linkk
    FROM @xmlT CROSS APPLY yourXML.nodes('//channel/item') N(C)

I had this same issue when google geocoding and parsing the XML result.  Seems that when run via an SQL job the XML result was being truncated.

Found the solution here: http://www.progtown.com/topic376715-a-problem-with-spoagetproperty-p2.html

Had to put:

SET TEXTSIZE 2147483647;

at the start of my stored proc.

Adding on sql Agent commang job

SET TEXTSIZE 2147483647;
exec (your proc name)

works


Is it possible to upgrade Oracle 8i to 12c? We have an old server running Oracle 8i.

Is it going to be possible to directly update it to something newer like Oracle 12c, or would that require a complete uninstall and re-install?

If we had to completely uninstall and re-install, is it then a case of importing the database dump from Oracle 8i and it will be converted, or is that also a manual task?

Update

Thanks for everyones input. I have checked with our application vendor (the ones who originally installed Oracle 8i for us around 10 years ago) and their application that we use does work with Oracle 12c but they are not willing to do the update for us.

They have basically said that we should do the update, preferably on a new server and at that point they would be willing to get involved to check their application out using the new database version etc.

Is there some way to find Oracle experts capable of doing this job (preferably UK based)? I'm pretty new to Oracle (past experience is mainly Microsoft SQL Server), day to day management/security, scripting etc. is ok but I'd rather have someone in who has done this (many times?) before.

Thanks again!

There is no in-place upgrade possible but the existing exp utility is still supported for importing dmp files.

Some of the issues you need to research are:

I have taken an export from an 8i database and imported it into an 11g with no issues. Importing into 12 should not be a problem but planning is everything!
will you use the same character set?  Often organizations start with western only and need to use UTF16 for more compatibility with other languages.
do yourself a favour and do an install on a new machine with new hardware.  12 requires more of everything, memory, hard drive.
many 8i parameters are not supported so you need to establish if there are any custom settings that you need equivalents for

Of course this would need to be tested.

One option would be to use something like Erwin or Toad to reverse engineer the schema, create a new 11g/12c database with all of the tablespaces that you need, generate a script that would create all of the schemas and their types and tables without constraints, create the schemas and tables, create a db link to the old database from the new database, do insert into for each table, generate and run a script to create all of the constraints, views and stored procedures. You will want to script it and run the conversion more than once while you work out the details. You also want to check your stored procedures for any code for set transaction commands. As you probably know rollback segments went away with 9i, thus you can no longer set certain code to use a particular rollback segment. You will also be switching from dictionary managed table spaces to locally managed table spaces. Which means that you want to ignore any segment information in your tables. Just use automatic storage for your segments and let the database track extent size and make sure that pctincrease is set to 0. In addition check your block size 8k is usually good for most applications.

If you have a lot of data, you can split the inserts into multiple scripts that run from different sessions, thus you can do it in parallel. While you can do an export/import you don't want to import the data dictionary from 8i and you don't want to keep the old extent sizes.


SQL Server xml performance I have a table with 21 columns. The majority are numerical and the rest are nvarchar.

I need to add 4 columns that will contain XML data for each row. The XML data for each row can be anywhere from 200 to 2,000 lines.

The question is:

Does adding the XML typed columns to the same table change the speed of querying that table?
What are the performance benefits when I add the xml typed columns to another table and join both tables when doing queries?
Is it better to encode the XML data to shrink it and decode it in the application?

There are a lot of facets to this, so it really depends.

  Does adding the XML typed columns to the same table change the speed of querying that table?

More data potentially could be read (I/O), but when you say speed of querying, if you mean finding rows based on columns outside of the XML, this should usually be based on a index and so the xml should not be especially involved in any performance differences

xml columns can be stored in row or out of row, with obvious implications for number of rows which can be stored in a page, fragmentation etc...  http://technet.microsoft.com/en-us/library/ms189087(v=sql.105).aspx  Storing out of row should mean that more rows fit per page, so less data is read and discarded when you don't actually have the xml data stored in-row.

  What are the performance benefits when I add the xml typed
  columns to another table and join both tables when doing queries?

Well, this is basically equivalent to storing out of row.  You'll only get an administrative  benefit doing it yourself in terms of being able to manage the other table independently.

  Is it better to encode the XML data to shrink it and decode it in the application?

Perhaps.  If we're assuming that you don't need to query it in the database (xml data type with xml query techniques), then why stored it as xml at all instead of varbinary or varchar?

Increasing the size of a table will always have a negative impact on performance. So adding XML columns will have a negative impact.

Splitting the tables will result in more flexibility as you will not always shuffle between the xmls.

That depends on your need to browse and use the xmls on the server side. SQL does not keep the xml in the database as a string (nvarchar) but in an optimized form. If you do not need to use it as it is in tsql then you will not need to keep it as an XML datatype so that means you can compress it as you want and keep it even binary.


How to move a 80GB MySQL Database with least effort and least time offline? Im a software developer with 4 years experience but brand new in my company, my first job is to move a 80GB mySQL database from a very very small and old database server to a brand new.
The problem: It is the alltime working database for nearly everything in my company... I can't reduce the data to less gb because it is all needed data.
So: How to do this and how to achieve this with the least time offline so around 500 people can work again fastly...

Information requested for Help:

SELECT COUNT(*) as '# TABLES', CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') as DATA, CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') as INDEXES, CONCAT(sum(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2)), 'G') as TOTAL, engine as ENGINE FROM information_schema.TABLES GROUP BY engine;

Answer:

3 NULL NULL NULL NULL
13 1.36G 1.70G 3.05G InnoDB
13 0.00G 0.00G 0.00G MEMORY
1159 44.04G 34.89G 78.90G MyISAM

The main problem with MyISAM is that you cannot create a copy of the database without locking the tables (otherwise the copy may be inconsistent). I would recommend you to research a potential migration to InnoDB.

You will have to execute FLUSH TABLES WITH READ LOCK in order to lock all tables in read only mode, then copy all the .MYD, .MYI and .frm files from the filesystem (basically, your entire datadir). Once you copy those files, you will want to get your binary log coordinates so you can setup replication with SHOW MASTER STATUS. If you cannot setup replication, you will have to leave the server in  read_only mode while you setup the other one. Otherwise, UNLOCK TABLES;. In previous versions of MySQL, you had the mysqlhotcopy script that automatized some of those tasks for MyISAM.

EDIT: As you have some InnoDB tables, you must be sure to copy the whole data directory (including ib_logfile* and ib_data*ones), and not only single files- otherwise the copy won't succeed.

Setup the new server copying the files to the right place, start the new server, and you can now setup a replication between the old server and the new one. Once you double check that the setup is correct and the key_buffer/buffer pool is warm, you will put the old server in read_only mode and failover your applications to the new one.

The idea is to avoid mysqldump, which is slower for exporting, but above all, for importing, although it is easier to use. That it is what it is internally executed with phpMyAdmin's "export/import database" feature.

I know this is a high level overview, but I recommend you to start reasearching the links I mentioned and about MySQL replication and asking additional specific questions if needed.

Do you have something like log shipping as in SQL Server?
That's what I would use in SQL Server. A matter of a few minutes downtime.


How to select distinct for one column and any in another column? I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another column.  For example, consider the following table with two columns, key and value:

key     value
===     =====
one     test
one     another
one     value
two     goes
two     here
two     also
three   example

I wish to get back one sample row, chosen arbitrarily, from each distinct key, perhaps getting these three rows:

key     value
===     =====
one     test
two     goes
three   example

How can I formulate such a query in SQL?

The easiest query to write is for MySQL (with not strict ANSI settings). It uses the non-standard construction:

SELECT key, value
FROM tableX
GROUP BY key ;

In recent version (5.7 and 8.0+) where the strict settings and ONLY_FULL_GROUP_BY are the default, you can use the ANY_VALUE() function, added in 5.7:

SELECT key, ANY_VALUE(value) AS value
FROM tableX
GROUP BY key ;

For other DBMSs, that have window functions (like Postgres, SQL-Server, Oracle, DB2), you can use them like this. The advantage is that you can select other columns in the result as well (besides the key and value) :

SELECT key, value
FROM tableX
    ( SELECT key, value,
             ROW_NUMBER() OVER (PARTITION BY key
                                ORDER BY whatever)     --- ORDER BY NULL
               AS rn                                   --- for example
      FROM tableX
    ) tmp
WHERE rn = 1 ;

For older versions of the above and for any other DBMS, a general way that works almost everywhere. One disadvantage is that you cannot select other columns with this approach. Another is that aggregate functions like MIN() and MAX() do not work with some datatypes in some DBMSs (like bit, text, blobs):

SELECT key, MIN(value) AS value
FROM tableX
GROUP BY key ;

PostgreSQL has a special non-standard DISTINCT ON operator that can also be used. The optional ORDER BY is for selecting which row from every group should be selected:

SELECT DISTINCT ON (key) key, value
FROM tableX
-- ORDER BY key, <some_other_expressions> ;


Export image file from bytea column I am trying to export an image file from a PostgreSQL database.The users_data_circulation table has a photo (bytea) column.

My command:

copy (select encode(photo,'hex') from users_data_circulation limit 1)
    TO '/tmp/imagetest.hext';

In operating system:

$> xxd -p -r /tmp/imagetest.hex > /tmp/imagetest.jpg
$> file /tmp/imagetest.jpg
/tmp/imagetest.jpg: ASCII TEXT

I can't open the jpg file. How can I convert this file to jpg?

devinim@devinimpostgresql:~$ hexdump -C /tmp/image.hex | more
00000000  5c 5c 33 37 37 5c 5c 33  33 30 5c 5c 33 37 37 5c  |\\377\\330\\377\|
00000010  5c 33 34 30 5c 5c 30 30  30 10 4a 46 49 46 5c 5c  |\340\\000.JFIF\\|
00000020  30 30 30 01 02 5c 5c 30  30 30 5c 5c 30 30 30 01  |000..\\000\\000.|
00000030  5c 5c 30 30 30 01 5c 5c  30 30 30 5c 5c 30 30 30  |\\000.\\000\\000|
00000040  5c 5c 33 37 37 5c 5c 33  34 31 5c 5c 30 30 30 5c  |\\377\\341\\000\|
00000050  76 50 49 43 5c 5c 30 30  30 02 5c 6e 5c 6e 01 5c  |vPIC\\000.\n\n.\|
00000060  5c 30 30 30 5c 5c 33 37  37 5c 5c 33 37 36 5c 5c  |\000\\377\\376\\|
00000070  30 30 30 21 50 69 63 74  75 72 65 20 45 6c 65 6d  |000!Picture Elem|
00000080  65 6e 74 73 2c 20 49 6e  63 2e 20 49 53 45 2f 53  |ents, Inc. ISE/S|
... continues like that

It's a horrible idea to store jpegs in the database.

That said, if you want to get it out of the database, you can use psql. This gets it out as hex.

psql -t -A -o "/tmp/imagetest.jpg" -c \
  "SELECT photo FROM users_data_circulation LIMIT 1";

You may also want to check out the large object.

DO $$
DECLARE
   l_lob_id OID;
   r record; BEGIN

  for r in
    select data, filename from bytea_table

   LOOP
    l_lob_id:=lo_from_bytea(0,r.data);
    PERFORM lo_export(l_lob_id,'/home/...'||r.filename);
    PERFORM lo_unlink(l_lob_id);
    END LOOP;

END; $$


Row-row based logging in a blackhole replication filter setup? I am using two MySQL server instances on the same server to filter replication to a third external server. My filter slave is using the blackhole engine as described here: https://dev.mysql.com/doc/refman/5.7/en/blackhole-storage-engine.html

Both master and slave use statement based replication. The documentation says:

  Inserts into a BLACKHOLE table do not store any data, but if statement
  based binary logging is enabled, the SQL statements are logged and
  replicated to slave servers. This can be useful as a repeater or
  filter mechanism.

The above statement makes me assume that if I had both of my MySQL instances set to row based replication, nothing would make it to the third, external database. Which kind of makes sense since there are no actual rows in the filtering blackhole database.

However, I have been thinking... Would it not be possible for the filtering middle instance to simply pass on any row based instructions it receives to its own binlog, i.e. would a row-row filtering blackhole setup not work?

https://dev.mysql.com/doc/refman/8.0/en/replication-features-blackhole.html -->

The BLACKHOLE storage engine accepts data but discards it and does not store it. When performing binary logging, all inserts to such tables are always logged, regardless of the logging format in use. Updates and deletes are handled differently depending on whether statement based or row based logging is in use. With the statement based logging format, all statements affecting BLACKHOLE tables are logged, but their effects ignored. When using row-based logging, updates and deletes to such tables are simply skipped—they are not written to the binary log. A warning is logged whenever this occurs.
For this reason we recommend when you replicate to tables using the BLACKHOLE storage engine that you have the binlog_format server variable set to STATEMENT, and not to either ROW or MIXED.


Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file source (and is the primary key of the imported table.)

The following query works perfectly

SELECT *
  FROM [CLIENT]
  left outer join
  (
    select cast(ENTRY_CODE as int) as ENTRY_CODE, ENTRY_DESCRIPTION
    from [ENTRY]
    inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
    where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
  ) as discounts on CLIENT_CLASS = ENTRY_CODE
  where Client_recd = 'A'
  --and client.[FileRecordID] = 10607

I get the exact number of rows I was expecting and everything is fine. However if I uncomment that last line so I only show the record from the client data table who has  client.[FileRecordID] = 10607 it reports to me

  Msg 245, Level 16, State 1, Line 1
  Conversion failed when converting the varchar value 'BK/TN' to data type int.

The problem is from cast(ENTRY_CODE as int) as ENTRY_CODE in the first outer join. ENTRY can contain text in the ENTRY_CODE column, however all records that have ENTRY_TABLE set to the line number from TABLE where TABLE_CODE = 'CLTDISC' will always be numbers only. I perform the cast as ENTRY_CODE may or may not have leading 0's and spaces so I am trying to get them to be well formed.

What appears to me to be happening is adding client.[FileRecordID] = 10607 to the outer query causes the where clause not to be evaluated before the cast to int in the inner query.

I have tried things like adding and ISNUMERIC(ENTRY_CODE) = 1 to both the inner and outer queries but it has no affect.

How do I fix this?
Can anyone explain what is happening so I can understand how to avoid this in the future?

Non filtered query planed execution

Filtered (non working) query planned execution

Notice the reversed order of the Compute Scalar and the Filter

Other things I have tried that do not work:

Move TABLE_CODE = 'CLTDISC' in to the on clause in the inner join.
Added OPTION (FORCE ORDER) query hint to the query.

Update:
I found a solution but it is very "hackish" IMHO, I really would still like a explanation of how to avoid things like this in the future and how to do this correctly.

SELECT *
  FROM [CLIENT]
  left outer join
  (
    select ENTRY_CODE, ENTRY_DESCRIPTION
    from [ENTRY]
    inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
    where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
  ) as discounts on CLIENT_CLASS = REPLACE(LTRIM(REPLACE(ENTRY_CODE, '0', ' ')), ' ', '0')
  where Client_recd = 'A'
  and client.[FileRecordID] = 10607

I'm sure there are dba.se and StackOverflow duplicates, but it was far faster for me to post this link:

https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors

In short, the Query Optimizer is really free to rewrite the plan as it sees best.  Sometimes, it means a column is transformed (CAST -> int) close to retrieval so that it doesn't have to be carried many steps forward before performing the CAST.

The only sure-fire way of preventing the error is to look at the CAST expression itself, e.g.:

cast(CASE WHEN ISNUMERIC(ENTRY_CODE)=1 THEN ENTRY_CODE END as int)

To test for values that can be converted to INT successfully, you need to test that all the characters are digits, or in other words, the string doesn't contain any non-digit characters.

WHERE EntryCode NOT LIKE '%[^0-9]%'

The query optimizer should push the predicate down into the index scan on ENTRY, eliminating the rows before trying to convert them to INT.


Oracle startup error (could not open parameter file) I installed the Oracle 11g server on Ubuntu 12.10. But I can't start the server. When I start up the server, the following error occurs:

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora'

How can I solve this issue? I think the connect failed because target host or object does not exist. How can a target host be specified?

Running:

$ ls -latr /u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora

returns:

-rw-r--r-- 1 oracle dba 2637 Mar 13 17:07 /u01/app/oracle/product/11.2.0/xe/dbs/initxe.ora

Running echo $ORACLE_SID displays nothing - an empty line.

I see two things wrong here.  Oracle tries to open a parameter file in the $ORACLE_HOME/dbs directory with the format "spfile" + $ORACLE_SID + ".ora".  If it cannot find it, it then tries to open "init" + $ORACLE_SID + ".ora".  This is where the problem(s) are.

By virtue of the fact that Oracle is trying to open a parameter file named "initXE.ora", your SID must be (or was at one point) equal to "XE".

1) This is the easy part, so try this first.  Since (in your comments) doing an echo $ORACLE_SID yields nothing, try setting it.

export ORACLE_SID=XE

Then try starting Oracle again.

2) In your comments above, you show the file is named "initxe.ora".  Is that really the name of the file?  Or did your paste into a comment somehow lowercase the output of the ls command?

The reason I point this out, is that Ubuntu (Linux) has a case-sensitive filesystem.  So initxe.ora and initXE.ora are actually two different files.  So if your comment is correct and the file is lower-cased, you'll want to set your ORACLE_SID to "xe" instead:

export ORACLE_SID=xe

Try that, and see if it works.

find "initXE.ora" in your oracle directory and used :
SQL> startup pfile=<directory_name>/initXE.ora


Running MongoDB and SQL Server on the same host- memory pressure We have a new application that is making use of both traditional MS SQL and MongoDB for data storage. We're running both instances on the same Windows 2008R2 x64 machine, with 64GB RAM.

There have been some anecdotal reports of poor performance that I've begun to investigate.

I can see that SQL Server is not consuming as much memory as I would expect, and is showing signs of memory pressure.

Eg.

AppDomain 16 (mssqlsystemresource.dbo[runtime].15) is marked for unload due to memory pressure

In contrast, MongoDB is using a huge working set, which is expected behavior based on the documentation

It relies on Windows to manage memory use.

It seems that SQL server isn't able to, or isn't requesting use any of the memory in the Mongo working set and that is impacting performance. The server is under very low load at the moment.

Page Life Expectancy seems high at: 1536
and Buffer cache hit ratio: 404002
Free list stalls/sec: 695
Free pages: 26529

Has anyone come across this issue? What it the expected behavior of SQL server in this scenario? I'm considering using the min server memory configuration option.

Set the min server memory option for SQL Server.  It will still release pages under memory pressure, unless you also allow SQL Server to lock pages in memory. The combination of setting min server memory and locking pages in memory should resolve the problem.

I've never used MongoDB, so I don't know if you can manage memory usage.  A quick search makes it seem you can't.

Assuming you're running a 64-bit server, and have more than 4 GB of RAM, you could run a 32-bit MongoDB instance, which will limit the memory to 2, 3, or 4GB depending on it's architecture.  Does a 32-bit MongoDB even exist?

Right click on your server in SSMS, select Properties.

Go to the Memory tab and set the mininum server memory to some reasonable value.

We were having perf issues due to mongo starving SQL Server, causing it to swap to the disk a lot. We then set the minimum to be 8GB (out of 32GB) and within a minute it started to get better.


Adhoc Queries filling up plan cache I have a bloated plan cache because of Adhoc queries.
I have SQL job that goes in and purges Adhoc plans every hour. (This process was adopted from Kimberly Tripp in regards to Plan cache bloat).
When I look at the top 50 adhoc plans I see this (with much more content in each row) for all 50:

Select '100', 0, ''; begin try dbcc inputbuffer(100) with no_infomsgs end try begin catch select 'RPC Event',0,'(unknown)' end catch; Select '102', 0, '';

How can I prevent that from filling up my plan cache? BTW..
I do have optimize for ad hoc workloads on.

Thanks,
Shelz

Following can be used to clean adhoc and prepared query plan cache . This is from Glenn Berry - Eight Different Ways to Clear the SQL Server Plan Cache

DBCC FREESYSTEMCACHE ('SQL Plans')

It also says:

  “I would argue that running DBCC FREEPROCCACHE does not cause that much distress with a modern processor, even on a very busy OLTP system. It will cause a pretty minor CPU spike for a few seconds on most systems as the query plans get recompiled as they are executed. “

Following query can give adhoc queries in the plan cache

SELECT  DISTINCT
        QCP.objtype
        ,MultipleQ.PlanCount
        ,qStat.query_hash
        ,sText.text AS QueryText
        ,size_in_bytes
        ,qStat.creation_time
FROM (
        SELECT query_hash,
               COUNT(query_hash) AS PlanCount
        FROM sys.dm_exec_query_stats
        GROUP BY query_hash
    ) AS MultipleQ
INNER JOIN sys.dm_exec_query_stats qStat ON MultipleQ.query_hash = qStat.query_hash
INNER JOIN sys.dm_exec_cached_plans  QCP
    ON QCP.plan_handle = qStat.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qStat.sql_handle) AS sText
CROSS APPLY sys.dm_exec_query_plan(qStat.plan_handle) AS qp
WHERE PlanCount > 1
AND QCP.objtype = 'Adhoc'
ORDER BY MultipleQ.PlanCount DESC

If you have Query Store you can use “Delete ad-hoc queries” as defined in
Monitoring performance by using the Query Store

Enabling optimize for adhoc workloads can be found in  Kimberly Tripp - Plan cache and optimizing for adhoc workloads


Determining SQL Server Server Edition without database access Anyone have an idea how to get Microsoft SQL Server Edition (Express, Standard, Enterprise, Developer) without access to the database or registry?

I got the version like this:

$SQLFP = (Get-WmiObject -Class Win32_Service -ErrorAction Stop | Where-Object { $PSItem.Name -eq 'MSSQLSERVER' } | Select-Object -ExpandProperty PathName).Split('"')[1]
$SQLVer = ([System.Diagnostics.FileVersionInfo]::GetVersionInfo($SQLFP).ProductVersion).Split('.')[0]

Switch ($SQLVer) {
    "10" { "Version: Microsoft SQL Server 2008/R2" }
    "11" { "Version: Microsoft SQL Server 2012" }
    "12" { "Version: Microsoft SQL Server 2014" }
    "13" { "Version: Microsoft SQL Server 2016" }
    "14" { "Version: Microsoft SQL Server 2017" }
    Default { "Unsupported version." }
}

I found plenty of ways to determine the Edition, Patch Levels, Service Packs and stuff from Registry and via SSMS, but unfortunately I have access to neither of them. I do have permission to access the file system.

If you have access to file system, one way of getting information could be to check available folders under %PROGRAMFILES%\Microsoft SQL Server\xyz

where xyz translates to:

 80 = SQL Server 2000    =  8.00.xxxx
 90 = SQL Server 2005    =  9.00.xxxx
100 = SQL Server 2008    = 10.00.xxxx
105 = SQL Server 2008 R2 = 10.50.xxxx
110 = SQL Server 2012    = 11.00.xxxx
120 = SQL Server 2014    = 12.00.xxxx
130 = SQL Server 2016    = 13.00.xxxx

Further you can analyze the installation summary file located at
%PROGRAMFILES%\Microsoft SQL Server\xyz\Setup Bootstrap\Log\Summary.txt
which will give you package properties that contains information about Installation edition (Enterprise).


Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships As an example, let's say I have a fact table with two dimensions and one measure

FactMoney table

ProjectKey int

PersonKey int

CashAmount money

The two dimensions are defined like this:

DimProject (a type 0 dimension - i.e. static)

ProjectKey int

ProjectName varchar(50)

DimPerson (a type 2 slowly changing dimension)

PersonKey int

PersonNaturalKey int

PersonName varchar(50)

EffectiveStartDate datetime

EffectiveEndDate datetime

IsCurrent bit

Pretty straightforward so far. Now I'll introduce a Person Category concept.

DimCategory

CategoryKey int

CategoryName varchar(50)

And build an M2M relationship between DimPerson and DimCategory

BridgePersonCategory

PersonKey int

CategoryKey int

So - people can have 1..n categories.

My problem is - as Person is a slowly changing dimension, when a person's name changes, we add a new person row and update our effective dates and is current flags, no big deal.

But what do we do with the person's categories? Do we need to add more rows to the bridge table every time a new person version pops up?

And as a corollary, if a person's categories change, does that mean we need to create a new row in the person table?

(please note I've also asked this question at stack-O and as directed by the first commenter, now posted this question here.)

Does DimCategory have any Type 2 attributes that you are tracking?

If it is not, then you would only need to add a new record to your bridge table based on whenever a Type 2 attribute in DimPerson changes since you will be inserting a new record in DimPerson with a new surrogate key. You would then need to add or update your bridge table with this new surrogate key for whichever categories are assigned.

However, if an attribute on DimCategory changes, you would just update that record and there would be no change in the surrogate key so the bridge table would be unchanged.

Now if you want to track things like when a Category assignment to a Person changes as a Type 2 attribute, then you would need to build the Effective Start and End Dates in your bridge table to be able to tell at what point in time a Person's Categories change.

Why new Surrogate Key  for SCD 2 .
Soft RI .
MVs , Join Indexes , Cubes  etc r nearly relic  except projected MVS ,Join Indexes etc with the advent of columnar DBs.
Kimbal design is Relic  for many  reasons

   With redundancy denormalization it can never be DW .

   It is not DW but  a physical semantic layer

   MDM

   bad use of surrogate Keys

   Columnar DBs at PHYSICAL SEMANTIC LAYER for performance i.e. denorm without Row Oriented cost

   business user Reporting Layers r now in Memory as well as huge capability .

   Basic aggregation on contexts if volume can be reduced will work wonder .

Still All will depend upon data Hierarchy , data changes , user requirement of history .
Regards


Applying data changes from one database to another I have an application running on Oracle 9i database and I was looking for something more practical to deploy data changes (not schema changes) from the development database to production.
Currently I work as follows:

Set all the parameters needed on development database (this might take months), the user validates everything and then I have to apply everything again (which might take some more months). I can't just import the development database into production, because all the data from production will be lost.
Is there something that can be done to avoid this "reworking"?

I tried Database Copy and Data Diff tools available on SQL Developer, but didn't work, since it has a lot of problems with dependencies when inserting new data on the tables..

The point is to keep the new/changed data made by the users in production while I work on the development database. Then import what I did without losing what the users did meanwhile.

Thanks in advance.

One way to do this is to ensure that all tables of properties, or metadata, have a date_created and date_last_modified date.  Then you can filter where the date created is in a time period and export the changes as inserts or updates.

What I do is harder. All changes to development properties are saved as a script of inserts/updates and I use a project/bug tracker to keep track of all the collection of changes.  I refresh development weekly so you see right away if you forgot something like a sequence or a grant.  A typical change could involve data inserts, package updates, grants, sequences and triggers so you have to keep it organized.

Edit: Luke asks how do you keep changes as scripts?
I do two things:

all properties and types are added by a package of procedures and functions.  This checks that the input is valid, updates any logging/audit tables and validates the changes against any business logic you may have in the database.  This is a lot of work to set up but allows you to have a consistent practice for adding new metadata.  Each time you use a procedure or function you can save your call as an sql statement.
I use Toad and this has the ability to filter a table for almost any criteria and export the results as Inserts.

Son, you can apply the data change on production, export the result and then issue a rollback. After the user validates the result, execute the same script with COMMIT instead of ROLLBACK.


"SSIS Evaluation Period Has Expired" on Dev Instance? I recently started receiving the following error on a dev server. At one point SQL Server 2016 CTP was installed here but we've upgraded since then. I've tried uninstalling / reinstalling SSIS as well as repairing it. Reboots occurred along the way. For the life of me I can't figure out why this error persists.

I've verified that the DTExec.exe is located where SQL Server expects it to be, using:

SELECT * FROM msdb.dbo.syssubsystems

...and that it's not referencing some other version of DTExec.exe previously installed on the server.

Is there anything else I can attempt to clear this alleged evaluation period?

  Executed as user: (Domain\SQLServiceAccount).
  Microsoft (R) SQL Server Execute Package Utility Version 13.0.1601.5 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved.
  Started: 11:00:01 PM
  Error: 2017-01-03 23:00:54.81 Code: 0xC0000033 Source: XXXX ETL XXXX
  Description: Integration Services evaluation period has expired.
  End Error
  Error: 2017-01-03 23:00:57.39 Code: 0xC0000033 Source: XXXX ETL XXXX
  Description: Integration Services evaluation period has expired.
  End Error
  Could not execute package because of error 0xC0000033.

The rest of the instance is fine. It is only SSIS that is producing the "evaluation period expired" error message.

Maybe someone have accidentally used an Evaluation Edition to set up SSIS? Perhaps, but you'd think an uninstall and subsequent reinstall (using a dev edition install kit) of the service would correct that problem if that were the case.

Run regedit and change the value of CommonFiles REG_DWORD under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\ConfigurationState

...from 1 to 3.

Run the Edition Upgrade and this will "upgrade" the components.

If that doesn't resolve the issue, then we need to make sure the client components are on the same service pack/build version as SQL Server.  If you have applied SP1 to SQL Server, check and make sure SP1 successfully installed on the client components as well.  Looking at your setup bootstrap logs will help determine that.

try using Edition Upgrade from SQL Server Installation Center


Delete rows from a table with inner join conditions I have a query that selects data from a table based on some inner joins:

select * from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

The output of this contains the rows from table1 that I want to delete. So I tried this:

delete from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

But that is not a valid query and returns an error. How can I delete all the rows from table1 which are the result of the above SELECT statement?

If your aim is only delete rows from table1, you can re-write your query as follow:

DELETE FROM table1  p
WHERE EXISTS(
    SELECT 'MYROW'
    FROM table2  e
    JOIN table3  d
        ON  d.col4 = 'IO'
        AND d.col5 = -1
        AND e.col3 = d.col6
    WHERE e.col1 = 'YU'
    AND e.username = p.username
    AND p.col2 = d.col3
);

You convert your INNER JOIN between main table (table1) and the others with using of WHERE EXISTS condition.

Note: string literals (like 'IO') need to be quoted with single quotes and not double quotes.

In MySQL this kind of deleting statement works:

delete p.* from table1 p
inner join table2 e on e.col1='YU' and e.username=p.username
inner join table3 d on p.col2=d.col3 and d.col4="IO" and d.col5=-1 and e.col3=d.col6

Pay attention to delete p.*.


Slow Select over View My simple SELECT statement on a view in Postgres 9.3.1 is pretty slow. The receipts table has 20 million entries and the other one has around 17k.

But I still think it should be faster than ~16 seconds?

Table receipts:

     Column      |            Type           | Modifiers | Storage     | Stats target  | Description
-----------------+---------------------------+-----------+-------------+---------------+--------------
 profitcenter    | character varying(5)      | not null  | extended    |               |
 receiptnumber   | bigint                    | not null  | plain       |               |
 receiptposition | character varying(6)      | not null  | extended    |               |
 date            | date                      | not null  | plain       |               |
 customerid      | integer                   |           | plain       |               |
 vkorg           | character varying(4)      |           | extended    |               |
 artikelnummer   | integer                   |           | plain       |               |
 price           | numeric(18,2)             |           | main        |               |
Indexes receiptnumber, date and customerid as B-Tree.

Table cc_add_entry2:

    Column     |          Type          | Modifiers | Storage     | Stats target  | Description
---------------+------------------------+-----------+-------------+---------------+--------------
 receiptnumber | bigint                 | not null  | plain       |               |
 customerid    | bigint                 |           | plain       |               |
 user          | character varying(255) |           | extended    |               |
 note          | text                   |           | extended    |               |
 date          | date                   |           | plain       |               |
Indexes receiptnumber, customerid as B-Tree.

The view is simple:

 SELECT s.receiptnumber,
    s.receiptposition,
    s.date,
    COALESCE(b.customerid, (s.customerid)::bigint) AS customerid,
    s.price
   FROM (dashboard.receipts s
     LEFT JOIN dashboard.cc_add_entry2 b ON ((s.receiptnumber = b.receiptnumber)));

EXPLAIN ANALYZE over a simple query:

explain analyze select * from test2 where customerid = 520001215;

 Hash Left Join  (cost=582.40..1086787.72 rows=100185 width=37) (actual time=2124.048..15937.744 rows=52 loops=1)
   Hash Cond: (s.receiptnumber = b.receiptnumber)
   Filter: (COALESCE(b.customerid, (s.customerid)::bigint) = 520001215)
   Rows Removed by Filter: 20036943
   ->  Seq Scan on receipts s  (cost=0.00..599969.96 rows=20036996 width=29) (actual time=0.904..7187.563 rows=20036995 loops=1)
   ->  Hash  (cost=360.51..360.51 rows=17751 width=16) (actual time=5.850..5.850 rows=17751 loops=1)
         Buckets: 2048  Batches: 1  Memory Usage: 694kB
         ->  Seq Scan on cc_add_entry2 b  (cost=0.00..360.51 rows=17751 width=16) (actual time=0.005..2.537 rows=17751 l
oops=1)
 Total runtime: 15937.962 ms

I am pretty sure the reason is that customerid in the view is the result of:

COALESCE(b.customerid, s.customerid::bigint)

After LEFT JOIN, cast & COALESCE, the predicate cannot be pushed down, so both tables have to be read in full, before the filter customerid = 520001215 can be applied. The fact that customerid can be NULL in both tables is not helping either. Can at least one be set to NOT NULL?

Extremely inefficient.

Run this query, and compare performance:

SELECT r.receiptnumber
    ,  r.receiptposition
    ,  r.date
    ,  c.customerid
    ,  r.price
FROM   dashboard.cc_add_entry2 c
JOIN   dashboard.receipts r USING (receiptnumber)
WHERE  c.customerid = 520001215;

UNION ALL
SELECT r.receiptnumber
    ,  r.receiptposition
    ,  r.date
    ,  r.customerid::bigint
    ,  r.price
FROM   dashboard.receipts r
LEFT   JOIN dashboard.cc_add_entry2 c USING (receiptnumber)
WHERE  r.customerid = 520001215
AND    c.customerid IS NULL;

Should be much faster. If you can't wrap it into a view, either use this query instead or write a SQL or plpgsql function that takes customerid as parameter.

Also these multicolumn indices would help some more:

CREATE INDEX foo1 ON dashboard.cc_add_entry2 (customerid, receiptnumber);
CREATE INDEX foo2 ON dashboard.cc_add_entry2 (receiptnumber)
WHERE customerid IS NULL;
CREATE INDEX foo3 ON dashboard.receipts (customerid, receiptnumber);

Do you have clustered and nonclustered indexes? Try clustered index on receipt number and date. And non clustered index on receipt number, receipt position, date, and price


Calculate row value based on previous and actual row values Hi everyone and thanks for your help.
I have the following situation: a table called statements that contains fields id(int), stmnt_date(date), debit(double), credit(double) and balance(double)

I want to calculate the balance following these rules:

The first row balance (chronologically) = debit - credit and for the rest of the rows

current row balance = chronologically previous row balance + current row debit - current row credit

As you can see on the picture above the rows are not arranged by date and that's why I used the word chronologically twice to emphasize on the importance of the stmnt_date value.

Thank you very much for your help.

Assuming that stmnt_date has a UNIQUE constraint, this would be fairly easy with window/analytic functions:

SELECT
    s.stmnt_date, s.debit, s.credit,
    SUM(s.debit - s.credit) OVER (ORDER BY s.stmnt_date
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
        AS balance
FROM
    statements AS s
ORDER BY
    stmnt_date ;

Unfortunately, MySQL does not (yet) have implemented analytic functions. You can solve the problem either with strict SQL, by self-joining the table (which should be rather inefficient although working 100%) or by using a specific  MySQL feature, variables (which would be quite efficient but you'd have to test it when upgrading mysql, to be sure that the results are still correct and not mangled by some optimization improvement):

SELECT
    s.stmnt_date, s.debit, s.credit,
    @b := @b + s.debit - s.credit AS balance
FROM
    (SELECT @b := 0.0) AS dummy
  CROSS JOIN
    statements AS s
ORDER BY
    stmnt_date ;

With your data, it will result in:

+------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014-05-15 |  3000 |      0 |    3000 |
| 2014-06-17 | 20000 |      0 |   23000 |
| 2014-07-16 |     0 |   3000 |   20000 |
| 2014-08-14 |     0 |   3000 |   17000 |
| 2015-02-01 |  3000 |      0 |   20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)

In, for example, MSSQL:

Use a with() statement to generate a CTE. This is essentially a temporary result set which will show the value of each row. You can use math in the with statement to create a column at the end, using math to show the total of the row is DEBIT-CREDIT.
In your with statement you'll need to assign row numbers to each row, use the OVER clause of WITH () to order by stmnt_date.

Then, recursively join the table onto itself, using a.ROWNUMBER= b.ROWNUMBER-1 or +1 which will allow you to refer the a.total+b.total= total of this row and the previous row.

I appreciate I'm not providing the code however this is the practical method to achieve this. I can supply code if requested :)


SQL Server 2008 user, group, or role already exists in the current database I am trying to migrate an old, problematic Plesk server to a new one but there are some problems with SQL Server 2008.

Plesk couldn't migrate one of the DBs. So, I backed up the DB and restored on the new server. Then I tried to restore 'User Mappings' for the existing user but I got "User, group, or role already exists in the current database" error. I googled for solutions and found a few but they didn't work.

I tried to run the following procedure but no luck.

sp_change_users_login 'Auto_Fix', 'UserName'

I also tried to delete it's scheme as a solution but couldn't do it because - according to SQL Server - it's referenced by an object.

I don't want to change that object's user/scheme because I am afraid of to broke something.

What should I do?

Note : Whenever you move your database from one server to another
  server there is a problem of orphan user usually occur. FileListOnly
  is the new term in sql server that has all the details of the created
  backup that who has access to it.

So there are sequence of task that you have to follow

  Firstly you have to restore the FileListOnly from the created backup to the destination or new server.
  Restore the backup.
  Use the sp_change_users_login  as per the need. For help regarding this procedure you can refer
  http://msdn.microsoft.com/en-us/library/ms174378.aspx.

I put a example here hope it will help:

> RESTORE FILELISTONLY FROM DISK = N'C:\YourDB.bak'
>
> RESTORE DATABASE YourDb FROM DISK = N'C:\YourDB.bak' WITH MOVE
> N'YourDB' TO N'D:\YourDB.mdf', MOVE N'YourDB_log' TO N'D:\YourDB.ldf',
> REPLACE
>
> exec YourDB.dbo.sp_change_users_login 'update_one', 'UserName','UserName'

USE database;
GO
EXEC sp_change_users_login 'Update_One', 'username1', 'username1';
GO


Allow Null Columns vs separate 1-to-many tables Perhaps similar questions have been asked, but this is re having null columns.

I am too inexperienced to determine what would be the sounder way to approach the design of this database. Is it better to have 5 or 6 additional columns on the one table that could reference IDs of other tables, or have 5 or 6 extra tables that provide a link if there is a need for them.

My inclination is that fewer null entries are better, but then joining the tables when accessing the database could have a higher cost?

Allow null is a better approach. It gives us significant performance benefits with significant effort shortage to accomplish the task.
A separate table will cost more JOIN. To query such a data is/can be certainly slower, than to query single table


Composite indexes: Most selective column first? I’ve been reading about composite indexes and I’m slightly confused about ordering. This documentation (little less than half way down) says

  In general, you should put the column expected to be used most often first in the index.

However, shortly after it says

  create a composite index putting the most selective column first; that is, the column with the most values.

Oracle also says it here in other words

  If all keys are used in WHERE clauses equally often, then ordering these keys from most selective to least selective in the CREATE INDEX statement best improves query performance.

However, I have found a SO answer that says differently. It says

  Arrange the columns with the least selective column first and the most selective column last. In the case of a tie lead with the column which is more likely to be used on its own.

The first documentation I referenced says that you should first go by the most often used whereas the SO answer says that should only be for tie breaking. Then they also differ on the ordering.

This documentation also talks about skip scanning and says

  Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

Another article says

  The prefix column should be the most discriminating and the most widely used in queries

which I believe most discriminating would mean most distinctive.

All of this research still leads me to the same question; should The most selective column be first or last? Should the first column be the most used and only the most selective on a tie-break?

These articles seem to be contradicting each other, but they do offer some examples. From what I have gathered, it seems to be more efficient for the least selective column to be the first in the ordering if you are anticipating Index Skip Scans. But I'm not really sure if that is correct.

From AskTom

  (in 9i, there is a new "index skip scan" -- search for that there to read about that. It makes the index (a,b) OR (b,a) useful in both of the above cases sometimes!)

  So, the order of columns in your index depends on HOW YOUR QUERIES are written. You want to be able to use the index for as many queries as you can (so as to cut down on the over all number of indexes you have) -- that will drive the order of the columns. Nothing else (selectivity of a or b does not count at all).

One of the arguments for arranging columns in the composite index in order from the least discriminating(less distinct values) to the most discriminating(more distinct values) is for index key compression.

SQL> create table t as select * from all_objects;

Table created.

SQL> create index t_idx_1 on t(owner,object_type,object_name);

Index created.

SQL> create index t_idx_2 on t(object_name,object_type,owner);

Index created.

SQL> select count(distinct owner), count(distinct object_type), count(distinct object_name ), count(*)  from t;

COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_TYPE) COUNT(DISTINCTOBJECT_NAME)      COUNT(*)
-------------------- -------------------------- --------------------------      ----------
                 30                         45                       52205      89807

SQL> analyze index t_idx_1 validate structure;

Index analyzed.

SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    5085584     90          2           28

SQL> analyze index t_idx_2 validate structure;

Index analyzed.

SQL> select btree_space, pct_used, opt_cmpr_count, opt_cmpr_pctsave  from index_stats;

BTREE_SPACE   PCT_USED OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------- ---------- -------------- ----------------
    5085584     90          1           14

According to the index statistics, the first index is more compressible.

Another is how the index is used in your queries.
If your queries mostly use col1,

For example, if you have queries like-
select * from t where col1 = :a and col2 = :b;
select * from t where col1 = :a;

-then index(col1,col2) would perform better.

If your queries mostly use col2,
select * from t where col1 = :a and col2 = :b;
select * from t where col2 = :b;

-then index(col2,col1) would perform better.
If all of your queries always specify both columns then it doesn't matter which column come first in the composite index.

In conclusion,
the key considerations in column ordering of composite index are index key compression and how you are going to use this index in your queries.

References:
Column order in Index
It’s Less Efficient To Have Low Cardinality Leading Columns In An Index (Right) ?
Index Skip Scan – Does Index Column Order Matter Any More ? (Warning Sign)

Different cases for different situations. Know your goal; then create your indexes and run explain plans for each and you will have your best answer for your situation.


Which version of SQL Server 2016 supports R Microsoft buying REvolution results (amongst other things) in the integration of R in SQL Server 2016.

Looking through the features supported by the SQL Server 2016 I cannot find R-Support. Does anyone know to what feature R-Integration belongs to or what version(s) integrate this feature?

SQL Server 2016 Community Technology Preview 2.2  is the latest release of SQL Server 2016. This version does not have R support.

Later release of 2016 (probably 3.0) will have support for R language.

   Found this slideshare presentation (slide - 130) that says R support will be in CTP 3.0.

EDIT: Announced and demoed in PASS Summit 2015 - As of SQL Server 2016 CTP 3.0, SQL server supports Advanced Analytics (RRE integration):

  With this release, we are very excited to announce the public availability SQL Server R Services in SQL Server 2016, an Advanced Analytics capability which supports enterprise-scale data science, significantly reducing the friction for adopting machine learning in your business. SQL Server R Services is all about helping customers embrace the highly popular open source R language in their business. R is the most popular programming language for Advanced Analytics. You can use it to analyze data, uncover patterns and trends and build predictive models. It offers an incredibly rich set of packages and a vibrant and fast-growing developer community. At the same time, embracing R in an enterprise setting presents certain challenges, especially as the volume of data rises and with the switch from modeling to production environments. Microsoft SQL Server R Services with in-database analytics helps customers embrace this technology by supporting several scenarios.

Also, Henk blogs about Introducing Microsoft SQL Server 2016 R Services

You should not focus on building too many tightly coupled solution, you should be focussing more on the aspects of building an ETL pipeline which uses, SQL Server as a source and later allows you to plug custom R scripts for any statistical use cases. Here you can think of using SSIS, or even KNIME for open source offerings.


SQL Server : explicitly create an index on a primary key and unique fields UPDATED:

I'll make the question clearer as the first answer isn't quite what I was looking for.

How can I execute this create table statement and ensure that the two automatically created indexes are stored on MY_INDEX_FILEGROUP as I am assuming that these automatically created indexes will be stored on MY_DATA_FILEGROUP that the table data will be stored in.

CREATE TABLE Product.Product(
    ProductId tinyint identity(1,1) constraint PK_Product_Product_ProductId primary key,
    ProductName varchar(50) unique not null,
    SupportEnded date default null null
) ON MY_DATA_FILEGROUP
GO

is it possible to seperate the automatically generated indexes into a different filegroup, from the data?

And as a seperate but related question is it possible to enforce a constraint like PRIMARY KEY or UNIQUE whilst preventing the automatic creation of the associated index?

How can I execute this create table statement and ensure that the two
  automatically created indexes are stored on MY_INDEX_FILEGROUP as I am
  assuming that these automatically created indexes will be stored on
  MY_DATA_FILEGROUP that the table data will be stored in.

The table will exist on whichever filegroup contains the clustered index, because the clustered index is the data.

By default, a primary key index is clustered, so if you specify a different filegroup for that, the table ends up there.

  is it possible to seperate the automatically generated indexes into a different filegroup, from the data?

Yes, but don't. Bears will eat you.

  And as a seperate but related question is it possible to enforce a constraint like PRIMARY KEY or UNIQUE whilst preventing the automatic creation of the associated index?

No, this is how SQL Server enforces the constraint behind the scenes. Technically, SQL could use any mechanism to enforce constraints; it just makes the most sense to use an index to do it.

In this above create table script,why cant you make the required changes? And also they are not created automatically,u need execute the script in order to create those indexes.

Also when you make a column as primary key by default clustered index will be created.and its a good practice to have primary key as clustered index.

see the script below:

 CREATE TABLE Product.Product(
        ProductId tinyint identity(1,1) PRIMARY KEY NOT NULL,
        ProductName varchar(50) null,
        SupportEnded date default null
    ) ON MyFileGroup_Data
    go

    CREATE unique nonclustered index noninx_ProductName  on Product(ProductName ) on filegroup_name1


I need to audit all actions for a user on all databases - not sure best way I have a user on a SQL Server that is used for executing SSRS queries. This user was made a sysadmin before I started at this job. I want to remove the sysadmin permission, but not break any reports. I do not know what DDL/DML the user is actually performing.

With that said, I want to run an audit, which I have done before, but since the auditing is so fine grained, I can't wrap my head around what all I should audit to catch the user actions on all databases.

Can anyone shed some light?

Thanks in advance!

Colleen Morrow's got some great resources regarding SQL Server Audit, including deployable code via PowerShell.  Her articles are well worth a read and I've deployed her solutions in my environment to great fanfare:

http://colleenmorrow.com/tag/auditing/

You can check out the Recommended Report and Alert Designs for LOGbinder for SQL Server from Windows security expert Randy Smith. He also published some articles on SQL Server Audit Logging as well as a free tool, the LOGbinder SQL Audit Policy Wizard, to easily set up SQL Server auditing.


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

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

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

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

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

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

Use an AGGREGATE function like MIN or MAX.

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

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

^^Above information is not what was requested.

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

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

select id,date from table2 group by id,date


Achieving "sequence number", rather than dateTime, for updates; postgres specifically So a postgres database (specifically, an AmazonRDS instance fwiw).

It's easy enough to arrange things to get the last updated (or created) time for any record.

That information would be a datetime.

What I want to achieve: imagine an integer, sequenceNumber, which is atomic and global to the database.

Simply, every table would have a sequenceNumber field.

Any time any record (ie, in any table) is updated (or created), it gets the next sequenceNumber.

You know how you have programmer friends who know nothing about databases. Wave. My idiot solution would be, in the entire codebase, every time you create or modify a record, be sure to remember to update sequenceNumber on it. (Of course, just have one global system that gives the next atomic sequenceNumber.)

What's the sound solution for this?

Indeed, is it completely available already in some way I don't know, or?

As a_horse_with_no_name originally suggested in question comments:

--assuming we have tables: a, b, ...
CREATE TABLE a
(
  a_id integer NOT NULL PRIMARY KEY,
  a_name text
) ;

CREATE TABLE b
(
  b_id integer NOT NULL PRIMARY KEY,
  b_name text
) ;

First we create a global sequence and add a column in each table:

-- create one global sequence to be used by all tables
CREATE SEQUENCE global_sequence_number ;

-- add a column in each table, that uses the same sequence
ALTER TABLE a
  ADD COLUMN global_sequence_id INT NOT NULL
    DEFAULT nextval('global_sequence_number'::regclass) ;

ALTER TABLE b
  ADD COLUMN global_sequence_id INT NOT NULL
    DEFAULT nextval('global_sequence_number'::regclass) ;

Create trigger function (one) and UPDATE triggers (one in each table):

-- create a trigger function to update the column
CREATE OR REPLACE FUNCTION update_global_sequence_id()
RETURNS TRIGGER AS
$$
BEGIN
   NEW.global_sequence_id = nextval('global_sequence_number'::regclass);
   RETURN NEW;
END;
$$ language 'plpgsql';

-- add a trigger, in each table
CREATE TRIGGER update_a_global_sequence_id
  BEFORE UPDATE ON a FOR EACH ROW
    EXECUTE PROCEDURE update_global_sequence_id();

CREATE TRIGGER update_b_global_sequence_id
  BEFORE UPDATE ON b FOR EACH ROW
    EXECUTE PROCEDURE  update_global_sequence_id();

And profit:

 -- example of use
INSERT INTO a (a_id, a_name)
VALUES
  (1, 'a'),  (2, 'b') ;

INSERT INTO b (b_id, b_name)
VALUES
  (3, 'c'),  (4, 'd'),  (5, 'e') ;

SELECT *
FROM a FULL JOIN b ON FALSE
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
  1   a  1
  2   b  2
             3   c  3
             4   d  4
             5   e  5

And after some updates, to check that the triggers work:

-- lets do some updates:
UPDATE a SET a_name = 'aa' WHERE a_id = 1 ;
UPDATE b SET b_name = 'cc' WHERE b_id = 3 ;

-- and see what happened
SELECT *
FROM a FULL JOIN b ON FALSE
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
  2   b  2
             4   d  4
             5   e  5
  1  aa  6                   -- sequence updated to 6
             3  cc  7        -- sequence updated to 7

Just use UUIDs if you want something globally unique to the database.

gen_random_uuid() in pgcrypto can generate them
To generate ITU-T Rec. X.667, ISO/IEC 9834-8:2005, and RFC 4122 UUIDs use uuid-ossp

The chances of a collision are roughly 'absolutely never going to happen', see this question on the serious chances of collision. Per the docs

  Some systems refer to this data type as globally unique identifier, or GUID, instead.

Sequences have a lot of downsides when used everywhere in the database

they're not gapless
make sharding difficult
make cross-db merging difficult too (think about merging two installs of same database)


Restoring from pg_dump with foreign key constraints In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored.  An example:

ERROR:  insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"
DETAIL:  Key (channel_id)=(1) is not present in table "channels".

Interestingly enough, I've noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.

caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem...

version: PostgreSQL 9.4.4.

How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created?

You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.

But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.

See https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.

Try creating new database with empty table with in it and restore the db. The problem probably can be with the existing relation between table.


Distinct value across multiple columns I have a table like this,

CREATE TABLE foo (
    id integer,
  col1 integer,
  col2 integer,
  col3 integer,
  col4 integer,
  col5 integer
);
INSERT INTO foo (id, col1, col2, col3, col4, col5)
VALUES
    (322, NULL, 3, 1, 2, 2),
    (323, 1, 3, 1, 2, 2),
    (324, 1, 1, 1, 2, 2),
    (325, 3, 3, 3, 3, NULL);

I need to write a query such that I need to get

ID, concatinated string of unique value from the columns without null

My desired output should be

ID, UniqVal
322, "3,1,2"
323, "1,3,2"
324, "1,2"
325, "3"

Note: The version is MySQL 5.5

Select each column along with the ID where the column IS NOT NULL. Use UNION to combine the result sets. This will already eliminate duplicates. Then GROUP BY the ID and use the group_concat() aggregation function to build your list.

SELECT id,
       group_concat(col
                    SEPARATOR ',') uniqval
       FROM (SELECT id,
                    col1 col
                    FROM elbat
                    WHERE col1 IS NOT NULL
             UNION
             SELECT id,
                    col2 col
                    FROM elbat
                    WHERE col2 IS NOT NULL
             UNION
             SELECT id,
                    col3 col
                    FROM elbat
                    WHERE col3 IS NOT NULL
             UNION
             SELECT id,
                    col4 col
                    FROM elbat
                    WHERE col4 IS NOT NULL
             UNION
             SELECT id,
                    col5 col
                    FROM elbat
                    WHERE col5 IS NOT NULL) x
       GROUP BY id;

SQL Fiddle

Just for comparison if MySQL ever implements the SQL Array type, you can do it like this (valid in PostgreSQL),

SELECT id,
  ARRAY(
    SELECT DISTINCT x
    FROM unnest(ARRAY[col1,col2,col3,col4,col5]) AS t(x)
    WHERE x IS NOT NULL
  )
FROM foo;

Or alternatively,

SELECT id, array_agg(DISTINCT x) FROM foo
CROSS JOIN LATERAL unnest(ARRAY[col1,col2,col3,col4,col5]) AS t(x)
WHERE x IS NOT NULL
GROUP BY id;


What Columns to create Indexes on? I have a table , which contains upto 20 columns. The data is selected, inserted, and deleted by 3 columns: Username, StartDate, and EndDate.

These 3 columns are NOT unique.

The table is a standalone table - no joins to other tables.

Do I create a clustered index on these 3 columns? Or do I create a UniqueID that autoincrements as a PK, and then create a non-clustered index on those 3 columns? Or is a Primary Key required at all?

SELECT StartDate, EndDate, Username, Value1, Value2, Value3, Value4, Value5, Value6
FROM  myTbl
WHERE Username = @Username
AND StartDate = @StartDate
AND EndDate = @EndDate;

DELETE FROM  myTbl
WHERE Username = @Username
AND StartDate = @StartDate
AND EndDate = @EndDate

From the limited information given, a suitable non-unique clustered index is:

CREATE CLUSTERED INDEX CX_dbo_myTbl_StartDate_EndDate_Username
ON dbo.myTbl (StartDate, EndDate, UserName);

That said, there are all sorts of other considerations that might mean a different choice would be optimal for the workload as a whole. If you improve your question with additional detail, it will be possible to give a better answer.

Also, in general, a PRIMARY KEY (clustered or not) is highly desirable. It is a fundamental principle of relational design that each row (tuple) of a table should be capable of being uniquely identified.

One clustered index is not enough, because it will do ClusteredIndexScan(logical operator in Exeution Plan)

So, if your version of SQL Server is above 2008 then this may help:

CREATE INDEX Idx_Name ON myTbl(UserName, StartDate, EndDate)
  INCLUDING(Value1, Value2, Value3, Value4, Value5, Value6)

this will allow Query Optimizer to use IndexSeek

If your version is 2005 or lower, yu can create covered index with all columns (but only if their total length is less than 900 bytes) like this:

CREATE INDEX Idx_Name ON
myTbl(UserName, StartDate, EndDate, Value1, Value2, Value3, Value4, Value5, Value6)


Installing SQL server 2008R2/2012 on windows server 2012 box we are trying to install SQL2k8R2 and SQL server 2012(Standard edition) on the windows server 2012 box (Standard 64 Bit).

For SQL 2k8r2, i used slipstream method so as to bring set up to atleast SP1 for support as compatibility on windows 2k12.

But the problem comes when we are running the set up, i.e. SQL2k8R2 and even SQL2012 , we are getting the below error for missing .Net 3.5

If we try to enable 3.5 manually from roles and features, we get the below erro

I've read on many articles, that we need to have the set up for windows to find the required source files for 3.5 for this to proceed. But before going to the team who actually set up the box, is there something we may be missing here and any action or alternative that can be given a try?

Thanks.

I have used option 2 from this article many times to fix this issue.

Basically insert the windows install disk, enable the .net 3 feature but select the option to 'specify an alternate path' at the end of the wizard. The different path should be D:\sources\sxs (if the disc is in your D drive)

Because i am not allowed to comment i write this here.

To install net framework 3.5 you need to be connected to internet. Im assuming you are ?

For more info check this link:
https://support.microsoft.com/en-us/kb/2734782


deleted the /var/log/mysql folder Due to a mistake i deleted the log folder for mysql. Is there a way to generate the folder and the error logs?
I'm using MariaDB 10.3 on Debian 9 Stretch

mysqld keeps the error log file open. If you delete the file the file handler is still valid and the file is available for the process that opened it (=mysqld).

# lsof  | grep mysqld
...
mysqld    20163 20192           mysql    2w      REG              253,2       4434         15 /var/log/mysql/error.log

# rm /var/log/mysql/error.log
# lsof  | grep mysqld | grep error
...
mysqld    20163 20310           mysql    2w      REG              253,2       4549         15 /var/log/mysql/error.log (deleted)

You can get the content of the error log from the /proc filesystem.

# ls -la /proc/20163/fd/ | grep error
l-wx------ 1 mysql mysql 64 May  2 15:49 1 -> /var/log/mysql/error.log (deleted)
l-wx------ 1 mysql mysql 64 May  2 15:49 2 -> /var/log/mysql/error.log (deleted)

# cat /proc/20163/fd/1
2020-05-02T15:49:46.163721Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 65535)
2020-05-02T15:49:46.355461Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-0ubuntu0.18.04.1-log) starting as process 20163 ...
2020-05-02T15:49:46.371978Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-05-02T15:49:46.372006Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-02T15:49:46.372010Z 0 [Note] InnoDB: Uses event mutexes
2020-05-02T15:49:46.372013Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-05-02T15:49:46.372016Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-02T15:49:46.372023Z 0 [Note] InnoDB: Using Linux native AIO
2020-05-02T15:49:46.372272Z 0 [Note] InnoDB: Number of pools: 1
2020-05-02T15:49:46.372380Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-05-02T15:49:46.376119Z 0 [Note] InnoDB: Initializing buffer pool, total size = 384M, instances = 1, chunk size = 128M
2020-05-02T15:49:46.398759Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-05-02T15:49:46.404255Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-05-02T15:49:46.421829Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-05-02T15:49:48.448175Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-05-02T15:49:48.448255Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-05-02T15:49:48.938748Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-05-02T15:49:48.939384Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-05-02T15:49:48.939393Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-05-02T15:49:48.939823Z 0 [Note] InnoDB: Waiting for purge to start
2020-05-02T15:49:48.990402Z 0 [Note] InnoDB: 5.7.29 started; log sequence number 3292611
2020-05-02T15:49:48.990563Z 0 [Note] InnoDB: Loading buffer pool(s) from /database/mysql/ib_buffer_pool
2020-05-02T15:49:48.990773Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-05-02T15:49:49.476557Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200502 15:49:49
2020-05-02T15:49:49.569129Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2020-05-02T15:49:49.569147Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2020-05-02T15:49:49.596450Z 0 [Warning] CA certificate ca.pem is self signed.
2020-05-02T15:49:49.596491Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-05-02T15:49:49.616398Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2020-05-02T15:49:49.616425Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2020-05-02T15:49:49.616456Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2020-05-02T15:49:49.856411Z 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=produ-data-db-0000-05f01-use2-relay-bin' to avoid this problem.
2020-05-02T15:49:49.946618Z 1 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2020-05-02T15:49:49.966480Z 2 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2020-05-02T15:49:49.966566Z 2 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000116' at position 2493, relay log './produ-data-db-0000-05f01-use2-relay-bin.000002' position: 1811
2020-05-02T15:49:49.971887Z 1 [Note] Slave I/O thread for channel '': connected to master 'replication@produ-data-db-0000-0c5f4-use2:3306',replication started in log 'mysql-bin.000116' at position 2493
2020-05-02T15:49:51.066602Z 0 [Note] Event Scheduler: Loaded 0 events
2020-05-02T15:49:51.066831Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.29-0ubuntu0.18.04.1-log'  socket: '/var/lib/mysql-files/mysql.sock'  port: 3306  (Ubuntu)
2020-05-02T15:50:05.681566Z 5 [Warning] IP address '10.0.182.254' could not be resolved: Name or service not known

Same way you can get other files (slow log, general log). However if the files were closed and were deleted - you're pretty much out of luck (not saying it's impossible to recover, but hard).

I'm not sure what you mean by "generate", but you can just create it. Make sure it is owned by the mysql user. When you restart mysqld, the error log will get re-created.


Mysql Multiple Joins Slow Performance I am working on a search app that queries a large amount of info (100M+ rows) with multiple filters that can be used and I am having trouble getting it to perform when multiple filters are combined. When I have three or more joins the query takes hours to finish. Is there any way to make multiple joins a lot faster or am I doing something wrong? The following SQL fiddle explains the predicament:

http://sqlfiddle.com/#!9/db1eab8/1

For reference the table structure and slow query is also below:

-- ----------------------------
-- MariaDB 10.1 Table structures (simplified names and columns for this case)
-- ----------------------------
-- Contains the main information rows - this normally has 100M+ rows
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
-- Other columns would normally be here but were removed to simplify this
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains the text content for a - This has the same amount of rows as `a`
DROP TABLE IF EXISTS `a_content`;
CREATE TABLE `a_content` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(2000) NOT NULL,
  `description` varchar(2000) NOT NULL,
  PRIMARY KEY (`id_a`),
  FULLTEXT `f` (`title`,`description`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains whether an `a` ID is hidden for a user ID - This normally has 30M+ rows
DROP TABLE IF EXISTS `a_hidden`;
CREATE TABLE `a_hidden` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  PRIMARY KEY (`id_a`,`user`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- Contains an `a`'s country ID's - This normally has 300M+ rows
DROP TABLE IF EXISTS `a_country`;
CREATE TABLE `a_country` (
  `id_a` int(11) NOT NULL AUTO_INCREMENT,
  `country` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_a`,`country`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Sample Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1');
INSERT INTO `a` VALUES ('2');
INSERT INTO `a` VALUES ('3');
INSERT INTO `a` VALUES ('4');
INSERT INTO `a` VALUES ('5');
INSERT INTO `a` VALUES ('6');
INSERT INTO `a` VALUES ('7');
INSERT INTO `a` VALUES ('8');
INSERT INTO `a` VALUES ('9');
INSERT INTO `a` VALUES ('10');

INSERT INTO `a_content` VALUES ('1','aa sadd','aa');
INSERT INTO `a_content` VALUES ('2','aasdadd sads','aa');
INSERT INTO `a_content` VALUES ('3','aa daa','aaf df');
INSERT INTO `a_content` VALUES ('4','aa sad','aadf ');
INSERT INTO `a_content` VALUES ('5','aa v','aaxcv');
INSERT INTO `a_content` VALUES ('6','aa c','aavv');
INSERT INTO `a_content` VALUES ('7','aa df','aaaa');
INSERT INTO `a_content` VALUES ('8','aa df','aa s');
INSERT INTO `a_content` VALUES ('9','aa vvv','a a');
INSERT INTO `a_content` VALUES ('10','ss','aaaa');

INSERT INTO `a_hidden` VALUES ('1','1');
INSERT INTO `a_hidden` VALUES ('3','1');
INSERT INTO `a_hidden` VALUES ('5','1');
INSERT INTO `a_hidden` VALUES ('7','1');
INSERT INTO `a_hidden` VALUES ('9','1');

INSERT INTO `a_country` VALUES ('1',1);
INSERT INTO `a_country` VALUES ('1',2);
INSERT INTO `a_country` VALUES ('2',1);
INSERT INTO `a_country` VALUES ('3',2);
INSERT INTO `a_country` VALUES ('4',4);
INSERT INTO `a_country` VALUES ('5',112);
INSERT INTO `a_country` VALUES ('6',33);
INSERT INTO `a_country` VALUES ('7',44);
INSERT INTO `a_country` VALUES ('8',66);
INSERT INTO `a_country` VALUES ('9',88);
INSERT INTO `a_country` VALUES ('10',99);

Query:

# Extremely slow and takes hours even though it's using indexes all over. If I remove one join it's instantly fast though
SELECT a.id
FROM  `a`
LEFT JOIN a_hidden ON a_hidden.id_a = a.id && a_hidden.user=1
LEFT JOIN a_content ON a_content.id_a = a.id
INNER JOIN a_country ON a_country.id_a = a.id && a_country.country IN (1,2,3)
WHERE a_hidden.id_a IS NULL && MATCH (a_content.title, a_content.description) AGAINST ('search' IN BOOLEAN MODE)
ORDER BY a.id DESC
LIMIT 0 , 50

The Explain of the above query and table structure:

Well.. MySQL doesn't work well as a search engine. I went with a search engine and selects with multiple filters are instant.

Try putting && a_hidden.user=1  and && a_country.country IN (1,2,3) in WHERE clause. Don't add conditions in join keep conditions in WHERE Clause.


Get rows in a range of time I have a table with two columns that stores TIME type values. This is the CREATE statement:

CREATE TABLE `sdriving_turno` (
    `idturno` bigint(20) NOT NULL AUTO_INCREMENT,
    `idempresa` int(10) unsigned NOT NULL,
    `tipo` varchar(30) NOT NULL,
    `hora_inicio` time DEFAULT NULL,
    `hora_fin` time DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`idturno` , `idempresa`),
    KEY `fk_sdriving_turno_sdriving_empresa1_idx` (`idempresa`),
    CONSTRAINT `sdriving_turno_ibfk_1` FOREIGN KEY (`idempresa`)
        REFERENCES `sdriving_empresa` (`idempresa`)
        ON DELETE CASCADE ON UPDATE CASCADE
)  ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1

I need to get the records in a range of TIME, lets say I need to get all the records BETWEEN 14:00 and 17:00 and for that I made this query (it has some JOINS but it's not relevant on this topic):

SELECT
    s.idregistros AS s__idregistros,
    s.maquinaemisorid AS s__maquinaemisorid,
    s.idoperador AS s__idoperador,
    s.idturno AS s__idturno,
    s.created_at AS s__created_at,
    s.updated_at AS s__updated_at,
    s2.idturno AS s2__idturno,
    s2.idempresa AS s2__idempresa,
    s2.tipo AS s2__tipo,
    s2.hora_inicio AS s2__hora_inicio,
    s2.hora_fin AS s2__hora_fin,
    s2.created_at AS s2__created_at,
    s2.updated_at AS s2__updated_at,
    s3.idmaquinaemisor AS s3__idmaquinaemisor,
    s3.idmaquina AS s3__idmaquina,
    s3.idemisor AS s3__idemisor,
    s3.created_at AS s3__created_at,
    s3.updated_at AS s3__updated_at,
    s4.idmaquina AS s4__idmaquina,
    s4.idempresa AS s4__idempresa,
    s4.patente AS s4__patente,
    s4.created_at AS s4__created_at,
    s4.updated_at AS s4__updated_at,
    s5.idoperador AS s5__idoperador,
    s5.idempresa AS s5__idempresa,
    s5.nombre AS s5__nombre,
    s5.rut AS s5__rut,
    s5.created_at AS s5__created_at,
    s5.updated_at AS s5__updated_at,
    s6.iddetalle_emisores AS s6__iddetalle_emisores,
    s6.idregistros AS s6__idregistros,
    s6.fecha_registro AS s6__fecha_registro,
    s6.dertalle_mensaje AS s6__dertalle_mensaje,
    s6.estado AS s6__estado,
    s6.revisado AS s6__revisado,
    s6.modo AS s6__modo,
    s6.remitente AS s6__remitente,
    s6.created_at AS s6__created_at,
    s6.updated_at AS s6__updated_at
FROM
    sdriving_registros_emisores s
        LEFT JOIN
    sdriving_turno s2 ON s.idturno = s2.idturno
        LEFT JOIN
    sdriving_maquina_emisor s3 ON s.maquinaemisorid = s3.idmaquinaemisor
        LEFT JOIN
    sdriving_maquina s4 ON s3.idmaquina = s4.idmaquina
        LEFT JOIN
    sdriving_operador s5 ON s.idoperador = s5.idoperador
        LEFT JOIN
    sdriving_detalle_emisores s6 ON s.idregistros = s6.idregistros
WHERE
    (s5.idempresa = '12'
        AND (s2.hora_inicio < '14:47'
        AND '14:47' > s2.hora_fin))

But when record has hora_inicio = 00:00:00 and hora_fin = 17:00:00 I get no results, why? What's wrong in my query?

Note 14:47 is the server data build through PHP if there is any way to get the records according to the current time in server just using SQL way let me know.

SELECT *
FROM `sdriving_turno`
WHERE hora_inicio < NOW() AND hora_fin < '17:00:00'

you could also do something like

SELECT *
FROM `sdriving_turno`
WHERE hora_fin BETWEEN NOW() AND '18:00:00'

Yes there is a way.
You can use now() function in sql to get the current time of the server while fetching records like the folowing query example..

select emp_id,now() from employee_tbl ;

it will return all the records of emp_id column along with the current server time.


Where is the cache for the MySQL caching_sha2_password auth plugin stored? Where is the cache for the new default authentication plugin caching_sha2_password in MySQL 8 stored? I want to know for future reference how to clear it in case I ever run into any problems using it.

'when there exists a cached copy of the password hash in memory' (ref).

So its all in memory.

FLUSH PRIVILEGES can clear it

Open MySQL from System Preferences > Initialize Database >
Type your new password.
Choose 'Use legacy password'
Start the Server again.
Now connect the MySQL Workbench

Please read full Answer here