<?php

// change these settings
$servername = 'localhost';
$username = 'dbuser';
$password = 'password';
$db = 'database';

// connect
$conn = new mysqli($servername, $username, $password);
try {
    $conn = new PDO("mysql:host=$servername;dbname=$db", $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="NO_ZERO_DATE"') );
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    echo "Connected successfully";
} catch(PDOException $e) {
    exit( "Connection failed: " . $e->getMessage() );
}

// get all table names
$stm = $conn->prepare('SHOW TABLES');
$stm->execute();
$table_names = array();
foreach ( $stm->fetchAll() as $row ) {
    $table_names[] = $row[0];
}

// for all tables
foreach ( $table_names as $table_name ) {
    echo "\nRepairing table $table_name...\n";

    // get the primary key name
    $stm = $conn->prepare( "show keys from $table_name where Key_name = 'PRIMARY'" );
    $stm->execute();
    $key_name = $stm->fetch()['Column_name'];

    // get the primary key type
    $stm = $conn->prepare( "show fields from $table_name where Field = '$key_name'" );
    $stm->execute();
    $key_type = $stm->fetch()['Type'];

    // if there is a primary key
    if ($key_name) {
        echo "Primary key is $key_name\n";

        try {
            // if auto_increment was missing there might be a row with key=0 . compute the next available primary key
            $sql = "select (ifnull( max($key_name), 0)+1) as next_id from $table_name";
            $stm = $conn->prepare( $sql );
            echo "$sql\n";
            $stm->execute();
            $next_id = $stm->fetch()['next_id'];

            // give a sane primary key to a row that has key = 0 if it exists
            $sql = "update $table_name set $key_name = $next_id where $key_name = 0";
            echo "$sql\n";
            $stm = $conn->prepare( $sql );
            $stm->execute();

            // set auto_increment to the primary key
            $sql = "alter table $table_name modify column $key_name $key_type auto_increment";
            echo "$sql\n";
            $stm = $conn->prepare( $sql );
            $stm->execute();

        } catch (PDOException $e) {
            echo "\n\nQuery: $sql\nError:" . $e->getMessage() . "\n\n";
        }
    } else {
        echo "No primary key found for table $table_name.\n";
    }
}
echo "\n\nFinished\n";
$conn = null;