OpenVMS Notes: MySQL and MariaDB

  1. The information presented here is intended for educational use by qualified OpenVMS technologists.
  2. The information presented here is provided free of charge, as-is, with no warranty of any kind.
  3. Is this text too small? You have two options:
    1. hold down the CTRL key while rolling the mouse wheel (zoom-in, zoom-out)
    2. use your keyboard like so:
      • hit: CTRL with "-" key to zoom smaller
      • hit: CTRL with "+" key to zoom larger
      • hit: CTRL with zero key to reset zoom
Edit: 2018-08-17
Relational Database Caveat: (please read all items in this disclaimer before continuing)
Recent (2018) Announcement: in the Q&A at the end of a webinar on 2018-04-25, VSI (VMS Software Inc) announced that they are planning to offer a supported port of MariaDB (and MySQL) on OpenVMS. Apparently, regulatory laws forbid many companies (medical sector, transportation sector, insurance sector, etc.) from using software which is not tied to an official support contract. While Mariadb Corporation Ab and Oracle both offer support contracts for their respective offerings on numerous platforms including Linux, Unix and Windows, neither support their respective products on OpenVMS.



A few technical details



A few technical details

Software for OpenVMS

Caveat: On this web-page I have included many more problems with MariaDB but that is only because it was the first relational database engine I placed into production on OpenVMS. Make no mistake: NEWER means BETTER and FASTER




Executive Summary

MySQL + MariaDB Tips (all versions)

  1. Before changing the root password, first experiment with a cloned root account:
  2. Now change the root password (remember to issue 'flush privileges')
  3. ODBC Access from a remote system
  4. A few MySQL commands for newbies and occasional users:
     To show the current date and time:
    	select now(),curdate(),curtime();
    To display engine variables beginning with the letter 'v':
    	show variables like 'v%';				-+- these commands produce the same result
    	show variables like 'v%' \g				-+	(because '\g' is a synonym for ';')
    	show variables like 'v%' \G				returns results without a pseudo-graphic box  
    To display all the databases contained within a db instance:
    	show databases;
    To display all the tables within database mysql:
       this command:
    	show tables from mysql;
       is the same as these two:
    	use mysql;
    	show tables;
    To display technical details about table mysql.user:
       this command:
    	describe mysql.user;
       is the same as these two:
    	use mysql;
    	describe user;
    To display all data within a table:
       this command:
    	select * from mysql.user;
       is the same as these two:
    	use mysql;
    	select * from user;
    To display selected fields from within a table:
    	select host,user,password from mysql.user;
    To display desired records from within a table:
    	select host,user,password from mysql.user where user='neil';
    To display desired records (via wildcard) from within a table:
    	select host,user,password from mysql.user where user like 'n%';		(starts with: n  )
    	select host,user,password from mysql.user where user like '%l';		(ends with:   l  )
    	select host,user,password from mysql.user where user like '%ei%';	(contains:    ei )
    	select host,user,password from mysql.user where ucase(user) like 'N%';	(returns: neil, Neil, etc.)
    To display the execution plan of various queries:
    	use mysql;
    	explain select password from user;			(extra: blank)
    	explain select host     from user;			(extra: Using index)
    	explain select host     from user order by host;	(extra: Using index)
    	explain select user     from user;			(extra: Using index)
    	explain select user     from user order by user;	(extra: Using index; Using filesort)
    To see the indexes associated with a table:
    	show indexes from user;					(Host, User)
    To see all indexes in database onuadmin:
    	WHERE TABLE_SCHEMA = 'onuadmin'; 
    To see available storage engines:
    	show engines;				(MEMORY, MRG_MYISAM, MyISAM, BLACKHOLE, CSV, Aria, 
    Duplicate a table so you can hack:
    	use icsis;				(switch to database 'icsis')
    	create table yada like profile;		(create empty table 'yada' using table 'profile' as a template)
    	insert into yada select * from profile;	(copy data from profile to yada)

  5. (some basic) Maintenance Commands:
    0) Stopping a runaway transaction or run-too-long transaction
    	Problem: A very complicated command may take forever to execute (while consuming 100% of the server's resources)
    	but killing the client process which issued the command will not stop the associated transaction. Here is how
    	you can regain control.
    	Update: what I previously thought was a runaway transaction turned out to be a run-too-long transaction. We were
    	doing a multi-table join but one of the columns was declared as UTF-8 whilst the other was declared as latin1
    	(a.k.a. ISO-8859-1). It turns out that when MariaDB sees this situation (comparing apples to oranges), it ignores
    	the offending index then attempts to process the transaction using raw uncollated data. If the table is large then
    	it might appear to take forever. (oops)
    	<sr> $
    	<ur> mysql --user=neil --password=passwd123
    	<sr> MariaDB [(none)]>
    	<ur> show processlist;
    	| Id      | User  | Host                          | db       | Command | Time    | State                           | Info          |
    	| 1816400 | dave  | | onuadmin | Sleep   |     276 |                                 | NULL          |
    	| 1816401 | dave  | | icsis    | Sleep   |     276 |                                 | NULL          |
    	| 1846611 | dave  | | NULL     | Sleep   | 1908905 |                                 | NULL          |
    	| 2021829 | neil  | localhost:3568                | onuadmin | Query   |    1321 | Sending data                    | create table r|
    	| 2021951 | neil  | localhost:3220                | onuadmin | Query   |     690 | Waiting for table metadata lock | select* from r|
    	| 2021955 | neil  | localhost:4124                | onuadmin | Query   |     641 | Waiting for table metadata lock | select count(*|
    	| 2021959 | vince | | onuadmin | Query   |     594 | Waiting for table metadata lock | SHOW COLUMNS F|
    	| 2021960 | vince | | onuadmin | Sleep   |     323 |                                 | NULL          |
    	| 2021962 | neil  | localhost:4715                | NULL     | Query   |       0 | NULL                            | show processli|
    	| 2022035 | vince | | NULL     | Sleep   |     255 |                                 | NULL          |
    	| 2022036 | vince | | NULL     | Sleep   |       2 |                                 | NULL          |
    	MariaDB [(none)]>
    	<ur> kill 2021829;
    	comment: at this point you should be thinking "MySQL/MariaDB is a lot like having an OS within an OS"
    1) Light Maintenance (no locking)
    	$ mysql --user=neil --password=passwd123
    	steps:	create new indexes;
    		drop old indexes;
    		execution plans will shift to new indexes;
    2) Medium Maintenance (minimal locking)
    	$ mysql --user=neil --password=passwd123
    	show databases;
    	use database whatever;
    	analyze table yada;
    	optimize table yada;
    3) Heavy Maintenance (locking)
    	mysqlcheck --help						# view the plethora of options
    									# including: -q and -e
    	mysqlcheck --user=neil --pass=passwd123 -c --all-databases	# check all tables in all databases
    	mysqlcheck --user=neil --pass=passwd123 -C --all-databases	# check changed tables in all databases
    	mysqlcheck --user=neil --pass=passwd123 -o --all-databases	# optimize all databases
    4) To shut down MySQL-5.1/and MariaDB-5.5-25( add these to script sys$manager:SYSHUTDWN.COM )
    	$ mysqladmin --user=neil --pass=passwd123 ping		! are you there?
    	$ mysqladmin --user=neil --pass=passwd123 ver		! display stats
    	$ mysqladmin --user=neil --pass=passwd123 refresh	! flushes a lot of stuff to disk
    	$ mysqladmin --user=neil --pass=passwd123 status	! display stats one-liner
    	$ mysqladmin --user=neil --pass=passwd123 shutdown	! should not see any error messages here
    	$ wait 0:0:05						! this 5-second delay is necessary
    	$ mysqladmin --user=neil --pass=passwd123 shutdown	! this second step is necessary ...
    	$! ... you will see an error message which can be blocked by including the "--silent" switch;
    	$! the MariaDB_Server process will usually exit within 10-20 minutes;
    	$! see InnoDB caveats further down this web page
    	$! see MariaDB-5 shutdown problems further down this web page
    6) Test for corrupt Tables (assumes you have brought up the server on port 8080):
    	$ mysqlcheck --user=neil --pass=passwd123 --port=8080 --fast --all-databases		# check tables not properly closed
    	$ mysqlcheck --user=neil --pass=passwd123 --port=8080 -c --quick --all-databases	# quick    check all tables in all db 
    	$ mysqlcheck --user=neil --pass=passwd123 --port=8080 -C --quick --all-databases	# quick    check changed tables  
    	$ mysqlcheck --user=neil --pass=passwd123 --port=8080 -c --all-databases		# normal   check
    	$ mysqlcheck --user=neil --pass=passwd123 --port=8080 -c --extended --all-databases	# extended check
    7) Repair Corrupt Table (real world example; table must be MyISAM; not InnoDB)
    	<ur>	mysqlcheck --user=neil --pass=passwd123 --port=8080 --repair mysql columns_priv
    	<sr>	mysql.columns_priv
    		warning  : Number of rows changed from 0 to 129
    		status   : OK
    	$! standalone recovery tools exist for MyISAM (the database does not need to be running) 
    8) Database backup (logical)
    	$ mysqldump --user=neil --pass=passwd123 --port=8080 --result-file=icsis.sql --databases icsis
    9) Database restore
    	$ mysqladmin --user=neil --pass=passwd123 --port=8080 --force drop material		# drop corrupt database
    	$ mysqladmin --user=neil --pass=passwd123 --port=8080 create database icsis		# create new database
    	$ mysql      --user=neil --pass=passwd123 --port=8080 -e "source icsis.sql" icsis	# restore
    10) Purging binary logs
    	caveat: it is dangerous to use the OS to delete files in the 'log' folder
    	<ur>	mysql --user=neil --pass=passwd123
    	<sr>	MariaDB [(none)]>
    	<ur>	show master logs;
    	<sr>	+--------------------+-----------+
    		| Log_name           | File_size |
    		| mariadb-bin.000001 |       290 |
    		| mariadb-bin.000002 |       264 |
    		| mariadb-bin.000011 |       264 |
    		| mariadb-bin.000012 |       245 |
    		MariaDB [(none)]>
    	<ur>	purge master logs before current_date - interval 1 day;
    		Query OK, 0 rows affected (0.31 sec)
    		MariaDB [(none)]>
    	<sr>	show master logs;
    		| Log_name           | File_size |
    		| mariadb-bin.000012 |       245 |
    		1 row in set (0.00 sec)
    		MariaDB [(none)]>

  6. MariaDB-5.5-25 Long Shutdown Problem
  7. (some advanced) Maintenance Commands
    1. if your server won't start do not panic; you most likely have not lost any data (yet)
    2. you first want to get the server limping along in recovery mode (perhaps on different port)
      • this is also known as read-only mode (recovery modes higher than 3 are dangerous)
      • your clients will be able to read but not insert or update
      • you will be able to create fresh database dumps to text files
      • you will be able to drop/create databases + tables as well as use the source command to load everything back in
    3. You want to run some checks on all the databases
    4. MyISAM tables are repairable with external applications (the database does not need to be running)

      --- DANGER DANGER DANGER --- the following stuff is past the point of no return ---
    5. use mysqldump to produce a backup copy of the corrupted database(s) into whatever.sql
          Did you see any errors? If so then you might want to use mysqldump to produce a backup copy of individual tables
    6. drop the corrupt database(s) only if you have logical backups
    7. connect with mysql client then use 'source' to import whatever.sql
      --- Dealing with InnoDB startup problems ---
    8. I have never been able to fix InnoDB startup problems with repair tools (and have invested a lot of time trying)
      1. if the database is not running then move all binary logs to another location. In my case these are all of the form:
        Don't worry, the server will recreate new binary-log files as well as the binary-log index.
      2. use mysqldump to create logical backups to whatever.sql
      3. drop all databases containing InnoDB tables only if you have logical backups
      4. connect with my_sql client then use source to import whatever.sql

    Starting a server that will not remain running
    	$ set def MYSQL055_ROOT:[000000.vms]
    	$ edit file: my.cnf
    	  insert these directives under [mysqld]
    		innodb_force_recovery=1 	# or 2-3 (safe) then 4-6 (dangerous); always try lower levels first
    		innodb_purge_threads=0		#
    		port=8080			# switch to some non-production port
    	$! innodb_force_recovery notes:
    	$!	0: 	Production    (tables can be written/modified)
    	$!	1 to 6: Recovery mode (tables cannot be written or modified but can be dropped)
    	$!		Use the lowest level that allows the server to run and no higher
    	$!		Tables can be dumped to files (emergency backup before table rebuild)
    	$!		4-6 are dangerous and can cause data loss so only use them in an emergency
    	$! 1) After startup in recovery mode, consider an immediate shutdown to inspect messages logged in files
    	$!	under folder "MYSQL055_ROOT:[mysql_server]". These messages should point you in the correct
    	$!	direction. For example,	myisamchk can be used to repair MyISAM tables when MariaDB is not running
    	$!				mysqlcheck can be used to repair most tables when MariaDB is running
    	$! 2) Recovery mode is not magic (it only turns off some internal checks) so you now have limited options
    	$!    a) restart the database in recovery mode then use mysqldump to make logical backups of all your
    	$!	 databases. These will be used to do a hard recovery if required.
    	$!    b) shutdown MariaDB
    	$!    c) backup everything under:
    	$!		MYSQL055_ROOT:[data]		# which holds your database tables 
    	$!		MYSQL055_ROOT:[log]		# which holds your transaction files
    	$!    d) if you have corrupt tables then you might wish should attempt a repair first (success rate: 75%)
    	$!    e) if you do not have corrupt tables but have some sort of InnoDB problem then you might wish to
    	$!	 try this (success rate is 33%) before doing a drop/recover:
    	$!		1) delete all the binary logs of the form: mariadb-bin.* then try a restart
    	$!			deleting is safe because you made a backup, right?
    	$!		2) if that does not work then also delete the redo files (ib_logfile0. and ib_logfile1.)
    	$!			deleting is safe because you made a backup, right?
    	$!                      Caveat: do not delete other files in the [.log] folder
    	$!    f) if "step-E" did not work then consider starting mariadb then dropping all the tables (or whole
    	$!	 databases) before attempting a hard recovery from your logical backups this should work but will
    	$!	 be time consuming (perhaps you should have been using master/slave replication) 
    	$!    g) if "step-F" did not work then delete everything under:
    	$!			MYSQL055_ROOT:[data]		# which holds your database tables 
    	$!			MYSQL055_ROOT:[log]		# which holds your transaction files
    	$!	 now invoke your original setup script (previous was only used during the initial installation)
    	$!	 beware: the script usually copies over a new version of my.cnf
    	$!	 Now do a hard recovery from here

  8. my demo apps written in "C/C++"
  9. my data export tools written in BASIC
  10. Stored Procedures (a few examples)
    1. A really simple demo
      -- file : neil_sp_demo_1.sql
      -- target: mysql-5.5 / mariadb-5.5
      -- notes : this is not a stored procedure but it demos session variables
      -- =====================================================================
      use icsis;
      -- this is a simple row counter
      select count(*) from profile;
      -- this same code employs an intermediate session variable ('sv_' prefix for my sanity) 
      set @pv_temp1 = 0;
      select count(*) from profile into @sv_temp1;
      select @sv_temp1;
    2. really simple demo with a cursor (accepts nothing; returns nothing)
      -- title : NEIL_SP_DEMO_2.SQL
      -- target: mysql-5.5 / mariadb-5.5
      -- notes : this stored procedure implements a cursor which does nothing special
      --       : nothing is formally passed to/from the procedure but a session variable is modified
      -- ===========================================================================================
      use icsis;
      drop procedure if exists neil_sp_demo2;
      delimiter ||
      create procedure neil_sp_demo2()
        -- need some local variables ('lv_' prefix is for my sanity)
        declare lv_stop INT;
        declare lv_my_count INT;
        declare lv_last_name char(25);
        declare lv_first_name char(25);
        declare lv_cur cursor for select last_name, first_name from profile;
        declare continue handler for not found set lv_stop = 1 ;
        -- init my session variable ('sv_' prefix is for my sanity)
        set @sv_nsr_temp = 0;
        open lv_cur ;
        set  lv_stop = 0 ;
        set  lv_my_count = 0 ;
        while (lv_stop = 0) do
           fetch lv_cur into lv_last_name, lv_first_name;
           if (lv_stop = 0) then
              -- count the records sessioned
              set lv_my_count = lv_my_count + 1;
              select lv_last_name, lv_first_name, lv_my_count;
           end  if;
        end  while;
        close lv_cur;
        -- copy to session variable before exit (ugh)
        set @sv_nsr_temp = lv_my_count;
      delimiter ;
      -- okay, time to test the procedure
      call neil_sp_demo2;
    3. A little hacking with binary (I might use this in a trigger)
      -- title : NEIL_SP_DEMO_3.SQL
      -- target: mysql-5.5 / mariadb-5.5
      -- notes : need to breakout a decimal value into bits
      -- ==================================================
      use icsis;
      drop procedure if exists neil_sp_demo3;
      delimiter ||
      create procedure neil_sp_demo3(in lv_data INT)
        -- need some local variables ('lv_' prefix is for my sanity)
        declare lv_bits INT;
        declare lv_temp INT;
        set lv_bits = 31;
        while (lv_bits >= 0) do
           if (lv_data & pow(2, lv_bits)) <> 0 then
              select lv_bits as "bit", "was set" as "state";
              select lv_bits as "bit", "was clear" as "state";
           end if;
           set lv_bits = (lv_bits - 1);
        end while;
      delimiter ;
      -- okay, time to test the procedure
      call neil_sp_demo3(9);

  11. Diagnosing UTF-8 problems
  12. Good news from VSI (VMS Software, Inc).
  13. New problems with  MySQL Workbench  (a bug or corporate vandalism?)

MariaDB-5.5-25 Weird Shutdown Problem

This stuff used to be part of the section above titled  "MySQL + MariaDB Tips". I have no idea if this bug was specific to MariaDB-5.5-25 on OpenVMS but it was never seen on MariaDB-10.0-19 on CentOS-7. Anyway, I keep this stuff here for a while in case some other poor devil experiences the same madness.

  1. Diagnosing a weird shutdown problem:
  2. Successful shutdown-hack for MariaDB-5.5-25 on OpenVMS (2017-03-15)
    1. whenever I make a hot-backup of my OpenVMS system, lots of MariaDB files are open (obviously)
    2. I noticed that whenever I restore an OpenVMS hot-backup on one of my lab machines, that MariaDB always starts with zero problems (woo-hoo)
    3. I inspected the shutdown script for MariaDB-10 on CentOS-7 and noticed that kill-0 is used rather than "mysqladmin shutdown" and this inspired the following hack which has never failed (so far) with MariaDB-5.5-25 on OpenVMS
    Shutdown hack: (use with caution; but it has worked for me 10 out of 10 times)
    1) $mysqladmin --user=neil --password=passwd123 refresh	! flush a lot of stuff to disk
    2) $show system/proc=mariadb* ! to learn the PID (Process ID) 3) $stop /id=PID ! kill the MariaDB_Server

Using MySQL or MariaDB as a form of RMS++ 

While I believe that RMS will be around forever (it must, since it is the basis for SYSUAF on OpenVMS), I am also convinced that it has outlived its usefulness in many modern applications. With MySQL and MariaDB it is just too easy to "add, remove and expand columns on the fly".  I have shown above that you can dumb-down MySQL and MariaDB but it will still be smarter and more useful than RMS (at least without the help of ACMS and/or application software). Even a dumbed-down relational database engine can provide you with:

to only name three of many. Think of this as a RMS++ or super RMS or a poor man's Oracle-Rdb. Now I do not recommend you jump in with both feet. Set up MySQL or MariaDB then play with it for a month or two before loading it with stuff you wouldn't care if you lost (like operational reports).

Software for Linux (an OpenVMS reality check)

Experiences (up to 2016-11-xx)

  Recent Events (2016-11-xx)

  Recent Events (2016-12-xx)

  Recent Events (2017-02-xx)


Sometimes installing an SQL-compliant storage engine is overkill. If your code is written in C/C++ then consider SQLite which is a set of libraries you include into your C program to give it SQL capabilities.

Final Thoughts / Miscellaneous Links

Character Sets


Business over the web (an OpenVMS future without VT-100 terminals)


Bootstraps for your brain

C/C++ programmers on very small projects should consider SQLite

Back to OpenVMS
Back to Home
Neil Rieck
Kitchener - Waterloo - Cambridge, Ontario, Canada.