~problem with select into outfile~
Hi Iam having a problem using select into outfile command, iam getting the following error. can anyone help me trace the problem. mysql SELECT * INTO OUTFILE'/home/public1/data.txt' FROM temp_table WHERE last_time_update = 2006-04-01; ERROR 1 (HY000): Can't create/write to file '/home/public1/data.txt' (Errcode: 13) Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext and reserved words
Hi Taco - interesting thread. Did you rebuilt the FullText indexes after restarting the server? From the 5.0.18 manual pages: 12.7.5. Fine-Tuning MySQL Full-Text Search quote Most full-text variables described in this section must be set at server startup time. A server restart is required to change them; they cannot be modified while the server is running. Some variable changes require that you rebuild the FULLTEXT indexes in your tables. Instructions for doing this are given at the end of this section. The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. (See Section 5.2.2, Server System Variables.) The default minimum value is four characters; the default maximum is version dependent. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file: [mysqld] ft_min_word_len=3 Then you must restart the server and rebuild your FULLTEXT indexes. Note particularly the remarks regarding myisamchk in the instructions following this list. /quote Regards Keith In theory, theory and practice are the same; in practice they are not. On Thu, 13 Apr 2006, Taco Fleur wrote: To: mysql@lists.mysql.com From: Taco Fleur [EMAIL PROTECTED] Subject: RE: Fulltext and reserved words Thanks for that, you saved the day. I am now trying to change that variable, I did a search through the ini files and changed the only file that had ft_min_word_len in it to ft_min_word_len = 2 restarted the server did SHOW VARIABLES and it still shows as 4, not sure what I am missing, going through the documentation but just can't find anything specific. Kind regards, Taco Fleur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext and reserved words
Hi, I did everything but rebuild, I restarted the server but the variable still has the same value. And I don't know how to rebuild the table, I read the documentation, but I guess I am just missing something there, like I have no idea as a newbie where to enter those commands etc. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, 13 April 2006 4:41 PM To: mysql@lists.mysql.com Subject: RE: Fulltext and reserved words Hi Taco - interesting thread. Did you rebuilt the FullText indexes after restarting the server? From the 5.0.18 manual pages: 12.7.5. Fine-Tuning MySQL Full-Text Search quote Most full-text variables described in this section must be set at server startup time. A server restart is required to change them; they cannot be modified while the server is running. Some variable changes require that you rebuild the FULLTEXT indexes in your tables. Instructions for doing this are given at the end of this section. The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. (See Section 5.2.2, Server System Variables.) The default minimum value is four characters; the default maximum is version dependent. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file: [mysqld] ft_min_word_len=3 Then you must restart the server and rebuild your FULLTEXT indexes. Note particularly the remarks regarding myisamchk in the instructions following this list. /quote Regards Keith In theory, theory and practice are the same; in practice they are not. On Thu, 13 Apr 2006, Taco Fleur wrote: To: mysql@lists.mysql.com From: Taco Fleur [EMAIL PROTECTED] Subject: RE: Fulltext and reserved words Thanks for that, you saved the day. I am now trying to change that variable, I did a search through the ini files and changed the only file that had ft_min_word_len in it to ft_min_word_len = 2 restarted the server did SHOW VARIABLES and it still shows as 4, not sure what I am missing, going through the documentation but just can't find anything specific. Kind regards, Taco Fleur -- 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] How To Trim Database To N Records
Is there a query that will, say, trim a table down to a million rows (with some sort order, of course, as I'm interested in deleting the oldest ones)? If you have got a datecolumn, you might also want to delete anything that is older than x days (2 in my example): DELETE FROM database.table WHERE datecolumn = DATE_SUB(sysdate(),INTERVAL 2 day); Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customized variations on words with fulltext
Not being an expert on FULLTEXT functions yet, I suppose you could hard code some type of search function into your application code, then create all the relevant search options for the FULLTEXT query in your app logic, then post the FULLTEXT query off to mysql. Or store the different search options in mysql tables. Then retrieve those options to build the multiple-name FULLTEXT search. Then send the query back to your database? Regards Keith On Thu, 13 Apr 2006, Taco Fleur wrote: To: mysql@lists.mysql.com From: Taco Fleur [EMAIL PROTECTED] Subject: customized variations on words with fulltext Hi all, sorry for all the questions lately, just getting my feet wet in MySQL. Is there any way to create my own variations on words so that MySQL will include those in the fulltext search? What I am after is for example if someone enters VB, I'd like the search to also look for; - Visual Basic - VisualBasic Is this possible, if so how? Thanks in advance for any help. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: customized variations on words with fulltext
Yes I have been thinking about the same, and already started collecting search words, but its not really the direction I want to go in if I can avoid it. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, 13 April 2006 4:51 PM To: mysql@lists.mysql.com Subject: Re: customized variations on words with fulltext Not being an expert on FULLTEXT functions yet, I suppose you could hard code some type of search function into your application code, then create all the relevant search options for the FULLTEXT query in your app logic, then post the FULLTEXT query off to mysql. Or store the different search options in mysql tables. Then retrieve those options to build the multiple-name FULLTEXT search. Then send the query back to your database? Regards Keith On Thu, 13 Apr 2006, Taco Fleur wrote: To: mysql@lists.mysql.com From: Taco Fleur [EMAIL PROTECTED] Subject: customized variations on words with fulltext Hi all, sorry for all the questions lately, just getting my feet wet in MySQL. Is there any way to create my own variations on words so that MySQL will include those in the fulltext search? What I am after is for example if someone enters VB, I'd like the search to also look for; - Visual Basic - VisualBasic Is this possible, if so how? Thanks in advance for any help. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb vs myisam
Do you have any idexes on the table? What does your mysql show create table tbl_name \G statement look like please? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 13 Apr 2006, Luke Vanderfluit wrote: To: mysql@lists.mysql.com From: Luke Vanderfluit [EMAIL PROTECTED] Subject: innodb vs myisam Hi. I have the following myisam table: The table is only 32,000 rows, but over 60Megs in size. And mysql seems to be wanting to write to that file alot, so it may well be trying to seek all over the disk looking for the right spot all the time. Does innodb do a better job at keeping the file on the disk smaller? Does an innodb table take up less disk space than myisam? Is an innodb table compacter and would therefore require less disk seek time or I/O than myisam? Kind regards. Luke. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: customized variations on words with fulltext
What about letting the user make the right choice in the first place? Like when you use Google, you only get what results from what you type into standard Google. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext and reserved words
If you are not rebuilding the FULLTEXT indexes, then that is probably why the change to the ft_min_word_len is not being recognised. It seems like mysql uses this value to set the index it builds for the FULLTEXT search. I don't know how to rebuild this index. You may need to delete your .MYI file for the particular table (if this is where FULLTEXT indexes live) and then rebuild the .MYI file with myisamchk, or some other mysql utility. Remember to make backups before experimenting on your db tables. Or copy them (as well) to a different directory, and do some tests on the copies first, untill you are sure what you are doing. HTH Keith In theory, theory and practice are the same; in practice they are not. On Thu, 13 Apr 2006, Taco Fleur wrote: To: [EMAIL PROTECTED], mysql@lists.mysql.com From: Taco Fleur [EMAIL PROTECTED] Subject: RE: Fulltext and reserved words Hi, I did everything but rebuild, I restarted the server but the variable still has the same value. And I don't know how to rebuild the table, I read the documentation, but I guess I am just missing something there, like I have no idea as a newbie where to enter those commands etc. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, 13 April 2006 4:41 PM To: mysql@lists.mysql.com Subject: RE: Fulltext and reserved words Hi Taco - interesting thread. Did you rebuilt the FullText indexes after restarting the server? From the 5.0.18 manual pages: 12.7.5. Fine-Tuning MySQL Full-Text Search quote Most full-text variables described in this section must be set at server startup time. A server restart is required to change them; they cannot be modified while the server is running. Some variable changes require that you rebuild the FULLTEXT indexes in your tables. Instructions for doing this are given at the end of this section. The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. (See Section 5.2.2, Server System Variables.) The default minimum value is four characters; the default maximum is version dependent. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file: [mysqld] ft_min_word_len=3 Then you must restart the server and rebuild your FULLTEXT indexes. Note particularly the remarks regarding myisamchk in the instructions following this list. /quote Regards Keith In theory, theory and practice are the same; in practice they are not. On Thu, 13 Apr 2006, Taco Fleur wrote: To: mysql@lists.mysql.com From: Taco Fleur [EMAIL PROTECTED] Subject: RE: Fulltext and reserved words Thanks for that, you saved the day. I am now trying to change that variable, I did a search through the ini files and changed the only file that had ft_min_word_len in it to ft_min_word_len = 2 restarted the server did SHOW VARIABLES and it still shows as 4, not sure what I am missing, going through the documentation but just can't find anything specific. Kind regards, Taco Fleur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
~Mysql db crashing again and again~
Hi, Iam having a mysql version 5.0.15-standard-log installed on redhat es4. It is configured as slave server. It was running fine till yesterday. Then it hit a mysql bug with signal 6. The mysql db was throwing the following errors on the shell prompt ie the OS shell prompt. *** glibc detected *** free(): invalid pointer: 0x089e6843 *** *** glibc detected *** free(): invalid pointer: 0x089e6843 *** The mysql error log shows continuous occurence of mysql signal 6 and signal 11. The output of error log file is as follows: - mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=0 max_connections=10 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 434135 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x89fd360 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x177f9c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x814e7f8 0x8787c8 (nil) 0x69840a 0x69eb3f 0x69eeba 0x81f8bb3 0x8240d56 0x824123a 0x82403cb 0x8166f0b 0x8168d82 0x81bf14b 0x81beb8c 0x8222586 0x8220387 0x872341 0x7046fe New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x89f1860 = drop procedure FHLMC.pool_info_select thd-thread_id=2 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060413 13:01:56 mysqld restarted Can anyone help me trace out the problem and how to go about the same ? Is it something related to glibc version that is causing the problem ? Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump ignores auto_increment
Hi, I got confused looking to see if this was a known problem - so thought I'd try again. mysql_standard 4.1.16 on Linux... Synopsis: Create table with auto_increment mysqldump database output doesn't create table with auto_increment. Example: Create a table in a junk database using, create table tbl ( id int(11) not null auto_increment, primary key(id)); Backup the database using, mysqldump -u ... -p junk out.sql Look at out.sql, (snipped for brevity), -- -- Table structure for table `tbl` -- DROP TABLE IF EXISTS `tbl`; CREATE TABLE `tbl` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM; wheres the auto_increment ??? Help! Regards, Ian.
Re: ~Mysql db crashing again and again~
Quoting Mohammed Abdul Azeem ([EMAIL PROTECTED]): Then it hit a mysql bug with signal 6. The mysql db was throwing the following errors on the shell prompt ie the OS shell prompt. I had exactly the same behaviour on MySQL 5.0.19 running on AMD64. What helped in my case was re-creating all the indexes on all the tables. Since then this problem disappeared. But what I did was copy the binary myisam tables from a 32 bit MySQL4 database to a 64 bit MySQL5 database. Just an idea. Regards, Sander. -- | Eat well, stay fit, die anyway. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 3 to 5 upgrade
Our web host is currently running MySQL 3.23.something, but we're shortly to be upgraded to MySQL 5. Can I be sure that this is absolutely backwards compatible? Are there any nastinesses lurking that I should know about that might cause my databases to collapse in a heap? My use of MySQL (as my previous question will attest!) is comparatively limited so far, and I tend to do everything either via phpMyAdmin or my own PHP scripts. Any warnings would be gratefully received! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Star Wars won't work. -- Frank Zappa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3 to 5 upgrade
Chris Sansom wrote: Our web host is currently running MySQL 3.23.something, but we're shortly to be upgraded to MySQL 5. Can I be sure that this is absolutely backwards compatible? Are there any nastinesses lurking that I should know about that might cause my databases to collapse in a heap? My use of MySQL (as my previous question will attest!) is comparatively limited so far, and I tend to do everything either via phpMyAdmin or my own PHP scripts. Any warnings would be gratefully received! do it that way: Make a real downgradeable SQL Dump (without collations n stuff) and have it saved. Upgrade to MySQL 5.x and execute that sql dump. Be warned that for example CONCAT behaves in a different way than in 3.x. If you have PHP scripts with some functions in their sql queries you should check them all. Data should be safe and beeing able to be inserted into the new SQL environment. Good Luck! -- 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: comparing 2 dbs and generating 'upgrade sql'
From: starmonkey Sent: 13 April 2006 01:51 hey guys, A while ago I remembered using SQLYog or some other tool that could look at two databases (say dev and test versions) and work out the differences in structure between the two, and generate some SQL to 'upgrade' one to the other (bunch of alter table commands basically). I'm wondering if there's any way to do this using free tools, as I believe SQLYog was commercial? SqlFairy (http://sqlfairy.sourceforge.net/) has a sqlt-diff command that should do what you want. mark -- Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~problem with select into outfile~
From: Mohammed Abdul Azeem Sent: 13 April 2006 07:29 Hi Iam having a problem using select into outfile command, iam getting the following error. can anyone help me trace the problem. mysql SELECT * INTO OUTFILE'/home/public1/data.txt' FROM temp_table WHERE last_time_update = 2006-04-01; ERROR 1 (HY000): Can't create/write to file '/home/public1/data.txt' (Errcode: 13) Checking with perror $perror 13 System error: 13 = Permission denied So I'd check the permissions on the directory, the user the mysqld runs as needs write permissions there. mark -- Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to create a query to get right commencing date
Hello, I hope someone can help me with my problem. I want to join 2 tables. One contains data about start and finish dates of a machine install, the second table conatins data of install costs and a commencing date. Table 1: +-+++-+-+ | Machine | StartDate | StopDate | Country | ObjType | +-+++-+-+ |1234 | 2006-02-01 | 2006-04-30 | NL | AA001 | | | 2005-10-03 | 2006-01-02 | JP | AA002 | +-+++-+-+ Table 2: +-++--+ | ObjType | ComDate| Rate | +-++--+ | AA001 | 2004-01-01 | 150 | | AA001 | 2005-01-01 | 90 | | AA002 | 2005-01-01 | 100 | | AA002 | 2005-03-15 | 95 | | AA002 | 2005-11-01 | 110 | +-++--+ Result: SELECT m.*, r.ComDate, r.Rate FROM machinedata m LEFT JOIN Rates r ON m.ObjType=r.ObjType; +-+++-+-++-- + | Machine | StartDate | StopDate | Country | ObjType | ComDate| Rate | +-+++-+-++-- + |1234 | 2006-02-01 | 2006-04-30 | NL | AA001 | 2004-01-01 | 150 | |1234 | 2006-02-01 | 2006-04-30 | NL | AA001 | 2005-01-01 | 90 | | | 2005-10-03 | 2006-01-02 | JP | AA002 | 2005-01-01 | 100 | | | 2005-10-03 | 2006-01-02 | JP | AA002 | 2005-03-15 | 95 | | | 2005-10-03 | 2006-01-02 | JP | AA002 | 2005-11-01 | 110 | +-+++-+-++-- + As you can see, I get 4 rows back from the query. And both machines are mentioned twice. But what I want is that I get just 2 rows with the latest commencing date (ComDate) based on when the machine has started with the rate that belongs to the commencing date. So the result that I want is Like this: +-+++-+-++-- + | Machine | StartDate | StopDate | Country | ObjType | ComDate| Rate | +-+++-+-++-- + |1234 | 2006-02-01 | 2006-04-30 | NL | AA001 | 2005-01-01 | 90 | | | 2005-10-03 | 2006-01-02 | JP | AA002 | 2005-03-15 | 95 | +-+++-+-++-- + How can I create a query to get this result? Can somebody help me? TIA, Arjan Hulshoff. -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3 to 5 upgrade
At 11:56 +0200 13/4/06, Barry wrote: Make a real downgradeable SQL Dump (without collations n stuff) and have it saved. Upgrade to MySQL 5.x and execute that sql dump. Be warned that for example CONCAT behaves in a different way than in 3.x. If you have PHP scripts with some functions in their sql queries you should check them all. Data should be safe and beeing able to be inserted into the new SQL environment. Thanks - sounds like good sounds advice. :-) I'll look into CONCAT. The only thing is that, judging by past experience, the host will only give us an approximate idea of when this might happen, so I may well be presented with a fait accompli! It's likely to happen in the middle of the night, so I just hope I don't wake up one day to dozens of emails saying the whole thing's broken. I'll just have to keep my fingers crossed. -- 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]
RE: customized variations on words with fulltext
In MySql 5 you can write a filter function (plugin) Santino At 17:04 +1000 13-04-2006, Taco Fleur wrote: Yes I have been thinking about the same, and already started collecting search words, but its not really the direction I want to go in if I can avoid it. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, 13 April 2006 4:51 PM To: mysql@lists.mysql.com Subject: Re: customized variations on words with fulltext Not being an expert on FULLTEXT functions yet, I suppose you could hard code some type of search function into your application code, then create all the relevant search options for the FULLTEXT query in your app logic, then post the FULLTEXT query off to mysql. Or store the different search options in mysql tables. Then retrieve those options to build the multiple-name FULLTEXT search. Then send the query back to your database? Regards Keith On Thu, 13 Apr 2006, Taco Fleur wrote: To: mysql@lists.mysql.com From: Taco Fleur [EMAIL PROTECTED] Subject: customized variations on words with fulltext Hi all, sorry for all the questions lately, just getting my feet wet in MySQL. Is there any way to create my own variations on words so that MySQL will include those in the fulltext search? What I am after is for example if someone enters VB, I'd like the search to also look for; - Visual Basic - VisualBasic Is this possible, if so how? Thanks in advance for any help. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql Error Number 1130
Wow, I found the problem! I think this may be a bug. In my case, I've got three instances running on ports 3306, 3307, and 3320. On the local machine, I connect to them via mysql -p --port-3306 --host=localhost mysql -p --port-3307 --host=localhost mysql -p --port-3320 --host=localhost Except that connecting to port 3307 doesn't really happen. It seems that the command line client connects to the main instance via the default socket when the host is not specified, or is the value localhost. So, even though I've specified the host and port, I wind up connecting to the main instance. I found this out by stopping the main instance on 3306, and then I issue: LX09:/etc # mysql --port=3307 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) LX09:/etc # mysql --port=3307 --host=localhost ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) LX09:/etc # mysql --port=3307 --host=127.0.0.1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. LX09:/etc # mysql --port=3307 --host=nnn.nnn.nnn.nnn -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Note that /var/lib/mysql/mysql.sock is the socket associated with the main instance, not the alternates. It seems that if I *specify* a port, then mysql ought to use that port. It only seems to be an issue when not specifying a --host, or when using the value 'localhost'. It's a bit terrifying because during all my testing, I am thinking I am connected to the correct instance, when in fact, I was not. It will be very easy to blow away the main instance data by mistake. Can I somehow convince the mysql command line client to use the specified parms, rather than the (incorrect) socket? Thank you. Regards, Rich -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 12, 2006 6:00 PM To: mysql@lists.mysql.com Subject: Re: MySql Error Number 1130 When you are trying to connect to port 3307, for example, are you specifying that port from your remote machine? Or are you connecting to the mysqld listening on port 3306? You could try shutting down the server listening on port 3306, and then connecting to port 3307. See if the error message changes or goes away. shell# mysql -h host_name -u root -px --port=3307 I think you may need to specify the absolute IP address in the user table, instead of a wildcard '%'. See if this helps. Use your root username and password in place of 'tommy'. I did not want to mess up my root user account! Use the IP address of your remote machine you want to connect to mysql with, in place of 10.0.0.5. mysql create user 'tommy'@'10.0.0.5' - identified by '12345'; Query OK, 0 rows affected (0.00 sec) mysql grant all on *.* - to 'tommy'@'10.0.0.5' - identified by '12345'; Query OK, 0 rows affected (0.00 sec) mysql select * from user where user = 'tommy' \G ** 1. row * Host: 10.0.0.5 User: tommy Password: snipped Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 1 row in set (0.00 sec) HTH Keith In theory, theory and practice are the same; in practice they are not. On Wed, 12 Apr 2006, Duzenbury, Rich wrote: To: mysql@lists.mysql.com From: Duzenbury, Rich [EMAIL PROTECTED] Subject: MySql Error Number 1130 Hi all, I am setting up two additional instances of mysql on my mysql server, which is running version 5.0.18-standard-log. I've got the additional instances set up, and they are running. I can see that they are bound to the proper ports. I can connect to them locally like mysql -p --port=3306 mysql -p --port=3307 mysql -p --port=3320 I have always been able to connect remotely from my workstation to the base server on 3306. My problem is that I cannot connect remotely to either of the new additional instances running on 3307 or 3320. Anytime I attempt to do so, I receive MySQL Error Number 1130, Host
Re: MySql Error Number 1130
Duzenbury, Rich wrote: Wow, I found the problem! I think this may be a bug. In my case, I've got three instances running on ports 3306, 3307, and 3320. On the local machine, I connect to them via mysql -p --port-3306 --host=localhost mysql -p --port-3307 --host=localhost mysql -p --port-3320 --host=localhost Except that connecting to port 3307 doesn't really happen. It seems that the command line client connects to the main instance via the default socket when the host is not specified, or is the value localhost. So, even though I've specified the host and port, I wind up connecting to the main instance. I found this out by stopping the main instance on 3306, and then I issue: LX09:/etc # mysql --port=3307 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) LX09:/etc # mysql --port=3307 --host=localhost ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) localhost means socket. LX09:/etc # mysql --port=3307 --host=127.0.0.1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. LX09:/etc # mysql --port=3307 --host=nnn.nnn.nnn.nnn -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Note that /var/lib/mysql/mysql.sock is the socket associated with the main instance, not the alternates. It seems that if I *specify* a port, then mysql ought to use that port. It only seems to be an issue when not specifying a --host, or when using the value 'localhost'. It's a bit terrifying because during all my testing, I am thinking I am connected to the correct instance, when in fact, I was not. It will be very easy to blow away the main instance data by mistake. Can I somehow convince the mysql command line client to use the specified parms, rather than the (incorrect) socket All of the above is the correct and documented behavior. This is covered in the manual, and can be found many times in the archives. Thank you. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql Error Number 1130
localhost means socket. Hmm, I don't believe it does. localhost is a DNS shortcut to the IP address of the local machine. If that is how mysql wants to treat things, then it should issue an error message on connect because --host=localhost and --port=anything would then be mutually exclusive. Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Error Number 1130
Duzenbury, Rich wrote: localhost means socket. Hmm, I don't believe it does. localhost is a DNS shortcut to the IP address of the local machine. If that is how mysql wants to treat things, then it should issue an error message on connect because --host=localhost and --port=anything would then be mutually exclusive. Thanks. Regards, Rich Wasn't it something like on local machines MySQL doesn't open a socket port because it works directly in program and not going the loop out of mysql and back in through the socket? -- 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]
Optimizing SQL statement
Hi, I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic data : record_time datetime - time when recording was added ip char(15) - ip that generated the traffic type tinyint(3) - traffic type ( 1 - local, 2 - internet ) inbound int(10) - in bytes outbound int(10) - out bytes Records are inserted each 5 minutes through a cron script. Currently there are 3,330,367 rows. Primary index is defined on ( ip, type, record_time ), columns in that order. Also there is an index defined only on record_time Now for an example to get traffic for this month, I use : SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out` FROM `accounting` WHERE `record_time` BETWEEN 2006040100 AND 2006041316 GROUP BY `ip`,`type` this query takes aprox 7 seconds Using EXPLAIN gives : select_typetable type possible_keys key key_len ref rows Extra SIMPLE accounting range record_time record_time 8 NULL 362410 Using where; Using temporary; Using filesort If I remove the SUM functions I am getting also Using index in group-by and the query takes only 0.25 sec Is there anyway to optimize this query to get faster responses ? Thanks, --- Puiu Hrenciuc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing SQL statement
Puiu Hrenciuc wrote: Hi, I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic data : record_time datetime - time when recording was added ip char(15) - ip that generated the traffic type tinyint(3) - traffic type ( 1 - local, 2 - internet ) inbound int(10) - in bytes outbound int(10) - out bytes Records are inserted each 5 minutes through a cron script. Currently there are 3,330,367 rows. Primary index is defined on ( ip, type, record_time ), columns in that order. Also there is an index defined only on record_time Now for an example to get traffic for this month, I use : SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out` FROM `accounting` WHERE `record_time` BETWEEN 2006040100 AND 2006041316 GROUP BY `ip`,`type` this query takes aprox 7 seconds Using EXPLAIN gives : select_typetable type possible_keys key key_len ref rows Extra SIMPLE accounting range record_time record_time 8 NULL 362410 Using where; Using temporary; Using filesort If I remove the SUM functions I am getting also Using index in group-by and the query takes only 0.25 sec Is there anyway to optimize this query to get faster responses ? Thanks, --- Puiu Hrenciuc Set an index on ip and type and probably also on record_time 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: Optimizing SQL statement
Hmmm, I have omited that : I also have an index on (ip,type) in that order Barry [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Puiu Hrenciuc wrote: Hi, I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic data : record_time datetime - time when recording was added ip char(15) - ip that generated the traffic type tinyint(3) - traffic type ( 1 - local, 2 - internet ) inbound int(10) - in bytes outbound int(10) - out bytes Records are inserted each 5 minutes through a cron script. Currently there are 3,330,367 rows. Primary index is defined on ( ip, type, record_time ), columns in that order. Also there is an index defined only on record_time Now for an example to get traffic for this month, I use : SELECT `ip`,`type`,SUM(`inbound`) AS `in`, SUM(`outbound`) as `out` FROM `accounting` WHERE `record_time` BETWEEN 2006040100 AND 2006041316 GROUP BY `ip`,`type` this query takes aprox 7 seconds Using EXPLAIN gives : select_typetable type possible_keys key key_len ref rows Extra SIMPLE accounting range record_time record_time 8 NULL 362410 Using where; Using temporary; Using filesort If I remove the SUM functions I am getting also Using index in group-by and the query takes only 0.25 sec Is there anyway to optimize this query to get faster responses ? Thanks, --- Puiu Hrenciuc Set an index on ip and type and probably also on record_time 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]
Wont insert into database
i have two php Forms to insert data into a Mysql 4.0.20d Database, one is just a simple form to test if it works the other is the Actual application, here is the test form and works Perfectly ? $name=$_POST['Name']; $value1=$_POST['value1']; $value2=$_POST['value2']; $sum=$_POST['sumfield']; mysql_connect(localhost,root,) or die(mysql_error()); mysql_select_db(testbase) or die(mysql_error()); mysql_query(INSERT INTO `formadder` VALUES ('$name','$value1', '$value2','$sum')); Print Your information has been successfully added to the database.; ? NOW , / here is the Form data submitter that doesn't Work Ive checked and checked and check this damn Code with my form and nothing is outta place, (i dont think) BUT will NOT Insert anything to the database even though i get no errors at all and it says Your information has been successfully added to the database.; all the feilds are blank ? $ordernumber = $_POST['ordernumber']; $companyname = $_POST['companyname']; $billingaddress = $_POST['billingaddress']; $City = $_POST['City']; $State2 = $_POST['State']; $Zip = $_POST['Zip']; $PhoneNumber= $_POST['PhoneNumber']; $FaxNumber = $_POST['FaxNumber']; $WebPage= $_POST['WebPage']; $EmailAddress = $_POST['EmailAddress']; $Notes = $_POST['Notes']; $Customer= $_POST['Customer']; $Startdate = $_POST['Startdate']; $Completedate = $_POST['Completedate']; $Biddate= $_POST['Biddate']; $Bidamount = $_POST['Bidamount']; $ElecProjCost = $_POST['ElecProjCost']; $ElecProjBill = $_POST['ElecProjBill']; $ElecRem = $_POST['ElecRem']; $CtrlProjCost = $_POST['CtrlProjCost']; $CtrlProjBill = $_POST['CtrlProjBill']; $CtrlProjrem = $_POST['CtrlProjrem']; $OthrProjCost = $_POST['OthrProjCost']; $OthrProjBill = $_POST['OthrProjBill']; $OthrProjrem = $_POST['OthrProjrem']; $BondAm= $_POST['BondAm']; $BondBill= $_POST['BondBill']; $BondRem= $_POST['BondRem']; mysql_connect(localhost,root,) or die(mysql_error()); mysql_select_db(workorder) or die(mysql_error()); mysql_query(INSERT INTO `orders` VALUES (`$ordernumber` , `$companyname` , `$billingaddress` , `$City` , `$State2` , `$Zip` , `$PhoneNumber` , `$FaxNumber` , `$WebPage` , `$EmailAddress` , `$Notes` , `$Customer` , `$Startdate` , `$Completedate` , `$Biddate` , `$Bidamount` , `$ElecProjCost` , `$ElecProjBill` , `$ElecRem` , `$CtrlProjCost` , `$CtrlProjBill` , `$CtrlProjrem` , `$OthrProjCost` , `$OthrProjBill` , `$OthrProjrem`, `$BondAm` , `$BondBill` , `$BondRem` )); Print Your information has been successfully added to the database.; ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql Error Number 1130
-Original Message- From: Barry [mailto:[EMAIL PROTECTED] Sent: Thursday, April 13, 2006 9:41 AM To: mysql@lists.mysql.com Subject: Re: MySql Error Number 1130 Duzenbury, Rich wrote: localhost means socket. Hmm, I don't believe it does. localhost is a DNS shortcut to the IP address of the local machine. If that is how mysql wants to treat things, then it should issue an error message on connect because --host=localhost and --port=anything would then be mutually exclusive. Thanks. Regards, Rich Wasn't it something like on local machines MySQL doesn't open a socket port because it works directly in program and not going the loop out of mysql and back in through the socket? I'm not sure I understand your statement. It's wise to use a unix domain socket where possible because they perform better than network sockets. However, it's misleading for the mysql client to ignore a command line directive as important as --port or --host without warning. Especially since this can cause connection to the wrong instance. Another way this would have been made more obvious is if the welcome message in the client were a bit more descriptive. Currently, I see 'Your mysql connection id is 2 to server version: 5.0.18-standard-log'. Perhaps the client should indicate the socket or ip/port that was actually used in the welcome message. Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wont insert into database
Brian E Boothe wrote: i have two php Forms to insert data into a Mysql 4.0.20d Database, one is just a simple form to test if it works the other is the Actual application, here is the test form and works Perfectly ? $name=$_POST['Name']; $value1=$_POST['value1']; $value2=$_POST['value2']; $sum=$_POST['sumfield']; mysql_connect(localhost,root,) or die(mysql_error()); mysql_select_db(testbase) or die(mysql_error()); mysql_query(INSERT INTO `formadder` VALUES ('$name','$value1', '$value2','$sum')); Print Your information has been successfully added to the database.; ? NOW , / here is the Form data submitter that doesn't Work Ive checked and checked and check this damn Code with my form and nothing is outta place, (i dont think) BUT will NOT Insert anything to the database even though i get no errors at all and it says Your information has been successfully added to the database.; all the feilds are blank ? $ordernumber = $_POST['ordernumber']; $companyname = $_POST['companyname']; $billingaddress = $_POST['billingaddress']; $City = $_POST['City']; $State2 = $_POST['State']; $Zip = $_POST['Zip']; $PhoneNumber= $_POST['PhoneNumber']; $FaxNumber = $_POST['FaxNumber']; $WebPage= $_POST['WebPage']; $EmailAddress = $_POST['EmailAddress']; $Notes = $_POST['Notes']; $Customer= $_POST['Customer']; $Startdate = $_POST['Startdate']; $Completedate = $_POST['Completedate']; $Biddate= $_POST['Biddate']; $Bidamount = $_POST['Bidamount']; $ElecProjCost = $_POST['ElecProjCost']; $ElecProjBill = $_POST['ElecProjBill']; $ElecRem = $_POST['ElecRem']; $CtrlProjCost = $_POST['CtrlProjCost']; $CtrlProjBill = $_POST['CtrlProjBill']; $CtrlProjrem = $_POST['CtrlProjrem']; $OthrProjCost = $_POST['OthrProjCost']; $OthrProjBill = $_POST['OthrProjBill']; $OthrProjrem = $_POST['OthrProjrem']; $BondAm= $_POST['BondAm']; $BondBill= $_POST['BondBill']; $BondRem= $_POST['BondRem']; mysql_connect(localhost,root,) or die(mysql_error()); mysql_select_db(workorder) or die(mysql_error()); mysql_query(INSERT INTO `orders` VALUES (`$ordernumber` , `$companyname` , `$billingaddress` , `$City` , `$State2` , `$Zip` , `$PhoneNumber` , `$FaxNumber` , `$WebPage` , `$EmailAddress` , `$Notes` , `$Customer` , `$Startdate` , `$Completedate` , `$Biddate` , `$Bidamount` , `$ElecProjCost` , `$ElecProjBill` , `$ElecRem` , `$CtrlProjCost` , `$CtrlProjBill` , `$CtrlProjrem` , `$OthrProjCost` , `$OthrProjBill` , `$OthrProjrem`, `$BondAm` , `$BondBill` , `$BondRem` )); Print Your information has been successfully added to the database.; ? echo the mysql_query please and see for youself if all variables were put in right. print_r($_GLOBALS); to see if all vars were given correctly. I also think PHP has probably a problem with `$name`. try using '$name' instead or `.$name.` 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: Wont insert into database
In the second example you are enclosing field values with ticks ` instead of single quotes ' You don't get any errors because you don't check for errors, if you put : echo mysql_error(); after mysql_query () you will see the error. To solve this problem simple replace ticks enclosing values from the second example with single quotes : mysql_query(INSERT INTO `orders` VALUES ('$ordernumber' , '$companyname' , '$billingaddress' , '$City' , '$State2' , '$Zip' , '$PhoneNumber' , '$FaxNumber' , '$WebPage' , '$EmailAddress' , '$Notes' , '$Customer' , '$Startdate' , '$Completedate' , '$Biddate' , '$Bidamount' , '$ElecProjCost' , '$ElecProjBill' , '$ElecRem' , '$CtrlProjCost' , '$CtrlProjBill' , '$CtrlProjrem' , '$OthrProjCost' , '$OthrProjBill' , '$OthrProjrem', '$BondAm', '$BondBill' , '$BondRem' )); - Original Message - From: Brian E Boothe [EMAIL PROTECTED] Newsgroups: mysql.general To: mysql@lists.mysql.com Sent: Friday, April 14, 2006 5:59 AM Subject: Wont insert into database i have two php Forms to insert data into a Mysql 4.0.20d Database, one is just a simple form to test if it works the other is the Actual application, here is the test form and works Perfectly ? $name=$_POST['Name']; $value1=$_POST['value1']; $value2=$_POST['value2']; $sum=$_POST['sumfield']; mysql_connect(localhost,root,) or die(mysql_error()); mysql_select_db(testbase) or die(mysql_error()); mysql_query(INSERT INTO `formadder` VALUES ('$name','$value1', '$value2','$sum')); Print Your information has been successfully added to the database.; ? NOW , / here is the Form data submitter that doesn't Work Ive checked and checked and check this damn Code with my form and nothing is outta place, (i dont think) BUT will NOT Insert anything to the database even though i get no errors at all and it says Your information has been successfully added to the database.; all the feilds are blank ? $ordernumber = $_POST['ordernumber']; $companyname = $_POST['companyname']; $billingaddress = $_POST['billingaddress']; $City = $_POST['City']; $State2 = $_POST['State']; $Zip = $_POST['Zip']; $PhoneNumber= $_POST['PhoneNumber']; $FaxNumber = $_POST['FaxNumber']; $WebPage= $_POST['WebPage']; $EmailAddress = $_POST['EmailAddress']; $Notes = $_POST['Notes']; $Customer= $_POST['Customer']; $Startdate = $_POST['Startdate']; $Completedate = $_POST['Completedate']; $Biddate= $_POST['Biddate']; $Bidamount = $_POST['Bidamount']; $ElecProjCost = $_POST['ElecProjCost']; $ElecProjBill = $_POST['ElecProjBill']; $ElecRem = $_POST['ElecRem']; $CtrlProjCost = $_POST['CtrlProjCost']; $CtrlProjBill = $_POST['CtrlProjBill']; $CtrlProjrem = $_POST['CtrlProjrem']; $OthrProjCost = $_POST['OthrProjCost']; $OthrProjBill = $_POST['OthrProjBill']; $OthrProjrem = $_POST['OthrProjrem']; $BondAm= $_POST['BondAm']; $BondBill= $_POST['BondBill']; $BondRem= $_POST['BondRem']; mysql_connect(localhost,root,) or die(mysql_error()); mysql_select_db(workorder) or die(mysql_error()); mysql_query(INSERT INTO `orders` VALUES (`$ordernumber` , `$companyname` , `$billingaddress` , `$City` , `$State2` , `$Zip` , `$PhoneNumber` , `$FaxNumber` , `$WebPage` , `$EmailAddress` , `$Notes` , `$Customer` , `$Startdate` , `$Completedate` , `$Biddate` , `$Bidamount` , `$ElecProjCost` , `$ElecProjBill` , `$ElecRem` , `$CtrlProjCost` , `$CtrlProjBill` , `$CtrlProjrem` , `$OthrProjCost` , `$OthrProjBill` , `$OthrProjrem`, `$BondAm` , `$BondBill` , `$BondRem` )); Print Your information has been successfully added to the database.; ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 3 to 5 upgrade
Going from 3 to 5 can break a number of important things. For example, join syntax semantics and precedence rules have changed since 3, and it is certainly possible this could break your code in important and dramatic ways. You should plan on spending time checking out all the SQL. Just as important, your MySQL host should really provide a 5.x playground for you to check your app out in for at least several weeks prior to the upgrade. Just waking up one morning with the database upgraded is almost surely going to be a mess. Good luck, Paul -Original Message- From: Chris Sansom [mailto:[EMAIL PROTECTED] Sent: Thursday, April 13, 2006 3:54 AM To: Barry; mysql@lists.mysql.com Subject: Re: MySQL 3 to 5 upgrade At 11:56 +0200 13/4/06, Barry wrote: Make a real downgradeable SQL Dump (without collations n stuff) and have it saved. Upgrade to MySQL 5.x and execute that sql dump. Be warned that for example CONCAT behaves in a different way than in 3.x. If you have PHP scripts with some functions in their sql queries you should check them all. Data should be safe and beeing able to be inserted into the new SQL environment. Thanks - sounds like good sounds advice. :-) I'll look into CONCAT. The only thing is that, judging by past experience, the host will only give us an approximate idea of when this might happen, so I may well be presented with a fait accompli! It's likely to happen in the middle of the night, so I just hope I don't wake up one day to dozens of emails saying the whole thing's broken. I'll just have to keep my fingers crossed. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication, sort of (5.0.18)
Hi all, I have two servers that have production data on them, and then one server where I would like to keep an active copy of the data. Currently, there is a job that runs in the middle of the night that basically does a mysqldump --host=production_server --all-databases | mysql --host=backup_server It takes a long time to copy over all the data, when in reality, it's not changing that much. The great thing about this approach is that yesterdays tables are very nearby and easily accessible in the case where someone fat-fingers some data. The trick to this is that I want the backup server to be one day behind the production server, not real time mirrored, so maybe replication isn't the right thing, I am not sure. I am using the innodb storage engine for most things, though there are a few myIsam tables. Advice appreciated. Thank you. Regards, Rich Duzenbury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Summing Collums
HI all ; i wanna be able to sum a colum in a mysql database and display the sum on the page field name Bondrem so if someone adds to the database the bondremaining colom would add together and show the total on the page record1 - [bondrem]=100 record2 - [bondrem]=450 total bondrem on page would show 550 can anyone help me -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New User Setting up MYSQL
Hi, I am very new to mysql. In the past I was using Access. I don't have any other experience setting up databases. I bought a book on MYSQL and downloaded the installation file to one of our server and then installed it using most of the defaults. I am now at my XP Pro laptop and want to access the database and start doing things such as importing table from access etc. I wasn't exactly sure what to use for a front end so I choose MYSQL Query browser. I am having trouble connecting to the newly created database. MYSQL Query Browser comes up with a screen that asks for a couple of things. Under stored connection I put in OCRPDC which is the name we use for the server I put this on. For stored host I put in the IP address of this server. I left the port as 3306 and arranged for this port to be open during the installation. Under username I have tried both root and cwildeman. I thought during the installation it asked for a user ID and this is what I plugged in. For a password I have used both my normal windows password which I use for many things and our administrator password which I thought I used during the installation. I wasn't sure what to use to Default Schema so I just put in test. I keep getting a connection error number 2003 stating I can't connect to MYSQL server. Is there something that someone can suggest? Thanks, Chuck
RE: MySql Error Number 1130
--- Duzenbury, Rich [EMAIL PROTECTED] wrote: -Original Message- From: Barry [mailto:[EMAIL PROTECTED] Sent: Thursday, April 13, 2006 9:41 AM To: mysql@lists.mysql.com Subject: Re: MySql Error Number 1130 Duzenbury, Rich wrote: localhost means socket. Hmm, I don't believe it does. localhost is a DNS shortcut to the IP address of the local machine. If that is how mysql wants to treat things, then it should issue an error message on connect because --host=localhost and --port=anything would then be mutually exclusive. Thanks. Regards, Rich Wasn't it something like on local machines MySQL doesn't open a socket port because it works directly in program and not going the loop out of mysql and back in through the socket? I'm not sure I understand your statement. It's wise to use a unix domain socket where possible because they perform better than network sockets. However, it's misleading for the mysql client to ignore a command line directive as important as --port or --host without warning. Especially since this can cause connection to the wrong instance. Another way this would have been made more obvious is if the welcome message in the client were a bit more descriptive. Currently, I see 'Your mysql connection id is 2 to server version: 5.0.18-standard-log'. Perhaps the client should indicate the socket or ip/port that was actually used in the welcome message. Thanks. Regards, Rich I couldn't find it with a quick trip through the docs but I seem to remember that using a single period for your host name will force an election to either use the socket or use the IP stack. I'm sorry but I don't remember which way it forces the client to connect but I do know it's only for local connections. shellmysql -u -p -h. -P3307 HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New User Setting up MYSQL
You might want to look get the mysql admin tool. It will call the query browser if needed (so don't get rid of that), but its what you use to supervise the server (set up users etc.) Hope this helps. Chuck Wildeman said: Hi, I am very new to mysql. In the past I was using Access. I don't have any other experience setting up databases. I bought a book on MYSQL and downloaded the installation file to one of our server and then installed it using most of the defaults. I am now at my XP Pro laptop and want to access the database and start doing things such as importing table from access etc. I wasn't exactly sure what to use for a front end so I choose MYSQL Query browser. I am having trouble connecting to the newly created database. MYSQL Query Browser comes up with a screen that asks for a couple of things. Under stored connection I put in OCRPDC which is the name we use for the server I put this on. For stored host I put in the IP address of this server. I left the port as 3306 and arranged for this port to be open during the installation. Under username I have tried both root and cwildeman. I thought during the installation it asked for a user ID and this is what I plugged in. For a password I have used both my normal windows password which I use for many things and our administrator password which I thought I used during the installation. I wasn't sure what to use to Default Schema so I just put in test. I keep getting a connection error number 2003 stating I can't connect to MYSQL server. Is there something that someone can suggest? Thanks, Chuck --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New User Setting up MYSQL
hi chuck... i don't use windows, but are you able to get a cmd window up/running, and what happens if you type mysql in the dir where the mysql.exe resides? do you also have this dir in your path? -bruce -Original Message- From: Chuck Wildeman [mailto:[EMAIL PROTECTED] Sent: Thursday, April 13, 2006 9:07 AM To: mysql@lists.mysql.com Subject: New User Setting up MYSQL Hi, I am very new to mysql. In the past I was using Access. I don't have any other experience setting up databases. I bought a book on MYSQL and downloaded the installation file to one of our server and then installed it using most of the defaults. I am now at my XP Pro laptop and want to access the database and start doing things such as importing table from access etc. I wasn't exactly sure what to use for a front end so I choose MYSQL Query browser. I am having trouble connecting to the newly created database. MYSQL Query Browser comes up with a screen that asks for a couple of things. Under stored connection I put in OCRPDC which is the name we use for the server I put this on. For stored host I put in the IP address of this server. I left the port as 3306 and arranged for this port to be open during the installation. Under username I have tried both root and cwildeman. I thought during the installation it asked for a user ID and this is what I plugged in. For a password I have used both my normal windows password which I use for many things and our administrator password which I thought I used during the installation. I wasn't sure what to use to Default Schema so I just put in test. I keep getting a connection error number 2003 stating I can't connect to MYSQL server. Is there something that someone can suggest? Thanks, Chuck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql Error Number 1130
Interesting. I have never heard of that option. LX09:/home/rduz/backup # mysql -p -h. --port=3307 Enter password: ERROR 2005 (HY000): Unknown MySQL server host '.' (1) Perhaps it no longer functions? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Summing Collums
[snip] i wanna be able to sum a colum in a mysql database and display the sum on the page field name Bondrem so if someone adds to the database the bondremaining colom would add together and show the total on the page record1 - [bondrem]=100 record2 - [bondrem]=450 total bondrem on page would show 550 can anyone help me [/snip] Yes, someone can help you. SELECT SUM(`column`) AS total FROM table; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump ignores auto_increment
Imran, Thanks for your reply. I tried what you asked, and it did the same. I then went onto a couple of other boxes with EXACTLY the same MySQL install and they worked (added the auto_increment). The box that fails is running Redhat 7.3 (for customer backward compatibilty). I dont have any other 7.3 boxes to try - everything else is newer. The my.cnf configuration files are pretty much the same on all installs. The version we are using is (tarball install)... mysql-standard-4.1.16-pc-linux-gnu-i686 The box where it doesnt work is the one on which it is most needed! Many Regards, Ian. On 4/13/06, Imran Chaudhry [EMAIL PROTECTED] wrote: Strange, I created the same table using your definition in my test database: MySQL [EMAIL PROTECTED] test show create table tbl; +---+--+ | Table | Create Table| +---+--+ | tbl | CREATE TABLE `tbl` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM | +---+--+ 1 row in set (0.00 sec) And then with mysqldump: [EMAIL PROTECTED]:/var/www/partner.smoothwall.net/db$ mysqldump test tbl --add-drop-table -Q -- MySQL dump 9.11 -- -- Host: localhostDatabase: test -- -- -- Server version 4.0.24_Debian-10ubuntu2-log -- -- Table structure for table `tbl` -- DROP TABLE IF EXISTS `tbl`; CREATE TABLE `tbl` ( `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM; Can you try again in one step? Create the table and then immediately exit the client and mysqldump it. -- http://www.ImranChaudhry.info MySQL Database Management Design Services
Re: Summing Collums
At 12:51 AM 4/14/2006, Brian E Boothe wrote: HI all ; i wanna be able to sum a colum in a mysql database and display the sum on the page field name Bondrem so if someone adds to the database the bondremaining colom would add together and show the total on the page record1 - [bondrem]=100 record2 - [bondrem]=450 total bondrem on page would show 550 can anyone help me select sum(bondrem) as Total BondRem from table This is covered in the manual. You also have You can also summarize by month to give you monthly results etc. See Group By: http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show Grants problem
Hello Does anyone know why a user would show up in a select from user query but not in a show grants command. What I am trying to say is I tried SHOW GRANTS FOR 'user1'@'%' and received that there is no such grant defined for 'user1'... But when I run SELECT * FROM user WHERE User = 'user1' I do get results. Could the user have been added using an insert causing the SHOW GRANTS to fail? Any Ideas Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
arrg need help summing Colum's
why cant i get this to sum ??? ? $link = mysql_connect(localhost,root,goobers) or die(mysql_error()); mysql_select_db(workorder, $link); $result = mysql_query(SELECT SUM(`ElecRem`) AS total FROM orders, $link); //$total = mysql_fetch_row($result); echo mysql_result($result); // outputs total //return $total[0]; echo mysql_error(); ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arrg need help summing Colum's
- Original Message - From: Brian E Boothe [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 14, 2006 5:09 AM Subject: arrg need help summing Colum's why cant i get this to sum ??? ? $link = mysql_connect(localhost,root,goobers) or die(mysql_error()); mysql_select_db(workorder, $link); $result = mysql_query(SELECT SUM(`ElecRem`) AS total FROM orders, $link); //$total = mysql_fetch_row($result); echo mysql_result($result); // outputs total //return $total[0]; echo mysql_error(); ? It would REALLY REALLY help if you gave us some idea why you think there is anything wrong with this code. You haven't indicated how the code is misbehaving or what statements, if any, are working correctly and which are failing. Are you successfully getting connected to the server? If yes, how do you know? Are you successfully connecting the database ('workorder')? If yes, how do you know? What happens when you run the query? You haven't given us any clue at all. Does the statement work but return an incorrect answer? Does it fail with an error message? If so, what is the error message? Or is it the statement that computes 'total' that is failing in some way? If so, what is wrong with it? All you've done is given us a fragment of code without clearly identifying the failing code or giving us any real symptoms, other than a remark about a summing problem. Without more information it is VERY hard to guess what might be wrong. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.1/310 - Release Date: 12/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arrg need help summing Colum's
I don't think your code should work anyway, you should really check PHP manual to get this work. mysql_query doesn't actually return the result, but a resource id that can be used with mysql_fetch_xxx functions. Try this : $link = mysql_connect(localhost,root,goobers) or die(mysql_error()); mysql_select_db(workorder, $link); $result = mysql_query(SELECT SUM(`ElecRem`) AS total FROM orders, $link); $row = mysql_fetch_array($result); $total=$row['total']; echo $total; Brian E Boothe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] why cant i get this to sum ??? ? $link = mysql_connect(localhost,root,goobers) or die(mysql_error()); mysql_select_db(workorder, $link); $result = mysql_query(SELECT SUM(`ElecRem`) AS total FROM orders, $link); //$total = mysql_fetch_row($result); echo mysql_result($result); // outputs total //return $total[0]; echo mysql_error(); ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Download older versions
Hello, I'm trying to locate the RPM downloads for MySQL version 4.0.26(4.0.26-pc-linux-gnu-i686), for a RH/7.3 system, but can't seem to find them. Doesn't MySQL site have older version archives where these older RPM's can be downloaded ... if so, where would they be located ?? TIA, Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Download older versions
On Thu, Apr 13, 2006 at 07:13:55PM -0500, Mike Blezien wrote: Hello, I'm trying to locate the RPM downloads for MySQL version 4.0.26(4.0.26-pc-linux-gnu-i686), for a RH/7.3 system, but can't seem to find them. Doesn't MySQL site have older version archives where these older RPM's can be downloaded ... if so, where would they be located ?? Old releases can be found at: http://downloads.mysql.com/archives.php Specifically, the RPM packages for 4.0.26 can be found at: http://downloads.mysql.com/archives.php?p=mysql-4.0v=4.0.26 Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Download older versions
Jim - Original Message - From: Jim Winstead [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Thursday, April 13, 2006 7:28 PM Subject: Re: Download older versions On Thu, Apr 13, 2006 at 07:13:55PM -0500, Mike Blezien wrote: Hello, I'm trying to locate the RPM downloads for MySQL version 4.0.26(4.0.26-pc-linux-gnu-i686), for a RH/7.3 system, but can't seem to find them. Doesn't MySQL site have older version archives where these older RPM's can be downloaded ... if so, where would they be located ?? Old releases can be found at: http://downloads.mysql.com/archives.php Specifically, the RPM packages for 4.0.26 can be found at: http://downloads.mysql.com/archives.php?p=mysql-4.0v=4.0.26 Jim Winstead MySQL Inc. Thanks. I guess I missed that. :) Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
i have the sums now i need to sum the Final 4 Sums together, i'll be done Finally !! geesh
o.k o.k ive thrown together this code Inwich DOES work but i have 4 Colombs i need to get this value for and then Sum the bstotals in a sum right now i have this SAME routine but just changing the name of ( btstotal ) to bstotal1 , bstotal2 , bstotal3 so my code in diffrent area's looks like this $sql = SELECT SUM(ElecRem) as btstotal FROM orders ; $sql = SELECT SUM(ProjRem) as btstotal1 FROM orders ; $sql = SELECT SUM(ProjRem) as btstotal2 FROM orders ; in another area how can i sum up the diffrent bstotals ??? like bstotalsum = ('bstotal + bstotal1 + bstotal2'); ? /// ---MAIN CODE ?php $link = mysql_connect(localhost,root,goobers) or die(mysql_error()); mysql_select_db(workorder, $link); $sql = SELECT SUM(ElecRem) as btstotal FROM orders ; $result = mysql_query($sql, $link) or die(mysql_error()); while ($row = mysql_fetch_assoc($result)) { extract($row); } echo 'h9font color=#00FF00' . $btstotal . '/h9'; mysql_free_result($result) or die(mysql_error()); mysql_close($link) or die(mysql_error()); ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]