default table type = innodb is stable??
hi, i have a machine with 6G memory, loaded with RHEL4. now, my question is mysql support innodb as the default table type? will be stable running RHEL4? i saw /usr/share/mysql/my-innodb-heavy-4G.cnf file, then, i replace the existing one (/etc/my.cnf). but, it failed to start the mysql server. please advise. thanks? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IP Resolution
Dear MySQL, My MySQL 4.1.9 has lost the ability to work out what IP address clients are connecting from. Eg: $ mysqladmin processlist ++--+---+--+-+--+---+--+ | Id | User | Host | db | Command | Time | State | Info| ++--+---+--+-+--+---+--+ | 5 | test | 0.0.0.0:55049 | test | Sleep | 10 | || | 6 | root | localhost | | Query | 0| | show processlist | ++--+---+--+-+--+---+--+ This is annoying as I can't authenticate users based on their IP address. I suspect this may be a clash between IPv6 and IPv4. It happened after a patch was applied to the AIX operating system and MySQL recompiled. Would any members have experience or ideas of how this problem may be resolved? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a question about system page.
I learn configuring the mysql server in the manul 5.3.1.I notice there is a parameter --large pages.Please tell my how do i alter linux system memory pages . _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting total results from a table
Hello, I am trying to build a sql statement for MySQL 4.0.x which does count the accumulated total entries in a table per day since a specified start date. For example: Day 1 = 10 Day 2 = 12 Day 3 = 9 The statement I am using gives back the entries per day, starting each day with 0. For example: Day1 = 10 Day2 = 2 Day3 =0 (-3) code: SELECT DATE_FORMAT( timestamp, '%Y%m%d') AS mydate, count(*) AS ct, ID FROM $DB.$T4 WHERE (timestamp = '$date_start' AND timestamp = '.$date_end.235959') AND confirmed = '1' GROUP BY mydate Has anybody an idea how to recraft the statement to get the accumulated entries per day? Thank you for any help, Alexandra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default table type = innodb is stable??
Currently i have 4 Gbyte RAM and RH 9 Linux using InnoDB and yes, it's stable :) Hiu Yen Onn wrote: hi, i have a machine with 6G memory, loaded with RHEL4. now, my question is mysql support innodb as the default table type? will be stable running RHEL4? i saw /usr/share/mysql/my-innodb-heavy-4G.cnf file, then, i replace the existing one (/etc/my.cnf). but, it failed to start the mysql server. please advise. thanks? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting total results from a table
Alexandra wrote: Hello, I am trying to build a sql statement for MySQL 4.0.x which does count the accumulated total entries in a table per day since a specified start date. For example: Day 1 = 10 Day 2 = 12 Day 3 = 9 The statement I am using gives back the entries per day, starting each day with 0. For example: Day1 = 10 Day2 = 2 Day3 =0 (-3) code: SELECT DATE_FORMAT( timestamp, '%Y%m%d') AS mydate, count(*) AS ct, ID FROM $DB.$T4 WHERE (timestamp = '$date_start' AND timestamp = '.$date_end.235959') AND confirmed = '1' GROUP BY mydate Has anybody an idea how to recraft the statement to get the accumulated entries per day? Thank you for any help, Alexandra Hi Alexandra you were not far off give this a go SELECT DATE_FORMAT( timestamp, '%Y%m%d') AS mydate, count(*) AS ct FROM $DB.$T4 WHERE timestamp = '$date_start' AND timestamp = '.$date_end.235959') AND confirmed = '1' GROUP BY DATE_FORMAT( timestamp, '%Y%m%d') i don't know what the ID field in the select clause is for , so i omitted it. i suspect the ID field in the select clause caused the prob. shout if you need more help. -- Arno Coetzee Developer Flash Media Group Office : +27 12 342 3490 Mobile : +27 82 693 6180 Fax : + 27 12 430 4269 www.flashmedia.co.za FMG Total messaging solution. For all your GSM (SMS and USSD) messaging needs. Quick and easy do-it-yourself interfaces. http://www.flashmedia.co.za/tms.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Raw devices and MySQL
Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote on 14/10/2005 03:28:15: Hi Listers, Does anybody know if the MyISAM engine (apart from InnoDB) allows the use of raw disk space rather than having cooked files? If not, is this feature likely to be included in a future release? I had a quick scour of the MySQL website but can't seem to find a page with upcoming features. Is there such a beast? Given the way MyISAM works, I would have thought it very unlikely that this would ever happen. It would mean the SQL team developing their own special-purpose file system. Why bother, when they already have such a file system, called InnoDB? It is difficult to see what gain there would be for investing a very large amount of effort which could probably better spent elsewhere. As I understand it, the gains of using raw devices with InnoDB are, while not zero, small. Why do you want such a feature? Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Raw devices and MySQL
Hi Alec, Thanks for the response. You are probably quite right about the gains, mostly my query was through curiosity more than anything. I use raw devices with Informix and a couple of other databases and there are small improvements to be seen on these products. On very large scale implementations, the small things can often translate into user satisfaction and that is a big part of my job 8-) Unfortunately I don't always get any say on the how and why of the clients application design and so have to make the best of what I have been given to manage. Often we are presented with the hardware as a fait accompli so if we can get an improvement, no matter how small, they tend to become important. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, 14 October 2005 6:53 PM To: Logan, David (SST - Adelaide) Cc: MySQL List Subject: Re: Raw devices and MySQL Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote on 14/10/2005 03:28:15: Hi Listers, Does anybody know if the MyISAM engine (apart from InnoDB) allows the use of raw disk space rather than having cooked files? If not, is this feature likely to be included in a future release? I had a quick scour of the MySQL website but can't seem to find a page with upcoming features. Is there such a beast? Given the way MyISAM works, I would have thought it very unlikely that this would ever happen. It would mean the SQL team developing their own special-purpose file system. Why bother, when they already have such a file system, called InnoDB? It is difficult to see what gain there would be for investing a very large amount of effort which could probably better spent elsewhere. As I understand it, the gains of using raw devices with InnoDB are, while not zero, small. Why do you want such a feature? Alec -- 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: upload images / mp3 more Than 1 MB capacity ---- please help
Dan Buettner wrote: I tend to disagree - at my place of employment, a newspaper, we have hundreds of gigabytes of BLOB data (ad and page layouts digital artwork) stored in SQL databases. Granted we are using Sybase for that and not MySQL but there are a lot of advantages to it - access control, change control and tracking, easy insertion and deletion, and access from any client right through the database driver so you can repurpose content more easily. There are situations where it might be useful to store large amounts of binary data in a database. For most situations the best solution is to store metadata about the file in a database and store the file itself on a file system. There have been lots of discussions about it on this list in the past. From those discussions one could conclude that in general a file system is best for storing (large) files and the metadata about these files can live in a database. But there are situations where storing large files in a database has more advantages. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing a database error
Hi I am working with MySql 4.1.14 under Windows XP. I am trying to do a database import from a file. I have the following table definiton CREATE TABLE attribute_instance ( name varchar(200) NOT NULL default '', id int(11) NOT NULL default '0', PRIMARY KEY (id,name), KEY id (id,name), KEY KM_INDEX_ATTRIBUTE_INSTANCE (id), CONSTRAINT `attribute_instance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `read_restring` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; When Mysql tries to create this table It shows the following error message : ERROR 1005 (HY000) at line 15: Can't create table '.\x\attribute_instance.frm' ( errno: 150) MySQL error code 150: Foreign key constraint is incorrectly formed It seems to be a foreing key error. But the database was working fine. I only did an export and then I want to import of the same database. Has anybody an idea of this error? Any advice? Thanks in advance and regards Boris Villazón -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to test the MySQL Server (windows version) ?
lu ming [EMAIL PROTECTED] wrote on 10/14/2005 12:20:19 AM: Hi, joerg Thank you very much! But i want to know more about this problem. I find a perl script about mysql server's test in the mysql-test subdirectory(/mysql-test/mysql-test-run.pl). Is this the test script written by perl what could replace the shell script(/mysql-test/mysql-test-run.sh) in Windows mentioned in your e-mail? Could it be rightly run in windows now? Or it cann't be run due to the unfinished work? Are the perl scripts that mentioned in your e-mail included in the MySQL Server's Unix source distributions(mysql-5.0.12-beta-linux) ? Are the perl test scripts only used in Mysql's internal works now? Best regards luming The problems are more with Windows than with the script. The testing script was written in BASH (I believe) which does not have an interpreter for Windows (outside of Cygwin. However if you are inside Cygwin, you really aren't working with Windows any more.) Several other scripting languages do have interpreters for Windows: VBScript (d), JScript (d), PHP, Python, PERL, and more. (d) = installed by default. I think that the mysql testing script is being ported from BASH into PERL and when that happens you will have the ability to run it on any Windows system configured to run PERL scripts. Until then, we Win32 people just trust the binaries and avoid compiling our own copies unless we absolutely need to. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Convert Subquery - need to find max of a set
I need to convert this query into one without a subquery: SELECT r.selector_forms_results_max_points AS points, r.selector_forms_results_description AS description, FROM selector_forms_results AS r WHERE r.selector_forms_id = 1 AND r.selector_forms_results_max_points = ( SELECT DISTINCT r.selector_forms_results_max_points AS max_points FROM selector_forms_results AS r WHERE r.selector_forms_results_max_points = 12 ORDER BY r.selector_forms_results_max_points DESC LIMIT 1 ); Given selector_forms_results_max_points like below: 9 9 9 11 11 11 11 13 13 and selector_forms_results_max_points target of 12, I want the maximum of the set of selector_forms_results_max_points that are less than 12. So in this case, I want all rows where selector_forms_results_max_points = 11. I hope I have explained this somewhat clearly. I cannot see how this can be accomplished with a join, but my sql skills are rudimentary. Thanks for any help. Table structure and sample data follow. kgt Table: CREATE TABLE `selector_forms_results` ( `selector_forms_results_id` int(11) NOT NULL auto_increment, `selector_forms_id` int(11) NOT NULL default '0', `selector_forms_results_description` varchar(255) NOT NULL default '', `selector_forms_results_max_points` int(11) NOT NULL default '0', PRIMARY KEY (`selector_forms_results_id`), KEY `selector_forms_results_max_points` (`selector_forms_results_max_points`) ); And some sample data: insert into `selector_forms_results` values (1,1,'Pro1 180',9); insert into `selector_forms_results` values (2,1,'Iron Eagle 180',9); insert into `selector_forms_results` values (3,1,'RR 180',9); insert into `selector_forms_results` values (4,1,'Pro1 200',11); insert into `selector_forms_results` values (5,1,'Iron Eagle Platinum 200',11); insert into `selector_forms_results` values (6,1,'RR 200',11); insert into `selector_forms_results` values (7,1,'IK 200',11); insert into `selector_forms_results` values (8,1,'Pro1 215',13); insert into `selector_forms_results` values (9,1,'Iron Eagle Platinum 215',13); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing a database error
(response interspersed) Boris Villazon [EMAIL PROTECTED] wrote on 10/14/2005 08:30:13 AM: Hi I am working with MySql 4.1.14 under Windows XP. I am trying to do a database import from a file. I have the following table definiton CREATE TABLE attribute_instance ( name varchar(200) NOT NULL default '', id int(11) NOT NULL default '0', PRIMARY KEY (id,name), KEY id (id,name), This index duplicates your primary key and is not needed KEY KM_INDEX_ATTRIBUTE_INSTANCE (id), Your primary key already indexes this column. This index is also not needed. CONSTRAINT `attribute_instance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `read_restring` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Is `read_restring` also an InnoDB table? Is `read_restring`.`id` also indexed? ) TYPE=InnoDB; When Mysql tries to create this table It shows the following error message : ERROR 1005 (HY000) at line 15: Can't create table '.\x\attribute_instance.frm' ( errno: 150) MySQL error code 150: Foreign key constraint is incorrectly formed Is that really what SHOW INNODB STATUS said or is that the output of perror? Whenever working with Innodb, if you get an error always check SHOW INNODB STATUS for more complete details. It seems to be a foreing key error. It is but exactly what was wrong with the foreign key you didn't say. You get that from SHOW INNODB STATUS. At this point in your script, does `read_restring` even exist? If it doesn't, you may need to reorganize your script. If `attribute_instance` has data already in it and at least one row has an `id` value that isn't on `read_restring`, that would also cause an error 150. But the database was working fine. I only did an export and then I want to import of the same database. Has anybody an idea of this error? Any advice? Thanks in advance and regards Boris Villazón Check my comments above and get back to us with the error description from SHOW INNODB STATUS immediately after re-creating the error if you are still having problems. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: How to test the MySQL Server (windows version) ?
Hi! [EMAIL PROTECTED] wrote: lu ming [EMAIL PROTECTED] wrote on 10/14/2005 12:20:19 AM: Hi, joerg Thank you very much! But i want to know more about this problem. I find a perl script about mysql server's test in the mysql-test subdirectory(/mysql-test/mysql-test-run.pl). Is this the test script written by perl what could replace the shell script(/mysql-test/mysql-test-run.sh) in Windows mentioned in your e-mail? It will be the one. It is not yet finished. Could it be rightly run in windows now? Or it cann't be run due to the unfinished work? You can run it, but you may have problems interpreting the results. Several tests will seem to fail, just because some output differs from the expected one. Obviously, much output is platform dependent (starting with file names, then process or thread IDs, ...), and the tests do not yet allow for the output generated on Windows. Note that I say output, not database result! Are the perl scripts that mentioned in your e-mail included in the MySQL Server's Unix source distributions(mysql-5.0.12-beta-linux) ? Yes, they are included (AFAIK - not checked), but they are under construction even for Unix. Just today, I fixed another problem in them, that had shown up during our internal 5.0 builds. Are the perl test scripts only used in Mysql's internal works now? Obviously, I cannot guarantee that nobody else is using them. But we have not proposed to anybody outside MySQL to do so. The problems are more with Windows than with the script. The testing script was written in BASH (I believe) which does not have an interpreter for Windows (outside of Cygwin. However if you are inside Cygwin, you really aren't working with Windows any more.) Just for completeness: If anybody wants to use Unix style tools (shell, awk, ed, tr, sed, lex/bison/yacc, ...) in a Windows environment, I propose to look at MinGW because these are native Windows binaries. Several other scripting languages do have interpreters for Windows: VBScript (d), JScript (d), PHP, Python, PERL, and more. (d) = installed by default. Right. So for us, Perl is the language of choice, as we can make it available on all platforms. I think that the mysql testing script is being ported from BASH into PERL and when that happens you will have the ability to run it on any Windows system configured to run PERL scripts. Until then, we Win32 people just trust the binaries and avoid compiling our own copies unless we absolutely need to. We will see not to disappoint that trust, and appreciate it. Thanks! Joerg -- Joerg Bruehe, Senior Production Engineer 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: Convert Subquery - need to find max of a set
Kristen G. Thorson [EMAIL PROTECTED] wrote on 10/14/2005 09:15:21 AM: I need to convert this query into one without a subquery: SELECT r.selector_forms_results_max_points AS points, r.selector_forms_results_description AS description, FROM selector_forms_results AS r WHERE r.selector_forms_id = 1 AND r.selector_forms_results_max_points = ( SELECT DISTINCT r.selector_forms_results_max_points AS max_points FROM selector_forms_results AS r WHERE r.selector_forms_results_max_points = 12 ORDER BY r.selector_forms_results_max_points DESC LIMIT 1 ); Given selector_forms_results_max_points like below: 9 9 9 11 11 11 11 13 13 and selector_forms_results_max_points target of 12, I want the maximum of the set of selector_forms_results_max_points that are less than 12. So in this case, I want all rows where selector_forms_results_max_points = 11. I hope I have explained this somewhat clearly. I cannot see how this can be accomplished with a join, but my sql skills are rudimentary. Thanks for any help. Table structure and sample data follow. kgt Table: CREATE TABLE `selector_forms_results` ( `selector_forms_results_id` int(11) NOT NULL auto_increment, `selector_forms_id` int(11) NOT NULL default '0', `selector_forms_results_description` varchar(255) NOT NULL default '', `selector_forms_results_max_points` int(11) NOT NULL default '0', PRIMARY KEY (`selector_forms_results_id`), KEY `selector_forms_results_max_points` (`selector_forms_results_max_points`) ); And some sample data: insert into `selector_forms_results` values (1,1,'Pro1 180',9); insert into `selector_forms_results` values (2,1,'Iron Eagle 180',9); insert into `selector_forms_results` values (3,1,'RR 180',9); insert into `selector_forms_results` values (4,1,'Pro1 200',11); insert into `selector_forms_results` values (5,1,'Iron Eagle Platinum 200',11); insert into `selector_forms_results` values (6,1,'RR 200',11); insert into `selector_forms_results` values (7,1,'IK 200',11); insert into `selector_forms_results` values (8,1,'Pro1 215',13); insert into `selector_forms_results` values (9,1,'Iron Eagle Platinum 215',13); The only advantages of doing this style of query as a subquery is that you do not need to explicitly create/destroy a temporary table (the engine does that for you behind the scenes) and you can write it in one statement. In my experience, the subquery versions are usually noticably slower. YMMV. Here is a non-subquery equivalent of your particular query: CREATE TEMPORARY TABLE tmpMAX ( KEY (`selector_forms_id`) ) SELECT `selector_forms_id` , MAX(`selector_forms_results_max_points`) as max_points FROM `selector_forms_id` WHERE `selector_forms_results_max_points`=12 GROUP BY `selector_forms_id`; SELECT r.`selector_forms_results_max_points` AS points, r.`selector_forms_results_description` AS description, FROM `selector_forms_results` AS r INNER JOIN `tmpMAX` tm on r.`selector_forms_id` = tm.`selector_forms_id` AND r.`selector_forms_results_max_points` = tm.`max_points` WHERE r.`selector_forms_id` = 1; DROP TEMPORARY TABLE tmpMAX; I made this a little less restrictive than your original query so that you could run the query against other `selector_form_id` values if you wanted. Instead of using a temporary table, I could have used a single variable to hold the max value as you only wanted the list for a single value of `selector_forms_id`. Does this help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Convert Subquery - need to find max of a set
[EMAIL PROTECTED] wrote: Kristen G. Thorson [EMAIL PROTECTED] wrote on 10/14/2005 09:15:21 AM: I need to convert this query into one without a subquery: SELECT r.selector_forms_results_max_points AS points, r.selector_forms_results_description AS description, FROM selector_forms_results AS r WHERE r.selector_forms_id = 1 AND r.selector_forms_results_max_points = ( SELECT DISTINCT r.selector_forms_results_max_points AS max_points FROM selector_forms_results AS r WHERE r.selector_forms_results_max_points = 12 ORDER BY r.selector_forms_results_max_points DESC LIMIT 1 ); Given selector_forms_results_max_points like below: 9 9 9 11 11 11 11 13 13 and selector_forms_results_max_points target of 12, I want the maximum of the set of selector_forms_results_max_points that are less than 12. So in this case, I want all rows where selector_forms_results_max_points = 11. I hope I have explained this somewhat clearly. I cannot see how this can be accomplished with a join, but my sql skills are rudimentary. Thanks for any help. Table structure and sample data follow. kgt Table: CREATE TABLE `selector_forms_results` ( `selector_forms_results_id` int(11) NOT NULL auto_increment, `selector_forms_id` int(11) NOT NULL default '0', `selector_forms_results_description` varchar(255) NOT NULL default '', `selector_forms_results_max_points` int(11) NOT NULL default '0', PRIMARY KEY (`selector_forms_results_id`), KEY `selector_forms_results_max_points` (`selector_forms_results_max_points`) ); And some sample data: insert into `selector_forms_results` values (1,1,'Pro1 180',9); insert into `selector_forms_results` values (2,1,'Iron Eagle 180',9); insert into `selector_forms_results` values (3,1,'RR 180',9); insert into `selector_forms_results` values (4,1,'Pro1 200',11); insert into `selector_forms_results` values (5,1,'Iron Eagle Platinum 200',11); insert into `selector_forms_results` values (6,1,'RR 200',11); insert into `selector_forms_results` values (7,1,'IK 200',11); insert into `selector_forms_results` values (8,1,'Pro1 215',13); insert into `selector_forms_results` values (9,1,'Iron Eagle Platinum 215',13); The only advantages of doing this style of query as a subquery is that you do not need to explicitly create/destroy a temporary table (the engine does that for you behind the scenes) and you can write it in one statement. In my experience, the subquery versions are usually noticably slower. YMMV. Here is a non-subquery equivalent of your particular query: CREATE TEMPORARY TABLE tmpMAX ( KEY (`selector_forms_id`) ) SELECT `selector_forms_id` , MAX(`selector_forms_results_max_points`) as max_points FROM `selector_forms_id` WHERE `selector_forms_results_max_points`=12 GROUP BY `selector_forms_id`; SELECT r.`selector_forms_results_max_points` AS points, r.`selector_forms_results_description` AS description, FROM `selector_forms_results` AS r INNER JOIN `tmpMAX` tm on r.`selector_forms_id` = tm.`selector_forms_id` AND r.`selector_forms_results_max_points` = tm.`max_points` WHERE r.`selector_forms_id` = 1; DROP TEMPORARY TABLE tmpMAX; I made this a little less restrictive than your original query so that you could run the query against other `selector_form_id` values if you wanted. Instead of using a temporary table, I could have used a single variable to hold the max value as you only wanted the list for a single value of `selector_forms_id`. Does this help? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thank you, this works perfectly for me. I don't even have any questions! kgt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query dies silently
Hi all, I have a rather strange problem that I cannot seem to figure out. When I run the following query from the MySQL console: SELECT Inventory.ID as InventoryID, Inventory.Name, Inventory.Capacity, Inventory.Beds, Avails.ID as AvailabilityID, Avails.ResortID, Avails.Cost, Avails.OpenWhen FROM Inventory, Avails WHERE Avails.InvID = Inventory.ID AND Avails.OpenWhen NOW() AND (Avails.StatusCode = 'GREEN' OR Avails.StatusCode = 'YELLOW') ORDER BY Avails.ResortID, Avails.InvID, Avails.OpenWhen; I get 142k rows back in just over 4 seconds. However, when I run this query using PHP through the browser, I get nothing at all. Occasionally, I get get an error about not being able to store the result, but this error does not always display. Is there some kind of query size limit that I am running into, or perhaps a temp table limit that is too low? mysql show variables; ++---+ | Variable_name | Value | ++---+ | back_log | 5 | | connect_timeout| 5 | | basedir| / | | datadir| /var/lib/mysql/ | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | join_buffer| 131072| | flush_time | 0 | | key_buffer | 8388600 | | language | /usr/share/mysql/english/ | | log| OFF | | log_update | OFF | | long_query_time| 10| | low_priority_updates | OFF | | max_allowed_packet | 1048576 | | max_connections| 100 | | max_connect_errors | 10| | max_delayed_insert_threads | 20| | max_join_size | 4294967295| | max_sort_length| 1024 | | max_write_lock_count | 4294967295| | net_buffer_length | 16384 | | pid_file | /var/lib/mysql/mysqld.pid | | port | 3306 | | protocol_version | 10| | record_buffer | 131072| | skip_locking | ON| | skip_networking| OFF | | socket | /var/lib/mysql/mysql.sock | | sort_buffer| 2097144 | | table_cache| 64| | thread_stack | 65536 | | tmp_table_size | 1048576 | | tmpdir | /tmp/ | | version| 3.22.32 | | wait_timeout | 28800 | ++---+ Please note that this database is on a host, and the version of the DB is out of my control. -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing a database error
[EMAIL PROTECTED] wrote: (response interspersed) Boris Villazon [EMAIL PROTECTED] wrote on 10/14/2005 08:30:13 AM: Hi I am working with MySql 4.1.14 under Windows XP. I am trying to do a database import from a file. I have the following table definiton CREATE TABLE attribute_instance ( name varchar(200) NOT NULL default '', id int(11) NOT NULL default '0', PRIMARY KEY (id,name), KEY id (id,name), This index duplicates your primary key and is not needed Oki, I will check ... thanks KEY KM_INDEX_ATTRIBUTE_INSTANCE (id), Your primary key already indexes this column. This index is also not needed. Oki, I will check also. CONSTRAINT `attribute_instance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `read_restring` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Is `read_restring` also an InnoDB table? Is `read_restring`.`id` also indexed? Yes, read_restring is also an InnoDB table and `read_restring`.`id` is also indexed. ) TYPE=InnoDB; When Mysql tries to create this table It shows the following error message : ERROR 1005 (HY000) at line 15: Can't create table '.\x\attribute_instance.frm' ( errno: 150) MySQL error code 150: Foreign key constraint is incorrectly formed Is that really what SHOW INNODB STATUS said or is that the output of perror? Whenever working with Innodb, if you get an error always check SHOW INNODB STATUS for more complete details. Show InnoDB status didn't give useful information in this case. It seems to be a foreing key error. It is but exactly what was wrong with the foreign key you didn't say. You get that from SHOW INNODB STATUS. At this point in your script, does `read_restring` even exist? If it doesn't, you may need to reorganize your script. If `attribute_instance` has data already in it and at least one row has an `id` value that isn't on `read_restring`, that would also cause an error 150. Thanks for your tip. I did reorganize my script and everything works fine. But the database was working fine. I only did an export and then I want to import of the same database. Has anybody an idea of this error? Any advice? Thanks in advance and regards Boris Villazón Check my comments above and get back to us with the error description from SHOW INNODB STATUS immediately after re-creating the error if you are still having problems. Thanks and regards Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Innodb?
Hello, I am just starting with MySQL and PHP, but this discussion about innodb has rather piqued my interest. Can someone please give a brief explanation as to what it is. Cheers, Joe.
Re: How to get a count from this query
Gerald Taylor [EMAIL PROTECTED] wrote on 10/14/2005 10:42:33 AM: Thank you for your answer. What I'm trying to do is return the scores of the diff values of datapoints between a particular user and all the other users in the system. (hence the mypmd.uid != pmd.uid) Some datapoints might be missing. I also want to know how many data point diffs we got for each user. I no longer need the avg. The tables are pretty straightforward CREATE TABLE pmd ( id bigint unsigned not null auto_increment primary key, uid bigint unsigned not null, pmid bigint unsigned not null, value int not null ) engine InnoDB DEFAULT CHARSET Latin1 and mnames is just the names of the datapoints being measured CREATE TABLE mnames ( pmid bigint unsigned not null auto_increment primary key, mname VARCHAR(20) ) engine InnoDB DEFAULT CHARSET Latin1 If an outer join would do it, I will like to learn something. Thnka you again. [EMAIL PROTECTED] wrote: Gerald Taylor [EMAIL PROTECTED] wrote on 10/13/2005 12:20:53 PM: SELECT avg(abs(pmd.value) - (mypmd.value))) as diff, pm.mname, pmd.uid FROM pmdata pmd, mnames pm, pmdata mypmd WHERE mypmd.uid= ? AND pmd.pmid=pm.pmid AND mypmd.pmid=pm.pmid AND pmd.uid != mypmd.uid GROUP BY pmd.pmid, pmd.uid ORDER BY pmd.uid What I would like also to return in this query is a COUNT of the number of pmd.uid of each different value so I know how many values I got from uid#1, uid#2 etc. I tried putting COUNT(pmd.uid) as numdelta but it just gave me a 1 in every row. The problem is I am not doing a straight GROUP BY pmd.uid Can you explain, in simple language, the question you are trying to answer with this query? I see an INNER JOIN and a != used together which makes me wonder if you needed to use one of the OUTER JOINs instead. Along with your explanation, please respond with the results of SHOW CREATE TABLE pmdata\G SHOW CREATE TABLE mnames\G Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine If you know how to make an explicit INNER JOIN, making either one of the OUTER JOINs becomes easy. I try to only use the explicit form of creating JOINs and I avoid the implicit form (the comma separated list that you used) like the plague. In your case, the INNER JOIN is appropriate because if there is only one user, there can be no differences and you shouldn't expect any results. If you want just a table of differences, that does not require a GROUP BY: SELECT me.uid as me , them.uid as them , n.mname as name , (me.value - them.value) as diff FROM pmd me INNER JOIN pmd them ON me.uid != them.uid AND me.pmid = them.pmid INNER JOIN mnames n ON me.pmid = n.pmid; If you want some statistics on your differences, that's another story. Assuming that each pmid value can appear more than once per uid (which may not be true for your data, but this assumption generalizes the solution) or that some pmids do not apply to some uids, this query should provide for each pmid of each uid, the name of the pmid, the sums of the differences, the sums of the absolute differences, the sum of the squares of the differences, the average difference, the average absolute difference, the standard deviation of the differences, and the count of how many other uids and pmids were compared to for each pmid that belongs to the user. In this case I would like to list ALL of the pmids for each uid, regardless if any other uid has the same one, so I need to use an OUTER JOIN ( the LEFT JOIN, in particular). SELECT me.uid , n.mname as name , sum(me.value - them.value) as sum_diff , sum(abs(me.value - them.value)) as sum_abs_diff , sum((me.value - them.value)*(me.value - them.value)) as sum_sq_diff , avg(me.value - them.value) as avg_diff , avg(abs(me.value - them.value)) as avg_abs_diff , std(me.value - them.value) as std_diff , count(distinct them.uid) as otheruids , count(them.pmid) as datapoints FROM pmd me INNER JOIN mnames n ON me.pmid = n.pmid; LEFT JOIN pmd them ON me.uid != them.uid AND me.pmid = them.pmid GROUP BY me.uid, n.mname; Let me know if that does what you want. And don't forget to CC: the list on all responses :-o Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Innodb?
Joe Herman [EMAIL PROTECTED] wrote on 10/14/2005 01:42:41 PM: Hello, I am just starting with MySQL and PHP, but this discussion about innodb has rather piqued my interest. Can someone please give a brief explanation as to what it is. Cheers, Joe. In a nutshell, InnoDB is one of the data storage engines distributed with MySQL (there are several). If you use the InnoDB storage engine to maintain your data you get certain features not available with some of the other storage engines (row-level locking, transction control, foreign keys, etc.). Each storage engine has it's strengths and weaknesses, including InnoDB, and which one will work best for you depends on your particular needs. Full details are in the manual which I HIGHLY RECOMMEND that you read (especially because you are new): http://dev.mysql.com/doc/ I recommend you stick with the HTML, viewable online version that applies to the version of your MySQL server and in your preferred language (translations are towards the bottom of the page). The online copies are indexed and searchable (even the translations) and you should become well acquainted with them. Look first for the section that describes the different storage engines for any specific information you need to answer your immediate question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: connection issue
Than you very much . It is working Anil DBA -Original Message- From: Dobromir Velev [mailto:[EMAIL PROTECTED] Sent: Thursday, October 13, 2005 8:58 PM To: mysql@lists.mysql.com Cc: Anil Subject: Re: connection issue When you connect without specifing the host, the mysql client will try to connect using the mysql socket file (usually /tmp/mysql.sock or /var/lib/mysql/mysql.sock) and when you specify the IP address it will try to connect using a TCP connection to port 3306. My guess is the you have name lookups turned on. If this is the case mysql will try to resolve the IP address given and probably the 192.168.3.111 IP is not resolving properly. The solution for this is to a add a skip-name-resolve option to your MySQL configuration file. You might also check http://dev.mysql.com/doc/refman/5.0/en/dns.html and the related articles in the MySQL documentation HTH Dobromir Velev On Thursday 13 October 2005 15:38, Anil wrote: Hi List, When I am trying to connect to mysql 4.0.20 database it is taking very long time when I specified host like Mysql -ux -p -h192.168.3.111 But it is connecting very quickly when I tried like below Mysql -ux -p It is an urgent issue. Please help me. Thanks Anil DBA -- Dobromir Velev -- 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: IP Resolution
MySQL 4.1.14 is the current version. You should always upgrade to the lastest release and test your problem before trying to report bugs. Ben Clewett wrote: Dear MySQL, My MySQL 4.1.9 has lost the ability to work out what IP address clients are connecting from. Eg: $ mysqladmin processlist ++--+---+--+-+--+---+--+ | Id | User | Host | db | Command | Time | State | Info| ++--+---+--+-+--+---+--+ | 5 | test | 0.0.0.0:55049 | test | Sleep | 10 | || | 6 | root | localhost | | Query | 0| | show processlist | ++--+---+--+-+--+---+--+ This is annoying as I can't authenticate users based on their IP address. I suspect this may be a clash between IPv6 and IPv4. It happened after a patch was applied to the AIX operating system and MySQL recompiled. Would any members have experience or ideas of how this problem may be resolved? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Non-linear degradation in bulk loads?
I've tried tweaking the structure of the schema to have, for example, a PRIMARY KEY index on email, no other indexes, and then insert in sorted order -- made no improvement whatsoever. Another clue that leads me to believe that this may be an OS issue: Starting a large cp on the same box (from a local filesystem other than the one the InnoDB data pool was on, to NFS) caused MySQL to become COMPLETELY backlogged (we went from ~15-20 connections at any given instant to 750 (our max_connections setting)). -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 12, 2005 8:15 AM To: mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? Jon, hmm... maybe one of the indexes inevitably is in a random order. Please post a typical SHOW INNODB STATUS\G when the inserts happen slowly. What is your my.cnf like? Regards, Heikki Innobase/Oracle - Alkuperäinen viesti - Lähettäjä: Jon Frisby [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED]; mysql@lists.mysql.com Lähetetty: Wednesday, October 12, 2005 3:08 AM Aihe: RE: Non-linear degradation in bulk loads? Two solutions: 1) sort the rows to be inserted on the key 'email' before inserting. 2) Or: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session: SET UNIQUE_CHECKS=0; For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. But make sure you do not have any duplicates in the rows! After sending my mail, I discovered SET UNIQUE_CHECKS=0, and subsequent to that it also occurred to me to try putting the data in in sorted order. Unfortunately, doing UNIQUE_CHECKS=0 did not work, and even the combination of both did not work. First chunk (3.4m rows) was ~1.5 minutes, second was ~5 minutes... At this point I'm inclined to believe that there is something very wrong with the disk subsystem because of this and other problems (doing a large cp from the datapool filesystem to another filesystem brought the database to a near-halt, among other things). As a stop-gap solution, I created the table with no indexes, and loaded all the data (loaded in linear time), and plan on doing a CREATE UNIQUE INDEX on the table. Will this happen in linear time, or near-linear time? *sigh* -JF -- 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]
Maximum execution time?
Hi Is there a way to setup a maximum query execution time per user. I understand the options max_connections and max_questions. But I am looking for a way to kill a query per user if it takes longer than XX minutes! Thanks John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update row/tbl question...
hi...
a basic/new/curious question. i have the following sample tbl schema. i do
the following update on the mysql command line and it updates for both
cases. my question is why???
it was my understanding that the two cases are different and shouldn't both
be triggered.the value of 'username' in the table is 'admin'.
DROP TABLE IF EXISTS UserTBL;
CREATE TABLE UserTBL (
username varchar(20) NOT NULL default '',
passwd varchar(60) NOT NULL default '',
pwFlg int(2) NOT NULL default '',
fname varchar(20) NOT NULL default '',
lname varchar(20) NOT NULL default '',
email varchar(50) NOT NULL default '',
`usertype` varchar(25) NOT NULL default '',
`gid` tinyint(3) unsigned NOT NULL default '1',
home_phone varchar(20) NOT NULL default '',
company_phone varchar(20) NOT NULL default '',
cell_phone varchar(20) NOT NULL default '',
address1 varchar(20) NOT NULL default '',
address2 varchar(20) NOT NULL default '',
city varchar(20) NOT NULL default '',
state varchar(20) NOT NULL default '',
countryID varchar(20) NOT NULL default '',
`active` enum('y','n') NOT NULL default 'n',
enable int(1) NOT NULL default '',
LoginStartTime timestamp NOT NULL default '',
LoginLimit int NOT NULL default '',
usergroup varchar(10) NOT NULL default '',
userlevel varchar(10) NOT NULL default '',
tmp_mail varchar(50) NOT NULL default '',
`real_name` varchar(32) NOT NULL default '',
`extra_info` varchar(100) NOT NULL default '',
`access_level` tinyint(4) NOT NULL default '0',
`registerDate` datetime NOT NULL default '-00-00 00:00:00',
`lastvisitDate` datetime NOT NULL default '-00-00 00:00:00',
ID int(5) NOT NULL auto_increment,
UNIQUE KEY ID (ID),
unique (username),
KEY `usertype` (`usertype`)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
update UserTBL set gid='25', usertype='Super Administrator11' where
username='ADmin';
update UserTBL set gid='25', usertype='Super Administrator11' where
username='admin';
'username' = 'admin' in the tabls...
thanks
-bruce
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update row/tbl question...
In the last episode (Oct 14), bruce said: a basic/new/curious question. i have the following sample tbl schema. i do the following update on the mysql command line and it updates for both cases. my question is why??? update UserTBL set gid='25', usertype='Super Administrator11' where username='ADmin'; update UserTBL set gid='25', usertype='Super Administrator11' where username='admin'; 'username' = 'admin' in the tabls... Text fields are compared case-insensitively. http://dev.mysql.com/doc/refman/4.1/en/case-sensitivity.html -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default table type = innodb is stable??
Are you using the my-innodb-heavy-4G.cnf file for your machine? i loaded the cnf file to /etc/my.cnf. then, it fails to start. what's wrong with it? mind to tell me on how to set the default table type equal to innodb?perhaps, can you guide me for cnf configuration for a 6G memory big machine for mysql? thanks again Ady Wicaksono wrote: Currently i have 4 Gbyte RAM and RH 9 Linux using InnoDB and yes, it's stable :) Hiu Yen Onn wrote: hi, i have a machine with 6G memory, loaded with RHEL4. now, my question is mysql support innodb as the default table type? will be stable running RHEL4? i saw /usr/share/mysql/my-innodb-heavy-4G.cnf file, then, i replace the existing one (/etc/my.cnf). but, it failed to start the mysql server. please advise. thanks? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default table type = innodb is stable??
hereby, i attached the my.cnf file. by default, i copied it from /usr/share/mysql/my-huge.cnf. however, i have added default table type = INNODB. from the my.cnf, i can see that there are flags for innodb table, starting from # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 384M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 100M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 if i uncomment it, then mysql server wont start at all. if i comment it, then, that's ok. please advise. thanks again Hiu Yen Onn wrote: Are you using the my-innodb-heavy-4G.cnf file for your machine? i loaded the cnf file to /etc/my.cnf. then, it fails to start. what's wrong with it? mind to tell me on how to set the default table type equal to innodb?perhaps, can you guide me for cnf configuration for a 6G memory big machine for mysql? thanks again Ady Wicaksono wrote: Currently i have 4 Gbyte RAM and RH 9 Linux using InnoDB and yes, it's stable :) Hiu Yen Onn wrote: hi, i have a machine with 6G memory, loaded with RHEL4. now, my question is mysql support innodb as the default table type? will be stable running RHEL4? i saw /usr/share/mysql/my-innodb-heavy-4G.cnf file, then, i replace the existing one (/etc/my.cnf). but, it failed to start the mysql server. please advise. thanks? # Example MySQL config file for very large systems. # # This is for a large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the --help option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] default-table-type = INNODB port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id
