Page 2 of 2
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 6:38 am
by etherkye
No idea what that is. My database is MySQL defult set up as provided by my webhost.
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 6:44 am
by VladSun
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB and BDB transactional storage engines. InnoDB provides full ACID compliance.
Usually, webhosting companies install MySQL with MyISAM table storage type by default. So, when you create a table that must support transactions you must explicitly set its type to InnoDB:
[sql]CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(120) DEFAULT NULL, ............. PRIMARY KEY (`id`)) ENGINE=InnoDB;[/sql]
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:00 am
by etherkye
How do i edit the engine so i don't lose currently stored data?
Edit: found it on phpmyadmin
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:04 am
by VladSun
[sql]ALTER TABLE `mytable` ENGINE = InnoDB;[/sql]
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:07 am
by etherkye
Code: Select all
SQL query:
ALTER TABLE `SalesRecord` ENGINE = InnoDB
MySQL said:
#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
All my tables have only 1 alto number, but multiple columns in the primary key.
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:15 am
by VladSun
If you want to use transactions you have to use InnoDB .... So, you need to recreate your tables so they conform to the innoDB engine requirements.
Move your composite PK to an index and make your auto column a PK
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:17 am
by etherkye
why is MyISAM the default engine on tables? does it to anything better?
Yes, MyISAM actuly lets you have the tables you want...
I can't have the altokey where i need it on InnoDB.
All my primary keys are at lesat 3 columns wide, as two columns are used to identify the user, the third colomn is the alto as i need the alto number to only increase for each user and not be unique globaly.
InnoDB won;t let me put the autokey where i need it >.<
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:19 am
by VladSun
MyISAM has better read performance than InnoDB.
Also, it has full text search capabilities.
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:30 am
by etherkye
Does the order of columns in a key affect the speed of data retreaval?
Edit: apprently moving the auto column to the top of the key names the database happy...
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:32 am
by VladSun
I don't think so
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 7:40 am
by etherkye
So i'm sacrificing full text search for it to work?
Glad i don't use LIKE in any of my searchs (i think).
Is there anything i can't perform on a InnoDB database that i should bare in mind while programming?
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 8:00 am
by etherkye
Great, how do i reset numbers on this table method? deleting all the rows doesnt help. it just keeps carrings on the number sequence.
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 8:21 am
by Eran
Glad i don't use LIKE in any of my searchs (i think).
FULLTEXT is not LIKE. It's a separate index that uses separate functions. You can most certainly use LIKE with InnoDB
Re: Questions about MySQL Transations and how to use them in php
Posted: Fri Aug 07, 2009 8:27 am
by etherkye
Well as i have no idea what it does, i shall assume i'm not using it and there will be no problems XD