Can't run a drush sql-dump - Database dump failed because Table '/tmp/#sql_1_0.MAI' is read only

[bdeclerc]

I'm using the docker4drupal resources to build me a labserver for drupal-sites, with the site-files residing outside of the docker-containers.

I've tried this for both Drupal 7 & Drupal 8 and get the same issue in both - inside a running site, I can modify data without any problems, using drush sqlc I can do things like "truncate table XXX;" but when I execute "show fields from XXX;" from drush sqlc I get this same error:

I'm trying to get a database-dump (or a drush ard) through drush using
docker-compose exec --user 82 php drush -r ./web/ sql-dump
but I get the error at the bottom - I'm including my current docker-compose.yml, I have similar setups for Drupal 7 site (with PHP5.6) and I've tried it with externally hosted db-datafiles and in-container db-datafiles, always the same result.

`version: "2"

services:
mariadbrefsite:
image: wodby/mariadb:10.1-2.1.0
environment:
MYSQL_ROOT_PASSWORD: password
MYSQL_DATABASE: siterefsite
MYSQL_USER: siterefsite
MYSQL_PASSWORD: drupal
networks:
- backend
volumes:
- /home/fedict/db/refsite/mariadb-init:/docker-entrypoint-initdb.d # Place init .sql file(s) here.

php:
image: wodby/drupal-php:7.1-2.1.0
environment:
PHP_SENDMAIL_PATH: /usr/sbin/sendmail -t -i -S mailhog:1025
DB_HOST: mariadbrefsite
DB_USER: siterefsite
DB_PASSWORD: drupal
DB_NAME: siterefsite
DB_DRIVER: mysql
networks:
- backend
volumes:
- /home/fedict/www/refsite/:/var/www/html

nginx:
image: wodby/drupal-nginx:8-1.10-2.1.0
depends_on:
- php
environment:
NGINX_STATIC_CONTENT_OPEN_FILE_CACHE: "off"
NGINX_ERROR_LOG_LEVEL: debug
NGINX_BACKEND_HOST: php
NGINX_SERVER_ROOT: /var/www/html/web
networks:
- web
- backend
volumes:
- /home/fedict/www/refsite/:/var/www/html/
labels:
- 'traefik.port=80'
- 'traefik.frontend.rule=Host:refsite.drulab.bosa.be'
- 'traefik.docker.network=traefik_webgateway'

volumes:
codebase:

networks:
web:
external:
name: traefik_webgateway
backend:
driver: bridge
`

• Host OS: RHEL 7.3
• Docker version: 17.03.1-ce
• Logs output:
  ` docker-compose exec --user 82 php drush -r ./web/ sql-dump
  -- MySQL dump 10.16 Distrib 10.1.21-MariaDB, for Linux (x86_64)
  --
  mysqldump: Error: 'Table '/tmp/#sql_1_2.MAI' is read only' when trying to dump tablespaces
  -- Host: mariadbrefsite Database: mariadbrefsite

---

-- Server version 10.1.22-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table batch

DROP TABLE IF EXISTS batch;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE batch (
bid int(10) unsigned NOT NULL COMMENT 'Primary Key: Unique batch ID.',
token varchar(64) CHARACTER SET ascii NOT NULL COMMENT 'A string token generated against the current user''s session id and the batch id, used to ensure that only the user who submitted the batch can effectively access it.',
timestamp int(11) NOT NULL COMMENT 'A Unix timestamp indicating when this batch was submitted for processing. Stale batches are purged at cron time.',
batch longblob COMMENT 'A serialized array containing the processing data for the batch.',
PRIMARY KEY (bid),
KEY token (token)
mysqldump: Couldn't execute 'show fields from batch': Table '/tmp/#sql_1_0.MAI' is read only (1036)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Stores details about batches (processes that run in…';
/!40101 SET character_set_client = @saved_cs_client */;
Database dump failed `

[ericmulder1980]

I'm having the exact same issue. I am running a project on my Mac and exported the database to a 'production' environment on a CentOS 7 server.

I can do all sorts of selects but in a few cases i get issues with a 'read only' tmp table. I have checked if i can write to the /tmp directory within the mariadb container when logging in as the mysql user (uid 100).

One of the queries that gives me the error is:

SELECT DISTINCT b.* FROM block b LEFT JOIN block_role r ON b.module = r.module AND b.delta = r.delta

I am clueless at this point.

[ericmulder1980]

I fixed my problem for now by reverting back to an old version of MariaDB (wodby/drupal-mariadb:1.0.0).

Ignore that. I was using wodby/mariadb and now replaced it with wodby/drupal-mariadb. Solved my problem for now.

[bdeclerc]

Rolling back to wodby/mariadb:2.0.0 does fix it - the wodby/drupal-mariadb repo seems like it's an old one that isn't updated any more?

[csandanov]

I didn't have time to look closely into this issue but try to use the latest non-tagged image wodby/mariadb:10.1

[ericmulder1980]

My first guess would be that is has something to do with updating mariadb to 10.1.22-r0. I'll try to compare the versions and see if it changes anything.

[bdeclerc]

Going to the non-tagged version doesn't help, rolling back to wodby/mariadb:10.1:2.0.0 does.

[csandanov]

I've just rebuilt a non-tagger version to 10.1.22-r1 (alpine 3.6). Try it out. Also, we had a lot of changes in mariadb config (performance tuning) in the last versions but I think it was after 2.1.0.

[bdeclerc]

Tried with the latest non-tagged version, but the error remains:
mysqldump: Error: 'Table '/tmp/#sql_1_2.MAI' is read only' when trying to dump tablespaces mysqldump: Couldn't execute 'show fields from actions': Table '/tmp/#sql_1_0.MAI' is read only (1036) Database dump failed

[csandanov]

Could you please attach the SQL file that can be used to reproduce the issue. Perhaps we should post a bug report to mariadb or alpine team.

[bdeclerc]

It happens even on a basic Drupal installation like this one:
0000.sql.gz
I managed to get a sql-dump by rolling back to the 2.0.0 image without deleting the database files.

[Vanuan]

The issue isn't specific to drupal, it looks to be specific to some combination of kernel options and alpine package.

I can only reproduce it in alpine package of mariadb.

Host OS: CentOS 7.4
Container OS: alpine:3.7
Dockerfile commands:

apk --update add mysql mysql-client # 10.1.28

Install commands:

# create user, permissions
...
mysql_install_db --user=mysql > /dev/null`
...

This query fails consistently:

SHOW CREATE TABLE FROM `some_table`

Also SHOW COLUMNS and other similar queries

Here's strace of a failing query:

recvfrom(42, "'\0\0\0", 4, MSG_DONTWAIT, NULL, NULL) = 4
recvfrom(42, "\3SHOW FULL FIELDS FROM `schema_v"..., 39, MSG_DONTWAIT, NULL, NULL) = 39
open("/tmp/#sql_100_0.MAI", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_PATH) = -1 ENOENT (No such file or directory)
open("/tmp/#sql_100_0.MAI", O_RDWR|O_CREAT|O_TRUNC|O_NOFOLLOW|O_CLOEXEC, 0660) = 43
fcntl(43, F_SETFD, FD_CLOEXEC)          = 0
write(43, "\376\376\t\3\4\20\3,\0\327\0\207\0\327\0\0\0\0\0\0\0\0\3\3\0\0\0\0\0\0\0\0"..., 215) = 215
write(43, "\332\375\250\336\365\215\21\347\255(\2B\300\250\0\6\0\0\0\0\0\0 \0\377\377\377\377\377\377\377\377"..., 135) = 135
write(43, "\0\0\0\0\0\0\0\1\0\1\0\0\0\0\0\0\0\0\0\0", 20) = 20
write(43, "\0\5\10F\0\0\0\10\0\10\0\0\0\0\0\0\0\0\0\0", 20) = 20
write(43, "\0\t\t%\0\0\0\10\0\10\0\0\0\0\2\0\0\0\0\0", 20) = 20
write(43, "\0\n\t-\0\0\0\10\0\10\0\0\0\0\4\0\0\0\0\0", 20) = 20
write(43, "\0\v\t5\0\0\0\10\0\10\0\0\0\0\10\0\0\0\0\0", 20) = 20
write(43, "\0\f\t=\0\0\0\10\0\10\0\0\0\0\20\0\0\0\0\0", 20) = 20
write(43, "\0\r\tE\0\0\0\10\0\10\0\0\0\0 \0\0\0\0\0", 20) = 20
write(43, "\0\1\0\1\0\10\6\2\0\2\0\0\0\0\0\1\0\0\0\0", 20) = 20
write(43, "\0\2\6\3\0\10\0\301\0\1\0\0\0\0\0\2\0\0\0\0", 20) = 20
write(43, "\0\3\6\304\0\10\0\301\0\1\0\0\0\0\0\4\0\0\0\0", 20) = 20
write(43, "\0\4\7\205\0\10\0\301\0\1\0\0\0\0\0\10\0\0\0\0", 20) = 20
write(43, "\0\7\10Z\0\10\0\n\0\1\0\0\0\0\0 \0\0\0\0", 20) = 20
write(43, "\0\10\10d\0\10\0\301\0\1\0\0\0\0\0@\0\0\0\0", 20) = 20
write(43, "\0\16\tM\0\10\0a\0\1\0\0\0\0@\200\0\0\0\0", 20) = 20
write(43, "\0\17\t\256\0\10\0a\0\1\0\0\0\1\200\1\0\0\0\0", 20) = 20
write(43, "\0\21\n\33\0\10\0\n\0\1\0\0\0\1\0\4\0\0\0\0", 20) = 20
write(43, "\0\22\n%\0\10\0R\0\1\0\0\0\1\0\10\0\0\0\0", 20) = 20
write(43, "\0\23\nw\0\10\0\361\0\1\0\0\0\1\0\20\0\0\0\0", 20) = 20
write(43, "\0\24\vh\0\10\f\2\0\2\0\0\0\1\0 \0\0\0\0", 20) = 20
write(43, "\0\6\10N\0\4\0\f\0\f\0\0\0\0\1\20\0\0\0\0", 20) = 20
write(43, "\0\20\n\17\0\4\0\f\0\f\0\0\0\1\0\2\0\0\0\0", 20) = 20
write(43, "\0\0\7\0\10\0\t\0\n\0\1\0\23\0\v\0\f\0\2\0\3\0\4\0\5\0\6\0\r\0\16\0"..., 42) = 42
lseek(43, 0, SEEK_CUR)                  = 812
open("/tmp/#sql_100_0.MAD", O_RDWR|O_CREAT|O_TRUNC|O_NOFOLLOW|O_CLOEXEC, 0660) = 44
fcntl(44, F_SETFD, FD_CLOEXEC)          = 0
lseek(44, 0, SEEK_END)                  = 0
write(44, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096
write(44, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4092) = 4092
pwrite64(44, "\376\377\377\377", 4, 8188) = 4
lseek(43, 0, SEEK_END)                  = 812
write(43, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 4096) = 4096
write(43, "\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 3284) = 3284
close(44)                               = 0
close(43)                               = 0
open("/tmp/#sql_100_0.MAI", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_PATH) = 43
readlink("/proc/self/fd/43", 0x7f0f52f94bc0, 4095) = -1 EACCES (Permission denied)
close(43)                               = 0
open("/tmp/#sql_100_0.MAI", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_PATH) = 43
readlink("/proc/self/fd/43", 0x7f0f52f94b60, 4095) = -1 EACCES (Permission denied)
close(43)                               = 0
lstat("/tmp/#sql_100_0.MAI", {st_mode=S_IFREG|0660, st_size=8192, ...}) = 0
unlink("/tmp/#sql_100_0.MAI")           = 0
lstat("/tmp/#sql_100_0.MAD", {st_mode=S_IFREG|0660, st_size=8192, ...}) = 0
unlink("/tmp/#sql_100_0.MAD")           = 0
lstat("/tmp/#sql_100_0.TMD", 0x7f0f52f99800) = -1 ENOENT (No such file or directory)
unlink("/tmp/#sql_100_0.TMD")           = -1 ENOENT (No such file or directory)
lstat("/tmp/#sql_100_0.OLD", 0x7f0f52f99800) = -1 ENOENT (No such file or directory)
unlink("/tmp/#sql_100_0.OLD")           = -1 ENOENT (No such file or directory)
sendto(42, "1\0\0\1\377\f\4#HY000Table '/tmp/#sql_10"..., 53, MSG_DONTWAIT, NULL, 0) = 53
recvfrom(42, 0x55d177a27b48, 4, MSG_DONTWAIT, NULL, NULL) = -1 EAGAIN (Resource temporarily unavailable)
poll([{fd=42, events=POLLIN|POLLPRI}], 1, 28800000

Here's an output:

MariaDB [test]> SHOW FULL FIELDS FROM `schema_version`;
ERROR 1036 (HY000): Table '/tmp/#sql_100_0.MAI' is read only

[Vanuan]

Found a bug report in Alpine: https://bugs.alpinelinux.org/issues/7345

[csandanov]

Since 2.5.0 we use new mariadb image compiled from sources instead of the alpine package.

[Vanuan]

@csandanov Is that still alpine based? Does it use musl?

[Vanuan]

Here are my findings:
https://stackoverflow.com/a/48178214/99024

[csandanov]

Yes, it's still alpine-based, does anyone have the issue with wodby/mariadb:10.1-3.1.2 (we also have 10.2 now)?

[Vanuan]

@csandanov the issue is reproducible since mariadb 10.1.22 on kernels below 4.4

So either upgrade kernel or downgrade mariadb. Or don't use alpine/musl

[csandanov]

What is your docker storage driver? If it's reproducible with a certain version of kernel there's a good chance it could be fixed by changing the driver, overlay2 proved itself as quite stable with kernel 4.x