asked on

Text / characters from database apperas as question mark in the HTML

Hi Experts,

I was installed MySql 5.0.19-nt on my local machine and imported data into it from sql scripts.
The data was successfully imported, and I can see the text correctly. Just to mention that the data was NOT in utf, but was normal ASCII.

The problem I'm having is that when I pull data from the database, I get all the text as question mark. All of my php files are NOT utf and on the header of the HTML page I set the character set of the page.

Also I can see that the default charset of the tables on the DB are utf-8, though the default char set variable is latin1.

How do I solve this problem?

Many thanks,
Doron

star_trek

If you are using apache look at the httpd.conf file on apache, comment out DefaultCharset variable and give it a try.

doront99

ASKER

no, I'm using PHP on Windows IIS. But I believe it comes from the MySql... no?

star_trek

The easier way to isolate the problem whether it is your database or webserver is
1) Query for the particular column using the command line, if the results are expected then it is not the databases.
2) Check the settings on the Webserver whether any DefaultCharsets.
3) If you are using any XSL(stylesheets) about any encodings that you are using, you might need to change ecoding on the page(you can try on one page, before changing in all of them).

doront99

ASKER

with the command line tool I still get question marks...

star_trek

then you are right the problem is with database, if you want to set the character-set to latin do the following

in my.cnf under [mysql] you can add
default-character-set=latin
and restart server

star_trek

the above should be latin1 not latin
also if you want utf-8
in my.cnf under [mysql] you can add
default-character-set=utf-8
and restart server

SOLUTION

Yuval_Shohat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

doront99

ASKER

Hi Yuval,

I have changed the default charset and still... not working...

here is the staus:

Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.19-nt
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: hebrew
Db characterset: hebrew
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 2 min 12 sec

Threads: 3 Questions: 314 Slow queries: 0 Opens: 0 Flush tables: 1 Open tab
les: 21 Queries per second avg: 2.379
--------------

star_trek

you can follow the link for more explanation
http://forums.mysql.com/read.php?103,43481,43745

Yuval_Shohat

take a look at the solution suggested by star_trek, and after that, if it doesnt work,
lets try to search for some clues somewhere else...
the problem with hebrew are ususally wrong encoding in the browser, which could be the result of one or more of several things.
first, take a look at your html output. take a look in a simple text editor. if the data from the DB is still question mark, try first one of the standart hebrew fonts (Ariel, Narkisim, David...). one of the resones for question marks instead of hebrew chars could be a non standart font.
check the data in the tables. try to export it to plain text and import it to a new table created by the following command:

CREATE TABLE t1 (c1 CHAR(10)) DEFAULT CHARACTER SET latin1 COLLATE hebrew_general_ci;

do the same import to a table created by:
CREATE TABLE t1 (c1 CHAR(10)) DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

then try to see what data comes out from each of these new tables...

tell us if it got you anywhere.

Yuval.

doront99

ASKER

No... still not working :(

Yuval_Shohat

ok, here we go.
after some thinking, what could go wrong, and some web searching...
take a look at your my.ini file (assuming you are running Windows),
first of all, there are two places where the set-default-character=hebrew should be set.
one is in the client section and the other is in the mysqld section (few rows down). set them both to hebrew.
back in the client section, add the following line:

character-sets-dir="c:/MySQL Server 5.0/share/charsets

dont forget to replace the "mysql server 5.0" with the directory where your mysql is installed.
in that directory there should be a directory called share, which in turen should contain another dirctory called charsets.
the character-sets-dir dirctive orders mysql to look for the hebrew encoding in that directory.
for some strange reason, it appears that mysql looks for it in c:/mysql/share/charsets or something like that...

restart the mysql server and check again the tables created in my previous comment.

hope this will do it.

Yuval.

Yuval_Shohat

the dirctive should be:
character-sets-dir="c:/MySQL Server 5.0/share/charsets"

notice the " in the end.

ps dont replace the / with \

ASKER CERTIFIED SOLUTION

star_trek

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

doront99

ASKER

Nothing is working... I even tried to go back with MySql version to 4.1 and even PHP 4.4.2...

Here are the results of the server variables:

+---------------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------------------------------+
| back_log | 50 |
| basedir | D:\Program Files\MySQL\MySQL Server 4.1\ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | hebrew |
| character_set_connection | hebrew |
| character_set_database | hebrew |
| character_set_results | hebrew |
| character_set_server | hebrew |
| character_set_system | utf8 |
| character_sets_dir | D:\Program Files\MySQL\MySQL Server 4.1\share\charsets/ |
| collation_connection | hebrew_general_ci |
| collation_database | hebrew_general_ci |
| collation_server | hebrew_general_ci |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | D:\Program Files\MySQL\MySQL Server 4.1\Data\ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 1800 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 2097152 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 49283072 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 25165824 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 26214400 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | D:\Program Files\MySQL\MySQL Server 4.1\share\english\ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| log | OFF |
| log_bin | OFF |
| log_error | .\DORON_DESKTOP.err |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 107374182400 |
| myisam_max_sort_file_size | 107374182400 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 35651584 |
| myisam_stats_method | nulls_unequal |
| named_pipe | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 622 |
| pid_file | D:\Program Files\MySQL\MySQL Server 4.1\Data\DORON_DESKTOP.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 61440 |
| read_only | OFF |
| read_rnd_buffer_size | 258048 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| shared_memory | OFF |
| shared_memory_base_name | MYSQL |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slave_transaction_retries | 0 |
| slow_launch_time | 2 |
| sort_buffer_size | 262136 |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | ON |
| storage_engine | InnoDB |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_replication | 0 |
| sync_replication_slave_id | 0 |
| sync_replication_timeout | 0 |
| system_time_zone | Jerusalem Standard Time |
| table_cache | 256 |
| table_type | InnoDB |
| thread_cache_size | 8 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 17825792 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.18-nt |
| version_comment | MySQL Community Edition (GPL) |
| version_compile_machine | ia32 |
| version_compile_os | Win32 |
| wait_timeout | 28800 |
+---------------------------------+----------------------------------------------------------------+

doront99

ASKER

OK, I'm sorry... continue to my last response, the older versions of MySql and PHP did solve the problem.
Maybe the problem is with MySql 5 or something... I don't know... but since it is working, that is all matter to me for now...

Many thanks for your help.
Cheers,
Doron

Yuval_Shohat

Shalom Doron ,
I know you have already closed the question... BUT... and its a big BUT.
I think im on to something. it took me a while... how can it be and what is wrong here if everything is exactly as it should be... i even compared your show variables output to mine, and it all looks fine.
I installed my own knowen and working mysql 5.0 to another computer with the default settings, and found out to my surprise that every data written in hebrew comes out as question marks.... sounds familiar to anyone???
i took my working my.ini, but still same same. that i have to say was a surprise since all hebrew letters are shown as question marks and i expected it to magically be readable hebrew...
so i started playing with it, thinking how can it be that everything workes the same but yet with differnt results.
To make a long story sort, I think I finaly found it. I dont know if its a bug (or a feature as some might say). The problem exists whenever the database was created with default character settings.
I have tried more then a few combination of server default character set latin1 hebrew, client default character set latin1 hebrew, and the result was almost the same...
if in the server part of the my.ini file the default character set = hebrew, then every NEWLY created database would be set automatically to hebrew, and everything should work just fine (notice, I wrote NEWLY created, existing databases gave question marks)
if the default character set in the server part was set to latin1, and the database was created with default, the results were again question marks... BUT if the database was created with the definition of hebrew
CREATE DATABASE `test8` /*!40100 DEFAULT CHARACTER SET hebrew */;
then it really didnt matter what default character set was set to, it just WORKED, hebrew was hebrew.
i went even further and checked if a table created in database with latin1 charset but with the hebrew as default charset:
CREATE TABLE `t1` (
`id` int(11) NOT NULL auto_increment,
`t` varchar(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=hebrew;

which should have worked, failed...

to sum it up, i think its a good idea to try and check,
create the database manually with the hebrew setting as default character set, then try to import the table(s) into the database.
It worked for me.

I dont know if all this info would help you, but hopfully someone in the future will have to spend only couple of minutes to figure this out...

Yuval.