Re: multiple table query
Justin French wrote:
Can anyone help me optimise the way I do this?
I have two tables, 'article' and 'user'. Article has a user_id which
related to the user table.
When selecting all articles from the the article table, I'd like to be
able to get the username of the user_id. Currently I'm doing this as a
separate query whilst looping through the articles, but this is
obviously causing way too many DB calls.
Yeah, you don't want to do that.
some pseudo code:
SELECT * FROM article
foreach article
{
SELECT username FROM user WHERE id=$user_id
}
Perhaps I need a join, or maybe just a more complex query -- can any one
lend a hand?
Yes, you need a join.
My guess is maybe something like
SELECT article.title, article.user_id, user.username
FROM article, user
WHERE user.id = article.user_id
Good guess. Have you tried it? That should do exactly what you want. You
could make it slightly shorter to type by using table aliases, like this:
SELECT a.title, a.user_id, u.username
FROM article a, user u
WHERE u.id = a.user_id;
See the manual for details on SELECTs
http://dev.mysql.com/doc/mysql/en/SELECT.html and JOINS
http://dev.mysql.com/doc/mysql/en/JOIN.html.
---
Justin French
http://indent.com.au
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I have a problem with MySQL.
If you give more details maybe someone on the list will be able to help. Astghik Barseghian wrote: Hello Philippe, I am trying to use DBMail from MySQL to send and receive messages but I get an SQL sytax error. Could you please tell me is there any way to use the mentioned component, or how can I execute the dll function from MySQL. Thank you. -- http://www.freenet.am/ -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PRODUCT() function - calculating the product of grouped numeric values
Hi, you might take a look at this thread: http://lists.mysql.com/mysql/166184 /Johan Hi, I'm searching for a function that enables me to calculate the product of a group of values, like SUM() does. MySQL server version is at least 4.0.14. For example, a query like SELECT id, PRODUCT(value) AS 'product' FROM table GROUP BY id for this table -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cygwin and High School Lab
A couple of people have wondered why I'm using Cygwin with MySQL and seem to think I'm trying to use the Linux version of MySQL on Windows. I've installed the latest Windows version of MySQL. I'm using Cygwin as the launcher rather than the Windows command prompt, mostly because it's easier for me to set up environment variables correctly, to run SSH, Tomcat, etc. from a Unix-y prompt rather than a DOS prompt. Since when I type mysql it's actually mysql.exe that gets run, I don't think Cygwin could be the culprit in the crash, but I'm going to try running MySQL from the Windows command prompt today to see if that makes a difference. Thanks, Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
for not working mysql on my computer
repspected sir my self bunty chunara, i am doing the project (for database i have used mysql) i have downlaod it from site but it, does not wok on my computer. i have install it and when i open the mysql from the dos promt it say that access deined at local host. please sir give me proper informmation about the installing working of the mysql.and whic connector should i used to connect the asp pages with the mysql. if any driver did you know then please send the address of it. sir if the mysql that work properly or any instruction of it, then please give me address of it or forward me reply message on this email address [EMAIL PROTECTED] your faithfully bunty chunara Yahoo! India Matrimony: Find your life partner online Go to: http://yahoo.shaadi.com/india-matrimony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PRODUCT() function - calculating the product of grouped numeric values
Hi, you might take a look at this thread: http://lists.mysql.com/mysql/166184 Thanks a lot - perfect. I was searching for a mathematical function too, but I didn't cope it... this version works great! Best regards, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with mysqldump between two servers
Hello, I did a mysql dump of a database between two servers we have using the following command: mysqldump --opt db_name -u username --password=password | mysql --host=hostname -C db_name -u username --password=password This command seemed to run ok as it transferred all the structure and data from the original server to the new server database. However, on closer inspection there is a problem. Many characters in the new database are showing up as a a question mark ? (e.g. characters such as , trademark symbol, and fractions such as 1/3). If I edit this data using phpmyadmin and replace it with the relevant symbol manually then it is ok. The difference between versions of mysql on both servers are not that different: 3.23.58 - new server 3.23.52 - old server I got the status information when I logged into mysql on both servers, and it showed that they are both using the same character sets. Client characterset:latin1 Server characterset:latin1 According to the documentation, mysqldump in these versions dumps using latin1 character set by default. I did a dump of the database locally on the old server using the following command: mysqldump --opt db_name -u username --password=password | mysql -C db_name_bak -u username --password=password I made a copy of the web site and pointed it at db_name_bak and all the characters in question displayed ok. So it does not look like a problem with the mysqldump output. It does look like there is some configuration issue on the new server regarding mysql, or maybe even php or apache, that is causing this. Or could there be some configuration in between the versions of Linux on the two servers. The old server is running red hat 7.3. The new server is running centos Any help would be appreciated. Thanks, Regards, Eugene. -- Eugene van den Hurk Rocket Media 118 Patrick St. Cork. http://www.rocketmedia.ie Tel: +353 21 4279517
db design for hosting site
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I have a web hosting picture gallery service at http://web-folio.net/ and am thinking about how to do the next version, which will store the media files in the filesystem but pretty much every thing else in a database (like it should be :-) I wonder about table design -- amongst other things, of course. Each customer will have some internal information (billing and contact info, etcetc) as well as external information (who s/he is, contact info, etcetc) and then will have metadata (comments, time/date, etcetc) about the pictures. One thing I want to offer is the ability to load balance / mirror a site on another of our servers. Another that might be neat is the ability to export/expose a certain set of one's files to someone else to import to share content. Other than that, as far as I can see each customer will be unique on each host and unique across the enterprise. Since I want the ability to mirror, it seems that I'll probably want one single DB replicated across my hosts so that comments and so on stay up to date (I still haven't crossed the bridge of how to keep the library itself in sync thru something like unison or rsync, but I do know that I really don't want to keep the files in the DB itself). I'm open to ideas of why I wouldn't, though. My real question comes down to table layout. Given the customer data as above (in much more detail, of course), I'm not sure whether I want a table structure for each customer (presumably instantiated as part of the site setup, but perhaps created by the code in a check this first before you do anything section) or a single large table structure. The latter seems more straightforward to me, and since it's a DB it shouldn't matter if the 'pictures' table (or the like) gets to be a million rows, but what I don't know about DB design would fill a book :-) This is a talkative list and I wouldn't be surprised to find lots of chatter back and forth on the matter, but I'll summarize what I learn and/or decide when the conversation or direct emails wind down. TIA HAND :-D - -- David T-G [EMAIL PROTECTED] http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (FreeBSD) iD8DBQFBNFRwGb7uCXufRwARAsqrAKCfPPhC56rMGoza4dsz1rB4xN4IUgCeNRBC 0frePa4NG3sEv57ysVDsfAo= =tHet -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: db design for hosting site
Since I want the ability to mirror, it seems that I'll probably want one single DB replicated across my hosts so that comments and so on stay up to date (I still haven't crossed the bridge of how to keep the library itself in sync thru something like unison or rsync, but I do know that I really don't want to keep the files in the DB itself). I'm open to ideas of why I wouldn't, though. Well, putting the files themselves in the database would solve your replication problem :-) All in one, one in all... My real question comes down to table layout. Given the customer data as above (in much more detail, of course), I'm not sure whether I want a table structure for each customer (presumably instantiated as part of the site setup, but perhaps created by the code in a check this first before you do anything section) or a single large table structure. IMO, a single table structure. Unless you want to give each customers its own _database_ (not just tables in the same database). The latter seems more straightforward to me, and since it's a DB it shouldn't matter if the 'pictures' table (or the like) gets to be a million rows, but what I don't know about DB design would fill a book :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.4 has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.1.4, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a new gamma development release, adding new features and fixing recently discovered bugs. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Made internal representation of TIMESTAMP values in InnoDB in 4.1 to be the same as in 4.0. This difference resulted in wrong datetime values in TIMESTAMP columns in InnoDB tables after upgrade from 4.0 to 4.1. (Bug #4492) Warning: extra steps during upgrade required! Unfortunately this means that if you are upgrading from 4.1.x, where x = 3, to 4.1.4 you should use mysqldump for saving and then restoring your InnoDB tables with TIMESTAMP columns. * The mysqld-opt Windows server was renamed to mysqld. This completes the Windows server renaming begun in MySQL 4.1.2. See section Selecting a Windows Server in the manual. * Added Latin language collations for the ucs2 and utf8 Unicode character sets. These are called ucs2_roman_ci and utf8_roman_ci. * Corrected the name of the Mac OS X StartupItem script (it must match the name of the subdirectory, which was renamed to MySQLCOM in MySQL 4.1.2). Thanks to Bryan McCormack for reporting this. * Added --start-datetime, --stop-datetime, --start-position, and --stop-position options to mysqlbinlog. These make point-in-time recovery easier. * Killing CHECK TABLE no longer results in the table being marked as corrupted; the table status remains the same as it was before CHECK TABLE started. See section KILL Syntax in the manual. * Made the MySQL server ignore SIGHUP and SIGQUIT on Mac OS X 10.3. This is needed because under this OS, the MySQL server receives lots of these signals (reported as Bug #2030). Bugs fixed: * Fixed a bug that caused libmysql to crash when attempting to fetch a value of MEDIUMINT column. (Bug #5126) * Fixed a bug that caused the MySQL server to crash when attempting to execute a prepared statement with SELECT ... INTO @var for a second time. (Bug #5034) * Fixed execution of optimized IN subqueries that use compound indexes. (Bug #4435) * Prohibited resolving of table fields in inner queries if fields do not take part in grouping for queries with grouping (inside aggregate function arguments, all table fields are still allowed). (Bug #4814) * Fixed a crash after SLAVE STOP if the IO thread was in a special state. (Bug #4629) * Fixed an old bug in concurrent accesses to MERGE tables (even one MERGE table and MyISAM tables), that could have resulted in a crash or hang of the server. (Bug #2408) * Fixed a bug that caused server crash on attempt to execute for a second time a prepared statement with NOT in WHERE or ON clauses. (Bug #4912) * MATCH ... AGAINST now works in a subquery. (Bug #4769) * Fixed a bug that omitted the `.err' extension of the error log file (--log-error) when the hostname contained a domain name. The domain name is now replaced by the extension. (BUG #4997) * Fixed a crash in myisamchk. (Bug #4901) * Fixed a bug which caused server crash if one used the CONVERT_TZ() function with time zone described in database as parameter and this time zone was not used before. (Bug #4508) * Support for %T, %r, %V, %v and %X, %x format specifiers was added to STR_TO_DATE() function. (Bug #4756) * Fixed a bug (hang) in NATURAL JOIN where joined table had no common column. (Bug #4807) * Fixed a crash caused by UNHEX(NULL). (Bug #4441) * mysql_fix_privilege_tables didn't correctly handle the argument of its --password=# option. (Bug #4240, Bug #4543) * Fixed that mysqlbinlog --read-from-remote-server sometimes couldn't accept 2 binary logs on command line. (Bug #4507) * Fixed that mysqlbinlog --position --read-from-remote-server had wrong # at lines. (Bug #4506) * If CREATE TEMPORARY TABLE t SELECT failed while loading the data, the temporary table was not dropped. (Bug #4551) * Fixed that when a multiple-table DROP TABLE failed to drop a table on master, the error code was not written to the binary log. (Bug #4553) * When the slave SQL thread was
(innodb_lock_wait_timeout = 0) == 1
Hi all, do you know how to set innodb_lock_wait_timeout = 0? Why is it to be 0? Regards, TomH -- PROSOFT EDV-Loesungen GmbH Co. KGphone: +49 941 / 78 88 7 - 121 Ladehofstrasse 28, D-93049 Regensburg cellphone: +49 174 / 41 94 97 0 Geschaeftsfuehrer: Axel-Wilhelm Wegmann [EMAIL PROTECTED] AG Regensburg HRA 6608 USt.183/68311http://www.proSoft-Edv.de -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication config without stopping master server
Hi All : I am still waiting for the response. Thanks in advance. Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:17 PM Subject: Re: replication config without stopping master server The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This machine is as a replication master server. I have configured the other machine as replication slave with same version of MySQL and OS. Now, I want to start replication slave server without stopping the master server. Is it possible? What I did,-- I configured the slave server and started the slave replication. Also gave the command LOAD DATA FROM MASTER. This starts replication but the log files (localhost-relay-bin.xxx) taking to much space and the disk being 100% full. The database is InnoDB. Actual data for replication=1500MB (at master replication server) Disk space available=25GB (at slave server) After starting the slave the logfiles taking 24.5GB and only 0.5GB the actual data. What is wrong with my configuration?? I have the limitation that I have not to stop the master replication server. Please suggest me the solution. Regards Naveen -- 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: Ok sir
I think it is best if MySQL questions are discussed on the mailing list rather than privately so that everyone can benefit from them. Originally, I did not understand your question so I replied to get you to make it clearer. Now that you have clarified your question, I find that I don't know the answer myself. However, some of the others on the others on this mailing list will probably be able to help you now. Rhino - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 4:17 AM Subject: Ok sir Good day sir sorry that i passed an undifined question. i actualy wanted to know if there was further configurations that i need to do so as to get through the version of the mysql is mysqladmin ver 1.4 and the os am using is win 2000 pro. actually the mysql is running but then i had php 5 so i thought of using macromedia flash mx and i need to querry mysql. so that php can do the querrying and sending the data to flash. sorry if this is bringing up any miss confort to you i just thought you could help. Any thing you say to me might go along way than you thought that is why i need you to say something to me thanks Benjamin _ FindLaw - Free Case Law, Jobs, Library, Community http://www.FindLaw.com Get your FREE @JUSTICE.COM email! http://mail.Justice.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump
Hi, I want to have an automatic backup done of my SQL databases, but cant quite figure out how to use mysqldump to do this properly... Ideally, I would like the database backed up, then FTP'd to my home server Can someone lend me a hand with this please! Thanks! Sparks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Licensing issues
To all: I'm a bit confused. After reading this mailing list, and licensing issues on mysql, a question came up to me Since I'm trying to implement a new database replacing Access Database for intranet environment at my work, do I still need a license since I am not exporting my works out of my company. Or is it safer to buy license to avoid the possible legal disputes? These legal stuffs blew my mind away, it is now in the dark, not understanding the legal issues. Can anyone enlighten this? By the way, I plan to implement MySQL production version into Windows 2000 platform running Apache and Php. Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concat
Hi, Concat() returns Null if any field in the field list is Null. How can I use it to return a String regardless, or is there another function to do it? regards ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Licensing issues
On Tue, 31 Aug 2004 08:23:01 -0400, Scott Hamm [EMAIL PROTECTED] wrote: Since I'm trying to implement a new database replacing Access Database for intranet environment at my work, do I still need a license since I am not exporting my works out of my company. If you don't redistribute, you don't need a license. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat
select concat(ifnull(Field1,'NULL'), Filed2) from My Table; - Original Message - From: A Z [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 2:25 PM Subject: Concat Hi, Concat() returns Null if any field in the field list is Null. How can I use it to return a String regardless, or is there another function to do it? regards ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concat
Concat() returns Null if any field in the field list is Null. How can I use it to return a String regardless, or is there another function to do it? COALESCE(value,...) Returns the first non-NULL value in the list. mysql SELECT COALESCE(NULL,1); - 1 mysql SELECT COALESCE(NULL,NULL,NULL); - NULL COALESCE() was added in MySQL 3.23.3. So, if you use CONCAT(COALESCE(Myfield, ''), COALESCE(OtherField, '') ) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With MySQLdump needed!!
Hi, I want to have an automatic backup done of my SQL databases, but cant quite figure out how to use mysqldump to do this properly... Ideally, I would like the database backed up, then FTP'd to my home server Can someone lend me a hand with this please! Thanks! Sparks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem running MySQL in high school lab
I second Todd's suggestion. There is no need to run Cygwin in order to have the MySQL servers or clients or any of its tools operating under Win32 (95, 98, 2000, ME, 2003, XP, etc). I only ever use Cygwin when I need to run a remote Linux desktop and as a teaching tool. Everything else I do with MySQL is platform specific. If I am on a *nix platform, I use *nix tools. If I am on Win32, I use Win32 tools. The differences are minimal and the stability is well worth it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Donny Simonton [EMAIL PROTECTED] wrote on 08/30/2004 09:37:26 PM: Todd, I don't use Windows XP as a production machine, but I do run MySQL on my personal machine running Windows XP, I run the Windows version of MySQL. Is there any reason that you are using Cygwin to run MySQL when you can run the MySQL windows binaries without any problems? The only thing I can think of is you are trying to teach them linux as well. I know in the MySQL training classes offered by MySQL they are always taught using Windows 2000 or XP, and they use the standard MySQL windows installer. Donny -Original Message- From: Todd O'Bryan [mailto:[EMAIL PROTECTED] Sent: Monday, August 30, 2004 8:09 PM To: [EMAIL PROTECTED] Subject: Problem running MySQL in high school lab I'm trying to teach my students how to use MySQL, and have installed it on all the lab machines along with Cygwin. Originally, I had the permissions set wrong and my students couldn't start the server, but I fixed that, and now mysqld works fine. Unfortunately, if you then mysql -u root, after a rather short period of time, the program crashes and pops an error message to the screen. The message, which I should have written down but didn't, says that an assertion has failed in ftell.c (not sure about the filename, but the gist is right) and stream != NULL (that I'm sure of) and then the program dies. I don't have similar problems when I'm logged in as me (which has Administrator privileges) or the machine Administrator. It must be a permissions problem, but I don't know what I need to give the students to prevent it. The MySQL stuff on the local machines need not be secure, so I've given full access to all users in the entire /cygwin/usr/local/ directory and its subdirectories, which is where I installed MySQL and all the other packages we're going to be playing with. There are some kids in there who don't need the temptation of being logged in as an Administrator, and since we're going to be using JDBC later for which the MySQL server will need to be running almost constantly in the background, I'd like to get this resolved with the least amount of temptation. The lab is all Windows XP Professional machines, and the students log into a domain hosted by a server in another teacher's lab. Any ideas appreciated, Todd P.S. If you could cc me any replies, I'd appreciate it, since I read the list on digest. -- 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: Tricky Date Query
Michael, Michael Stassen wrote: Mike Blezien wrote: Hello, Not sure this is possible to do with SQL or if needs to be done with in the application programming language we're using(Perl), but I need to get the a weekdate, IE SELECT EXTRACT(DAY FROM NOW()) which would return 30 Now what I need to do, is if the query above is greater or less then 15, get the next month or current month date for the 15th. IE if I ran this query today, and the value is 30(15), I need to generate the date 2004-09-15. If the query was run on 2004-09-14(15) generate the date 2004-09-15, if the query was run on 2004-12-25(15), generate the date for 2005-01-15 Is this possible to do with MySQL ?? Thx's Mickalo Yes. How about SELECT IF(DAYOFMONTH(CURDATE()) = 15, CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-15'), CONCAT(YEAR(CURDATE() + INTERVAL 1 MONTH), '-', MONTH(CURDATE() + INTERVAL 1 MONTH),'-15')) AS next15; or better yet SELECT IF(DAYOFMONTH(CURDATE()) = 15, DATE_FORMAT(CURDATE(), '%Y-%m-15'), DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15; works like a charm, thx's :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With MySQLdump needed!!
-Original Message- From: Sparks [mailto:[EMAIL PROTECTED] I want to have an automatic backup done of my SQL databases, but cant quite figure out how to use mysqldump to do this properly... I use a script that I run as a nightly cron job. Here's the one I use: --- #!/bin/sh DATE=`date +%Y%m%d` cd /var/lib/mysql-backup /usr/bin/mysqldump --user=backup --password=Password --quick --lock-tables --all-databases | gzip mysql.$DATE.gz cd /var/lib/mysql-backup/logs mv update.log update.$DATE.log /usr/bin/mysqladmin --user=backup --password=Password flush-logs gzip update.$DATE.log --- Obviously you'd want this to be readable only by root, since it contains passwords. The backup user will need global SELECT and RELOAD privilages. You could easily modify this to FTP or SCP the files when they're done. If you don't care about the update logs you can simplify a bit and eliminate the RELOAD privilage, but I personally find them useful to have. (With a backup and the update log that follows it, you can restore to any point by restoring the backup, editing the log, then rerunning it against the server.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relay or Master Binary Lod Corruption.
Hi all, Last week I got this message below.. 040829 19:38:43 Slave SQL thread: I/O error reading event(errno: -1 cur_log-e rror: 11)/usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.18-standard-log' socket: '/tmp/mysql.sock' port: 3306 040829 19:38:43 Error reading relay log event: Aborting slave SQL thread becaus e of partial event read 040829 19:38:43 Slave: Could not parse relay log event entry. The possible reas ons are: the master's binary log is corrupted (you can check this by running 'my sqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary lo g or slave's relay log, you will be able to know their names by issuing 'SHOW SL AVE STATUS' on this slave. Error_code: 0 040829 19:38:43 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'www-bin .005' position 633703721 I have 2 mysql daemons running in slave mode and I got his message on only one of them. So I thought that the corruption was based on the relay.. So What I did was to CHANGE MASTER TO an after possition that the problem occur. So I tried some values but nothing changed. So As a solution I shut down the master (I was not willing to do this but I had to) and take a snapshot and rebuild the master again.. Ans started the slave from the new master binary log created.. So this solved my problem but Actually What should I do in a situation like this if it happens again? Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql and errno 13 on RedHat
Hi there, I need to install a database on a RedHat system. Although I'm pretty at ease with Debian, I feel a little unconfortable and cannot find why I get an Error: errno 13 (Permission Denied) when I try to create a new database. Is there some place to look at first? I cannot find any way to do this easily. There is no strace installed on the system (nor any possibility to do so easily). Any idea? Thanks, Yannick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help With MySQLDump needed!!
Sparks, I'm not sure why but when I try to reply to your question, Outlook Express thinks it is a newsgroup post, not an email, and insists on knowing the newsgroup name to which I should send my answer. Obviously, that is bogus so I am responding with a new note. Anyway, here's my reply to your question. You didn't mention what operating system you are using or what version of MySQL you have. I am running MySQL 4.0.15 on Linux Mandrake 8.1. This is a bash script that I wrote for myself to do automated backups. It doesn't include the FTP stuff that you wanted but I'll leave that up to you and/or the others on this mailing list ;-) Unfortunately, the script wraps when I cut and paste it so I've put it in afile attachment for you (and anyone else who wants it). I commented the code fairly heavily when I wrote it so it should be more-or-less self-explanatory if you know bash. However, feel free to post followup questions if you need anything explained to you. You'll need to replace the question marks in the USERID and PASSWORD variables with your own values; likewise, you will want to modify the BACKUP_PATH variable to identify where you want your backups written and set NUMBER_OF_DAILY_BACKUPS_TO_KEEP to the number you want. You should probably leave BACKUP_TIMESTAMP alone. The echo statements are just so that I have a nice report that tells me exactly what is happening; you can omit them if you don't want the report. The 'for' statement simply gets a list of all of the database names on my copy of MySQL; the 'do' loop that follows makes a mysqldump backup of the database, then backups older than 7 days are displayed and then deleted. I've been using this without problems for a year or so. Rhino---rhino1 AT sympatico DOT ca"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies." - C.A.R. Hoare tech.gif-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: db design for hosting site
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Martijn, et al -- ...and then Martijn Tonies said... % % Since I want the ability to mirror, it seems that I'll probably want one ... % really don't want to keep the files in the DB itself). I'm open to ideas % of why I wouldn't, though. % % Well, putting the files themselves in the database would solve % your replication problem :-) Well, that's true, and I thoguht of that :-) % % All in one, one in all... But hasn't this gone around the list a dozen times and, while either will work, isn't the general feelign that the best idea is to keep those big files (pictures, documents, music, movies, ...) out on the filesystem rather than inside the database? Sure, small data (comments, for instance, as you'll have noted) is great in the DB, but stuff as big as that just clogs up the DB and requires an extra export step to get out anyway. Um, right? Besides, this way I can leave the quota restrictions to the OS :-) % % My real question comes down to table layout. Given the customer data as % above (in much more detail, of course), I'm not sure whether I want a ... % you do anything section) or a single large table structure. % % IMO, a single table structure. Unless you want to give each customers % its own _database_ (not just tables in the same database). I don't nkow that I want to do that, though that's an interesting idea that hadn't occurred to me. So for the moment I'm still looking at a single table structure. Good enough... % % With regards, % % Martijn Tonies Thanks HAND Still listening... :-D - -- David T-G [EMAIL PROTECTED] http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (FreeBSD) iD8DBQFBNJfkGb7uCXufRwARAnssAJ9a+7WrAR7rzT/Mif7uIpdJBneDuwCgpfn4 2+dV7a6pA1qzJ6vzuEbYlo0= =lbRt -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and errno 13 on RedHat
Error 13 Permission Denied means that the mysqld process doesn't have permission by the filesystem to create whatever files/directories it needs to in the dir you have listed as datadir. Make sure mysqld is running as the right user (ps aux | grep mysqld) or chmod -R the datadir so mysqld can write to it. -Eric On Tue, 31 Aug 2004 16:00:39 +0100, Yannick Warnier [EMAIL PROTECTED] wrote: Hi there, I need to install a database on a RedHat system. Although I'm pretty at ease with Debian, I feel a little unconfortable and cannot find why I get an Error: errno 13 (Permission Denied) when I try to create a new database. Is there some place to look at first? I cannot find any way to do this easily. There is no strace installed on the system (nor any possibility to do so easily). Any idea? Thanks, Yannick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSERT IGNORE like feature for rows failing foreign key constraints?
I never got a reply for this, and I'm still trying to figure out the best way to handle it. Anyone? John A. McCaskey -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 2:17 PM To: [EMAIL PROTECTED] Subject: INSERT IGNORE like feature for rows failing foreign key constraints? I have a logging table where I insert a large number of rows every 5 minutes. For performance reasons this occurs in bulk inserts of about 5000 rows at a time. (ie. INSERT INTO table VALUES(...), (...), (...)) One of the fields in the table is an id that connects it to another table. It is possible that by the time the insert occurs (they queue up in memory briefly before I create the bulk insert) a separate process has deleted the entry in the parent table and the id is invalid. When this happens right now the entire insert of 5000 rows fails because one single row is bad. I want the behavior to be that the one fails silently and the other 4999 insert successfully. Any ideas how I can do this? It seems like INSERT IGNORE would make sense but that appears to only ignore duplicates not foreign key failures. John A. McCaskey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Permission System
Hi all, I have a need to use a kind of user permission system in the database, but because the database is a hosted solution, I do not have access to the real user tables and permissions and such. SO... What we're talking about is a document management system: 1. Some users can read, write, and add documents 2. Some users can only read documents 3. Some users cannot do any of the above I need to implement some kind of table that allows these things. CREATE TABLE MemberPermissions ( MemberID bigint NOT NULL auto_increment, canRead tinyint NOT NULL default 0, canWrite tinyint NOT NULL default 0, canInsert tinyint NOT NULL default 0, PRIMARY KEY (MemberID) ) TYPE=MyISAM; where the canRead, canWrite, and canInsert are Boolean flags of 0 or 1. Clearly, I will need to implement a login system. With this design, it appears that there will either need to be two queries - one to check the permissions, and the second to perform the action requested. I suppose the other thing I could do is query the permissions on login and cache that info in session data while the user is logged in. I had another thought to assign permissions to groups, and then assign a group to a user. Would this be easier and more manageable? I am looking for feedback as to whether this is an appropriate design, or perhaps some suggestions for a better design, pitfalls to watch out for, etc. Best regards, -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about [mysql defunct] processes
Subject: Re: Question about [mysql defunct] processes Ken Sorensen [EMAIL PROTECTED] wrote: I am stumped. I have a very busy MySQL db that we've run for about 3 years with no problems. A couple of months ago we upgraded the system to RH-3AS with RedHat's distro mysql-server 3.23.58-1.9. Recently I've started getting zombie processes ([mysql defunct]). Remove the RedHat's distro mysql-server and install properly built MySQL binary version in RPM format. Download it from www.mysql.com. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com Hi Egor, Thanks for your reply. I realized after I posted, I was to ambiguous with my question. Sorry for that. I did change out the MySQL Distro., and as I suspected MySQL is not the problem! Neither was the OS. I cannot use a statically compiled Distro. because we've added functions to this implementation. This system does about 28 queries per second from DBD-Mysql, ODBC, remote mysql connections, etc. About any way you can connect to it, some process does. I used 'lsof' on the command line and found the processes in question were linked to pipes from MySQL to the OS. I found my defunct process! When we first built this implementation, we added a function that interacted with the OS. This added function worked from any query I sent it, but it intermittently threw out defunct processes. I recompiled this function using the current OS compilers and added it again to MySQL. When I tried to remove the function from MySQL, I received errors. I had to remove it from the mysql.func table directly. I added the function shared lib to my OS, used 'ldconfig', and used the 'CREATE function ...' in MySQL. That function is used about 100+ times a day in certain queries, and so far, I have no more defunct processes. It has been almost a week now under normal operating conditions. I consider this problem solved. Thanks Egor for your post, and your willingness to help me. Ken -- _ Ken Sorensen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
specify data type in select statement
We've run into a problem where binary char column types crash our c++ application when migrating from RedHat 7.3 to RedHat ES. Is there a way to specify the data type to be returned in the select statement. In the below example, selecting office_id will crash the app if it's left as a binary column. I'm looking for something like select charoffice_id from office; create table office ( office_id char(8) binary not null, office_name varchar(50) ); Thanks! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia If it's not broketweak it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication config without stopping master server
Naveen, You don't need to shut down the master but you have to stop writing to the database for taking the snapshot. Follow the description in the manual: 3. If you are using MyISAM tables, flush all the tables and block write queries by executing `FLUSH TABLES WITH READ LOCK' command. mysql FLUSH TABLES WITH READ LOCK; and then take a snapshot of the data on your master server. snip While the read lock placed by `FLUSH TABLES WITH READ LOCK' is in effect, read the value of the current binary log name and offset on the master: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | mysql-bin.003 | 73 | test,bar | foo,manual,mysql | +---+--+--+--+ 1 row in set (0.06 sec) The `File' column shows the name of the log, while `Position' shows the offset. In the above example, the binary log value is `mysql-bin.003' and the offset is 73. Record the values. You will need to use them later when you are setting up the slave. Once you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master: mysql UNLOCK TABLES; Regards, Thomas Spahni On Tue, 31 Aug 2004, Naveen C Joshi wrote: Hi All : I am still waiting for the response. Thanks in advance. Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:17 PM Subject: Re: replication config without stopping master server The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This machine is as a replication master server. I have configured the other machine as replication slave with same version of MySQL and OS. Now, I want to start replication slave server without stopping the master server. Is it possible? What I did,-- I configured the slave server and started the slave replication. Also gave the command LOAD DATA FROM MASTER. This starts replication but the log files (localhost-relay-bin.xxx) taking to much space and the disk being 100% full. The database is InnoDB. Actual data for replication=1500MB (at master replication server) Disk space available=25GB (at slave server) After starting the slave the logfiles taking 24.5GB and only 0.5GB the actual data. What is wrong with my configuration?? I have the limitation that I have not to stop the master replication server. Please suggest me the
Re: Mysql and errno 13 on RedHat
Hi, Make sure that the mysql user have permissions to write into the folder /var/lib/mysql (or whatever your data folder is) . HTH On Tuesday 31 August 2004 18:00, Yannick Warnier wrote: Hi there, I need to install a database on a RedHat system. Although I'm pretty at ease with Debian, I feel a little unconfortable and cannot find why I get an Error: errno 13 (Permission Denied) when I try to create a new database. Is there some place to look at first? I cannot find any way to do this easily. There is no strace installed on the system (nor any possibility to do so easily). Any idea? Thanks, Yannick -- Dobromir Velev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: specify data type in select statement
Perhaps a casting function would help you: http://dev.mysql.com/doc/mysql/en/Cast_Functions.html If case-sensitivity isn't an issue, you may want to consider converting from char(8) binary to just char(8). Cheers, --V walt wrote: We've run into a problem where binary char column types crash our c++ application when migrating from RedHat 7.3 to RedHat ES. Is there a way to specify the data type to be returned in the select statement. In the below example, selecting office_id will crash the app if it's left as a binary column. I'm looking for something like select charoffice_id from office; create table office ( office_id char(8) binary not null, office_name varchar(50) ); Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Per Thread CPU usage
Hi, is it somehow possible to log how much CPU time a thread (or query) consumed? (this is only for debugging purposes) Thanks in advanced, Frank de Bot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: specify data type in select statement
- Original Message - From: walt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 12:17 PM Subject: specify data type in select statement We've run into a problem where binary char column types crash our c++ application when migrating from RedHat 7.3 to RedHat ES. Is there a way to specify the data type to be returned in the select statement. In the below example, selecting office_id will crash the app if it's left as a binary column. I'm looking for something like select charoffice_id from office; create table office ( office_id char(8) binary not null, office_name varchar(50) ); Have a look at the Cast() and Convert() functions. They are described at http://dev.mysql.com/doc/mysql/en/Cast_Functions.html. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to admin a server that currently has too many connections?
I am creating an index on a very large innodb table that is taking a very long time to complete which I understand is a limitation of innodb. The problem is that another application has been issuing queries against the table and those queries have never timed out. So now I can't kill the connections, because I can't make a database connection to mysql to even see their thread ids: [EMAIL PROTECTED] mysql]$ mysqladmin -u root -p processlist Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Too many connections' I don't want to kill the mysqld process, because that would stop my index creation. Is there anything you can do in this situation? I don't think connections from mysqladmin should ever be denied. Is this a feature request that I should make? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple JOINs
Hello, first i would like to greet all of you because i am new on this list. I am working on yellowpages application and am having some problems. I have different tables for storing info about different companies, their addresses phone numbers and their field of work (i can't remember exact word in english Tables look like this: 1. Table company: ID, name, web, email 2. Table company_address: ID, ID_company, street, city, state... (one company can have multiple addresses so this table is linked to table company via ID_company field 3. Table company_telephone: ID, ID_company_address, number (ID_company_address is linked to table company_address because on different locations companies can have multiple phone numbers) When i want to display all of the data for a company i use following query: SELECT company.name, city, state, street, number, company_telephone.naziv AS tel_naziv FROM company LEFT JOIN company_address ON company.ID = company_address.ID_company LEFT JOIN company_telephone ON company_address.ID = company_telephone.ID_company_address And this works great. Now beside this data, i want to display field of work for each company. for that, i use following tables: 4. Table field: ID, name, l, r (l and r are used for storing data hierarchically) 5. Table field_lookup: ID, ID_field, ID_company, rank (which is used as lookup table in which i can connect companies and their field of work. Field rank is used when you search or browse in one category so that some companies can be displayed first). OK, now i want to display list of companies with their addrersses and phone nrs. together with their field of work. I use following: SELECT company.name, city, state, street, number, company_telephone.naziv AS tel_naziv, field FROM company LEFT JOIN company_address ON company.ID = company_address.ID_company LEFT JOIN company_telephone ON company_address.ID = company_telephone.ID_company_address LEFT JOIN field ON company.ID = field_lookup.ID_company field.ID = field_lookup.ID_field Now i expect something like that: Company1, City1, State1, Street1, Number1, Naziv1, Field1 Company2, City2, State2, Street2, Number2, Naziv2, Field2 Company3, City3, State3, Street3, Number3, Naziv3, Field3 But, it returns this: Company1, City1, State1, Street1, Number1, Naziv1, Field1 Company2, City2, State2, Street2, Number2, Naziv2, NULL Company3, City3, State3, Street3, Number3, Naziv3, NULL Company1, City1, State1, Street1, Number1, Naziv1, NULL Company2, City2, State2, Street2, Number2, Naziv2, Field2 Company3, City3, State3, Street3, Number3, Naziv3, NULL Company1, City1, State1, Street1, Number1, Naziv1, NULL Company2, City2, State2, Street2, Number2, Naziv2, NULL Company3, City3, State3, Street3, Number3, Naziv3, Field3 What seems to be problem? Is my database design good? It is important that one company can have multiple addresses, multiple phone numbers and multiple fields of work. I am also interested are there any free PHP/MySql solutions for yellow pages application. Thanks for your replies! Best regards, Marko -- Relaxen und watch das blinkenlights...
Re: mysqldump
Create a script that would evoke the mysqldump let crond schedule the task. If you're using windows, a batch file will do create a schedule task for your batch file. - Original Message - From: Sparks [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 8:22 PM Subject: mysqldump Hi, I want to have an automatic backup done of my SQL databases, but cant quite figure out how to use mysqldump to do this properly... Ideally, I would like the database backed up, then FTP'd to my home server Can someone lend me a hand with this please! Thanks! Sparks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server crashed with the following error message
Hi, Yesterday, our production MySQL server died with the following error message. Please let us know if there are any patches for this. 040830 19:52:13 InnoDB: Assertion failure in thread 27156583 in file mem0pool.c line 493 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] 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... Cannot determine thread, fp=0x877c76d8, backtrace may not be correct. Number of processes running now: 1 Thanks and Regards, Pandu R Pabbisetty Software Architect Five9 7901 Stoneridge Drive, Suite 200 Pleasanton, CA 94588 USA 925-201-2023 (direct), 925-469-0172 (fax) [EMAIL PROTECTED] www.five9.com The Leader in Hosted VOIP Contact Center Solutions
RE: How to admin a server that currently has too many connections ?
Is there already a mysql privileged account logged in? The database will allow for an administrative account to login, if one is not already active, in the event of too many connections. It sounds as if you may be running one or more accounts with this privilege. -Original Message- From: Justin Swanhart To: [EMAIL PROTECTED] Sent: 8/31/04 11:43 AM Subject: How to admin a server that currently has too many connections? I am creating an index on a very large innodb table that is taking a very long time to complete which I understand is a limitation of innodb. The problem is that another application has been issuing queries against the table and those queries have never timed out. So now I can't kill the connections, because I can't make a database connection to mysql to even see their thread ids: [EMAIL PROTECTED] mysql]$ mysqladmin -u root -p processlist Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Too many connections' I don't want to kill the mysqld process, because that would stop my index creation. Is there anything you can do in this situation? I don't think connections from mysqladmin should ever be denied. Is this a feature request that I should make? -- 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]
Optimize table time estimation...
Does anyone know of any guidelines or references that I can refer to with regard to how long it takes to Optimize tables? I'm running on a 3GHz x86 [single] processor box with SCSI RAID and lots of ram. The Mysql db files are approx. 35G (uncompressed). The table in question is described like this: +---+---+--+-+-+---+ | siteEvent_oid | bigint(255) | | PRI | 0 | | | vendorHandle | varchar(255) | YES | | NULL | | | eventCode | int(11) | | | 0 | | | visitorId | varchar(255) | | MUL | | | | sessionId | varchar(255) | YES | | | | | timestamp | timestamp(14) | YES | MUL | NULL | | +---+---+--+-+-+---+ Can anyone give me an estimate on how long it would take to optimize this? I don't want to start the process and not know how long it will take to complete. Thanks in advance! Chris Bergeron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple JOINs
- Original Message - From: Marko Knezevic [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 12:53 PM Subject: Multiple JOINs Hello, first i would like to greet all of you because i am new on this list. I am working on yellowpages application and am having some problems. I have different tables for storing info about different companies, their addresses phone numbers and their field of work (i can't remember exact word in english Tables look like this: 1. Table company: ID, name, web, email 2. Table company_address: ID, ID_company, street, city, state... (one company can have multiple addresses so this table is linked to table company via ID_company field 3. Table company_telephone: ID, ID_company_address, number (ID_company_address is linked to table company_address because on different locations companies can have multiple phone numbers) When i want to display all of the data for a company i use following query: SELECT company.name, city, state, street, number, company_telephone.naziv AS tel_naziv FROM company LEFT JOIN company_address ON company.ID = company_address.ID_company LEFT JOIN company_telephone ON company_address.ID = company_telephone.ID_company_address And this works great. Now beside this data, i want to display field of work for each company. for that, i use following tables: 4. Table field: ID, name, l, r (l and r are used for storing data hierarchically) 5. Table field_lookup: ID, ID_field, ID_company, rank (which is used as lookup table in which i can connect companies and their field of work. Field rank is used when you search or browse in one category so that some companies can be displayed first). OK, now i want to display list of companies with their addrersses and phone nrs. together with their field of work. I use following: SELECT company.name, city, state, street, number, company_telephone.naziv AS tel_naziv, field FROM company LEFT JOIN company_address ON company.ID = company_address.ID_company LEFT JOIN company_telephone ON company_address.ID = company_telephone.ID_company_address LEFT JOIN field ON company.ID = field_lookup.ID_company field.ID = field_lookup.ID_field Now i expect something like that: Company1, City1, State1, Street1, Number1, Naziv1, Field1 Company2, City2, State2, Street2, Number2, Naziv2, Field2 Company3, City3, State3, Street3, Number3, Naziv3, Field3 But, it returns this: Company1, City1, State1, Street1, Number1, Naziv1, Field1 Company2, City2, State2, Street2, Number2, Naziv2, NULL Company3, City3, State3, Street3, Number3, Naziv3, NULL Company1, City1, State1, Street1, Number1, Naziv1, NULL Company2, City2, State2, Street2, Number2, Naziv2, Field2 Company3, City3, State3, Street3, Number3, Naziv3, NULL Company1, City1, State1, Street1, Number1, Naziv1, NULL Company2, City2, State2, Street2, Number2, Naziv2, NULL Company3, City3, State3, Street3, Number3, Naziv3, Field3 What seems to be problem? Is my database design good? It is important that one company can have multiple addresses, multiple phone numbers and multiple fields of work. I am also interested are there any free PHP/MySql solutions for yellow pages application. Your design seems fine, although you haven't explained how the field and field_lookup tables join to the others so I am making some assumptions. I think your problem is that some of your Left Joins should be Inner Joins. Are you familiar with the difference? Remember, a left join will pick up orphan rows - rows that don't have an equal, non-null key in the other table - and add them to the result. I think that's what is happening here. In fact, looking at your query and data, I'm not sure why any of your joins are Left Joins. It seems to me that each of your joins should only take place if a given pair of tables has equal, non-null keys. I don't see why you would want *any* orphan rows in your result. At the very least, I would make the join involving the Field table an inner join and see what happens. The other thing that strikes me as a possible problem is the '' operator in the last join. I mostly use DB2 but it doesn't have this operator so I'm not completely sure what '' will do in a MySQL join. (I am familiar with the '' operator in programming languages, like Java, I've just never seen it used in joins before.) You might get a better result if you didn't use the '' operator and added another join for the Field_Lookup table. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple JOINs
Rhino wrote: snip The other thing that strikes me as a possible problem is the '' operator in the last join. I mostly use DB2 but it doesn't have this operator so I'm not completely sure what '' will do in a MySQL join. (I am familiar with the '' operator in programming languages, like Java, I've just never seen it used in joins before.) You might get a better result if you didn't use the '' operator and added another join for the Field_Lookup table. Rhino is a synonym for AND. See http://dev.mysql.com/doc/mysql/en/Logical_Operators.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple JOINs
- Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: Marko Knezevic [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 3:46 PM Subject: Re: Multiple JOINs Rhino wrote: snip The other thing that strikes me as a possible problem is the '' operator in the last join. I mostly use DB2 but it doesn't have this operator so I'm not completely sure what '' will do in a MySQL join. (I am familiar with the '' operator in programming languages, like Java, I've just never seen it used in joins before.) You might get a better result if you didn't use the '' operator and added another join for the Field_Lookup table. Rhino is a synonym for AND. See http://dev.mysql.com/doc/mysql/en/Logical_Operators.html. Yeah, I saw that in the manual. Does it behave EXACTLY the same as the AND keyword? Or are there some subtle differences? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump all tables except 2 in a database
Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. Thanks a lot! Emi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple JOINs
Rhino wrote: - Original Message - From: Michael Stassen [EMAIL PROTECTED] Rhino wrote: snip The other thing that strikes me as a possible problem is the '' operator in the last join. I mostly use DB2 but it doesn't have this operator so I'm not completely sure what '' will do in a MySQL join. (I am familiar with the '' operator in programming languages, like Java, I've just never seen it used in joins before.) You might get a better result if you didn't use the '' operator and added another join for the Field_Lookup table. Rhino is a synonym for AND. See http://dev.mysql.com/doc/mysql/en/Logical_Operators.html. Yeah, I saw that in the manual. Does it behave EXACTLY the same as the AND keyword? Or are there some subtle differences? Rhino The manual seems clear that they are synonyms. Did something lead you to expect differences? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump all tables except 2 in a database
At 16:13 -0400 8/31/04, Emi Lu wrote: Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. On the mysqldump command, name the database followed by the 8 tables you wnat to dump. You cannot say dump all but ... -- 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]
CHECK TABLE looks up all databases.
Hi, I have some questions regarding CHECK TABLE, OPTIMIZE TABLE. When i run these commands on table1 mysql seems to lock up all other databases, is it possible to run these things in the background so that other databases can be accessible. // Fredrik Carlsson. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crash on Windows XP SP2 when not run as an administrator
I've tested mysql in my high school lab running from an administrator and from a student account, both from within the Cygwin terminal, and at the Windows command prompt. From the administrator account, it runs fine for hours. From the student account, it promptly (within 15 seconds or so) crashes with the same error each time. I managed to write down the error: -- Debug Assertion Failed! Program: mysqld.exe File: ftell.c Line: 50 Expression: stream != NULL --- This pretty successfully crashes the MySQL server so that if you're running the client, you get messages about the server being inaccessible and such. Is there something that non-administrators need access to that could be causing the problem? Could it be an SP2 issue? I have access to a server where I can set up student accounts so that we can do the tutorial I have planned, but I do, eventually, need to be able to run MySQL on my lab machines. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple JOINs
- Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: Marko Knezevic [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 4:17 PM Subject: Re: Multiple JOINs Rhino wrote: - Original Message - From: Michael Stassen [EMAIL PROTECTED] Rhino wrote: snip The other thing that strikes me as a possible problem is the '' operator in the last join. I mostly use DB2 but it doesn't have this operator so I'm not completely sure what '' will do in a MySQL join. (I am familiar with the '' operator in programming languages, like Java, I've just never seen it used in joins before.) You might get a better result if you didn't use the '' operator and added another join for the Field_Lookup table. Rhino is a synonym for AND. See http://dev.mysql.com/doc/mysql/en/Logical_Operators.html. Yeah, I saw that in the manual. Does it behave EXACTLY the same as the AND keyword? Or are there some subtle differences? Rhino The manual seems clear that they are synonyms. Did something lead you to expect differences? No, nothing specific. Just 20+ years of systems work that says sometimes, if the syntax is subtly different, the result is subtly different ;-) I'm probably just being paranoid though ;-) Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump all tables except 2 in a database
- Original Message - From: Emi Lu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 4:13 PM Subject: mysqldump all tables except 2 in a database Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. You haven't said which version of MySQL you are using or what operating system you are running so this may not be much use to you. I am running MySQL 4.0.15 on Linux Mandrake 8.2. A bash script I posted earlier today could probably be adapted to do what you want. It acquires the names of all of the databases and takes backups of them; all you'd have to do is add an 'if' statement that excluded the two databases that you didn't want to dump. I have attached the script again to this note since it is rather hard to read when it is wrapped in the email editor. Let me know if you have any questions about the script. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suppression of result in SELECT @temp := column?
Did anyone ever follow up on this question? I'm looking for the answer, too. Eamon Daly - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 9:16 AM Subject: Suppression of result in SELECT @temp := column? Is there any way to not sending the result of a user variable assignment to the client? I.e. Suppress the result of; SELECT @temp := columnID FROM table WHERE column = whatever LIMIT 0,1 ...since I only use @temp in my next statement to produce the actual result. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple JOINs
Rhino wrote: Your design seems fine, although you haven't explained how the field and field_lookup tables join to the others so I am making some assumptions. Table field_lookup has two values defined as primary keys: ID_company and ID_field Thus, using simple query like: SELECT company.name, field FROM company, field, field_lookup WHERE company.ID = field_lookup.ID_company field.ID = field_lookup.ID_field i can get which company does what. In the matter of fact, i found some solution to my problem by using following query: SELECT company.name, city, state, street, number, company_telephone.naziv AS tel_naziv, field FROM company, field, field_lookup LEFT JOIN company_address ON company.ID = company_address.ID_company LEFT JOIN company_telephone ON company_address.ID = company_telephone.ID_company_address WHERE company.ID = field_lookup.ID_company field.ID = field_lookup.ID_field ORDER BY rank But now, if one company doesn't have its field of work entered it won't be displayed. I think your problem is that some of your Left Joins should be Inner Joins. Are you familiar with the difference? Remember, a left join will pick up orphan rows - rows that don't have an equal, non-null key in the other table - and add them to the result. I think that's what is happening here. But if i use INNER JOIN i will see only subjects who have all the data entered. You can for instance have subject who has only address and doesn't have phone number and it won't be listed. I am not so familiar with different types of JOIN so any help is appreciated. Thanks for the help! -- Regards, Marko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple JOINs
- Original Message - From: Marko Knezevic [EMAIL PROTECTED] To: MySQL list [EMAIL PROTECTED]; Rhino [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 6:11 PM Subject: Re: Multiple JOINs Rhino wrote: Your design seems fine, although you haven't explained how the field and field_lookup tables join to the others so I am making some assumptions. Table field_lookup has two values defined as primary keys: ID_company and ID_field Thus, using simple query like: SELECT company.name, field FROM company, field, field_lookup WHERE company.ID = field_lookup.ID_company field.ID = field_lookup.ID_field i can get which company does what. In the matter of fact, i found some solution to my problem by using following query: SELECT company.name, city, state, street, number, company_telephone.naziv AS tel_naziv, field FROM company, field, field_lookup LEFT JOIN company_address ON company.ID = company_address.ID_company LEFT JOIN company_telephone ON company_address.ID = company_telephone.ID_company_address WHERE company.ID = field_lookup.ID_company field.ID = field_lookup.ID_field ORDER BY rank But now, if one company doesn't have its field of work entered it won't be displayed. I think your problem is that some of your Left Joins should be Inner Joins. Are you familiar with the difference? Remember, a left join will pick up orphan rows - rows that don't have an equal, non-null key in the other table - and add them to the result. I think that's what is happening here. But if i use INNER JOIN i will see only subjects who have all the data entered. You can for instance have subject who has only address and doesn't have phone number and it won't be listed. I am not so familiar with different types of JOIN so any help is appreciated. Okay, you've convinced me that you need some Left Joins ;-) I think you'll have to use a mix of Left Joins and Inner Joins. In the cases where you need a row even if something doesn't exist, like a subject who has an address but no phone number, use a Left Join. In any case where a row shouldn't appear unless there is a match between the keys, use an Inner Join. I wish I could be more precise than that but I don't know your particular business as well as you do. To help clarify the difference between Inner and Left Joins, I'm going to paste in part of an email I wrote last week helping someone with this point. You probably know a lot of the things in there already but I think this should help you understand the difference between an Inner Join and a Left Join. Please post again if you anything here isn't clear or you still need help despite my instant tutorial. -- I've put together some examples of inner and outer joins for you. I'll use a fixed font throughout so that the table columns are lined up for easier viewing. Before I show the examples to you, a quick review of the concept of a join. A join is the process of concatenating one row of a table to one or more rows of another table. [Concatenating is the process of adding something on to the end of something else. If I join a row from Table A to a row from Table B, the resulting row (or rows) has the columns of Table A followed by the columns of Table B, all in the same result set.] The two tables are still separate and distinct; they have not been permanently combined nor have they been changed in any way.] The usual basis for a join is equality of non-null values. This means that a row in Table A normally only joins to a row in Table B if the two rows have equal non-null keys. We'll see this in detail in a minute. Okay, let's imagine these two tables: Departments: ++---+ | deptno | deptname | ++---+ | A00| Administration| | B01| Accounting| | C01| Education | | D11| Manufacturing | | NULL | Future department | ++---+ Employees: +---++--+--+ | empno | name | workdept | salary | +---++--+--+ | 1 | Allan Abel | C01 | 5.00 | | 2 | Bert Baker | A00 | 6.00 | | 3 | Cal Carter | B01 | 8.00 | | 4 | Dave Doone | A00 | 4.00 | | 5 | Ed Ellings | NULL | 6.00 | | 6 | Fred Fermi | Z99 | 55000.00 | +---++--+--+ If you look at these two tables, they have only one column in common. (Tables that are being joined normally have at least one thing in common and sometimes more.) The thing that these tables have in common is that each table contains department numbers. The Departments table calls them 'deptno' and the Employees table calls them 'workdept' but they are the same thing. The Departments table contains one row for each of the departments in the company. The primary key of the
Is their still any reason why Stored Procedure calls are not supported in MySql?
Firstly, I do appologize for my ignorance in advance. I read the message regarding PRODUCT() and thought to myself:A perl interpreter can be run in a C program. Some one must have written a userdefined function that can execute perl code. Googling I found http://software.tangent.org/download/myperl-0.8.tar.gz at http://tangent.org. Looking at the readme file myperl is mysql userdefined function that executes perl code contained in a table in the select statement. The command line version of mysql also has the ability to start an editor. Theoretically, one could: 1. create a table: Create table user.sparky.functions ( name TEXT NOT NULL , code TEXT NOT NULL ); 2. \e myfunc.mypl 3. LOAD DATA IN FILE myfunc.mypl INTO TABLE user.sparky.functions; 4. /* assume no errors */ Select myfunc(code, colum_data) from user.sparky.functions, data_table where (code = myperl_code ) and ( column_data = what I am looking for ); If and I stress if my assumptions are valid, then stored procedure calls could be written in any interpreted language with an interpreter that can be linked into mysql. Of course from a security stand point this could be dangerous but chrooted brain dead interperter with limited functionality and limits on the amount of disk space and memory that can be used should solve those problems. One interesting consequence of the development of infrastructure to support the development of stored procedure calls is that IDE developers that support mysql would have new market to potentially exploit in a corporate enviroment i.e. run code and get metrics such as how long it to process data i.e. Hi,Lo, Avg. In conclusion, I appologize for my ignorance again however I must still ask: Is their still any reason why Stored Procedure calls are not supported in MySql? Thank you, Raymond -- Date: Mon, 30 Aug 2004 11:29:35 -0400 To: Thomas Schager [EMAIL PROTECTED], [EMAIL PROTECTED] From: Sapenov [EMAIL PROTECTED] Subject: Re: PRODUCT() function - calculating the product of grouped numeric values Message-ID: [EMAIL PROTECTED] Hello Thomas, You probably may consider to write a UDF to do that. Here is a list of available UDF extensions for 4.0.20 - http://mysql-udf.sourceforge.net/ Regards, Khazret Sapenov - http://tangent.org myperl (default) 0.8 2004-01-12 07:58:51 About myperl allows you to execute Perl from inside of MySQL. It can either be stored in a row, or it can be specified in your SQL statement. You also can select other columns which will be passed to Perl. Changes * Added support for the perl interpreter to persist for requests * re-aranged package to meet CPAN spec. * Fix for myperlgroup (it was calling itself too frequently) TAR/GZ http://software.tangent.org/download/myperl-0.8.tar.gz -- Date: Mon, 30 Aug 2004 09:48:55 -0400 To: Per Lonnborg [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: SELECT, ORDER one DISTINCT kolumn? Message-ID: [EMAIL PROTECTED] Per Lonnborg wrote: Hi, Here´s a question from a newbie: I have a database containing ET-times for drivers and their cars. On the web startpage (http://www.lanechoice.net/~pelle/br/) i have made a Top-10 list containing the 10 fastest times. I'll guess column names based on the web page. I have 2 problems: 1. How to select just ONE (the fastest of course) time per uniqe drivercar? SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat ORDER BY fastest_tid DESC LIMIT 10; 2.IF the driver/car has run some race faster than 7,5 secs, he/she is disqualified and should not be on the Top-10 at all. SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat HAVING fastest_tid = 7.5 ORDER BY fastest_tid LIMIT 10; The query I use right now is: Select * from ettan where tid =7.5 ORDER BY tid LIMIT 10 /Per Stockholm, Sweden Note that I only selected aggregate values and grouped columns. If you need the values of non-grouped columns (ort, datum, 60fot, hast) which correspond to the minimum times, you'll need to do a bit more. The manuaal describes 3 methods http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. ___ Skicka gratis SMS! http://www.passagen.se Michael -- -- End of mysql-plain Digest *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is their still any reason why Stored Procedure calls are not supported in MySql?
Stored procedures are in the works for MySQL 5.0: http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html The manual is your friend. Cheers, --V Jacob, Raymond A Jr wrote: Firstly, I do appologize for my ignorance in advance. I read the message regarding PRODUCT() and thought to myself:A perl interpreter can be run in a C program. Some one must have written a userdefined function that can execute perl code. Googling I found http://software.tangent.org/download/myperl-0.8.tar.gz at http://tangent.org. Looking at the readme file myperl is mysql userdefined function that executes perl code contained in a table in the select statement. The command line version of mysql also has the ability to start an editor. Theoretically, one could: 1. create a table: Create table user.sparky.functions ( name TEXT NOT NULL , code TEXT NOT NULL ); 2. \e myfunc.mypl 3. LOAD DATA IN FILE myfunc.mypl INTO TABLE user.sparky.functions; 4. /* assume no errors */ Select myfunc(code, colum_data) from user.sparky.functions, data_table where (code = myperl_code ) and ( column_data = what I am looking for ); If and I stress if my assumptions are valid, then stored procedure calls could be written in any interpreted language with an interpreter that can be linked into mysql. Of course from a security stand point this could be dangerous but chrooted brain dead interperter with limited functionality and limits on the amount of disk space and memory that can be used should solve those problems. One interesting consequence of the development of infrastructure to support the development of stored procedure calls is that IDE developers that support mysql would have new market to potentially exploit in a corporate enviroment i.e. run code and get metrics such as how long it to process data i.e. Hi,Lo, Avg. In conclusion, I appologize for my ignorance again however I must still ask: Is their still any reason why Stored Procedure calls are not supported in MySql? Thank you, Raymond -- Date: Mon, 30 Aug 2004 11:29:35 -0400 To: Thomas Schager [EMAIL PROTECTED], [EMAIL PROTECTED] From: Sapenov [EMAIL PROTECTED] Subject: Re: PRODUCT() function - calculating the product of grouped numeric values Message-ID: [EMAIL PROTECTED] Hello Thomas, You probably may consider to write a UDF to do that. Here is a list of available UDF extensions for 4.0.20 - http://mysql-udf.sourceforge.net/ Regards, Khazret Sapenov - http://tangent.org myperl (default) 0.8 2004-01-12 07:58:51 About myperl allows you to execute Perl from inside of MySQL. It can either be stored in a row, or it can be specified in your SQL statement. You also can select other columns which will be passed to Perl. Changes * Added support for the perl interpreter to persist for requests * re-aranged package to meet CPAN spec. * Fix for myperlgroup (it was calling itself too frequently) TAR/GZ http://software.tangent.org/download/myperl-0.8.tar.gz -- Date: Mon, 30 Aug 2004 09:48:55 -0400 To: Per Lonnborg [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: SELECT, ORDER one DISTINCT kolumn? Message-ID: [EMAIL PROTECTED] Per Lonnborg wrote: Hi, Here´s a question from a newbie: I have a database containing ET-times for drivers and their cars. On the web startpage (http://www.lanechoice.net/~pelle/br/) i have made a Top-10 list containing the 10 fastest times. I'll guess column names based on the web page. I have 2 problems: 1. How to select just ONE (the fastest of course) time per uniqe drivercar? SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat ORDER BY fastest_tid DESC LIMIT 10; 2.IF the driver/car has run some race faster than 7,5 secs, he/she is disqualified and should not be on the Top-10 at all. SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat HAVING fastest_tid = 7.5 ORDER BY fastest_tid LIMIT 10; The query I use right now is: Select * from ettan where tid =7.5 ORDER BY tid LIMIT 10 /Per Stockholm, Sweden Note that I only selected aggregate values and grouped columns. If you need the values of non-grouped columns (ort, datum, 60fot, hast) which correspond to the minimum times, you'll need to do a bit more. The manuaal describes 3 methods http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html. ___ Skicka gratis SMS! http://www.passagen.se Michael -- -- End of mysql-plain Digest *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump all tables except 2 in a database
I have a script on my site that I call smalltables. When run, it echoes out the names of all of the tables _except_ for the large ones that I don't back up. I then use this in the mysqldump command line: /usr/local/mysql/bin/mysqldump -q --user=UUU --host=localhost --password=PPP DB_NAME `smalltables.php` The script runs show tables and filters out those that I don't want to back up. By using an exclusion list, I don't have to add new tables to the list very often. Jeff; On Tue, 31 Aug 2004 15:23:35 -0500, Paul DuBois [EMAIL PROTECTED] said: At 16:13 -0400 8/31/04, Emi Lu wrote: Hello all, In mysql, do we have a way to mysqldump all tables except two in a database. I know we have the way only dump schema, only data, a specific table, both data structure and data. But could someone help me about dumping all tables in a structure except two. For example, in databse D1, I have 10 tables, I'd like to dump 8 of them at one time. On the mysqldump command, name the database followed by the 8 tables you wnat to dump. You cannot say dump all but ... -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is their still any reason why Stored Procedure calls are not supported in MySql?
On Tue, 31 Aug 2004 18:47:57 -0400, Jacob, Raymond A Jr wrote: http://software.tangent.org/download/myperl-0.8.tar.gz at http://tangent.org. Looking at the readme file myperl is mysql userdefined function that executes perl code contained in a table in the select statement. The command line version of mysql also has the ability to start an editor. Theoretically, one could: 1. create a table: Create table user.sparky.functions ( name TEXT NOT NULL , code TEXT NOT NULL ); 2. \e myfunc.mypl 3. LOAD DATA IN FILE myfunc.mypl INTO TABLE user.sparky.functions; 4. /* assume no errors */ Select myfunc(code, colum_data) from user.sparky.functions, data_table where (code = myperl_code ) and ( column_data = what I am looking for ); If and I stress if my assumptions are valid So far they are. then stored procedure calls could be written in any interpreted language with an interpreter that can be linked into mysql. No. None of this allows Stored Procedures to call back to MySQL and update some data in a totally different table in the same transaction. They are still only functions that can only work with what is passed to them, they can not reach out and get additional input from elsewhere in the database. MySQL 5 has the foundations for a stored procedure implementation, but it will be a while. If you need stored procedures now, you need a different database. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and errno 13 on RedHat
Le mar 31/08/2004 à 16:21, Dobromir Velev a écrit : Hi, Make sure that the mysql user have permissions to write into the folder /var/lib/mysql (or whatever your data folder is) . Thanks Eric and Dobromir. This was a problem of permissions on /var/lib/mysql indeed. Although it did not work immediatly after a change, I saw (how awful) that the mysql version was a 3.23 or something and replaced by the recommended version 4. Now it all works perfectly (although mysqld is now divided in ten different processes, but I don't know why and I don't mind as long as it works). Thanks, Yannick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is their still any reason why Stored Procedure calls are not supported in MySql?
My Bad. I was all into 4.xx and since most of the www.mysql.com references 4.xx I never thought about 5.0. I appologize for disturbing the list. thank you, raymond -Original Message- From: V. M. Brasseur [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 18:54 To: Jacob, Raymond A Jr Cc: [EMAIL PROTECTED] Subject: Re: Is their still any reason why Stored Procedure calls are not supported in MySql? Stored procedures are in the works for MySQL 5.0: http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html The manual is your friend. Cheers, --V Jacob, Raymond A Jr wrote: Firstly, I do appologize for my ignorance in advance. I read the message regarding PRODUCT() and thought to myself:A perl interpreter can be run in a C program. Some one must have written a userdefined function that can execute perl code. Googling I found http://software.tangent.org/download/myperl-0.8.tar.gz at http://tangent.org. Looking at the readme file myperl is mysql userdefined function that executes perl code contained in a table in the select statement. The command line version of mysql also has the ability to start an editor. Theoretically, one could: 1. create a table: Create table user.sparky.functions ( name TEXT NOT NULL , code TEXT NOT NULL ); 2. \e myfunc.mypl 3. LOAD DATA IN FILE myfunc.mypl INTO TABLE user.sparky.functions; 4. /* assume no errors */ Select myfunc(code, colum_data) from user.sparky.functions, data_table where (code = myperl_code ) and ( column_data = what I am looking for ); If and I stress if my assumptions are valid, then stored procedure calls could be written in any interpreted language with an interpreter that can be linked into mysql. Of course from a security stand point this could be dangerous but chrooted brain dead interperter with limited functionality and limits on the amount of disk space and memory that can be used should solve those problems. One interesting consequence of the development of infrastructure to support the development of stored procedure calls is that IDE developers that support mysql would have new market to potentially exploit in a corporate enviroment i.e. run code and get metrics such as how long it to process data i.e. Hi,Lo, Avg. In conclusion, I appologize for my ignorance again however I must still ask: Is their still any reason why Stored Procedure calls are not supported in MySql? Thank you, Raymond -- Date: Mon, 30 Aug 2004 11:29:35 -0400 To: Thomas Schager [EMAIL PROTECTED], [EMAIL PROTECTED] From: Sapenov [EMAIL PROTECTED] Subject: Re: PRODUCT() function - calculating the product of grouped numeric values Message-ID: [EMAIL PROTECTED] Hello Thomas, You probably may consider to write a UDF to do that. Here is a list of available UDF extensions for 4.0.20 - http://mysql-udf.sourceforge.net/ Regards, Khazret Sapenov - http://tangent.org myperl (default) 0.8 2004-01-12 07:58:51 About myperl allows you to execute Perl from inside of MySQL. It can either be stored in a row, or it can be specified in your SQL statement. You also can select other columns which will be passed to Perl. Changes * Added support for the perl interpreter to persist for requests * re-aranged package to meet CPAN spec. * Fix for myperlgroup (it was calling itself too frequently) TAR/GZ http://software.tangent.org/download/myperl-0.8.tar.gz -- Date: Mon, 30 Aug 2004 09:48:55 -0400 To: Per Lonnborg [EMAIL PROTECTED] From: Michael Stassen [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: SELECT, ORDER one DISTINCT kolumn? Message-ID: [EMAIL PROTECTED] Per Lonnborg wrote: Hi, Here´s a question from a newbie: I have a database containing ET-times for drivers and their cars. On the web startpage (http://www.lanechoice.net/~pelle/br/) i have made a Top-10 list containing the 10 fastest times. I'll guess column names based on the web page. I have 2 problems: 1. How to select just ONE (the fastest of course) time per uniqe drivercar? SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat ORDER BY fastest_tid DESC LIMIT 10; 2.IF the driver/car has run some race faster than 7,5 secs, he/she is disqualified and should not be on the Top-10 at all. SELECT namm, fabrikat, MIN(tid) AS fastest_tid FROM ettan GROUP BY namm, fabrikat HAVING fastest_tid = 7.5 ORDER BY fastest_tid LIMIT 10; The query I use right now is: Select * from ettan where tid =7.5 ORDER BY tid LIMIT 10 /Per Stockholm, Sweden Note that I only selected aggregate values and grouped columns. If you need the values of non-grouped columns (ort, datum, 60fot, hast) which correspond to the minimum times, you'll need to do a bit more. The manuaal describes 3 methods
Re: ERROR 1005 - Please help
Hi Michael, I must say thank you for helping me. Yes you are right - I made the changes and it works. I will have to read the manual to further understand the issue with Primary and Foreign keys. Thank you. Maru --- Michael Stassen [EMAIL PROTECTED] wrote: The problem is in table SECTIONS. From the manual, In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html In SECTIONS, you have an unnecessary index on courseID (it's the first column in the primary key) and no index where sectionID comes first (it's second in the primary key). The former is simply inefficient, the latter is causing the error. Most likely, you should drop the index on courseID and add one on sectionID: ALTER TABLE SECTIONS DROP INDEX courseID, ADD INDEX (sectionID); After you do that, the ENROLLS table creation should work without error. Keeping the courseID index and reversing the primary key should also work, but a primary key on (sectionID, courseID) makes less logical sense, I think. Michael Mulugeta Maru wrote: I have posted this in a subject called - InnoDB table creation. I am just trying to be specific. Please forgive me if this is not allowed. I have searched the online help and this site. I can not find out why I am getting this error: ERROR 1005 at line 33: Can't creat table '.\enrollment1\enrolls.frm' (errno: 150) Here is what I am trying to do: CREATE TABLE ENROLLS (courseID SMALLINT NOT NULL, sectionID SMALLINT NOT NULL, studentID SMALLINT NOT NULL, grade SMALLINT, PRIMARY KEY(courseID,sectionID,studentID), FOREIGN KEY(courseID) REFERENCES COURSES(courseID) ON UPDATE CASCADE ON DELETE CASCADE, INDEX(sectionID), FOREIGN KEY(sectionID) REFERENCES SECTIONS(sectionID) ON UPDATE CASCADE ON DELETE CASCADE, INDEX(studentID), FOREIGN KEY(studentID) REFERENCES STUDENTS(studentID) ON UPDATE CASCADE ON DELETE CASCADE)TYPE=INNODB; Pleas help if you can. Thank you. Maru __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication config without stopping master server
Hi Thomas, Many thanks for valuable suggestion. My database is InnoDB here and the tables are in .frm, .MYD and .MYI. Is the same procedure for this database also? My master server database is being update in every minute and I will prefer to not lock on write. If there is no other way to replicate data on slave than I will do the same. As my database is being update in every minute and if I lock it for write, than is there any side effect or it will recover all after unlock. Please provide me help in this situation. Thanks Regards Naveen - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: Naveen C Joshi [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 9:48 PM Subject: Re: replication config without stopping master server Naveen, You don't need to shut down the master but you have to stop writing to the database for taking the snapshot. Follow the description in the manual: 3. If you are using MyISAM tables, flush all the tables and block write queries by executing `FLUSH TABLES WITH READ LOCK' command. mysql FLUSH TABLES WITH READ LOCK; and then take a snapshot of the data on your master server. snip While the read lock placed by `FLUSH TABLES WITH READ LOCK' is in effect, read the value of the current binary log name and offset on the master: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | mysql-bin.003 | 73 | test,bar | foo,manual,mysql | +---+--+--+--+ 1 row in set (0.06 sec) The `File' column shows the name of the log, while `Position' shows the offset. In the above example, the binary log value is `mysql-bin.003' and the offset is 73. Record the values. You will need to use them later when you are setting up the slave. Once you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master: mysql UNLOCK TABLES; Regards, Thomas Spahni On Tue, 31 Aug 2004, Naveen C Joshi wrote: Hi All : I am still waiting for the response. Thanks in advance. Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:17 PM Subject: Re: replication config without stopping master server The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This
