Warning: mysqli_error() expects exactly 1 parameter, 0 given error

Warning: mysqli_error() expects exactly 1 parameter, 0 given error::

 

mysqli_error() needs you to pass the connection to the database as a parameter. Documentation here has some helpful examples:

Try altering your problem line like so and you should be in good shape:

$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error($myConnection));

 

source: http://php.net/manual/en/mysqli.error.php

Database engine difference of InnoDB, MyISAM, Memory

Database engine difference of InnoDB, MyISAM, Memory

InnoDB:: 

This is the default storage engine for MySQL 5.5 and higher.  It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints.  It supports commit, rollback, and crash-recovery capabilities to protect data.  It also support row-level locking. It’s “consistent nonlocking reads” increases performance when used in a multiuser environment.  It stores data in clustered indexes which reduces I/O for queries based on primary keys.

MyISAM::

This storage engine, manages non transactional tables, provides high-speed storage and retrieval, supports full text searching.

MEMORY::

Provides in-memory tables, formerly known as HEAP.  It sores all data in RAM for faster access than storing data on disks.  Useful for quick looks up of reference and other identical data.

 

 

For Database engines Please visit the link below,

http://stackofcodes.in/2018/03/13/database-engine-php/

Database engine in php

Database engine in php

Storage engines (underlying software component) are MySQL components, that can handle the SQL operations for different table types to store and manage information in a database. InnoDB is mostly used general-purpose storage engine and as of MySQL 5.5 and later it is the default engine. There are many storage engines available in MySQL and they are used for different purposes.

Version : MySQL 5.6

Storage engines of MySQL

EnginesDescription
InnoDBThis is the default storage engine for MySQL 5.5 and higher. It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints. It supports commit, rollback, and crash-recovery capabilities to protect data. It also support row-level locking. It’s “consistent nonlocking reads” increases performance when used in a multiuser environment. It stores data in clustered indexes which reduces I/O for queries based on primary keys.
MyISAMThis storage engine, manages non transactional tables, provides high-speed storage and retrieval, supports full text searching.
MEMORYProvides in-memory tables, formerly known as HEAP. It sores all data in RAM for faster access than storing data on disks. Useful for quick looks up of reference and other identical data.
MERGEGroups more than one similar MyISAM tables to be treated as a single table, can handle non transactional tables, included by default.
EXAMPLEYou can create tables with this engine, but can not store or fetch data. Purpose of this is to teach developers about how to write a new storage engine.
ARCHIVEUsed to store a large amount of data, does not support indexes.
CSVStores data in Comma Separated Value format in a text file.
BLACKHOLEAccepts data to store but always returns empty.
FEDERATEDStores data in a remote database.

 

For , List of Storage Engines supported by your MySQL installation, Visit here,

http://stackofcodes.in/2018/03/13/list-storage-engines-supported-mysql-installation/

List of Storage Engines supported by your MySQL installation

source:  https://www.w3resource.com/

List of Storage Engines supported by your MySQL installation

List of Storage Engines supported by your MySQL installation

Setting the Storage Engine

In CREATE TABLE STATEMENT you can add ENGINE table option to mention a storage engine. See the following CREATE TABLE statements, where different engines have used :

CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

In MySQL 5.6, the default engine is InnoDB. The default storage engine is used if you do not mention the other engine name in ENGINE option. You can specify the default engine by using the –default-storage-engine server startup option (Command-Line Format), or by setting the default-storage-engine option in the my.cnf configuration file.

You can set the default storage engine for the current session by setting the default_storage_engine variable using set command.

SET default_storage_engine=ARCHIVE;

If you want to convert a table form one storage engine to another, use an ALTER TABLE statement. See the following statement :

ALTER TABLE table1 ENGINE = InnoDB;

To store the table and column definitions for a new table, MySQL always creates an .frm file. Depending on the storage engine the table’s index and data may be stored in one or more other files. The server creates the .frm file above the storage engine level.

MySQL: InnoDB Storage Engine

InnoDB is a storage engine for MySQL that balances high reliability and high performance. As of MySQL 5.5 and later, it is the default storage engine.

Feaures of InnoDB storage engine :

Storage limits64TBTransactionsYesLocking granularityRow
MVCC (Multiversion concurrency control)YesGeospatial data type supportYesGeospatial indexing supportNo
B-tree indexesYesT-tree indexesNoHash indexesNo
Full-text search indexesYesClustered indexesYesData cachesYes
Index cachesYesCompressed dataYesEncrypted dataYes
Cluster database supportNoReplication supportYesForeign key supportYes
Backup / point-in-time recoveryYesQuery cache supportYesUpdate statistics for data dictionaryYes

Advantages of InnoDB storage engine

  • InnoDB has maximum performance when processing large data volumes.
  • Its DML operations (add, update and delete data) is ACID (atomic, consistent, isolated and durable) model compatible, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.
  • Row-level locking (locks are placed on single records (rows)) system increase multi-user concurrency and performance. All InnoDB locks held by a transaction are released when the transaction is committed or aborted.
  • InnoDB tables arrange your data on disk to optimize queries based on primary keys.
  • InnoDB supports FOREIGN KEY constraints to maintain data integrity. Therefore inserts, updates, and deletes are all checked to ensure they do not result in inconsistencies across different tables.
  • It is possible to mix InnoDB tables with tables from other MySQL storage engines within the same statement. For example, you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.

Handling AUTO_INCREMENT in InnoDB :

InnoDB provides a method that improves scalability and performance of SQL statements that insert rows into tables with AUTO_INCREMENT columns. To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column (col1 in the example) must be defined as part of an index. See the following example :

mysql> CREATE TABLE table1 (col1 INT(10) NOT NULL AUTO_INCREMENT, 
col2 CHAR(30), PRIMARY KEY (col1));
Query OK, 0 rows affected (0.50 sec)

Handling FOREIGN KEY Constraints in InnoDB :

MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. Foreign key definitions for InnoDB tables are subject to the following conditions :

  • InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.
  • InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys.
  • InnoDB allows a foreign key constraint to reference a non-unique key. This is an InnoDB extension to standard SQL.

Limitation: InnoDB table :

  • Maximum 1017 columns are allowed in a table (raised in MySQL 5.6.9 from the earlier limit of 1000).
  • Maximum 64 secondary indexes are allowed in a table. Secondary indexes is a type of InnoDB index that represents a subset of table columns.
  • By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
  • The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
  • The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
  • Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
  • The maximum table space size is four billion database pages (64TB) and the minimum table space size is slightly larger than 10MB.

MySQL: MyISAM Storage Engine

MyISAM storage engine is based on the older ISAM storage engine (not available now) but has many useful extensions.

Features of MyISAM storage engine :

Storage limits256TBTransactionsNoLocking granularityTable
MVCC (Multiversion concurrency control)NoGeospatial data type supportYesGeospatial indexing supportYes
B-tree indexesYesT-tree indexesNoHash indexesNo
Full-text search indexesYesClustered indexesNoData cachesNo
Index cachesYesCompressed dataYesEncrypted dataYes
Cluster database supportNoReplication supportYesForeign key supportNo
Backup / point-in-time recoveryYesQuery cache supportYesUpdate statistics for data dictionaryYes

Each MyISAM table is stored on disk in three files.

  • An .frm file stores the table format.
  • The data file has an .MYD (MYData) extension.
  • The index file has an .MYI (MYIndex) extension.

Creating MyISAM tables : 
Use CREATE TABLE statement to create am MyISAM table with ENGINE clause. As of MySQL 5.6, it is necessary to use ENGINE clause to specify the MyISAM storage engine because InnoDB is the default engine. Here is an example :

mysql> CREATE TABLE table2 (col1 INT, col2 CHAR(30)) ENGINE = MYISAM;
Query OK, 0 rows affected (0.19 sec)

Main characteristics of MyISAM tables :

  • Up to 63-bit file length large files are supported on file systems and operating systems that support large files.
  • (232)2(1.844E+19) rows are allowed in a MyISAM table.
  • Maximum 64 number of indexes and 16 number of columns per index are allowed.
  • The maximum key length is 1000 bytes.
  • Internal handling of one AUTO_INCREMENT column per table is supported.
  • You can put the data file and index file in different directories on different physical devices to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE
  • BLOB and TEXT columns can be indexed.
  • NULL values are permitted in indexed columns. This takes 0 to 1 bytes per key.
  • Each character column can have a different character set.
  • Support for a true VARCHAR type; a VARCHAR column starts with a length stored in one or two bytes.
  • Tables with VARCHAR columns may have fixed or dynamic row length.
  • The sum of the lengths of the VARCHAR and CHAR columns in a table may be up to 64KB.
  • Arbitrary length UNIQUE constraints.

Corrupted MyISAM Tables :

MyISAM table format is very reliable, but in some occasion you can get corrupted tables if any of the following events occur :

  • The mysqld (Known as MySQL Server) process is killed in the middle of a write.
  • Hardware failures.
  • An unexpected computer shutdown occurs.
  • Using an external program to modify a table
  • A software bug in the MySQL or MyISAM code.

MySQL: MEMORY Storage Engine

The MEMORY storage engine creates tables that are stored in memory. Because the data can be crashed due to hardware or power issues, you can only use these tables as temporary work areas or read-only caches for data pulled from other tables. When the MySQL server halts or restarts, the data in MEMORY tables is lost.

Features of MEMORY storage engine :

Storage limitsRAMTransactionsNoLocking granularityTable
MVCCNoGeospatial data type supportNoGeospatial indexing supportNo
B-tree indexesYesT-tree indexesNoHash indexesYes
Full-text search indexesNoClustered indexesNoData cachesN/A
Index cachesN/ACompressed dataNoEncrypted dataYes
Cluster database supportNoReplication supportYesForeign key supportNo
Backup / point-in-time recoverYesQuery cache supportYesUpdate statistics for data dictionaryYes

When to Use MEMORY storage engine:

  • Operations involving transient, non-critical data such as session management or caching.
  • In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.
  • By default, an index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix.
  • The InnoDB internal maximum key length is 3500 bytes, but MySQL itself restricts this to 3072 bytes (combined index key in a multi-column index).
  • The maximum row length except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is about 8000 bytes for the default page size of 16KB.
  • Internally InnoDB supports row sizes larger than 65,535 bytes, but MySQL itself imposes a row-size limit of 65,535 for the combined size of all columns.
  • The maximum tablespace size is four billion database pages (64TB) and the minimum tablespace size is slightly larger than 10MB.

MySQL: MERGE Storage Engine

The MERGE storage engine (also known as MRG_MyISAM) is a collection of identical MyISAM tables (identical column and index information with same order) that can be used as single table. You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table.

Security issue: If a user has access to MyISAM table, say t1, that user can create a MERGE table m1 that accesses t1. However, if the administrator revokes the user’s privileges on t1, the user can continue to access the data of t1 through m1.

MySQL: CSV Storage Engine

The CSV storage engine stores data in text files using comma-separated values format and the CSV storage engine is always compiled into the MySQL server. The server creates a table format file (.frm extension) and a data file (.csv extension) in the database directory when you create a CSV table. Both .frm and .csv files name begins with the table name. The data file is a plain text file and the storage engine saves data in comma-separated values format. The following example shows how to create and use a CSV table :

CSV Limitations :

  • Does not support indexing.
  • Does not support partitioning.
  • All columns must have the NOT NULL attribute in a CSV table.

MySQL: ARCHIVE Storage Engine

The ARCHIVE storage engine is used to store large amounts of unindexed data in a very small footprint. The storage engine is included in MySQL binary distributions. To enable this storage engine (if you build MySQL from source), invoke CMake with the -DWITH_ARCHIVE_STORAGE_ENGINE option. When you create an ARCHIVE table, the server creates a table format file (.frm extension) in the database directory.

Features of ARCHIVE storage engine:

Storage limitsNoneTransactionsNoLocking granularityTable
MVCCNoGeospatial data type supportYesGeospatial indexing supportNo
B-tree indexesNoT-tree indexesNoHash indexesNo
Full-text search indexesNoClustered indexesNoData cachesNo
Index cachesNoCompressed dataYesEncrypted dataYes
Cluster database supportNoReplication supportYesForeign key supportNo
Backup / point-in-time recoveryYesQuery cache supportYesUpdate statistics for data dictionaryYes

ARCHIVE storage engine supports

  • INSERT and SELECT.
  • ORDER BY operations
  • BLOB columns
  • AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index.
  • AUTO_INCREMENT table option in CREATE TABLE statements

ARCHIVE storage engine does not support

  • DELETE, REPLACE, or UPDATE
  • Inserting a value into an AUTO_INCREMENT column less than the current maximum column value.

ARCHIVE storage engine: Storage & Retrieval

  • The ARCHIVE engine uses zlib lossless data compression (see http://www.zlib.net/).
  • Rows are compressed as they are inserted.
  • On retrieval, rows are uncompressed on demand; there is no row cache.

MySQL: EXAMPLE Storage Engine

The EXAMPLE storage engine is a stub engine that does nothing and serve as an example in the MySQL source code that clarify how to begin writing new storage engines. To examine the source for the EXAMPLE engine, look in the storage/example directory of a MySQL source distribution. When you create an EXAMPLE table :

  • The server creates a table format file (.frm extension) in the database directory.
  • No other files are created
  • No data can be stored into the table.
  • Retrievals return an empty result.
  • Does not support indexing.

To enable the EXAMPLE storage engine if you build MySQL from source, invoke CMake with the -DWITH_EXAMPLE_STORAGE_ENGINE option.

MySQL: BLACKHOLE Storage Engine

The BLACKHOLE storage engine acts as a “black hole” that accepts data but returns an empty result. To enable the BLACKHOLE storage engine (in case of MySQL build from source), invoke CMake with the -DWITH_BLACKHOLE_STORAGE_ENGINE option. When you create a BLACKHOLE table, the server creates a table format file (.frm) in the database directory. The BLACKHOLE storage engine supports all kinds of indexes. Here is an example :

mysql> CREATE TABLE test10 (slno INT, message CHAR(40)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO test10 VALUES(1, 'message1'), (2, 'message2');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test10;
Empty set (0.03 sec)

MySQL: FEDERATED Storage Engine

The FEDERATED storage engine is used to access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables. To include the FEDERATED storage engine (in case of MySQL build from source), invoke CMake with the -DWITH_FEDERATED_STORAGE_ ENGINE option.
To enable FEDERATED (not enabled by default in the running server), you must start the MySQL server binary using the –federated option. To check the source for the FEDERATED engine, look in the storage/ federated directory of a MySQL source distribution.

Create a FEDERATED table

You can create a FEDERATED table in the following ways :

  • Using CONNECTION
  • Using CREATE SERVER

Using CONNECTION : To use this method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. Seethe following example :

CREATE TABLE federated_table (
    roll_no     INT(3) NOT NULL AUTO_INCREMENT,
    stu_name   VARCHAR(42) NOT NULL DEFAULT '',
    total_marks  INT(5) NOT NULL DEFAULT '0',
    PRIMARY KEY  (roll_no),
    INDEX stu_name (stu_name),
    INDEX total_marks (total_marks)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://[email protected]_host:9306/federated/test10_table';

The format of the connection string is as follows :

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

Where:

  • scheme : A recognized connection protocol. Only mysql is supported as the scheme value at this point.
  • The user name for the connection, must have been created on the remote server, and have suitable privileges to perform the required actions like SELECT, INSERT, UPDATE, and so forth on the remote table.
  • The password for user_name. (Optional)
  • host_name: The host name or IP address of the remote server.
  • port_num: The port number (default : 3306) for the remote server. (Optional)
  • db_name: The name of the database holding the remote table.
  • tbl_name: The name of the remote table.

Using CREATE SERVER: To use this method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. See the following example:

CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)

The server_name is used in the connection string when creating a new FEDERATED table.

Differences between InnoDB and MyISAM

FeatureInnoDBMyISAM
Storage limits64TB256TB
TransactionsYesNo
Locking granularityRowTable
MVCCYesNo
Geospatial data type supportYesYes
Geospatial indexing supportNoYes
B-tree indexesYesYes
T-tree indexesNoNo
Hash indexesNoNo
Full-text search indexesYesYes
Clustered indexesYesNo
Data cachesYesNo
Index cachesYesYes
Compressed dataYesYes
Encrypted dataYesYes
Cluster database supportNoNo
Replication supportYesYes
Foreign key supportYesNo
Backup / point-in-time recoveryYesYes
Query cache supportYesYes
Update statistics for data dictionaryYesYes

 

source:  https://www.w3resource.com/