shlomi-noach / openarkkit Goto Github PK

I created a simple table with an autoincrement key, and wrote a procedure to 
insert into it in a loop.  I started this procedure, and then ran 
oak-online-alter-table several times.  It succeeded several times, but then 
sometimes my stored procedure died with the following error, which makes it 
look like there is a race condition in the process of altering the table:

5141> call doinsert(50000);
ERROR 1146 (42S02): Table 'test.__oak_b' doesn't exist

Here is the full output of the tool:

$ python oak-online-alter-table.py --host=127.0.0.1 --database=test --table=b 
--alter="engine=innodb" --user=root --port=5141
-- Connecting to MySQL
-- Table test.b is of engine innodb
-- Checking for UNIQUE columns on test.b, by which to chunk
-- Possible UNIQUE KEY column names in test.b:
-- - a
-- Table test.__oak_b has been created
-- Table test.__oak_b has been altered
-- Checking for UNIQUE columns on test.__oak_b, by which to chunk
-- Possible UNIQUE KEY column names in test.__oak_b:
-- - a
-- Checking for UNIQUE columns on test.b, by which to chunk
-- - Found following possible unique keys:
-- - a (int)
-- Chosen unique key is 'a'
-- Shared columns: a
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- a (min, max) values: ([1L], [2416L])
-- Tables unlocked
-- Copying range (1), (1000), progress: 0%
-- Copying range (1000), (2000), progress: 41%
-- Copying range (2000), (2416), progress: 82%
-- Copying range 100% complete. Number of rows: 2416
-- Deleting range (1), (1000), progress: 0%
-- Deleting range (1000), (2000), progress: 41%
-- Deleting range (2000), (2416), progress: 82%
-- Deleting range 100% complete. Number of rows: 0
-- Table test.b has been renamed to test.__arc_b,
-- and table test.__oak_b has been renamed to test.b
-- Table test.__arc_b was found and dropped
-- ALTER TABLE completed

Do you know what is happening, and can it be solved?

Hi,

how hard would it be to add this option to the tool ?
I would like to create a ghost table, do some work on it while it is still 
getting live stream, and then perform an atomic rename ?

Thanks in advance,
Joffrey

What steps will reproduce the problem?
test@localhost> create table test_mi (id mediumint unsigned not null 
auto_increment primary key, stuff varchar(20) not null) engine innodb;
Query OK, 0 rows affected (0.04 sec)

test@localhost> insert into test_mi (stuff) values ("hello, world"), ("hello 
again");
Query OK, 1 row affected (0.00 sec)

3. oak-online-alter-table -d test -S /tmp/mysql.sock -t test_mi -a 'add column 
morestuff2 varchar(20)'

What is the expected output? What do you see instead?
-- Connecting to MySQL
-- Table test.test_mi is of engine innodb
-- Checking for UNIQUE columns on test.test_mi, by which to chunk
-- Possible UNIQUE KEY column names in test.test_mi:
-- - id
-- Table test.__oak_test_mi has been created
-- Table test.__oak_test_mi has been altered
-- Checking for UNIQUE columns on test.__oak_test_mi, by which to chunk
-- Possible UNIQUE KEY column names in test.__oak_test_mi:
-- - id
-- Checking for UNIQUE columns on test.test_mi, by which to chunk
-- - Found following possible unique keys:
-- - id (mediumint)
-- Chosen unique key is 'id'
-- Shared columns: stuff, id
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- id (min, max) values: ([1L], [2L])
-- Tables unlocked
<type 'exceptions.Exception'> sequence item 0: expected string, long found
-- ERROR: Errors found. Initiating cleanup
-- Tables unlocked
-- Table test.__oak_test_mi was found and dropped
-- Dropped custom trigger test_mi_AD_oak
-- Dropped custom trigger test_mi_AU_oak
-- Dropped custom trigger test_mi_AI_oak
-- ERROR: sequence item 0: expected string, long found

Adding mediumint to the list of integer types checked in 
get_shared_unique_key_columns makes it work.

What version of the product are you using? On what operating system?
  - seen with v180, mysql 5.1.55, on rhel linux 4.8

Please provide any additional information below.

What steps will reproduce the problem?
1. oak-chunk-update --verbose  --database=tipsfora_myimobitrax  --user=root 
--ask-pass  --socket=/var/lib/mysql/mysql.sock  --execute="DELETE FROM 
myimobitrax.mt_click where click_time <1412060400 and OAK_CHUNK(mt_click)"

What is the expected output? What do you see instead?

- Checking for UNIQUE columns on myimobitrax.mt_click, by which to chunk
-- Table locked READ
-- camp_id,click_time,click_id (min, max) values: ([12L, 1404201757L, 
18792867L], [1290L, 1415419358L, 45645222L])
-- Table unlocked
sequence item 0: expected string, long found


What version of the product are you using? On what operating system?
openark-kit-196-1.noarch.rpm (tried 170 and 180 - the same results)
Centos6.6
python 2.6.6

Please provide any additional information below.

Thank you,
Vitaly

columns name in get_table_columns need to be enclosed in backticks

def get_table_columns(read_table_name):
    """
    Return the list of column names (lowercase) for the given table
    """
    query = """
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA='%s'
            AND TABLE_NAME='%s'
        """ % (database_name, read_table_name)
    column_names = set(["`"+row["COLUMN_NAME"].lower()+"`" for row in get_rows(query)])
    return column_names

If you have bin log or replication slave filtering rules that disable binary 
logging or replication replay of a table that you wish to ALTER, you can run 
into problems when trying to use oak-online-alter-table on said table.

The main issue is that binary logging/replication playback of the new copy of 
the table (_oak..etc)  will continue, while the main table will not. This 
causes problems with INSERT.., SELECT.

Theoretically one could add in a binlog/replication filter for the _oak_* table 
in advance, however, this is somewhat invasive, since changing bin log or 
replication filter rules actually requires a MySQL server restart. 

I found the best way to deal with this is to have a flag that lets you tell 
oak-online-alter-table to  SET SQL_LOG_BIN=0 for the duration of the online 
alter operation. This simple patch adds a flag to oak-online-alter-table to 
allow you to do exactly that.

The new option is:

  -N or --skip-binlog

The caveat, of course, is that if you need to recover from a backup and use 
logs to roll forward, your table alter will not be logged and you may need to 
perform it again. This is a problem only for people who bin log everything but 
use replication slave filters to exclude playback for specific tables.

Relevant file: oak-online-alter-table

In the function "get_shared_unique_key_columns", variables "unique_key_type" 
and "unique_key_column_names" are assigned a value at most twice: once to None 
(lines 196, 197), and again inside the if block on line 208. The remaining 
variable in the returned tuple, "count_columns_in_unique_key", is assigned a 
value outside of said if block. It will be assigned a value as many times as 
there are items in the Set "shared_unique_key_column_names_set". For tables 
with multiple unique keys of different column counts, it is possible for 
"count_columns_in_unique_key" to hold the column count for a unique key other 
than the one found in "unique_key_column_names".

This discrepancy can cause an error in the function "get_unique_key_range", 
when attempting to assign N columns to M variables, where N != M.

A diff that corrects this is attached.

-Stephen

What steps will reproduce the problem?
1.
2.
3.

What is the expected output? What do you see instead?


What version of the product are you using? On what operating system?


Please provide any additional information below.

Easily reproducible by attempting to add a column to a table with only a single 
row.

Python will throw an exception about float() requiring a string or number as a 
parameter.

This is caused by the different "ratio_complete_query" queries not handling 
divide by zero errors that will occur when trying to calculate ratio complete.

I provided a fix patch that simply wraps these ratio calculations in 
IFNULL(…, 1) so that it considers "100%" completion ratio if it get's NULL 
returned by the calculation (assumed that was caused by divide by 0)

Run: oak-online-alter-table -h

Output:


[root@mslvldbt01 ~]# oak-online-alter-table -h
usage: oak-online-alter-table [options]

options:
  -h, --help            show this help message and exit
  -u USER, --user=USER  MySQL user
  -H HOST, --host=HOST  MySQL host (default: localhost)
  -p PASSWORD, --password=PASSWORD
                        MySQL password
  --ask-pass            Prompt for password
  -P PORT, --port=PORT  TCP/IP port (default: 3306)
  -S SOCKET, --socket=SOCKET
                        MySQL socket file. Only applies when host is localhost
  --defaults-file=DEFAULTS_FILE
                        Read from MySQL configuration file. Overrides all
                        other options
  -d DATABASE, --database=DATABASE
                        Database name (required unless table is fully
                        qualified)
  -t TABLE, --table=TABLE
                        Table to alter (optionally fully qualified)
  -g GHOST, --ghost=GHOST
                        Table name to serve as ghost. This table will be
                        created and synchronized with the original table
  -a ALTER_STATEMENT, --alter=ALTER_STATEMENT
                        Comma delimited ALTER statement details, excluding the
                        'ALTER TABLE t' itself
  -c CHUNK_SIZE, --chunk-size=CHUNK_SIZE
                        Number of rows to act on in chunks. Default: 1000
  -l, --lock-chunks     Use LOCK TABLES for each chunk
  --sleep=SLEEP_MILLIS  Number of milliseconds to sleep between chunks.
                        Default: 0
  --cleanup             Remove custom triggers, ghost table from possible
                        previous runs
  -v, --verbose         Print user friendly messages
  -q, --quiet           Quiet mode, do not verbose
exceptions.Exception 0
-- ERROR: Errors found. Initiating cleanup
-- ERROR: 0
Traceback (most recent call last):
  File "/usr/bin/oak-online-alter-table", line 957, in ?
    exit_with_error(err)
  File "/usr/bin/oak-online-alter-table", line 856, in exit_with_error
    exit(1)
TypeError: 'str' object is not callable

Python version:

[root@mslvldbt01 ~]# python -V
Python 2.4.3

Note the error output at the end.

Hello,

I need to do a local build of your project with some site-specific default 
values (mysql socket path, etc.).

Could you please upload the RPM spec file?

Thanks,
Jason Antman

I was installed openark-kit-196-1.noarch.rpm . The command ouput got error:

# oak-show-replication-status -S /dev/shm/mysql_m.sock 
-- master log: mysql-bin.000033
-- Slave host   Slave port      Master_Log_File Seconds_Behind_Master   Status 
-- ERROR: Cannot SHOW SLAVE STATUS on :49715

how about this ?

What steps will reproduce the problem?
1. I am getting error that say "No module named MySQLdb" while altering table
Here is the screenshot:

# oak-online-alter-table --socket=/var/lib/mysql/mysql.sock --database=test
--table=ahmdabad --alter="ADD KEY(City)"
Traceback (most recent call last):
  File "/usr/bin/oak-online-alter-table", line 21, in ?
    import MySQLdb
ImportError: No module named MySQLdb


What is the expected output? What do you see instead?


What version of the product are you using? On what operating system?
I am using RHEL OS.

Please provide any additional information below.

I'm having trouble running this, and it seems that there are either some 
differences in my Python installation that it doesn't handle gracefully, or 
that it's finding an error it doesn't know how to handle.  I am not experienced 
enough with Python to have an opinion.  It always seems to exit with this, no 
matter what error it encounters (e.g. cannot log into MySQL):

-- ERROR: 'str' object is not callable
Traceback (most recent call last):
  File "/usr/bin/oak-online-alter-table", line 954, in ?
    exit_with_error(err)
  File "/usr/bin/oak-online-alter-table", line 853, in exit_with_error
    exit(1)
TypeError: 'str' object is not callable

Here is the full information:

CREATE TABLE `b` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

$ oak-online-alter-table --host=127.0.0.1 --database=test --table=b 
--alter="engine=innodb" --user=root --port=5141
-- Connecting to MySQL
-- Table test.b is of engine myisam
-- Checking for UNIQUE columns on test.b, by which to chunk
-- Possible UNIQUE KEY column names in test.b:
-- - a
-- Table test.__oak_b has been created
-- Table test.__oak_b has been altered
-- Checking for UNIQUE columns on test.__oak_b, by which to chunk
-- Possible UNIQUE KEY column names in test.__oak_b:
-- - a
-- Checking for UNIQUE columns on test.b, by which to chunk
-- - Found following possible unique keys:
-- - a (int)
-- Chosen unique key is 'a'
-- Shared columns: a
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- a (min, max) values: ([1L], [9990972L])
-- Tables unlocked
exceptions.Exception sequence item 0: expected string, long found
-- ERROR: Errors found. Initiating cleanup
-- Tables unlocked
-- Table test.__oak_b was found and dropped
-- Dropped custom trigger b_AD_oak
-- Dropped custom trigger b_AU_oak
-- Dropped custom trigger b_AI_oak
-- ERROR: sequence item 0: expected string, long found
Traceback (most recent call last):
  File "/usr/bin/oak-online-alter-table", line 954, in ?
    exit_with_error(err)
  File "/usr/bin/oak-online-alter-table", line 853, in exit_with_error
    exit(1)
TypeError: 'str' object is not callable

What steps will reproduce the problem?
1. No problem just a design improvement?

What is the expected output? What do you see instead?
no difference

What version of the product are you using? On what operating system?
openark-kit-170

Please provide any additional information below.

A trigger is used:
        CREATE TRIGGER %s.%s AFTER UPDATE ON %s.%s
        FOR EACH ROW
        BEGIN
            DELETE FROM %s.%s WHERE (%s) = (%s);
            REPLACE INTO %s.%s (%s) VALUES (%s);
        END

Imho there is no need for the DELETE

Regards
Erkan

oak-online-alter-table
line:929 

exit_with_error("Aletered table must have a UNIQUE KEY on a single column")

I think the word Aletered is a misspell

DELETE FROM %s.%s WHERE (%s) = (%s);

The same WHERE clause works for SELECT case, hitting index, but not for DELETE.

http://bugs.mysql.com/bug.php?id=43187
According to the age of the issue, I don't think it would be fixed soon:)

Rewriting WHERE clause to "col1 = value1, col2 = value2, ..." does help.

What steps will reproduce the problem?
1. run this oak-security-audit --defaults-file=~myuser/.my.oak.cnf 
--audit-level=strict
2. exits with this error 'users.user' isn't in GROUP BY at the Looking for 
accounts with identical (non empty) passwords
3. On percona 5.6 server

What is the expected output? What do you see instead?
Get to this:
-- Looking for accounts with identical (non empty) passwords
-- ---------------------------------------------------------
'users.user' isn't in GROUP BY


What version of the product are you using? On what operating system?
downloaded version 1.0.5 but that was not displayed in the --help info.
Ubuntu wheezy (64 bit)

Please provide any additional information below.

What steps will reproduce the problem?
1. run: oak-chunk-update --defaults-file=/home/user/.my.cnf 
--host=xxx.xxx.xxx.xxx ...

What is the expected output? What do you see instead?
script try to connect to localhost, no take care about host option and per 
documentation must do that

What version of the product are you using? On what operating system?
oak-chunk-update any not OS related

Please provide any additional information below.
def open_connection():
    if options.defaults_file:
        conn = MySQLdb.connect(
            read_default_file = options.defaults_file,
            db = database_name)

host option should be there too

What steps will reproduce the problem?
1. Run the following command (taken from the documentation)
oak-online-alter-table --database=world --table=City
2. Fails with the following error
<type 'exceptions.Exception'> 'NoneType' object has no attribute '__getitem__'

What is the expected output? What do you see instead?
The command is documented to work when you do not specify the alter option, it 
should rebuild the table. The command fails and displays an error.

What version of the product are you using? On what operating system?
Build 196 running on Python 2.7.8 on Windows 7

Please provide any additional information below.
This appears to be a bug introduced in build 195 on line 759
verbose("- Reminder: altering %s.%s: %s..." % (database_name, 
original_table_name, options.alter_statement[0:30]))
If the alter option is not specified then this line causes an error.

A workaround is to specify an empty alter option, so the command from the 
documentation becomes
oak-online-alter-table --database=world --table=City --alter=""

Here's the relevant output:

-- Connecting to MySQL
-- Table X.X is of engine innodb
-- Checking for UNIQUE columns on X.X, by which to chunk
-- Possible UNIQUE KEY column names in X.X:
-- - id
-- Table X.__oak_X has been created
-- Table X.__oak_X has been altered
-- Checking for UNIQUE columns on X.__oak_X, by which to chunk
-- Possible UNIQUE KEY column names in X.__oak_notes:
-- - id
-- Checking for UNIQUE columns on X.X, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: a, b, c, d, e, f, g, h, i, j, k, id, l, m
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables

-- Tables locked WRITE
-- id (min, max) values: ([1L], [1376476L])
-- Tables unlocked
-- Copying range (1), (1134), progress: 0%
...
-- Copying range (1376475), (1376475), progress: 100%
-- Copying range (1376475), (1376475), progress: 100%
-- Copying range (1376475), (1376475), progress: 100%
-- Copying range (1376475), (1376475), progress: 100%
-- Copying range (1376475), (1376475), progress: 100%
-- Copying range (1376475), (1376475), progress: 100%
-- Copying range (1376475), (1376475), progress: 100%

At that point I aborted the operation after a minute or so of it not doing 
anything useful.


What version of the product are you using? On what operating system?

openarkkit: latest at the time of writing, 180
OS: Ubuntu 10.04.1 LTS
Server version: 5.1.66-0ubuntu0.10.04.1-log (Ubuntu)



I'm starting to work on reproducing/debugging it, but this output may be 
sufficient for someone who knows this code.

OS: CentOS release 5.4 (Final)
python: python-2.4.3-27.el5
MySQL-python: MySQL-python-1.2.1-1

[root@localhost]# oak-online-alter-table
-- ERROR: Errors found. Initiating cleanup
-- ERROR: No table specified. Specify with -t or --table
exceptions.Exception 'str' object is not callable
-- ERROR: Errors found. Initiating cleanup
-- ERROR: 'str' object is not callable
Traceback (most recent call last):
  File "/usr/local/bin/oak-online-alter-table", line 957, in ?
    exit_with_error(err)
  File "/usr/local/bin/oak-online-alter-table", line 856, in exit_with_error
    exit(1)
TypeError: 'str' object is not callable

After googling, the error above, which could be harmless seems could be fixed 
using sys.exit() instead of exit() around line 856.

I am trying to use oak-online-alter-table to rebuild a table.  I am getting the 
error:
ERROR: 'NoneType' object is unsubscriptable

this is my schema:
CREATE TABLE `zendeskulator_events` (
  `id` bigint(12) NOT NULL,
  `ticket_id` int(12) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `type` enum('CHANGE','CREATE') DEFAULT NULL,
  `body` text,
  `author_id` int(11) DEFAULT NULL,
  `field_name` varchar(45) DEFAULT NULL,
  `previous_value` text,
  `value` text,
  `desk` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`),
  KEY `field_name` (`field_name`),
  KEY `ticket_id` (`ticket_id`),
  KEY `type` (`type`),
  KEY `author_id` (`author_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Any advice?

What steps will reproduce the problem?
1. I am executing this command after installing 
[root@rhel scripts-2.3]# oak-show-limits -H 192.168.1.5 -u root -p root -P
3306 -d bulk

I am getting this error,

Traceback (most recent call last):
  File "/usr/bin/oak-show-limits", line 21, in ?
    import MySQLdb
  File "/usr/lib/python2.3/site-packages/MySQLdb/__init__.py", line 19, in ?
    import _mysql
ImportError: libmysqlclient_r.so.14: cannot open shared object file: No
such file or directory

What is the expected output? What do you see instead?


What version of the product are you using? On what operating system?
I am using 2.3, 

[root@rhel openark-kit-84]# uname -a
Linux rhel.mydomain 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT 2007 i686
i686 i386 GNU/Linux

[root@rhel openark-kit-84]# vi /etc/*release
Red Hat Enterprise Linux AS release 4 (Nahant Update 5)

Please provide any additional information below.
Am I missing anything here?

What steps will reproduce the problem?

oak-online-alter-table --database=test_bd --table=test --alter="ADD COLUMN 
created DATETIME NULL DEFAULT NULL AFTER modified" --
socket=/var/lib/mysql/mysqld.sock

-- Connecting to MySQL
-- Table test_bd.test is of engine innodb
-- Checking for UNIQUE columns on test_bd.test, by which to chunk
-- Possible UNIQUE KEY column names in test_bd.test:
-- - id
-- Table test_bd.__oak_test has been created
exceptions.Exception (1054, "Unknown column 'modified' in '__oak_test'")
-- ERROR: Errors found. Initiating cleanup
-- Tables unlocked
-- Table test_bd.__oak_test was found and dropped
-- ERROR: (1054, "Unknown column 'modified' in '__oak_test'")
Traceback (most recent call last):
  File "scripts/oak-online-alter-table", line 954, in ?
    exit_with_error(err)
  File "scripts/oak-online-alter-table", line 853, in exit_with_error
    exit(1)
TypeError: 'str' object is not callable

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24979 DEFAULT CHARSET=latin1;

Thank you,

Jean-Christophe Petit

What steps will reproduce the problem?

1- mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 99
Server version: 5.1.54-1ubuntu4-log (Ubuntu)

mysql> use test;
Database changed

mysql> create table table1 (`key` int auto_increment primary key);
Query OK, 0 rows affected (0.10 sec)
mysql> ^Z

$ oak-show-limits --user=root --ask-pass --socket /var/run/mysqld/mysqld.sock 
Password: 
-- ERROR: Error reading test.table1.key
(1064, "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 'key) AS 
max_value FROM `test`.`table1`' at line 1")

What is the expected output? What do you see instead?
It shouldn't output at all because the because test.table1.key is not close to 
it's limits. The error that's reporting is a failed query because I used a 
reserved word (key) as a column name.

What version of the product are you using? On what operating system?
$ dpkg -l|grep -i ark
ii  openark-kit                           180-1                                 
     Common utilities for MySQL

on ubuntu 11.04

Please provide any additional information below.
that's the query that's failing:

"SELECT MAX(%s) AS max_value FROM `%s`.`%s`"

it should be escaping the column_name as well. 

Find attached a patch (got the source from trunk revision 182)

PS. I know I shouldn't be using reserved words as column names...

Posting in name of user:
add a feature for oak-block-account which lists the currently 
blocked/accessible accounts.

Not sure if I'm doing something wrong or there is an actual issue.

mysql --version
mysql  Ver 14.14 Distrib 5.1.56, for redhat-linux-gnu (x86_64) using readline 
5.1

rpm -qa | grep openark
openark-kit-180-1

oak-online-alter-table --database=teamlab --table=activities --alter="ADD 
KEY(thing_id, thing_type, contact_id)" --user=root --ask-pass 
--socket=/var/lib/mysql/mysql.sock --chunk-size=5000 --sleep=1000 --verbose
-- Connecting to MySQL
Password: 
-- Table teamlab.activities is of engine innodb
-- Checking for UNIQUE columns on teamlab.activities, by which to chunk
-- Possible UNIQUE KEY column names in teamlab.activities:
-- - id
-- Table teamlab.__oak_activities has been created
-- Table teamlab.__oak_activities has been altered
-- Checking for UNIQUE columns on teamlab.__oak_activities, by which to chunk
-- Possible UNIQUE KEY column names in teamlab.__oak_activities:
-- - id
-- Checking for UNIQUE columns on teamlab.activities, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: thing_type, user_id, email_id, updated_field, created_at, 
after, thing_id, updated_at, contact_id, action, mongo_contact_id, 
college_team_id, id, before
-- Created AD trigger
exceptions.Exception (1064, "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 'before) VALUES (NEW.thing_type, NEW.user_id, NEW.email_id, 
NEW.updated_field, NE' at line 5")
-- ERROR: Errors found. Initiating cleanup
-- Tables unlocked
-- Table teamlab.__oak_activities was found and dropped
-- Dropped custom trigger activities_AD_oak
-- ERROR: (1064, "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 
'before) VALUES (NEW.thing_type, NEW.user_id, NEW.email_id, NEW.updated_field, 
NE' at line 5")
Traceback (most recent call last):
  File "/usr/local/bin/oak-online-alter-table", line 957, in ?
    exit_with_error(err)
  File "/usr/local/bin/oak-online-alter-table", line 856, in exit_with_error
    exit(1)
TypeError: 'str' object is not callable

No ‘AFTER’ triggers are defined on the table (the utility creates its own 
triggers for the duration of the operation)

The description misled me that the tool moves other triggers except AFTER ones 
:)
I have tables with BEFORE triggers to alter, so I wrote a patch. I'll be very 
happy if you guys also think it useful and merge it.

It has been not supported officially something like "alter table partition ..." 
at present.
I tried it but failed.

BEFORE:
CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

AFTER:
CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`last_updated`)
) PARTITION BY RANGE ( UNIX_TIMESTAMP( last_updated ) ) ...;

I'd like to create partitions using column `last_updated`, so I have to alter 
the primary key which would cause no shared unique key to use by 
oak-online-alter-table.
However, in some cases ( at least in my case ), the original pk column `id` is 
still a unique column logically.

I did a dirty hack to achieve my need.

$ diff /usr/local/openark-kit-180/bin/oak-online-alter-table 
/usr/local/src/openark-kit-180/build/scripts-2.6/oak-online-alter-table
46d45
<     parser.add_option("--unique-key-column-name", 
dest="unique_key_column_name")
181,182d179
<
<     
possible_unique_key_column_names_set.append(options.unique_key_column_name)

Would you mind to add such a option to support the feature?

When running the following, it never locks the tables. There isn't any activity 
in show full processlist from the oak process nor is there activity on the 
messages table. I've tried with the latest release version and the version 
attached to issue #30

oak-online-alter-table --database=captainu --table=messages --alter="ADD 
KEY(from_email, from_id, from_type)" --user=root --ask-pass 
--socket=/var/lib/mysql/mysql.sock --chunk-size=5000 --sleep=1000
-- Connecting to MySQL
Password: 
-- Table captainu.messages is of engine innodb
-- Checking for UNIQUE columns on captainu.messages, by which to chunk
-- Possible UNIQUE KEY column names in captainu.messages:
-- - id
-- Table captainu.__oak_messages has been created
-- Table captainu.__oak_messages has been altered
-- Checking for UNIQUE columns on captainu.__oak_messages, by which to chunk
-- Possible UNIQUE KEY column names in captainu.__oak_messages:
-- - id
-- Checking for UNIQUE columns on captainu.messages, by which to chunk
-- - Found following possible unique keys:
-- - id (int)
-- Chosen unique key is 'id'
-- Shared columns: body, created_at, to_email, reply_to_id, updated_at, 
login_token, subject, archived, from_type, from_email, to_id, to_type, token, 
flags, most_likely_college_team_id, athlete_id, id, bounced, message_id, from_id
-- Created AD trigger
-- Created AU trigger
-- Created AI trigger
-- Attempting to lock tables
-- ...
-- ...
-- ...
-- ...
-- ...

An account with an empty password is not handled correctly.

What steps will reproduce the problem?
I was simply running an oak-online-alter-table with my development database.  
My schema may be a little out of the ordinary, but they script would crash 
with: 

<type 'exceptions.Exception'> sequence item 1: expected string, long found

I pulled from latest, currently at revision 188.

The solution is simple:

replace line 746:
verbose("%s range (%s), (%s), progress: N/A" % (description, 
",".join(unique_key_range_start_values), ",".join(unique_key_range_end_values)))

with:
verbose("%s range (%s), (%s), progress: N/A" % (description, 
to_string_list(unique_key_range_start_values), 
to_string_list(unique_key_range_end_values)))

What steps will reproduce the problem?

# For Ubuntu
cd /tmp
wget http://openarkkit.googlecode.com/files/openark-kit-180-1.deb
sudo apt-get install python-mysqldb
sudo dpkg -i openark-kit-180-1.deb

# oak-show-replication-status
cd $HOME/sandboxes/rsandbox_5_5_24
oak-show-replication-status --defaults-file=master/my.sandbox.cnf


What is the expected output? What do you see instead?
Got: 
int() argument must be a string or a number, not 'list'

Expected:

-- master log: mysql-bin.000003
-- Slave host   Slave port  Master_Log_File Seconds_Behind_Master   Status
-- ERROR: Cannot SHOW SLAVE STATUS on SBslave1:21380
-- ERROR: Cannot SHOW SLAVE STATUS on SBslave2:21381


What version of the product are you using? On what operating system?

OpenArk 180
Ubuntu 12.04
MySQL 5.5.24
MySQL Sandbox

Please provide any additional information below.

On a different system this seemed to work.

The cleanup() method drops the ghost table before dropping the custom triggers. 
For a short moment, this leaves the database in a state where triggers on the 
original table fail along with queries that triggered them because the oak__ 
table doesn't exist anymore. I believe it would be safer to drop the triggers 
first.

What steps will reproduce the problem?
1. Run an online alter table

What is the expected output? What do you see instead?

-- Attempting to lock tables

-- Tables locked WRITE
-- id (min, max) values: ([1L], [66514986L])
-- Tables unlocked
exceptions.Exception sequence item 0: expected string, long found
-- ERROR: Errors found. Initiating cleanup
-- Tables unlocked

What version of the product are you using? On what operating system?

openark-kit-170-1.noarch.rpm on CentOS 5.4

Please provide any additional information below.

MySQL 5.5.8, python 2.4.3, MySQL-python.x86_64 1.2.1-1 

$ python -V
Python 2.4.3


$ oak-online-alter-table --help
usage: oak-online-alter-table [options]

options:
  -h, --help            show this help message and exit
  -u USER, --user=USER  MySQL user
  -H HOST, --host=HOST  MySQL host (default: localhost)
  -p PASSWORD, --password=PASSWORD
                        MySQL password
  --ask-pass            Prompt for password
  -P PORT, --port=PORT  TCP/IP port (default: 3306)
  -S SOCKET, --socket=SOCKET
                        MySQL socket file. Only applies when host is localhost
  --defaults-file=DEFAULTS_FILE
                        Read from MySQL configuration file. Overrides all
                        other options
  -d DATABASE, --database=DATABASE
                        Database name (required unless table is fully
                        qualified)
  -t TABLE, --table=TABLE
                        Table to alter (optionally fully qualified)
  -g GHOST, --ghost=GHOST
                        Table name to serve as ghost. This table will be
                        created and synchronized with the original table
  -a ALTER_STATEMENT, --alter=ALTER_STATEMENT
                        Comma delimited ALTER statement details, excluding the
                        'ALTER TABLE t' itself
  -c CHUNK_SIZE, --chunk-size=CHUNK_SIZE
                        Number of rows to act on in chunks. Default: 1000
  -l, --lock-chunks     Use LOCK TABLES for each chunk
  --sleep=SLEEP_MILLIS  Number of milliseconds to sleep between chunks.
                        Default: 0
  --cleanup             Remove custom triggers, ghost table from possible
                        previous runs
  -v, --verbose         Print user friendly messages
  -q, --quiet           Quiet mode, do not verbose
exceptions.Exception 0
-- ERROR: Errors found. Initiating cleanup
-- ERROR: 0
Traceback (most recent call last):
  File "/usr/local/bin/oak-online-alter-table", line 954, in ?
    exit_with_error(err)
  File "/usr/local/bin/oak-online-alter-table", line 853, in exit_with_error
    exit(1)
TypeError: 'str' object is not callable


$ python
Python 2.4.3 (#1, Sep  3 2009, 15:37:37)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-46)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> exit
'Use Ctrl-D (i.e. EOF) to exit.'
>>> exit(1)
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
TypeError: 'str' object is not callable


$ python
Python 2.4.3 (#1, Sep  3 2009, 15:37:37)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-46)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sys
>>> sys.exit
<built-in function exit>
>>> sys.exit(1)

Quoting mail sent to Shlomi by Lachlan M.:

We have some very large tables that we needed to upgrade the
AUTO_INCREMENT id field from UNSIGNED INT to UNSIGNED BIGINT -- during
the process of trying to perform this upgrade we hit some frustrating
issues...

If the online alter tool encountered an InnoDB deadlock or
lock_wait_timeout situation, it would treat the error as a "hard
error" and fail the whole table rebuild, rather than retry some
predetermined number of times...

This was painful for us, since we were in an emergency situation where
we got caught out and had to upgrade the table ASAP in order to get
service working for customers again. The tables were very large, so
some of them actually failed after 10-12 hours...

I'd guess this could be a problem for anyone really -- trying to do
such things with large somewhat active tables and failing after many
hours will be an issue anyone would want to avoid.

My patch adds a new parameter called --max-lock-retries (-r) which
defaults to 10. It will catch and retry any query that passes through
the act_query() function that fails as a result of a DEADLOCK or LOCK
WAIT TIMEOUT.

In addition, I made a secondary optional parameter to the act_query
function that lets you specify whether you want it to retry forever
(rather than up to the max-lock-retries). Then I moved the
lock_tables_write() function to simply specify that parameter and
removed the exception catching and retry logic from that function.

I've attached my patched version of oak-online-alter-table script,
which was based on the latest script from the openark-kit-180-1 RPM.

What steps will reproduce the problem?

1.  When running "select * from db\G" you will notice on a default installation 
there are two blank user entries:
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User: 
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User: 

2.  lets say I have two users with two different databases that start with 
"test_".  Both users will be able to see the others database with these 
defaults in place.

3.  Here is the test:
-- as root
create database test_user1;
create database test_user2;
grant select on `test_user1`.* to 'user1'@'localhost';
grant select on `test_user2`.* to 'user2'@'localhost'; 

-- as user1
mysql -uuser1

[Wed Apr 13 14:33:35 2011] (user1@localhost) [(none)]> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| test_user1         |
| test_user2         | <-- user1 should NOT be able to see this database
+--------------------+
4 rows in set (0.00 sec)

[Wed Apr 13 14:33:40 2011] (user1@localhost) [(none)]> show grants; 
+-------------------------------------------------------+
| Grants for user1@localhost                            |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost'             |
| GRANT SELECT ON `test_user1`.* TO 'user1'@'localhost' |
+-------------------------------------------------------+

What version of the secure are you using?
This was from the MySQL Conference

On what operating system?
Any OS, any version of MySQL.

Please provide any additional information below.

Please let me know if you need anything else.

best,

Chris Schneider

when runing the folowing command the system freeze:

python /usr/local/bin/oak-chunk-update --socket=/var/lib/mysql/mysql.sock 
--database=XXX--port=3306 -v --user=root --pass=xxxx --execute="insert into 
users (user_id, guid, se
    select tu.user_id, tu.name, tu.session_id, tu.subno, tu.base_url, tud.client_app, tud.msisdn, tud.msisdn_verify
        from tmp_users tu, tmp_user_details tud where tu.user_id = tud.user WHERE OAK_CHUNK(tmp_users);"
-- Checking for UNIQUE columns on UMA.tmp_users, by which to chunk
-- Table locked READ
-- user_id (min, max) values: ([1L], [12426L])
-- Table unlocked
-- Performing chunks range (1), (1000), progress: 0%



What is the expected output? What do you see instead?
expected output should be that the data is copied from one table to another


What version of the product are you using? On what operating system?
LSB 
Version:    core-2.0-noarch:core-3.2-noarch:core-4.0-noarch:core-2.0-x86_64:core-3.
2-x86_64:core-4.0-x86_64:desktop-4.0-amd64:desktop-4.0-noarch:graphics-2.0-amd64
:graphics-2.0-noarch:graphics-3.2-amd64:graphics-3.2-noarch:graphics-4.0-amd64:g
raphics-4.0-noarch
Distributor ID: SUSE LINUX
Description:    SUSE Linux Enterprise Server 11 (x86_64)
Release:    11
Codename:   n/a

MySQL-server-community-5.1.58-1.sles11
python-mysql-1.2.2-2.12
python-2.6.0-8.7

Please provide any additional information below.