import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;public class Example {public static void main(String[] args) throws SQLException {String url = "jdbc:mysql://localhost:3306/mydatabase";Properties props = new Properties();props.setProperty("user", "myusername");props.setProperty("password", "mypassword");try (Connection conn = DriverManager.getConnection(url, props)) {// Use the connection...}}}
import org.testcontainers.containers.MariaDBContainer;
<dependency> <groupId>org.testcontainers</groupId> <artifactId>mariadb</artifactId> <version>1.18.0</version> <scope>test</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.30</version> <scope>test</scope> </dependency>
MariaDBContainer<?> container = new MariaDBContainer<>("mariadb:10.3") .withDatabaseName("mydb") .withUsername("user") .withUrlParam("user", "user") .withUrlParam("password", "test"); container.start(); String url = container.getJdbcUrl();
jdbc:mysql://localhost:3458/mydb?password=test&user=root
CREATE DATABASE mydb
public String getSchema() throws SQLException {
try {
synchronized(this.getConnectionMutex()) {
this.checkClosed();
return this.propertySet.getEnumProperty(PropertyKey.databaseTerm).getValue()
== DatabaseTerm.SCHEMA ? this.database : null;
}
} catch (CJException var5) {
throw SQLExceptionsMapping.translateException(var5, this.getExceptionInterceptor());
}
}
public String getCatalog() throws SQLException {
try {
synchronized(this.getConnectionMutex()) {
return this.propertySet.getEnumProperty(PropertyKey.databaseTerm).getValue()
== DatabaseTerm.SCHEMA ? null : this.database;
}
} catch (CJException var5) {
throw SQLExceptionsMapping.translateException(var5, this.getExceptionInterceptor());
}
}The string data types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.
A fixed-length nonbinary (character) string
A variable-length nonbinary (character) string
CHAR is fixed length while VARCHAR is variable length. That means, a CHAR(x) string has exactly x characters in length, including spaces. A VARCHAR(x) string can have up to x characters and it cuts off trailing spaces, thus might be shorter than the declared length.
CREATE TABLE IF NOT EXISTS ADV.GSR_FUNCTIONPERMISSION ( ... REGISTRATIONALLOWED CHAR(1) DEFAULT 0 NOT NULL, );
A small non-binary string
In SQL, there is no specific "text" column type. However, many SQL database systems provide data types that can store large amounts of textual data, such as "VARCHAR" or "TEXT" data types.For example, MySQL provides a "TEXT" data type that can store up to 65,535 characters, while PostgreSQL provides a "VARCHAR" data type that can store up to 1 GB of textual data.So while "text" is not a standard SQL column type, there are commonly used data types that can be used to store textual data in SQL databases.
A very small non-binary string
A medium-sized non-binary string
A large non-binary string
Type | Maximum length
-----------+-------------------------------------
TINYTEXT | 255 (2 8−1) bytes
TEXT | 65,535 (216−1) bytes = 64 KiB
MEDIUMTEXT | 16,777,215 (224−1) bytes = 16 MiB
LONGTEXT | 4,294,967,295 (232−1) bytes = 4 GiBThe "status" command is a client-side command that provides basic information about the server, including the version, connection ID, current user, and current database. It is executed within the MariaDB client, and the output is displayed in the console.On the other hand, the "show status" command is a server-side command that displays a list of server status variables and their values. It provides detailed information about the server's internal state, such as the number of queries executed, the number of connections, and the amount of memory used.
> status--------------mysql Ver 15.1 Distrib 10.9.3-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapperConnection id: 4Current database:Current user: root@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server: MariaDBServer version: 10.9.3-MariaDB-1:10.9.3+maria~ubu2204 mariadb.org binary distributionProtocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8mb4Db characterset: utf8mb4Client characterset: latin1Conn. characterset: latin1UNIX socket: /run/mysqld/mysqld.sockUptime: 11 min 43 secThreads: 1 Questions: 6 Slow queries: 0 Opens: 17 Open tables: 10 Queries per second avg: 0.008--------------
> select user from mysql.user;+-------------+| User |+-------------+| root || user1 || mariadb.sys || root |+-------------+
$ docker run -d--name mariadb1093-p 3316:3306-e MARIADB_USER=user1-e MARIADB_PASSWORD=upassw1-e MARIADB_ROOT_PASSWORD=rpassw1-v $HOME/DOCKER/dockerMariaDB/datafiles1093:/var/lib/mysqlmariadb:latest
- Oracle syntax compatibility mode for running Oracle Database applications without change.
- Detailed guide section on migrating from SQL Server to MariaDB.
- JSON Data type and rich set of JSON functions
- Selection of a specific storage engine such as InnoDB, Aria, and MyISAM
FLOAT 4 ByteREAL 8 Byte Synonym for DOUBLE DOUBLE 8 Byte
MySQL treats REAL as a synonym for DOUBLE, unless the REAL_AS_FLOAT SQL mode is enabled
- The REAL data type is an approximate number with floating point numeric data.- REAL value is approximate which means not all range of values can be represented exactly.- REAL is equivalent to FLOAT(24).
MySQL Type Java TypeCHAR, VARCHAR, LONGVARCHAR StringTEXT StringTINYINT byteSMALLINT shortINT (INTEGER) intBIGINT longNUMERIC, DECIMAL java.math.BigDecimalREAL floatFLOAT, DOUBLE doubleBIT booleanBINARY, VARBINARY, LONGVARBINARY byte[]BLOB, MEDIUMBLOB, LARGEBLOB byte[]DATE java.sql.DateTIME java.sql.TimeDATETIME, TIMESTAMP java.sql.Timestamp
Orchestrator supports:Discovery: Orchestrator actively crawls through your topologies and maps them. It reads basic MySQL info such as replication status and configuration, and provides a slick visualisation of your topologies, including replication problems, even in the face of failures.
Refactoring: Orchestrator understands replication rules. It knows about binlog file:position, GTID, Pseudo GTID, Binlog Servers.
Recovery: Orchestrator uses a holistic approach to detect master and intermediate master failures. Based on information gained from the topology itself, it recognises a variety of failure scenarios.
CREATE USER 'orc_client_user'@'172.20.0.10' IDENTIFIED BY 'orc_client_password'; GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orc_client_user'@'172.20.0.10'; GRANT SELECT ON mysql.slave_master_info TO 'orc_client_user'@'172.20.0.10';
There is a meeting room system that provides users to reserve a meeting room, and when the user has successfully reserved the meeting room, a new corresponding data will be added in the table as follows....The problem occurs when two users want to occupy the same time slot in the same meeting room simultaneously, and they can both pass the first SELECT validation, so they can both insert a reservation, and a conflict occurs. And such a situation can not be solved by adding a lock, because there is no row to lock at the beginning.
To solve such phantom reads, the developer must use some tricks to reveal conflicts hidden under the same table.One way is to create a new table and pre-fill it with data to act as a coordinator for simultaneous operations. In the case of this meeting room system, we can create a table time_slots that lists all time slots in advance as follows.When the meeting room is to be reserved, we not only execute SELECT on the original booking, but also SELECT on time_slots, and we can add FOR UPDATE because the data already exists. It is worth noting that the new SELECT FOR UPDATE is executed before the original SELECT.In that case, when the expected time slots of two simultaneous users overlap, they will be blocked by the exclusive lock and become one after the other, and the latter will fail directly because it sees the result of the previous completion.
