Professional Web Applications Themes

Problem with very long 'create table as select' query - MySQL

This query has at about 19kB: create tab_out as select x1*(0.019613279029726982)+x2*(0.018225347623229027 )+...+x1000*(0.037470344454050064) as zm1, from tab_in After running it database server crashes and after auto restart I have in log: 061106 11:42:12 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.0.22-max-log' socket: '/var/run/mysql/mysql.sock' port: 3306 MySQL Community Edition - Experimental (GPL) Number of processes running now: 0 061106 15:56:03 mysqld restarted 061106 15:56:04 InnoDB: Started; log sequence number 0 43655 061106 15:56:04 [Note] Recovering after a crash using kamilajs-bin 061106 15:56:10 [Note] Starting crash recovery... 061106 15:56:10 [Note] Crash recovery finished. 061106 15:56:10 [Note] /usr/local/mysql/bin/mysqld: ready for connections. Version: '5.0.22-max-log' socket: '/var/run/mysql/mysql.sock' ...

  1. #1

    Default Problem with very long 'create table as select' query

    This query has at about 19kB:

    create tab_out as
    select
    x1*(0.019613279029726982)+x2*(0.018225347623229027 )+...+x1000*(0.037470344454050064)
    as zm1,
    from tab_in

    After running it database server crashes and after auto restart I have
    in log:

    061106 11:42:12 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
    Version: '5.0.22-max-log' socket: '/var/run/mysql/mysql.sock' port:
    3306 MySQL Community Edition - Experimental (GPL)

    Number of processes running now: 0
    061106 15:56:03 mysqld restarted
    061106 15:56:04 InnoDB: Started; log sequence number 0 43655
    061106 15:56:04 [Note] Recovering after a crash using kamilajs-bin
    061106 15:56:10 [Note] Starting crash recovery...
    061106 15:56:10 [Note] Crash recovery finished.
    061106 15:56:10 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
    Version: '5.0.22-max-log' socket: '/var/run/mysql/mysql.sock' port:
    3306 MySQL Community Edition - Experimental (GPL)

    When I've changed this query to:

    create tab_out as
    select
    x1*(0.019613279029726982)+x2*(0.018225347623229027 )+...+x500*(0.037470344454050064)
    as zm1a,
    x501*(0.019613279029726982)+x2*(0.0182253476232290 27)+...+x1000*(0.037470344454050064)
    as zm1b,
    from tab_in

    everything is OK. Is there some kind of limit on variable definition
    length? And if - why mysql crashes not telling me what is the problem?
    How to fix it?

    Thank you for any suggestions.

    Best regards.
    Tomasz Guest

  2. #2

    Default Re: Problem with very long 'create table as select' query

    =?ISO-8859-2?Q?Tomasz_Sok=F3lski?= <pl> wrote: 

    Wait! You have a table with 1000 columns?
    Now that's a weird design.
     

    Well, there is nothing in the log.
     

    I just tried with a little Perl test program and a recent MySQL-5.0.
    I created a table with 999 columns of type FLOAT and filled it with
    100 rows of random data. Then I SELECTed

    rand()*c001
    rand()*c001 + rand()*c002
    rand()*c001 + rand()*c002 + rand()*c003
    ....
    rand()*c001 + rand()*c002 + rand()*c003 + ... + rand()*c999


    for 536 columns I get the following error:
     

    Please note: this is a normal SQL error, reported from mysqld to the
    client. Mysqld did not crash. I restarted mysqld with thread_stack=512K
    in my.cnf and this time my test program finished without a problem.


    Conclusion: the SQL pr eats memory if it has to p long
    expressions. You can compensate by configuring a bigger stack.


    I cannot explain why your mysqld crashed. Maybe the stack isn't
    checked in 5.0.22 or your specific build of it.


    PS: I could not create a 1000 x FLOAT table in InnoDB because that
    would have exceeded the maximum row length. So I used 999.

    PPS: find my test program attached


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  3. #3

    Default Re: Problem with very long 'create table as select' query

    Axel Schwenke napisa?(a): 

    That's not my design - I just work with problems with such designs :)
    BTW - how to store data from forms - where each form has 1000 questions.
    Each record is one filled form which contains 1000 answers.
     [/ref]

    Thank you very much - it helped.
     

    Very weird - this is standard version from www.mysql.org running on
    Slackware 10.2 with 2.6.17.7 kernel.
     

    We use MyIsam engine.
     

    Thank's a lot!
    Tomasz Guest

  4. #4

    Default Re: Problem with very long 'create table as select' query

    Tomasz Sok髄ski wrote: 
    >
    >
    > That's not my design - I just work with problems with such designs :)
    > BTW - how to store data from forms - where each form has 1000 questions.
    > Each record is one filled form which contains 1000 answers.
    >[/ref]

    Read up on database normalization. A much better way would be to have a
    second table with three columns - userid, questionid and answer.
     [/ref]
    >
    >
    > Thank you very much - it helped.


    >
    > Very weird - this is standard version from www.mysql.org running on
    > Slackware 10.2 with 2.6.17.7 kernel.

    >
    >
    > We use MyIsam engine.

    >
    >
    > Thank's a lot![/ref]


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Problem with very long 'create table as select' query

    Jerry Stuckle napisa艂(a): 
    > Read up on database normalization. A much better way would be to have a
    > second table with three columns - userid, questionid and answer.[/ref]

    Very interesting.

    So we have table:

    userid questionid answer

    paul 1 0.33333
    paul 2 0.45454
    paul 3 0.23232
    john 1 0.55555
    john 2 0.44444
    john 3 0.22222

    I want to insert new records:

    userid questionid answer

    paul 4 answer=questionid[1].answer(where userid = paul) +
    questionid[2].answer(where userid = paul)
    john 4 answer=questionid[1].answer(where userid = john) +
    questionid[2].answer(where userid = paul)

    Is there a possibility to insert this records in one sql command?

    (sorry - I'm not an SQL programmer - I would like to know if this is
    hard to write in one sql command)
    Tomasz Guest

  6. #6

    Default Re: Problem with very long 'create table as select' query

    Tomasz Sok贸lski napisa艂(a): 

    I mean:

    paul 4 answer=questionid[1].answer(where userid = paul) +
    questionid[2].answer(where userid = paul)
    john 4 answer=questionid[1].answer(where userid = john) +
    questionid[2].answer(where userid = john)


    If I have 1000 columns with answers it's simple - I can insert new
    column question1001 = question1 + question2 - how to do that if I have just:

    userid questionid answer
    Tomasz Guest

  7. #7

    Default Re: Problem with very long 'create table as select' query

    Tomasz Sok贸lski wrote: 
    >>
    >> Read up on database normalization. A much better way would be to have
    >> a second table with three columns - userid, questionid and answer.[/ref]
    >
    >
    > Very interesting.
    >
    > So we have table:
    >
    > userid questionid answer
    >
    > paul 1 0.33333
    > paul 2 0.45454
    > paul 3 0.23232
    > john 1 0.55555
    > john 2 0.44444
    > john 3 0.22222
    >
    > I want to insert new records:
    >
    > userid questionid answer
    >
    > paul 4 answer=questionid[1].answer(where userid = paul) +
    > questionid[2].answer(where userid = paul)
    > john 4 answer=questionid[1].answer(where userid = john) +
    > questionid[2].answer(where userid = paul)
    >
    > Is there a possibility to insert this records in one sql command?
    >
    > (sorry - I'm not an SQL programmer - I would like to know if this is
    > hard to write in one sql command)[/ref]

    Not a problem at all.

    INSERT INTO answers (name, questionid, answer) VALUES ('paul',
    questionid, answer);

    Of course, you would also be better off using id's - for instance, what
    if you have two "paul"s? Something like:


    Table TestTakers
    id name

    Table Answers

    id question answer

    Your TestTakers table might look like:

    1 paul
    2 John

    And your Answers table might look like

    1 1 0.33333
    1 2 0.45454
    1 3 0.23232
    2 1 0.55555
    2 2 0.44444
    2 3 0.22222

    Then to get a specific answer you would join the two tables, i.e.

    SELECT answer FROM Answers
    JOIN TestTakers ON Answers.id = TestTakers.id
    WHERE questionid = 2;

    You really need to read up on SQL and database normalization. SQL is a
    very powerful language, and proper normalization will make you life much
    easier.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  8. #8

    Default Re: Problem with very long 'create table as select' query

    Jerry Stuckle napisa艂(a): 

    No - I know how to INSERT rows and how to join tables - but the problem is:

    We have one table - for example:

    userid variableid variablevalue
    1 1 0.3
    1 2 0.2
    1 3 0.6
    2 1 0.3
    2 2 0.4
    2 3 0.1

    the problem is: For every user in the table (userid) find a value
    (variablevalue) for new variable (variableid = 4) according to:

    variable 4 = variable 2 + variable 3

    and insert this new variable into table as new record - so the table
    after operation would be:

    userid variableid variablevalue
    1 1 0.3
    1 2 0.2
    1 3 0.6
    1 4 0.8 (0.2 + 0.6)
    2 1 0.3
    2 2 0.4
    2 3 0.1
    2 4 0.5 (0.4 + 0.1)

    Is this possible with just one SQL command?
    Tomasz Guest

  9. #9

    Default Re: Problem with very long 'create table as select' query

    =?UTF-8?B?VG9tYXN6IFNva8OzbHNraQ==?= <pl> wrote: 

    Why would somebody like to do that? It's adding redundant data to the
    table. If you need the sum of some answers per user, use GROUP BY:

    SELECT userid, SUM(variablevalue) AS value FROM foo
    WHERE variableid IN (2,3) GROUP BY userid
     

    You can use INSERT ... SELECT ... to select from the table and insert
    the results at the same time. Again: this will denormalize your data.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  10. #10

    Default Re: Problem with very long 'create table as select' query

    Tomasz Sok贸lski wrote: 
    >
    > No - I know how to INSERT rows and how to join tables - but the problem is:
    >
    > We have one table - for example:
    >
    > userid variableid variablevalue
    > 1 1 0.3
    > 1 2 0.2
    > 1 3 0.6
    > 2 1 0.3
    > 2 2 0.4
    > 2 3 0.1
    >
    > the problem is: For every user in the table (userid) find a value
    > (variablevalue) for new variable (variableid = 4) according to:
    >
    > variable 4 = variable 2 + variable 3
    >
    > and insert this new variable into table as new record - so the table
    > after operation would be:
    >
    > userid variableid variablevalue
    > 1 1 0.3
    > 1 2 0.2
    > 1 3 0.6
    > 1 4 0.8 (0.2 + 0.6)
    > 2 1 0.3
    > 2 2 0.4
    > 2 3 0.1
    > 2 4 0.5 (0.4 + 0.1)
    >
    > Is this possible with just one SQL command?[/ref]


    Tomasz,

    I agree with Alex. This is adding redundant data to the table, and will
    make it much harder to manage. What happens if you need to go in and
    change variable 2, for instance? You'll have to recompute variable 4.

    Rather, use the SQL calls to compute on the fly.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Replies: 4
    Last Post: July 30th, 02:02 AM
  2. trying to update a table after making a join select query on two tables
    By rob merritt in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: March 1st, 10:33 PM
  3. Select Query problem
    By irinchiang@justeducation.com in forum PHP Development
    Replies: 9
    Last Post: February 1st, 09:45 AM
  4. problem with the select query
    By sid tow in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 13th, 11:23 AM
  5. Replies: 4
    Last Post: October 15th, 07:37 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139