Anytime i create an innodb table mysql connection drops. The frm file
gets created, but the table is reported as "in use" and can't be accessed.


CREATE TABLE test(
test int
) TYPE = innodb

MySQL said: Documentation
#2013 - Lost connection to MySQL server during query


Mysql seems to be configured to allow innodb tables:

Variable Session value Global value
have innodb YES YES


alex.

Try ENGINE=InnoDB instead of TYPE.


"alex" <me@privacy.net> schreef in bericht
news:44a8ec73$0$15790$14726298@news.sunsite.dk...
> Anytime i create an innodb table mysql connection drops. The frm file
> gets created, but the table is reported as "in use" and can't be accessed.
>
>
> CREATE TABLE test(
> test int
> ) TYPE = innodb
>
> MySQL said: Documentation
> #2013 - Lost connection to MySQL server during query
>
>
> Mysql seems to be configured to allow innodb tables:
>
> Variable Session value Global value
> have innodb YES YES
>
>
> alex.

En/na HH3 ha escrit:
> Try ENGINE=InnoDB instead of TYPE.

no joy

SQL query:
CREATE TABLE test(
test int
) ENGINE = innodb

MySQL said:
#2013 - Lost connection to MySQL server during query


it also fails in the same way when creating views.

mysql version is 5.0.22-max under linux.

this might be useful:


InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 399.
InnoDB: You may have to recover from a backup.
060703 16:58:22 InnoDB: Page dump in ascii and hex (16384 bytes):

(snip ... page dump)

;InnoDB: End of page dump
060703 16:58:22 InnoDB: Page checksum 3423916029, prior-to-4.0.14-form
checksum 265310224
InnoDB: stored checksum 2989763325, prior-to-4.0.14-form stored checksum
265310224
InnoDB: Page lsn 0 43826461, low 4 bytes of lsn at page end 43826461
InnoDB: Page number (if stored to page already) 399,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an 'inode' page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 399.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

Number of processes running now: 0
060703 16:58:22 mysqld restarted
060703 16:58:23 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060703 16:58:23 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43842364.
InnoDB: Doing recovery: scanned up to log sequence number 0 43842364
InnoDB: Last MySQL binlog file position 0 0, file name
060703 16:58:23 InnoDB: Started; log sequence number 0 43842364
060703 16:58:23 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.22-max' socket: '/tmp/mysql.sock' port: 3306 MySQL
Community Edition - Experimental (GPL)

alex <me@privacy.net> wrote:

> Anytime i create an innodb table mysql connection drops. The frm file
> gets created, but the table is reported as "in use" and can't be accessed.
>
> CREATE TABLE test(
> test int
> ) TYPE = innodb
>
> MySQL said: Documentation
> #2013 - Lost connection to MySQL server during query

Your MySQL server is crashing. There can be many reasons.
Please have a look at MySQLs error log.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

alex <me@privacy.net> wrote:
> this might be useful:
>
> InnoDB: Database page corruption on disk or a failed
> InnoDB: file read of page 399.
> InnoDB: You may have to recover from a backup.

Well, your InnoDB table space is corrupted. Probably you have a serious
problem with either your hardware (disks) or software (filesystem bug).
First of all you should find and fix this problem. Have a look into
your syslog for hard disk problems.

> InnoDB: If the corrupt page is an index page
> InnoDB: you can also try to fix the corruption
> InnoDB: by dumping, dropping, and reimporting
> InnoDB: the corrupt table. You can use CHECK
> InnoDB: TABLE to scan your table for corruption.
> InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
> InnoDB: about forcing recovery.

There is not much to add. If you have a backup of your InnoDB tables
you should remove the table space(s) and start with a fresh one.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

En/na Axel Schwenke ha escrit:
> Well, your InnoDB table space is corrupted. Probably you have a serious
> problem with either your hardware (disks) or software (filesystem bug).
> First of all you should find and fix this problem. Have a look into
> your syslog for hard disk problems.

I finally got innodb working again... I just had to begin with a clean
mysql installation.. (i was installing mysql OVER my previous mysql
installation to keep data files etc). Disks seem to be ok though.

I copied the corrupted database but the tables are still reported as "IN
USE". But i can now create views and innodb tables without problems. At
least I managed to recover about the 90% of my data (and lost data isn't
relevant).

thanks,
alex.

alex wrote:
> En/na Axel Schwenke ha escrit:
>
>> Well, your InnoDB table space is corrupted. Probably you have a serious
>> problem with either your hardware (disks) or software (filesystem bug).
>> First of all you should find and fix this problem. Have a look into
>> your syslog for hard disk problems.
>
>
> I finally got innodb working again... I just had to begin with a clean
> mysql installation.. (i was installing mysql OVER my previous mysql
> installation to keep data files etc). Disks seem to be ok though.
>
> I copied the corrupted database but the tables are still reported as "IN
> USE". But i can now create views and innodb tables without problems. At
> least I managed to recover about the 90% of my data (and lost data isn't
> relevant).
>
> thanks,
> alex.

Alex,

NEVER a good idea! There is no guarantee the new database is 100% compatible with the older format.
MySQL tries to make it work, but it doesn't always work, as you noticed.

ALWAYS backup all your data (including MYSQL tables) before moving to a new version. Then you can
restore afterwards if necessary.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================