kiat has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks,

This has nothing to do with perl but with MySQL. Please bear with me :)

I created a new table yesterday and inserted some values from an old table to the new table (on a live server but it was unlikely for anybody to access the site during that time). The syntax used was:

INSERT INTO new_table (col1, col2) SELECT col1, col2 FROM old_table;

I did a SHOW TABLES and DESCRIBE new_table and verified that the new table was created and the values were inserted correctly.

I then accessed a page from the browser and everything looked fine - this page pulled out values from the new table.

Then I modified the specs of two columns of the new_table (I had done this before without problems). I didn't get any errors with the modifications.

After that, I accessed the page again from the broswer. This time round, the sql select didn't work. Curious, I did a SHOW TABLES and to my surprise, the new table was gone.

What could have happened? Have you encountered this sort of things before?

Replies are listed 'Best First'.
Re: OT: complete loss of a new table
by tachyon (Chancellor) on Oct 08, 2004 at 03:27 UTC

    What could have happened?

    You or someone else DROPed or RENAMEd the table? You USEd the wrong database?

    Have you encountered this sort of things before?

    No and to be realistic it is unlikely that you have uncovered a bug in MySQL. The spontanteous loss of tables is the kind of thing people notice. You can see if what is really there by looking at the raw data dir. A database is a directory. A table is 3 files within that directory. For example: 

    [root@devel3 mysql]# locate mysql/test
/var/lib/mysql/test
/var/lib/mysql/test/testac.frm
/var/lib/mysql/test/testac.MYI
/var/lib/mysql/test/testac.MYD
/var/lib/mysql/test/base.frm
/var/lib/mysql/test/base.MYI
/var/lib/mysql/test/base.MYD

    [download]

    My mysql data dir is /var/lib/mysql. The test database has two tables called 'testac' and 'base'. If by chance you can see the .frm, MYI and .MYD tables that correspond to your table check their perms. You could just do 'locate -u; locate missing_table.MYD

    cheers

    tachyon

[reply]
[d/l]
[select]
      Thanks, tachyon :)

      You or someone else DROPed or RENAMEd the table? You USEd the wrong database?
      Very unlikely as I'm the only one with access to the database.

      The database is hosted on another server. I am not quite sure if I can access that server to look at the database's directory. The server that's hosting the site ia shared server.

      I've since re-created the table and gone through basically the same steps as I did before. There's no problem the second round.

      But I'm puzzled what happened on my first attempt.

[reply]

        So just let me get this straight ;-) According to you:

        1. You think you are the only one with access to the database.
        2. The database is on another server
        3. It is a shared server
        4. You apparently don't have root on that server

        Kneel before me, for I am ROOT, and your data is mine to behold

        cheers

        tachyon

[reply]
Re: OT: complete loss of a new table
by Anonymous Monk on Oct 08, 2004 at 08:23 UTC
    I think the most likely thing to have occurred is that you made a mistake. You might have dropped the table by accident. Since the database is running on a server you don't seem to have otherwise access to, you probably can't look at your logfile (assuming there is even one). Without a log of all the exact actions you took, it's very hard for outsiders to tell what happened.
[reply]
      Thanks!

      I checked the history of commands. There wasn't a drop statement.

[reply]
Re: OT: complete loss of a new table
by dorko (Prior) on Oct 08, 2004 at 14:52 UTC
    For the sake of completeness in trouble shooting, did you COMMIT your work?

    Cheers,

    Brent

    -- Yeah, I'm a Delt.
[reply]
      I did it through the mysql client. Every executed statement is committed. Or am I missing something?
[reply]
Re: OT: complete loss of a new table
by Plankton (Vicar) on Oct 08, 2004 at 16:36 UTC
    Well you could of always restored your data if you had saved off a copy with mysqldump. You should use it until you figured out what caused your table to disappear.

    Snakehandlers.net
[reply]

Back to Seekers of Perl Wisdom