Creating the ColdFusion Client Variable Storage Database and Tables within MySQL

ColdFusion does not create the proper tables for client variable storage when using MySQL as the database engine.  This is valid in ColdFusion 9 and also still present in ColdFusion 10.  These tables should be created manually for client variable storage to function.

Two tables are required. CDATA and CGLOBAL. Details about theses tables are as follows:

The CDATA table must have the following columns:

Column | Data type

cfid | CHAR(64), TEXT, VARCHAR, or any data type capable of taking variable length strings up to 64 characters

app | CHAR(64), TEXT, VARCHAR, or any data type capable of taking variable length strings up to 64 characters

data | MEMO, LONGTEXT, LONG VARCHAR, CLOB, or any data type capable of taking long, indeterminate-length strings

The CGLOBAL table must have the following columns:

Column | Data type

cfid | CHAR(64), TEXT, VARCHAR, or any data type capable of taking variable length strings up to 64 characters

data | MEMO, LONGTEXT, LONG VARCHAR, CLOB, or any data type capable of taking long, indeterminate-length strings

lvisit | TIMESTAMP, DATETIME, DATE, or any data type that stores date and time values

If the database has not yet been created, you can create the database and tables with this:

CREATE DATABASE %cfvars_db_name%;
USE %cfvars_db_name%;
/*Table structure for table cdata */
DROP TABLE IF EXISTS cdata;
CREATE TABLE cdata (
cfid char(64) NOT NULL,
app char(64) NOT NULL,
data text,
UNIQUE KEY id1 (cfid,app)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Table structure for table cglobal */
DROP TABLE IF EXISTS cglobal;
CREATE TABLE cglobal (
cfid char(64) NOT NULL,
data text,
lvisit datetime NOT NULL,
KEY id2 (cfid),
KEY id3 (lvisit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To create the tables, the following queries can be used (this is if you have already created the database and have an appropriately privileged user that is able to access the database):

CREATE TABLE cdata (
cfid varchar(64) NOT NULL default '',
app varchar(64) NOT NULL default '',
data longtext NOT NULL,
PRIMARY KEY (cfid,app)
);
CREATE TABLE cglobal (
cfid varchar(64) NOT NULL default '',
data longtext NOT NULL,
lvisit timestamp,
KEY cfid (cfid),
KEY lvisit (lvisit)
);