The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL 4.1 and ISAM tables - Check Before Upgrading!

Discussion in 'General Discussion' started by PeteC, Mar 15, 2005.

  1. PeteC

    PeteC Well-Known Member

    Joined:
    May 8, 2003
    Messages:
    106
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Texas
    I've upgraded 8 servers to MySQL 4.1. Very smooth so far. Only a few Java sites had issues because the latest Connector/J enforces error conditions that the old one ignored, and those sites had some inappropriate coding that needed fixing.

    However, on server #8, I ran into a site that had ISAM tables in a database. MySQL 4.1 deprecates these, and 5.0 is going to drop support for them. It seems the cPanel install of MySQL 4.1 doesn't include ISAM support, so before upgrading to 4.1 on a server, it's a good idea to do this:

    1. See if you have any ISAM tables:

    cd /var/lib/mysql
    find -name *.IS?

    2. If you find any files with those names, then convert the tables to MYISAM before upgrading to MySQL 4.1:

    mysql
    \u name-of-database-with-isam-tables
    ALTER TABLE tbl_name TYPE = MYISAM;

    Of course, substitute your database name and table name where there's blue text. You have to repeat that for all ISAM tables. I only found one database on 10 servers with any ISAM tables, but it's best to check in advance.

    Here's a relevant page on the MySQL site:

    http://dev.mysql.com/doc/mysql/en/isam-storage-engine.html
     
  2. MagiCat

    MagiCat Registered

    Joined:
    Jul 19, 2004
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Obviously you've been pretty successful in performing this upgrade. Did you have to do anything else besides changing the radio button to 4.1 from 4.0 or did you have to do other stuff to get it to work correctly?
     
  3. steil

    steil Registered

    Joined:
    May 21, 2005
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    How did you fix the server with the isam tables? I've ran into the same problem and am unable to fix it. We've tried downgrading but ran into issues with mysql start again when we done that. On this server there are about 15 sites that all have isam tables.

    Did you manage to get 4.1 working with isam or something else? Any help you have to offer on this would be greatly appreicated.
     
  4. PeteC

    PeteC Well-Known Member

    Joined:
    May 8, 2003
    Messages:
    106
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Texas
    You probably won't like my solution, but here's what I think I did (working from memory, and this wasn't yesterday). I still had a server or two running 4.0. So I copied the tables to one of those servers, did the ALTER TABLE thing, then copied them back to the 4.1 server.

    Here's the catch. I copied the table files _MANUALLY_ from the 4.1 server to the 4.0 server. I didn't dump a .sql file on the 4.1 server then run it on the 4.0 server. I think the exact process went like this:

    1) Restore an old backup of the site with ISAM tables onto a server with MySQL 4.0. This creates the databases properly.

    2) Take the site with ISAM tables offline on the server with 4.1 (so that no database updates will occur).

    3) Copy the table files from the 4.1 server to the 4.0 server.

    4) Alter the tables on the 4.0 server, then dump them.

    5) Run the .sql file on the 4.1 server.

    I don't know if this is foolproof, but it seems to have worked in my situation. I run 10 servers and only had one site with ISAM tables, so I wasn't hit hard by this.
     
  5. PeteC

    PeteC Well-Known Member

    Joined:
    May 8, 2003
    Messages:
    106
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Texas
    This was the only real problem. I had a few customers with sloppy JSP code and it seems the driver for 4.1 is more picky so they had to fix their code. I also had a few customers reporting password problems (I believe 4.1 lengthened the password hash), but all of those worked things out without my help. Overall, the upgrade was smoother than I expected.
     
  6. steil

    steil Registered

    Joined:
    May 21, 2005
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Thanks, but we solved it anyways. What we ended up doing was copying all the folders out of the mysql directory. Then we uninstalled all the sql RPMs. Then we managed to reinstall sql without errors then. Once this was working we copied the database folders back in. Unfortunatly we ran into problems copying over the mysql folder so we had to recreate all the users an permissions but that was a small price to pay to get things working.
     
  7. haze

    haze Well-Known Member

    Joined:
    Dec 21, 2001
    Messages:
    1,550
    Likes Received:
    3
    Trophy Points:
    38
    A good way to check a server before upgrading is to insert "new" into the global my.cnf ( will probably also work on a user by user basis as well ) and then see if there are any reported problems. This way, any issues ( hopefully ) can be worked out via the clients before an upgrade is completed. The mysql doc's also have some very helpfull tips in their upgrade instructions.
     
  8. silversurfer

    silversurfer Well-Known Member

    Joined:
    Dec 29, 2002
    Messages:
    274
    Likes Received:
    0
    Trophy Points:
    18
    Is there any real compelling reason to upgrade to 4.1?
     
  9. haze

    haze Well-Known Member

    Joined:
    Dec 21, 2001
    Messages:
    1,550
    Likes Received:
    3
    Trophy Points:
    38
  10. silversurfer

    silversurfer Well-Known Member

    Joined:
    Dec 29, 2002
    Messages:
    274
    Likes Received:
    0
    Trophy Points:
    18
  11. gmagana

    gmagana Active Member

    Joined:
    May 18, 2005
    Messages:
    41
    Likes Received:
    0
    Trophy Points:
    6
    This is absolutely an eye of the beholder thing. Having come from an Oracle background, the more "enterprise" MySQL becomes the better. Nested queries fall into that "why didn't MySQL have it in there before?" category.

    Now speaking as a sysadmin, though, It's much easier to never upgrade anything that currently works.

    Speaking as a marketer, though, offering "outdated" service is no good. Giving the impression to your customers that you don't upgrade your systems to the current stable versions is also Not a Good Thing.

    So, it's not black and white. There's reasons to upgrade and there's reasons not to upgrade.
     
  12. haze

    haze Well-Known Member

    Joined:
    Dec 21, 2001
    Messages:
    1,550
    Likes Received:
    3
    Trophy Points:
    38
    You should at least start planning a migration plan if anything and start testing it out before hand to ensure a stable transition. As noted above, you will eventually need to upgrade, be it for features or security updates and the like.

    You may also soon start to notice clients and / or potential clients requesting the newer version as its features become more widely adopted by program developers, etc.
     
  13. silversurfer

    silversurfer Well-Known Member

    Joined:
    Dec 29, 2002
    Messages:
    274
    Likes Received:
    0
    Trophy Points:
    18
    I just tested on one server and it all went without a hitch. holding back has helped as we had watched all the potential issues and problems on the migration from the other threads closely for months as well as to let Cpanel iron out the issues. So we managed to skirt around any potential problem so far.

    I will do more testing and probably upgrade soon on all the rest of the servers.
     
  14. silversurfer

    silversurfer Well-Known Member

    Joined:
    Dec 29, 2002
    Messages:
    274
    Likes Received:
    0
    Trophy Points:
    18
    Is there any thing to watch out for if you have DBs in Greek and Arabic etc?

    I read that there's a lot of problems with the databases being totally unreadable.
     
Loading...

Share This Page