RE: error 1016 : cant open ibd file even though it exists
Still a mystery about your missing InnoDB database files Rithish. I seem to remember there is an option that will allow you to store InnoDB files in their own seperate directory safely, and not in the /var/lib/mysql default directory. One of the many things I like about MyISAM tables is that each database is kept in its own subdirectory, whereas the default for InnoDB databases seems to be mixed in with the mysql log files. This gives me a uneasy feeling, especially as I delete the log files when backing up mysql databases. The moral of this post must certainly be something like: As the data stored in a company's databases is vital to the operation of the company, when it comes to the integrity and safety of your company's databases, you cannot be too paranoid. Make regular checks and backups of your databases, implementing a staged backup policy, so that whatever happens, you as the DBA, are fully covered for all possible circumstances. Which is obviously, thank God, what you have done Rithish. Kind Regards Keith In theory, theory and practice are the same; In practice they are not. On Tue, 28 Feb 2006, Rithish Saralaya wrote: To: mysql@lists.mysql.com From: Rithish Saralaya [EMAIL PROTECTED] Subject: RE: error 1016 : cant open ibd file even though it exists Hello Keith. The power outage was known before-hand, and the server was shutdown before the outage happened. The server was brought up once the power returned. So no UPs intervention happened here. Regards, Rithish. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, February 25, 2006 8:13 PM To: mysql@lists.mysql.com Subject: RE: error 1016 : cant open ibd file even though it exists I do not use Innodb tables at the moment, so all this is pure speculation. Is/was the server connected to a UPS when the power failure happened? If so, did the UPS function properly and do you have any UPS logs to match against the mysql error log? Things to check for would be the time the UPS took over from the mains supply? If there was no UPS in operation, were your InnoDB tables corrupted when the power failure occured. Is it possible for the InnoDB tables to have become so corrupted that mysql had no alternative but to re-construct the tables? What are your config settings for mysql at boot up time? Do you have any directives to force checking of InnoDB tables at boot-time, and if so what are they please? As soon as you mentioned 'power failure' the words 'table corruption' sprang to my mind. Also, are there any mysql transaction logs (*.log) you can check, to see what was happening when the power outage occured? Keith In theory, theory and practice are the same; In practice they are not. On Sat, 25 Feb 2006, Rithish Saralaya wrote: To: [EMAIL PROTECTED] From: Rithish Saralaya [EMAIL PROTECTED] Subject: RE: error 1016 : cant open ibd file even though it exists Hello David. There was supposed to be a power outage in our office that day. So the server was shut down. Finally when the power was back, the machine was plugged on. That's all. No file system change. Nothing. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
returning empty columns
This is probably going to sound like an odd request, but is there a way to return empty columns in Mysql. For example a roll call sheet I want to do a select of names from my table and then add a column for each of the next 12 weeks. I tried this. SELECT `Call`, concat(FName, ' ', LName) as Name, 'Mar-6' ,'Mar-13', 'Mar-20', 'Mar-27' FROM table Order BY LName, FName The problem is it put that date on every row and I just want the names to show up in the column header I know I could just write some php code to print out a table with the columns but I have a handy php function the prints the out put of a query in a table already so if I can find the right query I don't have to change that code any. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL 5 and CPu at 99.99%
hi, Taiyo | innodb_buffer_pool_size | 8388608 | key_buffer_size | 8388600 Try to increase the innodb_buffer_pool_size if you use the innodb storage or key_buffer_size if MyISAM storage is used or both. You can get more information about innodb_buffer_pool_size and key_buffer_size from this: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Best regards, Leo Huang 2006/2/28, Taiyo [EMAIL PROTECTED]: Greetings, We are running a server and the CPU is at %99.99 at all times, after about 2-3 hours of processing queries just hang, sounds like our hardware is weak but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS. I was hoping someone could look at our settings and would help us analyze this issue: Please advise. Here are the stats: Some version information: mySQL version: 5.0.16-standard Uname: Linux db.example.com 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux RAM:4GB SWAP 1GB HD: 2 SCSI 10k RPM on 2 separate controllers. Some information about the load: Queries per second avg: 16.346 (about) Our biggest table is 3.5 million records and we index 3 of the columns for fulltext search We do a lot of join queries on 2 tables. Some mySQL variables: [mysqld] tmpdir=/db.example.com/tmp query_cache_size=1048576 query_cache_limit = 33554432 query_cache_size = 33554432 myisam_sort_buffer_size = 33554432 sort_buffer_size = 33554432 max_connections=500 table_cache = 1000 max_tmp_tables = 256 Here is all of my mysql -e 'SHOW VARIABLES': +-+- -+ | 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 | 2 | | 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 | | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_checksums| ON | | innodb_commit_concurrency | 0 | |
UPDATE from monthly to yearly rows
Hi List, Please help me with the following problem in MySQL 5.0.15. I have 2 MyISAM tables like: - table Updates with fields myKey, Year, Month, Value where Month has the values 1 .. 12 - table Data with fields myKey, Year, Jan, Feb, ... Dec I need to put all available monthly Values from Updates to 1 Data record where MyKey and Year are equal. I tried the following query: UPDATE Data AS db INNER JOIN Updates AS U ON db.myKey = U.myKey SET db.Jan = IF(U.Month = 1, U.Value, db.Jan), db.Feb = IF(U.Month = 2, U.Value, db.Feb), ... db.Dec = IF(U.Month=12, U.Value,db.Dec); But this query takes only the first available Month in Updates, and ignores the other months per myKey / Year combination. I would appreciate your help. TIA, Cor
Simple libmysql C API tutorial needed
Hi! I already wrote a wrapper for unixodbc... Now I would like to skip odbc and connect to mysql directly... AFAIK that is done using libmysql, right? I looked at the www.mysql.com buzzword-bloat for 15 seconds before I decided to subscribe here and just ask instead (I'm one of those too-long-didn't-read kinds). Any link to a code snippet opening a connection, sending a query and closing the connection would be highly appreciated. Dirk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
jdbc:mysql
Hi, I have a problem connecting to MySQL. I could connect to the database with this sentence: jdbc:mysql://localhost:3306/db but not with this sentence, with the same IP that the localhost: jdbc:mysql://192.xxx.xxx.xxx:3306/db What's wrong? Thanks for all, Amaia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: returning empty columns
- Original Message - From: 2wsxdr5 [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 28, 2006 3:43 AM Subject: returning empty columns This is probably going to sound like an odd request, but is there a way to return empty columns in Mysql. For example a roll call sheet I want to do a select of names from my table and then add a column for each of the next 12 weeks. I tried this. SELECT `Call`, concat(FName, ' ', LName) as Name, 'Mar-6' ,'Mar-13', 'Mar-20', 'Mar-27' FROM table Order BY LName, FName The problem is it put that date on every row and I just want the names to show up in the column header I know I could just write some php code to print out a table with the columns but I have a handy php function the prints the out put of a query in a table already so if I can find the right query I don't have to change that code any. In 20+ years of writing and teaching SQL I can't remember anyone ever wanting to do this but you can easily get a blank column (or twelve) with just a slight modification of the technique you already tried. The values you put within apostrophes, like 'Mar-20', are just literals so, instead of putting text between the apostrophes, just write two consecutive apostrophes. Therefore: SELECT `Call`, concat(FName, ' ', LName) as Name, '' ,'', '', '' FROM table Order BY LName, FName will give you the same information you got before but each of the four extra columns should be empty. If you want those columns to have titles, use an AS expression, like this: SELECT `Call`, concat(FName, ' ', LName) as Name, '' as Eenie ,'' as Meenie, '' as Miney, '' as Moe FROM table Order BY LName, FName Be careful when typing my examples: to get a blank column, you need two consecutive apostrophes (sometimes called single quotes) but the AS expressions need to be within double quotes. Wait! I was wrong! I just tried it using single quotes in the AS expressions and it still worked fine: SELECT `Call`, concat(FName, ' ', LName) as Name, '' as 'Eenie' ,'' as 'Meenie', '' as 'Miney', '' as 'Moe' FROM table Order BY LName, FName It even worked when I used backtics (`): SELECT `Call`, concat(FName, ' ', LName) as Name, '' as `Eenie` ,'' as `Meenie`, '' as `Miney`, '' as `Moe` FROM table Order BY LName, FName You can also use pairs of double quotes to create the empty columns: SELECT `Call`, concat(FName, ' ', LName) as Name, as `Eenie` , as `Meenie`, as `Miney`, as `Moe` FROM table Order BY LName, FName But you can't use pairs of backtics: SELECT `Call`, concat(FName, ' ', LName) as Name, `` as `Eenie` , `` as `Meenie`, `` as `Miney`, `` as `Moe` FROM table Order BY LName, FName So, MySQL is more tolerant than I realized. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL 5 and CPu at 99.99%
Thank you very much, I will have a look at it. -Original Message- From: leo huang [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 28, 2006 3:47 AM To: Taiyo Cc: mysql@lists.mysql.com Subject: Re: mySQL 5 and CPu at 99.99% hi, Taiyo | innodb_buffer_pool_size | 8388608 | key_buffer_size | 8388600 Try to increase the innodb_buffer_pool_size if you use the innodb storage or key_buffer_size if MyISAM storage is used or both. You can get more information about innodb_buffer_pool_size and key_buffer_size from this: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Best regards, Leo Huang 2006/2/28, Taiyo [EMAIL PROTECTED]: Greetings, We are running a server and the CPU is at %99.99 at all times, after about 2-3 hours of processing queries just hang, sounds like our hardware is weak but we are running a 4GB RAM Dual Xeons 3.4 at 64bit OS. I was hoping someone could look at our settings and would help us analyze this issue: Please advise. Here are the stats: Some version information: mySQL version: 5.0.16-standard Uname: Linux db.example.com 2.6.9-22.ELsmp #1 SMP Mon Sep 19 18:00:54 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux RAM:4GB SWAP 1GB HD: 2 SCSI 10k RPM on 2 separate controllers. Some information about the load: Queries per second avg: 16.346 (about) Our biggest table is 3.5 million records and we index 3 of the columns for fulltext search We do a lot of join queries on 2 tables. Some mySQL variables: [mysqld] tmpdir=/db.example.com/tmp query_cache_size=1048576 query_cache_limit = 33554432 query_cache_size = 33554432 myisam_sort_buffer_size = 33554432 sort_buffer_size = 33554432 max_connections=500 table_cache = 1000 max_tmp_tables = 256 Here is all of my mysql -e 'SHOW VARIABLES': +-+- -+ | 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 | 2 | | 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 | | | innodb_additional_mem_pool_size | 1048576
RE: returning empty columns
Chris, See if this works for you: SELECT ' ' as 'Call' , concat(FName, ' ', LName) as Name , ' ' as 'Mar-6' , ' ' as 'Mar-13' , ' ' as 'Mar-20' , ' ' as 'Mar-27' FROM table ORDER BY LName, FName Notice that there is a single blank space being returned for the Call, Mar-6, Mar-13, Mar-20, Mar-27 columns. I tried returning null as well as an empty string (i.e., '') but those did NOT give me column headings. Hope this helps. Randall Price VT.SETI.IAD.MIG:Microsoft Implementation Group http://vtmig.vt.edu [EMAIL PROTECTED] -Original Message- From: 2wsxdr5 [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 28, 2006 3:43 AM To: mysql@lists.mysql.com Subject: returning empty columns This is probably going to sound like an odd request, but is there a way to return empty columns in Mysql. For example a roll call sheet I want to do a select of names from my table and then add a column for each of the next 12 weeks. I tried this. SELECT `Call`, concat(FName, ' ', LName) as Name, 'Mar-6' ,'Mar-13', 'Mar-20', 'Mar-27' FROM table Order BY LName, FName The problem is it put that date on every row and I just want the names to show up in the column header I know I could just write some php code to print out a table with the columns but I have a handy php function the prints the out put of a query in a table already so if I can find the right query I don't have to change that code any. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.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: email to db
On 2/24/06, Mark [EMAIL PROTECTED] wrote: Can anyone tell me if it is possible to send an email from outlook to a DB so it updates a record. I have a php tipping script hosted with a mysql DB but would like users to email their tips instead of loggin on the site. Mark Well, no, DBs are not email handlers, unless you tell your MTA for example to handle the email and update the DB. I mean, you can do it, if you have access to your server and/or someone really likes you at your provider... :) Keep in mind that login and forms are secure compared to an email, how would it check its from one of your users? How could you be sure the content would be just text? Users screw things up, what if he sends you a whole book in the email, or an atachment? You would need a whole system to do that with no riscs... If you have dedicated hosting, you can do it, else, I doubt any ISP would allow it... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade problems 4.1-5.0.18
Hello fellow listers, I recently upgraded via RPMs from 4.1 to 5.0.18 on a RH9.0 (glibc2.3, kernel 2.4.20) machine. Now I have problems starting the mysqld automatically. When it is invoked, the error Couldn't find MySQLmanager or server is emitted. Here is a short history of my actions thus far. - Installed (updated) RPMs w/o shutting down current 4.1 mysqld first - Can't start mysqld from init.d script as root - reset by hand the links to match those mentioned at top of mysql start script (/etc/rc3.d/S99mysql, /etc/rc0.d/K01mysql) - Complains w/ the above mentioned error but mysqlmanager, mysqld_safe and mysqld all exist owned by root w/ 755 permissions - able to start mysqld and mysqld_safe as root on the command-line - reset 'root' and 'mysql' user passwords as they were forgotten from earlier install of 4.1 - ran mysql_fix_priviledge_tables - every time server is run and subsequently shut down (via kill not kill -9), the socket filedoesn't go away - this prompts a change in the socket entry of /etc/my.cnf in order to restart (mysql.sock(x) - mysql.sock(x+1)) - the directory /var/lib/mysql is owned by 'mysql' with permissions 755 - able to connect to a running mysqld as both 'root' and 'mysql' users I hope this is enough info as this post is getting rather long. So, in short, my problem is that I cannot get the mysqld running automatically at boot-time and the socket files do not go away after the server exits. Any info on this would be greatly appreciated. Steven J Orton Software Engineer Northrop Grumman Mission Systems Middletown, RI 02842 [Email] [EMAIL PROTECTED] [Office] 401-851-3540 [Lab] 401-832-1959 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dynamic queries v/s Stored Procs
I'm in a process of transitioning from dynamic queries that were generated in C# code to using Stored Procedures. Although i haven't done any analysis of time difference between the two. But i do have few reasons to move to stored procedures: 1) I work on a security application and i heard of SQL injection. This is one of the most common problems with dynamic queries. But if i use stored procs, I won't have to worry about Sql injections, since queries in Stored Procs are precompiled. 2) Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component. 3) Lastly, stored procs add an extra layer of abstraction; i can keep the database access to storage and retrieval and move most of the business logic into a middle tier. Although i have the DB code in place as dynamically generated strings in code. I might have to do a lot of rework to implement stored procs. Is it really worth it to move to stored procs? If it's really a good idea to move my sql queries out of C# code into stored procs. I've the following problems: Problem 1: -- I have code in place that generates dynamic query like this: dbCmd.CommandText = UPDATE Table1 SET + setClause + WHERE ID = + _id; Table1 has about 15 fields and on the basis of what field is changed, i generate the setclause. Now if i have to do it in Stored Proc, i have to add a parameter for each field and update all the fields on the basis of the criteria. Which would be considered best practice in this case? Problem 2: I'm also using dynamic queries in case of SELECT queries. For example, i have to fetch records from Table1 on the basis of ID or Name or combination of both. dbCmd.CommandText = SELECT ID, Name, Field3, Field3, ..., + whereClause + ORDER BY Name;; a) To implement this in stored proc, i can either have different stored proc to get data from Table1 on the basis of criteria, or b) i can have all the criteria fields as parameters and have If statements to check which one is set and write a sql query for each case. What would be considered best practice in this case? Thanks.
RE: MYSQL: Unlimited Forums, Scalablity Issues, advice please? - Bayesian Filter detected spam
Let's try to characterize the load and sizing. If the posts are mostly text. 100 forums X 100,000 posts X 2,000 bytes per post = 20GB large but not huge We have people reading the posts. Even speed readers will take minutes to read the new posts. Maybe 1 in 10 - 100 readers will do a new post. A person writing a new post will take at least 5 minutes to type the 2000 bytes. So, while this site might have 100's to 1000's of concurrent users, the load profile on the database is much lower than a dynamic database driven web site where users are constantly searching/linking and the time spent on a specific page is seconds. I suspect if you watch some typical forum activity and build a crude database interaction model, you will find that even with 1000's of connected users the database server will need to run less than 100 relatively simple selects per second and many of them will be served by the query cache. This is a very rushed analysis with lots of assumptions, but if close then I think you are looking at at most a pair of dual dual-core 4GB systems running Master Slave replication. -Original Message- From: J. Pow [mailto:[EMAIL PROTECTED] On Behalf Of jay Sent: Monday, February 27, 2006 6:06 PM To: Philip Hallstrom Cc: mysql@lists.mysql.com Subject: [SPAM] - Re: MYSQL: Unlimited Forums, Scalablity Issues, advice please? - Bayesian Filter detected spam Hi Philip, thanks for the reply. Single master + many read only slaves would only solve the problem of handling many many concurrent read accesses, by distributing the load across all slaves. However, I guess the real problem, is that the writes would still need to be performed across ALL databases, and the DB would be HUGE, would it not? Lets say I host 100 forums, with 100k posts each, every write would need to be replicated to as many slaves as I have. Thanks! Jay Philip Hallstrom wrote: Hi there, I am in the midst of creating a forums hosting site. 1. It has to be highly scalable, so I doubt a single MYSQL db with TONS of subforums that simulate full forums would do. 2. Also regarding scalablity, I hope to Add capacity as and when its needed. So i'll have one server running initially, and when it gets too crowded, i'll get two servers etc. 3. I will be providing a user with a dashboard that allows him to view all his subscribed posts across ALL forums. So lets say a user is a member of 25 forums, this dashboard view will allow the user to view all his posts across all the forums. Does anyone have advice that could point me in the right direction? I have solved the scalability issue WITHIN a forum (code can handle million + posts easy), but I havent solved the issue of scaling MULTIPLE separate forums. What about having a single write master with many read-only slaves? Then modify your code so that posts go to the master and everything else happens on the slaves? Also, does there exist any php package that helps ease the process of deciding which Server/database to connect to? For example, someone accesses FORUM A, so the script would automatically know to direct all queries to the DB in SERVER 1 etc, and if i try to access FORUM J, it would connect to SERVER 2 etc. I could easily hard code this, but I was thinking what if internal IP addresses change, or I decide to migrate a busy forum to a server of its own etc, so perhaps there is a better available packaged solution designed for this task. Create a table on a central server that contains this mapping. This server could also hold the login tables as well... Just a thought. -- 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: Dynamic queries v/s Stored Procs
Thanks for the answer. Now let me get this right, are you suggesting using prepared statements from the C# code? Since I have seen some examples of prepared statements used in stored procedures too. I understand stored procedures are not best tool in all situations. But what if while inserting records, I have certain operations that need to be executed consecutively. But in case of queries written inside the code, I have separate database calls and that results in too many database calls. For example, 1) I insert data in member table and 2) Get the ID and 3) Insert a new row in entity table with that ID. I can do all that in one stored proc. Do you think it's a good idea to use stored procedure for certain cases and may be use prepared statements from my c# code in other. Lastly, from architecture point of view, is it considered good to have sql string embedded in C# code? Thanks, Reema -Original Message- From: James Black [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 28, 2006 10:54 AM To: rtroiana Subject: Re: Dynamic queries v/s Stored Procs -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 rtroiana wrote: Although i have the DB code in place as dynamically generated strings in code. I might have to do a lot of rework to implement stored procs. Is it really worth it to move to stored procs? You may want to use prepared statements instead of stored procedures, as it does protect against sql injection. It obviously is as much work to change as a dynamic query, but I don't think stored procedures are the best tool in all situations. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEBHIpikQgpVn8xrARAxf+AJ9Vj+IsMrTXAaIYgB3fHrEZPJZv7ACfdu74 flJn+6NSRt5cLZIfRauZUhY= =cUPJ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: jdbc:mysql
Not sure yet. Could it be that you're behind the router? And you might need to redirect the traffic to the model to the IP of your PC and you're PC's IP should be set static so that next time when you reboot, it won't change. Xiaobo Hi, I have a problem connecting to MySQL. I could connect to the database with this sentence: jdbc:mysql://localhost:3306/db but not with this sentence, with the same IP that the localhost: jdbc:mysql://192.xxx.xxx.xxx:3306/db What's wrong? Thanks for all, Amaia -- 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: jdbc:mysql
Is your mysql server listening just on localhost (127.0.0.1)? That is differnet than 192.xxx.xxx.xxx -Original Message- From: Xiaobo Chen [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 28, 2006 11:27 AM To: Amaia Anabitarte Cc: mysql@lists.mysql.com Subject: Re: jdbc:mysql Not sure yet. Could it be that you're behind the router? And you might need to redirect the traffic to the model to the IP of your PC and you're PC's IP should be set static so that next time when you reboot, it won't change. Xiaobo Hi, I have a problem connecting to MySQL. I could connect to the database with this sentence: jdbc:mysql://localhost:3306/db but not with this sentence, with the same IP that the localhost: jdbc:mysql://192.xxx.xxx.xxx:3306/db What's wrong? Thanks for all, Amaia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: jdbc:mysql
does the user that you're using has the enough privileges to connect from 192.xxx.xxx.xxx ??? Is your mysql server listening just on localhost (127.0.0.1)? That is differnet than 192.xxx.xxx.xxx -Original Message- From: Xiaobo Chen [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 28, 2006 11:27 AM To: Amaia Anabitarte Cc: mysql@lists.mysql.com Subject: Re: jdbc:mysql Not sure yet. Could it be that you're behind the router? And you might need to redirect the traffic to the model to the IP of your PC and you're PC's IP should be set static so that next time when you reboot, it won't change. Xiaobo Hi, I have a problem connecting to MySQL. I could connect to the database with this sentence: jdbc:mysql://localhost:3306/db but not with this sentence, with the same IP that the localhost: jdbc:mysql://192.xxx.xxx.xxx:3306/db What's wrong? Thanks for all, Amaia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: document
Please read the document. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Some general questions
I was asked to scope out the following features, and am not the person to be implementing them. Hence the manuals weren't particularly helpful. I'm looking for definitive references one way or the other for the following questions. SOS! Does MySQL support the following: - Support for DDL commands and dynamic SQL inside stored procedures? - EXECUTE STATEMENT in Stored Procedures. (may be same question as above) - Support for creation of databases inside stored procedures. - Support for UTF-8 data storage in VARCHAR columns. - Support of CLOBS or TEXT columns such that we can store up to 60,000 bytes of TEXT data _ On the road to retirement? Check out MSN Life Events for advice on how to get there! http://lifeevents.msn.com/category.aspx?cid=Retirement -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some general questions
I was asked to scope out the following features, and am not the person to be implementing them. Hence the manuals weren't particularly helpful. I'm looking for definitive references one way or the other for the following questions. SOS! Does MySQL support the following: - Support for DDL commands and dynamic SQL inside stored procedures? Yes. - EXECUTE STATEMENT in Stored Procedures. (may be same question as above) See above. - Support for creation of databases inside stored procedures. I don't know, but it should be easy to try :) - Support for UTF-8 data storage in VARCHAR columns. Yes. - Support of CLOBS or TEXT columns such that we can store up to 60,000 bytes of TEXT data Yes. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ 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]
Re: Some general questions
Does MySQL support the following: - Support for DDL commands and dynamic SQL inside stored procedures? Yes. - EXECUTE STATEMENT in Stored Procedures. (may be same question as above) Yes to a limited extent: PREPARE | EXECUTE accepts CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE and most database info (SHOW) commands. - Support for creation of databases inside stored procedures. Yes - Support for UTF-8 data storage in VARCHAR columns. Yes - Support of CLOBS or TEXT columns such that we can store up to 60,000 bytes of TEXT data Yes. PB http://www.artfulsoftware.com - J A wrote: I was asked to scope out the following features, and am not the person to be implementing them. Hence the manuals weren't particularly helpful. I'm looking for definitive references one way or the other for the following questions. SOS! Does MySQL support the following: - Support for DDL commands and dynamic SQL inside stored procedures? - EXECUTE STATEMENT in Stored Procedures. (may be same question as above) - Support for creation of databases inside stored procedures. - Support for UTF-8 data storage in VARCHAR columns. - Support of CLOBS or TEXT columns such that we can store up to 60,000 bytes of TEXT data _ On the road to retirement? Check out MSN Life Events for advice on how to get there! http://lifeevents.msn.com/category.aspx?cid=Retirement No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with Query Optimization
Hi All,
We use Vbulletin for our message boards, and I have a query which
takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread AS thread
LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid = deletionlog.
primaryid
AND TYPE = 'thread' )
WHERE forumid =98
AND sticky =0
AND visible =1
AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15
There is an index on the field 'lastpost' ( Type: INT(10) ) , also Explain
Select gives me this..
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
thread
index
*NULL*
lastpost
4
*NULL*
112783
Using where
1
SIMPLE
deletionlog
eq_ref
PRIMARY
PRIMARY
5
foxbox16.thread.threadid,const
1
Using where; Using index; Not exists
I am no SQL guru so can somebody kindly help me to optimize this query so
that it could run faster , as during peak times this slows the DB down a
lot..
Kishore Jalleda
Re: Some general questions
Thanks! With regard to the ClOBs or Text fields, is that CLOB support? Or support for Text columns that hold up to 60,000 bytes of TEXT data? From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: J A [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Some general questions Date: Tue, 28 Feb 2006 12:44:08 -0600 /Does MySQL support the following: - Support for DDL commands and dynamic SQL inside stored procedures? / Yes. /- EXECUTE STATEMENT in Stored Procedures. (may be same question as above) / Yes to a limited extent: PREPARE | EXECUTE accepts CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE and most database info (SHOW) commands. /- Support for creation of databases inside stored procedures. / Yes /- Support for UTF-8 data storage in VARCHAR columns. / Yes /- Support of CLOBS or TEXT columns such that we can store up to 60,000 bytes of TEXT data / Yes. PB http://www.artfulsoftware.com - J A wrote: I was asked to scope out the following features, and am not the person to be implementing them. Hence the manuals weren't particularly helpful. I'm looking for definitive references one way or the other for the following questions. SOS! Does MySQL support the following: - Support for DDL commands and dynamic SQL inside stored procedures? - EXECUTE STATEMENT in Stored Procedures. (may be same question as above) - Support for creation of databases inside stored procedures. - Support for UTF-8 data storage in VARCHAR columns. - Support of CLOBS or TEXT columns such that we can store up to 60,000 bytes of TEXT data _ On the road to retirement? Check out MSN Life Events for advice on how to get there! http://lifeevents.msn.com/category.aspx?cid=Retirement No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with Query Optimization
Kishore,
We use Vbulletin for our message boards, and I have a
query which
takes approximately 1.2 - 1.5 seconds to execute, on a table
('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread
LEFT JOIN deletionlog
ON ( thread.threadid = deletionlog.primaryid AND TYPE = 'thread' )
WHERE forumid = 98
AND sticky = 0
AND visible = 1
AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15
Your formatting of EXPLAIN output is a bit hard to read, but did you
try indexes on
(sticky,lastpost) and (forumid,sticky,visible)? And depending on how
big deletionlog is,
you might move ...
AND deletionlog.primaryid IS NULL
from the WHERE clause to the ON clause, to reduce the number of rows
the query engine
has to write to its temp table.
PB
-
Kishore Jalleda wrote:
Hi All,
We use Vbulletin for our message boards, and I have a query which
takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread AS thread
LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid = deletionlog.
primaryid
AND TYPE = 'thread' )
WHERE forumid =98
AND sticky =0
AND visible =1
AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15
There is an index on the field 'lastpost' ( Type: INT(10) ) , also Explain
Select gives me this..
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
thread
index
*NULL*
lastpost
4
*NULL*
112783
Using where
1
SIMPLE
deletionlog
eq_ref
PRIMARY
PRIMARY
5
foxbox16.thread.threadid,const
1
Using where; Using index; Not exists
I am no SQL guru so can somebody kindly help me to optimize this query so
that it could run faster , as during peak times this slows the DB down a
lot..
Kishore Jalleda
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some general questions
Thanks! With regard to the ClOBs or Text fields, is that CLOB support? Or support for Text columns that hold up to 60,000 bytes of TEXT data? In MySQL they're 'BLOB's not 'CLOB's, see http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html. PB http://www.artfulsoftware.com - J A wrote: Thanks! With regard to the ClOBs or Text fields, is that CLOB support? Or support for Text columns that hold up to 60,000 bytes of TEXT data? From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: J A [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Some general questions Date: Tue, 28 Feb 2006 12:44:08 -0600 /Does MySQL support the following: - Support for DDL commands and dynamic SQL inside stored procedures? / Yes. /- EXECUTE STATEMENT in Stored Procedures. (may be same question as above) / Yes to a limited extent: PREPARE | EXECUTE accepts CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE and most database info (SHOW) commands. /- Support for creation of databases inside stored procedures. / Yes /- Support for UTF-8 data storage in VARCHAR columns. / Yes /- Support of CLOBS or TEXT columns such that we can store up to 60,000 bytes of TEXT data / Yes. PB http://www.artfulsoftware.com - J A wrote: I was asked to scope out the following features, and am not the person to be implementing them. Hence the manuals weren't particularly helpful. I'm looking for definitive references one way or the other for the following questions. SOS! Does MySQL support the following: - Support for DDL commands and dynamic SQL inside stored procedures? - EXECUTE STATEMENT in Stored Procedures. (may be same question as above) - Support for creation of databases inside stored procedures. - Support for UTF-8 data storage in VARCHAR columns. - Support of CLOBS or TEXT columns such that we can store up to 60,000 bytes of TEXT data _ On the road to retirement? Check out MSN Life Events for advice on how to get there! http://lifeevents.msn.com/category.aspx?cid=Retirement No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE from monthly to yearly rows
Cor, I need to put all available monthly Values from Updates to 1 Data record where MyKey and Year are equal. IOW you want to save the results of the business end of a crosstab (pivot table) query. The crosstab analysis will require a full query. MySQL has an INSERT ... SELECT command, but no UPDATE ... SELECT command, so this will be a two-step. If I understand your description correctly, you want to aggregate by month and report by mykey and year, so your crosstab would look something like this (not tested)... CREATE TEMPORARY TABLE crosstab SELECT d.myKey, d.year, SUM(IF(u.month=1 ,u.value,0)) AS jan, SUM(IF(u.month=2 ,u.value,0)) AS feb, ... etc ... SUM(IF(u.month=12,u.value,0)) AS dec) FROM data AS d INNER JOIN updates AS u USING (myKey) GROUP BY mykey,year; aggregating updates to one row per mykey per year. Then update the data table with something like ... UPDATE Data AS d INNER JOIN crosstab AS c ON d.myKey = c.myKey AND d.year = c.year SET d.Jan = c.jan ... etc ... PB - C.R.Vegelin wrote: Hi List, Please help me with the following problem in MySQL 5.0.15. I have 2 MyISAM tables like: - table Updates with fields myKey, Year, Month, Value where Month has the values 1 .. 12 - table Data with fields myKey, Year, Jan, Feb, ... Dec I need to put all available monthly Values from Updates to 1 Data record where MyKey and Year are equal. I tried the following query: UPDATE Data AS db INNER JOIN Updates AS U ON db.myKey = U.myKey SET db.Jan = IF(U.Month = 1, U.Value, db.Jan), db.Feb = IF(U.Month = 2, U.Value, db.Feb), ... db.Dec = IF(U.Month=12, U.Value,db.Dec); But this query takes only the first available Month in Updates, and ignores the other months per myKey / Year combination. I would appreciate your help. TIA, Cor No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to pass a string expression into a stored procedure
I have a SELECT query that looks similar to the following:
SELECT
FirstName,
LastName
FROM
myTable
WHERE
LastName IN ('PRICE', 'SMITH');
What I want to do is create a stored procedure for this SELECT query
similar to the following:
CREATE PROCEDURE spGetNames (IN strNames VARCHAR(255))
BEGIN
SELECT
FirstName,
LastName
FROM
myTable
WHERE
LastName IN (strNames);
END
And then I would like to call it like so:
CALL spGetNames ('PRICE,SMITH');
This does not work, neither does:
CALL spGetNames ( 'PRICE', 'SMITH' );
My question is how to format the string parameter so this query works.
Any help is greatly appreciated.
Thanks,
Randall Price
VT.SETI.IAD.MIG : Microsoft Implementation Group
http://vtmig.w2k.vt.edu http://vtmig.w2k.vt.edu/
* [EMAIL PROTECTED]
' (540) 231-4396
Re: bind-address by name under 5.0.18
Hi James,
Sort of. If I run the command manually, it does.
But my script uses su to run the server as an unprivileged user.
What happens if you:
su -m mysql -c 'sh -c /usr/local/bin/mysqld_safe
--defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql
--pid-file=/var/db/mysql/www.example.com.pid --bind-address=localhost /dev/null '
Hmm, I don't see why it shouldn't work when run inside su... odd.
As far as I know, there are no requirements for superuser in order to
bind to certain IPs.
I'm also hopeful to get multi-IP support, so that I can have the
server listen on two NICs or what-have-you, such as:
[mysqld]
bind_address=foo bar localhost
This is a reasonable feature request, and I'll try to do it (I don't see
why it can't work). It would require some changes to how the address to
bind to are passed around (currently a variable called my_bind_addr).
I haven't tried that yet, so perhaps it is already functional as
well, I'll try to get to testing that soon.
It's not. Here's the code that handles bind-address from sql/mysqld.cc:
6733case (int) OPT_BIND_ADDRESS:
6734 if ((my_bind_addr= (ulong) inet_addr(argument)) == INADDR_NONE)
6735 {
6736struct hostent *ent;
6737if (argument[0])
6738 ent=gethostbyname(argument);
6739else
6740{
6741 char myhostname[255];
6742 if (gethostname(myhostname,sizeof(myhostname)) 0)
6743 {
6744sql_perror(Can't start server: cannot get my own
hostname!);
6745exit(1);
6746 }
6747 ent=gethostbyname(myhostname);
6748}
6749if (!ent)
6750{
6751 sql_perror(Can't start server: cannot resolve hostname!);
6752 exit(1);
6753}
6754my_bind_addr = (ulong)
((in_addr*)ent-h_addr_list[0])-s_addr;
6755 }
6756 break;
And with other DBs, I can set 'sql.example.com' to resolve to N
IPs (N=4 in this example):
10.0.0.10
10.0.1.10
10.0.2.10
10.0.3.10
Then I can have N separate hosts share the same start-up script,
because the SQL server binds only to those IPs which are local. Since
each of the N servers has only one of those IPs locally, each
server automatically binds to the correct IP. And if the IPs ever
change, I just update DNS, and there's no need to do any maintenance
on the start-up scripts. These IPs would not necessarily be in the
same subnet and in fact, are CNAMEs, such as sql.example.com
defined as:
sql.example.com.IN CNAME dallas-sql.example.com
sql.example.com.IN CNAME cleveland-sql.example.com
sql.example.com.IN CNAME portland-sql.example.com
sql.example.com.IN CNAME sydney-sql.example.com
I am grateful to be making progress. Thank you for your assistance.
Hmm, I guess you'd expect to at least get warnings for each of these
that couldn't be bound to?
This seems like a very strange way to configure a machine. To each, his
own. :)
Regards,
Jeremy
--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cleaning Bin-Log Files
Does anyone know of a method or script that will automatically delete bin-log files after x days? I've got a few customers, all utilizing a master and slave server... my problem is that the harddrives often get filled to capacity if they go unchecked over a few months. Most of this is attributed to the binlogs which fill to about 1GB and then starts a new log. We're using MySQL 4.026. Any thoughts?
Re: bind-address by name under 5.0.18
6752 exit(1); 6753} 6754my_bind_addr = (ulong) ((in_addr*)ent-h_addr_list[0])-s_addr; 6755 } 6756 break; And with other DBs, I can set 'sql.example.com' to resolve to N IPs (N=4 in this example): 10.0.0.10 10.0.1.10 10.0.2.10 10.0.3.10 Then I can have N separate hosts share the same start-up script, because the SQL server binds only to those IPs which are local. Since each of the N servers has only one of those IPs locally, each server automatically binds to the correct IP. And if the IPs ever change, I just update DNS, and there's no need to do any maintenance on the start-up scripts. These IPs would not necessarily be in the same subnet and in fact, are CNAMEs, such as sql.example.com defined as: sql.example.com.IN CNAME dallas-sql.example.com sql.example.com.IN CNAME cleveland-sql.example.com sql.example.com.IN CNAME portland-sql.example.com sql.example.com.IN CNAME sydney-sql.example.com I am grateful to be making progress. Thank you for your assistance. Hmm, I guess you'd expect to at least get warnings for each of these that couldn't be bound to? A *warning*, yes. There's certainly no way to bind to an IP that isn't local. However, if the IP *is local*, and yet MySQL *still* can't bind to it, that should be an *error*. This seems like a very strange way to configure a machine. To each, his own. :) The advantage is that all the machines can have exactly the same configuration (except for the ifconfig IP). The sql start-up is exactly the same, and thus you only maintain 1 script instead of N. Another case where this would be handy is on a machine with multiple NICs on various LANs. One might set a DNS to resolve to the N different IPs that machine has, and then tell the SQL server to bind to that DNS name. The SQL server could then automatically answer queries on each of the IPs specified in DNS. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleaning Bin-Log Files
- Original Message -
From: Shaun Adams [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, February 28, 2006 5:19 PM
Subject: Cleaning Bin-Log Files
Does anyone know of a method or script that will automatically delete
bin-log files after x days? I've got a few customers, all utilizing a
master and slave server... my problem is that the harddrives often get
filled to capacity if they go unchecked over a few months. Most of this
is
attributed to the binlogs which fill to about 1GB and then starts a new
log.
We're using MySQL 4.026.
Any thoughts?
This may be excessive for what you are trying to do but it's a starting
point.
The following bash script is one that I run every day via a cron job. (You
didn't say what OS your server is; mine is Linux Mandrake so I use cron for
scheduling.) The script is fairly heavily commented so you'll probably
understand it if you know bash but feel free to ask followup questions if
things aren't clear. Basically, the script gets the names of each of the
databases on the system, does a backup via mysqldump, then lists all backups
older than a given number of days, then deletes those older backups. It
writes a short report and emails it to me and the system administrator for
the server. It's run without problems for several years now.
You're not worried about database backups so you can probably remove the
do/done and simply list all the bin-logs that meet your criteria, then
delete them. You may not want to bother sending that email but you could use
cron to schedule this script to run as often as you like.
#!/bin/bash
#This script makes a separate database-level backup of each of the current
MySQL databases and
#deletes backups older than a certain number of days.
#This script is normally invoked via a cron job so that it runs once per day
in the middle of the night.
#The crontab entry looks like this:
#0 3 * * * sh /home/rhino/MySQL/backup2.bash /home/rhino/MySQL/backup2.out
21; cat /home/rhino/MySQL/backup2.out | sendEmail -f [EMAIL PROTECTED] -t
[EMAIL PROTECTED] [EMAIL PROTECTED]
-u MySQL Backup Report
USERID=myuserid; #The userid to use for creating the backup
PASSWORD=mypasswd; #The password to use for creating the backup
BACKUP_TIMESTAMP=`/bin/date +%Y%m%d-%H%M%S`; #The timestamp
(MMDD-HHMMSS) of the backup
BACKUP_PATH=/home/rhino/MySQL/backup; #The directory into which the backup
will be written
NUMBER_OF_DAILY_BACKUPS_TO_KEEP=7; #The number of generations of backups to
keep
echo ** REPORT BEGINS **;
echo
echo Program Name: $0
report_date=`/bin/date`
echo Report Date: $report_date;
echo
#Display the non-secret values used in this run.
echo Backup Values:;
echo Backup timestamp is $BACKUP_TIMESTAMP;
echo Backup path is $BACKUP_PATH;
echo Number of daily backups to keep = $NUMBER_OF_DAILY_BACKUPS_TO_KEEP;
#For each database currently in MySQL, take a database-level backup, then
list any backups older than a certain number of days, then delete those old
backups.
for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
echo
echo Backing up database $ONE_DBNAME;
/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD}
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql
echo Deleting these old backups for this database...
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print; #display old
backups (if any)
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}' ';';
#delete old backups (if any)
done
echo
echo ** REPORT ENDS **;
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.1/271 - Release Date: 28/02/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some general questions
Thanks! With regard to the ClOBs or Text fields, is that CLOB support? Or support for Text columns that hold up to 60,000 bytes of TEXT data? In MySQL they're BLOBs not CLOBs, see http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html. PB http://www.artfulsoftware.com No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Silicon Valley MySQL Meetup Group: Storage Engines in Depth
Interested in MySQL? Live in peninsula/south bay? This MySQL Meetup is for you! Join us for the March MySQL Meetup on March 13th at 7pm in Santa Clara! The topic for this month is Storage Engines In-Depth! I'll cover the details of both MyISAM and InnoDB, how each works, its strengths and weaknesses, and proper configuration of each. I'll wrap up by talking about the future of storage engines in MySQL -- some of the new work going on in 5.0 and 5.1, and some demos! The topics each month are varied, but you can be sure to learn a lot at every meeting. Find out the next meeting dates, the topics, and RSVP at Meetup.com: http://mysql.meetup.com/101/ I hope to see you there! Regards, Jeremy Cole MySQL Meetup Organizer Silicon Valley -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to change root password
Hi everyone, I was installing drupal on my webserver with mysql after i followed instructions on how to configure and setup drupal i cant't access the root password of my mysql. my password won't work all. Any idea how to change my password of root? or is is passible that my mysql is currupted? Any help? Thank you. :' ' ' ' ': Arnel G. Pastrana ::' : [EMAIL PROTECTED] . ` '` `- Debian - when you have better things to do than fixing a system. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to change root password
On 2/26/06, Arnel Pastrana [EMAIL PROTECTED] wrote: Hi everyone, I was installing drupal on my webserver with mysql after i followed instructions on how to configure and setup drupal i cant't access the root password of my mysql. my password won't work all. Any idea how to change my password of root? or is is passible that my mysql is currupted? Any help? http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
file permission
Hi, When I use a query SELECT * into outfile '/tmp/report.csv' fields terminated by ',' lines terminated by '\n' FROM table;, it will create a file with the following permission and owner. -rw-rw-rw- 1 mysql mysql 2489 Mar 1 11:30 report.csv How can I change the default permission or the ownership of the file? Rgds, unplug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: file permission
On Tuesday 28 February 2006 22:55, unplug wrote: Hi, When I use a query SELECT * into outfile '/tmp/report.csv' fields terminated by ',' lines terminated by '\n' FROM table;, it will create a file with the following permission and owner. -rw-rw-rw- 1 mysql mysql 2489 Mar 1 11:30 report.csv How can I change the default permission or the ownership of the file? Rgds, unplug chown your_username:your_username report.cvs Of course change the your_username to the user you are logged in as. -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: file permission
Thanks! But I want to change the default setting of the file permission and ownership. (i.e. the file will be created with permission and ownerhsip according to my setting). On 3/1/06, CodeHeads [EMAIL PROTECTED] wrote: On Tuesday 28 February 2006 22:55, unplug wrote: Hi, When I use a query SELECT * into outfile '/tmp/report.csv' fields terminated by ',' lines terminated by '\n' FROM table;, it will create a file with the following permission and owner. -rw-rw-rw- 1 mysql mysql 2489 Mar 1 11:30 report.csv How can I change the default permission or the ownership of the file? Rgds, unplug chown your_username:your_username report.cvs Of course change the your_username to the user you are logged in as. -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) -- 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: file permission
You can't do that. The mysql server runs as user mysql, so it cannot create files owned by another user. If you run the mysql server as root (don't!), you might be able to do that, but I do not believe mysql has a facility for changing the owner of a file. You are better off running a script as the user you want to own the file and outputing the results via the script. j- k- On Tuesday 28 February 2006 19:18, unplug wrote: Thanks! But I want to change the default setting of the file permission and ownership. (i.e. the file will be created with permission and ownerhsip according to my setting). On 3/1/06, CodeHeads [EMAIL PROTECTED] wrote: On Tuesday 28 February 2006 22:55, unplug wrote: Hi, When I use a query SELECT * into outfile '/tmp/report.csv' fields terminated by ',' lines terminated by '\n' FROM table;, it will create a file with the following permission and owner. -rw-rw-rw- 1 mysql mysql 2489 Mar 1 11:30 report.csv How can I change the default permission or the ownership of the file? Rgds, unplug chown your_username:your_username report.cvs Of course change the your_username to the user you are logged in as. -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Joshua Kugler PGP Key: http://pgp.mit.edu/ CDE System Administrator ID 0xDB26D7CE http://distance.uaf.edu/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: file permission
On Tuesday 28 February 2006 23:41, Joshua Kugler wrote: You can't do that. The mysql server runs as user mysql, so it cannot create files owned by another user. If you run the mysql server as root (don't!), you might be able to do that, but I do not believe mysql has a facility for changing the owner of a file. You are better off running a script as the user you want to own the file and outputing the results via the script. j- k- I was just going to post something similar to that. Try something like this: #!/bin/bash Your query you want to run... chown user:user /path/to/report.cvs Then put this in your cron.daily or where ever you wish to run it or crontab 00 03 * * * /path/to/script I think I explained that right. :) -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: file permission
Actually, here is my case. There is a script owned by user A running daily. script--- rm -rf /tmp/report.csv mysql -umysql -pabc table -e SELECT * into outfile '/tmp/report.csv' fields terminated by ',' lines terminated by '\n' FROM table; In the first run, a file with the following permission and ownership will be created. -rw-rw-rw- 1 mysql mysql 2489 Mar 1 11:30 report.csv In the seconde run, the script will failed as user A have no permission to remove the file. In order to run the script daily, ownership of the file should be changed to user A. On 3/1/06, CodeHeads [EMAIL PROTECTED] wrote: On Tuesday 28 February 2006 23:41, Joshua Kugler wrote: You can't do that. The mysql server runs as user mysql, so it cannot create files owned by another user. If you run the mysql server as root (don't!), you might be able to do that, but I do not believe mysql has a facility for changing the owner of a file. You are better off running a script as the user you want to own the file and outputing the results via the script. j- k- I was just going to post something similar to that. Try something like this: #!/bin/bash Your query you want to run... chown user:user /path/to/report.cvs Then put this in your cron.daily or where ever you wish to run it or crontab 00 03 * * * /path/to/script I think I explained that right. :) -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) -- 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]
