Definition of mysql_server_init
Hi All I am trying to port my MySQL code on HP-UX, and am anuable to link my code as I am getting the standard functions like mysql_query(), mysql_store_result() etc as undefined. I am currently linking my code with libmysqlclient_r.a. Do I need any other library to be linked as well?? -- Bye for now, Nishant God Gave Me Nothing I Wanted, He Gave Me Everything I Needed !
Query not using indexes?
Here is a query I'm trying to run. It doesn't look like it is overly complex and, granted, it's joining on a few tables, I wouldn't imagine it would take 6.5s to execute. SELECT TRIM( users.username ) AS username, TRIM( games.game_name ) AS game_name, CASE WHEN 0 != UNIX_TIMESTAMP( MAX( collections.modified )) THEN TRIM( MAX( collections.modified )) ELSE TRIM( MAX( collections.created )) END AS modified, TRIM( users.hide_inventory ) AS hide_inventory FROM collections INNER JOIN users ON users.id = collections.user_id INNER JOIN game_pieces ON game_pieces.id = collections.game_piece_id INNER JOIN games_sets ON games_sets.id = game_pieces.games_set_id INNER JOIN games ON games.id = games_sets.game_id GROUP BY users.username, games.game_name ORDER BY users.username; 167 rows in set (6.49 sec) Table collections has 76,328 rows Table users has 291 rows Table game_pieces has 5,491 rows Table games_sets has 29 rows Table games has 3 rows Table games has a FK for games_sets which has a FK for game_pieces. All are InnoDB tables on a MySQL 5.x database Here is the same query EXPLAINed: *** 1. row *** id: 1 select_type: SIMPLE table: games type: index possible_keys: PRIMARY key: ix_games_GameName key_len: 102 ref: NULL rows: 3 Extra: Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: games_sets type: ref possible_keys: PRIMARY,ix_games_sets_games_sets,ix_games_sets_games key: ix_games_sets_games key_len: 8 ref: cake_communal_haven.games.id rows: 4 Extra: Using index *** 3. row *** id: 1 select_type: SIMPLE table: game_pieces type: ref possible_keys: PRIMARY,ix_game_pieces_games_set_id,ix_game_pieces_set_piece key: ix_game_pieces_games_set_id key_len: 9 ref: cake_communal_haven.games_sets.id rows: 127 Extra: Using where; Using index *** 4. row *** id: 1 select_type: SIMPLE table: collections type: ref possible_keys: ix_collections_game_piece_id,ix_collections_user_id_game_piece_id ,ix_collections_user_id key: ix_collections_game_piece_id key_len: 8 ref: cake_communal_haven.game_pieces.id rows: 23 Extra: *** 5. row *** id: 1 select_type: SIMPLE table: users type: ref possible_keys: PRIMARY key: PRIMARY key_len: 22 ref: cake_communal_haven.collections.user_id rows: 1 Extra: 5 rows in set (0.00 sec) In the EXPLAIN, it doesn't look like any indexes are being used in rows 4 and 5. Is that right? I'm looking at all the columns that are being joined and they all do have indexes. If I take out the tables game_pieces, games_sets and games and remove TRIM( games.game_name ) AS game_name from the select, the execution time goes down to 1.9 seconds so it seems as if it isn't using an appropriate index from either/any of those tables. Is there anything I can do to speed this query up? Or is the joining of 76k+ rows to 5k+ rows (plus the other tables) really going to slow things down that significantly? I can't imagine that it would because I'm sure there are alot of other people using MySQL on much larger databases. thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySSQL on HP-UX
Hi, Nishant Gupta wrote: [/usr/local/mysql-5.1.12-beta-hpux11.11-hppa2.0w]scripts/mysql_install_db --user=mysql chown: unknown user id mysql Installing all prepared tables 061212 19:39:30 [ERROR] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! Did you do that? Is there a user called mysql? regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query not using indexes?
Make sure that all joined fields are of identical types, otherwise the query executor must cast each and every join value, which may be affecting the query time... Jay Chris Boget wrote: Here is a query I'm trying to run. It doesn't look like it is overly complex and, granted, it's joining on a few tables, I wouldn't imagine it would take 6.5s to execute. SELECT TRIM( users.username ) AS username, TRIM( games.game_name ) AS game_name, CASE WHEN 0 != UNIX_TIMESTAMP( MAX( collections.modified )) THEN TRIM( MAX( collections.modified )) ELSE TRIM( MAX( collections.created )) END AS modified, TRIM( users.hide_inventory ) AS hide_inventory FROM collections INNER JOIN users ON users.id = collections.user_id INNER JOIN game_pieces ON game_pieces.id = collections.game_piece_id INNER JOIN games_sets ON games_sets.id = game_pieces.games_set_id INNER JOIN games ON games.id = games_sets.game_id GROUP BY users.username, games.game_name ORDER BY users.username; 167 rows in set (6.49 sec) Table collections has 76,328 rows Table users has 291 rows Table game_pieces has 5,491 rows Table games_sets has 29 rows Table games has 3 rows Table games has a FK for games_sets which has a FK for game_pieces. All are InnoDB tables on a MySQL 5.x database Here is the same query EXPLAINed: *** 1. row *** id: 1 select_type: SIMPLE table: games type: index possible_keys: PRIMARY key: ix_games_GameName key_len: 102 ref: NULL rows: 3 Extra: Using index; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: games_sets type: ref possible_keys: PRIMARY,ix_games_sets_games_sets,ix_games_sets_games key: ix_games_sets_games key_len: 8 ref: cake_communal_haven.games.id rows: 4 Extra: Using index *** 3. row *** id: 1 select_type: SIMPLE table: game_pieces type: ref possible_keys: PRIMARY,ix_game_pieces_games_set_id,ix_game_pieces_set_piece key: ix_game_pieces_games_set_id key_len: 9 ref: cake_communal_haven.games_sets.id rows: 127 Extra: Using where; Using index *** 4. row *** id: 1 select_type: SIMPLE table: collections type: ref possible_keys: ix_collections_game_piece_id,ix_collections_user_id_game_piece_id ,ix_collections_user_id key: ix_collections_game_piece_id key_len: 8 ref: cake_communal_haven.game_pieces.id rows: 23 Extra: *** 5. row *** id: 1 select_type: SIMPLE table: users type: ref possible_keys: PRIMARY key: PRIMARY key_len: 22 ref: cake_communal_haven.collections.user_id rows: 1 Extra: 5 rows in set (0.00 sec) In the EXPLAIN, it doesn't look like any indexes are being used in rows 4 and 5. Is that right? I'm looking at all the columns that are being joined and they all do have indexes. If I take out the tables game_pieces, games_sets and games and remove TRIM( games.game_name ) AS game_name from the select, the execution time goes down to 1.9 seconds so it seems as if it isn't using an appropriate index from either/any of those tables. Is there anything I can do to speed this query up? Or is the joining of 76k+ rows to 5k+ rows (plus the other tables) really going to slow things down that significantly? I can't imagine that it would because I'm sure there are alot of other people using MySQL on much larger databases. thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySSQL on HP-UX
well the problem was olved when i used the follwoing command scripts/mysql_install_db --user=root bin/mysqld_safe --user=root Gues by default it was not creating the mysql user. Now on prompt mysql is running fine... But... As I am trying to port my MySQL code on HP-UX, and am unable to link my code as I am getting the standard functions like mysql_query(), mysql_store_result() etc as undefined. I am currently linking my code with libmysqlclient_r.a. Do I need any other library to be linked as well?? Note that the same code runs perfectlty fine on Linux On 12/13/06, Nils Meyer [EMAIL PROTECTED] wrote: Hi, Nishant Gupta wrote: [/usr/local/mysql-5.1.12-beta-hpux11.11-hppa2.0w ]scripts/mysql_install_db --user=mysql chown: unknown user id mysql Installing all prepared tables 061212 19:39:30 [ERROR] Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! Did you do that? Is there a user called mysql? regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Bye for now, Nishant God Gave Me Nothing I Wanted, He Gave Me Everything I Needed !
Re: Query not using indexes?
Make sure that all joined fields are of identical types, otherwise the query executor must cast each and every join value, which may be affecting the query time... Can you even define fields having foreign keys to be of a different type? Anyway, taking a look at my JOIN INNER JOIN users ON users.id = collections.user_id INNER JOIN game_pieces ON game_pieces.id = collections.game_piece_id INNER JOIN games_sets ON games_sets.id = game_pieces.games_set_id INNER JOIN games ON games.id = games_sets.game_id users.id is VARCHAR(20) and is a FK to collections.user_id which is VARCHAR(20) game_pieces.id is BIGINT(20) and is a FK to collections.game_piece_id which is BIGINT(20) games_sets.id is BIGINT(20) and is a FK to game_pieces.games_set_id which is BIGINT(20) games.id is BIGINT(20) and is a FK to games_sets.game_id which is BIGINT(20) thnx, Christoph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
system : Install Mysql 5.x binaries with php4 on Debian
Hello, I use Debian Linux, and I could install (with apt-get install) fine the Mysql-4.1+apache+php4. I tried fine Mysql 5. on the same debian machine. Now I want to use mysql 5. through web/php4 Then I run into problem, like : Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) in /var/www/test/t1.php on line 9 Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /var/www/test/t1.php on line 10 from script.php : connexion a la base refuse the tool my_print_defaults doesn't help. any hint? thank you _-¯-_-¯-_-¯-_-¯-_ Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying DB to new structure
We have a moderately sized database, more than 5GB in size, several million rows and 70 tables. We're running MySQL 5.22 and the database uses innodb throughout with multiple foreign keys in use. During development the structure of several tables has been changed many times, such that we now have a number of rendundent columns. We've created a new, empty database with our proposed new structure and I'm now looking for the most efficient way to get our existing data into this new structure, dropping any data in columns that no longer exist. Can anyone propose a sensible way to go about this? Because we're using innodb, dropping columns one at a time takes an age as every index is rebuilt. Just laoding the database from a mysqldump file takes about five hours so I'm pretty sure we'll want to load data from our old database into the new db with the new structure - if anyone can recommend a strategy to do that, or suggest an alternative, I'd be most appreciative! Thanks, Russell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
account that can only do backup
Hi, I want to create a MySQL user account that can only do database backup but nothing else. What privileges does such an account need? I'm having trouble with connecting to mysql.com to look at the documents. It's just spinning forever. Actually, the connection to mysql.com has become extremely slow since I noticed the mysql.com was redesigned. Thanks in advance, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Copying DB to new structure
Well, Maybe 70 pairs of select into outfile - load data infile. At least, this way you can select only columns you want to be in your new database. Best, Mikhail Berman -Original Message- From: Russell Horn [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 13, 2006 12:41 PM To: mysql@lists.mysql.com Subject: Copying DB to new structure We have a moderately sized database, more than 5GB in size, several million rows and 70 tables. We're running MySQL 5.22 and the database uses innodb throughout with multiple foreign keys in use. During development the structure of several tables has been changed many times, such that we now have a number of rendundent columns. We've created a new, empty database with our proposed new structure and I'm now looking for the most efficient way to get our existing data into this new structure, dropping any data in columns that no longer exist. Can anyone propose a sensible way to go about this? Because we're using innodb, dropping columns one at a time takes an age as every index is rebuilt. Just laoding the database from a mysqldump file takes about five hours so I'm pretty sure we'll want to load data from our old database into the new db with the new structure - if anyone can recommend a strategy to do that, or suggest an alternative, I'd be most appreciative! Thanks, Russell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.27: character problem
Hello, I'm using MySQL version 5.0.27 under Windows XP professional. I have a text file with some SQL commands (I create a few tables and insert some rows into them). I noticed that all columns where I tried to insert a swedish character, that character got corrupted. But it works if I type the same explicitly in mysql monitor. What do I need to do so I can use command files and still have proper handling of swedish characters? - Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
definition of Created_tmp_files in show status
According to the mysql reference manual, the definition of this field is: How many temporary files mysqld has created. Can someone elaborate on this? What causes mysql to create a temporary file? I see something indicating it may be associated with replication. In our environment (which has replication) we see counts for this variable in the tens of thousands. Thanks in advance, Kevin Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com
Re: account that can only do backup
On 12/13/06, Bing Du [EMAIL PROTECTED] wrote: Hi, I want to create a MySQL user account that can only do database backup but nothing else. What privileges does such an account need? I'm having trouble with connecting to mysql.com to look at the documents. It's just spinning forever. Actually, the connection to mysql.com has become extremely slow since I noticed the mysql.com was redesigned. It all depends on HOW you're performing your backup, but using mysqldump, I noticed that SELECT and LOCK TABLES is enough, and a simple Google search confirmed that someone else thinks the same: http://dannyman.toldme.com/2006/08/22/mysql-backup-grant-privileges/ -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for another database. If you have even one of these you must make all updates in the default database if you want them to be replicated. This is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to 5.0 the same day which explains why we did not see this behavior sooner. I removed the option from the config file and just used replicate-ignore-db instead and all is right in the world again. Hope this helps someone else down the road. Thanks for all of your helpful information and suggestions. http://dev.mysql.com/doc/refman/5.0/en/binary-log.html -Ed Logan, David (SST - Adelaide) wrote: Hi Ed, You may like to look at this, especially the last statement. If you are not using these options however, I would suspect a bug. Regards --replicate-do-db=db_name Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database. An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated: USE prices; UPDATE sales.january SET amount=amount+1000; The main reason for this just check the default database behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need. If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 6.9, How Servers Evaluate Replication Rules. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Wednesday, 13 December 2006 10:55 AM To: Ed Pauley II Cc: mysql@lists.mysql.com Subject: Re: Replication Problem? Ed, this is unfortunately by design. Personally I don't get why this choice was made... Reference: http://lists.mysql.com/mysql/190869 Regards, Atle FCI, Inc. - Unix Systems Administrator On Tue, 12 Dec 2006, Ed Pauley II wrote: We recently upgraded to MySQL 5.0. Since upgrading I have noticed that queries of the form INSERT INTO test.test_table VALUES('test','1') no longer replicate. If you connect to or change to the test database and then execute INSERT INTO test_table VALUES('test','1') the query replicates. Is this normal behavior? Is there a configuration setting that I can change to make replication accept explicit database naming in the query? Any help would be greatly appreciated! -- Ed Pauley II [EMAIL PROTECTED] -- Ed Pauley II [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help*
I am a novice with Unix and user of MySQL on windows….. I have a problem, i.e. I want to install MySQL5.0 at my FreeBSD 6.1 box with following configurations: --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static How and at What place, can I configure the Makefile under /usr/ports/databases/mysql50-server/ to have MySQL server working with above mentioned configurations? -- Thanks! BR / vj
Help: Installation problem
I am a novice with Unix and user of MySQL on windows….. I have a problem, i.e. I want to install MySQL5.0 at my FreeBSD 6.1 box with following configurations: --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static How and at What place, can I configure the Makefile under /usr/ports/databases/mysql50-server/ to have MySQL server working with above mentioned configurations? I have spent 7 days trying to solve this problem please help!!! -- Thanks! BR / vj -- Thanks! BR / vj
Re: Replication Problem? - SOLVED
It turns out that I have a binlog-ignore-db option configured for another database. If you have even one of these you must make all updates in the default database if you want them to be replicated. This is a change in behavior from 4.0 to 4.1. We upgraded from 4.0 to 4.1 to 5.0 the same day which explains why we did not see this behavior sooner. I removed the option from the config file and just used replicate-ignore-db instead and all is right in the world again. Hope this helps someone else down the road. Thanks for all of your helpful information and suggestions. http://dev.mysql.com/doc/refman/5.0/en/binary-log.html -Ed Logan, David (SST - Adelaide) wrote: Hi Ed, You may like to look at this, especially the last statement. If you are not using these options however, I would suspect a bug. Regards --replicate-do-db=db_name Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database. Note that this does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while having selected a different database or no database. An example of what does not work as you might expect: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated: USE prices; UPDATE sales.january SET amount=amount+1000; The main reason for this just check the default database behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need. If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 6.9, How Servers Evaluate Replication Rules. --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Wednesday, 13 December 2006 10:55 AM To: Ed Pauley II Cc: mysql@lists.mysql.com Subject: Re: Replication Problem? Ed, this is unfortunately by design. Personally I don't get why this choice was made... Reference: http://lists.mysql.com/mysql/190869 Regards, Atle FCI, Inc. - Unix Systems Administrator On Tue, 12 Dec 2006, Ed Pauley II wrote: We recently upgraded to MySQL 5.0. Since upgrading I have noticed that queries of the form INSERT INTO test.test_table VALUES('test','1') no longer replicate. If you connect to or change to the test database and then execute INSERT INTO test_table VALUES('test','1') the query replicates. Is this normal behavior? Is there a configuration setting that I can change to make replication accept explicit database naming in the query? Any help would be greatly appreciated! -- Ed Pauley II [EMAIL PROTECTED] -- Ed Pauley II Internet Software Developer Bloodstock Research Information Services 859.223. 800.354.9206 ext. 297 [EMAIL PROTECTED] http://www.brisnet.com http://www.brisbet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: account that can only do backup
On 12/13/06, Bing Du [EMAIL PROTECTED] wrote: Hi, I want to create a MySQL user account that can only do database backup but nothing else. What privileges does such an account need? I'm having trouble with connecting to mysql.com to look at the documents. It's just spinning forever. Actually, the connection to mysql.com has become extremely slow since I noticed the mysql.com was redesigned. It all depends on HOW you're performing your backup, but using mysqldump, I noticed that SELECT and LOCK TABLES is enough, and a simple Google search confirmed that someone else thinks the same: http://dannyman.toldme.com/2006/08/22/mysql-backup-grant-privileges/ Thanks much for the response, Daniel. Sorry I did not make it clear in my original post. Yes, you've read my mind. I use mysqldump. The pointer you provided was very helpful. Appreciate it. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to determine MyISAM sort order?
Hi, Is there a way to find which index was used to sort MyISAM table (suppose someone run myisamchk --sort-index --sort-records=2 in the past, how to find that sorting was done based on index #2?) And a related question: how to determine which is the index #2? I guess if I look at the order of indexes in the 'SHOW CREATE TABLE name' report that will do it. Is that the right way? Is there more official way? thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine MyISAM sort order?
In the last episode (Dec 13), Jacek Becla said: Is there a way to find which index was used to sort MyISAM table (suppose someone run myisamchk --sort-index --sort-records=2 in the past, how to find that sorting was done based on index #2?) You can run a select * from mytable and see whether any columns are in order; that's about it. Note that if records were inserted/deleted since the sort, no column may be completely in order. And a related question: how to determine which is the index #2? I guess if I look at the order of indexes in the 'SHOW CREATE TABLE name' report that will do it. Is that the right way? Is there more official way? You can also use SHOW FIELDS from name, or select column_name from information_schema.columns where table_name=name and table_schema=schema and ordinal_position=2; -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unlimited client connections for MySQL
Hello. I'm developing a client application for several platforms that will need to connect to a remote MySQL database. Unfortunately, MySQL refuses connections from external IP's that aren't allowed and since the clients using this will be on unknown IP addresses (their home computers), I'm in a bit of a situation. How does one setup a MySQL account with no IP restrictions? Thanks, Brent Anderson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unlimited client connections for MySQL
GRANT (ALL|SELECT|INSERT|UPDATE|DELETE|etc) ON DATABASE.* TO 'user'@'%' IDENTIFIED BY 'password' See http://dev.mysql.com/doc/refman/5.0/en/grant.html for details. Note that localhost is considered as a special case, not included in the wildcard % HTH, Dan On 12/13/06, Brent Anderson [EMAIL PROTECTED] wrote: Hello. I'm developing a client application for several platforms that will need to connect to a remote MySQL database. Unfortunately, MySQL refuses connections from external IP's that aren't allowed and since the clients using this will be on unknown IP addresses (their home computers), I'm in a bit of a situation. How does one setup a MySQL account with no IP restrictions? Thanks, Brent Anderson -- 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: Unlimited client connections for MySQL
Brent Anderson wrote: Hello. I'm developing a client application for several platforms that will need to connect to a remote MySQL database. Unfortunately, MySQL refuses connections from external IP's that aren't allowed and since the clients using this will be on unknown IP addresses (their home computers), I'm in a bit of a situation. How does one setup a MySQL account with no IP restrictions? You probably have a line in your my.cnf that restricts the server to only listen on the localhost address. Look for bind-address = 127.0.0.1 Comment that line out, restart the server, and it should accept connections from all client machines (assuming that you don't have other firewall restrictions as well). Note however that this can be a big security hole. /dwight -- Dwight N. Tovey [EMAIL PROTECTED] http://www.dtovey.net/~dwight/ Please Do Not send me Microsoft Word attachments. See http://www.gnu.org/philosophy/no-word-attachments.html --- Work to Live : Live to Ride : Ride to Work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: definition of Created_tmp_files in show status
Hi, A replication slave needs some of its temporary files to survive a machine restart so that it can replicate temporary tables or LOAD DATA INFILE operations. If files in the temporary file directory are lost when the server restarts, replication fails. MySQL creates temporary files as hidden files. tmp_table_size variable will determine the size of the temporary table. But if it exceeds, then server automatically converts it to disk-based tables. The server variables, Created_tmp_tables -The number of in-memory temporary tables created automatically by the server while executing statements. Created_tmp_disk_tables -The number of temporary tables on disk created automatically by the server while executing statements. Created_tmp_files - How many temporary files mysqld has created. were used to determine the temporary files status. Thanks ViSolve DB Team Original Message - From: Kevin Fries [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, December 14, 2006 12:57 AM Subject: definition of Created_tmp_files in show status According to the mysql reference manual, the definition of this field is: How many temporary files mysqld has created. Can someone elaborate on this? What causes mysql to create a temporary file? I see something indicating it may be associated with replication. In our environment (which has replication) we see counts for this variable in the tens of thousands. Thanks in advance, Kevin Do you Yahoo!? Everyone is raving about the all-new Yahoo! Mail beta. http://new.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help: Installation problem
VeeJay wrote: I am a novice with Unix and user of MySQL on windows….. I have a problem, i.e. I want to install MySQL5.0 at my FreeBSD 6.1 box with following configurations: --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static How and at What place, can I configure the Makefile under /usr/ports/databases/mysql50-server/ to have MySQL server working with above mentioned configurations? Wouldn't it be more appropriate to ask a FreeBSD list this question? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]