Re: Can't get a login shell for some databases
Pam Astor schrieb: Hi, I have about a half dozen small databases associated with a couple of small shopping carts, discussion forums, etc. All six databases have usernames and passwords associated with them, and all the databases are connected to the php based forums and shopping carts, no problems with the php applications reading and writing data to them. I set the databases up a few weeks ago, the first one I set up, I can’t remember exactly how I set it up. Here is what I don’t understand. I’m able to login as root, and also the very first database I set up, I am able to log in to MySQL via shell using the username and password associated with that user. However for all the other databases, I am not able to log in to a MySQL shell using the other usernames associated with their databases – even though the php applications are configured to use the usernames, passwords and database names for those users which I can not log in to get a shell MySQL session. How is it that my php applications can log in to MySQL and I can’t get a terminal connection to them? I’m sure it’s something about granting a login shell but how would I do that? connectiong from shell means connecting as localhost by default, connecting from PHP can be some different server and/or PHP uses the full IP address/hostname of the server check/compare the privileges for your users for 'localhost' and '%' -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
History of changed rows
Hi all. How can we manage the history of changed rows in the database. I have some idea but not yet implemented. By using triggers to make a copy of the row being changed to the another table in the same db. Is there any way to only save the changed fields data and field name? Any other idea? Thanks CPK -- Keep your Environment clean and green.
mysql logfile configuration
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi @all, ich have configured in /etc/my.cf as follow: [...] log=/var/log/mysqld.log [...] Now, mysqld writes every query to the log file. How can I avoid the queries in the log file? I want to see any action done by mysql, but not the queries. Thx, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQEVAwUBSAh3Hds3frmum9swAQIZbgf7B40LUiqjZchb2fyefyy9mNjxEIeM/ftt tBVIjnrVVesyJo6FZJdZo3tlfNh3hkrrNIqTbR6Dy5P2sDRZ+4GIyMs+MEX9A4dw NBjaMoj648HyQivjomJYe0vwWBKF+UmTM2qOsQEQi1AArJtg33mBGTNOA++6HigX TpVGkn991+AyTUt+JYXDXOyHOjaLoSz/AhWSjvz5kM5lgyZPcssDKWVDzWDAOtrO KTnYsby59+ctK3h61tkAecJeAElZq0JnMlalJlwXnIbcolGcO59Z/gC0U80VMYyf kGdRzHbegCVd+r2M1haLdk6Ygunk/3KQa/GW+gqhuYjRMpCcudJqkw== =el3Y -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: History of changed rows
No problem.
I do this using three triggers on Insert, Update and Delete. Then
update a log file who's schema starts:
CREATE TABLE ?_log (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
transact ENUM ('I','U','D') NOT NULL,
key_from_table ??? NOT NULL,
KEY (key_from_table),
field_1 ?? ,
field_2 ??,
...
field_n ??
I don't know a way of copying over every field accept long-hand in the
triggers.
Hope this is useful...
Ben
C K wrote:
Hi all.
How can we manage the history of changed rows in the database. I have some
idea but not yet implemented. By using triggers to make a copy of the row
being changed to the another table in the same db. Is there any way to only
save the changed fields data and field name? Any other idea?
Thanks
CPK
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Slave
Hi Is there a way to find out when the MySQL Slave has been restarted. Thanks and Regards Kaushal
data truncation warnings by special characters
Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor
RE: Can't get a login shell for some databases
connectiong from shell means connecting as localhost by default, connecting from PHP can be some different server and/or PHP uses the full IP address/hostname of the server check/compare the privileges for your users for 'localhost' and '%' I ran “SELECT * from mysql.user;” the command generated a list. For root it showed in the host column local host, the second row for root showed the name of the web server the mysql install is on. The third and fourth rows showed no users, however the third and fourth rows showed in the host columns localhost and the server name, respectively. The fifth and sixth lines showed the first non root user I created and for this user it showed in the host Colum the % character, and the other line for this user showed localhost as the host. The seventh line shows the second non root user I created – it has just one line and shows localhost as the host. All the rest of the users I created show the % character in the host column. No IP addresses are listed anywhere. _ More immediate than e-mail? Get instant access with Windows Live Messenger. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_instantaccess_042008
RE: Performance problem
I`ve resolved my problems without hardware manipulation. Thanks to all. -Mensaje original- De: Francisco Rodrigo Cortinas Maseda Enviado el: miércoles 16 de abril de 2008 18:57 Para: mysql@lists.mysql.com Asunto: RV: Performance problem Hi all, im new on the performance tuning of this database (MySQL 5.0.45, rpm-based installation), and i have one performance problem on our new installation: - The radius servers (that are written on perl) we have are writing the auth and acct log to one mysql database. The conn we have is an TCPIP conn. - We have two databases, one for auth data and another for acct data. - We have one table for each day on each database, on which we insert the auth and acct data. We also have three indexes on each table, that occupy almost 300M per day. - The volume of traffic is nearly 10 million rows per day. - The partition of the database is mounted on a LVM partition of a RAID1 disk. We are experiencing problems about the performance of the database, in the way that we are seeing that the radius clients are seeing the radius servers gone away for the acct service. The server of the database is a Dell Poweredge 1855 with 6 GB of RAM and RHEL4. We have modified the variables of the database with: SET GLOBAL thread_cache_size=8; SET GLOBAL table_cache=256; set GLOBAL max_connections=200; set GLOBAL key_buffer_size=1610416128; set GLOBAL read_buffer_size=524288; set GLOBAL read_rnd_buffer_size=1048576; SET GLOBAL delayed_insert_limit=400; SET GLOBAL delayed_queue_size=12000; SET GLOBAL net_buffer_length=32768; The queries that we are doing are: INSERT DELAYED IGNORE () VALUES (); Originally, the server has 2GB of RAM, but seeing this problems, we have installed another 4 GB of RAM. From the statistics of vmstat we see that we are suffering som IO bottleneck (i think): procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 4 0 4280956 40144 139245600 014 1853 1180 1 0 48 50 0 3 0 4279932 40152 139348800 010 1882 1258 2 0 42 56 0 3 0 4279908 40172 139450800 0 2052 1861 1202 2 1 45 52 0 4 0 4276452 40192 139552800 0 9179 1850 1164 2 1 66 31 1 3 0 4274748 40200 139630000 0 7 1957 1337 2 1 64 34 0 4 0 4272956 40212 139732800 024 1926 1283 2 1 41 56 0 3 0 4271484 40224 139861600 026 1906 1250 2 1 32 66 0 3 0 4270204 40228 139965200 0 9 1855 1154 2 0 24 74 0 3 0 4268924 40236 140016400 010 1852 1144 2 0 24 74 1 4 0 4267516 40248 140145200 013 2063 1480 2 1 27 71 0 3 0 4264476 40280 140272000 0 11134 1965 1363 2 1 49 48 0 4 0 4262772 40300 140374000 013 1971 1382 2 0 60 37 0 4 0 4261372 40316 140476400 015 1875 1213 2 1 46 52 0 3 0 4260028 40328 140553200 014 1831 1152 2 0 48 50 The wa column shows a quite large number, so we think that it is an IO bottleneck. The question is: ¿ has anybody have seesomething similar? ¿has anybody an idea about how to resolve this problem? Thanks. Antes de imprimir este e-mail piense bien si es necesario hacerlo. * Este mensaje es privado y CONFIDENCIAL y se dirige exclusivamente a su destinatario. Si usted ha recibido este mensaje por error, no debe revelar, copiar, distribuir o usarlo en ningún sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y cualquier documento adjunto que pudiera contener. El correo electrónico via Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepción. JAZZTEL no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilización del correo electrónico via Internet y la grabación de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinión expresada en este mensaje pertenece únicamente al autor remitente, y no representa necesariamente la opinión de JAZZTEL, a no ser que expresamente se diga y el remitente esté autorizado para hacerlo. * This message is private and CONFIDENTIAL and it is intended exclusively for its addressee. If you receive this message in error, you should not disclose, copy, distribute this e-mail or use it in any other way. Please inform the sender and delete the message and attachments from your system.Internet e-mail neither guarantees the confidentiality nor the integrity or proper receipt of the messages sent. JAZZTEL does not assume any liability for those circumstances. If the addressee of this message does not consent to the use of Internet e-mail and message recording, please notify us
Re: History of changed rows
C K wrote: Hi all. How can we manage the history of changed rows in the database. I have some idea but not yet implemented. By using triggers to make a copy of the row being changed to the another table in the same db. Is there any way to only save the changed fields data and field name? Any other idea? Thanks CPK How about mysqlbinlog? :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get a login shell for some databases
Pam Astor schrieb: connectiong from shell means connecting as localhost by default, connecting from PHP can be some different server and/or PHP uses the full IP address/hostname of the server check/compare the privileges for your users for 'localhost' and '%' [...] The seventh line shows the second non root user I created – it has just one line and shows localhost as the host. All the rest of the users I created show the % character in the host column. would be much more easier if you would send this output here (with faked names, passwords and hosts ...) -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Slave
Hi Is there a way to find out when the MySQL Slave has been restarted. Thanks and Regards Kaushal You can determine the server actions by looking through the slave log file. David
RE: data truncation warnings by special characters
-Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 8:42 AM To: mysql@lists.mysql.com Subject: data truncation warnings by special characters Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. [JS] This sounds like it is related to the problems I've been having. I think I've come to an understanding of my issues, but I am not using LOAD DATA INFILE. I'll be posting what I have learnt, once I get a chance, but I don't know if it will help you. What is the source of your input data? Windows? Linux? The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get a login shell for some databases
A user in MySql is not just a username, but a username and a host. The host of '%' denotes all hosts accept 'localhost'. Therefore you usually require two entries for each user: CREATE USER ben@'%' INDENTIFIED BY 'ben'; CREATE USER ben@'localhost' INDENTIFIED BY 'ben'; Does this help? Ben Pam Astor wrote: connectiong from shell means connecting as localhost by default, connecting from PHP can be some different server and/or PHP uses the full IP address/hostname of the server check/compare the privileges for your users for 'localhost' and '%' I ran “SELECT * from mysql.user;” the command generated a list. For root it showed in the host column local host, the second row for root showed the name of the web server the mysql install is on. The third and fourth rows showed no users, however the third and fourth rows showed in the host columns localhost and the server name, respectively. The fifth and sixth lines showed the first non root user I created and for this user it showed in the host Colum the % character, and the other line for this user showed localhost as the host. The seventh line shows the second non root user I created – it has just one line and shows localhost as the host. All the rest of the users I created show the % character in the host column. No IP addresses are listed anywhere. _ More immediate than e-mail? Get instant access with Windows Live Messenger. http://www.windowslive.com/messenger/overview.html?ocid=TXT_TAGLM_WL_Refresh_instantaccess_042008 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: data truncation warnings by special characters
-Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 8:42 AM To: mysql@lists.mysql.com Subject: data truncation warnings by special characters Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. [JS] I should have mentioned that when I tried the same operations using server version 5.0.45, I got a much more meaningful error message: Incorrect string value: '\x96 Urug...' for column 'prod_title' at row 1 Are you running a 4.x server? The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Stored Procedure error 1307
I'm attempting to create a MySQL stored procedure, however, when I attempt to create even a simple Hello, world procedure, I get the following error: ERROR 1307 (HY000): Failed to CREATE PROCEDURE test_procedure_k which is listed as: Error: 1307 SQLSTATE: HY000 (ER_SP_STORE_FAILED) Message: Failed to CREATE %s %s I've googled for the solution, and the only problems I can find are people who haven't upgraded correctly. This is a clean installation, though, and I don't recall any errors when installing. I'm not ruling anything out, but I don't think that's the case. The results of mysqlcheck mysql for the proc tables are: mysql.proc OK mysql.procs_priv OK Anyone have any thoughts, or come across this before? TIA, kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Slave
On Fri, Apr 18, 2008 at 7:06 PM, Ben Clewett [EMAIL PROTECTED] wrote: Check the error.log on both server and client, should show where connected and disconnected. Or write a small program to execute SHOW SLAVE STATUS every minute and log the results. Which I belive is done for you in the MySql dashboard program suit. Ben Kaushal Shriyan wrote: Hi Is there a way to find out when the MySQL Slave has been restarted. Thanks and Regards Kaushal Hi I could see the restart in tail -f /var/log/mysql/mysqld.err file while doing slave stop and slave start, but when i use mk-slave-restart -u root -p test -h localhost --verbose. it gets hung. Any clue Thanks and Regards Kaushal
Re: MySQL Slave
Check the error.log on both server and client, should show where connected and disconnected. Or write a small program to execute SHOW SLAVE STATUS every minute and log the results. Which I belive is done for you in the MySql dashboard program suit. Ben Kaushal Shriyan wrote: Hi Is there a way to find out when the MySQL Slave has been restarted. Thanks and Regards Kaushal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can't get a login shell for some databases
MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] would be much more easier if you would send this output here (with faked names, passwords and hosts ...) OK...here it is: SELECT * from mysql.user; +-+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--+--++-+--+---+-+-+--+ | Host| User| Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | +-+-+--+-+-+-+-+-+---+-+---+--+---++-+++--++---+--+--+-+--+--++-++--+--++-+--+---+-+-+--+ | localhost | root| 076d41f46bee2ec3 | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y| Y| Y | Y | Y | Y | || | | 0 | 0 | 0 |0 | | myhost.net | root| | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | Y | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y| Y| Y | Y | Y | Y | || | | 0 | 0 | 0 |0 | | myhost.net | | | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N| N| N | N | N | N | || | | 0 | 0 | 0 |0 | | localhost | | | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N| N| N | N | N | N | || | | 0 | 0 | 0 |0 | | % | user1 | 6827b0f45e06bf7d | N | N | N | N | N | N | N | N | N| N | N | N | N | N | N| N | N | N| N| N | N| N| N | N | N | N | || | | 0 | 0 | 0 |0 | |
RE: Can't get a login shell for some databases
A user in MySql is not just a username, but a username and a host. The host of '%' denotes all hosts accept 'localhost'. Therefore you usually require two entries for each user: CREATE USER ben@'%' INDENTIFIED BY 'ben'; CREATE USER ben@'localhost' INDENTIFIED BY 'ben'; Does this help? Makes sense, BUT, isn't the info between the last set of tickmarks '' the password for the user? When I created the users, I ran the command: grant usage on db1.* to joe identified by 'whateverpassword'; Then I ran a second grant command: grant select, drop, etc, on db1.* to joe; I should probally say that my 5.0.22 MySQL database is on a centos 5.1 box. _ Pack up or back up–use SkyDrive to transfer files or keep extra copies. Learn how. http://www.windowslive.com/skydrive/overview.html?ocid=TXT_TAGLM_WL_Refresh_skydrive_packup_042008
Re: mysql logfile configuration
just comment this parameter, all actions done by mysql will be in hostnam.err file On 4/18/08, Uwe Kiewel [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi @all, ich have configured in /etc/my.cf as follow: [...] log=/var/log/mysqld.log [...] Now, mysqld writes every query to the log file. How can I avoid the queries in the log file? I want to see any action done by mysql, but not the queries. Thx, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQEVAwUBSAh3Hds3frmum9swAQIZbgf7B40LUiqjZchb2fyefyy9mNjxEIeM/ftt tBVIjnrVVesyJo6FZJdZo3tlfNh3hkrrNIqTbR6Dy5P2sDRZ+4GIyMs+MEX9A4dw NBjaMoj648HyQivjomJYe0vwWBKF+UmTM2qOsQEQi1AArJtg33mBGTNOA++6HigX TpVGkn991+AyTUt+JYXDXOyHOjaLoSz/AhWSjvz5kM5lgyZPcssDKWVDzWDAOtrO KTnYsby59+ctK3h61tkAecJeAElZq0JnMlalJlwXnIbcolGcO59Z/gC0U80VMYyf kGdRzHbegCVd+r2M1haLdk6Ygunk/3KQa/GW+gqhuYjRMpCcudJqkw== =el3Y -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Stored Procedure error 1307
can u please try this a root user mysql -uroot regards anandkl On 4/18/08, kabel [EMAIL PROTECTED] wrote: I'm attempting to create a MySQL stored procedure, however, when I attempt to create even a simple Hello, world procedure, I get the following error: ERROR 1307 (HY000): Failed to CREATE PROCEDURE test_procedure_k which is listed as: Error: 1307 SQLSTATE: HY000 (ER_SP_STORE_FAILED) Message: Failed to CREATE %s %s I've googled for the solution, and the only problems I can find are people who haven't upgraded correctly. This is a clean installation, though, and I don't recall any errors when installing. I'm not ruling anything out, but I don't think that's the case. The results of mysqlcheck mysql for the proc tables are: mysql.proc OK mysql.procs_priv OK Anyone have any thoughts, or come across this before? TIA, kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get a login shell for some databases
Pam Astor wrote: A user in MySql is not just a username, but a username and a host. The host of '%' denotes all hosts accept 'localhost'. Therefore you usually require two entries for each user: CREATE USER ben@'%' INDENTIFIED BY 'ben'; CREATE USER ben@'localhost' INDENTIFIED BY 'ben'; Does this help? Makes sense, BUT, isn't the info between the last set of tickmarks '' the password for the user? Yes. When I created the users, I ran the command: grant usage on db1.* to joe identified by 'whateverpassword'; Then I ran a second grant command: grant select, drop, etc, on db1.* to joe; The ANSI-SQL syntax is to just use GRANT to create users. You will still need to use GRANT twice for both users: joe@'%' and joe@'localhost'. But I find the MySql syntax for creating user with CREATE USER and then GRANT easier to follow: CREATE USER joe@'%' IDENTIFIED BY 'whateverpassword'; CREATE USER joe@'localhost' IDENTIFIED BY 'whateverpassword'; GRANT ALL ON db1.* TO joe@'%'; GRANT ALL ON db1.* TO joe@'localhost'; If you see what I mean... Just remember that a user always has a host, and you should always use the two together. Ben I should probally say that my 5.0.22 MySQL database is on a centos 5.1 box. _ Pack up or back up–use SkyDrive to transfer files or keep extra copies. Learn how. http://www.windowslive.com/skydrive/overview.html?ocid=TXT_TAGLM_WL_Refresh_skydrive_packup_042008 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Stored Procedure error 1307
On Friday 18 April 2008 10:26:16 Ananda Kumar wrote: can u please try this a root user mysql -uroot regards anandkl Same result, unfortunately. kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A SQL Query Question
userId long picture MeduimBlob datePosted DateTime A userId can have many pictures posted. I want to write a query that returns a distinct userId along with the most recent picture posted. Can someone suggest an elegant and fast query to accomplish this? Latest pic for user N: SELECT userID,MAX(dateposted) FROM tbl WHERE userID=N; Latest pics per user: SELECT t1.userID,t1.dateposted FROM tbl t1 LEFT JOIN tbl t2 ON t1.userID=t2.userID AND t1.datepostedt2.dateposted WHERE t2.userID IS NULL; PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimize mysql table's physical storage
In the last episode (Apr 18), tech user said: My mysql table has been created for long time, it increases day by day, and become huge. Right now a full scan to the table for the first time is very slow. So I was thinking to optimize it. This table is stored in many non-sequential disk fragments I think. I want to make this table to be stored in disk with the sequential fragments.That will increase the scan speed. Is there any tool to do it? Thanks in advance. If you are solely concerned with filesystem fragmentation, shut MySQL down, copy your .MYI and .MYD (or .ibd if you're using innodb file-per-table) files to a temporary directory, delete the originals, and move your new files into their place. Or if you're running Windows, simply run its disk defragmenter :) If you have deleted/inserted many rows in your table and are using a variable-length table (pretty much any table with VARCHARs), then you may also have internal table fragmentation. The OPTIMIZE TABLE command will compact your file by copying the row data to a new temporary table, rebuilding the indexes, and deleting the original table. http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: History of changed rows
CK, How can we manage the history of changed rows in the database. Point-in-time architecture. For a bit of discussion see http://www.artfulsoftware.com/infotree/tip.php?id=547 PB - C K wrote: Hi all. How can we manage the history of changed rows in the database. I have some idea but not yet implemented. By using triggers to make a copy of the row being changed to the another table in the same db. Is there any way to only save the changed fields data and field name? Any other idea? Thanks CPK No virus found in this incoming message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.23.1/1385 - Release Date: 4/18/2008 9:30 AM
RE: Performance problem
On Fri, 18 Apr 2008, Francisco Rodrigo Cortinas Maseda [EMAIL PROTECTED] wrote: im new on the performance tuning of this database (MySQL 5.0.45, rpm-based installation), and i have one performance problem on our new installation: ... We are experiencing problems about the performance of the database, in the way that we are seeing that the radius clients are seeing the radius servers gone away for the acct service. I`ve resolved my problems without hardware manipulation. Me, I'd not like to see much technical detail, but I'm curious now: what sorts of things did you do? Restructuring, different queries, what? -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication for auto-increment tables
Hi, Has anyone got mysql master-slave replication setup on v4.1. Were you able to get tables with auto_increment update properly to slave ? If yes, please let me know. I need some advise on how to set it up to work well. I get stuck at duplicate errors quite often, and those are not real duplicates, its just that its id on slave was already occupied by some previous entry!! I see mysql 5 has options like: auto-increment-increment auto-increment-offset , but with v4.1 Any help is appreciated. Thanks!
Working with Images
Hi;
The python code works properly, so I assume this is a strictly MySQL
question now :)
If I grab an image in the database thus:
sql = select pic1 from products where id=' + str(id) + ';
cursor.execute(sql)
pic1 = cursor.fetchall()[0][0].tostring()
# pic1 = cursor.fetchall()[0][0] // either this or the above line
and try and re-insert it thus:
cursor.execute('update products set pic1=%s where id=%s, ;',
(pic1, id))
it tells me I have an error in my MySQL syntax. What is the error?
TIA,
Victor
RE: Can't get a login shell for some databases
The ANSI-SQL syntax is to just use GRANT to create users. You will still need to use GRANT twice for both users: joe@'%' and joe@'localhost'. But I find the MySql syntax for creating user with CREATE USER and then GRANT easier to follow: CREATE USER joe@'%' IDENTIFIED BY 'whateverpassword'; CREATE USER joe@'localhost' IDENTIFIED BY 'whateverpassword'; GRANT ALL ON db1.* TO joe@'%'; GRANT ALL ON db1.* TO joe@'localhost'; If you see what I mean... Just remember that a user always has a host, and you should always use the two together. Ben Thanks Ben, OK Got it, One more thing, I have already created these users and don't want to mess their passwords up or break their associated php scripts access. So how do I grant users, who already have a password, localhost access? _ Going green? See the top 12 foods to eat organic. http://green.msn.com/galleries/photos/photos.aspx?gid=164ocid=T003MSN51N1653A
Re: History of changed rows
On Fri, Apr 18, 2008 at 8:13 AM, Peter Brawley [EMAIL PROTECTED] wrote: CK, How can we manage the history of changed rows in the database. Point-in-time architecture. For a bit of discussion see http://www.artfulsoftware.com/infotree/tip.php?id=547 PB I have used this convention and it works well. I have a real problem with how it suggests using NULL. NULL is undefined, and they are basically abusing it to mean infinite positive. NULL has issues being indexed, big datetime values don't. At the recent postgres convention I eavesdropped on a discussion about a possible new temporal range format which would basically consist of a start date and an end date and preclude any other entries from overlapping. Such a format would be ideal for PITA, but the start and end would need to be not null... -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim)
Re: data truncation warnings by special characters
Hi Jerry, Sorry, I should have mentioned that I use Windows XP with MySQL 5.0.15nt. The script giving errors now is used once a year, and last year without any warning. Since last year all I changed was adding in my.ini: # in [client] part default-character-set=utf8 # in [mysqld] part default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci When I undo these changes, and stop and start mysql, the errors remain. The Data.txt file (from an external source) looks okay with Wordpad. TIA, Cor - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'C.R.Vegelin' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, April 18, 2008 2:30 PM Subject: RE: data truncation warnings by special characters -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 8:42 AM To: mysql@lists.mysql.com Subject: data truncation warnings by special characters Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. [JS] This sounds like it is related to the problems I've been having. I think I've come to an understanding of my issues, but I am not using LOAD DATA INFILE. I'll be posting what I have learnt, once I get a chance, but I don't know if it will help you. What is the source of your input data? Windows? Linux? The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data truncation warnings by special characters
I have some php code I use to import data that is a bit more flexible and robust than the load data statement in MySQL If you use php I can share the code with you. C.R.Vegelin wrote: Hi Jerry, Sorry, I should have mentioned that I use Windows XP with MySQL 5.0.15nt. The script giving errors now is used once a year, and last year without any warning. Since last year all I changed was adding in my.ini: # in [client] part default-character-set=utf8 # in [mysqld] part default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci When I undo these changes, and stop and start mysql, the errors remain. The Data.txt file (from an external source) looks okay with Wordpad. TIA, Cor - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'C.R.Vegelin' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, April 18, 2008 2:30 PM Subject: RE: data truncation warnings by special characters -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 8:42 AM To: mysql@lists.mysql.com Subject: data truncation warnings by special characters Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. [JS] This sounds like it is related to the problems I've been having. I think I've come to an understanding of my issues, but I am not using LOAD DATA INFILE. I'll be posting what I have learnt, once I get a chance, but I don't know if it will help you. What is the source of your input data? Windows? Linux? The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data truncation warnings by special characters [SOLVED]
Thanks Chris, Well the script in question is a MySQL script to load a MySQL database. Currently I use only PHP only for database retrieval. But after trial and error I managed to find a 'work around', as follows: a) CREATE TABLE tmp with ENGINE = MyISAM CHARSET = LATIN1; b) LOAD DATA INFILE statement; = no truncation warnings c) ALTER TABLE tmp CONVERT TO CHARACTER SET UTF8; Thanks again and a nice weekend. Cor - Original Message - From: Chris W [EMAIL PROTECTED] To: MYSQL General List mysql@lists.mysql.com Sent: Friday, April 18, 2008 8:38 PM Subject: Re: data truncation warnings by special characters I have some php code I use to import data that is a bit more flexible and robust than the load data statement in MySQL If you use php I can share the code with you. C.R.Vegelin wrote: Hi Jerry, Sorry, I should have mentioned that I use Windows XP with MySQL 5.0.15nt. The script giving errors now is used once a year, and last year without any warning. Since last year all I changed was adding in my.ini: # in [client] part default-character-set=utf8 # in [mysqld] part default-character-set=utf8 character-set-server = utf8 collation-server = utf8_general_ci When I undo these changes, and stop and start mysql, the errors remain. The Data.txt file (from an external source) looks okay with Wordpad. TIA, Cor - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'C.R.Vegelin' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, April 18, 2008 2:30 PM Subject: RE: data truncation warnings by special characters -Original Message- From: C.R.Vegelin [mailto:[EMAIL PROTECTED] Sent: Friday, April 18, 2008 8:42 AM To: mysql@lists.mysql.com Subject: data truncation warnings by special characters Hi List, I get strange Data truncated for column Description warnings when loading a tab separated file with special characters. [JS] This sounds like it is related to the problems I've been having. I think I've come to an understanding of my issues, but I am not using LOAD DATA INFILE. I'll be posting what I have learnt, once I get a chance, but I don't know if it will help you. What is the source of your input data? Windows? Linux? The definition of the target table is: CREATE TEMPORARY TABLE tmp ( Code CHAR(8) NOT NULL, Description TEXT NOT NULL, KEY Code (Code) ) ENGINE = MyISAM CHARSET=UTF8; The Load Into statement I use is: LOAD DATA INFILE 'D:/Input/Data.txt' INTO TABLE tmp FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES; The truncation warnings are caused by words like PURÉES. This leads to a truncted Description field like: Jams, Jellies, Pur. Any idea how to solve this ? Thanks in advance, Cor -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- 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]
Handling special characters
This isn't really a problem with MySQL; MySQL just trips on it. Let me caution you that this discussion applies to Windows. I did observe this in my applications on Linux, but the problem will show itself only when reading a file, whether uploaded or not; there will be no problem if the data is entered into a form in a web browser. I did most of my testing with 0x96, the Windows n-dash. - Trying to INSERT a funky character will give you a data truncated message from a 4.x server, or a complaint about the character not being valid in the target character set from a 5.x server. The error message from the 5.x server will point you directly to the problem character. - The ultimate cause of the problem is that Windows applications use the CP-1252 character set. It is similar to, but not identical to, UTF-8. - MySQL does not recognize the CP-1252 character set, at least not out of the box. Therefore you cannot get MySQL to convert CP-1252 characters to UTF-8. You could try CP-1251, I didn't test that. - Sourcing a file containing one of these troublesome characters into the MySQL CLI will trigger the problem, because the data is sucked in as-is. - Copying and pasting into the CLI will sometimes avoid the problem because Windows automatically transliterates some characters during that operation. - Windows does NOT TRANSLATE the characters. Note that I said transliterates. The 0x96 character, the CP-1252 n-dash, is silently changed to 0x2D: a hyphen when you paste it into a console application. - The reason that data entered into a web form works is that IE (and I suppose other browsers) maps the CP-1252 characters into their corresponding UTF-8 characters. Thus 0x96, when pasted into a browser, turns into 0xe28093 when pushed back to the server. That three-byte sequence is the UTF-8 n-dash character. What we have chosen to do is to transliterate the most common of these troublesome characters ourselves. In PHP, when working with a file it looks like this: $cp1252_special_chars = array( \x96 = -, \x97 = --, \x91 = \\', \x92 = \\', \x85 = ..., \x93 = \, \x94 = \ ); On the web server side (UTF-8) it looks like this: $utf_special_chars = array( \xe2\x80\x93 = -, \xe2\x80\x94 = --, \xe2\x80\x98 = \\', \xe2\x80\x99 = \\', \xe2\x80\xa6 = ..., \xe2\x80\x9c = \, \xe2\x80\x9d = \ ); This is not a perfect solution for everyone, but it suits our needs. The data we work with comes in higgledy-piggledy, and we want it consistent whether someone sends us a data file or pastes a string into one of our forms. If somebody (not I, given my level of knowledge and available time) adds CP-1252 to the character sets available with MySQL, then you could use CONVERT() with USING to handle this (assuming that you knew ahead of time where your data was coming from). I hope this helps, I grew a few rings and shed a lot of bark to get here. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can't get a login shell for some databases
One more thing, I have already created these users and don't want to mess their passwords up or break their associated php scripts access. So how do I grant users, who already have a password, localhost access? just copy the row in the mysql table -- Sebastian Mendel Not sure what you mean - which table? Which row? _ Use video conversation to talk face-to-face with Windows Live Messenger. http://www.windowslive.com/messenger/connect_your_way.html?ocid=TXT_TAGLM_WL_Refresh_messenger_video_042008 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
