Entering https:// in the tooltip text breaks MySQL

[nicko]

Warning: If you attempt to replicate this it may kill your MySQL installation.

If i include the following text within a tooltip text when creating a field in an entity:
https://
then it breaks MySQL. The first message is an error 500 saying failed to rebuild database. Then any action after returns a 500 at the top of the screen. MySQL gets stuck in a restart loop where it stays alive for about 60 seconds before crashing. I have tested and can replicate this when creating a new varchar field within the Contact entity that included https:// in the tooltip. On a failed system I was able to get into the MySQL logs and it looked like MySQL was crashing shortly after startup when it was trying to run a procedure that accessed the Contacts table.

Here is a snip from the Espo log file after the issue has occurred:

[2022-07-06 21:31:08] WARNING: E_WARNING: PDO::query(): MySQL server has gone away {"code":2,"message":"PDO::query(): MySQL server has gone away","file":"/var/www/html/applicatio
n/Espo/ORM/SqlExecutor.php","line":70} []
[2022-07-06 21:31:08] WARNING: E_WARNING: PDO::query(): Error reading result set's header {"code":2,"message":"PDO::query(): Error reading result set's header","file":"/var/www/h
tml/application/Espo/ORM/SqlExecutor.php","line":70} []
[2022-07-06 21:31:08] ERROR: (HY000) SQLSTATE[HY000]: General error: 2006 MySQL server has gone away; GET /Activities/action/popupNotifications; line: 70, file: /var/www/html/app
lication/Espo/ORM/SqlExecutor.php [] []
[2022-07-06 21:31:08] ERROR: (2006) SQLSTATE[HY000] [2006] MySQL server has gone away; GET /Notification/action/notReadCount; line: 122, file: /var/www/html/application/Espo/ORM/
PDO/DefaultPDOProvider.php [] []


The only solution I could find was to restore a backup of the database, as SQL would not stay alive long enough to do anything in SQL apart from restore a backup.

I have attached an image of creating an entity with tooltiip text that causes the issue.

Espo v7.0.8
PHP 7.3.33
MySQL Version 8.0.29

[lazovic]

Hi nicko,

I can't reproduce your problem, unfortunately. Everything works as it should.

I will also try to reproduce this error with your requirements exactly, but it will take a little time.

EspoCRM 7.0.9
PHP 7.4.22
MySQL 8.0.16

[yuri]

This is not possible. Must be a coincidence. Could be a problem in hardware or environment. Please, such issues not in bug reports.

[yuri]

Could be a hardware problem.

[nicko]

It looks like it only happens on the Contact entity. I tried replicating it on Accounts but the field created fine. I've tested on Contacts creating varchar and int, and both had the result of an error 500 and then MySQL keeps restarting.

I can replicate every time on different deployments, so I dont think its a coincidence.

I could also replicate it on a different installation on EspoCRM 7.0.9.

I thought I'd see if I could get some more log output from MySQL - I get the following from MySQL via PHPMyAdmin:


Warning in ./libraries/classes/Dbi/DbiMysqli.php#213
mysqli_query(): Error reading result set's header

Backtrace

./libraries/classes/Dbi/DbiMysqli.php#213: mysqli_query(
,
string 'SELECT COUNT(*) FROM `espocrm`.`contact`',
integer 0,
)
./libraries/classes/DatabaseInterface.php#319: PhpMyAdmin\Dbi\DbiMysqli->realQuery(
string 'SELECT COUNT(*) FROM `espocrm`.`contact`',
,
integer 1,
)
./libraries/classes/DatabaseInterface.php#1621: PhpMyAdmin\DatabaseInterface->tryQuery(
string 'SELECT COUNT(*) FROM `espocrm`.`contact`',
integer 256,
integer 1,
boolean false,
)
./libraries/classes/Table.php#715: PhpMyAdmin\DatabaseInterface->fetchValue(string 'SELECT COUNT(*) FROM `espocrm`.`contact`')
./libraries/classes/Controllers/Database/DatabaseStructureController.php#1099: PhpMyAdmin\Table->countRecords(boolean true)
./libraries/classes/Controllers/Database/DatabaseStructureController.php#988: PhpMyAdmin\Controllers\Database\DatabaseStructureC ontroller->getValuesForInnodbTable(
array,
integer 2392064,
)
./libraries/classes/Controllers/Database/DatabaseStructureController.php#423: PhpMyAdmin\Controllers\Database\DatabaseStructureC ontroller->getStuffForEngineTypeTable(
array,
integer 2392064,
integer 0,
)
./libraries/classes/Controllers/Database/DatabaseStructureController.php#195: PhpMyAdmin\Controllers\Database\DatabaseStructureC ontroller->displayTableList()
./db_structure.php#38: PhpMyAdmin\Controllers\Database\DatabaseStructureC ontroller->indexAction()


Warning in ./libraries/classes/Dbi/DbiMysqli.php#213
mysqli_query(): MySQL server has gone away

Backtrace

./libraries/classes/Dbi/DbiMysqli.php#213: mysqli_query(
,
string 'SELECT COUNT(*) FROM `espocrm`.`contact`',
integer 0,
)
./libraries/classes/DatabaseInterface.php#319: PhpMyAdmin\Dbi\DbiMysqli->realQuery(
string 'SELECT COUNT(*) FROM `espocrm`.`contact`',
,
integer 1,
)
./libraries/classes/DatabaseInterface.php#1621: PhpMyAdmin\DatabaseInterface->tryQuery(
string 'SELECT COUNT(*) FROM `espocrm`.`contact`',
integer 256,
integer 1,
boolean false,
)
./libraries/classes/Table.php#715: PhpMyAdmin\DatabaseInterface->fetchValue(string 'SELECT COUNT(*) FROM `espocrm`.`contact`')
./libraries/classes/Controllers/Database/DatabaseStructureController.php#1099: PhpMyAdmin\Table->countRecords(boolean true)
./libraries/classes/Controllers/Database/DatabaseStructureController.php#988: PhpMyAdmin\Controllers\Database\DatabaseStructureC ontroller->getValuesForInnodbTable(
array,
integer 2392064,
)
./libraries/classes/Controllers/Database/DatabaseStructureController.php#423: PhpMyAdmin\Controllers\Database\DatabaseStructureC ontroller->getStuffForEngineTypeTable(
array,
integer 2392064,
integer 0,
)
./libraries/classes/Controllers/Database/DatabaseStructureController.php#195: PhpMyAdmin\Controllers\Database\DatabaseStructureC ontroller->displayTableList()
./db_structure.php#38: PhpMyAdmin\Controllers\Database\DatabaseStructureC ontroller->indexAction()

[lazovic]

nicko,

Attempt to receive the given error again was not crowned with success. This time I used exactly the same requirements as yours.

Perhaps the problem is in some settings of MySQL.
You might even need to reinstall it.

But this issue absolutely does not depend on EspoCRM.

[nicko]

Thanks Iazovic. My deployment is running on a LAMP setup on Ubuntu, so yes maybe its something with the MySQL deployment within the LAMP image that my hosting company provides.

[nicko]

Update on this: Firstly, I wish I could change the name of the post, it should read:

MySQL gets into a reboot loop after adding a field to an entity.

I found that adding new fields to some entities would cause MySQL to crash and then restart, with output similar to my earlier posts. The only way I could get the instance working again was to restore a backup, but I still couldn't add any new fields to certain entities, which is obviously a major issue if you want to continue to expand the deployment.

The initial fix I found was to run an optimise on the table in question. I did this from phpMyAdmin: click the table, go to Operations, scroll down to Table maintenance and click Optimize table. It should return ok, with a message saying something like "Table does not support optimize, doing recreate + analyze".

Thats ok for just one table, but what if this issue is also affecting other tables? You can do a command line optimise using the following (assuming your Espo DB is called espocrm):

mysqlcheck -o espocrm

I use Ansible to run this across lots of hosts. So far so good. I'm testing it before rolling out to more.

Hope this helps someone