Getting last months data with SQL

 BETWEEN date_format(NOW() – INTERVAL 1 MONTH, ‘%Y-%m-01’) AND last_day(NOW() – INTERVAL 1 MONTH) 

Advertisements
Getting last months data with SQL

Slave problems migrating MySQL 5.5.6 to 5.5.7

When upgrading some servers from 5.5.6 to 5.5.7 I got the following error on the slaves.

Last_Error: Error ‘You cannot ‘ALTER’ a log table if logging is enabled’ on query. Default database: ‘mysql’. Query: ‘ALTER TABLE slow_log


MODIFY start_time TIMESTAMP NOT NULL,
MODIFY user_host MEDIUMTEXT NOT NULL,
MODIFY query_time TIME NOT NULL,
MODIFY lock_time TIME NOT NULL,
MODIFY rows_sent INTEGER NOT NULL,
MODIFY rows_examined INTEGER NOT NULL
MODIFY db VARCHAR(512) NOT NULL,
MODIFY last_insert_id INTEGER NOT NULL,
MODIFY insert_id INTEGER NOT NULL,
MODIFY server_id INTEGER UNSIGNED NOT NULL,
MODIFY sql_text MEDIUMTEXT NOT NULL’

I got around this by doing the following

set set global slow_query_log = 0;
slave start;
set global slow_query_log = 1;

As I had updated the slaves first I also needed to run

alter table mysql.user drop column plugin;
alter table mysql.user drop column authentication_string;

on the slave as when I updated the master it tried to readd the columns

Slave problems migrating MySQL 5.5.6 to 5.5.7