Converting InnoDB tables to Barracuda in mysql/mariadb

$ cd /path/to/your/moodle
$ php admin/cli/mysql_compressed_rows.php
Script for detection of row size problems in MySQL InnoDB tables.

By default InnoDB storage table is using legacy Antelope file format
which has major restriction on database row size.
Use this script to detect and fix database tables with potential data
overflow problems.

Options:
-i, --info Show database information
-l, --list List problematic tables
-f, --fix Attempt to fix all tables (requires SUPER privilege)
-s, --showsql Print SQL statements for fixing of tables
-h, --help Print out this help

Example:
$ sudo -u www-data /usr/bin/php admin/cli/mysql_compressed_rows.php -l

$ php admin/cli/mysql_compressed_rows.php -l
mdl_data Compact (needs fixing)
mdl_data_fields Compact (needs fixing)
mdl_enrol_paypal Compact (needs fixing)
mdl_lti Compact (needs fixing)
mdl_user Compact (needs fixing)
mdl_user_info_field Compact (needs fixing)

$ php admin/cli/mysql_compressed_rows.php -f
Cannot enable GLOBAL innodb_file_per_table setting, use --showsql option and execute the statements manually.!!! Error writing to database !!!

$ php admin/cli/mysql_compressed_rows.php -s
Copy the following SQL statements and execute them using account with SUPER privilege:

USE moodle27;
SET SESSION sql_mode=STRICT_ALL_TABLES;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
ALTER TABLE mdl_data ROW_FORMAT=Compressed;
ALTER TABLE mdl_data_fields ROW_FORMAT=Compressed;
ALTER TABLE mdl_enrol_paypal ROW_FORMAT=Compressed;
ALTER TABLE mdl_lti ROW_FORMAT=Compressed;
ALTER TABLE mdl_user ROW_FORMAT=Compressed;
ALTER TABLE mdl_user_info_field ROW_FORMAT=Compressed;

$ mysql -u root -p
Enter password: [invisible]
Welcome to the MySQL monitor.
[...]
mysql> use dbname;
Database changed
mysql> SET SESSION sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE mdl_data ROW_FORMAT=Compressed;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 2

Jdbc configurations in eclipse

 Usage of Mariadb java connector


#Download mariadb java connector mariadb-java-client-1.3.6.jar (exact name depends on the driver version) from https://downloads.mariadb.org/connector-java/
#Then include in eclipse under java build path --> libraries --> Add External Jar files and also include in tomcat web server under lib folder
#In servlet use the below code

<pre>
  //loading drivers for mysql
            Class.forName("org.mariadb.jdbc.Driver");

//creating connection with the database
          Connection  con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","dbuser","dbpasswd");
</pre>

 Usage of mysqldb java connector 


#Download mysql java connector mysql-connector-java-5.1.36-bin.jar (exact name depends on the driver version) from http://www.mysql.com/downloads/connector/j/
#Then include in eclipse under java build path --> libraries --> Add External Jar files and also include in tomcat web server under lib folder
#In servlet use the below code

<pre>
            //loading drivers for mysql
            Class.forName("com.mysql.jdbc.Driver");
 

//creating connection with the database
          Connection  con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","dbuser","dbpasswd");
</pre>