MariaDB SQL Grammar [Part 4]

MariaDB Error Codes

MariaDB shares many error codes with MySQL, it also has its own set of specific error codes. Understanding error codes is important for troubleshooting database issues, as it provides insight into what went wrong and how to fix it.

Shared MariaDB/MySQL Error Codes

MariaDB and MySQL share error codes from 1000 to 1800. These codes include common errors such as syntax errors, connection errors, and permission errors. Here are some of the most common shared error codes:

• Error Code 1045: Access denied for user 'user'@'host' (using password: YES) This error is caused when the user does not have sufficient permissions to access a specific database or table. It may also indicate an incorrect username or password.

• Error Code 1064: You have an error in your SQL syntax This error is caused by a syntax error in an SQL statement. It could be a missing or extra character, a typo, or incorrect syntax.

• Error Code 1146: Table 'database.table' doesn't exist This error is caused when a query is trying to access a non-existent table. It could be a typo in the table name or a missing CREATE TABLE statement.

• Error Code 1364: Field 'field_name' doesn't have a default value This error is caused when a query is trying to insert a row into a table without specifying a value for a non-null field that does not have a default value.

MariaDB-Specific Error Codes

In addition to the shared error codes, MariaDB has its own set of specific error codes. These codes are numbered from 1900 and up. Some of the most common MariaDB-specific error codes include:

• Error Code 1905: Lock wait timeout exceeded; try restarting transaction This error is caused when a transaction is waiting for a lock to be released, but the lock is held by another transaction for too long. It can be fixed by either increasing the lock wait timeout or by optimizing the queries to reduce the time needed to acquire the lock.

• Error Code 1932: The used command is not allowed with this MariaDB version This error is caused when a command that is not supported by the current version of MariaDB is used. It could be an outdated query or a new feature that is not yet supported.

• Error Code 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) This error is caused when the MySQL server is not running or when the connection to the server is refused. It could also indicate an incorrect socket file path.

• Error Code 2013: Lost connection to MySQL server during query This error is caused when the connection to the server is lost while a query is being executed. It could be caused by network issues, server overload, or a timeout.

For detailed error codes refer : https://mariadb.com/kb/en/mariadb-error-codes/#shared-mariadbmysql-error-codes

Numeric Literals

Numeric literals are a fundamental part of any programming language, including SQL. Numeric literals are used to represent numeric values, such as integers, decimals, and floating-point numbers. In this article, we will discuss the syntax of numeric literals in SQL and provide examples to illustrate their usage.

Syntax of Numeric Literals Numeric literals in SQL are written as a sequence of digits from 0 to 9. The sign can precede the digits, but it is optional for positive numbers. The integer and decimal parts of a decimal number are separated by a dot (.). If the integer part is zero, it can be omitted, but the literal must begin with a dot. The following are some examples of decimal literals:

• 10

• +10

• -10

• 0.1

• .1

• +0.1

• +.1

In some cases, adding zeroes at the end of a decimal number can increment the precision of the expression where the number is used. For example, the PI() function by default returns a number with six decimal digits. However, the PI()+0.0000000000 expression (with ten zeroes) returns a number with ten decimal digits.

In addition to decimal literals, SQL supports exponential notation for representing very large or very small numbers. The exponent is preceded by an E or e character. The exponent can be preceded by a sign and must be an integer. A number N with an exponent part X is calculated as N * POW(10, X). The following are some examples of exponential notation:

• 0.2E3 -- 0.2 * POW(10, 3) = 200

• .2e3

• .2e+2

• 1.1e-10 -- 0.00000000011

• -1.1e10 -- -11000000000

SQL also supports hexadecimal literals, which are interpreted as numbers when used in numeric contexts. Hexadecimal literals begin with a 0x prefix, followed by a sequence of hexadecimal digits. The following are some examples of hexadecimal literals:

• 0x10 -- 16 in decimal

• 0xFF -- 255 in decimal

Conclusion Numeric literals are a crucial aspect of SQL syntax. They are used to represent numeric values, such as integers, decimals, and floating-point numbers. SQL provides support for decimal literals, exponential notation, and hexadecimal literals. Understanding the syntax and usage of numeric literals is essential for working with numeric data in SQL.

More Details:

https://github.com/AgeDB-Enterprise/maria-server

https://github.com/apache/age