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.

januzi

Counsellor

• Join Date: Dec 2008
• Posts: 113

---

#2

04-21-2009, 04:33 PM

Try restarting mysql.

---

Comment

Cancel Post

thornton

New Member

• Join Date: Apr 2009
• Posts: 4

---

#3

04-21-2009, 07:03 PM

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

Cancel Post

thornton

New Member

• Join Date: Apr 2009
• Posts: 4

---

#4

04-21-2009, 07:10 PM

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

Cancel Post

sterin71

Counsellor

• Join Date: Apr 2009
• Posts: 108

---

#5

04-22-2009, 11:06 AM

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

Cancel Post

thornton

New Member

• Join Date: Apr 2009
• Posts: 4

---

#6

04-22-2009, 12:02 PM

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

Cancel Post

sterin71

Counsellor

• Join Date: Apr 2009
• Posts: 108

---

#7

04-22-2009, 12:37 PM

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

Cancel Post

thornton

New Member

• Join Date: Apr 2009
• Posts: 4

---

#8

04-22-2009, 01:39 PM

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

Cancel Post

thornton

New Member

• Join Date: Apr 2009
• Posts: 4

---

#9

04-22-2009, 02:26 PM

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

Cancel Post

januzi

Counsellor

• Join Date: Dec 2008
• Posts: 113

---

#10

04-22-2009, 02:51 PM

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

Cancel Post

thornton

New Member

• Join Date: Apr 2009
• Posts: 4

---

#11

04-22-2009, 03:17 PM

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

Cancel Post

januzi

Counsellor

• Join Date: Dec 2008
• Posts: 113

---

#12

04-22-2009, 04:01 PM

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

Cancel Post

thornton

New Member

• Join Date: Apr 2009
• Posts: 4

---

#13

04-22-2009, 04:28 PM

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

Cancel Post

sterin71

Counsellor

• Join Date: Apr 2009
• Posts: 108

---

#14

04-22-2009, 04:30 PM

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.