Created
June 3, 2024 09:51
-
-
Save ChaituVR/738383376281effd774f4ed85bd14656 to your computer and use it in GitHub Desktop.
change-table-collate.ts
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// usuage: ts-node quick/collate-tables-issue/change-table-collate.ts <table-name> | |
import db from '../../utils/prodMysql'; | |
const tableName = process.argv[2]; | |
if (!tableName) { | |
console.error('Please provide a table name'); | |
process.exit(1); | |
} | |
async function main() { | |
// Create a new table with the same schema as the original table | |
const newTableName = `_${tableName}_new`; | |
const createTableSql = `CREATE TABLE ${newTableName} LIKE ${tableName}`; | |
await db.queryAsync(createTableSql); | |
console.log(`Created table ${newTableName} like ${tableName}`); | |
// Change collate of the new table | |
const changeCollateSql = `ALTER TABLE ${newTableName} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs`; | |
await db.queryAsync(changeCollateSql); | |
console.log(`Changed collate of table ${newTableName}`); | |
// Insert data from the original table to the new table in batches | |
const totalRows = (await db.queryAsync(`SELECT COUNT(*) as total FROM ${tableName}`))[0].total; | |
const copyTableSql = `INSERT INTO ${newTableName} SELECT * FROM ${tableName} ORDER BY created ASC`; | |
let offset = 0; | |
const batchSize = 100000; | |
while (true) { | |
const copyTableWithOffsetSql = `${copyTableSql} LIMIT ${offset}, ${batchSize}`; | |
const results = await db.queryAsync(copyTableWithOffsetSql); | |
if (results.affectedRows === 0) { | |
break; | |
} | |
offset += batchSize; | |
console.log(`Progress: ${((Math.min(offset, totalRows)/totalRows)*100).toFixed(2)}%`); | |
} | |
// Rename the original table to a backup table | |
const backupTableName = `_${tableName}_backup`; | |
const renameTableSql = `RENAME TABLE ${tableName} TO ${backupTableName}`; | |
await db.queryAsync(renameTableSql); | |
console.log(`Renamed table ${tableName} to ${backupTableName}`); | |
// Check if the data is copied correctly | |
const newTableRows = (await db.queryAsync(`SELECT COUNT(*) as total FROM ${newTableName}`))[0].total; | |
const backupTableRows = (await db.queryAsync(`SELECT COUNT(*) as total FROM ${backupTableName}`))[0].total; | |
if (newTableRows === backupTableRows) { | |
console.log('Data copied successfully'); | |
} else { | |
console.error('Data not copied correctly, Difference:', backupTableRows - newTableRows); | |
// copy remaining data from backup table to new table | |
const copyRemainingDataSql = `INSERT INTO ${newTableName} SELECT * FROM ${backupTableName} ORDER BY created ASC LIMIT ${newTableRows}, ${backupTableRows - newTableRows}`; | |
await db.queryAsync(copyRemainingDataSql); | |
console.log('Copied remaining data from backup table to new table'); | |
} | |
// Rename the new table to the original table name | |
const renameNewTableSql = `RENAME TABLE ${newTableName} TO ${tableName}`; | |
await db.queryAsync(renameNewTableSql); | |
console.log(`Renamed table ${newTableName} to ${tableName}`); | |
console.log('\nDone!! 🥳'); | |
process.exit(0); | |
} | |
main(); |
Author
ChaituVR
commented
Jun 5, 2024
// usuage: ts-node quick/collate-tables-issue/copy-remaining-rename.ts
import db from '../../utils/localMysql';
const originalTableName = 'votes';
const newTableName = `_${originalTableName}_new`;
const backupTableName = `_${originalTableName}_backup`;
async function main() {
console.time('Time');
// Rename the original table to a backup table
await db.queryAsync(`RENAME TABLE ${originalTableName} TO ${backupTableName}`);
console.log(`Renamed table ${originalTableName} to ${backupTableName}`);
// Copy remaining data from the backup table to the new table
let lastCreated = (await db.queryAsync(`SELECT created FROM ${newTableName} ORDER BY created DESC LIMIT 1`))[0]?.created;
const copyTableSql = `REPLACE INTO ${newTableName} SELECT * FROM ${backupTableName} WHERE created >= ? ORDER BY created ASC LIMIT 200000`;
await db.queryAsync(copyTableSql, [lastCreated]);
// Check if new table is equal to the backup table
const backupTableCount = (await db.queryAsync(`SELECT COUNT(*) as count FROM ${backupTableName}`))[0].count;
console.log('Backup table count:', backupTableCount);
const newTableCount = (await db.queryAsync(`SELECT COUNT(*) as count FROM ${newTableName}`))[0].count;
console.log('New table count:', newTableCount);
if(backupTableCount === newTableCount) {
console.log('All data copied!');
// Rename the new table to the original table
await db.queryAsync(`RENAME TABLE ${newTableName} TO ${originalTableName}`);
console.log(`Renamed table ${newTableName} to ${originalTableName}`);
} else {
// Rename the backup table to the original table
await db.queryAsync(`RENAME TABLE ${backupTableName} TO ${originalTableName}`);
console.log(`Renamed table ${backupTableName} to ${originalTableName}`);
}
console.timeEnd('Time');
process.exit(0);
};
main();
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment