RE: characterset problem 4.1.7
I just read the post 'MySQL 4.1 and Unicode produces crap' on Dec. 6 by Yves Goergen. I guess we are having the same issue. I notice that 4.1.0 if I do "SHOW VARIABLES LIKE "char%"', there is only one setting, however, in 4.1.7 there are different characterset. I did tried to change all variables to use utf8, but the problem still exist. Is there anybody could help? Or did I asked the question in a wrong group? Thank you. Francis Mak -----Original Message- From: Francis Mak [mailto:[EMAIL PROTECTED] Sent: Friday, December 03, 2004 7:42 PM To: [EMAIL PROTECTED] Subject: characterset problem 4.1.7 Dear all, I was using mysql 4.1.0, all table use utf8. I can use php to store and display utf8 character without any problem. Yesterday I upgraded 4.1.0 to 4.1.7. I use mysql-control-center and I can see the data in 4.1.7 are utf8 characters. However, when I use my php program to display it, all things become ??? I supspect it is due to the client connection? What config I need to do in order to make php display correctly? Please Please! Thank you very much. Francis Mak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
characterset problem 4.1.7
Dear all, I was using mysql 4.1.0, all table use utf8. I can use php to store and display utf8 character without any problem. Yesterday I upgraded 4.1.0 to 4.1.7. I use mysql-control-center and I can see the data in 4.1.7 are utf8 characters. However, when I use my php program to display it, all things become ??? I supspect it is due to the client connection? What config I need to do in order to make php display correctly? Please Please! Thank you very much. Francis Mak
RE: Cannot do join on varchar
Since I don't have control on this server(cannot upgrade), so I cannot tell if same happen in 4.1.3. However, I would like to know the result in 4.0 as well. Anyone test this script in 4.0/3.X server? I suspect this could be a unicode problem in 4.1? If 4.1.3 could fix the problem I will suggest my admin to do an upgrade. Luckly this is not a production server... EXPLAIN SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id In Extra show: "Impossible WHERE noticed after reading const tables" I also found out that the trim should actually go to the 'matching' id: /* Previously I suggest trim on the left column: */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id /* However, this works as well, and it seems more logical? */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE TRIM(tb_test1.id)='abcde' AND tb_test1.id = tb_test2.id Anybody could explain this? Thank you. Francis -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 8:19 PM To: 'Francis Mak '; '[EMAIL PROTECTED] ' Subject: RE: Cannot do join on varchar Have you tried this with the latest MySQL 4.1.x build? Does it not work in this version? What does the explain plan show? -Original Message- From: Francis Mak To: [EMAIL PROTECTED] Sent: 7/13/04 4:01 AM Subject: Cannot do join on varchar Hi, I found out that when joining varchar column, you have to trim down the left column in order for MYSQL to match the row, is it a known issue? Or join only works fine in number column? CREATE TABLE `tb_test1` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; CREATE TABLE `tb_test2` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; INSERT INTO tb_test1(id, name) VALUES('abcde', 'NAME1') INSERT INTO tb_test2(id, name) VALUES('abcde', 'NAME2') /* The following display zero row */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id /* The following will display correct result */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id Redhat 9, with MYSQL 4.1.1, using UTF8 as default charset. Any idea?? Thank you. Francis Mak -- 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]
Cannot do join on varchar
Hi, I found out that when joining varchar column, you have to trim down the left column in order for MYSQL to match the row, is it a known issue? Or join only works fine in number column? CREATE TABLE `tb_test1` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; CREATE TABLE `tb_test2` ( `id` varchar(5) NOT NULL default '', `name` varchar(100) NOT NULL default '', ) TYPE=MyISAM DEFAULT CHARSET= utf8; INSERT INTO tb_test1(id, name) VALUES('abcde', 'NAME1') INSERT INTO tb_test2(id, name) VALUES('abcde', 'NAME2') /* The following display zero row */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND tb_test1.id = tb_test2.id /* The following will display correct result */ SELECT tb_test1.id FROM tb_test1, tb_test2 WHERE tb_test1.id='abcde' AND TRIM(tb_test1.id) = tb_test2.id Redhat 9, with MYSQL 4.1.1, using UTF8 as default charset. Any idea?? Thank you. Francis Mak
max length of primary key
Hi, I have a problem when creating a table: CREATE TABLE test ( test_key varchar(255) NOT NULL default '', test_value text NOT NULL, PRIMARY KEY (test_key) ) TYPE=MYISAM; It then report error: mySQL Error: Specified key was too long. Max key length is 500 Why the varchar exceeded 500? I have a "set-variable= default-character-set=utf8" in my.cnf, maybe this is the cause of error? And is there way to increase this 500 value? Or I must use InnoDB then? Mysql 4.1.1, RedHat 9 Thanks. Francis Mak
RE: error 1045 happened randomly
um.. I dont know if glibc/openssl will cause the problem. I am not very linux expert here.. but showing you this may help giving out some info? # rpm -qa|grep glibc glibc-devel-2.3.2-11.9 glibc-common-2.3.2-11.9 glibc-2.3.2-11.9 glibc-kernheaders-2.4-8.10 and openssl, I am self compiled: openssl-0.9.7c -Original Message- From: Duncan Hill [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 12:02 AM To: [EMAIL PROTECTED] Subject: Re: error 1045 happened randomly On Monday 01 December 2003 11:46, Francis Mak wrote: > I am using PHP4.3.4 + MYSQL 4.1.0-alpha-standard(binary, not rpm and > not compiled by myself) + REDHAT 9 Hmm, I just read this part again.. RedHat 9 is the consistent part between your systems and mine. Wonder if glibc or openssl is at fault? -- 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: error 1045 happened randomly
ok, I have tried giving a blank password and after 15mins of refreshing(previously it give me error in just around 2-3 mins.) I don't see the Access Denied message anymore. Duncan, Wouter, thanks!! though "blank password" is not a good solution, but at least it fix the issue. I think I have to go back to previous version, :( and I am looking forward to use 4.1 stable in my next project, since the full unicode support. does mysql team know this issue? I wanna report this to make this product better. did you, Duncan? and Duncan said the problem occur when user hits the DB 100 times. how could you monitor the number of hits?? by using what tools? thank you guys! -Original Message- From: Wouter van Vliet [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 8:52 PM To: 'Francis Mak'; [EMAIL PROTECTED] Subject: RE: error 1045 happened randomly Yes, I replied quick. That's because I'm at work and have nothing better to do :D. When I'm at home it usually takes me a bit longer. Anyways, this error, does it ONLY occur on index.php? Or also on other pages? Now that I have read Duncan's remarks (also quick in his replies), it may be a bug in the alpha version. Though I still don't believe in the random part, since there must be something internal in MySQL that does it with some kind of pattern. Before you 'regrant', I'd advice you to first clear the previous grant. You'd probably do that out of instinct, .. though. If that doesn't solve, you might go with Duncan's thing. The No Password. But be carefull you don't do that on your production server. Or, in my eyes better yet, downgrade to the latest stable verion of MySQL. And compile it yourself, .. always better since it's totally fit for your system then. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1045 happened randomly
- I am 100% sure that all scripts are using include ("db.php"); so all pages are using the same login and password and hostname - since I am using password, so it shows "Using Password YES" as well. - The error indeed occured on the same page. e.g. when you are viewing index.php, it may run successfully. and you keep on refreshing your browser on the same page, 'sometimes' it will show the error. for your ref., below are the connect function used in my db.php(which is just a very common function): $dbhost="localhost"; $dbuser="auser"; $dbpwd="pass"; $dbname="dbname"; function connectdb(){ global $dbhost, $dbuser, $dbpwd, $dbname; ($dbname) ? $dbname : $dbname = $dbuser; $dbh = mysql_connect($dbhost, $dbuser, $dbpwd) or die("Failed to connect database server ".mysql_error()." ".mysql_errno()); mysql_select_db("$dbname") or die("Unable to select database"); return $dbh; } thanks!! Wouter, you replied so quick, ;) of course I won't do blank password in a production server. But just wanna know it is a problem in 4.1-alpha or not. and I will try to "regrant" by using the statement you gave me. thanks. see another thread about this: http://groups.google.com/groups?hl=zh-TW&lr=&ie=UTF-8&oe=UTF-8&threadm=bkeab v%241bq6%241%40FreeBSD.csie.NCTU.edu.tw -Original Message- From: Wouter van Vliet [mailto:[EMAIL PROTECTED] Sent: Monday, December 01, 2003 8:16 PM To: 'Francis Mak'; [EMAIL PROTECTED] Subject: RE: error 1045 happened randomly First of all, there was no reason for the "flush privileges;" call. This is automatically handled by MySQL when you use the GRANT and REVOKE statements. Doesn't do any harm though ;). Second, nothing is ever random in a computer. Not even the rand(); function. When you start realizing that, you will come closer to the solution. What I expect is that in some script you have mistyped the password or somthing. Does this message say anything else besides "1045: Access Denied for user '[EMAIL PROTECTED]'"? Usually it also says: "Using password YES" or "Using password NO". So, try to see the logic of this error. Is it always displayed when executing the same script, are different scripts involved each time. Is the username the error tells you the same as the username you granted access to? Are your mysql_connect(); calls complete? Check all that and report back ;) Wouter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1045 happened randomly
Duncan, thanks for your tips. I will give it a try. so this is a known bug in mysql? and for sure using blank password is not a long term solutions, what if it is a share hosting env.? any answer from mysql team? many thanks. francis On Monday 01 December 2003 11:46, Francis Mak wrote: > it works successfully, however, sometimes it just give me "Error 1045: > Access Denied for user '[EMAIL PROTECTED]'" > it happens RANDOMLY. > > Could anybody please let me know how to fix this??? Remove the password from the login and the problem appears to go away. I've hammered my test DBs with thousands of serial logins, and the problem only shows when a login has a password. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 1045 happened randomly
Hi, sorry if this has been answered previously, however I tried to google, I found a few people having this issue, but no solutions provided. I am using PHP4.3.4 + MYSQL 4.1.0-alpha-standard(binary, not rpm and not compiled by myself) + REDHAT 9 all I did is: grant all on privileges on dbname.* to 'auser'@localhost identified by "pass"; flush privileges; and in my PHP, I provide the correct login, password and using 'localhost'. it works successfully, however, sometimes it just give me "Error 1045: Access Denied for user '[EMAIL PROTECTED]'" it happens RANDOMLY. Could anybody please let me know how to fix this??? Many thanks. Francis