MySQL

📘

Whitelist IPs

If your database network-access policy requires whitelisting our IPs first, you can find them here: https://docs.polytomic.com/docs/whitelist-ips.

1. Create a read-only account on your MySQL database. You will supply this account's credentials to Polytomic.

2. In Polytomic, go to Connections → Add Connection → MySQL.

3. Enter host and credential details.

4. Click Save.

MySQL as a Destination

Polytomic supports writing to MySQL tables as a destination. There are requirements for this:

1. The user Polytomic uses to connect to MySQL must have both INSERT and UPDATE permission grants (and CREATE if you want Polytomic to be able to create new tables).
2. You must connect Polytomic to a primary (master) database.

Once the above requirements are met, you can create a sync in Polytomic to write into your table as normal.

Polytomic requires that you specify an identity field to be used as a key when deciding to insert or update data. While not required, we recommend that this column have an index on it. Polytomic will only send INSERT statements for new records and UPDATE statements for existing records; it will not use INSERT ... ON DUPLICATE syntax.

Note: when setting up a Create or Create and Update sync, all non-nullable columns must be specified in your sync mapping.

Creating new tables

Polytomic can also create new tables in MySQL. Polytomic has two requirements for schema creation:

• The Polytomic MySQL user has to have the CREATE permission grant.
• You must be running MySQL 5.6+ for JSON support.

You should be able to create a new table using the Create new table.. target for MySQL. A new table will be created after you save the sync for the first time.

Once the table is created, the table will never be dropped or altered by Polytomic. If you want to change the schema, either modify the schema by hand and reflect the updates in Polytomic, or get Polytomic to create another new table.

Before saving the sync, you'll be asked to input a table name, which will be used when creating the new table. The new columns will be named and typed based on the source column names.

Polytomic creates new columns in MySQL with the largest types available to ensure the data will fit. For each selected field, Polytomic will create a table with the following column types:

Example

Suppose we want to create a new table that contains user IDs, their organization ID, the user's name, and the user's last login time.

When you attempt to create the table, Polytomic will execute the following:

CREATE SCHEMA IF NOT EXISTS `polytomic`;

CREATE TABLE `polytomic`.`last_login_info` (
	`__pt_rowid` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`users_id` LONGTEXT NOT NULL,
	`orgs_id` LONGTEXT,
	`name` LONGTEXT,
	`last_login` DATETIME,
	`polytomic_created_at` DATETIME,
	`polytomic_updated_at` DATETIME,
	CONSTRAINT idx_last_login_info_users_id UNIQUE (users_id (256)));

Note the following:

• An auto-incrementing key is created on all Polytomic tables.
• A unique index is created for the Identity value.
• Identity columns are not-nullable, but the rest of the fields are.
• Column names are normalized to remove non-compliant characters.
• Our models had a name conflict (Users.Id and Orgs.Id), so the column name includes the model name as well.
• String-typed identity fields index only the first 256 characters.
• Metadata fields are created to track both Creates and Updates.