Re: Auto Increment in InnoDB
It might also be done by keeping a last-revision table. Then you'd only select 1 record from that, and up the number. On Thu, Apr 22, 2010 at 5:34 PM, Chris W 4rfv...@cox.net wrote: Johan De Meersman wrote: On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dk wrote: 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=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: Loading 4.1.12 dump to 5.0.18 server
On Thu, Apr 22, 2010 at 8:19 PM, Shawn Green shawn.l.gr...@oracle.com wrote: 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. The 4.1 server does not exist any more. This is a dump from Jan 2008 that I need to load. I will have to check with the dbas at this client and see if they can have that release and can install it somewhere. -- 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 9:39 PM, Rob Wultsch wult...@gmail.com wrote: 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. They are not upgrading anything. They have a dump from Jan 2008 that they want me to look at and do some analysis on. In Jan 2008 they were running 4.1.12. Today they are running 5.0.18. This is not something that I have control over. This is at a client site. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Community Server 5.1.46 has been released
Dear MySQL users, MySQL Community Server 5.1.46, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.46 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.46 on new servers or upgrading to MySQL 5.1.46 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-46.html Enjoy! === C.1.2. Changes in MySQL 5.1.46 InnoDB Plugin Notes: * InnoDB Plugin has been upgraded to version 1.0.7. This version is considered of General Availability (GA) quality. The InnoDB Plugin Change History (http://www.innodb.com/doc/innodb_plugin-1.0/innodb-changes.ht ml) may contain information in addition to those changes reported here. In this release, the InnoDB Plugin is included in source and binary distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64, ia64), and generic Linux RPM packages. It also does not work for FreeBSD 6 and HP-UX or for Linux on generic ia64. Functionality added or changed: * There is a new system variable, skip_name_resolve, that is set from the value of the --skip-name-resolve server option. This provides a way to determine at runtime whether the server uses name resolution for client connections. (Bug#37168: http://bugs.mysql.com/bug.php?id=37168) Bugs fixed: * Security Fix: Privilege checking for UNINSTALL PLUGIN was incorrect. (Bug#51770: http://bugs.mysql.com/bug.php?id=51770) * Partitioning: Partition pruning on RANGE partitioned tables did not always work correctly; the last partition was not excluded if the range was beyond it (when not using MAXVALUE). Now the last partition is not included if the partitioning function value is not within the range. (Bug#51830: http://bugs.mysql.com/bug.php?id=51830) * Partitioning: The insert_id server system variable was not reset following an insert that failed on a partitioned MyISAM table having an AUTO_INCREMENT column. (Bug#50392: http://bugs.mysql.com/bug.php?id=50392) * Partitioning: Foreign keys are not supported on partitioned tables. However, it was possible via an ALTER TABLE statement to set a foreign key on a partitioned table; it was also possible to partition a table with a single foreign key. (Bug#50104: http://bugs.mysql.com/bug.php?id=50104) * Partitioning: GROUP BY queries performed poorly for some partitioned tables. This was due to the block size not being set for partitioned tables, thus the keys per block was not correct, which could cause such queries to be optimized incorrectly. (Bug#48229: http://bugs.mysql.com/bug.php?id=48229) See also Bug#37252: http://bugs.mysql.com/bug.php?id=37252. * Partitioning: REPAIR TABLE failed for partitioned ARCHIVE tables. (Bug#46565: http://bugs.mysql.com/bug.php?id=46565) * Replication: TRUNCATE TABLE performed on a temporary table using the InnoDB storage engine was logged even when using row-based mode. (Bug#51251: http://bugs.mysql.com/bug.php?id=51251) * Replication: When using temporary tables the binary log needs to insert a pseudo-thread ID for threads that are using temporary tables, each time a switch happens between two threads, both of which are using temporary tables. However, if a thread issued a failing statement before exit, its ID was not recorded in the binary log, and this in turn caused the ID for the next thread that tried to do something with a temporary table not to be logged as well. Subsequent replays of the binary log failed with the error Table ... doesn't exist. (Bug#51226: http://bugs.mysql.com/bug.php?id=51226) * Replication: If the master was using sql_mode='TRADITIONAL', duplicate key errors were not sent to the slave, which
sql query advise
hello,
i have to write a query which has to pull data from a remote mysql
server, modify the table scheme, format some of the fields and then
populate the new database.
i am using MySQLdb which is a python interface to mysql db.
how would i write a query to do this update from from a single
statement that uses tables from both databases?
in essence how to merge these two lines into one statement:
select_promoCode_records = SELECT oppc_id, limitedDate FROM
db1.partner_promoCode
update_promoCode_record = UPDATE db2.partner_promoCode SET
limitedDate =%s WHERE oppc_id =%s
here is a simplified version of what i have so far.
[code]
#!/usr/local/bin/python2.6
# -*- coding: utf-8 -*-
#
import MySQLdb
# connect to the MySQL server and select the databases
dbhost = 'localhost'
dbuser = 'user'
dbpasswd = 'password'
try:
# connect to db
origin = MySQLdb.connect (host = dbhost,
user = dbuser,
passwd =
dbpasswd,
)
except MySQLdb.Error, e:
print Error %s % e
sys.exit (1)
select_promoCode_records = SELECT oppc_id, limitedDate FROM
db1.partner_promoCode
update_promoCode_record = UPDATE db2.partner_promoCode SET
limitedDate =%s WHERE oppc_id =%s
org = origin.cursor()
org.execute(select_promoCode_records)
results = org.fetchall()
try:
for row in results:
oppc_id, date = row
org.execute(update_promoCode_record, (int(date), int(oppc_id)))
source.commit()
except:
print Error: enable to put data
# bye!
origin.close()
source.close()
[/code]
thanks
--
¿noʎ uɐɔ uʍop ǝpısdn ǝʇıɹʍ uɐɔ ı - % .join( [
{'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in
,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Community Server 5.1.46 has been released
On Fri, Apr 23, 2010 at 10:03 AM, Karen Langford karen.langf...@oracle.com wrote: Dear MySQL users, MySQL Community Server 5.1.46, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.46 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.46 on new servers or upgrading to MySQL 5.1.46 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-46.html Enjoy! === C.1.2. Changes in MySQL 5.1.46 InnoDB Plugin Notes: * InnoDB Plugin has been upgraded to version 1.0.7. This version is considered of General Availability (GA) quality. The InnoDB Plugin Change History (http://www.innodb.com/doc/innodb_plugin-1.0/innodb-changes.ht ml) may contain information in addition to those changes reported here. In this release, the InnoDB Plugin is included in source and binary distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64, ia64), and generic Linux RPM packages. It also does not work for FreeBSD 6 and HP-UX or for Linux on generic ia64. Functionality added or changed: * There is a new system variable, skip_name_resolve, that is set from the value of the --skip-name-resolve server option. This provides a way to determine at runtime whether the server uses name resolution for client connections. (Bug#37168: http://bugs.mysql.com/bug.php?id=37168) Bugs fixed: * Security Fix: Privilege checking for UNINSTALL PLUGIN was incorrect. (Bug#51770: http://bugs.mysql.com/bug.php?id=51770) * Partitioning: Partition pruning on RANGE partitioned tables did not always work correctly; the last partition was not excluded if the range was beyond it (when not using MAXVALUE). Now the last partition is not included if the partitioning function value is not within the range. (Bug#51830: http://bugs.mysql.com/bug.php?id=51830) * Partitioning: The insert_id server system variable was not reset following an insert that failed on a partitioned MyISAM table having an AUTO_INCREMENT column. (Bug#50392: http://bugs.mysql.com/bug.php?id=50392) * Partitioning: Foreign keys are not supported on partitioned tables. However, it was possible via an ALTER TABLE statement to set a foreign key on a partitioned table; it was also possible to partition a table with a single foreign key. (Bug#50104: http://bugs.mysql.com/bug.php?id=50104) * Partitioning: GROUP BY queries performed poorly for some partitioned tables. This was due to the block size not being set for partitioned tables, thus the keys per block was not correct, which could cause such queries to be optimized incorrectly. (Bug#48229: http://bugs.mysql.com/bug.php?id=48229) See also Bug#37252: http://bugs.mysql.com/bug.php?id=37252. * Partitioning: REPAIR TABLE failed for partitioned ARCHIVE tables. (Bug#46565: http://bugs.mysql.com/bug.php?id=46565) * Replication: TRUNCATE TABLE performed on a temporary table using the InnoDB storage engine was logged even when using row-based mode. (Bug#51251: http://bugs.mysql.com/bug.php?id=51251) * Replication: When using temporary tables the binary log needs to insert a pseudo-thread ID for threads that are using temporary tables, each time a switch happens between two threads, both of which are using temporary tables. However, if a thread issued a failing statement before exit, its ID was not recorded in the binary log, and this in turn caused the ID for the next thread that tried to do something with a temporary table not to be logged as well. Subsequent replays of the binary log failed with the error Table ... doesn't exist. (Bug#51226: http://bugs.mysql.com/bug.php?id=51226) * Replication: If
MySQL Community Server 5.1.46 has been released
Dear MySQL users, MySQL Community Server 5.1.46, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.46 is recommended for use on production systems. For an overview of what's new in MySQL 5.1, please see http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html For information on installing MySQL 5.1.46 on new servers or upgrading to MySQL 5.1.46 from previous MySQL releases, please see http://dev.mysql.com/doc/refman/5.1/en/installing.html MySQL Server is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ Not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://forge.mysql.com/wiki/Contributing For information on open issues in MySQL 5.1, please see the errata list at http://dev.mysql.com/doc/refman/5.1/en/open-bugs.html The following section lists the changes in the MySQL source code since the previous released version of MySQL 5.1. It may also be viewed online at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-46.html Enjoy! === C.1.2. Changes in MySQL 5.1.46 InnoDB Plugin Notes: * InnoDB Plugin has been upgraded to version 1.0.7. This version is considered of General Availability (GA) quality. The InnoDB Plugin Change History (http://www.innodb.com/doc/innodb_plugin-1.0/innodb-changes.ht ml) may contain information in addition to those changes reported here. In this release, the InnoDB Plugin is included in source and binary distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64, ia64), and generic Linux RPM packages. It also does not work for FreeBSD 6 and HP-UX or for Linux on generic ia64. Functionality added or changed: * There is a new system variable, skip_name_resolve, that is set from the value of the --skip-name-resolve server option. This provides a way to determine at runtime whether the server uses name resolution for client connections. (Bug#37168: http://bugs.mysql.com/bug.php?id=37168) Bugs fixed: * Security Fix: Privilege checking for UNINSTALL PLUGIN was incorrect. (Bug#51770: http://bugs.mysql.com/bug.php?id=51770) * Partitioning: Partition pruning on RANGE partitioned tables did not always work correctly; the last partition was not excluded if the range was beyond it (when not using MAXVALUE). Now the last partition is not included if the partitioning function value is not within the range. (Bug#51830: http://bugs.mysql.com/bug.php?id=51830) * Partitioning: The insert_id server system variable was not reset following an insert that failed on a partitioned MyISAM table having an AUTO_INCREMENT column. (Bug#50392: http://bugs.mysql.com/bug.php?id=50392) * Partitioning: Foreign keys are not supported on partitioned tables. However, it was possible via an ALTER TABLE statement to set a foreign key on a partitioned table; it was also possible to partition a table with a single foreign key. (Bug#50104: http://bugs.mysql.com/bug.php?id=50104) * Partitioning: GROUP BY queries performed poorly for some partitioned tables. This was due to the block size not being set for partitioned tables, thus the keys per block was not correct, which could cause such queries to be optimized incorrectly. (Bug#48229: http://bugs.mysql.com/bug.php?id=48229) See also Bug#37252: http://bugs.mysql.com/bug.php?id=37252. * Partitioning: REPAIR TABLE failed for partitioned ARCHIVE tables. (Bug#46565: http://bugs.mysql.com/bug.php?id=46565) * Replication: TRUNCATE TABLE performed on a temporary table using the InnoDB storage engine was logged even when using row-based mode. (Bug#51251: http://bugs.mysql.com/bug.php?id=51251) * Replication: When using temporary tables the binary log needs to insert a pseudo-thread ID for threads that are using temporary tables, each time a switch happens between two threads, both of which are using temporary tables. However, if a thread issued a failing statement before exit, its ID was not recorded in the binary log, and this in turn caused the ID for the next thread that tried to do something with a temporary table not to be logged as well. Subsequent replays of the binary log failed with the error Table ... doesn't exist. (Bug#51226: http://bugs.mysql.com/bug.php?id=51226) * Replication: If the master was using sql_mode='TRADITIONAL', duplicate key errors were not sent to the slave, which
Re: sql query advise
hi martin,
On Fri, Apr 23, 2010 at 9:50 PM, Martin Gainty mgai...@hotmail.com wrote:
Norm-
I would strongly suggest locking the table before updating..a SELECT for
UPDATE would accomplish that objective:
thanks for the reply and the advise on locking the table
SELECT oppc_id, limitedDate FROM db1.partner_promoCode_record FOR UPDATE;
UPDATE db2.partner_promoCode SET limitedDate =%s WHERE oppc_id =%s
so in essence one can chain sql statements by using the ';' as a separator.
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.
Date: Fri, 23 Apr 2010 20:28:32 +0200
Subject: sql query advise
From: nor...@khine.net
To: mysql@lists.mysql.com
hello,
i have to write a query which has to pull data from a remote mysql
server, modify the table scheme, format some of the fields and then
populate the new database.
i am using MySQLdb which is a python interface to mysql db.
how would i write a query to do this update from from a single
statement that uses tables from both databases?
in essence how to merge these two lines into one statement:
select_promoCode_records = SELECT oppc_id, limitedDate FROM
db1.partner_promoCode
update_promoCode_record = UPDATE db2.partner_promoCode SET
limitedDate =%s WHERE oppc_id =%s
here is a simplified version of what i have so far.
[code]
#!/usr/local/bin/python2.6
# -*- coding: utf-8 -*-
#
import MySQLdb
# connect to the MySQL server and select the databases
dbhost = 'localhost'
dbuser = 'user'
dbpasswd = 'password'
try:
# connect to db
origin = MySQLdb.connect (host = dbhost,
user = dbuser,
passwd = dbpasswd,
)
except MySQLdb.Error, e:
print Error %s % e
sys.exit (1)
select_promoCode_records = SELECT oppc_id, limitedDate FROM
db1.partner_promoCode
update_promoCode_record = UPDATE db2.partner_promoCode SET
limitedDate =%s WHERE oppc_id =%s
org = origin.cursor()
org.execute(select_promoCode_records)
results = org.fetchall()
try:
for row in results:
oppc_id, date = row
org.execute(update_promoCode_record, (int(date), int(oppc_id)))
source.commit()
except:
print Error: enable to put data
# bye!
origin.close()
source.close()
[/code]
thanks
--
¿noʎ uɐɔ uʍop ǝpısdn ǝʇıɹʍ uɐɔ ı - % .join( [
{'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in
,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with
Hotmail. Get busy.
--
¿noʎ uɐɔ uʍop ǝpısdn ǝʇıɹʍ uɐɔ ı - % .join( [
{'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in
,adym,*)uzq^zqf ] )
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
difference between MySQL 5.5.3 and 5.5.4
Hello all, What is the difference between 5.5.4 and 5.5.3? I see that 5.5.3 is available on more platforms. Is one more stable than the other? Thanks -Zardosht -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
