Re: Alter Table Add Column - How Long to update
On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote: On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. I'm just a bit curious as to why this happens. Looking at the manuals / books etc, it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I'm just not too sold on that idea given that, for eg: a MSSQL server, adds a new column in just secs rather than minutes on MySQL. But anyway, I do understand what is happening right now. Thanks. (So, if I were to want to add a column to a Table which has a couple of million rows, It would take a _looong_ time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I do not understand so how does the MSSQL DB work when we alter table add column* * 2006/10/22, Ow Mun Heng [EMAIL PROTECTED]: On Fri, 2006-10-20 at 09:06 -0700, William R. Mussatto wrote: On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. I'm just a bit curious as to why this happens. Looking at the manuals / books etc, it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I'm just not too sold on that idea given that, for eg: a MSSQL server, adds a new column in just secs rather than minutes on MySQL. But anyway, I do understand what is happening right now. Thanks. (So, if I were to want to add a column to a Table which has a couple of million rows, It would take a _looong_ time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
problems with unsubscription
Hello! I cannot unsubscribe from the list. After filling in the form (http://lists.mysql.com/[EMAIL PROTECTED]), no e-mail comes to me for me to approve the unsubscription. P. S. If somebody of the list administrators reads this message, please unsubscribe my address ([EMAIL PROTECTED]) from the list. -- Good luck! Vladimir Please, avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Workbench
Hi everyone. I´m using mysql workbench to design my database and am heaving a snag. How can i rename my object? It always gets the name test and i did not found where can i change its name. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Sun, 2006-10-22 at 21:32 +0800, 黄小聪 wrote: it says this is done so that other read processes can still access the DB/table in it's OLD state w/o any hiccups. I do not understand so how does the MSSQL DB work when we alter table add column Frankly, I don't really know. I tried adding a new column to an existing MSSQL DB and it really just took a few seconds. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_multi startup stop
Sounds like you need to configure a user that has SHUTDOWN privileges. From: http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html Make sure that the MySQL account used for stopping the mysqld servers (with the mysqladmin program) has the same username and password for each server. Also, make sure that the account has the SHUTDOWN privilege. If the servers that you want to manage have different usernames or passwords for the administrative accounts, you might want to create an account on each server that has the same username and password. For example, you might set up a common multi_admin account by executing the following commands for each server: shell mysql -u root -S /tmp/mysql.sock -p Enter password: mysql GRANT SHUTDOWN ON *.* - TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; The username/password goes in your my.cnf under a [mysqld_multi] section. Atle - Flying Crocodile Inc, Unix Systems Administrator On Sat, 21 Oct 2006, Low Kian Seong wrote: Dear all, I am running the official mysql rpms on a sles 9 box, and I am running multiple versions of mysql servers on different ports. The problem I am facing is that when i run mysqld_multi start n it starts the servers up fine but when i do the reverse : mysqld_multi stop n, it doesn't stop the server and I have to manually search for the pids and execute a kill on the process to stop it. Anybody have any idea ? Thank you in advance. Low Kian Seong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
administrtor-user
Can u guys tell me.. How can I be the administrator for the mysql and thenI I need to create users x, y,z and assign them a password. and give them privilages.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: administrtor-user
Hi First, shell useradd mysql Changing UserName and Group name for data directory shell chown -R mysql:mysql data Create Initial database shell scripts/mysql_install_db --user=mysql Start the Server Then to User: First, use the mysql program to connect to the server as the MySQL root user: shell mysql --user=root mysql 1. After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts: mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; [OR] mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; 2. mysql INSERT INTO user (Host,User,Password) VALUES('localhost','UN','PWD'); Then to Change Password: 1. Passwords may be assigned from the command line by using the mysqladmin command: shell mysqladmin -u user_name -h host_name password newpwd 2. Another way to assign a password to an account is to issue a SET PASSWORD statement: mysql SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit'); 3. mysql SET PASSWORD = PASSWORD('biscuit'); To Drop user: 1. mysql DELETE FROM user WHERE User='' and HOST='' Thanks ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, October 23, 2006 9:30 AM Subject: administrtor-user Can u guys tell me.. How can I be the administrator for the mysql and thenI I need to create users x, y,z and assign them a password. and give them privilages.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: administrtor-user
I am using sql 4.1.21. useradd mysql itself is not working Regards - Original Message - From: Visolve DB Team To: Renish ; mysql@lists.mysql.com Sent: Monday, October 23, 2006 12:17 PM Subject: Re: administrtor-user Hi First, shell useradd mysql Changing UserName and Group name for data directory shell chown -R mysql:mysql data Create Initial database shell scripts/mysql_install_db --user=mysql Start the Server Then to User: First, use the mysql program to connect to the server as the MySQL root user: shell mysql --user=root mysql 1. After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts: mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; [OR] mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; 2. mysql INSERT INTO user (Host,User,Password) VALUES('localhost','UN','PWD'); Then to Change Password: 1. Passwords may be assigned from the command line by using the mysqladmin command: shell mysqladmin -u user_name -h host_name password newpwd 2. Another way to assign a password to an account is to issue a SET PASSWORD statement: mysql SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit'); 3. mysql SET PASSWORD = PASSWORD('biscuit'); To Drop user: 1. mysql DELETE FROM user WHERE User='' and HOST='' Thanks ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, October 23, 2006 9:30 AM Subject: administrtor-user Can u guys tell me.. How can I be the administrator for the mysql and thenI I need to create users x, y,z and assign them a password. and give them privilages.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: administrtor-user
Hi, On which platform? It do good in Linux and Unix. Thanks ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: Visolve DB Team [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, October 23, 2006 10:05 AM Subject: Re: administrtor-user I am using sql 4.1.21. useradd mysql itself is not working Regards - Original Message - From: Visolve DB Team To: Renish ; mysql@lists.mysql.com Sent: Monday, October 23, 2006 12:17 PM Subject: Re: administrtor-user Hi First, shell useradd mysql Changing UserName and Group name for data directory shell chown -R mysql:mysql data Create Initial database shell scripts/mysql_install_db --user=mysql Start the Server Then to User: First, use the mysql program to connect to the server as the MySQL root user: shell mysql --user=root mysql 1. After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts: mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; [OR] mysql GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; 2. mysql INSERT INTO user (Host,User,Password) VALUES('localhost','UN','PWD'); Then to Change Password: 1. Passwords may be assigned from the command line by using the mysqladmin command: shell mysqladmin -u user_name -h host_name password newpwd 2. Another way to assign a password to an account is to issue a SET PASSWORD statement: mysql SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit'); 3. mysql SET PASSWORD = PASSWORD('biscuit'); To Drop user: 1. mysql DELETE FROM user WHERE User='' and HOST='' Thanks ViSolve DB Team. - Original Message - From: Renish [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, October 23, 2006 9:30 AM Subject: administrtor-user Can u guys tell me.. How can I be the administrator for the mysql and thenI I need to create users x, y,z and assign them a password. and give them privilages.. -- 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]
Embedded server with the archive storage engine
Hi, Is it possible to use the embedded server with the archive storage engine (version 5.0.24a)? I tried to build it on windows with VS2005, and encountered a few problems. After I managed to build it, I had an assertion failure in a file close operation. I built libmysqld in the following way: Added ha_archive.cpp to the libmysqld project, and defined HAVE_ARCHIVE_DB in the project settings dialog. Then I had a link error. It seems that this so called development environment didn't managed to define the #define properly. (I searched the source tree, but I was unable to find an #undef for it) So I put an explicite #define at the beginning of handler.cpp (before the first #include). After that, it built. But now I have the assertion. Is there anything else I can do, or it is not supposed to work on the first place. Thanks ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]