Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts

    Exclamation URGENT sql5.1 to 5.5 and ERROR 1146 (42S02): Table doesn't exist (not all tables)

    Hi guys

    i wrote a management system a couple of years ago that has been functioning perfectly well. that was until we got a new webserver.

    i installed the latest zendserver and it all looked good until i tried one section today and im getting an error

    "ERROR 1146 (42S02): Table 'dms.risks' doesn't exist" i get this with maybe 7 of the tables out of say 30. (i went through them all doing a DESCRIBE... tedious! )

    any ideas on how to fix? i havent looked at sql for a while now as its all been running fine. so i am very rusty with sql and there seems to be so many threads that are a bit over my head. i have the old server still sat here that i can plug in. i basically just backed up the whole zendserver folder and then copied the bits across, which im assuming is the wrong way to do things but its worked fine in the past.
    Last edited by babelfish; 06-17-2014 at 08:21 AM. Reason: resolved now
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #2
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,306
    Thanks
    4
    Thanked 205 Times in 202 Posts
    Did you check the case of the table names?
    Dave .... HostMonster for all of your hosting needs

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    yes, everything is lower case.

    having a look at the old MySQL51\data\dms folder i see the ones im having problems with in 5.5 only have a *.frm whereas the others have *.myd and *.myi could that be the problem?
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    ive just plugged in the old server. everything still working but same files and names. hmmmmm.

    helppppppp!!!!
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #5
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Simply copying the data folders can work if you're only dealing with MyISAM tables, but isn't a very good method especially when upgrading from 5.1 to 5.5 and it sounds like you missed some files in that copy process.

    Use the mysqldump tool to backup your databases. It will create the needed sql file(s) for you to move/restore the databases on the new server.

  • #6
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Before restoring the databases on the new server, you should verify that it supports the storage engines that the tables are using. That is done with the "show engines" statement.

    e.g.,
    Code:
    mysql> show engines;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)

  • #7
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    having a look at the old MySQL51\data\dms folder i see the ones im having problems with in 5.5 only have a *.frm whereas the others have *.myd and *.myi could that be the problem?
    I forgot to address this issue.

    If the database directory only has .frm files (one for each table), then most likely each of those tables are using the Innodb storage engine and you forgot to copy over the ibdata1 file to the new server. That file is located in the parent directory (i.e. the data directory).

  • Users who have thanked FishMonger for this post:

    babelfish (06-17-2014)

  • #8
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by FishMonger View Post
    I forgot to address this issue.

    If the database directory only has .frm files (one for each table), then most likely each of those tables are using the Innodb storage engine and you forgot to copy over the ibdata1 file to the new server. That file is located in the parent directory (i.e. the data directory).
    ah, that did it!

    i wonder why some dbs are Innodb and others are not. strange. i guess that was my first php/mysql app and i didnt really know what i was doing. cheers!
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson

  • #9
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    i wonder why some dbs are Innodb and others are not.
    Prior to v5.5 the default storage engine was MyISAM. Starting with v5.5 the default is InnoDB.

    InnoDB is preferred because it supports foreign key constraints and is ACID compliant.

    Comparison of MySQL database engines - Wikipedia, the free encyclopedia

  • #10
    Regular Coder
    Join Date
    Jun 2002
    Location
    England =)
    Posts
    523
    Thanks
    26
    Thanked 0 Times in 0 Posts
    thanks. strange though as ive always used the same zendserver framework with sql5.1. oh well. thanks anyway!
    "They hired me for my motivational skills. Everyone at work says they have to work much harder when I`m around" Homer J Simpson


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •