[ken]

Never mind the “also”…I had combined a note that I posted on another thread.

Anyhow, I dare to do use SQLYog in this case for synchronization because I have an old version of an application and a new one, and I can do a structure sync then a database sync more easily than re-entering my configured pages, and contacts, etc. This is not a production database yet, but a proof of concept site, so I can be a little cavalier…I did take a backup before I restructured the source database.

Anyway, good point on the “cost” of getting this info. But isn't there a system table that keeps track of how many records are in each table? Anyway, PHPMyAdmin does it every time you list the tables, so how bad can it be?

My only justification for this request is that I have a case where I want to transfer data from one database to another, but not overwrite “support” tables, which might have been improved in the new version, i.e. maybe there are new zipcodes or countries or something…although I can see that I might have FK integrity problems if I don't bring the support tables over.

I remember a thread from a Joomla developer who was looking for a way to synch content from his local work machine to a web server. This is one of those cases where you might only want to sync tables where the target is empty. In this case, there were more entries in the “configuration” table of the new version of CiviCRM than in the old version. Hopefully, the additions were only made to the end, and not within the data.

Hopefully, we'll see over the next days, if this thread stays near the top of the board, what other users think.

Thanks for considering…

Best,

Ken

[ken]

peterlaursen wrote on Nov 14 2008, 08:13 AM:

We have released 7.13 RC.

Shift+click is now functional for selection of multiple group of objects. Interface is similar to Gmail (what also means the ctrl+click is not used – unlike Windows explorer for example)

Peter, I did a database and structure sync yesterday with 7.14, and Shift-Click did not work for me.

I just did this again…in database sync, I can't get Shift-Click to make multiple selects, no matter where I click.

Ken

[ken]

I have assembled the information that you request, and that I think you should need, including logins to the two LimeSurvey instances, and the JOB file.

I used Google to find the ticket system, and created a Ticket #6355.

Please let me know if you can confirm my findings.

I would actually prefer to have the job run from Linux, ultimately, but getting to execute correctly outside the GUI seems the first step.

Let me know, too, if you need more info.

Thanks,

Ken

[ken]

Nope, no floating point.

The proof case is very simple.

Either:

1) Setup two installs of LimeSurvey with Fantastico, create a survey on the first, activate it, generate some tokens, export and import those to the new install (OR just sync the entire tokens table over)

Now, take the survey a couple of times in the first instance using a couple of tokens from the tokens_### (example: tokens_360) table.

OR

2) Create this table structure into two separate databases, and add some records, with at least tid (the PK) and then sync those PKs to the other table. Now change the completed field from N to a timestamp in the first table/database

THEN

Set up a simple sync job on the `completed` field from the first database to the second database, the point being that when people complete the survey, the second database (and the users of that database) finds out within 24 hours or whatever the sync interval is. See that the job runs fine from the Wizard and opening it back from the job file interactively.

Go back through the Wizard and set the job up as scheduled. Try to execute it from the Job Scheduler using the Execute button. This is where it's not working for me. Job starts but does nothing useful, let alone what's desired (updating the new completed records).

I have attached the structure below.

Hope this suffices for your testing. If not, I can give you the connection information for my two instances, or whatever you need.

Best,

Ken

— phpMyAdmin SQL Dump

— version 2.11.9.2

— http://www.phpmyadmin.net

—

— Host: localhost

— Generation Time: Nov 12, 2008 at 11:47 PM

— Server version: 5.0.67

— PHP Version: 5.2.3

—

— LimeSurvey 1.72 Tokens Table

—

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

/*!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: `xyz_lime3`

—

—

---

—

— Table structure for table `lime_tokens_360`

—

CREATE TABLE IF NOT EXISTS `lime_tokens_360` (

`tid` int(11) NOT NULL auto_increment,

`firstname` varchar(40) collate utf8_unicode_ci default NULL,

`lastname` varchar(40) collate utf8_unicode_ci default NULL,

`email` text collate utf8_unicode_ci,

`emailstatus` text collate utf8_unicode_ci,

`token` varchar(36) collate utf8_unicode_ci default NULL,

`language` varchar(25) collate utf8_unicode_ci default NULL,

`sent` varchar(17) collate utf8_unicode_ci default 'N',

`completed` varchar(17) collate utf8_unicode_ci default 'N',

`attribute_1` varchar(100) collate utf8_unicode_ci default NULL,

`attribute_2` varchar(100) collate utf8_unicode_ci default NULL,

`mpid` int(11) default NULL,

PRIMARY KEY (`tid`),

KEY `lime_tokens_360_idx` (`token`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2001 ;

[ken]

I am sure it's not syncing, because I was looking at the data in PHPMyAdmin. I forgot I could look through SQLYog.

Even moreso this morning, the job was supposed to run at 0600, and apparently did not, because I have 3 records with timestamps in the field I am synchronizing in the source table, and only 2 in the target.

In the Control Panel, I right click Run on the job. It opens a DOS window, and it apparently connects to the database(s) because it shows the correct count, 2000 records in each table, and then 0 0 0 0 something like that for all the inserted/changed, etc. fields.

I had some spaces in the directory name where my job file was, so I moved a copy to another directory with no spaces, but the result is the same. I can see that the connections are evidently being made, but no action is being performed.

The Last Result for both copies is 0x1.

Thanks,

Ken

[ken]

Update: This actually works rather well, because the survey results are one record per response in the surveys_### table, and the record of who responed and the datestamp is in tokens_###, so depending on whether you want the client to be able to see the responses, just update those tables one-way.

Thanks,

Ken

[ken]

peterlaursen wrote on Nov 11 2008, 04:26 PM:

There is no direct option to do only UPDATES (and skip INSERTs and DELETEs).

Can that become a Feature Request?

peterlaursen wrote:

I will not comment on specific applications (like PHPsurveyor). In principle do not expect us to know any! If there is anything special with it you will have to communicate it here!

Right, Peter. I am keeping the questions to functionality, and only providing the context to the degree that I think it might be helpful to another user of the same system.

It looks now like I would only have to synchronize one table, and that there should be no adds or deletes to that table, so it might be easier than I thought.

I will report back…

Thanks,

Ken

[ken]

peterlaursen wrote on Apr 30 2008, 10:33 AM:

“Save Query Builder Layout” is something we have discussed only briefly. Not decided when we will do this. We can save as “Schema Designer Layout”, but a “Query Builder Layout”” poses more problems (basically storring GRID information). Alternatively we could provide and option to 'reverse engineer SQL query into Query Builder' .. but hat is an even bigger task. Bascially priorities depend on the demand!

Peter, Don't give up on this…as far as reverse engineer SQL (which could then be your own generated SQL, too), how about at least just populating the tables on the grid, and maybe the fields and criteria, and popping a window “SQL reverse engineering is a beta process, please check your query”.

If you can populate the qrid on a drag/drop, it shouldn't be that hard to populate it from reading a query, and the field names seem to be readily available, too.

peterlaursen wrote on Apr 30 2008, 10:33 AM:

The query itself can be stored, of course (as a file and a 'favorite', but I think you know).

In my 6.54 install, neither of these options is available while the Query Builder tab is selected, but only become available when the Query tab is selected. Is there some major reason that we can't just save or execute a query from the Builder screen?

This is similar to my post regarding making Yog more like Access in moving between SQL, Visual Design, and Result views. It's a great goal. I'd like to see it, because I hate the new Office 2007 interface, and the ODBC errors.

Thanks,

Ken

[ken]

peterlaursen wrote on Dec 17 2007, 11:41 AM:

* click+shift for range selection/deselection

click+shift still doesn't work for me in the table list under Create Schema for Database in HTML in 6.54

Thanks,

K

[ken]

peterlaursen wrote on May 7 2008, 04:53 AM:

1) The logic of the Structure Sync code will make it impossible for us not to consider column order. Also to prepare the databae for Data Sync the column order must be the same. This we won't change.

2) We could probably provide an option to ignore collation differences, but if you “don't care about it” why is it so important?. You should be aware that concatting, joining, using subqueires (and much more) data with different collations is not possible. For 'non-trivial queries' collations are important! I do not think we will do this either, but this we will consider again.

Thanks again. I'll be more clear.

1) Good. I don't actually have that issue, but someone else had grouped it with the collation issue, and it sounds related.

2) I am only trying to insert, so I don't think that collation is important to me, hence “don't care”. I am only trying to determine whether there is a corresponding field of identical name and identical or similar type. When I do the Structure Sync now, I get a lot of Alter Table or Alter Field, statements that I can't tell if they are only generating because of the collation difference.

Thanks,

Ken

[ken]

Mahesh wrote on Apr 8 2008, 12:34 AM:

We have fixed this issue in version 6.15, please upgrade to latest version.

I think that the original question was how to make the structure sync ignore collation and column order differences. I don't see where that has been answered, sorry.

I have Enterprise 6.52, and when I do a structure sync, I get a lot of “garbage” diffs that are really only collation diffs, which I don't care about.

Is there now a way to ignore diffs in collation and/or field order, and how?

Thanks,

Ken

[ken]

I have Enterprise 6.14. I have a requirement to import some Basecamp data, which I understand to be XML, but I don't see XML import or the ability to open an XML file as a datasource in my version. Is it still on the ToDo List?

Is there another GUI tool for this? If I get it into MySQL, then I can pump it anywhere with the Yog.

Thanks,

Ken

[ken]

Yeah, Peter, I understand…and you are giving a lot of software power for not very much money, so THANKS!

Thanks for the clarification that structures must be identical.

Regarding the Sugar upgrade, my host said “post_max_size is now 64 megs.”, which allowed the upgrade wizard to run.

The wizard did a massive restructure on the database, which I am glad to not have had to do with another tool.

Thanks,

Ken

[ken]

The server is MySQL – 5.0.48

I found that deleting the single MyISAM table was not the simple fix that had naively hoped that it would be.

I am going to have to check with my hosting company about doing the reinstall, I think…

Thanks, Peter, as I realize that my server problems are actually out of the scope of your software.

Buttons grayed out is fine, I agree. Maybe one little error message like : “Sync can not proceed. Server A/Server B did not provide XYZ information” would be nice.

I will report back…

Ken

[ken]

Right…I definitely don't expect you to provide specific knowledge of every LAMP application, for sure 🙂

peterlaursen wrote on Nov 22 2007, 05:07 PM:

My best intuition tells me the answer that with the 'Migration Tool' you will be able to generate a job file that migrates one database to another and changes mapping/column definitions accordingly!

OK, and the JOB file, I can distribute? Anyone who wanted to use it would need a copy of SQLYog, of course 🙂

If I were near my own computer, I would have just checked it out in the software…thanks for the answers.

Thanks,

Ken

[Author]

Posts