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-votes-with-collate-and-length.ts
import db from '../../utils/prodMysql';

const tableName = 'votes'

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}`);

    // Change `space` and `voter` length of the new table to VARCHAR(100)
    const changeLengthSql = `ALTER TABLE ${newTableName} MODIFY space VARCHAR(100), MODIFY voter VARCHAR(100)`;
    await db.queryAsync(changeLengthSql);
    console.log(`Changed length of space and voter columns 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)}%`);
    }
    
    console.log('\nDone!! 🥳');
    process.exit(0);
}

main();

import db from '../../utils/prodMysql';

const tableName = 'votes_copy';

async function main() {
    const newTableName = `_${tableName}_new`;
    // const createTableSql = `CREATE TABLE ${newTableName} LIKE ${tableName}`;
    // await db.queryAsync(createTableSql);
    console.log(`Created table ${newTableName} like ${tableName}`);

    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}`);

    const changeLengthSql = `ALTER TABLE ${newTableName} MODIFY space VARCHAR(100), MODIFY voter VARCHAR(100)`;
    await db.queryAsync(changeLengthSql);
    console.log(`Changed length of space and voter columns of table ${newTableName}`);
    const firstCreated = (await db.queryAsync(`SELECT created FROM ${tableName} ORDER BY created ASC LIMIT 1`))[0].created;
    const lastCreated = (await db.queryAsync(`SELECT created FROM ${tableName} ORDER BY created DESC LIMIT 1`))[0].created;
    let last_created_in_new_table = (await db.queryAsync(`SELECT created FROM ${newTableName} ORDER BY created DESC LIMIT 1`))[0]?.created || firstCreated;
    const batchSize = 10000;
    console.time('Time');
    while (true) {
        console.log(`Copying data from created >= ${last_created_in_new_table}`);
        const copyTableSql = `REPLACE INTO ${newTableName} SELECT * FROM ${tableName} WHERE created >= ? ORDER BY created ASC LIMIT ${batchSize}`;
        await db.queryAsync(copyTableSql, [last_created_in_new_table]);
        last_created_in_new_table = (await db.queryAsync(`SELECT created FROM ${newTableName} ORDER BY created DESC LIMIT 1`))[0].created;
        if(last_created_in_new_table >= lastCreated) {
            console.log('All data copied upto', last_created_in_new_table); 
            break;
        }
        console.log(`Progress: ${(((last_created_in_new_table - firstCreated)/(lastCreated - firstCreated))*100).toFixed(2)}% , last_created_in_new_table: ${last_created_in_new_table}`);
        console.timeLog('Time');
    }

    console.log('\nDone!! 🥳');
    process.exit(0);
}

main();

import db from '../../utils/prodMysql';

const tableName = 'votes';

async function main() {
    const newTableName = `_${tableName}_new`;

    const createTableSql = `CREATE TABLE ${newTableName} LIKE ${tableName}`;
    await db.queryAsync(createTableSql);
    console.log(`Created table ${newTableName} like ${tableName}`);

    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}`);

    const changeLengthSql = `ALTER TABLE ${newTableName} MODIFY space VARCHAR(100), MODIFY voter VARCHAR(100)`;
    await db.queryAsync(changeLengthSql);
    console.log(`Changed length of space and voter columns of table ${newTableName}`);

    const dropIndexesSql = `ALTER TABLE ${newTableName} 
        DROP INDEX id,
        DROP INDEX ipfs, 
        DROP INDEX voter, 
        DROP INDEX space, 
        DROP INDEX proposal, 
        DROP INDEX app, 
        DROP INDEX vp, 
        DROP INDEX vp_state, 
        DROP INDEX cb`;
    await db.queryAsync(dropIndexesSql);
    console.log(`Dropped indexes of table ${newTableName}`);

    // Insert data from the original table to the new table in batches
    const { minCreated, maxCreated } = (await db.queryAsync(`SELECT MIN(created) as minCreated, MAX(created) as maxCreated FROM ${tableName}`))[0];
    let lastCreated = (await db.queryAsync(`SELECT created FROM ${newTableName} ORDER BY created DESC LIMIT 1`))[0]?.created || minCreated;
    const batchSize = 50000;
    console.time('Time');
    while (true) {
        console.log(`Copying data from created >= ${lastCreated}`);
        const copyTableSql = `REPLACE INTO ${newTableName} SELECT * FROM ${tableName} WHERE created >= ? ORDER BY created ASC LIMIT ${batchSize}`;
        await db.queryAsync(copyTableSql, [lastCreated]);
        lastCreated = (await db.queryAsync(`SELECT created FROM ${newTableName} ORDER BY created DESC LIMIT 1`))[0].created;
        if(lastCreated >= maxCreated) {
            console.log('All data copied upto', lastCreated); 
            break;
        }
        console.timeLog('Time');
        console.log(`Progress: ${(((lastCreated - minCreated)/(maxCreated - minCreated))*100).toFixed(2)}% , lastCreated: ${lastCreated}`);
    }

    // Add back indexes
    const addIndexesSql = `ALTER TABLE ${newTableName} 
        ADD INDEX id(id),
        ADD INDEX ipfs(ipfs),
        ADD INDEX voter(voter),
        ADD INDEX space(space),
        ADD INDEX proposal(proposal),
        ADD INDEX app(app),
        ADD INDEX vp(vp),
        ADD INDEX vp_state(vp_state),
        ADD INDEX cb(cb)`;
    await db.queryAsync(addIndexesSql);
    console.log(`Added indexes to table ${newTableName}`);
    console.log('\nDone!! 🥳');
    console.timeEnd('Time');
    
    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();