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]

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 lando/legacy-cli#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/legacy-cli@d139cd3

[eelkeblok]

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

[dayio]

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 lando/legacy-cli#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.