Mariadb-docker: Rename Table twice raise error "Tablespace is missing for a table"

[ambrosiora]

I'm facing a problem when try to rename a table twice. I mean, the first rename works great, but the seccond attempt not.
Using mariadb:latest image when running docker containers on Windows 10 and mapping /var/mysql/lib folder to host.
If I do not map the persist volume, all works great...

Steps to reproduce the error:

Setup the docker-compose.yml

version: '3'
services:
  database_container:
    container_name: my_db
    image: mariadb:latest
    ports:
      - 3306:3306
    environment:
      MYSQL_DATABASE: mytestdb
      MYSQL_USER: usr_db
      MYSQL_PASSWORD: 123456
      MYSQL_ROOT_PASSWORD: 123456
    volumes:
      - ./db/persist:/var/lib/mysql

Run docker-compose up -d
connect with your favorite client (I'm using DBeaver Enterprise)
Run a create table statement:

CREATE TABLE NewTable (
    ID int primary key,
    name varchar(10)
)

Run a RENAME table statement like this:
RENAME TABLE mytestdb.NewTable TO mytestdb.NewTablea;

Then, run again, another RENAME the statement:
RENAME TABLE mytestdb.NewTablea TO mytestdb.NewTableb;

The error I'm getting is:
SQL Error [1025] [HY000]: (conn=4) Error on rename of './mytestdb/newtablea' to './mytestdb/newtableb' (errno: 194 "Tablespace is missing for a table")

Notes:
1 - When I install the MariaDB client via .msi installer for windows, all works fine
2 - I changed the variable lower_case_table_names to 1 because the default with windows is coming 2 for me, but with no success, the error still the same.
3 - I created the same container with mysql:latest image instead of mariadb and all works fine
4 - Tried to adjust folder permissions on windows, giving all permisions to anyone (like a chmod 777 on linux), but the erros still the same.

So I think that there's some problem with windows + mariadb:latest image

[wglambert]

I'm not able to reproduce on Linux, you could try filing this over at https://github.com/docker/for-win/issues

$ docker-compose up -d
Creating network "root_default" with the default driver
Creating my_db ... done

$ docker exec -it my_db mysql -uusr_db -p123456 -Dmytestdb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.7-MariaDB-1:10.5.7+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mytestdb]> CREATE TABLE NewTable (
    -> ID int primary key,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.007 sec)

MariaDB [mytestdb]> RENAME TABLE mytestdb.NewTable TO mytestdb.NewTablea;
Query OK, 0 rows affected (0.004 sec)

MariaDB [mytestdb]> RENAME TABLE mytestdb.NewTablea TO mytestdb.NewTableb;
Query OK, 0 rows affected (0.003 sec)

MariaDB [mytestdb]> exit

$ docker logs my_db 2>&1 | tail -n 5
2020-11-09 19:40:02 0 [Warning] 'proxies_priv' entry '@% root@6eb02c07c9fe' ignored in --skip-name-resolve mode.
2020-11-09 19:40:02 0 [Note] Reading of all Master_info entries succeeded
2020-11-09 19:40:02 0 [Note] Added new Master_info '' to hash table
2020-11-09 19:40:02 0 [Note] mysqld: ready for connections.
Version: '10.5.7-MariaDB-1:10.5.7+maria~focal'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

[ambrosiora]

Hi @wglambert
Yes, already have tested on linux and the problem not happens, you have to do this on windows.

I can try to fill the same issue on https://github.com/docker/for-win/issues
The issue is created there: https://github.com/docker/for-win/issues/9457

But, I guess they will tell me to talk with you guys, saying that the problem is with mariadb. I kind of agree with that, because with mysql image the problem not happens.

What do you think?

Can I help with something? Like, if you ask me to reproduce some scenarios on windows I can try...

[wglambert]

An issue like this isn't in our realm since it's only reproducible on Docker for Windows and doesn't occur on Linux.
There's nothing actionable we could do to alleviate this problem as maintainers of the image since it's related to the host's environment

https://success.mirantis.com/article/are-linux-containers-on-windows-supported

[grooverdan]

I've copied this issue to the MariaDB upstream https://jira.mariadb.org/browse/MDEV-24189 to see if they can identify the cause.

[vaintroub]

maybe trying Linux "native" lower_case_table_names=0 works. If not I'd also suggest to play with NTFS case-sensitivity https://www.windowscentral.com/how-enable-ntfs-treat-folders-case-sensitive-windows-10 , and then lower_case_table_names=0 . I do not think there are too many (or any?) tests on Innodb outside of Windows , on case-insensitive file system

[ambrosiora]

Thank you very much @grooverdan! I will keep on tracking this (already saved on my favorites 😉)

Hey @vaintroub about to set lower_case_table_names=0 I already have tried with fingers crossed haha, it was my first or second option I think... But on init a error is raised saying that the environment on the tablespaces is placed isn't case sensitive 😒
but thank you about enable ntfs case sensitive, I can test it, just to know if the error is about case-sensitivity.

[ambrosiora]

Hi @vaintroub

I just tested it. I enabled the case-sensitivity on persist folder and I was pretty confident that this workaround would really work. But it did not work =/

Setting lower_case_table_names = 0 and even trying to use lower_case_table_names = 1 to force all keep on lower case.
But with no positive results.
Maybe the problem isn't about case-sensitivity 🤔 but another things we don't know yet? 😂 The only thing I know is the MySQL container can handle it.

I opened side by side (mariadb and mysql) persist volume and figured out that even both containers is using InnoDB engines, the MySQL not create .frm files 😮 just the .ibd files...

I'm running out of ideas and, worked-around on windows using mysql for dev, and on production mode, keep using mariadb, because is linux environment there.

[Prosolve]

Had exactly the same issue also Docker running on Windows 10 with persistent volume. Error seemed to occur creating a key but not 100% sure of the steps. Happened though several times using HeiduSQL and/or MySQL Workbench designers. Got around it by copying the CREATE TABLE code (in HeidiSQL tool is is there as you edit), then deleting the table and re-creating the table using the CREATE TABLE code from a query prompt. Not ideal but it seemed to work

[grooverdan]

MDEV-24189 above shows this to be a WSL problem. However since no-one has put a bug report into https://github.com/microsoft/WSL it won't get fixed. This of course can be corrected. Please reference the above MDEV when putting in an issue.

[grooverdan]

WSL bug per MDEV-24189