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

Upgrading MySQL - Upgrade MySQL to 5.0

This guide will show you how to on a Linux Box.

Also see article.