change-table-collate.ts

change-table-collate.ts

// 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();

// 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();