Re: Load Balance on MySql
Thanx for the information , but i wanted to go for a long term plan and for a fixed solution. bcause little by little that load getting high. please need some mysql expertise help. Thank you, Shaine. Ady Wicaksono [EMAIL PROTECTED] wrote: Hi Lee better you start to benchmark your system using tools like http://sysbench.sourceforge.net/ You will find out, what boundary you hit, if you hit max thread, reduce thread stack size but very careful on this stuff :) On 1/18/07, Shain Lee wrote: Hi Friends , I have huge WAP content database and it's included with wallpapers, ringtones, games ...etc.That content database getting hits more that 1000 /sec. This is actually massive. My Webserver s are Tomcat and apache , because some applications written in java and some are php . perl. Hence i needed to use that both wap servers.Due high load for the content database ,it's getting hang/stuck/panic. I couldn't even restart the mysql service. I have restart the machine , no any other way to release the stucked load. OS - RHEL : 2.6.9-42.0.3.ELsmp MySql - 4.1.7 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 # flush back_log=2000 connect_timeout=10 interactive_timeout=25 join_buffer_size=4M key_buffer=1024M max_allowed_packet=64M max_connections=2000 max_connect_errors=10 myisam_sort_buffer_size=256M read_buffer_size=8M read_rnd_buffer_size=8M sort_buffer_size=8M table_cache=4096 thread_cache_size=400 thread_concurrency=16 wait_timeout=50 query_cache_size=1024M query_cache_limit=32M query_cache_type=1 log=/var/log/mysqld_sql.log log-error=/var/log/mysqld_error.log # [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Now , i have to think about any perfect load balancing method , i can't duplicate the databse in another machine. It's directly conflict with serving contents for each request. can somebody help me on it ? what would be the perfect way to have a balance the load ? How can we take the statics of mysql databse ? is there any open source product available ? Thanx in advance, Shaine. - New Yahoo! Mail is the ultimate force in competitive emailing. Find out more at the Yahoo! Mail Championships. Plus: play games and win prizes. - What kind of emailer are you? Find out today - get a free analysis of your email personality. Take the quiz at the Yahoo! Mail Championship.
Re: Load Balance on MySql
Hi Shain, Shain Lee wrote: Now , i have to think about any perfect load balancing method , i can't duplicate the databse in another machine. It's directly conflict with serving contents for each request. Why not use replication? If you don't want to do it on application level (seperate reads from writes by using two database connections), you might want to try out multi-master replication. It is somewhat tricky to setup as there are problems with auto_increment, but that is documented in mysql documentation. http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html You could then use any load balancing solution you desire to spread load between servers. can somebody help me on it ? what would be the perfect way to have a balance the load ? How can we take the statics of mysql databse ? is there any open source product available ? In recent MySQL 5.0 versions, there is the mysqlreport command line tool, which shows various statistics. As you are using mysql 4.1, you can't use it. You can try monitoring SHOW STATUS output. I would especially be interested in your query cache statistics as you have a very large cache. Do you store the content in the database or just links to files? regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about index usage
Hi guys, I have a question about index usage in MySQL. I have a query: MYSQL: ([EMAIL PROTECTED]) [webstats] EXPLAIN SELECT sum(users) as totaal_uniek, page_id FROM webstats.stats_hour where page_id LIKE 'vipPage_%' and site = 'spelpuntVip' and date 1166353093 group by page_id ORDER BY totaal_uniek DESC LIMIT 10; ++-++---+---+--- ---+-+--++-- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+--- ---+-+--++-- + | 1 | SIMPLE | stats_hour | range | date,page_id,pageId_site_date | date | 4 | NULL | 833057 | Using where; Using temporary; Using filesort | ++-++---+---+--- ---+-+--++-- + 1 row in set (0.05 sec) And a table stats_hour with indexes: +++---+--+-- ---+---+-+--++--+--- -+-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++---+--+-- ---+---+-+--++--+--- -+-+ | stats_hour | 0 | year_mon_day_hour_pageId_site |1 | year| A |NULL | NULL | NULL | | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site |2 | mon | A |NULL | NULL | NULL | | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site |3 | day | A |NULL | NULL | NULL | | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site |4 | hour| A |NULL | NULL | NULL | | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site |5 | page_id | A |NULL | NULL | NULL | YES | BTREE | | | stats_hour | 0 | year_mon_day_hour_pageId_site |6 | site| A |NULL | NULL | NULL | | BTREE | | | stats_hour | 1 | date |1 | date| A | 525625 | NULL | NULL | | BTREE | | | stats_hour | 1 | mon |1 | mon | A | 14 | NULL | NULL | | BTREE | | | stats_hour | 1 | page_id |1 | page_id | A | 23053 | NULL | NULL | YES | BTREE | | | stats_hour | 1 | hour |1 | hour| A | 28 | NULL | NULL | | BTREE | | | stats_hour | 1 | day |1 | day | A | 36 | NULL | NULL | | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site |1 | day | A | 36 | NULL | NULL | | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site |2 | mon | A | 426 | NULL | NULL | | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site |3 | year| A |1342 | NULL | NULL | | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site |4 | page_id | A | 328515 | NULL | NULL | YES | BTREE | | | stats_hour | 1 | day_mon_year_pageId_site |5 | site| A | 328515 | NULL | NULL | | BTREE | | | stats_hour | 1 | pageId_site_date |1 | page_id | A | 23053 | NULL | NULL | YES | BTREE | | | stats_hour | 1 | pageId_site_date |2 | site| A | 23892 | NULL | NULL | | BTREE | | | stats_hour | 1 | pageId_site_date |3 | date| A | 2628125 | NULL | NULL | | BTREE | | +++---+--+-- ---+---+-+--++--+--- -+-+ I dont understand why MySQL doesnt use the pageId_site_date index. Maybe someone here can explain this issue to me or know a
Re: Strange InnoDB Deadlock Behavior
Jason, I assume that your principal databases are INNODB databases. Regards On 1/17/07, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi Juan, Just wanted to touchbase and see if you had any suggestions based on the my.cnf and machine config. Thank you in advance. Best Regards, Jason On 1/15/07, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Jason, Send me a my.cnf in order to view your configuration ( using innodb storage engine). Send me a configuration of your machine ( CPU and Memory). Regards [client] #password = [your_password] port= 3306 socket = /tmp/mysql.sock # *** Application-specific options follow here *** # # The MySQL server # [mysqld] # generic configuration options port= 3306 socket = /tmp/mysql.sock datadir = /node1_css_mysql basedir = /opt/mysql-5.0.27 # back_log is the number of connections the operating system can keep in # the listen queue, before the MySQL connection manager thread has # processed them. If you have a very high connection rate and experience # connection refused errors, you might need to increase this value. # Check your OS documentation for the maximum value of this parameter. # Attempting to set back_log higher than your operating system limit # will have no effect. back_log = 50 # Don't listen on a TCP/IP port at all. This can be a security # enhancement, if all processes that need to connect to mysqld run # on the same host. All interaction with mysqld must be made via Unix # sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the enable-named-pipe option) will render mysqld useless! #skip-networking # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. # Modified by Juan max_connections = 256 skip_name_resolve # Maximum amount of errors allowed per host. If this limit is reached, # the host will be blocked from connecting to the MySQL server until # FLUSH HOSTS has been run or the server was restarted. Invalid # passwords and other errors during the connect phase result in # increasing this value. See the Aborted_connects status variable for # global counter. max_connect_errors = 10 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable open-files-limit in # section [mysqld_safe] # Modified by Juan table_cache = 256 # Enable external file level locking. Enabled file locking will have a # negative impact on performance, so only use it in case you have # multiple database instances running on the same files (note some # restrictions still apply!) or if you use other software relying on # locking MyISAM tables on file level. #external-locking # The maximum size of a query packet the server can handle as well as # maximum query size server can process (Important when working with # large BLOBs). enlarged dynamically, for each connection. # Modified by Juan max_allowed_packet = 32M # The size of the cache to hold the SQL statements for the binary log # during a transaction. If you often use big, multi-statement # transactions you can increase this value to get more performance. All # statements from transactions are buffered in the binary log cache and # are being written to the binary log at once after the COMMIT. If the # transaction is larger than this value, temporary file on disk is used # instead. This buffer is allocated per connection on first update # statement in transaction # Modified by Juan #binlog_cache_size = 1M max_binlog_size= 1 # Maximum allowed size for a single HEAP (in memory) table. This option # is a protection against the accidential creation of a very large HEAP # table which could otherwise use up all memory resources. max_heap_table_size = 64M # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a disk # based merge sort is used instead - See the Sort_merge_passes # status variable. Allocated per thread if sort is needed. # Modified by Juan sort_buffer_size = 1M # This buffer is used for the optimization of full JOINs (JOINs without # indexes). Such JOINs are very bad for performance in most cases # anyway, but setting this variable to a large value reduces the # performance impact. See the Select_full_join status variable for a # count of full JOINs. Allocated per thread if full join is found join_buffer_size = 2M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of
SELECT from 3 tables - Need help
Hi All, I'm working on a Intranet database with a few tables to allow all the workers from the company to access it. However, I'm facing a problem with a QUERY. I've did try to use JOIN but I'm not able to get the results I need... I have 3 tables (concerning this problem): Table 'Quotes' QuoteID ClientID Date Price Comments Table 'Clients' ClientID Name Address Email Table 'Products' QuoteID ProductName ProductType ProductShape After filling the info to table quotes, I would like to do a QUERY to SELECT a specific QuoteID and also to JOIN to the result the client information (being ClientID the relation) and also to JOIN the information of the product if available (being QuoteID the relation). The problem is that if there is no data in table 'Products' that matched QuoteID from table 'Quotes' than, the field 'QuoteID' is returned empty. I would like to do a SELECT that would return all the fields in the 'Quotes' table plus the client info and plus the product info but only if available. If product info is not available I would like to get a result having all 'Quotes' info and 'Clients' info. My query is: SELECT * FROM `Quotes` LEFT JOIN (`Products`,`Clientes`) ON (`Products`.`QuoteID`=`Quotes`.`QuoteID` AND `Clientes`.`ClientID`=`Quotes`.`ClientID`) WHERE `Quotes`.`QuoteID`=6936 Any suggestion? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT from 3 tables - Need help
I would like to do a SELECT that would return all the fields in the 'Quotes' table plus the client info and plus the product info but only if available. If product info is not available I would like to get a result having all 'Quotes' info and 'Clients' info. My query is: SELECT * FROM `Quotes` LEFT JOIN (`Products`,`Clientes`) ON (`Products`.`QuoteID`=`Quotes`.`QuoteID` AND `Clientes`.`ClientID`=`Quotes`.`ClientID`) WHERE `Quotes`.`QuoteID`=6936 Try this: SELECT * FROM `Quotes` LEFT OUTER JOIN `Products` ON (`Products`.`QuoteID`=`Quotes`.`QuoteID`) LEFT OUTER JOIN `Clientes` ON (`Clientes`.`ClientID`=`Quotes`.`ClientID`) WHERE `Quotes`.`QuoteID`=6936 Dundo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT from 3 tables - Need help
From Davor: Try this: SELECT * FROM `Quotes` LEFT OUTER JOIN `Products` ON (`Products`.`QuoteID`=`Quotes`.`QuoteID`) LEFT OUTER JOIN `Clientes` ON (`Clientes`.`ClientID`=`Quotes`.`ClientID`) WHERE `Quotes`.`QuoteID`=6936 Dundo Still the same... :( When a QuoteID from Quotes is not available as QuoteID from Products the result I get gets an empty QuoteID. It seems that it saves the QuoteID from the last table it worked with... Should I change the order of the tables in the query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT from 3 tables - Need help
Olexandr Melnyk wrote: I din't know about listiтg multiple tables in the JOIN clause up to now, but anyways it looks like QuoteId from the Products table is overriding the same field from the Quotes table. Try to replace the asterisk with an explicit list of fields you want to get. Hi, thanks for the reply (you should reply to the list also) Do you mean that I should replace SELECT * FROM with SELECT field1, [field2],[...] FROM? If so, I need to specify the table name like SELECT Clients.Name correct? Also, how can I access to Quotes.QuoteID and Products.QuoteID? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT from 3 tables - Need help
Do you mean that I should replace SELECT * FROM with SELECT field1, [field2],[...] FROM? Yes. If so, I need to specify the table name like SELECT Clients.Name correct? You only need to do that when 2 tables have the same column name. In general, though, it's good practice to always include the table name when joining multiple tables. Also, how can I access to Quotes.QuoteID and Products.QuoteID? Use aliases (which can be anything; I'm just replacing the '.' with a '_' below) SELECT Quotes.QuoteID AS Quotes_QuoteID, Products.QuoteID AS Products_QuoteId FROM ... Thnx, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT from 3 tables - Need help
From Chris, Do you mean that I should replace SELECT * FROM with SELECT field1, [field2],[...] FROM? Yes. If so, I need to specify the table name like SELECT Clients.Name correct? You only need to do that when 2 tables have the same column name. In general, though, it's good practice to always include the table name when joining multiple tables. Also, how can I access to Quotes.QuoteID and Products.QuoteID? Use aliases (which can be anything; I'm just replacing the '.' with a '_' below) SELECT Quotes.QuoteID AS Quotes_QuoteID, Products.QuoteID AS Products_QuoteId FROM ... Thnx, Chris After Olexandr Melnyk proposition to specify all the fields instead of SELECT * FROM and after Chris Boget letting me know how to have multiple columns with the same name from different tables by using aliases, I've been able to accomplish what I want! :) However, after having it working correctly, I've noticed that I was no using 3 or maybe 4 columns of each table so, in the columns list I've only set those that I needed. By doing that, I stopped the need for aliases (since I'm only including Quotes.QuoteID and not including Products.QuoteID) and I can still reffer to the columns by their real name. Anyway, i'ts allways good to know that aliases exist, what they are and what they're for. So, for now I'll say thank you all and I'll probably come back. Thanks, Nuno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication help, please
Dear List, As recently as last Sunday January 14, 2007, we have enabled replication between two servers in our organization. The master server runs MySQL 4.1.10a, the slave runs 5.0.18. Since then, we have had a number of interruptions in replication when the slave server stopped replicating for different reasons. I was able to fix the problems pointed out by the error log on the slave server, but I am witnessing strange behavior on the part of the slave. Every time, I look up slave status using show slave status, I see the value of Seconds_Behind_Master getting bigger nor smaller as one would expect. I am pasting actual reports of show slave status at the end of this E-mail. Could anyone help me to find out why the slave reports such thing, and how to overcome it. mysql show slave status\G; *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: saruman Master_User: alatarreplica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: SB2000-bin.000139 Read_Master_Log_Pos: 857395571 Relay_Log_File: alatar-relay-bin.05 Relay_Log_Pos: 190740012 Relay_Master_Log_File: SB2000-bin.000139 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: secdocs Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 190663065 Relay_Log_Space: 858304045 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 285342 1 row in set (0.00 sec) ERROR: No query specified mysql show slave status\G; *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: saruman Master_User: alatarreplica Master_Port: 3306 Connect_Retry: 60 Master_Log_File: SB2000-bin.000139 Read_Master_Log_Pos: 857395745 Relay_Log_File: alatar-relay-bin.05 Relay_Log_Pos: 190740012 Relay_Master_Log_File: SB2000-bin.000139 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: secdocs Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 190663065 Relay_Log_Space: 858304221 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 285344 1 row in set (0.00 sec) ERROR: No query specified Your help is greatly appreciated, Mikhail Berman Ives Group
Re: SELECT from 3 tables - Need help
2007/1/18, Nuno Oliveira [EMAIL PROTECTED]: Hi, thanks for the reply (you should reply to the list also) Heh, that was the default Gmail behaviour. Do you mean that I should replace SELECT * FROM with SELECT field1, [field2],[...] FROM? It is generally a good pratice to keep away from queries with asterisks in production systems. And, as Chris mentioned, it is also recommended to always specify the table name for multi-table queries. Olexandr Melnyk, http://omelnyk.net/
Connecting from a remote computer
Hi All, This is a dump and simple question but I can't seem to get it to work. How do I enable a user to be able to connect from a remost host using MySQL Administrator? I added % but once signed in, I still can't access the user panel in the administrator. Thanks. Kay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting from a remote computer
Hi All, This is a dump and simple question but I can't seem to get it to work. How do I enable a user to be able to connect from a remost host using MySQL Administrator? I added % but once signed in, I still can't access the user panel in the administrator. Thanks. Kay I don't know if I can help but MySQL users have some privileges ans one of the configurations on the privileges of a user is the host from where it can connect. If the user is configured to connect only from localhost you won't be able to connect as that user from a remote machine. -Nuno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting from a remote computer
Just to toss something else in here. A lot of times you have a server that you can connect to by ssh, but because of firewalls, can't access mysql through. If you can, however, connect to the database through ssh, you can do port forwarding. In *nix systems it should be something like this: ssh -L 3306:server.com:3306 -N -f [EMAIL PROTECTED] This will forward requests from port 3306 locally to port 3306 on server.com. If you're on windows, you can also do port forwarding through putty: http://www.cs.uu.nl/technical/services/ssh/putty/puttyfw.html This becomes pretty easy when you get the hang of it. You can also do like: ssh -L 3000:server.com:3306 -N -f [EMAIL PROTECTED] if you're, say, running a local mysql instance. As a reminder connections will have to occur to localhost, not the server. Hope this helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query skips one set of records
The query displayed below performs flawlessly, except for these two records: 7364M0174000250510 Invoice 2006-12-13 2006-12-13 2006-12-31 7365M01740 002506 5 Invoice 2006-12-13 2006-12-13 2006-12-31 Here's the table structure: member_idvarchar(6) member_sub_idvarchar(6) pay_method varchar(8) monthly_cost decimal(11,0) anniv_bill_date date dtCreateddate fetch_date date This query: SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) ) ) AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method = 'Invoice' GROUP BY member_id Should return Amount as $280 : ( 10*2 ) + 200 for the first record plus 5 * 12 for the next one. Instead it is returning $180. Other records which have similar conditions are processed with no difficulty. Is this kind of intermittency a bug in MySQL 3.23? If anyone has any suggestions, then I would love to know it. If I execute a test query without the SUM() function and GROUP BY, but using all of the other WHERE conditions, these two records are flawlessly selected from the data set. With SUM() and GROUP BY - wrong results, for only these two. Suggestions or hints will be welcome. I can now upgrade to MySQL 4.x, so I will do that. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.410 / Virus Database: 268.16.14/636 - Release Date: 1/18/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connecting from a remote computer
I got it working perfectly on a Linux server, but my boss wants this
on a Windows IIS server. sigh
The remote connection seems to be working now but I have another
problem now... the page is not doing anything - doesn't seem to be
connecting to the database, but I got no error messages
whatsoever. The page just indicated Done on the bottom. The
mysql_connect.php file is listed below. Is there a privilege problem
or is there something else I need to enable?
Much thanks.
Kay
?php # Script - mysql_connect.php
// This file contains the database access information for the
database. This file also establishes a connection to MySQL and
selects the database.
// Set the database access information as constants.
define ('DB_USER', 'username);
define ('DB_PASSWORD', 'userpass');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'databasename');
// Make the connnection and then select the database.
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could
not connect to MySQL: ' . mysql_error() );
mysql_select_db (DB_NAME) OR die ('Could not select the database: ' .
mysql_error() );
// Function for escaping and trimming form data.
function escape_data ($data) {
global $dbc;
if (ini_get('magic_quotes_gpc')) {
$data = stripslashes($data);
}
return mysql_real_escape_string (trim ($data), $dbc);
} // End of escape_data() function.
?
At 10:53 AM 1/18/2007 Thursday, Chris White wrote:
Just to toss something else in here. A lot of times you have a
server that you can connect to by ssh, but because of firewalls,
can't access mysql through. If you can, however, connect to the
database through ssh, you can do port forwarding. In *nix systems
it should be something like this:
ssh -L 3306:server.com:3306 -N -f [EMAIL PROTECTED]
This will forward requests from port 3306 locally to port 3306 on
server.com. If you're on windows, you can also do port forwarding
through putty:
http://www.cs.uu.nl/technical/services/ssh/putty/puttyfw.html
This becomes pretty easy when you get the hang of it. You can also do like:
ssh -L 3000:server.com:3306 -N -f [EMAIL PROTECTED]
if you're, say, running a local mysql instance. As a reminder
connections will have to occur to localhost, not the server. Hope this helps.
RE: Connecting from a remote computer
If you have that short a script to test wit, try it from the command line.
Your HTML might be obscuring the error messages.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Kay C. Tien [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 18, 2007 4:32 PM
To: Chris White
Cc: mysql@lists.mysql.com
Subject: Re: Connecting from a remote computer
I got it working perfectly on a Linux server, but my boss wants this
on a Windows IIS server. sigh
The remote connection seems to be working now but I have another
problem now... the page is not doing anything - doesn't seem to be
connecting to the database, but I got no error messages
whatsoever. The page just indicated Done on the bottom. The
mysql_connect.php file is listed below. Is there a privilege problem
or is there something else I need to enable?
Much thanks.
Kay
?php # Script - mysql_connect.php
// This file contains the database access information for the
database. This file also establishes a connection to MySQL and
selects the database.
// Set the database access information as constants.
define ('DB_USER', 'username);
define ('DB_PASSWORD', 'userpass');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'databasename');
// Make the connnection and then select the database.
$dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could
not connect to MySQL: ' . mysql_error() );
mysql_select_db (DB_NAME) OR die ('Could not select the database: ' .
mysql_error() );
// Function for escaping and trimming form data.
function escape_data ($data) {
global $dbc;
if (ini_get('magic_quotes_gpc')) {
$data = stripslashes($data);
}
return mysql_real_escape_string (trim ($data), $dbc);
} // End of escape_data() function.
?
At 10:53 AM 1/18/2007 Thursday, Chris White wrote:
Just to toss something else in here. A lot of times you have a
server that you can connect to by ssh, but because of firewalls,
can't access mysql through. If you can, however, connect to the
database through ssh, you can do port forwarding. In *nix systems
it should be something like this:
ssh -L 3306:server.com:3306 -N -f [EMAIL PROTECTED]
This will forward requests from port 3306 locally to port 3306 on
server.com. If you're on windows, you can also do port forwarding
through putty:
http://www.cs.uu.nl/technical/services/ssh/putty/puttyfw.html
This becomes pretty easy when you get the hang of it. You
can also do like:
ssh -L 3000:server.com:3306 -N -f [EMAIL PROTECTED]
if you're, say, running a local mysql instance. As a reminder
connections will have to occur to localhost, not the server.
Hope this helps.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query]
Please post to the list not to me personnally. Original Message Subject: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query From: John Anderson [EMAIL PROTECTED] Date:Thu, January 18, 2007 10:24 To: William R. Mussatto [EMAIL PROTECTED] -- I optimized every table after I first imported the data. The tables were probably in use, off and on for testing, for about a week after the optimize table was ran on every table before I noticed this problem. I'm not saying the problem didn't exist within that week, I'm just saying I didn't notice it ;) . Another thing. Does the query optimizer keep any sort of statistics and use them to make decisions for future queries on the same table? If so, then that could be the problem because we have certain fields, containing only numbers, but were previously setup as varchars for some unknown reason. I changed them all to int types but some queries in obscure parts of our applications are still querying this field as if it were a character field, using LIKE, etc. I'm slowly but sure tracking those down and fixing them, I'm just curious if that could have anything to do with this strange behavior. Thanks, John A. -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 5:17 PM To: mysql@lists.mysql.com Subject: Re: [PART 2/2] InnoDB - Different EXPLAINs for same query Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: mysql SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 1 | SIMPLE | rb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | sb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | ser | ref| PRIMARY,billedCurrencyCode | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY | 2 | global.ser.billedCurrencyCode |1 | Using index |
FW: [PART 2/2] InnoDB - Different EXPLAINs for same query
I optimized every table after I first imported the data. The tables were probably in use, off and on for testing, for about a week after the optimize table was ran on every table before I noticed this problem. I'm not saying the problem didn't exist within that week, I'm just saying I didn't notice it ;) . Another thing. Does the query optimizer keep any sort of statistics and use them to make decisions for future queries on the same table? If so, then that could be the problem because we have certain fields, containing only numbers, but were previously setup as varchars for some unknown reason. I changed them all to int types but some queries in obscure parts of our applications are still querying this field as if it were a character field, using LIKE, etc. I'm slowly but sure tracking those down and fixing them, I'm just curious if that could have anything to do with this strange behavior. Thanks, John A. -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 5:17 PM To: mysql@lists.mysql.com Subject: Re: [PART 2/2] InnoDB - Different EXPLAINs for same query Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: mysql SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 1 | SIMPLE | rb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | sb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | ser | ref| PRIMARY,billedCurrencyCode | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY | 2 | global.ser.billedCurrencyCode |1 | Using index | ++-+---++--- -+-+-+-- -+--+--+ 5 rows in set (0.00 sec) mysql show index from customerdetail; +++-+--+
Re: Query skips one set of records
In the last episode (Jan 18), Miles Thompson said: The query displayed below performs flawlessly, except for these two records: 7364 M0174000250510 Invoice 2006-12-13 2006-12-13 2006-12-31 7365 M01740 002506 5 Invoice 2006-12-13 2006-12-13 2006-12-31 Here's the table structure: member_idvarchar(6) member_sub_idvarchar(6) pay_method varchar(8) monthly_cost decimal(11,0) anniv_bill_date date dtCreateddate fetch_date date This query: SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) ) ) AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method = 'Invoice' GROUP BY member_id Should return Amount as $280 : ( 10*2 ) + 200 for the first record plus 5 * 12 for the next one. Instead it is returning $180. Not for me: mysql create table subinfo ( member_id varchar(6), member_sub_id varchar(6), pay_method varchar(8),monthly_cost decimal(11,0), anniv_bill_date date, dtCreated date, fetch_date date); Query OK, 0 rows affected (0.03 sec) mysql insert into subinfo values (7364,M01740,Invoice,10,2006-12-13,2006-12-13,2006-12-31), (7365,M01740,Invoice,5,2006-12-13,2006-12-13,2006-12-31); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ), (SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) )) AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method = 'Invoice' GROUP BY member_id; +---+---++ | member_id | member_sub_id | Amount | +---+---++ | 7364 | M01740| 220.00 | | 7365 | M01740| 60.00 | +---+---++ 2 rows in set (0.10 sec) mysql select version(); +---+ | version() | +---+ | 3.23.58 | +---+ 1 row in set (0.00 sec) I get the same result on 5.1.14, too. Try selecting count(*) along with the other columns in your query and verify that another record isn't sneaking in and getting totalled up. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about index usage
Michaël de Groot wrote: Hi guys, I have a question about index usage in MySQL. I have a query: MYSQL: ([EMAIL PROTECTED]) [webstats] EXPLAIN SELECT sum(users) as totaal_uniek, page_id FROM webstats.stats_hour where page_id LIKE 'vipPage_%' and site = 'spelpuntVip' and date 1166353093 group by page_id ORDER BY totaal_uniek DESC LIMIT 10; How many results does this query return: select count(*) from webstats.stats_hour where page_id LIKE 'vipPage_%'; How many rows in the table? It could be that this grabs too many rows and it's easier for mysql to look at the data rather than the index. Try an index on (page_id, site, date) and see how that goes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
