Auto Increment in InnoDB
I have a InnoDB table which contains columns named 'cluster' and 'file'
('cluster' + 'file' is a primary key). I want to add a new column that
tracks the revision number of a file for a given cluster and a file. The
situation is tailor made for a MyIsam table where I can add a new Auto
Increment column as a secondary column in a multiple column index. How
can I get the same behavior in an InnoDB table? Given below is a view of
how the records will look like
| Cluster |File| Rev |
| clusterA | fileA | 1|
| clusterA | fileA | 2|
-
| clusterB | fileA | 1|
| clusterB | fileB | 1|
Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: DBA questions to ask WAS: How to corrupt a database please???
-Original Message- From: Nurudin Javeri [mailto:nsjav...@idh.com] Sent: Sunday, April 18, 2010 9:25 AM To: mysql@lists.mysql.com Subject: How to corrupt a database please??? Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu 100GB! That's a HUGE database to play with and can take more than hours to repair. I would tone it down. You just want them to fix it and know what the commands/steps are, not spend hours sitting there watching a blinking cursor. We have almost a BILLION rows in our DB and it's only 70GB and we cringe if we have to alter a table as we know it's going to take a LONG time. I would focus on JUNIOR DBA tasks, such as: How do you start/stop the mysql server? sudo /etc/init.d/mysql stop|start|restart Where are the mysql logs found? (Trick Q: On ubuntu they're stupidly in /var/log/messages !?) GRANTing permissions (figure out why the script isn't writing to the DB, or find the security issue with this user, etc.) Execute a mysql command from the bash command line (not the mysql CLI) How do you find a slow query (slow query log) Setup a master/slave and then deliberately write to the slave. This causes replication to fail then. How do you fix it? mysql show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: No Last_Error: Error 'Duplicate key name 'id_operator'' on query. Default database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD INDEX `id_operator` (`id_operator`)' mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G What if /var/log/mysql growing too large? mysql PURGE BINARY LOGS; (The command RESET MASTER is not intended while replication slaves are running.) Foreign Key Failures? mysql show innodb status; LATEST FOREIGN KEY ERROR 090604 0:50:37 Cannot drop table `core/city` because it is referenced by `core/state` How do you make a backup? mysqldump -uroot -p --opt --add-drop-database --complete-insert --quote-names --comments --verbose --databases mydatabase | gzip -c ~/mydatabase.sql.gz How would you load that backup back in? gunzip ~/mydatagbase.sql.gz | mysql -uroot -p How do you load a comma separated file with a column header line? load data infile '/home/prod/user-batch.csv' ignore into table invitation_request fields terminated by ',' ignore 1 lines; What if you have lost/forgotten the mysql root password? http://dev.mysql.com/doc/refman/5.1/en/resetting-permissions.html#resetting -permissions-unix You could throw these in for good measure: mysqlcheck --user=root --password=XX --check --auto-repair mydatabase mysqlcheck --user=root --password=XX --analyze --auto-repair mydatabase Ask about mytop and various field lengths: # BIGINT UNSIGNED = 8 Byte = = 18446744073709551615 # INT UNSIGNED = 4 Byte = = 4294967295 # MEDIUMINT UNSIGNED = 3 Byte = FF = 16777215 # SMALLINT UNSIGNED = 2 Byte = = 65535 # TINYINT UNSIGNED = 1 Byte = FF = 255 # BIGINT SIGNED = -9223372036854775808 to 9223372036854775807 # INT SIGNED = -2147483648 to 2147483647 # MEDIUMINT SIGNED = -8388608 to 8388607 # SMALLINT SIGNED = -32768 to 32767 # TINYINT SIGNED = -128 to 127 # TINYTEXT = 255 # TEXT = 65535 # MEDIUMTEXT = 16777215 # LONGTEXT = 4294967295 # TEXT fields are NOT case sensitive, whereas BLOB fields are. # Always try to use UNSIGNED integers whenever possible. Anyways, you get the idea. Don't set the JUNIOR interviewee up for failure from the start. The questions should be something in the realm of what they'll be doing on a daily basis. The rest is stuff that Google will solve if and when that time comes. You want to make sure they have a solid grasp of mysql, not an expert in it. Don't blast them if they don't have this stuff memorized. I sure as hell don't, that's what our Wiki is for. You want people that can find answers, not memorize them. Daevid. http://daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fw: BIN LOG Error when use Begin Trans in Replication.
Hi Max Bube, The following are the variables related to the binlog. mysqlshow variables varibale_name : Value . . . binlog_cache_size : 32768 binlog_direct_non_transactional_updates : OFF binlog_format : STATEMENT . . . By default I found the statement based[it is better than row based?] format for bin log. I left it as such. I hope it is enough. As you said, in the my.ini file which part i suppose to add this binlog-format = statement ? Thank you Vikram From: Max Bube maxb...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Thu, 22 April, 2010 2:17:29 AM Subject: Re: Fw: BIN LOG Error when use Begin Trans in Replication. Hi Vikram, Add binlog_format=row to your my.ini master's conf file 2010/4/21 Vikram A vikkiatb...@yahoo.in Hi Hao Ding, I attached in my request mail it self. Please find the attachment. Thank you From: hao ding fire9di...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Wed, 21 April, 2010 7:25:05 PM Subject: Re: BIN LOG Error when use Begin Trans in Replication. Vikram, I don't find my.ini. On Wed, Apr 21, 2010 at 9:19 PM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I have installed mysql 5.1.45 version on windows server 2003[standard edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as slave. And the default engine in both severs is INNODB. I am using visual basic for my front end. The replication system is running quite well. When i am using the rs.BeginTrans I am facing the following error when i am pointing my master, [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' If i point my slave as server[I have done for checking beginTrans]; it is running quite well. Can I have help from any one. It will be useful to me. I am attaching the my.ini for your reference. Thank you Vikram A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
Re: Fw: BIN LOG Error when use Begin Trans in Replication.
hi, I tried with ROW and MIXED tyoe it is working fine. When i go for statement based, it is causing the error. mysql SET GLOBAL binlog_format = 'STATEMENT'; mysql SET GLOBAL binlog_format = 'ROW'; mysql SET GLOBAL binlog_format = 'MIXED'; can you suggest Which is the best format? Thank you VIKRAM From: Max Bube maxb...@gmail.com To: Vikram A vikkiatb...@yahoo.in Cc: MY SQL Mailing list mysql@lists.mysql.com Sent: Thu, 22 April, 2010 2:17:29 AM Subject: Re: Fw: BIN LOG Error when use Begin Trans in Replication. Hi Vikram, Add binlog_format=row to your my.ini master's conf file 2010/4/21 Vikram A vikkiatb...@yahoo.in Hi Hao Ding, I attached in my request mail it self. Please find the attachment. Thank you From: hao ding fire9di...@gmail.com To: Vikram A vikkiatb...@yahoo.in Sent: Wed, 21 April, 2010 7:25:05 PM Subject: Re: BIN LOG Error when use Begin Trans in Replication. Vikram, I don't find my.ini. On Wed, Apr 21, 2010 at 9:19 PM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I have installed mysql 5.1.45 version on windows server 2003[standard edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as slave. And the default engine in both severs is INNODB. I am using visual basic for my front end. The replication system is running quite well. When i am using the rs.BeginTrans I am facing the following error when i am pointing my master, [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' If i point my slave as server[I have done for checking beginTrans]; it is running quite well. Can I have help from any one. It will be useful to me. I am attaching the my.ini for your reference. Thank you Vikram A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
Re: More CPU or More RAM?
On Wed, Apr 21, 2010 at 4:52 PM, shamu...@gmail.com shamu...@gmail.comwrote: replacement etc, but that costs me too much if I can use hardware to solve the same problem. and Yes I know I will run to same problem again when the It may be that you can't actually solve it with more hardware. The version of drupal we were using (we've built a highly customised one by now) had the annoying tendency to explicitly lock tables, even when not strictly necessary. A lock is a lock, regardless of how much hardware you throw against it. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Auto Increment in InnoDB
You can't, iirc - if you add an autoincrement to InnoDB it MUST be the
primary key.
You *can*, however, add that, set it as PK and stick a unique index on
(cluster, file) instead. Behaviour will be identical, but be aware that
there will be some performance implications - you will now have to do an
extra primary key lookup every time you select based on the (cluster,file)
key.
On Thu, Apr 22, 2010 at 7:03 AM, Aveek Misra ave...@yahoo-inc.com wrote:
I have a InnoDB table which contains columns named 'cluster' and 'file'
('cluster' + 'file' is a primary key). I want to add a new column that
tracks the revision number of a file for a given cluster and a file. The
situation is tailor made for a MyIsam table where I can add a new Auto
Increment column as a secondary column in a multiple column index. How can I
get the same behavior in an InnoDB table? Given below is a view of how the
records will look like
| Cluster |File| Rev |
| clusterA | fileA | 1|
| clusterA | fileA | 2|
-
| clusterB | fileA | 1|
| clusterB | fileB | 1|
Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
Re: More CPU or More RAM?
On 04/21/2010 02:21 PM, Tom Worster wrote: I'd go with the 4G 4-core server. If you're running apache and a sensible OS, the extra cores can be helpful. So, unless you know you have a need for very large key buffers, 4G should leave the OS plenty for FS cache. Not that I actually have a clue. I really just wanted to be the first to answer the original question. Actually, Perrin already answered the question :) And now for my 2c. All other things being equal, I would opt for a more ram over a faster CPU. The extra ram can be used to reduce the load on the CPU through judicious caching and whatnot, but if I needed more CPU cycles to serve 60K pages, I'd be worried about how many more requests it would take to saturate the new CPU. In other words, I would gamble that extra ram would be sufficient. If I'm wrong, I'd have to address the underlying problem, but at least I would have more ram to work with when doing so. That being said, what does 4G vs. 8G comes to what/mo for a dedicated server? Say $30/g *4g * 1.25%(profit) = $150. And assuming a 12 month payback on the hardware, that would be $12.50ish/month more for the extra ram? -r -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: More CPU or More RAM?
Also, have some munin plugins. There are the ones I add to the ones in a standard munin distribution, and give plenty of info. Only the mysql_ one is actually mine, I got the rest off muninexchange. Guess I should incorporate their functionality into mine sometime. A good look at the data that comes out of these (and an understanding of how MySQL works, of course) will tell you what's up, what's happened and where to tune. On Thu, Apr 22, 2010 at 9:12 AM, Johan De Meersman vegiv...@tuxera.bewrote: On Wed, Apr 21, 2010 at 4:52 PM, shamu...@gmail.com shamu...@gmail.comwrote: replacement etc, but that costs me too much if I can use hardware to solve the same problem. and Yes I know I will run to same problem again when the It may be that you can't actually solve it with more hardware. The version of drupal we were using (we've built a highly customised one by now) had the annoying tendency to explicitly lock tables, even when not strictly necessary. A lock is a lock, regardless of how much hardware you throw against it. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
backup: mysqldump or mysqlhotcopy ?
hello, i'm new to MySQL, so i have currently some basic questions. I have a MySQL-Server with 15 users. Every User can create databases. I expect that the amount of data which has to be backuped will increase constantly. What i want: I'd like to have a backup on a regular basis. I think i will create two cronjobs, one running daily, the other running weekly. Initially, we will have just MyISAM tables, later on maybe InnoDB tables. I'd like to backup _all_ databases, but having the possibility to restore only some databases. And the number and names of the databases change often, but i don't like to adapt my script regulary. Which tool do you recommend ? mysqldump or mysqlhotcopy ? Can i backup all databases with mysqlhotcopy using mysqlhotcopy --regexp=[a-zA-Z0-9]\+ ? Can i restore from this backup only some databases just copying the saved directories of the backuped databases to the MySQL-directory ? Thanks in advance. Bernd -- Bernd Lentes Systemadministration Institut für Entwicklungsgenetik HelmholtzZentrum münchen bernd.len...@helmholtz-muenchen.de phone: +49 89 3187 1241 fax: +49 89 3187 3826 http://www.helmholtz-muenchen.de/idg Im Kampf um das Unerreichbare verliert das Erreichte seinen Wert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ANN: New version of Database Workbench, the multi-DBMS IDE now available!
Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular multi-DBMS development tool: Database Workbench 4.0 Pro With this version we're reached a milestone: Database Workbench is now fully Unicode enabled and offers new tools to increase your productivity. There have been numerous improvements to existing tools and the user interface making it even better than before. For more information, see http://www.upscene.com/ Database Workbench supports: - Borland InterBase ( 4.x - 9.x ) - Firebird ( 1.x, 2.x ) - MS SQL Server/MSDE ( 7, 2000, 2005, 2008, MSDE 1 2, SQL Express ) - MySQL 4.x, 5.x - Oracle Database ( 8i, 9i, 10g, 11g ) - Sybase SQL Anywhere ( 9, 10 and 11 ) - NexusDB ( 3.0 and up ) Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fw: BIN LOG Error when use Begin Trans in Replication.
Hi Vikram, Add binlog_format=row to your my.ini master's conf file 2010/4/21 Vikram A vikkiatb...@yahoo.in Hi Hao Ding, I attached in my request mail it self. Please find the attachment. Thank you -- *From:* hao ding fire9di...@gmail.com *To:* Vikram A vikkiatb...@yahoo.in *Sent:* Wed, 21 April, 2010 7:25:05 PM *Subject:* Re: BIN LOG Error when use Begin Trans in Replication. Vikram, I don't find my.ini. On Wed, Apr 21, 2010 at 9:19 PM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I have installed mysql 5.1.45 version on windows server 2003[standard edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as slave. And the default engine in both severs is INNODB. I am using visual basic for my front end. The replication system is running quite well. When i am using the rs.BeginTrans I am facing the following error when i am pointing my master, [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' If i point my slave as server[I have done for checking beginTrans]; it is running quite well. Can I have help from any one. It will be useful to me. I am attaching the my.ini for your reference. Thank you Vikram A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
Re: Auto Increment in InnoDB
I am not sure I understand. If I make the autoincrement column as part
of the primary key as (rev + cluster + file), how do I ensure that a
reset of the revision number is done as soon as (cluster + file)
combination changes? It looks like I need to do the following to mimic
the same behavior as that of an autoincrement column in MyISAM
SELECT @id := IFNULL(MAX(rev), 0) FROM table WHERE cluster='clusterA'
AND file='fileA' ;
SET @id := @id + 1;
INSERT INTO table (cluster, file, rev) VALUES ('clusterA', 'fileA', @id);
Additionally I guess the above needs to be encapsulated in a transaction
to ensure atomic updates to the 'rev' number for a given cluster and
file combination. Any thoughts?
Thanks
Aveek
Johan De Meersman wrote:
You can't, iirc - if you add an autoincrement to InnoDB it MUST be the
primary key.
You *can*, however, add that, set it as PK and stick a unique index on
(cluster, file) instead. Behaviour will be identical, but be aware
that there will be some performance implications - you will now have
to do an extra primary key lookup every time you select based on the
(cluster,file) key.
On Thu, Apr 22, 2010 at 7:03 AM, Aveek Misra ave...@yahoo-inc.com
mailto:ave...@yahoo-inc.com wrote:
I have a InnoDB table which contains columns named 'cluster' and
'file' ('cluster' + 'file' is a primary key). I want to add a new
column that tracks the revision number of a file for a given
cluster and a file. The situation is tailor made for a MyIsam
table where I can add a new Auto Increment column as a secondary
column in a multiple column index. How can I get the same behavior
in an InnoDB table? Given below is a view of how the records will
look like
| Cluster |File| Rev |
| clusterA | fileA | 1|
| clusterA | fileA | 2|
-
| clusterB | fileA | 1|
| clusterB | fileB | 1|
Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be
--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra ave...@yahoo-inc.com wrote: I am not sure I understand. If I make the autoincrement column as part of the primary key as (rev + cluster + file), how do I ensure that a reset of the revision number is done as soon as (cluster + file) combination changes? You want the autoincrement to go up every time you change the values in your primary key ? Aside from it not being a recommended practice, to put it mildly, that you update primary key values (possible referential inconsistency), I'm not aware of this behaviour in MyISAM, either. An autoincrement is assigned if, and only if you assign NULL or (zero) to an autoincrement column during an insert. If your application behaves differently, it is probably already done either in the application, or possibly through the use of triggers. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
SQL_BIG_RESULT being ignored?
Using 5.0.51, I have a fairly substantial SELECT ... GROUP BY query to which I have added the SQL_BIG_RESULT hint. According to the 5.0 SELECT manual, SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows. In this case, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements. However, during query execution, I saw Converting HEAP to MyISAM in the processlist for that thread. I may be misunderstanding the manual, but shouldn't that hint force the temporary table to disk rather than a HEAP table? Thanks for any help anyone can provide, kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
MyISAM has this really cool feature where you can specify autoincrement on a secondary column in a multiple column index. In such a case the generated value for the autoincrement column is calculated as MAX(autoincrement column) + 1 WHERE prefix='given-prefix'. For more refer to http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html. This is exactly what I want, however I have an InnoDB table so this will not work (in an InnoDB table, you cannot specify autoincrement on a secondary column). So what I wanted to know was if there is some easy way to mimic that behavior. In my last mail that I sent, in order to mimic that functionality on InnoDB, I had to write several statements that possibly need to be a part of a transaction. Of course that also meant that I cannot specify that column as an autoincrement but instead specify it something as INT NOT NULL. Thanks Aveek Johan De Meersman wrote: On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra ave...@yahoo-inc.com wrote: I am not sure I understand. If I make the autoincrement column as part of the primary key as (rev + cluster + file), how do I ensure that a reset of the revision number is done as soon as (cluster + file) combination changes? You want the autoincrement to go up every time you change the values in your primary key ? Aside from it not being a recommended practice, to put it mildly, that you update primary key values (possible referential inconsistency), I'm not aware of this behaviour in MyISAM, either. An autoincrement is assigned if, and only if you assign NULL or (zero) to an autoincrement column during an insert. If your application behaves differently, it is probably already done either in the application, or possibly through the use of triggers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
Kudos for managing to drag up such an obscure piece of functionality :-) I can see where it would be useful, though. As to your question, though: given that that page indicates that it will reuse deleted sequence numbers, I think your best bet would be select @id := count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should be slightly faster than a max(), I think. That in a trigger on your table should emulate the behaviour pretty closely. Am I mistaken, or does your code try to start from sequence 0 ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Auto Increment in InnoDB
How can count(*) in an InnoDB table be faster than MAX() considering that the former needs to do a table scan and the latter can use an index if correctly used? My code starts the sequence from 1. Thanks Aveek Johan De Meersman wrote: Kudos for managing to drag up such an obscure piece of functionality :-) I can see where it would be useful, though. As to your question, though: given that that page indicates that it will reuse deleted sequence numbers, I think your best bet would be select @id := count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should be slightly faster than a max(), I think. That in a trigger on your table should emulate the behaviour pretty closely. Am I mistaken, or does your code try to start from sequence 0 ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
The count happens after the where on an index - it should just count the appropriate index rows without looking at the values. Worth benchmarking on your dataset, though. On Thu, Apr 22, 2010 at 1:22 PM, Aveek Misra ave...@yahoo-inc.com wrote: How can count(*) in an InnoDB table be faster than MAX() considering that the former needs to do a table scan and the latter can use an index if correctly used? My code starts the sequence from 1. Thanks Aveek Johan De Meersman wrote: Kudos for managing to drag up such an obscure piece of functionality :-) I can see where it would be useful, though. As to your question, though: given that that page indicates that it will reuse deleted sequence numbers, I think your best bet would be select @id := count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should be slightly faster than a max(), I think. That in a trigger on your table should emulate the behaviour pretty closely. Am I mistaken, or does your code try to start from sequence 0 ? -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Auto Increment in InnoDB
On Thu, 22 Apr 2010 13:12:16 +0200, Johan De Meersman vegiv...@tuxera.be wrote: Kudos for managing to drag up such an obscure piece of functionality :-) I can see where it would be useful, though. As to your question, though: given that that page indicates that it will reuse deleted sequence numbers, I think your best bet would be select @id := count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should be slightly faster than a max(), I think. That in a trigger on your table should emulate the behaviour pretty closely. Wouldn't that strategy cause problems if one or more rows have been deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row 2 has been deleted - new sequence number would be 4). / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dkwrote: Wouldn't that strategy cause problems if one or more rows have been deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row 2 has been deleted - new sequence number would be 4). Yeps. I'm none too sharp today, apparently. Max() it is. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Auto Increment in InnoDB
At 12:03 AM 4/22/2010, Aveek Misra wrote:
I have a InnoDB table which contains columns named 'cluster' and 'file'
('cluster' + 'file' is a primary key). I want to add a new column that
tracks the revision number of a file for a given cluster and a file. The
situation is tailor made for a MyIsam table where I can add a new Auto
Increment column as a secondary column in a multiple column index. How can
I get the same behavior in an InnoDB table? Given below is a view of how
the records will look like
| Cluster |File| Rev |
| clusterA | fileA | 1|
| clusterA | fileA | 2|
-
| clusterB | fileA | 1|
| clusterB | fileB | 1|
Aveek,
You should be able to add an autoincrement field to a compound key if
the autoinc field is the first field of the compound key. This probably
doesn't help you though.
Mike
Thanks
Aveek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Speeding up inserts in InnoDB
I have a very simple table.
CREATE TABLE `hams`.`phoneticcallsign` (
`CallSign` char(6) NOT NULL,
`PhoneticCallSign` char(6) NOT NULL,
PRIMARY KEY (`CallSign`),
KEY `PhoneticIdx` (`PhoneticCallSign`) USING BTREE
)
I inserted a little over 1 million records with CallSign = to a value
from another table and PhoneticCallSign blank. Then I used the
following simple php script to set the value of PhoneticCallSign.
$query = SELECT `CallSign` \n;
$query .= FROM `phoneticcallsign` \n;
$query .= WHERE `PhoneticCallSign` = '' \n;
$result = mysql_query($query) or die(DB error $query . mysql_error() );
while(($row = mysql_fetch_row($result))){
$CallSign = $row[0];
$PhoneticCallSign = SoundsLike($CallSign);
$query = UPDATE `phoneticcallsign` \n;
$query .= SET `PhoneticCallSign` = '$PhoneticCallSign' \n;
$query .= WHERE `CallSign` = '$CallSign' \n;
$Uresult = mysql_query($query) or die(DB error $query .
mysql_error() );
}
This was running very slow and I was getting only about 50 inserts per
second. I noticed that the table was InnoDB so I decided to change it
to MyISAM and try again. With MyISAM I was getting around 10,000
inserts per second. Surely there is some way to make InnoDB faster.
Any ideas?
Chris W
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Auto Increment in InnoDB
Johan De Meersman wrote: On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dkwrote: Wouldn't that strategy cause problems if one or more rows have been deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row 2 has been deleted - new sequence number would be 4). Yeps. I'm none too sharp today, apparently. Max() it is. That may not be an issue in this case. Since it sounds like he is keeping a revision history, I wouldn't be surprised if he plans on not allowing the deleting of records, unless of course all of the revision history for a given file Cluster/File are deleted. If that is the case the count would work fine. If that is not the case, max may not work either since if the last revision record has been deleted then using max will give faulty data as well. Seems the only way for something like this to work is if you keep the full revision history. Although I suppose that if you were to keep say the most recent X revisions then the last revision would always be in the table and max could work where count would not always. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Speeding up inserts in InnoDB
I'm a little confused.. are the inserts slow, or are the updates slow?
It sounds like you mean the updates were going about 50/updates sec. You
could speed up the update by adding an index on phoneticcallsign.CallSign.
JW
On Thu, Apr 22, 2010 at 10:13 AM, Chris W 4rfv...@cox.net wrote:
I have a very simple table.
CREATE TABLE `hams`.`phoneticcallsign` (
`CallSign` char(6) NOT NULL,
`PhoneticCallSign` char(6) NOT NULL,
PRIMARY KEY (`CallSign`),
KEY `PhoneticIdx` (`PhoneticCallSign`) USING BTREE
)
I inserted a little over 1 million records with CallSign = to a value from
another table and PhoneticCallSign blank. Then I used the following simple
php script to set the value of PhoneticCallSign.
$query = SELECT `CallSign` \n;
$query .= FROM `phoneticcallsign` \n;
$query .= WHERE `PhoneticCallSign` = '' \n;
$result = mysql_query($query) or die(DB error $query . mysql_error() );
while(($row = mysql_fetch_row($result))){
$CallSign = $row[0];
$PhoneticCallSign = SoundsLike($CallSign);
$query = UPDATE `phoneticcallsign` \n;
$query .= SET `PhoneticCallSign` = '$PhoneticCallSign' \n;
$query .= WHERE `CallSign` = '$CallSign' \n;
$Uresult = mysql_query($query) or die(DB error $query . mysql_error()
);
}
This was running very slow and I was getting only about 50 inserts per
second. I noticed that the table was InnoDB so I decided to change it to
MyISAM and try again. With MyISAM I was getting around 10,000 inserts per
second. Surely there is some way to make InnoDB faster.
Any ideas?
Chris W
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
--
-
Johnny Withers
601.209.4985
joh...@pixelated.net
Re: Speeding up inserts in InnoDB
Sorry I misspoke, I am doing updates not inserts. If I was doing
inserts I thought about the multiple record at a time idea but unless
there is something I don't know, I don't think you can do that with
updates. I will look into turning autocommit off and see what that does.
Chris W.
Andrew Carlson wrote:
If you are doing batch inserts, either turn autocommit off, and commit
after every so many inserts, or use the multiple values insert
statement to insert multiple records at one time. If the inserts are
coming from multiple sources/processes, it's a little bit of a harder
problem.
On Thu, Apr 22, 2010 at 10:13 AM, Chris W 4rfv...@cox.net wrote:
I have a very simple table.
CREATE TABLE `hams`.`phoneticcallsign` (
`CallSign` char(6) NOT NULL,
`PhoneticCallSign` char(6) NOT NULL,
PRIMARY KEY (`CallSign`),
KEY `PhoneticIdx` (`PhoneticCallSign`) USING BTREE
)
I inserted a little over 1 million records with CallSign = to a value from
another table and PhoneticCallSign blank. Then I used the following simple
php script to set the value of PhoneticCallSign.
$query = SELECT `CallSign` \n;
$query .= FROM `phoneticcallsign` \n;
$query .= WHERE `PhoneticCallSign` = '' \n;
$result = mysql_query($query) or die(DB error $query . mysql_error() );
while(($row = mysql_fetch_row($result))){
$CallSign = $row[0];
$PhoneticCallSign = SoundsLike($CallSign);
$query = UPDATE `phoneticcallsign` \n;
$query .= SET `PhoneticCallSign` = '$PhoneticCallSign' \n;
$query .= WHERE `CallSign` = '$CallSign' \n;
$Uresult = mysql_query($query) or die(DB error $query . mysql_error() );
}
This was running very slow and I was getting only about 50 inserts per
second. I noticed that the table was InnoDB so I decided to change it to
MyISAM and try again. With MyISAM I was getting around 10,000 inserts per
second. Surely there is some way to make InnoDB faster.
Any ideas?
Chris W
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=naclos...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Loading 4.1.12 dump to 5.0.18 server
I need to load a dump from a 4.1.12 server to a 5.0.18 server. When I do that it fails with: ERROR 1064 (42000) at line 23: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BTREE (`bookId`,`productId`,`clusterId`,`symbolId`), KEY `symbolId` TYPE BTREE' at line 37 I did use sed to get rid of those lines, but then I hit a problem with lines that had ENGINE=HEAP. I changed those to ENGINE=MEMORY, then I hit another problem. I certainly could iteratively do this until I get it to load, but I'm wondering if anyone knew of a better solution for this? Is the some tool that will convert the dump? TIA! -larry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
First and Last timestamp of the day/week / month
I need to be able to get a first and last timestamp for a day a week or a month. I have an example of what I did so far that gets me that info for a week... but I fear that it is far more complex than it needs to be. Anyone have a simple way to get first and last timestamp for these intervals? SELECT timestamp(ADDDATE(CURDATE(), -1 * (DAYOFWEEK(CURDATE()) - 1))) , from_unixtime(unix_timestamp(ADDDATE(CURDATE()+6, -1 * (DAYOFWEEK(CURDATE()) - 1)))+86399)
Re: First and Last timestamp of the day/week / month
You could rewrite it english friendly (5.1.37) SET @DAY_START = curdate(); SET @WEEK_START = curdate() - interval weekday(now()) DAY; SET @MONTH_START = date_format(curdate(), %Y-%m-01); ## DAY SELECT timestamp(@DAY_START) as min_ts, timestamp(@DAY_START + INTERVAL 1 DAY - INTERVAL 1 SECOND ) as max_ts ; ## WEEK SELECT timestamp(@WEEK_START) as min_ts, timestamp(@WEEK_START + INTERVAL 1 WEEK - INTERVAL 1 SECOND ) as max_ts ; ## MONTH SELECT timestamp(@MONTH_START) as min_ts, timestamp(@MONTH_START + INTERVAL 1 MONTH - INTERVAL 1 SECOND ) as max_ts ; ~~ c|_| Alister West - Saving the world from coffee! On 22 April 2010 14:25, Cantwell, Bryan bcantw...@firescope.com wrote: I need to be able to get a first and last timestamp for a day a week or a month. I have an example of what I did so far that gets me that info for a week... but I fear that it is far more complex than it needs to be. Anyone have a simple way to get first and last timestamp for these intervals? SELECT timestamp(ADDDATE(CURDATE(), -1 * (DAYOFWEEK(CURDATE()) - 1))) , from_unixtime(unix_timestamp(ADDDATE(CURDATE()+6, -1 * (DAYOFWEEK(CURDATE()) - 1)))+86399) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Loading 4.1.12 dump to 5.0.18 server
Larry Martell wrote: I need to load a dump from a 4.1.12 server to a 5.0.18 server. When I do that it fails with: ERROR 1064 (42000) at line 23: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BTREE (`bookId`,`productId`,`clusterId`,`symbolId`), KEY `symbolId` TYPE BTREE' at line 37 I did use sed to get rid of those lines, but then I hit a problem with lines that had ENGINE=HEAP. I changed those to ENGINE=MEMORY, then I hit another problem. I certainly could iteratively do this until I get it to load, but I'm wondering if anyone knew of a better solution for this? Is the some tool that will convert the dump? TIA! -larry Have you tried dumping the data with the mysqldump from the 5.1 package? It should be able to connect just fine to the 4.1 instance and its output is already 5.1 compatible. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Loading 4.1.12 dump to 5.0.18 server
On Thu, Apr 22, 2010 at 1:42 PM, Larry Martell larry.mart...@gmail.com wrote: I need to load a dump from a 4.1.12 server to a 5.0.18 server. When I do that it fails with: Upgrading from an early 4.1 series to an incredibly early 5.0 series is a bad idea. Your first priority should be upgrading your destination to something 5.0.67 (min) or later. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
