Yum Install MariaDB/MySQL disaster but fixed

So this should be an easy install of MariaDB/MySQL. I do not think this was a Maria issue but just an overall bug. Here is what happened and how I fixed it.

yum install mariadb-server
Then I added the rest to have what you see below.

[root@Fedora64 log]# rpm -qa | grep maria
mariadb-5.5.31-1.fc17.x86_64
mariadb-server-5.5.31-1.fc17.x86_64
mariadb-libs-5.5.31-1.fc17.x86_64
mariadb-devel-5.5.31-1.fc17.x86_64



I thought it was odd that I did not get a /etc/init.d/mysql file but I went with it, I wanted to see what happened.

[root@Fedora64 log]# mysqld_safe
130608 19:54:36 mysqld_safe Logging to '/var/log/mysqld.log'.
130608 19:54:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 19:54:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

130608 19:54:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 19:54:37 InnoDB: The InnoDB memory heap is disabled
130608 19:54:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130608 19:54:37 InnoDB: Compressed tables use zlib 1.2.5
130608 19:54:37 InnoDB: Using Linux native AIO
130608 19:54:37 InnoDB: Initializing buffer pool, size = 128.0M
130608 19:54:37 InnoDB: Completed initialization of buffer pool
130608 19:54:37 InnoDB: highest supported file format is Barracuda.
130608 19:54:38  InnoDB: Waiting for the background threads to start
130608 19:54:39 Percona XtraDB (http://www.percona.com) 5.5.31-MariaDB-30.2 started; log sequence number 1597945
130608 19:54:39 [Note] Plugin 'FEEDBACK' is disabled.
130608 19:54:39 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130608 19:54:39 [Note] Server socket created on IP: '0.0.0.0'.
130608 19:54:39 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
130608 19:54:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended



Wow... The first run and a failure is not a good sign. This is a fresh install mysql directory should have been installed. So I started with --skip-grant-tables so I could get into the box and look around.

[root@Fedora64 mysql]# ls -la
total 28700
drwxr-xr-x.  2 mysql mysql     4096 Jun  8 19:58 .
drwxr-xr-x. 43 root  root      4096 Jun  8 19:41 ..
-rw-rw----.  1 mysql mysql    16384 Jun  8 19:50 aria_log.00000001
-rw-rw----.  1 mysql mysql       52 Jun  8 19:50 aria_log_control
-rw-rw----.  1 mysql mysql 18874368 Jun  8 19:50 ibdata1
-rw-rw----.  1 mysql mysql  5242880 Jun  8 19:58 ib_logfile0
-rw-rw----.  1 mysql mysql  5242880 Jun  8 19:45 ib_logfile1
[root@Fedora64 mysql]#

[root@Fedora64 mysql]# mysqld_safe --skip-grant-tables
130608 20:02:45 mysqld_safe Logging to '/var/log/mysqld.log'.
130608 20:02:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql



OK so it started... and does run but still a MAJOR problem still no MYSQL table !

[root@Fedora64 /]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
Phase 3/3: Running 'mysql_fix_privilege_tables'...
ERROR 1049 (42000): Unknown database 'mysql'
FATAL ERROR: Upgrade failed



This can still be fixed....

[root@Fedora64 /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

MariaDB [(none)]> create database mysql ;
Query OK, 1 row affected (0.13 sec)

MariaDB [(none)]> exit
Bye


OK now it has a mysql table I should be able to upgrade it

[root@Fedora64 /]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
mysql
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK
[root@Fedora64 /]#


OK So I stopped mysqld and started it without --skip-grants after all it just installed it and I have not yet set a password.

[root@Fedora64 mysql]# mysqld_safe
[root@Fedora64 /]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)



Ok so let me try that again...

[root@Fedora64 mysql]# mysqld_safe --skip-grant-tables

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.01 sec)

MariaDB [mysql]> select * from user;
Empty set (0.00 sec)

MariaDB [(none)]>  create user root ;



I cannot use the following command because --skip-grant-tables in enabled.
create user root identified by '';

 So now I have a root user only by name because it has zero privileges.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)



So I have to go into the box again with --skip-grant-tables enabled and update the root account

 UPDATE user
SET Select_priv = 'Y',
  Insert_priv='Y',
           Update_priv='Y',
           Delete_priv='Y',
           Create_priv='Y',
             Drop_priv='Y',
           Reload_priv='Y',
         Shutdown_priv='Y',
          Process_priv='Y',
             File_priv='Y',
            Grant_priv='Y',
       References_priv='Y',
            Index_priv='Y',
            Alter_priv='Y',
          Show_db_priv='Y',
            Super_priv='Y',
 Create_tmp_table_priv='Y',
      Lock_tables_priv='Y',
          Execute_priv='Y',
       Repl_slave_priv='Y',
      Repl_client_priv='Y',
      Create_view_priv='Y',
        Show_view_priv='Y',
   Create_routine_priv='Y',
    Alter_routine_priv='Y',
      Create_user_priv='Y',
            Event_priv='Y',
          Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user = 'root';


Now a restart without --skip-grant-tables enabled and I am in as root!

[root@Fedora64 /]# ps -ef | grep mysql
root      4522  1513  0 20:26 pts/0    00:00:00 /bin/sh /bin/mysqld_safe
mysql     4650  4522  0 20:27 pts/0    00:00:03 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root      8348  3178  0 20:47 pts/1    00:00:00 grep --color=auto mysql
[root@Fedora64 /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]>



WHEW. This is more of an example of how to fix it when things go wrong but I still wanted the /etc/init.d/mysql file

Pivot table or No Pivot Table

This topic recently came up on the forums.mysql.com site.

The opinion expressed was that pivot tables are very hard to scale and maintain it would be worth a redesign of schema instead of a pivot table. This is a valid opinion with valid points.

I would like to add the topic here to help express my point of view and have it available for others.

It all depends on the data being gathered on if you should use a pivot table or not. The example given in a previous post by me was just an example of how they work.

If you are collecting known  user information (First and Last name, Address information, Phone ) then yes a pivot table is more complicated that what you would need.  If you just have a few data points to tie them to outside of that core information then yes another table is a solution and tied with a simple join.

The pivot table concept is valid when it is for dynamic amounts of data per entity you are collecting.
You might need 10 data points for 100 users. You might need 500 data points on the next 100 users.  Can the schema handle it easily?  

The example given in previous post I agree  does not require a pivot table. But I just used the concept given to me in the forum to answer the question asked.

Ideally you can use both solutions in your schema. Core data points, keep in columns. Dynamic data keep in pivot tables.

If it is built correctly it is very scalable, the billions and billions of data I stored in the pivot table proved this to me easily.  That does not mean it would not require some work. You very well might find that creating some views or summary tables that look into the pivot table would be easier for others to gather data. This begs the question then why wasn't the data stored that way in the first place? Again it depends on the dynamic nature of your data and application that uses the data.

MEMORY and TEMPORARY Tables

Since I have received a request to help answer forum.mysql.com questions with the blog I will continue to post some extended examples here.

I noticed this post: http://forums.mysql.com/read.php?10,588192,588192#msg-588192  and I first thought of a different way to handle the situation.

If you need tables to handle temporary information you can go about it in two ways. One if it is per session processing then you should create a TEMPORARY table only:

 CREATE TEMPORARY TABLE `temporary_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ; 


This will result in NO .frm file and will go away and close of the session.
If you need it available longer and need it to be fast you can use a MEMORY table. This will stay until you reboot database, delete table, etc... 

CREATE TABLE `memory_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY;


This again means NO .frm file.

So if you want to go clean the memory tables  up because you have so many or something you can find a list with the following ...

SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables,
  SUM(DATA_LENGTH+INDEX_LENGTH) AS size,
SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')
  AND ENGINE = "MEMORY" GROUP BY TABLE_SCHEMA, ENGINE;


As always... review your needs and benchmark what works best for you and your application.

Pivot tables example in MySQL

I was asked on the forums.mysql.com site how to build a subscription table to track courses and etc.

It was easier to post the full example here, it is a quick brief example but you get the idea.

The concept here is simple.
We store information in rows that we can then pull back out into different columns when needed.

The request was for a subscription of students and courses....

First I built out some tables and data...


 CREATE TABLE `details` (
  `details_id` int(11) NOT NULL AUTO_INCREMENT,
  `details_label` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`details_id`)
) ENGINE=InnoDB;
INSERT INTO details VALUES (1,'First Name') , (2, 'Last Name') ;

CREATE TABLE `subjects` (
  `subject_id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` enum('History','English','Geography','Mathematics','Science','Social Studies','Foreign Languages','Visual and Performing Arts') DEFAULT NULL,
  `subject_detail` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`subject_id`)
) ENGINE=InnoDB;
INSERT INTO subjects VALUES (1,'Mathematics', 'Algebra') , (2,'History', '1826-1926')  ,  (3,'Geography', ' Africa Studies') ;

CREATE TABLE `student` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(150) DEFAULT NULL,
  `student_key` varchar(20) DEFAULT NULL,
 `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB;
INSERT INTO student (`student_id` ,`email`,`student_key`) VALUES (1,'foobar@gmail.com','iasdjf');

CREATE TABLE `student_details` (
  `student_details_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT 0,
  `details_id` int(11)  DEFAULT 0,
  `details_value` varchar(255) DEFAULT NULL,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`student_details_id`)
) ENGINE=InnoDB;
INSERT INTO student_details VALUES (NULL,1,1,'John',NOW()) ,  (NULL,1,2,'Smith',NOW()) ;

 CREATE TABLE `courselist` (
  `courselist_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT 0,
  `subject_id` int(11) DEFAULT NULL,
  `status` enum('Waitlisted','Subscribed','Denied') DEFAULT NULL,
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`courselist_id`)
) ENGINE=InnoDB;
INSERT INTO courselist VALUES ( NULL,1, 1 , 'Waitlisted' , NOW() ) , ( NULL,1, 2 , 'Subscribed' , NOW() )  , ( NULL,1, 3 , 'Denied' , NOW() )   ;


First just pull information about the student : 


> SELECT s.student_id , d.details_label , sd.details_value
    -> FROM student s
    -> INNER JOIN student_details sd ON s.student_id = sd.student_id
    -> INNER JOIN details d ON sd.details_id = d.details_id;
+------------+---------------+---------------+
| student_id | details_label | details_value |
+------------+---------------+---------------+
|          1 | First Name    | John          |
|          1 | Last Name     | Smith         |
+------------+---------------+---------------+
2 rows in set (0.00 sec)


We can dig more and keep adding information...


> SELECT s.student_id , d.details_label , sd.details_value , c.status, j.subject, j.subject_detail
    -> FROM student s
    -> INNER JOIN student_details sd ON s.student_id = sd.student_id
    -> INNER JOIN details d ON sd.details_id = d.details_id
    -> INNER JOIN courselist c ON s.student_id = c.student_id
    -> INNER JOIN subjects j ON j.subject_id = c.subject_id
    -> ;
+------------+---------------+---------------+------------+-------------+-----------------+
| student_id | details_label | details_value | status     | subject     | subject_detail  |
+------------+---------------+---------------+------------+-------------+-----------------+
|          1 | First Name    | John          | Waitlisted | Mathematics | Algebra         |
|          1 | Last Name     | Smith         | Waitlisted | Mathematics | Algebra         |
|          1 | First Name    | John          | Subscribed | History     | 1826-1926       |
|          1 | Last Name     | Smith         | Subscribed | History     | 1826-1926       |
|          1 | First Name    | John          | Denied     | Geography   |  Africa Studies |
|          1 | Last Name     | Smith         | Denied     | Geography   |  Africa Studies |
+------------+---------------+---------------+------------+-------------+-----------------+
6 rows in set (0.00 sec)


That is not very useful or clean though...
So redo this to pull exactly what we want...


> SELECT s.student_id ,sd1.details_value as FIRST_NAME, sd2.details_value as LAST_NAME,   c.status, j.subject, j.subject_detail
    -> FROM student s
    -> INNER JOIN student_details sd1 ON s.student_id = sd1.student_id AND sd1.details_id = 1
    -> INNER JOIN student_details sd2 ON s.student_id = sd2.student_id AND sd2.details_id = 2
    -> INNER JOIN courselist c ON s.student_id = c.student_id
    -> INNER JOIN subjects j ON j.subject_id = c.subject_id
    -> ;
+------------+------------+-----------+------------+-------------+-----------------+
| student_id | FIRST_NAME | LAST_NAME | status     | subject     | subject_detail  |
+------------+------------+-----------+------------+-------------+-----------------+
|          1 | John       | Smith     | Waitlisted | Mathematics | Algebra         |
|          1 | John       | Smith     | Subscribed | History     | 1826-1926       |
|          1 | John       | Smith     | Denied     | Geography   |  Africa Studies |
+------------+------------+-----------+------------+-------------+-----------------+
3 rows in set (0.00 sec)


MySQL Check Table

The MySQL Check tables command is very useful for anyone who wants to do the following:

• Checking Version Compatibility 
• Checking Data Consistency 
• Upgrades
• General Table Errors

 The process is simple enough:

> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| City            |
| Country         |
| CountryLanguage |
+-----------------+

> check table City\G
*************************** 1. row ***************************
   Table: world.City
      Op: check
Msg_type: status
Msg_text: OK


This is a good task to stay updated on so you are aware of possible errors. One possible problem is that this tool really focuses on MyISAM and not InnoDB. If you use it for InnoDB, The "Check table" command really only applies when you add the QUICK option (or no options).  The FAST, CHANGED, MEDIUM and EXTENDED options are all ignored for InnoDB. Now, if you are asking yourself, what about InnoDB? Why would MySQL ignore data consistency in the InnoDB engine? Take a deep breath and relax, InnoDB is ACID complaint, ACID is "an acronym standing for atomicity, consistency, isolation, and durability." So do not disregard checking InnoDB tables because it can still provide you some insight or confirmation on your tables. Keep in mind that if an InnoDB table was to be corrupted the server will shut down to protect the data. You just get more bang for your buck with MyISAM tables and this tool.

Hopefully you get a response of "OK" or "Table is already up to date" otherwise you need to run a repair table to fix the table.

So what are the options available to us so you can do this often and easily.
The documentation link below will also provide you with several community driven automatic options. You can script it the process and easily show tables then apply check tables to all of your results. It just appears easier to me though to use the tools provided for you.


$ mysqlcheck -u root -p --databases world --fast
Enter password:
world.City                               OK
world.Country                            OK
world.CountryLanguage                    OK

$mysqlcheck -u root -p --databases world --fast --check-only-changed
Enter password:
world.City                               OK
world.Country                            OK
world.CountryLanguage                    OK


Now this is simple and direct but it also lends itself to another question, What about the password?

Should you create a user with no password that is allowed to check tables just so you do not have to put a password into your script or cron job? You want to prevent having the password sitting around in .mysql_history files as well.  So again take advantage of the tools available for you. MySQL 5.6 introduced the MySQL Configuration Utility.  I have an example of how to set it up in a previous blog post:
http://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html

mysqlcheck --login-path=local  --databases world --fast --check-only-changed
world.City                              OK
world.Country                           OK
world.CountryLanguage                   OK


$ mysqlcheck --help will provide a full list of options available to you.
Now, you can check all of your tables, keep your passwords out of the crontab file and/or scripts.

Documentation:

• http://dev.mysql.com/doc/refman/5.5/en/check-table.html