TurnKey Linux Virtual Appliance Library

LAMP phpMyAdmin supports INNODB - no need to edit my.cnf? (SOLVED)

I have been trying to figure out if I need to change my.cnf to enable INNODB from the LAMP appliance I have installed..

Any advice how to make it enabled? or alter the tables into INNODB instead of MyISIAM?

Currently I have set up some tables in MyISIAM. . .as I tried using INNODB - it generates error message. .

Thanks.
leow

Alon Swartz's picture

Tips using InnoDB

Firstly, see this post for reference.

Secondly, I have found that the innodb logfiles constantly get corrupted when the mysql service shuts down. An ugly (but useful) hack is to remove the logfiles when starting the service:
# ugly hack for innodb (logfiles constantly get corrupted)
sed -i s"|  'start')|  'start')\n        # ugly hack for innodb logfile corruption\n        rm -f /var/lib/mysql/ib_logfile*|" /etc/init.d/mysql
Note: I have not used this hack in a production environment, but in my testing it works without any unforeseen issues.

Hi, Alon, this is what I got.

Hi, Alon, this is what I got. .

root@lamp:~# sed -i s"| 'start')| 'start')
> rm -f /var/lib/mysql/ib_logfile*|" /etc/init.d/mysql
sed: -e expression #1, char 21: unterminated `s' command
root@lamp:~#
 

It is still not working... 

P.S. I am still a newbie, I know I have typed wrongly but not knowing exactly what was wrong, please anyone help to correct the hacks I entered..


Alon Swartz's picture

The newline is being interpreted by your shell

The newline is being interpreted by your shell.
The easiest way would be to manually edit /etc/init.d/mysql, and add this after 'start')
# ugly hack for innodb logfile corruption
rm -f /var/lib/mysql/ib_logfile*
Don't forget to specify the default storage engine (see mentioned post for details).
And ofcourse, /etc/init.d/mysql restart after your changes.

So, I went on to do

So, I went on to do these:

root@lamp:~# /etc/init.d/mysql stop
 * Stopping MySQL database server mysqld                                                                      [ OK ]
root@lamp:~# mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
root@lamp:~# mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
root@lamp:~# /etc/init.d/mysql start
 * Starting MySQL database server mysqld                                                                      [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
root@lamp:~# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
root@lamp:~# show engines
-bash: show: command not found
root@lamp:~# cat /etc/mysql/conf.d/storage_engine.cnf
cat: /etc/mysql/conf.d/storage_engine.cnf: No such file or directory
root@lamp:~# /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld                                                                      [ OK ]
 * Starting MySQL database server mysqld                                                                      [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
 

 

===

I still can't create tables with engine INNODB...


Alon Swartz's picture

the specified commands where exemplary

The specified commands where exemplary, when you receive an error, stop and try figure out why.

A couple of notes:

  • if you changed the mysql root password, you need to specify it: mysql -uroot -pPASSWORD
  • show engines; is a mysql query, and needs to be executed in the mysql prompt (after successfully connecting).
  • you need to create /etc/mysql/conf.d/storage_engine.cnf (I just used cat to show the contents of the file)

Here is how I have placed the

Here is how I have placed the code manually:

# ugly hack for innodb logfile corruption
rm -f /var/lib/mysql/ib_logfile*
#!/bin/bash
#
### BEGIN INIT INFO
# Provides:          mysql
# Required-Start:    $remote_fs $syslog mysql-ndb
# Required-Stop:     $remote_fs $syslog mysql-ndb
# Should-Start:      $network $named $time
# Should-Stop:       $network $named $time
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Start and stop the mysql database server daemon
------

It doesn't help..any way I have been trying to create the file storage_engine.cnf.. How do I create that? Do I just create a blank file named storage_engine.cnf?

 


Alon Swartz's picture

Clarifications

when I referred to start, i didn't mean at the "start" of the file, but rather after the 'start') code - it should look something like this:

BEFORE
------

case "${1:-''}" in
  'start')
        sanity_checks;
        # Start daemon
        log_daemon_msg "Starting MySQL database server" "mysqld"

AFTER
-----

case "${1:-''}" in
  'start')
        # ugly hack for innodb logfile corruption
        rm -f /var/lib/mysql/ib_logfile*
        sanity_checks;
        # Start daemon
        log_daemon_msg "Starting MySQL database server" "mysqld"

Regarding storage_engine.cnf, you can use your favourite editor (vim, nano, etc.) to create the file, or just do this:

cat >/etc/mysql/conf.d/storage_engine.cnf<<EOF
[mysqld]
default-storage-engine = InnoDB
EOF

BTW, why do you want to use InnoDB in the first place??

Hi Alon, thanks but about

Hi Alon, thanks but about creating the storage_engine.cnf..

I have followed exactly as you have written, however in the file I will have only one line which is:

default-storage-engine = InnoDB

I doubt that is right as I restart mysql, the error I received is:

root@lamp:~# /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld                                                                                     error: Found option without preceding group in config file: /etc/mysql/conf.d/storage_engine.cnf at line: 1
error: Found option without preceding group in config file: /etc/mysql/conf.d/storage_engine.cnf at line: 1
                                                                                                                      [ OK ]
error: Found option without preceding group in config file: /etc/mysql/conf.d/storage_engine.cnf at line: 1
 * Starting MySQL database server mysqld                                                                                     error: Found option without preceding group in config file: /etc/mysql/conf.d/storage_engine.cnf at line: 1
error: Found option without preceding group in config file: /etc/mysql/conf.d/storage_engine.cnf at line: 1
error: Found option without preceding group in config file: /etc/mysql/conf.d/storage_engine.cnf at line: 1
                                                                                                                      [ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
 

 **

About why I use InnoDB, I thought that's how we need to create tables which involve foreign keys..


Alon Swartz's picture

missing [mysqld]

Just add [mysqld] to storage_engine.cnf , that should fix it.

It works in MySQL > show engines; But not phpMyAdmin > Storage..

It works in MySQL > show engines;

I can see my default engine now is INNODB.

But not in:

phpMyAdmin > Storage engines

Default engine is still MyISIAM.

In short, it doesn't work when I try creating INNODB engine table.

After went through all the changes I now can't even create MyISIAM table in phpMyAdmin.

Like I asked earlier, shouldn't we do something with my.cnf?

 

 


Hi Alon, sorry for me being

Hi Alon, sorry for me being desperate and unknowlegeable in Linux..

Thanks for your help this far although I still can't seem to find any solutions to this..


Alon Swartz's picture

No problem, just keep...

No problem, just keep experimenting and learning, and you'll get there.

You are right that you need to edit my.cnf, which is what we did although indirectly. my.cnf imports all configuration in /etc/mysql/conf.d/

I'm out of tips, so I will need to perform the changes locally and see if I can replicate the issues you are having. I'll try get to it later in the day and report back my findings...

Thank you..

Just Thank you..


Alon Swartz's picture

Steps to enabled InnoDB as default storage engine

Ok, so I found some time to do some testing, and have sketched out the exact steps I used to enable InnoDB as the default storage engine of MySQL.
Please note the GOTCHA in the PHPMyAdmin section.

Steps to enabled InnoDB as default storage engine

Stop MySQL

/etc/init.d/mysql stop


Ugly hack for innodb (logfiles constantly get corrupted)

sed -i "s|  'start')|  'start')\n        # ugly hack for innodb logfile corruption\n        rm -f /var/lib/mysql/ib_logfile*|" /etc/init.d/mysql


Start MySQL

/etc/init.d/mysql start


Display supported/default engines

mysql -uroot -pPASSWORD
> show engines;
 MyISAM     | DEFAULT
 InnoDB     | YES
 ...
> quit;


Configure default storage engine

cat > /etc/mysql/conf.d/storage_engine.cnf <<EOF
[mysqld]
default-storage-engine = InnoDB
EOF


Restart mysql for changes to take effect

/etc/init.d/mysql restart


 Display supported/default engines

mysql -uroot -pPASSWORD
> show engines;
 MyISAM     | ENABLED
 InnoDB     | DEFAULT
 ...
> quit;


Testing with PHPMyAdmin

Connect to phpmyadmin with your browser: https://appliance_ip:12322
Click "storage engines" -> "InnoDB"
  This will display InnoDB's configuration and that it is the default storage engine

GOTCHA: Do not be confused by the description of MyISAM
        MyISAM       Default engine as of MySQL 3.23 with great performance

        It is just the description, it is not the engine status

Create a test database with test tables, notice that the default is InnoDB.

 

Enjoy!

It probably has already been working since my last post. . :)

I was just overly desperate or something..

I noticed that you are definitely right there wasn't much new things in your latest post - just that I didn't know how to read description and status they are 2 different thing. :)

Yes, now it works. I imported INNODB tables and it works.

Thank you, Alon.


Alon Swartz's picture

Better solution for innodb logfiles

In my previous post I recommended using an ugly hack to remove the innodb logfiles pre mysql start as they "contantly get corrupted".

In theory, this should not happen and if it does the sysadmin should remedy the problem by shutting down mysql (without errors), manually remove the logfiles and restart mysql to avoid data loss.

I hacked together a patch to the mysql init script to be a little less forceful. It only takes into account the default innodb configuration, so if you tweaked it this might not work for you.

My patch is in bold:

#!/bin/bash
#
### BEGIN INIT INFO
# Provides:          mysql
# Required-Start:    $remote_fs $syslog mysql-ndb
# Required-Stop:     $remote_fs $syslog mysql-ndb
# Should-Start:      $network $named $time
# Should-Stop:       $network $named $time
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Start and stop the mysql database server daemon
# Description:       Controls the main MySQL database server daemon "mysqld"
#                    and its wrapper script "mysqld_safe".
### END INIT INFO
#
set -e
set -u
${DEBIAN_SCRIPT_DEBUG:+ set -v -x}

test -x /usr/sbin/mysqld || exit 0

. /lib/lsb/init-functions

SELF=$(cd $(dirname $0); pwd -P)/$(basename $0)
CONF=/etc/mysql/my.cnf
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"

# priority can be overriden and "-s" adds output to stderr
ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql -i"

# Safeguard (relative paths, core dumps..)
cd /
umask 077

# mysqladmin likes to read /root/.my.cnf. This is usually not what I want
# as many admins e.g. only store a password without a username there and
# so break my scripts.
export HOME=/etc/mysql/

## Fetch a particular option from mysql's invocation.
#
# Usage: void mysqld_get_param option
mysqld_get_param() {
    /usr/sbin/mysqld --print-defaults \
        | tr " " "\n" \
        | grep -- "--$1" \
        | tail -n 1 \
        | cut -d= -f2
}

## Do some sanity checks before even trying to start mysqld.
sanity_checks() {
  # check for config file
  if [ ! -r /etc/mysql/my.cnf ]; then
    log_warning_msg "$0: WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz"
    echo                "WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz" | $ERR_LOGGER
  fi

  # check for diskspace shortage
  datadir=`mysqld_get_param datadir`
  if LC_ALL=C BLOCKSIZE= df --portability $datadir/. | tail -n 1 | awk '{ exit ($4>4096) }'; then
    log_failure_msg "$0: ERROR: The partition with $datadir is too full!"
    echo                "ERROR: The partition with $datadir is too full!" | $ERR_LOGGER
    exit 1
  fi
}

## Checks if there is a server running and if so if it is accessible.
#
# check_alive insists on a pingable server
# check_dead also fails if there is a lost mysqld in the process list
#
# Usage: boolean mysqld_status [check_alive|check_dead] [warn|nowarn]
mysqld_status () {
    ping_output=`$MYADMIN ping 2>&1`; ping_alive=$(( ! $? ))

    ps_alive=0
    pidfile=`mysqld_get_param pid-file`
    if [ -f "$pidfile" ] && ps `cat $pidfile` >/dev/null 2>&1; then ps_alive=1; fi
   
    if [ "$1" = "check_alive"  -a  $ping_alive = 1 ] ||
       [ "$1" = "check_dead"   -a  $ping_alive = 0  -a  $ps_alive = 0 ]; then
    return 0 # EXIT_SUCCESS
    else
      if [ "$2" = "warn" ]; then
          echo -e "$ps_alive processes alive and '$MYADMIN ping' resulted in\n$ping_output\n" | $ERR_LOGGER -p daemon.debug
    fi
      return 1 # EXIT_FAILURE
    fi
}

delete_innodb_logfiles () {
    # remove innodb logfiles if they differ in size
    datadir=`mysqld_get_param datadir`

    ibsize0=$(du $datadir/ib_logfile0 | cut -f 1)
    ibsize1=$(du $datadir/ib_logfile1 | cut -f 1)
    if [ "$ibsize0" != "$ibsize1" ]; then
        rm -f $datadir/ib_logfile*
        return 0
    fi
    return 1
}
# # main() # case "${1:-''}" in   'start')     sanity_checks;     # Start daemon     log_daemon_msg "Starting MySQL database server" "mysqld"     if mysqld_status check_alive nowarn; then        log_progress_msg "already running"        log_end_msg 0     else           /usr/bin/mysqld_safe > /dev/null 2>&1 &         # 6s was reported in #352070 to be too few when using ndbcluster         for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do                 sleep 1             if mysqld_status check_alive nowarn ; then break; fi         log_progress_msg "."         done         if mysqld_status check_alive warn; then                 log_end_msg 0             # Now start mysqlcheck or whatever the admin wants.             output=$(/etc/mysql/debian-start)         [ -n "$output" ] && log_action_msg "$output"         else             log_end_msg 1         log_failure_msg "Please take a look at the syslog"         fi     fi     # Some warnings         if $MYADMIN variables | egrep -q have_bdb.*YES; then             echo "BerkeleyDB is obsolete, see /usr/share/doc/mysql-server-5.0/README.Debian.gz" | $ERR_LOGGER -p daemon.info         fi         if [ -f /etc/mysql/debian-log-rotate.conf ]; then             echo "/etc/mysql/debian-log-rotate.conf is obsolete, see /usr/share/doc/mysql-server-5.0/NEWS.Debian.gz" | $ERR_LOGGER -p daemon.info         fi     ;;   'stop')     # * As a passwordless mysqladmin (e.g. via ~/.my.cnf) must be possible     # at least for cron, we can rely on it here, too. (although we have     # to specify it explicit as e.g. sudo environments points to the normal     # users home and not /root)     log_daemon_msg "Stopping MySQL database server" "mysqld"     if ! mysqld_status check_dead nowarn; then         set +e         shutdown_out=`$MYADMIN shutdown 2>&1`; r=$?         set -e         if [ "$r" -ne 0 ]; then             log_end_msg 1             [ "$VERBOSE" != "no" ] && log_failure_msg "Error: $shutdown_out"             log_daemon_msg "Killing MySQL database server by signal" "mysqld"             killall -15 mysqld             server_down=             for i in 1 2 3 4 5 6 7 8 9 10; do                 sleep 1                 if mysqld_status check_dead nowarn; then server_down=1; break; fi             done             if test -z "$server_down"; then killall -9 mysqld; fi         fi     fi     if ! mysqld_status check_dead warn; then         log_end_msg 1         log_failure_msg "Please stop MySQL manually and read /usr/share/doc/mysql-server-5.0/README.Debian.gz!"         exit -1     else         #its safe to clean delete logfiles if mysql has shutdown without errors
        delete_innodb_logfiles || true
        log_end_msg 0     fi     ;;   'restart')     set +e; $SELF stop; set -e     $SELF start     ;;   'reload'|'force-reload')       log_daemon_msg "Reloading MySQL database server" "mysqld"     $MYADMIN reload     log_end_msg 0     ;;   'status')     if mysqld_status check_alive nowarn; then       log_action_msg "$($MYADMIN version)"     else       log_action_msg "MySQL is stopped."       exit 3     fi       ;;   'delete-innodb-logfiles')
    # remove innodb logfiles if they differ in size
    # do not remove the logfiles if mysql is not stopped
    if mysqld_status check_alive nowarn; then
      log_action_msg "MySQL is not stopped, refusing to delete innodb logfiles"
      exit 4
    fi
    if delete_innodb_logfiles; then
      log_action_msg "InnoDB logfiles deleted"
    else
      log_action_msg "InnoDB logfiles not deleted as they do not differ in size"
    fi
    ;;
  *)     echo "Usage: $SELF start|stop|restart|reload|force-reload|status|delete-innodb-logfiles"     exit 1     ;; esac

Hi Alon, I don't even know

Hi Alon, I don't even know where to begin. See, I'm 'still' dumb with Linux. Maybe I need more time practising .. but college has been busy with -non-linux assignments.

Keep updating, thank you very much..

By the way, if I SSH through any of Turnkey appliance, and use apt-get update from there, is it the correct way to do so? or is it automatically updated?

Thanks.


Post new comment

The content of this field is kept private and will not be shown publicly. If you have a Gravatar account, used to display your avatar.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <p> <span> <div> <h1> <h2> <h3> <h4> <h5> <h6> <img> <map> <area> <hr> <br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong> <font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <strike> <caption>

More information about formatting options

Leave this field empty. It's part of a security mechanism.
(Dear spammers: moderators are notified of all new posts. Spam is deleted immediately)