Wednesday, December 28, 2005

What could be causing this?

I recently migrated a Nucleus CMS from MySQL 4.x to MySQL 5.0 but upon migration, I get the following message

ERROR 1054 (42S22): Unknown column 'i.inumber' in 'on clause'


when running the query:

SELECT i.inumber as itemid, i.iblog as blog, i.ititle as title, i.ibody as body, m.mname as author, m.mrealname as authorname, i.itime, i.imore as more, m.mnumber as authorid, m.memail as authormail, m.murl as authorurl, c.cname as category, i.icat as catid, i.iclosed as closed, t.tags as tags, t.item_id as item_id FROM nucleus_item as i, nucleus_member as m, nucleus_category as c LEFT join nucleus_plug_tags as t ON t.item_id=i.itemid WHERE i.iauthor=m.mnumber and i.iblog=1 and i.icat=c.catid and i.idraft=0 and (( match (ititle,ibody,imore) against ('aids') > 0 ) or ( t.tags LIKE '%/aids/%') ) and i.itime<="2005-12-28 19:15:54" ORDER BY i.itime DESC;



The interesting thing is that the column exists in the table and no change has been made. Any ideas what would be causing this?

Thanks

Update

Just noticed that the following works

SELECT i.inumber as itemid, i.iblog as blog, i.ititle as title, i.ibody as body, i.itime, i.imore as more, i.icat as catid, i.iclosed as closed, t.tags as tags, t.item_id as item_id FROM nucleus_item as i LEFT join nucleus_plug_tags as t ON t.item_id=i.inumber WHERE i.iblog=1 and i.idraft=0 and (( match (ititle,ibody,imore) against ('aids') > 0 ) or ( t.tags LIKE '%/aids/%') ) and i.itime<="2005-12-28 19:15:54" ORDER BY i.itime DESC;



What could be causing this? When I shorten the query, it works but not when other tables are involved?

Any ideas??

Monday, December 26, 2005

MySQL error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)

A friend of mine was getting the following error and called me up. After telling him how to fix it, I decided to post it here too.
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)
The solution was simple, just start MySQL :)

--
Did you know you can help educate the world through your bookmarks? Join my alpha project featuring social media tools (OpenEncyclopaedia.com) and share your bookmarks while educating the world.

Thursday, December 22, 2005

Open Encyclopaedia - Web 2.0 Style

Sometime ago I learned how hard it can be dealing with DMOZ and the so-called Open Directory Project. My lessons learned were that the open directory was open for the editors, and not for everyone. Since that time I have read some shocking specualtion about what goes on at the open directory project.

With Wikipedia I learned this year that unless the topic you want to write about interests a million people, they won't let you publish it.

So where should the developing news and information go to gain an audience without breaking the budget?

For sometime now, I have been working on a web 2.0 style project that aims to add some innovative features on top of certain concepts taken from web 2.0, Wikipedia, and ODP. The alpha version of this project is available online at OpenEncyclopaedia.com with many features planned and coded but not yet deployed. Contributors will be able to tag their contributions for organizing purposes. In the next update, the project will feature syndication technologies incorporating RSS and mobile Web applications.

After registering for free, users can contribute resources (Blog URL, News story, podcast, video podcast, RSS, XML, Atom, MS Word, MS Excel, PDF etc) to OpenEncyclopaedia.com and earn points. New members get 500 points just for joining.

Please help: I have limited resources for this project and can use your support. Your advice, ideas and suggestions are highly appreciated. I am also looking forward to get sponsorship from companies in exchange for advertising so the project can be supported. (See below for my e-mail address)

Thank you for taking the time to read about the OpenEncyclopaedia.com project.

Frank
ebusinessconsultingus@yahoo.com
--------------------------
Open Encyclopaedia Blog
Open Encyclopaedia Wiki

Tuesday, November 22, 2005

MySQL 5: Transferring database from MySQL 3.23.XX

Earlier, when I wrote about upgrading to MySQL 5, I didn't transfer any databases from MySQL 3.23.56 to MySQL 5 because I was going to use the new MySQL 5 installation as a client and load all old tables to MySQL 5 server on a separate machine. Today I needed to transfer some tables from MySQL 3.23.56 to new MySQL 5 (on the same machine) so here I am writing this post.

First, I copied my database directory

cp -p -r /drive2/oldsdd4/mysql/mydb /var/lib/mysql/


Once the MySQL database directory has been copied, you may need to fix permissions (see below).

Once the database directory has been moved, the following command can be used to dump the contents of the database.

mysqldump -uroot -p"mypass" mydb > mydb.sql


To load data back, use the following

mysqlimport -uroot -p"mypass" mydb mydb.sql



Fixing Permissions
I was getting the following error:

PHP Fatal error: Table 'mytable' is read only! query: INSERT INTO mytable (s, h, t) values('MySecretSession', '192.168.0.1', 1132694778) in /var/www/html/domain.com/includes/database.mysql.inc on line 120



I used the following to fix the error

chmod -R u=rwx,g=rwx,o=rx /var/lib/mysql/mydb
chown -R mysql:mysql /var/lib/mysql/mydb



You can use the following to view the status of the table

SHOW TABLE STATUS LIKE 'sessions' \G



Remember to make a backup :)

Good luck!


Also see: Upgrading MySQL,
upgrading tables from 4.0, upgrading tables from 3.23
Technorati:

To buy: MYSQL 5 Definitve Guide by Michael Kofler

Thursday, November 17, 2005

Open Source Patents Search by Patent Commons - eBay Launches Developers Challenge

Open Source Patents Search
Supported by Computer Associates, Novell, IBM, Sun, Red Hat and others, OSDL (Open Source Development Labs has launched a patents search platform to allow open source developers to search patents that have been donated to the open source community.



From PatentCommons website:
The Patent Commons Project is dedicated to documenting the boundaries of The Commons -- a preserve where developers and users of software can innovate, collaborate, and access patent resources in an environment of enhanced safety, protected by pledges of support made by holders of software patents

Yet Another Blog by Microsoft CTO
Ray Ozzie, CTO of Microsoft, has started yet another blog. He first began blogging in August of 2002. In his opening post, he writes:

As in the past, it's not my intent to be pitching our products here. We've got plenty of mechanisms - old school and new - that work well for that sort of thing. But to the extent that I'm excited about something, or I think there might be a different angle that you might be interested in, I'll chime in.
...
and
...
At times there will be controversies I just can't or won't engage in.


I am sure some people want Bill Gates to start blogging too by now. Also see, Microsoft to enter supercomputer market.

eBay Developers Program Now Free!
eBay recently introduced changes to the pricing structure for the eBay
Developers Program. According to the recent email I received:


We are excited today to introduce changes to the pricing structure for the eBay
Developers Program that will make it even easier to innovate on the eBay platform.
Those include:

- Membership - now free, effective November 14, 2005
- eBay Web Services/ API calls - now free, effective November 14, 2005
- Standard application certification - now free effective, November 14, 2005

And Developer Live Chat technical support is now available to every member of the
program at one low cost - $75/hour or $250 for four (4) hours.

...
Why are offering this new free pricing to our members? First we want to thank you
for your contributions to the eBay marketplace, because we know that without our
developer community eBay wouldn’t be where we are today. Second, we want to remove
barriers to innovation on the platform. Since the first API was released in 2000,
third party developers have led the charge in developing the most cutting edge and
exciting applications and services for the eBay community.


Its all good from eBay but I would have loved it more if they would have done this just a few months ago, when I started building an interface for eBay using MySQL, PHP and AJAX. I could've saved $250.

If you are an experienced MySQL DBA, I encourage you to join the eBay developer program. If you are a webmaster, consider joining the affiliate program first through CJ and then signing up for the eBay developer program. In addition to earning $12-$20 per active user registration, you can get a good percentage of fees collected by eBay. UPS and eBay also recently announced that they are joining forces to allow US based eBay sellers to ship globally.

eBay Developers Challenge
Greg Isaacs, Director of eBay Developers Program announced the beginning of a developers challenge by eBay. He writes:
Now that most barriers to development have been removed, please consider
participating in the eBay Developers Challenge at
http://developer.ebay.com/challenge2006 to win valuable prizes, including a $5,000 cash prize. The contest starts today!



Technorati Tags:

Google Base Live! - Apache Server Status

Earlier, I posted about GoogleSQL and Google Base being a threat to MySQL. On Tuesday night, Google announced on its blog that Google Base is now live and ready for users content. Some are calling it a threat to small businesses, while others think of it as an opportunity. More on Google Base going live from Google Blog. Also see: Google Base on SMA

If you use Apache, you can turn on server-status and extended status information to get some useful information. Also see: Optimize Apache and using clickstream module

Google Base | MySQL | GoogleSQL

Wednesday, November 16, 2005

MySQL 5 Optimization and Tuning Guide

Earlier, I mentioned some MySQL optimization resources. Here is the guide on optimizing sysctl.conf. Today, I will be optimizing MySQL 5.0.15 on a Red Hat Enterprise Linux ES release 3 with dual Xeon 2GHz processors and 2GB RAM.


Note: Please use this work-in-progress guide for reference only and at your own risk. I am creating this guide on a development server and will not be responsible if anything bad happens. If you find something is incorrect or can be made better please let me know by adding a comment to my blog (mysqldatabaseadministration)



my.cnf: If you have installed or upgraded to MySQL 5, chances are that you may not find the /etc/my.conf file. Don't worry as there are several sample configuration files that come with MySQL. You can invoke
locate my-large.cnf
to find the sample configuration file for MySQL 5 on your server. By default, on Red Hat Enterprise, it can be located at the following location.
/usr/share/doc/MySQL-server-standard-5.0.15/my-large.cnf


Setting MySQL 5 global options:
To set global MySQL5 options, we will need to copy my-large.cnf to /etc/my.cnf

Setting MySQL 5 server-specific options:
By default, MySQL5 data directory is /var/lib/mysql. To set server-specific options for MySQL5, we can move the sample MySQL configuration file to our data directory.

For our MySQL5 optimization and tuning tutorial, we will be setting global MySQL5 configurations by copying the my.cnf file to /etc/ directory. In case, you are waiting for me to give you the command:
cp -p /usr/share/doc/MySQL-server-standard-5.0.15/my-large.cnf /etc/my.cnf
Getting information about current values:
We can obtain information about the currently set values by running the following command. Abbreviated output is displayed for reference.

# mysqladmin variables -uroot -p"PASS"

Output from a development server

+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_routine_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | server.net.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 10 |
| system_time_zone | CST |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.15-standard |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+------------------------------------------+


Other commands to get MySQL status information, when connected to a MySQL server
SHOW VARIABLES;
SHOW STATUS;
On shell prompt
mysqladmin variables
mysqladmin extended-status


Decrease the value of connect_timeout: By default, the wait_timeout value is 28800. Unless you want MySQL to keep waiting for 28800 seconds (480 minutes or 8 hours), please decrease its value according to your application needs. On the test server, I have the wait_timeout set to 60.

Open the file /etc/my.cnf and under [mysqld], place a line similar to following

wait_timeout=60
Increase connect_timeout from 5 to 10
connect_timeout=10
Decrease interactive_timeout from 28800 to 100
interactive_timeout=120
Increase join_buffer_size from 131072 to 1M
join_buffer_size=1M
Increase query_cache_size from 0 to 128M
query_cache_size=128M
Increase query cache limit from 1048576 to 2M
query_cache_limit=2M
Increase max allowed packet size from 1M to 16M
max_allowed_packet=16M
Increase table cache cache from 256 to 1024
table_cache=1024
Increase sort buffer size from 1M
sort_buffer_size=2M
Increase read buffer size from 1M
read_buffer_size=2M
Increase read_rnd_buffer_size to 4M
read_rnd_buffer_size=4M
Other settings

sort_buffer_size=8M
key_buffer = 256M
key_buffer_size=64M



MySQL Query Cache
To maximize use of MySQL query cache, similar queries used by an application should be identical, i.e. their casing must mache. From MySQL:

Queries are compared before parsing, so the following two queries are regarded as different by the query cache:

SELECT * FROM tbl_name
Select * from tbl_name

Queries must be exactly the same (byte for byte) to be seen as identical. In addition, query strings that are identical may be treated as different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.

When is a query not be cached?
From MySQL documentation (Full list of when a MySQL query is not cached by MySQL server):

A query cannot be cached if it contains any of the following functions:

BENCHMARK() CONNECTION_ID() CURDATE()
CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP()
CURTIME() DATABASE() ENCRYPT() with one parameter
FOUND_ROWS() GET_LOCK() LAST_INSERT_ID()
LOAD_FILE() MASTER_POS_WAIT() NOW()
RAND() RELEASE_LOCK() SYSDATE()
UNIX_TIMESTAMP() with no parameters USER()

How can you "tell" MySQL server to cache a query?
If query_cache_type system variable on your MySQL server is set to ON or DEMAND, you can specify SQL_CACHE in your query to tell MySQL server explicity to use query cache for this query. An example will look something like

SELECT SQL_CACHE ... ;

In contrast, tell MySQL 5 server not to cache your query by using SQL_NO_CACHE in your SQL statements.

SELECT SQL_NO_CACHE ... ;
Please note that a user on dev.mysql.com pointed out the following,

Using SQL_NO_CACHE does not bypass the cache mechanism, so it is not a
reliable way to time query speed. All it does is tell the server not to
cache the results of the specific query you're sending.

If you've already executed the query without the option, some other time, that cached result will be fetched.
Another user, Uwe, pointed out the following

To completely disable reading (AND writing) from/to cache use a comment at the beginning of the query. MySQL's ignoring the query cache if the query doesn't start with "select" (at least up to 4.1).

/* disable qcache */ SELECT * FROM tbl WHERE 1

This is very useful to test query performance in a running environment as you can't be sure that the query's not already in the cache.


How to optimize disk seek time on your MySQL 5 server?
To optimize disk seek time on your server, you can try distributing the data onto more than one hard disk drive.

Using BENCHMARK() function (Output shows 10000000 simple additions can be performed in 0.93 seconds)

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 558 to server version: 5.0.15-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.07 sec)

mysql> SELECT BENCHMARK(10000000,1+1);
+-------------------------+
| BENCHMARK(10000000,1+1) |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.93 sec)

Estimating the performance by counting disk seeks
According to MySQL, using B-tree indexes, you need this many seeks to find a row:
log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1
Note that in MySQL, an index block is normally 1K (1024 bytes) . The data pointer is usually 4 bytes. According to MySQL,
For a 500,000-row table with an index length of 3 bytes (medium integer), the formula indicates log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 seeks. This index would require storage of about 500,000 * 7 * 3/2 = 5.2MB (assuming a typical index buffer fill ratio of 2/3), so you probably have much of the index in memory and so need only one or two calls to read data to find the row.
For more see MySQL optimization.

Other Notes
Be aware that in case your server's CPU needs more data than can fit in the CPU cache, main memory bandwidth can become a bottleneck.

You can use crash-me to "find functions, types, and limits that you can use with a selection of database servers. crash-me does not check for every possible feature, but it is still reasonably comprehensive, performing about 450 tests." - MySQL

more complex MySQL permissions setup = more overhead


Resources:
Index Merge Optimization
Custom Benchmarks, Super-Smack (can bring a system to its knees)
MySQL and 50GB of transaction tables and 200GB of other customer data
Storage Engines and Table Types
MySQL Optimization
MySQL Query Cache
MySQL in batch mode, Foreign Keys, Searching on Two Keys, MySQL Program Options,
MySQL Instance Manager (Connecting to MySQL Instance Manager and Creating User Accounts) - serves for monitoring and management of MySQL Database Server instances
The typical startup/shutdown cycle for a MySQL server with the MySQL Instance Manager enabled is as follows:

* The MySQL Instance Manager is started with /etc/init.d/mysql script.
* The MySQL Instance Manager starts all instances and monitors them.
* If a server instance fails the MySQL Instance Manager restarts it.
* If the MySQL Instance Manager is shut down (for instance with the /etc/init.d/mysql stop command), all instances are shut down by the MySQL Instance Manager.


MySQL Installation Layouts


Side note: Google recently relaunched Urchin web analytics software as Google Analytics for advertisers and publishers. Details on Search Marketing Ambassador and here. Also, if you are a webmaster in addition to being a MySQL DBA, you can get $50 in credit from Yahoo! Search Marketing for sponsored search. Also see information on Google and Yahoo! sitemaps

Tags:

Thursday, November 10, 2005

TurboDbAdmin - AJAX based PHPMyAdmin on Steroids?

Call it PHPMyAdmin on steroids, or just an online database administration tool that happens to be AJAX based. TurboDbAdmin is one of the coolest AJAX based FREE application-of-the-year for MySQL database administrators. The good news, for those of you who are still using MySQL 4 or MySQL 5, is that minimum requirements for TurboDbAdmin by Turbo AJAX is MySQL 3 or higher with PHP 4 or higher installed.

Some highlights:
- Data is efficiently retrieved from the server so you don't have to wait. Columns are sortable.
- Real-time editing of table data: edits are automatically saved to your database server.
- Add, delete, backup, and rename databases. MySQL servers supported

For those of you still unfamiliar with , here is a quote from Wikipedia

Asynchronous JavaScript and XML, or Ajax, is a web development technique for creating interactive web applications using a combination of:

* XHTML (or HTML) and CSS for presenting information
* The Document Object Model manipulated through JavaScript to dynamically display and interact with the information presented
* The XMLHttpRequest object to exchange data asynchronously with the web server. (XML is commonly used, although any format will work, including preformatted HTML, plain text, JSON and even EBML)

Like DHTML, LAMP, or SPA, Ajax is not a technology in itself, but a term that refers to the use of a group of technologies together. In fact, derivative/composite technologies based substantially upon Ajax, such as AFLAX, are already appearing.


Some features such as executing MySQL queries on the server are still in development according to the website. A Live demo of TurboDbAdmin for MySQL is available at http://www.turboajax.com/turbodbadmin_demo

Other AJAX articles:
Faeriebell: AJAX Buzz - AJAX and PHP

Technorati Tags:

Gain a robust analysis platform by utilizing MySQL with the Pentaho OLAP Server

Pentaho Corporation, a company providing a full spectrum of open source Business Intelligence (BI) tools from reporting and analysis to dashboards to data mining, today announced in a press release that it's customers
"now have the opportunity to gain a robust analysis platform at a remarkably low cost by utilizing open source relational databases such as MySQL together with the Pentaho On-Line Analytical Processing (OLAP) Server."

Mondrian project will become a permanent feature of the Pentaho Business Intelligence (BI) project. The Business Intelligence project provides comprehensive reporting, analysis, dashboards, workflow, and data mining capabilities.

Mondrian Server was initially released in 2002. According to the press release:
Mondrian OLAP Server supports the aggregation, calculation and categorization of data from standard relational databases, including Oracle, , Microsoft Server, IBM DB2, and others. Mondrian uses the world's most popular Java integrated development environment - Eclipse - as the graphical editor for defining and editing OLAP models. It also implements the industry standard OLAP query language - MDX - and works with both the for Analysis and specifications.


More information on Pentaho is available on http://www.pentaho.org/

MySQL, Apache, PHP - Tuning and Optimizing web server

I have been receiving many requests on writing a detailed server optimization guide. Unfortunately, at the moment I am quite busy with work. However, while I keep you guys waiting, keep watching this post as I will post some of the available MySQL and Apache optimization resources.

If you know of a good tutorial on tuning MySQL and Apache, please add it by adding your comments.

Thank you
Frank Mash

EV1 Forums: Four Primary Areas (MySQL, Apache, sysctl.conf, PHP) for Optimizing your server
Jeremy Zawodny: MySQL Tuning Advice
MySQL: MySQL Tuning and Optimization/Optimisation Tips
Linux Magazine: MySQL Performance Tuning (Registration required)

Wednesday, November 09, 2005

First Google Base and Now Google Automat?

Earlier, I reported about Google Base. Now comes an even bigger news. Search Marketing Ambassador is reporting that Peter M. Zollman, Founding Principal of Classified Intelligence said:
"It's crystal clear is planning for an all-out move into classified advertising. These patent filings and the disclosure of Base a few weeks ago show the company is actively preparing to offer free listings for cars, homes, jobs and 'stuff,' even for merchants, among its services."

I read a report earlier about an investment firm analyst predicting that Google Automat has huge potential. Watch out eBay and every auctions and classifieds site. Here comes Google.

Sunday, November 06, 2005

MySQL - Creating multiple tables in multiple databases

I was recently asked to help create multiple MySQL tables on a MySQL 4 server using a MySQL 5 client.

This post will show you how you can create multiple tables in multiple databases using our database of choice, MySQL.

Setup a shell script to execute the query. Assume that the first argument passed is the database, indicated by $db.


# CODE FOR synch_db.sh
db=$1;
mysql -uuser -ppass --host=192.168.0.1 -e 'CREATE TABLE IF NOT EXISTS '$db'.table_name ( id int(11) NOT NULL auto_increment, alias varchar(80) default NULL, tag varchar(80) default NULL, mapping int(1) default 0, PRIMARY KEY (id)) TYPE=MyISAM;'

mysql -uuser -ppass --host=192.168.0.1 -e 'CREATE TABLE IF NOT EXISTS '$db'.table_name2 ( id int(11) NOT NULL auto_increment, alias varchar(80) default NULL, tag varchar(80) default NULL, mapping int(1) default 0, PRIMARY KEY (id)) TYPE=MyISAM;'




Create a CSV file containing database names to which you want changes to be applied. Following is how our CSV looks like. In our case the CSV is semi-colon separated and the 8th field containing the database name.
domain.com;field1;field2;....field7;database_name

And finally we can query the CSV file, extract the database name and run our shell script to create tables across all databases.


cat merged.csv | cut -d ";" -f 8 | while read i ; do sh synch_db.sh $i; done;



Please, add your questions and comments.

Monday, October 31, 2005

GoogleSQL? Is Google base a threat to MySQL and small websites? - Should MySQL jump into Google's domain?

I first heard about Google Base on SEW (a post made by shimsand).

Google Base is Google’s latest project in an effort to organize the world’s information. It isn’t in Beta or Alpha yet. They are still developing it. I wasn’t able to get much information from my sources at Google, but I’ll tell you what they told me. Basically, Google Base will present feed results on the results page. The feeds will probably be anywhere from 1-3 results, and either presented above or below the horizontal sponsored ads. I asked if the feeds will include podcast feeds, or just RSS feeds. My source wasn’t sure. I asked if they be sponsored ads for feeds, or just what Google considers relevant? Answer not known. We’ll just have to wait and see.

At first I thought Google Base may be a new kind of personalized home page. I was wrong. Today I am finding information on Google Base just about everywhere.

Aiming to list everything about *you*, Google Base is Google's direct attack on eBay, Paypal (an eBay company), Craigslist, and thousands of other small websites.

Regarding when exactly Google base will be officially announced, Search Engine Roundtable (SER) had the following to say:

This new tool will be introduced during the 'Google Zeitgeist'05 Partner Forum' to be held today at Google HQ in California. We expect that 'Google Purchases' [hey, I spotted that domain two weeek's ago] --the new micropayments service among users-- will be also introduced as a complement to 'Google Base'.


On its official Google Blog, the following was posted by Tom Oliveri, Product Marketing Manager, Google Inc.
You may have seen stories today reporting on a new product that we're testing, and speculating about our plans. Here's what's really going on. We are testing a new way for content owners to submit their content to Google, which we hope will complement existing methods such as our web crawl and Google Sitemaps. We think it's an exciting product, and we'll let you know when there's more news.


Google is, IMO, crossing the line here by becoming a content publisher itself. Mom and Pop websites online have been hit very hard by Google's unjustified penalties. From what I see, and as confirmed by Google's SEC filings, Google thinks of pretty much everyone as a competitor, even its own partners. I know of several innocent webmasters who, after having years of success with Google, were penalized to the point that they simply closed down their operations. First Google went after directory sites, then travel sites, then shopping sites, then affiliate sites, and now every auction service provider is going to be considered a scraper or a content provider that "adds little or no original value." It's also interesting to note the timing with which Google penalizes innocent websites. For e.g., right at the beginning of the travel season, travel sites are dropped. Right before the shopping season, shopping sites are dropped (two years in a row). It will not end here.



Screenshots Courtesy: Dirson/Flickr


But Google Base is not the only project Google is up to. Andrew Hitchcock posted on his blog about Jeffrey Dean's talk on BigTable. In his post, Andrew writes:

BigTable has been in development since early 2004 and has been in active use for about eight months (about February 2005). There are currently around 100 cells for services such as Print, Search History, Maps, and Orkut. Following Google's philosophy, BigTable was an in-house development designed to run on commodity hardware. BigTable allows Google to have a very small incremental cost for new services and expanded computing power (they don't have to buy a license for every machine, for example). BigTable is built atop their other services, specifically GFS, Scheduler, Lock Service, and MapReduce.

Each table is a multi-dimensional sparse map. The table consists of rows and columns, and each cell has a time version. There can be multiple copies of each cell with different times, so they can keep track of changes over time. In his examples, the rows were URLs and the columns had names such as "contents:" (which would store the file data) or "language:" (which would contain a string such as "EN").

In order to make each [tablet] manage the huge tables, the tables are split at row boundaries and saved as tablets. Tablets are each around 100-200 MB and each machine stores about 100 of them (they are stored in GFS). This setup allows fine grain load balancing (if one tablet is receiving lots of queries, it can shed other tablets or move the busy tablet to another machine) and fast rebuilding (when a machine goes down, other machines take one tablet from the downed machine, so 100 machines get new tablet, but the load on each machine to pick up the new tablet is fairly small).

Tablets are stored on systems as immutable SSTables and a tail of logs (one log per machine). When system memory is filled, it compacts some tablets. He went kind of fast through this, so I didn't have time to write everything down, but here is the overview: There are minor and major compactions. Minor compactions involve only a few tablets, while major ones involve the whole system. Major compactions can reclaim hard disk space. The location of the tablets are actually stored in special BigTable cells. The lookup is a three-level system. The clients get a pointer to the META0 tablet (there is only one). This tablet is heavily used, and so one machine usually ends up shedding all its other tablets to support the load. The META0 tablet keeps track of all the META1 tablets. These tables contain the location of the actual tablet being looked up. There is no big bottleneck in the system, because they make heavy use of pre-fetching and caching.

Back to columns. Columns are in the form of "family:optional_qualifier". In his example, the row "www.cnn.com" might have the columns "contents:" with the HTML of the page, "anchor:cnn.com/news" with the anchor text of that link ("CNN Homepage"), and "anchor:stanford.edu/" with that anchor text ("CNN"). Columns have type information. Columns families can have attributes/rules that apply to their cells, such as "keep n time entries" or "keep entries less than n days old". When tablets are rebuilt, these rules are applied to get rid of any expired entries. Because of the design of the system, columns are easy to create (and are created implicitly), while column families are heavy to create (since you specify things like type and attributes). In order to optimize access, column families can be split into locality groups. Locality groups cause the columns to be split into different SSTables (or tablets?). This increases performance because small, frequently accessed columns can be stored in a different spot than the large, infrequent columns.

All the tablets on one machine share a log; otherwise, one million tablets in a cluster would result in way too many files opened for writing (there seems to be a discrepancy here, he said 100 tablets per machine and 1000 machines, but that doesn't equal one million tablets). New log chunks are created every so often (like 64 MB, which would correspond with the size of GFS chunks). When a machine goes down, the master redistributes its log chunks to other machines to process (and these machines store the processed results locally). The machines that pick up the tablets then query the master for the location of the processed results (to update their recently acquired tablet) and then go directly to the machine for their data.

There is a lot of redundant data in their system (especially through time), so they make heavy use of compression. He went kind of fast and I only followed part of it, so I'm just going to give an overview. Their compression looks for similar values along the rows, columns, and times. They use variations of BMDiff and Zippy. BMDiff gives them high write speeds (~100MB/s) and even faster read speeds (~1000MB/s). Zippy is similar to LZW. It doesn't compresses as highly as LZW or gzip, but it is much faster. He gave an example of a web crawl they compressed with the system. The crawl contained 2.1B pages and the rows were named in the following form: "com.cnn.www/index.html:http". The size of the uncompressed web pages was 45.1 TB and the compressed size was 4.2 TB, yielding a compressed size of only 9.2%. The links data compressed to 13.9% and the anchors data compressed to 12.7% the original size.

They have their eye on the future with some features under consideration. 1. Expressive data manipulation, including having scripts sent to clients to modify data. 2. Multi-row transaction support. 3. General performance for larger cells. 4. BigTable as a service. It sounds like each service (such as Maps or Search History) have their own cluster running BigTable. They are considering running a Google-wide BigTable system, but that would require fairly splitting resources and compute time, etc.


From the screenshots provided by Dirson,
Examples of items you can find in Google Base:

- Description of your party planning service
- Articles on current events from your website
- Listing of your used car for sale
- Database of protein structures


Interestingly, when I tried to login using my Google account, I was continously kicked back to the login page to confirm my password. Needless to say, I stopped trying.

Philipp Lenssen posted on his blog:

This sounds big and immensely interesting. Is Google putting a layer in-between dynamic web sites and their databases, replacing MySQL/PostgreSQL/MS SQL, and creating a new GoogleSQL... possibly, with their ads in it? I can’t wait to try it.


GoogleSQL? Whoa! Only time will tell.

Now that Google is coming into MySQL's domain, isn't it time for MySQL to jump in to Google's domain? If I was consulting MySQL AB (yes I provide consulting services), I would definitely urge MySQL AB to start a search service. If MySQL won't, Oracle may. Knowing the loyalty and passion of MySQL community, there is no doubt in my mind that should MySQL jump in to the search industry, their results will be far better and relevant than Google.

First Open Source Enterprise CMS Integrates with MySQL

Standards-Based System Enables Developers to Easily Build Custom Applications; Lowers Cost and Complexity of Deploying True Enterprise Content Management

MySQL developers can "gain access to the latest technology and develop against a certified, supported version of the product"


From: Open Source Business Conference 2005

BOSTON--October. 31, 2005--

Enterprises and developers can now download the first enterprise-class content management system that utilizes the power of open source. Traditional Enterprise Content Management (ECM) systems were designed a decade ago, leaving them with cumbersome interfaces and legacy technology. Alfresco is built on modern, service oriented technologies and standards from this decade, such as Spring, Hibernate, Lucene and Web Services, JSR-168, JSR-170 and MyFaces. These help to dramatically bring down the cost and complexity of both implementation and use of these systems. The company is lead by a team of content management veterans including the co-founder of Documentum.

The Alfresco ECM solution is designed for small to mid-sized enterprises and departments in Global 1000 organizations that want a system that is easy for end-users to use while also being simple-to-install and flexible for developers to create new and customized content applications.

Alfresco has benefited from support from MySQL and JBoss and integrates to MySQL, JBoss Portal, JBoss Application Server, JBoss Cache, JBoss Application Server and Hibernate.

"Informa has over 7000 employees in 159 offices in 40 countries. We were looking for a flexible, cost-effective system that could meet the diverse requirements of our business," said Bob Hecht, VP - Content Strategy for Informa. "The success of the current project will hinge on ease-of-use and configurability. We determined that the Alfresco approach is an excellent fit for our user requirements and it's cutting edge technical architecture will provide us the flexibility and power to out-perform the traditional ECM market."

Alfresco ECM: Already in Use Worldwide

Alfresco has already logged tens of thousands of downloads since releasing a preview of the repository in June. The company has benefited from support from JBoss and MySQL. Alfresco integrates to the JBoss Portal, JBoss Application Server, JBoss Cache, JBoss Application Server, Hibernate and MySQL. Customers across all sectors in both the US and Europe have started to benefit from Alfresco. A number of hosting partners are being announced highlighting the strength of the product in this environment. The Alfresco software is true zero footprint ECM whereby no ActiveX or Java Applets are required on the client. It is easy to setup and users can be invited to groups without costly administration. As the system is not proprietary and uses standards such as JSR-170 and Web Services, a client can use the same system both internally and externally.

"Thanks to the increasing corporate regulation as well as the exponential growth of electronic documents, content management has emerged as a 'must have' application," said Alfresco CEO John Powell. "The problem is, those companies that have installed complex content management systems in the past, today find that they are underutilized thanks to complex interfaces and large feature-sets that do not meet the needs of today's enterprise. We built Alfresco to fix those problems."

Easy-to-use Interface Ensures User Adoption

Alfresco uses the Microsoft CIFS interface, which enables users to treat content just as they would a Microsoft Windows shared file drive. Unlike other interfaces, CIFS enables users to take work offline and synchronize it when reconnecting. It also allows applications that will only work with mounted drives or non-networked file systems to work natively with the Alfresco repository. These can include a user's native editing tool or a corporate application.

"In most companies today, even those with long-standing content management systems, you find a shared drive is where people place their information, eschewing central management. Since that's a process people are comfortable using, we made Alfresco work within that structure," said Alfresco CTO John Newton. "This ease-of-use extends beyond the product functionality making the product easy to install and rollout throughout a company with minimal training."

Additional product benefits include:

  • Developer Productivity and Portability - Developers can easily customize the system through wizards or the language of their choice. This can include customization through Aspect-Oriented Programming, Web Services, simple Templating, XML, Java and JCR-170. These customizations are portable across multiple operating systems and clients. They can also be used between a hosted and an internal environment.-- Scalable and Fault Tolerant Architecture - The server offers high levels of scalability through a fully distributed, clustered cache. High availability is offered through replication and failover management.
  • Compliance/Secure Document Lifecycle Management - Alfresco's Aspect-Oriented approach allows document lifecycle rules to be configured quickly and easily. Here, for example, "author, review, approve and distribute" workflows can be simply set-up with rules to both change the security and format of the document with a full audit trail.
Multiple Levels of Network Support

Also today, Alfresco announced the availability of both a free open source "Community Network" and a series of fee-based support networks. Alfresco has designed several levels of network support to meet the specific needs of the organization and enterprise using the product.

These include:

  • Community Network: Free open source downloadable Enterprise Content Management system supported by a large and active community of open source developers;
  • Developer Network: Enables developers to gain access to the latest technology and develop against a certified, supported version of the product;
  • Professional Network: Designed for small enterprises or departments in Global 1000 organizations and layers in additional security functionality and support;
  • Enterprise Network: Designed for the Global 1000 offering support and functionality that includes advanced security features such as single sign-on and mission critical features such as clustering, failover and advanced caching. This is priced to allow for unlimited seats.
Pricing and Availability

The Community Network is free and is available for download today at www.alfresco.org.

The Developer Network is $4.99 monthly per user and the Professional Network is $9.99 monthly per user. The Enterprise Network offers unlimited users for a monthly fee of $625. For a limited period all customers taking out a support subscription to the Alfresco Professional Network will receive the Alfresco Enterprise Network product capabilities free of charge. The Professional and Enterprise Network's are available for download today at www.alfrescosoftware.com.

About Alfresco

Alfresco Software Inc. is the leading open source alternative for enterprise content management. It is the first company to bring the power of open source to the enterprise content management market, enabling unprecedented scale and a much lower total cost of ownership than proprietary systems. Founded by a team of content management veterans that includes the co-founder of Documentum, John Newton, and former COO of Business Objects, John Powell, Alfresco is based in London. For more information, visit www.alfrescosoftware.com.


Alfresco Software Inc.
Ian Howells, +44 1628 860 639

Sources: Alfrescosoftware.com , BUSINESS WIRE
Editor: Frank Mash
Tags:

Sunday, October 30, 2005

Planet MySQL - Are you in?

For any MySQL DBA, PlanetMySQL is an indispensable community resource pulling latest information about MySQL from blogs targeted towards MySQL users.
If your blog is about MySQL, consider submitting it to PlanetMySQL.org. An aggregated RSS feed is available at PlanetMySQL. If your blog is accepted to this MySQL community, you can be sure that your voice is being heard in the MySQL community.

Thanks to everyone at MySQL and PlanetMySQL for providing such an excellent resource and for listing MySQL Database Administration blog as a contributor blog.

MySQL5 - PHP4 - Troubleshooting - Call to undefined function: mysql_connect()

Earlier, I showed you how to install MySQL 5 (MySQL5) on a Red Hat Enterprise Linux server. The MySQl 5 installation went just fine for us, however today when I tried to run a test script incorporating a MySQL 5 client to MySQL 4 server connection, I received the following errors

PHP Fatal error: Call to undefined function: mysql_connect()



I was aware that mysql_connect has been discontinued in C API, but why am I getting this error? I found that mysql_connect has been deprecated in favor of mysql_real_connect(). While that doesn't solve our issue at hand, I do want to take a moment to point out that Ingo Tomahogh has a good tip posted on http://dev.mysql.com about migrating your code from using mysql_connect() to mysql_real_connect function. To put it in Ingo's words:

If you need to compile old programs still using this function, you might want to add the following macro definition to your programs (possibly via the compiler's command line) so you needn't change all calls to mysql_connect() :


#define mysql_connect(m,h,u,p) mysql_real_connect((m),(h),(u),(p),NULL,0,NULL,0)


You should also make sure the MYSQL pointer is never NULL in these calls, though.

Thanks to David Phillips for pointing out the following

In response to the mysql_connect() macro, also make sure to call mysql_init() before using it. You didn't need to (or at least it worked) with older versions of the library. You will immediately crash if you don't.


Another thing worth noting here is that the errors and return values for mysql_real_connect() are the same as mysql_connect(). More information on mysql_connect function in available from dev.mysql.com.

Now, back to troubleshooting our "Call to undefined function: mysql_connect()". Some other MySQL error messages:

PHP Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client



The issue PHP is complaining about here is threefold.

1. missing mysql.so extension definition in /etc/php.ini, and/or
2. missing mysql.so file on system
3. missing lib*mysql.so* files on your system

First, get a copy of mysql.so and put it in /usr/lib/php4 or equivalent.

cp -p /old/usr/lib/php4/mysql.so /usr/lib/php4/



Now, put the following line in /etc/php.ini

extension=mysql.so


After restarting httpd (Apache), I got

PHP Warning: Unknown(): Unable to load dynamic library '/usr/lib/php4/mysql.so' - libmysqlclient.so.10: cannot open shared object file: No such file or directory in Unknown on line 0



To fix this, get a copy of libmysqlclient.so.10. Assuming you have got a copy on your hard drive

cp -p /old/usr/lib/libmysqlclient.so.10 /usr/lib/



Now, restart Apache, and voila! you should be able to connect. To verify, load phpinfo() and you should see a section similar to the following:

mysql

MySQL Supportenabled
Active Persistent Links 0
Active Links 0
Client API version HIDDEN
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib/mysql -lmysqlclient



Related MySQL troubleshooting tips from fellow webmasters online.
Resolving a Fatal error: Call to undefined function mysql_connect() in RedHat
PHP works, but not with mysql
could not connect mysql5 from php5
Call to undefined function: mysql_connect()
on Technorati
on Del.icio.us

Friday, October 28, 2005

Upgrade to MySQL5 1 - Upgrading MySQL3 to MySQL4 - Red Hat Enterprise Linux 3 RHEL

Here comes the time to upgrade from MySQL 3.23 to the latest MySQL 5.0. In the first part, I will guide you through the MySQL 3 to MySQL 4 upgrade.

MySQL recommends we upgrade to next series available.

As a general rule, we recommend that when upgrading from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 rather than to 4.1 or 5.0.
  • Read upgrading section for the release series to which you are upgrading, change notes to learn about new features.
  • Backup your databases.
  • Making changes to the grant tables. Read Upgrading the Grant Tables.
  • Read MySQL replication upgrade info
  • MySQL-Max distribution: According to MySQL, you will need to " upgrade later to a non-Max version of MySQL, mysqld_safe still attempts to run the old mysqld-max server. If you perform such an upgrade, you should manually remove the old mysqld-max server to ensure that mysqld_safe runs the new mysqld server."
Moving data files and Table Character Sets
For this MySQL says that you can move MySQL format and data files "between different versions on the same architecture as long as you stay within versions for the same release series of MySQL". If character set of a table needs to be changed, then run
charsetmyisamchk -r -q --set-character-set=
on all MyISAM tables. The character sets need to be changed because other indexes that you created on MySQL may not work because the sort order can be changed if the character set is changed.

Renaming old MySQL

At this point you may want to rename MySQL directory for backup. mysqldump can be used to do this job. Then mysql or mysqlimport can be invoked to read the database dumps created by MySQL.

MySQL says

If, after an upgrade, you experience problems with recompiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs.
....
If problems occur, such as that the new mysqld server does not want to start or that you cannot connect without a password, verify that you do not have some old
my.cnf
file from your previous installation. You can check this with the
--print-defaults
option (for example, mysqld --print-defaults). If this displays anything other than the program name, you have an active
my.cnf
file that affects server or client operation.

It is a good idea to rebuild and reinstall the Perl

DBD::mysql
module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP
mysql and mysqli extensions or the Python MySQLdb module.

Convert old ISAM tables to MyISAM
format

You have the following options:
  1. Use mysql_convert_table_formatscript as described here. Then use the following command only if all tables in the database are ISAM or MyISAM tables
    shell>mysql_convert_table_format database db_name
  2. To convert tables individually, use
    mysql> ALTER TABLE tbl_name TYPE=MyISAM;
To know the table type for any MySQL table, use
mysql> SHOW TABLE STATUS LIKE 'tbl_name';
Make sure you are not using shared MySQL libraries (Perl DBD::mysql module, MySQLdb). Otherwise according to MySQL, "you should recompile them, because the data structures used in libmysqlclient.so."

MySQL UPGRADE GUIDE 1

MySQL Server Changes with MySQL4 onwards
  1. server by default no longer loads user-defined functions. “User-Defined Function Security Precautions”.
  2. many new privileges in the
    mysql.user
    table : “Privileges Provided by MySQL”.
  3. safe_mysqld has been renamed to mysqld_safe
  4. "InnoDB support is included by default" (binary)
  5. mysqld option --temp-pool enabled by default(better performance with Linux).
  6. Startup options --skip-locking and --enable-locking have been renamed to --skip-external-locking and--external-locking
  7. External system locking of MyISAM/ISAM files is turned off by default. To turn it on, use
    -external-locking
Renamed startup variables and options:

Name in 3.23 Name in 4.0 (and above)
myisam_bulk_insert_tree_size
bulk_insert_buffer_size
query_cache_startup_type
query_cache_type
record_buffer
read_buffer_size
record_rnd_buffer
read_rnd_buffer_size
sort_buffer
sort_buffer_size
warnings
log-warnings
--err-log
--log-error
(for mysqld_safe)


Why should you use truncate instead of delete

You should use TRUNCATE TABLE when you want to delete all rows from a table and you do not need to obtain a count of the number of rows that were deleted. (DELETE FROM tbl_name returns a row count in 4.0 and does not reset the AUTO_INCREMENT counter, and TRUNCATE TABLE is faster.)
For Full Text indexes

To use MATCH ... AGAINST (... IN BOOLEAN MODE) full-text searches, you must rebuild existing table indexes using REPAIR TABLE tbl_name USE_FRM. If you attempt a boolean full-text search without rebuilding the indexes in this manner, the search returns incorrect results. See “Fine-Tuning MySQL Full-Text Search”.
Also see: “How to Make a Threaded Client”
Comments and Errors

.
Download MySQL from here.


wget http://dev.mysql.com/..../MySQL-server-5.0.15-0.i386.rpm



Now lets verify the integrity of the package using md5sum

[root@plain install]# md5sum MySQL-server-5.0.15-0.i386.rpm
13971fd27ff2dc38537ff157ef6b715a MySQL-server-5.0.15-0.i386.rpm
Now verify that the MD5 checksum listed matches the MD5 Checksum on MySQL site.

In our case the md5 checksums do match.

13971fd27ff2dc38537ff157ef6b715a == 13971fd27ff2dc38537ff157ef6b715a

Therefore we can continue with the installation.

MySQL won't let you have both servers on one machine as we get the following error when installing MySQL 5 on top of MySQL 3

[root@plain install]# rpm -ivh MySQL-server-5.0.15-0.i386.rpm
warning: MySQL-server-5.0.15-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
MySQL conflicts with mysql-3.23.58-2.3
MySQL-server conflicts with mysql-server-3.23.58-1

We get the following errors when trying to uninstall MySQL

[root@plain install]# rpm -ev mysql
error: Failed dependencies:
libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3
libmysqlclient.so.10 is needed by (installed) libdbi-dbd-mysql-0.6.5-5
libmysqlclient.so.10 is needed by (installed) MySQL-python-0.9.1-6
libmysqlclient.so.10 is needed by (installed) MyODBC-2.50.39-12
libmysqlclient.so.10 is needed by (installed) mysql-server-3.23.58-1
mysql = 3.23.58 is needed by (installed) mysql-devel-3.23.58-2.3
mysql is needed by (installed) MySQL-python-0.9.1-6
mysql = 3.23.58 is needed by (installed) mysql-server-3.23.58-1

To fix this, we need to uninstall MySQL 3.23 and all dependencies

rpm -ev mysql perl-DBD-MySQL-2.1021-3 libdbi-dbd-mysql-0.6.5-5 MySQL-python-0.9.1-6 MyODBC-2.50.39-12 mysql-server-3.23.58-1 mysql-devel-3.23.58-2.3 MySQL-python-0.9.1-6 mysql-server-3.23.58-1
Now lets get the other MySQL utilities

Benchmark suits

Downloading

wget http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-server-standard-5.0.15-0.rhel3.i386.rpm/from/ftp://ftp.orst.edu/pub/mysql/

wget http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-client-standard-5.0.15-0.rhel3.i386.rpm/from/ftp://ftp.orst.edu/pub/mysql
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm/from/pick/ftp:/ftp.orst.edu/pub/mysql
wget http://dev.mysql.com/get/Downloads/MySQL-5.0/MySQL-devel-standard-5.0.15-0.rhel3.i386.rpm/from/pick/ftp:/ftp.orst.edu/pub/mysql/

You should have the following RPMs now for MySQL

[root@plain mysql]# ls
MySQL-bench-5.0.15-0.i386.rpm MySQL-server-standard-5.0.15-0.rhel3.i386.rpm
MySQL-client-standard-5.0.15-0.rhel3.i386.rpm MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm

Calculate MD5


Verify all the packages using MD5

[root@plain mysql]# md5sum MySQL-*
3b02c71d5d5354bcd8ce9a534a999d74 MySQL-bench-5.0.15-0.i386.rpm
c7a0047f24d2ba0494829eb5a9c572bf MySQL-client-standard-5.0.15-0.rhel3.i386.rpm
b13df3d95bb8fca02d23546b84931140 MySQL-server-standard-5.0.15-0.rhel3.i386.rpm
109ca0273a6a7c460e4afaef3def88b3 MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm
51772ca969fa08a13064410469d79191 MySQL-devel-standard-5.0.15-0.rhel3.i386.rpm


Now lets install the MySQL 5 RPM

[root@plain mysql]# rpm -ivh MySQL-client-standard-5.0.15-0.rhel3.i386.rpm
warning: MySQL-client-standard-5.0.15-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-client-standard ########################################### [100%]
[root@plain mysql]# rpm -ivh MySQL-server-standard-5.0.15-0.rhel3.i386.rpm
warning: MySQL-server-standard-5.0.15-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-server-standard ########################################### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h server password 'new-password'
See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
Starting MySQL.[ OK ]
[root@plain mysql]# rpm -ivh MySQL-devel-standard-5.0.15-0.rhel3.i386.rpm
warning: MySQL-devel-standard-5.0.15-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-devel-standard ########################################### [100%]
[root@plain mysql]#
[root@plain mysql]# rpm -ivh MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm
warning: MySQL-shared-standard-5.0.15-0.rhel3.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
Preparing... ########################################### [100%]
1:MySQL-shared-standard ########################################### [100%]
[root@plain mysql]# rpm -ivh MySQL-bench-5.0.15-0.i386.rpm
warning: MySQL-bench-5.0.15-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5
error: Failed dependencies:
MySQL-client is needed by MySQL-bench-5.0.15-0
[root@plain mysql]#

Notice we got an error for the benchmarks one. We will investigate this later.

Now we can confirm that the MySQL RPMs have been installed.

[root@plain mysql]# rpm -qa | grep -i mysql
MySQL-client-standard-5.0.15-0.rhel3
MySQL-server-standard-5.0.15-0.rhel3
MySQL-shared-standard-5.0.15-0.rhel3
MySQL-devel-standard-5.0.15-0.rhel3



END


EV1 - MySQL4

Wednesday, October 26, 2005

MySQL 5.0 DBA - Review

Today, I launched this blog and became aware of MySQL 5.0 being released. I have decided to upgrade MySQL from 3.23.56 to 5.0. This post will contain my review and the next post will include my upgrade to MySQL 5.0 details.

First some exciting stuff about MySQL

QUICK LOOKS: (MySQL 5.0 Whitepaper)

  • MySQL has six million active installations worldwide
  • 40,000 MySQL downloads occur every day.


Countless corporations, educational facilities, and government agencies rely on MySQL to manage their critical data assets and service anywhere from hundreds to billions of requests per day. With over six million active installations worldwide and 40,000 downloads occurring every day, the popularity and prominence of MySQL continue to rise.


reliability, performance and ease-of-use are "the three priorities of the MySQL database server."


ACID Transactions – MySQL supports ACID (Atomic, Consistent, Isolated, Durable) transaction model that ensures the integrity and validity of every entered transaction. Full COMMIT and ROLLBACK, with redo logs and undo (rollback) segment areas to ensure data consistency during crash recovery operations.
• Server-Based Data Integrity – Invalid data (bad dates, invalid numbers, etc.) can be automatically rejected at the server, with column-level rules enforcement being possible. In addition, full foreign-key support is provided so that complete data referential integrity is guaranteed.
• Low Contention Issues – Unlimited row-level locking is provided within InnoDB as is automatic deadlock detection, custom isolation levels (READ COMMITTED, etc.), to virtually eliminate locking issues. In addition, InnoDB supports multi-version concurrency control so that those reading data see a snapshot of the requested information as it existed at the moment their query was issued. This ability ensures that readers don’t block writers and vice-versa, and leads to extremely high levels of data availability.


  • Support for stored procedures
  • Fast performance
  • Easier Security Administration
  • Fast Performance
  • Reduced Network Traffic

Using MySQL 5.0 Stored procedures

  • very easy to use
  • adhere to the ANSI SQL 2003 specification
  • standoutput ability for SELECT statements



Creating MySQL Stored Procedure


delimiter //
create procedure myproc()
select * .....;
//
delimiter;
call top_broker();




delimiter //
create function broker_name(id int)
returns varchar(50)
deterministic
begin
declare broker_name(VARCHAR (50)
select * into broker_name from broker .....;
return broker_name;
end
//
delimiter;
select top_broker(1);





  • "SHOW PROCEDURE STATUS " is now possible
  • mysql.proc can be queried
  • 5.0 INFORMATION_SCHEMA data dictionary
  • SHOW CREATE PROCEDURE function (to create DDL)

MySQL 5.0 is "capable of meeting our customers' toughest requirements" : Rick Bernard - SSA Global.

MySQL 5.0 Released for Production


MySQL 5.0 Now Available for Production Use Enterprise Platform Vendors Express Enthusiastic Support for World's Most Popular Open Source Database Major New Version Delivers Advanced SQL Standard-Compliant Features Such as Stored Procedures, Triggers, Views & New Pluggable Storage Engines

UPPSALA, Sweden & CUPERTINO, California. -- October 24, 2005 -- MySQL AB, developer of the world's most popular open source database, today announced the general availability of MySQL 5.0, the most significant product upgrade in the company's ten-year history. Starting today, MySQL 5.0 can be downloaded under the open source GPL license at http:/dev.mysql.com.

"We are quite proud to deliver this new version of MySQL -- as it includes the most-requested features from our users, customers and partners," said Marten Mickos, CEO of MySQL AB. "With their feedback, we have been able to provide important enterprise-class features while staying true to MySQL's renowned heritage of high-performance, reliability and ease-of-use."
MySQL 5.0's Enterprise Database Features

The new MySQL 5.0 combines enterprise-grade reliability and performance with advanced SQL 2003 standard-compliant features, making it the most cost-effective database solution for business-critical applications. The product's new functionality will be especially familiar to database developers and DBAs of proprietary relational database systems -- allowing IT departments to ease the integration and transition of their data, applications and skill-sets.

MySQL 5.0 delivers dozens of new enterprise features, including:

* Stored Procedures and SQL Functions -- to embed business logic in the database and improve performance;
* Triggers -- to enforce complex business rules at the database level;
* Views -- to ensure protection of sensitive information;
* Cursors -- to allow easier database development and reference of large datasets;
* Information Schema -- to provide easy access to metadata;
* XA Distributed Transactions -- supports complex transactions across multiple databases in heterogeneous environments;
* SQL Mode -- provides server-enforced data integrity for new and existing data;
* New Federated and Archive Storage Engines -- MySQL's unique pluggable storage engine architecture allows greater flexibility, functionality and performance by making it easy to swap database engines in and out, based on users' application requirements;
* New Migration Toolkit -- A new graphical toolkit that completely migrates all data and objects from Oracle, Microsoft SQL Server, Microsoft Access and other database platforms to MySQL;
* Instance Manager -- new management assistant that allows remote starting/stopping of any MySQL Server, as well as remote editing of configuration files, reading of error and query logs, and more;
* Updated Connectors and Visual Tools -- new high-performance versions of MySQL's ODBC, Java and .NET database drivers are now available, along with updated versions of the MySQL Query Browser and MySQL Administrator.

"MySQL 5.0 represents a huge leap in features and functionality for the MySQL database," said Gunter Rombauts, European IT manager for Chicago Metallic. "As a worldwide leader in metal ceiling systems, it's critical that our database supports heavy-duty enterprise applications with speed and reliability. We are looking forward to using MySQL 5.0 as a key component of Chicago Metallic's global IT infrastructure."
Industry Support for MySQL 5.0

Over 30 enterprise software and database tool vendors have expressed support for MySQL 5.0. These third-party partners' products and platforms will be certified for MySQL Network, the company's commercial database subscription service. They include Acronis, ActiveGrid, Actuate BIRT, Agresso, Alphasoftware, Arkeia, Business Objects, Embarcadero Technologies, Emic Networks, Four J's, GoldenGate Software, HP, IBM, Informatica, Jaspersoft, NEC System Technologies, Nomura Research Institute, Novell, Pentaho, Quest Software, Red Hat, Samsung SDS, SAP, SourceLabs, SpikeSource, SSA Global, Steeleye, Sumisho Computer Systems Corporation, Sun Microsystems, SugarCRM, Zend, and Zimbra.

SSA Global, a leading global provider of extended enterprise solutions and services, will certify SSA ERP LN 6.1 with MySQL Network 5.0 before the end of this year. "MySQL 5.0, with its enterprise-class features, represents a very cost-effective and proven database alternative capable of meeting our customers' toughest requirements," said Rick Bernard, vice president Solutions Management, SSA Global. "SSA Global is committed to Open Source solutions, and support for MySQL is the next significant step in delivering value to our customers."

Ton Dobbe, product marketing manager for Agresso, states, "Apart from being the most popular open source database with over 6 million active installations worldwide, MySQL 5.0 also provides our clients with a robust, viable alternative to traditional offerings in the market at total cost of ownership savings of up to 90 percent."
Platforms and Availability

The production version of MySQL 5.0 is available now for Linux, Windows, Solaris, Mac OS X, FreeBSD, HP-UX, IBM AIX 5L and a range of other operating systems.All MySQL database software is available under a dual licensing model. Developers and organizations can choose between an open source (GPL) or a commercial MySQL license, depending on their deployment requirements. End-user enterprises can also subscribe to MySQL Network, a comprehensive set of certified software and premium support services.For software downloads and additional information, visit http://www.mysql.com/products/.
About MySQL

MySQL AB develops and supports a family of high performance, affordable database products. The company's flagship product is MySQL, the world's most popular open source database, with more than 6 million active installations. Many of the world's largest organizations, including Yahoo!, Sabre Holdings, The Associated Press, Suzuki and NASA are realizing significant cost savings by using MySQL to power high-volume Web sites, business-critical enterprise applications and packaged software.

With headquarters in Sweden and the United States -- and operations around the world -- MySQL AB supports both open source values and corporate customers' needs in a profitable, sustainable business. For more information about MySQL, please visit www.mysql.com.

# # #

MySQL is a registered trademark of MySQL AB in the USA and other countries. Other products mentioned may be trademarks of their respective companies.

Source: MySQL.com

Information for MySQL Database Administrators (DBA)
See MySQL 5.0 Changes and
MySQL upgrading from 4.1