Re: Aes Encryption
On Thursday 10 February 2005 19:00, love wrote: Has any body implemented Aes encryption while storing critical data in mysql? I want to know the logic you are implementing to store your passwords to encrypt/decrypt data. Love Kumar Love Kumar wrote: I think this question could not be answered globally. What exactly do you want to store where, and who should or should not be able to read the data? Is it that you want to store something secret into a table which should only be readable by a particular mysql-user? bh -- Bernhard Fischer [EMAIL PROTECTED] Telekommunikation und Medien Fachhochschule St. Plten/St. Poelten University of Applied Sciences Herzogenburger Strae 68 | 3100 St. Plten | +43 (0) 2742 313228 48 pgp8rhn0Cg6UW.pgp Description: PGP signature
Re: MySQL as Email database [sort of OT]
Brent Baisley wrote: I am looking to store all incoming emails into a MySQL database. I've looked into dbmail, but can't get it to compile under Mac OSX (I posted a message on that list). I was wondering if anyone could point me in another direction to use MySQL as an email message store. I don't need a webmail interface, just a way of getting messages from a mail server to a MySQL database. Preferably as a direct transfer, but it could be a script that runs periodically. Currently it seems the best path is using Perl, but I would think this has been done before, just can't find it on google. - have a look at http://atmail.com/index.ehtml It's a web-based email system that uses a MySQL database as a back-end. It's written mostly in Perl, and there might be something you can use. The db schema, however, is appalling... - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql hypothetical performance
I think you meant select session from users where user_id = 'X'. Anyway, it doesn't matter how big your table is as long as you build an index on (user_id, session). This way MySQL doesn't have to touch the table for this query. --- Scott Haneda [EMAIL PROTECTED] wrote: I build a few login and password systems in MySql, my general layout is as follows: Users table and account table. The users table has the bare minimum columns in it, id, username, password, session, updated, added. Account table has stuff like first name, last name, email address, street address etc etc. Sometimes upwards of 30 or so fields. My logic in this comes from a past history with Filemaker, which never really supported relations well, relations were each separate databases. The idea was, the less data in each database, the faster you can select it. So with only minimal data in the users database in filemaker, I and not needeing the account data very often, that is how I built it. In a login pass system, I am selecting from the users table on every page load, I only need to select the session field generally. My question is, does it matter? it would be easier to put all the data in one table, but then that one table will have more data in it. I however, will only be selecting one item from that now large table. Summary: is SELECT session from users where session = 'x' faster or slower depending on how many columns are in the table? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql ended problem
In Linux , when I start mysql using below command MYSQL_HOME=`pwd` ./bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.ini --socket=$MYSQL_HOME/mysql.sock --tmpdir=$MYSQL_HOME/tmp --port=13306 --user=root I get a message mysql ended and server gets shutdown It happens in RedHat linux. MySql Version is 4.0.20 I have attached my.ini file with this mail Thanks in Advance for your help. --Yoge #This File was made using the WinMySQLadmin 1.0 Tool #12/27/02 2:51:42 PM #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] #set-variable=long_query_time=1 set-variable=transaction-isolation=READ-UNCOMMITTED #default value for read_buffer is 128KB set-variable= read_buffer=1M #default value is 8MB set-variable= key_buffer=32M #default value for read_buffer is 2MB set-variable= sort_buffer=4M #default value is 8MB set-variable= myisam_sort_buffer_size=16M #default value is 8MB set-variable= bulk_insert_buffer_size=16M #default value is 64 set-variable= table_cache=128 #log-slow-queries=slowquerylogger.log #log-update=updatelogger.log [client] #default password for MySQL password=abc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error 1271 (HY000) - Illegal mix of collations
It means you have a problem with the way either your tables, columns, database, or connection Firstly do a show create table and check that the character set for the table(s) concerned are the same and that any 'character' column has the same set as the table. If you set the default char set at database level and the default on the table is different you'll need to wrap your query with a set character set pair - the first to set it to what you need and the second to set it back to the default. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Eli [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 05:47 To: mysql@lists.mysql.com Subject: Error 1271 (HY000) - Illegal mix of collations Hi, I'm running a query using UNION, where all parts of the union are queries from the same syntax and from tables with same definition, and the select is same too. Each of the union parts is a query with JOINs. I got this error: ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' I tried to look after it on the net, but couldn't find anything meaningful. thanks in advance, -Eli. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1271 (HY000) - Illegal mix of collations
Hello. See: http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html Probably, you should carefully read the parts of manual related to the character sets. See: http://dev.mysql.com/doc/mysql/en/charset.html Eli [EMAIL PROTECTED] wrote: Hi, I'm running a query using UNION, where all parts of the union are queries from the same syntax and from tables with same definition, and the select is same too. Each of the union parts is a query with JOINs. I got this error: ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION' I tried to look after it on the net, but couldn't find anything meaningful. thanks in advance, -Eli. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: error on restting password
Hello.
The entity of the user in MySQL consists of two parts: the user name
and the host from which user connects to MySQL server (can be a '%
- it means any host). Look like your user table doesn't have an entry
for user 'root'@'host_from_you_are_connecting'. You should manually add the
record for user root and your host.
See:
http://dev.mysql.com/doc/mysql/en/access-denied.html
http://dev.mysql.com/doc/mysql/en/privileges.html
fredrich [EMAIL PROTECTED] wrote:
hi all,
i need to reset the root password. i have done 2
things.
but not worked.
i read
http://dev.mysql.com/doc/mysql/en/resetting-permissions.html
and do ..
a). i changed my.ini, add a line like
skip-grant-tables
and started mysql. then issue command ..
mysqladmin -u root flush-privileges password mypass
but error :
mysqladmin: unable to change password; error: 'Can't
find any matching row in the user table'
b). i choose alternative way.
update user set password=PASSWORD('mypass') where
user='root';
flush privileges;
then restart mysql.
the password is changed. but i cannot login when issue
mysql -u root -p. and type mypass
SPEC : win 2003 + mysql 4.1.5.
on spec : WINxp sp 2 + mysql 4.1.9 it works fine.
note : i want to reset password on mysql 4.1.5.
any idea for this ?
thanks.
__
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [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]
Re: Replication issue: I/O thread dies immediately after START SLAVE with no logged errors
Hello. Please send us an output of SHOW MASTER STATUS ans SHOW SLAVE STATUS. Can you reproduce the problem using official binaries? Tierney Thurban [EMAIL PROTECTED] wrote: Hi all. Sorry if you get this twice -- it was posted to mysql-replication earlier, but it doesn't look like that list is really used. I'm having a problem with my replication setup. This is my first time setting up replication, so this may be a simple problem. I'm using one master and one slave, both running debian-testing, and they both have brand new 4.1.9 mysql installs (via apt-get). The problem is that each time I do a START SLAVE, the I/O thread dies almost immediately. I can see it running only if I do START SLAVE; SHOW SLAVE STATUS\G on a single line. The master's log shows the following each time I START SLAVE or START SLAVE IO_THREAD: 6 Connect slave@IP on 6 Query SELECT UNIX_TIMESTAMP() 6 Query SHOW VARIABLES LIKE 'SERVER_ID' 6 Query SELECT @@GLOBAL.COLLATION_SERVER 6 Query SELECT @@GLOBAL.TIME_ZONE 6 Query SHOW SLAVE HOSTS 6 Quit There are no error messages in the .err file on either server, even with --log-warnings on both. I've added everything appropriate that I've been able to find to my.cnf on each server (see below). If anyone has any suggestions, please let me know -- I've been looking through docs and mailing lists for quite some time now, with no luck. Thanks, Tierney Here's what I did to set up replication: I added / changed a number of fields in my.cnf on both machines (see below). Master: Started mysqld. Created a new database, a new table, and put a single row in it. Created a slave account: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'x' Locked the database: FLUSH TABLES WITH READ LOCK Tar'd the data_dir/replicated directory and transferred it to the slave. Checked the binlog file and position and unlocked the database: SHOW MASTER STATUS UNLOCK TABLES Slave: Started mysqld. Set the master: CHANGE MASTER TO MASTER_HOST='IP', MASTER_USER='slave', MASTER_PASSWORD='x', MASTER_LOG_FILE='recorded value', MASTER_LOG_POS=recorded value; Began replication: START SLAVE; Master's my.cnf (comment lines removed): [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 log = /var/log/mysql/mysql.log basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-external-locking old-passwords = 1 key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 26214400 query_cache_type= 1 server-id = 1 log-bin = /var/log/mysql/mysql-bin.log binlog-do-db= replicated log-warnings binlog-ignore-db= mysql binlog-ignore-db= test [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M Slave's my.cnf (comment lines removed) [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 log = /var/log/mysql/mysql.log basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-external-locking old-passwords = 1 key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 26214400 query_cache_type= 1 server-id = 2 replicate-do-db = replicated replicate-ignore-db = mysql replicate-ignore-db = test log-warnings log-bin = /var/log/mysql/mysql-bin.log [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com --
Re: table handler error
[EMAIL PROTECTED] mysql-debug-4.1.9-pc-linux-gnu-i686]$ perror 5 Error code 5: Input/output error See: http://dev.mysql.com/doc/mysql/en/operating-system-error-codes.html http://dev.mysql.com/doc/mysql/en/error-handling.html http://dev.mysql.com/doc/mysql/en/perror.html [MySQL][ODBC 3.51 Driver][mysqld-4.0.20]Got error 5 from table handler Can anyone translate this for me and/or point me to a mapping of table handler error codes to their meanings? Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: error on restting password - with some addition
Hello. mysql stop automatically, and in process tab on task manager there is no any process name mysqld*. You may find errors in the error log. See: http://dev.mysql.com/doc/mysql/en/error-log.html my next question is can we address this problem by installing 4.1.9. and/or delete any data in Your problem doesn't depend on the MySQL version, but you should upgrade to the latest release. fredrich [EMAIL PROTECTED] wrote: in addition : when issue : mysqld-nt --skip-grant-tables mysql stop automatically, and in process tab on task manager there is no any process name mysqld*. thats why i just added a line skip-grant-tables on my.ini. my next question is can we address this problem by installing 4.1.9. and/or delete any data in mysql\data\mysql. and copy paste database from 4.1.5 to 4.1.9. thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: mysql ended problem
Hello. What is in the error log? See: http://dev.mysql.com/doc/mysql/en/error-log.html yoge [EMAIL PROTECTED] wrote: [-- multipart/alternative, encoding 7bit, 1 lines --] [-- text/plain, encoding 7bit, charset: us-ascii, 21 lines --] In Linux , when I start mysql using below command MYSQL_HOME=`pwd` ./bin/mysqld_safe --defaults-file=$MYSQL_HOME/my.ini --socket=$MYSQL_HOME/mysql.sock --tmpdir=$MYSQL_HOME/tmp --port=13306 --user=root I get a message *mysql ended *and server gets shutdown It happens in RedHat linux. MySql Version is 4.0.20 I have attached my.ini file with this mail Thanks in Advance for your help. --Yoge [-- text/html, encoding 7bit, charset: us-ascii, 31 lines --] [-- text/plain, encoding 7bit, charset: US-ASCII, 38 lines, name: my.ini --] #This File was made using the WinMySQLadmin 1.0 Tool #12/27/02 2:51:42 PM #Uncomment or Add only the keys that you know how works. #Read the MySQL Manual for instructions [mysqld] #set-variable=long_query_time=1 set-variable=transaction-isolation=READ-UNCOMMITTED #default value for read_buffer is 128KB set-variable= read_buffer=1M #default value is 8MB set-variable= key_buffer=32M #default value for read_buffer is 2MB set-variable= sort_buffer=4M #default value is 8MB set-variable= myisam_sort_buffer_size=16M #default value is 8MB set-variable= bulk_insert_buffer_size=16M #default value is 64 set-variable= table_cache=128 #log-slow-queries=slowquerylogger.log #log-update=updatelogger.log [client] #default password for MySQL password=abc [-- text/plain, encoding 7bit, charset: us-ascii, 4 lines --] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Random 1036 Table XXX is read-only
Hello. Can you reproduce a problem on the official binary? Sergey [EMAIL PROTECTED] wrote: úÄÒÁ×ÓÔ×ÕÊÔÅ After upgrading mysql server from 4.1.3beta to 4.1.9 (FreeBSD 4.8, mysql is installed from ports) I sometimes have the following problem: when some perl client tries to execute INSERT or UPDATE query via DBD::mysql it from time to time (but NOT always ) gets error 1036 (Table 'XX' is read only). Meanwhile: 1) Mysql user who tries to execute a query has all necessary priveleges 2) File system priveleges are also correctly set. mysqld runs under user mysql. Mysql datadir and ALL database files are owned by it and have 770 privilege mask. 3) The problem refers to MANY tables in different databases, but does NOT turn up systematicaly (by random from time to time). 4) Dumping and restoring problem tables did not solve the case 5) Upgrading DBD and DBI did not help either -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Upgrading from 3.23.57 to 4.1.9 using mysqldump
Hello.
Try --complete-insert command line option for mysqldump. See:
http://dev.mysql.com/doc/mysql/en/mysqldump.html
[EMAIL PROTECTED] wrote:
Hi,
I just tried to upgrade from mysql 3.23.57 (SPARC/Solaris) to 4.1.9
(i386/Linux) using mysqldump but when importing the dump on the new machine
mysql gives the error:
ERROR 1136 (21S01) at line NNN: Column count doesn't match value count at row
1
and in line NNN you find
INSERT INTO `db` VALUES
('localhost','dbxxx','','Y','Y','Y','Y','Y','Y','N','Y','Y','Y'),...
so he is right. The table 'db' has more entries in 4.1.9 than in 3.23.57! But
what is the correct way to upgrade? (NO! It is not possible for me to upgrade
directly on the 3.23.57 machine!)
MANY thanx in advance,
Frank
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [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]
Re: Slow Replication
Hello. The outputs of the following statements would be helpful, if you want that somebody helps you: SHOW MASTER STATUS; SHOW SLAVE STATUS; SHOW STATUS; Execute them on the master and the slave. Hannes Rohde [EMAIL PROTECTED] wrote: Hi all, We use MySQL as a database backend on a portal site. We have a two database server setup (one master, one slave). The master is a PIV 3,2 GHz., 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram and a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even though the slave is a bigger system and is quite fast with selects, it always falls behind in replication (Seconds behind the server keeps growing at high-load times). Is there any way to speed up the replication a little more? I have already tried a whole lot of things but have never been successful, yet :-( Here is a snapshot of the configuration: skip-name-resolve key_buffer=1M max_allowed_packet=1M thread_cache_size=128 thread_stack=128K table_cache=1024 join_buffer_size=5M read_buffer_size=5M sort_buffer_size=5M thread_concurrency=4 query_cache_size = 32M query_cache_limit = 1M query_cache_type = 2 max_connections=900 innodb_data_file_path=ibdata1:2G:autoextend innodb_buffer_pool_size=1200M innodb_additional_mem_pool_size=20M P.S.: I hope I have given you enough information - it's my post on the list...;-) I appreciate your help, Hannes Rohde -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Converting Text columns from mysql 4.0 to 4.1
Hello. Please send us an output of the following statement: SHOW VARIABLES LIKE '%char%'; See: http://dev.mysql.com/doc/mysql/en/charset-conversion.html Bruce Dembecki [EMAIL PROTECTED] wrote: Hi! We have a problem converting our 4.0 text columns from a Hong Kong database to 4.1. In order to get the conversions to work generally speaking we build our databases with default character set utf8 - it means the German products still work, and the English ones, and the Chinese ones, and the Anyway, we ran into a problem on the Hong Kong platform where the text column imports as a single space to 4.1... If I look at the data in 4.0 I see actual text (I suppose, it's mostly jibberish on my screen), while in 4.1 all I have after the import is a single space character. If I change the column type to blob (from text) I can get the data imported without problem, except that the data is now in a blob column. If I try to alter the table to a text column, I am left with the single spaces again. Looking at the data that does get affected (not all records suffer this fate, just some) it appears that they have multiple languages, for example Chinese or more often Japanese, together with something like an email address which is written in latin type characters. I can post a new entry through the webapp with mixed languages, it's just the export/import that seems to be be letting us down - or converting the blob to a text in 4.1 after the fact. I even tried building a duplicate table format and doing an INSERT SELECT where the source is a blob and the target is a text, and that also fails. Clearly I can't convert the rest of my databases if there is a chance that our message bodies will be munged With about 100 databases each with 60 tables it's not even going to be easy to try and script it in such a way that I could do a dump and an import with something changing the table type in the .sql file from text to blob, let alone the time it will take us to first test the Application and web servers to see if making the change to a blob column will affect us in any way. Do I need to be doing all this work... Is there something I have done incorrectly? Is this a bug that someone is fixing and will go away next version? I can provide the dump files if someone wants to test... Let me know. Best Regards, Bruce -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: optimize table and replication failure
Hello. Use OPTIMIZE NO_WRITE_TO_BINLOG syntax. See: http://dev.mysql.com/doc/mysql/en/optimize-table.html Mike Debnam [EMAIL PROTECTED] wrote: I issued a optimize table statement on my master which failed with a lock wait timeout message due to some competing queries running at the same time. Now the statement has been replicated to my slaves, and it completes successfully. However it's killing the slave input thread with the following message in my error log: 050208 15:10:38 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Lock wait timeout exceeded; try restarting transaction' (1205), Error on slave: 'no error' (0). Default database: 'MyDB'. Query: 'optimize table My_Table', Error_code: 0 050208 15:10:38 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'db-bin.000340' position 73609938 What's the correct way to work around this? I don't mind if the optimize table statement is skipped on the slaves. I just want replication to continue. MySQL versions 4.1.7 on the master, 4.1.9 on the slave. Thanks. Mike -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Aes Encryption
Hello. If you mentioned an AES_ENCRYPT(), see: http://dev.mysql.com/doc/mysql/en/encryption-functions.html You specify your password in your queries. The database doesn't contains the password by itself. Your application can ask a user for a password each time, and you don't have to store passwords in the source. What i think Aes encryption reduce the risk but does not make data 100% safe because of source code dependency. Nobody gives you a 100% garanty. love [EMAIL PROTECTED] wrote: Hi, Aes Encryption requires a password (key) to access data, now where do we store this key? with the source code ? or in seperate database ? because any body who has the access to souce code can view the critical info or even if you store passwords in another database then it is not a big deal to get those passwords if source code is accessible. What i think Aes encryption reduce the risk but does not make data 100% safe because of source code dependency. Love Kumar Perl Develpment Team eBookers.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Makefile my entries.
Hello. I didn't have a practice with the KDEvelop. But I'll give you some general rules. My suggestion, is that you use gcc compiler. mysql_config just produces a list of libraries which you have to add. Usually you can specify them in the environment variable $LDFLAGS or similar. Run configure with --help option, and in most cases it gives you some advice. If no, edit maually Makefile.in and find the place where additional libraries was specified. Put there your libraries. Also you need to specify a library path, which is added in the same way, but you should put somethinkg like -L/path_to_mysqllibraries if them don't located in the standart directories. To specify the includes use -I/path_to_includes. Usually you can add it to your CFLAGS. But for complete understanding what you should do, just read man gcc, especially parts related to -I flags and -L flags. Also read info pages about GNU Autotools. Another, elegant, way to add the mysql_config, is to use something like LDFLAGS+= `mysql_config --your_option`, which automatically produces the list of libraries. Mohsen Pahlevanzadeh [EMAIL PROTECTED] wrote: Dears,I want to add mysql's INCLUDEs LIBs path to my Makefile.in . Of course, i have wizarded a project with KDEvelop.It has created 3 Makefile with .cvs .am .in extensions. Please guide me that i can add mysql_config to them. Yours,Mohsen -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: Aes Encryption
There is not some thing secret to be stored but the idea is to encrypt customer credit card information so it is not avilable to unauthorized users but key cannot be stored in source code as any body who can hack databases to pull out the information can also hack key from source code, so make the risk 0% i wanted to know where to store the key but now James Black has suggested to pass the key on application startup and not to store on disk which is a good idea and will try to implement that. Thanks evey one for your help. Love .. [EMAIL PROTECTED] wrote: On Thursday 10 February 2005 19:00, love wrote: Has any body implemented Aes encryption while storing critical data in mysql? I want to know the logic you are implementing to store your passwords to encrypt/decrypt data. Love Kumar Love Kumar wrote: I think this question could not be answered globally. What exactly do you want to store where, and who should or should not be able to read the data? Is it that you want to store something secret into a table which should only be readable by a particular mysql-user? bh -- Bernhard Fischer [EMAIL PROTECTED] Telekommunikation und Medien Fachhochschule St. Plten/St. Poelten University of Applied Sciences Herzogenburger Strae 68 | 3100 St. Plten | +43 (0) 2742 313228 48 The information transmitted is intended only for the person or entity to whom it is addressed and may contain confidential and / or privileged Material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Thank you for your understanding co-operation.
Re: error on restting password - with some addition
yeah, after long time, thanks for answering. at least i can reset password. 1. i continue what i did but set password to ''(it doestn work if you set to other password). and restart server normally(not with skip-grant-tables). i can login to server. and set root password. thanks fred. --- Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. mysql stop automatically, and in process tab on task manager there is no any process name mysqld*. You may find errors in the error log. See: http://dev.mysql.com/doc/mysql/en/error-log.html my next question is can we address this problem by installing 4.1.9. and/or delete any data in Your problem doesn't depend on the MySQL version, but you should upgrade to the latest release. fredrich [EMAIL PROTECTED] wrote: in addition : when issue : mysqld-nt --skip-grant-tables mysql stop automatically, and in process tab on task manager there is no any process name mysqld*. thats why i just added a line skip-grant-tables on my.ini. my next question is can we address this problem by installing 4.1.9. and/or delete any data in mysql\data\mysql. and copy paste database from 4.1.5 to 4.1.9. thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aes Encryption
On Friday 11 February 2005 11:52, love wrote: There is not some thing secret to be stored but the idea is to encrypt customer credit card information so it is not avilable to unauthorized users but key cannot be stored in source code as any body who can hack databases to pull out the information can also hack key from source code, so make the risk 0% i wanted to know where to store the key but now James Black has suggested to pass the key on application startup and not to store on disk which is a good idea and will try to implement that. Thanks evey one for your help. Love .. Nevertheless you should be aware that the information is travelling unencrypted between the mysql client and server unless you're using SSL tunneling or similar techniques. Additionally, as long as your mysql server is running, data is also accessable unencrypted through let's say an attacker (if permissions are not set correctly or any security exploit exist) even if you pass the key at startup. Before modiying mysqld code you should consider to use a crypto filesystem storing your tables onto. Bernhard -- Bernhard Fischer [EMAIL PROTECTED] Telekommunikation und Medien Fachhochschule St. Plten/St. Poelten University of Applied Sciences Herzogenburger Strae 68 | 3100 St. Plten | +43 (0) 2742 313228 48 pgpm3tZTzerwi.pgp Description: PGP signature
Re: Aes Encryption
Nevertheless you should be aware that the information is travelling unencrypted between the mysql client and server unless you're using SSL tunneling or similar techniques. Sorry, since mysql 4.0, SSL is of course possible. (I'm still using 3.23) bh pgp7GAhKO8td6.pgp Description: PGP signature
Re: Filtering non-ascii characters from mysql data, null, tab etc
What about the following?
mysql create table test (txt varchar(255)) Type=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql insert into test values('Some Text\0and some more');
Query OK, 1 row affected (0.00 sec)
mysql select * from test;
*** 1. row ***
txt: Some Text
1 row in set (0.00 sec)
mysql update test set txt = replace(txt,'\0','NUL');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from test;
*** 1. row ***
txt: Some TextNULand some more
1 row in set (0.00 sec)
Cheers,
Thomas Spahni
On Thu, 10 Feb 2005, zzapper wrote:
On Thu, 10 Feb 2005 10:17:00 +, wrote:
Hi,
I've successfully used the following update-replace statement to replace
strings in mysql data
update tbl_county_lookup set countyname=replace(countyname,'amp;','and') ;
However I've had problems trying to replace a null character 0x00h ,
I'd be interested to know the syntax to filter null characters.
Secondly I'd be interested in a general filter for non-ascii.
cheers
Can't believe I'm the only one who's ever had this problem, I've googled and
just found a few fellow
searchers!!
(I will probably have to dump the db and use a perl script!)
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as Email database [sort of OT]
Brent,
see below:
On Thu, 10 Feb 2005, Brent Baisley wrote:
I am looking to store all incoming emails into a MySQL database. I've
looked into dbmail, but can't get it to compile under Mac OSX (I posted
a message on that list). I was wondering if anyone could point me in
another direction to use MySQL as an email message store. I don't need a
webmail interface, just a way of getting messages from a mail server to
a MySQL database. Preferably as a direct transfer, but it could be a
script that runs periodically. Currently it seems the best path is using
Perl, but I would think this has been done before, just can't find it on
google.
I use procmail to forward a copy of certain mail messages to the following
shell script which you can use as a starting point.
Cheers,
Thomas Spahni
#!/bin/sh
# This shell script is free software; all possible disclaimers apply
#
# get Mail from stdin and store into MySQL database.
#
#
DBASE=mydbase
SENDER=sendertable
ARCHIV=messagearchive
MYSQL=mysql -N
DECODEMIME=/home/user/bin/decmime.pl
if test $1 == initialize ; then
echo CREATE TABLE IF NOT EXISTS $SENDER (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
whencrtdDATETIME,
lastaccsTIMESTAMP,
mailaddrVARCHAR(255) NOT NULL,
actcountINT DEFAULT 0,
remarks VARCHAR(255) NOT NULL,
UNIQUE INDEX (mailaddr)) TYPE=MyISAM; | $MYSQL $DBASE
#
echo CREATE TABLE IF NOT EXISTS $ARCHIV (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
whencrtdDATETIME,
mailfromVARCHAR(255) NOT NULL,
mailsubjVARCHAR(255) NOT NULL,
textbeitrag TEXT,
INDEX (mailfrom),
FULLTEXT INDEX (mailsubj,textbeitrag)) TYPE=MyISAM; \
| $MYSQL $DBASE
#
exit 0
fi
# functions:
# code for escaping $1 argument
# escaped string goes to stdout
TAB=' '
myesc() {
echo $1 | sed -e 's/\\//g' -e s/'/'/g -e s/$TAB/t/g \
| tr -d '\012\015'
}
mysqltextescape() {
echo $1 | sed -e 's/\\//g' -e s/'/'/g -e s/$TAB/t/g \
-e 's/$/\\n/g' -e '$ s/\\n$//' | tr -d '\012\015'
}
mysql_like_escape() {
echo $1 | sed \
-e 's/\\//g' \
-e s/'/'/g \
-e 's/%/\\%/g' \
-e 's/_/\\_/g'
}
# mail comes from stdin
TEXT=$(cat -)
FROMADDR=$(echo $TEXT | formail -cx From: \
| sed -e s/^ *// | $DECODEMIME)
MYFROMADDR=$(mysql_like_escape $FROMADDR)
FROMEXISTS=$(echo SELECT mailaddr FROM $SENDER \
WHERE mailaddr LIKE '$MYFROMADDR'; | $MYSQL $DBASE)
if test -z $FROMEXISTS ; then
# insert new entry
echo INSERT INTO $SENDER \
VALUES(NULL, NOW(), NULL, '$(myesc $FROMADDR)', 1, ''); \
| $MYSQL $DBASE
else
# update existing entry
echo UPDATE $SENDER \
SET lastaccs = NULL, actcount = actcount+1 \
WHERE mailaddr LIKE '$MYFROMADDR'; | $MYSQL $DBASE
fi
# put into archiv
# Global variables for metamail
export KEYHEADS=''
export MM_NOASK=1
export MM_NOTTTY=1
export MM_QUIET=1
export MAILCAPS=/home/tsp/bin/mailcap
# White Space, one SPACE and one TAB:
WS=''
BODY=$(echo $TEXT | metamail -B -q -x 2/dev/null | formail -I \
| sed -e s/^[$WS]*$// \
| sed -n -e 1,$ H -e $ g \
-e s/^\\n*// -e s/\\n*$// \
-e $ p)
# beautify the subject line:
SUBJECT=$(echo $TEXT | formail -x Subject: | sed \
-e s/AW: /Re: /g \
-e s/Aw: /Re: /g \
-e s/RE: /Re: /g \
-e s/R: /Re: /g \
-e s/^ *// \
-e s/ */ /g \
-e s/Re: Re: Re: Re: /Re: / \
-e s/Re: Re: Re: /Re: / \
-e s/Re: Re: /Re: /)
MYBODY=$(mysqltextescape $BODY)
echo INSERT INTO $ARCHIV \
VALUES(NULL, NOW(), '$(myesc $FROMADDR)', \
'$(myesc $SUBJECT)','$MYBODY'); \
| $MYSQL $DBASE
exit 0
###
And this is decmime.pl
###
#!/usr/bin/perl -w
use strict;
use MIME::Words qw(:all);
#my $mimewordarg;
my $decoded;
my $mimestdin;
#$mimewordarg = $ARGV[0];
$mimestdin = STDIN;
chomp $mimestdin;
$decoded = decode_mimewords(
$mimestdin,
);
print $decoded, \n;
exit(0);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: global object-id
Well, the answer is no--there's no magic way to have an auto_increment do what you want. You could use a GUID--basically a pseudo-random number such the expected time to pick a duplicate is on the order of the lifetime of the universe. But GUID's are big and ugly, and it would be nice to just have an integer. Presumably, one of the things you'd like is to have inserts in different threads run concurrently--so your increment field should be unique, even in threads that haven't committed yet. Here's a scheme that may help. Yes, it has an extra table. But it mostly does what you want. create table global_increment ( value int not null primary key auto_increment ) type=InnoDB. You create a new value by insert into global_increment values (0); select last_insert_id(); Once you've got a new value, it's yours. You can commit it immediately or not, as you like, and still use it in your other tables without interference. Unfortunately, the global_increment table grows, and you need to delete old values. One way to do this is to estimate how many values are likely to still belong to current transactions. You can then do: commit; delete from global_increment where value (your_value - said_estimate); (We use something like this as part of a scheme to automatically update information shown on client screens when the database changes.) HTH = original message follows = From: Martijn Tonies [EMAIL PROTECTED] To: Konrad Kieling [EMAIL PROTECTED], mysql List mysql@lists.mysql.com Subject: Re: global object-id Date: Thu, 10 Feb 2005 16:57:49 +0100 is there a simple way (ie without creating and deleting datasets in an extra table) to use an auto_increment id field in several tables. no table has to have all ids, so i cannot use the field of a master table in childs. it seems one can use a sequence in postgresql in different tables. is it possible in a similar way? sequences are separate objects used to generated, guess what, sequential numbers. The actual usage of the numbers is undefined. Auto-inc is auto-inc. Plain and simple. It's something related to a column in a particular table. Each table can have it's own instance of 1 (and only 1) auto-incrementing column. Short answer: no. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL as Email database [sort of OT]
Thanks, that looks something like I was looking for. Quickly looking
through it, it doesn't look like it handles attachments. But thanks,
it's a jump start.
On Feb 11, 2005, at 7:28 AM, Thomas Spahni wrote:
Brent,
see below:
On Thu, 10 Feb 2005, Brent Baisley wrote:
I am looking to store all incoming emails into a MySQL database. I've
looked into dbmail, but can't get it to compile under Mac OSX (I
posted
a message on that list). I was wondering if anyone could point me in
another direction to use MySQL as an email message store. I don't
need a
webmail interface, just a way of getting messages from a mail server
to
a MySQL database. Preferably as a direct transfer, but it could be a
script that runs periodically. Currently it seems the best path is
using
Perl, but I would think this has been done before, just can't find it
on
google.
I use procmail to forward a copy of certain mail messages to the
following
shell script which you can use as a starting point.
Cheers,
Thomas Spahni
#!/bin/sh
# This shell script is free software; all possible disclaimers apply
#
# get Mail from stdin and store into MySQL database.
#
#
DBASE=mydbase
SENDER=sendertable
ARCHIV=messagearchive
MYSQL=mysql -N
DECODEMIME=/home/user/bin/decmime.pl
if test $1 == initialize ; then
echo CREATE TABLE IF NOT EXISTS $SENDER (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
whencrtdDATETIME,
lastaccsTIMESTAMP,
mailaddrVARCHAR(255) NOT NULL,
actcountINT DEFAULT 0,
remarks VARCHAR(255) NOT NULL,
UNIQUE INDEX (mailaddr)) TYPE=MyISAM; | $MYSQL $DBASE
#
echo CREATE TABLE IF NOT EXISTS $ARCHIV (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
whencrtdDATETIME,
mailfromVARCHAR(255) NOT NULL,
mailsubjVARCHAR(255) NOT NULL,
textbeitrag TEXT,
INDEX (mailfrom),
FULLTEXT INDEX (mailsubj,textbeitrag)) TYPE=MyISAM; \
| $MYSQL $DBASE
#
exit 0
fi
# functions:
# code for escaping $1 argument
# escaped string goes to stdout
TAB=' '
myesc() {
echo $1 | sed -e 's/\\//g' -e s/'/'/g -e s/$TAB/t/g \
| tr -d '\012\015'
}
mysqltextescape() {
echo $1 | sed -e 's/\\//g' -e s/'/'/g -e s/$TAB/t/g \
-e 's/$/\\n/g' -e '$ s/\\n$//' | tr -d '\012\015'
}
mysql_like_escape() {
echo $1 | sed \
-e 's/\\//g' \
-e s/'/'/g \
-e 's/%/\\%/g' \
-e 's/_/\\_/g'
}
# mail comes from stdin
TEXT=$(cat -)
FROMADDR=$(echo $TEXT | formail -cx From: \
| sed -e s/^ *// | $DECODEMIME)
MYFROMADDR=$(mysql_like_escape $FROMADDR)
FROMEXISTS=$(echo SELECT mailaddr FROM $SENDER \
WHERE mailaddr LIKE '$MYFROMADDR'; | $MYSQL $DBASE)
if test -z $FROMEXISTS ; then
# insert new entry
echo INSERT INTO $SENDER \
VALUES(NULL, NOW(), NULL, '$(myesc $FROMADDR)', 1, ''); \
| $MYSQL $DBASE
else
# update existing entry
echo UPDATE $SENDER \
SET lastaccs = NULL, actcount = actcount+1 \
WHERE mailaddr LIKE '$MYFROMADDR'; | $MYSQL $DBASE
fi
# put into archiv
# Global variables for metamail
export KEYHEADS=''
export MM_NOASK=1
export MM_NOTTTY=1
export MM_QUIET=1
export MAILCAPS=/home/tsp/bin/mailcap
# White Space, one SPACE and one TAB:
WS=''
BODY=$(echo $TEXT | metamail -B -q -x 2/dev/null | formail -I \
| sed -e s/^[$WS]*$// \
| sed -n -e 1,$ H -e $ g \
-e s/^\\n*// -e s/\\n*$// \
-e $ p)
# beautify the subject line:
SUBJECT=$(echo $TEXT | formail -x Subject: | sed \
-e s/AW: /Re: /g \
-e s/Aw: /Re: /g \
-e s/RE: /Re: /g \
-e s/R: /Re: /g \
-e s/^ *// \
-e s/ */ /g \
-e s/Re: Re: Re: Re: /Re: / \
-e s/Re: Re: Re: /Re: / \
-e s/Re: Re: /Re: /)
MYBODY=$(mysqltextescape $BODY)
echo INSERT INTO $ARCHIV \
VALUES(NULL, NOW(), '$(myesc $FROMADDR)', \
'$(myesc $SUBJECT)','$MYBODY'); \
| $MYSQL $DBASE
exit 0
###
And this is decmime.pl
###
#!/usr/bin/perl -w
use strict;
use MIME::Words qw(:all);
#my $mimewordarg;
my $decoded;
my $mimestdin;
#$mimewordarg = $ARGV[0];
$mimestdin = STDIN;
chomp $mimestdin;
$decoded = decode_mimewords(
$mimestdin,
);
print $decoded, \n;
exit(0);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives:
Re: Stored procedure debugger
matt_lists wrote: Anybody have any recommendations for a stored procedure editor/debugger? We are developing a test program with 5.x.x and procedures to see if it'll work for us stuck trying to get variables sorted out, and without a proper debugger it's extremely hard thanks in advance Nobody using stored procedures yet? Ours are hundreds of lines long, debugging on the command line really really sucks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two versions of MySQL on same machine
Dear MySQL, I require two versions of MySQL on the same machine. [Specifically part of an HACMP array where services are portable and move from server to server, using AIX 5.1]. The machine I have will have to support MySQL 4.0.11 and 4.1.8. I have three questions: 1. Are there any server libs? I can see these on a Linux box, none of which look like server libs, so I might be lucky? librt.so.1 libz.so.1 libdl.so.2 libpthread.so.0 libcrypt.so.1 libnsl.so.1 libm.so.6 libc.so.6 2. If there are and server libs, will the server libs conflict between the two versions? 2. If they conflict, is there a way of compiling MySQL to it's specific libs from the standard package, without static compilation? Any help, however honest or candied, would be extremely useful. Regards, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
HI, i give some more information about my application. 1) i have 41 million records , and this records are in 10 tables.so each table contains arrounds 4 million records. 2) Each table contains same columns definition . Total column is 61 and total number of the indexes column is 6.ok 3)now i fired the query like select count(*) from tablename where . in where clause having allmost all columns. 4) that query is fired on 10 tables from servlet with 10 threades .okk when i execute , i got the result after 6 to 7 minute. upto that i think you get my point. now i want to that result will come in 2 to 3 minute. is this possible in Mysql? also i have restored all my tables in mssql and then mssql give me result in 2 to 3 minute. but i can't my whole database shift to mssql. so can you have some idea that how can i speed up my query ? if you want to more description then let me know. Thank you Shailendra On Thu, 10 Feb 2005 11:06:04 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: See below Shailendra Soni [EMAIL PROTECTED] wrote on 02/10/2005 01:43:18 AM: Thank , But i can't create multipal index it will not useful for my tabels. I tryed to set GLOBAL keycache1.key_buffer_size = 128*1024 but it gives error that unknown system varible ' keycache1' . can you tell me that is this useful for my problem? and if yes how it is work? and how can i solve this error. Thanks again reply soon Regards: Shailendra I do not recognize that command either. Where did you find it and how was it related to improving query performance? This situation is analogous to you saying to me My car is slow, how do I make it go faster?. I know nothing about your table structures, your indexes, your query, or the issue itself (exactly how slow is it? how fast would you like it to be?). If you really need help with a query, please respond with all of the following information: 1) The text of the actual query 2) The results of an EXPLAIN on that query 3) The results of SHOW CREATE TABLE x\G for each table used in the query. 4) A description of why this query is not meeting your needs and what needs you would like it to meet. Once I have all of that background information, either I or someone else on the list will be able to help you with this issue. Do not forget to CC: the list with your responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine On Wed, 9 Feb 2005 10:02:49 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] com wrote: Shailendra Soni [EMAIL PROTECTED] wrote on 02/09/2005 08:28:36 AM: Hi, I have a question regarding speed of the query. In my application i am useing Mysql 4.0.20a-nt. I have 10 tables and each table contains 400 records and also 61 columns. I already created indexs on six column which are important for me. i fired the query on tables through servlet(thread). I fired same query on all tables on same time, but it has take time to getting result . allmost 7 to 10 minute . so please tell me how can i imporve speed of the Mysql or query? so it will take less time ! Thanks Shailendra Have you tried looking at this for ideas, too? http://dev.mysql.com/doc/mysql/en/query-speed.html Most of us start with an EXPLAIN of the query and work from there (see suggested reading). Check your table structures and, if the frequency of this query justifies it, an appropriate multi-column index (not multiple single-column indexes). Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convert to character set (upgrading from 4.0 to 4.1)
Hi.. I encountered some problems with character sets. On the 4.0 version I stored UTF-8 strings. When upgraded to 4.1, I saw the default character set was 'latin1', so I converted to UTF-8 using this: ALTER TABLE my_table CONVERT TO CHARACTER SET 'utf8', DEFAULT CHARACTER SET 'utf8'; When viewing the pages, I see that some of the characters were corrupted (the same chars always). I tried to return to convert back to 'latin1' but the problem remained. What was the problem? How can I fix it? BTW: for some reason I cannot reply to posts in this mailing list using Thunderbird. (On PHP mailing list replying works). please help... -thanks, Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Two versions of MySQL on same machine
You need to install each version into its own directory/path structure - if you have a default install of Mysql then you also need to take this into account. For each version you need to setup a my.cnf and if you want full separation different database directories. In the my.cnf you specify different ports/sockets. For any given user you then need to ensure they pickup the correct binaries and libraries(if they're building applications). If you're auto starting Mysql at boot you either need to write a script which provides each version separately or write a generice script which finds and starts/stops/shutsdown each installed version. Then you'll need to work out migrations strategies, user admin between versions, etc. Welcome to the world of system/database admin. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 14:12 To: mysql@lists.mysql.com Subject: Two versions of MySQL on same machine Dear MySQL, I require two versions of MySQL on the same machine. [Specifically part of an HACMP array where services are portable and move from server to server, using AIX 5.1]. The machine I have will have to support MySQL 4.0.11 and 4.1.8. I have three questions: 1. Are there any server libs? I can see these on a Linux box, none of which look like server libs, so I might be lucky? librt.so.1 libz.so.1 libdl.so.2 libpthread.so.0 libcrypt.so.1 libnsl.so.1 libm.so.6 libc.so.6 2. If there are and server libs, will the server libs conflict between the two versions? 2. If they conflict, is there a way of compiling MySQL to it's specific libs from the standard package, without static compilation? Any help, however honest or candied, would be extremely useful. Regards, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Two versions of MySQL on same machine
This site may actually help out more: http://dev.mysql.com/doc/mysql/en/mysqld-multi.html -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 9:58 AM To: mysql@lists.mysql.com Subject: RE: Two versions of MySQL on same machine You need to install each version into its own directory/path structure - if you have a default install of Mysql then you also need to take this into account. For each version you need to setup a my.cnf and if you want full separation different database directories. In the my.cnf you specify different ports/sockets. For any given user you then need to ensure they pickup the correct binaries and libraries(if they're building applications). If you're auto starting Mysql at boot you either need to write a script which provides each version separately or write a generice script which finds and starts/stops/shutsdown each installed version. Then you'll need to work out migrations strategies, user admin between versions, etc. Welcome to the world of system/database admin. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 14:12 To: mysql@lists.mysql.com Subject: Two versions of MySQL on same machine Dear MySQL, I require two versions of MySQL on the same machine. [Specifically part of an HACMP array where services are portable and move from server to server, using AIX 5.1]. The machine I have will have to support MySQL 4.0.11 and 4.1.8. I have three questions: 1. Are there any server libs? I can see these on a Linux box, none of which look like server libs, so I might be lucky? librt.so.1 libz.so.1 libdl.so.2 libpthread.so.0 libcrypt.so.1 libnsl.so.1 libm.so.6 libc.so.6 2. If there are and server libs, will the server libs conflict between the two versions? 2. If they conflict, is there a way of compiling MySQL to it's specific libs from the standard package, without static compilation? Any help, however honest or candied, would be extremely useful. Regards, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
YES, I need a LOT more information. Please provide ALL the information I asked for in my previous post (especially questions 1, 2, and 3). To compare with my automobile analogy: You told me that your auto is towing a lot of identical trailers and that if you use a different vehicle on a different road, you can drive faster pulling the same load. Your information is useful as additional information but not useful to answer your specific question. If you want specific help about a specific query, I have to have the information that is specific to your query. Comparing performance with another engine is not descriptive of the issues you are having. This is especially true for the query you give _as an example_ because COUNT(*) is handled very differently in the two database servers you compared. InnoDb uses versioning locks on it's records, that makes it practically impossible to determine exactly how many records are available to any user at any one time. This improves concurrency but makes COUNT(*) hard to compute quickly. How InnoDB estimates COUNT(*) is by taking the average of 10 random dives through the index tree. Please respond with the information that ANYONE (not just I) would need in order to answer your questions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shailendra Soni [EMAIL PROTECTED] wrote on 02/11/2005 09:35:53 AM: HI, i give some more information about my application. 1) i have 41 million records , and this records are in 10 tables.so each table contains arrounds 4 million records. 2) Each table contains same columns definition . Total column is 61 and total number of the indexes column is 6.ok 3)now i fired the query like select count(*) from tablename where . in where clause having allmost all columns. 4) that query is fired on 10 tables from servlet with 10 threades .okk when i execute , i got the result after 6 to 7 minute. upto that i think you get my point. now i want to that result will come in 2 to 3 minute. is this possible in Mysql? also i have restored all my tables in mssql and then mssql give me result in 2 to 3 minute. but i can't my whole database shift to mssql. so can you have some idea that how can i speed up my query ? if you want to more description then let me know. Thank you Shailendra On Thu, 10 Feb 2005 11:06:04 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: See below Shailendra Soni [EMAIL PROTECTED] wrote on 02/10/2005 01:43:18 AM: Thank , But i can't create multipal index it will not useful for my tabels. I tryed to set GLOBAL keycache1.key_buffer_size = 128*1024 but it gives error that unknown system varible ' keycache1' . can you tell me that is this useful for my problem? and if yes how it is work? and how can i solve this error. Thanks again reply soon Regards: Shailendra I do not recognize that command either. Where did you find it and how was it related to improving query performance? This situation is analogous to you saying to me My car is slow, how do I make it go faster?. I know nothing about your table structures, your indexes, your query, or the issue itself (exactly how slow is it? how fast would you like it to be?). If you really need help with a query, please respond with all of the following information: 1) The text of the actual query 2) The results of an EXPLAIN on that query 3) The results of SHOW CREATE TABLE x\G for each table used in the query. 4) A description of why this query is not meeting your needs and what needs you would like it to meet. Once I have all of that background information, either I or someone else on the list will be able to help you with this issue. Do not forget to CC: the list with your responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine On Wed, 9 Feb 2005 10:02:49 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] com wrote: Shailendra Soni [EMAIL PROTECTED] wrote on 02/09/2005 08:28:36 AM: Hi, I have a question regarding speed of the query. In my application i am useing Mysql 4.0.20a-nt. I have 10 tables and each table contains 400 records and also 61 columns. I already created indexs on six column which are important for me. i fired the query on tables through servlet(thread). I fired same query on all tables on same time, but it has take time to getting result . allmost 7 to 10 minute . so please tell me how can i imporve speed of the Mysql or query? so it will take less time ! Thanks Shailendra Have you tried looking at this for ideas, too? http://dev.mysql.com/doc/mysql/en/query-speed.html Most of us start with an EXPLAIN of the query and work from there
RE: Two versions of MySQL on same machine
AS I read the Mysql-multi stuff it works if you have multiple instances of the same version but not different version instances. With different version instances you (may) have differences in mysqld_safe, mysqld itself, and more than likely in the libraries themselves so you need to use the mysqld_safe version that corresponds to the version you want. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Dean, Michael L USAATC [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 15:07 To: 'mysql@lists.mysql.com' Subject: RE: Two versions of MySQL on same machine This site may actually help out more: http://dev.mysql.com/doc/mysql/en/mysqld-multi.html -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 9:58 AM To: mysql@lists.mysql.com Subject: RE: Two versions of MySQL on same machine You need to install each version into its own directory/path structure - if you have a default install of Mysql then you also need to take this into account. For each version you need to setup a my.cnf and if you want full separation different database directories. In the my.cnf you specify different ports/sockets. For any given user you then need to ensure they pickup the correct binaries and libraries(if they're building applications). If you're auto starting Mysql at boot you either need to write a script which provides each version separately or write a generice script which finds and starts/stops/shutsdown each installed version. Then you'll need to work out migrations strategies, user admin between versions, etc. Welcome to the world of system/database admin. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 14:12 To: mysql@lists.mysql.com Subject: Two versions of MySQL on same machine Dear MySQL, I require two versions of MySQL on the same machine. [Specifically part of an HACMP array where services are portable and move from server to server, using AIX 5.1]. The machine I have will have to support MySQL 4.0.11 and 4.1.8. I have three questions: 1. Are there any server libs? I can see these on a Linux box, none of which look like server libs, so I might be lucky? librt.so.1 libz.so.1 libdl.so.2 libpthread.so.0 libcrypt.so.1 libnsl.so.1 libm.so.6 libc.so.6 2. If there are and server libs, will the server libs conflict between the two versions? 2. If they conflict, is there a way of compiling MySQL to it's specific libs from the standard package, without static compilation? Any help, however honest or candied, would be extremely useful. Regards, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ** ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** ** ** -- 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] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any
RE: 1 to many query
Hey Tom, Thanks for giving me a hand. I think I solved the problem, a bit painful: ok, i think i solved the problem... if i want the chart layout to be like this on a page: project | state_a | state_c | state_d | state_e 1 - - Y- 2 - Y -- 3 Y - -- i should first: 1) run a select distinct state flag from table2 used by project to print out the header 2) then select project_id, state from table3 in the order of the state_flag pulled from the header... and for each row: if the project_id is different from the previous one, jump to the next project display row in the chart for each state (in 1) if the current project_states.state = state.. print Y else print -. a little painful, but I can give room to keep track of all the project state changes and add new states anytime without the need to add new column... man thanks, -ljb -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: Thursday, February 10, 2005 6:15 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: 1 to many query -Original Message- From: Tom Crimmins Sent: Thursday, February 10, 2005 17:08 To: livejavabean Cc: mysql@lists.mysql.com Subject: RE: 1 to many query -Original Message- From: livejavabean Sent: Thursday, February 10, 2005 16:47 To: mysql@lists.mysql.com Subject: 1 to many query Hi there.. Hope you can give me some thoughts on this. let say we have 3 tables table 1 (pk=project_id) === - project_id - project_name table 2 (pk=project_id, project_state_flag) === - project_id - project_state_flag (fk to state_flag) table 3 (pk=state_flag) === - state_flag - state_flag_name thank you.. but do u think it is possible to make the query return: - 1 row per project - each project state row's state become a column e.g. project 1, name, state a, state b, state c... project 2, name, state a, state b, state c. thanks in advance.. This looks like a many to many relationship to me. Each project is associated with multiple state_flags, and each state_flag can be associated with multiple projects. If you have mysql 4.1 or greater, you can use try the following. It won't get you separate columns for each state_flag_name, but it will give you a list of all the state_flag_names associated with each project in a single column. SELECT t1.project_id, t1.project_name, GROUP_CONCAT(t3.state_flag_name) as state_flags FROM t1 INNER JOIN t2 ON (t1.project_id = t2.project_id) INNER JOIN t3 ON (t2.project_state_flag = t3.state_flag) GROUP BY t1.project_id Sorry, also forgot to add that if you want projects returned that don't have any state_flags associated with them you will need to make that first inner join a left join. regards, -ljb --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- 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]
4.1.7 Character set problem ( Ithink?)
Hi List, I am having a strange problem on Linux Fedora Core 3 with MySQL 4.1.7 ( offical mysql rpms). The data was originally stored in MySQL 3.something and was placed into the database via a MySQLDump file. It is too late to reload the data. I have a table called fees: CREATE TABLE `fees` ( `refID` int(11) NOT NULL default '0', `price` text, `tuitionFee` tinyint(4) default NULL, `examFee` tinyint(4) default NULL, `otherFee` tinyint(4) default NULL, `feeText` text, `pending` tinyint(4) default '0', PRIMARY KEY (`refID`), KEY `refID` (`refID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin2 When I try updating the price field for one record, it doesn't seem to recognise the pound sign (£): mysql UPDATE fees SET price= '£45' WHERE refID=732; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql select price from fees where refID=732; +---+ | price | +---+ | ?45 | +---+ 1 row in set (0.00 sec) The same result ?45 is returned via php as well, so its not a console display problem. I have also tried this with the latin1 character set with the same results. I know I am probably better off changing the field type to a double and placing the pound sign in my php code, but I am curious as to why this happens. Is it a problem with the character sets? Should I be using a different character set for English language text ( no international chars ). Any help will be appreciated. Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
Normally I do not reply to myself but I just realized that in my previous response I confused COUNT(*) (which is slow for InnoDB because it always does a table scan to resolve the version lock of each and every row) with SHOW STATUS (which computes table sizes based on the average of 1 random passes , like I described). Sorry to everyone I may have confused. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 02/11/2005 10:04:02 AM: YES, I need a LOT more information. Please provide ALL the information I asked for in my previous post (especially questions 1, 2, and 3). To compare with my automobile analogy: You told me that your auto is towing a lot of identical trailers and that if you use a different vehicle on a different road, you can drive faster pulling the same load. Your information is useful as additional information but not useful to answer your specific question. If you want specific help about a specific query, I have to have the information that is specific to your query. Comparing performance with another engine is not descriptive of the issues you are having. This is especially true for the query you give _as an example_ because COUNT(*) is handled very differently in the two database servers you compared. InnoDb uses versioning locks on it's records, that makes it practically impossible to determine exactly how many records are available to any user at any one time. This improves concurrency but makes COUNT(*) hard to compute quickly. How InnoDB estimates COUNT(*) is by taking the average of 10 random dives through the index tree. Please respond with the information that ANYONE (not just I) would need in order to answer your questions. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shailendra Soni [EMAIL PROTECTED] wrote on 02/11/2005 09:35:53 AM: HI, i give some more information about my application. 1) i have 41 million records , and this records are in 10 tables.so each table contains arrounds 4 million records. 2) Each table contains same columns definition . Total column is 61 and total number of the indexes column is 6.ok 3)now i fired the query like select count(*) from tablename where . in where clause having allmost all columns. 4) that query is fired on 10 tables from servlet with 10 threades .okk when i execute , i got the result after 6 to 7 minute. upto that i think you get my point. now i want to that result will come in 2 to 3 minute. is this possible in Mysql? also i have restored all my tables in mssql and then mssql give me result in 2 to 3 minute. but i can't my whole database shift to mssql. so can you have some idea that how can i speed up my query ? if you want to more description then let me know. Thank you Shailendra On Thu, 10 Feb 2005 11:06:04 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: See below Shailendra Soni [EMAIL PROTECTED] wrote on 02/10/2005 01:43:18 AM: Thank , But i can't create multipal index it will not useful for my tabels. I tryed to set GLOBAL keycache1.key_buffer_size = 128*1024 but it gives error that unknown system varible ' keycache1' . can you tell me that is this useful for my problem? and if yes how it is work? and how can i solve this error. Thanks again reply soon Regards: Shailendra I do not recognize that command either. Where did you find it and how was it related to improving query performance? This situation is analogous to you saying to me My car is slow, how do I make it go faster?. I know nothing about your table structures, your indexes, your query, or the issue itself (exactly how slow is it? how fast would you like it to be?). If you really need help with a query, please respond with all of the following information: 1) The text of the actual query 2) The results of an EXPLAIN on that query 3) The results of SHOW CREATE TABLE x\G for each table used in the query. 4) A description of why this query is not meeting your needs and what needs you would like it to meet. Once I have all of that background information, either I or someone else on the list will be able to help you with this issue. Do not forget to CC: the list with your responses. Shawn Green Database Administrator Unimin Corporation - Spruce Pine On Wed, 9 Feb 2005 10:02:49 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] com wrote: Shailendra Soni [EMAIL PROTECTED] wrote on 02/09/2005 08:28:36 AM: Hi, I have a question regarding speed of the query. In my application i am useing Mysql 4.0.20a-nt. I have 10 tables and each table contains 400 records
Re: Two versions of MySQL on same machine
Thanks for the documentation, there are some interesting things in there. I may however have to go further than this and have true autonomous MySQL. Not sharing any /etc/my.cfg. Running everything from some '~' directory: ~/etc/mysql.conf ~/bin/mysql* ~/libexec/mysqld ~/logs/ ~/var/ ~/mysql.sock ~/start.sh ~/stop.sh (etc) Therefore having a truely portable service group. This is needed so that I can shunt the service from machine to machine to control load and handle outages. This bit seems farly simple. As long as I start it up very carefully so as to avoid IP conflicts, read the correct mysql.conf file etc... But my worry still remains: I can make MySQL 'autonomous' in this way only to a point. They limit I can see is where external libs are needed. These may conflict if compiled from different versions with the same file name. I would be interested to know from any member whether this may be the case. Whether there are any libs MySQL creates which will conflict from different versions. Eg, does 4.0 and 4.1 use different libmysqlclinet.so but have the same file name. Therefore one will work, one will dump :( Or maybe I can create the libs in my autonomous portable directory: ~/libs/libmysqlclient.so But many thanks for the documentation, this will be a great start to finding my solution. Regards, Ben. Kevin Cowley wrote: AS I read the Mysql-multi stuff it works if you have multiple instances of the same version but not different version instances. With different version instances you (may) have differences in mysqld_safe, mysqld itself, and more than likely in the libraries themselves so you need to use the mysqld_safe version that corresponds to the version you want. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Dean, Michael L USAATC [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 15:07 To: 'mysql@lists.mysql.com' Subject: RE: Two versions of MySQL on same machine This site may actually help out more: http://dev.mysql.com/doc/mysql/en/mysqld-multi.html -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 9:58 AM To: mysql@lists.mysql.com Subject: RE: Two versions of MySQL on same machine You need to install each version into its own directory/path structure - if you have a default install of Mysql then you also need to take this into account. For each version you need to setup a my.cnf and if you want full separation different database directories. In the my.cnf you specify different ports/sockets. For any given user you then need to ensure they pickup the correct binaries and libraries(if they're building applications). If you're auto starting Mysql at boot you either need to write a script which provides each version separately or write a generice script which finds and starts/stops/shutsdown each installed version. Then you'll need to work out migrations strategies, user admin between versions, etc. Welcome to the world of system/database admin. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 14:12 To: mysql@lists.mysql.com Subject: Two versions of MySQL on same machine Dear MySQL, I require two versions of MySQL on the same machine. [Specifically part of an HACMP array where services are portable and move from server to server, using AIX 5.1]. The machine I have will have to support MySQL 4.0.11 and 4.1.8. I have three questions: 1. Are there any server libs? I can see these on a Linux box, none of which look like server libs, so I might be lucky? librt.so.1 libz.so.1 libdl.so.2 libpthread.so.0 libcrypt.so.1 libnsl.so.1 libm.so.6 libc.so.6 2. If there are and server libs, will the server libs conflict between the two versions? 2. If they conflict, is there a way of compiling MySQL to it's specific libs from the standard package, without static compilation? Any help, however honest or candied, would be extremely useful. Regards, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ** ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by
RE: Two versions of MySQL on same machine
If your libraries/executables in your autonomus portable directories are from the later version you shouldn't have problems - they're generally backwards compatible. You're also better off since you're running two 4.n.x versions. The problems really occur if you have 4.n.x and 3.n.x installed on the same machine and the 3.n.x are in the default path for system users. You then have to remember to explicitly reset the path every time you want to use any of the 4.n.x utilities as they'll try and use the 3.n.x libraries and fail. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 15:54 To: Kevin Cowley Cc: mysql@lists.mysql.com Subject: Re: Two versions of MySQL on same machine Thanks for the documentation, there are some interesting things in there. I may however have to go further than this and have true autonomous MySQL. Not sharing any /etc/my.cfg. Running everything from some '~' directory: ~/etc/mysql.conf ~/bin/mysql* ~/libexec/mysqld ~/logs/ ~/var/ ~/mysql.sock ~/start.sh ~/stop.sh (etc) Therefore having a truely portable service group. This is needed so that I can shunt the service from machine to machine to control load and handle outages. This bit seems farly simple. As long as I start it up very carefully so as to avoid IP conflicts, read the correct mysql.conf file etc... But my worry still remains: I can make MySQL 'autonomous' in this way only to a point. They limit I can see is where external libs are needed. These may conflict if compiled from different versions with the same file name. I would be interested to know from any member whether this may be the case. Whether there are any libs MySQL creates which will conflict from different versions. Eg, does 4.0 and 4.1 use different libmysqlclinet.so but have the same file name. Therefore one will work, one will dump :( Or maybe I can create the libs in my autonomous portable directory: ~/libs/libmysqlclient.so But many thanks for the documentation, this will be a great start to finding my solution. Regards, Ben. Kevin Cowley wrote: AS I read the Mysql-multi stuff it works if you have multiple instances of the same version but not different version instances. With different version instances you (may) have differences in mysqld_safe, mysqld itself, and more than likely in the libraries themselves so you need to use the mysqld_safe version that corresponds to the version you want. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Dean, Michael L USAATC [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 15:07 To: 'mysql@lists.mysql.com' Subject: RE: Two versions of MySQL on same machine This site may actually help out more: http://dev.mysql.com/doc/mysql/en/mysqld-multi.html -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 9:58 AM To: mysql@lists.mysql.com Subject: RE: Two versions of MySQL on same machine You need to install each version into its own directory/path structure - if you have a default install of Mysql then you also need to take this into account. For each version you need to setup a my.cnf and if you want full separation different database directories. In the my.cnf you specify different ports/sockets. For any given user you then need to ensure they pickup the correct binaries and libraries(if they're building applications). If you're auto starting Mysql at boot you either need to write a script which provides each version separately or write a generice script which finds and starts/stops/shutsdown each installed version. Then you'll need to work out migrations strategies, user admin between versions, etc. Welcome to the world of system/database admin. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 14:12 To: mysql@lists.mysql.com Subject: Two versions of MySQL on same machine Dear MySQL, I require two versions of MySQL on the same machine. [Specifically part of an HACMP array where services are portable and move from server to server, using AIX 5.1]. The machine I have will have to support MySQL 4.0.11 and 4.1.8. I have three questions: 1. Are there any server libs? I can see these on a Linux box, none of which look like server libs, so I might be lucky? librt.so.1 libz.so.1 libdl.so.2 libpthread.so.0 libcrypt.so.1 libnsl.so.1 libm.so.6 libc.so.6 2. If there are and server libs, will the server libs conflict between the two
Re: Two versions of MySQL on same machine
Kevin, I am pleased to hear this is probably possible. Now to give it a go and see what happens. Regards, Ben. Kevin Cowley wrote: If your libraries/executables in your autonomus portable directories are from the later version you shouldn't have problems - they're generally backwards compatible. You're also better off since you're running two 4.n.x versions. The problems really occur if you have 4.n.x and 3.n.x installed on the same machine and the 3.n.x are in the default path for system users. You then have to remember to explicitly reset the path every time you want to use any of the 4.n.x utilities as they'll try and use the 3.n.x libraries and fail. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 15:54 To: Kevin Cowley Cc: mysql@lists.mysql.com Subject: Re: Two versions of MySQL on same machine Thanks for the documentation, there are some interesting things in there. I may however have to go further than this and have true autonomous MySQL. Not sharing any /etc/my.cfg. Running everything from some '~' directory: ~/etc/mysql.conf ~/bin/mysql* ~/libexec/mysqld ~/logs/ ~/var/ ~/mysql.sock ~/start.sh ~/stop.sh (etc) Therefore having a truely portable service group. This is needed so that I can shunt the service from machine to machine to control load and handle outages. This bit seems farly simple. As long as I start it up very carefully so as to avoid IP conflicts, read the correct mysql.conf file etc... But my worry still remains: I can make MySQL 'autonomous' in this way only to a point. They limit I can see is where external libs are needed. These may conflict if compiled from different versions with the same file name. I would be interested to know from any member whether this may be the case. Whether there are any libs MySQL creates which will conflict from different versions. Eg, does 4.0 and 4.1 use different libmysqlclinet.so but have the same file name. Therefore one will work, one will dump :( Or maybe I can create the libs in my autonomous portable directory: ~/libs/libmysqlclient.so But many thanks for the documentation, this will be a great start to finding my solution. Regards, Ben. Kevin Cowley wrote: AS I read the Mysql-multi stuff it works if you have multiple instances of the same version but not different version instances. With different version instances you (may) have differences in mysqld_safe, mysqld itself, and more than likely in the libraries themselves so you need to use the mysqld_safe version that corresponds to the version you want. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Dean, Michael L USAATC [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 15:07 To: 'mysql@lists.mysql.com' Subject: RE: Two versions of MySQL on same machine This site may actually help out more: http://dev.mysql.com/doc/mysql/en/mysqld-multi.html -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 9:58 AM To: mysql@lists.mysql.com Subject: RE: Two versions of MySQL on same machine You need to install each version into its own directory/path structure - if you have a default install of Mysql then you also need to take this into account. For each version you need to setup a my.cnf and if you want full separation different database directories. In the my.cnf you specify different ports/sockets. For any given user you then need to ensure they pickup the correct binaries and libraries(if they're building applications). If you're auto starting Mysql at boot you either need to write a script which provides each version separately or write a generice script which finds and starts/stops/shutsdown each installed version. Then you'll need to work out migrations strategies, user admin between versions, etc. Welcome to the world of system/database admin. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 14:12 To: mysql@lists.mysql.com Subject: Two versions of MySQL on same machine Dear MySQL, I require two versions of MySQL on the same machine. [Specifically part of an HACMP array where services are portable and move from server to server, using AIX 5.1]. The machine I have will have to support MySQL 4.0.11 and 4.1.8. I have three questions: 1. Are there any server libs? I can see these on a Linux box, none of which look like server libs, so I might be lucky? librt.so.1 libz.so.1 libdl.so.2 libpthread.so.0 libcrypt.so.1 libnsl.so.1 libm.so.6 libc.so.6 2. If there are and server libs, will the server libs conflict between the two versions? 2. If they conflict, is there a way of compiling MySQL to it's specific libs from the standard
Re: Two versions of MySQL on same machine
The clients are backwards compatible. Meaning if you put a 4.1 client on the machine it will work for both 4.1 and 4.0 installations. The only real trick to running two instances of mysqld on the same machine is to specify the datadir on startup as in mysqld_safe --datadir=/path/to/instance/one in each datadir you will need to set up a my.cnf file with paths specific to that instance. Example in instance/one/my.cnf you might set socket = /path/to/instance/one/mysqld.sock port=3306 In instance two start mysqld_safe --datadir=/path/to/instance/two/ and set the variables socket = /path/to/instance/two port=3307 pid-file, log-error and a few other variables will need to be set in the same way. A quick scan of one of the default my.cnf files should tell you everything you need to change. If you use the pre compiled statically linked binaries you won't have to worry about versions of system libraries. On Fri, 11 Feb 2005 15:53:55 +, Ben Clewett [EMAIL PROTECTED] wrote: Thanks for the documentation, there are some interesting things in there. I may however have to go further than this and have true autonomous MySQL. Not sharing any /etc/my.cfg. Running everything from some '~' directory: ~/etc/mysql.conf ~/bin/mysql* ~/libexec/mysqld ~/logs/ ~/var/ ~/mysql.sock ~/start.sh ~/stop.sh (etc) Therefore having a truely portable service group. This is needed so that I can shunt the service from machine to machine to control load and handle outages. This bit seems farly simple. As long as I start it up very carefully so as to avoid IP conflicts, read the correct mysql.conf file etc... But my worry still remains: I can make MySQL 'autonomous' in this way only to a point. They limit I can see is where external libs are needed. These may conflict if compiled from different versions with the same file name. I would be interested to know from any member whether this may be the case. Whether there are any libs MySQL creates which will conflict from different versions. Eg, does 4.0 and 4.1 use different libmysqlclinet.so but have the same file name. Therefore one will work, one will dump :( Or maybe I can create the libs in my autonomous portable directory: ~/libs/libmysqlclient.so But many thanks for the documentation, this will be a great start to finding my solution. Regards, Ben. Kevin Cowley wrote: AS I read the Mysql-multi stuff it works if you have multiple instances of the same version but not different version instances. With different version instances you (may) have differences in mysqld_safe, mysqld itself, and more than likely in the libraries themselves so you need to use the mysqld_safe version that corresponds to the version you want. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Dean, Michael L USAATC [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 15:07 To: 'mysql@lists.mysql.com' Subject: RE: Two versions of MySQL on same machine This site may actually help out more: http://dev.mysql.com/doc/mysql/en/mysqld-multi.html -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 9:58 AM To: mysql@lists.mysql.com Subject: RE: Two versions of MySQL on same machine You need to install each version into its own directory/path structure - if you have a default install of Mysql then you also need to take this into account. For each version you need to setup a my.cnf and if you want full separation different database directories. In the my.cnf you specify different ports/sockets. For any given user you then need to ensure they pickup the correct binaries and libraries(if they're building applications). If you're auto starting Mysql at boot you either need to write a script which provides each version separately or write a generice script which finds and starts/stops/shutsdown each installed version. Then you'll need to work out migrations strategies, user admin between versions, etc. Welcome to the world of system/database admin. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 14:12 To: mysql@lists.mysql.com Subject: Two versions of MySQL on same machine Dear MySQL, I require two versions of MySQL on the same machine. [Specifically part of an HACMP array where services are portable and move from server to server, using AIX 5.1]. The machine I have will have to support MySQL 4.0.11 and 4.1.8. I have three questions: 1. Are there any server libs? I can see these on a Linux box, none of which look like server libs, so I might be lucky? librt.so.1 libz.so.1 libdl.so.2 libpthread.so.0 libcrypt.so.1
RE: php conection problems
HI All, I'm back,...I've removed MySQL completely and reinstalled it using the binaries for Mac OS X 10.3+ from the mysql website. I still have a Client API version of 3.23 in the phpinfo() while I see 4.1.9 from the command line. I can get things to work using the Old_Password suggested earlier,... Is this a Mac OS X thing? Or is this normal? Do all the rest of you have newer version of MySQL recognized by php/apache? Thoughts? Thanks in advance Jeff -- Jeff Mao [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php conection problems
You should try to do a : mysql_connect($host, $user, $password) or die(mysql_error()); to have an error message. It will be easier to find an answer from that I think.As I told you if it's the password problem you will have Client does not support authentication protocol for example. From: Jeff Mao [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: RE: php conection problems Date: Fri, 11 Feb 2005 11:29:17 -0500 HI All, I'm back,...I've removed MySQL completely and reinstalled it using the binaries for Mac OS X 10.3+ from the mysql website. I still have a Client API version of 3.23 in the phpinfo() while I see 4.1.9 from the command line. I can get things to work using the Old_Password suggested earlier,... Is this a Mac OS X thing? Or is this normal? Do all the rest of you have newer version of MySQL recognized by php/apache? Thoughts? Thanks in advance Jeff -- Jeff Mao [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: php conection problems
Jeff Mao wrote: I'm back,...I've removed MySQL completely and reinstalled it using the binaries for Mac OS X 10.3+ from the mysql website. I still have a Client API version of 3.23 in the phpinfo() while I see 4.1.9 from the command line. This has *nothing* to do with the MySQL you've installed, or with the Apache httpd -- phpinfo() is showing you what MySQL libraries *PHP* was compiled with. If you want PHP to use newer (4.x) client libraries, either find a binary distribution that suits you or compile it yourself. -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: php conection problems
Thanks,...that's the info I needed to help me know where to look for a solution,..I'll direct my work on the php side of things! Jeff At 9:07 AM -0800 2/11/05, Hassan Schroeder wrote: Jeff Mao wrote: I'm back,...I've removed MySQL completely and reinstalled it using the binaries for Mac OS X 10.3+ from the mysql website. I still have a Client API version of 3.23 in the phpinfo() while I see 4.1.9 from the command line. This has *nothing* to do with the MySQL you've installed, or with the Apache httpd -- phpinfo() is showing you what MySQL libraries *PHP* was compiled with. If you want PHP to use newer (4.x) client libraries, either find a binary distribution that suits you or compile it yourself. -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jeff Mao [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
32 bit vs. 64 bit
What knid of performance difference can I expect between 2 xeon 3ghz 32bit compared with 2 IBM Power5 64bit processors if memeory and everything else is the same? Thanks, Scott Pippin [EMAIL PROTECTED]
RE: php conection problems
You need to rebuild PHP (./configure --with-mysql, make, make install), etc. Don't know everything offhand, but it will build the libraries you need and use the new client to connect. Michael -Original Message- From: Jeff Mao [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 11:29 AM To: mysql@lists.mysql.com Subject: RE: php conection problems HI All, I'm back,...I've removed MySQL completely and reinstalled it using the binaries for Mac OS X 10.3+ from the mysql website. I still have a Client API version of 3.23 in the phpinfo() while I see 4.1.9 from the command line. I can get things to work using the Old_Password suggested earlier,... Is this a Mac OS X thing? Or is this normal? Do all the rest of you have newer version of MySQL recognized by php/apache? Thoughts? Thanks in advance Jeff -- Jeff Mao [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 32 bit vs. 64 bit
The 64-bit, as far as MySQL is concerned, would give you better performance. MySQL is built for 64-bit. Michael -Original Message- From: Scott Pippin [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 12:56 PM To: mysql@lists.mysql.com Subject: 32 bit vs. 64 bit What knid of performance difference can I expect between 2 xeon 3ghz 32bit compared with 2 IBM Power5 64bit processors if memeory and everything else is the same? Thanks, Scott Pippin [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New to MySQL on Linux
Having inherited an elderly PIII/500MHz box with an 8Gb SCSI disk, that had an apparently unusable XP SP2 OS on it, I decided to wipe the disk and install my first Linux instead, using an ancient RedHat 7.3 distribution. Having done that successfully, and increased the memory from 256 to 768Mb, I think I'm now ready to install the latest MySQL on it. All my previous MySQL experience, unfortunatley, has been on WinNT, usually installed with the msi installer. Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). Suggestions, please? Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 32 bit vs. 64 bit
Hi! Am Fr, den 11.02.2005 schrieb Scott Pippin um 18:55: What knid of performance difference can I expect between 2 xeon 3ghz 32bit compared with 2 IBM Power5 64bit processors if memeory and everything else is the same? As for the raw CPU power, I have no figures. You probably know that a Power CPU gets more performance per MHz than an x86 one. In the DB context, you can typically use integer benchmarks as a rough guideline AFAIK. IMNSHO, you buy a 64 bit CPU in order to have (now or later) more RAM to fill your large address space, and in the DBMS context you will be using this RAM for caches. Then, the question is: What kind of performance difference can I expect from larger caches? And to this, the typical answer is: It depends on the amount of data accessed frequently enough. As soon as you can reuse data in the cache, you save the disk access cost. So IMO using a 64 bit CPU ensures you can use larger caches either immediately or later, it protects your basic hardware (+ software + admin training) investments in case your data (are or get) too large. Still IMO, this also means you always should select a box that allows for RAM upgrade, even if you do not buy it immediately (unless you know it will not grow that much). In short: There is only one replacfement for RAM: more RAM! Regards and HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php conection problems
Jeff,
When you upgraded to the 4.1 version of MySQL did you run the
mysql_fix_privilege_tables script? I just upgraded a 3.23 server to the 4.1
and ran that script and my php seems to work fine using the 3.23 client. I
had to fix other instances where I had used the password('pass') method in
MySQL to populate a column, but as far as connecting I had no problems.
Michael
-Original Message-
From: Jeff Mao [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 10, 2005 3:02 PM
To: mysql@lists.mysql.com
Subject: php conection problems
Hi All,
I'm not sure this a MySQL problem or a PHP problem,...
I just picked up a new laptop, and like I had always done in the past,
installed MySQL and PHP so I could test and play with code locally.
The laptop is a Mac Powerbook running 10.3.8. The MySQL installation is the
Standard 4.1.19 installation from the dev.mysql.com website.
The php installation is Marc Liyanage's (entropy.ch) package installer for
4.3.10.
php appears to be OK as pulling a page with ?php phpinfo () ? yields the
normal output.
MySQL appears fine as I can login and do things Terminal using the mysql
client.
But setting up an of the numerous simple php/mysql web sites, like phpbb or
moodle give me database connection failures,...
In the past, I've always simply installed these two pieces as I mentioned
above and everything was so easy,but it's been awhile since I last did
this,did I miss something?
Thanks
Jeff
--
Jeff Mao
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: php conection problems
This wasn't an upgrade,...brand new laptop,...clean installs of
everything which is why I was surprised things didn't go as smoothly
as I expected,...I did not compile php myself,...used a package
installer made by Marc Liyanage (entropy.ch) who typically has very
well put together stuff for Macs,...I'm checking back on his site to
see if this was an oops on his part or by design, etc,...
Jeff
At 1:19 PM -0500 2/11/05, Dean, Michael L USAATC wrote:
Jeff,
When you upgraded to the 4.1 version of MySQL did you run the
mysql_fix_privilege_tables script? I just upgraded a 3.23 server to the 4.1
and ran that script and my php seems to work fine using the 3.23 client. I
had to fix other instances where I had used the password('pass') method in
MySQL to populate a column, but as far as connecting I had no problems.
Michael
-Original Message-
From: Jeff Mao [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 10, 2005 3:02 PM
To: mysql@lists.mysql.com
Subject: php conection problems
Hi All,
I'm not sure this a MySQL problem or a PHP problem,...
I just picked up a new laptop, and like I had always done in the past,
installed MySQL and PHP so I could test and play with code locally.
The laptop is a Mac Powerbook running 10.3.8. The MySQL installation is the
Standard 4.1.19 installation from the dev.mysql.com website.
The php installation is Marc Liyanage's (entropy.ch) package installer for
4.3.10.
php appears to be OK as pulling a page with ?php phpinfo () ? yields the
normal output.
MySQL appears fine as I can login and do things Terminal using the mysql
client.
But setting up an of the numerous simple php/mysql web sites, like phpbb or
moodle give me database connection failures,...
In the past, I've always simply installed these two pieces as I mentioned
above and everything was so easy,but it's been awhile since I last did
this,did I miss something?
Thanks
Jeff
--
Jeff Mao
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Jeff Mao
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Any means to get the optimizer out of the way?
Harrison Fisk [EMAIL PROTECTED] writes: The difference between the count(*) and the other query is that the real query has to use the datafile to retrieve the data when you are involving the actual columns. With the count(*) query it is using an Index only read, meaning that it doesn't have to the use the datafile at all to resolve it. If you do an EXPLAIN on the count(*) query, you should see a 'Using Index' in the Extra column. So it is using the index and estimating it is going to have to read 3885524 rows from the data file. Assuming the estimate is close, that will be an extra 3885524 disk seeks and reads to find the data for your query. That is why vmstat is showing the query doing much more disk i/o. Harrison, Thanks for helping me to better understand what the explain was telling me. Though I'm not sure it quite fits with the other data I collected from the strace of the mysqld that servicing my query. It looked to be doing a sequential pread, based on the record size of 9 bytes. This was one reason I felt the query was doing a table scan to fulfill the query. One interesting experiment I did was to try to do a summation query with and without an index. The query with an index too 31 hrs. While the same data set without the index took 7 hours. The only way you could improve this is to make a combined index across (member_id, pts_awarded) and get rid of the only (member_id) index. Then MySQL would be able to again use only the index to resolve the query. Keep in mind this would increase your index size by about a third, so it would take more diskspace and you would fit less into cache, so it would decrease response times slightly for the count(*) query. Thank you for this suggestion. I've dropped the original index and have added the composite index to the table. It increased the size of the index file by about 50% as expected, but the summation query seems to be doing a much better job at scanning the data in the index rather than in the index/table combination. We'll see how long this summation query takes at this point. Thank you for your help and explanations, Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to MySQL on Linux
On Fri February 11 2005 19:15, Terry Riley wrote: Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). Well you should know that you can just install MySQL from withing your install cd. (at least with RH-9). If not, I recommend you use the RH update utility. up2date (requires root privileges) and then just choose MySQL from the list of items. Then installing it is as simple as clicking finish. Good luck and kind regards PS: if you prefer commandline utilities, you might interest yourself in apt-get See www.apt-get.org (it says it's for debian but I use it for my Fedora Core which is RedHat anyway) Once you've installed it, installing any package is as simple as typing (example for mysql) apt-get install mysql it will download the packages and perform the installation (note: root privileges required) Andy -- --- Registered Linux user number 379093 --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Auto Reply to your message (aavello@servinco.cl)
Dear [EMAIL PROTECTED] Please configure your email client to stop sending auto reply messages in reply to posts to the mysql mailing list. Filters are your friends to achieve this. Thank you in advance With kind regards Andy -- --- Registered Linux user number 379093 --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hmmm, verrry interesting on big summation
Harrison, Taking your suggestion and building a combined key of member_id and pts_awarded the query took 17 mins create table pts_sumC_snap select member_id, count(1) count, sum(pts_awarded) points from pts_awarded_snap group by member_id; Query OK, 12488780 rows affected (16 min 50.21 sec) Records: 12488780 Duplicates: 0 Warnings: 0 Building the combined index took 1 hr 12 mins for the total creation time of approximately 1.5 hours. Without any kind of index on the pts_awarded_snap table the query took 7 hours to build a similar summation table. When I built the index on member_id, the query took 31 hours to complete utilizing the index that took more time to build. This data set holds 776723372 rows. Bottom line, there appears to quite a difference between how fast indicies can be read and processed than how long it takes to process the index and data combination. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to MySQL on Linux
Terry Riley wrote: install my first Linux instead, using an ancient RedHat 7.3 distribution. Having done that successfully, and increased the memory from 256 to 768Mb, I think I'm now ready to install the latest MySQL on it. All my previous MySQL experience, unfortunatley, has been on WinNT, usually installed with the msi installer. Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). Personally, I hate installers, so I'd get the non-rpm Linux distro. Unzip/untar and you have a nice self-contained directory, so *you* know where everything is, in case you want to de-install, install a later version in parallel, etc. And as it happens, that's at the very top of the list on the MySQL downloads page :-) Linux (x86, glibc-2.2, static, gcc) Standard 4.1.9 26.9M HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: New to MySQL on Linux
I'd have to agree with Hassan here, with MySQL having the binaries built, it's quite easy if you can set PATHs and make some symbolic links (for service mysql start to work). Michael -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 2:31 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: New to MySQL on Linux Terry Riley wrote: install my first Linux instead, using an ancient RedHat 7.3 distribution. Having done that successfully, and increased the memory from 256 to 768Mb, I think I'm now ready to install the latest MySQL on it. All my previous MySQL experience, unfortunatley, has been on WinNT, usually installed with the msi installer. Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). Personally, I hate installers, so I'd get the non-rpm Linux distro. Unzip/untar and you have a nice self-contained directory, so *you* know where everything is, in case you want to de-install, install a later version in parallel, etc. And as it happens, that's at the very top of the list on the MySQL downloads page :-) Linux (x86, glibc-2.2, static, gcc) Standard 4.1.9 26.9M HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication issue: I/O thread dies immediately after START SLAVE with no logged errors
Looking at your my.cnf files I don't see where you've told the slave what server to connect to. The slave needs to know what server is the master. This is usually accomplished by including a couple of lines in my.cnf. If the file master.info is in the data directory it will override the my.cnf settings because it contains more information. So you either need to add lines like: master-host = hostname master-user = username master-password = password to my.cnf on the slave or add an appropriately formatted and constructed master.info file to the data directory. If the master.info file exists and is blank or doesn't include enough information, delete it. If it's there and looks right, include it's contents in your next mail here (you can blank out the username/password info). Best Regards, Bruce Tierney Thurban [EMAIL PROTECTED] wrote: Hi all. Sorry if you get this twice -- it was posted to mysql-replication earlier, but it doesn't look like that list is really used. I'm having a problem with my replication setup. This is my first time setting up replication, so this may be a simple problem. I'm using one master and one slave, both running debian-testing, and they both have brand new 4.1.9 mysql installs (via apt-get). The problem is that each time I do a START SLAVE, the I/O thread dies almost immediately. I can see it running only if I do START SLAVE; SHOW SLAVE STATUS\G on a single line. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why MySQL doesn't cache queries that populate temp tables?
Why MySQL insists on ignoring the query cache whenever I use the same query repeatedly to populate a temp table? So I have: create temporary table MyTable select SQL_CACHE * from SomeTable WHERE (A bunch of criteria) limit 1000; SomeTable is a read-only table. If I issue the query without the temp table population, it gets cached fine. It's just when I use the temp table MySQL stops caching. Is there a way to force MySQL to repopulate the temp table from the cache? Thanks! __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with historic aggregation of data
I need to get the aggregate data from various tables for a report. The idea is that we audit devices daily on a schedule, and also allow users to audit the devices by choosing certain tests to run. It is also the case that new tests are added daily. So the scheduled test today has more tests than yesterdays and that has more than the day before's, etc. I want to get a report that shows ALL tests ever run on the device in it's lifetime, but only the most recent of each test (and the date it was from). So if I ran tests like this: Date Device TestResult - -- - -- 02/011 100 [scheduled] blah blah blah... 02/011 101 [scheduled] blah blah blah... 02/011 102 [scheduled] blah blah blah... 02/011 105 [one off] foo foo foo... 02/021 100 [scheduled] blah blah blah... 02/021 101 [scheduled] blah blah blah... 02/021 102 [scheduled] blah blah blah... 02/021 103 [scheduled] ble ble ble... 02/021 106 [one off] bar bar bar... 02/031 100 [scheduled] blah blah blah... 02/031 101 [scheduled] blah blah blah... 02/031 102 [scheduled] blah blah blah... 02/031 103 [scheduled] ble ble ble... 02/031 104 [scheduled] blo blo blo... 02/012 100 [scheduled] blah blah blah... 02/012 101 [scheduled] blah blah blah... 02/012 102 [scheduled] blah blah blah... 02/012 106 [one off] bar bar bar... ... Etc ... What I'd expect to get back for device 1 is TestDate - 100 02/03 this is more current than others 101 02/03 this is more current than others 102 02/03 this is more current than others 103 02/03 this is more current than others 104 02/03 this is more current than others 105 02/01 since this was run long ago once 106 02/02 since this was ran recently My actual tables are pretty huge, and I'll spare you them. I also am coding this in PHP, in case I need to split this task up somehow. We are using v4.0.18 and can't change. I'm hoping there is some magic incantation of MAX(), GROUP BY, DISTINCT, that will harvest this info for me. Thanks in advance, Daevid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
no subject
Hannes Rohde [EMAIL PROTECTED] wrote: innodb_data_file_path=ibdata1:2G:autoextend innodb_buffer_pool_size=1200M innodb_additional_mem_pool_size=20M May not solve the replication issue, but if this is a 4GByte server that is dedicated to MySQL (ie you aren't using memory for anything else, like.. say... a web server or something) and the MySQL server is dedicated to InnoDB which the other memory settings seem to support - then you need to revise this. If the system is running a 64 bit OS you should be running a 64 bit binary and you should set the InnoDB Buffer pool to closer to 3200M... Additional memory of something like 256M or maybe even 512M would work well... If the system is running a 32 bit operating system (with a PIV it probably is 32 bit) I've found that the best mix for us has been at 1850M/256M - that won't break the memory limits of a 32 bit OS... You may need to adjust slightly depending on your OS. I you're running a 32 bit OS having gobs and gobs of ram isn't going to help a lot because you can't give InnoDB more than 2Gbytes. Of course if it is used for something other than MySQL then you clearly need to keep some memory available for that too. On our setup we don't use autoextend for the InnoDB data files, we make a whole lot of 2000M data files (like 40 of them) - some Operating systems don't deal well with large files - if your single InnoDB data file is a little on the large side, then maybe (small chance) the issue is there... Again that would probably affect more than just replication so probably isn't the cause. One of the things that affects replication is the network link between the two servers, are they both connected at high speed with similar duplex settings... Shouldn't be an issue as Replication isn't that hard on the network resources, but if you were running one server at 100Mbit/Full Duplex and the switch was running at a 100/half or something, weird things could be happening. We've even seen setups where one side was set to Autonegotiate and other side was set to 100/Full causing problems, because they end up at 10/Half on one side and 100/Full on the other, which gives pretty scary network performance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re : Slow Replication
Hannes Rohde [EMAIL PROTECTED] wrote: innodb_data_file_path=ibdata1:2G:autoextend innodb_buffer_pool_size=1200M innodb_additional_mem_pool_size=20M May not solve the replication issue, but if this is a 4GByte server that is dedicated to MySQL (ie you aren't using memory for anything else, like.. say... a web server or something) and the MySQL server is dedicated to InnoDB which the other memory settings seem to support - then you need to revise this. If the system is running a 64 bit OS you should be running a 64 bit binary and you should set the InnoDB Buffer pool to closer to 3200M... Additional memory of something like 256M or maybe even 512M would work well... If the system is running a 32 bit operating system (with a PIV it probably is 32 bit) I've found that the best mix for us has been at 1850M/256M - that won't break the memory limits of a 32 bit OS... You may need to adjust slightly depending on your OS. I you're running a 32 bit OS having gobs and gobs of ram isn't going to help a lot because you can't give InnoDB more than 2Gbytes. Of course if it is used for something other than MySQL then you clearly need to keep some memory available for that too. On our setup we don't use autoextend for the InnoDB data files, we make a whole lot of 2000M data files (like 40 of them) - some Operating systems don't deal well with large files - if your single InnoDB data file is a little on the large side, then maybe (small chance) the issue is there... Again that would probably affect more than just replication so probably isn't the cause. One of the things that affects replication is the network link between the two servers, are they both connected at high speed with similar duplex settings... Shouldn't be an issue as Replication isn't that hard on the network resources, but if you were running one server at 100Mbit/Full Duplex and the switch was running at a 100/half or something, weird things could be happening. We've even seen setups where one side was set to Autonegotiate and other side was set to 100/Full causing problems, because they end up at 10/Half on one side and 100/Full on the other, which gives pretty scary network performance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with historic aggregation of data
-Original Message- From: Daevid Vincent Sent: Friday, February 11, 2005 14:32 To: mysql@lists.mysql.com Subject: Need help with historic aggregation of data I need to get the aggregate data from various tables for a report. The idea is that we audit devices daily on a schedule, and also allow users to audit the devices by choosing certain tests to run. It is also the case that new tests are added daily. So the scheduled test today has more tests than yesterdays and that has more than the day before's, etc. I want to get a report that shows ALL tests ever run on the device in it's lifetime, but only the most recent of each test (and the date it was from). So if I ran tests like this: Date Device TestResult - -- - -- 02/011 100 [scheduled] blah blah blah... 02/011 101 [scheduled] blah blah blah... 02/011 102 [scheduled] blah blah blah... 02/011 105 [one off] foo foo foo... 02/021 100 [scheduled] blah blah blah... 02/021 101 [scheduled] blah blah blah... 02/021 102 [scheduled] blah blah blah... 02/021 103 [scheduled] ble ble ble... 02/021 106 [one off] bar bar bar... 02/031 100 [scheduled] blah blah blah... 02/031 101 [scheduled] blah blah blah... 02/031 102 [scheduled] blah blah blah... 02/031 103 [scheduled] ble ble ble... 02/031 104 [scheduled] blo blo blo... 02/012 100 [scheduled] blah blah blah... 02/012 101 [scheduled] blah blah blah... 02/012 102 [scheduled] blah blah blah... 02/012 106 [one off] bar bar bar... ... Etc ... What I'd expect to get back for device 1 is Test Date - 100 02/03 this is more current than others 101 02/03 this is more current than others 102 02/03 this is more current than others 103 02/03 this is more current than others 104 02/03 this is more current than others 105 02/01 since this was run long ago once 106 02/02 since this was ran recently SELECT device,test,MAX(date) FROM my_table GROUP BY device,test ORDER BY device,test This will give you all devices. and SELECT test,MAX(date) FROM my_table WHERE device=1 GROUP BY test ORDER BY test will give you results for device 1. My actual tables are pretty huge, and I'll spare you them. I also am coding this in PHP, in case I need to split this task up somehow. We are using v4.0.18 and can't change. I'm hoping there is some magic incantation of MAX(), GROUP BY, DISTINCT, that will harvest this info for me. Thanks in advance, Daevid. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering non-ascii characters from mysql data, null, tab etc
On Fri, 11 Feb 2005 12:46:29 +0100 (CET), wrote:
Tom adapting your script,
create table test (txt varchar(255)) Type=MyISAM;
insert into test values('Some Text\nand some more');
update test set txt = replace(txt,'\n','');
BTW
\n = null
\0 seems to be something else
Turns out my rotten character (they all seem to display as a hollow box) was a
\r
thanx
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with historic aggregation of data
select test, max(audit_date) from your_table where device = 1 group by test order by 1 --- Daevid Vincent [EMAIL PROTECTED] wrote: I need to get the aggregate data from various tables for a report. The idea is that we audit devices daily on a schedule, and also allow users to audit the devices by choosing certain tests to run. It is also the case that new tests are added daily. So the scheduled test today has more tests than yesterdays and that has more than the day before's, etc. I want to get a report that shows ALL tests ever run on the device in it's lifetime, but only the most recent of each test (and the date it was from). So if I ran tests like this: Date Device TestResult - -- - -- 02/011 100 [scheduled] blah blah blah... 02/011 101 [scheduled] blah blah blah... 02/011 102 [scheduled] blah blah blah... 02/011 105 [one off] foo foo foo... 02/021 100 [scheduled] blah blah blah... 02/021 101 [scheduled] blah blah blah... 02/021 102 [scheduled] blah blah blah... 02/021 103 [scheduled] ble ble ble... 02/021 106 [one off] bar bar bar... 02/031 100 [scheduled] blah blah blah... 02/031 101 [scheduled] blah blah blah... 02/031 102 [scheduled] blah blah blah... 02/031 103 [scheduled] ble ble ble... 02/031 104 [scheduled] blo blo blo... 02/012 100 [scheduled] blah blah blah... 02/012 101 [scheduled] blah blah blah... 02/012 102 [scheduled] blah blah blah... 02/012 106 [one off] bar bar bar... ... Etc ... What I'd expect to get back for device 1 is Test Date - 100 02/03 this is more current than others 101 02/03 this is more current than others 102 02/03 this is more current than others 103 02/03 this is more current than others 104 02/03 this is more current than others 105 02/01 since this was run long ago once 106 02/02 since this was ran recently My actual tables are pretty huge, and I'll spare you them. I also am coding this in PHP, in case I need to split this task up somehow. We are using v4.0.18 and can't change. I'm hoping there is some magic incantation of MAX(), GROUP BY, DISTINCT, that will harvest this info for me. Thanks in advance, Daevid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering non-ascii characters from mysql data, null, tab etc
On Fri, 11 Feb 2005 12:46:29 +0100 (CET), wrote:
Tom adapting your script,
create table test (txt varchar(255)) Type=MyISAM;
insert into test values('Some Text\nand some more');
update test set txt = replace(txt,'\n','');
BTW
\n = null
\0 seems to be something else
Turns out my rotten character (they all seem to display as a hollow box) was a
\r
thanx
zzapper (vim, cygwin, wiki zsh)
--
vim -c :%s%s*%CyrnfrTfcbafbeROenzSZbbyranne%|:%s)[R-T]) )Ig|:norm G1VGg?
http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Special Characters
Does anyone know where I can find a list of characters that cannot be inserted into a MySQL database as part of a string? I know that MySQL does not accept single and double quotes and that these have to replaced by their name code equivalents. Are there any other characters that are unacceptable? Any help would be greatly appreciated. Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to MySQL on Linux
Thanks to all who replied - food for thought... Cheers Terry - Original Message - Having inherited an elderly PIII/500MHz box with an 8Gb SCSI disk, that had an apparently unusable XP SP2 OS on it, I decided to wipe the disk and install my first Linux instead, using an ancient RedHat 7.3 distribution. Having done that successfully, and increased the memory from 256 to 768Mb, I think I'm now ready to install the latest MySQL on it. All my previous MySQL experience, unfortunatley, has been on WinNT, usually installed with the msi installer. Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). Suggestions, please? Cheers Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Special Characters
Asad Habib [EMAIL PROTECTED] wrote on 02/11/2005 04:03:32 PM: Does anyone know where I can find a list of characters that cannot be inserted into a MySQL database as part of a string? I know that MySQL does not accept single and double quotes and that these have to replaced by their name code equivalents. Are there any other characters that are unacceptable? Any help would be greatly appreciated. Thanks. - Asad May I refer you to the FINE MANUAL: http://dev.mysql.com/doc/mysql/en/string-syntax.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Remove spaces
Hi I have a table with a number of fields The table is already populated, however some entries have got spaces both before and after the data. Future imports into the table will have the spaces removed, however im still stuck with my extra spaces. I have checked the Mysql manual but could not figure out how to remove the extra spaces that are already in the dbase ? Any help appreciated. Regards John B -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remove spaces
update your_table set your_field = trim(your_field) --- John Berman [EMAIL PROTECTED] wrote: Hi I have a table with a number of fields The table is already populated, however some entries have got spaces both before and after the data. Future imports into the table will have the spaces removed, however im still stuck with my extra spaces. I have checked the Mysql manual but could not figure out how to remove the extra spaces that are already in the dbase ? Any help appreciated. Regards John B -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remove spaces
Thanks for this I did this: update mc_census set surname = trim(surname) however it fails with a syntax error ? I'm on 4.1 Regards John B -Original Message- From: Homam S.A. [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 21:29 To: mysql@lists.mysql.com Subject: Re: Remove spaces update your_table set your_field = trim(your_field) --- John Berman [EMAIL PROTECTED] wrote: Hi I have a table with a number of fields The table is already populated, however some entries have got spaces both before and after the data. Future imports into the table will have the spaces removed, however im still stuck with my extra spaces. I have checked the Mysql manual but could not figure out how to remove the extra spaces that are already in the dbase ? Any help appreciated. Regards John B -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remove spaces
It should work fine. These should execute fine on your
server:
create table yourtable(yourfield varchar(256));
insert into yourtable(yourfield)
values (' This needs to be trimmed ');
update yourtable set yourfield = trim(yourfield);
select * from yourtable;
I'm running version 4.1.9, but I think trim() was
supported long time ago.
--- John Berman [EMAIL PROTECTED] wrote:
Thanks for this
I did this:
update mc_census set surname = trim(surname)
however it fails with a syntax error ?
I'm on 4.1
Regards
John B
-Original Message-
From: Homam S.A. [mailto:[EMAIL PROTECTED]
Sent: 11 February 2005 21:29
To: mysql@lists.mysql.com
Subject: Re: Remove spaces
update your_table
set your_field = trim(your_field)
--- John Berman [EMAIL PROTECTED]
wrote:
Hi
I have a table with a number of fields
The table is already populated, however some
entries
have got spaces both
before and after the data.
Future imports into the table will have the spaces
removed, however im still
stuck with my extra spaces.
I have checked the Mysql manual but could not
figure
out how to remove the
extra spaces that are already in the dbase ?
Any help appreciated.
Regards
John B
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.7 -
Release
Date: 10/02/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Meet the all-new My Yahoo! - Try it today!
http://my.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.7 - Release
Date: 10/02/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.7 - Release
Date: 10/02/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remove spaces
Please post the error because this looks correct. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 16:05 To: 'Homam S.A.' Cc: mysql@lists.mysql.com Subject: RE: Remove spaces Thanks for this I did this: update mc_census set surname = trim(surname) however it fails with a syntax error ? I'm on 4.1 Regards John B -Original Message- From: Homam S.A. [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 21:29 To: mysql@lists.mysql.com Subject: Re: Remove spaces update your_table set your_field = trim(your_field) --- John Berman [EMAIL PROTECTED] wrote: Hi I have a table with a number of fields The table is already populated, however some entries have got spaces both before and after the data. Future imports into the table will have the spaces removed, however im still stuck with my extra spaces. I have checked the Mysql manual but could not figure out how to remove the extra spaces that are already in the dbase ? Any help appreciated. Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remove spaces
The error is simply: [JGSGB 4.1 Host] ERROR 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 'update mc_census set surname = trim(surname)' at line 6 John B -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 22:16 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Remove spaces Please post the error because this looks correct. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 16:05 To: 'Homam S.A.' Cc: mysql@lists.mysql.com Subject: RE: Remove spaces Thanks for this I did this: update mc_census set surname = trim(surname) however it fails with a syntax error ? I'm on 4.1 Regards John B -Original Message- From: Homam S.A. [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 21:29 To: mysql@lists.mysql.com Subject: Re: Remove spaces update your_table set your_field = trim(your_field) --- John Berman [EMAIL PROTECTED] wrote: Hi I have a table with a number of fields The table is already populated, however some entries have got spaces both before and after the data. Future imports into the table will have the spaces removed, however im still stuck with my extra spaces. I have checked the Mysql manual but could not figure out how to remove the extra spaces that are already in the dbase ? Any help appreciated. Regards John B -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Remove spaces
-Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 16:22 To: 'Tom Crimmins' Cc: mysql@lists.mysql.com Subject: RE: Remove spaces The error is simply: [JGSGB 4.1 Host] ERROR 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 'update mc_census set surname = trim(surname)' at line 6 line 6 ??? Something is wrong here. Are you excuting this from the mysql client? Something is getting sent to the server before this. Put a semi-colon before your statement and it will probably work, but I don't know exactly what the problem is. John B -Original Message- From: Tom Crimmins [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 22:16 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Remove spaces Please post the error because this looks correct. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 16:05 To: 'Homam S.A.' Cc: mysql@lists.mysql.com Subject: RE: Remove spaces Thanks for this I did this: update mc_census set surname = trim(surname) however it fails with a syntax error ? I'm on 4.1 Regards John B -Original Message- From: Homam S.A. [mailto:[EMAIL PROTECTED] Sent: 11 February 2005 21:29 To: mysql@lists.mysql.com Subject: Re: Remove spaces update your_table set your_field = trim(your_field) --- John Berman [EMAIL PROTECTED] wrote: Hi I have a table with a number of fields The table is already populated, however some entries have got spaces both before and after the data. Future imports into the table will have the spaces removed, however im still stuck with my extra spaces. I have checked the Mysql manual but could not figure out how to remove the extra spaces that are already in the dbase ? Any help appreciated. Regards John B --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering non-ascii characters from mysql data, null, tab etc
zzapper wrote:
On Fri, 11 Feb 2005 12:46:29 +0100 (CET), wrote:
Tom adapting your script,
create table test (txt varchar(255)) Type=MyISAM;
insert into test values('Some Text\nand some more');
update test set txt = replace(txt,'\n','');
BTW
\n = null
\0 seems to be something else
Turns out my rotten character (they all seem to display as a hollow box) was a
\r
thanx
zzapper (vim, cygwin, wiki zsh)
--
No. \n is a newline, \r is a return, and \0 is the null character C uses to
terminate strings. Continuing your example:
mysql CREATE TABLE test (id INT, txt VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)
mysql INSERT INTO test VALUES (1, 'Some Text\0 and some more'),
- (2, 'Some Text\nand some more');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM test;
+--+--+
| id | txt |
+--+--+
|1 | Some Text|
|2 | Some Text
and some more |
+--+--+
2 rows in set (0.00 sec)
mysql UPDATE test SET txt = REPLACE(txt, '\0', '');
Query OK, 1 row affected (0.13 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql UPDATE test SET txt = REPLACE(txt, '\n', ' ');
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0
mysql SELECT * FROM test;
+--+-+
| id | txt |
+--+-+
|1 | Some Text and some more |
|2 | Some Text and some more |
+--+-+
2 rows in set (0.00 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb auto increment - reset itself automatically?
Hi, My database is mostly made up of MyIsam tables, and some InnoDB tables. One particular Innodb table works fine with an auto increment field. The table is updated often, records being added and deleted at pretty much the same rate. So, there are only a very few records in the table at any given time. It was being used and the auto increment value was around 21. Recently, after the records were deleted in the system (by my client - through a database system that I created - NOT directly via the db), I noticed that new records to the table start with the auto increment field '1'. I don't understand this. The autoincrement field seems to have reset itself back to 0. I know this because there are now 3 records in that table with ids 1,2,3 I tested it again by adding a new record, which was assigned the id 4. Then, i deleted that, and added a new record, which was assigned the id 5. So its working as normal again! I just dont understand how Mysql reset the autoincrement field from 21 back to 0 Can anyone think of a reason why this would happen? I'm really worried about the database now :( Thanks very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb auto increment - reset itself automatically?
http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html Rishi Daryanani wrote: Hi, My database is mostly made up of MyIsam tables, and some InnoDB tables. One particular Innodb table works fine with an auto increment field. The table is updated often, records being added and deleted at pretty much the same rate. So, there are only a very few records in the table at any given time. It was being used and the auto increment value was around 21. Recently, after the records were deleted in the system (by my client - through a database system that I created - NOT directly via the db), I noticed that new records to the table start with the auto increment field '1'. I don't understand this. The autoincrement field seems to have reset itself back to 0. I know this because there are now 3 records in that table with ids 1,2,3 I tested it again by adding a new record, which was assigned the id 4. Then, i deleted that, and added a new record, which was assigned the id 5. So its working as normal again! I just dont understand how Mysql reset the autoincrement field from 21 back to 0 Can anyone think of a reason why this would happen? I'm really worried about the database now :( Thanks very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb auto increment - reset itself automatically?
You may want to read this section of the manual: http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Rishi Daryanani [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 22:16 To: mysql@lists.mysql.com Subject: Innodb auto increment - reset itself automatically? Hi, My database is mostly made up of MyIsam tables, and some InnoDB tables. One particular Innodb table works fine with an auto increment field. The table is updated often, records being added and deleted at pretty much the same rate. So, there are only a very few records in the table at any given time. It was being used and the auto increment value was around 21. Recently, after the records were deleted in the system (by my client - through a database system that I created - NOT directly via the db), I noticed that new records to the table start with the auto increment field '1'. I don't understand this. The autoincrement field seems to have reset itself back to 0. I know this because there are now 3 records in that table with ids 1,2,3 I tested it again by adding a new record, which was assigned the id 4. Then, i deleted that, and added a new record, which was assigned the id 5. So its working as normal again! I just dont understand how Mysql reset the autoincrement field from 21 back to 0 Can anyone think of a reason why this would happen? I'm really worried about the database now :( Thanks very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
increased disk ops after alter table
Hi All, I had to do an alter table on a very large InnoDB table (100 million rows avg. row size 300 bytes inclusive of index length). This table is on a 200G disk and the server has 4G RAM. I also archived 50 million rows to another table. So the total table size is now 1/2 of its original. Since the alter table was run, a reporter process (that does selects on the table) has been taking longer than before. The performance has been degrading with evey attempt to fix the issue. I tried the following to fix the problem: 1. analyze table table name; 2. alter table table Name type=InnoDB; (to fix defragmented index data after large deletes) 3. alter table table name order by timestamp; (to make the data sequential if the alter table from previous steps had screwed up the order) Each of the above steps (done over a few days) have progressively degraded the performance of the reporting process to an extent that it now takes twice the time to run the same queries. The disk is 100% busy when the query is being executed. Before the alter table was run, the disk was Idle all thro' the reporter's run and there was very little disk access. It seemed that the data was being served from the filesystem cache. Running explain on the queries indicate that the correct index is being used and there are no table scans. Although all the queries run by the reporter are now logged as slow the number of rows examined is always equal to number of rows sent. Has anyone come across a similar problem. If so, what should i do to fix it? Your responses would be greatly appriciated. thanks, -pradeep PS: The InnoDB buffer pool size was also increased to 800MB after the alter table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New to MySQL on Linux
On Friday 11 February 2005 09:15, Terry Riley said something like: Having inherited an elderly PIII/500MHz box with an 8Gb SCSI disk, that had an apparently unusable XP SP2 OS on it, I decided to wipe the disk and install my first Linux instead, using an ancient RedHat 7.3 distribution. First suggestion: get something recent: Suse 9.2, Mandrake 10.1, Fedora Core 3, the latest Debian. A distro that old will have major security (and probably usability issues). Now the question: If I'm only using this as a database (no development) on RH7.3, which is the preferred download? I am confused by the plethora of options available for Linux. Just need something that is relatively simple to install (either 4.1.9 or 5.0.x). I would doubt the current MySQL RPM's would support something as old as RH 7.3. If you install something recent, there will be recent versions of MySQL (Mandrake even has 5.0 in the contrib section, I would assume Fedora would too. You will have to intstall the server portion, and probably the client portion. You then can use the MySQL GUI tools to admin the box from a Windows machine. Using something like Mandrake or Fedora, their installer tools will resolve all the dependencies for you. Hope that gets you started a little. If you need more detail, feel free to ask. j- k- -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- 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 mysql-listers mysql select version(); ++ | version() | ++ | 4.1.7-standard-log | ++ 1 row in set (0.10 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]:~ following mysql-behaviour made me loose several hours yesterday: 1. a root user is installed with password having overall permission over the database. i can access the db through this root user in the following manner: [EMAIL PROTECTED]:~ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 2. for the sake of an application i added a second user named root, but this one from the host rosetta. i added the password for this second root user using use mysql; update user set password ... where user = root and host = rosetta.ayni.com; flush privileges; the password i attributed to this root-user is the same as that one for the original root user. my intention was to access the database in the following manner: [EMAIL PROTECTED]:~ mysql -h rosetta -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 3. this second root-user, however, presented the following strange behaviour: i could access the database in the manner described in 2. several times. and then, at once, access was denied, and i had to redo the flush privileges; from then on, i could access the db again several times as described in 2., but again after some time, access was denied and i had to redo the flush privileges; 4. what i did then: 4.a took a mysqldump of the entire mysql installation. 4.b stopped the mysql daemon 4.c removed the entire directory where the database had been 4.d uninstalled mysql-server 4.e re-installed mysql-server 4.f re-created the database from the mysqldump 5. remains to be seen if now mysql is stable suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
