max size of TEXT columns
hi listers, 1. environment: [EMAIL PROTECTED] ~ uname -a Linux myhost.mydomain.com 2.6.16-1.2080_2.rhfc5.cubbi_suspend2 #1 Wed Mar 29 12:54:32 CEST 2006 i686 i686 i386 GNU/Linux [EMAIL PROTECTED] ~ localhost.(none) show variables like version%; +-+--+ | Variable_name | Value | +-+--+ | version | 5.0.18-standard | | version_comment | MySQL Community Edition - Standard (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | +-+--+ 4 rows in set (0.00 sec) localhost.(none) localhost.mydb show variables like max%; ++--+ | Variable_name | Value | ++--+ | max_allowed_packet | 1048576 | localhost.mydb select max(char_length(history)) from my_contacts2; +---+ | max(char_length(history)) | +---+ | 65535 | +---+ 1 row in set (0.01 sec) localhost.mydb 2. problem according do the mysql docu TEXT/BLOB fields depend solely on the db environment and can grow to any length: 11.4.3. The |BLOB| and |TEXT| Types The maximum size of a |BLOB| or |TEXT| object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the |max_allowed_packet| variable, but you must do so for both the server and your client program. For example, both *mysql* and *mysqldump* allow you to change the client-side |max_allowed_packet| value. See Section 7.5.2, “Tuning Server Parameters” http://www.ayni.com/mysql/optimization.html#server-parameters, Section 8.3, “mysql — The MySQL Command-Line Tool” http://www.ayni.com/mysql/client-side-scripts.html#mysql, and Section 8.8, “mysqldump — A Database Backup Program” http://www.ayni.com/mysql/client-side-scripts.html#mysqldump. BUT: The table in consideration contains a column named history containing all message text which has been sent to the mail-address in the row in question. i ment to have observed that this column in reality does not contain all message text from the very beginning. today, i found out that the maximum lenght of the history fields in all tables of this kind is 65535. when an update is ocurring, the column history is recreated using concat(new_text, history) so that the newest text is always at the beginning of the column. but the oldest text at the end of the column apparently is lost. 3. question which parameter do i have to change in order to get this TEXT column really to any length? any hint is very much appreciated, thanks in advance. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union/collation problem, error 1267: feature or bug?
Hi Gleb, i finally found out a method to be entirely independent from any character-set as well as collation-sequence problem, when forming a UNION, where you occasionnally have to insert place-holders in one of the SELECT statements: as (text, varchar, char) placeholders use NULL instead of or '' if your application permits it. thanks for your suggestion an help. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union/collation problem, error 1267: feature or bug?
Hi Gleb, localhost.(none) show session variables like %version%; +-+--+ | Variable_name | Value| +-+--+ | protocol_version| 10 | | version | 4.1.16-standard | | version_comment | MySQL Community Edition - Standard (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | +-+--+ 5 rows in set (0.00 sec) localhost.(none) with the new version , in a first view, the problem i reported on 20. january 2006, does not occur anymore. thanks for your suggestion an help. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
union/collation problem, error 1267: feature or bug?
Hi mysqllers, 1. following installation localhost.addresses2 show global variables like version%; +-+--+ | Variable_name | Value| +-+--+ | version | 4.1.14-standard | | version_comment | MySQL Community Edition - Standard (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | +-+--+ 4 rows in set (0.00 sec) localhost.addresses2 2. following two tables are involved: localhost.addresses2 describe contacts2; +--+--+--+-+---++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---++ | name | text | YES | | NULL || | firm | text | YES | | NULL || | title| text | YES | | NULL || | phone| text | YES | | NULL || | mail | text | YES | | NULL || | comment | text | YES | | NULL || | status | text | YES | | NULL || | url | text | YES | | NULL || | businesscategory | text | YES | | NULL || | address | text | YES | | NULL || | kanton | text | YES | | NULL || | addon| text | YES | | NULL || | givenname| text | YES | | NULL || | history | text | YES | | NULL || | favorit | text | YES | | NULL || | last_update | timestamp| YES | | CURRENT_TIMESTAMP || | counter | int(10) unsigned | | PRI | NULL | auto_increment | +--+--+--+-+---++ 17 rows in set (0.00 sec) localhost.addresses2 localhost.addresses2 describe tasks2; +--+--+--+-+---++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---++ | task_name| text | YES | | NULL || | actiony | text | YES | | NULL || | date_start | date | YES | | NULL || | date_end | date | YES | | NULL || | date_last_action | date | YES | | NULL || | date_next_action | date | YES | | NULL || | start_time | time | YES | | NULL || | end_time | time | YES | | NULL || | task_address | text | YES | | NULL || | task_comment | text | YES | | NULL || | task_responsible | text | YES | | NULL || | project | text | YES | | NULL || | task_history | text | YES | | NULL || | task_last_update | timestamp| YES | | CURRENT_TIMESTAMP || | contact_link | int(11) | YES | | NULL || | task_counter | int(10) unsigned | | PRI | NULL | auto_increment | +--+--+--+-+---++ 16 rows in set (0.00 sec) localhost.addresses2 3. following character-sets and collations: localhost.addresses2 show session variables like character%; +--++ | Variable_name| Value | +--++ | character_set_client | utf8
from bad into worse
Hi listers, [EMAIL PROTECTED]:~ rpm -qa |grep -i mysql MySQL-server-4.1.14-0 MySQL-shared-4.1.14-0 MySQL-client-4.1.14-0 [EMAIL PROTECTED]:~ [EMAIL PROTECTED]:~ uname -a Linux myhost 2.6.5-7.108-default #1 Wed Aug 25 13:34:40 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ on 12.feb.2005 i reported a problem with the privilege system of mysql. The workaround i had found at that time was the command flush privileges; -- this morning, with the newest version of mysql before version 5, i again did changes in the privilege domain of mysql, using commands of the type revoke... grant... for which, according to the doc, no terminating flush privileges; should be necessary. -- But, alas, clients complained that they got the following error when trying to connect to their database: MySQL Error Number: 1130: Host 'myhost.mydomain.com' is not allowed to connect to this MySQL server which, from the shell client, looks like: [EMAIL PROTECTED]:~ mysql -h myhost -u myuser -p Enter password: ERROR 1130 (0): #HY000Host 'myhost.mydomain.com' is not allowed to connect to this MySQL server [EMAIL PROTECTED]:~ i then did the flush privileges; and everything again workes fine. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: where mysql_config?
Hi this is the error message you get when trying to access mysql from php using a userid which has been created under mysql 4.1 whithout taking the precaution to apply the old_password function to create the password: Client does not support authentication protocol requested by server; consider upgrading MySQL client suomi Hi listers i am fiddling around with the problem of not being able to access mysql 4.1.* from php with new users created in mysql 4.1. no official statement can be found nor from mysql nor from php concerning this problem. both just avoid to talk about it. the only hint i found is create php with the mysqli api. but to create this type of api i need a program called mysql_config, which is said to come with any mysql distro later than 4.1. this is not true. it is not included in mysql 4.1.9 which i am currently using. in the news, everyone was talking about mysql_config, nowone knew where to find it. so, where is it? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where mysql_config?
Hi listers i am fiddling around with the problem of not being able to access mysql 4.1.* from php with new users created in mysql 4.1. no official statement can be found nor from mysql nor from php concerning this problem. both just avoid to talk about it. the only hint i found is create php with the mysqli api. but to create this type of api i need a program called mysql_config, which is said to come with any mysql distro later than 4.1. this is not true. it is not included in mysql 4.1.9 which i am currently using. in the news, everyone was talking about mysql_config, nowone knew where to find it. so, where is it? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timediff and subtime: when is the result negativ?
Hi listers, could you please explain: mysql select timediff(10:00:00, 12:13:14) - ; +--+ | timediff(10:00:00, 12:13:14) | +--+ | -02:13:14| +--+ 1 row in set (0.00 sec) mysql select subtime(10:00:00, 12:13:14); +-+ | subtime(10:00:00, 12:13:14) | +-+ | -02:13:14 | +-+ 1 row in set (0.00 sec) mysql select version(); ++ | version() | ++ | 4.1.9-standard | ++ 1 row in set (0.00 sec) mysql why is the result of timediff negativ? timediff in the doc (quote) returns the time between the start time expr and the end time expr2. expr and expr2 are time or date-and-time expressions, but both must be of the same type(unquote) the difference between start- and end-time, that is in our case start-time 10:00:00 and end_time 12:13:14. in common sense, a difference in time is considered postiv, if the end-time is later than the start time, otherwise negativ. is this not so in mysql? talking about subtime (quote)subtracts expr2 from expr and returns the result. expr is a time or datetime expression, and expr2 is a time expression.(unquote): the result is correct, i.e. negativ. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi turns me crazy: no reply button found in your ... mailing-list. Hello. Please send us an ouput of the following statement, which you should perform in the middle of your test case: show grants for current_user(); THIS IS THE OUTPUT: DROP TABLE fai_accounts DROP TABLE fai_contacts DROP TABLE fai_knowledge DROP TABLE fai_tasks DROP TABLE fai_timer DELETE FROM participants WHERE ident = 'fai' DELETE FROM part_val WHERE ident = 'fai' UPDATE SET activ = 'inactiv' WHERE ident = 'fai' Query 'UPDATE SET activ = 'inactiv' WHERE ident = 'fai'' failed. MySQL Error Number: 1064: 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 'SET activ = 'inactiv' WHERE ident = 'fai'' at line 1 REVOKE all ON fai_accounts FROM 'fai'@'mydom.tld' MySQL Error Number: 1064: 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 'SET activ = 'inactiv' WHERE ident = 'fai'' at line 1 show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_contacts FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_knowledge FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_tasks FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_timer FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_files FROM 'fai'@'mydom.tld' Query 'REVOKE all ON fai_files FROM 'fai'@'mydom.tld'' failed. MySQL Error Number: 1147: There is no such grant defined for user 'fai' on host 'mydom.tld' on table 'fai_files' show grants for current_user() MySQL Error Number: 1147: There is no such grant defined for user 'fai' on host 'mydom.tld' on table 'fai_files' Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select ON participants FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select,update ON part_val FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE GRANT OPTION ON *.* FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION DROP USER 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION and after that i get [EMAIL PROTECTED]:~ mysql -h mydom.tld -u root -p Enter password: ERROR 1130 (0): Host 'mydom.tld' is not allowed to connect to this MySQL server [EMAIL PROTECTED]:~ and when i do a (in a valid root window of course) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql i can login again: [EMAIL PROTECTED]:~ mysql -h mydom.tld -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 113 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi in the meantime i installed mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.07 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ shows exactly the same behaviour as the older version: DROP TABLE fili_accounts DROP TABLE fili_contacts DROP TABLE fili_knowledge DROP TABLE fili_tasks DROP TABLE fili_timer DELETE FROM participants WHERE ident = 'fili' DELETE FROM part_val WHERE ident = 'fili' UPDATE SET activ = 'inactiv' WHERE ident = 'fili' Query 'UPDATE SET activ = 'inactiv' WHERE ident = 'fili'' failed. MySQL Error Number: 1064: 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 'SET activ = 'inactiv' WHERE ident = 'fili'' at line 1 REVOKE all ON fili_accounts FROM 'fili'@'mydom.tld' MySQL Error Number: 1064: 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 'SET activ = 'inactiv' WHERE ident = 'fili'' at line 1 show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_contacts FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_knowledge FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_tasks FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_timer FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_files FROM 'fili'@'mydom.tld' Query 'REVOKE all ON fili_files FROM 'fili'@'mydom.tld'' failed. MySQL Error Number: 1147: There is no such grant defined for user 'fili' on host 'mydom.tld' on table 'fili_files' show grants for current_user() MySQL Error Number: 1147: There is no such grant defined for user 'fili' on host 'mydom.tld' on table 'fili_files' Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select ON participants FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select,update ON part_val FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE GRANT OPTION ON *.* FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION DROP USER 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION and after that [EMAIL PROTECTED]:~ mysql -h mydom -u root -p Enter password: ERROR 1130 (0): Host 'rosetta.ayni.com' is not allowed to connect to this MySQL server [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ and then doing a mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql which makes me again go into mysql: [EMAIL PROTECTED]:~ mysql -h mydom -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.1.9-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
one hour is/is not 60 minutes, that's the question...
Hi again mysql-listers mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.00 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux mydom 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ mysql select addtime(now(), '00:00:00'); ++ | addtime(now(), '00:00:00') | ++ | 2005-02-15 16:49:17| ++ 1 row in set (0.00 sec) mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) mysql select addtime(now(), '01:00:00'); ++ | addtime(now(), '01:00:00') | ++ | 2005-02-15 17:50:27| ++ 1 row in set (0.00 sec) in my opinion the result of the second and third example above must be the same. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi again mysql-listers regarding the problem i mentionned this morning: for a description check out my message of this morning. in the meantime i have found out 1. how to reproduce the problem 2. how to work around the problem 1. how to reproduce: DROP TABLE afir_accounts ; DROP TABLE afir_contacts ; DROP TABLE afir_knowledge ; DROP TABLE afir_tasks ; DROP TABLE afir_timer ; DELETE FROM participants WHERE ident = 'afir' ; DELETE FROM part_val WHERE ident = 'afir' ; REVOKE all ON afir_accounts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_contacts FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_knowledge FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_tasks FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_timer FROM 'afir'@'myhost.tld' ; REVOKE all ON afir_files FROM 'afir'@'myhost.tld' ; REVOKE select ON participants FROM 'afir'@'myhost.tld' ; REVOKE select,update ON part_val FROM 'afir'@'myhost.tld' ; REVOKE GRANT OPTION ON *.* FROM 'afir'@'myhost.tld' ; DROP USER 'afir'@'myhost.tld'; you may substitute whatever string for afir. after such a sequence of commands the root-userid i described in my message earlier today cannot be used to access the mysql db anymore. you get the error message host myhost.tld is not allowed to access this mysql server. 2. how to work around i just added FLUSH PRIVILEGES ; to the above sequence, and everything works well. this command, however, according to mysql docu, should not be necessary after such a sequence of commands. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
