Unable to export to server when using MySql

[QuentinSc]

Describe the bug
LHCI server with MySql database gives 500 error when uploading report

To Reproduce
Steps to reproduce the behavior:

1. Install Lighthouse CI Server with docker image patrickhulce/lhci-server
2. Run lhci wizard to setup everything
3. Configure .lighthouserc.js to upload report on this server
4. Run lhci autorun
5. See error

Expected behavior
upload command success

Logs/Screenshots
`Error: Unexpected status code 500

<title>Error</title>

Error: Incorrect string value: '\xE2\x80\xAAFra...' for column 'lhr' at row 1
Error
    at Query.run (/usr/src/lhci/node_modules/sequelize/lib/dialects/mysql/query.js:52:25)
    at /usr/src/lhci/node_modules/sequelize/lib/sequelize.js:315:28
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async MySQLQueryInterface.insert (/usr/src/lhci/node_modules/sequelize/lib/dialects/abstract/query-interface.js:308:21)
    at async model.save (/usr/src/lhci/node_modules/sequelize/lib/model.js:2490:35)
    at async runs.create (/usr/src/lhci/node_modules/sequelize/lib/model.js:1362:12)
    at async SqlStorageMethod.createRun (/usr/src/lhci/node_modules/@lhci/server/src/api/storage/sql/sql.js:692:17)
    at async /usr/src/lhci/node_modules/@lhci/server/src/api/routes/projects.js:200:19
    at /usr/src/lhci/node_modules/@lhci/server/src/api/express-utils.js:30:18
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

at ApiClient._convertFetchResponseToReturnValue (C:\src\lhci\client\node_modules\@lhci\utils\src\api-client.js:78:21)
at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
at async runLHCITarget (C:\src\lhci\client\node_modules\@lhci\cli\src\upload\upload.js:438:17)
at async Object.runCommand (C:\src\lhci\client\node_modules\@lhci\cli\src\upload\upload.js:592:16)
at async run (C:\src\lhci\client\node_modules\@lhci\cli\src\cli.js:106:7)Waiting for the debugger to disconnect...

WARNING: upload command failed.
Done running autorun.
`
Environment (please complete the following information):

• OS: Windows with WSL2 Docker

Additional context
Same report is correctly uploaded if the server use the sqlite database or postgres

[RomanRanges1122]

The error message you're encountering indicates a problem with encoding when attempting to store a string value in the MySQL database. Specifically, it seems like there's an issue with the character encoding for the string being stored.

The relevant part of the error message is:

Error: Incorrect string value: '\xE2\x80\xAAFra...' for column 'lhr' at row 1

This suggests that the string being attempted to be stored (\xE2\x80\xAAFra...) contains characters that cannot be correctly encoded or stored in the MySQL database using the current character set.

To address this issue, you can try the following steps:

Check Character Encoding Settings: Ensure that the character encoding settings for your MySQL database are properly configured. You may need to adjust the character set and collation settings to support the full range of characters being used in your data.

Use UTF-8 Encoding: It's generally recommended to use UTF-8 character encoding for storing text data in databases, as it supports a wide range of characters. Make sure that both the MySQL database and the application connecting to it are configured to use UTF-8 encoding.

Check Data Input: Verify the data being sent to the MySQL database and ensure that it doesn't contain any invalid or unsupported characters. If necessary, sanitize or normalize the data before storing it.

Update MySQL Configuration: If your MySQL server is not configured to use UTF-8 encoding by default, you may need to update the MySQL configuration file (my.cnf) to specify UTF-8 as the default character set.

Update Table Schema: If the issue persists after adjusting the character encoding settings, you may need to modify the table schema to use a character set and collation that can properly handle the data being stored.

Test with Different Data: If possible, try uploading a report with different content to see if the issue is specific to certain characters or data inputs.

By following these steps, you should be able to resolve the error and successfully upload reports to your MySQL database with the LHCI server.

[QuentinSc]

Hello,
All is installed out of the box with the docker image, it must working ootb no ?

[mmeester]

I actually ran into the same issue and solved it by the help of this issue #419 (comment)

and fixed it by adding the following to the lighthouserc.json file:

{ 
	...
	"sequelizeOptions": {
          "define": {
            "charset": "utf8mb4",
            "collate": "utf8mb4_unicode_ci"
          }
     }
}

Make sure you remove current tables from the database and restart the application so it regenerates the tables