"write Database Operator - Error: table already exists"

BArndtBArndt MemberPosts:2Contributor I
edited June 2019 inHelp
Hi RM community,
since a few days i try solve a litte problem.

I have a MySQL DB with table x, this one is created in a preprocess to realise parallel processing. If i try to write something into this table with the write Database Operator (prdefined, append, different batch sizes) i always get the message:
Databse error occured: Table 'x' already exists.

Bug, Feature or some stupid mistake.

It is working one time if i dont create the table before. The Operator creates the table and fill the data inside, but to read out the data and recreate the table every subprocess is no solution for me.

- columns have the rigth names and metadata

Thanks in advance,

Benjamin
Tagged:

Answers

  • CharlieFirpoCharlieFirpo MemberPosts:48Contributor II
    Dear Benjamin,

    Maybe fine logging can give you some extra information about the problem (it's only a tip):
    Tools -> Preferences -> Gui -> rapidminer.gui.log.level : FINE

    Run the process then check out the Log tab.

    Good luck!
    Charlie
  • JEdwardJEdward RapidMiner Certified Analyst, RapidMiner Certified Expert, MemberPosts:578Unicorn
    It sounds like a problem with you chosen parameters on the operator.
    RapidMiner has a few options with the write database operator.
    1: that it creates a table & writes data to it. For this you would choose the parameter 'none'.
    2: that it appends data into the table.
    3: that it overwrites an existing table.

    Choose which one you would like to use depending on your purpose.
    There is also another operator Update Database which takes a key field you provide it and either adds new records or updates existing matches.

    Try having a look at the below process with the comments on the operators.





    <宏/ >







    This is the update database operator.
    It will update existing records with a matching ID. Any new records will be appended to the database table. 





    This operator has Overwrite Mode set to 'append'. If the table already exists then the records from your process will be written into this database alongside the existing records. 




    This operator has Overwrite Mode set to 'none'. The table will be created and data written to it.
    If the table already exists this parameter will throw an error. 



    This operator has Overwrite Mode set to 'overwrite'. If the table already exists the existing data will be overwritten.














  • BArndtBArndt MemberPosts:2Contributor I
    Thx Edward,
    我知道不同的选项,选择append, because the table already existe. This is the point which looks like a bug. Choosing append and getting the message "table already exists". I also tryed Update Database, in this case i always get the message that there is no colum named 'xy'. But the table is correct...

    I choose another option, convert the data to csv and load the csv into the table, this is working.

    but thx for the help
  • JEdwardJEdward RapidMiner Certified Analyst, RapidMiner Certified Expert, MemberPosts:578Unicorn
    Hi Benjamin,

    Is there a column named 'xy'? I often get messages when I forget that the column names need to match exactly.
    For example attribute 'email' goes into database column 'email'
    but an error is thrown for attribute 'email' goes into database column 'Email'

    I often use these operators for reading, writing & updating data into databases via SQL Server, MySQL & PostgreSQL so I'm not sure it's a bug.
    Are you able to replicate your problem in a simple example and upload the process(es) here?

    One thing I have learned when doing this is that it's often better to create database tables using the Execute SQL operator as it gives more fine control over indexes, keys & column attributes.
    Another tip when dealing with very large datasets it can sometimes be better to use the Execute SQL operator with a LOAD INFILE statement as this means that your RapidMiner isn't overloaded by memory out errors.
Sign InorRegisterto comment.