| Author |
Re: [PEAR] Transactions in mdb2
|
|
| Paolo Sala 2006-03-22, 8:03 am |
| Paolo Sala scrisse in data 03/22/06 13:10:
> I'm trying to use transactions using pear::mdb2. The method
> supports('transactions') answer true but beginTransaction() return the
> error code "beginTransaction: transactions are not in use"
>
I'm sorry, I have send the thread to quickly... the problem is that
supports('transaction') answer true but supports('transactions') answer
false.
I have to guess I can't use transactions in mdb2? Is This feature not
supported yet or my php, mysql or... configuration is not supported by mdb2?
Thank you very much
Piviul
| |
| Paolo Sala 2006-03-22, 7:00 pm |
| I exposed my problem in a confugsed way. I try to be more clear.
In a debian sarge I have installed php4 ver. 4.3.10, pear::mdb2 ver.
2.0.0, pear::mdb2_driver_mysql ver. 1.0.0 and I would use transactions.
If I use the supports('transactions') mdb2 method I can guess that
transactions are not supported.
Aren't mdb2 transactions supported yet? Aren't mdb2 transactions
supported on php4? Does anyone know why transactions are not supported?
Thank you very much
Piviul
| |
| Lukas Smith 2006-03-22, 7:00 pm |
| Paolo Sala wrote:
> Paolo Sala scrisse in data 03/22/06 13:10:
>
> I'm sorry, I have send the thread to quickly... the problem is that
> supports('transaction') answer true but supports('transactions') answer
> false.
>
> I have to guess I can't use transactions in mdb2? Is This feature not
> supported yet or my php, mysql or... configuration is not supported by
> mdb2?
It was a stupid foobar. In the past InnoDB was not bundled with all
major MySQL binary releases, which is why you need to specifically
enable transaction support. I probably should have changed this default
before going to 1.0.0 but its too late now.
$options['use_transactions'] = true;
$options['default_table_type'] = 'InnoDB';
$mdb2 = MDB2::factory($dsn, $options);
regards,
Lukas
| |
| Paolo Sala 2006-03-22, 7:00 pm |
| Lukas Smith scrisse in data 03/22/06 15:45:
> It was a stupid foobar. In the past InnoDB was not bundled with all
> major MySQL binary releases, which is why you need to specifically
> enable transaction support. I probably should have changed this
> default before going to 1.0.0 but its too late now.
>
> $options['use_transactions'] = true;
> $options['default_table_type'] = 'InnoDB';
>
> $mdb2 = MDB2::factory($dsn, $options);
Thank you very much Lukas but doesn't seems to work. I put the following
code
> // After include DB::MDB2 and set $dsn...
> $options['use_transactions'] = true;
> $options['default_table_type'] = 'InnoDB';
>
> $mdb2=& MDB2::factory($dsn, $options);
> if (!$mdb2->supports('transactions'))
> echo 'I'm sorry, no transactions support.';
and the answer is:
> I'm sorry, no transactions support.
Where I wrong?
Piviul
| |
| Lukas Smith 2006-03-22, 7:00 pm |
| Paolo Sala wrote:[color=darkred]
> Lukas Smith scrisse in data 03/22/06 15:45:
>
>
> Thank you very much Lukas but doesn't seems to work. I put the following
> code
>
ah yes .. this is done at connect ..
so use MDB2::connect() instead of MDB2::factory()
regards,
Lukas
| |
| Paolo Sala 2006-03-22, 7:00 pm |
| Lukas Smith scrisse in data 03/22/06 17:03:
> ah yes .. this is done at connect ..
>
> so use MDB2::connect() instead of MDB2::factory()
Thank you very much Lukas but I have no solved my problems... in effect
now supports('transactions') return true but if I begin a transaction,
rollback doesn't work. That's the code I've used:
> [...cut...]
> $options['use_transactions'] = true;
> $options['default_table_type'] = 'InnoDB';
>
> $mdb2=& MDB2::connect($dsn, $options);
>
> if ($mdb2->supports('transactions'))
> $mdb2->beginTransaction();
>
> $strSQL= 'INSERT INTO [...cut...]';
>
> if ($mdb2->in_transaction)
> $mdb2->rollback();
and I have a commit not a rollback... :-(
Piviul
| |
| Paolo Sala 2006-03-22, 7:00 pm |
| Yes, I've forgotten to insert a queryAll. I rewrite the code:
> $options['use_transactions'] = true;
> $options['default_table_type'] = 'InnoDB';
>
> $mdb2=& MDB2::connect($dsn, $options);
>
> if ($mdb2->supports('transactions'))
> $mdb2->beginTransaction();
>
> $strSQL= 'INSERT INTO [...cut...]';
>
> $mdb2->queryAll($strSQL);
>
> if ($mdb2->in_transaction)
> $mdb2->rollback();
and the result is a commit even if I have called a rollback. Furthermore
the property in_transaction is true: is the rollback function that
doesn't works!
Piviul
Piviul
| |
| Lukas Smith 2006-03-22, 7:00 pm |
| Paolo Sala wrote:
> Yes, I've forgotten to insert a queryAll. I rewrite the code:
>
>
> and the result is a commit even if I have called a rollback. Furthermore
> the property in_transaction is true: is the rollback function that
> doesn't works!
well you still need to make sure that the table you are operating on is
a transaction enabled table handler ..
the default_table_handler option for example simply means that MDB2 will
create all tables with this table handler by default. MDB2 also
determines based on this setting if you are using transactional table
handlers or not ..
regards,
Lukas
| |
| Paolo Sala 2006-03-22, 7:00 pm |
| Lukas Smith scrisse in data 03/22/06 18:01:
> Paolo Sala wrote:
>
>
>
> well you still need to make sure that the table you are operating on
> is a transaction enabled table handler ..
....I hope you forgive my newbeness questions: how can I know if the
table is "transaction enabled"? If it is not, there is a way to do it
transaction enabled? Perhaps it depends from mysql installation?
> the default_table_handler option for example simply means that MDB2
> will create all tables with this table handler by default. MDB2 also
> determines based on this setting if you are using transactional table
> handlers or not ..
I've found so poor documentation about mdb2...
Piviul
| |
| Lukas Smith 2006-03-22, 7:00 pm |
| Paolo Sala wrote:
> ...I hope you forgive my newbeness questions: how can I know if the
> table is "transaction enabled"? If it is not, there is a way to do it
> transaction enabled? Perhaps it depends from mysql installation?
yes .. these days mysql bundles innodb with all major binary distributions.
the easiest way to determine the table handler is to use:
SHOW CREATE TABLE [tablename]
to change the table type you need to use:
ALTER TABLE [tablename] TYPE = innodb
>
> I've found so poor documentation about mdb2...
yes ... i know: http://pooteeweet.org/blog/336
regards,
Lukas
| |
| Paolo Sala 2006-03-23, 8:01 am |
| Lukas Smith scrisse in data 03/22/06 18:36:
> yes .. these days mysql bundles innodb with all major binary
> distributions.
>
> the easiest way to determine the table handler is to use:
> SHOW CREATE TABLE [tablename]
>
> to change the table type you need to use:
> ALTER TABLE [tablename] TYPE = innodb
Thank you very much Lukas, I've learned a lot from you.
Can you please give me some further suggestions about mysql? If I need
transactions support on mysql 4.1 you suggest me to use innodb database
engine? I've read that oracle buyed innodb; do you think I can invest my
efforts on innodb even if freedom is very important for me? What about
Berkeley db?
Thank you very much.
Piviul
| |
| Lukas Smith 2006-03-23, 8:01 am |
| Paolo Sala wrote:
> Can you please give me some further suggestions about mysql? If I need
> transactions support on mysql 4.1 you suggest me to use innodb database
> engine? I've read that oracle buyed innodb; do you think I can invest my
> efforts on innodb even if freedom is very important for me? What about
> Berkeley db?
At this point I would stick with InnoDB. Its the more reliable and
performant solution mainly because its used more. I think MySQL will
introduce a new option by the end of this year, but thats just me guessing.
regards,
Lukas
| |
| Paolo Sala 2006-03-23, 8:01 am |
| Paolo Sala scrisse in data 03/23/06 11:42:
> Lukas Smith scrisse in data 03/22/06 18:36:
>
>
>
> Thank you very much Lukas, I've learned a lot from you.
>
> Can you please give me some further suggestions about mysql? If I
> need transactions support on mysql 4.1 you suggest me to use innodb
> database engine? I've read that oracle buyed innodb; do you think I
> can invest my efforts on innodb even if freedom is very important for
> me? What about Berkeley db?
One more question... there is a way using mdb2 to know if a rollback
didn't succeeds?
Thank you very and very much indeed
Piviul
| |
| Alexey Borzov 2006-03-23, 8:01 am |
| Hi,
Paolo Sala wrote:
> Can you please give me some further suggestions about mysql? If I need
> transactions support on mysql 4.1 you suggest me to use innodb database
> engine? I've read that oracle buyed innodb; do you think I can invest my
> efforts on innodb even if freedom is very important for me? What about
> Berkeley db?
Suggestion: drop MySQL, switch to PostgreSQL. Benefits: BSD license, no history
of license changes (MySQL changed licenses several times), developed by a
community rather than a company, so cannot be "bought". One robust "storage
engine" rather than a dozen half-baked incompatible ones (try creating a table
in MySQL with *both* foreign keys and a full-text index, to see what I mean)
with their bugs and gotchas. The server is easily extensible, too: you can even
write stored procedures in PHP if so desire.
Downsides: you can't run PostgreSQL and develop for it if you only read MySQL's
documentation. You'll actually have to consult its own docs. Also, its hard to
find hosting companies offering PostgreSQL for $5. Then again, if $5 hosting is
sufficient for your projects, you'd better go with SQLite.
| |
| Lorenzo Alberton 2006-03-23, 8:01 am |
| Ciao Paolo,
> Can you please give me some further suggestions about mysql? If I need
> transactions support on mysql 4.1 you suggest me to use innodb database
> engine? I've read that oracle buyed innodb; do you think I can invest my
> efforts on innodb even if freedom is very important for me? What about
> Berkeley db?
if you want to read more about the future of innodb in MySQL,
I'd suggest searching through the http://www.planetmysql.org/
archives. Many words have been spent on the issue.
Short version: I think you can safely use that engine.
If you don't want to worry about license issues and
have a real enterprise dbms, for free, then have a
look at http://www.firebirdsql.org/ or http://www.postgresql.org/
Both dbms are supported by MDB2.
HTH
Cheers,
--
Lorenzo Alberton
http://pear.php.net/user/quipo
| |
| Lukas Smith 2006-03-23, 8:01 am |
| Paolo Sala wrote:
> One more question... there is a way using mdb2 to know if a rollback
> didn't succeeds?
MDB2 will report any errors from the underlying database. However MySQL
only issues a warning if a non transactional database was modified
during a transaction and a rollback is issued.
regards,
Lukas
| |
| Paolo Sala 2006-03-23, 8:01 am |
| Well, now I don't really know what I have to do and what I'll do but...
this is the price of freedom and I love it so much! :-))
Thank you very and very much to all
Piviul
|
|
|
|