sqlite3tomysql

KO | EN

gitlite — search

#javascript #python #hacktoberfest #react #ai #typescript #llm #go #golang #android #machine-learning #rust #deep-learning #linux

★ 14 Open GitHub ↗

Sqlite3 to mariadb migration script

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# sqlite3tomysql
Sqlite3 to mariadb migration script. In theory this could be applied for any sqlite -> MariaDB migration but it hasn't been tested. Nevertheless, it won't make changes to kuma.db, so you could run against target MariaDB & see if this script does the job.

All credits to [Seamlessly Migrating SQLite Databases to MySQL: A Comprehensive Python Guide](https://medium.com/@gadallah.hatem/seamlessly-migrating-sqlite-databases-to-mysql-a-comprehensive-python-guide-f8776f50e356).

## How to execute the script?

### Prerequisites

Before running the migration script, ensure you have the following setup:

1. **Python Environment**
   - Python 3.6 or higher installed
   - Install required Python package:
     ```bash
     pip install mysql-connector-python
     ```

2. **MySQL/MariaDB Database Setup**
   - Create a database and user with appropriate privileges:
     ```sql
     CREATE DATABASE kuma CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
     CREATE USER 'kuma_user'@'localhost' IDENTIFIED BY 'your_secure_password';
     GRANT ALL PRIVILEGES ON kuma.* TO 'kuma_user'@'localhost';
     FLUSH PRIVILEGES;
     ```

3. **Configure Script Variables**
   
   Edit the variables at the end of `migrate.py` file:
   
   ```python
   # Update SQLite database file path
   sqlite_database_file = 'data/kuma.db'  # Path to your SQLite database
   
   # Update MySQL connection configuration
   mysql_connection_config = {
       'host': 'localhost',           # MySQL server host
       'user': 'kuma_user',          # Database user
       'password': 'your_secure_password',  # User password
       'database': 'mysqldatabase',            # Target database name
       # 'collation': 'utf8mb4_uca1400_ai_ci' # Optional: override collation (default: utf8mb4_unicode_ci)
   }
   ```

### Script Features

The migration script provides comprehensive SQLite to MySQL/MariaDB migration with the following capabilities:

- **Configurable Collation**: Supports custom collations via the `collation` configuration option (default: `utf8mb4_unicode_ci`)
  - Solves errors like `1273 (HY000): Unknown collation` on newer MariaDB versions
- **Smart Type Mapping**: Automatically converts SQLite data types to appropriate MySQL equivalents
  - Correctly maps `TIME` columns to MySQL `TIME` (not `DATETIME`)
  - Handles `DATE`, `DATETIME`, and `TIMESTAMP` appropriately
- **MySQL vs MariaDB Compatibility**: Automatically detects the target server type and adjusts schema generation
  - Skips DEFAULT values for TEXT, LONGTEXT, BLOB, JSON, and GEOMETRY columns on MySQL (error 1101 prevention)
  - Retains DEFAULT values for these types on MariaDB (which supports them)
- **Reserved Word Handling**: Escapes MySQL reserved keywords (like `group`, `order`, `key`) with backticks
- **Index Compatibility**: Handles MySQL's 767-byte index limit by adjusting VARCHAR lengths for indexed columns
- **Foreign Key Management**: Temporarily disables foreign key checks during migration
- **Batch Processing**: Processes data in batches of 1000 rows for better performance
- **Error Recovery**: Uses `INSERT IGNORE` to skip duplicate key errors and continue processing
- **Timestamp Conversion**: Converts Unix timestamps to MySQL DATETIME format (especially for `knex_migrations` table)
- **Auto-increment Handling**: Properly maps SQLite INTEGER PRIMARY KEY to MySQL AUTO_INCREMENT

### Execution

Run the migration script:

```bash
python3 migrate.py
```

The script will prompt for confirmation before proceeding with the migration.

### Example Run Log

```
python3 migrate.py
WARNING: This will drop and recreate tables in MySQL database 'mysqldatabase'. Are you sure? (yes/no): yes
Connected to SQLite database: data/kuma.db
Connected to MySQL database: kuma
Disabled MySQL foreign key checks.
Found tables in SQLite: ['heartbeat', 'sqlite_sequence', 'user', 'notification', 'monitor_notification', 'tag', 'monitor_tag', 'setting', 'incident', 'group', 'monitor_group', 'notification_sent_history', 'docker_host', 'status_page', 'status_page_cname', 'maintenance', 'maintenance_status_page', 'monitor_maintenance', 'api_key', 'sqlite_stat1', 'knex_migrations', 'knex_migrations_lock', 'remote_browser', 'monitor_tls_info', 'proxy', 'monitor', 'stat_daily', 'stat_hourly', 'stat_minutely']

Processing table: `heartbeat`
Generated CREATE TABLE statement:
CREATE TABLE IF NOT EXISTS `heartbeat` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `important` TINYINT(1) NOT NULL DEFAULT 0,
    `monitor_id` INT UNSIGNED NOT NULL,
    `status` SMALLINT NOT NULL,
    `msg` LONGTEXT,
    `time` DATETIME NOT NULL,
    `ping` INT UNSIGNED,
    `duration` INT UNSIGNED NOT NULL DEFAULT 0,
    `down_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `end_time` DATETIME DEFAULT NULL,
    `retries` INT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Table `heartbeat` created in MySQL.
Copying 15234 rows to `heartbeat` using: INSERT IGNORE INTO `heartbeat` (`id`,`important`,`monitor_id`,`status`,`msg`,`time`,`ping`,`duration`,`down_count`,`end_time`,`retries`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
Successfully processed 16 batches out of 16 for table `heartbeat`
Data copied to `heartbeat`.

Processing table: `user`
Generated CREATE TABLE statement:
CREATE TABLE IF NOT EXISTS `user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255),
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `timezone` VARCHAR(150),
    `twofa_token` VARCHAR(32),
    `twofa_last_token` VARCHAR(32),
    `twofa_status` TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Table `user` created in MySQL.
Copying 1 rows to `user` using: INSERT IGNORE INTO `user` (`id`,`username`,`password`,`active`,`timezone`,`twofa_token`,`twofa_last_token`,`twofa_status`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
Successfully processed 1 batches out of 1 for table `user`
Data copied to `user`.

...

Processing table: `stat_minutely`
Generated CREATE TABLE statement:
CREATE TABLE IF NOT EXISTS `stat_minutely` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `monitor_id` INT UNSIGNED NOT NULL,
    `timestamp` DATETIME NOT NULL,
    `ping` INT UNSIGNED,
    `up` INT UNSIGNED NOT NULL DEFAULT 0,
    `down` INT UNSIGNED NOT NULL DEFAULT 0,
    `ping_min` INT UNSIGNED DEFAULT 0,
    `ping_max` INT UNSIGNED DEFAULT 0,
    `extras` LONGTEXT,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Table `stat_minutely` created in MySQL.
Copying 42264 rows to `stat_minutely` using: INSERT IGNORE INTO `stat_minutely` (`id`,`monitor_id`,`timestamp`,`ping`,`up`,`down`,`ping_min`,`ping_max`,`extras`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)
Successfully processed 43 batches out of 43 for table `stat_minutely`
Data copied to `stat_minutely`.

Foreign key checks re-enabled in MySQL.
Database connections closed.
```

## Uptime Kuma Migration Steps

For migrating Uptime Kuma from SQLite3 to MariaDB, follow these steps carefully:

### Prerequisites
⚠️ **Important**: First bump to V2 using SQLite3 database before migrating to MariaDB. This ensures migration scripts get executed and your schema will be in good shape.

**Do not migrate from SQLite3 to MariaDB while going from v1 to v2** - this will not work as v2 is a major version and your v1 schema is not compatible with v2.

### Migration Process

1. **Shutdown Uptime Kuma**
   - Shutdown the Uptime Kuma node to ensure data consistency during migration

2. **Execute Migration Script**
   - Feed in MariaDB details in the Python script & execute it
   - Depending on the data volume (rows), migration may take a couple of minutes
   - Example: Migration with moderate data can take around 2 minutes

3. **Update Docker Configuration**
   - Bump docker image to 2.x version
   - Add MariaDB details to docker environment variables
   - Start the container

4. **Verification**
   - Boom, you have Uptime Kuma now running on MariaDB!

### Known Quirks Handled

This script handles several quirks specific to the migration:
- **Collation Compatibility**: Allows overriding the connection and table collation to resolve compatibility issues (e.g., using `utf8mb4_uca1400_ai_ci` on newer MariaDB or `utf8mb4_general_ci` on older systems)
- **Reserved Keywords**: Tables named `group` (MariaDB won't allow as it's a reserved keyword, but the script circumvents this with escape characters)
- **TIME vs DATETIME Mapping**: Correctly preserves `TIME` columns (like `start_time`/`end_time` in `maintenance` table) as `TIME` in MySQL instead of incorrectly converting to `DATETIME`
- **Index Length Limitations**: Handles MySQL's 767-byte index key length limit by adjusting VARCHAR sizes for indexed columns
- **MySQL DEFAULT Restrictions**: Automatically detects MySQL and skips DEFAULT values for TEXT, LONGTEXT, BLOB, JSON, and GEOMETRY columns (MySQL error 1101), while preserving them for MariaDB
- Other edge cases are handled automatically by the script

sqlite3tomysql

Explore Similar Repositories

Was this content helpful?

Recent Feedback

Download README