Skip to content
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

MySQL: User-defined variables leak across migrations #2197

Closed
htto opened this issue Nov 6, 2018 · 8 comments
Closed

MySQL: User-defined variables leak across migrations #2197

htto opened this issue Nov 6, 2018 · 8 comments

Comments

@htto
Copy link

@htto htto commented Nov 6, 2018

Which version and edition of Flyway are you using?

5.2.1

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

flyway commandline 5.2.1

Which database are you using (type & version)?

mysql --version
mysql Ver 15.1 Distrib 10.1.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Which operating system are you using?

ubuntu 18.10

What did you do?

Create two migrations with different variables in them, noticing the variable leak with a typo in the second migration:

V1__one.sql:
SELECT id INTO @var1 FROM table WHEREtext = 'text1';
...

V2__two.sql:
SELECT id INTO @var2 FROM table WHEREtext = 'text2';
INSERT INTO table (tid,value) VALUES (@var1, 'value2');

What did you expect to see?

Not having access to @var1 in migration V2, thus having the second migration always fail

What did you see instead?

The second migration V2 fails only if migrated without V1 (@var1 isn't known), but works with a clean before (@var1 is used from migration V1).

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 6, 2018

Hmm interesting. Is there a way to unset all user-defined variables with MySQL?

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 6, 2018

Digging deeper into this, it appears that starting with MySQL 5.7 the following should be possible:

  • SELECT variable_name FROM performance_schema.user_variables_by_thread WHERE variable_value IS NOT NULL
  • SET all those variables to NULL in a single SET @var1=NULL,@var2=NULL, ... statement

This should effectively plug this leak.

@axelfontaine axelfontaine added this to the Flyway 5.2.2 milestone Nov 6, 2018
@axelfontaine axelfontaine changed the title Flyway re-uses variables across migrations MySQL: User variables leak across migrations Nov 6, 2018
@axelfontaine axelfontaine changed the title MySQL: User variables leak across migrations MySQL: User-defined variables leak across migrations Nov 6, 2018
@htto
Copy link
Author

@htto htto commented Nov 6, 2018

Hmm looks like that table is not enabled per default:
ERROR 1146 (42S02): Table 'performance_schema.user_variables_by_thread' doesn't exist
thus probably not that portable.

Anyway, I thought flyway's parser would analyze the SQL statements figuring variables and reset them or at least reset the session to a consistent state across the migrations.

If this behavior is to be expected, it might be useful to state this in the documentation.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 6, 2018

For MariaDB 10.2 and later it appears the query should be replaced with SELECT variable_name FROM information_schema.USER_VARIABLES WHERE variable_value IS NOT NULL . See https://mariadb.com/kb/en/library/information-schema-user_variables-table/

The fix for this will therefore only be implemented by default for MariaDB 10.2+ and MySQL 5.7+.

For earlier MariaDB versions, the existence of this table should first be checked with SELECT count(*) FROM information_schema.PLUGINS WHERE plugin_name='user_variables' and plugin_status='ACTIVE'

@htto
Copy link
Author

@htto htto commented Nov 6, 2018

So I'm not getting a fix when running 10.1?

SELECT variable_name FROM information_schema.USER_VARIABLES WHERE variable_value IS NOT NULL;
ERROR 1109 (42S02): Unknown table 'user_variables' in information_schema

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 6, 2018

So I'm not getting a fix when running 10.1?

You must install the user_variables plugin for 10.1 for our upcoming fix to work for you. Read up on that topic in the MariaDB docs if you aren't familiar with this.

@htto
Copy link
Author

@htto htto commented Nov 7, 2018

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Nov 7, 2018

Good point. Then 10.2 will indeed be the minimum supported version for this fix.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants