Sambungan MySQL dari Lucee: had, timeout dan utf8mb4

Sambungan MySQL dari Lucee: had, timeout dan utf8mb4

Masalah sambungan MySQL dari Lucee hampir selalu jatuh ke dalam empat kategori: kolam sambungan kehabisan tempat, sambungan mati selepas menganggur, charset tidak sepadan sehingga aksara atau emoji rosak, dan pelayan pangkalan data tidak dapat dicapai. Artikel ini menunjukkan setiap punca, mesej ralat sebenar yang ditangkap dalam rig, dan pembetulannya.

Kolam sambungan Lucee lawan had sambungan MySQL

Lucee tidak membuka sambungan baharu untuk setiap pertanyaan. Ia mengurus sambungan melalui Apache Commons Pool 2, dan setiap datasource mendapat kolamnya sendiri. Apabila pertanyaan tamat, sambungan dikembalikan ke kolam untuk diguna semula, bukannya ditutup.

Had di pihak Lucee dan had di pihak MySQL adalah dua perkara berbeza, dan ia mesti sejajar. connectionLimit pada datasource Lucee mengehadkan berapa banyak sambungan yang Lucee sanggup buka dan pegang untuk satu datasource. MySQL pula mengehadkan di pihaknya sendiri melalui max_connections secara global dan max_user_connections untuk setiap pengguna. Kalau kolam Lucee dibenarkan membuka lebih banyak sambungan daripada yang MySQL izinkan, anda akan mendapat ralat "Too many connections", walaupun nama datasource, hos dan kata laluan semuanya betul.

Peraturannya mudah: saiz kolam Lucee mesti lebih kecil atau sama dengan bilangan sambungan yang MySQL izinkan untuk pengguna itu.

Mendaftar datasource MySQL dengan this.datasources

Cara paling bersih untuk mendaftar datasource dalam kod, dan bukan melalui Lucee Admin, ialah struktur this.datasources di dalam Application.cfc. Bentuk lengkapnya untuk MySQL:

this.datasources["kedai"] = {
    type: "mysql",
    host: "127.0.0.1",
    database: "kedai",
    port: 3306,
    username: "app_kedai",
    password: "rahsia_aplikasi",
    connectionLimit: 20,
    connectionTimeout: 30,
    validationQuery: "SELECT 1",
    custom: { useUnicode: true, characterEncoding: "UTF-8" }
};

Blok ini ialah bentuk yang didokumenkan dalam panduan datasource Lucee. Kunci kolam iaitu connectionLimit, connectionTimeout dan validationQuery disahkan diterima oleh enjin Lucee 7.1 dalam rig, tetapi pemacu sebenar yang diuji ialah H2, bukan MySQL, jadi sambungan MySQL penuh ditandakan sebagai didokumenkan dan tidak diuji. Jika anda lebih suka rentetan JDBC mentah, ganti type dengan class dan connectionString, contohnya jdbc:mysql://127.0.0.1:3306/kedai?useUnicode=true&characterEncoding=UTF-8.

Tiga kunci kolam: connectionLimit, connectionTimeout dan validationQuery

Tiga kunci ini menentukan kesihatan kolam sambungan anda.

connectionLimit mengehadkan bilangan maksimum sambungan dalam kolam untuk datasource itu. Nilai lalainya ialah -1, iaitu tanpa had, jadi Lucee membuka sambungan seberapa banyak yang diperlukan. Dalam aplikasi web yang sibuk, nilai tanpa had ini boleh dengan cepat memakan semua sambungan yang MySQL izinkan. Tetapkan nilai munasabah seperti 20 atau 50, dan padankan dengan kapasiti MySQL anda.

connectionTimeout menetapkan berapa lama Lucee menunggu untuk mendapatkan sambungan daripada kolam sebelum menyerah dan melempar ralat. Apabila semua sambungan sedang digunakan dan tiada yang dikembalikan tepat pada masanya, permintaan gagal selepas tempoh ini.

validationQuery ialah pertanyaan ringan yang dijalankan kolam untuk mengesahkan sambungan masih hidup sebelum diserahkan kepada permintaan. Untuk MySQL, SELECT 1 ialah pilihan standard. Ia melindungi aplikasi daripada sambungan yang telah diputuskan semasa menganggur.

MAX_USER_CONNECTIONS dan max_connections di MySQL

Bahagian ini merujuk kepada MySQL, jadi ia didokumenkan dan tidak diuji dalam rig yang menggunakan H2.

MySQL mempunyai dua suis untuk menghadkan sambungan. max_connections ialah bilangan maksimum sambungan serentak untuk seluruh pelayan, dengan nilai lalai 151. max_user_connections ialah had untuk setiap pengguna, dengan nilai lalai 0 yang bermaksud tanpa had. Dua suis ini ialah tempat paling kerap ralat "Too many connections" bermula. Anda boleh menyemak nilainya dengan SHOW VARIABLES LIKE 'max_connections' dan SHOW VARIABLES LIKE 'max_user_connections', lalu mengehadkan satu pengguna aplikasi dengan SET GLOBAL max_user_connections = 20.

Apabila anda melihat "Too many connections", semak dahulu siapa yang memakan sambungan. Selalunya puncanya ialah kolam Lucee yang dibiarkan tanpa had pada connectionLimit, bukan nilai MySQL yang terlalu rendah. Naikkan max_connections hanya selepas anda mengesahkan aplikasi benar-benar memerlukan sebanyak itu, kerana setiap sambungan memakan memori pelayan.

wait_timeout dan sambungan mati semasa menganggur

Satu lagi punca kegagalan senyap ialah wait_timeout di MySQL. Ia ialah tempoh, dalam saat, yang MySQL tunggu pada sambungan tidak aktif sebelum menutupnya. Nilai lalainya ialah 28800 saat, iaitu lapan jam, dan ia juga didokumenkan, tidak diuji dalam rig H2.

Lucee membuka sambungan dan mengembalikannya ke kolam. Jika aplikasi tidak menerima trafik selama lapan jam, atau anda menetapkan wait_timeout jauh lebih kecil seperti 60 saat, MySQL menutup sambungan itu. Lucee masih menyimpan sambungan itu dalam kolamnya dan menyangka ia hidup. Permintaan seterusnya mengambil sambungan mati itu dan gagal, anda melihat ralat "Communications link failure" pada permintaan pertama selepas tempoh senyap.

Pembetulan standard ialah menetapkan wait_timeout lebih lama daripada masa maksimum sambungan duduk dalam kolam, dan menghidupkan validationQuery supaya kolam mengesahkan sambungan sebelum menggunakannya. Dengan itu sambungan mati dibuang dan diganti dengan sambungan baharu secara automatik, bukannya diserahkan kepada pertanyaan anda.

Mesej ralat sebenar: komunikasi terputus

Untuk bahagian ini rig benar-benar boleh menjalankan kod, jadi mesej di bawah ialah keluaran enjin yang ditangkap, bukan ditulis semula daripada ingatan. Saya mencipta datasource yang menunjuk ke port mati 127.0.0.1:59999 lalu menjalankan satu pertanyaan terhadapnya. Lucee membalas dengan kod HTTP 500 dan mesej ini:

Lucee 7.1.2.54-SNAPSHOT Error (java.io.IOException)
lucee.runtime.exp.PageRuntimeException:Connection is broken: "java.net.ConnectException: Connection refused (connect failed): 127.0.0.1:59999" [90067-232]

Dua perkara penting di sini. Pertama, "Connection refused" bermaksud tiada proses mendengar pada port itu: pangkalan data mati, port salah, atau hos tidak boleh dicapai. Kedua, kod [90067-232] ialah kod ralat pemacu H2 rig ini; dengan MySQL kod dan frasa berbeza, contohnya "Communications link failure", tetapi puncanya sama. Ini berbeza daripada "datasource not found" yang dibincangkan dalam artikel ralat datasource tidak ditemui: di sini nama betul, tetapi sambungan tidak boleh dibuat.

"Connection refused" juga berbeza daripada tamat masa. "Refused" berlaku serta-merta kerana tiada yang mendengar pada port itu. Tamat masa berlaku apabila hos wujud tetapi tidak menjawab dalam tempoh connectionTimeout, contohnya tembok api yang membuang paket secara senyap.

Pertanyaan yang benar-benar berjalan dalam rig

Sebagai bukti hujung yang sihat, pertanyaan berikut menggunakan datasource kedai dengan jadual produk dalam rig, dan menghadkan hasil dengan maxrows:

<cfquery name="qProduk" datasource="kedai" maxrows="3">
    SELECT id, nama, harga, stok
    FROM produk
    ORDER BY id
</cfquery>
<cfoutput>#qProduk.recordCount# baris dikembalikan.</cfoutput>

Pertanyaan ini memulangkan kod HTTP 200 dan 3 baris dikembalikan. dalam rig. Sentiasa gunakan cfqueryparam untuk nilai daripada pengguna, dan cftransaction untuk operasi yang mesti atomik.

utf8mb4: kenapa utf8 di MySQL bukan UTF-8 sebenar

Bahagian ini ialah fakta dokumentasi MySQL, bukan ujian rig, kerana ia mengenai pemacu dan pelayan MySQL.

Dalam MySQL, charset yang dinamakan utf8 sebenarnya ialah utf8mb3, subset UTF-8 yang hanya menyokong aksara sehingga tiga bait. Ia wujud sebelum emoji menjadi sebahagian Unicode, jadi ia tidak boleh menyimpan aksara empat bait. Emoji, banyak ideograf CJK yang jarang, dan simbol matematik tertentu semuanya memerlukan empat bait, lalu gagal disimpan atau dirosakkan secara senyap apabila lajur menggunakan utf8.

utf8mb4 ialah UTF-8 sebenar, menyokong sehingga empat bait bagi setiap aksara. Dokumentasi MySQL menyatakan charset yang disyorkan untuk semua aplikasi baharu ialah utf8mb4, manakala utf8mb3 telah ditandakan usang. Jadi apabila anda mencipta jadual untuk kandungan yang mungkin mengandungi emoji, seperti komen atau medan nama, gunakan utf8mb4, bukan utf8.

Menggandingkan charset Lucee dengan MySQL

Menukar jadual kepada utf8mb4 sahaja tidak mencukupi. Kedua-dua hujung sambungan mesti bercakap UTF-8, jika tidak aksara rosak di tengah jalan.

Di pihak Lucee, tetapkan charset pada sambungan JDBC melalui custom, iaitu custom: { useUnicode: true, characterEncoding: "UTF-8" }, atau tambah useUnicode=true&characterEncoding=UTF-8 pada connectionString. Di pihak MySQL, pastikan pangkalan data, jadual dan lajur menggunakan utf8mb4.

Mengesahkan charset hujung ke hujung

Cara paling jujur untuk mengesahkan charset berfungsi hujung ke hujung ialah bertanya kepada MySQL tentang charset sambungan semasa. Dua arahan ini didokumenkan dan dijalankan dalam klien MySQL, bukan melalui rig H2:

SELECT @@character_set_connection, @@character_set_client, @@character_set_results;
SHOW VARIABLES LIKE 'character_set%';

@@character_set_connection memberitahu charset yang digunakan untuk literal dalam pertanyaan semasa, dan nilai lalainya dalam MySQL 8.0 ialah utf8mb4. Jika output menunjukkan utf8 atau utf8mb3, sambungan masih menggunakan charset tiga bait, dan emoji akan rosak walaupun jadual sudah utf8mb4. Ujian muktamad ialah menulis satu emoji melalui Lucee, membacanya semula, dan memastikan ia kembali sama.

Jadual gejala, punca dan tindakan

Soalan Lazim

Apakah bezanya connectionLimit Lucee dengan max_connections MySQL?

connectionLimit mengehadkan sambungan dalam kolam untuk satu datasource di pihak Lucee, manakala max_connections mengehadkan sambungan untuk seluruh pelayan di pihak MySQL. Kedua-duanya mesti sejajar, jika tidak anda mendapat "Too many connections".

Kenapa emoji saya menjadi tanda soal atau hilang selepas disimpan?

Kemungkinan besar lajur MySQL anda menggunakan charset utf8, yang sebenarnya utf8mb3 tiga bait. Emoji ialah aksara empat bait, jadi ia tidak boleh disimpan. Tukar lajur dan jadual kepada utf8mb4, dan pastikan sambungan JDBC Lucee juga menetapkan characterEncoding kepada UTF-8.

Adakah saya perlu menetapkan validationQuery untuk MySQL?

Ia sangat disyorkan. Tanpa validationQuery, kolam Lucee mungkin menyerahkan sambungan yang sudah ditutup oleh wait_timeout MySQL, dan pertanyaan pertama selepas tempoh senyap gagal. Dengan SELECT 1, sambungan mati dibuang dan diganti secara automatik.

Apakah maksud "Connection refused" berbanding tamat masa sambungan?

"Connection refused" berlaku serta-merta kerana tiada proses mendengar pada hos dan port itu, biasanya pangkalan data mati atau port salah. Tamat masa berlaku apabila hos wujud tetapi tidak menjawab dalam tempoh connectionTimeout, contohnya tembok api yang membuang paket secara senyap.

Bagaimana saya tahu charset sambungan semasa ialah utf8mb4?

Jalankan SELECT @@character_set_connection atau SHOW VARIABLES LIKE 'character_set%' dalam klien MySQL. Jika output menunjukkan utf8mb4, sambungan itu menyokong emoji. Jika ia menunjukkan utf8 atau utf8mb3, sambungan masih terhad kepada tiga bait.

Sumber

• Datasources, docs.lucee.org
• Database Connection Management, docs.lucee.org
• Server System Variables, MySQL 8.0 Reference Manual
• The utf8 Character Set (Alias for utf8mb3), MySQL 8.4 Reference Manual
• Connection Character Sets and Collations, MySQL 8.0 Reference Manual