J

Daftar Referensi

[BEL07] Bell, Charles A. (2007). Expert MySQL. Apress. [HAE02] Haecke, Bernard Van (2002). JDBC 3.0 - Java Database Connectivity. Hungry Minds, Inc. [JGR07]

www.jgroups.org

[SAJ07]

Sajuthi, Satria Putra (2007). Implementasi Fault Tolerant JDBC Connector pada DBMS MySQL untuk Mendukung Replikasi di Mid Tier pada Arsitektur Three Tier. Institut Teknologi Bandung.

[SIL02]

Silberschatz, Avi, Henry F. Korth, S. Sudarshan (2002). Database System Concepts. McGrawHill.

[SHO87] Shooman, Martin (1987). Software Engineering: Design, Reliability, and Management. McGraw-Hill. [ZHA01] Zhao, W., L. E. Moser, P. M. Melliar-Smith (2001). Increasing the Reliability of Three Tier Applications. Department of Electrical and Computer Engineering, University of California.

ix

Daftar Pustaka

[COU01] Couloruis, George, Jean Dollimore, Tim Kindberg (2001). Distributed System Concepts and Design. Addison Wesley. [SQL07] MySQL Internal Manual (2007).

x

Lampiran A Parameter Opsional JDBC URL pada MySQL Connector/J Klasifikasi

Nama properti

Nilai default

user password com.mysql.jdbc. socketFactory

StandardSocketF actory

Koneksi dan otentikasi

connectTimeout

0

socketTimeout

0

connectionLifecycleInterceptors useConfigs interactiveClient

false

localSocketAddress propertiesTransform useCompression

false

tcpKeepAlive

true

tcpNoDelay

true

tcpRcvBuf

0

tcpSndBuf

0

tcpTrafficClass

0

autoReconnect

false

autoReconnectForPools

false

failOverReadOnly

true

maxReconnects

3

Availabilitas dan

reconnectAtTxEnd

false

clustering

initialTimeout

2

roundRobinLoadBalance

false

queriesBeforeRetryMaster

50

secondsBeforeRetryMaster

30

Jaringan

resourceId

Keamanan

allowMultiQueries

false

useSSL

false

requireSSL

false

allowLoadLocalInfile

true

allowURLInLocalInfile

false

A-1

A-2

Klasifikasi

Nama properti

Nilai default

clientCertificateKeyStorePassword clientCertificateKeyStoreType clientCertificateKeyStoreUrl

Keamanan

trustCertificateKeyStorePassword trustCertificateKeyStoreType trustCertificateKeyStoreUrl

Performansi

paranoid

false

callableStmtCacheSize

100

metadataCacheSize

50

prepStmtCacheSize

25

prepStmtCacheSqlLimit

256

alwaysSendSetIsolation

true

maintainTimeStats

true

useCursorFetch

false

blobSendChunkSize

1048576

cacheCallableStmts

false

cachePrepStmts

false

cacheResultSetMetadata

false

cacheServerConfiguration

false

defaultFetchSize

0

dontTrackOpenResources

false

dynamicCalendars

false

elideSetAutoCommits

false

enableQueryTimeouts

true

holdResultsOpenOverStatementClose

false

largeRowSizeThreshold

2048

loadBalanceStrategy

random

locatorFetchBufferSize

1048576

rewriteBatchedStatements

false

useDirectRowUnpack

true

useDynamicCharsetInfo

true

useFastDateParsing

true

useFastIntParsing

true

useJvmCharsetConverters

false

useLocalSessionState

false

useReadAheadInput

true

A-3

Klasifikasi

Nama properti

Nilai default com.mysql.jdbc.

logger

log.StandardLog ger

gatherPerfMetrics

false

profileSQL

false

profileSql

Debugging dan

reportMetricsIntervalMillis

30000

maxQuerySizeToLog

2048

packetDebugBufferSize

20

slowQueryThresholdMillis

2000

slowQueryThresholdNanos

0

useUsageAdvisor

false

autoGenerateTestcaseScript

false

autoSlowLog

true com.mysql.jdbc.

profiling clientInfoProvider

JDBC4CommentCli entInfoProvider

dumpMetadataOnColumnNotFound

false

dumpQueriesOnException

false

enablePacketDebug

false

explainSlowQueries

false

includeInnodbStatusInDeadlockExcept ions

false

logSlowQueries

false

logXaCommands

false

resultSetSizeThreshold

100

traceProtocol

false

useNanosForElapsedTime

false

useUnicode

true

characterEncoding characterSetResults connectionCollation

Lain- lain

useBlobToStoreUTF8OutsideBMP utf8OutsideBmpExcludedColumnNamePat tern utf8OutsideBmpIncludedColumnNamePat tern sessionVariables

false

A-4

Klasifikasi

Nama properti

Nilai default

allowNanAndInf

false

autoClosePStmtStreams

false

autoDeserialize

false

blobsAreStrings

false

capitalizeTypeNames

true

clobCharacterEncoding

Lain- lain

clobberStreamingResults

false

continueBatchOnError

true

createDatabaseIfNotExist

false

emptyStringsConvertToZero

true

emulateLocators

false

emulateUnsupportedPstmts

true

functionsNeverReturnBlobs

false

generateSimpleParameterMetadata

false

ignoreNonTxTables

false

jdbcCompliantTruncation

true

maxRows

-1

netTimeoutForStreamingResults

600

noAccessToProcedureBodies

false

noDatetimeStringSync

false

noTimezoneConversionForTimeType

false

nullCatalogMeansCurrent

true

nullNamePatternMatchesAll

true

overrideSupportsIntegrityEnhancemen tFacility

false

padCharsWithSpace

false

pedantic

false

pinGlobalTxToPhysicalConnection

false

populateInsertRowWithDefaultValues

false

processEscapeCodesForPrepStmts

true

relaxAutoCommit

false

retainStatementAfterResultSetClose

false

rollbackOnPooledClose

true

runningCTS13

false

serverTimezone statementInterceptors strictFloatingPoint

false

strictUpdates

true

A-5

Klasifikasi

Lain- lain

Nama properti

Nilai default

tinyInt1isBit

true

transformedBitIsBoolean

false

treatUtilDateAsTimeStamp

true

ultraDevHack

false

useGmtMillisForDatetimes

false

useHostsInPrivileges

true

useInformationSchema

false

useJDBCCompliantTimezoneShift

false

useOldAliasMetadataBehavior

false

useOldUTF8Behavior

false

useOnlyServerErrorMessages

true

useSSPSCompatibleTimezoneShift

false

useServerPrepStmts

false

useSqlStateCodes

true

useStreamLengthsInPrepStmts

true

useTimezone

false

useUnbufferedInput

true

yearIsDateType

true

zeroDateTimeBehavior

exception

Lampiran B Implementasi Pembentukan Koneksi pada MySQL Connector/J Pada MySQL Connector/J yang merupakan connector untuk program Java ke database MySQL, mekanisme pembentukan koneksi secara detil adalah sebagai berikut. Kelas com.mysql.jdbc.Driver yang merupakan turunan dari kelas com.mysql.jdbc.NonRegisteringDriver java.sql.Driver

dan

implementasi

dari

interface

akan memanggil method connect untuk menginstansiasi kelas

com.mysql.jdbc.ConnectionImpl.

// file: com.mysql.jdbc.NonRegisteringDriver.java // Try to make a database connection to the given URL public java.sql.Connection connect(String url, Properties info) throws SQLException { … Connection newConn = com.mysql.jdbc.ConnectionImpl.getInstance( host(props), port(props), props, database(props), url); … }

Kode B-1 Pembentukan koneksi ke JDBC URL

java.sql.Driver com.mysql.jdbc.NonRegisteringDriver <>+NonRegisteringDriver() +java.sql.Connection connect(String url, Properties info)

com.mysql.jdbc.Driver <>+Driver()

Gambar B-1 Diagram kelas Driver

B-1

B-2

Kelas

com.mysql.jdbc.ConnectionImpl

com.mysql.jdbc.Connection ,

merupakan

implementasi

interface

sedangkan interface com.mysql.jdbc.Connection

sendiri merupakan turunan dari interface java.sql.Connection. Pada instansiasi ConnectionImpl

akan

dipanggil

method

handleNewInstance

dari

kelas

com.mysql.jdbc.Util.

java.sql.Connection

com.mysql.jdbc.ConnectionProperties

com.mysql.jdbc.Connection com.mysql.jdbc.ConnectionPropertiesImpl

com.mysql.jdbc.ConnectionImpl -static final Constructor JDBC_4_CONNECTION_CTOR -long connectionId -MysqlIO io #static Connection getInstance(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) <>#ConnectionImpl(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) -initializeDriverProperties(Properties info) #createNewIO(boolean isForReconnect)

com.mysql.jdbc.JDBC4Connection <>+JDBC4Connection(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url)

Gambar B-2 Diagram kelas Connection // file: com.mysql.jdbc.ConnectionImpl.java // Creates a connection instance protected static Connection getInstance(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) throws SQLException { … return (Connection) Util.handleNewInstance(JDBC_4_CONNECTION_CTOR, new Object[] { hostToConnectTo, Constants.integerValueOf(portToConnectTo), info, databaseToConnectTo, url}); }

Kode B-2 Pembentukan objek Connection

Pada pemanggilan method handleNewInstance, akan dilakukan instansiasi kelas com.mysql.jdbc.JDBC4Connection com.mysql.jdbc.ConnectionImpl.

dan

parent-nya,

yaitu

kelas

B-3

com.mysql.jdbc.Util +static final handleNewInstance(Constructor ctor, Object[] args)

Gambar B-3 Diagram kelas Util // file: com.mysql.jdbc.Util.java // Handles constructing new instance with the given constructor public static final Object handleNewInstance(Constructor ctor, Object[] args) throws SQLException { … return ctor.newInstance(args); … }

Kode B-3 Penanganan pembentukan objek dengan konstruktor yang diberikan

// file: com.mysql.jdbc.JDBC4Connection.java public JDBC4Connection(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) throws SQLException { super(hostToConnectTo, portToConnectTo, info, databaseToConnectTo, url); }

Kode B-4 Pembentukan objek JDBC4Connection

Objek JDBC4Connection tersebut kemudian akan membangun channel Input/Output ke MySQL server dengan memanggil method createNewIO. // file: com.mysql.jdbc.ConnectionImpl.java // Creates a connection to a MySQL Server protected ConnectionImpl(String hostToConnectTo, int portToConnectTo, Properties info, String databaseToConnectTo, String url) throws SQLException { … initializeDriverProperties(info); … createNewIO(false); … }

Kode B-5 Pembentukan koneksi ke MySQL server

B-4

Method createNewIO akan menginstansiasi kelas com.mysql.jdbc.MysqlIO yang menangani koneksi TCP ke MySQL server. Obyek MysqlIO tersebut akan melakukan handshaking pada method doHandshake. // file: com.mysql.jdbc.ConnectionImpl.java // Creates an IO channel to the server protected void createNewIO(boolean isForReconnect) throws SQLException { … this.io = new MysqlIO(newHost, newPort, mergedProps, getSocketFactoryClassName(), this, getSocketTimeout(), this.largeRowSizeThreshold.getValueAsInt()); this.io.doHandshake(this.user, this.password, this.database); this.connectionId = this.io.getThreadId(); }

Kode B-6 Pembentukan channel Input/Output ke MySQL server Pada instansiasi kelas MysqlIO, akan dilakukan pembentukan koneksi dengan menggunakan java.net.Socket. Socket yang terbentuk kemudian akan di-assign ke atribut mysqlConnection. com.mysql.jdbc.MysqlIO #java.net.Socket mysqlConnection -SocketFactory socketFactory <>+MysqlIO(String host, int port, Properties props, String socketFactoryClassName, ConnectionImpl conn, int socketTimeout, int useBufferRowSizeThreshold) +doHandshake(String user, String password, String database)

Gambar B-4 Diagram kelas MysqlIO

com.mysql.jdbc.SocketFactory

com.mysql.jdbc.StandardSocketFactory #java.net.Socket rawSocket +java.net.Socket afterHandshake() +java.net.Socket beforeHandshake() +java.net.Socket connect(String hostname, int portNumber, Properties props)

Gambar B-5 Diagram kelas SocketFactory // file: com.mysql.jdbc.MysqlIO.java // Connect to the MySQL server and setup a stream connection public MysqlIO(String host, int port, Properties props,

B-5 String socketFactoryClassName, ConnectionImpl conn, int socketTimeout, int useBufferRowSizeThreshold) throws IOException, SQLException { … this.mysqlConnection = this.socketFactory.connect(this.host, this.port, props); … this.mysqlConnection = this.socketFactory.beforeHandshake(); … }

Kode B-7 Pembentukan koneksi melalui socket

Method doHandshake yang dipanggil setelah pembentukan koneksi berhasil akan membaca Handshake Initialization Packet yang diterima dari MySQL server. Handshake Initialization Packet merupakan paket greeting yang digunakan untuk mengidentifikasikan server. Setelah selesai membaca Handshake Initialization Packet, objek MysqlIO akan mengirimkan Client Authentication Packet ke MySQL server. Client Authentication Packet digunakan untuk otentikasi client ke MySQL server.

// file: com.mysql.jdbc.MysqlIO.java // Initialize communications with the MySQL server void doHandshake(String user, String password, String database) throws SQLException { … // Read Handshake Initialisation Packet Buffer buf = readPacket(); // Get protocol version this.protocolVersion = buf.readByte(); … // Get server version this.serverVersion = buf.readString("ASCII"); … // Get the thread ID of the server thread handling the request threadId = buf.readLong(); // Get first „scramble string‟ salt for encryption this.seed = buf.readString("ASCII"); … // Get server capabilities this.serverCapabilities = buf.readInt(); … // Get server character set this.serverCharsetIndex = buf.readByte() & 0xff;

B-6 // Get server status this.serverStatus = buf.readInt(); … // Get rest of salt String seedPart2 = buf.readString("ASCII"); … Buffer packet = null; … // Set Client Authentication Packet if ((this.serverCapabilities & CLIENT_SECURE_CONNECTION) != 0) { this.clientParam |= CLIENT_SECURE_CONNECTION; … secureAuth411(null, packLength, user, password, database, true); … } else { packet = new Buffer(packLength); … // Set client parameter packet.writeLong(this.clientParam); // Set maximum packet size packet.writeLong(this.maxThreeBytes); // Set character set to „latin1‟ packet.writeByte((byte) 8); // Set of bytes reserved for future use packet.writeBytesNoNull(new byte[23]); // Set user data packet.writeString(user, CODE_PAGE_1252, this.connection); … // Set password data packet.writeString(Util.newCrypt(password, this.seed), CODE_PAGE_1252, this.connection); … // Set database name packet.writeString(database, CODE_PAGE_1252, this.connection); … // Send Client Authentication Packet send(packet, packet.getPosition()); … this.mysqlConnection = this.socketFactory.afterHandshake(); … } }

Kode B-8 Handshaking dan otentikasi dengan MySQL server

Pada mode CLIENT_SECURE_CONNECTION, akan dilakukan pemanggilan method checkErrorPacket

untuk memeriksa respon MySQL server tentang otentikasi.

Otentikasi hanya akan dianggap berhasil jika MySQL server mengembalikan paket OK.

B-7

// file: com.mysql.jdbc.MysqlIO.java // Secure authentication for 4.1.1 and newer servers void secureAuth411(Buffer packet, int packLength, String user, String password, String database, boolean writeClientParams) throws SQLException { … // Set client parameter packet.writeLong(this.clientParam); // Set maximum packet size packet.writeLong(this.maxThreeBytes); // Set character set to „utf8‟ packet.writeByte((byte) UTF8_CHARSET_INDEX); // Set of bytes reserved for future use packet.writeBytesNoNull(new byte[23]); … // Set user data packet.writeString(user, "utf-8", this.connection); … // Set password data packet.writeByte((byte) 0x14); … packet.writeBytesNoNull(Security.scramble411(password, this.seed)); // Set database name packet.writeString(database, "utf-8", this.connection); // Send Client Authentication Packet send(packet, packet.getPosition()); // Read what server thinks about the new auth message report checkErrorPacket(); }

Kode B-9 Secure authentication dengan MySQL server

Lampiran C Penanganan Koneksi pada MySQL Penanganan koneksi dari client pada MySQL dilakukan oleh program utama yang bernama mysqld. Program tersebut akan melakukan initial check, menginisialisasi komponen, melakukan listen di port yang ditentukan, serta menunggu koneksi dari client dengan memanggil fungsi handle_connections_sockets. // file: sql\mysqld.cc int main(int argc, char **argv) { … if (init_common_variables(MYSQL_CONFIG_NAME, argc, argv, load_default_groups)) unireg_abort(1); // Will do exit … if (init_server_components()) exit(1); network_init(); … create_shutdown_thread(); create_maintenance_thread(); … handle_connections_sockets(0); … (void) pthread_mutex_lock(&LOCK_thread_count); … (void) pthread_mutex_unlock(&LOCK_thread_count); … }

Kode C-1 Program utama MySQL server

Setiap mendeteksi koneksi dari client, fungsi handle_connections_sockets akan membuat thread baru dengan memanggil prosedur create_new_thread dan menghidupkan objek THD yang akan mengelola informasi mengenai thread tersebut. // file: sql\mysqld.cc // Handle new connections and spawn new process to handle them pthread_handler_t handle_connections_sockets(void *arg __attribute__((unused))) { … THD *thd;

C-1

C-2 … DBUG_PRINT("general",("Waiting for connections.")); … while (!abort_loop) { … /* Don't allow too many connections */ if (!(thd= new THD)) … if (sock == unix_sock) thd->security_ctx->host=(char*) my_localhost; … create_new_thread(thd); } … }

Kode C-2 Penanganan pembentukan koneksi dari client

Fungsi handle_one_connection akan dipanggil ketika thread baru diciptakan. Fungsi tersebut akan menangani segala hal terkait sebuah koneksi.

// file: sql\mysqld.cc // Create new thread to handle incoming connection static void create_new_thread(THD *thd) { … pthread_mutex_lock(&LOCK_thread_count); … if (cached_thread_count > wake_thread) { // Get thread from cache thread_cache.append(thd); wake_thread++; pthread_cond_signal(&COND_thread_cache); } else { // Create new thread to handle connection int error; thread_count++; thread_created++; threads.append(thd); if (thread_count - delayed_insert_threads > max_used_connections) max_used_connections= thread_count - delayed_insert_threads; DBUG_PRINT("info",(("creating thread %lu"), thd->thread_id)); thd->connect_utime= thd->start_utime= my_micro_time(); if ((error=pthread_create(&thd->real_id,&connection_attrib, handle_one_connection,(void*) thd))) { DBUG_PRINT("error", ("Can't create thread to handle request (error %d)",error)); … }

C-3 } (void) pthread_mutex_unlock(&LOCK_thread_count); } DBUG_PRINT("info",("Thread created")); … }

Kode C-3 Pembuatan thread untuk koneksi client

Fungsi handle_one_connection menangani handshaking dan otorisasi setiap client dengan memanggil fungsi check_connection. // file: sql\sql_parse.cc pthread_handler_t handle_one_connection(void *arg) { THD *thd= (THD*) arg; … do { if (error=check_connection(thd)) { // Wrong permissions … goto end_thread; } … while (!net->error && net->vio != 0 && !(thd->killed == THD::KILL_CONNECTION)) { if (do_command(thd)) break; } … end_thread: close_connection(thd, 0, 1); … } while (!(test_flags & TEST_NO_THREADS)); … }

Kode C-4 Penanganan koneksi client

Fungsi check_connection akan melakukan handshaking dengan mengirimkan Handshake Initialization Packet dan membaca respon dari client berupa Client Authentication Packet. Setelah melakukan parsing terhadap Client Authentication Packet, check_connection akan memanggil fungsi check_user untuk memeriksa user dan password yang diberikan. // file: sql\sql_parse.cc

C-4 // Perform handshake, authorize client and update thd ACL variables static int check_connection(THD *thd) { … NET *net= &thd->net; … DBUG_PRINT("info", ("New connection received on %s", vio_description(net->vio))); … { // buff[] needs to big enough to hold the server_version variable char buff[SERVER_VERSION_LENGTH + SCRAMBLE_LENGTH + 64]; // Set server capabilities ulong client_flags = (CLIENT_LONG_FLAG | CLIENT_CONNECT_WITH_DB | CLIENT_PROTOCOL_41 | CLIENT_SECURE_CONNECTION); if (opt_using_transactions) client_flags|=CLIENT_TRANSACTIONS; #ifdef HAVE_COMPRESS client_flags |= CLIENT_COMPRESS; #endif /* HAVE_COMPRESS */ #ifdef HAVE_OPENSSL if (ssl_acceptor_fd) client_flags |= CLIENT_SSL; // SSL is available #endif /* HAVE_OPENSSL */ … // Create new scramble for each handshake create_random_string(thd->scramble, SCRAMBLE_LENGTH, &thd->rand); … // Write server characteristics: up to 16 bytes allowed … int2store(end+3, thd->server_status); … // Write scramble tail end= strmake(end, thd->scramble + SCRAMBLE_LENGTH_323, SCRAMBLE_LENGTH - SCRAMBLE_LENGTH_323) + 1; // Write connection message and read reply if (net_write_command(net, (uchar) protocol_version, "", 0, buff, (uint) (end-buff)) || (pkt_len= my_net_read(net)) == packet_error || pkt_len < MIN_HANDSHAKE_SIZE) { … return(ER_HANDSHAKE_ERROR); } } … // Set client flags thd->client_capabilities=uint2korr(net->read_pos); if (thd->client_capabilities & CLIENT_PROTOCOL_41) { … // Set maximum packet size thd->max_client_packet_length= uint4korr(net->read_pos+4);

C-5 // Set character set thd_init_client_charset(thd, (uint) net->read_pos[8]); … } else { // Set maximum packet size thd->max_client_packet_length= uint3korr(net->read_pos+2); … } … #ifdef HAVE_OPENSSL … if (thd->client_capabilities & CLIENT_SSL) { // Do the SSL layering … if ((pkt_len= my_net_read(net)) == packet_error || pkt_len < NORMAL_HANDSHAKE_SIZE) { … return(ER_HANDSHAKE_ERROR); } } #endif … // Set autocommit mode if ((thd->client_capabilities & CLIENT_TRANSACTIONS) && opt_using_transactions) net->return_status= &thd->server_status; … char *user= end; char *passwd= strend(user)+1; char *db= passwd; … db= db_buff; … user= user_buff; … return check_user(thd, COM_CONNECT, passwd, passwd_len, db, TRUE); }

Kode C-5 Handshaking dan otorisasi client