Problems with backup and special characters
Restoring a backup of a very large database, I've received an error message by the server (4.13) about an sql syntax error. I've done the backup with mysql administrator and I've used the same procedure for months without problems. I've discovered that problem is into a blob field that store long descriptive text. That filed, is populated automatically with an import procedure from an Oracle db where, when a phrase is too long, it continues on another record. In this field, often is stored phrases contained between quotation marks (). However the problem is present only when the phrase begin into a record with quotation marks and reach the end into the next record BUT with the ONLY quotation marks. How can I solve this problem if I cannot modify the insert sql statement? I need an expedient that works only for the backup and restore procedures. Thanks a lot Stefano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disasterous database corruption
On Wednesday 10 August 2005 10:23 am, Daniel Kasak wrote: Fajar Priyanto wrote: Well, it's not 5.0.x, it's 4.0.25-standard. I'm not sure how the error appears in the first place. What I notice when I open the database using phpMyadmin is that that particular troubled table status is in use. I'm allowed to ssh into the server, and hopefully I can save the table one by one as you suggested. I'll inform the result here. Thanks. That actually sounds nothing like the problem I'm having. I'm not getting any 'in use' errors. Perhaps you have a different issue? I'm not sure, but the symptom is similar to yours. Apparently the command SHOW doesn't work. And now, using SELECT from phpMyadmin I'm manually dumping every table. Luckily, I have a backup of last month database so I can follow the name of 56 tables. Thanks. -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux2.arinet.org 13:58:10 up 1:57, Mandrakelinux release 10.2 (Limited Edition 2005) for i586 public key: https://www.arinet.org/fajar-pub.key -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cleanup of connections
Hi, I've noticed that connections are sometimes left open in MySQL when i use the Show Processlist command. Does MySQL do any form of housekeeping to close old connections or is it just down to the developers to make sure they issue close connection statements? Thanks. == For more information on Hargreaves Lansdown, visit our web site http://www.hargreaveslansdown.co.uk IMPORTANT NOTICE This email is intended solely for the recipient and is confidential and not for third party unauthorised distribution. If an addressing, or transmission, error has misdirected this email, please notify the author by replying to this email or notifying the system manager ([EMAIL PROTECTED]) if you are not the intended recipient you must not disclose, distribute, copy, print or rely on this email. Any opinions expressed in this document are those of the author and do not necessarily reflect the opinions of Hargreaves Lansdown. In addition, staff are not authorised to enter into any contract through email and therefore nothing contained herein should be construed as such. This email has been prepared using information believed by the author to be reliable and accurate but Hargreaves Lansdown makes no warranty as to the accuracy or completeness. In particular, Hargreaves Lansdown does not accept responsibility for any changes made to this email after it was sent. All group companies are Authorised and regulated by the Financial Services Authority and registered in England and the registered office is Kendal House, 4 Brighton Mews, Clifton, Bristol, BS8 2NX. Telephone: 0117 9889880 __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Getting the data dictionary in MySQL 4.1.13
Hi, Initially I used MySQL 5.0.4 for all my development. At that time I used the following SQL query to load the whole data dictionary into my local memory to reduce load on DB as I am using the prepared statements API, that requires column types as one of the inputs. select Table_Name,column_name,Data_Type from information_schema.columns; But now we reverted back to 4.1.13 (for releasing on production). But there is no information_schema database in MySQL 4.1.13. So can any one tell me how can I build the data dictionary in MySQL 4.1.13. Thank you sujay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication, charset / collations Problem
Hello. `key` varchar(255) character set utf8 collate utf8_bin NOT NULL All your character_set_xxx variables have a 'latin1' value, while the `key` column has utf8. In my opinion, this is the root of the problem. Marco P$hler [EMAIL PROTECTED] wrote: Please, send the output of the following statement executed both on master and slave: show variables like '%char%'; mysql show global variables like 'c%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert| ON | | connect_timeout | 5 | +--++ 12 rows in set (0.00 sec) The output is identical on both servers. Send the definition of your table as well: SHOW CREATE TABLE sum_day_key_requests; mysql SHOW CREATE TABLE sum_day_key_requests \G *** 1. row *** Table: sum_day_key_requests Create Table: CREATE TABLE `sum_day_key_requests` ( `day` date NOT NULL default '-00-00', `type` varchar(50) character set latin1 collate latin1_bin NOT NULL default '', `client` varchar(16) character set latin1 collate latin1_bin NOT NULL default '', `channel` varchar(16) character set latin1 collate latin1_bin NOT NULL default '', `campaign` varchar(100) character set latin1 collate latin1_bin NOT NULL default '', `grouping` varchar(64) character set latin1 collate latin1_bin NOT NULL default '', `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', `afftraf` varchar(5) character set latin1 collate latin1_bin NOT NULL default '', `sum` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`day`,`type`,`client`,`channel`,`campaign`,`grouping`,`key`,`afftraf`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (1.05 sec) The create statements are identical on both servers. I have no more ideas. Should I try to reproduce the error with a smaller example ? Marco --- http://www.tuxoo.org http://www.kontaktlinsen-preisvergleich.de -- 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: Problems with backup and special characters
Hello. What do you think about switching to mysqldump with --hex-blob option? [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Restoring a backup of a very large database, I've received an error message by the server (4.13) about an sql syntax error. I've done the backup with mysql administrator and I've used the same procedure for months without problems. I've discovered that problem is into a blob field that store long descriptive text. That filed, is populated automatically with an import procedure from an Oracle db where, when a phrase is too long, it continues on another record. In this field, often is stored phrases contained between quotation marks (). However the problem is present only when the phrase begin into a record with quotation marks and reach the end into the next record BUT with the ONLY quotation marks. How can I solve this problem if I cannot modify the insert sql statement? I need an expedient that works only for the backup and restore procedures. Thanks a lot Stefano -- 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: Getting the data dictionary in MySQL 4.1.13
Hello. The common way is to use different 'SHOW' statements, for example 'SHOW CREATE TABLE'. Sometimes mysqldump with --no-data helps a lot. Sujay Koduri [EMAIL PROTECTED] wrote: Hi, Initially I used MySQL 5.0.4 for all my development. At that time I used the following SQL query to load the whole data dictionary into my local memory to reduce load on DB as I am using the prepared statements API, that requires column types as one of the inputs. select Table_Name,column_name,Data_Type from information_schema.columns; But now we reverted back to 4.1.13 (for releasing on production). But there is no information_schema database in MySQL 4.1.13. So can any one tell me how can I build the data dictionary in MySQL 4.1.13. Thank you sujay -- 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: Cleanup of connections
Hello. What state do MySQL threads of your old connections have? Ben Smith [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: US-ASCII, 23 lines --] Hi, I've noticed that connections are sometimes left open in MySQL when i use the Show Processlist command. Does MySQL do any form of housekeeping to close old connections or is it just down to the developers to make sure they issue close connection statements? Thanks. == -- 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: Cleanup of connections
-Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 10 August 2005 09:07 To: mysql@lists.mysql.com Subject: Re: Cleanup of connections Hi, They appear to be in SLEEP state Hello. What state do MySQL threads of your old connections have? Ben Smith [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: US-ASCII, 23 lines --] Hi, I've noticed that connections are sometimes left open in MySQL when i use the Show Processlist command. Does MySQL do any form of housekeeping to close old connections or is it just down to the developers to make sure they issue close connection statements? Thanks. == -- 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] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ == For more information on Hargreaves Lansdown, visit our web site http://www.hargreaveslansdown.co.uk IMPORTANT NOTICE This email is intended solely for the recipient and is confidential and not for third party unauthorised distribution. If an addressing, or transmission, error has misdirected this email, please notify the author by replying to this email or notifying the system manager ([EMAIL PROTECTED]) if you are not the intended recipient you must not disclose, distribute, copy, print or rely on this email. Any opinions expressed in this document are those of the author and do not necessarily reflect the opinions of Hargreaves Lansdown. In addition, staff are not authorised to enter into any contract through email and therefore nothing contained herein should be construed as such. This email has been prepared using information believed by the author to be reliable and accurate but Hargreaves Lansdown makes no warranty as to the accuracy or completeness. In particular, Hargreaves Lansdown does not accept responsibility for any changes made to this email after it was sent. All group companies are Authorised and regulated by the Financial Services Authority and registered in England and the registered office is Kendal House, 4 Brighton Mews, Clifton, Bristol, BS8 2NX. Telephone: 0117 9889880 __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP! sql command question for mysql
Hello there, first of all, my english isn't good, hope you understand what I mean. I have a table name p like that: mysql select * from p; +---+---++ | id| price | vendor | +---+---++ | OG012 |40 | a | | OG012 |20 | b | | OG012 |20 | c | | OG013 |40 | c | +---+---++ and I have another table name q: mysql select * from q; +++ | vendor | rating | +++ | a | 1 | | b | 2 | | c | 3 | +++ My question is how do I find the minimal price for each id and vendor rating is highest. I wish my result is: +---+---++ | id| price | vendor | +---+---++ | OG012 |20 | b | | OG013 |40 | c | +---+---++ Since vender b and c are the same price for id(OG012), but vendor b has minimal rating then vendor c. Many thanks, Joe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN QUERY - UPDATE ... help?!
Brendan Gogarty wrote: We are running mysql 3.23.58 and I want to do a query with joins from two tables and then insert the results into the column of a third. [snip] Shawn Green wrote: Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the thing to be updated. [snip] Michael wrote: [snip] From the manual page Shawn cites: Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables. So, yes, it's a version issue. So, to return to my original question ... Is there a workaround or a way of updating from a joined query in 4mysql? I thought perhaps creating a temporary table, but even this doesn't seem to work? There MUST be a way of doing this ... Its an administrative nightmare. Regards, Brendan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to find out the name of the server
Is this information exposed anywhere or am I going to have to write something In particular I want the connection ID And Is the application at the source exposed And Can you trap the login event as a trigger yet? Kn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gleb Paharenko Sent: 09 August 2005 11:02 To: mysql@lists.mysql.com Subject: Re: how to find out the name of the server Hello. You can determine the address of the server using 'status' command: mysql status -- mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) Connection id: 532284 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 4.1.12-log Protocol version: 10 Connection: 10.100.1.176 via TCP/IP ^^ Server characterset:cp1251 Db characterset:latin1 Client characterset:latin1 Conn. characterset:cp1251 TCP port: 3306 Uptime: 23 days 6 hours 27 min 59 sec suomi [EMAIL PROTECTED] wrote: Hi every probably silliest question ever posted: we are running mysql on serveral hosts and sometime it is important to know, which host is the mysql server you are connected to. this is not a session nor a global variable, there is no show statement... what is it else? how to ask a mysql client the name of the server you are connected to? suomi -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleanup of connections
Hello. Normally mysqld successfully closes connection if client has correctly disconnected. If you want to debug this issue, please, provide information about your operating system and MySQL version. If your developers connect to MySQL through TCP/IP, check with netstat the state of connections to MySQL port (usually 3306). Ben Smith [EMAIL PROTECTED] wrote: They appear to be in SLEEP state -- 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: Upgrade from 4.018 to 4.1.13 ?
Hello. See: http://dev.mysql.com/doc/mysql/en/upgrade.html m i l e s [EMAIL PROTECTED] wrote: Hi, I have a silly questionDUH Im running OS X Server, I installed 4.018 and I want to run 4.1.13, how do I upgrade the server, without losing anything ? Do I just install over it ? Of course backing up all the tables and what not first ? Any pointers would be helpful. Thanks. M i l e s. -- 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: Table and Data access only through procedures
Hello. MySQL 5.0.10 offers modifying tables from stored procedures, however I've met some problems. I've tried to create a procedure like your DELETE_USER, but DROP USER seems to accept only literal strings, not variables, while direct modifying of GRANT tables and calling FLUSH privileges leads server to hang. See my bug report: http://bugs.mysql.com/bug.php?id=12485 Terence [EMAIL PROTECTED] wrote: Since there was no reply, I will try once more. I wish to limit table access and data manipulation strictly through procedures. Can MySQL 5 offer this? If a user connects to the db and executes UPDATE or DELETE it should not allow it, except by calling a procedure e.g. CALL DELETE_USER($user_id) Since my database is being called through Oracle, VB, ASP and PHP applications, it's becoming increasingly difficult to ensure data integrity (and auditing). Any tips would be great. Original Message Subject: Tabls access only through procedures Date: Thu, 14 Jul 2005 17:10:51 +0800 From: Terence [EMAIL PROTECTED] To: mysql@lists.mysql.com mysql@lists.mysql.com Hi List, I belive version 5 does not allow me to grant access to execute procedures, but deny updates to tables directly? I am attempting to ensure that all data modification is done through procedures. Any tips or ideas when this will be available or work-arounds? Thanks, Terence -- 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: Help table's locked/missing?
Hello. Do you have a shell account? What engines do you use for your tables? In your previous post you said you had tried to repair database (did you want to say table?) while it was in use. What way had you chosen for repairing? If myisamchk - that wasn't good idea on running server. If all your tables MyISAM and you have a shell on that server, stop the server and use copy all your databases to safe location. Than use myisamchk to check and repair. Then replace the old database with repaired one. Fajar Priyanto [EMAIL PROTECTED] wrote: Hi Gleb, This is what I have using SHOW PROCESSLIST: Waiting for tableSHOW TABLE STATUS FROM `db_mambo` LIKE 'mos_TFS_visits' Help me pls, is there any way I can save this database? Looks like the troubled table is mos_TFS_visits. Thanks, Fakar -- 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: Cleanup of connections
I've found out a little more. There is a PHP script that is run from a cron job which goes off and gets data from a 3rd party. Generally this completes just fine but there is another job, a cron job, that Kills this script/connection if it takes more than 2 minutes. I was just wondering if this would leave the connection open in MySQL. -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 10 August 2005 10:05 To: mysql@lists.mysql.com Subject: Re: Cleanup of connections Hello. Normally mysqld successfully closes connection if client has correctly disconnected. If you want to debug this issue, please, provide information about your operating system and MySQL version. If your developers connect to MySQL through TCP/IP, check with netstat the state of connections to MySQL port (usually 3306). Ben Smith [EMAIL PROTECTED] wrote: They appear to be in SLEEP state -- 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] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ == For more information on Hargreaves Lansdown, visit our web site http://www.hargreaveslansdown.co.uk IMPORTANT NOTICE This email is intended solely for the recipient and is confidential and not for third party unauthorised distribution. If an addressing, or transmission, error has misdirected this email, please notify the author by replying to this email or notifying the system manager ([EMAIL PROTECTED]) if you are not the intended recipient you must not disclose, distribute, copy, print or rely on this email. Any opinions expressed in this document are those of the author and do not necessarily reflect the opinions of Hargreaves Lansdown. In addition, staff are not authorised to enter into any contract through email and therefore nothing contained herein should be construed as such. This email has been prepared using information believed by the author to be reliable and accurate but Hargreaves Lansdown makes no warranty as to the accuracy or completeness. In particular, Hargreaves Lansdown does not accept responsibility for any changes made to this email after it was sent. All group companies are Authorised and regulated by the Financial Services Authority and registered in England and the registered office is Kendal House, 4 Brighton Mews, Clifton, Bristol, BS8 2NX. Telephone: 0117 9889880 __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT vs SET for creating variables
I am using version 4.0.12-nt on a Windows 2000 machine. I have noticed some difference in the way SET and SELECT create variables. The following statements do not work the way I would expect. SELECT @neededStep := 10; SELECT @startOfCenter := 7; SELECT @returnData := IF(@neededStep = @startOfCenter, CONCAT(@neededStep, ' = ', @startOfCenter), CONCAT(@neededStep, ' ', @startOfCenter) ); In the above example @returnData ends up being '10 = 7', but when @neededStep is less than 10 the expected results are found. (could this be because @neededStep is stored as text and not a number?) In the next example @returnData ends up being '10 7', as expected. This seems to work for all values of @neededStep I have tested. SET @neededStep := 10; SET @startOfCenter := 7; SELECT @returnData := IF(@neededStep = @startOfCenter, CONCAT(@neededStep, ' = ', @startOfCenter), CONCAT(@neededStep, ' ', @startOfCenter) ); It would seem that SET is a better way to create variables from constant values, but I would like to understand why. Does anybody know what is happening here? Thanks for your time! bill -- Bill Dodson Parkline, Inc. http://www.parkline.com phone: 304-586-2113 x149 fax: 304-586-3842 email: [EMAIL PROTECTED] Email Disclaimer The information in any email is confidential and may be legally privileged. It is intended solely for the addressee. Access to the email message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. If you have received an email message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table and Data access only through procedures
Gleb, I had already submitted that bug, around a week ago, you can see my version here: http://bugs.mysql.com/bug.php?id=12307 Apparently it's been fixed in 5.0.11.. Now documenting. You may want to update your bug to point to mine and close it off ;) The procedure in the bug causes some interesting issues when thinking about security levels also. Basically, it was a test case procedure that I had wrote as an example for my blog (I'm doing kind of a 5.0 new features run on it at the moment) - that does the kind of thing being asked about here. From the security perspective, you can see that SQL SECURITY is set to INVOKER. In other words the procedure will run with all the privileges that the person that calls the procedure has.. This is done because we would pick up on who's running the procedure, to compare against a list of privileged users to make sure that they can actually run the procedure. However, as this is the case - they can't then go and UPDATE the mysql.user table, without direct privileges to it. If you ran it with SQL SECURITY DEFINER, and created the procedure as say, root, then CURRENT_USER() will always return [EMAIL PROTECTED], which would be kind of useless in this context. To run this under SQL SECURITY INVOKER you would have to, at the very minimum, GRANT SELECT (user), UPDATE (password) ON mysql.user TO 'invoker'@'host' To run the FLUSH (which doesn't look like it's going to be allowed anyway), you would also need to GRANT the RELOAD privilege to the user as well. Then - what's to stop the person going an updating the table directly..? ;) Well, to get this to work all you have to do is set the procedure to run as SQL SECURITY DEFINER, and create the procedures with a user that has all of the privileges to run them, drop all of the user checking stuff - then simply GRANT EXECUTE on each procedure to each user that should be allowed to run them.. This way the users will have no direct table level privileges, just very specific PROCEDURE EXECUTE permissions. To add the privileges with the example above would be GRANT EXECUTE ON PROCEDURE admin.update_user_password TO .. to each user that you want to allow to update a password. Hope this helps out a little with the original question as well.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 10 August 2005 09:55 To: mysql@lists.mysql.com Subject: Re: Table and Data access only through procedures Hello. MySQL 5.0.10 offers modifying tables from stored procedures, however I've met some problems. I've tried to create a procedure like your DELETE_USER, but DROP USER seems to accept only literal strings, not variables, while direct modifying of GRANT tables and calling FLUSH privileges leads server to hang. See my bug report: http://bugs.mysql.com/bug.php?id=12485 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 09/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Case issue in Information_schema
Hello, I was just checking INFORMATION_SCHEMA in 5.0.10. All the tables, columns and values are given in CAPS lock. The case is not important in Windows but it is in Linux. Do I assume that even in Linux it will be in CAPS and the case is not going to change in near future? Karam __ Yahoo! Mail for Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQLDUMP and Triggers, Functions and SP
Hello, mysqldump bundled with 5.0.10 does not seem to support the above features. Any plans when they will be supported? Karam __ 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]
mysql 5.0.10 compilation error
Hello, I am trying to compile 5.0.10. While compiling i am getting the following error: e:\mysql5010abetasrc\extra\yassl\src\yassl_imp.cpp(393) : fatal error C1001: INTERNAL COMPILER ERROR (compiler file 'msc1.cpp', line 2701) Please choose the Technical Support command on the Visual C++ Help menu, or open the Technical Support help file for more information and 5-6 more similar to this. I am also getting this one: LINK : fatal error LNK1181: cannot open input file '..\extra\yassl\Debug\yassl.lib' What should I do? Previously, it used to compile without any problem! Karam __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0.10 compilation error
I am trying to compile 5.0.10. While compiling i am getting the following error: e:\mysql5010abetasrc\extra\yassl\src\yassl_imp.cpp(393) : fatal error C1001: INTERNAL COMPILER ERROR (compiler file 'msc1.cpp', line 2701) Please choose the Technical Support command on the Visual C++ Help menu, or open the Technical Support help file for more information and 5-6 more similar to this. I am also getting this one: LINK : fatal error LNK1181: cannot open input file '..\extra\yassl\Debug\yassl.lib' What should I do? Download the binaries? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQLDUMP and Triggers, Functions and SP
mysqldump bundled with 5.0.10 does not seem to support the above features. http://dev.mysql.com/doc/mysql/en/mysqldump.html lists triggers in 5.0.11. I see no mention there of functions or stored procedures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf not found
hi, I tried installing MySQL4.1.13 and in the process I installed the following packages * MySQL-client-4.1.13-0.i386.rpm * MySQL-server-4.1.13-0.i386.rpm * MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm I am able to make connections to mysql and able to do update,insert,select and everything over the database. But i need to turnoff the auto commit option as i am planning to use INNOdb tables, which needs to be edited in the my.cnf file (i donnow if i can change this information anywhere else!!) I looked for this file in the following locations. /etc/ /var/lib/mysql/ but i am not able to find it. please help me if i can do anything for this Thank you sujay
Re: mysql 5.0.10 compilation error
I plan to modify mysqldump and mysql import utility to support SP, Triggers etc and plan to send a patch to the MySQL development tree. Thus downloading the binary does not seem to be option :) --- Martijn Tonies [EMAIL PROTECTED] wrote: I am trying to compile 5.0.10. While compiling i am getting the following error: e:\mysql5010abetasrc\extra\yassl\src\yassl_imp.cpp(393) : fatal error C1001: INTERNAL COMPILER ERROR (compiler file 'msc1.cpp', line 2701) Please choose the Technical Support command on the Visual C++ Help menu, or open the Technical Support help file for more information and 5-6 more similar to this. I am also getting this one: LINK : fatal error LNK1181: cannot open input file '..\extra\yassl\Debug\yassl.lib' What should I do? Download the binaries? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM?]: Case issue in Information_schema
Thx. That answered my question. karam --- Mark Leith [EMAIL PROTECTED] wrote: Hi Karam, I'm not sure what you mean by All the tables, columns and values are given in CAPS lock... Certainly, all of the INFORMATION_SCHEMA tables and their columns are defined in upper case. This probably won't change.. However, any values are stored in the case that they were created in. For example, if you created a table in test called foo, and were to SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'test'; it would return foo - not FOO. HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: 10 August 2005 15:26 To: mysql@lists.mysql.com Subject: [SPAM?]: Case issue in Information_schema Hello, I was just checking INFORMATION_SCHEMA in 5.0.10. All the tables, columns and values are given in CAPS lock. The case is not important in Windows but it is in Linux. Do I assume that even in Linux it will be in CAPS and the case is not going to change in near future? Karam -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 09/08/2005 __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UTF8 support in MySQL 4.0
So how can I do that? I've already tried SET CHARACTER SET and it didn't work since someone said its not available in MySQL 4.0. You can store UTF-8 in any database in the world. UTF-8 is compatible with any application capable of dealing with null-terminated strings of 8-bit characters. That's why it's possible in the Unix/C world, which was designed with null-terminated strings of 8-bit characters in mind. What UTF-8 support gets you is the ability for the database server to do things like Unicode-aware collations and such. But the lack of this feature doesn't prevent you from simply _storing_ UTF-8. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf not found
Sujay Koduri wrote: hi, I tried installing MySQL4.1.13 and in the process I installed the following packages * MySQL-client-4.1.13-0.i386.rpm * MySQL-server-4.1.13-0.i386.rpm * MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm I am able to make connections to mysql and able to do update,insert,select and everything over the database. But i need to turnoff the auto commit option as i am planning to use INNOdb tables, which needs to be edited in the my.cnf file (i donnow if i can change this information anywhere else!!) I looked for this file in the following locations. /etc/ /var/lib/mysql/ but i am not able to find it. please help me if i can do anything for this Thank you sujay my.cnf doesn't exist till you create it. See the manual for more http://dev.mysql.com/doc/mysql/en/option-files.html. You don't need my.cnf to turn off autocommit, though. In fact, you don't even need to turn it off. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT vs SET for creating variables
Bill Dodson wrote: I am using version 4.0.12-nt on a Windows 2000 machine. I have 4.1.11. I have noticed some difference in the way SET and SELECT create variables. The following statements do not work the way I would expect. SELECT @neededStep := 10; SELECT @startOfCenter := 7; SELECT @returnData := IF(@neededStep = @startOfCenter, CONCAT(@neededStep, ' = ', @startOfCenter), CONCAT(@neededStep, ' ', @startOfCenter) ); In the above example @returnData ends up being '10 = 7', but when @neededStep is less than 10 the expected results are found. (could this be because @neededStep is stored as text and not a number?) I get '10 7'. In the next example @returnData ends up being '10 7', as expected. This seems to work for all values of @neededStep I have tested. SET @neededStep := 10; SET @startOfCenter := 7; SELECT @returnData := IF(@neededStep = @startOfCenter, CONCAT(@neededStep, ' = ', @startOfCenter), CONCAT(@neededStep, ' ', @startOfCenter) ); This also gives me '10 7'. It would seem that SET is a better way to create variables from constant values, but I would like to understand why. Does anybody know what is happening here? I expect it's a bug which has since been fixed (though I didn't check the bugs db to be sure). You are using a very old version of mysql -- 4.0.12 was released in March, 2003. The current version in the 4.0.x series is 4.0.25. You can read the rather impressive list of bugs fixed since 4.0.12 in the manual http://dev.mysql.com/doc/mysql/en/news-4-0-x.html. I'd suggest upgrading. Thanks for your time! bill Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT vs SET for creating variables
Bill, SELECT @neededStep := 10; SELECT @startOfCenter := 7; SELECT @returnData := IF(@neededStep = @startOfCenter, CONCAT(@neededStep, ' = ', @startOfCenter), CONCAT(@neededStep, ' ', @startOfCenter) ); In the above example @returnData ends up being '10 = 7', but when @neededStep is less than 10 the expected results are found. (could this be because @neededStep is stored as text and not a number?) In 5.0.10, @returndata is '10 7'; PB - Bill Dodson wrote: I am using version 4.0.12-nt on a Windows 2000 machine. I have noticed some difference in the way SET and SELECT create variables. The following statements do not work the way I would expect. SELECT @neededStep := 10; SELECT @startOfCenter := 7; SELECT @returnData := IF(@neededStep = @startOfCenter, CONCAT(@neededStep, ' = ', @startOfCenter), CONCAT(@neededStep, ' ', @startOfCenter) ); In the above example @returnData ends up being '10 = 7', but when @neededStep is less than 10 the expected results are found. (could this be because @neededStep is stored as text and not a number?) In the next example @returnData ends up being '10 7', as expected. This seems to work for all values of @neededStep I have tested. SET @neededStep := 10; SET @startOfCenter := 7; SELECT @returnData := IF(@neededStep = @startOfCenter, CONCAT(@neededStep, ' = ', @startOfCenter), CONCAT(@neededStep, ' ', @startOfCenter) ); It would seem that SET is a better way to create variables from constant values, but I would like to understand why. Does anybody know what is happening here? Thanks for your time! bill -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 8/9/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index in the desc order
I am not able to create index with the DESC flag on it. - javabuddy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.cnf not found
Thanks a lot stassen :) sujay -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 10, 2005 9:50 PM To: Sujay Koduri Cc: MySQL mailing list Subject: Re: my.cnf not found Sujay Koduri wrote: hi, I tried installing MySQL4.1.13 and in the process I installed the following packages * MySQL-client-4.1.13-0.i386.rpm * MySQL-server-4.1.13-0.i386.rpm * MySQL-shared-standard-4.1.13-0.rhel3.i386.rpm I am able to make connections to mysql and able to do update,insert,select and everything over the database. But i need to turnoff the auto commit option as i am planning to use INNOdb tables, which needs to be edited in the my.cnf file (i donnow if i can change this information anywhere else!!) I looked for this file in the following locations. /etc/ /var/lib/mysql/ but i am not able to find it. please help me if i can do anything for this Thank you sujay my.cnf doesn't exist till you create it. See the manual for more http://dev.mysql.com/doc/mysql/en/option-files.html. You don't need my.cnf to turn off autocommit, though. In fact, you don't even need to turn it off. See the manual for details http://dev.mysql.com/doc/mysql/en/innodb-and-autocommit.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index in the desc order
Gana wrote: I am not able to create index with the DESC flag on it. - javabuddy. Hassan Schroeder answered this exact question just yesterday http://lists.mysql.com/mysql/187582. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Index with DESC not working on v5
asdas wrote:
It could be a known issue, but thought it's a well known well used
feature so, asking you all.
I am trying to create an index with the following command -
create index an_idx on atable (acol desc, bcol, ccol)
Then when I do a -
show index from atable
It shows for all indexed columns the 'Collation' is 'A'. And also when we
do a query without any ORDER BY, the result is not sorted in descending
order on acol. For example a query like
select pk, acol, bcol, ccol from atable
doesn't show the result in descending order of acol, instead it's in
ascending order of acol.
Is there a simple trick I am missing ?
I am using MySQL version 5.0.3.
Thanks in advance.
Hassan Schroeder wrote:
Note the last line of this excerpt from The Fine Manual :-)
http://dev.mysql.com/doc/mysql/en/create-index.html
An index_col_name specification can end with ASC or DESC. These keywords
are allowed for future extensions for specifying ascending or descending
index value storage. Currently they are parsed but ignored; index values
are always stored in ascending order.
HTH!
Also, you cannot expect your results to be ordered wihout an explicit ORDER BY
clause. Without ORDER BY, mysql is free to return rows in any order it
chooses (generally in the order they are found). For example:
CREATE TABLE atable (pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
acol CHAR(3), bcol INT, ccol CHAR(3),
INDEX an_idx (acol, bcol, ccol)
);
INSERT INTO atable (acol, bcol, ccol)
VALUES ('aaa',1,'aaa'), ('bbb',1,'aaa'), ('aaa',3,'aaa'),
('aaa',2,'abc'), ('aaa',1,'bbb'), ('bbb',3,'abc'),
('aaa',2,'aaa'), ('aaa',1,'abc'), ('abc',3,'def'),
('bbb',2,'aaa');
DELETE FROM atable WHERE bcol = 3;
INSERT INTO atable (acol, bcol, ccol)
VALUES ('ccc',1,'aaa'), ('ccc',2,'aaa'), ('ccc',1,'bbb');
SELECT pk, acol, bcol, ccol FROM atable;
++--+--+--+
| pk | acol | bcol | ccol |
++--+--+--+
| 1 | aaa |1 | aaa |
| 2 | bbb |1 | aaa |
| 13 | ccc |1 | bbb |
| 4 | aaa |2 | abc |
| 5 | aaa |1 | bbb |
| 12 | ccc |2 | aaa |
| 7 | aaa |2 | aaa |
| 8 | aaa |1 | abc |
| 11 | ccc |1 | aaa |
| 10 | bbb |2 | aaa |
++--+--+--+
10 rows in set (0.00 sec)
You see? Without ORDER BY, the rows are returned as they are found. Note
that rows from the second INSERT are where the deleted rows were.
An exception may occur if you only ask for columns which are covered by a
single index. In that case, mysql may choose to read the data from the
covering index, rather than from the table. Then you'd get results in index
order:
mysql SELECT pk FROM atable;
++
| pk |
++
| 1 |
| 2 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
| 11 |
| 12 |
| 13 |
++
10 rows in set (0.00 sec)
mysql SELECT acol, bcol, ccol FROM atable;
+--+--+--+
| acol | bcol | ccol |
+--+--+--+
| aaa |1 | aaa |
| aaa |1 | abc |
| aaa |1 | bbb |
| aaa |2 | aaa |
| aaa |2 | abc |
| bbb |1 | aaa |
| bbb |2 | aaa |
| ccc |1 | aaa |
| ccc |1 | bbb |
| ccc |2 | aaa |
+--+--+--+
10 rows in set (0.00 sec)
I wouldn't count on this though. If you need ordered results, you need ORDER
BY.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Index with DESC not working on v5
Thanks a lot Michael, it sure helps. People are conversing... without posting their email or filling up their mail box. ~~1123696760414~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MSSQL to MySQL mapping
We have a web based application running on IIS 5.0 using MS SQL Server 2000 as the DBMS; we use ODBC to connect to the DB. We migrated our DB to MySQL and used the MySQL ODBC driver. It appears that some of the SQL statements that are accepted by SQL Server 2000 are not accepted by MySQL. Is there a mapping from MSSQL to MySQL statements? Thank you John C. _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MSSQL to MySQL
We have a web application that uses SQL statements and interacts via an ODBC interface to MS SQL Server. When we changed the DBMS to MySQL some of the SQL statements are not accepted by MySQL. Is there a mapping between MSSQL to MySQL statements? Thank you John C. _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleanup of connections
Hello. MySQL usually waits some time for data from client even if connection is broken, because MySQL could no nothing about it. The time depends of the socket implementation of your OS (for example for TCP, connection can be marked as established even if the other side is down for several seconds). Also different xxx_timeout variables are influenced as well. than 2 minutes. I was just wondering if this would leave the connection open in MySQL. Yes, in my opinion, this could be the cause of your problem. Ben Smith [EMAIL PROTECTED] wrote: I've found out a little more. There is a PHP script that is run from a cron job which goes off and gets data from a 3rd party. Generally this completes just fine but there is another job, a cron job, that Kills this script/connection if it takes more than 2 minutes. I was just wondering if this would leave the connection open in MySQL. -- 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]
help with slow query
this query runs slow because AVG and COUNT on maps_rating table i think. can anything be done to improve? query: SELECT maps.*, AVG(maps_rating.rating) AS rating, COUNT(maps_rating.id) AS votes, user.username FROM maps LEFT JOIN maps_rating ON (maps.id = maps_rating.map) LEFT JOIN user ON (user.userid = maps.userid) GROUP BY maps.id ORDER BY maps.dateline DESC LIMIT $start, $perpage structure: maps - rows: 700 +--++-+---+++ | id | mip | map | userid | author | filename | +--++-+---+++ maps_rating - rows: 2,000 +--+-+--++---+---+ | id | map | rating | userid | ipaddress | dateline | +--+-+--++---+---+ user - rows: 10,000 +--+---+ | userid | username | +--+---+ -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 8/9/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OR in Query String
Hi List, I have the following query where I am trying to locate a single students record. I only know that the students record has an id of 3690 and an employer_id of 3 possibles. So I thought that OR would work great. The problem is that it returns all students with employer_id's of 3, 78, 79. How do I construct the query correctly to accomplish what I am after, without only using the student_id? I need to verify and return the employer_id. SELECT student_id, employer_id FROM wat_student_job WHERE student_id = 3690 AND employer_id = 3 OR employer_id = 78 OR employer_id = 79 Thanks in advance. Regards, Justin Palmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OR in Query String
Justin Palmer wrote: Hi List, I have the following query where I am trying to locate a single students record. I only know that the students record has an id of 3690 and an employer_id of 3 possibles. So I thought that OR would work great. The problem is that it returns all students with employer_id's of 3, 78, 79. How do I construct the query correctly to accomplish what I am after, without only using the student_id? I need to verify and return the employer_id. SELECT student_id, employer_id FROM wat_student_job WHERE student_id = 3690 AND employer_id = 3 OR employer_id = 78 OR employer_id = 79 SELECT student_id, employer_id FROM wat_student_job WHERE student_id = 3690 AND ( employer_id = 3 OR employer_id = 78 OR employer_id = 79 ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OR in Query String
Justin Palmer wrote: Hi List, I have the following query where I am trying to locate a single students record. I only know that the students record has an id of 3690 and an employer_id of 3 possibles. So I thought that OR would work great. The problem is that it returns all students with employer_id's of 3, 78, 79. How do I construct the query correctly to accomplish what I am after, without only using the student_id? I need to verify and return the employer_id. SELECT student_id, employer_id FROM wat_student_job WHERE student_id = 3690 AND employer_id = 3 OR employer_id = 78 OR employer_id = 79 Thanks in advance. Regards, Justin Palmer an IN list may be what you want here WHERE student_id = 3690 AND employer_id IN (3, 78, 79); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL C API Version incompatibility
hi, I was connecting to MySQL 5.0.4 through the C API and it was workign fine. But when I downgraded to MYSQL 4.1.13, the same code is giving the following error. mysql_stmt_bind_result() failed Using unsupported buffer type: 0 (parameter: 1) I tried uninstalling all the existing mysql rpm's and reinstall them again. But this didnt help. Please help on this . Thank you sujay
Re: Upgrade from 4.018 to 4.1.13 ?
Are you running the stock Apple MySQL install?? I went through this a few times. If so figure out where the default Apple install places the data dir. The MySQL pkg installers will install everything in: /usr/local/mysql/data/ where: /usr/local/mysql/ is really a symbolic link to each incremental MySQL install. So if you install 4.1.13 you will have something like this... /usr/local/mysql - mysql-standard-4.1.13-apple-darwin7.9.0-powerpc /usr/local/mysql-standard-4.1.13-apple-darwin7.9.0-powerpc /usr/local/mysql-standard-4.1.12-apple-darwin7.9.0-powerpc etc.. So each time you updgrade MySQL you need to manually move the data from dir to dir. I stopped this by creating a my.cf file and specifying a path for my data like /usr/local/data/mysql or /var/data/ mysql/ The upgrades are painless after that. Now that being said there is a Apple tech note: http://docs.info.apple.com/article.html?artnum=107852 on MySQL upgrades. To be safe you should dump your data as noted, upgrade (w/ the my.cf file added) and re-insert your data fresh. Dan T On Aug 9, 2005, at 9:21 PM, m i l e s wrote: Hi, I have a silly questionDUH Im running OS X Server, I installed 4.018 and I want to run 4.1.13, how do I upgrade the server, without losing anything ? Do I just install over it ? Of course backing up all the tables and what not first ? Any pointers would be helpful. Thanks. M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
long text insert failure
Dear Guys,
Can please anyone advice me how to successfully insert long text data
into my innodb table rawlog with table stucture as follows:
mysql desc rawlog;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| log | longtext | YES | | NULL| |
+---+--+--+-+-+---+
1 row in set (0.03 sec)
here is one sample of the syslog data I'm trying to insert into the
table without success:
insert into rawlog values ('133date=2005-07-25 time=12:38:23
device_id=FGT1002105200379 log_id=0022010001 type=traffic
subtype=allowed pri=notice vd=root SN=1321 duration=180 policyid=1
proto=17 service=29716/udp status=accept src=192.168.2.63
srcname=192.168.2.63 dst=193.11.28.37 dstname=193.11.28.37
src_int=internal dst_int=external sent=46 rcvd=86 sent_pkt=1
rcvd_pkt=1 src_port=10055 dst_port=29716 vpn=n/a tran_ip=202.189.48.98
tran_port=43957 dir_disp=org tran_disp=noop');
I've already tried changing my column data type from varchar to text
types but still the insert statement cannot be typed all the way to
the end of statement.I can only type half of the syslog data. Does it
has something to do
with my system? I'm using mysql version 4.1.11 running in windows xp
32 bit. My max_allowed_packet is 1048576, do I need to increase this?
by how much?
Please advice me as a need to insert lots of long syslog data in this table.
All the best.
Elizabeth
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MSSQL to MySQL mapping
John c wrote: We have a web based application running on IIS 5.0 using MS SQL Server 2000 as the DBMS; we use ODBC to connect to the DB. We migrated our DB to MySQL and used the MySQL ODBC driver. It appears that some of the SQL statements that are accepted by SQL Server 2000 are not accepted by MySQL. Is there a mapping from MSSQL to MySQL statements? Not that I know of, but that would be a good idea. If you can't find the function that you're after in the docs, you can always post to the list for help ... and maybe compile that list of mappings while you're at it :) -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot start mysqld
I installed mysql from the following rpm packages: mysql-4.1.10a-2.RHEL4.1 mysql-server-4.1.10a-2.RHEL4.1 I tried running 'mysqld start' and the server said startup failed. Here is the mysq.log: 050810 20:26:48 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 050810 20:26:49 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 050810 20:26:50 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 050810 20:26:51 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 050810 20:26:52 InnoDB: Started; log sequence number 0 0 050810 20:26:52 [ERROR] Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050810 20:26:52 mysqld ended Appreciate any help with this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
