Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Forum Replies Created

Viewing 15 posts - 3,091 through 3,105 (of 7,398 total)
  • Author
    Posts
  • in reply to: #26170
    peterlaursen
    Participant

    1) Yes, this solution handles one row per iteration. That is why we loop.

    2) When data from one row in `second`table has been tranferred to the `first` table that row is deleted on `second` (you still have the original CSV, so no problem in that!). So next loop will take the next row. If we did not delete a row for every iteration it would continue forever to take the same row!

    3) You will need the DELIMITER statement if you have more than a single statement inside inside a 'stored program' (Stored Procedure/Function, Trigger, Event) because inside the BEGIN .. END construction you use the default ” ; ” delimiter. The user-defined DELIMITER (in the example “$$”) is used to conclude the CREATE statement (in the example “END$$”). It would not be a good idea either to put a page telling 'end of story' of page 4 of a book if it has 400 pages! 🙂

    There are probably more solutions. I think the SELECT .. WHERE IN (SELECT …) construction could be handled by a JOIN as well. But I did not find a solution that does not loop. Traditional MySQL programming (before version 5) would typically implement such loop in some script/application (PHP, C, Perl etc.), but a Stored Procedure can do this too. With other databases (SQL Server, Oracle etc.) the 'tradition' is different – here programmers will typically avoid external applications if a SP or a Trigger can do the job. MySQL is 'moving in that direction'.

    in reply to: #26168
    peterlaursen
    Participant

    LOAD DATA is not allowed in Stored Procedures, so that cannot be included.

    But except for that your SP could look like:

    Code:
    DROP PROCEDURE IF EXISTS `test`.`csvfix`;

    DELIMITER $$

    CREATE PROCEDURE `test`.`csvfix`()
    BEGIN

    DECLARE my_id INTEGER;

    WHILE ((select count(*) from test.second) > 0) DO

    UPDATE `first` SET `va2`= (select `va2` from `second` order by `id` limit 1)
    WHERE `id` IN (select `id` from `second` order by `id`) LIMIT 1;
    SET my_id = (select `id` from `second` order by `id` limit 1);
    DELETE FROM `second` WHERE `id` = my_id;

    END WHILE;

    END$$

    DELIMITER;

    in reply to: #26167
    peterlaursen
    Participant

    ok .. I do not claim this is the most efficient way but it works!

    say your table is like

    CREATE TABLE `first` (

    `id` int(11) NOT NULL default '0',

    `va1` varchar(20) default NULL,

    `va2` varchar(20) default NULL

    )

    now import the CSV into this table

    CREATE TABLE `second` (

    `id` int(11) NOT NULL default '0',

    `va2` varchar(20) default NULL

    )

    You can copy the data for every row with this set of statements (requires MySQL 4.1 or higher due to use of subqueries)

    Code:
    update `first` set `va2`= (select `va2` from second order by `id` limit 1) where `id` in (select `id` from second order by `id`) limit 1;
    set @id = (select `id` from second order by `id` limit 1);
    delete from second where id = @id;

    With the example from previous post you need to execute three times. It does no harm to execute more times – it will update nothing. The statements take data from one row in second table, updates first table with that data and deletes the row in second table. (Note this will handle UPDATES only not new data to be INSERTed)

    So you may create a Stored Procedure running the above statements in a loop (either just doing so many times that you know it is enough – or better test for the condition “select count (*) from second = 0” to exit the lop. Of course the CSV import (LOAD DATA INFILE statement) may be included with the Stored Procedure as well, so that may just do

    Code:
    call my_csvsync(file_to_sync);

    .. whenever you have a new file to be synced!

    .

    in reply to: #26174
    peterlaursen
    Participant

    No we do not SHOW STORAGE ENGINES we SELECT FROM INFORMATION_SCHEMA.ENGINES.

    SHOW STORAGE ENGINES has issues with specific server versions and with 3rd party engines.

    Please read:

    http://webyog.com/faq/content/29/135/en/wh…-by-sqlyog.html

    http://code.google.com/p/sqlyog/issues/detail?id=613

    http://www.webyog.com/forums//index.php?showtopic=3832

    I think we should hardcode support for MEMORY. Except for that I will not advocate any change.

    Because what we are doing is the correct way with MySQL 'pluggable storage engine' architecture introduced in 5.1

    Just execute “ALTER TABLE … ENGINE …”

    As regards FEDERATED we will need a complete new interface to define those. You cannot just ALTER an 'ordinary' table to a FEDERATED table. A FEDERATED table is a 'link' to a table on another server.

    in reply to: #26166
    peterlaursen
    Participant

    Let us create a small test case. That always make things easier.

    You have a table with data like:

    (1,a,:cool:

    (2,c,d)

    (3,e,f)

    (4.g,h)

    (5,i,j)

    (6,k,l)

    (7,m.n)

    (8,o,p)

    and a CSV file containing

    (3,x)

    (4,y)

    (7,z)

    and x shall replace f, y shall replace h and finally z shall replace n.

    I think this is 'problem in a nutshell´? Now let us think about this!

    But still I think it was worth considering working directly against the database (create VIEWs for every 'worker task') with a GUI client like SQLyog.

    No other users out there who has some good idea?

    in reply to: #26164
    peterlaursen
    Participant

    I think you can do a RIGHT/LEFT JOIN.

    But I think you should consider a better solution than CSV if it is for production systems.

    I thought it was some kind of 'recovery' your were doing.

    btw: I had the server crash too!

    reported here: http://bugs.mysql.com/bug.php?id=35469

    It is a server crash. It is not connection being dropped (like when exceeding max_allowed_packet)

    in reply to: #26162
    peterlaursen
    Participant

    “the products_id column is the primary key in the products table and it exists in the csv file also.”

    Then it is definitely possible to import the CSV to an empty table, JOIN the original table and the table table populated by CSV import.

    You are not wasting my time. This is learning for me too!

    in reply to: #26160
    peterlaursen
    Participant

    but apart from those technicalities ..

    If the CSV anad the table does not have at least a single column in common how should it be possible to tell what row from the CSV match which row in the table.

    Only chance is if you can have the luck to add an autoincrement column where rows are ordered like in the CSV.

    in reply to: #26159
    peterlaursen
    Participant

    With a little manipulation I think things can be done!

    another idea:

    1) create a new table t1 that 'fits' your CSV and import to it

    2) create a autoincrement field on both your original table and the t1 table so that row no's match in both

    3) create view v1 .. as select from both tables (INNER JOIN the auto_increment column from one table ON the a_i column the other)

    4) create table t2 as select * from v1

    5) ALTER TABLE t2 to add indexes, defaults etc ..

    in reply to: #26157
    peterlaursen
    Participant

    I think your 'gone away message' is a server bug. What server version?

    But my mistake: REPLACE INTO will not preserve data. Every column not specified will that their defaults.

    You can use the (Enterprise) Migration Tool with the Microsoft text driver, however. Alternatively I think you need a script/application that reads the file and updates.

    Actually I tried such CSV import to an 'partial VIEW'. This is not workig for me either. And I do no quite understand the error because the underlying table has no indexes at all!

    [attachment=854:notwork.jpg]

    in reply to: #25631
    peterlaursen
    Participant

    this is fixed in the trunk. Next beta will have the fix.

    note: ONLY this is fixed: unchecking NULL in BLOB VIEWER will change value from NULL to EMPTY string. Note that you will have to save the row after closing BLOB viewer.

    in reply to: #26155
    peterlaursen
    Participant
    in reply to: #26154
    peterlaursen
    Participant

    no directly. It wild garble.

    One option is to create a VIEW with the two columns ind improt into this one (be sure that the remaining columns have NULL allowed or a DEFAULT).

    But we do not support export/import in GUI for VIEWS (for some silly reason). You will have the generate the LOAD DATA infile statement from the editor (you can import to a 'dummy' table and copy the SQL from HISTORY)

    in reply to: #25630
    peterlaursen
    Participant

    I meant

    also SET EMPTY and SET NULL (unless declared NOT NULL) from context menu should be disabled for those types ..

    in reply to: #25629
    peterlaursen
    Participant

    ok .. also SET DEFAULT and SET NULL from context menu are disabled for TEXT and BLOB types! (SET DEFAULT does not apply for TEXT and BLO:cool:.

    It should be solved in this context:

    http://code.google.com/p/sqlyog/issues/det…3&q=varchar

    (because this also applies to TEXT types?)

    btw: what would you prospose for a BLOB NOT NULL?

Viewing 15 posts - 3,091 through 3,105 (of 7,398 total)