Nested query issue in MySQL 4.1
Hello all
I have a problem to report. There is a SELECT query that selects data from
three tables according to certain criteria. It goes like this:
SELECT bglavna.T001, bpolja.ID AS ID2, bpotpolja.TEKST
FROM (bglavna INNER JOIN bpolja ON bglavna.ID=bpolja.FK) INNER JOIN
bpotpolja ON bpolja.ID=bpotpolja.FK
WHERE bpolja.TAG = '200' AND bpotpolja.SUBF = 'a' AND bglavna.ID IN (...);
"..." is replaced by a subquery that works perfectly and goes like this:
SELECT bglavna.ID FROM (bglavna INNER JOIN bpolja ON bglavna.ID = bpolja.FK)
INNER JOIN bpotpolja ON bpolja.ID = bpotpolja.FK WHERE bpolja.TAG = '700'
AND bpotpolja.SUBF = 'a' AND bpotpolja.TEKST = 'kishon'
As it can be seen, two very similar queries executed on same tables.
Subquery works perfectly and gives the results immediately. But when i put
it in the first query, disk activity does not stop at all and I need to kill
the process. Then I execute subquery, gather results and separate them with
comma(", ") and put them in the first query and everything works perfectly.
So, first query works good when results are explicitly given, and second
query works fine all the time, but when I join them together, everything
stops functioning. I indexed almost every column but it did not help. These
kind of queries allways worked, I don't know what could have happened. Every
sugestion is highly appreciated.
Thank you
Bruno
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dumping mysql databases that have BLOB column
Greetings Does anyone know the exact syntax to dump all mysql databases, but some of them have blob columns. When I execute mysqldump --all-databases mysqldump doesn't work properly whein it reaches these blobs. So I need only syntax to backup everything and restore everything back without having problems with escape characters and so on. Thank you all in advance Bruno - 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
MySQL BLOB fields and large binary data
Greetings Seemingly MySQL has a persistant problem that I could not solve whatever I tried to do. It seems that it is impossible to send large amount of binary data to mysql's BLOB field. It is written in manual that 4.0x versions do not have limit for max_allowed_packet. It is also written that bugs regarding that problem are corrected in 4.08. However, I can not send a file to blob field that is larger than 4-5 MB. I use ADO 2.6 and recommended STREAM object for inserting binary data. max_allowed_packet is very large, 60-100MB, database has one table without records, MySQL is 4.08 with its 128 MB of ram, and other 128 is for windows 2000 system. Wait timeout is also very big. Table type is InnoDB. But error message persists: MySQL Server has gone away. MyODBC is 3.51.05. This is recent data regarding this problem in documentation: You will also get a lost connection if you are sending a packet >= 16M if your client is older than 4.0.8 and your server is 4.0.8 and above, or the other way around. One thing that I do not understand is client's version. I presume that it referes to mysql.exe, but I access server using VB application and MyODBC. Has anyone solved this problem. It is, I'd say "stupid", that these kind of things can not be done with such robust database system. I like MySQL very much, but my whole project depends on this situation and I'll seriously think about using some other RDBMS if I can't solve this problem. Thank you all in advance Bruno Croatia - 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
Memory allocation error in MySQL
Greetings Recently I have a great problem. My whole project depends on it and some people are not satisfied with my aplication because of it. I use database for storing into InnoDB tables word documents into BLOBs, and some other information about docs into standard column types. However, after a database file has grown to 60 MB everything started working very slow and MySQL server (4.0.4 max) reports to my VB application Memory Allocation Error when I try to insert a document of 16 mb size. I have not found this type of error anywhere in documentation or on news. I use ADO and stream object for inserting binary data. Server is 1100 mhz celeron with 256 mb of RAM. That should be enough for database of 60-70 mbytes. Please help me. Thank you in advance Bruno - 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
UNICODE in MySQL
Greetings all I was wandering whether MySQL (4.0.4 or higher) in combination with InnoDB support UNICODE. If I store central European characters using MyODBC from VB, am I sure that they are stored in UNICODE in InnoDB table file and that they can be returned in the same form on every machine? Thank you all in advance Bruno - 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
MySQL subquery that works
Greetings! I am aware that MySQL does not support subqueries. I have a 3 gb large database of bibliographic records. Database is designed by a model that is accepted by almost every library system, but there is one problem. When searching that database, nested queries are needed and there's a difference between mysql and every other database system. Now, when I linked these tables to Access database and used JET SQL everything was working very nice and nested queries worked too because Access Database supports them. Speed is impressive. The question is what does Access do in order to perform subqueries on MySQL via MyODBC driver since it is obvious that they work? What can I do, but I do not want to use access database as a layer between my application and mysql server? I use Visual Basic for accessing mysql server. Thank You very much Bruno - 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
MySQL and UPDATE query
Greetings I have problems executing very simple UPDATE query. I do not know whether it is a bug or not, but it works in Access and also works with mySQL, but only from access using linked tables. Query is as followes: UPDATE Table_name SET Field_name = Left(Field_name, 4) & 'a' & Right(Field_name, 4); This query should change every Field_name's 5'th character to 'a'. One more thing - every record in this table has fixed size value so every value is a string 10 characters long. When using access database or mysql database over access everything goes well, but when using VB and MyODBC or just mysql client, every Field_name's value bocomes 0 (zero). What did I do wrong? Thank you all Bruno Batarelo - 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
MySQL BLOB & Visual Basic
Greetings. I try to save 7MB large binary string to mysql blob field in the latest mysql server. I use innodb tables. Error I receive from myodbc driver says: Memory allocation error. Here is my.ini extract: set-variable=max_allowed_packet=30M set-variable=wait_timeout=10 set-variable = innodb_buffer_pool_size=30M set-variable = innodb_additional_mem_pool_size=15M Everything should work just fine but I do not know what's wrong. Does anyone have an idea? If this is not solvable, how can I expect mysql to receive large amounts of data? Thank You for your patience Bruno - 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
MySQL BLOB field - reliable or NOT?
I belive that I have a little bit bigger problem with MySQL, and for now, no one told me a solution. Problem is with BLOB fields. That type of field behaves OK if I use INSERT INTO statement. But the problem is that it is impossible to use INSERT INTO when I need to send a string full of binary characters I.E. picture, EXE etc. The only solution is to open that kind of file, place it in a variable, use ADO and with help of .AddNew mthod to write that variable to the database. Result: Multiple-step operation generated errors - error. Have You ever met this kind of problem. I have noticed that BLOB fields are not so attractive to work with. Are they reliable enough for work because whenever I try to work with this data type, I always have difficulties in combination with ADO, and seemingly, ADO has not got problems in work. If You can help me, I'd appreciate it. Bruno - 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
BLOB and ADO
Greetings There is a persistant problem while trying to insert a string from within VB 6.0 application to the LONGBLOB field. I use ADO and code for adding new record is as followes: TabelaTekst.AddNew TabelaTekst!Polje = BinaryString TabelaTekst.Update and error is: "Multiple-step operation generated errors. Check each status value." I do not know what to do. I use MySQL 4.01 and MyODBC 3.51.03. It occures only with this field data type. I would appreciate any help since I'm in big trouble if I do not solve this. Thank You all. Bruno - 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
Delete
Greetings In the comments section (delete speed) somebody wrote that that it is much faster to delete results of previously executed select query. How can I do that when delete from..where in(select query); is not possible? Regards Bruno - 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
BLOB field and ADO
Greetings! Has anyone ever had the following problem with ADO ODBC connection: I create a table with a BLOB field. From MySQL shell I update that field using insert into statement and all works perfectly. But when I try to execute that query from VB usnig ADO and ODBC connection (MyODBC 2.50 or 3.51) it results with a new record, but the value in the BLOB field is always Null. Notice that it only happens with BLOB fields in combination with ODBC. Thank You Bruno - 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
Backslash BUG?
Greetings
I use latest stabile version of MySQL. Please, try to create temp table or
normal table and from mysql.exe try to insert a backslash symbol into a
field. Here is an example:
Enter password: **
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 441 to server version: 3.23.49-max
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> \u elba
Database changed
mysql> create temporary table TTable(Field1 mediumtext);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into ttable (Field1) values ('\');
'>
'>
'>
'>
That means that query ok message is skipped. The same problem occur if I try
the same operation with blob data type. Please report me about this.
Bruno
-
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
