Re: Arrays
Hi again,
Thanks for the tips. My problem is:
I have a Function in Mysql that has some arguments in the signature as follows:
CREATE FUNCTION Myfunction( type TINYINT, sec SMALLINT, vid INTEGER,
way TINYINT, quid INTEGER, day TINYINT )
RETURNS CHAR(50)
BEGIN
DECLARE result CHAR(50);
DECLARE Temp DECIMAL(9,1);
SELECT Table1( vid, day, way) INTO Temp;
IF Temp IS NOT NULL THEN
SELECT CONCAT_WS(',',sec, 0, quid, Temp) into result;
ELSE
SELECT CONCAT_WS(',',sec, 0, quid, 0 ) into result;
END IF;
RETURN result;
END;
The problem seems to be solved by using CHAR but I really want to have a
sequence of Integers as the result not CHAR. The reason that I dont use a table
to insert the result into it and retrieve it later is that this process takes
more time that It should. Any thoughts?
Best regards,
Javad
From: Shawn Green (MySQL) shawn.l.gr...@oracle.com
To:
Cc: javad bakhshi javadbakh...@yahoo.com; Johan De Meersman
vegiv...@tuxera.be; mysql@lists.mysql.com mysql@lists.mysql.com
Sent: Monday, August 29, 2011 4:51 PM
Subject: Re: Arrays
On 8/27/2011 11:18, wrote:
2011/08/26 13:58 -0700, javad bakhshi
Thanks guys for the help. but my problem seems to stand unsolved.
Right, no arrays. Nothing is left but table. I used a temporary table, but
note that MySQL also does not let table be returned, or passed in. The
table-name will be *sigh* global.
If the table remains an active part of the connection, you always have access
to it. It is possible to pass the name of the table into a stored procedure and
use a prepared statement to do nearly anything you want to do with the data.
What is it you are trying to do with this array in the first place? It sounds
as though trying to process this data using the set-oriented functions of the
SQL language is causing you more of a problem than the fact that the language
doesn't have an array data type.
There are normally many ways to solve any problem. Perhaps if you shared the
problem you are trying to solve, you can see how many different ways the
members of the list can solve it without resorting to an array?
-- Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
-- MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=javadbakh...@yahoo.com
Re: locked non-existent row
On Wed, Aug 31, 2011 at 4:10 AM, Peter Brawley wrote: While a transaction in one thread tries to update a non-existent InnoDB row with a given key value, an attempt to insert that value in another thread is locked out. Does anyone know where this behaviour is documented? In the manual it is called gap locking: http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.html Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
Hello Javad,
On 9/2/2011 05:51, javad bakhshi wrote:
Hi again,
Thanks for the tips. My problem is:
I have a Function in Mysql that has some arguments in the signature as
follows:
CREATE FUNCTION Myfunction( type TINYINT, sec SMALLINT, vid INTEGER,
way TINYINT, quid INTEGER, day TINYINT )
RETURNS CHAR(50)
BEGIN
DECLARE result CHAR(50);
DECLARE Temp DECIMAL(9,1);
SELECT Table1( vid, day, way) INTO Temp;
IF Temp IS NOT NULL THEN
SELECT CONCAT_WS(',',sec, 0, quid, Temp) into result;
ELSE
SELECT CONCAT_WS(',',sec, 0, quid, 0 ) into result;
END IF;
RETURN result;
END;
The problem seems to be solved by using CHAR but I really want to have a
sequence of Integers as the result not CHAR. The reason that I dont use
a table to insert the result into it and retrieve it later is that this
process takes more time that It should. Any thoughts?
Actually, that's not 'the problem' you are attempting to solve at all.
That is an attempt at a 'solution' to the problem.
If I stare very hard into my crystal ball and use all of my
psychic-SQL-fu, all I can tell is that you are attempting to build some
kind of comma-separated value. However, why you need this list and what
it is trying to solve is beyond me.
Please step back from the SQL end of things for a second and talk to us
about the actual problem you are trying to solve. Are you generating
nuclear launch codes? Are you indexing someone's genome? Are you trying
to build a web page? All of this SQL coding is designed to be a single
step in some larger process. If you share that larger purpose with us,
we may be able to suggest a more efficient approach than arrays to solve
your larger problem.
Regards,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
Hi,
None of the above. :) .This is just an academic research on a benchmark. I just
want to access my database with this function and return 4 Integer values.
I separated the numbers with comma to process on it later as an string and
split the result using string functions which did work. I was just wondering if
its possible with mysql to return a sequence of numbers or not. I think to
solve my problem among the ways mentioned before I will create a table with
Engine=Memory to speed up things. I don't think any other way is possible.
Thanks,
Javad
From: Shawn Green (MySQL) shawn.l.gr...@oracle.com
To: javad bakhshi javadbakh...@yahoo.com
Cc: mysql@lists.mysql.com mysql@lists.mysql.com
Sent: Friday, September 2, 2011 2:18 PM
Subject: Re: Arrays
Hello Javad,
On 9/2/2011 05:51, javad bakhshi wrote:
Hi again,
Thanks for the tips. My problem is:
I have a Function in Mysql that has some arguments in the signature as
follows:
CREATE FUNCTION Myfunction( type TINYINT, sec SMALLINT, vid INTEGER,
way TINYINT, quid INTEGER, day TINYINT )
RETURNS CHAR(50)
BEGIN
DECLARE result CHAR(50);
DECLARE Temp DECIMAL(9,1);
SELECT Table1( vid, day, way) INTO Temp;
IF Temp IS NOT NULL THEN
SELECT CONCAT_WS(',',sec, 0, quid, Temp) into result;
ELSE
SELECT CONCAT_WS(',',sec, 0, quid, 0 ) into result;
END IF;
RETURN result;
END;
The problem seems to be solved by using CHAR but I really want to have a
sequence of Integers as the result not CHAR. The reason that I dont use
a table to insert the result into it and retrieve it later is that this
process takes more time that It should. Any thoughts?
Actually, that's not 'the problem' you are attempting to solve at all. That is
an attempt at a 'solution' to the problem.
If I stare very hard into my crystal ball and use all of my psychic-SQL-fu, all
I can tell is that you are attempting to build some kind of comma-separated
value. However, why you need this list and what it is trying to solve is beyond
me.
Please step back from the SQL end of things for a second and talk to us about
the actual problem you are trying to solve. Are you generating nuclear launch
codes? Are you indexing someone's genome? Are you trying to build a web page?
All of this SQL coding is designed to be a single step in some larger process.
If you share that larger purpose with us, we may be able to suggest a more
efficient approach than arrays to solve your larger problem.
Regards,
-- Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Re: locked non-existent row
On 9/1/2011 9:06 AM, Jerry Schwartz wrote: -Original Message- From: Peter Brawley [mailto:peter.braw...@earthlink.net] Sent: Wednesday, August 31, 2011 10:40 AM To: r...@grib.nl; mysql@lists.mysql.com Subject: Re: locked non-existent row On 8/31/2011 4:50 AM, Rik Wasmus wrote: While a transaction in one thread tries to update a non-existent InnoDB row with a given key value, an attempt to insert that value in another thread is locked out. Does anyone know where this behaviour is documented? [JS] Forgive my ignorance, but I thought that was standard behavior for a row- or row-range lock (not just MySQL) in any DBMS that supported row locking. (Back when these things were first being invented, one term was predicate locking.) The general idea was that you are locking rows that meet certain criteria, whether any or all of them exist or not. You're locking not only the existence, but the potential existence, of those rows. I would expect it to apply not only to keys, but to any set. For example, SELECT * FROM `t` WHERE `t`.`x` 3 FOR UPDATE; should lock all rows where `t`.`x` 3 for update, insertion, or deletion -- regardless of whether or not `x` is a key. Otherwise you have no way of knowing who wins. The ability to lock non-existent records is critical. Try it, you'll see. I agree entirely. I didn't question the practice. My question concerns documentation. PB - Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.giiresearch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: locked non-existent row
On 9/2/2011 6:15 AM, Jochem van Dieten wrote: On Wed, Aug 31, 2011 at 4:10 AM, Peter Brawley wrote: While a transaction in one thread tries to update a non-existent InnoDB row with a given key value, an attempt to insert that value in another thread is locked out. Does anyone know where this behaviour is documented? In the manual it is called gap locking: http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.html That makes sense. A reference in http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read to those bits would be helpful to users. PB Jochem -- Jochem van Dieten http://jochem.vandieten.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Arrays
2011/09/02 06:09 -0700, javad bakhshi Hi, None of the above. :) .This is just an academic research on a benchmark. I just want to access my database with this function and return 4 Integer values. I separated the numbers with comma to process on it later as an string and split the result using string functions which did work. I was just wondering if its possible with mysql to return a sequence of numbers or not. I think to solve my problem among the ways mentioned before I will create a table with Engine=Memory to speed up things. I don't think any other way is possible. The string takes less time than table (you have not shown your table code) only because of the problem s size. If you had thousands of numbers the string at first would be only slower, but with enough it would not work at all, because you would reach MySQL s string-size limit. A benchmark of repeatedly only 4 integers is not much of one. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
select ... into local outfile ... ???
hi, there, the following is my sql statement: SELECT HIGH_PRIORITY SQL_BIG_RESULT SQL_NO_CACHE tb.url_sign, m_url, m_title, m_weight INTO OUTFILE '/tmp/a.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n' STARTING BY '=' FROM d_local.ta, d_news.tbWHERE ta.url_sign = tb.url_sign Before, the server and the client were on the same machine; now, I need to generate the output file on client (which is on a different machine from the server). But it seams that there is no select ... into LOCAL file statement.Any suggestion is appreciated. Dennis
Re: select ... into local outfile ... ???
Dennis, The following closely simulates the default INTO OUTFILE, you'll have to modify it slightly for your purposes: mysql --delimiter=comma -N select_statement.sql \ | sed 's/\/\\\/g' \ | sed 's/\t/\,\/g' \ | sed 's/$/\/g' \ | sed 's/^/\/g' \ | sed 's/\NULL\/\\N/g' \ | sed 's/\\t/\t/g' ' output.txt Tyler * *On 9/2/11 3:40 PM, Dennis wrote: hi, there, the following is my sql statement: SELECT HIGH_PRIORITY SQL_BIG_RESULT SQL_NO_CACHE tb.url_sign, m_url,m_title, m_weightINTO OUTFILE '/tmp/a.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n' STARTING BY '=' FROM d_local.ta, d_news.tbWHERE ta.url_sign = tb.url_sign Before, the server and the client were on the same machine; now, I need to generate the output file on client (which is on a different machine from the server). But it seams that there is no select ... into LOCAL file statement.Any suggestion is appreciated. Dennis
Build from bazaar source on Mac 10.7 (Lion) fails
Hello, I am trying to setup a development machine to start delving into some of the internal code of the MySQL DB. I'm off to a great start, since I can't even get it to build properly. I'm sure I'm missing an important step, but am following the instructions from http://dev.mysql.com/doc/refman/5.5/en/installing-development-tree.html and http://dev.mysql.com/doc/refman/5.5/en/installing-source-distribution.html Below are my steps, but I do have another instance of mysql running that I use for work development. So I was trying to set this one up as on a different port. $ mkdir mysql-repo $ cd mysql-repo/ $ bzr init-repo . $ bzr branch lp:mysql-server/5.5 5.5 $ cd 5.5 $ cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5 -DMYSQL_TCP_PORT=3310 -DMYSQL_UNIX_ADDR=/tmp/mysql-devel.sock $ make VERBOSE=1 The Make step fails at 95%, here's the last few lines: [ 95%] Building C object unittest/mysys/CMakeFiles/lf-t.dir/lf-t.c.o cd /Users/derekd/devel/mysql-repo/5.5/unittest/mysys /usr/bin/gcc -DHAVE_CONFIG_H -Wall -O2 -g -DDBUG_OFF -I/Users/derekd/devel/mysql-repo/5.5/include -I/Users/derekd/devel/mysql-repo/5.5/sql -I/Users/derekd/devel/mysql-repo/5.5/regex -I/Users/derekd/devel/mysql-repo/5.5/extra/yassl/include -I/Users/derekd/devel/mysql-repo/5.5/unittest/mytap -o CMakeFiles/lf-t.dir/lf-t.c.o -c /Users/derekd/devel/mysql-repo/5.5/unittest/mysys/lf-t.c Linking C executable lf-t cd /Users/derekd/devel/mysql-repo/5.5/unittest/mysys /Applications/CMake 2.8-5.app/Contents/bin/cmake -E cmake_link_script CMakeFiles/lf-t.dir/link.txt --verbose=1 /usr/bin/gcc -Wall -O2 -g -DDBUG_OFF -Wl,-search_paths_first -Wl,-headerpad_max_install_names CMakeFiles/lf-t.dir/lf-t.c.o -o lf-t -lpthread ../mytap/libmytap.a ../../mysys/libmysys.a ../../strings/libstrings.a ../../dbug/libdbug.a ../../mysys/libmysys.a ../../dbug/libdbug.a ../../strings/libstrings.a -lz -lpthread /Applications/CMake 2.8-5.app/Contents/bin/cmake -E cmake_progress_report /Users/derekd/devel/mysql-repo/5.5/CMakeFiles [ 95%] Built target lf-t make -f unittest/mysys/CMakeFiles/my_atomic-t.dir/build.make unittest/mysys/CMakeFiles/my_atomic-t.dir/depend cd /Users/derekd/devel/mysql-repo/5.5 /Applications/CMake 2.8-5.app/Contents/bin/cmake -E cmake_depends Unix Makefiles /Users/derekd/devel/mysql-repo/5.5 /Users/derekd/devel/mysql-repo/5.5/unittest/mysys /Users/derekd/devel/mysql-repo/5.5 /Users/derekd/devel/mysql-repo/5.5/unittest/mysys /Users/derekd/devel/mysql-repo/5.5/unittest/mysys/CMakeFiles/my_atomic-t.dir/DependInfo.cmake --color= Dependee /Users/derekd/devel/mysql-repo/5.5/unittest/mysys/CMakeFiles/my_atomic-t.dir/DependInfo.cmake is newer than depender /Users/derekd/devel/mysql-repo/5.5/unittest/mysys/CMakeFiles/my_atomic-t.dir/depend.internal. Dependee /Users/derekd/devel/mysql-repo/5.5/unittest/mysys/CMakeFiles/CMakeDirectoryInformation.cmake is newer than depender /Users/derekd/devel/mysql-repo/5.5/unittest/mysys/CMakeFiles/my_atomic-t.dir/depend.internal. Scanning dependencies of target my_atomic-t make -f unittest/mysys/CMakeFiles/my_atomic-t.dir/build.make unittest/mysys/CMakeFiles/my_atomic-t.dir/build /Applications/CMake 2.8-5.app/Contents/bin/cmake -E cmake_progress_report /Users/derekd/devel/mysql-repo/5.5/CMakeFiles [ 95%] Building C object unittest/mysys/CMakeFiles/my_atomic-t.dir/my_atomic-t.c.o cd /Users/derekd/devel/mysql-repo/5.5/unittest/mysys /usr/bin/gcc -DHAVE_CONFIG_H -Wall -O2 -g -DDBUG_OFF -I/Users/derekd/devel/mysql-repo/5.5/include -I/Users/derekd/devel/mysql-repo/5.5/sql -I/Users/derekd/devel/mysql-repo/5.5/regex -I/Users/derekd/devel/mysql-repo/5.5/extra/yassl/include -I/Users/derekd/devel/mysql-repo/5.5/unittest/mytap -o CMakeFiles/my_atomic-t.dir/my_atomic-t.c.o -c /Users/derekd/devel/mysql-repo/5.5/unittest/mysys/my_atomic-t.c /var/folders/1q/0bfhzdp923vf_856z7dzsncmgn/T//ccROezHa.s:596:suffix or operands invalid for `add' make[2]: *** [unittest/mysys/CMakeFiles/my_atomic-t.dir/my_atomic-t.c.o] Error 1 make[1]: *** [unittest/mysys/CMakeFiles/my_atomic-t.dir/all] Error 2 make: *** [all] Error 2 My machine is: $ uname -a Darwin DDMac 11.1.0 Darwin Kernel Version 11.1.0: Tue Jul 26 16:07:11 PDT 2011; root:xnu-1699.22.81~1/RELEASE_X86_64 x86_64 Thanks for any help. P.S. I have posted this on the Builds/Binary Forums, so I apologize for the duplicate content.
