maria-developers team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

Hi, Nirbhay!

On Nov 29, nirbhay@xxxxxxxxxxx wrote:
> revision-id: 020e569dc7f638b6add8a043b1c9ff308f688ffa
> parent(s): 7b55b67de5fdfe91283357fe6c7ccc3f9e355925
> committer: Nirbhay Choubey
> branch nick: b4412
> timestamp: 2014-11-29 19:05:32 -0500
> message:
> 
> MDEV-4412 : SLOW QUERY LOG - add affected rows (UPDATE / DELETE) in slow query log
> 
> Added Rows_affected to slow query log & mysql.slow_log table.
> 
> diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
> index b9bb59c..aeaf919 100644
> --- a/scripts/mysql_system_tables_fix.sql
> +++ b/scripts/mysql_system_tables_fix.sql
> @@ -249,7 +249,8 @@ SET GLOBAL general_log = @old_log_state;
>  SET @old_log_state = @@global.slow_query_log;
>  SET GLOBAL slow_query_log = 'OFF';
>  ALTER TABLE slow_log
> -  ADD COLUMN thread_id BIGINT(21) UNSIGNED NOT NULL AFTER sql_text;
> +  ADD COLUMN thread_id BIGINT(21) UNSIGNED NOT NULL AFTER sql_text,
> +  ADD COLUMN rows_affected INTEGER NOT NULL AFTER thread_id;
>  ALTER TABLE slow_log
>    MODIFY start_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
>    MODIFY user_host MEDIUMTEXT NOT NULL,

I am not sure it'll work.

It would work if you need to upgrade a table that has neither thread_id
nor rows_affected column to the latest version. So that ALTER would've
been an acceptable solution if both thread_id and rows_affected were
added in the same MariaDB version.

But they were not and there are quite a few MariaDB releases where
slow_log table has thread_id and doesn't have rows_affected.

I suspect in that case ALTER will fail. But if you'd like you can try a
small test case and see whether such an ALTER will add a second column
even if the first one already exists.

Better add rows_affected in a separate ALTER TABLE statement.

Regards,
Sergei