Product Table Design Question
Hi All, I'm not sure the best design approach for a product table for a number of different hardware devices. Some devices have IP, Port, CPU, Memory specs, whilst some don't. Current desing is below. Products: product_id product_name maker_id controller_id product_type_id product_model_number product_serial_number product_age condition_id product_price product_sold_price product_sold product_auctioned product_qty product_last_updated product_data_output product_desc ProductTypes: product_type_id product_type_detail Makers: maker_id maker_detail Some products we'll have are, switches/routers/dedicated servers/firewalls etc. Should I just make a Specs table, or, specific table for each type of product.? Appreciate any thoughts on this. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems importing dump 4.0 4.1
Hi, Thanks for your reply. What do you mean by right character set ? The default character set for ver. 4.1 is latin1, right ? This set should support all western european languages ! Regarding --default-character-set command in chapter 10 is written that a way to change settings is to recompile... Is there another less painful way to do this ? Thanks in advance Roberto Jobet Hello. Specify the right character set with --default-character-set command line option for mysql (if you're using it for reading dump). Chapter 10 of the manual could be helpful. See: http://dev.mysql.com/doc/mysql/en/charset.html [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I'm trying to import a db dump made on ver. 4.0, on a new 4.1 vers= ion. All accented characters (french, spanish) are replaced by a '?'=0D = What it's the right way to import it ? Thanks in advance Robert= o Jobet =0A=0A=0A=0A= =0ANavighi a 4 MEGA e i primi 3 mesi sono GRATIS. =0AScegli Liber= o Adsl Flat senza limiti su http://www.libero.it=0A -- 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] 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero! Scaricalo su INTERNET GRATIS 6X http://www.libero.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT problem and QUESTION OF SPEED
Reni Fournier wrote: Thanks for the solution. It looks like it would work, but I don't have MySQL 4.1 (which I believe is required for this to work, since this is SUBSELECT, isn't it?). Assuming I have to use two selects, which would you say is faster, creating a temporary table in MySQL, or extracting the data back into PHP, and recreating a SELECT from there? by using in memory temporary table, you'll avoid the round trip between, the PHP server and the SQL server, which would be a bit faster. ..Rene On 2-Jun-05, at 2:28 PM, [EMAIL PROTECTED] wrote: Hi Reni, thsi can be a solution, many others are possible : mysql select distinct the_date, person_id, cost, name - from trips,persons - where person_id=persons.id - and the_date in(select max(the_date) from trips a - where a.person_id=person_id - group by person_id) - ; ++---+--+--+ | the_date | person_id | cost | name | ++---+--+--+ | 2005-02-08 | 1 | 580 | john | | 2005-01-25 | 2 | 200 | jane | | 2005-02-03 | 3 | 600 | mike | | 2005-02-20 | 4 | 320 | mary | ++---+--+--+ 4 rows in set (0.00 sec) Mathias Selon Reni Fournier [EMAIL PROTECTED]: I'm having a really hard time selecting rows from a table in one SELECT statement. I can do it in two SELECTS, but it seems I should be able to do it in one. TRIPS iddateperson_idcost --- 12005-01-012500 22005-01-051400 32005-01-124350 42005-01-153175 52005-01-172385 62005-01-252200 72005-02-033600 82005-02-081580 92005-02-204320 PERSONS idname - 1john 2jane 3mike 4mary 5henry Okay, I want to select from Trips the most recent trip for each person. As you can see, some of the Persons have travelled more than once, but I only want the last trip each one made. Also, not everyone in Persons has made a trip (Henry). Here's the output I'm looking for: 2005-02-204320mary 2005-02-081580john 2005-02-033600mike 2005-01-252200jane I've written and re-written my SELECT queries numerous times, but can't seem to retrieve just one, most-recent trip/person. Any ideas? ...Rene -- 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] -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PARTIALLY SOLVED] Illegal mix of collations for operation IN
I did more investigation and found out that 1) with MySQL 4.1.10-nt both queries executed from Query Browser work fine (no error) - I haven't chance to test it with my application through ODBC connection 2) when I set initial statement in MyODBC SET CHARACTER SET 'cp1250' instead of SET NAMES 'cp1250' for connection to MySQL 4.1.9-nt-log queries from my application work fine and that is what I need mysql show create table files \G *** 1. row *** Table: files Create Table: CREATE TABLE `files` ( `FileName` char(100) collate latin2_czech_cs NOT NULL default '', `CommandType` enum('NONE','ON_SUCCESS','ON_NOTRANSMIT') collate latin2_czech_cs NOT NULL default 'NONE', `Command` char(128) collate latin2_czech_cs default NULL, PRIMARY KEY (`FileName`,`CommandType`) ) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs 1 row in set (0.01 sec) mysql show variables like '%char%'; +--+-+ | Variable_name| Value | +--+-+ | character_set_client | cp1250 | | character_set_connection | latin2 | | character_set_database | latin2 | | character_set_results| cp1250 | | character_set_server | latin2 | | character_set_system | utf8 | | character_sets_dir | D:\Program Files\MySQL\MySQL Server 4.1\share\charsets/ | +--+-+ 7 rows in set (0.00 sec) mysql show variables like '%col%'; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | cp1250_general_ci | | collation_database | latin2_czech_cs | | collation_server | latin2_general_ci | | protocol_version | 10| +--+---+ 4 rows in set (0.00 sec) I am still wondering why SELECT * FROM files WHERE filename = 'file1.txt' OR filename = 'file2.txt' was working and SELECT * FROM files WHERE filename IN('file1.txt', 'file2.txt') wasn't Dusan - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 02, 2005 2:13 PM Subject: Re: Illegal mix of collations for operation IN Hello. Usually debugging of such kind of problems starts with examination of the output of: show variables like '%char%'; show variables like '%colla%'; Send the output of: show create table your_table; Hello, I have problem which I don't understand. if I send command (from Query Browser or from C++ Builder application) SELECT FileName FROM Files WHERE FileName = 'FILE1.TXT' OR FileName = 'FILE2.TXT' query executes without any problem but command SELECT FileName FROM Files WHERE FileName IN ('FILE1.TXT', 'FILE2.TXT') throws error illegal mix of collations for operation 'IN' I thought that IN is somehow by optimizer translated to ORs Could someone explain me why first query is OK and second not? Please. I'm using WinXP SP2 and MySQL 4.1.9-nt-log databases and tables use CHARSET=latin2 COLLATE=latin2_czech_cs I already read some articles about the topic in MySQL forum but they didn't helped me much. Thanks in advance Dusan Pavlica Du$an Pavlica [EMAIL PROTECTED] wrote: -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to write SQL statement to do such query?
Hi! dear all, (BI encountered a problem with designing SQL statement. For example, I (Bwant to query population information from a "city" table, and the (Brecords returned must be ordered according to the population of the (Bcity. Like this: (B+---+-+-+ (B|capital | country | Population | (B+---+-++ (B| Seoul| South Korea | 9981619 | (B|Jakarta | Indonesia | 9604900 | (B|Ciudad de M$BqY(Bico | Mexico | 8591309 | (B|Moscow | Russian Federation| 8389200 | (B|Tokyo| Japan| 7980230 | (B|Peking | China| 7472000 | (B|London | United Kingdom | 7285000 | (B|Cairo | Egypt| 6789479 | (B (BThis is a very simple SQL statement, but I want to add sequence number (Bto each city, for example Seoul is number 1, Jakarta is number 2 and (Bso on. Namely like this: (B+---+---+-+-+ (B| |capital | country | (BPopulation | (B+---+---+-++ (B| 1 | Seoul| South Korea | 9981619 | (B| 2 |Jakarta | Indonesia | 9604900 | (B| 3 |Ciudad de M$BqY(Bico | Mexico | 8591309 | (B| 4 |Moscow | Russian Federation| 8389200 | (B| 5 |Tokyo| Japan| 7980230 | (B| 6 |Peking | China| 7472000 | (B| 7 |London | United Kingdom | 7285000 | (B| 8 |Cairo | Egypt| 6789479 | (B (BThen, how do I get such a result? (B (BBest wishes (BHongwei Liu (B2005/06/03 (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recovery question
All, I've got a table with about 25mill rows that was victim of a crash recently. (power-failure). I've been trying to recover it, but I'm not making much progress. From the most recent attempts: myisamchk --safe-recover --force table - recovering (with keycache) MyISAM-table 'table' Data records: 21622679 4988000 7256000 10627000 myisamchk: error: 126 for record at pos 1589881104 MyISAM-table 'table' is not fixed because of errors myisamchk -r --force --tmpdir=/data2/tmp table - recovering (with sort) MyISAM-table 'table' Data records: 12876899 - Fixing index 1 - Fixing index 2 - Fixing index 3 Key 3 - Found wrong stored record at 0 MyISAM-table 'table' is not fixed because of errors Where do I go from here? I've got a backup of the table, but I'm not sure what sort of state it is in. -- /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Charsets in console
Hi there, Question: I have a database with names that may contain accented characters like é or è. My charset collation are the defaults latin1 latin1_swedish_ci. In my browser all characters come out all right (both in phpMyAdmin and in my application), but when I open a command console (mysql.exe) all characters look messed up.Manually typing accented characters works, but they do not match the ones in the database. E.g. The name Céline is in the database, I see Céline in the browser, but I see C8line in the console. Manually requesting the record of Céline (select * from individuals where name='céline') fails. The funny thing is that the C8line record *is* found when I type the request without accents (select * from individuals where name='celine')? Does anybody know how to make the console show and accept the accented characters? Thanks Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Out of memory, mysqld got signal 6;
Hi all, Over the last couple of days, a MySQL 4.0.21 server of ours has crashed a couple of times, with the error message listed below. It has previously been running without problems since being installed (about 150 days so far). I've not had much luck in tracking down what signal 6 means - the references I can find on the 'net seem to be from people running MySQL on a *BSD machine, while ours is on a RHEL3 server. Can anyone please point me in the right direction to track down what this might be? The server has 10GB of RAM, and MySQL has been compiled with -fomit-frame-pointer. I can re-compile MySQL without this option if it's absolutely necessary. Thanks, 050602 17:18:47 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388608 read_buffer_size=2093056 max_used_connections=235 max_connections=800 threads_connected=190 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3281785 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0xc271010 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... frame pointer (ebp) is NULL, did you compile with -fomit-frame-pointer? Aborting backtrace! Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x1a70a218 is invalid pointer thd-thread_id=1642298 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Fatal signal 11 while backtracing -- Andrew Hill Software Developer m3 Media Services Limited Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7371 IRC: #max on freenode.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recovery question
Per Jessen wrote: I've got a backup of the table, but I'm not sure what sort of state it is in. Correction - no backup is available. This table has got to be recoverable. -- /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Product Table Design Question
Mark Sargent [EMAIL PROTECTED] wrote on 06/03/2005 03:04:23 AM: Hi All, I'm not sure the best design approach for a product table for a number of different hardware devices. Some devices have IP, Port, CPU, Memory specs, whilst some don't. Current desing is below. Products: product_id product_name maker_id controller_id product_type_id product_model_number product_serial_number product_age condition_id product_price product_sold_price product_sold product_auctioned product_qty product_last_updated product_data_output product_desc ProductTypes: product_type_id product_type_detail Makers: maker_id maker_detail Some products we'll have are, switches/routers/dedicated servers/firewalls etc. Should I just make a Specs table, or, specific table for each type of product.? Appreciate any thoughts on this. Cheers. Mark Sargent. In my opinion, you have identified the core descriptors for any product already (your Products table). Now what you need to be able to provide is a list of descriptors and their values: (# of ports, 16), (# of cpus,2), (# expansion bays,6), (expansion option 1, TELCO-card), etc That kind of flexible design (where you are not limited to the number of extra items you can tack onto a Product) fits best into it's own, vertically organized table (ProductFeatures, below) CREATE TABLE Features ( feature_id int auto_increment primary key , feature varchar(75) not null , UNIQUE (feature) ) ENGINE=InnoDB; CREATE TABLE ProductFeatures ( prodfeat_id int auto_increment primary key , product_id int not null , feature_id int not null , value varchar(255) , UNIQUE(product_id, feature_id) , KEY(feature_id) , FOREIGN KEY (product_ID) References Products(product_id) , FOREIGN KEY (feature_ID) References Features(feature_id) ) ENGINE=InnoDB; Just to keep your data (and your GUI design) more manageable, I normalized the list of possible features into their own table. That way you don't have someone typing in # of ports for one product and port count for another. The UNIQUE index on the ProductFeatures should help to prevent having more than 1 of the same feature described for the same product. The FK constraints prevents you from adding non-existent features to a product that may or may not exist. Just my 2 cents... Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Charsets in console
Hello. Most probably you should set the correct character sets. Search on the list similar problems are often met there. See: http://dev.mysql.com/doc/mysql/en/charset-defaults.html What output does the following statement produce: show variables like '%char%'; Send the output of: show create table your_table; Carl Seghers [EMAIL PROTECTED] wrote: Hi there, Question: I have a database with names that may contain accented characters like $ or $. My charset collation are the defaults latin1 latin1_swedish_ci. In my browser all characters come out all right (both in phpMyAdmin and in my application), but when I open a command console (mysql.exe) all characters look messed up.Manually typing accented characters works, but they do not match the ones in the database. E.g. The name C$line is in the database, I see C$line in the browser, but I see C8line in the console. Manually requesting the record of C$line (select * from individuals where name='c$line') fails. The funny thing is that the C8line record *is* found when I type the request without accents (select * from individuals where name='celine')? Does anybody know how to make the console show and accept the accented characters? Thanks Carl -- 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: Out of memory, mysqld got signal 6;
Hello. Switch to the latest release 4.1.12 (or 4.0.24). It is strongly recommended to use official binaries. Andrew Hill [EMAIL PROTECTED] wrote: Hi all, Over the last couple of days, a MySQL 4.0.21 server of ours has crashed a couple of times, with the error message listed below. It has previously been running without problems since being installed (about 150 days so far). I've not had much luck in tracking down what signal 6 means - the references I can find on the 'net seem to be from people running MySQL on a *BSD machine, while ours is on a RHEL3 server. Can anyone please point me in the right direction to track down what this might be? The server has 10GB of RAM, and MySQL has been compiled with -fomit-frame-pointer. I can re-compile MySQL without this option if it's absolutely necessary. Thanks, 050602 17:18:47 Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=3D8388608 read_buffer_size=3D2093056 max_used_connections=3D235 max_connections=3D800 threads_connected=3D190 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =3D 3281785 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=3D0xc271010 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... frame pointer (ebp) is NULL, did you compile with -fomit-frame-pointer? Aborting backtrace! Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x1a70a218 is invalid pointer thd-thread_id=3D1642298 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Fatal signal 11 while backtracing -- Andrew Hill Software Developer m3 Media Services Limited Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7371 IRC: #max on freenode.net=20 -- 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: recovery question
Hello. REPAIR TABLE ... USE_FRM helps in difficult cases. See: http://dev.mysql.com/doc/mysql/en/repair-table.html Per Jessen [EMAIL PROTECTED] wrote: All, I've got a table with about 25mill rows that was victim of a crash recently. (power-failure). I've been trying to recover it, but I'm not making much progress. From the most recent attempts: myisamchk --safe-recover --force table - recovering (with keycache) MyISAM-table 'table' Data records: 21622679 4988000 7256000 10627000 myisamchk: error: 126 for record at pos 1589881104 MyISAM-table 'table' is not fixed because of errors myisamchk -r --force --tmpdir=/data2/tmp table - recovering (with sort) MyISAM-table 'table' Data records: 12876899 - Fixing index 1 - Fixing index 2 - Fixing index 3 Key 3 - Found wrong stored record at 0 MyISAM-table 'table' is not fixed because of errors Where do I go from here? I've got a backup of the table, but I'm not sure what sort of state it is in. -- 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: Problems importing dump 4.0 4.1
Hello. Regarding --default-ch aracter-set command in chapter 10 is written that a way to change settings is to recompile... Is there another less painful way to do this ? You may specify default character sets using options when you start mysqld. See: http://dev.mysql.com/doc/mysql/en/charset-server.html Check the character sets with the following statement: show variables like '%char%'; What version of mysqldump did you use to perform a dump? Versions from 4.1.x distributions usually put SET NAMES='utf8' in the beginning of the dump file. [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, Thanks for your reply. What do you mean by right character set= ? The default character set for ver. 4.1 is latin1, right ? This set s= hould support all western european languages ! Regarding --default-ch= aracter-set command in chapter 10 is written that a way to change settin= gs is to recompile... Is there another less painful way to do this ? =0D = Thanks in advance Roberto Jobet Hello. Specify the right= character set with --default-character-set command line option for my= sql (if you're using it for reading dump). Chapter 10 of the manual co= uld be helpful. See: http://dev.mysql.com/doc/mysql/en/charset.html=0D = [EMAIL PROTECTED] [EMAIL PROTECTED] wro= te: Hi, I'm trying to import a db dump made on ver. 4.0, = on a new 4.1 vers=3D ion. All accented characters (french= , spanish) are replaced by a '?'=3D0D =3D What it's the r= ight way to import it ? Thanks in advance Rob= ert=3D o Jobet =3D0A=3D0A=3D0A=3D0A= =3D =3D0ANavighi a 4 MEGA e i primi = 3 mesi sono GRATIS. =3D0AScegli Liber=3D o Adsl Flat senza limiti su= http://www.libero.it=3D0A -- 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: Out of memory, mysqld got signal 6;
-Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 03 June 2005 12:29 To: mysql@lists.mysql.com Subject: Re: Out of memory, mysqld got signal 6; Switch to the latest release 4.1.12 (or 4.0.24). It is strongly recommended to use official binaries. Sure, can do - but is this a known issue with 4.0.21? I wasn't able to find anything in the bug tracker... TIA, -- Andrew Hill Software Developer m3 Media Services Limited Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7371 IRC: #max on freenode.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write SQL statement to do such query?
SQL My wrote: (B (B Hi! dear all, (B I encountered a problem with designing SQL statement. For example, I (B want to query population information from a "city" table, and the (B records returned must be ordered according to the population of the (B city. Like this: (B +---+-+-+ (B |capital | country | Population | (B +---+-++ (B | Seoul| South Korea | 9981619 | (B |Jakarta | Indonesia | 9604900 | (B |Ciudad de M$BqY(Bico | Mexico | 8591309 | (B |Moscow | Russian Federation| 8389200 | (B |Tokyo| Japan| 7980230 | (B |Peking | China| 7472000 | (B |London | United Kingdom | 7285000 | (B |Cairo | Egypt| 6789479 | (B (B This is a very simple SQL statement, but I want to add sequence number (B to each city, for example Seoul is number 1, Jakarta is number 2 and (B so on. Namely like this: (B +---+---+-+-+ (B | |capital | country | (B Population | (B +---+---+-++ (B | 1 | Seoul| South Korea | 9981619 | (B | 2 |Jakarta | Indonesia | 9604900 | (B | 3 |Ciudad de M$BqY(Bico | Mexico | 8591309 | (B | 4 |Moscow | Russian Federation| 8389200 | (B | 5 |Tokyo| Japan| 7980230 | (B | 6 |Peking | China| 7472000 | (B | 7 |London | United Kingdom | 7285000 | (B | 8 |Cairo | Egypt| 6789479 (B | (B (B Then, how do I get such a result? (B (B Best wishes (B Hongwei Liu (B 2005/06/03 (B (BWith a user variable http://dev.mysql.com/doc/mysql/en/variables.html. (B Something like this: (B (B SET @r = 0; (B SELECT @r:[EMAIL PROTECTED] AS rank, capital, country, Population (B FROM city (B ORDER BY Population DESC; (B (BMichael (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in basic query
well - that was what I tried first - but that does not work because that returns the latest date for which the task_id has a record as Assignment. (It is like it picks up the max date from all records that have name_rec_type as Assignment) But I wanted was if the date corresponding to the name_rec_type as Assignment is the max(date) then return that task_id. I hope I have made myself clear... Thanks, Anoop On 6/2/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, Try just : SELECT id_secr_rqst task_id, MAX(dt_aud_rec) AS latest FROM isr2_aud_log WHERE name_rec_type = 'Assignment' AND id_secr_rqst ='TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF' GROUP BY id_secr_rqst ; ++-+ | task_id | latest | ++-+ | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | ++-+ 1 row in set (0.02 sec) Mathias Selon Anoop kumar V [EMAIL PROTECTED]: Hi mysql-ers, I need help in a basic query: I have this table: select * from isr2_aud_log where id_secr_rqst IN ('TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') --and name_rec_type = 'Assignment' order by id_secr_rqst, dt_aud_rec ++-+-+ | id_secr_rqst | dt_aud_rec | name_rec_type | ++-+-+ | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | Submission | | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | Exception Requested | | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:30:46 | Exception Resource | | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:33:22 | Director Approval | | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:34:43 | Assignment | | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:35:03 | Risk Assessment | | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:36:03 | Assignment | | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:37:40 | SERB Approval | | TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF | 2005-05-20 14:39:29 | Assignment | ++-+-+ and i am using this query: SELECT t1.id_secr_rqst task_id, MAX(t2.dt_aud_rec) AS latest, t1.dt_aud_recAS date1 FROM isr2_aud_log t1, isr2_aud_log t2 WHERE t1.id_secr_rqst = t2.id_secr_rqst AND t1.name_rec_type = 'Assignment' AND t1.id_secr_rqst IN ( 'TASK23C6054B9D416C08:1284FD4:103FB047DF1:-7ECF') GROUP BY t2.id_secr_rqst HAVING date1 = latest What I expected to get is the id_secr_rqst which has the last name_rec_type = 'Assignment' In this case there is only one id_secr_rqst and it has the last name_rec_type as 'Assignment'. But I do not seem to get consistent results. As I am using an older version of mysql I do not have the liberty to use subqueries and will have to do everything using joins only. The problem I am facing is that this query only sometimes returns rows and most of the time I get an empty result set. This table does not have any primary keys. Can somebody please point out what is the mistake I am doing - I think it just needs a tweak here and there (I hope..) Thanks, Anoop -- Thanks and best regards, Anoop -- Thanks and best regards, Anoop
Re: LEFT JOIN changes order of results
[EMAIL PROTECTED] writes: You have a sort because you did an order by. If you had an index with the desired order by, it may be used. Try as you usage of covering indexes. Hi Mathias, Along with your suggestion, a little more thinking about the problem and some experimenting seems to have led to a good solution. There are only a few columns that are commonly sorted by, so I'll create a multi-column index for each of those columns, with mls_num as the second column. Then I'll make the sort on mls_num be in the same direction (ASC or DESC) as the primary sort. According to some quick experiments with EXPLAIN, query plans using this scheme seem to be comparable to plans without the additional mls_num sort. Thanks! ScottG. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recovery question
try REPAIR TABLE 'tablename' Gleb Paharenko wrote: Hello. REPAIR TABLE ... USE_FRM helps in difficult cases. See: http://dev.mysql.com/doc/mysql/en/repair-table.html Per Jessen [EMAIL PROTECTED] wrote: All, I've got a table with about 25mill rows that was victim of a crash recently. (power-failure). I've been trying to recover it, but I'm not making much progress. From the most recent attempts: myisamchk --safe-recover --force table - recovering (with keycache) MyISAM-table 'table' Data records: 21622679 4988000 7256000 10627000 myisamchk: error: 126 for record at pos 1589881104 MyISAM-table 'table' is not fixed because of errors myisamchk -r --force --tmpdir=/data2/tmp table - recovering (with sort) MyISAM-table 'table' Data records: 12876899 - Fixing index 1 - Fixing index 2 - Fixing index 3 Key 3 - Found wrong stored record at 0 MyISAM-table 'table' is not fixed because of errors Where do I go from here? I've got a backup of the table, but I'm not sure what sort of state it is in.
Re: Out of memory, mysqld got signal 6;
Hello. This could be not exactly 4.0.21 issue, but issue of a compiler, say, if you've built MySQL manually. Sure, can do - but is this a known issue with 4.0.21? I wasn't able to find anything in the bug tracker... TIA, -- Andrew Hill Software Developer m3 Media Services Limited Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7371 IRC: #max on freenode.net =20 -- 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]
Performance problems through gateway
I am running mysql 4.0.18 on Windows 2003 server which also hosts my apache tomcat server. My applet makes a connection to the mysql database on the server as well as a socket connection to a service on the same server. In the lab with only a hub between the client and server, the application performs well and data is transferred quickly. In the deployed environment with a pair of gateways in between, socket performance is not affected much, but the application gui bogs down on the database queries. Performance is so slow that some simple GUI updates take up to 5-7 seconds with only a simple 1 table update occurring. Does anyone have experience with this and/or can provide some insight? This e-mail and any files transmitted with it are proprietary and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of ITT Industries, Inc. The recipient should check this e-mail and any attachments for the presence of viruses. ITT Industries accepts no liability for any damage caused by any virus transmitted by this e-mail.
Re: Performance problems through gateway
Celona, Paul - AES [EMAIL PROTECTED] wrote on 06/03/2005 01:03:18 PM: I am running mysql 4.0.18 on Windows 2003 server which also hosts my apache tomcat server. My applet makes a connection to the mysql database on the server as well as a socket connection to a service on the same server. In the lab with only a hub between the client and server, the application performs well and data is transferred quickly. In the deployed environment with a pair of gateways in between, socket performance is not affected much, but the application gui bogs down on the database queries. Performance is so slow that some simple GUI updates take up to 5-7 seconds with only a simple 1 table update occurring. Does anyone have experience with this and/or can provide some insight? It sounds like you don't have all of your indexes declared on your production database. There could also be an issue of network lag between your application server and your database server. The best performing applications use the fewest trips to the database to accomplish what they need. You may want to examine your application design and minimize the number of trips you make to the server. For example, assume you run two queries, one to get a list of departments and another to list the people in each department. If you design your application to perform one lookup to get the departments list then loop through that list to find the department's people, you are making way too many trips to the database. A more efficient design is to JOIN the two tables and submit just one query. Then, as you process the results, you detect when the Department value changes and adjust your OUTPUT accordingly. Could it be the volume of data you are trying to present is just that much larger with your production data set than it was with your development dataset that it's taking that much longer to format the output? You provided so FEW details of the actual issue, it's VERY hard to be more helpful. Can you provide more details of what isn't working the way you want and why? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
mysqlhotcopy
Hello, Anyone here run mysqlhotcopy? I've read the docs on it but they are basicly just a howto and don't go too in depth. I've got a DB that is the back end to a 24/7 application. I ususally do backups from a replicated db by shutting down the Replicated DB then doing a tar are all the db files, then starting the db when done. Does mysqlhotcopy allow you to take a full snapshot of the db without needing to shut it down? What happens to read writes and updates while mysqlhotcopy is running? Any info or experiences anyone has would be greatly appreciated. Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
I run 24/7 applications also. Use mysqlhotcopy to do exactly what you're doing by hand now. Run mysqlhotcopy on a slave server. It does exactly what you think. Lock and flush the tables, tarball them, and unlock them. No shutdown required. - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 03, 2005 11:11 AM Subject: mysqlhotcopy Hello, Anyone here run mysqlhotcopy? I've read the docs on it but they are basicly just a howto and don't go too in depth. I've got a DB that is the back end to a 24/7 application. I ususally do backups from a replicated db by shutting down the Replicated DB then doing a tar are all the db files, then starting the db when done. Does mysqlhotcopy allow you to take a full snapshot of the db without needing to shut it down? What happens to read writes and updates while mysqlhotcopy is running? Any info or experiences anyone has would be greatly appreciated. Thanks, Jeff -- 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]
max_seeks_for_key in InnoDB
I am having problems with MySQL inconsistently choosing the wrong index, or no index at all, for queries on tables with 20 million rows. Would it be a good idea for me to set max_seeks_for_key to 1 (or something less than 4 billion), in order to force MySQL to use an index? We are using InnoDB. Since InnoDB has clustered indexes, is there ever a good reason for MySQL to prefer a table scan? Thanks, -Bob
RE: mysqlhotcopy
Am I right in assuming that while mysqlhotcopy is running, nobody else can write to or update the DB? Jeff -Original Message- From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED] Sent: Friday, June 03, 2005 2:24 PM To: Jeff McKeon; mysql@lists.mysql.com Subject: Re: mysqlhotcopy I run 24/7 applications also. Use mysqlhotcopy to do exactly what you're doing by hand now. Run mysqlhotcopy on a slave server. It does exactly what you think. Lock and flush the tables, tarball them, and unlock them. No shutdown required. - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 03, 2005 11:11 AM Subject: mysqlhotcopy Hello, Anyone here run mysqlhotcopy? I've read the docs on it but they are basicly just a howto and don't go too in depth. I've got a DB that is the back end to a 24/7 application. I ususally do backups from a replicated db by shutting down the Replicated DB then doing a tar are all the db files, then starting the db when done. Does mysqlhotcopy allow you to take a full snapshot of the db without needing to shut it down? What happens to read writes and updates while mysqlhotcopy is running? Any info or experiences anyone has would be greatly appreciated. Thanks, Jeff -- 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: Performance problems through gateway
Celona, Paul - AES wrote: I am running mysql 4.0.18 on Windows 2003 server which also hosts my apache tomcat server. My applet makes a connection to the mysql database on the server as well as a socket connection to a service on the same server. In the lab with only a hub between the client and server, the application performs well and data is transferred quickly. In the deployed environment with a pair of gateways in between, socket performance is not affected much, but the application gui bogs down on the database queries. Performance is so slow that some simple GUI updates take up to 5-7 seconds with only a simple 1 table update occurring. Does anyone have experience with this and/or can provide some insight? If your applet is making connections on each page, you might be having reverse dns problems. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_seeks_for_key in InnoDB
Hi, you can use a hint to force specific index usage : http://lib.hutech.edu.vn/ebookonline/ebook1/0596003064/hpmysql-CHP-5-SECT-4.html But this is not a good idea since data change and index selectivity can become bad. Also, if the index scan + the table scan is bigger than a full table scan, even you will prefer FTS. So, according to selectivity, usage of an index can be a very bad idea. Thsi depends on how many rows your query retreives among the count(*) of the table. Mathias Selon Bob O'Neill [EMAIL PROTECTED]: I am having problems with MySQL inconsistently choosing the wrong index, or no index at all, for queries on tables with 20 million rows. Would it be a good idea for me to set max_seeks_for_key to 1 (or something less than 4 billion), in order to force MySQL to use an index? We are using InnoDB. Since InnoDB has clustered indexes, is there ever a good reason for MySQL to prefer a table scan? Thanks, -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
Yep. - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: Jeremiah Gowdy [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, June 03, 2005 12:11 PM Subject: RE: mysqlhotcopy Am I right in assuming that while mysqlhotcopy is running, nobody else can write to or update the DB? Jeff -Original Message- From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED] Sent: Friday, June 03, 2005 2:24 PM To: Jeff McKeon; mysql@lists.mysql.com Subject: Re: mysqlhotcopy I run 24/7 applications also. Use mysqlhotcopy to do exactly what you're doing by hand now. Run mysqlhotcopy on a slave server. It does exactly what you think. Lock and flush the tables, tarball them, and unlock them. No shutdown required. - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 03, 2005 11:11 AM Subject: mysqlhotcopy Hello, Anyone here run mysqlhotcopy? I've read the docs on it but they are basicly just a howto and don't go too in depth. I've got a DB that is the back end to a 24/7 application. I ususally do backups from a replicated db by shutting down the Replicated DB then doing a tar are all the db files, then starting the db when done. Does mysqlhotcopy allow you to take a full snapshot of the db without needing to shut it down? What happens to read writes and updates while mysqlhotcopy is running? Any info or experiences anyone has would be greatly appreciated. Thanks, Jeff -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
Jeff McKeon wrote: Am I right in assuming that while mysqlhotcopy is running, nobody else can write to or update the DB? Yes. That's why it's better to run it on a slave. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance problems through gateway
For further clarification, what we are observing is that pull down lists (which are already built on the GUI) take a long time to complete processing. The processing we are performing upon user selection is taking the selected element, updating 1 database column in 1 table with the value, and then writing a string (timestamp plus about 30 chars) to our event log table. So we have no selects, just one update and one insert. The performance of the data transfers using the direct socket connection goes from 15 milli sec (in the lab) to ~32 milli sec (in pseudo production env). But the database calls go from 1 sec to several seconds (have not measured this yet). The database was exactly the same in both trials. We are moving small amounts of data (100 bytes) in any query. Does this shed any light? Celona, Paul - AES wrote: I am running mysql 4.0.18 on Windows 2003 server which also hosts my apache tomcat server. My applet makes a connection to the mysql database on the server as well as a socket connection to a service on the same server. In the lab with only a hub between the client and server, the application performs well and data is transferred quickly. In the deployed environment with a pair of gateways in between, socket performance is not affected much, but the application gui bogs down on the database queries. Performance is so slow that some simple GUI updates take up to 5-7 seconds with only a simple 1 table update occurring. Does anyone have experience with this and/or can provide some insight? From: gerald_clark If your applet is making connections on each page, you might be having reverse dns problems. From: Shawn Green It sounds like you don't have all of your indexes declared on your production database. There could also be an issue of network lag between your application server and your database server. The best performing applications use the fewest trips to the database to accomplish what they need. You may want to examine your application design and minimize the number of trips you make to the server. For example, assume you run two queries, one to get a list of departments and another to list the people in each department. If you design your application to perform one lookup to get the departments list then loop through that list to find the department's people, you are making way too many trips to the database. A more efficient design is to JOIN the two tables and submit just one query. Then, as you process the results, you detect when the Department value changes and adjust your OUTPUT accordingly. Could it be the volume of data you are trying to present is just that much larger with your production data set than it was with your development dataset that it's taking that much longer to format the output? You provided so FEW details of the actual issue, it's VERY hard to be more helpful. Can you provide more details of what isn't working the way you want and why? Shawn Green Database Administrator Unimin Corporation - Spruce Pine This e-mail and any files transmitted with it are proprietary and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of ITT Industries, Inc. The recipient should check this e-mail and any attachments for the presence of viruses. ITT Industries accepts no liability for any damage caused by any virus transmitted by this e-mail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4 Old Password
Is there a way to tell mysql 4 to defaul to the old style passwords instead of having to run this every time we add a user? SET PASSWORD FOR mailto:'user'@'localhost' 'user'@'localhost' = OLD_PASSWORD('passowordo'); Applications we develop in php, and phpMyadmin don't seem to work with the new style password. Thanks!
Re: MySQL 4 Old Password
On Friday 03 June 2005 03:36 pm, Joey wrote: SET PASSWORD FOR mailto:'user'@'localhost' 'user'@'localhost' = OLD_PASSWORD('passowordo'); Why not recompile php to work with 4.1? --old-password Jeff pgpEctk2PnMK1.pgp Description: PGP signature
replication stopped
I took a couple of days vacation last week and naturally the /data partition of my database server filled up because of something I was running and had forgotten about. So my backup guy, thinking to help out, gzipped all my .bin logs. Now I am noticing replication seems to have stopped on my other server. Not sure if the two events are related or not. It could be that things got all hosed up not because of the gzipping but because the partition filled up in the first place? Anyway, what do I do now? Do I need to do a new dump and start replication all over again? --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Old Password
Joey wrote: Is there a way to tell mysql 4 to defaul to the old style passwords instead of having to run this every time we add a user? SET PASSWORD FOR mailto:'user'@'localhost' 'user'@'localhost' = OLD_PASSWORD('passowordo'); Applications we develop in php, and phpMyadmin don't seem to work with the new style password. Thanks! Did you check the manual? http://dev.mysql.com/doc/mysql/en/password-hashing.html http://dev.mysql.com/doc/mysql/en/old-client.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compile error
I am stumped with this compile problem with 4.0.24 src. Not sure what is causing it. Can anyone provide some insights? Last time I compiled on the same environment was 4.0.20 source. And all was ok. gcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/ms/svc/mysql/4.0.24\ -DDATADIR=\/ms/data/mysql/4.0.24\ -DSHAREDIR=\/ms/svc/mysql/4.0.24/share/mysql\ -DHAVE_CONFIG_H -I. -I. -I.. -I../innobase/include -I./../include -I./../regex -I. -I../include -I. -O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -mcpu=v8 -Wa,-xarch=v8plusa -fno-implicit-templates -fno-exceptions -fno-rtti -D_FILE_OFFSET_BITS=64 -DHAVE_CURSES_H -I/netapp/home/mislam/devel/database/mysql40/include -DHAVE_RWLOCK_T -fno-inline -c sql_yacc.cc /usr/local/share/bison.simple: In function `int yyparse()': /usr/local/share/bison.simple:347: `YYSIZE_T' undeclared (first use this function) /usr/local/share/bison.simple:347: (Each undeclared identifier is reported only once /usr/local/share/bison.simple:347: for each function it appears in.) /usr/local/share/bison.simple:347: parse error before `;' make[3]: *** [sql_yacc.o] Error 1 make[3]: Leaving directory `/netapp/home/mislam/devel/database/mysql40/sql' make[2]: *** [install-recursive] Error 1 make[2]: Leaving directory `/netapp/home/mislam/devel/database/mysql40/sql' make[1]: *** [install] Error 2 make[1]: Leaving directory `/netapp/home/mislam/devel/database/mysql40/sql' make: *** [install-recursive] Error 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL for performance statistics
Hi all, I'm new to MySQL and I'm trying to write a program that among other things monitors database usage. I would like to write an SQL query to retrieve info like session id, user name, cpu usage. Is there any MySQL system tables that gives me this sort of information? To be more precise, I'm trying to get the MySQL equivalent of the following Oracle SQL: select sesstat.sid as sid, sess.username user, sess.status as status, sess.program as program, aud.name as command, sesstat.value / 100 as cpu, from V$STATNAME statname, V$SESSTAT sesstat, V$SESSION sess, sys.audit_actions aud where statname.statistic# = sesstat.statistic# and sess.sid = sesstat.sid and sess.username is not null and statname.name = 'CPU used by this session' and aud.action = sess.command order by sess.sid thanks! Ana __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/
Re: SQL for performance statistics
In the last episode (Jun 03), Ana Leite said: I'm new to MySQL and I'm trying to write a program that among other things monitors database usage. I would like to write an SQL query to retrieve info like session id, user name, cpu usage. Is there any MySQL system tables that gives me this sort of information? To be more precise, I'm trying to get the MySQL equivalent of the following Oracle SQL: select sesstat.sid as sid, sess.username user, sess.status as status, sess.program as program, aud.name as command, sesstat.value / 100 as cpu, Try SHOW PROCESSLIST. that will get you all the above info except CPU usage. You can't get that, since MySQL uses threads and getrusage only gives you the process total. Oracle creates a separate process for each connection, which is why it can give you CPU. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied
Hi, I have been noticing following error when trying to connect mysql. ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) or ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) What could be reason? Is there any way I can connect to database without changing passwd? thanks - Discover Yahoo! Get on-the-go sports scores, stock quotes, news more. Check it out!