Problem with 4.1.3 that I not had with 4.0.20
Good Morning Community! I have a problem with 4.1.3 that I not had with 4.0.20. Here is my table: CREATE TABLE url_cat_copy ( id int(11) NOT NULL auto_increment, url varchar(100) default NULL, domain varchar(100) default NULL, bytes bigint(20) default NULL, hits bigint(20) default NULL, bytes_priv decimal(20,2) default NULL, hits_priv decimal(20,2) default NULL, bytes_bus decimal(20,2) default NULL, hits_bus decimal(20,2) default NULL, cat1 varchar(100) default NULL, cat2 varchar(100) default NULL, cat3 varchar(100) default NULL, PRIMARY KEY (id) ); Here is a sample-data: INSERT INTO url_cat_copy VALUES (272, '195.120.225.10', '195.120.225.10', 3040, 3, 2533.33, 2.50, 506.67, 0.50, 'Shopping', 'Religion', 'Music/Webradio'); Here ist the query: SELECT * FROM url_cat_copy WHERE bytes - ( bytes_priv + bytes_bus ) 0 OR hits - (hits_priv + hits_bus) 0; The result from the query should be no record (empty recordset). With 4.0.20 it was, with 4.1.3 it is not. Can you reconstruct the problem on your 4.1.3 installation? Why has the result changed? How I must change the query, that the result is an empty recordset again? Thank you in advance! Martin Rytz
reports slows down network
We have MySQL and MySQL-MAX on a RH8 release. The server specs is as follows: Dual Xeon CPU 2.8GHz 2G DDR Memory 215GB HDD storage SCSI U160 HDD When a report (large one) runs I've noticed the following through tops. mysqld-max = 97.2 - 99.9 %CPU with 1.2% memory usage In the top of tops I've noticed that all 4 CPU (CPU0-CPU3) never gets over 50% for more than 2-3 seconds. Can someone tell me why the reports makes the server run slower than expected and how can I set mysqld-max to make use of each CPU thus keep the CPU usage down. Is there a command I can run in mysql that can give me an accurate reading of how much cpu and memory is being used? Thanks Jon L. Miller, ASE, CNS, CLS, MCNE Director/Sr Systems Consultant MMT Networks Pty Ltd http://www.mmtnetworks.com.au I don't know the key to success, but the key to failure is trying to please everybody. -Bill Cosby !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META http-equiv=Content-Type content=text/html; charset=iso-8859-1 META content=MSHTML 6.00.2800.1458 name=GENERATOR/HEAD BODY style=MARGIN-TOP: 2px; FONT: 10pt Arial; MARGIN-LEFT: 2px DIVWe have MySQL and MySQL-MAX on a RH8 release.nbsp; The server specs is as follows:/DIV DIVDual Xeon CPU 2.8GHz/DIV DIV2G DDR Memory/DIV DIV215GB HDD storage SCSI U160 HDD/DIV DIVnbsp;/DIV DIVWhen anbsp;report (large one) runs I've noticed the following through tops./DIV DIVmysqld-max = 97.2 - 99.9 %CPU with 1.2% memory usage/DIV DIVIn the top of tops I've noticed that all 4 CPU (CPU0-CPU3)nbsp;never gets over 50% for more than 2-3 seconds./DIV DIVnbsp;/DIV DIVCan someone tell me why the reports makes the server run slower than expected and how can I set mysqld-max to make use of each CPU thus keep the CPU usage down./DIV DIVIs there a command I can run in mysql that can give me an accurate reading of how much cpu and memory is being used?/DIV DIVnbsp;/DIV DIVnbsp;/DIV DIVThanks/DIV DIVnbsp;/DIV DIVnbsp;/DIV DIVJon L. Miller,nbsp; ASE, CNS, CLS, MCNEBRDirector/Sr Systems ConsultantBRMMT Networks Pty LtdBRA href=http://www.mmtnetworks.com.au;http://www.mmtnetworks.com.au/A/DIV DIVnbsp;/DIV DIVI don't know the key to success, but the key to failureBRnbsp;is trying to please everybody. -Bill Cosby/DIV DIVnbsp;/DIV DIVnbsp;/DIV/BODY/HTML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with 4.1.3 that I not had with 4.0.20
try this query SELECT bytes, bytes_priv, bytes_bus, hits, hits_priv, hits_bus, bytes - ( bytes_priv + bytes_bus ), hits - (hits_priv + hits_bus) FROM url_cat_copy WHERE (bytesround(bytes_priv+bytes_bus,0)) or (hitsround(hits_priv + hits_bus,0)) i think its got something to do with the decimal type.. try googling or search in the manual book i've red somewhere, that a decimal number in computer never as exact as what they have meant.. HTH Leo On Tue, 24 Aug 2004 08:41:47 +0200, Martin Rytz [EMAIL PROTECTED] wrote: Good Morning Community! I have a problem with 4.1.3 that I not had with 4.0.20. Here is my table: CREATE TABLE url_cat_copy ( id int(11) NOT NULL auto_increment, url varchar(100) default NULL, domain varchar(100) default NULL, bytes bigint(20) default NULL, hits bigint(20) default NULL, bytes_priv decimal(20,2) default NULL, hits_priv decimal(20,2) default NULL, bytes_bus decimal(20,2) default NULL, hits_bus decimal(20,2) default NULL, cat1 varchar(100) default NULL, cat2 varchar(100) default NULL, cat3 varchar(100) default NULL, PRIMARY KEY (id) ); Here is a sample-data: INSERT INTO url_cat_copy VALUES (272, '195.120.225.10', '195.120.225.10', 3040, 3, 2533.33, 2.50, 506.67, 0.50, 'Shopping', 'Religion', 'Music/Webradio'); Here ist the query: SELECT * FROM url_cat_copy WHERE bytes - ( bytes_priv + bytes_bus ) 0 OR hits - (hits_priv + hits_bus) 0; The result from the query should be no record (empty recordset). With 4.0.20 it was, with 4.1.3 it is not. Can you reconstruct the problem on your 4.1.3 installation? Why has the result changed? How I must change the query, that the result is an empty recordset again? Thank you in advance! Martin Rytz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why VARCHAR TO CHAR automatically when the length less than 4.
I have a questions about varchar columns change to CHAR columns automatically. See http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html In addition to that - it doesn't really matter as the CHAR datatype isn't properly implemented in MySQL and behaves the same as the VARCHAR datatype. I don't believe that is correct. We recently had a very large disk space problem that was solved when we discovered that a column that should have been varchar was instead char, and was taking up excess storage because of it (which a datatype acting like varchar wouldn't do). It should be noted that I wasn't talking about physical storage but about the logical datatype. How things are stored on disk is totally independant from the logical datatype - you can do whatever you like with it. 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]
Specify which domain to connect from
Hello, Is there someway to specify which domain one would like to connect from in the mysql console. When connecting on my server I always get connected through localhost. I would like to connect through my DNS name. quasi example : mysql -u [EMAIL PROTECTED] -p regards /Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specify which domain to connect from
maybe u can try: mysql -u [EMAIL PROTECTED] -p -h dnsbasedhostname --- Jonas Ladenfors [EMAIL PROTECTED] wrote: Hello, Is there someway to specify which domain one would like to connect from in the mysql console. When connecting on my server I always get connected through localhost. I would like to connect through my DNS name. quasi example : mysql -u [EMAIL PROTECTED] -p regards /Jonas -- 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 - Helps protect you from nasty viruses. 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]
--skip-name-resolve doesn't work
Hello, today one of our caching nameservers failed. Although a second nameserver was listed in /etc/resolf.conf and mysqld was started with --skip-name-resolv, mysql struggled and took very long to accept new connections. It's obvious, that mysql is doing reverse-dns lookups against what configured him to do. All entries in the Host table contain % instead of ip-addresses. But that shouldn't be a reason. With % I mean: Any ip-address. Mysql seems to think: Any hostname, so resolv the hostname from the ip-address first. How can I completely prevent mysql from looking up hosts? I definetely can't define every ip-address because it really can be _any_. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Testing for the existence of an index
Jesse, mysql SHOW INDEX FROM mytable; gives you all indexes for `mytable`; you can process the results with perl. Thomas Spahni On Mon, 23 Aug 2004, Jesse Sheidlower wrote: I have a database where, most of the time, I'm bulk-loading data into new tables from an external source, several million rows at a time. For this purpose, I create the tables without indexes, and then add all my indexes after the load is done, for efficiency purposes. I'd now like to add the possibility of adding some data to the existing database. In this case, the indexes exist, and then the new data will be indexed as it goes in (which is OK given the relatively small amount of data to be processed this way). I'd like to be able to test for the existence of an index, so that after the bulk-load, I can see if there are indexes, there won't be any, and I can create them; but after an addition to an existing database, there will be indexes, and I won't create them. How do I do this? It wasn't clear from the manual, and I'm away from my books now so can't look there for advice. I'm using Perl to process the data, if there's a Perlish way of doing things that would be easier than SQL. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to delete records after particular position ?
Hi, Here i am again. I can select particular record by using LIMIT 9,1 But e.g. if i want to delete this record then how shall i do ? I tried to use Delete from table name Limit 9, 1 But i am getting erorr. pls can anybody let me know the exact syntax for this ? regards Manisha
Re: How to delete records after particular position ?
On Monday 23 August 2004 11:08, Manisha Sathe might have typed: Hi, Here i am again. I can select particular record by using LIMIT 9,1 But e.g. if i want to delete this record then how shall i do ? I tried to use Delete from table name Limit 9, 1 Said record is probably keyed in some manner - either with an auto-increment field, or it will (should) have identifying characteristics. See the manual for delete syntax using matching fields. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to delete records after particular position ?
Manisha Sathe [EMAIL PROTECTED] wrote on 23/08/2004 11:08:10: Here i am again. I can select particular record by using LIMIT 9,1 But e.g. if i want to delete this record then how shall i do ? I tried to use Delete from table name Limit 9, 1 But i am getting erorr. pls can anybody let me know the exact syntaxfor this ? MySQL does not allow this because it is, in the general case, extremely dangerous. Suppose someone else had added or selected records from the table between your SELECT and DELETE? You would then delete a different record to the one you intended to. Generally you can only delete using a WHERE clause so that you are sure that you are accessing the column you intend. The easiest way of doing this, if you have no other obvious way of doing it, is to an AUTO_INCREMENT column to your table, which will give each row a unique reference number. You can then deleted the row having that reference number knowing you will deleted the right record regardless of what else may be occurring. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to delete records after particular position ?
if you dont want to add an auto-increment column, you can probably specify ALL columns and their values in WHERE clause and end with LIMIT 1. eg: DELETE FROM Table WHERE Column1=Value1 AND Column2=Value2 ... LIMIT 1 --- Manisha Sathe [EMAIL PROTECTED] wrote: Hi, Here i am again. I can select particular record by using LIMIT 9,1 But e.g. if i want to delete this record then how shall i do ? I tried to use Delete from table name Limit 9, 1 But i am getting erorr. pls can anybody let me know the exact syntax for this ? regards Manisha ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to?
An existing database table structures has changed, I want to create the same database with new table structures. I do not want to lose any data. How can I do this? do a backup how? regards ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help for a query
select name from mytable a where changedate (select changedate from mytable b where a.name=b.name and a.changedate != b.changedate); or: select name from mytable a where exists (select * from mytable b where a.name=b.name and a.changedate b.changedate); Or, especially if your MySQL is earlier than 4.1 and you don't have subqueries: (also untested) select t1.name from mytable t1, mytable t2 where t1.name = t2.name and t1.date = 'd1' and t2.date = 'd2' and t1.changeDate t2.changeDate - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to delete records after particular position ?
I believe the syntax is Delete from MyTable Where MyCriteria LIMIT HowMany. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Manisha Sathe [mailto:[EMAIL PROTECTED] Sent: Monday, August 23, 2004 6:08 AM To: [EMAIL PROTECTED] Subject: How to delete records after particular position ? Hi, Here i am again. I can select particular record by using LIMIT 9,1 But e.g. if i want to delete this record then how shall i do ? I tried to use Delete from table name Limit 9, 1 But i am getting erorr. pls can anybody let me know the exact syntax for this ? regards Manisha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
runaway process eating all cpu-time?
Hi there, after a system-crash I got the following problem. Some seconds after starting mysql (v4.0.20) a process will stay there taking all cpu-time it can get (99.9% ;)) ... and it can't be terminated, just killed completely. mysqlcheck ran through without any problems, the logs tell nothing. I just did a strace on the last process doing that: --- time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 sched_yield() = 0 time(NULL) = 1093348120 rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP 32], 8) = 0 rt_sigsuspend([HUP INT QUIT PIPE TERM TSTP] unfinished ... --- SIGRT_0 (Real-time signal 0) --- --- Any idea where to look at that problem further? The system is a linux - redhat-7.2 with dual athlon-mp, 2 GB ram, running kernel-2.4.26. Using the official mysql.com-binaries (rpm-install). TIA, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL with Intel Compiler
Dear Friends, We want to use MySQL compiled with Intel Compiler to get superior performance. Our server is a rack mounted HP DL 380 server with Redhat Enterprise Linux Advanced Server 3.0. We have used mysql-standard-4.1.3-beta-pc-linux-i686-icc.tar.gz. But we are getting the following error : --- [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql --force Installing all prepared tables Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! 040824 17:29:49 Aborting 040824 17:29:49 ./bin/mysqld: Shutdown complete --- For more details(Full text of Error Message), please see the attachment. But mysql user group is existing. Please kindly help me. Thanks in Advance With Regards Santhanam [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql --force Installing all prepared tables Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! 040824 17:29:49 Aborting 040824 17:29:49 ./bin/mysqld: Shutdown complete ./bin/mysql_create_system_tables: line 651: 2177 Broken pipe cat END_OF_DATA use mysql; $c_d $i_d $c_h $i_h $c_u $i_u $c_f $i_f $c_t $c_c $c_ht $c_hc $c_hr $c_hk $c_tzn $i_tzn $c_tz $i_tz $c_tzt $i_tzt $c_tztt $i_tztt $c_tzls $i_tzls END_OF_DATA Installation of system tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! [EMAIL PROTECTED] mysql]# -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to?
On Tue, 24 Aug 2004 11:57:42 +0100, A Z wrote: An existing database table structures has changed, I want to create the same database with new table structures. I do not want to lose any data. How can I do this? do a backup how? Sure.. There are several ways to accomplish this. 1. Create new tables under a new user. Copy data from olduser.tablename to newuser.tablename. 2. Export data, and reload data. 3. (for some type of changes) Alter table 4. Dump the table username.dmp. Alter the file to the new schema, manipulate sql insert statement to convert the data. [make sure that you keep a clean copy of the original data.] source the dump file \. username.dmp and of course there are many varations on a theme. you are only limited by your imagination (and time...and money...) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: runaway process eating all cpu-time?
Do you have anything in the show processlist? Are you using innodb table types and a rollback is occurring? -Original Message- From: Thomas - Lists To: [EMAIL PROTECTED] Sent: 8/24/04 6:49 AM Subject: runaway process eating all cpu-time? Hi there, after a system-crash I got the following problem. Some seconds after starting mysql (v4.0.20) a process will stay there taking all cpu-time it can get (99.9% ;)) ... and it can't be terminated, just killed completely. mysqlcheck ran through without any problems, the logs tell nothing. I just did a strace on the last process doing that: --- time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 sched_yield() = 0 time(NULL) = 1093348120 rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP 32], 8) = 0 rt_sigsuspend([HUP INT QUIT PIPE TERM TSTP] unfinished ... --- SIGRT_0 (Real-time signal 0) --- --- Any idea where to look at that problem further? The system is a linux - redhat-7.2 with dual athlon-mp, 2 GB ram, running kernel-2.4.26. Using the official mysql.com-binaries (rpm-install). TIA, Thomas -- 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: runaway process eating all cpu-time?
Hi Victor, thanks for your reply. Victor Pendleton wrote: Do you have anything in the show processlist? Are you using innodb table types and a rollback is occurring? mysql show processlist; +---+-+---+---++--++--+ | Id| User| Host | db| Command| Time | State | Info | +---+-+---+---++--++--+ | 810 | DELAYED | | phpadsnew | Delayed_insert | 3| Waiting for INSERT | | | 10125 | root| localhost | NULL | Sleep | 70 || NULL | | 12020 | root| localhost | NULL | Query | 0| NULL | show processlist | +---+-+---+---++--++--+ Could it be the delayed_insert thread? I checked its tables extended already. Any way to see a connection-id - process-id relation? I'm using innodb just for one table at the moment. but its idle too. Any ideas? Thanks, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
frecvent table corruption
Hello. My company is using mysql as a backed to Bugzilla. However, recently, as the number of users that are logging bugs increased we are experiencing frequent table corruption and loss of data. The actual error message we get is: SELECT query FROM namedqueries WHERE userid = 4 AND name = '(Default query)': Got error 127 from table handler at globals.pl line 276. After repairing the table by using the mysql Control Center we lose lots of data, even 60% of it. The mysql version is 4.0.18 installed on Red Hat. Is this a bug? How exactly do we fix this, with a patch? Regards,Alex
newbie's trick question?
I'm learning SQL from Access program. Since each category vary in how they are monitored: -Tracked by hours only -Tracked by qty, hours and qty/hours. -Tracked by pages, orders, hours, pages/hours and orders/hours. I'm trying to figure out a way to select mrcategories.id during SELECTION process i.e.: SELECT concat(associates.lastname,', ',associates.firstname) as Operator, mrdaily.kronoshours as Hours, mrdaily.pto as PTO, /* My question section / select mrcategories.id=3, mrcategories.category as PM Order, mrentries.quantity as QTY, mrentries.hours as HOURS, round(mrentries.quantity/mrentries.hours,2) as 'QTY PPH', select mrcategories.id=4 mrcategories.category as SURVEY, mrentries.quantity as QTY, mrentries.hours as HOURS, round(mrentries.quantity/mrentries.hours,2) as 'QTY PPH', select mrcategories.id=12 mrcategories.category as KEY mrentries.hours as HOURS, select mrcategories.id=15 mrcategories.category as REFUND LETTERS, mrentries.quantity as PAGES, mrentries.hours as HOURS, round(mrentries.quantity/mrentries.hours,2) as 'PAGES PPH', ETC... /* End of my question section / FROM mrentries, associates, mrdaily, mrcategories WHERE mrentries.iddaily=mrdaily.id AND associates.id=mrdaily.idassociates AND entrydate='2004-08-03' AND mrcategories.id=mrentries.category; Is it possible? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL with Intel Compiler
Can't change to run as user 'mysql' ; Please check that the user exists! man useradd _ Steve Poirier -Original Message- From: Santhanam [mailto:[EMAIL PROTECTED] Sent: August 24, 2004 8:02 AM To: [EMAIL PROTECTED] Subject: MySQL with Intel Compiler Dear Friends, We want to use MySQL compiled with Intel Compiler to get superior performance. Our server is a rack mounted HP DL 380 server with Redhat Enterprise Linux Advanced Server 3.0. We have used mysql-standard-4.1.3-beta-pc-linux-i686-icc.tar.gz. But we are getting the following error : --- [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql --force Installing all prepared tables Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! 040824 17:29:49 Aborting 040824 17:29:49 ./bin/mysqld: Shutdown complete --- For more details(Full text of Error Message), please see the attachment. But mysql user group is existing. Please kindly help me. Thanks in Advance With Regards Santhanam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL with Intel Compiler
Sorry, didn't see you already had a mysql user. David has a better english than mine: It looks like your MySQL server process is hitting a problem when it tries to change its user id to that of the user 'mysql'. There are two possible reasons for this: 1. You're starting the server process as a user who isn't 'root', so the user doesn't have the necessary privilege to change the UID of the server process. -- OR -- 2. You *are* starting the server process as 'root', but your system doesn't have a user called 'mysql', so the process is trying, and failing, change its UID to a user that doesn't exist. Try finger mysql to see whether you actually have a user named 'mysql'. Thinking about it, there are other possibilities, assuming that you *do* have a user named 'mysql'. 3. The 'mysql' user doesn't have a home directory, or for some reason that user doesn't have write-access to its home directory. 4. The 'mysql' user doesn't have the necessary access privileges to /path/to/mysql and all of its sub-directories. source http://www.talkaboutprogramming.com/group/comp.lang.java.databases/messages/ 57463.html _ Steve Poirier -Original Message- From: Santhanam [mailto:[EMAIL PROTECTED] Sent: August 24, 2004 8:02 AM To: [EMAIL PROTECTED] Subject: MySQL with Intel Compiler Dear Friends, We want to use MySQL compiled with Intel Compiler to get superior performance. Our server is a rack mounted HP DL 380 server with Redhat Enterprise Linux Advanced Server 3.0. We have used mysql-standard-4.1.3-beta-pc-linux-i686-icc.tar.gz. But we are getting the following error : --- [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql --force Installing all prepared tables Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! 040824 17:29:49 Aborting 040824 17:29:49 ./bin/mysqld: Shutdown complete --- For more details(Full text of Error Message), please see the attachment. But mysql user group is existing. Please kindly help me. Thanks in Advance With Regards Santhanam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Specify which domain to connect from
No, there is no option to control the client's apparent hostname. Considering that restricting the allowable connecting hosts is part of mysql's access control mechanism, this makes sense. Whether your client hostname is localhost or a domain name/ip address is controlled by your connection method. By default, mysql connects to the server on localhost via unix socket. When connecting this way, the client hostname is localhost, as well. If you specify a domain name/ip address of the server with -h, however, you connect to that server via tcp/ip, so the client hostname is your domain name/ip address. So, you can accomplish what you want. If you and the mysql server are on myhost.mydomain.com, mysql -h myhost.mydomain.com -u jonas -p connects you to the server via tcp/ip, with myhost.mydomain.com as the connecting hostname. That said, I can't think of any reason why you would want to do this. tcp/ip adds overhead, so it is less efficient. In other words, connecting to localhost is the default partly because it's better. No doubt you do have a reason for wanting this, but there may be a better solution. If you told us why you want your connection to appear to come from your hostname (tcp/ip) instead of from localhost (unix socket), someone might suggest a better way. Michael Jonas Ladenfors wrote: Hello, Is there someway to specify which domain one would like to connect from in the mysql console. When connecting on my server I always get connected through localhost. I would like to connect through my DNS name. quasi example : mysql -u [EMAIL PROTECTED] -p regards /Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Question
Howdy all, Quick question about foreign keys. If I have a database with foreign keys setup, then drop one of the tables (which is referenced by many of the others) and re-add the table, will the existing FKs work? I'm seeing errors in SHOW INNODB STATUS under the LATEST FOREIGN KEY ERRORS section that claim that the referenced table (the one I dropped and readded) doesn't exist. It does, I can query it and join to it, but I can't do inserts into any table that references the table. Any ideas? Cheers, Tripp ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql2000 to mysql
Hello, Can someone advise me of the best/easiest way to move an entire DB (Tables and data) from sql2000 (my client) to mySQL (my System)? I need to advise someone on how I wish the data sent to me. Any help would be appreciated. Thanks, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication binary log files location
I'm trying to move my binary log files onto a different drive than our main data drive to get a little performance boost. the drives are set up like so: drive 1 (sda): swap /boot /usr drive 2 (sdb): / mysql is installed in /usr/local/mysql and its data directory is /usr/local/mysql/var I want to set the binary logs to go to the 2nd drive, sdb, so I made a new directory, /logging I went into logging and changed its ownership and group to mysql... chown -R mysql . chgrp -R mysql . [EMAIL PROTECTED] logging]# pwd /logging [EMAIL PROTECTED] logging]# ls -al total 8 drwxr-xr-x2 mysqlmysql4096 Aug 24 04:26 . drwxr-xr-x 23 root root 4096 Aug 24 04:53 .. then went into my.cnf and put: log-bin=/logging but when I try to start mysql this way: [EMAIL PROTECTED] mysql]# bin/mysqld_safe --user=mysql no logging, and I get this in the .err file 040824 5:46:53 Could not use /logging for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. error 13 is typically a unix permissions error, right? but I changed the /logging directory to be owned by mysql (and am running mysqld_safe --user=mysql) any ideas? thanks, -L Luke Crouch 918-461-5326 [EMAIL PROTECTED]
RE: sql2000 to mysql
Are you wanting to move Foreign keys, Triggers, Stored procedures and the like as well or just the data? -Original Message- From: Tim Winters To: [EMAIL PROTECTED] Sent: 8/24/04 10:36 AM Subject: sql2000 to mysql Hello, Can someone advise me of the best/easiest way to move an entire DB (Tables and data) from sql2000 (my client) to mySQL (my System)? I need to advise someone on how I wish the data sent to me. Any help would be appreciated. Thanks, Tim -- 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: Specify which domain to connect from
At 11:27 -0400 8/24/04, Michael Stassen wrote: No, there is no option to control the client's apparent hostname. Considering that restricting the allowable connecting hosts is part of mysql's access control mechanism, this makes sense. Whether your client hostname is localhost or a domain name/ip address is controlled by your connection method. By default, mysql connects to the server on localhost via unix socket. When connecting this way, the client hostname is localhost, as well. If you specify a domain name/ip address of the server with -h, however, you connect to that server via tcp/ip, so the client hostname is your domain name/ip address. So, you can accomplish what you want. If you and the mysql server are on myhost.mydomain.com, mysql -h myhost.mydomain.com -u jonas -p connects you to the server via tcp/ip, with myhost.mydomain.com as the connecting hostname. That said, I can't think of any reason why you would want to do this. tcp/ip adds overhead, so it is less efficient. In other words, connecting to localhost is the default partly because it's better. No doubt you do have a reason for wanting this, but there may be a better solution. If you told us why you want your connection to appear to come from your hostname (tcp/ip) instead of from localhost (unix socket), someone might suggest a better way. To connect to a local server using TCP/IP, you can also specify a host of 127.0.0.1 to use the loopback interface. One reason to do this is when you are connecting to a replication master running on the same machine. If you specify localhost, the connection tries to use the socket file and any port specification gets ignored. If you use 127.0.0.1, the port specification will be used. Michael Jonas Ladenfors wrote: Hello, Is there someway to specify which domain one would like to connect from in the mysql console. When connecting on my server I always get connected through localhost. I would like to connect through my DNS name. quasi example : mysql -u [EMAIL PROTECTED] -p regards /Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: runaway process eating all cpu-time?
I converted the only innodb-table to myisam so the innodb-engine is no longer used. The problem has vanished since then. Pity that there seems to be no simple way to diagnose the problem in innodb. check table has showed no problems. Seems like I have to stay away from innodb for the time being :(. thomas Victor Pendleton wrote: Do you have anything in the show processlist? Are you using innodb table types and a rollback is occurring? -Original Message- From: Thomas - Lists To: [EMAIL PROTECTED] Sent: 8/24/04 6:49 AM Subject: runaway process eating all cpu-time? Hi there, after a system-crash I got the following problem. Some seconds after starting mysql (v4.0.20) a process will stay there taking all cpu-time it can get (99.9% ;)) ... and it can't be terminated, just killed completely. mysqlcheck ran through without any problems, the logs tell nothing. I just did a strace on the last process doing that: --- time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348119 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 time(NULL) = 1093348120 sched_yield() = 0 time(NULL) = 1093348120 rt_sigprocmask(SIG_SETMASK, NULL, [HUP INT QUIT PIPE TERM TSTP 32], 8) = 0 rt_sigsuspend([HUP INT QUIT PIPE TERM TSTP] unfinished ... --- SIGRT_0 (Real-time signal 0) --- --- Any idea where to look at that problem further? The system is a linux - redhat-7.2 with dual athlon-mp, 2 GB ram, running kernel-2.4.26. Using the official mysql.com-binaries (rpm-install). TIA, Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL with Intel Compiler
Hi! On Aug 24, Santhanam wrote: Dear Friends, We want to use MySQL compiled with Intel Compiler to get superior performance. Our server is a rack mounted HP DL 380 server with Redhat Enterprise Linux Advanced Server 3.0. We have used mysql-standard-4.1.3-beta-pc-linux-i686-icc.tar.gz. But we are getting the following error : --- [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql --force Installing all prepared tables Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! 040824 17:29:49 Aborting 040824 17:29:49 ./bin/mysqld: Shutdown complete http://bugs.mysql.com/bug.php?id=4408 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Secure Connection(e.g. SSL) Question
I need to connect to a remote MySQL database from a PC using SSL. I would prefer to connect using perl DBD. Does anyone have a suggestion how I can accomplish this task or an alternative solution? Thank You -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Row level locking with InnoDB
Hi, I have a quick question, which I did not find answerd by the manual: Given this sample query: SELECT COUNT(ID) FROM mytable WHERE property=value FOR UPDATE Which rows in 'mytable' are locked after this query? Every row with property=value, because these rows were used to calculate COUNT(ID). No rows, because no row is actually returned / selected. Thanks and greetings, Kai Ruhnau -- This signature is left as an exercise for the reader. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Overriding mysql connection variables
Does anyone know whether it is possible to override the MySQL connection variables used in a PHP script? I use Plesk and want it to use a database that is located on an external server, but I cannot edit the variables as Plesk is closed source? Joel Moss HomepageUniverse.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Debating over table types
I know the major differences between Innodb and MyIsam. -table locking versus row locks -foreign key support I know there are more but those are the ones that stick out in mind. I think Innodb requires a bit more storage space but I could be wrong. In some cases foreign key support would be a nice thing. So my questions are: What is the ultimate criteria for choosing Innodb. And, if I just want it because I like to have the luxary of foreign key / cascading etc, is that reason enough to use them. Stuart
Re: sql2000 to mysql
Victor Pendleton wrote: Are you wanting to move Foreign keys, Triggers, Stored procedures and the like as well or just the data? definitely!! so you make relations in your codes for projects... If not use Triggers, not use Stored procedures then, You move database, table and data with a small script.. -Original Message- From: Tim Winters To: [EMAIL PROTECTED] Sent: 8/24/04 10:36 AM Subject: sql2000 to mysql Hello, Can someone advise me of the best/easiest way to move an entire DB (Tables and data) from sql2000 (my client) to mySQL (my System)? I need to advise someone on how I wish the data sent to me. Any help would be appreciated. Thanks, Tim Best Regards. -- - Davut Topcan -- ~ ~ ~~ ~ - OTVT Solutions~ ~ - IT Solutions ~ ~ - Web Development ~ ~ - Software Development ~ ~~ ~ -- ~ ~ == JacK == ~ ~ | | ^Daniel^ ~ ~ _ | | ^^~ ~ | |_| | www.NoGate.org~ ~ \___ / www.DTClife.com ~ ~~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Debating over table types
InnoDb doesn't support full text indexes on text columns so if you need to search text fields you may want ot use MyISAM. It is perfectly ok to have a mixture of InnoDB and MyISAM in a single database so you can make your decision on a table by table basis. Joe Stuart Felenstein [EMAIL PROTECTED] wrote: I know the major differences between Innodb and MyIsam. -table locking versus row locks -foreign key support I know there are more but those are the ones that stick out in mind. I think Innodb requires a bit more storage space but I could be wrong. In some cases foreign key support would be a nice thing. So my questions are: What is the ultimate criteria for choosing Innodb. And, if I just want it because I like to have the luxary of foreign key / cascading etc, is that reason enough to use them. Stuart [EMAIL PROTECTED] http://www.joeaudette.com http://www.earworkout.com
RE: sql2000 to mysql
Hi Victor, Just Tables and Data. Ideally scripted to create the tables and insert the data. will sql2000 product something similer to a .sql file which can simple be run as a script? Thx At 12:45 PM 24/08/2004, Victor Pendleton wrote: Are you wanting to move Foreign keys, Triggers, Stored procedures and the like as well or just the data? -Original Message- From: Tim Winters To: [EMAIL PROTECTED] Sent: 8/24/04 10:36 AM Subject: sql2000 to mysql Hello, Can someone advise me of the best/easiest way to move an entire DB (Tables and data) from sql2000 (my client) to mySQL (my System)? I need to advise someone on how I wish the data sent to me. Any help would be appreciated. Thanks, Tim -- 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: MySQL Secure Connection(e.g. SSL) Question
I need to connect to a remote MySQL database from a PC using SSL. I would prefer to connect using perl DBD. Does anyone have a suggestion how I can accomplish this task or an alternative solution? What about stunnel or ssh tunnels and then use DBD::mysql on top. ISTR that DBD::mysql cannot use SSL'ed mysql client connections and your remote server would have to be complied to support it. Greg Thank You -- 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: Debating over table types
I know the major differences between Innodb and MyIsam. -table locking versus row locks -foreign key support I know there are more but those are the ones that stick out in mind. Well, how about transaction support? I think Innodb requires a bit more storage space but I could be wrong. In some cases foreign key support would be a nice thing. So my questions are: What is the ultimate criteria for choosing Innodb. And, if I just want it because I like to have the luxary of foreign key / cascading etc, is that reason enough to use them. Foreign keys luxury? Well, if this aint a read-only database, I wouldn't call that a luxury, but rather a requirement. 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: sql2000 to mysql
You can try SQLyog's ODBC Import feature. SQLyog can be found at http://www.webyog.com Regards Karam --- Tim Winters [EMAIL PROTECTED] wrote: Hi Victor, Just Tables and Data. Ideally scripted to create the tables and insert the data. will sql2000 product something similer to a .sql file which can simple be run as a script? Thx At 12:45 PM 24/08/2004, Victor Pendleton wrote: Are you wanting to move Foreign keys, Triggers, Stored procedures and the like as well or just the data? -Original Message- From: Tim Winters To: [EMAIL PROTECTED] Sent: 8/24/04 10:36 AM Subject: sql2000 to mysql Hello, Can someone advise me of the best/easiest way to move an entire DB (Tables and data) from sql2000 (my client) to mySQL (my System)? I need to advise someone on how I wish the data sent to me. Any help would be appreciated. Thanks, Tim -- 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] ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
All Queries Hanging/Freeze: Trying to perform Select 1
Hi, We have recently upgraded to MySQL 4.0 (binary) from 3.23.50, and we are seeing *all* MySQL queries freeze up every morning for about 25 seconds. I don't believe this is the standard run-of-the-mill MyISAM locking problem people tend to run into, as queries in separate databases hang, aswell as SELECT 1 queries hanging. Here's the situation, a few jobs run over night that do a humongous amount of inserts into a single table in two separate databases. (We'll call this 'table A', as it is named the same in both databases). This nightly job is run for only 2 databases out of maybe 100. We use batch inserts to insert these rows. When these jobs finish, all is calm (all the users are asleep). All the servers are pretty much entirely idle. There should be no system cronjobs running at this point. Then, sometime between 10 minutes and an hour later, all the queries going to the mysql server will freeze for ~25 seconds. During this time, vmstat 1 reports that one full cpu is cranking 100% user time. (this is a 4 way box, so %25 user time is reported). We have no cronjobs running at this time, and we have disabled nightly optimization since this problem started occuring. Last night, I setup a 'show processlist' python script, and I captured the results every 5 seconds. I have approximately 5 processlist captures from the server in this state. They look like this (this is from the first capture) (31531L, 'ABC', 'xxx:48319', 'ABC', 'Query', '1', None, 'select 1') (31532L, 'ABC', 'xxx:48320', 'ABC', 'Sleep', '21', '', None) (31533L, 'BCD', 'xxx:48322', 'BCD', 'Query', '0', None, 'select 1') (31567L, 'CDE', 'xxx:48489', 'CDE', 'Query', '0', None, 'select 1') (31610L, 'DEF', 'xxx:48622', 'DEF', 'Query', '0', None, 'select 1') (31611L, 'EFG', 'xxx:48626', 'EFG', 'Query', '1', None, 'select 1') (31666L, 'HIJ', 'yyy:54831', 'HIJ', 'Query', '0', None, 'select count(GlobalPreferences.primaryKey) from GlobalPreferences') (32016L, 'JKL', 'zzz:50352', 'JKL', 'Query', '2', 'update', 'INSERT INTO Response (responseSetID,questionID,userID,hasRule,isNA,creationDate,versionNumber,foilID') [...] There are approximately 600 db connections total, at at this point, only a small fraction are hanging. You will note there is exactly one insert being run at this time. I believe this is related to a trigger for this condition. This insert, however, is *NOT* into 'Table A'. The table this insert is going into should have seen a lot of select activity overnight, but no inserts. You can see the select 1's hanging, and a few select count's hanging. Both of those queries are diagnostic queries run by the application to make sure the database is healthy. The hang time in the above list is small, but if we look at my last capture: (31895L, 'ABC', 'yyy:55654', 'ABC', 'Query', '16', None, 'select 1') (31898L, 'BCD', 'yyy:55658', 'BCD', 'Query', '21', None, 'select 1') (31948L, 'CDE', 'yyy:56017', 'CDE', 'Query', '21', None, 'select 1') (32016L, 'JKL', 'zzz:50352', 'JKL', 'Query', '28', 'update', 'INSERT INTO Response (responseSetID,questionID,userID,hasRule,isNA,creationDate,versionNumber,foilID') (31793L, 'DEF', 'yyy:55176', 'DEF', 'Query', '25', None, 'select count(GlobalPreferences.primaryKey) from GlobalPreferences') (31794L, 'DEF', 'yyy:55177', 'DEF', 'Query', '15', None, 'select count(GlobalPreferences.primaryKey) from GlobalPreferences') (31795L, 'DEF', 'yyy:55178', 'DEF', 'Query', '4', None, 'select count(GlobalPreferences.primaryKey) from GlobalPreferences') The select 1's and counts range from hanging 4 seconds to 25 seconds, and the insert hangs for 28 seconds. Based on the increase in the number of hanging requests, I do not believe *any* requests have completed since that insert started (although my show processlist seems to go through!). The Mysql.err log is empty, the Mysql-slow.log shows all the queries above, including the insert. A few things to note. vmstat shows very little I/O during this period. It shows slightly less than normal I/O then a 'standard' idle state, presumably because none of the mysql queries go through during this period. I/O is not frozen however, as I do see disk writes during this period. Here's the server config: my.cnf: [mysqld] skip-locking server-id = 4 skip-innodb set-variable= key_buffer=800M set-variable= tmp_table_size=1024M set-variable= max_allowed_packet=16M set-variable= thread_stack=128K set-variable= max_connections=2000 set-variable= max_connect_errors=9 set-variable= table_cache=1024 set-variable= myisam_max_sort_file_size=4096 set-variable= myisam_sort_buffer_size=512M set-variable= join_buffer_size=512M set-variable= sort_buffer=512M query_cache_size= 512M The system has 4gigs of memory, recently reduced from 8 gigs to try to work around a separate problem, and we're running the 2.4.26 linux kernel on a quad cpu x86 machine. Any thoughts? What kind of internal
Re: Debating over table types
Right, I have set up some tables with text columns that will need full text searching, In those cases I chose myisam. Is there any danger in changing table types on the fly ? Not including the loss of any foreign keys that may have been set up (innodb to myisam). I'm wondering more about data corruption ? Stuart Joe Audette [EMAIL PROTECTED] wrote: InnoDb doesn't support full text indexes on text columns so if you need to search text fields you may want ot use MyISAM. It is perfectly ok to have a mixture of InnoDB and MyISAM in a single database so you can make your decision on a table by table basis. Joe Stuart Felenstein wrote: I know the major differences between Innodb and MyIsam. -table locking versus row locks -foreign key support I know there are more but those are the ones that stick out in mind. I think Innodb requires a bit more storage space but I could be wrong. In some cases foreign key support would be a nice thing. So my questions are: What is the ultimate criteria for choosing Innodb. And, if I just want it because I like to have the luxary of foreign key / cascading etc, is that reason enough to use them. Stuart [EMAIL PROTECTED] http://www.joeaudette.com http://www.earworkout.com
Re: All Queries Hanging/Freeze: Trying to perform Select 1
Any thoughts? What kind of internal locks might be generated? Is there another command I can run to get the status of those queries that are hanging? It shows 'None' for the state (NULL), I've never seen a Aha.. So I had a brainstorm while driving to lunch. Is it possible this is related to the query cache? Obviously we did not have a query cache before we upgraded to 4.0. Over night, the heavy jobs running inserts millions of records into Table A, but basically does hundreds of thousands of select's from Table B. Table B is the table we're seeing the insert into that appears to be freezing all the queries on the server (including 'select 1'). Presumably, if this is the very first insert into this table, the query cache must be flushed for that table. I am assuming the query cache is nearly full of queries against that table (as the system has been idle since this job finished). Our query cache is 512M max. Can it really take 25 seconds to clear the query cache of all those queries? Would that hang all the queries in the manner we are seeing (Including 'select 1')? The Insert would be in the 'update' state while clearing the query cache? I can update my script to also pull back 'show status' from the server tonight and see what the query cache is doing at the same time. If it does turn out to be a query cache lock, what course of action can we take? Periodic 'reset query cache', throughout the night? Is a query cache of 512M beyond the recommended size? I wouldn't have expected it to take 25 seconds to clear out a single table's query cache, is this possible? Thanks, -Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANNC: MySQL Administrator 1.0.10 released
MySQL Administrator 1.0.10 has been released and is available for download. This is the second generally available and stable release and contains bugs fixed since the previous public release. MySQL Administrator is a GUI management console for MySQL, with support for tasks such as managing users, configuring MySQL, performing backups, editing table definitions etc. More information at: http://www.mysql.com/products/administrator/ You can download sources and binaries for Windows and Linux from: http://dev.mysql.com/downloads/administrator/ -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina 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: Debating over table types
In the last episode (Aug 24), Stuart Felenstein said: Right, I have set up some tables with text columns that will need full text searching, In those cases I chose myisam. Is there any danger in changing table types on the fly ? Not including the loss of any foreign keys that may have been set up (innodb to myisam). I'm wondering more about data corruption ? During testing I've converted tables from MyISAM to Innodb and back with no problems. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sql2000 to mysql
If you have DTS you can export the data to a CSV format. If you have MyODBC installed you could export directly to MySQL. -Original Message- From: Tim Winters To: Victor Pendleton; '[EMAIL PROTECTED] ' Sent: 8/24/04 11:34 AM Subject: RE: sql2000 to mysql Hi Victor, Just Tables and Data. Ideally scripted to create the tables and insert the data. will sql2000 product something similer to a .sql file which can simple be run as a script? Thx At 12:45 PM 24/08/2004, Victor Pendleton wrote: Are you wanting to move Foreign keys, Triggers, Stored procedures and the like as well or just the data? -Original Message- From: Tim Winters To: [EMAIL PROTECTED] Sent: 8/24/04 10:36 AM Subject: sql2000 to mysql Hello, Can someone advise me of the best/easiest way to move an entire DB (Tables and data) from sql2000 (my client) to mySQL (my System)? I need to advise someone on how I wish the data sent to me. Any help would be appreciated. Thanks, Tim -- 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]
FW: Automatic Failover
I have checked the TODO list and cannot find any mention of an automatic failover feature. In Jeremy's High Performance MySQL book, he makes mention of it being some sort of a plan for the future. Also, in a section of the manual centered on replication, there is also mention of it. I also found two threads from the past year that mentions it briefly. My question is this: Does any one have any more info on what the plan is? Thanks, Chris ** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select ENUM values
I'm wondering if there is a better way to select the values of a ENUM field. I have a ENUM field called greeting that has 'Mr.','Mrs.','Ms.','Dr.' in it. I need to put these in a HTML select. Right now I'm doing a SHOW COLUMNS FROM global_lead LIKE 'greeting' and then parsing out the response for the enum values (found on mysql site). But I was thinking there might be a more elegant way to do it. Is there? Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
calculating ratio of two datetime columns
I am selecting two columns each of type DateTime from a mysql table from
perl.
my $sth = $dbh-prepare(SELECT End_Date_Scheduled, End_Date_Actual FROM
Table1 WHERE ID='10' );
$sth-execute();
while (my $ref = $sth-fetchrow_hashref()) {
printFound: end date scheduled = $ref-{'End_Date_Scheduled'}, end
date actual = $ref-{'End_Date_Actual'}\n;
}
$sth-finish();
It works.
However, I need to find the % ratio = ((end_date_scheduled -
end_date_actual)/end_date_scheduled) * 100
To do this I need the DateTime values in seconds. Can someone help me
with this?
Best Regards
Vishwa Rao
Re: Select ENUM values
At 11:51 -0700 8/24/04, Michael Pawlowsky wrote: I'm wondering if there is a better way to select the values of a ENUM field. I have a ENUM field called greeting that has 'Mr.','Mrs.','Ms.','Dr.' in it. I need to put these in a HTML select. Right now I'm doing a SHOW COLUMNS FROM global_lead LIKE 'greeting' and then parsing out the response for the enum values (found on mysql site). But I was thinking there might be a more elegant way to do it. Is there? No. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Add new database into existing MYSQL database
Hi, all: I use root login to create a new databse in the existing server. Then I use the existing user name in the mysql user table as user name, add the database and username .. info into mysql db table. Then mysqladmin to load the table. When I login the database using the existing user name, I only can see previously existing database without seeing the new added database. What is the problem ? Thanks a lot. Yong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext performance problem.
How fast is this query?
SELECT id
FROM msg_body
WHERE MATCH(body) AGAINST( 'WORD')
If it's fast , you may have to re-state your query so that you are not FT
searching and joining tables in the same statement. The optimizer can only
use 1 index at a time from any table. We may be running into a conflict
about which index to use.
You might break up your query like this in order to get better speed:
CREATE TEMPORARY TABLE tmpBody
SELECT id
FROM msg_body
WHERE MATCH(body) AGAINST( 'WORD')
CREATE TEMPORARY TABLE tmpMsg
SELECT ID
FROM msg_header
WHERE MATCH (list) against ('listname')
ALTER TABLE tmpBody ADD KEY(id)
ALTER TABLE tmpMsg ADD KEY(id)
SELECT
h.bodyid
, h.id
, h.subject
, h.mfrom
, h.date
, msg_header.list
FROM tmpMsg tm
INNER JOIN msg_header h
ON h.id = th.id
INNER JOIN tmpBody tb
on h.bodyid = tb.id
The two FT searches should happen quickly ( 10 seconds each), Adding
indices to both temp tables could take up to 2 seconds each. The final
query is fully indexed so it should return 2 seconds. This gives us a
worst-case scenario of 26 seconds. However, I would guess that you get
sub-tens during actual testing. Since I opted to use temporary tables they
will be unique per connection so you will not need to worry about name
collisions during concurrent executions. I would still DROP TABLE tmpMsg,
tmpBody before closing the connection just to make sure those resources
are released as soon as possible.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Fredrik Carlsson [EMAIL PROTECTED] wrote on 08/20/2004 01:13:40
PM:
Hi all,
I'm running a small mail archive and have a little problem with the
fulltext search performance.
I really appreciate any tips/design suggestions (even if it dont have to
do with the search problem ;) ).
Database schema:
mysql describe msg_header;
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| id | int(11) | | PRI | NULL| auto_increment |
| parent | int(11) | YES | MUL | NULL||
| bodyid | int(11) | YES | | NULL||
| list| varchar(80) | YES | MUL | NULL||
| mfrom | varchar(80) | YES | | NULL||
| mto | varchar(80) | YES | | NULL||
| subject | varchar(200) | YES | MUL | NULL||
| mcc | varchar(80) | YES | | NULL||
| sdate | varchar(45) | YES | | NULL||
| batch | varchar(80) | YES | MUL | NULL||
| msgid | varchar(90) | YES | | NULL||
| date| datetime | YES | MUL | NULL||
+-+--+--+-+-++
12 rows in set (0.00 sec)
mysql describe msg_body;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-++
| id| int(11) | | PRI | NULL| auto_increment |
| body | text| YES | MUL | NULL||
+---+-+--+-+-++
2 rows in set (0.00 sec)
index from msg_body;
*** 1. row ***
Table: msg_body
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*** 2. row ***
Table: msg_body
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*** 3. row ***
Table: msg_body
Non_unique: 1
Key_name: body
Seq_in_index: 1
Column_name: body
Collation: A
Cardinality: 295996
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
3 rows in set (0.00 sec)
The search querys using fulltext indexes takes around 1minute and no
one want to use a search that slow :/
The Query is the following:
SELECT msg_header.bodyid,msg_header.id,
msg_header.subject,msg_header.mfrom, msg_header.date, msg_header.list
FROM
msg_header,msg_body WHERE msg_header.bodyid=msg_body.id AND
match(msg_header.list) against('LISTNAME')
AND match(msg_body.body) AGAINST('WORD');
For a couple of month ago the msg-body and msg-headers parts where in
the same table and the fulltext search was really fast 1 sec, but
everything else just became slower so i splitted it upp in two tables.
But now i need to match msg_header.bodyid against msg_body.id to be able
to now witch
Re: rows to columns - not crosstab
I still see what you want as a crosstab query. The only difference, as you say very well, is that you want to pivot on the date type values and not the ID values. The only other thing you need to decide in order to make a crosstab report is what information goes in the position for the row ID=x and column datetype=y. Do you want to see the SUM of some value for each of the rows with that ID value and Date type? Or the average or maximum or minimum or the standard deviation for the set. I am asking you how do you want to calculate each of the values of x, y, z, and w as you listed them in your example output? It's the same pattern as the other crosstab queries (aka pivot tables) but you have to tell me which column you want to calculate values from and which calculation to use before I can give you an example using your data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Donna Hinshaw [EMAIL PROTECTED] wrote on 08/20/2004 03:13:16 PM: Hi - I've read the threads about converting rows of data into columns, but those threads assume the number of distinct rows is very limited (say 7 for days of week). Instead, I have a table like this: iddate date type 1 ... a 1 ... b 2 a 2 d Where the number of distinct id values is in the 100,000 range but the distinct date types are limited to about 20. I want to get a table (not a view) like this: iddate a date b date d .. 1 x y null 2 z null w (based on the values in the first table above) So...I can't think how to do this. Help would be appreciated. TIA. dmh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Figuring out the ranking position of an item on a table given its partnumber
C.F. Scheidecker Antunes [EMAIL PROTECTED] wrote: I have a table that has a PartNumber and the Quantity of Items sold for each partNumber, that is: PartNumber Qty I need to get its sales raking given its PartNumber, that is. So if I order the table by Qyt in descending order the first record will be the partNumber that sold the most. If I want to know what is 123 raking position according to that. Is there any easy way to do it? I think you may be looking for something like this: set @a = 0; select @a := @a+1 as rank, PartNumber from my_table order by Qty desc; Unfortunately, selecting only the record with rank 123 doesn't work very well with this query, so you may want to load a temporary table and then select from that table: set @a = 0; create temporary table foo select @a := @a+1 as rank, PartNumber from my_table order by Qty desc; select * from foo where rank = 123; (I haven't addressed the question of how you deal with records where the quantity is the same. Left as an exercise for the reader. :-) - seb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL with Intel Compiler
We've seen this problem too. In our case we definitely had a mysql user, and the mysql user definitely existed. From one prompt we could start the gcc-compiled MySQL correctly, but not the Intel-compiled MYSQL. We were able to fix the problem by setting LD_LIBRARY_PATH=/lib. -JF -Original Message- From: Steve Poirier [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 24, 2004 8:03 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: MySQL with Intel Compiler Sorry, didn't see you already had a mysql user. David has a better english than mine: It looks like your MySQL server process is hitting a problem when it tries to change its user id to that of the user 'mysql'. There are two possible reasons for this: 1. You're starting the server process as a user who isn't 'root', so the user doesn't have the necessary privilege to change the UID of the server process. -- OR -- 2. You *are* starting the server process as 'root', but your system doesn't have a user called 'mysql', so the process is trying, and failing, change its UID to a user that doesn't exist. Try finger mysql to see whether you actually have a user named 'mysql'. Thinking about it, there are other possibilities, assuming that you *do* have a user named 'mysql'. 3. The 'mysql' user doesn't have a home directory, or for some reason that user doesn't have write-access to its home directory. 4. The 'mysql' user doesn't have the necessary access privileges to /path/to/mysql and all of its sub-directories. source http://www.talkaboutprogramming.com/group/comp.lang.java.datab ases/messages/ 57463.html _ Steve Poirier -Original Message- From: Santhanam [mailto:[EMAIL PROTECTED] Sent: August 24, 2004 8:02 AM To: [EMAIL PROTECTED] Subject: MySQL with Intel Compiler Dear Friends, We want to use MySQL compiled with Intel Compiler to get superior performance. Our server is a rack mounted HP DL 380 server with Redhat Enterprise Linux Advanced Server 3.0. We have used mysql-standard-4.1.3-beta-pc-linux-i686-icc.tar.gz. But we are getting the following error : --- [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql --force Installing all prepared tables Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! 040824 17:29:49 Aborting 040824 17:29:49 ./bin/mysqld: Shutdown complete --- For more details(Full text of Error Message), please see the attachment. But mysql user group is existing. Please kindly help me. Thanks in Advance With Regards Santhanam -- 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 binary log files location
I found the solution to my own problem... the log-bin option is a specific filename. so when you set: log-bin=/logging mysql must have ownership of the / folder, as it is trying to create /logging.001 and /logging.index easily fixed by adding the file-name to the path... log-bin=/logging/host-name which will now create /logging/host-name-bin.001 and /logging/host-name-bin.index and since mysql has ownership of the logging directory, this is okay... -L Luke Crouch 918-461-5326 [EMAIL PROTECTED]
One form multiple inserts
Trying to figure out how this gets done. Let me explain first. By the way I hope this is not off topic as perhaps it is more towards application then core db . I have a table: Count[int][auto-increment], MemberID[int], Title[varchar], TitleYear[int] In my organization members can have or have held various titles. I'm trying to collect, right now, 5 titles and the years they've held those titles. Extraneous - Now I'm in a precarious place, cause I am new to web dev and db and have had the fortunate experience to be using an extension to dreamweaver that makes it relatively simple. Until you have to go beyond what they provide in functionality / capability. It's php / adodb. So Ive looked around and found a few methods but no detail, nor have I really drilled down on how they get implemented. One method was basically a loop of sorts. Not entirely sure but I suppose pages can keep refreshing 5 times. Second, was to pass all the variables over to another page. It sounded like maybe this secondary page is hidden but set up to accept an insert. The third, which I tried, unsuccessfully, was through the use of after triggers. Possible I need to work on this more. Anyway I wouldn't mind hearing how other people deal with this issue. Not sure if I could set up some SQL statements. Thank you , Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Failed to start in Fedora Core 2
Anyone else getting a MYSQL [FAILED] when it starts up with Fedora Core 2. The funny part is that it is started and accepting connections. It's just the message comming back as failed either at boot up or when doing a /etc/init.d/mysqld start Regards, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reports slows down network
Jon Miller wrote: We have MySQL and MySQL-MAX on a RH8 release. The server specs is as follows: Dual Xeon CPU 2.8GHz 2G DDR Memory 215GB HDD storage SCSI U160 HDD When a report (large one) runs I've noticed the following through tops. mysqld-max = 97.2 - 99.9 %CPU with 1.2% memory usage In the top of tops I've noticed that all 4 CPU (CPU0-CPU3) never gets over 50% for more than 2-3 seconds. Can someone tell me why the reports makes the server run slower than expected and how can I set mysqld-max to make use of each CPU thus keep the CPU usage down. Is there a command I can run in mysql that can give me an accurate reading of how much cpu and memory is being used? Thanks I'm pretty sure that each individual query can only run on 1 CPU at once. If you have more than 1 query, each one *can* get allocated to a different CPU, but you're not going to get all CPUs running one thread ( query ). As for why the query takes so long ... why is MySQL only using 1.2% of your memory? Have a look at: http://dev.mysql.com/doc/mysql/en/Server_parameters.html If MySQL isn't using enough memory it will have to use the disk(s) a lot, which will slow things down a lot. Without seeing any of your config files or queries, there's not much more to be said. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.3beta hangs after several consective drop table
I don't know if it is a bug, but I repeated did:
DROP TABLE IF NOT EXISTS company;
Create table company {
...
...
};
create name_idx on company (name(32));
The server hangs at create name_idxthe first couple of them were
really quick.
Any idea?
Thanks a lot!
Haitao
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about innodb_thread_concurrency
Hi, This is a question about optimization of mysql's InnoDB performance ... quoting the doc's The default value is 8. If you have low performance and |SHOW INNODB STATUS| reveals many threads waiting for semaphores, you may have thread thrashing and should try setting this parameter lower or higher. The default value is 8. If you have low performance and |SHOW INNODB STATUS| reveals many threads waiting for semaphores, you may have thread thrashing and should try setting this parameter lower or higher. What is a reasonable value for a dual Xeon 2.8Ghz with 6 disks? we are currently running with innodb_thread_concurrency=16. Each DB has 200-400 active php clients, and there appears to be quite a large backup within innodb, based on this. Any suggestions? Or experience with setting this option to 500? Thanks :) Devananda Neopets, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can append word into varchar column in Update statement?
Hi all, I though it is impossible to do that but I like check with you guys in case it is just because I never heard it. I have a table with a text column. Is there any way I can append some word into this field when I update the table? For instance, Table temp, column ReviewComments, data type of ReviewComments is varchar. Value in ReviewComments is PSRC. After update, value in ReviewComments should be PSRC, WHC Can I do that by using UPDATE SET . Thanks a lot. Monet ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AVG Function
Hey Everyone, I can you some assistance on this query. I have three tables one is called ranking and the other is called routes and finally the users table. The users table is not really important. The ranking table looks like this: id rating 1 5.0 2 5.1 3 5.2 4 5.3 5 5.3a 6 5.3b and so on... The routes table looks like this: user_id route rating 1 somename5.2 1 5.3 1 5.3a Here's my query: SELECT ranking.rating, AVG(id), users.username, users.user_id, routes.rating, routes.user_id FROM ranking, routes, users WHERE username='$username' AND users.user_id = routes.user_id AND ranking.rating = routes.rating GROUP BY username What I am trying to do is find the average rating for this user. For example: 5.2 = 3 5.3 = 4 5.3a = 5 ___ 3 + 4 + 5 = 12 / 3 = 4 So 4 = 5.3 The average for this user would be 5.3. Any help would be most appreciated. Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
