Table doesn't exist from stored function
Hi, When I try to run this function, I receive ERROR 1146 (42S02): Table 'gi2.meta' doesn't exist CREATE FUNCTION get_version() RETURNS INT UNSIGNED BEGIN DECLARE exist_ TINYINT; SELECT COUNT(*) INTO exist_ FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'GI2' AND TABLE_NAME = 'Meta' LIMIT 1; IF (0 exist_) THEN BEGIN DECLARE ver_ INT UNSIGNED; SELECT SUBSTRING(Version, 14, LENGTH(Version) - 15) INTO ver_ FROM GI2.Meta LIMIT 1; IF (ver_ = 65) THEN RETURN 5; ELSE RETURN 0; END IF; END; END IF; RETURN 1; END; I can't see why I am receiving this error, as I am checking if the table exists before the select. If this is a limitation in MySQL, any ideas how to circumvent it? Thanks ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with new mysql versions. (They hang)
Hi, I am running relatively complex queries to analyse a dataset. I have query execution times of around half an hour with version 5.27 I tried both version 5.33 and 5.34, and experienced the following: The first few queries ar run faster than with version 5.27, but after a while a query remain hanging. Mysqld is totally using one of the processors. The strange thing is that the load moves from one processor to the other. But this might be some Windows weirdness. I know about the undecideability of the stopping problem, but I find the increase of the execution time from approx 30 minutes to more than 16 hours pretty unprobable. Anybody experienced similar things with these versions? Any idea how to debug/circumvent this problem? Beta patches? Anyone? The speed increase I experienced with the first few queries would be nice to have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql server has gone away
Subject: mysql server has gone away do u knw any othr circumstances in which such an error occurs? n whts the best poss soln here- to do a reconnect If you are using version 5.* and get a FUNCTION does not exist error before loosing the connection, than patch your source as described in bug report #24572, and recompile. ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIQUE KEY vs NULLs
Hi, I have an InnoDB table similar to this: CREATE TABLE Target (IMSI VARCHAR(15) ASCII, IMEI VARCHAR(15) ASCII, UNIQUE KEY (IMSI, IMEI)); After playing a bit with it, I managed to add duplicate records, if one of the fields was a NULL: +-+-+ | IMSI| IMEI| +-+-+ | NULL| 35195600126418 | | NULL| 35195600126418 | +-+-+ Is this a bug, or a feature? :-) If it is a feature, than how can I assure uniqueness for a table in a sense that won't allow such duplicates? Thx ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UNIQUE KEY vs NULLs
From: Dan Buettner [mailto:[EMAIL PROTECTED] This is a feature - a NULL value is an undefined value, therefore two NULL values are not the same. Can be a little confusing but makes sense when you think about it. A UNIQUE index does ensure that non-NULL values are unique; you could specify that your column not accept NULL values. I was afraid to hear something like this. I'd rather not use some invalid value to mark unknown fields. So I went a bit further, and tried to ensure the uniqueness of the null values with a trigger. CREATE TRIGGER Target_Before_Insert BEFORE INSERT ON Target FOR EACH ROW IF NEW.IMSI IS NULL OR NEW.IMEI IS NULL THEN BEGIN DECLARE c_ INT UNSIGNED; SELECT COUNT(*) INTO c_ FROM Target WHERE IMSI = NEW.IMSI AND IMEI = NEW.IMEI; IF c_ THEN SET NEW.Id = NULL; END IF; END; END IF; Here Id is a non NULL field, so setting it to NULL should trigger an error. But when I run an insert where the trigger body would run, then I get the following error. Table 'Target' was not locked with LOCK TABLES I am pretty sure, I don't use LOCK TABLES at all. What is going on here? How can I get rid of this error? Thx ImRe Dan On 12/11/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I have an InnoDB table similar to this: CREATE TABLE Target (IMSI VARCHAR(15) ASCII, IMEI VARCHAR(15) ASCII, UNIQUE KEY (IMSI, IMEI)); After playing a bit with it, I managed to add duplicate records, if one of the fields was a NULL: +-+-+ | IMSI| IMEI| +-+-+ | NULL| 35195600126418 | | NULL| 35195600126418 | +-+-+ Is this a bug, or a feature? :-) If it is a feature, than how can I assure uniqueness for a table in a sense that won't allow such duplicates? Thx ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
odd return value from mysql_stmt_execute()
Hi, It seems that mysql_stmt_execute() sometimes returns 1, and in this case mysql_stmt_error() says that it Lost connection to MySQL server during query Can/should I treate this situation as an ordinary CR_SERVER_LOST, or is this something completely different? Thx ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weird error with stored function
Hi, It seems that the problem was the following: In mysql_stmt_execute() sp_cache_flush_obsolete() is called to flush the statement cache (or something similar). But the proper sp_cache_routines_and_add_tables() functions won't be called from open_tables(), because thd-lex-query_tables_own_last is other than NULL. Inserting the line thd-lex-mark_as_requiring_prelocking(NULL); into Prepared_statement::execute(), after the call to thd-set_n_backup_statement() seems to fix the problem. Now I'd like to know how to submit this patch, and if somebody with more than a few hours of experience with the MySQL codebase can see some possible pitfalls in this fix. Thx ImRe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 23 November 2006 12:14 To: 'mysql@lists.mysql.com' Subject: Weird error with stored function Hi, I am trying to use stored functions through the prepared statement C API. I have one application that issues relatively long running queries, while the other is doing some inserts, updates, and short queries. If the second application somehow calls a stored function (either directly through a prepared statement, or indirectly through a trigger) while first is in the middle of a query, then I receiving an error like this one: FUNCTION gi.get_country does not exist Anybody have seen something like this before? How can I circumvent it? I'm on windows, running MySQL 5.0.27-community-log. I am using InnoDB for my database, and the server SQL mode is sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SU BSTITUTION,ANSI,ONLY_FULL_GROUP_BY Thanks in advance ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird error with stored function
Hi, I am trying to use stored functions through the prepared statement C API. I have one application that issues relatively long running queries, while the other is doing some inserts, updates, and short queries. If the second application somehow calls a stored function (either directly through a prepared statement, or indirectly through a trigger) while first is in the middle of a query, then I receiving an error like this one: FUNCTION gi.get_country does not exist Anybody have seen something like this before? How can I circumvent it? I'm on windows, running MySQL 5.0.27-community-log. I am using InnoDB for my database, and the server SQL mode is sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANS I,ONLY_FULL_GROUP_BY Thanks in advance ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join on nearest value
Hi, I have two tables, both containing a UNIX_TIMESTAMP column. Is it possible to join the tables on the nearest values of these columns? If no, then could anybody show me a query to circumvent this limitation? Thanks ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Hungarian collation
Hi, From: Peter Gulutzan [mailto:[EMAIL PROTECTED] MySQL is looking for an authoritative, official statement which states all the current Hungarian collation rules. According to the Reference Level Description of the hungarian language (ISBN 9634206441 or the hungarian version on line: http://bme-tk.bme.hu/other/kuszob/hangok.htm ) the rules are the following: Apparently http://bme-tk.bme.hu/other/kuszob/hangok.htm is an educational site (something to do with the council of Europe) as opposed to an official standards site, if I'm understanding correctly. Yes. There is a standard about the collation to use in libraries and bibliographies. You can find some data about it here: http://www.mszt.hu/standardsearch/detail.asp?id=007042 The definitive guide of the hungarian language is the A magyar helyesírás szabályai (ISBN 9630577356) issued by the Hungarian Academy of Sciences. An older issue (from 1985) is available for download from here (in Hungarian): http://mek.oszk.hu/01500/01547/index.phtml It describes practically the same collation rules as the Reference Level description, with an additional rule about (latin-like) letters that don't appear in the Hungarian alphabet. This is the following: These letters are sort with their unadorned version, except when all else is equal. In that case they are coming after the native variants I.e.: galamb Gärtner gáz and mosna Mošna - The basic order of the alphabet is a á b c cs d dz dzs e é f g gy h i í j k l ly m n ny o ó ö ő p q r s sz t ty u ú ü ű v w x y z zs - For the short-long vowel pairs (a á, e é, i í, o ó, ö ő, u ú, ü ű) long = short usually, but long short if all else is equal. E.g., kád kar kár kard So far, this seems to be the opinion of a majority, although not everyone describes the rule the same way. If MySQL adopts this rule, SELECT * FROM t WHERE column1 = 'kár'; will not return rows where column1 = 'kar'. But perhaps SELECT * FROM t WHERE column LIKE 'ká%' will return rows where column1 = 'kar' This sounds pretty good to me, especially that in the Hungarian language, the accent marks tend to appear disappear from words according to the suffix. - The long double consonants are sorting as if they would have been expanded. I.e., ggy as gygy, nny as nyny So 'ccs sorts with cscs' is true, i.e. ccs cds I expect that there is no rule which could apply for all LIKE searches. I think, it would be nice (again, because of certain suffix rules) if e.g., LIKE 'cs%' would also match 'ccs' - Composit words are sorted according to word parts. I.e., meggyújt meglát megy meggy I don't see a way to determine what is a composite word. So MySQL would return meglát megy meggy meggyújt I was sort of expecting this :-) ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Speed of DECIMAL
From: Jerry Schwartz [mailto:[EMAIL PROTECTED] What is going slower, INSERT / UPDATES or SELECTS? Complex SELECTs CHAR should make for quite efficient processing, since to a large degree nobody cares what's in there: it just slams the data in, or does a simple byte-by-byte comparison. There is probably hardware support for that kind of operation. Decimal arithmetic, on the other hand, requires more data manipulations. The size of the column probably is outweighed by the more complex data handling. I am doing the following operations: - Joins based on indexed columns - Division by power of 10 - Substring - LIKE comparisons in the form of decimal_column LIKE 'number%' Is any of these especially slow decimals? How would they work with BIGINT(15) UNSIGNED ZEROFILL? No doubt someone whose internals experience is more recent than mine will chime in. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, October 26, 2006 4:37 AM To: mysql@lists.mysql.com Subject: Speed of DECIMAL Hi, I was hoping to speed up my database operations a bit by changing some colums in my database from CHAR(15) ASCII to DEC(15) UNSIGNED ZEROFILL. I was expecting a speedup as DEC(15) is more compact, and this columns are also part of InnoDB indices. In contrary to my expectations, running my test suit took approximately three times as much time, as before. Could anybody give me a probable reason for this slowdown? Thx ImRe P.S.: Ver 5.0.24a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speed of DECIMAL
Hi, I was hoping to speed up my database operations a bit by changing some colums in my database from CHAR(15) ASCII to DEC(15) UNSIGNED ZEROFILL. I was expecting a speedup as DEC(15) is more compact, and this columns are also part of InnoDB indices. In contrary to my expectations, running my test suit took approximately three times as much time, as before. Could anybody give me a probable reason for this slowdown? Thx ImRe P.S.: Ver 5.0.24a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Embedded server with the archive storage engine
Hi, Is it possible to use the embedded server with the archive storage engine (version 5.0.24a)? I tried to build it on windows with VS2005, and encountered a few problems. After I managed to build it, I had an assertion failure in a file close operation. I built libmysqld in the following way: Added ha_archive.cpp to the libmysqld project, and defined HAVE_ARCHIVE_DB in the project settings dialog. Then I had a link error. It seems that this so called development environment didn't managed to define the #define properly. (I searched the source tree, but I was unable to find an #undef for it) So I put an explicite #define at the beginning of handler.cpp (before the first #include). After that, it built. But now I have the assertion. Is there anything else I can do, or it is not supposed to work on the first place. Thanks ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
merging mysql databases
Hi, I am developping some devices that gather and process data using mysql(embedded). Is there any easy way to merge the databases from these devices into a single database? I could easily deal with the semantics of the merge in SQL level. What I don't know is how can my merge/analysis application see the databases copied from the devices. (I'd rather not go through csv exports if possible ...) Any ideas? Thx ImRe P.S.: Ver 5.0.24a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Hungarian collation
From: Peter Gulutzan [EMAIL PROTECTED] MySQL is looking for an authoritative, official statement which states all the current Hungarian collation rules. According to the Reference Level Description of the hungarian language (ISBN 9634206441 or the hungarian version on line: http://bme-tk.bme.hu/other/kuszob/hangok.htm ) the rules are the following: - The basic order of the alphabet is a á b c cs d dz dzs e é f g gy h i í j k l ly m n ny o ó ö ő p q r s sz t ty u ú ü ű v w x y z zs - For the short-long vowel pairs (a á, e é, i í, o ó, ö ő, u ú, ü ű) long = short usually, but long short if all else is equal. E.g., kád kar kár kard - The long double consonants are sorting as if they would have been expanded. I.e., ggy as gygy, nny as nyny - Composit words are sorted according to word parts. I.e., meggyújt meglát megy meggy An alternative collation sometimes used (in libraries, and some dictionaries and lexica) is according to the basic latin alphabet, whit the accented letters having the same value as the not accented. Or anything in between. E.g., honoring the digraphs and the trigraph, but leaving the accents out of the business. I hope this helps. ImRe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Charset for SELECT ... INTO OUTFILE
Hi, I have a database where the database character set is utf-8 and some rows are ascii. I want to save the results of some queries, and SELECT ... INTO OUTFILE looks like an easy way to do it. But I need the output in ucs-2. Is there any way to specify the charset for SELECT ... INTO OUTFILE, or is it always uses the database character set? Thx. ImRe P.S.: Ver 5.0.24a-community-log -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Enums vs prepared statements
Hi,
I try to read enums from a database through the prepared statement API, as a
number.
The enum is defined e.g., as ENUM('Accept', 'Reject', 'Reject_All')
The bind structure is filled in the following way:
buffer_type = MYSQL_TYPE_LONG
buffer points to an uint32_t
buffer_length = 4
length points to an unsigned long
is_null points to a bool
is_unsigned = true
error points to a my_bool
After calling mysql_stmt_fetch() I receive MYSQL_DATA_TRUNCATED, error is
set to 1, and length is set to 4.
Could somebody explain what am I doing wrong? Inserts seem to work woth the
same setup properly, only selects have this problem.
Thx.
ImRe
P.S.: version = 5.0.22
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speed problems (?!)
hi all i've mysql 2.32.52 installed, and there is a table with nearly 2.000.000 records in it (4 field/record). i have 256megs of RAM, and the linux version is RedHat 7.3. i do a simple delete, like: delete from foo_db where foo10; (this is around 15.000 record) and after 30 minutes, still nothing. the load is over 2, minimal disk activity. the filesize is around 300MByte. what could be wrong with this? why is it so slow? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
