Re: ad hoc replication for 3 X 40 000 rows
However sync's from MS SQL to MYSQL are required every 24 hours after the initial dataload. And this has me puzzled. Advice anyone? Please ;-) If there's a auto-inc key, you can find the last one from the MySQL_table then SELECT ... from MSSQL_table where key found_key. Otherwise, add a field to the tables: seen ENUM('NEW', 'GET', 'GOT') default 'NEW'; At update time: UPDATE MSSQL_table SET seen='GET' WHERE seen='NEW'; SELECT ... FROM MSSQL_table WHERE seen='GET'; INSERT INTO MySQL_table ... UPDATE MSSQL_table SET seen='GOT' WHERE seen='GET'; lather, rinse, repeat. -- Don Readdon_r...@stripped It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. Thanks very much Don, I think that shows the way. However I cannot make any changes to the MSSQL db. So as I only have 3 tables I am thinking of populating a 3 shadow tables in the mysql db. The network to the MSSQL db and the MSQSL db itself is unreliable, hence the need to do an extract. Any quick wins spring to mind? (Or gotchas?) Syd
Difference between MySQL Connector/ODBC 3.51 and ODBC 5.1
Hi, We are currently running a early version of MySQL and are looking to upgrade to the latest version 5. However, we have a number of external applications which use the MySQL Connector/ODBC 3.51. What are the differences between ODBC 3.51 and ODBC 5.1. For example do I need to use ODBC 5.1 if we upgrade our database to version 5 or can I continue using ODBC 3.51. I carried out some limited tests and found that ODBC 3.51 worked fine with MySQL database version 5. However, if I use ODBC 5.1 with MySQL version 5 I have a very strange problem as documented here : http://bugs.mysql.com/bug.php?id=44935. Basically all my queries fail to return any string/varchar values. Thanks for any help. Regards, Neil
Bug? Distinct AS with Order By
I'm not sure what to search on to see if someone has reported this as a bug or if I'm doing something wrong... Generic code to draw a SELECT element on the screen sometimes it ends up like such... SELECT DISTINCT name AS myvalue,name AS mydisp FROM names WHERE name!= ORDER BY myvalue On 4.1.22 this returns A A B B C C D D On 5.0.22 this returns D D D D D D D D The odd thing is that if I remove the order by clause it works fine... It also works fine if I remove the second copy of the column BUT this is generic code so it might also be doing something like productid as myvalue,productname as mydisp where the values are different. Ultimately I can sort the array I end up with but it seems like this should work. Especially since it did in 4.x. Am I crazy? Doing something wrong? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Bug? Distinct AS with Order By
From: Matt Neimeyer m...@neimeyer.org Generic code to draw a SELECT element on the screen sometimes it ends up like such... SELECT DISTINCT name AS myvalue,name AS mydisp FROM names WHERE name!= ORDER BY myvalue On 4.1.22 this returns A A B B C C D D On 5.0.22 this returns D D D D D D D D The odd thing is that if I remove the order by clause it works fine... It also works fine if I remove the second copy of the column BUT this is generic code so it might also be doing something like productid as myvalue,productname as mydisp where the values are different. Ultimately I can sort the array I end up with but it seems like this should work. Especially since it did in 4.x. Am I crazy? Doing something wrong? Doesn't look crazy to me, and it works in 5.0.32 http://www.privatepaste.com/50RvhihKKm Perhaps time to patch that server ... Send instant messages to your online friends http://uk.messenger.yahoo.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: Bug? Distinct AS with Order By
From: Glyn Astill glynast...@yahoo.co.uk Doesn't look crazy to me, and it works in 5.0.32 http://www.privatepaste.com/50RvhihKKm Perhaps time to patch that server ... I've guessed at the table def there, obviously your def may be different and that would surely affect the palanners choice. Perhaps you'd post yours? Send instant messages to your online friends http://uk.messenger.yahoo.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: Passwords not working
On Mon, Oct 19, 2009 at 07:45:11PM -0400, Michael Dykman wrote: The type of password instability you are talking about is pretty much unheard of in MySQL.. Yeah, well, I can have a real black thumb for this sort of thing :-) I'm sure I read about at least two different ways to add passwords. I don't know... usually, it works, but on this particular machine... I just tried to connect asa root again, and it's rejecting the password that I KNOW I set... it's written down. I am not mistyping it. I'm copy-and-pasting from the same text that I used to set the password. I just don't know how to get MySQL to tell me exactly what it's unhappy about. I'm going to go and reset the root password again, and it'll work for a while... but tomorrow, it almost certainly will not work again, and I'll have to go back and reset it *again*. however, reverse DNS resolution is always messing up depending on the network setup. From a console on your database host, how easily can you resolve the hostnames that your The web server and database server (both VMs under VMware ESXi) each have two network interfaces... one public, and one private. The private interfaces are connected to a private VLAN on a virtual switch that is only for these two servers. MySQL only listens on 172.16.1.1, and the web server connects to that IP. On each host, I have a hosts entry for the other. -- *** * John Oliver http://www.john-oliver.net/ * * * *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help! Can't get my MySQL service started!
I'm trying to configure and start a MySQL 5.1 service for the first time, and I'm having trouble. The MySQL Server 5.1 system was installed on a separate drive from the system drive (if that's an issue). My GUI tools are on the C: drive, and the server installation is on the E: drive. Anyway, I'm using mysql administrator to try and create, configure and start a new MySQL database. I've never done this before, and I'm having trouble finding any clear guidance on how to do it online. I think I'm close, but here is the log of my last three attempts to start my new service: SERVICE SETTINGS: Display Name: MySQL Service Description: MySQLESAI CONFIGURATION FILE: Config fielname: E:\Program Files\MySQL\MySQL Server 5.1\my-large.ini (I chose this one at random to have something to start with) Section Name: mysqld PATH TO BINARY: E:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld This all seems pretty clear and ok... so here we go: Attempt 1: Trying to start the server ... Server could not be started. Can't create test file E:\Program Files\MySQL\MySQL Server 5.1\data\mail.lower-test Can't create test file E:\Program Files\MySQL\MySQL Server 5.1\data\mail.lower-test Aborting E:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Shutdown complete I then created a data folder in that directory, and tried to start it again: Attempt 2: Trying to start the server ... Server could not be started. Plugin 'FEDERATED' is disabled. Can't open the mysql.plugin table. Please run mysql_upgrade to create it. I ran mysql_upgrade which seemed to work (gave me no indication there were any problems. Then tried again to start through Administrator: Attempt 3: Trying to start the server ... Server could not be started. Plugin 'FEDERATED' is disabled. Can't open the mysql.plugin table. Please run mysql_upgrade to create it. Recovering after a crash using mysql-bin Starting crash recovery... Crash recovery finished. Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist So this sounds bad. I don't know if this has anything to do with it, but I followed the advice at this site: http://dev.mysql.com/doc/refman/5.1/en/windows-start-service.html and added the MyQL bin path (E:\Program Files\MySQL\MySQL Server 5.1\bin) to my system PATH... any issues there? Any help or guidance would be very very helpful - I need to get this thing running ASAP! Thank you so much in advance - please send any responses to mattlaure...@gmail.com -- Matt -- Matt Laurence mattlaure...@gmail.com http://www.mattlaurence.com/ Senior Interactive Art Director http://www.linkedin.com/in/mattlaurence Bassist, Publicist, Humorist www.secretagentmen.com Pix: www.flickr.com/mattlaurence Follow me at: http://twitter.com/Mattynabib DJ Nabib: http://blip.fm/Mattynabib
MySQL Community Server 5.1.40 has been released
Dear MySQL users, MySQL Community Server 5.1.40, a new version of the popular Open Source Database Management System, has been released. MySQL 5.1.40 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.40 on new servers or upgrading to MySQL 5.1.40 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-40.html Enjoy! === C.1.1. Changes in MySQL 5.1.40 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 S/390, PowerPC and generic ia64. For further details on the InnoDB Plugin see http://dev.mysql.com/doc/refman/5.1/en/news-5-1-38.html Bugs fixed: * Incompatible Change: In binary installations of MySQL, the supplied binary-configure script would start and configure MySQL, even when request help on the command with the --help command-line option. The --help, if provided, will no longer start and install the server. (Bug#30954: http://bugs.mysql.com/30954) * Partitioning: When reorganizing partitions, not all affected subpartitions were removed prior to renaming. One way in which the issue was visible was that attempting to reorganize two partitions into a single partition having the same name as one of the original partitions could lead to a crash of the server. (Bug#47029: http://bugs.mysql.com/47029) See also Bug#45961: http://bugs.mysql.com/45961, Bug#43729: http://bugs.mysql.com/43729. * Partitioning: An online or fast ALTER TABLE of a partitioned table could leave behind temporary files in the database directory. (Bug#46483: http://bugs.mysql.com/46483) * Partitioning: When performing an INSERT ... SELECT into a partitioned table, read_buffer_size bytes of memory were allocated for every partition in the target table, resulting in consumption of large amounts of memory when the table had many partitions (more than 100). This fix changes the method used to estimate the buffer size required for each partition and limits the total buffer size to a maximum of approximately 10 times read_buffer_size. (Bug#45840: http://bugs.mysql.com/45840) * Partitioning: Inserting negative values into an AUTO_INCREMENT column of a partitioned table could lead to apparently unrelated errors or a crash of the server. (Bug#45823: http://bugs.mysql.com/45823) * Partitioning: Unnecessary calls were made in the server code for performing bulk inserts on partitions for which no inserts needed to be made. (Bug#35845: http://bugs.mysql.com/35845) See also Bug#35843: http://bugs.mysql.com/35843. * Replication: Performing ALTER TABLE ... DISABLE KEYS on a slave table caused row-based replication to fail. (Bug#47312: http://bugs.mysql.com/47312) * Replication: BEGIN statements were not included in the output of mysqlbinlog. (Bug#46998: http://bugs.mysql.com/46998) * Replication: When using row-based replication, importing a dump made with mysqldump and replicating a row with an AUTO_INCREMENT column set to 0, with NO_AUTO_VALUE_ON_ZERO active on the master, the row was inserted successfully on the master; however any setting for NO_AUTO_VALUE_ON_ZERO was ignored on the slave. When the AUTO_INCREMENT column was incremented, this caused replication to fail on the slave due to a duplicate key error. In some cases it could also cause the slave to crash. (Bug#45999: http://bugs.mysql.com/45999) * Replication: Concurrent transactions that inserted rows into a table with an AUTO_INCREMENT column could break statement-based or mixed-format replication error 1062 Duplicate entry '...' for key 'PRIMARY' on the slave. This was especially likely to happen when one of the transactions activated a trigger that inserted rows
RE: Help! Can't get my MySQL service started!
Do you have a program called mysql_install_db? It looks like you need that to create the initial mysql system database in your data directory. Regards, Gavin Towey -Original Message- From: Matthew Laurence [mailto:mattlaure...@gmail.com] Sent: Thursday, October 22, 2009 10:24 AM To: mysql@lists.mysql.com Subject: Help! Can't get my MySQL service started! I'm trying to configure and start a MySQL 5.1 service for the first time, and I'm having trouble. The MySQL Server 5.1 system was installed on a separate drive from the system drive (if that's an issue). My GUI tools are on the C: drive, and the server installation is on the E: drive. Anyway, I'm using mysql administrator to try and create, configure and start a new MySQL database. I've never done this before, and I'm having trouble finding any clear guidance on how to do it online. I think I'm close, but here is the log of my last three attempts to start my new service: SERVICE SETTINGS: Display Name: MySQL Service Description: MySQLESAI CONFIGURATION FILE: Config fielname: E:\Program Files\MySQL\MySQL Server 5.1\my-large.ini (I chose this one at random to have something to start with) Section Name: mysqld PATH TO BINARY: E:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld This all seems pretty clear and ok... so here we go: Attempt 1: Trying to start the server ... Server could not be started. Can't create test file E:\Program Files\MySQL\MySQL Server 5.1\data\mail.lower-test Can't create test file E:\Program Files\MySQL\MySQL Server 5.1\data\mail.lower-test Aborting E:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld: Shutdown complete I then created a data folder in that directory, and tried to start it again: Attempt 2: Trying to start the server ... Server could not be started. Plugin 'FEDERATED' is disabled. Can't open the mysql.plugin table. Please run mysql_upgrade to create it. I ran mysql_upgrade which seemed to work (gave me no indication there were any problems. Then tried again to start through Administrator: Attempt 3: Trying to start the server ... Server could not be started. Plugin 'FEDERATED' is disabled. Can't open the mysql.plugin table. Please run mysql_upgrade to create it. Recovering after a crash using mysql-bin Starting crash recovery... Crash recovery finished. Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist So this sounds bad. I don't know if this has anything to do with it, but I followed the advice at this site: http://dev.mysql.com/doc/refman/5.1/en/windows-start-service.html and added the MyQL bin path (E:\Program Files\MySQL\MySQL Server 5.1\bin) to my system PATH... any issues there? Any help or guidance would be very very helpful - I need to get this thing running ASAP! Thank you so much in advance - please send any responses to mattlaure...@gmail.com -- Matt -- Matt Laurence mattlaure...@gmail.com http://www.mattlaurence.com/ Senior Interactive Art Director http://www.linkedin.com/in/mattlaurence Bassist, Publicist, Humorist www.secretagentmen.com Pix: www.flickr.com/mattlaurence Follow me at: http://twitter.com/Mattynabib DJ Nabib: http://blip.fm/Mattynabib The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org