Fixing Drobo MySQL installation and Kodi library view tables

  Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
andrewjpaull Offline
Junior Member
Posts: 2
Joined: Mar 2015
Reputation: 0
Post: #1
I have come across a couple of issues regarding the Drobo NAS devices and their native MySQL ported app and have created fixes for them.

Issue #1: MySQL on Drobo may fail installation and "Stop" halfway through. Re-installing may not work and restarting the device has no effect. Although there is already a thread regarding this issue, the "fix" given for it does not work for everyone.

Issue #2: After starting MySQL on the Drobo and granting all necessary MySQL permissions to the XBMC/Kodi user, XBMC/Kodi library views (tvshowview, movieview, episodeview, musicvideoview, etc..) may not be created or populated when the databases are made. When this happens, selecting a library such as Movies or TV Shows within XBMC/Kodi will result in the user being presented with the "genre, title, year..." view rather than the library content view.

This is because proper access hasnt been granted by the MySQL application for all the tables that the SQLite3 format in XBMC/Kodi uses and therefore certain tables, in this case "views" are not created. MySQL 5.6 is more SQL-2003 compliant and therefore has more data security that previous mySQL editions did not.


Issue #1 Resolution
Anyone who owns a Drobo NAS and is planning on using it for an XBMC/Kodi source and MySQL Server should know that the Drobo port of the Perl App needs to be installed manually before the installation of MySQL can be completed. Sometimes however; As stated previously, even when installing Perl first, the MySQL installation may ultimately fail and re-installing MySQL or restarting the Drobo or PC may not resolve the issue.

That Is where this thread comes in.

There are some consistency issues with the newest Drobo Perl5 application and the Drobo MySQL 5.6 app that prevents MySQL installation from completing.
To solve this and allow MySQL to install completely and begin running, perform the following actions:

1. Uninstall MySQL if it has been partially installed or has stopped running.
2. Copy the "Perl5" folder to the same DroboApps directory it is already in and rename the copy "Perl" without the 5. Do not delete the original "Perl5" folder. Both directories are needed for this fix to work. There should now be a "Perl" and "Perl5" folder in the DroboApps directory.
3. Restart the MySQL installation and it should complete successfully and begin running on its own.
4. SSH into the Drobo MySQL app and create the XBMC/Kodi user and grant all necessary permissions. There is another thread that explains how to do this.

The MySQL 5.6 application sometimes looks for data in both the "Perl" and "Perl5" folders for installation. I do not know why this is. The information is obviously identical. MySQL installation scripts for the Drobo ported app may not have been updated properly to call upon data in directories of newer Perl versions.

Issue #2 Resolution
Because the Drobo port for MySQL has been updated to 5.6 there are some inherent issues with XBMC/Kodi compatibility. One of these issues pertains to certain access rights that are denied to applications attempting to alter or create tables externally. If you are one of the few people having issues accessing your library views or saving the content settings for your XBMC/Kodi source folders, follow these steps:

1. Access your DroboApps folder and find the MySQL application directory. Inside locate the folder titled "etc" and open it.
2. Within the "etc" folder there is a file titled "my.cnf". Open this file using notepad or Notepad++
3. Locate the line that says "mysql mode = "
4. In the parameters that follow remove these two strings: "STRICT_TRANS_TABLES" and "ONLY_FULL_GROUP_BY". Be sure to remove the "," if there is one before the first, or after the last parameter in that section. These parameters prevent some access to table modifications in MySQL even if the user has been granted full access to make changes.
5. Log into your MySQL app and remove any schemas that were already created in your Drobo MySQL database.
6. Make sure your XBMC/Kodi "advancedsettings.xml" points your XBMC/Kodi application to the Drobo's MySQL database. Setting the host by IP is the best practice
7. Open XBMC/Kodi, create/set content for your source directories and scan for content.
8. Once some media files have been added to your library like Movies or TV Shows, open one and see if the proper content view opens.


I hope this helps. I spent a long time trying to resolve these issues myself and there were not alot of topics on the web that provided good answers.

*UPDATE 03/25/2015*

I have located the configuration information in the MySQL "services.sh" file that determines where MySQL will check for the Perl installation. By default MySQL only looks for a directory named Perl and not for Perl 5. The file contents are below:


#!/bin/sh
#
# MySQL server

. /etc/service.subr

prog_dir=`dirname \`realpath $0\``

name="mysql"
version="5.6.13"
description="MySQL server"
framework_version="2.0"

daemon="${prog_dir}/bin/mysqld"
datadir="${prog_dir}/data"
pidfile="/tmp/DroboApps/${name}/pid.txt"
logfile="/tmp/DroboApps/${name}/log.txt"
conffile="${prog_dir}/etc/my.cnf"

_create_user() {
grep -q "^mysql" /etc/passwd 1> /dev/null 2>&1
if [ $? -ne 0 ]; then
adduser -S -H -h "${datadir}" -D -s /bin/false -G nobody -u 40 mysql 1> /dev/null 2>&1
fi
chown -R mysql "/tmp/DroboApps/${name}" "${prog_dir}" 1>> "${logfile}" 2>&1
}

_create_config() {
if [ ! -f "${conffile}" ]; then
cp "${conffile}.default" "${conffile}" 1>> "${logfile}" 2>&1
fi
}

_init_mysql() {
local rootpass
if [ -d ${datadir}/mysql ]; then
return 0
fi
if [ ! -x $DROBOAPPS_DIR/perl/bin/perl ]; then
echo "There is no /usr/bin/perl. MySQL requires perl to initialize the database." 1>> ${logfile}
return 1
fi
HOME="${datadir}" $DROBOAPPS_DIR/perl/bin/perl ${prog_dir}/scripts/mysql_install_db --basedir="${prog_dir}" --datadir="${datadir}" --user=mysql --random-passwords 1>> "${logfile}" 2>&1
if [ ! -f "${datadir}/.mysql_secret" ]; then
echo "MySQL root password file not found. root.cnf not created!" >> ${logfile} ; return 2
fi
rootpass=`tail -n2 "${datadir}/.mysql_secret" | awk -F": " 'NR==1 {print $2}' 2> /dev/null`
if [ -z "$rootpass" ]; then
echo "MySQL root password not found. root.cnf not created!" >> ${logfile} ; return 2
fi
rm -fr "${datadir}/.root.cnf"
cat > "${datadir}/.root.cnf" << EOCNF
[client]
user=root
password=$rootpass
EOCNF
chmod 600 "${datadir}/.root.cnf" 1> /dev/null 2>&1
}

start() {
_create_user
_create_config
_init_mysql
rm -f /tmp/DroboApps/mysql/pid.txt
${daemon} --basedir="${prog_dir}" --datadir="${datadir}" --pid-file="${pidfile}" --log-error="${logfile}" --user=mysql 1>> "${logfile}" 2>&1 &
}

case "$1" in
start) start_service ;;
stop) stop_service ;;
restart) stop_service ; sleep 3 ; start_service ;;
status) status ;;
create_db) shift ; create_db $@ ;;
*) echo "Usage: $0 [start|stop|restart|status]" ; exit 1 ;;
esac



To solve this issue, begin installing MySQL on your Drobo. When the installation stops, open the MySQL "service.sh" file and edit the two lines with the "$DROBOAPPS_DIR/perl/bin/perl" directory to read as "$DROBOAPPS_DIR/perl5/bin/perl". Once this is complete, save the file, restart the Drobo and start the app. After clicking the MySQL start button it may take up to 30 seconds before the app status changes from "Stopped" to "Running" This is normal.
(This post was last modified: 2015-03-25 18:00 by andrewjpaull.)
find quote
Ned Scott Offline
Team-Kodi Wiki Guy
Posts: 24,734
Joined: Jan 2011
Reputation: 333
Location: Arizona, USA
Post: #2
neat. Added to tricks and tips forum.

You can make easy links to the XBMC wiki using double brackets around common XBMC words: [[debug log]] = debug log (wiki), [[Video library]] = Video library (wiki), [[SMB]] = SMB (wiki) , [[userdata]] = userdata (wiki), etc
find quote