Comments (11)
As the title suggests this is a MySQL limitation that stopped+shutdown slaves
(or slaves under snapshot) with open temporary tables turn to be inconsistent.
So question is how far shall I go to go around this MySQL bug/problem.
I'm using MyISAM temporary tables for the main reasons that they are fast to
create/drop and are non persistent (if anything crashes, I don't need to
maintain a "tables I should be dropping later on" list).
If I'm going to use persistent tables, I won't dare use MyISAM. As result, I
will be using InnoDB, which will turn all DDL to be very slow; so this is a
tight corner.
If you know of a good solution please let me know.
Original comment by [email protected]
on 19 May 2013 at 6:40
from common-schema.
I could use CONNECTION_ID() for naming the tables, and do a cleanup for all
tables with IDs not in PROCESSLIST. Someone will have to pay for this cleanup;
namely, other script executions. Cleanup is required in case of crash or
premature termination of script. Typically this would not happen, but one can
never know.
Still, in some operations such as split() I create supporting tables while the
script executes. I would hate to create these as normal tables; I've already
seen the impact of (temporary) innodb tables creation on scripting -- it's
disastrous.
Original comment by [email protected]
on 19 May 2013 at 10:06
from common-schema.
I'm not sure there is a perfect workaround for this. I wonder if things
like split() that will potentially amortize the cost of creating the temp
table over time can stand the cost of creating it with innodb.
Original comment by baron.schwartz
on 20 May 2013 at 11:53
from common-schema.
I can see how this is a serious issue for long running scripts (never releasing
the temporary tables, thereby not allowing for safe slave shutdown). Need to
think this over.
Original comment by [email protected]
on 21 May 2013 at 4:35
from common-schema.
Will attempt using the following tables:
_sql_tokens
_qs_variables
_script_report_data
_split_column_names_table
as global (standard) InnoDB tables.
Will add a "session_id" (== CONNECTION_ID()) column to each
Where possible (certainly in _sql_tokens), will currently rename tables and add
view under same original name which only filters appropriate rows.
Upon script termination / split termination, will cleanup tables
Upon script startap / split startup, will cleanup tables for same
CONNECTION_ID()
May need to support additoinal "queryscript_cleanup()" routine to handle
leftover from aborted scripts
Original comment by [email protected]
on 26 May 2013 at 10:04
from common-schema.
Original comment by [email protected]
on 2 Jun 2013 at 2:44
- Changed state: Started
from common-schema.
Fixed in revision 468 (not yet released)
Original comment by [email protected]
on 3 Jun 2013 at 5:47
from common-schema.
Original comment by [email protected]
on 3 Jun 2013 at 5:47
- Changed state: Verified
from common-schema.
Attached temporary build file
Original comment by [email protected]
on 3 Jun 2013 at 5:58
Attachments:
from common-schema.
Thanks! Looks like there is still a (very small, reasonable) chance of this
problem happening but this should be much improved.
Original comment by baron.schwartz
on 3 Jun 2013 at 5:12
from common-schema.
Temporary tables are now only created per split() operation, and for the
duration of table analysis; so
CREATE-CREATE-CREATE-CREATE-SELECT-DROP-DROP-DROP-DROP. Tables are dropped
before actually iterating the split() statement, so should be very short period.
Original comment by [email protected]
on 3 Jun 2013 at 7:31
from common-schema.
Related Issues (20)
- The split query_script statement will not allow the use of PARTITION selection syntax feature of MySQL 5.6 HOT 3
- common_schema.json_to_xml is not parsing valid json array elements. HOT 3
- Documentation issue - Google is blocking access to query script execution page HOT 2
- Looking for innodb_index_stats in the wrong schema in mysql 5.6 HOT 4
- Bug in the _as_datetime() function HOT 4
- SUPER privilege error with Amazon RDS HOT 7
- Documentation issue
- sql_range_partition does not function as expected when the column is a timestamp integer
- split does not work if stop is specified HOT 1
- Routine dependencies fails on replace function
- Routine dependencies confuses default schema HOT 1
- Routine dependencies returns column name from a join <using> clause HOT 1
- Routine dependencies confused by <insert ... on duplicate update> HOT 1
- Routine dependencies misinterpret column when using insert statement schema name HOT 1
- common_schema 2.2 fails on install HOT 1
- Patch for /common_schema/routines/general_internal/_get_json_token.sql
- Minus breaks extract_json_value and anything dependent on json_to_xml HOT 1
- Errors when trying to install script via SQLYog HOT 1
- add next partition not using datetime fail after a value >= 10000 (or thereabouts)
- json_to_xml - malformed charset on output
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from common-schema.