Re: How to define utf8 function
Hi, Hello. This should be fixed in 5.0.18. See: http://bugs.mysql.com/bug.php?id=13909 17.2.1. CREATE PROCEDURE and CREATE FUNCTION http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html says ... - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - As of MySQL 5.0.18, the server uses the data type of a routine parameter or function return value as follows. These rules also apply to local routine variables created with the DECLARE statement (Section 17.2.9.1, “DECLARE Local Variables”). * Assignments are checked for data type mismatches and overflow. Conversion and overflow problems result in warnings, or errors in strict mode. * For character data types, if there is a CHARACTER SET clause in the declaration, the specified character set and its default collation are used. If there is no such clause, the database character set and collation are used. (These are given by the values of the character_set_database and collation_database system variables.) * Only scalar values can be assigned to parameters or variables. For example, a statement such as SET x = (SELECT 1, 2) is invalid. Before MySQL 5.0.18, parameters, return values, and local variables are treated as items in expressions, and are subject to automatic (silent) conversion and truncation. Stored functions ignore the sql_mode setting. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I thought that this explanation means function controls CHARACTER SET clause properly from 5.0.18. So, I checked on version 5.0.18, but situation is same mysql SELECT VERSION(); +-+ | VERSION() | +-+ | 5.0.18-standard-log | +-+ 1 row in set (0.01 sec) mysql SET NAMES utf8; Query OK, 0 rows affected (0.00 sec) ## I tried to make function tokyo() which returns string 'Tokyo' in ## Japanase. _utf8 X'E69DB1E4BAAC' means Tokyo in Japanese. mysql DELIMITER // mysql CREATE FUNCTION tokyo() RETURNS VARCHAR(20) CHARACTER SET utf8 - DETERMINISTIC RETURN _utf8 X'E69DB1E4BAAC'; - // Query OK, 0 rows affected (0.00 sec) mysql DELIMITER ; mysql SELECT tokyo(); +-+ | tokyo() | +-+ | ?? | +-+ 1 row in set, 1 warning (0.00 sec) mysql SELECT HEX(tokyo()); +--+ | HEX(tokyo()) | +--+ | 3F3F | +--+ 1 row in set, 1 warning (0.00 sec) mysql SELECT CHARSET(tokyo()); +--+ | CHARSET(tokyo()) | +--+ | binary | +--+ 1 row in set (0.00 sec) mysql SHOW CREATE FUNCTION tokyo\G *** 1. row *** Function: tokyo sql_mode: Create Function: CREATE FUNCTION `tokyo`() RETURNS varchar(20) DETERMINISTIC RETURN _utf8 X'E69DB1E4BAAC' 1 row in set (0.00 sec) mysql Returned charater type of function is binary. And, SHOW CREATE FUNCTION removed CHARACTER SET utf8 part. Why? Hirofumi Fujiwara[EMAIL PROTECTED], [EMAIL PROTECTED] Time Intermedia Corporationhttp://www.timedia.co.jp/ Corporate Strategy Department Knowledge Engineering Center 26-27 Saka-machi Shinjuku-ku, Tokyo 160-0002 Japan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myPhpAdmin
This is perhaps a bit off-topic, but... I tried to install and configurate phpAdmin on an internal web-server. I copied a default config file and created my own config.inc.php file. In this I set $cfg['Servers'][$i]['host'] = 'sql.dahl-stamnes.net'; a 'host sql.dahl-stamnes.net' shows that it is an alias pointing to another machine where the mySQL server is running. However, when trying to access myPhpAdmin, I get the following error: Error MySQL said: Documentation #1045 - Access denied for user 'quest'@'r2d2.dahl-stamnes.net' (using password: NO) It seems like the host name given in the config file is ignored and that it try to connect to the host where the web-server is running. It should not be like this, should it? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting utf-8 data problems
Dave, what is the result of prompt set | grep LANG ? I suspect your problem is not within MySQL. Did you look at your testfile using a editor? Thomas Spahni On Wed, 4 Jan 2006, Dave M G wrote: MySQL List, I have recently switched over from Windows to Ubuntu Linux, in order to emulate as much as possible the environment I have on my web hosting service. The goal is to be able to develop and test my web sites more completely at home before uploading them. I have successfully installed Apache, PHP, and MySQL (Most of which came by default when installing Ubuntu). I'm comfortable writing PHP and MySQL code in a web page, but I am very much a beginner in MySQL set up and maintenance. I have all the PHP and HTML files downloaded, and now my next step is to copy the databases from my web hosting service to my home machine. A lot of my database data is bilingual, English and Japanese. I try at every turn to store and retrieve all data in UTF-8 format. Using phpMyAdmin on my virtual hosting service, I exported my database information to a text file, which I then opened on my local machine, again through the phpMyAdmin interface. It mostly worked. All the tables and their contents were inserted into the home version of the database. However, when viewing the web pages where content is dynamically called from the database, all the Japanese text appears on my home machine as a series of question marks. So far as I know, I selected to use utf-8 encoding at every available opportunity. I'm wondering if the problems came when saving to a plain text file. Can anyone recommend the best way to preserve text encoding methods when copying a database from one machine to another? Any advice is much appreciated. Thank you. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: myPhpAdmin
Jørn Dahl-Stamnes wrote:
This is perhaps a bit off-topic, but...
I tried to install and configurate phpAdmin on an
internal web-server. I copied a default config file and
created my own config.inc.php file. In this I set
$cfg['Servers'][$i]['host'] =
'sql.dahl-stamnes.net';
a 'host sql.dahl-stamnes.net' shows that it is an alias
pointing to another machine where the mySQL server is
running.
However, when trying to access myPhpAdmin, I get the
following error:
Error
MySQL said: Documentation
#1045 - Access denied for user
'quest'@'r2d2.dahl-stamnes.net' (using password: NO)
It seems like the host name given in the config file is
ignored and that it try to connect to the host where the
web-server is running.
It should not be like this, should it?
Actually, yes. The error indicates that the user ('quest') you have defined
in your config file, has not been authorized to access the database from
your host 'r2d2.dahl-stamnes.net'
If you have access to a mysql console on the mysql host, try this:
GRANT ALL PRIVILEGES ON database name.* TO 'quest'@'r2d2.dahl-stamnes.net'
IDENTIFIED BY 'somepassword';
FLUSH PRIVILEGES;
Remember to set the password in the config file accordingly...
Regards,
Peter Normann
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database slows down when mass users logging on
Hi Alex Thanks for this, and sorry for the late reply. That was the entire my.cnf file in my last email. This is from SHOW VARIABLES in mysql. +-+--+ | Variable_name | Value| +-+--+ | auto_increment_increment| 1| | auto_increment_offset | 1| | automatic_sp_privileges | ON | | back_log| 50 | | basedir | /| | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection| latin1_swedish_ci| | collation_database | latin1_swedish_ci| | collation_server| latin1_swedish_ci| | completion_type | 0| | concurrent_insert | 1| | connect_timeout | 5| | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s| | default_week_format | 0| | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4| | engine_condition_pushdown | OFF | | expire_logs_days| 0| | flush | OFF | | flush_time | 0| | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4| | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024 | | have_archive| YES | | have_bdb| NO | | have_blackhole_engine | NO | | have_compress | YES | | have_crypt | YES | | have_csv| NO | | have_example_engine | NO | | have_federated_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam | NO | | have_ndbcluster | NO | | have_openssl| NO | | have_query_cache| YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink| YES | | init_connect| | | init_file | | | init_slave |
Re: Database slows down when mass users logging on
Sorry I also forgot to ask - when you say the tables, did you want the table structure? There are quite a few tables, perhaps I should just send the tables that are read and written to often when a user hits a page? On 04/01/06, Alex [EMAIL PROTECTED] wrote: Hi, Please provide details like what tables are you using, the entire my.cnf and the information from the mysqld.err when the crashes occurred. --Alex -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about sql security
Hello. In my opinion, a good description can be found here: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html wangxu [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]
Re: Exporting utf-8 data problems
Hello. However, when viewing the web pages where content is dynamically called from the database, all the Japanese text appears on my home machine as a series of question marks. You should localize the problem. Check with the text editor that files, which you have got from phpMyAdmin really contain data in UTF8 encoding. Do not use phpAdmin until you completely understand what's going on. Use mysql command line client to import your databases. After you have done the import, check with some client that the data in MySQL is still correct and in UTF8 (use GUIs like MySQL Query Browser in case your terminal doesn't support unicode, and you can't use mysql command line client). Check the settings of you server with: show variables like '%char%'; statement. Read: http://dev.mysql.com/doc/refman/5.0/en/charset.html Dave M G wrote: MySQL List, I have recently switched over from Windows to Ubuntu Linux, in order to emulate as much as possible the environment I have on my web hosting service. The goal is to be able to develop and test my web sites more completely at home before uploading them. I have successfully installed Apache, PHP, and MySQL (Most of which came by default when installing Ubuntu). I'm comfortable writing PHP and MySQL code in a web page, but I am very much a beginner in MySQL set up and maintenance. I have all the PHP and HTML files downloaded, and now my next step is to copy the databases from my web hosting service to my home machine. A lot of my database data is bilingual, English and Japanese. I try at every turn to store and retrieve all data in UTF-8 format. Using phpMyAdmin on my virtual hosting service, I exported my database information to a text file, which I then opened on my local machine, again through the phpMyAdmin interface. It mostly worked. All the tables and their contents were inserted into the home version of the database. However, when viewing the web pages where content is dynamically called from the database, all the Japanese text appears on my home machine as a series of question marks. So far as I know, I selected to use utf-8 encoding at every available opportunity. I'm wondering if the problems came when saving to a plain text file. Can anyone recommend the best way to preserve text encoding methods when copying a database from one machine to another? Any advice is much appreciated. Thank you. -- Dave M G -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away ??
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Any reason why this happens? Should I be worried?=20 You have an old MySQL version (4.1.8), upgrade to the latest release. Jørn Dahl-Stamnes wrote: I have been using mysql client for some time when I got this error: mysql select * from mytable; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... ERROR 1045 (28000): Access denied for user 'username'@'my.ip.addr' (usi= ng=20 password: YES) ERROR: Can't connect to the server mysql quit Bye $ mysql -h sql -u sqluser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6669 to server version: 4.1.8-standard MySQL is running on a server and I have to go through a firewall to reach i= t. Any reason why this happens? Should I be worried?=20 =2D-=20 J=F8rn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- 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: Database slows down when mass users logging on
Hi, Please provide the log extracts from /var/log/mysql_error.log in your box when the crashes occurred and also you havent mentioned the table types thats you are using. MyISAM tables are the deafult for your configuration. This table type is not recommeded for highly concurrent usage. If you are using this table type increase the key_buffer_size. If you are using innodb tables increase Innodb_buffer_pool_size, it is set to a very low 8MB. Please follow the link http://dev.mysql.com/doc/refman/5.1/en/server-parameters.html. Pay attention to the last 4 paragraphs before user comments. Hope this helps. Thanx Alex On Wed, 04 Jan 2006 15:25:39 +0530, Jonathan Chong [EMAIL PROTECTED] wrote: Hi Alex Thanks for this, and sorry for the late reply. That was the entire my.cnf file in my last email. This is from SHOW VARIABLES in mysql. +-+--+ | Variable_name | Value| +-+--+ | auto_increment_increment| 1| | auto_increment_offset | 1| | automatic_sp_privileges | ON | | back_log| 50 | | basedir | /| | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection| latin1_swedish_ci| | collation_database | latin1_swedish_ci| | collation_server| latin1_swedish_ci| | completion_type | 0| | concurrent_insert | 1| | connect_timeout | 5| | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s| | default_week_format | 0| | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | div_precision_increment | 4| | engine_condition_pushdown | OFF | | expire_logs_days| 0| | flush | OFF | | flush_time | 0| | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4| | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024 | | have_archive| YES | | have_bdb| NO | | have_blackhole_engine | NO | | have_compress | YES | | have_crypt | YES | | have_csv| NO | | have_example_engine | NO | | have_federated_engine | NO | | have_geometry | YES
Re: Database slows down when mass users logging on
the table type whether it is myisam or innodb. create table statements are preferred when you have slow query issues. --Alex On Wed, 04 Jan 2006 15:57:06 +0530, Jonathan Chong [EMAIL PROTECTED] wrote: Sorry I also forgot to ask - when you say the tables, did you want the table structure? There are quite a few tables, perhaps I should just send the tables that are read and written to often when a user hits a page? On 04/01/06, Alex [EMAIL PROTECTED] wrote: Hi, Please provide details like what tables are you using, the entire my.cnf and the information from the mysqld.err when the crashes occurred. --Alex -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about sql security
I refer a question about sql security option of create procedure syntax at 2005-12. But i can't quite understood with that answer. Can you give me a example to describe the effect of set sql security option ? CREATE PROCEDURE ... SQL SECURITY INVOKER ... SQL SECURITY (compliance with SQL2003) specifies whether the user privileges of the author (DEFINER) or the INVOKER apply; the default is DEFINER. Since 5.0.3, MySQL has supported GRANTs for CREATE, ALTER and EXECUTE. The first includes the latter two. If binary logging in enabled, see http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html. PB - -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.11/219 - Release Date: 1/2/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: logging issue
I'm inclined to think this is a database config issue in MailScanner. Double check the Port setting that MailScanner is using and check that against the port that your MySQL server binds to. It is normally 3306, and they should match. If you don't know how to check, can you send me the output of: netstat -tan | grep 3306 Your log output shows an error code of 110 which means 'Connection timed out'. This is different to 'Connection refused'. Is there a possibility of very high database activity from another source while MailScanner is trying to get at the database? How you tried invoking the MailScanner connection at different times of the day? What interface does MailScanner use to connect? It looks like Perl DBI, but you may be using ODBC? Finally, can you tell me your perl version by: perl -v Imran Chaudhry -- http://www.EjectDisc.com Get your Digital Identity - Domain Names, Web Space, E-mail More! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database slows down when mass users logging on
Hi Alex There are over a hundred tables for the site, and those that are related to this query are probably about 15-30. Do you want the CREATE TABLE syntax for all of them? Funnily enough, the file /var/log/mysql_error.log doesn't exist .. I'm using myISAM tables, yes. On 04/01/06, Alex [EMAIL PROTECTED] wrote: the table type whether it is myisam or innodb. create table statements are preferred when you have slow query issues. --Alex On Wed, 04 Jan 2006 15:57:06 +0530, Jonathan Chong [EMAIL PROTECTED] wrote: Sorry I also forgot to ask - when you say the tables, did you want the table structure? There are quite a few tables, perhaps I should just send the tables that are read and written to often when a user hits a page? On 04/01/06, Alex [EMAIL PROTECTED] wrote: Hi, Please provide details like what tables are you using, the entire my.cnf and the information from the mysqld.err when the crashes occurred. --Alex -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost Connection executing query
What are your wait_timeout and/or interactive_timeout values set to? Does the server crash and auto restart? Check server's up time. Do both servers have the exact table schema? Same column datatypes and indexes to be specific. Although your data volumn may be similar, can the actual data be problamatic? Can you rewrite the UPDATE statement as a SELECT statement to see if you can target the rows you are expecting to target? You can check section A.2.8. MySQL server has gone away in the online manual, which also covers your message, for list of things to try. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Ed -Original Message- From: Tripp Bishop [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Lost Connection executing query Howdy all, First off, We're running 5.0.15. Theres a particular update statement that we run that updates data in several tables. On our mac OSX test server (also running 5.0.15) the query executes quickly and without any errors or warnings. On our linux box, which is our production box, we get the following error as soon as the query is executed: ERROR 2013 (HY000): Lost connection to MySQL server during query The databases have a similar amount of data in them. I've googled on the error but mostly get pages containing questions about the error when generated by stored procedures and mostly on 5.0.3. We're not using stored procedures. This is just a straight-up query. Here's the query: UPDATE customer_indicator INNER JOIN customer_search_pref ON customer_search_pref.customer_id = customer_indicator.customer_id AND customer_search_pref.office_id = customer_indicator.office_id LEFT JOIN contact_log ON contact_log.customer_id = customer_indicator.customer_id LEFT JOIN sent ON sent.pref_id = customer_search_pref.pref_id SET customer_indicator.office_id = 33, customer_search_pref.office_id =33, customer_indicator.agent_id = 105, sent.office_id = 33, contact_log.office_id = 33, customer_indicator.next_message_id = 4403 WHERE customer_indicator.customer_id = 78437 AND customer_indicator.office_id = 34; The approximate sizes of the tables involved are: customer_indicator: 40K records customer_search_pref: 45K contact_log: 390K sent: 20M (million) So my question is, what are some possible causes of this error? Why would trying to execute this query cause the connection to be lost? Why would the query work fine on our mac system and fail on the prodcution box? Thanks, Tripp __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.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]
BIT datatype and trying to use it: data too long error. Could this be a bug?
Hi there, With the help of the people at CoreLab, we found out this problem: After long testing we detected source of the problem. It's STRICT_TRANS_TABLES flag in sql-mode my.ini variable. This variable affects only CREATE TABLE and CREATE PROCEDURE statements. Even if you simplify script to create server objects to DROP TABLE IF EXISTS newtab; CREATE TABLE newtab ( b BIT ) ENGINE=MYISAM ROW_FORMAT=FIXED CHARACTER SET latin1 COLLATE latin1_swedish_ci; DROP PROCEDURE IF EXISTS newtab_I; CREATE PROCEDURE newtab_I(IN b BIT) BEGIN INSERT INTO newtab (b) VALUES (b); END; you still get the error. We think, this is MySQL server problem. To check this hypothesis you can call 'CALL newtab_I(1)' statement from mysql.exe command line utility. Trying to call newtab_I with (1) for the BIT parameter will result into: Data too long for column 'b' at row 1 If this is as designed, how should one use this particular datatype? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calendar table workaround
I created a calendar table (date only), but all where clauses include a uid. Is the following a sane workaround to get a usable calendar table? Anything else I can do? my $sth = $dbh-prepare( create table $temp_tbl (date date, uid varchar(14)) select date, ? as uid from calendar where date between ? and ?); $sth-execute($uid, $bdate, $edate); Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting utf-8 data problems
Thank you for the advice. In order to isolate the problem, I have checked to see if the text file that I have exported from the MySQL server on my virtual host is, in fact, in UTF-8. By opening it in OpenOffice and selecting UTF-8 encoding, it displays correctly. Not entirely without problems. Most of the Japanese text shows up correctly. But about 10% of it shows like this: #12503;#12523;#12539;#12522;#12540;#12501;#12391; I believe this is because there is probably some Japanese text that was entered in sometime before the server upgraded MySQL to 4.1. But, ignoring the 10% of legacy text, it seems that at the very least, I do have a proper UTF-8 encoded text file with which to import into my home MySQL server. Instead of importing the data as an SQL file (which successfully imported, but with faulty Japanese characters), I copied the text and pasted them in as a straight SQL query. But it returns an error. Can anyone enlighten me as to why the file would import into SQL as an SQL file, but the text won't work as an import statement? Here is the error output: SQL query: # phpMyAdmin MySQL-Dump # version 2.3.3pl1 # http://www.phpmyadmin.net/ (download page) # # Host: localhost # Generation Time: Jan 04, 2006 at 10:04 AM # Server version: 3.23.37 # PHP Version: 4.3.11 # Database : `signup` # # # Table structure for table `event_groups` # CREATE TABLE event_groups( egid int( 11 ) NOT NULL AUTO_INCREMENT , GROUP int( 11 ) NOT NULL default '0', event int( 11 ) NOT NULL default '0', PRIMARY KEY ( egid ) ) TYPE = MYISAM MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group int(11) NOT NULL default '0', event int(11) NOT NULL de (the error message cuts abruptly, as shown here) Any advice would be much appreciated. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Exporting utf-8 data problems
dave I believe group is a reserved word. change to grp. david -Original Message- From: Dave M G [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 8:55 AM To: mysql@lists.mysql.com Subject: Re: Exporting utf-8 data problems Thank you for the advice. In order to isolate the problem, I have checked to see if the text file that I have exported from the MySQL server on my virtual host is, in fact, in UTF-8. By opening it in OpenOffice and selecting UTF-8 encoding, it displays correctly. Not entirely without problems. Most of the Japanese text shows up correctly. But about 10% of it shows like this: #12503;#12523;#12539;#12522;#12540;#12501;#12391; I believe this is because there is probably some Japanese text that was entered in sometime before the server upgraded MySQL to 4.1. But, ignoring the 10% of legacy text, it seems that at the very least, I do have a proper UTF-8 encoded text file with which to import into my home MySQL server. Instead of importing the data as an SQL file (which successfully imported, but with faulty Japanese characters), I copied the text and pasted them in as a straight SQL query. But it returns an error. Can anyone enlighten me as to why the file would import into SQL as an SQL file, but the text won't work as an import statement? Here is the error output: SQL query: # phpMyAdmin MySQL-Dump # version 2.3.3pl1 # http://www.phpmyadmin.net/ (download page) # # Host: localhost # Generation Time: Jan 04, 2006 at 10:04 AM # Server version: 3.23.37 # PHP Version: 4.3.11 # Database : `signup` # # # Table structure for table `event_groups` # CREATE TABLE event_groups( egid int( 11 ) NOT NULL AUTO_INCREMENT , GROUP int( 11 ) NOT NULL default '0', event int( 11 ) NOT NULL default '0', PRIMARY KEY ( egid ) ) TYPE = MYISAM MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group int(11) NOT NULL default '0', event int(11) NOT NULL de (the error message cuts abruptly, as shown here) Any advice would be much appreciated. -- Dave M G -- 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: Are these db stats normal...part 2
I've also checked the error log and there isn't anything that indicates where these aborted_client connections are coming from. I checked today and I have 100 more of these. -James
Re: Database slows down when mass users logging on
I would suggest looking at the slow query log first and figure out what queries were taking long when the crash occurs at heavy concurrent usage, and then try to optimize those queries, it might a world of difference, also since you have only 1GB ram , try decreasing the sort_buffer_size, read_buffer_size to like 500K instead of the default 2M Kishore Jalleda On 1/4/06, Jonathan Chong [EMAIL PROTECTED] wrote: Hi Alex There are over a hundred tables for the site, and those that are related to this query are probably about 15-30. Do you want the CREATE TABLE syntax for all of them? Funnily enough, the file /var/log/mysql_error.log doesn't exist .. I'm using myISAM tables, yes. On 04/01/06, Alex [EMAIL PROTECTED] wrote: the table type whether it is myisam or innodb. create table statements are preferred when you have slow query issues. --Alex On Wed, 04 Jan 2006 15:57:06 +0530, Jonathan Chong [EMAIL PROTECTED] wrote: Sorry I also forgot to ask - when you say the tables, did you want the table structure? There are quite a few tables, perhaps I should just send the tables that are read and written to often when a user hits a page? On 04/01/06, Alex [EMAIL PROTECTED] wrote: Hi, Please provide details like what tables are you using, the entire my.cnf and the information from the mysqld.err when the crashes occurred. --Alex -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.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]
Re: Which Engine?
On Tuesday, January 3, 2006 1711, [EMAIL PROTECTED] wrote: You are going to need to setup your own application-level locking scheme and rollback procedures if you don't want to use or cannot use the locking and transaction support built into InnoDb. Well, I WANT to use InnoDB, but I guess that I'll have to roll my own scheme to handle the (relatively few) inserts that I need on the MySQL tables. For the tables that do not support transactions (because they are MyISAM or something else) you will probably need to take snapshots of the initial state of each table, ensuring that no other process tries to update or insert to that table while you have it snap-shotted (or you will invalidate the OTHER process should you need to roll back). The table-wide locks are generally good enough for that kind of protection but they are performance killers if you have any sort of concurrency or if your transaction takes too long. It shouldn't take very long - just enough to insert a new user and the corresponding privileges. Also, new users should be relatively infrequent once we get the initial data loaded. I would strongly recommend NOT using the mysql tables for your application's security needs. I would roll my own application-level permission tables and use them to control access through the front end. My tentative plan is to have my own security levels (none, guest, user and admin) enforced by logic in my application, but I also want to restrict privileges to the minimum necessary at the database level. Generally, my end users do not get direct read-write access to the tables behind any application. They may get read-only access but that's through their own accounts, not the accounts I use to access the DB with from the application itself. I'm not sure that I understand. Are you saying that all users connect from your application using a small set of access accounts (known to MySQL) and then you use your own tables to look up the actual permissions for each person (not known to MySQL)? If you still need to create user accounts on the fly, stick with the GRANT and REVOKE statements and do that part of the processing either first or last (outside of your other transactions) That way you can know for sure if you got the account changed or not either before you start the transaction or just before you commit or rollback. That was the plan. Lookup and be aware that certain commands contain an implicit COMMIT when they are executed so your transaction may end earlier than you planned if you use one of those commands. These are usually data definition statements (ALTER TABLE, etc) but it's better for you if you know them all. Good point. I knew that some commands issue an implicit COMMIT, but hadn't thought to check all of them. Sorry I can't be more specific but it's time to blast and dinner is waiting. Thanks for the suggestions. Cheers! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- John Hoover [EMAIL PROTECTED] 301-890-6932 (H) 202-767-2335 (W) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting utf-8 data problems
Hi, Maybe your problem is linked to phpmyadmin? I was having issues when trying to use an 'old' version (2.5.7) because the interface itself of phpmyadmin was set up as iso-8859-1, and that was causing all my data to be entered as isolatin even if the tables were declared as utf8. By updating to 2.6.4 I've been able to set up the language in phpmyadmin as en-utf-8, and now when I use this interface the data are actually utf8 in the tables. hth, melanie From: Dave M G [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: Exporting utf-8 data problems Date: Thu, 05 Jan 2006 00:54:44 +0900 Thank you for the advice. In order to isolate the problem, I have checked to see if the text file that I have exported from the MySQL server on my virtual host is, in fact, in UTF-8. By opening it in OpenOffice and selecting UTF-8 encoding, it displays correctly. Not entirely without problems. Most of the Japanese text shows up correctly. But about 10% of it shows like this: #12503;#12523;#12539;#12522;#12540;#12501;#12391; I believe this is because there is probably some Japanese text that was entered in sometime before the server upgraded MySQL to 4.1. But, ignoring the 10% of legacy text, it seems that at the very least, I do have a proper UTF-8 encoded text file with which to import into my home MySQL server. Instead of importing the data as an SQL file (which successfully imported, but with faulty Japanese characters), I copied the text and pasted them in as a straight SQL query. But it returns an error. Can anyone enlighten me as to why the file would import into SQL as an SQL file, but the text won't work as an import statement? Here is the error output: SQL query: # phpMyAdmin MySQL-Dump # version 2.3.3pl1 # http://www.phpmyadmin.net/ (download page) # # Host: localhost # Generation Time: Jan 04, 2006 at 10:04 AM # Server version: 3.23.37 # PHP Version: 4.3.11 # Database : `signup` # # # Table structure for table `event_groups` # CREATE TABLE event_groups( egid int( 11 ) NOT NULL AUTO_INCREMENT , GROUP int( 11 ) NOT NULL default '0', event int( 11 ) NOT NULL default '0', PRIMARY KEY ( egid ) ) TYPE = MYISAM MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group int(11) NOT NULL default '0', event int(11) NOT NULL de (the error message cuts abruptly, as shown here) Any advice would be much appreciated. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ The new MSN Search Toolbar now includes Desktop search! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 64bit static build?
Hello All! I have noticed that MySQL does not distribute a static linked binary of MySQL x86_64. I ran into the pthread_rwlock_wrlock hang with nptl bug on an earlier install of Fedora Core 2. The MySQL binary version I am using now is mysql-pro-4.0.26-unknown-linux-gnu-x86_64-glibc23.tar.gz which is dynamically linked, by statically linking the binary on a system with the latest patches apply to NPTL I could just install the binary instead of upgrading to Fedora Core 4. So, my questions are: 1) Is there a reason for not distributing static linked binary for 64bin platforms? 2) I am looking at compiling MySQL myself for an Intel Xeon EM64T box using pgcc, any insight on compiler options to get the best performance? Thank you all for you help! Regards, --Dave David W. Juntgen Medical Informatics Engineering Inc. Phone: 260.459.6270 Fax: 260.459.6271
RE: Lost Connection executing query
The query fails instantly so I don't think it's a timeout issue. The wait_timeout and interactive_timeout variables are set to 28800. The server seems to be crashing and auto restarting because as you suggested the uptime is small when I do a show status right after attempting the query. The schemas are identical and most of the data is the same. When I try to rewrite the update as a select I get an impossible where clause when I do an explain on the select. It can't be a max packet issue because the actual query is really small. The query runs fine on the MAC and takes about 1 second to run. I could break this update statement up into 4 seperate update statements but I'd prefer to keep it as one. I did notice that the MySQL manual suggests running CHECK TABLE on the table(s) involved but no other queries that operate against these tables seem to be having trouble so it seems unlikely that table corruption would be a problem. We did recently upgrade the server from 4.0.40 to 5.0.15 and we did not dump the tables and reimport them. On the MAC we did do a dump and reimport. I wonder if that could be the cause of this problem. I had forgetten about that important difference. Cheers, Tripp --- [EMAIL PROTECTED] wrote: What are your wait_timeout and/or interactive_timeout values set to? Does the server crash and auto restart? Check server's up time. Do both servers have the exact table schema? Same column datatypes and indexes to be specific. Although your data volumn may be similar, can the actual data be problamatic? Can you rewrite the UPDATE statement as a SELECT statement to see if you can target the rows you are expecting to target? You can check section A.2.8. MySQL server has gone away in the online manual, which also covers your message, for list of things to try. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Ed -Original Message- From: Tripp Bishop [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Lost Connection executing query Howdy all, First off, We're running 5.0.15. Theres a particular update statement that we run that updates data in several tables. On our mac OSX test server (also running 5.0.15) the query executes quickly and without any errors or warnings. On our linux box, which is our production box, we get the following error as soon as the query is executed: ERROR 2013 (HY000): Lost connection to MySQL server during query The databases have a similar amount of data in them. I've googled on the error but mostly get pages containing questions about the error when generated by stored procedures and mostly on 5.0.3. We're not using stored procedures. This is just a straight-up query. Here's the query: UPDATE customer_indicator INNER JOIN customer_search_pref ON customer_search_pref.customer_id = customer_indicator.customer_id AND customer_search_pref.office_id = customer_indicator.office_id LEFT JOIN contact_log ON contact_log.customer_id = customer_indicator.customer_id LEFT JOIN sent ON sent.pref_id = customer_search_pref.pref_id SET customer_indicator.office_id = 33, customer_search_pref.office_id =33, customer_indicator.agent_id = 105, sent.office_id = 33, contact_log.office_id = 33, customer_indicator.next_message_id = 4403 WHERE customer_indicator.customer_id = 78437 AND customer_indicator.office_id = 34; The approximate sizes of the tables involved are: customer_indicator: 40K records customer_search_pref: 45K contact_log: 390K sent: 20M (million) So my question is, what are some possible causes of this error? Why would trying to execute this query cause the connection to be lost? Why would the query work fine on our mac system and fail on the prodcution box? Thanks, Tripp __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which Engine?
John Hoover wrote: I need some advice re my choice of a storage engine for transaction-safe processing and including tables that are not transaction-safe within transactions. The problem: We need to insert related records into several different tables and be sure that all the insertions succeeded. It seems that transactions are the recommended way of achieving this and I was thinking of using InnoDB tables. However, I'm not sure if that is the best engine to use - can anyone give me reasons for selecting a specific transaction-safe engine? You can use BDB or InnoDB - InnoDB provides row level locking, BDB provides page level locking. I'd say InnoDB is the way to go (usually). Also, some of my insertions will involve the mySQL tables (creation of a new user and granting privileges, for example). According to the manuals, those tables use the myISAM engine and can not be changed to any other engine. What is the best way to handle insertion errors on myISAM tables? I had planned to test for an error after each operation and, if one occurred, manually undo whatever previous operations had already succeeded. That's a lot of work if the operation involves multiple tables and I'd like to know if there is a better alternative. Finally, if I do handle errors manually, what should I do if there is an error in the error handler? For example, suppose I've inserted one record and then an error prevents insertion of the related record so that I have to delete the previously inserted record. Is it possible for the delete to fail? If so, I'll have a partial transaction that can't be completed and can't be undone - what should I do to clean up? What are your insertion operations? Typically, you would use GRANT statements to add users the these tables - and those statements (if they fail) won't do any GRANTing. I wouldn't grant access using insert statements - you'll be flushing your privilege tables regularly. Unless you are using the Host table, I'd recommend you do the following: 1. Prior to modifying a user, use the 'show grants' statement to find out what access the user has - store that. 2. Perform all your GRANT operations. 3. If a single operation fails, remove the user and execute the stored access (from step 1) for the user to restore his/her access. - if the user didn't already exist, just remove all their access. For users that won't connect to the database directly, you probably don't want to create individual accounts - as if the user connects directly they can perform operations outside the bounds of your application (where you may implement business logic). Assuming you stick with grant statements, it shouldn't be too difficult to maintain integrity when you want to do your pseudo-transactions. Use InnoDB everywhere else. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ISAM tables broken
An old server died during the holidays. I had a database with 2 tables on it on an old version of MySQL (circa Redhat 7?) I have the latest backup of the files but not a dump (kick himself) I put the files in the correct /var/lib/mysql/gallery and restarted MySQL. It shows the gallery database but not the tables I have tried ISAM check and it seems OK but MySQL won't open the tables. I have even tried the files in a fresh compiled MySQL rather than the FC4 version in case that's a problem. Are my tables doomed? TIA Simon PS they are at http://titanic.co.uk/gallery/tblGallery.ISD http://titanic.co.uk/gallery/tblGallery.ISM http://titanic.co.uk/gallery/tblPicture.ISD http://titanic.co.uk/gallery/tblPicture.ISM if anyone needs to see them... There's nothing private in them. [EMAIL PROTECTED] gallery]# ls -l total 1196 -rwxrwxrwx 1 mysql mysql6511 Jan 4 15:30 tblGallery.ISD -rwxrwxrwx 1 mysql mysql2048 Jan 4 15:30 tblGallery.ISM -rwxrwxrwx 1 mysql mysql 1185360 Jan 4 15:30 tblPicture.ISD -rwxrwxrwx 1 mysql mysql 18432 Jan 4 15:30 tblPicture.ISM [EMAIL PROTECTED] gallery]# mysqlshow +---+ | Databases | +---+ | gallery | | mysql | | test | +---+ [EMAIL PROTECTED] gallery]# mysqlshow gallery Database: gallery ++ | Tables | ++ ++ [EMAIL PROTECTED] gallery]# isam isamchk isamlog [EMAIL PROTECTED] gallery]# isamchk tblGallery.ISD isamchk: error: 'tblGallery.ISD' is not a ISAM-table [EMAIL PROTECTED] gallery]# isamchk tblGallery.ISM Checking ISAM file: tblGallery.ISM Data records: 17 Deleted blocks: 0 - check file-size - check delete-chain - check index reference [EMAIL PROTECTED] gallery]# isamchk tblPicture.ISD isamchk: error: 'tblPicture.ISD' is not a ISAM-table [EMAIL PROTECTED] gallery]# isamchk tblPicture.ISM Checking ISAM file: tblPicture.ISM Data records:2640 Deleted blocks: 0 - check file-size - check delete-chain - check index reference [EMAIL PROTECTED] gallery]# isamchk -r tblPicture.ISM - recovering ISAM-table 'tblPicture.ISM' Data records: 2640 - Fixing index 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Calendar table workaround
Oops, I meant to post this on the mailing list, not send it to the original poster. Rhino - Original Message - From: Rhino [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 1:25 PM Subject: Re: Calendar table workaround - Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 04, 2006 10:45 AM Subject: Calendar table workaround I created a calendar table (date only), but all where clauses include a uid. Is the following a sane workaround to get a usable calendar table? Anything else I can do? my $sth = $dbh-prepare( create table $temp_tbl (date date, uid varchar(14)) select date, ? as uid from calendar where date between ? and ?); $sth-execute($uid, $bdate, $edate); I have no idea what you are asking, which may explain why no one has replied to your question yet. I've been working with relational databases for 20 years and I've never heard the term calendar table. What are you trying to accomplish? If you describe clearly what you are trying to do, perhaps someone can help you devise a way to do it in MySQL. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.12/220 - Release Date: 03/01/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can someone tell me why this fails?
Can someone tell me why this fails? I'm using v4.1.11 Select IF(((Select 1+1) Null) and ((Select 1+1) 0), (Select 1+1), 'WRONG') I would expect a result of 2. - Thanks
Re: Calendar table workaround
- Original Message - From: Rhino [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 1:25 PM Subject: Re: Calendar table workaround - Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 04, 2006 10:45 AM Subject: Calendar table workaround I created a calendar table (date only), but all where clauses include a uid. Is the following a sane workaround to get a usable calendar table? Anything else I can do? my $sth = $dbh-prepare( create table $temp_tbl (date date, uid varchar(14)) select date, ? as uid from calendar where date between ? and ?); $sth-execute($uid, $bdate, $edate); I have no idea what you are asking, which may explain why no one has replied to your question yet. I've been working with relational databases for 20 years and I've never heard the term calendar table. What are you trying to accomplish? If you describe clearly what you are trying to do, perhaps someone can help you devise a way to do it in MySQL. Rhino A table of dates to which to join other tables, ensuring reports that reflect days for which no data is available. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost Connection executing query
Your import or CHECK TABLE sound like your best bets. Interesting about your SELECT statement conversion though, under the optimization section, it suggests you may still have a problem. Can you run your EXPLAIN SELECT on your MAC for comparison? 7.2.4. How MySQL Optimizes WHERE Clauses Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows. Ed -Original Message- From: Tripp Bishop [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 11:01 AM To: emierzwa; mysql@lists.mysql.com Subject: RE: Lost Connection executing query The query fails instantly so I don't think it's a timeout issue. The wait_timeout and interactive_timeout variables are set to 28800. The server seems to be crashing and auto restarting because as you suggested the uptime is small when I do a show status right after attempting the query. The schemas are identical and most of the data is the same. When I try to rewrite the update as a select I get an impossible where clause when I do an explain on the select. It can't be a max packet issue because the actual query is really small. The query runs fine on the MAC and takes about 1 second to run. I could break this update statement up into 4 seperate update statements but I'd prefer to keep it as one. I did notice that the MySQL manual suggests running CHECK TABLE on the table(s) involved but no other queries that operate against these tables seem to be having trouble so it seems unlikely that table corruption would be a problem. We did recently upgrade the server from 4.0.40 to 5.0.15 and we did not dump the tables and reimport them. On the MAC we did do a dump and reimport. I wonder if that could be the cause of this problem. I had forgetten about that important difference. Cheers, Tripp --- [EMAIL PROTECTED] wrote: What are your wait_timeout and/or interactive_timeout values set to? Does the server crash and auto restart? Check server's up time. Do both servers have the exact table schema? Same column datatypes and indexes to be specific. Although your data volumn may be similar, can the actual data be problamatic? Can you rewrite the UPDATE statement as a SELECT statement to see if you can target the rows you are expecting to target? You can check section A.2.8. MySQL server has gone away in the online manual, which also covers your message, for list of things to try. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Ed -Original Message- From: Tripp Bishop [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Lost Connection executing query Howdy all, First off, We're running 5.0.15. Theres a particular update statement that we run that updates data in several tables. On our mac OSX test server (also running 5.0.15) the query executes quickly and without any errors or warnings. On our linux box, which is our production box, we get the following error as soon as the query is executed: ERROR 2013 (HY000): Lost connection to MySQL server during query The databases have a similar amount of data in them. I've googled on the error but mostly get pages containing questions about the error when generated by stored procedures and mostly on 5.0.3. We're not using stored procedures. This is just a straight-up query. Here's the query: UPDATE customer_indicator INNER JOIN customer_search_pref ON customer_search_pref.customer_id = customer_indicator.customer_id AND customer_search_pref.office_id = customer_indicator.office_id LEFT JOIN contact_log ON contact_log.customer_id = customer_indicator.customer_id LEFT JOIN sent ON sent.pref_id = customer_search_pref.pref_id SET customer_indicator.office_id = 33, customer_search_pref.office_id =33, customer_indicator.agent_id = 105, sent.office_id = 33, contact_log.office_id = 33, customer_indicator.next_message_id = 4403 WHERE customer_indicator.customer_id = 78437 AND customer_indicator.office_id = 34; The approximate sizes of the tables involved are: customer_indicator: 40K records customer_search_pref: 45K contact_log: 390K sent: 20M (million) So my question is, what are some possible causes of this error? Why would trying to execute this query cause the connection to be lost? Why would the query work fine on our mac system and fail on the prodcution box? Thanks, Tripp __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! DSL - Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com -- MySQL General
RE: Calendar table workaround
I have no idea what you are asking, which may explain why no one has replied to your question yet. I've been working with relational databases for 20 years and I've never heard the term calendar table. What are you trying to accomplish? If you describe clearly what you are trying to do, perhaps someone can help you devise a way to do it in MySQL. Rhino A table of dates to which to join other tables, ensuring reports that reflect days for which no data is available. I forget the query but I know it can be done. But can't you just have a table (called calendar?) with each entry having it's own row with a date column that gives whatever date you need in it and then other columns for any other details you need to have to go along with the date. After you have a table full of dates you can just do a query that will grab all the information and display it like a calendar and show you days that have information and days that don't have anything. I'll see if I can't find that query for you and explain it to you. jay
Re: Can someone tell me why this fails?
Ed, Can someone tell me why this fails? I'm using v4.1.11 Select IF(((Select 1+1) Null) and ((Select 1+1) 0), (Select 1+1), 'WRONG') I would expect a result of 2. NULL is neither equal nor unequal to anything including itself. To get the result you expect, write Select IF(((Select 1+1) IS NOT Null) and ((Select 1+1) 0), (Select 1+1), 'WRONG'); PB No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.12/220 - Release Date: 1/3/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calendar table workaround
Jonathan,
I understand what you mean by a calendar table, but like Rhino I've no
idea
what you're asking. How to link the calendar table to other tables
depends on
your other tables. For a fairly simple generic example of a
calendar table see http://www.artfulsoftware.com/queries.php#20.
PB
-
Jonathan Mangin wrote:
- Original Message -
From: "Rhino" [EMAIL PROTECTED]
To: "Jonathan Mangin" [EMAIL PROTECTED]
Sent: Wednesday, January 04, 2006 1:25 PM
Subject: Re: Calendar table workaround
- Original Message -
From: "Jonathan Mangin" [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, January 04, 2006 10:45 AM
Subject: Calendar table workaround
I created a calendar table (date only), but all
where clauses include a uid. Is the following a
sane workaround to get a usable calendar table?
Anything else I can do?
my $sth = $dbh-prepare("
create table $temp_tbl
(date date,
uid varchar(14))
select date,
? as uid
from calendar
where date between ? and ?");
$sth-execute($uid, $bdate, $edate);
I have no idea what you are asking, which may explain why no one has
replied
to your question yet.
I've been working with relational databases for 20 years and I've never
heard the term "calendar table". What are you trying to accomplish? If you
describe clearly what you are trying to do, perhaps someone can help you
devise a way to do it in MySQL.
Rhino
A table of dates to which to join other tables,
ensuring reports that reflect days for which no
data is available.
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.12/220 - Release Date: 1/3/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lost Connection executing query
The explain resuts were from on the mac. I knew about the where clause optimization but that wouldn't explain why the server crashes when I issue the update statement. It is an interesting situation however. I agree that you're probably correct about the import/CHECK TABLES. Cheers, Tripp --- [EMAIL PROTECTED] wrote: Your import or CHECK TABLE sound like your best bets. Interesting about your SELECT statement conversion though, under the optimization section, it suggests you may still have a problem. Can you run your EXPLAIN SELECT on your MAC for comparison? 7.2.4. How MySQL Optimizes WHERE Clauses Early detection of invalid constant expressions. MySQL quickly detects that some SELECT statements are impossible and returns no rows. Ed -Original Message- From: Tripp Bishop [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 11:01 AM To: emierzwa; mysql@lists.mysql.com Subject: RE: Lost Connection executing query The query fails instantly so I don't think it's a timeout issue. The wait_timeout and interactive_timeout variables are set to 28800. The server seems to be crashing and auto restarting because as you suggested the uptime is small when I do a show status right after attempting the query. The schemas are identical and most of the data is the same. When I try to rewrite the update as a select I get an impossible where clause when I do an explain on the select. It can't be a max packet issue because the actual query is really small. The query runs fine on the MAC and takes about 1 second to run. I could break this update statement up into 4 seperate update statements but I'd prefer to keep it as one. I did notice that the MySQL manual suggests running CHECK TABLE on the table(s) involved but no other queries that operate against these tables seem to be having trouble so it seems unlikely that table corruption would be a problem. We did recently upgrade the server from 4.0.40 to 5.0.15 and we did not dump the tables and reimport them. On the MAC we did do a dump and reimport. I wonder if that could be the cause of this problem. I had forgetten about that important difference. Cheers, Tripp --- [EMAIL PROTECTED] wrote: What are your wait_timeout and/or interactive_timeout values set to? Does the server crash and auto restart? Check server's up time. Do both servers have the exact table schema? Same column datatypes and indexes to be specific. Although your data volumn may be similar, can the actual data be problamatic? Can you rewrite the UPDATE statement as a SELECT statement to see if you can target the rows you are expecting to target? You can check section A.2.8. MySQL server has gone away in the online manual, which also covers your message, for list of things to try. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Ed -Original Message- From: Tripp Bishop [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 03, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Lost Connection executing query Howdy all, First off, We're running 5.0.15. Theres a particular update statement that we run that updates data in several tables. On our mac OSX test server (also running 5.0.15) the query executes quickly and without any errors or warnings. On our linux box, which is our production box, we get the following error as soon as the query is executed: ERROR 2013 (HY000): Lost connection to MySQL server during query The databases have a similar amount of data in them. I've googled on the error but mostly get pages containing questions about the error when generated by stored procedures and mostly on 5.0.3. We're not using stored procedures. This is just a straight-up query. Here's the query: UPDATE customer_indicator INNER JOIN customer_search_pref ON customer_search_pref.customer_id = customer_indicator.customer_id AND customer_search_pref.office_id = customer_indicator.office_id LEFT JOIN contact_log ON contact_log.customer_id = customer_indicator.customer_id LEFT JOIN sent ON sent.pref_id = customer_search_pref.pref_id SET customer_indicator.office_id = 33, customer_search_pref.office_id =33, customer_indicator.agent_id = 105, sent.office_id = 33, contact_log.office_id = 33, customer_indicator.next_message_id = 4403 WHERE customer_indicator.customer_id = 78437 AND customer_indicator.office_id = 34; The approximate sizes of the tables involved are: customer_indicator: 40K records customer_search_pref: 45K contact_log: 390K sent: 20M (million) So my question is, what are some possible causes of this error? Why would trying to execute this query cause the connection to be lost? Why would the query work fine on our mac system and fail on the prodcution
Re: Calendar table workaround
RE: Calendar table workaround A table of dates to which to join other tables, ensuring reports that reflect days for which no data is available. I forget the query but I know it can be done. But can't you just have a table (called calendar?) with each entry having it's own row with a date column that gives whatever date you need in it and then other columns for any other details you need to have to go along with the date. - Sorry, I thought this was a very common situation. And, therefore, instantly recognizable. I'll include the full story. my $bdate = '2005-08-01'; my $edate = '2005-08-14'; my $uid = 'george'; my $temp_tbl = 'calendar_' . $uid; my $sth = $dbh-prepare( create table $temp_tbl (date date, uid varchar(14)) engine = memory select date, ? as uid from calendar where date between ? and ?); $sth-execute($uid, $bdate, $edate); $sth = $dbh-prepare( (select $temp_tbl.date as date, concat(type,seq) as event, time_format(time,'%H:%i'), value as val1, '' as val2 from $temp_tbl left join table1 on table1.date = $temp_tbl.date where $temp_tbl.uid = ? and $temp_tbl.date between ? and ?) union (select $temp_tbl.date, concat(type,seq), time_format(time,'%H:%i'), t1_val, t2_val from $temp_tbl left join table2 on table2.date = $temp_tbl.date where $temp_tbl.uid = ? and $temp_tbl.date between ? and ?) order by date, event); $sth-execute($uid, $bdate, $edate, $uid, $bdate, $edate); These are $uid-specific reports (where .uid = ?) and uid, of course, doesn't exist in my standard 'calendar table.' The question: Is creating another temporary table (that does include both date and uid) the best thing to do here? Thanks.
Can this SELECT go any faster?
Hello, I have a table called (history) containing thousands of rows. Each row is UNIX time-stamped, and belong to a particular account. I would like to know which months a particular account has been active. (For example, maybe one account has been active since June 2004, so the SELECT should return every month since then.) Here's what I'm using: SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 7 (8 total, Query took 0.1975 sec) month 200601 200512 200511 200510 200509 200508 200507 200506 This account (216) has about 8000 rows. There are Indexes for account_id and time_sec. I'm running MySQL 5.0.16. When I run EXPLAIN, I am told: id: 1 select_type: SIMPLE table: history type: ref possible_keys: account_id key: account_id key_len: 4 ref: const rows: 6556 Extra: Using where; Using temporary; Using filesort Any ideas how I can speed this up more? (I am just starting to learn how to improve MySQL performance but clearly have a ways to go.) Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT uses index but is still slow
I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can someone tell me why this fails?
Thanks Peter, for the quick reply. Peter Brawley [EMAIL PROTECTED] 1/4/06 11:44 AM Ed, Can someone tell me why this fails? I'm using v4.1.11 Select IF(((Select 1+1) Null) and ((Select 1+1) 0), (Select 1+1), 'WRONG') I would expect a result of 2. NULL is neither equal nor unequal to anything including itself. To get the result you expect, write Select IF(((Select 1+1) IS NOT Null) and ((Select 1+1) 0), (Select 1+1), 'WRONG'); PB
Inconsistent rows returned examined in slow query log
I have entries in my slow query log for identical queries but, as you can see from the log entries below (including one irrelevant query), the number rows examined and returned varies. The tables are _not_ being updated. The query cache is 'on demand', so I'm also not sure why the subsequent queries were not dealt with by the query cache. Any ideas? Thanks, James Harvard # Time: 060103 9:45:12 # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 86 Lock_time: 0 Rows_sent: 12 Rows_examined: 6733255 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 1998 group by dates.date_month order by dates.date_month; # Time: 060103 9:45:46 # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 70 Lock_time: 0 Rows_sent: 7 Rows_examined: 3737912 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 1998 group by dates.date_month order by dates.date_month; # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 50 Lock_time: 0 Rows_sent: 4 Rows_examined: 1585713 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 2000 group by dates.date_month order by dates.date_month; # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 117 Lock_time: 0 Rows_sent: 9 Rows_examined: 5196480 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 1998 group by dates.date_month order by dates.date_month; # [EMAIL PROTECTED]: lasso[lasso] @ localhost [127.0.0.1] # Query_time: 113 Lock_time: 0 Rows_sent: 9 Rows_examined: 5196063 select sql_cache dates.date_month from data_gb_e data inner join dates on data.date_id = dates.date_id and dates.date_year = 1998 group by dates.date_month order by dates.date_month; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can this SELECT go any faster?
Try this SELECT replace(left(history.time_sec,7),'-','') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC; This is what I get on 1 of my tables with no index on perm_user_ID , 80,000 rows in the table and 7,000 rows where perm_user_ID = 'CSRB' on version 5.0.17. mysql SELECT replace(left(pord_Timestamp,7),'-','') AS month - FROM product_order_main - WHERE perm_user_ID = 'CSRB' - GROUP BY month - ORDER BY pord_Timestamp DESC; ++ | month | ++ | 200511 | | 200510 | | 200509 | | 200508 | | 200507 | | 200506 | | 200505 | | 200504 | | 200503 | | 200502 | | 200501 | | 200412 | | 200411 | | 200410 | | 200409 | | 200408 | | 200407 | | 200406 | | 200405 | | 200404 | | 200403 | | 200402 | | 200401 | | 200312 | | 200311 | ++ 25 rows in set (0.08 sec) mysql select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +--+ | count(*) | +--+ | 7095 | +--+ 1 row in set (0.05 sec) mysql select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +--+ | count(*) | +--+ | 7095 | +--+ 1 row in set (0.05 sec) mysql select count(*) from product_order_main; +--+ | count(*) | +--+ |80774 | +--+ 1 row in set (0.05 sec) mysql select version(); +---+ | version() | +---+ | 5.0.17-nt | +---+ 1 row in set (0.00 sec) -Original Message- From: René Fournier [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 2:23 PM To: mysql@lists.mysql.com Subject: Can this SELECT go any faster? Hello, I have a table called (history) containing thousands of rows. Each row is UNIX time-stamped, and belong to a particular account. I would like to know which months a particular account has been active. (For example, maybe one account has been active since June 2004, so the SELECT should return every month since then.) Here's what I'm using: SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 7 (8 total, Query took 0.1975 sec) month 200601 200512 200511 200510 200509 200508 200507 200506 This account (216) has about 8000 rows. There are Indexes for account_id and time_sec. I'm running MySQL 5.0.16. When I run EXPLAIN, I am told: id: 1 select_type: SIMPLE table: history type: ref possible_keys: account_id key: account_id key_len: 4 ref: const rows: 6556 Extra: Using where; Using temporary; Using filesort Any ideas how I can speed this up more? (I am just starting to learn how to improve MySQL performance but clearly have a ways to go.) Thanks. ...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]
Re: Can this SELECT go any faster?
Thanks, but I don't think replace will help me, since my time_sec column is not DATE. Here's the table def (well, the part that matters): CREATE TABLE history ( id int(10) unsigned NOT NULL auto_increment, time_sec int(10) unsigned NOT NULL default '0', time_msec smallint(5) unsigned NOT NULL default '0', amount int(11) NOT NULL default '0', account_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY account_id (account_id), KEY time_sec (time_sec), KEY time_msec (time_msec), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ; This is why I am formatting time_sec... so I can refer to them as months, e.g.: SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC ...Rene On 4-Jan-06, at 2:05 PM, Gordon Bruce wrote: Try this SELECT replace(left(history.time_sec,7),'-','') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC; This is what I get on 1 of my tables with no index on perm_user_ID , 80,000 rows in the table and 7,000 rows where perm_user_ID = 'CSRB' on version 5.0.17. mysql SELECT replace(left(pord_Timestamp,7),'-','') AS month - FROM product_order_main - WHERE perm_user_ID = 'CSRB' - GROUP BY month - ORDER BY pord_Timestamp DESC; ++ | month | ++ | 200511 | | 200510 | | 200509 | | 200508 | | 200507 | | 200506 | | 200505 | | 200504 | | 200503 | | 200502 | | 200501 | | 200412 | | 200411 | | 200410 | | 200409 | | 200408 | | 200407 | | 200406 | | 200405 | | 200404 | | 200403 | | 200402 | | 200401 | | 200312 | | 200311 | ++ 25 rows in set (0.08 sec) mysql select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +--+ | count(*) | +--+ | 7095 | +--+ 1 row in set (0.05 sec) mysql select count(*) from product_order_main WHERE perm_user_ID = 'CSRB'; +--+ | count(*) | +--+ | 7095 | +--+ 1 row in set (0.05 sec) mysql select count(*) from product_order_main; +--+ | count(*) | +--+ |80774 | +--+ 1 row in set (0.05 sec) mysql select version(); +---+ | version() | +---+ | 5.0.17-nt | +---+ 1 row in set (0.00 sec) -Original Message- From: René Fournier [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 04, 2006 2:23 PM To: mysql@lists.mysql.com Subject: Can this SELECT go any faster? Hello, I have a table called (history) containing thousands of rows. Each row is UNIX time-stamped, and belong to a particular account. I would like to know which months a particular account has been active. (For example, maybe one account has been active since June 2004, so the SELECT should return every month since then.) Here's what I'm using: SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 7 (8 total, Query took 0.1975 sec) month 200601 200512 200511 200510 200509 200508 200507 200506 This account (216) has about 8000 rows. There are Indexes for account_id and time_sec. I'm running MySQL 5.0.16. When I run EXPLAIN, I am told: id: 1 select_type: SIMPLE table: history type: ref possible_keys: account_id key: account_id key_len: 4 ref: const rows: 6556 Extra: Using where; Using temporary; Using filesort Any ideas how I can speed this up more? (I am just starting to learn how to improve MySQL performance but clearly have a ways to go.) Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database slows down when mass users logging on
If you are knowledgeable about the application and know which tables are frequently accessed during the time of crash, you can send the create statements used to create those tables. Also any indexing and how many rows are in the table will also help. In my opinion over a hundred tables for an application sound bit too high. Perhaps some of table can be collapsed to fewer tables. On 1/4/06, Jonathan Chong [EMAIL PROTECTED] wrote: Hi Alex There are over a hundred tables for the site, and those that are related to this query are probably about 15-30. Do you want the CREATE TABLE syntax for all of them? Funnily enough, the file /var/log/mysql_error.log doesn't exist .. I'm using myISAM tables, yes. On 04/01/06, Alex [EMAIL PROTECTED] wrote: the table type whether it is myisam or innodb. create table statements are preferred when you have slow query issues. --Alex On Wed, 04 Jan 2006 15:57:06 +0530, Jonathan Chong [EMAIL PROTECTED] wrote: Sorry I also forgot to ask - when you say the tables, did you want the table structure? There are quite a few tables, perhaps I should just send the tables that are read and written to often when a user hits a page? On 04/01/06, Alex [EMAIL PROTECTED] wrote: Hi, Please provide details like what tables are you using, the entire my.cnf and the information from the mysqld.err when the crashes occurred. --Alex -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- http://chatter.mirislam.com/
Re: Alternative Mysql gui than Navicat for OSX
What is it you are looking for in the application? I use DBVisualizer free version for most of my mysql and oracle use. You can give it a try and see if you like it or not. On 1/2/06, Dan Rossi [EMAIL PROTECTED] wrote: Hi there i am looking for an alternative gui app than navicat for osx. Ive used CocoaMysql in the past and is limited in terms of administration, however has more working features than navicat ever does. I assumed this product was pretty good, however after purchasing boy was i wrong. I wished SqlYog had an osx version as it is a very good application. These people have extremely poor support and have features missing out of it which should be in there in the first place, like advanced dump options like adding bloody drop table statements LOL, they claimed its a feature request. Im really peeved now so i am telling people not to buy this product they are ignoring all my tickets now. The application systematically crashes just doing something simple , i cannot find any crash logs in the normal places osx apps usually use, and ive just found thats its storing saved queries and saved views in a folder called osx in my /Applications root folder not in the Navicat folder or a preference folder ! I had no idea what it is and like to clean my machine up alot and kept trashing it and couldnt work out why the saved queries went missing, this is extremely poor programming. So dont buy navicat , stick with the terminal client :D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- http://chatter.mirislam.com/
Re: How to create database in different location
Chris Guo wrote: Chris, We are using mysql as backend database for one of the application in our company server, and there are too many data in the Mysql database. So I wonder how I create a database on different location. How do you define too much data in the MySQL database? Do you want to replicate? Or use clustering? More details are required. Kind regards -- Colin Charles, Community Engineer MySQL AB, Kuala Lumpur, Malaysia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database slows down when mass users logging on
Hey, As per you config in my.cnf there has to be two logs log_error = /var/log/mysql_error.log log-slow-queries = /var/log/mysql_slow_queries.log We have to have that info in error log to hunt down the issue. Also if possible obtain the messages from /var/log/messages (these are kernel messages) when the crash occurred. Without the error info from the mysql_error.log we cant do much. Thanx Alex On Wed, 04 Jan 2006 19:00:16 +0530, Jonathan Chong [EMAIL PROTECTED] wrote: Hi Alex There are over a hundred tables for the site, and those that are related to this query are probably about 15-30. Do you want the CREATE TABLE syntax for all of them? Funnily enough, the file /var/log/mysql_error.log doesn't exist .. I'm using myISAM tables, yes. On 04/01/06, Alex [EMAIL PROTECTED] wrote: the table type whether it is myisam or innodb. create table statements are preferred when you have slow query issues. --Alex On Wed, 04 Jan 2006 15:57:06 +0530, Jonathan Chong [EMAIL PROTECTED] wrote: Sorry I also forgot to ask - when you say the tables, did you want the table structure? There are quite a few tables, perhaps I should just send the tables that are read and written to often when a user hits a page? On 04/01/06, Alex [EMAIL PROTECTED] wrote: Hi, Please provide details like what tables are you using, the entire my.cnf and the information from the mysqld.err when the crashes occurred. --Alex -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- Jonathan Chong http://www.arsenal-now.com/ http://www.arsenal-mania.com/ http://www.ashburrn.com/ http://www.jonathan-chong.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
an backup syntax error
Follow is my preform and result: - mysql backup table ht_detail to '/'; +++--+--+ | Table | Op | Msg_type | Msg_text | +++--+--+ | test.ht_detail | backup | error| Failed copying .frm file (errno: 13) | | test.ht_detail | backup | status | Operation failed | +++--+--+ 2 rows in set, 1 warning (0.00 sec) - How to solve it? I use 5.0.16.
RE: an backup syntax error
Hi, The error message (errno: 13) indicates a permissions problem. Check the permissions of the user that is running the backups or, alternatively, check the permissions that are set for this file. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: wangxu [mailto:[EMAIL PROTECTED] Sent: Thursday, 5 January 2006 1:29 PM To: mysql@lists.mysql.com Subject: an backup syntax error Follow is my preform and result: - mysql backup table ht_detail to '/'; +++--+-- + | Table | Op | Msg_type | Msg_text | +++--+-- + | test.ht_detail | backup | error| Failed copying .frm file (errno: 13) | | test.ht_detail | backup | status | Operation failed | +++--+-- + 2 rows in set, 1 warning (0.00 sec) - How to solve it? I use 5.0.16. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
回复： MySQL 4.1.13 and utf-8 language encoding
firstly,I don't use utf8,then maybe this will help you,first dump out your in sql form,then find a mysql 4.1 with phpmyadmin 2.5* computer,or make one,then deploy your sql in the latin mode,then dump out it again,redeploy it on your web host,i think it will do,and no matter how you modify phpmyadmin 2.6 ,no matter utf8 or latin or gb,you will find ? in phpmyadmin all the time ,i don't know how to fix it,and it will be ok on your page 立 周 [EMAIL PROTECTED] 写道： --- 维斯 苏 写道: just use phpmyadmin 2.5* because the 2.6* default use utf8,what you need to do is dump out your old mysql in sql form, use phpmyadmin 2.5* to redeploy it on 4.1*. That is it. I don't have control over phpmyadmin version because it is on a shared hosting plan. Can i manually modify the SQL and explisively set collation attributes to unicode_general_ci? because the collation attribute does exist on MySQL 4.0, and 4.1.13 server is default to latin1_swedish_ci. Will this work out? http://www.cnads.org/ thanks. __ 赶快注册雅虎超大容量免费邮箱? http://cn.mail.yahoo.com __ 赶快注册雅虎超大容量免费邮箱? http://cn.mail.yahoo.com
Re: SELECT DISTINCT uses index but is still slow
Hi James, I have found similar - slowdown - effects for queries. However, it is not always clear what causes the lack of speed. For example, I have a table with more than 9 million rows, including a non-unique indexed item myKey (tinyint). The query Select myKey, count(*) from myTable Group By myKey; takes with the CLI about 25 seconds, BUT the second time it takes only 0.01 second ! I think that the 1st query run includes loading indices into memory. I suggest to test your query twice from the CLI. HTH, Cor Vegelin - Original Message - From: James Harvard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 04, 2006 8:28 PM Subject: SELECT DISTINCT uses index but is still slow I have a query that takes a long time even though it is only examining the index file. Is this normal? I would have thought that MySQL would be much quicker to get the unique values from an index. select distinct date_id from data_table; # returns 89 rows in 23 seconds - simple index on date_id with 2 other indices - just under 40,000,000 rows in table - index file is 730 MB EXPLAIN SELECT gives the following: type = index key = date_id rows = 39726908 extra = using index FWIW the result is identical with 'select date_id from data_table group by date_id;'. (Using version 4.1.15 on Windows, and I can't see anything relevant in the change notes for 4.1.16.) Finally, here's a CREATE TABLE: CREATE TABLE data_table ( is_import tinyint(1) NOT NULL DEFAULT 0, comcode_id mediumint(8) UNSIGNED NOT NULL DEFAULT 0, date_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, country_id smallint(5) UNSIGNED NOT NULL DEFAULT 0, value bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity_2 bigint(20) UNSIGNED DEFAULT NULL, c_value bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_1 bigint(20) UNSIGNED NOT NULL DEFAULT 0, c_quantity_2 bigint(20) UNSIGNED DEFAULT NULL, port_id tinyint(3) UNSIGNED NOT NULL DEFAULT 0, KEY date_id (date_id), KEY country_id (country_id), KEY comcode_id (comcode_id,date_id) ) ENGINE=MyISAM ROW_FORMAT=FIXED; TIA, James Harvard -- 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: ISAM tables broken
Where are your .frm files, are you not including them in your directory listing or are they missing? If they're missing, that's most likely your problem right there.. :) Atle - Flying Crocodile Inc, Unix Systems Administrator On Wed, 4 Jan 2006, Simon Faulkner wrote: An old server died during the holidays. I had a database with 2 tables on it on an old version of MySQL (circa Redhat 7?) I have the latest backup of the files but not a dump (kick himself) I put the files in the correct /var/lib/mysql/gallery and restarted MySQL. It shows the gallery database but not the tables I have tried ISAM check and it seems OK but MySQL won't open the tables. I have even tried the files in a fresh compiled MySQL rather than the FC4 version in case that's a problem. Are my tables doomed? TIA Simon PS they are at http://titanic.co.uk/gallery/tblGallery.ISD http://titanic.co.uk/gallery/tblGallery.ISM http://titanic.co.uk/gallery/tblPicture.ISD http://titanic.co.uk/gallery/tblPicture.ISM if anyone needs to see them... There's nothing private in them. [EMAIL PROTECTED] gallery]# ls -l total 1196 -rwxrwxrwx 1 mysql mysql6511 Jan 4 15:30 tblGallery.ISD -rwxrwxrwx 1 mysql mysql2048 Jan 4 15:30 tblGallery.ISM -rwxrwxrwx 1 mysql mysql 1185360 Jan 4 15:30 tblPicture.ISD -rwxrwxrwx 1 mysql mysql 18432 Jan 4 15:30 tblPicture.ISM [EMAIL PROTECTED] gallery]# mysqlshow +---+ | Databases | +---+ | gallery | | mysql | | test | +---+ [EMAIL PROTECTED] gallery]# mysqlshow gallery Database: gallery ++ | Tables | ++ ++ [EMAIL PROTECTED] gallery]# isam isamchk isamlog [EMAIL PROTECTED] gallery]# isamchk tblGallery.ISD isamchk: error: 'tblGallery.ISD' is not a ISAM-table [EMAIL PROTECTED] gallery]# isamchk tblGallery.ISM Checking ISAM file: tblGallery.ISM Data records: 17 Deleted blocks: 0 - check file-size - check delete-chain - check index reference [EMAIL PROTECTED] gallery]# isamchk tblPicture.ISD isamchk: error: 'tblPicture.ISD' is not a ISAM-table [EMAIL PROTECTED] gallery]# isamchk tblPicture.ISM Checking ISAM file: tblPicture.ISM Data records:2640 Deleted blocks: 0 - check file-size - check delete-chain - check index reference [EMAIL PROTECTED] gallery]# isamchk -r tblPicture.ISM - recovering ISAM-table 'tblPicture.ISM' Data records: 2640 - Fixing index 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can i get a mysql technology standard
Can i get a technology standard? It include database capability,table capability,row number limit in table,blob object capability etc
Package failed to install
Hi Mysql Users, Have not used mysql for years and was trying to install new version. Error = Package failed due to Coreutils and tibpthread.so.0 not installed. How do I fix this problem?? Thanks in advace. As I said it will tack me a few days to get back up to speed. Andrew
Re: Can i get a mysql technology standard
wangxu wrote: Can i get a technology standard? It include database capability,table capability,row number limit in table,blob object capability etc... Hi, A good read of the manual at http://dev.mysql.com/doc/refman/5.0/en/index.html would give you almost all of the information that you require. Especially the page http://dev.mysql.com/doc/refman/5.0/en/introduction.html which gives the various capabilities of the database in general. If you look under the datatype definitions, you will find the maximum size of each datatype and any limitations they may have. Each engine is fairly well described in the section http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html along with any special features and/or limitations. Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
