News:

Support for jDownloads 3 has been ended
Since 17 August 2023 Joomla.org has discontinued support for Joomla 3.x. Therefore, we will no longer offer official support for our Joomla 3 jDownloads version 3.9.x from January 2024.
Please update your website to the latest Joomla version (Joomla 4 or Joomla 5) as soon as possible. Afterwards, please update jDownloads to the latest published version. The longer you delay, the more difficult the upgrade process for your website is likely to be.

Main Menu
Support-Forum

Migration / Install Error - SQL -> Unknown column - [Solved]

Started by Nyx, 18.11.2014 10:25:37

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Nyx

hi,

Thank you for your testing and for the solution of "... lediglich ein kleiner bug..." a small bug with a big effect.  ;)
I just tested it and there were no installation errors :) -> thank you!

all work fine, but the new File IDs is a big problem for:

x) the JDownloads Rating
x) the Component "Jcomments"
x) the Component "Custom Properties"

all work with the JD File IDs
So the JD Rating & jComments & Custom Properties all wrong by my migration.


So currently I have two options:
I change with phpMyAdmin

1)
all new File IDs to the old IDs -> This are 424 IDs in the moment.

or 2)
all old File IDs in the _jdownloads_rating to the new IDs  -> This are 295 IDs in the moment.
all old File IDs in the _jcomments to the new IDs  -> This are 49 IDs in the moment.
all old File IDs in the _custom_properties to the new IDs  -> This are 424 IDs in the moment.


... a lot of work and a long downtime of my website in the migration time. I have think about it.


Ps: Arno: "Hm... maybe should we correct this in a later version."
when do you think you could solve this problem?



  •  

Arno

Hi,
Quote
all work fine, but the new File IDs is a big problem for:

x) the JDownloads Rating
x) the Component "Jcomments"
x) the Component "Custom Properties"
maybe would it be possible to expand the import function by a special routine which corrected the ratings.
For the JComments i must check at first how it is handled.
What mean you with x) the Component "Custom Properties?

Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

Nyx

hm the import function is a good idea.

The  JComments table "__jcomments"

Spalte
-------
id
parent
thread_id
path
level
[b]object_id[/b]
object_group
object_params
lang
userid
name
username
email
homepage
title
comment
ip
date
isgood
ispoor
published
deleted
subscribe
source
source_id
checked_out
checked_out_time
editor


The column "object_id" is the id from JD column file_id.

e.g.:

id parent thread_id path level [b]object_id[/b] object_group object_params lang userid name username email homepage title comment ip date isgood ispoor published deleted subscribe source source_id checked_out checked_out_time editor

1 0 0 0 0 [b]307[/b] com_jdownloads en-GB 0 test test erter@sds.com ertert ::1 2014-12-09 12:35:00.000000 0 0 1 0 0 0 0 0000-00-00 00:00:00.000000



and for the Component "Custom Properties":

this is a Joomla Tag System, with a Plugin for JD Downloads.
http://solidsystem.it/joomla-extensions/custom-properties.html

I wrote it in:
http://www.jdownloads.com/forum/index.php?topic=7271.0
about it.


this work the same way:

Spalte
------
id
ref_table
[b]content_id[/b]
field_id
value_id



e.g:


id ref_table [b]content_id[/b] field_id value_id
4 jdownloads_files [b]12[/b] 4 9


In this case the column "content_id" is the id from JD column file_id.
  •  

Arno

Thanks for the examples.
I will seen what i can do in this week.
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

Nyx

Thank you for your tests.

I have try the "russian way"

.... Status: JD 3.x is installed with out error.
Only the files ids are wrong /new.

I did the following:

1) Edit the JD 1.9.2.11 JD/TXT Backup file with the JD 1.8.4 data:
- Del all data except for:

e.g.:

<script type="text/javascript">
    /* for modal output */
    function openWindow (url) {
        fenster = window.open(url, "_blank", "width=550, height=480, STATUS=YES, DIRECTORIES=NO, MENUBAR=NO, SCROLLBARS=YES, RESIZABLE=NO");
        fenster.focus();
    }
</script>

<?php
$database
->setQuery("TRUNCATE TABLE `kto6d_jdownloads_files`") ;$database->query();

$database->setQuery("INSERT INTO kto6d_jdownloads_files ( `file_id`,`file_title`,`file_alias`,`description`,`description_long`,`file_pic`,`thumbnail`,`thumbnail2`,`thumbnail3`,`price`,`release`,`language`,`system`,`license`,`url_license`,`license_agree`,`update_active`,`cat_id`,`metakey`,`metadesc`,`size`,`date_added`,`file_date`,`publish_from`,`publish_to`,`use_timeframe`,`url_download`,`extern_file`,`extern_site`,`mirror_1`,`mirror_2`,`extern_site_mirror_1`,`extern_site_mirror_2`,`url_home`,`author`,`url_author`,`created_by`,`created_mail`,`modified_by`,`modified_date`,`submitted_by`,`set_aup_points`,`downloads`,`custom_field_1`,`custom_field_2`,`custom_field_3`,`custom_field_4`,`custom_field_5`,`custom_field_6`,`custom_field_7`,`custom_field_8`,`custom_field_9`,`custom_field_10`,`custom_field_11`,`custom_field_12`,`custom_field_13`,`custom_field_14`,`ordering`,`published`,`checked_out`,`checked_out_time` ) VALUES ( '7','xxxx','xxx','TEXT.','','icon.png','img.png','','','','','1','1','8','','0','0','2','','','465.03 KB','2007-12-26 09:47:37','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','0','file.pdf','','0','http://www.url.com','0','0','','autorname','','System','','uploader','2011-11-07 15:32:11','0','0','4261','0','0','0','0','0','115','','','','','0000-00-00','0000-00-00','','','422','1','0','0000-00-00 00:00:00' );"); $database->query();$i++; 

..... 
etc. .....

?>




RENAME alle "$database" in "$db"
so the new code is:


<script type="text/javascript">
    /* for modal output */
    function openWindow (url) {
        fenster = window.open(url, "_blank", "width=550, height=480, STATUS=YES, DIRECTORIES=NO, MENUBAR=NO, SCROLLBARS=YES, RESIZABLE=NO");
        fenster.focus();
    }
</script>

<?php
$db
->setQuery("TRUNCATE TABLE `kto6d_jdownloads_files`") ;$db->query();

$db->setQuery("INSERT INTO kto6d_jdownloads_files ( `file_id`,`file_title`,`file_alias`,`description`,`description_long`,`file_pic`,`thumbnail`,`thumbnail2`,`thumbnail3`,`price`,`release`,`language`,`system`,`license`,`url_license`,`license_agree`,`update_active`,`cat_id`,`metakey`,`metadesc`,`size`,`date_added`,`file_date`,`publish_from`,`publish_to`,`use_timeframe`,`url_download`,`extern_file`,`extern_site`,`mirror_1`,`mirror_2`,`extern_site_mirror_1`,`extern_site_mirror_2`,`url_home`,`author`,`url_author`,`created_by`,`created_mail`,`modified_by`,`modified_date`,`submitted_by`,`set_aup_points`,`downloads`,`custom_field_1`,`custom_field_2`,`custom_field_3`,`custom_field_4`,`custom_field_5`,`custom_field_6`,`custom_field_7`,`custom_field_8`,`custom_field_9`,`custom_field_10`,`custom_field_11`,`custom_field_12`,`custom_field_13`,`custom_field_14`,`ordering`,`published`,`checked_out`,`checked_out_time` ) VALUES ( '7','xxxx','xxx','TEXT.','','icon.png','img.png','','','','','1','1','8','','0','0','2','','','465.03 KB','2007-12-26 09:47:37','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','0','file.pdf','','0','http://www.url.com','0','0','','autorname','','System','','uploader','2011-11-07 15:32:11','0','0','4261','0','0','0','0','0','115','','','','','0000-00-00','0000-00-00','','','422','1','0','0000-00-00 00:00:00' );"); $db->query();$i++; 

..... 
etc. .....

?>




So I  clean the table "TRUNCATE TABLE `kto6d_jdownloads_files" and import all "INSERT INTO kto6d_jdownloads_files" data sets / downloads.
I make a Restore with the JD 3.x Restore function and this TXT file.

-> all ok

I had to give all files/downloads in JD Joomla Webinterface a new "Access" -> "Public" and give all files "Language" -> "All".

So all ratings now right. / all file id's the "old one" -> and no errors or false data.

I have forgotten something? And "should" go this way?

  •  

Arno

Hm... i understand not exactly what you will doing here:

QuoteSo I  clean the table "TRUNCATE TABLE `kto6d_jdownloads_files" and import all "INSERT INTO kto6d_jdownloads_files" data sets / downloads.
I make a Restore with the JD 3.x Restore function and this TXT file.
You can not restore a backup file from 1.8.x in 3.2.x. Some database fields has changed in 3.2.

So you should use the 'normal import way' which we had debated early (and which worked fine). Only in this case is your database structure valid.
I will try in the next days to rework the import so that also ratings, comments and so on are corrected. But i need a little more time for this.
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

Nyx

I only save this to a txt:


<script type="text/javascript">
   /* for modal output */
   function openWindow (url) {
       fenster = window.open(url, "_blank", "width=550, height=480, STATUS=YES, DIRECTORIES=NO, MENUBAR=NO, SCROLLBARS=YES, RESIZABLE=NO");
       fenster.focus();
   }
</script>

<?php
$db
->setQuery("TRUNCATE TABLE `kto6d_jdownloads_files`") ;$db->query();

$db->setQuery("INSERT INTO kto6d_jdownloads_files ( `file_id`,`file_title`,`file_alias`,`description`,`description_long`,`file_pic`,`thumbnail`,`thumbnail2`,`thumbnail3`,`price`,`release`,`language`,`system`,`license`,`url_license`,`license_agree`,`update_active`,`cat_id`,`metakey`,`metadesc`,`size`,`date_added`,`file_date`,`publish_from`,`publish_to`,`use_timeframe`,`url_download`,`extern_file`,`extern_site`,`mirror_1`,`mirror_2`,`extern_site_mirror_1`,`extern_site_mirror_2`,`url_home`,`author`,`url_author`,`created_by`,`created_mail`,`modified_by`,`modified_date`,`submitted_by`,`set_aup_points`,`downloads`,`custom_field_1`,`custom_field_2`,`custom_field_3`,`custom_field_4`,`custom_field_5`,`custom_field_6`,`custom_field_7`,`custom_field_8`,`custom_field_9`,`custom_field_10`,`custom_field_11`,`custom_field_12`,`custom_field_13`,`custom_field_14`,`ordering`,`published`,`checked_out`,`checked_out_time` ) VALUES ( '7','xxxx','xxx','TEXT.','','icon.png','img.png','','','','','1','1','8','','0','0','2','','','465.03 KB','2007-12-26 09:47:37','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','0','file.pdf','','0','http://www.url.com','0','0','','autorname','','System','','uploader','2011-11-07 15:32:11','0','0','4261','0','0','0','0','0','115','','','','','0000-00-00','0000-00-00','','','422','1','0','0000-00-00 00:00:00' );"); $db->query();$i++; 

$db->setQuery("INSERT INTO kto6d_jdownloads_files ( `file_id`,`file_title`,`file_alias`,`description`,`description_long`,`file_pic`,`thumbnail`,`thumbnail2`,`thumbnail3`,`price`,`release`,`language`,`system`,`license`,`url_license`,`license_agree`,`update_active`,`cat_id`,`metakey`,`metadesc`,`size`,`date_added`,`file_date`,`publish_from`,`publish_to`,`use_timeframe`,`url_download`,`extern_file`,`extern_site`,`mirror_1`,`mirror_2`,`extern_site_mirror_1`,`extern_site_mirror_2`,`url_home`,`author`,`url_author`,`created_by`,`created_mail`,`modified_by`,`modified_date`,`submitted_by`,`set_aup_points`,`downloads`,`custom_field_1`,`custom_field_2`,`custom_field_3`,`custom_field_4`,`custom_field_5`,`custom_field_6`,`custom_field_7`,`custom_field_8`,`custom_field_9`,`custom_field_10`,`custom_field_11`,`custom_field_12`,`custom_field_13`,`custom_field_14`,`ordering`,`published`,`checked_out`,`checked_out_time` ) VALUES ( '7','xxxx','xxx','TEXT.','','icon.png','img.png','','','','','1','1','8','','0','0','2','','','465.03 KB','2007-12-26 09:47:37','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','0','file.pdf','','0','http://www.url.com','0','0','','autorname','','System','','uploader','2011-11-07 15:32:11','0','0','4261','0','0','0','0','0','115','','','','','0000-00-00','0000-00-00','','','422','1','0','0000-00-00 00:00:00' );"); $db->query();$i++; 

$db->setQuery("INSERT INTO kto6d_jdownloads_files ( `file_id`,`file_title`,`file_alias`,`description`,`description_long`,`file_pic`,`thumbnail`,`thumbnail2`,`thumbnail3`,`price`,`release`,`language`,`system`,`license`,`url_license`,`license_agree`,`update_active`,`cat_id`,`metakey`,`metadesc`,`size`,`date_added`,`file_date`,`publish_from`,`publish_to`,`use_timeframe`,`url_download`,`extern_file`,`extern_site`,`mirror_1`,`mirror_2`,`extern_site_mirror_1`,`extern_site_mirror_2`,`url_home`,`author`,`url_author`,`created_by`,`created_mail`,`modified_by`,`modified_date`,`submitted_by`,`set_aup_points`,`downloads`,`custom_field_1`,`custom_field_2`,`custom_field_3`,`custom_field_4`,`custom_field_5`,`custom_field_6`,`custom_field_7`,`custom_field_8`,`custom_field_9`,`custom_field_10`,`custom_field_11`,`custom_field_12`,`custom_field_13`,`custom_field_14`,`ordering`,`published`,`checked_out`,`checked_out_time` ) VALUES ( '7','xxxx','xxx','TEXT.','','icon.png','img.png','','','','','1','1','8','','0','0','2','','','465.03 KB','2007-12-26 09:47:37','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00','0','file.pdf','','0','http://www.url.com','0','0','','autorname','','System','','uploader','2011-11-07 15:32:11','0','0','4261','0','0','0','0','0','115','','','','','0000-00-00','0000-00-00','','','422','1','0','0000-00-00 00:00:00' );"); $db->query();$i++; 

..... 
etc. ..... -> 420  x  "$db->setQuery("INSERT INTO kto6d_jdownloads_files ( ...." -> all Downloads.

?>



so i ONLY "flush" the JD 3.x table "kto6d_jdownloads_file" and import ONLY the old kto6d_jdownloads_file files / Download data.

-> https://localhost/administrator/index.php?option=com_jdownloads&view=tools (Tools)
-> "Backup Restoration" button to import the TXT

But, i will wait for your solution, thank you!
  •  

Arno

QuoteI only save this to a txt:
You can try this but you should afterwards extensive test all jD sections in frontend and backend.
Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

Nyx

I will wait for you :-)
The e.g.: the cats are wrong,...
so i have  flush this to,.. and so on.

Wenn you have time i think your way will be the better way ;)
  •  

Arno

Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •  

Nyx

Quote
Update 2015-01-07 Version 3.2.21 Beta published:

corrections for the import process from 1.9.x (the imported Downloads retained now the original ID). So it can now be useful for some users to renew the import process. The bug from 3.2.20 is fixed here.

Sounds very good  ;D
I will try it soon.
  •  

Nyx

so....
The new Version JD 3.2.21 beta work perfekt!
After the data Import from jd 1.8.4 / jd 1.9.2 -All File Ids & cats Ids are the old one (from 1.8.4 / jd 1.9.2) thank you very much  :)
Also the old jcomments data is so right.

THANK YOU !
  •  

Arno

Best Regards / Gruß
Arno
Please make a Donation for jDownloads and/or write a review on the Joomla! Extensions directory!
  •