[anti]

This is what the 'table data' list looks like:

[attachment=759:list.jpg]

But as you can see the ȹ are correctly interpreted as 'returns' in the blob field:

[attachment=761:blob.jpg]

I am using SQLYog Enterprise (trial) v6.07.

[anti]

I know that they (e107) shouldn't be using the id of the rows, but that's the way they developed the system from very early on in the project's management, and so most of the scripts use the row id to reference the content. I don't think this could easily be changed to the system you describe above without breaking thousands of existing websites. They may attempt it in the future, but my main concern is just to be able to manage the situation as it stands now.

So can I assume that there is no way around the 'there must never be any duplicate id numbers' rule?

I can't disable that somehow to allow me to freely edit the id numbers SQLYog?

One idea I had was to export the problematic tables to Excel, change all the id numbers there, and import back again.

Would something like that work? Would Excel be likely to corrupt the data or utf-8 characterset?

Thanks for any further advice.

[anti]

> 1) first run a structure sync from test machine to production machine

> 2) and next run a datasync

> .. that also is what a lot of web developers are doing!

But how are they doing it? Surely they can't possibly just be choosing the tables they think may have changed, and blindly clicking 'execute'? Certainly for myself, I'd need to see which tables differ so I can select the correct ones to begin with, and then in the case of conflicts I'd need to be able to see why they were different (ie. compare the row data) and choose which table should overwrite the other (would it be possible to even decide which row(s) should be overwritten???).

> The point I see is that also for data sync you want to preview and accept every change?

Yes. To be of any real use to non-database experts, the sync tool would need to build a visual tree of the compared database objects, marking differences by using different colours or icons. It would also need to allow viewing of the data in the rows of changed tables, and provide options to either copy or not, and to change the direction of sync (ie. in the case of a conflict, to be able to choose which table gets overwritten).

Currently the sync tool does not work the way I expected it to, but as I said I know nothing about databases (neither do 80% of the people building sites with CMS packages). Maybe what I'm requesting is not possible due to the limitations of mySQL, but most people would not feel safe blindly choosing tables which they think needed syncing, and then just executing the command without a visual review of what was going to be done or any control over the process.

I hope you don't think I am criticising SQLYog too much. It is by far the best database software I have seen for mySQL, and I will be buying it anyway, even without the features I require in the datasync tool – it is faster, more convenient and safer than importing/exporting from two instances of phpmyadmin. And lets not even mention Navicat, the database destoyer.

Thanks for your input so far into this subject.

[anti]

Have you ever worked with a Content Management System like Joomla, e107, drupal, etc?

We are only talking about between 60 tables (e107) and 200 tables (Joomla).

Most website developers have a local version (using WAMP) for development.

And they have their live site which is being used all the time.

Suppose our web developer finds a new gallery plugin. He wouldn't install it on the live site in case it broke the site. Instead he'd install it on the local site, and test it thoroughly. Installing it means there are new files and new database tables created in the local installation. After testing for a few days the developer is happy that it works. He now has these options:

1/

He has not kept a track of which files and database changes the plugin made (and his hacks, and any other changes he made to settings, including uninstalling his old gallery), so he has to upload his entire local site (files and database) to the live site. However, this will overwrite all the new files and database content that his users have published whilst he was testing the plugin. So it is not possible to do this.

2/

He has to manually monitor all changes (files and database) that the plugin makes whilst he is testing it. Once testing is over, he has to manually, selectively upload ONLY the changes to his live site so he does not overwrite the files and database content that users have published whilst he was testing the plugin. Doing this manually without a comparison tool is extremely difficult and is error-prone.

3/

He closes the live site for the weekend, replacing his local site with the entire live site before he installs and tests the plugin. He can then upload the entire local site (files and database) and over-write the live site after testing because no changes have been made to the live site. However, he does a lot of development on the site and it is unfeasible to keep closing the live site every night/weekend – the users need access to it.

4/

He keeps a track of everything he does whilst testing, and repeats the installation, set-up and hacks exactly on the live site, effectively doubling the amount of work – everything he does to his local site, he then has to manually duplicate on the remote site.

All four of these options are a major pain, but this is actually how all these web developers are currently having to manage their CMS sites.

However, now that the web developer has found an ftp client which can compare the local and remote files, he can easily and automatically upload the new plugin files to the live site and be certain that he is not overwriting any of the files published by the users. At the same time he can automatically download all the files that the users published during that time, so that both local and remote sites now have exactly the same file system. Fantastic!

BUT can he find a tool to do the same with his databases? Is there a tool that will allow him to see the tables side by side and automatically highlight the differences between them? Can he simply mark the new 'plugin' tables for upload, and mark all the remote tables which have changed (forums, comments, articles, shoutbox, userblogs, user galleries, etc.) for download?

Apparently not!

These two seem close:

http://www.sqlmanager.net/products/mysql/d…creenshots/1893

http://www.dswsoft.com/sqlbalance.php

This seemed close, until I tried it: http://www.dbconvert.com/convert-foxpro-to-mysql-sync.php

And my suggestion is that if mySQLYog could help solve this comparison/syncronisation problem for all the thousands of people using CMS website packages, in the absence of any decent competition, you'd vastly increase your userbase, fame and income.

[anti]

Thanks for your reply, but unfortunately there is no info there that I didn't already know.

However, I apologise because my second post seems to have been confusing.

Here's a summary of the above posts:

I have a good ftp client which can compare and sync my local and remote files.

I am now looking for a tool with similar functionality to syncronise my databases.

Although I've only tried it for a few hours, I think SQLYog cannot do what I need.

The data syncronisation works, but it lacks a comparison feature. Website developers

need to know which local and remote tables have been changed and what those changes

are BEFORE choosing the tables for sync and in which direction they should syncronised.

It would be great if the SQLYog data sync tool looked like the structure comparison tool.

And allowed the user to see the table differences and choose which ones to sync and which direction.

There are not many programs which can do this comparison/syncronisation job. If you extended the

capability of the SQLYog data sync tool, there are only about another three programs which can do it.

[anti]

Ah… I just found the 'structure comparison' tool. This is sooo close to what I need!

If only it could compare the table data as well as the structure, and display which tables were different

(even perhaps letting me see the contents of the tables??!), and then let me choose which version

(remote or local) to keep and which to overwrite during two-way sync.

As it is, the structure comparison tool does not flag tables which have different data.

But this is what thousands of web developers need! If mySQLYog could do this for 50$, it would probably

make you a millionaire within a year because every single web developer on the planet would want this tool.

Please, does anyone have advice about what I am trying to achieve?

BTW, the ftp client I use which does remote/local file comparisons is called TurboFTP.

As far as I know, it is the only one which has such a comparison tool.

Lots compare single directories, but this can compare and sync an entire website structure.

[Author]

Posts