MySQL Technology
The main discussion forum for MySQL. Please use specialized forums for specific topics.
Updated: 2 hours 32 min ago
Sorting MPTT Tree by number of belonging entries in different table (1 reply)
Hi,
I'm having a bit of a hard time figuring out the correct solution to this problem. I have a `Location` table which is organized as an MPTT tree. Each Location hasMany Places associated with it (foreign key `location_id` in `Places` table). Each Place has a HABTM (many-many) relationship with `Categories`, which is also an MPTT tree. In my application I obviously want to list Places which belong to either a specific Location or one of it's children and/or a specific Category or one of it's children. All that works great, no worries there. UI-wise Categories take precedence over Locations, i.e. when a Category is selected, I only want to display Locations which have Places which are in this Category (filtering empty Locations). That works great too.
My problem now is that I also want to sort the Locations according to the number of Places associated with them, taking into account the currently selected Category. What I need is:
SELECT Locations
WHERE Places in this Location (or children of it) are also in a specific Category (or children of it)
ORDER BY number of Places in this Location, after the above filters have been applied
My current query looks like this:
SELECT `Location`.*
FROM
`locations` AS `Location`,
`locations` AS `Child`,
`places` AS `Place`
LEFT JOIN `category_places` AS `Category`
ON `Category`.`place_id` = `Place`.`id`
WHERE
`Child`.`lft` BETWEEN `Location`.`lft` AND `Location`.`rght`
AND `Child`.`id` = `Place`.`location_id`
AND `Category`.`category_id` IN ( /* ids from a separate query */ )
GROUP BY `Location`.`id`
For internal reasons I already got the Category ids from a separate query, so I'm only joining to the categories_places HABTM table and filter on category_ids here. Anyway, this gets me my Locations perfectly fine, except that I can't figure out how to correctly sort them by COUNT(`Place`.`id`).
I know this is a pretty specialized question, but any input would be appreciated. :)
Chrs,
Dav
I'm having a bit of a hard time figuring out the correct solution to this problem. I have a `Location` table which is organized as an MPTT tree. Each Location hasMany Places associated with it (foreign key `location_id` in `Places` table). Each Place has a HABTM (many-many) relationship with `Categories`, which is also an MPTT tree. In my application I obviously want to list Places which belong to either a specific Location or one of it's children and/or a specific Category or one of it's children. All that works great, no worries there. UI-wise Categories take precedence over Locations, i.e. when a Category is selected, I only want to display Locations which have Places which are in this Category (filtering empty Locations). That works great too.
My problem now is that I also want to sort the Locations according to the number of Places associated with them, taking into account the currently selected Category. What I need is:
SELECT Locations
WHERE Places in this Location (or children of it) are also in a specific Category (or children of it)
ORDER BY number of Places in this Location, after the above filters have been applied
My current query looks like this:
SELECT `Location`.*
FROM
`locations` AS `Location`,
`locations` AS `Child`,
`places` AS `Place`
LEFT JOIN `category_places` AS `Category`
ON `Category`.`place_id` = `Place`.`id`
WHERE
`Child`.`lft` BETWEEN `Location`.`lft` AND `Location`.`rght`
AND `Child`.`id` = `Place`.`location_id`
AND `Category`.`category_id` IN ( /* ids from a separate query */ )
GROUP BY `Location`.`id`
For internal reasons I already got the Category ids from a separate query, so I'm only joining to the categories_places HABTM table and filter on category_ids here. Anyway, this gets me my Locations perfectly fine, except that I can't figure out how to correctly sort them by COUNT(`Place`.`id`).
I know this is a pretty specialized question, but any input would be appreciated. :)
Chrs,
Dav
How to store large amounts of similar data (2 replies)
Hi Everyone!
I'm trying to design a database and I can't figure out what would be the best design.
I have only 2 types of objects: "Object" and "Attributes". An Object can have multiple Attriibute-s. There's a hierarchy which defines the relationship between numerous Objects.
Now, my problem is that the application will have on average about 1 million Object entries. Ideally, I would have the Objects table that would link a certain ObjectID to an AttributeID in the Attributes table. Objects should be able to contain other objects.
However, if I store 1 million rows in a table, the queries become too slow. A simple 'SELECT * From Objects' returns all the results in 0.6sec (1 client is using the database). Unfortunately, 0.6 seconds is unacceptable for this application, and thus I am searching for ideas and perhaps you guys will be able to help. I was looking into parallel connections, table partitioning and clusters, but I can't put together a solid model for storing large amounts of similar Objects.
If you could come up with an advice, I will greatly appreciate it!
Thanks in advance!
I'm trying to design a database and I can't figure out what would be the best design.
I have only 2 types of objects: "Object" and "Attributes". An Object can have multiple Attriibute-s. There's a hierarchy which defines the relationship between numerous Objects.
Now, my problem is that the application will have on average about 1 million Object entries. Ideally, I would have the Objects table that would link a certain ObjectID to an AttributeID in the Attributes table. Objects should be able to contain other objects.
However, if I store 1 million rows in a table, the queries become too slow. A simple 'SELECT * From Objects' returns all the results in 0.6sec (1 client is using the database). Unfortunately, 0.6 seconds is unacceptable for this application, and thus I am searching for ideas and perhaps you guys will be able to help. I was looking into parallel connections, table partitioning and clusters, but I can't put together a solid model for storing large amounts of similar Objects.
If you could come up with an advice, I will greatly appreciate it!
Thanks in advance!
[del] mysql-daemon doesn't start anymore (no replies)
(sorry for disturbing. Problem now sent to bugs)
Question, how to manage more than one article in a table (1 reply)
Hey @all
So, I try to explain, what I have.
Till yet, I have had two order tables, but I got many problems with getting the right invoice number, select all of my articles, and so on...
So, I thought, I bring the two tables together, to only one table. The idea behind this, is that I only have one table, which should be referenced to a child table with all my articles, so that the searching of articles will be much more easier.
But I have the problem, that my brain is overheating, because I don't know how to realise it.
I have (as I sayed above) two different order tables. - Let's say: Orders A & Orders B
The Orders A can contain two articles (one for the right side & one for the left side) - One Order can have as maximum these two articles (1:1 relation)
The Oders B contains "only" one articel, BUT One order can have 'n' articles stored. (1:n relation)
How do I have to design my tables correct, so that it will be correct!?
THX
So, I try to explain, what I have.
Till yet, I have had two order tables, but I got many problems with getting the right invoice number, select all of my articles, and so on...
So, I thought, I bring the two tables together, to only one table. The idea behind this, is that I only have one table, which should be referenced to a child table with all my articles, so that the searching of articles will be much more easier.
But I have the problem, that my brain is overheating, because I don't know how to realise it.
I have (as I sayed above) two different order tables. - Let's say: Orders A & Orders B
The Orders A can contain two articles (one for the right side & one for the left side) - One Order can have as maximum these two articles (1:1 relation)
The Oders B contains "only" one articel, BUT One order can have 'n' articles stored. (1:n relation)
How do I have to design my tables correct, so that it will be correct!?
THX
Data Privacy at Any Cost (no replies)
Transaction data is a major cause of concern in any organization. Being a financial institution it was a constant challenge to protect sensitive data that usually leaks out from the GUI. To top it was the issue of ever growing application database that triggered the red alarm almost every other day. We found the perfect fix in the form of ‘Data Privacy Pack for Oracle E-Business Suite’ – a complete centralized manager for data classification and security aspects. It was worth every penny spent. For more information visit
http://www.solix.com/data_privacy_oracle_ebusiness_suite.htm
http://www.solix.com/data_privacy_oracle_ebusiness_suite.htm
logic for storing a particular data into databse (1 reply)
Hi guys.........here is my problem........
Suppose I have two questions(in the below given format) and I have to store them in databse.....with some meta info attached with each question........and during retrieval i can choose to retrieve either one of the questions or both..it depends:
Q.1 Which is the correct structure of benzene?
A) <structure-1> B) <structure-2> C) <structure-3> D) <structure-4>
Q.2 For the following equation:
H2SO4 + NAOH => Na2SO4 + H20 //////the equation would be in textbook format
What is the energy consumed in the process?
Now the above is my data.....i am in dilemma as to how to store these questions so that it enables question based retrieval..the problem is due to the images and equations shown above.....should I classify equations and tuctures as images and other data as text??? I yes then how do i store each of these images????
Thanks is advance
Suppose I have two questions(in the below given format) and I have to store them in databse.....with some meta info attached with each question........and during retrieval i can choose to retrieve either one of the questions or both..it depends:
Q.1 Which is the correct structure of benzene?
A) <structure-1> B) <structure-2> C) <structure-3> D) <structure-4>
Q.2 For the following equation:
H2SO4 + NAOH => Na2SO4 + H20 //////the equation would be in textbook format
What is the energy consumed in the process?
Now the above is my data.....i am in dilemma as to how to store these questions so that it enables question based retrieval..the problem is due to the images and equations shown above.....should I classify equations and tuctures as images and other data as text??? I yes then how do i store each of these images????
Thanks is advance
Switch Two Fields in an Update Paradox (2 replies)
I have a database table where some data went into the wrong fields, so now I need to switch them to the right fields. But there is a bit of a paradox when you try to do this. If you try this command:
update test
set col1=col2, col2=col1
where id=1
Then col1 will be equal to col2 and then col2 will be equal to col2, so both columns will be set to the value of col2, when I really wanted to switch the values.
It would not be practical to reimport the data correctly because of the effect it would have on the rest of the system.
Thanks for any help you can provide.
update test
set col1=col2, col2=col1
where id=1
Then col1 will be equal to col2 and then col2 will be equal to col2, so both columns will be set to the value of col2, when I really wanted to switch the values.
It would not be practical to reimport the data correctly because of the effect it would have on the rest of the system.
Thanks for any help you can provide.
incomplete results in subquery (no replies)
Hello everyone,
I have the following query.
select a.*, b.exibue, b.almac,
--begin problematic subquery
(select CONCAT(CAST(e.dtoprd as CHAR), ',', CAST(e.dtoprt AS CHAR), ',', CAST(e.dtoprs AS CHAR)) from alfpreal e
where CAST(e.cod1 AS UNSIGNED) <= CAST(CONCAT(a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4) AS UNSIGNED)
and CAST(e.cod2 AS UNSIGNED) >= CAST(CONCAT(a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4) AS UNSIGNED)
) as prigen
-- end problematic subquery
FROM minimart_copy a left join minimaexi b
using (NUAR1, NUAR2, NUAR3, NUAR4, NUAR5)
left join forzona3 c on (b.almac = c.cventa)
WHERE
(CONCAT(NUAR1, NUAR2, NUAR3, NUAR4) like '$BusNum%' or
MATCH (nomart) AGAINST ('+LAVABO +BLANCO +ALOA' IN BOOLEAN MODE))
AND (almac = 29 or almac = 28 or almac = 7600)
ORDER BY a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4, a.NUAR5;
As you see, the third result parameter ('as prigen') should return three different columns from table "alfpreal" concatenated in a single string in the form of "val1, val2, val3".
The query works, but the subquery return only "val1, val2," and that's it. If I take out that query and execute it by itself, it wors as inteded. But whenever I run it inside the larger query, it fails again. All the other results work as intended.
I'm using MySQL 5.022.
Any clue of what's going on?
Thanks for any help anyone can provide.
Regards,
I.-
I have the following query.
select a.*, b.exibue, b.almac,
--begin problematic subquery
(select CONCAT(CAST(e.dtoprd as CHAR), ',', CAST(e.dtoprt AS CHAR), ',', CAST(e.dtoprs AS CHAR)) from alfpreal e
where CAST(e.cod1 AS UNSIGNED) <= CAST(CONCAT(a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4) AS UNSIGNED)
and CAST(e.cod2 AS UNSIGNED) >= CAST(CONCAT(a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4) AS UNSIGNED)
) as prigen
-- end problematic subquery
FROM minimart_copy a left join minimaexi b
using (NUAR1, NUAR2, NUAR3, NUAR4, NUAR5)
left join forzona3 c on (b.almac = c.cventa)
WHERE
(CONCAT(NUAR1, NUAR2, NUAR3, NUAR4) like '$BusNum%' or
MATCH (nomart) AGAINST ('+LAVABO +BLANCO +ALOA' IN BOOLEAN MODE))
AND (almac = 29 or almac = 28 or almac = 7600)
ORDER BY a.NUAR1, a.NUAR2, a.NUAR3, a.NUAR4, a.NUAR5;
As you see, the third result parameter ('as prigen') should return three different columns from table "alfpreal" concatenated in a single string in the form of "val1, val2, val3".
The query works, but the subquery return only "val1, val2," and that's it. If I take out that query and execute it by itself, it wors as inteded. But whenever I run it inside the larger query, it fails again. All the other results work as intended.
I'm using MySQL 5.022.
Any clue of what's going on?
Thanks for any help anyone can provide.
Regards,
I.-
PROPOSALS to make MySQL better (no replies)
Improvement of UPDATE syntax.
-- 1a. An example of current correct syntax: UPDATE forum_topic AS ft SET ft.message_last_id = (SELECT MAX(fm.id) FROM forum_message AS fm WHERE fm.pid = ft.id), ft.message_count = (SELECT COUNT(fm.id) FROM forum_message AS fm WHERE fm.pid = ft.id) -- 1b. The same example of alternative proposal: UPDATE forum_topic AS ft SET (ft.message_last_id, ft.message_count) = (SELECT MAX(fm.id), COUNT(fm.id) FROM forum_message AS fm WHERE fm.pid = ft.id)
-- 1a. An example of current correct syntax: UPDATE forum_topic AS ft SET ft.message_last_id = (SELECT MAX(fm.id) FROM forum_message AS fm WHERE fm.pid = ft.id), ft.message_count = (SELECT COUNT(fm.id) FROM forum_message AS fm WHERE fm.pid = ft.id) -- 1b. The same example of alternative proposal: UPDATE forum_topic AS ft SET (ft.message_last_id, ft.message_count) = (SELECT MAX(fm.id), COUNT(fm.id) FROM forum_message AS fm WHERE fm.pid = ft.id)
sort aborted (no replies)
Hi~.
I have a problem.
I installed 4.1.22-standard-log
It works as usual, but It sometimes generates errors.
"[ERROR] /db/mysql/bin/mysqld: Sort aborted"
And then, sometimes Mysqld restart by memory problem.
I'd like to why the error happened.
=---err file.
080831 16:49:06 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 17:04:45 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 19:05:53 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 19:34:20 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 20:01:21 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 21:09:08 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 22:53:42 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 23:49:46 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 0:30:43 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 3:12:41 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 4:38:28 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 5:07:35 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 7:59:36 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 8:05:47 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 8:11:13 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 9:37:39 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 9:40:21 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 11:19:21 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 11:50:49 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 12:54:46 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 13:36:36 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 13:52:27 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 21:53:13 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 21:53:13 [ERROR] /db/mysql/bin/mysqld: Sort aborted
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=134217728
read_buffer_size=3141632
max_used_connections=200
max_connections=200
threads_connected=179
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1359070 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x35c03aa8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x2c7a46c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81263b4
0x830c80b
0x830c1c3
0x81a0b2b
0x8171b34
0x81d5873
0x816d1a9
0x816bdf6
0x8165970
0x815d6d8
0x81d64eb
0x81d7033
0x81d6d44
0x815462b
0x8138f94
0x813e2a6
0x8137c6a
0x81378de
0x81370d5
0xa323cc
0x99cc3e
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9c24788 = SELECT /* rss.conf (SELECT_CHANNEL_OTHER_NOTREADYN_FIRST_VISIT_ALARMY) */ a.name, a.chid, b.itemid, b.link,b.title,date_format(b.itempub, '%Y.%m.%d %H:%i:%s'),b.author,b.description , b.firstimgurl, b.category,a.fldid,a.openyn^M
FROM ^M
(^M
select x.chid,u.rsslevel, u.insert_time,u.name,u.openyn,u.fldid,u.chid,u.blogid,x.itempub ^M
from user_channel u , channel x ,(select fldid from folder where blogid='07rbl' and openyn='Y' union select 0) f^M
where u.blogid='07rbl' and u.chid=x.chid and u.fldid=f.fldid^M
order by x.itempub desc limit 10^M
) a, item_alarmy b^M
WHERE a.chid=b.chid^M
AND a.blogid='07rbl'^M
AND a.openyn='Y'^M
AND b.update_time >= a.insert_time^M
AND ifnull(b.rsslevel, 0) <= ifnull(a.rsslevel, 0)^M
ORDER by b.itempub desc, b.itemid desc^M
limit 10
thd->thread_id=144515
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
080901 21:55:04 mysqld restarted
080901 21:55:05 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
080901 21:55:05 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 26 3795066713.
InnoDB: Doing recovery: scanned up to log sequence number 26 3800309248
InnoDB: Doing recovery: scanned up to log sequence number 26 3805552128
InnoDB: Doing recovery: scanned up to log sequence number 26 3810795008
InnoDB: Doing recovery: scanned up to log sequence number 26 3816037888
InnoDB: Doing recovery: scanned up to log sequence number 26 3821280768
InnoDB: Doing recovery: scanned up to log sequence number 26 3826523648
InnoDB: Doing recovery: scanned up to log sequence number 26 3831766528
InnoDB: Doing recovery: scanned up to log sequence number 26 3837009408
InnoDB: Doing recovery: scanned up to log sequence number 26 3842252288
InnoDB: Doing recovery: scanned up to log sequence number 26 3847495168
InnoDB: Doing recovery: scanned up to log sequence number 26 3852738048
InnoDB: Doing recovery: scanned up to log sequence number 26 3852909009
080901 21:55:06 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 6358173, file name /data/mysql/rssudb1-bin.000933
080901 21:56:55 InnoDB: Flushing modified pages from the buffer pool...
080901 21:56:57 InnoDB: Started; log sequence number 26 3852909009
/db/mysql/bin/mysqld: ready for connections.
Version: '4.1.22-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)
I have a problem.
I installed 4.1.22-standard-log
It works as usual, but It sometimes generates errors.
"[ERROR] /db/mysql/bin/mysqld: Sort aborted"
And then, sometimes Mysqld restart by memory problem.
I'd like to why the error happened.
=---err file.
080831 16:49:06 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 17:04:45 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 19:05:53 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 19:34:20 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 20:01:21 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 21:09:08 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 22:53:42 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080831 23:49:46 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 0:30:43 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 3:12:41 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 4:38:28 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 5:07:35 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 7:59:36 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 8:05:47 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 8:11:13 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 9:37:39 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 9:40:21 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 11:19:21 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 11:50:49 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 12:54:46 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 13:36:36 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 13:52:27 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 21:53:13 [ERROR] /db/mysql/bin/mysqld: Sort aborted
080901 21:53:13 [ERROR] /db/mysql/bin/mysqld: Sort aborted
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=134217728
read_buffer_size=3141632
max_used_connections=200
max_connections=200
threads_connected=179
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1359070 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0x35c03aa8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x2c7a46c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81263b4
0x830c80b
0x830c1c3
0x81a0b2b
0x8171b34
0x81d5873
0x816d1a9
0x816bdf6
0x8165970
0x815d6d8
0x81d64eb
0x81d7033
0x81d6d44
0x815462b
0x8138f94
0x813e2a6
0x8137c6a
0x81378de
0x81370d5
0xa323cc
0x99cc3e
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9c24788 = SELECT /* rss.conf (SELECT_CHANNEL_OTHER_NOTREADYN_FIRST_VISIT_ALARMY) */ a.name, a.chid, b.itemid, b.link,b.title,date_format(b.itempub, '%Y.%m.%d %H:%i:%s'),b.author,b.description , b.firstimgurl, b.category,a.fldid,a.openyn^M
FROM ^M
(^M
select x.chid,u.rsslevel, u.insert_time,u.name,u.openyn,u.fldid,u.chid,u.blogid,x.itempub ^M
from user_channel u , channel x ,(select fldid from folder where blogid='07rbl' and openyn='Y' union select 0) f^M
where u.blogid='07rbl' and u.chid=x.chid and u.fldid=f.fldid^M
order by x.itempub desc limit 10^M
) a, item_alarmy b^M
WHERE a.chid=b.chid^M
AND a.blogid='07rbl'^M
AND a.openyn='Y'^M
AND b.update_time >= a.insert_time^M
AND ifnull(b.rsslevel, 0) <= ifnull(a.rsslevel, 0)^M
ORDER by b.itempub desc, b.itemid desc^M
limit 10
thd->thread_id=144515
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
080901 21:55:04 mysqld restarted
080901 21:55:05 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
080901 21:55:05 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 26 3795066713.
InnoDB: Doing recovery: scanned up to log sequence number 26 3800309248
InnoDB: Doing recovery: scanned up to log sequence number 26 3805552128
InnoDB: Doing recovery: scanned up to log sequence number 26 3810795008
InnoDB: Doing recovery: scanned up to log sequence number 26 3816037888
InnoDB: Doing recovery: scanned up to log sequence number 26 3821280768
InnoDB: Doing recovery: scanned up to log sequence number 26 3826523648
InnoDB: Doing recovery: scanned up to log sequence number 26 3831766528
InnoDB: Doing recovery: scanned up to log sequence number 26 3837009408
InnoDB: Doing recovery: scanned up to log sequence number 26 3842252288
InnoDB: Doing recovery: scanned up to log sequence number 26 3847495168
InnoDB: Doing recovery: scanned up to log sequence number 26 3852738048
InnoDB: Doing recovery: scanned up to log sequence number 26 3852909009
080901 21:55:06 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 6358173, file name /data/mysql/rssudb1-bin.000933
080901 21:56:55 InnoDB: Flushing modified pages from the buffer pool...
080901 21:56:57 InnoDB: Started; log sequence number 26 3852909009
/db/mysql/bin/mysqld: ready for connections.
Version: '4.1.22-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)
[ANN] PBXT Beta Version Released! (no replies)
Hi All!
I am pleased to announce that the Beta version of PBXT has just been released. PBXT is a fast, lightweight, transactional storage engine for MySQL. More information is available at http://www.primebase.org.
You can download the source code at http://www.primebase.org/download. I have also updated the documentation for this version (http://www.primebase.org/documentation).
Configuring and building the engine is easier than ever now. To configure PBXT all you have to do is specify the path to the MySQL source code tree (after building MySQL), for example:
./configure --with-mysql=/home/foo/mysql/mysql-5.1.26-rc
The PBXT configure command will retrieve all required options from the MySQL build. For example whether to do a debug or optimized build and where to install the plugin are determined automatically, depending on how you configured MySQL.
This was a source of some mistakes when building the plugin, so I think it is really cool!
My thanks to all who have tested PBXT so far. If you haven't tried out the engine yet, please do!
Let me know if you have any comments or questions, I'll be glad to help.
Best regards,
Paul
--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com
I am pleased to announce that the Beta version of PBXT has just been released. PBXT is a fast, lightweight, transactional storage engine for MySQL. More information is available at http://www.primebase.org.
You can download the source code at http://www.primebase.org/download. I have also updated the documentation for this version (http://www.primebase.org/documentation).
Configuring and building the engine is easier than ever now. To configure PBXT all you have to do is specify the path to the MySQL source code tree (after building MySQL), for example:
./configure --with-mysql=/home/foo/mysql/mysql-5.1.26-rc
The PBXT configure command will retrieve all required options from the MySQL build. For example whether to do a debug or optimized build and where to install the plugin are determined automatically, depending on how you configured MySQL.
This was a source of some mistakes when building the plugin, so I think it is really cool!
My thanks to all who have tested PBXT so far. If you haven't tried out the engine yet, please do!
Let me know if you have any comments or questions, I'll be glad to help.
Best regards,
Paul
--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com
Suddenly getting Error 1060 Duplicate Column Name errors on Select queries (no replies)
Hi,
This is weird, I have several queries which haven't been changed for months, which have worked perfectly well (example query below), however are suddenly throwing 'duplicate column name' errors - error 1060.
I haven't changed the queries or the installation of MySQL for months (running version 5.0.22)
I know the subquery returns 2 columns both called 'ID' (as both the Locations and Control tables both have a column called ID and I'm selecting all columns from both tables using the *, however this didn't used to bother MySQL, but now it suddenly does without any apparent reason.
Can anyone shed any light on why this behaviour has changed without any change to the installation or queries being run!?
Many thanks,
Chris.
Example query...
SELECT a.* FROM
(SELECT Locations.*,Locb.Company AS Parn,Control.*,date_format(Control.Completed,'%d/%m/%y') AS cmpl,Control.ID AS cid,Qset.Type,Users.Name FROM Locations LEFT JOIN Locations AS Locb ON Locations.Parent=Locb.ID LEFT JOIN Control ON Locations.ID=Control.Location AND Control.Qset IN (4,18,24,155) AND Control.Status='Released' AND Control.Completed > (curdate()-interval 365 day) LEFT JOIN Qset ON Control.Qset=Qset.ID LEFT JOIN Users ON Control.User=Users.ID WHERE Locations.Org=6 and Locations.Parent<>0 and Locations.Operational='Y')
AS a
WHERE a.Completed IS NULL
This is weird, I have several queries which haven't been changed for months, which have worked perfectly well (example query below), however are suddenly throwing 'duplicate column name' errors - error 1060.
I haven't changed the queries or the installation of MySQL for months (running version 5.0.22)
I know the subquery returns 2 columns both called 'ID' (as both the Locations and Control tables both have a column called ID and I'm selecting all columns from both tables using the *, however this didn't used to bother MySQL, but now it suddenly does without any apparent reason.
Can anyone shed any light on why this behaviour has changed without any change to the installation or queries being run!?
Many thanks,
Chris.
Example query...
SELECT a.* FROM
(SELECT Locations.*,Locb.Company AS Parn,Control.*,date_format(Control.Completed,'%d/%m/%y') AS cmpl,Control.ID AS cid,Qset.Type,Users.Name FROM Locations LEFT JOIN Locations AS Locb ON Locations.Parent=Locb.ID LEFT JOIN Control ON Locations.ID=Control.Location AND Control.Qset IN (4,18,24,155) AND Control.Status='Released' AND Control.Completed > (curdate()-interval 365 day) LEFT JOIN Qset ON Control.Qset=Qset.ID LEFT JOIN Users ON Control.User=Users.ID WHERE Locations.Org=6 and Locations.Parent<>0 and Locations.Operational='Y')
AS a
WHERE a.Completed IS NULL
Data Privacy at Any Cost (no replies)
Transaction data is a major cause of concern in any organization. Being a financial institution it was a constant challenge to protect sensitive data that usually leaks out from the GUI. To top it was the issue of ever growing application database that triggered the red alarm almost every other day. We found the perfect fix in the form of ‘Data Privacy Pack for Oracle E-Business Suite’ – a complete centralized manager for data classification and security aspects. It was worth every penny spent. For more information visit http://www.solix.com/data_privacy_oracle_ebusiness_suite.htm.
Several users for several customers, web hosting? (no replies)
Hello
I am trying to make a control panel for a web hosting site. The MySQL-part of it should, like other cps have done before, create databases and users. I wondered what would be the best way to keep each customer's users and databases separate from other customer's databases and users? And how can I list all databases for just one of the customer's for example. Should I keep a separate db for what users belong to what customer etc.?
thanks
I am trying to make a control panel for a web hosting site. The MySQL-part of it should, like other cps have done before, create databases and users. I wondered what would be the best way to keep each customer's users and databases separate from other customer's databases and users? And how can I list all databases for just one of the customer's for example. Should I keep a separate db for what users belong to what customer etc.?
thanks
Whem i use having with my consult? (no replies)
Good Morning Friends,
i tryed create this code... but it's very slow in my system.
someone have idea what i can do??
I would like select a data having < 500 days in dismissal and admission of employe
wrong code... :-(
SELECT tb_demissao.nome, tb_demissao.pernr, tb_lojas.id_loja, tb_demissao.status, tb_demissao.dtdemi, tb_demissao.dtadmi, tb_demissao.motivo, tb_demissao_motivo.descricao_motivo, tb_cpf.stat2, tb_cpf.pernr, tb_cpf.btrtl, tb_cpf.btrtl, DATEDIFF(tb_demissao.dtdemi,tb_demissao.dtadmi) as Total FROM tb_lojas JOIN tb_cpf ON (tb_lojas.id_loja = tb_cpf.btrtl) JOIN tb_demissao ON (tb_cpf.pernr=tb_demissao.pernr) JOIN tb_demissao_motivo ON (tb_demissao.motivo = tb_demissao_motivo.id_demissao_motivo) GROUP BY tb_cpf.bukrs, tb_demissao.nome, tb_demissao.pernr, tb_lojas.id_loja, tb_demissao.status, tb_demissao.dtdemi, tb_demissao.dtadmi, tb_demissao.motivo, tb_demissao_motivo.descricao_motivo, tb_cpf.stat2, tb_cpf.pernr, tb_cpf.btrtl, tb_cpf.btrtl HAVING (Total < 500 and tb_demissao.status=8 AND tb_cpf.bukrs = 'LAME' AND tb_cpf.btrtl = '0145')
Tks,
leo
i tryed create this code... but it's very slow in my system.
someone have idea what i can do??
I would like select a data having < 500 days in dismissal and admission of employe
wrong code... :-(
SELECT tb_demissao.nome, tb_demissao.pernr, tb_lojas.id_loja, tb_demissao.status, tb_demissao.dtdemi, tb_demissao.dtadmi, tb_demissao.motivo, tb_demissao_motivo.descricao_motivo, tb_cpf.stat2, tb_cpf.pernr, tb_cpf.btrtl, tb_cpf.btrtl, DATEDIFF(tb_demissao.dtdemi,tb_demissao.dtadmi) as Total FROM tb_lojas JOIN tb_cpf ON (tb_lojas.id_loja = tb_cpf.btrtl) JOIN tb_demissao ON (tb_cpf.pernr=tb_demissao.pernr) JOIN tb_demissao_motivo ON (tb_demissao.motivo = tb_demissao_motivo.id_demissao_motivo) GROUP BY tb_cpf.bukrs, tb_demissao.nome, tb_demissao.pernr, tb_lojas.id_loja, tb_demissao.status, tb_demissao.dtdemi, tb_demissao.dtadmi, tb_demissao.motivo, tb_demissao_motivo.descricao_motivo, tb_cpf.stat2, tb_cpf.pernr, tb_cpf.btrtl, tb_cpf.btrtl HAVING (Total < 500 and tb_demissao.status=8 AND tb_cpf.bukrs = 'LAME' AND tb_cpf.btrtl = '0145')
Tks,
leo
Error 1146, but all files are there (no replies)
Hello,
i have a strange problem in one database. My system is Linux and i have installed mysql Ver 14.12 Distrib 5.0.24a, for pc-linux-gnu (i486) using readline 5.1
Using mysql command line, i can use the database (oscpt2) and all tables, but when i execute a Select on a table (configuration, for example) from the database i get the error "1146 - Table 'oscpt2.configuration' doesn't exist".
All .frm files are in the system, and are not corrupted.
What can i do ? Rebuilt the database ?
Thanks
i have a strange problem in one database. My system is Linux and i have installed mysql Ver 14.12 Distrib 5.0.24a, for pc-linux-gnu (i486) using readline 5.1
Using mysql command line, i can use the database (oscpt2) and all tables, but when i execute a Select on a table (configuration, for example) from the database i get the error "1146 - Table 'oscpt2.configuration' doesn't exist".
All .frm files are in the system, and are not corrupted.
What can i do ? Rebuilt the database ?
Thanks
Selecting a subset of table via keys (no replies)
I have a DB of UPC/barcodes in one table with 3 populated fields, UPC, size and product, and I have another identically formatted table with just a couple hundred UPCs populated in it, no other data... What I want is to populate the actual second table with the corresponding size and product fields from matching UPCs...
It could be via query, or via view/constraints/foreign key reference, I dunno the best way... It would be nice to also get a seperate list of any UPCs from second table not found in main table, but that's secondary and not as big a deal. I posted over in Newbie forum, but no response there yet and I'm not sure I was clear enough, or if its a Newbie enough problem :-) Thanks for any help!
It could be via query, or via view/constraints/foreign key reference, I dunno the best way... It would be nice to also get a seperate list of any UPCs from second table not found in main table, but that's secondary and not as big a deal. I posted over in Newbie forum, but no response there yet and I'm not sure I was clear enough, or if its a Newbie enough problem :-) Thanks for any help!
Insight into NEON Enterprise Software’s New Data Archiving Product: TITAN Archive (no replies)
Data archiving and legal compliance of such data have always been a priority for companies. NEON Enterprise Software’s newest data archiving product, TITAN Archive, is the newest player in this area. It easily manages databases that archive large amount of old data while preserving all legal requirements associated with it.
TITAN Archive uses a Java interface which allows it to merge easily with existing databases and can be operated as an EADOtm (Encapsulated Archive Data Object). This makes TITAN Archive an independent entity allowing the administrator to search multiple databases using standard SQL queries. This special feature allows easy formatting of archived data to ensure easy and fast data retrieval.
TITAN Archive is a must for organizations that are close to the point of no return with their data archiving needs. Visit http://neonesoft.com/TAR.shtm to learn more.
TITAN Archive uses a Java interface which allows it to merge easily with existing databases and can be operated as an EADOtm (Encapsulated Archive Data Object). This makes TITAN Archive an independent entity allowing the administrator to search multiple databases using standard SQL queries. This special feature allows easy formatting of archived data to ensure easy and fast data retrieval.
TITAN Archive is a must for organizations that are close to the point of no return with their data archiving needs. Visit http://neonesoft.com/TAR.shtm to learn more.
problem using mysql -h myhost (no replies)
Hi,
I'm not sure if this is the right forum - there seems to be a huge number!
I've been developing web pages code on one machine using PHP/MySQL with the
database on another machine. Now that it's supposedly working I copied all
the stuff across to the same machine that runs the MySQL server. All the
mysql_connect() calls now fail. This seems to be becuase I am using the
name of the host rather than 'localhost'. It's not just a php problem
as I then found that the mysql command behaves the same way -
mysql -h myhost
run on myhost gives out an error:
ERROR 2003 (HY000): Can't connect to MySQL server on 'myhost' (111)
putting 'localhost' (or omitting -h ...) works fine.
I'm running under Ubuntu Linux 8.04 and the mysql command --help gives:
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
while mysql -v gives:
Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)
I'm not sure if this is the right forum - there seems to be a huge number!
I've been developing web pages code on one machine using PHP/MySQL with the
database on another machine. Now that it's supposedly working I copied all
the stuff across to the same machine that runs the MySQL server. All the
mysql_connect() calls now fail. This seems to be becuase I am using the
name of the host rather than 'localhost'. It's not just a php problem
as I then found that the mysql command behaves the same way -
mysql -h myhost
run on myhost gives out an error:
ERROR 2003 (HY000): Can't connect to MySQL server on 'myhost' (111)
putting 'localhost' (or omitting -h ...) works fine.
I'm running under Ubuntu Linux 8.04 and the mysql command --help gives:
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
while mysql -v gives:
Server version: 5.0.51a-3ubuntu5.1 (Ubuntu)
truncating the general_log table (1 reply)
A nice feature of MySQL 5.1 is the ability to have the slow_query_log and the general_log, log to a table instead of a file.
Does anyone know of a way to keep these tables from growing too large?
I could probably put an AFTER INSERT trigger on each table and delete everything older than X days. How much additional overhead would that cause?
Or maybe use the event scheduler?
I really only want to be able to see/store the last hour's worth of queries.
ideas? thoughts?
Does anyone know of a way to keep these tables from growing too large?
I could probably put an AFTER INSERT trigger on each table and delete everything older than X days. How much additional overhead would that cause?
Or maybe use the event scheduler?
I really only want to be able to see/store the last hour's worth of queries.
ideas? thoughts?