MySQL Server uptime.
I know this isn't really on topic but I thought some people may find this interesting. The below stats were taken from a non-production server which I dump to from my production server as a kind of backup method using MysqlFront. I might add that today I did several multi-table joins on the world database (+7mil population, languages, surface area of largest city per continent etc...) and still received decent result set times in the order of 0.06 to 0.5 seconds. The server is a lightning fast Pentium Pro 90 with 32 MB EDO RAM and RedHat 7.0. So anyone who still says that the MySQL database can't cut the mustard, obviously hasn't tried. Mysql 3.23.43 Up 212 days, Processed 199,654 queries (average = 0.01/sec) Processed 2 slow queries (woops ;-) don't you hate leaving out the where statement on joins) Neil Silvester Webmaster Heat and Control Ph: +61 7 3877 6429 [EMAIL PROTECTED] http://www.heatandcontrol.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Support contracts question
Just a trivial question. How does dual locations work? I am based in our Brisbane Australia office and am using MySQL as a backend for a company Intranet. Our parent company is based in Hayward, California and has recently converted an old DOS based database over to MySQL. We are both in the process of purchasing support contracts, but should we get one each? Or can we share even though there are 2 separate installations doing separate things on different platforms? Don't get me wrong, the cost of support doesn't bother me, I mean for what you get it is well worth the price, but this is just an issue of logistics (and accounting freaking out for duplicating items). Hope this question makes sense. Neil Silvester Webmaster Heat and Control Ph: +61 7 3877 6429 [EMAIL PROTECTED] http://www.heatandcontrol.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL installed but no MySQLAdmin???
You also need to download and install the MySQL client programs rpm from the mysql.com website too. This will install mysql and mysqladmin client progs. Neil -Original Message- From: Vernon A Webb [mailto:[EMAIL PROTECTED]] Sent: Thursday, 14 February 2002 1:01 PM To: MySQL Subject: MySQL installed but no MySQLAdmin??? Why, if I have MySQL is the MySQLAdmin not on the box? Is there something else I must install? I've downloaded the rpm from the web site and installed. When it boots, MySQL does come up and it says that it has started without errors. What am I missing? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Missing values in an INT type column
I hope this doesn't sound like a stupid question. Is there anyway of finding missing values in an INT column type? Say I have a telephone extension guide. Using the MIN() and MAX() functions I am able to find the start and end ranges. But what about finding the extensions that are not allocated. At the moment I am thinking I will need to use an external script that will query the individual entries in the array of my returned result (SELECT extensions FROM table ORDER BY extensions). If the difference between consecutive rows row[x] and row[y] is +1 (or negative depending on ascending or descending ordering), then all is good. But if the result is +x, I will need to echo an array to the screen of row[x] ++1 while row[x] row[y]. It will take some work, but shouldn't be too hard to implement. I guess my question is, is there a function in MySQL that will do this, or at least help me along with it, or has anyone come across a need for this before. Neil Silvester Webmaster Heat and Control Ph: +61 7 3877 6429 [EMAIL PROTECTED] http://www.heatandcontrol.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Really good idea on Performance Tuning???
That is possible too: - MySQL Training (http://www.mysql.com/training/) - MySQL Support (http://www.mysql.com/support/) - MySQL Consulting (http://www.mysql.com/consulting/) However, it will never be a substitute for manual tuning. Good database performance more than simply the result of a calculation. It involves topics like: understanding how the MySQL Server processes a query, how the optimiser works, how/when indexes are used, how queries and tables should be designed for optimal performance. You can only get those things through: - A lot of practical experience with (My)SQL (long term). - Training (fast, gain the knowledge through investing in education). I will have to agree on this point right here. I recently completed a MySQL training course and not only have I got my server optimised to get the most out of the hardware, but have also optimised all my large tables with correct index usage, and large queries so they are running up to 200% faster than previously. - Consulting (fast, personalised, gain (some) knowledge through investing). - Support (Have issues resolved quickly by paying someone else to do it, not necessarily gaining the knowledge for the future). Generally, when talking about optimising MySQL servers and applications, I'd go for training as a first choice: best return on investment for that particular case. Absolutely. See above. Neil Silvester Webmaster Heat and Control Pty Ltd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with MySQL DATE_FORMAT or is it a PEBKAC??
I appear to be having an error with the following update that I am submitting to my database. I am using mysql's DATE_FORMAT function to pull the date out and insert in a user friendly form. Upon submission of the modified data, I use the below SQL to update the table information. Everything is running fine when I leave out the $ShowStart and $ShowEnd out of the equation, but when I add it back in, instead of updating the existing entry in the database, it adds in a new entry. $ShowStart = ($StartYear-$StartMonth-$StartDay); $ShowEnd = ($EndYear-$EndMonth-$EndDay); $sql = mysql_query (UPDATE tradeshows SET showname = \$ShowName\, city = \$ShowCity\, country = \$ShowCountry\, startdate = \$ShowStart\, enddate = \$ShowEnd\, booth = \$BoothNumber\, boothsize = \$BoothSize\, ondisplay = \$OnDisplay\, attendees = \$Attendees\ WHERE ID = \$ShowID\ ); The $ShowID variable is definitely correct, as I decided to echo it back to the browser so I could double check my PHP. I know that it is much better to use the UNIX time stamp to reformat dates, but I still do not understand what I could be doing wrong. has anyone come across this before? The MySQL version I am using is 3.23.39 (hmm. maybe I should upgrade this to start with). Cheers. Neil Silvester - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT DISTINCT BINARY crashes mysql on null values
I tried this on an existing table using a char(50) column with 956 entries,
of which I have 1 valid entry and 954 NULL values. I have 2 records returned
1 = valid entry, 1 = NULL and mysql did not crash.
Is it only when you are using temporary tables? Or have you tried this on
multiple tables to get the same effect?
Neil Silvester
Webmaster
Heat and Control Pty Ltd
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 30 January 2002 7:45 AM
To: [EMAIL PROTECTED]
Subject: SELECT DISTINCT BINARY crashes mysql on null values
Description:
Executing a SELECT DISTINCT statement in conjunction
with the BINARY cast operator will crash MySQL server
when the column being cast contains one or more null
values.
How-To-Repeat:
Executing the following code from a mysql prompt will
cause the MySQL server to crash and restart.
create temporary table wassup (
rowid int not null AUTO_INCREMENT PRIMARY KEY,
col1 varchar(10) null
)
;
insert into wassup
(col1)
values
('aaa'),
('AAA'),
(NULL)
;
select distinct binary col1 from wassup
;
Fix:
Work-arounds: Defining the column as binary during the create
table statement alleviates the need for the binary cast during
selects, and thereby eliminates the crashes. Alternatively,
adding where col1 is not null to the select statement
will also work.
Submitter-Id: submitter ID
Originator:Steve Severance
Organization:
MySQL support: none
Synopsis: SELECT DISTINCT BINARY crashes mysql on null values
Severity: non-critical
Priority: low
Category: mysql
Class: sw-bug
Release: mysql-3.23.47 (Official MySQL RPM)
Environment:
System: Linux vexweb1 2.4.2-2smp #1 SMP Sun Apr 8 20:21:34 EDT 2001 i686
unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-81)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 Jan 4 16:56 /lib/libc.so.6 -
libc-2.2.2.so
-rwxr-xr-x1 root root 1236396 Apr 6 2001 /lib/libc-2.2.2.so
-rw-r--r--1 root root 26350254 Apr 6 2001 /usr/lib/libc.a
-rw-r--r--1 root root 178 Apr 6 2001 /usr/lib/libc.so
Configure command: ./configure --disable-shared
--with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static
--without-berkeley-db --without-innodb --enable-assembler
--with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock
--prefix=/ --with-extra-charsets=complex --exec-prefix=/usr
--libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share
--localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include
--mandir=/usr/man '--with-comment=Official MySQL RPM'
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Suppress WARNING
Or you can just add an @ in front of the function you are calling in your script which tells PHP to suppress all error warnings, which allows you script to continue running. Neil Silvester -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 30 January 2002 3:52 PM To: Dan Nelson; Tshering Norbu Cc: [EMAIL PROTECTED] Subject: Re: Suppress WARNING At 11:40 PM 1/29/2002 -0600, Dan Nelson wrote: In the last episode (Jan 30), Tshering Norbu said: Hi list, How do you suppress the WARNING message like the one here: Warning: stat failed for photos/p73n1.jpg (errno=2 - No such file or directory) in /ad/details.php on line 12 Everything works fine even with that error. I wanted to suppress this error and not display at all. This is a PHP question having nothing to do with mysql. Try a PHP mailing list. Good advice. However, since I know the answer, I hope it's ok to respond. edit php.ini. There's a variable in there I don't remember the exact name off-hand, but it's pretty obvious. The file is very well commented and will give you the details of what you should set the value to for various options. -Ed - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Text formatting for HTML output.
Currently I am using a standard TEXTAREA/TEXTAREA field for information input to the MySQL database. When I display the allready information in the text fields to allow the user to manipulte the information, the formatting includes any carrige returns that have been entered. However when I display the text using the mysql_fetch_array the returns are no more, and instead are only displayed as a single space between the next word. I know this is due to the way that HTML reads whitespace, but how can I force the formatting of the text so it is displayed how the user intended. I have tried to use ADDSLASHES() to reformatt the variables befofe entering them into the tables, but I am unsure if this is the right method. And if so, how can I convert the \n to br? I am not sure if this is a question that I should be asking to this maillist or not, but I thought I would give it a shot. MySQL = 3.23 Language= PHP Any help would be greated with open arms. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Alphabetical AUTO_INCREMENT?
How do I convert (or create from scratch) a table who's AUTO_INCREMENT column would be a, b, c, d, e and so forth up to z? At the moment I am using an INT column 1, 2, 3, 4, etc, but I would really be able to make good use in my queries if this column was alphabetical instead. Thanks. Neil Silvester P.S. database, mysql _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select statement problem
As I am still a newbie to mysql I am sure that some of the problems that I am coming accross are created by myself, and others are probably so simple that they are staring me in the face. I do, however, seem to be having some problems with the below statement. I would like the result of the query to only display one entry per company no matter how many products they have. $result = mysql_query(SELECT competitor.ID, Competitor.CompetitorName FROM competitor, competitorproducts, productcategory WHERE competitorproducts.CompID=competitor.ID and competitorproducts.CatID=productcategory.ID and productcategory.ID like \$cat%\ and competitor.CompetitorName like \$CompName%\ and competitorproducts.ProductName like \$CompProd%\ order by CompetitorName ); I am using PHP as a front end. TIA. Neil Silvester P.S. database, mysql _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHP variable conversion problem.
Another PHP problem has kept me up all night, scouring through my database books and trying everything I could think of. I am still new to the MySQL and PHP field, but never the lass I will not give up. $cat is a variable that is passed to the query from the previous page. The SELECT statement works faultlessly, except for the point at which no choice is made from the drop down on the page prior to this one. I have set a value of x to the drop down for a non selection, but am having problems converting that x value to a % value withing my SELECT statement. I am assuming that I will need an IF THEN before the SELECT, but I have tried several variation to no avail. $result = mysql_query(SELECT CompetitorName FROM competitor, competitorproducts, productcategory WHERE competitorproducts.CompID=competitor.ID AND productcategory.ID=\$cat\ AND competitorproducts.CatID=productcategory.ID ); Any help would be mostly appreciated. Thanks. Neil Silvester Webmaster / Systems Administrator Heat and Control Inc. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHP variable conversion problem.
Another PHP problem has kept me up all night, scouring through my database books and trying everything I could think of. I am still new to the MySQL and PHP field, but never the lass I will not give up. $cat is a variable that is passed to the query from the previous page. The SELECT statement works faultlessly, except for the point at which no choice is made from the drop down on the page prior to this one. I have set a value of x to the drop down for a non selection, but am having problems converting that x value to a % value withing my SELECT statement. I am assuming that I will need an IF THEN before the SELECT, but I have tried several variation to no avail. $result = mysql_query(SELECT CompetitorName FROM competitor, competitorproducts, productcategory WHERE competitorproducts.CompID=competitor.ID AND productcategory.ID=\$cat\ AND competitorproducts.CatID=productcategory.ID ); Any help would be mostly appreciated. Thanks. Neil Silvester Webmaster / Systems Administrator Heat and Control Inc. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHP variable conversion problem.
Another PHP problem has kept me up all night, scouring through my database books and trying everything I could think of. I am still new to the MySQL and PHP field, but never the lass I will not give up. $cat is a variable that is passed to the query from the previous page. The SELECT statement works faultlessly, except for the point at which no choice is made from the drop down on the page prior to this one. I have set a value of x to the drop down for a non selection, but am having problems converting that x value to a % value withing my SELECT statement. I am assuming that I will need an IF THEN before the SELECT, but I have tried several variation to no avail. $result = mysql_query(SELECT CompetitorName FROM competitor, competitorproducts, productcategory WHERE competitorproducts.CompID=competitor.ID AND productcategory.ID=\$cat\ AND competitorproducts.CatID=productcategory.ID ); Any help would be mostly appreciated. Thanks. Neil Silvester Webmaster / Systems Administrator Heat and Control Inc. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHP and Mysql problem
I am having a little problem with a PHP 4 statement in one of my webpages. I
am using a drop down box of selections that is built a table in a MySQL
database.
This is what I have so far (everything works well, the correct information
is displayed for both the Category and ID column values.
select name=Product
optionPlease select a category.../option
?php
// display entries in the selection box and
// associate the column ID of each entry
while ($row = mysql_fetch_array($result)) {
echo(option value= . $row[ID] . . $row[Category] . /option);
}
?
/select
Unfortunately this causes the HTML code to b
OPTION value=1Product Category 1/OPTION
when I need it to be
OPTION value=1Product Category 1/OPTION
Can anyone recommend another approach so that the quotation marks are
generated around the ID value that is returned?
TIA
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mzximum character count
Hello, I am working on a MySQL database that lists the current competitors of the company I work for as a part of a global intranet. At present I have a table that contains the corporate website of our competitors. I would like to find out what record in the table has the largest amount of characters. This way I could possibly decrease the varchar size (currently 100) to speed up the queries. My table is as below. CREATE TABLE competitor ( CompetitorsName varchar(35) not null, Website varchar(100), ID int auto_increment not null primary key ) TIA. Neil Silvester Systems Administrator / Webmaster. Heat and Control Inc. _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
