Hello,
Another division in my organization is maintaining a mysql database. For
various reasons, data is deleted from this database after it has aged
more than a month. My division has a need for long term storage of the
same data, so I am trying the following strategy:
1. Create a snapshot ("slave") of the master database.
2. Enable binary logging on the master database.
3. Periodically, run the binary logs through the mysqlbinlog utility to
produce SQL text.
4. Parse the SQL text to remove DELETE's of aged data.
5. Apply the SQL text to the snapshot (slave).
Now data older than one month is being saved in the slave.
When I came to step 5, I submitted the SQL text file as a batch job:
mysql -u root -p -e "source binlogs_045_052_parsed"
After many hours I received the following error message:
ERROR 1036 (HY000) at line 1387471 in file: 'binlogs_045_052_parsed':
Table 'AB4539p2' is read only
At this point, the mysql batch job terminated.
Has anyone come across a similar error under similar circumstances?
Some background:
I am running mysql 5.0 on a Windows 2003 server. The SQL text file,
binlogs_045_052_parsed, contains only three kinds of statements:
millions of LOAD DATA LOCAL INFILEs, a handful of DROP TABLEs, and a few
CREATE TABLEs. The LOAD DATA LOCAL INFILEs are doing inserts into about
5000 tables in the database. Before I received the error, many inserts
into table 'AB4539p2' succeeded without any error.
I have a few more basic questions:
1. Will all the LOAD DATA LOCAL INFILES submitted before the error have
been committed, or will all the statements from the batch job have
rolled back once the error occurred? (I want to know if I need to
resubmit all of the file binlogs_045_052_parsed to mysql, or just the
portion from the error.)
2. I notice that mysqlbinlog puts ROLLBACK statements at the beginning
and end of the SQL text file. I don't understand the purpose of these
ROLLBACKs.
Thank you,
Eric
