Re: MySQL slow when altering tables.
At 01:32 PM 7/20/2007, Andrew Rosolino wrote: Whenever I alter a mysql table like add a new field it gets really slow and all other processes lag. Is there a memory variable I can adjust for this? It may be slow because if it is rebuilding the indexes to the table. Don't forget that when you Alter a table, it creates a copy of the table and after the data is loaded into the new table, the indexes have to be rebuilt. Run Show ProcessList when the server is running slow to see what it is actually doing. If you have a few million rows in the table and lots of indexes, you may want to increase the size of key_buffer to a few hundred MB if you have the memory. The upper limit I think on 32 bit machines is 2GB Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seeing Table Structure
Hi; I need to see the table structure of all the tables of a database I have. I could also just dump all the data from all the tables in the database and copy the database, thus grabbing the table structures. I can´t find in the documentation how to do either of those. Please advise. TIA, Tony AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com.
how to SUM over TIME col (without TIME_TO_SEC)?
Hi, how can i get the SUM of a TIME field without using TIME_TO_SEC()? example: SELECT SUM(`my_time`) FROM `table` GROUP BY `id` returns: 765 some completely wrong result, not formated in as TIME SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(`my_time`))) FROM `table` GROUP BY `id` returns: 838:59:59 correct result in TIME format, but with rounding errors -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeing Table Structure
I don't know of any way of doing this for all tables. I wrote a python script that creates a html file with information about tables (engine, fields,keys,indices) If you are interested in it I can email it Olaf On 7/23/07 11:34 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi; I need to see the table structure of all the tables of a database I have. I could also just dump all the data from all the tables in the database and copy the database, thus grabbing the table structures. I can´t find in the documentation how to do either of those. Please advise. TIA, Tony AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeing Table Structure
Try this: # mysqldump -d DBNAME tables.sql --no-data, -d Do not write any row information for the table. This is very useful if you want to dump only the CREATE TABLE statement for the table. On Mon, July 23, 2007 18:22, Olaf Stein wrote: I don't know of any way of doing this for all tables. I wrote a python script that creates a html file with information about tables (engine, fields,keys,indices) If you are interested in it I can email it Olaf On 7/23/07 11:34 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi; I need to see the table structure of all the tables of a database I have. I could also just dump all the data from all the tables in the database and copy the database, thus grabbing the table structures. I can´t find in the documentation how to do either of those. Please advise. TIA, Tony AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max_connect_errors
Hello, Does MySQL store a current error count (for each host?) internally that I can enquire on? I did not find this information in the MySQL manual. If the current error count is available, so that I can monitor it for specific hosts and issue a alert when it crosses a threshold. Please help! Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you.
su-like functionality
Is there a way for mysql to login as an administrator and su to a normal user? What I'd like to achieve is a way to log in to our clients' accounts (we are a web-hosting company) without having to use their passwords. Having to su keeps ownerships and stuff like that in check. Thanks in advance, Carlo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: su-like functionality
That does not work ... Ownership should not be an issue as all files should belong to the mysql user anyway Just create a second user for the individual databases (you could also just create one account that can do everything, though I do not recommend that) with more rights and use that account for administrative work Olaf On 7/23/07 4:19 AM, Carlo Sogono [EMAIL PROTECTED] wrote: Is there a way for mysql to login as an administrator and su to a normal user? What I'd like to achieve is a way to log in to our clients' accounts (we are a web-hosting company) without having to use their passwords. Having to su keeps ownerships and stuff like that in check. Thanks in advance, Carlo - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance of extended insert vs. load data
all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a load data infile against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables can be done in advance). thanks!
Re: performance of extended insert vs. load data
On 7/23/07, Sid Lane [EMAIL PROTECTED] wrote: is either one significantly faster than the other? Yes, LOAD DATA INFILE is much faster. are there additional (faster) approaches I have not thought of? Not that I've found. I think you'd have to write directly to the C API to beat LOAD DATA INFILE. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of extended insert vs. load data
I think you will find the load data infile will work faster. I am performing testing right now in preparation for a migration from 4.1 to 5.0 but I am confident that will be the case. Keith - Original Message - From: Sid Lane [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 23, 2007 1:44:53 PM (GMT-0500) America/New_York Subject: performance of extended insert vs. load data all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a load data infile against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables can be done in advance). thanks! -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877
Re: performance of extended insert vs. load data
At 11:44 AM 7/23/2007, Sid Lane wrote: all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a load data infile against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables can be done in advance). thanks! Load data will of course be much faster. However to obtain the maximum speed you need to load the data to an empty table, because then MySQL will load the data without updating the index for every row that's added, and will instead rebuild the index only after all data has been loaded, which of course is much faster. The other alternative is to remove all indexes prior to using load data and build the index manually when the data has been loaded. (Unique indexes may pose a problem because it will throw an error if it finds a duplicate index- but if you verified uniqueness in Oracle, then it won't give you a problem) The big drawback with Load Data is of course there is no exception log kept. You only know the number of rows added. With Insert you at least have a chance to log the errors yourself. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of extended insert vs. load data
On 7/23/07, mos [EMAIL PROTECTED] wrote: Load data will of course be much faster. However to obtain the maximum speed you need to load the data to an empty table, because then MySQL will load the data without updating the index for every row that's added, and will instead rebuild the index only after all data has been loaded, which of course is much faster. He's using InnoDB tables, so it doesn't make much difference. MyISAM tables have a fast bulk index rebuild, but InnoDB do not. The other alternative is to remove all indexes prior to using load data and build the index manually when the data has been loaded. The easiest way to do that is with ALTER TABLE DISABLE KEYS and ENABLE KEYS. But again, not much help with InnoDB tables. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeing Table Structure
Yes, please, email it to me. Tony -Original Message- From: Olaf Stein [EMAIL PROTECTED] To: [EMAIL PROTECTED]; MySql mysql@lists.mysql.com Sent: Mon, 23 Jul 2007 12:22 pm Subject: Re: Seeing Table Structure I don't know of any way of doing this for all tables. wrote a python script that creates a html file with information about ables (engine, fields,keys,indices) If you are interested in it I can email it Olaf On 7/23/07 11:34 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi; I need to see the table structure of all the tables of a database I have. I could also just dump all the data from all the tables in the database and copy the database, thus grabbing the table structures. I can´t find in the documentation how to do either of those. Please advise. TIA, Tony AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com. -- ySQL General Mailing List or list archives: http://lists.mysql.com/mysql o unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com.
Re: Seeing Table Structure
That works. Thanks. Tony -Original Message- From: Mogens Melander [EMAIL PROTECTED] To: Olaf Stein [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; MySql mysql@lists.mysql.com Sent: Mon, 23 Jul 2007 12:29 pm Subject: Re: Seeing Table Structure Try this: # mysqldump -d DBNAME tables.sql --no-data, -d Do not write any row information for the table. This is very useful if you want to dump only the CREATE TABLE statement for the table. On Mon, July 23, 2007 18:22, Olaf Stein wrote: I don't know of any way of doing this for all tables. I wrote a python script that creates a html file with information about tables (engine, fields,keys,indices) If you are interested in it I can email it Olaf On 7/23/07 11:34 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi; I need to see the table structure of all the tables of a database I have. I could also just dump all the data from all the tables in the database and opy the database, thus grabbing the table structures. I can´t find in the documentation how to do either of those. Please advise. TIA, Tony AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. - ater Mogens Melander 45 40 85 71 38 66 870 133 224 -- his message has been scanned for viruses and angerous content by MailScanner, and is elieved to be clean. - ySQL General Mailing List or list archives: http://lists.mysql.com/mysql o unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] AOL now offers free email to everyone. Find out more about what's free from AOL at AOL.com.
Re: su-like functionality
On Mon, July 23, 2007 10:19, Carlo Sogono wrote: Is there a way for mysql to login as an administrator and su to a normal user? What I'd like to achieve is a way to log in to our clients' accounts (we are a web-hosting company) without having to use their passwords. Having to su keeps ownerships and stuff like that in check. Thanks in advance, Carlo ??? I'm having a hard time imagine what you want to do with that ??? You, being the administrator (root), you should be able to do whatever, on any DB on your server, without being recognzied as anybody. Please elaborate. -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of extended insert vs. load data
Shure, load data is way faster than full inserts. I was thinking: while $warnings -lt 100% do dump ora-data | mysql database done swap IP-addr. On Mon, July 23, 2007 19:59, B. Keith Murphy wrote: I think you will find the load data infile will work faster. I am performing testing right now in preparation for a migration from 4.1 to 5.0 but I am confident that will be the case. Keith - Original Message - From: Sid Lane [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 23, 2007 1:44:53 PM (GMT-0500) America/New_York Subject: performance of extended insert vs. load data all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a load data infile against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables can be done in advance). thanks! -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 (o) 919-433-0786 (c) 850-637-3877 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql dump help!
Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
One common error is trying to do this inside the mysql console, instead of the shell prompt, i think thats your problem because at least the first one is correct. Carlos Red Hope wrote: Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Oh boy, and here's the big stupid question. How do I get to the shell prompt? Sorry! Yea, I'm that newbie. :) Thank you, Lillian --- Carlos Proal [EMAIL PROTECTED] wrote: One common error is trying to do this inside the mysql console, instead of the shell prompt, i think thats your problem because at least the first one is correct. Carlos Red Hope wrote: Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos more. http://mobile.yahoo.com/go?refer=1GNXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Well hopefully, typing quit at the mysql console get you back to the shell. Otherwise, you need to open a Gnome Console, KDE Konsole or Windows CommandPrompt from the different menus on your operating system If the mysql directory is on your PATH you can use mysqldump anywhere, if not, move to the mysql/bin directory and then you can run it without any problem. Hope that help. Carlos Red Hope wrote: Oh boy, and here's the big stupid question. How do I get to the shell prompt? Sorry! Yea, I'm that newbie. :) Thank you, Lillian --- Carlos Proal [EMAIL PROTECTED] wrote: One common error is trying to do this inside the mysql console, instead of the shell prompt, i think thats your problem because at least the first one is correct. Carlos Red Hope wrote: Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos more. http://mobile.yahoo.com/go?refer=1GNXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian --- Carlos Proal [EMAIL PROTECTED] wrote: Well hopefully, typing quit at the mysql console get you back to the shell. Otherwise, you need to open a Gnome Console, KDE Konsole or Windows CommandPrompt from the different menus on your operating system If the mysql directory is on your PATH you can use mysqldump anywhere, if not, move to the mysql/bin directory and then you can run it without any problem. Hope that help. Carlos Red Hope wrote: Oh boy, and here's the big stupid question. How do I get to the shell prompt? Sorry! Yea, I'm that newbie. :) Thank you, Lillian --- Carlos Proal [EMAIL PROTECTED] wrote: One common error is trying to do this inside the mysql console, instead of the shell prompt, i think thats your problem because at least the first one is correct. Carlos Red Hope wrote: Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos more. http://mobile.yahoo.com/go?refer=1GNXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: su-like functionality
Mogens Melander wrote: On Mon, July 23, 2007 10:19, Carlo Sogono wrote: Is there a way for mysql to login as an administrator and su to a normal user? What I'd like to achieve is a way to log in to our clients' accounts (we are a web-hosting company) without having to use their passwords. Having to su keeps ownerships and stuff like that in check. Thanks in advance, Carlo ??? I'm having a hard time imagine what you want to do with that ??? You, being the administrator (root), you should be able to do whatever, on any DB on your server, without being recognzied as anybody. Please elaborate. Well first of all I'm a software developer so I do not know much about administering the database, and even as a programmer I know only general SQL commands and am not familiar with mysql-specific details. We're implementing the Single Sign-On feature of phpMyAdmin. Our control panel software logging our clients automatically into PMA by populating the PMA session with their login credentials...however this would mean that we have to store and retrieve their passwords (not ideal!). If I used an administrator's password, then they would be able to see and edit via PMA all the other databases in the system. Having an su-like feature allows us to login to their account using their own username without having to keep their passwords. Hope that was clear. Other suggestions on getting around this problem is also welcome. Regards, Carlo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
mysqldump -u user -p -q --single-transaction --tab=/dest/dir database or mysql -u root -ppassword database output.sql On Tue, July 24, 2007 03:16, Red Hope wrote: Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Hmmm, try \qenter before typing mysqldump ;^) Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
I think we are daling with a windows user, who are not that familiar with a command prompt. Correct me if i'm wrong. How to get to a point where you would be able to execute a mysqldump, will depend on what OS you are running, and how you installed MySQL. On Tue, July 24, 2007 04:41, Carlos Proal wrote: Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: su-like functionality
Ok, if memory serves me right, phpmyadmin use the credentials from mysql's grant/deny schema. So when loggin into PMA, you will have the priveleges on the server that was granted to that user. When installing/configuring PMA, it will insist on getting a root/privilleged users login/password to use for creating new databases/users ao. Not having the root/superuser's access will keep you from manipulating other users DB/tables. There is no way that you can simulate being another user like log in as A and change to B. You have to GRANT A priveleges to B's databases, to do stuff on them. It's probably too late for me to make sense, so i leave it here for now. On Tue, July 24, 2007 04:54, Carlo Sogono wrote: Mogens Melander wrote: On Mon, July 23, 2007 10:19, Carlo Sogono wrote: Is there a way for mysql to login as an administrator and su to a normal user? What I'd like to achieve is a way to log in to our clients' accounts (we are a web-hosting company) without having to use their passwords. Having to su keeps ownerships and stuff like that in check. Thanks in advance, Carlo ??? I'm having a hard time imagine what you want to do with that ??? You, being the administrator (root), you should be able to do whatever, on any DB on your server, without being recognzied as anybody. Please elaborate. Well first of all I'm a software developer so I do not know much about administering the database, and even as a programmer I know only general SQL commands and am not familiar with mysql-specific details. We're implementing the Single Sign-On feature of phpMyAdmin. Our control panel software logging our clients automatically into PMA by populating the PMA session with their login credentials...however this would mean that we have to store and retrieve their passwords (not ideal!). If I used an administrator's password, then they would be able to see and edit via PMA all the other databases in the system. Having an su-like feature allows us to login to their account using their own username without having to keep their passwords. Hope that was clear. Other suggestions on getting around this problem is also welcome. Regards, Carlo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeing Table Structure
If you have MySQL5 you can show table structure via the 'information_schema' pseudo-database. This has the advantage of dumping column data for all tables in one output table. In addition, you can select/sort the metadata using standard SQL: [EMAIL PROTECTED] show tables from information_schema; +---+ | Tables_in_information_schema | +---+ | CHARACTER_SETS| | COLLATIONS| | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | KEY_COLUMN_USAGE | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | STATISTICS| | TABLES| | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | VIEWS | | USER_PRIVILEGES | +---+ 16 rows in set (0.00 sec) [EMAIL PROTECTED] describe information_schema.columns; +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | TABLE_CATALOG| varchar(1024) | YES | | NULL| | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(21)| NO | | 0 | | | COLUMN_DEFAULT | varchar(64) | YES | | NULL| | | IS_NULLABLE | varchar(3)| NO | | | | | DATA_TYPE| varchar(64) | NO | | | | | CHARACTER_MAXIMUM_LENGTH | bigint(21)| YES | | NULL| | | CHARACTER_OCTET_LENGTH | bigint(21)| YES | | NULL| | | NUMERIC_PRECISION| bigint(21)| YES | | NULL| | | NUMERIC_SCALE| bigint(21)| YES | | NULL| | | CHARACTER_SET_NAME | varchar(64) | YES | | NULL| | | COLLATION_NAME | varchar(64) | YES | | NULL| | | COLUMN_TYPE | longtext | NO | | | | | COLUMN_KEY | varchar(3)| NO | | | | | EXTRA| varchar(20) | NO | | | | | PRIVILEGES | varchar(80) | NO | | | | | COLUMN_COMMENT | varchar(255) | NO | | | | +--+---+--+-+-+---+ 19 rows in set (0.02 sec) [EMAIL PROTECTED] select table_name,column_name,data_type from information_schema.columns where table_schema='sedberg' and table_name like 'elp%' order by column_name; ++-+---+ | table_name | column_name | data_type | ++-+---+ | elpfiles | check_time | datetime | | elpfiles | filename| varchar | | elpfiles | file_base | varchar | | elpfiles | file_date | datetime | | elpfiles | file_ext| varchar | | elpfiles | file_group | int | | elpfiles | file_owner | int | | elpfiles | file_sha1 | varchar | | elpfiles | file_size | bigint| | elpfiles | pathname| varchar | | elpfiles | _id | int | ++-+---+ 11 rows in set (0.03 sec) Keep in mind that (1) this database is read-only, and (2) I wouldn't try joining other tables against information_schema tables for any heavily-used applications. The information_schema information is generated on-the-fly (as far as I understand it), so it has no indices and queries cannot be optimized well. See http://dev.mysql.com/doc/refman/5.0/en/information-schema.html for more information. steve At 6:29 PM +0200 7/23/07, Mogens Melander wrote: Try this: # mysqldump -d DBNAME tables.sql --no-data, -d Do not write any row information for the table. This is very useful if you want to dump only the CREATE TABLE statement for the table. On Mon, July 23, 2007 18:22, Olaf Stein wrote: I don't know of any way of doing this for all tables. I wrote a python script that creates a html file with information about tables (engine, fields,keys,indices) If you are interested in it I can email it Olaf On 7/23/07 11:34 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi; I need to see the table structure of all the tables of a database I have. I could also just dump all the data from all the tables in the database and copy the database,
Lost Connection
Hi All, I have connect to mysql-server using client. When i run the query it give message lost connection to mysql. The details is as below. mysql select user_id, user_name from user_info; ERROR 2013 (HY000): Lost connection to MySQL server during query. System configuration is Pentium 2.6 Ghz Ram 1 GB My.cnf key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 max_connections= 50 #table_cache= 64 #thread_concurrency = 10 sort_buffer_size= 1M innodb_buffer_pool_size = 70M innodb_log_buffer_size = 2M innodb_additional_mem_pool_size = 8M table_cache = 64 thread_cache_size = 8 read_buffer_size= 1M read_rnd_buffer_size= 2M myisam_sort_buffer_size = 4M # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 16M I don't understand why the connection get lost during simplest query also. Regards, Krishna
Lost Connection
Hi All, I have connect to mysql-server using client. When i run the query it give message lost connection to mysql. The details is as below. mysql select user_id, user_name from user_info; ERROR 2013 (HY000): Lost connection to MySQL server during query. System configuration is Pentium 2.6 Ghz Ram 1 GB My.cnf key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 max_connections= 50 #table_cache= 64 #thread_concurrency = 10 sort_buffer_size= 1M innodb_buffer_pool_size = 70M innodb_log_buffer_size = 2M innodb_additional_mem_pool_size = 8M table_cache = 64 thread_cache_size = 8 read_buffer_size= 1M read_rnd_buffer_size= 2M myisam_sort_buffer_size = 4M # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 16M I don't understand why the connection get lost during simplest query also. Regards, Krishna
Re: Lost Connection
This can be a serious issue, maybe a sign of some kind of data corruption. If you lost the connection, probably the server is restarting (you can check that on the error log), do this happen only with this table ? Carlos krishna chandra prajapati wrote: Hi All, I have connect to mysql-server using client. When i run the query it give message lost connection to mysql. The details is as below. mysql select user_id, user_name from user_info; ERROR 2013 (HY000): Lost connection to MySQL server during query. System configuration is Pentium 2.6 Ghz Ram 1 GB My.cnf key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 max_connections= 50 #table_cache= 64 #thread_concurrency = 10 sort_buffer_size= 1M innodb_buffer_pool_size = 70M innodb_log_buffer_size = 2M innodb_additional_mem_pool_size = 8M table_cache = 64 thread_cache_size = 8 read_buffer_size= 1M read_rnd_buffer_size= 2M myisam_sort_buffer_size = 4M # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 16M I don't understand why the connection get lost during simplest query also. Regards, Krishna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of extended insert vs. load data
mysqlimport with parallel threads is worth giving a try. It is similar to 'load data infile' but with concurrent threads loading the tables. I think , it was added in mysql-5.1.18. But it is said to work with previous versions also according to the author : http://krow.livejournal.com/519655.html -Ravi B. Keith Murphy wrote: I think you will find the load data infile will work faster. I am performing testing right now in preparation for a migration from 4.1 to 5.0 but I am confident that will be the case. Keith - Original Message - From: Sid Lane [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, July 23, 2007 1:44:53 PM (GMT-0500) America/New_York Subject: performance of extended insert vs. load data all, I need to migrate ~12GB of data from an Oracle 10 database to a MySQL 5.0one in as short a window as practically possible (throw tablespace in r/o, migrate data repoint web servers - every minute counts). the two approaches I am considering are: 1. write a program that outputs the Oracle data to a fifo pipe (mknod) and running a load data infile against it 2. write a program that dynamically builds extended insert statements up to length of max_allowed_packet (similar to mysqldump -e) is either one significantly faster than the other? I know I could benchmark it but I was hoping someone could save me writing #2 to find out if it's not the way to go... are there additional (faster) approaches I have not thought of? FWIW these are 95% innodb (5% myisam are static reference tables can be done in advance). thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Hey y'all, I use charming Windows XP on here. I've taken database classes but lucky for me we never used *real* MySQL. Below I typed up exactly what I put into the MySQL prompt and this is what it kicks back to me. Lillian mysql mysql \R shell PROMPT set to 'shell' shell shellmysqldump -u root -ppassword test test.sql; ERROR 1064 4200: You have an error in your SQL syntax; checkthe manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -ppassword test test.sql' at line 1 --- Carlos Proal [EMAIL PROTECTED] wrote: Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mailp=summer+activities+for+kidscs=bz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
try this mysqldump -u root -ppassword --database test test.sql On 7/24/07, Red Hope [EMAIL PROTECTED] wrote: Hey y'all, I use charming Windows XP on here. I've taken database classes but lucky for me we never used *real* MySQL. Below I typed up exactly what I put into the MySQL prompt and this is what it kicks back to me. Lillian mysql mysql \R shell PROMPT set to 'shell' shell shellmysqldump -u root -ppassword test test.sql; ERROR 1064 4200: You have an error in your SQL syntax; checkthe manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -ppassword test test.sql' at line 1 --- Carlos Proal [EMAIL PROTECTED] wrote: Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mailp=summer+activities+for+kidscs=bz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Yep, good for you, welcome to the real world You are changing the prompt but are still inside the dbms, you need to get out, because mysqldump is an application (.exe file) not a sql command, ie Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.0.41-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql mysql quit Bye D:\lillianmysqldump -u root -ppassword test test.sql; -- but maybe after the quit, the window will close, if that happens, open a new command prompt from start - programs - accesories -- command prompt and then run mysqldump Carlos Red Hope wrote: Hey y'all, I use charming Windows XP on here. I've taken database classes but lucky for me we never used *real* MySQL. Below I typed up exactly what I put into the MySQL prompt and this is what it kicks back to me. Lillian mysql mysql \R shell PROMPT set to 'shell' shell shellmysqldump -u root -ppassword test test.sql; ERROR 1064 4200: You have an error in your SQL syntax; checkthe manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -ppassword test test.sql' at line 1 --- Carlos Proal [EMAIL PROTECTED] wrote: Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mailp=summer+activities+for+kidscs=bz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
