Upgrading My ColdFusion Blog From MySQL 5.7.10 To MySQL 8.0.28

0
57


The other day, I asked my managed hosting provider to upgrade my MySQL version from 5.7.10 to 5.7.37 – the most recent general availability release of the 5.7 edition. I didn’t want to go any farther than that because I didn’t want to test the update – I just wanted access to the JSON functions and column-type. There was a little miscommunication, however, and the Support team ended up putting me on the latest version of MySQL, 8.0.28. This caused a few hours of accidental downtime (due to an incompatibility with the datasource connection-string). But, once I hotfixed that, the site appeared to be running smoothly. Once the dust settled, I took this an opportunity to cleanup a bunch of database schema and connectivity issues.

  1. the 8.0.22 JAR file, the connection error changed.

  2. Once I removed the old MySQL driver, the error message changed to include an issue with the timezone, or lack thereof:

    Connection verification failed for data source: bennadel

    java.sql.SQLException: The server time zone value ‘Coordinated Universal Time’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the ‘serverTimezone’ configuration property) to use a more specific time zone value if you want to utilize time zone support.

To hotfix this issue, I went into the datasource and added the following to my connection string:

serverTimezone=UTC

And poof, the site started working again!

In my experience, the database connection string is one of those parts of the application that tends to rot over time. As different versions of the database engine come-and-go, aspects of the connection string become irrelevant. And, since few of us “application developers” are also “database experts”, it’s challenging to look at the connection string and have a sense of what shouldn’t be there; and, perhaps more importantly, what is missing.

To start cleaning up my MySQL connection string, I went to the MySQL Connector/J 8.0 Developer Guide – Changes in Connection Properties and looked to see if anything I currently had was either removed or deprecated. I found three relevant properties that I currently had in my connection string that should not be there:

  • useUnicode=true (removed) – while not technically in this document, I found that useUnicode is no longer even listed as a property in the Connector/J connection options.

  • useLegacyDatetimeCode=false (removed) – removed in 8.0 version.

  • useDynamicCharsetInfo=false (removed) – removed in 8.0 version.

After looking at the Connector/J changes, I then went to the Connector/J 8.0 configuration properties and looked through the list to see what was available. I ended up including the following properties:

  • allowMultiQueries=true – I’ve long used this feature in my ColdFusion applications to allow me to execute more than one statement inside a single CFQuery tag. While some consider this to be a “Security issue”, I find it to be extremely helpful.

  • characterEncoding=UTF-8 – In order to get the ColdFusion datasource connection to work with emoji and other Astral Plane Unicode characters, I had to tell it to specifically use a UTF-8 charset.

  • serverTimezone=UTC – I don’t quite understand why I needed this since the ColdFusion server is running in UTC already. But, apparently this helps the driver figure out how to apply date/time conversions on the data coming out of the database.

  • tinyInt1isBit=falseI always use TINYINT instead of BIT in my ColdFusion applications. And, I don’t want the MySQL driver trying to magically turn anything into a BIT on my behalf. I am expecting my “Boolean values” to be coming back as 1 or 0.

  • useConfigs=maxPerformance – Because I like the sound of max performance!

For this last one – useConfigs – I had a hard time finding any documentation on it at all. I finally went into the GitHub repository for the Connector/J Driver and found the .properties file that gets loaded for maxPerformance:

#
# A configuration that maximizes performance, while
# still staying JDBC-compliant and not doing anything that
# would be "dangerous" to run-of-the-mill J2EE applications
#
# Note that because we're caching things like callable statements
# and the server configuration, this bundle isn't appropriate
# for use with servers that get config'd dynamically without
# restarting the application using this configuration bundle.

cachePrepStmts=true
cacheCallableStmts=true

cacheServerConfiguration=true

#
# Reduces amount of calls to database to set
# session state. "Safe" as long as application uses
# Connection methods to set current database, autocommit
# and transaction isolation
# 

useLocalSessionState=true
elideSetAutoCommits=true
alwaysSendSetIsolation=false

# Can cause high-GC pressure if timeouts are used on every
# query
enableQueryTimeouts=false

# Bypass connection attribute handling during connection
# setup
connectionAttributes=none

In the end, here’s the value that I have in the Connection String property of my ColdFuision datasource:

allowMultiQueries=true&characterEncoding=UTF-8&serverTimezone=UTC&tinyInt1isBit=false&useConfigs=maxPerformance

I will strongly caveat though that, as stated above, I am not a database expert. As such, take all of this with a grain of salt – this is just what I setup, this is not a recommendation for your setup.

updated a few table columns to be utf8mb4 in order to allow people to post emoji in my blog comments. This was the least amount of work that I could in order to enable that feature. But, with this upgrade to MySQL 8, I wanted to take a moment to actually modernize all of my tables and character sets.

I really only have a vague understanding of how all the character set encodings work, so I turned to Mathias Bynens’ article, How to support full Unicode in MySQL databases. Based on what he has there – and some stuff that I read on StackOverlow about collation – I wrote a bunch of ALTER statements to change:

  • The default character set on the database.
  • The default character set on the tables.
  • Update the character set of any text-based field.

Basically, I changed all the character sets to utf8mb4 and all the collations to utf8mb4_0900_ai_ci:

NOTE: You may notice that I have a mixture of headless-camel-case and snake-case in my table column names. This is just a change in my preferred style over time. I intend to go back and update all the columns to be headless-camel-case eventually.

/* Change the default charset for the database. */
ALTER DATABASE bennadel
	DEFAULT CHARACTER SET utf8mb4
	DEFAULT COLLATE utf8mb4_0900_ai_ci
;

/* Change the default charset for all the tables. */
ALTER TABLE blog_comment
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE blog_comment_edit_token
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE blog_entry
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE blog_entry_blog_entry_jn
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE blog_entry_subscription
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE blog_entry_tag_jn
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE member
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE member_approval
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE pending_blog_comment
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;
ALTER TABLE tag
	CHARACTER SET utf8mb4,
	COLLATE utf8mb4_0900_ai_ci
;

/* Update the text columns to have the new charset. */
ALTER TABLE
	blog_comment
MODIFY COLUMN
	content longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	content_markdown longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
;
ALTER TABLE
	blog_comment_edit_token
MODIFY COLUMN
	value varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
;
ALTER TABLE
	blog_entry
MODIFY COLUMN
	name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	description longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	content longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	content_markdown longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	meta_keywords varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	meta_description text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
;
ALTER TABLE
	member
MODIFY COLUMN
	name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	email varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	url varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
;
ALTER TABLE
	pending_blog_comment
MODIFY COLUMN
	content longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	contentMarkdown longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	authorName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	authorEmail varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
MODIFY COLUMN
	authorUrl varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
;
ALTER TABLE
	tag
MODIFY COLUMN
	name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL
;

/* Optimize tables with new changes. */
OPTIMIZE TABLE blog_comment;
OPTIMIZE TABLE blog_comment_edit_token;
OPTIMIZE TABLE blog_entry;
OPTIMIZE TABLE member;
OPTIMIZE TABLE pending_blog_comment;
OPTIMIZE TABLE tag;

As part of the column ALTER statements, I took the opportunity to remove a bunch of DEFAULT values. At this point in my career, I kind of prefer not having any defaults so that the code throws an error if I’m missing something in my INSERT statements. And, if I find that I have code doesn’t “do anything”, then I’d rather remove that column from the database rather than having a bunch of meaningless defaults.

I also increased a number of varchar fields to be 255 in length. I’m currently mid-thought on how I feel about varchar field length – more to come on that in a future post. I was also a little afraid that I would get some sort of accidental value truncation with the new character set – though, that fear may have been unfounded?





Source link

Leave a reply

Please enter your comment!
Please enter your name here