Forum Replies Created
-
AuthorPosts
-
peterlaursen
Participant1) 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'.
peterlaursen
ParticipantLOAD 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`()
BEGINDECLARE 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;
peterlaursen
Participantok .. 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!
.
peterlaursen
ParticipantNo 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.
peterlaursen
ParticipantLet 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?
peterlaursen
ParticipantI 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)
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!
peterlaursen
Participantbut 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.
peterlaursen
ParticipantWith 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 ..
peterlaursen
ParticipantI 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]
peterlaursen
Participantthis 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.
peterlaursen
Participantno 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)
peterlaursen
ParticipantI meant
also SET EMPTY and SET NULL (unless declared NOT NULL) from context menu should be disabled for those types ..
peterlaursen
Participantok .. 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?
-
AuthorPosts