Re: InnoDB issues - tables not found
Tomasz,
are you running on Windows?
Please use innodb_table_monitor as explained in section 9.1 of
http://www.innodb.com/ibman.html, and also look into section 15.1.
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
Speed up adding of features to MySQL/InnoDB through support contracts
See http://www.innodb.com for the online manual and latest news on InnoDB
-Original Message-
From: Tomasz Korycki [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Date: Tuesday, March 05, 2002 10:30 AM
Subject: InnoDB issues - tables not found
Hi,
Newbie here, so please be kind...
I decided to try out the foreign keys (REFERENCES tabel(column) in CREATE
TABLE) and I hit two problems. Maybe it's my clumsiness with search
specification, but I couldn't find answers in the archive. Oh, I tried it
on InnoDB tables created just for this test. So here goes:
First problem: SHOW CREATE does _not_ use the same syntax I did when
creating tables - it omits REFERENCES... completely. This is not a big
deal, I can just add those in the scripts if I need them, but it is
somewhat worrisome;
Second problem - and this is a big one - after I looked around in those
tables, inserting, selecting and updating data, I disconnected from the DB,
disconnected from the server and shut down the client. Until then I _could_
see the table structures, the fact they were InnoDB type, column
definitions, and so on. I did also see free space and so on. When I came
back after dinner and connected back... no such luck. Any attempt to get
any rows out (like SELECT * FROM `table_name`) gives me:
[FLOW11 as tomek] ERROR 1016: Can't open file: 'table_name.InnoDB'. (errno:
1)
I can't see structure, number of rows, free space, table type, size,
nothing. Logged out and in using different (sufficiently privileged) names
- no cigar. Looked through the hostname.err log - nothing there, as well.
Disconnected the client, shut down the MySQL - nothing seems wrong. It
started back without complaint as well - but I still can't see my tables...
I checked mysql database -everything is working as expected, things show
up...
In a way I hope it is a FAQ, in which case I would appreciate a gentle
nudge. In case it's not, though, Is there something glaring I did/didn't
do?
MySQL is 4.0.1-alpha-log on Linux 2.2.17 installed from RPM downloaded from
MySQL site. Here is how I created one of the tables in question (in a new
database):
CREATE TABLE `ALBUM` (
`Ix` int(11) NOT NULL auto_increment,
`Title` varchar(255) binary default NULL,
`Released` date default NULL,
`Billing` int(11) REFERENCES PERFORMER(Ix),
`Tracks` int(11) REFERENCES TRACK(Ix),
`Remarks` blob,
`Live` enum('Live','Studio','Both','Unknown') default 'Unknown',
`Compilation` enum('Yes','No') default No,
`RecordedFrom` date default NULL,
`RecordedTo` date default NULL,
`Company` varchar(255) binary default NULL,
`ID` tinytext,
PRIMARY KEY (`Ix`)
) TYPE=InnoDB;
The rest of them were similar.
Speak up, sages, I'm hanging on Your... well, fingers, in this case ;)
--
sql, query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB issues - tables not found
At 04:18 2002-03-05, you wrote:
Tomasz,
are you running on Windows?
No, Linux 2.2.17
Please use innodb_table_monitor as explained in section 9.1 of
http://www.innodb.com/ibman.html, and also look into section 15.1.
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
Speed up adding of features to MySQL/InnoDB through support contracts
See http://www.innodb.com for the online manual and latest news on InnoDB
-Original Message-
From: Tomasz Korycki [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Date: Tuesday, March 05, 2002 10:30 AM
Subject: InnoDB issues - tables not found
Hi,
Newbie here, so please be kind...
I decided to try out the foreign keys (REFERENCES tabel(column) in CREATE
TABLE) and I hit two problems. Maybe it's my clumsiness with search
specification, but I couldn't find answers in the archive. Oh, I tried it
on InnoDB tables created just for this test. So here goes:
First problem: SHOW CREATE does _not_ use the same syntax I did when
creating tables - it omits REFERENCES... completely. This is not a big
deal, I can just add those in the scripts if I need them, but it is
somewhat worrisome;
Second problem - and this is a big one - after I looked around in those
tables, inserting, selecting and updating data, I disconnected from the DB,
disconnected from the server and shut down the client. Until then I _could_
see the table structures, the fact they were InnoDB type, column
definitions, and so on. I did also see free space and so on. When I came
back after dinner and connected back... no such luck. Any attempt to get
any rows out (like SELECT * FROM `table_name`) gives me:
[FLOW11 as tomek] ERROR 1016: Can't open file: 'table_name.InnoDB'. (errno:
1)
I can't see structure, number of rows, free space, table type, size,
nothing. Logged out and in using different (sufficiently privileged) names
- no cigar. Looked through the hostname.err log - nothing there, as well.
Disconnected the client, shut down the MySQL - nothing seems wrong. It
started back without complaint as well - but I still can't see my tables...
I checked mysql database -everything is working as expected, things show
up...
In a way I hope it is a FAQ, in which case I would appreciate a gentle
nudge. In case it's not, though, Is there something glaring I did/didn't
do?
MySQL is 4.0.1-alpha-log on Linux 2.2.17 installed from RPM downloaded from
MySQL site. Here is how I created one of the tables in question (in a new
database):
CREATE TABLE `ALBUM` (
`Ix` int(11) NOT NULL auto_increment,
`Title` varchar(255) binary default NULL,
`Released` date default NULL,
`Billing` int(11) REFERENCES PERFORMER(Ix),
`Tracks` int(11) REFERENCES TRACK(Ix),
`Remarks` blob,
`Live` enum('Live','Studio','Both','Unknown') default 'Unknown',
`Compilation` enum('Yes','No') default No,
`RecordedFrom` date default NULL,
`RecordedTo` date default NULL,
`Company` varchar(255) binary default NULL,
`ID` tinytext,
PRIMARY KEY (`Ix`)
) TYPE=InnoDB;
The rest of them were similar.
Speak up, sages, I'm hanging on Your... well, fingers, in this case ;)
--
sql, query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
sql, query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB issues - tables not found
At 04:18 2002-03-05, you wrote: Tomasz, are you running on Windows? Please use innodb_table_monitor as explained in section 9.1 of http://www.innodb.com/ibman.html, and also look into section 15.1. Best regards, Re: 9.1: Well, can't find innodb* anywhere on the system Re: 15.1: The second problem seems to be mine, the one about orphaned' .frm files. That would seem to account for me seeing a tables list, but nothing about them. Obviously, the .frm files did exist. Couldn't drop the tables or the database (Unknown table ...,...,...). Let me repeat: everything was just fine until I quit mysql. When I logged back in - with the same command, I use ksh and 'set -o vi' - that's when the issues started. I got out of this trouble by shutting down the server, manually removing all InnoDB files (including .frm), then starting it again - which created the IDB files. I then used my script to create database and all the tables. And everything was fine until I logged out. I now logged back in and everything is still OK: I can desc tables, and so on. Just one issue: when I do SHOW CREATE ..., it still doesn't show any REFERENCES ... that I had when I created the tables. Either I'm exceptionally thick, or the docs really say that InnoDB has the capability to use the foreign keys... I took special care to reference only tables that already existed, in case it's a concern. Any idea? Also about how long will the tables stay healthy? I don't care about those 5 records I put in each, but to really test, I need to put a bit more... snip! THX! -- sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
