Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

mySQL 3.23.51 with BDB or InnoDB Transactional tables NOT rollback!!

 
Robert Paris
Ranch Hand
Posts: 585
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using a jdbc driver and mySQL 3.23.51. I have created a BDB table and an innoDB table (both of which are supposedly transactionable). The DatabaseMetaData also returns true when I query "supportsTransactions". However, every time I setAutoCommit to false, execute an update, then try to rollback, it says:
java.sql.SQLException: General error: Warning: Some non-transactional changed tables couldn't be rolled back
at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
at com.mysql.jdbc.MysqlIO.sqlQuery(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.Connection.execSQL(Unknown Source)
at com.mysql.jdbc.Connection.rollback(Unknown Source)
Anyone know what I need to do to get this to work?
 
Robert Paris
Ranch Hand
Posts: 585
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In case anyone's interested, I found the answer. You need to do a few small steps, mainly adding to your my.ini (on windows) or my.cnf (on unix/linux) a few lines.
1. In your my.ini/my.cnf file, find the line "[mysqld]". If the file does not exist, create it (on windows, this goes in the "windir" which is usually c:\WINNT. TO find out your windir, go to a cmd prompt and type "echo %windir%") (on Unix, this goes in /etc/my.cnf) If the line does not exist, type that in on one line (without the quotes).
2. Add the following lines (each line with a "#" is a comment):
#The home directory for the transaction data files
#This can be left blank and it will default to
# <mysql_install_home>/data
innodb_data_home_dir =
#The actual path of the innoDB data file
#(Don't worry if the file doesn't exist, it will create it)
# syntax: <file_path>:<size>:<increaseIfNeeded>:<MaxSize>
# You don't need a hardcoded path. If you just do:
# iData:30M:autoextend:200M
# it'll put it in the data directory. Also, everything
# after <size> is optional
innodb_data_file_path = <path>/<file>:<size>M:autoextend:max:<maxSize>M
3. You can also add the following lines to further configure your innodb handling:
#Set buffer pool size to 50 - 80 % of your computer's
#memory
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#Set the log file size to about 25 % of the buffer pool size
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
# Set ..flush_log_at_trx_commit to 0 if you can afford
#losing some last transactions
innodb_flush_log_at_trx_commit=1
4. Save the file and restart your mysql by typing
"mysqld-max --console"
That's it!
 
Robert Paris
Ranch Hand
Posts: 585
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, and get this! When I added that InnoDB information, just for kicks I tried creating a BDB table and rolling back...and it worked!!
Also, I just realized I forgot to tell you all how to take advantage of transaction capabilities. Just one small thing: whenever you create a table, you need to append "TYPE=InnoDB" to the end, like this:
CREATE TABLE TestInnoTable
(
IDNo INT NOT NULL PRIMARY KEY AUTO INCREMENT,
TextField VARCHAR (25)
) TYPE=InnoDB
That's it!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic