I have 2 MySQL servers with row-based replication between them. Both of them are masters and slaves for each other (active-active master-master setup). If I understand it correctly pt-osc creates triggers to catch any changes while running. But from what I know triggers are not fired in a row-based replication environment. So I guess pt-osc is not able to catch changes made on the second master during the change, is it? EDIT: While doing some tests and I saw that pt-osc was creating triggers on both masters which would cover changes from both sides. Still I'm quite unsure if I can safely do online changes in this environment.

I am looking at the option called "alter-foreign-keys-method " in the pt-online-schema-change tool where it uses the drop_swap. In the documentation, it says there are 2 risks: > First, for a short time between dropping the original table and renaming the temporary table, the table to be altered simply does not exist, and queries against it will result in an error. Secondly, if there is an error and the new table cannot be renamed into the place of the old one, then it is too late to abort, because the old table is gone permanently. What I am trying to understand is the second risk where the renaming of the table will fail. According to the MySQL documentation, >RENAME TABLE tbl_name TO new_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string “tbl_name_ibfk_” to reflect the new table name. InnoDB interprets foreign key constraint names that begin with the string “tbl_name_ibfk_” as internally generated names. > Foreign key constraint names that point to the renamed table are automatically updated unless there is a conflict, in which case the statement fails with an error. A conflict occurs if the renamed constraint name already exists. In such cases, you must drop and re-create the foreign keys for them to function properly. Would this be the only scenario where the rename table will fail? Because I think I can just write a plugin or a script to make sure that no constraint named "tbl_name_ibfk_" exists before pt-online-schema-change tool runs.

MySQL version : Percona Server for MySQL 5.7.40-43-log We have Master-Master(Active-Passive) Replication setup in our cluster. Only one Master(Active) is receiving the requests for writes while other one is Passive. Each Master then have 4 Replicas to it. There is bi-directional replica going on between Active and Passive Master. Now, I need to perform an ALTER to a huge table that has ~600M rows and the table size is around : 250G. All the replicas including the Masters(Since they are replicas for each other anyways) have the following flag for replication : **Replicate_Wild_Do_Table as data%.%** so I am not concerned about the _new, _old table creation while actual execution of the pt-osc because **all the tables(%)** are included in replication. Before running the pt-osc on the production, I would like to test it on a staging cluster so I have created a cluster like A --> B --> C where A is like my Active Master, B is my Passive Master and C is my Replica pointing to B. Please note that I have not set up Master-Master replication between A B. My main concern is with the replicas that are present in the production cluster as in while doing it on the Master, there shouldn't be any issues with the replication and the replicas should also have the updated change to the table. I have used the following flags in the pt-osc statement but I am getting error with recurse & recursion-method pt-online-schema-change --alter "ADD COLUMN Cost decimal(18,9) NOT NULL DEFAULT '0.000000000'" \ --alter-foreign-keys-method=auto \ --check-slave-lag=50 \ --max-lag=50 \ --recurse \ --recursion-method=processlist \ --host=replica-015 \ --max-load=Threads_running=30 \ --critical-load=Threads_running=50 \ --chunk-time=0.5 \ --slave-user=repl \ --slave-password='xxxxxxx' \ --chunk-size=10000 \ --progress percentage,1 \ --dry-run \ --user=test \ --password='xxxxxxx' \ D=avon,t=excel I tried processlist, hosts but that didn't worked and I got the following error : I updated the pt-osc command and used --recurse=1 and --recursion-method=processlist and the message is gone but I don't completely get it as to what recurse or recursion-method did here since in the out I don't see any slaves detected vs in some other output I saw online that says no slave detected. I not completely sure about the DSN table which is mentioned in the percona documentation : https://docs.percona.com/percona-toolkit/pt-online-schema-change.html Can someone help me here in understanding the replication related flag in pt-osc command and how should I proceed with the --dry-run or --execute in the staging cluster environment that I have created. Thank you in advance.

Lost some 430 rows from the original table. I was archiving 1 year old data (1st Apr 2022 to 31st Mar 2023) from one of my servers, (Server version: 5.7.44-log MySQL Community Server (GPL)) using pt-archiver (percona-toolkit-3.0.13-1.el7.x86_64). Because the server choked, because of less resources and the Linux machine was not responding and swapping a lot, I had to cancel pt-archiver in the middle after a lot of attempts to take a shell session. I waited for good 15 minutes or so and then had to kill the mysqld process, shutdown the EC2 machine, increase the hardware and then reinitiate the pt-archiver tool. The mysql server had innodb_flush_log_at_trx_commit=1 sync_binlog = 1 I was thinking that above config would save my data even if I cancel pt-archiver in the middle, but it doesn't seem so. SO before I initiated the archival, I had taken a count of rows in the said range and after archival, I had checked the count and it seems 430 rows are missing. Following are the command I used mysql --login-path=archiver --skip-column-names --silent SOURCE_DB -e "select count(*) from SOURCE_DB.SOURCE_TABLE where crt_tm between date_format('2022-04-01 00:00:00','%Y-%m-%d 00:00:00') and date_format('2023-03-31 23:59:59','%Y-%m-%d 00:00:00')" &>> pt-archiver-LOGFILE.log Here it showed 21713301 rows pt-archiver --source h=SOURCE_HOST,P=PORT,u=USER,p=PASS,D=SOURCE_DB,t=SOURCE_TABLE,i=idx_crt_tm --dest h=DEST_HOST,P=PORT,u=USER,p=PASS,D=ARCHIVE_DB,t=ARCHIVE_TABLE --where "crt_tm in (select crt_tm from (select crt_tm from SOURCE_TABLE where crt_tm between date_format('2022-04-01 00:00:00','%Y-%m-%d 00:00:00') and date_format('2023-03-31 23:59:59','%Y-%m-%d 00:00:00')) AS temp) " --charset=UTF8 --sleep 5 --progress 10000 --limit=5000 --txn-size 5000 --replace --bulk-insert --bulk-delete --statistics --purge &>> ${LOGFILE} **When I checked the SOURCE_TABLE, I have zero rows in that date range. and the final count of rows in the ARCHIVE_TABLE is 21712871.** Is it possible to lose data in such way? Is there any way to get the data back? **And yes once the row is inserted, in the database it is never updated or deleted, meaning that the data being archived does never get and updates or deletes on it. Did I miss any important option in the pt-archiver command?** Attached is an excerpt of the log file. 2024-10-22+20:22:04: Table SOURCE_TABLE ... Archival from 01-Apr-2022_00-00-00 till 31-Mar-2023_23-59-59 started. Archiving table from SOURCE_DB.SOURCE_TABLE ...... to 172.21.90.94 ARCHIVE_DB.ARCHIVE_TABLE. 2024-10-22 20:22:04 ------------------------------------------------------------------------------------------------ Follwoing are the total number of rows being archived. 21713301 ------------------------------------------------------------------------------------------------ First Run ------------------------------------------------------------------------------------------------ at /bin/pt-archiver line 4907. Exiting on SIGINT. # A software update is available: TIME ELAPSED COUNT 2024-10-22T20:22:14 0 0 2024-10-22T20:23:04 50 10000 2024-10-22T20:24:48 154 20000 ... ... ... ... 2024-10-23T00:21:10 14335 1360000 2024-10-23T00:21:10 14335 1360000 Started at 2024-10-22T20:22:14, ended at 2024-10-23T00:23:33 Source: A=UTF8,D=SOURCE_DB,P=3306,h=SOURCE_HOST,i=idx_crt_tm,p=...,t=SOURCE_TABLE,u=pt_archiver Dest: A=UTF8,D=SOURCE_DB_archive,P=3306,h=DEST_HOST,i=idx_crt_tm,p=...,t=ARCHIVE_TABLE,u=pt_archiver SELECT 1365000 INSERT 1360000 DELETE 1360000 Action Count Time Pct bulk_deleting 272 12986.3633 89.69 sleep 272 1360.7520 9.40 bulk_inserting 272 56.9958 0.39 select 273 36.9763 0.26 commit 546 5.5169 0.04 print_bulkfile 1360000 -3.0093 -0.02 other 0 35.8642 0.25 2024-10-23+00:23:34: Table SOURCE_DB.SOURCE_TABLE ... Archival from 01-Apr-2022_00-00-00 till 31-Mar-2023_23-59-59 End. ------------------------------------------------------------------------------------------------ Second Run ------------------------------------------------------------------------------------------------ 2024-10-23+00:43:28: Table SOURCE_TABLE ... Archival from 01-Apr-2022_00-00-00 till 31-Mar-2023_23-59-59 started. Archiving table from SOURCE_DB.SOURCE_TABLE ...... to 172.21.90.94 ARCHIVE_DB.ARCHIVE_TABLE. 2024-10-23 00:43:28 ------------------------------------------------------------------------------------------------ Follwoing are the total number of rows being archived. 20353301 ------------------------------------------------------------------------------------------------ TIME ELAPSED COUNT 2024-10-23T00:43:40 0 0 2024-10-23T00:45:00 80 10000 2024-10-23T00:46:50 190 20000 2024-10-23T00:48:22 282 30000 ... ... ... ... 2024-10-24T05:43:38 104397 20340000 2024-10-24T05:43:49 104408 20350000 2024-10-24T05:43:54 104414 20353301 Started at 2024-10-23T00:43:40, ended at 2024-10-24T05:43:59 Source: A=UTF8,D=SOURCE_DB,P=3306,h=SOURCE_HOST,i=idx_crt_tm,p=...,t=SOURCE_TABLE,u=pt_archiver Dest: A=UTF8,D=ARCHIVE_DB,P=3306,h=DEST_HOST,i=idx_crt_tm,p=...,t=ARCHIVE_TABLE,u=pt_archiver SELECT 20353301 INSERT 20353301 DELETE 20353301 Action Count Time Pct bulk_deleting 4071 83296.8183 79.77 sleep 4071 20355.7030 19.49 select 4072 157.4209 0.15 bulk_inserting 4071 143.8715 0.14 commit 8142 46.2508 0.04 print_bulkfile 20353301 -17.9404 -0.02 other 0 437.3239 0.42 2024-10-24+05:43:59: Table SOURCE_DB.SOURCE_TABLE ... Archival from 01-Apr-2022_00-00-00 till 31-Mar-2023_23-59-59 End. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------

We have a star schema data warehouse running on MySQL 5.6. We keep a rolling 18 months of data in our fact tables using partitions by month. We have a number of dynamic dimension tables that are referenced by multiple fact tables. However, we have no easy way to remove the rows from dimension tables that are no longer referenced by any fact table. Quick summary looks like this: dim_url - 1B rows - 360GB fact_ranks - 2.3B rows - 240GB fact_seen - 2.8B rows - 295GB Currently we are attempting to use a combination of Percona Archiver and triggers to generate "used dimension keys" tables, so we can do the process online. We then use the key table to build a new dimension that only has referenced rows. However, we have been unable to complete this process in production and estimate it could take up to a month. This has to be a common problem with a more elegant solution.

The version, mysql is InnoDB 5.6.10-log, pt-online-schema-change 3.1.0. table info is

email varchar(225) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
uid varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
hotel_brand_id int(11) DEFAULT NULL,

UNIQUE KEY index_membership_customers_on_uid_and_hotel_brand_id (uid,hotel_brand_id),

My purpose is to add a unique index,

pt-online-schema-change --recursion-method=none --nocheck-unique-key-change h=$DATABASE_HOST,D=$DATABASE,t=$TABLE,u=$DATABASE_USER,p=$DATABASE_PASSWORD   --alter "ADD UNIQUE INDEX index_membership_customers_on_email_and_hotel_brand_id (email, hotel_brand_id)"   --$RUN --alter-foreign-keys-method=auto

then I met the unique index length issue(https://stackoverflow.com/questions/1814532/mysql-error-1071-specified-key-was-too-long-max-key-length-is-767-bytes) ,

Creating new table...
Error creating new table: DBD::mysql::db do failed: Specified key was too long; max key length is 767 bytes [for Statement "CREATE TABLE umami_prod._membership_customers_new (

It seems this was the cause

uid varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
UNIQUE KEY index_membership_customers_on_uid_and_hotel_brand_id (uid,hotel_brand_id)

so I want to resize the columns or change its character set, but whatever I got to do, it failed because pt-online-schema-change try to create a new table first, then it failed because of this index length issue in the beginning. I couldn't change anything So my question is 1. How does the current table support this utfm8 varchar(255), How does it possible?

UNIQUE KEY index_membership_customers_on_uid_and_hotel_brand_id (uid,hotel_brand_id)

2. Maybe there are table settings I missed for pt-online-schema-change 3. Anything I couldn't do, next, it is a 4M rows size table, and since pt-online-schema-change couldn't work because of the above issue, my only option is alter, to lock whole table? Thanks for your reply, any hint might help, thanks

The set up I work with is MySQL 5.5 in a master-slave replication configuration, but we have to use "MIXED" replication (statement and row-based) because we use an application that requires it, and it happens to be on our same server. We had replication stop recently, and I want to make sure we have recovered properly. I downloaded and installed the Percona Toolkit, but it doesn't look like I'll be able to use pt-table-checksum in this case, specifically because of the row-based replication: > **2.27.5 LIMITATIONS** Replicas using row-based replication pt-table-checksum requires statement-based replication, and it sets > binlog_format=STATEMENT on the master, but due to a MySQL limitation > replicas do not honor this change. Therefore, checksums will not > replicate past any replicas using row-based replication that are > masters for further replicas. The tool automatically checks the > binlog_format on all servers. See > --[no]check-binlog-format . (Bug 899415) Are there any other options for doing a checksum on row-based replication tables? I have found this on the Percona Forum which says it *will* work, but I cannot have it change my cnf file as it claims it will do. It also warns, when I attempt to run it, the following: > Replica mysql-b.example.com has binlog_format MIXED which could cause > pt-table-checksum to break replication. Please read "Replicas using > row-based replication" in the LIMITATIONS section of the tool's > documentation. If you understand the risks, specify > --no-check-binlog-format to disable this check.

I am using pt-archiver for daily archiving of tables, but while selecting data from one tables I am getting following error and it restart mysql instance 2021-07-07 13:21:17 0x7fe0dffdc700 InnoDB: Assertion failure in thread 140603807352576 in file btr0pcur.cc line 46 I run pt-archiver with --dry-run and following is my select query SELECT /*!40001 SQL_NO_CACHE */ irig_time,device_id,message_id,mode,protection_1,protection_2,protection_3,protection_4,alarm_1,alarm_2,alarm_3,alarm_4,grid_switch_control,dc_switch_1_on,dc_switch_2_on,additional_feedback_external_sensor,module_communication_fault_position FROM acbm_status_v2_0_0 FORCE INDEX(PRIMARY) WHERE (DATE(irig_time)=DATE_SUB(CURDATE(), INTERVAL 1 DAY)) ORDER BY irig_time,device_id LIMIT 200 If i run this query manually still getting assertion error and it restart mysql instance. Following is table structure Table: acbm_status_v2_0_0 Columns: irig_time datetime(6) PK device_id int(11) PK message_id bigint(20) UN mode varchar(64) protection_1 int(10) UN protection_2 int(10) UN protection_3 int(10) UN protection_4 int(10) UN alarm_1 int(10) UN alarm_2 int(10) UN alarm_3 int(10) UN alarm_4 int(10) UN grid_switch_control tinyint(1) dc_switch_1_on tinyint(1) dc_switch_2_on tinyint(1) additional_feedback_external_sensor tinyint(1) module_communication_fault_position int(10) UN Below is complete trace 2021-07-07 13:21:17 0x7fe0dffdc700 InnoDB: Assertion failure in thread 140603807352576 in file btr0pcur.cc line 461 InnoDB: Failing assertion: page_is_comp(next_page) == page_is_comp(page) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com . InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 13:21:17 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=18 max_threads=500 thread_count=18 connection_count=17 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 206883 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7fe01c000d40 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7fe0dffdbe60 thread_stack 0x40000 mysqld(my_print_stacktrace+0x2c)[0x556a3c9cab7c] mysqld(handle_fatal_signal+0x501)[0x556a3c2e1f01] /lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7fe1fffaa730] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x10b)[0x7fe1ffa857bb] /lib/x86_64-linux-gnu/libc.so.6(abort+0x121)[0x7fe1ffa70535] mysqld(+0x6c1083)[0x556a3c2a9083] mysqld(+0x6c30da)[0x556a3c2ab0da] mysqld(_Z15row_search_mvccPh15page_cur_mode_tP14row_prebuilt_tmm+0xd03)[0x556a3cc699a3] mysqld(_ZN11ha_innobase13general_fetchEPhjj+0xdf)[0x556a3cb6d4af] mysqld(_ZThn760_N11ha_innopart18index_next_in_partEjPh+0x2d)[0x556a3cb8351d] mysqld(_ZN16Partition_helper19handle_ordered_nextEPhb+0x299)[0x556a3c714199] mysqld(_ZN7handler13ha_index_nextEPh+0x1c5)[0x556a3c3358d5] mysqld(+0xb932dc)[0x556a3c77b2dc] mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x18f)[0x556a3c7817cf] mysqld(_ZN4JOIN4execEv+0x20b)[0x556a3c77aacb] mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x2e0)[0x556a3c7e2d50] mysqld(+0xbbd45b)[0x556a3c7a545b] mysqld(_Z21mysql_execute_commandP3THDb+0x4924)[0x556a3c7ac564] mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3dd)[0x556a3c7ae94d] mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x1062)[0x556a3c7afa22] mysqld(_Z10do_commandP3THD+0x207)[0x556a3c7b0d67] mysqld(handle_connection+0x298)[0x556a3c8690c8] mysqld(pfs_spawn_thread+0x157)[0x556a3ce77cd7] /lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7fe1fff9ffa3] /lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fe1ffb474cf] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7fe01c004860): SELECT /*!40001 SQL_NO_CACHE */ irig_time,device_id,message_id,mode,protection_1,protection_2,protection_3,protection_4,alarm_1,alarm_2,alarm_3,alarm_4,grid_switch_control,dc_switch_1_on,dc_switch_2_on,additional_feedback_external_sensor,module_communication_fault_position FROM ycube2.acbm_status_v2_0_0 FORCE INDEX(PRIMARY) WHERE (DATE(irig_time)=DATE_SUB(CURDATE(), INTERVAL 1 DAY)) ORDER BY irig_time,device_id LIMIT 1000 Connection ID (thread ID): 41 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash.

I've got an InnoDB table over 50GB with indexes. The query I'm running looks like this: pt-online-schema-change h=localhost,D=db,t=table --critical-load="Threads_running=100" --check-slave-lag "h=XX.XX.XX.XX" --max-lag 10 --execute --pass PASS --alter "REORGANIZE PARTITION max INTO ( PARTITION p19 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-05-01 00:00:00') ), PARTITION p20 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-06-01 00:00:00') ), PARTITION max VALUES LESS THAN maxvalue )" No matter what settings I use, I get the Threads_running issue. I have also tried: --chunk-size 100 (also tried 20) --max-load="Threads_running=20" Shouldn't it just wait until threads running gets lowered? Why does it keep quitting? I could raise the critical load to allow 200 threads, but I'm afraid of the server crashing. Any advice?

My installed versions of MariaDB and Percona Toolkit on MacBook: brew info percona-toolkit percona-toolkit: stable 3.0.10 (bottled), HEAD Percona Toolkit for MySQL https://www.percona.com/software/percona-toolkit/ /usr/local/Cellar/percona-toolkit/3.0.10 (244 files, 8.4MB) * Poured from bottle on 2018-05-31 at 09:52:48 From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/percona-toolkit.rb ==> Dependencies Required: mysql ✔, openssl ✔ ==> Options --HEAD Install HEAD version DB server versions: show global variables like '%version%'; +-------------------------+---------------------------+ | Variable_name | Value | +-------------------------+---------------------------+ | innodb_version | 5.6.36-82.1 | | protocol_version | 10 | | slave_type_conversions | | | version | 10.1.26-MariaDB | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_malloc_library | system | | version_ssl_library | OpenSSL 1.0.1f 6 Jan 2014 | | wsrep_patch_version | wsrep_25.19 | +-------------------------+---------------------------+ 10 rows in set (0.01 sec) Funny is in order to install percona-toolkit I had to install Oracle MySQL and afterwords change back to MariaDB brew install mariadb brew unlink mariadb brew install percona-toolkit brew unlink mysql brew link mariadb We use Galera cluster with row based replication: show global variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) I did a simple use case: mysqlbinlog mysql-bin.0013* > all.sql pt-query-digest --type binlog all.sql all.sql: 1% 37:37 remain (...) all.sql: 96% 01:23 remain all.sql: 98% 00:30 remain # 2417.5s user time, 51.4s system time, 89.16M rss, 4.24G vsz # Current date: Fri Jun 1 07:42:57 2018 # Hostname: aukVivi0009 # Files: all.sql # Overall: 0 total, 2.05k unique, 0 QPS, 0x concurrency __________________ # Time range: 2018-05-26 02:00:54 to 2018-05-31 08:05:28 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Query size 10.68G 6 287.51k 492.88 833.10 1.92k 107.34 # Profile # Rank Query ID Response time Calls R/Call V/M Item # =========== =========== =========== =========== =========== ===== ====== why is the output empty? Is pt-query-digest not compatible with MariaDB? Changes in binlog format? Any workaround?

We are using two MYSQL servers in ring replication where master is also a slave of the other master. Both have slaves connected to each other. We want to run pt-online-schema-change to add couple of columns but we are not sure if it may break the replication due to ring replication in place. Any advice would be great.

We have a table with 600M+ records and using AWS Aurora with master-slave replication. **Requirement:** We need to add two columns with index in customer table. **Aurora Instance Specs:** Ram: 30.5 GB CPU: 4 vCPU I replicated the cluster and created instance with same configuration and ran the update process to newly created master instance to see how it works. But it's taking a lot time; ( already been 4 days ) and still running. Is there anything I can do to speedup the alter process? **Command:** pt-online-schema-change --execute --ask-pass --user=admin --statistics --chunk-time=1 --alter 'ADD COLUMN email_sha1 CHAR(40) DEFAULT null, ADD COLUMN email_sha256 CHAR(64) DEFAULT null, ADD INDEX email_sha1(email_sha1) USING BTREE, ADD INDEX email_sha256(email_sha256) USING BTREE' D=example,t=customer,h=example.public.rds.amazonaws.com

I have a very large table, lets call it example, and I try to execute a little alter command on that table: ALTER TABLE example ADD example_column TINYINT(1) NOT NULL DEFAULT 0; Checking the progress of alter command: mysql -e 'show engine innodb status \G' | grep 'undo \| log \| entries' Give me nice info, about the time - more or less it will take 17 days to complete ... Alter block the table, so blocking production table for 17 days is not a good option. ;) I try to investigate some great tools on the net, like: 1. Pt-online-schema-change - Percona 2. gh-ost - GitHub's Online Schema Migrations for MySQL I have also read the documentation, and the limitation section of the above tools: gh-ost limitations - does not support triggers at all - does not support foreign keys at all pt-online-schema-change limitations - The use of triggers means that the tool will not work if any triggers are already defined on the table. - Does not provide any good way of altering tables which contain foreign keys My example table have triggers and foreign keys... Could you give me some advice, how to deal with this alter? I have MySQL 5.6. I use GTID (row-based) replication. I will be very grateful for advice!

I'm using pt-query-digest (v2.2.20) slowlog output format to filter out queries based on their timestamp, to run further digests on the filtered slowlog : ptqd 11-21.slow.log --output slowlog --no-report --filter '($event->{timestamp} > 1483957800) && ($event->{timestamp} 11-21.halfhour.log The point is my source log 11-21.slow.log has query cache hit info, for instance : # Time: 170109 12:08:43 # User@Host: pidf_user[pidf_user] @ [10.226.13.132] # Thread_id: 32829731 Schema: webmbpidf QC_hit: No # Query_time: 0.015387 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 0 Whereas the filtered output 11-21.halfhour.log doesn't : # Time: 170109 11:59:59 # User@Host: pest_user[pest_user] @ 10.226.13.13 [] # Thread_id: 32805954 # Query_time: 0.000221 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 l lose schema information as well but it's less a problem in my situation. Is there a way to keep the query cache info in the slowlog output ? Any help would me much appreciated.

I used pt-online-schema-change but the process died in the middle. The script makes a copy of the original table with the name followed by "_new". Since the process died in the middle, the table "_new" wasn't deleted and when I try to delete it my application crashes. It seems whenever I alter the original table this _new table is also changed so if it is not found it crashes. I would like to know how can I remove this _new table.

While trying to use pt-table-sync with the following options for a list of slaves: pt-table-sync --print --replicate schema.checksum_table --sync-to-master slave-001 I receive the following error message on some of the slaves: DBD::mysql::db STORE failed: Turning off AutoCommit failed at /usr/lib/perl5/DBI.pm line 723. The MySQL version for the slave is 5.6.17 and for the master 5.6.15. Other slaves for which this does work also have MySQL 5.6.15 version. I am able to turn off session autocommit if I log in to the slave directly using MySQL client and the same user being used for pt-table-sync. The checksum table in use was created by previously running pt-table-checksum. DBI.pm version: perl -MDBI -e 'print "$DBI::VERSION\n";' 1.63 Has anyone encountered this issue before? Please let me know if you need me to provide more information.

My environment: # cat /etc/redhat-release Red Hat Enterprise Linux Server release 6.8 (Santiago) # uname -a Linux A.B.C 2.6.32-642.4.2.el6.x86_64 #1 SMP Mon Aug 15 02:06:41 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux # rpm -q mysql-server percona-toolkit mysql-server-5.1.73-7.el6.x86_64 percona-toolkit-2.2.19-1.noarch # I'm running pt-table-checksum and I'm getting following error: > Skipping chunk X of Y.Z because MySQL chose no index instead of the > word_sid_typeindex. mysql> DESCRIBE search_index; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | word | varchar(50) | NO | MUL | | | | sid | int(10) unsigned | NO | MUL | 0 | | | type | varchar(16) | YES | | NULL | | | score | float | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> SHOW TABLE STATUS WHERE Name='search_index'\G; *************************** 1. row *************************** Name: search_index Engine: InnoDB Version: 10 Row_format: Compact Rows: 3155432 Avg_row_length: 51 Data_length: 163250176 Max_data_length: 0 Index_length: 415514624 Data_free: 839909376 Auto_increment: NULL Create_time: 2016-09-01 21:14:58 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.13 sec) ERROR: No query specified mysql> Please advice.

I'm running MySQL in a master-slave configuration. pt-heartbeat is showing "Host is blocked because of many connection errors"...but connections are well under limit (limit is 600 connections, MySQL workbench shows I'm hovering around 250, and I'm having no other problems connecting). Here is my CLI, running from the master: PTDEBUG=1 pt-heartbeat --host="mysql-slave" --user=root --ask-pass --database="percona" --monitor --master-server-id="14" Any ideas? Should I run the suggested 'mysqladmin flush-hosts' as PTDEBUG suggests? I just don't feel that's the problem if I'm below the limit and not seeing this anywhere else. Thx

export/import: $ mysqldump -h6dhdbm01 -p clee2 users > 6dhdbm01-clee2.users.sql Enter password: $ echo $? 0 $ mysql clee2 line 1. The current value for innodb_lock_wait_timeout is 120. If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=120 to avoid this warning, else manually set the variable and restart MySQL. Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information. TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 10-14T08:39:12 0 0 100679 9 0 2.804 clee2.users $ query: $ mysql -e"SELECT * FROM percona.checksums WHERE db = 'clee2' AND tbl = 'users';" +-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ | db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts | +-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ | clee2 | users | 1 | 0.071199 | PRIMARY | 0 | 1491 | b3c4d418 | 1000 | b3c4d418 | 1000 | 2014-10-14 08:38:58 | | clee2 | users | 2 | 0.089819 | PRIMARY | 1492 | 9015 | b50c2d1b | 7022 | b50c2d1b | 7022 | 2014-10-14 08:38:59 | | clee2 | users | 3 | 0.13534 | PRIMARY | 9016 | 38573 | b8bcb3ec | 27133 | b8bcb3ec | 27133 | 2014-10-14 08:38:59 | | clee2 | users | 4 | 0.221181 | PRIMARY | 38574 | 108035 | 239edb5d | 65524 | 239edb5d | 65524 | 2014-10-14 08:38:59 | | clee2 | users | 5 | 0.066098 | PRIMARY | NULL | 0 | 0 | 0 | 0 | 0 | 2014-10-14 08:38:59 | | clee2 | users | 6 | 0.06616 | PRIMARY | 108035 | NULL | 0 | 0 | 0 | 0 | 2014-10-14 08:39:00 | +-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ $ mysql -h6dhdbm01 -p -e"SELECT * FROM percona.checksums WHERE db = 'clee2' AND tbl = 'users';" Enter password: +-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ | db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts | +-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ | clee2 | users | 1 | 0.083038 | PRIMARY | 0 | 1491 | b3c4d418 | 1000 | b3c4d418 | 1000 | 2014-10-14 08:39:09 | | clee2 | users | 2 | 0.228184 | PRIMARY | 1492 | 7932 | 26fd4c48 | 6021 | 26fd4c48 | 6021 | 2014-10-14 08:39:10 | | clee2 | users | 3 | 0.249667 | PRIMARY | 7933 | 20618 | 89d9fbdb | 11655 | 89d9fbdb | 11655 | 2014-10-14 08:39:10 | | clee2 | users | 4 | 0.383064 | PRIMARY | 20619 | 40104 | 17c8b21e | 17895 | 17c8b21e | 17895 | 2014-10-14 08:39:10 | | clee2 | users | 5 | 0.42216 | PRIMARY | 40105 | 63159 | 1beb0525 | 20747 | 1beb0525 | 20747 | 2014-10-14 08:39:11 | | clee2 | users | 6 | 0.466877 | PRIMARY | 63160 | 86949 | 41efafd2 | 22408 | 41efafd2 | 22408 | 2014-10-14 08:39:11 | | clee2 | users | 7 | 0.432634 | PRIMARY | 86950 | 108035 | ccc6ead0 | 20953 | ccc6ead0 | 20953 | 2014-10-14 08:39:12 | | clee2 | users | 8 | 0.033889 | PRIMARY | NULL | 0 | 0 | 0 | 0 | 0 | 2014-10-14 08:39:12 | | clee2 | users | 9 | 0.035318 | PRIMARY | 108035 | NULL | 0 | 0 | 0 | 0 | 2014-10-14 08:39:12 | +-------+-------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+ $ How can I force pt-table-checksum to have _SINGLE_ chunk vs multiple? or at least same chunks, so I can actually compare.

I created my little lab where I can try different setup/scenarios. I have four machines debootsraped/chroot and this is my current layout: `| Master A | | Master B | | | Slave C | | Slave D |` Database is created with one table, replication is working. Intention of this post it to find out if it is possible to do the following: - I set sql_log_bin=0 and create table called sync on masterA, (to simulate inconsistent state) - then I returned variable sql_log_bin to 1. Normally when I try to use pt-table-sync this doesn't work (throwing error that table doesn't exists; which is noted on percona site), so I was thinking if I create identical table on slave and try to synchronize that it will work but it doesn't. Is there a solution to sync it with pt-table-sync, to avoid backing up db on masterB and moving to masterA?