GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL on Unix ERROR 1036 (HY000): Table is read only

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL on Unix ERROR 1036 (HY000): Table is read only

    I zipped up a database on one server and copied it to another(both Unix). Then i unzipped the files and placed them in the correct database. Now when I go to the MySQL DB on the second server and write a select statement it works, but I am not able to do an insert/update/delete. I see this error:
    ERROR 1036 (HY000): Table is read only

    I tried running chmod 755 on all the files in the database, without success. Any suggestions? Thanks in advance.

  • #2
    Try restarting mysql.

    Comment


    • #3
      Thanks for the advice januzi. How do i restart MySQL? DO I need to restart the Unix server or can I just restart MySQL from the command line. Please excuse my ignorance, as I am new to Unix environment.

      Comment


      • #4
        Since its a prod DB, I need to do this sometime tonight. I will try >su
        > cd /etc/init.d
        >./mysql stop
        >./mysql start

        Comment


        • #5
          Apart from:
          chmod 755

          Have you checked that the files are owned by mysql?

          Otherwise you should run:
          chown -R mysql /your/mysql/datadir

          Comment


          • #6
            Thanks for all the help guys, I tried chmod 777 and I tried chown but it doesnt work. I am not sure if I can restart MySQL, as this is a production database, and I do not want to break anything. Any other ideas?

            Comment


            • #7
              So you copied the database without stopping the MySQL server?
              Then the bets are pretty much off.

              The only thing I can think of then is the:
              FLUSH TABLES;

              Which will force a close on all open tables, but at that point a restart on the mysql server is just as good.

              May I ask why you are playing around with copying tables like this into a production server?
              Are they very huge or is there some other reason why you don't use a SQL dump of the table and recreate it on the production server?

              Comment


              • #8
                The database exists on the server, I copied a few tables over from another server(using tar) and placed them in the database folder. I did this without stopping the MySQL server.

                It's a small company and our Unix admin left so I have to take care of this stuff for a while. They are actually very small tables less than a 1MB

                So could you tell me what the impact of restarting MySQL would be? And is there any workaround?

                Comment


                • #9
                  And yes, I tried mysqldump but I keep getting an error that says
                  mysqldump:command not found.
                  I am running this from the command line and not from inside MySQL and I am running it as a superuser. I also tried running it after navigating to the directory where I have mysql and also from mysql/bin where I can see mysqldump

                  Comment


                  • #10
                    Is there phpmyadmin ? As root create new database, create user with full rights to that database. Log in phpmyadmin as that user and import dumped tables.

                    Comment


                    • #11
                      I do not have phpmyadmin on my machine. I am not sure if its present on the server. Could you be more specific, or point me to link where this is explained clearly. Thank you. Also I need to contact my IT dept to restart MySQL, which would take a week or so.

                      Comment


                      • #12
                        http://www.phpmyadmin.net/home_page/index.php
                        It needs http server with php+mysql(i)

                        As for console, You could:
                        mysql -u root -p
                        create database
                        create user for that database
                        exit from mysql console
                        mysql -u root -p dbname < dbname.sql

                        Comment


                        • #13
                          I do not have a sql dump file with a .sql extension. I zipped the files inside the DB on one server and copied them to another and placed them inside a DB folder of the same name as on the first server.
                          So I already have a DB and a user for it, I granted all rights to the user too. I am able to select but not able to insert/update/delete onthe tables I copied over from the other server. I am able to insert/update/delete from the tables I created freshly in the database.

                          Comment


                          • #14
                          • thornton wrote on Wed, 22 April 2009 21:26

                            And yes, I tried mysqldump but I keep getting an error that says
                            mysqldump:command not found.
                            I am running this from the command line and not from inside MySQL and I am running it as a superuser. I also tried running it after navigating to the directory where I have mysql and also from mysql/bin where I can see mysqldump

                            If you are in the mysql/bin directory you have to write:

                            ./mysqldump -uroot -p yourDatabase yourTableName > yourDumpFile.sql

                            The important part is the ./ at the beginning.
                            Because in contrast to Windows the current directory is not part of the PATH in Unix.

                            Then on your new server:

                            ./mysql -uroot -p yourDatabase < yourDumpFile.sql


                            And you should be able to do this without restarting mysql.
                            Hopefully the DROP TABLE yourTable; command will still work, even though you can't insert/update to the table.

                            If this doesn't work you will have to restart the server.
                            The impact will most probably only be that the database is not available during the few moments it takes to restart it.
                            Quote:


                            So could you tell me what the impact of restarting MySQL would be? And is there any workaround?


                            The workarounds are the ones I have mentioned in my posts.

                            Comment


                            • #15
                              It didn't solve my problem completely, but thats awesome, i didn't find that tip anywhere. I tried the mysqldump with ./ and it worked. now I am able to take a sqldump of my DB. but when i got to the second server and try to restore it using
                              ./mysql -uroot -p yourDatabase < yourDumpFile.sql
                              it prints out a lot of data from the tables, but it doesnt actually cipy all the tables from the dump. I can still only see the existing tables and not the new ones. Any ideas?

                              Comment

                              Working...
                              X