Thanks to a colleague I can do this: module load prog/kubectl.
Next we need to authenticate, but we will skip that because I have already done it!
Now, we need to find the namespace. How do we find the namespace? kubectl get namespaces. That lists everything. Including all the stuff we are not interested. I happen to know my namespace is mariadb.
me@desktop:~$ ./bin/kubectl get namespace mariadb
NAME STATUS AGE
mariadb Active 685d
aha! pods?
me@desktop:~$ ./bin/kubectl get pods --namespace=mariadb
NAME READY STATUS RESTARTS AGE
und-prod-mariadb-galera-0 1/1 Running 0 3d3h
oh! shell? kubectl help suggests attach or run. There is
also cp which we can use to transfer files out of the container. That may be usefull
later.
shell.
me@desktop:~$ kubectl help exec
Execute a command in a container.
Examples:
;; cut ;;
# Switch to raw terminal mode, sends stdin to 'bash' in ruby-container from pod mypod
# and sends stdout/stderr from 'bash' back to the client
kubectl exec mypod -c ruby-container -i -t -- bash -il
;; cut ;;
Hm, which container. The pod has multiple. How about kubectl get containers?
No, get does not know how to get containers directly. However, kubectl get pod und-prod-mariadb-galera-0
jondy94@tlhw-3-1:~$ kubectl get pod und-prod-mariadb-galera-0
Error from server (NotFound): pods "und-prod-mariadb-galera-0" not found
Erm, did I misspell? No. Does it want a namespace?
me@desktop:~$ kubectl get pod und-prod-mariadb-galera-0 --namespace=mariadb
NAME READY STATUS RESTARTS AGE
und-prod-mariadb-galera-0 1/1 Running 0 3d3h
That did the same thing as kubectl get pods --namespace=mariadb. However,
-o yaml! Turns out that -o yaml works just as well with get pod
as it does with get pods. The yaml output is long, so I wont quote it here.
So, can we get a shell now? Yes.
me@desktop:~$ kubectl exec und-prod-mariadb-galera-0 -c mariadb -i -t -- bash -il
Error from server (NotFound): pods "und-prod-mariadb-galera-0" not found
Erm, right, namespace.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -i -t -- bash -il
root@und-prod-mariadb-galera-0:/# ps aux
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 1 0.3 1.9 2779108 483956 ? Ssl Aug21 16:04 mysqld
root 160963 0.0 0.0 4616 664 pts/0 Ss+ 09:11 0:00 /bin/sh -c TERM=xterm-256color; export TERM; [ -x /bin/bash ] && ([ -x /usr/bin/script ] && /usr/bin/
root 160969 0.0 0.0 4616 92 pts/0 S+ 09:11 0:00 /bin/sh -c TERM=xterm-256color; export TERM; [ -x /bin/bash ] && ([ -x /usr/bin/script ] && /usr/bin/
root 160970 0.0 0.0 19296 956 pts/0 S+ 09:11 0:00 /usr/bin/script -q -c /bin/bash /dev/null
root 160971 0.0 0.0 4616 404 pts/1 Ss 09:11 0:00 sh -c /bin/bash
root 160972 0.0 0.0 18496 2072 pts/1 S+ 09:11 0:00 /bin/bash
root 167037 0.0 0.0 18496 2048 pts/2 Ss 11:56 0:00 bash -il
root 167072 0.0 0.0 34388 1552 pts/2 R+ 11:56 0:00 ps aux
Yay! We have a shell. Yes yes, containerization-inqisitors, the container probably should not have bash and other stuff in it. I just work here alright. Now. Grabbing all the sweet data. Just a slight problem.
As it turns out I only have basic knowledge of mariadb and mysql management so I have only a
vague idea of how to copy the data. I am aware of a tool named mysqldump. What does man mysqldump say?
NAME
mysqldump - a database backup program
SYNOPSIS
mysqldump [options] [db_name [tbl_name ...]]
DESCRIPTION
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce
the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to
another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
That sounds about right. That being said, it would be interesting if we could do this copying more directly, instead of first exporting to a file, copying that and then importing. Because if there is a direct copy tool that would only be one step! So is there such a tool?
Well the MySQL 5.7 Reference Manual: Copying MySQL Databases to Another Machine explains that mysqldump can actually do that too, because it can output the data on stdout and then we can just stream that to the destination machine. However, on second thought we will dump to a file. Why? Backups. (There are other backups in this system, but having one of my own makes it easier to checkpoint and compare.) If it turns out the dump-files become to large (unlikely in this case) we can revisit streaming at that point.
So, lets compose a mysqldump invocation. The MySQL Reference Manual suggests
mysqldump --quick db_name | gzip > db_name.gz. Which is fine, if I knew the name of
the database. Which I do not. So, how do we discover the names of our databases.
Well, the MySQL Reference Manual does mention this tool called mysqladmin. It does have a suggestive name. Cue man mysqladmin.
NAME
mysqladmin - client for administering a MySQL server
SYNOPSIS
mysqladmin [options] command [command-options] [command [command-options]] ...
DESCRIPTION
mysqladmin is a client for performing administrative operations. You can use it to check the server's configuration and
current status, to create and drop databases, and more.
Invoke mysqladmin like this:
shell> mysqladmin [options] command [command-arg] [command [command-arg]] ...
Sounds about right. But not quite. Searching for "database" does not provide a way to list them, only creating and dropping them. (People that know MySQL are probably throwing stuff at their monitors at this point. Have patience. Get another coffe.) If we look around some more in the MySQL Reference Manual it turns out there is a subsection called Overview of MySQL Programs. As the title suggests it provides an overview of the tools. It is a good idea to seek out these kinds of pages when you are learning a new system, even before you start actually working. Otherwise you may be doing complicated things unecessarily.
Now, the mysql command-line tool is for executing SQL statements. I do know a little bit of SQL, for example SHOW DATABASES. How do we run that using mysql in the container? How about:
root@und-prod-mariadb-galera-0:/# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
That is a failure. At least the tool is installed, I almost expected it to not be included in
the container. So, how do we authenticate? What exactly does using password: NO mean?
Is it saying we did not use a password (which we did not do) or that we cannot use a password?
How about we actually read the docs for once.
So, typing mysql reference manual error 1045 access denied for user into Google we
find Troubleshooting Problems Connecting to MySQL.
It explains that It means a root password has already been assigned during installation and it
has to be supplied. Fair enough, there is a root password assigned and I happen to know
what it is. So:
root@und-prod-mariadb-galera-0:/# mysql -p
Enter password: obviously_not_the_actual_password
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 55270
Server version: 10.3.10-MariaDB-1:10.3.10+maria~bionic 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 [(none)]>
Yay! Do we have databases?
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| auser001 |
| auser002 |
| auser003 |
....
| buser001 |
....
| information_schema |
....
| mysql
....
| performance_schema
....
| test |
| teststuff |
| testtest |
....
+--------------------+
3045 rows in set (0.024 sec)
There we go. Lots of databases with names corresponding to the totally real usernames. (Always anonymize the data before publishing it!) Also, some garbage to clean out apparently.
Turns out there are a lot of databases. It will be a pain to dump each one individually. Is
there perhaps a simpler way? Well, after some more digging in the reference manual we find
Dumping Data in SQL Format with mysqldump.
So there is a --all-databses. So we did not actually need the names. Oh well. Before
we dump everything, do we have space to actually dump everything at once in the container?
root@und-prod-mariadb-galera-0:/# df -h
Filesystem Size Used Avail Use% Mounted on
overlay 230G 154G 64G 71% /
tmpfs 64M 0 64M 0% /dev
tmpfs 12G 0 12G 0% /sys/fs/cgroup
shm 64M 0 64M 0% /dev/shm
tmpfs 12G 421M 12G 4% /etc/hostname
/dev/sda1 230G 154G 64G 71% /etc/hosts
/dev/rbd0 63G 25G 39G 39% /var/lib/mysql
tmpfs 12G 12K 12G 1% /run/secrets/kubernetes.io/serviceaccount
tmpfs 12G 0 12G 0% /proc/acpi
tmpfs 12G 0 12G 0% /proc/scsi
tmpfs 12G 0 12G 0% /sys/firmware
Containers are weird. Or at least this one is. So /etc/hosts is 154G. Pretty sure that is some Kubernetes shennanigans going on right there. Note, /var/lib/mysql currently uses 25G. That is
our data in a reasonably compact binary format. The dump made by mysqldump will be text and will probably be larger. However we have about 64 gigabyte in the root filesystem. Which may not be enough, but could work. We will add some compression and hope for the best.
So, to dump all of the data, we would run something like this:
mysqldump --all-databases --routines | gzip > dump.tgz
Now, before we run this there are two things to remember. The lifetime of the shell we are running inside and where the logs of this command go! It is easy to forget, but we do not want this to be interrupted if say our desktop machine powers off and we also do not want to loose important messages printed on stderr. So, lets first redirect stderr to a file. Also, lets move to homedir of root.
cd /root && mysqldump --all-databases --routines 2>mysqldump-stderr.log | gzip > dump.tgz
Now, this will loose errors printed by gzip, so lets put that in another file, just in case. (Like, OOM or write errors.) Finally, how do we make sure this does not get interrupted? Screen? Tmux?
cd /root && mysqldump --all-databases --routines 2>mysqldump-stderr.log | gzip > dump.tgz 2>gzip-stderr.log
root@und-prod-mariadb-galera-0:/# screen
bash: screen: command not found
root@und-prod-mariadb-galera-0:/# tmux
bash: tmux: command not found
Well, those are not supposed to be in there. Can we use kubectl to start persistent processes in a container? Even if they are not in the original configuration? So, remember how we started an interactive shell before. What if we just make it not interactive and not allocate a pseudo terminal?
No -i or -l to bash, because we do not wish to interact nor start a login shell. Also remove -i from kubectl since we do not wish to pass our local stdin to the container. Finally, no -t to kubectl because stdin is not going to be a TTY. Also, lets replace bash with, uh, sleep.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- sleep 120
So, I expected kubectl to terminate at once and leave sleep running in the container. However kubectl will wait for the process to terminate. That being said, kubectl will not terminate processes that it has started if kubectl itself is interrupted or killed. So, we can just press CTRL-C or otherwise kill or local kubectl process. The sleep process in the container continues to run. So, now we are ready!
Now, because the exporting may take a while, we will do a smaller test run on just the mysql database. Just to se that we have got the correct idea.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- mysqldump --databases mysql --routines 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log
Lets run it.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- mysqldump --databases mysql --routines 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log
bash: /root/mysql_dump.tgz: Permission denied
bash: /root/mysqldump-stderr.log: Permission denied
Well. Right. We need to wrap the command that is intended for the container so that it is not interpreted by our local shell.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- bash -c "mysqldump --databases mysql --routines 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log"
It finished almost instantly. Did we get our files?
root@und-prod-mariadb-galera-0:/# cd /root
root@und-prod-mariadb-galera-0:~# ls -l
total 8
-rw-r--r-- 1 root root 0 Aug 24 14:21 gzip-stderr.log
-rw-r--r-- 1 root root 20 Aug 24 14:21 mysql_dump.tgz
-rw-r--r-- 1 root root 116 Aug 24 14:21 mysqldump-stderr.log
root@und-prod-mariadb-galera-0:~# cat mysqldump-stderr.log
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
Ah, password. Adding passwords to the commandline is not so nice. How does the mysqld process inside the container know the root password? Well, if we take a look at the yaml data from our previous kubectl experiments, we find something interesting. Run this:
kubectl get pods --namespace=mariadb -o yaml
- env:
- name: POD_NAMESPACE
valueFrom:
fieldRef:
apiVersion: v1
fieldPath: metadata.namespace
- name: MYSQL_ROOT_PASSWORD
valueFrom:
secretKeyRef:
key: mariadb-root-password
name: und-prod-mariadb-galera
- name: GALERA_SERVICE
value: und-prod-mariadb-galera-cluster
- name: MYSQL_INITDB_SKIP_TZINFO
value: "1"
This does not tell us the password, but it does tell us that there should be an environment variable called MYSQL_ROOT_PASSWORD. Well, is there one?
root@und-prod-mariadb-galera-0:~# env | grep MYSQL_ROOT_PASSWORD
MYSQL_ROOT_PASSWORD=this_is_where_the_password_would_be
Ah. So we can just juse that environment variable on the command line to mysqldump? Lets try it.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- bash -c "mysqldump --password=$MYSQL_ROOT_PASSWORD --databases mysql --routines 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log"
That also finished quickly. Did it fail?
root@und-prod-mariadb-galera-0:~# cat mysqldump-stderr.log
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
But we provided the password? (It is listed twice because we are not deleting our logs between runs.) Well, no, because we did not escape the dollar sign. The variable was looked up in our local shell, not in the container. So lets escape it and try again.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- bash -c "mysqldump --password=\$MYSQL_ROOT_PASSWORD --databases mysql --routines 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log"
Now there was a slight delay before it finished. So maybe it did something this time around.
root@und-prod-mariadb-galera-0:~# ls -l
total 3696
-rw-r--r-- 1 root root 0 Aug 24 14:21 gzip-stderr.log
-rw-r--r-- 1 root root 3778070 Aug 24 14:38 mysql_dump.tgz
-rw-r--r-- 1 root root 232 Aug 24 14:36 mysqldump-stderr.log
root@und-prod-mariadb-galera-0:~# cat mysqldump-stderr.log
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
Ah, we got actual data in mysql_dump.tgz and no new error messages. Also, it may be
a good idea to add date and time information to these kinds of batch commands, so that you can see
where the logs start and stop. It may be a good idea to add sequence numbers or datetimestamps to
the filenames as well. Personally I do not like deleting or overwriting the logs because it is easy
to accidentally loose usefull information.
Now we will transfer that to our local machine and inspect the generated SQL code. Then we will attempt to apply it to the destination machine!
me@desktop:~$ kubectl cp mariadb/und-prod-mariadb-galera-0:/root/mysql_dump.tgz ~/mysql_dump.tgz
tar: Removing leading `/' from member names
me@desktop:~$ ls -h ~/mysql_dump.tgz
-rw-r--r-- 1 me employee 3.7M Aug 24 16:50 /tmp/mysql_dump.tgz
Seems like kubectl cp always uses tar internally, so we get some spurios output from tar. Lets unpack it and take a look. Wait a second.
me@desktop:~$ file mysql_dump.tgz
mysql_dump.tgz: gzip compressed data, last modified: Mon Aug 24 14:38:29 2020, from Unix
me@desktop:~$ gunzip mysql_dump.tgz
me@desktop:~$ ls -l mysql_dump.tar
-rwx------ 1 jondy94 employee-liu.se 42006632 Aug 24 16:56 mysql_dump.tar
me@desktop:~$ file mysql_dump.tar
mysql_dump.tar: UTF-8 Unicode text, with very long lines
me@desktop:~$ head mysql_dump.tar
-- MySQL dump 10.16 Distrib 10.3.10-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost Database: mysql
-- ------------------------------------------------------
-- Server version 10.3.10-MariaDB-1:10.3.10+maria~bionic
/*!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 */;
Oookey. I guess the kubectl cp command is really only for directories?
Also, I am starving. So, see you next time.
Now, lets see how if we can dump the rest.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- bash -c "mysqldump --password=\$MYSQL_ROOT_PASSWORD --all-databases --routines 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log"
This will take a while. We should keep an eye on the disk and memory usage. Just in case. I usually use something like watch --interval 1 'df -h; free -m' and top in another terminal.
Huh. It stopped. Was there errors? Uh-oh.
root@und-prod-mariadb-galera-0:~# cat mysqldump-stderr.log
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
mysqldump: Couldn't execute 'SHOW CREATE FUNCTION `generateResNo`': Failed to load routine user1234.generateResNo. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) (1457)
That last line is new and troublesome. So, does mysql.proc exist? Lets find out.
root@und-prod-mariadb-galera-0:~# mysql -u root --password=$MYSQL_ROOT_PASSWORD mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 69893
Server version: 10.3.10-MariaDB-1:10.3.10+maria~bionic 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 [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.001 sec)
MariaDB [mysql]>
It does exist. That leaves corruption or "bad data". How do we tell these apart? Also, what is mysql.proc and what kind of data does it contain? Again, lets find the docs, for MariaDB it appears to be mysql.proc Table - MariadDB Knowledge Base. "Knowledge Base" sure sounds like another good reference. That page does have a table listing. Lets compare it to what is in our mysql.proc.
MariaDB [mysql]> DESCRIBE proc;
+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-------------------------------+
| db | char(64) | NO | PRI | | |
| name | char(64) | NO | PRI | | |
| type | enum('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') | NO | PRI | NULL | |
| specific_name | char(64) | NO | | | |
| language | enum('SQL') | NO | | SQL | |
| sql_data_access | enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA') | NO | | CONTAINS_SQL | |
| is_deterministic | enum('YES','NO') | NO | | NO | |
| security_type | enum('INVOKER','DEFINER') | NO | | DEFINER | |
| param_list | blob | NO | | NULL | |
| returns | longblob | NO | | NULL | |
| body | longblob | NO | | NULL | |
| definer | char(141) | NO | | | |
| created | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| modified | timestamp | NO | | 0000-00-00 00:00:00 | |
| sql_mode | set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT') | NO | | | |
| comment | text | NO | | NULL | |
| character_set_client | char(32) | YES | | NULL | |
| collation_connection | char(32) | YES | | NULL | |
| db_collation | char(32) | YES | | NULL | |
| body_utf8 | longblob | YES | | NULL | |
| aggregate | enum('NONE','GROUP') | NO | | NONE | |
+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------------------+-------------------------------+
21 rows in set (0.001 sec)
That is a lot of horizontal scrolling. Looks like our local table agrees with the documentation. So the structure of the table is ok. That leaves bad data in some row or data corruption. So, we need to learn more about Stored Procedures which we can read about at Stored Procedure Internals - MariaDB Knowledge Base. It explains that the mysql.proc table contains (among other things) sourcecode for procedures and functions. So perhaps "bad data" refers to a syntax error? How do we take a look at user1234.generateResNo?
Something like this perhaps?
MariaDB [mysql]> SELECT db, name, type, specific_name, definer FROM mysql.proc;
+-----------+---------------------------------------------------+-----------+---------------------------------------------------+----------------+
| db | name | type | specific_name | definer |
+-----------+---------------------------------------------------+-----------+---------------------------------------------------+----------------+
| mysql | AddGeometryColumn | PROCEDURE | AddGeometryColumn | root@localhost |
| mysql | DropGeometryColumn | PROCEDURE | DropGeometryColumn | root@localhost |
| user001 | addContact | PROCEDURE | addContact | user001@% |
| user002 | addDestination | PROCEDURE | addDestination | user002@% |
| user003 | addPassenger | PROCEDURE | addPassenger | user003@% |
| user004 | addPassenger | PROCEDURE | addPassenger | user004@% |
| user005 | calculateFreeSeats | FUNCTION | calculateFreeSeats | user005@% |
.... cut ....
| user10529 | addPayment | PROCEDURE | addPayment | user10529@% |
| user10530 | addYear | PROCEDURE | addYear | user10530@% |
| user10531 | addDay | PROCEDURE | addDay | user10531@% |
| user10532 | addFlight | PROCEDURE | addFlight | user10532@% |
| user10533 | addYear | PROCEDURE | addYear | user10533@% |
+-----------+---------------------------------------------------+-----------+---------------------------------------------------+----------------+
10533 rows in set (0.050 sec)
Alright, there are a few stored procedures in this database. (Again, usernames redacted. Do not use simple increasing sequences in your usernames!) So why did we dump all of them? Well, I am not sure which column user1234.generateResNo corresponds to. Turns out it is not in any column, it is the conjunction of the db column, which in this case mostly corresponds to users and then either name or specific_name is appended with a dot separating them. With that we can write some SQL selects.
MariaDB [mysql]> SELECT db = user1234, name, type, specific_name, definer FROM mysql.proc;
MariaDB [mysql]> SELECT db, name, type, specific_name, definer WHERE db = "user1234" FROM mysql.proc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE db = "user1234" FROM mysql.proc' at line 1
Did I mention I do not know SQL? Anyway, FROM goes before WHERE.
MariaDB [mysql]> SELECT db, name, type, specific_name, definer FROM mysql.proc WHERE db = "user1234";
+----------+--------------------+-----------+--------------------+------------+
| db | name | type | specific_name | definer |
+----------+--------------------+-----------+--------------------+------------+
| user1234 | addContact | PROCEDURE | addContact | aleul850@% |
| user1234 | addDay | PROCEDURE | addDay | aleul850@% |
| user1234 | addDestination | PROCEDURE | addDestination | aleul850@% |
| user1234 | addFlight | PROCEDURE | addFlight | aleul850@% |
| user1234 | addPassenger | PROCEDURE | addPassenger | aleul850@% |
| user1234 | addPayment | PROCEDURE | addPayment | aleul850@% |
| user1234 | addReservation | PROCEDURE | addReservation | aleul850@% |
| user1234 | addRoute | PROCEDURE | addRoute | aleul850@% |
| user1234 | addYear | PROCEDURE | addYear | aleul850@% |
| user1234 | calculateFreeSeats | FUNCTION | calculateFreeSeats | aleul850@% |
| user1234 | calculatePrice | FUNCTION | calculatePrice | aleul850@% |
| user1234 | generateResNo | FUNCTION | generateResNo | aleul850@% |
+----------+--------------------+-----------+--------------------+------------+
12 rows in set (0.017 sec)
MariaDB [mysql]> SELECT db, name, type, body FROM mysql.proc WHERE db = "user1234" AND name = "generateResNo";
+----------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| db | name | type | body |
+----------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user1234 | generateResNo | FUNCTION | BEGIN
DECLARE Rows INT;
DECLARE Number INT;
SET Rows = (SELECT COUNT(*) FROM Reservation);
IF(Rows = 0) THEN
SET Number = (SELECT FLOOR(10000000 + RAND() * 89999999));
ELSE
SET Number = (
SELECT FLOOR(10000000 + RAND() * 89999999) AS random_number
FROM Reservation
WHERE "random_number" NOT IN (SELECT Reservation_Number FROM Reservation)
LIMIT 1
);
END IF;
RETURN Number;
END |
+----------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
MariaDB [mysql]> SELECT db, name, type, specific_name, definer, created, modified FROM mysql.proc WHERE db = "user1234" AND name = "generateResNo";
+----------+---------------+----------+---------------+------------+---------------------+---------------------+
| db | name | type | specific_name | definer | created | modified |
+----------+---------------+----------+---------------+------------+---------------------+---------------------+
| user1234 | generateResNo | FUNCTION | generateResNo | user1234@% | 2017-04-22 14:44:21 | 2017-04-22 14:44:21 |
+----------+---------------+----------+---------------+------------+---------------------+---------------------+
1 row in set (0.001 sec)
Wait a sec. The manpage of mysqldump describes the --routines option as follows:
--routines, -R Include stored routines (procedures and functions) for the dumped databases in the output. This option
requires the SELECT privilege for the mysql.proc table.
The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to create the
routines. However, these statements do not include attributes such as the routine creation and modification timestamps,
so when the routines are reloaded, they are created with timestamps equal to the reload time.
If you require routines to be created with their original timestamp attributes, do not use --routines. Instead, dump
and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the
mysql database.
Since it will generate CREATE PROCEDURE and CREATE FUNCTION presumably
the sourcecode has to be valid. It does suggest that we may be able to just dump the table directly
and bypass the possibility that the sourcecode is "bad data". But before we do that we are going
to try to eliminate data corruption. Why do that first? Mostly because the error mentions data
corruption and that it is quite simple and quick to run a basic check of the metadata.
MariaDB [mysql]> CHECK TABLE mysql.proc;
+------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.proc | check | status | OK |
+------------+-------+----------+----------+
1 row in set (0.118 sec)
MariaDB [mysql]> CHECK TABLE mysql.proc EXTENDED;
+------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.proc | check | status | OK |
+------------+-------+----------+----------+
1 row in set (0.215 sec)
So, with that test done it is unlikely to be data corruption. That leaves the "bad data" which
I think is just syntax error. The thing is, syntax errors are kind of expected because this is an
educational system, students make mistakes (all humans do). The thing that is a bit strange is
that I expected MySQL to not allow sourcecode with syntax error in mysql.spec. But this data
has been migrated before, or maybe the student has deleted tables that this function references?
I have no clue. We will just attempt a "raw" dump instead of using --routines.
First, we will do a dump of all of the "normal" databases. Then we will grab the speciall ones, like mysql.proc in a separate file. (So that we can use --all-databases to get the majority of databases.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- bash -c "mysqldump --password=\$MYSQL_ROOT_PASSWORD --all-databases 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log"
Still finished quickly. Error? Error.
root@und-prod-mariadb-galera-0:~# cat mysqldump-stderr.log
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
mysqldump: Got error: 1045: "Access denied for user 'root'@'localhost' (using password: NO)" when trying to connect
mysqldump: Couldn't execute 'SHOW CREATE FUNCTION `generateResNo`': Failed to load routine aleul850.generateResNo. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6) (1457)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'Airport' was not locked with LOCK TABLES (1100)
So, searching the error on google and reading the manpage of mysqldump it seems we need to either use --lock-tables or --single-transaction, depending on which storage engine we are using. I think we are using InnoDB, but it is possible to select storage engine when creating a table, so we need to check that everything is actually using InnoDB.
First, which storage engines exist? An Introduction to MariaDB Storage Engines seems like a good start. So.
root@und-prod-mariadb-galera-0:~# mysql -u root --password=$MYSQL_ROOT_PASSWORD
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 90803
Server version: 10.3.10-MariaDB-1:10.3.10+maria~bionic 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 [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.000 sec)
MariaDB [(none)]> select @@global.storage_engine;
+-------------------------+
| @@global.storage_engine |
+-------------------------+
| InnoDB |
+-------------------------+
1 row in set (0.000 sec)
So, InnoDB is supported and is the default storage engine. Do we have any tables that overruled the default?
MariaDB [(none)]> SELECT table_name, engine FROM information_schema.tables WHERE NOT engine = 'InnoDB';
+----------------------------------------------------+--------------------+
| table_name | engine |
+----------------------------------------------------+--------------------+
| VIEWS | Aria |
...
| INNODB_SYS_SEMAPHORE_WAITS | MEMORY |
...
| users | PERFORMANCE_SCHEMA |
...
| accounts | PERFORMANCE_SCHEMA |
...
| error_producer | MEMORY |
...
| slow_log | CSV |
...
| test | MyISAM |
+----------------------------------------------------+--------------------+
851 rows in set (1.559 sec)
So there are a few. Some of these are system tables, but a lot a are user tables. Are there any InnoDB tables?
MariaDB [(none)]> SELECT COUNT(table_name) FROM information_schema.tables WHERE engine = 'InnoDB';
+-------------------+
| COUNT(table_name) |
+-------------------+
| 33187 |
+-------------------+
1 row in set (1.586 sec)
There are InnoDB tables to. So we cannot use --single-transaction. That leaves --lock-tables or --lock-all-tables.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- bash -c "mysqldump --password=\$MYSQL_ROOT_PASSWORD --all-databases --lock-all-tables 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log"
Surprise. New error.
root@und-prod-mariadb-galera-0:~# cat mysqldump-stderr.log
mysqldump: Couldn't execute 'show create table `weekly_schedule`': Table 'user231.weekly_schedule' doesn't exist in engine (1932)
So, what does that mean? Which engine does this table use? How do we find out? We can probably search in information_schema.tables, but we need to select both database and table. There is more information about information-schema.tables here INFORMATION_SCHEMA Tables Introduction and here The INFORMATION_SCHEMA TABLES Table. Apparently TABLE_SCHEMA is another name for database.
MariaDB [(none)]> SELECT table_schema, table_name, engine FROM information_schema.tables WHERE table_schema = 'user231' AND table_name = 'weekly_schedule';
+--------------+-----------------+--------+
| table_schema | table_name | engine |
+--------------+-----------------+--------+
| user231 | weekly_schedule | InnoDB |
+--------------+-----------------+--------+
1 row in set (0.001 sec)
MariaDB [(none)]> show engine innodb status;
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type | Name | Status |
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB | |
=====================================
2020-08-27 11:55:33 0x7fb0ec0d7700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 671 srv_active, 0 srv_shutdown, 530483 srv_idle
srv_master_thread log flush and writes: 531145
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 360
OS WAIT ARRAY INFO: signal count 360
RW-shared spins 0, rounds 718, OS waits 338
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 718.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-27 09:29:34 0x7fb0dcca2700 Error in foreign key constraint of table user231/weekly_schedule:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT `fk_weekly_schedule_route` FOREIGN KEY (`RouteInSchedule`) REFERENCES `Route` (`RouteId`)
------------
TRANSACTIONS
------------
Trx id counter 66117813
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421872588706056, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421872588701840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
218039 OS file reads, 450 OS file writes, 115 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 8 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
Hash table size 69257, node heap has 27 buffer(s)
Hash table size 69257, node heap has 38 buffer(s)
Hash table size 69257, node heap has 26 buffer(s)
Hash table size 69257, node heap has 60 buffer(s)
Hash table size 69257, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 38638514999
Log flushed up to 38638514999
Pages flushed up to 38638514999
Last checkpoint at 38638514990
0 pending log flushes, 0 pending chkp writes
14 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 291504128
Dictionary memory allocated 124068071
Buffer pool size 16384
Free buffers 1024
Database pages 15201
Old database pages 5591
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9769, not young 1815941
0.00 youngs/s, 0.00 non-youngs/s
Pages read 151523, created 131, written 387
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15201, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140396598998784, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 4681254
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 255611
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
+--------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.033 sec)
MariaDB [(none)]> SELECT table_schema, table_name, engine, create_time, update_time, check_time FROM information_schema.tables WHERE table_schema = 'user231';
+--------------+-------------------+--------+---------------------+-------------+------------+
| table_schema | table_name | engine | create_time | update_time | check_time |
+--------------+-------------------+--------+---------------------+-------------+------------+
| user231 | weekly_schedule | NULL | NULL | NULL | NULL |
| user231 | allFlights | NULL | NULL | NULL | NULL |
| user231 | booking | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | contact | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbsale | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | passengerticket | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbdebit | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbsupplier | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | weekday | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbsupply | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Booking | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | reservation | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | payment | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Country | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | airport | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbsale_supply | NULL | NULL | NULL | NULL |
| user231 | jbstore | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | newitem | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | LESS_THAN_AVERAGE | NULL | NULL | NULL | NULL |
| user231 | jbitem | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Year | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | test | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Flight | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Reservation | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbparts | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | flight | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Payment | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Route | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Continent | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | debit2 | NULL | NULL | NULL | NULL |
| user231 | Airport | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | debit | NULL | NULL | NULL | NULL |
| user231 | PassengerTicket | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Weekday | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | passenger | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | IsMember | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Contact | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | WeeklySchedule | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbdept | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbcity | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | jbemployee | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | Passenger | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
| user231 | YearDate | InnoDB | 2020-08-21 08:18:18 | NULL | NULL |
+--------------+-------------------+--------+---------------------+-------------+------------+
43 rows in set, 1 warning (0.005 sec)
MariaDB [(none)]> SELECT table_schema, table_name, engine, create_time, update_time, check_time FROM information_schema.tables WHERE table_schema = 'me';
+--------------+------------+--------+---------------------+-------------+------------+
| table_schema | table_name | engine | create_time | update_time | check_time |
+--------------+------------+--------+---------------------+-------------+------------+
| me | foo | InnoDB | 2020-08-21 08:18:19 | NULL | NULL |
+--------------+------------+--------+---------------------+-------------+------------+
1 row in set (0.026 sec)
Um, me is, well, me. That table was not made at create_time. Or I have goldfish memory. Ah, after reading my "journal" from that day, turns out create_time is probably when the in-memory structure was created, because 2020-08-21 08:18 is about the time we restarted the database server. That leaves us with user231 tables with no engine. That does sound bad.
What if we just ignore the errors? What could possibly go wrong. Lets try --force.
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- bash -c "mysqldump --password=\$MYSQL_ROOT_PASSWORD --all-databases --force 2>>root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>>/root/gzip-stderr.log"
root@und-prod-mariadb-galera-0:~# cat mysqldump-stderr.log
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'Airport' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'bp' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'Week_schedule' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'bareservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'c' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `calculate`': Table 'Day' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `flightData`': Table 'Day' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'debug' was not locked with LOCK TABLES (1100)
mysqldump: Got error: 1932: "Table 'user231.weekly_schedule' doesn't exist in engine" when using LOCK TABLES
mysqldump: Couldn't execute 'show create table `weekly_schedule`': Table 'user231.weekly_schedule' doesn't exist in engine (1932)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `available_seats`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `departures`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `prices`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'date' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'Booking' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'Airport' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'bp' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'Week_schedule' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'bareservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'c' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `calculate`': Table 'Day' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `flightData`': Table 'Day' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'debug' was not locked with LOCK TABLES (1100)
mysqldump: Got error: 1932: "Table 'user231.weekly_schedule' doesn't exist in engine" when using LOCK TABLES
mysqldump: Couldn't execute 'SHOW CREATE TABLE `weekly_schedule`': Table 'user231.weekly_schedule' doesn't exist in engine (1932)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `available_seats`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `departures`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `prices`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'date' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'Booking' was not locked with LOCK TABLES (1100)
Lets try --lock-tables. We do not really need consistency between databases in this case.
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'Airport' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'bp' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'Week_schedule' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'bareservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'c' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `calculate`': Table 'Day' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `flightData`': Table 'Day' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'debug' was not locked with LOCK TABLES (1100)
mysqldump: Got error: 1932: "Table 'user231.weekly_schedule' doesn't exist in engine" when using LOCK TABLES
mysqldump: Couldn't execute 'show create table `weekly_schedule`': Table 'user231.weekly_schedule' doesn't exist in engine (1932)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `available_seats`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `departures`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `prices`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'date' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'show create table `allFlights`': Table 'Booking' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'Airport' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'bp' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'Week_schedule' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'bareservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'c' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `calculate`': Table 'Day' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `flightData`': Table 'Day' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'debug' was not locked with LOCK TABLES (1100)
mysqldump: Got error: 1932: "Table 'user231.weekly_schedule' doesn't exist in engine" when using LOCK TABLES
mysqldump: Couldn't execute 'SHOW CREATE TABLE `weekly_schedule`': Table 'user231.weekly_schedule' doesn't exist in engine (1932)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `available_seats`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `departures`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `prices`': Table 'reservation' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'date' was not locked with LOCK TABLES (1100)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `allFlights`': Table 'Booking' was not locked with LOCK TABLES (1100)
Wait, are thos references between databses perhaps? That would explain why we get error 1100 with --lock-tables (which is active by default via --opt so this test was pointless) but not with --lock-all-tables. Because --lock-all-tables locks all tables of all databases. Not just the current database and its tables. Since there is no write traffic from outside, maybe we can just scip the locking? I am thinking about doing this because I suspect that these errors means it did not generate sql code for those tables, since it could not guarantee consistency in the way the default options indicate it should?
me@desktop:~$ kubectl exec --namespace=mariadb und-prod-mariadb-galera-0 -c mariadb -- bash -c "mysqldump --password=\$MYSQL_ROOT_PASSWORD --all-databases --skip-lock-tables --force 2>>/root/mysqldump-stderr.log | gzip > /root/mysql_dump.tgz 2>/root/gzip-stderr.log"
root@und-prod-mariadb-galera-0:~# cat mysqldump-stderr.log
mysqldump: Couldn't execute 'show create table `weekly_schedule`': Table 'user231.weekly_schedule' doesn't exist in engine (1932)
mysqldump: Couldn't execute 'SHOW CREATE TABLE `weekly_schedule`': Table 'user231.weekly_schedule' doesn't exist in engine (1932)
Alright, we will leave user231 for later. Lets see if we can import this on the destination machine and verify integrity. We also have to get the procedures and triggers.
Need to stop mysqld but keep the container alive. Or attach another container to the pod?