MySQLSyntaxErrorException on ALTER TABLE ... CONVERT TO ... in MySQL Connector/J Running Ubuntu 14.04 with mysql-connector-java-5.1.28 installed.

Let's create a table:

CREATE TABLE mytable (value1 VARCHAR(32000), value2 TEXT, value3 VARCHAR(1000), value4 VARCHAR(32000)) CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Executing the following ALTER TABLE statement via Statement.execute() fails saying that "Column length too big..*".

According to the documentation ALTER TABLE ... CONVERT TO ... should however perform automatic type conversion as necessary to ensure that the new column is long enough to store as many characters as the original column.
The statement indeed runs in the native MySQL client performing the necessary conversions.

Am I stumbling into a bug in JDBC or is this behavior expected?

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Column length too big for column 'value1' (max = 21845); use BLOB or TEXT instead
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:949)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:795)
    at Main.main(Main.java:32)

In my previous response I have not taken into account CONVERT TO behavior, so I have tried your test, first using the mysql native client and all works fine, column are automatically converted, and then using a jdbc connector and actually I got the same error... but after some test and some research I have found the solution.

You have to add in your jdbc connection url the parameter jdbcCompliantTruncation and set it to false

jdbc:mysql://localhost/test?jdbcCompliantTruncation=false

  Should the driver throw java.sql.DataTruncation exceptions when data
  is truncated as is required by the JDBC specification when connected
  to a server that supports warnings (MySQL 4.1.0 and newer)? This
  property has no effect if the server sql-mode includes
  STRICT_TRANS_TABLES.

  Default: true

Source: 5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J

Running my java code using the above connection url the table mytable are correctly converted (I have tried only with an empty table):

mysql> desc mytable;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| value1 | mediumtext    | YES  |     | NULL    |       |
| value2 | mediumtext    | YES  |     | NULL    |       |
| value3 | varchar(1000) | YES  |     | NULL    |       |
| value4 | mediumtext    | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

So, if the server runs without STRICT_TRANS_TABLES in sql_mode, ex:

mysql> show variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

the jdbcCompliantTruncation=false cause the alignment of the behavior between server and jdbc client.


Running a Job from a Stored Procedure in another server? How can I run a job from another server using a stored procedure?

Let's say

server 1 : db1
server 2 : db2
username : testssis
password : testssispass
sqljob found in db2: job1

Here is the part im lost with how can I make this work?

xp_cmdshell + osql command -U: -P: + sp_startjob

With a linked server - much more reliable and secure than xp_cmdshell:

EXEC Server2.msdb.dbo.sp_start_job @job_name = N'Job2';


Which to store: miles or kilometers? My app is launching to a US audience first, but I want to internationalize the database schema for future expansion. Obviously I will convert between the two units as necessary regardless, but are there any benefits to storing one or the other in the database? My initial thought is that it might be helpful to store kilometers since miles are used by less countries worldwide, so long-term, I will be doing less conversions overall. But in the short-term, while the app is still US-only, I will be converting units every time I access the database.

I'd recommend storing everything in Planck Lengths.  That way you get the most accuracy possible.

The Planck length, is the unit of length approximately 1.6 * 10^-35 metres. It is in the system of units known as Planck units. The Planck length is deemed "natural" because it can be defined from three fundamental physical constants: the speed of light, Planck's constant, and the gravitational constant. Max Planck was the first to propose the Planck length, a base unit in a system of measurement he called "natural units". Although quantum mechanics and general relativity were unknown when Planck proposed his natural units, it later became clear that at a distance equal to the Planck length, gravity begins to display quantum effects, whose understanding would require a theory of quantum gravity.

Meter lengths are an easy conversion of "x" meters divided by 1.6e+35.

Of course, you going to need a fairly large data type to store your measurements.

All kidding aside, why not store the unit-of-measurement with the actual measurement (in a separate column of course, probably as a foreign key).  This way when you are displaying the measurement you do not need to convert it at all.  Simply display the measurement, along with the unit of measure.  The only time you'll need to convert the measurement is if you want to compare it to other measurements in a different unit.


deadlock detected during oracle expdp I have an Oracle 11g and I wanted to export all my shcema objects,

with this command:

expdp as '/ as sysdba' directory=DIR dumpfile=DWH%U.dmp logfile=DWH.log filesize=40000m parallel=32 schemas=DWH

This is the start of the output I'v got:

Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1840. GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 14 with process name "DW0D" prematurely terminated
ORA-31671: Worker process DW0D had an unhandled exception.
ORA-39078: unable to dequeue message for agent KUPC$A_1_100742920000000 from queue "KUPC$C_1_20190915100610"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 610
ORA-04020: deadlock detected while trying to lock object SYS.KUPC$C_1_20190915100610
ORA-06512: at "SYS.KUPW$WORKER", line 1887
ORA-06512: at line 2
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/AUDIT_OBJ
Processing object type SCHEMA_EXPORT/TABLE/RLS_POLICY/RLS_POLICY
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/VIEW/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/SYNONYM/RLS_POLICY/RLS_POLICY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
. . exported "DWH"."T3"                            9.119 GB 21698829

The exception occured but the export hasn't failed.. and I can see that the tables start to be exported.

What is this exception mean? deadlock on object of sys?
I can see that the object "KUPC$C_1_20190915100610" is a queue of SYS..
Should I need to restart my export or can I use it after finished?

Thank you.

since you have multiple errors I recommend you to do the following:

if you have an old dump inside this directory move it to another place.
execute the following command select * from v$sgastat where pool='streams pool'; and increase STREAMS_POOL_SIZE value.
check open_cursor value and increase it (make sure scope=both when you edit it).

Note: I would recommend leaving step 3 to the end. Try to take a backup before executing it.


How to create a cumulative sum on update in mysql-python [Migrated from Stackoverflow]

I have the following statement in Python for MySQL Driver, that increments counter by one, on update with the same language.

add_lang = ("INSERT INTO Languages (lang, length, counter)\
            VALUES (%s, %s, 1)\
            ON DUPLICATE KEY UPDATE\
            counter = counter + 1;")

Now, what I want to achieve is a cumulative sum. My analogy suggests:

add_lang = ("INSERT INTO Languages (lang, length, counter)\
            VALUES (%s, 0, 1)\
            ON DUPLICATE KEY UPDATE\
            counter = counter + 1;\
            length = length + new_length")

How to differentiate between inserted new values, and current value before update ? Notice new_length and length. this is just to show my aim.

Try this:

add_lang = ("INSERT INTO Languages (lang, length, counter)\
VALUES (%s, %s, 1)\
ON DUPLICATE KEY UPDATE\
counter = counter + 1,\
length = length + VALUES(length);")

NB: edited to apply parameter replacement to the VALUES


Partial (filtered) replication in PostgreSQL My setup looks something like this: I have a primary server (Postgres 9.6) that continually scrapes a bunch of data from the web. I'd like to be able to stream a subset of tables or databases from that primary into a secondary instance so I can read the data from a web app. The web app needs to do standard web app things with its database (read/write tables for user accounts, perform authentication, etc.) and also be able to run queries on the replicated data.

Streaming replication with a hot standby looked like it would be up my alley, but a Reddit response from a few years ago seems to suggest that you can't filter for specific tables or write to the replicated cluster at all. From poking around on the Internet, my impression is that most people solve this problem with third-party tools like Bucardo and Slony – I'm open to that, but I'm looking for the simplest possible solution and adding a dedicated replication system to my stack seems unnecessarily intense. This SO question from last year had a promising lead using logical decoding, which makes me wonder if there is any new work in that direction. I'm also wondering if it would work to just run two instances of Postgres and bind them to different ports.

Are there any solutions here that don't require installing third-party tools? If a third-party tool is absolutely necessary, which one would have the lowest overhead for my purposes, from your experience?

Thanks in advance for the help, y'all. First question here and I'm thrilled to have access to your expertise.

Multiple people suggested using the pglogical extension, which turned out to work really well for this use-case.


Difference between isolation levels in locktable implementation I'm trying to implement lock tables in database with c++ and mysql. i'm using tpc-a benchmarks where i simulate a mini bank database and i run a bunch of threads on it and each thread do 10 transactions concurrently and goal is to maintain consistency after all transaction finished their work.
here is a piece of code that is a part of for loop that each thread run 10 times :

g_lockTableAccount->lockRecord(hashInt(randomAccountNumSource, g_numAccount), randomAccountNumSource, GetCurrentThread());
    startTransaction(conn);
    //get first account balance and branch.
    {

        std::string query = "SELECT account_balance,branch_ID from `account` WHERE account_ID = " + std::to_string(randomAccountNumSource) + ";";
        executeQuery(conn, query);
        MYSQL_RES * res = mysql_use_result(conn);
        MYSQL_ROW row = mysql_fetch_row(res);

        sourceAccountBalance = toInt(row[0]);
        sourceAccountBranch = toInt(row[1]);
        mysql_free_result(res);

    }
    //--------------------------------------------------------------------
    sourceAccountBalance -= amount;
    {
        std::string query = "UPDATE `Account` SET account_balance = '" +
            std::to_string(sourceAccountBalance) + "' where account_ID = " + std::to_string(randomAccountNumSource) + ";";
        executeQuery(conn, query);

    }
    //--------------------------------------------------------------------
    commitTransaction(conn);
    g_lockTableAccount->unlockRecord(hashInt(randomAccountNumSource, g_numAccount), randomAccountNumSource);

i lock and unlock other records with the same pattern. but i feel there is something wrong with it because i think the pattern of running a sql command and committing it is not correct, but the problem is when i start transaction at the beginning of for loop and commit at the end this pattern ensue deadlocks because at any isolation level the database won't release the locks it acquired. how can i do this correctly and without getting deadlocks?
is there anyway at all?
how different isolation levels affect this problem?

In just MySQL, one would do this:

START TRANSACTION;
SELECT @bal := balance FROM acct WHERE id=123  FOR UPDATE;
SET @bal = @bal - @amount;
UPDATE acct SET balance = @bal WHERE id = 123;
COMMIT;

The FOR UPDATE declares the intention to change the row(s) found.  This keeps other threads away -- not via deadlock, but via a delay.

I don't know what is added by lockRecord().  Maybe it is a replacement for FOR UPDATE.  If so, it is over-kill since it grabs the row for longer than the MySQL code does.

I don't think transaction_isolation_level matter, but I am not sure.

A deadlock can occur when you grab multiple rows in a transaction while another transaction is grabbing the same rows in a different order.  In some situations, the "gap" next to a row counts, and can participate in a deadlock.


Oracle AWS RDS supermaster user cant grant select on some tables I'm dealing with some sort of issue beyond my Oracle knowledge. A customer wants to execute some queries to detect issues, locks, etc in a Oracle RDS database. I assume this queries needs database administration privelege to be executed, in fact, is pretty clear that me, as AWS Oracle supermaster can execute without error any query, for example:

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece;

the same query in normal user returns an error

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:
Error at Line: 3 Column: 6

so my initial idea was to let normal user to SELECT some tables, one by one, adding privileges one by one. It worked to me with the first query, I was able to add this to the normal user (by sqldeveloper plus)

GRANT SELECT ON v$session to "TESTUSER";

so I decided to continue this approach. Bad for me, next SQL commands to assign privileges are returning an error, for example:

Error starting at line : 14 in command -
GRANT SELECT ON v$sqltext_with_newlines to "TESTUSER"
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

So is like I'm not "powerful" enough as supermaster of Oracle RDS to assign some privileges to other Users. I tried to use the GUI of sqldeveloper and run "Grant all" and it returns to me the same error. Most of the privileges were not able to be granted.

Is this normal behavior? I can assure I have the supermaster user of AWS RDS Oracle. I double checked before.

Thanks

This will not work:

GRANT SELECT ON v$sqltext_with_newlines to "TESTUSER";

v$sqltext_with_newlines is a public synonym for sys.v_$sqltext_with_newlines. If you want to grant select on that view to someone, use this:

GRANT SELECT ON sys.v$sqltext_with_newlines to "TESTUSER";

And after that, TESTUSER can select from v$sqltext_with_newlines. But:

Just because your user can select from v_$sqltext_with_newlines, that does not mean you can grant this privilege to others. For that, you need to have the select privilege granted with the GRANT OPTION to your user. This restriction can be worked around by having the GRANT ANY OBJECT PRIVILEGE system privilege, for objects in user schemas, but not for objects owned by SYS.

Here is the list of privileges of the master user:

Master User Account Privileges

  ALTER DATABASE LINK, ALTER PUBLIC DATABASE LINK, DROP ANY DIRECTORY,
  EXEMPT ACCESS POLICY, EXEMPT IDENTITY POLICY, GRANT ANY OBJECT
  PRIVILEGE, RESTRICTED SESSION, EXEMPT REDACTION POLICY

  AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE, CONNECT, CTXAPP, DBA,
  EXECUTE_CATALOG_ROLE, RECOVERY_CATALOG_OWNER, RESOURCE,
  SELECT_CATALOG_ROLE

So no, the master user does not have unlimited access, there are some things that even that user can not do.


Adding two columns results in concatenation instead of addition I need help using addition.  When I do it populates using the concatenate function.

I used the following in my access database Total: [ANTI TERROR]+[Records] and instead of returning 1 + 1 = 2 it returns 11 - i.e. it's using the concatenate function.

Maybe you should use the type conversion function CInt()?

As in, CInt(expr1) + CInt(expr2).  Does this fix your problem?

This is necessary if you haven't defined the column data type as a numeric type.


Using non-replicated read/write databases on a MySQL slave server I have a MySQL slave server that has two databases being replicated from a master. What I'd like to do is add a third database that I will exclude from replication and use as a read/write database. Basically this is a database to store local session variables, so I won't have to run MySQL on the application server in this region.

So my questions are:

Can I do this?
If I can, is it a good idea, or should I keep the slave strictly to read only databases (beyond practical considerations)?

Any answer much appreciated, technical explanations and links to more reading would be great. I've found it difficult to Google much on this topic.

You can safely write to a schema on the slave if that schema does not exist on the master or if you correctly use replication filters to prevent its replication to the slave.

"Correctly" is the important word in that sentence.  Refer to this page:

https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html

Check the --replicate-do-db and other filter settings, and note that the behavior changes based on whether binlog events are formatted as rows or as statements.  When statement-based replication is being used, replication filters only apply to the database in use as the current database.  When row-based replication is used, the filters are applied regardless of current database.

Be sure you get this right, otherwise, you could have a situation like this:

replicate-ignore-db=monkey
binlog_format=STATEMENT

Then you do this:

USE zebra;
TRUNCATE monkey.tablex;

Guess what?  tablex just got truncated on both master and slave.  However, if you had this instead:

replicate-ignore-db=monkey
binlog_format=ROW

and did the same TRUNCATE on the master, monkey.tablex would not be truncated on the slave.

Note that this all applies according the binlog_format in use at the time the event is logged, and since binlog_format can be changed on a per-session basis, you can get some surprises unless you know exactly what's going on.

Having said all that, if the schema you're writing to does not even exist on the master, then you have nothing to worry about, other than the fact that you can't have read_only or super_read_only set on the slave if you plan on writing to it, so you can't count on it preventing write activity.


Needing A Database Design Book I am designing a database and it has so many relationships among my tables and i need a book that teaches database design very well.I am looking for a book where table relationships simple and complex has been covered extensively and maybe case studies in the book.

Both of these guys authored several books, but I'm going to link to their blogs in case you want something more immediate.

Louis Davidson: http://drsql.org
Paul Nielsen: http://www.sqlserverbible.com/


SQL Server 2008 R2 Log File Location One of the databases I am going to support does not have a validate log file location.  The drive which the log file is pointing to does not exist.   But I don't see any error in database error log because of this.

Is there a default location for SQL server log file when the log file path is not set right?

Thanks

If you go to server properties, in the database settings, you will find the default database data, log and backup locations :


Move Oracle data to and from MySQL with the help of PHP? My application is in PHP which by default uses MySQL db. I have connected MySQL & Oracle dbs with PHP.

Now I want to move data from Oracle db into Mysql db through PHP. Is it possible to do? Is it necessary for me to somehow sync between the two databases as we need to have our backup on the Oracle db.

How do I do this with the help of PHP?

How about if you export your data to an external script file using MySQL dump (you have the options of exporting only the data and not the sturcture), which can be done through PHP and you run another PHP script of load the data into Oracle using straight PHP or an oracle client.

Instead of using MySQL dump you can also generate an SQL file which contains the records which have changed

You can also schedule the jobs using Cron but the import into Oracle job should happen some time, maybe an hour or two depending on the time it takes to generate the dump file.


[Solved]MySQL index work in one table and not work in another table when using IN I was asked if the index is working or not when we use IN keyword in MySQL like below:

 //suppose col has index and the value is almost unique
 SELECT * FROM yourtable WHERE col IN('xxx','yyy','zzz');

In order to get the accurate result, I do the test myself,I created two tables,and index works on one table but not work on another table,I do not know why.Hope someone helps me understand it.Thanks in advance.

The first table is called system_users :

CREATE TABLE system_users(
  id INT(8) NOT NULL AUTO_INCREMENT,
  NAME CHAR(15),
  gender CHAR(1),
  age INT(2),
  department CHAR(1),
  email VARCHAR(20),
  PRIMARY KEY  (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE INDEX name_index ON system_users(NAME);

INSERT  INTO system_users(id,NAME,gender,age,department,email)
  VALUES
(1,'euser_izpwhzzcz','1',37,'1','yvmk0oqm0lf@mail.com'),
(2,'muser_czepyfklv','1',20,'1','a9wq08gcllk@mail.com'),
(3,'tuser_mwrluajek','0',20,'1','o8jqqebi0bt@mail.com'),
(4,'duser_jzgpheafx','0',22,'2','hegehgsbfmg@mail.com'),
(5,'auser_iacjkekhg','0',36,'2','46drnrja29i@mail.com'),
(6,'euser_eikhwvwsi','1',31,'2','56amq6nayde@mail.com'),
(7,'muser_lblozikah','1',29,'3','qlbtvpzgrej@mail.com'),
(8,'cuser_xhsuuzzur','0',35,'3','klrr6kurylg@mail.com'),
(9,'vuser_iwmklbyzp','0',22,'3','ngdrlguhzrp@mail.com'),
(10,'buser_yfnffsfoc','0',37,'4','o1jvffj4ahu@mail.com');

Now,when I use EXPLAIN to analysis below query sql,I found the index works

EXPLAIN SELECT * FROM system_users WHERE NAME IN('euser_eikhwvwsi','buser_yfnffsfoc');

As we see the value of type is range,in this scenario index seems works when use IN

The second table is called test:

CREATE TABLE test(
  id INT PRIMARY KEY AUTO_INCREMENT,
  c1 VARCHAR(10),
  c2 VARCHAR(10),
  c3 VARCHAR(10),
  c4 VARCHAR(10),
  c5 VARCHAR(10)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE INDEX c_index ON test(c1);

INSERT INTO test(c1,c2,c3,c4,c5) VALUES('a1','a2','a3','a4','a5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('b1','b2','b3','b4','b5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('c1','c2','c3','c4','c5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('d1','d2','d3','d4','d5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('e1','e2','e3','e4','e5');

INSERT INTO test(c1,c2,c3,c4,c5) VALUES('a1','b1','c1','d1','e1');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('b1','a2','b2','b4','b5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('c1','a3','c3','d4','c5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('d1','a4','d3','d4','d5');
INSERT INTO test(c1,c2,c3,c4,c5) VALUES('e1','a5','b5','e4','e5');

Now when I use EXPLAIN to analysis below sql,the index seem not work since the value of type is all

EXPLAIN SELECT * FROM test WHERE c1 IN('a1','b1');

Both of the two tables has ordinary column and datas,but when use IN,why the type of one is range while the other is all?

PS:The version of MySQL in my side is 5.0.67,I have run OPTIMIZE TABLE for both table and still get the same result.

Below are the result when execute ANALYZE TABLE

Note:I have found it has something to do with the data amount in my version,if I insert hundreds of data,then the explain result are expected

In both cases, you should and will get index utilized in your explain plan.  I am not sure why you are getting different plans, but I have got the same by executing your code as it is.

First plan

And the second plan:

There may some fragmentation/table stats issue.  would you execute OPTIMIZE TABLE or ANALYZE TABLE on your tables to see the correct plan?


Enabling MSSQL System Trace for DB Objects I am trying to get changed recorded in MSSQL server for tables, view and stored procedures.

I have tried to achieve the same by right clicking on the server->Reports->standard reports->schema changes history. But it doesn't show all the history from the beginning.

How far can I get sys trace from database as currently, I am getting records from last few months only. My requirement is to capture them from the very beginning.

Regards,
Zafor.

What you are using is part of a default trace, 2005 to 2012 SQL Server uses this but it is deprecated and in later versions system_health is an Extended Events session that is used going forward.

MSDN on default trace.

MSDN on system_health.

The default to store trace data for SQL Server is 20MB, so it is not a time based operation per se, but a space based. So it makes sense you can't see everything but you do see some data.

MSSQLTips: Aaron Bertrand on historical traces.

A common recommendation is to create a script setup to copy the data into a table that won't be deleted or to move and aggregate the trace log files.

For Extended Events, SQL Server will store up to four 5MB files, below is an article to view and adjust those settings for greater retention.

SQLWorldWide:Taiob Ali on Extended Event file retention.


Controlled Transaction Blocking - Resume Behavior When several procedures are blocked on a shared resource, how does sql server pick which one to resume?
Example 1
proc sp_a
begin tran

  select 1 from dbo.lock_tbl with ( tbllockx )

  delay wait for '00:00:01'
  commit / rollback;

Execute
exec sp_a -- on terminal 1
exec sp_a -- on terminal 2
exec sp_a -- on terminal 3

Does sql server choose whether to start the instance on 2 or 3 first?
Example 2
Closer to intended use
proc sp_b @source_param

declare @rows cursor = ... get_rows( @source_param )
while ( ... for each @row )
begin
  begin tran
    select 1 from dbo.lock_tbl with ( tbllockx )

    -- do logic
    exec sp_g @row

  commit / rollback;

  delay wait for '00:00:01'
end

Execute
exec sp_b 'd' -- on terminal 1
exec sp_b 'e' -- on terminal 2
exec sp_b 'f' -- on terminal 3

Will the code above round-robin execute procedure sp_g from each procedure
eg: will it execute sp_g on terminal 1, then execute sp_g on terminal 2, then execute sp_g on terminal 3, then execute g on terminal 1, etc?
Followups :
Does it pass the lock to the procedure which has been waiting the longest?
Is there a way to encourage round robin behavior?
Rationale
( optional )
The database is a low transaction database, but will hit deadlocks when multiple transactions occur at the same time, due to complex execution business logic. A global lock table can be used to eliminate deadlocks via throttling the execution flow to one thread at a time. Keeping the execution synchronous seems more desirable than restructuring the database and UI into asynchronous queues. The work will take place in small transactions and hand off procedure control between the procedures, which will take turns to complete their respective work in chunks, thus slowing the completion of the tasks in exchange for reliable synchronous completion.

Example 1: You shouldn't care. The process is much more involved than it seems, and I don't think you have any guarantee. The 3 commands go into the queue in order. The first one is pulled and executed, when the second is attempted, the engine may choose to put it back in queue if it waits for more than some threshold, and the third may be pulled and attempted. if it happens that the resources are now free, the 3rd will be executed first. if not, it may go back in queue again.

Example 2: I think the above explains it, and the answer to both your questions is no AFAIK, unless you develop an execution buffer of your own. Don't.

Rationale: Now you got to the core challenge, which should really have been the title - "How to deal with deadlocks". You assumed that your 'global lock table' solution is the only way to make things right, but I think the price of losing all concurrency is not worth it. It would be easier just to have the procedures executed serially using a scheduler of some sort. You will open a Pandora box with this approach that will be hard to close. Queuing and syncing is a very complicated challenge.

If I were in your place, I would first investigate the root cause of the deadlocks. Sometimes, something as simple as adding or modifying an index can solve it.
If you post your deadlock graphs here, along with the involved queries with their execution plans, people will help you pin point the root issue.

HTH


Recursive CTE throws temp tablespace is empty error I need to aggregate the contents of multiple rows into a row as a delimited text. Here's the simplified table with sample data which represents what i want to do.

CREATE TABLE SPD_OWNER.EMP (
    EMPID NUMBER,
    NAME VARCHAR2(20),
    MGRID NUMBER,
    DEPT VARCHAR2(5)
);

INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT');

-- query which has issue
WITH tmp (rnum,dept,id) AS
    (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
    (SELECT rnum, dept, id FROM tmp WHERE rnum=1
        UNION ALL
    SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);

Am expecting below output.

5 IT 1/2/3/4/5

But running above query gives me an error SQL Error [25153] [99999]: ORA-25153: Temporary Tablespace is Empty

Isn't the condition tmp.rnum=cte.rnum+1 sufficient to break the recursive loop? Here's what am imagining to be happening.

1st select (above union all) will select rnum=1
1st recursive iteration will select tmp.rnum=cte.rnum+1, i.e.
tmp.rnum=2
2nd recursive iteration will select tmp.rnum=3
and so on till this condition is invalid, i.e. when cte.rnum=5 (because then tmp.rum=5+1 will not have matching record)

Am assuming that am getting the error since it's going into infinite loop.
What am i missing?

Side Note: Am using Oracle DB, so i can easily do this using LISTAGG. But i also want to run this query in H2 DB. So i want to have a solution that uses standard sql. Am open to other ways to achieve the same result.

That error does not mean the temp tablespace got full, it means it is empty. Empty as no tempfile in it.

CREATE TABLE EMP (
    EMPID NUMBER,
    NAME VARCHAR2(20),
    MGRID NUMBER,
    DEPT VARCHAR2(5)
);

INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT');

Query:

SQL> WITH tmp (rnum,dept,id) AS
    (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
    (SELECT rnum, dept, id FROM tmp WHERE rnum=1
        UNION ALL
    SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
  8  WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);

      RNUM DEPT  ID
---------- ----- --------------------
         5 IT    1/2/3/4/5

Now if I drop the tempfile:

SQL> select file_id from dba_temp_files;

   FILE_ID
----------
         1

SQL> alter database tempfile 1 drop;

Database altered.

SQL> select file_id from dba_temp_files;

no rows selected

SQL> WITH tmp (rnum,dept,id) AS
    (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
    (SELECT rnum, dept, id FROM tmp WHERE rnum=1
        UNION ALL
    SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
  8  WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);
WITH tmp (rnum,dept,id) AS
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty

Check DBA_TEMP_FILES and fix your temporary tablespace.


Multi-Column index not used for index-only scan, but partial index is My question is why a multi-column index is not used for a index only scan, when a partial index with equivalent information (i think) is.

The table:

CREATE TABLE test
(
  id      INT,
  descr   TEXT,
  flag    BOOLEAN
);

INSERT INTO test
SELECT GENERATE_SERIES(1,100000) AS id,
       MD5(RANDOM()::TEXT) AS descr,
       (RANDOM() < 0.1) AS flag;

SELECT *
FROM test LIMIT 10;

A content sample:

id  descr   flag
1   81978ceb5514461fbad9af1152ad78f6    true
2   cc0aee68ba3e0095cc74d53e8da55fef    false
3   689a76e5897d565638f8ddd2d2019b7a    true
4   9df03bc2969a6af88cd1d6e0423d0f4c    true
5   318983766d11f831e9f0df34606dc908    false
6   198102bb71640a16f28263b7fb56ba2e    false
7   9bef7320389db46a8ad88ffa611e81b5    false
8   c1f0d637ee0a985aa7d768a78d2d97b1    false
9   781b4064f721ae3879d95579264b0aba    false
10  c4582890bb1e9af430e0f36b50f5e88c    false

The query I need to run is:

SELECT id
FROM test
WHERE flag;

Now if I use a partial index. The query (eventually) gets executed as a index only scan:

CREATE INDEX i1
  ON test (id) WHERE flag;

QUERY PLAN
Index Only Scan using i1 on test  (cost=0.29..354.95 rows=9911 width=4) (actual time=0.120..6.268 rows=9911 loops=1)
  Heap Fetches: 9911
  Buffers: shared hit=834 read=29
Planning time: 0.806 ms
Execution time: 6.922 ms

What I do not understand is, why a multi column index of the following form is never used for a index-only scan?

CREATE INDEX i2
  ON test (flag, id);

QUERY PLAN
Bitmap Heap Scan on test  (cost=189.10..1122.21 rows=9911 width=4) (actual time=0.767..5.986 rows=9911 loops=1)
  Filter: flag
  Heap Blocks: exact=834
  Buffers: shared hit=863
  ->  Bitmap Index Scan on i2  (cost=0.00..186.62 rows=9911 width=0) (actual time=0.669..0.669 rows=9911 loops=1)
        Index Cond: (flag = true)
        Buffers: shared hit=29
Planning time: 0.090 ms
Execution time: 6.677 ms

Can't the query visit all consecutive leaves of the btree where flag is True to determine all ids?
(Note that tuple visibility probably is not the problem since the index-only scan is used with the partial index i1.)

My Postgres version is: PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit

Tuple visibility is the problem.  It uses the bitmap index scan in the unvacuumed case because it thinks it will be faster, not because it is incapable of using the IOS.  Once I do a VACUUM test on your exact example, it starts using the index only scan on index i2, because now it thinks that the IOS will be faster.

As for why it doesn't use the bitmap scan for the partial index when the table is not vacuumed, that is a bit more complicated and is not about visibility. One of the benefits of bitmap scans (and in your case, the only benefit of them) is that it reads the table in physical order and visits each table block only once, regardless of the order of the index. PostgreSQL sees a perfect correlation between id and the order of the physical table rows, so for the index with id as a leading column (the partial index) it think this benefit does not really matter, as it will read the table in physical order anyway even with a regular index scan.  Since bitmap scans have slightly higher CPU cost, it won't do one when it offers no predicted IO advantage.  The correlation between the flag column and the physical table order is lower, and so it thinks bitmap index scans will get a benefit when using the index which has flag as its leading column, which happens to be the full index.  It thinks this benefit outweighs the CPU cost.  If the table was marked all-visible, then the benefits of an index-only-scan would tip the scales back again, but without vacuuming, that doesn't happen.

Now in the case of the index on (flag, id), the leading column is being tested for equality and the only rows selected will have the same value for flag.  So what really matters is the correlation with the next column in the index, id, not with the flag column.  But PostgreSQL isn't (apparently) clever enough to understand this. Now I am kind of curious how hard it would be to make it understand that.


Replicating schema changes between production and development databases Not too sure if this would be a good and proper way to go about things, so I apologize if I'm kind of far off.

Currently running Microsoft SQL Server 2005, and in this we have a database for a production website, and now just made a copy of it for a development version of the website. What my question is, what would be the proper way to "replicate" or "mirror" table structure changes and things of that nature from the development database to the production one, when we do our upgrade? Is there a simple way to do a migration like this?

If you have access to a db version control system (ala Red Gate), you can have it create a deployment script fairly easily.
If not, just create a file and script any ddl changes you make and store them in a file, then when you are ready (and have backed up prod) you can run the script against prod to make your changes.
Be aware that certain changes can require extra steps (adding a not null column without a default is always a favorite), but that is the basic procedure. Also be aware of how your data might affect your ability to promote changes (key constraints, etc.) which is why tools like Red Gate's SQL Compare and Version control are worth the money.
No I don't work for Red Gate, but I've had to do this job with and without their tools and it is a lot more work without them.


Is it possible to write a SELECT statement to obtain all dates between 2 date columns? My question is related to these 2 questions:

https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates/27319120
https://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function

However in my case, I have a table with an ID,StartDate,EndDate columns and I want to apply the same approach (but instead of 2 days, I want to use the entire table).

And before dropping into functions, I wanted to see if this is possible w/ a select and a date dimension table.

Basically I want to scale this up to the entire table:

SELECT
    t.Id
    ,dd.[Date]
FROM dbo.DateDimension dd
    ,dbo.T1 t
WHERE
    t.Id = 957
    and  dd.Date>=t.StartDate and dd.Date <= t.EndDate

I have Sample Data + Desired Output (The Select Statement) here:
www.sqlfiddle.com/#!18/0e389/2

There is probably something I don't understand in your question, but from the looks of it all you have to do is remove the predicate t.id = ? You can shorten the query a bit by using BETWEEN predicate, I also prefer explicit JOINS over "," joins:

SELECT t.Id, dd.[Date]
FROM dbo.DateDimension dd
JOIN dbo.dbse t
    ON dd.Date BETWEEN t.StartDate and t.EndDate;

In your example in the fiddle, you output 3 id's. If you only want a subset you can use an IN predicate:

SELECT t.Id, dd.[Date]
FROM dbo.DateDimension dd
JOIN dbo.dbse t
    ON dd.Date BETWEEN t.StartDate and t.EndDate;
WHERE t.id IN (4229, 4268, 4269);


Improving SQL Server performance on VMware under Linux I have a Linux server in a small company (3 users) acting as a domain server and running two VMs under VMware: one has  SQL Server 2000 on it and one has SQL Server 2005, in each case running on Win2k. Each of the SQL Servers has a couple of small-but-important databases.

As near as I can tell, the Linux server spends its life utterly bored. It has 2GB of memory, but even with the VMs it never uses more than about 512MB, with the rest being allocated to cache.

But the VM performance stinks! The databases are slower than before I moved them from dedicated (but ancient) machines. If a database hasn't been used for a while, you can go have a coffee before it starts responding again.

What can I do to improve the performance?

I had a similar problem. Virtual disks kill performance. Try to get as much data into memory as possible. I eventually put the disk intensive VM's onto its own RAID stack.

Most people in OIT do not recommend putting disk intensive applications into a VM because of this reason. You also didn't specify which VMWare product you are using. ESXi was released for free not to long ago, if you are not using that you may want to try it. It may help you get that last bit of performance you need.


Separated databases, separeted datas We have a global project. We will have some servers all continents. We separate the datas because it is not necessary that they be in one place together. BUT! We need unique IDs (MySQL, InnoDB).
I have two ideas.

We use the auto_increment_increment and auto_increment_offset each server. The problem: we don't know how many servers will be!
We create a custom random ID generator with VARCHAR(16-32) primary keys. The problem: How can we guarantee the unique values (maybe unique server prefix?) and it isn't slower?

Have anybody other idea? I made some fast benchmark, and I don't find differents the integer or varchar primary key.

Use the solution that already exists for this problem.  Use a UUID.  This would require no communication or management of installations overall.  Do not use plain random values.  The birthday paradox means that the possibility of a collision increases exponentially as the number of records increases linearly.  UUIDs are specifically designed to address this issue.  I am not sure how good MySQL's UUID generator is.

Another option would be to use a compound primary key with an installation ID or customer ID that is determined by you for each instance of the application, combined with an auto_increment field to make sure each record is unique within each instance.


is there any way to left join the select result in MySQL 5.7 Now I am using this query to left join the result of my query:
select date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d') as dt,count(*)
from action_record a
where user_id = 16459
and action_type = 1
group by date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d')

left JOIN

select date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') as dt,max(login_count) as login_count
from report_user b
where user_id = 16459
group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')

on a.dt = b.dt

but it shows error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left JOIN
select date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') as' at line 6

what should I to fix this problem?

You have an incorrect mix of operators in the wrong order, specifically your LEFT JOIN operator can't come after the WHERE or GROUP BY operators. (Normally what you're trying to achieve would be easy with CTEs but your version of MySQL doesn't have CTEs yet.)
Re-writing the query with sub-queries like this is how you can achieve your goals:
SELECT a.dt, a.groupCount, b.login_count
FROM
(
    select date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d') as dt,count(*) as groupCount
    from action_record a
    where user_id = 16459
        and action_type = 1
    group by date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d')
) a
LEFT JOIN
(
    select date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') as dt,max(login_count) as login_count
    from report_user b
    where user_id = 16459
    group by date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')
) b
ON a.dt = b.dt


pg_dump fails to create a backup when running from System User in Windows Task Scheduler I am trying to run a pg_dump through a scheduled task in windows. I have two batch files. One creates the task. The other one runs the pg_dump. Even though the pg_dump works with the current user with no problem, I have to run the pg_dump as a System because I want it to run even if the user is not logged on. Unfortunately, to run a task when a user is logged off, you have to run it in System user and that doesn't work for pg_dump. I couldn't see any error. It simply creates an empty backup file and after that, pg_dump keeps on running, blocking future runs.

Part of my pg_dump code is the following.

%PG_BIN%pg_dump -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -F c -b -v -f %BACKUP_FILE% %DB%

Part of my Schtaks code that creates the task.

SchTasks /Create /SC MINUTE /mo 30 /RU "SYSTEM" /TN "Database Backup" /TR "%SCRIPT_DIR%backup_db.bat"

I found a work around for the problem. I had to change the run as user to the current logged in user after first setting it to SYSTEM. This will prompt the user to enter the password. This change doesn't remove the radio selection on "Run whether the user is logged on or not" and pg_dump works with the current user. The nice thing is, the code runs silently. The code I added under the schedule task creator batch script is the follow.

SchTasks /Change /TN "Database Backup" /RU "%username%" /RP *


Split a query results multiple times I need to create a procedure to make statistics on my database.
First, I decided to create a temporary table to "simplify" my work.
It contains all the relevant actions (FormIdNb) of the working units (NumCavalier) on one day.
-- Every action from all units will be stored in this temp table
-- DateHeure = Time of action
-- NumCavalier = ID of working unit
-- FormIdNb = Action type
CREATE TABLE #tmp_DailyMissions (
DateHeure datetime,
NumCavalier  nchar(3),
FormIdNb int
)

INSERT INTO #tmp_DailyMissions
SELECT        DateHeure, Cavalier.NumCavalier, FormIdNb
FROM            ECN4, Cavalier
WHERE        (DateHeure BETWEEN @StartTime AND @EndTime) AND (Cavalier.Terminal = @terminal) AND (Cavalier.NumCavalier = ECN4.NumCavalier)

Problem is, I'm not able to make the request to make what I want :

I need to split the results by time interval (15min for 1 day)
I need to split again on the maximum value of 'FormIdNb' of each working unit 'NumCavalier' as :

FormIdNb > 3 ==> The working unit was active on that time interval
FormIdNb = 3 ==> The working unit was idle on that time interval (if not active on that same time interval)
FormIdNb < 3 ==> The working unit was inactive on that time interval (if not active or idle on that same time interval)

And finally, make a count for each time interval of working units depending the previous value.

I've already done this, it works partially, but it does not take into account the values of the preceding joins (for example : a working unit can appear active and idle at the same time, and so on...).
I think that I have to make a lot of changes to make it work :
;WITH
time_cte(StartTime, EndTime) AS
(
SELECT @starttime StartTime, DATEADD(mi, 15, @starttime) EndTime
UNION ALL
SELECT EndTime, DATEADD(mi, 15, EndTime) FROM time_cte
WHERE EndTime < @EndTime
)

SELECT StartTime, Count(DISTINCT CavsTerminal_ACTIVE.NumCavalier),
Count(DISTINCT CavsTerminal_IDLE.NumCavalier),
Count(DISTINCT CavsTerminal_INACTIVE.NumCavalier)
FROM time_cte
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) > 3
) CavsTerminal_ACTIVE ON  CavsTerminal_ACTIVE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) = 3
) CavsTerminal_IDLE ON  (CavsTerminal_IDLE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime)
LEFT OUTER JOIN (
SELECT NumCavalier AS NumCavalier, DateHeure
FROM #tmp_DailyMissions
GROUP BY NumCavalier, FormIdNb, DateHeure
HAVING MAX(FormIdNb) < 3
) CavsTerminal_INACTIVE ON  (CavsTerminal_INACTIVE.DateHeure BETWEEN time_cte.StartTime AND time_cte.EndTime)
GROUP BY StartTime

Without implementing #2, your query would probably look like this:

WITH time_cte(StartTime, EndTime) AS
  (
    SELECT
      @starttime StartTime,
      DATEADD(mi, 15, @starttime) EndTime

    UNION ALL

    SELECT
      StartTime = @starttime,
      EndTime   = DATEADD(mi, 15, @starttime)
    FROM
      time_cte
    WHERE
      EndTime < @EndTime
  )
SELECT
  t.StartTime,
  UnitCount = COUNT(d.NumCavalier)
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure >= t.StartTime
    AND d.DateHeure < t.EndTime
GROUP BY
  t.StartTime
;

Now in order to split the COUNT(d.NumCavalier) into three values based on the value of FormIdNb, you check that value when counting – like this:

WITH time_cte(StartTime, EndTime) AS
  (
    SELECT
      StartTime = @starttime,
      EndTime   = DATEADD(mi, 15, @starttime)

    UNION ALL

    SELECT
      EndTime,
      DATEADD(mi, 15, EndTime)
    FROM
      time_cte
    WHERE
      EndTime < @EndTime
  )
SELECT
  t.StartTime,
  ActiveUnitCount   = COUNT(CASE WHEN d.FormIdNb < 3 THEN d.NumCavalier END),
  IdleUnitCount     = COUNT(CASE WHEN d.FormIdNb = 3 THEN d.NumCavalier END),
  InactiveUnitCount = COUNT(CASE WHEN d.FormIdNb > 3 THEN d.NumCavalier END)
FROM
  time_cte AS t
  LEFT OUTER JOIN #tmp_DailyMissions AS d
    ON d.DateHeure >= t.StartTime
    AND d.DateHeure < t.EndTime
GROUP BY
  t.StartTime
;

This is called conditional aggregation: you are aggregating the results based on some condition.

One of the charms of the method is that you can still get the total count in the same output, if necessary, by including COUNT(d.NumCavalier) into the resulting column set.


select count(*) in mysql 5.5 innodb-- rewrite advice? I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow...

select count(*)
from mails3
  join questions using (question_id)
where mails3.from_user_id = '86696'
  and mails3.is_community_star = 1
  and mails3.rating = 3
  and questions.is_rated_community = 1;

the query execution plan looks simple enough but very slow

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: mails3
         type: ref
possible_keys: question_id_index,emails_doublestars,favorite_emails,user_responses,owner_responses,sentmail,watchlist,read_responses,rating_date,read_and_unrated
          key: emails_doublestars
      key_len: 8
          ref: const,const,const
         rows: 2784
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: questions
         type: eq_ref
possible_keys: PRIMARY,is_rated_community_senddate_idx
          key: PRIMARY
      key_len: 5
          ref: answerology.mails3.QUESTION_ID
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

Since you already have the necessary indexes, consider the next thing: Amazons RDS

You are most likely using the default settings. You cannot effectively tune InnoDB for multithreaded/multicore usage in smaller RDS server models.

As to server model usage of memory, here is a chart

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

Since count requires passing through many data and index pages, try using a bigger server model.

I have discussed this before:

Aug 02, 2012 : Local database vs Amazon RDS
Feb 26, 2013 : MySQL optimization

Give it a Try !!!


insert data in multiple tables in one mysql query Suppose there are two tables e1 and e2 with
e1
|id |time     | ca| cb|
|1  | 12:20:00| 5 | 10|
|2  | 12:25:00| 9 | 6 |

and:
e2
|id| time    | cc|
|1 | 12:20:00| 11|
|2 | 12:25:00| 50|

Now I want to insert third record ine1 and e2 having id:3 and store id,time in both table and ca,cb in e1 and cc in e2.it should be perform in single query.
e1
|id | time       |  ca|   cb  |
|1  |  12:20:00  |  5 |   10  |
|2  | 12:25:00   |  9 |   6   |
|**3|   12:28:00 |  11|    7**|

and:
e2
id| time      |  cc|
|1|  12:20:00 |  11|
|2|   12:25:00|  50|
|**3| 12:28:00|  23**|

whether its possible ?I am novice to MySQL

Here are two ways to provide the appearance of "should be performed in single query".  What is your goal behind requesting such?
Data integrity  (see "ACID")
There are cases where you 'must' tie two inserts together, else the integrity of the database is in question.  The classic example is moving money from one account to another, but that usually involves a single table.
 BEGIN;
 UPDATE ... take money out of one person's account
 UPDATE ... add that money to another person's account
 COMMIT;

Whether the same table or different tables are involved, the above guarantees that both operations will be done or neither will be done.  The "money" won't vanish even if there is a power failure at any time during the "transaction".
Is that what you need for "inserting into two table at the same time"?  A transaction can, and should, be used for any set of writes to table(s) in database(s) (on a single instance of MySQL) where the writes must be done "at the same time".
Programming
Akina suggests using a Stored Procedure as a way to programmatically wrap the sequence of events, thereby giving the user the appearance of a "single operation":
CALL Transfer(acct1, acct2, amount);


Update: How to update the first two rows with a Y flag I am trying to update the first two rows of a column from null to Y so that a user should be able to select two images.

Can you please tell me how to fix the query below;

DECLARE CURSOR tp_Identifier_cur
     IS
        select distinct TP_IDENTIFIER
        from IMAGES
        ORDER BY TP_IDENTIFIER ASC;
     l_tp_Identifier  tp_Identifier_cur%ROWTYPE;
  BEGIN
     OPEN tp_Identifier_cur;
     LOOP
        FETCH tp_Identifier_cur INTO l_tp_Identifier;
        EXIT WHEN tp_Identifier_cur%NOTFOUND;
        UPDATE IMAGES SET OMG = 'Y'
        where TP_IDENTIFIER = l_tp_Identifier.TP_IDENTIFIER
        and rownum <= 2
        order by Image_identifier;
     END LOOP;
     CLOSE tp_Identifier_cur;
  END;

You don't need a cursor for this:

update IMAGES
  set OMG='Y'
where image_identifier in (select image_identifier
                             from (select image_identifier
                                     from IMAGES
                                    order by IMAGE_IDENTIFIER)
                            where rownum<=2)

If you want 2 rows per TP_IDENTIFIER, I'd go for a window function:

update IMAGES
  set OMG='Y'
where exists   (select 1
                  from (select image_identifier,tp_identifier,
                               row_number() over (partition by tp_identifier order by image_identifier) as rn
                          from IMAGES
                       ) t
                 where t.image_identifier=IMAGES.image_identifier
                   and t.tp_identifier = IMAGES.tp_identifier
                   and rn<=2)


issues with subquery sql Hi im getting this error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
this is my current SQL query:
DECLARE @UgpEntry VARCHAR(50)

SELECT @UgpEntry = UgpEntry FROM OITM t1 WHERE t1.ItemCode='CID-11418272385'

IF (@UgpEntry = -1)

SELECT
t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     ,
           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [price_1]
     ,
         (SELECT T0.[ItemCode], T2.[U_NAME] as 'User that created Item', t0.updatedate

FROM AITM T0 inner join OUSR T2 on t0.usersign = t2.internal_K

WHERE T0.[ItemCode] = t1.ItemCode

group by T0.[ItemCode], T2.[U_NAME], t0.loginstanc, t0.updatedate

having t0.loginstanc = (select min(loginstanc) from AITM where itemcode = t0.loginstanc)),
     CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='CID-11418272385'

ELSE

SELECT t1.ItemCode as sapitemcode
     , t1.CodeBars as Barcode
     , t1.ItemName as description
     ,LEFT(t1.ItemName,20) as short_description
     , (select max(p.Price)
        from ITM9 p
        where p.ItemCode = t1.ItemCode
          and p.UomEntry = 1 and p.PriceList = 1) as [price_1mananitas],

           (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 1) as [preciocajamananitas],
(select max(p.Price)
        from ITM9 p
        where p.ItemCode = t1.ItemCode
          and p.UomEntry = 1 and p.PriceList = 2) as [price_2cabra],
               (select max(p.Price)
        from ITM1 p
        where p.ItemCode = t1.ItemCode
          and p.PriceList = 2) as [preciocajacabra]

     , CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as TaxCode,t1.U_GRUPOA, t1.U_GRUPOB, t1.U_GRUPOC, t1.UgpEntry

FROM OITM t1
WHERE t1.ItemCode='CID-11418272385'

i just added this subquery:
(SELECT T0.[ItemCode], T2.[U_NAME] as 'User that created Item', t0.updatedate

    FROM AITM T0 inner join OUSR T2 on t0.usersign = t2.internal_K

    WHERE T0.[ItemCode] = t1.ItemCode

    group by T0.[ItemCode], T2.[U_NAME], t0.loginstanc, t0.updatedate

    having t0.loginstanc = (select min(loginstanc) from AITM where itemcode = t1.ItemCode))

when i run this alone i get something like this:

what im trying to do, is to add that select inside my main query so will appear as new columns inside the original query. So it should appear like new columns (T2.[U_NAME] and t0.updatedate)  at the end like this:

what im missing?
thank you

used:
WITH sub AS (
    SELECT
        t1.itemcode as sapitemcode,
        t1.CodeBars as Barcode,
        t1.ItemName as description,
        LEFT(t1.ItemName,20) as short_description,
        --MAX(case when T0.PriceList = 1 then t0.Price else null end) as price_1,
        case when T0.PriceList = 1 then t0.Price else null end as price_1,
        NULL as precio_tableta,
        CASE t0.PriceList when '1' THEN 1 END as Price_level_1,
        --CASE t1.SalUnitMsr when 'PZA' THEN 1 WHEN 'UND' THEN 1 when 'CAJA' THEN 1 when '' THEN NULL END as uom_group_id,
        case when t1.NumInSale > 1 THEN '1' END as uom_group_id,
        (   SELECT T5.[ItemCode]
            FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K
            WHERE T5.[ItemCode] = 'CID-11418272385'
            group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate
            having t5.loginstanc = (select min(loginstanc)
                        from AITM
                        where itemcode = 'CID-11418272385')) AS col1,
        (   SELECT T6.[U_NAME] as 'User that created Item'
            FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K
            WHERE T5.[ItemCode] = 'CID-11418272385'
            group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate
            having t5.loginstanc = (select min(loginstanc)
                        from AITM
                        where itemcode = 'CID-11418272385')) aS col2,
        (   SELECT t5.updatedate
            FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K
            WHERE T5.[ItemCode] = 'CID-11418272385'
            group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate
            having t5.loginstanc = (select min(loginstanc)
                        from AITM
                        where itemcode = 'CID-11418272385')) AS col3,
        CASE t1.VatGourpSa when 'V0' THEN 4 when 'V1' THEN 1 WHEN 'V2' THEN 2 WHEN 'V3' THEN 3 END as Impuesto,
        t1.U_GRUPOA as Grupo,
        t1.U_GRUPOB as Departamento,
        t1.U_GRUPOC as Categoria,
        t1.NumInSale as ItemsPerSalesUnit,
        t1.SellItem as SellItem
    from ITM1 T0
    inner join oitm t1 on t0.itemcode = t1.itemcode
    inner join ouom t2 on t2.uomentry = t0.uomentry
    where t1.ItemCode='CID-11418386891'
    and PriceList='1'
)
SELECT sub.sapitemcode,
    sub.Barcode,
    sub.description,
    sub.short_description,
    MAX(sub.price_1),
    sub.precio_tableta,
    sub.Price_level_1,
    sub.uom_group_id,
    sub.col1,
    sub.col2,
    sub.col3,
    sub.Impuesto,
    sub.Grupo,
    sub.Departamento,
    sub.Categoria,
    sub.ItemsPerSalesUnit,
    sub.SellItem
FROM sub
GROUP BY sub.Barcode,
    sub.description,
    sub.short_description,
    sub.precio_tableta,
    sub.Price_level_1,
    sub.uom_group_id,
    sub.col1,
    sub.col2,
    sub.col3,
    sub.Impuesto,
    sub.Grupo,
    sub.Departamento,
    sub.Categoria,
    sub.ItemsPerSalesUnit,
    sub.SellItem


SYSAUX used space close to 100% I am using Oracle 11g database server.

I checked the SYSAUX used space using Object Browser, and it shows 99%.

What will happen if it reaches 100%?

That depends-- are your data files set to autoextend?  If they are, they'll just grow themselves up to whatever limit you may have set (or to the size of the available disk).  If not, whenever the database tries to allocate the next extent in the tablespace, the operation will fail and whatever is trying to write data to a table in the SYSAUX tablespace will fail.  There are many products that you might install in SYSAUX and any one of them might be the unlucky one that needs to allocate the next extent so you can't really predict what impact that would have.  I'd guess that you'd stop being able to write audit information since the audit trail is commonly in SYSAUX and commonly the most prevalent user of space among products that are commonly installed in SYSAUX.

You can check whether your data files are set to autoextend by looking at the autoextensible attribute in dba_data_files.

select file_name, autoextensible
  from dba_data_files
 where tablespace_name = 'SYSAUX';


Writing something then immediately read when using read/write split: how to deal with replication lag We have an issue where we write to the master table, then immediately must read using the reader. We are experiencing issues with replication lag where the data is not available to the reader at the time we execute our read statement (our replication lag is around 18 ms, which seems pretty fast so I don't think it's a problem with having too much lag).

What patterns are there to deal with this problem? So far our solutions are:

Just use the writer to read
Put logic in our code to wait for replication to occur

Are there any other ways of dealing with this?

We have an issue where we write to the master table, then immediately must read ...

If the Requirement is that the read is time-critical and must have the very latest data, then read the very latest data - from the master.
This is especially true if you're generating values as part of the update and need to retrieve them, which you should ideally do within the same transaction.  (Actually, that's about the only reason I can think of that you'd need to read back the [same] data that you've just saved).


How would I sync tables in two separate servers in mysql? DB: mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using
readline 5.1

I have two separate servers running MariaDB 5.5, both in production.
Both setups are live, and users might access any setup they want.
I need to sync certain table columns from these two databases.
The tables I need to be synced are Userprofile and Devicedetails. Both have a lastlogin and lastused column respectively, and the one with the later date would need to be replaced in the other database.
Schema for Userprofile:
+-------------------------+------------------+------+-----+---------+----------------+
| Field                   | Type             | Null | Key | Default | Extra          |
+-------------------------+------------------+------+-----+---------+----------------+
| ProfileId               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Username                | varchar(255)     | NO   |     | NULL    |                |
| Disabled                | int(11)          | NO   |     | 0       |                |
| Tokenhash               | varchar(50       | NO   |     | 0       |                |
| LastLoginTime           | datetime         | NO   |     | NULL    |                |
+-------------------------+------------------+------+-----+---------+----------------+

Schema for Deviceprofile:
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| Userid           | varchar(255) | NO   |     | NULL    |       |
| Registerdate     | date         | YES  |     | NULL    |       |
| Approved         | int          | YES  |     | NULL    |       |
| MACaddress       | varchar(512) | NO   |     | NA      |       |
| Hostname         | varchar(255) | NO   |     | NA      |       |
| Domain           | varchar(255) | NO   |     | NA      |       |
+------------------+--------------+------+-----+---------+-------+

As a user logs into a server, their lastlogintime would update. If they create a new secret, that would also update (only used for login, think something like a TOTP Token). This user could tomorrow login to the other server, and they should be able to use their existing tokens.
Similarly, their device details would be captured, and Approved would be set to 1, via an external process. This should also be carried over, in case the user accessed the other setup the next day.
If the data from the remote is latest, it should replace in the current table.
I have looked into I..ODKU, but it seems that requires a primary key to clash to update the row, but Profileid is auto-increment, so that would just add rows to the table.
So far, I've exported the data I need into a CSV, and I'm stuck at how I would check which is greater, by looping over the file and checking each individual entry, but there must be a better way of doing this that is far more efficient.

What you likely want to use is called Replication, which is a methodology for keeping two databases across two servers in sync with each other. Please see the MariaDB documentation here on how to set it up: Setting Up Replication
Also I think you'll probably want to understand the different Binary Log Formats before you set it up as well.


What is the term for a “wide”, non-normalized view/query? A well-designed relational DB schema tends to have a relatively large count of relatively small, normalized tables — a cluster of snowflakes, I think, describes the situation.

In order to access the relational data, queries are necessary that join these separate tables into relatively large, non-normalized but very views (or virtual tables)

What is the accepted term for such a “wide” view/query -- a practical, "virtual table" resulting from multiple, useful joins of small, normalized tables?

I have given considerable thought over the years to database design; I just don't have much recent hands on experience so am not necessarily au courant with the terminology.

I am quite sure that the term you are looking for is derived relation.
In relational jargon, a single and normalized table is called a base relation (or base table). In a SQL database management system (DBMS), relations (tables) of this category are the ones declared with the aid of the data definition language (DDL).
The fact of applying certain relational operations (such as JOIN, for instance) on these base relations (or base tables), yields a different type of relation, which is known as derived relation (or derived table). It may be useful to think of this kind of relations as “virtual” tables (as you properly stated) that arise as a result of “playing” with the aforementioned base ones. Tables of this class are declared with the aid of the data manipulation language (DML) in a SQL DBMS.
In order to get practical advantages, a derived relation (table) can be set up as a VIEW in a DBMS. Of course, some of them can be wide and some of them can be narrow, and this aspect is determined by the number of base relations involved and the number of attributes (columns) defined in a particular operation.
To support the above, and because you want an accepted term, it is necessary to refer to an authoritative source. The best one is, naturally, the inventor of the Relational Model, Dr. Edgar Frank Codd. Regarding to these type of relations (tables), here is a small excerpt1 from his paper Extending the Database Relational Model to Capture More Meaning:

Base relations are those which are defined independently of other relations in the database in the sense that no base relation is completely derivable (independently of time) from any other base relation(s). Derived relations are those which can be completely derived from the base relations. It is this kind of relation which is normally employed to provide users or application programs with their own views of the database.

And here is an extract2 from his book The Relational Model for Database Management: Version 2:

…[R]elations, or R-tables, that are internally represented by stored data in some implementation-defined way are called the base relations or base R-tables. All R-tables other than base R-tables are called derived relations or, synonymously, derived R-tables. An example of a derived relation is a view. A view is a virtual R-table defined in terms of other R-tables, and is represented by its defining expression only.

References
1 Codd, E. F. (Dec. 1979). Extending the database relational model to capture more meaning, ACM Transactions on Database Systems, Volume 4 Issue 4 (pp. 397-434). New York, NY, USA.
2 Codd, E. F. (Jan. 1990). Introduction to Version 2 of the Relational Model. In The Relational Model for Database Management: Version 2 (pp. 17-18). Boston, MA, USA: Addison-Wesley


Change Data Capture not running when Mirror is Suspended We have 2 SQL 2012 SP2 Enterprise servers that have some mirrored databases. One of these databases also has CDC enabled on a number of tables so that we can run some business logic when there are changes to the table.

Yesterday we did some maintenance on the mirror server (the primary was running exposed). A few hours later we re-enabled the mirror and it synchronised as expected.

What we found out was that the CDC data that we normally process close to real time didn't get collected until after the mirror was resumed.

2 questions out of this:

When you have a mirror configured, is CDC data only collected when the mirror is synchronized?
Is there anyway to make CDC capture run when a mirror is paused?

CDC uses the log reader, same as replication. When mirroring or availability groups are used, by default the log reader will not process log records that have not already been hardened at all mirroring / availability group secondary replicas.

This can be changed with Trace Flag 1448, which enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled, the log reader always waits for the synchronous secondary replicas.

References:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability#CDC

https://support.microsoft.com/en-us/help/937041/fix-changes-in-the-publisher-database-are-not-replicated-to-the-subscr


Postgis building with `make docs`, "cannot parse /html/docbook.xsl" When I try to build the docs, I'm getting,

make -C doc html
make[1]: Entering directory '/home/ecarroll/code/postgis/doc'
make -C html/image_src images
make[2]: Entering directory '/home/ecarroll/code/postgis/doc/html/image_src'
make[2]: Nothing to be done for 'images'.
make[2]: Leaving directory '/home/ecarroll/code/postgis/doc/html/image_src'
/usr/bin/xsltproc --param section.autolabel 1 --param section.label.includes.component.label 1 --param chunk.section.depth 0 --param generate.section.toc.level 2 --param funcsynopsis.style kr --param admon.graphics 1 --param admon.textlabel 0 --param simplesect.in.toc 0 --param use.id.as.filename 1 --param chunk.quietly 1 --nonet --stringparam html.stylesheet style.css  \
    --output html/postgis.html \
    /html/docbook.xsl \
    postgis-out.xml
warning: failed to load external entity "/html/docbook.xsl"
cannot parse /html/docbook.xsl
Makefile:258: recipe for target 'html/postgis.html' failed
make[1]: *** [html/postgis.html] Error 4
make[1]: Leaving directory '/home/ecarroll/code/postgis/doc'
GNUmakefile:89: recipe for target 'docs' failed
make: *** [docs] Error 2

This is because you don't have docbook.xsl. On Ubuntu this is found in the docbook-xsl package, run

sudo apt-get install docbook-xsl
./configure
make docs


How to Configure Always Encrypted using PowerShell I just want to ask if there is a way to configure always encrypted columns using power shell? like for example i want to encrypt a column in a table with deterministic using powershell codes?

You can configure Always Encrypted using PowerShell. This is documented in Microsoft's Docs page.


MySQL integer column with string search returns the invalid result Can anyone please help me to understand the MySQL select query behavior,

Table Structure:

CREATE TABLE `dummytable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mobile` int(11) NOT NULL,
  `welcome` varchar(150) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Question: Why I am getting this result?

SELECT * FROM `dummytable` WHERE `mobile` = '\'\'' LIMIT 50

A column declared to be INT is designed to hold a number, but not a "phone number".  It can be in the range of about +/- 2 billion.

For a phone number, use VARCHAR(20) CHARACTER SET ascii; that will probably be long enough (20 characters) to handle country-code, dashes, extension, and other kruft that the user might enter.

If you desire to validate that the number is a "valid" string, do that in your application code before trying to store it.


How to UPDATE/DELETE an Oracle Foreign Table with Foreign Data Wrapper from PostgreSQL? I need to transfer lots of row from my PostgreSQL database to a remote Oracle DB through the Oracle Foreign Data Wrapper (FDW). The required operation is so far INSERT to a Foreign Table (FT), but now am thinking to do DELETE/UPDATE as well. UPDATE is needed to flag the inserted rows based on some condition.

Originally I had a problem that INSERT to an Oracle FT got this error:

ORA-08177: can't serialize access for this transaction

And trying with different kind of Oracle Table showed the following pattern:

So then I choose the plain TABLE_4 (without any PK/index). But it turned out that DELETE/UPDATE does not like it, as stated in the error message:

ERROR:  no primary key column specified for foreign Oracle table
DETAIL:  For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
HINT:  Set the option "key" on the columns that belong to the primary key.

However when i use the TABLE_6 (with a PK), a DELETE/UPDATE command keeps on getting the same error that suggests to have a PK.

So how to UPDATE/DELETE an Oracle Foreign Table though Oracle FDW ?

The Oracle FDW docs here tells the following without any details/example that i need:

  If you want to UPDATE or DELETE, make sure that the key option is
  set on all columns that belong to the table's primary key.  Failure to
  do so will result in errors.

and

  For UPDATE and DELETE to work, the columns corresponding to the
  primary key columns of the Oracle table must have the key column
  option set. These columns are used to identify a foreign table row, so
  make sure that the option is set on all columns that belong to the
  primary key.

FYI, my playground:

PostgreSQL 9.6.11 64-bit with PostGIS 2.5.1
PostgreSQL 9.6.15 64-bit with PostGIS 2.5.3
each Postgres is on top of Windows Server 2008 R2 Datacenter, using Oracle FDW version 1.1, connected to different Oracle DB both with version 11.2.0.4.0 64-bit.
though PostGIS is vital in my PostgreSQL database, the data to be transfered to Oracle DB has no geometry at all

As the documentation and the error say, you have to define the foreign table with the key column option for all columns that belong to the primary key. Without knowing what the foreign key is, it is impossible to identify the row in the remote table that should be updated or deleted (for INSERT, that is not necessary).

Here is an example for a table definition that sets the key option:

CREATE FOREIGN TABLE atable (
   id    bigint OPTIONS (key 'true') NOT NULL,
   value text
) SERVER oraserver OPTIONS (table 'ATABLE');

In this example id is the primary key.

You can use the IMPORT FOREIGN SCHEMA statement to have oracle_fdw define foreign tables for you, which will automatically set the key option as appropriate.

The ORA-08177 is a different affair. It can be that oracle_fdw has to scan an Oracle table several times during a single statement (for example, if the foreign table scan in on the inner side of a nested loop join), and oracle_fdw has to ensure that the data seen during these scans are consistent.

That would not be guaranteed with the default READ COMMITTED transaction isolation level, so oracle_fdw has to use SERIALIZABLE (which is not serializable, but guarantees read stability).

Now Oracle has done a seriously bad job implementing this so-called SERIALIZABLE isolation level. It is not downright buggy, because according to the book it is always allowed to throw a serialization error in a serializable transaction, but Oracle hat interpreted this quite freely and throws a serialization error whenever implementing read stability correctly would have proven too cumbersome.

For example, if a concurrent INSERT causes an index page split, a serializable transaction that tries to INSERT at the same time will receive a serialization error. This is of course silly. Similarly, any data modification on a table that has received a data modification from a concurrent transaction will result in a serialization error, even if they don't touch the same rows.

Your response should be, like with all serialization errors, to retry the transaction and hope for more luck next time.

Because this is a common problem, and the cries for a different solution have become so loud, I have recently pulled a patch that enables you to set an option isolation_level on the foreign server. You can set this option to read_committed to change the isolation level to the unsafe value READ COMMITTED.

This is not in a released version yet, but you can safely use Git HEAD if you want to try it. Use this unsafe option at your own risk. For INSERTs, nothing can go wrong, but you are not safe from inconsistent query results in the presence of concurrent data modifications.

A word of caution: while bulk data modification works with oracle_fdw, it is not very efficient, because there is a round trip between PostgreSQL and Oracle for each affected row. The reason for this is in the foreign data wrapper API. It would be difficult to work around that, and I don't think bulk data modifications to be such an important use case.


Linked Server ERROR while trying to insert some rows into remote table I'm trying to insert some rows from local table, to a remote table.

begin distributed tran
begin try
    insert into [ali\servername].[dbname].[dbo].[mytable] select col1, col2 from mytable;
    commit
end try
begin catch
    print 'fail'
    rollback tran
end catch

But I get this error :

  OLE DB provider "SQLNCLI11" for linked server "ali\servername" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

I found this article but there is no way to fix problem in SSMS. Anyone can help?

Thanks in advance.

For linked servers you have to executed like this (specify columns you are inserting into):

insert into [ali\servername].[dbname].[dbo].[mytable]
(col1, col2)
select col1, col2 from mytable;


Restoring Database, creating new copy and use existing transaction logs for the original backup database to restore the NEW database to be current? I am not a DBA nor a database guy, we just recieved a database backup, a differential database backup and daily database transaction logs (each day probably has about 15-20 trans logs). I now have about 20 days worth of transaction logs.

In case the database crashes, I do not want to re-install the original database, the original differiential backup and XXX days of transaction logs.

My question is:
Can I just restore the base database backup, the differential database backup, the 1st 10 days of transaction logs and then do a backup of the database, and then still use the 11th day of the transaction logs to add to the NEW backup I just created? How would I go about doing this? Could someone provide me some T-SQL code on how this could be done or is there a much easier way to work around this?

Well actually I already have some of the steps done, but confused on what would be the best solution since my new database does not seem to work with the trans logs, below is my code:
Restores base backup

  RESTORE DATABASE db_wasumu FROM DISK=
  'C:\Summit_FTP\Full_Backup\ECW33DBCUSTR12A_db_wasumu_FULL_20160522_060256.bak'     WITH MOVE 'db_wasumu' TO 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu.mdf',     MOVE
  'db_wasumu_Log' TO 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu_log.ldf',
    REPLACE, NORECOVERY, STATS = 10;

Restores diff backup

  RESTORE DATABASE db_wasumu FROM DISK=
  'C:\Summit_FTP\Full_Backup\ECW33DBCUSTR12A_db_wasumu_DIFF_20160527_004940.bak'
    WITH MOVE 'db_wasumu' TO 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu.mdf',     MOVE
  'db_wasumu_Log' TO 'C:\Program Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\db_wasumu_log.ldf',
    REPLACE, NORECOVERY, STATS = 10;

Restores transaction log

  RESTORE LOG db_wasumu FROM DISK =
  'C:\Summit_FTP\wasumu_20160527\ECW33DBCUSTR12A_db_wasumu_LOG_20160527_010404.trn'

    WITH NORECOVERY;

Create new  backup

  Now I do the backup: BACKUP DATABASE db_wasumu TO DISK = 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\db_wasumu-Full Database
  Backup_after0529_2.bak'

But when I tried to append the next Transaction log it states:

  Msg 4330, Level 16, State 1, Line 1 This backup set cannot be applied
  because it is on a recovery path that is inconsistent with the
  database. The recovery path is the sequence of data and log backups
  that have brought the database to a particular recovery point. Find a
  compatible backup to restore, or restore the rest of the database to
  match a recovery point within this backup set, which will restore the
  database to a different point in time.  For more information about
  recovery paths, see SQL Server Books Online. Msg 3013, Level 16, State
  1, Line 1

RESTORE LOG is terminating abnormally.

Thanks in advance for your help!!

Sincerely,
Paul

That won't work. As soon as you recover the database after restore, that begins a new timeline for the database.

If this is an on-going process, one possible solution is to get the team generating the backups to take a differential every day which will have fairly low impact on effort and system load. This is in addition to what they currently have. However, it will require some careful management of the files to ensure you have the right sequence of full backup, differential and tlogs. The benefit is, you will not have more than the 20 tlog files to apply if you ever need to restore the database to the latest version. You also don't lose existing PITR based on the 15-20 tlog backups taken daily (I'm assuming there is some PITR requirement given the number of log backups).

If you want to further reduce the number of log files to apply, you can increase the number of differential backups per day but I'd wager the added complexity in managing the files far outweighs the benefits. If you script all your tasks (sounds like you are), execution and scrutinizing for issues is fairly easy to do. If you're using the SSMS GUI, well, you really shouldn't.

** To make it clear, this assumes you already reset the backup chain with a full database backup at some interval. Having just a single full DB backup and only taking differentials and tlog backups from there on is not a viable solution. In most situations, the differential will eventually grow to a large size, possibly bigger than the base (full DB) backup if you have lots of write activities. An example of a basic backup cycle might look like:
 - Full DB backup - every Sunday 2am
 - Differential DB backup - every day
 - 8pm Transaction log backup - every 15minutes
This cycle has potential data loss of up to 15 minutes if there are no other protections (e.g. AlwaysOn) in place.

If this is just a 1-time thing, i.e. you won't get any more tlog backups then you could reduce your recovery time when the goal is to get to the latest point-in-time available. Just restore the DB, differential and apply all the logs. Once done, take a backup of that and keep it along with all the other backup files you have. This buys you rapid recovery when you need to get to the latest version available since you only need to do a single DB restore. However, don't delete all the other backup files in case you need to restore to a specific point-in-time other than the most recent.


Querying an Oracle view don't return records using some clients but it does using others I was given this view that when I query it using OracleSQLDeveloper it returns nothing, but if I use Toad it does return records!

I cannot inspect the definition of the view, and no errors are logged. The query is pretty simple:

select * from weirdview ;

So I have questions:

It must be something different in the session/connection parameters, right?
Is there a way to trace on server side what went wrong? I think there must be exceptions raised but ignored, would that be possible?
How can I list the session parameters from the client that does see results so I can duplicate that configuration in the others.

regards

The TNS Listener running on the server hosting the database has a log file located (on Unix-type servers using Oracle recommended naming) at

/u01/app/oracle/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log

The file listener.log may be named something else if you have multiple listeners.  That log file will show the connection parameters presented by users connecting to the database via the TNS Listener.  I would compare the information in the log for the two connections to see the differences.  And if that looks the same, Michael Kutz' comments would be accurate; there may be a login trigger that looks at the program connecting; if you look at the table  v$session you can see the program column might be used to restrict access.


SQL Write History of Last one week Can we get below information?

SQL Server 2008 R2 write history of Last one week?
I want to know the Disk utilization per/hour for last one week.

We want know reason for strange IO increase in one of our PROD servers.

Please let me know if someone can help me on this. Thanks.

You can check this only if you are monitoring this.

You can also check for problems at a certain time, if you see in SQL Server Log waiting to perform IO operation.

I would start by doing all the Glenn Alan Berry scripts. https://sqlserverperformance.wordpress.com/2016/06/08/sql-server-diagnostic-information-queries-for-june-2016/

You can run a perfmon together with a sqlprofiler and look for the statements that are causing problems (this will give you hardware an query information which you will have to combine)

What I usually get my customers to use is SQL Sentry(SentryOne) this is a montoring tool which has a month free trial. This will show all information you need correlated to queries in an easy to understand GUI. This however is not the only solution, but it is the one I prefer.


ORA-09925: Unable to create audit trail file I'm using Oracle 11.1.0.6.0 db, on Linux 64 bit server on Amazon cloud.

I'm getting following error when I run the connect command

SQL> connect / as sysdba
ERROR:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 9925
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 9925

When I run this df -h command I'm not seeing the file system it's just showing as below

$ df -h
Filesystem Size Used Avail Use% Mounted on

I do not understand cause of this issue. Please let me know how to solve this issue

Try this:
connect to SQL*Plus, then do:

show parameter audit_file_dest

then exit SQL*Plus and do:

cd <path displayed above>
ls -ld .

What are the permissions?  Ownership?

Perhaps a mounted filesystem has gone missing, and you don't have write permissions on the underlying mountpoint?


How to filter specific entries from a MySql table I have a MySql table and it has following values.

Table name results.

    Reg.No   SubjectCode   Attempt   Pass/Fail
    112108   CMIS 1113           1   fail
    112110   CMIS 1114           1   pass
    112119   CMIS 1114           1   fail
    112108   CMIS 1113           2   fail
    112107   CMIS 1113           1   fail
    112108   CMIS 1113           3   pass

Following are the business rules.

Students can have several attempts to pass the subject.
Student should pass each subjects to get the degree.
Some student pass in first attempt.
Some take more than 3 attempt.
However student can try until he/she pass.
But some still remain fail.

So I want to get the Reg.No of students who are still unable to pass the subject.
(eg .112119 and 112107 still unable to pass their subject)

I was unable to write a query for this problem.

Can someone help me?

Thank you

This is what you need.

SELECT DISTINCT `original_results`.`Reg.No`
FROM  `results` AS `original_results`
WHERE NOT EXISTS (
    SELECT DISTINCT `helper_results`.`Reg.No`
    FROM `results` AS `helper_results`
    WHERE `helper_results`.`Reg.No` = `original_results`.`Reg.No` AND `helper_results`.`Pass/Fail` = 'pass'
)


Table column references partial key of table B In my database (MS SQL Server) I use language specific texts. So I defined table Texts as follows:

 CREATE TABLE [dbo].[Texts](
    [Id] [int] NOT NULL,
    [language] [nchar](5) NOT NULL,
    [text] [nvarchar](max) NULL,
 CONSTRAINT [PK_Texts] PRIMARY KEY CLUSTERED
(
    [Id] ASC,
    [language] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[Texts]  WITH CHECK ADD  CONSTRAINT [CK_Texts_language]
CHECK  (([language]='en-US' OR [language]='de-DE'))

ALTER TABLE [dbo].[Texts] CHECK CONSTRAINT [CK_Texts_language]

These texts should be used anywhere in my tables, e.g. here:

CREATE TABLE [dbo].[MyObject](
    [id] [int] NOT NULL,
    [name] [int] NOT NULL,
    [comment] [int] NOT NULL,
 CONSTRAINT [PK_PlantDescription] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

How can I specify the constraint that a value of MyObject.name or MyObject.comment should be a value of the partial key Texts.Id?

There may be several texts for one id, with different languages. So (Id, language) is unique, not Id itself.

EDIT:
Here is an example for table Texts:

Id  language    text
1   de-DE   Holunder
1   en-US   elder
2   de-DE   Von diedem Busch kann man sowohl die Blüten als auch die Früchte verwerten.
2   en-US   From this bush you can use both flowers and fruits.

and table MyObject:

Id  name    comment
512 1       2

Together with UDF

CREATE FUNCTION [dbo].[GetText]
(
    @id int,
    @language nchar(5)
)
RETURNS nvarchar(MAX)
AS
BEGIN
    Declare @result nvarchar(max);

    Select @result = text from dbo.Texts where id = @id AND [language] = @language;
    if @result is NULL
    BEGIN
        Select TOP 1 @result = text from dbo.Texts where id = @id ;
    END
    return @result;
END

With these definitions and the following query

SELECT [Id]
      ,[dbo].[GetText]([name], 'en-US') AS Name
      ,[dbo].[GetText]([comment], 'en-US') AS Comment
  FROM [dbo].[PlantDescription]

I get the result:

Id  Name    Comment
512 elder   From this bush you can use both flowers and fruits.

You see: When I delete row (Id = 1), there will be orphaned rows that I have to delete by a trigger.

Or is there a standard way for language specific texts that I didn't find in the inetrnet?

One way is to create a domain table for the text keys:

CREATE TABLE TEXT_KEYS
( TEXT_KEY INT NOT NULL PRIMARY KEY );

CREATE TABLE Texts
( TEXT_KEY INT NOT NULL REFERENCES TEXT_KEYS (TEXT_KEY)
, [language] [nchar](5) NOT NULL
, [text] [nvarchar](max) NULL
,   CONSTRAINT [PK_Texts] PRIMARY KEY CLUSTERED
    ( TEXT_KEY ASC, [language] ASC
[...]

Now you can use TEXT_KEY from TEXT_KEYS as a foreign key for your other tables.