Just joined the list because of this really bizarre problem. I'm using 9.2 on a laptop. I'll describe the basic structure of my database, and what's going on. If I leave out any important details, tell me, and I'll do my best to fill them in. I am using the default mysql rpm that came with 9.2 + any updates that YAST told me to do. The only thing not stock on this machine is the upgraded KDE (3.4) that I'm using, but I don't think that has anything to do with this. I don have a couple of other programs installed, like tora, but I don't think they are the problem either. I have a database called tlp. Within TLP I have a number of actual tables. I can edit, update, delete, etc. all of the tables but one. For instance, if I go into the table "participants" via konqueror, and edit a row, everything works just fine. In fact, I do this several times a week as I update the information on my participants. But, enrollement is pretty stable. Now I need to update the information in one row in this table, and I can't. Tora gets errors, mysqlcc gets errors, konqueror, and OpenOffice.org all get errors. But, they don't get ANY errors on any other table in the database. The others work just like they should. This table used to work like it should. I can't figure out what I've done, or how to undo it. I have the mysql-administrator installed, and don't see anything to change with it, and I looked through the documentation with mysql, and tried to unlock my tables, thinking some program had thrown a lock on it, but no dice. I still can't update my table. If you have any ideas, suggestions, of kludges to get me up and running, I'd appreciate it! -- Alex Lowe's 3 Rules for Climbing: 1 - Come Back Alive! 2 - Come Back Still Friends With Your Partners! 3 - Get To The Top!
On Sunday 27 March 2005 09:32 pm, Andy Choens wrote:
konqueror, and OpenOffice.org all get errors.
Not to side track you from your quest for an answer... How do you access mysql thru Kong? -- _____________________________________ John Andersen
I usually use the Alt-F2 shortcut and I type in sql:/ You can also do this directly in the location bar. It works really nice. If you have mysql, postgresql, etc, make sure you have the proper QT connections or it won't work. --andy On Monday 28 March 2005 3:24 am, John Andersen wrote:
On Sunday 27 March 2005 09:32 pm, Andy Choens wrote:
konqueror, and OpenOffice.org all get errors.
Not to side track you from your quest for an answer... How do you access mysql thru Kong?
-- Alex Lowe's 3 Rules for Climbing: 1 - Come Back Alive! 2 - Come Back Still Friends With Your Partners! 3 - Get To The Top!
Sounds like a corrupt table. Run this command from the commandline as root: mysqlcheck -r -A Brad Dameron SeaTab Software www.seatab.com ----- Original Message ----- From: "Andy Choens" <andy.choens@gmail.com> To: <suse-linux-e@suse.com> Sent: Sunday, March 27, 2005 10:32 PM Subject: [SLE] MySQL Table Locked?
Just joined the list because of this really bizarre problem. I'm using 9.2 on a laptop. I'll describe the basic structure of my database, and what's going on. If I leave out any important details, tell me, and I'll do my best to fill them in.
I am using the default mysql rpm that came with 9.2 + any updates that YAST told me to do. The only thing not stock on this machine is the upgraded KDE (3.4) that I'm using, but I don't think that has anything to do with this. I don have a couple of other programs installed, like tora, but I don't think they are the problem either.
I have a database called tlp. Within TLP I have a number of actual tables. I can edit, update, delete, etc. all of the tables but one. For instance, if I go into the table "participants" via konqueror, and edit a row, everything works just fine. In fact, I do this several times a week as I update the information on my participants. But, enrollement is pretty stable. Now I need to update the information in one row in this table, and I can't. Tora gets errors, mysqlcc gets errors, konqueror, and OpenOffice.org all get errors. But, they don't get ANY errors on any other table in the database. The others work just like they should. This table used to work like it should. I can't figure out what I've done, or how to undo it.
I have the mysql-administrator installed, and don't see anything to change with it, and I looked through the documentation with mysql, and tried to unlock my tables, thinking some program had thrown a lock on it, but no dice. I still can't update my table.
If you have any ideas, suggestions, of kludges to get me up and running, I'd appreciate it! -- Alex Lowe's 3 Rules for Climbing: 1 - Come Back Alive! 2 - Come Back Still Friends With Your Partners! 3 - Get To The Top!
-- Check the headers for your unsubscription address For additional commands send e-mail to suse-linux-e-help@suse.com Also check the archives at http://lists.suse.com Please read the FAQs: suse-linux-e-faq@suse.com
I gave it a try and I got this as a return. mysql.columns_priv OK mysql.db OK mysql.func OK mysql.host OK mysql.tables_priv OK mysql.user OK mysql.user_info OK tlp.adults OK tlp.campers OK I tried to edit the table again, and same result. --andy On Monday 28 March 2005 3:53 am, Brad Dameron wrote:
Sounds like a corrupt table. Run this command from the commandline as root:
mysqlcheck -r -A
Brad Dameron SeaTab Software www.seatab.com
----- Original Message ----- From: "Andy Choens" <andy.choens@gmail.com> To: <suse-linux-e@suse.com> Sent: Sunday, March 27, 2005 10:32 PM Subject: [SLE] MySQL Table Locked?
Just joined the list because of this really bizarre problem. I'm using 9.2 on a laptop. I'll describe the basic structure of my database, and what's going on. If I leave out any important details, tell me, and I'll do my best to fill them in.
I am using the default mysql rpm that came with 9.2 + any updates that YAST told me to do. The only thing not stock on this machine is the upgraded KDE (3.4) that I'm using, but I don't think that has anything to do with this. I don have a couple of other programs installed, like tora, but I don't think they are the problem either.
I have a database called tlp. Within TLP I have a number of actual tables. I can edit, update, delete, etc. all of the tables but one. For instance, if I go into the table "participants" via konqueror, and edit a row, everything works just fine. In fact, I do this several times a week as I update the information on my participants. But, enrollement is pretty stable. Now I need to update the information in one row in this table, and I can't. Tora gets errors, mysqlcc gets errors, konqueror, and OpenOffice.org all get errors. But, they don't get ANY errors on any other table in the database. The others work just like they should. This table used to work like it should. I can't figure out what I've done, or how to undo it.
I have the mysql-administrator installed, and don't see anything to change with it, and I looked through the documentation with mysql, and tried to unlock my tables, thinking some program had thrown a lock on it, but no dice. I still can't update my table.
If you have any ideas, suggestions, of kludges to get me up and running, I'd appreciate it! -- Alex Lowe's 3 Rules for Climbing: 1 - Come Back Alive! 2 - Come Back Still Friends With Your Partners! 3 - Get To The Top!
-- Check the headers for your unsubscription address For additional commands send e-mail to suse-linux-e-help@suse.com Also check the archives at http://lists.suse.com Please read the FAQs: suse-linux-e-faq@suse.com
-- Alex Lowe's 3 Rules for Climbing: 1 - Come Back Alive! 2 - Come Back Still Friends With Your Partners! 3 - Get To The Top!
On Monday 28 March 2005 3:53 am, Brad Dameron wrote:
Sounds like a corrupt table. Run this command from the commandline as root:
mysqlcheck -r -A
Didn't help. Mysql seems to think it is fine. In fact, after thinking about it some more, I decided to try backing up the table and restoring it. In this schema I have 4 tables. The other 3 are working just fine. So, I made a sql back-up of the one table that's not working correctly. Then, I dropped the table. After that I resored my back-up. That went very smoothly, but the table isn't acting right. For instance, if I use konqui to edit the table, when I try to go and edit the contents of a row, I get this error: SQL KIOSlave: An error has occured Could not execute query. Could not access adults You might suggest my permissions aren't correct, but I can access the other tables just fine, so my permissions are fine and so is my connection between QT and mysql. I've also tried logging into mysql as root, and I get the same results. Using mysqladministrator, I renamed the table adults to contacts, but that didn't help either, so I changed it's name back. It let me change it's name. To see if it means anything to anybody, I've inserted the SQL that I got from the back-up. I had to not include the data itself, sorry. I can't post that on the internet! --andy SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS; SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION; SET NAMES utf8; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=NO_AUTO_VALUE_ON_ZERO */; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tlp`; USE `tlp`; CREATE TABLE `adults` ( `parent_number` int(11) NOT NULL auto_increment, `camper_number` int(3) NOT NULL default '0', `camper_number_2` int(3) default '0', `primary_contact` char(3) NOT NULL default '1', `mail` char(3) NOT NULL default '0', `role` varchar(11) default '', `first_name` varchar(50) default NULL, `middle_name` varchar(20) default NULL, `last_name` varchar(20) NOT NULL default '', `cellphone` varchar(20) default '', `homephone` varchar(20) NOT NULL default '', `workphone` varchar(20) default '', `address_1` varchar(50) NOT NULL default '', `address_2` varchar(20) default '', `city` varchar(20) NOT NULL default '', `state` char(2) NOT NULL default '', `zip` varchar(11) NOT NULL default '', `email` varchar(100) default '', `notes` longtext, `email?` int(11) NOT NULL default '0', PRIMARY KEY (`parent_number`) ) TYPE=MyISAM; INSERT INTO `adults` VALUES ........I had to cut this out. I hope this helps someone help me. If it doesn't I'm going to go join a mysql list next.
On Monday 28 March 2005 3:53 am, Brad Dameron wrote:
Sounds like a corrupt table. Run this command from the commandline as root:
mysqlcheck -r -A
Brad Dameron SeaTab Software www.seatab.com
In one last attempt to get this to work, I dropped the entire schema, and restored it. I get the exact same error pattern I have previously described. I'm going to try and see if anyone on a mysql list has any ideas....... --andy
The Monday 2005-03-28 at 16:37 -0500, Andy Choens wrote:
In one last attempt to get this to work, I dropped the entire schema, and restored it. I get the exact same error pattern I have previously described.
I'm going to try and see if anyone on a mysql list has any ideas.......
I know very little about this, but when I created a table I found that I could not have a field named "index", I had a lot of problems and had to rename it to "indice" instead. I was using rekall and mysql (suse 9.1). It is just a wild guess, change fields names. Who knows, it might work :-) -- Cheers, Carlos Robinson
On Monday 28 March 2005 8:21 pm, Carlos E. R. wrote:
The Monday 2005-03-28 at 16:37 -0500, Andy Choens wrote:
In one last attempt to get this to work, I dropped the entire schema, and restored it. I get the exact same error pattern I have previously described.
I'm going to try and see if anyone on a mysql list has any ideas.......
I know very little about this, but when I created a table I found that I could not have a field named "index", I had a lot of problems and had to rename it to "indice" instead. I was using rekall and mysql (suse 9.1).
It is just a wild guess, change fields names. Who knows, it might work :-)
-- Cheers, Carlos Robinson
Thanks, but I don't have any fields named index. What's confusing is how is USED to work. Something has changed somewhere else. I didn't change the table, it just quit working. --andy -- Alex Lowe's 3 Rules for Climbing: 1 - Come Back Alive! 2 - Come Back Still Friends With Your Partners! 3 - Get To The Top!
On Mon, 2005-03-28 at 21:46 -0500, Andy Choens wrote:
Thanks, but I don't have any fields named index. What's confusing is how is USED to work. Something has changed somewhere else. I didn't change the table, it just quit working. --andy
I am not a DBA but using webmin to connect to mysql there is an icon for table permissions that looks like you can change permissions for individual tables. Maybe something has go wrong there. Just a suggestion for something else to try before you pull all of your hair out. -- Ken Schneider UNIX since 1989, linux since 1994, SuSE since 1998 * Only reply to the list please* "The day Microsoft makes something that doesn't suck is probably the day they start making vacuum cleaners." -Ernst Jan Plugge
I am not a DBA but using webmin to connect to mysql there is an icon for table permissions that looks like you can change permissions for individual tables. Maybe something has go wrong there. Just a suggestion for something else to try before you pull all of your hair out.
Well, I downloaded webmin and set it up. So far so good. It's not the interface I would like, but I don't seem to get any errors this way. Of course, I'm editing the table as root, so it had better let me do what ever the heck I want! :-) I'm going to install usermin and see if that helps any. If it does, at least I'll be able to use webmin while I figure out the root of my difficulty. It certainly gives my back access to my database, which is incredibly nice. Thanks for the idea. I'm going play around with these permissions more. --andy
The Tuesday 2005-03-29 at 00:09 -0500, Andy Choens wrote:
I'm going to install usermin and see if that helps any. If it does, at least I'll be able to use webmin while I figure out the root of my difficulty. It certainly gives my back access to my database, which is incredibly nice.
You can try also "rekall" to access your database. That's what I use. -- Cheers, Carlos Robinson
Andy, Presuming that you've already tried the UNLOCK TABLES-command and it didn't help... I've seen similar problems (w/ 4.0.18). Found out this: - If you make a complete copy of the database, eg. database 'data1' to 'data2' the new database ('data2') is not locked. (which proves that the data itself is okay, I think) - If you then remove the old database ('data1') and rename 'data2' to 'data1' it stops working, => locked. - If you simply rename 'data1' to 'data2' it works. So there must be a 'flag' or 'tag' or something in the mysql (system) database that tells MySQL that the database is locked. Now, what I didn't try was the following sequence: - Copy 'data1' to 'data2' - DROP DATABASE data1; - rename 'data2' to 'data1' I intended to do a little more research in MySQL's own database mysql, but I stupidly first upgraded to 4.1 - only to find out that the old database was 'unlocked' again. (Guess that something in mysql got overwritten) I do not think that 4.1 fixed it, only that it cleared the 'tag', so I'm waiting for the problem appear again, and then I'll try to catch it. Marty On Tuesday 29 March 2005 08:09, Andy Choens wrote:
I am not a DBA but using webmin to connect to mysql there is an icon for table permissions that looks like you can change permissions for individual tables. Maybe something has go wrong there. Just a suggestion for something else to try before you pull all of your hair out.
Well, I downloaded webmin and set it up. So far so good. It's not the interface I would like, but I don't seem to get any errors this way. Of course, I'm editing the table as root, so it had better let me do what ever the heck I want! :-)
I'm going to install usermin and see if that helps any. If it does, at least I'll be able to use webmin while I figure out the root of my difficulty. It certainly gives my back access to my database, which is incredibly nice.
Thanks for the idea. I'm going play around with these permissions more.
--andy
I think upgrading may be the route I go as well. Yeah, I tried unlocking. No dice. Here's what I tried. I backed up ALL of the tables in the schema called TLP. I dropped the tables and then I dropped the schema. After that, I reset mysqld, just to make sure it had cleared it's memory. I then used the sql file to restore my schema, with all of the tables included. This worked just fine. However, I seem to only have read access on the table adults. I've checked the table for errors, and mysql says it is fine. I can edit all of the other tables via the KIO slave as well as via ODBC, so I know those are set up and functioning correctly. OOo can't edit the table, Tora gets error messages, etc. I can rename it though, which I found odd, even though that doesn't seem to change it's behavior (It's still locked). Oddly enough, on the suggestion of someone else on the list, I tried webmin, and this tool seems to be able to edit my database just fine, as user achoens OR root. I don't know why, but right now, webmin is the only way I can change the data in this table. This is a BIG thanks to the folks over at webmin. --andy On Tue, 29 Mar 2005 20:11:37 +0300, Onemarty <marty.one@pp.inet.fi> wrote:
Andy,
Presuming that you've already tried the UNLOCK TABLES-command and it didn't
help...
I've seen similar problems (w/ 4.0.18). Found out this:
- If you make a complete copy of the database, eg. database 'data1' to 'data2' the new database ('data2') is not locked. (which proves that the data itself
is okay, I think) - If you then remove the old database ('data1') and rename 'data2' to 'data1' it stops working, => locked. - If you simply rename 'data1' to 'data2' it works.
So there must be a 'flag' or 'tag' or something in the mysql (system) database that tells MySQL that the database is locked.
Now, what I didn't try was the following sequence: - Copy 'data1' to 'data2' - DROP DATABASE data1; - rename 'data2' to 'data1'
I intended to do a little more research in MySQL's own database mysql, but I stupidly first upgraded to 4.1 - only to find out that the old database was 'unlocked' again. (Guess that something in mysql got overwritten)
I do not think that 4.1 fixed it, only that it cleared the 'tag', so I'm waiting for the problem appear again, and then I'll try to catch it.
Marty
On Tuesday 29 March 2005 08:09, Andy Choens wrote:
I am not a DBA but using webmin to connect to mysql there is an icon for table permissions that looks like you can change permissions for individual tables. Maybe something has go wrong there. Just a suggestion for something else to try before you pull all of your hair out.
Well, I downloaded webmin and set it up. So far so good. It's not the interface I would like, but I don't seem to get any errors this way. Of course, I'm editing the table as root, so it had better let me do what ever the heck I want! :-)
I'm going to install usermin and see if that helps any. If it does, at least I'll be able to use webmin while I figure out the root of my difficulty. It certainly gives my back access to my database, which is incredibly nice.
Thanks for the idea. I'm going play around with these permissions more.
--andy
-- Check the headers for your unsubscription address For additional commands send e-mail to suse-linux-e-help@suse.com Also check the archives at http://lists.suse.com Please read the FAQs: suse-linux-e-faq@suse.com
participants (7)
-
Andrew Choens
-
Andy Choens
-
Brad Dameron
-
Carlos E. R.
-
John Andersen
-
Ken Schneider
-
Onemarty