ScrollableResults causing OutOfMemory
Hello,
I have a Company table with 50 million rows that I am trying to index
with Hibernate search with
fullTextSession.setFlushMode(FlushMode.MANUAL);
fullTextSession.setCacheMode(CacheMode.IGNORE);
ScrollableResults results = fullTextSession.createCriteria(
Company.class ).setFetchSize(100).scroll( ScrollMode.FORWARD_ONLY );
while( results.next() ) {
curCompany = (Company) results.get(0);
fullTextSession.index(curCompany);
index++;
if (index % 100 == 0)
{
fullTextSession.clear(); //clear every batchSize since
the queue is processed
}
}
Above results in OutOfMemoryError. I looked at the Hibernate query and
there is no LIMIT clause. I am using MySQL 5.0.51g and Connector/J
5.0.6. Connector/J debug shows ScrollableResults enabled.
Thanks
H
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large temporary file(s)
In mysql, GROUP BY also by default does sorting using "ORDER BY", so you an
avoid it by including "ORDER BY NULL" after GROUP BY, this should help.
On 6/24/08, Jerry Schwartz <[EMAIL PROTECTED]> wrote:
>
> In all of that information, I forgot to post the actual query:
>
> INSERT INTO consolidated_customer_data
>SELECT
>customers.customer_id,
>account.account_name,
>customers.email,
>customers.email_status,
>customers.dm_status,
>customers.status,
>customers.last_name,
>customers.first_name,
>customers.sal,
>customers.company,
>customers.address_1,
>customers.address_2,
>customers.address_3,
>customers.country,
>customers.zip,
>customers.input_source,
>customers.interest_category,
>customers.interest_subcategory,
>CONCAT("|", GROUP_CONCAT(cust_topics.topic_code SEPARATOR "|"), "|")
>
>AS topic_list,
>stage.stage_name
>FROM
>customers JOIN account ON account.account_id = customers.account_id
>JOIN stage ON customers.stage_id = stage.stage_id
>LEFT JOIN cust_topics ON customers.customer_id =
> cust_topics.customer_id
>GROUP BY customers.customer_id;
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
Maatkit question
Hi guys, This might be somewhat off topic but does anyone have experience with Baron Schwartz's maatkit, particulary with error messages? I have several databases on my master/slave setup and can use mk-table-checksum on almost all of them on both hosts. There are 3 databases where I get this error, independent of if I try to run it on master and slave or just one of them: DBD::mysql::db selectrow_hashref failed: fetch() without execute() at /usr/bin/mk-table-checksum line 1485. Any ideas where this might be coming from? Thanks Olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial data and mysql
On Thu, Apr 24, 2008 at 4:06 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote: > I have been storing points in mysql without use of the spatial > extension. I do not forsee the need to ever store more than points, > and am wondering if the spatial extensions would offer any significant > advantages. I have looked a bit for tutorials, etc... and have not > found much. > > One feature that I would like is to be able to find all points withen > X distance from of point Y, without doing a table scan. Would the > spatial index (Rtree) be able to achieve this? > > Are there any good tutorials (or heaven forbid, books) that anyone can > suggest? > > Should I go hang out with the cool kids that are using postGIS ;) I'm going to answer my own question for the sake of the archive. The spatial index would be able to do an indexed search for points within a bounded box on releases as early as 4.1. Take a look at http://dev.mysql.com/doc/refman/4.1/en/using-a-spatial-index.html . -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with CREATE TABLE/DROP TABLE
On Jun 24, 2008, at 10:13 AM, Rolando Edwards wrote: My first impression is to say: "Sounds like the InnoDB internal data dictionary still has the table recorded somewhere." According to Page 566 Paragraph 3 of "MySQL Administrator's Guide and Language Reference" (ISBN 0-672-32870-4), "InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there." This is may explain why the bug persists. Innobase Oy never got to fix it, and now Oracle owns Innobase Oy (I am still in mourning over that). This was my first thought, but the original bug report suggests that the bug occurs with other table types besides InnoDB. You may want to look into Falcon if you want ACID transaction supported tables. Transactional support isn't critical to my implementation, but foreign keys are. Also, it's my undertanding that Falcon is unsupported before MySQL 6, and I'm not prepared to upgrade that far yet. -- Gwynne, Daughter of the Code "This whole world is an asylum for the incurable." -Original Message- From: Gwynne Raskind [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 2:58 AM To: mysql@lists.mysql.com Subject: Problem with CREATE TABLE/DROP TABLE I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. -- Gwynne, Daughter of the Code "This whole world is an asylum for the incurable." -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with CREATE TABLE/DROP TABLE
My first impression is to say: "Sounds like the InnoDB internal data dictionary still has the table recorded somewhere." According to Page 566 Paragraph 3 of "MySQL Administrator's Guide and Language Reference" (ISBN 0-672-32870-4), "InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there." This is may explain why the bug persists. Innobase Oy never got to fix it, and now Oracle owns Innobase Oy (I am still in mourning over that). You may want to look into Falcon if you want ACID transaction supported tables. -Original Message- From: Gwynne Raskind [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 2:58 AM To: mysql@lists.mysql.com Subject: Problem with CREATE TABLE/DROP TABLE I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. -- Gwynne, Daughter of the Code "This whole world is an asylum for the incurable." -- 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]
Table-specific grants problem
Hi, Here is the simplest test case I could boil this down to. I'm running this in 5.0.51a on RHEL4. Preconditions: - mysql.user only contains root user - mysql.db is empty - mysql.tables_priv is empty - mysql.host is empty I login as root, and run the following: CREATE TABLE test.foo ( id int unsigned auto_increment, name varchar(255), PRIMARY KEY (id) ); CREATE USER [EMAIL PROTECTED]; GRANT SELECT, INSERT, UPDATE, DELETE ON test.foo TO [EMAIL PROTECTED]; FLUSH PRIVILEGES; Now I login as [EMAIL PROTECTED], and run "SHOW DATABASES". I only see information_schema. If I try to "USE test" or "SELECT * FROM test.foo", I get the following error: ERROR 1044 (42000): Access denied for user 'test'@'localhost' to database 'test' Now I login as root again, and execute the following: GRANT SELECT ON test.* TO [EMAIL PROTECTED]; FLUSH PRIVILEGES; Now I switch back to [EMAIL PROTECTED] Now "test" shows up when I do "SHOW DATABASES", and I can "SELECT * FROM test.foo". However, if I try "INSERT INTO test.foo SET name='bar'", I get the same error again: ERROR 1044 (42000): Access denied for user 'test'@'localhost' to database 'test' It's as if table-level permissions are being completely ignored, and only database level permissions are kicking in. I have been unable to reproduce on my FC9 box (also 5.0.51a) or my OSX box (also 5.0.51a). This leads me to believe it's something external, such as a linked library or a configuration file. I've looked at the config - can't see anything too special there - and I just don't know enough about this to pontificate on potential issues with linked libraries. Wondering if anyone can help me out, as I am about ready to pull all of my hair right out of my head! I have searched Google repeatedly, and have skimmed through all bugs containing the word "grant" reported in version 5, and can't find anything. Any help is greatly appreciated! Thanks, Taylor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Do I need to use GROUP BY to do this?
Off the top of my head I can't think of a way of getting the output in the format that you want. If you use a query like: SELECT SUBSTRING(sales_date,1,10), sales_type, COUNT(sales_id) FROM sales_activity GROUP BY SUBSTRING(sales_date,1,10), sales_type; You'll get output like: Datetypenumber 2008-06-15 1 4 2008-06-15 2 2 2008-06-16 1 2 2008-06-17 1 2 which is the data that you want in a different output format. Thanks On Mon, 2008-06-23 at 18:16 -0700, Grant Giddens wrote: > Ian, > > Thanks for the help, this query worked perfectly. Can you also help > me with one more query? Say my sales_type=1 for a sale, and > sales_type=2 for a return. I'd like to do 1 query to get a count of > the sales and returns for each day. Here was my test data again: > > INSERT INTO `sales_activity` VALUES (1, '2008-06-15 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (2, '2008-06-15 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (3, '2008-06-15 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (4, '2008-06-15 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (5, '2008-06-15 13:00:00', 2); > INSERT INTO `sales_activity` VALUES (6, '2008-06-15 13:00:00', 2); > INSERT INTO `sales_activity` VALUES (7, '2008-06-16 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (8, '2008-06-16 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (9, '2008-06-17 13:00:00', 1); > INSERT INTO `sales_activity` VALUES (10, '2008-06-17 13:00:00', 1); > > The result of the query should be: > > date salesreturns > -- -- > 2008-06-1542 > 2008-06-1620 > 2008-06-1720 > > Thanks, > Grant > > --- On Wed, 6/18/08, Ian Simpson <[EMAIL PROTECTED]> wrote: > From: Ian Simpson <[EMAIL PROTECTED]> > Subject: Re: Do I need to use GROUP BY to do this? > To: "Grant Giddens" <[EMAIL PROTECTED]> > Cc: mysql@lists.mysql.com > Date: Wednesday, June 18, 2008, 11:02 AM > > > > I happen to have worked on a similar query this morning, so > it's in my > mind :) > > SELECT SUBSTRING(sales_date,1,10), COUNT(sales_id) > FROM sales_activity > WHERE sales_type = 1 > GROUP BY SUBSTRING(sales_date,1,10); > > should do the trick. > > On Tue, 2008-06-17 at 18:21 -0700, Grant Giddens wrote: > > Hi, > > > > I have a table where I keep sales transactions, so > I'm trying to do a query that will count the number of > transactions per day. > > > > My test data looks like: > > > > -- > > -- Table structure for table `sales_activity` > > -- > > > > CREATE TABLE `sales_activity` ( > > `sales_id` int(11) NOT NULL auto_increment, > > `sales_date` datetime NOT NULL default '-00-00 > 00:00:00', > > `sales_type` tinyint(4) NOT NULL default '0', > > PRIMARY KEY (`sales_id`) > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; > > > > -- > > -- Dumping data for table `sales_activity` > -- > Ian Simpson > System Administrator > MyJobGroup > > > This email may contain confidential information and is > intended for the recipient(s) only. If an addressing or > transmission error has misdirected this email, please notify > the author by replying to this email. If you are not the > intended recipient(s) disclosure, distribution, copying or > printing of this email is strictly prohibited and you should > destroy this mail. Information or opinions in this message > shall not be treated as neither given nor endorsed by the > company. Neither the company nor the sender accepts any > responsibility for viruses or other destructive elements and > it is your responsibility to scan any attachments. -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
