Please help me get my shop running again

[sofiaengvall]

My web hotel stopped supporting the php version my shop was using so I tried moving it to my own windows server. The page is up and running but there is something seriouslly wrong with the encoding. This also messes up the connection to the database so ordering won't work and I can't look at old orders in the admin interface.

 

Please help!

 

It looks like this: http://shop.babythings.se

 

Thanks a million!

 

/Sofia

 

[MrPhil]

If your shop has been running since (at least) May 2008, I would guess it's probably still at osC 2.2 MS2. That is a very very old version that is incompatible with modern PHP versions (thus it stopped working on your old host). It's also very insecure and vulnerable to hackers. Finally, if you're running it on your "own" server (not a professionally managed server), the hackers will take you apart, regardless of what version of osC you're running.

 

The page is displayed as Latin-1, but the encoding problems show that some of your text is UTF-8. Somehow you got things mixed up in your files and your database. That will require manual repairs (editing database entries).

 

You have no choice but to install osC 2.3.4 (or better yet, osC 2.3.4BS responsive community version, from GitHub) and migrate your data over. It's simply not going to be worth it to try to patch up such an old version of osC. You will be up to date on features and security, and compatible with current PHP versions (up through 5.6).

[sofiaengvall]

I just need to get the shop running for a short while as I'm creating another shop. I'm also not at all worried about security issues. If I'm hacked I'll just fix it. (I've worked as it specialist since -96) The problem is that the shop have character set mismatch issues. That's what I need help with. Please help me with that!

 

I had a web hotel but had to move the shop since they stopped supporting the ancient php versions. But when I got it up on my windows 2k server (good virtual machine in server room) I had there character issues. It's not estetic issues. It messes up the sql so the shop won't work at all. :(

 

Since I'm at home with my 8-year-old son that got autism right now I don't really have the time to put hours and hours on this. I have tried changing the char set of the database though and a few other things I don't remember.

 

I have made a lot of changes to the code of the shop, mostlly just making the add-ins work together.. but it's not like it'd be a wakl in the park to upgrade... I think at least.. And since I'm still installing a separate prestashop...

 

I'm thinking about buying the add-in for prestashop to convert all data but then I need a running osc shop.

 

Please help! <3

 

/Sofia

[sofiaengvall]

I have the backup files that I used creating the database. Do you think the problem could be solved be playing with the encoding of the file and then scrapping the "old" database and making a new from the bu? I've been thinking the problem lies in the way the shop handles the characters... Since it doesn't seem to get very far when the "wrong" characters are concerned.

 

I could try running the shop on apache instead of iis... guess that would be more "unix-like"... or even do a linux-virtual mashine, but I'm totally lost at linux lol, though I can google and read it'd take time.......

[MrPhil]

Just a thought... when you set up your new hosting, and imported the database, did you properly declare the encoding of the backup file to phpMyAdmin (or other MySQL import)? The default for an .sql import is Latin-1, so in the UTF-8 backup file, accented (multibyte in UTF-8) characters may have been treated as a sequence of Latin-1 characters, and converted into UTF-8 characters in the database. I've seen that happen before.

 

I hope you don't delay in getting your new shop up (of course, we all hope it's osC 2.3.4 or 2.3.4BS). Not worrying about security can be very very expensive to you if a hacker gets in and compromises customer data (especially financial data). If it doesn't turn out to be a simple brain-check like failing to declare the .sql file encoding, it may be faster to migrate your database to osC 2.3.4BS and start with a fresh installation there.

[sofiaengvall]

I have worked with sql a bit professionally but never with mysql and never moving a database from an unix server to a windows server. I have backed the database up using phpMyAdmin (at the web server hosting) and I have installed MySQL on my windows server using rdp. Then I probably just run the database backup file starting (21k lines):

-- phpMyAdmin SQL Dump
-- version 3.5.8.1
-- http://www.phpmyadmin.net
--
-- Host: 10.246.17.20:3306
-- Generation Time: Apr 15, 2015 at 03:19 PM
-- Server version: 5.5.42-MariaDB-1~wheezy
-- PHP Version: 5.3.3-7+squeeze15

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `babythings_se`
--
CREATE DATABASE `babythings_se` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `babythings_se`;

-- --------------------------------------------------------

--
-- Table structure for table `address_book`
--

CREATE TABLE IF NOT EXISTS `address_book` (
  `address_book_id` int(11) NOT NULL AUTO_INCREMENT,
  `customers_id` int(11) NOT NULL,
  `entry_gender` char(1) NOT NULL,
  `entry_company` varchar(32) DEFAULT NULL,
  `entry_firstname` varchar(32) NOT NULL,
  `entry_lastname` varchar(32) NOT NULL,
  `entry_street_address` varchar(64) NOT NULL,
  `entry_suburb` varchar(32) DEFAULT NULL,
  `entry_postcode` varchar(10) NOT NULL,
  `entry_city` varchar(32) NOT NULL,
  `entry_state` varchar(32) DEFAULT NULL,
  `entry_country_id` int(11) NOT NULL DEFAULT '0',
  `entry_zone_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`address_book_id`),
  KEY `idx_address_book_customers_id` (`customers_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=941 ;

--
-- Dumping data for table `address_book`
--

INSERT INTO `address_book` (`address_book_id`, `customers_id`, `entry_gender`, `entry_company`, `entry_firstname`, `entry_lastname`, `entry_street_address`, `entry_suburb`, `entry_postcode`, `entry_city`, `entry_state`, `entry_country_id`, `entry_zone_id`) VALUES

How would I do it in a better way? I still havn't had much time to pack orders until now so it really has been quite nice that the shop went down... But now I really need it up again..

 

I'm running mysql-installer-web-community-5.6.24.0 om a Windows Server 2008 R2 Datacenter virtual mashine if that matters.

 

Also, the data looks fine in the database:

 

http://www.babythings.se/other/mysql/babythings_mysql_1.png

 

As I told you the problem seems to be the osc shop, not the database data. I have tried changing the type to the data in mysql and I seem to remember there being a change in how the errors appeared but I'm not certain.

 

http://www.babythings.se/other/mysql/babythings_mysql_2.png

 

 

Thanks a million! :)

 

/Sofia

[MrPhil]

My point was that phpMyAdmin (and probably any other .sql file import utility) needs to know what character encoding the .sql file was written in, so that it does the correct encoding conversion (if necessary) upon import. The default (for phpMyAdmin, anyway) is to assume the .sql file is Latin-1. If the file is actually UTF-8 (multibyte accented characters), the individual bytes of a single character get imported as multiple characters, and converted into multiple UTF-8 characters. This may have been what happened to your site, which is why I asked you to consider this point. It has nothing to do with the osC code, nor the .sql backup, but with the process of importing the backup. Once you have absolutely ruled out that cause, we would have to look for another cause.

[sofiaengvall]

How would I do that then? If the fact that the data seems ok doesn't say anything.

 

Thanks a milion for helping! :) <3

 

/Sofia

[MrPhil]

How did you import the data into the database? Did you have a backup .sql file and use phpMyAdmin to import it, or something else? If you use phpMyAdmin, there's a field somewhere in the IMPORT function that you specify what the encoding is for your .sql file (UTF-8, Latin-1, etc.). If you left it at the default Latin-1, it would have corrupted the database restore process (multibyte UTF-8 characters are treated as multiple Latin-1 characters, and expanded into multiple UTF-8 characters, each multiple bytes itself). You could try backing up your current database, and restoring the original backup being careful to tell MySQL in some manner what the backup's character encoding is (presumably UTF-8).

 

If that proves that bad import process was the culprit, you then have to figure out how you want to fix your database. You could compare the two backups, and manually separate out the new records added since the restore (as well as updates) and properly import (and replace) those records from the newer restore. Or, you could just see (visual inspection) which records are corrupted (accented characters expanded into multiple characters) and manually edit the database via phpMyAdmin or use an editor on the new backup and re-import it. Any way you do it, it will be some labor in finding what's corrupted and then fixing it. If there has been no database activity since you restored the database (incorrectly), you can simply correctly import the old backup. If there have been any changes to the database since that restore, you will have to be careful not only to restore the old data, but also to make sure you don't lose the new products, orders, etc.

 

Of course, the first step is to confirm that (all or some of) your accented characters were corrupted by an improper restore process (expanded into multiple UTF-8 characters). If that's not the problem, we'll have to look elsewhere. However, from your initial description of the symptoms, I suspect that's likely to be the problem.

 

One last thought: this assumes your text (character) data is consistently all Latin-1 or all UTF-8 in all the fields. If you botched a Latin-1 to UTF-8 conversion at some earlier point, and your fields (and data) are a mixture of two or more encodings, then nothing will save you and you have to manually edit all the "wrong" fields to correct the data.