Newbie First Use Connection Question - Mac OSX 10.5.6
I am brand new to MySQL and JAVA/Netbeans 6.5 so please excuse the stupid questions ... 1. I have just downloaded and successfully installed MySQL v5.1 on my MacBook Pro running OS X 10.5.6 2. I have also downloaded and installed MySQL Tools: Administrator Query Browser (I come from a Visual Basic MS SQL Server 2000/2005 environment) When you install MS SQL server the default login is sa with a blank password. My question is; How do I login (connection settings) to MySQL (for Administrator and Query Browser tools)? (I start my server by going to settings and then MySQL icon, Start Server, so my server is running) Unfortunately, I have never seen MySQL in action nor do I know anyone to ask/show me how to get started.
Re: Newbie First Use Connection Question - Mac OSX 10.5.6
The root Password will be blank after initial install.
You can set it at a shell prompt with commands something like this...
shell mysql -u root
mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql FLUSH PRIVILEGES;
mysql EXIT;
Where newpwd is your desired password.
Best to secure or delete all accounts with blank passwords :)
You can then start MySQL administrator and log in using the username root,
the password you specified in the SET PASSWORD command and the host of
localhost.
Regards
John
I am brand new to MySQL and JAVA/Netbeans 6.5 so please excuse the
stupid questions ...
1. I have just downloaded and successfully installed MySQL v5.1 on my
MacBook Pro running OS X 10.5.6
2. I have also downloaded and installed MySQL Tools: Administrator
Query Browser
(I come from a Visual Basic MS SQL Server 2000/2005 environment)
When you install MS SQL server the default login is sa with a blank
password.
My question is;
How do I login (connection settings) to MySQL (for Administrator and
Query Browser tools)?
(I start my server by going to settings and then MySQL icon, Start
Server, so my server is running) Unfortunately, I have never seen
MySQL in action nor do I know anyone to ask/show me how to get started.
__
This email has been scanned by Netintelligence
http://www.netintelligence.com/email
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie First Use Connection Question - Mac OSX 10.5.6
or as the docs read:
shell mysqladmin password your password
John Daisley wrote:
The root Password will be blank after initial install.
You can set it at a shell prompt with commands something like this...
shell mysql -u root
mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql FLUSH PRIVILEGES;
mysql EXIT;
Where newpwd is your desired password.
Best to secure or delete all accounts with blank passwords :)
You can then start MySQL administrator and log in using the username root,
the password you specified in the SET PASSWORD command and the host of
localhost.
Regards
John
I am brand new to MySQL and JAVA/Netbeans 6.5 so please excuse the
stupid questions ...
1. I have just downloaded and successfully installed MySQL v5.1 on my
MacBook Pro running OS X 10.5.6
2. I have also downloaded and installed MySQL Tools: Administrator
Query Browser
(I come from a Visual Basic MS SQL Server 2000/2005 environment)
When you install MS SQL server the default login is sa with a blank
password.
My question is;
How do I login (connection settings) to MySQL (for Administrator and
Query Browser tools)?
(I start my server by going to settings and then MySQL icon, Start
Server, so my server is running) Unfortunately, I have never seen
MySQL in action nor do I know anyone to ask/show me how to get started.
__
This email has been scanned by Netintelligence
http://www.netintelligence.com/email
Left join does not work with Count() as expected
I have two simple tables. One - list of forums, second - list of messages and i want to get the list of forums with number of messages in each. Here is the query: SELECT forums.id , COUNT( forum_msg.id ) AS cnt FROM forums LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id ORDER BY forums.sorder ASC The problem is that if a forum does not have any messages then the line with such forums.id does not appear at all. If i delete COUNT( forum_msg.id ) AS cnt from Select - i get all forums, as expected. If i leave the count() as shown - i get only forums with messages in the result. As far as i can remember it was not like this before. I am running 5.1.3 -- Artem Kuchin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Left join does not work with Count() as expected
You need to group by forum_id... On 2/19/09 11:09 AM, Artem Kuchin mat...@itlegion.ru wrote: I have two simple tables. One - list of forums, second - list of messages and i want to get the list of forums with number of messages in each. Here is the query: SELECT forums.id , COUNT( forum_msg.id ) AS cnt FROM forums LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id ORDER BY forums.sorder ASC The problem is that if a forum does not have any messages then the line with such forums.id does not appear at all. If i delete COUNT( forum_msg.id ) AS cnt from Select - i get all forums, as expected. If i leave the count() as shown - i get only forums with messages in the result. As far as i can remember it was not like this before. I am running 5.1.3 -- Artem Kuchin - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Left join does not work with Count() as expected
SELECT forums.id , COUNT( forum_msg.id ) AS cnt FROM forums LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id ORDER BY forums.sorder ASC Missing GROUP BY. PB - Artem Kuchin wrote: I have two simple tables. One - list of forums, second - list of messages and i want to get the list of forums with number of messages in each. Here is the query: SELECT forums.id , COUNT( forum_msg.id ) AS cnt FROM forums LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id ORDER BY forums.sorder ASC The problem is that if a forum does not have any messages then the line with such forums.id does not appear at all. If i delete COUNT( forum_msg.id ) AS cnt from Select - i get all forums, as expected. If i leave the count() as shown - i get only forums with messages in the result. As far as i can remember it was not like this before. I am running 5.1.3 -- Artem Kuchin Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.235 / Virus Database: 270.10.23/1951 - Release Date: 2/13/2009 6:51 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Left join does not work with Count() as expected
Following the documentation available at http://dev.mysql.com/doc/refman/5.0/en/join.html t1t2 a b a c --- --- 1 x 2 z 2 y 3 w Then a natural left join would product these results mysql SELECT * FROM t1 NATURAL LEFT JOIN t2; +--+--+--+ | a| b| c| +--+--+--+ |1 | x| NULL | |2 | y| z| +--+--+--+ A Left join would produce these results) (Row1 is included but 3 is leftout because it doesnt have a corresponding row in t1) mysql SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a); +--+--+--+--+ | a| b| a| c| +--+--+--+--+ |1 | x| NULL | NULL | |2 | y|2 | z| +--+--+--+--+ (Row1 is included but 3 is leftout because it doesnt have a corresponding row in t1) Your query SELECT forums.id , COUNT( forum_msg.id ) AS cnt FROM forums LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id ORDER BY forums.sorder ASC assuming you have the data Forums f Forum_msg fm f.idf.cnt fm.id fm.cnt - - 1 1 2 2 2 2 2 3 3 YIELDS these results --- if.id f.cnt fm.id fm.cnt -- - -- --- 1 1NULL 0 2 22 2 Martin Gainty __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Thu, 19 Feb 2009 19:09:04 +0300 From: mat...@itlegion.ru To: mysql@lists.mysql.com Subject: Left join does not work with Count() as expected I have two simple tables. One - list of forums, second - list of messages and i want to get the list of forums with number of messages in each. Here is the query: SELECT forums.id , COUNT( forum_msg.id ) AS cnt FROM forums LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id ORDER BY forums.sorder ASC The problem is that if a forum does not have any messages then the line with such forums.id does not appear at all. If i delete COUNT( forum_msg.id ) AS cnt from Select - i get all forums, as expected. If i leave the count() as shown - i get only forums with messages in the result. As far as i can remember it was not like this before. I am running 5.1.3 -- Artem Kuchin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ See how Windows connects the people, information, and fun that are part of your life. http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
InnoDB - CREATE INDEX - Locks table for too long
I need to add an index on a table on a production server. It is one 7Gb InnoDB table with single .ibd file (one_file_per_table), the index creation on preprod server took 40 minutes but table was smaller. I tried to add the index but was locking all applications on production and had to kill it. I have requested a maintenance window but it will take long time. Since this application is scanning like crazy I'd like to do it a.s.a.p. Do you have any hint for a non locking solution? I have created smaller indexes and brought the average rows retrieved via full table scan from 2 Million to 400.000 per second, now I just need this last index! Thanks to Baron for the slow-query-log analyzer tool, it is simple but perfect! And I used it without patching the server. I have easily found the worst queries just watching three values: elapsed time, number of occurences, and rows retrieved. And I discovered all the tables which needed indexing, so simple, so good! Thanks Baron! Cheers Claudio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
enabling storage engine with RPM install
We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not enabled. We install from RPMs so I'm not sure how to enable the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to enable a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Resend: enabling storage engine with RPM install
Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the database. The innodb engine is not supported by the database. We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not installed with it. We install from RPMs so I'm not sure how to install the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to install a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Resend: enabling storage engine with RPM install
Where on earth did you get an RPM that doesn't have InnoDB support? I find this unlikely. I think it is more likely that you have some configuration error that's causing InnoDB to disable itself on start. How do you know InnoDB isn't supported? And by isn't supported I mean isn't compiled into mysqld. Per your commend that InnoDB wasn't installed with mysqld -- it is not separate. It's built into the /usr/sbin/mysqld binary (or whatever that is on your system). For example, look at this: strings /usr/sbin/mysqld | grep -i innodb If you see a bunch of lines starting with InnoDB: blah blah, you have a binary that includes InnoDB, and it's just disabled for some reason. Baron On Thu, Feb 19, 2009 at 6:58 PM, Jim Lyons jlyons4...@gmail.com wrote: Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the database. The innodb engine is not supported by the database. We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not installed with it. We install from RPMs so I'm not sure how to install the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to install a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Resend: enabling storage engine with RPM install
Yes, I had a slip of the mind. The engine that was not supported by the install is the Federated engine. I apologize, I had a blind spot. The SHOW ENGINES command lists FEDERATED but has NO in the Support column. The question, though, is how does one add an unsupported engine to an RPM install? Is it possible? Otherwise I have to either compile from source or upgrade to a version that I hope will have it. On Thu, Feb 19, 2009 at 10:08 PM, Baron Schwartz ba...@xaprb.com wrote: Where on earth did you get an RPM that doesn't have InnoDB support? I find this unlikely. I think it is more likely that you have some configuration error that's causing InnoDB to disable itself on start. How do you know InnoDB isn't supported? And by isn't supported I mean isn't compiled into mysqld. Per your commend that InnoDB wasn't installed with mysqld -- it is not separate. It's built into the /usr/sbin/mysqld binary (or whatever that is on your system). For example, look at this: strings /usr/sbin/mysqld | grep -i innodb If you see a bunch of lines starting with InnoDB: blah blah, you have a binary that includes InnoDB, and it's just disabled for some reason. Baron On Thu, Feb 19, 2009 at 6:58 PM, Jim Lyons jlyons4...@gmail.com wrote: Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the database. The innodb engine is not supported by the database. We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not installed with it. We install from RPMs so I'm not sure how to install the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to install a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Resend: enabling storage engine with RPM install
Perhaps you disabled it via my.cnf Sent via BlackBerry from T-Mobile -Original Message- From: Jim Lyons jlyons4...@gmail.com Date: Thu, 19 Feb 2009 22:45:01 To: MySQLmysql@lists.mysql.com Subject: Re: Resend: enabling storage engine with RPM install Yes, I had a slip of the mind. The engine that was not supported by the install is the Federated engine. I apologize, I had a blind spot. The SHOW ENGINES command lists FEDERATED but has NO in the Support column. The question, though, is how does one add an unsupported engine to an RPM install? Is it possible? Otherwise I have to either compile from source or upgrade to a version that I hope will have it. On Thu, Feb 19, 2009 at 10:08 PM, Baron Schwartz ba...@xaprb.com wrote: Where on earth did you get an RPM that doesn't have InnoDB support? I find this unlikely. I think it is more likely that you have some configuration error that's causing InnoDB to disable itself on start. How do you know InnoDB isn't supported? And by isn't supported I mean isn't compiled into mysqld. Per your commend that InnoDB wasn't installed with mysqld -- it is not separate. It's built into the /usr/sbin/mysqld binary (or whatever that is on your system). For example, look at this: strings /usr/sbin/mysqld | grep -i innodb If you see a bunch of lines starting with InnoDB: blah blah, you have a binary that includes InnoDB, and it's just disabled for some reason. Baron On Thu, Feb 19, 2009 at 6:58 PM, Jim Lyons jlyons4...@gmail.com wrote: Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the database. The innodb engine is not supported by the database. We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not installed with it. We install from RPMs so I'm not sure how to install the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to install a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Resend: enabling storage engine with RPM install
How does one do that in my.cnf? It is possible to disable a supported engine by screwing up the my.cnf configuration. For example, I once pointed the InnoDB data file to a directory that still had root as its owner. The Innodb engined appeared as DISABLED in the SHOW ENGINES output, but it was supported. I chown'd the directory to the proper owner and it was fine. (This may be why I mistyped InnoDB in my first post - I had InnoDB on my mind.) In the case of the FEDERATED engine in my database, it's not supported at all. I don't think I can turn support on or off in my.cnf. I would love it, if I could, though. On Thu, Feb 19, 2009 at 10:47 PM, chaim.rie...@gmail.com wrote: Perhaps you disabled it via my.cnf Sent via BlackBerry from T-Mobile -Original Message- From: Jim Lyons jlyons4...@gmail.com Date: Thu, 19 Feb 2009 22:45:01 To: MySQLmysql@lists.mysql.com Subject: Re: Resend: enabling storage engine with RPM install Yes, I had a slip of the mind. The engine that was not supported by the install is the Federated engine. I apologize, I had a blind spot. The SHOW ENGINES command lists FEDERATED but has NO in the Support column. The question, though, is how does one add an unsupported engine to an RPM install? Is it possible? Otherwise I have to either compile from source or upgrade to a version that I hope will have it. On Thu, Feb 19, 2009 at 10:08 PM, Baron Schwartz ba...@xaprb.com wrote: Where on earth did you get an RPM that doesn't have InnoDB support? I find this unlikely. I think it is more likely that you have some configuration error that's causing InnoDB to disable itself on start. How do you know InnoDB isn't supported? And by isn't supported I mean isn't compiled into mysqld. Per your commend that InnoDB wasn't installed with mysqld -- it is not separate. It's built into the /usr/sbin/mysqld binary (or whatever that is on your system). For example, look at this: strings /usr/sbin/mysqld | grep -i innodb If you see a bunch of lines starting with InnoDB: blah blah, you have a binary that includes InnoDB, and it's just disabled for some reason. Baron On Thu, Feb 19, 2009 at 6:58 PM, Jim Lyons jlyons4...@gmail.com wrote: Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the database. The innodb engine is not supported by the database. We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not installed with it. We install from RPMs so I'm not sure how to install the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to install a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Resend: enabling storage engine with RPM install
Can you post you my.cnf please Sent via BlackBerry from T-Mobile -Original Message- From: Jim Lyons jlyons4...@gmail.com Date: Thu, 19 Feb 2009 23:06:33 To: chaim.rie...@gmail.com Cc: MySQLmysql@lists.mysql.com Subject: Re: Resend: enabling storage engine with RPM install How does one do that in my.cnf? It is possible to disable a supported engine by screwing up the my.cnf configuration. For example, I once pointed the InnoDB data file to a directory that still had root as its owner. The Innodb engined appeared as DISABLED in the SHOW ENGINES output, but it was supported. I chown'd the directory to the proper owner and it was fine. (This may be why I mistyped InnoDB in my first post - I had InnoDB on my mind.) In the case of the FEDERATED engine in my database, it's not supported at all. I don't think I can turn support on or off in my.cnf. I would love it, if I could, though. On Thu, Feb 19, 2009 at 10:47 PM, chaim.rie...@gmail.com wrote: Perhaps you disabled it via my.cnf Sent via BlackBerry from T-Mobile -Original Message- From: Jim Lyons jlyons4...@gmail.com Date: Thu, 19 Feb 2009 22:45:01 To: MySQLmysql@lists.mysql.com Subject: Re: Resend: enabling storage engine with RPM install Yes, I had a slip of the mind. The engine that was not supported by the install is the Federated engine. I apologize, I had a blind spot. The SHOW ENGINES command lists FEDERATED but has NO in the Support column. The question, though, is how does one add an unsupported engine to an RPM install? Is it possible? Otherwise I have to either compile from source or upgrade to a version that I hope will have it. On Thu, Feb 19, 2009 at 10:08 PM, Baron Schwartz ba...@xaprb.com wrote: Where on earth did you get an RPM that doesn't have InnoDB support? I find this unlikely. I think it is more likely that you have some configuration error that's causing InnoDB to disable itself on start. How do you know InnoDB isn't supported? And by isn't supported I mean isn't compiled into mysqld. Per your commend that InnoDB wasn't installed with mysqld -- it is not separate. It's built into the /usr/sbin/mysqld binary (or whatever that is on your system). For example, look at this: strings /usr/sbin/mysqld | grep -i innodb If you see a bunch of lines starting with InnoDB: blah blah, you have a binary that includes InnoDB, and it's just disabled for some reason. Baron On Thu, Feb 19, 2009 at 6:58 PM, Jim Lyons jlyons4...@gmail.com wrote: Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the database. The innodb engine is not supported by the database. We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not installed with it. We install from RPMs so I'm not sure how to install the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to install a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Resend: enabling storage engine with RPM install
What configuration parameter in the config file could have an impact on whether a particular storage engine is supported? The binaries are built before the config is even used. I don't mind posting the file, but I don't see the point. The question is pretty simple, can one add a storage engine to an RPM install? The config file follows. Thanks. [client] socket = /db/data/mysql.sock port= 3306 [mysqld] socket = /db/data/mysql.sock datadir = /db/data tmpdir = /db/tmp port= 3306 user= mysql max_allowed_packet = 1024M lower_case_table_names=0 log-bin=/db/binlog/tlsgriffin01-bin sync_binlog = 1 expire_logs_days = 14 log-error=/db/log/tlsgriffin01-err.log log-slow-queries=/db/log/tlsgriffin01-slow.log long_query_time = 1 log_warnings=2 server-id = 101 skip-slave-start sysdate-is-now log_bin_trust_function_creators=1 skip-external-locking key_buffer_size = 128M query_cache_size = 256M table_cache = 4096 thread_concurrency = 14 thread_cache_size = 0 open_files_limit = 10240 max_connections = 1000 skip-bdb read_buffer_size = 64M read_rnd_buffer_size = 64M sort_buffer_size = 64M tmp_table_size = 512M max_heap_table_size = 250M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 20G innodb_data_home_dir = /db/innodb innodb_log_group_home_dir = /db/innodb innodb_data_file_path = ibdata1:10M:autoextend:max:4G innodb_log_files_in_group = 2 innodb_log_file_size = 256M innodb_file_per_table innodb_buffer_pool_size = 400M innodb_additional_mem_pool_size = 80M transaction-isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer = 64M sort_buffer_size = 16M read_buffer = 16M write_buffer = 16M [mysqlhotcopy] interactive-timeout [manager] socket = /db/log/manager.sock pid-file=/db/log/manager.pid password-file = /db/data/.mysqlmanager.passwd monitoring-interval = 60 port = 1998 bind-address = tlsgriffin01 [mysql.server] use-manager On Thu, Feb 19, 2009 at 11:24 PM, chaim.rie...@gmail.com wrote: Can you post you my.cnf please Sent via BlackBerry from T-Mobile -- *From*: Jim Lyons *Date*: Thu, 19 Feb 2009 23:06:33 -0600 *To*: chaim.rie...@gmail.com *Subject*: Re: Resend: enabling storage engine with RPM install How does one do that in my.cnf? It is possible to disable a supported engine by screwing up the my.cnf configuration. For example, I once pointed the InnoDB data file to a directory that still had root as its owner. The Innodb engined appeared as DISABLED in the SHOW ENGINES output, but it was supported. I chown'd the directory to the proper owner and it was fine. (This may be why I mistyped InnoDB in my first post - I had InnoDB on my mind.) In the case of the FEDERATED engine in my database, it's not supported at all. I don't think I can turn support on or off in my.cnf. I would love it, if I could, though. On Thu, Feb 19, 2009 at 10:47 PM, chaim.rie...@gmail.com wrote: Perhaps you disabled it via my.cnf Sent via BlackBerry from T-Mobile -Original Message- From: Jim Lyons jlyons4...@gmail.com Date: Thu, 19 Feb 2009 22:45:01 To: MySQLmysql@lists.mysql.com Subject: Re: Resend: enabling storage engine with RPM install Yes, I had a slip of the mind. The engine that was not supported by the install is the Federated engine. I apologize, I had a blind spot. The SHOW ENGINES command lists FEDERATED but has NO in the Support column. The question, though, is how does one add an unsupported engine to an RPM install? Is it possible? Otherwise I have to either compile from source or upgrade to a version that I hope will have it. On Thu, Feb 19, 2009 at 10:08 PM, Baron Schwartz ba...@xaprb.com wrote: Where on earth did you get an RPM that doesn't have InnoDB support? I find this unlikely. I think it is more likely that you have some configuration error that's causing InnoDB to disable itself on start. How do you know InnoDB isn't supported? And by isn't supported I mean isn't compiled into mysqld. Per your commend that InnoDB wasn't installed with mysqld -- it is not separate. It's built into the /usr/sbin/mysqld binary (or whatever that is on your system). For example, look at this: strings /usr/sbin/mysqld | grep -i innodb If you see a bunch of lines starting with InnoDB: blah blah, you have a binary that includes InnoDB, and it's just disabled for some reason. Baron On Thu, Feb 19, 2009 at 6:58 PM, Jim Lyons jlyons4...@gmail.com wrote: Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the database. The innodb engine is not supported by the database. We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not installed with it. We install from RPMs so I'm not sure how to install the storage engine. If
Re: Resend: enabling storage engine with RPM install
The answer is no Sent via BlackBerry from T-Mobile -Original Message- From: Jim Lyons jlyons4...@gmail.com Date: Thu, 19 Feb 2009 23:50:13 To: chaim.rie...@gmail.com Cc: MySQLmysql@lists.mysql.com Subject: Re: Resend: enabling storage engine with RPM install What configuration parameter in the config file could have an impact on whether a particular storage engine is supported? The binaries are built before the config is even used. I don't mind posting the file, but I don't see the point. The question is pretty simple, can one add a storage engine to an RPM install? The config file follows. Thanks. [client] socket = /db/data/mysql.sock port= 3306 [mysqld] socket = /db/data/mysql.sock datadir = /db/data tmpdir = /db/tmp port= 3306 user= mysql max_allowed_packet = 1024M lower_case_table_names=0 log-bin=/db/binlog/tlsgriffin01-bin sync_binlog = 1 expire_logs_days = 14 log-error=/db/log/tlsgriffin01-err.log log-slow-queries=/db/log/tlsgriffin01-slow.log long_query_time = 1 log_warnings=2 server-id = 101 skip-slave-start sysdate-is-now log_bin_trust_function_creators=1 skip-external-locking key_buffer_size = 128M query_cache_size = 256M table_cache = 4096 thread_concurrency = 14 thread_cache_size = 0 open_files_limit = 10240 max_connections = 1000 skip-bdb read_buffer_size = 64M read_rnd_buffer_size = 64M sort_buffer_size = 64M tmp_table_size = 512M max_heap_table_size = 250M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 20G innodb_data_home_dir = /db/innodb innodb_log_group_home_dir = /db/innodb innodb_data_file_path = ibdata1:10M:autoextend:max:4G innodb_log_files_in_group = 2 innodb_log_file_size = 256M innodb_file_per_table innodb_buffer_pool_size = 400M innodb_additional_mem_pool_size = 80M transaction-isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer = 64M sort_buffer_size = 16M read_buffer = 16M write_buffer = 16M [mysqlhotcopy] interactive-timeout [manager] socket = /db/log/manager.sock pid-file=/db/log/manager.pid password-file = /db/data/.mysqlmanager.passwd monitoring-interval = 60 port = 1998 bind-address = tlsgriffin01 [mysql.server] use-manager On Thu, Feb 19, 2009 at 11:24 PM, chaim.rie...@gmail.com wrote: Can you post you my.cnf please Sent via BlackBerry from T-Mobile -- *From*: Jim Lyons *Date*: Thu, 19 Feb 2009 23:06:33 -0600 *To*: chaim.rie...@gmail.com *Subject*: Re: Resend: enabling storage engine with RPM install How does one do that in my.cnf? It is possible to disable a supported engine by screwing up the my.cnf configuration. For example, I once pointed the InnoDB data file to a directory that still had root as its owner. The Innodb engined appeared as DISABLED in the SHOW ENGINES output, but it was supported. I chown'd the directory to the proper owner and it was fine. (This may be why I mistyped InnoDB in my first post - I had InnoDB on my mind.) In the case of the FEDERATED engine in my database, it's not supported at all. I don't think I can turn support on or off in my.cnf. I would love it, if I could, though. On Thu, Feb 19, 2009 at 10:47 PM, chaim.rie...@gmail.com wrote: Perhaps you disabled it via my.cnf Sent via BlackBerry from T-Mobile -Original Message- From: Jim Lyons jlyons4...@gmail.com Date: Thu, 19 Feb 2009 22:45:01 To: MySQLmysql@lists.mysql.com Subject: Re: Resend: enabling storage engine with RPM install Yes, I had a slip of the mind. The engine that was not supported by the install is the Federated engine. I apologize, I had a blind spot. The SHOW ENGINES command lists FEDERATED but has NO in the Support column. The question, though, is how does one add an unsupported engine to an RPM install? Is it possible? Otherwise I have to either compile from source or upgrade to a version that I hope will have it. On Thu, Feb 19, 2009 at 10:08 PM, Baron Schwartz ba...@xaprb.com wrote: Where on earth did you get an RPM that doesn't have InnoDB support? I find this unlikely. I think it is more likely that you have some configuration error that's causing InnoDB to disable itself on start. How do you know InnoDB isn't supported? And by isn't supported I mean isn't compiled into mysqld. Per your commend that InnoDB wasn't installed with mysqld -- it is not separate. It's built into the /usr/sbin/mysqld binary (or whatever that is on your system). For example, look at this: strings /usr/sbin/mysqld | grep -i innodb If you see a bunch of lines starting with InnoDB: blah blah, you have a binary that includes InnoDB, and it's just disabled for some reason. Baron On Thu, Feb 19, 2009 at 6:58 PM, Jim Lyons jlyons4...@gmail.com wrote: Sorry, but I'm resending because I made a mistake in terminology and want to be clear. The problem isn't that innodb is DISABLED on the
Newbie Question - MySQL Administrator
This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn’t available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff
Re: enabling storage engine with RPM install
Hi Jim, mysql rpm installation comes with default storage engine myism and innodb. Just verify show engines on mysql prompt. On Fri, Feb 20, 2009 at 5:25 AM, Jim Lyons jlyons4...@gmail.com wrote: We have 5.0.22 installed on a test machine and for some reason the innodb storage engine was not enabled. We install from RPMs so I'm not sure how to enable the storage engine. If we compiled ourselves, we'd recompile but that's not an option. Does anyone know how to enable a storage engine once mysql's been installed by an RPM? How does one make the selections in the first place with RPMs? We've always just taken what we got and it was sufficient. Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Krishna Chandra Prajapati MySQL DBA, Email-id: prajapat...@gmail.com
Re: Newbie Question - MySQL Administrator
Jeff, For starters, it looks like you need a value for VARCHAR. Try the same statement but with VARCHAR(255) . On Fri, Feb 20, 2009 at 1:17 AM, Jeff Murdock jeff_murd...@yahoo.com wrote: This is on a Mac OS X (v10.5.6) system in case that matters. 1. - MySQL Administrator Help button says: HELP Help isn't available for MySQL Administrator. Really, no help or did I screw-up the install somehow? 2. - I tried to create my first Table in MySQL Administrator but got this message: ERROR Error executing SQL commands to create table. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NUL' at line 3 (error 1064) Trying to Execute this: CREATE TABLE `test`.`AddressBook` ( `RecNo` INT NOT NULL AUTO_INCREMENT, `FirstName` VARCHAR DEFAULT NULL, `LastName` VARCHAR DEFAULT NULL, `Street1` VARCHAR DEFAULT NULL, `Street2` VARCHAR DEFAULT NULL, `City` VARCHAR DEFAULT NULL, `State` VARCHAR DEFAULT NULL, `Zip` VARCHAR DEFAULT NULL, `HomePhone` VARCHAR DEFAULT NULL, `CellPhone` VARCHAR DEFAULT NULL, PRIMARY KEY (`RecNo`) ) CHARACTER SET utf8 COMMENT = 'Sample'; Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Resend: enabling storage engine with RPM install
Hi JIm, If you are installing mysql on debian operating system you will get all the storage engines which are required. mysql show engines; ++--++ | Engine | Support | Comment| ++--++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| YES | CSV storage engine | | ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables | | FEDERATED | YES | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine| ++--++ 12 rows in set (0.00 sec) Otherwise you can have source installation on any platform to get the all or requisite storage engine. You can have full control with source installation. On Fri, Feb 20, 2009 at 11:20 AM, Jim Lyons jlyons4...@gmail.com wrote: What configuration parameter in the config file could have an impact on whether a particular storage engine is supported? The binaries are built before the config is even used. I don't mind posting the file, but I don't see the point. The question is pretty simple, can one add a storage engine to an RPM install? The config file follows. Thanks. [client] socket = /db/data/mysql.sock port= 3306 [mysqld] socket = /db/data/mysql.sock datadir = /db/data tmpdir = /db/tmp port= 3306 user= mysql max_allowed_packet = 1024M lower_case_table_names=0 log-bin=/db/binlog/tlsgriffin01-bin sync_binlog = 1 expire_logs_days = 14 log-error=/db/log/tlsgriffin01-err.log log-slow-queries=/db/log/tlsgriffin01-slow.log long_query_time = 1 log_warnings=2 server-id = 101 skip-slave-start sysdate-is-now log_bin_trust_function_creators=1 skip-external-locking key_buffer_size = 128M query_cache_size = 256M table_cache = 4096 thread_concurrency = 14 thread_cache_size = 0 open_files_limit = 10240 max_connections = 1000 skip-bdb read_buffer_size = 64M read_rnd_buffer_size = 64M sort_buffer_size = 64M tmp_table_size = 512M max_heap_table_size = 250M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 20G innodb_data_home_dir = /db/innodb innodb_log_group_home_dir = /db/innodb innodb_data_file_path = ibdata1:10M:autoextend:max:4G innodb_log_files_in_group = 2 innodb_log_file_size = 256M innodb_file_per_table innodb_buffer_pool_size = 400M innodb_additional_mem_pool_size = 80M transaction-isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer = 64M sort_buffer_size = 16M read_buffer = 16M write_buffer = 16M [mysqlhotcopy] interactive-timeout [manager] socket = /db/log/manager.sock pid-file=/db/log/manager.pid password-file = /db/data/.mysqlmanager.passwd monitoring-interval = 60 port = 1998 bind-address = tlsgriffin01 [mysql.server] use-manager On Thu, Feb 19, 2009 at 11:24 PM, chaim.rie...@gmail.com wrote: Can you post you my.cnf please Sent via BlackBerry from T-Mobile -- *From*: Jim Lyons *Date*: Thu, 19 Feb 2009 23:06:33 -0600 *To*: chaim.rie...@gmail.com *Subject*: Re: Resend: enabling storage engine with RPM install How does one do that in my.cnf? It is possible to disable a supported engine by screwing up the my.cnf configuration. For example, I once pointed the InnoDB data file to a directory that still had root as its owner. The Innodb engined appeared as DISABLED in the SHOW ENGINES output, but it was supported. I chown'd the directory to the proper owner and it was fine. (This may be why I mistyped InnoDB in my first post - I had InnoDB on my mind.) In the case of the FEDERATED engine in my database, it's not supported at all. I don't think I can turn support on or off in my.cnf. I would love it, if I could, though. On Thu, Feb 19, 2009 at 10:47 PM, chaim.rie...@gmail.com wrote: Perhaps you disabled it via
