InnoDB Commit question
Hi, we have a db with myisam and a single innodb table. 2 separate processes are inserting data into the myisam tables and the innodb table at the same time. We have noticed an issue where commits to the innodb table appear to be delayed until the process inserting into the myisam tables is finished. Has anyone else noticed this behavior? What could be causing this? Any help appreciated Rob Brooks The Brian Group LLC
Commit question
Hi, we have a db with myisam and a single innodb table. 2 separate processes are inserting data into myisam and the innodb tables at the same time. Intermittently, we have noticed an issue where commits to the innodb table appear to be delayed until the process inserting into the myisam tables is finished. Has anyone else noticed this behavior? What could be causing this? Any help appreciated Rob Brooks The Brian Group LLC
RE: Query Error Help
nm ... I found the problem ... I need to use BINARY(LPAD ...)
-Original Message-
From: Rob Brooks [mailto:[EMAIL PROTECTED]
Sent: Monday, December 19, 2005 10:04 AM
To: mysql@lists.mysql.com
Subject: Query Error Help
Hello, I have this query:
SELECT FL3_PatientControlNumber, claims.RecordKey as reckey,
FL1_ProviderName, CalcFlag
FROM claims INNER JOIN service_line ON service_line.ClaimKey =
claims.RecordKey
WHERE ( service_line.FL42_ServiceLineRevCode BETWEEN LPAD('110',4,'0') AND
LPAD('210',4,'0' ) ) ORDER BY FL3_PatientControlNumber
which returns this error:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT),
(utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation
'between'
and am confused as to what's going on with this?
Rob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Error Help
Hello, I have this query:
SELECT FL3_PatientControlNumber, claims.RecordKey as reckey,
FL1_ProviderName, CalcFlag
FROM claims INNER JOIN service_line ON service_line.ClaimKey =
claims.RecordKey
WHERE ( service_line.FL42_ServiceLineRevCode BETWEEN LPAD('110',4,'0') AND
LPAD('210',4,'0' ) ) ORDER BY FL3_PatientControlNumber
which returns this error:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT),
(utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation
'between'
and am confused as to what's going on with this?
Rob
RE: Anyone use Snort and Acid?
I used snort and acid but that go me nowhere -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Monday, November 14, 2005 11:18 PM To: mysql@lists.mysql.com; Ed Kasky Subject: Re: Anyone use Snort and Acid? > . . . . . . . . . . . . . . . . . . > Randomly Generated Quote (125 of 1011): > "It must be a peace without victory Only a peace between equals > can last." --Woodroe Wilson > Whoever generated this quote needs to use a spellchecker; the correct spelling is WOODROW Wilson. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.0/167 - Release Date: 11/11/2005 -- 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]
max packet size question
Is there a practical maximum for max packet size? Is it simply a function of the amount of memory that you have?
server params
Hello, we have a web based application where the mysql server runs on a separate box from the code. The 2 boxes are on the same subnet so there really shouldn't be any latency issues(emphasis on shouldn't .) but we're trying to track down some timeout glitches in the application. I was wondering what things I might look at as far as server variable settings that might be pertinent to having the mysql server on a different box from the code. server 4.0.2 on darwin6.8 Thx Rob
wait_timeout
I have the following in the mysqld section of my.cnf: set-variable = wait_timeout=360 everything else in the conf file seems to take but my wait_timeout variable stays at the default of 28800 my version is 4.0.20 for apple-darwin6.8 Any thoughts?
FW: need opinion on FmPro Migrator
The tool is well regarded. I have not used it, but many people have with success. But, most FMP databases are so poorly structured that it is often best to start over. Keep in mind the necessity of importing data from FMP to MySQL though. . Kevin Bice Foster Enterprises 512-583-0573 http://fmpweb.com > -Original Message- > From: Ted Zeng [mailto:[EMAIL PROTECTED] > Sent: Monday, March 28, 2005 11:05 AM > To: mysql@lists.mysql.com > Subject: need opinion on FmPro Migrator > > Hi, all, > > I need to convert databases from FileMaker to MySQL. I searched > the web and found this product > FmPro Migrator > > I am wondering if people here have used it and know how well it works. > > Or are there any other way to do the conversion out there? > > Ted Zeng > Adobe Systems Inc. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table join question
Although I didn't find it in the docs anywhere, I know from experience that you cannot join more than 31 tables in 4.0.21 I was wondering if anybody knew if this limit has changed in version 5 Thx Rob
number of table joins in MySQL 5.xx
Is the maximum number of table joins still 31 in MySQL 5.xx?
RE: using between
Well, we have this db with various ip address ranges and the country of origin associated with each ... the format is: countryOfOrigin FromIP ToIP --- -- US some lower boundsome upper bound Canada some lower boundsome upper bound Etc... So the real statement would be: Select countryOfOrigin From IPRangeTable where targetIP between FromIP and ToIP Obviously, this looks at every record to see if targetIP is in the range. I'm just trying to think of a better way to do it The ranges are mutually exclusive so once it finds it, that would be it. I guess I could put a 'limit 1' on there to get it to quit once it finds it. Is there something better? -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, March 25, 2005 2:36 PM To: MySQL list Subject: Re: using between At 01:48 PM 3/25/2005, you wrote: >Hello > >Is there a way when searching for a range of values for a particular field >that mysql would not have to look at the entire table ... I'm guessing with >some type of composite key or something? ... > >e.g. > >SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; > >Field1 and field2 are indexed but that doesn't help because you're looking >for a range of values instead of a particular value > Rob, I'm having a hard time wrapping my head around your example.It's like the tail wagging the dog. Putting variables and columns to your syntax I get: select cust_num from table_invoices where 5 between invoice_amt and tax; I think you meant to say: select aCol from aTable where aCol between val1 and val2; If aCol is indexed, then MySQL will use the index to get a Range on val1 and val2. Just use "Explain" and you'll see the index that it's using. Mike -- 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]
using between
Hello Is there a way when searching for a range of values for a particular field that mysql would not have to look at the entire table ... I'm guessing with some type of composite key or something? ... e.g. SELECT aField FROM aDatabase WHERE aVariable BETWEEN field1 AND field2; Field1 and field2 are indexed but that doesn't help because you're looking for a range of values instead of a particular value -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL help
in question below, the problem is not in record '7047' but in the record which starts with the name 'Triad' also ... I'm using 4.0.20-standard-log -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 2:56 PM To: mysql@lists.mysql.com Subject: SQL help Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4" thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4" thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | < ... truncated for brevity > +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- 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]
SQL help
Can someone help me with this? this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: +---+--- -+--+--+ | Name | Detail | ID | ID | +---+--- -+--+--+ | Jade Arch Series | 3/4" thick beveled jade acrylic arch on a beveled jade acrylic base | 7015 | 437 | | Jade Arrow Series | 3/4" thick beveled jade acrylic arrow on beveled jade acrylic base. | 7016 | 438 | | Queen Jade| Unique and elegant shape makes a beautiful free standing award. Glass is jade color. | 7041 | 463 | | Octavia Jade | Beautiful jade glass octagon shaped award. | 7043 | 465 | | Jade Arresting Obelisk| Pristine, monumental style jade glass award. | 7045 | 467 | | Jade Autumn Leaf | Contemporary design derived from natural shape. Cut from jade glass. | 7047 | 469 | | Triad | Free standing clear acrylic with a thick triangular shape. | 7069 | NULL | | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue, black, or light velour backgrounds. Retro style clock with new style design that allows it to hang in vertical or horizontal position. Large engravable black brass plate. Lifetime Guaranteed Quartz Movement | 7073 | 494 | | Traditional Wood and Glass Clock | Traditional clock with polished brass and cherry wood finished accents. Polished glass upright holds the clock. Black brass engravable plate. Lifetime Guaranteed Quartz Movement | 7087 | 508 | < ... truncated for brevity > +---+--- -+--+--+ 14 rows in set (0.06 sec) but this statement: select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left join items_online on items_online.ItemKey = Items.ID where items_online.ID = NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1; gives this: Empty set (0.00 sec) The only difference in the 2 statements is the 'where items_online.ID = NULL' part. Clearly in the first set, items_online.ID = NULL in record 7047 but when I look for it specifically, it is not found?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group_concat
Hello, I have a strong need/desire to use group_concat but I don't/can't run 4.1 right now. There is MyGroupConcat http://www.codeproject.com/useritems/MyGroupConcat.asp And I have no problem creating a .so on FreeBSD but I have a server running OSX and I'm not familiar with shared code on this platform. Anybody done this? Rob
