Re: Issue with timestamp columns while working with MySQL load data in file
On 2015/04/12 08:52, Pothanaboyina Trimurthy wrote: The problem is , as mentioned the load data is taking around 2 hours, I have 2 timestamp columns for one column I am passing the input through load data, and for the column DB_MODIFIED_DATETIME no input is provided, At the end of the load data I could see only one timestamp value for both the columns, though the load data takes 2 hours to load the data. Can any one explain how exactly the load data infile works, and why only a single timestamp is inserting for all 1 million records though the load data taking around 2 hours. Look up function SYSDATE: all other times timestamps are kept in step, beginning at the time when the transaction begins. Your described effect is intended. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Issue with timestamp columns while working with MySQL load data in file
Hi All, I am facing an issue with timestamp columns while working with MySQL load data in file, I am loading around a million records which is taking around 2 hours to complete the load data. Before get into more details about the problem, first let me share the table structure. CREATE TABLE `test_load_data` ( `id1` int(11) DEFAULT NULL, `col10` varchar(255) DEFAULT NULL, `DB_CREATED_DATETIME` datetime DEFAULT NULL, `DB_MODIFIED_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; LOAD DATA LOCAL INFILE '/x.dat' INTO TABLE test_load_data FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (id1,col10,@DB_CREATED_DATETIME) SET DB_CREATED_DATETIME = NOW() ; mysql select DB_CREATED_DATETIME,DB_MODIFIED_DATETIME,count(1) from test_load_data group by DB_CREATED_DATETIME,DB_MODIFIED_DATETIME; +-+--+--+ | DB_CREATED_DATETIME | DB_MODIFIED_DATETIME | count(1) | +-+--+--+ | 2015-04-07 10:08:09 | 2015-04-07 10:08:09 | 100 | +-+--+--+ 1 row in set (2.14 sec) The problem is , as mentioned the load data is taking around 2 hours, I have 2 timestamp columns for one column I am passing the input through load data, and for the column DB_MODIFIED_DATETIME no input is provided, At the end of the load data I could see only one timestamp value for both the columns, though the load data takes 2 hours to load the data. Can any one explain how exactly the load data infile works, and why only a single timestamp is inserting for all 1 million records though the load data taking around 2 hours. Thank you in advance. -- Thanks, Trimurthy P Mobile : +91 97397 64298 http://mysqlinternals.blogspot.in/ https://www.linkedin.com/pub/trimurthy-pothanaboyina/5a/9a9/96b
Re: mysql load balancing
Miguel, On Fri, Dec 25, 2009 at 4:56 PM, Miguel Angel Nieto cor...@miguelangelnieto.net wrote: Load balancing, or high availability? I do not think there is anything good and simple AND generic out of the box. As previous posters have noted, you generally have to build something on top of other tools. Hi, I have the HA solved with MMM. Now, I want load balacing, sending read queries to slaves and write queries to masters. I read about mysql proxy, sqlrelay... but I didn't know the difference betwen them, and I think the best way to do this is to split in your application. The magical read/write split, done in a way that's invisible to the application, is almost invariably a source of problems when there is replication lag (which there always is). The application needs to be aware of replication lag and must know how to handle it or when it's OK to ignore it. Most applications cannot simply let a dumb intermediate layer handle it for them, because there are always cases when lag is not permissible at all, mixed with cases where lag is OK, and the application needs to make the decision. -- Baron Schwartz Percona Inc: Services and Support for MySQL http://www.percona.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: mysql load balancing
Miguel, On Sun, Dec 20, 2009 at 6:21 PM, Miguel Angel Nieto cor...@miguelangelnieto.net wrote: Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... Load balancing, or high availability? I do not think there is anything good and simple AND generic out of the box. As previous posters have noted, you generally have to build something on top of other tools. - Baron -- Baron Schwartz Percona Inc: Services and Support for MySQL http://www.percona.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: mysql load balancing
Load balancing, or high availability? I do not think there is anything good and simple AND generic out of the box. As previous posters have noted, you generally have to build something on top of other tools. Hi, I have the HA solved with MMM. Now, I want load balacing, sending read queries to slaves and write queries to masters. I read about mysql proxy, sqlrelay... but I didn't know the difference betwen them, and the possible problems of each tool (latency for example). If anyone, like Pascal, have experience with those kind of tools, please, share them :P -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- 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 load balancing
Baron: Load balancing, or high availability? I do not think there is anything good and simple We use MySQL master-master replication to keep geographically separated databases in sync. It works very well. We built a management layer on top of it to allow the endpoints (Web servers) to talk to either database in case one fails. That is not necessary but it adds a extra layer of proctection. Neil -- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net Host your MySQL database on a CentOS virtual server for $25/mo Unmetered bandwidth = no overage charges, 7 day free trial -- 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 load balancing
El día 22 de diciembre de 2009 13:44, Miguel Angel Nieto cor...@miguelangelnieto.net escribió: It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. Thank you. I have read about LVS and keepalived but I can't see the difference between them. Are they the same thing? I want the load balancing for my replicated servers. I suppose that LVS can't distinguish between inserts and selects (to send queries to the master o slave server). I do not know about keepalived, but it seems to provide the same service that heartbeat does: user-level awareness of the failure of a machine or a service. LVM, however, does kernel module, ip-level, load balancing and automatic failure clustering. The big confusion with those apps is that most of them are extensible and combinable to achieve the same goal. About the read/write balancing (which is a good question, and that is why I asked what you were intending to do on the other end), I have seen it done **only at application level**, not transparently, because of the problems derived from asynchronous replication (lag between master writes and slaves see the data). I am sorry I cannot help you, but please, share here if you found something useful AND with good performance. Merry Xmas! -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- 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 load balancing
Hi, I've had quite a bit of success deploying mysql-proxy in my clients infrastructure. The standard read/write splitting is quite easy to achieve - but I also add some custom code to match with specific case (connection pooling, x second to the same master after a write/update, specific command goes to this specific 'async' server...). Since the 'rewrite' language is LUA its is quite easy to extend the feature. I did complete some benchmarks of those systems for a client (overhead cost) but I don't know if they are ready to share them (and I don't have access to them since I'm mobile right now). Ill inquire. P. On 12/24/09, Jaime Crespo Rincón jcre...@warp.es wrote: El día 22 de diciembre de 2009 13:44, Miguel Angel Nieto cor...@miguelangelnieto.net escribió: It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. Thank you. I have read about LVS and keepalived but I can't see the difference between them. Are they the same thing? I want the load balancing for my replicated servers. I suppose that LVS can't distinguish between inserts and selects (to send queries to the master o slave server). I do not know about keepalived, but it seems to provide the same service that heartbeat does: user-level awareness of the failure of a machine or a service. LVM, however, does kernel module, ip-level, load balancing and automatic failure clustering. The big confusion with those apps is that most of them are extensible and combinable to achieve the same goal. About the read/write balancing (which is a good question, and that is why I asked what you were intending to do on the other end), I have seen it done **only at application level**, not transparently, because of the problems derived from asynchronous replication (lag between master writes and slaves see the data). I am sorry I cannot help you, but please, share here if you found something useful AND with good performance. Merry Xmas! -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=pascal.char...@gmail.com -- Sent from my mobile device -- Pascal Charest, skype: pascal.charest Free software consultant @ Laboratoire Phoenix http://www.labsphoenix.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: mysql load balancing
2009/12/21 Miguel Angel Nieto cor...@miguelangelnieto.net: Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... What do you prefer? Hi, The solutions I have heard most from our customers (in production) are not mysql-specific: 1) Simple, not load-aware *DNS balancing* for simple applications without fault-tolerance: http://en.wikipedia.org/wiki/Round_robin_DNS 2) More complex and customizable *Linux Virtual Server*, with integrated heartbeat and session aware: http://www.linuxvirtualserver.org/ It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. I hope that helps. Regards, -- Jaime Crespo MySQL Java Instructor Warp Networks http://warp.es -- 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 load balancing
Hi, El día 22 de diciembre de 2009 10:14, Jaime Crespo Rincón jcre...@warp.es escribió: 2009/12/21 Miguel Angel Nieto cor...@miguelangelnieto.net: Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... What do you prefer? Hi, The solutions I have heard most from our customers (in production) are not mysql-specific: 1) Simple, not load-aware *DNS balancing* for simple applications without fault-tolerance: http://en.wikipedia.org/wiki/Round_robin_DNS 2) More complex and customizable *Linux Virtual Server*, with integrated heartbeat and session aware: http://www.linuxvirtualserver.org/ It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. Thank you. I have read about LVS and keepalived but I can't see the difference between them. Are they the same thing? I want the load balancing for my replicated servers. I suppose that LVS can't distinguish between inserts and selects (to send queries to the master o slave server). See you. -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql load balancing
Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... What do you prefer? What are the benefits and bugs? :) Thank you. -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- 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 - load data file question..
can u please show use the content of the test.csv file. Also is comapny
name a single column or two different columns
If its two different columns than try this
load data file '/foo/test.csv' into table abc.test fields terminated by ','
(company,name);
On 6/28/08, bruce [EMAIL PROTECTED] wrote:
Hi..
I've got an issue with doing a Load data file cmd..
my test text tbl has a column named company name i'm trying to figure out
how to use the load data file cmd, to be able to extract the company name
col...
when i do:
load data file '/foo/test.csv' into table abc.test (company name);
load data file '/foo/test.csv' into table abc.test ('company name');
i get errs for both of the above...
i can get this to work if i have a column with a single name...
can't find any pointers via google..
any pointers/thoughts/etc...
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql - load data file question..
Hi..
I've got an issue with doing a Load data file cmd..
my test text tbl has a column named company name i'm trying to figure out
how to use the load data file cmd, to be able to extract the company name
col...
when i do:
load data file '/foo/test.csv' into table abc.test (company name);
load data file '/foo/test.csv' into table abc.test ('company name');
i get errs for both of the above...
i can get this to work if i have a column with a single name...
can't find any pointers via google..
any pointers/thoughts/etc...
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with MySQL load
Hi all, I've a server where between 1-2 there comes a high load from less than 1k queries there's a jump to 60+k queries. It all started after adding more RAM to the server. Anyone seen this before ? Regards, Toke Herkild -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed Pauley II wrote: Continuent's m/cluster will not work for me as it does not allow replication across a WAN. Yeah, known problem... We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. Why do you need a backup site to write things to your master ? Or did I got wrong ? I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. Let me see if I got it right: you have two sites, one master on each, one slave on each., and you want both master to replicate to the other and both slaves to receive data from them as well. Right ? If so, MySQL does not support multi-master setup. As far as I've heard there is no direct move into that direction from MySQL developers, although a voting system for a new master among one of the slaves can happen in the near future. What may work is to have only one master on one of your sites and both sites update the same master (reducing speed for the slave site) and in the case of failure you switch them (manually or with some tool). hope that helps, --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Renato Golin wrote: Ed Pauley II wrote: Continuent's m/cluster will not work for me as it does not allow replication across a WAN. Yeah, known problem... We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. Why do you need a backup site to write things to your master ? Or did I got wrong ? This is another geographical location with automatic failover if there is a problem, network, hardware etc. with the primary location. When the problem is corrected, or corrects itself the traffic is automatically sent back to the primary location. Without 2-way replication data would be lost. We have been doing this for since MySQL 4.0 was released. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. Let me see if I got it right: you have two sites, one master on each, one slave on each., and you want both master to replicate to the other and both slaves to receive data from them as well. Right ? If so, MySQL does not support multi-master setup. It is not a multi-master setup. The master at each location is both master and slave to each other. The slaves are only slaves to the master in their respective locations. My problem is really with how to load balance the slaves at each location. As far as I've heard there is no direct move into that direction from MySQL developers, although a voting system for a new master among one of the slaves can happen in the near future. What may work is to have only one master on one of your sites and both sites update the same master (reducing speed for the slave site) and in the case of failure you switch them (manually or with some tool). hope that helps, --renato -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed Pauley II wrote: This is another geographical location with automatic failover if there is a problem, network, hardware etc. with the primary location. When the problem is corrected, or corrects itself the traffic is automatically sent back to the primary location. Without 2-way replication data would be lost. We have been doing this for since MySQL 4.0 was released. Ok, If that's your problem, they might have something to you in the near future with the new master election. It'll probably work on a 2-way replication when the old master is restored. While this is not out yet, what you might do is what I did back with 3.23 which is: bring old restored master up as slave, wait for all changes replicate, shutdown temp master, set it as slave, bring old master as master and bring temp master as slave, restoring production scheme. It is not a multi-master setup. The master at each location is both master and slave to each other. The slaves are only slaves to the master in their respective locations. My problem is really with how to load balance the slaves at each location. Load balance them using the local master or using both slaves as a load balance ? I'd rather go for the first one because the second one will raise more problems than solve, specially related to network delays. If, in the first case, one master fails you can issue a change master to the other master and it'll keep running. As you'll only have the slave there will be no load balancing on that site. --renato -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed Pauley II wrote: This is another geographical location with automatic failover if there is a problem, network, hardware etc. with the primary location. When the problem is corrected, or corrects itself the traffic is automatically sent back to the primary location. Without 2-way replication data would be lost. We have been doing this for since MySQL 4.0 was released. I don't think you're describing it properly. Do you mean that both masters are not master for the same database? In that case you could make them slaves of each other, but not for the same db. At least, not as I understand it. (And I have set up Mysql as active-active at 2 geographically diverse colos.) It is not a multi-master setup. The master at each location is both master and slave to each other. The slaves are only slaves to the master in their respective locations. My problem is really with how to load balance the slaves at each location. We use a Netscaler 9000 in front of our slaves but you can use any load balancing appliance really. We used to use an Alteon. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Peter Zaitsev wrote: On Fri, 2006-08-04 at 15:54 -0400, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. As some people mentioned there is Continuent solution, this is what was Emic networks previously. If you're building solution on your own such as master and number of slaves there are plenty of things to think, regarding load balancing, for example if replication breaks for any reason of falls behind on one of the slaves you might want to kick it up. For very basic setup you even can use DNS for load balancing, which does not solve many of the problems describe. The same kind of simple load balancing is build in MySQL JDBC Driver. In general everyone seems to implement something on their own, working well for their application. Continuent's m/cluster will not work for me as it does not allow replication across a WAN. We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. I have not been able to find an appliance that will balance the reads for me. I have looked into possible solutions such as Sequoia, which I know would require a different setup. Is anyone actually using Sequoia? Does anyone use an appliance for load balancing MySQL? LVS seems like a good possibility but I would rather have an out-of-box solution since I will be repeating this setup at a remote location. -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed Pauley II wrote: Continuent's m/cluster will not work for me as it does not allow replication across a WAN. We have an offsite backup that needs to be in the replication (2-way to make switching back and forth easy) chain. I am thinking of a master, slave setup at each location where the masters continue to replicate both ways and then each replicates to it's own slaves. I would like to load balance these slaves on each end. I have not been able to find an appliance that will balance the reads for me. I have looked into possible solutions such as Sequoia, which I know would require a different setup. Is anyone actually using Sequoia? Does anyone use an appliance for load balancing MySQL? LVS seems like a good possibility but I would rather have an out-of-box solution since I will be repeating this setup at a remote location. Sorry, did not try Sequoia so can't tell how well it works -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Load Balancing
I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
You can have a simple LVS setup running with a plugin from Nagios, check_mysql, which will connect to the mysql daemon and run a status query. If you want anything more than that you most likely will have to write a custom check plugin (shouldn't be that hard). LVS works nicely as a mysql loadbalancer in my experience. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 4 Aug 2006, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
Ed, in Jeremy Zawodny's (excellent) book High Performance MySQL, there is a chapter on load balancing - though it's a bit more of a theoretical discussion than a how-to. There are a couple of commercial products mentioned briefly - Veritas and EMIC Networks. One idea he presents might work for you, since you're already doing HTTP load balancing: have each HTTP server talk to a mysql instance on localhost, with the mysql instance configured as a replication slave. Depends on your situation but that could be elegantly simple - assuming you have a farm of servers, you just add mysql to your standard build image, rebuild them and start replicating. Roll out your app talking to localhost and you've got it. Of course rarely are things read-only; for something that writes as well as reads, you'll likely have to do some re-coding so that db writes go to the master and reads go to localhost. Then you have possible problems if you need reads to show the results of writes right away. It gets fun! Sound like this might work for you? Dan On 8/4/06, Ed Pauley II [EMAIL PROTECTED] wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
I should mention that the below concerns read-only daemons, Dan's post reminded me of that. Having multiple masters in a load balanced environment is extremely difficult to do right. I would wager that for most applications, at least internet related, you'll have a much higher read-to-write ratio where you can get by having only one master while using multiple replicated slaves (even for really high traffic sites). Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 4 Aug 2006, Atle Veka wrote: You can have a simple LVS setup running with a plugin from Nagios, check_mysql, which will connect to the mysql daemon and run a status query. If you want anything more than that you most likely will have to write a custom check plugin (shouldn't be that hard). LVS works nicely as a mysql loadbalancer in my experience. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 4 Aug 2006, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. -Ed -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing
On Fri, 2006-08-04 at 15:54 -0400, Ed Pauley II wrote: I am looking into a scale-out solution for MySQL. I have read white papers and searched the web but I can't find a load balancer that claims to work well for MySQL. MySQL's white paper shows NetScaler in the scale-out stack but nothing on Citrix.com mentions MySQL. I also read that friendster wrote a custom script for NetScaler to work in a MySQL environment. I would rather not have to do that. Is there an out-of-box solution for load balancing MySQL. My understanding is that MySQL is a little more complicated than HTTP load balancing, which we already do with Coyote Point Equalizers. I have thought about LVS. Has anyone had any experience with load balancing MySQL? Any recommendations? Thanks in advance. As some people mentioned there is Continuent solution, this is what was Emic networks previously. If you're building solution on your own such as master and number of slaves there are plenty of things to think, regarding load balancing, for example if replication breaks for any reason of falls behind on one of the slaves you might want to kick it up. For very basic setup you even can use DNS for load balancing, which does not solve many of the problems describe. The same kind of simple load balancing is build in MySQL JDBC Driver. In general everyone seems to implement something on their own, working well for their application. -- Peter Zaitsev, MySQL Performance Expert MySQL Performance Blog - http://www.mysqlperformanceblog.com MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load and unload immediately
Hi! Server system SBS (Novell Small Business suite) 6.5 sp 1 with MySQL ver. 4.0.15a, PHP 4.2.3, all of them on same machine. After restart server command the MySQL server is loading and unloading immediately. The error file : MYSQL:/data/WEB.err contain the following text: sys:/mysql/bin/mysqld.nlm: Character set 'UTF8' is not a compiled character set and is not specified in the 'sys:/mysql/share/charsets/Index' file The log file : MYSQL:/data/WEB.safe contain the following text: MySQL Server 4.0.16a, for pc-netware (i686) address : 0.0.0.0 port : 3306 daemon : sys:/mysql/bin/mysqld base directory : sys:/mysql data directory : MYSQL:/data pid file : MYSQL:/data/WEB.pid error file : MYSQL:/data/WEB.err log file : MYSQL:/data/WEB.safe mysql started: 17 Mar 2006 12:54:49 mysql stopped: 17 Mar 2006 12:54:49 Please help Tia Nanu
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Kevin A. Burton wrote: Jason J. W. Williams wrote: Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Define DoS? - Denial of Service... - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Ian Sales (DBA) wrote: Kevin A. Burton wrote: Define DoS? - Denial of Service... ug... Thats not what I meant... I mean what type of behavior were you noticing? Just all connections being occupied on the server? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Load Balancing w/ Alteons...Half Open Connections
Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Best Regards, Jason Williams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Load Balancing w/ Alteons...Half Open Connections
Jason J. W. Williams wrote: Has anyone ever had a problem with Alteon load balancers leaving the MySQL connections half open? After about a minute of heavy use the Alteon has completely DoS'd our MySQL servers. I know we must be doing something wrong...just not sure what. Any help is greatly appreciated! Define DoS? Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Load on server
Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED]
RE: MySQL Load on server
If I understand correctly, this is what you want: SHOW PROCESSLIST --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 11:40 PM To: MySQL Masters Subject: MySQL Load on server Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Load on server
Dear Tom, Thank you very much. Is there a way to log all the processes on the MySQL server to analyze later. At 09:48 AM 1/5/2005, Tom Crimmins wrote: If I understand correctly, this is what you want: SHOW PROCESSLIST --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 04, 2005 11:40 PM To: MySQL Masters Subject: MySQL Load on server Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED] Regards Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Load on server
In you're my.cnf file (in /etc by default on a linux box) add under [mysqld] the option log for text logging. for example [mysqld] log=mylogfile Whatever user mysqld is running as must have write perms to the log file. You can then anaylze this as it will contain connections and queries. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath Sent: Wednesday, January 05, 2005 12:18 AM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: RE: MySQL Load on server Dear Tom, Thank you very much. Is there a way to log all the processes on the MySQL server to analyze later. At 09:48 AM 1/5/2005, Tom Crimmins wrote: If I understand correctly, this is what you want: SHOW PROCESSLIST --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Sheni R. Meledath Sent: Tuesday, January 04, 2005 11:40 PM To: MySQL Masters Subject: MySQL Load on server Hello: We have multiple web sites hosted on a virtual web server. Many of the web sites are using MySQL databases. Recently we are having problems with the CPU load due to mysql processes. We are not able to track the corrupted database or script (PHP). There are many databases on the server. CPU Load on server CPU states: % user, % nice, % system, % interrupt, % idle Mem: 654M Active, 82M Inact, 142M Wired, 37M Cache, 112M Buf, 90M Free Swap: 1024M Total, 226M Used, 798M Free, 22% Inuse PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 28181 cyber001 50 0 32088K 7180K RUN 282:01 71.29% 71.29% mysqld Is there any way to track the processes on the MySQL databases? Please suggest a solution to fix this problem. Thank You. Regards Sheni R Meledath [EMAIL PROTECTED] Regards Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing...
We have fail-over using Linux Virtual Server, now upgrading to Red Hat Cluster Suite. We do not implement load-balancing. Here is why. In order to have full true load balancing, you need to have two or more MySQL database server replicating data to each other in real time. Currently your only choice is master-master replication. master-master replication is not fast enough for real-time updates for a load-balanced environment. It is good enough for fail-over which is what we currently implement. You can still have load-balancing in a distributed fashion. If you have two MySQL database servers for your cluster, and you designate half your databases for one server and half for the other, technically you are implementing load balancing as your distributing your data among two servers. Although this is not really the best practice. However in this setup, each server can be a fail-over for the other. So if one server fails the other takes over as master for it's databases. fail-over recovery. This is one issue worth considering. If your MySQL database server comes back online, LVS (Linux Virtual Server) wants to immediately throw it back in as master/primary. However, once the server is up, it needs time to get back up to date from the other master. So for recovery, you will need to perform this manually and not let LVS do this automatically. Bring the server back online, allow time for the replication to complete, then go to the LVS and set the server as active primary. -RG Kevin A. Burton wrote: Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing...
We also use Linux Virtual Server for load balancing, but only on our read-only cluster. Our current levels of RW traffic do not demand more than one machine. Russell E Glaue wrote: We have fail-over using Linux Virtual Server, now upgrading to Red Hat Cluster Suite. We do not implement load-balancing. Here is why. In order to have full true load balancing, you need to have two or more MySQL database server replicating data to each other in real time. Currently your only choice is master-master replication. master-master replication is not fast enough for real-time updates for a load-balanced environment. It is good enough for fail-over which is what we currently implement. You can still have load-balancing in a distributed fashion. If you have two MySQL database servers for your cluster, and you designate half your databases for one server and half for the other, technically you are implementing load balancing as your distributing your data among two servers. Although this is not really the best practice. However in this setup, each server can be a fail-over for the other. So if one server fails the other takes over as master for it's databases. fail-over recovery. This is one issue worth considering. If your MySQL database server comes back online, LVS (Linux Virtual Server) wants to immediately throw it back in as master/primary. However, once the server is up, it needs time to get back up to date from the other master. So for recovery, you will need to perform this manually and not let LVS do this automatically. Bring the server back online, allow time for the replication to complete, then go to the LVS and set the server as active primary. -RG Kevin A. Burton wrote: Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing...
Kevin A. Burton wrote: Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing we're using an Alteon 2424 load balancer. we have 5 mysql slave machines behind it. it works very well, except for the normal problems with mysql replication. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load balancing...
Was curious what people on the list are using for load balancing.. there are a number of techniques here but it would be interesting if people could share some real-world experiences HTTP load balancing is pretty well understood but there's not a bunch out there on MySQL load balancing Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing
Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. Good idea. You may also want to check how query caching helps your application (look at the query cache stats in SHOW STATUS with different cache sizes), see if some dynamic pages could be converted to periodically re-generated static, and check if you can pool the writes (eg. if you are logging page hits, append to a file instead of writing to db, and run load data infile once a minute) -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL load balancing
Hi, Currently our web infrastructure has one main MySQL server, to which connections are made by (mostly) mod_perl running under Apache (on 3 different machines), and several other custom-built application servers on other servers (which have persistant connections, and do both reads and writes). This machine answers between 300 and 1200 queries per second, and the load average is generally around 3-5 (falling below 1 off-peak). We have max_connections set to 100, and are starting to hit this limit quite frequently. The machine is a dual PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 array, and it only runs MySQL. I am intending on implementing a load-balancing solution, whereby we have a pool of around 3 slaves replicating off the main database server. mod_perl scripts can then make a connection to a random slave, and send their read queries there. Hopefully we can then scale this by adding more slaves to the pool. Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. I have looked at the master-master replication solution so that any machine in the replication pool can have write queries run on it; unfortunately several of our tables use auto_increment fields, so this isn't an option. Any advice on this would be appreciated. Alex Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL load balancing
At 02:06 PM 2/25/2004, you wrote: Hi, Currently our web infrastructure has one main MySQL server, to which connections are made by (mostly) mod_perl running under Apache (on 3 different machines), and several other custom-built application servers on other servers (which have persistant connections, and do both reads and writes). This machine answers between 300 and 1200 queries per second, and the load average is generally around 3-5 (falling below 1 off-peak). We have max_connections set to 100, and are starting to hit this limit quite frequently. The machine is a dual PIII 1.2Ghz with 1.5GB RAM and a 73GB SCSI RAID-5 array, and it only runs MySQL. I am intending on implementing a load-balancing solution, whereby we have a pool of around 3 slaves replicating off the main database server. mod_perl scripts can then make a connection to a random slave, and send their read queries there. Hopefully we can then scale this by adding more slaves to the pool. Problem: all the mod_perl pages run a few write queries, so they will require a connection to the main database server. Since around 80% of our queries are reads, would you recommend that each script has two connections: one for read queries, and one for write queries? We can determine which queries should be run on which connection using Perl. I have looked at the master-master replication solution so that any machine in the replication pool can have write queries run on it; unfortunately several of our tables use auto_increment fields, so this isn't an option. Here are a couple of suggestions. You can have auto-inc fields unique between databases by creating a primary index from the AutoInc, and Db_Id columns. Each database would have its own Db_Id so you know which database created the row once it is merged back into the main database. This also works with InnoDb tables as long as the auto-inc field appears first in the key. So the key would be '1,A' for rcd_id=1 for database A, and '1,B' for rcc_id=1 on database B etc. Another alternative would be to split the application into several databases, one that is replicated for reading (DBR1, DBR2, DBR3 etc), a single database for updates (DBU1) and a master database (DBM). The webservers access the replicated databases (DBR1...DBR3), and updates are made to DBU1. Your web application just uses a different connection to access DBU1 instead of DBRn. The updated rows of DBU1 are then fetched and imported into DBM, the master database. This can be a manual process that is done every 5 minutes or so. When the rows are imported into DBM, the rows are removed from DBU1 so DBU1 has only new rows that haven't been imported yet. The changes made to DBM are then replicated back to DBR1 through DBR3. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql load
I was wondering how many records A mysql table can comfortably handle.. and which table type supports the greatest amount of record capacity. Can I have a few million records in 1 table? Over 10 million? Thanks !
Re: mysql load
On Fri, Jan 16, 2004 at 10:14:08AM -0800, Andrew Kwiczola wrote: I was wondering how many records A mysql table can comfortably handle.. and which table type supports the greatest amount of record capacity. Can I have a few million records in 1 table? Over 10 million? Thanks ! Hi, Andrew. I posted yesterday explaining that my table seems fairly slow for queries. The list archive should have the message at http://lists.mysql.com My table has over 498,000,000 records of fixed-length: CREATE TABLE `inv0web02` ( `docid` int(10) unsigned NOT NULL default '0', `offset` smallint(5) unsigned NOT NULL default '0', `termid` int(10) unsigned NOT NULL default '0', `taglistid` smallint(5) unsigned NOT NULL default '0', `whichpara` tinyint(3) unsigned NOT NULL default '0', `weight_in_subdoc` float unsigned NOT NULL default '0', PRIMARY KEY (`docid`,`offset`), KEY `termid_index` (`termid`), KEY `whichpara_index` (`whichpara`), KEY `taglistid_index` (`taglistid`), KEY `weight_index` (`weight_in_subdoc`), KEY `docid_index` (`docid`), KEY `offset_index` (`offset`), KEY `termid_docid_whichpara_offset` (`termid`,`docid`,`whichpara`,`offset`) ) TYPE=MyISAM; mysql show table status; | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++---++-+-+--+---++-+-+-++-+ | inv0web02 | MyISAM | Fixed | 498093481 | 18 | 8965682658 | 77309411327 | 33526264832 | 0 | NULL | 2004-01-15 13:54:28 | 2004-01-15 14:42:01 | 2004-01-15 23:16:29 || | This takes about 40GB on disk, from 20GB of raw input. (I used INNODB previously, it took about 120GB on disk with comparable performance otherwise). To allow so many records with MyISAM, you need to ALTER TABLE tablename MAX_ROWS=[very large value]; to allow for pointers to be big enough for all the rows. So, I'd say that yes, you can create quite large tables in MySQL. Certainly for most purposes a few million records should not be any problem. -- Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
uhhh.there are only two fields in the table, but I'll give it a whirl. :) As for Donald's advice: it gave me access denied for user. :\ -Mike -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:08 PM To: Mike At Spy; Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE [snip] 70050;451 70322;451 LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' [/snip] [/snip] I am going to recommend that you specify which columns the data goes into LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one`(foo, bar) FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
[snip] 70050;451 70322;451 LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' [/snip] [/snip] I am going to recommend that you specify which columns the data goes into LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one`(foo, bar) FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql LOAD DATA INFILE
When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
[snip] Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' [/snip] Mike, could we see just a bit of the php9GOwvw file...just out of curiosity -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql LOAD DATA INFILE
Mike, There's another thread in this group 'Probleem with data import from text file'. In one of the messages you'll find 'There was a bug in v4.0.12 for Windows: binary packages were compiled without --enable-local-infile.' Might apply to you too. HansH P.S.: Running on W-XP I cann't get it to work in 4.0.13, 4.0.14b or 4.1 .. ... running server and client on one machine saved my day. Mike At Spy wrote: Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: mysql LOAD DATA INFILE
I see you still have the word LOCAL in there. Did you try and remove it? To do that in PHPMyAdmin you will need to run the import so you get the error message and then copy and paste it into the SQL section of the PHPMyadmin tool. Delete the world LOCAL and then run the query. It should work. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:59 PM To: Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: mysql LOAD DATA INFILE
Then the only way you can do it that I can think of is to write a PHP script to do basically what PHPMyAdmin is trying to do but without the LOCAL in there. However to do that you would need to be able to place the PHP file on the server, and I guess you probably can't do that either. Talk about catch 22... The only other way I can think of is to install MySQL on a machine you control, then import the data there using the method I suggested earlier. Once you have done that, you can use PHPMyAdmin to export the database from your machine. It should give you a SQL command that will create the data on another machine. Just copy and paste that into the SQL window on the main server and that will insert it. It's a little messy but that's the only other way I can think of doing it. Sorry. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:24 PM To: Donald Tyler; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Ah. No wonder it dinna work. Neither did specifying the columns as Jay suggested. I also have no choice but to do it through the browser - I don't have command line access on the server. :\ -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:08 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Oh and just a note. This solution won't work if you are uploading the file to the server through the browser. You will need to put the file on the server and adjust the commands PATH accordingly. -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:07 PM To: '[EMAIL PROTECTED]' Subject: FW: mysql LOAD DATA INFILE I see you still have the word LOCAL in there. Did you try and remove it? To do that in PHPMyAdmin you will need to run the import so you get the error message and then copy and paste it into the SQL section of the PHPMyadmin tool. Delete the world LOCAL and then run the query. It should work. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:59 PM To: Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
[snip] It's a little messy but that's the only other way I can think of doing it. Sorry. [/snip] If he has the file locally to himself he can do this via phpMyAdmin through the load text file option. Depending on the version there is always a way to load files ... even to remote servers For instance, if I am looking at the structure of a table there is a link at the bottom of the page that says Insert data from a textfile into table which gives you the option to browse for a local file to load into that table -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
[snip] I see you still have the word LOCAL in there. Did you try and remove it? To do that in PHPMyAdmin you will need to run the import so you get the error message and then copy and paste it into the SQL section of the PHPMyadmin tool. Delete the world LOCAL and then run the query. It should work. [/snip] Good catch Donald...I missed tha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
Ah. No wonder it dinna work. Neither did specifying the columns as Jay suggested. I also have no choice but to do it through the browser - I don't have command line access on the server. :\ -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:08 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Oh and just a note. This solution won't work if you are uploading the file to the server through the browser. You will need to put the file on the server and adjust the commands PATH accordingly. -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:07 PM To: '[EMAIL PROTECTED]' Subject: FW: mysql LOAD DATA INFILE I see you still have the word LOCAL in there. Did you try and remove it? To do that in PHPMyAdmin you will need to run the import so you get the error message and then copy and paste it into the SQL section of the PHPMyadmin tool. Delete the world LOCAL and then run the query. It should work. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:59 PM To: Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
It's just a standard text file; here is a piece of it: 70321;451 70050;451 70322;451 70323;451 70051;451 67026;451 70053;451 67420;451 67419;451 70451;451 70452;451 67820;451 72080;451 72081;451 72082;451 72083;451 20148;452 39761;452 20147;452 20112;452 Just lines like this: a little over 15,000 of them. :) -Mike -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:58 PM To: Mike At Spy; Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE [snip] Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' [/snip] Mike, could we see just a bit of the php9GOwvw file...just out of curiosity -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
That is messy, but you are right. I have FTP access and such - that's why I was wondering that with the new version of MySQL out, is this a change in MySQL? Or can the people who do have command line access re-compile it or something to give me permission to do what I need to do? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:30 PM To: [EMAIL PROTECTED] Subject: FW: mysql LOAD DATA INFILE Then the only way you can do it that I can think of is to write a PHP script to do basically what PHPMyAdmin is trying to do but without the LOCAL in there. However to do that you would need to be able to place the PHP file on the server, and I guess you probably can't do that either. Talk about catch 22... The only other way I can think of is to install MySQL on a machine you control, then import the data there using the method I suggested earlier. Once you have done that, you can use PHPMyAdmin to export the database from your machine. It should give you a SQL command that will create the data on another machine. Just copy and paste that into the SQL window on the main server and that will insert it. It's a little messy but that's the only other way I can think of doing it. Sorry. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:24 PM To: Donald Tyler; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Ah. No wonder it dinna work. Neither did specifying the columns as Jay suggested. I also have no choice but to do it through the browser - I don't have command line access on the server. :\ -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:08 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Oh and just a note. This solution won't work if you are uploading the file to the server through the browser. You will need to put the file on the server and adjust the commands PATH accordingly. -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:07 PM To: '[EMAIL PROTECTED]' Subject: FW: mysql LOAD DATA INFILE I see you still have the word LOCAL in there. Did you try and remove it? To do that in PHPMyAdmin you will need to run the import so you get the error message and then copy and paste it into the SQL section of the PHPMyadmin tool. Delete the world LOCAL and then run the query. It should work. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:59 PM To: Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
Oh and just a note. This solution won't work if you are uploading the file to the server through the browser. You will need to put the file on the server and adjust the commands PATH accordingly. -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:07 PM To: '[EMAIL PROTECTED]' Subject: FW: mysql LOAD DATA INFILE I see you still have the word LOCAL in there. Did you try and remove it? To do that in PHPMyAdmin you will need to run the import so you get the error message and then copy and paste it into the SQL section of the PHPMyadmin tool. Delete the world LOCAL and then run the query. It should work. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:59 PM To: Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL LOAD zipped DATA from File
Dear Ladies and Sirs, can anyone give me a hint please, if it is possible toimport data data from a zipped File, without unzipping it before. I'm using MySQL 3.23 on a LINUX System. I guess it is possible using a 'named pipe' and 'funzip' , but I don't lnow how. Thank you Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL LOAD zipped DATA from File
Dear Ladies and Sirs, can anyone give me a hint please, if it is possible toimport data data from a zipped File, without unzipping it before. I'm using MySQL 3.23 on a LINUX System. I guess it is possible using a 'named pipe' and 'funzip' , but I don't lnow how. I've used Perl and the Archive::Zip module to grab a text file form a zip file and use it to populate a database. I write mine to a temp file but there might be a way to do grab it into a variable and process it straght up instead of wiritng to a file but If it's very big youmay not want to do that. I'd check our search.cpan.org for Archive::Zip and do it in Perl! Check out [EMAIL PROTECTED] HTH DMuey Thank you Klaus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL LOAD zipped DATA from File
Hi! On Aug 01, Dan Muey wrote: Dear Ladies and Sirs, can anyone give me a hint please, if it is possible toimport data data from a zipped File, without unzipping it before. I'm using MySQL 3.23 on a LINUX System. I guess it is possible using a 'named pipe' and 'funzip' , but I don't lnow how. Manual: `LOAD DATA INFILE' Syntax ... If you need `LOAD DATA' to read from a pipe, you can use the following trick: mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x cat /dev/tcp/10.1.1.12/4711 /mysql/db/x/x mysql -e LOAD DATA INFILE 'x' INTO TABLE x x ... Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: updated mysql load data local...
Sarah, Wednesday, December 04, 2002, 12:48:15 AM, you wrote: SK We updated mysql yesterday to 3.23max and have just discovered that load SK data local infile . no longer works unless you enable it somewhere SK specifically. I've tried starting mysql with the option -local-infile=1 in SK several different files and on the command line. I can make it work with a SK funcion in C (mysql_options()) but I need to be able to do this manually as SK well. I'm sure someone else has run into this. I looked it up on the SK sql.com site and none of the info I found helped me to fix this problem. Because you can't do it with any SQL command, only by using local-infile option or configure with --enable-local-infile: http://www.mysql.com/doc/en/LOAD_DATA_LOCAL.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
updated mysql load data local...
We updated mysql yesterday to 3.23max and have just discovered that load data local infile . no longer works unless you enable it somewhere specifically. I've tried starting mysql with the option -local-infile=1 in several different files and on the command line. I can make it work with a funcion in C (mysql_options()) but I need to be able to do this manually as well. I'm sure someone else has run into this. I looked it up on the sql.com site and none of the info I found helped me to fix this problem. Please help! Thanks, Sarah - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql load issue
Tim, Normally PHP/MySQL doesn't run as mine, even if that is your Windows login name - but it is possible... Use command line. Log on as root. Check localhost privileges for user mine. (good coverage in the manual) Regards, =dn I have been trying to get mysql db to run but keep getting access denied. The error is: Warning: Access denied for user: 'mine@localhost' (Using password: YES) in /home/gulf/public_html/SiTeS_1/pUbS9_A/test/config.inc.php on line 106 Warning: MySQL Connection Failed: Access denied for user: 'mine@localhost' (Using password: YES) in /home/gulf/public_html/SiTeS_1/pUbS9_A/test/config.inc.php on line 106 Line 106 is: mysql_connect($mysql_host, $mysql_user, $mysql_pass); please help soon thanks. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql load issue
Tim, [returned the conversation to the list, so that others may benefit] Right now in the config.inc.php file i have it set as: ### MySQL data $mysql_host = localhost; // localhost name (usually:localhost) $mysql_user = mine; // MySQL username $mysql_pass = mine2; // MySQL password $mysql_base = gulf; // MySQL database Is this fine? =it's only fine if the user created is also the user 'running' the browser! =you have declared values for the four fields, but where is your Connect call? (and thereafter your DB Select call? =remember that the username and hostname are linked to establish the full UserId when it comes to establishing privilege. Is your MySQL RDBMS running on the same machine as you/your browser are working? I went in to phpmyadmin and created a user mine. =are you able to gain access at the command line (with mine/mine2)? =BTW: now that we all know your pswd, I trust this is not an Internet-facing machine! =Idle curiousity: what will the gulf db contain? =Regards, =dn --- DL Neil [EMAIL PROTECTED] wrote: Tim, Normally PHP/MySQL doesn't run as mine, even if that is your Windows login name - but it is possible... Use command line. Log on as root. Check localhost privileges for user mine. (good coverage in the manual) Regards, =dn I have been trying to get mysql db to run but keep getting access denied. The error is: Warning: Access denied for user: 'mine@localhost' (Using password: YES) in /home/gulf/public_html/SiTeS_1/pUbS9_A/test/config.inc.php on line 106 Warning: MySQL Connection Failed: Access denied for user: 'mine@localhost' (Using password: YES) in /home/gulf/public_html/SiTeS_1/pUbS9_A/test/config.inc.php on line 106 Line 106 is: mysql_connect($mysql_host, $mysql_user, $mysql_pass); please help soon thanks. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql load issue
I have been trying to get mysql db to run but keep getting access denied. The error is: Warning: Access denied for user: 'mine@localhost' (Using password: YES) in /home/gulf/public_html/SiTeS_1/pUbS9_A/test/config.inc.php on line 106 Warning: MySQL Connection Failed: Access denied for user: 'mine@localhost' (Using password: YES) in /home/gulf/public_html/SiTeS_1/pUbS9_A/test/config.inc.php on line 106 Line 106 is: mysql_connect($mysql_host, $mysql_user, $mysql_pass); please help soon thanks. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL load problems.
Maximo, - Original Message - From: Maximo Migliari [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, November 13, 2002 11:31 PM Subject: MySQL load problems. Hello all, I've been having some problems with MySQL crashing. I use a mixture of MyISAM and InnoDB tables, but most of the data is under InnoDB tables. My web applications all do relatively complex join queries. Almost all fields in the WHERE clauses of queries are indexed. I'm running RedHat 7.2, and MySQL version is 3.23.53a The server is: PIII-866 Mhz, 512 MB RAM 18 GIG SCSI drive (no RAID). The server acts as an Apache server AND as a MySQL server The server seems to work fine and has +- an uptime load of 1 - 1.5 when MySQL is running arround 20 queries/sec When it reaches about 30-40 queries/sec, the machine goes to 3-5 load in uptime, often registers slow queries, and eventually dies without any error messages (except that the socket file doesn't exist - i.e. crash). can you send me the error log yourhostname.err from the datadir of MySQL? I could try to resolve stack traces from it if any. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems.... almost forgot
Almost forgot to say, I'm running mysql with the following command: ./safe_mysql --user=mysql --skip-networking --skip-locking --log-slow-queries - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems.
Hello all, I've been having some problems with MySQL crashing. I use a mixture of MyISAM and InnoDB tables, but most of the data is under InnoDB tables. My web applications all do relatively complex join queries. Almost all fields in the WHERE clauses of queries are indexed. I'm running RedHat 7.2, and MySQL version is 3.23.53a The server is: PIII-866 Mhz, 512 MB RAM 18 GIG SCSI drive (no RAID). The server acts as an Apache server AND as a MySQL server The server seems to work fine and has +- an uptime load of 1 - 1.5 when MySQL is running arround 20 queries/sec When it reaches about 30-40 queries/sec, the machine goes to 3-5 load in uptime, often registers slow queries, and eventually dies without any error messages (except that the socket file doesn't exist - i.e. crash). How should I set my memory usage in my.cnf I have a hard time understanding how MySQL works with memory, how it manages it, etc. What should I consider when setting my memory settings??? I have included a paste of my.cnf # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=128M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= net_buffer_length=16K set-variable= myisam_sort_buffer_size=96M set-variable= thread_cache=8 set-variable= max_connections=500 log-bin server-id = 1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = innodb/ibdata1:1000M:autoextend innodb_data_home_dir = /usr/local/mysql/data/ innodb_log_group_home_dir = /usr/local/mysql/data/innodb innodb_log_arch_dir = /usr/local/mysql/data/innodb set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=32M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=96M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=128M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems.... almost forgot
Almost forgot to say, I'm running mysql with the following command: ./safe_mysql --user=mysql --skip-networking --skip-locking --log-slow-queries - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems.
Hello all, I've been having some problems with MySQL crashing. I use a mixture of MyISAM and InnoDB tables, but most of the data is under InnoDB tables. My web applications all do relatively complex join queries. Almost all fields in the WHERE clauses of queries are indexed. I'm running RedHat 7.2, and MySQL version is 3.23.53a The server is: PIII-866 Mhz, 512 MB RAM 18 GIG SCSI drive (no RAID). The server acts as an Apache server AND as a MySQL server The server seems to work fine and has +- an uptime load of 1 - 1.5 when MySQL is running arround 20 queries/sec When it reaches about 30-40 queries/sec, the machine goes to 3-5 load in uptime, often registers slow queries, and eventually dies without any error messages (except that the socket file doesn't exist - i.e. crash). How should I set my memory usage in my.cnf I have a hard time understanding how MySQL works with memory, how it manages it, etc. What should I consider when setting my memory settings??? I have included a paste of my.cnf # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=128M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= net_buffer_length=16K set-variable= myisam_sort_buffer_size=96M set-variable= thread_cache=8 set-variable= max_connections=500 log-bin server-id = 1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = innodb/ibdata1:1000M:autoextend innodb_data_home_dir = /usr/local/mysql/data/ innodb_log_group_home_dir = /usr/local/mysql/data/innodb innodb_log_arch_dir = /usr/local/mysql/data/innodb set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=32M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=160M set-variable = innodb_additional_mem_pool_size=96M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=128M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MYSQL: LOAD DATA INFILE and 'äöü' Characters
MYSQL: I have problems with importing data including characters like 'äöü'. These characters where translated to some other unusable characters. In the command line with a single insert statement the 'äöü' characters are accepted! Can help someone ? Thanks Enrico Kratter Alpenblickstrasse 30 CH-8630 Rüti mailto:ekratter;gmx.net TelP:055'241'16'76 TelG:055'254'21'48 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql load data question
Taylor, Thursday, May 23, 2002, 11:46:16 PM, you wrote: TL What priviledge does a user need in order to be able to execute the load data infile command in mysql..? is it FILE? Yes, you are right. Your user must have file privilege. Note: MySQL must have permissions on that file in your OS. TL Thanks, TL Taylor -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql load data question
What priviledge does a user need in order to be able to execute the load data infile command in mysql..? is it FILE? Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql load data question
Yes it is ... See: http://www.mysql.com/doc/P/r/Privileges_provided.html Gurhan -Original Message- From: Taylor Lewick [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 23, 2002 4:46 PM To: [EMAIL PROTECTED] Subject: mysql load data question What priviledge does a user need in order to be able to execute the load data infile command in mysql..? is it FILE? Thanks, Taylor Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql Load Data question
I read in the Mysql manual where you can change the default column seperator, but it didnt specify how to do this. So, how can I tell the LOAD DATA command to use commas, or better yet, where can I go to find out about mysql command syntax...? Thanks,. Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql Load Data question
Everytime you have something to ask, first point your browser to : http://www.mysql.com/doc/ Type in the keywords (in this case LOAD DATA) The first url in the resultset will be : http://www.mysql.com/doc/L/O/LOAD_DATA.html which is what you are looking for. Gurhan -Original Message- From: Taylor Lewick [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 16, 2002 4:52 PM To: [EMAIL PROTECTED] Subject: Mysql Load Data question I read in the Mysql manual where you can change the default column seperator, but it didnt specify how to do this. So, how can I tell the LOAD DATA command to use commas, or better yet, where can I go to find out about mysql command syntax...? Thanks,. Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Load Data question
Hi, Look at http://www.mysql.com/doc/L/O/LOAD_DATA.html and you can try this... load data infile 'yourfile' into table your_table fields terminated by '\your_separator_char_for_fields' lines terminated by '\your_separator_char_for_row'; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Taylor Lewick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 11:52 PM Subject: Mysql Load Data question I read in the Mysql manual where you can change the default column seperator, but it didnt specify how to do this. So, how can I tell the LOAD DATA command to use commas, or better yet, where can I go to find out about mysql command syntax...? Thanks,. Taylor Lewick Unix System Administrator Fortis Benefits 816 881 6073 Help Wanted. Seeking Telepath... You Know where to apply. Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL load problems - gcc-2.96?
Mike Robinson [EMAIL PROTECTED] writes: Thanks for your note. You wrote: There are known problems with gcc-2.96 which comes with RH 7.2 distribution. There is disclaimer about it at www.mysql.com/downloads/mysql-3.23.html as you noticed. There is much reading on the GCC 2.96 issue. There seems to be two paths of dicussion. I mention them here with all due respect. The first path is the one I subscribed too when I first got bit on the hindside by this. Basically, the gcc people forked 2.95.2 and it became 2.96. Only, it really wasn't 2.96. The version number 2.96 wasn't actually a version number, rather, the _codename_ for the development branch. I found this whole statement ludicrous. At this point, I figured it was a huge GCC problem that RedHat got sucked into. That sorry excuse for an official statement can be found here: http://gcc.gnu.org/gcc-2.96.html There is no official RedHat position that I can find. I did find this though: http://www.bero.org/gcc296.html does a good job of it, so we decided not to fuel more flamewars. FWIW, reproducible testcases are good... all complex software products have bugs (MySQL, gcc 2.95.x, gcc 2.96RH, gcc 3.0.x, egcs), often in interaction with oneanother (e.g. timing/locking issues), so reproducible testcases are a necessity. -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL load problems - gcc-2.96?
[EMAIL PROTECTED] (Trond Eivind Glomsrød) writes: FWIW, reproducible testcases are good... all complex software products have bugs (MySQL, gcc 2.95.x, gcc 2.96RH, gcc 3.0.x, egcs), often in interaction with oneanother (e.g. timing/locking issues), so reproducible testcases are a necessity. FWIW, it might be worth trying to rebuild the rpms with --disable-assembler. The assembler has had problems in the past in conjuction with compiler optimizations. -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL load problems - gcc-2.96?
Hello, Mike Robinson wrote: Hello to all, I've had some severe issues on redhat-7.x boxes and mysql compiled from source with gcc-2.96 much like the warning states on the download page. On these boxes, more than say 20-25 concurrent or near-concurrent connects produced unexpected results with PHP4. cut There are known problems with gcc-2.96 which comes with RH 7.2 distribution. There is disclaimer about it at www.mysql.com/downloads/mysql-3.23.html as you noticed. Similar problems occur with different applications, but it seems that updated gcc-2.96 available from update.redhat.com are more stable. Unfortunaely these problems are very hard to diagnose :( They are not easily repeatable. Recently RedHat released 7.3 which comes with gcc-2.96-110 Note that 110! It is just couple of days around so nothing can be said about it yet, but let's hope RedHat team fixed these problems. Can I ask you to provide more info about your setup? Especially if problem persists. [EMAIL PROTECTED] is better place for such issues. -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL load problems - gcc-2.96?
Thanks for your note. You wrote: There are known problems with gcc-2.96 which comes with RH 7.2 distribution. There is disclaimer about it at www.mysql.com/downloads/mysql-3.23.html as you noticed. There is much reading on the GCC 2.96 issue. There seems to be two paths of dicussion. I mention them here with all due respect. The first path is the one I subscribed too when I first got bit on the hindside by this. Basically, the gcc people forked 2.95.2 and it became 2.96. Only, it really wasn't 2.96. The version number 2.96 wasn't actually a version number, rather, the _codename_ for the development branch. I found this whole statement ludicrous. At this point, I figured it was a huge GCC problem that RedHat got sucked into. That sorry excuse for an official statement can be found here: http://gcc.gnu.org/gcc-2.96.html There is no official RedHat position that I can find. I did find this though: http://gcc.gnu.org/gcc-2.96.html Similar problems occur with different applications, but it seems that updated gcc-2.96 available from update.redhat.com are more stable. Unfortunaely these problems are very hard to diagnose :( They are not easily repeatable. I am able to reproduce this problem very easily, including on Redhat-7.2. Basically, any app using the libmysqlclient.so compiled with gcc-2.96 connecting to a server compiled with gcc-2.96 will experience problems. This is particularly the case with PHP4. Recently RedHat released 7.3 which comes with gcc-2.96-110 Note that 110! It is just couple of days around so nothing can be said about it yet, but let's hope RedHat team fixed these problems. Can I ask you to provide more info about your setup? Especially if problem persists. [EMAIL PROTECTED] is better place for such issues. Well here's the thing. I'm not really qualified to submit that this is a mysql bug, or a gcc bug, or a redhat problem. I am qualified however to recognize that mysql is saying not our problem, can't reproduce it, use the binaries, gcc saying not our fault, wasn't meant to be used and hiding behind the cloak of some weird codenaming convention, and RedHat saying unofficially, gcc-2.96 is fine, stuff it's compiling is broken. Surely there has to be a better explanation, given the vast array of knowledge and experience at work on MySQL, GCC, and RedHat. Best Regards Mike Robinson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL load problems - gcc-2.96?
Mike, what kind of problem? What exactly goes wrong with PHP4? Thanks, Vadim Mike Robinson wrote: I am able to reproduce this problem very easily, including on Redhat-7.2. Basically, any app using the libmysqlclient.so compiled with gcc-2.96 connecting to a server compiled with gcc-2.96 will experience problems. This is particularly the case with PHP4. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL load problems - gcc-2.96?
Vadim P. writes: Mike, what kind of problem? What exactly goes wrong with PHP4? Thanks, Vadim Mike Robinson wrote: I am able to reproduce this problem very easily, including on Redhat-7.2. Basically, any app using the libmysqlclient.so compiled with gcc-2.96 connecting to a server compiled with gcc-2.96 will experience problems. This is particularly the case with PHP4. In my case[s], compiling mysql with gcc-2.96 provides a libmysqlclient.so which in turn is used when compiling PHP4 with external mysql libs (as opposed to the internal libs bundled with PHP. Whether or not connecting to mysql with PHP, but particularly so, if there are more than [anywhere from] 10-25 concurrent connections, the php script making the connection will experience unexpected results. On some machines, I've seen 'connection timed out' errors written in the mysql error log. On other machines, I've seen nothing written to any log, the error is undetectable other than the php script not getting a connection. Just to throw a loop into the whole thing, about 1 in every 6 tests accepted a decent load, say 50-60 concurrent connections without a problem. I've just reproduced this problem on 7 machines, in 7 tries. So I'm batting a thousand on reproducing the problem. At first blush, I'd say the client wasn't releasing the connection handle, or it was doing something to it other than releasing it, and apps needing connections to mysql were hitting some artificial wall. Again, switching to the mysql.com binaries didn't provide the fix. I had to recompile PHP with the static mysql client libs supplied in the binary download. Then the problem went merrily away. I've not done the test with the client libs provided by PHP. I can't really try that configuration on my machines; I use other apps that use mysql so I have to use external libs. I have to wonder how many machines out there are affected by this. If one uses the mysql rpms supplied with their RedHat-7.x distribution, they're succeptible to this issue. Its a huge WTF. Best Regards Mike Robinson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL load problems - gcc-2.96?
Mike, BTW, have you been able to build Mysql 4.0 under RH7.x from the development tree lately? I keep getting some missing definitions messages as of last week, when I performed a regular 'bk resync'. Prior to that, all had been just fine. TIA, Vadim P. Mike Robinson wrote: Hello to all, I've had some severe issues on redhat-7.x boxes and mysql compiled from source with gcc-2.96 much like the warning states on the download page. On these boxes, more than say 20-25 concurrent or near-concurrent connects produced unexpected results with PHP4. Replacing the binaries compiled from source with binaries from mysql.com did not in itself provide the cure. It wasn't until I recompiled PHP with the static client libs provided in the binary download did this problem go away. It seems that the client .so compiled with gcc-2.96 was the problem, but I could be wrong. The gcc-2.96 issue has been around since early 2000. I'm have to mention that I've read the statement put out by gnu on the gcc-2.96 issue and I think its a joke, but I'm wondering if there is any update on the official mysql take is on that whole thing. Since there seems to still be issues with gcc-3.nn, it appears this might get worse, or stay bad for quite a while, before it gets any better. Best Regards Mike Robinson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL load problems - gcc-2.96?
Hello to all, I've had some severe issues on redhat-7.x boxes and mysql compiled from source with gcc-2.96 much like the warning states on the download page. On these boxes, more than say 20-25 concurrent or near-concurrent connects produced unexpected results with PHP4. Replacing the binaries compiled from source with binaries from mysql.com did not in itself provide the cure. It wasn't until I recompiled PHP with the static client libs provided in the binary download did this problem go away. It seems that the client .so compiled with gcc-2.96 was the problem, but I could be wrong. The gcc-2.96 issue has been around since early 2000. I'm have to mention that I've read the statement put out by gnu on the gcc-2.96 issue and I think its a joke, but I'm wondering if there is any update on the official mysql take is on that whole thing. Since there seems to still be issues with gcc-3.nn, it appears this might get worse, or stay bad for quite a while, before it gets any better. Best Regards Mike Robinson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL load problems - gcc-2.96?
On Sun, May 12, 2002 at 12:06:33PM -0400, Mike Robinson wrote: Hello to all, I've had some severe issues on redhat-7.x boxes and mysql compiled from source with gcc-2.96 much like the warning states on the download page. On these boxes, more than say 20-25 concurrent or near-concurrent connects produced unexpected results with PHP4. Yuck. That's not fun. Replacing the binaries compiled from source with binaries from mysql.com did not in itself provide the cure. It wasn't until I recompiled PHP with the static client libs provided in the binary download did this problem go away. It seems that the client .so compiled with gcc-2.96 was the problem, but I could be wrong. Interesting. The gcc-2.96 issue has been around since early 2000. I'm have to mention that I've read the statement put out by gnu on the gcc-2.96 issue and I think its a joke, but I'm wondering if there is any update on the official mysql take is on that whole thing. Since there seems to still be issues with gcc-3.nn, it appears this might get worse, or stay bad for quite a while, before it gets any better. The MySQL team is working on building with GCC 3.x. Eventually, that's likely to become *the* compiler for building MySQL. But so far, I don't believe their recommendations have changed. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 4.0.2: up 3 days, processed 53,701,609 queries (172/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Load Data Infile
I have a table that has following fields id (auto increment) date appleprice orangeprice pearprice When I try to LOAD DATA INFILE with this file using comma as field delimiter and newline as end of row 2000-01-04,281.08,5.27,430.05 2000-01-05,280.06,5.14,421.98 2000-01-06,279.99,5.13,408.59 2000-01-07,281.72,5.14,415.79 2000-01-10,281.32,5.17,415.80 2000-01-11,282.83,5.16,416.36 2000-01-12,281.64,5.14,419.70 2000-01-13,283.27,5.11,421.50 2000-01-14,283.25,5.13,429.09 2000-01-17,284.99,5.10,430.91 2000-01-18,285.75,5.10,432.21 it only writes to one row in the table and seems to be adding the id fields and date fields. pearprice field is empty. This is result of the one row affected id date appleprice orangeprice pearprice 127 2028-01-08 5.27 430.05 Is my data formated incorrectly? Help! Can someone please show me example of their mysql data in a working situation? Thanks Craig - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Load Data Infile
You need to specify the columns you're loading, if they aren't all the columns in the table in the same order. So, you need to add (date, appleprice, orangeprice,pearprice) at the end of your LOAD DATA INFILE statement. As for only loading one row, I'd guess your input file line endings don't match what you specified. See http://www.mysql.com/doc/L/O/LOAD_DATA.html Michael On Fri, 22 Feb 2002, Craig Westerman wrote: I have a table that has following fields id (auto increment) date appleprice orangeprice pearprice When I try to LOAD DATA INFILE with this file using comma as field delimiter and newline as end of row 2000-01-04,281.08,5.27,430.05 2000-01-05,280.06,5.14,421.98 2000-01-06,279.99,5.13,408.59 2000-01-07,281.72,5.14,415.79 2000-01-10,281.32,5.17,415.80 2000-01-11,282.83,5.16,416.36 2000-01-12,281.64,5.14,419.70 2000-01-13,283.27,5.11,421.50 2000-01-14,283.25,5.13,429.09 2000-01-17,284.99,5.10,430.91 2000-01-18,285.75,5.10,432.21 it only writes to one row in the table and seems to be adding the id fields and date fields. pearprice field is empty. This is result of the one row affected id date appleprice orangeprice pearprice 127 2028-01-08 5.27 430.05 Is my data formated incorrectly? Help! Can someone please show me example of their mysql data in a working situation? Thanks Craig - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Load Data Infile
At 13:13 -0600 2/22/02, Craig Westerman wrote: I have a table that has following fields id (auto increment) date appleprice orangeprice pearprice When I try to LOAD DATA INFILE with this file using comma as field delimiter and newline as end of row You need to add the column list: ... INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '' (date, appleprice, orangeprice, pearprice) Otherwise, LOAD DATA has no idea the id column is not present in the data file. 2000-01-04,281.08,5.27,430.05 2000-01-05,280.06,5.14,421.98 2000-01-06,279.99,5.13,408.59 2000-01-07,281.72,5.14,415.79 2000-01-10,281.32,5.17,415.80 2000-01-11,282.83,5.16,416.36 2000-01-12,281.64,5.14,419.70 2000-01-13,283.27,5.11,421.50 2000-01-14,283.25,5.13,429.09 2000-01-17,284.99,5.10,430.91 2000-01-18,285.75,5.10,432.21 it only writes to one row in the table and seems to be adding the id fields and date fields. pearprice field is empty. This is result of the one row affected id date appleprice orangeprice pearprice 127 2028-01-08 5.27 430.05 Is my data formated incorrectly? Help! Can someone please show me example of their mysql data in a working situation? Thanks Craig - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Announce: mrtg-mysql-load
Those of you using MRTG to monitor your servers, routers, etc. may be interested in knowing that a MySQL extension for MRTG is now available. This extension will allow you to monitor the number of questions and the number of slow queries the server is handling over time. Links to the extension and a live demo for a couple of test servers are at http://www.bitbybit.dk/mysqlfaq / Carsten Carsten H. Pedersen Keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
