Enabling INNODB
in mysql 4.1 that came with xampp package, i saw that innodb table type is disabled..how may i enable it?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Enabling InnoDB
Actually am using mysql 5.0 version, default it using MyISAM engine, but i wnat ot use InnoDB engine. I tried to change the my.cnf file with entry "default_server_engnie=InnoDB".but there is no use.
Enabling SSL Using MySQL Rpm
How do I enable MySQL to accept ssl connections using the rpm version of MySQL 4.1.10. I am using RHEL 3.0. Can some one point in the right direction?
Enabling Replication
If I add log-bin server-id=1 to my.ini and restart the server it starts creating the .bin logs - woo hoo ! Unfortunately I then cannot log into,or use mysql - I have tried at the cmd line with localhost in the connect string and also the actual IP address ..... no joy If I comment out the log-bin lines then all is ok again (phew!) - nothing is present in the error log either .... Any ideas what I am missing ?
Web-enabling The Database
After information has been added to the mysql database, how do you go about web-enabling the information to be searched by your visitors?
Enabling Remote Connections
Can someone tell me how to set up the mysql server to accept remote connections? Im using mysql 4.1 on Linux Ubuntu 5.10 I have tried editing my.cnf and changing the bind-address from 127.0.0.1 to the server's IP address, but this just results in the server failing to restart with the following error: Starting MySQL database server: mysqld...failed. Please take a look at the syslog. /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! What do I have to do to get it to accept local and remote connections? Code:
Enabling SSL & Setting Up A Certificate
I have MySQL 5.0.27 on XP Pro. I have not been able to find the correct syntax for starting MySQL so that SSL is enabled.I've searched the forums and the Manual. ALso, can anyone point me to a location which provides clear guidance on setting up an SSL certificate? %.9.7.4 of the Reference Manual wasn't too helpful for me.
Enabling Multiple Statements
I'm trying to execute the following multiple statements using MySql w/ the ODBC connector. INSERT INTO Sites(Hostname) values('zzz'); INSERT INTO Hosts(SiteID, Hostname) values(last_insert_id(), 'zzz'); I get the following error: [1064] [1] [0] "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; INSERT INTO SiteHosts(SiteID, Hostname) VALUES(@@IDENTITY, 'zzz')' at line 2" Is there something I have to do to enable multiple statements?
Enabling Federated Tables.
I am using mysql 5.0.27-community-nt on windows xp. Federated engine is inactive. How can I activate the federated engine?
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 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...)
INNODB Crashing
Shortly ago our system hung, with all queries showing a status of 'statistics'. I was not able to cleanly shutdown and restart the database and had to kill the main process which was at 99% cpu. Now I cant restart, and am getting this dialog in my error file, can anyone please help!? 041014 10:57:57 mysqld started 041014 10:57:57 Warning: Asked for 196608 thread stack, but got 126976 041014 10:57:58 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 53 604876995 InnoDB: Doing recovery: scanned up to log sequence number 53 610119680 InnoDB: Doing recovery: scanned up to log sequence number 53 612616861 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 1 row operations to undo InnoDB: Trx id counter is 0 972032512 041014 10:57:58 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 InnoDB: Error: trying to access page number 128 in space 0 InnoDB: which is outside the tablespace bounds. InnoDB: Byte offset 0, len 16384, i/o type 10 041014 10:58:02InnoDB: Assertion failure in thread 4096 in file fil0fil.c line 1207 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. See section 6.1 of InnoDB: http://www.innodb.com/ibman.php about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=268435456 read_buffer_size=1044480 max_used_connections=0 max_connections=300 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 875341 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x84152a0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe488, stack_bottom=0x20386365, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x64207369 is invalid pointer thd->thread_id=1702047021 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 041014 10:58:02 mysqld ended
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?
|
TRACKER
