CiviCRM Community Forums (archive)

(is it the right word to use instead of "Hey" at the beginning of the message?) :-)

It's not wrong.

We're getting offtopic here, so I won't press you anymore. I remember not being able to imagine switching to Linux - and it lasted only until I switched. I still have Windows on my workstation, but didn't boot with it for many, many months now.

OK, thanks for the encouragement. Truth is that the other thing is testing on IE all the time...

But you can connect remotely from your desktop - just give mysql command line host and port parameters and it should attach to remote server without a problem. Technically, it's almost the same way as your CiviCRM install is connecting to the database, so they shouldn't be blocking it.

However, as said before, there is a learning curve in the beginning which requires some time, so you might want to start learning it after you fix your db encoding problems.

I have used it before--I didn't know about remote connect. I think I must do this now. Thanks.

Because I realized in the meantime that the dump is the issue, not the insert. The dump is bad data. I have confirmed this. OK, I will try with the command line, probably Sunday.

Thanks.

Ok, I’m back from פריז (including the fascinating district of המארה).

After reading the above, I assume the problem might indeed be in connection character set specification. While this shouldn’t really affect the dump, it can affect the upload of the data; basically, if you have an UTF-8 dump, you should issue before importing (or add to the dump) a SET NAMES utf8 SQL command.

Do let me know if this helps of if you make any other progress with your case.

hershel:

no hassle. would be great if you could repay the favor and help answer a few posts on the forum over the next week or so. Also you could help with the hebrew translation for CiviCRM and get it to a better state. That would be a great contribution!

http://translations.civicrm.org/he_IL/

lobo

This means there’s something wrong; our template file for civicrm_currency is in UTF-8…

I misspoke. What I meant is that if I do an upgrade, then it indeed DOES ruin that table. If I use a fresh V1.8 install and then use my custom PHP scripts to import my V1.4 data, then everything works. Interestingly enough, here is my code for one table:

$dbh = mysql_connect('OLD SERVER') || die("Could not connect to MySQL Server");;
mysql_select_db('OLD_DB') || die("Could not connect to SQL database");
//mysql_query("SET NAMES 'utf8'") or die('SET NAMES failed');

$sql = "select * from  `civicrm_individual`";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
$rows[] = $row;
}

$dbh = mysql_connect('NEW SERVER') || die("Could not connect to MySQL Server");;
mysql_select_db('NEW DB') || die("Could not connect to SQL database");
mysql_query("SET NAMES 'utf8'") or die('SET NAMES failed');

foreach($rows as $row)
{
$row['first_name'] = addslashes ($row['first_name']);
$row['middle_name'] = addslashes ($row['middle_name']);
$row['last_name'] = addslashes ($row['last_name']);
$sql = "insert into `civicrm_individual` ( `id`,`contact_id`,`first_name`,`middle_name`,`last_name`,`prefix_id`,`suffix_id`,`custom_greeting`,`job_title`,`gender_id`,`birth_date`,`is_deceased`,`phone_to_household_id`,`email_to_household_id`,`mail_to_household_id` ) values ".
  "(    '{$row['id']}','{$row['contact_id']}','{$row['first_name']}','{$row['middle_name']}','{$row['last_name']}','{$row['prefix_id']}','{$row['suffix_id']}','{$row['custom_greeting']}','{$row['job_title']}','{$row['gender_id']}','{$row['birth_date']}','{$row['is_deceased']}','{$row['phone_to_household_id']}','{$row['email_to_household_id']}','{$row['mail_to_household_id']}'  );";

include "runsql.php";
}

This code works, but if I remove the comments from:

//mysql_query("SET NAMES 'utf8'") or die('SET NAMES failed');

then I get garbage. So I think there maybe something odd with my V1.4 DB. It is, BTW, the notoriously bad version of MySQL 4.0. I think 4.0.27 maybe.

Anyway, I have run my scripts, and all data is in and we are proceeding now with 1.8.

Based on this and the above code snippets, my take is that your CiviCRM 1.4 was indeed borked. 

LOL

Ah-ha! MySQL 4.0 does not have a proper UTF-8 support, period. For UTF-8, you really have to run MySQL 4.1 – so that was it.

Ah, this explains why it took me literally DAYS to get the data in there and the DAYS this time to get it out again.

I will avoid uploading any *really* deragatory comments toward this host which I am never using again.

Cool. Do let us know if you have any other issues with CiviCRM.

Well, there is one issue with some guy in Poland who is apparently prejudiced against the Hebrew language, but I think I can handle him myself.