Re: Move records to archive server?
Have a look at the Merge engine. On Sun, Oct 18, 2009 at 10:31 PM, Allen Fowler allen.fow...@yahoo.comwrote: Hello, I have a Python application that is using MySQL to store records of transactions about 3 tables with ~1k records each. How can I periodically copy the records off the production on to an archive server? I would like to this for two reasons: 1) To run data-mining queries on a copy of the live dataset without the risk of overloading the production server. 2) To maintain an addional set of historical tables that contain all the records the system has ever had, even after they are dropped from the live tables. How should I go about doing this? Thank you, :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- That which does not kill you was simply not permitted to do so for the purposes of the plot.
update fields with a prefix - ?? how to
dear all, a novice here quickie regarding query syntax - is it possible to take fields values from one column and update the same column with new values like this: prefix_OldValue column: one, two, three - column: prefix_one, prefix_two, ... can this be done with one query and with on use of abstractions, no php or similar scripting techincs? cheers, lejeczek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: update fields with a prefix - ?? how to
Something in the ilk of update *table* set *field* = concat(prefix_, *field*) where *condition * should do the trick. On Mon, Oct 19, 2009 at 4:56 PM, lejeczek pelj...@yahoo.co.uk wrote: dear all, a novice here quickie regarding query syntax - is it possible to take fields values from one column and update the same column with new values like this: prefix_OldValue column: one, two, three - column: prefix_one, prefix_two, ... can this be done with one query and with on use of abstractions, no php or similar scripting techincs? cheers, lejeczek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- That which does not kill you was simply not permitted to do so for the purposes of the plot.
RE: Load Data Infile quirk
At 05:40 AM 10/18/2009, John wrote: Mike, What behaviour you experience depends to some extent on what storage engine you are using and on what other non-unique indexes you have on the tables. With LOAD DATA INFILE on empty MyISAM tables all non-unique indexes are created in a separate batch which makes it much faster if you have a lot of indexes. Ok, I thought that ALL indexes would be rebuilt later, including my primary index, and one unique index I have on the table. I must have misread that in the manual. Thanks. From memory you can create the indexes faster by turning them off with 'ALTER TABLE tablename DISABLE KEYS' before the 'LOAD DATA INFILE' command and then using 'ALTER TABLE tablename ENABLE KEYS' to re-create the indexes after the LOAD DATA INFILE completes. But Disable Keys has no affect on primary or unique indexes. So the only way for me to speed this up on loading data into empty tables is to remove all indexes and build them after the data has been loaded. That should save me 30% on the load times. Mike Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: 17 October 2009 22:49 To: mysql@lists.mysql.com Subject: Load Data Infile quirk I'm trying to speed up Load Data Infile and after some experimenting have noticed this qwirk. BTW, all of the tables used below are empty and have identical table structures. The value being loaded into the primary key column is 'NULL'. Test1: 246 seconds to run Load Data Infile into a table (Table1) with 1 primary autoinc column, and 2 compound keys. Test2: 69 seconds to run Load Data Infile into similar table (Table2) with no keys 111 seconds to rebuild the missing keys in Table2 69+111=180 seconds for Table2 compared to 246 seconds for Table1. Now I thought when using Load Data Infile on an empty table it would rebuild *all* of the keys AFTER the data has been loaded. This may not be the case. I suspect the extra time for Test1 is caused by the Load Data building the primary key as the data is being loaded. Can someone confirm this? If so, then when loading data into an empty table, it is always going to be faster to remove the keys then load the data, then add the keys. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.422 / Virus Database: 270.14.20/2441 - Release Date: 10/16/09 18:39:00 -- 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
RE: insert random number into table
Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -Original Message- From: Scott Haneda [mailto:talkli...@newgeo.com] Sent: Friday, October 16, 2009 2:44 PM To: Jerry Schwartz Cc: Ray; mysql@lists.mysql.com Subject: Re: insert random number into table I always maintain a timestamp in my random numbers. As long as my precision is higher than my requests per second, wouldn't I be safe from collisions? Assuming a time machine is not invented. [JS] As long as... is very dangerous. Even if your mean transaction arrival rate is okay, you can't rule out the possibility of two transactions arriving at the same time (within the precision of your clock). Admittedly, we're getting theoretical here; but I always like to come at things from the theoretical side, since it is so easy to overlook your assumptions if you come at it from the opposite direction. -- Scott Iphone says hello. On Oct 16, 2009, at 11:29 AM, Jerry Schwartz jschwa...@the-infoshop.com wrote: JS] Just remember that the pseudo-random numbers that come out of a computer, regardless of where or how they are generated, are not guaranteed to be unique. Although the odds of a collision are slim, my philosophy has always been if it's possible, it will happen - in the middle of your vacation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Benetl, a free ETL tool for files using postgreSQL, is out in version 3.2
Dear all, Benetl, a free ETL tool for files using MySQL, is out in version 3.2. This new version is now supporting Java SE 6 and using memory arguments for JVM. You can freely download it at : www.benetl.net You can learn more about ETL tools at: http://en.wikipedia.org/wiki/Extract,_transform,_load Thanks for your interest. Regards, -- Benoît Carpentier www.benetl.net Founder of Benetl Java project manager -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Passwords not working
I have a problem with MySQL passwords... I set them, write them down... and they stop working. I have to go in and manually reset them. Right now, I have a database that, even after resetting the password, I still cannot access it. /var/log/mysql.log doesn't give me any useful information. How can I get MySQL to tell me what it's unhappy about, or get more information from the client other than it just didn't work? I also have problems with MySQL resolving names, or not resolving names, or ??? I usually add 'user'@'ip.address' and 'user'@'host.name' But, more and more often, I've had to put skip-name-resolve in my.cnf, but with my current problem, I'm still seeing that 'user'@'host-name' is being rejected, even when I use -h ip.address on the command line And when I add those two users, and go to reset passwords, it doesn't want to let me specify 'user'@'ip.address' or 'user'@'host.name' but just 'user' I *think* it's resetting the password for both... the hashes are always the same. But I just don't know. What am I missing? -- *** * 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
Re: Passwords not working
The type of password instability you are talking about is pretty much unheard of in MySQL.. 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 client is presenting? What is your network setup? Not the safest of practices, but for dev accounts, I usually create one for user@'%' and sometimes one one for u...@localhost if needed and that works very well for me.. - michael dykman On Mon, Oct 19, 2009 at 7:30 PM, John Oliver joli...@john-oliver.net wrote: I have a problem with MySQL passwords... I set them, write them down... and they stop working. I have to go in and manually reset them. Right now, I have a database that, even after resetting the password, I still cannot access it. /var/log/mysql.log doesn't give me any useful information. How can I get MySQL to tell me what it's unhappy about, or get more information from the client other than it just didn't work? I also have problems with MySQL resolving names, or not resolving names, or ??? I usually add 'user'@'ip.address' and 'user'@'host.name' But, more and more often, I've had to put skip-name-resolve in my.cnf, but with my current problem, I'm still seeing that 'user'@'host-name' is being rejected, even when I use -h ip.address on the command line And when I add those two users, and go to reset passwords, it doesn't want to let me specify 'user'@'ip.address' or 'user'@'host.name' but just 'user' I *think* it's resetting the password for both... the hashes are always the same. But I just don't know. What am I missing? -- *** * 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=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift -- 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, 19 Oct 2009 16:30:47 -0700 John Oliver joli...@john-oliver.net wrote: I have a problem with MySQL passwords... I set them, write them down... and they stop working. I have to go in and manually reset them. Right now, I have a database that, even after resetting the password, I still cannot access it. /var/log/mysql.log doesn't give me any useful information. How can I get MySQL to tell me what it's unhappy about, or get more information from the client other than it just didn't work? I also have problems with MySQL resolving names, or not resolving names, or ??? I usually add 'user'@'ip.address' and 'user'@'host.name' But, more and more often, I've had to put skip-name-resolve in my.cnf, but with my current problem, I'm still seeing that 'user'@'host-name' is being rejected, even when I use -h ip.address on the command line And when I add those two users, and go to reset passwords, it doesn't want to let me specify 'user'@'ip.address' or 'user'@'host.name' but just 'user' I *think* it's resetting the password for both... the hashes are always the same. But I just don't know. What am I missing? Are you accessing MySQL from the same host? If so, you don't need the -h option unless that's the only entry in your grant table under that username (i.e. 'user'@'ip-address'). Can you give us an example of how you're setting the username and their permissions? Here's a typical example that gives access to an entire database to a single user provided they're accessing it on the same host: GRANT ALL on database-name.* to 'user'@'localhost' identified by 'foobar'; The username, password AND hostname have to match up for authentication to be successful: 'user'@'localhost' may be different than 'user'@'ip-address' even if they're intended to be the same person. -- Greg Maruszeczka Office: 250.412.9651 || Mobile: 250.886.4577 Skype: websage.ca || GTalk IM: gmarus http://websage.ca GnuPG-ID: 0x4309323E, http://pgp.mit.edu -- 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
someone probably installed mysql for DHCP address e.g 192.168.fu.bar then as luck would have it the IP address changed if you pull all network connections everyone on that box should be able to access mysql Salutations de l'état du chômage Martin Gainty __ Note de déni et de confidentialité 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: Mon, 19 Oct 2009 16:48:36 -0700 From: listm...@websage.ca To: mysql@lists.mysql.com Subject: Re: Passwords not working On Mon, 19 Oct 2009 16:30:47 -0700 John Oliver joli...@john-oliver.net wrote: I have a problem with MySQL passwords... I set them, write them down... and they stop working. I have to go in and manually reset them. Right now, I have a database that, even after resetting the password, I still cannot access it. /var/log/mysql.log doesn't give me any useful information. How can I get MySQL to tell me what it's unhappy about, or get more information from the client other than it just didn't work? I also have problems with MySQL resolving names, or not resolving names, or ??? I usually add 'user'@'ip.address' and 'user'@'host.name' But, more and more often, I've had to put skip-name-resolve in my.cnf, but with my current problem, I'm still seeing that 'user'@'host-name' is being rejected, even when I use -h ip.address on the command line And when I add those two users, and go to reset passwords, it doesn't want to let me specify 'user'@'ip.address' or 'user'@'host.name' but just 'user' I *think* it's resetting the password for both... the hashes are always the same. But I just don't know. What am I missing? Are you accessing MySQL from the same host? If so, you don't need the -h option unless that's the only entry in your grant table under that username (i.e. 'user'@'ip-address'). Can you give us an example of how you're setting the username and their permissions? Here's a typical example that gives access to an entire database to a single user provided they're accessing it on the same host: GRANT ALL on database-name.* to 'user'@'localhost' identified by 'foobar'; The username, password AND hostname have to match up for authentication to be successful: 'user'@'localhost' may be different than 'user'@'ip-address' even if they're intended to be the same person. -- Greg Maruszeczka Office: 250.412.9651 || Mobile: 250.886.4577 Skype: websage.ca || GTalk IM: gmarus http://websage.ca GnuPG-ID: 0x4309323E, http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail: Trusted email with Microsoft’s powerful SPAM protection. http://clk.atdmt.com/GBL/go/177141664/direct/01/
