[peterlaursen]

I am not in doubt that it is a problem with the file itself then!

the reaon why I asked if the server is running in ANSI-mode (or some other non-standard mode), is that could confuse the Cpanel backup-utility.

I think there will be the need for an extensive “search and replace” with that file from an editor like Ultraedit.

[peterlaursen]

OK then …

then someone will have to test with (at least some of) the data with MySQL in logging mode and maybe inspect the file as such in a hex-editor. My best idea right now.

Ritesh probaly have better tools and surroundings for this than I or anybody else.

What is the MySQL version?

[peterlaursen]

I believe that you used an editor tool (such as a word-processor) that you should not use for a pure text/sql-file?

70 MB is quite a bit but try opening the file in a PURE text-editor and save it. If notepad can not (I would not even try!) I believe the Textpad and Ultraedit can do that.

[peterlaursen]

read this:

http://webyog.com/whitepaper/Using_SJA_2006_07_18.pdf

this trick might help you:

Quote:

If you want to be 100% sure that you don’t lose newly created data with the sync and also want to ensure that all data is synced you can build a PK composed of any field(s) that you wouldnormally for a PK AND any other having different DEFAULT value on the two hosts. Itdoesn’t matter if that variable is a number-type and has defaults for instance “0” and “1”respectively or it’s a char-type having defaults for instance “webhost” and “localhost”. With this new PK “conflicting primary keys” will not occur during sync, since the same value for the PK would never exist on both hosts with data created since last sync. The host-specific part of such PK will always tell on which host the row was first created and it will work fine with SJA and INSERTs and UPDATEs (as long as there has only been an update on one of the hosts – but after all only updating data on one server between sync’s must be elementary discipline necessary in situations like these). But think over then too how to be able to DELETE rows – the problem you run into here is that deleting from one host won’t do if you after that run a twoway-sync! You will have to DELETE from both hosts between syncs, if not the deleted row shall be written back from the host where it was not deleted. It is just an example of the “dangers” of having two DB’s in principle containing the same data and letting them develop independently. It’s your job as SJA-user or DBA to think the consequences of different situations to an end and take appropriate action, since each server will not be able to reach the data located on the other host …

[peterlaursen]

or are there some non-pritable characters in file ?

did you open it in any sort of word-processor to do the editing ?

this I don't understand:

Quote:

First we had an issue with the keyword option, so I enclosed it as `option`.

[peterlaursen]

server modes are explained her

http://dev.mysql.com/doc/mysql/en/server-sql-mode.html

Could that be it?

If “MySQL Administrator” can connect it will show you the current settings.

[peterlaursen]

No problem here!

I can import your sql-statement form SQL-pane in SQLyog and from a file.

Does the server use ANSI ?

[peterlaursen]

Quote:

So I did a SQL backup of a Mambo database using Cpanel

…

I'm using SQLyog to import the data

If you don't use SQLyog for the exports/backup operation, you should not blame it for the content of the SQL-file! When importing a dump to MySQL SQLyog does not do anything else than executing the SQL-statements in the file with the MySQL-server.

Did I understand you right?

[peterlaursen]

I also tried a variation with your data: I deleted some rows so that there should be more 'add column' and 'drop column' statements boths ways.

Still there is only one drop statement

/* Alter table in Second database */

alter table `frogtest`.`fv_areas`,

add column `idbb_intro_area` int(11) NULL after `name_area`,

drop column `idreg_area`;

/* Alter table in Second database */

alter table `frogtest`.`fv_miscinfo`,

add column `idbb_desc_misc` int(11) NULL DEFAULT '0' after `id_misc`;

/* Alter table in Second database */

alter table `frogtest`.`fv_regions`,

add column `idbb_intro_reg` int(11) NULL after `name_reg`,

add column `idbb_info_reg` int(11) NULL after `idbb_intro_reg`;

/* Alter table in Second database */

alter table `frogtest`.`fv_villas`,

add column `idbb_info_vil` int(11) NULL after `sleeps_perm_vil`;

/* Alter table in First database */

alter table `cazantco_frogtest`.`fv_areas`,

add column `idreg_area` int(11) NULL DEFAULT '0' after `name_area`,

drop column `idbb_intro_area`;

/* Alter table in First database */

alter table `cazantco_frogtest`.`fv_miscinfo`,

add column `name_misc` varchar(25) NULL after `id_misc`;

/* Alter table in First database */

alter table `cazantco_frogtest`.`fv_regions`;

/* Alter table in First database */

alter table `cazantco_frogtest`.`fv_villas`,

add column `sleeps_total_vil` int(11) NULL DEFAULT '0' after `sleeps_perm_vil`;

[peterlaursen]

I just reproduced it with another example.

Seems that it will only DROP one column.

But it does not affect dropping of tables as this shows:

/* Alter table in Second database */

alter table `test2`.`corp_profile`,

drop column `Billing`;

/* Drop in Second database */

drop table `test2`.`indv_profile`;

/* Alter table in Second database */

alter table `test2`.`name`;

/* Alter table in Second database */

alter table `test2`.`name_address`;

(here there should have been 'drop column' statements in the last two ALTERs too)

hmmm … it must be a recent “mal-tailoring” of the code.

A 'flag' that is not initialized/reset or something …

It has worked for me dozens of times earlier!

[peterlaursen]

yes …

the “insert way” it is OK

/* Alter table in First database */

alter table `frogtest`.`fv_areas`,

add column `idbb_intro_area` int(11) NULL after `idreg_area`;

/* Alter table in First database */

alter table `frogtest`.`fv_miscinfo`,

add column `idbb_desc_misc` int(11) NULL DEFAULT '0' after `name_misc`;

/* Alter table in First database */

alter table `frogtest`.`fv_regions`,

add column `idbb_intro_reg` int(11) NULL after `name_reg`,

add column `idbb_info_reg` int(11) NULL after `idbb_intro_reg`;

/* Alter table in First database */

alter table `frogtest`.`fv_villas`,

add column `idbb_info_vil` int(11) NULL after `sleeps_total_vil`;

but not the “drop way”

/* Alter table in Second database */

alter table `cazantco_frogtest`.`fv_areas`,

drop column `idbb_intro_area`;

/* Alter table in Second database */

alter table `cazantco_frogtest`.`fv_miscinfo`;

/* Alter table in Second database */

alter table `cazantco_frogtest`.`fv_regions`;

/* Alter table in Second database */

alter table `cazantco_frogtest`.`fv_villas`;

It does not make any difference whether indexes are chosen or not.

What could it be that leeds the tool astray here .. ?

[peterlaursen]

you will only need to export the structure of both DB.

An empty DB is OK. No data is needed for this!

Just copy from the OBJECTS pane like

CREATE TABLE `whertogo` (

`Id` int(10) unsigned NOT NULL,

`goname` varchar(50) collate latin1_danish_ci default NULL,

`towncity` varchar(50) collate latin1_danish_ci default NULL,

`type` varchar(50) collate latin1_danish_ci default NULL,

PRIMARY KEY (`Id`),

KEY `citieswhertogo` (`towncity`),

KEY `townix` (`towncity`),

CONSTRAINT `whertogo_ibfk_1` FOREIGN KEY (`towncity`) REFERENCES `cities` (`town_city`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci

[peterlaursen]

It is ABSOLUTELY not without problems if it shall work with all versions of MySQL – both those that use COLLATION and those that do not.

Up to 4.0 it would be not problem to include a DEFAULT CHARSET= xxx.

but with 4.1 ++ it must be DEFAULT CHARSET=xxx COLLATE=xxx_yyy, but the COLLATE=xxx_yyy must be with each column definition.

From MySQL 4.1 there are so many “encodings” (your expression, not a MySQL expression) if you consider each COLLATION variation an “encoding”, that I believe it will be very confusing.

[peterlaursen]

It does not occur here and I have the same prefixing

like “domainusername_databasename”

and also use HTTP-tunnelling.

[peterlaursen]

Quote:

and each time I run the sync tool and apply it, the fields from one table only are dropped

You were here too, weren't you ?

http://www.webyog.com/forums/index.php?sho…c=1622&hl=arrow

here we agreed that you should recognize the difference between “execute query” and “execute all queries”. Queries are seperated with “;”. F5 only executes ONE.”execute all queries” is available as the double green arrow icon or from the menu.

.. but now you write that it is the SQL generated that is buggy.

Can you paste in the table definitions (no data needed)?

[Author]

Posts