Unable to alter DB on mac, when using local volumes

[kazeshini178]

Currently every time I run an ALTER statement on MySQL docker instance my database schema seems too corrupt.

Current attempted the following

docker run -e MYSQL_ROOT_PASSWORD=rootPassword -v /Users/[username]/project/db-data:/var/lib/mysql -p 3307:3306 mysql:5.7

also tried in a docker-compose

mysql:
    image: mysql:5.7
    environment:
      MYSQL_ROOT_PASSWORD: rootPassword
    volumes:
      - ./db-data:/var/lib/mysql
    ports:
      - "3307:3306"

When I run either of these update statements I get the errors/message below

ALTER TABLE `Sample` ADD `NewColumn` longtext CHARACTER SET 'utf8mb4' NULL;
-- OR
ALTER TABLE `Sample` 
ADD COLUMN `NewColumn` LONGTEXT NULL DEFAULT NULL;

2021-02-11T15:01:33.380914Z 11 [Warning] InnoDB: Table sample contains 20 user defined columns in InnoDB, but 19 columns in MySQL. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-02-11T15:01:33.380954Z 11 [Warning] InnoDB: Cannot open table sample from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2021-02-11T15:01:46.852926Z 3 [Note] InnoDB: Table sample is corrupted. Please drop the table and recreate it
2021-02-11T15:01:46.852947Z 3 [Warning] InnoDB: Cannot open table sample from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

The part the confuse me is if don't use a volume that is bound to a local folder, and use a docker volume (not 100% how to describe the difference). So when I use the following everything seems to work

docker run -e MYSQL_ROOT_PASSWORD=rootPassword -v db-data:/var/lib/mysql -p 3307:3306 mysql:5.7

I think it's something specific to macOS as everything above works on Windows without any issues.

[wglambert]

Docker for Mac's filesystem osxfs and the way it interfaces with Linux containers can sometimes have these bind-mount issues. So the workaround you found with using Docker's named volumes is the way to go
https://collabnix.com/how-docker-for-mac-works-under-the-hood/

There's a couple threads with similar issues:

MariaDB/mariadb-docker#95 (comment)

Essentially, the best solution is to put /var/lib/mysql in a volume on your Docker VM (managed via docker volume xyz commands) instead of trying to share those files directly all the way back to your Mac or Windows host filesystem.

MariaDB/mariadb-docker#95 (comment)

Docker and MariaDB work fine together; the bit that doesn't work is the custom filesystem employed by Docker for Windows and Docker for Mac for sharing files across the VM boundary, which is not all that surprising for a database, which often use features like mmap for performance but thus also require support from the underlying filesystem. There are many reports of similar issues with vboxsf, for example.

MariaDB/mariadb-docker#95 (comment)

Any fixes would have to happen either in Docker's shared filesystem or in MariaDB itself (not something we can really fix in this Docker image), so I'd recommend checking their respective upstream bugtrackers for any discussion of fixing the problem.

[wglambert]

Going to close since there's nothing actionable that we could do to alleviate this

You could also try searching through the mac issues https://github.com/docker/for-mac/issues or asking over at the Docker Community Forums, Docker Community Slack, or Stack Overflow.