Questions about MySQL Transations and how to use them in php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post by etherkye »

No idea what that is. My database is MySQL defult set up as provided by my webhost.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post by etherkye »

How do i edit the engine so i don't lose currently stored data?

Edit: found it on phpmyadmin
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post by VladSun »

[sql]ALTER TABLE `mytable` ENGINE = InnoDB;[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post 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 >.<
Last edited by etherkye on Fri Aug 07, 2009 7:21 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post by VladSun »

MyISAM has better read performance than InnoDB.
Also, it has full text search capabilities.
There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post 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...
Last edited by etherkye on Fri Aug 07, 2009 7:36 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Questions about MySQL Transations and how to use them in php

Post by VladSun »

I don't think so
There are 10 types of people in this world, those who understand binary and those who don't
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post 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?
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Questions about MySQL Transations and how to use them in php

Post 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
etherkye
Forum Newbie
Posts: 21
Joined: Fri Aug 07, 2009 3:40 am

Re: Questions about MySQL Transations and how to use them in php

Post 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
Post Reply