You are much better off running the ALTER TABLE like this
ALTER TABLE `example` ADD `example_column` TINYINT(1) DEFAULT NULL,
What could be the benefit ? Defining a column whose default value is NULL will not attempt to increase the size of the row. This is true for VARCHAR. Honestly, I'm not sure this is true for numeric. (INT,...
I do not understand why an application requires the MIXED replication format, as MIXED is equal to STATEMENT-based, but changing to ROW format when executing "unsafe" queries. So, a MIXED-compatible application should be compatible with both STATEMENT and ROW.
As you can see here, if the the format detected (server-side) is ROW or MIXED, it tries to change ...
Like many other solutions designed for an almost lockless schema change, pt-online-schema-change works by creating triggers on your table and creating a _new table. As stated in the documentation:
Any modifications to data in the original tables during the copy will
be reflected in the new table, because the tool creates triggers on
the original table ...
InnoDB tables tend to occupy more space than MyISAM tables do.
While converting, new physical files are being created, so you have to have free space on the disk (Hard to estimate, but to be safe I would say 120G)
If you use Percona tools to make the change online, all modifications on the original table will be stored in a temporary table using triggers. ...
pt-online-schema-change takes advantage of the consistency nature of relational databases at table level. Let's see what happens when we write to the original table, one of three cases:
The chunk has already been copied. No problem, the trigger will overwrite the values of the accessory table within the transaction
The chunk has not yet been copied. The ...
A quick 101 on Percona Xtrabackup:
Basically, what the tool does is copying the data live in an inconsistent way, but making sure it gets all the necessary changes that are happening at copy time, so it can revert it to a proper state. In other words, it does an "unclean full copy" and then a "controlled crash recovery" with a custom InnoDB instance. That ...
In the latest Percona XtraDB cluster version it is possible to make WAN segments so that there is more efficient communication between nodes. If you didn't do that, all nodes would try to replicate to all nodes, which is highly inefficient for Galera. With Galera 3/PXC 5.6, you can establish those so that only one node at a time will replicate to the other ...
DISKSPACE FOR EVERYTHING INNODB
SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace
DISKSPACE FOR DATABASE mydb BY TABLE
SELECT table_name tbl,SUM(data_length+...
"Diffs cannot be detected because no slaves were found."
That means that you are using the tool incorrectly -a single run must be done so that it creates checksums that are eventually consistent for the master and the slave.
Run pt-table-checksum only directed to the master, and create the appropriate permissions so that it can check the slave, too. Maybe ...
Unless your production database is mission-critical and restore time is of the utmost importance, I would generally suggest that you wait to run the apply-log until you need the backup.
Advantages of running apply-log immediately after taking a backup:
Your backup is ready for use immediately (with --move-back / --copy-back), saving you precious time in ...
See Reading RBR binary logs with pt-query-digest and LP #1377887: pt-query-digest fails to parse binary log with RBR events
That bug report is over 2 years old and ranked as "low" priority. Suggest you add a comment to clue Percona in that there is interest in getting it fixed.
If you use a dsn table as a recursion method, then pt-online-schema-change can do the job for you. This is documented here:
If you use the hosts method, though, pt-osc will detect that SHOW SLAVE STATUS returns more than 1 row, ...
You are taking a hash of a phone number? Why? Indexing the phone number is arguably better than building a hash and indexing that. For one thing, the phone number is always(?) shorter than the hash.
Kill the ALTER and rethink the task.
The likely reason for the problem is the randomness of the column being indexed -- Toward the end of the process, it ...
pt-online-schema-change requires setting triggers on a table. MySQL (prior to version 5.7) only allows one trigger per event (before insert, after insert etc.) This error is informing you know that a trigger is already defined, and pt-online-schema-change is prevented from running.
tl;dr: You need to remove your triggers so the tool can run. With MySQL 5....
This is what I'd suggest:
run SHOW INDEXES FROM Y.Z\G
use the index with the highest Cardinality in --chunk-index option
use a different hash function --function=md5
use a smaller --chunk-size (eg. 200)
Optionally, run ANALYZE TABLE on the affected table to re-calculate its statistics. Make sure you're aware of the effects of running ANALYZE TABLE on a ...
The message is both expected and disturbing.
WHY EXPECTED ?
I don't find this message disturbing at all. I would expect pt-table-checksum to bypass chunks since there is no defined uniqueness. Granted, InnoDB will create a row-based index called gen_clust_index when there is no PRIMARY KEY or UNIQUE INDEX. So, chunks read from the table have no rhyme or ...
The problem was that every now and then an unrelated query put a lot of stress on the server and was causing the threads running to nearly max out. It was doing this regularly before the schema change was first run.
I think the bug is in your copy of DBD::mysql which seems to have been fixed sometime before version 4.027. Make sure your copy of DBD::mysql is up to date. 4.041 was released in Nov of 2016.
Not sure if the issue is resolved. But as suggested here're some ways you can do this :
1. ALTER TABLE Approach :
ALTER TABLE ENGINE=INNODB;
Considerations for this approach : As you mentioned you have a big table this command will take time to execute.
CREATE TABLE and INSERT INTO Approach :
You can create a empty table with INNODB storage engine and ...
I have an MyISAM2InnoDB blog that covers your question thoroughly.
It discusses index differences, especially PRIMARY KEY and FULLTEXT, space (2x-3x is typical), LOCK TABLES, decreasing key_buffer_size and increasing innodb_buffer_pool_size (very important), etc.