Hot answers tagged mysql-5.5
5
From the documentation:
You can also discard an input value by assigning it to a user variable
and not assigning the variable to a table column:
LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, @dummy, column2, @dummy, column3);
3
How are you coming to the conclusion that "these files contain some data even after graceful shutdown"?
Even if it's been fully flushed and uncommitted transactions cleared, etc., by using innodb_fast_shutdown = 0, the "flushing" probably means that the file is "empty" as far as MySQL is concerned, but it won't physically be full of zeroes.
If you start ...
3
The steps you outlined are correct.
I have written about this before
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
May 30, 2012 : mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size
Doing innodb_fast_shutdown = 1 is incomplete because ibdata1 still have other transactional tidbits that ...
2
There is a small problem in your aproach, if I understood correctly, you changed the log file size, and restarted the MySQL with innodb_fast_shutdown = 0. The problem here is that you still have some transaction on log files that aren't in the ibdata. What you need to do is tell MySQL to write all changes from log files to ibdata before move the log files, ...
2
Are you running this test between RDS and MySQL workbench on your own machine?
The MySQL client (and MySQL workbench) are including the time it takes for RDS to return the value of "1" to your workstation (from my workstation on the other side of the world, the response from RDS US West takes 280ms).
If you try enabling profiling on the RDS service, you ...
2
As your main concern is to have an incremental backup solution, you can change the follow variables:
Changes:
innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush it to disk at once.
innodb_doublewrite - Disable the innodb double write
sync_binlog - Disable syn binlog
long_query_time - increase the long query time or ...
2
MySQL lacks common table expressions but you can achieve the same goal here with the multiple-table update syntax:
update activitybooking
cross join ( select sum(pool1_count) p1, sum(pool2_count) p2, sum(pool3_count) p3
from `activitybooking`
where `abt`='12' and (id='958' or `submitted`='1' or ...
2
If you need to JOIN or compare columns, then ensure they are the same datatype. This avoids implicit conversions which usually invalidate index usage.
However, for JOINs especially this can be completely prevented by defining a foreign key constraint: this forces you to have the same datatype. See MySQL docs which says:
Corresponding columns in the ...
1
Like @a_horse_with_no_name told on his comment, the differences are documented in here, but here is some information:
Size:
datetime - uses 8 bytes for each field
timestamp - uses 4 bytes for each field (half of the size)
Range:
datetime - 1000-01-01 00:00:00 to 9999-12-31 23:59:59
timestamp - 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
...
1
According to the documentation, you can use SET statements to transform the data on the way in.
[SET col_name = expr,...]
The expr expression can include the column name, which will be interpreted as the data being read from the file and destined for that column... so, for example, at the end of your LOAD DATA INFILE statement you might use:
SET ...
1
ANSWER #1
This feels clumsy doing this in Windows but here it goes. If you are able to login to mysql, then run this script
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set SQLSTMT=SELECT CONCAT('REPAIR TABLE ',table_schema,'.',table_name,';')
set SQLSTMT=%SQLSTMT% FROM information_schema.tables WHERE engine='InnoDB'
set MYSQL_CONN=-u%MYSQL_USER% ...
1
The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:
DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo
WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = ...
1
The reason for the discrepancy is obvious.
When you run
SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
(SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='proc') B;
you get
mysql> SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A,
-> (SELECT COUNT(1) MySQLProcColumns FROM ...
Only top voted, non community-wiki answers of a minimum length are eligible
