Check for numeric values
Hello, I need to select some data from a table where a column is not a numerical value but can't seem to get the right syntax for this. Basically we need to do something like this: SELECT * FROM tablename WHERE column_name (IS NOT A NUMERIC VALUE) what is the correct syntax to accomplish this? MySQL version: 5.5 Thank you, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Check for numeric values
Thank you this is very helpful and was what I was looking for. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - Original Message - From: Sukhjinder K. Narula To: Mike Blezien Cc: MySQL List Sent: Tuesday, October 08, 2013 8:08 AM Subject: Re: Check for numeric values Hi, You could use regular expression to do this, here is the example with the reference site that might help you: If your data is 'test', 'test0', 'test', '111test', '111' SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+$'; Result: '111' In regex ^ mean begin, and $ - end. SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+\.?[0-9]*$'; - for 123.12 *But,* select all records where number exists: SELECT * FROM myTable WHERE col1 REGEXP '[0-9]+'; Result: 'test0' and 'test' and '111test' and '111' http://stackoverflow.com/questions/5064977/detect-if-value-is-number-in-mysql Regards. On Tue, Oct 8, 2013 at 7:53 AM, Mike Blezien mick...@frontiernet.netwrote: Hello, I need to select some data from a table where a column is not a numerical value but can't seem to get the right syntax for this. Basically we need to do something like this: SELECT * FROM tablename WHERE column_name (IS NOT A NUMERIC VALUE) what is the correct syntax to accomplish this? MySQL version: 5.5 Thank you, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-**=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=**-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Formatting Numbers with commas
Thank you Simon exactly what I was looking for. Appreciate the assistance. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - Original Message - From: Simon Griffiths To: 'Mike Blezien' ; 'MySQL List' Sent: Sunday, February 12, 2012 10:26 AM Subject: RE: Formatting Numbers with commas Please see http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_format Regards, Simon Griffiths -Original Message- From: Mike Blezien [mailto:mick...@frontiernet.net] Sent: 12 February 2012 16:00 To: MySQL List Subject: Formatting Numbers with commas Hello, Is there a function to automatically format long numercial values before it's entered into the table, i.e I have a value like 159600 and would be entered as 159,600 or 78450 would be entered 78,450 etc., ? Thank you, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Even or Odds numbers
Hello, is there a function, using MySQL 5.0v, that can detect if a numerical value is either an Even or Odd number Thanks, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ Office: 1.712.395.0670 Skype Contact: cgimickalo -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Even or Odds numbers
- Original Message - From: Christoph Boget christoph.bo...@gmail.com To: Mike Blezien mick...@frontiernet.net Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, August 31, 2010 1:06 PM Subject: Re: Even or Odds numbers is there a function, using MySQL 5.0v, that can detect if a numerical value is either an Even or Odd number MOD() http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_mod SELECT MOD( X, 2 ) where X is your number (or column name). If 0, it's even if 1 it's odd. thnx, Christoph Thanks that should do the trick. Appreciate the other response too. Big help :) Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Custom Programming Web Hosting Services http://www.thunder-rain.com/ Office: 1.712.395.0670 Skype Contact: cgimickalo -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Special Characters
Thx's Jerry, appreciate the info, very helpful. ;) Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'Mike Blezien' [EMAIL PROTECTED]; 'MySQL List' mysql@lists.mysql.com Sent: Monday, November 24, 2008 9:21 AM Subject: RE: Special Characters -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: Sunday, November 23, 2008 4:59 PM To: MySQL List Subject: Special Characters Hello, we are in the process of setting up a database with members data and other info. We'll need to enter names with special characters, i.e: apostrophes: O'Rourke tildes: Magglio Ordóñez titlo: Anaïs hyphen: Chun-Myers Is there some special table setup required, collation or storage engine, in order to enter data like this or can this be handled with our programming, Perl? Where using MySQL 5.0.51 [JS] For the most flexibility, I suggest you use utf8_general_ci as the default for your entire database. That's what we use, and it will swallow just about anything (including Chinese). -- 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]
Special Characters
Hello, we are in the process of setting up a database with members data and other info. We'll need to enter names with special characters, i.e: apostrophes: O'Rourke tildes: Magglio Ordóñez titlo: Anaïs hyphen: Chun-Myers Is there some special table setup required, collation or storage engine, in order to enter data like this or can this be handled with our programming, Perl? Where using MySQL 5.0.51 Thx's, 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]
Order by Mixed Alpha/Numeric
Hello, we have a column in a table, called tag numbers, which start with numerical and alpha characters, IE: 012345 123456 LH12345 KB055698 47899901 MO558585 ... etc. now what I need to do is to display all the tag numbers, but order by their alpha prefix, either DESC or ASC. What is the correct syntax to order by the alpha characters, first?? 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]
Birthday format
Hello,
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without rounding off. we just
want the '54' value. We can do it easy enough with our Perl programming, but was
wondering if this can be accomplished within the actual query itself.
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: Birthday format
Chris,
- Original Message -
From: Chris Boget [EMAIL PROTECTED]
To: Mike Blezien [EMAIL PROTECTED]; MySQL List
mysql@lists.mysql.com
Sent: Monday, June 25, 2007 2:15 PM
Subject: Re: Birthday format
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without rounding off. we just
want the '54' value. We can do it easy enough with our Perl programming, but
was wondering if this can be accomplished within the actual query itself.
Wouldn't this work
SELECT FLOOR((TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365 ) AS age
? What version of mysql are you using?
thnx,
Chris
Ok, this worked fine, thanks. We're using Ver., 4.1.22
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday format
thanks, worked prefectly,
Mike
- Original Message -
From: Jerry Schwartz [EMAIL PROTECTED]
To: 'Mike Blezien' [EMAIL PROTECTED]; 'MySQL List'
mysql@lists.mysql.com
Sent: Monday, June 25, 2007 3:01 PM
Subject: RE: Birthday format
mysql SELECT FLOOR(54.75);
+--+
| FLOOR(54.75) |
+--+
| 54 |
+--+
1 row in set (0.05 sec)
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
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Monday, June 25, 2007 2:48 PM
To: MySQL List
Subject: Birthday format
Hello,
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without
rounding off. we just
want the '54' value. We can do it easy enough with our Perl
programming, but was
wondering if this can be accomplished within the actual query itself.
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday format
- Original Message -
From: Mike Aubury [EMAIL PROTECTED]
To: mysql@lists.mysql.com; Mike Blezien [EMAIL PROTECTED]
Cc: Jerry Schwartz [EMAIL PROTECTED]
Sent: Monday, June 25, 2007 3:44 PM
Subject: Re: Birthday format
I might be wrong - but isn't that going to mess up on or around the birthday
because of leap years ?
I know its more complex - but something like :
select year(curdate())-year(1952-06-24)-
(dayofyear(curdate())dayofyear(1952-06-24)) age;
might work better...
Kwel ... this works just as well.
Thx's
Mike
On Monday 25 June 2007 21:07, Mike Blezien wrote:
thanks, worked prefectly,
Mike
- Original Message -
From: Jerry Schwartz [EMAIL PROTECTED]
To: 'Mike Blezien' [EMAIL PROTECTED]; 'MySQL List'
mysql@lists.mysql.com
Sent: Monday, June 25, 2007 3:01 PM
Subject: RE: Birthday format
mysql SELECT FLOOR(54.75);
+--+
| FLOOR(54.75) |
+--+
| 54 |
+--+
1 row in set (0.05 sec)
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
www.giiexpress.com
www.etudes-marche.com
-Original Message-
From: Mike Blezien [mailto:[EMAIL PROTECTED]
Sent: Monday, June 25, 2007 2:48 PM
To: MySQL List
Subject: Birthday format
Hello,
we have a simple query to calculate someones birthday:
SELECT (TO_DAYS(CURDATE()) - TO_DAYS('1952-10-06')) / 365;
= 54.75
Now is there away, using SQL, to remove the .75 without
rounding off. we just
want the '54' value. We can do it easy enough with our Perl
programming, but was
wondering if this can be accomplished within the actual query itself.
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]
--
Mike Aubury
Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Murlain Business Centre, Union Street, Chester, CH1 1QP
--
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: Scheduled backups
Hello, - Original Message - From: John Meyer [EMAIL PROTECTED] To: MySQL General mysql@lists.mysql.com Sent: Monday, May 14, 2007 10:26 AM Subject: Re: Scheduled backups J Trahair wrote: Hi Everyone I have set up a scheduled backup using MySQL Administrator. Stored connection, database, dates and time, even the Windows user password (in fact, blank). It doesn't start at the correct time, or indeed any time. Have I missed something? Thanks for your help. Jonathan Trahair OS and version? this is a nice MySQL B/U bash script we've been using for sometime and works quite nicely, on a LINUX system. MySQL Backup Script VER. 2.5 - http://sourceforge.net/projects/automysqlbackup/ 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: MySQL Daylight Savings Time Patch - easy check
Out of curiousity, what should be done if they results are different. We checked
on one of boxes and got two different results:
SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),
- UNIX_TIMESTAMP('2007-03-11 03:00:00');
+---+---+
| UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00')
|
+---+---+
|117360 |1173603600
|
+---+---+
Thx's
Mickalo
- Original Message -
From: Ryan Stille [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, February 24, 2007 3:28 PM
Subject: Re: MySQL Daylight Savings Time Patch - easy check
Ryan Stille wrote:
Paul DuBois wrote:
At 4:40 PM -0600 2/20/07, Ryan Stille wrote:
Is there an easy way to test to see if MySQL already has the proper tables
loaded?
-Ryan
Yes, reload them. :-) After that, they're current! ...
After digging around on the net for a while I found an easy way to tell if
your MySQL installation is ready for the new daylight savings time.
SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11
03:00:00');
This should return the same value, even though you are feeding it different
times, because this is when the 1 hr change occurs. I get the correct result
on both of my machines. On one of them I've run the suggested
|mysql_tzinfo_to_sql command, on the other, the time zone tables are
completely empty!
Any wisdom on these time zone tables - are they ever used, should I populate
them or not?
-Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch - easy check
Ryan,
I just restart MySQL on the one box that was off, and bingo ... the 2 results
are now the same. I think the system time zone was changed a while back, so
after restarting, it set it back to the default SYSTEM setting.
Mickalo
- Original Message -
From: Ryan Stille [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, February 24, 2007 4:07 PM
Subject: Re: MySQL Daylight Savings Time Patch - easy check
I'm not exactly sure what you should do, hence my earlier question about the
empty time zone tables. I get the same (correct) result on both of my
servers - on one of them I've updated the MySQL time zone tables, and on the
other the tables are empty, always have been. Hopefully someone will weigh in
on this.
Is your OS updated? On Linux you can check by running 'zdump -v
/etc/localtime | grep 2007'. You should see some lines mentioning March 11.
If your OS is not ready for the change, that could be your problem.
You could also try updating the MySQL timezone tables, as was mentioned
earlier in this thread. I did mine with this command:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql -p
And yes I got a few errors, as other people have mentioned in this thread.
But they all appear to be related to overseas timezones so I'm not too
concerned about them.
-Ryan
Mike Blezien wrote:
Out of curiousity, what should be done if they results are different. We
checked on one of boxes and got two different results:
SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),
- UNIX_TIMESTAMP('2007-03-11 03:00:00');
+---+---+
| UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11
03:00:00') |
+---+---+
|117360 |
1173603600 |
+---+---+
Thx's
Mickalo
- Original Message - From: Ryan Stille [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, February 24, 2007 3:28 PM
Subject: Re: MySQL Daylight Savings Time Patch - easy check
Ryan Stille wrote:
Paul DuBois wrote:
At 4:40 PM -0600 2/20/07, Ryan Stille wrote:
Is there an easy way to test to see if MySQL already has the proper
tables loaded?
-Ryan
Yes, reload them. :-) After that, they're current! ...
After digging around on the net for a while I found an easy way to tell if
your MySQL installation is ready for the new daylight savings time.
SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11
03:00:00');
This should return the same value, even though you are feeding it different
times, because this is when the 1 hr change occurs. I get the correct
result on both of my machines. On one of them I've run the suggested
|mysql_tzinfo_to_sql command, on the other, the time zone tables are
completely empty!
Any wisdom on these time zone tables - are they ever used, should I populate
them or not?
-Ryan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Year - Field type
have you try using the datatype YEAR for you table field/column ? Mickalo - Original Message - From: John Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 19, 2007 11:45 AM Subject: Year - Field type Hi Using mysql4 Sure this is an easy one a field in my dbase is year of birth, its always a 4 digit number, for some reason Im failing to sort by the field in my results, it was originally a varchar field so I updated it to int but still no luck. Pointers appreciated. Regards John Berman -- 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]
Calculation distances
Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) TIA, Mike(mickalo)Blezien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation distances
Chris, this is something very similar to what we are attempting to accomplish: http://www.papajohnsonline.com/restlocator/RestaurantLocator Mike - Original Message - From: Chris W [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED]; MYSQL General List mysql@lists.mysql.com Sent: Sunday, August 20, 2006 8:59 PM Subject: Re: Calculation distances Mike Blezien wrote: Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) The zip code tables usually give a lat long location somewhere near the center of that zip code (which can be HUGE in rural areas) You can then use some math that with give you the distance between the coordinates of 2 zip codes. Here is a web site I found for help when doing similar calculations. Remember that this type of math usually wants angle measurements in radians, not degrees. http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random Error
Hello, occassionally we come accross this error: 2013 - Lost connection to MySQL server during query not all the time, but now and then, no real pattern that we can see of find in the logs. but appear to happen mainly with SELECT's is there a way to fix this or trace it down to a cause? 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: Random Error
It's PHP I believe, oscommerce web site, MySQL 4.0.27. this is for an associate of ours and I am not much of a PHP programmer, strickly Perl :) Mike - Original Message - From: Dan Buettner [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Wednesday, August 16, 2006 3:24 PM Subject: Re: Random Error Mike, can you provide more details? OS platform, networking overview, types of apps experiencing this error (PHP app, mysql cli, etc), does it happen with local as well as network connections, etc. This will help us help you. Dan On 8/16/06, Mike Blezien [EMAIL PROTECTED] wrote: Hello, occassionally we come accross this error: 2013 - Lost connection to MySQL server during query not all the time, but now and then, no real pattern that we can see of find in the logs. but appear to happen mainly with SELECT's is there a way to fix this or trace it down to a cause? 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] -- 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]
Checking List
Is this list still working ? Mickalo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
correct syntax
Hello, we need to query one of database tables that sore usernames, and many of them have spaces in them that need to be removed. what would be the correct function to identify each username column value that has a space(s) in them? TIA, Mike(mickalo)Blezien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: correct syntax
Gerald, - Original Message - From: Gerald L. Clark [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Thursday, June 08, 2006 8:40 AM Subject: Re: correct syntax Mike Blezien wrote: Hello, we need to query one of database tables that sore usernames, and many of them have spaces in them that need to be removed. what would be the correct function to identify each username column value that has a space(s) in them? TIA, Mike(mickalo)Blezien How about LIKE % % that seems to work. Thanks :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Baffled by query error syntax
Hello, this is a continued problem we are having from a earlier posting to the list regarding a query. We need to calculate the SUM of the column 'agent_product_time' which is a TIME datatype column and according to the manual: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html this is the way to SUM the total time, which keeps producing a syntax error and figure out why MySQL version 4.1.12 --- SELECT c.account_id,a.name,a.company, SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 GROUP BY c.account_id HAVING mins = '500' ORDER BY mins ERROR: #1064 - 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 '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins FROM account a LEFT JO' at line 1 -- What would be producing the syntax error here.?? Again, any help would be much appreciated. 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: Baffled by query error syntax
Hi Sheeri, Is your 'u.modified' column a TIME datatype '00:00:00' Mike - Original Message - From: sheeri kritzer [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Wednesday, May 17, 2006 9:10 AM Subject: Re: Baffled by query error syntax Mike, I can't really help except to ask if you're sure you copied and pasted the query correctly. I did a similar query against a test system: select u.uid,u.username,b.buddyUid,SEC_TO_TIME(SUM(TIME_TO_SEC(u.modified))) as mins from Users u left join BuddyList b on u.uid = b.uid where u.modified = DATE_SUB(NOW(),INTERVAL 14 DAY) and country=au group by u.uid having mins = '2' order by mins; Similar joins, similar where clause, etc and yet I got an answer (almost 700 rows, took 2 seconds) while you got a syntax error. select @@version; +-+ | @@VERSION | +-+ | 4.1.12-standard-log | +-+ So I'm not sure what to recommend other than trying the query again to make sure there aren't typos. MySQL usually gives a syntax error *where* the error happens. In this case, it would indicate a problem with SEC_TO_TIME( but there shouldn't be a problem, both according to the manual AND according to my example. I would prepare for a bug report -- create 2 new tables in the test db, in this case you don't need a lot of test data, do the join, and if you still get the problem, submit a bug report (you've just done the steps to recreate part). Many times I've done this and realized where my bug was because the query worked in the test table. -Sheeri On 5/17/06, Mike Blezien [EMAIL PROTECTED] wrote: Hello, this is a continued problem we are having from a earlier posting to the list regarding a query. We need to calculate the SUM of the column 'agent_product_time' which is a TIME datatype column and according to the manual: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html this is the way to SUM the total time, which keeps producing a syntax error and figure out why MySQL version 4.1.12 --- SELECT c.account_id,a.name,a.company, SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 GROUP BY c.account_id HAVING mins = '500' ORDER BY mins ERROR: #1064 - 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 '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins FROM account a LEFT JO' at line 1 -- What would be producing the syntax error here.?? Again, any help would be much appreciated. 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Baffled by error
Hello, MySQL 4.1.12 trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id ORDER BY mins ERROR: # - Invalid use of group function Any help appreciated... 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: Baffled by error
Tried that, still same error. If I remove the 'AND SUM(c.agent_product_time) = '500' then it works, but I need this in the query. Mike - Original Message - From: Ing. Edwin Cruz [EMAIL PROTECTED] To: 'Mike Blezien' [EMAIL PROTECTED]; 'MySQL List' mysql@lists.mysql.com Sent: Tuesday, May 16, 2006 12:58 PM Subject: RE: Baffled by error Is because in your select you have c.accound_id and in your group by clause you have a.accound_id Try this: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) = '500' GROUP BY c.account_id ORDER BY mins Regards! -Mensaje original- De: Mike Blezien [mailto:[EMAIL PROTECTED] Enviado el: Martes, 16 de Mayo de 2006 12:30 p.m. Para: MySQL List Asunto: Baffled by error Hello, MySQL 4.1.12 trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id ORDER BY mins ERROR: # - Invalid use of group function Any help appreciated... 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by error
corrected it and still the same error. Mike - Original Message - From: Chris Sansom [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com Sent: Tuesday, May 16, 2006 12:56 PM Subject: Re: Baffled by error At 12:29 -0500 16/5/06, Mike Blezien wrote: trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id ORDER BY mins ERROR: # - Invalid use of group function Any help appreciated... Just a wild guess, but... you have 'ON c.account_id = a.id', but you're grouping by a.account_id. Do both of those definitely exist, or should you be grouping by a.id? -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ It isn't pollution that's harming the environment. It's the impurities in our air and water that are doing it. -- Al Gore, former US Vice President -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by error
Peter, - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, May 16, 2006 1:07 PM Subject: Re: Baffled by error Mike Blezien wrote: Hello, MySQL 4.1.12 trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id ORDER BY mins WHERE doesn't understand aggregation. Move the condition SUM(c.agent_product_time) = 500 to a HAVING clause. pb How would this query be constructed with the HAVING clause, not real fimilar with this type of clause. thx's Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by error
Peter, - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, May 16, 2006 1:25 PM Subject: Re: Baffled by error Mike, How would this query be constructed with the HAVING clause, not real fimilar with this type of clause. SELECT a.id, a.name, a.company, SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 GROUP BY a.account_id HAVING SUM(c.agent_product_time) = 500 ORDER BY mins; PB Much appreciated and it works prefectly now... :) Thx's Mike - Mike Blezien wrote: Peter, - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Tuesday, May 16, 2006 1:07 PM Subject: Re: Baffled by error Mike Blezien wrote: Hello, MySQL 4.1.12 trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id ORDER BY mins WHERE doesn't understand aggregation. Move the condition SUM(c.agent_product_time) = 500 to a HAVING clause. pb How would this query be constructed with the HAVING clause, not real fimilar with this type of clause. thx's Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by error
Gordon, thank you, works prefectly now. Mike - Original Message - From: Gordon [EMAIL PROTECTED] To: 'Mike Blezien' [EMAIL PROTECTED]; 'MySQL List' mysql@lists.mysql.com Sent: Tuesday, May 16, 2006 3:17 PM Subject: RE: Baffled by error Try this SELECT c.account_id, a.name,a.company, SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 GROUP BY a.account_id HAVING SUM(c.agent_product_time) = '500' ORDER BY mins You have to do the SUM with the GROUP BY before you can test the criteria -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 16, 2006 10:30 AM To: MySQL List Subject: Baffled by error Hello, MySQL 4.1.12 trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 9 AND SUM(c.agent_product_time) = '500' GROUP BY a.account_id ORDER BY mins ERROR: # - Invalid use of group function Any help appreciated... 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Older version MySQL
Hello, I am attempting to upgrade one of our older servers running RH 7.3 w/glibc-2.2.5-44 it's currently using 3.23.58 and tried installing 4.0.26 rpm's for linux, but won't install due to the following error: libc.so.6(GLIBC_2.3) is needed by MySQL-shared-4.0.26-0 libpthread.so.0(GLIBC_2.3.2) is needed by MySQL-shared-4.0.26-0 libc.so.6(GLIBC_2.3) is needed by MySQL-shared-compat-4.0.26-0 libpthread.so.0(GLIBC_2.3.2) is needed by MySQL-shared-compat-4.0.26-0 Now of the RPM's will install. Is there a 4.0.+ version that will install on this type of server without have to upgrade alot of other stuff ?? Or can we build it from source, and if so, what is the recommend configure options used to build from source ? TIA, Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Older version MySQL
we where hoping to avoid all that, if possible :) it's not much of a product server anymore, just for some small developement. I guess we'll stick with what's installed. Mike - Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 21, 2006 5:38 PM Subject: RE: Older version MySQL You might like to take a look at smart package manager. It's a wizz at dealing with dependancy conflicts. http://labix.org/smart http://wiki.suselinuxsupport.de/wikka.php?wakka=HowtoSmartPackageManager http://forums.suselinuxsupport.de/index.php?showtopic=21121 HTH Keith In theory, theory and practice are the same; in practice they are not. On Fri, 21 Apr 2006, Nicolas Verhaeghe wrote: To: 'Mike Blezien' [EMAIL PROTECTED], 'MySQL List' mysql@lists.mysql.com From: Nicolas Verhaeghe [EMAIL PROTECTED] Subject: RE: Older version MySQL Dependency issue, I would use RPM Package Manager. It always solved that type of issue. http://www.rpm.org/ -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: Friday, April 21, 2006 3:17 PM To: MySQL List Subject: Older version MySQL Hello, I am attempting to upgrade one of our older servers running RH 7.3 w/glibc-2.2.5-44 it's currently using 3.23.58 and tried installing 4.0.26 rpm's for linux, but won't install due to the following error: libc.so.6(GLIBC_2.3) is needed by MySQL-shared-4.0.26-0 libpthread.so.0(GLIBC_2.3.2) is needed by MySQL-shared-4.0.26-0 libc.so.6(GLIBC_2.3) is needed by MySQL-shared-compat-4.0.26-0 libpthread.so.0(GLIBC_2.3.2) is needed by MySQL-shared-compat-4.0.26-0 Now of the RPM's will install. Is there a 4.0.+ version that will install on this type of server without have to upgrade alot of other stuff ?? Or can we build it from source, and if so, what is the recommend configure options used to build from source ? TIA, Mike(mickalo)Blezien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change datadir in rpm installation
- Original Message - From: tuxlinsecure [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 18, 2006 11:41 AM Subject: Change datadir in rpm installation Hi, I am a newbie. I need to change the datadir after the rpm installation How to do it? Thanks, in your my.cnf under the mysql.server group add: [mysql.server] # datadir location datadir = /var/lib/mysql then restart MySQL and assuming you've create the necessary folder and related database(s) that will be stored there. 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]
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
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]
Days in Month
Hello, is there a MySQL function to determine the total days of any given month, IE if current month is April, is there a function or query to find the total days of the month? TIA, Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting number days between 2 dates
Hello, I'm sure this is a simple query but haven't come up with a good approach. Need to get the number of days between two dates. IE: today's date: (2006-04-01 - 2006-03-05) need to calculate the number of days between these dates.. what is the best query statement to accomplish this? TIA, Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting number days between 2 dates
Jorrit,
- Original Message -
From: Jorrit Kronjee [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, April 01, 2006 7:46 AM
Subject: Re: Getting number days between 2 dates
Mike Blezien wrote:
Hello,
I'm sure this is a simple query but haven't come up with a good approach.
Need to get the number of days between two dates. IE: today's date:
(2006-04-01 - 2006-03-05)
need to calculate the number of days between these dates.. what is the best
query statement to accomplish this?
TIA,
Mike,
You probably want to use something like this:
SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');
Thanks, that works, also using the DAYOFYEAR produces the same results as I just
found :)
appreciate the help
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting number days between 2 dates
thx's Keith, another option :)
Mike
- Original Message -
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, April 01, 2006 10:52 AM
Subject: Re: Getting number days between 2 dates
Use SELECT DATEDIFF('new_date', 'old_date');
mysql SELECT DATEDIFF('2006-04-01','2006-04-01');
+-+
| DATEDIFF('2006-04-01','2006-04-01') |
+-+
| 0 |
+-+
1 row in set (0.00 sec)
mysql SELECT DATEDIFF('2006-04-01','2007-04-01');
+-+
| DATEDIFF('2006-04-01','2007-04-01') |
+-+
|-365 |
+-+
1 row in set (0.00 sec)
mysql SELECT DATEDIFF('2006-04-01','2005-04-01');
+-+
| DATEDIFF('2006-04-01','2005-04-01') |
+-+
| 365 |
+-+
1 row in set (0.00 sec)
DATEDIFF(expr,expr2)
DATEDIFF() returns the number of days between the start date
expr and the end date expr2. expr and expr2 are date or
date-and-time expressions. Only the date parts of the values
are used in the calculation.
mysql SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
- 1
mysql SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
- -31
Regards
Keith
In theory, theory and practice are the same;
in practice they are not.
On Sat, 1 Apr 2006, Rhino wrote:
To: Mike Blezien [EMAIL PROTECTED],
Jorrit Kronjee [EMAIL PROTECTED], mysql@lists.mysql.com
From: Rhino [EMAIL PROTECTED]
Subject: Re: Getting number days between 2 dates
- Original Message - From: Mike Blezien
[EMAIL PROTECTED]
To: Jorrit Kronjee [EMAIL PROTECTED]; mysql@lists.mysql.com
Sent: Saturday, April 01, 2006 9:00 AM
Subject: Re: Getting number days between 2 dates
Jorrit,
- Original Message - From: Jorrit Kronjee
[EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Saturday, April 01, 2006 7:46 AM
Subject: Re: Getting number days between 2 dates
Mike Blezien wrote:
Hello,
I'm sure this is a simple query but haven't come up with a
good approach. Need to get the number of days between two
dates. IE: today's date: (2006-04-01 - 2006-03-05)
need to calculate the number of days between these dates..
what is the best query statement to accomplish this?
TIA,
Mike,
You probably want to use something like this:
SELECT TO_DAYS('2006-03-15') - TO_DAYS('2006-03-01');
Thanks, that works, also using the DAYOFYEAR produces the same
results as I just found :)
appreciate the help
I'd be careful with DAYOFYEAR() if I were you.
DAYOFYEAR() only tells you which day it is within a given year. If you try
to use DAYOFYEAR to tell the difference in days between dates that are in
different years, you are certainly going to get the wrong answer. For
instance, DAYOFYEAR(2006-04-01) - DAYOFYEAR(2005-04-01) gives an answer of
0 days when the correct answer is 365.
A better choice for getting the difference between two dates in days is
probably DATEDIFF() or TO_DAYS().
--
Rhino
--
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]
Update multiple tables
Hello, I'm alittle unclear on how too update multiple tables. We have two tables with the same column name: account.state account_service.state when we update the account table, we also need to update the account_service table with the same value for the 'state' column. Can this be done with MySQL 4.1.12. and what is the correct syntax? Thx's Mickalo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem restarting server
Yes, that was the problem, the my.cnf file was missing, replace it, restarted and all is well :) thx's Mike - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 22, 2006 2:10 AM Subject: Re: Problem restarting server Mike, - Original Message - From: Mike Blezien [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 22, 2006 3:42 AM Subject: Problem restarting server Hello, we had to reboot our server and now we can't get MySQL started, in the error log it states: == 060321 19:34:13 mysqld started 060321 19:34:13 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: Error: log file ./ib_logfile0 is of different size 0 67108864 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes! 060321 19:34:13 [ERROR] Can't init databases 060321 19:34:13 [ERROR] Aborting 060321 19:34:13 [Note] /usr/sbin/mysqld: Shutdown complete 060321 19:34:13 mysqld ended = How can this problem be fixed so we can restart MySQL server, kind of in a bind here now... mysqld is probably reading a different my.cnf file now than it did when you started mysqld last time. Have you edited my.cnf or replaced it while mysqld was running? Or could mysqld fail to find a my.cnf file altogether? You should determine where your my.cnf is, and where your ibdata files and ib_logfiles are, and specify in my.cnf appropriately: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html innodb_data_file_path innodb_log_group_home_dir innodb_log_files_in_group innodb_log_file_size TIA, Mike Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem restarting server
Hello, we had to reboot our server and now we can't get MySQL started, in the error log it states: == 060321 19:34:13 mysqld started 060321 19:34:13 [Warning] Asked for 196608 thread stack, but got 126976 InnoDB: Error: log file ./ib_logfile0 is of different size 0 67108864 bytes InnoDB: than specified in the .cnf file 0 5242880 bytes! 060321 19:34:13 [ERROR] Can't init databases 060321 19:34:13 [ERROR] Aborting 060321 19:34:13 [Note] /usr/sbin/mysqld: Shutdown complete 060321 19:34:13 mysqld ended = How can this problem be fixed so we can restart MySQL server, kind of in a bind here now... TIA, Mike 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]
Perl MySQL
Hello, MySQL version: 4.1.10a we are getting this error when attempting to connect to our MySQL database: install_driver(mysql) failed: Can't load '/usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.10: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 6) line 3 Compilation failed in require at (eval 6) line 3. Perhaps a required shared library or dll isn't installed where expected Now I recall reading something about this, something to do with the DBD version used. Or is this something too do with the MySQL version we are using? Any info be much appreciated. TIA, -- Mike(mickalo)Blezien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database design help
Hello, we currently have a small database setup for affilates and visitor/leads. I believe we have a one to many application, one affiliate can have several visitor/leads but each visitor can only be assigned to one affiliate. What I need to know if this the best design for this setup. Basically a visitor fills out a form, and is assigned to one affiliate. So I was wondering is it better to create a joining table between the `affiliates` table and the `visitors` table or will this design be efficent as it is. Below are the 2 tables in question CREATE TABLE IF NOT EXISTS affiliates ( affid int(6) unsigned NOT NULL auto_increment, affiliate_id int(10) unsigned NOT NULL default '', affiliate_email varchar(60) NOT NULL default '', PRIMARY KEY (affid), KEY affiliate_id (affiliate_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS visitors ( visitorid int(6) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', email varchar(60) NOT NULL default '', state char(2) NOT NULL default '', ip varchar(20) NOT NULL default '', dtime datetime NOT NULL default '-00-00 00:00:00', exported varchar(10) default NULL, affid int(6) unsigned NOT NULL default '0', PRIMARY KEY (visitorid), KEY email (email), KEY affid (affid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; TIA, -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design help
OK, I think I got it now. Thanks for the additional info, that helps alot. Rhino wrote: - Original Message - From: Mike Blezien [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Friday, February 17, 2006 7:49 AM Subject: Database design help Hello, we currently have a small database setup for affilates and visitor/leads. I believe we have a one to many application, one affiliate can have several visitor/leads but each visitor can only be assigned to one affiliate. What I need to know if this the best design for this setup. Basically a visitor fills out a form, and is assigned to one affiliate. So I was wondering is it better to create a joining table between the `affiliates` table and the `visitors` table or will this design be efficent as it is. Below are the 2 tables in question CREATE TABLE IF NOT EXISTS affiliates ( affid int(6) unsigned NOT NULL auto_increment, affiliate_id int(10) unsigned NOT NULL default '', affiliate_email varchar(60) NOT NULL default '', PRIMARY KEY (affid), KEY affiliate_id (affiliate_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS visitors ( visitorid int(6) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', phone varchar(20) NOT NULL default '', email varchar(60) NOT NULL default '', state char(2) NOT NULL default '', ip varchar(20) NOT NULL default '', dtime datetime NOT NULL default '-00-00 00:00:00', exported varchar(10) default NULL, affid int(6) unsigned NOT NULL default '0', PRIMARY KEY (visitorid), KEY email (email), KEY affid (affid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; When you say joining table, I assume you mean an intersection table or association table, which are the more common terms that describe something used to break a many-to-many relationship into two one-to-many relationships. I've never heard it described as a joining table but I _think_ we're talking about the same thing In any case, I don't think you need anything but the two tables you have here. If there is only ever going to be a single affiliate assigned to a given visitor, then this is a one-to-many relationship and there is no need for an additional table. However, I would suggest one small amendment to your visitors table. Add the clause: FOREIGN KEY (affid) references affiliates(affid) on delete INSERT A DELETE RULE HERE This will ensure that you never add an affid other than a value found in the Affiliates table to the affid column of the visitors table. It will also ensure the proper behaviour when deletes take place in the affiliates table. For example, if you use ON DELETE CASCADE as your delete rule, if one of the affliates is deleted from the affiliates table, all of the rows with his ID will also be deleted from the visitors table. If you use ON DELETE RESTRICT, you will not be able to delete an affiliate from the affiliates table unless all of the Visitors rows with his ID have had their affid changed to that of some other affiliate. If you use on DELETE SET NULL, you can freely delete affiliates even if they have rows in the Visitors table; the Visitors rows will just have their affids set to null, which effectively means that those Visitors have no assigned affiliate. -- Rhino -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: working w/UK postcodes
Yes, after some further research, I found a Perl Modules that handles this queit nicely. thx's [EMAIL PROTECTED] wrote: I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? The only effective way to do this is with geographic coordinates (your XY, I guess). While US zip codes _generally_ increase east to west and _usually_ have a numeric difference somewhat related to their geographic distance, this isn't always true. I know of two island zip codes (unrelated to those that surround it) in New York State. -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: working w/UK postcodes
Andy, thank you, this is very helpful understanding the UK postcode scheme, appreciate your input. Andy Eastham wrote: Hi, There is a database available which maps post codes to grid references. This is controlled by the Royal Mail. See http://www.royalmail.com/portal/rm/jump2?mediaId=400088catId=400084 This may also be interesting: http://www.jibble.org/ukpostcodes/ In case you don't know, UK post codes consist of between 5 and seven characters which are in four parts with increasing resolution: City/Area Code (one or two letters) District (one or two digits) Sector (one digit) Unit (two letters) See http://www.equibrand.co.uk/postcodes.html They are usually written with a space between the District and Sector. Eg: W1 2AA MK6 3AB SN25 1DD This structure means you can easily give a central location for a partial postcode, ie right truncated to the sector, district or just the city. Hope this helps, Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 January 2006 13:12 To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: working w/UK postcodes I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? The only effective way to do this is with geographic coordinates (your XY, I guess). While US zip codes _generally_ increase east to west and _usually_ have a numeric difference somewhat related to their geographic distance, this isn't always true. I know of two island zip codes (unrelated to those that surround it) in New York State. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UK Postcodes
Hello, we are working with a database that stores UK postcodes, which are different then US zipcodes. I've found alot of information for working with zipcodes, locating closed distances within a zipcode range, but haven't found anything regarding working with UK type postcodes. Was hoping someone on the list may have worked with UK postcodes and may have some info on the best way to query these postcodes for locating closed location, distances,.etc? thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UK Postcodes
Hello, we are working with a database that stores UK postcodes, which are different then US zipcodes. I've found alot of information for working with zipcodes, locating closed distances within a zipcode range, but haven't found anything regarding working with UK type postcodes. Was hoping someone on the list may have worked with UK postcodes and may have some info on the best way to query these postcodes for locating closed location, distances,.etc? thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UK Postcodes
Hello, we are working with a database that stores UK postcodes, which are different then US zipcodes. I've found alot of information for working with zipcodes, locating closed distances within a zipcode range, but haven't found anything regarding working with UK type postcodes. Was hoping someone on the list may have worked with UK postcodes and may have some info on the best way to query these postcodes for locating closed location, distances,.etc? thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Testing
is the list still working ? -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
working w/UK postcodes
Hello, I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrade RH 7.3
Hello, we are currently running std. MySQL 4.0.26 on an old production server using RH 7.3, using the std rpms downloaded from: http://sunsite.informatik.rwth-aachen.de/mysql/Downloads/MySQL-4.0/ we would like to upgrade to 4.1.15, which I believe is the current stable release, and was wondering if there are any problems encountered with RH 7.3 and MySQL 4.1+ that we should be aware prior to upgrading. Which rpms should we use to do the upgrade? we run the std MySQL server. TIA, -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with tricky query
Hello,
I am trying to put together a single query from across 4 tables and not having a
problem getting the results needed. table structure:
##
CREATE TABLE IF NOT EXISTS job_posts (
jobid int(6) unsigned NOT NULL auto_increment,
job_vacancy mediumtext NOT NULL,
job_information mediumtext NOT NULL,
job_procedure mediumtext NOT NULL,
job_posttime varchar(10) NOT NULL default '',
PRIMARY KEY (jobid)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_job (
jobid int(6) unsigned NOT NULL default '0',
schoolid int(6) unsigned NOT NULL default '0',
KEY jobid (jobid,schoolid)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_region (
accountid int(6) unsigned NOT NULL default '0', # equal as schoolid
region_id tinyint(2) unsigned NOT NULL default '0',
country char(2) NOT NULL default '',
PRIMARY KEY (accountid),
KEY region_id (region_id)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_access (
accountid int(6) unsigned NOT NULL auto_increment, # equal to schoolid
groupid enum('sc_school','sc_user','sc_admin') NOT NULL default 'sc_school',
username varchar(40) binary NOT NULL default '',
password varchar(20) NOT NULL default '',
status enum('active','pending','expired','pending_paid') NOT NULL default
'pending',
acctdays smallint(4) NOT NULL default '0',
regdate date NOT NULL default '-00-00',
PRIMARY KEY (accountid),
KEY username (username,status,acctdays)
) TYPE=InnoDB;
##3
what we need to obtain in the query are the following.
all data from `job_posts', schoolid from `school_job`, country from
`school_region` where `school_access` status = 'active'
the `school_job` and `school_access` tables are also used to tie(join) too a
`school_profile` table(all personal data)
any help with optimizing a single query would be appreciated.
TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with tricky query
Hello,
[EMAIL PROTECTED] wrote:
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 08:34:56 AM:
Hello,
I am trying to put together a single query from across 4 tables and
not having a
problem getting the results needed. table structure:
##
CREATE TABLE IF NOT EXISTS job_posts (
jobid int(6) unsigned NOT NULL auto_increment,
job_vacancy mediumtext NOT NULL,
job_information mediumtext NOT NULL,
job_procedure mediumtext NOT NULL,
job_posttime varchar(10) NOT NULL default '',
PRIMARY KEY (jobid)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_job (
jobid int(6) unsigned NOT NULL default '0',
schoolid int(6) unsigned NOT NULL default '0',
KEY jobid (jobid,schoolid)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_region (
accountid int(6) unsigned NOT NULL default '0', # equal as schoolid
region_id tinyint(2) unsigned NOT NULL default '0',
country char(2) NOT NULL default '',
PRIMARY KEY (accountid),
KEY region_id (region_id)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_access (
accountid int(6) unsigned NOT NULL auto_increment, # equal to
schoolid
groupid enum('sc_school','sc_user','sc_admin') NOT NULL default
'sc_school',
username varchar(40) binary NOT NULL default '',
password varchar(20) NOT NULL default '',
status enum('active','pending','expired','pending_paid') NOT NULL
default
'pending',
acctdays smallint(4) NOT NULL default '0',
regdate date NOT NULL default '-00-00',
PRIMARY KEY (accountid),
KEY username (username,status,acctdays)
) TYPE=InnoDB;
##3
what we need to obtain in the query are the following.
all data from `job_posts', schoolid from `school_job`, country from
`school_region` where `school_access` status = 'active'
the `school_job` and `school_access` tables are also used to tie(join)
too a
`school_profile` table(all personal data)
any help with optimizing a single query would be appreciated.
I can't see (based on your column names) how to associate one row from
job_posts to any other row(s) in either school_region or school_access.
Did you leave out a table definition (or two)? I can go through school_job
to get at the schoolid but I still cannot relate to either of those
tables. I need some way of matching either jobid to accountid or schoolid
to accountid.
I have to have some way of tying those four tables together in order to
build your query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
the `school_job` table should tie them together, the jobid and schoolid would
tie them together in the `job_posts` table, the schoolid(in the `school_job`
table), would be the same value as the accountid in the `school_access` and
`school_region` tables, just assigned a different column name for other reasons.
by the way, this is MySQL Ver., 3.23.58 which I can not upgrade either
Hope this helps. :)
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with tricky query
[EMAIL PROTECTED] wrote:
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 10:08:24 AM:
Hello,
[EMAIL PROTECTED] wrote:
Mike Blezien [EMAIL PROTECTED] wrote on 10/04/2005 08:34:56
AM:
Hello,
I am trying to put together a single query from across 4 tables and
not having a
problem getting the results needed. table structure:
##
CREATE TABLE IF NOT EXISTS job_posts (
jobid int(6) unsigned NOT NULL auto_increment,
job_vacancy mediumtext NOT NULL,
job_information mediumtext NOT NULL,
job_procedure mediumtext NOT NULL,
job_posttime varchar(10) NOT NULL default '',
PRIMARY KEY (jobid)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_job (
jobid int(6) unsigned NOT NULL default '0',
schoolid int(6) unsigned NOT NULL default '0',
KEY jobid (jobid,schoolid)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_region (
accountid int(6) unsigned NOT NULL default '0', # equal as schoolid
region_id tinyint(2) unsigned NOT NULL default '0',
country char(2) NOT NULL default '',
PRIMARY KEY (accountid),
KEY region_id (region_id)
) TYPE=InnoDB;
CREATE TABLE IF NOT EXISTS school_access (
accountid int(6) unsigned NOT NULL auto_increment, # equal to
schoolid
groupid enum('sc_school','sc_user','sc_admin') NOT NULL default
'sc_school',
username varchar(40) binary NOT NULL default '',
password varchar(20) NOT NULL default '',
status enum('active','pending','expired','pending_paid') NOT NULL
default
'pending',
acctdays smallint(4) NOT NULL default '0',
regdate date NOT NULL default '-00-00',
PRIMARY KEY (accountid),
KEY username (username,status,acctdays)
) TYPE=InnoDB;
##3
what we need to obtain in the query are the following.
all data from `job_posts', schoolid from `school_job`, country from
`school_region` where `school_access` status = 'active'
the `school_job` and `school_access` tables are also used to tie(join)
too a
`school_profile` table(all personal data)
any help with optimizing a single query would be appreciated.
I can't see (based on your column names) how to associate one row from
job_posts to any other row(s) in either school_region or
school_access.
Did you leave out a table definition (or two)? I can go through
school_job
to get at the schoolid but I still cannot relate to either of those
tables. I need some way of matching either jobid to accountid or
schoolid
to accountid.
I have to have some way of tying those four tables together in order
to
build your query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
the `school_job` table should tie them together, the jobid and schoolid
would
tie them together in the `job_posts` table, the schoolid(in the
`school_job`
table), would be the same value as the accountid in the `school_access`
and
`school_region` tables, just assigned a different column name for
other reasons.
by the way, this is MySQL Ver., 3.23.58 which I can not upgrade either
Hope this helps. :)
It certainly does
SELECT
jp.*
, sj.schoolid
, sr.country
FROM job_posts jp
INNER JOIN school_job sj
ON sj.jobid = jp.jobid
INNER JOIN school_access sa
ON sa.accountid = sj.schoolid
LEFT JOIN school_region sr
ON sj.schoolid = sr.accountid
WHERE sa.status = 'active';
That will list every job in any school (whether it has a region or not)
where the status is 'active'. Does this answer your needs?
I think this should do it. many thanks for the help :)
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex Query
Hello,
I have a search query to build on two table below is the table structure, and I
need to build a query to search on specific cities(or all cities),specific
subjects(or all subjects) and specific levels(or all levels)
# Table structure for tt_jobs and tt_jobbids
# tt_jobs
pid int(6) unsigned NOT NULL auto_increment,
ttid int(6) unsigned NOT NULL default '0',
jobid varchar(10) NOT NULL default '',
title varchar(60) NOT NULL default '',
subjects smallint(4) NOT NULL default '0',
levels smallint(4) NOT NULL default '0',
cities smallint(4) NOT NULL default '0',
location varchar(250) NOT NULL default '',
day_required varchar(4) NOT NULL default '',
tuition_place tinyint(2) NOT NULL default '0',
duration smallint(4) NOT NULL default '0',
students varchar(6) NOT NULL default '',
tuition_description text,
status enum('open','closed','tempclosed') NOT NULL default 'open',
PRIMARY KEY (pid),
KEY ttid (ttid),
KEY searchidx (cities,levels,subjects)
# tt_jobbids
pid int(6) unsigned NOT NULL default '0',
ttid int(6) unsigned NOT NULL default '0',
bidamt decimal(4,2) NOT NULL default '0.00',
bidtime varchar(10) NOT NULL default '',
bidstatus enum('pending','tutoraccept','tuteeaccept','reject') NOT NULL
default 'pending',
bidexpires varchar(20) NOT NULL default '',
KEY pid (pid),
KEY ttid (ttid),
KEY bidexpires (bidexpires)
Now the search can be on all cities,
levels and subjects which is no problem, it just extracts all open jobs.
# Query to display all open jobs and works fine
SELECT js.pid,js.title,js.subjects,js.levels,js.cities,COUNT(jb.ttid) AS
totalbid FROM tt_jobs js LEFT JOIN tt_jobbids jb ON js.pid = jb.pid
WHERE js.status = 'open' GROUP BY js.pid ORDER BY js.pid DESC
Now I need to be able to perform a search on these two tables but with
specific queries on cities, and subjects and levels. The cities,subjects and
levels columns data are all numerical values(IE 1 - 100).
Now when the search is submitted it can be performed in various ways
# (X = a numerical value)
all cities and subjects = X and levels = X
all subjects and all cites and subject = X
all cities and level = X and subject = X
all levels and all subjects and cities = X
subject = X and cities = X and all levels
subject = X and cities = X and levels = X
And so on, in various combinations. And I haven't come up with
a workable solution to do this. And was hoping someone could suggest
a feasible query to do this or do I need to restructure the tables that
are being searched on??
Hope this is clear :)
TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using AVG
Hello, when using the AVG function like this: SELECT AVG((5+8+10)/3) AS rate; it returns NULL?? the AVG can be used to do a literal math calculation ?? TIA -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using AVG
Simon Garner wrote: Mike Blezien wrote: Hello, when using the AVG function like this: SELECT AVG((5+8+10)/3) AS rate; it returns NULL?? the AVG can be used to do a literal math calculation ?? TIA That doesn't make any sense... AVG is a GROUP BY function. If you have 3 rows with values 5, 8 and 10 then surely SELECT (5+8+10)/3 AS rate; is what you want? -Simon Exactly, my mistake... too long of a day. got my logic mixed up :) thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-Language setup
Hello, we are working on a project that will be using multiple languages to be displayed and stored in a database, for producing various text strings in english and the language associted with it's english counter part. What do we need to consider before creating the database and tables that will be used to store this type of data with various language being stored due to the various unique characters used in many languages. TIA, -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ Tel: 1.712.395.0670 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi-Language setup
Hello, we are working on a project that will be using multiple languages to be displayed and stored in a database, for producing various text strings in english and the language associted with it's english counter part. What do we need to consider before creating the database and tables that will be used to store this type of data with various language being stored due to the various unique characters used in many languages. TIA, -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-Language setup
Gleb Paharenko wrote: Hello. Unicode is usually used in such cases. Mike Blezien [EMAIL PROTECTED] wrote: Hello, we are working on a project that will be using multiple languages to be displayed and stored in a database, for producing various text strings in english and the language associted with it's english counter part. What do we need to consider before creating the database and tables that will be used to store this type of data with various language being stored due to the various unique characters used in many languages. Unfortunately, if I am reading the docs correctly, this is only support in MySQL 4.1 and older version do not support changing or assigning character sets. or am I wrong on this ?? Thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Functions
Hello,
Sorry for the slightly OT question :)
Hoping we have some expert MySQL Function to perl function people on the list.
Is there an equivelant function in perl for converting IP's, the same as MySQL
functions: INET_ATON('IP_ADDRESS_STR') and INET_NTOA('STR')
TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump privileges
Hello, what type of privileges need to be assigned and/or changed when upgrading form MySQL 4.0.24 to 4.1.10 in order to allow users use of the mysqldump utility. Users where allowed to use the mysqldump to backup their databases and worked fine with 4.0.24, till the upgrade to 4.1.10 which no will not allow them access to their respective database any more. We get alot of access denied to databases... lock tables.. or other options. TIA -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ Tel: 1.712.395.0670 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table handler errors
Hello, we started getting these table handler errors 12, not all the time, but just now then. this is a FreeBSD 4.9-RELEASE O/S, w/MySQL 4.0.24 installed. The database in question is used by this vBulletin BBS system. Not sure it's a problem with the BBS or how MySQL is configured. Was wonder if anyone may have some insight on how to remedy this type of error. TIA, -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table handler errors
Jigal van Hemert wrote: From: Mike Blezien we started getting these table handler errors 12, not all the time, but just now For errors from the operating system you can use perror to find out the description of the code: http://dev.mysql.com/doc/mysql/en/perror.html Regards, Jigal. Yes, I've going through the docs and the manual i have hear locally, couldn't really find a specific reason and/or causing for the error. The table type that was causing the error was a HEAP table, changed it to a MyISAM table type and the error disappeared, no more handler errors now... go figure! Thx's -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY sorting
Hello, I'm trying to figure out how sort a column alphabetically, but some of the values start with a numerical value. So when I do the ORDER BY column_name all the values starting alphabeticlly are listed first, then the numerical values after that.. can this be done in a single query.. MySQL ver. 4.0.20 TIA, -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext doesn't seem to find certain text
mos wrote: At 01:54 PM 9/14/2004, you wrote: If I perform a fulltext search for 'foo', it won't match text like 'foo, inc'. Why? Mysql 3.23.52 Joe, By default MySQL has a 4 letter minimum for fulltext searches; words must be at least 4 letters in length otherwise they won't get added. Since you are using MySQL 3.23, you will need to recompile the binaries. With MySQL 4.x, you can simply change a variable in the configuration file and rebuild the fulltext indexes. Mike What variable setting is changed in the my.cnf, and is this under the mysqld group ?? What is the proceedure to rebuild the FT indexes and the changes are made ?? TIA -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ Tel: 1.712.395.0670 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Date Query
Michael, Michael Stassen wrote: Mike Blezien wrote: Hello, Not sure this is possible to do with SQL or if needs to be done with in the application programming language we're using(Perl), but I need to get the a weekdate, IE SELECT EXTRACT(DAY FROM NOW()) which would return 30 Now what I need to do, is if the query above is greater or less then 15, get the next month or current month date for the 15th. IE if I ran this query today, and the value is 30(15), I need to generate the date 2004-09-15. If the query was run on 2004-09-14(15) generate the date 2004-09-15, if the query was run on 2004-12-25(15), generate the date for 2005-01-15 Is this possible to do with MySQL ?? Thx's Mickalo Yes. How about SELECT IF(DAYOFMONTH(CURDATE()) = 15, CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()), '-15'), CONCAT(YEAR(CURDATE() + INTERVAL 1 MONTH), '-', MONTH(CURDATE() + INTERVAL 1 MONTH),'-15')) AS next15; or better yet SELECT IF(DAYOFMONTH(CURDATE()) = 15, DATE_FORMAT(CURDATE(), '%Y-%m-15'), DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15; works like a charm, thx's :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tricky Date Query
Hello, Not sure this is possible to do with SQL or if needs to be done with in the application programming language we're using(Perl), but I need to get the a weekdate, IE SELECT EXTRACT(DAY FROM NOW()) which would return 30 Now what I need to do, is if the query above is greater or less then 15, get the next month or current month date for the 15th. IE if I ran this query today, and the value is 30(15), I need to generate the date 2004-09-15. If the query was run on 2004-09-14(15) generate the date 2004-09-15, if the query was run on 2004-12-25(15), generate the date for 2005-01-15 Is this possible to do with MySQL ?? Thx's Mickalo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Possible
Hello, MySQL version 4.0.20 I'm trying to figure out, if it's possible to do, with one query to update a single table. Query #1 select m.memid from members m left join active a on a.memid = m.memid where a.acct_days 0 and m.account = 1; Query # 2 update members set account = 2 where memid in (results from query #1) Can this all be done in one sql statement, in query 2? TIA Mickalo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Possible
Michael Stassen wrote: First, I think the LEFT JOIN is unnecessary. Rows in members without a corresponding row in active will not have acct_days0 (it'll be NULL), so, by using LEFT JOIN you are (possibly) creating rows only to discard them. I think a simple join will do. You want a multi-table update: UPDATE members JOIN active ON active.memid = members.memid SET mmembers.account = 2 WHERE active.acct_days 0 AND members.account = 1; This works starting with 4.0.4, so it will work in your 4.0.20. See the manual http://dev.mysql.com/doc/mysql/en/UPDATE.html for more. Michael Mike Blezien wrote: Hello, MySQL version 4.0.20 I'm trying to figure out, if it's possible to do, with one query to update a single table. Query #1 select m.memid from members m left join active a on a.memid = m.memid where a.acct_days 0 and m.account = 1; Query # 2 update members set account = 2 where memid in (results from query #1) Can this all be done in one sql statement, in query 2? Thx's appreciate the info. This should do the trick :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Authentication Scheme Bypass
Hello, Happen to come accross this articale http://www.securiteam.com/unixfocus/5BP0420DFQ.html and was wondering, using 4.0.20, if we need to make some changes or has this been addressed by MySQL staff ?? TIA, -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query last record in table
Hello, what is the most effecient way to query the last record in a table, if querying the primary key(acctid) column ?? I've tried select *,max(acctid) as lastid from subscribers where acctid = 'lastid' group by acctid but this returns nothing ?? TIA :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Query last record in table]
cancel this, found my problem :) this query worked: SELECT * FROM subscribers ORDER BY acctid DESC LIMIT 1 Original Message Subject: Query last record in table Date: Mon, 05 Jul 2004 13:52:28 -0500 From: Mike Blezien [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Organization: Thunder Rain Internet Publishing To: MySQL List [EMAIL PROTECTED] Hello, what is the most effecient way to query the last record in a table, if querying the primary key(acctid) column ?? I've tried select *,max(acctid) as lastid from subscribers where acctid = 'lastid' group by acctid but this returns nothing ?? TIA :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Packet Errors
Hello, I recently noticed this error in our mysql error log file: Aborted connection 5439 to db: 'database_name' user: 'someuser' host: `localhost' (Got an error reading communication packets) --- we're running MySQL 4.0.20, for pc-linux (i686) on a RH7.3, build from the standard RPM's it seems this just standard recently from what I can see in the logs. Is there something we can do to eliminate this or prevent it, if possible. ?? TIA MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Packet Errors
Thx's :) David Griffiths wrote: These errors could mean a connection timed out, or a mysql-client didn't properly close the connection, or possibly a network error. I went to mysql.com and looked in the searchable docs: http://dev.mysql.com/doc/mysql/en/Communication_errors.html If |Aborted connections| messages appear in the error log, the cause can be any of the following: * The client program did not call |mysql_close()| before exiting. * The client had been sleeping more than |wait_timeout| or |interactive_timeout| seconds without issuing any requests to the server. See section 5.2.3 Server System Variables http://dev.mysql.com/doc/mysql/en/Server_system_variables.html. * The client program ended abruptly in the middle of a data transfer. When any of these things happen, the server increments the |Aborted_clients| status variable. Those searchable docs are very handy for looking up error codes, etc. David. Mike Blezien wrote: Hello, I recently noticed this error in our mysql error log file: Aborted connection 5439 to db: 'database_name' user: 'someuser' host: `localhost' (Got an error reading communication packets) --- we're running MySQL 4.0.20, for pc-linux (i686) on a RH7.3, build from the standard RPM's it seems this just standard recently from what I can see in the logs. Is there something we can do to eliminate this or prevent it, if possible. ?? TIA MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Correcting Time
Hello, Somehow the mysql server time is set 3 hours earlier than the system time(CDT), is there a way to correct this so they both are the same ? TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions 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]
MySQL Warnings
Hello, Noticed this morning when restarting MySQL, saw this in the error log file: Version 4.0.20(RPM's) --- 040601 9:17:17 /usr/sbin/mysqld: Normal shutdown 040601 9:17:18 InnoDB: Starting shutdown... 040601 9:17:20 InnoDB: Shutdown completed 040601 9:17:20 /usr/sbin/mysqld: Shutdown Complete 040601 09:17:20 mysqld ended 040601 09:17:20 mysqld started 040601 9:17:20 Warning: Asked for 196608 thread stack, but got 126976 040601 9:17:21 InnoDB: Started - Is this type of Warning something to be concerned about or is this common or how best to fix ?? This is on a RH/Linux 7.3 box. Also, we noticed if the innodb_force_recovery = X is enabled in the my.cnf file, we can't insert any data without getting table handler errors (-1) and something in the error log file about change newraw to raw and removing the innodb_force_recovery ?? Why is that ?? TIA, -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions 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: MySQL Warnings
thx's for the input :) Mike Jigal van Hemert wrote: From: Mike Blezien [EMAIL PROTECTED] 040601 9:17:20 Warning: Asked for 196608 thread stack, but got 126976 Is this type of Warning something to be concerned about or is this common or how best to fix ?? --quote from Sergei Golubchik [EMAIL PROTECTED] I think, this is because --log-warnings was changed to be ON by default. Disable with --skip-log-warnings --end quote Yep, it works, the warnings disappear (no surprise really...) Unfortunately after 3-4 questions on this list about these warnings nobody from MySQL AB seems to think it's worth giving an answer about the seriousness of the warning and how to solve it (instead of surpressing it). Anyway the MySQL server seems to run fine, despite the smaller thread stack.. Best regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL update 4.0.20 w/RPM's
Hello, got this error when upgrading our MySQL: rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm Preparing...### [100%] file /usr/lib/libmysqlclient.so.12.0.0 from install of MySQL-shared-4.0.20-0 conflicts with file from package MySQL-shared-compat-4.0.18-0 file /usr/lib/libmysqlclient_r.so.12.0.0 from install of MySQL-shared-4.0.20-0 conflicts with file from package MySQL-shared-compat-4.0.18-0 MySQL upgraded to version 4.0.20 with no apparent problems so far, from 4.0.18 but was wondering if this could be a problem down the road or should we remove the old shared or shared-compact RPM's, then do the upgrade to the 4.0.20 RPM's ?? These are the current installed RPM's, O/S RH/Linux 7.3: MySQL-bench-4.0.18-0 MySQL-embedded-4.0.20-0 MySQL-shared-4.0.18-0 MySQL-server-4.0.20-0 MySQL-devel-4.0.20-0 MySQL-shared-compat-4.0.18-0 MySQL-client-4.0.20-0 -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN Messager: [EMAIL PROTECTED] Cell:1.985.320.1191 - Office:1.712.737.2548 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RPM install upgrade error / ver. 4.0.20
Hello, got this error when updating our MySQL: rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm Preparing...### [100%] file /usr/lib/libmysqlclient.so.12.0.0 from install of MySQL-shared-4.0.20-0 conflicts with file from package MySQL-shared-compat-4.0.18-0 file /usr/lib/libmysqlclient_r.so.12.0.0 from install of MySQL-shared-4.0.20-0 conflicts with file from package MySQL-shared-compat-4.0.18-0 MySQL updated to version 4.0.20 with no apparent problems so far, from 4.0.18 but was wondering if this could be a problem down the road or should we remove the old shared or shared-compact RPM's, then do the upgrade to the 4.0.20 RPM's ?? These are the current installed RPM's: MySQL-bench-4.0.18-0 MySQL-embedded-4.0.20-0 MySQL-shared-4.0.18-0 MySQL-server-4.0.20-0 MySQL-devel-4.0.20-0 MySQL-shared-compat-4.0.18-0 MySQL-client-4.0.20-0 TIA, -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions 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]
Change Character Set for one user
Hello, is it possible to change the default character set for one mysql user without affecting the global character set setting, via the the user's .my.cnf option file ?? thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions 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]
Change Character Set
Hello, is it possible to change the default character set for one mysql user without affecting the mysql global character set setting, via the the user's .my.cnf option file ?? thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions 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]
Error: Too many open files
Hello, this maybe a more related issue with DBI API but I thought I try first posting here to see why we would be getting this error (24) - Too many open files Error Message: DBD::mysql::st execute failed: File './mysql/user.MYD' not found (Errcode: 24) What would be causing this type of error too occur Thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions 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]
mySQL error 1016
Hello, Trying to fix this problem mySQL error 1016 I found this at the InnoDB site. Another symptom of an out-of-sync data dictionary is that MySQL prints an error that it cannot open a file yourtablename.InnoDB. ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1) That seems to be my issue. I just cannot seem to get it to go away. So this is what it said to do: - You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and moving the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed. - But it won't DROP with PHPMyAdmin. Is there a manual way to clear this little buggar out of there? TIA MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions 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]
Repair tables
Hello, What is the recommend alternative to REPAIR TABLE for InnoDB tables, this does not work with Innodb table types ?? TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN Messager: [EMAIL PROTECTED] Cell:1.985.320.1191 - Office:1.712.737.2548 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Website
FYI... Just connected to the www.mysql.com site this morning with no problems :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] 1.985.320.1191 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Ian Gibbons wrote: On 21 Apr 2004 at 9:14, Lou Olsten wrote: I have not been able to access the mysql.com server for about a day and a half now from my office. From home, it's fine. There have been rare occasions in the past when our provider had dropped (or very slow) connectivity with certain nodes on the Internet. My understanding there is limited, but I know that there are really only a handful of actual back-bone providers out there for the 'Net, and if a main provider has problems with one of those points, it can take down (or slow) access to vast geographical areas. I believe that's what's going on with our provider at present. However, getting them to troubleshoot it is another matter altogether. It usually starts with Did you restart your modem? and degrades from there. I can get to every other site that I normally visit without problems. Still no MySQL as of 9:14am EST. Hi, No luck with www.mysql.com but http://dev.mysql.com/ works fine. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Long numbers
I think your looking for the ROUND(N,D) function, IE:
select round('3.4285714285714',2) as miles;
3.43 miles
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
1.985.320.1191
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Craig Hoffman wrote:
Hi There:
I have a query where I get an average of weekly miles.
For example:
Average Weekly Miles:
3.4285714285714
How would I get this number into a more read format? Such as 3.42. I
have tried the truncate but I can't seem to get it to work. Does any
one have any suggestions?
SELECT TURNCATE(AVG(distance)), rest of the query
Thanks - CH
__
Craig Hoffman - eClimb Media
v: (847) 644 - 8914
f: (847) 866 - 1946
e: [EMAIL PROTECTED]
w: www.eclimb.net
_
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL upgrade tips
Hello, we are currently running MySQL version 3.23.58, and plan to upgrade to 4.0.18 via the RPM's on a RH/Linux system,... can we retain all the user privileges and databases without loosing any data and our user privileges after the upgrade... we do have backups of all the databases. Is there any major changes we need to consider after upgrading ?? TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transaction Not supported
Thx's for all the responses... are coding was correct, and the database was set
up correct. The problem was the Perl DBI and DBD::mysql modules where way
out-dated and didn't support the 'rollback' and 'commit' yet... problem solved
after updating the modules.
appologizes as this was not related to this mailing list.
--
MikemickaloBlezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Quality Web Hosting
http://www.justlightening.net
MSN: [EMAIL PROTECTED]
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Joshua J. Kugler wrote:
Hmm...but it *should* work. DBI::mysql should implement those calls and
transform them to BEGIN/COMMIT calls. I wonder why it doesn't.
j- k-
On Tuesday 30 March 2004 11:24 pm, Jonas Lindén said something like:
Sorry Mike, Joshua is absolutly correct. Dont listen to my nonses ;)
What I did was trying to use something like this which to my knowledge
doesnt work on MySQL servers.
$dbh-commit();
$dbh-rollback();
/Jonas
- Original Message -
From: Joshua J. Kugler [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, March 31, 2004 9:02 AM
Subject: Re: Transaction Not supported
On Tuesday 30 March 2004 09:35 pm, Jonas Lindén said something like:
I dont think that transactions are supported (yet?) by the PERL DBI.
to Mike Blezien [EMAIL PROTECTED]. My response follows.
Yes they are...at least I have used them in a project before using
Perl::DBI.
[EMAIL PROTECTED] ~]$ perl
use DBI;
print $DBI::VERSION, \n;
1.37
[EMAIL PROTECTED] ~]$
My mysql.pm shows $VERSION = '2.0419'
Are you sure you are connecting to an InnoDB database and that you are
doing
transactions on InnoDB tables?
My code is rather simple:
#Begins the transaction
$dbh-do('BEGIN');
.
.
.
.
$dbh-do('COMMIT');
What does your code look like?
j- k-
--
Joshua J. Kugler
Fairbanks, Alaska
Computer Consultant--Systems Designer
.--- --- ... ..- .--.- ..- --. .-.. . .-.
[EMAIL PROTECTED]
ICQ#:13706295
Every knee shall bow, and every tongue confess, in heaven, on earth, and
under
the earth, that Jesus Christ is LORD -- Count on it!
--
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]
Transaction Not supported
Hello, I keep getting this error when attempting to utilize a database that has Innodb tables, and using transactions. This is from a Perl/DBI script too: Error: Transactions not supported by database at module. bla..bla I realize this maybe a DBI question, but I thought I'd check here first incase we missed something is the settings for the MySQL server Version: 4.0.17-standard - Official MySQL-standard binary and it has innodb enabled, the autocommit is set to 1, then when the transaction is executed, the autocommit is set to 0 untill the commit is called Is there something in the variables I can check to make sure transaction have been enabled ?? TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL RPM Upgrade
Hello, we are currently running MySQL 4.0.15 w/InnoDB tables on a RH/Linux 9.0 and would like to upgrade. the current RPM's installed are: MySQL-client-4.0.15-0 MySQL-embedded-4.0.15-0 MySQL-devel-4.0.15-0 MySQL-shared-compat-4.0.15-0 MySQL-shared-4.0.15-0 MySQL-server-4.0.15-0 MySQL-bench-4.0.15-0 do we need to upgraded ALL these RPM's??... and if so, will we still retain all the current InnoDB data files and databases already setup if we upgrade ?? TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL RPM Upgrade
Thx' Alex. do the old RPM's need to be removed first before upgrading ?? -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Alex Greg wrote: Hello, we are currently running MySQL 4.0.15 w/InnoDB tables on a RH/Linux 9.0 and would like to upgrade. the current RPM's installed are: MySQL-client-4.0.15-0 MySQL-embedded-4.0.15-0 MySQL-devel-4.0.15-0 MySQL-shared-compat-4.0.15-0 MySQL-shared-4.0.15-0 MySQL-server-4.0.15-0 MySQL-bench-4.0.15-0 do we need to upgraded ALL these RPM's?? No - you probably don't need bench, devel, shared or embedded. and if so, will we still retain all the current InnoDB data files and databases already setup if we upgrade ?? Yes. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RPM upgrade
Hello, we are currently running MySQL 4.0.15 w/InnoDB tables on a RH/Linux 9.0 and would like to upgrade. the current RPM's installed are: MySQL-client-4.0.15-0 MySQL-embedded-4.0.15-0 MySQL-devel-4.0.15-0 MySQL-shared-compat-4.0.15-0 MySQL-shared-4.0.15-0 MySQL-server-4.0.15-0 MySQL-bench-4.0.15-0 do we need to upgraded ALL these RPM's??... and if so, will we still retain all the current InnoDB data files and databases already setup if we upgrade ?? TIA -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query syntax help
Hello all, I've been looking at this SQL query a dozen times or more, but keep getting a syntax error message, Query: SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS name,aw.siteid,ai.email,as.username,as.status FROM affiliate_info ai,affiliate_signup as,affiliate_website aw WHERE aw.siteid = 1000 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid what is wrong with this query syntax ?? the syntax error is suppose to be in this area: `affiliate_website aw WHERE aw.siteid = 1000` MySQL version 4.0.15 w/InnoDB tables TIA, -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query syntax help
Thx's Fred... as soon as I sent the email and re-read it again... I spotted the 'as' alias table reference to the table, was actual a reserved word,..causing the error :) thx's again. -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Fred van Engen wrote: On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote: I've been looking at this SQL query a dozen times or more, but keep getting a syntax error message, Query: SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS name,aw.siteid,ai.email,as.username,as.status FROM affiliate_info ai,affiliate_signup as,affiliate_website aw AS is a reserved word. WHERE aw.siteid = 1000 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid what is wrong with this query syntax ?? the syntax error is suppose to be in this area: `affiliate_website aw WHERE aw.siteid = 1000` A bit before that. Regards, Fred. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Removing default MySQL install
Try using this command instead: rpm -e --nodeps (the RPM you want to remove) Eve Atley wrote: I've been trying to install a later version of MySQL (4.0.16). The Redhat default install is 3.x, and using rpm -e fails to remove the default packages due to 'dependencies'. How can I safely and successfully uninstall the default mysql packages (server and client) so I can start fresh? Thanks, Eve -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RPM upgrades
Hello, we are in the process of upgrading our machine and currently running the following RPM's MySQL-server-4.0.15-0 MySQL-bench-4.0.15-0 MySQL-embedded-4.0.15-0 MySQL-devel-4.0.15-0 MySQL-client-4.0.15-0 MySQL-shared-compat-4.0.15-0 MySQL-shared-4.0.15-0 this is on a RH/Linux 7.3(i686) do we need to update ALL these RPM's if we want to go with the MAX version ?? TIA ;) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
