Re: Got error 139 from storage engine (InnoDB)
James, all, James Corteciano wrote: Hi All, I have received error message ERROR 1030 (HY000) at line 167: Got error 139 from storage engine when importing dump database to MySQL server. The MySQL server is using InnoDB. I have google it and it's something problem on exceeding a row-length limit in the InnoDB table. you are putting overload on our crystal balls - why don't you tell us (at least!) the MySQL version and the platform? error 139 looks like it is some Unix/Linux. On all these platforms, the exit code 139 means the process died because of signal 11 (segmentation violation, SIGSEGV), and then a core dump was taken (indicated by the value 128). 139 == 11 + 128. You should use your debugger of choice to get a stack backtrace, unless this was already done automatically during crash analysis / recovery. Any have idea how to fix this? Only when the backtrace is known, there may be some educated guesses about the cause of the problem. But all such effort is wasted unless you tell the versions. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to slim MySQL?
Hi! Nima Mohammadi wrote: On Mon, Jul 5, 2010 at 8:26 PM, Rob Wultsch wult...@gmail.com wrote: [[...]] You probably want to compile your own version of MySQL. You probably want to remove debugging symbols* (which have been present since the mid 5.0 series, iirc), and any engines/character sets/etc you don't need. *Alternatively you can run the strip command. -- Rob Wultsch wult...@gmail.com [[...]] I guess for removing debug symbols I need to add the --without-debug option to the ./configure command. I think using these options would also be helpful: - --without-man - --without-docs This will affect the package size, but not the individual binary. - --without-ipv6 - --disable-largefile largefile shouldn't have a big effect on size, but in a tiny embedded system you probably really don't need it. I'm not sure which engine we're going to use, so I'll have to defer this to another time. The engine will be quite important, stripping all engines you don't need will have the largest effect on size. Is there any other work I could do to strip MySQL? Check the character sets you configure, get rid of those you don't need. You might look into compiler options optimizing for space rather than for speed, but I hope you are not that much limited by your system. If you want to run client/server as separate binaries, you can use --without-embedded. However, I guess linking the server part to the application will have a big effect on space requirements, so you really should look into that. Are you using version 5.1 or 5.5 ? Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to slim MySQL?
Roam, Why are you installing a client server database on an embedded system? There are better databases for this task. In the past I've been able to squeeze a Windows application and an SQL database so it could run directly off of a floppy disk (remember those?). But it wasn't client server. :) It seems to me you are trying to fit an elephant into a phone booth. Just don't expect it to sit up and dance. BTW, where are the MySQL temp files going to go? Like the ones used for sorting the table? Also are you going to have crash recovery in case the machine gets accidentally powered off and damages the database? Mike At 09:59 AM 7/5/2010, you wrote: Hi folks, I'd like to install MySQL on an embedded system. It's a powerful x86-based computer with the only limitation of having a small-size flash ROM as its secondary storage. I tried installing MySQL from source which resulted in occupying 140 MB of disk space, while the maximum amount of flash memory I'm permitted to use is about 20-30 MB. So I'm wondering how to go about slimming MySQL down to the bare minimum. Following is the list of directories at the root of the installation directory: * bin/ * include/ * lib/ * libexec/ * mysql-test/ * share/ * sql-bench/ I think removing the 'include', 'mysql-test' and 'sql-bench' directories may be a good start, but I'm still far away from having a tiny little mysql! BTW, I tried MySQL Embedded, but it seems that it doesn't meet our needs. Any help would be highly appreciated :) *-- Nima Mohammadi* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Got error 139 from storage engine (InnoDB)
Hi Joerg, Thanks for your reply. I found out that this error was limitation row length of 8000 bytes on InnoDB. I have check the dump sql file and one particular table is causing error 139. What I did is just to use MyISAM engine rather than InnoDB for a specific table only. BTW, the machine has 24GB memory and Quad-Core CPU. The platform is RHEL 5.5 x64 and mysql-server-5.0.77-4.el5_4.2. *Rob Wultsch *and Prabhat Kumar, thanks for your response. Regards, James On Tue, Jul 6, 2010 at 5:19 PM, Joerg Bruehe joerg.bru...@sun.com wrote: James, all, James Corteciano wrote: Hi All, I have received error message ERROR 1030 (HY000) at line 167: Got error 139 from storage engine when importing dump database to MySQL server. The MySQL server is using InnoDB. I have google it and it's something problem on exceeding a row-length limit in the InnoDB table. you are putting overload on our crystal balls - why don't you tell us (at least!) the MySQL version and the platform? error 139 looks like it is some Unix/Linux. On all these platforms, the exit code 139 means the process died because of signal 11 (segmentation violation, SIGSEGV), and then a core dump was taken (indicated by the value 128). 139 == 11 + 128. You should use your debugger of choice to get a stack backtrace, unless this was already done automatically during crash analysis / recovery. Any have idea how to fix this? Only when the backtrace is known, there may be some educated guesses about the cause of the problem. But all such effort is wasted unless you tell the versions. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603
combined or single indexes?
Is there a benefit to a combined index on a table? Or is multiple single column indexes better? If I have table 'foo' with columns a, b, and c. I will have a query like: select c from foo where a in (1,2,3) and b 12345; Is index on a,b better in any way than an a index and a b index? An explain with one index sees it but doesn't use it (only the where) and having 2 indexes sees both and uses the one on b. Am I right to think that 2 indexes are better than one combined one? thx, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to slim MySQL?
On Tue, Jul 6, 2010 at 2:00 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi! Nima Mohammadi wrote: On Mon, Jul 5, 2010 at 8:26 PM, Rob Wultsch wult...@gmail.com wrote: [[...]] You probably want to compile your own version of MySQL. You probably want to remove debugging symbols* (which have been present since the mid 5.0 series, iirc), and any engines/character sets/etc you don't need. *Alternatively you can run the strip command. -- Rob Wultsch wult...@gmail.com [[...]] I guess for removing debug symbols I need to add the --without-debug option to the ./configure command. I think using these options would also be helpful: - --without-man - --without-docs This will affect the package size, but not the individual binary. - --without-ipv6 - --disable-largefile largefile shouldn't have a big effect on size, but in a tiny embedded system you probably really don't need it. I'm not sure which engine we're going to use, so I'll have to defer this to another time. The engine will be quite important, stripping all engines you don't need will have the largest effect on size. Is there any other work I could do to strip MySQL? Check the character sets you configure, get rid of those you don't need. You might look into compiler options optimizing for space rather than for speed, but I hope you are not that much limited by your system. If you want to run client/server as separate binaries, you can use --without-embedded. However, I guess linking the server part to the application will have a big effect on space requirements, so you really should look into that. Are you using version 5.1 or 5.5 ? Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 Thanks for your help. Your answer was quite helpful :) I'm not sure what --without-embedded option does but I'll certainly consider that. We are using MySQL 5.1. -- Nima Mohammadi
Re: How to slim MySQL?
On Tue, Jul 6, 2010 at 7:10 PM, mos mo...@fastmail.fm wrote: Roam, Why are you installing a client server database on an embedded system? There are better databases for this task. In the past I've been able to squeeze a Windows application and an SQL database so it could run directly off of a floppy disk (remember those?). But it wasn't client server. :) It seems to me you are trying to fit an elephant into a phone booth. Just don't expect it to sit up and dance. BTW, where are the MySQL temp files going to go? Like the ones used for sorting the table? Also are you going to have crash recovery in case the machine gets accidentally powered off and damages the database? Mike As I previously said, deciding which RBMS to use is not up to me. Actually I myself suggested SQLite, but the system has already been designed and it's not possible to change the plans. Our case doesn't fit into your analogy (elephant and phone booth). The system has a powerful processor, 2GB of RAM and broadcasts video over the network. But the operating system resides on a flash memory. The client program, used to receive the stream, connects to the MySQL server to do some ACL and authentication stuff. There are some stored procedure in the DB which does the job. The purpose of using a flash memory and a read-only ramfs was to conqueror the accidentally powering off part! It will update the flash memory when we command it to do so. Hope the scenario is clear now! Did you really need this explanation to answer my question? -- Nima Mohammadi
Re: How to slim MySQL?
The more information, the easiest to pinpoint solutions. delete all client and administrativ tools (mysql, mysqladmin, etc from the server, since you aren't doing any transactions, I think you can ditch all engines excepto the isam/myisam, the basic one, also you migth want to leave memory engine... If I understand your project, you migth copy the database to the ram into a tmpfs and if any change is needed, you update it, maybe to another engine?? What would I do. Build mysql in some machine, only copy mysqld and it's libraries, start it and trace it with strace -e open to see what files does it needs at startup. Supply them. Add the database, run the aplication. If not working, repeat... until it works... that would be my approach, I think that would take like... 2 days at most. Don't think about leaning down the mysql, better thik about providing what it barely needs to run properly. Good luck. On Tue, Jul 6, 2010 at 10:59 AM, Nima Mohammadi nima@gmail.com wrote: On Tue, Jul 6, 2010 at 7:10 PM, mos mo...@fastmail.fm wrote: Hope the scenario is clear now! Did you really need this explanation to answer my question? -- Nima Mohammadi
Re: combined or single indexes?
Hi Bryan, all! Bryan Cantwell wrote: Is there a benefit to a combined index on a table? Or is multiple single column indexes better? This is a FAQ, but I'm not aware of a place to point you for the answer. If I have table 'foo' with columns a, b, and c. I will have a query like: select c from foo where a in (1,2,3) and b 12345; Is index on a,b better in any way than an a index and a b index? Any multi-column index can only be used when the values for the leading column(s) is/are known (in your example, they are). My standard example is a phone book: It is sorted by last name, first name; you cannot use this order when the last name in unknown (you have to sequentially scan it). An explain with one index sees it but doesn't use it (only the where) and having 2 indexes sees both and uses the one on b. Testing select strategies requires that you have a meaningful amount of data, and a close-to-real distribution of values: If your tables hold too few rows, the system will notice that it is wasteful to access them via the index, a scan is faster. And if your value distribution differs too much from later real data, the strategy selected will also differ. Am I right to think that 2 indexes are better than one combined one? It depends: AFAIK, MySQL will not yet combine several indexes, but evaluate only one per table access. If you have a usable multi-column index, it will provide better selectivity than a single-column index, so it is better if all the leading values are given. I cannot specifically comment on conditions using in and . HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fixed Connection Diagnostic Tool
Thank you all for the help and info! This error happened because I started MySQL with -bind-address=91.203.57.207; even if Softslate is given the proper IP address and port number is fails on connection pooling. I fixed the problem by setting the MySQL IP address to 127.0.0.1. I am thinking that the reason is that the C3P0 connection pooling cannot work on a 'remote' machine. Michel - Original Message - From: Rob Wultsch wult...@gmail.com To: michel compu...@videotron.ca Cc: mysql@lists.mysql.com Sent: Tuesday, July 06, 2010 1:52 AM Subject: Re: Connection Diagnostic Tool On Mon, Jul 5, 2010 at 3:55 PM, michel compu...@videotron.ca wrote: I have been trying to figure this one out, but I don't have the skill sets here so I can use some help. I tried ' -h 127.0.0.1' in my bash shell and I get a command not found, so I am still really off-the-mark. Is there a place on the net I can look up what it does and how to run it? Thanks! I am pretty sure Michael that meant running the command line mysql client: mysql -uuser -ppass -h127.0.0.1 -e 'select hello world!' -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=compu...@videotron.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fixed Connection Diagnostic Tool
C3P0 connection does, indeed work well on remote machines.. In fact, I only deploy it locally on dev servers. My production systems all use c3p0 on remote servers. Again, if you can connect from the command line of your client machine to the server *via TCP* with the same credentials as your DataSource is using, then it will all just work fine. You appear to have specified a bind address which made local TCP connections impossible. Address that, and you sohuld have no trouble at all. - md On Tue, Jul 6, 2010 at 6:45 PM, michel compu...@videotron.ca wrote: Thank you all for the help and info! This error happened because I started MySQL with -bind-address=91.203.57.207; even if Softslate is given the proper IP address and port number is fails on connection pooling. I fixed the problem by setting the MySQL IP address to 127.0.0.1. I am thinking that the reason is that the C3P0 connection pooling cannot work on a 'remote' machine. Michel - Original Message - From: Rob Wultsch wult...@gmail.com To: michel compu...@videotron.ca Cc: mysql@lists.mysql.com Sent: Tuesday, July 06, 2010 1:52 AM Subject: Re: Connection Diagnostic Tool On Mon, Jul 5, 2010 at 3:55 PM, michel compu...@videotron.ca wrote: I have been trying to figure this one out, but I don't have the skill sets here so I can use some help. I tried ' -h 127.0.0.1' in my bash shell and I get a command not found, so I am still really off-the-mark. Is there a place on the net I can look up what it does and how to run it? Thanks! I am pretty sure Michael that meant running the command line mysql client: mysql -uuser -ppass -h127.0.0.1 -e 'select hello world!' -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=compu...@videotron.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: combined or single indexes?
Hi, MySQL can use a single index in a query as you've seen in the result of explain. Of course it is better to have an index made of 2 or more columns because it will match better the query. But if I remember well, the in() function can't use an index. And I think it also can't use an index if you use OR operators like: select foo from table where a=1 or a=2; So for your query the single-column index for the second column is enough. I've seen some tricks for using a faster method by using union and 2-column index, something like: select foo from table where a=1 and b1234 union select foo from table where a=2 and b1234 union select foo from table where a=3 and b1234 This might be faster in some cases because the query would be able to use the 2-column index, and especially if the content of those columns is made only of numbers, because in that case the query will use only the index, without getting data from the table. -- Octavian - Original Message - From: Bryan Cantwell bcantw...@firescope.com To: mysql@lists.mysql.com Sent: Tuesday, July 06, 2010 6:41 PM Subject: combined or single indexes? Is there a benefit to a combined index on a table? Or is multiple single column indexes better? If I have table 'foo' with columns a, b, and c. I will have a query like: select c from foo where a in (1,2,3) and b 12345; Is index on a,b better in any way than an a index and a b index? An explain with one index sees it but doesn't use it (only the where) and having 2 indexes sees both and uses the one on b. Am I right to think that 2 indexes are better than one combined one? thx, Bryancan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=octavian.rasn...@ssifbroker.ro __ Information from ESET NOD32 Antivirus, version of virus signature database 5257 (20100707) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5257 (20100707) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
