foreign key creation on MyISAM storage engine
HI, I previously tried this same question on the GUI tool list, but not much traffic there, so I thought I'd give it a go here. I've been reading some articles that suggest I should be able to create a relationship on a MyISAM table, it would be great if someone could confirm or deny this. --- Hi all, has anyone experienced any problems creating foreign keys (relationships) on tables that are of storage engine MyISAM? I can't seem to be able to create any relationships on those tables in MySQL Administrator 1.1.8 Any help would be much appreciated. Kind regards, Taco Fleur - CEO Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years!
Re: foreign key creation on MyISAM storage engine
Hello Taco, I previously tried this same question on the GUI tool list, but not much traffic there, so I thought I'd give it a go here. I've been reading some articles that suggest I should be able to create a relationship on a MyISAM table, it would be great if someone could confirm or deny this. --- Hi all, has anyone experienced any problems creating foreign keys (relationships) on tables that are of storage engine MyISAM? Now, there will be people telling you that you CAN create foreign keys. Well, in a way, this is true - you can create columns with values that point to other tables. What you probably are asking, is if you can referential integrity constraints. The answer, for MyISAM tables, is NO. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated select query
Gabriel PREDA wrote: About the first problem I think you need to give us more data ! Okay i thought yesterday how i can make up some real problem and have this: Guess you have a database with customers and a database with projects. Those prijects have timestamps so you know what time he added that project. Now my problem is to get the First project the customer has added. And i want it to have it like ++--+---+ |Customer|Time |Projectname| ++--+---+ |John|2005-11-23|Teatime| |Jim |2005-02-19|Having Lunch | |Bob |2005-06-11|My big Project!| ++--+---+ So that i have the first project every customer has added first and only that. But the Databases looks like Customer: +--++--++---+-+ |id|Fnam|Snam |Street |Code |City | +--++--++---+-+ |01|John|Doh |Funnystreet 4 |87624 |Somewherecity| |02|Jim |Bobjoe|Anotherfunnystreet 8|213+E13|Othercity| |03|Bob |Joejim|boringstreet 67 |324456 |Boringcity | +--++--++---+-+ Projects +--+-++--+ |id|parent_id|Name|Time | +--+-++--+ |01|01 |Teatime |2005-11-23| |02|01 |Suppertime |2005-12-14| |03|02 |having Lunch|2005-02-19| |04|02 |having Dinner |2005-04-12| |05|02 |having something|2005-07-17| |06|03 |My small Project|2005-02-10| |07|03 |My big Project! |2005-06-11| +--+-++--+ I hope this enlights it a bit Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.18 crashing on AMD64
Hello! I have a dual Opteron 250 system with 4GB memory running Debian with MySQL version 5.0.18 and unfortunately it keeps crashing at (somewhat) random intervals with messages like: | Mar 14 00:32:59 zwart mysqld[29820]: *** glibc detected *** double | free or corruption (!prev): 0x012b1ab0 *** | Mar 15 02:24:17 zwart mysqld[13255]: *** glibc detected *** free(): | invalid next size (normal): 0x012d3d30 *** | Mar 16 00:32:51 zwart mysqld[17749]: *** glibc detected *** double | free or corruption (!prev): 0x01333540 *** | Mar 16 14:44:07 zwart mysqld[471]: *** glibc detected *** double | free or corruption (!prev): 0x013e4160 *** All followed by the message: | Mar 16 14:44:07 zwart mysqld[471]: mysqld got signal 6; Which causes the server to stop responding to any query done. It's still running, and i can still connect to it, but anything from select statements to showing tables makes the session hang indefinitely. Eventually causing mysqld to refuse connections, and the only solution is to kill -9 the mysqld process which leaves corrupt tables... I have another AMD64 running on somewhat the same setup, same kernel, same mysql version, same processors, only less memory (2GB). Is this problem caused by bad memory? And if so, why does only mysql suffer from it? I have perl processes running that take about the same amount of memory as mysql does but yet only mysql keeps crashing. Any hints are welcome! Thanks in advance! Kind regards, Sander Smeenk. -- | For security, this message has been encrypted with ROT13 twice. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated select query
|06|03 |My small Project|2005-02-10| |07|03 |My big Project! |2005-06-11| Small mistake. The project with ID 06 should have a date above ID 07. Sorry for that! -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complicated select query
try something like this select customer,max(time),name from customers join projects on projects.parent_id=customer.id group by customer; regards Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services Barry [EMAIL PROTECTED] wrote: Gabriel PREDA wrote: About the first problem I think you need to give us more data ! Okay i thought yesterday how i can make up some real problem and have this: Guess you have a database with customers and a database with projects. Those prijects have timestamps so you know what time he added that project. Now my problem is to get the First project the customer has added. And i want it to have it like ++--+---+ |Customer|Time |Projectname| ++--+---+ |John|2005-11-23|Teatime| |Jim |2005-02-19|Having Lunch | |Bob |2005-06-11|My big Project!| ++--+---+ So that i have the first project every customer has added first and only that. But the Databases looks like Customer: +--++--++---+-+ |id|Fnam|Snam |Street |Code |City | +--++--++---+-+ |01|John|Doh |Funnystreet 4 |87624 |Somewherecity| |02|Jim |Bobjoe|Anotherfunnystreet 8|213+E13|Othercity| |03|Bob |Joejim|boringstreet 67 |324456 |Boringcity | +--++--++---+-+ Projects +--+-++--+ |id|parent_id|Name|Time | +--+-++--+ |01|01 |Teatime |2005-11-23| |02|01 |Suppertime |2005-12-14| |03|02 |having Lunch|2005-02-19| |04|02 |having Dinner |2005-04-12| |05|02 |having something|2005-07-17| |06|03 |My small Project|2005-02-10| |07|03 |My big Project! |2005-06-11| +--+-++--+ I hope this enlights it a bit Barry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql server
i have a hp server . i have installed mysql 4 in that when ever i shut down the system and start again the mysql server does not start it says its an error when i try to reconfigure it service started will be disabled can anyone help me out - Original Message - From: leo huang [EMAIL PROTECTED] To: Lakshmi M P [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, March 28, 2006 11:47 AM Subject: Re: mysql restart error hi, Lakshmi The mysql process had ended. I get it from both mysql err log and ps output. regards, Leo Huang 2006/3/28, Lakshmi M P [EMAIL PROTECTED]: Run ps -ef | grep mysql and see any mysql process is running and if so kill the same and try to start mysql.It may help. leo huang wrote: hi, I met the MySQL restart error today. First, I stopped the running mysql server using /usr/local/mysql/bin/mysqladmin -uroot shutdown. After the server shutdowned, I restarted it using /usr/local/mysql/bin/mysqld_safe . Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port: Address already in use. There was no other process that was using the port 3306 which mysql server use. But there were some mysql connect did not release because the shutdown. The error log is followed: 060328 8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown 060328 8:20:47 InnoDB: Starting shutdown... 060328 8:20:49 InnoDB: Shutdown completed; log sequence number 120 2134241340 060328 8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete 060328 08:20:49 mysqld ended 060328 08:21:15 mysqld started 060328 8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 060328 8:21:15 [ERROR] Do you already have another mysqld server running on port: 3306 ? 060328 8:21:15 [ERROR] Aborting 060328 8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete 060328 08:21:15 mysqld ended The netstat outputs are followed: $ netstat -al Active Internet connections (including servers) Proto Recv-Q Send-Q Local Address Foreign Address(state) tcp4 0 0 bj.3306 s4.9405 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.5168 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.25007 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.9940 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.3916 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.15229 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.6479 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.7873 FIN_WAIT_2 Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE. Any comment will be great thankful! Regards, Leo Huang -- regards, Lakshmi.M.P. DBA-Support Sify Limited. Ext:4134 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] www.sify.com - your homepage on the internet for news, sports, finance, astrology, movies, entertainment, food, languages etc No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.2/294 - Release Date: 3/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CREATE TABLE x AS with ENGINE = ARCHIVE
Hi, I'm using MySQL 4.11. Is it possible to use the CREATE TABLE x AS syntax alongside the ENGINE = x pragma, since this would make archiving of tables very simple. I require something along the lines of this: CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE If this cannot be done then I can always get equivalent functionality by performing CREATE TABLE x (...) ENGINE=x followed by INSERT INTO x SELECT * FROM x, however this is not so neat since it require knowledge of the source table structure which makes it less ideal for automation. Many thanks, Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE x AS with ENGINE = ARCHIVE
I dont think that it is possible to specify the engine when creating a table this way, you could however create the table using the: CREATE TABLE old AS SELECT * FROM request_log and then issue an alter table command setting the engine to whatever you like. Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services Terry Burton [EMAIL PROTECTED] wrote: Hi, I'm using MySQL 4.11. Is it possible to use the CREATE TABLE x AS syntax alongside the ENGINE = x pragma, since this would make archiving of tables very simple. I require something along the lines of this: CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE If this cannot be done then I can always get equivalent functionality by performing CREATE TABLE x (...) ENGINE=x followed by INSERT INTO x SELECT * FROM x, however this is not so neat since it require knowledge of the source table structure which makes it less ideal for automation. Many thanks, Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Pure Web Solution http://www.purewebsolution.co.uk PHP, MYSQL, Web Design Web Services -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE x AS with ENGINE = ARCHIVE
On 3/28/06, Pure Web Solution [EMAIL PROTECTED] wrote: I dont think that it is possible to specify the engine when creating a table this way, you could however create the table using the: CREATE TABLE old AS SELECT * FROM request_log and then issue an alter table command setting the engine to whatever you like. Thanks Paul, That solution seems a little wasteful in terms of resources though. If there is no nice way to achieve this operation then do people think that it would be a good idea to push for the inclusion of this functionality. If more folks than myself can see the benefit in this then please speak up, in which can I'll cross post this to the development list to see what the devs make of it. Warm regards, Tez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE x AS with ENGINE = ARCHIVE
Terry I require something along the lines of this: CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE CREATE TABLE tblname ENGINE=archive SELECT * FROM request_log; PB - Terry Burton wrote: Hi, I'm using MySQL 4.11. Is it possible to use the CREATE TABLE x AS syntax alongside the ENGINE = x pragma, since this would make archiving of tables very simple. I require something along the lines of this: CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE If this cannot be done then I can always get equivalent functionality by performing CREATE TABLE x (...) ENGINE=x followed by INSERT INTO x SELECT * FROM x, however this is not so neat since it require knowledge of the source table structure which makes it less ideal for automation. Many thanks, Tez -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.2/293 - Release Date: 3/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table doesn't exist?
Thank you Dilipkumar... boy did I learn my lesson. The one good thing about this is that the database was at the end of the testing stage, so I didn't lose critical customer data. I could have gotten a backup copy of the data files from our server backups, but at this point that would have been more trouble than just rebuilding the table. Next time I might not be so lucky, so this morning I did go ahead and create my own backup copies of everything! Thanks everybody for your help! -Original Message- From: Dilipkumar [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 28, 2006 4:24 AM To: Sara Woglom Cc: mysql@lists.mysql.com Subject: Re: Table doesn't exist? Hi, Better suggestion is you would have backed your ibdata1 and alo the log file because that is the data files which contains your precouse data. If you have your backup try to import the lost tables. Restart your mysql with your new ibdata1 files and import. This might help to solve your problem. Sara Woglom wrote: I had to shut down my MySQL server (5.0.18) because of an error while editing a table (Table is full). I restarted it fine, but in order to do so I had to delete all my ibdata and ib_logfile files. Now, I am getting ERROR 1146: Table 'dbname.tablename' doesn't exist. Obviously it does exist, because when I request SHOW TABLES; it lists them all correctly. What on earth is going on? I followed the directions for restarting the server and deleting the log files! I also ran mysqladmin flush-privileges, flush-hosts, and refresh. Please help! -- -- -- Thanks Regards, Dilipkumar DBA Support
Re: CREATE TABLE x AS with ENGINE = ARCHIVE
On 3/28/06, Peter Brawley [EMAIL PROTECTED] wrote: I require something along the lines of this: CREATE TABLE old AS SELECT * FROM request_log ENGINE=ARCHIVE CREATE TABLE tblname ENGINE=archive SELECT * FROM request_log; PB Excellent. Exactly what I need. Thanks :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indiana Time Zone Issues
Hello All, Indiana will move to EDT on April 2nd. I am concerned with MySQL DATETIME stamps that I have stored in my systems and whether or not I need to restart each MySQL daemon to ensure that it reads the correct system time and zone info. Your help is greatly appreciated. Regards, --Dave David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax: 260.459.6271 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql timezone problem
after changing my system timezone from UTC to MSD i have the following problem: after restarting mysql server its timezone has not changet at all: mysql show variables like '%zone%'; +--++ | Variable_name| Value | +--++ | system_time_zone | UTC| | time_zone| SYSTEM | +--++ meanwhile system command date says the following: # date Tue Mar 28 18:35:45 MSD 2006 OS: FreeBSD 6.0-RELEASE mysql: mysql Ver 14.12 Distrib 5.0.18, for portbld-freebsd6.0 (i386) using 5.0 -- С уважением, Сергей Аверьянов, ООО Паллант Мобайл, Руководитель отдела разработок +7 9272 702841 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indiana Time Zone Issues
At 9:38 -0500 3/28/06, Dave Juntgen wrote: Hello All, Indiana will move to EDT on April 2nd. I am concerned with MySQL DATETIME stamps that I have stored in my systems and whether or not I need to restart each MySQL daemon to ensure that it reads the correct system time and zone info. DATETIME values are stored as given and retrieved without change. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random 'select permission denied' since upgrade to 5.0.18
Hello list, Recently we upgraded from 3.23.3 to 5.0.18 and started noticing some weird behaviour. We have update scripts running on a regular interval and some of these scripts randomily exited with MySQL error: SELECT command denied to user 'user'@'host' for table 'example' However, this error message only appears sporadically. Usually these scripts run without any problems. tcpdump shows nothing more than we already know. I see the error message appear on different queries. We have the server running on Gentoo Linux (mysql-5.0.18-r30) as a master (with one slave on another Gentoo machine). Any ideas to debug this problem any further? Thanks in advance, Jorrit By the way, there's been a previous posting to this list describing similar problems (no solution though): http://marc.theaimsgroup.com/?l=mysqlm=113050497323853w=2 -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock'
- When I execute the command mysqld_safe:
[EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql
[1] 7298
[EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from
/var/lib/mysql
/* {processing... the cursor is blinking but I don't have any answer... So,
I press ENTER} */
[EMAIL PROTECTED] mysql]#
- The log message is:
060323 16:51:11 mysqld started
060323 16:51:11 InnoDB: Started; log sequence number 0 43655
060323 16:51:11 [Note] /usr/local/mysql/bin/mysqld: ready for
connections.
Version: '5.0.19-standard' socket: '/var/lib/mysql/mysql.sock' port:
3306 MySQL Community Edition - Standard (GPL)
- Aparently it's OK. But when I execute the command below occurs the
following error:
[EMAIL PROTECTED] mysql]# bin/mysqladmin version
bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock'
exists!
- In the log file, there isn't any new message. No Log Message was
generated.
- Now, I kill the process and tried another command (I don't execute the
inicial command bin/mysqld_safe –user=mysql ). But I have the same
answer:
[EMAIL PROTECTED] mysql]# bin/mysqladmin variables
bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/tmp/mysql.sock'
exists!
- No log message was generated.
- Maybe, I have change the port that MySQL is operating. Or the command
bin/mysqld_safe --user=mysql is not really starting the DB.
Maybe, I have change the port that MySQL is operating. Or the command
bin/mysqld_safe --user=mysql is not really starting the DB.
Thank's
Re: mysql timezone problem
[EMAIL PROTECTED] schrieb: after changing my system timezone from UTC to MSD i have the following problem: after restarting mysql server its timezone has not changet at all: mysql show variables like '%zone%'; +--++ | Variable_name| Value | +--++ | system_time_zone | UTC| | time_zone| SYSTEM | +--++ This is not necessarily wrong. See if select now(); gives you the correct time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql server
Good day! Can you be more concrete? I believe nobody in this list can read your thoughts. What tells mysql? What do you see in logs? prathima rao wrote: i have a hp server . i have installed mysql 4 in that when ever i shut down the system and start again the mysql server does not start it says its an error when i try to reconfigure it service started will be disabled can anyone help me out - Original Message - From: leo huang [EMAIL PROTECTED] To: Lakshmi M P [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, March 28, 2006 11:47 AM Subject: Re: mysql restart error hi, Lakshmi The mysql process had ended. I get it from both mysql err log and ps output. regards, Leo Huang 2006/3/28, Lakshmi M P [EMAIL PROTECTED]: Run ps -ef | grep mysql and see any mysql process is running and if so kill the same and try to start mysql.It may help. leo huang wrote: hi, I met the MySQL restart error today. First, I stopped the running mysql server using /usr/local/mysql/bin/mysqladmin -uroot shutdown. After the server shutdowned, I restarted it using /usr/local/mysql/bin/mysqld_safe . Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port: Address already in use. There was no other process that was using the port 3306 which mysql server use. But there were some mysql connect did not release because the shutdown. The error log is followed: 060328 8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown 060328 8:20:47 InnoDB: Starting shutdown... 060328 8:20:49 InnoDB: Shutdown completed; log sequence number 120 2134241340 060328 8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete 060328 08:20:49 mysqld ended 060328 08:21:15 mysqld started 060328 8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 060328 8:21:15 [ERROR] Do you already have another mysqld server running on port: 3306 ? 060328 8:21:15 [ERROR] Aborting 060328 8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete 060328 08:21:15 mysqld ended The netstat outputs are followed: $ netstat -al Active Internet connections (including servers) Proto Recv-Q Send-Q Local Address Foreign Address(state) tcp4 0 0 bj.3306 s4.9405 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.5168 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.25007 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.9940 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.3916 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.15229 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.6479 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.7873 FIN_WAIT_2 Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE. Any comment will be great thankful! Regards, Leo Huang -- regards, Lakshmi.M.P. DBA-Support Sify Limited. Ext:4134 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] www.sify.com - your homepage on the internet for news, sports, finance, astrology, movies, entertainment, food, languages etc No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.2/294 - Release Date: 3/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT searches with hyphens.
Does anyone know how to get FULLTEXT indexing not to treat hyphens as word breaks? Basically I am searching part numbers and descriptions and need something like Z-5500 to be returned. I want to keep using FT for the scoring. I lowered the min word length to 2. I am thinking I could lower it to 1 and replace the - in the `match against term` with a space, but it would be cleaner if this can be manipulated in the index. Thanks for any suggestions, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb errors on startup
Hello, I'm getting this in my errorlog: 060328 18:43:45 mysqld ended 060328 18:43:46 mysqld started 060328 18:43:46 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 060328 18:43:46 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './db16041/intradv_cms_websites.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a table created with InnoDB: CREATE TEMPORARY TABLE, and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 060328 18:43:46 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 060328 18:43:46 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './db16041/intradv_cms_wsepgmerchant.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a table created with InnoDB: CREATE TEMPORARY TABLE, and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 060328 18:43:46 InnoDB: Started; log sequence number 0 18831221 /vrmd/mysql/mysql-4.1.18/libexec/mysqld: ready for connections. Version: '4.1.18-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution How do I get rid of these messages / the errors? The page for troubleshooting didn't help. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.18 crashing on AMD64
On Tue, Mar 28, 2006 at 10:12:58AM +0200, Sander Smeenk wrote: Hello! I have a dual Opteron 250 system with 4GB memory running Debian with MySQL version 5.0.18 and unfortunately it keeps crashing at (somewhat) random intervals with messages like: | Mar 14 00:32:59 zwart mysqld[29820]: *** glibc detected *** double | free or corruption (!prev): 0x012b1ab0 *** | Mar 15 02:24:17 zwart mysqld[13255]: *** glibc detected *** free(): | invalid next size (normal): 0x012d3d30 *** | Mar 16 00:32:51 zwart mysqld[17749]: *** glibc detected *** double | free or corruption (!prev): 0x01333540 *** | Mar 16 14:44:07 zwart mysqld[471]: *** glibc detected *** double | free or corruption (!prev): 0x013e4160 *** You don't say which versions of glibc or the kernel you're running, but if you're running stock Debian Sarge, that's a problem because of its known-buggy glibc 2.3.2. We are still using MySQL 4.0.x, but have had good luck running Debian testing, and I expect sid would be good too if you don't need to run much more than a db server on this machine. I'd also use a recent kernel, though we've been running on a 2.6.13.1 kernel on a dual opteron with 6gb ram since last September without a problem, so the kernel doesn't have to be that recent. You can either manually install the later glibc's (2.3.5 or 2.3.6) from testing/sid, or just update the whole kit and kaboodle. --Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy regex replace?
On 2006-03-20, at 12:11, Pooly wrote: 2006/3/19, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED]: On 2006-03-18, at 00:59, Yani Copas wrote: Is there a quick and dirty way to update such that I can only affect the portion of a string (varchar column) that matches a regexp? (e.g. replace all '%20' with ' ' leaving the rest untouched?) You know that proverb - For a man in possession of a hammer, everything looks like a nail. Don't do that. MySQL is *really slow* with Regular Expressions. It will be much easier to SELECT all records you want to change, storing their IDs in a list (or array) construct, then tell your favourite script program to construct an REPLACE query out of these chosen few, after it does whatever you want it to do with the records' data. Yeah, but sometimes beoing able to do such things on the mysql command line would be very helpful ! (Instead of having a script for such simple things which would be like having a jack hammer for a nail.. ) Oh, in that case use a subselect syntax - inner one would SELECT FROM using REGEX syntax (use the fine manual, it's described in detail) for the outer one, wytch would create an UPDATE statement (SQL) using data from the inner one. Oh, add outermost one executing that statement, if you wish or simply copypaste the result of the previous cascade. -- Seks, seksić, seksolatki...uri: news:pl.soc.seks.moderowana Siúil, siúil, siúil A rúin...Siúil go sochair agus siúil go ciúin! Siúil go doras agus éalaigh liom! Is go dté tú mo mhúirnín slán... https://hyperreal.info | https://kanaba.info |= Szanuj Zieleń! smime.p7s Description: S/MIME cryptographic signature
Customer Recommendation Query
We are looking for some help with queries that will accomplish a similar feature to what Amazon does. When you purchase a product, Amazon looks at all other people who have purchased that product, and then looks at all of the OTHER products those people have purchased, and uses that data to suggest related products to you. That's essentially what we are trying to do. We have 3 tables: members, actions, and member_actions. The 'members' table tracks all of our customers, the 'actions' table tracks all of the different actions each member can complete, and the 'member_actions' table is the weak entity link that tracks which actions each member have completed. Let's say a member completes action 'abc'. We want to query the member_actions table for all members that have also completed action 'abc'. We then want to determine what the top 3 other actions are that were completed by members who have completed action 'abc', while making sure that only actions that have not already been completed by this member are considered. We are using MySQL version 3.23. There are approximately 500 unique rows in the 'actions' table and 2,000,000 rows in the member_actions table, with 3,000+ actions being recorded at any given time. Is it possible to achieve this functionality with one/few queries? The statistics above may be important because if a query takes too long to execute, the server may not be physically capable of executing that query 3,000+ times simultaneously. Another option we have considered is to create a separate table called 'correlation' with two fields: action and correlated_action. We would then populate this table in a batch process following the pseudocode below. SELECT DISTINCT(action) FROM member_actions Loop SELECT DISTINCT(member) FROM member_actions WHERE action = x Loop SELECT DISTINCT(action) FROM member_actions WHERE member = y AND action x Loop INSERT INTO correlation (action, correlated_action) VALUES (x, z) Then we could easily query this table to find correlated actions like so: SELECT DISTINCT(correlated_action), COUNT(*) AS count FROM correlation WHERE action = x GROUP BY correlated_action ORDER BY count DESC This would not solve the issue of only returning actions that the member had not already completed, but that could probably be accomplished by simply joining the correlation table back to the member_actions table. So, our question is whether or not this is feasible with a one/few query approach, or if this is something that should be accomplished with something similar to the approach above? Can anyone provide a good start for us?
problems/feature request ideas
2 weeks ago we had a server crashing, and while I was checking it out (before, during and after crashes) I noticed that after a crash, the server was slow. Threads would run for 200 seconds or more, and yet when they finished, nothing was written to the slow query log. Why would that be? Slow query logging was on all the time, and other slow queries were written after that (anything greater than 4 seconds would be) Also, do other folks find that a deadlock log would be useful? InnoDB obviously stores it in RAM because SHOW ENGINE INNODB STATUS will show you the last deadlock information. But I feel like a deadlock log would be useful, to see how many deadlocks we get in a certain period of time (but not an averageI'm sure there are peak times, etc). Any ideas/comments? -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with UTF and MySQL
On 2006-03-20, at 16:12, Gabriel PREDA wrote:
One must issue immediately after connection:
SET NAMES 'utf8'
Also look at:
SHOW VARIABLES LIKE 'collation_%';
SHOW VARIABLES LIKE 'character_set_%';
Server must know what you are assking for... and they ALL have to
talk the
same language !!!
IMO, it is OK to say that without the quotes :-} {-:
--
(defmethod node-to-dot ((node node)) ;; Viva Draconis!
(node-dot-using-attibute node (node-attribute node)))
(defmethod node-to-dot-using-attribute ((node node)
(attribute sc-element)) t) ;; https://hyperreal.info
smime.p7s
Description: S/MIME cryptographic signature
Li'l problem with count()
Hi
pFlies in pot = ?=flies();? /p
function flies(){
$sql = 'select count(*) from flies';
$lnk = mysql_connect('localhost','root','123456');
$db = mysql_select_db('moar',$lnk);
$result = mysql_query($sql,$lnk);
mysql_free_result($result);
mysql_close($lnk);
return $result;
}
Why this leads to 'ressource id #10' instead of the flies count ?
The query gives the actual count if I use it in a line command !!
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Li'l problem with count()
[snip]
pFlies in pot = ?=flies();? /p
function flies(){
$sql = 'select count(*) from flies';
$lnk = mysql_connect('localhost','root','123456');
$db = mysql_select_db('moar',$lnk);
$result = mysql_query($sql,$lnk);
mysql_free_result($result);
mysql_close($lnk);
return $result;
}
Why this leads to 'resource id #10' instead of the flies count ?
[/snip]
Because $result is a resource in PHP, not the actual result of the
query;
function flies(){
$sql = 'select count(*) AS flyCount from flies';
$lnk = mysql_connect('localhost','root','123456');
$db = mysql_select_db('moar',$lnk);
$result = mysql_query($sql,$lnk);
$foo = mysql_num_rows($result);
mysql_free_result($result);
mysql_close($lnk);
return $foo;
}
http://www.php.net/mysql_num_rows
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Question: alternative to crazy left joins?
Hi All,
I'm migrating to a contact relationship management system (CRM) for one of
my clients from a proprietary Access database. The CRM system can import
our donor's contact history, but only in a non-normalized format with up to
10 donations per contact record in a single row of an Excel spreadsheet.
Needless to say Access SQL doesn't seem up to the task of converting our
Donations table (individual donation indexed by ID and ContactID) to this
funky format, so I've fired up MySQL in hopes of getting this done. But I'm
not sure how to proceed. I tried using a nutty left join on ContactID but
making sure the DonatioIDs for each joined row were different, but that
didn't work (the SQL was:
select a.ContactID,a.DonationID as aID,b.DonationID as bID,c.DonationID as
cID from (fcdcTomcatsDonations as a LEFT JOIN fcdcTomcatsDonations as b ON
a.ContactID=b.ContactID) LEFT JOIN fcdcTomcatsDonations as c ON
a.ContactID=c.ContactID WHERE a.DonationID != b.DonationID AND b.DonationID
!= c.DonationID and a.DonationID != c.DonationID;
It has numerous faults:
* It's a pain to write (and I need to allow for up to 10 donations per
user!)
* It doesn't accound for different variations of the same selection (e.g.
notated as {a.DonationID,b.DonationID,c.DonationID}, {1,2,3}, {2,1,3} and
{3,2,1} are all included in the result)
I'm sure I'm betraying some fundamental SQL ignorance here, but maybe
someone out their can point me in the right direction. What I want is
something like this:
| ContactID| Donation1.* | Donation2.* | Donation3.* | etc...
Thanks in advance...off to Ruby to see if I can code some hack quick to get
this task done with...;)
-Ethan
--
View this message in context:
http://www.nabble.com/SQL-Question%3A-alternative-to-crazy-left-joins--t1357877.html#a3636912
Sent from the MySQL - General forum at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock'
On Tue, 28 Mar 2006, Áquila Chaves wrote:
To: mysql@lists.mysql.com
From: Áquila Chaves [EMAIL PROTECTED]
Subject: error: 'Can't connect to local MySQL server through socket
'/tmp/mysql.sock'
- When I execute the command mysqld_safe:
[EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql
[1] 7298 [EMAIL PROTECTED] mysql]# Starting mysqld daemon
with databases from /var/lib/mysql /* {processing...
the cursor is blinking but I don't have any answer...
So, I press ENTER} */ [EMAIL PROTECTED] mysql]#
- The log message is:
060323 16:51:11 mysqld started
060323 16:51:11 InnoDB: Started; log sequence number 0 43655
060323 16:51:11 [Note] /usr/local/mysql/bin/mysqld: ready for
connections.
Version: '5.0.19-standard' socket: '/var/lib/mysql/mysql.sock' port:
3306 MySQL Community Edition - Standard (GPL)
So you have got mysqld running OK, and waiting for you to
connect to it on the socket /var/lib/mysql/mysql.sock
- Aparently it's OK. But when I execute the command below
occurs the following error: [EMAIL PROTECTED] mysql]#
bin/mysqladmin version bin/mysqladmin: connect to
server at 'localhost' failed error: 'Can't connect to
local MySQL server through socket '/tmp/mysql.sock'
(2)' Check that mysqld is running and that the socket:
'/tmp/mysql.sock' exists!
You are trying to connect to the mysql server on a different
socket than the one mysqld is listening on for connections.
You need to tell mysqladmin to connect to the socket that
mysqld is listening to. In this case it is
/var/lib/mysql/mysql.sock.
You could do this with:
/bin/mysqladmin --socket=/var/lib/mysql/mysql.sock
That should work. If you have set a password you will need
to use that as well.
You could also set the --socket value in /etc/my.cnf by
adding a few lines to it like this:
Note that directives in the my.cnf file are the same as on
the command-line, but without the preceeding -- double-dash.
# /etc/my.cnf
#
# The following options will be passed to all MySQL clients
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306
HTH
Keith
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Keith, Thanks for answering me. Yes I did. Nothing in the MySQL log either. Keep in mind that these errors only sometimes show up and usually in the middle of a few queries in a row (while permissions are set for the whole table). Because the script runs fine most of the time, I don't think privilege settings are the key here. Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Jorrit Keith Roberts wrote: Check your mysql log and see if it says anything about not being able to use the new password format. Did run mysql_fix_privilege_tables to update your mysql passwords in the mysql privileges database? Regards Keith In theory, theory and practice are the same; in practice they are not. On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Random 'select permission denied' since upgrade to 5.0.18 Hello list, Recently we upgraded from 3.23.3 to 5.0.18 and started noticing some weird behaviour. We have update scripts running on a regular interval and some of these scripts randomily exited with MySQL error: SELECT command denied to user 'user'@'host' for table 'example' However, this error message only appears sporadically. Usually these scripts run without any problems. tcpdump shows nothing more than we already know. I see the error message appear on different queries. We have the server running on Gentoo Linux (mysql-5.0.18-r30) as a master (with one slave on another Gentoo machine). Any ideas to debug this problem any further? Thanks in advance, Jorrit By the way, there's been a previous posting to this list describing similar problems (no solution though): http://marc.theaimsgroup.com/?l=mysqlm=113050497323853w=2 -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Customer Recommendation Query
On Tue, 28 Mar 2006, Brian Erickson wrote: To: mysql@lists.mysql.com From: Brian Erickson [EMAIL PROTECTED] Subject: Customer Recommendation Query snip We are using MySQL version 3.23. There are approximately 500 unique rows in the 'actions' table and 2,000,000 rows in the member_actions table, with 3,000+ actions being recorded at any given time. snip So, our question is whether or not this is feasible with a one/few query approach, or if this is something that should be accomplished with something similar to the approach above? Can anyone provide a good start for us? I think a good start would be to consider the possiblity of upgrading from 3.23 to 5.0.18/19. I'm sure there is alot more functionality available for you to utilise then - not just in SELECT statements either. Regards Keith In theory, theory and practice are the same; in practice they are not. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Li'l problem with count()
Sandy,
$result = mysql_query($sql,$lnk);
...
Why this leads to 'ressource id #10' instead of the flies count ?
The query gives the actual count if I use it in a line command !!
You've been fooled by your variable name. For SELECT, SHOW, DESCRIBE or
EXPLAIN, mysql_query() returns a resource on success, for
other type of SQL statements (UPDATE, DELETE etc) mysql_query() returns TRUE
on success, and they both return FALSE
on error.
PB
-
Hi
pFlies in pot = ?=flies();? /p
function flies(){
$sql = 'select count(*) from flies';
$lnk = mysql_connect('localhost','root','123456');
$db = mysql_select_db('moar',$lnk);
$result = mysql_query($sql,$lnk);
mysql_free_result($result);
mysql_close($lnk);
return $result;
}
Why this leads to 'ressource id #10' instead of the flies count ?
The query gives the actual count if I use it in a line command !!
thanks
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.2/294 - Release Date: 3/27/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like max% \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Customer Recommendation Query
Brian, Let's say a member completes action 'abc'. We want to query the member_actions table for all members that have also completed action 'abc'. We then want to determine what the top 3 other actions are that were completed by members who have completed action 'abc', while making sure that only actions that have not already been completed by this member are considered. Ordered groupwise quotas. For a subquery-free two-table example see http://www.artfulsoftware.com/queries.php#18. PB - Brian Erickson wrote: We are looking for some help with queries that will accomplish a similar feature to what Amazon does. When you purchase a product, Amazon looks at all other people who have purchased that product, and then looks at all of the OTHER products those people have purchased, and uses that data to suggest related products to you. That's essentially what we are trying to do. We have 3 tables: members, actions, and member_actions. The 'members' table tracks all of our customers, the 'actions' table tracks all of the different actions each member can complete, and the 'member_actions' table is the weak entity link that tracks which actions each member have completed. Let's say a member completes action 'abc'. We want to query the member_actions table for all members that have also completed action 'abc'. We then want to determine what the top 3 other actions are that were completed by members who have completed action 'abc', while making sure that only actions that have not already been completed by this member are considered. We are using MySQL version 3.23. There are approximately 500 unique rows in the 'actions' table and 2,000,000 rows in the member_actions table, with 3,000+ actions being recorded at any given time. Is it possible to achieve this functionality with one/few queries? The statistics above may be important because if a query takes too long to execute, the server may not be physically capable of executing that query 3,000+ times simultaneously. Another option we have considered is to create a separate table called 'correlation' with two fields: action and correlated_action. We would then populate this table in a batch process following the pseudocode below. SELECT DISTINCT(action) FROM member_actions Loop SELECT DISTINCT(member) FROM member_actions WHERE action = x Loop SELECT DISTINCT(action) FROM member_actions WHERE member = y AND action x Loop INSERT INTO correlation (action, correlated_action) VALUES (x, z) Then we could easily query this table to find correlated actions like so: SELECT DISTINCT(correlated_action), COUNT(*) AS count FROM correlation WHERE action = x GROUP BY correlated_action ORDER BY count DESC This would not solve the issue of only returning actions that the member had not already completed, but that could probably be accomplished by simply joining the correlation table back to the member_actions table. So, our question is whether or not this is feasible with a one/few query approach, or if this is something that should be accomplished with something similar to the approach above? Can anyone provide a good start for us? No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.2/294 - Release Date: 3/27/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.2/294 - Release Date: 3/27/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Li'l problem with count()
thanks Because $result is a resource in PHP, not the actual result of the query; $foo = mysql_num_rows($result); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
[EMAIL PROTECTED] wrote: On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like max% \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith Keith, Here's the output: mysql show variables like max% \G *** 1. row *** Variable_name: max_allowed_packet Value: 1047552 *** 2. row *** Variable_name: max_binlog_cache_size Value: 4294967295 *** 3. row *** Variable_name: max_binlog_size Value: 1073741824 *** 4. row *** Variable_name: max_connect_errors Value: 10 *** 5. row *** Variable_name: max_connections Value: 300 *** 6. row *** Variable_name: max_delayed_threads Value: 20 *** 7. row *** Variable_name: max_error_count Value: 64 *** 8. row *** Variable_name: max_heap_table_size Value: 16777216 *** 9. row *** Variable_name: max_insert_delayed_threads Value: 20 *** 10. row *** Variable_name: max_join_size Value: 4294967295 *** 11. row *** Variable_name: max_length_for_sort_data Value: 1024 *** 12. row *** Variable_name: max_relay_log_size Value: 0 *** 13. row *** Variable_name: max_seeks_for_key Value: 4294967295 *** 14. row *** Variable_name: max_sort_length Value: 1024 *** 15. row *** Variable_name: max_sp_recursion_depth Value: 0 *** 16. row *** Variable_name: max_tmp_tables Value: 32 *** 17. row *** Variable_name: max_user_connections Value: 0 *** 18. row *** Variable_name: max_write_lock_count Value: 4294967295 18 rows in set (0.00 sec) -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
is your access control by hostname or IPnumber? if hostname you could be having transient DNS issues - where the IPnumber on the client connect can't be resolved into the permitted hostname (fast enough). try using IPnumber in the access control and see if the problem goes away - if it does you'll want to look into your inverse-map DNS issues. I checked it. It's by IP number. However, I wonder, could this be the case even after multiple queries in the same TCP session? Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table keys
create table members ( logon_idvarchar(15) NOT NULL, email_addr varchar(30) NOT NULL, member_type char(1), email_verified char(1), logon_pwvarchar(15), date_added date, last_login timestamp, count_of_logons INT, first_name varchar(30), last_name varchar(30), primary key login_id (login_id), key email_addr (email_addr)); When doing a insert row, if the logon_id value is all ready in the table I get a dup id msg. This is fine and what I want to happen. But when inserting a row with a unique logon_id value that has a email_addr that is already used by some other logon_id, mysql allows the insert. This is not the action I want. I need to be able to do lookup by logon_id or by email_addr and retrieve the row. I can do that now, but if 2 logon_id's have the same email address I get both rows. I need the email address to be unique across all rows. How can I change this table definition so email_addr is unique across all rows of the table? I read the manual about 'unique index' options, but still don't comprehend what the manual says. Also as you can see I do not select a engine type, is there some engine type better suited and or faster for the way I am trying to use the keys? Thanks for your advice and help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table keys
fbsd_user said: create table members ( logon_idvarchar(15) NOT NULL, email_addr varchar(30) NOT NULL, member_type char(1), email_verified char(1), logon_pwvarchar(15), date_added date, last_login timestamp, count_of_logons INT, first_name varchar(30), last_name varchar(30), primary key login_id (login_id), UNIQUE INDEX email_addr (email_addr)); --- When doing a insert row, if the logon_id value is all ready in the table I get a dup id msg. This is fine and what I want to happen. But when inserting a row with a unique logon_id value that has a email_addr that is already used by some other logon_id, mysql allows the insert. This is not the action I want. I need to be able to do lookup by logon_id or by email_addr and retrieve the row. I can do that now, but if 2 logon_id's have the same email address I get both rows. I need the email address to be unique across all rows. How can I change this table definition so email_addr is unique across all rows of the table? I read the manual about 'unique index' options, but still don't comprehend what the manual says. Also as you can see I do not select a engine type, is there some engine type better suited and or faster for the way I am trying to use the keys? Thanks for your advice and help. Close but you need to specify that the index is UNIQUE (see changes above) --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 [EMAIL PROTECTED] wrote: On Tue, 28 Mar 2006, Jorrit Kronjee wrote: To: mysql@lists.mysql.com From: Jorrit Kronjee [EMAIL PROTECTED] Subject: Re: Random 'select permission denied' since upgrade to 5.0.18 snip Could this message appear when, for instance, a maximum amount of threads has been spawned or MySQL has reached its connection limit? Possible - what are your settings for the relevant mysql server variables? You could try something like: show variables like max% \G I'm not really sure what all the server variables do, but they may be relevant to your problem. Keith Keith, Here's the output: mysql show variables like max% \G *** 1. row *** Variable_name: max_allowed_packet Value: 1047552 *** 2. row *** Variable_name: max_binlog_cache_size Value: 4294967295 *** 3. row *** Variable_name: max_binlog_size Value: 1073741824 *** 4. row *** Variable_name: max_connect_errors Value: 10 *** 5. row *** Variable_name: max_connections Value: 300 *** 6. row *** Variable_name: max_delayed_threads Value: 20 is this relevant ? *** 7. row *** Variable_name: max_error_count Value: 64 *** 8. row *** Variable_name: max_heap_table_size Value: 16777216 *** 9. row *** Variable_name: max_insert_delayed_threads Value: 20 ditto *** 10. row *** Variable_name: max_join_size Value: 4294967295 *** 11. row *** Variable_name: max_length_for_sort_data Value: 1024 *** 12. row *** Variable_name: max_relay_log_size Value: 0 *** 13. row *** Variable_name: max_seeks_for_key Value: 4294967295 *** 14. row *** Variable_name: max_sort_length Value: 1024 *** 15. row *** Variable_name: max_sp_recursion_depth Value: 0 *** 16. row *** Variable_name: max_tmp_tables Value: 32 *** 17. row *** Variable_name: max_user_connections Value: 0 *** 18. row *** Variable_name: max_write_lock_count Value: 4294967295 18 rows in set (0.00 sec) -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Li'l problem with count()
Hi Jay I just tried your code but I used $foo=mysql_fetch_object($result) and returned $foo-flyCount. It works the same as your code. (I actually have 738 flies in my pot, it's a crowd, and a lot of proteins !!! ;-) ). What method is heavier on the MySQL engine ? thinking count() is said to be the fastest way but since we use mysql_num_rows or mysql_fetch_object (or any other php method) aren't we doubling the procedure !? select count(*) from table stores only the count in memory which is stored in the database (as said in the help file). When we use mysql_num_rows() it counts the records again or only retrieve the count like mysql does ? . select count(*) as mycount from table stores the count as table with only one line with one column which is retrieved with mysql_fetch_xxx(). select all * from table stores the hole table in memory. Using mysql_num_rows() counts the lines in that table in memory. /thinking I think that the fastest method and the cheapest one is using select count(*) from table and using mysql_num_rows(), isn't it ? but there realy is no other way of retrieving the count() directly, without passing by the php function ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: Li'l problem with count()
[snip] I think that the fastest method and the cheapest one is using select count(*) from table and using mysql_num_rows(), isn't it ? but there realy is no other way of retrieving the count() directly, without passing by the php function ? [/snip] Actually, SELECT COUNT only returns one row, the count of the table. Do this Select count(*) AS foo from table $bar = mysql_fetch_array($result); Echo $bar['foo']; Faster and more efficient since MySQL maintains this information in an indexed sort of way. If you do Select * from table $result = mysql_query Echo mysql_num_rows($result); That counts the rows actually returned in the result set. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb errors on startup
Marten, I replied today to your earlier email with the message pasted below. Regards, Heikki Marten, can you email the complete .err log from the server to [EMAIL PROTECTED] I am interested in what caused the very first crash in the server. Now your database seems to be seriously corrupt, since the log sequence number in the log files is only 14 MB, while it is = 153 MB in a data file! Did you move around or delete ib_logfiles or ibdata files or .ibd files, or edit my.cnf while mysqld was running? Are you using some exotic file system? The output looks like ib_logfiles and data files from different servers would be mixed. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Marten Lehmann [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 28, 2006 7:49 PM Subject: innodb errors on startup Hello, I'm getting this in my errorlog: 060328 18:43:45 mysqld ended 060328 18:43:46 mysqld started 060328 18:43:46 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 060328 18:43:46 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './db16041/intradv_cms_websites.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a table created with InnoDB: CREATE TEMPORARY TABLE, and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 060328 18:43:46 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. 060328 18:43:46 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './db16041/intradv_cms_wsepgmerchant.ibd'! InnoDB: Have you moved InnoDB .ibd files around without using the InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE? InnoDB: It is also possible that this is a table created with InnoDB: CREATE TEMPORARY TABLE, and MySQL removed the .ibd file for this. InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html InnoDB: how to resolve the issue. 060328 18:43:46 InnoDB: Started; log sequence number 0 18831221 /vrmd/mysql/mysql-4.1.18/libexec/mysqld: ready for connections. Version: '4.1.18-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution How do I get rid of these messages / the errors? The page for troubleshooting didn't help. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems/feature request ideas
Sheeri, - Original Message - From: sheeri kritzer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, March 28, 2006 9:29 PM Subject: problems/feature request ideas 2 weeks ago we had a server crashing, and while I was checking it out (before, during and after crashes) I noticed that after a crash, the server was slow. Threads would run for 200 seconds or more, and yet when they finished, nothing was written to the slow query log. Why would that be? Slow query logging was on all the time, and other slow queries were written after that (anything greater than 4 seconds would be) Also, do other folks find that a deadlock log would be useful? InnoDB obviously stores it in RAM because SHOW ENGINE INNODB STATUS will show you the last deadlock information. But I feel like a deadlock log would be useful, to see how many deadlocks we get in a certain period of time (but not an averageI'm sure there are peak times, etc). SHOW DEADLOCKS is in our TODO. It would definitely be useful for users. Any ideas/comments? -Sheeri Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key creation on MyISAM storage engine
On Tuesday, 28 March 2006 at 10:09:16 +0200, Martijn Tonies wrote: Hello Taco, I previously tried this same question on the GUI tool list, but not much traffic there, so I thought I'd give it a go here. I've been reading some articles that suggest I should be able to create a relationship on a MyISAM table, it would be great if someone could confirm or deny this. has anyone experienced any problems creating foreign keys (relationships) on tables that are of storage engine MyISAM? Now, there will be people telling you that you CAN create foreign keys. Well, in a way, this is true - you can create columns with values that point to other tables. What you probably are asking, is if you can referential integrity constraints. The answer, for MyISAM tables, is NO. Currently foreign key constraints only work for InnoDB tables. We're working making foreign keys table independent, but I can't give you a completion date yet. Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpxsSKuVLoQT.pgp Description: PGP signature
Re: SP Debugger (was: Re: ANN: Database Workbench 2.8.5 released!)
On Tuesday, 28 March 2006 at 8:29:08 +0200, Martijn Tonies wrote: Hello Nick, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.5 has been released today! I'd like to hear more about the stored procedure debugger -- does all the functionality in the documentation work with MySQL? Anybody used this with MySQL, who could describe their experience with it? I would really love a good SP debugger! I'm sorry to say the debugger only works with InterBase and Firebird. The MySQL server doesn't provide debugging hooks, by itself, this is a problem for 3rd party tool vendors. InterBase or Firebird don't provide these either, but we are emulating server behaviour at the client side. Although this works for a very large part, it isn't exactly easy and there are still problems sometimes, it's hard to get perfect. This is the reason why we haven't gone that route with MySQL. What would it take on our side for you to reconsider that decision? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED] Are you MySQL certified? http://www.mysql.com/certification/ pgpBr0QFv9y1F.pgp Description: PGP signature
mac() from a subset
Hi code snippet select max(f1) as nf1 , max(f2) as nf2, max(f3) as nf3, max(f4) as nf4 from table /code How can I extract a max value from the 4 columns of the result ? ex: greatest(nf1,nf2,nf3,nf4) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Force a COMMIT on InnoDB tables?
I'm wondering if there's any way to force updates on InnoDB tables to require an explicit COMMIT when running queries from the mysql command-line client (similar to Oracle's command line client)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlcc temp table created for queries
Hi, I am using mysqlcc 0.9.4-beta to browse my DB. Server side am using 5.0.18-nt. However, when I try to double click on a table to see the data, it will always return the error. [localhost] ERROR 1146: Table 'test.1' doesn't exist The SQL query for this is SELECT * FROM `test` The query is still sucessful but I would like to know what causes this error. However, when I try to browse another server running 4.1.10-nt, double clicking produces no such error. Setup is similiar to the above. It seems like different version are doing things differently. Looking through the newsgroups forums, I understand that that mysql5.0 will prepend the tablename to each column. But I don't understand what does 'test.1' refer to. Can anyone help explain? Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql restart error
hi Dhandapani, The 3306 port is not listening. But there are some connection whose state is FIN_WAIT_2 as you can see in my first letter. After about 10 minutes I shutdowned mysql, I restarted mysql as root using: /usr/local/mysql/bin/mysqld_safe . It worked. Before it, I did this as mysql and I got the error. Regards, Leo Huang 2006/3/28, [S] Dhandapani [EMAIL PROTECTED] : Hi Leo, check for cnf file for which port you have configured the port .If it is in 3306 port then do netstat -an|grep LIST ,check for 3306 port is listening on your system .If yes you mysql process has not shutdown properly. shutdown the mysql process completely and start the mysql process by specifying your datadirectory. /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --datadir=/usr/local/mysql/data/ --user=mysql port= 3306 socket = /tmp/mysql.sock Regards, Dhandapani leo huang wrote: hi, Lakshmi The mysql process had ended. I get it from both mysql err log and ps output. regards, Leo Huang 2006/3/28, Lakshmi M P [EMAIL PROTECTED] [EMAIL PROTECTED]: Run ps -ef | grep mysql and see any mysql process is running and if so kill the same and try to start mysql.It may help. leo huang wrote: hi, I met the MySQL restart error today. First, I stopped the running mysql server using /usr/local/mysql/bin/mysqladmin -uroot shutdown. After the server shutdowned, I restarted it using /usr/local/mysql/bin/mysqld_safe . Then, I got this error: [ERROR] Can't start server: Bind on TCP/IP port: Address already in use. There was no other process that was using the port 3306 which mysql server use. But there were some mysql connect did not release because the shutdown. The error log is followed: 060328 8:20:45 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown 060328 8:20:47 InnoDB: Starting shutdown... 060328 8:20:49 InnoDB: Shutdown completed; log sequence number 120 2134241340 060328 8:20:49 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete 060328 08:20:49 mysqld ended 060328 08:21:15 mysqld started 060328 8:21:15 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 060328 8:21:15 [ERROR] Do you already have another mysqld server running on port: 3306 ? 060328 8:21:15 [ERROR] Aborting 060328 8:21:15 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete 060328 08:21:15 mysqld ended The netstat outputs are followed: $ netstat -al Active Internet connections (including servers) Proto Recv-Q Send-Q Local Address Foreign Address(state) tcp4 0 0 bj.3306 s4.9405 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.5168 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.25007 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.9940 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.3916 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.15229 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.6479 FIN_WAIT_2 tcp4 0 0 bj.3306 s4.7873 FIN_WAIT_2 Our mysql version is 4.1.18. It is running on FreeBSD 4.9-RELEASE. Any comment will be great thankful! Regards, Leo Huang -- regards, Lakshmi.M.P. DBA-Support Sify Limited. Ext:4134 ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] www.sify.com - your homepage on the internet for news, sports, finance, astrology, movies, entertainment, food, languages etc
Multiple-Master Replication recovery
Hello: If I use Multiple-Master Replication with two mysql server, when one of them goes down(disk crashed) must I shutdown the good one to recover the Multiple-Master Replication ? regards, gu lei
Re: SQL Question: alternative to crazy left joins?
eth1 [EMAIL PROTECTED] wrote on 03/28/2006 03:04:13 PM:
Hi All,
I'm migrating to a contact relationship management system (CRM) for
one of
my clients from a proprietary Access database. The CRM system can
import
our donor's contact history, but only in a non-normalized format with up
to
10 donations per contact record in a single row of an Excel spreadsheet.
Needless to say Access SQL doesn't seem up to the task of converting our
Donations table (individual donation indexed by ID and ContactID) to
this
funky format, so I've fired up MySQL in hopes of getting this done. But
I'm
not sure how to proceed. I tried using a nutty left join on ContactID
but
making sure the DonatioIDs for each joined row were different, but that
didn't work (the SQL was:
select a.ContactID,a.DonationID as aID,b.DonationID as bID,c.DonationID
as
cID from (fcdcTomcatsDonations as a LEFT JOIN fcdcTomcatsDonations as b
ON
a.ContactID=b.ContactID) LEFT JOIN fcdcTomcatsDonations as c ON
a.ContactID=c.ContactID WHERE a.DonationID != b.DonationID AND
b.DonationID
!= c.DonationID and a.DonationID != c.DonationID;
It has numerous faults:
* It's a pain to write (and I need to allow for up to 10 donations per
user!)
* It doesn't accound for different variations of the same selection
(e.g.
notated as {a.DonationID,b.DonationID,c.DonationID}, {1,2,3}, {2,1,3}
and
{3,2,1} are all included in the result)
I'm sure I'm betraying some fundamental SQL ignorance here, but maybe
someone out their can point me in the right direction. What I want is
something like this:
| ContactID| Donation1.* | Donation2.* | Donation3.* | etc...
Thanks in advance...off to Ruby to see if I can code some hack quick to
get
this task done with...;)
-Ethan
--
View this message in context: http://www.nabble.com/SQL-Question%3A-
alternative-to-crazy-left-joins--t1357877.html#a3636912
Sent from the MySQL - General forum at Nabble.com.
The correct way to model your information is to use the method you
describe as being used in the Access database. That data is normalized.
You even admit that you are denormalizing the data. The new CRM system is
imposing an artificial limit of 10 donations to any single contact. What
real-world rule says that after 10 donations, the contact is done? Or,
what real-world rule says to ignore the 11th or older contribution? These
artificial limit of only 10 donations in the donation history would be a
deal breakers for me.
Basically, the new design breaks several of the fundamental rules of
efficient database design. I would seriously doubt the capabilities of the
new system if this is how the backend is organized. I worry for your
client.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Force a COMMIT on InnoDB tables?
patrick wrote: I'm wondering if there's any way to force updates on InnoDB tables to require an explicit COMMIT when running queries from the mysql command-line client (similar to Oracle's command line client)? set autocommit = 0 See http://dev.mysql.com/doc/refman/5.0/en/innodb-and-autocommit.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with UTF and MySQL
Yes you are right it works... but if the documentation is wrong I will be
wrong in the following also...
Majority of SET statements are documented without quotes:
AUTOCOMMIT = {0 | 1}
BIG_TABLES = {0 | 1}
FOREIGN_KEY_CHECKS = {0 | 1}
IDENTITY = value
INSERT_ID = value
LAST_INSERT_ID = value
SQL_AUTO_IS_NULL = {0 | 1}
SQL_BIG_SELECTS = {0 | 1}
SQL_BUFFER_RESULT = {0 | 1}
SQL_LOG_BIN = {0 | 1}
SQL_LOG_OFF = {0 | 1}
SQL_LOG_UPDATE = {0 | 1}
SQL_QUOTE_SHOW_CREATE = {0 | 1}
SQL_SAFE_UPDATES = {0 | 1}
SQL_SELECT_LIMIT = {value | DEFAULT}
SQL_WARNINGS = {0 | 1}
TIMESTAMP = {timestamp_value | DEFAULT}
UNIQUE_CHECKS = {0 | 1}
Even the:
CHARACTER SET {charset_name | DEFAULT}
I'm not talking here about setting a string user variable without quotes...
that would be absurd... but reaching to
SET NAMES this one is the only one documented *WITH quotes*:
NAMES {'charset_name' | DEFAULT}
And also its pointed that with a note:
Note that the syntax for SET NAMES differs from that for setting most
other options.
Also in the manual on chapter: *10.4. Connection Character Sets and
Collations*
there is written:
There are two statements that affect the connection character sets:
SET NAMES '*charset_name*'
SET CHARACTER SET *charset_name*
Again the SET NAMES with quotes anything else without !
So... dear writers of MySQL... or DOCs... what's the catch ?
And... of course I'll use it with quotes ... until further notice !
--
Gabriel PREDA
Senior Web Developer
On 3/28/06, Adam i Agnieszka Gąsiorowski FNORD [EMAIL PROTECTED] wrote:
One must issue immediately after connection:
SET NAMES 'utf8'
IMO, it is OK to say that without the quotes :-} {-:
