ג'יבריש במסד הנתונים

מקרה מעניין בנושא ג'יבריש שקרה אצלנו (SPD Hosting) בימים האחרונים (והתגובות אליו בפייסבוק) גרמו לי להבין שההבנה בכל נושא ה"יש לי ג'בריש באתר, למה???" לוקה בחסר.

וכמו תמיד, כאשר אנחנו לא מבינים משהו, אנחנו נוטים להאשים את חברת האחסון (בעיקר כי אנחנו לא תמיד יכולים לדעת מה תלוי בהם ומה לא).

אז בואו נתאר את הסיטואציה: העברנו את האתר שלנו משרת X ל-Y ואז מתרחשת אחת מ-2 תופעות:

1. כלל הכיתוב בעברית מופיע בג'יבריש (או לפחות המידע שנטען ממסד הנתונים)
2. הכיתוב באתר נראה תקין, אך כשמזינים מידע חדש (למשל, שומרים פוסט קיים מחדש) כלל העברית הופכת לג'יבריש.

למה זה קורה?

ראשית נגדיר מתי זה לא קורה: כלל המאמר מדבר על החיבור ל-MYSQL, כלומר – אני לא אסקור במאמר זה מצב שבו שמרנו את התווים במסמך (HTML) כ-UTF8 אך הגדרנו meta charset של hebrew (במקרה כזה, אנחנו נקבל ג'יבריש).

אז מתי זה כן קורה? מתי נקבל ג'יבריש?

הג'יבריש מתרחש כאשר התווים שאנחנו מאחסנים ב-MYSQL שונה מהקידוד בו אנחנו קוראים אותו.
מצב זה יכול להווצר מכיוון:

1. הגדרות ה-Connection ל-MYSQL (ערך פנימי בהגדרות חברת האחסון) שונות מההגדרה בחברת האחסון הנוכחית:
   לדוגמא: בעבר (לפני עידן ה-UTF8), היה נכון שחברות האחסון המאחסנות אתרים בעברית יגדירו את ה-mysql לדבר בקידוד בשם hebrew (הגיוני סה"F) – יחד עם זאת, רוב חברות האחסון שפשוט התקינות mysql בתצורה של "out of the box" (במילים אחרות, לא שינו דבר בקונפיגורציה הבסיסית איתה מגיע ה-mysql).
   דבר זה יצר שבחברת אחסון א' העברית אוכסנה במסד הנתונים בפורמט latin1, ובחברת אחסון ב' העברית אוכסנה בפורמט hebrew.
   מבחינת הלקוח- ב-2 המקרים הוא ראה את העברית בצורה תקינה, ההבדל הוא כאמור – בשלב המעבר.
2. הגדרת ברירת המחדל למסד הנתונים היא כתיבת קידוד מסויים, בעוד ה-connection נעשה בקידוד אחר:
   לרוב אין לנו שליטה על כיצד יפתח מסד הנתונים בחברת האחסון, הסיבה לכך היא מכיוון שמסד הנתונים לרוב נפתח באמצעות ממשק הניהול איתו אנחנו עובדים (כלומר, אנחנו לא שולחים ל-MYSQL שאילתת CREATE DATABASE לצורך יצירת המסד, אלה פשוט יוצרים אותו דרך ממשק הניהול שלנו).
   במהלך יצירת מסד הנתונים, אחד הנתונים שניתן להגדיר (לא מדובר בנתון חובה) הוא "מה הקידוד שבו יאוחסנו הנתונים החדשים" – מכיוון שלא מדובר בנתון חובה (מצד אחד), ומכיוון שישנה ברירת מחדל של ה-mysql (כלומר, הגדרה ברמת השרת), ייתכן והקידוד שהוגדר לא עולה בקנה אחד עם האפליקציה שלנו.
   עובדה זו גורמת לכך שכל טבלה שתכתב, במידה ולא ציינו במדוייק מה הקידוד שלה, גם היא תכתב בהגדרות ברירת המחדל של השרת.
   בנקודה זו חשוב לזכור: אומנם אין לנו גישה להשפיע על איך נוצר מסד הנתונים (כאמור, אוטומטציה של ממשק הניהול), אבל בהחלט יש לנו השפעה כיצד נוצרות הטבלאות – הרי אנחנו כמתכנתים בונים את מסד הנתונים שלנו כאוות נפשנו .. ובכך, ברוב המקרים:
   יחד עם זאת, לא כולנו מתכנתים, לא כולנו שולטים ב-SQL ורובנו לרוב פשוט מתקינים תוכנות קוד פתוח אשר מתקינות את מסד הנתונים באופן עצמאי (בהנחה שנתנו להם את פרטי הגישה למסד הנתונים שלנו).נתאר לרגע את המצב באופן גרפי:
   
   כלומר, במידה ולא צויין אחרת – קידוד מסד הנתונים (או השאלה "כיצד ישמרו נתונים חדשים אשר ישמרו למסד" מחלחלים כלפי מטה).

האם נתונים אלו אמורים להפריע לנו להביא לישראל אתר שאוכסן בגרמניה? (לדוגמא, או להפך)

לחלוטין לא, ההגדרות שכל חברת אחסון בחרה לבצע ב-Mysql לא צריכה להשפיע עליכם בשום אופן שהוא.
נכון, אנחנו מצפים לראות שרוב חברות האחסון יגדירו כיום UTF8 כברירת המחדל, אבל לא תמיד כך המקרה – וזה בכל אופן, לא משפיע עלינו.

אז מה עושים אם בכל זאת יש לי ג'יבריש?

במילים אחרות: חברת האחסון לא אשמה – יופי, מה עכשיו? – הסברנו למה זה קורה, עכשיו נסביר איך פותרים את זה.

לצורך ההסבר, נשתמש בדוגמא:

כבר הבנו שלפי הגדרת ברירת המחדל, התווים ישמרו במסד הנתונים בהתאם לקידוד בו הוגדר מסד הנתונים (או הטבלה).
נגיד (לדוגמא) שאנחנו מעבירים את האתר שלנו משרת שהגדיר את ה-MYSQL שלו לעבוד ב-UTF8 ולשרת בו מסד הנתונים עובד כ-LATIN1.

מה משמעות הגדרת השרת?

1. מסדי נתונים חדשים בשרת א' יווצרו בקידוד של utf8 ומסדי נתונים חדשים בשרת ב' יווצרו בקידוד של latin1 – הנ"ל לא רלוונטי למקרה שלנו, מכיוון שלא מדובר במסד נתונים חדש.
2. ה-connection אשר יתבצע (בכל בקשת mysql_connect או mysqli_connect) מהאפליקציה למסד הנתונים כפי שמוגדר בברירת המחדל (במילים אחרות, שרת א' ינסה לקרוא את הנתונים בקידוד של utf8 ושרת ב' ינסה לקרוא את אותם הנתונים בקידוד של latin1)
   משמעות הדבר היא שבמידה והנתונים נשמרו במקור ב-utf8, השרת השני שקורא את הנתונים (ב-Latin1) יראה ג'יבריש ולא את העברית כמצופה – מקרה זה הוא הרלוונטי במקרה שלנו.

פתרון א': שינוי הטקסט שנשמר בתוך מסד הנתונים

במקרה זה, פשוט נמיר את כלל המידע במסד הנתונים שלנו מ-utf8 ל-latin1.

איך? ובכן, זה מסוג הדברים שיותר כל להגיד מאשר לעשות (ולעיתים לא ראלי-  לדוגמא: במידה ומסד הנתונים שלנו שוקל 80 ג'יגה).

בכל זאת איך מבצעים?

1. יש לבצע dump למסד הנתונים – חשוב לוודא כי ה-dump מבוצע באמצעות ה-connection נכון (באמצעות default-character-set, אסביר בהמשך).
   לדוגמא:

   mysqldump -u <username> -p<password> <database_name> –default-charecter-set=latin1 > filename.sql

2. יש לתרגם את התווים בקובץ ה-dump שזה עתה הורדנו מ-utf8 ל-latin1, ניתן לבצע ב-3 אופנים:
   א. שירותים Online: (מסוכן מסוכן מסוכן) קחו בחשבון שאתם מוציאים את כל ה-database שלכם לצד שלישי, אם יש שם מידע חשוב.. אולי שווה לשקול שלא לבצע זאת.
   שירות לדוגמא: http://www.unicodetools.com/unicode/utf8-to-latin-converter.phpב. שימוש ברכיב iconv לצורך התרגום, לדוגמא:

   iconv -f "UTF-8" -t "latin1" <source file> -o <destenation file>

   שימו לב, iconv מצוי לרוב במערכות linux (אם כי לעיתים יש להתקינו במיוחד), הוא פחות מצוי במערכת windows (אבל קיים גם שם, במידה ותרצו – ניתן להוריד גרסת windows מהקישור הבא).

   ג. הצורה הפשוטה מבין השלושה: שינוי תצורת יצירת הטבלאות (כך שבתקווה, כאשר נזריק את המידע מחדש הוא יוזרק בקידוד הנכון)
   כיצד מבצעים?

   יש לפתוח את הקובץ, לחפש את המופעים של "CHARSET=utf8" ולשנות אותם ל-CHARSET=latin1.

   בקיצור – לא פשוט.

פתרון ב': המרת הקידוד שבו מבוצע החיבור ל-MYSQL

זהו הפתרון ההגיוני והשפוי ביותר.

הפתרון אומר בעצם "אין מה להלחם בהגדרות השרת, הן כפי שהן – ורוב לא ניתנות לשינוי – ולפיכך, בואו נקרא את הנתונים כפי שהם נכתבו במקור למסד הנתונים".

במילים אחרות: אנחנו יודעים לומר למה הנתונים נכתבו בקידוד בו הם נכתבו בשרת המקורי – אם נדע לשנות את צורת ה-connection (כלומר, את הקידוד שבו האפליקציה פונה למסד הנתונים, נוכל לפתור את בעיית בג'יבריש!)

איך מבצעים זאת?

נעים להכיר: פונקצית set names של mysql.

שימו בפונקציה זו אומר בעצם "אוקי, אני מבין שהקידוד המקורי המידע במסד הנתונים שונה מהקידוד בו השרת החדש מבצע connection, ולכן – אני אבקש במקרה הספציפי הזה לבצע connection עם קידוד אחר".

במילים אחרות, מכיוון שברור המקרים (בטח באחסון שיתופי) אין לנו אפשרות לשנות את הקידוד ברירת המחדל של השרת – נשנה את הקידוד של ה-connection שמבצעת האפליקציה למסד הנתונים.

לצורך כך אנו זקוקים למספר נתונים:

1. הקידוד בו נשמרו הנתונים בשרת הקודם
2. המקום שבו מבצעת האפליקציה את החיבור ל-mysql (כלומר, את מיקום פונקצית ה-mysql_connect או mysqli_connect)

לאחר מכן, פשוט נציב מיד לאחר ביצוע החיבור למסד הנתונים את הקריאה לפונקציה set names – בהמשך לדוגמא שלנו:

mysql_query("SET NAMES 'utf8'")

דוגמאות לשינוי הקידוד במערכות קוד פתוח סטנדרטיות:

חשוב להדגיש – מדובר בפתרון בעייתי מעט – ככלל אצבע, לא מומלץ לשנות את קוד המקור של מערכות שונות (מכיוון שברמת העקרון, הדבר עלול לגרום לבעיה בעדכונים עתידיים) – יחד עם זאת, השינוי אותו אנו מבצעים הוא מינורי יחסית ובעיקר, נדרש.

JOOMLA

כברירת מחדל, Joomla (בגרסאות המתקדמות שלה) מנסה לעבוד תמיד כ-UTF8, ה"עובדה" לכך היא שהמערכת מכילה הגדרת ברירת מחדל של "set names" – קרה והנתונים שלכם הוגדרו אחרת? ניתן לשנות זאת:

בקובץ libraries/joomla/database/driver/mysqli.php נחפש את הביטוי "public function setUTF()", ואת תוכנו נשנה מ-Utf8 ל-latin1

WORDPRESS

החיים ב-wordpress קצת כלים יותר, פשוט ניתן להזין לקובץ ה-wp-config.php שלכם את השורה הבאה:

define('DB_CHARSET', 'latin1');

DRUPAL

בקובץ includes/database.mysql.inc נשנה את השורות הבאות:

if (!empty($GLOBALS['db_collation'])) {
mysql_query('SET NAMES utf8 COLLATE ' . $GLOBALS['db_collation'], $connection);
}
else {
mysql_query('SET NAMES utf8', $connection);
}

שיראו באופן הבא:

if (!empty($GLOBALS['db_collation'])) {
mysql_query('SET NAMES latin1 COLLATE ' . $GLOBALS['db_collation'], $connection);
}
else {
mysql_query('SET NAMES latin1', $connection);
}

איך אפשר לדעת מה הקידוד שכל שרת הגדיר?

במידה ואין לנו את המידע מהספק הקודם, נצטרך לבצע אחד מה-2:

1. מכיוון שקידוד ברירת המחדל נקבע לפי קידוד הטבלאות שלנו – במידה ונכנס לאחסון הישן ונבחן מהו קידוד הטבלה, נוכל לדעת (סביר להניח) מה היה קידוד המידע.
2. לנחש: נשמע מוזר, אבל לעיתים נקבל מצב שבו אין לנו את המידע הנ"ל (חברת האחסון הקודמת לא רוצה לספק את המידע, ה-dump שלנו לא מכיל את ה-character set איתו נוצרה הטבלה ועוד).
   ולכן, הפתרון הוא בעצם ניחוש וטעיה.. כאשר ת'כלס, אין לרוב הרבה אופציות: utf8,latin1 או hebrew.

בהצלחה!

• About
• Latest Posts

מנהל מערכת

חלק מחברת SPD Hosting החל משנת 2006.
כחלק מהתפקידים שלי בחברה, אני אחראי על תוכניות ההכשרה של עובדים חדשים.
חי ונושם Hosting, קוד פתוח ו-Linux (ויש גם מילה טובה ל-Windows)

Latest posts by מנהל מערכת (see all)

• ריבוי אתרים בחשבון = סיכון אבטחה - יולי 16, 2017
• Let's encrypt – תעודות SSL, ובחינם! - ינואר 17, 2017
• PHPMailer Exploit - דצמבר 28, 2016