Tag Info

Hot answers tagged

4

That SQL dialect is from Oracle but this answer will be for MySQL. If datum is DATE select count (*),datum date from tx_feeder where datum between (CURDATE() - INTERVAL 31 DAY) and (CURDATE() - INTERVAL 1 DAY) group by datum; If datum is DATETIME or TIMESTAMP select count (*),DATE(datum) date from tx_feeder where datum between (CURDATE() - INTERVAL 31 ...


4

The clause after on is the condition which is if met, the row will be returned with the result set. In your example, the condition is always met, so the result is the Cartesian product of the joined tables. This is similar to saying ON 1=1, which is always true. You will get the same result if you don't add the condition with the inner join.


2

I think this could be a whitespaces issue. The problem is that MySQL ignores trailing whitespace when doing string comparison. See http://dev.mysql.com/doc/refman/5.6/en/char.html All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. ... For ...


2

Root Cause foreign_key_checks is not a global option you can preset at startup. Why ? When you click on that link to the Documentation on foreign_key_checks, there is no chart that says it is global or session scope. Other options will specify the scope in a chart ft_boolean_syntax says Variable Scope Global ; Dynamic Variable Yes innodb_doublewrite says ...


2

XtraBackup logs the most recent LSN not only in xtrabackup_info which you'll have to extract, but to STDERR, too: 150731 16:33:28 innobackupex: Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... 150731 16:33:28 innobackupex: Waiting for log copying to finish xtrabackup: The latest check point (for incremental): '1948062848' xtrabackup: Stopping log ...


2

There are values that you need to graph with absolute values, such as Innodb_buffer_pool_bytes_dirty Threads_connected Threads_running There are also values whose delta needs to be calculated and graphed, such as Queries Com_select Com_delete Com_update etc ... Incoming and Outgoing Traffic Bytes_received Bytes_sent You should consider using ...


2

You should perform an UPDATE LEFT JOIN and set enabled based on the right side being NULL UPDATE table1 A LEFT JOIN table2 B ON A.code = B.id SET A.enabled = 1 - ISNULL(B.id); Why should this work ? If ISNULL(B.id) is 0, that means A.enabled is set to 1 (1 - 0) because it is in table2 If ISNULL(B.id) is 1, that means A.enabled is set to 0 (1 - 1) ...


2

You can use the technique described in: http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/ to mimic: row_number() over (partition by ... order by ...) In your case that would be something like: SELECT user_id, comment, row_number FROM ( SELECT @row_number:=CASE WHEN @user_id=user_id ...


2

When you store a password you have to store encrypt(password,salt) and salt for this user. To check if input from user is the right password you comapre encrypt(input,salt) with the stored encrypted string. Chanck the section about 8.6.2 What Is Salt? in Practical UNIX & Internet Security, by Simson Garfinkel & Gene Spafford from O'Reilly.


2

In your first query you do ...orders.created <= '2015-07-31'; which is actually orders.created <= '2015-07-31 00:00:00'; since you have a timestamp or datetime datatype. Either use the DATE() function like you did in your other attempt, or write it like orders.created <= '2015-07-31 23:59:59';


2

You can create a temp table, insert your parameters there and then join it: create temporary table tmp (x int); insert into tmp(x) values (10027),(11535); select x.* from activities as x join tmp as y on y.x = x.actor_id join tmp as z on z.x = x.original_actor_id;


1

Better yet: where orders.created >= '2015-07-01' and orders.created < '2015-07-01' + INTERVAL 1 MONTH; This works for DATE, DATETIME, DATETIME(6), leap year, etc. (Notice the <, not <=.)


1

in 5.6.12: (Well, not really an answer, but more info.) mysql> insert into se108833 (product_id,product_id_nn) values ('',''); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> show warnings; +---------+------+-----------------------------------------------------------------+ | Level | Code | Message ...


1

The output is only showing the current query in a transaction. What else is in the BEGIN...COMMIT? Or are you running autocommit=0? Or =1? SELECT grabs shared locks on the rows it is looking at, for the duration of the transaction. INSERT grabs exclusive lock(s). It sounds like the SELECT already has a lock that the INSERT desires. Perhaps the SELECT ...


1

Ofcourse a physical files restore will be quicker than loading a dump. So, if you have full physical backup / snapshot of mysql data-directory - you can go fast! (Below answer assumes that you have physical backup available.) Tables are MyISAM: Stop mysql Copy database folder to mysql datadir Correct permissions Start mysql Tables ...


1

So, rephrasing your requirement a little, you want to delete every row whose pos has the value of OF and for which another row in the same table exists with the same plyrID, year and teamID, and with pos of RF, LF or CF. That sounded almost SQL, you would only need to correct some "grammar": DELETE FROM MyTable WHERE pos = 'OF' AND EXISTS ( ...


1

If the four digits you need to extract are either exactly at the end of the string or are followed only by spaces, it would probably be easiest to extract them using the RIGHT() function after removing the trailing spaces using the RTRIM() function: SELECT RIGHT(RTRIM(YourColumnName), 4) AS SomeCode FROM YourTableName WHERE ... ;


1

When this DML be written to iblog files ? -- "as needed" during the hour When the DML be written to binary log files ? -- Never. Information for the binlog is gathered, but not written until COMMIT. Will the related data pages on disc be modified during 10:00 AM to 11:00 AM -- There may be modifications to the pages. They will be invisible to the user. ...


1

Go to folder C:\wamp\bin\mysql\MYSQL_VERSION\bin Hit shift + right-click in that folder and select "Open command window here" from the menu that pops up. This will open a command window in the path you are already in, so C:\wamp\bin\mysql\MYSQL_VERSION\bin Enter any of the MySQL commands you like with any of the executables you find in that folder. To ...


1

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query. Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event. UPDATE: To prove the query ...


1

I have seen this since about 2002. Ignore it; it will go away. I have failed to find out what causes it, and it so elusive that I could not get anyone to look at as a "bug". I do not know if this will have any impact on innobackupex.


1

With MyISAM, you can get an AUTO_INCREMENT that starts over with each new value. CREATE TABLE foo ( C INT UNSIGNED NOT NULL, N INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(C,N) ) ENGINE=MyISAM; INSERT INTO foo (C) SELECT C FROM your_table; Then... SELECT 100*C + N FROM foo gives you the desired numbers. (If you need U and R, add ...


1

I think NOW() is what should be stored into a TIMESTAMP. As it is stored, the time is modified according to the timezone, and UTC is stored in the table. Upon retrieving, the reverse modification is performed. (This, of course, assumes the system has the 'correct' timezone established. See SHOW VARIABLES LIKE '%zone%';.) That way, if you have users ...


1

As per documentation your error is: Error: 1261 SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS) Message: Row %ld doesn't contain data for all columns Thus it appears that your table have more column than the file. You will have to specify the column-names explicitly. Also the remaining columns should be nullable. (You might want to share table definition ...


1

Your bind-address needs to be 0.0.0.0 or at the very least the IP address of the interface you will be connecting to from your remote machine. If you set it to 127.0.0.1 only loopback connections from the server can be made.


1

Perhaps you should dump the users and load the users into the VM Here is my post : Export all MySQL users After running the code in it and saving it to a file called MyDatabaseUSers.sql, you log into mysql on the VM and run this: mysql> source MyDatabaseUSers.sql This will create all the users with the same grants and passwords. Don't worry: ...


1

Yes, that would be a good idea. I have mentioned a FaceBook Engineer's Blog about doing that, along with putting the DoubleWrite Buffer and Tablespace Files in other places, in my other posts Feb 24, 2012 : Tuning dedicated Percona Server with XtraDB for OLTP (Also See Switching Gears TO RAID) Feb 06, 2014 : MySQL on SSD - what are the disadvantages? Jan ...


1

If I understand correctly, the ideal solution would need GROUP BY GROUPING SETS which is not available in MySQL. Your two options are to use WITH ROLLUP: SELECT date, type, currency, SUM(orders) AS orders, SUM(qty) AS qty, SUM(shipped_orders) AS shipped_orders, SUM(shipped_qty) AS shipped_qty, SUM(return_qty) AS ...


1

it's better to add a userID to your worketime table instead of User and also the name user is reserved word so don't use it. The view you are looking for is made like this CREATE VIEW user_task_time as select `user`,taskid,sec_to_time(sum(TIME_TO_SEC(TIMEDIFF(endwork,startwork)))) as timeWorked FROM worktime GROUP BY user,taskid; TIMEDIFF() return a time ...


1

wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-server-community-5.1.73-1.rhel5.x86_64.rpm wget http://dev.mysql.com/get/Downloads/MySQL-5.1/MySQL-client-community-5.1.73-1.rhel5.x86_64.rpm sudo rpm -Uvh MySQL-client-community-5.1.73-1.rhel5.x86_64.rpm sudo rpm -Uvh MySQL-server-community-5.1.73-1.rhel5.x86_64.rpm sudo service mysql start ...



Only top voted, non community-wiki answers of a minimum length are eligible