Improvement of load_file() function.
MySQL provides an internal function named load_file(). It reads the file saved on the host server and returns the contents of the file as a string. This is an appoach to make users can read the file on the host server directly by using database. But I think this function can be improved further. Firstly, load_file() only returns the whole contents of the file. We need a large buffer(memory) to store it if the file is very large. The file will be opened, read and closed once we use this file. Secondly, if we want to read a file on the host server, the only way provided by mysql is load_file(). But I know Oracle provides a filed type named bfile, a pointer to the file and we can use this field to read the file directly. In the customers' opinion, they also have this kind of requirement. So we want to add a field type representing a file on the server host for mysql and make users can read the file conveniently. We can directly use this field to read the whole or a part of the file. Do you agree us and have any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using mysqlbinlog with tables with binary data (blobs)
We have had some cases where we wanted to recover data using the binlogs from some days ago. The database has a lot of binary objects (blobs). The normal way to recover are to restore database files from a consistant point, and then use mysqlbinlog to spool from that point till where we want to end. mysqlbinlog only produces text sql output. This means it is useless for databases with blobs. How can we spool data from our binary logs? mysqldump has an option --hex-blob. I found an old feature request from 2005, asking to add a similar feature to mysqlbinlog. I found no followups to the request. Is this doable? Ingvar -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem
We have a windows server running our main database and an IIS server. We also have a linux box with apache/php that uses the same database server. We have configured a replication from that host to another windows server. Then we verified that not all the queries were replicated properly. We find out that some queries that come from the linux box and have cr/lf on them are not being replicated. ie: sql = select * from table_1; I think that may be some issues about how linux manages cr/lf. Whan can I do? thank you all! _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview
Re: user permissions to all DB
solidzh wrote: 2007/8/21, Jay Pipes [EMAIL PROTECTED]: Terry wrote: Hello, I want to grant a user all permissions to all DBs on the system as well as any new DBs that show up. I want to avoid having to modify permissions everytime a new DB is added. Is there a way to do this? GRANT SELECT, INSERT, CREATE, ... ON *.* TO 'username'@'hostname' IDENTIFIED BY 'password'; That's well but why not, grant all on *.* to 'user'@'host' identified by 'pwd'; ? Because then you give the user SUPER, FILE, ALTER, SHUTDOWN, and PROCESS privileges, which probably isn't a good idea... :) Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problem
Hi, Jorge Martínez wrote: We have a windows server running our main database and an IIS server. We also have a linux box with apache/php that uses the same database server. We have configured a replication from that host to another windows server. Then we verified that not all the queries were replicated properly. We find out that some queries that come from the linux box and have cr/lf on them are not being replicated. ie: sql = select * from table_1; SELECT queries are not replicated. Only queries that change data are replicated. I think that may be some issues about how linux manages cr/lf. Whan can I do? thank you all! _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication problem
I'm sorry. I have posted a wrong example. Here you have a better one: update table_1 \n set field_1 = 'xx', \n field_2 = 'yy'; \n Date: Tue, 21 Aug 2007 11:16:50 -0400 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Replication problem Hi, Jorge Martínez wrote: We have a windows server running our main database and an IIS server. We also have a linux box with apache/php that uses the same database server.We have configured a replication from that host to another windows server. Then we verified that not all the queries were replicated properly. We find out that some queries that come from the linux box and have cr/lf on them are not being replicated. ie: sql = select * from table_1; SELECT queries are not replicated. Only queries that change data are replicated. I think that may be some issues about how linux manages cr/lf. Whan can I do?thank you all! _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Llama a tus amigos de PC a PC: ¡Es GRATIS! http://get.live.com/messenger/overview
A select for a game ranking page.
Hi I have a table with: player_name, top_score, number_of_plays When I list them out I ORDER BY top_score DESC, number_of_plays DESC, player_name to help give some sort of order to the people with the same scores. What I would like to do is find out a players position without looping through all the records, so my plan was to do a SELECT count(*) and have WHERE top_score the players top score.. however when there are many scores the same I want to also do WHERE number_of_plays the players number of plays. Doing WHERE top_score 1000 AND number_of_plays 10 is no good as some players have higher scores but lower plays but should be counted as been higher ranked. I don't want to loop through the scores, that's not very elegant. Also creating a temp table where the scores are in order and then counting on that would also be overkill? I hope this makes sense and that there is a solution. -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A select for a game ranking page.
Hi, Critters wrote: Hi I have a table with: player_name, top_score, number_of_plays When I list them out I ORDER BY top_score DESC, number_of_plays DESC, player_name to help give some sort of order to the people with the same scores. What I would like to do is find out a players position without looping through all the records, so my plan was to do a SELECT count(*) and have WHERE top_score the players top score.. however when there are many scores the same I want to also do WHERE number_of_plays the players number of plays. Doing WHERE top_score 1000 AND number_of_plays 10 is no good as some players have higher scores but lower plays but should be counted as been higher ranked. I don't want to loop through the scores, that's not very elegant. Also creating a temp table where the scores are in order and then counting on that would also be overkill? I hope this makes sense and that there is a solution. This is a common problem with ranked data. It seems to be exactly the topic I wrote an O'Reilly article on: http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored proc question
Hi all, My basic question is, is there a simple way of finding out if a select statement executed within a stored proc returns something. I could go ahead, do the fetch (the query is done with a cursor) and count how many records were returned but looping over the result. In below proc the variable cnt serves this purpose. Is there an easier way to do this, like a cursor property or so? Thanks Olaf DELIMITER // DROP PROCEDURE IF EXISTS rfg2// CREATE PROCEDURE rfg2() READS SQL DATA BEGIN DECLARE idd INT; DECLARE cnt INT; DECLARE genos_done INT DEFAULT 0; DECLARE genos CURSOR FOR select a1 from geno_cidr_raw where ident=28; DECLARE CONTINUE HANDLER FOR NOT FOUND SET genos_done=1; SET cnt=0; geno_block: BEGIN OPEN genos; REPEAT FETCH genos INTO idd; IF genos_done=0 THEN SET cnt=cnt+1; END IF; UNTIL genos_done END REPEAT; CLOSE genos; END geno_block; SELECT cnt; END// DELIMITER ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is the average of an int column returned as a string
Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED]
Re: Why is the average of an int column returned as a string
Eric Lommatsch wrote: Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? I'm a little unclear where they're being returned as strings, and how you know they're strings and not floats. I think the math is done with floats, so even if your columns are floating-point, you'll get the same results. But in general, you can use CAST(), though CAST-ing to a floating-point isn't supported. I don't know why not. SELECT CAST(AVG(col) AS DECIMAL(9,2)... Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied for user on Windows Server
I have a MySql server running on a Windows box that I cannot connect to from another machine. I first checked to make sure the user has the correct permissions, which the user does, it has the %. I did read something that gave me the impression the user needs to have a tranditional windows account on the machine, so I created one. Still nothing. Is there anything special I need to do to allow access to MySql when the database is running on a different machine? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user on Windows Server
More info... I am able to login with the account via phpMyAdmin on the server. Also, I am trying to use phpMyAdmin to change the permissions, but I cannot figure out the syntax: GRANT ON ppsntracker.* TO [EMAIL PROTECTED] WITH GRANT OPTION; Cartoper On 8/21/07, Car Toper [EMAIL PROTECTED] wrote: I have a MySql server running on a Windows box that I cannot connect to from another machine. I first checked to make sure the user has the correct permissions, which the user does, it has the %. I did read something that gave me the impression the user needs to have a tranditional windows account on the machine, so I created one. Still nothing. Is there anything special I need to do to allow access to MySql when the database is running on a different machine? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is the average of an int column returned as a string
In the last episode (Aug 21), Eric Lommatsch said: I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? They get created as decimals for me. It's possible you've run into an already-fixed bug. I suggest upgrading to 5.0.45 and see if the problem is still there. Note that 5.0.18 is almost two years old and later versions have fixed literally hundreds of issues. http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html mysql create table test1 (num integer); Query OK, 0 rows affected (0.05 sec) mysql insert into test1 values (1),(2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql create table test2 as select avg(num) from test1; Query OK, 1 row affected (0.08 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql desc test2; +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | avg(num) | decimal(14,4) | YES | | NULL| | +--+---+--+-+-+---+ 1 row in set (0.02 sec) mysql select version(); +--+ | version()| +--+ | 5.0.45-debug-log | +--+ 1 row in set (0.00 sec) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is the average of an int column returned as a string
At 8:25 PM -0400 8/21/07, Baron Schwartz wrote: Eric Lommatsch wrote: Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? I'm a little unclear where they're being returned as strings, and how you know they're strings and not floats. I think the math is done with floats, so even if your columns are floating-point, you'll get the same results. But in general, you can use CAST(), though CAST-ing to a floating-point isn't supported. I don't know why not. SELECT CAST(AVG(col) AS DECIMAL(9,2)... Eric, are you using the C API binary (prepared statement) protocol? If so, even if the values are DECIMAL, they'll be returned in string form: DECIMAL values are returned as strings, which is why the corresponding C type is char[]. DECIMAL values returned by the server correspond to the string representation of the original server-side value. For example, 12.345 is returned to the client as '12.345'. If you specify MYSQL_TYPE_NEWDECIMAL and bind a string buffer to the MYSQL_BIND structure, mysql_stmt_fetch() stores the value in the buffer without conversion. If instead you specify a numeric variable and type code, mysql_stmt_fetch() converts the string-format DECIMAL value to numeric form. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query error
Hi, I'm trying to execute this query: SELECT group_post.group_thread_id, FROM group_post LEFT OUTER JOIN group_post_moderation ON (group_post.group_post_id = group_post_moderation.group_post_id) LEFT OUTER JOIN group_post_mod_option ON (group_post_moderation.group_post_moderation_option = group_post_mod_option.option_id) WHERE group_thread_id = '6' GROUP BY group_post.group_thread_id ORDER BY lft; But when I do, I get this error: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right sy ntax to use near 'FROM group_post LEFT OUTER JOIN group_post_moderation ON (grou Can anyone please tell me what is causing that? I'm using MySQL4. Thanks, - Naz. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to limit word result by query
Hi all, Is it possible to do query which limiting the result into some words only? i.e the complete sentence is I am able to login with the account and I just want to view I am able to login... Many thanks for any reply. Regards, Willy -- www.sangprabv.web.id www.binbit.co.id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
