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.
Menu
Edit: 2024-04-20
Relational Database Caveat: (please read all items in this disclaimer before continuing)
  • Oracle-Rdb (OpenVMS only) or Oracle-DB (all platforms) represent the BMW and Mercedes Benz of the enterprise database industry. You will not find anything better.
  • However, most charities, students, and small businesses can only afford Volkswagens and this is where MySQL and MariaDB are popular.
  • If your organization has any kind of annual IS/IT budget then you should stick with Oracle-Rdb (OpenVMS only) or Oracle-DB (all platforms) and should pay for annual support.
  • If you do not have the skills to fix problems yourself, or cannot tolerate problems lasting days-to-weeks, then you should never depend upon open source software without some kind of active support contract. Why? Open source developers do not worry about things like: "who answers the call when Joe is on vacation?" or "Joe just perished in a traffic accident". People who sell support contracts do worry about such things. On top of this, I have seen boundary issues in open source software go unanswered for years. What "you may think is an important problem" might be ignored by the rest of the user community.
  • MySQL support contracts are available from other companies including Oracle
  • MariaDB support contracts are available from other companies including MariaDB Corporation Ab

MySQL

History (10k view)

A few technical details

MariaDB

History (10k view)

A few technical details

Software for OpenVMS

MySQL-4.1

MySQL-5.1

MariaDB-5.5

Executive Summary
  • this web-page contains many problems associated MariaDB-5.5 but that is only because it was the first relational database engine I placed into production on OpenVMS
  • we installed MariaDB-5.5-25 onto our production OpenVMS-8.4 AlphaServer in 2014 as an experiment to move beyond ISAM/RMS
  • as data was slowly migrated to MariaDB, the XtraDB engine (labeled InnoDB in MariaDB) began to experience ever longer graceful shutdown times
    • we noticed this when our database exceeded 5 GB in size
    • graceful shutdown time appeared to be proportional to run time (~7 minutes for every day of up-time)
    • self support blogs claimed these problems were fixed in MariaDB-10 which was not yet available on OpenVMS
      1. MariaDB version numbers once matched MySQL versions
      2. Due to a legal dispute between Oracle Corp and MariaDB AB, MariaDB-5 was followed by MariaDB-10
      3. Once Oracle saw this, MySQL-6 had a very short life then was followed by MySQL-8
  • we migrated our production software to an OpenVMS-8.4 Itanium platform in 2015
    • graceful shutdown times were faster but still way too long
  • we acquired a surplus HP ProLiant server (ML370) in 2016 then installed CentOS-7 just to test MariaDB-10.1
    • our database size is now 15.6 GB
    • our OpenVMS client software connected to the remote MariaDB server over a private network cable (the connect statement was modified to include the "-h" switch)
    • while testing a copy of our production OpenVMS database we noticed shutdown times are now almost instantaneous and we have never experienced shutdown-related corruption
  • the tests were so successful that we never went back. We now run a hybrid system (our primary business system is still based upon OpenVMS but our database is hosted by CentOS-7)
  • even though we now depend upon a MariaDB system running on Linux, we still require MariaDB libraries on our OpenVMS system in order to build client-side OpenVMS programs
  • From the rumor mill:
    • it is currently not possible to do a native OpenVMS port of MariaDB or MySQL after version 5.5 because C11 support is missing in both the C and C++ languages
      • The C/C++ for OpenVMS-8.4 on Alpha and Itanium might never be brought up to date (HP ignored the problem for way too long)
      • VSI is working on a solution for OpenVMS-9.x on x86-64

OpenVMS Life after 2023

As we third-party VMS supporters age-out of the industry, OpenVMS users would be wise to turn toward VSI (https://vmssoftware.com/) where you will find a lot of opensource software adapted for OpenVMS. That said, I still find the stuff from Mark Berryman very useful.
Source Product Scope URL Notes OpenSSL
VSI LibMariaDB v3 AXP/I64/X86 https://vmssoftware.com/products/libmariadb/ client only link to shared image
VSI LibMariaDB v2 AXP/I64/X86 https://vmssoftware.com/products/libmariadb/ client only [[[ not yet tested ]]]
VSI MariaDB-5.5-63 AXP/I64/X86 https://vmssoftware.com/products/mariadb/ client + server SSL_, SSL$, or shared image
Mark Berryman MariaDB-5.5 AXP/I64 https://theberrymans.com/php_kits/ client + server SSL_ or Berryman libs
Mark Berryman MaraDB-10 X86 https://theberrymans.com/php_kits/ client only SSL3$

Visit my OpenVMS source code area to learn how to compile and link (includes: Berryman MariaDB, VSI MariaDB, and LibMariaDB)

SSL Libraries on OpenVMS (common locations only)
Location Prefix Package Notes
SYS$Library
 SSL_
usually unofficial creations (eg. http://polarhome.com)

SSL$  <= OpenSSL-0.9
 official HP/HPE

SSL1$  >= OpenSSL-1.0
 official HP/HPE

SSL111$  OpenSSL-1.1.1
 official VSI

SSL3$  Newer FIPS stuff
 official VSI

MySQL + MariaDB Tips (all versions)

password caveat: in simpler days, database passwords were meant to be: alphabetic, numeric or alphanumeric. Passwords today are allowed to contain so-called strange characters. If your password contains any strange characters like a space or a dollar sign, then remember to quote the password when connecting from the mysql client

  1. Before changing the root password, first experiment with a cloned root account:
     
    • use the root account to connect to the master table (which is named mysql):
      legend:
<sr> = system response
<ur> = user response
------------------------------------------------------------------------------------------
<sr>	$						! my DCL prompt
<ur>	set term/width=132/nowrap			!
<sr>	$						!
<ur>	mysql -uroot					! connect as user "root" (no password)
							! alternate syntax: "--user=root"
<sr>	Welcome to the MariaDB monitor. Commands end with ; or \g.
	Your MariaDB connection id is 25
	Server version: 5.5.25-MariaDB-log Source distribution

	This software comes with ABSOLUTELY NO WARRANTY. This is free software,
	and you are welcome to modify and redistribute it under the GPL v2 license

	Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

	MariaDB [(none)]>				! my MariaDB prompt
<ur>	use mysql					! switch to the master database
<sr>	Database changed
	MariaDB [mysql]>				! new MariaDB prompt
<ur>	select host, user, password from user;		! inspect table: user
<sr>	+-------------------+------+----------+
	| host              | user | password |
	+-------------------+------+----------+
	| localhost         | root |          |
	| kawc09.on.bell.ca | root |          |
	| 127.0.0.1         | root |          |
	| ::1               | root |          |
	| localhost         |      |          |
	| kawc09.on.bell.ca |      |          |
	+-------------------+------+----------+
	6 rows in set (0.00 sec)

        MariaDB [mysql]>
    • create a new account then copy the privs from root:
      <ur>	create user neil@localhost identified by 'yada123';	! note: pass=yada123
<sr>	Query OK, 0 rows affected (0.00 sec)

	MariaDB [mysql]>					!
<ur>	grant ALL on *.* to neil@localhost with grant option;	! now apply some privs
<sr>	Query OK, 0 rows affected (0.00 sec)
	MariaDB [mysql]>					!
<ur>	select host,user,password from user;			! generate a query
<sr>	+-------------------+------+-------------------------------------------+
	| host              | user | password                                  |
	+-------------------+------+-------------------------------------------+
	| localhost         | root |                                           |
	| kawc09.on.bell.ca | root |                                           |
	| 127.0.0.1         | root |                                           |
	| ::1               | root |                                           |
	| localhost         |      |                                           |
	| kawc09.on.bell.ca |      |                                           |
	| localhost         | neil | *CE6FFA38DBB56C2B064706A7C4402CA7B7D0E9E8 |
	+-------------------+------+-------------------------------------------+
	7 rows in set (0.00 sec)

	MariaDB [mysql]>
<ur>	exit;
<sr>	Bye
	$						! my DCL prompt
------------------------------------------------------------------------------------
<ur>	mysql --user=neil --password=yada123		! this works (and it should)
------------------------------------------------------------------------------------
<ur>	mysql --user=neil --password=passwd124		! this fails (and it should)
------------------------------------------------------------------------------------
    • now change the password of the cloned account
      <sr>	$						! my prompt
<ur>	mysql --user=root mysql				! connect as "root" to database "mysql"
<sr>	MariaDB [mysql]>
<ur>	update user set password=password('1234') where user='neil';
<sr>	Query OK, 1 row affected (0.01 sec)
	Rows matched: 1 Changed: 1 Warnings: 0

	MariaDB [mysql]>
<ur>	exit;
<sr>	Bye
	$
-----------------------------------------------------------------------------------
<ur>	mysql --user=neil --password=1234		! fails (and it should not)
-----------------------------------------------------------------------------------
<ur>	mysql --user=neil --password=yada123		! works (and it should not)
-----------------------------------------------------------------------------------
    • make the password change take effect in the current server instance (shutdown-restart would do the same)
      <sr>	$
<ur>	mysql -uroot					! connect as user "root" (no password)
							! alternate syntax "--user=root"
<sr>	MariaDB [none]>
<ur>	flush privileges;
<sr>	Query OK, 0 rows affected (0.00 sec)

	MariaDB [none]>
<ur>	exit;
<sr>	Bye
	$
-------------------------------------------------------------------------------
<ur>	mysql --user=neil --password=1234		! works (and it should)
-------------------------------------------------------------------------------
    • Once you setup a priv account for remote access (see next tip: ODBC Access from a remote system) then consider doing the bulk of your work graphically via MySQL Workbench
    • From this point on, do not use the root account with MariaDB Monitor (but the priv account you do use should also be a real account on OpenVMS). Why? From time-to-time this program will write messages to a history file in the SYS$LOGIN folder of whatever account you were using. Since most OpenVMS systems do not have a root account, these messages will go to the universal bit bucket in the sky.
  2. Now change the root password (remember to issue 'flush privileges')
     
  3. ODBC Access from a remote system
    steps:
    • the ODBC port is 3306 but the server binds to 127.0.0.1 by default which means "only local connections". To enable remote connections you must:
      1. locate file:
            my.cnf
      2. disable line:
            bind-address = 127.0.0.1 (prepend with an octothorpe; also known as a hash mark or '#')
      3. enable line:
            #bind-address = 0.0.0.0  (remove the octothorpe)
      4. restart the database
    • in the examples shown above, I created an account called neil@localhost which cannot be used from a remote computer for obvious reasons. To allow access from all other machines you must first create an account with no host definition (eg. neil) or to only allow access from specific machines then you must create an account with specific host definitions (eg. neil@kawc0g.on.bell.ca). In the following example "%" means wildcard:
      <sr>	MariaDB [mysql]> 
<ur>	select host,user,password from user;
<sr>	+-------------------+----------+-------------------------------------------+
	| host              | user     | password                                  |
	+-------------------+----------+-------------------------------------------+
	| localhost         | root     |                                           |
	| kawc09.on.bell.ca | root     |                                           |
	| 127.0.0.1         | root     |                                           |
	| ::1               | root     |                                           |
	| localhost         |          |                                           |
	| kawc09.on.bell.ca |          |                                           |
	| localhost         | neil     | *5D8EF4B0222013574A34CEBDCB462CDC61C44754 |
	| kawc0g.on.bell.ca ! neil     | *5D8EF4B0222013574A34CEBDCB462CDC61C44754 |
	| %                 | neil     | *5D8EF4B0222013574A34CEBDCB462CDC61C44754 |
	+-------------------+----------+-------------------------------------------+
	9 rows in set (0.03 sec)
	
	MariaDB [mysql]>
    • download an appropriate ODBC connector for your client machine (most people will want to play with the Windows version first):
  4. A few MySQL commands for newbies or occasional users:
     To show the current date and time from internal functions:
    select curdate();	
    select curtime();
    select now();			

To display engine variables beginning with the letter 'v':
    show variables like 'v%';		-+- these two 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:
    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 locate a specifically named field as if it were data:
    select * from information_schema.columns where table_name='contract' and column_name like 'neil%';
    select * from information_schema.columns where table_name='contract' and column_name like '%neil%';
    select * from information_schema.columns where table_name='contract' and column_name like '%neil';  

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)

Comment about keys vs. indexes:
  1) some databases treat keys and indexes as different objects. For example, Oracle-RDB treats
	keys as the objects on which you hang constraints. When you create a key with this database
	engine you are not creating an index. That requires an additional command.
  2) both MySQL and MariaDB treat keys and indexes as synonyms. Constraints and collating sequences
	are just optional items associated with the index-key compound widget. This is reminiscent
	of older ISAM products like RMS from Digital Equipment Corporation and CICS (pronounced
	"kicks") from IBM
  3) in both MySQL and MariaDB be sure to include the index field in your select AND include an
	"order by" clause. For example in this demo "pin" is PRIMARY KEY indexed but the resulting
	displays will be totally different from each other
	  a) select first_name, last_name from profile order by pin;
	  b) select *                     from profile order by pin;
	  c) select *;
	Why is this important? When a primary key is defined in ISAM-RMS the natural retrieval is
	always done by primary key. With both MySQL and MariaDB the engine always takes the least
	energetic path. It requires more energy to "scan by index then jump around retrieving records"
	than "just beginning at the first record then reading until the last record"

To see the indexes associated with a user:
    show indexes from user;					(Host, User)

To see the indexes associated with a table (method #1):
    show indexes from icsis.status_tech_tracking;

To see the indexes associated with a table (method #2):

    select * from information_schema.statistics
	where table_schema = 'icsis'  and
	table_name = 'status_tech_tracking';

    select * from information_schema.statistics
	where table_schema = 'icsis'  and
	table_name = 'status_tech_tracking' \G

    describe information_schema.statistics;

    select  table_name, index_name, column_name
	from information_schema.statistics
	where table_schema = 'icsis' and
	table_name = 'status_tech_tracking';

    select  distinct
	table_name, index_name, column_name
	from information_schema.statistics
	where table_schema = 'icsis' and
	table_name = 'status_tech_tracking';
 
To see available storage engines:
    show engines;			(MEMORY, MRG_MYISAM, MyISAM, BLACKHOLE, CSV, Aria, 
					 ARCHIVE, FEDERATED, InnoDB, PERFORMANCE_SCHEMA, SPHINX) 

Duplicate a table so you can hack:
    use icsis;					(switch to database 'icsis')
    create table yada like profile;		(create table 'yada' using table 'profile' as a template)
    insert into yada select * from profile;	(copy all data from table profile to table yada)

     
  5. (some basic) Maintenance Commands:
    1) Stopping a "runaway" 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.
	
   Caveat: 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).
   When MariaDB detects this situation (comparing apples to oranges), it ignores the offending index then attempts to process
   the transaction using raw uncollated (unindexed) data. If the table is large then it might appear to take forever. (oops!)
 
   <sr> $
   <ur> mysql --user=neil --password=yada123
   <sr> MariaDB [(none)]>
   <ur> show processlist;
   <sr> 
   +---------+-------+----------------------+----------+---------+---------+--------------------------------+---------------+
   | Id      | User  | Host                 | db       | Command | Time    | State                          | Info          |
   +---------+-------+----------------------+----------+---------+---------+--------------------------------+---------------+
   | 1816400 | dave  | d40che.bell.ca:54664 | onuadmin | Sleep   |     276 |                                | NULL          |
   | 1816401 | dave  | d40che.bell.ca:54665 | icsis    | Sleep   |     276 |                                | NULL          |
   | 1846611 | dave  | d40che.bell.ca:54753 | 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 | d6hcjd.bell.ca:52609 | onuadmin | Query   |     594 | Waiting for table metadata lock| SHOW COLUMNS F|
   | 2021960 | vince | d6hcjd.bell.ca:52610 | onuadmin | Sleep   |     323 |                                | NULL          |
   | 2021962 | neil  | localhost:4715       | NULL     | Query   |       0 | NULL                           | show processli|
   | 2022035 | vince | d6hcjd.bell.ca:52701 | NULL     | Sleep   |     255 |                                | NULL          |
   | 2022036 | vince | d6hcjd.bell.ca:52702 | NULL     | Sleep   |       2 |                                | NULL          |
   +---------+-------+----------------------+----------+---------+---------+--------------------------------+---------------+
   MariaDB [(none)]>
   <ur> kill 2021829;
	
   comment: "kill" is a shell command seen in UNIX or Linux. At this point you should be thinking:
	 MySQL/MariaDB is a lot like having an OS within an OS

2) Light Maintenance (no locking)

   $ mysql --user=neil --password=yada123

 	steps:	create new indexes;
		drop old indexes;
		execution plans will shift to new indexes;

3) Medium Maintenance (minimal locking)

	$ mysql --user=neil --password=yada123
	show databases;
	use database whatever;
	analyze table yada;
	optimize table yada;

4) Heavy Maintenance (locking)

	mysqlcheck --help						# view the plethora of options
									# including: -q and -e
	mysqlcheck --user=neil --pass=yada123 -c --all-databases	# check all tables in all databases
	mysqlcheck --user=neil --pass=yada123 -C --all-databases	# check changed tables in all databases
	mysqlcheck --user=neil --pass=yada123 -o --all-databases	# optimize all databases

5) To shut down MySQL-5.1/and MariaDB-5.5-25( add these to script sys$manager:SYSHUTDWN.COM )

   $ mysqladmin --user=neil --pass=yada123 ping		! are you there?
   $ mysqladmin --user=neil --pass=yada123 ver		! display stats
   $ mysqladmin --user=neil --pass=yada123 refresh	! flushes a lot of stuff to disk
   $ mysqladmin --user=neil --pass=yada123 status	! display stats one-liner
   $ mysqladmin --user=neil --pass=yada123 shutdown	! should not see any error messages here
   $ wait 0:0:05					! this 5-second delay is necessary
   $ mysqladmin --user=neil --pass=yada123 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=yada123 --port=8080 --fast --all-databases		# check tables not properly closed
   $ mysqlcheck --user=neil --pass=yada123 --port=8080 -c --quick --all-databases	# quick check all tables in all db 
   $ mysqlcheck --user=neil --pass=yada123 --port=8080 -C --quick --all-databases	# quick check changed tables  
   $ mysqlcheck --user=neil --pass=yada123 --port=8080 -c --all-databases		# normal check
   $ mysqlcheck --user=neil --pass=yada123 --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=yada123 --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=yada123 --port=8080 --result-file=icsis.sql --databases icsis

9) Database restore

   $ mysqladmin --user=neil --pass=yada123 --port=8080 --force drop material		# drop corrupt database
   $ mysqladmin --user=neil --pass=yada123 --port=8080 create database icsis		# create new database
   $ mysql      --user=neil --pass=yada123 --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=yada123
   <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
    Caveats:
    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:
                MYSQL055_ROOT:[logs]mariadb-bin.*
        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
	$ @start_mysqld.com
	$!
	$! 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++
    • moved to my OpenVMS source code area
      • demos how to do MySQL operations from within DEC-C or DEC-C++
      • demos how to call the MySQL client from within DEC-BASIC
      • demos how to build lib_mysqludf_sys (a User Defined Function which allows you to execute DCL commands from within Stored Procedures)
  9. my demo apps written in DEC-BASIC
    • moved to my OpenVMS source code area
    • our current system consists of 70+ BASIC programs reading/writing 150+ RMS indexed files. In order for us to do a parallel (RMS=production, MySQL=daily snapshot) operation over the next 12 months, we needed a semi-autonomous method of tool generation. These three BASIC programs (see: mysql_import_helper.bas) can read record definition declarations in our BASIC source code then generate:
      • 99% complete BASIC programs to extract data from RMS files then write it to CSV files (usually tab delimited)
        • you need to edit one line before compiling
        • once built, there is no need to do this again unless the RMS file format changes which means these can be called from batch (and I am doing so now)
        • the open/read ignores your basic indexes/keys
        • this tool is preferable to opening up your source code to insert customized export routines.
      • 99% complete SQL scripts to create the tables then import the data from CSV files
        • since this code does not (yet) analyze the OPEN statements, you will need to insert SQL statements to create: constraints, indexes and keys, etc. you could modify the table creation statements before the import but this would cause the import to be hundreds of times slower (lots of recursion while it maintains each index AND you could still end up with a lop sided index if the inserted data was already ordered). All large imports should always be indexless and constraintless; you tack them on as a finishing step.
      • This may sound crazy, but in 2019 I wrote some code so that whenever my remaining two BASIC programs write to ISAM/RMS, the BASIC programs also call my MySQL client routines (written in DEC-C) to update the MariaDB database on a secondary system. It appears that we have 100% synchronization and since all updates happen well under a second, the remote system is now being used by management for all ad-hoc reports (written in Python)
  10. Stored Procedures (a few examples)
    comment: although these examples were written using EDT on OpenVMS then pushed into MariaDB by the source command, I have found that writing/testing/debugging is more productive using SQL Workbench 8 which is only available in a 64-bit edition (at the time of this writing)
     
    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()
begin
  -- 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;
end||

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)
begin
  -- 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";
     else
        select lv_bits as "bit", "was clear" as "state";
     end if;
     set lv_bits = (lv_bits - 1);
  end while;
end||

delimiter ;

-- okay, time to test the procedure
call neil_sp_demo3(9);

       
  11. Diagnosing UTF-8 problems
    • Overview: we proudly support both official Canadian languages: French and English. You will find lots of bad advice from riffraff lurking around self-help sites (like StackOverflow) advising that all UTF-8 problems can be fixed by switching your database to latin1. Do not do this. If your business is required to support any European languages then you must set your database to utf8 (Americans would be wise to add support for Spanish if they haven't done so already). If your business is required to support all human languages (perhaps you want to store emails or invoices from your partner company in China) then you must set your database to utf8mb4. Note that world-wide systems like Twitter, Facebook, Google, and Wikipedia are already set to mtf8mb4
    • Many database administrators wisely place the file system into binary mode then declare textual data as being utf8 (or utf8mb4)
      • as implemented in either MySQL or MariaDB, the utf8 declaration defaults to the 3-byte variety; you need to declare utf8mb4 to get access to the full UTF-8 spectrum
      • if you want to store any transactional information (including emails) from your business partners in China then you must use utf8mb4
    • We just discovered an anomaly where some database accounts return data as utf8 while others return latin1. To see why this is weird, inspect the following supporting information:
      • Global variables describe how various parts of the server will behave (eg. new databases and tables will default to character set "xxx" whenever you issue a command without including a character set)
      • Unless overridden by settings files or a client side switch, a client's Session variables will almost always will be a copy from the Global Variables.
        So if the database in question is employing utf8 and the client wished to work in latin1 (a.k.a. Windows-1252 which is a superset of ISO-8859-1) all the client needs do is change one, or more, session variables. Yep, unlike webservers, the database can convert between character sets on-the-fly.
      • The following example comes from MaraDB-10.1.19 but works the same way in MariaDB-5.5-25
        Caveat:
        • be careful when connecting to remote servers because your local client will pickup settings from local files like these:
          • system-wide: my.cnf
          • personal: my.cnf
          Note: client software will only pay attention to settings under the "[client]" stanza (a.k.a. group)
           
        • first use this client string to see how you would connect by default after reading local settings files

          mysql --print-defaults

        • now use this client string to see how you will connect without reading local settings files (the "--no-defaults" switch must be first):

          mysql --no-defaults --help --verbose

          ... then notice that "character_set = auto"

        now use the "--no-defaults" switch to connect to the remote database like so:
        $ mysql --no-defaults -udilbert -psecret -hkawc4m.on.bell.ca

{ stuff chopped out }

MariaDB [icsis]> show global variables where Variable_name like '%char%' \g  

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [icsis]> show session variables where Variable_name like '%char%' \g

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

8 rows in set (0.00 sec)

MariaDB [icsis]>
      • Since I did not specify an alternate character set, why are my session variables different than global variables?
        • I do not know what follows is a bug or a feature but it appears that any database accounts with SUPER priv get a different set of SESSION variables.
        • This means that you must always provide a character-set switch when connecting by MySQL Client
        • Likewise, you must do the same when using programs via the C-API
      • I have discovered that using the client switch "--default-character-set=whatever" does not always work (or doesn't not work reliably) when you connect. You need to change the character set after you connect (easy to do with the C-API). Scripts should be setup to employ the client switch "--init-command=" (this needs to be tested; I will update this webpage within a few days)
         
  12. New problems with  MySQL Workbench
    • I have been using MySQL Workbench 6.1 (32-bit Windows edition) since 2014 to connect to MaraDB-5.5-25
    • When you use this tool to connect to MariaDB-10 you will get a warning telling you that the tool might not work with the higher versioned database
    • Apparently early versions of MySQL Workbench 6.2 also work with MariaDB-10 with a warning
    • I recently installed MySQL Workbench 6.3.9 (64-bit Windows edition) which displays a warning then crashes.
    • If the windows application log contains any .NET messages then you need to reinstall the Visual-C++ redistributable
    • update-1: you really want to be using Workbench 8.0 (64-bit Windows edition) at the very minimum because the "schemas browser" (see picture to the right) displays a nifty 3-icon tool pallet when hovering over a table entry:
      1. INFO (looks like a stylized "I") provides table information
      2. MTCE (looks like a wrench) can be used to quickly modify table attributes as well as column attributes
      3. DATA (looks like a spread sheet) can be used to
    • update-2: My employer just (2021.06.xx) forced me to migrate to a new laptop running Windows-10 (64-bit).
      • PROBLEM: the new version of MySQL Workbench to will not connect (connection config-then-test works but the app crashes when you attempt to connect
      • SOLUTION: Even through I am using a 64-app with a 64-bit OS, I needed to install two versions of the Visual-C++ 2019 redistributable (needed both "x64" and "x86")
  13. We just ran out of available connections (yikes!)
    • our client routines (all written using the MySQL C API) log all MySQL/MariaDB errors and warnings in an external file meant for proactive maintenance.
    • I occasionally see error numbers (like -6) without any error text. Apparently this can happen whenever the client software is unable to connect to the server.
    • So check out the following displays
      MariaDB [(none)]> show status like '%max%';
+-----------------------------------+-----------+
| Variable_name                     | Value     |
+-----------------------------------+-----------+
| Connection_errors_max_connections | 2         |
| Innodb_checkpoint_max_age         | 80826164  |
| Innodb_max_trx_id                 | 164571514 |
| Innodb_row_lock_time_max          | 51266     |
| Max_statement_time_exceeded       | 0         |
| Max_used_connections              | 152       |
| Tc_log_max_pages_used             | 0         |
+-----------------------------------+-----------+
7 rows in set (0.00 sec)
 
MariaDB [(none)]> show variables like 'max_con%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)
 
MariaDB [(none)]>
    • Max_connections limits the number of connections to 151
    • Max_used_connections is a high-water mark and shows 152 (so we have already hit the upper limit)
      • one slot is reserved for emergency use by root which is why we see 152 rather than 151
    •  Connection_errors_max_connections show that 2 transactions failed due to Max_connections being set too low.
    • This display shows how to immediately increase the number of connections in the running instance.
      MariaDB [(none)]> SET GLOBAL max_connections = 512;
MariaDB [(none)]>
    • The change just shown will not survive a restart. To make the change permanent, you must add the line  "SET max_connections = 512" to the settings file (under the [mysqld] stanza header) which may have one of the following names depending upon your server version:
      • "/etc/my.cnf"
      • "/etc/my.cnf.d/server.cnf "
      note: some systems use the ".ini" extension.

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:
    • Long Shutdown Problem (2015-08-xx)
      • I'm running into shutdown-restart problems with MariaDB-5.5-25 on OpenVMS
        • I am not sure if this pertains to MariaDB on other platforms
        • my total file sizes under MYSQL055_ROOT:[000000]  is ~ 5.1 GB
      • The server will not always restart after a shutdown; or more precisely, it will restart then detects a problem, writes some data to the log file, then crashes
        • file location: MYSQL055_ROOT:[000000.mysql_server]
        • file name: MYSQLD.LOG
        • usually contains something like this:
          150825 13:05:19 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
150825 13:05:19  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 14202570, file name /mysql055_root/log/mariadb-bin.000011
150825 13:05:25  InnoDB: Waiting for the background threads to start
%SYSTEM-F-STKOVF, stack overflow, PC=0000000000DA09E0, PS=0000001B
%TRACE-F-TRACEBACK, symbolic stack dump follows
  image    module    routine             line      rel PC           abs PC      
 MYSQLD  rem0rec  rec_get_offsets_func  95152 0000000000000CE0 0000000000DA09E0
 MYSQLD  page0cur  page_cur_search_with_match
                                       105075 0000000000001414 0000000000D82D34
      • This could be be caused by:
        • Some problem peculiar to this release of MariaDB before the port to OpenVMS
        • Some problem peculiar to this port of MariaDB to OpenVMS
          • The poor state of the current CRTL under OpenVMS-8.4 which was ignored by HP for over a decade (MariaDB is written in C/C++ so would be highly dependent upon the CRTL built into OpenVMS-8.4)
        • OS file caching (called XFC on OpenVMS)
          • write-through is the default; can be modified on a file-by-file basis
          • write-through is mandatory with SANs (storage area networks)
        • RAID caching policy
          • (delayed) write-back provides the greatest amount of system speed which is why hardware installers usually enable it. But this has been known to corrupt databases
          • write-through is preferred (over write-back) if the RAID has no battery backup
          • write-through is preferred (over write-back) if file caching is also done in the OS where the admin has more control
        • RAID hardware problem (bad disk or bad memory)
      • failure mode analysis demands the following actions:
        • simplify the environment by eliminating any extraneous factors
        • break down the remaining problem into functional blocks then test/eliminate them individually
        • I've got to start somewhere so will begin by disabling XFC caching on some/all directories and files associated with MariaDB
          1) (consider) excluding some files from XFC (the OpenVMS extended file cache)
	$ SET DEF MYSQL055_ROOT:[000000]
	$ set file /CACHING_ATTRIBUTE=no_cach data.dir/log
	$ set file /CACHING_ATTRIBUTE=no_cach [data...]*.*/log
	$ set file /CACHING_ATTRIBUTE=no_cach log.dir/log
	$ set file /CACHING_ATTRIBUTE=no_cach [log...]*.*/log
    reference: http://h30266.www3.hp.com/odl/axpos/opsys/vmsos84/aa_pv5nj_tk/aa_pv5nj_tk.HTML
    quote: Disabling Caching for a File
	   To prevent XFC to from caching a particular file, such as a database file,
	   set the caching attribute of the file to no caching. 

2) (consider) excluding all files from XFC (the OpenVMS extended file cache)
	$ SET DEF MYSQL055_ROOT:[000000]
	$ set file /CACHING_ATTRIBUTE=no_cach *.*/log
	$ set file /CACHING_ATTRIBUTE=no_cach [...]*.*/log
    • More information (2015-08-29)
      • I have MariaDB-5.5-25 running on four non-production OpenVMS machines (three Alpha Servers; one Itanium2)
        • thank the deity that we never shipped these Alpha junkers to our hardware reseller
      • it appears that shutting down any database which employs only MyISAM and CSV tables can take up to 2-minutes
        • the shutdown command is executed twice between a 5-second delay
        • wait up to 120 seconds for the process to exit OpenVMS
        • a restart is always successful
      • shutting down any database which also employs InnoDB (XtraDB) tables can take much longer (minutes to hours)
        • the shutdown command is executed twice between a 5-second delay
        • now ~7 minutes is required for every day of up-time but if you wait for a natural exit then a restart is always successful
        • prematurely killing Maria at the 5 minute mark (because the process appears quiescent for more than a minute) always corrupts InnoDB tables
        • Okay so MariaDB has been running for 189 days on my production system and we need to take it down to install OS patches. It seems ridiculous to think we will require ~ 22 hours for a clean shutdown
          • update (2015-09-07): I tried a shutting down MariaDB on this node during a long weekend (after first reducing tx_isolation); bad news: the server never exited after ~36 hours so I killed it; good news: it did a crash recovery during startup without a stack overflow (this is probably the way it was designed to work)
    • Caveat: the following solution sloweded (but did not fix) my problem
      so I deleted this stuff
     
  2. Successful shutdown-hack for MariaDB-5.5-25 on OpenVMS (2017-03-15)
    observations:
    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; this was totally unexpected)
    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 (I'll come back here and update this page if it ever does fail BUT it is still working perfectly as of 2020-04-30)
       
    Shutdown hack: (use with caution; but it has worked for me 10 out of 10 times) 
    1) $mysqladmin --user=root --password=yada123 flush-tables 
2) $mysqladmin --user=root --password=yada123 flush-logs 
3) $mysqladmin --user=root --password=yada123 refresh   ! flush a lot of stuff to disk
4) $show system/proc=mariadb*                           ! find MariaDB's PID (Process ID)
5) $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)

SQLite

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

Acronyms

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

Miscellaneous

Bootstraps for your brain

C/C++ programmers writing small applications should consider SQLite

Back to Home
Neil Rieck
Waterloo, Ontario, Canada.  /div>