#4 Problems after reconstructing installation from backup file

closed
None
5
2005-09-28
2005-06-20
Ulf Benedix
No

Hi,

I have still some problems after "reconstructing" my
Wikindx3.0.7-Installation (restart with empty database,
let Wikindx3 create the tables, import the table
entries from the backup file, .... this was bugs item
#1217492...).

Basically, I can work with it, but I found that

- all timestamps for new added ressources are set to
the default (0000-00-00 etc.), so the List->Timestamp
(to get all recently added resources on the screen)
cannot work

- if I add a resource to a user defined bibliography,
the list of resources in this collection is
overwritten, i.e. before adding it it said e.g.
"2,56,334", after it says ",555" in
WKX_user_bibliography.bibliography

- is it o.k. that, when using a user defined
bibliography for browsing, new ressources are not
automatically added to this bibilography?

Sorry I have to ask again for help...

ulf

Discussion

  • Mark Grimshaw

    Mark Grimshaw - 2005-06-20

    Logged In: YES
    user_id=655937

    Hi Ulf,

    Sorry to hear you're still having problems.

    In answer to the timestamp problem: using a MySQL client,
    you shoul dbe able to update all resource timestamps to the
    c urrent time with:
    UPDATE `wkx_resource_timestamp` SET `timestamp` = NOW( );

    For the list of resources in the user bibliography,
    congratulations for discovering a bug. I'll fix it asap.

    I'm happy with the third point. Often it's best to not do
    things automatically and, in this case, the operation of
    adding a resource to a user bib. is easier than removing one.

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-06-20

    Logged In: YES
    user_id=655937

    I forgot to ask, have you perhaps considered exporting what
    you do have to bibtex, re-installing (after removing all
    tables in the database) and then re-importing the bibtex
    file? It's highly likely that continuing on the path you
    have chosen will throw up more problems and it may be better
    to just bite the bullet and start more or less from scratch.
    Exporting to bibtex won't export your quotes, paraphrases,
    musings but will export either the abstract or notes. The
    alternative is to export an Endnote tabbed file then import
    the Endnote XML. At least you should have the important
    resource data and hopefully will no longer have problems
    such as you've been listing.

    The user bibliography bug has now been fixed and will be
    available in the next release. If you want the quick fix,
    in core/user/MYWIKINDX.php replace lines 465 to 477
    inclusive with:
    $dbBibsRow = $this->db->fetchOne($recordset);
    if(!$dbBibsRow)
    $updateArray = array('bibliography' => implode(",",
    $inputBibs));
    else
    {
    $dbBibs = explode(",", $dbBibsRow['bibliography']);
    foreach($inputBibs as $id)
    {
    if(array_search($id, $dbBibs) === FALSE)
    $dbBibs[] = $id;
    }
    $updateArray = array('bibliography' => implode(",",
    $dbBibs));
    }

     
  • Ulf Benedix

    Ulf Benedix - 2005-06-21

    Logged In: YES
    user_id=1231610

    Hi,

    - thank you for the quick fix for the user_bibliography
    problem;

    it is at least better now - first I thought it was o.k, but:

    I started with a new bibliography, added a ressource and
    checked with
    MySQL Query Browser the contents of
    WKX_user_bibliography.bibliography for the new
    bibilography: it said:

    (395)

    This is o.k. I added then a ressource, coming to

    (3,285)

    and -as there is no ressorce with id 3 in my database- only
    one ressource displayed.

    After adding 2 more I got

    (3,223,95)

    and so on.

    (When I tried it first, my first ressource added was 479,
    after adding one more I got 4,244 - as 4
    existed, 2 ressources were displayed, which seemd o.k. -
    but then I noticed that it was not the one I added first,
    and after
    adding more, I got something like 4,333,444,
    so I started to test it with a new bibliography, with the
    results above.)

    It seems, that now the first entry, but only the first entry
    survives, and of this entry only the first number...

    as if , after

    > $dbBibs = explode(",", $dbBibsRow['bibliography']);

    $dbBibs only contains the first number of the first id of
    the list,
    so: as if $dbBibsRow['bibliography'] only contains the first
    first number of the id of the list.

    - for the "reconstruction", here is what I did in detail,
    I used database operations, not BiBTeX:
    (database server is MySQL 4.1.9 on Suse Linux 9.x with
    Apache 2.0.50, PHP 4.3.8)

    > dump with MySQL Administrator dump 1.4
    > edit the dump file:
    >> delete all rows starting with /*!
    >> delete all drop table and create table (except for
    WKX_database_summary),
    >> keep all "insert" statement but
    >> remove the "admin" row insert to WKX_users
    >> remove the "general" row insert to WKX_category
    > save as a sql script
    > drop all tables in the database
    > run Wikindx3.0.7
    > complete the admin form,
    > logout,
    > run the sql script

    - I think this worked fine, and that I have now a consistent
    3.0.7-installation, and all my resource
    data are in place; so there is only the timestamp problem left.

    This may be a new behavior of 3.0.7 (with MySQL 4.1.x?) -
    before my "reconstruction", with the strange behavior I had,
    I could not add new resources with 3.0.7, so I could not see
    this behavior before. And with 3.0.1, the timestamps were o.k.

    (I was happy with the automatical timestamps, because I used
    the list->timestamps
    list to get all recently added resources on top of the list,
    to add them to their respective user bibliographies.
    Now, all new ressources appear at the end of the list, in
    inpredictable sub-order.)

    Ulf

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-06-21

    Logged In: YES
    user_id=655937

    I think I know what's going wrong if, as I understand, your
    new resources don't update their timestamp. Around v. 3.0.4
    or so, I changed the timestamp field from TIMESTAMP to
    DATETIME. Can you check if your timestamp fields in:
    WKX_resource,
    WKX_musing_text,
    WKX_paraphrase_comment,
    WKX_quote_comment,
    WKX_new and
    WKX_user_register

    are set to type DATETIME?

     
  • Ulf Benedix

    Ulf Benedix - 2005-06-22

    Logged In: YES
    user_id=1231610

    Hi,

    I checked it and found all timestamp fields with type DATETIME.

    For a test, I added a quote comment, and also there (in
    WKX_quote_comment) the timestamp value is set to default
    (0000-...)

    I think, they changed the behavior of TIMESTAMP with MySQL
    4.1 .

    Ulf

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-06-22

    Logged In: YES
    user_id=655937

    When updating the timestamp field, it's updated as with any
    other field - the datetime string is simply written as is
    without using functions such as NOW(). It does this by
    using AdoDB's DBTimestamp() function which takes UNIX epoch
    time and converts it to the appropriate DATETIME format.

    As an example, here's the SQL statement I get when adding a
    paraphrase/comment:
    UPDATE `WKX_resource_timestamp` SET
    `timestamp`='\'2005-06-22 22:49:30\'' WHERE `id`='18'

    What happens when you try to run that through your MySQL
    client (changing the id no. presumably)? AFAIK, whatever
    the MySQL version, there should be no problem writing that
    to a DATETIME field.

    If you want to see the SQL statements used by WIKINDX,
    uncomment line 104 in the function query() in
    core/sql/SQL.php. Check to see that you're at least getting
    an appropriate SQL statement as above and that it will run
    through your MySQL client.

     
  • Ulf Benedix

    Ulf Benedix - 2005-06-22

    Logged In: YES
    user_id=1231610

    Hi,

    this statement:

    UPDATE `WKX_resource_timestamp` SET
    `timestamp`='\'2005-06-22 22:49:30\'' WHERE `id`='409';

    results in no update - timestamp stays 0000-...

    but when I delete the '' delimiting the string, like this:

    UPDATE `WKX_resource_timestamp` SET `timestamp`='2005-06-22
    22:49:30' WHERE `id`='409';

    this works!

    This is what I get when I run the statements with the the
    mysql client on the server:

    mysql> UPDATE `WKX_resource_timestamp` SET
    `timestamp`='\'2005-06-22 22:49:30\'' WHERE `id`='409';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    Rows matched: 1 Changed: 0 Warnings: 1

    mysql> UPDATE `WKX_resource_timestamp` SET
    `timestamp`='2005-06-22 22:49:30' WHERE `id`='409';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    Ulf

     
  • Ulf Benedix

    Ulf Benedix - 2005-06-22

    Logged In: YES
    user_id=1231610

    Hi, i forgot to add the Warning Message produced by the
    first statement (with \'):
    mysql> show warnings;
    +---------+------+--------------------------------------------------------------+
    | Level | Code | Message
    |
    +---------+------+--------------------------------------------------------------+
    | Warning | 1264 | Data truncated; out of range for column
    'timestamp' at row 1 |
    +---------+------+--------------------------------------------------------------+

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-06-22

    Logged In: YES
    user_id=655937

    OK - now we're getting somewhere. Here should be a quick fix:

    In core/sql/SQL.php in the formatTimestamp() function change
    line 539 from:

    return $this->db->DBTimeStamp($time);
    to:
    return stripslashes(str_replace("'", "",
    $this->db->DBTimeStamp($time)));

    This works for me.

     
  • Ulf Benedix

    Ulf Benedix - 2005-06-23

    Logged In: YES
    user_id=1231610

    This works for me too.
    Thanks!

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-06-23
    • assigned_to: nobody --> sirfragalot
     
  • Mark Grimshaw

    Mark Grimshaw - 2005-06-23

    Logged In: YES
    user_id=655937

    Great. And the code fix works for other MySQL databases so
    I'll keep it in.

     
  • Ulf Benedix

    Ulf Benedix - 2005-06-24

    Logged In: YES
    user_id=1231610

    Hi,

    maybe I was misleading you when I wrote in one of my comments:
    "only the time stamp problem left" ;-)

    I still have the strange behavior when adding ressources to
    user bibilographies.

    I wonder if you can reproduce the following:
    -> add a new ressource id > 9, eg. with id = 333 to a new
    bibliography (I get in WKX_user_bibliography.bibliography: 333)
    -> add another ressource, eg. id = 12 (I get: 3,12)
    -> add more ressources, eg. id 22,33 (I get: 3,22,33).

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-06-24

    Logged In: YES
    user_id=655937

    Hi Ulf,

    I believe I've already fixed that behaviour (the last id in
    the list is dropped when adding new resources to a user
    bibliography) and it's in CVS ready for the next release
    (hopefully a few days away).

     
  • Ulf Benedix

    Ulf Benedix - 2005-06-27

    Logged In: YES
    user_id=1231610

    Hi,

    I was wondering if I should mention the behavior I get again,
    because I used the patch from your comment of the 2005-06-21
    08:48,
    and it was better, but it was still not working as expected.
    If it is fixed now, I'll be happy to wait for the next version.

    In any case, I just had a look on the code, just in case I
    could find out
    how it works and what goes wrong with my installation (I try
    to learn a little of php now ;-).
    And I think, there may have been something wrong with the line

    470 > $dbBibs = explode(",", $dbBibsRow['bibliography']);

    I wondered if the limitation by ['bibliography'] may cause
    the trouble I have:
    wouldn't this be identical to $dbBibsRow[0], which would
    cause exactly the behavior I described,
    because the explode would only take into account the very
    first character in $dbBibsRow?.

    I tried with

    470 > $dbBibs = explode(",", $dbBibsRow);

    and this seems to work as expected.

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-06-27

    Logged In: YES
    user_id=655937

    Yes, that's the offending line. Lines 464-473 of
    MYwIKINDX.php are now:

    // bibliography is a comma-separated list of unique resource
    ids.
    $dbBibsRow = $this->db->fetchOne($recordset);
    if(!$dbBibsRow)
    $updateArray = array('bibliography' => implode(",",
    $inputBibs));
    else
    {
    $dbBibs = explode(",", $dbBibsRow);
    $updateBibs = array_unique(array_merge($dbBibs, $inputBibs));
    $updateArray = array('bibliography' => implode(",",
    $updateBibs));
    }

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-09-28

    Logged In: YES
    user_id=655937

    Fixed.

     
  • Mark Grimshaw

    Mark Grimshaw - 2005-09-28
    • status: open --> closed
     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks