Problem with creating database table

ERPNext Support
1

I have a problem when creating a new site as follows:

% sudo bench new-site erp.test.com
MySQL root password: 
ERROR 4047 (HY000) at line 10: InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
Table 'tabDefaultValue' missing in the restored site. Database not installed correctly, this can due to lack of permission, or that the database name exists. Check your mysql root password, validity of the backup file or use --force to reinstall

As I figured out, the default tables are not created as expected. because when I ran:

% bench --site erp.test.com install-app erpnext
I got this:
An error occurred while installing erpnext:
(1146, “Table ‘_795edb06adf65e00.tabDefaultValue’ doesn’t exist”)

to reassure I logged into MariaDB shell and I got this:

MariaDB [(none)]> use _795edb06adf65e00
Database changed
MariaDB [_795edb06adf65e00]> show tables;
Empty set (0.001 sec)

Any advice please, how to create these default tables?

Thank you.

2

do you have site1.local created on your system as part of easy install?

3

Hello, I’m having the same problem. @neerajvkn would that show in the /sites directory? If so, I don’t have that created on my system.

4

Use older Mariadb for now. Preferably 10.3

Or change code for new site creation to complete Table 'tabDefaultValue' missing Error When creating new site - #3 by revant_one

5

I’m also facing this issue when trying to force restore from a backup of an existing production site (on mariadb version 10.3.30) on a local instance running mariadb version 10.4.13. The site does not have ERPNext installed. Only 1 custom app on top of Frappe.

ERROR 2006 (HY000) at line 2591: MySQL server has gone away
Table 'tabDefaultValue' missing in the restored site. Database not installed correctly, this can due to lack of permission, or that the database name exists. Check your mysql root password, validity of the backup file or use --force to reinstall
6

I have also tried to restore by downgrading the local instance mariadb version to 10.3.30 from 10.4.13 but I’m seeing the same error while restoring

7

Another observation:

On the production instance running on Ubuntu, table names are case sensitive so tabdefaultvalue doesn’t exist but tabDefaultValue does. On the other hand, on my local instance running on macOS, table names are not case sensitive so both tabdefaultvalue and tabDefaultValue exist and return the same table. I don’t know if this is part of the problem.

EDIT: Doesn’t seem to be a Mac issue since the same error’s there while trying to restore on Ubuntu

SOLVED: Increased the max_allowed_packet (default value on Mac was 16777216) to 107374182 after following this article “MySQL server has gone away“ while importing SQL Dump - Solved | TablePlus

8

I have stumbled upon this issue on Macbook Air M1 after upgrading to 11.6(Big Sur).

11:01:46 web.1            |   File "/Users/anand/projects/frappe/bench-dev/env/lib/python3.8/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
11:01:46 web.1            |     raise errorclass(errno, errval)
11:01:46 web.1            | pymysql.err.OperationalError: (4047, 'InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.')

In mysql config, I have added the following line and restarted mysql server.

[mysqld]
innodb-read-only-compressed=OFF

This config resolved the issue.

4 Likes