feat: support MySQL CTAS #67
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
| name: MySQL Copy Instance Test | |
| on: | |
| push: | |
| branches: [ "main" ] | |
| pull_request: | |
| branches: [ "main" ] | |
| jobs: | |
| copy-instance-test: | |
| runs-on: ubuntu-latest | |
| services: | |
| source: | |
| image: mysql:lts | |
| env: | |
| MYSQL_ROOT_PASSWORD: root | |
| ports: | |
| - 13306:3306 | |
| options: >- | |
| --health-cmd="mysqladmin ping" | |
| --health-interval=10s | |
| --health-timeout=5s | |
| --health-retries=3 | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - name: Set up Go | |
| uses: actions/setup-go@v5 | |
| with: | |
| go-version: '1.23' | |
| - name: Set up Python | |
| uses: actions/setup-python@v5 | |
| with: | |
| python-version: '3.13' | |
| - name: Install dependencies | |
| run: | | |
| go get . | |
| pip3 install "sqlglot[rs]" | |
| curl -LJO https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell_9.1.0-1debian12_amd64.deb | |
| sudo apt-get install -y ./mysql-shell_9.1.0-1debian12_amd64.deb | |
| - name: Setup test data in source MySQL | |
| run: | | |
| mysqlsh -hlocalhost -P13306 -uroot -proot --sql <<'EOF' | |
| CREATE DATABASE testdb; | |
| USE testdb; | |
| -- Normal table, which should be copied to MyDuck via duckdb's csv import | |
| CREATE TABLE users ( | |
| id INT AUTO_INCREMENT PRIMARY KEY, | |
| name VARCHAR(100), | |
| status ENUM('active', 'inactive', 'pending') DEFAULT 'pending' | |
| ); | |
| INSERT INTO users (name, status) VALUES | |
| ('test1', 'active'), | |
| ('test2', 'inactive'), | |
| ('test3', 'pending'); | |
| -- Make a gap in the id sequence | |
| INSERT INTO users VALUES (100, 'test100', 'active'); | |
| INSERT INTO users (name, status) VALUES ('test101', 'inactive'); | |
| -- A table with non-default starting auto_increment value | |
| CREATE TABLE items ( | |
| id INT AUTO_INCREMENT PRIMARY KEY, | |
| v BIGINT check (v > 0), | |
| name VARCHAR(100) | |
| ) AUTO_INCREMENT=1000; | |
| INSERT INTO items (v, name) VALUES (1, 'item1'), (2, 'item2'), (3, 'item3'); | |
| -- Table with UUID primary key | |
| -- For such tables, MySQL Shell generates nontrivial LOAD DATA statements | |
| -- to copy the data to MyDuck: LOAD DATA ... (@id, title, created_at) SET id = FROM_BASE64(@id), | |
| -- which can only be executed by the go-mysql-server framework for now. | |
| CREATE TABLE documents ( | |
| id BINARY(16) PRIMARY KEY, | |
| title VARCHAR(200), | |
| status ENUM('draft', 'published', 'archived') DEFAULT 'draft', | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | |
| ); | |
| INSERT INTO documents (id, title, status) VALUES | |
| (UUID_TO_BIN(UUID()), 'Document 1', 'published'), | |
| (UUID_TO_BIN(UUID()), 'Document 2', 'draft'); | |
| EOF | |
| - name: Build and start MyDuck Server | |
| run: | | |
| go build -v | |
| ./myduckserver & | |
| sleep 5 | |
| - name: Run copy-instance test | |
| run: | | |
| # Set local_infile to true to allow loading data from files | |
| mysqlsh -uroot --no-password --sql -e "SET GLOBAL local_infile = 1;" | |
| # Copy the data from source MySQL to MyDuck | |
| mysqlsh -hlocalhost -P13306 -uroot -proot \ | |
| -- util copy-instance "mysql://root:@127.0.0.1:3306" \ | |
| --users false --ignore-version true | |
| # Verify the data was copied | |
| for table in users items documents; do | |
| mysqlsh -hlocalhost -P13306 -uroot -proot --sql -e " | |
| SELECT * FROM testdb.$table ORDER BY id; | |
| " | tee source_data_$table.tsv | |
| mysqlsh -uroot --no-password --sql -e " | |
| SELECT * FROM testdb.$table ORDER BY id; | |
| " | tee copied_data_$table.tsv | |
| diff source_data_$table.tsv copied_data_$table.tsv | |
| done | |