Fw: Weird problem with mysql_query
- Original Message - From: Darryle Steplight [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, September 09, 2008 6:59 PM Subject: Re: Weird problem with mysql_query Hi G, There is nothing weird about your results. When you do a Count(*) without a GROUP BY(someColumn) you are essentially asking MySQL how many rows are present in the table. But when you do use Group By someColum , you are asking MySql how many rows do I have of someColumn . It's just a good practice to use GROUP BY when you want to a count of a specific column . mysql select count(*) as 'Count' from logins GROUP BY dawiz I found my problem - it turned out to be a misconception on my part; I was using sprintf(buf,%d, row[i]) if it was a numeric field - this was printing the address rather than the value. Apparently even though the data is type MYSQL_TYPE_LONGLONG it should be treated as char. G Vaughn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird problem with mysql_query
I don't have that much experience with MySQL having mostly worked with MSSQL, but I'm sure the logic is still the same. I believe the query select count(*) as 'count' from logins GROUP BY dawiz will fail because dawiz is not a column, it's a value within the table. If I'm not mistaken, a GROUP BY is only required when the COUNT needs to be split into groups: e.g. select player, count(*) as 'Count' from logins GROUP BY player; player | Count --- player1 | 12 player2 | 7 player3 | 35 or: select count(*) as 'Count' from logins WHERE player = 'player1'; Count - 12 or: select count(*) as 'Count' from logins; Count - 54 Regards, Andy Darryle Steplight wrote: Hi G, There is nothing weird about your results. When you do a Count(*) without a GROUP BY(someColumn) you are essentially asking MySQL how many rows are present in the table. But when you do use Group By someColum , you are asking MySql how many rows do I have of someColumn . It's just a good practice to use GROUP BY when you want to a count of a specific column . mysql select count(*) as 'Count' from logins GROUP BY dawiz The above query should return the results you are looking for. On Tue, Sep 9, 2008 at 6:06 PM, MySql [EMAIL PROTECTED] wrote: We are running MySql version 5.0.45-Debian_1ubuntu3.1-log Debian etch distribution under Ubuntu. If I submit the following query via mysql_query it acts as if the where is not there: select count(*) as 'Count' from logins where player = 'aqwert'; this returns: Count 143578160 Submitting the same query at a MySql prompt works correcty: mysql select count(*) as 'Count' from logins where player = 'dawiz'; +---+ | Count | +---+ | 6026 | +---+ 1 row in set (0.00 sec) Modifying the query to use a group by returns the correct count: TotalCount Total 6026 Is there something I should know about mysql_query and a simple count(*)? G Vaughn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird problem with mysql_query
We are running MySql version 5.0.45-Debian_1ubuntu3.1-log Debian etch distribution under Ubuntu. If I submit the following query via mysql_query it acts as if the where is not there: select count(*) as 'Count' from logins where player = 'aqwert'; this returns: Count 143578160 Submitting the same query at a MySql prompt works correcty: mysql select count(*) as 'Count' from logins where player = 'dawiz'; +---+ | Count | +---+ | 6026 | +---+ 1 row in set (0.00 sec) Modifying the query to use a group by returns the correct count: TotalCount Total 6026 Is there something I should know about mysql_query and a simple count(*)? G Vaughn
Re: Weird problem with mysql_query
Hi G, There is nothing weird about your results. When you do a Count(*) without a GROUP BY(someColumn) you are essentially asking MySQL how many rows are present in the table. But when you do use Group By someColum , you are asking MySql how many rows do I have of someColumn . It's just a good practice to use GROUP BY when you want to a count of a specific column . mysql select count(*) as 'Count' from logins GROUP BY dawiz The above query should return the results you are looking for. On Tue, Sep 9, 2008 at 6:06 PM, MySql [EMAIL PROTECTED] wrote: We are running MySql version 5.0.45-Debian_1ubuntu3.1-log Debian etch distribution under Ubuntu. If I submit the following query via mysql_query it acts as if the where is not there: select count(*) as 'Count' from logins where player = 'aqwert'; this returns: Count 143578160 Submitting the same query at a MySql prompt works correcty: mysql select count(*) as 'Count' from logins where player = 'dawiz'; +---+ | Count | +---+ | 6026 | +---+ 1 row in set (0.00 sec) Modifying the query to use a group by returns the correct count: TotalCount Total 6026 Is there something I should know about mysql_query and a simple count(*)? G Vaughn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird problem upgrading from MySQL 4.0.21 to MySQL 4.1.7/InnoDB mysql db (D'oh)
No InnoDB here, just some old ISAM tables. Sorry about my stupidity on this one, I have only myself to blame... I have a bunch of old-school ISAM tables that need to be converted to MyISAM, is there any way to do this en-masse? (I.E. not having to go through each DB in the DBMS and ALTER TABLE tablename TYPE=MYISAM; one by one) I see that ISAM support is in the codebase but not built by default, I guess I would rather migrate up to a DB type that isn't considered to be 'legacy' . -- Mark P. Hennessy [EMAIL PROTECTED] I have a MySQL DBMS running with a mysql DB that apparently is an InnoDB DB, all other DBs in this DBMS are MyISAM. When I try to upgrade, I get the following output: 041215 16:41:53 mysqld started InnoDB: Resetting space id's in the doublewrite buffer 041215 16:41:57 InnoDB: Started; log sequence number 0 1404262 InnoDB: You are upgrading to an InnoDB version which allows multiple InnoDB: tablespaces. Wait that purge and insert buffer merge run to InnoDB: completion... InnoDB: Full purge and insert buffer merge completed. InnoDB: You have now successfully upgraded to the multiple tablespaces InnoDB: format. You should NOT DOWNGRADE to an earlier version of InnoDB: InnoDB! But if you absolutely need to downgrade, see InnoDB: http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html InnoDB: for instructions. 041215 16:42:00 [ERROR] /usr/local/mysql-4.1.7/libexec/mysqld: Can't find file: 'host.MYI' (errno: 2) 041215 16:42:00 [ERROR] Fatal error: Can't open privilege tables: Can't find file: 'host.MYI' (errno: 2) 041215 16:42:00 mysqld ended Of course, there is no host.MYI because they are InnoDB and not MyISAM. What could be causing this? When I downgrade back to 4.0.21, I get a notice that the mysql DB using InnoDB is successfully downgraded to 4.1.1-style without any problem. Please advise. -- Mark P. Hennessy [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]
Weird problem upgrading from MySQL 4.0.21 to MySQL 4.1.7/InnoDB mysql db
I have a MySQL DBMS running with a mysql DB that apparently is an InnoDB DB, all other DBs in this DBMS are MyISAM. When I try to upgrade, I get the following output: 041215 16:41:53 mysqld started InnoDB: Resetting space id's in the doublewrite buffer 041215 16:41:57 InnoDB: Started; log sequence number 0 1404262 InnoDB: You are upgrading to an InnoDB version which allows multiple InnoDB: tablespaces. Wait that purge and insert buffer merge run to InnoDB: completion... InnoDB: Full purge and insert buffer merge completed. InnoDB: You have now successfully upgraded to the multiple tablespaces InnoDB: format. You should NOT DOWNGRADE to an earlier version of InnoDB: InnoDB! But if you absolutely need to downgrade, see InnoDB: http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html InnoDB: for instructions. 041215 16:42:00 [ERROR] /usr/local/mysql-4.1.7/libexec/mysqld: Can't find file: 'host.MYI' (errno: 2) 041215 16:42:00 [ERROR] Fatal error: Can't open privilege tables: Can't find file: 'host.MYI' (errno: 2) 041215 16:42:00 mysqld ended Of course, there is no host.MYI because they are InnoDB and not MyISAM. What could be causing this? When I downgrade back to 4.0.21, I get a notice that the mysql DB using InnoDB is successfully downgraded to 4.1.1-style without any problem. Please advise. -- Mark P. Hennessy [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird problem for TIMESTAMP feild
Hi All, I am getting very weired problem after shifting our server. here is the details:- I have a table with two columns 1-TimeStamp timestamp(14) 2- SID bigint(20) when inserting _INSERT INTO _/_TABLENAME values (1258975462,125987)_ results in /_INSERT INTO _/_TABLENAME values (00,125987)_ Timestamp entry gets zeros. Any idea? whats happening Regards, *TM* /
RE: Weird problem for TIMESTAMP feild
Invalid entry format for the timestamp field. Did this work before? -Original Message- From: Tariq Murtaza To: [EMAIL PROTECTED] Sent: 7/26/04 4:43 AM Subject: Weird problem for TIMESTAMP feild Hi All, I am getting very weired problem after shifting our server. here is the details:- I have a table with two columns 1-TimeStamp timestamp(14) 2- SID bigint(20) when inserting _INSERT INTO _/_TABLENAME values (1258975462,125987)_ results in /_INSERT INTO _/_TABLENAME values (00,125987)_ Timestamp entry gets zeros. Any idea? whats happening Regards, *TM* / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird problem for TIMESTAMP feild
According to the docs (http://dev.mysql.com/doc/mysql/en/DATETIME.html) The number 1258975462 equates to the date 1258-97-54 62:00:00 which is not a valid date. That's why you get the zero date value. That number could also equate to a unix_timestamp() value for the date 2009-11-23 06:24:22 select from_unixtime(1258975462) +---+ | from_unixtime(1258975462) | +---+ | 2009-11-23 06:24:22 | +---+ 1 row in set (0.05 sec) (http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html) What date did it represent in your data? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tariq Murtaza [EMAIL PROTECTED] wrote on 07/26/2004 05:43:04 AM: Hi All, I am getting very weired problem after shifting our server. here is the details:- I have a table with two columns 1-TimeStamp timestamp(14) 2- SID bigint(20) when inserting _INSERT INTO _/_TABLENAME values (1258975462,125987)_ results in /_INSERT INTO _/_TABLENAME values (00,125987)_ Timestamp entry gets zeros. Any idea? whats happening Regards, *TM* /
Weird problem with displaying and retrieving varchar
Hello, I have a table with 3 columns: mysql desc srv_ref_cities; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | state_id | char(2) | YES | | NULL| | | COUNTYCODE | char(3) | | | | | | CITY | varchar(40) | | | | | ++-+--+-+-+---+ And the problem is when i display a query (two of the columns don't show): mysql select * from srv_ref_cities where state_id='08'; +--+++ | state_id | COUNTYCODE | CITY | +--+++ || Acres Green || Aguilar if I have just state_id, countycode it displays just fine. I am thinking that maybe the city is unicode but i can't figure this one out. Has anyone else run into this problem or does anyone have any ideas to help me debug this? I have tried versions 4.0.15 and 5 but the same thing happens in each version. thanks, will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird problem with displaying and retrieving varchar
Will Richardson wrote: Hello, I have a table with 3 columns: mysql desc srv_ref_cities; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | state_id | char(2) | YES | | NULL| | | COUNTYCODE | char(3) | | | | | | CITY | varchar(40) | | | | | ++-+--+-+-+---+ And the problem is when i display a query (two of the columns don't show): mysql select * from srv_ref_cities where state_id='08'; +--+++ | state_id | COUNTYCODE | CITY | +--+++ || Acres Green || Aguilar Looks to me like your city contains a carriage return followed by some spaces. This is overwriting the state_id and COUNTYCODE on your screen. The data is there. You just can't see it. if I have just state_id, countycode it displays just fine. I am thinking that maybe the city is unicode but i can't figure this one out. Has anyone else run into this problem or does anyone have any ideas to help me debug this? I have tried versions 4.0.15 and 5 but the same thing happens in each version. thanks, will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird problem with differences MySQL 3.23.38 und 4.0.12
Axel Tietje [EMAIL PROTECTED] wrote: Hi everyone... I actually have two servers: 1. Server: MySQL 4.0.12 2. Server: MySQL 3.23.38 This query: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB' AND o_val = '1') ) GROUP BY o_obj HAVING (_69B96431 = '01') shows on server 1 (4.0.12): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) but on server 2 (3.23.38): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | NULL | NULL | | D27518B1 | 01| S | NULL | NULL | +--+---++---++ Please note the differences in fourth and fifth column while having absolutely identical tables and data. The following query works on 1. Server (4.0.12), but not on 2. Server (3.23.38): [skip] 3.23.38 was released about 2,5 years ago. Since that time many bugs were fixed. No wonder that query doesn't work on 3.23.38. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [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]
Weird problem with differences MySQL 3.23.38 und 4.0.12
Hi everyone... I actually have two servers: 1. Server: MySQL 4.0.12 2. Server: MySQL 3.23.38 This query: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB' AND o_val = '1') ) GROUP BY o_obj HAVING (_69B96431 = '01') shows on server 1 (4.0.12): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) but on server 2 (3.23.38): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | NULL | NULL | | D27518B1 | 01| S | NULL | NULL | +--+---++---++ Please note the differences in fourth and fifth column while having absolutely identical tables and data. The following query works on 1. Server (4.0.12), but not on 2. Server (3.23.38): SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB') ) GROUP BY o_obj HAVING (_69B96431 = '01'); 1. Server says: +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) 2. Server says: Empty set (0.00 sec) Explain shows the folowing in column 'Extra': 1. Server: Using where; Using temporary; Using filesort 2. Server: where used; Using temporary All other columns are identical: table TBL_32BF90B0 type ALL possible_keys o_key,o_val key NULL key_len NULL ref NULL rows 312 The following query shows the same result on both servers: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431 FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') ) GROUP BY o_obj HAVING (_69B96431 = '01') Both servers say: +--+---++ | o_obj| _69B96431 | T_69B96431 | +--+---++ | 1672BE70 | 01| S | | D27518B1 | 01| S | +--+---++ 2 rows in set (0.01 sec) Now, why that? TIA, Axel.
Re: Weird problem with differences MySQL 3.23.38 und 4.0.12
Some of the values passed to the CASE expressions are NULL. There was a bug in handling NULL in CASE that was fixed in MySQL 4.0.8: http://www.mysql.com/doc/en/News-4.0.8.html Note the last item on the page. I believe this explains the differences that you are seeing. At 16:28 +0200 6/12/03, Axel Tietje wrote: Hi everyone... I actually have two servers: 1. Server: MySQL 4.0.12 2. Server: MySQL 3.23.38 This query: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB' AND o_val = '1') ) GROUP BY o_obj HAVING (_69B96431 = '01') shows on server 1 (4.0.12): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) but on server 2 (3.23.38): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | NULL | NULL | | D27518B1 | 01| S | NULL | NULL | +--+---++---++ Please note the differences in fourth and fifth column while having absolutely identical tables and data. The following query works on 1. Server (4.0.12), but not on 2. Server (3.23.38): SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB') ) GROUP BY o_obj HAVING (_69B96431 = '01'); 1. Server says: +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) 2. Server says: Empty set (0.00 sec) Explain shows the folowing in column 'Extra': 1. Server: Using where; Using temporary; Using filesort 2. Server: where used; Using temporary All other columns are identical: table TBL_32BF90B0 type ALL possible_keys o_key,o_val key NULL key_len NULL ref NULL rows 312 The following query shows the same result on both servers: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431 FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') ) GROUP BY o_obj HAVING (_69B96431 = '01') Both servers say: +--+---++ | o_obj| _69B96431 | T_69B96431 | +--+---++ | 1672BE70 | 01| S | | D27518B1 | 01| S | +--+---++ 2 rows in set (0.01 sec) Now, why that? TIA, Axel. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird Problem.
Ok, I have a program I am working on, there is probably just something I don't understand about the behaviour of the C API and not a bug with MySQL's C api. I have an app that listens on a specified TCP port, once a user connects and successfully authenticates, two more processes are spawned (leaving the single original process to accept new connections), one process sits with a blocking read() on the tcp socket with the user, and just waits to recieve text data from the user, the other process waits till it has text data to write() to the user's socket. This program uses a MySQL backend to manage things like usernames and passwords, etc. If the user sends data just for the server, there is no problem working with MySQL, if the user recieves data from another user, there is still no trouble, but when the user sends data to another user, and then makes a request to the server that results in the server calling mysql_real_connect(), mysql_real_connect() just hangs indefinately (the mysql_init() call prior to the call to mysql_real_connect() appears to work fine though). I really have no idea why either, I've found nothing in the docs or mailing list archives that seems anything like the trouble I am experiencing. Let me try to explain the problem more clearly: User A recieves data from User B, no MySQL trouble, User A sends data to the server, no MySQL trouble, User A sends data to User B, we have trouble (and of course, User B has trouble if he sends data to User A). User A-TCPsocketA-processA read()-processA connect to process B's unix socket-processB read() read data from unix socket from processA- processB write()-TCPsocketB-User B And this causes mysql_real_connect() to hang if User A makes a request that causes the server to attempt to mysql_real_connect. I've run tcpdump and I've seen that mysql_real_connection seems to work at first, there is some talking between the client and MySQL, but then things just stop working, I also have this problem if I do a local connection with MySQL as well. Here are my system specs: mysqld Ver 3.23.54 for pc-linux-gnu on i686 I've tried with kernel 2.5.59 as well, I'm currently using: 2.4.21-pre3 I have dual pentium III 800 mhz cpu's (133 fsb) 1 GB of pc133 ram If more info is needed, I will glady provide it. Thanks. -Cameron --- Oh what is this test, Knights Who say... Oh Knights who until recently, said Ni? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird problem again.
I was the one having issues getting Lost Connection and whatnot for no reason. I have recompiled under glibc2.2.5 with 2.95 and 3.04. I just tried 4.0.2 from the source tree. Sometimes from the command line of doing mysql -uroot -p I get this: ERROR: And that's it. I run it again, and it goes in fine. Any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird problem with mysql database
I have added some extra fields to the mysql.user table, namely:
full_name VARCHAR(255)
job VARCHAR(255)
location VARCHAR(255)
phone VARCHAR(255)
reminder VARCHAR(255) NOT NULL
ip_addr VARCHAR(255) NOT NULL
I want to store these values for each person that creates a new account on my database.
The database is still in production test mode, so I was adding some dummy users.
The statement to create the account is:
INSERT into user (
Host,
User,
Password,
full_name,
job,
location,
phone,
reminder,
coreID,
ip_addr
)
VALUES ('%','yessir',PASSWORD('yesyesyes'),'Nathan
Lanier','Yes-man','FL-SE','123-1234','yesyesyes','161.161.1.131')
I kept getting Access Denied errors for this user. I scoured the newsgroup archives
for this problem, and tried every bit of advice on MySQL.com's documentation under
4.2.10, and nothing helped. I restarted the mysqld, and nothing helped. I needed to
make sure that I could still create new users, so, I tried:
INSERT into user (
Host,
User,
Password,
full_name,
job,
location,
phone,
reminder,
coreID,
ip_addr
)
VALUES ('%','newUser',PASSWORD('yesyesyes'),'Nathan
Lanier','','','','yesyesyes','161.161.1.131')
This one works perfectly. I narrowed the problem down to the job column, so I deleted
the value for user 'yessir'. It worked! In order to then figure out why some
accounts with values in the job column work and user 'yessir' didn't, I tried various
values for the job column. Thinking it was the hyphen, I put in Yesman and it
worked! But, I have another user with 'Heave-ho' as it's job and that one works fine.
So, it's not the hyphen. Very strange.
I think that the Yes- is throwing it off. But, if that was so, why can I change the
reminder column, on the same account, to be Yes-yesyes and the account still
works? Is there some limitation to using the keyword job? The column full_name
resides before job in the table, so I don't think that it's conflicting with the
order of the priviledge columns.
Any ideas would be appreciated so that I can make sure that some unsuspecting user
doesn't throw some value in their entry that will cause the system not to work for
them. Or maybe there's a better way of going about this?
Nicholas Hamlin
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Weird problem with mysql database
Hamlin Nicholas-qa568 writes:
I have added some extra fields to the mysql.user table, namely:
full_name VARCHAR(255)
job VARCHAR(255)
location VARCHAR(255)
phone VARCHAR(255)
reminder VARCHAR(255) NOT NULL
ip_addr VARCHAR(255) NOT NULL
I want to store these values for each person that creates a new account on my
database.
The database is still in production test mode, so I was adding some dummy users.
The statement to create the account is:
INSERT into user (
Host,
User,
Password,
full_name,
job,
location,
phone,
reminder,
coreID,
ip_addr
)
VALUES ('%','yessir',PASSWORD('yesyesyes'),'Nathan
Lanier','Yes-man','FL-SE','123-1234','yesyesyes','161.161.1.131')
I kept getting Access Denied errors for this user. I scoured the newsgroup
archives for this problem, and tried every bit of advice on MySQL.com's documentation
under 4.2.10, and nothing helped. I restarted the mysqld, and nothing helped. I
needed to make sure that I could still create new users, so, I tried:
INSERT into user (
Host,
User,
Password,
full_name,
job,
location,
phone,
reminder,
coreID,
ip_addr
)
VALUES ('%','newUser',PASSWORD('yesyesyes'),'Nathan
Lanier','','','','yesyesyes','161.161.1.131')
This one works perfectly. I narrowed the problem down to the job column, so I
deleted the value for user 'yessir'. It worked! In order to then figure out why
some accounts with values in the job column work and user 'yessir' didn't, I tried
various values for the job column. Thinking it was the hyphen, I put in Yesman and
it worked! But, I have another user with 'Heave-ho' as it's job and that one works
fine. So, it's not the hyphen. Very strange.
I think that the Yes- is throwing it off. But, if that was so, why can I change
the reminder column, on the same account, to be Yes-yesyes and the account still
works? Is there some limitation to using the keyword job? The column full_name
resides before job in the table, so I don't think that it's conflicting with the
order of the priviledge columns.
Any ideas would be appreciated so that I can make sure that some unsuspecting user
doesn't throw some value in their entry that will cause the system not to work for
them. Or maybe there's a better way of going about this?
Nicholas Hamlin
Tables in mysql databases are not designed to be changed in any single
fashion.
ALTERing will completely break security and permission systems and
will even prevent MySQL from proper functioning.
And you can only access them via GRANT / REVOKE command.
--
Regards,
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Weird problem with mysql database
Sinisa Milivojevic writes: ALTERing will completely break security and permission systems and will even prevent MySQL from proper functioning. Can you give me an example? I'm having no problems with security even after adding these columns. Users only have access to the dbs and tables that I assign in mysql.db and mysql.tables_priv, since all users are initially created with only usage privileges. How does adding non-privilege, information only columns to the end of mysql.user jeopardize security? And you can only access them via GRANT / REVOKE command. I don't understand this statement. I can access them via GRANT/REVOKE, or also by directly updating the information using UPDATE, INSERT, and DELETE (with the subsequent FLUSH PRIVILEGES). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Weird problem with mysql database
* Hamlin Nicholas-qa568 I have added some extra fields to the mysql.user table, namely: full_name VARCHAR(255) job VARCHAR(255) location VARCHAR(255) phone VARCHAR(255) reminder VARCHAR(255) NOT NULL ip_addr VARCHAR(255) NOT NULL I want to store these values for each person that creates a new account on my database. Yes, one might think that is a good idea, but it isn't... :) The mysql database is an internal system database, you are probably better of considering it read only... The GRANT and REVOKE commands modify these tables. cut Any ideas would be appreciated so that I can make sure that some unsuspecting user doesn't throw some value in their entry that will cause the system not to work for them. Or maybe there's a better way of going about this? Create your own user tables in your own database, and use only one or a few real mysql users... maybe one for read only, one for doing changes and one for admin, depending on your application, of course. This means you must have username and password columns in your table in addition to those you had planned, and you check the login against this. If it is ok, you do the real login to mysql with the uname/pw you wish for this user. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Some weird problem (aren't they all :-)
Hi there! I have a question. I've created a page in PHP4 which displays the result of the following query: select event_detail.id, event_detail.startdate, event_detail.enddate, event_detail.title, event_detail.comment, event_detail.active, event_detail.subscription_open, event_main.url from event_detail, event_main where event_detail.id_main = event_main.id and startdate '$today' and active = 'Y' order by startdate asc And everything works as it should...up until this weekend where I receive the following error: Warning: Supplied argument is not a valid MySQL result resource in /u284/oneiros/algemeen/agenda.php on line 28 The strange thing is that when I remove the order by line in the query, everything works fine. I've tried recreating the two tables in question but to no avail. Oh, yes. On my local server, I run MySQL version 3.23.41-nt and everything works just fine. The version on the server with the provider is 3.22.21 Many thanks in advance Blessed Be Peter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird problem
I just got this problem... bin/mysql -h localhost -u root logs me into MySQL at the command line However, the CGI script for Apache webserver gave me an error connecting to the database $source = DBI:mysql:red:localhost; $username = root; $password = ; Did any of you ever face this problem before?? BTW, I'm running Mandrake Linux... Very puzzled Tony _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Weird problem
Silver Fox wrote: I just got this problem... bin/mysql -h localhost -u root logs me into MySQL at the command line However, the CGI script for Apache webserver gave me an error connecting to the database $source = DBI:mysql:red:localhost; $username = root; $password = ; Did any of you ever face this problem before?? BTW, I'm running Mandrake Linux... It is probably a problem with the permissions of the /path/to/mysql.sock. The web server usually runs as user nobody so make sure that the path to the mysql.sock has world read and execute. --Bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Weird problem. 3.23.37, RH 6.0
try SELECT * WHERE x like '3411'. Steve Sobol wrote: I have a varchar field that contains the street number (part of a mailing address). It's a varchar because the field may occasionally contain non-numeric characters, but normally it doesn't. Let's call the field x. I have a record where x='3411'. In spite of the fact that x is a varchar, the query SELECT * WHERE x='3411' returns no rows, while SELECT * WHERE x=3411 returns the row containing x=3411. Why doesn't the string comparison work on the varchar field? I can't guarantee that there won't ever be non-numeric characters in that field, or I would have made it an int instead. Help! Thanks. -- Tired of Earthlink? Get JustTheNet! Nationwide Dialup, ISDN, DSL, ATM, Frame Relay, T-1, T-3, and more. EARTHLINK AMNESTY PROGRAM: Buy a year, get two months free More info coming soon to http://JustThe.net, or e-mail me! B!ff: K3wl, w3'v3 r00t3D da [EMAIL PROTECTED] 0h CrAp, INC0M!Ng $%^NO CARRIER - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Weird problem. 3.23.37, RH 6.0
Dibo Chen wrote: try SELECT * WHERE x like '3411'. Like doesn't work either. I tried it. mysql select ppn from main where streetnum=3411; +---+ | ppn | +---+ | 01A003290 | | 01A016440 | +---+ 2 rows in set (3.04 sec) mysql select ppn from main where streetnum='3411'; Empty set (2.85 sec) mysql select ppn from main where streetnum like '3411'; Empty set (2.88 sec) Field TypeNullKey Default Extra id bigint(4) PRI 0 ppn varchar(40) streetnum varchar(80) streetdir varchar(80) streetname varchar(80) streetsuffixvarchar(80) streetnum2 varchar(80) taxdist varchar(5) owner1 varchar(40) -- Tired of Earthlink? Get JustTheNet! Nationwide Dialup, ISDN, DSL, ATM, Frame Relay, T-1, T-3, and more. EARTHLINK AMNESTY PROGRAM: Buy a year, get two months free More info coming soon to http://JustThe.net, or e-mail me! B!ff: K3wl, w3'v3 r00t3D da [EMAIL PROTECTED] 0h CrAp, INC0M!Ng $%^NO CARRIER - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql weird problem
Hello, i am developing a shopping cart with perl and mysql. The project almost finished is running fine on WinME with Mysql and Perl and Apache. Its running fine with Opera IE and Netscape. When i transfer the project on my protable running Win98,Opera and IE work but netscape just tries continuouosly to connect but nothing. I have copied exactly the folder of mysql and apache eactly to the same directories on my portable as on the desktop. I don't understand why this happens with netscape. I tried the suggestions in the manual for the DNS problem. Still the same. I start mysql with winmysqladmin-green light comes on ok. Do i need the file my.cnf in the root? Any suggestions please? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql weird problem
I think your problem is at the netscapeapache level, not the mysql level. Can you view static html pages served by your local apache with netscape? since netscape, opera, and IE all connect to apache, and then apache calls perl to connect to mysql, I doubt your problem has anything to do with perl or mysql. -Original Message- From: Haris [mailto:[EMAIL PROTECTED]] Sent: Saturday, April 21, 2001 6:11 PM To: [EMAIL PROTECTED] Subject: Mysql weird problem Hello, snip When i transfer the project on my protable running Win98,Opera and IE work but netscape just tries continuouosly to connect but nothing. snip - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Very weird problem
Hi, I am using the newest versions of MySQL,PHPMyAdmin, and PHP4 on IIS server. The MySQLdatabase and PHP is working like a champ, but when I connect to localhost/phpmyadmin it asks me for login/password and domain. The problem is domain. We have a virtual private network here. So the machine is not a part of any domains. It has ip number of 10.24.*.* I can connect to mysql thru mysqld -u root -pmypassword so I know the user name and password are working. Any ideas how I can disable the question for domain or how to get around that problem? Thanks, Oskar Kaszubski Berry College - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
