SEO

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.

=?ISO-8859-2?Q?Tomasz_Sok=F3lski?= <askman25@NOSPAMgazeta.pl> wrote:
> 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

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

> 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

Well, there is nothing in the log.

> 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?

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:

> DBD::mysql::st execute failed: Thread stack overrun:
> 186116 bytes used of a 196608 byte stack, and 10788 bytes needed.
> Use 'mysqld -O thread_stack=#' to specify a bigger stack.

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 parser eats memory if it has to parse 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 Schwenke napisa?(a):
> Wait! You have a table with 1000 columns?
> Now that's a weird design.

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.

>> DBD::mysql::st execute failed: Thread stack overrun:
>> 186116 bytes used of a 196608 byte stack, and 10788 bytes needed.
>> Use 'mysqld -O thread_stack=#' to specify a bigger stack.

Thank you very much - it helped.

> 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 parser eats memory if it has to parse 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.

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

> 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.

We use MyIsam engine.

> PPS: find my test program attached

Thank's a lot!

Tomasz Sokólski wrote:
> Axel Schwenke napisa?(a):
>
>> Wait! You have a table with 1000 columns?
>> Now that's a weird design.
>
>
> 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.
>

Read up on database normalization. A much better way would be to have a
second table with three columns - userid, questionid and answer.

>>> DBD::mysql::st execute failed: Thread stack overrun:
>>> 186116 bytes used of a 196608 byte stack, and 10788 bytes needed.
>>> Use 'mysqld -O thread_stack=#' to specify a bigger stack.
>
>
> Thank you very much - it helped.
>
>> 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 parser eats memory if it has to parse 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.
>
> Very weird - this is standard version from www.mysql.org running on
> Slackware 10.2 with 2.6.17.7 kernel.
>
>> 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.
>
>
> We use MyIsam engine.
>
>> PPS: find my test program attached
>
>
> Thank's a lot!


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

Jerry Stuckle 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.
> Read up on database normalization. A much better way would be to have a
> second table with three columns - userid, questionid and answer.

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 Sokólski napisał(a):
> 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)

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 Sokólski wrote:
> Jerry Stuckle 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.
>>
>> Read up on database normalization. A much better way would be to have
>> a second table with three columns - userid, questionid and answer.
>
>
> 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)

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.
jstucklex@attglobal.net
==================

Jerry Stuckle napisał(a):
> Not a problem at all.
>
> INSERT INTO answers (name, questionid, answer) VALUES ('paul',
> questionid, answer);
>

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?

=?UTF-8?B?VG9tYXN6IFNva8OzbHNraQ==?= <askman25@NOSPAMgazeta.pl> wrote:
>
> 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)

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

> Is this possible with just one SQL command?

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/

Tomasz Sokólski wrote:
> Jerry Stuckle napisał(a):
>
>> Not a problem at all.
>>
>> INSERT INTO answers (name, questionid, answer) VALUES ('paul',
>> questionid, answer);
>>
>
> 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,

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.
jstucklex@attglobal.net
==================