-
Notifications
You must be signed in to change notification settings - Fork 1.3k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Working towards an atomic table swap #32
Comments
Thank you for spending the time reviewing this. I'm closing this issue as #65 came up, which I believe to solve the cut-over even in face of connection failure, and via pure MySQL commands. |
timvaillancourt
pushed a commit
to timvaillancourt/gh-ost
that referenced
this issue
Aug 4, 2022
Support tables with existing zero dates
timvaillancourt
pushed a commit
to timvaillancourt/gh-ost
that referenced
this issue
Aug 4, 2022
Support tables with existing zero dates
timvaillancourt
added a commit
that referenced
this issue
Aug 10, 2022
* Merge pull request #31 from openark/zero-date Support zero date and zero in date, via dedicated command line flag * Merge pull request #32 from openark/existing-date-with-zero Support tables with existing zero dates * Remove un-needed ignore_versions file * Fix new lint errors from golang-ci update Co-authored-by: Shlomi Noach <2607934+shlomi-noach@users.noreply.github.com>
RainbowDashy
referenced
this issue
in bytebase/gh-ost
Dec 13, 2022
* Merge pull request #31 from openark/zero-date Support zero date and zero in date, via dedicated command line flag * Merge pull request #32 from openark/existing-date-with-zero Support tables with existing zero dates * Remove un-needed ignore_versions file * Fix new lint errors from golang-ci update Co-authored-by: Shlomi Noach <2607934+shlomi-noach@users.noreply.github.com>
d-bytebase
referenced
this issue
in bytebase/gh-ost
Dec 13, 2022
* Add script and docs for linter (github#1151) * Enable more `golang-ci` linters (github#1149) * Only build RPM and deb packages for amd64 * Convert character to bytes and insert into table using latin1 * delete junk files * restore connection charset to utf8mb4 * Allow zero in dates (github#1161) * Merge pull request #31 from openark/zero-date Support zero date and zero in date, via dedicated command line flag * Merge pull request #32 from openark/existing-date-with-zero Support tables with existing zero dates * Remove un-needed ignore_versions file * Fix new lint errors from golang-ci update Co-authored-by: Shlomi Noach <2607934+shlomi-noach@users.noreply.github.com> * Add missing doc from PR github#1131 (github#1162) * Set a transaction isolation level for MySQL connections (github#1156) * Set transaction isolation in connections * Revert load_map.go change * Var rename * Restore comment * Some fix to unit tests. * convert to bytes if character string without charsetConversion. * chore: remove duplicate word in comments (github#1175) Signed-off-by: Abirdcfly <fp544037857@gmail.com> Signed-off-by: Abirdcfly <fp544037857@gmail.com> * Improve applier `.ReadMigrationRangeValues()` func accuracy (github#1164) * Use a transaction in applier `ReadMigrationRangeValues` func * Private func names * Add basic tests for applier (github#1165) * Add basic tests for applier * Add header * Add basic test for inspector (github#1166) * Add basic test for inspector * Add header * Fix return * Add basic tests to migrator (github#1168) * add-rocksdb-as-transactional-engine * Add basic test for hooks (github#1179) * Enable more `golangci-lint` linters (github#1181) * Print status to migration context logger * fix CI tests to ubuntu-20.04 because ubuntu-22.04 (current -latest) doesn't support MySQL 5.7 * temp commit to investigate datetime-with-zero test failure * more testing * add extra debugging output * debugging * add error detection for test setup, sort tests to make it easier to track progress * fix broken test by removing invalid insert statement * Fix: Change table name table name is 'tbl' not 'tble' * Attempt instant DDL if supported * minor cleanup * Add tests, incorporate feedback * Improve docs * Address PR feedback * Make it clear in docs it is disabled by default but safe. * Update go/logic/migrator.go Co-authored-by: dm-2 <45519614+dm-2@users.noreply.github.com> * remove useless func per review * support rocksdb as transactional engine * Modify tests to support rocksdb tests * SetConnectionConfig * add support for rocksdb * add support for rocksdb * add percona to versions in workflows * add description and optimize tests * Apply suggestions from code review Co-authored-by: dm-2 <45519614+dm-2@users.noreply.github.com> * Apply suggestions from code review Co-authored-by: Tim Vaillancourt <tim@timvaillancourt.com> * Update go/logic/applier.go Signed-off-by: Abirdcfly <fp544037857@gmail.com> Co-authored-by: Tim Vaillancourt <tim@timvaillancourt.com> Co-authored-by: dm-2 <45519614+dm-2@users.noreply.github.com> Co-authored-by: wangzihuacool <wangzihuacool@163.com> Co-authored-by: wangzihuacool <47876169+wangzihuacool@users.noreply.github.com> Co-authored-by: Shlomi Noach <2607934+shlomi-noach@users.noreply.github.com> Co-authored-by: Abirdcfly <fp544037857@gmail.com> Co-authored-by: Nicholas Calugar <nicholas.calugar@gusto.com> Co-authored-by: Hasan Mshawrab <63023909+hasanMshawrab@users.noreply.github.com> Co-authored-by: Morgan Tocker <mtocker@squareup.com> Co-authored-by: Morgan Tocker <tocker@gmail.com> Co-authored-by: lukelewang <lukelewang@tencent.com>
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
As per #26, the solution of atomically swapping the tables is unsafe in the event of death of connections.
I've been considering some other solutions. I've reached dead-end with all of them, but hopefully someone else can pick on where I stopped and find a solution.
An idea based on views, derived from my earlier work on http://code.openark.org/blog/mysql/auto-caching-tables is as follows:
Views & SLEEP(), dead-end on the SLEEP() part
tbl
ghost
create or replace view ghost_view as select * from ghost where sleep(600)>=0
(this view is updatable, and can receive
INSERT
,UPDATE
,DELETE
)rename table tbl to tbl_old, ghost_view to tbl
RENAME
works just as well on views as it does on tablesWe now have time to apply changes onto
ghost
rename table tbl to tbl_old_view, ghost to tbl
but therename
is blocked until all those hanging queries are complete. There's no way to migrate those already running queries onto the renamed ghost table.rename
hangs until the queries complete, which is not what we want.Any ideas?
Views & GET_LOCK(), spaghetti on GET_LOCK()
Before I realized the spaghetti was there, I found this solution to be appealing:
GET_LOCK
, so same "what if the connection dies" problem is still thererename
(as opposed to death of any connection)And then I found the spaghetti.
The solution is similar to the above, but:
GET_LOCK('ding', 0)
and succeedscreate or replace view ghost_view as select * from ghost where get_lock('ding',600) >= 0
rename table tbl to tbl_old, ghost_view to tbl
queries are blocked on
tbl
(which is now aview
)ghost
rename table tbl to tbl_old_view, ghost to tbl
RELEASE_LOCK('ding')
in connection mysqlbinlog reader proof of concept #1rename
unblocks and we have our migrated table in place.Before discussing the Problem, note that we can variant as follows:
SELECT GET_LOCK('ding#1', 600)
toGET_LOCK('ding#n', 600)
(we take n locks)create or replace view ghost_view as select * from ghost where get_lock('ding#1',600)+get_lock('ding#2',600)+...+get_lock('ding#n',600) >= 0
Such that it would take the death of all n connections to make a premature
rename
.So, back to the problem. Each query will issue
get_lock
and queries will block each other, leading to really scary workload. We can hack around this by doing crazy stuff like:create view... select where release_lock(concat('ding', if(get_lock('ding',600) >= 0, '', ''))) >= 0
. This will make sure to release any acquired lock.Ideas?
The text was updated successfully, but these errors were encountered: