db-import fails to wipe tables in MySQL 8

[RoSk0]

Lando version: v3.0.14

Lando file

name: mysql8-test
services:
  db:
    type: mysql:8.0
    portforward: true
tooling:
  'db-import <file>':
    service: db
    cmd: /helpers/sql-import.sh
    user: root
    options:
      no-wipe:
        boolean: true

$ lando db-import db.sql.gz 
Preparing to import /app/db.sql.gz into database 'database' on service 'db' as user root...

Emptying database... 
NOTE: See the --no-wipe flag to avoid this step!
Dropping activity_log from database database...
ERROR 1347 (HY000) at line 2: 'database.activity_log' is not VIEW

db.sql.gz

[RoSk0]

Haven't tested, but looking at the code latest and greatest is affected as well.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions and please check out this if you are wondering why we auto close issues.

[RoSk0]

Done another test run.

Lando v3.0.26:

$ lando db-import db.sql.gz 
Preparing to import /app/db.sql.gz into database 'database' on service 'db' as user root...

Emptying database... 
NOTE: See the --no-wipe flag to avoid this step!
Gzipped file detected!
Importing /app/db.sql.gz...
Import complete!

$ lando db-import db.sql.gz 
Preparing to import /app/db.sql.gz into database 'database' on service 'db' as user root...

Emptying database... 
NOTE: See the --no-wipe flag to avoid this step!
Dropping activity_log from database database...
ERROR 1347 (HY000) at line 2: 'database.activity_log' is not VIEW

Lando v3.1.4:

$ lando db-import db.sql.gz 
Preparing to import /app/db.sql.gz into database 'database' on service 'db' as user root...

Emptying database... 
NOTE: See the --no-wipe flag to avoid this step!
Gzipped file detected!
Importing /app/db.sql.gz...
Import complete!

$ lando db-import db.sql.gz 
Preparing to import /app/db.sql.gz into database 'database' on service 'db' as user root...

Emptying database... 
NOTE: See the --no-wipe flag to avoid this step!
Dropping activity_log from database database...
ERROR 1347 (HY000) at line 2: 'database.activity_log' is not VIEW

[xurizaemon]

The current MySQL wipe implementation in db-import tries to execute DROP VIEW IF EXISTS $t; DROP TABLE IF EXISTS $t; for each $t in SHOW TABLES.

https://github.com/lando/cli/blob/main/plugins/lando-services/scripts/sql-import.sh#L117-L124

Where the table exists and is not a view, this throws an error on MySQL 8 (8.0.26 here).

$ lando db-import tmp/drupal9.sql
Preparing to import /app/tmp/drupal9.sql into database 'drupal9' on service 'database' as user root...

Emptying drupal9... 
NOTE: See the --no-wipe flag to avoid this step!
Dropping block_content from drupal9 database...
ERROR 1347 (HY000) at line 2: 'drupal9.batch' is not VIEW

$ lando drush sqlc
mysql> DROP VIEW IF EXISTS batch;
ERROR 1347 (HY000): 'drupal9.batch' is not VIEW
mysql> DROP TABLE IF EXISTS batch;
Query OK, 0 rows affected (0.06 sec)

If we can make the same permissions assumption as the pgsql implementation already does (that the executing user can drop and create DBs) then the DROP DATABASE; CREATE DATABASE approach should be fine? PR in https://github.com/lando/cli/pull/59

We are already deleting all tables and views in the DB, at which point I think that a DB drop is both faster and more accurate - no other artefacts (errr, triggers?) will be preserved.

Confirmed with lando/cli@d139cd3fca4e02d9232aa62f012c6dcfcd2a6c6f

[eelkeblok]

I can reproduce the problem on the drupal9 recipe with MySQL replaced with version 8. Not sure how to test this PR though.

[Bouhnosaure]

Hi !

I have the same issue here on Mysql 8.0

➜  back-end git:(develop) lando db-import b28....0882de.sql.gz
Preparing to import /app/b28....0882de.sql.gz into database 'laravel' on service 'database' as user root...

Emptying laravel... 
NOTE: See the --no-wipe flag to avoid this step!
Dropping activity_log from laravel database...
ERROR 1347 (HY000) at line 2: 'laravel.activity_log' is not VIEW

I thinks yes since the option --no-wipe exists, we could drop the entire database and re-create it after.

[RayHollister]

I've been having the same issue lately.

Rays-Lambo:pogo.news rayhollister$ lando db-import pogo_news.sql
Preparing to import /app/pogo_news.sql into database 'wordpress' on service 'database' as user root...

Emptying wordpress...
NOTE: See the --no-wipe flag to avoid this step!
Dropping pogo_bcpt from wordpress database...
ERROR 1347 (HY000) at line 2: 'wordpress.pogo_bcpt' is not VIEW

The only workaround I have found is to destroy the lando, start it and then run the import. Seems to work everytime. Kinda annoying though.

[charlie59]

Can confirm this is still an issue with lando v3.6.4 charlie@MacBook-Pro-2 drupal9 % lando db-import grahamleader.sql Preparing to import /app/grahamleader.sql into database 'drupal9' on service 'database' as user root...

Emptying drupal9... NOTE: See the --no-wipe flag to avoid this step! Dropping batch from drupal9 database... ERROR 1347 (HY000) at line 2: 'drupal9.batch' is not VIEW

[luispimentellopes]

Hi, I still have the same issue. I've created a workaround to allow DB importation in Lando. (It requires Drush)

events:
  pre-db-import:
      # Fix MySQL 8 import issue (dropping DB with Drush).
      - appserver: echo "Dropping DB" && $LANDO_WEBROOT/../vendor/bin/drush sql:drop -y

[xurizaemon]

I stopped using db-import as a result of this, but figured I'd come back and give it a go again today. Ran into the same behaviour:

$ lando db-import tmp/drupal9.migrate-rolling.20230213.2133.sql.gz
Preparing to import /app/tmp/drupal9.migrate-rolling.20230213.2133.sql.gz into database 'drupal9' on service 'database' as user root...

Emptying drupal9... 
NOTE: See the --no-wipe flag to avoid this step!
Dropping authmap from drupal9 database...
ERROR 1347 (HY000) at line 2: 'drupal9.authmap' is not VIEW

Are those affected by this issue doing something different from most folks?

I submitted https://github.com/lando/cli/pull/59 ages ago but need to re-test and address that test failure.

[RayHollister]

I'm still experiencing the same issue on the WordPress recipe. I have also experienced it on Pantheon as well.

[doodirock]

Same issue from lamp recipe. Any workarounds?

[eelkeblok]

Workarounds are either wipe the DB manually, or use the --no-wipe flag. Only use the latter if you know the database to not have changed. Especially tables that are in the current database but are not in the dump can give trouble (e.g. a table that gets created by installing a module that is only present on development systems, for instance).

[rlorenzo]

I'm chiming in that this bug just hit me when using MySQL 8. It wasn't a problem before when I was using MySQL 5.7.

[kerasai]

I've worked around this by copying the /helpers/sql-import.sh to a location in my project, making the following edits, and adding tooling to invoke the customized script.

Replace the portion of the script where it queries and deletes the tables with this, which (1) queries the views and deletes them, then (2) queries and deletes the tables:

    # Gather and destroy views
    TABLES=$($SQLSTART -e "SHOW FULL TABLES WHERE Table_Type = 'VIEW'" | awk '{ print $1}' | grep -v '^Tables' || true)
  
    # PURGE IT ALL! Drop views as needed
    for t in $TABLES; do
      echo "Dropping $t from $DATABASE database..."
      $SQLSTART <<-EOF
        SET FOREIGN_KEY_CHECKS=0;
        DROP VIEW IF EXISTS \`$t\`;
EOF
    done
  
    # Gather and destroy tables
    TABLES=$($SQLSTART -e "SHOW FULL TABLES WHERE Table_Type != 'VIEW'" | awk '{ print $1}' | grep -v '^Tables' || true)
  
    # PURGE IT ALL! Drop tables as needed
    for t in $TABLES; do
      echo "Dropping $t from $DATABASE database..."
      $SQLSTART <<-EOF
        SET FOREIGN_KEY_CHECKS=0;
        DROP TABLE IF EXISTS \`$t\`;
EOF
    done

The tooling bit from .lando.yml:

tooling:
  mysql8-db-import:
    service: mysql8
    cmd: scripts/sql-import.sh

[chrisferagotti]

Kinda ridiculous that this is still an issue. mysql 8 is becoming more and more of a requirement in places, and it can't natively support something as simple as a database wipe. I've considered kerasai's solution (thanks!), but given that I'm not losing anything more than the DB I already wanted removed (and about 20 seconds of my life), I'll just keep doing the lando destroy -y for now.

[nathansimmonds]

I've been running into this recently, too. My workaround is to ssh onto the database instance and drop/recreate the db, after which the db-import will work.

# lando ssh --service=database

# mysql -u root

mysql> drop database foobar;
mysql> create database foobar;

[purdy]

I'm also running into this with a lamp recipe with mysql 8.0. I'm running Drupal, so my workaround is to do a lando drush sql-drop --yes and then lando db-import --no-wipe [FILE], but it'd be nice if db-import worked with 8 like it did with 5.