I don't see anything wrong with the result. one-database means "Ignore statements except those that occur while the default database is the one named at the command line." Naturally the dump is not meant to be executed selectively. If only one database is to be restored, then the dump should be produced accordingly, mysqldump is perfectly capable of it, and the client's one-database is not a replacement.

Did you find somewhere in the documentation that it is supposed to work like this?

Hi Elena

OK. Let us separate the discussion into different parts:

1.) --one-database

I refer to the same text like you and agree. I go a bit further and look at documentation.

MariaDB: https://mariadb.com/kb/en/library/mysql-command-line-client/

"Ignore statements except those those that occur while the default database is the one named on the command line. This filtering is limited, and based only on USE statements. This is useful for skipping updates to other databases in the binary log."

MySQL 5.5 ff.: https://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_one-database

Where they have a nice example of the use case.

So both examples have some indications for "use it for restore/pitr". That is the reason I ASSUMED (bad!) that this option is intended for selective restore of a schema from a full logical backup. One has to ask the inventor of this option what was the original idea...

Further it can very easily be fixed by inserting a "USE `nonsense`;" before the terminating SET statements of the backup.

2.) How is dump done in real world

We see often in real world that people do a backup with --all-databases (because of consistency, because setting up a slave, etc.). But during restore they find out, that they only want to restore ONE schema. And then it is too late! Further MariaDB/MySQL do not provide, like other enterprise databases, a restore of only a part of the database from a full backup (mariabackup cannot do either, thus we always have to recommend logical schema backup as well). So --one-database would be the perfect fit to fill this gap.

So if this is a bug or a feature request one can discuss a lot. We can work around it in our tools to make it work smoothly. But for your product I would an advantage IMHO.

"If only one database is to be restored, then the dump should be produced accordingly, mysqldump is perfectly capable of it, and the client's one-database is not a replacement. "

My assumptions proved to be true:

Some more tests:

This does not work either:

1. mysqldump --user=root --databases world > world_dump1.sql
2. cat world_dump1.sql | mysql --user=root --one-database --database=world
   ERROR 1231 (42000) at line 115: Variable 'time_zone' can't be set to the value of 'NULL'

This is the only way it works:

1. mysqldump --user=root world > world_dump2.sql
2. cat world_dump2.sql | mysql --user=root --one-database --database=world

But in this case it completely makes no sense...

Further it can very easily be fixed by inserting a "USE `nonsense`;" before the terminating SET statements of the backup.

First, you can't – it will simply fail on this USE statement when you try to restore it, instead of failing on SET.
But more importantly, those initial SET statements which you skip when you run one-database are not just useless garbage, they are actually needed in many cases. It's very likely that real-life restore will not work without them.

But in this case it completely makes no sense...

It does make sense if you accept the fact that one-database option has nothing to do with mysqldump, it's completely unrelated. For why one-database was invented – I can't say, but you can't make every script or tool be aware of all permutations of all other scripts or tools options and produce the output to satisfy them all. Yes, if you run mysqldump with only one database, it could be changed to write SET statements after USE. But what if you run mysqldump --database world test (which you can just as easily do)? How will it solve anything?

MariaDB/MySQL do not provide, like other enterprise databases, a restore of only a part of the database from a full backup

I would agree it's a reasonable feature request.

Possibly it could be done by writing all SET statements (forth and back) for each database separately. It's an overhead, but a negligible one, since SET is fast. However, I didn't do any analysis of what else can go wrong, it's just a quick thought.

I did the tests for our Ops Center and the Backup/Recovery Manager. Our Ops Center should be capable to do automated restore tests together with the rman...

So I have to add a:

USE `world`;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

in front of the dump as it is done in the dump itself then it should work. Unfortunately there is no UNUSE or similar..

This seems to work:

| mysql --user=root --one-database --database=world

But as you said one should think a bit more about the impact...