Questions about MySQL Transations and how to use them in php
Moderator: General Moderators
Re: Questions about MySQL Transations and how to use them in php
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
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
[sql]CREATE TABLE `mytable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(120) DEFAULT NULL, ............. PRIMARY KEY (`id`)) ENGINE=InnoDB;[/sql]
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: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.
[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
Re: Questions about MySQL Transations and how to use them in php
How do i edit the engine so i don't lose currently stored data?
Edit: found it on phpmyadmin
Edit: found it on phpmyadmin
Re: Questions about MySQL Transations and how to use them in php
[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
Re: Questions about MySQL Transations and how to use them in php
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 keyRe: Questions about MySQL Transations and how to use them in php
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
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
Re: Questions about MySQL Transations and how to use them in php
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 >.<
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.
Re: Questions about MySQL Transations and how to use them in php
MyISAM has better read performance than InnoDB.
Also, it has full text search capabilities.
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
Re: Questions about MySQL Transations and how to use them in php
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...
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.
Re: Questions about MySQL Transations and how to use them in php
I don't think so
There are 10 types of people in this world, those who understand binary and those who don't
Re: Questions about MySQL Transations and how to use them in php
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?
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
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
FULLTEXT is not LIKE. It's a separate index that uses separate functions. You can most certainly use LIKE with InnoDBGlad i don't use LIKE in any of my searchs (i think).
Re: Questions about MySQL Transations and how to use them in php
Well as i have no idea what it does, i shall assume i'm not using it and there will be no problems XD