Generated MySQL Columns and Changing Values

I was speaking at PHP[Tek] this week on the JSON Data Type and using generated columns. JSON columns can not be indexed but data from a JSON column can be extracted via a generated column and that column can be indexed. All was going well until someone asked me about modifying data in a generated column. Was it possible?

I blinked hard. I have not tried that! I had not seen any mention of that in the documentation. So I had to admit that I did not know and would have to try that.

The Test

mysql> CREATE TABLE gentest (a INT, b INT AS (a + 1) STORED, INDEX(b));
...
mysql> INSERT INTO gentest VALUES (1),(2),(3),(4);
...

So now we have a table with data to test. So lets try to modify the value of one of the generated columns.

mysql> UPDATE gentest SET b = 9 WHERE a = 1;

And what did the server do?

It returned an error and told me The value specified for generated column 'b' in table 'gentest' is not allowed.

Lesson Learned

So now I know that the server will not allow you to mangle, er, change the value of generated columns away from their definition. And yes, I find the same thing with both the VIRTUAL and STORED versions of generated columns.

Oracle Linux 7, MySQL 5.7.12 and YUM for JSON Document Store Part 1

Okay, I will admit that I grew up professionally without package management. Well, we did have SSCS and RCS under Unix and a lot of very brilliant shells scripts but nothing modern programmers would deign to touch. Then came Linux Torvalds (and why hasn't he been given a Nobel prize?!?!) and I blundered into the Redhat world with their RPM package manager. RPMS are great but over time I was seduced by Ubuntu and became and became an apt-get fiend.

Now I need to do a demo of the new MySQL 5.7.12 Document store features on an RPM based distro, Oracle 7.1. I could have used a Vagrant or Docker image but ended up using VirtualBox. You can even use a fresh install on hardware even though that seems to be becoming passe in a virtual & containerized world. So if you are following along please adjust to your platform.

After installing the latest and greatest Virtualbox from VirtualBox.org, I pulled the ISO image from Oracle. Then I set up a virtual machine using that ISO. And now ends the hard part.

The next step is to get the MySQL YUM repository configured on the system. And this is done by downloading a RPM with the needed info inside. The instructions show you how to use the rpm program to load this download. Then fire up your text editor of choice to pry open /etc/yum.repos.d/mysql-community.repo file. Make sure the enabled lines under the [mysql57-community] is set to 1 and that the other [mysql5X-community] have enabled lines set to zero. We can only use the new Docstore feature start with 5.7.12, so the earlier versions will not do.

Now set the enabled line to 1 under [mysql-tools-preview]. This loads the docstore software.

Now sudo yum install mysql-community-server mysql-shell to get all the software loaded. Then sudo service mysqld start will bring up the server. Please note the secure root password is in your log file and you will have to look in there in order to login the first time with a quick grep 'temporary password' /var/log/mysqld.log. Then login to the server and change the ugly generated password with your new password ALTER USER 'root'@'localhost' IDENTIFIED BY 'LessUglySecret1!';

So the server is set up as a regular relational server but we need to get the document store features setup. Simply type mysqlsh -u root -p --classic --dba enableXProtocol. Then it is a simple mysqlsh -u root -p --sql to have a SQL session on the server.

Next time: More document store explorations