SQL, (Structured Query Language; other database links, SEQUEL.) A relational database management system RDMS
Also links to other Database related websites. MySQL, (pronounced "my ess cue el," not "my sequel"),
is a Relational Database Management System, (RDMS), which means it stores data
in separate tables rather than putting all the data in one big area. This adds
flexibility, as well as speed. The SQL part of MySQL stands for "Structured
Query Language," which is the most common language used to access
databases. The MySQL database server is the most popular open source database in
the world. It is extremely fast and easy to customize, due to its architecture.
Extensive reuse of code within the software, along with a minimalist approach to
producing features with lots of functionality, gives MySQL a claimed unmatched speed,
compactness, stability, and ease of deployment. Their unique separation of the
core server from the storage engine makes it possible to run with very strict
control, or with ultra fast disk access, whichever is more appropriate for the
situation.
Click Here For Your Own Business Website. With hundreds Of Free Webmaster Resources. Webmaster resources like no other site
on the Internet. Provides webmasters with the tools they need to create fun and valuable business websites from scratch, within minutes. Combined professional
quality designs, powerful PHP and MySQL scripts to create the largest and most exclusive turnkey collection
for web designers, entrepreneurs and beginners or enthusiast.
SQL Junkies A feature-packed SQL Server Web site communities on the Internet today. Community for developers to
come and learn about building solutions using Microsoft SQL Server while being part of a collaborative community of peers.
MySQL Query Analyzer is a free, powerful and simple to use tool for creating QL scripts for MySQL database engine.
mysqld, also known as MySQL Server, is the
main program that does most of the work in a MySQL installation. MySQL Server manages access to the MySQL data directory that contains databases and tables.
The data directory is also the default location for other information such as log files and status files
phpMyAdmin (Web Interface for SQL). A free software tool written in PHP intended to handle the administration of MySQL over the World Wide Web. phpMyAdmin supports a wide range of operations with MySQL. The most frequently used operations are supported by the user interface (managing databases, tables, fields, relations, indexes, users, permissions, etc), while you still have the ability to directly execute any SQL statement.
BigDump: Staggered MySQL Dump Importer. Staggered import of large and very large MySQL Dumps (like
phpMyAdmin Dumps) even through the web-servers with hard runtime limit and those in safe mode. The script executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped. This is great for getting those huge SQL files uploaded and installed ready for use with your SQL quires.
MySQLDumper is a backup script for MySQL-Databases, written in
PHP and Perl. MySQLDumper uses a proprietary technique to avoid execution interruption. It only reads and saves a certain amount of commands and then calls itself via JavaScript and memorizes how far in the process it was and resumes its action from its last standby. MySQLDumper restores a backup file by using the same process. Unlike other tools splitting and splicing of large files is no longer necessary. MySQLDumper offers to write data directly into a compressed gz-File. The Restore-Script is able to read this file directly without unpacking it. Of course you can use it without compression, however using Gzip saves a sizeable amount of bandwidth.
mysqldump A Database Backup Program. The
mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or
XML format. The Database Publishing Wizard enables the deployment of SQL Server 2005 databases (both schema and data) into a shared hosting environment on either a SQL Server 2000 or 2005 server. The tool supports two modes of deployment: It generates a single SQL script file which can be used to recreate a database when the only connectivity to a server is through a web-based control panel with a script execution window. It connects to a web service provided by your hoster and directly creates objects on a specified hosted database.
The Database Publishing Wizard provide both a graphical and a command-line interface. In addition, it can integrate directly into Visual Studio 2005 or Visual Web Developer 2005
mylvmbackup is a tool for quickly creating backups of a MySQL server's data files. To perform a backup, mylvmbackup obtains
a read lock on all tables and flushes all server caches to disk, creates a snapshot of the volume containing the MySQL data directory, and unlocks the
tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup
proceeds. See Lenz Grimmer's blog Random notes about Linux, MySQL and Open SourceAlso read LanchPad
mylvmbackup
Maatkit makes
MySQL easier and safer to manage. It provides simple, predictable ways to do
things you cannot otherwise do. It would be nice if these features were included
with MySQL, but they are not. That's why Maatkit is now shipping by default with
many GNU/Linux distributions such as Debian and CentOS. You can use
Maatkit to prove replication is working correctly, fix corrupted data, automate
repetitive tasks, speed up your servers, and much, much more.
MySQL Backup enables you to backup a consistent image of a MySQL Server's data and associated
metadata via a direct connection to the MySQL server. The backup is synchronized between different storage engines and with the binary log (that can be used for
point in time recovery). Different techniques are used by different storage engines to provide the best possible backup and restore. The backup image is
stored as a file by the MySQL server. Note: MySQL Backup is currently being developed and this page describes the work in progress.
Online Backup of MySQL Cluster
MySQL Workbench is a visual database design tool that is developed by MySQL. It is the successor application
of the DBDesigner4 project. It is able to display EER Diagrams, (Entity-Relationship Diagrams), that visualize different parts of the
catalogue.
PostgreSQL code
generator A tiny and easy to use application that generates a native PL/pgSQL
script. The script incrementally updates the tables, columns, indexes and
constraints in the database to match the RISE model. Once the database model is
updated, the views defined in the RISE model are created in the database and
possible default data, entered in the model, is inserted.
Thu, 02 Sep 2010 21:40:46 +0000 - As previously mentioned, Darren Cassar has been working on a new automated installer for the DBbenchmark program. It’s now available for download: click here. All you need to do is save it to the directory that you want to install to and then make sure it’s executable: “chmod 700 installer.sh”, then run it “./installer.sh”.
Thu, 02 Sep 2010 21:40:44 +0000 - How long it may take MySQL with Innodb tables to shut down ? It can be quite a while.
In default configuration innodb_fast_shutdown=ON the main job Innodb has to do to complete shutdown is flushing dirty buffers. The number of dirty buffers in the buffer pool varies depending on innodb_max_dirty_pages_pct as well as workload and innodb_log_buffer_size and can be anywhere from 10 to 90% in the real life workloads. Innodb_buffer_pool_pages_dirty status will show you the actual data. Now the flush speed also depends on number of factors. First it is your storage configuration – you may be looking at less than 200 writes/sec for single entry level hard drive to tens of thousands of writes/sec for high end SSD card. Flushing can be done using multiple threads (in XtraDB and Innodb Plugin at least) so it scales well with multiple hard drives. The second important variable is your workload, especially how dirty pages would line up on the hard drive. If there are a lot of sequential pages which are dirty Innodb will be able to use larger size IOs – up to 1MB flushing dirty pages which can be a lot faster than flushing data page by page.
So if we have system with single hard drive doing 200 IO/ssc, 48G buffer pool which is 90% dirty and completely random page writes we’ll look at 13500 seconds or about 5min per 1GB of Buffer pool size.
This is worse case scenario though it is quite common in practice to see shutdown time of about 1min per GB of buffer pool per hard drive.
Baron has written a nice post how to decrease innodb shutdown time which you may want to read on this topic.
Entry posted by peter |
No comment
Add to: | | | |
Thu, 02 Sep 2010 20:24:20 +0000 - So far the benchmarking script supports Linux, FreeBSD, and OSX. I’m installing virtual machines today to get ready for development on the next OS that the community wants to have supported. Vote today for your choice. Development will begin Friday 2010-09-03.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.
Thu, 02 Sep 2010 17:53:19 +0000 - At yesterdays Eigenbase Developer Meetup at SQLstream’s offices in San Francisco we arrived at a new logo for LucidDB. DynamoBI is thrilled to have supported and funded the design contest to arrive at our new mascot. Over the coming months you’ll see the logo make it’s way out to the existing luciddb.org sites, wiki sites, etc. I’m really happy to have a logo that matches the nature of our database - BAD ASS!
Thu, 02 Sep 2010 17:47:27 +0000 - Often, the first step in evaluating and deploying a database is to load an existing dataset into the database. In the latest version, TokuDB makes use of multi-core parallelism to speed up loading (and new index creation). Using the loader, MySQL tables using TokuDB load 5x-8x faster than with previous versions of TokuDB.
Measuring Load Performance
We generated several different datasets to measure the performance of TokuDB when doing a LOAD DATA INFILE … command. To characterize performance, we vary
rows to load
keys per row
row length (including keys)
All generated keys, including the primary, are random, 8-byte values. The remaining data, needed to pad out the row length to specified length, is text.
Two files files are produced as part of data generation.
data file, containing ‘|’ separated fields
sql file, containing the CREATE TABLE command corresponding to the generated data
For instance, if the number of keys is 3 and the row length is 256 bytes, the following SQL statement is produced:
CREATE TABLE load_table (\
val0 BIGINT UNSIGNED NOT NULL,\
val1 BIGINT UNSIGNED NOT NULL,\
val2 BIGINT UNSIGNED NOT NULL,\
pad VARCHAR(232) NOT NULL,\
PRIMARY KEY (val0),\
KEY valkey1 (val1),\
KEY valkey2 (val2)\
) ENGINE=tokudb
We can make the data generation program available if anyone is interested.
Load Test
A simple shell script
creates the test table
performs a LOAD DATA INFILE <datafile> INTO TABLE load_table FIELDS TERMINATED BY ‘|’
returns execution time
For the experiments to be meaningful, we created datasets that do not fit in memory.
Results
We ran our benchmark on an Amazon Web Services c1.large node with 8 cores and 7 GB of memory. The test loads 100M rows (NOT pre-sorted). The data file was on a 2 disk RAID-0, the MySQL DB files on a different 2 disk RAID-0.
TokuDB Version 3 (~single-threaded) v. TokuDB Version 4 (multi-threaded)
Keys
Row Len
v3 rows/s
v4 rows/s
Speedup
1
64
27K
142K
5.1
4
64
13K
82K
6.2
1
256
7K
54K
7.2
4
256
5K
43K
8.2
Other metrics
Several metrics can be used to measure performance:
rows per second : data insert rate
key-value pairs per second : indicates how fast the primary and secondary indexes are being created
MB/s : how much raw data is being added to the database
Metrics for TokuDB v4:
Keys
Row Len
Rows/sec
KV-pairs/sec
MB/sec
1
64
142K
142K
9.1
4
64
82K
330K
5.3
1
256
54K
54K
13.9
4
256
43K
173K
11.1
These results show
significant parallelization (we believe larger CPU core count machines will see even larger benefits)
a significant jump in absolute load performance
speed-ups are not limited to tables with many keys – even the 1 key tables are 5-7x faster
We will report further results, especially speedups on larger CPU count machines, as they become available.
Thu, 02 Sep 2010 14:31:00 +0000 - I am speaking at MySQL Sunday. The title for my talk is Success with MySQL and I will focus on things that operations and users can do to make a MySQL deployment succeed. There are many interesting talks scheduled for Sunday, including several at the same time as mine. I hope to see you there.
Thu, 02 Sep 2010 13:35:00 +0000 - I get a number of question about contentions/"stuck in..". So here comes some explanation to:ContentionThread Stuck inWhat you can do about itIn 99% of the cases the contentions written out in the out file of the data nodes (ndb_X_out.log) is nothing to pay attention to.sendbufferpool waiting for lock, contentions: 6000 spins: 489200 sendbufferpool waiting for lock, contentions: 6200 spins: 494721Each spin is read from the L1 cache (4 cycles on a Nehalem (3.2GHz), so about a nanosecond).1 spin = 1.25E-09 seconds (1.25ns)In the above we have:(494721-489200)/(6200-6000)= 27 spins/contentionTime spent on a contention=27 x 1.25E-09=3.375E-08 seconds (0.03375 us)So we don't have a problem..Another example (here is a lock guarding a job buffer (JBA = JobBuffer A, in short it handles signals for heartbeats and some other small things, all traffic goes over JobBuffer B).jbalock thr: 1 waiting for lock, contentions: 145000 spins: 3280892543 jbalock thr: 1 waiting for lock, contentions: 150000 spins: 3403539479(3403539479-3280892543)/(150000-145000)=24529 spins/contentionTime spent on a contention: 3.06613E-05 seconds (30.66us )This is a bit higher than I would have expected and I think more analysis is needed. However, i tend not to get these contentions on a busy system.Ndb kernel thread X is stuck in ..Ndb kernel thread 4 is stuck in: Job Handling elapsed=100 Watchdog: User time: 82 System time: 667 Ndb kernel thread 4 is stuck in: Job Handling elapsed=200Watchdog: User time: 82 System time: 668 Ndb kernel thread 4 is stuck in: Job Handling elapsed=300 Watchdog: User time: 82 System time: 669 Ndb kernel thread 4 is stuck in: Job Handling elapsed=400 Watchdog: User time: 82 System time: 670Here the important is to look at how User time and System time behaves.If User time is constant (as it is here - 82ms), but the System time is growing (667, 668 etc) which indicates that the OS kernel is busy.Slow network? Sub-optimal kernel version? NIC drivers? swapping? some kernel process using too much cpu?If User time is growing it is probably because the ndb kernel is overloaded.What can you do about this?In config.ini:RealtimeScheduler=1LockExecThreadToCPU=[cpuids]check that cpuspeed is not running ( yum remove cpuspeed ).. and finally ask us to optimize more!Also, pay attention if you get the contentions on an idle Cluster or a busy Cluster.
Thu, 02 Sep 2010 10:48:00 +0000 - Join us at this live event in Milan to better understand what’s new with MySQL. You will learn more about the current and future state of MySQL, now part of the Oracle family of products. We will also cover Oracle’s investment in MySQL aiming to make it even a better MySQL.
In particular the following topics will be discussed:
Oracle’s MySQL Strategy
What’s New for:
The MySQL Server
MySQL Cluster
MySQL Enterprise
MySQL Workbench
Stay tuned because we are organizing a similar event in Rome that will be announced soon. Attendance is free, but you’ll need to register in advance. Seats are limited, register today!
When:September 28, 2010: 9:30am in Milan - Register here.
Thu, 02 Sep 2010 10:31:18 +0000 - (English version) Herhalde bildiğiniz gibi, biz tamamen sanal şirketiz, yani ana merkez gib bir şeyimiz yok. Hepimiz evinden çalışıyor ve biz sadece bir ya da iki kez yılda gerçek hayatta buluşuyorus. Bu sene kararımız İstanbul‘a düştü. Firmadaki tek Türk ben olduğumdan dolayı toplantının organizesi bana düştü.
İlk adım olarak böyle bir toplantının özelikleri nedir ve en iyi şekilde nasıl hazır edilir diye araştırdım. Kaj Arnö bunun ayrıntılarını blog’unda açıklamış bile: How to arrange a physical meeting in a virtual organisation. Kaj ile İstanbul’a 2008′de gelmiştik ve çeşitli konferanslar vermiştik.
İlk toplantı tarihi ile ilgili bir anket yaptık ve İstanbul’daki toplantı Ekim ayına karar verdik. Tam olarak, Perşembe, 7 Ekim’den Salı, 12 Ekim 2010′a kadar.
Yaklaşık 30 kişilik toplantıyı düzenlemek pek bir basit görev değil, bu nedenle toplantıyı hazırlanmak için bana bir asistan tahsis edildi – oldukçada ünlü bir asistan – My Widenius. My (okunuşu Mü) MySQL’ın My’sü ve ona bir mariadb.org e-posta adresinle ulaşabilisiniz.
Eğer Ekim’in başında İstanbul’da iseniz, sizi bizim toplantıya davet etmekten mutluluk duyarız. Üç toplantı günleri olacaktır: Cuma 8 Ekim, Cumartesi 9 Ekim ve Pazar 10 Ekim 2010. Toplantıların çoğu herkese açık olacaktır. Misafirlerimiz olarak Facebook, Percona ve Intel bizlen olucak.
Ayrıca yerel (İstanbul) kullanıcı grupları ve bizim toplantıya ilgilenlerini arıyoruz. Sizi İstanbul’da görmek üzere, …
Thu, 02 Sep 2010 08:56:15 +0000 - Sphinx search is a full text search engine, commonly used with MySQL.
There are some misconceptions about Sphinx and its usage. Following is a list of some of Sphinx’ properties, hoping to answer some common questions.
Sphinx is not part of MySQL/Oracle.
It is a standalone server; an external application to MySQL.
Actually, it is not MySQL specific. It can work with other RDBMS: PostgreSQL, MS SQL Server.
And, although described as “free open-source SQL full-text search engine”, it is not SQL-specific: Sphinx can read documents from XML.
It is often described as “full text search for InnoDB”. This description is misleading. Sphinx indexes text; be it from any storage engine or external source. It solves, in a way, the issue of “FULLTEXT is only supported by MyISAM”. Essentially, it provided full-text indexing for InnoDB tables, but in a very different way than the way MyISAM’s FULLTEXT index works.
Sphinx works by reading documents, usually from databases. Considering the case of MySQL, Sphinx issues a SQL query which retrieves relevant data (mostly the text you want to index, but other properties allowed).
Being an external module, it does not update its indexes on the fly. So if 10 new rows are INSERTed, it has no knowledge of this. It must be called externally to re-read the data (or just read the new data), and re-index.
This is perhaps the greatest difference, functionality-wise, between Sphinx and MyISAM’s FULLTEXT. The latter is always updated, for every row INSERTed, DELETEd or UPDATEd. The latter also suffers by this property, as this makes for serious overhead with large volumes.
There’s more than one way to make that less of an issue. I’ll write some more in future posts.
Sphinx does not keep the text to itself; just the index. Sphinx cannot be asked “Give me the blog post content for those posts containing ‘open source’”.
Sphinx will only tell you the ID (i.e. Primary Key) for the row that matches your search.
It is up to you to then get the content from the table.
With SphinxSE (Sphinx Storage Engine for MySQL) this becomes easier, all-in-one query.
It can keep other numeric data. Such data can be used to filter results.
It provides with GROUP BY-like, as well as ORDER BY-like mechanism.
It allows for ordering results by relevance.
It allows for exact match search, boolean search, and more.
It has an API & implementation for popular programming languages: PHP, Python, Perl, Ruby, Java.
The above describes Sphinx as a general fulltext search engine for databases. It does, however, have special treatment for MySQL:
First and foremost, it knows how to query MySQL for data (duh!)
If you don’t mind compiling from source, you can rebuild MySQL with SphinxSE: a storage engine implementation. This storage engine does not actually hold any data, but rather provides an SQL-like interface to the search daemon.
Thus, you can query for search results using SELECT statements, JOINing to document tables, retrieving results, all in one step.
If you do mind compiling MySQL, be aware that MariaDB comes with SphinxSE built in in newer versions.
It implements the MySQL protocol. You can connect to the sphinx server using a MySQL client, and actually issue SQL statements to retrieve data. Not all SQL is supported. The valid subset is called SphinxQL.
Snippets (excerpts) are supported via MySQL UDF.
Thu, 02 Sep 2010 06:58:52 +0000 - Open Query is now three years old! We initially started with consulting and training services, and extended this with our proactive subscriptions that also offers system administration and monitoring.
So how is it going? Pretty well. We’ve been profitable from the start, without funding (beyond a few hundred $ startup costs paid by Arjen) or any credit – by choice. Our objective has never been to grow ridiculously in terms of revenue or number of customers, we simply charge reasonable prices for real service. Right now we have dozens of clients on an ongoing basis, a neat trickle of new clients, and Open Query sustains the livelyhood and lifestyle of a number of people.
For me (Arjen), the three year mark is particularly interesting, since most startups do not make it past their first two years. With our different approach to doing business, we’ve seen our fair share of skepticism. Not that we mind, if anything it’s encouragement
If you’d like to learn more about our business principles, see the Upstarta site.
Thu, 02 Sep 2010 05:17:08 +0000 - Seriously, it did. Sorta.
I use Workbench for my daily work, and it’s a great tool. If you haven’t tried the 5.2 release yet, you should. While performing some maintenance, I happened to issue a DELETE statement against a table which had no indexes (it was 10 rows), and Workbench complained:
Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
It turns out this is a new feature in 5.2.26 (and is still there in 5.2.27) – Workbench now uses the equivalent of –safe-updates mode for the mysql command-line client (also known as the –i-am-a-dummy option – seriously). This wasn’t exactly convenient for me, especially since the DELETE was part of a larger script which I then had to revise and step through manually after it failed, but there’s an easy way to change this behavior. If you’re like me, you might consider disabling this:
Go to Edit -> Preferences
Select the SQL Editor tab
Uncheck “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”
Despite the text, the –safe-updates mode affects more than UPDATE and DELETE statements without WHERE clauses – it requires such statements to explicitly use indexes.
I’m changing this behavior on my installation before I run into other problems.
I’m no dummy.
Wed, 01 Sep 2010 23:05:54 +0000 - In database modeling, a m:n relationship is usually resolved by an additional table. But what if this relation is used only for archiving and the number of links in the resulting table is not too high? In that context, I got the idea to store all referring ID's as CSV string directly into a TEXT column of one of the referring tables. I came to this idea, because otherwise I would have to build complicated foreign keys and this way I also save one additional table. Certainly, this only makes sense if the data is not frequently accessed as foreign key. Nevertheless, I would like to tackle the problem, even if the implementation is very MySQL-oriented.Read the rest »
Wed, 01 Sep 2010 16:43:19 +0000 - “Hey DBA! Remember that change you made for my group four or five months ago? Well, we were waiting for things to get better but they have not. Can you change it back ASAP?!?”
I know several of you DBAs after reading the above are reaching for their antacids. OR something stronger.
Keeping track of what changes were made to which systems in your head is bound to bite a hard working DBA sooner or later.
This is one of those oh-so-obvious tips that will be ignored by many but it will come back to haunt like a cheap RAID array. Get a notebook, a pen, and keep it by you when you work. Now anytime you make a material change or perform a maintenance function on one of your systems, note what EXACTLY the change you made, the reason for the change, plus the day and date. It will take some effort to do this the first two times but you this to become an ingrained habit. Keeping track of running myisamcheck, changing the IP address of a replication slave, or setting up a MyIsam buffer cache is tedious but it will pay off when you are assailed with something similar to the sentence that starts this entry.
And for those of us with poor rotten hand writing — take the effort to make it legible. You may need to read it at 3AM after twenty hours of fighting a problem. Or the poor person covering for you while you are on vacation can not tell if you changed max_connections or max_connect_errors and decides to set them both to zero.
So why not store this data electronically? Well, Murphy’s Law aside, it is very handy to have something to refer to that is portable, does not need electrons to read, and is based on a technology even the most pointy haired boss can understand.
And it helps to have a print out of your my.cnfs taped into the back of the notebook.
Here are the last two entries in my log.
August 30, 2010 — Installed UDF for calculating median value on IBMtest1.
September 10, 2010 — System ‘A1′ : changed DBBC.NumBlocksPCT from 66 to 20 in Calpont.xml. PrimProc process was not able to allocate 66% but was able to get 20%. Will slowly walk up setting until it fails again to determine maximum setting.
Wed, 01 Sep 2010 16:15:17 +0000 - Not much new this time: just summary of part 3 published and fixed mistake in chapter 10 (thanks, Shane!).
Summary.
In the third part we discussed methods of application debugging in cases when query plays secondary role in the problem.
I'd like to bring your attention we only discussed most frequent cases while MySQL server has a lot of parameters which of them can affect application. Analyze parameters which you use. One of the methods is run problematic query using MySQL server running with option --no-defaults and examine if results are different for MySQL server run with parameter which you use. If results are different analyze why parameter affects it and solve the problem.
...
Rest of the chapter is here.
Wed, 01 Sep 2010 13:24:00 +0000 - Platform: MySQL 5.x
Most of the time it would be handy to have a native MySQL script which would allow one to kill the sleeping connections which are in sleep state for more than 180 sec.. On the other hand DBA's can use "wait_timeout" etc parameters to control this..
[code]
# -- Make sure you are logged as MySQL 'root' user or any user who have got super privileges
DELIMITER $$
DROP PROCEDURE IF EXISTS `uKillSleepingSessions`$$
CREATE PROCEDURE `uKillSleepingSessions`()
COMMENT 'This routne is used to kill idle sessions'
READS SQL DATA
BEGIN
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE uID bigint(4);
DECLARE my_cur CURSOR FOR
SELECT ID
FROM information_schema.PROCESSLIST PL
WHERE PL.COMMAND='Sleep' AND PL.TIME > 180;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;
OPEN my_cur;
select FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH my_cur
INTO uID;
IF no_more_rows THEN
CLOSE my_cur;
LEAVE the_loop;
END IF;
SET @tmp_sql= CONCAT("KILL ",uID);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;
END$$
DELIMITER ;
# Usage from mysql prompt/gui client
# Once called , it would cleanup idle connections which are sleeping for more than 180 sec
call uKillSleepingSessions();
[/code]
Wed, 01 Sep 2010 07:42:27 +0000 - Mårten Mickos, the CEO of Eucalyptus and former CEO of MySQL AB, will be back on stage as the closing keynoter on September 19th at MySQL Sunday, one of the community events at the start of Oracle Open World 2010.
The opening keynote will be delivered by Edward Screven, Chief Corporate Architect at Oracle.
MySQL Sunday has a very rich schedule, and by the registration numbers it looks like it's going to be packed.
Wed, 01 Sep 2010 01:26:10 +0000 - We’re proud to announce the next release of MySQL Workbench, version 5.2.27. This is the second maintenance release for 5.2 GA (Generally Available). This maintenance release does not introduce any new features, but focuses on general product improvement and usability. We hope you will make MySQL Workbench your preferred tool for Design, Development, and Administration of your MySQL database applications.
As always, we want to thank everyone for the great feedback we have received. This helps us to continuously improve and extend the functionality and stability of MySQL Workbench – please keep up on approaching us with any ideas to develop our product even further.
MySQL Workbench 5.2 GA
Data Modeling
Query (replaces the old MySQL Query Browser)
Administration (replaces the old MySQL Administrator)
Please get your copy from our Download site. Sources and binary packages are available for several platforms, including Windows, Mac OS X and Linux.
http://dev.mysql.com/downloads/workbench/
To get started quickly, please take a look at this short tutorial.
MySQL Workbench 5.2 RC Tutorial
http://wb.mysql.com/?p=406
Workbench Documentation can be found here.
http://dev.mysql.com/doc/workbench/en/index.html
In addition to the new Query/SQL Development and Administration modules, version 5.2 features improved stability and performance – especially in Windows, where OpenGL support has been enhanced and the UI was optimized to offer better responsiveness.
This release also includes improvements to the scripting capabilities of the SQL Editor. You can read more about it in
http://wb.mysql.com/workbench/doc/
For a detailed list of resolved issues, see the change log.
http://dev.mysql.com/doc/workbench/en/wb-change-history.html
If you need any additional info or help please get in touch with us.
Post in our forums, leave comments on our blog pages or if you want to talk to us directly you can visit us on our IRC channel #workbench on irc.freenode.net.
- The MySQL Workbench Team
Wed, 01 Sep 2010 00:52:49 +0000 - Ignacio Nin and I (mostly Ignacio) have worked together to create tcprstat[1], a new tool that times TCP requests and prints out statistics on them. The output looks somewhat like vmstat or iostat, but we’ve chosen the statistics carefully so you can compute meaningful things about your TCP traffic.
What is this good for? In a nutshell, it is a lightweight way to measure response times on a server such as a database, memcached, Apache, and so on. You can use this information for historical metrics, capacity planning, troubleshooting, and monitoring to name just a few.
The tcprstat tool itself is a means of gathering raw statistics, which are suitable for storing and manipulating with other programs and scripts. By default, tcprstat works just like vmstat: it runs once, prints out a line, and exits. You’ll probably want to tell it to run forever, and continue to print out more lines. Each line contains a timestamp and information about the response time of the requests within that time period. Here “response time” means, for a given TCP connection, the time elapsed from the last inbound packet until the first outbound packet. For many simple protocols such as HTTP and MySQL, this is the moral equivalent of a query’s response time.
The statistics we chose to output by default are the count, median, average, min, max, and standard deviation of the response times, in microseconds. These are repeated for the 95th and 99th percentiles as well. Other metrics are also available. Here’s a sample:
[root@server] # tcprstat -p 3306 -n 0 -t 1
timestamp count max min avg med stddev 95_max 95_avg 95_std 99_max 99_avg 99_std
1276827985 1341 24556 23 149 59 767 310 91 69 1030 107 112
1276827986 1329 12098 28 134 63 461 299 91 65 667 104 93
1276827987 1180 13277 22 202 93 873 439 103 79 1523 131 169
1276827988 1441 15878 27 180 139 672 427 116 79 1045 136 128
1276827989 1432 157198 26 272 138 4165 405 115 80 1092 134 123
1276827990 1835 25198 26 183 124 734 448 115 85 1141 137 141
1276827991 1242 6949 29 129 114 301 233 98 61 686 109 84
1276827992 1480 284181 25 442 127 7432 701 128 114 4157 173 293
1276827993 1448 9339 22 161 88 425 392 104 80 1280 126 140
tcprstat uses libpcap to capture traffic. It’s a threaded application that does the minimum possible work and uses efficient data structures. Your feedback on the kernel/userland exchange overhead caused by the packet sniffing would be very appreciated — libpcap allows the user to tune this exchange, so if you have suggestions on how to improve it, that’s great.
We build statically linked binaries with the preferred version of libpcap, which means there are no dependencies. You can just run the tool. In the future, packages in the Percona repositories will provide another means for rapid installation via yum and apt.
tcprstat is beta software. Several C/C++ experts reviewed its code and gave it a thumbs-up, so many eyes have been on the code. We’ve performed tests on servers with high loads and observed minimal resource consumption. I personally have been running it for many weeks on some production servers without stopping it and have seen no problems, so I am pretty sure it has no memory leaks or other problems. Nevertheless, it’s a first prototype release, and we want much more testing. We might also change the functionality; as we build tools around it, we discover new things that might be useful. When we’re happy with it and you’re happy with it, we’ll take the Beta label away and make it GA.
The tcprstat user’s manual and links to downloads are on the Percona wiki. Commercial support and services are provided by Percona. Bug reports, feature requests, etc should go to the Launchpad project linked from the user’s manual. General discussion is welcome on the Google Group also linked from the user’s manual.
[1] Historical note: we initially called this tool rtime, but did not publicize it. However, some of you might have heard of “rtime” before. This is the same tool.
Entry posted by Baron Schwartz |
8 comments
Add to: | | | |
Tue, 31 Aug 2010 22:57:22 +0000 - On September 19, 2010, Oracle is hosting MySQL Sunday, a half-day technical conference jam-packed with the latest on MySQL, the world's most popular open source database. The sessions will offer you insights into the latest MySQL technical innovations and community developments. Check out the agenda.
Keynotes
We are very excited that Marten Mickos, CEO, Eucalyptus Systems, will be joining us to deliver the closing keynote at MySQL Sunday, in addition to Edward Screven, Oracle's Chief Corporate Architect and Head of the MySQL business.
Secure your seat
MySQL Sunday is open to all Oracle OpenWorld, JavaOne, and Oracle Develop attendees, including those with the value-priced Discover pass ($75 if you register by September 18). You will be asked if you are attending MySQL Sunday during the conference registration process. Register today.
When: Sunday, September 19, 12:30 p.m. - 5:00 p.m.
Where: San Francisco Marriott Marquis
Tue, 31 Aug 2010 21:56:50 +0000 - There was a lot of talk about this being the next menace after email spam. I’m not actually sure what it’s called for VoIP systems, but my Asterisk setup has started to be attacked over the last few days. Lots of entries like:
[Aug 27 19:20:30] NOTICE[18826] chan_sip.c: Registration from '"742"<sip:742@a.b.c.d>' failed for '208.109.86.187' - No matching peer found
...
[Aug 31 10:13:10] NOTICE[18826] chan_sip.c: Registration from '"1002" <sip:1002@a.b.c.d>' failed for '41.191.224.2' - Wrong password
Lots of messages get logged a second and I noticed this as suddenly CPU load on my PC jumped up quite a bit.
For the moment I’ve routed these addresses via the interface lo0 so they won’t bother me any more, but I need to come up with a better solution.
First I’m curious if applications like Asterisk or FreeSwitch have any built-in anti-abuse controls to recognise bad behaviour and to disable those abusers. I’m pretty sure that I’ve not read about anything for Asterisk, and I’m currently reading the FreeSWITCH book I bought but haven’t come across this mentioned yet. Seems that applications like this may need to have these controls added at some time, just as sendmail, postfix and most mail servers have had to adjust to a hostile world.
The other option of course is to use a firewall or packet filter to limit the incoming traffic rate from a single IP to port 5060 or whereever the SIP connection is being accepted so that when going over the limit the ip will be blocked for some time. iptables can do this I think so I’m going to have to read about how to configure and set that up.
There are other applications designed to watch logs and use them to automatically add temporary blocks. fail2ban is one of these. I’ll also have to see if I can configure it for this task.
So if this has happened to you how do you protect your VoIP systems from that hostile world of the Internet?
Tue, 31 Aug 2010 20:26:07 +0000 - The development cycle is moving right along for the community’s newest MySQL benchmarking script. I’m pleased to announce that we now officially support FreeBSD (version 8.1 tested) so go ahead and download now and test your FreeBSD, Linux, or OSX MySQL server! Click here for the download.
Courtesy of Darren Cassar and some generous coding this weekend, we’re going to be releasing a auto-installer / updater for the application which you can use to automate that part of the process. Stay tuned for information on that release.
Tue, 31 Aug 2010 18:39:14 +0000 - Are you thinking...not one more blog, please! We have received a lot of feedback that we at Oracle need to be more vocal about our investment and progress with MySQL. The MySQL team at Oracle is very excited to launch a new blog where we will offer you the latest and greatest updates on product announcements, news, events, customers, activities, and overall progress about MySQL. You can be sure to find a mix of technical and business content.
As you continue to follow your favorite MySQL bloggers, we also hope that you will add "Oracle's MySQL Blog" to that list over time.
I manage MySQL product marketing at Oracle. You will meet a number of my colleagues in product marketing, product management, community relations and product development over time as you see them write through this blog.
Thanks for listening, and we look forward to your feedback.
Monica
Tue, 31 Aug 2010 18:12:22 +0000 - Scenario: Master-Master replication
Description: Master A is the active db server whilst Master B is a read only swappable db server hence both are creating binary logs. During backup I run “FLUSH LOGS” in order to have a simpler point in time recovery procedure if that case arises.
Problem: Flush logs is mean mean command :) …. it rotates not only my binary logs but my error log too (since I user error-log=blahblahblah in my my.cnf). Well given I flush logs every night my error log is cycled through every night, but unlike binary logs which have an incrimental number attached to the fine, error logs only have a `-log` attached to the filename and a second “FLUSH LOG” would just clear all error logs permanently. That is really not fun believe me!
So what is the solution? you could either:
1. Not use “FLUSH LOGS” (nah that aint happenin)
2. Not use –error-log (that aint happenin either cos I need to use it for my specific setup)
3. Create an ugly hack script which saves the error log or renames it or whatever (Ugly Ugly)
4. Create a variable in MySQL which says: flush-log-ignore-error-log which if set “FLUSH LOGS” will know not to mess around with my error log. Hence … create a feature request (nicer and friendlier solution) and submit a tested patch too whilst at it.
If you think it is not idea that “FLUSH LOGS” recycles through error logs or and you have been bitten by the way it works like me, then feel free to put your vote into http://bugs.mysql.com/bug.php?id=56385&thanks=sub so that MySQL sees the importance of it.
Because Sharing is Caring!
Tue, 31 Aug 2010 17:59:00 +0000 - The bidding war between HP and Dell for 3PAR has created great theater. The rationale is simple, both HP and Dell want a complete set of products to sell into the new cloud space and 3PAR is the only bitsized morsel among EMC, IBM and Hitachi that addresses this space. What is the compelling advantage they offer in storage? Elasticity. 3PAR provides the ability for companies to add/remove storage in thin slices (AKA thin provisioning). How does this relate to ScaleDB? We do the exact same thing for databases in the cloud and we do it for the most popular database in the cloud, MySQL.How does VMWare play into this? Their CEO Paul Maritz was on CNBC talking about the hybrid cloud and how companies want to run core cloud capabilities on premise and then use the public cloud providers to handle compute overflow during peak usage. This means that public cloud value to corporations, assuming Maritz is correct, is based largely on their ability to provide elasticity. It will no longer be sufficient for public cloud companies to provide reserved servers, because the reserved servers will be run in the company’s data center. The public cloud will add/remove servers to handle peaks in usage. So elasticity is EVERYTHING. ScaleDB is all about elasticity for the database.It is also interesting to note from the Maritz interview that he sees the next wave of cloud (and hence the next wave of cloud consolidation) coming from the software sector. More specifically, the ability to take existing applications and make them run on the cloud. In other words, to make them elastic. Again, this is exactly what ScaleDB does. We take existing MySQL applications and make them elastic.It is also interesting to note that HP and Dell have decimated their own R&D and are now looking to acquire that expertise from outside, and they are willing to pay for the expertise.Another theme playing out in the background makes this situation even more interesting. Oracle has adopted a systems approach, where they combine their hardware and software:“The heart of the interview focused on Oracle's interest in Sun. By combining Sun's expertise in hardware with Oracle's software, Ellison suggested, the combined company can become a powerful "systems" company that sells solutions to businesses. The competitor that Ellison wants to beat: IBM.”Summary: Cloud is the next battle ground. It all starts with the hardware/infrastructure (e.g. 3PAR) and then moves upstream to software. Oracle will be focused on selling complete systems, alienating HP & Dell, among others. This is compounded by the fact that HP and Dell have decimated their R&D, so they are forced to partner/acquire. At the same time, if Maritz’s vision of public clouds becoming effectively excess capacity for handling peaks from corporations is realized, then elasticity in the cloud will become critical as well. This obviously plays to ScaleDB’s strengths.
Tue, 31 Aug 2010 17:43:30 +0000 - Last week I followed an very interesting ORACLE webinar delivered by Chris Mason : The State of Btrfs File System for Linux
BTRFS was initiated by Chris Mason who used to be responsible for Reiserfs at Suse and now works for Oracle. The first release started in 2007. BTRFS has been merged into Linux kernel in 2009. Now there are developers from REDHAT, INTEL SUSE, IBM, HP ... storage vendors. The project is very active. Ubuntu is considering to use it soon as its default filesystem. BTRFS is licensed under the GPL license. An interesting to read short summary of the life of BTRFS : A short history of BTRFS
...
Tue, 31 Aug 2010 12:47:23 +0000 - To get information from a database it is necessary to execute a query to get this data.
Usually an ordinary SQL editor is used to create queries. To use such editor, one should remember the syntax of the SELECT operator and the names of tables and columns.
Let’s use a visual instrument developed specially to design queries, and see that it’s much easier to create queries visually instead of typing them in an editor.
Task:
It’s necessary to show the salaries of the employees of departments situated in different cities for the 2008 year in descending order.
We will do this on a MySQL server database. The process of creating this database was described in the How to: Create MySQL Database in One Shot article. You can Download MySQL Demo Database (or for SQL Server Download SQL Server Demo Database).
Solution:
Let’s create an empty document in dbForge Query Builder for MySQL (dbForge Query Builder for SQL Server).
After this let’s drag tables from Database Explorer to the diagram, the order of tables during dragging doesn’t matter.
As we can see, the application joins these tables automatically.
Query Builder: Query Diagram
Now let’s select the columns you need to get data from.
Click the checkbox near the Loc column of the dept table on the diagram, and after that the SalAmount column of the sal table.
You can see the selected columns on the Selection tab.
Query Builder: Selection Tab
Now let’s select the sum function on this tab in the column with aggregate functions for the SumAmount column.
Query Builder: Aggregate
Now it is necessary to set grouping by the Loc column, but the application selected to group data by the Loc column automatically. Let’s make sure of that by going to the Group By tab.
Query Builder: Group By Tab
Now we should cut the selection and keep only data of the 2008 year in the result.
To do this, let’s go to the Where tab and click the button with the green plus on it. The “=” symbol should appear. Let’s click the first phrase – <enter a value>.
Query Builder: Enter Value
After this the Operand Editor form should appear. Let’s select the Date and Time group from the Function list and double click the year(date) function in the list. After that let’s choose and double click the SalDate column in the other list.
Query Builder: Operand Editor
Let’s close the form and click the second one. Let’s enter 2008 there.
Query Builder: Type Constant
It’s time to execute the query we’ve created visually. To do this, let’s click F5.
Query Builder: Query Result
Now let’s look at the structure of the query we’ve created. To do this, let’s open Document Outline and open all nods.
Query Builder: Document Outline
Now let’s look at the DML of the created query. To do this, let’s go to the Text tab.
Query Builder: SELECT Query
Conclusion:
As we can see, the usage of a visual instrument for building queries allows to solve the task visually without going deep into the refinements of syntax of the SELECT statement itself and of the specifics connected with differences between MySQL and SQL Server syntax, to look at the syntax of the created query, to decrease the duration of the data selection process, and to look at the structure of the available query as a tree.
Tue, 31 Aug 2010 03:40:02 +0000 - Quick solution to an issue that the affected Debian Lenny release: the process used to collect the MEMORY_ACTIVE_BYTES variable has been modified to correct a situation where some systems report an array of memory information instead of the expected single integer value. The bug has been fixed in revision 21 and the current download (revision 22) is available for download or svn update. As usual, you can download the MySQL dbbenchmark script here: [downloads].
Thanks goes to Brian Vowell at Evernote.com for bringing this bug to my attention. The original bug report can be found here: [link]
Mon, 30 Aug 2010 21:04:24 +0000 - There are many times when writing an application that single threaded database operations are simply too slow. In these cases it’s a matter of course that you’ll use multi-threading or forking to spawn secondary processes to handle the database actions. In this simple example for Python multi-threading you’ll see the how simple it is to improve the performance of your python app.
#!/usr/bin/python
## DATE: 2010-08-30
## AUTHOR: Matt Reid
## WEBSITE: http://themattreid.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
## Copyright 2010-present Matt Reid
from __future__ import division
from socket import gethostname;
import threading
import sys
import os
import MySQLdb
class threader(threading.Thread):
def __init__(self,method):
threading.Thread.__init__(self)
self.tx =
self.method = method
def run(self):
run_insert()
def run_insert():
sql = "INSERT INTO table (`id`,`A`,`B`,`C`) VALUES (NULL,'0','0','0');")
try:
cursor.execute(sql)
db.commit()
except:
print "insert failed"
def init_thread():
for thread in range(threads):
background = threader()
background.start()
background.join()
def main():
try:
init_thread()
except:
print "failed to initiate threads"
sys.exit(0)
if __name__ == "__main__":
mysql_host = "localhost" #default localhost
mysql_pass = "pass" #default dbbench
mysql_user = "user" #default dbbench
mysql_port = 3306 #default 3306
mysql_db = "schema" #default dbbench
threads = 4 #must be INT not STR
try:
db = MySQLdb.connect(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db, port=mysql_port)
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
cursor = db.cursor()
main()
db.close()
Mon, 30 Aug 2010 20:52:42 +0000 - We had a very successful weekend of Planet.mysql users submitting their database statistics so I’ve pushed some code into a new release today so that everyone can benefit from some new features. The biggest change is to the threading logic. Previously the benchmarking script was serializing MySQL operations and only making use of a secondary thread (not the invoking thread) to query the database. Now you have the option of running with “–threads=x” to make use of your multi-core server. A good example of this improvement was on my Macbook Pro; before the threading change it was inserting ~700/sec, after the code change I tried –threads=4 and saw an improvement to ~900/sec. Rather significant.
Download the new script now and see how your server compares to the ones in the central database!
Mon, 30 Aug 2010 19:27:30 +0000 - We’re happy to announce the latest maintenance release of MySQL Connector/Net 6.2.4.
Version 6.2.4 maintenance release includes:
Enhancement that allows a procedure to be recreated with a different number of parameters
Fixes for 29 bugs
For details see http://dev.mysql.com/doc/refman/5.1/en/connector-net-news-6-2-4.html
MySQL Connector 6.2.4 :
Provides secure, high-performance data connectivity with MySQL.
Implements ADO.NET interfaces that integrate into ADO.NET aware tools.
Is a fully managed ADO.NET driver written in 100% pure C#.
Provide Visual Studio Integration
If you are a current user, we look forward to your feedback on all the new capabilities we are delivering.
As always, you will find binaries and source on our download pages.
Please get your copy from http://dev.mysql.com/downloads/connector/net/6.2.html.
To get started quickly, please take a look at our short tutorials.
MySQL Connector/NET Tutorials
http://dev.mysql.com/doc/refman/5.1/en/connector-net-tutorials.html
Blog postings and general information can be found on our Developer Zone site.
MySQL Developer Zone
http://dev.mysql.com/usingmysql/dotnet/
.NET Forum
http://forums.mysql.com/list.php?38
Blog
http://planet.mysql.com
Connecotor/NET Documentation and details on changes between releases can be found on these pages
http://dev.mysql.com/doc/refman/5.1/en/changes-6.2.x.html
http://dev.mysql.com/doc/refman/5.1/en/connector-net.html
http://dev.mysql.com/doc/refman/5.1/en/connector-net-news-6-2-4.html
If you need any additional info or help please get in touch with us by posting in our forums or leaving comments on our blog pages.
Mon, 30 Aug 2010 17:59:52 +0000 - TweetThe “DATA”
“The wonderful thing about standards is that there are so many of them to choose from.” – Grace Hopper
A Shape file is the most common format for GIS vector data and just about every GIS program can use them. Unfortunately not all GIS data come in a shape file format. An E00 (E-zero-zero) file is the file format used by Environment Systems Research Institute’s (ESRI). ESRI is the Photoshop of the GIS workplace. E00 files are used by ArcInfo and ArcGIS Explorer. These are the two most common file formats. Spatial Data Transfer Standard (SDTS) is used by the US government to transfer data between dissimilar computer systems and GPS (.gpx) data used to transfer data to and from a GPS device.
Here is a list of the vector and raster file formats I have come across.
.shp/.shx/.dbf Shapefile – vector data (All three files make up a set.)
.E00 Proprietary ESRI files.
.klm Keyhole markup language used by Google Earth (GE)
.gpx/ .gps a common GPS data format for software applications
.gml Geographic markup language
.pdb PocketAPRS vector map
.map APRSdos/WinAPRS/MacAPRS vector map
.gnis GNIS labels file (actually points instead of vectors)
.tif/.fgd geo TIFF raster image maps
Geospatial Data Abstraction Library (GDAL) is the work horse utility convert between these formats.. It is really a set of utilities and is the guts of the MapServer. The program ogr2ogr is included in GDAL. This program is key to converting and getting data into MySQL. The OGR toolkit is a sub kit of the FW Tools Toolkit. It has several command line tools. The two most useful are:
ogrinfo – inspects a GIS data source and spits out summary data or detailed information about the layers, kinds of geometries found in the file.
ogr2ogr – this is a command line tool that converts data. Ogr supports many formats: Shapefile, MapInfo Tab file, TIGER, s57, DGN, CSV, DBF, GML, KML, Interlis, SQLite, ODBC, ESRI GeoDatabase (MDB format), PostGIS/PostgreSQL, MySQL
Like the unix ‘file’ command, these utilities can recognize the type of data you feed them. One odd thing about ogr2ogr is, its command options are in output file then the input file order.
Here are a couple of useful examples from a cheat sheet on these utilities.
# Again this is based on Redhat 5.5
yum install gdal
ogrinfo ../data/Oklahoma/oklahoma_poi.shp
INFO: Open of `../data/Oklahoma/oklahoma_poi.shp'
using driver `ESRI Shapefile' successful.
1: oklahoma_poi (Point)
wget http://mappinghacks.com/data/TM_WORLD_BORDERS-0.2.zip
mkdir boundaries_shp
unzip TM_WORLD_BORDERS-0.2.zip -d boundaries_shp
cd boundaries_shp
ogr2ogr -f "MySQL" MySQL:"geo,user=root,host=localhost,password=" -nln world_borders -lco engine=MYISAM TM_WORLD_BORDERS-0.2.shp
mysql geo -e 'select iso3,astext(SHAPE) from world_borders where iso2="VA";'
+------+--------------------------------------------------------------------+
| iso3 | astext(SHAPE) |
+------+--------------------------------------------------------------------+
| VAT | POLYGON((12.445 41.903,12.451 41.907,12.456 41.901,12.445 41.903)) |
+------+--------------------------------------------------------------------+
In part one I said “GIS / Mapping Systems work with both text data and graphical data.” There are even more raster graphical file formats. GDAL has a library for raster GEO data types with a very large list of file formats it supports. Besides converting format types, GDAL can also warp, stitch and re-project images to fit a give projection. These blog posts are about MySQL so I will leave the conversion of graphical data to someone else.
In a coming posts I’ll go over:
What data is available and where can you find it?
More examples on what you can do with GIS data and MySQL.
Viewing our GIS data.
How to collect your own GIS data.
Good and bad examples of searching GIS data.
Optimizing MySQL GIS. Is it really worth using?
Mon, 30 Aug 2010 16:43:49 +0000 - MySQL Connector/Net 6.1.5, a new version of the all-managed .NET driver for MySQL has been released. This is a maintenance release of the 6.1 branch and is suitable for use with MySQL server versions 5.0 and higher. It is now available in source and binary form from http://dev.mysql.com/downloads/connector/net/6.1.html] and mirror sites (note that not all mirror sites may be up to date at this point of time – if you can’t find this version on some mirror, please try again later or choose another download site.)
This is maintenance release. Please review the change log (included with product or at http://dev.mysql.com/doc/refman/5.1/en/connector-net-news-6-1-5.html) for details.
Thank you for working with MySQL!
Mon, 30 Aug 2010 16:37:27 +0000 - MySQL Connector/Net 6.0.7, a new version of the all-managed .NET driver for MySQL has been released. This is a maintenance release of the 6.0 branch and is suitable for use with MySQL server versions 5.0 and higher. It is now available in source and binary form from http://dev.mysql.com/downloads/connector/net/6.0.html] and mirror sites (note that not all mirror sites may be up to date at this point of time – if you can’t find this version on some mirror, please try again later or choose another download site.)
This is maintenance release. Please review the change log (included with product or at http://dev.mysql.com/doc/refman/5.1/en/connector-net-news-6-0-7.html) for details.
Thank you for working with MySQL!
Mon, 30 Aug 2010 16:35:00 +0000 - I'm pleased to announce the release of Spider storage engine version 2.22(beta), Vertical Partitioning storage engine version 0.11(beta) and BKA-for-ha_partition version 0.3.Spider is a Storage Engine for database sharding.http://spiderformysql.com/Vertical Partitioning is a Storage Engine for vertical partitioning for a table.http://launchpad.net/vpformysqlBKA-for-ha_partition is a patch file of supporting "Batched Key Access" for table partitioning feature.http://launchpad.net/partitionmrrformysqlThe main changes in this version are following.Spider- MS Windows support.- Add UDF "spider_copy_tables". Spider HA feature is now available!Vertical Partitioning- MS Windows support.Pathced MySQL source code and compiled MySQL binary are available from this release.Please see "99_change_logs.txt" in the download documents for checking other changes.Thanks to Toru for working for supporting Windows.Enjoy!
Mon, 30 Aug 2010 11:42:22 +0000 - Just in case it wasn't clear from Hakan's post, we are opening up the next Monty Program company meeting in October 7-12 to be a general MariaDB developers meeting. (In fact, we've had a few guests in all of the previous meetings too, but now it's formal and public.) Ever since Sun folded this annual MySQL AB tradition (to save money) there has been people asking when the next meeting would be, since for the developer community outside MySQL AB it was the main networking and information sharing event of the year. Last MySQL user conference we agreed that something needs to be done, and this is it. If you work on any of the MySQL variants, a storage engine, or are otherwise interested in deep architectural MySQL/MariaDB discussions, you are welcome to join and should contact Hakan or My for details.1 The invitation of course also is valid for Oracle employees, in case you were wondering.
And that's the other significant news hiding in Hakan's post: My Widenius, an experienced traveller herself, will be in charge of meeting logistics. This means even My herself now works for MariaDB, and can be reached with my at mariadb dot org :-)
1. If you are a Drizzle hacker, it is probably less interesting, but you are of course still welcome if you come! Anyway, we did also discuss having a similar developer meeting adjacent to an OpenSQL camp or something that could cover broader topics, and this is still an option. But as it is now, this is a MySQL/MariaDB focused meeting and there already was a separate Drizzle Developer day and we can just see what else is needed.
read more
Mon, 30 Aug 2010 09:54:51 +0000 - This has really been a long debate as to which approach is more performance orientated, normalized databases or denormalized databases. So this article is a step on my part to figure out the right strategy, because neither one of these approaches can be rejected outright. I will start of by discussing the pros and cons of both the approaches.
Pros and Cons of a Normalized database design.
Normalized databases fair very well under conditions where the applications are write-intensive and the write-load is more than the read-load. This is because of the following reasons:
Normalized tables are usually smaller and have a smaller foot-print because the data is divided vertically among many tables. This allows them to perform better as they are small enough to get fit into the buffer.
The updates are very fast because the data to be updated is located at a single place and there are no duplicates.
Similarly the inserts are very fast because the data has to be inserted at a single place and does not have to be duplicated.
The selects are fast in cases where data has to be fetched from a single table, because normally normalized tables are small enough to get fit into the buffer.
Because the data is not duplicated so there is less need for heavy duty group by or distinct queries.
Although there seems to be much in favor of normalized tables, with all the pros outlined above, but the main cause of concern with fully normalized tables is that normalized data means joins between tables. And this joining means that read operations have to suffer because indexing strategies do not go well with table joins.
Now lets have a look at the pros and cons of a denormalized database design.
Pros and cons of denormalized database design.
Denormalized databases fair well under heavy read-load and when the application is read intensive. This is because of the following reasons:
The data is present in the same table so there is no need for any joins, hence the selects are very fast.
A single table with all the required data allows much more efficient index usage. If the columns are indexed properly, then results can be filtered and sorted by utilizing the same index. While in the case of a normalized table, since the data would be spread out in different tables, this would not be possible.
Although for reasons mentioned above selects can be very fast on denormalized tables, but because the data is duplicated, the updates and inserts become complex and costly.
Having said that neither one of the approach can be entirely neglected, because a real world application is going to have both read-loads and write-loads. Hence the correct way would be to utilize both the normalized and denormalized approaches depending on situations.
Using normalized and denormalized approaches together.
The most common way of mixing denormalized and normalized approaches is to duplicate related columns from one table into another table. Let me show you by example:
Suppose you have a products table and an orders table.
The normalized approach would be to only have the product_id in the orders table and all the other product related information in the products table.
But that would make the query that filters by product_name and sorts by order_date inefficient because both are stored in different tables.
In a fully normalized schema, such a query would be performed in the following manner:
SELECT product_name, order_date
FROM orders INNER JOIN products USING(product_id)
WHERE product_name like 'A%'
ORDER by order_date DESC
As you can see MySQL here will have to scan the order_date index on the orders table and then compare the corresponding product_name in the products table to see if the name starts with A.
The above query can be drastically improved by denormalizing the schema a little bit, so that the orders table now includes the product_name column as well.
SELECT product_name, order_date
FROM orders
WHERE product_name like 'A%'
ORDER by order_date DESC
See how the query has become much simpler, there is no join now and a single index on columns product_name, order_date can be used to do the filtering as well as the sorting.
So can both the techniques be used together? Yes they can be, because real word applications have a mix of read and write loads.
Final words.
Although, denormalized schema can greatly improve performance under extreme read-loads but the updates and inserts become complex as the data is duplicate and hence has to be updated/inserted in more than one places.
One clean way to go about solving this problem is through the use of triggers. For example in our case where the orders table has the product_name column as well, when the value of product_name has to be updated, then it can simply be done in the following way:
Have a trigger setup on the products table that updates the product_name on any update to the products table.
Execute the update query on the products table. The data would automatically be updated in the orders table because of the trigger.
However, when denormalizing the schema, do take into consideration, the number of times you would be updating records compared to the number of times you would be executing SELECTs. When mixing normalization and denormalization, focus on denormalizing tables that are read intensive, while tables that are write intensive keep them normalized.
Mon, 30 Aug 2010 04:27:05 +0000 - This weekend, I decided to attend BarcampJB pretty last minute. Lucky for me, barcamps are made for chaotics like me, so it was no problem at all. I found some friends that live here in Kuala Lumpur who I drove down to JB with (JB is around a 5 hour drive from KL, we did it in 3.5 ).
The camp was very interesting. Because JB is on the border with Singapore, there’s a good crossover between Malaysian and Singaporean techies.
I decided to go all out and give three talks on Saturday: First up was the MMM talk I’ve given at a few conferences before. All went well, and later on in the day some people approached me for more in-depth questions. It still seems that people have this idea in their head that they somehow need MySQL Cluster when there is more then one machine involved. When I explain them that that is very rarely the case and they can achieve what they want with MMM as well, they are often happy to hear that.
My next talk was more of a personal development one. People keep asking me here where I am from. When I explain to them that I’ve been location independent for the last 3 years, they are usually very eager to find out how I pull that off. I decided to summarise my experiences and put them in a talk. This talk was very well attended and I loved giving it. Most of the attendants were young techies, they are usually in a perfect position to do something very similar to what I’m doing.
The last talk was a lightning talk on Zabbix, the Open Source monitoring system we use at Open Query. Quick, and dirty, but effective.
Other interesting talks I attended were on breeze, an online banking application made for Standard Chartered bank that looks very slick and usable (If anyone from my bank is reading this: get with the program and fix our banking application to enter the 21st century please ).
Conary and Foresight Linux were interesting as well. Conary (the package management system in Foresight Linux) is not quite mature yet, but definitely a very interesting technology. I was interested to hear about it and hope to see it become more mainstream in the future.
Daniel Cerventus gave a good lightning talk on what not to do as a startup. The main message was to just do it, and not wait for grant money or VC’s. Some solid tips as well, one of them being to run your potential name through Namechk, a handy potential username checker for many services.
There was obviously also a lot of networking and we went for a foot massage at the end of the day. Funny fact: I was the only one to stay awake through the massage (Even though I am narcoleptic), while two of my friends (who I won’t name here ) snored all the way through it
All in all another succesful tech event in Malaysia. Definitely one of the many reasons I love living here!
Sun, 29 Aug 2010 16:41:35 +0000 - How To Set Up MySQL Database Replication With SSL Encryption On
Debian Lenny
This tutorial describes how to set up database replication in MySQL
using an SSL connection for encryption (to make it impossible for
hackers to sniff out passwords and data transferred between the master
and slave). MySQL replication allows you to have an exact copy of a
database from a master server on another server (slave), and all updates
to the database on the master server are immediately replicated to the
database on the slave server so that both databases are in sync. This is
not a backup policy because an accidentally issued DELETE command will
also be carried out on the slave; but replication can help protect
against hardware failures though.
Sun, 29 Aug 2010 04:47:43 +0000 - Just a quick note to let everyone know that our new benchmarking script now supports OSX 10.6 on Intel hardware. That means you can run one simple command and get all of the sequential and random INSERT and SELECT performance statistics about your database performance. As usual the script is open source and released under the new BSD license. Give is a try by downloading now! See the download page for more details.
Sat, 28 Aug 2010 14:22:54 +0000 - Do you ever stop and think? It happens to me all the time, random subjects kick in and I end up searching (aka googling) and today I wondered … Why is MySQLs logo a dolphin? Shame on me it took me so long to ask the question but as the saying goes, better late than never.
As per MySQLs own article:
“We’ve been discussing a new logo for at least a year”, says main developer and MySQL AB founder Michael “Monty” Widenius. “I am personally concerned about the survival of endangered species, and I liked the idea of the dolphin as soon as it came up. It combines great symbol value with a powerful, modern design.”
After a while the name was chosen out of 6357 suggestions by the community and that is how Sakila the dolphin came to be :) …. and we lived happily ever after (or at least hope to!)
Sat, 28 Aug 2010 13:37:53 +0000 - MySQL 5.1 has a great feature which not many people know about, that is the fact it can be extended via. the use of plugins. Unfortunately how you go about this is not incredibly well documented. You can search for examples on the internet, dig through the MySQL source code and ask on the forums and you may figure it all out. But doing all this is time consuming and could easily put someone off. So Sergei Golubchik and I have got together to bring you this book which will show you, using examples, how to write your own plugins.
We start by explaining the UDF API which has been around for a long time, and then move on to Daemon Plugins, Information Schema Plugins, Full-text Search Plugins and Storage Engine Plugins. Each with usable examples.
MySQL 5.1 Plugins Development has just been published by Packt Publishing and I believe it is well worth a look if you are thinking of extending MySQL.
Sat, 28 Aug 2010 00:34:09 +0000 - You can download the first release of the benchmarking script here: http://code.google.com/p/dbbenchmark/
Please read the README file or consult the Support page before running the benchmarks.
Sat, 28 Aug 2010 00:26:21 +0000 - Welcome to DBbenchmarks.com, a publicly accessible database that tracks anonymously submitted data about MySQL server performance. You can use this site to see research the performance of certain types of hardware when running MySQL. Our open-source benchmarking script is free to own and use, we only ask that you allow the script to connect to this database and submit the results. All results and data collected is anonymous and viewable on this site. We only track performance data from MySQL – you can see the list on the About page.
Check out the database of benchmarks here: [link]
Fri, 27 Aug 2010 20:29:32 +0000 - It’s that time of the year again — Oracle OpenWorld time — and it’s my pleasure to announce our regular Oracle bloggers meetup again this year. We all know that Oracle community has grown this year so we expect to see folks from all the different technologies including MySQL, Java, Sun hardware folks in addition to the core Oracle database and apps crowd.
So… all of you Oracle bloggers attending Oracle Open World 2010…
… you are invited to attend this Oracle Bloggers Meetup during OOW 2010 — a chance to meet your online buddies face-to-face in relaxed and informal atmosphere.
When: Wed, 22-Sep-2010, 5:30pm
Where: Lower Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103.
View Larger Map
Street view:
View Larger Map
See the “Lower Dining Room” on the floor plan below and ask where is the “Bloggers Meetup” booked under my name — Alex Gorbachev. These are the keywords to find us easily.
The plan is to gather at 5:30pm on Wednesday after three (or four for those of us starting on Sunday) days of intense learning. This year, you won’t need to find where to kill few hours in between of the OOW sessions and customer appreciation event at the Treasure Island — the best place to be this year is our bloggers meetup — the place where all the “cool kids” are.
As usual, thanks to Oracle Technology Network and Pythian for sponsoring the venue and drinks. HP is planning to establish a prize again this year for something fun… yes, we will again do something fun.
Last year, we were collecting signatures on our Bloggers Meetup T-Shirts so feel free to wear them this year to show your seniority at the event. ;-) This year’s activity is a surprise but if you have something cool in mind — let me know privately {last_name} at pythian.com.
For those of you who don’t know the history… The Bloggers Meetups during the Oracle Open World were started by Mark Rittman and continued by Eddie Awad and then I picked up the flag. They have been great success so let’s keep them this way! To give you an idea, here are the photos from the OOW08 Bloggers Meetup (courtesy of Eddie Awad) and last year’s meetup blog post update from myself.
If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us make sure we have the attendance numbers right. I will maintain the list here. Make sure you provide your blog URL with your comment — it’s a Bloggers Meetup in the end! Make sure you comment here if you are attending so that we have enough room, food and (most important) drinks.
Of course, do not under any circumstances forget to blog and tweet about this year’s bloggers meetup.
Looking forward to seeing all of you again this year!
Who is coming:
1. Alex Gorbachev
2. Vanessa Simmons
3. Marc Fielding
4. Arup Nanda
5. Kevin Closson
6. John Piwowar
7. Asif Momen
8. Arjen Visser
9. Kamran Agayev
10. Rob van Wijk
11. Karl Arao
12. Markus Eisele
13. Riyaj Shamsudeen
14. Mohan Dutt aka OCP Advisor
15. Jacco Landlust
16. Marco Gralike
17. Chet Justice
18. Eric Jenkinson
19. Fuad Arshad
20. Meg Bear
21. Jake Kuramoto
22. Rich (AppsLab)
23. Anthony (AppsLab)
24. Michael Aldrich
25. Sheeri K. Cabral
26. Iggy Fernandez
27. Martin Nash
28. Richard Foote
29. Espen Barroso-Gomez
Fri, 27 Aug 2010 17:58:28 +0000 - Stuttgart: a rainy day, waiting for Iftar. Good time for good news!
During my vacation I read about a request for a MariaDB package for Mac OS X and did some research. Back from vacation I have an alpha version of MariaDB package for Mac OS X for our community to test.
Caution: this is the first installer I ever wrote on a Mac, so use it on a test system only!
I would like you to test the installer and provide us with feedback.
Known issues in the MariaDB installer:
The Preferences Pane app for starting/stopping the server instance is missing
The installer for setting up MariaDB as a Startup Item is missing.
Side node: while digging into the Mac installer I found two
bugs in the MySQL Mac OS X installer.
http://bugs.mysql.com/bug.php?id=56279
Mac installer does not work as documented
http://bugs.mysql.com/bug.php?id=56280
Mac installer’s postflight script does not work all the time
You can grab the package from here:
http://lisas.de/~hakan/file/mariadb-5.1.49-osx10.6-x86.pkg
Fri, 27 Aug 2010 13:36:47 +0000 - TokuDB has a big advantage over B-trees when trickle loading data into existing tables. However, it is possible to preprocess the data when bulk loading into empty tables or when new indexes are created. TokuDB release 4 now uses a parallel algorithm to speed up these types of bulk insertions. How does the parallel loader performance compare with the serial loader? We use the Air Traffic Control (ATC) data and queries described in a Percona blog and also used in an experiment with TokuDB 2.1.0 to gain some insight.
Our ATC data is about 122M rows in size, is stored in a 40GiB CSV file, and can be found in our Amazon S3 public bucket. See the end of this blog for details. We use a table schema with 8 indices to speed up the ATC queries. The loader inserts the data into the primary fractal tree and one fractal tree for each of the 8 keys.
The load was run on two (old) machines:
2 core: Intel Core 2 Duo E8500 3.16GHz, 5GiB RAM, 1 disk SATA 1TB.
8 core: 2 socket quad core Xeon X5460 3.16GHz, 16GiB RAM, 6 disk SAS 1TB RAID0.
Load times:
Load Times for the ATC Database
TokuDB 2.1.0 and MySQL 5.1.36
TokuDB 4.1.1 and MySQL 5.1.46
TokuDB Speedup
2 core
10,974s
5,201s
2.1x
8 core
11,286s
2,655s
4.2x
We use “LOAD DATA INFILE” to load the CSV file into a MySQL table. The CSV file can be found in our Amazon S3 public bucket.
We see over 4x load time speed up for this database. This compares favorably with the other loaders. The next steps are to run experiments on machines with a larger number of cores, identify bottlenecks, and remove them.
BTW, the ATC load times into MyISAM are roughly the same as seen with the TokuDB 2.1.0 serial loader. Perhaps loads into MyISAM can be made faster with some twiddling of the MySQL system variables.
TokuDB data sizes (including indices):
6.7 GiB, or almost 6 times smaller than the CSV source file. The parallel loader uses the same compression parameters as the serial loader, so no change in size occurred.
Query times:
Query Times for the ATC Database
TokuDB 2.1.0
TokuDB 4.1.1
Q0
22s
28s
Q1
67s
33s
Q2
20s
24s
Q3
54s
29s
Q4
2s
1s
Q5
11s
7s
Q6
35s
29s
Q7
32s
39s
Q8.1y
7s
4s
Q8.2y
42s
32s
Q8.3y
37s
33s
Q8.4y
38s
35s
Q8.10y
139s
56s
Q9
36s
35s
TokuDB 4 uses the standard MySQL handler row iterator, so the query times are roughly unchanged. In addition, the query handling is not yet parallelized, so there is not much difference in query times on the 2 core and 8 core machines.
BTW, the query times for MyISAM are slightly longer than TokuDB.
The queries can be found in our Amazon S3 public bucket.
Future blogs:
Loader scalability on systems with larger number of cores. Machines with 48 cores (4 socket, 12 cores per socket) are now reasonably priced.
How we implemented the parallel loader: algorithms and parallel runtime.
The ATC CSV data files, the schema, and the queries can be retrieved from our public Amazon S3 bucket called
tokutek-pub. Here are the Amazon S3 keys:
atc_On_Time_Performance.mysql.csv.gz.aa
atc_On_Time_Performance.mysql.csv.gz.ab
atc_On_Time_Performance.mysql.csv.gz.ac
atc_On_Time_Performance.mysql.csv.gz.ad
atc_On_Time_Performance.mysql.csv.gz.xml
atc_ontime_create_covered.sql
atc_ontime_create_covered.sql.xml
atc_ontime_queries.tar.gz
atc_ontime_queries.tar.gz.xml
s3get
s3get.xml
Fri, 27 Aug 2010 03:47:27 +0000 - Open Query now has its own @openquery account on Twitter and Identi.ca so you can conveniently follow us there for announcements and tips – and also ask us questions! All OQ engineers can post/reply. The OQ site front page also tracks this feed.
Previously I was posting from my personal @arjenlentz account with #openquery hashtag, but that’s obviously less practical.
SQL Server Hosting Toolkit The goal of the SQL Server Hosting Toolkit is to enable a great experience around SQL Server in shared hosting environments. The toolkit will eventually consist of a suite of tools and services that hosters can deploy for use by their customers. It will also serve as an incubation vehicle for tools that hosting customers can download and use directly, regardless of whether their hoster has deployed the toolkit. See the
Project Roadmap for details on where we're going.
MySQLMan is a web based database manager. It allows you to perform common maintenance and administration tasks in Mysql (Mysql is a great mostly-free SQL database server).
MySQLMan was based off of
phpMyAdmin, but written in
Perl. It allows you to do common tasks like: browse/create/drop databases; browse/search/create/drop/alter tables; import/export data; add/remove/alter table; columns; add/remove/alter table keys, etc...
XAMPP is an easy to install
Apache
distribution containing MySQL, PHP and
Perl. XAMPP is really very easy to install and to use - just download, extract and start. From
Apache Friends
Toad for MySQL empowers MySQL developers and administrators develop code more efficiently. It also provides utilities to compare, extract and search for objects, manage projects, import/export data and administer the database. Toad for MySQL increases developer productivity and offers access to a solid community of experts and peers for interactive support.
Sphinx (SQL
Phrase
Index), Free open-source SQL
full-text search engine. Provides fast, size-efficient and relevant fulltext search functions to other applications. Sphinx was specially designed to
integrate well with SQL databases and scripting languages. Currently built-in data sources support fetching data either via direct connection to MySQL or
PostgreSQL, or using XML pipe mechanism.
Syphix Free open-source SQL full-text search engine. As we know build in full text search is currently limited only to
MyISAM search engine as well as has few other limits. Today Sphinx
Search plugin for MySQL was
released which now provides fast and easy to use full text search solution for all storage engines. This version also adds a lot of other new features, including boolean search and distributed searching.
e107 is a content management system written in
PHP and using the popular open source mySQL database system for content storage. It's completely free and totally customisable, and in constant development.
PhotoPost is written in highly optimized
PHP code and uses a lightning fast MySQL database backend. PhotoPost uses either
ImageMagick™ or the GD Graphics Library to resize uploaded images and create thumbnails. Chances are, your web host already has either ImageMagick or GD
installed on your server, so be sure to check with them if you don't know if you have one or both installed. More
Graphics, Graphics file formats Video and Images
phpBB is a high powered, fully scalable, and highly customizable Open Source bulletin board package. phpBB has a user-friendly interface, simple and straightforward administration panel, and helpful FAQ. Based on the powerful PHP server language and your choice of MySQL, MS-SQL, PostgreSQL or Access/ODBC database servers, phpBB is the ideal free community solution for all web sites.
Maatkit Tools for SQL. Makes MySQL easier and safer to manage. It provides simple, predictable ways to do things you
cannot otherwise do. It would be nice if these features were included with MySQL, but they are not. That's why Maatkit is now shipping by default with many
GNU/Linux distributions such as Debian and CentOS. You can use Maatkit to prove replication is working correctly, fix corrupted data, automate repetitive
tasks, speed up your servers, and much, much more. This is the older
MySQL Toolkit. This toolkit contains essential command-line utilities for MySQL, such as a table checksum tool and query profiler. It provides missing features such as checking. A set of essential tools for MySQL users, developers and administrators. The project®s goal is to make high-quality command-line tools that follow the
UNIX philosophy of doing one thing and doing it well. They are designed for scriptability and ease of processing with standard command-line utilities such
as Awk and Sed. slaves for data consistency, with emphasis on quality and scriptability.
Automatic MySQL Backup. Backup multiple MySQL databases with one script. (Now able to backup ALL
databases on a server easily. no longer need to specify each database separately). Backup all databases to a single backup file or to a separate
directory and file for each database. Automatically compress the backup files to save disk space using either gzip or bzip2 compression. Can backup remote MySQL
servers to a central server. Runs automatically using
cron or can be run
manually. Can e-mail the backup log to any specified e-mail address instead of "root". (Great for hosted websites and databases). Can email the
compressed database backup files to the specified email address. Can specify maximum size backup to email. Can be set to run PRE and POST backup commands.
Choose which day of the week to run weekly backups.
MySQL Dump Use MySQL Dump to backup your MySQL databases, both structure and
data. The script can be run from command prompt only. So, you can use
crontab or other system
scheduler to fully automate your data backup process. The script browse all databases that you select and write SQL statements for creating tables and
inserting data into files (one for each database). As an option the dumps may be compressed into zip archive. Finally output files stored into directory that you
selected.
Toad® for MySQL Freeware - Provides a comprehensive solution for MySQL professionals to create and execute queries, as well as build and manage database objects. You'll benefit from the project manager, the formatting feature, version control integration, the database browser, the security manager and an extensive knowledge base called Knowledge Xpert for MySQL.
Sql-Articles. This site is intended to produce articles related to sql server and its a free resource .All latest developments in the world of SQL Server will be available. Feel free to post your suggestion in the suggestion tab. You can also contribute any kind of Tips relating to SQL Server in the Tips tab. The sole purpose of starting this website is to create a knowledge base and help all the Newbies in SQL
MyAccess is an AddIn for
MS Access which allows you to manage MySQL databases from within
Access.
Partner-Developed Storage Engines. MySQL Partners are actively developing Storage Engines that are optimized for specific application domains:-
InnoDBInnobase Oy is the developer of InnoDB, the leading transactional storage engine for the MySQL open source database. Innobase also develops and distributes the InnoDB Plugin, Embedded InnoDB and InnoDB Hot Backup. Innobase, a subsidiary of Oracle, is a MySQL Enterprise Platinum partner.
Community-Developed Storage Engines. MySQL's community of open source developers are also developing Storage Engines that are optimized for specific application domains.:-
MySQL migration: MyISAM to InnoDB
Restrictions on InnoDB Tables Warning: Do not convert MySQL system tables in the mysql database from MyISAM
to InnoDB tables! This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or
re-generate them with the mysql_install_db script.
Scalable BLOB Streaming infrastructure for MySQL will transform MySQL into a scalable media server capable of streaming pictures, films, MP3 files and other binary and text objects (BLOBs) directly in and out of the database. On this site you will find all information relating to the ongoing activities of this project. The development is led by PrimeBase Technologies, Prime Base an open source software company.
Blob=(Binary Large Object) is a that is store the data as binary. Other types of data used in
a databases, for example numbers and strings, which store letters and numbers
blobs can be used to store images or other multimedia files because of the
binary type used. Note: They may often use more storage than other data types.
Protecting
Your PHP/MySQL Queries from SQL Injection SQL injection is a serious concern
for webmasters, as an experienced attacker can use this hacking technique to
gain access to sensitive data and/or potentially cripple your database. If you
haven't secured your applications, I implore you to get yourself familiar with
the following method and grind it into your coding routine. One unsafe query can
result in a nightmare for you or your client.
SQLServerCentral A Microsoft SQL Server community of DBAs, developers and SQL Server users
Green Computing, Data Centre Efficiency, Virtualization And The Cloud
There's been a lot of talk of late about green computing, how data centres are becoming more efficient, virtualization and particularly the idea that cloud computing is some sort of panacea. Yes, that maybe so. But you rarely hear anyone mention inefficient software being taken to task: it's the elephant in the room.
Doubtless most of you do this already, but if you don't you probably should at least consider it: install XCache. If you serve pages from Drupal or moreover with PHP you could, as I have, increase your PHP throughput by 167% for five minutes of effort.
The scourge of parallelism and scaling everywhere: locking. Or in MySQL/MyISAM — and to be more precise — table locks. Here's an overview of what to look out for and how one might go about reducing the frequency at which they occur.
Although there is nothing groundbreaking in this document consider it a bringing together of techniques for your first foray into optimization. We won't discuss the more esoteric methods of squeezing the very last millisecond out of MySQL. There are a myriad of parameters to tune: here's what you need to get right first.
SQL
Converter. Makes Databases Easy. Convert Excel to SQL in Minutes. SQL
Converter 2 for Excel makes databases easy. Start with your familiar Excel
spreadsheet and it will generate a MySQL database. Given any file that Excel can
read, SQL Converter for Excel will identify the header row and let you select
the best data-type for each column. For most files, you can have a MySQL
database within three minutes.
Device Tools is a comprehensive and free portal, aimed at
providing engineers who develop connected devices all the information needed to
make their next design a success. Covers
MySQL Databases and low level and high level coding.
LAMP is an acronym for a set of free software programs commonly used together to
run dynamic Web sites: Linux, the operating
system; Apache, the web server; MySQL, the database management system (or database
server); Perl,
PHP, and/or Python, scripting languages.
Host Library Tutorial is designed to guide you through the initial steps of
setting up Apache, MySQL, and PHP on Linux.
Senna is an embeddable fulltext search engine, which you can use in conjunction with various scripting languages and databases. Senna is an inverted index based engine,
and combines the best of n-gram indexing and word indexing to achieve fast, precise searches. While senna codebase is rather compact it is scalable enough to handle large amounts of data and queries.
SPARQL (pronounced "SPARkLe"). SPARQL is the query language for the Semantic Web (see Semantic Web use cases). SPARQL queries hide the details of data management, which lowers costs and increases robustness of data integration on the Web. SPARQL Query Language for RDF, SPARQL Protocol for RDF, and SPARQL Query Results XML Format. More XML, Extensible Markup Language
MySQL backup,compress and FTP from WinForms app. From your Windows Forms
application, implement two menu commands a) backup entire MySQL database,
compress it and send it with FTP to a ftp server b) reverse = fetch from FTP
server, uncompress, restore to MySQL
SiteVault functions as an FTP files and MySql backup program that will allow you
to browse your backups and restore them with ease. It will be an excellent FTP
program that will allow you to maintain as many connections as you wish, copy
between FTP servers, edit files remotely. It will do as many transfers as you
need simultaneously. It will also double as an awesome file manager and computer
explorer, it's network browsing being the fastest we've seen so far. It'll help
you keep your sites safe, your backups clean and your business running. The
program is meant for persons or organizations running one or multiple sites or
web developers that need to have their work safe.
Having the SSMS Tools Pack out in the wild enables me to get much “joy” from different bug reports people send me. And let me tell you, people send me back some seriously weird errors.
But the most unexpected error message I’ve seen so far was the OverflowException when calling System.Data.SqlClient.SqlDataReader.GetDecimal(Int32 i).
It turns out that SQL Server decimal data type is not mapped to the .Net decimal in it’s whole range. .Net decimal type only maps to SQL Server one in the decimal(29, X) range.
That means that if you have a decimal column that has the precision higher than 29 and no matter the scale, you won’t be able to use the native .Net data type.
So what to do? Let’s take a look with an example. The comments provide additional info.
SQL Code:
-- create a test table in tempdb with one valid and one invalid decimal mapping column. CREATETABLE TestTable ( [ID] [INT] IDENTITY(1,1) NOTNULL, [ValidDecVal] [DECIMAL](29, 2) NOTNULL, [InvalidDecVal] [DECIMAL](30, 30) NOTNULL ) GO
-- insert some data INSERT INTO TestTable(ValidDecVal, InvalidDecVal) -- both values are in correct range SELECT 123456789012345678901234567.56, 0.123456789012345678901234567890
C# Code:
privatevoid GetData() { using (SqlConnection conn = new SqlConnection(@"server=TestServer; database=tempdb; Integrated Security=SSPI;")) { using (SqlCommand cmd = new SqlCommand("SELECT ID, ValidDecVal, InvalidDecVal FROM TestTable", conn)) { conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
rdr.Read(); // get the id int id = rdr.GetInt32(0); // get the 29 precision value just fine decimal validDecimal = rdr.GetDecimal(1); // this errors out ... decimal invalidDecimal = rdr.GetDecimal(2); // so does this ... decimal invalidDecimal = rdr.GetSqlDecimal(2).Value;
// the only thing to do is to either pass around SqlDecimal class SqlDecimal invalidDecimalAsSqlDecimal = rdr.GetSqlDecimal(2); // or to cast it to string string invalidDecimalAsString = invalidDecimalAsSqlDecimal.ToString();
// ... do something with upper values Console.WriteLine(invalidDecimalAsSqlDecimal); Console.WriteLine(invalidDecimalAsString); } } }
If you’re working with large precision types I’d like to hear how you overcome this problem in .Net.
The only way I’ve found to deal with this is to either pass around the raw SqlDecimal data type or its string value.
A quick search revealed this Connect item that showed the problem with Linq2SQL. I don’t know why this isn’t fixed yet but I hope it will be soon. If you encounter this problem, vote it up.
Mon, 30 Aug 2010 11:32:11 GMT - Nothing earth-shattering here, I was just helping out a colleague with this so I thought I'd post up the example I gave him.
-- sample table: create table People ( Person varchar(1) primary key, City varchar(10), Age int )
go
-- with some sample data:
insert into People select 'A','Boston',23 union all -- odd # select 'B','Boston',43 union all select 'C','Boston',29 union all
select 'D','Chicago',15 union all -- single #
select 'E','NY',12 union all -- even # select 'F','NY',55 union all select 'G','NY',57 union all select 'H','NY',61
go
-- here's our query, showing median age per city:
select city, AVG(age) as MedianAge from ( select City, Person, Age, ROW_NUMBER() over (partition by City order by Age ASC) as AgeRank, COUNT(*) over (partition by City) as CityCount from People ) x where x.AgeRank in (x.CityCount/2+1, (x.CityCount+1)/2) group by x.City
go
-- clean it all up drop table People
And here's the result:
city MedianAge
---------- -----------
Boston 29
Chicago 15
NY 56
(3 row(s) affected)
Simply remove "City" from the SELECT clause and the GROUP BY clause to get the median age for all.
There may be more efficient tricks out there, but this is certainly the shortest and simplest technique I am aware of.
Here’s a blog from David Levy (Blog|Twitter) about the new window connection coloring using regular expression and why is it really useful to him. Thanx for kind words Dave.
Microsoft makes a pretty good OLE DB Provider for DB2 that you can use for SSIS, DTS, and linked servers under SQL Server. It's sometimes difficult to configure but you can get some good starter settings here.
In yet another remarkable but not uncommon blunder on their part, Microsoft's installation package will only install the provider on a server with Enterprise or Developer Edition. Why they do this I don't know, but if you have a Standard Edition SQL Server and want to use this provider, here's a workaround:
Install Enterprise or Developer Edition side-by-side on your Standard Edition server as a named instance, only the database engine components.
Install the DB2 OLEDB Provider.
Uninstall the side-by-side instance. Or keep it if you like it and have a valid license.
Notes:
I've only tested this with Standard Edition, but I believe it will work with any edition of SQL Server.
You may be able to use a trial version of SQL Server for the side-by-side install, I haven't tested it.
This technique also works for SQL Server 2000 instances, as long as your side-by-side instance is 2005 or higher
And deprive their user base with a ridiculous and unnecessary SKU limitation
Sorry MS if you don't agree, but DB2 integration is NOT an Enterprise-only feature, nor one that should cost $30K+ to get. If you really think so, then charge $30K for the provider, don't tease your customers with a freebie.
I added the parenthetical title because no one really cares if they can make their databases better. :)
Based on some recent tweets (one and two) and blogs I've been reading/writing, I thought I'd create an example of some excellent but underutilized features in SQL Server: defaults and user-defined types.
User-defined types (UDTs) are a way of renaming a standard SQL type and additionally providing a NULL or NOT NULL constraint on that type:
CREATE TYPE [USERNAME] AS varchar(64) NULL;
This can be used in a table definition like so:
CREATE TABLE [MyTable] [UserCreated] USERNAME;
It saves some typing, and I get an automatic NULL constraint on the column, which I can override with NOT NULL. (Normally I do the reverse; allowing nulls is not a "constraint")
Defaults are a deprecated feature that will be removed from SQL Server at a later date. Why? Because someone hates this:
And reduce my typing by over 60%. By the way, did you like how the datetime UDT, default definition, and binding all fit in a single 140-character tweet? I sure did!
I'll leave it up to you to decide if consistent, reusable data types with shorter names and identical defaults are worthwhile. And in case you're asking, yes, I've had developers argue AGAINST this very sentence.
Rules are another non-ANSI feature that SQL Server inherited from Sybase, and Microsoft also feels are A Bad Thing™ that should be deprecated. I don't have a good example right now but I should have one in a future blog soon. In the meantime you can read some of my thoughts in this forum post.
I can understand Microsoft wanting to remove Sybase cruft, especially if it's non-ANSI standard, but so far I've not heard of any new features to replace them, such as CREATE DOMAIN, which is ANSI standard and neatly combines types, defaults, and rules into one package.
If you feel this is a useful feature to have, and a bad one to lose, please visit Microsoft's Connect site and vote on this item. It shouldn't be that hard for them to implement, since they can just borrow the code from the open-source PostGreSQL project. ;)
I recently had to modify tables to add some auditing columns and triggers. Being a goodfair to middling crazed, insane DBA and wanting to ensure data integrity and good design, I made the columns not nullable, and therefore also had to add defaults for these new columns:
ALTER TABLE [dbo].[MyTable]
ADD [DateCreated] DATETIME CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) NOT NULL,
[DateModified] DATETIME CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) NOT NULL,
[UserCreated] VARCHAR (64) CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) NOT NULL,
[UserModified] VARCHAR (64) CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) NOT NULL;
Unfortunately by making them not nullable, the ALTER statement has to rewrite all the data pages to insert the default values. This kinda-sorta locked down the table(s) and made the database unresponsive for about, oh, 2 hours or so. (Stooooooooopid multi-million row tables!) It also had the side effect of making months worth of transactions appear as if they were created instantly by the same user. For some odd reason my boss felt neither circumstance was a good idea to deploy to production.
Happily the solution to both problems was to make the new columns nullable:
ALTER TABLE [dbo].[MyTable]
ADD CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) FOR [DateModified],
CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) FOR [UserCreated],
CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) FOR [UserModified];
The problem was turning the original, single statement into the 2 new statements, without doing it by hand. (Did I mention there were a few hundred tables in 8 different databases?) Playing around a bit I discovered that this syntax works perfectly:
ALTER TABLE [dbo].[MyTable]
ADD [DateCreated] DATETIME NULL, CONSTRAINT [DF_MyTable_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
[DateModified] DATETIME NULL, CONSTRAINT [DF_MyTable_DateModified] DEFAULT (getdate()) FOR [DateModified],
[UserCreated] VARCHAR (64) NULL, CONSTRAINT [DF_MyTable_UserCreated] DEFAULT (suser_sname()) FOR [UserCreated],
[UserModified] VARCHAR (64) NULL, CONSTRAINT [DF_MyTable_UserModified] DEFAULT (suser_sname()) FOR [UserModified];
Wow, that's pretty close to the original! (And it runs instantly since it only requires schema locks on the table) It's still annoying because I have to copy the new column name and paste it at the end of the DEFAULT definition.
Normally this is where I break out my 1337 4@X0r skillz and INFORMATION_SCHEMA and generate the SQL I need. Sadly the cruel, cold mistress named CHANGE CONTROL had other plans: we have to build from source and deploy with zero changes to the script. Minor changes are allowed but must be exactly repeatable/reproducible. (And also checked into source control, but that's for another time.)
If you've ever played with regular expressions (regex) you'll know that it's not hard to reformat the first statement into the last. If you haven't used regex before, or always get frustrated by them, hopefully this example will convince you to spend more time with them. SQL Server Management Studio has a find/replace feature that can use regex. Here's what I used to get the job done (each regex follows after the "-> "):
The Find expression breaks up each line into the following groups, using { and } to denote each:
1. All characters before left square bracket [ -> {.*}
2. All characters between (and including) square brackets -> {\[.*\]}
3. All characters between right square bracket ] and the word CONSTRAINT -> {.*}
4. The word CONSTRAINT -> {CONSTRAINT}
5. All characters between CONSTRAINT and NOT NULL -> {.*}
6. The words NOT NULL -> {NOT NULL}
7. All characters after NOT NULL -> {.*}
The Replace expression uses backreferences (\1, \2, etc.) to do the following:
1. Rebuild the first 3 captured groups exactly as before -> \1\2\3
2. Add a space and the word NULL -> NULL
3. Add a comma, space, and the word CONSTRAINT -> , CONSTRAINT
4. Rebuild the 5th captured group (all characters between CONSTRAINT and NOT NULL) -> \5
5. Add the FOR keyword and a space -> FOR
6. Add the 2nd captured group, which is the column name and its enclosing brackets -> \2
7. Add the remaining characters captured after NOT NULL -> \7
Try it out for yourself! You can, of course, do a lot more with regular expressions than this, but it's a good introduction for a relatively common problem. In case you're wondering, the find/replace ran in about 15-20 seconds, and the original 2 hour deployment now takes less than 1 minute.
One final note: if you've used regex in Perl or some other language, you'll probably wonder why I'm capturing groups with {} instead of the standard () characters. I wonder that too; my only explanation is Microsoft are idiotshate Unix and Perl people have their own unexplained reasons for using non-standard characters. It's not the first time.
When using the Visual Studio tools to create the Entity Data Model, it automatically builds the Connection String for you, and one of the default settings is to set the MultipleActiveResultSets option to True. If you change this setting to False, then Profiler will properly report that the query was executed within the application database.
Please note that I have not done any load testing to determine what type of performance impact this change might have, so YMMV. But for me, I know it will perform acceptably for our needs, and the greater need in my view is to have accurate Profiler results.
I have posted this work-around to the Connect item that I opened. I still believe that this is a bug in SQL Server and hope that Microsoft will address it in a future release. If you share my concern, I encourage you to login and vote this up.
This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant.
I've come up with a solution that need only 125 bytes of storage. All other solutions covered in Mr Celko's book has at least 1,000 bytes of storage.
Here is my solution, complete with all procedures to assign and release seats, together with views to display current status of each and one seat.
-- Setup sample data CREATETABLEdbo.Restaurant ( Seats BINARY(125)NOTNULL )
-- Initialize an empty restaurant INSERTdbo.Restaurant ( Seats ) SELECT0x GO
-- Create procedure for handling seat assignment CREATEPROCEDURE dbo.spAssignSeat ( @Seat SMALLINT ) AS
WITHL0AS(SELECT 1 AS c UNIONALLSELECT 1), L1AS(SELECT 1 AS c FROM L0 AS A CROSSJOIN L0 AS B), L2AS(SELECT 1 AS c FROM L1 AS A CROSSJOIN L1 AS B), L3AS(SELECT 1 AS c FROM L2 AS A CROSSJOIN L2 AS B), Nums AS(SELECTROW_NUMBER()OVER(ORDERBY c)- 1 AS Number FROM L3)
SELECTTOP(125) Number FROMNums ORDERBYNumber GO
-- Create view CurrentSeatings CREATEVIEW dbo.vwCurrentSeatings AS
SELECT8 * v.number + b.number + 1 AS Seat, SIGN(SUBSTRING(Seats, 1 + v.number, 1)&POWER(2, b.number))AS Taken FROMdbo.Restaurant AS s INNERJOINdbo.vwNums AS v ON v.Number BETWEEN 0 AND 124 INNERJOINdbo.vwNums AS b ON b.Number BETWEEN 0 AND 7 GO
-- Create the available seats sequence view CREATEVIEW dbo.vwAvailableSeats AS
WITH cteSource(Seat, Taken, grp) AS ( SELECTSeat, Taken, Seat -ROW_NUMBER()OVER (PARTITIONBY Taken ORDERBY Seat)AS grp FROMdbo.vwCurrentSeatings ) SELECTROW_NUMBER()OVER (ORDERBY grp)AS Sequence, MIN(Seat)AS FromSeat, MAX(Seat)AS ToSeat FROMcteSource WHERETaken = 0 GROUPBYgrp GO
-- Display the wanted result SELECTSequence, FromSeat, ToSeat FROMdbo.vwAvailableSeats ORDERBYSequence
A while back I wrote a CLR object to track database growth. The CLR object queries sp_databases for each passed in server and saves the data into a table. By using this CLR object, I can track all of my systems in one location. I blogged about this tool in this article.
A few weeks ago, I noticed that my SQL Agent job that runs this CLR object was failing. It was only failing on one of my database servers, so I ran sp_databases on that system to figure out what was going on. I noticed that for one database, the DATABASE_SIZE column was NULL. After doing some digging, I realized that sp_databases has a bug in it for any database that is 2TB in size or greater.
Sp_databases is using int data type for the DATABASE_SIZE column. Well that’s not big enough for a 2+TB database! A bigint should have been used instead. For backward compatibility reasons, Microsoft decided to display NULL for this situation rather than an error in SQL Server 2005 and 2008. In SQL Server 2000, it throws an error.
I found out that there is no bug fix for this, so I had two options. The first option would be to manually patch sp_databases on each of the servers. The second option would be to patch my CLR object. If I patched my CLR object, I could either create a new stored procedure, perhaps name it sp_databases2, or put the query directly into the C# code.
I didn’t like the option of patching sp_databases on potentially hundreds of servers even though I could easily deploy it using a batch file that calls sqlcmd. I don’t like the idea of modifying Microsoft’s code even though it’s a simple bug fix.
With the second option of patching my CLR object, I didn’t like the idea of creating a new stored procedure as that adds a dependency to my tool. So I instead decided to patch the CLR object by putting the correct query directly in the code.
You can download the new version here. Please reference the old article for how to call it, and especially how to call it for all of your servers. The old article also links to the new version of the code, since I didn’t change functionality.
Here’s the query in sp_databases that has a bug:
select
DATABASE_NAME = db_name(s_mf.database_id),
DATABASE_SIZE = convert(int,
case-- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...whenconvert(bigint, sum(s_mf.size)) >= 268435456
thennullelsesum(s_mf.size)*8 -- Convert from 8192 byte pages to Kbend),
REMARKS = convert(varchar(254),null)
from
sys.master_files s_mf
where
s_mf.state = 0 and-- ONLINE
has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have accessgroupby s_mf.database_id
orderby 1
And here’s the bug fix:
select
DATABASE_NAME = db_name(s_mf.database_id),
DATABASE_SIZE = convert(bigint, convert(bigint, sum(s_mf.size))*8),
REMARKS = convert(varchar(254),null)
from sys.master_files s_mf
where
s_mf.state = 0 and
has_dbaccess(db_name(s_mf.database_id)) = 1
groupby s_mf.database_id
orderby 1
I regularly move logins between servers. Mostly this is between production and our DR site. I’ve used the code in KB246133 many, many times but it’s pretty limited. I started with that and wrote the script below.
There is a user-defined function that’s created in master. You can create it in any database you want but you’ll need to update the script. The function converts varbinary hashed passwords to a string representation.
It keeps the password intact for SQL Server logins.
It scripts both Windows logins and SQL Server logins. It also scripts role membership.
It keeps the SID intact for SQL Server logins. This is important so you don’t have to remap users to logins.
The script that is generated uses IF NOT EXISTS so that it doesn’t try to create logins that already exist.
It DOES NOT handle removal of logins from roles. It does handle disabled accounts but I haven’t done much testing on that.
I’ve tested this on SQL Server 2005 and SQL Server 2008.
You’ll probably need to change your results so that you display more characters by default. Under Tools –> Options –> Query Results –> SQL Server –> Results to Text increase the maximum number of characters returned to 8192 (or a number high enough that the results aren’t truncated). You’ll want to set results to text before running this.
USE [master] GO
/****** Object: UserDefinedFunction [dbo].[fn_hexadecimal] ****/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATEFUNCTION [dbo].[fn_hexadecimal] ( -- Add the parameters for the function here @binvalue varbinary(256) ) RETURNSVARCHAR(256) AS BEGIN
DECLARE @charvalue varchar(256) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END return @charvalue
END GO
SET NOCOUNT ON GO --use MASTER GO PRINT'-----------------------------------------------------------------------------' PRINT'-- Script created on ' + CAST(GETDATE() ASVARCHAR(100)) PRINT'-----------------------------------------------------------------------------' PRINT'' PRINT'-----------------------------------------------------------------------------' PRINT'-- Create the windows logins' PRINT'-----------------------------------------------------------------------------' SELECT'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE [name] = ''' + [name] + ''') CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english] GO
' FROM master.sys.server_principals where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN') AND [name] notlike'BUILTIN%' and [NAME] notlike'NT AUTHORITY%' and [name] notlike'%\SQLServer%' GO
PRINT'-----------------------------------------------------------------------------' PRINT'-- Create the SQL Logins' PRINT'-----------------------------------------------------------------------------' select'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''') CREATE LOGIN [' + [name] + '] WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED, SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ', DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=' + CASEWHEN is_expiration_checked = 1 THEN'ON'ELSE'OFF'END + ', CHECK_POLICY=OFF GO IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''') ALTER LOGIN [' + [name] + '] WITH CHECK_EXPIRATION=' + CASEWHEN is_expiration_checked = 1 THEN'ON'ELSE'OFF'END + ', CHECK_POLICY=' + CASEWHEN is_policy_checked = 1 THEN'ON'ELSE'OFF'END + ' GO
' --[name], [sid] , password_hash from master.sys.sql_logins where type_desc = 'SQL_LOGIN' and [name] notin ('sa', 'guest')
PRINT'-----------------------------------------------------------------------------' PRINT'-- Disable any logins' PRINT'-----------------------------------------------------------------------------' SELECT'ALTER LOGIN [' + [name] + '] DISABLE GO ' from master.sys.server_principals where is_disabled = 1
' from master.sys.server_role_members rm join master.sys.server_principals r on r.principal_id = rm.role_principal_id join master.sys.server_principals l on l.principal_id = rm.member_principal_id where l.[name] notin ('sa') AND l.[name] notlike'BUILTIN%' and l.[NAME] notlike'NT AUTHORITY%' and l.[name] notlike'%\SQLServer%'
SELECT x.value('name[1]','VARCHAR(10)')AS Name FROM @Inf.nodes('/root/names')AS t(x)
If you try to run the code above, you will get an error message like this
Msg 9402, Level 16, State 1, Line 3 XML parsing: line 1, character 39, unable to switch the encoding
Why is that? If you change the encoding to UTF-8, the code works.
The solution is to know that UTF-16 works like UNICODE, and how do we denote UNICODE strings in SQL Server? Yes, by prefixing the string with N.
So this code works with UTF-16 encoding and you can happily continue to work.
Sun, 04 Jul 2010 21:59:57 GMT - This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog.
Consider this sample data
DECLARE @Sample TABLE
(
ID INT,
Data VARCHAR(100)
)
INSERT @Sample
VALUES (1,'Peso & Performance SQL'),
(1,'MVP'),
(2,'Need help <? /> -- '),
(2,'With XML string concatenation ?')
The "trick" is to use the TYPE directive (to deal with entitization), and then use ".value" function to get the correct value out.
So here is the final query.
Last November, I blogged about a weird bug with SQL Server 2005 on a Windows 2008 cluster. We were having issues with Database Mail and other things and learned that it was due to the server names being in lower case. To workaround the issue, we had to follow the steps outlined here as there was no bug fix at the time. I’ve now learned that this bug has been fixed! The fix is included in cumulative update package 9 for SQL Server 2005 service pack 3. Check it out here.
I got an email from Mr Celko and he correctly stated that my previous solution was not truly working with sets, so he posted me some solutions and proper sample data.
With this information at my hand, I started to investigate what really is needed to get this Relational Division to work properly with two sets; Dividend and Divisor.
Some of you know me well, and know I am not satisfied with just solving the problem. There have to be some tweaks, and I did that too with this solution. Not only is it only touching the Dividend table once and Divisor table once, you can also set if you want a division with no remainder (which means all records in Divisor should match and not a single record more), or allow a division with remainder (which means all the records should match and maybe more records).
Great? Just set 1 for "No remainder" and 0 for "Allow remainder".
Simple as that. So why does it work? Remember your old algebra? "Divide is the same thing as multiply with the inverse number..."
-- Celko 1 SELECT D1.group_id FROM Dividend AS D1 WHERE D1.item_name IN (SELECT item_name FROM Divisor) GROUPBY D1.group_id HAVINGCOUNT(DISTINCT D1.item_name)= (SELECTCOUNT(*)FROM Divisor) ANDCOUNT(DISTINCT D1.item_name)= (SELECTCOUNT(*)FROM Dividend AS D2 WHERE D2.group_id = D1.group_id)
-- Celko 2 SELECT D1.group_id FROM Dividend AS D1 WHERE D1.item_name IN (SELECT item_name FROM Divisor) ANDNOTEXISTS( SELECT* FROM Dividend AS D2 WHERE D2.group_id = D1.group_id AND D2.item_name NOTIN (SELECT item_name FROM Divisor) ) GROUPBY D1.group_id HAVINGCOUNT(DISTINCT D1.item_name)= (SELECTCOUNT(*)FROM Divisor)
-- Celko 3 SELECT D1.group_id FROM ( SELECT group_id, item_name, COUNT(*)OVER (PARTITIONBY group_id)AS cnt FROM Dividend )AS D1 WHERE D1.item_name IN (SELECT item_name FROM Divisor) AND cnt = (SELECTCOUNT(*)FROM Divisor) GROUPBY D1.group_id HAVINGCOUNT(D1.cnt)= (SELECTCOUNT(*)FROM Divisor)
--Celko 4 ;WITH Divisor2 AS ( SELECT group_id, MIN(CASEWHEN item_name IN (SELECT item_name FROM Divisor)THEN 1 ELSE 0 END)OVER(PARTITIONBY group_id)AS single, SUM(CASEWHEN item_name IN (SELECT item_name FROM Divisor)THEN 1 ELSE 0 END)OVER(PARTITIONBY group_id)AS full_basket FROM Dividend ) SELECT D.group_id FROM Dividend AS D, Divisor2 WHERE D.group_id = Divisor2.group_id AND Divisor2.single = 1 AND Divisor2.full_basket = (SELECTCOUNT(*)FROM Divisor) GROUPBY D.group_id
You can copy and paste the code to a query window and run them. Investigate the execution plan and compare the 4 of them.
And now to my solution.
-- Peso 1 SELECT group_id FROM ( SELECT t.group_id, SUM(CASEWHEN t.item_name = n.item_name THEN 1 ELSE 0 END)AS cnt, COUNT(*)AS Items FROM dbo.Dividend AS t CROSSJOIN dbo.Divisor AS n GROUPBY t.group_id, t.item_name )AS d GROUPBY group_id HAVINGSUM(cnt)=MIN(Items) ANDMIN(cnt)>= 1 -- 1 means no remainder, 0 means remainder
After some challenging with MVP Adam Machanic, here is another version
-- Peso v2 SELECT t.group_id FROM ( SELECT group_id, COUNT(*)AS cnt FROM dbo.Dividend GROUPBY group_id )AS kc INNERJOIN( SELECTCOUNT(*)AS cnt FROM dbo.Divisor )AS nc ON nc.cnt = kc.cnt INNERJOIN dbo.Dividend AS t ON t.group_id = kc.group_id INNERJOIN dbo.Divisor AS n ON n.item_name = t.item_name GROUPBY t.group_id HAVINGCOUNT(*)=MIN(nc.cnt)
So it seems my solution is cleaner and faster than the previous existing. But the best thing is yet hidden. My solution cares for multi-column division (just expand the CASE and GROUP BY clauses) whereas the previous 4 do not. Well, not easily anyway.
It will involve some replacing for IN with EXISTS, and some string concatenation for the DISTINCT clauses.
If you have a solution, I encourage you to reply to my blog, or respond to my still unanswered questions on the ASP.NET Forums or Stack Overflow or Server Fault. I will happily mark your answer as correct (assuming that it is).
I came across an interesting post on Microsft SQL Server forum this afternoon. It was a question about Relational algebra and the poster wanted to have an efficient query to solve hos problem.
The problem could be solved with relational division, but there is no such operator in SQL Server. Maybe there will be same day.
For a fully working solution, see http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx
But for now there is no such operator, so we as developers have to find our own ways.
First prepare and populate some sample data
People had already been active and posted some solutions, of which this common query was present.
SELECTs.ParentID FROM@Sample AS s WHEREs.Keyword IN('one','two','three') GROUPBYs.ParentID HAVINGCOUNT(DISTINCT s.Keyword)= 3 ANDCOUNT(DISTINCT s.Keyword)=(SELECTCOUNT(*)FROM @Sample AS x WHERE x.ParentID = s.ParentID)
and this type of query
SELECTs.ParentID FROM@Sample AS s WHEREs.Keyword IN('one','two','three') ANDNOTEXISTS ( SELECT* FROM@Sample AS x WHEREx.ParentID = s.ParentID AND x.Keyword NOTIN('one','two','three') ) GROUPBYs.ParentID HAVINGCOUNT(DISTINCT s.Keyword)= 3
And even a XML query!
;WITH AggStr AS ( SELECTParentId, ( SELECTCAST(','ASVARCHAR(MAX))+ c.Keyword FROM@Sample AS c WHEREc.ParentID = p.ParentID ORDERBYc.Keyword FORXMLPATH('') )AS c1 FROM( SELECTDISTINCT ParentID FROM@Sample )AS p ) SELECTParentID FROMAggStr WHEREc1 =',one,three,two'
The good thing is that all three produce the same wanted result but the bad thing is the inefficient execution plans. Then one poster did his homework and read about Mr Celko and translated his algorithm to the current problem, and then the query looked like this
SELECTParentID FROM( SELECTParentID, Keyword, COUNT(*)OVER (PARTITIONBY ParentID)AS cnt FROM@Sample )AS w WHEREKeyword IN('one','two','three') AND cnt = 3 GROUPBYParentID HAVINGCOUNT(cnt)= 3
With these queries in mind, I thought about the problem and realized the problem did in fact have a much simpler solution.
The query I came up with is the simplest of them all, and just does one pass of the source table. Yes, only one pass just as the first Celko query for relational division, but without the internal worktable.
This is the query I came up with
How does the query work? The second aggregation filtering just makes sure all three keywords are present.
But the first aggregation filter? What does it do? To simplify, I just write that it takes care of the modulo part of the relational division. There cannot be a "fractional" part of the relational division, because it means that particular ParentID has more keywords than wanted.
Simple as that.
//Peso
PS. These are the textual execution plans for the four types of queries and then mine.
|--Filter(WHERE:([Expr1003]=CASE WHEN [Expr1007] IS NULL THEN (0) ELSE [Expr1007] END)) |--Nested Loops(Left Outer Join, OUTER REFERENCES:([s].[ParentID])) |--Filter(WHERE:([Expr1003]=(3))) ||--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1014],0))) ||--Stream Aggregate(GROUP BY:([s].[ParentID]) DEFINE:([Expr1014]=Count(*))) ||--Index Scan(OBJECT:(@Sample AS [s]),WHERE:(@Sample.[Keyword] as [s].[Keyword]='one' OR @Sample.[Keyword] as [s].[Keyword]='three' OR @Sample.[Keyword] as [s].[Keyword]='two') ORDERED FORWARD) |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1015],0))) |--Stream Aggregate(DEFINE:([Expr1015]=Count(*))) |--Index Seek(OBJECT:(@Sample AS [x]), SEEK:([x].[ParentID]=@Sample.[ParentID] as [s].[ParentID]) ORDERED FORWARD)
|--Filter(WHERE:([Expr1007]=(3))) |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1010],0))) |--Stream Aggregate(GROUP BY:([s].[ParentID]) DEFINE:([Expr1010]=Count(*))) |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([s].[ParentID])) |--Index Scan(OBJECT:(@Sample AS [s]),WHERE:(@Sample.[Keyword] as [s].[Keyword]='one' OR @Sample.[Keyword] as [s].[Keyword]='three' OR @Sample.[Keyword] as [s].[Keyword]='two') ORDERED FORWARD) |--Index Seek(OBJECT:(@Sample AS [x]), SEEK:([x].[ParentID]=@Sample.[ParentID] as [s].[ParentID]),WHERE:(@Sample.[Keyword] as [x].[Keyword]<>'one' AND @Sample.[Keyword] as [x].[Keyword]<>'three' AND @Sample.[Keyword] as [x].[Keyword]<>'two') ORDERED FORWARD)
|--Filter(WHERE:([Expr1004]=(1) AND [Expr1005]=(3))) |--Stream Aggregate(GROUP BY:([ParentID]) DEFINE:([Expr1004]=MIN([Expr1006]), [Expr1005]=SUM([Expr1006]))) |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Keyword]='three' OR [Keyword]='two' OR [Keyword]='one' THEN (1) ELSE (0) END)) |--Index Scan(OBJECT:(@Sample), ORDERED FORWARD)
The OP Asked: "If I supply a Date, How can I get the next available date, that is not a holiday, and is not on the weekend."
I came up with this hack...please let me know if anyone has come with a different version
Thanks
Brett
CREATE TABLE Holidays (Holiday_Dt datetime, HoldayName varchar(255))
GO
INSERT INTO Holidays(Holiday_Dt, HoldayName)
SELECT '1/1/2010', 'New Years Day' UNION ALL
SELECT '1/18/2010', 'Martin Luther King' UNION ALL
SELECT '2/15/2010', 'President''s Day' UNION ALL
SELECT '5/31/2010', 'Memorial Day' UNION ALL
SELECT '7/5/2010', 'July 4th Holiday' UNION ALL
SELECT '9/6/2010', 'Labor Day' UNION ALL
SELECT '11/25/2010', 'Thanksgiving' UNION ALL
SELECT '11/26/2010', 'Black Friday' UNION ALL
SELECT '12/24/2010', 'Christmas Holiday' UNION ALL
SELECT '12/31/2010', 'New Years Eve'
GO
DECLARE @inp_Dt datetime; SET @inp_Dt = '11/25/2010'
SELECT TOP 1 CASE WHEN Holiday_Dt IS NULL THEN inp_Dt ELSE DATEADD(d,n,inp_Dt) END AS Next_Available_Dt
FROM (SELECT @inp_Dt AS inp_Dt) AS XXX
LEFT JOIN Holidays h
ON h.Holiday_Dt = xxx.inp_Dt
LEFT JOIN (SELECT 1 n UNION SELECT 2 n UNION SELECT 3 n UNION SELECT 4 n UNION
SELECT 5 n UNION SELECT 6 n UNION SELECT 7 n) AS n
ON xxx.inp_Dt < DATEADD(d,n,inp_Dt)
WHERE DATENAME(dw,DATEADD(d,n,inp_Dt)) NOT IN ('Saturday','Sunday')
AND NOT EXISTS (SELECT * FROM Holidays WHERE Holiday_Dt = DATEADD(d,n,inp_Dt))
GO
This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML.
First, create some sample data like this
-- Prepare sample data
DECLARE @Sample TABLE
(
RowID INTIDENTITY(1, 1)PRIMARYKEYCLUSTERED,
Expense SMALLMONEYNOTNULL
)
-- Populate sample data
INSERT @Sample
(
Expense
)
VALUES (12.51),
(45.63),
(66.35),
(92.66),
(65.46),
(54.01),
(32.23),
(27.16),
(78.92),
(14.58)
Next, we need to create a variable to hold the user's wanted total sum.
-- Prepare user supplied parameter
DECLARE @WantedSUM SMALLMONEY= 111.09
And we also need to create a temporary staging table to hold the valid combinations
-- Prepare temporary staging table
DECLARE @Temp TABLE
(
CombID INTIDENTITY(1, 1)PRIMARYKEYCLUSTERED,
TrackPath XMLNOTNULL
)
Now we only have to do the calculations!
Here I am using a special trick to get the unique combination, since the path of records 1>2>3 is the same as 1>3>2, 2>1>3, 2>3>1, 3>1>2 and 3>2>1. See explanation between combination and permutation at Wikipedia here.
To keep track of which records I already have used in the total sum, I simply remove the record id (RowID) from the Hits list.
And, to give the correct answer at the end, I build a XML string with visited RowID's building up the correct sum.
-- Calculate all possible permutations using recursion
;WITH ctePack(Total, Hits, TrackPath)
AS (
SELECT s.Expense AS Total,
(SELECT'#'+CAST(x.RowID ASVARCHAR(MAX))FROM @Sample AS x WHERE x.RowID <> s.RowID ORDER BY x.RowIDFORXMLPATH(''))+'#'AS Hits,
INNERJOIN ctePack AS p ON p.Hits LIKE'%#'+CAST(s.RowID ASVARCHAR(MAX))+'#%'
WHERE p.Total + s.Expense <= @WantedSum
)
INSERT @Temp
(
TrackPath
)
SELECTMIN(TrackPath)
FROM ctePack
WHERE Total = @WantedSum
GROUPBY Hits
When the iterations are over, and we have the wanted combniation(s), the task left is to report the records giving us the correct sum.
We also need the records grouped so that we can see which group each expense belong to. In same cases, one and the same record may used in multiple groups.
-- Display the final resultset
SELECT t.CombID,
s.RowID,
s.Expense
FROM @Temp AS t
CROSSAPPLY t.TrackPath.nodes('/ID')AS f(n)
INNERJOIN @Sample AS s ON s.RowID = f.n.value('.','INT')
ORDERBY t.CombID,
s.RowID
If you don't want to use XML, you can write the recursive cte like this, to get all included records directly.
-- Calculate all possible permutations using recursion ;WITH ctePack(RowID, Expense, Total, Tracker) AS ( SELECT s.RowID, s.Expense, s.Expense AS Total, ( SELECT'#'+CAST(x.RowID ASVARCHAR(MAX)) FROM @Sample AS x WHERE x.RowID <> s.RowID ORDER BY x.RowID FORXMLPATH('') )+'#'AS Tracker FROM @Sample AS s WHERE s.Expense <= @WantedSum
UNIONALL
SELECT s.RowID, s.Expense, p.Total + s.Expense, REPLACE(p.Tracker,'#'+CAST(s.RowID ASVARCHAR(MAX))+'#','#')AS Tracker FROM @Sample AS s INNERJOIN ctePack AS p ON p.Tracker LIKE'%#'+CAST(s.RowID ASVARCHAR(MAX))+'#%' WHERE p.Total + s.Expense <= @WantedSum ) SELECTDISTINCT DENSE_RANK()OVER (ORDERBY Tracker)AS CombID, RowID,
Expense FROM ctePack WHERE Total = @WantedSum
Sat, 26 Jun 2010 15:42:45 GMT - I didn't discover this, a person on StackOverflow did. Just read the question and did some thinking. If you are interested, then please read the question and potentially comment @ StackOverflow.
I've never submitted anything to Microsoft Connect. Do you think that this qualifies? It would certainly seem if not a bug, then a severe annoyance if it ever happened in my code.
When I try to rename a node name such as a table or column in Management Studio, I cannot use DELETE key to remove previous characters. However, I can use BACKSPACE key.
I attended the Web Camp in Redmond last Friday at the Microsoft Conference Center. It was a really nice setup in the room with rows of tables (plenty of space between them) and long power strips so every attendee could plug-in their laptop. The week before that, I attended SQLSaturday, also at a Microsoft facility, and it had rows of tables setup and plenty of in-floor outlets. Maybe this is just to be expected at Microsoft…it makes sense that of the events that will be held at a Microsoft facility, it is likely that a large portion of the attendees will have laptops with them. I sure wish the Washington State Convention Center, which is the recent and next location for the PASSCommunity Summit had a setup like this.
Sure I know that you can’t fit as many people into a room if you set it up with tables, but there are several attendees who are there with laptops open, taking notes and following along, and it sure would be nice. Maybe there is a way the rooms could be configured with chairs-only rows on one side and tables on the other, if it’s not possible to do all tables. Or find some other way to divide the room into sections. Even if you don’t have a laptop, it is nice to have a table to put your notepad on to take notes. At the very least, I would like to see convention centers have more in-floor outlets so that if you’re not going to give me a table, you at least don’t force me to vie for one of the few seats on the outside aisle near a real wall that does have an outlet in it, and then lay out my power cord in a trip hazard. Make it easier (and safer) for everyone.
While at WebCamp, I mentioned my Entity Framework 4 / Profiler conundrum to James Senior (@jsenior) and he asked me to send him an email follow-up which I have done, so hopefully he can find out what is going on with this. It strikes me as really odd that I get correct results in a SQL 2000 instance, but wrong results in a SQL 2008 R2 instance. I wonder what’s different.
mysql_explain_log is
part of the standard MySQL distribution. It can be used to feed general MySQL
logs back into MySQL and use EXPLAIN on all statements to analyse which indexes
have been used and which queries didn't use any index.
phpMyAdmin is a web based database
administration tool specifically for managing MySQL databases
The Windows® Azure™ Platform is an internet-scale cloud computing and services platform hosted in Microsoft data centers. The Windows Azure Platform provides a range of functionality to build applications that span from consumer web to enterprise scenarios and includes a cloud operating system and a set of developer services. Fully interoperable through the support of industry standards and web protocols such as REST and SOAP, you can use
the Azure services individually or together, either to build new applications or to extend existing ones. What is the Windows
Azure Platform?
Windows Azure Platform Training Kit includes a set of technical content
including hands-on labs, presentations, and demos that are designed to help you
learn how to use the Windows Azure platform including: Windows SQL Azure
and .NET Services.
Windows Azure Platform Developer Center More
Microsoft® Windows 7, Windows 8, Vista. XP, etc.
Embarcadero
Developer Network (EDN), the community site for developers where you can
access, leverage and contribute valuable information and knowledge at any time.
The knowledge, systems, and membership that are EDN exist to enhance the
effectiveness of your day-to-day job performance, enrich the career of anyone
involved in systems development and management, and extend the breadth and depth
of our industry. Includes C++ Builder, Delphi, J Builder, InterBase, Rapid SQL,
etc... Using RAD, (Rapid Application Development), C++ environment and framework
designed for ultra-fast development of highly-maintainable Windows GUI
applications
Maatkit Tools for SQL. Makes MySQL easier and safer to manage. It provides simple, predictable ways to do things you cannot otherwise do. It would be nice if these features were included with MySQL, but they are not. That's why Maatkit is now shipping by default with many GNU/Linux distributions such as Debian and CentOS. You can use Maatkit to prove replication is working correctly, fix corrupted data, automate repetitive tasks, speed up your servers, and much, much more. This is the older MySQL Toolkit. This toolkit contains essential command-line utilities for MySQL, such as a table checksum tool and query profiler. It provides missing features such as checking. A set of essential tools for MySQL users, developers and administrators. The project's goal is to make high-quality command-line tools that follow the
UNIX philosophy of doing one thing and doing it well. They are designed for scriptability and ease of processing with standard command-line utilities such
as Awk and Sed. slaves for data consistency, with emphasis on quality and scriptability.
Reserved Words Certain words such as SELECT,DELETE, or BIGINT are reserved and require special
treatment for use as identifiers such as table and column names. This may also be true for the names of built-in functions. Reserved words are permitted as identifiers if you quote them as described in Section 8.2, Schema Object Names
Sphinx (SQL Phrase Index), Free open-source SQL full-text search engine. Provides fast, size-efficient and relevant fulltext
search functions to other applications. Sphinx was specially designed to integrate well with SQL databases and scripting languages. Currently built-in data sources support fetching data either via direct connection to MySQL or PostgreSQL, or using XML pipe mechanism.
Syphix Free open-source SQL full-text search engine. As we know build in full text search is currently limited only to MyISAM search engine as well as has few other limits. Today Sphinx
Search plugin for MySQL was released which now provides fast and easy to use full text search solution for all storage engines. This version also adds a lot of other new features,
including boolean search and distributed searching.
MySQL crashes is FullText search on with some words:-
SQL Error 28 and Error code 30. "MySQL's temporary directory" /tmp that lacks space available. (Not the main drive).
Errcode: 30. This may also be t/mp file issue that may result in message
like; execute failed: Can't create/write to file '/tmp/#sql_xyx.MYI' (Errcode:
30) Similar type of error may be cause by too many connection. Error 28 in
SQL may occur because of duplicate data in the database. SQL databases should be
normalized.
Cleaning up /tmp directory on busy cPanel web hosting servers. MySQL leaves and uses it's temporary files in /tmp, and if there is no space in there, queries will start failing. Uploads from PHP or Perl are placed in there till the upload process is over, they cannot be further placed there because there is no more space left. So far, we have failing MySQL & inability to upload complete PHP files, system administrator hell.
querysniffer is a MySQL query
sniffer written with Net::Pcap. It sniffs the network with pcap, extracts
queries from mysql packets, and prints them on standard output.
What is SQL Injection? It is a way to inject SQL query/command as an input possibly via web pages. Many web pages take parameters from web user, and make SQL query to the database. With SQL Injection, it is possible for us to send
SQL quire that will carry out an undesired result. For example It could be likened to issuing a format *.* in DOS.
SQL Injection Walkthrough
This article will try to help beginners with grasping the problems facing
them while trying to utilize SQL Injection techniques, to successfully utilize
them, and to protect themselves from such attacks.
SQL Injection Prevention Cheat Sheet This article is focused on providing clear, simple, actionable guidance for preventing SQL Injection flaws in your applications. SQL Injection attacks are unfortunately very common, and this is due to two factors: a) the significant prevalence of SQL Injection vulnerabilities, and b) the attractiveness of the target (i.e., the database typically contains all the interesting/critical data for your application).
How to Detect and Prevent a WordPress Spam Injection Attack. Spam Injection
software hides spam keyword links in code that is usually encoded with a
PHP function that
effectively scrambles HTML,
to be decoded once safely embedded on your server, database, etc. You won't see
these files decoded, but the Google Bot and other bots will when crawling your
site! Once the Bots access the code the spam injection software has done it's
work, effectively stealing your search index to improve their own
pagerank. Also see
Blogs
Web Masters.Click Here Now to start making money. A Great opportunity to make some money. Receive
50% by offering your users Ton's of Keywords on
A Great Portal websites. Our Affiliate Program Pays you 50% on Level 1 of Every Sale of our
Text Link both searchable and static Text Link!
A Computer Portal. Freeware, Shareware. Download software. Computer languages and Programming code. Including PERL Scripts and Java Scripts. Webmaster Tools. Internet Marketing, Website promotion.
Hardware Help from BIOS to Windows and UNIX.
