topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Tuesday March 19, 2024, 4:56 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Changing from MyISAM to InnoDB in MYSQL DB  (Read 2973 times)

questorfla

  • Supporting Member
  • Joined in 2012
  • **
  • Posts: 570
  • Fighting Slime all the Time
    • View Profile
    • Donate to Member
Changing from MyISAM to InnoDB in MYSQL DB
« on: December 10, 2014, 07:21 PM »
Given the following statements in a "template.sql" used as the starting point for building a MySQL DB.
Code: Text [Select]
  1. DROP TABLE IF EXISTS `cat_permissions`;
  2. CREATE TABLE IF NOT EXISTS `cat_permissions` (
  3.   `category_id` int(11) NOT NULL default '0',
  4.   `user_id` int(11) NOT NULL default '0',
  5.   `permission` enum('View') NOT NULL default 'View',
  6.   PRIMARY KEY  (`category_id`,`user_id`)
  7. ) TYPE=MyISAM;
If I change the 'Type' to "Type=InnoDB" ...        When I run the install, I just wondered what the chances of it working?   I would like to convert a DB from MySQL to InnoDB and not real sure about the consequences of the making the change.  Either that, of find out why this won't run under MySQL Vesion 5.5, or any version since 5.1
« Last Edit: December 10, 2014, 07:22 PM by questorfla, Reason: fixed code highlighting »

Stoic Joker

  • Honorary Member
  • Joined in 2008
  • **
  • Posts: 6,646
    • View Profile
    • Donate to Member
Re: Changing from MyISAM to InnoDB in MYSQL DB
« Reply #1 on: December 10, 2014, 09:55 PM »
They're both MySQL database types, one is just transactional. A transactional db is something you either need (for certain type of data processing) or you don't (because it has more overhead).

IIRC the handling of NULL changed around then, I had a few intranet sites that needed some quick tweaking when I upgraded the MySQL db engine because of the way I had (miss)used NULL on a few columns.

questorfla

  • Supporting Member
  • Joined in 2012
  • **
  • Posts: 570
  • Fighting Slime all the Time
    • View Profile
    • Donate to Member
Re: Changing from MyISAM to InnoDB in MYSQL DB
« Reply #2 on: January 03, 2015, 06:42 PM »
That could be it as the error I got was something about invalid values.  What I changed was the TYPE=MyISAM to ENGINE=MYISAM as I had been told of that change.  but I knew there had to be more than just that. And it is a long jump from 5.1 to 5.5.  Additionally I ran into some issues in PHP handlers.  Way over my capabilities in that department.  That is why I gave up Software support of Hardware support.
But lately even hardware has begun to morph at a rate faster than I can keep up with it.
At least I feel a little better to know I am not alone in my despair.

Shades

  • Member
  • Joined in 2006
  • **
  • Posts: 2,922
    • View Profile
    • Donate to Member
Re: Changing from MyISAM to InnoDB in MYSQL DB
« Reply #3 on: January 04, 2015, 01:03 AM »
Are you hung up on database names or can you use whatever database?

With the latter, you could make a database "<your db name>_ISAM", recreate all tables & columns from "<your db name>" into "<your db name>_ISAM", then transfer/replicate the data and point your application(s) to use "<your db name>_ISAM"?

This way you are creating a test environment where you can see if the engine change is beneficial or not (to current the work flow).

Really consider the vastly improved MySQL Workbench 6.2.4 as your assistant in this task (Oracle provides Community (free) and commercial editions). The Community edition (which I have a bit of personal experience with) helped me out of a situation with my mediawiki setup and showed me that its wizard for replicating MySQL databases is very competent while very easy to use.

The look and feel of MySQL Workbench has improved a lot, making it much(!) more useful than previous incarnations of this software. Now this software is practically similar to the software that comes with full-fledged database solutions such as those from Oracle and Microsoft.