how to optimize this query
Hi, I've got a query that I believe should be giving me better performance than it is, since I've put indexes on all the appropriate columns...I'll include as much info as I can for anyone wishing to help me optimize the query/tables/indicies...thanks in advance for any assistance... the query: select ind_first_name, ind_last_name, fam_phone, max(iat_date) max_date from tms_individual_account_transactions, tms_families, tms_individuals where fam_key = ind_fam_key and ind_key = iat_ind_key and iat_date = '2000-01-01' group by ind_key order by ind_last_name, ind_first_name; it takes ~150 seconds on a PIII 667 MHz, with 640MB RAM...and returns ~700 rows... MySQL server version: 4.0.18 tms_families: ~1000 records tms_individuals: ~1700 records tms_individual_account_transactions: ~10 records the explain plan: +++---++-++++ | table | type | possible_keys | key| key_len | ref| rows | Extra | +++---++-++++ | tms_families| ALL| PRIMARY | [NULL] | [NULL] | [NULL] | 993 | Using temporary; Using filesort| | tms_individuals| ref| PRIMARY,index_ind_fam_key| index_ind_fam_key| 4 | tms_families.fam_key| 1 || | tms_individual_account_transactions| ref| index_iat_ind_key,index_iat_date| index_iat_ind_key| 4 | tms_individuals.ind_key| 79 | Using where| +++---++-++++ now, I know what's killing the query is the max() and group by, but I still think I should get better performance?? now, the table definitions (sorry for the overkill, but there's referential integrity I needed to maintain with extraneous tables): CREATE TABLE `tms_marriage_statuses` ( `mst_key` int(11) unsigned NOT NULL default '0', `mst_description` varchar(15) NOT NULL default '', PRIMARY KEY (`mst_key`), KEY `unique_mst_description` (`mst_description`) ) TYPE=InnoDB; CREATE TABLE `tms_families` ( `fam_key` int(11) unsigned NOT NULL default '0', `fam_name` varchar(50) NOT NULL default '', `fam_sortname` varchar(25) NOT NULL default '', `fam_address` varchar(100) default '', `fam_city` varchar(50) default '', `fam_prov_state` char(2) default '', `fam_postal_zip_code` varchar(10) default '', `fam_phone` varchar(14) default '', `fam_update_user` varchar(15) default '', `fam_update_date` timestamp(14) NOT NULL, `fam_cmts_family_no` int(11) unsigned default '0', `fam_admin_comments` varchar(255) default NULL, PRIMARY KEY (`fam_key`), UNIQUE KEY `unique_fam_name` (`fam_name`), KEY `index_fam_cmts_family_no` (`fam_cmts_family_no`) ) TYPE=InnoDB; CREATE TABLE `tms_individuals` ( `ind_key` int(11) unsigned NOT NULL default '0', `ind_fam_key` int(11) unsigned NOT NULL default '0', `ind_last_name` varchar(30) NOT NULL default '', `ind_first_name` varchar(20) NOT NULL default '', `ind_middle_name` varchar(20) default NULL, `ind_name_title` varchar(6) default NULL, `ind_name_suffix` char(3) default NULL, `ind_marriage_status` int(11) unsigned NOT NULL default '0', `ind_sex` char(1) NOT NULL default '', `ind_join_date` date default NULL, `ind_birth_date` date default NULL, `ind_envelope_number` varchar(10) default NULL, `ind_update_user` varchar(15) default NULL, `ind_update_date` timestamp(14) NOT NULL, `ind_admin_comments` varchar(255) default NULL, `ind_cmts_member_no` int(11) unsigned default NULL, `ind_deceased` char(1) NOT NULL default 'N', `ind_member` char(1) NOT NULL default 'Y', PRIMARY KEY (`ind_key`), KEY `index_ind_fam_key` (`ind_fam_key`), KEY `index_ind_marriage_status` (`ind_marriage_status`), KEY `index_ind_cmts_member_no` (`ind_cmts_member_no`), CONSTRAINT `tms_individuals_ibfk_1` FOREIGN KEY (`ind_fam_key`) REFERENCES `tms_families` (`fam_key`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `tms_individuals_ibfk_2` FOREIGN KEY (`ind_marriage_status`) REFERENCES `tms_marriage_statuses` (`mst_key`) ) TYPE=InnoDB; CREATE TABLE `tms_accounts` ( `acc_key` int(11) unsigned NOT NULL default '0', `acc_number` int(11) unsigned NOT NULL default '0', `acc_description` varchar(50) NOT NULL default '', `acc_update_user` varchar(15) default '', `acc_update_date` timestamp(14) NOT NULL, PRIMARY KEY (`acc_key`), UNIQUE KEY `unique_acc_number` (`acc_number`) ) TYPE=InnoDB; CREATE TABLE `tms_individual_account_transactions` ( `iat_key` int(11) unsigned NOT NULL default '0', `iat_ind_key` int(11) unsigned NOT NULL default '0', `iat_acc_key` int(11)
Re: InnoDB to MyISAM
Hi, If you're loocking for consurrent inserts/deletes, use innodb (row level locking). This will be better than myisam. The text column can be created in aother table and stay referenced via a FK. This will ceratinly be faster since insert or delete on myisam implies a LOCK table implicit mecanism. Mathias Selon Jeremiah Gowdy [EMAIL PROTECTED]: key relationships? MyISAM can't FK's. Yes it can, they're just not enforced. LOL - that's just like saying that MyISAM supports transactions if you only do transactions that are a single statement... In other words: useles. Somehow I use these useles foreign keys to create relational databases. A foreign key is merely a column which references a primary key or a unique key in the same or another table. I think you're confusing referential constraints with foreign keys. Even in database systems that support referential constraints and cascade dependencies, the usage of such constraints on foreign keys is optional and dependant on your desire to trade-off referential integrity for performance. In some systems, the performance penalty of enforcing referential constraints and doing deletions in dependant tables simply cannot be afforded during high usage hours for databases that serve real-time systems (I work in telecom for example), and therefore one may choose to implement a cleanup process during very low usage hours in the middle of the night to find orphaned records, delete them, and then optimize the table. This allows you to avoid deleting anything but the primary record while people are waiting on the phone, which for MyISAM has the added benefit of allowing concurrent insert/select to continue in the dependent tables. -- 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: InnoDB to MyISAM
key relationships? MyISAM can't FK's. Yes it can, they're just not enforced. LOL - that's just like saying that MyISAM supports transactions if you only do transactions that are a single statement... In other words: useles. Somehow I use these useles foreign keys to create relational databases. A foreign key is merely a column which references a primary key or a unique key in the same or another table. I think you're confusing referential constraints with foreign keys. In my book, referential constraints and foreign key constraints (the full name) are the same. Even in database systems that support referential constraints and cascade dependencies, the usage of such constraints on foreign keys is optional and dependant on your desire to trade-off referential integrity for performance. In some systems, the performance penalty of enforcing referential constraints and doing deletions in dependant tables simply cannot be afforded during high usage hours for databases that serve real-time systems (I work in telecom for example), and therefore one may choose to implement a cleanup process during very low usage hours in the middle of the night to find orphaned records, delete them, and then optimize the table. This allows you to avoid deleting anything but the primary record while people are waiting on the phone, which for MyISAM has the added benefit of allowing concurrent insert/select to continue in the dependent tables. I'm not going to do a serious answer on this one if this is really what you're doing. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sync 2 live MySQL Databases
Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync. I need to sync both ways and basicly live, the every 5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it. Any ideas, sugestion would be welcome. Thanks Cecil Mnr Cecil J.C. Brand Computer Services/ RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2940 Email :[EMAIL PROTECTED] _ University of the Free State: This message and its contents are subject to a disclaimer. Please refer to http://www.uovs.ac.za/disclaimer for full details. Universiteit van die Vrystaat: Hierdie boodskap en sy inhoud is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by http://www.uovs.ac.za/vrywaring beskikbaar. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update combine values
Hi am fairly new to mysql and need some help, i have search thru online documents but found not much help to this. my question is that, is it possible to combine values of multiple fields in another field of same table with an update statement such as this; UPDATE UserTable SET FullName = FirstName MiddleName LastName; i appreciate any help, thanks ismet
RE: update combine values
Ismet Dere mailto:[EMAIL PROTECTED] wrote: my question is that, is it possible to combine values of multiple fields in another field of same table with an update statement such as this; UPDATE UserTable SET FullName = FirstName MiddleName LastName; UPDATE UserTable SET FullName = CONCAT(IFNULL(CONCAT(FirstName, ' '), ''), IFNULL(CONCAT(MiddleName, ' '), ''), IFNULL(LastName,'')); Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB to MyISAM
Hi, If you're loocking for consurrent inserts/deletes, use innodb (row level locking). This will be better than myisam. I'm looking for concurrent inserts / selects, reading and writing. The key is to get the reads and blocking writes (writes that need the insert ID, which can't be done DELAYED) done as quickly as possible. Deletes are relatively unimportant as long as the primary record is marked deleted. Cleaning up the FK linked records is of no consequence in this particular application, and can be deferred to a nightly process. This will ceratinly be faster since insert or delete on myisam implies a LOCK table implicit mecanism. Insert does not imply a LOCK TABLE if there are no deleted records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync 2 live MySQL Databases
On 26/05/2005 6:40 p.m., Cecil Brand wrote: Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync. I need to sync both ways and basicly live, the every 5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it. Unfortunately, the simple answer is you can't. While it is possible to set up multi-master replication with MySQL, this is not useful for the vast majority of applications. If you are sending write queries to both masters then you have the possibility of the two servers becoming out of sync. This is particularly true with auto_increment primary keys - if you have a table and you send one insert statement to each server, both at the same time, then they might both assign the same auto_increment number to each row, and then they will both be forced to ignore the replicated inserts they receive from each other - screwing your table. Your options are to use MySQL Cluster (not an option for a lot of people as your entire database has to be stored in RAM), or if you're just after high availability, set up one-way replication with dynamic master failover (which I am currently trying to figure out how to do myself). -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
maximum size of mysql server-id field
Does anyone know what the range for server-id can be? Is it a 16 bit number? 32 bit? I can't seem to find it in the documentation or via google. I guess most people just do server-id = 1 and server-id = 2 but for large setups, that becomes difficult to manage and there are better ways of tying a slave server-id to a machine (last few digits of MAC addr, IP addr, etc...) Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB to MyISAM
I think you're confusing referential constraints with foreign keys. In my book, referential constraints and foreign key constraints (the full name) are the same. Yes, referential constraints and foreign key constraints are the same thing. Notice the word constraints. Let me say this again: A foreign key is merely a column which references a primary key or a unique key in the same or another table. A referential constraint (or foreign key constraint if you prefer) defines an integrity condition that must be satisfied by all the rows in two tables. See the difference? So yes, MyISAM has foreign keys, no MyISAM does not have foreign key constraints. Kindly don't go around saying MyISAM doesn't have foreign keys because it simply makes no sense. Referential integrety is not required for foreign keys. The only thing you need to have a foreign key is a column which contains the value of a primary key or unique key in another table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync 2 live MySQL Databases
This is precisly the problem that I'm sitting with. It look like unless MySQL AB desides to build such a feature into one of the future releases this would beable to work. Mnr Cecil J.C. Brand Computer Services/ RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2940 Email :[EMAIL PROTECTED] Simon Garner [EMAIL PROTECTED] 5/26/2005 8:56:14 AM On 26/05/2005 6:40 p.m., Cecil Brand wrote: Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync. I need to sync both ways and basicly live, the every 5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it. Unfortunately, the simple answer is you can't. While it is possible to set up multi-master replication with MySQL, this is not useful for the vast majority of applications. If you are sending write queries to both masters then you have the possibility of the two servers becoming out of sync. This is particularly true with auto_increment primary keys - if you have a table and you send one insert statement to each server, both at the same time, then they might both assign the same auto_increment number to each row, and then they will both be forced to ignore the replicated inserts they receive from each other - screwing your table. Your options are to use MySQL Cluster (not an option for a lot of people as your entire database has to be stored in RAM), or if you're just after high availability, set up one-way replication with dynamic master failover (which I am currently trying to figure out how to do myself). -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ University of the Free State: This message and its contents are subject to a disclaimer. Please refer to http://www.uovs.ac.za/disclaimer for full details. Universiteit van die Vrystaat: Hierdie boodskap en sy inhoud is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by http://www.uovs.ac.za/vrywaring beskikbaar. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB to MyISAM
I think you're confusing referential constraints with foreign keys. In my book, referential constraints and foreign key constraints (the full name) are the same. Yes, referential constraints and foreign key constraints are the same thing. Notice the word constraints. Let me say this again: A foreign key is merely a column which references a primary key or a unique key in the same or another table. A referential constraint (or foreign key constraint if you prefer) defines an integrity condition that must be satisfied by all the rows in two tables. See the difference? So yes, MyISAM has foreign keys, no MyISAM does not have foreign key constraints. Kindly don't go around saying MyISAM doesn't have foreign keys because it simply makes no sense. Referential integrety is not required for foreign keys. The only thing you need to have a foreign key is a column which contains the value of a primary key or unique key in another table. Right. We can agree on this wording if you like. I've never met it before in such a way though :-) Nevertheless, foreign key constraints belong in the database, not in your application... If you have foreign keys (your wording), you need foreign key constraints. Period. Plain and simple. No discussion :-) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum size of mysql server-id field
On Wed, May 25, 2005 11:59 pm, David said: Does anyone know what the range for server-id can be? Is it a 16 bit number? 32 bit? I can't seem to find it in the documentation or via google. I did dogpile.com for my.cnf format server-id and found: http://mysqld.active-venture.com/Replication_Options.html which claims it's a 32-bit non-negative. range from 1 to 2^32 - 1 I dunno if they are authoritative or not, mind you... 32-bit that would have been my first guess, fwiw... -- Like Music? http://l-i-e.com/artists.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB to MyISAM
Martijn Tonies mailto:[EMAIL PROTECTED] wrote: Nevertheless, foreign key constraints belong in the database, not in your application... If you have foreign keys (your wording), you need foreign key constraints. Period. Plain and simple. No discussion :-) Foreign keys are foreign keys. Constraints are constraints. Foreign key constraints are... well, you do the math. So, in your opinion, MySql was never really a relational database until whatever version enforcing refential constraints was released? Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB to MyISAM
Nevertheless, foreign key constraints belong in the database, not in your application... If you have foreign keys (your wording), you need foreign key constraints. Period. Plain and simple. No discussion :-) Foreign keys are foreign keys. Constraints are constraints. Foreign key constraints are... well, you do the math. So, in your opinion, MySql was never really a relational database until whatever version enforcing refential constraints was released? I told you I wouldn't get into that. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB to MyISAM
Peter Normann wrote: Martijn Tonies mailto:[EMAIL PROTECTED] wrote: Nevertheless, foreign key constraints belong in the database, not in your application... If you have foreign keys (your wording), you need foreign key constraints. Period. Plain and simple. No discussion :-) Foreign keys are foreign keys. Constraints are constraints. Foreign key constraints are... well, you do the math. So, in your opinion, MySql was never really a relational database until whatever version enforcing refential constraints was released? - if you want the full half-hour argument on whether RDBMS are *really* relational, check out http://www.dbdebunk.com/index.html :-) - 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: restarting mysql server
Hello. Probably mysqld_safe uses wrong values for directories. Possible solution is to store the correct paths in configuration file, and if it is situated not in standard location, launch mysqld_safe with --defaults-file=path_to_config_file. Or use command line options for mysqld_safe. Check the error log, it should be in /var/lib/mysql. See: http://dev.mysql.com/doc/mysql/en/program-options.html Dear there, After install mysql 4.1.12 by using rpm, I have some problems to restart server after shutting down it. When I use mysqld_safe try to restart, it give me error as follws: Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050525 18:08:04 mysqld ended Then, I notice in /var/run directory, there is no directory called mysqld. And I also not familiar with mysqld.pid file. But when I use /etc/init.d/mysql start, the server can restart again. So, what's wrong? Maybe the path /var/run/mysqld is not right? Another quesion is when I use SELECT Host, User FROM mysql.user; command in mysql, it give me error: ERROR 1046 (3D000): No database selected. what is mysql.user database? Is it created automatically after start mysql first time? I just beginner of mysql. Please help! Thanks a lot! ying Ying Sun [EMAIL PROTECTED] 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: Can't Start MySql
Hello. It could be a bug, because you're using rather deprecated software (Win98) and a very old MySQL 4.0.14. A Z [EMAIL PROTECTED] wrote: MySQL 4.0.14 Win98 platform. what could cause the following error by typing mysqld --console. regards 050525 16:50:13 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 1 1545457619 InnoDB: Doing recovery: scanned up to log sequence number 1 1545457619 050525 16:50:13 InnoDB: Flushing modified pages from the buffer pool... 050525 16:50:13 InnoDB: Started C:\TEAM\SQL\BIN\MYSQLD.EXE: ready for connections. Version: '4.0.14-max-debug' socket: '' port: 3306 050525 16:50:14 InnoDB: Assertion failure in thread 4293331611 in file ../innob ase/include\page0page.ic line 482 InnoDB: Failing assertion: offs UNIV_PAGE_SIZE InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com InnoDB: Thread 4293334199 stopped in file C:\build\build\mysql-4.0.14b\innobase\ ___ How much free photo storage do you get? Store your holiday snaps for FREE with Yahoo! Photos http://uk.photos.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: Lost connection to MySQL server during query problem
Hello. Does SHOW STATUS executed from JDBC client return increased values of this parameters? Could your server die during queries from JDBC (check the error log)? Sometimes you should increase interactive_timeout as well. A lot of variables usually could be changed using SET statement. See: http://dev.mysql.com/doc/mysql/en/system-variables.html Sometimes when querying mySQL 4.1.11 on Linux machine I get the error Lost connection to MySQL server during query. Here are the symptoms 1.When running the query from the server it returns OK 2.When running the query from another machine using the mySQL query browser it returns OK 3.When running the query from another machine from Java using mySQL JDBC it fails with Lost connection to... 4.When running the query from another machine using software like EMS it fails with Lost connection to... 5.The error is always after 4.7 seconds and only in heavy queries (not only SELECT, even DELETES ) The problem is probably not in the communication parameters, like max_allowed_packet or connection_timeout on the server because I increased then Does anyone know how can I control those parameters from JDBC? Amir Shay [EMAIL PROTECTED] 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]
problem when running mysql server
I have instal mysql in /usr/local/mysql. When I run the server. The message showed on the screen is as follows: [EMAIL PROTECTED] bin]# ./safe_mysqld [1] 5122 [EMAIL PROTECTED] bin]# Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050526 18:05:14 mysqld ended I think the server is closed automatically. I check the mysqld.log. It says: 050526 17:46:09 mysqld started Warning: Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line 050526 17:46:09 InnoDB: Started 050526 17:46:09 /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050526 17:46:09 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050526 17:46:09 Aborting 050526 17:46:09 InnoDB: Starting shutdown... 050526 17:46:11 InnoDB: Shutdown completed 050526 17:46:11 /usr/local/mysql/bin/mysqld: Shutdown Complete 050526 17:46:11 mysqld ended What is the problem? _ MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: problem when running mysql server
qin lei wrote: I have instal mysql in /usr/local/mysql. When I run the server. The message showed on the screen is as follows: [snip] Warning: Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line 050526 17:46:09 InnoDB: Started 050526 17:46:09 /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050526 17:46:09 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) Did you run the script that install the initial tables : (excerpts from TFM): To initialize the grant tables, use one of the following commands, depending on whether mysql_install_db is located in the bin or scripts directory: shell bin/mysql_install_db --user=mysql shell scripts/mysql_install_db --user=mysql Peter Normann People fall into 10 categories. Those who understands binary, and those who don't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update combine values
Hi, try this : mysql select * from usertable; +--+---++--+ | fullname | firstname | Middlename | lastname | +--+---++--+ | NULL | Scott | Junior | Tiger| +--+---++--+ 1 row in set (0.00 sec) mysql UPDATE UserTable SET FullName = concat(FirstName,' ',MiddleName, ' ',LastName); Query OK, 1 row affected (0.19 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from usertable; ++---++--+ | fullname | firstname | Middlename | lastname | ++---++--+ | Scott Junior Tiger | Scott | Junior | Tiger| ++---++--+ 1 row in set (0.00 sec) You can also define the separator once using concat_WS. see http://dev.mysql.com/doc/mysql/en/string-functions.html for more details. Mathias Selon Ismet Dere [EMAIL PROTECTED]: Hi am fairly new to mysql and need some help, i have search thru online documents but found not much help to this. my question is that, is it possible to combine values of multiple fields in another field of same table with an update statement such as this; UPDATE UserTable SET FullName = FirstName MiddleName LastName; i appreciate any help, thanks ismet -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
percentage query
Hey all, I have some difficulties building a querie. Table objects type_id area Table 4dtypes type_id name what I need is a query who gives me the folowing result: name (grouped by type_id) - sum of area -- percentage of total area so I need to group the records on type_id, but how can I get the percentage? This is my query till now: SELECT 4dtypes.name, Sum(objects_0.area) FROM 4dtypes, objects where 4dtypes.type_id=objects.type_id GROUP BY 4dtypes.type_id I hope you understand, Thx in advance. Reinhart
