re: replication newbie questions
Hi! Ed == Ed L mysql@bluepolka.net writes: Ed Mysql newbie here, looking for some help configuring 5.0.45 master-slave Ed replication. Here's my scenario... Ed We have a heavily loaded 30gb 5.0.45 DB we need to replicate via Ed master-slave configuration to a new, beefier server running same mysql Ed 5.0.45, and then cutover to the new server. Due to extreme SAN Ed congestion and a grossly overloaded master server, our DB dumps take 5.5 Ed hours. But we cannot afford that much downtime or locking during the Ed replication transition; we can manage 10-15 minutes, but more is very Ed problematic. Ed I understand that FLUSH TABLES WITH READ LOCK will lock the tables for Ed the duration of the 5.5 hour dump. Is this true? Yes. Ed If so, we'd like to dump/initialize/sync slave WITHOUT any locking Ed anything the master for more than a few seconds if at all possible. Ed Will this give us the dump we need? Ed mysqldump --single-transaction --master-data --all-databases You can do a dump without locking by using the xtrabackup tool. This however assumes you are using InnoDB as the storage engine. The other option is to use file system snapshots, if your file system supports that. In this case you only have to do the FLUSH TABLES ... for the duration of the snapshot. Regards, Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
replication newbie questions
Mysql newbie here, looking for some help configuring 5.0.45 master-slave replication. Here's my scenario... We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic. I understand that FLUSH TABLES WITH READ LOCK will lock the tables for the duration of the 5.5 hour dump. Is this true? If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need? mysqldump --single-transaction --master-data --all-databases Thank you in advance for any help. Ed
Re: replication newbie questions
Why don't u try snapshot backups, where the lock held for less duration. Or can't u take mysql dumps during Night time when there is less bd activity On Thursday, August 29, 2013, Ed L. mysql@bluepolka.net wrote: Mysql newbie here, looking for some help configuring 5.0.45 master-slave replication. Here's my scenario... We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic. I understand that FLUSH TABLES WITH READ LOCK will lock the tables for the duration of the 5.5 hour dump. Is this true? If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need? mysqldump --single-transaction --master-data --all-databases Thank you in advance for any help. Ed
Re: replication newbie questions
On 8/28/13 2:00 PM, Ananda Kumar wrote: Why don't u try snapshot backups, where the lock held for less duration. Or can't u take mysql dumps during Night time when there is less bd activity I neglected to mention these systems are both CentOS linux systems. Unfortunately, the 5.5 hour dumps are already done during the least busy times. Regarding snapshots, how long are snapshot locks held? These are ext4 filesystems. Assuming the lock is not held for long, what's the recommended way to do snapshots on ext4? Thanks, Ed On Thursday, August 29, 2013, Ed L. mysql@bluepolka.net mailto:mysql@bluepolka.net wrote: Mysql newbie here, looking for some help configuring 5.0.45 master-slave replication. Here's my scenario... We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic. I understand that FLUSH TABLES WITH READ LOCK will lock the tables for the duration of the 5.5 hour dump. Is this true? If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need? mysqldump --single-transaction --master-data --all-databases Thank you in advance for any help. Ed
Re: replication newbie questions
if i u have LVM's then lock is held only for the duration of taking snapshot, which would be few min, if there is very less activity on the db. On Wed, Aug 28, 2013 at 3:08 PM, Ed L. mysql@bluepolka.net wrote: On 8/28/13 2:00 PM, Ananda Kumar wrote: Why don't u try snapshot backups, where the lock held for less duration. Or can't u take mysql dumps during Night time when there is less bd activity I neglected to mention these systems are both CentOS linux systems. Unfortunately, the 5.5 hour dumps are already done during the least busy times. Regarding snapshots, how long are snapshot locks held? These are ext4 filesystems. Assuming the lock is not held for long, what's the recommended way to do snapshots on ext4? Thanks, Ed On Thursday, August 29, 2013, Ed L. mysql@bluepolka.net wrote: Mysql newbie here, looking for some help configuring 5.0.45 master-slave replication. Here's my scenario... We have a heavily loaded 30gb 5.0.45 DB we need to replicate via master-slave configuration to a new, beefier server running same mysql 5.0.45, and then cutover to the new server. Due to extreme SAN congestion and a grossly overloaded master server, our DB dumps take 5.5 hours. But we cannot afford that much downtime or locking during the replication transition; we can manage 10-15 minutes, but more is very problematic. I understand that FLUSH TABLES WITH READ LOCK will lock the tables for the duration of the 5.5 hour dump. Is this true? If so, we'd like to dump/initialize/sync slave WITHOUT any locking anything the master for more than a few seconds if at all possible. Will this give us the dump we need? mysqldump --single-transaction --master-data --all-databases Thank you in advance for any help. Ed
A Newbie question about make and the term.c file
I am trying to install mysql 5.1.59 on my ppc running os x and I get this error message in the term.c file. cc1: warnings being treated as errors term.c: In function ‘term_set’: term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type make[2]: *** [term.o] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all-recursive] Error 1 I can't figure out what it means or how to fix it. Help please. Peter
Re: A Newbie question about make and the term.c file
I can't help directly with the error message (the warning seems fairly harmless), but may I inquire why you are building MySQL instead of using one of the prepared binaries? Compiling under OS/X can be pretty harrowing. - michael dykman On Sat, Oct 8, 2011 at 2:49 AM, Peter Schrock peter.schr...@gmail.comwrote: I am trying to install mysql 5.1.59 on my ppc running os x and I get this error message in the term.c file. cc1: warnings being treated as errors term.c: In function ‘term_set’: term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type make[2]: *** [term.o] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all-recursive] Error 1 I can't figure out what it means or how to fix it. Help please. Peter -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: A Newbie question about make and the term.c file
I can understand your point, however, as stated, I am using a ppc architecture and am not afforded the luxury of binaries in the most up to date versions. I know using 5.1.59 isn't the most up to date, but I was also having issues with the most current version. I might start with the most current binary for ppc and then try upgrading. About the error, I am sure it is harmless, but I can't get past it, which means I can't use mysql. Peter On Oct 8, 2011, at 7:54 AM, Michael Dykman mdyk...@gmail.com wrote: I can't help directly with the error message (the warning seems fairly harmless), but may I inquire why you are building MySQL instead of using one of the prepared binaries? Compiling under OS/X can be pretty harrowing. - michael dykman On Sat, Oct 8, 2011 at 2:49 AM, Peter Schrock peter.schr...@gmail.comwrote: I am trying to install mysql 5.1.59 on my ppc running os x and I get this error message in the term.c file. cc1: warnings being treated as errors term.c: In function ‘term_set’: term.c:946: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:947: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:949: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:950: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:952: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:953: warning: passing argument 1 of ‘tgetflag’ discards qualifiers from pointer target type term.c:955: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type term.c:956: warning: passing argument 1 of ‘tgetnum’ discards qualifiers from pointer target type make[2]: *** [term.o] Error 1 make[1]: *** [all-recursive] Error 1 make: *** [all-recursive] Error 1 I can't figure out what it means or how to fix it. Help please. Peter -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Newbie question: Association table and Foreign Key
Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren
Re: Newbie question: Association table and Foreign Key
Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: Association table and Foreign Key
Oh yeah, forgot to mention that. Yes, one event will have multiple categories. And one category can be applicable to multiple events. On Tue, Mar 1, 2011 at 8:33 PM, Dhaval Jaiswal jaiswal.dha...@enzenglobal.com wrote: Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font
Re: Newbie question: Association table and Foreign Key
2011/03/01 20:23 +0800, Wagyu Beef Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Well, if your problem is really like that in the example that you quote, then look up 'REFERENCES' under 'CREATE TABLE'. That shows you what to put in the common table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie question database tables
Thanks all... I got this to work! Much appreciated.. And thanks for patience with a newbie! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
newbie question database tables
I'm working on a database that needs to do a few things and getting brain freeze on one part. Scenario: I want to compile a db of articles with these tables: Categories Topics Users Categories cat_ID | cat_name Topics top_ID | top_name | top_content | cat_ID Users user_ID | user_name | top_ID or user_ID | user_name | top_ID | top_IDb | top_IDc etc (output to web page using php) But I need to show which users are using which topics, and I can add top_ID to the user file, which is fine if they are only using one topic. I could add 5 different topic to each user, but then I couldn't expand later. Reverse is true if I add user_ID to the Topics. So, need an idea how to solve this so it doesn't matter how many new users I keep adding, I can still see who is using the topics. As I said, a newbie question. Thanks much. -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: newbie question database tables
Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: Friday, September 10, 2010 12:09 PM To: mysql@lists.mysql.com Subject: newbie question database tables I'm working on a database that needs to do a few things and getting brain freeze on one part. Scenario: I want to compile a db of articles with these tables: Categories Topics Users Categories cat_ID | cat_name Topics top_ID | top_name | top_content | cat_ID Users user_ID | user_name | top_ID or user_ID | user_name | top_ID | top_IDb | top_IDc etc (output to web page using php) But I need to show which users are using which topics, and I can add top_ID to the user file, which is fine if they are only using one topic. [JS] What you need is another table, users_topic: users_topics: user_ID | top_ID and get rid of the top_ID field from the users table. That's the general technique to use when you need a cross-reference. I could add 5 different topic to each user, but then I couldn't expand later. Reverse is true if I add user_ID to the Topics. So, need an idea how to solve this so it doesn't matter how many new users I keep adding, I can still see who is using the topics. As I said, a newbie question. Thanks much. -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie question database tables
On 9/10/10 12:31 PM, Jerry Schwartz wrote: Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Thank you! -- Patrice Olivier-Wilson http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] newbie sequel question: how do we search for multiple things on 1 field like:
On Fri, Jun 18, 2010 at 16:30, Dave deal...@gmail.com wrote:
SELECT * FROM contacts WHERE state = 'CA' and name = 'bob' or
name = 'sam' or name = 'sara'
We begin by asking on the right list (mysql@lists.mysql.com, CC'd
by courtesy).
You're on the right track though. Try a WHERE...IN statement:
SELECT * FROM contacts WHERE state='CA' AND name IN ('bob','sam','sara');
--
/Daniel P. Brown
daniel.br...@parasane.net || danbr...@php.net
http://www.parasane.net/ || http://www.pilotpig.net/
We now offer SAME-DAY SETUP on a new line of servers!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: importing cvs settings - followup
Thanks again for assistance. FYI, I did track this thread down http://ask.metafilter.com/57007/Missing-commas-in-CSV-file (exerpt: Maybe there is a space or something in the 14th column of the first 15 rows. posted by. on February 14, 2007 It's a bug in Excel (not something you did wrong.) posted by . February 14, 2007 ) -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie question: importing cvs settings
-Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: Sunday, September 27, 2009 10:19 AM Cc: 'mysql' Subject: Re: Newbie question: importing cvs settings Back again... I have 192 records to import, and tried my extra line at the end hoping for a work around, but nope, it failed at line 17 again. Invalid field count in CSV input on line 17. Anyone have an idea why this might be happening? [JS] This is just a shot in the dark, but Excel can be rather surprising when it puts out a CSV file. Depending upon the data, and exactly how you've specified the export, it can put double-quotes in unexpected places. If you leave out the 17th line of data what happens? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Patrice Olivier-Wilson wrote: Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: importing cvs settings
Jerry Schwartz wrote: [JS] This is just a shot in the dark, but Excel can be rather surprising when it puts out a CSV file. Depending upon the data, and exactly how you've specified the export, it can put double-quotes in unexpected places. If you leave out the 17th line of data what happens? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com Thanks Jerry, Gavin and John: Sorry for not an immediate response to all of your suggestions. Other demands were pulling at me since I first asked for assistance. I opened the .csv file with Text Wrangler, and the commas are missing at about line 17 portfolio_ID,portfolio_sort_ID,portfolio_title,portfolio_bodycopy,portfolio_image,portfolio_before ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg, ,1,Kitchens,,123.jpg ,1,Kitchens,,123.jpg ,1,Kitchens,,123.jpg So not sure why that is happening. I'm on a Mac, using Excel 2008. But at least you all have helped me find what it is doing, so now, I can at least pull into a txt file and make corrections manually. My thanks to all of you for your help and patience. (above represents an empty portfolio_ID, a filled in sort_ID, title, empty bodycopy, image, empty before) Thank you. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie question: importing cvs settings
1. Try opening up the csv file in a text editor, viewing it in a spreadsheet looks like it's hiding some extra formatting or lines that may be causing problems. 2. Try importing through the mysql CLI. From the screenshot you posted, it looks like PMA is parsing the file and creating an insert statement for each line. It may be incorrectly handling some of that data. Using the CLI you'll get better feedback about what, if any, the error is. Regards, Gavin Towey -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: Saturday, September 26, 2009 11:02 AM To: 'mysql' Subject: Re: Newbie question: importing cvs settings Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you John wrote: I assume you mean csv not cvs! What is the error you get when the import fails? What version of MySQL are you using? Can you post the output of SHOW CREATE TABLE for the table you are trying to load the file in to and a sample of the csv which is failing to load? Do you get the same error if you try and load the files using MySQL client and the LOAD DATA INFILE command? (See this link for details on how to use LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html) Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 26 September 2009 17:08 To: mysql Subject: Newbie question: importing cvs settings Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: importing cvs settings
Back again... I have 192 records to import, and tried my extra line at the end hoping for a work around, but nope, it failed at line 17 again. Invalid field count in CSV input on line 17. Anyone have an idea why this might be happening? Patrice Olivier-Wilson wrote: Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie question: importing cvs settings
Patrice, Can you post the output of SHOW CREATE TABLE for the table you are having difficulty inserting into? Without knowing the table structure its very difficult to work out why your data load is failing. Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 27 September 2009 15:19 Cc: 'mysql' Subject: Re: Newbie question: importing cvs settings Back again... I have 192 records to import, and tried my extra line at the end hoping for a work around, but nope, it failed at line 17 again. Invalid field count in CSV input on line 17. Anyone have an idea why this might be happening? Patrice Olivier-Wilson wrote: Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.113/2396 - Release Date: 09/26/09 05:51:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie question: importing cvs settings
Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie question: importing cvs settings
I assume you mean csv not cvs! What is the error you get when the import fails? What version of MySQL are you using? Can you post the output of SHOW CREATE TABLE for the table you are trying to load the file in to and a sample of the csv which is failing to load? Do you get the same error if you try and load the files using MySQL client and the LOAD DATA INFILE command? (See this link for details on how to use LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html) Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 26 September 2009 17:08 To: mysql Subject: Newbie question: importing cvs settings Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.113/2396 - Release Date: 09/26/09 05:51:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: importing cvs settings
Yep, typo ...:-( I did some screen shots of 2 tests. A workaround solution is to make a final entry in the csv file that I don't really need. Then everything up to that point gets imported ok using CSV method. The LOAD DATA method did not enter anything. My earlier assumption about line 17 was false. It was dependent on how many rows, and I had been using tests with same amount of data. As I said, very beginner level, so thanks for the patience. screenshots at http://biz-comm.com/mysqlprojects/ thank you John wrote: I assume you mean csv not cvs! What is the error you get when the import fails? What version of MySQL are you using? Can you post the output of SHOW CREATE TABLE for the table you are trying to load the file in to and a sample of the csv which is failing to load? Do you get the same error if you try and load the files using MySQL client and the LOAD DATA INFILE command? (See this link for details on how to use LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html) Regards John Daisley MySQL Cognos Contractor Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer (CMDEV) IBM Cognos BI Developer Telephone +44 (0)7812 451238 Email j...@butterflysystems.co.uk -Original Message- From: Patrice Olivier-Wilson [mailto:b...@biz-comm.com] Sent: 26 September 2009 17:08 To: mysql Subject: Newbie question: importing cvs settings Greetings: I have a project for which need to import cvs files into db. I can do so up to a point. The import will only do 16 lines, consistently. Error is failing at line 17. Steps: create table fields in Excel document, where they all match database fields enter information in several of the columns, but not all as client will be filling it in online (leaving ID blank) save excel to .cvs log into phpMyAdmin import cvs I've experimented with several settings in the import, but consistently, it fails at line 17, even with different .cvs files. Any guidance, most appreciated. -- Patrice Olivier-Wilson 888-385-7217 http://biz-comm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Indexing? (Warning: relative newbie.)
After a few off-list e-mails with Tim, I issued ALTER TABLE dbmail_messageblks ADD INDEX ( blocksize AND physmessage_id ); which took almost 11 hours to index. Once done, however, my select statement went from a hair over 50 minutes to 15 seconds. (1.69 seconds after the index was cached.) Wow. Thanks for the help, all! -Ken On Wed, June 24, 2009 12:03 pm, Little, Timothy wrote: To answer your questions in no particular order, YES you can speed it up with indexing. You might want to first create an index on ( blocksize AND physmessage_id ). Why, you might ask, index on physmessage_id? Because then the db won't have to do a fetch on items from the table since it's in the INDEX itself, saving any unnecessary reads. Realistically, I can't see that taking more than a few seconds, at most, to execute. However, making the index might take a serious bit of time. Please let us all know how it does or does not work. Tim... -Original Message- From: Ken D'Ambrosio [mailto:k...@jots.org] Sent: Wednesday, June 24, 2009 11:07 AM To: mysql@lists.mysql.com Subject: Indexing? (Warning: relative newbie.) Hi, all. I'm a long-time MySQL user who's only recently had to start learning some administrative stuff, largely because I finally have a decently-sized database. My database is about 100 GB; I'm using it -- via dbmail (www.dbmail.org) -- as a mail server for my company. While dbmail is well-and-good with its IMAP front-end, I'm thinking of writing a Python front-end to do some queries directly against MySQL. But some of them take a l-o-n-g time. As an example, I've got a table with slightly over a million records; I'd like to be able to show (say) only IDs of messages under a half-MB. The query would look something like this: select physmessage_id,blocksize from dbmail_messageblks where blocksize 50; That query takes 50 minutes. A smidge long to wait. So I said, Huh. That's impressive. And I tried it without the physmessage_id: select blocksize from dbmail_messageblks where blocksize 50; That took 14 seconds. A bit more in my timeframe. Can I optimize this with indexing? Should I be using a different DB engine? Is there a site/book I should be learning DBA fundamentals from that might offer me direction for stuff like this? Sorry for all the newbie questions, but I haven't done serious database stuff since Foxbase/dBase III days. Things have changed a little since then. Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=tlit...@tgrnet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@jots.org -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Indexing? (Warning: relative newbie.)
Hi Walter, Walter Heck - OlinData.com wrote: Hey Tim, all On Wed, Jun 24, 2009 at 10:03 AM, Little, Timothytlit...@thomaspublishing.com wrote: Why, you might ask, index on physmessage_id? Because then the db won't have to do a fetch on items from the table since it's in the INDEX itself, saving any unnecessary reads. FYI: That only holds true for InnoDB, not for MyISAM. I think you have confused the InnoDB behavior of using the entire PRIMARY KEY as the unique row identifier for each entry in a secondary key with the practice of defining a covering index. Tim was correct: an index on (blocksize,physmessage_id) would allow that query to avoid any direct reads of the data table as all of the information for the query would have come from the index itself (regardless of database engine). -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Indexing? (Warning: relative newbie.)
Hi, all. I'm a long-time MySQL user who's only recently had to start learning some administrative stuff, largely because I finally have a decently-sized database. My database is about 100 GB; I'm using it -- via dbmail (www.dbmail.org) -- as a mail server for my company. While dbmail is well-and-good with its IMAP front-end, I'm thinking of writing a Python front-end to do some queries directly against MySQL. But some of them take a l-o-n-g time. As an example, I've got a table with slightly over a million records; I'd like to be able to show (say) only IDs of messages under a half-MB. The query would look something like this: select physmessage_id,blocksize from dbmail_messageblks where blocksize 50; That query takes 50 minutes. A smidge long to wait. So I said, Huh. That's impressive. And I tried it without the physmessage_id: select blocksize from dbmail_messageblks where blocksize 50; That took 14 seconds. A bit more in my timeframe. Can I optimize this with indexing? Should I be using a different DB engine? Is there a site/book I should be learning DBA fundamentals from that might offer me direction for stuff like this? Sorry for all the newbie questions, but I haven't done serious database stuff since Foxbase/dBase III days. Things have changed a little since then. Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Indexing? (Warning: relative newbie.)
To answer your questions in no particular order, YES you can speed it up with indexing. You might want to first create an index on ( blocksize AND physmessage_id ). Why, you might ask, index on physmessage_id? Because then the db won't have to do a fetch on items from the table since it's in the INDEX itself, saving any unnecessary reads. Realistically, I can't see that taking more than a few seconds, at most, to execute. However, making the index might take a serious bit of time. Please let us all know how it does or does not work. Tim... -Original Message- From: Ken D'Ambrosio [mailto:k...@jots.org] Sent: Wednesday, June 24, 2009 11:07 AM To: mysql@lists.mysql.com Subject: Indexing? (Warning: relative newbie.) Hi, all. I'm a long-time MySQL user who's only recently had to start learning some administrative stuff, largely because I finally have a decently-sized database. My database is about 100 GB; I'm using it -- via dbmail (www.dbmail.org) -- as a mail server for my company. While dbmail is well-and-good with its IMAP front-end, I'm thinking of writing a Python front-end to do some queries directly against MySQL. But some of them take a l-o-n-g time. As an example, I've got a table with slightly over a million records; I'd like to be able to show (say) only IDs of messages under a half-MB. The query would look something like this: select physmessage_id,blocksize from dbmail_messageblks where blocksize 50; That query takes 50 minutes. A smidge long to wait. So I said, Huh. That's impressive. And I tried it without the physmessage_id: select blocksize from dbmail_messageblks where blocksize 50; That took 14 seconds. A bit more in my timeframe. Can I optimize this with indexing? Should I be using a different DB engine? Is there a site/book I should be learning DBA fundamentals from that might offer me direction for stuff like this? Sorry for all the newbie questions, but I haven't done serious database stuff since Foxbase/dBase III days. Things have changed a little since then. Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=tlit...@tgrnet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Indexing? (Warning: relative newbie.)
Hey Tim, all On Wed, Jun 24, 2009 at 10:03 AM, Little, Timothytlit...@thomaspublishing.com wrote: Why, you might ask, index on physmessage_id? Because then the db won't have to do a fetch on items from the table since it's in the INDEX itself, saving any unnecessary reads. FYI: That only holds true for InnoDB, not for MyISAM. cheers, -- Walter Heck, Engineer @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Newbie ... Schema details listing
in information_schema.tables e.g. SELECT AUTO_INCREMENT from information_schema.tables where WHERE TABLE_NAME = Products AND TABLE_SCHEMA = bs3578; Viel Gluck, Martin __ Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: bobsh...@ntlworld.com To: mysql@lists.mysql.com CC: win32-h...@lists.mysql.com Subject: Newbie ... Schema details listing Date: Sun, 12 Apr 2009 14:54:50 +0100 I have written a .cfm to output a listing of columns with Name, Format, Size, NULL, KEY, etc. One thing I need to include is whether the Primary Key is AUTO_INCREMENT That element does not appear to be in the same SCHEMA table. Where is it ? and How can I use it with this Query ... ? CFQuery datasource=bs3578 name=pColumns SELECT COLUMN_NAME AS pCname, DATA_TYPE AS pDtype, IS_NULLABLE AS pISnull, CHARACTER_MAXIMUM_LENGTH AS pMAXlen, COLUMN_DEFAULT AS pCdefault, COLUMN_TYPE AS pCtype, COLUMN_KEY AS pCkey, NUMERIC_SCALE AS pNscale, NUMERIC_PRECISION AS pNprec FROMINFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = Products AND TABLE_SCHEMA = bs3578; /cfquery -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12962 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message _ Rediscover Hotmail®: Now available on your iPhone or BlackBerry http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Mobile1_042009
Newbie ... Schema details listing
I have written a .cfm to output a listing of columns with Name, Format, Size, NULL, KEY, etc. One thing I need to include is whether the Primary Key is AUTO_INCREMENT That element does not appear to be in the same SCHEMA table. Where is it ? and How can I use it with this Query ... ? CFQuery datasource=bs3578 name=pColumns SELECT COLUMN_NAME AS pCname, DATA_TYPE AS pDtype, IS_NULLABLE AS pISnull, CHARACTER_MAXIMUM_LENGTH AS pMAXlen, COLUMN_DEFAULT AS pCdefault, COLUMN_TYPE AS pCtype, COLUMN_KEY AS pCkey, NUMERIC_SCALE AS pNscale, NUMERIC_PRECISION AS pNprec FROMINFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = Products AND TABLE_SCHEMA = bs3578; /cfquery -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12962 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: Newbie --- JOINS
Hello Bob, BobSharp wrote: Picture does not seem to have been carried in the message, posts with attachment did not seem to get through either. So hope the link works. Below is the ER diagram in an exercise I am trying to do. http://www.probowluk.co.uk/images/er_ECA_001.jpg It's been going well so far, and several ColdFusion pages written already. Need to do one for a Purchase Order Report for ... - given SupplierCode - given StartDate and EndDate of Orders My problem is in the CFquery - understanding what JOINS to use and in what order to use them. Which kind of join to write all depends on how inclusive you want your results to be. On a sheet of paper draw two overlapping circles (yes this is a Venn diagram). In the left circle, write left, in the right circle write right, and where they overlap write inner. Above each circle put the name of a table. From your diagram and your descriptoin is sounds like you wanted to link Suppliers and PurchaseOrders. Put Suppliers over the left circle and PurchaseOrders over the right. If I assume that `Suppliers`.`SupplierCode` and `PurchaseOrders`.`SupplierCodefk` are the same numbers then here is how to get your results. Remember: a JOIN is a form of cross-product between two tables. If you have one row in one table and it matches to 3 rows in the other table, then the first table's data will be listed 3 times. This correct and proper behavior for a database. To get the list of all rows in Suppliers and any matching rows from PurchaseOrders, use this form: SELECT ... FROM `Suppliers` LEFT JOIN `PurchaseOrders` ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk` WHERE ... To get all of the rows from PurchaseOrders and only those matching rows from Suppliers, use: SELECT ... FROM `Suppliers` RIGHT JOIN `PurchaseOrders` ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk` WHERE ... And if you only want to list rows from either table if they have at least one matching row in the other, use: SELECT ... FROM `Suppliers` INNER JOIN `PurchaseOrders` ON `Suppliers`.`SupplierCode` = `PurchaseOrders`.`SupplierCodefk` WHERE ... The WHERE clause is optional. Of course, you will have to either supply a list of columns or use the * operator for the SELECT clause but this is the general shape of the command you will want to use. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie --- JOINS
Picture does not seem to have been carried in the message, posts with attachment did not seem to get through either. So hope the link works. Below is the ER diagram in an exercise I am trying to do. http://www.probowluk.co.uk/images/er_ECA_001.jpg It's been going well so far, and several ColdFusion pages written already. Need to do one for a Purchase Order Report for ... - given SupplierCode - given StartDate and EndDate of Orders My problem is in the CFquery - understanding what JOINS to use and in what order to use them. (it is a MyISAM database --- no constraints) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12908 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Newbie and JOINS
Below is the ER diagram in an exercise I am trying to do. It's been going well so far, and several ColdFusion pages written already. Need to do one for a Purchase Order Report for ... - given SupplierCode - given StartDate and EndDate of Orders My problem is in the CFquery - understanding what JOINS to use and in what order to use them. (it is a MyISAM database = no constraints) -- I am using the free version of SPAMfighter. We are a community of 6 million users fighting spam. SPAMfighter has removed 12908 of my spam emails to date. Get the free SPAMfighter here: http://www.spamfighter.com/len The Professional version does not have this message
Re: Newbie Question - MySQL Administrator
The online help for mysql administrator is here: http://dev.mysql.com/doc/administrator/en/index.html OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Fri, Feb 20, 2009 at 7:17 AM, Jeff Murdock jeff_murd...@yahoo.com wrote: This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn't available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie First Use Connection Question - Mac OSX 10.5.6
I am brand new to MySQL and JAVA/Netbeans 6.5 so please excuse the stupid questions ... 1. I have just downloaded and successfully installed MySQL v5.1 on my MacBook Pro running OS X 10.5.6 2. I have also downloaded and installed MySQL Tools: Administrator Query Browser (I come from a Visual Basic MS SQL Server 2000/2005 environment) When you install MS SQL server the default login is sa with a blank password. My question is; How do I login (connection settings) to MySQL (for Administrator and Query Browser tools)? (I start my server by going to settings and then MySQL icon, Start Server, so my server is running) Unfortunately, I have never seen MySQL in action nor do I know anyone to ask/show me how to get started.
Re: Newbie First Use Connection Question - Mac OSX 10.5.6
The root Password will be blank after initial install.
You can set it at a shell prompt with commands something like this...
shell mysql -u root
mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql FLUSH PRIVILEGES;
mysql EXIT;
Where newpwd is your desired password.
Best to secure or delete all accounts with blank passwords :)
You can then start MySQL administrator and log in using the username root,
the password you specified in the SET PASSWORD command and the host of
localhost.
Regards
John
I am brand new to MySQL and JAVA/Netbeans 6.5 so please excuse the
stupid questions ...
1. I have just downloaded and successfully installed MySQL v5.1 on my
MacBook Pro running OS X 10.5.6
2. I have also downloaded and installed MySQL Tools: Administrator
Query Browser
(I come from a Visual Basic MS SQL Server 2000/2005 environment)
When you install MS SQL server the default login is sa with a blank
password.
My question is;
How do I login (connection settings) to MySQL (for Administrator and
Query Browser tools)?
(I start my server by going to settings and then MySQL icon, Start
Server, so my server is running) Unfortunately, I have never seen
MySQL in action nor do I know anyone to ask/show me how to get started.
__
This email has been scanned by Netintelligence
http://www.netintelligence.com/email
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie First Use Connection Question - Mac OSX 10.5.6
or as the docs read:
shell mysqladmin password your password
John Daisley wrote:
The root Password will be blank after initial install.
You can set it at a shell prompt with commands something like this...
shell mysql -u root
mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql FLUSH PRIVILEGES;
mysql EXIT;
Where newpwd is your desired password.
Best to secure or delete all accounts with blank passwords :)
You can then start MySQL administrator and log in using the username root,
the password you specified in the SET PASSWORD command and the host of
localhost.
Regards
John
I am brand new to MySQL and JAVA/Netbeans 6.5 so please excuse the
stupid questions ...
1. I have just downloaded and successfully installed MySQL v5.1 on my
MacBook Pro running OS X 10.5.6
2. I have also downloaded and installed MySQL Tools: Administrator
Query Browser
(I come from a Visual Basic MS SQL Server 2000/2005 environment)
When you install MS SQL server the default login is sa with a blank
password.
My question is;
How do I login (connection settings) to MySQL (for Administrator and
Query Browser tools)?
(I start my server by going to settings and then MySQL icon, Start
Server, so my server is running) Unfortunately, I have never seen
MySQL in action nor do I know anyone to ask/show me how to get started.
__
This email has been scanned by Netintelligence
http://www.netintelligence.com/email
Newbie Question - MySQL Administrator
This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn’t available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff
Re: Newbie Question - MySQL Administrator
Jeff, For starters, it looks like you need a value for VARCHAR. Try the same statement but with VARCHAR(255) . On Fri, Feb 20, 2009 at 1:17 AM, Jeff Murdock jeff_murd...@yahoo.com wrote: This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn't available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
newbie config help needed
Hello all, I have set up a little server and am pitching to an organization to host their upcoming new site. The box is Centos 5.2 up to date and mysql 5.0.45. The requirements below must be met but I don't know how to verify their status. Can someone point me in the right direction. Will read directions if necessary Dave * Your server must have the following MySQL grant privileges (for tables): SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE * Your MySQL server must not be running in Strict mode. -- Canada must refuse to be entangled in any more wars fought to make the world safe for capitalism. -- The Regina Manifesto, 1933 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie config help needed
Dave, Log in as the user you intend to set up for them. Issue the following: show grants; select @@sql_mode; If you aren't sure how to interpret the information I will direct you to the manual: http://dev.mysql.com/doc/refman/5.0/en/ Side note: I would upgrade. 5.0.45 has some pretty serious bugs. I'd upgrade to the latest 5.0 release. Baron On Sat, Jan 31, 2009 at 4:13 PM, Dave Stevens g...@uniserve.com wrote: Hello all, I have set up a little server and am pitching to an organization to host their upcoming new site. The box is Centos 5.2 up to date and mysql 5.0.45. The requirements below must be met but I don't know how to verify their status. Can someone point me in the right direction. Will read directions if necessary Dave * Your server must have the following MySQL grant privileges (for tables): SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE * Your MySQL server must not be running in Strict mode. -- Canada must refuse to be entangled in any more wars fought to make the world safe for capitalism. -- The Regina Manifesto, 1933 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: newbie config help needed
Hi Dave, that's easy. Either install http://www.phpmyadmin.net/home_page/index.php on the server, the best tool to manage a mysql server. Or use XAMPP, a complete package from http://www.apachefriends.org/en/index.html Guenther Dave Stevens wrote: Hello all, I have set up a little server and am pitching to an organization to host their upcoming new site. The box is Centos 5.2 up to date and mysql 5.0.45. The requirements below must be met but I don't know how to verify their status. Can someone point me in the right direction. Will read directions if necessary Dave * Your server must have the following MySQL grant privileges (for tables): SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE * Your MySQL server must not be running in Strict mode. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Newbie question, how do I respond to post?
Hello, I feel stupid here... How do I respond to a specific post? I posted last night, I got a response... now I want to post a response to the response. Don't see a way to do that... Thanks, Lee ** Start the year off right. Easy ways to stay in shape. http://body.aol.com/fitness/winter-exercise?NCID=aolcmp0030002489
RE: Newbie question, how do I respond to post?
Either use reply to all, or manually enter the list email address in the to field. There are two general feelings about list operation: 1) all replies should go to the list 2) all replies should default to the sender This list is configured as #2 (I prefer #1 myself) Thanks, David Ruggles CCNA MCSE (NT) CNA A+ Network EngineerSafe Data, Inc. (910) 285-7200 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 23, 2008 10:19 AM To: mysql@lists.mysql.com Subject: Newbie question, how do I respond to post? Hello, I feel stupid here... How do I respond to a specific post? I posted last night, I got a response... now I want to post a response to the response. Don't see a way to do that... Thanks, Lee ** Start the year off right. Easy ways to stay in shape. http://body.aol.com/fitness/winter-exercise?NCID=aolcmp0030002489 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: A single number
Hi Mário, all ! Mário Gamito wrote (re-ordered): Sebastian Mendel wrote: Mário Gamito schrieb: Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved But instead i get two rows: |-- |comment_approved | COUNT (comment_agent) | |-- |0| 1 | |-- |1|47 | |-| Instead - maybe contrary to what you desire to get. But it is exactly what your command asks for: - Two columns (the expressions following SELECT) - for all different values of comment_approved. I've Google about it but found no answer (my bad, probably). The MySQL manual should be the better place for this, read about the SELECT statement. The exact URL to use depends on the version of MySQL you are running. [[...]] just include only the things you want in the SELECT also it seems you require only comment_agent LIKE '%Linux%' and NOT OR comment_approved=0 if you only need the '47' SELECT COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' GROUP by comment_approved Hi Sebastian, Thank you for your answer. I tried your way, but still, I get a column with two values: |- |COUNT(comment_agent)| || | 1| -- | 47| -- What I need is just the 47. Any ideas ? Plenty. Obviously you currently have a row in your data which satisfies comment_agent LIKE '%Linux%' and has comment_approved=0 . What makes the 47 so special ? It is the count which is associated with comment_approved=1 - right ? Then simply specify that: SELECT COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' AND comment_approved = 1 As now you specify a single value for comment_approved, there is no need any more to use grouping. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer 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]
Re: Newbie: A single number
Mário Gamito schrieb: Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved But instead i get two rows: |-- |comment_approved | COUNT (comment_agent) | |-- |0| 1 | |-- |1|47 | |-| I've Google about it but found no answer (my bad, probably). What I want is to have just 47 as a result of the SELECT. Any help would be appreciated. just include only the things you want in the SELECT also it seems you require only comment_agent LIKE '%Linux%' and NOT OR comment_approved=0 if you only need the '47' SELECT COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' GROUP by comment_approved -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: A single number
You have two rows the zero which has a single row and the other row If the answer you are after is 48. select sum(ct_ct_comment_agent) from (SELECT comment_approved, COUNT(comment_agent) as ct_comment_agent from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved )as a On Monday 21 January 2008 6:07:46 pm Mário Gamito wrote: Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved But instead i get two rows: |-- |comment_approved | COUNT (comment_agent) | |-- |0| 1 | |-- |1|47 | |-| I've Google about it but found no answer (my bad, probably). What I want is to have just 47 as a result of the SELECT. Any help would be appreciated. Warm regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: A single number
Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved But instead i get two rows: |-- |comment_approved | COUNT (comment_agent) | |-- |0| 1 | |-- |1|47 | |-| I've Google about it but found no answer (my bad, probably). What I want is to have just 47 as a result of the SELECT. Any help would be appreciated. Warm regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: A single number
Hi Sebastian, Thank you for your answer. I tried your way, but still, I get a column with two values: |- |COUNT(comment_agent)| || | 1| -- | 47| -- What I need is just the 47. Any ideas ? Warm Regards, Mário Gamito Sebastian Mendel wrote: Mário Gamito schrieb: Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved But instead i get two rows: |-- |comment_approved | COUNT (comment_agent) | |-- |0| 1 | |-- |1|47 | |-| I've Google about it but found no answer (my bad, probably). What I want is to have just 47 as a result of the SELECT. Any help would be appreciated. just include only the things you want in the SELECT also it seems you require only comment_agent LIKE '%Linux%' and NOT OR comment_approved=0 if you only need the '47' SELECT COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' GROUP by comment_approved -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: A single number
Hi Michael, I've tried your tip, but I get the error: #1054 - Unknown column 'ct_ct_comment_agent' in 'field list' Any ideas ? Warm Regards, Mário Gamito Michael Cole wrote: You have two rows the zero which has a single row and the other row If the answer you are after is 48. select sum(ct_ct_comment_agent) from (SELECT comment_approved, COUNT(comment_agent) as ct_comment_agent from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved )as a On Monday 21 January 2008 6:07:46 pm Mário Gamito wrote: Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved But instead i get two rows: |-- |comment_approved | COUNT (comment_agent) | |-- |0| 1 | |-- |1|47 | |-| I've Google about it but found no answer (my bad, probably). What I want is to have just 47 as a result of the SELECT. Any help would be appreciated. Warm regards, Mário Gamito -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie: A single number
I think this change will get you what you want: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' AND comment_approved=0 GROUP by comment_approved andy Mário Gamito wrote: Hi, I'm trying to get a single number out of a SELECT statement: SELECT comment_approved, COUNT(comment_agent) from wp_comments WHERE comment_agent LIKE '%Linux%' OR comment_approved=0 GROUP by comment_approved But instead i get two rows: |-- |comment_approved | COUNT (comment_agent) | |-- |0| 1 | |-- |1|47 | |-| I've Google about it but found no answer (my bad, probably). What I want is to have just 47 as a result of the SELECT. Any help would be appreciated. Warm regards, Mário Gamito -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Q- Mac OSX install - login to mysql not working...
(Sorry if this is a duplicate post, email issues w/multiple accts) I have a MacBook Leopard OS 10.5.1 2 GB RAM. I've got a good amount of knowledge of computer systems, but haven't setup or worked with mySQL before. (usually use it preinstalled on my hosting service). I downloaded the package and installed it via the automated setup that comes for the MacOS. It started fine. The MySQL Sys prefs tool shows the status of the server as running. However, I want to now setup Wordpress on my localhost. I need to create a database on mysql. I opened the shell and navigate to the location of mysql. I have been using the instructions as shown below. $ mysql -u adminusername -p Enter password: This is where I'm stuck. No matter what account I use, I am unable to login to the database. My personal account is the only account on the machine, while it's not root, it has full admin privs, and I've tried everything to login, including no password, my account's password, etc. Since I've not activated root on this machine (I understand that Apple, by default disables it), perhaps there's something I'm missing in my lack of knowledge of the way the Mac OS uses root? I'm sure this is a stupid newbie issue, but I'm a bit wrapped around the axle here. Do I need to use sudo? (I've even attempted this with no success). I've searched on a few lists to see if anyone has had this problem, but not seen it yet. Should I just reinstall? Am I just missing something that passed by in install (I don't remember adding a password other than entering the password that I use to admin the machine. Thoughts? Thanks in advance for any help.
Re: Newbie Q- Mac OSX install - login to mysql not working...
On Dec 13, 2007 12:11 PM, Al [EMAIL PROTECTED] wrote: (Sorry if this is a duplicate post, email issues w/multiple accts) I have a MacBook Leopard OS 10.5.1 2 GB RAM. I've got a good amount of knowledge of computer systems, but haven't setup or worked with mySQL before. (usually use it preinstalled on my hosting service). I downloaded the package and installed it via the automated setup that comes for the MacOS. It started fine. The MySQL Sys prefs tool shows the status of the server as running. However, I want to now setup Wordpress on my localhost. I need to create a database on mysql. I opened the shell and navigate to the location of mysql. I have been using the instructions as shown below. $ mysql -u adminusername -p Enter password: This is where I'm stuck. No matter what account I use, I am unable to login to the database. My personal account is the only account on the machine, while it's not root, it has full admin privs, and I've tried everything to login, including no password, my account's password, etc. Since I've not activated root on this machine (I understand that Apple, by default disables it), perhaps there's something I'm missing in my lack of knowledge of the way the Mac OS uses root? I'm sure this is a stupid newbie issue, but I'm a bit wrapped around the axle here. Do I need to use sudo? (I've even attempted this with no success). I've searched on a few lists to see if anyone has had this problem, but not seen it yet. Should I just reinstall? Am I just missing something that passed by in install (I don't remember adding a password other than entering the password that I use to admin the machine. Thoughts? Thanks in advance for any help. No idea about the particulars of OSX, however http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix should work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question connecting with windows named pipes (resolved)
Thanks Sebastian, that did the trick.
The full command I use is:
$mysqli = new mysqli(., $username,$password,
$database,null,/tmp/mysql.sock);
Regards,
JC
Sebastian Mendel wrote:
John Comerford schrieb:
Hi Folks,
I have a database running on Window XP, that I want to disable network
connections to and enable 'named pipes'. I am running MySQL 5.0.27
and my.ini looks like...
[...]
I can connect to the DB using the GUI tools if I set my pipe name to
'/tmp/mysql.sock' using the login dialog box.
[...]
mysql_connect('.', ...);
you have to a dot as host: '.'
Re: Newbie Question connecting with windows named pipes (resolved)
John Comerford schrieb:
Thanks Sebastian, that did the trick.
The full command I use is:
$mysqli = new mysqli(., $username,$password,
$database,null,/tmp/mysql.sock);
there is no need for the socket, their are no sockets on windows
$mysqli = new mysqli('.', $username, $password, $database);
--
Sebastian Mendel
www.sebastianmendel.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question connecting with windows named pipes (resolved)
Hi Sebastian,
I did read somewhere that sockets was not valid for windows, and
'/tmp/mysql.sock' doesn't resolve to anything on my machine. But I have
tried the following commands:
$mysqli = new mysqli(., $username,$password, $database);
or
$mysqli = new mysqli(., $username,$password, $database,null);
and I get the following error:
Can't open named pipe to host: . pipe: MySQL (2)
but when I include the /tmp/mysql.sock parameter it works fine. I was
thinking that maybe I have things configured in such a way that MySQL is
using /tmp/mysql.sock as the pipe name. I am going to do some testing
this evening to see if my suspicion is correct.
Thanks again,
John
Sebastian Mendel wrote:
John Comerford schrieb:
Thanks Sebastian, that did the trick.
The full command I use is:
$mysqli = new mysqli(., $username,$password,
$database,null,/tmp/mysql.sock);
there is no need for the socket, their are no sockets on windows
$mysqli = new mysqli('.', $username, $password, $database);
Re: Newbie Question connecting with windows named pipes (resolved)
John Comerford schrieb: Hi Sebastian, I did read somewhere that sockets was not valid for windows, and '/tmp/mysql.sock' doesn't resolve to anything on my machine. But I have tried the following commands: $mysqli = new mysqli(., $username,$password, $database); or $mysqli = new mysqli(., $username,$password, $database,null); and I get the following error: Can't open named pipe to host: . pipe: MySQL (2) yes, 'MySQL' is default name of the pipe, used by the libaray but when I include the /tmp/mysql.sock parameter it works fine. I was thinking that maybe I have things configured in such a way that MySQL is using /tmp/mysql.sock as the pipe name. I am going to do some testing this evening to see if my suspicion is correct. yes: Note: you can specify a pipe name on the advanced network page, if required. possible this is et with the soccket paramter in my.cnf [mysqld] #port= 3306 socket= /tmp/mysql.sock -- Sebastian Mendel www.sebastianmendel.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question connecting with windows named pipes (resolved)
Hi Sebastian, That was it alright. I commented out the socket lines from mysql.ini (restarted it) and changed my php command to: $mysqli = new mysqli(., $username,$password, $database); and all seems to be working fine now. Thanks again for your help, much appreciated. Regards, John Sebastian Mendel wrote: John Comerford schrieb: Hi Sebastian, I did read somewhere that sockets was not valid for windows, and '/tmp/mysql.sock' doesn't resolve to anything on my machine. But I have tried the following commands: $mysqli = new mysqli(., $username,$password, $database); or $mysqli = new mysqli(., $username,$password, $database,null); and I get the following error: Can't open named pipe to host: . pipe: MySQL (2) yes, 'MySQL' is default name of the pipe, used by the libaray but when I include the /tmp/mysql.sock parameter it works fine. I was thinking that maybe I have things configured in such a way that MySQL is using /tmp/mysql.sock as the pipe name. I am going to do some testing this evening to see if my suspicion is correct. yes: Note: you can specify a pipe name on the advanced network page, if required. possible this is et with the soccket paramter in my.cnf [mysqld] #port= 3306 socket= /tmp/mysql.sock -- 1^st Floor, 184 -186 Glenferrie Road, Malvern VIC 3144 PH:*(03) 9500 1466* FX :*(03) 9500 1469* Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Web: www.styleman.com.au http://www.styleman.com.au The information in this e-mail is confidential and is intended solely for the addressee. Any views or opinions presented are solely those of the author and do not necessarily represent those of Option Systems Pty Ltd. If you are not the intended recipient, please delete this message and contact the sender.
Newbie Question connecting with windows named pipes
Hi Folks, I have a database running on Window XP, that I want to disable network connections to and enable 'named pipes'. I am running MySQL 5.0.27 and my.ini looks like... [client] #password= your_password port= 3306 socket= /tmp/mysql.sock [mysqld] #port= 3306 socket= /tmp/mysql.sock #Allow connections via named pipes (Windows NT+ only). Note: you can specify a pipe name on the advanced network page, if required. enable-named-pipe #Don't allow connections via TCP/IP. skip-networking I can connect to the DB using the GUI tools if I set my pipe name to '/tmp/mysql.sock' using the login dialog box. However when I try and connect using PHP I get an error. I have tried several variants of the connect command and I get various errors but all are along the lines of: Unknown MySQL server host '/tmp/mysql.sock' (11004) or Can't connect to MySQL server on 'localhost' (10061) I have tried $mysqli = new mysqli(null, $username,$password, $database); $mysqli = new mysqli(localhost:/tmp/mysql.sock, $username,$password, $database); $mysqli = new mysqli(localhost, $username,$password, $database,3306,/tmp/mysql.sock); $mysqli = new mysqli(localhost, $username,$password, $database,/tmp/mysql.sock); $mysqli = new mysqli(/tmp/mysql.sock, $username,$password, $database); I have also tried the above commands using mysqli_connect ? I have done a few searches of the web but seem to always come up with something like the above? Anybody have any ideas why it won't connect in PHP ? I know this is not a PHP forum, but I am wondering if I have something wrong in my MySQL setup ? TIA, JC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Question connecting with windows named pipes
John Comerford schrieb:
Hi Folks,
I have a database running on Window XP, that I want to disable network
connections to and enable 'named pipes'. I am running MySQL 5.0.27
and my.ini looks like...
[...]
I can connect to the DB using the GUI tools if I set my pipe name to
'/tmp/mysql.sock' using the login dialog box.
[...]
mysql_connect('.', ...);
you have to a dot as host: '.'
--
Sebastian Mendel
www.sebastianmendel.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with initdb script in lxr installation - newbie needs help
This is the exact error that mySQL reported: ERROR 1064 (42000): You have an error in you sql syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'release char(255) bunary not null, primary key (fileid,relea' at line 3 MySQL server version is 5.0.22. The script requires 4.x. If Fedora core 6 allows without dependency issues I have no problem with 4.x. Anyways I think it would be better if the script can be corrected because it will be useful for the majority newbies like me. On 5/7/07, Michael Dykman [EMAIL PROTECTED] wrote: perhaps you might share with us what error you are getting and what version of MyQSL you are using? The more context you can suply, the better. On 5/7/07, Shahbaz Khan [EMAIL PROTECTED] wrote: This portion of the script gives error. reate table lxr_releases (fileid int not null references lxr_files, release char(255) binary not null, primary key (fileid,release) The script is present as attachment if needed. I suspect this script is for mysql 4.x version and if so what needs to be done? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with initdb script in lxr installation - newbie needs help
RELEASE is a reserved word, and if used as column-name, it must be quoted (back-tick) `release` char(255) . On Tue, May 8, 2007 12:09, Shahbaz Khan wrote: This is the exact error that mySQL reported: ERROR 1064 (42000): You have an error in you sql syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'release char(255) bunary not null, primary key (fileid,relea' at line 3 MySQL server version is 5.0.22. The script requires 4.x. If Fedora core 6 allows without dependency issues I have no problem with 4.x. Anyways I think it would be better if the script can be corrected because it will be useful for the majority newbies like me. On 5/7/07, Michael Dykman [EMAIL PROTECTED] wrote: perhaps you might share with us what error you are getting and what version of MyQSL you are using? The more context you can suply, the better. On 5/7/07, Shahbaz Khan [EMAIL PROTECTED] wrote: This portion of the script gives error. reate table lxr_releases (fileid int not null references lxr_files, release char(255) binary not null, primary key (fileid,release) The script is present as attachment if needed. I suspect this script is for mysql 4.x version and if so what needs to be done? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with initdb script in lxr installation - newbie needs help
This portion of the script gives error. reate table lxr_releases (fileid int not null references lxr_files, release char(255) binary not null, primary key (fileid,release) The script is present as attachment if needed. I suspect this script is for mysql 4.x version and if so what needs to be done? /* Read this into mysql with \. initdb-mysql when logged in as root to delete the old lxr database and create a new */ drop database if exists lxr; create database lxr; use lxr; /* symnum filenum */ create table lxr_files ( filenamechar(255) binary not null, revisionchar(255) binary not null, fileid int not null auto_increment, primary key (fileid) /*, unique (filename, revision) */ ); create table lxr_symbols ( symname char(255) binary not null, symid int not null auto_increment, primary key (symid), unique (symname) ); create table lxr_indexes ( symid int not null references lxr_symbols, fileid int not null references lxr_files, lineint not null, langid tinyint not null references lxr_declarations, typesmallint not null references lxr_declarations, relsym int references lxr_symbols ); create table lxr_releases (fileid int not null references lxr_files, release char(255) binary not null, primary key (fileid,release) ); create table lxr_useage (fileid int not nullreferences lxr_files, lineint not null, symid int not nullreferences lxr_symbols ); create table lxr_status (fileid int not null references lxr_files, status tinyint not null, primary key (fileid) ); create table lxr_declarations (declid smallint not null auto_increment, langid tinyint not null, declarationchar(255) not null, primary key(declid, langid) ); create index lxr_indexindex on lxr_indexes (symid) ; create unique index lxr_symbolindex on lxr_symbols (symname) ; create index lxr_useageindex on lxr_useage (symid) ; create index lxr_filelookup on lxr_files (filename); grant all on lxr.* to [EMAIL PROTECTED]; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complete newbie (OSX Server - MySQL)
Do you have shell access to this server? Try to access MySQL by using command: mysql and then do: show databases; You could also try to use root as username and leave the password empty. If it works then it would mean that your MySQL is not setup with any security or anything. Did you create any database before you started the installation of the script? There might be required that you create the table site and the user site before you do anything. Just some thoughts... /Peter -Original Message- From: Scott Yamahata [mailto:[EMAIL PROTECTED] Sent: Sunday, December 24, 2006 4:29 AM To: mysql@lists.mysql.com Subject: Complete newbie (OSX Server - MySQL) Complete newbie here. Purchased software for my OSX Server 10.4.8 to use with MySQL and PHP. I get an installation window that opens and asks for Host: (with a default of localhost). User: site Password: (the one the person who sold me the script provided). Database: site License key: (the one the person who sold me the script provided). I hit the save button and get the following: Warning: mysql_connect(): Access denied for user 'site'@'localhost' (using password: YES) in /Library/WebServer/Documents/!install/install.php on line 298 Could not connect to the Database Questions: 1. I don't know if I have to do anything to MySQL to add the User and Database or whether the scripts supposed to do it for me. 2. I don't know if I'm supposed to use localhost or greenheartworld.com, which was the domain that I supplied to him (but want to change). Any help is greatly appreciated. Thanks, Scott _ Get FREE Web site and company branded e-mail from Microsoft Office Live http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ -- 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]
Complete newbie (OSX Server - MySQL)
Complete newbie here. Purchased software for my OSX Server 10.4.8 to use with MySQL and PHP. I get an installation window that opens and asks for Host: (with a default of localhost). User: site Password: (the one the person who sold me the script provided). Database: site License key: (the one the person who sold me the script provided). I hit the save button and get the following: Warning: mysql_connect(): Access denied for user 'site'@'localhost' (using password: YES) in /Library/WebServer/Documents/!install/install.php on line 298 Could not connect to the Database Questions: 1. I don't know if I have to do anything to MySQL to add the User and Database or whether the scripts supposed to do it for me. 2. I don't know if I'm supposed to use localhost or greenheartworld.com, which was the domain that I supplied to him (but want to change). Any help is greatly appreciated. Thanks, Scott _ Get FREE Web site and company branded e-mail from Microsoft Office Live http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie - View several databases as one
Hello, I have an application that stores the information in several MySql databases and I am starting to look at the information and I found a problem. Information is stored everyday in threed different sessions. Each session creates a new database with several tables in it. The problem is that i don't care about sessions and I would like to view all the information that is soted as it would be a unique database. Somebody told me that this could be done with MySQL but as he was no an expert he was not able to give more details. Does anyone now if there is any feature that allows to do so? thnaks in advance. -- View this message in context: http://www.nabble.com/Newbie---View-several-databases-as-one-tf2697982.html#a7524188 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - View several databases as one
On 11/24/06, Algomez [EMAIL PROTECTED] wrote: Hello, I have an application that stores the information in several MySql databases and I am starting to look at the information and I found a problem. Information is stored everyday in threed different sessions. Each session creates a new database with several tables in it. The problem is that i don't care about sessions and I would like to view all the information that is soted as it would be a unique database. Somebody told me that this could be done with MySQL but as he was no an expert he was not able to give more details. Does anyone now if there is any feature that allows to do so? I don't know of any feature like that, in fact, from what you said I can say there's something VERY wrong about your DB model, separate databases are (AFAIK) suppose to separate data that's not interconnected. You can always use more than one connection at your application and treat the results in order to join them, but that's not really a fix and can greatly impact the speed of the process. Other than that, I don't think there's a way to do this... Maybe someone with more knowledge can contribute. Anyway, I'm not an expert, but a great friend is a DBA and he once told me that if you have to CODE the creation of a database for any purpose besides restoring a backup, something is wrong. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie help db locked err is The table '#sql_2c52_0' is full [ag]
Hy thank you all, I'm getting the following err msg - The table '#sql_2c52_0' is full running a complex query (and only running that query) with three outer join it is a simple db that stores mail msgs my query is about mail msg-recipients-sender the biggest table mail_archive has 90467 rows I checked every things I know: -disk space tables integrity isam check etc... my server is- server version: 4.0.24_Debian-10sarge2-log all tables are myISAM tables this snippets is in csv format output from command: SHOW TABLE STATUS FROM mydbLIKE 'mail%' mysql Name,Type,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Create_options,Comment mail_archive,MyISAM,Dynamic,90467,4307,389686660,4294967295,2044928,0,102183,2006-10-01 02:38:34,2006-10-01 23:15:31,2006-10-01 02:43:55,, mail_attachment_archive,MyISAM,Dynamic,47107,47,2233816,4294967295,1024,0,,2006-01-20 09:53:26,2006-10-01 23:15:31,,, mail_recipients,MyISAM,Dynamic,682960,51,34918212,4294967295,5704704,19044,,2006-01-20 09:53:26,2006-10-01 23:15:31,,, mail_senders,MyISAM,Dynamic,49247,50,2497204,4294967295,421888,3560,,2006-01-20 09:53:26,2006-10-01 23:15:31,2006-03-23 23:53:08,, any help would be appreciate. TIA ag. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie help db locked err is The table '#sql_2c52_0' is full [ag]
Adriano - MySQL names temp tables like that (starting with # sign), in paticular while storing a result set to be sorted. I would guess that your query is returning a result set large enough to hit a filesystem limit on your box. How big are you expecting the results to be? Perhaps you are missing a join, or have a malformed query in some way, and therefore producing a cartesian product. You could try removing any sort you have on the data to see if the query will return results that way, since MySQL won't attempt to sort the data and therefore may not fill up a temp table. You could also try a LIMIT x on your statement to see if your query is returning what you expect. Finally, it is very helpful in this forum if you post your table structures, your actual query as well as the output you get from running EXPLAIN query; Best, Dan On 10/21/06, adriano ghezzi [EMAIL PROTECTED] wrote: Hy thank you all, I'm getting the following err msg - The table '#sql_2c52_0' is full running a complex query (and only running that query) with three outer join it is a simple db that stores mail msgs my query is about mail msg-recipients-sender the biggest table mail_archive has 90467 rows I checked every things I know: -disk space tables integrity isam check etc... my server is- server version: 4.0.24_Debian-10sarge2-log all tables are myISAM tables this snippets is in csv format output from command: SHOW TABLE STATUS FROM mydbLIKE 'mail%' mysql Name,Type,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Create_options,Comment mail_archive,MyISAM,Dynamic,90467,4307,389686660,4294967295,2044928,0,102183,2006-10-01 02:38:34,2006-10-01 23:15:31,2006-10-01 02:43:55,, mail_attachment_archive,MyISAM,Dynamic,47107,47,2233816,4294967295,1024,0,,2006-01-20 09:53:26,2006-10-01 23:15:31,,, mail_recipients,MyISAM,Dynamic,682960,51,34918212,4294967295,5704704,19044,,2006-01-20 09:53:26,2006-10-01 23:15:31,,, mail_senders,MyISAM,Dynamic,49247,50,2497204,4294967295,421888,3560,,2006-01-20 09:53:26,2006-10-01 23:15:31,2006-03-23 23:53:08,, any help would be appreciate. TIA ag. -- 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(2): newbie help db locked err is The table '#sql_2c52_0' is full [ag]
before all thank you for your answer, next while waiting for an answ3er I backup the db then tried to convert tables in Inno db format everything run fine now, the same query execute ok but i prefer myIsam format i use it as default, then I'd like to understand what is the problem, here are all infos you requested, thnks again for your time; the query is right when there were few rows worked fine, actually just to i set limit 10 but nothing same error these are th tables; mysql explain mail_archive; +--+--+--+-++--- -+ | Field| Type | Null | Key | Default| Extra | +--+--+--+-++--- -+ | message_id | int(11) | | PRI | NULL | auto_increment | | message_file | varchar(150) | | MUL || | | subject | varchar(150) | | || | | date | varchar(100) | | || | | real_date| date | | | -00-00 | | | headers | text | | || | | body | text | | || | | body_html| text | | || | | flag | smallint(5) unsigned | | | 0 | | +--+--+--+-++--- - mysql explain mail_recipients; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | foxbox_user_id | int(11) | | | 0 | | | message_id | int(11) | | MUL | 0 | | | name | varchar(100) | | | | | | email | varchar(100) | | | | | ++--+--+-+-+---+ mysql explain mail_senders; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | foxbox_user_id | int(11) | | | 0 | | | message_id | int(11) | | MUL | 0 | | | name | varchar(100) | | | | | | email | varchar(100) | | | | | ++--+--+-+-+---+ mysql explain mail_attachment_archive; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | message_id | varchar(150) | | | 0 | | | file_name | varchar(150) | | | | | | type | varchar(100) | | | | | ++--+--+-+-+---+ and finally this is the query SELECT mail_senders.name AS mitt, mail_recipients.name AS dest, mail_archive.message_id, mail_archive.subject, mail_archive.message_file, mail_archive.date, mail_archive.real_date, mail_archive.* FROM (mail_archive INNER JOIN mail_recipients ON mail_archive.message_id = mail_recipients.message_id) INNER JOIN mail_senders ON mail_archive.message_id = mail_senders.message_id order by message_id desc limit 10 it is also my thought the problem is in anyway related to space, but can't understand were and which setting is related to this 4 2006/10/21, Dan Buettner [EMAIL PROTECTED]: Adriano - MySQL names temp tables like that (starting with # sign), in paticular while storing a result set to be sorted. I would guess that your query is returning a result set large enough to hit a filesystem limit on your box. How big are you expecting the results to be? Perhaps you are missing a join, or have a malformed query in some way, and therefore producing a cartesian product. You could try removing any sort you have on the data to see if the query will return results that way, since MySQL won't attempt to sort the data and therefore may not fill up a temp table. You could also try a LIMIT x on your statement to see if your query is returning what you expect. Finally, it is very helpful in this forum if you post your table structures, your actual query as well as the output you get from running EXPLAIN query; Best, Dan On 10/21/06, adriano ghezzi [EMAIL PROTECTED] wrote: Hy thank you all, I'm getting the following err msg - The table '#sql_2c52_0' is full
Newbie
Hi All, I am very new. I wish to do some exercise before I go to real business. Could anyone advise me any tutorial link or resources for beginner? With best regards, Abu Naser School Of Life Sciences Heriot-Watt University Edinburgh EH14 4AS Email: [EMAIL PROTECTED] Phone: +44(0)1314518265 Fax : +44(0) 131 451 3009
Re: Newbie
Naser, Md Abu wrote: Hi All, I am very new. I wish to do some exercise before I go to real business. Could anyone advise me any tutorial link or resources for beginner? Some tutorials are listed at http://www.artfulsoftware.com/dbresources.html. Also you might want to look at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.html. PB Naser, Md Abu wrote: Hi All, I am very new. I wish to do some exercise before I go to real business. Could anyone advise me any tutorial link or resources for beginner? With best regards, Abu Naser School Of Life Sciences Heriot-Watt University Edinburgh EH14 4AS Email: [EMAIL PROTECTED] Phone: +44(0)1314518265 Fax : +44(0) 131 451 3009 No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/401 - Release Date: 7/26/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/401 - Release Date: 7/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie..foreign key clarification
I'm still unsure about foreign keys..even after reading the doc file and Paul Dubois 3rd edition My doubt is...how much constraint is applied.( probably a bad explanation) If i have a table 'Customers' with the primary key being 'CustID' Then i have a table 'LastVisit' with a foreign key 'CustID' btw...i have a Form-SubForm in OO that i'm working with where i have the 2 tables linked should the foreign key 'LastVisit.CustID' only allow values that are in the linked primary field? or will it allow any value that is in the table ' Customers.CustID' ? When i enter any value that doesn't exist in the 'Customers.CustID' column i get the ref. integrity error...BUT i want it to kick ANY value that doesn't relate to the linked parent table out as an error. Pretend i didn't mention OO. -- Grass Cake
Re: newbie..foreign key clarification
Grass Cake wrote: I'm still unsure about foreign keys..even after reading the doc file and Paul Dubois 3rd edition My doubt is...how much constraint is applied.( probably a bad explanation) If i have a table 'Customers' with the primary key being 'CustID' Then i have a table 'LastVisit' with a foreign key 'CustID' btw...i have a Form-SubForm in OO that i'm working with where i have the 2 tables linked should the foreign key 'LastVisit.CustID' only allow values that are in the linked primary field? or will it allow any value that is in the table ' Customers.CustID' ? When i enter any value that doesn't exist in the 'Customers.CustID' column i get the ref. integrity error...BUT i want it to kick ANY value that doesn't relate to the linked parent table out as an error. It will check to make sure the CustID value is in the Customers table. If it's not there, then it will give you an error. If it is there, the insert/update will work fine. It has no way of knowing if you are inserting the right record, all it cares about is that the CustID value is in the other table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie needs help
Hi All,
I'm going throught some tutorial about uploading and displaying
images files. But the display script isn't working. Here's what I have:
Table setup is:
CREATE TABLE `image` (
`ImageId` int(10) NOT NULL auto_increment,
`Image` longblob,
`FileType` varchar(32) default NULL,
PRIMARY KEY (`ImageId`)
)
Add image script (addimage.php) is:
?php // add image script
if ($_POST['Submit']) {
if ($_POST['MAX_FILE_SIZE'] = $_FILES['file']['size']) {
//print_r($_FILES);
include ('includes/mysql_connect.php'); // connect to db
$photo = addslashes(fread(fopen($_FILES['file']['tmp_name'], r),
$_FILES['file']['size']));
$query = sprintf(INSERT INTO image(Image, FileType) VALUES ('%s',
'%s'), $photo, $_FILES['file']['type']);
if (mysql_query($query)) {
$messages[] = Your files is successfully store in database;
} else {
$messages[]= mysql_error();
}
} else {
$messages[] = The file is bigger than the allowed size (96k) please
reduce your file size;
}
}
?
html
head
titleAdd Image/title
/head
body
?
if (isset($messages)) {
foreach ($messages as $message) {
print $message .br;
}
}
?
form action= method=post enctype=multipart/form-data name=form1
input type=file name=file
input type=hidden name=MAX_FILE_SIZE value=96000
input type=submit name=Submit value=Submit
/form
/body
/html
And imageloader.php is:
?php // imageloader.php
include ('includes/mysql_connect.php');
$result = mysql_query(SELECT ImageId from image);
while ($row = mysql_fetch_array($result)) {
$ids[]=$row['ImageId'];
}
?
html
head
titleImage Loader/title
/head
body
select image:br
table width=80% border=0 cellspacing=0 cellpadding=0
tr
td width=10%id/td
td width=90%Image/td
/tr
tr
td valign=top
table width=100% border=0
cellspacing=0 cellpadding=0
? foreach ($ids as $id) { ?
tr
tda href=?id=?= $id;
??= $id; ?/a/td
/tr
? } ?
/table
/td
td? if (isset($_GET['id'])) { ?img
src=image.php?id=?= $_GET['id']; ?? } ?/td
/tr
/table
/body
/html
And finally image.php is
?php // image.php
include ('includes/mysql_connect.php'); // connect to db
$result = mysql_query(sprintf(SELECT * from image WHERE ImageId =
%d, $_GET['id']));
$row = mysql_fetch_array($result);
header(sprintf(Content-type: %s, $row['FileType']));
echo Here's the picture: :, $row['Image'];
?
When I click on the individual image id, the actual image won't
show. Can someone tell me what am I missing here?
Much thanks.
Kay
Re: newbie needs help
I'm going throught some tutorial about uploading and displaying images files. But the display script isn't working. Here's what I have: I think you may want to bring this to a php based list, not a mysql one. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie needs help
At 04:22 PM 7/21/2006 Friday, Scott Haneda wrote: I'm going throught some tutorial about uploading and displaying images files. But the display script isn't working. Here's what I have: I think you may want to bring this to a php based list, not a mysql one. Yes, it's cross-posted. I just thought to try it here. Kay -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie needs help
While this is offtopic, just a side note: You probably want to store your images on disk with the filename in the database rather than the actual image binary data in the db. Filesystems are very good at storing and retreiving chunks of binary. Databases do it because... well... I'm not really sure why. Mark. On 7/21/06, Scott Haneda [EMAIL PROTECTED] wrote: I'm going throught some tutorial about uploading and displaying images files. But the display script isn't working. Here's what I have: I think you may want to bring this to a php based list, not a mysql one. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Maunder [EMAIL PROTECTED] http://www.markmaunder.com/ +1-206-6978723 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Rob Desbois schrieb: To those who responded - read the question. He wants to combine the values from the data column of *2* rows into one, not just a straightforward string concatenation. Sorry but you want me to write the whole SQL query? He has to use his brain. Grouping and joining the tables. I'm not here for doing your or his work! Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re[2]: Newbie - CREATE VIEW Question
Sorry but you want me to write the whole SQL query? He has to use his brain. Grouping and joining the tables. I'm not here for doing your or his work! Barry, I agree that it's often better to point someone in the right direction rather than just writing the query for them, but in this case it was a newbie question. From where I saw it, the difficulty was in concatenating values from 2 rows, not the concatenation itself. That is why I thought your response was not sufficient. Apologies if I caused any offense. --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Rob Desbois schrieb: Sorry but you want me to write the whole SQL query? He has to use his brain. Grouping and joining the tables. I'm not here for doing your or his work! Barry, I agree that it's often better to point someone in the right direction rather than just writing the query for them, but in this case it was a newbie question. And therefore it's most important that he tries to learn how to look at the doc. Or your newbies will start asking every shit on List because the don't know what to do else. From where I saw it, the difficulty was in concatenating values from 2 rows, not the concatenation itself. That is why I thought your response was not sufficient. Apologies if I caused any offense. Well it was to be exact concating 2 tables with their rows. Well your post was also not sufficient, because you didn't helped him at all, too. So what do we learn about this? Nothing. It's internet! Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re[2]: Newbie - CREATE VIEW Question
I agree that it's often better to point someone in the right direction rather than just writing the query for them, but in this case it was a newbie question. And therefore it's most important that he tries to learn how to look at the doc. Remembering my own troubles learning MySQL, it can be difficult to know *what* to search for - if this person is completely new to SQL, the concept of joining a table to itself might not occur. Or your newbies will start asking every shit on List because the don't know what to do else. I know, it is annoying answering questions just because someone can't be bothered. From where I saw it, the difficulty was in concatenating values from 2 rows, not the concatenation itself. That is why I thought your response was not sufficient. Apologies if I caused any offense. Well it was to be exact concating 2 tables with their rows. Well your post was also not sufficient, because you didn't helped him at all, too. Yes, I know my post didn't answer the question either :) I mailed because I thought you might've misunderstood the original question and thought it was just about how to concatenate two strings, rather than the more difficult joining part. --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Rob Desbois schrieb: I agree that it's often better to point someone in the right direction rather than just writing the query for them, but in this case it was a newbie question. And therefore it's most important that he tries to learn how to look at the doc. Remembering my own troubles learning MySQL, it can be difficult to know *what* to search for - if this person is completely new to SQL, the concept of joining a table to itself might not occur. That's why i gave a hint of where to look. Or your newbies will start asking every shit on List because the don't know what to do else. I know, it is annoying answering questions just because someone can't be bothered. I don't answer them. It's just annyoning to see the list overflowing with posts that had been easily done looking at the docs for a few minutes. From where I saw it, the difficulty was in concatenating values from 2 rows, not the concatenation itself. That is why I thought your response was not sufficient. Apologies if I caused any offense. Well it was to be exact concating 2 tables with their rows. Well your post was also not sufficient, because you didn't helped him at all, too. Yes, I know my post didn't answer the question either :) I mailed because I thought you might've misunderstood the original question and thought it was just about how to concatenate two strings, rather than the more difficult joining part. Well more difficult is relative. Did not misunderstood. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie - CREATE VIEW Question
Say I have the following tables; siteID,name -- site1, XYZ site2, RSQ ID,site,data 1, site1, M 2, site2, Q 3, site2, Y 4, site1, P ... etc. And I want to create a view like this; siteID,name,data -- site1, XYZ, (M,P) site2, RSQ, (Q,Y) where all the related column data in the second table is placed in another column. How can I do this? Is there a function that can group these values into one variable or array? Thank you -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5168593 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - CREATE VIEW Question
Search the Manual for CONCAT. SELECT /Peter -Original Message- From: z247 [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 10:02 PM To: mysql@lists.mysql.com Subject: Newbie - CREATE VIEW Question Say I have the following tables; siteID,name -- site1, XYZ site2, RSQ ID,site,data 1, site1, M 2, site2, Q 3, site2, Y 4, site1, P ... etc. And I want to create a view like this; siteID,name,data -- site1, XYZ, (M,P) site2, RSQ, (Q,Y) where all the related column data in the second table is placed in another column. How can I do this? Is there a function that can group these values into one variable or array? Thank you -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5168593 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
z247 schrieb:
Say I have the following tables;
siteID,name
--
site1, XYZ
site2, RSQ
ID,site,data
1, site1, M
2, site2, Q
3, site2, Y
4, site1, P
... etc.
And I want to create a view like this;
siteID,name,data
--
site1, XYZ, (M,P)
site2, RSQ, (Q,Y)
where all the related column data in the second table is placed in another
column. How can I do this? Is there a function that can group these values
into one variable or array?
Thank you
CONCAT_WS(', ',siteID,name,data)
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re[2]: Newbie - CREATE VIEW Question
z247 schrieb:
Say I have the following tables;
siteID,name
--
site1, XYZ
site2, RSQ
ID,site,data
1, site1, M
2, site2, Q
3, site2, Y
4, site1, P
... etc.
And I want to create a view like this;
siteID,name,data
--
site1, XYZ, (M,P)
site2, RSQ, (Q,Y)
where all the related column data in the second table is placed in another
column. How can I do this? Is there a function that can group these values
into one variable or array?
Thank you
CONCAT_WS(', ',siteID,name,data)
Barry
To those who responded - read the question.
He wants to combine the values from the data column of *2* rows into one, not
just a straightforward string concatenation.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Hi, Say I have the following tables; siteID,name -- site1, XYZ site2, RSQ ID,site,data 1, site1, M 2, site2, Q 3, site2, Y 4, site1, P ... etc. And I want to create a view like this; siteID,name,data -- site1, XYZ, (M,P) site2, RSQ, (Q,Y) where all the related column data in the second table is placed in another column. How can I do this? Is there a function that can group these values into one variable or array? Requires 4.1 or higher: SELECT table1.siteID, table1.name, GROUP_CONCAT(table2.data SEPARATOR ,) AS all_data FROM table1 JOIN table2 ON table1.siteID=table2.site GROUP BY table1.siteID Regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Thank you! -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5171108 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - CREATE VIEW Question
Hi, the GROUP_CONCAT worked. Thank you. However, I'm getting duplicates in the all_data column. Is there a function like array_unique in PHP to remove these duplicates? I tried DISTINCT but that did not work. Thank you. -- View this message in context: http://www.nabble.com/NewbieCREATE-VIEW-Question-tf1890326.html#a5171910 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
faster query (newbie)
I have a query statement like this select hiart01a.cust, hiarf01.nama, hiarf01.al2, hiart01a.tgl, hiart01a.netto, hiart01a.muka from hiart01a, hiarf01 where hiart01a.tgl=from_days(to_days(now()) - 180) and hiart01a.tgl=from_days(to_days(now()) - 30) order by hiart01a.cust; when I run this statement, it takes more than 2 hours. Detail information: -. there are 200K rows in both tables (hiart01a, hiarf01) -. I am using mysql 5.019 -. I am running on testing server (pentium 4, 3GHz, RAM 1GB) and the system variables like this innodb_additional_mem_pool_size 25165824 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_additional_mem_pool_size 25165824 innodb_autoextend_increment8 innodb_buffer_pool_awe_mem_mb 0 nbsp; innodb_buffer_pool_size 734003200 innodb_log_buffer_size 4194304 nbsp; innodb_log_file_size17825792 innodb_open_files 300 innodb_thread_concurrency 100 max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connect_errors10 max_connections 800 max_delayed_threads 20 max_error_count64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 4294967295 max_length_for_sort_data1024 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_tmp_tables32 max_user_connections 0 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_typeON read_buffer_size 61440 I am wondering there is a way to make the query process faster. I am looking forward to a favorable reply from you. Thank you. Regards, Eko - Yahoo! Sports Fantasy Football 06 - Go with the leader. Start your league today!
Re: faster query (newbie)
Eko, you're not JOINing the two tables together - your database is taking the time to produce a huge result set known as a cartesian product, which is probably not what you want. You need to introduce an additional WHERE clause specifying how the two tables should be JOINed, like WHERE hiart01a.column = hiarf01.column AND hiart01a.tgl=from_days(to_days(now()) - 180) and hiart01a.tgl=from_days(to_days(now()) - 30) Also, if you do not have indexes on the columns the two tables JOIN on, that would help, as would an index on hiart01a.tgl - since you are searching on that column. Posting table structures when you have a question like this is always helpful - the output from 'SHOW CREATE TABLE hiart01a;' for example. Good luck, Dan Eko Budiharto wrote: I have a query statement like this select hiart01a.cust, hiarf01.nama, hiarf01.al2, hiart01a.tgl, hiart01a.netto, hiart01a.muka from hiart01a, hiarf01 where hiart01a.tgl=from_days(to_days(now()) - 180) and hiart01a.tgl=from_days(to_days(now()) - 30) order by hiart01a.cust; when I run this statement, it takes more than 2 hours. Detail information: -. there are 200K rows in both tables (hiart01a, hiarf01) -. I am using mysql 5.019 -. I am running on testing server (pentium 4, 3GHz, RAM 1GB) and the system variables like this innodb_additional_mem_pool_size 25165824 innodb_autoextend_increment8innodb_buffer_pool_awe_mem_mb 0 innodb_additional_mem_pool_size 25165824innodb_autoextend_increment8 innodb_buffer_pool_awe_mem_mb 0 nbsp; innodb_buffer_pool_size 734003200 innodb_log_buffer_size 4194304 nbsp; innodb_log_file_size17825792 innodb_open_files 300 innodb_thread_concurrency 100 max_allowed_packet 1048576 max_binlog_cache_size 4294967295 max_binlog_size 1073741824 max_connect_errors10 max_connections 800 max_delayed_threads 20 max_error_count64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 4294967295 max_length_for_sort_data1024 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_tmp_tables32 max_user_connections 0 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_typeON read_buffer_size 61440 I am wondering there is a way to make the query process faster. I am looking forward to a favorable reply from you. Thank you. Regards, Eko - Yahoo! Sports Fantasy Football ’06 - Go with the leader. Start your league today! -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More LEFT JOIN newbie fun!
Not long ago, some highly knowledgeable people here kindly helped me out with a fairly complex query... Finding names of people (and other info) where one or more fields match the search string in up to five tables (abstracting somewhat): select distinct id, firstname, lastname, etc... from master_info as r inner join general_info as g left join table_1 as t1 on t1.id = r.id left join table_2 as t2 on t2.id = r.id left join table_3 as t3 on t3.id = r.id left join table_4 as t4 on t4.id = r.id left join table_5 as t5 on t5.id = r.id where g.id = r.id and (t1.blurb like '%searchterm%' or t2.blurb like '%searchterm%' or t3.blurb like '%searchterm%' or t4.blurb like '%searchterm%' or t5.blurb like '%searchterm%') That's all fine and dandy, but now I need to extend this to a further four tables... except it's really eight tables in four pairs. I'll call these table_a and table_ga .. table_d and table_gd. So far, I can get it to work if I add just one pair, in either of two ways: left join table_ga as tga on tga.id = r.id left join table_a as ta on ta.ida = tga.ida or: left join (table_ga as tga inner join table_a as ta) on (tga.id = r.id and ta.ida = tga.ida) in each case adding: or ta.blurb like '%searchterm%' to the where clause. As you'll realise this is because the text has to match the blurb column in ta, which is in turn identified by its own id which has to be matched in tga, which is simply two columns of ids (one of people, one of blurbs). That does, as I say, work, but it does slow things down pretty drastically - from less than half a second to about four seconds (whichever of the two methods I use). And when I add a second pair (table_b and table_gb) it's nearly a minute, so obviously this is going to multiply up very nastily if I add the other two pairs. I've now added full text indices to the blurb columns in table_a and table_b and that's speeded things up a lot - about 7.5 seconds now. However, in this instance there are matches in both table_a and table_b (as well as in some of the 1..5 tables). When I add the remaining two pairs in - where I know there are no matches - well, it's still running after several minutes, and that's after full text indexing those tables too. Obviously, there's a better way of doing this - any ideas? (And I'm now cancelling the last query which still hasn't finished!) -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Without music to decorate it, time is just a bunch of boring production deadlines or dates by which bills must be paid. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More LEFT JOIN newbie fun!
At 16:09 +0100 11/5/06, I wrote: Not long ago, some highly knowledgeable people here kindly helped me out with a fairly complex query... ... That's all fine and dandy, but now I need to extend this to a further four tables... What I should have added is that for the moment this has to be possible in MySQL 3.23, so I can't do nice things like match ... against ... -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ A censor is a man who knows more than he thinks you ought to. -- Laurence J. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Explain explanation (was: More LEFT JOIN newbie fun!)
Following my post about this complex search I'm trying to do... In the initial post I said I'd tried adding: left join table_ga as tga on tga.id = r.id left join table_a as ta on ta.ida = tga.ida or: left join (table_ga as tga inner join table_a as ta) on (tga.id = r.id and ta.ida = tga.ida) to my query, and that both got the same results. Having added two pairs and tried both versions, I find the second one is fractionally (but I mean /really/ fractionally) faster, as reported by phpMyAdmin. However, when I do an Explain on both versions, the first shows values of 375, 17, 3 and a bunch of 1s in the rows column, whereas the second shows 375, 34, 6 and the same lot of 1s. All other details are identical. As I understand it in my naive, newbie way, as a rough rule of thumb you can compare the speeds of queries by multiplying together the rows values... in which case the search that was fractionally slower should have been four times as fast. What's going on here? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I'm on a seafood diet - I see food, I eat it. -- Dolly Parton -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should I upgrade to 4.1 or 5.0 (Newbie level question)
MySQL Mailing List, Currently I'm running MySQL 4.1.15 on my home computer where I do web development, and MySQL 4.1.13-beta on my hosting service. Actually, I do work on web sites that are hosted on many hosting services, but on only one of them do I have enough access to decide for myself if I want to upgrade the server. It seems that MediaWiki, something I'm currently experimenting with, requires MySQL 4.1.14 or above. So at the very least I need to upgrade MySQL on the hosting service. But I'm a little confused by the presence of MySQL 5.0. On the mysql.com web site, it promotes version 5.0 and barely makes any mention of previous versions. (Of course, in the support documentation there is a lot about previous versions, but I'm just talking about the site's promotional text.) Ordinarily, a new version of any software wouldn't be confusing. I would assume that whatever the latest version that the developers are making available is the one that is supported and stable and preferable to use. But none of my hosting services anything higher than 4.1.15. And my home computer, which runs Ubuntu, defaulted to installing 4.1.15 and uthe application update manager doesn't update it to 5.0. So if 5.0 is the current version of MySQL, why does it seem to me that it's not widely adopted? Would it be problematic now or later if I upgraded to 5.0? Would I have to soon upgrade to 5.0 if I upgraded to 4.15 now? Thank you for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Locking Question
David T. Ashley wrote: Nigel wrote: mod_php will persist the MySQL connection holding open any lock or syncronisation token obtained through any of the three methods : begin/commit, lock/unlock tables or get_lock/release_lock. PHP does ensure that even in the event of timeouts or fatal errors any shutdown handlers registered are still executed so it is possible to clean up properly whichever method is used. http://uk.php.net/manual/en/function.register-shutdown-function.php If you use php's pdo with transactions it perform a rollback for you on abort or completion. What is a pdo? Thanks, Dave. PHP's newest official way to talk to databases: http://www.*php*.net/*pdo *http://wiki.cc/*php*/PDO http://www.phpro.org/tutorials/Introduction-to-PHP-PDO-(PHP-Data-Objects).html Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
