large query result caching (using C API)

2003-12-12 Thread Alex E.Wintermann 
MySQL 4.1.0-alpha-max-nt
MySQL C API interface
Visual C++ 6.0

trying some query when cache is on...
it causes ERROR: Lost connection to MySQL sever during query
MySQL cache memory used for _this_ query ~900Kb

but when cache is off or query result size is less than few Kb, everything is OK.

WHY?

additional information:
/* my.ini BEGIN */
[WinMySQLAdmin]
Server=E:/mysql/bin/mysqld-nt.exe
QueryInterval=10
[mysqld]
basedir = e:/mysql/
datadir = e:/mysql/data/
default-character-set=cp1251
key_buffer_size=64M
tmp_table_size=16M
table_cache=256 
sort_buffer=16M
read_buffer_size=1M 
read_rnd_buffer_size=1M
query_cache_size=64M
query_cache_limit=5M
/* my.ini END */

---
Best regards, Alex. mailto:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysql error

2003-12-10 Thread Alex E.Wintermann 
Hello mysql,

MySQL 4.1.0-alpha-max-nt
phpMyAdmin 2.5.4

/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
  `id` int(11) NOT NULL auto_increment,
  `id_tovar_vid` int(11) NOT NULL default '0',
  `name` varchar(100) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  UNIQUE KEY `id` (`id`)
) TYPE=InnoDB CHARSET=cp1251;

INSERT INTO `sp_tovar_vid` VALUES (1, 0, 'string 1', '');
INSERT INTO `sp_tovar_vid` VALUES (2, 1, 'string 2', '');
INSERT INTO `sp_tovar_vid` VALUES (3, 2, 'string 3', '');
INSERT INTO `sp_tovar_vid` VALUES (4, 0, 'string 4', '');

INSERT INTO `sp_tovar_vid` (`id`,`id_tovar_vid`,`name`,`description`) VALUES 
('','0','','');
UPDATE `sp_tovar_vid` SET `id` = '0' WHERE `id` = LAST_INSERT_ID() LIMIT 1 ;
/* END DUMP */

/* BEGIN QUERY */
SELECT  t1.`id`,
CONCAT(if(t3.`name`='','',CONCAT(t3.`name`,' - 
')),if(t2.`name`='','',CONCAT(t2.`name`,' - ')),t1.`name`) AS col1, 
t1.`description` 
FROM`sp_tovar_vid` AS t1, 
`sp_tovar_vid` AS t2, 
`sp_tovar_vid` AS t3 
WHERE   t1.`id_tovar_vid`=t2.`id` AND 
t2.`id_tovar_vid`=t3.`id` AND 
col1'string'
ORDER BY col1;
/* END QUERY */

QUESTION: why ERROR caused: #1054 - Unknown column 'col1' in 'where clause'

result wanted
++--+-+
| id | col1 | description |
++--+-+
|  1 | string 1 | |
|  2 | string 1 - string 2 | |
|  3 | string 1 - string 2 - string 3 | |
|  4 | string 4 | |
++--+-+
4 rows in set (0.00 sec)
/**/  

-- 
Best regards,
 Alex  mailto:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re[2]: Tree-like structure: make it simply

2003-12-05 Thread Alex E.Wintermann 
Hello Matthew,

Friday, December 5, 2003, 2:12:08 AM, you wrote:

thanks, but some more see below.

[skipped]
 I have some table:
 /* BEGIN DUMP */
 CREATE TABLE `sp_tovar_vid` (
   `id` int(11) NOT NULL auto_increment,
   `id_tovar_vid` int(11) NOT NULL default '0',
   `name` varchar(100) NOT NULL default '',
   `description` varchar(255) NOT NULL default '',
   UNIQUE KEY `id` (`id`)
 );

 INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
 INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
 INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
 INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
 INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
 INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
 INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
 INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
 INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
 INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
 INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
 INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
 INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1', '(to subcat.2
 to cat.1)');
 /* END DUMP /

[skipped]
 _QUESTION 2_: how to display table with columns=
 ('parent_category.name' ,'category.name')?
 i tried this:
 /***/
 SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
 AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
 /***/
 How to make it more simply?

 _QUESTION 3_: how to display string:
 subsubcat 1 - subcat.2 - cat.1
 if we have only `id`=18 ?


M use SELECT IF(id=18, true_expression, false expression) FROM table...
M I'm not clear what you mean by subsubcat 1 - subcat.2 - cat.1... minus, or
M some sort of string concatenation?
M true expression might be field1 - field2, or CONCAT(field1, field2, ...)

elucidation: i need to run recurse search to build string shown.
it means: if we have `id`=18, we must build following string after
mentioned recurse search runnnig.

string: subsubcat 1 + subcat 2 + cat.1 + root

it's tree:
/**/
root (`id`=0, `parent`=0)
|--cat.1 (`id`=7, `parent`=0)
| |-subcat 1
| |--subcat 2 (`id`=13, `parent`=7)
| | |-subsubcat 1 (`id`=18, `parent`=13)
| |-subcat 3
|-cat.2
|--cat.3
| |-subcat 1
| |-subcat 2
| |-subcat 3
|-cat.4
|-cat.5
/**/

-- 
Best regards,
 Alexmailto:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Tree-like structure: make it simply

2003-12-04 Thread Alex E.Wintermann 
Hello mysql,

I have some table:
/* BEGIN DUMP */
CREATE TABLE `sp_tovar_vid` (
  `id` int(11) NOT NULL auto_increment,
  `id_tovar_vid` int(11) NOT NULL default '0',
  `name` varchar(100) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  UNIQUE KEY `id` (`id`)
);

INSERT INTO `sp_tovar_vid` VALUES (0, 0, 'root', 'root category');
INSERT INTO `sp_tovar_vid` VALUES (7, 0, 'cat.1', '');
INSERT INTO `sp_tovar_vid` VALUES (8, 0, 'cat.2', '');
INSERT INTO `sp_tovar_vid` VALUES (9, 0, 'cat.3', '');
INSERT INTO `sp_tovar_vid` VALUES (10, 0, 'cat.4', '');
INSERT INTO `sp_tovar_vid` VALUES (11, 0, 'cat.5', '');
INSERT INTO `sp_tovar_vid` VALUES (12, 7, 'subcat 1', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (13, 7, 'subcat 2', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (14, 7, 'subcat 3', '(to cat.1)');
INSERT INTO `sp_tovar_vid` VALUES (15, 9, 'subcat 1', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (16, 9, 'subcat 2', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (17, 9, 'subcat 3', '(to cat.3)');
INSERT INTO `sp_tovar_vid` VALUES (18, 13, 'subsubcat 1', '(to subcat.2
to cat.1)');
/* END DUMP /

_QUESTION 1_: in what way should i run query to restore table with
`id`' like in dump? (`id` is auto_increment)

_QUESTION 2_: how to display table with columns=
('parent_category.name' ,'category.name')?
i tried this:
/***/
SELECT t1.`name`,t2.`name` FROM `sp_tovar_vid` AS t1, `sp_tovar_vid`
AS t2 WHERE t1.`id`=t2.`id_tovar_vid` ORDER BY t1.`name`,t2.`name`;
/***/
How to make it more simply?

_QUESTION 3_: how to display string:
subsubcat 1 - subcat.2 - cat.1
if we have only `id`=18 ?

-- 
Best regards,
 Alex  mailto:[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]