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 Hacker Includes MySQL cheatsheets; when you need a quick access to a command or query and do not have time for reading an article or essay or digging through forum posts, cheatsheets are life savers. In such a spirit, we have decided to create a cheatsheet for commonly used MySQL commands. Performance Best Practices
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.
MySQL Manager is a handy, easy to use application specially designed to help you manage MySQL servers and databases.
SQL Tutorial. By the end of this tutorial, you should have a good general understanding of the SQL syntax, and be able to write SQL queries using the correct syntax.
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.
Fri, 03 Feb 2012 22:16:54 +0000 - I recently noticed both MariaDB and XtraDB (not MySQL yet) have a (newer) variable query_cache_strip_comments.
This variable is great for those who want to append comments to various queries, but still want the query cache to be able to serve such queries. Unfortunately, with MySQL, this is not currently possible.
In the past, I wrote a post on using MySQL Proxy which described a technique of monitoring queries through the proxy by appending IP addresses to the queries so one could track where they originated from. However, one pitfall to that was the MySQL query cache *does not* ignore the comment and treats them all as different queries (see the user comments for further discussion). (I did subsequently enhance that functionality implementing the SHOW PROXY PROCESSLIST command (often used in the Proxy Admin module), in large part because of this limitation.)
To enable it (in MariaDB 5.3+ and XtraDB), just add query_cache_strip_comments under the [mysqld] section in your my.cnf file and restart mysqld.
Alternatively, you can also set it dynamically:
mysql> set @@global.query_cache_strip_comments=1;
Query OK, 0 rows affected (0.04 sec)
mysql> show global variables like 'query_cache_strip_comments';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| query_cache_strip_comments | ON |
+----------------------------+-------+
mysql> select version();
+---------------+
| version() |
+---------------+
| 5.3.3-MariaDB |
+---------------+
Fwiw, from examining the source code, both implementations seemed to differ, but the end result is the same, and it’s a welcome addition, if you ask me.
Fri, 03 Feb 2012 21:21:40 +0000 - The MySQL Forums seem to be have been around forever. There are groups for announcements, performance, InnoDB, Cluster, Replication, Certification, Events, questions in German, partitioning, and just under a hundred MySQL related subjects in total. Most folks in the MySQLverse seem to regularly lurk in one or more of the forums. There area great source of information and sometimes humor. But are you using the forums to help build your MySQL mental muscle? Ask yourself how would you solve the problem or how would you guide a junior DBA or developer to fix an issue posted in a forum.
Try reading the groups that are not in your regular reading list. Is there a question in there you can not answer even after a peek at the documentation?
After running MySQL for a few years, it is easy to skip over the problems of novices found in the Newbie section. But like the old story of the Emperor’s New Clothes, a new set of eyes can point out things we do not notice. Recently someone in the Newbie group wanted to set up a round robin for inserts over eight disks in an attempt to get 8x performance gain. Hmmm, if that did scale would it scale linearly(1) or close to it? Too bad I do not have the hardware or spare cycles to give it a try.
Or question on how to remove a ‘width=xxx’ pattern from a text field. Hmm, the old GIGO rules would dictate that some sort of ETL tool or script remove the unwanted chaff before it goes into the database. Do you use regular expressions in a PHP, Perl, or Java hack? Could a column oriented editor let you WYSIWYG the data? Maybe a trigger on insert that culls the unwanted width data? Hey, does your data have anything hidden in a text filed that should not be there and how best to get rid of it?
So read through a few forums and use them to exercise you mind. And if you see something in a question that you can answer, please do so.
Nothing scales linearly that you need to scale linearly. At least not on your budget and or schedule.
Fri, 03 Feb 2012 18:42:48 +0000 - Mark Callaghan speaks at the New England Database Summit about how data manageability is more important than performance.
Peak performance is thrown out, 95%-98% is important.
Variance shouldn't be large.
Data manageability is rate of interrupts per server for the operations team. Rate of server growth much bigger than rate of new hires for the systems teams. A lot of the db team is from University of Wisconsin-Madison!
Why MySQL? Because it was there when he came. Mark and ops/engineering peers made it scale 10x. He likes MySQL for OLTP, InnoDB is "An amazing piece of software."
They can get 500,000 qps using a cached workload, which is on par with memcached.
What Facebook really does is OLTP for the social graph. The workload is secondary indexes, index-only queries, small joins but most queries use one table, multi-row transactions, majority of workload does not need SQL/optimizer, they do a physical and logical backup.
Most of this does not require SQL [blogger's note - they built Cassandra]. Why is the grass greener on the other side? automated replacement of failed nodes, less downtime on schema changes and/or fewer schema changes, multi-master, better compressions, etc.
Circa 2010, 13 million queries per second, 4 ms reads, 5 ms writes, 38GB peak network per second, etc.
Why so many servers? Big data high queries per seconds. They add servers to add IOPS, so they're interested in compression and flash, so they can get more IOPS. If they do remain on disk, write-optimized dbs are interesting too. About 10 people on the db team, which is very small for a company that size.
How to scale MySQL? Fix stalls to make use of capacity, improve efficiency to use fewer queries/fewer data. Fixing stalls doesn't make MySQL faster, makes it less slow.
[blogger's note - I stopped taking notes here because this is a rehash of the "How Facebook Does MySQL" talk that has been done over and over...]
[restarted when he started talking about data manageability again]
How Facebook got it's data manageable.
pylander- sheds load during a query pileup - kills dup queries, limits # of queries from some specific accounts -- take off on Highlander: there can be only one.
dogpile - collects data during a query pileup - gets perf counters and list of running queries, generates HTML page with interesting results.
Online schema change tool, for frequent schema changes, especially adding indexes. This briefly locks the table, to setup triggers to track changes, copy data to a new table with the new desired schema, replay changes to the new table, then briefly lock the table again as you rename the new table as the target table.
Manageability is a work in progress -- working on:
- make InnoDB compression work for OLTP
- Faker - tool for prefetching for replication slaves - replay workload is: page read, do some modification, page write. bottleneck might be disk reads, work is done by a single thread, transactions on master are concurrent. Faker has multiple threads replay transactions in "fake-changes" mode, no undo, no rollback, read-only, fetches into the buffer pool the pages needed for that transaction. Captures about 70% of disk reads for replication, they're working on fixes to get it up to 80-90%.
- auto replacement - replace failed and unhealthy MySQL servers.
- Auto resharding - sharding is easy, re-sharding is hard.
open issues in manageability:
diagnose why one host is slow, others are not.
....and some more.
Fri, 03 Feb 2012 17:45:48 +0000 - Part of every SkySQL subscription is paid to Monty Program to fund server development and improved functionality in MariaDB® (which is then sent upstream for inclusion in the MySQL® Server).
Thus, we'd like to know what you'd like to see in the upcoming releases of the server.
Please take a couple seconds (literally) and let your vote be heard:
http://www.skysql.com/content/new-server-functionality-have-your-say
We thank you and look forward to hearing your thoughts and ideas!
Fri, 03 Feb 2012 17:45:00 +0000 - The Rocky Mountain Oracle User Group ( RMOUG ) Training days are almost here. This year they are going to have a MySQL Oracle ACE Director, Ronald Bradford talk on MySQL Security Essentials. I will also be having a MySQL Crash Course . I was going to talk about replication but after attending the IOUG User summit and the seeing the vast interest in MySQL from Oracle users, I decided to take a step back and do this talk 1st. I have had a similar talk at a RMOUG meetup hands on labs in the past, but it has been updated.Alex Gorbachev of Pythian also has a session, Monitoring MySQL with Oracle Cloud Control 12c. Look forward to seeing everyone and I think great things are on the horizon with RMOUG and MySQL.
Fri, 03 Feb 2012 16:08:00 +0000 - According to VentureBeat*, games companies raised a record-breaking $1.54 billion in funding last year and social gaming accounted for over half of that. No wonder everyone wants to have a piece of that pie!With the arrival of social network platforms, the gaming industry has seen an explosion in casual and social gaming. The social gamer represents a massive audience that cuts across all age, gender and demographic boundaries. Online social games are some of the most demanding applications in the world, with millions of users, stringent response times, complex simulation models and billing requirements. Games take years to develop for a reason ...Online social games are data-driven applications, and databases are central to these applications. However, there is no single database architecture that will fit the different types of data that the application needs to store. A data management architecture needs to account for the diversity of data, and optimize for some of the differences in the datatypes. E.g. it is ok to lose leaderboard data during a game as it can be reconstituted, whereas billing data needs to be 100% ACID. Therefore, with the generous contribution of Joshua Butcher, we just published a whitepaper that discusses the different types of data stored for various functions in social gaming. We will see that there cannot be a one-size-fits-all approach to database architecture, and suggest a sharding strategy based on schema partitioning. With our new whitepaper, we’re also starting a discussion on what the database of choice might be for anyone wanting to develop online social games. With so many NoSQL databases now available, one might wonder why MySQL would be a good database choice for the gaming industry. To find out, download our whitepaper today! If you have any questions or comments, feel free to reply to this blog below or reach out to us on Facebook, LinkedIn, Xing, Twitter or directly via these contact details.* http://venturebeat.com/2012/01/06/deanbeat-game-companies-raised-a-record-breaking-1-55b-in-2011/
Fri, 03 Feb 2012 10:55:07 +0000 - Accidentially I came across the statement “SHOW GRANTS requires the SELECT privilege for the mysql database.” in MySQL documentation (http://dev.mysql.com/doc/refman/5.1/en/show-grants.html).
It is not quite true. Any user can “SHOW GRANTS [FOR himself]” with no privileges at all. But more important: SELECT priviege is requried on database-level, Privilege to the privileges tables is not enough. See
SHOW GRANTS;
/*returns
Grants for me@%
—————————————————–
GRANT USAGE ON *.* TO ‘me’@'%’
GRANT SELECT ON `mysql`.`user` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`tables_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`procs_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`db` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`columns_priv` TO ‘me’@'%’*/
SHOW GRANTS FOR root@localhost;
/*returns
Error Code: 1044
Access denied for user ‘me’@'%’ to database ‘mysql’ */
Anybody having SELECT privilege to the mysql database can read logs if you use log tables. And unlike when logging to files (where you can specify logfile paths) there is no option to specify another database for the log tables.
I think it is a serious security flaw. The reason is that a log may contain data. That may be private data (email addresses, bank account numbers .. you name it). Consider a statement like
UPDATE `identity`SET `bank_account_no` = ….. WHERE social_security_id = ….. ;
You should have SELECT privilege to the `identity` table to see those data. But if you can read logs you need not.
I don’t claim all applications would send such statements (but I believe that some will do). You can avoid it to some extent by using user variables, hashes, or by writing complex statements with JOINs and SUBQUERIES so that you don’t need to ‘ping-pong’ data from the server to the application and back (but it may conflict with performance in particular if SUBQUERIES are used).
So you should be careful with logging to tables. Not only is performance not good, but there are security concerns. Who should be allowed to see the logs? (but still log tables may be convenient for a test/development scenario though.)
I posted this bug report: http://bugs.mysql.com/bug.php?id=64215
Tweet
Fri, 03 Feb 2012 08:53:11 +0000 - I just recently used Event scheduler which was the major feature in MySQL 5.1 version. Its very much similar to the linux crontab functionality. MySQL Event is nothing but a bunch of statements which we can run on specific schedule. … Continue reading →
Fri, 03 Feb 2012 06:00:15 +0000 - With new year many new projects, new technologies, new frameworks and new ideas are springing up at the speed of light and bloggers in the database arena are keeping up with this pace and this Log Buffer Edition is also living up to that pace and covers some of those posts in Log Buffer #257. [...]
Thu, 02 Feb 2012 20:50:07 +0000 - Collaborate 2012 MySQL Sessions
Please mark you calendars for the MySQL sessions at Collaborate this April in Las Vegas.
Date
Session ID
Session Details
Track
Sun. Apr. 22 4:30 pm – 5:30 pm
9390
The Essentials of Data Discovery: Do you Know Where Your Data Is?
Asset Lifecycle Management
Mon. Apr. 23 9:45 am – 10:45 am
826
Virtualization Boot Camp: Virtualizing Oracle On VMware – Quick Tips
Database
Mon. Apr. 23 12:15 pm – 12:45 pm
554
Set Up MySQL In Five Minutes Flat (2-Part Session)
Database
Mon. Apr. 23 1:15 pm – 2:15 pm
578
Shell Scripting for MySQL Administration
Database
Mon. Apr. 23 2:30 pm – 3:30 pm
681
Portable SQL between Oracle and MySQL
Development
Mon. Apr. 23 3:45 pm – 4:45 pm
429
MySQL High Availability Solutions and case studies
Database
Mon. Apr. 23 5:00 pm – 6:00 pm
436
Making Sense of Parent-Child Relationships – The Oracle Kind, Not the Human Kind
BI/Data Warehousing/EPM
Mon. Apr. 23 5:00 pm – 6:00 pm
493
Administering MySQL for Oracle DBAs
Database
Mon. Apr. 23 5:00 pm – 6:00 pm
9050
Training? Where do I even begin?
Professional Development
Tue. Apr. 24 8:00 am – 9:00 am
281
Database Development Boot Camp: SQL Tips, Techniques and Traps to Avoid
BI/Data Warehousing/EPM
Tue. Apr. 24 8:00 am – 9:00 am
729
Challenges of Big Databases with MySQL
Database
Tue. Apr. 24 12:00 pm – 12:30 pm
389
MySQL for Oracle DBAs or How to Speak MySQL for Beginners (2-Part Session)
Database
Tue. Apr. 24 12:00 pm – 12:30 pm
1554
Set Up MySQL In Five Minutes Flat (2-Part Session)
Database
Tue. Apr. 24 2:00 pm – 3:00 pm
694
Security Boot Camp: Avoiding SQL Injection: Don’t Let a Stranger “Shoot You Up”
Development
Tue. Apr. 24 2:00 pm – 3:00 pm
809
Demystifying MySQL for Oracle DBAs and Developers
Database
Tue. Apr. 24 4:30 pm – 5:30 pm
362
Security Boot Camp: Security Around MySQL
Database
Wed. Apr. 25 12:00 pm – 12:30 pm
1389
MySQL for Oracle DBAs or How to Speak MySQL for Beginners (2-Part Session)
Database
Thu. Apr. 26 9:45 am – 10:45 am
9395
Understanding the ROI of Archiving: Why Data Archiving is a Must do for 2012
Application Strategy and Services
Thu, 02 Feb 2012 20:37:52 +0000 - Schooner has a blog post showing that one node of their product beats 9 nodes of Clustrix’s in throughput. But this reduces everything to a single number, and that’s not everything that matters. If you’ve looked at Vadim’s white paper about Clustrix’s (paid-for) performance evaluation with Percona, you see there is a lot of detail about how consistent the throughput and response time are.
I’d love to see that level of details in any product comparison. A single number often isn’t enough to judge how good the performance is — fast is not the only thing that matters.
I have absolutely no doubts that a single node of Schooner’s product can run like a deer. It isn’t doing any cross-node communication, after all, so it had better be faster than something that blends multiple nodes together into a virtual “single database server.” And I think if the full story were told, it would be a great knock-down drag-out fight. Give us more details, Schooner!
Further Reading:High Performance MySQL, Second Edition: Query Performance Optimization
An ongoing thread of blogs on MySQL performance
Status update on High Performance MySQL
High Performance MySQL Third Edition pre-order available
Sessions of interest at the Percona Performance Conference
Thu, 02 Feb 2012 18:11:32 +0000 - http://collaborate12.ioug.org
Double Down at COLLABORATE 12- The IOUG Forum with Two Ways to Save- and a Chance to Win!
The user-driven Oracle event of the year is fast-approaching, and IOUG wants you to make youreducational experience a sure bet. Between hundreds of cutting-edge education sessions, workshops and legendary Oracle speakers, you’ll return from Las Vegas with valuable knowledge to transform into immediate results for your business. No need to go all in to attend- IOUG is sweetening the pot with ways for you to save big bucks and even pocket some cash while you’re at it. Register today for your chance to win a $200 American Express Gift Card!The deck is stacked at COLLABORATE 12 – The IOUG Forum in your favor.
Register for the conference today with the priority code EM03 and be immediately entered for a chance to add a $200 AmEx Gift Card to your winnings for the week. Treat yourself to some fusion cuisine while in Vegas, catch a show on the strip, or just pocket the prize for a rainy day!
Buy-in to the table at COLLABORATE 12-The IOUG Forum has never been more reasonable. Hotel rates are only going up from here on out, so book your room today to take advantage of the final day of ultra-reasonable rates!
How about adding more savings to the mix on your conference registration?Sign up for a seat at the table with IOUG before February 29 and be dealt a winning hand: save up to $500 on conference registration by booking ahead of the early bird, and we’ll even throw in a bonus, extra day of education($599 value)! Don’t miss your chance to to take advantage of these massive savings . Book your COLLABORATE 12 journey today!
Guarantee your seat at the table today, and save by registering before the Early Bird Deadline! Registration rates start as low as $1,295* for members of IOUG. Resolve to give yourself a fantastic week of Oracle Education, and happy 2012!
* Rate assumes hotel and group discount
Boot Camp Information
Housing
Deep Dive Information
IOUG Registration Benefits
Exhibit at COLLABORATE 12
Session Schedule
Want to sponsor a Deep Dive? Contact Jconlon@ioug.org for more information.
Thu, 02 Feb 2012 17:13:10 +0000 - Thanks to everyone who has already taken part in our survey exploring changing attitudes to MySQL following its acquisition by Oracle and examining the competitive dynamic between MySQL and other database technologies, including NoSQL and NewSQL.
The response has been great and even a quick look at the results makes for interesting reading, particularly in the light of our previous findings which indicated declining MySQL usage.
I am really looking forward to having the opportunity for a deep dive into the results and break out the figures to get a better understanding of the potential impact of alternative MySQL distribution and support providers, as well as NoSQL and NewSQL, on continued usage of MySQL.
The survey results will be made freely available on our blogs, as well as being included in a long format report containing our additional analysis and research related to the MySQL ecosystem and competitive dynamic.
Right now, however, is your last chance to contribute to the survey and get your voice heard. There are just 12 questions to answer, spread over four pages, and the entire survey should take no longer than five minutes to complete. All individual responses are of course confidential.
The survey will close in 24 hours.
Thu, 02 Feb 2012 15:46:46 +0000 - If you’ve tried compiling MySQL/MariaDB/XtraDB from source code on Windows, you may have run into the following error:
C:\GnuWin32\bin\bison.exe: m4: Invalid argument
Now, it is a known bug to receive this error if you install it to a location with spaces in the path. So don’t do that!
But, there are cases when you have not installed it to a path with spaces, and you still receive this error.
I’ve encountered it a number of times, and I’m not the only one (btw, thanks Venu for your existing work-around up to this point!).
…
Problem:
Compile MySQL/MariaDB/XtraDB on Windows and receive the following error during compilation:
C:\GnuWin32\bin\bison.exe: m4: Invalid argument
Troubleshooting:
As you can see, there is no space in the path that is even reported.
Let’s double-check that that is the path returned from the command line:
C:\mariadb-5.3\bld>where bison
C:\GnuWin32\bin\bison.exe
C:\mariadb-5.3\bld>where m4
C:\GnuWin32\bin\m4.exe
Again, both are stored in a location without spaces.
Let’s double-check what CMake reports for the bison path in CMakeCache.txt:
//path to the bison executable
BISON_EXECUTABLE:FILEPATH=C:/GnuWin32/bin/bison.exe
Again, the same path with no spaces.
In the build output (either from Visual Studio or the Command Line), near the “bison.exe: m4: Invalid argument” error, you should see a link to the “build log” for that which failed, which contains additional information.
Opening that file, you’ll see this is the full command that is trying to be run:
C:\GnuWin32\bin\bison.exe -y -p MYSQL --output=C:/mariadb-5.3/bld/sql/sql_yacc.cc
--defines=C:/mariadb-5.3/bld/sql/sql_yacc.h C:/mariadb-5.3/sql/sql_yacc.yy
I recommend trying to run it from the command line yourself.
If you’re already seeing the “bison.exe: m4: Invalid argument” error, then you’re likely going to see it here (which is good for later testing):
C:\mariadb-5.3\bld>C:\GnuWin32\bin\bison.exe -y -p MYSQL
--output=C:/mariadb-5.3/bld/sql/sql_yacc.cc
--defines=C:/mariadb-5.3/bld/sql/sql_yacc.h C:/mariadb-5.3/sql/sql_yacc.yy
C:\GnuWin32\bin\bison.exe: m4: Invalid argument
So what is going on?
Well, thanks to Wlad(!), he recommended I run Procmon to see what it reports.
I downloaded and ran procmon.exe.
Run bison command from command line:
C:\GnuWin32\bin\bison.exe -y -p MYSQL
--output=C:/mariadb-5.3/bld/sql/sql_yacc.cc
--defines=C:/mariadb-5.3/bld/sql/sql_yacc.h C:/mariadb-5.3/sql/sql_yacc.yy
In procmon, search (ctrl + f) for “bison”
Locate an entry (may see many) and look at the paths for the bison.exe executable (it is easy output to read).
The $Path for the bison entry said:
C:\ProgramData\Microsoft\Windows\Start Menu\Programs\GnuWin32\Bison
Aha! For one, that is just a directory with no executables. But for two, and more importantly, it has a “space” in the path.
So, that is the root cause of the problem.
Solution:
Initially, I had run an installer to install GnuWin32 Bison. This added docs shortcuts to the C:\ProgramData\Microsoft\Windows\Start Menu\Programs\GnuWin32\Bison location (and apparently created some registry entry that ultimately was used by VS2008), and links to those docs in my Start Menu.
Personally, I know where the docs are, so I can live without them being in my “Start Menu” (heck, maybe you could even copy them back, but it ultimately needs removed from the registry).
So, if you’re the same way, then this should work for you:
Copy C:\GnuWin32 (and subdirs) to a safe location
Run the bison uninstall (to remove the entry from the registry)
Ensure current C:\GnuWin32 does not exist
Ensure C:\ProgramData\Microsoft\Windows\Start Menu\Programs\GnuWin32 does not exist (those true docs will already be saved in the copy we made anyway, that will be restored to C:\GnuWin32)
Re-boot computer (because stale registry values will remain and thus a restart is necessary – this I also detected with Procmon)
Copy the saved GnuWin32 (and subdirs) back to C:\.
Ensure C:\GnuWin32\bin is in the $PATH for Windows.
Now, re-run your command from the command line (or rebuild – but the command line command is much quicker for testing):
C:\mariadb-5.3\bld>C:\GnuWin32\bin\bison.exe -y -p MYSQL --output=C:/mariadb-5.3/bld/sql/sql_yacc.cc
--defines=C:/mariadb-5.3/bld/sql/sql_yacc.h C:/mariadb-5.3/sql/sql_yacc.yy
C:\mariadb-5.3\bld>
\o/
Once that’s good to go, you’ll know your build will run successfully too (at least it won’t fail with this dreaded m4 error.)
Hope this helps.
Thu, 02 Feb 2012 15:28:23 +0000 - I’ll be speaking on April 11th at 4:30 pm in Room 4 in at the Percona Conference and Expo Talk. The topic will be “Creating a Benchmark Infrastructure That Just Works.”
Throughout my career I’ve been involved with maintaining the performance of database applications and therefore created many benchmark frameworks. At Tokutek, an important part of my role is measuring the performance of our storage engine over time and versus competing solutions. There is nothing proprietary about what I’ve created, it can be used anywhere.
My presentation will cover how I created the benchmark infrastructure at Tokutek:
Hardware and software considerations (including physical vs. virtual)
Selecting benchmarks
Capturing detailed information during the benchmark
Automation
Storing results
Visualization
Trend analysis
Continuous integration (monitoring the performance of future versions)
Self-service (let people get the information they want)
Track: Tools
Experience level: Intermediate
Tokutek is also a sponsor of the show and will have an expo booth. So, I hope to see you at my talk and/or at our booth.
Thu, 02 Feb 2012 15:00:07 +0000 - Let me tell you a very touching story about these awards.
As I said in the comments : Sometimes we think about something and the next minute we did it…
And it exactly what happened, I took my inspiration from the TUAW Best Of 2011 (Yes, I’m also an Apple fan boy, sorry :-)), and a few minutes later, MySQL[plus] awards 2011 were live !
I thought I could have 50 voters, perhaps 100 voters, in my wildest dreams…
But more than 300 voters and more than 4000 views later, the reality is there : You are amazing !
But, no, it wasn’t perfect !
Shlomi said “You’ve got yourself into a bottomless pit!”, and it’s true.
Ronald said “Your options leave a lot out”, and it’s true.
But I tried to offer this overview of the most used tools with spontaneity, bravery and honesty.
And in my heart, I know you enjoyed it (no ?)
It now remains 11 months to prepare the coming awards…
So, final results are available : http://www.mysqlplus.net/2012/01/05/vote-mysqlplus-community-awards-2011
Remember that the real winner is the community !
PS : When I said “Answer with your heart first”, this meant that there was no place for cheaters…
Related Posts :Does mpstat can replace vmstat ?Vote for MySQL[plus] awards 2011 !How to quickly identify queries with pt-query-digest and pt-query-advisor from rules ?Monitoring your monitoring tools (MONyog inside) ![UPDATED] Why you should go to Percona Live (London) ?
Thu, 02 Feb 2012 01:44:00 +0000 - Percona recently evaluated the performance of Clustrix appliances by running tpcc-mysql benchmark at a scale factor of 5000 warehouse. Based on Percona’s blog, each Clustrix appliance node comprises of the following:2x Intel 4-core processors (Xeon)48GB RAM (40GB allocated to InnoDB buffer pool)7x Intel SSD G2 (160GB each), software RAID0The following results are taken from the report for Clustrix (tpcc-mysql new-order transactions/ 10 seconds):For convenience, the above results have been converted to TPM (transactions/minute):You can download the detailed white paper here: Clustrix_TPCC_Percona_White PaperThe SchoonerSQL ComparisonWe were curious to see what a single instance of SchoonerSQL (a full distribution of MySQL/InnoDB) on a single node could compare at the same benchmark scale - 5000 warehouses with the same benchmark, tpcc-mysql from Percona.SchoonerSQL was installed on a system with the following configuration:2x Intel 6-core processors (Westmere)92GB DRAM (72GB used for InnoDB buffer pool)8x OCZ SSDs (200GB each) (can be alternatively replaced with FusionIO or Intel)2x SAS HDDs with NVRAM in RAID controller (for OS and commit log) Result summary: At 64 connections with exactly the same workload conditions, a single SchoonerSQL node measured more throughput than 9 Clustrix nodes.
Wed, 01 Feb 2012 23:12:00 +0000 - Gearman is awesome. If you do not know what it is, its a queue and load balancing system for an arbitrary number of workers which enables distributed computing across many nodes. Some of the same guys who worked on mySQL source worked on Gearman.Feel free to search my blog on other gearman uses.The Problem:We store a lot of stats, make a lot of changes and we want to see the result of the stats in realtime. Our stat system is pretty slick. For each tag increment the application increments a count and group said tag by minute, hour, month with a hash tag numeric representation of the text for compact writes. This means 1 tag write produces 4 SQL statements. We track over 239211 distinct tags at around 10K Writes per second on a single mySQL instance on EC2 in a RAID-10 EBS xtra-large Config (although because EBS mirrors internally I can just raid-0 but I was too scared).Once the mySQL instance hits disk (EBS) our throughput becomes very unstable, possibly slowing down the site.The solution was to defer these writes but how can I do it without building a logging system, aggregator, loader and having a bunch of moving parts? Really I want to only spend 10 mins on this problem and use existing monitoring code. So my 10 min solution: 3 mins to write the code 6 mins testing 55 seconds of patting myself on back 5 seconds to deploySolution Detail:Since Gearman workers connect to the GearmanD server's Job QUEUE and loop for more Jobs. This means program stays in memory for the length of the process (until worker restart). This means I can transform the data in application memory. Since the program is persistently connected to the DB that means I can periodically load the data in chunks.In stead of having 100s of possible concurrent connections doing writes I can control the writes based on the number of workers. Innodb is very fast and consistent at low levels of concurrency (less then 50).Since I can drain the queue from GearmanD and transform the data locally I do not really need to worry about running out of memory on the queue server. The consumer is faster then the producer. I can combine 1000s of writes into a single write.Let's look at some code:<? require_once("includes/config.php"); require_once("includes/DB/EventTrackerDB.php"); class EventTrackerETL { // // keep track of distinct tags // public static $eventTable = array(); // // the next flush // public static $nextWrite = 0; // // keep a stat of total writes // public static $totalEvents = 0; // // total number of events // const MAX_NUMBER_OF_EVENTS = 30000; // // number of seconds to pause // const FLUSH_INTERVAL = 20; // seconds /* * transform all the tags into a sum of the counts entered * @params string $event - the tag being incremented * @params int $count - the supplied count many times its just 1 * @params int $timeEntered - EPOC timestamp * @retuns void */ public static function transform($event, $count, $timeEntered) { // // initialize or increment a tag // if (isset(self::$eventTable[$event])){ self::$eventTable[$event] += $count; } else { self::$eventTable[$event] = $count; } // // flush if we hit the max number of events // if (sizeof(self::$eventTable) > self::MAX_NUMBER_OF_EVENTS){ return self::load(); } // // flush if its our time // if (self::$nextWrite < time()){ return self::load(); } return; } /* * flush the stored tags to the database * @returns void */ protected static function load() { // // write transformed events to the db // $thisRun = 0; foreach(self::$eventTable as $event => $sum) { EventTrackerDB::singleton()->updateEvent($event, $sum); $thisRun++; self::$totalEvents++; } $msg = "EventTracker write complete $thisRun events this run and a total of " . self::$totalEvents . " events written so far"; Debugger::log("OT", $msg); // // re-init // self::$nextWrite = time() + self::FLUSH_INTERVAL; self::$eventTable = array(); } }?>In summary with gearman I am able to process 250K events in seconds. The queue never builds up and there is special code to handle kills (not SIGKILL).
Wed, 01 Feb 2012 22:17:38 +0000 - Instead of the usual text-heavy blog posts that appear here, I thought it would be fun to mix things up and do a screencast showing exactly how easy it is to upgrade MySQL to MariaDB:
Some notes:
The laptop I’m using had MySQL 5.1.55 installed with one database (apart from the system database). Installing MariaDB does not impact existing data in any way and once the install completed I had instant access to my data.
As part of the install you are given the option to set a new password for the root user. I choose to do it in the video, but you don’t need to. If you leave the password field blank the root password will not be changed. Other database users are preserved, of course.
As with any database upgrade, before doing this to a production system you should have backups and test.
Links:
Links shown or mentioned in the video:
http://kb.askmonty.org/en/installing-mariadb-deb-files
http://downloads.askmonty.org/mariadb/
http://downloads.askmonty.org/mariadb/repositories/
and of course: http://mariadb.org/
Comments?
What do you think? Should we make more screencasts? If so, what would you like to see demonstrated?
Wed, 01 Feb 2012 21:56:01 +0000 - I will be in Austin for SITE presenting Teaching Database Concepts with Open Source Software on Wednesday, March 6th. So if you are a teacher attending SITE, please come attend my session. And bring your boots so we can hit 6th Street afterwards!
Wed, 01 Feb 2012 17:03:34 +0000 - An attendee to Espen’s recent webinar asked how to check tables for corruption. This kind of ties into my recent post on InnoDB’s handling of corrupted pages, because the best way to check for corruption is with CHECK TABLES, but if a page is corrupt, InnoDB will crash the server to prevent access to the corrupt data. As mentioned in that post, this can only be changed by changing InnoDB.
So how are you supposed to check for corruption that might be introduced by bad hardware, a bug, or so forth?
It’s a great question. The answer I would give for most cases is “check your backups for corruption instead of your live server.” You need to do this anyway — a backup that isn’t checked is a ticking time bomb. You need to verify (at least periodically) that your backups are recoverable.
The usual procedure goes like this: copy your backup somewhere, start a server instance on it, and run CHECK TABLES. You can use the mysqlcheck program to do this conveniently.
You could also use innochecksum, which doesn’t require starting the server. But it only verifies that each page’s checksum matches the page’s data, it doesn’t do all the other checks that are built into InnoDB (making sure that the LSNs are sane, for example).
How often? As often as possible. Some people refresh their dev/staging environment every day with last night’s backup, which is a great way to make failures obvious, as long as you verify that it truly does happen (e.g. what if it fails and you keep running with yesterday’s without knowing it?). If you can’t do it daily, then weekly is perfectly acceptable to most people. I’m not saying a specific interval should/ought to be your goal, I’m just remarking on what a lot of people seem to feel good about.
Wed, 01 Feb 2012 16:22:00 +0000 - Continuent is increasing its presence in Europe to offer our solutions and services for the open source database business sector in the region. VNC is Continuent's new partner in Germany, Austria and Switzerland (DACH). Read the entire press release here.Continuent and VNC are hosting a live webcast demonstrating advanced MySQL and PostgreSQL replication and clustering with Continuent Tungsten
Wed, 01 Feb 2012 13:00:14 +0000 - In various discussions I have expressed that I think that there are way too many options and variables in MySQL (and it gets worse for every new release). There are simply too many to know and remember them all. If you manage a server yourself it is (probably) a minor problem as you should know the options you use and not use those that you don’t know. But if you are connected to a server that you don’t manage yourself you may get surprises if some rare option you are not familiar with has been set.
The last one I came across is ‘skip-character-set-client-handshake’. MySQL documentation http://dev.mysql.com/doc/refman/5.5/en/server-options.html says “To ignore client information and use the default server character set, use –skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0.”
Now what is the idea in having a recent server ‘behave like MySQL 4.0′ ? There could have been some compatibility concerns in early 4.1/5.0 releases, but now (after 8 years) is it stilll relevant to have? I don’t think so.
The ‘chain breaks’ in the case where a client (we are talking about clients compiled with the C-API or a connector having similar functionalities as mysql_options() in the C-API – what means that it is not PHP we are talking about!) requires character_set_client to be utf8. The client will execute “SET NAMES UTF8;” after connection just in case the server has a non-utf8 default charset. It works fine. SET NAMES works as expected. So far so good. Futher the client is using the ‘reconnect flag’. Reconnects may take place after connection was lost in several cases (client was idle for longer than wait_timeout, some tunnel system server (SSH tunnel, VPN tunnel) disconnected client and server, intermittent hardware or networking failures etc.). The important point to note here is that reconnect is handled by the API transparently for the ‘parent code’ where it is linked. Thus the client cannot know when to execute “SET NAMES UTF8;” again (as a regular SQL statement). The client will instead set the connection charset to utf8 in mysql_options() so that utf8 also will also be character set for the connection after a reconnect. But with the ‘skip-character-set-client-handshake’ -option set, it simply has no efffect (the option is intended to work like this).
As a consequense there is no way not to have non-ASCII characters garble in the client after automatic reconnection in such environment as far as I can see.
We had such report recently. It took some time to figure out the reason. There is also an old related bug here http://bugs.mysql.com/bug.php?id=11972 btw, but this is fixed long ago even though I first thought it might have found its way back to PerconaServer (what was used in this particular case), it was not the case.
It is fair enough to have compability options with older servers for some time. But is it still after 8 years? Also in a case like this where it breaks the way a client and the server is intended to ‘negotiate’ multiple charsets?
More old options to get rid of? I am a collector of such!
Tweet
Wed, 01 Feb 2012 09:13:00 +0000 - Shlomi Noach has made a couple of posts (here and here) on Visual Editors vs. commandline for MySQL working with SQL statements. All in all, I tend to agree with him in many respects, but I am surprised that he recommands MySQL Query Browser, as this is the editor that breaks one of the things that seems to annoy him (and me) most: Every SQL statement that is issed uses it's own connection. Which is not to say others may feel differently about it.As for myself, I started developing my own MyQuery tool because there are things you just cannot do from the commandline, and there are things that are really complex and non-inituitive to do from the commandline, and you need a better tool than the commandline. Again: This is not to say that there is something wrong with the MySQL commandline, it really should not be full featured, but it should run all SQL commands with ease.So, what was I missing in the commandline MySQL that led to the development of MyQuery, and project that I am still working on, on and off? Mainly, using the commandline makes it REAL hard to develop and debug scripts. That was the main reason! At the time that I released MyQueru 1.0, I was involved in a project that required A LOT of SQL scripting. And if you have a big, big SQL script, then you know what happens: The scripts runs for a few 100 lines, and then there is an error or a typy, and the commandline just keeps processing until we get an error, and then it stops (alternatively, you use --force, in which case you usually get truckloads of errors, and even if youjust get one error,it gets lost among all the other errors).OK, so the script failed with an error, then you just fix the error and run it again? Right? Well, that approach assumes that your script is idempotent, which isn't always possible and is at least difficult in many cases. Wouldn't it be neat to have the script running stop when there is an error, allow you to fix the error, and then keep running from where the error occured? That is exactly what you can do with MyQuery.Alternatively, here is another script running scenario:You have a script that takes forever to run. You might have the odd error in there, but you can fix them afterwards, just run the script please! But also tell me about any errors in there, so I can fix them! OK, MyQuery does this too. You can run with force in MyQuery also, but the errors are should in a special errors tab. After the script has finished running, you can see the errors in the error output tab, and that is not intermixed with any status printouts or anything. And yes, click on an error text and you get to that position in the script.Using SQL to generate SQL? Yeah, you can do that with the MySQL commandline, but MyQuery makes it far easier.MyQuery has a bunch of other neat features, like the ability to save and store blob data to disk (doing that from the commandline is somewhat possible, but only with many limitations. INTO DUMPFILE only works for single row for example), extend the interface with custom tools, using SQL statements, or the MyQuery API and many other things (One supplied tool will nicely break up a long SQL statement into several indented lines, simple but useful when working with big SQL statements). That MyQuery has a colour-coded editor with a very flexible setup is also a bonus that makes it easier to work with.Actually, the reason I started developing MyQuery is probably still the best reason to use it: To run scriripts./Karlsson
Wed, 01 Feb 2012 08:19:12 +0000 - My previous post drew some attention, and in particular two comments I wish to relate to. I also wish to make a more fine-grained observation on visual editors.
One comment is by Peter Laursen, who rejected the generalization in my post, and another by wlad, who was harsher (but to the point), suggesting my post translated to "I don't know it, therefore it sucks".
I must have delivered the wrong message, since apparently people read my post as "don't use visual editors, they are bad for you", which is not what I intended to say, nor is it what I actually think. I took a very specific aspect of visual editors and commented on that. My comment should not extrapolate to "anything about visual editors is bad".
I don't think people should only be using the command line, and am not in the least interested in vi/emacs/eclipse/netbeans kind of wars. Of course everyone should be using whatever works best for them in terms of productivity and ease of use. And of course visual editors have great advantages over command line.
I'm sorry, it would be childish to assume otherwise, and extra-extra-extrapolation to suggest that was my hidden meaning.
Reflections on Peter's comments
I can see that my post was harsh on visual editors, and agree one cannot just generalize on the dozens of editors available. I have used a few tools, and only to some extent, since I personally feel more comfortable with command line, and so I cannot possibly generalize on all tools. Nor are they all equal.
My point is, there is one de-facto tool, which is the command-line, for which there are no hidden assumptions. For which I don't need to investigate whether a new connection is being opened, or if my query is somehow modified, or... I guess one can say "hey, my tool does nothing of the sort and acts just like the command line". I'm not arguing. The visual editor is an additional layer between you and the server (the mysql command line client is in itself a layer, but a very transparent one, a very low level one), one which needs to take care of visual issues, such as display space, GUI components, widgets not running out of memory, responsiveness, good looks. These, one way or the other, add complexity to the discussion between yourself and your database.
One can say "it's all configurable, there's good documentation, you should know the tool you are using". To some extent the same can be said of the command line client. Here is an observation of mine, please consider it as user input: many (I don't know if most) users will not bother modifying the settings. Many will feel uncomfortable with turning checkboxes on and off, or modify thresholds. It is similar in essence to users not modifying the default InnoDB settings for the MySQL server, working with 8MB of buffer pool.
Anyway I agree my post was over-generalizing on the criticism of visual editors. Sorry for that. It should have been more specific on the advantages of command line.
Reflections on wlad's comments
Since I wanted to concentrate on the command line issue, I didn't elaborate on other stuff. For example, I didn't specify on what type of trainings I ask students to close down their visual editors and open up a command line. Perhaps one could read as "from this moment on, and for the entire duration of the training, no one should be using visual editors, and anyone who does will find himself/herself out of the class". I really didn't think to elaborate on that, and even now - wow, it will take me ages to tell the full story. So I will be extra-brief:
I do make that suggestion. I do that when I start discussion of keys & execution plans. I don't enforce that. I don't mind which tool a developer uses. I actually tell them about various GUI tools they can use (naming the ones I see as most popular among other students).
But I also do not turn my training to "I'm going to do this session using the same tools you are using just because you are using them and you are in this class".
There is a multi-layered reasoning for that:
Many times there is a mixture of tools in the classroom. I can't just pick up on one. The command line is the most common tool everyone can agree upon. I sometimes demonstrate on MySQL Workbench since many are familiar with it being an "official" GUI tool by the same company which makes the database.
If I dwell on the particulars of a GUI tool, this consumes precious time that must be used to explain the EXPLAIN and talk about INDEX in depth. I just don't have that time on training. The command line consumes very little time for me.
I also don't see that I should demonstrate using commercial products. I have nothing against commercial, and on occasion I buy commercial, and I certainly do not slight a commercial product just because it's commercial, but I prefer to sponsor open source. If students have a commercial product, I don't see this is my duty to walk them through the product.
There are actually a few more reasons; operating systems, installations, assumptions on installed software, strict system administrators who would not installed anything I would tell them, projectors, font sizes, contrast of colors on projected image, ... Believe me, I've been doing this for years, there are many factors to any small decision in class.
Last, I wish to relate to wlad's "I don't know it. It sucks" attribution to my post.
It is true there is not one single SQL GUI tool I know bottoms up. And the status is deteriorating, since I've long since decided my personal preference is command line. This makes me a non-expert, no-authority on any GUI tool. I actually present myself as such in class. This does not lower the quality of the training; it's an aspect outside the training (for the reasons I mentioned above). I therefore agree to "I don't know it".
Now, I strongly believe I do not make the connection to "therefore it sucks". I think that's an exaggerated extrapolation of what I said. But it is such an overwhelming statement about myself for me to read, that I will keep it in mind. It's a good thing to keep in mind, not only on GUI tools, but also in real life.
Wed, 01 Feb 2012 03:06:23 +0000 - The first Central Virginia MySQL Meetup was a nice little howdy-do, and as a test flight, I think it showed that the bird can get off the ground quite nicely. So, with the generous help of our meeting host Meddius, we’re going to do it regularly on the 3rd Wednesday of every month. The next event is already scheduled — I will be talking about high availability options for MySQL.
I’m interested in having outside speakers. Anyone who’d like to come and present something MySQL-relevant, please feel free to email me, or contact me via the Meetup page with the “suggest a Meetup” feature. If you’re traveling from outside the area, the airport is CHO, and it’s about 30 minutes away. Amtrak is also very convenient. I’m happy to chauffeur you, and can help you find lodging too.
I will not try to steer overly much, but I hope that this turns into a group where people introduce themselves, mention job openings and other news of interest, and so on.
There are also a couple of related meetups nearby that I want to promote: NOVA MySQL at AOL’s headquarters led by Mike DelNegro, DevOps DC at CustomInk’s offices led by Nathen Harvey, and one I haven’t been to yet but hope to attend soon, Shenandoah Ruby Users Group in Harrisonburg near Rosetta Stone’s headquarters, led by John Paul Ashenfelter.
Further Reading:New Central Virginia MySQL Meetup
Speaking at MySQL Meetup in Northern Virginia
Free DevOpsDC meetup on Tuesday
News on MySQL Cacti Templates
An ongoing thread of blogs on MySQL performance
Wed, 01 Feb 2012 00:57:18 +0000 - We are pleased to announce that MySQL Enterprise Monitor 2.3.9 is now available for download on the My Oracle Support (MOS) web site as our latest GA release. It will also be available via the Oracle Software Delivery Cloud in approximately 1-2 weeks. This is a maintenance release that fixes a number of bugs. You can find more information on the contents of this release in the changelog:
http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-news-2-3-9.html
You will find binaries for the new release on My Oracle Support:
https://support.oracle.com
Choose the "Patches & Updates" tab, and then use the "Product or Family (Advanced Search)" feature.
And from the Oracle Software Delivery Cloud (in about 1-2 weeks):
http://edelivery.oracle.com/
Choose "MySQL Database" as the Product Pack and you will find the Enterprise Monitor along with other MySQL products.
If you haven't looked at 2.3 recently, please do so now and let us know what you think.
Thanks and Happy Monitoring!
- The MySQL Enterprise Tools Development Team
Tue, 31 Jan 2012 18:59:34 +0000 - Why read stale data from an asynchronous MySQL replica (slave)? Fetch it from a local cache instead! Good for the clusters overall load, good for your applications performance. And, possible with PECL/mysqlnd_ms 1.3, the replication and load balancing plugin for PHP MySQL users.
The idea is simple
Any application using asynchronous MySQL replication must be capable of handling stale results read from a slave (replica) that is lagging behind the master. The quality of service that the application needs from the database cluster is low. If an application explicitly states the minimum service quality it needs, the underlying systems can adopt to it. That’s a cool thing, because the underlying systems don’t need to do more work than necessary. In the case of a PHP MySQL user, the first underlying system is the database driver library, which is mysqlnd.
Tell mysqlnd that you can deal with data that is as old as five seconds. Then, mysqlnd can search a matching slave lagging no more than five seconds or even replace the slave access with a TTL-based cache access. Cache? Sure, PECL/mysqlnd_qc, various storage backends including main memory, user-defined, APC and Memcache … have a look at the quickstart.
It exists…
The first step is done. The development trees of PECL/mysqlnd_qc 1.1 and PECL/mysqlnd_ms 1.3 have been de-stabilized. A first, crude approach to make the query cache and replication plugin work transparently together exists.
./configure --enable-mysqlnd-ms --enable-mysqlnd-ms-cache-support
PECL/mysqlnd_ms 1.2 introduced the mysqlnd_ms_set_qos() function for setting the service level required from the cluster.
mysqlnd_ms_set_qos($connection,
MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL,
MYSQLND_MS_OPTION_CACHE,
5);
If you set the service level as shown above, PECL/mysqlnd_ms does never read from a slave that reports itself to be lagging more than 5 seconds behind the master. If no matching slave is found, PECL/mysqlnd_ms picks the master. Additionally, PECL/mysqlnd_ms tries to cache all slave queries for up to 5 seconds. The cache logic is so, that you never get data older than 5 seconds.
The initial cache logic
First, PECL/mysqlnd_ms asks PECL/mysqlnd_qc if the query is already in the cache. If so, PECL/mysqlnd_ms stops searching slaves, continues working and tries to fetch the results from the cache in the following. If fetching from cache fails, which should happen rarely, it reads the result from a master.
In case the query is not cached yet, PECL/mysqlnd_ms searches for all slaves that lag no more than 5 seconds. The cache TTL is reduced by the highest lag found. If, for example, there are two slaves, lagging 2 and 3 seconds behind, the cache TTL is set to 5 - max(2, 3) = 5 - 3 = 2 seconds.
MYSQLND_MS_OPTION_CACHE
Slave lag
TTL
Slave 1
5
2
3
Slave 2
5
3
2
Then, slave selection continues, for example, load balancing is done. At the end of the chain eventually one slave has been selected. The query is run on the slave and put into the cache for 2 seconds.
How the plugins work together
PECL/mysqlnd_ms does control the cache, PECL/mysqlnd_qc, through SQL hints. PECL/mysqlnd_ms sets exactly the same SQL hints that are also available to the user, as described in the PECL/mysqlnd_qc manual.
/*qc=on*//*qc_ttl=2*/SELECT id FROM test
… and tomorrow: a sneak preview of the new built-in pattern based caching for PECL/mysqlnd_qc 1.1.
Happy hacking!
@Ulf_Wendel
Tue, 31 Jan 2012 15:43:09 +0000 - Following
the success of our OTN MySQL Developer Day in London last October, we are
running additional MySQL Developer Days in Europe!
Join us in:
Germany:
Frankfurt - February 9th - Check out
the agenda
and register here!
France:
Paris - March 21st - Check out
the agenda
and register here!
The MySQL
Developer Day is a one-stop shop for you to learn all the essential MySQL
skills. In this free, one-day seminar, we will cover everything you need to
know to successfully design, develop, and manage your MySQL databases. You'll
also learn the guidelines and best practices in performance tuning and
scalability. Space is limited so register now!
Additionally,
we will also be holding a MySQL High Availability Seminar in Stockholm on
February 23rd. During this free Oracle
seminar we will review the various options and technologies at your disposal to
implement highly available & highly scalable MySQL infrastructures, as well
as best practices in terms of architectures. Register
Now!
And…remember
our MySQL
Embedded Online Forum today from 9.00 am to 12.00 pm PT!
Tue, 31 Jan 2012 15:04:50 +0000 - The binary version for MySQL Cluster 7.1.19 has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://support.oracle.com/ (commercial version)
A description of all of the changes (fixes) that have gone into MySQL Cluster 7.1.19 (compared to 7.1.18) will appear in the 7.1.19 Change log.
Tue, 31 Jan 2012 01:07:32 +0000 - I’m about to head to Paris to present at the February meetup of the MySQL User Group in Paris, France. It happens 1st February from 6-8pm at the Patricks Irish Pub. Its free to attend, and I understand that SkySQL keeps this event afloat.
I’m also heading to my first FOSDEM right afterwards and will definitely hang out at the MySQL & Friends Devroom. There is an amazing lineup of speakers, with all talks being about 25-30 minutes, it looks like it is going to be a lot of fun. To boot, Michael “Monty” Widenius will also be there, so expect lots of Salmiakkikossu.
If you want to keep track of where Monty Program folk are going to be to talk about MariaDB, make sure you’re subscribed to our news page, which also includes important release information. Pretty much every conference that we plan to attend (and have attended) is at the conference page.
I am looking forward to meeting & learning from many MariaDB/MySQL users!
Related posts:Using MariaDB in production?
OpenSUSE users have a choice of database now!
Plugins & Storage Engines Summit for MySQL/MariaDB
Mon, 30 Jan 2012 23:09:58 +0000 - MySQL Embedded Online Forum
Tuesday, January 31, 2012 9:00AM PST – 12:00 PM PST
Think You Know MySQL? Get the Facts.
MySQL is even better than you think. According to the experts, it’s easier to use and it’s more capable than most ISVs / OEMs realize, and by taking a few tried-and-tested steps before shipping your MySQL-embedded products, you can lower your product’s costs and increase its performance.
Join our expert-created and delivered MySQL Embedded Online Forum to learn:
Why MySQL is a great embedded database for startups as well as the largest software, hardware, and appliance vendors in the world, and how its features ensure costs remain low throughout an application’s life cycle.
MySQL installation options that require minimal or zero end user effort and how to easily build them into your application
How to secure MySQL embedded in applications, appliances, and devices
Tips to simplify your integration with MySQL using the most popular MySQL Connectors and guidance on selecting the settings that will maximize your application’s performance
Why MySQL delivers excellent performance and how to go beyond the default settings to optimize MySQL’s queries, reporting, and search capabilities
It Just Works! The Beauty of MySQL as an Embedded Database
Craig Sylvester
Principal MySQL Sales Engineer, Oracle
Building MySQL Embedded for Simple Installation and Security
Craig Sylvester
Principal MySQL Sales Engineer, Oracle
Getting the Most Out of MySQL Connectors for Better Performance and Streamlined Integration
Matt Lord
Senior Principal MySQL Support Engineer, Oracle
Better than Great: MySQL Embedded Performance and Reporting
Alexander Rubin
MySQL Technical Consulting Manager, Oracle
Mon, 30 Jan 2012 19:32:00 +0000 - A recent trip to the SCALE Conference raised a few common questions. One of them was: does Oracle does offer a MySQL 5.5 exam ? I hope the links below help all of those that asked and others who might have the same question.MySQL for Beginners (SQL-4401) This exam has been validated for MySQL 5.0, 5.1 and 5.5. They also offer a MySQL 5 Certified Associate ExamOracle also now offers, MySQL Specialization Criteria.Related Press Release :New Oracle PartnerNetwork (OPN) Specialization for MySQL Enables Partners to Showcase Expertise
Mon, 30 Jan 2012 18:55:17 +0000 - I am honored to have been selected to give a 3-hour tutorial on white-hat Google hacking and other MySQL security topics at the 2012 Percona Live: MySQL Conference and Expo. The tutorial schedule can be found here and for more about the presentation I will be giving, you can see Google Hacking MySQL and More MySQL Security. I am also pleased that Percona has given a discount code for OurSQL: The MySQL Database Community Podcast listeners - 10% off any ticket price with code PL-pod.
The discount is on top of early bird prices, so you get two discounts this way!
I cannot wait for the conference -- all the tutorials and sessions look very top-notch.
Mon, 30 Jan 2012 18:19:38 +0000 - I’ll be presenting at MySQL Meetup in Raleigh,NC February 21,2012. The talk with be about Optimizing MySQL Configuration which I believe is a great topic for my first talk at this meetup group as it covers something every MySQL user has to deal with, also being something both beginner and advanced MySQL Users can learn a lot from. I’ll also bring High Performance MySQL 2nd edition for a drawing as well as exclusive discount codes for MySQL Conference and Expo 2012 in Santa Clara. See you there.
Mon, 30 Jan 2012 16:29:03 +0000 - MySQL Stored Routines (functions and procedures) are not only used for improving performance but also they’re handy when it comes to enhancing security and restricting user access. This post briefs...
Mon, 30 Jan 2012 15:56:05 +0000 - Tuesday, February 21, 2012
1:30 PM – 7:00 PM
(registration starts 1:00 p.m.)
Raffles Hotel Singapore
Casuarina Suite B, Level 3
1 Beach Road, 189673, Singapore
Please Register Online
The MySQL Developer Day is a one-stop shop for you to learn all the essential skills about MySQL. In this free, half-day seminar, we will cover everything you need to know to successfully design, develop, and manage your MySQL databases. You’ll also learn the guidelines and best practices in performance tuning and scalability.
Attend this event and gain the knowledge to:
Develop your new applications cost-effectively using MySQL
Improve performance of your existing MySQL databases
Manage your MySQL environment more efficiently
Don’t miss this exclusive opportunity to network with your peers, and hear the tips and tricks from the MySQL experts at Oracle. No matter you’re a DBA or a developer, come find out how you can make the best of MySQL in your IT infrastructure or in your commercial products and applications.
Networking reception is sponsored by Avnet after the session from 5:00 p.m. – 7:00 p.m.
Please join us to network with your peers while enjoying light refreshments!
Mon, 30 Jan 2012 15:04:34 +0000 - Students in my training classes usually prefer to use some kind of visual editor for MySQL. Typically this would be the software they're using at work. Sometimes they just bring over their laptops with the software installed. Or they would use MySQL Workbench, which is what I usually have pre-installed on their desktops.
I see MySQL Workbench, SQLyog, Toad for MySQL, or several more.
I always humbly suggest they close down their software and open up a command line.
It isn't fancy. It may not even be convenient (especially on Windows, in my opinion). And repeating your last command with a minor modification requires a lot of key stroking. Or you would copy+paste from some text editor. Most students will give it a shot, then go back to their favorite editor.
Well, again and again I reach the same conclusion:
Visual editors are not as trustworthy as the command line.
Time and again students show me something on their editor. Behavior seems strange to me. Opening up a console shows a completely different picture.
Things like:
The visual editor would open a new connection for every new query (oh, so the @user_defined_variable I've just assigned turns NULL, or the TEMPORARY TABLE disappears).
The visual editor will only show 1,000 results, via LIMIT 0,1000. "But the same query runs so much faster on my machine!". Well, sure, a filesort of 1,000,000 rows that can satisfy the first 1,000 will quit early!
The visual editor shows table definition graphically. "I didn't realize the index did(n't) cover this and that columns. I didn't realize it only covered first n characters of my VARCHAR.". That's because you can't beat SHOW CREATE TABLE, the definite table structure description.
The visual editor allows for export/import/copy/transfer of tables and rows with just one click! "Why is it so complicated in the command line to purge 1,000,000 rows from a table?". Ummm, did you realize the visual editor would typically use a naive approach of doing everything in one huge transaction?
The visual editor is smart. But sometimes you don't want smart. You just assume simple. I personally take great precaution with smart solutions. Luckily, with scripts you have so much greater control (i.e. command line options, "dry-run" mode, etc.) that I have greater confidence in them.
I do like it when a visual editor plays it both smart and safe, in such way that before doing its smart work it actually presents you with the query it's going to issue. Which is why I always considered MySQL Query Browser (now replaced by Workbench) to be the visual editor of choice in my classes.
But, at the end of the day, I strongly believe: if you don't know how to do it with command line, you can't really know how it's done.
Mon, 30 Jan 2012 14:57:26 +0000 - Dear Kettle fans,
Today I’m really excited to be able to announce a few really important changes to the Pentaho Data Integration landscape. To me, the changes that are being announced today compare favorably to reaching Kettle version 1.0 some 9 years ago, or reaching version 2.0 with plugin support or even open sourcing Kettle itself…
First of all…
Pentaho is again open sourcing an important piece of software. Today we’re bringing all big data related software to you as open source software. This includes all currently available capabilities to access HDFS, MongoDB, Cassandra, HBase, the specific VFS drivers we created as well as the ability to execute work inside of Hadoop (MapReduce), Amazon EMR, Pig and so on.
This is important to you because it means that you can now use Kettle to integrate a multitude of technologies, ranging from files over relational databases to big data and NoSQL. You can do this in other words without writing any code. Take a look at how easy it is to program for Hadoop MapReduce:
In other words, this part of the big news of today allows you to use the best tool for the job, whatever that tool is. You can now combine the large set of steps and job entries with all the available data sources and use that to integrate everything. Especially for Hadoop the time it takes to implement a MapReduce job is really small taking the sting out of costly and long training and testing cycles.
But that’s not all…
Pentaho Data Integration as well as the new big data plugins are now available under the Apache License 2.0. This means that it’s now very easy to integrate Kettle or the plugins in 3rd party software. In fact, for Hadoop, all major distributions are already supported including: Amazon Elastic MapReduce, Apache Hadoop, Cloudera’s Distribution including Apache Hadoop (CDH), Cloudera Enterprise, EMC Greenplum HD, HortonWorks Data Platform powered by Apache Hadoop, and MapR’s M3 Free and M5 Edition.
The change of Kettle from LGPL to Apache License 2.0 was broadly supported by our community and acts as an open invitation for other projects (and companies) to integrate Kettle. I hope that more NoSQL, Big Data and Big Search communities will reach out to us to work together to even broaden our portfolio. The way I see it, the Kettle community just got a whole lot bigger!
Where are the goodies?
The main landing page for the Big Data community is placed on our wiki to emphasize our intention to closely work with the various communities to make Pentaho Big Data a success. You can find all information over there, including a set of videos, PDI 4.3.0 preview download (including Big Data plugins), Hadoop installation instructions, PRD configuration information and much more.
Thanks for your time reading this and thanks for using Pentaho software!
Matt
Mon, 30 Jan 2012 14:35:24 +0000 - Since one of our users has encountered a problem when modifying data via Data Editor, we decided to bring such a situation to our users’ attention.
Let’s assume you have the following table:
CREATE TABLE dept(
DeptNo INT(10),
DName VARCHAR(14),
loc VARCHAR(13),
Salsum FLOAT
);
Let’s execute the following query in the SQL document:
SELECT DName, loc FROM dept WHERE DeptNo = 20;
Now if you change any of the values of the first record in Data Editor, the changes will be applied to all records of the original table that have ‘DName’ = ‘Research’ and ‘loc’ = ‘Dallas’.
It happens because when dbForge Studio for MySQL generates the internal UPDATE statement, only the fields returned by the previous query are included in the WHERE clause of the UPDATE statement. In our example DeptNo = 20 that was stated in the WHERE clause of the SELECT statement was not included in the internal UPDATE statement as there was no the ‘DeptNo’ field in the grid when modifying data.
So that if you’re planning to modify data that will be returned as a result of the SELECT statement in the grid, we suggest you to make sure that all the fields that are specified in the WHERE clause of the SELECT statement are also specified in the SELECT clause of the statement.
In our example, you have to specify the ‘DeptNo’ field in the SELECT clause of the statement.
SELECT DeptNo, DName, loc FROM dept WHERE DeptNo = 20;
Now if you modify any of the values of the records in Data Editor, the above actions will exclude accidental changes in the records that are not displayed in the grid.
Finally, to avoid such problems that may occur when modifying data that was returned as a result of the ‘SELECT’ statement inside of which the ‘WHERE’ clause is used, make sure you’re specifying the ‘*’ in the ‘SELECT’ clause or at least all the columns listed in the ‘WHERE’ clause of the statement.
Mon, 30 Jan 2012 10:16:19 +0000 - Don’t share root user password and mysql.user table acess with anyone till you have full trust on it. Because that encrypted password is real password in MySQL so if anyone knows that than he/she can easily login with any user … Continue reading →
Sat, 28 Jan 2012 20:00:18 +0000 - News/Feedback
Registration for Percona Live: MySQL Conference and Expo is open! The conference is from Tuesday, April 10th through Thursday, April 12th. Use code PL-pod and save 10% off the early bird prices!.
The next free Oracle Technology Network Developer Day for MySQL will be Thursday, February 9th, 2012 in Frankfurt, Germany. http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=148478&src=7314534&src=7314534&Act=240
read more
Fri, 27 Jan 2012 22:35:44 +0000 - Pluggable M3 (Monitis Monitor Manager) Framework
Who needs an introduction about M3? – Perhaps no one!
After gaining some reputation with M3, providing extra-easy integration of any monitor into Monitis it was time to take it to the next level.
Generally speaking, the work flow of M3 was described in detail in this article.
After some thought and design, we’ve decided it’d be best if M3 was pluggable. Pluggable in terms of being able to easily add execution and parsing plugins.
The interface and behavior of M3 stayed exactly the same, however now it is much easier to obtain data from any source and parse the data the way you want it.
Saying that, it was time to put the new design for a test. We tried to integrate the DBI support into M3.
Guess what – it was much easier than expected!
The general idea of DB integration with Monitis
DB (as in Database) integration with Monitis has shown to be invaluable. Being able to extract application related data from a DB and load it to Monitis means you can graph anything going on in your database related application. Monitoring your DB is an integral part of server monitoring in today’s world.
Be it maximum number of users at peak hours, gathering statistics about record usage or just checking if your DB is serving requests quickly enough – graphing performance and uptime. Your imagination is the limit – considering your application is DB related of course.
How it’s done – the technicalities beyond
The sharp-eyed among you would notice that M3 got beefed up with a directory named “Execution” and a directory named “Parsing”.
These directories hold the plugins M3 uses for each.
All that I had to do to add DBI support is to create a new execution plugin called “DBI.pm” and fill it up with code from the Monitis DBI module which already existed.
And that my friends, that, was easy. Mission accomplished – Pluggable M3 eases the job for us.
Another way to integrate MySQL
MySQL could have been integrated previously into M3 by executing mysql and feeding it with a query, having something similar to:
<exectemplate>echo "select count(*) from mysql.user;" | mysql</exectemplate>
This form of execution will invoke the ‘mysql’ executable, feeding it the above query. Execution of this will result in the response of the query being returned with some other useless output.
Please avoid this form of execution, as now DBI support is fully integrated into M3. In the next paragraph I’ll show how it should be done.
Lets fetch
While conveying ideas about M3, I decided to prefer to provide very simple examples of operation, however, I encourage you, the end user to let your imagination go wild, adding interesting counters which show how your application really perform and behave.
In this example we’ll just count the number of users in an installed MySQL database. An example anyone should be able to imitate and then scale to his own need.
We’ll examine the following example. I suggest opening the mentioned link in a new window while continuing the read.
Looking at this example it is quite straight-forward to understand. The query to execute is over there, together with some other parameters which are mandatory to connect to a database, such as the database name, user name, password, etc.
An explanation should be provided however for the odd db_statistics parameter. Those of you who have read the HTTP extraction article about M3 would have probably remembered the http_statistics parameter. Then, the db_statistics one does more or less the same, providing some meta counters for the query:
How long did the query take to process (in seconds)
The success/failure of the query (1 for success, 0 for failure)
I encourage to do the parsing of query results, or actually the lack-of-parsing of query results in a way that takes the whole output returns from the query, hence the regular expression of (.*).
If your query returns more than what you need, please tune your query better.
Test your execution with:
# ./DryRun.pl dbi_sample.xml DB: 'root@DBI:mysql:mysql:localhost', Query: 'select count(*) from mysql.user;' at MonitisMonitorManager.pm line 223 Matched '(.*)'=>'6' in '6' at MonitisMonitorManager.pm line 237 OK This is a dry run, data for monitor 'Sample MySQL Monitor' was not really updated. at ./DryRun.pl line 18
It should work. Then you can work your query to fit your own needs.
Next in line
We’re looking for some more new ideas to add as plugins for M3. If you have any idea about any plugin (execution or parsing plugin) that you would like to see in M3 – we want to hear you!
Integrating software into the powerful Monitis has never been easier, now that you have M3!
Monitis can monitor anything! Follow us also on Twitter and GitHub.
Share Now:
Fri, 27 Jan 2012 20:40:00 +0000 - Don't miss these talks at MySQL Users Conference:Tuesday, April 10MySQL Replication 101, 9:30 am -12:30 pm in Room 3Wednesday, April 11What's new in MySQL 5.5 and 5.6 Replication, 11:00 am - 12:00 pm in Room 7Be a Data Management Hero with Good Backups!, 1:00 pm - 2:00 pm in Room 6One to Many: The Story of Sharding at Box, 1:00 pm - 2:00 pm in Room 3Build simple and complex replication
Fri, 27 Jan 2012 19:32:00 +0000 - As Keith announced today, there are two more Oracle ACE Directors for MySQL expertise. In case you are wondering how an ACE Director compares to a regular ACE, here is an overview and some FAQ.
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.
Working with MySQL Events. MySQL Events were added in MySQL 5.1.6 and offer an alternative to scheduled tasks and cron jobs. Events can be used to create backups, delete stale records, aggregate data for reports, and so on. Unlike standard triggers which execute given a certain condition, an event is an object that is triggered by the passage of time and is sometimes referred to as a temporal trigger. You can schedule events to run either once or at a recurring interval when you know your server traffic will be low.
SQLBackupAndFTP Ideal for any SQL Server database where backups have to be sent daily to a remote FTP server. It would save maintenance time on any SQL Server version, but it is especially useful for SQL Server Express 2005 and SQL Server Express 2008, since they don’t have built in tools for backup at all.
SQLBackupAndFTP can schedule daily jobs to
* Backup your SQL Server databases
* Zip the backups
* FTP(S) the backups to a remote FTP Server or save it on the LAN
* Send you an e-mail confirmation on job’s success or failure.
* Execute custom SQL scripts before and after backup
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.
mtop, MySQL top Monitors a MySQL server showing the queries which are taking the most amount of time to complete Shows top mysql threads. Features include 'zooming' in on a process to show the complete query, 'explaining' the query optimizer information for a query and 'killing' queries. In addition, server performance statistics, configuration information, and tuning tips are provided.
mkill (MySQL kill) monitors a MySQL server for long running queries and kills them after a specified time interval. Queries can be selected based on regexes on the user, host, command, database, state and query. Shows the MySQL commands consuming the greatest time. Normally, run as a console program this will allow you to see errant or badly optimized queries as they will stay on the screen for a while. However, if you are hunting for short lived queries, running in the manualrefresh mode with a short refresh time will allow you to catch short lived queries as well.
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
MDF viewer tool is available at SysTools to view MDF file. Now, you can read or view SQL database with Free SQL Server database viewer tool with having more capabilities to view SQL database contents. Advanced Free SQL Server database viewer tool helps you to view corrupt MDF database and deleted items like: stored procedures, tables, triggers, rules
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 domain. :-
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.
Chances are, that if you're seeing this error, you've run out of space in /tmp or wherever tmpdir points to. If only for the time it takes MySQL to create a temporary file (filling the parition) and delete it in disgust.
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.
Query-Objects A SQL query builder component that leverages on the power of .NET version 4. It allows your end-users to build SQL queries quickly and very intuitively by simply dragging and dropping tables and views on a worksheet. This is the perfect tool when it comes to extracting data quickly out of a database with only a minimum knowledge of SQL.
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.
A presentation on Redis, a relatively new database with rich datatypes, lots of features and an amazing performance. The presentation will cover following things:
* What Redis is and how it compares to other databases such as BigTable or MySQL
* How Plurk is using Redis to store millions of timelines and do billions of operations on these timelines without much hardware
* How Redis can be used in a lot of creative ways, such as creating a high-performance graph database or a high-performance queue
* The distributed future of Redis (scaling Redis to thousands of machines)
Amir Salihefendic is a co-founder and lead developer of Plurk, a social network that's highly popular in Taiwan and other parts of Asia.
Redis is written in ANSI C and works in most POSIX systems, (Portable Operating System Interface), like Linux, *BSD, Mac OS X, Solaris, and so on. Redis is free software released under the very liberal BSD license. Redis is reported to compile and work under WIN32 if compiled with Cygwin.
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.
Have you ever wanted to add more than one Integration Services existing package (e.g. 20 packages) in a SSIS project? Well, you may suppose that an Open Dialog supports multiple files selection to import more than one file at a time ...
BIDS Open Dialog doesn’t allow this, you can just select a single file! Hence the loss of valuable time spent to import the packages one at a time.
Few days ago I learned a trick that solves the problem, thanks to this post by Matt Masson.
Just copy all the packages to import from Windows Explorer (Ctrl + C):
Then just right click on the SSIS Packages folder of the Integration Services project and make a simple Past (CTRL + V):
So “auto-magically” you’ll have all those packages imported in your Integration Services project!!
This marks my last post as a SQLPASS Board member. I learned a lot during my year of service and I thank everyone involved for this opportunity. I would especially like to thank the Chapter leaders and Regional Mentors for Virtual Chapters who (mostly) patiently taught me about Virtual Chapters. I hope the changes I put in place will help strengthen and grow VCs and PASS going forward. I would also like to thank every one who encouraged me to reach beyond my comfort zone and accept a leadership position within the PASS organization.
My overall principle was to be a good steward of the PASS community. Could I have done more? Always. Did I do enough? I hope so. But PASS is a volunteer organization and my time, like yours, is limited. I have other obligations in life that supersede PASS. Now I have more time for some of those. I won’t be going away or leaving the SQL Community. I will still contribute to the community and support PASS, just in a different role. Time to let somebody else enjoy the hot seat for a while.
Finally, everyone who voted (not just for me) deserves a thanks. More voters and more engaged voters, strong candidates, and a vigorous debate were all I wanted out of declaring as a candidate last year. This year the SQL community got exactly that.
“SELECT *” isn’t just hazardous to performance, it can actually return blatantly wrong information.
There are a number of blog posts and articles out there that actively discourage the use of the SELECT * FROM …syntax. The two most common explanations that I have seen are:
Performance: The SELECT * syntax will return every column in the table, but frequently you really only need a few of the columns, and so by using SELECT * your are retrieving large volumes of data that you don’t need, but the system has to process, marshal across tiers, and so on. It would be much more efficient to only select the specific columns that you need.
Future-proof: If you are taking other shortcuts in your code, along with using SELECT *, you are setting yourself up for trouble down the road when enhancements are made to the system. For example, if you use SELECT * to return results from a table into a DataTable in .NET, and then reference columns positionally (e.g. myDataRow[5]) you could end up with bad data if someone happens to add a column into position 3 and skewing all the remaining columns’ ordinal position. Or if you use INSERT…SELECT * then you will likely run into errors when a new column is added to the source table in any position.
And if you use SELECT * in the definition of a view, you will run into a variation of the future-proof problem mentioned above. One of the guys on my team, Mike Byther, ran across this in a project we were doing, but fortunately he caught it while we were still in development. I asked him to put together a test to prove that this was related to the use of SELECT * and not some other anomaly. I’ll walk you through the test script so you can see for yourself what happens.
We are going to create a table and two views that are based on that table, one of them uses SELECT * and the other explicitly lists the column names. The script to create these objects is listed below.
IF OBJECT_ID('testtab') ISNOTNULL DROPTABLE testtab go IF OBJECT_ID('testtab_vw') ISNOTNULL DROPVIEW testtab_vw go IF OBJECT_ID('testtab_vw_named') ISNOTNULL DROPVIEW testtab_vw_named go CREATETABLE testtab (col1 NVARCHAR(5) null, col2 NVARCHAR(5) null) INSERT INTO testtab(col1, col2) VALUES ('A','B'), ('A','B') GO CREATEVIEW testtab_vw ASSELECT * FROM testtab GO CREATEVIEW testtab_vw_named ASSELECT col1, col2 FROM testtab go
Now, to prove that the two views currently return equivalent results, select from them.
SELECT'star', col1, col2 FROM testtab_vw SELECT'named', col1, col2 FROM testtab_vw_named
OK, so far, so good. Now, what happens if someone makes a change to the definition of the underlying table, and that change results in a new column being inserted between the two existing columns? (Side note, I normally prefer to append new columns to the end of the table definition, but some people like to keep their columns alphabetized, and for clarity for later people reviewing the schema, it may make sense to group certain columns together. Whatever the reason, it sometimes happens, and you need to protect yourself and your code from the repercussions.)
DROPTABLE testtab go CREATETABLE testtab (col1 NVARCHAR(5) null, col3 NVARCHAR(5) NULL, col2 NVARCHAR(5) null) INSERT INTO testtab(col1, col3, col2) VALUES ('A','C','B'), ('A','C','B') go SELECT'star', col1, col2 FROM testtab_vw SELECT'named', col1, col2 FROM testtab_vw_named
I would have expected that the view using SELECT * in its definition would essentially pass-through the column name and still retrieve the correct data, but that is not what happens. When you run our two select statements again, you see that the View that is based on SELECT * actually retrieves the data based on the ordinal position of the columns at the time that the view was created. Sure, one work-around is to recreate the View, but you can’t really count on other developers to know the dependencies you have built-in, and they won’t necessarily recreate the view when they refactor the table.
I am sure that there are reasons and justifications for why Views behave this way, but I find it particularly disturbing that you can have code asking for col2, but actually be receiving data from col3. By the way, for the record, this entire scenario and accompanying test script apply to SQL Server 2008 R2 with Service Pack 1.
So, let the developer beware…know what assumptions are in effect around your code, and keep on discouraging people from using SELECT * syntax in anything but the simplest of ad-hoc queries.
And of course, let’s clean up after ourselves. To eliminate the database objects created during this test, run the following commands.
Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks. And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them. You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted. Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway.
Some of you have seen or read about SQL Server constraints and have applied them to your table designs…unless you're a vendor ;)…and may even use CHECK constraints to limit numeric values, or length of strings, allowable characters and such. CHECK constraints can, however, do more than that, and can even provide enhanced security and other restrictions.
One tip or trick that I didn't cover very well in the presentation is using constraints to do unusual things; specifically, limiting or preventing inserts into tables. The idea was to use a CHECK constraint in a way that didn't depend on the actual data:
-- create a table that cannot accept data
CREATE TABLE dbo.JustTryIt(a BIT NOT NULL PRIMARY KEY,
CONSTRAINT chk_no_insert CHECK (GETDATE()=GETDATE()+1))
INSERT dbo.JustTryIt VALUES(1)
I'll let you run that yourself, but I'm sure you'll see that this is a pretty stupid table to have, since the CHECK condition will always be false, and therefore will prevent any data from ever being inserted. I can't remember why I used this example but it was for some vague and esoteric purpose that applies to about, maybe, zero people. I come up with a lot of examples like that.
However, if you realize that these CHECKs are not limited to column references, and if you explore the SQL Server function list, you could come up with a few that might be useful. I'll let the names describe what they do instead of explaining them all:
CREATE TABLE NoSA(a int not null,
CONSTRAINT CHK_No_sa CHECK (SUSER_SNAME()<>'sa'))
CREATE TABLE NoSysAdmin(a int not null,
CONSTRAINT CHK_No_sysadmin CHECK (IS_SRVROLEMEMBER('sysadmin')=0))
CREATE TABLE NoAdHoc(a int not null,
CONSTRAINT CHK_No_AdHoc CHECK (OBJECT_NAME(@@PROCID) IS NOT NULL))
CREATE TABLE NoAdHoc2(a int not null,
CONSTRAINT CHK_No_AdHoc2 CHECK (@@NESTLEVEL>0))
CREATE TABLE NoCursors(a int not null,
CONSTRAINT CHK_No_Cursors CHECK (@@CURSOR_ROWS=0))
CREATE TABLE ANSI_PADDING_ON(a int not null,
CONSTRAINT CHK_ANSI_PADDING_ON CHECK (@@OPTIONS & 16=16))
CREATE TABLE TimeOfDay(a int not null,
CONSTRAINT CHK_TimeOfDay CHECK (DATEPART(hour,GETDATE()) BETWEEN 0 AND 1))
GO
-- log in as sa or a sysadmin server role member, and try this:
INSERT NoSA VALUES(1)
INSERT NoSysAdmin VALUES(1)
-- note the difference when using sa vs. non-sa
-- then try it again with a non-sysadmin login
-- see if this works:
INSERT NoAdHoc VALUES(1)
INSERT NoAdHoc2 VALUES(1)
GO
-- then try this:
CREATE PROCEDURE NotAdHoc @val1 int, @val2 int AS
SET NOCOUNT ON;
INSERT NoAdHoc VALUES(@val1)
INSERT NoAdHoc2 VALUES(@val2)
GO
EXEC NotAdHoc 2,2
-- which values got inserted?
SELECT * FROM NoAdHoc
SELECT * FROM NoAdHoc2
-- and this one just makes me happy :)
INSERT NoCursors VALUES(1)
DECLARE curs CURSOR FOR SELECT 1
OPEN curs
INSERT NoCursors VALUES(2)
CLOSE curs
DEALLOCATE curs
INSERT NoCursors VALUES(3)
SELECT * FROM NoCursors
I'll leave the ANSI_PADDING_ON and TimeOfDay tables for you to test on your own, I think you get the idea. (Also take a look at the NoCursors example, notice anything interesting?)
The real eye-opener, for me anyway, is the ability to limit bad coding practices like cursors, ad-hoc SQL, and sa use/abuse by using declarative SQL objects. I'm sure you can see how and why this would come up when discussing Revenge: The SQL.;) And the best part IMHO is that these work on pretty much any version of SQL Server, without needing Policy Based Management, DDL/login triggers, or similar tools to enforce best practices.
All seriousness aside, I highly recommend that you spend some time letting your mind go wild with the possibilities and see how far you can take things. There are no rules!
At Pass Summit 2011 a new project was announced. It’s a Microsoft SQL Azure Lab and its codename is Microsoft “Data Explorer”. According to the official blog (http://blogs.msdn.com/b/dataexplorer/), this new tool provides an innovative way to acquire new knowledge from the data that interest you. In a nutshell, Data Explorer allows you to combine data from multiple sources, to publish and share the result. In addition, you can generate data streams in the RESTful open format (Open Data Protocol), and they can then be used by other applications. Nonetheless we can still use Excel or PowerPivot to analyze the results.
Sources can be varied: Excel spreadsheets, text files, databases, Windows Azure Marketplace, etc.. For those who are not familiar with this resource, I strongly suggest you to keep an eye on the data services available to the Marketplace:
To tell the truth, as I read the above blog post, I was tempted to think of the Data Explorer as a "SSIS on Azure" addressed to the Power User. In fact, reading the response from Tim Mallalieu (Group Program Manager of Data Explorer) to the comment made to his post, I had a positive response to my first impression:
“…we originally thinking of ourselves as Self-Service ETL. As we talked to more folks and started partnering with other teams we realized that would be an area that we can add value but that there were more opportunities emerging.”
The typical operations of the ETL phase ( processing and organization of data in different formats) can be obtained thanks to Data Explorer Mashup. This is an image of the tool:
The flexibility in the manipulation of information is given by Data Explorer Formula Language. This is a formula-based Excel-style specific language:
Anyone wishing to know more can check the project page in addition to aforementioned blog:
In light of this new project, there is no doubt about the intention of Microsoft to get closer and closer to the Power User, providing him flexible and very easy to use tools for data analysis. The prime example of this is PowerPivot.
The question that remains is always the same: having in a company more Power User will implicitly mean having different data models representing the same reality. But this would inevitably lead to anarchical data management... What do you think about that?
Thanx to all the people who voted for it. I'm glad you all like it and use it with great success.
Also I've added a possibility for you to subscribe to email notifications in case the auto-updater doesn't work for you for some reason like being behind a proxy.
I’m presenting in Omaha on Writing Faster SQL at 6PM on December 7th. You can find meeting details on the Omaha SQL Server User Group page. The meeting location requires an RSVP so building security has a list of attendees.
The presentation is a series of suggestions on improving performance. It ranges from simple things like comparing indexed columns to scalar values up to tips for reducing query compiles and asynchronous processing patterns. Nearly all of these come from specific issues I’ve encountered working on poorly performing SQL Servers.
It is always a good start when you can steal a title line from one of the best writers in the English language. Let’s hope I can make the rest of this post live up to the opening.
One recurring problem with SQL server is moving databases to new servers. Client applications use a variety of ways to resolve SQL Server names, some of which are not changed easily <cough SharePoint /cough>. If you happen to be using default instances on both the source and target SQL Server, then the solution is pretty simple. You create (or bug the network admin until she creates) two DNS “A” records. One points the old name to the new IP address. The other creates a new alias for the old server, since the original system name is now redirected. Note this will redirect ALL traffic from the old server to the new server, including RDP and file share connection attempts.
Figure 1 – Microsoft DNS MMC Snap-In
Figure 2 – DNS New Host Dialog Box
Both records are necessary so you can still access the old server via an alternate name.
Server Role
IP Address
Name
Alias
Source
10.97.230.60
SQL01
SQL01_Old
Target
10.97.230.80
SQL02
SQL01
Table 1 – Alias List
If you or somebody set up connections via IP address, you deserve to have to go to each app and fix it by hand. That is the only way to fix that particular foul-up.
If have to deal with Named Instances either as a source or a target, then it gets more complicated. The standard fix is to use the SQL Server Configuration Manager (or one of its earlier incarnations) to create a SQL client alias to redirect the connection. This can be a pain installing and configuring the app on multiple client servers. The good news is that SQL Server Configuration Manager AND all of its earlier versions simply write a few registry keys. Extracting the keys into a .reg file makes centralized automated deployment a snap.
If the client is a 32-bit system, you have to extract the native key. If it is a 64-bit, you have to extract the native key and the WoW (32 bit on 64 bit host) key.
First, pick a development system to create the actual registry key. If you do this repeatedly, you can simply edit an existing registry file. Create the entry using the SQL Configuration Manager. You must use a 64-bit system to create the WoW key. The following example redirects from a named instance “SQL01\SQLUtiluty” to a default instance on “SQL02”.
Figure 3 – SQL Server Configuration Manager - Native
Figure 3 shows the native key listing.
Figure 4 – SQL Server Configuration Manager – WoW
If you think you don’t need the WoW key because your app is 64 it, think again. SQL Server Management Server is a 32-bit app, as are most SQL test utilities. Always create both keys for 64-bit target systems.
Now that the keys exist, we can extract them into a .reg file. Fire up REGEDIT and browse to the following location: HKLM\Software\Microsoft\MSSQLServer\Client\ConnectTo. You can also search the registry for the string value of one of the server names (old or new).
Right click on the “ConnectTo” label and choose “Export”. Save with an appropriate name and location. The resulting file should look something like this:
Figure 5 – SQL01_Alias.reg
Repeat the process with the location: HKLM\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
Note that if you have multiple alias entries, ALL of the entries will be exported. In that case, you can edit the file and remove the extra aliases.
You can edit the files together into a single file. Just leave a blank line between new keys like this:
Figure 6 – SQL01_Alias_All.reg
Of course if you have an automatic way to deploy, it makes sense to have an automatic way to Un-deploy. To delete a registry key, simply edit the .reg file and replace the target with a “-“ sign like so.
Figure 7 – SQL01_Alias_UNDO.reg
Now we have the ability to move any database to any server without having to install or change any applications on any client server. The whole process should be transparent to the applications, which makes planning and coordinating database moves a far simpler task.
This is the final blog for my PASS Summit 2011 series. Well okay, a mini-series, I guess.
On the last day of the conference, I attended Keith Elmore’ and Boris Baryshnikov’s (both from Microsoft) “Introducing the Microsoft SQL Server Code Named “Denali” Performance Dashboard Reports, Jeremiah Peschka’s (blog|twitter) “Rewrite your T-SQL for Great Good!”, and Kimberly Tripp’s (blog|twitter) “Isolated Disasters in VLDBs”.
Keith and Boris talked about the lifecycle of a session, figuring out the running time and the waiting time. They pointed out the transient nature of the reports. You could be drilling into it to uncover a problem, but the session may have ended by the time you’ve drilled all of the way down. Also, the reports are for troubleshooting live problems and not historical ones. You can use Management Data Warehouse for historical troubleshooting. The reports provide similar benefits to the Activity Monitor, however Activity Monitor doesn’t provide context sensitive drill through.
One thing I learned in Keith’s and Boris’ session was that the buffer cache hit ratio should really never be below 87% due to the read-ahead mechanism in SQL Server. When a page is read, it will read the entire extent. So for every page read, you get 7 more read. If you need any of those 7 extra pages, well they are already in cache.
I had a lot of fun in Jeremiah’s session about refactoring code plus I learned a lot. His slides were visually presented in a fun way, which just made for a more upbeat presentation. Jeremiah says that before you start refactoring, you should look at your system. Investigate missing or too many indexes, out-of-date statistics, and other areas that could be leading to your code running slow. He talked about code standards. He suggested using common abbreviations for aliases instead of one-letter aliases. I’m a big offender of one-letter aliases, but he makes a good point. He said that join order does not matter to the optimizer, but it does matter to those who have to read your code. Now let’s get into refactoring!
Eliminate useless things – useless/unneeded joins and columns. If you don’t need it, get rid of it!
Instead of using DISTINCT/JOIN, replace with EXISTS
Simplify your conditions; use UNION or better yet UNION ALL instead of OR to avoid a scan and use indexes for each union query
Branching logic – instead of IF this, IF that, and on and on…use dynamic SQL (sp_executesql, please!) or use a parameterized query in the application
Correlated subqueries – YUCK! Replace with a join
Eliminate repeated patterns
Last, but certainly not least, was Kimberly’s session. Kimberly is my favorite speaker. I attended her two-day pre-conference seminar at PASS Summit 2005 as well as a SQL Immersion Event last December. Did I mention she’s my favorite speaker? Okay, enough of that.
Kimberly’s session was packed with demos. I had seen some of it in the SQL Immersion Event, but it was very nice to get a refresher on these, especially since I’ve got a VLDB with some growing pains. One key takeaway from her session is the idea to use a log shipping solution with a load delay, such as 6, 8, or 24 hours behind the primary. In the case of say an accidentally dropped table in a VLDB, we could retrieve it from the secondary database rather than waiting an eternity for a restore to complete. Kimberly let us know that in SQL Server 2012 (it finally has a name!), online rebuilds are supported even if there are LOB columns in your table. This will simplify custom code that intelligently figures out if an online rebuild is possible.
There was actually one last time slot for sessions that day, but I had an airplane to catch and my kids to see!
Well we’re about a month past PASS Summit 2011, and yet I haven’t finished blogging my notes! Between work and home life, I haven’t been able to come up for air in a bit. Now on to my notes…
On Thursday of the PASS Summit 2011, I attended Klaus Aschenbrenner’s (blog|twitter) “Advanced SQL Server 2008 Troubleshooting”, Joe Webb’s (blog|twitter) “SQL Server Locking & Blocking Made Simple”, Kalen Delaney’s (blog|twitter) “What Happened? Exploring the Plan Cache”, and Paul Randal’s (blog|twitter) “More DBA Mythbusters”. I think my head grew two times in size from the Thursday sessions. Just WOW!
I took a ton of notes in Klaus' session. He took a deep dive into how to troubleshoot performance problems. Here is how he goes about solving a performance problem:
Start by checking the wait stats DMV
System health
Memory issues
I/O issues
I normally start with blocking and then hit the wait stats. Here’s the wait stat query (Paul Randal’s) that I use when working on a performance problem. He highlighted a few waits to be aware of such as WRITELOG (indicates IO subsystem problem), SOS_SCHEDULER_YIELD (indicates CPU problem), and PAGEIOLATCH_XX (indicates an IO subsystem problem or a buffer pool problem).
Regarding memory issues, Klaus recommended that as a bare minimum, one should set the “max server memory (MB)” in sp_configure to 2GB or 10% reserved for the OS (whichever comes first). This is just a starting point though!
Regarding I/O issues, Klaus talked about disk partition alignment, which can improve SQL I/O performance by up to 100%. You should use 64kb for NTFS cluster, and it’s automatic in Windows 2008 R2.
Joe’s locking and blocking presentation was a good session to really clear up the fog in my mind about locking. One takeaway that I had no idea could be done was that you can set a timeout in T-SQL code view LOCK_TIMEOUT. If you do this via the application, you should trap error 1222.
Kalen’s session went into execution plans. The minimum size of a plan is 24k. This adds up fast especially if you have a lot of plans that don’t get reused much. You can use sys.dm_exec_cached_plans to check how often a plan is being reused by checking the usecounts column. She said that we can use DBCC FLUSHPROCINDB to clear out the stored procedure cache for a specific database. I didn’t know we had this available, so this was great to hear. This will be less intrusive when an emergency comes up where I’ve needed to run DBCC FREEPROCCACHE.
Kalen said one should enable “optimize for ad hoc workloads” if you have an adhoc loc. This stores only a 300-byte stub of the first plan, and if it gets run again, it’ll store the whole thing. This helps with plan cache bloat.
I have a lot of systems that use prepared statements, and Kalen says we simulate those calls by using sp_executesql. Cool!
Paul did a series of posts last year to debunk various myths and misconceptions around SQL Server. He continues to debunk things via “DBA Mythbusters”. You can get a PDF of a bunch of these here. One of the myths he went over is the number of tempdb data files that you should have. Back in 2000, the recommendation was to have as many tempdb data files as there are CPU cores on your server. This no longer holds true due to the numerous cores we have on our servers. Paul says you should start out with 1/4 to 1/2 the number of cores and work your way up from there. BUT! Paul likes what Bob Ward (twitter) says on this topic:
8 or less cores –> set number of files equal to the number of cores
Greater than 8 cores –> start with 8 files and increase in blocks of 4
One common myth out there is to set your MAXDOP to 1 for an OLTP workload with high CXPACKET waits. Instead of that, dig deeper first. Look for missing indexes, out-of-date statistics, increase the “cost threshold for parallelism” setting, and perhaps set MAXDOP at the query level.
Paul stressed that you should not plan a backup strategy but instead plan a restore strategy. What are your recoverability requirements? Once you know that, now plan out your backups.
As Paul always does, he talked about DBCC CHECKDB. He said how fabulous it is. I didn’t want to interrupt the presentation, so after his session had ended, I asked Paul about the need to run DBCC CHECKDB on your mirror systems. You could have data corruption occur at the mirror and not at the principal server. If you aren’t checking for data corruption on your mirror systems, you could be failing over to a corrupt database in the case of a disaster or even a planned failover. You can’t run DBCC CHECKDB against the mirrored database, but you can run it against a snapshot off the mirrored database.
Once you have a good virtualization platform chosen, whether it is a desktop, server or laptop environment, the temptation is to build “X”. “X” may be a SharePoint lab, a Virtual Cluster, an AD test environment or some other cool project that you really need RIGHT NOW. That would be doing it wrong.
My grandfather taught woodworking and cabinetmaking for twenty-seven years at a trade school in Alabama. He was the first instructor hired at that school and the only teacher for the first two years. His students built tables, chairs, and workbenches so the school could start its HVAC courses. Visiting as a child, I also noticed many extra “helper” stands, benches, holders, and gadgets all built from wood.
What does that have to do with a virtual lab, you ask? Well, that is the same approach you should take. Build stuff that you will use. Not for solving a particular problem, but to let the Virtual Lab be part of your normal troubleshooting toolkit.
Start with basic copies of various Operating Systems. Load and patch server and desktop OS environments. This also helps build your collection of ISO files, another essential element of a virtual Lab. Once you have these “baseline” images, you can use your Virtualization software’s snapshot capability to freeze the image. Clone the snapshot and you have a brand new fully patched machine in mere moments. You may have to sysprep some of the Microsoft OS environments if you are going to create a domain environment or experiment with clustering. That is still much faster than loading and patching from scratch.
So once you have a stock of raw materials (baseline images in this case) where should you start. Again, my grandfather’s workshop gives us the answer. In the shop it was workbenches and tables to hold large workpieces that made the equipment more useful. In a Windows environment the same role falls to the fundamental network services: DHCP, DNS, Active Directory, Routing, File Services, and Storage services. Plan your internal network setup. Build out an AD controller with all the features listed. Make the actual domain an isolated domain so it will not care about where you take it. Add the Microsoft iSCSI target. Once you have this single system, you can leverage it for almost any network environment beyond a simple stand-alone system.
Having these templates and fundamental infrastructure elements ready to run means I can build a quick lab in minutes instead of hours. My solutions are well-tested, my processes fully documented with screenshots, and my plans validated well before I have to make any changes to client systems. the work I put in is easily returned in increased value and client satisfaction.
The only thing more controversial than new Federal Tax plans is new Licensing plans from Microsoft. In both cases, everyone calculates several numbers.
First, will I pay more or less under this plan?
Second, will my competition pay more or less than now?
Third, will <insert interesting person/company here> pay more or less?
Not that items 2 and 3 are meaningful, that is just how people think.
First up is a switch from per-socket to per-core licensing. Anyone who didn’t see something like this coming should rapidly search for a new line of work because you are not paying attention. The explosion of multi-core processors has made SQL Server a bargain. Microsoft is in business to make money and the old per-socket model was not going to do that going forward.
Per-core licensing also simplifies virtualization licensing. Physical Core = Virtual Core, at least for licensing. Oversubscribe your processors, that’s your lookout. You still pay for what is exposed to the VM. The cool part is you can seamlessly move physical and virtual workloads around and the licenses follow. The catch is you have to have Software Assurance to make the licenses mobile. Nice touch there.
Let’s have a moment of silence for the late, unlamented, largely ignored Workgroup Edition. To quote the Microsoft FAQ: “Standard becomes our sole edition for basic database needs”. Considering I haven’t encountered a singe instance of SQL Server Workgroup Edition in the wild, I don’t think this will be all that controversial.
As for pricing, it looks like a wash with current per-socket pricing based on four core sockets. Interestingly, that is the minimum core count Microsoft proposes to swap to transition per-socket to per-core if you are on Software Assurance. Reading the fine print shows that if you are using more, you will get more core licenses:
Licenses purchased with Software Assurance (SA) will upgrade to SQL Server 2012 at no additional cost. EA/EAP customers can continue buying processor licenses until your next renewal after June 30, 2012. At that time, processor licenses will be exchanged for core-based licenses sufficient to cover the cores in use by processor-licensed databases (minimum of 4 cores per processor for Standard and Enterprise, and minimum of 8 EE cores per processor for Datacenter).
Looks like the folks who invested in the AMD 12-core chips will make out like bandits.
Now, on to something new: SQL Server Business Intelligence Edition. Yep, finally a BI-specific SKU licensed for server+CAL configurations only. Note that Enterprise Edition still supports the complete feature set; the BI Edition is intended for smaller shops who want to use the full BI feature set but without needing Enterprise Edition scale (or costs). No, you don’t get ColumnStore, Compression, or Partitioning in the BI Edition. Those are Enterprise scale features, ThankYouVeryMuch. Then again, your starting licensing costs are about one sixth of an Enterprise Edition system (based on an 8 core server).
The only part of the message I am missing is if the current Failover Licensing Policy will change. Do we need to fully or partially license failover servers? That is a detail I definitely want to know.
So how do the new SQL Server Developer Tools (previously code-named Juneau) stack up against SQL Source Control? Read on to find out.
At the PASS Community Summit a couple of weeks ago, it was announced that the previously code-named Juneau software would be released under the name of SQL Server Developer Tools with the release of SQL Server 2012. This replacement for Database Projects in Visual Studio (also known in a former life as Data Dude) has some great new features. I won’t attempt to describe them all here, but I will applaud Microsoft for making major improvements. One of my favorite changes is the way database elements are broken down. Previously every little thing was in its own file. For example, indexes were each in their own file. I always hated that. Now, SSDT uses a pattern similar to Red-Gate’s and puts the indexes and keys into the same file as the overall table definition.
Of course there are really cool features to keep your database model in sync with the actual source scripts, and the rename refactoring feature is now touted as being more than just a search and replace, but rather a “semantic-aware” search and replace. Funny, it reminds me of SQL Prompt’s Smart Rename feature. But I’m not writing this just to criticize Microsoft and argue that they are late to the party with this feature set. Instead, I do see it as a viable alternative for folks who want all of their source code to be version controlled, but there are a couple of key trade-offs that you need to know about when you choose which tool set to use.
First, the basics
Both tool sets integrate with a wide variety of source control systems including the most popular: Subversion, GIT, Vault, and Team Foundation Server. Both tools have integrated functionality to produce objects to upgrade your target database when you are ready (DACPACs in SSDT, integration with SQL Compare for SQL Source Control). If you regularly live in Visual Studio or the Business Intelligence Development Studio (BIDS) then SSDT will likely be comfortable for you. Like BIDS, SSDT is a Visual Studio Project Type that comes with SQL Server, and if you don’t already have Visual Studio installed, it will install the shell for you. If you already have Visual Studio 2010 installed, then it will just add this as an available project type. On the other hand, if you regularly live in SQL Server Management Studio (SSMS) then you will really enjoy the SQL Source Control integration from within SSMS. Both tool sets store their database model in script files. In SSDT, these are on your file system like other source files; in SQL Source Control, these are stored in the folder structure in your source control system, and you can always GET them to your file system if you want to browse them directly.
For me, the key differentiating factors are 1) a single, unified check-in, and 2) migration scripts. How you value those two features will likely make your decision for you.
Unified Check-In
If you do a continuous-integration (CI) style of development that triggers an automated build with unit testing on every check-in of source code, and you use Visual Studio for the rest of your development, then you will want to really consider SSDT. Because it is just another project in Visual Studio, it can be added to your existing Solution, and you can then do a complete, or unified single check-in of all changes whether they are application or database changes. This is simply not possible with SQL Source Control because it is in a different development tool (SSMS instead of Visual Studio) and there is no way to do one unified check-in between the two. You CAN do really fast back-to-back check-ins, but there is the possibility that the automated build that is triggered from the first check-in will cause your unit tests to fail and the CI tool to report that you broke the build. Of course, the automated build that is triggered from the second check-in which contains the “other half” of your changes should pass and so the amount of time that the build was broken may be very, very short, but if that is very, very important to you, then SQL Source Control just won’t work; you’ll have to use SSDT.
Refactoring and Migrations
If you work on a mature system, or on a not-so-mature but also not-so-well-designed system, where you want to refactor the database schema as you go along, but you can’t have data suddenly disappearing from your target system, then you’ll probably want to go with SQL Source Control. As I wrote previously, there are a number of changes which you can make to your database that the comparison tools (both from Microsoft and Red Gate) simply cannot handle without the possibility (or probability) of data loss. Currently, SSDT only offers you the ability to inject PRE and POST custom deployment scripts. There is no way to insert your own script in the middle to override the default behavior of the tool. In version 3.0 of SQL Source Control (Early Access version now available) you have that ability to create your own custom migration script to take the place of the commands that the tool would have done, and ensure the preservation of your data. Or, even if the default tool behavior would have worked, but you simply know a better way then you can take control and do things your way instead of theirs.
You Decide
In the environment I work in, our automated builds are not triggered off of check-ins, but off of the clock (currently once per night) and so there is no point at which the automated build and unit tests will be triggered without having both sides of the development effort already checked-in. Therefore having a unified check-in, while handy, is not critical for us. As for migration scripts, these are critically important to us. We do a lot of new development on systems that have already been in production for years, and it is not uncommon for us to need to do a refactoring of the database. Because of the maturity of the existing system, that often involves data migrations or other additional SQL tasks that the comparison tools just can’t detect on their own. Therefore, the ability to create a custom migration script to override the tool’s default behavior is very important to us. And so, you can see why we will continue to use Red Gate SQL Source Control for the foreseeable future.
Did you know that if you run a query in SQL Server, and it processes it as a hash match, and there's not enough memory to fully process it, the memory will spill to disk? You can find out all about it here.
Note: I know this makes even less sense than my usual blog posts do, so for more information, check out Jen'sAwesomesauceblog entry about it. (Please don't sue me Jen!)
I arrived in Seattle last Monday afternoon to attend PASS Summit 2011. I had really wanted to attend Gail Shaw’s (blog|twitter) and Grant Fritchey’s (blog|twitter) pre-conference seminar “All About Execution Plans” on Monday, but that would have meant flying out on Sunday which I couldn’t do.
On Tuesday, I attended Allan Hirt’s (blog|twitter) pre-conference seminar entitled “A Deep Dive into AlwaysOn: Failover Clustering and Availability Groups”. Allan is a great speaker, and his seminar was packed with demos and information about AlwaysOn in SQL Server 2012. Unfortunately, I have lost my notes from this seminar and the presentation materials are only available on the pre-con DVD. Hmpf!
In Gail’s session, she went over how to fix bad plans and bad query patterns. Update your stale statistics!
How to fix bad plans
Use local variables – optimizer can’t sniff it, so it’ll optimize for “average” value
Use RECOMPILE (at the query or stored procedure level) – CPU hit
OPTIMIZE FOR hint – most common value you’ll pass
How to fix bad query patterns
Don’t use them – ha!
Catch-all queries
Use dynamic SQL
OPTION (RECOMPILE)
Multiple execution paths
Split into multiple stored procedures
OPTION (RECOMPILE)
Modifying parameter values
Use local variables
Split into outer and inner procedure
OPTION (RECOMPILE)
She also went into “last resort” and “very last resort” options, but those are risky unless you know what you are doing. For the average Joe, she wouldn’t recommend these. Examples are query hints and plan guides.
While I enjoyed Andrew’s session, I didn’t take any notes as it was familiar material. Andrew is a great speaker though, and I’d highly recommend attending his sessions in the future.
Next up was Dan’s PowerShell session. I need to look into profiles, manifests, function modules, and function import scripts more as I just didn’t quite grasp these concepts. I am attending a PowerShell training class at the end of November, so maybe that’ll help clear it up. I really enjoyed the Excel integration demo. It was very cool watching PowerShell build the spreadsheet in real-time. I must look into this more! On a side note, I am jealous of Dan’s hair. Fabulous hair!
Brent’s session showed us how to quickly gather information about a server that you will be taking over database administration duties for. He wrote a script to do a fast health check and then later wrapped it into a stored procedure, sp_Blitz. I can’t wait to use this at my work even on systems where I’ve been the primary DBA for years, maybe there’s something I’ve overlooked. We are using EPM to help standardize our environment and uncover problems, but sp_Blitz will definitely still help us out. He even provides a cloud-based update feature, sp_BlitzUpdate, for sp_Blitz so you don’t have to constantly update it when he makes a change. I think I’ll utilize his update code for some other challenges that we face at my work.
What an amazing week I had at PASS Summit 2011 in Seattle, WA! I hadn’t attended a PASS conference since September of 2005 when it was in Grapevine, Texas. It has grown so much since then. I am not sure how many people attended back then, but I’d guesstimate about 1500. They announced that at this year’s conference there were 4000 attendees. WOW!
Here are my favorite aspects of this conference:
Networking! – Not only did I meet a lot of new people, but I also got to meet people in person that I’ve known on the Internet for years like Mladen Prajdić (blog|twitter) and Rob Volk (blog|twitter). I even met someone that I’d recently helped out in the SQLTeamforums.
Learning – I took a lot of notes during the sessions I attended and plan on blogging very soon about them. It is amazing the amount of things you learn and the things that you unlearn. Yes I said unlearn. Some of the stuff that I thought I knew was either out-dated or just plain wrong.
Fun, fun, fun – To say that this conference was fun would be an understatement. I had a blast!
I attended the “Welcome Reception and Quizbowl” on Tuesday night, the “Exhibitor Reception” on Wednesday night, and the “Community Appreciation Party” at GameWorks on Thursday night. There were many other after-hours events to attend, but I had to make my kids a priority at night so I had to get back to my hotel room before 9pm so that I could Skype with them.
It was very entertaining reading and posting with #sqlpass on Twitter. Twitter has changed the conference experience for the better.
I will definitely be able to do my job better due to attending this conference. The return on investment is HUGE!
At the Summit you’ll see PASS announce the total attendance and the “total registrations”. The total registrations is the sum of the conference attendees and the pre-conference registrations. A single person can be counted three times (conference plus two pre-cons) in the total registration count.
When I was doing marketing for the Summit this drove me nuts. I couldn’t figure out why anyone would use total registrations. However, when I tried to stop reporting this number I got lots of pushback. Apparently this is how conferences compare themselves to each other. Vendors, sponsors and Microsoft all wanted to know our total registration number. I was even asked why we weren’t doing more “things” that people could register for so that our number would be even larger. This drove me nuts.
I understand that many of you are very detail oriented. I just want to make sure you understand what numbers you’re seeing when we include them in the keynote at the Summit.
Two years ago 59 SQL Server MVP's came together and helped make one of the best book on SQL Server out there. Each chapter was written by an MVP about a part of SQL Server they loved working with. This resulted in superb quality content and excellent ratings from the readers. To top it off all earnings went to a good cause, the War Child International organization. That book was SQL Server MVP Deep Dives.
This year 63 SQL Server MVPs, me included, decided it was time do repeat the success of the first book.
The topics in 60 chapters are grouped in 5 groups: Architecture, Database Administration, Database Development, Performance Tuning and Optimization, Business Intelligence. They represent over 1000 years of daily experience in various areas of SQL Server. I have contributed chapter 28 in Database Development group titled Getting asynchronous with Service Broker. In it I show you the Service Broker template you can use for secure communication between two or more SQL server instances for whatever purpose you may have. If you haven't heard of Service Broker it's a part of the database engine that enables you to do completely async operations in the database itself or between databases and instances.
The official release of the book will be next week at PASS where there will be 2 slots where most of the authors will be there signing the books you bring. This is also a great opportunity to meet everyone and ask about any problems you may have. So definitely come say hi.
Again we decided on a charity that will be supported by this book. It's called Operation Smile. They provide free surgeries to repair cleft lip, cleft palate and other facial deformities for children around the globe. You can also help them by donating.
You can preorder it on at Manning Publications website or on Amazon. By having it you not only get to learn a lot, improve your skills and have fun but you also help a child have a normal life. If that's not a good cause then I don't know what it is.
The last two years we’ve put the Board in front of the members and taken questions. We’re going to do that again this year. It will be in Room 307/308 from 12:15 to 1:30 on Friday.
Yes, this time overlaps with the Birds of a Feather Lunch and the start of afternoon sessions – but only partially. You can attend the Q&A and still get to parts of both of those. There just isn’t a great time to do this. Every time overlaps with something. We can’t do it after the last session on Friday. We can’t fit it between the last session and the evening events on Wednesday or Thursday. We had some discussion around breakfast time but I didn’t think that was realistic. This is the least bad time we could come up with.
Last year we had 60-70 people attend. These are the items that were specific things that I could work on:
The first question was whether to increase transparency around individual votes of Board members. We approved this at the Board meeting the following day. The only caveat was that if the Board is given confidential information as a basis for their vote then we may not be able to disclose individual votes. Putting a Director in a position where they can’t publicly defend the reason for their vote is a difficult situation. Thanks Kendal!
Can we have a Board member discretionary fund? As background, I took a couple of people to lunch so we could have a quiet place to talk. I bought lunch but wasn’t able to expense it back to PASS. We just don’t have a budget item for things like this. I think we should. I would guess the entire Board would like it also. It was in an earlier version of the budget but came out as part of a cost-cutting move to balance the budget. I’d like to see it added back in but we’ll have to see.
I know there were a comments about the elections. At this point we had created the Election Review Committee. I’ve already written at length about this process.
Where does IT work go? PASS started to publish our internal management reports starting in December 2010. You can find them on our Governance page. These aren’t filtered at all and include a variety of information about IT projects. The most recent update had roughly a page of updates related to IT. Lots of the work was related to Summit and the Orator tool that we use to manage speaker submissions.
There were numerous requests that Tina Turner not be repeated. Done. I don’t think we’ll do anything quite like that again.
We had a request for a payment plan for Summit. We looked into this briefly but didn’t take any action. We didn’t think the effort was worth the small number of people that would use it. If you disagree, submit this on our Summit Feedback site and get some votes.
There were lots of suggestions around the first-timers events – especially from first timers. You can find all our current activities related to first-timers at the First Timers page on the Summit web site. Plus links to 34 (!) blog posts on suggestions for first-timers. And a big THANK YOU to Confio and Red Gate for sponsoring this.
I hope you get the chance to attend. These events are very helpful to me as a Board member. I like being able to look around the room as comments are being made and see the audience reaction. It helps me gauge the interest in an idea.
I’d also like to direct you to the Summit Feedback site. You can submit and vote on ideas to make the Summit a better experience. As of right now we have the suggestions from last year still up. We may reset these prior to the Summit though.
Boy, how time flies! I can hardly believe that the 2011 PASS Summit is just one week away. Maybe it snuck up on me because it’s a few weeks earlier than last year. Whatever the cause, I am really looking forward to next week. The PASS Summit is the largest SQL Server conference in the world and a fantastic networking opportunity thrown in for no additional charge. Here are a few thoughts to help you maximize the week.
Networking
As Karen Lopez (blog | @DataChick) mentioned in her presentation for the Professional Development Virtual Chapter just a couple of weeks ago, “Don’t wait until you need a new job to start networking.” You should always be working on your professional network. Some people, especially technical-minded people, get confused by the term networking. The first image that used to pop into my head was the image of some guy standing, awkwardly, off to the side of a cocktail party, trying to shmooze those around him. That’s not what I’m talking about. If you’re good at that sort of thing, and you can strike up a conversation with some stranger and learn all about them in 5 minutes, and walk away with your next business deal all but approved by the lawyers, then congratulations. But if you’re not, and most of us are not, I have two suggestions for you. First, register for Don Gabor’s 2-hour session on Tuesday at the Summit called Networking to Build Business Contacts. Don is a master at small talk, and at teaching others, and in just those two short hours will help you with important tips about breaking the ice, remembering names, and smooth transitions into and out of conversations. Then go put that great training to work right away at the Tuesday night Welcome Reception and meet some new people; which is really my second suggestion…just meet a few new people. You see, “networking” is about meeting new people and being friendly without trying to “work it” to get something out of the relationship at this point. In fact, Don will tell you that a better way to build the connection with someone is to look for some way that you can help them, not how they can help you.
There are a ton of opportunities as long as you follow this one key point: Don’t stay in your hotel! At the least, get out and go to the free events such as the Tuesday night Welcome Reception, the Wednesday night Exhibitor Reception, and the Thursday night Community Appreciation Party. All three of these are perfect opportunities to meet other professionals with a similar job or interest as you, and you never know how that may help you out in the future. Maybe you just meet someone to say HI to at breakfast the next day instead of eating alone. Or maybe you cross paths several times throughout the Summit and compare notes on different sessions you attended. And you just might make new friends that you look forward to seeing year after year at the Summit. Who knows, it might even turn out that you have some specific experience that will help out that other person a few months’ from now when they run into the same challenge that you just overcame, or vice-versa. But the point is, if you don’t get out and meet people, you’ll never have the chance for anything else to happen in the future.
One more tip for shy attendees of the Summit…if you can’t bring yourself to strike up conversation with strangers at these events, then at the least, after you sit through a good session that helps you out, go up to the speaker and introduce yourself and thank them for taking the time and effort to put together their presentation. Ideally, when you do this, tell them WHY it was beneficial to you (e.g. “Now I have a new idea of how to tackle a problem back at the office.”) I know you think the speakers are all full of confidence and are always receiving a ton of accolades and applause, but you’re wrong. Most of them will be very happy to hear first-hand that all the work they put into getting ready for their presentation is paying off for somebody.
Training
With over 170 technical sessions at the Summit, training is what it’s all about, and the training is fantastic! Of course there are the big-name trainers like Paul Randall, Kimberly Tripp, Kalen Delaney, Itzik Ben-Gan and several others, but I am always impressed by the quality of the training put on by so many other “regular” members of the SQL Server community. It is amazing how you don’t have to be a published author or otherwise recognized as an “expert” in an area in order to make a big impact on others just by sharing your personal experience and lessons learned. I would rather hear the story of, and lessons learned from, “some guy or gal” who has actually been through an issue and came out the other side, than I would a trained professor who is speaking just from theory or an intellectual understanding of a topic.
In addition to the three full days of regular sessions, there are also two days of pre-conference intensive training available. There is an extra cost to this, but it is a fantastic opportunity. Think about it…you’re already coming to this area for training, so why not extend your stay a little bit and get some in-depth training on a particular topic or two? I did this for the first time last year. I attended one day of extra training and it was well worth the time and money. One of the best reasons for it is that I am extremely busy at home with my regular job and family, that it was hard to carve out the time to learn about the topic on my own. It worked out so well last year that I am doubling up and doing two days or “pre-cons” this year.
And then there are the DVDs. I think these are another great option. I used the online schedule builder to get ready and have an idea of which sessions I want to attend and when they are (much better than trying to figure this out at the last minute every day). But the problem that I have run into (seems this happens every year) is that nearly every session block has two different sessions that I would like to attend. And some of them have three! ACK! That won’t work! What is a guy supposed to do? Well, one option is to purchase the DVDs which are recordings of the audio and projected images from each session so you can continue to attend sessions long after the Summit is officially over. Yes, many (possibly all) of these also get posted online and attendees can access those for no extra charge, but those are not necessarily all available as quickly as the DVD recording are, and the DVDs are often more convenient than downloading, especially if you want to share the training with someone who was not able to attend in person.
Remember, I don’t make any money or get any other benefit if you buy the DVDs or from anything else that I have recommended here. These are just my own thoughts, trying to help out based on my experiences from the 8 or so Summits I have attended. There is nothing like the Summit. It is an awesome experience, fantastic training, and a whole lot of fun which is just compounded if you’ll take advantage of the first part of this article and make some new friends along the way.
Our budget situation is a little different this year than in years past. We were late getting an initial budget approved. There are a number of different reasons this occurred. We had different competing priorities and the budget got pushed down the list. And that’s completely my fault for not making the budget a higher priority and getting it completed on time.
That left us with initial budget approval in early August rather than prior to June 30th. Even after that there were a number of small adjustments that needed to be made. And one large glaring mistake that needed to be fixed. We had a typo in the budget that made it through twelve versions of review. In my defense I can only say that the cell was red so of course it had to be negative! And that’s one more mistake I can add to my long and growing list of Mistakes I’ll Never Make Again.
Last week we passed a revised budget (version 17) with this corrected. This is the version we’re cleaning up and posting to the web site this week or next.
What better way to end the summer and start those productive autumn days ahead than with a fresh new version of the SSMS Tools Pack.
This is a big release with two new features that are huge productivity boosters.
First new feature are Tab Sessions. Every SQL tab you open is saved every N (default 2) minutes and is stored in a session. This works similar to internet browser sessions. Once you reopen SSMS you can restores your last session with a click of a button. You even get every window connected to the server it was previously connected to. The Tab History Window looks like this:
The second feature is Execution Plan Analyzer. It is designed to quickly help you find costliest operators by a number of properties. If that's not enough you can easily search through the whole execution plan for whatever you like.
And to top it off you can auto analyze the execution plan. The analysis reports various problems the execution plan has and suggests a most common solution. The ultimate purpose of the Execution Plan Analyzer is to make your troubleshooting quicker and easier. It uses a simple user interface that is easy to navigate and is built directly into the execution plan itself. The execution plan analyzer looks like this:
Smaller fixes include a completely redesigned SQL History Search window and various other bug fixes.
You can download the new version 2.0 at the Download page.
For more detailed feature descriptions go to the main Features Page.
Saturday…Saturday….Saturday…. September 17th. TWO THOUSAND ELEVEN!
OK, it's not a tractor pull, but it's even better: FREE SQL SERVER TRAINING! They have a bunch of great speakers lined up, and for some reason, me. (Protip: be good friends with theprogramcommittee, have sufficient bribe funds, and if all else fails, lots of alcohol, drugs and a camera. Ba-ZING! You too can speak at SQL Saturday!)
I will be presenting Revenge: The SQL! in a new and improved SQL Saturday themed presentation. Actually, it's the same ol' presentation, I just updated the slide theme to match the new SQL Saturday website design. (Yeah guys, thanks for changing that a month ago. So much for coasting on the old format.)
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
Exportizer is a free database export tool. It allows to export open table to file, clipboard, or printer. Can convert DB, DBF, text, CSV, and some other database files to text, CSV, XLS, RTF, XML, HTML, DBF, INSERT statements.
Many options ensure full control over output. There are several useful functions for data manipulating and bookmarking.The most important operations can be performed through the command line.
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
WebSQL Studio Designed in order to help you easily connect to your remote databases and manage them. it secures the data transmitted by encrypting it, allows you to perform various comparisons on remote databases as well as queries.
NoSQL Database a broad class of database management systems that differ from classic relational database management systems (RDBMSes) in some significant ways. These data stores may not require fixed table schemas, and usually avoid join operations and typically scale horizontally. NoSQL definition: Next Generation Databases mostly addressing some of the points: being non-relational, distributed, open-source and horizontally scalable. The original intention has been modern web-scale databases. The movement began early 2009 and is growing rapidly. Often more characteristics apply as: schema-free, easy replication support, simple API, eventually consistent / BASE (not ACID), a huge data amount. Non - Relational Universe. More WikiPedia NoSQL
Object Database Management System Focusing on object database technology, objects and databases, the integration of object-oriented programming and databases, persistent object life cycle management, object oriented persistence technologies, Cloud data stores and NoSQL Database. ODMG Standard
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.
MySQL Query Profile. Diagnose Performance Problems Check overall performance,etc...
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.
ABSTRACT: This talk discusses how IT professionals can go about
learning what they need to know to prevent the most significant
emerging data security vulnerabilities, and the impact these
vulnerabilities are having on electronic commerce. In this talk,
I will review how attacks such as XSRF (Cross-Site-Request-Forgery)
and SQL Injection work, and how to properly defend against them.
Then, I will present some industry-wide statistics on software
security vulnerabilities reported to various databases, and
emerging trends in the field of software security. Finally, it will
discuss the current state of security education, and provide
pointers to certification programs, books, and
organizations where you can learn more.
Speaker: Neil Daswani.
Neil has served in a variety of research , development, teaching,
and managerial roles at Google, Stanford University , DoCoMo USA Labs,
Yodlee, and Bellcore (now Telcordia Technologies). His areas of
expertise include security, wireless data technology, and peer-to-peer
systems.
He has published extensively in these areas, frequently is invited
to give talks at industry and academic conferences, and has been granted
several U.S. patents. He received a Ph.D. and a master's in computer science from Stanford University , and earned a bachelor's in computer
science with honors with distinction from Columbia University.
Open Web Application Security Project (OWASP) is not-for-profit worldwide charitable organization focused on improving the security of application software. Our mission is to make application security visible, so that people and organizations can make informed decisions about true application security risks. Everyone is free to participate in OWASP and all of our materials are available under a free and open software license.
Security Focus a mainstay in the security community. From original news content to detailed technical papers and guest columnists, we’ve strived to be the community’s source for all things security related. SecurityFocus was formed with the idea that community needed a place to come together and share its collected wisdom and knowledge. At SecurityFocus, the community has always been our primary focus. The SecurityFocus website now focuses on a few key areas that are of greatest importance to the security community.
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).
Hack-Proofing Your ASP.NET Applications Clovers issues about
SQL Injection What Is It? SQL injection is an attack in which one or more commands are inserted into a query to form a new query that was never intended by the developer. This almost always occurs when dynamic SQL is being used; that is, when you’re concatenating strings in your code to form SQL statements. SQL injection can occur in your Microsoft .NET Framework code if you’re forming a query or procedure call, and it can occur in your server-side T-SQL code as well, such as in the case of dynamic SQL in stored procedures. More ASP, Active Sever Pages coding links
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, HTML 5,
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.
Allen White (blog | twitter), marathoner, SQL Server MVP and presenter, and all-around awesome author is hosting this month's T-SQL Tuesday on sharing SQL Server Tips and Tricks. And for those of you who have attended my Revenge: The SQL presentation, you know that I have 1 or 2 of them. You'll also know that I don't recommend using anything I talk about in a production system, and will continue that advice here…although you might be sorely tempted. Suffice it to say I'm not using these examples myself, but I think they're worth sharing anyway.
At the Summit you’ll see PASS announce the total attendance and the “total registrations”. The total registrations is the sum of the conference attendees and the pre-conference registrations. A single person can be counted three times (conference plus two pre-cons) in the total registration count.
