Mysql Timezone
Hi everyone is it possible in Mysql 5.0.1 to set the timezone for a user ? PS: I don't have root access to mysql, so I'm looking for away to do it as a normal user. --- Ahmad http://www.v-tadawul.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld_multi startup stop
Dear all, I am running the official mysql rpms on a sles 9 box, and I am running multiple versions of mysql servers on different ports. The problem I am facing is that when i run mysqld_multi start n it starts the servers up fine but when i do the reverse : mysqld_multi stop n, it doesn't stop the server and I have to manually search for the pids and execute a kill on the process to stop it. Anybody have any idea ? Thank you in advance. Low Kian Seong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_upgrade shows error
Hello, I've just upgraded my MySQL server to version 5.0.26 and ran the mysql_upgrade script with basedir, datadir, password and socket arguments. The first time, I ran it, it listed a bunch of tables (database.tablename) with an OK after them. In the end, it said: ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' This is the only message it prints out when I run it again. Does anybody know what that means? Is mysql_upgrade broken? Is a table broken? Which one? Do I need to care about it? My OS is Debian 3.1, I downloaded the glibc 2.3 linux max version of MySQL. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie help db locked err is The table '#sql_2c52_0' is full [ag]
Hy thank you all, I'm getting the following err msg - The table '#sql_2c52_0' is full running a complex query (and only running that query) with three outer join it is a simple db that stores mail msgs my query is about mail msg-recipients-sender the biggest table mail_archive has 90467 rows I checked every things I know: -disk space tables integrity isam check etc... my server is- server version: 4.0.24_Debian-10sarge2-log all tables are myISAM tables this snippets is in csv format output from command: SHOW TABLE STATUS FROM mydbLIKE 'mail%' mysql Name,Type,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Create_options,Comment mail_archive,MyISAM,Dynamic,90467,4307,389686660,4294967295,2044928,0,102183,2006-10-01 02:38:34,2006-10-01 23:15:31,2006-10-01 02:43:55,, mail_attachment_archive,MyISAM,Dynamic,47107,47,2233816,4294967295,1024,0,,2006-01-20 09:53:26,2006-10-01 23:15:31,,, mail_recipients,MyISAM,Dynamic,682960,51,34918212,4294967295,5704704,19044,,2006-01-20 09:53:26,2006-10-01 23:15:31,,, mail_senders,MyISAM,Dynamic,49247,50,2497204,4294967295,421888,3560,,2006-01-20 09:53:26,2006-10-01 23:15:31,2006-03-23 23:53:08,, any help would be appreciate. TIA ag. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No Primary Key and No Index?
Does not having a Primary Key and No indexes really speed up inserts significantly? We have a log table. it has the fields, cart_id, referer, remote_ip, server_name, user_agent, company, action, type, and value that we are tracking vistor log information for our ecommerce site. Every page is tracked that a person goes to in the log file. Currently we have about 500,000 rows. So I am wondering if we are really saving that much by not having a Primary Key and no indexes. Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.com); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie help db locked err is The table '#sql_2c52_0' is full [ag]
Adriano - MySQL names temp tables like that (starting with # sign), in paticular while storing a result set to be sorted. I would guess that your query is returning a result set large enough to hit a filesystem limit on your box. How big are you expecting the results to be? Perhaps you are missing a join, or have a malformed query in some way, and therefore producing a cartesian product. You could try removing any sort you have on the data to see if the query will return results that way, since MySQL won't attempt to sort the data and therefore may not fill up a temp table. You could also try a LIMIT x on your statement to see if your query is returning what you expect. Finally, it is very helpful in this forum if you post your table structures, your actual query as well as the output you get from running EXPLAIN query; Best, Dan On 10/21/06, adriano ghezzi [EMAIL PROTECTED] wrote: Hy thank you all, I'm getting the following err msg - The table '#sql_2c52_0' is full running a complex query (and only running that query) with three outer join it is a simple db that stores mail msgs my query is about mail msg-recipients-sender the biggest table mail_archive has 90467 rows I checked every things I know: -disk space tables integrity isam check etc... my server is- server version: 4.0.24_Debian-10sarge2-log all tables are myISAM tables this snippets is in csv format output from command: SHOW TABLE STATUS FROM mydbLIKE 'mail%' mysql Name,Type,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Create_time,Update_time,Check_time,Create_options,Comment mail_archive,MyISAM,Dynamic,90467,4307,389686660,4294967295,2044928,0,102183,2006-10-01 02:38:34,2006-10-01 23:15:31,2006-10-01 02:43:55,, mail_attachment_archive,MyISAM,Dynamic,47107,47,2233816,4294967295,1024,0,,2006-01-20 09:53:26,2006-10-01 23:15:31,,, mail_recipients,MyISAM,Dynamic,682960,51,34918212,4294967295,5704704,19044,,2006-01-20 09:53:26,2006-10-01 23:15:31,,, mail_senders,MyISAM,Dynamic,49247,50,2497204,4294967295,421888,3560,,2006-01-20 09:53:26,2006-10-01 23:15:31,2006-03-23 23:53:08,, any help would be appreciate. TIA ag. -- 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: No Primary Key and No Index?
It does speed things up to have no indices, sure. How much, I do not know exactly. It takes CPU time to update the index, time to write it to disk, etc. I have always used indices heavily, since I have tended to work with data that needs to be sliced diced lots of different ways. I personally consider it to be good database design to always have a primary key unique id number in my tables - but in some cases it's just plain not needed. Yours may be just such a case, since it's just logging data. Do you feel you need such a unique identifier? If you feel you do, add it. If you notice it's slowing things way down and that is unacceptable, you could evaluate whether to start tweaking performance / upgrading hardware, or just removing the column from the table. HTH, Dan On 10/21/06, Brett Harvey [EMAIL PROTECTED] wrote: Does not having a Primary Key and No indexes really speed up inserts significantly? We have a log table. it has the fields, cart_id, referer, remote_ip, server_name, user_agent, company, action, type, and value that we are tracking vistor log information for our ecommerce site. Every page is tracked that a person goes to in the log file. Currently we have about 500,000 rows. So I am wondering if we are really saving that much by not having a Primary Key and no indexes. Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.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: No Primary Key and No Index?
Another thought I had just after hitting send - if you're concerned about adding to the time it takes to load a page due to logging, you should consider using an INSERT DELAYED syntax if you can. That causes the database to immediately return a success message to the client (probably your webserver in this case) while it buffers the statement to be performed at the next opportunity. See the manual page for more info and caveats: http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html You could use this regardless of whether or not you choose to add indices, even. Dan On 10/21/06, Brett Harvey [EMAIL PROTECTED] wrote: Does not having a Primary Key and No indexes really speed up inserts significantly? We have a log table. it has the fields, cart_id, referer, remote_ip, server_name, user_agent, company, action, type, and value that we are tracking vistor log information for our ecommerce site. Every page is tracked that a person goes to in the log file. Currently we have about 500,000 rows. So I am wondering if we are really saving that much by not having a Primary Key and no indexes. Thanks! -- /Brett C. Harvey; /Creative-Pages.Net, President; /Facility Management Systems, CTO (www.fmsystems.biz); /Lasso Partner Association Member ID #LPA135259 (www.omnipilot.com/www.lassopartner.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: mysql_upgrade shows error
Yves, my recollection is that I ran into this exact same error msg on a recent 4.1 - 5.0 upgrade, and the bottom line is you don't need to worry about it. The 'File_priv' column exists as it should for 5.0. Dan On 10/21/06, Yves Goergen [EMAIL PROTECTED] wrote: Hello, I've just upgraded my MySQL server to version 5.0.26 and ran the mysql_upgrade script with basedir, datadir, password and socket arguments. The first time, I ran it, it listed a bunch of tables (database.tablename) with an OK after them. In the end, it said: ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv' This is the only message it prints out when I run it again. Does anybody know what that means? Is mysql_upgrade broken? Is a table broken? Which one? Do I need to care about it? My OS is Debian 3.1, I downloaded the glibc 2.3 linux max version of MySQL. -- Yves Goergen LonelyPixel [EMAIL PROTECTED] http://beta.unclassified.de – My web laboratory. -- 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(2): newbie help db locked err is The table '#sql_2c52_0' is full [ag]
before all thank you for your answer, next while waiting for an answ3er I backup the db then tried to convert tables in Inno db format everything run fine now, the same query execute ok but i prefer myIsam format i use it as default, then I'd like to understand what is the problem, here are all infos you requested, thnks again for your time; the query is right when there were few rows worked fine, actually just to i set limit 10 but nothing same error these are th tables; mysql explain mail_archive; +--+--+--+-++--- -+ | Field| Type | Null | Key | Default| Extra | +--+--+--+-++--- -+ | message_id | int(11) | | PRI | NULL | auto_increment | | message_file | varchar(150) | | MUL || | | subject | varchar(150) | | || | | date | varchar(100) | | || | | real_date| date | | | -00-00 | | | headers | text | | || | | body | text | | || | | body_html| text | | || | | flag | smallint(5) unsigned | | | 0 | | +--+--+--+-++--- - mysql explain mail_recipients; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | foxbox_user_id | int(11) | | | 0 | | | message_id | int(11) | | MUL | 0 | | | name | varchar(100) | | | | | | email | varchar(100) | | | | | ++--+--+-+-+---+ mysql explain mail_senders; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | foxbox_user_id | int(11) | | | 0 | | | message_id | int(11) | | MUL | 0 | | | name | varchar(100) | | | | | | email | varchar(100) | | | | | ++--+--+-+-+---+ mysql explain mail_attachment_archive; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | message_id | varchar(150) | | | 0 | | | file_name | varchar(150) | | | | | | type | varchar(100) | | | | | ++--+--+-+-+---+ and finally this is the query SELECT mail_senders.name AS mitt, mail_recipients.name AS dest, mail_archive.message_id, mail_archive.subject, mail_archive.message_file, mail_archive.date, mail_archive.real_date, mail_archive.* FROM (mail_archive INNER JOIN mail_recipients ON mail_archive.message_id = mail_recipients.message_id) INNER JOIN mail_senders ON mail_archive.message_id = mail_senders.message_id order by message_id desc limit 10 it is also my thought the problem is in anyway related to space, but can't understand were and which setting is related to this 4 2006/10/21, Dan Buettner [EMAIL PROTECTED]: Adriano - MySQL names temp tables like that (starting with # sign), in paticular while storing a result set to be sorted. I would guess that your query is returning a result set large enough to hit a filesystem limit on your box. How big are you expecting the results to be? Perhaps you are missing a join, or have a malformed query in some way, and therefore producing a cartesian product. You could try removing any sort you have on the data to see if the query will return results that way, since MySQL won't attempt to sort the data and therefore may not fill up a temp table. You could also try a LIMIT x on your statement to see if your query is returning what you expect. Finally, it is very helpful in this forum if you post your table structures, your actual query as well as the output you get from running EXPLAIN query; Best, Dan On 10/21/06, adriano ghezzi [EMAIL PROTECTED] wrote: Hy thank you all, I'm getting the following err msg - The table '#sql_2c52_0' is full
