RE: why does it not work
On Mon, 21 May 2001, indrek siitan wrote: order by rand() didn't work back in 3.22. if i remember correctly (if this doesn't work, you can try to search the list archives), the workaround was: SELECT col1,col2,RAND() as r FROM table ORDER BY r LIMIT 1; Thanks. Now I am into another problem. I am not sure whether it is php or mysql. Though it works in the command prompt of mysql. Below is the snippet of my php script. What I am trying to do is to randomly display a scroll from the special table. This happens when the page is loaded or refreshed. // sql statement $searchStmt = select special.contid, special.cityid, special.curr1, special.catalogo, special.hf, special.curr2, special.vitofferta, special.fh, special.club, special.stars, city.city,RAND as r from special,city where DATE_SUB(special.periodo, INTERVAL 3 DAY) '$tdate' and city.id=special.cityid order by r limit 1 ; var timerID=null var run=false count = 10 // counter of cycles ScrollSpeed = 800; // milliseconds between scrolls ScrollChars = 1;// chars scrolled per time period function Stop() { if(run) clearTimeout(timerID) run=false } function Start() { Stop(); Marquee() } function Marquee() { var msg = document.forms[0].message.value; var tmp = msg.substring(0,ScrollChars); msg = msg.substring(ScrollChars) + tmp; document.forms[0].message.value = msg; if(count200) { timerID = setTimeout('Marquee()',ScrollSpeed); run= true; count++; } } !-- end -- /script form ?php while($row = mysql_fetch_object($result)) { ? input name=message size=57 value=?php echo $row-city . , . Catalogo Prezzo . . $row-curr1 . . $row-catalogo . . $row-hf . , . Vitofferta . . $row-curr2 . . $row-vitofferta . . $row-fh; ? ?php } ? /form - 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: Telnet and ftp problem
Joseph Bueno wrote: Your problem seems very strange ! Yes it seems... How do you start and stop mysql ? With the standard SuSE startup script /sbin/init.d/mysql MySQL is running on port 3306 BTW, on which OS are you running ? SuSE Linux 6.4 I must add that the telnetd daemon reports the login call by putting a connect from in /var/log/messages, but still the telnet prompt on the remote server does not appear. Same for ftp. It's a maze. Bye and thank you for your answer. - 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
ÇëÇó°ïÖú
ÄãºÃ£¬´òÈÅÄúÁË£¬ÎÒÊÇÒ»ÃûÐÂÊÖ£¬ÏÖÔÚÎÒ°´ÊéÉϵIJ½ÖèÔÚWIN98Ï°²×°ÁËmysql-3.23.38-win.zip£¬ÏÖÔÚ³öÕâÑùµÄÎÊÌâ µ±ÔÚC£º\MYSQL\BIN MYSQLD Ö´ÐÐʱ³öÏÖ´íÎó can't initialize innodb as 'innodb_data_file_path' is not set ÇëÎÊÎÒÓ¦¸ÃÔõÑùÉèÖᣠ__ === ÐÂÀËÃâ·Ñµç×ÓÓÊÏä (http://mail.sina.com.cn) Ñ°ÕÒÐÂÏÊ, Ó®¿ÆÁú±ùÏä! (http://ad.doubleclick.net/clk;2847753;5579344;y?http://gd.sina.com.cn/ad/kelon) ʹÓÃÊÖ»ú¶ÌÐÅ¡°ÓʼþÌáÐÑ¡±¹¦ÄÜ£¬ËæʱÁ˽âµÄÊÕÐÂÐÅÇé¿ö£¡ (http://sms.sina.com.cn/docs/sina_mailalert.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
Re: count(*) on different tables
is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Select 'Table1' as tableName, count(*) as rowCount from table1 UNION Select 'Table2' as tableName, count(*) as rowCount from table2 ... etc Assuming that MySQL can use the UNION clause. (I haven't checked) I think MySQL actually doesn't support union. ;/ I wonder if this is so difficult.. Only getting rowcounts of different tables in one result. I tried also the folliowing: select count(*) from table1, table2. But this (curiously) returns table1rowcount * table2rowcount Greetings, Ansgar - 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:
I do not know the answer myself as I am using linux, not windoze. I'm only translating it into English. Hi, I'm a newbie. I have followed the procedure in the manuals and installed mysql-3.23.38-win.zip onto a Win98 system. I am now getting the following problem. when running C£º\MYSQL\BIN MYSQLD I get the following error can't initialize innodb as 'innodb_data_file_path' is not set What configuration options do I need to set to fix this problem? - Original Message - From: tsqvictor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 15:33 Subject: ÇëÇó°ïÖú ÄãºÃ£¬´òÈÅÄúÁË£¬ÎÒÊÇÒ»ÃûÐÂÊÖ£¬ÏÖÔÚÎÒ°´ÊéÉϵIJ½ÖèÔÚWIN98Ï°²×°ÁË mysql-3.23.38-win.zip£¬ÏÖÔÚ³öÕâÑùµÄÎÊÌâ µ±ÔÚC£º\MYSQL\BIN MYSQLD Ö´ÐÐʱ³öÏÖ´íÎó can't initialize innodb as 'innodb_data_file_path' is not set ÇëÎÊÎÒÓ¦¸Ã ÔõÑùÉèÖᣠ__ === ÐÂÀËÃâ·Ñµç×ÓÓÊÏä (http://mail.sina.com.cn) Ñ°ÕÒÐÂÏÊ, Ó®¿ÆÁú±ùÏä! (http://ad.doubleclick.net/clk;2847753;5579344;y?http://gd.sina.com.cn/ad/ke lon) ʹÓÃÊÖ»ú¶ÌÐÅ¡°ÓʼþÌáÐÑ¡±¹¦ÄÜ£¬ËæʱÁ˽âµÄÊÕÐÂÐÅÇé¿ö£¡ (http://sms.sina.com.cn/docs/sina_mailalert.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 - 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
Problem with mysql connections.
Dear Sir, We installed MySql server on one system. I have the test database, and My own database named Gomoos. When i using to connect to the database using java program with a jdbc driver i am not able to connect to my database Gomoos .But i am able to connect to test database. When i am using localhost insted of IPaddress then i amable to connect to any database on local system. But from a remote system iam able to connect to onlytest database. I am getting an error saying access denied for user: '@myIPaddress' Please give me a soultion to resolve this problem. Thanks Regards Niranjan Babu.P - 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: Problem with mysql connections.
see GRANT in the manual. Snehalata wrote: Dear Sir, We installed MySql server on one system. I have the test database, and My own database named Gomoos. When i using to connect to the database using java program with a jdbc driver i am not able to connect to my database Gomoos .But i am able to connect to test database. When i am using localhost insted of IPaddress then i amable to connect to any database on local system. But from a remote system iam able to connect to onlytest database. I am getting an error saying access denied for user: '@myIPaddress' Please give me a soultion to resolve this problem. Thanks Regards Niranjan Babu.P - 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: telnet MySQL
you should use the client to connect to your mysql since the server doesn't use the standard telnet protocol. The client is available for several OSes. For examply mysql -u peter -h grdmss57 -p Would connect to grdmss57 as user peter and would then ask for a password (which is safer than entering it on the command line) regards On Mon, 14 May 2001, Peter van Dijk wrote: On Mon, May 14, 2001 at 07:26:44AM -0700, Shtykh Roman wrote: When I try to telnet MySQL which uses port 3030 as below: telnet grdmss57 3030 I get : Trying 163.143.156.38... Connected to grdmss57.u-aizu.ac.jp. Escape character is '^]'. ( 3.23.38jN(xIDWiConnection closed by foreign host. What is the possible reason? Is it normal? This is normal. Don't telnet to your mysqld :) Greetz, Peter. - 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: Re:
At 15:47 22/5/2001 +0800, Rolf Hopkins wrote: Hi, Please translate for him, to use on Win98 the mysql-opt.exe if he wants to use the MyISAM table, otherwise to use mysqld-max.exe to have support of InnoDB and BDB and to include on my.ini/my.cnf file the keys for InnoDB. The mysqld.exe has problems to run on Win9x (the InnoDB lib was compiled with the NT pre-processor define). Regards, Miguel I do not know the answer myself as I am using linux, not windoze. I'm only translating it into English. Hi, I'm a newbie. I have followed the procedure in the manuals and installed mysql-3.23.38-win.zip onto a Win98 system. I am now getting the following problem. when running C£º\MYSQL\BIN MYSQLD I get the following error can't initialize innodb as 'innodb_data_file_path' is not set What configuration options do I need to set to fix this problem? - Original Message - From: tsqvictor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 15:33 Subject: ÇëÇó°ïÖú ÄãºÃ£¬´òÈÅÄúÁË£¬ÎÒÊÇÒ»ÃûÐÂÊÖ£¬ÏÖÔÚÎÒ°´ÊéÉϵIJ½ÖèÔÚWIN98Ï°²×°ÁË mysql-3.23.38-win.zip£¬ÏÖÔÚ³öÕâÑùµÄÎÊÌâ µ±ÔÚC£º\MYSQL\BIN MYSQLD Ö´ÐÐʱ³öÏÖ´íÎó can't initialize innodb as 'innodb_data_file_path' is not set ÇëÎÊÎÒÓ¦¸Ã ÔõÑùÉèÖᣠ__ === ÐÂÀËÃâ·Ñµç×ÓÓÊÏä (http://mail.sina.com.cn) Ñ°ÕÒÐÂÏÊ, Ó®¿ÆÁú±ùÏä! (http://ad.doubleclick.net/clk;2847753;5579344;y?http://gd.sina.com.cn/ad/ke lon) ʹÓÃÊÖ»ú¶ÌÐÅ¡°ÓʼþÌáÐÑ¡±¹¦ÄÜ£¬ËæʱÁ˽âµÄÊÕÐÂÐÅÇé¿ö£¡ (http://sms.sina.com.cn/docs/sina_mailalert.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 - 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 __ ___ __ / |/ /_ __/ __/ __ \/ / http://www.mysql.com/ / /|_/ / // /\ \/ /_/ / /__ Miguel Solórzano [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ São Paulo, Brazil ___/ Development Team - 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
istalling
Hi, I have just installed RH6.2 as server and now want to install Apache+MySQL+PHP4. Is it better to download MySQL rpm version OR src.rpm OR .tar.gz? I think Apache needs to be installed first them MySQL (does MySQL need to be aware of Apache) then PHP with mysql and with apache extensions. I am not sure if all three need to be installed from their source or not? Regards Ajdin - 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
How can be minimized downtime on MyISAM-InnoDB transition?
Hi, We recently decided to start switching on InnoDB for some our web projects. We have tested InnoDB on test machines and have been satisifed with results. Now the question: how can we minimize downtime on production server during transition? We see two choises: 1) change mysql server to single-user mode and change types of tables with 'alter table TABLE name=InnoDB', start mysql server to normal mode again 2) change mysql server to single-user mode and dump tables with mysqldump --quick, create new tables of InnoDB type without indexes, restore their content from dump, restore indexes, start mysql server to normal mode again. We can afford several hours of downtime so we don't want to bother with too complex solutions for this transitions like setting up replication server and so on. Some info on tables: most of them are quite small except one which has about 1,800,000 records (size of MyISAM files about 70Mb). -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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: secure connection (SSL/SSH tunneling) with windows client
On Tue, May 22, 2001 at 12:51:50AM +, [EMAIL PROTECTED] wrote: I know this has been discussed a lot. I did read the mail archive and found lots of interesting info. Although I have a problem. We're developing a windows software that has to exchange sensitive data with a mySQL server. I read about implementing a SSH tunnel. It works well, although I have not found a free ssh client for windows. Even if I did, it would not be really helpful as we don't want to explain to our users they have to install a third party software that does IP tunneling in order to transmit data securely. Any suggestions? Even if SSL support was added to mySQL, I guess I'd still have to wait for SSL support in the client libs we use on windows (ZeosLib)... Hmm. If you want encryption, somebody has to add it at some point in time... For free SSH clients: you may find links via http://www.openssh.com/ The problem with SSH tunneling is that you need an account on the target machine to open the SSH connection. It has the advantage that once the connection has been established, no further overhead is added (except the actual encryption/decryption). For SSL: if you don't integrate SSL into your product, you can use the stunnel software (http://www.stunnel.org) to tunnel your connections. This works without accounts on the target machines. You may see a significant performance impact, as each new SSL connection requires a cryptograpic handshake. (Every time a connection to the database is opened. If you open it once and leave it open, that's fine. If you are using open-transaction-close cycles, its horrible. In the long run for a complete integration this problem can be tackled with session caching but it is not available for an stunnel solution, AFAIK.) http://www.stunnel.org/examples/mysql.html I am ready to encrypt every sensitive fields in the database, although I am wondering if the username and password will be transferred in plain text. I've seen somewhere that it isn't, but what kind of security/encryption is used and can we consider it safe? Both the designers of the SSH and the TLS/SSL protocols have spent a considerable amount of time to design the protocols to be secure and tamper proof (think of man-in-the-middle attacks) when correctly used and it took more than one iteration to bring it to the state it is now. I would therefore recommend to stay with these solutions. Best regards, Lutz -- Lutz Jaenicke [EMAIL PROTECTED] BTU Cottbus http://www.aet.TU-Cottbus.DE/personen/jaenicke/ Lehrstuhl Allgemeine Elektrotechnik Tel. +49 355 69-4129 Universitaetsplatz 3-4, D-03044 Cottbus Fax. +49 355 69-4153 - 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
Q: SQL query problems
Hello, I have a problem composing a sql-statement for my special needs. I hope anybody can help me. This is the situation: I have 2 tables, a and b as following: -- | a | -- | eid | name | -- -- | b| -- | id | gid | eid | -- *) a.eid and b.id are primary keys *) b.eid points to a.eid entries Now my wish: *) b.gid = fixed given value *) i want all entries from a , which are NOT referenced by any entries from b.eid where b.gid = fixed given value Is this possible with only one sql-statement? - I have tried over and over, but didn't get a correct sollution. One of my tries was: select a.eid from a,b where b.gid = fixed and a.eid != b.eid but it didn't work out. Any help is welcome! THX -- greetings - WEMMER Alexander [EMAIL PROTECTED] Telematik Student at the Technical University of Graz, Austria. https://wemmer.at/ - 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: Re:
¶Ô²»Æð, ÎÒÖÐÎÄÖ»»¹ºÃ... Èç¹ûÄãÒªÓà MyISAM tables, ÔÚWin98ÉÏÃæÄãÓ¦¸ÃÓÃmysql-opt.exe. Èç¹ûÄãÒªInnoDB ¸ú BDB support, ÔÚWin98ÉÏÃæÄãÓ¦¸ÃÓÃmysqld-max.exeÒ²°üÀ¨InnoDBµÄkeysÔÚÀïÃæµÄ my.ini/my.cnfµµ°¸. ÔÚWin9xÉÏÃæÓÃmysqld.exeÓÐÎÊÌâÒòΪmysqld.exeÊÇNTµÄ°æ±¾. InnoDBµÄlibraryÓÐNT support. - Original Message - From: Miguel Angel Sol¨®rzano [EMAIL PROTECTED] To: Rolf Hopkins [EMAIL PROTECTED]; tsqvictor [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 16:17 Subject: Re: Re: ÇëÇó°ïÖú At 15:47 22/5/2001 +0800, Rolf Hopkins wrote: Hi, Please translate for him, to use on Win98 the mysql-opt.exe if he wants to use the MyISAM table, otherwise to use mysqld-max.exe to have support of InnoDB and BDB and to include on my.ini/my.cnf file the keys for InnoDB. The mysqld.exe has problems to run on Win9x (the InnoDB lib was compiled with the NT pre-processor define). Regards, Miguel I do not know the answer myself as I am using linux, not windoze. I'm only translating it into English. Hi, I'm a newbie. I have followed the procedure in the manuals and installed mysql-3.23.38-win.zip onto a Win98 system. I am now getting the following problem. when running C¡êo\MYSQL\BIN MYSQLD I get the following error can't initialize innodb as 'innodb_data_file_path' is not set What configuration options do I need to set to fix this problem? - Original Message - From: tsqvictor [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 15:33 Subject: ???¨®¡ã??¨² ??o?¡ê?¡ä¨°¨¨??¨²¨¢?¡ê??¨°¨º?¨°???D?¨º?¡ê ¨²?¨°¡ã¡ä¨º¨¦¨¦?¦Ì?2??¨¨?¨²WIN98??¡ã2¡Á¡ã¨¢? mysql-3.23.38-win.zip¡ê¨²3??a?¨´¦Ì??¨º¨¬a ¦Ì¡À?¨²C¡êo\MYSQL\BIN MYSQLD ?¡äDD¨º¡À3???¡ä¨ª?¨® can't initialize innodb as 'innodb_data_file_path' is not set ???¨º?¨°¨®|?? ???¨´¨¦¨¨???¡ê __ === D?¨¤??a¡¤?¦Ì?¡Á¨®¨®¨º?? (http://mail.sina.com.cn) ?¡ã?¨°D??¨º, ¨®???¨¢¨²¡À¨´??! (http://ad.doubleclick.net/clk;2847753;5579344;y?http://gd.sina.com.cn/ad/k e lon) ¨º1¨®?¨º??¨²?¨¬D??¡ã¨®¨º?t¨¬¨¢D??¡À1|?¨¹¡ê???¨º¡À¨¢??a¦Ì?¨º?D?D??¨¦?? ¡ê? (http://sms.sina.com.cn/docs/sina_mailalert.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 - 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 __ ___ __ / |/ /_ __/ __/ __ \/ / http://www.mysql.com/ / /|_/ / // /\ \/ /_/ / /__ Miguel Sol¨®rzano [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ S?o Paulo, Brazil ___/ Development Team - 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
JOIN optimization
Hi, I'm rather new to SQL (well, I had a one-year course at the university many years ago, but they didn't teach us anything practical). I did a few very simple data bases, but now I'm moving to something more serious with joins and 'group by'. The idea is to create a search engine for a battery distributor, specifying various characteristics like * who makes it (only one per battery) * special characteristics (various) * tipical applications (various) To simplify, I have the following tables: Battery: * batID (primary key) * makID * descr Maker: * makID (primary key) * mak Special: * speID (primary key) * special Bat_Spe: * batID * speID (both in primary key) Tipical: * tipID (primary key) * tipical Bat_Tip: * batID * tipID (primary key) First I want to make a list of batteries with all the carateristics they have: SELECT M.mak, B.descr, S.special, T.tipical FROM Battery B, Maker M, Special S, Bat_Spe BS, Tipical T, Bat_Tip BT WHERE M.makID=B.makID AND B.batID=BS.batID AND BS.speID=S.speID AND B.batID=BT.batID AND BT.tipID=T.tipID QUESTION: Is there a better way of writing this join ? Now I want to make a list of batteries which have caracteristics: * Special: speA * Tipical: tipA or tipB Además I want to sort the result such that if a battery has both tipA _and_ tipB, it comes first. I am thinking of doing it with temporary tables: tmp_spe: * batID (primary key) * count tmp_tip: * batID (primary key) * count INSERT into tmp_spe SELECT batID, count(speID) FROM Bat_Spe WHERE (speID='speA') GROUP BY batID QUESTION: Is there any difference between * SELECT batID, count(speID) * SELECT batID, count(batID) * SELECT batID, count(*) ? In this case, the three work and return the same result. INSERT into tmp_tip SELECT batID, count(tipID) FROM Bat_Tip WHERE (tipID='tipA' OR tipID='tipB') GROUP BY batID SELECT B.batID, (TS.count+TT.count) FROM Battery B, tmp_spe TS, tmp_tip TT WHERE B.batID=TS.batID AND B.batID=TT.batID ORDER BY 2 DESC This returns all the batteries which have all the characteristics I want, ordered as I want. QUESTION: Well, how does it sound ? Is there a better way to do it ? QUESTION: Now, I would need to get, for each battery matched, a list of all the characteristics it has. That is, if battery with batID='bat1' has tipical applications 'tipA', 'tipB' and 'tipC', can I retrieve in one shot all the batteries matched, together with the characteristics each one has, _or_ do I have to go through more temporary tables, _or_ is it better to make one query for each battery I have Well, I know that's a bit long, but it would greatly help to orientate me on how to continue... Thanks in advance for any help or hint, Olivier
Re: Symbolic Links under Windows
After following a promising lead, I'm at a dead end again. 1. Downloaded and installed binary distribution for MySQL 3.23.38 as recommended (originally tested with 3.23.37). 2. Installed mysqld-max-nt.exe service under Windows NT 4.0 SP 4. 3. perl script successfully executed: $dbh = DBI-connect(DBI:mysql:mysql:localhost); $sth = $dbh-prepare(SELECT * FROM db); $sth-execute; $row = $sth-fetchrow_hashref; A row was returned showing fields and values, so the service is running and databases can be accessed. 4. Created text file c:\mysql\data\mydb.sym with the string: c:\inetpub\wwwroot\mydb 5. Moved the c:\mysql\data\mydb tree to c:\inetpub\wwwroot\mydb. 6. Stopped and restarted MySQL service just in case. 7. perl script tried to execute: $dbh = DBI-connect(DBI:mysql:mydb:localhost); 8. Error reported by CGI script: Error 1049 Text: Unknown database 'mydb' I tried all manner of /, \\ in mydb.sym, extra blank lines. I also tried using mysqladmin to create the database after the mydb.sym file was created. It still created the mydb directory under c:\mysql\data. Can't make it work nohow. Any suggestions? Thanks Tom
Re: Remote access to MySQL with MySQLGUI on Win32
Nicolas Verhaeghe writes: I am trying to see if I can communicate with a MySQL on Free BSD from the MySQL GUI client on my Windows NT. The GUI client cannot connect because the MySQL seems to be refusing this connection. This does not seem to be server-related, but related to MySQL itself, since, when I try to telnet in port 3306 on that server, I got the error message: Host my FQDN is not allowed to connect to this MySQL server Do I have to modify something in the configuration file in that MySQL? And where is that file on the FreeBSD install? Thanks a lot if you can help! Hi! The above error has nothing to do with mysqlgui. The above error means that you have not set up privileges so that your user can connect from the computer on which mysqlgui runs. Read the manual on the MySQL access system. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - 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 with MySQL and Poweredge 6450..
Cody Swanson writes: Hey, We just got a new Dell Poweredge 6450 preloaded with redhat 6.2. Our intention is to move the database off a smaller machine and load it into this one. We currently have MySQL 3.23.38 using the innobase driver on redhat 6.2 with kernel 2.4.4. This is on a dual PIII650 with 1 gig of ram and it runs fine. Here is a copy of a status readout in mysql: cut We have tried 2.2.x kernels and all kinds of memory configs but we are at a loss for what to do. It seems to be a machine problem because the same data on a different machine works fine. If anyone has any ideas I would love it! Thanks. Hi! Please first check a hardware, before we try to isolate a problem. This could be a bug in InnoDB handler, but if all is working well on other machines it could be machine specific. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - 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: mysqld got signal 11;
Steve writes: Hi, I am running mysql 3.23.33 getting this error about once a day causeing mysql to restart Thanks and Regards, Steve Reed Network Administrator Oznetwork Pty. Ltd. 8233 1900 142 Phillip St Sydney NSW 2000 Hi! Please can you check whether your hyperseek_links table is corrupted or not ?? If it is not corrupted and the above query crashes again MySQL, please compress your table and upload it to ftp://support.mysql.com:/pub/mysql/secret. Just let us know a file name. Regards, Sinisa __ _ _ ___ == MySQL AB /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| /*/^^^\*\^^^ /*/ \*\Developers Team - 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
Trouble with Cyrilic
Hello, my name is Dimiter Atanasov. I'm newbie in mysql servers. I have a strange problem, when i try to select anythik written on Bulgarian (windows-1251) I recieve a unexpected results, some letters are unacceptable lice a (BG) or 'n' and 'm' and the server miss them. Do you have any ideas how to overcome the problem (mysql - unknown-freebsdelf4) - ver of my mysql - 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 with MySQL and Poweredge 6450..
Cody, did you copy the InnoDB data files, log files, and .frm files verbatim to the new computer? Have you tried wuith the same my.cnf parameters you had in the old computer? Does InnoDB crash always in the same function call? It looks like the internal data dictionary of InnoDB is corrupted. InnoDB tries to load the table definition and crashes while it is adding a column definition for the table. But if it works on another machine, that is strange. On one of our own machines where we have Red Hat 6.2 and software RAID, we have seen sporadic crashes, and sporadic failure of disk i/o. First, it would be useful to know what table it is. If you try simple queries (like fetching one row) from each of the tables in your database, for what table does it crash? What is the create table definition for that table? Have you dropped or altered the table? Regards, Heikki Tuuri Innobase Oy At 02:05 PM 5/22/01 +0300, you wrote: Content-Type: message/rfc822 Content-Description: forwarded message Content-Transfer-Encoding: 7bit Subject: Problems with MySQL and Poweredge 6450.. From: Cody Swanson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Date: Mon, 21 May 2001 16:49:43 -0300 (ADT) Hey, We just got a new Dell Poweredge 6450 preloaded with redhat 6.2. Our intention is to move the database off a smaller machine and load it into this one. We currently have MySQL 3.23.38 using the innobase driver on redhat 6.2 with kernel 2.4.4. This is on a dual PIII650 with 1 gig of ram and it runs fine. Here is a copy of a status readout in mysql: mysql Ver 11.15 Distrib 3.23.38, for pc-linux-gnu (i686) Connection id: 232107 Current database: Current user: root@localhost Current pager: stdout Using outfile: '' Server version:3.23.38 Protocol version: 10 Connection:Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime:1 day 19 hours 23 min 39 sec Threads: 55 Questions: 70609984 Slow queries: 15 Opens: 203 Flush tables: 1 Open tables: 155 Queries per second avg: 451.994 As you can see we have quite a load on this poor machine, this is why we wanted to move to a bigger badder machine so we could push more out of MySQL. Anyway, as mentioned before, our machine is a Poweredge 6450 with 4 Xeon 750mhz processors and 4gb of ram. We also have a dell 4 channel PercRaid card that does a raid 5 onto 4 36gb scsi disks. So we loaded Mysql 3.23.38 onto the machine with no extra tweeks, just with the raw install from dell, setup the Innobase driver and imported our data. Once we moved the site onto the new server everything worked great, we could do super heavy queries in no time, it was great! Then a day later I was looking at the .err log and noticed that MySQL had crashed several times! Each time it crashes it restarts itself and the innobase tables fix themselves. Innobase is verry cool! But this is a real problem! So I took the .err file and parsed the stack trace against the symbol file for mysql like the manual says. Here is a copy of the .err file with the stack dump from one crash. This one is from kernel 2.4.3: mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died. 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... Stack range sanity check OK, backtrace follows: 0x81c508a 0x80f4ca5 0x80f44d4 0x80f823e 0x80f4021 0x80caee6 0x80c4a2d 0x809bdbf 0x8097de1 0x8097522 0x8098177 0x80abede 0x8084967 0x80877fd 0x8082ba9 0x80820d6 Stack trace successful, trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0xb91044a8 is invalid pointer thd-thread_id = 6230 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 6230 did to cause the crash. In some cases of really bad corruption, this value may be invalid Please use the information above to create a repeatable test case for the crash, and send it to [EMAIL PROTECTED] Number of processes running now: 0 010516 15:55:52 mysqld restarted InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 2512947858 InnoDB: Doing recovery: scanned up to log sequence number 0 2512963 Here is the results of running stackresolve: [root@cobalt /tmp]# /usr/local/mysql/bin/resolve_stack_dump -s /tmp/mysqld.sym -n /tmp/mysql.stack 0x81c508a pthread_sighandler + 150 0x80f4ca5 dict_col_add_to_cache + 153 0x80f44d4 dict_table_add_to_cache + 1128 0x80f823e dict_load_table + 3326 0x80f4021 dict_table_get + 169 0x80caee6 open__11ha_innobasePCciUi + 310
Re: Trouble with Cyrilic
On Tue, May 22, 2001 at 02:41:44PM -0400, Dimiter Atanasov wrote: Hello, my name is Dimiter Atanasov. I'm newbie in mysql servers. I have a strange problem, when i try to select anythik written on Bulgarian (windows-1251) I recieve a unexpected results, some letters are unacceptable lice a (BG) or 'n' and 'm' and the server miss them. Do you have any ideas how to overcome the problem (mysql - unknown-freebsdelf4) - ver of my mysql Are you using the appropriate charset? The easiest way to configure MySQL to use Win-1251 is to compile it from the databases/mysql323-server port, using the following command: make WITH_CHARSET=cp1251 WITH_XCHARSET=all fetch clean all install If you have installed MySQL as a binary package, there might be a way to change the currently used character set, though I'm not really sure how to do that. The MySQL manual mentions SET OPTION CHARACTER SET, but I've never done it that way - I've always compiled MySQL from source, and specified the correct character set to use at build time for both the server and the client. G'luck, Peter -- This sentence claims to be an Epimenides paradox, but it is lying. - 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:How can be minimized downtime on MyISAM-InnoDB transition?
Ilya, ALTER TABLE should be faster: InnoDB does not have special optimization for separate index creation. Therefore the fastest way is to do the inserts directly to an InnoDB table. You should set the InnoDB buffer pool size big during the transition, to reduce disk i/o. Not bigger than 80 % of the physical memory, though. You should set InnoDB log files big, as described in the manual at http://www.innobase.fi, and also the log buffer to 8 MB, for example. Make sure you do not run out of tablespace: InnoDB tables take a lot more space than MyISAM tables. If an ALTER TABLE runs out of space, it will start a rollback, and that can take hours if it is disk bound. In that case it is better that you kill the database process and delete all InnoDB files and all InnoDB table .frm files, and start your job again, rather than wait millions of disk i/os to complete. Regards, Heikki Tuuri Innobase Oy Hi, We recently decided to start switching on InnoDB for some our web projects. We have tested InnoDB on test machines and have been satisifed with results. Now the question: how can we minimize downtime on production server during transition? We see two choises: 1) change mysql server to single-user mode and change types of tables with 'alter table TABLE name=InnoDB', start mysql server to normal mode again 2) change mysql server to single-user mode and dump tables with mysqldump --quick, create new tables of InnoDB type without indexes, restore their content from dump, restore indexes, start mysql server to normal mode again. We can afford several hours of downtime so we don't want to bother with too complex solutions for this transitions like setting up replication server and so on. Some info on tables: most of them are quite small except one which has about 1,800,000 records (size of MyISAM files about 70Mb). -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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: How can be minimized downtime on MyISAM-InnoDB transition?
Ilya, actually, it may be safest that you use CREATE TABLE to create an identical InnoDB table, and then do INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey something AND yourkey somethingelse; In this way you can divide the insertion into pieces, and you have better control on what is happening. After all data is inserted, you can rename tables. Regard, Heikki At 04:22 PM 5/22/01 +0400, you wrote: Thanks for info. HT Ilya, HT ALTER TABLE should be faster: InnoDB does not have special optimization HT for separate index creation. Therefore the fastest way is to do the inserts HT directly to an InnoDB table. HT You should set the InnoDB buffer pool size big during the HT transition, to reduce disk i/o. Not bigger than 80 % of the physical HT memory, though. You should set InnoDB log files big, as described in the HT manual at http://www.innobase.fi, and also the log buffer to 8 MB, HT for example. HT Make sure you do not run out of tablespace: InnoDB tables take a lot HT more space than MyISAM tables. If an ALTER TABLE runs out of space, HT it will start a rollback, and that can take hours if it is disk bound. HT In that case it is better that you kill the database process and HT delete all InnoDB files and all InnoDB table .frm files, and start HT your job again, rather than wait millions of disk i/os to complete. HT Regards, HT Heikki Tuuri HT Innobase Oy -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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: How can be minimized downtime on MyISAM-InnoDB transition?
Thanks for idea again :) HT Ilya, HT actually, it may be safest that you use CREATE TABLE to create HT an identical InnoDB table, and then do HT INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey something HT AND yourkey somethingelse; HT In this way you can divide the insertion into pieces, and you have HT better control on what is happening. After all data is inserted, HT you can rename tables. HT Regard, HT Heikki HT At 04:22 PM 5/22/01 +0400, you wrote: Thanks for info. HT Ilya, HT ALTER TABLE should be faster: InnoDB does not have special optimization HT for separate index creation. Therefore the fastest way is to do the inserts HT directly to an InnoDB table. HT You should set the InnoDB buffer pool size big during the HT transition, to reduce disk i/o. Not bigger than 80 % of the physical HT memory, though. You should set InnoDB log files big, as described in the HT manual at http://www.innobase.fi, and also the log buffer to 8 MB, HT for example. HT Make sure you do not run out of tablespace: InnoDB tables take a lot HT more space than MyISAM tables. If an ALTER TABLE runs out of space, HT it will start a rollback, and that can take hours if it is disk bound. HT In that case it is better that you kill the database process and HT delete all InnoDB files and all InnoDB table .frm files, and start HT your job again, rather than wait millions of disk i/os to complete. HT Regards, HT Heikki Tuuri HT Innobase Oy -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.com/) | -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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: myisampack
No pack is like zipping some thing up. It makes it small but it still needs to be unzipped to retrieve. There is in real life no advantage to disadvatege except size of DB. Hope this help Simon -Original Message- From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]] Sent: 22 May 2001 12:54 To: [EMAIL PROTECTED] Subject: myisampack If I have a lot of rows with text that never changes. Only more rows are added. Should/Can I then use myisampack ? Will it make my FULLTEXT search faster ? Will other database SELECTs become faster ? For now I do not care about disk space. Best regards Jacob - 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
request
I'd like to know how many similtanous request support a MySql database . thanks for answering - 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
Securely access a mysql server over the net
I have found an isp that will leave port 3306 open for me to talk to the mysql server, my question is Is anything sent over this connection secure, if not is there some way of encrypting it such as ssl?
OPTIMIZATION
Please give me advice on optimizing my server: Here are the system specs dual 733 Pentium 512 megs of ram 9 gig scsi -- Here is the options that I use based on the documentation for a moderate # of users: -O max_connections=1000 -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O record_buffer=1M - How can I improve on these options for a higher number of users than 1000. Is 5000 connections to high for mysql? How can I improve the above options since I have a good server? any Advice is appreciated Last question ... Has anybody taken the mysql classes in America? if so let us know how your experience was taking it, if you learned anything and if it was worth the money. Thanks ' Randy - 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: Securely access a mysql server over the net
Cyber Monkey wrote: I have found an isp that will leave port 3306 open for me to talk to the mysql server, my question is Is anything sent over this connection secure, if not is there some way of encrypting it such as ssl? Yes.. you ssh like here www.ssh.com www.openssh.org and do not communicate with the server over port 3306 but over a tunneled connection via ssh. - 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
Group By Question
I have a query that is using Group By Title. Is there anyway to make 2 Titles such as League of their own and League of their own### group as the same. I am removing the #'s in the output but I still get 2 results - 1 for each. Ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JOIN optimization
Hi, I'm rather new to SQL (well, I had a one-year course at the university many years ago, but they didn't teach us anything practical). I did a few very simple data bases, but now I'm moving to something more serious with joins and 'group by'. The idea is to create a search engine for a battery distributor, specifying various characteristics like * who makes it (only one per battery) * special characteristics (various) * tipical applications (various) To simplify, I have the following tables: Battery: * batID (primary key) * makID * descr Maker: * makID (primary key) * mak A battery can have only one manufacturer, so you might as well add the mak column to Battery and remove Maker. Special: * speID (primary key) * special Bat_Spe: * batID * speID (both in primary key) This is a join table. It creates a many-to-many join between Battery and Special. Tipical: Do you mean Type? * tipID (primary key) * tipical Bat_Tip: * batID * tipID (primary key) From the SELECT statement immediately below, it appears that this is a join table. If that is the case, then Bat_Tip.tipID should not be a primary key. If Bat_Tip.tipID is a key, then you don't need the Bat_Tip join table. * *** First I want to make a list of batteries with all the carateristics they have: SELECT M.mak, B.descr, S.special, T.tipical FROM Battery B, Maker M, Special S, Bat_Spe BS, Tipical T, Bat_Tip BT WHERE M.makID=B.makID AND B.batID=BS.batID AND BS.speID=S.speID AND B.batID=BT.batID AND BT.tipID=T.tipID QUESTION: Is there a better way of writing this join ? Given your current schema, no. * *** Now I want to make a list of batteries which have caracteristics: * Special: speA * Tipical: tipA or tipB Además I want to sort the result such that if a battery has both tipA _and_ tipB, it comes first. I am thinking of doing it with temporary tables: tmp_spe: * batID (primary key) * count tmp_tip: * batID (primary key) * count INSERT into tmp_spe SELECT batID, count(speID) FROM Bat_Spe WHERE (speID='speA') GROUP BY batID QUESTION: Is there any difference between * SELECT batID, count(speID) * SELECT batID, count(batID) * SELECT batID, count(*) ? In this case, the three work and return the same result. In the future, if you decide to change the WHERE clause to search for two or more speIDs (e.g. WHERE speID = 'speA' OR speID = 'speB'), then the counts will be different. Decide what you actually want to count; speIDs, batteries, or rows. Even if the WHERE clause doesn't change, what you count gives you a clue as to what the statement does. Suppose you want to count rows and you use count(batID). Six months from now when you've forgotten how this works, the use of count(batID) will make it more difficult to figure out that you are counting rows. INSERT into tmp_tip SELECT batID, count(tipID) FROM Bat_Tip WHERE (tipID='tipA' OR tipID='tipB') GROUP BY batID SELECT B.batID, (TS.count+TT.count) FROM Battery B, tmp_spe TS, tmp_tip TT WHERE B.batID=TS.batID AND B.batID=TT.batID ORDER BY 2 DESC I believe that's a fluke. Since you are ordering by (TS.count + TT.count), a battery with speA and tipA can appear ahead of a battery with tipA and tipB. In order to avoid this, use ORDER BY TT.count DESC You can combine the two ORDER BY conditions to order within TT.count. ORDER BY 2 DESC, TT.count DESC This returns all the batteries which have all the characteristics I want, ordered as I want. QUESTION: Well, how does it sound ? Is there a better way to do it ? Your method looks good to me. Pretty impressive for someone who studied SQL many years ago for a period of one year. QUESTION: Now, I would need to get, for each battery matched, a list of all the characteristics it has. That is, if battery with batID='bat1' has tipical applications 'tipA', 'tipB' and 'tipC', can I retrieve in one shot all the batteries matched, together with the characteristics each one has, _or_ do I have to go through more temporary tables, _or_ is it better to make one query for each battery I have Join the last statement above to the Bat_Tip and Tipical tables. SELECT B.batID, T.tipical FROM Battery B, tmp_spe TS, tmp_tip TT, Bat_Tip BT, Tipical T WHERE B.batID=TS.batID AND B.batID=TT.batID AND B.batID = BT.batID AND BT.tipID = T.tipID You can join to the Special table in the same way. Well, I know that's a bit long, but it would greatly help to orientate me on how to continue... Thanks in advance for any help or hint, Olivier All SQL was processed in the MySQL server in my brain, not the one on my computer. Expect syntax errors. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak MySQL list magic words: sql query database - Before posting,
Installing in Solaris8 problem
Hi all, i have had several problems trying to install mysql on a Ultra60 sparc with Solaris8, i get the following messages running the configure script. I dont have any idea of why is this happenning, probably some related with 64 bits OS or something. Anyone have a clue ??? thanx friends. ./configure --prefix=/vol03/mysql-3.23.38 --with-berkeley-db loading cache ./config.cache checking host system type... sparc-sun-solaris2.8 checking target system type... sparc-sun-solaris2.8 checking build system type... sparc-sun-solaris2.8 checking for a BSD compatible install... ./install-sh -c checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for working aclocal... found checking for working autoconf... found checking for working automake... found checking for working autoheader... found checking for working makeinfo... missing checking whether to enable maintainer-specific portions of Makefiles... no checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for gawk... (cached) nawk checking for gcc... (cached) gcc checking whether the C compiler (gcc ) works... yes checking whether the C compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C... (cached) yes checking whether gcc accepts -g... (cached) yes checking for c++... (cached) c++ checking whether the C++ compiler (c++ ) works... yes checking whether the C++ compiler (c++ ) is a cross-compiler... yes checking whether we are using GNU C++... (cached) yes checking whether c++ accepts -g... (cached) yes checking how to run the C preprocessor... (cached) gcc -E checking for ranlib... (cached) ranlib checking for ld used by GCC... (cached) /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... (cached) no checking for BSD-compatible nm... (cached) /usr/ccs/bin/nm -p checking whether ln -s works... (cached) yes checking for object suffix... o checking for executable suffix... no checking for gcc option to produce PIC... -fPIC checking if gcc PIC flag -fPIC works... yes checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.lo... yes checking if gcc supports -fno-rtti -fno-exceptions ... yes checking if gcc static flag -static works... -static checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking whether the linker (/usr/ccs/bin/ld) supports shared libraries... yes checking command to parse /usr/ccs/bin/nm -p output... ok checking how to hardcode library paths into programs... immediate checking for /usr/ccs/bin/ld option to reload object files... -r checking dynamic linker characteristics... solaris2.8 ld.so checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for objdir... .libs creating libtool loading cache ./config.cache checking for a BSD compatible install... ./install-sh -c checking for bison... no checking for byacc... no checking for pdftex... no checking return type of sprintf... configure: error: can not run test program while cross compiling _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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
InnoDB tuning tips now in the manual at www.innobase.fi
Hi! I wrote a short new section 9 to the InnoDB manual at http://www.innobase.fi It is a collection of performance tuning tips most of which I have also given on this mailing list. A new section 4.1 talks about MyISAM - InnoDB table conversion. Regards, Heikki Tuuri Innobase Oy (Mail filter bait: SQL database) - 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
Average of all NON-NULL columns in a ROW?
Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? Thanks! Graeme - 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: UNIX_TIMESTAMP converts to CURRENT_DATE on a second call
Sinisa writes: - timestamp is saved in datetime format - first timestamp column is silently updated on each UPDATE What this means, of course, is that when you want to deal with your own TIMESTAMP data, you need to maintain some sort of sacrificial column (e.g. LAST_UPDATED TIMESTAMP) that can be subjected to this automatic update (and must be the first TIMESTAMP column in your table!), so that MySQL leaves your other real TIMESTAMP columns alone. Though really, this behavior should be an *option* (preferably a *per-table* option) in MySQL, not an automatic feature (i.e. assuming that any TIMESTAMP column must be a last-updated type column data). TIMESTAMP is an ANSI type with its own defined semantics, and it's perfectly valid to expect that the user may want to maintain their own timestamp data without MySQL making any assumptions about it.. -- Shankar.
Can/Does Mysql Broadcast to IPs?
Greetings: Actually what I'm asking is what will happen if 50 users all in the same subnet (A.B.C.*) all request the same huge row from the database? Are there any optimizations for this? Thank you - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Average of all NON-NULL columns in a ROW?
On Tue, May 22, 2001 at 12:29:35PM -0400, Graeme B. Davis wrote: Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? SELECT SUM(field) / COUNT(field) FROM table WHERE othercondition AND (field IS NOT NULL) G'luck, Peter -- I am the meaning of this sentence. - 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
Ugly output
I'm running the following: select if(substring(username,1,8)='cctcpin-', lcase(trim(leading concat(substring_index(username,'-',1), '-') from username)), lcase(username)), sec_to_time(sum(acctsessiontime)) as session from radacct where acctstarttime between '2001-04-21' and '2001-05-21' and acctstoptime group by username order by username; which should return distinct usernames and their total monthly usage. The data comes out correct, it just looks like this: | wilk | 23:42:53 | | will | 19:51:16 | | willyt | 51:17:38 | | wmoore | 33:05:05 | | wmp | 13:44:10 | | xlazyk | 13:25:27 | | ybarra | 39:02:06 | | zanew | 02:57:12 | | zboswell | 44:38:06 | Any ideas where the spaces or line return comes from? It is not stored in the table, as this looks fine: mysql select distinct(username) from radacct where username like 'cctc%' order by username limit 5; +--+ | username | +--+ | cctcpin-4707 | | cctcpin-abennett | | cctcpin-abetribe | | cctcpin-ace4krn | | cctcpin-acres| +--+ 5 rows in set (1.62 sec) And to the text-based folks, my apologies for the ugly line wraps. It looks that bad in my pine session also. Thanks, -- === Kip Turkphone: 915.234.5678 Systems Administrator or 800.695.9016 Killer of Spam/Writer of Code/Penguin Proponent West Central Net fax: 915.656.0071 === - 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: How to retrieve record-id after 'insert into'?
In order to retrieve the id you inserted and not someones's id that was inserted a bit after yours, I guess you should lock the table before your insert statement, do the insert, retrieve the id just inserted, and then unlock the table so others can operate with the table again. This is my guess... Anybody have another way??? Siomara From: Peter Pentchev [EMAIL PROTECTED] To: Viktor van den Berg [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to retrieve record-id after 'insert into'? Date: Mon, 21 May 2001 17:21:44 +0300 On Mon, May 21, 2001 at 03:58:10PM +0200, Viktor van den Berg wrote: Hi! I like to know how to retrieve the record id after an insert into query. I have the table user containing two columns: UID (auto_increment) and USERNAME. After I have added a new user (using insert into user (uid) etc.) I like to know the UID of the new record. I can set the record pointer to the last record, but if anybody else also inserts a record at the same time, I think this will give a problem. Can anybody give me a suggestion? Look at the MySQL manual. Reference INSERT, look for mentioning of AUTO_INCREMENT fields, and specifically, for a reference to the LAST_INSERT_ID() function. Simple, isn't it? :) All referenced in the manual, if you take the time to check what the manual has to say about 'INSERT'.. :) G'luck, Peter -- This inert sentence is my body, but my soul is alive, dancing in the sparks of your brain. - 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 _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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
sql manual
is there any sql manaual over the internet0' no matter that is not a mysql specificly, I would like to have a good manual for beguiners, cheers - 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: Average of all NON-NULL columns in a ROW?
Select avg(id1) from tableName where id1 is not null; ? - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 11:29 AM Subject: Average of all NON-NULL columns in a ROW? Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? Thanks! Graeme - 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: How to retrieve record-id after 'insert into'?
Did you take a look at the LAST_INSERT_ID() function I mentioned? G'luck, Peter -- This sentence no verb. On Tue, May 22, 2001 at 01:53:24PM -0300, Siomara Pantarotto wrote: In order to retrieve the id you inserted and not someones's id that was inserted a bit after yours, I guess you should lock the table before your insert statement, do the insert, retrieve the id just inserted, and then unlock the table so others can operate with the table again. This is my guess... Anybody have another way??? Siomara From: Peter Pentchev [EMAIL PROTECTED] To: Viktor van den Berg [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: How to retrieve record-id after 'insert into'? Date: Mon, 21 May 2001 17:21:44 +0300 On Mon, May 21, 2001 at 03:58:10PM +0200, Viktor van den Berg wrote: Hi! I like to know how to retrieve the record id after an insert into query. I have the table user containing two columns: UID (auto_increment) and USERNAME. After I have added a new user (using insert into user (uid) etc.) I like to know the UID of the new record. I can set the record pointer to the last record, but if anybody else also inserts a record at the same time, I think this will give a problem. Can anybody give me a suggestion? Look at the MySQL manual. Reference INSERT, look for mentioning of AUTO_INCREMENT fields, and specifically, for a reference to the LAST_INSERT_ID() function. Simple, isn't it? :) All referenced in the manual, if you take the time to check what the manual has to say about 'INSERT'.. :) - 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: UNIX_TIMESTAMP converts to CURRENT_DATE on a second call
Shankar Unni wrote: Sinisa writes: - timestamp is saved in datetime format - first timestamp column is silently updated on each UPDATE What this means, of course, is that when you want to deal with your own TIMESTAMP data, you need to maintain some sort of sacrificial column (e.g. LAST_UPDATED TIMESTAMP) that can be subjected to this automatic update (and must be the first TIMESTAMP column in your table!), so that MySQL leaves your other real TIMESTAMP columns alone. Though really, this behavior should be an *option* (preferably a *per-table* option) in MySQL, not an automatic feature (i.e. assuming that any TIMESTAMP column must be a last-updated type column data). TIMESTAMP is an ANSI type with its own defined semantics, and it's perfectly valid to expect that the user may want to maintain their own timestamp data without MySQL making any assumptions about it.. -- Shankar. What this means is you should be using a DATETIME field instead of a timestamp. -- Gerald L. Clark [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
bug report
Hello, I think me and my fellows here at office, found a bug in mysql. The system is: RedHat 7.0, kernel 2.2.16, on Celeron 700MHz mysql version 3.23.22 Here is the script: ( cat EOF connect test; create table test (i numeric(4), j numeric(4)); insert into test (i,j) values (1, 1); insert into test (i,j) values (1, 2); insert into test (i,j) values (1, 3); insert into test (i,j) values (2, 1); insert into test (i,j) values (2, 2); select * from test; select i, min(j), max(j) from test group by i; EOF ) | mysql -p and here is the result: i j 1 1 1 2 1 3 2 1 2 2 i min(j) max(j) 1 0 3 2 0 2 the interesting part is that if I replace create table test (i numeric(4), j numeric(4)) with create table test (i integer, j integer) , then the result is ok We have tested this script on another machine with Pentium 100MHz and RH7.0, and the result is the same. I hope this bug report will be usefull. Looking forward for your reply, Adrian Tarog System Engineer SC JOLIDON SRL - 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: Average of all NON-NULL columns in a ROW?
Ahh.. that, too :) G'luck, Peter -- If I were you, who would be reading this sentence? On Tue, May 22, 2001 at 12:04:34PM -0500, Cal Evans wrote: Select avg(id1) from tableName where id1 is not null; ? - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 11:29 AM Subject: Average of all NON-NULL columns in a ROW? Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? - 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: JOIN optimization
Hi Bob, Many thanks for your answer, I was afraid that due to the length of the mail, it would scare everybody... Battery: * batID (primary key) * makID * descr Maker: * makID (primary key) * mak A battery can have only one manufacturer, so you might as well add the mak column to Battery and remove Maker. Well, the idea was not repeating the name of the maker, just have its ID. Special: * speID (primary key) * special Bat_Spe: * batID * speID (both in primary key) Tipical: Do you mean Type? Well, in fact I mean Typical (typical application of the battery) * tipID (primary key) * tipical Bat_Tip: * batID * tipID (primary key) In fact it is like for join table Bat_Spe: * tipID (both in primary key) QUESTION: Is there any difference between * SELECT batID, count(speID) * SELECT batID, count(batID) * SELECT batID, count(*) ? In this case, the three work and return the same result. In the future, if you decide to change the WHERE clause to search for two or more speIDs (e.g. WHERE speID = 'speA' OR speID = 'speB'), then the counts will be different. Decide what you actually want to count; speIDs, batteries, or rows. Even if the WHERE clause doesn't change, what you count gives you a clue as to what the statement does. Suppose you want to count rows and you use count(batID). Six months from now when you've forgotten how this works, the use of count(batID) will make it more difficult to figure out that you are counting rows. I think I was confused because each couple (speID, batID) is unique. That's why the 3 forms are equivalent here. For the last question, (list of characteristics for each battery matched) I decided to make a query for each class of characteristics ('spe', 'tip') since I wanted to do some additional treatments. Thanks again Bob, I feel more confident now. Olivier - 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: mysqld got signal 11;
On Monday 21 May 2001 23:29, Steve wrote: Hi, I am running mysql 3.23.33 getting this error about once a day causeing mysql to restart mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attemping backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong stack range sanity check, ok, backtrace follows 0x400737c6 0x80ff2af 0x80ff1e1 0x8101227 0x80e4873 0x80e45b8 0x80dd605 0x80c3265 0x80c72bf 0x80c2675 0x80c1cde stack trace successful, now will try to get some variables. Some pointers may be invalid and cause dump abort thd-query at 0x81f9bf0 = SELECT * FROM hyperseek_links WHERE ID in(988779528,988588937,988779473,988599648,988630588,988634546,988 600011,988613411,988613424,988605698,988614258,988614259,988613615,988619035 ,988616328,988596036,988599643,988613419,988635422,98862 2151,988627434,988613538,988627431,988622188,988613415,988621976,988592381,9 88636427,988613413,988631044,988623929,988614599,9886231 05,988635148,988595545,988597946,988610251,988616059,988627430,988608473,988 598139,988627429,988611026,988598120,988625124,988634377 ,988627427,988604205,988629277,988631289,988612742,988592490,988635848,98862 8639,988593015,988611041,988589630,988593784,988593696,9 88590456,988590457,988610004,988609853,988609428,988628676,988625092,9885967 10,988614174,988611930,988628208,988628684,988589448,988 634777,988631086,988627424,988589395,988629406,988593093,988627423,988605901 ,988619264,988589911,988624763,988610330,988588752,98863 3204,988605807,988627421,988613695,988626494,988613013,988606490,988600691,9 88627420,988628061,988614780,988592519,988594515,9 thd-thread_id = 7951 successfully dumped variables, if you ran with --log take a look at the details of what thread 7951 did to cause the crash. In some cases of really bad corruption, this value can be invalid Please use the information above to create a repeatable test case for the crash, and send it to [EMAIL PROTECTED] Number of processes running now: 2 010521 19:14:19 mysqld restarted /usr/local/mysql/libexec/mysqld: ready for connections 010521 19:59:34 Aborted connection 1646 to db: 'search' user: 'search' host: `203.111.10.101' (Got an error reading communication p ackets) then ran (note this was on a previous error, I included the above error as it was the most informative in the log file): root@netmsc1:/tmp# resolve_stack_dump -s /tmp/mysqld.sym -n mysql.stack Cannot determine thread, ebp=0xb14c, backtrace may not be correct stack range sanity check, ok, backtrace follows 0x400737c6 _end + 938024678 0x40072d66 _end + 938022022 0x80be19a create_new_thread__FP3THD + 606 0x80be61b handle_connections_sockets__FPv + 1015 0x80bdd90 main + 2412 0x400f5577 _end + 938556567 0x808891d _start + 33 New value of ebp failed sanity check terminating backtrace First, you seem to be resolving a different stack trace than the one you have posted. Can you resolve the original one too? Can you try our 3.23.38 binary and see if it solves the problem? Your own source build is not going to be very stable as the number of connections increases unless you take some measures to fix up LinuxThreads - see http://www.mysql.com/doc/L/i/Linux.html . -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - 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
Problem in creating table structure
hello mysql This is bineet from osprey software technology from india actually i just installed and configure the MySql database instead of postgres in linux and i have a script which actaully create the table structure in the database it create the sequence and indexes too.In the sequence it actally increment the one of feild from one table But after installing and creating database in MySql i find that MySql doesnot support sequence so could pl tell me what would be the sloution for that is any autoincrement filed in MySql for this and could you pl send any document related to command and data types etc which actually MySql support. i am very new in MySql and you know it;s different from others. waiting for reply Bineet Suri(Osprey Software Technology) [EMAIL PROTECTED] [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL performance question
Joshua, Comparing Oracle vs. MySQL myisam tables, MySQL will save disk space, and provide you with much faster queries, but you have to be aware of the table locking issues if you are doing updates/inserts/deletes mixed with reads. MySql documentation says that mysql enforces table level locking if no explicit locking is there.If I am using non transaction sensitive tables can I go ahead without providing any explicit locking?I am using myISAM. Regards, Ravi _ Joshua Chamas Chamas Enterprises Inc. NodeWorks free web link monitoring Huntington Beach, CA USA http://www.nodeworks.com 1-714-625-4051 - 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!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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: bug report
Can you try this with a more recent MySQL version? I cannot reproduce it here, on FreeBSD 4.3 running MySQL 3.23.38. G'luck, Peter -- This sentence contains exactly threee erors. On Tue, May 22, 2001 at 08:32:24PM +0300, Tarog Adrian wrote: Hello, I think me and my fellows here at office, found a bug in mysql. The system is: RedHat 7.0, kernel 2.2.16, on Celeron 700MHz mysql version 3.23.22 Here is the script: ( cat EOF connect test; create table test (i numeric(4), j numeric(4)); insert into test (i,j) values (1, 1); insert into test (i,j) values (1, 2); insert into test (i,j) values (1, 3); insert into test (i,j) values (2, 1); insert into test (i,j) values (2, 2); select * from test; select i, min(j), max(j) from test group by i; EOF ) | mysql -p and here is the result: i j 1 1 1 2 1 3 2 1 2 2 i min(j) max(j) 1 0 3 2 0 2 the interesting part is that if I replace create table test (i numeric(4), j numeric(4)) with create table test (i integer, j integer) , then the result is ok We have tested this script on another machine with Pentium 100MHz and RH7.0, and the result is the same. I hope this bug report will be usefull. Looking forward for your reply, - 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: bug report
That is pretty old. 3.23.38 works fine. Tarog Adrian wrote: Hello, I think me and my fellows here at office, found a bug in mysql. The system is: RedHat 7.0, kernel 2.2.16, on Celeron 700MHz mysql version 3.23.22 Here is the script: ( cat EOF connect test; create table test (i numeric(4), j numeric(4)); insert into test (i,j) values (1, 1); insert into test (i,j) values (1, 2); insert into test (i,j) values (1, 3); insert into test (i,j) values (2, 1); insert into test (i,j) values (2, 2); select * from test; select i, min(j), max(j) from test group by i; EOF ) | mysql -p and here is the result: i j 1 1 1 2 1 3 2 1 2 2 i min(j) max(j) 1 0 3 2 0 2 the interesting part is that if I replace create table test (i numeric(4), j numeric(4)) with create table test (i integer, j integer) , then the result is ok We have tested this script on another machine with Pentium 100MHz and RH7.0, and the result is the same. I hope this bug report will be usefull. Looking forward for your reply, Adrian Tarog System Engineer SC JOLIDON SRL - 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 -- Gerald L. Clark [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: MYSQL performance question
VVM Ravikumar Sarma Chengalvala wrote: MySql documentation says that mysql enforces table level locking if no explicit locking is there.If I am using non transaction sensitive tables can I go ahead without providing any explicit locking?I am using myISAM. Yes. This is how I use MySQL too. --Josh _ Joshua Chamas Chamas Enterprises Inc. NodeWorks free web link monitoring Huntington Beach, CA USA http://www.nodeworks.com1-714-625-4051 - 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
port default
Hi, what is the default port to listen to on MySQL? __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.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
mysqldump and locking
I have a database-driven web site that uses PHP and MySQL 3.23.38. The database is about 1 gigabyte in size. While performing a mysqldump --opt to back up the database, the web site is completely inaccessible. After doing some investigating, I believe that this is because of the --lock-tables option that is implied by the --opt argument. Every page of the web site updates a session table that is a heap table. It looks like the mysqlpdump is locking the heap table for read along with all of the other tables while the dump is occuring. Should heap tables really be locked during the mysqldump? Does anyone have any ideas for a way to back up the database without having the site be down while it happens? Cory. - 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: Can/Does Mysql Broadcast to IPs?
On Tue, May 22, 2001 at 12:39:02PM -0400, Jacob Martin wrote: Greetings: Actually what I'm asking is what will happen if 50 users all in the same subnet (A.B.C.*) all request the same huge row from the database? Are there any optimizations for this? Not that I'm aware of. MySQL treats each client separately from the others. You could probably build a middleware layer to do this sort of thing, though... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 2 days, processed 19,473,431 queries (78/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: port default
default port is 3306 Chad -Original Message- From: sj c [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 22, 2001 1:15 PM To: [EMAIL PROTECTED] Subject: port default Hi, what is the default port to listen to on MySQL? __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.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: count(*) on different tables
UNION is currently not supported, though one workaround is merge tables, it's not quite the same thing. As to your query SELECT COUNT(*) FROM table1,table2, it is returning the expected result. Without a WHERE clause, you are getting the cartesian product of both tables (all rows from table1 joined to all rows of table2). Ansgar Becker [EMAIL PROTECTED] wrote: is it possible to get *one* quick result with the rowcount of each table in one database, without knowing the column-names? this does *not* work: select count(t1.*), count(t2.*) from table1 t1, table2 t2 Select 'Table1' as tableName, count(*) as rowCount from table1 UNION Select 'Table2' as tableName, count(*) as rowCount from table2 ... etc Assuming that MySQL can use the UNION clause. (I haven't checked) I think MySQL actually doesn't support union. ;/ I wonder if this is so difficult.. Only getting rowcounts of different tables in one result. I tried also the folliowing: select count(*) from table1, table2. But this (curiously) returns table1rowcount * table2rowcount Greetings, Ansgar - 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: Problem in creating table structure
On Tue, May 22, 2001 at 10:36:02AM -0700, bineet suri wrote: hello mysql This is bineet from osprey software technology from india actually i just installed and configure the MySql database instead of postgres in linux and i have a script which actaully create the table structure in the database it create the sequence and indexes too.In the sequence it actally increment the one of feild from one table But after installing and creating database in MySql i find that MySql doesnot support sequence so could pl tell me what would be the sloution for that is any autoincrement filed in MySql for this and could you pl send any document related to command and data types etc which actually MySql support. i am very new in MySql and you know it;s different from others. [snip] Before posting, please check: http://www.mysql.com/manual.php (the manual) Look at the link above, specifically at 7.3. Column Types and 7.7. CREATE TABLE syntax. In the latter, take a look at something you described yourself - AUTO_INCREMENT. G'luck, Peter -- This sentence every third, but it still comprehensible. - 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 with sum() in a query.
I'm gaving a problem with adding several sums together when using a group by clause. The table I have looks like this: school_id int school_name varchar sport varchar cost_a float(9,2) cost_b float(9,2) cost_c float(9,2) The real table is normalized, figured it would make for a simpler example this way, so you don't have to tell me ;) Each school has records for several diffrent sports I'm trying to query for the total amount spent for each school with the following query. SELECT school_name, (sum(cost_a)+sum(cost_b)+sum(cost_c)) as total FROM schools GROUP BY school With the desired result school_1 125000.00 school_2 234642.12 school_3 98433.45 But I get zero's for the total column. If I query for only one school I get the correct numbers: SELECT school, (sum(cost_a)+sum(cost_b)+sum(cost_c)) as total FROM schools WHERE school='myschool' GROUP BY school If I query for a few schools (school_id3) sometimes I get unusual numbers, 17.24 for one example. I'm not sure what's happening with these queries so I have to ask. Am I doing the query wrong? Is SQL or mySQL not able to handle this query? Or did I find a bug in mySQL? -- Jeff Bearer, RHCE Webmaster PittsburghLIVE.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: port default
On Tue, 22 May 2001 11:15:24 -0700 (PDT), sj c [EMAIL PROTECTED] wrote: Hi, what is the default port to listen to on MySQL? I believe it's 3306. Mike(mickalo)Blezien Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225) 686-2002 = - 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
Sun-Solaris 2.6 binary
Hi, We are running on a SunOS 5.6 Generic_105181-25 sun4u sparc SUNW,Ultra-5_10 platform. I noticed that there is not a binary file for 2.6. Is there anyway way for you guys to generate one for us. We are a company of about 200 (?) developing GSM network software. I'm evaluating MySQL. I used the mySQL version 3.21 that I found you had a binary for the OS we are currently using. That version lacked too many features for us to use. It seems that the current version, 3.23, has most of the functionality we're looking for. Yes, I do realize that I can build from the source files. But out gnu compiler is an older version. And there is a lot of hoops to jump to get the latest version. Thank-you for all your work!! -eva- THIS TRANSMISSION, INCLUDING ANY ATTACHMENTS OR FILES, CONTAINS AIRNET COMMUNICATIONS CORPORATION CONFIDENTIAL AND PROPRIETARY INFORMATION WHICH MAY BE OTHERWISE EXEMPT FROM DISCLOSURE. The information is intended to be for the exclusive use of the individual or entity named above. If you are not the intended recipient, be advised that any disclosure, copying, distribution or other use of this information is strictly prohibited. If you have received this transmission in error, please notify us by telephone at 1-321-984-1990 or by email to [EMAIL PROTECTED] immediately and do not read, print or save this information in any manner. - 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: Average of all NON-NULL columns in a ROW?
No, I'm looking for the average of all not NULL columns inside a certain _record/row_. There are other columns in the each row. Right now I have to do this: (these are survey questions, which don't necessarily need to be answered) SELECT IF(q1a,@cnt:=1,@cnt:=0), IF(q1b,@cnt:=@cnt+1,0), IF(q1c,@cnt:=@cnt+1,0), IF(q1d,@cnt:=@cnt+1,0), IF(q1e,@cnt:=@cnt+1,0), IF(q1f,@cnt:=@cnt+1,0), IF(q1g,@cnt:=@cnt+1,0), IF(q1h,@cnt:=@cnt+1,0), IF(q1i,@cnt:=@cnt+1,0), IF(q1j,@cnt:=@cnt+1,0), IF(q7,@cnt:=@cnt+1,0), IF(q8,@cnt:=@cnt+1,0), IF(q9,@cnt:=@cnt+1,0), ROUND((q1a+q1b+q1c+q1d+q1e+q1f+q1g+q1h+q1i+q1j+q7+q8+q9)/@cnt,2) AS surveyavg, This works but looks really bad and I was wondering if there is another way to do this. If this data were in columns, I could just AVG(q1a) and GROUP BY q1a, but this data is in each row. Ideas? graeme - Original Message - From: Cal Evans [EMAIL PROTECTED] To: Graeme B. Davis [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 1:04 PM Subject: Re: Average of all NON-NULL columns in a ROW? Select avg(id1) from tableName where id1 is not null; ? - Original Message - From: Graeme B. Davis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 11:29 AM Subject: Average of all NON-NULL columns in a ROW? Is there a way to get the AVG of all specified NON-NULL columns in one row? Right now I am doing this in a little script, but it would be nice if I could do something like this: DATA - id1044NULL3NULL I want the average of 0,4,4,3 ie (0+4+4+3)/4 is there a way to do this in a SELECT query? Thanks! Graeme - 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: MYSQL performance question
VVM Ravikumar Sarma Chengalvala wrote: Joshua, Comparing Oracle vs. MySQL myisam tables, MySQL will save disk space, and provide you with much faster queries, but you have to be aware of the table locking issues if you are doing updates/inserts/deletes mixed with reads. MySql documentation says that mysql enforces table level locking if no explicit locking is there.If I am using non transaction sensitive tables can I go ahead without providing any explicit locking?I am using myISAM. Regards, Ravi You don't need to provide explicit lockin in most cases. But if you do not and mysql needs to make a lock for an update it will lock the table against other reads until the update is complete, it is quick, but if you are pushing the server with many updates and reads at the same time it can be very slow, or if you have updates that take a long time you can create a large queue of waiting readers. - 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
Disregard Ugly Output
Pardon my idiocy in posting that this command gave ugly output: select if(substring(username,1,8)='cctcpin-', lcase(trim(leading concat(substring_index(username,'-',1), '-') from username)), lcase(username)) as username, sec_to_time(sum(acctsessiontime)) as session from radacct where acctstarttime between '2001-04-21' and '2001-05-21' and acctstoptime group by username order by username; The problem wasn't the data, it was the column header. I added the 'as username' portion and it returned to a normal column width. I'll drag my noise back off the list now. MySQL was not the culprit. -- === Kip Turkphone: 915.234.5678 Systems Administrator or 800.695.9016 Killer of Spam/Writer of Code/Penguin Proponent West Central Net fax: 915.656.0071 === - 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: request
On Tue, May 22, 2001 at 04:21:03PM +0200, Pascal Bruyere wrote: I'd like to know how many similtanous request support a MySql database . It depends on the hardware/software involved, but you can pretty easily handle a few thousand on good hardware and a good operating system. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 2 days, processed 20,032,703 queries (79/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
Query on Merge table
Why did this not work? mysql select cdate,hour(ctime) as hr, count(distinct sName) from dbpopsj1 where hour(ctime) = 0 group by cdate, hr order by cdate, hr; ERROR 1032: Can't find record in 'dbpopsj1' While this did work! mysql select cdate,hour(ctime) as hr, count(sName) from dbpopsj1 where hour(ctime) = 0 group by cdate, hr order by cdate, hr; ++--+--+ | cdate | hr | count(sName) | ++--+--+ | 2001-05-21 |0 | 256078 | ++--+--+ 1 row in set (35.74 sec) And this worked and had to read thru all records and sort them! select * from dbpopsj1 where sIpa=24.13.105.71 order by ctime limit 50; - this worked display not pertinent. This table is a merge table of 4 tables. Also the first query works when I called any of the base tables; - 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: Re: Ugly output
The excess space could've come from the very long column name of the first field. Shrink that with an alias: select if(substring) AS shortname, sec_to_time( ) AS session ... Cheers, Kent Hoover filter fodder:database,sql - 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: Re: Ugly output
The excess space could've come from the very long column name of the first field. Shrink that with an alias: select if(substring) AS shortname, sec_to_time( ) AS session ... Cheers, Kent Hoover filter fodder:database,sql - 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: count(*) on different tables
I had the same problem to solv before, and this is what I did. PS: I don't know if this is the right way to do it, but it worked for me. mysql CREATE TABLE Test.myTempTable (myTable VARCHAR(20), nrows AS INTEGER); mysql INSERT INTO Test.myTempTable SELECT Table1, COUNT(*) FROM Table1; // 10 rows mysql INSERT INTO Test.myTempTable SELECT Table2, COUNT(*) FROM Table2; // 265 rows mysql SELECT * FROM Test.myTempTable; ++--+ | myTable| nrows| ++--+ | Table1 | 10 | | Table2 | 265 | ++--+ 2 rows in set (0.00 sec) mysql DROP TABLE Test.myTempTable; // Finally drop the temp table It is a little hard to code it in a application since you need to RUN 4 statements and treat each result in a separated row. But you can use this to count any quantity of tables. []'s Crercio O. Silva - 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: sql manual
Found this one to be excellent: http://w3.one.net/~jhoffman/sqltut.htm On Tuesday 22 May 2001 08:58, webmaster wrote: is there any sql manaual over the internet0' no matter that is not a mysql specificly, I would like to have a good manual for beguiners, cheers -- Joshua Kugler Associated Students of the University of Alaska Fairbanks Information Services Director [EMAIL PROTECTED] 907-474-7601 - 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
Replication problem
I am attempting to setup a stand by database using replication. I have a table abc on the master server that I wish to replicate to the slave server as abclive. In other words, I want the updates to be written into a database of a different name on the slave. I thought this would be easy, and I tried using the following config options in the slave my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock master-host=10.0.0.1 master-user=repl master-password=somepass master-port=3306 server-id=2 replicate-wild-do-table=abc.% replicate-rewrite-db=abc-abclive I grabbed a snapshot of the abc database and copied to to the datadir, renaming it to abclive. I started the master/slave and it didn't work- updates were not being propogated into abclive and the error log reported no problem. I also tried the following options: replicate-do-db=abc replicate-rewrite-db=abc-abclive but it didn't work and then I read a note which said that if you use inserts like insert into abc.field ... where you named the database in the insert (which we do) then you needed to use the replicate-wild-do-table command. But neither seemed to have the desired effect. Is there an issue with using inserts with database names AND replication with the rewrite-db ??? I went back and took out the rewrite line in the first config set above and moved the slave database back to abc, and replication worked fine. I just can't get it to replicate with a new name, abclive. Can someone please tell me how to accomplish what I'm trying to do? Our standby database will be on a machine which contains daemons that are ready to go. To set these up, I need to test them and they use the original abc database name. So during the setup, I need to have a writable database to test the setup and must have the replicated one go to a different name. Once the machine is ready to go live, the test database just needs dropped and the replicated standby can be renamed. If I can't solve the rewrite problem, then I'll be forced to setup 2 instances of MySQL simply for this reason. Any hints are appreciated... Byron Guernsey V3 Networks, Inc.
database shutdown!
Hi, 1. Anyone recommends a particular shutdown procedure for mysql database? 2. The reason: i)I get some weardy errors on NT4.0 workstation like memory referencing while doing the shutdown ii) Some times I get an error while shutting down the mysql server .Once I use SHOW Databases after the shutdown command the problems never occur. 3. I am not using mysql as an NTService and I installed mysql in 'D:/' drive. 4. Your experience and suggestions will be highly appreciated Regards, Ravi Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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: Fulltext Strange Results...
Do you know roughly when this will be done. * * Visit http://www.computerstaff.net - Computer Jobs at all LEVELS * * -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED]] Sent: 20 May 2001 22:44 To: technical Support Subject: Re: Fulltext Strange Results... Hi! On May 20, technical Support wrote: I have just done this: select * from indexed_table where match(indexed_col) against ('visual basic') This returns rows with either visual or basic. I read phrase searching was allowed in mySQL v4.0? It's still in todo. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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.sock file
We have a client program that fails trying to access tmp/mysql.sock on open. It seems that newer servers either dont create the tmp file or store it somewhere else. What can be done to make the existing binary work with both older and newer servers? Is there a my.cnf setting? And also, why has this become an issue? Can it be a build issue with the server? Or is this file being scrubbed by some startup routine (Debian is well-known to do damage upon start up this way)? thanks. Dennis - 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
configure error: no curses/termcap library found
I am running Redhat Linux 7.0 (kernel version 2.2.17-14) on a Pentium II, and I loaded the source for mysql version 3.23.38. When I run ./configure --prefix=/usr/local/mysql (as it says to do in the documentation), I get the error listed as the subject of this mailing. When doing a search for curses I find /usr/lib/libncurses.so.4 /usr/lib/libncurses.so.4.0 /usr/lib/libncurses.so.5 /usr/lib/libncurses.so.5.2 When searching for termcap, I find /lib/libtermcap.so.2 /lib/libtermcap.so.2.0.8 What am I doing wrong? Thanks, Bill Tangren bjt[AT]usno.navy.mil - 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: Can/Does Mysql Broadcast to IPs?
In the last episode (May 22), Jacob Martin said: Actually what I'm asking is what will happen if 50 users all in the same subnet (A.B.C.*) all request the same huge row from the database? Are there any optimizations for this? Since the transport is TCP, there's nothing Mysql can do about it. -- Dan Nelson [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
Fulltext and Word Documents ...
I have just loaded a MICROSOFT WORD document to be stored in a mySQL blob column. I will like to know if the blob column that will be indexed by the FULLTEXT indexer? * * Visit http://www.computerstaff.net - Computer Jobs at all LEVELS * * - 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
Installation Trouble
I installed the RPM of 3.23 on RH 7.1 today. Everything seemed to go OK (once I got Perl in the right place), but now I don't have any client tools to access any of the databases or run sql queries. Any suggestions? Sean Tibbetts Do nothing which is of no use. Miyamoto Musashi Sean Tibbetts Web Technologies Group CFC-OTS (502) 573-3850 ext. 201 Do nothing which is of no use. Miyamoto Musashi - 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 file
Dennis, do a find / -name mysql.sock 2/dev/null to find exactly where mysql puts this file.. My rpm install put it in /var/lib/mysql/mysql.sock Find where your mysql.sock file is and make a symbolic on it pointing to /tmp/mysql.sock ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock Also check your file permissions. Make sure the mysqld user has access to this directory. Chad -Original Message- From: Dennis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 22, 2001 3:56 PM To: [EMAIL PROTECTED] Subject: mysql.sock file We have a client program that fails trying to access tmp/mysql.sock on open. It seems that newer servers either dont create the tmp file or store it somewhere else. What can be done to make the existing binary work with both older and newer servers? Is there a my.cnf setting? And also, why has this become an issue? Can it be a build issue with the server? Or is this file being scrubbed by some startup routine (Debian is well-known to do damage upon start up this way)? thanks. Dennis - 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: configure error: no curses/termcap library found
Just a guess, but you might have to add --with-ncurses=/usr so that you provide the basedir for the curses package. Do a ./configure --help and it will list the options. I believe --with-curses or --with-ncurses is one of them. Also, I think there is supposed to be a libncurses.so and libtermcap.so without a version number and its usually a symlink the the real file (with a version number). You'll want to look at the libs you found to distinguish which are symlinks, and when you find the latest version of the library (like libncurses.so.5.2) you can symlink libncurses.so to it. Likewise with the libtermcap. Byron - Original Message - From: Bill Tangren [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 22, 2001 4:59 PM Subject: configure error: no curses/termcap library found I am running Redhat Linux 7.0 (kernel version 2.2.17-14) on a Pentium II, and I loaded the source for mysql version 3.23.38. When I run ./configure --prefix=/usr/local/mysql (as it says to do in the documentation), I get the error listed as the subject of this mailing. When doing a search for curses I find /usr/lib/libncurses.so.4 /usr/lib/libncurses.so.4.0 /usr/lib/libncurses.so.5 /usr/lib/libncurses.so.5.2 When searching for termcap, I find /lib/libtermcap.so.2 /lib/libtermcap.so.2.0.8 What am I doing wrong? Thanks, Bill Tangren bjt[AT]usno.navy.mil - 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
Two connections with C-API
Is it possible to connect a database two times simultaneously with the C-API? I always get a segfault when connecting a second time. I need this because I want to feed a second table with data processed within a mysql_use_result fetch. I can't use mysql_store_result, because the result set is very huge. Any other suggestions? Thanks in advance Robert - 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
Connect to server problem
Hi I have the Mysql server on W2k and local all is OK, but when I want connect from other computer to Mysql I became error message " General SQL error - alias " my application use Delphi. please help me. TNX Krystian - 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
Using identical column names in seperate tables
From the MySQL Manual (13.6 Other Optimization Tips) http://www.mysql.com/doc/T/i/Tips.html Columns with identical information in different tables should be declared identical and have identical names. Before Version 3.23 you got slow joins otherwise. Try to keep the names simple (use name instead of customer_name in the customer table). To make your names portable to other SQL servers you should keep them shorter than 18 characters. I'm a little confused about this statement. I'm reading two possible tips, and I want to make sure I understand... How does keeping names 'simple' optimize my database? How does naming columns with identical information in different table the same optimize my database? Thanks, Nathan - 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 file
I faced the same problem and I solved it by creating a link under tmp called mysqld.sock pointing to my mysql.sock let me know if it helped Siomara From: Dennis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: mysql.sock file Date: Tue, 22 May 2001 16:56:19 -0400 We have a client program that fails trying to access tmp/mysql.sock on open. It seems that newer servers either dont create the tmp file or store it somewhere else. What can be done to make the existing binary work with both older and newer servers? Is there a my.cnf setting? And also, why has this become an issue? Can it be a build issue with the server? Or is this file being scrubbed by some startup routine (Debian is well-known to do damage upon start up this way)? thanks. Dennis - 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 _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.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: Two connections with C-API
Is it possible to connect a database two times simultaneously with the C-API? works... just wrote a bug with a pointer sry for posting Robert - 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: Fulltext and Word Documents ...
On Tue, May 22, 2001 at 10:37:20PM +0100, Nii Larnyoh wrote: I have just loaded a MICROSOFT WORD document to be stored in a mySQL blob column. I will like to know if the blob column that will be indexed by the FULLTEXT indexer? Given that it's not [just] text, what do you expect to happen? I'd expect it to either not work at all, or to work in a semi-broken way. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 3 days, processed 20,532,951 queries (78/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
FULLTEXT built for 2 char words OK ?
We need FULLTEXT searching on 2 character length words. If we recompile MySQL to do FULLTEXT indexes on 2 char length words is it going to work OK or will such short words undo the advantages of FULLTEXT ? Also will the data files be useable by other standard MySQL binary builds or will we have to use MySQLs built for 2 char length FULLTEXT words to access the data files ? thanks, - Sam. - Do You Yahoo!? Yahoo! Auctions $2 Million Sweepstakes - Got something to sell?
TR : Date format prolem in database
Database,sql,query -Message d'origine- De : Nicolas Villatte [mailto:[EMAIL PROTECTED]] Envoyé : mardi 22 mai 2001 23:18 À : '[EMAIL PROTECTED]' Objet : Date format prolem I insert a date like this '2002-10-15' in the DB, everything is ok when doing a select with DBTools, but when I am listing the fields in Jscript using ASP The field format is : 'Tue Oct 15 00:00:00 UTC+0200 2002'... Does anyone has a clue about this problem? Thanks, Nicolas Villatte __ IT Manager Creative Web SPRL Rue Kessels straat, 38 1030 Brussels Office Phone: +32 2 2450110 Office Fax: +32 2 2161628 Mobile Phone : +32 477 588136 Internet Mail: mailto:[EMAIL PROTECTED] Visit us on the web: http://www.creativeweb.be __ BEGIN:VCARD VERSION:2.1 N:Villatte;Nicolas FN:Nicolas Villatte ORG:Creative Web SPRL TITLE:IT Manager TEL;WORK;VOICE:+32 (2) 245.01.10 TEL;HOME;VOICE:+32 (02) 344.94.05 TEL;CELL;VOICE:+32 (477) 588136 TEL;WORK;FAX:+32 (2) 216.16.28 ADR;WORK:;;Rue Kessels, 38;Bruxelles;;1030;Belgique LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Rue Kessels, 38=0D=0ABruxelles 1030=0D=0ABelgique ADR;HOME:;;Rue des châtaignes, 51;Bruxelles;;1190;Belgique LABEL;HOME;ENCODING=QUOTED-PRINTABLE:Rue des ch=E2taignes, 51=0D=0ABruxelles 1190=0D=0ABelgique EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20010226T195907Z END:VCARD BEGIN:VCARD VERSION:2.1 N:Villatte;Nicolas FN:Nicolas Villatte ORG:Creative Web SPRL TITLE:IT Manager TEL;WORK;VOICE:+32 (2) 245.01.10 TEL;HOME;VOICE:+32 (02) 344.94.05 TEL;CELL;VOICE:+32 (477) 588136 TEL;WORK;FAX:+32 (2) 216.16.28 ADR;WORK:;;Rue Kessels, 38;Bruxelles;;1030;Belgique LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Rue Kessels, 38=0D=0ABruxelles 1030=0D=0ABelgique ADR;HOME:;;Rue des châtaignes, 51;Bruxelles;;1190;Belgique LABEL;HOME;ENCODING=QUOTED-PRINTABLE:Rue des ch=E2taignes, 51=0D=0ABruxelles 1190=0D=0ABelgique EMAIL;PREF;INTERNET:[EMAIL PROTECTED] REV:20010226T195907Z END:VCARD smime.p7s
Re: Using identical column names in seperate tables
In the last episode (May 21), Nathanial Hendler said: From the MySQL Manual (13.6 Other Optimization Tips) http://www.mysql.com/doc/T/i/Tips.html Columns with identical information in different tables should be declared identical and have identical names. Before Version 3.23 you got slow joins otherwise. Try to keep the names simple (use name instead of customer_name in the customer table). To make your names portable to other SQL servers you should keep them shorter than 18 characters. I'm a little confused about this statement. I'm reading two possible tips, and I want to make sure I understand... They put two ideas into that paragraph: 1 - Identical definitions on joined fields make the optimizer happy 2 - For neatness purposes, use simple column names -- Dan Nelson [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
Porting Access to MySQL
Hi, I have a user who want to move there database from Access to MySQL. Are there tools available to make the conversion simple, or what is the best way of going about this? 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
RE: Porting Access to MySQL
www.accessmysql.com I use it when doing conversions free download too Cost ya 30 dollars US not get annoying messages every 5 or 10 minutes Dave Carter Chief Web Architect Accelerated Business Technologies, Inc. http://www.abti.cc 717.464.2970 -Original Message- From: Mitchell [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 22, 2001 6:43 PM To: [EMAIL PROTECTED] Subject: Porting Access to MySQL Hi, I have a user who want to move there database from Access to MySQL. Are there tools available to make the conversion simple, or what is the best way of going about this? 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 - 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: Can/Does Mysql Broadcast to IPs?
Anyone up for a new module? Dan Nelson wrote: In the last episode (May 22), Jacob Martin said: Actually what I'm asking is what will happen if 50 users all in the same subnet (A.B.C.*) all request the same huge row from the database? Are there any optimizations for this? Since the transport is TCP, there's nothing Mysql can do about it. -- Dan Nelson [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 - 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: Can/Does Mysql Broadcast to IPs?
Hmmm...anyone up for a new module? ! Dan Nelson wrote: In the last episode (May 22), Jacob Martin said: Actually what I'm asking is what will happen if 50 users all in the same subnet (A.B.C.*) all request the same huge row from the database? Are there any optimizations for this? Since the transport is TCP, there's nothing Mysql can do about it. -- Dan Nelson [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 - 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
Possible UPDATE bug
Description: When updating a table after it has been selected with a LEFT JOIN, some UPDATE queries execute normally without actually updating the data, unless the data to be updated is selected normally first. This script is written in mod_perl, using DBI::mysql to connect to the database. The following are the tables referenced by the query: mysql desc dealer; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | dealer_id| int(8)| | PRI | NULL| auto_increment | | dealer_code | varchar(5)| | | || | group_id | int(8)| | | 0 || | emp_id | int(8)| | | 0 || | address_id | int(8)| | | 0 || | dealer_name | varchar(200) | | | || | dealer_state | int(8)| | | 0 || | dealer_zip | varchar(5)| | | || | dealer_contact_fname | varchar(100) | YES | | NULL|| | dealer_contact_lname | varchar(100) | YES | | NULL|| | dealer_URL | varchar(200) | YES | | NULL|| | dealer_email | varchar(100) | YES | | NULL|| | dealer_password | varchar(100) | YES | | NULL|| | dealer_ph1 | varchar(25) | YES | | NULL|| | dealer_ph2 | varchar(25) | YES | | NULL|| | dealer_fax | varchar(25) | YES | | NULL|| | active | enum('Y','N') | | | N || +--+---+--+-+-++ 17 rows in set (0.00 sec) mysql desc dealer_lead_method; +---++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---++--+-+-+---+ | dealer_id | int(8) | | PRI | 0 | | | method_id | tinyint(4) | | PRI | 0 | | | method_parameters | text | YES | | NULL| | +---++--+-+-+---+ 3 rows in set (0.00 sec) mysql desc tlkp_dealer_lead_method; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | method_id | tinyint(4) | | PRI | NULL| auto_increment | | method_name | varchar(50) | YES | | NULL|| +-+-+--+-+-++ 2 rows in set (0.00 sec) mysql desc tlkp_address; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | address_id | int(8) | | PRI | NULL| auto_increment | | export | int(8) | | | 0 || | address_addr_1 | varchar(200) | YES | | NULL|| | address_addr_2 | varchar(200) | YES | | NULL|| | address_city | varchar(200) | YES | | NULL|| | address_state | int(8) | | | 0 || | address_zip| varchar(5) | YES | | NULL|| | address_plus4 | varchar(4) | YES | | NULL|| | cdate | int(8) | | | 0 || ++--+--+-+-++ 9 rows in set (0.01 sec) mysql desc tlkp_state; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | state_id | int(8) | | PRI | NULL| auto_increment | | state_abbr | char(2) | YES | | NULL|| | state_name | varchar(100) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.00 sec) The select with join: my $q = SELECT * FROM dealer, tlkp_address, tlkp_state LEFT JOIN dealer_lead_method ON dealer.dealer_id = dealer_lead_method.dealer_id LEFT JOIN tlkp_dealer_lead_method ON dealer_lead_method.method_id = tlkp_dealer_lead_method.method_id WHERE dealer.dealer_id = $dealer_id AND dealer.address_id =
[Fwd: Ramifications of /Lost+Found in DataDir]
database Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: database,sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. You have written the following: Are there any? Could someone somehow force a file to go to lost+found and then read it? Am I safe if I turn off all file privileges in the grants? What do you think? - 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
About MyODBC Some Common Errors
Hi! Avukatpro == Avukatpro Limited Sirketi [EMAIL PROTECTED] writes: Avukatpro In ODBC documents some common error section is says Avukatpro - If you are connecting to a server with a character set that isn't compiled Avukatpro into the MySQL client library (the defaults are: Avukatpro latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis Avukatpro ) then you need to install the mysql character definitions from the Avukatpro charsets directory into the c:\mysql\share\charsets. The client will automaticly use the character set that the server is used, so you don't have to specify this in my.ini Yes, if you have VC++, you should be able to quite easily recompile mysql.lib with all character sets and link MyODBC with this. Regards, Monty - 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
Help! (LONGTEXT)
Hello mysql, Can anybody tell me what should I do? I use LONGTEXT column in my table to store data files (html, for example). But I see that I cannot simply INSERT INTO table SET longtext_column=whole_file. But I must do it today. But how??? Thanks beforehand. -- Best regards, Olexandr Vynnychenko mailto:[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: Help! (LONGTEXT)
At 3:22 AM +0300 5/23/01, Olexandr Vynnychenko wrote: Hello mysql, Can anybody tell me what should I do? I use LONGTEXT column in my table to store data files (html, for example). But I see that I cannot simply INSERT INTO table SET longtext_column=whole_file. But I must do it today. But how??? The LOAD_FILE() function might help you if you have the files on the server host and you have the FILE privilege. Thanks beforehand. -- Best regards, Olexandr Vynnychenko mailto:[EMAIL PROTECTED] -- Paul DuBois, [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[2]: Help! (LONGTEXT)
Hello Paul, Wednesday, May 23, 2001, 3:24:29 AM, you wrote: PD At 3:22 AM +0300 5/23/01, Olexandr Vynnychenko wrote: Hello mysql, Can anybody tell me what should I do? I use LONGTEXT column in my table to store data files (html, for example). But I see that I cannot simply INSERT INTO table SET longtext_column=whole_file. But I must do it today. But how??? PD The LOAD_FILE() function might help you if you have the files on the PD server host and you have the FILE privilege. Thanks beforehand. -- Best regards, Olexandr Vynnychenko mailto:[EMAIL PROTECTED] But if the file is local? -- Best regards, Olexandrmailto:[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