If you have binlog enabled and binlog_do_db set to some table and then make a mariadb-dump and import the dump to some database different of the one set in binlog_do_db, then all the Innodb tables in that database have no records.
I'm attaching a bash script to demonstrate the problem.
The result from all counts should e 1, but when binlog_do_db is set the result for the innodb table changes.
That behaviour wasn't observed in 10.5 versions. It appeared after upgrade to 10.8.
Cannot report for the versions in between, unfortunately.
Sorry, I wasn't able to repeat the bug with your script. It shows
+------------+
| innodb_bug |
+------------+
| 1 |
+------------+
on
# mysql -e 'select version()'
+-------------------------------------+
| version() |
+-------------------------------------+
| 10.8.6-MariaDB-1:10.8.6+maria~deb10 |
+-------------------------------------+
The issue will be closed as not reproducible, but don't worry, if you provide more info we'll reopen it
Sergei Golubchik
added a comment - - edited Sorry, I wasn't able to repeat the bug with your script. It shows
+------------+
| innodb_bug |
+------------+
| 1 |
+------------+
on
# mysql -e 'select version()'
+-------------------------------------+
| version() |
+-------------------------------------+
| 10.8.6-MariaDB-1:10.8.6+maria~deb10 |
+-------------------------------------+
The issue will be closed as not reproducible, but don't worry, if you provide more info we'll reopen it
That is very interesting.
I've just updated from 10.8.5 to: 10.8.6-MariaDB-1:10.8.6+maria~deb10-log and the bug is still reproducible.
I'll try to find out what else it depends on.
Anton Avramov
added a comment - That is very interesting.
I've just updated from 10.8.5 to: 10.8.6-MariaDB-1:10.8.6+maria~deb10-log and the bug is still reproducible.
I'll try to find out what else it depends on.
this shows the table empty. If the server is started with --log-bin --binlog-do-db=foo
Removing at least one of the two assignments makes insert to work.
Sergei Golubchik
added a comment - this seems related to innodb bulk insert.
set unique_checks=0, foreign_key_checks=0;
create table t1 (c int (11) not null auto_increment primary key ) engine=innodb;
insert into t1 values (1);
select count (*) as innodb_ok from t1;
this shows the table empty. If the server is started with --log-bin --binlog-do-db=foo
Removing at least one of the two assignments makes insert to work.
This bug does not seem to affect MariaDB Server 10.6, which was the first major version to include MDEV-515. This could be related to the follow-up change MDEV-24621.
Marko Mäkelä
added a comment - This bug does not seem to affect MariaDB Server 10.6, which was the first major version to include MDEV-515 . This could be related to the follow-up change MDEV-24621 .
During commit, InnoDB does apply the buffered bulk insert operation in innodb_prepare_commit_versioned().
But if we add the option like --log-bin and --binlog_do_db=<non-default database> then
we never call trx_end_id= ha_info->ht()->prepare_commit_versioned(thd, &trx_start_id);.
#if 1 // FIXME: This should be done in ha_prepare().
if (rw_trans || (thd->lex->sql_command == SQLCOM_ALTER_TABLE &&
I think prepare_commit_versioned should be called irrespective of binlog state. So I added the patch in bb-10.10-MDEV-28699 and bb-10.4-MDEV-28699 too.
Thirunarayanan Balathandayuthapani
added a comment - During commit, InnoDB does apply the buffered bulk insert operation in innodb_prepare_commit_versioned().
But if we add the option like --log-bin and --binlog_do_db=<non-default database> then
we never call trx_end_id= ha_info->ht()->prepare_commit_versioned(thd, &trx_start_id); .
#if 1 // FIXME: This should be done in ha_prepare().
if (rw_trans || (thd->lex->sql_command == SQLCOM_ALTER_TABLE &&
thd->lex->alter_info.flags & ALTER_ADD_SYSTEM_VERSIONING &&
is_real_trans))
{
ulonglong trx_start_id= 0, trx_end_id= 0;
for (Ha_trx_info *ha_info= trans->ha_list; ha_info; ha_info= ha_info->next())
{
if (ha_info->ht()->prepare_commit_versioned)
{
trx_end_id= ha_info->ht()->prepare_commit_versioned(thd, &trx_start_id);
if (trx_end_id == ULONGLONG_MAX)
{
my_error(ER_ERROR_DURING_COMMIT, MYF(0), 1);
goto err;
}
if (trx_end_id)
break; // FIXME: use a common ID for cross-engine transactions
}
}
But rw_trans is disabled if we use the above option.
/* rw_trans is TRUE when we in a transaction changing data */
bool rw_trans= is_real_trans &&
(rw_ha_count > (thd->is_current_stmt_binlog_disabled()?0U:1U));
binlog_do_db disables the binlog for test database. So it sets rw_trans to false.
rw_trans indicates only binlog writes.
To verify the claim, ran the versioning suite with
./mtr --suite=versioning --mysqld=--log-bin=1 --mysqld=--binlog_do_db=foo
Completed: Failed 28/100 tests, 72.00% were successful.
Failing test(s): versioning.delete_history versioning.select versioning.select2 versioning.update versioning.trx_id versioning.commit_id versioning.rpl versioning.rpl_mix versioning.rpl_row versioning.rpl_stmt
I think prepare_commit_versioned should be called irrespective of binlog state. So I added the patch in bb-10.10- MDEV-28699 and bb-10.4- MDEV-28699 too.
People
Thirunarayanan Balathandayuthapani
Anton Avramov
Votes:
1Vote for this issue
Watchers:
6Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
{"report":{"fcp":865.2000000476837,"ttfb":247.20000004768372,"pageVisibility":"visible","entityId":116620,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":0.5,"journeyId":"5a56d9aa-dd0f-4e63-bf4b-f9d2b5149094","navigationType":0,"readyForUser":1004.4000000953674,"redirectCount":0,"resourceLoadedEnd":584.5,"resourceLoadedStart":252.5,"resourceTiming":[{"duration":26.40000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":252.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":252.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":278.90000009536743,"responseStart":0,"secureConnectionStart":0},{"duration":26.899999856948853,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":252.90000009536743,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":252.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":279.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":228.20000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":253.09999990463257,"connectEnd":253.09999990463257,"connectStart":253.09999990463257,"domainLookupEnd":253.09999990463257,"domainLookupStart":253.09999990463257,"fetchStart":253.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":284.7999999523163,"responseEnd":481.2999999523163,"responseStart":312.5,"secureConnectionStart":253.09999990463257},{"duration":331.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":253.20000004768372,"connectEnd":253.20000004768372,"connectStart":253.20000004768372,"domainLookupEnd":253.20000004768372,"domainLookupStart":253.20000004768372,"fetchStart":253.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":285,"responseEnd":584.5,"responseStart":333.2000000476837,"secureConnectionStart":253.20000004768372},{"duration":61.200000047683716,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":253.29999995231628,"connectEnd":253.29999995231628,"connectStart":253.29999995231628,"domainLookupEnd":253.29999995231628,"domainLookupStart":253.29999995231628,"fetchStart":253.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":285.40000009536743,"responseEnd":314.5,"responseStart":313.2000000476837,"secureConnectionStart":253.29999995231628},{"duration":63.700000047683716,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":253.29999995231628,"connectEnd":253.29999995231628,"connectStart":253.29999995231628,"domainLookupEnd":253.29999995231628,"domainLookupStart":253.29999995231628,"fetchStart":253.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":285.5,"responseEnd":317,"responseStart":314.7000000476837,"secureConnectionStart":253.29999995231628},{"duration":31.09999990463257,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":253.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":253.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":284.59999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":63.700000047683716,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":253.5,"connectEnd":253.5,"connectStart":253.5,"domainLookupEnd":253.5,"domainLookupStart":253.5,"fetchStart":253.5,"redirectEnd":0,"redirectStart":0,"requestStart":286.2000000476837,"responseEnd":317.2000000476837,"responseStart":315.2000000476837,"secureConnectionStart":253.5},{"duration":64,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":253.59999990463257,"connectEnd":253.59999990463257,"connectStart":253.59999990463257,"domainLookupEnd":253.59999990463257,"domainLookupStart":253.59999990463257,"fetchStart":253.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":288.40000009536743,"responseEnd":317.59999990463257,"responseStart":315.7000000476837,"secureConnectionStart":253.59999990463257},{"duration":32.89999985694885,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":253.70000004768372,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":253.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":286.59999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":72.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":253.79999995231628,"connectEnd":253.79999995231628,"connectStart":253.79999995231628,"domainLookupEnd":253.79999995231628,"domainLookupStart":253.79999995231628,"fetchStart":253.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":289.40000009536743,"responseEnd":326.2000000476837,"responseStart":322.5,"secureConnectionStart":253.79999995231628},{"duration":282.7000000476837,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":279.2000000476837,"connectEnd":279.2000000476837,"connectStart":279.2000000476837,"domainLookupEnd":279.2000000476837,"domainLookupStart":279.2000000476837,"fetchStart":279.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":376.90000009536743,"responseEnd":561.9000000953674,"responseStart":557,"secureConnectionStart":279.2000000476837},{"duration":271.89999985694885,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":290.2000000476837,"connectEnd":290.2000000476837,"connectStart":290.2000000476837,"domainLookupEnd":290.2000000476837,"domainLookupStart":290.2000000476837,"fetchStart":290.2000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":381.40000009536743,"responseEnd":562.0999999046326,"responseStart":558.2000000476837,"secureConnectionStart":290.2000000476837},{"duration":142.5,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":615.2999999523163,"connectEnd":615.2999999523163,"connectStart":615.2999999523163,"domainLookupEnd":615.2999999523163,"domainLookupStart":615.2999999523163,"fetchStart":615.2999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":717.2999999523163,"responseEnd":757.7999999523163,"responseStart":757,"secureConnectionStart":615.2999999523163},{"duration":231.29999995231628,"initiatorType":"script","name":"https://www.google-analytics.com/analytics.js","startTime":857.7999999523163,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":857.7999999523163,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":1089.0999999046326,"responseStart":0,"secureConnectionStart":0},{"duration":262.5,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":866,"connectEnd":866,"connectStart":866,"domainLookupEnd":866,"domainLookupStart":866,"fetchStart":866,"redirectEnd":0,"redirectStart":0,"requestStart":1090.5,"responseEnd":1128.5,"responseStart":1127.9000000953674,"secureConnectionStart":866}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":93,"responseStart":247,"responseEnd":290,"domLoading":250,"domInteractive":1080,"domContentLoadedEventStart":1080,"domContentLoadedEventEnd":1133,"domComplete":1709,"loadEventStart":1709,"loadEventEnd":1709,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":1044},{"name":"bigPipe.sidebar-id.end","time":1044.7999999523163},{"name":"bigPipe.activity-panel-pipe-id.start","time":1044.9000000953674},{"name":"bigPipe.activity-panel-pipe-id.end","time":1048.5999999046326},{"name":"activityTabFullyLoaded","time":1156.4000000953674}],"measures":[],"correlationId":"1a503a6675bf31","effectiveType":"4g","downlink":9.7,"rtt":0,"serverDuration":97,"dbReadsTimeInMs":22,"dbConnsTimeInMs":31,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Sorry, I wasn't able to repeat the bug with your script. It shows
+------------+
| innodb_bug |
+------------+
| 1 |
+------------+
on
# mysql -e 'select version()'
+-------------------------------------+
| version() |
+-------------------------------------+
| 10.8.6-MariaDB-1:10.8.6+maria~deb10 |
+-------------------------------------+
The issue will be closed as not reproducible, but don't worry, if you provide more info we'll reopen it