Problem myODBC 3.51.05
Configuration: - WinXP SP1 (Workstation) - Windows 2000 SP2 (Server) - MySQL 4.0.4-beta-max-ny-log - MyODBC 3.51.05 - InnoDB - Delphi 7 - ADO 2.7 Problem with simple query Delphi/MyODBC 3.51.05: - qry.SQL.Add('select * from lokalen'); - qry.open; When I open the query, the next message is displayed: 'Access violation at address 1000724A in module 'myodbc3.dll.' 'Write of address 50455250' With the stable version (MyODBC 2.5), the same query works fine. Do we have to do an upgrade of our mySQL database, or is it a bug? Guy Van den Bosch M.C.S.C. bvba Brusselsesteenweg 386 B-3020 Herent Belgium Tel: +32 16 49 99 10 Fax: +32 16 48 05 94 [EMAIL PROTECTED] www.mcsc.be - 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
Is it a bug
Hi, I have installed mysql max 4.0.4 beta on a Suse (ver. 8.0) linux server and I have created a database with this schema: CREATE TABLE ecoras ( rsnum tinyint(4) NOT NULL, rsori tinyint(1) NOT NULL, rscod int(11) NOT NULL, rsid int(11) NOT NULL auto_increment, rsdat date NOT NULL default '-00-00', rstda date NOT NULL default '-00-00', rstcd int(11) NOT NULL default '0', rstec int(11) default NULL, rstes varchar(50) NOT NULL default '', rstpg varchar(6) NOT NULL default '0', rsart varchar(250) NOT NULL default '', rsimm varchar(12) NOT NULL default '', rsimr varchar(12) default NULL, rsrub int(11) default NULL, rsrub2 int(11) default NULL, rsord int(11) default NULL, rspst int(11) default NULL, rsgrd char(3) default NULL, rsric smallint(6) default NULL, rsdis smallint(6) default NULL, rssel smallint(6) default NULL, rscom1 varchar(100) default NULL, rscom2 varchar(250) default NULL, rscom3 varchar(250) default NULL, rssot varchar(100) default NULL, rsoch varchar(100) default NULL, rsgio1 int(11) default NULL, rsgio2 int(11) default NULL, rsint1 int(11) default NULL, rsint2 int(11) default NULL, rsarg1 int(11) default NULL, rsarg2 int(11) default NULL, rsarg3 int(11) default NULL, rsnom1 int(11) default NULL, rsnom2 int(11) default NULL, rsnom3 int(11) default NULL, rstip char(1) default NULL, rsind tinyint(1) default NULL, datsys date NOT NULL default '-00-00', timsys time NOT NULL default '00:00:00', rsdel enum('Y','N') default 'N', PRIMARY KEY (rsnum,rsori,rscod), UNIQUE KEY rsid (rsid), KEY rsdat (rsdat,rstda,rstes,rstpg), KEY rstda (rstda,rstcd,rstpg,rsart), KEY rstcd (rstcd,rstda,rstpg), KEY rstcd2 (rstcd,rsdat,rstpg), KEY rsimm (rsimm), KEY rsgio1 (rsgio1), KEY rsgio2 (rsgio2), KEY rsint1 (rsint1), KEY rsint2 (rsint2), KEY rsarg1 (rsarg1), KEY rsarg2 (rsarg2), KEY rsarg3 (rsarg3), KEY rsnom1 (rsnom1), KEY rsnom2 (rsnom2), KEY rsnom3 (rsnom3), KEY rsind (rsind,datsys,timsys) ) TYPE=MyISAM; # CREATE TABLE ecorub ( rsnum tinyint(4) NOT NULL, rsrub int(11) NOT NULL auto_increment, rsdes varchar(50) NOT NULL default '', rssig varchar(15) NOT NULL default '', rsrif int(11) default NULL, rsrif2 int(11) default NULL, rsrif3 int(11) default NULL, rsrif4 int(11) default NULL, rsrif5 int(11) default NULL, rsord int(11) default NULL, rsrms char(1) default NULL, rstmp int(11) default NULL, PRIMARY KEY (rsnum,rsrub), KEY rsord (rsnum,rsord,rsdes,rsrub), KEY rsdes (rsnum,rsdes,rsrub), KEY rssig (rsnum,rssig,rsrub), KEY rsrif (rsnum,rsrif,rsrub), KEY rsrif2 (rsnum,rsrif2,rsrub), KEY rsrif3 (rsnum,rsrif3,rsrub), KEY rsrif4 (rsnum,rsrif4,rsrub), KEY rsrif5 (rsnum,rsrif5,rsrub), KEY rsrms (rsnum,rsrms,rsrub) ) TYPE=MyISAM; # CREATE TABLE ecotes ( rstcd int(11) NOT NULL auto_increment, rstes varchar(100) NOT NULL default '', rstec int(11) default NULL, rstmp int(11) default NULL, rsord int(11) default NULL, rsdir varchar(100) default NULL, rscon int(11) default NULL, rsweb enum('Y','N') default 'Y', rsdin datetime default NULL, rsdup datetime default NULL, PRIMARY KEY (rstcd), KEY ecotes_2 (rstes,rstcd), KEY ecotes_3 (rstec,rstcd), KEY ecotes_4 (rsord,rstes,rstcd), KEY rsdir (rsdir,rstcd), KEY rscon (rscon,rstcd), KEY rsdin (rsdin,rstcd), KEY rsdup (rsdup,rstcd), KEY rstmp (rstmp,rstcd) ) TYPE=MyISAM; # CREATE TABLE ecotxt ( rsori tinyint(1) NOT NULL default '0', rscod int(11) NOT NULL default '0', rstxt mediumtext, PRIMARY KEY (rsori, rscod), FULLTEXT KEY rstxt (rstxt) ) TYPE=MyISAM; # And I try to execute this query: SELECTecoras.rstda, ecotes.rstes, ecoras.rsart, ecoras.rsimm, ecorub.rsdes, ecoras.rstpg, ecoras.rsdat, ecoras.rsrub FROM ecoras, ecotxt LEFT JOIN ecotes ON ecoras.rstcd = ecotes.rstcd LEFT JOIN ecorub ON ecoras.rsnum = ecorub.rsnum and ecoras.rsrub = ecorub.rsrub WHEREecoras.rsori = ecotxt.rsori AND ecoras.rscod = ecotxt.rscod AND (rsdat = '2002-12-01') and (rsdat = '2002-12-23') AND (match(ecotxt.rstxt) against('JAVA' IN BOOLEAN MODE)) ORDER BY ecoras.rspst, ecoras.rstda, ecotes.rstes, ecoras.rstpg, ecoras.rsart I have no data in my resultset, but data-base contains data which match conditions. If I excute the following query I have a valid resultset. SELECTecoras.rstda, ecotes.rstes, ecoras.rsart, ecoras.rsimm, ecorub.rsdes, ecoras.rstpg, ecoras.rsdat, ecoras.rsrub FROM ecoras, ecotxt LEFT JOIN ecotes ON ecoras.rstcd = ecotes.rstcd LEFT JOIN ecorub ON ecoras.rsnum = ecorub.rsnum and ecoras.rsrub = ecorub.rsrub WHEREecoras.rsnum = 0 AND
Peculiar Problem
Placed At : MAATDLN Govind Raghuram@SATYAM 12/31/2002 02:50 PM Hi, I am trying to do an update query - update bb_activeamtbalance set ui_orgbalance = +orgbalance+,ui_remainingbalance =+rembalance+,ui_usedcurrently=+usedbal+,ui_upddatetime=now(),ui_timeusedcurrently =+timeused+,ui_currentprodcode='+product+',ui_expirydate='+exptime+' where ui_userid='+userid+' I find this query in the slow query list. This query succeeds once in 3 times and when it hangs...I find that if I do a flush tables then the query succeeds again for some number of times and then the problem reoccurs. I am trying to figure what could be the cause...can someone help me with this. I am invoking this query through a java program using org.gjt.mm.mysql.Driver. Raghu - 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
user's trace
hi all, anyone knows how to get the history of a user? ... i mean ... in my database i have a lot of user with different privileges. what i need is to reach all the insert, update and delete queries executed by one user. is it possible? i check the bin logs but they seems not to trace the user that do the query. is it true? thanks in advance. natale babbo mysql, query __ Yahoo! Cellulari: scarica i loghi e le suonerie per le tue feste! http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - 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
load data local infile
Hi, I have just upgraded to 3.23.54 How to input bulk data into table? I have tried: LOAD DATA LOCAL INFILE lcopen1.txt INTO TABLE lcopen; but it shows: ERROR 1148: The used command is not allowed with this MySQL version What did I miss? What method can I use to input bulk data? Terence Ng _ Lonely Christmas(³¯«³¨³)¡A·ºå»P·(Twins)¡AµL¶¡¹D(¹q¼v)... ¦ÜIn¤â¾÷¹aÁn ³§A¹L¥V¤Ñ http://ringtone.yahoo.com.hk - 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 : Optimizing
The server is mainly a Web server (Linux RedHat 7.3, P3 1GHz, server chipset, 500M) with CPU usage 5% CPU states: 1.7% user, 0.3% system, 0.0% nice, 97.8% idle Mem: 513900K av, 502676K used, 11224K free, 0K shrd, 114640K buff Swap: 522072K av, 0K used, 522072K free 321032K cached The used RAM moves from 470M tp 510M up and down. --- my.cnf is very basic: [root@webserv1 etc]# more my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- From phpMmyAdmin SHOW STATUS I get: Max_used_connections 9 Other info sounds complicated but I could dump here. --- SHOW VARIABLES back_log 50 basedir /usr/ bdb_cache_size 8388600 bdb_log_buffer_size 32768 bdb_home /var/lib/mysql/ bdb_max_lock 1 How can I change bdb_home /var/lib/mysql/ to avoid link to /home/database/ ? Then shall I have to changed datadir /var/lib/mysql/ as well? (I want my DBs to be in /home/database/) Also get: max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connections 100 max_connect_errors 10 max_delayed_threads 20 max_heap_table_size 16777216 max_join_size 4294967295 max_sort_length 1024 max_user_connections 0 max_tmp_tables 32 max_write_lock_count 4294967295 myisam_max_extra_sort_file_size 256 myisam_max_sort_file_size 2047 myisam_recover_options 0 myisam_sort_buffer_size 8388608 net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 open_files_limit 0 --- In phpMyAdmin I saw an optimize table but don't find detail in the doc...is it worth it? --- I also find phpMyAdmin slow to display table content... --- Any idea? JM -Original Message- From: Simon Windsor [mailto:[EMAIL PROTECTED]] Sent: Monday, December 30, 2002 12:26 PM To: MySQL Subject: Re: Optimizing Hi What else is running on the machine ? What processes are using the computers resources ? Use top to find out. What are the memory parameters used in my.cnf ? The answers to these questions will provide some helpfull answers to your problem. Simon On Monday 30 Dec 2002 11:16 am, BPF Webmaster wrote: I migrated my site from a low end PC mutualized hosting to a brand new P3 1GHz 512M server I now manage myself. Unfortunately I found MySQL slower now, the time for a PHP page is now about 6 seconds and was about .6 before!!! I haven't done any optimization and the DB is the same, so the size (about 10,000 records). The only 2 weaknesses I saw is that: 1- I use a RedHat 7.3 RPM (haven't compiled myself) 2- the database folder is on another partition linked from /var/... to /home/... but on the same disk (a fast 10Krpm UW-SCSI). What would you advise? JM An example at http://www.labdv.com/forum/index.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: An Idea
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stefan, et al -- ...and then Stefan Hinz, iConnect (Berlin) said... % % So (taking a deep breath !) I would be prepared to lay the foundations ... % % Thanks, I appreciate that. I've been waiting a moment before answering, % waiting for others to take a deep breath and say yes, too, but it % seems you are the only one. I'd love to contribute, but not only am I very busy but also very new. I could probably contribute a lot of *questions*, but this FAQ is probably meant to also have *answers* :-) % % [James: ] $44.99 SRP - a low cost compared to the 'deep breath below % :-) % % I know there are good books, and I especially like Paul's books on % MySQL. There are good books by German authors on MySQL, too. I'm happy to hear of it; I may have to go out and pick it up! % % But, then again, refering to books will bring up the same sort of % answers people on this list are complaing about (hey, stupid, go buy % book xyz and read it before asking silly questions). With a FAQ, this Well, it would be nice to have a listing of readers' favorite and most helpful books so that newbies know *what* to go and pick out, for one thing; I don't think that book references are all bad! % could be You will find the answer for your question at % www.mysql.com/faq/answer_xyz.html. Yes; that's very good. % % So, any thoughts? Where do we go from here? % % Nowhere, I suppose :( Wait; quite on the contrary! I think that good work can start (granted, it would probably start faster if I jumped in to contribute more, but...) right now! % % Maybe some of the folks at MySQL AB will read this and come up with a % database structure for the FAQ on MySQL.com and user accounts for you % and me (and maybe others, once this thing has started). Why should it have to be there? Let anyone with a site set it up and get it going and see if readers will even care about it, and *then* perhaps have it move to mysql.com later... % % Regards, % -- % Stefan Hinz [EMAIL PROTECTED] % Geschäftsführer / CEO iConnect GmbH http://iConnect.de % Heesestr. 6, 12169 Berlin (Germany) % Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 HTH HAND Happy Holidays mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+EXu2Gb7uCXufRwARAq2QAJ49JlM22lxrMndZLWf9BFzRvlrWQgCfU/LP M/PviclU9pDOmcKXOgmSPp8= =1os1 -END PGP SIGNATURE- - 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: An Idea
Hi A faq without questions would be pretty empty! While you have the questions why not keep a note (or send them to me) Once we have some momentum we can put some answers and tutorials together. I thought I would email MySql to see if they like the idea, if not then I will build it. Peter --- Excellence in internet and open source software --- Sunmaia Birmingham UK www.sunmaia.net tel. 0121-242-1473 International +44-121-242-1473 --- -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: 31 December 2002 11:13 To: mysql users Cc: Stefan Hinz, iConnect (Berlin) Subject: Re: An Idea -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stefan, et al -- ...and then Stefan Hinz, iConnect (Berlin) said... % % So (taking a deep breath !) I would be prepared to lay the foundations ... % % Thanks, I appreciate that. I've been waiting a moment before answering, % waiting for others to take a deep breath and say yes, too, but it % seems you are the only one. I'd love to contribute, but not only am I very busy but also very new. I could probably contribute a lot of *questions*, but this FAQ is probably meant to also have *answers* :-) % % [James: ] $44.99 SRP - a low cost compared to the 'deep breath below % :-) % % I know there are good books, and I especially like Paul's books on % MySQL. There are good books by German authors on MySQL, too. I'm happy to hear of it; I may have to go out and pick it up! % % But, then again, refering to books will bring up the same sort of % answers people on this list are complaing about (hey, stupid, go buy % book xyz and read it before asking silly questions). With a FAQ, this Well, it would be nice to have a listing of readers' favorite and most helpful books so that newbies know *what* to go and pick out, for one thing; I don't think that book references are all bad! % could be You will find the answer for your question at % www.mysql.com/faq/answer_xyz.html. Yes; that's very good. % % So, any thoughts? Where do we go from here? % % Nowhere, I suppose :( Wait; quite on the contrary! I think that good work can start (granted, it would probably start faster if I jumped in to contribute more, but...) right now! % % Maybe some of the folks at MySQL AB will read this and come up with a % database structure for the FAQ on MySQL.com and user accounts for you % and me (and maybe others, once this thing has started). Why should it have to be there? Let anyone with a site set it up and get it going and see if readers will even care about it, and *then* perhaps have it move to mysql.com later... % % Regards, % -- % Stefan Hinz [EMAIL PROTECTED] % Geschäftsführer / CEO iConnect GmbH http://iConnect.de % Heesestr. 6, 12169 Berlin (Germany) % Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 HTH HAND Happy Holidays mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+EXu2Gb7uCXufRwARAq2QAJ49JlM22lxrMndZLWf9BFzRvlrWQgCfU/LP M/PviclU9pDOmcKXOgmSPp8= =1os1 -END PGP SIGNATURE- - 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: ensuring an instructor, client, or room isn't double-booked
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Benjamin, et al -- ...and then Benjamin Pflugmann said... % % Hi David. Hi! % % On Sun 2002-12-29 at 16:21:56 -0500, [EMAIL PROTECTED] wrote: % ...and then Benjamin Pflugmann said... % [...] % No; everything is one hour. Two hours is two bookings. % % Ah. Good. That makes the case easier and is exactly the case my last % answer was about. OK. % % [...] % % know beforehand when the class starting at x o'clock will end? And % % saving a number for the time slot would also work? % % Saving a number? I don't know what you mean here... % % Not important anymore. Ah; good :-) % % But for completeness: You could do as with school classes. 9-10 % o'clock is 1st, 10-11 is 2nd and so on. So you could theoretically % save date 2003-02-10 class 1 instead of date 2003-02-10 time % 09:00:00. Oh, I see... I've been thinking about that, too; I currently use a datetime field, but all I really need is a date and an hour. I figure with one combined field I can match against -YY-ZZ% but maybe two fields would make it easier to pull out the time column... % % Not that my suggestion would be better necessarily, it just served to % make it less ambiguous what we are talking about. Seemed it failed. ;) Hah :-) Well, thanks, anyway! % % [...] % % In the case of time slots, you could create a unique index on % % (client,time) and (instr,time) and skip the selects. Simply insert and % % check for an duplicate key error. % % Hmmm... OK; avoiding duplicates is the end goal and so I should probably % learn more about this apparently simple approach. % % Yes, the main purpose of UNIQUE indexes is to guarantee uniqueness in % a column (or a set of columns). So that's the way to go. Oh, I see! Cool. Now I presume that I will want a unique index for (instructor,timeslot) and a[nother] unique index for (client,timeslot). I can't do a unique index on the place because some rooms are booked for groups (I'm planning to create 12 records for a 12-seat class when it's defined, and then I'll update those records until there are no more with empty client values as I add students). Do I want two unique indexes like that, or some other approach? % % Um, what next? :-) % % :-) Hee hee. % % This is probably in the mysql doc, so a pointer would be appreciated % but know that I will [try to] look up multi-column indexes and see % how they deal with duplicates. % % http://www.mysql.com/doc/en/Multiple-column_indexes.html Cool; thanks. % % Although the manual covers a lot of stuff which is not strictly % MySQL-related, it is not well suited to be an introduction into the % world of relational databases. Yeah. I've been drawing heavily on past theory classes, but that's now ten years old :-) % % May I suggest Paul DuBois' book? It provides a lot of insight in such % issues. You may. If you know anyone who wants to buy it for me for a belated Christmas present, you can suggest that, too :-) OK, OK... Having found out about it, I think I will probably go and get it; it sounds like a good investment. Even if I start with a borrowed copy from a library I want to get my hands on it. % % [...] % I figured I'd have to check every record to see if the datetime and ... % many of them), and maybe not with the indexes (hmmm... indices?) you % mention above. % % Well, that is what indexes are all about. Searching for 1 record in OK... Neat. % 1 billion with indexes can be as fast as a full table scan for 1 in 30 % records. Good :-) % % A more complex answer from some ealier posts of mine (still % simplified): % % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486 % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591 Thanks; I'll read them next. [Actually I just popped out and read them.] Oh, I get it; an index is a fast BTree pointer to the data; it's a hash table! Cool. Yes, that's what I want, all right. % % I don't know exactly, but we're probably looking at a total of 3k or 4k ... % that a few or a lot? % % It depends on how many time slots you have. The general answer is that % you want the column with the highest cardinality first. But also the % storage size matters and so on. That's what etc was about. ;-) Heh :-) % % Usually I use the trial and error method I mentioned below, myself. OK. % But sometimes it is so obvious that you don't have to. Like if you had % only 40 time slots (only planning a week ;). The client should come % first. Yeah. I won't have that few; I'll have about 80 per week (6a thru 7p on six days) for every week from now until the end of time (hey, I can hope that I'll be making money from this software for that long!). So in the beginning I'll have fewer time slots, but by the end of the first year that will very probably have flipped... Good grief; what do you do in that case? Well, I suppose it's an opportunity to charge for an annual tuneup ;-) % ... % I
Re: altering data structure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Benjamin, et al -- ...and then Benjamin Pflugmann said... % % Hi again. And again :-) % % On Sun 2002-12-29 at 15:21:33 -0500, [EMAIL PROTECTED] wrote: % ...and then Benjamin Pflugmann said... % [...] % % Of course, that's only possible if you can live with being % % MySQL-specific. % % Hmmm... so it's a mysql thing and not a SQL thing. Well, I'd like to % avoid that, even though I like mysql... % % Well, setting up a database (or updating it) is always very % vendor-specific, isn't it? Well, yeah, but I want to try to write portable code; just because I like mysql doesn't meant that my customer (or some future contract employer) will... % % % Besides, I have yet to see production-level SQL that works on several % databases without change (except for stuff that is developed that That's my general goal, though I hardly know enough to know what's specific versus standard [yet]. % way). The problem is that the SQL standard is too unspecific in some Ahhh... % areas and the vendors have to fill in the details and it becomes a % PITA to work without the vendor-specific features. Yeah. Well, mysql, postgresql, and oracle are enough for me; now I only have to become expert, or even fluent, in three. Ha ha :-) % % IMHO, the most common example is the lack of sequence support. % AUTO_INCREMENT is MySQL-specific. To be portable you have to simulate Oops! :-) % sequences yourself, which is possible, but you won't do except if you % *know* you will need to run on a different RDBMS later. Yeah. Well, or if you plan to be able to run on one later... % % More info here: % % http://www.mysql.com/doc/en/Compatibility.html % % % Thanks HAND HH % % % Hehe. I wondered about the non-abbriviation in your first mail % already. ;-) *grin* I figure the first one in the thread will define it for later since it's not too standard... :-) % % HTH, % % Benjamin. % % % -- % [EMAIL PROTECTED] Thanks again HAND HH mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+EYVJGb7uCXufRwARAuwYAKDCffmOYnUM2G81hel5l9vLOoShqwCeOhzz /sb2qzOkmS0TYBUCwsSFW3E= =/oTU -END PGP SIGNATURE- - 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.Sock problem
I had this same problem... for 4 Days! Check the permission on the /var/lib/mysql directory. Check the logs /var/logs/mysql.log to make sure its not crashing or not connecting Check the permissions on the /var/run/mysql it should have rwxr-xr-x mysql mysql (this was my problem) I thought I was being cool by using: chown ./* to change the of all the files in my /var/www directory. I didn't realize I had change everything in ../ which changed ownership on everyting in the /var structure - Original Message - From: A.T.Z. [EMAIL PROTECTED] To: Jason Steig [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 31, 2002 1:59 AM Subject: RE: Mysql.Sock problem Is mysql running (ps -axu) if not: /path/to/safe_mysqld --user=mysql This is explained in the docs -Oorspronkelijk bericht- Van: Jason Steig [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 31 december 2002 9:08 Aan: [EMAIL PROTECTED] Onderwerp: Mysql.Sock problem I' am relatively new to MySQL and I 'am having a problem with starting the ./mysql program I always get the mysql.sock error. I know this mysql.sock file is used when the local machine is the mysql server. Likewise I can't start mysql.server or mysqld. I' am trying to start it as root. I know this is not a good practice. The first think I have to do is (./mysql -u (User) password). This is what allows me to create a user to run mysqld? Correct? - 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: replication
On Mon, 30 Dec 2002, Arthur Fuller wrote: Is there in *nix an implementation of GUIDs? If the MS math is to be believed, GUIDs are guaranteed unique for the next century. No matter how many monkeys are at the typewriters. Supposing a) that there is a *nix GUID implementation, what's the right column type to use within MyISAM and InnoDB? Assuming that anyone besides me finds this interesting, will MySQL ever support GUID column types? Arthur Would it be possible to create a limited GUID for the purpose of our networks fitting into a BIGINT UNSIGNED 8 Bytes? Something like 4 bytes taken from the IP number and 4 bytes INTEGER UNSIGNED for counting? This would still be good for 4294967295 objects. Or restrict the mechanism from global to the internal (B or C) network and use 2 bytes for the host and 6 bytes for counting. 281474976710656 different items generated from each host would be sufficient for most applications. Any host generating those Network Unique ID's at a pace of 1000 per second could keep on doing so for nearly 9000 years ... Thomas Spahni - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Maxime LEMAIRE [EMAIL PROTECTED] Cc: Mysql [EMAIL PROTECTED] Sent: Monday, December 30, 2002 10:56 AM Subject: Re: replication On Mon, Dec 30, 2002 at 08:37:13AM +0100, Maxime LEMAIRE wrote: Hi, I would like to know how mySQL manage the identity (auto increment counter) in a replication environment when 2 servers are both master on the same database. In that situation, you really need to generate your own primary keys. Using auto-increment with a dual-master setup is asking for trouble. It'll fail sooner or later, leaving you two servers that no longer agree on primary keys. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 15 days, processed 549,176,147 queries (411/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 - 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
Bug? ORDER BY UPPER(alias) doesn't work.
Hi, is this a bug or not? I'm using MySQL 3.23.49! When I want to use this: SELECT CASE WHEN hmContactGroup.Id = 1 THEN CONCAT(hmContact.FullName, ' ', hmContact.CountryId) ELSE COALESCE(hmContact.DjName, hmContact.FullName) END AS ContactName FROM hmContact, hmContact_ContactGroup, hmContactGroup WHERE (hmContactGroup.Id = 1) AND (hmContactGroup.ShowLinkGroup = 'Y') AND (hmContact_ContactGroup.ContactGroupId = hmContactGroup.Id) AND (hmContact.Id = hmContact_ContactGroup.ContactId) AND (hmContact.Website IS NOT NULL) ORDER BY UPPER(ContactName) ASC it will return with ERROR 1054: Unknown column 'ContactName' in 'order clause' Without UPPER in the ORDER BY it works. In ORACLE 8i it works ... BEST REGARDS, Robert GRAF Vienna, AUSTRIA - 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: manual for 3.23.53 pdf?
On Mon, 30 Dec 2002, Richard Nagle wrote: looking for a pdf manual for mysql 3.23.53 anyone? You may build it yourself: texi2dvi4a2ps -p manual.texi Thomas Spahni - 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.Sock problem
It appears that we both are climbing the same mountain. The best approach is to hire a consultant to come in and get you started in the right direction. If, as is our case, this is unlikely, I will attempt to relay to you some of what I have encountered so far. Typically we prefer downloading RPM files rather than tarball files. The RPM files are utilized better by an application called WEBMIN (www.webmin.org) . I am going to recommend that you familiarize yourself with Webmin very thoroughly. By clicking on the SYSTEM button, you can then select the SoftWare Packages icon, and then the Package Tree to see what your server has installed. Or, you can uninstall programs that you will not need. The Bootup and Shutdown icon will allow you to start applications and to specify what ones you want started when the Linux server is booted. We encountered some horrific problems trying to instal MySQL from a taball file that just seemed to go on and on. However, using Webmin and corresponding RPM files to install MySQL, we had no problem at all. Plus, we can now see live examples of how MySQL is supposed to have been set up. Never having seen a working setup before, this proved to be very valuable. Finally, you want to access your MySQL database from your browser, you will more than likely have to familiarize yourself with PHP (personal home page). It is also downloaded as an RPM file. Note: you must install the RPM module that allows PHP applications to utilize MySQL databases. HTH LeVar Bery [EMAIL PROTECTED] 12/31/02 07:19AM I had this same problem... for 4 Days! Check the permission on the /var/lib/mysql directory. Check the logs /var/logs/mysql.log to make sure its not crashing or not connecting Check the permissions on the /var/run/mysql it should have rwxr-xr-x mysql mysql (this was my problem) I thought I was being cool by using: chown ./* to change the of all the files in my /var/www directory. I didn't realize I had change everything in ../ which changed ownership on everyting in the /var structure - Original Message - From: A.T.Z. [EMAIL PROTECTED] To: Jason Steig [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 31, 2002 1:59 AM Subject: RE: Mysql.Sock problem Is mysql running (ps -axu) if not: /path/to/safe_mysqld --user=mysql This is explained in the docs -Oorspronkelijk bericht- Van: Jason Steig [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 31 december 2002 9:08 Aan: [EMAIL PROTECTED] Onderwerp: Mysql.Sock problem I' am relatively new to MySQL and I 'am having a problem with starting the ./mysql program I always get the mysql.sock error. I know this mysql.sock file is used when the local machine is the mysql server. Likewise I can't start mysql.server or mysqld. I' am trying to start it as root. I know this is not a good practice. The first think I have to do is (./mysql -u (User) password). This is what allows me to create a user to run mysqld? Correct? - 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 - 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: Privileges in MySQL 4.0.7 (upgrade)
Dear list, no one has answered my question up to now. Looking through the manual again today, I still can't figure out what to do. just upgraded from MySQL 4.0.3 to 4.0.7 on my Win2K box at home. The problem is, I still have the _old_ privileges in my mysql tables, not the new ones like super, show database etc. All the manual (and the user notes) are telling me is that I have to run the mysql_fix_privilege_tables script. But where can I find it? It's not in the MySQL Windows distribution. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Stefan Hinz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 31, 2002 12:03 AM Subject: Privileges in MySQL 4.0.7 (upgrade) Dear list, just upgraded from MySQL 4.0.3 to 4.0.7 on my Win2K box at home. Leaving DATADIR as it was, the 4.0.7 installation did not touch the privilege tables in the mysql database, so I did not have the new privileges and features available. As there doesn't seem to be a mysql_fix_privilege_tables script for Win2K, I wiped out my old mysql database (pressing DEL in Win Explorer), and installed again. This gave me a new mysql database, but no new privileges at all. My old user accounts are gone, though, so the installation procedure definitely set up something new. DESCRIBE host; DESCRIBE user; DESCRIBE db; shows me that features like Create_tmp_table_priv and Super_priv are still missing. The most modern feature I can find is the References_priv. Win Explorer tells me the mysql database is from 2002-12-30 (today), but all the tables in there are from 2001-10-06. I took down the server when installing the update, and I put a copy of my old DATADIR on another disk. Any hints are greatly appreciated. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - 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: Privileges in MySQL 4.0.7 (upgrade)
Stefan, you may want to take the queries from the mysql_fix_privilege_tables shell script and run them manually. All queries are nice and clean in the script and can be extracted with any editor. Thomas Spahni On Tue, 31 Dec 2002, Stefan Hinz wrote: just upgraded from MySQL 4.0.3 to 4.0.7 on my Win2K box at home. Leaving DATADIR as it was, the 4.0.7 installation did not touch the privilege tables in the mysql database, so I did not have the new privileges and features available. As there doesn't seem to be a mysql_fix_privilege_tables script for Win2K, I wiped out my old mysql database (pressing DEL in Win Explorer), and installed again. This gave me a new mysql database, but no new privileges at all. My old user accounts are gone, though, so the installation procedure definitely set up something new. DESCRIBE host; DESCRIBE user; DESCRIBE db; shows me that features like Create_tmp_table_priv and Super_priv are still missing. The most modern feature I can find is the References_priv. Win Explorer tells me the mysql database is from 2002-12-30 (today), but all the tables in there are from 2001-10-06. I took down the server when installing the update, and I put a copy of my old DATADIR on another disk. Any hints are greatly appreciated. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - 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: Privileges in MySQL 4.0.7 (upgrade)
Dear Thomas, you may want to take the queries from the mysql_fix_privilege_tables shell script and run them manually. Thanks! If only I could find this script ... Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: Stefan Hinz [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, December 31, 2002 2:58 PM Subject: Re: Privileges in MySQL 4.0.7 (upgrade) Stefan, you may want to take the queries from the mysql_fix_privilege_tables shell script and run them manually. All queries are nice and clean in the script and can be extracted with any editor. Thomas Spahni On Tue, 31 Dec 2002, Stefan Hinz wrote: just upgraded from MySQL 4.0.3 to 4.0.7 on my Win2K box at home. Leaving DATADIR as it was, the 4.0.7 installation did not touch the privilege tables in the mysql database, so I did not have the new privileges and features available. As there doesn't seem to be a mysql_fix_privilege_tables script for Win2K, I wiped out my old mysql database (pressing DEL in Win Explorer), and installed again. This gave me a new mysql database, but no new privileges at all. My old user accounts are gone, though, so the installation procedure definitely set up something new. DESCRIBE host; DESCRIBE user; DESCRIBE db; shows me that features like Create_tmp_table_priv and Super_priv are still missing. The most modern feature I can find is the References_priv. Win Explorer tells me the mysql database is from 2002-12-30 (today), but all the tables in there are from 2001-10-06. I took down the server when installing the update, and I put a copy of my old DATADIR on another disk. Any hints are greatly appreciated. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - 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: An Idea
David, regarding the MySQL FAQ: Why should it have to be there? Let anyone with a site set it up and get it going and see if readers will even care about it, and *then* perhaps have it move to mysql.com later... MySQL.com would be the natural place for the FAQ. Any other place wouldn't be half as good. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - 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
Year Lists
Hiya, I have a database about projects in a company, they all have a start year and end year. If the projects have not ended then they get a end year of . I have made a SQL command, given a year, works out with projects are running in that year. The company I am doing this for, wants basically a drop down box which says (1950- 8 Projects) and such like. Now with the current command I have it would mean 92or so SQL commands as the company has records back to 1910. Is there a way to do this in one command? I have searched all the mannual and I can work out something similair to a for loop in SQL. Would variables help? Any help would be greatly appreciated. Have a great New Year. Steve - 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: Year Lists
On Tue, 2002-12-31 at 14:53, Steve Vernon wrote: Hiya, I have a database about projects in a company, they all have a start year and end year. If the projects have not ended then they get a end year of . I have made a SQL command, given a year, works out with projects are running in that year. The company I am doing this for, wants basically a drop down box which says (1950- 8 Projects) and such like. Now with the current command I have it would mean 92or so SQL commands as the company has records back to 1910. Is there a way to do this in one command? I have searched all the mannual and I can work out something similair to a for loop in SQL. Would variables help? Hi Steve, You want to use select group by, something like: select start_year, count(*) from projects group by start_year; == Martin - 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: Pattern Match on 3.23
It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - 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 [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What does this mean? : Warning: thr_alarm queue is full
Hi! Chung == Chung Ha-nyung [EMAIL PROTECTED] writes: Chung query. Chung I happened to find out that the lots of following error messages are Chung stored Chung error log file: Chung Warning: thr_alarm queue is full Chung What's this? Is it serious problem? The above is a very strange error; Basicly something that should never happen. It means that somehow you have more threads wanting to request for a alarm (used to timeout connections) than was registered when mysqld started. This should be impossible as we init the alarms with the following code in mysqld.cc init_thr_alarm(max_connections+max_insert_delayed_threads) In other words, we reserve as many alarms as we can have connections + insert delay threads; As a thread can only have one alarm and and no other threads than a connection or insert delay thread uses alarms I can't understand how you can get this error. Note that even if the alarm call fails, mysqld will work normally with the exception that a long lived connection will not time out. If you cando a new bug report, preferably with the 'mysqlbug' script, that includes the following information, I could try to dig deeper into this problem. Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: Operating system: Regards, Monty -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ 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
Re: nested transactions?
Heikki Tuuri wrote: Transaction savepoints are already implemented internally inside InnoDB. The MySQL support for the syntax might come around August 2003. If someone is willing to sponsor the implementation, they may come quicker. For the sake of those with more programming time than money, what kind of theoretical work is required to allow nested transactions? -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: Year Lists
Martin, Thanks for the reply. Good plan but at the moment my query is like (had to alter it slightly as it was in PHP and multiple lines, so dont take it as working). SELECT projects.name, project.endyear, project.startyear FROM projects WHERE projects.startyear = '(INPUT THE YEAR HERE)(projects.leaveyear = '(YEAR HERE AS WELL)' || projects.leaveyear = '' ) LIMIT 5000 What you suggest basically will give a count on start year (or end year if altered), which I have implemented and working for ended year. Say a project started in 1920 and another in 1921 and they both finish in 1925. I would expect to get this: 19201 Project 19212 Projects 19222 Projects 19232 Projects 19242 Projects 19252 Projects 19260 Projects (or no line at all ideally). up to 2003 Ideally if a year does not have any projects it would not be listed. Any more help would be great, or I could be wrong and you could have the answer! Steve On Tue, 2002-12-31 at 14:53, Steve Vernon wrote: Hiya, I have a database about projects in a company, they all have a start year and end year. If the projects have not ended then they get a end year of . I have made a SQL command, given a year, works out with projects are running in that year. The company I am doing this for, wants basically a drop down box which says (1950- 8 Projects) and such like. Now with the current command I have it would mean 92or so SQL commands as the company has records back to 1910. Is there a way to do this in one command? I have searched all the mannual and I can work out something similair to a for loop in SQL. Would variables help? Hi Steve, You want to use select group by, something like: select start_year, count(*) from projects group by start_year; == Martin - 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: Pattern Match on 3.23
Qunfeng, Thanks for the feedback, I surely appreciate it. I asked the pattern match question, because I am using a hosting service that hosts MySQL 3.23. Since I have a need to search on terms less than 3 characters long and I can not re-compile, I was looking for another solution. I thought that I might be able to use pattern matching as a substitute, but it sounds like performance might be an issue with large tables. If you have any other recommendations on how I could approach my problem, I would surely appreciate them. At 07:35 AM 12/31/02 -0800, Qunfeng Dong wrote: It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - 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 [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: Pattern Match on 3.23
If you are searching with %pattern%, your speed is depending on the speed of table-scan. The speed of tablescan depends on your my.cnf setting (increase record buffer size?) and how big your records are. I am using pattern search here with millions of records and the performance is not terriblly too bad. Maybe you can still use fulltext search for general cases; and use pattern match ONLY when you are searching for 3-char-term. You should be able to make such Switch through your interface. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, Thanks for the feedback, I surely appreciate it. I asked the pattern match question, because I am using a hosting service that hosts MySQL 3.23. Since I have a need to search on terms less than 3 characters long and I can not re-compile, I was looking for another solution. I thought that I might be able to use pattern matching as a substitute, but it sounds like performance might be an issue with large tables. If you have any other recommendations on how I could approach my problem, I would surely appreciate them. At 07:35 AM 12/31/02 -0800, Qunfeng Dong wrote: It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - 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 [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 [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Year Lists
Sounds to me like you'll need to join for that -- and list all the years you are interested in in another table. Try this? SELECT y.Year, count(*) FROM Year as y LEFT JOIN projects ON project.startyear=y.Year AND project.endyear=y.Year GROUP BY y.Year; This will give you a count of 0 for any years without a project (Almost 100% certain =] ). But don't blame me if the query is grossly inefficient! Nick Elliott - Original Message - From: Steve Vernon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 31, 2002 10:44 AM Subject: Re: Year Lists Martin, Thanks for the reply. Good plan but at the moment my query is like (had to alter it slightly as it was in PHP and multiple lines, so dont take it as working). SELECT projects.name, project.endyear, project.startyear FROM projects WHERE projects.startyear = '(INPUT THE YEAR HERE)(projects.leaveyear = '(YEAR HERE AS WELL)' || projects.leaveyear = '' ) LIMIT 5000 What you suggest basically will give a count on start year (or end year if altered), which I have implemented and working for ended year. Say a project started in 1920 and another in 1921 and they both finish in 1925. I would expect to get this: 19201 Project 19212 Projects 19222 Projects 19232 Projects 19242 Projects 19252 Projects 19260 Projects (or no line at all ideally). up to 2003 Ideally if a year does not have any projects it would not be listed. Any more help would be great, or I could be wrong and you could have the answer! Steve On Tue, 2002-12-31 at 14:53, Steve Vernon wrote: Hiya, I have a database about projects in a company, they all have a start year and end year. If the projects have not ended then they get a end year of . I have made a SQL command, given a year, works out with projects are running in that year. The company I am doing this for, wants basically a drop down box which says (1950- 8 Projects) and such like. Now with the current command I have it would mean 92or so SQL commands as the company has records back to 1910. Is there a way to do this in one command? I have searched all the mannual and I can work out something similair to a for loop in SQL. Would variables help? Hi Steve, You want to use select group by, something like: select start_year, count(*) from projects group by start_year; == Martin - 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 - 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: Upgrade to MySQL 4.0.x
I have a few questions about whether or not I should upgrade to MySQL 4.0.7-Gamma on my Windows XP system. I am now running MySQL 3.23.54a on my Laptop. Its used for developing/testing, and the applications needs to be transported to the final server after completion. Most of the time however the server is a 3.23.x and not 4.0.x. Is 4.0.x backwards compatible? Can I tranfer my databases without problems from my current MySQL 3.23.54 database to MySQL 4.0.x.? And is MySQL 4.0.7-gamma coonsiderd stable? Yes and Yes. There shouldn't be a problem with porting data from one version to another. The only true way I guess would be to test it. We have been using the 4.0.6 on our production machine since it came out and have had no problems. Also I would like to know how-to upgrade, can I just instal over my excisting MySQL installation? You should be able to just install over the top without any problems. Backup tour data directory to a different directory though to play it safe. Scott Pippin [EMAIL PROTECTED] - 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: Binary Data and BLOB Problem
Mike, Thank you very much for responding to my question. Haven't been able to find much out there. Your article, to which you referred in your response, was something I have already used to get me as far as I had gotten. I'd found it in my search for an answer. I found the connection string and the explanation of the options you used particularly useful. The comment you made about the cursor location confirmed what I read elsewhere, though put a different way. I've read that MySQL simply doesn't support cursors, so you have to set the cursor location to client. In the MySQL docs I've seen where they indicate that in the near future they plan to support cursors. I was able to follow the example on stardeveloper.com (http://www.stardeveloper.com/articles/display.html?article=2001033101page=1) for uploading files and inserting them into a database as described without trouble. The troubles start when I try to adapt the example for use with MySQL instead of MS Access. I can't for the life of me get it to work consistently. I have changed the cursor location to client, I have changed the max_allowed_packed to 1600, I have swapped out the table names that you can use with Access with the complete select statements that MySQL requires. Still, when I try to upload a file larger than 1 MB I get the following error: Microsoft OLE DB Provider for ODBC Drivers error '80040e21' [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]Lost connection to MySQL server during query. Sometimes the error is: Microsoft OLE DB Provider for ODBC Drivers error '80040e21' [MySQL][ODBC 3.51 Driver][mysqld-4.0.5-beta-max-nt]Got a packet bigger than 'max_allowed_packet' I'm sure the file is less than 2MB. With this code: ' Checking to make sure if file was uploaded If fileSize 0 Then set rsImage = Server.CreateObject(ADODB.Recordset) rsImage.ActiveConnection = MM_connShooters_STRING rsImage.Source = select * from web_lab.Images limit 0 rsImage.CursorType = adOpenKeyset rsImage.CursorLocation = adUseClient rsImage.LockType = adLockPessimistic rsImage.Open() rsImage_numRows = 0 ' Adding data rsImage.AddNew rsImage(userID) = userID rsImage(imageType) = contentType rsImage(imageSize) = fileSize rsImage(imageName) = fileName rsImage(imageCaption) = caption ERROR--rsImage.Update rsImage.Close Set rsImage = Nothing Response.Write font color=greenFile was successfully uploaded... Response.Write /font Else Response.Write font color=brownNo file was selected for uploading Response.Write .../font End If When I adapt the code to use the Stream object instead of .AppendChunk, I get this error: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. The code for the insert is as follows: ' Checking to make sure if file was uploaded If fileSize 0 Then set adoImageStream = Server.CreateObject(ADODB.Stream) adoImageStream.Type = adTypeBinary set rsImage = Server.CreateObject(ADODB.Recordset) rsImage.ActiveConnection = MM_connShooters_STRING rsImage.Source = select * from web_lab.Images limit 0 rsImage.CursorType = adOpenKeyset rsImage.CursorLocation = adUseClient rsImage.LockType = adLockPessimistic rsImage.Open() rsImage_numRows = 0 ' Adding data rsImage.AddNew adoImageStream.Open ERROR-- adoImageStream.Write fileData rsImage(userID) = userID rsImage(imageType) = contentType rsImage(imageSize) = fileSize rsImage(imageName) = fileName rsImage(imageCaption) = caption rsImage(imageData) = adoImageStream.read rsImage.Update adoImageStream.Close rsImage.Close Set rsImage = Nothing Response.Write font color=greenFile was successfully uploaded... Response.Write /font Else Response.Write font color=brownNo file was selected for uploading Response.Write .../font End If I'm almost sure this error arises because the class object used for the page (found on the site with the tutorial, called 'loader.asp') changes the binary form data to a string, so the stream of type adTypeBinary doesn't accept the data, but when I change the stream type to text then I get an error because the field is expecting binary data. I haven't found an efficient enough way to change the data back to binary or to alter the class so that it doesn't turn the file data into a string in the first place. The relevant code from the class is this subroutine, which gets called when the object is initialized, to parse the form data: Private Sub getData(rawData) Dim separator separator = MidB(rawData, 1, InstrB(1, rawData, ChrB(13)) - 1) Dim lenSeparator lenSeparator = LenB(separator) Dim currentPos currentPos = 1 Dim inStrByte inStrByte = 1 Dim value, mValue Dim tempValue tempValue = While inStrByte 0 inStrByte = InStrB(currentPos, rawData, separator) mValue = inStrByte - currentPos If mValue 1 Then value = MidB(rawData, currentPos, mValue) Dim begPos, endPos, midValue, nValue Dim intDict Set intDict = Server.CreateObject(Scripting.Dictionary) begPos = 1 + InStrB(1, value, ChrB(34)) endPos =
Is the Max edition of 4.0.7 required if running on a server with RAID?
Hi, all. I've been using 4.0.5 beta-max-nt on my development PC for about a month and am getting ready to transfer the web app and install MySQL on a server which uses RAID. Is the Max edition of 4.0.7 necessary for a server with RAID. Or can the standara edition be used? Also, I'm still looking for a .zip binary install of 4.0.7-Max gamma version, if there's one available. The standard edition has a .zip binary install for Windows, but so far I haven't found one for the Max edition. Anyone know if there is one out yet? Thanks, Rick Rick Faircloth, Prism Productions - 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: Pattern Match on 3.23
Qunfeng, ..millions of records.. seems like a lot... Would you be kind enough to provide me with your hardware configuration? Thanks. At 07:57 AM 12/31/02 -0800, Qunfeng Dong wrote: If you are searching with %pattern%, your speed is depending on the speed of table-scan. The speed of tablescan depends on your my.cnf setting (increase record buffer size?) and how big your records are. I am using pattern search here with millions of records and the performance is not terriblly too bad. Maybe you can still use fulltext search for general cases; and use pattern match ONLY when you are searching for 3-char-term. You should be able to make such Switch through your interface. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, Thanks for the feedback, I surely appreciate it. I asked the pattern match question, because I am using a hosting service that hosts MySQL 3.23. Since I have a need to search on terms less than 3 characters long and I can not re-compile, I was looking for another solution. I thought that I might be able to use pattern matching as a substitute, but it sounds like performance might be an issue with large tables. If you have any other recommendations on how I could approach my problem, I would surely appreciate them. At 07:35 AM 12/31/02 -0800, Qunfeng Dong wrote: It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - 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 [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 [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 - How to use more than 32 index in a table ?
Hi, How to use more than 32 index in a table ? I need to use 68 index in one same table (Mysql v 3.23.44. How to make ? Thank you, Alberto J. Alvares - 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: HELP for mysql ended error
If you check the MySQL reference guide, you'll see that the Errorcode 13 is indicating that you don't have your permissions set up correctly for your data and log directories. Once you fix that, you should (hopefully) be okay. David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] -Original Message- From: Chamlee, Douglas, B. (Contractor) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 31, 2002 11:38 AM To: '[EMAIL PROTECTED]' Subject: HELP for mysql ended error I am trying to upgrade the mysql database. I am installing on a Unix (SGI's IRIX) platform, upgrading to 3.23.52. I am installing a tar distribution (binary) from SGI's freeware web site. The installation goes fine - no errors - execpt when I try to start mysql it returns mysql ended and will not start. If there are any mysql pro's out there that could help I would really, really, really appreciate it. Thanks When I try to start mysql I get the following error at the command line: [62]my_server_name:/usr/freeware/binsafe_mysqld Starting mysqld daemon with databases from /local/mysql 021231 10:06:19 mysqld ended This is the output from my .err file from mysql: [84]server1.bliss.army.mil :/local/mysqlmore server1.bliss.army.mil.err 021231 09:51:09 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 9:51:09 Could not use server1-bin for logging (error 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Normal shutdown 021231 9:51:09 /usr/freeware/sbin/mysqld: Error on delete of '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 2) 021231 09:51:09 mysqld ended 021231 09:51:50 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 9:51:50 Could not use doe1-bin for logging (error 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Normal shutdown 021231 9:51:50 /usr/freeware/sbin/mysqld: Error on delete of '/local/mysql/doe1.bliss.army.mil.pid' (Errcode: 2) 021231 9:51:50 /usr/freeware/sbin/mysqld021231 09:51:50 mysqld ended 021231 09:57:06 mysqld started /usr/freeware/sbin/mysqld: Too many parameters /usr/freeware/sbin/mysqld Ver 3.23.52 for sgi-irix6.5 on mips Use '--help' or '--no-defaults --help' for a list of available options 021231 09:57:06 mysqld ended 021231 10:05:01 mysqld started /usr/freeware/sbin/mysqld: Fatal error: Can't find messagefile '/usr/freeware/bin/share/mysql/english/errmsg.sys' 021231 10:05:02 mysqld ended 021231 10:05:24 mysqld started /usr/freeware/sbin/mysqld: Fatal error: Can't find messagefile '/usr/freeware/bin/share/mysql/english/errmsg.sys' 021231 10:05:24 mysqld ended [85]server1.bliss.army.mil :/local/mysql!! more server1.bliss.army.mil.err 021231 09:51:09 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 9:51:09 Could not use sever1-bin for logging (error 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Normal shutdown 021231 9:51:09 /usr/freeware/sbin/mysqld: Error on delete of '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 2) 021231 09:51:09 mysqld ended 021231 09:51:50 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 9:51:50 Could not use server1-bin for logging (error 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Normal shutdown 021231 9:51:50 /usr/freeware/sbin/mysqld: Error on delete of '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 2) 021231 9:51:50 /usr/freeware/sbin/mysqld021231 09:51:50 mysqld ended 021231 09:57:06 mysqld started /usr/freeware/sbin/mysqld: Too many parameters /usr/freeware/sbin/mysqld Ver 3.23.52 for sgi-irix6.5 on mips Use '--help' or '--no-defaults --help' for a list of available options 021231 09:57:06 mysqld ended 021231 10:05:01 mysqld started /usr/freeware/sbin/mysqld: Fatal error: Can't find messagefile '/usr/freeware/bin/share/mysql/english/errmsg.sys' 021231 10:05:02 mysqld ended 021231 10:05:24 mysqld started /usr/freeware/sbin/mysqld: Fatal error: Can't find messagefile
HELP for mysql ended error
I am trying to upgrade the mysql database. I am installing on a Unix (SGI's IRIX) platform, upgrading to 3.23.52. I am installing a tar distribution (binary) from SGI's freeware web site. The installation goes fine - no errors - execpt when I try to start mysql it returns mysql ended and will not start. If there are any mysql pro's out there that could help I would really, really, really appreciate it. Thanks When I try to start mysql I get the following error at the command line: [62]my_server_name:/usr/freeware/binsafe_mysqld Starting mysqld daemon with databases from /local/mysql 021231 10:06:19 mysqld ended This is the output from my .err file from mysql: [84]server1.bliss.army.mil :/local/mysqlmore server1.bliss.army.mil.err 021231 09:51:09 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 9:51:09 Could not use server1-bin for logging (error 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Normal shutdown 021231 9:51:09 /usr/freeware/sbin/mysqld: Error on delete of '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 2) 021231 09:51:09 mysqld ended 021231 09:51:50 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 9:51:50 Could not use doe1-bin for logging (error 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Normal shutdown 021231 9:51:50 /usr/freeware/sbin/mysqld: Error on delete of '/local/mysql/doe1.bliss.army.mil.pid' (Errcode: 2) 021231 9:51:50 /usr/freeware/sbin/mysqld021231 09:51:50 mysqld ended 021231 09:57:06 mysqld started /usr/freeware/sbin/mysqld: Too many parameters /usr/freeware/sbin/mysqld Ver 3.23.52 for sgi-irix6.5 on mips Use '--help' or '--no-defaults --help' for a list of available options 021231 09:57:06 mysqld ended 021231 10:05:01 mysqld started /usr/freeware/sbin/mysqld: Fatal error: Can't find messagefile '/usr/freeware/bin/share/mysql/english/errmsg.sys' 021231 10:05:02 mysqld ended 021231 10:05:24 mysqld started /usr/freeware/sbin/mysqld: Fatal error: Can't find messagefile '/usr/freeware/bin/share/mysql/english/errmsg.sys' 021231 10:05:24 mysqld ended [85]server1.bliss.army.mil :/local/mysql!! more server1.bliss.army.mil.err 021231 09:51:09 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 9:51:09 Could not use sever1-bin for logging (error 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 9:51:09 /usr/freeware/sbin/mysqld: Normal shutdown 021231 9:51:09 /usr/freeware/sbin/mysqld: Error on delete of '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 2) 021231 09:51:09 mysqld ended 021231 09:51:50 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 9:51:50 Could not use server1-bin for logging (error 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 9:51:50 /usr/freeware/sbin/mysqld: Normal shutdown 021231 9:51:50 /usr/freeware/sbin/mysqld: Error on delete of '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 2) 021231 9:51:50 /usr/freeware/sbin/mysqld021231 09:51:50 mysqld ended 021231 09:57:06 mysqld started /usr/freeware/sbin/mysqld: Too many parameters /usr/freeware/sbin/mysqld Ver 3.23.52 for sgi-irix6.5 on mips Use '--help' or '--no-defaults --help' for a list of available options 021231 09:57:06 mysqld ended 021231 10:05:01 mysqld started /usr/freeware/sbin/mysqld: Fatal error: Can't find messagefile '/usr/freeware/bin/share/mysql/english/errmsg.sys' 021231 10:05:02 mysqld ended 021231 10:05:24 mysqld started /usr/freeware/sbin/mysqld: Fatal error: Can't find messagefile '/usr/freeware/bin/share/mysql/english/errmsg.sys' 021231 10:05:24 mysqld ended 021231 10:06:19 mysqld started /usr/freeware/sbin/mysqld: File './server1-bin.014' not found (Errcode: 13) 021231 10:06:19 Could not use server1-bin for logging (error 13) 021231 10:06:19 /usr/freeware/sbin/mysqld: Can't create/write to file '/local/mysql/server1.bliss.army.mil.pid' (Errcode: 13) 021231 10:06:19 /usr/freeware/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 021231 10:06:19 /usr/freeware/sbin/mysqld: Error on delete of
Re: ensuring an instructor, client, or room isn't double-booked
David, On Tue 2002-12-31 at 06:47:38 -0500, [EMAIL PROTECTED] wrote: [...] Oh, I see... I've been thinking about that, too; I currently use a datetime field, but all I really need is a date and an hour. I figure with one combined field I can match against -YY-ZZ% but maybe two fields would make it easier to pull out the time column... well, using one field looks fine to me. Both approaches have their advantages and disadvantages and unless you see a particular problem (sounds familiar? ;-), just go with the simpler solution (one column). [...] Now I presume that I will want a unique index for (instructor,timeslot) and a[nother] unique index for (client,timeslot). I can't do a unique index on the place because some rooms are booked for groups (I'm planning to create 12 records for a 12-seat class when it's defined, and then I'll update those records until there are no more with empty client values as I add students). Do I want two unique indexes like that[...]? Correct. [...] You may. If you know anyone who wants to buy it for me for a belated Christmas present, you can suggest that, too :-) :-) [...] % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:77486 % http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115591 Thanks; I'll read them next. [Actually I just popped out and read them.] Oh, I get it; an index is a fast BTree pointer to the data; it's a hash table! Cool. Yes, that's what I want, all right. Just a minor clarification: it's *like* a hash table in its *purpose*. Actually, MySQL even supports real hashes for in-memory tables (TYPE=HEAP), so I thought it's worth pointing out. [...] Yeah. I won't have that few; I'll have about 80 per week (6a thru 7p on six days) for every week from now until the end of time (hey, I can hope that I'll be making money from this software for that long!). So in the beginning I'll have fewer time slots, but by the end of the first year that will very probably have flipped... Good grief; what do you do in that case? Depends (familiar, isn't it? ;-). From what I heard until yet, I assume that MySQL will easily handle your database, so having a sub-perfect index should be fine. So go long-term, since with fewer rows the index doesn't matter that much. Well, I suppose it's an opportunity to charge for an annual tuneup ;-) [...] Of course. ;-) If it is anything like the projects I know, you will correcting your application to client's wishes long before a year has passed. HTH and a HNY ;) Benjamin. -- [EMAIL PROTECTED] - 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
Problems / Reset password / switching to decafe
I been trying to follow the instruction at mysql site to fix mysql, and get it back up and running, so i can add databases. the problem is getting into it (mysql), for some reason the mysqladmin password does not work. so, been forced to kill -9 pid and try the mysql instruction to rest password no luck here is a copy and paste of my terminal window: Trying to start up mysqld; [MacG4a:~] rnagle% mysqld --skip-grant-tables 021231 13:38:05 Can't start server : Bind on unix socket: Address already in use 021231 13:38:05 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 021231 13:38:05 Aborting 021231 13:38:05 mysqld: Shutdown Complete [MacG4a:~] rnagle% Trying another thing; [MacG4a:~] rnagle% mysqladmin -u root password 4321 mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! [MacG4a:~] rnagle% Anyone care to comment on what going on here? I already kill the process, and was trying to restart How does one get back into flushing the passwords, and gaining access, since the instruction don't work. What am I doing wrong, or? what step are missing? or what brand of coffee should I switch to. Big Thanks Rick - 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: GET_LOCK locks
Re: GET_LOCK locks Is there a way to show the status of locks created in this fash- ion? I'm looking for something that would list the locks current in existance, and which thread is holding it. filter: sql, mysql, mysqladmin - 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
req. for help for installing mysql under linux
Hi all, I am a newbie(student) to mysql in linux environment. I was trying to install the mysql-3.23.51-pc-linux-gnu-i686.tar.gz on my linux - mandrake 8.2 from sourceforge.net. When I type in ./configure, it says, there is no need to configure for binary..., then the mysqld starts and closes off immediately... My hostname.err contains the following message... Since, I don't know anything about crashing and signals in linux, can anyone tell me what might have gone wrong with the installation process... after looking at the following or any pointers for installing mysql... (Also, I tried to install mysql-3.23.47-pc-linux-gnu-i686.tar.gz, but it also was having the same problem...) 021231 16:33:50 mysqld started mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=8388600 record_buffer=131072 sort_buffer=2097144 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K bytes of memory Hope that's ok, if not, decrease some variables in the equation Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffda88, stack_bottom=0x1cd1fa80, thread_stack=65536, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x1be1fb70 is invalid pointer thd-thread_id=65537 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 65537 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash 021231 16:33:51 mysqld ended Thanks in advance.. Sireesha _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemailxAPID=42PS=47575PI=7324DI=7474SU= http://www.hotmail.msn.com/cgi-bin/getmsgHL=1216hotmailtaglines_addphotos_3mf - 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
fighting with select distinct
Stuck on this join temp (MySQL ver 3.23) From one table I need: mysql_query(CREATE TEMPORARY TABLE temp TYPE=HEAP SELECT DISTINCT field1, field2 FROM $table); both field1 and field2 have various repeating data and I need to return only the first occurance of the distinct data along with the rest of the data selected below for only those distinct rows. $result = mysql_query(SELECT $table.field3, $table.field2, $table.field1, $table.field4 FROM $table LEFT OUTER JOIN temp ON $table.prikeyfield0 = temp.prikeyfield0 ORDER BY $table.field1 DESC); Running the above kills the distinct select and returns all of the rows. I see where it is broken in that temp.prikeyfield0 is not being collected into the temp table, but as prikeyfield0 is a primary key, all values are distinct and when added to the select distinct statement, all rows are returned. Is there any way to SELECT field0, (DISTINCT field1, field2) FROM table? Sure would make this easy! or SELECT field0 WHERE DISTINCT field1, field2 FROM table? There seems to be very little information anywhere on DISTINCT or perhaps DISTINCT has very few options? Thanks! -- John Hinton - Goshen, VA. http://www.ew3d.com Those who dance are considered insane by those who can't hear the music - 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
loading a db table into a php array from mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I'm so far about knee-deep in my project where I'll be using php to talk to the mysql database and spit out my web pages. I wonder if I should be making individual calls to the database or loading a table into an array so that I can walk it without those calls. For instance, I have an instructors table, a clients table, and a schedule table that has an instructor number column, a client number column, and a time slot column. In order to print an instructor's schedule for the day, I have to query the instructors table to get the id where the name matches, and then query the schedule table to get the clients and times where the time slot matches some time today, and then I have to repeatedly query the clients table to get the names where the returned id matches. Since the schedule should be arranged in time order, I might even have client 1 and then client 2 and then client 1 again -- but I've already switched to 2 so I have to start over for 1. It seems a bit silly to, say, load the entire clients table into an array because there could be thousands or millions of clients, but it's an awful pain to go and make all of those mysql_query calls to walk the clients list. Should I just build a huge query something like $query = select fname,lname from clients where ; foreach ($results_from_previous_query_somehow as $clinum) { $query .= id = '$clinum' or ; } $query .= id = '' ; # nice always-failing value; easier than pruning and then query that way, to get all of what I need in one shot but not the whole table (unless I need it all)? Does sql like big OR clauses like that? I don't know if this is a PHP or a MySQL question, so this once I've posted it to both lists. I'll also summarize to both. TIA HAND Happy New Year mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+Eeq6Gb7uCXufRwARAvYVAJ95HQCrsZLO0JR3rW67kQlG55gnGwCg0qka TFUrVgkkqiqez9gVeCNf0gY= =n9im -END PGP SIGNATURE- - 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: Pattern Match on 3.23
We are running on linux redhat 7.3, RAM 4G, I am using my.huge-cnf. 1.2Ghz dual CPUs PIII. The mySQL database resides on SCSI disk. We do pattern search on varchar or text fields of a table with about 2.6 millions records and going (it also joins with other smaller tables). Hope this helps. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, ..millions of records.. seems like a lot... Would you be kind enough to provide me with your hardware configuration? Thanks. At 07:57 AM 12/31/02 -0800, Qunfeng Dong wrote: If you are searching with %pattern%, your speed is depending on the speed of table-scan. The speed of tablescan depends on your my.cnf setting (increase record buffer size?) and how big your records are. I am using pattern search here with millions of records and the performance is not terriblly too bad. Maybe you can still use fulltext search for general cases; and use pattern match ONLY when you are searching for 3-char-term. You should be able to make such Switch through your interface. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, Thanks for the feedback, I surely appreciate it. I asked the pattern match question, because I am using a hosting service that hosts MySQL 3.23. Since I have a need to search on terms less than 3 characters long and I can not re-compile, I was looking for another solution. I thought that I might be able to use pattern matching as a substitute, but it sounds like performance might be an issue with large tables. If you have any other recommendations on how I could approach my problem, I would surely appreciate them. At 07:35 AM 12/31/02 -0800, Qunfeng Dong wrote: It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - 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 [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 [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 __ 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: GET_LOCK locks
On Tue, Dec 31, 2002 at 01:49:00PM -0500, 'Bob Diss' wrote: Re: GET_LOCK locks Is there a way to show the status of locks created in this fash- ion? I'm looking for something that would list the locks current in existance, and which thread is holding it. No. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 16 days, processed 578,418,725 queries (403/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
Re: Pattern Match on 3.23
Thanks Qunfeng, Hope you have a Happy New Year.! At 11:17 AM 12/31/02 -0800, Qunfeng Dong wrote: We are running on linux redhat 7.3, RAM 4G, I am using my.huge-cnf. 1.2Ghz dual CPUs PIII. The mySQL database resides on SCSI disk. We do pattern search on varchar or text fields of a table with about 2.6 millions records and going (it also joins with other smaller tables). Hope this helps. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, ..millions of records.. seems like a lot... Would you be kind enough to provide me with your hardware configuration? Thanks. At 07:57 AM 12/31/02 -0800, Qunfeng Dong wrote: If you are searching with %pattern%, your speed is depending on the speed of table-scan. The speed of tablescan depends on your my.cnf setting (increase record buffer size?) and how big your records are. I am using pattern search here with millions of records and the performance is not terriblly too bad. Maybe you can still use fulltext search for general cases; and use pattern match ONLY when you are searching for 3-char-term. You should be able to make such Switch through your interface. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: Qunfeng, Thanks for the feedback, I surely appreciate it. I asked the pattern match question, because I am using a hosting service that hosts MySQL 3.23. Since I have a need to search on terms less than 3 characters long and I can not re-compile, I was looking for another solution. I thought that I might be able to use pattern matching as a substitute, but it sounds like performance might be an issue with large tables. If you have any other recommendations on how I could approach my problem, I would surely appreciate them. At 07:35 AM 12/31/02 -0800, Qunfeng Dong wrote: It can perform pattern match on text field. The only draw back is the speed (especially if you are using %pattern% to do the search) when you tables are getting huge, since there is no index to help. Qunfeng --- Frank Peavy [EMAIL PROTECTED] wrote: I would like to use pattern matching as a substitute to fulltext search on MySQL 3.23. Is this a good alternative? Are there any limits, like not being able to perform a pattern match on a 'text' field, etc., that I need to be aware of? Thanks. - 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 [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 [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 __ 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
SUMMARY - Re: loading a db table into a php array from mysql
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- ...and then David T-G said... % % I'm so far about knee-deep in my project where I'll be using php to talk % to the mysql database and spit out my web pages. I wonder if I should be % making individual calls to the database or loading a table into an array % so that I can walk it without those calls. A little RTFMing later, after some helpful pointers, our hero returns. Gee, look what a complex join can do for me: mysql select substring(s.timeslot,1,13),i.fname,concat(c.fname,' ',c.lname) from personnel as i, clients as c, schedule as s where i.id = s.instr and c.id = s.client and i.fname = 'penelope' order by timeslot; ++--+-+ | substring(s.timeslot,1,13) | fname| concat(c.fname,' ',c.lname) | ++--+-+ | 2002-12-27 06 | penelope | david t-g | | 2002-12-27 07 | penelope | david t-g | | 2002-12-27 08 | penelope | laura t-g | | 2002-12-27 10 | penelope | david t-g | ++--+-+ 4 rows in set (0.00 sec) All of what I want in one single call :-) I'll probably have to refine this a bit as I learn more about how to use it, but I'm on my way. Thanks again HAND HNY mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+Ef15Gb7uCXufRwARAiumAKCeJaDVTj2lULSiguWUhueZ33nQAQCgm8df MY7UobhF+klun/sBa9RF4jY= =6NDi -END PGP SIGNATURE- - 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: Problems / Reset password / switching to decafe
Rick, What messages are you seeing in the .err file in your logging directory? I typically run my servers with the KDE up and I use the service configuration tool to restart services. Have you tried to fire up MySQLCC and connect to your server? Dave -Original Message- From: Richard Nagle [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 31, 2002 12:45 PM To: [EMAIL PROTECTED] Subject: Problems / Reset password / switching to decafe I been trying to follow the instruction at mysql site to fix mysql, and get it back up and running, so i can add databases. the problem is getting into it (mysql), for some reason the mysqladmin password does not work. so, been forced to kill -9 pid and try the mysql instruction to rest password no luck here is a copy and paste of my terminal window: Trying to start up mysqld; [MacG4a:~] rnagle% mysqld --skip-grant-tables 021231 13:38:05 Can't start server : Bind on unix socket: Address already in use 021231 13:38:05 Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 021231 13:38:05 Aborting 021231 13:38:05 mysqld: Shutdown Complete [MacG4a:~] rnagle% Trying another thing; [MacG4a:~] rnagle% mysqladmin -u root password 4321 mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! [MacG4a:~] rnagle% Anyone care to comment on what going on here? I already kill the process, and was trying to restart How does one get back into flushing the passwords, and gaining access, since the instruction don't work. What am I doing wrong, or? what step are missing? or what brand of coffee should I switch to. Big Thanks Rick - 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: Privileges in MySQL 4.0.7 (upgrade)
At 14:26 31/12/2002 +0100, Stefan Hinz, iConnect (Berlin) wrote: Hi, Dear list, no one has answered my question up to now. Looking through the manual again today, I still can't figure out what to do. Sorry but your issue is my fault. In the next release I will update the tables. In the meantime you should do the following: - I send for you the tables on separate e-mail. or You install Cygwin in your machine and I send for you the mysql_fix_privilege_tables.sh script in separate e-mail. Edit the @bindir@ changing for your path, for example I did: /home/Administrador. Copy the mysql.exe client to this directory. Run the server. Run the below command e.g.: Administrador@LIGHT ~ $ ./mysql_fix_privilege_tables.sh This scripts updates the mysql.user, mysql.db, mysql.host and the mysql.func table to MySQL 3.22.14 and above. This is needed if you want to use the new GRANT functions, CREATE AGGREAGATE FUNCTION or want to use the more secure passwords in 3.23 If you get Access denied errors, you should run this script again and give the MySQL root user password as a argument! Converting all privilege tables to MyISAM format If your tables are already up to date or partially up to date you will get some warnings about 'Duplicated column name'. You can safely ignore these! ERROR 1060 at line 2: Duplicate column name 'File_priv' Creating Grant Alter and Index privileges if they don't exists You can ignore any Duplicate column errors ERROR 1060 at line 1: Duplicate column name 'Grant_priv' Adding columns needed by GRANT .. REQUIRE (openssl) You can ignore any Duplicate column errors Creating the new table and column privilege tables Changing name of columns_priv.Type - columns_priv.Column_priv You can ignore any Unknown column errors from this ERROR 1054 at line 1: Unknown column 'Type' in 'columns_priv' Fixing the func table You can ignore any Duplicate column errors ERROR 1060 at line 1: Duplicate column name 'type' Adding new fields used by MySQL 4.0.2 to the privilege tables You can ignore any Duplicate column errors Updating new privileges in MySQL 4.0.2 from old ones -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ São Paulo - Brazil ___/ 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
simple (I thought) delete question
Hi folks, I hope that dumb questions are allowed in here ... I have two MySQL tables and I would like to delete records from one based on associated values in another. For example, using the example tables below, how would I delete all the records in the TIDS table that had an associative value of 'Air Force' in the TID_ADMIN table? TID_ADMIN ID (pk) | Service 1 Army 2 Navy 3 Air Force TIDS -- ID | ADMIN_ID | Project Name -- 1 1 Project X 2 1 Project Y 3 2 Vision C 4 3 Clearout T 5 3 Clearout F 6 3 Trustee 433 I have tried, to no avail, using variants of ... DELETE FROM tids, tid_admin WHEREtid_admin.admin_id = tids.admin_id AND tid_admin.service = 'Air Force' Can someone point me in the right direction. I'm still feeling my way through all this. Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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: Replication priority / speed
Or, alternatly, is there a way to limit the slave thread to only X bin-log transactions per second? There is not. Any plan to add this feature? I would think it'd be useful... Wouldn't it be better to *solve* your problem instead of going around it? Well, as this is how our software is designed, I'm going at solving it as best I can... The problem, as I see it, is the MySQL slaves consume the system (load average goes very high-- most likely I/O bound as someone else said) when there's a lot of updates to do at once. Therefor, I'm looking to MySQL resources to help solve the problem. MyTOP says our key efficiency is 97.35%, with an average of 1.24 q/sec (on the master-- most queries are done directly on the slave, with only updates happening on the master). We've optimized things as best we can. 1.24 q/sec doesn't sound loaded at all. Nope, it's not loaded up much at all, except when somebody makes a huge change or bulk-load (not terrably often, but expected to be more)... How many queries/second do you see on your slave box? 13.75, according to MyTOP... How many slow queries per hour? MyTOP says this particular slave has been up 47 days, 2 hours... It's had 56M queries, of those 4,559 were slow... Not being a MySQL expert, I'm not sure how to get slow queries/hour directly from MySQL. You say you that most queries are done directly on the slave. Why don't you spread the SELECTs across both boxes? Do you mean using a load balancer accross all the slaves? That would defeat the purpose of having a local slave on each web server-- that purpose being to return results as quickly as possible (network traffic is expensive compaired to local disk)... The problem is our customers are allowed to bulk-load keywords into our database, which causes about 4 large tables to be updated quite a bit. Whenever this happens, the slaves struggle to get caught back up... Have you tried enabling DELAY_KEY_WRITE on the 4 tables that your bulk-loader updates? I'm using DELAY_KEY_WRITE on my slaves... I didn't know it, but I just looked and it says ON in 'show variables';... When looking through the docs on this, I also found low_priority_updates, which I could set on the slaves-- would this help? Why not spread the bulk-load in time, so that the keywords aren't added instantly? We're looking into this now, as well-- some type of log that would keep track of the large updates, and insert/update/delete them later, a few at a time. The problem is we lose the real-time notification of success/failure... Why not modify your software so that it doesn't read from the slave while your bulk-loader runs? The bulk load can happen on any web server, how would it notify the others? Where would they query instead? Can you give some more information on your master slave config? (hardware, OS, MySQL show variables, show status) RedHat 6.2 on i386, stock RPM install of MySQL-3.23.36-1... The only options we set up on the slaves are to connect to the master-- everything else is stock... Thanks again for the help, -Matt Sturtz- - 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: simple (I thought) delete question
Richard, Tuesday, December 31, 2002, 4:12:15 PM, you wrote: Hi folks, I hope that dumb questions are allowed in here ... I have two MySQL tables and I would like to delete records from one based on associated values in another. For example, using the example tables below, how would I delete all the records in the TIDS table that had an associative value of 'Air Force' in the TID_ADMIN table? TID_ADMIN ID (pk) | Service 1 Army 2 Navy 3 Air Force TIDS -- ID | ADMIN_ID | Project Name -- 1 1 Project X 2 1 Project Y 3 2 Vision C 4 3 Clearout T 5 3 Clearout F 6 3 Trustee 433 I have tried, to no avail, using variants of ... DELETE FROM tids, tid_admin WHEREtid_admin.admin_id = tids.admin_id AND tid_admin.service = 'Air Force' Can someone point me in the right direction. I'm still feeling my way through all this. Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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 Now i don't use the multitable deletes much .. at least not yet but i think DELETE tids FROM tids, tid_admin WHEREtids.admin_id = tid_admin.id AND tid_admin.service = 'Air Force' should delete the rows from tids you wanted -- Brian Lindner - 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: simple (I thought) delete question
if you want to do it in one statement, you'll need to wait for subselects to be implemented in MySql. Apparently this is underway and was scheduled to appear in MySql 4.1 if subselects were supported, you'd do something like this: delete from tids where exists ( select 1 from tids_admin where tids_admin.id = tids.admin_id) otherwise, you'll need to issue multiple statements. e.g. 1. locate records to delete 2. delete records either in bulk using an IN (id1, id2, id3,) expression or just deleting one at a time greg. - Original Message - From: Richard Forgo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 01, 2003 8:12 AM Subject: simple (I thought) delete question Hi folks, I hope that dumb questions are allowed in here ... I have two MySQL tables and I would like to delete records from one based on associated values in another. For example, using the example tables below, how would I delete all the records in the TIDS table that had an associative value of 'Air Force' in the TID_ADMIN table? TID_ADMIN ID (pk) | Service 1 Army 2 Navy 3 Air Force TIDS -- ID | ADMIN_ID | Project Name -- 1 1 Project X 2 1 Project Y 3 2 Vision C 4 3 Clearout T 5 3 Clearout F 6 3 Trustee 433 I have tried, to no avail, using variants of ... DELETE FROM tids, tid_admin WHEREtid_admin.admin_id = tids.admin_id AND tid_admin.service = 'Air Force' Can someone point me in the right direction. I'm still feeling my way through all this. Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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: simple (I thought) delete question
Didn't fully read your question. Need the extra bit in the subselect to identify air force rows only, but again, this won't work until 4.1 with subselects is released. delete from tids where exists ( select 1 from tids_admin where tids_admin.id = tids.admin_id and tids_admin.service = 'Air Force') - Original Message - From: Greg Matthews [EMAIL PROTECTED] To: Richard Forgo [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, January 01, 2003 9:29 AM Subject: Re: simple (I thought) delete question if you want to do it in one statement, you'll need to wait for subselects to be implemented in MySql. Apparently this is underway and was scheduled to appear in MySql 4.1 if subselects were supported, you'd do something like this: delete from tids where exists ( select 1 from tids_admin where tids_admin.id = tids.admin_id) otherwise, you'll need to issue multiple statements. e.g. 1. locate records to delete 2. delete records either in bulk using an IN (id1, id2, id3,) expression or just deleting one at a time greg. - Original Message - From: Richard Forgo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 01, 2003 8:12 AM Subject: simple (I thought) delete question Hi folks, I hope that dumb questions are allowed in here ... I have two MySQL tables and I would like to delete records from one based on associated values in another. For example, using the example tables below, how would I delete all the records in the TIDS table that had an associative value of 'Air Force' in the TID_ADMIN table? TID_ADMIN ID (pk) | Service 1 Army 2 Navy 3 Air Force TIDS -- ID | ADMIN_ID | Project Name -- 1 1 Project X 2 1 Project Y 3 2 Vision C 4 3 Clearout T 5 3 Clearout F 6 3 Trustee 433 I have tried, to no avail, using variants of ... DELETE FROM tids, tid_admin WHEREtid_admin.admin_id = tids.admin_id AND tid_admin.service = 'Air Force' Can someone point me in the right direction. I'm still feeling my way through all this. Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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: An Idea
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stefan -- ...and then Stefan Hinz, iConnect (Berlin) said... % % David, % % regarding the MySQL FAQ: % % Why should it have to be there? Let anyone with a site set it up and ... % % MySQL.com would be the natural place for the FAQ. Any other place % wouldn't be half as good. Oh, to be sure. And maybe we could even get the mysql.com folks to prominently list a pointer to the off-site FAQ if they don't want to maintain it or give out accounts to maintain it. I just wouldn't want to see it dropped if they don't jump on it at the start. HAND Happy New Year mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+EjfnGb7uCXufRwARAvWNAJ9GWPaZm2tjJh4pdQNNG7EV9cdxLACdGWpV tC44gsIMkjgUkNtZlkpZ+Y0= =XS30 -END PGP SIGNATURE- - 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
load data local infile
Hi, I have just upgraded to 3.23.54 How to input bulk data into table? I have tried: LOAD DATA LOCAL INFILE lcopen1.txt INTO TABLE lcopen; but it shows: ERROR 1148: The used command is not allowed with this MySQL version How come? What method can I use to input bulk data? Terence Ng _ Lonely Christmas(³¯«³¨³)¡A·ºå»P·(Twins)¡AµL¶¡¹D(¹q¼v)... ¦ÜIn¤â¾÷¹aÁn ³§A¹L¥V¤Ñ http://ringtone.yahoo.com.hk - 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: simple (I thought) delete question
I guess I am a bit confused at the response below... Please see: http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html Why can't the foreign key logic be used in version 3.23?.. At 09:45 AM 1/1/03 +1100, Greg Matthews wrote: Didn't fully read your question. Need the extra bit in the subselect to identify air force rows only, but again, this won't work until 4.1 with subselects is released. delete from tids where exists ( select 1 from tids_admin where tids_admin.id = tids.admin_id and tids_admin.service = 'Air Force') - Original Message - From: Greg Matthews [EMAIL PROTECTED] To: Richard Forgo [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, January 01, 2003 9:29 AM Subject: Re: simple (I thought) delete question if you want to do it in one statement, you'll need to wait for subselects to be implemented in MySql. Apparently this is underway and was scheduled to appear in MySql 4.1 if subselects were supported, you'd do something like this: delete from tids where exists ( select 1 from tids_admin where tids_admin.id = tids.admin_id) otherwise, you'll need to issue multiple statements. e.g. 1. locate records to delete 2. delete records either in bulk using an IN (id1, id2, id3,) expression or just deleting one at a time greg. - Original Message - From: Richard Forgo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 01, 2003 8:12 AM Subject: simple (I thought) delete question Hi folks, I hope that dumb questions are allowed in here ... I have two MySQL tables and I would like to delete records from one based on associated values in another. For example, using the example tables below, how would I delete all the records in the TIDS table that had an associative value of 'Air Force' in the TID_ADMIN table? TID_ADMIN ID (pk) | Service 1 Army 2 Navy 3 Air Force TIDS -- ID | ADMIN_ID | Project Name -- 1 1 Project X 2 1 Project Y 3 2 Vision C 4 3 Clearout T 5 3 Clearout F 6 3 Trustee 433 I have tried, to no avail, using variants of ... DELETE FROM tids, tid_admin WHEREtid_admin.admin_id = tids.admin_id AND tid_admin.service = 'Air Force' Can someone point me in the right direction. I'm still feeling my way through all this. Rik Forgo JIST3 Army Test, Training and Technology Integration Office (T3I) Diverse Technologies Corp. (c) 443.463.8571 (h) 410.859.8474 - 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 - 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: An Idea
we find people just like to ask questions, and no matter how good our FAQ's and help are, many people have circumstances that make it more efficient to push the question into the queue, and wait for an answer to pop back later. lists work, and faq's work, some like to call...etc. personally, I'd prefer a search engine style... like google, but only for mySQL topics, and with a visible list of most popular search terms. something that can be based upon pages of htm and emails that exists, and that can sit under a few web pages using Htdig or alkaline or something... Guten Rutsch Jim -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 31, 2002 4:36 PM To: mysql users Cc: Stefan Hinz, iConnect (Berlin) Subject: Re: An Idea -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stefan -- ...and then Stefan Hinz, iConnect (Berlin) said... % % David, % % regarding the MySQL FAQ: % % Why should it have to be there? Let anyone with a site set it up and ... % % MySQL.com would be the natural place for the FAQ. Any other place % wouldn't be half as good. Oh, to be sure. And maybe we could even get the mysql.com folks to prominently list a pointer to the off-site FAQ if they don't want to maintain it or give out accounts to maintain it. I just wouldn't want to see it dropped if they don't jump on it at the start. HAND Happy New Year mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+EjfnGb7uCXufRwARAvWNAJ9GWPaZm2tjJh4pdQNNG7EV9cdxLACdGWpV tC44gsIMkjgUkNtZlkpZ+Y0= =XS30 -END PGP SIGNATURE- - 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: An Idea
On 12/31/02 8:11 PM, JamesD [EMAIL PROTECTED] wrote: lists work, and faq's work, some like to call...etc. personally, I'd prefer a search engine style... like google, but only for mySQL topics, and with a visible list of most popular search terms. something that can be based upon pages of htm and emails that exists, and that can sit under a few web pages using Htdig or alkaline or something... Guten Rutsch Jim Isn't that funny: if I have a mysql related question and search google I end up in the mysql online documentation in 90% of cases. JM2Cs /h - 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: load data local infile
sound like you may want to add the following to your my.cnf file for MySQL [mysqld] local-infile=1 [mysql] local-infile=1 - hcir On Tuesday, December 31, 2002, at 06:23 PM, Terence Ng wrote: Hi, I have just upgraded to 3.23.54 How to input bulk data into table? I have tried: LOAD DATA LOCAL INFILE lcopen1.txt INTO TABLE lcopen; but it shows: ERROR 1148: The used command is not allowed with this MySQL version How come? What method can I use to input bulk data? - 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