MYSQL :: INNODB Crashing

MySQL Crashing
I have a MySQL server running on Linux and Linux kills the process every few days with and out of memory error. I have 1 GB of RAM and 1 GB of swap space and no huge queries. No Joins or anything like that, just inserts, deletes, updates and selects on a series of pretty straightforward tables. I am accessing with PHP.

FULLTEXT Crashing Mysql 4.0.14
I'm setting up a database server on a Dell Poweredge 2650, dual 1.8GHZ pentium
with 1GB of memory and RAID5 drives. I've installed RedHat 9, and updated the
kernel to 2.4.20-19.9smp. I've installed the RPM binary distribution of MySQL
4.0.14 right from the MySQL website. The only thing I have done is disable
InnoDb in my my.cnf file.

Database Table:
I have a database with 21.4 million records. One field is a VARCHAR(255) field
on which I have created a FULLTEXT index.

Problem:
Most single word full text searches work perfectly and quickly. Occasionally,
when someone enters several words (without any operators), MySQL will crash
with the following in the error log (see below). This is totally consistent,
and MySQL will crash on the same submitted search text each time. Then other,
more complex terms will work. But I can make MySQL crash every time with the
query below in the error log. I've already rebuilt the fulltext index with
myisamchk, and there are no writes to this table at all - it's totally read
only.

I'm not sure what to do next - I don't want to have my database crashing every
hour when someone submits a valid search term that trips up the database.

Code:

FULLTEXT Crashing Mysql 4.0.14
Environment:
I'm setting up a database server on a Dell Poweredge 2650, dual 1.8GHZ pentium
with 1GB of memory and RAID5 drives. I've installed RedHat 9, and updated the
kernel to 2.4.20-19.9smp. I've installed the RPM binary distribution of MySQL
4.0.14 right from the MySQL website. The only thing I have done is disable
InnoDb in my my.cnf file.

Database Table:
I have a database with 21.4 million records. One field is a VARCHAR(255) field
on which I have created a FULLTEXT index.

Problem:
Most single word full text searches work perfectly and quickly. Occasionally,
when someone enters several words (without any operators), MySQL will crash
with the following in the error log (see below). This is totally consistent,
and MySQL will crash on the same submitted search text each time. Then other,
more complex terms will work.

But I can make MySQL crash every time with the
query below in the error log. I've already rebuilt the fulltext index with
myisamchk, and there are no writes to this table at all - it's totally read
only.

I'm not sure what to do next - I don't want to have my database crashing every
hour when someone submits a valid search term that trips up the database. Code:

FULLTEXT Crashing Mysql 4.0.14
Environment:

I'm setting up a database server on a Dell Poweredge 2650, dual 1.8GHZ pentium
with 1GB of memory and RAID5 drives. I've installed RedHat 9, and updated the
kernel to 2.4.20-19.9smp. I've installed the RPM binary distribution of MySQL
4.0.14 right from the MySQL website. The only thing I have done is disable
InnoDb in my my.cnf file.

Database Table:
I have a database with 21.4 million records. One field is a VARCHAR(255) field
on which I have created a FULLTEXT index.

Problem:
Most single word full text searches work perfectly and quickly. Occasionally,
when someone enters several words (without any operators), MySQL will crash
with the following in the error log (see below). This is totally consistent,
and MySQL will crash on the same submitted search text each time. Then other,
more complex terms will work. But I can make MySQL crash every time with the
query below in the error log. I've already rebuilt the fulltext index with
myisamchk, and there are no writes to this table at all - it's totally read
only.

I'm not sure what to do next - I don't want to have my database crashing every
hour when someone submits a valid search term that trips up the database.

Code:

MySQL Page Crashing
I have a simple ASP page, which uses GetRows to display the value of a fieldname within a DB. This page works with a mySQL db I have, which is only 10 records.

I have another database (same format), which contains 1.7 million records and doesn't work. The page sits there for a bit, then crashes, displaying a "page cannot be displayed" error.

How can I find out what's going on? Where is the problem occuring, etc.

Frequently Crashing MySQL Table
I have a table that keeps crashing, about 2-3 times a day. It starts returning crazy results from queries (ie, only 1 result for a simple select query, when there should be about 100), and I have to run a repair to fix it.

It's handling about 5 queries a second, non-stop, but that shouldn't be a problem, should it? Any ideas on what could cause frequent crashes like this?

Mysql Server Crashing All The Time
I run mysql on a win2003 machine. The database connects with a phpbb bulletin board and a gaming server. I get about 2 to 3 mysql server crashes every day since install, I have the latest version and have tried repairing and reinstalling mysql several times.

Is there a way of finding out what cause the crash (ie a crash log) with the ultimate aim of fixing my problem?

MySQL Table Crashing Randomly
I have a table that crashes randomly. It may go a week before crashing and other times it'll go minutes between crashes. Does mySQL keep a log of what events caused the crash? What can I do to track down the problem?

Help On Innodb
I have read few articles on innodb and quite confuse about those stuff. So said that you need to do some start up setting on innod buffer pool and etc. Where can I like get this list of things which need to be done on innodb. I need some optimization tips for innodb.

Innodb
I've always managed dependencies accross tables with a 'link' table:

Eg:

Users belong to Groups so I'd have a User2Groups linking table to manage the connection.

This works OK, but I have to manage the data integerity myself (if I delete a Group, I must also delete the record in the user2Groups table).

I've recently heard of innodb and the CASCADE features - allowing the db to reatain referential integrity.

Can someone please summarize the pros and cons of this - perhaps give reasons when to use and when not to use, and point me in the right direction for using innodb -

BDB Vs INNODB
Are there any big differences between using BDB and INNODB table types? I know they both support transactions and BDB is generally slower than INNODB. I'm talking more about the implementation rather than performance.

For instance, I developed this application which at the time I was writing it was for INNODB tables. Should the same app work fine using the BDB table type.

3.23.x To 4.0.x INNODB
Can I take multiple 2GB files in /var/lib/innodb and the corresponding
files in /var/lib/iblogs created with 3.32.55 (INNODB) and upgrade to
4.0.13 and let it use those existing files without a problem? Without
dumping and re-importing? I'd only need to additionally run the
mysql_privs_fix(?) script as part of a normal 3.23->4.0 upgrade?

I did this as a test and it appeared to work but only on a test
machine.. Want to make 100% certain before trying it on a production
box.

64-Bit And INNODB
With an Opteron (or Itanium for that matter) using let's say the RedHat 9 or
AS 3 betas available for those CPUs, how will MySQL/INNODB function?
Specifically does the ability to allocate a bigger chunk of memory than 2GB
just work "out of the box"? Can I set innodb_buffer_pool_size to 12GB for
instance if I have 16GB in a box?

Now then, along the same lines and not really MySQL oriented but someone
here will likely know. I see lots of Opteron boards with 2 CPUs being
advertised that have 4 DIMM slots "per CPU". Is it one large flat memory
space or do they do SMP in some strange fashion where each CPU has access to
it's own memory? Therefore not giving a flat 16GB memory space if 8 slots
were populated with 2GB DIMMs? I hope not but the way they're being
described it is somewhat confusing.

Even more off-topic, anyone know of a good 1U or 2U Opteron or Itanium
system with >8 DIMM slots? 64-bit is great but unless you can stuff a _lot_
of memory in it, a lot of the advantage is lost. I wish someone like
SuperMicro would offer a high end Opteron SuperServer. I love their dual
Xeon systems..

How To Set Up INNODB
I want MySQL to use INNODB tables.I created the tables using innodb as stated on mysql website but not having any luck. I am interested in maintaining referential integrity between primary and foreign keys..meaning..

"if i update the value in one table the other tables gets updated automatically too"..and if not wrong Mysql doesnt support this feature but innodb does.

so how i can basically setup MySQL to use INNODB on linux.

InnoDB
I am struggling a bit to get going with mysql and my tutorial works through InnoDB tables without a clear explanation of the setup.
To date I have modified the /etc/my.cnf file. mysqld starts without complaining. I have modified my.cnf to include:

innodb_data_file_path = /data/mysqldata/innodb_data/innodata:10M:autoextend
innodb_data_home_dir=

It turns out that logged in as a mysql user if I "use innodta' it mounts as a the database. So am I correct in assuming that identifying the tablespace name in innodb_data_file_path is the equivalent of 'create database <databaseName> with a MyISAM table?

Also once having 'use innodata' I can issue a create tables command and then see <tablename>.frm .MYD, and MYI listed in the innodb_data directory. However when <tblename> was created I could not use the 'engine = innodb; ' as the last line of the create table sql statement without getting an error. This suggests that the table that I made without 'engine=innodb' is just the usual MyISAM based table. Right? Wrong? Huh?
I think the documentation is a little lacking in this area as are two other MySQL books that I purchased

Specifying INNODB
Is it a bug or a feature that with the conditions

1. INNODB is not configured in my.cnf
2. Table type is specified as INNODB

that when you create the table there is no warning,
and no syntax error, and the table type silently
and Microsoft-like, defaults to MyISAM?

Now, if you specify table type as TYPE=bullshit,
you get a nice (and expected) syntax error.

Innodb
I have a table with approx. 150,000,000 ++ rows. It should be approx
80byte for each row. Now I'm planning to use it with mysql/innodb.
Can you guys suggest anything about this.
I have many questions... Can Innodb work well with such tables?
Will queries (select/update) be slow?
What hardware do you suggest, cpu/ram?

Innodb
I'm using mysql 5.0.22 as packaged (and updated) with Ubuntu Dapper.
I would like to use Innodb, but "show innodb status" says "ERROR 1235
(42000): Cannot call SHOW INNODB STATUS because skip-innodb is
defined". However, there is no skip-innodb in anywhere in
/etc/mysql/my.cnf. I found a file called /etc/mysql/debian.cnf which
appears to be used as a defaults file for behind-the-scenese stuff. It
doesn't mention skip-innodb. I've checked /etc/init.d/mysql and it has
no reference to skip-innodb.
I've googled the issue, and can't find anything. Am I missing
something?

No InnoDB
OS Fedora Core 3
I have been trying to set InnoDB engine on mysql 4.0 and 5.0 as default with no joy so far.
I have edited my.cnf file and put it in the datadir /usr/lib/.
Everytime I create a table the engine is MyIsam.

Myisam Or Innodb
Is there a way to show what type of a storeage engine a table is using from the mysql monitor?

The Age-old MyISAM Vs InnoDB
Okay, someone help me out here. I want to see some numbers supporting the omnipresent claim that MyISAM tables are faster than InnoDB tables. I don't want to hear "MyISAM is the best because it's faster"; Google gave me over 100,000 links to pages that say that. I want to see numbers, results from benchmarking. I'm well aware of the difference in features between the two engines (transactions, referential integrity, full-text indexing, etc), I'm simply interested in seeing support for the claims of speed superiority the MyISAM crowd are always quick to shout. I'll start:

http://www.mysqlperformanceblog.com/...hmarks-part-1/
This is the only set of data I could find comparing InnoDB and MyISAM, and shows in general InnoDB demonstrating superior performance over MyISAM. This test was done exclusively on SELECT statements, where MyISAM allegedly dominates InnoDB hands down.

In my own (admittedly limited) testing, again solely on SELECT statements using a mixture of indexed and non-indexed columns in WHERE, ORDER BY, and GROUP BY columns, and even tossing in a few aggregate functions just for fun, InnoDB and MyISAM were virtually neck-in-neck, with InnoDB never eeking out more than a 20% lead and MyISAM never returning faster at all. (Average InnoDB lead: 2.8%, statistically insignificant and putting it well within the realm of possibility that my observation that InnoDB was always faster was nothing more than a fluke.)

Innodb And Myisam
what are difference characteristics between inodb and myisam?

InnoDB Or MyISAM?
I'm having a tough time on how to choose a table type in my database design.

I am designing a multi-user web application and want to take advantage of both transactions and full text searching. However, as you know, I can only choose InnoDB for transactions or MyISAM for full text indexing/searching.

How do you guys go about deciding which to choose from? I've thought about using different types in different tables, to suit my needs. However, all data is dependent on each other. This means if I set table A to InnoDB, and table B to MyISAM, future transactions wouldn't work properly if I had to delete/update to both tables at once.

Innodb Or MyISAM ?
I have been reading a lot about Innodb and MyISAM recently. MyISAM offers speed whereas Innodb offers reliability. MyISAM is fast but with no transactions support, Innodb has certain, but very important limitations.

The InnoDB which makes it fast for INSERTs, UPDATEs and DELETEs while MyISAM offer the best performance for SELECTs.

so my doubt here which is best MyISAM or Innodb.Can I use Both engines in a
single table and How Innodb handles foreign key constraints and how Myisam handles fulltext indexes.if we apply both in a single table.what is the sysntax for doing like this

Innodb Vs Myisam
hi every one i am working on a database for a website, and just want to know how faster will be myisam from innodb if you please can tell me in percentage if you want to look at the design of the database i had it on this

Using Transactions With InnoDB
Right now I am experiencing with transactions in MySQL using the InnoDB engine.
I found a really great article which includes all about transactions:
http://dev.mysql.com/books/mysqlpres...rial/ch10.html

However, I still got a question and I couldn't find the answer exactly in this article.
A simple example:

UPDATE users SET money = money - 1000 WHERE id = 1 ;
UPDATE users SET money = money + 1000 WHERE id = 2 ;
I just want both queries executed OR both not executed, so I should use a transaction.
I thought the following would give me the right result, but it is not exactly what I want:

START TRANSACTION ;
UPDATE users SET money = money - 1000 WHERE id = 1 ;
UPDATE users SET money = money + 1000 WHERE id = 2 ;
COMMIT ;
If user 1 doesn't have 1000 dollar anymore, both queries shouldn't be executed.
The same for when the field name in the first query is wrong spelled or doesn't exist, both queries shouldn't be executed.

I read something about ROLLBACK in the mysql article on the mysql.com website and I think I can solve my problem with this one, but I don't know exactly how to use this.

The example in the article says:

START TRANSACTION ;
UPDATE users SET money = money - 1000 WHERE id = 1 ;
UPDATE users SET money = money + 1000 WHERE id = 2 ;
SELECT money FROM users WHERE id = 1 ;
ROLLBACK ;
But this one doesn't work for me. The ROLLBACK will always be executed, even when the user got enough money.

InnoDB Vs MyISAM
I wonder witch one is best? What is the differance? Witch one is faster in witch case?

I hope someone can take littel time to answer me about these two database motors.

MyIsam DB To InnoDB...
How do I convert my existing database, which I'm using MySQL ver 4 for Windows and MyISAM table type to store the data, to the new format – InnoDB, without problem (
losing data...)

Replication And InnoDB
I'd like to setup the following:

Server A <-- Server B <-- Server C
Data A some of Data A some of Data A
Data B Data B

Server A should be the master for server B. Server B should get not
all but some of the data of server A. Additionally there should be
other data stored on server B. Server B is slave for server A and
master for server C. Server C simply should get all the data of server
B.

No problem with MyISAM, but with InnoDB some questions arise:

The manual says, that one should shut down the master and copy the
InnoDB datafiles, logs and .frm files.

1.) If I only need some data of server A on server B wouldn't it be
overkill to copy all the InnoDB data?

2.) If I want to store other data on server B I can't mix up the
InnoDB datafiles and logs for data A and B, can I?

Innodb Build
When I installed MySQL awhile backe I somehow neglected to allow Innodb. Now when I try to create an Inno table it says that I have to build with Innodb. Do I need to completely uninstall MySQL and start over? I ran through the administrator configuration program and thought that that would do it but it didn't.

InnoDB Can't Use System's RAM As Specified
I am coming across an annoying problem with a new InnoDB installation.
On a 4Gig RAM box, Mysql doesn't seem to want to grab the RAM I am
telling it to take.
I would like for it to try and use around 3 Gigs for its
innodb_buffer_pool_size, but it refuses to startup with anything greater
than 512M. I bugged RedHat about it, and they had me change the shared
memory allowance to 3Gigs, but nothing changed. I'd really like to get
the pool size up so that the server doesn't have to go to disk so often.
I will admit to being an InnoDB newbie, I am reading everything I can
find though and have used the example my.cnf files to tweak it pretty
decently. This memory issue is all that is really bugging me at the
moment.

System Specs:
RedHat Enterprise ES 2.1 2.4.9-e27smp
MySQL 4.0.14
Compaq/HP Proliant 300 Series
Xeon Dual 2.8Ghz Pent4
4Gigs RAM
2 72Gig Scsi 320 drives Raid 1+0

InnoDB Corruption
My MySQL crashed this morning which lead me to look into my server to see what was up. Upon inspection it seems I may have made a mistake.

Back on June 25th 2006 I upgraded MySQL from 4.1.12 to 4.1.20 and I copied over the data folder (or so I thought) and got it up and running and happy. However when I looked today it seems that I may have not copied over the log files! The following error shows up in the error log:

060808 10:48:44 InnoDB: Error: page 531 log sequence number 0 266909086
InnoDB: is in the future! Current system log sequence number 0 62488108.
InnoDB: Your database may be corrupt.
060808 10:48:44 InnoDB: Error: page 532 log sequence number 0 266926873
InnoDB: is in the future! Current system log sequence number 0 62488108.
InnoDB: Your database may be corrupt.

a bunch of them!

I have a feeling that it is because I failed to drop in the log files after the upgrade.

What is the easiest way to remedy this situation? I do have the old log file. Is there anyway to append the two logs togehter?

Missing .myi And .myd Using INNODB
I just started this job and the previous IT Manager did not backup the mysql DB's -- It appears one crashed over the weekend and I have no idea where to begin. I do not want to do anything to make things worst. I did copy all of the .frm and lb* files to another directory for safe keeping. Can I recover from this? One of the lb files is huge, which leads me to believe it might be useful in a recovery.

InnoDb -v- MyISAM
I have three tables one of which is a ManyToMany table.

tbl_Groups
Group_ID | Group_Name

tbl_Premises
Premises_ID | Premises_Name

m2m_GroupsPremises
Group_ID | Premises_ID

can the two tbl's be made with MyISAM and the m2m be made with innoDb or must they all be innoDB? I wonder if only those with a foreign key need to be built with InnoDb.

Innodb & Locks
My Applications, that uses ADO/VB^ to communicate with a MYSQL db with Innodb tabes need to create row level locks.

I got this working by using the following code ( abridged from main application) .....

MyIsam -v- InnoDB
What is the view of the panel on this one? I suspect that there may be trade-offs but, I welcome your experience-based opinions.

InnoDB Vs. MyISAM
I'm making a database with about 20 tables, each once with many rows (ranging from 1000 to 100,000 from table to table) and it needs to be quick for updates and selects. I'm confident that I can arrange the tables and keys in a manner which provides me with good speed, however I cannot decide on which database to use.

I read some documentation that said that InnoDB was best for a web server (where the database is hosted) for transactions. However each user only ever performs a single action at a time (although many users are online simultaniously). The only times I need to lock and tables or records are for backups and maintenance, which I will do inside the user code anyway. With that information can anyone help me to determine which would be the best database, InnoDB or MyISAM?

Fulltext For InnoDB?
I was wondering, does anyone know if there are any plans to add FULLTEXT
indexes to the almighty InnoDB such that it catches up with the funky
MyISAM in terms of things you can pass to ALTER TABLE?

InnoDB Vs MyISAM
I'm looking at what tables to use in my database and am thinking innodb will be better as it allows foreign keys however I was just wondering how much faster myISAM tables are?

My table will probably have around 500,000 records...

Any thoughts / advice?

MyISAM Vs. InnoDB
I'm designing an event registration web application and have created a database on our UNIX web hosting server using MySQL 4.1. I need to join several tables using a registration ID.

My question: What is the best table design method to use - MyISAM or InnoDB?

Specifying INNODB Without Configuration
Is it a bug or a feature that with the conditions

1. INNODB is not configured in my.cnf
2. Table type is specified as INNODB

that when you create the table there is no warning,
and no syntax error, and the table type silently
and Microsoft-like, defaults to MyISAM?

Now, if you specify table type as TYPE=bullshit,
you get a nice (and expected) syntax error.

MyIsam V Innodb
I am working with a test database. Originally the tables were all innodb. I have a number of queries involving and I had performed a certain amount of optimisation to ensure that the queries were using the correct indexes and were not performing a table scan.

I then changed the table type to MyIsam because I wanted to speed up the application and I do not require that the database is transaction-safe. However when I ran the same EXPLAIN on the exact same queries as before, I noticed that the queries were going to take significantly longer to run, depsite the fact that they were still going to use the correct indexes.

I have reviewed the documentation and cannot find anything of this nature. I was totally surprised because I expected MYISAM to speed up the queries.

Fulltext For InnoDB?
I was wondering, does anyone know if there are any plans to add FULLTEXT indexes to the almighty InnoDB such that it catches up with the funky MyISAM in terms of things you can pass to ALTER TABLE?

My.Ini - InnoDB Entries
I have been using MySQL in a Windows environment for about 6 months and have successfully written a couple of applications. Reading all and anything about MySQL I have been looking into the My.Ini entries, particularly those affecting InnoDB tables. Code:

Backup InnoDB
I just installed MySQL and I need to make a backup of my InnoDB tables
but I don't want to purchase the tool to do it. Does anyone have a
detailed list of how to do this.

MyISAM Vs. InnoDB
I've been having some problems with the .myi files, (the index files), of my MyISAM tables dissapearing. It can be fixed by restarting the server but I cannot figure out what is causing the problem.

So, in looking for a solution I started reading about different table types and realized that I will not be able to use MyISAM tables for some transactional stuff that I am about to build.

This led me to wonder - should I just change all my tables to InnoDB - I'll be able to do transactions and maybe I won't have problems with .myi files dissapearing!

So, here's my dilemma - the MySQL manual is contradictory about why I should not use InnoDB tables - in one place it says it's slow because it's transactional, but in another it says it is the fastest disk-based engine in existence.

Finally the real question: Is there any reason I should not switch to all InnoDB tables? Are they really super fast yet support both transactions and foreign key constraints?

Innodb Vs MyISAM?
Besides transaction capability, is there anything else about innodb
tables that makes it superior to myISAM?

I really don’t need transactions, but for example, I need superior
reliability for index (too many index corruptions in the past). Is
innodb better in that area, or any other areas?

Innodb Logfiles
Is it possible to manually delete the logfiles created in innodb tables
and then do a 'touch logfilename' to recreate it?