MYSQL - "Identifier name 'components_restaurant_related_restaurants_restaurants_links_inv_fk' is too long"

[semucarusi]

Bug report

Describe the bug

"Identifier name 'components_restaurant_related_restaurants_restaurants_links_inv_fk' is too long",

Steps to reproduce the behavior

Just Change the database from sqlite to MySQL and try to start the application.

The complete error trace is the following:

code: 'ER_TOO_LONG_IDENT',
errno: 1059,
sqlMessage: "Identifier name 'components_restaurant_related_restaurants_restaurants_links_inv_fk' is too long",
sqlState: '42000',
index: 0,
sql: 'alter table components_restaurant_related_restaurants_restaurants_links add index components_restaurant_related_restaurants_restaurants_links_inv_fk(restaurant_id)'
}

System

Node.js version: 16.7.0
Strapi version: 4.10.7
Database: MYSQL 5.7
Operating system: MacOS

[alisharafian]

"Thanks to my friend Subhan Behjati for solving this problem"
First, extract the information from the sqlite database with the "datagrip" program and convert it to "mysql". And then create a new folder in the "api" folder and I named it data and put the database converted to "mysql" in that folder and create a new "seed" file that contains the information about the "mysql" database.
for example new seed file : const fs = require('fs');
const path = require('path');
const knex = require('knex');
const fse = require('fs-extra');

// Define the path to your SQL file
const sqlFilePath = path.join(__dirname, '../data/strapi_food.sql');

// Read the SQL file
const sqlQueries = fs.readFileSync(sqlFilePath, 'utf-8');

const uploadDataPath = path.join(path.resolve('data'), 'uploads');
const uploadPath = path.join(path.resolve('public'), 'uploads');

const tmpPath = path.resolve('.tmp');

// Configure the database connection
const db = knex({
client: 'mysql',
connection: {
host: 'localhost',
user: 'root',
password: '',
database: 'strapi_db',
},
});

async function updateUid() {
const filePath = ./package.json;

try {
if (fse.existsSync(filePath)) {
const rawFile = fse.readFileSync(filePath);
const packageJSON = JSON.parse(rawFile);

  if (packageJSON.strapi.uuid.includes('FOODADVISOR')) return null;

  packageJSON.strapi.uuid =
    `FOODADVISOR-${
      process.env.GITPOD_WORKSPACE_URL ? 'GITPOD-' : 'LOCAL-'
    }` + uuid();

  const data = JSON.stringify(packageJSON, null, 2);
  fse.writeFileSync(filePath, data);
}

} catch (e) {
console.error(e);
}
}

async function dumpMySQL() {
// Split the queries using the delimiter (assuming each query ends with ';')
const queries = sqlQueries.split(';');

// Remove any empty queries
const validQueries = queries.filter((query) => query.trim() !== '');

// Execute each query
for (const query of validQueries) {
await db.raw(query);
console.log(Query executed: ${query});
}
console.log('Seed completed successfully.');
// Disconnect from the database
await db.destroy();
}

// Function to execute SQL queries
async function sobhanCustom_executeQueries() {
try {
await updateUid();
} catch (error) {
console.log(error);
}

try {
await fse.emptyDir(tmpPath);
} catch (err) {
console.log(Failed to remove ${tmpPath});
}

try {
await dumpMySQL();
} catch (error) {
console.error('Error executing seed:', error);
}

try {
await fse.emptyDir(uploadPath);
} catch (err) {
console.log(Failed to remove ${uploadPath});
}

try {
await fse.copy(uploadDataPath, uploadPath, { overwrite: true });
} catch (err) {
console.log(Failed to move ${uploadDataPath} to ${uploadPath});
}

}

// Run the seed
sobhanCustom_executeQueries();

[alisharafian]

and you need change database.js like this :

module.exports = ({ env }) => {
const client = env('DATABASE_CLIENT', 'mysql');

const connections = {
mysql: {
connection: {
connectionString: env('DATABASE_URL'),
host: env('DATABASE_HOST', 'localhost'),
port: env.int('DATABASE_PORT', 3306),
database: env('DATABASE_NAME', 'strapi_db'),
user: env('DATABASE_USERNAME', 'root'),
password: env('DATABASE_PASSWORD', ''),
ssl: env.bool('DATABASE_SSL', false) && {
key: env('DATABASE_SSL_KEY', undefined),
cert: env('DATABASE_SSL_CERT', undefined),
ca: env('DATABASE_SSL_CA', undefined),
capath: env('DATABASE_SSL_CAPATH', undefined),
cipher: env('DATABASE_SSL_CIPHER', undefined),
rejectUnauthorized: env.bool(
'DATABASE_SSL_REJECT_UNAUTHORIZED',
true
),
},
}