Re: Oracle query..
Renish wrote: Hello, Sorry. Again u r wrong.. Thers no connection with MYSQL here. Well you're *very* much in the wrong list. Why are you posting to a MySQL list if your question has nothing to do with MySQL? You do realise there are Oracle lists, don't you? Surely people there will be able to give you more accurate advice than people here? -- 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]
Problem with query on 5.11
Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? I'm using MySQL 5.1.11. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: £ (Great Britain Pound) breaks MySQL insert an d update - disastrous overwrite
Paul,
I have a Java Struts application running on Tomcat 5. I have
mysql 5 installed on redhat EL 3. When a user enters text
with a £ sign (Great Britain Pound) in the browser and clicks
enter, any insert or update statement apparently gets
truncated in mysql. The insert reliably breaks and returns
this error:
db exception thrown: java.sql.SQLException: Syntax error or
access violation message from server: You have an error in
your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '' at line 1
[...]
I'm not familiar with Java, but when I create webapplications (using Perl), I
use prepared queries with placeholders to manipulate data that is coming from a
(basically unreliable) source like a user interface. In that way, I don't have
to worry about quoting special characters, etcetera, the database driver takes
care of that.
It works like this (i.e. in the Perl DBI, and skipping any error trapping for
clarity, $dbh is database handle, $sth is statement handle):
# Prepare query w/ placeholders
$sth = $dbh-prepare('insert into advert (vacancyid, adverttitle, mainbody,
forfurtherinfo, finalcopy, instructionsforagency, created) values
(?,?,?,?,?,?,?)');
# Execute query
$sth-execute(884, 'test3', 'Computer Officer (Part-time) blabla','' ,'h3TEST
JOB/h3
blabla £24,161-£31,525 blabla', '', now());
As I said, I'm not in the know about Java, but you may want to check out if
something like this is possible, and if it solves your issue.
Regards,
Martijn
--
The information contained in this communication and any attachments is
confidential
and may be privileged, and is for the sole use of the intended recipient(s). Any
unauthorized review, use, disclosure or distribution is prohibited. If you are
not
the intended recipient, please notify the sender immediately by replying to this
message and destroy all copies of this message and any attachments. ASML is
neither
liable for the proper and complete transmission of the information contained in
this
communication, nor for any delay in its receipt.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle query..
Thanks dear. i thought u could be of some use in Oracle aswelll - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Renish [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, October 20, 2006 1:59 PM Subject: Re: Oracle query.. Renish wrote: Hello, Sorry. Again u r wrong.. Thers no connection with MYSQL here. Well you're *very* much in the wrong list. Why are you posting to a MySQL list if your question has nothing to do with MySQL? You do realise there are Oracle lists, don't you? Surely people there will be able to give you more accurate advice than people here? -- 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]
RE: Deletes on big tables
Thanks Chris for your input. I have no redundant indexes but of course I will double check. I will look into making smaller tables. Marvin -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: 19 October 2006 23:52 To: Marvin Wright Cc: mysql@lists.mysql.com Subject: Re: Deletes on big tables 2.If I could split the tables up into smaller tables would this help ? My dilemma here is that I can split the data, the data would be in different tables but on the same hardware, the same number of deletes would still have to happen so would it actually make any difference ? No idea about the rest but yes you'll get better performance with smaller tables. What indexes do you have on the table(s) ? Hopefully no redundant or duplicate ones. Deletes have to update all indexes on the table at the same time - the less data, the smaller the index(es), the quicker the response. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Deletes on big tables
Thanks for your input. Currently I am managing the foreign key links in my application and not within the database so this is a possibility to look into. A stored procedure is also a good idea, however I must upgrade my MySQL version to = 5.0. I am currently running an optimize on all tables each weekend so this is already taken care of. In your opinion would moving to a 64-bit OS and being able to use the memory better give a better performance increase ? Again, thanks for your help. Marvin. -Original Message- From: nigel wood [mailto:[EMAIL PROTECTED] Sent: 20 October 2006 00:02 To: Marvin Wright Cc: mysql@lists.mysql.com Subject: Re: Deletes on big tables Marvin Wright wrote: I have 3 tables where I keep cache records, the structures are something like TableA is a 1 to many on TableB which is a 1 to many on TableC To give you an idea of size, TableA has 8,686,769 rows, TableB has 5,6322,236 rows and TableC has 1,089,635,551 rows. My expiry runs on a daily basis and deletes approximately this number of rows from each table. TableA500,000 TableB 4,836,560 TableC 71,534,549 My suggestions: 1) Add an (expirydate,primary key) composite index on table A and make sure your foreign keys are in place 2) Turn on cascading deletes for these three tables or (less optimally) use a single multi-table delete in a stored procedure to delete lots of rows (in A) per query not singles 3) run the delete query with a limit of 1 rows or so in a transaction, use show innodb status to monitor how much rollback space the innodb engine has left and up the number rows if possible. 4) If your data integrity can take the risk turn off innodb's flush on commit for this connection during the deletes. 5) Run optimise table once a week, if your deleting lots of records for a while the database is probably fragmented. If nessasary for availablity use a DR slave/master and run optimise table on the DR pair before promoting them to live. Then look at the memory. If you cant upgrade the OS to use all that memory (we do on 32 bit servers) at least create a huge ram disk and tell MySQL to use it as temporary storage. Hope that helps Nigel -- 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: MySQL 5.1
Sid Lane wrote: any update on the 5.1 general release date? is it still on target for Q4 - Q1? any narrower window? Last I heard, Q2 next year. Regards --jm On 8/30/06, Colin Charles [EMAIL PROTECTED] wrote: Logan, David (SST - Adelaide) wrote: Hi! Does anybody have any idea when 5.1 may come to General Release? I am particularly interested in MySQL Cluster as I have several databases (around 50) totalling 26Gb and would like to consider moving to this version because of the Cluster Disk Data Storage and the fact the current hardware doesn't have the RAM requirements to hold these databases. I expect late (Q4/2006) to early (Q1/2007) as a good time for release. I plan to store the data on a SAN and was wondering if anybody had any experience with this as yet? This is why you're after 5.1, so you get disk as opposed to memory only based storage then -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: £ (Great Britain Pound) breaks MySQL insert and update - disastrous overwrit e
Paul Warner wrote: When a user enters text with a £ sign (Great Britain Pound) in the browser and clicks enter, any insert or update statement apparently gets truncated in mysql. It's possible that somewhere along the line, the character is getting translated to a multibyte Unicode format. (UTF-16 or -32, most likely.) The hex value for the pound sign is 00A3, which includes a null character, which the MySQL C API will interpret as end-of-string. You either need to escape that null (which isn't really the right thing...this isn't a BLOB) or explicitly convert it to UTF-8. UTF-8 is an encoding explicitly made to work around this limitation of C, which is why MySQL likes it. And since you want to accept a wider character support, you should set the web server and database to also use UTF-8. It's the right way to handle such characters. ISO 8859 is a backwards compatibility hack to allow ancient technologies to support one of several subsets of the world's characters. What you really want is to support them all, transparently. That's Unicode. See http://www.fileformat.info/info/unicode/char/00a3/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: £ (Great Britain Pound) breaks MySQL insert and update - disastrous overwrit e
Warren Young wrote:
Paul Warner wrote:
When a user enters text with a £ sign (Great Britain Pound) in the
browser and clicks enter, any insert or update statement apparently
gets truncated in mysql.
It's possible that somewhere along the line, the character is getting
translated to a multibyte Unicode format. (UTF-16 or -32, most
likely.) The hex value for the pound sign is 00A3, which includes a
null character, which the MySQL C API will interpret as end-of-string.
From my own experience I'd point out that if your using a terminal to
do something like SSH, that too needs to be set to the correct encoding
as well. Data being imported (be that any method, even cutting and
pasting) can also be an area of strife, I suffered greatly when when
producing a copious country and region listing system with everything in
the correct character sets for a website that had up until then been
ISO-{whatever}.
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: £ (Great Britain Pound) breaks MySQL insert and update - disastrous overwrit e
Warren Young wrote:
Paul Warner wrote:
When a user enters text with a £ sign (Great Britain Pound) in the
browser and clicks enter, any insert or update statement apparently
gets truncated in mysql.
It's possible that somewhere along the line, the character is getting
translated to a multibyte Unicode format. (UTF-16 or -32, most
likely.) The hex value for the pound sign is 00A3, which includes a
null character, which the MySQL C API will interpret as end-of-string.
From my own experience I'd point out that if your using a terminal to
do something like SSH, that too needs to be set to the correct encoding
as well. Data being imported (be that any method, even cutting and
pasting) can also be an area of strife, I suffered greatly when when
producing a copious country and region listing system with everything in
the correct character sets for a website that had up until then been
ISO-{whatever}.
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
Is 9640 a word by itself? A full-text search wouldn't find abc9640, you'd need to use LIKE for that. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Don O'Neil [mailto:[EMAIL PROTECTED] Sent: Friday, October 20, 2006 2:44 AM To: mysql@lists.mysql.com Subject: Problem with query on 5.11 Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? I'm using MySQL 5.1.11. Thanks! -- 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]
Reports
Hi everyone do you know any good (free) application that make it easy to generate (with charts) a very nice reports from mysql ? Thanks --- Ahmad http://www.v-tadawul.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reports
-Original Message- From: Ahmad Al-Twaijiry [mailto:[EMAIL PROTECTED] Sent: Friday, October 20, 2006 10:00 AM To: MySQL List Subject: Reports Hi everyone do you know any good (free) application that make it easy to generate (with charts) a very nice reports from mysql ? This is a very good tool. I don't use the charts features, so I'm not sure how advanced those are. http://jasperforge.org/sf/projects/ireport -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Jerry, Is 9640 a word by itself? A full-text search wouldn't find abc9640, No a full text search would find numbers pretending to be a word, the full text search has a fairly high level definition of a word. Try searching for 1960 over at http://ftvdb.bfi.org.uk/search.php. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with query on 5.11
Jerry, Is 9640 a word by itself? A full-text search wouldn't find abc9640, No a full text search would find numbers pretending to be a word, the full text search has a fairly high level definition of a word. Try searching for 1960 over at http://ftvdb.bfi.org.uk/search.php. Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: £ (Great Britain Pound) breaks MySQL insert a nd update - disastrous overwrite - FIXED
Thanks, Warren and Phil for your responses. And Martijn.
Yesterday I went through all my code and tomcat/web application configuration
settings, and converted all to UTF-8. I converted the database to UTF-8.
According to all the books and articles I've read, I have set every setting to
use UTF-8. Still the inserts and updates were breaking. Gerald Clark suggested
I turn query logging on, and I did.
This showed that the query, in mysql, was truncated when it contained a £ sign,
and perfectly fine when it didn't. It was perfect when I handed it to the
MySQL JDBC connector, but broken when it got to mysql. Anything without a £
sign was fine on both sides. And if I gave the query to mysql via the command
line interface, or via perl, it was fine. It didn't seem to be in my app, nor
in mysql itself. It seemed to be the connector!
So I looked and was surprised to find I was using
mysql-connector-java-3.13-beta-bin.jar. I just upgraded to
mysql-connector-java-5.0.3-bin.jar and that has fixed the problem! Why was I
using a beta version? I do not know.
Thanks, everyone, for your help! I hope the next person who runs into this
problem benefits from this thread.
Regards,
Paul
- Original Message
From: Philip Mather [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Friday, October 20, 2006 2:24:18 PM
Subject: Re:£ (Great Britain Pound) breaks MySQL insert and update - disastrous
overwrite
Warren Young wrote:
Paul Warner wrote:
When a user enters text with a £ sign (Great Britain Pound) in the
browser and clicks enter, any insert or update statement apparently
gets truncated in mysql.
It's possible that somewhere along the line, the character is getting
translated to a multibyte Unicode format. (UTF-16 or -32, most
likely.) The hex value for the pound sign is 00A3, which includes a
null character, which the MySQL C API will interpret as end-of-string.
From my own experience I'd point out that if your using a terminal to
do something like SSH, that too needs to be set to the correct encoding
as well. Data being imported (be that any method, even cutting and
pasting) can also be an area of strife, I suffered greatly when when
producing a copious country and region listing system with everything in
the correct character sets for a website that had up until then been
ISO-{whatever}.
Regards,
Phil
--
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: delimiter
In my tests here i saw something strange. I tried to run my .sql file in the mysql prompt and it works fine. When i change my data structure and tried to run again, it gives me again the old error in create trigger (it was not changed). Then i copy whole code of creating triggers from my .sql file and past it to mysql prompt and it works fine. My system admin told me that it could be a mysql bug. Someon here know something about that? João Cândido de Souza Neto [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I tried to escape the pipe character but it does not work. I shall try to explain better what is happening. I have got a .sql file which create my whole database (including triggers). When i run it at phpmyadmin it fails, then i tried to run a part of my .sql file which creates a trigger in a mysql prompt and it works fine. Is there some trouble using the delimiter comand by a php script? Dan Buettner [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] João, the pipe character | often needs to be escaped, usually with a backslash like so: \| Can you post the command you are running and the error you get? Dan On 10/19/06, João Cândido de Souza Neto [EMAIL PROTECTED] wrote: Hi everyone. I have getting a sintax error when i try to execute DELIMITER | in my mysql 5 server. Could anyone help me about it? -- João Cândido de Souza Neto Curitiba Online [EMAIL PROTECTED] (41) 3324-2294 (41) 9985-6894 http://www.curitibaonline.com.br -- 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: £ (Great Britain Pound) breaks MySQL insert a nd update - disastrous overwrite - FIXED
One further post for the archive:
the version of mysql we are running now is 5.0.22
the mysql connector is now mysql-connector-java-5.0.3-bin.jar
The old connector worked fine with mysql 3.23 (and I tested again with the old
database this morning, and all was fine, including £ signs). Then we upgraded
to a new server running 5.0.22 a couple of weeks ago. And that was it. It
seems one better keep the connector in the same version realm as the database.
Now we are in UTF-8, it is saving everything I can throw at it without
creating garbage characters. Whew.
Regards,
Paul
- Original Message
From: Paul Warner [EMAIL PROTECTED]
To: Philip Mather [EMAIL PROTECTED]; MySQL List mysql@lists.mysql.com
Sent: Friday, October 20, 2006 4:14:44 PM
Subject: Re: £ (Great Britain Pound) breaks MySQL insert and update -
disastrous overwrite - FIXED
Thanks, Warren and Phil for your responses. And Martijn.
Yesterday I went through all my code and tomcat/web application configuration
settings, and converted all to UTF-8. I converted the database to UTF-8.
According to all the books and articles I've read, I have set every setting to
use UTF-8. Still the inserts and updates were breaking. Gerald Clark suggested
I turn query logging on, and I did.
This showed that the query, in mysql, was truncated when it contained a £ sign,
and perfectly fine when it didn't. It was perfect when I handed it to the
MySQL JDBC connector, but broken when it got to mysql. Anything without a £
sign was fine on both sides. And if I gave the query to mysql via the command
line interface, or via perl, it was fine. It didn't seem to be in my app, nor
in mysql itself. It seemed to be the connector!
So I looked and was surprised to find I was using
mysql-connector-java-3.13-beta-bin.jar. I just upgraded to
mysql-connector-java-5.0.3-bin.jar and that has fixed the problem! Why was I
using a beta version? I do not know.
Thanks, everyone, for your help! I hope the next person who runs into this
problem benefits from this thread.
Regards,
Paul
- Original Message
From: Philip Mather [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Friday, October 20, 2006 2:24:18 PM
Subject: Re:£ (Great Britain Pound) breaks MySQL insert and update - disastrous
overwrite
Warren Young wrote:
Paul Warner wrote:
When a user enters text with a £ sign (Great Britain Pound) in the
browser and clicks enter, any insert or update statement apparently
gets truncated in mysql.
It's possible that somewhere along the line, the character is getting
translated to a multibyte Unicode format. (UTF-16 or -32, most
likely.) The hex value for the pound sign is 00A3, which includes a
null character, which the MySQL C API will interpret as end-of-string.
From my own experience I'd point out that if your using a terminal to
do something like SSH, that too needs to be set to the correct encoding
as well. Data being imported (be that any method, even cutting and
pasting) can also be an area of strife, I suffered greatly when when
producing a copious country and region listing system with everything in
the correct character sets for a website that had up until then been
ISO-{whatever}.
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? I'm using InnoDB by the way Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.1.14 Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.1.14, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. Version 3.1.14 is a bugfix release for the legacy stable branch that is suitable for use with any MySQL version including MySQL-4.1, MySQL-5.0 or MySQL-5.1 beta. It is now available in source and binary form from the Connector/J download pages at http://dev.mysql.com/downloads/connector/j/3.1.html and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.) This is the second to last planned bug fix release for this branch. We encourage users to consider upgrading as soon as possible to the current production branch of Connector/J, 5.0, available for download at http://dev.mysql.com/downloads/connector/j/5.0.html Best Regards, -Mark - From the changelog: 10-19-06 - Version 3.1.14 - Fixed BUG#20479 - Updatable result set throws ClassCastException when there is row data and moveToInsertRow() is called. - Fixed BUG#20485 - Updatable result set that contains a BIT column fails when server-side prepared statements are used. - Fixed BUG#16987 - Memory leak with profileSQL=true. - Fixed BUG#19726 - Connection fails to localhost when using timeout and IPv6 is configured. - Fixed BUG#16791 - NullPointerException in MysqlDataSourceFactory due to Reference containing RefAddrs with null content. - Fixed BUG#20306 - ResultSet.getShort() for UNSIGNED TINYINT returns incorrect values when using server-side prepared statements. - Fixed BUG#20687 - Can't pool server-side prepared statements, exception raised when re-using them. - Fixed BUG#21062 - ResultSet.getSomeInteger() doesn't work for BIT(1). - Fixed BUG#18880 - ResultSet.getFloatFromString() can't retrieve values near Float.MIN/MAX_VALUE. - Fixed BUG#20888 - escape of quotes in client-side prepared statements parsing not respected. Patch covers more than bug report, including NO_BACKSLASH_ESCAPES being set, and stacked quote characters forms of escaping (i.e. '' or ). - Fixed BUG#19993 - ReplicationDriver does not always round-robin load balance depending on URL used for slaves list. - Fixed calling toString() on ResultSetMetaData for driver-generated (i.e. from DatabaseMetaData method calls, or from getGeneratedKeys()) result sets would raise a NullPointerException. - Fixed Bug#21207 - Driver throws NPE when tracing prepared satements that have been closed (in asSQL()). - Removed logger autodectection altogether, must now specify logger explicitly if you want to use a logger other than one that logs to STDERR. - Fixed BUG#22290 - Driver issues truncation on write exception when it shouldn't (due to sending big decimal incorrectly to server with server-side prepared statement). - Driver now sends numeric 1 or 0 for client-prepared statement setBoolean() calls instead of '1' or '0'. - Fixed bug where driver would not advance to next host if roundRobinLoadBalance=true and the last host in the list is down. - Fixed BUG#18258 - DatabaseMetaData.getTables(), columns() with bad catalog parameter threw exception rather than return empty result set (as required by spec). - Check and store value for continueBatchOnError property in constructor of Statements, rather than when executing batches, so that Connections closed out from underneath statements don't cause NullPointerExceptions when it's required to check this property. - Fixed BUG#21814 - time values outside valid range silently wrap. - Fixed bug when calling stored functions, where parameters weren't numbered correctly (first parameter is now the return value, subsequent parameters if specified start at index 2). - Fixed BUG#21814 - time values outside valid range silently wrap. - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (MingW32) iD8DBQFFOPzDtvXNTca6JD8RArD4AJ0Sj3fjDe8kGGLG2zZ6R0d+cHdxXgCeIiJ8 Bp64VqruY45Y2/Uah1YSfmA= =ZVdW -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mention regular accounts' setup before superusers'
As its first example of creating accounts, the manual says After connecting to the server as `root', you can add new accounts. The following statements use `GRANT' to set up four new accounts: mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' - IDENTIFIED BY 'some_pass' WITH GRANT OPTION; I would first mention regular accounts' setup before superusers'. P.S., I note one can do $ echo SELECT \*,CWORD,HWORD,CMP FROM dict WHERE CMP=\T\\;|mysql but a second \* cannot be used: SELECT \*,CWORD,\* etc. P.S., in Appendix H, GNU's street address has changed. P.S., -t causes a blank to be appended to each line! $ echo SELECT ... FROM ...|mysql db -t|grep \ $ (except the header and top and bottom border.) (Caught with emacs' show-trailing-whitespace.) (mysql -E does too for empty fields, by the way.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
Yes, there is a full text index, there are about 12,000 rows or so. Don -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, October 19, 2006 11:47 PM To: Don O'Neil Cc: mysql@lists.mysql.com Subject: Re: Problem with query on 5.11 Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with query on 5.11
I just deleted and re-created my indexes and the query works now... I guess the index got corrupted somehow. Strange that I never saw any indications of this in messages, just that 0 rows were returned. Don -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, October 19, 2006 11:47 PM To: Don O'Neil Cc: mysql@lists.mysql.com Subject: Re: Problem with query on 5.11 Don O'Neil wrote: Why does this query return no results: SELECT * FROM FileList WHERE MATCH Filename AGAINST (9640) When there are entries in the Filename list that have 9640 in them? How many rows are in the table? Full text won't work with only a couple of rows. And you do have a full text index on the filename field right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with query: select customers that ARO NOT in orders table
hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with query: select customers that ARO NOT in orders table
On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan I think the most explicit way is with a sub select: select cust_id from customers where cust_id not in (select cust_id from orders); -w -- 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]
Desire for ability to select from procedure results
I know that MySQL does not currently support using stored procedures as a subquery and, as far I understand, this is not planned. I personally would like this and got used to it in ms sqlserver. For people interested in the topic here is a thread I found on Jon Haddad's site Rusty Razor Blade: http://www.rustyrazorblade.com/index.php/2006/09/27/can-you-use-a-stored-procedure-in-a-subquery-i-dont-think-so-mysql/ Some people advocate using views but since MySQL does not support parameterized views either it lacks the flexibility I wish. I have thought of having my stored procs create and populate temp (possibly memory) tables, perhaps using a naming convention based on the stored proc name. I could then select from the temp tables contents. This will probably work but I would be interested in other people's views and/or experience with this. Reactions? Thanks, Peter Andrews -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with query: select customers that ARO NOT in orders table
Ok. Just found I gave wrong info. To make my life easier, the person who created db named cust_id in 'orders' table as SoldTo [EMAIL PROTECTED] in this case, select cust_id from customers where cust_id not in (select Soldto from orders); will not work :( On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan I think the most explicit way is with a sub select: select cust_id from customers where cust_id not in (select cust_id from orders); -w -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: £ (Great Britain Pound) breaks MySQL insert and update - disastrous overwrit e - FIXED
Paul Warner wrote: Now we are in UTF-8, it is saving everything I can throw at it without creating garbage characters. Whew. Yep. Even though it wasn't the solution to your immediate problem, switching to UTF-8 will prevent a whole class of future ones. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: delimiter
On Thu, 2006-10-19 at 18:18 -0300, JoXo CXndido de Souza Neto wrote: I tried to escape the pipe character but it does not work. I shall try to explain better what is happening. I have got a .sql file which create my whole database (including triggers). When i run it at phpmyadmin it fails, then i tried to run a part of my .sql file which creates a trigger in a mysql prompt and it works fine. I believe the DELIMITER command is currently only a mysql client command (i.e. it only works in the command line client). Perhaps it works in the Query Browser; I don't use the GUI tools, so I'm not sure. Is there some trouble using the delimiter comand by a php script? Dan Buettner [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Joo, the pipe character | often needs to be escaped, usually with a backslash like so: \| Can you post the command you are running and the error you get? Dan On 10/19/06, Joo Cndido de Souza Neto [EMAIL PROTECTED] wrote: Hi everyone. I have getting a sintax error when i try to execute DELIMITER | in my mysql 5 server. Could anyone help me about it? -- Joo Cndido de Souza Neto Curitiba Online [EMAIL PROTECTED] (41) 3324-2294 (41) 9985-6894 http://www.curitibaonline.com.br -- 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: help with query: select customers that ARO NOT in orders table
On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: Ok. Just found I gave wrong info. To make my life easier, the person who created db named cust_id in 'orders' table as SoldTo [EMAIL PROTECTED] in this case, select cust_id from customers where cust_id not in (select Soldto from orders); will not work :( Maybe I'm not understanding you but as long as Soldto is cust_id just with a different column name it will still work. The subquery will result in a list of values and then the NOT IN clause will check if your cust_id is not in that list. For more info check out: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote: hi to all, I have table customers (PK cust_id) I have table orders (PK order_id, FK cust_id) I need query that will selecct all customers from 'customers' they don't have any order, there is not their cust_id in 'orders'. couls somebody help me? thanks. -afan I think the most explicit way is with a sub select: select cust_id from customers where cust_id not in (select cust_id from orders); -w -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 5.0.4 Has Been Released
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
MySQL Connector/J 5.0.4 a new version of the Type-IV all-Java JDBC
driver for MySQL has been released.
This is a bugfix release for the current production branch of Connector/J.
Version 5.0.4 is suitable for use with any MySQL version including
MySQL-4.1, MySQL-5.0 or MySQL-5.1 beta.
It is now available in source and binary form from the Connector/J
download pages at http://dev.mysql.com/downloads/connector/j/5.0.html
and mirror sites (note that not all mirror sites may be up to date at
this point of time - if you can't find this version on some mirror,
please try again later or choose another download site.)
As always, we recommend that you check the change log
(http://dev.mysql.com/doc/refman/5.0/en/cj-news.html) and Upgrading
sections (http://dev.mysql.com/doc/refman/5.0/en/cj-upgrading.html) in
the manual before upgrading to be aware of changes in behavior that
might affect your application.
-Mark
- From the change log (notice that this release includes changes and
fixes from versions 3.1.14 and the yet-to-be-released 3.1.15 versions):
10-20-06 - Version 5.0.4
- Fixed BUG#21379 - column names don't match metadata in cases where
server doesn't return original column names (column functions) thus
reaking compatibility with applications that expect 1-1 mappings between
findColumn() and rsmd.getColumnName(), usually manifests itself as
Can't find column ('') exceptions.
- Fixed BUG#21544 - When using information_schema for metadata,
COLUMN_SIZE for getColumns() is not clamped to range of
java.lang.Integer as is the case when not using information_schema, thus
leading to a truncation exception that isn't present when not using
information_schema.
- Fixed configuration property jdbcCompliantTruncation was not
being used for reads of result set values.
- Fixed BUG#22024 - Newlines causing whitespace to span confuse
procedure parser when getting parameter metadata for stored procedures.
- Driver now supports {call sp} (without () if procedure has no
arguments).
- Fixed BUG#22359 - Driver was using milliseconds for
Statement.setQueryTimeout() when specification says argument is to be in
seconds.
- Workaround for server crash when calling stored procedures via a
server-side prepared statement (driver now detects prepare(stored
procedure) and substitutes client-side prepared statement), addresses
BUG#22297.
- Added new _ci collations to CharsetMapping, fixing Bug#22456 -
utf8_unicode_ci not working.
- Fixed BUG#22290 - Driver issues truncation on write exception when
it shouldn't (due to sending big decimal incorrectly to server with
server-side prepared statement).
- Fixed BUG#22613 - DBMD.getColumns() does not return expected
COLUMN_SIZE for the SET type, now returns length of largest possible
set disregarding whitespace or the , delimitters to be consistent
with the ODBC driver.
- Driver now sends numeric 1 or 0 for client-prepared statement
setBoolean() calls instead of '1' or '0'.
- DatabaseMetaData correctly reports true for supportsCatalog*()
methods.
nn-nn-06 - Version 3.1.15
- Fixed BUG#23281 - Downed slave caused round-robin load balance to
not cycle back to first host in list.
10-19-06 - Version 3.1.14
- Fixed BUG#20479 - Updatable result set throws ClassCastException
when there is row data and moveToInsertRow() is called.
- Fixed BUG#20485 - Updatable result set that contains a BIT column
fails when server-side prepared statements are used.
- Fixed BUG#16987 - Memory leak with profileSQL=true.
- Fixed BUG#19726 - Connection fails to localhost when using
timeout and IPv6 is configured.
- Fixed BUG#16791 - NullPointerException in MysqlDataSourceFactory
due to Reference containing RefAddrs with null content.
- Fixed BUG#20306 - ResultSet.getShort() for UNSIGNED TINYINT
returns incorrect values when using server-side prepared statements.
- Fixed BUG#20687 - Can't pool server-side prepared statements,
exception raised when re-using them.
- Fixed BUG#21062 - ResultSet.getSomeInteger() doesn't work for BIT(1).
- Fixed BUG#18880 - ResultSet.getFloatFromString() can't retrieve
values near Float.MIN/MAX_VALUE.
- Fixed BUG#20888 - escape of quotes in client-side prepared
statements parsing not respected. Patch covers more than bug report,
including NO_BACKSLASH_ESCAPES being set, and stacked quote characters
forms of escaping (i.e. '' or ).
- Fixed BUG#19993 - ReplicationDriver does not always round-robin
load balance depending on URL used for slaves list.
- Fixed calling toString() on ResultSetMetaData for driver-generated
(i.e. from DatabaseMetaData method calls, or from getGeneratedKeys())
result sets would raise a
Summing rows and colums in mysql 5.0 heeelllppp!!
How can I keep a running sum of the values in rows as well as columns in a mysql database and display them,, Can someone show examples of this thanks Value1 12 14 34 Sum -- Value2 14 23 45 Sum -- Sum -- -- -- -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.8/489 - Release Date: 10/20/2006
Count query?
Hello all Maybe it's been a long week, but I'm trying to do something that should be simple and just not getting anywhere. I have two tables: accounts acctid: int unique acctowner: char ... docs docid: int unique acctid: int doctitle: char ... I want to list my accounts along with a count of the documents associated with each account. I tried a simple GROUP BY: SELECT a.acctid, a.acctowner, COUNT(d.acctid) FROM accounts AS a, documents AS d WHERE a.acctid = d.acctid GROUP BY a.acctid; This almost works, but I don't get any of the accounts that don't have any associated documents. I want all accounts listed, even if COUNT(documents) is 0. Would this work better as a subquery? I'll admit that I'm still stumbling when it comes to subqueries. Any help would be greatly appreciated. /dwight -- Dwight N. Tovey [EMAIL PROTECTED] http://www.dtovey.net/~dwight/ Please Do Not send me Microsoft Word attachments. See http://www.gnu.org/philosophy/no-word-attachments.html --- Higher education helps your earning capacity. Ask any college professor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php and utf8 problem
Hello, I am trying to install php nuke and I get this error: File 'c:\mysql\share\charsets\?.conf' not found (Errcode: 2) Character set '#33' is not a compiled character set and is not specified in the 'c:\mysql\share\charsets\Index' file I have researched it and nothing ive tried works. Any ideas? Thanks, James
Re: Count query?
On 10/21/06, Dwight Tovey [EMAIL PROTECTED] wrote: Hello all Maybe it's been a long week, but I'm trying to do something that should be simple and just not getting anywhere. I have two tables: accounts acctid: int unique acctowner: char ... docs docid: int unique acctid: int doctitle: char ... I want to list my accounts along with a count of the documents associated with each account. I tried a simple GROUP BY: SELECT a.acctid, a.acctowner, COUNT(d.acctid) FROM accounts AS a, documents AS d WHERE a.acctid = d.acctid GROUP BY a.acctid; Change it to a left join: ... FROM accounts a LEFT OUTER JOIN documents d ON a.acctid=d.acctid GROUP BY a.acctid; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count query?
chris smith wrote: On 10/21/06, Dwight Tovey [EMAIL PROTECTED] wrote: Hello all Maybe it's been a long week, but I'm trying to do something that should be simple and just not getting anywhere. I have two tables: accounts acctid: int unique acctowner: char ... docs docid: int unique acctid: int doctitle: char ... I want to list my accounts along with a count of the documents associated with each account. I tried a simple GROUP BY: SELECT a.acctid, a.acctowner, COUNT(d.acctid) FROM accounts AS a, documents AS d WHERE a.acctid = d.acctid GROUP BY a.acctid; Change it to a left join: ... FROM accounts a LEFT OUTER JOIN documents d ON a.acctid=d.acctid GROUP BY a.acctid; Bingo. I new it had to be something simple. I didn't even think of an outer join. It all works fine now. This should save me from having to select the accounts, then loop through to count the documents for each account. I didn't like that idea at all. Thanks again. /dwight -- Dwight N. Tovey [EMAIL PROTECTED] http://www.dtovey.net/~dwight/ Please Do Not send me Microsoft Word attachments. See http://www.gnu.org/philosophy/no-word-attachments.html --- About the time we think we can make ends meet, somebody moves the ends. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Summing rows and colums in mysql 5.0 heeelllppp!!
Something like this should work if you want to do it in SQL: SELECT a, b, c, a+b+c FROM table where clause UNION ALL SELECT SUM(a), SUM(b), SUM(c), SUM(a+b+c) where clause Dan On 10/20/06, Brian E Boothe [EMAIL PROTECTED] wrote: How can I keep a running sum of the values in rows as well as columns in a mysql database and display them,, Can someone show examples of this thanks Value1 12 14 34 Sum -- Value2 14 23 45 Sum -- Sum -- -- -- -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.8/489 - Release Date: 10/20/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
