Re: Birthday strategy
Pooly wrote: Hi, I would like to display a list of members who have their birthday a given day (today for instance). For such an application I've used a single integer column to store a number consisting of the month and day (day as 2 digits!!!) concatenated. So dates range from 101 to 1231. The range isn't continuous, but at least the dates are ordered correctly. In this situation you can also easily query ranges (who's celebrating their birthday in the next week/month/etc.). The only caveat is when the start and end of the range is in two different years; then you'll have to split the range up and use a range for each year. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB admin with limited access
Salutations! I am relatively new to the MySQL flavor of database server and now have run into a situation on my hosting that I don't seem to be able to resolve. I installed a new query using TEMPORARY tables via the PHP 4 interface on a production database and suddenly began receiving PHP timeouts. From this point on, access to the database has been severely restricted. I have run a few diagnostics and ascertained the following: - there are a growing number of processes - queries - displayed by mysql_list_processes as accessing my database - phpMyAdmin displays a process that can't be killed. It appears to be the process to execute the SQL using TEMPORARY tables. - only one table appears to be blocked. SELECT COUNT(*) on all other tables works fine. This is a central table, however it was not involved in the TEMPORARY table statement. When I attempt SELECT COUNT(*) on the problem table, the statement blocks for a period (PHP timeout?) and then dies with no apparent error status or message. - The support people at my hosting claim that I still have 1.4 GB space (my complete hosting allocation) for TEMPORARY tables. I have tried to get my hosting - hostm.com - to assist, but besides suggesting RTFM and the repair function, they have not been particularly helpful. I'm sure all it needs is for the server to be restarted, but I do understand that it is not always possible to do such in a shared environment. So 1) How does a user with no shell access kill this process? 2) Is a repair really the right thing to do in this situation? 3) With the constraint of PHP 4.3.11 and MySQL 4.0.25-standard, how do I use TEMPORARY tables and not get myself into this predicament? Thanks much for any tips. James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday strategy
Hi! Thanks for all your answers. I'll go for a flexible solution, with one column for day, one for month and if i want to compute the age, a year column. This will also give more flexibility if someone does not want to give his birthdate with precision (He would be able to only give the month, and if he give the year I'll compute his age). Thanks! 2005/8/25, Jigal van Hemert [EMAIL PROTECTED]: Pooly wrote: Hi, I would like to display a list of members who have their birthday a given day (today for instance). For such an application I've used a single integer column to store a number consisting of the month and day (day as 2 digits!!!) concatenated. So dates range from 101 to 1231. The range isn't continuous, but at least the dates are ordered correctly. In this situation you can also easily query ranges (who's celebrating their birthday in the next week/month/etc.). The only caveat is when the start and end of the range is in two different years; then you'll have to split the range up and use a range for each year. Regards, Jigal. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
Hal Vaughan [EMAIL PROTECTED] wrote on 24/08/2005 17:41:36: # Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? I don't think so. But may I inquire why you do not want to have a key? What you are saying is How can I do a job without using the tool designed for the job?. If there is no key, in order to do what you want, MySQL would have to do a linear search through the table in order to check for duplicates - the kind of lengthy operation it is designed to avoid whenever possible. The key is a necessary part of the effect you want to achieve. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change table encoding to UTF-8
On Wednesday 24 August 2005 20:01, MightyData wrote: What is the proper procedure to change the table (or database encoding) from latin1 to UTF-8 with MySQL 4.1.x? My thought is to export the data to text file, drop the table, recreate the table with the proper encoding and then import the data. Is there a better way or something I missed? ALTER TABLE TABLE CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' that's how we did it. - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.com FileMaker 7 Certified Developer FileMaker Authorized Trainer Check out our FileMaker 7 training classes! - Kind regards, Andreas Streichardt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY distance from a point
Scott Gifford wrote: Hello, I'd like to sort my query results based on their distance from a given point. The actual data I have will be in (longitude,latitude) format, but I can convert to something else if that will work better. For example, I may have data like this Item Latitude Longitude - Scott's House 37.4419 -122.1419 Tom's House37.4519 -122.2419 Mary's House 37.4619 -122.3419 Sally's House 37.4719 -122.4419 and I'd like to see these rows sorted by distance from (38,-121). So, some one else is doing GIS appliations, huh... Like I was saying earlier, unfortunately, even if you did have the distance function, it would not work, because the results are calculated on a planar surface. The SRIDs in the GIS functions are there, but pretty much useless. And, here you go: SELECT( DEGREES( ACOS( SIN(RADIANS( latitue1 )) * SIN(RADIANS( latitue2 )) + COS(RADIANS( latitue1 )) * COS(RADIANS( latitue2 )) * COS(RADIANS( longitude1 - longitude2 )) ) * 60 * 1.1515 ) ) AS distance Just sort that by distance. -- http://www.douglassdavis.com
Re: Change table encoding to UTF-8
Hello. If you're upgrading from 4.0 read: http://dev.mysql.com/doc/mysql/en/charset-conversion.html If your tables were created in 4.1 you may use ALTER TABLE and ALTER DATABASE to change the table (or database encoding). See: http://dev.mysql.com/doc/mysql/en/alter-table.html http://dev.mysql.com/doc/mysql/en/alter-database.html MightyData [EMAIL PROTECTED] wrote: What is the proper procedure to change the table (or database encoding) from latin1 to UTF-8 with MySQL 4.1.x? My thought is to export the data to text file, drop the table, recreate the table with the proper encoding and then import the data. Is there a better way or something I missed? - Kirk Bowman Phone: 972-390-8600 MightyData, LLC http://www.mightydata.com FileMaker 7 Certified Developer FileMaker Authorized Trainer Check out our FileMaker 7 training classes! - -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user defined exceptions
Hello. For a pity I'm not a JDBC expert. Hi, Thanks for that bit of info. This may be a stupid question, but how exactly does setting the variable @err help me with my current situation? Will this allow me to generate a JDBC Exception or is it something I'll need to change in the code to check if an error code has been set? Thanks again! Leigh -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
Hello. What is the value of the default_charset variable in your php.ini file? What version of MySQL do you use? Florian Burkart [EMAIL PROTECTED] wrote: What might help as well is another problem I have: Somehow, the data I am getting out of mysql and php and is being served by = apache is still in iso format, and not utf8. Which leads to bad displaying = (unless i switch back to iso in the browser, but then the html stuff in utf= 8 gets strange (which is served correctly in utf8).=20 Is that related? If not, still someone with hints on it? On Wed, 24 Aug 2005 20:55:21 +0300 Gleb Paharenko [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday strategy
Hello. You're right. I haven't understood the problem properly. Terence [EMAIL PROTECTED] wrote: Wouldnt that only work for the current year? For example I was born on 1970-08-25, select id from members where birthday = now(); wouldnt return my birthday if it was today. Or am I missing something new in 5.0.11? Gleb Paharenko wrote: Hello. Why don't you want to use just: select id from members where birthday = now(); See: mysql show create table members\G; *** 1. row *** Table: members Create Table: CREATE TABLE `members` ( `id` int(11) NOT NULL auto_increment, `birthdate` date default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql select * from members; +++ | id | birthdate | +++ | 1 | 2004-02-29 | | 2 | 2005-02-28 | | 3 | 2005-08-24 | +++ mysql select id from members where birthdate = now(); ++ | id | ++ | 3 | ++ mysql select now(); +-+ | now() | +-+ | 2005-08-24 20:27:20 | +-+ 1 row in set (0.00 sec) mysql select version(); +---+ | version() | +---+ | 5.0.11-beta-debug-log | +---+ 1 row in set (0.00 sec) If you add an index on birthdate, you can avoid table scans. create index `birthdate` on members(birthdate); mysql explain select id from members where birthdate = now()\G; *** 1. row *** id: 1 select_type: SIMPLE table: members type: ref possible_keys: birthdate key: birthdate key_len: 4 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec) Pooly [EMAIL PROTECTED] wrote: Hi, I would like to display a list of members who have their birthday a given day (today for instance). My idea is to store their birth date in a column, and then query the table against the column. But the query would be like : select id from members where MONTH(birthday) =3D MONTH(NOW()) AND DAY(birthday)=3DDAY(NOW()) but it would perform a entire table scan with that. What would be your best strategy for that sort of query ? And how would you deal with 29th of february ? --=20 Pooly Webzine Rock : http://www.w-fenec.org/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Older tables caught between ISAM and MyISAM
Hello. Have you tried mysql_convert_table_format script? Spencer Yost [EMAIL PROTECTED] wrote: I just upgraded a server to MySQL 4.1.4. I was at 4.0.8 - everything worked perfect at 4.0.8 However, since the upgrade, a few dozen tables in a few older databases are apparently still in ISAM format. The tables now misbehave when trying to access them(marked in use, error out, trash the server, you name it). This is especially troubling because one of the tables is mysql.user so none of my customer's programs work )-; downgrading back to 4.0.8 for other reasons unfortunately so I have to slog through this. What is infuriating is isamchk reports that each table is in fine shape(with an accurate record count), but no other utilities, like myisamchk, mysqldump, will touch the table . Likewise, none of the SQL statements like REPAIR table (with or without use_frm) or ALTER table work. All of the utilities statements, without hardly an exception, complain that the MYI file is missing. It is missing and never existed for these tables as I have backups going back years and they are on none of them. My guess is these table files appear on the surface to these utilities to be MyISAM(header flag or something) file, but are really ISAM files. Any help if figuring out how to get the data out of the files and into newer tables would be APPRECIATED Spencer Yost Sundance Consulting 336.287.8017 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UK Bank Holidays
Hi, I need to work out number of business days worked by staff in our company i.e. Available days = Days in year - (Saturdays + Sundays + Bank Holidays) (Available Days - Time Off) = Capacity Is MySQL aware of UK Bank Holidays or do I have to create a separate table and keep it updated with Bank Holiday dates? Thanks for your help. Shaun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create serial number by select
Hello All! I have a little problem. I can't do serial number in result of select. Example: TABLE1 value -- res1 res2 res3 SELECT (??), value FROM table1 ... 1res1 2res2 3 ... . . I can't build serial number in table1! Thans! Best Regards! Zoli
Re: create serial number by select
Gyurasits Zoltán wrote: Hello All! I have a little problem. I can't do serial number in result of select. I guess you want to display a sequential number for each row in the result. First of all a warning: if there is no ORDER BY in the query there is no specific order in which the results will be returned. Often it seems as if the records are returned in the order in which they were once inserted, but this is by far not always the case. A solution that might work for you is: SET @count:=0; SELECT @count:[EMAIL PROTECTED] AS `count`, `value` FROM `table`; count value - 1 res1 2 res2 3 res5 4 res18 Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Truly random 'groupie'
Hi, I think I saw this question being asked before, but I can't remember if their was a simple answer. If I have a table of two columns (PK and FK) with a one to many relationship, i.e. PK FK 1 a 2 a 3 a 4 b 5 b 6 b 7 c 8 c 9 d Can I easily select a truly random value of PK for each distinct value of FK? I use the words 'truly random' as I think the term 'pseudo random' applies to the following syntax... SELECT PK,FK FROM above_table GROUP BY FK; Of course I can do... SELECT MIN(PK),FK FROM above_table GROUP BY FK; SELECT MAX(PK),FK FROM above_table GROUP BY FK; But what I would really love to do is something like... SELECT RANDOM(PK),FK FROM above_table GROUP BY FK; Any general (and simple) SQL solution to this problem? Any hacker out there who can put together a UDF in 10 minutes flat? The problem is slightly more complex than stated, because PK is actually a multi-part primary key. In my fevered imagination I picture syntax that looks like this... SELECT RANDOM(ROW(PK_P1,PK_P2)),FK FROM above_table GROUP BY FK; but that could be a garbage suggestion. Any pointers to previous answers to similar questions would be great. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MysqlI
Hi Did someone use MySQLI in production environment ? Is the transactions aspect reliable ? (bugs etc) Thanks. Sorry I should have specified that it's in the PHP environment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL
news [EMAIL PROTECTED] wrote on 08/25/2005 08:14:53 AM: Hi Did someone use MySQLI in production environment ? Is the transactions aspect reliable ? (bugs etc) Thanks. Sorry I should have specified that it's in the PHP environment. The short answer is Yes. Do you want a list of companies from the Fortune 100 or are you interested in a particular business area? There are some huge names (http://www.mysql.com/customers/) that use MySQL for critical, public-facing, and infrastructure applications. Many of them use PHP as their front-end. Did you have a specific question or were you just curious of the viability of PHP+MySQL as an application platform? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Truly random 'groupie'
Dan Bolser [EMAIL PROTECTED] wrote on 08/25/2005 08:03:23 AM: Hi, I think I saw this question being asked before, but I can't remember if their was a simple answer. If I have a table of two columns (PK and FK) with a one to many relationship, i.e. PK FK 1 a 2 a 3 a 4 b 5 b 6 b 7 c 8 c 9 d Can I easily select a truly random value of PK for each distinct value of FK? I use the words 'truly random' as I think the term 'pseudo random' applies to the following syntax... SELECT PK,FK FROM above_table GROUP BY FK; Of course I can do... SELECT MIN(PK),FK FROM above_table GROUP BY FK; SELECT MAX(PK),FK FROM above_table GROUP BY FK; But what I would really love to do is something like... SELECT RANDOM(PK),FK FROM above_table GROUP BY FK; Any general (and simple) SQL solution to this problem? Any hacker out there who can put together a UDF in 10 minutes flat? The problem is slightly more complex than stated, because PK is actually a multi-part primary key. In my fevered imagination I picture syntax that looks like this... SELECT RANDOM(ROW(PK_P1,PK_P2)),FK FROM above_table GROUP BY FK; but that could be a garbage suggestion. Any pointers to previous answers to similar questions would be great. All the best, Dan. I can think of a way to do it in three steps. The last time I saw this type of question, the poster was looking for ways of random sampling within a population.If you application is similar, this approach will probably meet your needs. If this is meant to produce random output (as in a constantly shifting web page) it may not scale well for you. Create a temporary table containing your PK fields and your FK field(s). When you populate that table create an extra column of random numbers. Create another temporary table to hold your PK and FK values but this time apply a UNIQUE index on the FK field. Do an INSERT IGNORE from your first temp table into this second ORDERed BY the random value. That should give you a pseudo-random list of PK values, one per each FK with each FK appearing only once in the list. The last step is to JOIN this final temp table with your FK source and your original data to produce the random list of matching items. As I cautioned earlier, this probably will not scale well for frequent use. I am sure that there are other ways to achieve the same effect but I haven't had my coffee yet today and that's as good as I got... ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: user defined exceptions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Leigh wrote: Gleb Paharenko wrote: Hello. There was a question about exceptions recently. The answer might be helpful: http://lists.mysql.com/mysql/188048 Leigh [EMAIL PROTECTED] wrote: Hi All, I'm currently working on porting a database form Informix 9.2 to MySQL 5 in order to determine the suitability of MySQL for our application. At the moment, I'm stuck with one of our stored procs that contains the following lines: -- If duplicates are detected, raise an error IF count 1 THEN RAISE EXCEPTION -746, 0, 'Duplicate mapping: code ' || p_code || ', cust ' || p_cust_code || ''; END IF; Under Informix, this IF statement raises a user defined JDBC Exception (the code -746 is for user defined errors). Is there any way of doing a similar thing in MySQL? If you need any more information, I'm happy to provide it. Thanks in advance! Regards, Leigh Hi, Thanks for that bit of info. This may be a stupid question, but how exactly does setting the variable @err help me with my current situation? Will this allow me to generate a JDBC Exception or is it something I'll need to change in the code to check if an error code has been set? Thanks again! Leigh Leigh, It looks like the other poster's example just ends up setting the error variable, _not_ creating a custom error. MySQL does not yet have a RAISERROR implementation, so to create an exception, you usually have to do something that is erroneous, but that has no side effects and then parsing the error message from the resultant SQLException (like dropping a non-existent table): DROP TABLE error.`error #` (note, this example is from http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf which is worth a read if you're starting out with MySQL stored procedures). -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDDcKltvXNTca6JD8RArB+AJoDQSYUlF5yCyz+1sc4X1YVudXQxACgi7Et 6bEs9HNLBRPzj8MgJu4dpfE= =HSwc -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'
Hey Gleb, thanks for taking your time! php.ini: ; As of 4.0b4, PHP always outputs a character encoding by default in ; the Content-type: header. To disable sending of the charset, simply ; set it to be empty. ; ; PHP's built-in default is text/html default_mimetype = text/html default_charset = utf-8 mysql version: mysql Ver 14.7 Distrib 4.1.13, for pc-linux-gnu (i686) using readline 5.0 php version: PHP 4.4.0 (cli) (built: Aug 23 2005 14:55:11) Copyright (c) 1997-2004 The PHP Group Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies apache2: Server version: Apache/2.0.54 Server built: Aug 24 2005 13:41:09 Server's Module Magic Number: 20020903:9 Architecture: 32-bit Server compiled with -D APACHE_MPM_DIR=server/mpm/prefork -D APR_HAS_SENDFILE -D APR_HAS_MMAP -D APR_HAVE_IPV6 (IPv4-mapped addresses enabled) -D APR_USE_SYSVSEM_SERIALIZE -D APR_USE_PTHREAD_SERIALIZE -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT -D APR_HAS_OTHER_CHILD -D AP_HAVE_RELIABLE_PIPED_LOGS -D HTTPD_ROOT=/usr -D SUEXEC_BIN=/usr/sbin/suexec2 -D DEFAULT_PIDLOG=/var/run/httpd.pid -D DEFAULT_SCOREBOARD=logs/apache_runtime_status -D DEFAULT_LOCKFILE=/var/run/accept.lock -D DEFAULT_ERRORLOG=logs/error_log -D AP_TYPES_CONFIG_FILE=/etc/apache2/mime.types -D SERVER_CONFIG_FILE=/etc/apache2/httpd.conf The text in the browser coming from PHP is as well as the plain HTML utf-8 encoded, it is only the mysql-queries in php which return iso - besides the original problem that the query sends strings as iso... :/ Thanks again, Florian On Thu, 25 Aug 2005 10:58:07 +0300 Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. What is the value of the default_charset variable in your php.ini file? What version of MySQL do you use? Florian Burkart [EMAIL PROTECTED] wrote: What might help as well is another problem I have: Somehow, the data I am getting out of mysql and php and is being served by = apache is still in iso format, and not utf8. Which leads to bad displaying = (unless i switch back to iso in the browser, but then the html stuff in utf= 8 gets strange (which is served correctly in utf8).=20 Is that related? If not, still someone with hints on it? On Wed, 24 Aug 2005 20:55:21 +0300 Gleb Paharenko [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Zur Zeit befinde ich mich hinter einer Firewall welche das versenden von E-Mails über die Adresse [EMAIL PROTECTED] verhindert. Ihr erhaltet Antworten auf E-Mails an [EMAIL PROTECTED] daher ebenfalls von [EMAIL PROTECTED] Es macht keinen Unterschied welche der beiden Adressen ihr anschreibt. Meine alte Handynummer +49-151-11616247 ist gekündigt. Hier in Guadalajara, Mexico, erreicht ihr mich unter der +52-3310650934. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MinGW and MySQL
Hi, MinGW is the GCC compiler for Win32 : http://www.mingw.org/ I get the mysql API working using the comments on this bug : http://bugs.mysql.com/bug.php?id=8059 2005/8/23, Michael Monashev [EMAIL PROTECTED]: Hello P I'm using QT4.0 which works with MinGW. What is the MinGW ? Database server? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UK Bank Holidays
Hi, I guess you have to maintain a table of bank holidays separately, that's the common setup. I don't know any application that can do it (even Excel don't do IIRC). Moreover, bank holidays can depend on your bussiness... 2005/8/25, Shaun [EMAIL PROTECTED]: Hi, I need to work out number of business days worked by staff in our company i.e. Available days = Days in year - (Saturdays + Sundays + Bank Holidays) (Available Days - Time Off) = Capacity Is MySQL aware of UK Bank Holidays or do I have to create a separate table and keep it updated with Bank Holiday dates? Thanks for your help. Shaun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MysqlI
Bill wrote: Hi Did someone use MySQLI in production environment ? Is the transactions aspect reliable ? (bugs etc) Thanks. Sorry I should have specified that it's in the PHP environment. mySQLi is a part of PHP, so you might be better asking this question over there, but mySQLi is a part of PHP5.0 which was released in July 2004, but was also part of the pre-release versions of PHP5 and PHP5.1rc1 was recently released. So I'm guessing it'd be reasonably safe to assume that its been fairly well tested over the last 12 months +. But anyway, you can find more information about MySQLi here : http://uk.php.net/manual/en/ref.mysqli.php Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UK Bank Holidays
Shaun, This is a great opportunity for you to be creative and... I guess you have to maintain a table of bank holidays separately, ...add some of your own in. Brill! Raz ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UK Bank Holidays
Shaun Is MySQL aware of UK Bank Holidays Nope. or do I have to create a separate table and keep it updated with Bank Holiday dates? Yep. PB - Shaun wrote: Hi, I need to work out number of business days worked by staff in our company i.e. Available days = Days in year - (Saturdays + Sundays + Bank Holidays) (Available Days - Time Off) = Capacity Is MySQL aware of UK Bank Holidays or do I have to create a separate table and keep it updated with Bank Holiday dates? Thanks for your help. Shaun -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create serial number by select
Hi, Why not adding an auto_increment column to your data ? 2005/8/25, Gyurasits Zoltán [EMAIL PROTECTED]: Hello All! I have a little problem. I can't do serial number in result of select. Example: TABLE1 value -- res1 res2 res3 SELECT (??), value FROM table1 ... 1res1 2res2 3 ... . . I can't build serial number in table1! Thans! Best Regards! Zoli -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RAID/MySQL configuration question
G'morning all! (Using Red Hat Linux Enterprise 4.1) I have a Dell PowerEdge 2800 with a PERC 4 RAID controller. The RAID controller has one RAID 1 mirror and one RAID 5 stripe volume created. We installed most of the OS stuff on the RAID 1 set and the /usr/local and /var on the RAID 5 set (since the Red Hat MySQL rpm puts the data directory under /var ). I'd like to know if there are any better ways to configure this (I can repartition and reinstall the OS, if necessary). background I'm having problems with a Tomcat application (OSP - ePortfolios) that uses a lot of disk space for uploaded files (under Tomcat directory which I install under /usr/local). Not sure how large the MySQL database will grow to be. I installed the Red Hat MySQL rpm, but not sure if it is RAID-aware and considering compiling MySQL from source ( --with raid ?). Or my problem may be with the MySQL Connector/J driver (which would be a question for the mysql-java list). The application builds and installs fine with no errors, but Tomcat only works for the static directories (i.e. /jsp-examples ) and not with the application that interacts with MySQL. I've installed this application successfully on an identical non-raid system. The only differenced between the two machines is that the problem child is RAID (configured as above) and the java sdk version changed from _08 to _09. /background 1) Best way to configure the RAID/partitions for best MySQL performance? 2) Is MySQL RAID-aware if not compiled: - - with raid? (unsure if the Red Hat rpm used that) 3) Is there a way to tell if a problem is specifically related to the MySQL Connector/J driver or a problem connecting to MySQL? (probably should direct that one to the mysql-java list, eh?) Thanks in advance for any help. This is the first RAID machine I've ever worked with. : \ Darren Addy University of Nebraska at Kearney -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create serial number by select
On Thu, 25 Aug 2005, Pooly wrote: Why not adding an auto_increment column to your data ? 2005/8/25, Gyurasits Zoltán [EMAIL PROTECTED]: I can't build serial number in table1! Because he is not allowe to modify the table, pick any reason. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID/MySQL configuration question
My guess is that the RAID has nothing to do with it -- it seems very unlikely. In any case, if you want top performance out of your raid, you may want to change things up. You'd get better performance if you didn't use RAID5. Use RAID1 or RAID10 for your data drives. RAID5 is slower than these other methods. Based on the information you've given, I'm assuming a few things: 1) your raid controller supports RAID10 2) you have an even number of drives. If this is the case, I would recreate the raid as a RAID10 (pair up your drives and then create a stripe out of the pairs). Then you can feel free to allocate space to whatever partition struction you need. On 8/25/05, Curious George [EMAIL PROTECTED] wrote: G'morning all! (Using Red Hat Linux Enterprise 4.1) I have a Dell PowerEdge 2800 with a PERC 4 RAID controller. The RAID controller has one RAID 1 mirror and one RAID 5 stripe volume created. We installed most of the OS stuff on the RAID 1 set and the /usr/local and /var on the RAID 5 set (since the Red Hat MySQL rpm puts the data directory under /var ). I'd like to know if there are any better ways to configure this (I can repartition and reinstall the OS, if necessary). background I'm having problems with a Tomcat application (OSP - ePortfolios) that uses a lot of disk space for uploaded files (under Tomcat directory which I install under /usr/local). Not sure how large the MySQL database will grow to be. I installed the Red Hat MySQL rpm, but not sure if it is RAID-aware and considering compiling MySQL from source ( --with raid ?). Or my problem may be with the MySQL Connector/J driver (which would be a question for the mysql-java list). The application builds and installs fine with no errors, but Tomcat only works for the static directories (i.e. /jsp-examples ) and not with the application that interacts with MySQL. I've installed this application successfully on an identical non-raid system. The only differenced between the two machines is that the problem child is RAID (configured as above) and the java sdk version changed from _08 to _09. /background 1) Best way to configure the RAID/partitions for best MySQL performance? 2) Is MySQL RAID-aware if not compiled: - - with raid? (unsure if the Red Hat rpm used that) 3) Is there a way to tell if a problem is specifically related to the MySQL Connector/J driver or a problem connecting to MySQL? (probably should direct that one to the mysql-java list, eh?) Thanks in advance for any help. This is the first RAID machine I've ever worked with. : \ Darren Addy University of Nebraska at Kearney -- 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: create serial number by select
Are you using MySQL database server? On Thu, 25 Aug 2005, [ISO-8859-1] Gyurasits Zoltán wrote: Hi, Yes! And the SELECT contain some table! SELECT (??) FROM table1 INNER JOIN table2 I have a problem with the follow solution: SET @count:=0; SELECT @count:[EMAIL PROTECTED] AS `count`, `value` FROM `table`; I can't execute more select in my system. (Delphi software) :( Only 1 select allowed! Because it is the reporting system... 1 output is 1 select! Best Regards! Zoli - Original Message - From: Jason Pyeron [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, August 25, 2005 5:23 PM Subject: Re: create serial number by select On Thu, 25 Aug 2005, Pooly wrote: Why not adding an auto_increment column to your data ? 2005/8/25, Gyurasits Zoltán [EMAIL PROTECTED]: I can't build serial number in table1! Because he is not allowe to modify the table, pick any reason. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do you change rows to columns?
Would anyone have a good idea on how to change rows to columns? I'm creating a report where I have counts per hour. I can produce +--+---+ | hour | count | +--+---+ |0 | 1 | |1 | 0 | |2 | 1 | |3 | 0 | |4 | 0 | |5 | 0 | |6 | 0 | |7 | 2 | |8 | 1 | |9 | 0 | | 10 | 1 | | 11 | 0 | | 12 | 0 | | 13 | 0 | | 14 | 0 | | 15 | 0 | | 16 | 0 | | 17 | 1 | | 18 | 0 | | 19 | 1 | | 20 | 1 | | 21 | 0 | | 22 | 0 | | 23 | 0 | +--+---+ And then tack on an additional column for each catagory I'm tracking. However, I want to turn this horizontal and tack on each catagory as a row. Any ideas? mediis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RAID/MySQL configuration question
On Thu, 25 Aug 2005, Gary Richardson wrote: My guess is that the RAID has nothing to do with it -- it seems very unlikely. In any case, if you want top performance out of your raid, you may want to change things up. You'd get better performance if you didn't use RAID5. Use RAID1 or RAID10 for your data drives. RAID5 is slower than these other methods. Based on the information you've given, I'm assuming a few things: 1) your raid controller supports RAID10 2) you have an even number of drives. If this is the case, I would recreate the raid as a RAID10 (pair up your drives and then create a stripe out of the pairs). Then you can feel free to allocate space to whatever partition struction you need. On 8/25/05, Curious George [EMAIL PROTECTED] wrote: G'morning all! (Using Red Hat Linux Enterprise 4.1) I have a Dell PowerEdge 2800 with a PERC 4 RAID controller. The RAID controller has one RAID 1 mirror and one RAID 5 stripe volume created. We installed most of the OS stuff on the RAID 1 set and the /usr/local and /var on the RAID 5 set (since the Red Hat MySQL rpm puts the data directory under /var ). use LVM to set things up, so you can resize later without taking the system down. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do you change rows to columns?
[snip] Would anyone have a good idea on how to change rows to columns? I'm creating a report where I have counts per hour. I can produce +--+---+ | hour | count | +--+---+ |0 | 1 | |1 | 0 | |2 | 1 | |3 | 0 | |4 | 0 | And then tack on an additional column for each catagory I'm tracking. However, I want to turn this horizontal and tack on each catagory as a row. [/snip] Use a cross-tab query i.e SELECT SUM(IF(category1, 1, 0)) as Category1, SUM(IF(category2, 1, 0)) as Category2, SUM(IF(category3, 1, 0)) as Category3 FROM table -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create serial number by select
Hi, Yes! And the SELECT contain some table! SELECT (??) FROM table1 INNER JOIN table2 I have a problem with the follow solution: SET @count:=0; SELECT @count:[EMAIL PROTECTED] AS `count`, `value` FROM `table`; I can't execute more select in my system. (Delphi software) :( Only 1 select allowed! Because it is the reporting system... 1 output is 1 select! Best Regards! Zoli - Original Message - From: Jason Pyeron [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, August 25, 2005 5:23 PM Subject: Re: create serial number by select On Thu, 25 Aug 2005, Pooly wrote: Why not adding an auto_increment column to your data ? 2005/8/25, Gyurasits Zoltán [EMAIL PROTECTED]: I can't build serial number in table1! Because he is not allowe to modify the table, pick any reason. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- 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 4.1.14 has been released
Hi, MySQL 4.1.14, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the current production version. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * SHOW CHARACTER SET and INFORMATION_SCHEMA now properly report the Latin1 character set as cp1252. (Bug #11216 (http://bugs.mysql.com/11216)) * MySQL Cluster: A new -p option is available for use with the ndb_mgmd client. When called with this option, ndb_mgmd prints all configuration data to stdout, then exits. * The output of perror --help now displays the --ndb option. (Bug #11999 (http://bugs.mysql.com/11999)) * NDB:Improved handling of the configuration variables NoOfPagesToDiskDuringRestartACC, NoOfPagesToDiskAfterRestartACC, NoOfPagesToDiskDuringRestartTUP, and NoOfPagesToDiskAfterRestartTUP should result in noticeably faster startup times for MySQL Cluster. (Bug #12149 (http://bugs.mysql.com/12149)) * Added support of where clause for queries with FROM DUAL. (Bug #11745 (http://bugs.mysql.com/11745)) * Added an optimization that avoids key access with NULL keys for the ref methodwhen used in outer joins. (Bug #12144 (http://bugs.mysql.com/12144)) * Added new query cache test for the embedded server to the test suite, there are now specific tests for the embedded and non-embedded servers. (Bug #9508 (http://bugs.mysql.com/9508)) * Query cache is switched off if a thread (connection) has tables locked. This prevents invalid results where the locking thread inserts values between a second thread connecting and selecting from the table. (Bug #12385 (http://bugs.mysql.com/12385)) Bugs fixed: * Slave I/O threads were considered to be in the running state when launched (rather than after successfully connecting to the master server), resulting in incorrect SHOW SLAVE STATUS output. (Bug #10780 (http://bugs.mysql.com/10780)) * On Windows, the server could crash during shutdown if both replication threads and normal client connection threads were active. (Bug #11796 (http://bugs.mysql.com/11796)) * Some subqueries of the form SELECT ... WHERE ROW(...) IN (subquery) were being handled incorrectly. (Bug #11867 (http://bugs.mysql.com/11867)) * The mysql_info() C API function could return incorrect data when executed as part of a multi-statement that included a mix of statements that do and do not return information. (Bug #11688 (http://bugs.mysql.com/11688)) * Renamed the rest() macro in my_list.h to list_rest() to avoid name clashes with user code. (Bug #12327 (http://bugs.mysql.com/12327)) * myisampack failed to delete .TMD temporary files when run with -T option. (Bug #12235 (http://bugs.mysql.com/12235)) * Concatenating USER()/DATEBASE() with a column produces invalid results. (Bug #12351 (http://bugs.mysql.com/12351)) * For PKG installs on Mac OS X, the preinstallation and postinstallation scripts were being run only for new installations and not for upgrade installations, resulting in an incomplete installation process. (Bug #11380 (http://bugs.mysql.com/11380)) * User variables were not automatically cast for comparisons, causing queries to fail if the column and connection character sets differed. Now when mixing strings with different character sets but the same coercibility, allow conversion if one character set is a superset of the other. (Bug #10892 (http://bugs.mysql.com/10892)) * Pathame values for options such as ---basedir or --datadir didn't work on Japanese Windows machines for directory names containing multibyte characters having a second byte of 0x5C ('\'). (Bug #5439 (http://bugs.mysql.com/5439)) * Mishanding of comparison for rows containg NULL values against rows produced by an IN subquery could cause a server crash. (Bug #12392 (http://bugs.mysql.com/12392)) * INSERT ... SELECT ... ON DUPLICATE KEY UPDATE could fail with an erroneous Column 'col_name' specified twice error. (Bug #10109 (http://bugs.mysql.com/10109)) * myisam.test failed when server compiled using --without-geometry option. (Bug #11083 (http://bugs.mysql.com/11083)) * Creation of the mysql group account failed during the RPM installation. (Bug #12348 (http://bugs.mysql.com/12348)) * FLUSH TABLES WITH READ LOCK combined with LOCK TABLE .. WRITE caused deadlock. (Bug #9459 (http://bugs.mysql.com/9459)) * GROUP_CONCAT ignores
Server-side/prepared statements and CR_SERVER_GONE
It seems that the implementation of server-side or prepared statements is significantly less robust than client-side prepared statements and other connection-dependent parts of MySQL, which means that they are going to be a pain in the backside to work with. Previously MySQL features have tended to be elegant and include a certain degree of graceful recovery, consider: 8x--- snip ---x8 mysql_query(create table tmp_foo (foo int)); if (mysql_query(truncate tmp_foo)) printf(#1 failed ** unexpected\n); system(service mysql restart); if (mysql_query(truncate tmp_foo)) printf(#2 failed expected, server gone\n); if (mysql_query(truncate tmp_foo)) printf(#3 failed **unexpected\n); else printf(connection recovered so the third query went thru again); 8x--- snip ---x8 Obviously if you have a client-side prepared statement, the connection loss isn't going to affect it. On a server-side prepare statement, however, losing your connection is going to blow away the server-side prepared statement, which was specific to that connection and held transiently in memory on the server. That's fair and understandable. But on the client you have to be aware that when the connection resets ALL of your prepared statements just went away and must ALL be re-constructed before being re-used. That means an awful lot of extra management for the client application and developer, especially since there is no way to tell that a statement is no-longer valid. It seems worth the minor extra memory overhead of having the client keep the source information for setting up the statement: copy the source sql statement text and copy the bind structure. Looking at the MYSQL_STMT structure it looks as though you have enough information to, for example, go through all of the stmt's attached to a connection and set their statement id to a value that indicates reset. - Oliver -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL
Hi Shawn The short answer is Yes. Do you want a list of companies from the Fortune 100 or are you interested in a particular business area? There are some huge names (http://www.mysql.com/customers/) that use MySQL for critical, public-facing, and infrastructure applications. Many of them use PHP as their front-end. Did you have a specific question or were you just curious of the viability of PHP+MySQL as an application platform? I'm not the curious type at all. At least not the type you mention here. ;-) I was reading about transactions in the PHP5 manual and since they stated that the mysqli_xx functions were experimental, I wondered if they were reliable enough to use them in a production environment. Data integrity rely at least a bit on transactions so that's my concern. It's always too late when you find that your data have been corrupted in any way. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL
news [EMAIL PROTECTED] wrote on 08/25/2005 01:39:20 PM: Hi Shawn The short answer is Yes. Do you want a list of companies from the Fortune 100 or are you interested in a particular business area? There are some huge names (http://www.mysql.com/customers/) that use MySQL for critical, public-facing, and infrastructure applications. Many of them use PHP as their front-end. Did you have a specific question or were you just curious of the viability of PHP+MySQL as an application platform? I'm not the curious type at all. At least not the type you mention here. ;-) I was reading about transactions in the PHP5 manual and since they stated that the mysqli_xx functions were experimental, I wondered if they were reliable enough to use them in a production environment. Data integrity rely at least a bit on transactions so that's my concern. It's always too late when you find that your data have been corrupted in any way. Thanks That seems more like a question to ask the PHP people as MySQL has nothing to do with the development or support of the mysqli_xx interfaces. Are transactions stable and supported (production ready) in MySQL? Yes, with one caveat. You must keep your data in InnoDB tables in order to get full commit/rollback support. MyISAM does not support automatic rollbacks. Is mysqli_xx written to properly use the built-in transaction support? I don't know. Again I refer you to PHP for an answer to that question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL
Hi Shawn, Are transactions stable and supported (production ready) in MySQL? Yes, with one caveat. You must keep your data in InnoDB tables in order to get full commit/rollback support. MyISAM does not support automatic rollbacks. Is mysqli_xx written to properly use the built-in transaction support? I don't know. Again I refer you to PHP for an answer to that question. I see. Thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you change rows to columns?
Hello. Similar question was asked recently. See: http://lists.mysql.com/mysql/187940 Mark Ahlstrom [EMAIL PROTECTED] wrote: Would anyone have a good idea on how to change rows to columns? I'm creating a report where I have counts per hour. I can produce +--+---+ | hour | count | +--+---+ |0 | 1 | |1 | 0 | |2 | 1 | |3 | 0 | |4 | 0 | |5 | 0 | |6 | 0 | |7 | 2 | |8 | 1 | |9 | 0 | | 10 | 1 | | 11 | 0 | | 12 | 0 | | 13 | 0 | | 14 | 0 | | 15 | 0 | | 16 | 0 | | 17 | 1 | | 18 | 0 | | 19 | 1 | | 20 | 1 | | 21 | 0 | | 22 | 0 | | 23 | 0 | +--+---+ And then tack on an additional column for each catagory I'm tracking. However, I want to turn this horizontal and tack on each catagory as a row. Any ideas? mediis -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
number formating
Here I have a table column defined as integer type. it stores number from 1 to the hundred range (3 digits). For nice output (without using any external programming languages), I would want the printed type to have zerofill. One way to do it is to convert the type of the column to the zerofill. Is there another way to do it? Such as round, cast. Kemin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is this a permissions problem?
Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [MySQL][ODBC 3.51 Driver]Access denied for user: '@localhost' to database 'client_db1' I am having trouble getting any MySQL site to work on my local PC, but they work fine remotely. I have just changed PC and now have IIS whereas before I used to have Personal Web Server. I have just managed to stop this happening with any microsoft access database site by changing permissions, but I am not having the same kind of result with the MySQL sites. Do I need to change permissions for the MySQL databases? If so, up on which folder do I change permissions. My websites are located in C:\inetpub\wwwroot, and MySQL is located at C:\mysql. The location of these folders didn't have any adverse effect with personal web server, does it with IIS? Mat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: number formating
Kemin Zhou wrote: Here I have a table column defined as integer type. it stores number from 1 to the hundred range (3 digits). For nice output (without using any external programming languages), I would want the printed type to have zerofill. One way to do it is to convert the type of the column to the zerofill. Is there another way to do it? Such as round, cast. Kemin I think LPAD(string, length, pad_string) is what you want. MySQL will automatically convert your integer to a string if used in string context, so something like this should do: SELECT LPAD(int_col, 3, '0') FROM your_table; For example: mysql SELECT LPAD(13, 3, '0'); +--+ | LPAD(13, 3, '0') | +--+ | 013 | +--+ 1 row in set (0.00 sec) LPAD() is described on the string functions page in the manual http://dev.mysql.com/doc/mysql/en/string-functions.html. One caveat: if the input string is longer than the given length, the string gets truncated (on the right). mysql SELECT LPAD(1234, 3, '0'); ++ | LPAD(1234, 3, '0') | ++ | 123| ++ 1 row in set (0.00 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Trigger exception handling
I don't think this is really what I'm looking for. What I need is to be able to perform certain validation on the data from within the trigger. If that validation fails, then I need the trigger to abort with an error. The handling below seems to just handle if there's a SQL error. I need to somehow create my own error condition. I'm almost looking for something like this I guess: Create trigger .. DECLARE EXIT HANDLER FOR SQLSTATE 'CustomError' SET @err=-500; .. If INSTR(new.ACCOUNT_NUM, ' ') 0 Then Throw error CustomError; End if; ... insert into accounts (ACCOUNT_NUM) values ('123 456'); ... insert should fail in this instance, irrespective of any other constraints like not-null/unique fields, etc. There's other fields/tables that validation is performed on from within the current PL/SQL triggers, this is just the most basic example. I'm getting the feeling I'm pretty rare in this situation. Thanks, Dan. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Friday, August 19, 2005 10:52 AM To: Burke, Dan Cc: mysql@lists.mysql.com Subject: Re: Trigger exception handling snip DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @err=23000; INSERT INTO testhandler VALUES( NULL) ; snip Burke, Dan wrote: Hello, I've been looking into converting our existing Oracle PL/SQL code to mysql. A lot of the syntax is pretty straight forward, and really doesn't require much change from what I've been testing with thus far. However, I'm trying to handle exceptions, and I cannot seem to find any documentation that shows me what I'm looking for. In oracle, we have bits of code like this: IF INSTR(:new.ACCOUNT_NUM, ' ') 0 THEN RAISE AcctNumHasSpace; END IF; and then EXCEPTION WHEN AcctNumHasSpace THEN RAISE_APPLICATION_ERROR(-20001, 'Cannot insert space into ACCOUNT_NUM'); But I cannot seem to figure out how to replicate that behavior. Is it something that's possible to do as of yet? Or not really? Basically in this situation, we want the insert to fail if there is a space in that field. Any advice would be great. Dan. _ This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.13/78 - Release Date: 8/19/2005 ___ This message has been scanned for viruses by TechTeam's email gateway. _ This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is this a permissions problem?
Matthew Stuart [EMAIL PROTECTED] wrote on 08/25/2005 03:26:51 PM: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [MySQL][ODBC 3.51 Driver]Access denied for user: '@localhost' to database 'client_db1' I am having trouble getting any MySQL site to work on my local PC, but they work fine remotely. I have just changed PC and now have IIS whereas before I used to have Personal Web Server. I have just managed to stop this happening with any microsoft access database site by changing permissions, but I am not having the same kind of result with the MySQL sites. Do I need to change permissions for the MySQL databases? If so, up on which folder do I change permissions. My websites are located in C:\inetpub\wwwroot, and MySQL is located at C:\mysql. The location of these folders didn't have any adverse effect with personal web server, does it with IIS? Mat Yes it is a permissions problem but not a _folder_ permissions problem. You error message tells me that you are attempting to authenticate with the mysql server as the mysql user anybody. The anybody account can be identified by the _lack_ of a name before the @ symbol. There are two things you need to check. A) there is a MySQL user with MySQL permissions to do whatever it is you want to do in MySQL. b) When you attempt to connect, you attempt to connect as that user. Check out the following reading for more details: http://dev.mysql.com/doc/mysql/en/user-account-management.html http://dev.mysql.com/doc/mysql/en/connecting-disconnecting.html http://dev.mysql.com/doc/mysql/en/connecting.html http://dev.mysql.com/doc/mysql/en/access-denied.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Trigger exception handling
Burke, Dan [EMAIL PROTECTED] wrote on 08/25/2005 03:42:18 PM: I don't think this is really what I'm looking for. What I need is to be able to perform certain validation on the data from within the trigger. If that validation fails, then I need the trigger to abort with an error. The handling below seems to just handle if there's a SQL error. I need to somehow create my own error condition. I'm almost looking for something like this I guess: Create trigger .. DECLARE EXIT HANDLER FOR SQLSTATE 'CustomError' SET @err=-500; .. If INSTR(new.ACCOUNT_NUM, ' ') 0 Then Throw error CustomError; End if; ... insert into accounts (ACCOUNT_NUM) values ('123 456'); ... insert should fail in this instance, irrespective of any other constraints like not-null/unique fields, etc. There's other fields/tables that validation is performed on from within the current PL/SQL triggers, this is just the most basic example. I'm getting the feeling I'm pretty rare in this situation. Thanks, Dan. snip No, what you want to do isn't rare, it's new to MySQL. Stored procedures and triggers are both new to MySQL 5.0 and may not be mature enough to do what you are asking of them, yet. It took several point updates to the beta code for triggers to even be able to work with other tables. As was mentioned before, there is not yet a raise error equivalent in the procedural SQL of MySQL. This, too, is new for 5.0. I look at it this way, you are one of the pioneering users in this area. What you discover and work out will benefit the rest of us and will demonstrate to the developers just how much they have left to do to make triggers a mature and useful feature. Best Wishes, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Trigger exception handling
Thank you for your response. I understand that stored procedures and triggers are very much new to MySQL. In fact, this has been an eagerly awaited feature by my management as a means to remove our dependence on that budget eater (Oracle) (and myself for personal projects). I was hoping that I just wasn't finding the right place in the documentation, or that someone else had already encountered this requirement in MySQL 5.0. I will take that as confirmation that what we need is not (yet?) in MySQL. With that in mind, I can see if I'm able to come up with a workaround or if I need to shelve these particular lines of code (comment them out in the MySQL version for now), and revisit it with each beta release. On a side note, what they've done so far is great! This is something, even in it's current functionality, that I see scores of use for. For hopefully obvious reasons, I don't use Oracle for personal projects, so triggers are a feature I've longed for in MySQL. Dan. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, August 25, 2005 4:16 PM To: Burke, Dan Cc: mysql@lists.mysql.com Subject: RE: Trigger exception handling insert into accounts (ACCOUNT_NUM) values ('123 456'); ... insert should fail in this instance, irrespective of any other constraints like not-null/unique fields, etc. snip No, what you want to do isn't rare, it's new to MySQL. Stored procedures and triggers are both new to MySQL 5.0 and may not be mature enough to do what you are asking of them, yet. It took several point updates to the beta code for triggers to even be able to work with other tables. As was mentioned before, there is not yet a raise error equivalent in the procedural SQL of MySQL. This, too, is new for 5.0. I look at it this way, you are one of the pioneering users in this area. What you discover and work out will benefit the rest of us and will demonstrate to the developers just how much they have left to do to make triggers a mature and useful feature. Best Wishes, Shawn Green Database Administrator Unimin Corporation - Spruce Pine ___ This message has been scanned for viruses by TechTeam's email gateway. _ This e-mail transmission is strictly confidential and intended solely for the person or organization to whom it is addressed. It may contain privileged and confidential information and if you are not the intended recipient, you must not copy, distribute or take any action in reliance on it. If you have received this e-mail in error, please notify the sender as soon as possible and delete the e-mail message and any attachment(s). This message has been scanned for viruses by TechTeam's email gateway.
Intelligent Converters product: MSSQL-to-MySQL
Has anyone ever used this MSSQL-to-MySQL converter? It's pretty reasonable at $40, and the demo output I got looked pretty good. But I wanted to see if there is anything I should be weary about. http://www.convert-in.com/mss2sql.htm Thanks, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Per-thread memory use question
Hi, This formula shows up in a few places (this is from http://dev.mysql.com/books/hpmysql-excerpts/ch06.html): min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer My question is, once one of the buffers (e.g., sort_buffer) is needed by a thread, does the thread hold onto it in case it needs it again, or does the thread free it as soon as it can? I'm using 4.1.13. I'm trying to figure out an optimum value for max_connections. If the threads don't release their memory, then I really do have to account for the fact that each thread over time will probably be holding each of those buffers. If threads give up the memory as soon as the, e.g., sort, is finished, then I only have to figure out how many threads are likely to need a sort_buffer at any given time. I looked through the manual, various online documentation, and the source, but haven't been able to determine an answer. Thanks, -- Pete Harlan [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The Intel MySQL compiled Server
Hello guys.. I want to use INTEL SHARED MySQL 4.0.25 - Pro with innodb tables on PRODUCTION SERVER DELL 2600 The Linux Kernel is 2.6.12 and the hardware is DUAL XEON 2.4 HT iwth 4 GB of RAM (i'm using raw partitions feature on innodb tables) this is a secure option ? the Intel version of MySQL is really secure to use on production servers ? anyone try this ? tnks -- - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 296 -6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
segmentation fault using mysql
Running MySql 4.1.14 on RedHat 7.2 I just upgraded from 4.1.13 to 4.1.14 and was am experiencing problems using the mysql client. # /usr/local/mysql/bin/mysql -h localhost -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 to server version: 4.1.14-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql \h Segmentation fault My searches have not resulted in any solutions at this point - just thought I'd throw it out to the list and see if anyone has had a similar experience. Ed Kasky ~ Randomly Generated Quote (324 of 486): I would have made this shorter, but I ran out of time. . . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
45 minutes to check table to complete
Hi, I've got a database that is closed to 7G. I'm using the Standard 4.1.14 version. The table was corrupt and I had to do a lot of moving of data to free up enough space on the partition as I discovered I needed at least twice the database size to do a recover. Well, using myisamchk -o worked. I then ran check table in the mysql client to see what it would say. It said all was well. I'm just suprised that it took 45 minutes to run. Anyone run check table on large databases? What kind of times did you encounter? I must say this is on an OLD box ... maybe 512 RAM and right now I don't now the disk drives used. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
On Thursday 25 August 2005 04:44 am, [EMAIL PROTECTED] wrote: Hal Vaughan [EMAIL PROTECTED] wrote on 24/08/2005 17:41:36: # Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? I don't think so. But may I inquire why you do not want to have a key? What you are saying is How can I do a job without using the tool designed for the job?. If there is no key, in order to do what you want, MySQL would have to do a linear search through the table in order to check for duplicates - the kind of lengthy operation it is designed to avoid whenever possible. The key is a necessary part of the effect you want to achieve. Alec I have some routines for entering large amounts of data into different tables. *IF* INSERT IGNORE worked, it was easy for me to simply add IGNORE to a query string (this is all in Perl) for tables where I did not want dupes. I also have a number of tables where there are reasons for allowing multiple entries. There are also some tables where items from one source must not be duplicated, where entries from another source should be, since they are counted later. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT IGNORE Doesn't Seem To Work
Hal, *IF* INSERT IGNORE worked ... INSERT IGNORE _does_ work exactly as documented in the manual: "If you specify the IGNORE keyword in an INSERT statement, errors that occur while executing the statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the error is ignored and the row is not inserted. Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest value values and inserted; warnings are produced but the statement does not abort." (http://dev.mysql.com/doc/mysql/en/insert.html) , it was easy for me to simply add "IGNORE " to a query string (this is all in Perl) for tables where I did not want dupes. In relational databases, the usual method of preventing duplicate values is via PRIMARY or UNIQUE indexes. Absent such indexes, you need application code to prevent dupes. PB - Hal Vaughan wrote: On Thursday 25 August 2005 04:44 am, [EMAIL PROTECTED] wrote: Hal Vaughan [EMAIL PROTECTED] wrote on 24/08/2005 17:41:36: # Okay, so INSERT IGNORE only works if I am avoiding duplicate keys. Is there any way to use INSERT the way I thought INSERT IGNORE worked -- in other words is there any keyword for the INSERT command to keep it from duplicating rows if there isn't a key? I don't think so. But may I inquire why you do not want to have a key? What you are saying is "How can I do a job without using the tool designed for the job?". If there is no key, in order to do what you want, MySQL would have to do a linear search through the table in order to check for duplicates - the kind of lengthy operation it is designed to avoid whenever possible. The key is a necessary part of the effect you want to achieve. Alec I have some routines for entering large amounts of data into different tables. *IF* INSERT IGNORE worked, it was easy for me to simply add "IGNORE " to a query string (this is all in Perl) for tables where I did not want dupes. I also have a number of tables where there are reasons for allowing multiple entries. There are also some tables where items from one source must not be duplicated, where entries from another source should be, since they are counted later. Hal No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.15/81 - Release Date: 8/24/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing underscores ( _ ) in database tables
(Sorry if this is a repeat; I think my first post didn't go through.) I'm still stuck on the problem I asked about a day or two ago. I'm working on a page at http://www.geozoo.org/stacks/ that draws data from a table that lists animal taxons (orders, families, species, etc.) in a child-parent relationship. It works exactly the way it should. Try http://www.geozoo.org/stacks/Animalia, watching the navigation links and the column on the right, for example. The problem is that the children of genera - species - are properly displayed as TWO WORDS - the child (species) and parent (genus). http://www.geozoo.org/stacks/Canis illustrates the problem I run into when I tweak my PHP so that Canis lupus is displayed instead of just lupus, for example. If you click Canis lupus or type in http://www.geozoo.org/stacks/Canis_lupus, you get a 404 Page Not Found Error. I want it to work like this page: http://animaldiversity.ummz.umich.e...anis_lupus.html Notice that the parent displays with just one variable: http://animaldiversity.ummz.umich.e...tion/Canis.html But I don't think I'm ever going to figure this out until I encounter someone who already has a similar script up and running. In the meantime, I had another idea. Suppose I create a new table field that lists the full species name, including an underscore. For example, genera and species look something like this in my current table: NAME | PARENT Canis | Canidae lupus | Canis Panthera | Felidae leo | Panthera Home | Pongidae sapiens | Homo My new table might look like this: NEWNAME | NAME | PARENT Canis | Canis | Canidae Canis_lupus | lupus | Canis Panthera | Panthera | Felidae Panthera_leo | leo | Panthera Homo | Homo | Pongidae Homo_sapiens | sapiens | Homo So instead of displaying Parent + Name (Homo sapiens) and adding an underscore, I just display NewName (Homo_sapiens). I'm just wondering if there's anything I need to know about using underscores in database tables. I assume I can manipulate the underscore with PHP and/or Apache mod_rewrite, if necessary. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]