The VMS SharkOpenVMS 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: 2017-03-24 (spelling and grammar)
Relational Database Caveat: (please read all items in this disclaimer before continuing)

MySQL

History

A few technical details

MariaDB

History

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 database engine I placed into production on OpenVMS. Make no mistake: NEWER means BETTER and FASTER

MySQL-4.1

MySQL-5.1

MariaDB-5.5-25 (similar to MySQL-5.5)

Executive Summary

MySQL/MariaDB Tips

  1. Before changing the root password, save time by first experimenting with a cloned root account:
     
  2. ODBC Access from a remote system
    steps:
  3. 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				'\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, 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:
    	SELECT DISTINCT
    	TABLE_NAME,
    	INDEX_NAME
    	FROM INFORMATION_SCHEMA.STATISTICS
    	WHERE TABLE_SCHEMA = 'onuadmin'; 
     
    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 empty table 'yada' using table 'profile' as a template)
    	insert into yada select * from profile;			(copy data from profile to yada)
    

  4. (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;
    	<sr> 
    	+---------+-------+-------------------------------+----------+---------+---------+---------------------------------+---------------+
    	| Id      | User  | Host                          | db       | Command | Time    | State                           | Info          |
    	+---------+-------+-------------------------------+----------+---------+---------+---------------------------------+---------------+
    	| 1816400 | dave  | d40che.bell.corp.bce.ca:54664 | onuadmin | Sleep   |     276 |                                 | NULL          |
    	| 1816401 | dave  | d40che.bell.corp.bce.ca:54665 | icsis    | Sleep   |     276 |                                 | NULL          |
    	| 1846611 | dave  | d40che.bell.corp.bce.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.corp.bce.ca:52609 | onuadmin | Query   |     594 | Waiting for table metadata lock | SHOW COLUMNS F|
    	| 2021960 | vince | d6hcjd.bell.corp.bce.ca:52610 | onuadmin | Sleep   |     323 |                                 | NULL          |
    	| 2021962 | neil  | localhost:4715                | NULL     | Query   |       0 | NULL                            | show processli|
    	| 2022035 | vince | d6hcjd.bell.corp.bce.ca:52701 | NULL     | Sleep   |     255 |                                 | NULL          |
    	| 2022036 | vince | d6hcjd.bell.corp.bce.ca:52702 | 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; never kill it;
    	$! see InnoDB caveats further down this 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)]>

  5. (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)
      1. this is also known as read-only mode (recovery modes higher than 3 are dangerous)
      2. your clients will be able to read but not insert or update
      3. you will be able to create fresh database dumps to text files
      4. 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 --- this 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..6; 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

  6. Diagnosing a weird Problem:
  7. Shutdown Details (just collecting information)
  8. Successful shutdown-hack for MariaDB-5.5-25 OpenVMS (2017-03-15)
    facts:
    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 starts up with zero problems
    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)
       
    Shutdown hack: (use with caution; worked for me every time I used it)
    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

  9. my demo apps written in "C/C++"
  10. my data export tools written in BASIC
  11. 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()
      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);

Using MySQL - 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 with both feet. Set up MySQL or MariaDB then play with it for a month before loading it with stuff you wouldn't care if you lost (like operational reports).

Software for Linux (an OpenVMS reality check)

my experiences as of 2016-11-21

  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++ (or you don't mind using a C-wrapper) 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 on very small projects should consider SQLite


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