Mysql database capacity
Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas
RE: Mysql database capacity
Hi Vishwas These and many other answers can be found here : http://dev.mysql.com/doc/refman/5.0/en/what-is.html There is no practical limitation on the row size apart from the number of fields x the size of these fields. eg. 1000 fields of varchar(255) or larger will take up that space. I don't know your table description so can't say. I have referenced the version 5.0 manual as I'm not sure of your version. How long to search a record in 1 billion rows? That is very much dependent on how you are searching, which index you are using etc. It could take as little as .01 of a second if you use the primary key to access the record directly. For the number of records, it is dependent on the size of the record. Unfortunately there is very little information in your email to be able to make a judgement. I would suggest you see the limitations in the url above and divide your record size into that. 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: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, 9 January 2006 8:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I keep character_set_client value after MyODBC auto reconnect?
Hello: I found that MyODBC use mysql_ping to check connection and reconnect. After reconnect by using mysql_ping character_set_client, character_set_connection and character_set_results go back to latin1. But I need them to be utf8. What can I do with MyODBC? Regards, gu lei 祝 事业有成，家庭和睦，身体健康，一切吉祥 古雷 --- 中企动力科技集团 技术事业发展部___技术架构部 \__企业IP通讯部 电话：010 58022278-302 地址：北京亦庄经济技术开发区北工大软件园 （地盛北街1号）A区3号楼 邮编：100176
RE: Mysql database capacity
I am giving the system configuration which we are using to run MySQL. 2-CPU 4G RAM, SAN filesystem. MySQL version : 4.14 (INNODB) OS : RHEL - 3 Amount of Data : 200G No of Rows : 278 million approximately (Every day 2.5-3 million rows gets added) Transaction rate : 300-400 reads/sec, 110-120 updates/sec, 80 inserts/sec Tables : 1 (only 1) This mysql is handling comfortably. So I think this info might help you to get a good idea about planning your system resources. I am not very much sure about the maximum rows/data it can support, but I am sure it can easily handle a billion of rows in a single table. Regards sujay -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 3:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql database capacity
Hi Sujay, Can you please post details like the my.cnf configs, how is the load on system like cpu, memory, disk usage etc. Thanx in advance --Alex On Mon, 09 Jan 2006 15:16:39 +0530, Sujay Koduri [EMAIL PROTECTED] wrote: I am giving the system configuration which we are using to run MySQL. 2-CPU 4G RAM, SAN filesystem. MySQL version : 4.14 (INNODB) OS : RHEL - 3 Amount of Data : 200G No of Rows : 278 million approximately (Every day 2.5-3 million rows gets added) Transaction rate : 300-400 reads/sec, 110-120 updates/sec, 80 inserts/sec Tables : 1 (only 1) This mysql is handling comfortably. So I think this info might help you to get a good idea about planning your system resources. I am not very much sure about the maximum rows/data it can support, but I am sure it can easily handle a billion of rows in a single table. Regards sujay -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 3:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Porting ISAM application with HANDLER syntax - bad performance
Hi, I'm porting a D-ISAM application to Mysql. My idea is to emulate ISAM calls with the HANDLER syntax. But I wish to make sure that performance will be at least as good as direct ISAM calls. Then I did some tests, scanning a simple table containing 6000 records with a small C program. Here are the results : SELECT * FROM mytable (first time) 7.48s SELECT * FROM mytable (cached) 0.15s HANDLER 7.67s Direct ISAM calls 0.37s I wonder if it is normal that HANDLER takes so long, and especially that HANDLER is so slow compared to direct ISAM calls. Do some of you have experience of HANDLER usage ? Christophe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: allocate space for innodb innodb_file_per_table
George, - Original Message - From: George Law [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Sunday, January 08, 2006 10:10 PM Subject: allocate space for innodb innodb_file_per_table Hi All, I am running mysql 5.0.18 with a innodb table of 9 GB (plus several others) on suse linux 9.3 with 4 GB ram. when doing a show table status command, this table shows Data_Free:0 I assume this is because it is the file per table setting, where the tables fall outside of the main ibdata files. Data_free = 0 in this case means that the whole .ibd file is currently allocated for that table. If you delete many rows from the table, you may see Data_free bigger than 0. The ibdata files are all preallocated when mysql starts up, is there anyway to preallocate the file per table files as well? No. Is there a specific innodb list? There is an InnoDB forum: http://forums.mysql.com/list.php?22 Thanks to Gleb's answer and the hint on the bulk load setting in my.cnf - that seems to have helped some what. I notice in the load data command there is a CONCURRENT option - but as far as I can tell, that only does MyISAM tables? You also suggested disablign the keys. Disabling keys has no effect on InnoDB type tables. I think I tried that, but with a table with 7 mil rows, that takes longer than the load data :-\ TIA. George Law Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql database capacity
Sujay , Tables : 1 (only 1) ::: Innodb or Myisam ? I see lots of updates happening on that table so , how frequent do you defragment the table . -- Thanks Praj On Mon, 9 Jan 2006 01:46:39 -0800 Sujay Koduri [EMAIL PROTECTED] wrote: I am giving the system configuration which we are using to run MySQL. 2-CPU 4G RAM, SAN filesystem. MySQL version : 4.14 (INNODB) OS : RHEL - 3 Amount of Data : 200G No of Rows : 278 million approximately (Every day 2.5-3 million rows gets added) Transaction rate : 300-400 reads/sec, 110-120 updates/sec, 80 inserts/sec Tables : 1 (only 1) This mysql is handling comfortably. So I think this info might help you to get a good idea about planning your system resources. I am not very much sure about the maximum rows/data it can support, but I am sure it can easily handle a billion of rows in a single table. Regards sujay -Original Message- From: vishwas kharajge [mailto:[EMAIL PROTECTED] Sent: Monday, January 09, 2006 3:05 PM To: mysql@lists.mysql.com Subject: Mysql database capacity Hi all I am working with startup company. Have some quries about Mysql 1. What is the maximum database storage capacity of mysql 2. What is the maximum row capacity? 3. How much time it will take to search the record if there are consider more than 1 billion rows in a table 4. How many records can i store in a single table. Please help me. Thanks in advance Vishwas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Praj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: working w/UK postcodes
I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? The only effective way to do this is with geographic coordinates (your XY, I guess). While US zip codes _generally_ increase east to west and _usually_ have a numeric difference somewhat related to their geographic distance, this isn't always true. I know of two island zip codes (unrelated to those that surround it) in New York State. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: working w/UK postcodes
Yes, after some further research, I found a Perl Modules that handles this queit nicely. thx's [EMAIL PROTECTED] wrote: I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? The only effective way to do this is with geographic coordinates (your XY, I guess). While US zip codes _generally_ increase east to west and _usually_ have a numeric difference somewhat related to their geographic distance, this isn't always true. I know of two island zip codes (unrelated to those that surround it) in New York State. -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to update record obtained from a query result?
Jan M schreef: How do I update a record obtained from a query result while ensuring that: 1) The record is the actual record in the database not a possible duplicate, e.g. is there a built-in record number identifying the actual DB record or do I have to organise that in the table structure. There is no built-in record number, but many use AUTO-INCREMENT fields as a unique record ID. In many queries you will retrieve data from more than one table. The result set may thus consist of combined and possibly generated data from more than one table and so you not have the actual record as a single entity. 2) The record hasnt been or cannot be changed by another thread/process, possibly by. locking / re-reading the record (is there another way?). InnoDB (an engine type, see online manual) provides a mechanism called transactions which allow you to lock the rows involved in your query (so others cannot change the data), update them, etc. After you complete your transaction the changes are committed to the database. In case of an problem you can roll back the transaction. Regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
RE: working w/UK postcodes
Hi, There is a database available which maps post codes to grid references. This is controlled by the Royal Mail. See http://www.royalmail.com/portal/rm/jump2?mediaId=400088catId=400084 This may also be interesting: http://www.jibble.org/ukpostcodes/ In case you don't know, UK post codes consist of between 5 and seven characters which are in four parts with increasing resolution: City/Area Code (one or two letters) District (one or two digits) Sector (one digit) Unit (two letters) See http://www.equibrand.co.uk/postcodes.html They are usually written with a space between the District and Sector. Eg: W1 2AA MK6 3AB SN25 1DD This structure means you can easily give a central location for a partial postcode, ie right truncated to the sector, district or just the city. Hope this helps, Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 January 2006 13:12 To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: working w/UK postcodes I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? The only effective way to do this is with geographic coordinates (your XY, I guess). While US zip codes _generally_ increase east to west and _usually_ have a numeric difference somewhat related to their geographic distance, this isn't always true. I know of two island zip codes (unrelated to those that surround it) in New York State. -- 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: working w/UK postcodes
Andy, thank you, this is very helpful understanding the UK postcode scheme, appreciate your input. Andy Eastham wrote: Hi, There is a database available which maps post codes to grid references. This is controlled by the Royal Mail. See http://www.royalmail.com/portal/rm/jump2?mediaId=400088catId=400084 This may also be interesting: http://www.jibble.org/ukpostcodes/ In case you don't know, UK post codes consist of between 5 and seven characters which are in four parts with increasing resolution: City/Area Code (one or two letters) District (one or two digits) Sector (one digit) Unit (two letters) See http://www.equibrand.co.uk/postcodes.html They are usually written with a space between the District and Sector. Eg: W1 2AA MK6 3AB SN25 1DD This structure means you can easily give a central location for a partial postcode, ie right truncated to the sector, district or just the city. Hope this helps, Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 January 2006 13:12 To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: working w/UK postcodes I need to do a search query on the columns called Name of business the name of the business Town Local town or city name Postcode this is the same as your zip code Categorythis is type of business ie dress shop bakers XY this is the table of geo address maped by postcode I need the search to give me acurate results by the above fields. Example: a user is searching Heywood postcode ol10 1jb and category hotel, We need to see the nearest hotel to that post code and then the next nearest and so on up to 24 hotels Is there way to query this in the same manner one would do it with US zipcodes? The only effective way to do this is with geographic coordinates (your XY, I guess). While US zip codes _generally_ increase east to west and _usually_ have a numeric difference somewhat related to their geographic distance, this isn't always true. I know of two island zip codes (unrelated to those that surround it) in New York State. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: remotely show databases
Hi Anthony, I am not sure if you have an installation of MySQL on your local server. If you do then you can try to use something like below to execute your SHOW DATABASES Local_server[path to your mysql/bin directory]/mysql --host=your_remote_host --user=your_user --password=your_password -e SHOW DATABASE Make sure that [EMAIL PROTECTED] has appropriate rights on your_remote_host (server) Best, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Ettinger Sent: Sunday, January 08, 2006 4:03 PM To: mysql@lists.mysql.com Subject: remotely show databases I know I can login via ssh and run $mysqlshow But I would then have to parse the outputted text, is there an easier way (I'm using Perl locally here). The pitfall of running it locally is that you DO have to password protect your database user since it's an outside connection to run SHOW DATABASES; I tried $man mysqlshow, but didn't see any easy way of simply returning a \n seperated list of databases. Any suggestions? I also need to do this for postgresql if anyone else knows of a standalone app that dumps the databases for a specific user. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [SPAM] - concat string and update question - Found word(s) remove list in the Text body
Try this UPDATE people SETphone = CASE WHEN LEFT(phone,4) = '405_' THEN MID(phone,5,20) WHEN LEFT(phone,3) = '405' THEN MID(phone,4,20) ELSE phone END FROM people WHERE LEFT(phone,3) = '405' AND LENGTH(phone) 7; This way you don't accidentally replace '405' contained in the rest of the phone number. Also, if the phone numbers contain punctuation you will need to change the '7' in the LENGTH criteria. You will have to replace 'people' and 'phone' with the appropriate table and column name respectively. -Original Message- From: 2wsxdr5 [mailto:[EMAIL PROTECTED] Sent: Friday, January 06, 2006 8:09 PM To: mysql@lists.mysql.com Subject: [SPAM] - concat string and update question - Found word(s) remove list in the Text body I have a table of people and their phone numbers, some have the area code and others do not. Everyone in this table lives in the same area code, so I would like to remove the area code from the phone number field. Basically replace '(405)' or '405-' with '' is there an easy way to do that in a query with out writing code? I know how to do it with code but would like an easier way if some one knows the SQL better than I do. -- 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: Are Mysql ODBC supporting connection pool?
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/connection-pool.html wangxu wrote: Are Mysql ODBC supporting connection pool? -- 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: triggers on 5.0.17 -- definer not fully qualified
Hello. This should be interesting for you: http://bugs.mysql.com/bug.php?id=16266 Ian Sales (DBA) wrote: - unfortunately, the set up demands that the master stays at 4.0. I can't change that. The triggers were working in 5.0.16. It's the new DEFINER that was added with 5.0.17 that seems to be causing the problem. - ian -- 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: How to update record obtained from a query result?
Hello.
1) The record is the actual record in the database not a possible
duplicate, e.g. is there a built-in record number identifying the
actual DB record or do I have to organise that in the table structure.
Perhaps, you should add UNIQUE key to you table structure. See:
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
2) The record hasnt been or cannot be changed by another
thread/process, possibly by. locking / re-reading the record (is there
another way?).
MySQL provides different kinds of locking. See:
http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
Jan M wrote:
Hi,
Newbie question (MySql 5.0 using C API). I've searched the mysql
website/Internet but cannot find the answer.
How do I update a record obtained from a query result while ensuring that:
1) The record is the actual record in the database not a possible duplicate,
e.g. is there a built-in record number identifying the actual DB record or
do I have to organise that in the table structure.
2) The record hasnt been or cannot be changed by another thread/process,
possibly by. locking / re-reading the record (is there another way?).
How do I lock and re-read the [actual] record anyway?
Code snippet:
sprintf(szStr,
SELECT * FROM %s WHERE UserName = '%s',
DB_TABLE,
szUserName);
if(mysql_real_query(db, szStr, strlen(szStr)) == 0)
{
result = mysql_store_result(db);
if(result) // There are rows
{
while ((row = mysql_fetch_row(result)))
{
// *** I need to update the record here! ***
}
mysql_free_result(result);
}
TIA.
Jan
--
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: Lost Connection executing query
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Tripp Bishop wrote: 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 -- 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: allocate space for innodb innodb_file_per_table
Hello. Is there a specific innodb list? There is an InnoDB forum: http://forums.mysql.com/list.php?22 disablign the keys. I think I tried that, but with a table with 7 mil rows, that takes longer than the load data :-\ Have you increased the myisam_sort_buffer_size variable to speed up the rebuilding of the keys? I'm unable to give an answer on your question about preallocating the ibdata files, but this link should be useful if you want to optimize InnoDB performance: http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html George Law wrote: Hi All, I am running mysql 5.0.18 with a innodb table of 9 GB (plus several others) on suse linux 9.3 with 4 GB ram. when doing a show table status command, this table shows Data_Free:0 I assume this is because it is the file per table setting, where the tables fall outside of the main ibdata files. The ibdata files are all preallocated when mysql starts up, is there anyway to preallocate the file per table files as well? Is there a specific innodb list? Thanks to Gleb's answer and the hint on the bulk load setting in my.cnf - that seems to have helped some what. I notice in the load data command there is a CONCURRENT option - but as far as I can tell, that only does MyISAM tables? You also suggested disablign the keys. I think I tried that, but with a table with 7 mil rows, that takes longer than the load data :-\ TIA. George Law -- 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: error in script
Hello.
You should install MySQL support in PHP. See:
http://dev.mysql.com/doc/refman/5.0/en/php-problems.html
Jon Miller wrote:
I'm a newbie to MySQL so please bear with me. I'm creating a program from =
a script I found and I'm wondering why I keep getting the same error =
message.
I can connect to the mysql server while I'm ssh to the server, I also can =
run phpmyadmin from my windows desktop and see the databases and create =
databases, tables and input the data. =20
I'm using Apache2, PHP4 and MySQL Ver 12.22 Distrib 4.0.24, for pc-linux-gn=
u (i386)
Thanks
Jon
Movie Database
Fatal error: Call to undefined function: mysql_connect() in /var/www/test=
5.php on line 8
/var/www/test5.php:
HTML
BODY
Movie DatabaseBR
pre
?php
// Connect to MySQL server
// User name is root and password is blank
$link =3D mysql_connect(192.168.2.16,mysql-username,mysql-password=
);
// Select the database
$db =3D mysql_select_db(movie_library, $link);
// Query the database for all fields from table 'movies'
$result =3D mysql_query(SELECT * FROM movies, $link);
// Loop through all the rows=20
while ($row =3D mysql_fetch_object($result))
{
// print the object for each row
print_r($row);
}
?
/pre
/BODY
/HTML
--
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: about mysql security
Hello. Searching in the manual didn't return me any pages which have five-tiered inside. If you're interested in MySQL security read this: http://dev.mysql.com/doc/refman/5.0/en/security.html http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html I get a concept about mysql security. It's named five-tiered access control.Can you tell me the detail of this concept? wangxu 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: triggers on 5.0.17 -- definer not fully qualified
Gleb Paharenko wrote: Hello. This should be interesting for you: http://bugs.mysql.com/bug.php?id=16266 Hi, The bug report implies that if you recreate the triggers on the slave (deleting them manually from the database directory), then it resolves the issue. Unfortunately, the problem I have is that the triggers themselves are not being replicated; they exist only on the 5.0.17 slave. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.16: updates not using index prefixes
Pete Harlan wrote: FYI, 4.1.16 appears not to be using prefixes of compound indexes when doing updates. Reverting to 4.1.15, or adding an index consisting of only the desired field, restores reasonable behavior. I have added feedback to a possibly-related bug, http://bugs.mysql.com/bug.php?id=15935, but wanted to send a message on the list in case someone is having trouble with 4.1.16 and doesn't know why, or is thinking of upgrading and might want to wait, or knows a solution to this problem besides downgrading. i noticed an increased load average on our servers, then i switched back to 4.1.15. now i'm waiting for 4.1.17 :) chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connections crashing MySQL?
We're running MySQL version 4.1.12 on Fedora Core 3 64-bit. we've been crashing; here is a mysqld.err file from one crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=335544320 read_buffer_size=131072 max_used_connections=2049 max_connections=2048 threads_connected=371 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4784112 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060108 14:43:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. [InnoDB crash recovery elided] - We have 6G of memory on the server, and we checked -- we're not running out of memory. I'm guessing that mysqld crashed because of that 2049th connection -- shouldn't it just refuse the connection, not crash? The variables in the mysqld.err match the /etc/my.cnf: [mysqld] old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port= 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 1024 thread_cache= 80 ft_min_word_len = 3 # Use this to prevent access via TCP/IP # skip_networking # Query Cache Settings - OFF due to overload of Session table query_cache_size = 32M query_cache_type = 2 # Log queries taking longer than long_query_time seconds long_query_time = 4 log-slow-queries = /var/lib/mysql/slow-queries.log log-error = /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 # when you change this recalculate total possible mysqld memory usage!! # key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections max_connections = 2048 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id = 15 max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 Any help is appreciated. We've been crashing around the same time every day, our busiest time of day. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: General Questions regarding mysql and php
I would totally agree with this. I moved from using Fedora Core 3 to SuSE 9.3 and haven't looked back. YaST is one of the best tools out there. With the stuff you have installed it would be best just to start over with a new install. :) jay -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Sun 1/8/2006 8:42 PM To: Andrew Burrows; MYSQL General List Subject: Re: General Questions regarding mysql and php Andrew, I used to be a big redhat fan - but if you are looking to totally bring everything up to date, I would suggested opensuse. having used redhat for years, Suse's not a big step - everything is still RPM based, however, Suse's admin tool, YaST, kicks butt :) Suse 10 comes with Apache 2, PHP5, and one of the latest 4.X versions of mysql. There are generic RPMs for mysql 5 on mysql's website - but I haven't used them - I am running 5.0.18, but using the binary distro because I needed to run both 4.x and 5.x at the same time to migrate some data. -- George Law - Original Message - From: Andrew Burrows [EMAIL PROTECTED] To: MYSQL General List mysql@lists.mysql.com Sent: Sunday, January 08, 2006 8:01 PM Subject: General Questions regarding mysql and php Hi MYSQL users, Just started playing with mysql apache php and other tricky stuff and have a few question to get me going after many years. Was wondering what the best GUI based administration tool is used today, I lasted used phpMyAdmin, is this still used or are there better applications available? Looking for some basic documentation on MYSQL could someone recommend something online or maybe a book?? I have an old system that will probably need upgrading. Apache 1.3 Mysql 3.22.32 Tomcat 3.1.1 Red Hat 8 Would you recommend upgrading this system or starting from scratch? Thanks in advance. Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: autocasting
(reading some old mail) Hi Jose, don't know if folks have answered your question. If you're using Mysql 5.0 or higher, you want to check out the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes -- http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html. Previous versions do not allow that. For case sensitivity, see http://dev.mysql.com/doc/refman/4.1/en/case-sensitivity.html -Sheeri On 12/21/05, Jose Soares [EMAIL PROTECTED] wrote: Hi all, I have some troubles about autocasting and unsensitive case. Look at this: mysql select data_fattura from 2005_ordini where data_fattura = 9911; | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | +--+ 926 rows in set (0.00 sec) and look at this: mysql select data_fattura from 2005_ordini where data_fattura = '-99-11'; | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | | -00-00 | +--+ 926 rows in set (0.00 sec) mysql select orecchio_ristampa,azienda from test; +---+--+ | orecchio_ristampa | azienda | +---+--+ | D | 006TS130 | | S | 006TS130 | | 0 | 006TS130 | | Y | 006TS130 | | X | 006TS130 | | E | 006TS130 | | 0 | 006ts130 | +---+--+ 7 rows in set (0.00 sec) mysql select orecchio_ristampa,azienda from test where orecchio_ristampa=0 and azienda='006ts130'; +---+--+ | orecchio_ristampa | azienda | +---+--+ | D | 006TS130 | | S | 006TS130 | | 0 | 006TS130 | | Y | 006TS130 | | X | 006TS130 | | E | 006TS130 | | 0 | 006ts130 | +---+--+ 7 rows in set (0.00 sec) Is there a way to disable autocasting and unsensitive case? Thank you, j -- 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]
Sorting with NULL
I have a table that contains a foreign key that can be null. When I do a search I want to have the matches that are null appear first, and then all other values sorted by another column. My data looks like this: id name nullabc 1 def 2 xyz nullzzz 7 aaa I want to get them in this order: nullabc nullzzz 7 aaa 1 def 2 xyz SELECT id, name FROM mytable ORDER BY ?? doing ORDER BY id, name will make the null values appear first, but then the following values will be in the wrong order. I've thought about using FIELD() in the order by, but the docs say it doesn't like nulls. Is there some other sorting mechanism I could use? Thanks, Marcus -- Marcus Bointon Synchromedia Limited: Putting you in the picture [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting with NULL
Marcus Bointon wrote: I have a table that contains a foreign key that can be null. When I do a search I want to have the matches that are null appear first, and then all other values sorted by another column. My data looks like this: idname nullabc 1def 2xyz nullzzz 7aaa I want to get them in this order: nullabc nullzzz 7aaa 1def 2xyz SELECT id, name FROM mytable ORDER BY ?? doing ORDER BY id, name will make the null values appear first, but then the following values will be in the wrong order. I've thought about using FIELD() in the order by, but the docs say it doesn't like nulls. Is there some other sorting mechanism I could use? Thanks, Marcus id IS NOT NULL will return 0 when id is NULL, 1 when id is anything else, so ORDER BY id IS NOT NULL, name should do it. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help counting player with lowest score for each week.
OK, turns out this was a two fold issue. The server I was on had 4.0 mySQL, which was severely limited in it's abilities to use subqueries. The server has since been update to the 4.1 series, and now the following 2-subquery query work just fine. SELECT firstname, lastname, B.playerid, count(B.playerid) as Bubbles, B.playerid from (SELECT firstname, lastname, amount, playerid, date, place FROM (SELECT firstname, lastname, amount, results.playerid, date, place FROM results, players WHERE players.playerid = results.playerid and amount=0 AND year(date)=$year ORDER BY DATE, place ASC ) as A GROUP BY A.date) AS B GROUP BY B.playerid ORDER BY Bubbles DESC; Thomas 'Skip' Hollowell wrote: How do I simply find out who the bubble is now in my db now that we track more than 1 $0 person. I need just the person with $0 in the amount column with the lowest Place for each date. I can iterate through it all if needed in PHP, but I am always looking to learn more SQL tricks. I am close, as this gives me 1 person, and it says what that amount is, but then it gives me the person with the highest PLACE, instead of what is shown in min(place). ( SELECT playerid, date, place, min(place) FROM results WHERE amount=0 AND year(date)=2006 GROUP BY date ORDER BY DATE, place DESC Full Query SELECT firstname, lastname, A.playerid, count(A.playerid) AS Bubbles, A.place FROM (SELECT playerid, date, place, min(place) FROM results WHERE amount=0 AND year(date)=2006 GROUP BY date ORDER BY DATE, place DESC ) AS A, players WHERE a.playerid = players.playerid GROUP BY A.playerid ORDER BY Bubbles DESC; Anyone? Skip. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how use sql_slave_skip_counter to restore slave replication
(still catching up, sorry about the old issues) Don't know what version is being used here, but one thing you might want to use, if you're just going to skip the errors anyway (hopefully you understand WHY you're getting them), is to start replication with the --slave-skip-errors option -- you can skip only the errors you want or all errors. See: http://dev.mysql.com/doc/refman/5.1/en/replication-options.html I'd recommend getting replication up to speed with the slave-skip-errors option, then stopping the slave, taking out that option, so that new errors will show up. (we've done this when adding a new slave server to our system -- we don't quite have a snapshot of a database as a backup, just use mysqldump and lock each database individually, so our 3 db's are out of sync. We start replication at the earliest point, so some records are already in the db. Once the slave is current, we stop it, change the slave to show us all errors, and start replication again. The ideal solution is to implement point-in-time backups, which I'm working on currently.) Leo, your solution is dangerous because you're suggesting that commands to the master be sent as insert ignore into or replace into which is probably not the desired result (the commands from the master are sent to the slave, so you'd have to use those commands on the master). I'm also going to guess that the original poster did not lock all tables for the duration of the backup, getting a *snapshot* backup of the database, and that's why the replication errors happened. -Sheeri On 12/30/05, Leo [EMAIL PROTECTED] wrote: other than what Gleb says, you can avoid the error using 'insert ignore into' or better yet 'replace into' for every insert statement AESYS S.p.A. [Enzo Arlati] wrote: I'm trying to use teh parameter sql_slave_skip_counter at run-time to restore slave replication. When a slave replication broke due some errors in code, my be a duplicate key, the only working way to restore the replica where to delete the existing record which conflicts whith the ones inserted by the replication process. So if I have a duplicate key 30020 ,I have to remove the record with the id 30020 and the replication can reinsert it's copy of record with id = 30020. This should be difficult to automate so I try another way using the global variable sql_slave_skip_counter. I try to skip 5 records using a statemente like this: set global sql_slave_skip_counter = 5; and then restart the slavre start slave; but the problem is the same and the command 'show slave status' report a skip_counter filed equals 0. Where I am wrong , some can help me ? Regards, Enzo -- 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 View Optimization Help
Thanks a lot Shawn. I didn't realize that views don't take advantage of indexing. This is the cause of my major performance hits. I'm basically using views as a form of DB abstraction over the tables. So, many of my views pull all records from all tables they join, and it is up to the user to submit a where query to the view. In many cases, I'm getting 20-30 second queries, whereas the underlying (indexed) tables return results in .33 seconds. The views themselves aren't using criteria. This runs contrary to what I imagine to be a common use of views, ie, vwSelectAllArizonaResidents sort of thing, where the view internally compiles the where criteria from the underlying table. Scott Klarenbach On 1/6/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Klarenbach [EMAIL PROTECTED] wrote on 01/06/2006 08:13:10 PM: Hello, I'm new to views and am discovering massive performance hits in the views I've created once the records start to climb above 20,000 or so. Does anyone know of a great primer/tutorial site for optimizing views in MySQL, or even generally? What are the best practices etc...? I find when I create the same view in SQL by joining the tables directly, it's much faster than the views which invariably are joining other views. Is there a recursion problem with this method? Should views only join underlying tables and not other views? Thanks. Scott. Treat views as you would any other query. All of the optimizations that normally apply to SELECT query performance should also apply to view performance. Views differ from tables in that they cannot be indexed. That is probably why you are getting performance hits by building views on views. Any query against a view (such as a second-tier derivative view) will end up performing the equivalent of a full table scan on any view it uses. There is no hard and fast rule about building views based on other views or based on tables. What works best for you should be which solution you stick with. If you have millions of rows in a base table and a view can reduce that to about ten thousand rows of summary information, I would be very tempted to stick with the view as the basis of a future query. You still have to generate that view each time you want to use it but its data may be sitting there in the query cache so it has the potential to be very fast. If I were you I would review the entire optimization chapter: http://dev.mysql.com/doc/refman/5.0/en/optimization.html It's loaded with useful information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL Replication
To add a few short notes: 1) What happens when you modify data on the slave directly depends on how you configure your setup. It is possible to have slave updates appear on the master, that is usually referred to as circular replication. Since you have 2 replication slaves I would advise against using circular replication (1 - 2 - 3 - 1). I would also highly recommend you use the 'read-only' flag on your slave to prevent accidental updates which would break data consistency. 2) In a setup like this, I would recommend that you consistently name your database handles appropriately in your code, say $dbh_write and $dbh_readonly. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 6 Jan 2006, Jason Williard wrote: I am trying to understand exactly how replication works. So far, I see that changes made on a master server are replicated to the slave server(s). However, if a change is made on a slave server, is that replicated back to the master as well as all other slaves? I am asking this question as I try to develop a plan for more efficient web servers. Here is what I am planning. Please let me know if this sounds smart, or like a bad idea. Server 1: Redhat MySQL Master Servers 2 3: Load-Balanced Redhat Apache web servers w/MySQL Slaves Servers 2 3 will be serving the same content and will need access to the same data from the MySQL server(s). I am hoping that running MySQL on each of the web servers will help to reduce the overall load on the servers. Thank You, Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Replication
To be clear: Replication in MySQL replicates the DML (data manipulation language) and DDL (data definition language) commands -- that is, any command that's an alter, update, insert, replace, create, drop, etc statement to the slave. If you write to the slave, it does not write back to the master. If you change the data on the slave server, replication from the master to the slave will happily continue (hopefully, unless you've changed something that would cause a duplicate key error or something) because it only sends the commands over. There's no way for the master to tell that the slave has changed. There is also no easy way to take a master and a slave and ensure that they do, indeed, have the same data. -Sheeri On 1/6/06, Jason Williard [EMAIL PROTECTED] wrote: I am trying to understand exactly how replication works. So far, I see that changes made on a master server are replicated to the slave server(s). However, if a change is made on a slave server, is that replicated back to the master as well as all other slaves? I am asking this question as I try to develop a plan for more efficient web servers. Here is what I am planning. Please let me know if this sounds smart, or like a bad idea. Server 1: Redhat MySQL Master Servers 2 3: Load-Balanced Redhat Apache web servers w/MySQL Slaves Servers 2 3 will be serving the same content and will need access to the same data from the MySQL server(s). I am hoping that running MySQL on each of the web servers will help to reduce the overall load on the servers. Thank You, Jason Williard -- 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 Replication
Hi Jason, Most other peoples responses are excellent as usual, however might I suggest getting a copy of High Performance MySQL by Jeremy Zawodny (O'Reilly publishers). This covers the exact scenario you are talking about. 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: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Saturday, 7 January 2006 9:32 AM To: mysql@lists.mysql.com Subject: MySQL Replication I am trying to understand exactly how replication works. So far, I see that changes made on a master server are replicated to the slave server(s). However, if a change is made on a slave server, is that replicated back to the master as well as all other slaves? I am asking this question as I try to develop a plan for more efficient web servers. Here is what I am planning. Please let me know if this sounds smart, or like a bad idea. Server 1: Redhat MySQL Master Servers 2 3: Load-Balanced Redhat Apache web servers w/MySQL Slaves Servers 2 3 will be serving the same content and will need access to the same data from the MySQL server(s). I am hoping that running MySQL on each of the web servers will help to reduce the overall load on the servers. Thank You, Jason Williard -- 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: working w/UK postcodes
Mike Blezien wrote: Yes, after some further research, I found a Perl Modules that handles this queit nicely. thx's Please tell me where this module is, if you would. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remotely show databases
'SHOW DATABASES;' | mysql -u foo -h bar.com this works, I haven't tested it with other databases though. On 1/9/06, Mikhail Berman [EMAIL PROTECTED] wrote: Hi Anthony, I am not sure if you have an installation of MySQL on your local server. If you do then you can try to use something like below to execute your SHOW DATABASES Local_server[path to your mysql/bin directory]/mysql --host=your_remote_host --user=your_user --password=your_password -e SHOW DATABASE Make sure that [EMAIL PROTECTED] has appropriate rights on your_remote_host (server) Best, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Ettinger Sent: Sunday, January 08, 2006 4:03 PM To: mysql@lists.mysql.com Subject: remotely show databases I know I can login via ssh and run $mysqlshow But I would then have to parse the outputted text, is there an easier way (I'm using Perl locally here). The pitfall of running it locally is that you DO have to password protect your database user since it's an outside connection to run SHOW DATABASES; I tried $man mysqlshow, but didn't see any easy way of simply returning a \n seperated list of databases. Any suggestions? I also need to do this for postgresql if anyone else knows of a standalone app that dumps the databases for a specific user. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html
RE: remotely show databases
Hi Anthony, If you are accessing the db from perl, just execute a show databases from there using the DBI interface. There is quite an amount of doco about this and other features at cpan.org. You could also look at the source for phpMyadmin as this has to get the database names to display them. The advantage of the DBI interface is that with a tiny bit of work, it will probably work with Postgresql as well. You could also give SELECT permission to a specific user on all databases that is not passworded and this should allow you to list the databases without having to send a password. 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Ettinger Sent: Tuesday, 10 January 2006 7:40 AM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: remotely show databases 'SHOW DATABASES;' | mysql -u foo -h bar.com this works, I haven't tested it with other databases though. On 1/9/06, Mikhail Berman [EMAIL PROTECTED] wrote: Hi Anthony, I am not sure if you have an installation of MySQL on your local server. If you do then you can try to use something like below to execute your SHOW DATABASES Local_server[path to your mysql/bin directory]/mysql --host=your_remote_host --user=your_user --password=your_password -e SHOW DATABASE Make sure that [EMAIL PROTECTED] has appropriate rights on your_remote_host (server) Best, Mikhail Berman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anthony Ettinger Sent: Sunday, January 08, 2006 4:03 PM To: mysql@lists.mysql.com Subject: remotely show databases I know I can login via ssh and run $mysqlshow But I would then have to parse the outputted text, is there an easier way (I'm using Perl locally here). The pitfall of running it locally is that you DO have to password protect your database user since it's an outside connection to run SHOW DATABASES; I tried $man mysqlshow, but didn't see any easy way of simply returning a \n seperated list of databases. Any suggestions? I also need to do this for postgresql if anyone else knows of a standalone app that dumps the databases for a specific user. -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- Anthony Ettinger Signature: http://chovy.dyndns.org/hcard.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FAS3050 or FAS3020
If anyone worked with FAS3050 / FAS3020 (http://www.netapp.com/products/filer/fas3000_ds.html) and mysql? We have very large DB (InnoDB mainly with some MyISAM tables) and want to use FAS3050 Any information about any problems or other issues will be very appreciated. Best regards, Alexander (Alik) Rubin
Re: MySQL Replication
I'll 2nd that High Performance MySQL. it is by far the best MySQL book I've come across (though I didn't need the 101 stuff, I specifically needed tuning/architecting for HA, etc.) the only knock I could make (which isn't their fault) is that it needs to be updated for 5.x (can you say 2nd edition?).
cannot connect to mysql
im quite new to mysql and here is the problem. I simply cannot connect to mysql through phpmyadmin or anything else. Lets say i enter the url to connect (localhost/phpmyadmin/index.html) i end up in the welcome page but it says access denied for user [EMAIL PROTECTED] (using password: NO) when i do the phpinfo test, everything seems to be ok with the server (i run apache) and mysql and php are installed and running, but i simply cannot connect. i guess the problem is the username and password or something like in the configuration of mysql that but i cant figure it out. Can someone tell me what i am missing ? thanks a lot !! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
char() or nchar() in mysql 5.0.18
Hello. MySQL 5.0.18 Not assigned 0x80 - 0xFF for CHAR(). When use: mysql_stmt_prepare(), mysql_stmt_bind_param(), mysql_stmt_execute(). For NCHAR()successfully assigned0x00 - 0xFF. Successfully executed: INSERT INTO test (h) VALUES(CHAR(128)) Vladimir Khodakov. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remotely show databases
On 1/9/06, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote:
Hi Anthony,
If you are accessing the db from perl, just execute a show databases
from there using the DBI interface. There is quite an amount of doco
about this and other features at cpan.org. You could also look at the
source for phpMyadmin as this has to get the database names to display
them.
The advantage of the DBI interface is that with a tiny bit of work, it
will probably work with Postgresql as well.
You could also give SELECT permission to a specific user on all
databases that is not passworded and this should allow you to list the
databases without having to send a password.
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Tuesday, 10 January 2006 7:40 AM
To: Mikhail Berman
Cc: mysql@lists.mysql.com
Subject: Re: remotely show databases
'SHOW DATABASES;' | mysql -u foo -h bar.com
this works, I haven't tested it with other databases though.
On 1/9/06, Mikhail Berman [EMAIL PROTECTED] wrote:
Hi Anthony,
I am not sure if you have an installation of MySQL on your local
server.
If you do then you can try to use something like below to execute your
SHOW DATABASES
Local_server[path to your mysql/bin directory]/mysql
--host=your_remote_host --user=your_user --password=your_password -e
SHOW DATABASE
Make sure that [EMAIL PROTECTED] has appropriate rights on
your_remote_host (server)
Best,
Mikhail Berman
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Sunday, January 08, 2006 4:03 PM
To: mysql@lists.mysql.com
Subject: remotely show databases
I know I can login via ssh and run $mysqlshow
But I would then have to parse the outputted text, is there an easier
way (I'm using Perl locally here).
The pitfall of running it locally is that you DO have to password
protect your database user since it's an outside connection to run
SHOW
DATABASES;
I tried $man mysqlshow, but didn't see any easy way of simply
returning
a \n seperated list of databases.
Any suggestions?
I also need to do this for postgresql if anyone else knows of a
standalone app that dumps the databases for a specific user.
Actually, for security reasons, the command has to be executed on the remote
ssh server that has network access to the mysql server. Going across the
Internet with DBI; may not be feasible unless you open up the access host.
RE: remotely show databases
True, however you could run the perl script locally via another
mechanism and allow it to return the database info in the format that
you desire, or run it using the mysql -e 'show databases' -u
local_user_that_doesn't_need_a_password
The other option is to use an ssh tunnel to the server in question. I
use that quite a bit. Redirect a port say, 1, to 3306 locally. That
would be ok if you can ssh to it, it will also secure the communications
and allow you to extract the information that you require.
You only then need to open the ssh port on the remote machine. From my
reading of you emails, that seems to be ok to do.
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Tuesday, 10 January 2006 9:33 AM
To: Logan, David (SST - Adelaide)
Cc: Mikhail Berman; mysql@lists.mysql.com
Subject: Re: remotely show databases
On 1/9/06, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote:
Hi Anthony,
If you are accessing the db from perl, just execute a show databases
from there using the DBI interface. There is quite an amount of doco
about this and other features at cpan.org. You could also look at the
source for phpMyadmin as this has to get the database names to display
them.
The advantage of the DBI interface is that with a tiny bit of work, it
will probably work with Postgresql as well.
You could also give SELECT permission to a specific user on all
databases that is not passworded and this should allow you to list the
databases without having to send a password.
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Tuesday, 10 January 2006 7:40 AM
To: Mikhail Berman
Cc: mysql@lists.mysql.com
Subject: Re: remotely show databases
'SHOW DATABASES;' | mysql -u foo -h bar.com
this works, I haven't tested it with other databases though.
On 1/9/06, Mikhail Berman [EMAIL PROTECTED] wrote:
Hi Anthony,
I am not sure if you have an installation of MySQL on your local
server.
If you do then you can try to use something like below to execute
your
SHOW DATABASES
Local_server[path to your mysql/bin directory]/mysql
--host=your_remote_host --user=your_user --password=your_password -e
SHOW DATABASE
Make sure that [EMAIL PROTECTED] has appropriate rights on
your_remote_host (server)
Best,
Mikhail Berman
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Sunday, January 08, 2006 4:03 PM
To: mysql@lists.mysql.com
Subject: remotely show databases
I know I can login via ssh and run $mysqlshow
But I would then have to parse the outputted text, is there an
easier
way (I'm using Perl locally here).
The pitfall of running it locally is that you DO have to password
protect your database user since it's an outside connection to run
SHOW
DATABASES;
I tried $man mysqlshow, but didn't see any easy way of simply
returning
a \n seperated list of databases.
Any suggestions?
I also need to do this for postgresql if anyone else knows of a
standalone app that dumps the databases for a specific user.
Actually, for security reasons, the command has to be executed on the
remote
ssh server that has network access to the mysql server. Going across the
Internet with DBI; may not be feasible unless you open up the access
host.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: remotely show databases
On 1/9/06, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote:
True, however you could run the perl script locally via another
mechanism and allow it to return the database info in the format that
you desire, or run it using the mysql -e 'show databases' -u
local_user_that_doesn't_need_a_password
The other option is to use an ssh tunnel to the server in question. I
use that quite a bit. Redirect a port say, 1, to 3306 locally. That
would be ok if you can ssh to it, it will also secure the communications
and allow you to extract the information that you require.
You only then need to open the ssh port on the remote machine. From my
reading of you emails, that seems to be ok to do.
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Tuesday, 10 January 2006 9:33 AM
To: Logan, David (SST - Adelaide)
Cc: Mikhail Berman; mysql@lists.mysql.com
Subject: Re: remotely show databases
On 1/9/06, Logan, David (SST - Adelaide) [EMAIL PROTECTED] wrote:
Hi Anthony,
If you are accessing the db from perl, just execute a show databases
from there using the DBI interface. There is quite an amount of doco
about this and other features at cpan.org. You could also look at the
source for phpMyadmin as this has to get the database names to display
them.
The advantage of the DBI interface is that with a tiny bit of work, it
will probably work with Postgresql as well.
You could also give SELECT permission to a specific user on all
databases that is not passworded and this should allow you to list the
databases without having to send a password.
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Tuesday, 10 January 2006 7:40 AM
To: Mikhail Berman
Cc: mysql@lists.mysql.com
Subject: Re: remotely show databases
'SHOW DATABASES;' | mysql -u foo -h bar.com
this works, I haven't tested it with other databases though.
On 1/9/06, Mikhail Berman [EMAIL PROTECTED] wrote:
Hi Anthony,
I am not sure if you have an installation of MySQL on your local
server.
If you do then you can try to use something like below to execute
your
SHOW DATABASES
Local_server[path to your mysql/bin directory]/mysql
--host=your_remote_host --user=your_user --password=your_password -e
SHOW DATABASE
Make sure that [EMAIL PROTECTED] has appropriate rights on
your_remote_host (server)
Best,
Mikhail Berman
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Anthony Ettinger
Sent: Sunday, January 08, 2006 4:03 PM
To: mysql@lists.mysql.com
Subject: remotely show databases
I know I can login via ssh and run $mysqlshow
But I would then have to parse the outputted text, is there an
easier
way (I'm using Perl locally here).
The pitfall of running it locally is that you DO have to password
protect your database user since it's an outside connection to run
SHOW
DATABASES;
I tried $man mysqlshow, but didn't see any easy way of simply
returning
a \n seperated list of databases.
Any suggestions?
I also need to do this for postgresql if anyone else knows of a
standalone app that dumps the databases for a specific user.
Actually, for security reasons, the command has to be executed on the
remote
ssh server that has network access to the mysql server. Going across the
Internet with DBI; may not be feasible unless you open up
Re: too many connections crashing MySQL?
HI, The below equation as been obtained from the docs in mysql.com. As per this equation and looking @ your configs, if definitely looks like a memory problem. innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB In an ideal case the above equation should evaluate to a value lesser than the physical memory available. Thanx Alex On Mon, 09 Jan 2006 22:12:53 +0530, sheeri kritzer [EMAIL PROTECTED] wrote: We're running MySQL version 4.1.12 on Fedora Core 3 64-bit. we've been crashing; here is a mysqld.err file from one crash: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=335544320 read_buffer_size=131072 max_used_connections=2049 max_connections=2048 threads_connected=371 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4784112 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060108 14:43:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. [InnoDB crash recovery elided] - We have 6G of memory on the server, and we checked -- we're not running out of memory. I'm guessing that mysqld crashed because of that 2049th connection -- shouldn't it just refuse the connection, not crash? The variables in the mysqld.err match the /etc/my.cnf: [mysqld] old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port= 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 1024 thread_cache= 80 ft_min_word_len = 3 # Use this to prevent access via TCP/IP # skip_networking # Query Cache Settings - OFF due to overload of Session table query_cache_size = 32M query_cache_type = 2 # Log queries taking longer than long_query_time seconds long_query_time = 4 log-slow-queries = /var/lib/mysql/slow-queries.log log-error = /var/lib/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 # when you change this recalculate total possible mysqld memory usage!! # key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections max_connections = 2048 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin server-id = 15 max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G; innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 Any help is appreciated. We've been crashing around the same time every day, our busiest time of day. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking issue with MyISAM on Solaris 10
Hi! Nowadays we've conducted something what I can call extended testing of MySQL on Solaris10/x86. The most annoying issue is a problem with locking queries to MyISAM-type tables. Mechanism seems to be simple to explain - long running query locks table for READ, next there is some DML query which must wait for this lock to be removed, and then every other query must also wait (even SELECTs, because it seems they wait for DML to complete), while wait times became completely unacceptable However this is the issue only on Solaris. The same application/database running on Linux has no such problems. The other thing is, that the same set of queries runs on Solaris much slower than on Linux - what at least partially explains no problems on Linux There is enabled disk-buffering, key buffer is even bigger than on Linux, we try mysql in versions 4 and 5, nonetheless on Solaris there is locking, on Linux there is not. Queries beeing executed make count(*) by full scan (due to hardly selective conditions (sex etc)) on table with ~1 rows and join to another table with ~100 rows. Any comments ? Best regards Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
