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.
Edit: 2020-06-29
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, universities and small businesses can only afford Volkswagens and this is where MySQL and MariaDB are found.
  • 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.
  • comment: MySQL support contracts are available from other companies including Oracle. Likewise, MariaDB support contracts are available from other companies including MariaDB Corporation Ab

MySQL

History (10k view)

  • MySQL is a fully SQL-compliant "open source" relational database created by MySQL Ab of Sweden
    • both the database, as well as the company, were named after "My", the older daughter of co-founder Michael "Monty" Widenius
    • open-source means it could be acquired, installed and used, free-of-charge under the GPL license
    • this company only made money by selling support
  • The first version appeared in 1995 for the PC
  • Sun Microsystems paid one billion dollars to acquire MySQL Ab in 2008 (not sure of the business model here since SUN intended to continue giving it away; perhaps they were thinking about paid support contracts)
  • Oracle acquired SUN Microsystems, along with MySQL, in 2010 (the acquisition began in 2009)
  • MySQL has diverted much attention (and perhaps a small amount of business) away from Oracle's flagship products so it should come as no surprise that Oracle has been accused of delaying "bug fixes" and "future product development". Consider these observations:
    • InnoDB was a popular "ACID compliant" storage engine used by the MySQL community since 2001. It was created by Innobase Oy of Helsinki Finland which was purchased by Oracle in 2005
    • BerkeleyDB (BDB) was a popular "ACID compliant" storage engine when created in 1986 for use with BSD UNIX. It was the default storage engine before MySQL-5.1 and was maintained by Sleepycat Software which was purchased by Oracle in 2006
    • After finalizing the purchase of Sun Microsystems in 2010, Oracle inserted a copyright notice along with a rights notice into the client banner. The following example hails from MySQL-5.1
      $ mysql -uroot mysql
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 69
      Server version: 5.1.46-log Source distribution
      
      Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
      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.
      
      mysql>

      Nothing wrong with a copyright notice but it does make lawyers and developers nervous.

A few technical details

  • high level languages can directly access MySQL via client APIs
    • most MySQL distros publish two symbol reference files:
      • mixed-case symbols for fourth generation languages (C/C++, Java, PHP, etc.)
      • upper-case symbols for third generation languages (BASIC, FORTRAN) but I have never tried this. Our VMS-BASIC programs call a C function which calls the C API
  • indirect access is also possible via ODBC, JDBC, etc.
  • numerous storage engines exist. Look at this example statement:
    CREATE DATABASE hack1;
    USE hack1;
    CREATE TABLE hack1 ( x1 INT(11) auto_increment, y2 char(60) default NULL, PRIMARY KEY (x1) ) ENGINE=kind ;

    where kind can be any one of the following:
    • ISAM (deprecated in MySQL 4.1, will be removed in MySQL 5.0)
    • MyISAM
    • MERGE
    • MEMORY
    • InnoDB
    • BDB (BerkeleyDB)
  • More storage engines have been added since MySQL 5.0
  • In this list, only InnoDB and BerkeleyDB are transaction-safe, ACID compliant, and replace "table locks" with "row locks"
  • Using either InnoDB or BerkeleyDB will cost you a very slight drop in performance but you'll get guaranteed rollbacks if an error occurs after a COMMIT
  • Links:

MariaDB

History (10k view)

  • MariaDB is a fully SQL-compliant "open source" relational database created by MariaDB Corporation Ab of Sweden.
    • both were named after "Maria", the younger daughter of co-founder Michael "Monty" Widenius
    • open-source means it could be acquired, installed and used, free-of-charge under the GPL license
    • this company only made money by selling support
  • Shortly after the purchase of MySQL Ab by SUN Microsystems in 2008, Widenius thought SUN was taking MySQL development in the wrong direction so started his own company called Monty Program Ab. This company forked MySQL into MariaDB to keep it free. Monty Program Ab merged with SkySQL into MariaDB Corporation Ab
  • Acquisitions by Oracle (see above) along with other activities in 2011-2012 (see next link) made the Linux community nervous which is why MariaDB is now the preferred relational database on most Linux distros (although you can replace it with MySQL if you desire)

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

MySQL-4.1

MySQL-5.1

  • all ports by Jean-François Piéronne
  • downloads (courtesy of VSM Software Services Pty Ltd of Austrailia):
  • documentation and help:
  • installation overview:
    • install zlib (MySQL will not work without it) then add the following line to script sys$manager:systartup_vms.com
      • @sys$common:[libz]startup.com
    • start your installation by one of the following stubs (depends if you installed it in the default location in sys$common or elsewhere):
        DCL Command Notes
      1 $ PRODUCT INSTALL MYSQL051 is installed to sys$common:[mysql051]
        $ SET DEFAULT sys$common:[MYSQL051.VMS]
      $ @LOGICALS "/SYSTEM/EXEC"
      $ @SYMBOLS
       
      2 $PRODUCT INSTALL MYSQL051 /DESTINATION=disk$user1:[000000] is installed to disk$user1:[mysql051]
        $ SET DEFAULT disk$user1:[MYSQL051.VMS]
      $ @LOGICALS "/SYSTEM/EXEC"
      $ @SYMBOLS
       
    • then
        DCL Command Notes
      3 $ copy my.cnf_template my.cnf/prot=w:re create config from template; protection bits will be tested
      4 $ edit my.cnf  
      5 $ copy [.mysql]run_mysqld.com_template [.mysql]run_mysqld.com/prot=w:re create script from template; protection bits will be tested
      6 $ edit [.mysql]run_mysqld.com  
    • then
        DCL Command Notes
      7 $ @[.MYSQL]FIRST_START_MYSQLD [Batch_Queue_Name] P1 is optional; this script will require 60-120 seconds maximum
      8 $ type/page mysql051_root:[mysql_server]mysqld.log  
      9 $ @[.MYSQL]START_MYSQLD [Batch_Queue_Name] P1 is optional
  • On 2014.01.22 Jean-François Piéronne told the world that he has no plans to release a version of MySQL for OpenVMS higher than 5.1 because HP has stopped supporting MySQL ports.
    • First off, who knew that HP was supporting development of MySQL on OpenVMS?
    • Could HP's decision have anything to do with the HP-Oracle Lawsuit of 2012-2013 which was triggered by Oracle's decision to no longer support Oracle products on Itanium? (perhaps)

MariaDB-5.5

Executive Summary
  • we installed MariaDB-5.5-25 onto our production OpenVMS-8.4 AlphaServer platform in 2014 as an experiment to move beyond ISAM/RMS (which we had been using to store data since 1987 while on VAX)
  • as data was slowly migrated to MariaDB, the XtraDB engine (labeled InnoDB in MariaDB) began to experience ever longer shutdown times (here I am referring to graceful shutdowns)
    • 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
      notes:
      1. MariaDB version numbers were once made to match MySQL versions
      2. Due to a legal dispute between Oracle Corp and MariaDB AB, MariaDB-5 was followed by MariaDB-10
      3. While Oracle originally planned to follow MySQL-5 with MySQL-6, the following version was called MySQL-8
        https://en.wikipedia.org/wiki/MySQL#Release_history
  • we migrated our production software to an OpenVMS-8.4 Itanium platform in 2015
    • shutdown times were faster but still way too long
  • we acquired a surplus HP ProLiant server in 2016 then installed CentOS-7 just to test MariaDB-10.1
    • 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)
    • our database size is now 15.6 GB
    • 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 on 2017-02-28 we incorporated a ProLiant-CentOS-MariaDB platform into our complete business solution (our primary business system is still based upon OpenVMS)
  • 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. So anyone out there hoping to keep OpenVMS alive should work on doing an OpenVMS port of MariaDB-10 as well as MySQL-8
  • From the rumor mill:
    • As others companies have done before them, VSI has renamed all their OpenVMS products with names like VSI-OpenVMS, VSI-BASIC, VSI-C, etc.
    • VSI offers supported ports of MySQL and MariaDB on VSI-OpenVMS
      • you will need to migrate away from HP/HPE versions of OpenVMS; doing so will require purchasing new OpenVMS licenses from VSI
    • For the time being (2019) it is not possible to do an OpenVMS port of MariaDB or MySQL after version 5.5 because of the lack of C11 support in VMS-C and VSI-C
      • C11 support can be added to gcc on Linux as is shown here
  • downloads published by Mark Berryman
  • caveats:
    • configuration data is found in file [.vms]my.cnf (used by both server and client binaries)
      • BIND ADDRESS
        • (all versions) by default, bind-address is set to "127.0.0.1" (do not change this to "0.0.0.0" until you have set a password for user "root"; see commands in the next section)
      • FEEDBACK
        • (version 5.5-25) by default, feedback is set to "ON". You can save resources by setting this to "OFF".
          • This change is optional for all versions of TCPware and MultiNet
          • This change is mandatory for v5.7 of TCPIP Services for OpenVMS where the listener crashes 5-minutes after the service starts (the server continues to run and is visible when you type "$show system" but you will not be able to connect using either mysqladmin or mysql client). This problem is caused by a bug in the optional FEEDBACK module (comment: I doubt the feedback module would ever work if you are sitting behind a firewall)
        • (versions 57+58) this module was not compiled into these releases
        • (version 59) by default, feedback is set to "ON". You can save resources by setting this to "OFF".
    • writing third party apps written in C/C++ by you
    • linking third-party apps written in C/C++ by you
      • Required libraries:
        • (version 5.5-25)
        • (versions 57 + 58 + 59)
          • does not require zlib; Mark has provided another library
          • does not require yassl (yet-another-ssl); Mark has provided two OpenSSL libraries -or- you can link against VMS-resident OpenSSL
    • "Readme.VMS" for MariaDB-5-5.57 (dated 2017-10-20):
      This kit contains a port of MariaDB v5.5.57 for VMS systems running on either
      Alpha or Itanium hardware.  MariaDB v5.5 is a combination of MariaDB v5.3 and
      MySQL v5.5.  As such, the terms MariaDB and MySQL tend to be used
      interchangeably within the product.
      
      The layout of the directory tree is slightly different than previous
      distributions of MySQL on VMS.  Among other things, a single kit contains all
      of the files needed for either platform.  The top-level directories (the
      siblings of this file) are as follows:
      
      [.bin]			Executable images, in [.bin.alpha] and [.bin.ia64]
      [.data]			The database files live here
      [.include]		Multiple directories, as built on Unix.  Needed
      			when writing code to call MySQL functions.
      [.lib]			Object libraries, in [.lib.alpha] and [.lib.ia64]
      [.log]			The database binary log files can live here if desired
      [.mysql_server]		Files containing SYS$OUTPUT and SYS$ERROR of mysqld
      [.scripts]		Files needed during installation to create or upgrade
      			the database.
      [.share]		Charset files used by the server.
      [.support-files]	Sample CNF files plus some Unix stuff.
      [.tmp]			Temporary directory used by the server.
      			tmpdir=/mysql055_root/tmp should appear in your my.cnf
      			file
      [.vms]			VMS startup files.  My.cnf lives here as well.
      
      The programs in this kit use the logical name MYSQL055_ROOT, not the logical
      name MYSQL051_ROOT used by previous distributions.  The logical name must be
      a concealed logical pointing to the parent of the above directories such that
      MYSQL055_ROOT:[bin], MYSQL055_ROOT:[data], etc. will work.  This logical name
      will be defined for you as part of the installation process, as well as the
      normal startup process, so you don't need to worry about adding it to any of
      your system startup files.
      
      Installation needs to be done either from the account that will be used to run
      MariaDB (MySQL) or from a privileged account with at least SYSPRV and CMKRNL.
      
      The START_MYSQLD.COM procedure in [.VMS] assumes that you have a username on
      on your system called MYSQL051_SRV that will be used to run MariaDB.  That is
      the only place that makes that assumption.  If you wish to use a different
      username, simply edit that command file to reflect the different username and
      modify step 1 of the installation process accordingly.
      
      For reference, here are the settings I have in my MYSQL051_SRV account:
      Username: MYSQL051_SRV                     Owner:
      Account:  MYSQL051                         UIC:    [37775,1] ([MYSQL,MYSQL051_SRV])
      CLI:      DCL                              Tables: DCLTABLES
      Default:  MYSQL055_ROOT:[MYSQL_SERVER]
      LGICMD:   MYSQL055_ROOT:[VMS]LOGIN.COM
      Flags:  DisCtlY DefCLI LockPwd Restricted DisWelcome DisNewMail DisMail
                    DisReport DisReconnect
      Primary days:   Mon Tue Wed Thu Fri
      Secondary days:                     Sat Sun
      Primary   000000000011111111112222  Secondary 000000000011111111112222
      Day Hours 012345678901234567890123  Day Hours 012345678901234567890123
      Network:  -----  No access  ------            -----  No access  ------
      Batch:    ##### Full access ######            ##### Full access ######
      Local:    -----  No access  ------            -----  No access  ------
      Dialup:   -----  No access  ------            -----  No access  ------
      Remote:   -----  No access  ------            -----  No access  ------
      Expiration:            (none)    Pwdminimum:  6   Login Fails:     0
      Pwdlifetime:           (none)    Pwdchange:      (pre-expired)
      Last Login:            (none) (interactive), 16-OCT-2017 16:52 (non-interactive)
      Maxjobs:         0  Fillm:      1000  Bytlm:       200000
      Maxacctjobs:     0  Shrfillm:      0  Pbytlm:           0
      Maxdetach:       0  BIOlm:      1000  JTquota:       4096
      Prclm:           8  DIOlm:      1000  WSdef:        16384
      Prio:            4  ASTlm:      2200  WSquo:        32768
      Queprio:         4  TQElm:        10  WSextent:     65536
      CPU:        (none)  Enqlm:      5000  Pgflquo:     2000000
      Authorized Privileges:
        NETMBX       TMPMBX
      Default Privileges:
        NETMBX       TMPMBX
      
      ---------------------------------------
      
      The installation process is as follows, depending on what you are doing:
      
      A). Updating an existing MariaDB 5.5 installation
          1. Set default to the directory pointed to by your current
             MYSQL055_ROOT logical name.
          3. Unzip MariaDB-5_5_57.zip, telling it to replace all files.
          4a. To upgrade an existing MariaDB 5.5 database execute
              @[.scripts]mysql_upgrade_db upgrade
              This procedure will upgrade the existing database in place.
              Make sure you have a good backup before starting this since
              previous versions of MariaDB will not read the updated database.
          4b. If something goes wrong and you need to repeat the upgrade step,
              delete everything in [.log] and [.data...] and then restore
      	those two directories from your backup copy.  Then repeat
      	the command in step 4a.
          5. Installation/upgrade is now complete.  To start MariaDB do
              @[.vms]start_mysqld
              This is also the command procedure that needs to be invoked at
              system startup.
      
      B). Upgrading a previous MySQL 5.1 installation to MariaDB 5.5
          1. Create a top level directory, owned by the account which will run
             MariaDB.  Example: CREATE/DIR/OWN=MYSQL051_SRV DISK$DRIVE:[MYSQL055]
          2. Set Default to this directory.
          3. Unzip MariaDB-5_5_57.zip
          4a. To upgrade an existing MySQL 5.1 database to MariaDB 5.5 execute
              @[.scripts]mysql_upgrade_db
              This procedure will copy the database files from the MySQL051
              location into this directory tree.  The original files will remain
              untouched.
          4b. If something goes wrong and you wish to repeat this step, simply
              delete everything in [.log] and [.data...] in between each try.
          5. Installation/upgrade is now complete.  To start MariaDB do
              @[.vms]start_mysqld
              This is also the command procedure that needs to be invoked at
              system startup.
      
      C). Installing a brand new MariaDB/MySQL kit where neither has been before
          1. Create a top level directory, owned by the account which will run
             MariaDB.  Example: CREATE/DIR/OWN=MYSQL051_SRV DISK$DRIVE:[MYSQL055]
          2. Set Default to this directory.
          3. Unzip MariaDB-5_5_57.zip
      	----------------------------------------
      	[ NSR-inserted comments ]
      	$ unzip mariadb-5_5_57.zip "Readme.VMS"
      	$ unzip -d DISK$DRIVE:[MYSQL055] mariadb-5_5_57.zip 
      	----------------------------------------
          4a. To create a brand new empty database execute
              @[.scripts]mysql_install_db
          4b. If something goes wrong and you wish to repeat this step, simply
              delete everything in [.log] and [.data...] in between each try.
          5. Installation/upgrade is now complete.  To start MariaDB do
      	----------------------------------------
      	[ NSR-inserted comments ]
      	1) you must first remove world-write privs from file "[.vms]my.cnf"
      	   like so with this DCL command:
      		$ set file [.vms]my.cnf/prot=(w:r)
      	   Failure to do so produces a strange anomalies in the client
      	2) OPTIONAL: if you changed username to MYSQL051_SRV then you
      	   will need to first modify script "[.vms]start_mysqld"  
      	----------------------------------------
              @[.vms]start_mysqld
              This is also the command procedure that needs to be invoked at
              system startup.
          6. The database contains an account named root with no password.
             Use this account to initially set up your users and databases.
      
      That should be all there is to it.  Feel free to contact me if there are any
      issues or questions.
      
      Mark Berryman
      mark@theberrymans.com

MySQL + MariaDB Tips (all versions)

password caveat: in simpler days, database passwords were meant to be: alphabetic, numeric or alphanumeric. In today's world, passwords 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"
      							! note: single dash for single character
      							! switches; double dash for long switches
      <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';	! create a new user named neil
      <sr>	Query OK, 0 rows affected (0.00 sec)
      
      	MariaDB [mysql]>					!
      <ur>	grant ALL on *.* to neil@localhost with grant option;	! our new account needs 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 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:
    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 C or C++
      • demos how to build lib_mysqludf_sys (a User Defined Function which allows you to execute DCL commands from within Stored Procedures)

  9. my data export tools written in 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.

  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.
     
    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.
    • According to some blogs, this problem began in version 6.2.19 and relates to an error in the way the product incorrectly recovers from a call to .NET
    • update: you really want to be using Workbench 8.0 (64-bit Windows edition) at the very minimum. Why?
      1. I have never seen this program crash on the 64-bit versions of Windows-7 or Windows-10
      2. 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
           
  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 delayed (but did not fix) the 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:

  • referential integrity (built into the database)
  • SQL access via ODBC (no need to buy third-party tools or licenses)
  • stored procedures

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)

  • we had been playing with various versions of MySQL-5.1 on OpenVMS for three years (2011-2013)
    • we never experienced any problems with small-to-medium sized tables
    • we experience some flakiness with large databases (tables with more than a million rows) so never put MySQL-5.1 into production
  • we had been using MariaDB-5.5-25 on OpenVMS for two years (2014 to 2016)
    • 50% of our "business critical data" still resides in RMS
    • 50% of our remaining data resides in MariaDB-5.5.25 (and this part appears an order of magnitude faster)
    • we have run into problems which we could solve
    • we have run into problems which we could not solve -AND- reaching out to others never once helped
    • The latest problem is the InnoDB corruption which occurs every time we shut down on OpenVMS (we have no other issues).
    • This left us with various questions:
      • is it the version of MariaDB we are using?
      • is it the ExtraDB storage engine?
      • did something happen to MariaDB during the OpenVMS port?
      • some Linux sites claim all issues are fixed after moving to MariaDB-10.x
  • MySQL and MariaDB on OpenVMS comes to us by the good graces of enthusiastic OpenVMS developers but you will find these products less supported than OpenVMS on Linux. So what do to?
    • look into playing with a more up-to-date version of MariaDB (but since none exists for OpenVMS you would need to do this with Linux or Windows)
    • look into changing the storage engine
    • hope that someone else quickly produces a new OpenVMS port of MariaDB-10
    • look into purchasing an alternate product like Mimer for which you can also purchase an optional support contract

  Recent Events (2016-11-xx)

  • I recently acquired (free of charge) a discarded HP ProLiant DL360-G5 which was manufactured in 2007 (this is an X86-64 XEON box for those following at home)
  • since RHEL and CentOS are currently all the rage in my employer's corporate datacenters, I tried to install CentOS-7 with the hope of installing MariaDB-10 then diverting DB requests on my OpenVMS system to the Linux box over a fast private network
  • This old DL has a built-in in RAID controller (P400) for which CentOS-7 could not find drivers so I tried CentOS-6
  • Likewise, CentOS-6 did not have any drivers for a P400 RAID so I tried CentOS-5
  • This auto-installed like a charm in under 60 minutes but installed MySQL-5.5 (MariaDB binaries are not available for CentOS-5)
  • With a little online help I was able to use the YUM utility to remove MySQL
  • Then I visited downloads.mariadb.org where they presented me with a repository configuration tool which I used to generate a file necessary to install MariaDB-10.1.19
  • Invoking the YUM tool (with help from instructions generated by the repository configuration tool) yielded a fully functional version of MariaDB-10 in under an hour.
  • Now for some reason I don't yet understand, transactions on the OpenVMS-CentOS hybrid system are faster than on the previous OpenVMS-only system.
  • The NIC on the ProLiant is limited to 100 Mb/s so going to a newer user ProLiant might enable 1000 Mb/s
  • caveat: CentOS-5 will be unsupported after 2017-03-xx so this is not something I would recommend. Anyway, we will play with this for three months before developing this any further

  Recent Events (2016-12-xx)

  • I recently acquired (free of charge) a discarded HP ProLiant ML370-G6 which was manufactured in 2011 (this is an X86-64 XEON box for those following at home)
  • This box has a built-in RAID controller (P410) which is supported by CentOS-7 and I was able to install with little effort
  • Upgrading to MariaDB-10.1.19 was child's play
  • These NIC's run 1000 Mb/s and the resulting OpenVMS-CentOS hybrid is scary fast.

  Recent Events (2017-02-xx)

  • I just got word about a discarded HP ProLiant ML385-G7 which was manufactured in 2014
  • Time to jump into the truck to fetch it

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

  • LAMP (Linux, Apache, MySQL, Perl) is is a powerful technology mash-up employed to serve up MySQL databases over the web (The "M" can also represent MariaDB)
  • XAMPP (X-platform, Apache, MySQL, PHP, Perl) is a cross-platform version of LAMP which you can download from here:
  • VAMP is an acronym of "VMS, Apache, MySQL, Perl"
  • VARD is an acronym of "VMS, Apache, RMS, DCL" (The "R" can also represents RDB)

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 Home
Neil Rieck
Waterloo, Ontario, Canada.  /div>