MySQL Configuration File Variable Tutorial-Part 2


auto_increment_increment & auto_increment_offset

Both variables are use with master-to-master replication, and can be used to control the operation of AUTO_INCREMENT columns. Both variables have global and session values and each can assume an integer value between 1 and 65,535 inclusive. Setting the value of either of these two variables to 0 causes its value to be set to 1 instead. Attempting to set the value of either of these two variables to an integer greater than 65,535 or less than 0 causes its value to be set to 65,535 instead. These two variables affect AUTO_INCREMENT column behavior. But AUTO_INCREMENT column range is not limited to 65,535.

Click here for more details.

back_log

This variable deals with the number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many connection requests in a very short time. It then takes some time (although very little) for the main thread to check the connection and start a new thread. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect a large number of connections in a short period of time. In other words, this value is the size of the listen queue for incoming TCP/IP connections.

Click here for more details.

basedir

This variable specifies the path to the MySQL installation directory. All paths are usually resolved relative to this directory. If you are installing MySQL manually than this will play a important role.

Click here for more details.

big-tables

This variable is used to set the flag to store the large result sets. Allow large result sets by saving all temporary sets in files. This option prevents most “table full” errors, but also slows down queries for which in-memory tables would suffice.

Click here for more details.

binlog_cache_size

This variable basically used by that storage engines which having a transactional query support. It gives the size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (–log-bin option). If you often use large, multiple-statement transactions, you can increase this cache size to get more performance.

Click here for more details.

binlog-format

This variable specifies whether to use row-based, statement-based, or mixed replication.

Click here for more details.

bulk_insert_buffer_size

This variable is used to give a boost performance to the MyISAM engine. MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT … SELECT, INSERT … VALUES (…), (…), …, and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

Click here for more details.

character-set-filesystem

This variable specified the file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT … INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multi-byte file names are allowed, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to ‘utf8′.

Click here for more details.

character-set-server

This variable specifies the server character set. Use charset_name as the default server character set. If you use this option to specify a nondefault character set, you should also use collation-server to specify the collation.

Click here for more details.

Share

MySQL Configuration File Variable Tutorial-Part 1

Today I am going to explain the MySQL server configuration file variable. MySQL Server (mysqld) is the main program that does most of the work in a MySQL installation. When MySQL Server starts it uses the configuration file. The first thing you need to know is where MySQL gets configuration information: from command-line arguments and settings in its configuration file. On Unix-like systems, the configuration file is generally located at /etc/my.cnf or /etc/mysql/my.cnf. On windows system the configuration file generally located at mysql/bin/my.ini or if you have xampp server than it will be at xampp/mysql/bin/my.ini; If you use your operating system’s start-up scripts, this is typically the only place you’ll specify configuration settings. If you start MySQL manually, as you might do when you’re running a test installation, you can also specify settings on the command line. Below is the list of configuration variables:

Name Variable  Scope Dynamic
abort-slave-event-count
allow-suspicious-udfs
ansi
auto_increment_increment Both Yes
auto_increment_offset Both Yes
back_log Global No
backup_history_log Global Yes
backup_history_log_file Global Yes
backup_progress_log Global Yes
backup_progress_log_file Global Yes
backupdir Global Yes
basedir Global No
big-tables Session Yes
bind-address
binlog_cache_size Global Yes
binlog-do-db
binlog-format Both Yes
binlog-ignore-db
binlog-row-event-max-size
bootstrap
bulk_insert_buffer_size Both Yes
character-set-client-handshake
character-set-filesystem Both Yes
character-set-server Both Yes
character-sets-dir Global No
chroot
collation-server Both Yes
completion_type Both Yes
concurrent_insert Global Yes
connect_timeout Global Yes
console
core-file
datadir Global No
datetime_format Both Yes
debug Both Yes
debug-sync-timeout
default-storage-engine
default-table-type
default-time-zone
default_week_format Both Yes
delay-key-write Global Yes
delayed_insert_limit Global Yes
delayed_insert_timeout Global Yes
delayed_queue_size Global Yes
des-key-file
disconnect-slave-event-count
div_precision_increment Both Yes
enable-locking
enable-named-pipe
enable-pstack
engine-condition-pushdown Both Yes
event-scheduler Global Yes
exit-info
expire_logs_days Global Yes
external-locking
falcon
falcon_checkpoint_schedule Global Yes
falcon_checksums Global Yes
falcon_consistent_read Both Yes
falcon_debug_mask Global Yes
falcon_debug_server Global No
falcon_disable_fsync Global Yes
falcon_gopher_threads Global No
falcon_index_chill_threshold Global Yes
falcon_initial_allocation Global Yes
falcon_io_threads Global Yes
falcon_large_blob_threshold Global No
falcon_lock_wait_timeout Global Yes
falcon_max_transaction_backlog Global Yes
falcon_page_cache_size Global No
falcon_page_size Global No
falcon_record_chill_threshold Global Yes
falcon_record_memory_max Global Yes
falcon_record_scavenge_floor Global Yes
falcon_record_scavenge_threshold Global Yes
falcon_scavenge_schedule Global No
falcon_serial_log_buffers Global No
falcon_serial_log_dir Global No
falcon_serial_log_priority Global Yes
falcon_support_xa Global No
falcon_use_deferred_index_hash Global No
falcon_use_sectorcache Global No
falcon_use_supernodes Global No
flush Global Yes
flush_time Global Yes
ft_boolean_syntax Global Yes
ft_max_word_len Global No
ft_min_word_len Global No
ft_query_expansion_limit Global No
ft_stopword_file Global No
gdb
general-log Global Yes
general_log_file Global Yes
group_concat_max_len Both Yes
help
init_connect Global Yes
init-file Global No
init_slave Global Yes
innodb
innodb_adaptive_hash_index Global No
innodb_additional_mem_pool_size Global No
innodb_autoextend_increment Global Yes
innodb_autoinc_lock_mode Global No
innodb_buffer_pool_size Global No
innodb_checksums Global No
innodb_commit_concurrency Global Yes
innodb_concurrency_tickets Global Yes
innodb_data_file_path Global No
innodb_data_home_dir Global No
innodb_doublewrite Global No
innodb_fast_shutdown Global Yes
innodb_file_io_threads Global No
innodb_file_per_table Global No
innodb_flush_log_at_trx_commit Global Yes
innodb_flush_method Global No
innodb_force_recovery Global No
innodb_lock_wait_timeout Global No
innodb_locks_unsafe_for_binlog Global No
innodb_log_buffer_size Global No
innodb_log_file_size Global No
innodb_log_files_in_group Global No
innodb_log_group_home_dir Global No
innodb_max_dirty_pages_pct Global Yes
innodb_max_purge_lag Global Yes
innodb_mirrored_log_groups Global No
innodb_open_files Global No
innodb_rollback_on_timeout Global No
innodb_stats_on_metadata Global Yes
innodb_status_file
innodb_support_xa Both Yes
innodb_sync_spin_loops Global Yes
innodb_table_locks Both Yes
innodb_thread_concurrency Global Yes
innodb_thread_sleep_delay Global Yes
interactive_timeout Both Yes
join_buffer_size Both Yes
join_cache_level Both Yes
keep_files_on_create Both Yes
key_buffer_size Global Yes
key_cache_age_threshold Global Yes
key_cache_block_size Global Yes
key_cache_division_limit Global Yes
language Global No
large-pages Global No
local-infile
log Global Yes
log-backup-output Global Yes
log-bin Global No
log-bin-index
log-bin-trust-function-creators Global Yes
log-error Global No
log-isam
log-output Global Yes
log-queries-not-using-indexes Global Yes
log-short-format
log-slave-updates Global No
log-slow-admin-statements
log-slow-queries Global Yes
log-slow-slave-statements
log-tc
log-tc-size
log-warnings Both Yes
long_query_time Both Yes
low-priority-updates Both Yes
lower_case_file_system Global No
lower_case_table_names Global No
maria
maria-block-size No
maria-checkpoint-interval Global Yes
maria-force-start-after-recovery-failures
maria-log-dir-path
maria-log-file-size Global Yes
maria-log-purge-type Global Yes
maria-max-sort-file-size Global Yes
maria-page-checksum Global Yes
maria-pagecache-age-threshold Global Yes
maria-pagecache-buffer-size Global No
maria-pagecache-division-limit Global Yes
maria-recover Global Yes
maria-repair-threads Both Yes
maria-sort-buffer-size Both Yes
maria-stats-method Both Yes
maria-sync-log-dir Global Yes
master-bind No
master-info-file
master-retry-count
max_allowed_packet Both Yes
max_binlog_cache_size Global Yes
max-binlog-dump-events
max_binlog_size Global Yes
max_connect_errors Global Yes
max_connections Global Yes
max_delayed_threads Both Yes
max_error_count Both Yes
max_heap_table_size Both Yes
max_join_size Both Yes
max_length_for_sort_data Both Yes
max_prepared_stmt_count Global Yes
max_relay_log_size Global Yes
max_seeks_for_key Both Yes
max_sort_length Both Yes
max_sp_recursion_depth Both Yes
max_tmp_tables Both Yes
max_user_connections Both Yes
max_write_lock_count Global Yes
memlock Global No
min-examined-row-limit Both Yes
myisam-block-size
myisam_data_pointer_size Global Yes
myisam_max_sort_file_size Global Yes
myisam-recover
myisam_repair_threads Both Yes
myisam_sort_buffer_size Both Yes
myisam_stats_method Both Yes
myisam_use_mmap Global Yes
ndb_autoincrement_prefetch_sz Both Yes
ndb_index_stat_cache_entries
ndb_index_stat_enable
ndb_index_stat_update_freq
ndb_report_thresh_binlog_epoch_slip
ndb_report_thresh_binlog_mem_usage
ndb_use_transactions Both Yes
net_buffer_length Both Yes
net_read_timeout Both Yes
net_retry_count Both Yes
net_write_timeout Both Yes
new Both Yes
old Global No
old-alter-table Both Yes
old-passwords Both Yes
old-style-user-limits
one-thread
open-files-limit Global No
optimizer_prune_level Both Yes
optimizer_search_depth Both Yes
optimizer_switch Both Yes
pid-file Global No
plugin_dir Global No
plugin-load
port Global No
port-open-timeout
preload_buffer_size Both Yes
query_alloc_block_size Both Yes
query_cache_limit Global Yes
query_cache_min_res_unit Global Yes
query_cache_size Global Yes
query_cache_type Both Yes
query_cache_wlock_invalidate Both Yes
query_prealloc_size Both Yes
range_alloc_block_size Both Yes
read_buffer_size Both Yes
read_only Global Yes
read_rnd_buffer_size Both Yes
relay-log
relay-log-index
relay-log-info-file
relay_log_purge Global Yes
relay_log_recovery Global Yes
relay_log_space_limit Global No
replicate-do-db
replicate-do-table
replicate-ignore-db
replicate-ignore-table
replicate-rewrite-db
replicate-same-server-id
replicate-wild-do-table
replicate-wild-ignore-table
report-host Global No
report-password Global No
report-port Global No
report-user Global No
safe-mode
safe-show-database Global Yes
safe-user-create
safemalloc-mem-limit
secure-auth Global Yes
secure-backup-file-priv Global No
secure-file-priv Global No
server-id Global Yes
show-slave-auth-info
skip-character-set-client-handshake
skip-concurrent-insert
skip-external-locking Global No
skip-falcon
skip-grant-tables
skip-host-cache
skip-innodb
skip-innodb-checksums
skip-locking
skip-name-resolve
skip-ndbcluster
skip-networking Global No
skip-new
skip-safemalloc
skip-show-database Global No
skip-slave-start
skip-ssl
skip-stack-trace
skip-symlink
skip-thread-priority
slave_compressed_protocol Global Yes
slave-load-tmpdir Global No
slave-net-timeout Global Yes
slave-skip-errors Global No
slave_transaction_retries Global Yes
slow_launch_time Global Yes
slow-query-log Global Yes
slow_query_log_file Global Yes
socket Global No
sort_buffer_size Both Yes
sporadic-binlog-dump-fail
sql-mode Both Yes
ssl
ssl-ca Global No
ssl-capath Global No
ssl-cert Global No
ssl-cipher Global No
ssl-key Global No
ssl-verify-server-cert
standalone
symbolic-links
sync-binlog Global Yes
sync-frm Global Yes
sync-master-info Global Yes
sync-relay-log Global Yes
sync-relay-log-info Global Yes
sysdate-is-now
table_definition_cache Global Yes
table_lock_wait_timeout Global Yes
table_open_cache Global Yes
tc-heuristic-recover
temp-pool
thread_cache_size Global Yes
thread_concurrency Global No
thread_handling Global No
thread_pool_size Global No
thread_stack Global No
time_format Both Yes
time_zone Both Yes
timed_mutexes Global Yes
tmp_table_size Both Yes
tmpdir Global No
transaction_alloc_block_size Both Yes
transaction-isolation
transaction_prealloc_size Both Yes
updatable_views_with_limit Both Yes
use-symbolic-links
user
verbose
version Global No
wait_timeout Both Yes
Share

Comparison between VARCHAR and CHAR data types

These are the two highly used string types in MySQL. Both store the character values. It’s very difficult to explain exactly how these values are stored on disk and in memory, because the implementations are storage engine-dependent (for example, Falcon uses its own storage formats for almost every data type). I am going to explain for MyISAM and InnoDB.

VARCHAR

VARCHAR as name represent variable character. It stores variable-length character strings and is the most common used string data type. It can require less storage space than fixed-length types, because it uses only as much space as it needs i.e. less space is used to store shorter values.

The exception is a MyISAM table created with ROW_FORMAT=FIXED, which uses a fixed amount of space on disk for each row and can thus waste space and used lot of disk space.

VARCHAR uses 1 or 2 extra bytes to record the value’s length: 1 byte if the column’s maximum length is 255 bytes or less, and 2 bytes if it’s more. For instance the latin1 character set, a VARCHAR (10) will use up to 11 bytes of storage space. A VARCHAR (1000) can use up to 1002 bytes, because it needs 2 bytes to store length information.

VARCHAR helps performance because it saves space. However, because the rows are variable-length, they can grow when you update them, which can cause extra work. If a row grows and no longer fits in its original location, the behavior is storage engine-dependent. For example, MyISAM may fragment the row, and InnoDB may need to split the page to fit the row into it. Other storage engines may never update data in place at all.

It’s usually worth using VARCHAR when the maximum column length is much larger than the average length; when updates to the field are rare, so fragmentation is not a problem; and when you’re using a complex character set such as UTF-8, where each character uses a variable number of bytes of storage.

CHAR

CHAR is second most common used string data type. It is fixed-length. MySQL always allocates enough space for the specified number of characters. When storing a CHAR value, MySQL removes any trailing spaces. Values are padded with spaces as needed for comparisons. CHAR is useful if you want to store very short strings, or if all the values are nearly the same length. For example, CHAR is a good choice for MD5 values for user passwords, which are always the same length. CHAR is also better than VARCHAR for data that’s changed frequently, because a fixed-length row is not prone to fragmentation.

For very short columns, CHAR is also more efficient than VARCHAR; a CHAR (1) designed to hold only Y and N values will use only one byte in a single byte character set, but a VARCHAR(1) would use two bytes because of the length byte.

I am going to explain the difference between VARCHAR and CHAR with respect of storing the value.

I am going to create a table with one column of CHAR data type.


mysql> CREATE TABLE mysql_char( char_val CHAR(10));
mysql> INSERT INTO mysql_char (char_val) VALUES
->('mysql'), ('  mysql'), ('mysql  ');

When you retrieve the values, the trailing spaces have been stripped away:

mysql> SELECT CONCAT("'", char_val, "'") FROM  mysql_char;

+—————————-+

| CONCAT(“‘”, char_val, “‘”) |

+—————————-+

| ‘mysql’ |

| ‘ mysql’ |

| ‘mysql’ |

+—————————-+

If we store the same values into a VARCHAR (10) column, we get the following result upon retrieval:

mysql> SELECT CONCAT("'", varchar_val, "'") FROM mysql_varchar;

+——————————-+

| CONCAT(“‘”, varchar_val, “‘”) |

+——————————-+

| ‘mysql’ |

| ‘ mysql’ |

| ‘mysql ‘ |

+——————————-+

References:

  1. MySQL Manual
  2. High Performance MySQL
Share

Optimal Data Types Improves the MySQL Performance

Right now MySQL Supports a large number of data types, and choosing the correct type to store your data is crucial to getting good performance. The following simple guidelines can help you make better choices, no matter what type of data you are storing:

Smaller data type is better for performance.

First of all try to use the smallest data type that can appropriately store and represent your data. Smaller data types are usually faster, because they use less space on the disk, in memory, and in the CPU cache. They also generally require fewer CPU cycles to process.

Make sure you don’t miscalculate the range of values you need to store, because increasing the data type range in multiple places in your schema can be a painful and time-consuming operation. For instance if you try to increase INT data type to BIG INT it will take a long time to process. If you’re in doubt as to which is the best data type to use, choose the smallest one that you don’t think you’ll exceed. If the system is not very busy or doesn’t store much data, or if you’re in the starting phase of the design process, you can change it easily later.

Simple data type is very good for performance.

The key concept is to reduce the CPU cycles while processing the data types. Fewer CPU cycles are typically required to process operations on simpler data types. For example, integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated. Integers are very good when using as an index column.

Here are two examples: you should store dates and times in MySQL’s built-in types instead of as strings, and you should use integers for IP addresses. Sooner MySQL is going to release one data type only for IP addresses.

 

For a performance boost try to avoid NULL if possible.

Always you should define fields as NOT NULL whenever you can. Especially when the column is UNIQUE INDEX A lot of tables include nullable columns even when the application does not need to store NULL (the absence of a value), merely because it’s the default. You should be careful to specify columns as NOT NULL unless you intend to store NULL in them. It’s tougher for MySQL to optimize queries that refer to nullable columns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM. Even when you do need to store a “no value” fact in a table, you might not need to use NULL. Consider using zero, a special value, or an empty string instead. The performance improvement from changing NULL columns to NOT NULL is usually small, so don’t make finding and changing them on an existing schema a priority unless you know they are causing problems. However, if you’re planning to index columns, avoid making them nullable if possible.

Reference:

  1. High Performance MySQL
  2. MySQL Manual
Share

MySQL Performance Boost by Selecting the Right Table Engine

MySQL Performance Boost by Selecting the Right Table Engine

Today I am going to explain you how the MySQL table engine affects the database performance.  While designing MySQL-based applications, you should decide which storage engine to use for storing your data. If you don’t think about this during the initial phase, you will possible face difficulties later in the process of development of your application. MySQL default engine is MyISAM; this engine doesn’t provide a feature you need, such as transactions, or maybe the mix of read and write queries. May be your application will require more granular locking than MyISAM’s table locks. Because you can select storage engines on a table-by-table basis, you’ll need a clear impression of how each table will be used and the data it will store. It also helps to have a good indulgent of the application as a whole and its potential for growth in future.

Although many factors can affect your decision about which storage engine(s) to use, it usually boils down to a few primary considerations.

Keep in mind the following points while selecting your storage engine:

Transactions

If your application requires transactions, InnoDB is the most stable, well incorporated, proven choice at this time. However, we expect to see the up-and-coming transactional engines become strong contenders as time passes.

MyISAM is a good choice if a task doesn’t require transactions and issues mainly either SELECT or INSERT queries. Sometimes specific modules of an application (such as logging) fall into this category.

Concurrency

How best to satisfy your concurrency requirement depends on your job. If you just need to insert and read concurrently, believe it or not, MyISAM is a fine choice! If you need to allow a mixture of operations to run concurrently without interfering with each other, one of the engines with row-level locking should work well.

Backups

The need to perform regular backups may also influence your table choices. If your server can be shut down at regular intervals for backups, the storage engines are equally easy to deal with. However, if you need to perform online backups in one form or another, the choices become less clear. Also bear in mind that using multiple storage engines increases the complexity of backups and server tuning.

Crash recovery

If you have a lot of data, you should seriously consider how long it will take to recover from a crash. For example MyISAM tables generally become corrupt more easily and take much longer to recover than InnoDB tables. In fact, this is one of the most important reasons why a lot of people use InnoDB when they don’t need transactions.

Special features

Finally, you sometimes find that an application relies on particular features or optimizations that only some of MySQL’s storage engines provide. For example, a lot of applications rely on clustered index optimizations. At the moment, that limits you to InnoDB and solidDB. On the other hand, only MyISAM supports full-text search inside MySQL. If a storage engine meets one or more critical requirements, but not others, you need to either compromise or find a clever design solution. You can often get what you need from a storage engine that seemingly doesn’t support your requirements.

Share

Top 25+ Open Source e-Commerce Solution

Following are the Open Source E-Commerce Applications:-

  1. osCommerce: osCommerce is an online shop e-commerce solution that offers a wide range of out-of-the-box features that allows online stores to be setup fairly quickly with ease, and is available for free as an Open Source based solution released under the GNU General Public License. More..
  2. Magento: Magento is a feature-rich, professionally developed open-source eCommerce solution offering merchants complete flexibility and control over the look, content, and functionality of their online stores. Magento’s intuitive administration interface features powerful marketing, search engine optimization and catalog-management tools to give merchants the power to create sites that are tailored to their unique business needs. More..
  3. Zen Cart: Zen Cart is an open source online store management system. It is PHP-based, using a MySQL database and HTML components. Support is provided for several languages and currencies, and it is freely available under the GNU General Public License. Zen Cart branched from osCommerce as a separate project. Beyond some aesthetic changes, the major differences between the two systems come from Zen Cart’s architectural changes (e.g. a template system) and additional included features in the core. More…
  4. FishCart: FishCart®, in use since January 1998, is a proven Open Source e-commerce system for products, services, online payment and online donation management. Written in PHP4, FishCart® has been tested on Windows NT, Linux, and various Unix platforms. FishCart presently supports the MySQL, PostgreSQL, Solid, Oracle and MSSQL. More…
  5. VirtueMart:  VirtueMart is an Open Source E-Commerce solution to be used together with a Content Management System (CMS) called Joomla! (and Mambo). Joomla! and VirtueMart are written in PHP and made easy for use in a PHP/MySQL environment.  More…
  6. phpShop: phpShop is a PHP-powered shopping cart application. It is released under the GNU General Public License. The primary purpose of phpShop is to provide a simple shopping cart solution that is easy to customize to suit any purpose.  More…
  7. MyMarket: MyMarket is a fully functional online shopping catalog system, built using PHP and MySQL. It was created by Ying Zhang  for the purpose of teaching people about the basics of creating an E-Commerce site. More…
  8. PrestaShop: The PrestaShop e-Commerce Solution was built to take advantage of essential Web 2.0 innovations such as dynamic AJAX-powered features and next-generation ergonomy. PrestaShop guides users through your product catalog intelligently and effortlessly, turning intrigued visitors into paying customers. More…
  9. nopCommerce: nopCommerce is an open source e-commerce solution that contains both a catalog frontend and an administration tool backend. nopCommerce is a fully customizable shopping cart. It’s stable and highly usable. From downloads to documentation, nopCommerce.com offers a comprehensive base of information, resources, and support to the nopCommerce community. More…
  10. dashCommerce: dashCommerce is an ASP.NET Open Source e-Commerce Application. Built using some of the latest technologies, dashCommerce is focused on providing a robust e-commercce application that is stable and highly usable. In addition, the dashCommerce community is one of the most active communities in the open source / .NET space, so take a look around. More…
  11. WordPress e-Commerce Plugin: The WordPress e-Commerce plugin is a state-of-the-art e-Commerce platform with a focus on aesthetics, web standards and usability. It is both free and priceless at the same time. More..
  12. OXID eShop: OXID eShop Community Edition is a proven and flexible open source software. Thousands of online businesses worldwide use its extensive functionality to create optimum eCommerce solutions. With its modular, state-of-the-art and standards-based architecture, customization is easy. More..
  13. Substruct: The first and most robust Ruby on Rails open source e-commerce project. Open source Ruby on Rails E-Commerce. More..
  14. OpenCart: OpenCart is an open source PHP-based online shopping cart system. A robust e-commerce solution for Internet merchants with the ability to create their own online business and participate in e-commerce at a minimal cost. More..
  15. AgoraCart: The powerful “Free” Open Source ecommerce shopping cart software solution offers you a very wide range of features that allow you to setup an online storefront or shop ranging from a simple template based store for novices to the integration of complex web design concepts created by your web designer. More ..
  16. StoreSprite: A powerful, free php/mysql shopping cart easy to install, customise and maintain,  no complex scripting to negotiate design and deploy a top rate store that your client can manage with little or no training. More..
  17. Spree Open Source E-Commerce: Spree capitalizes on the dynamic nature of the Ruby progamming language to provide the most flexible commerce platform available. Spree has been designed from the ground up to be as open and extensible as possible. You no longer have to be frustrated with the limited set of choices provided by most platforms. Spree is maintained by an active team of developers who continue to refine the code based on lessons learned from the challenges of real world deployments. More..
  18. DigiStore: Digistore Ecommerce 4.0 is a fully featured open source ecommerce solution based on the popular osCommerce engine. Boasting improvements both to the administration and the end user experience – Digistore has quickly become a popular choice for people wishing to market products online. More..
  19. Satchmo: Like most Open Source projects, Satchmo was started to “scratch an itch.” This particular itch was to create a framework for developing a python based shopping cart framework software using Django. More..
  20. cpCommerce: cpCommerce is an open-source e-commerce solution that is maintained by templates and modules. This Ecommerce Script combines many utilities found in other “free” ecommerce scripts, except I allow you to completely customize the entire design by editing a single template file and the CSS (Cascading StyleSheets) each section pertains to. Due to how the script was developed, the program finds your template, and is told where to place each element in the program using easy to read tags. More..
  21. Ubercart: Ubercart is an exciting open source e-commerce package that fully integrates your online store with Drupal, the leading open source content management system. This is a killer combination for anyone looking to build a community around a product, sell access to premium content, offer paid file downloads, and much much more! More..
  22. Chromium Cart: Chromium Cart is the premier open source (free) PHP-based online shopping cart system aimed at both beginners as well as professionals. Chromium Cart is an easy to use, yet widely expandable e-commerce solution. Unlike many of the “clones” out there, ChromiumCart was developed from the ground up using MVC framework. More..
  23. DantoCart: DantoCart is an open source shopping cart solution released under the GNU General Public License. This means that you are free to use, distribute and modify DantoCart under the terms of the GNU License. More..
  24. eclime: eclime is a very powerful Smarty™ based e-commerce/shopping cart software build from trusted osCommerce 2.2 engine, with many useful contributions added. It has all the features needed to run a successful internet store and can be customized to whatever configuration you need. More..
  25. osCSS: osCSS is the leading Open Source online shop e-commerce solution that is available for free under the GNU General Public License. It features a rich set of out-of-the-box online shopping cart functionality that allows store owners to setup, run, and maintain their online stores with minimum effort and with no costs, license fees, or limitations involved. More..
  26. OFBIZ: The Apache Open For Business Project is an open source enterprise automation software project licensed under the Apache License Version 2.0. By open source enterprise automation we mean: Open Source ERP, Open Source CRM, Open Source E-Business / E-Commerce, Open Source SCM, Open Source MRP, Open Source CMMS/EAM, and so on. More..

Download the features checklist of few of the above application:E-commerce Checklist

Share

Query Performance-Identifying Slow Queries Tutorial

Figuring out which queries are slow is usually easier than figuring out why they’re slow and making the necessary changes to fix them. The easiest way to track them is to let MySQL do some of the work for you. By enabling the slow query log, you instruct MySQL to log every query that takes longer than a specified number of seconds. In addition to the query, it also logs some other metadata.

# Time: 090708 18:00:25

# User@Host: root[root] @ localhost []

# Query_time: 24  Lock_time: 0  Rows_sent: 0  Rows_examined: 957115

SET timestamp=1247056225;

INSERT INTO topsearch(key_count,keyword,queryId) SELECT sum(key_count) key_count, keyword, queryId FROM (SELECT keyword, queryId, count( keyword ) key_count FROM running_queries WHERE keyword != ” AND queryId != ” GROUP BY keyword, queryId ORDER BY key_count DESC LIMIT 45)t GROUP BY keyword ORDER BY keyword;

While the log contains a lot of useful information, there’s one very important bit of information missing: an idea of why the query was slow. Sure, if the log says 12,000,000 rows were examined and 1,200,000 sent to the client, you know why it was slow. But things are rarely that clear cut. Worse yet, you may find a slow query, paste it into your favorite MySQL client, and find that it executes in a fraction of a second.

You must be careful not to read too much information into the slow query log. When a query appears in the log, it doesn’t mean that it’s a bad query—or even a slow one. It simply means that the query took a long time then. It doesn’t mean that the query will take a long time now or in the future.

There are any numbers of reasons why a query may be slow at one time but not at others:

  • A table may have been locked, causing the query to wait. The Lock_time indicates how long the query waited for locks to be released.
  • None of the data or indexes may have been cached in memory yet. This is common when MySQL is first started or hasn’t been well tuned.
  • A nightly backup process was running, making all disks I/O considerably slower.
  • The server may have been handling hundreds of other unrelated queries at that same time, and there simply wasn’t enough CPU power to do the job efficiently.

The list could go on. The bottom line is this: the slow query log is nothing more than a partial record of what happened. You can use it to generate a list of possible suspects, but you really need to investigate each of them in more depth. Of course, if you happen to see the same query appearing in the log over and over, there’s a very good chance you have a slow query on your hands.

MySQL also comes with mysqldumpslow, a Perl script that can summarize the slow query log and provide a better idea of how often each slower query executes. That way you don’t waste time trying to optimize a 30-second slow query that runs once a day, while there are five other 2-second slow queries that run thousands of time per day. You can download this file at the end of this article.

There is one more tool which is used to find out slow queries i.e. mytop to perform real-time query monitoring, including slow queries.

The most basic reason a query doesn’t perform well is because it’s working with too much data. Some queries just have to sift through a lot of data and can’t be helped. That’s unusual, though; most bad queries can be changed to access less data. We’ve found it useful to analyze a poorly performing query in two steps:

  1. Find out whether your application is retrieving more data than you need. That usually means it’s accessing too many rows, but it might also be accessing too many columns.
  2. Find out whether the MySQL server is analyzing more rows than it needs.

Are You Asking the Database for Data You Don’t Need?

Some queries ask for more data than they need and then throw some of it away. This demands extra work of the MySQL server, adds network overhead,* and consumes memory and CPU resources on the application server.

Here are a few typical mistakes:

Fetching more rows than needed

One common mistake is assuming that MySQL provides results on demand, rather than calculating and returning the full result set. We often see this in applications designed by people familiar with other database systems. These developers are used to techniques such as issuing a SELECT statement that returns many rows, then fetching the first N rows, and closing the result set (e.g., fetching the 100 most recent articles for a news site when they only need to show 10 of them on the front page). They think MySQL will provide them with these 10 rows and stop executing the query, but what MySQL really does is generate the complete result set. The client library then fetches all the data and discards most of it. The best solution is to add a LIMIT clause to the query.

Fetching all columns from a multitable join

If you want to retrieve all actors who appear in Academy Dinosaur, don’t write the query this way:

mysql> SELECT * FROM user

-> INNER JOIN user_course USING(user_id)

-> INNER JOIN desc USING(desc_id)

-> WHERE dec.title = ‘Academy End’;

That returns all columns from all three tables. Instead, write the query as follows:

mysql> SELECT * FROM user….

Fetching all columns

You should always be suspicious when you see SELECT *. Do you really need all columns? Probably not. Retrieving all columns can prevent optimizations such as covering indexes, as well as adding I/O, memory, and CPU overhead for the server. Some DBAs ban SELECT * universally because of this fact, and to reduce the risk of problems when someone alters the table’s column list.

Of course, asking for more data than you really need is not always bad. In many cases we’ve investigated, people tell us the wasteful approach simplifies development, as it lets the developer use the same bit of code in more than one place. That’s a reasonable consideration, as long as you know what it costs in terms of performance. It may also be useful to retrieve more data than you actually need if you use. Some type of caching in your application, or if you have another benefit in mind. Fetching and caching full objects may be preferable to running many separate queries that retrieve only parts of the object.

Download : mysqldumpslow

Share

MySQL DELETE Statement across Multiple Tables Tutorial

Single-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY ...]

[LIMIT row_count]

For the single-table syntax, the DELETE statement deletes rows from tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the ROW_COUNT() function. The WHERE clause, if given, specifies the conditions that identify which rows to delete. With no WHERE clause, all rows are deleted. If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted.

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

OR

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

where_condition is an expression that evaluates to true for each row to be deleted.

Important facts:

  • Currently, you cannot delete from a table and select from the same table in a subquery.
  • You need the DELETE privilege on a table to delete rows from it. You need only the SELECT privilege for any columns that are only read, such as those named in the WHERE clause.
  • DELETE statement with no WHERE clause deletes all rows. A faster way to do this, when you do not need to know the number of deleted rows, is to use TRUNCATE TABLE. However, within a transaction or if you have a lock on the table, TRUNCATE TABLE cannot be used whereas DELETE can.
  • If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is reused later for a BDB table, but not for a MyISAM or InnoDB table.
  • If you delete all rows in the table with DELETE FROM tbl_name (without a WHERE clause) in autocommit mode, the sequence starts over for all storage engines except InnoDB and MyISAM. There are some exceptions to this behavior for InnoDB tables.
  • The time required to delete individual rows is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasing the key_buffer_size system variable.
  • To delete all rows from a table, TRUNCATE TABLE tbl_name is faster than than DELETE FROM tbl_name. Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.

The DELETE statement supports the following modifiers:

  • If you specify LOW_PRIORITY, the server delays execution of the DELETE until no other clients are reading from the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE).
  • For MyISAM tables, if you use the QUICK keyword, the storage engine does not merge index leaves during delete, which may speed up some kinds of delete operations.
  • The IGNORE keyword causes MySQL to ignore all errors during the process of deleting rows. (Errors encountered during the parsing stage are processed in the usual manner.) Errors that are ignored due to the use of IGNORE are returned as warnings.

Examples:

DELETE FROM user WHERE name = ‘Veer’;

This query will delete all the rows from the table user.

DELETE FROM user WHERE name = ‘Veer’ LIMIT 4;

Normally, MySQL makes no guarantees about which four records selected by the WHERE clause it will delete. An ORDER BY clause in conjunction with LIMIT provides better control.

DELETE FROM user WHERE name = ‘Veer’ ORDER BY id LIMIT 4;

This will delete the first four record from the table sorted in the ascending order of id field. That means it leaves the records which has higher id value.

Multiple-table DELETE statements can be written in two formats. For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted. For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted. The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching. The following example demonstrates one syntax, for a query that deletes rows from a table t1 where the id values match those in a table t2:

DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;

The second syntax is slightly different:

DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;

To delete the matching records from both tables, the statements are:

      DELETE t1, t2 FROM t1, t2 WHERE t1.id = t2.id;

Or

DELETE FROM t1, t2 USING t1, t2 WHERE t1.id = t2.id;

Delete statement using JOINS

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Or

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

Above both statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

Above statement delete rows that exist in t1 that have no match in t2.

In DELETE statement you can use the alias also like this:

DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2
WHERE a1.id=a2.id;

DELETE Statement using a group by function and on single table.

DELETE FROM employee USING employee INNER JOIN (SELECT employee_id
FROM employee
GROUP BY employee_id

HAVING COUNT(*)>1) AS emp USING (employee_id);

DELETE statement can work with multiple joins

DELETE a1, a2, a3, a4, a5
FROM atom AS a1
LEFT JOIN atom_desc AS a2 ON a2.desc_id = a1.id
LEFT JOIN atom_review AS a3 ON a3.review_id = a2.id
LEFT JOIN atom_content AS a4 ON a4.content_id = a1.id
LEFT JOIN atom_history AS a5 ON a5.history_id = a4.id
WHERE a1.name = ‘Veer’;
Share

What is MySQL Configuration File?

Configuring a MySQL server is often just a matter of editing the configuration file to make any changes you need and then restarting the server. While that sounds rather simple, adjusting the server’s configuration is something you’re not likely to do on a daily basis. More likely, you’ve installed MySQL, configured it minimally or with the defaults, and then let it run. Most users never go back and adjust the server configuration until a problem arises. As a result, it’s easy to forget how to configure MySQL.

Another possibility is that you didn’t even know there was a configuration file for MySQL. For the majority of projects, MySQL’s default configuration is more than sufficient on modern hardware. It may not be as fast as it can be (because you haven’t optimized it), but it will certainly meet your basic needs.

File Locations

When MySQL starts, it reads its configuration files in a particular order, unless told otherwise. On Unix, the order is:

  1. /etc/my.cnf
  2. datadir/my.cnf
  3. ~/.my.cnf

On Windows, the order:

  1. %SystemRoot%/my.ini
  2. C:\my.cnf

Three command-line arguments affect how MySQL reads its configuration files:

–no-defaults

Tells MySQL not to read any configuration files.

–defaults-file=/path/to/file

Tells MySQL to read this file only, and any other files explicitly declared with –defaults-extra-file.

–defaults-extra-file=/path/to/file

Tells MySQL to read this file after reading the /etc/my.cnf global configuration file .

Files read later in the process override those set in previously read files. If both /etc/my.cnf and datadir/my.cnf specify a value for the TCP port that MySQL should listen to, the latter takes precedence.

This behavior can be quite helpful when you need to run multiple servers either on the same host or on several different hosts. You can give all servers an identical copy of /etc/my.cnf that specifies all the values that aren’t specific to a single host. With that out of the way, the few host-specific settings can be maintained in a small supplemental file such as datadir/my.cnf.

A similar strategy works if you’d like to run multiple servers on a single host. By putting all the common settings in /etc/my.cnf and the server-specific settings in each datadir/my.cnf, it’s easy to keep several servers running with a minimum of effort.

For example, perhaps you want to run a couple different instances of the MySQL server, one for each character set you plan to use (to make your life easier). You might put all your “common” settings in /etc/my.cnf and the following in /etc/my.english.cnf:

default-character-set=latin1

port=3306

socket=/var/lib/mysql/english.sock

Your /etc/my.german.cnf file has:

default-character-set=latin1_de

port=3307

socket=/var/lib/mysql/german.sock

You might even have /etc/my.korean.cnf with:

default-character-set=euc_kr

port=3308

socket=/var/lib/mysql/korean.sock

Now, when you start up the three servers, you want each to load all the settings from the shared /etc/my.cnf file, and then get settings from one of each of the previous language-based configuration files. You can use a command like the following:

$ mysqld_safe –defaults-extra-file=/etc/my.german.cnf

$ mysqld_safe –defaults-extra-file=/etc/my.english.cnf

$ mysqld_safe –defaults-extra-file=/etc/my.korean.cnf

This command yields three different mysqld instances, running on ports 3306 through 3308, each using the language-specific configuration options mentioned in the file indicated by the defaults-extra-file switch.

MySQL is usually installed as a service on Windows. As a result, Windows users must call c:\mysql\bin\mysqld directly to pass command-line arguments.

File Format

The configuration file format consists of one or more sections, each of which may contain one or more lines. Sections begin with a name in square brackets, such as [mysqld]; this identifies the program to which the options should be applied. Each line contains a comment, a key/value pair, a set-variable directive, or a Boolean directive. Blank lines are ignored.

Two special section names can occur in each configuration file: [server] and [client]. Items listed in the [server] block apply to the MySQL server process. Those in the [client] section apply to all client programs that use the MySQL C client library, including mysql, mysqlhotcopy, and mysqldump.

Comments begin with # or ; and continue to the end of the line:

# this is a comment

; so is this

There is no multiline comment format. You can’t place a comment at the end of an otherwise non-empty line:

key_buffer=128M # a comment can't go here

The key/value pairs are settings such as:

user = mysql

port = 3306

The set-variable statements look like key/value pairs in which the value is a key/value pair itself:

set-variable = key_buffer=384M

set-variable = tmp_table_size=32M

Spaces aren’t important in set-variable lines. You can also write the two previous lines as follows:

set-variable = key_buffer = 384M

set-variable=tmp_table_size=32M

Either way, MySQL will understand you. However, consider using some space to enhance readability.

As of Version 4.1, the set-variable= portion of the variable definition is no longer needed and is deprecated. In current versions:

set-variable = key_buffer=384M

and:

key_buffer=384M

are both interpreted in an identical manner by the server at startup time. If you are running a version that supports leaving out the set-variable clause, it probably is best to do so because it won’t be supported forever. We’ve chosen to use the older format here because it’s what you’re likely to have already, and the sample configuration files in the standard MySQL distribution continue to use it.

The few boolean directives are just stated plainly:

skip-bdb

Individual lines in the configuration file are limited to 2 KB in length. While it’s rare that you’ll ever need to use a line that long, it can occasionally be a problem.

Sample Files

my-small.cnf- MySQL config file for small systems. This is for a system with little memory (<= 64M) where MySQL is only used from time to time and it’s important that the mysqld daemon doesn’t use much resources.

my-medium.cnf- MySQL config file for medium systems. This is for a system with little memory (32M – 64M) where MySQL plays an important part, or systems up to 128M where MySQL is used together with other programs (such as a web server).

my-large.cnf- MySQL config file for large systems. This is for a large system with memory = 512M where the system runs mainly MySQL.

my-huge.cnf- MySQL config file for very large systems. This is for a large system with memory of 1G-2G where the system runs mainly MySQL.

my-innodb-heavy-4G.cnf- MySQL config file for very large systems for innodb only. This is a MySQL example config file for systems with 4GB of memory running mostly MySQL using InnoDB only tables and performing complex queries with few connections.

To use a sample file, simply copy it to /etc/my.cnf (or systemdir\win.ini on Windows) and making changes as necessary. While none is likely to be ideal for any particular setup, each file is a good starting point for setting up a new system. Failure to make adjustments to the sample configuration can lead to worse performance in some cases.

Share

Tutorial for MySQL data validation

Though most validation of data is generally controlled by applications, there are still ample situations where data is manipulated directly on the database. MySQL validation basically depends on the SQL modes of the server. Both INSERT and UPDATE statements can be run with the IGNORE option to allow for certain type of validation errors to go through.

What are server SQL modes?

Server SQL modes define what SQL syntax MySQL should support and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. The MySQL Server applies these modes individually to different clients.

Many operational characteristics of MySQL Server can be configured by setting the SQL mode. This mode consists of optional values that each controls some aspect of query processing. By setting the SQL mode appropriately, a client program can instruct the server how strict or forgiving to be about accepting input data, enable or disable behaviours relating to standard SQL conformance, or provide better compatibility with other database systems. It’s necessary to understand how to do this because references to the SQL mode occur throughout this study guide.

The SQL mode is controlled by means of the sql_mode system variable. To assign a value to this variable, use a SET statement. The value should be an empty string, or one or more mode names separated by commas. If the value is empty or contains more than one mode name, it must be quoted. If the value contains a single mode name, quoting is optional. SQL mode values are not case sensitive, although this study guide always writes them in uppercase. Here are some examples:

  • Clear the SQL mode:

SET sql_mode = ”;

  • Set the SQL mode using a single mode value:

SET sql_mode = ANSI_QUOTES;

SET sql_mode = ‘TRADITIONAL’;

  • Set the SQL mode using multiple mode names:

SET sql_mode = ‘IGNORE_SPACE,ANSI_QUOTES’;

SET sql_mode = ‘STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO’;

To check the current sql_mode setting, select its value like this:

mysql> SELECT @@sql_mode;

+———————————————-+

| @@sql_mode                                   |

+———————————————-+

| STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO |

+———————————————-+

Some SQL mode values are composite modes that actually enable a set of modes. Values in this category include ANSI and TRADITIONAL. To see which mode values a composite mode consists of, retrieve the value after setting it:

mysql> SET sql_mode=’TRADITIONAL”;

Query OK, 0 rows affected (0.07 sec)

mysql> SELECT @@sql_mode\G

*************************** 1. row ***************************

@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,

NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,

NO_AUTO_CREATE_USER

1 row in set (0.03 sec)

The following list describes all supported modes:

  • ALLOW_INVALID_DATES

Don’t do full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

The server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To allow such dates, enable ALLOW_INVALID_DATES.

  • ANSI_QUOTES

Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier.

  • ERROR_FOR_DIVISION_BY_ZERO

Produce an error in strict mode (otherwise a warning) when a division by zero (or MOD(X,0)) occurs during an INSERT or UPDATE. If this mode is not enabled, MySQL instead returns NULL for divisions by zero. For INSERT IGNORE or UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is NULL.

  • HIGH_NOT_PRECEDENCE

The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression was parsed as (NOT a) BETWEEN b AND c. The old higher-precedence behaviour can be obtained by enabling the HIGH_NOT_PRECEDENCE SQL mode.

mysql> SET sql_mode = '';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 0
mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
mysql> SELECT NOT 1 BETWEEN -5 AND 5;
        -> 1
  • IGNORE_SPACE

Allow spaces between a function name and the “(” character. This causes built-in function names to be treated as reserved words. For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error:

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax

The table name should be quoted:

mysql> CREATE TABLE `count` (i INT);
Query OK, 0 rows affected (0.00 sec)

The IGNORE_SPACE SQL mode applies to built-in functions, not to user-defined functions or stored functions. It is always allowable to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled.

  • NO_AUTO_CREATE_USER

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified.

  • NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behaviour for 0 so that only NULL generates the next sequence number.

This mode can be useful if 0 has been stored in a table’s AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

  • NO_BACKSLASH_ESCAPES

Disable the use of the backslash character (“\”) as an escape character within strings. With this mode enabled, backslash becomes an ordinary character like any other.

  • NO_DIR_IN_CREATE

When creating a table, ignore all INDEX DIRECTORY and DATA DIRECTORY directives. This option is useful on slave replication servers.

  • NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

Up through MySQL 5.1.11, with NO_ENGINE_SUBSTITUTION disabled, the default engine is used and a warning occurs if the desired engine is known but disabled or not compiled in. If the desired engine is invalid (not a known engine name), an error occurs and the table is not created or altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable for any reason (whether disabled or invalid).

As of MySQL 5.1.12, storage engines can be pluggable at runtime, so the distinction between disabled and invalid no longer applies. All unavailable engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable.

  • NO_FIELD_OPTIONS

Do not print MySQL-specific column options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

  • NO_KEY_OPTIONS

Do not print MySQL-specific index options in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

  • NO_TABLE_OPTIONS

Does not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE. This mode is used by mysqldump in portability mode.

  • NO_UNSIGNED_SUBTRACTION

In integer subtraction operations, do not mark the result as UNSIGNED if one of the operands is unsigned. In other words, the result of a subtraction is always signed whenever this mode is in effect, even if one of the operands is unsigned. For example, compare the type of column c2 in table t1 with that of column c2 in table t2:

mysql> SET SQL_MODE='';
mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
mysql> DESCRIBE t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c2    | bigint(21) unsigned |      |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
mysql> SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
mysql> DESCRIBE t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c2    | bigint(21) |      |     | 0       |       |
+-------+------------+------+-----+---------+-------+

Note that this means that BIGINT UNSIGNED is not 100% usable in all contexts.

mysql> SET SQL_MODE = '';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+
mysql> SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
  • NO_ZERO_DATE

In strict mode, don’t allow '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. When not in strict mode, the date is accepted but a warning is generated.

  • NO_ZERO_IN_DATE

In strict mode, do not accept dates where the year part is nonzero but the month or day part is 0 (for example, '0000-00-00' is legal but '2010-00-01' and '2010-01-00' are not). If used with the IGNORE option, MySQL inserts a '0000-00-00' date for any such date. When not in strict mode, the date is accepted but a warning is generated.

  • ONLY_FULL_GROUP_BY

Do not allow queries for which the SELECT list refers to no aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY clause:

SELECT name, address, MAX(age) FROM t GROUP BY name;

As of MySQL 5.1.11, this mode also restricts references to no aggregated columns in the HAVING clause that are not named in the GROUP BY clause.

  • PAD_CHAR_TO_FULL_LENGTH

By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval. This mode was added in MySQL 5.1.20.

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.37 sec)
mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.01 sec)
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1   | CHAR_LENGTH(c1) |
+------+-----------------+
| xy   |               2 |
+------+-----------------+
1 row in set (0.00 sec)
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1         | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy         |              10 |
+------------+-----------------+
1 row in set (0.00 sec)
  • PIPES_AS_CONCAT

Treat || as a string concatenation operator (same as CONCAT()) rather than as a synonym for OR.

  • REAL_AS_FLOAT

Treat REAL as a synonym for FLOAT. By default, MySQL treats REAL as a synonym for DOUBLE.

  • STRICT_ALL_TABLES

Enable strict mode for all storage engines. Invalid data values are rejected. Additional detail follows.

  • STRICT_TRANS_TABLES

Enable strict mode for transactional storage engines, and when possible for no transactional storage engines. Additional details follow.

Strict mode controls how MySQL handles input values that are invalid or missing. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

For transactional tables, an error occurs for invalid or missing values in a statement when either of the STRICT_ALL_TABLES or STRICT_TRANS_TABLES modes are enabled. The statement is aborted and rolled back.

For no transactional tables, the behaviour is the same for either mode, if the bad value occurs in the first row to be inserted or updated. The statement is aborted and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value occurs in the second or later row, the result depends on which strict option is enabled:

  • For STRICT_ALL_TABLES, MySQL returns an error and ignores the rest of the rows. However, in this case, the earlier rows still have been inserted or updated. This means that you might get a partial update, which might not be what you want. To avoid this, it is best to use single-row statements because these can be aborted without changing the table.
  • For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and insert the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement.

Strict mode disallows invalid date values such as '2004-04-31'. It does not disallow dates with zero months or day parts such as '2004-04-00' or “zero” dates. To disallow these as well, enable the NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes in addition to strict mode.

If you are not using strict mode (that is, neither STRICT_TRANS_TABLES nor STRICT_ALL_TABLES is enabled), MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behaviour by using INSERT IGNORE or UPDATE IGNORE.

Share