Re: Migration from SQL Server
Workbench provides some migration features and supports SQL Server http://www.mysql.com/products/workbench/migrate/ On Mon, Jul 14, 2014 at 4:47 PM, Felipe Coutinho felipelcouti...@gmail.com wrote: Hello, I'm a web developer and I'm developing a web application on Rails. I'm gonna use MySQL at production (I'm using Amazon RDS with MySQL) for this app. But this app already has a version of it (developed at ASP.NET) at production using SQL Server. Now I need to migrate the data from SQL Server to MySQL. The databases are very similar: - Few columns change the name. - One table was split in two - Few columns added - Few columns changed the type. Do you suggest any tool the helps with this migration? Thank you, Felipe. -- Felipe Leal Coutinho Website http://www.felipelc.com/ | Linkedin http://www.linkedin.com/pub/felipe-coutinho/21/902/a22 | Facebook http://www.facebook.com/felipelcoutinho
Re: sort order
Hi Wes, have you double checked you character set and collation ?? http://dev.mysql.com/doc/refman//5.5/en/charset-charsets.html The collation is which determines the sorting order. Carlos Proal On Wed, May 30, 2012 at 4:02 PM, Wes James compte...@gmail.com wrote: I have mysql 5.1.62-0ubuntu0.11.10.1 installed, but the sort order for utf8-unicode doesn't seem to be right. It is sorting some text like this (order by title): ! ! *`-=[];',./~@#$%^()_+{}|:?\ ! ![ !@ !a !A !t !test 'Coal age' [ \--\ 100 years when it should be (shouldn't it?): ! ! *`-=[];',./~@#$%^()_+{}|:?\ ! !@ !A ![ !a !t !test 'Coal age' 100 years [ \--\ -- Why is it doing this? Thanks, -wes
Re: file privilege
Hi, You have look at the user table which has global privileges, db is specific to each database. Carlos On Mon, Jan 30, 2012 at 10:56 PM, kalin m ka...@el.net wrote: hi... so i have this user in the user table in the mysql db that has all the privileges but grant. now i need for that user to have the file privilege so it can do into outfile. thing is i don't see that privilege anywhere in the db table. i know that i have to do the grant file on user@localhst thing but what table does that write to? also if i have all the other privileges granted and just need to add the file one, do i have to re-grant all the rest of those or it'd just add the file privilege to all the already granted ones? the tables_priv, columns_priv and procs_priv don't have any file privilege listed. this is the list of privileges i can see off the db table: | Select_priv | enum('N','Y') | Insert_priv | enum('N','Y') | Update_priv | enum('N','Y') | Delete_priv | enum('N','Y') | Create_priv | enum('N','Y') | Drop_priv | enum('N','Y') | Grant_priv| enum('N','Y') | References_priv | enum('N','Y') | Index_priv| enum('N','Y') | Alter_priv| enum('N','Y') | Create_tmp_table_priv | enum('N','Y') | Lock_tables_priv | enum('N','Y') | Create_view_priv | enum('N','Y') | Show_view_priv| enum('N','Y') | Create_routine_priv | enum('N','Y') | Alter_routine_priv| enum('N','Y') | Execute_priv | enum('N','Y') | Event_priv| enum('N','Y') | Trigger_priv | enum('N','Y') thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: file privilege
Correct. Carlos On Tue, Jan 31, 2012 at 12:03 AM, kalin m ka...@el.net wrote: thanks carlos. so if i go with: grant file on user that wont infringe on the all other privileges the user already has, correct? thanks.. On 1/31/12 12:47 AM, Carlos Proal wrote: Hi, You have look at the user table which has global privileges, db is specific to each database. Carlos On Mon, Jan 30, 2012 at 10:56 PM, kalin m ka...@el.net mailto:ka...@el.net wrote: hi... so i have this user in the user table in the mysql db that has all the privileges but grant. now i need for that user to have the file privilege so it can do into outfile. thing is i don't see that privilege anywhere in the db table. i know that i have to do the grant file on user@localhst thing but what table does that write to? also if i have all the other privileges granted and just need to add the file one, do i have to re-grant all the rest of those or it'd just add the file privilege to all the already granted ones? the tables_priv, columns_priv and procs_priv don't have any file privilege listed. this is the list of privileges i can see off the db table: | Select_priv | enum('N','Y') | Insert_priv | enum('N','Y') | Update_priv | enum('N','Y') | Delete_priv | enum('N','Y') | Create_priv | enum('N','Y') | Drop_priv | enum('N','Y') | Grant_priv| enum('N','Y') | References_priv | enum('N','Y') | Index_priv| enum('N','Y') | Alter_priv| enum('N','Y') | Create_tmp_table_priv | enum('N','Y') | Lock_tables_priv | enum('N','Y') | Create_view_priv | enum('N','Y') | Show_view_priv| enum('N','Y') | Create_routine_priv | enum('N','Y') | Alter_routine_priv| enum('N','Y') | Execute_priv | enum('N','Y') | Event_priv| enum('N','Y') | Trigger_priv | enum('N','Y') thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: SSIS and MySQL
David Afaik, the connector is pure .net application, so there is no 32/64 bits distinction; you can use it in either architectures. Carlos On Mon, Nov 14, 2011 at 9:23 AM, David Stoltz dsto...@shh.org wrote: Hi Folks, I'm in need to connect from my SQL 2008R2 (64 bit) server, to my MySQL database server... I found this page: http://www.mysql.com/downloads/connector/net/ This page has the 32 bit drivers, but I *believe* I would need 64 bit, since it's being installed on the 64 bit SQL server, correct? Are there 64 bit drivers available? Does anyone have any advice for me? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=carlos.pr...@gmail.com
Re: problem
Swaroop: How are you handling the connections to db ? only one, with a pool, something adhoc ?. It looks like the connection is only one and expires after a time out and you get the error that there is no connection. Carlos On 5/2/2011 3:15 PM, Andrés Tello wrote: Seems more a java issue than a mysql issue... check for your parameters for any keepalive or persostent option... I think you wouldn't need to recompile just re-run or at leat, reboot the server.. On Mon, May 2, 2011 at 2:34 AM, swaroop joisjois_swar...@yahoo.com wrote: Hello friends, I have MySQL server version 5.0.51a-Ubuntu installed on Ubuntu 8.04 machine . I would describe briefly what we are doing . Basically we have built a server that listen to Gprs connection from client and accepts data in form packets and inserts that data into MySQL database.I run three commands .1.listening to Gprs connection and displaying all the received packets on the terminal.2.Number of packets read will showed in Java serial forwarder (Tinyos for reference )which listens on another port 3.command that invokes Java files for inserting data into database table . Initially when i run the command everything works fine and when he receive packets he is able to insert data into table in MySQL database . He will still be listening on the port (i.e he is running 24*7)Assume i receive data after 12 hrs .i am experiencing the problem of .It may not necessarily be 12 hrs . If i have to insert data again i have to recompile the code again and run all the commands . The error that is troubling is Result for query failed. SQLState = 08003i googled the error and found that this Sqlstate indicates connection does not exist . I dont have any clues.Can any one help me please ? Regards,Swaroop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: performance debian lenny and vmware
Rafael Performance depends on several things, but none related with debian or vmware per se. So we need more information about you configuration (ram, buffers, etc) and you environment (concurrent users, transactions, etc). Maybe you have not tuned your mysql and it is slow because of that. Carlos Proal On 3/27/2011 1:14 PM, Rafael Valenzuela wrote: Hi all: I have installed mysql 5.0 on a debian lenny 64 bits, in vmware,but I see that there are problems with mysql performance the server is too very slow. any solution? or any idea? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query precision issue
Thats because float columns store approximate data values, you may need to use an error range in comparison, or at your own discretion use the decimal data type. You can get more info in: http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html Carlos Proal On 10/25/2010 12:15 PM, Sairam Krishnamurthy wrote: All, I have simple query like 'select * from table1 where column1=-107.689878'. This returns an empty set. But there is data corresponding to this value of column. When I looked more into it, it seems like a precision issue. The value for column1 is -107.689878. More interesting is that the following query fetches the row, 'select * from table1 where column1=-107.689878000' Note that there are only three trailing zeros in the second query while there were four in the first. Can somebody help me to find out the problem? I can very well truncate the trailing zeros when querying, but I am interested in finding why an additional trailing zero returns an empty set. -- 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 extend innodb files?
You have to round the size of the last data file (ibdata4) and add the new ones. You can find more information on the manual: http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html http://dev.mysql.com/doc/refman/5.1/en/adding-and-removing.html Carlos On 9/28/2010 12:59 AM, Vokern wrote: Hello, Currently I have the setting: innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend Because the last file of ibdata4 is very large (more than 50G), if I want extend the data to more files, for example, ibdata5, ibdata6... how to do it? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: BLOB data gets encoded as utf8! (Anyone?)
I think that probably the mistake is in the way you are inserting the blob. If i understand right, you are inserting the blob with a traditional insert statement ??, in my experience i always use prepared statements to insert blobs, in that way the text goes exactly as the source without any utf-8 conversion. Besides this, using the insert can truncate your blob / statement, because some drivers/connectors limit the size of the sql statement, so if you are uploading a huge file the statement will not work (I dont know if this happens with the mysql driver or if there is a limit), so i strongly recommend using prepared statements for blobs with any database. Hope this helps. Carlos Proal On 8/28/2010 1:04 AM, Andreas Iwanowski wrote: Has no one encountered this before? There has got to be a solution, and I still haven't found it... So if you have any input please let me know! -Original Message- From: Andreas Iwanowski [mailto:namez...@afim.info] Sent: Tuesday, August 24, 2010 2:48 PM To: mysql@lists.mysql.com Subject: BLOB data gets encoded as utf8! Hello everyone! I am using an MFC unicode project that uses ODBC to access a MySQL 5.1.50 database via the MySQL ODBC 5.1.6 driver. character_set_connection is set to utf8 (Which I believe is the default for the driver) One of the tables contains two LONGBLOB columns, and the table default charset is utf-8 (since the application is unicode). However, when inserting into the LONGBLOB columns via an INSERT statement, the data gets corrupted/modified because is incorrectly UTF-8 encoded. My insert statement (simplified) does this: INSERT INTO _table_ (Desc, Data) VALUES ('...', '_blobdata_'); I have also tried the _binary introducer (http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html) INSERT INTO _table_ (Desc, Data) VALUES ('...', _binary'_blobdata_'); I'm escaping 0x00, 0x22, 0x27, and 0x5C, which are the only four that MySQL requires to be escaped per documentation for BLOB fields, and according to the MySQL docs columns of any BLOB type use no character set. Here is the problem: I found that, for example, 0xFF gets modified to 0xC3BF, which is in fact the UTF8 encoding for ASCII/Binary 0xFF. Needless to say the data becomes useless. I did, however, determine that the data is already corrupted in the myodbc.log file that the driver outputs if the option is set, so there is likely a problem with the driver settings or with the statement itself. I cannot be the first one to encounter this issue, so maybe if you have an idea (or a solution would be even better! :) then please let me know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=namez...@afim.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Anyone can help resolve this problem?
On 5/30/2010 5:44 PM, Lancer wrote: Hi there. Sorry for my poor english. Iinstalled MySQL 5.5 m3 though original RPM package and I modified the MySQL root password. When Iinstall WordPress, I got the error message like 'Error establishing adatabase connection'. But when I clear MySQL root password, everything goes well. I don't know that why. For security, Idon't want keep the empty password for production service. Anyone can help me to resolve it? Im just guessing but, did you flush the privileges ? maybe the change have no taken effect and then the valid password is still the old one. Btw, phpbb using mysql (not mysqli, it normal working) extension, phpbb will return error message like 'Access Denied for r...@localhost (using password:NO), why display NO and why phpbb use root to connect to database? I dont know the deep details on phpbb, but the account is usually setup in a config file (there you can change root...). Also note that mysql extension uses old style passwords and mysqli the new ones, you can get more info on the manual: http://dev.mysql.com/doc/refman/5.5/en/old-client.html Hope this helps. Carlos -- 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 put table definition into another table using SQL?
Hi Mike, you need to query the information_schema schema/database that contains the database dictionary. You can read the manual or a beautiful diagram here: http://www.xcdsql.org/Misc/MySQL_INFORMATION_SCHEMA.html Have a nice day Carlos Proal On 5/11/2010 11:06 AM, mos wrote: At 10:53 AM 5/11/2010, Anirudh Sundar wrote: Hi Mike, Did you try this :- Create table TableDef select * from TableX where 1 = 2; If you give this statement you will create the new table TableDef with the same variables and datatypes as table TableX Let me know if it works Cheers, Anirudh Sundar Anirudh, Thanks for the quick reply. I use something like that all the time when I want to copy a table's definition. Actually I use Create table2 select * from table1 limit 0. But what I'm looking for here is the actual definition of TableX to be stored in TableDef. So each row of TableDef represents a column definition from TableX. And I'd like TableDef to have these rows: ColName ColType --- -- First_Name Char(15) Last_Name Char(20) Start_Date Date Salary Double The above data are actually rows from TableDef. TableDef only has 2 columns ColName and ColType. The TableDef rows are of course the columns making up TableX. Mike On Tue, May 11, 2010 at 9:06 PM, mos mailto:mo...@fastmail.fmmo...@fastmail.fm wrote: I'd like to get the field names and data types of a table, say TableX, and put it into TableDef using nothing but SQL. I know I can list the table definition using Describe Table and then loop through the results and insert the first two columns Field and Type into TableDef, but is there a way to do it using just SQL? Example: Describe TableX: First_Name Char(15) Last_Name Char(20) ... Start_Date Date .. Salary Double .. And I'd like TableDef to have these rows: ColName ColType --- -- First_Name Char(15) Last_Name Char(20) Start_Date Date Salary Double Is there a way to do this with one SQL statement? I'm really looking for the MySQL internal table where it stores the table definitions. TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sundar.anir...@gmail.comhttp://lists.mysql.com/mysql?unsub=sundar.anir...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb buffer pool usage
Hi Machiel, What do you mean with innodb buffer pool is at 100% full ? There are several status variables associated with innodb buffer pool ie: Innodb_buffer_pool_pages_free is the number of unused data pages. Innodb_buffer_pool_pages_total is the total number of pages. Innodb_buffer_pool_pages_data is the total number of used data pages (clean and dirty). Innodb_buffer_pool_pages_dirty is the number of dirty data pages. The number of clean data pages can be calculated from these first two status variables. etc.. You can calculate the usage ratio with a basic recipe: Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total This is the value that you have to monitor. If its close to 1, then you set up too much memory for innodb (warning: that this can be due to frequent flushed to disk, so you have to check that too); if on the other hand the ratio is too low, then you effectively are running out of resources and may need to add more memory to innodb. You can imply other things, with the other variables too. Obviously you have to do the job and review this values along the time, maybe there are some actions/effects like running reports or etl processes, you have to figure out what is going on as a whole not just the values in the formula. BTW: In Oracle is the same story, one thing is what you reserve for...and other thing is the actual usage. The latter if you are tuning Oracle manually, because one important difference in Oracle 10 and 11 is that the buffers can grow and shrink automatically (if you configure it) so you can say use the 100% memory at your convenience and Oracle can, for example, reduce the sort buffers and extend the index buffers on the fly. Obviously this also has advantages and disadvantages, but as a new DBA is good to get involved in this concepts and comparisons between dbms. Carlos Proal On 3/16/2010 12:46 AM, Machiel Richards wrote: Hi all Maybe someone can assist me with this one. A while back I requested some information relating to the MySQL innodb buffer pool size that seems to fill up rather frequently. The buffer pool is currently set to 3Gb , and it takes about 2-3 weeks after a restart to fill up. Someone replied and stated that this is preferred to be running at 100% usage as it means that it is running optimally. However, the oracle guys in our office disagrees with this and want to know the following: . If the innodb buffer pool is at 100% full, how will we know when it needs more buffers o i.e. let's say the database starts getting very busy and needs more buffers, how will we know that it requires this if the buffer pool usage is already at 100%. I am fairly new to database administration so no luck in answering them on this so I would appreciate the assistance. Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Dumping table contents to stdout in tab-separated value format
I dont know if this can help, but if you are on unix/linux you can call cat after the dump and easily you can get the output to the stdout ie: $ mysqldumptempfile.txt... cat tempfile.txt Carlos On 2/20/2010 9:03 PM, Yang Zhang wrote: Hi, I'm interested in piping out the contents of a mysql table to stdout in tab-separated value format, but: - using 'select * into outfile' can't write to stdout. - mysqldump --fields-... requires --tab, which requires an output file path. - I also tried mkfifo /tmp/pipe and select * into outfile '/tmp/pipe', but mysql complains about the file existing already. Is there any other quick way to do this without having to write a client app? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with the World.sql sample database
On 2/11/2010 11:47 AM, kebede teferi wrote: Could any one lead me to a true link where I can download the world.sql sample database? Thanks. http://dev.mysql.com/doc/index-other.html Carlos Proal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Forgot the root passwd on Mysql intalled on a windows machine
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Have a nice day Carlos On 2/2/2010 10:22 AM, ishaq gbola wrote: Hi Guys, I have forgotten the root password on mysql database installed on a windows machine, how can I reset this password. I have tried un-installing and re-installing it but it still seems to be requesting for the password. Does anyone know a way out? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I NEED HELP ON 'SOURCE(\.) MYSQL
Error 2 is No such file or directory So, probably the file is not in the right path or you need to explicitly provide the path to the file. Carlos On 2/1/2010 5:38 PM, kebede teferi wrote: Hi, I'm very new to this and I need help. What I want to do is to execute a source command statement from cmd to create tables and populate them on a data base that I created in Mysql. Here is what I did at CMD: *I changed the directory from c:\ to mysql *At mysql prompt i used -u root -p that prompted me to give my password. *I put in my password and I was allowed to access the mysql server. *I used the 'the use --database' command and I can see the database I created is active and waiting to be worked on. *then to run the script which is on a note pad with the name create and file type: sql I typed the following at the mysql prompt: *source create.sql; I get the error message '2' and '22' whichever way I tried the source command. I appreciate your help Kebede Teferi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Good source for sample data?
Google for data generator, there are free and commercial solutions available. Carlos On 1/28/2010 5:52 PM, Brian Dunning wrote: Hey all - I need a few million sample contact records - name, company, address, email, web, phone, fax. ZIP codes and area codes and street addresses should be correct and properly formatted, but preferably not real people or companies or email addresses. But they'd work if you did address validation or mapping. Anyone have a suggestion? - Brian -- 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 change mysql default database directory
Absolutely, check for the variable datadir on the config file (my.ini on windows and my.cnf on *nix). Carlos On 1/28/2010 9:12 PM, Lucky Wijaya wrote: Hi all, I've installed MySQL on 320GB Harddisk (partitioned into 3 partitions). I want to know if there's a way to change MySQL default database directory from C: to D:. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Sharding
Krishna I dont have any benchmark but you can take a look on the Cafepress presentation that includes some data (or ask the authors for deep reference) http://assets.en.oreilly.com/1/event/2/Horizontal%20Scaling%20with%20HiveDB%20Presentation.pdf Carlos On 1/22/2010 11:44 PM, Krishna Chandra Prajapati wrote: Hi Carlos, Have you tried and benchmark hivedb. any body reviewed hivedb. Please share the experience. Krishna On Fri, Jan 22, 2010 at 11:47 PM, Carlos Proal carlos.pr...@gmail.com mailto:carlos.pr...@gmail.com wrote: Hi Krishna Depending on your programming language, you can use http://www.hivedb.org/ Also you can try the new Spider Storage Engine http://spiderformysql.com/ I have not tried this one but seems interesting and there are a couple reviews by Giuseppe Maxia that can help you: http://datacharmer.blogspot.com/2009/04/test-driving-spider-storage-engine.html http://datacharmer.blogspot.com/2009/07/sharding-for-masses-spider-storage.html Carlos On 1/22/2010 4:47 AM, Krishna Chandra Prajapati wrote: Hi List, I am looking opensource tool for mysql sharding. One is mysql-proxy, but it is in alpha stage. Another one is spock proxy. Any one benchmark spock proxy. Is there other tools also. Please share views with mysql sharding Any response is highly appreciated. Thanks, Krishna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com
Re: How to change mysql default database directory
As Suresh and others said, you have to stop the dbms, move to content of the dir and restart the dbms. Also, if you have a fresh install, innodb tables will be saved on a datafile inside the same data dir, but if is not a fresh install you may have innodb variables (ie. innodb_data_home_dir) in the config file, and you may need to change those too. Carlos On 1/28/2010 9:25 PM, Lucky Wijaya wrote: Is that all ? So, if i changed datadir on the config file, whenever i create databases mysql will store it on selected directory ? How about the created-before database ? Btw, I'm using Windows. Thanks in advance. *From:* Carlos Proal carlos.pr...@gmail.com *To:* mysql@lists.mysql.com *Sent:* Fri, January 29, 2010 10:16:31 AM *Subject:* Re: How to change mysql default database directory Absolutely, check for the variable datadir on the config file (my.ini on windows and my.cnf on *nix). Carlos On 1/28/2010 9:12 PM, Lucky Wijaya wrote: Hi all, I've installed MySQL on 320GB Harddisk (partitioned into 3 partitions). I want to know if there's a way to change MySQL default database directory from C: to D:. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=luckyx_cool_...@yahoo.com
Re: Sharding
Hi Krishna Depending on your programming language, you can use http://www.hivedb.org/ Also you can try the new Spider Storage Engine http://spiderformysql.com/ I have not tried this one but seems interesting and there are a couple reviews by Giuseppe Maxia that can help you: http://datacharmer.blogspot.com/2009/04/test-driving-spider-storage-engine.html http://datacharmer.blogspot.com/2009/07/sharding-for-masses-spider-storage.html Carlos On 1/22/2010 4:47 AM, Krishna Chandra Prajapati wrote: Hi List, I am looking opensource tool for mysql sharding. One is mysql-proxy, but it is in alpha stage. Another one is spock proxy. Any one benchmark spock proxy. Is there other tools also. Please share views with mysql sharding Any response is highly appreciated. Thanks, Krishna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: innodb recovery
Hi Johny Do you have the my.cnf configuration file ?? that can simplify things. Carlos On 1/20/2010 3:32 AM, Johny Brawo wrote: Hello! I got all data files (ibdata1, ib_logfile, etc) recovevered from mine old Debian 3.1 box (and i dont know MySQL version :( ). I want to get that DB running again. Can i copy these files to newer version of MySQL, and if i can - how? Any commands, any parameters? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Quick help with Insert
Hi !! You need a left join and then an insert. Please read: http://dev.mysql.com/doc/refman/5.1/en/join.html or google for tutorials on left join And tell me if you have further questions Carlos On 1/19/2010 1:00 PM, Intell! Soft wrote: Hey Not really quick ; - But nobody knows an answer? THX -Ursprüngliche Nachricht- Von: Intell! Soft [mailto:intellis...@fachoptiker.net] Bereitgestellt: Donnerstag, 14. Jänner 2010 17:40 Bereitgestellt in: gmane.comp.db.mysql.general Unterhaltung: Quick help with Insert Betreff: Quick help with Insert Hey I would need quick help with an Insert statement So, I have two tables Table A Table B So, in Table A I have a field called customerID The same field I do have in Table B So, I want to find out, which customerID from Table A is NOT in Table B and fill the customerID's which are not present in Table B. Understood? - Hope so THX !
Re: Record old passwords ?
On 1/18/2010 6:52 PM, Colin Streicher wrote: On January 18, 2010 01:34:15 pm Tompkins Neil wrote: Hi I'm in the process of designing a login system to a secure web page using MySQL. One of the features is we need to record and ensure that the user password is different from any of the last four passwords he/she has used. I was thinking of create four fields called Password1, Password2, Password3 and Password4 to record the old passwords. Is this a preferred method - or does anyone else have any recommendations ? Thanks, Neil I'm not an awesome database designer, most of what I do is code related stuff, I think what I would do for this is 1. hash the password( sha256/512 whatever) and then 2. store the hash in a string with delimiters. In that way, you solve 2 problems. You can store as many as you want to because you can just check hashes to make sure it isn't the same, and second, you aren't storing passwords in plain- text, which is a personal pet peeve. Neil, As others appointed, having another table with old passwords is a good design solution, and can allow you to have more than 4 passwords on your history. But in fact your solution is the best solution for performance and is called denormalization, this solution gives good performance because in 1 read you get all the passwords but has the limitation of be fixed to only 4 passwords (which is not so bad because you can add new columns as needed, you will never have 20 history passwords anyway, do you ?). So, thats the trade, design vs performance, you should pick the best for you. The solution proposed by Colin is another way to do it but, from the good design perspective is NOT a good solution, is what its called a multivalued attribute and all those should be avoided. But again, is up to you. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Replication Delete is not gettting replicated
I dont see anything unusual or missing on your config file and as the only thing missing are deletes, i think that might be a permission issue. Can you check out the grants for your replication users and see if they have full permissions granted ? mysql show grants for x; where is x is replication and replication2 respectively. Carlos On 1/18/2010 1:35 AM, Manasi Save wrote: Hi Anand, Please find below my configuration file of both the masters: ON MASTER 1: [mysqld] datadir=/var/lib/mysql/ socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin=/usr/local/mysql/bin.log #binlog-do-db=database name # input the database which should be replicated binlog-ignore-db=mysql# input the database that should be ignored for replication binlog-ignore-db=test log-bin-index=/usr/local/mysql/log-bin.index log_slave_updates server-id=2 auto_increment_increment=2 auto_increment_offset=1 #information for becoming slave. master-host = 192.168.1.1 master-user = replication master-password = replication master-port = 3306 [mysql.server] user=mysql [mysqld_safe] err-log=/var/lib/mysql/mysql.log pid-file=/var/lib/mysql/mysql.privatedns.com.pid ON MASTER 2: [mysqld] datadir=/var/lib/mysql/ socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin=/usr/local/mysql/bin.log #binlog-do-db=database name # input the database which should be replicated binlog-ignore-db=mysql# input the database that should be ignored for replication binlog-ignore-db=test log-bin-index=/usr/local/mysql/log-bin.index log_slave_updates server-id=1 auto_increment_increment=2 auto_increment_offset=2 #information for becoming slave. master-host = 192.168.1.2 master-user = replication2 master-password = replication2 master-port = 3306 [mysql.server] user=mysql [mysqld_safe] err-log=/var/var/lib/mysql/mysql.log pid-file=/var/lib/mysql/mysql.privatedns.com.pid Please let me know if I need to add any parameter to enable this replication. Thanks in advance. -- Regards, Manasi Save Quoting Anand kumar : can you give us the configuration(.cnf) file from both the masters ? --Anand On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save manasi.s...@artificialmachines.com mailto:manasi.s...@artificialmachines.com wrote: Hi All, I have configured MySQL Master-Master Replication on my servers. When I am inserting or updating any data in a regular table the data is getting replicated. But When I am doing delete on that same table. the data is only getting deleted only on the server where I am doing delete. but it is not getting replicated on its slave. Even if I am doing truncate it is not getting replicated. Can anyone provide any input on this? Thanks in advance. -- Regards, Manasi Save
Re: MySQL Replication Delete is not gettting replicated
Hi Manasi Yes, you only need the repl_slave_priv, the show grants should give you something like: GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY PASSWORD '...' If thats ok, have check your binlog and relay binlog to see if them contain the delete statements? Im trying to figure out whats wrong. Carlos On 1/18/2010 11:06 PM, Manasi Save wrote: Dear Carlos, Thanks for the response. But I haven't gave any privileges besides repl_slave priv to user replication and replication2 respectively. So does that amke any difference really? Thanks in advance. -- Regards, Manasi Save Quoting Carlos Proal carlos.pr...@gmail.com: I dont see anything unusual or missing on your config file and as the only thing missing are deletes, i think that might be a permission issue. Can you check out the grants for your replication users and see if they have full permissions granted ? mysql show grants for x; where is x is replication and replication2 respectively. Carlos On 1/18/2010 1:35 AM, Manasi Save wrote: Hi Anand, Please find below my configuration file of both the masters: ON MASTER 1: [mysqld] datadir=/var/lib/mysql/ socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin=/usr/local/mysql/bin.log #binlog-do-db=database name # input the database which should be replicated binlog-ignore-db=mysql# input the database that should be ignored for replication binlog-ignore-db=test log-bin-index=/usr/local/mysql/log-bin.index log_slave_updates server-id=2 auto_increment_increment=2 auto_increment_offset=1 #information for becoming slave. master-host = 192.168.1.1 master-user = replication master-password = replication master-port = 3306 [mysql.server] user=mysql [mysqld_safe] err-log=/var/lib/mysql/mysql.log pid-file=/var/lib/mysql/mysql.privatedns.com.pid ON MASTER 2: [mysqld] datadir=/var/lib/mysql/ socket=/var/lib/mysql/mysql.sock old_passwords=1 log-bin=/usr/local/mysql/bin.log #binlog-do-db=database name # input the database which should be replicated binlog-ignore-db=mysql# input the database that should be ignored for replication binlog-ignore-db=test log-bin-index=/usr/local/mysql/log-bin.index log_slave_updates server-id=1 auto_increment_increment=2 auto_increment_offset=2 #information for becoming slave. master-host = 192.168.1.2 master-user = replication2 master-password = replication2 master-port = 3306 [mysql.server] user=mysql [mysqld_safe] err-log=/var/var/lib/mysql/mysql.log pid-file=/var/lib/mysql/mysql.privatedns.com.pid Please let me know if I need to add any parameter to enable this replication. Thanks in advance. -- Regards, Manasi Save Quoting Anand kumar : can you give us the configuration(.cnf) file from both the masters ? --Anand On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save manasi.s...@artificialmachines.com mailto:manasi.s...@artificialmachines.com wrote: Hi All, I have configured MySQL Master-Master Replication on my servers. When I am inserting or updating any data in a regular table the data is getting replicated. But When I am doing delete on that same table. the data is only getting deleted only on the server where I am doing delete. but it is not getting replicated on its slave. Even if I am doing truncate it is not getting replicated. Can anyone provide any input on this? Thanks in advance. -- Regards, Manasi Save -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HELP! RESET MASTER hosed replication
The issue is that replication relies on this logs !!!, so when you deleted them . Generally speaking you have to: stop the slave sync the master with the slave (there are several ways to do this and depending how busy is your master) grab the master status (position) change the slave to point to the master new position start the slave Hope this helps. Carlos On 12/23/2009 2:20 PM, Daevid Vincent wrote: I got an alert that one of the drives was filling up (3% free). So I figured out that a large chunk was from /var/log/mysql r...@pse10:~# find / -type d -print0 | xargs -0 -n1 du -sk | sort -rn | head -n20 ~/dir-sizes.txt r...@pse10:~# cat ~/dir-sizes.txt 159121012 / 70442396 /var 70127764 /var/log 69991160 /var/log/mysql big offender 56307436 /data 31479936 /home 29386076 /data/mysql 26899784 /data/archive It looked like the /var/log/mysql was pretty full of these Mysql-bin.00 log files, ... -rw-rw 1 mysql adm 105019928 2009-12-23 05:07 mysql-bin.001196 -rw-rw 1 mysql adm 105004751 2009-12-23 05:08 mysql-bin.001197 -rw-rw 1 mysql adm 104978518 2009-12-23 05:10 mysql-bin.001198 -rw-rw 1 mysql adm 104949073 2009-12-23 05:11 mysql-bin.001199 -rw-rw 1 mysql adm 104925795 2009-12-23 05:13 mysql-bin.001200 -rw-rw 1 mysql adm 104974354 2009-12-23 05:14 mysql-bin.001201 -rw-rw 1 mysql adm 105089249 2009-12-23 05:16 mysql-bin.001202 -rw-rw 1 mysql adm 105165487 2009-12-23 05:17 mysql-bin.001203 -rw-rw 1 mysql adm 104926853 2009-12-23 05:19 mysql-bin.001204 -rw-rw 1 mysql adm 105139076 2009-12-23 05:20 mysql-bin.001205 -rw-rw 1 mysql adm 104891552 2009-12-23 05:22 mysql-bin.001206 -rw-rw 1 mysql adm 104959626 2009-12-23 05:25 mysql-bin.001207 -rw-rw 1 mysql adm 104883048 2009-12-23 05:27 mysql-bin.001208 -rw-rw 1 mysql adm 104993511 2009-12-23 05:28 mysql-bin.001209 -rw-rw 1 mysql adm 104945974 2009-12-23 05:30 mysql-bin.001210 -rw-rw 1 mysql adm 35468892 2009-12-23 05:30 mysql-bin.001211 -rw-rw 1 mysql adm 21728 2009-12-23 05:30 mysql-bin.index -rw-r- 1 mysql adm 12836 2009-12-23 00:12 mysql-slow.log ... so I took the liberty of resetting them... http://dev.mysql.com/doc/refman/5.0/en/reset.html vince...@pse10 /var/log/mysql $ dbroot (r...@localhost) [(none)] RESET MASTER; vince...@pse10 /var/log/mysql $ ll total 2792 -rw-rw 1 mysql adm 2801618 2009-12-23 05:35 mysql-bin.01 -rw-rw 1 mysql adm 32 2009-12-23 05:35 mysql-bin.index -rw-r- 1 mysql adm 14987 2009-12-23 05:35 mysql-slow.log -rw-r- 1 mysql adm1102 2009-12-22 00:13 mysql-slow.log.1.gz -rw-r- 1 mysql adm 891 2009-12-21 00:02 mysql-slow.log.2.gz -rw-r- 1 mysql adm1318 2009-12-20 00:02 mysql-slow.log.3.gz -rw-r- 1 mysql adm 687 2009-12-19 00:02 mysql-slow.log.4.gz -rw-r- 1 mysql adm5246 2009-12-17 20:38 mysql-slow.log.5.gz -rw-r- 1 mysql adm 156 2009-12-16 06:25 mysql-slow.log.6.gz -rw-r- 1 mysql adm1114 2009-12-15 16:26 mysql-slow.log.7.gz Which freed up a tremendous amount of space again... However, a co-worker informed me that now our slaves are broken and replication is hosed! What did I do wrong or forget to do? I see no mention of something I was supposed to do for replication scenarios... Was I supposed to RESET SLAVE too? As of right now, /var/log/mysql has grown to mysql-bin.28 since last night when I reset it. How do I recover from this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: HELP! RESET MASTER hosed replication
Yep, It might not be needed to sync, but its better to double check ;). Carlos On 12/23/2009 2:44 PM, Claudio Nanni wrote: In this case it should not be needed to sync the slave, Resetting the master basically broke the 'pipe' but events are still on the new binary logs, it might have lost some event in some unfortunate case, but anyway it is probably for now to put slave back on track, check and eventually resync. Ciao Claudio On 23 dec 2009 21:35, Carlos Proal carlos.pr...@gmail.com mailto:carlos.pr...@gmail.com wrote: The issue is that replication relies on this logs !!!, so when you deleted them . Generally speaking you have to: stop the slave sync the master with the slave (there are several ways to do this and depending how busy is your master) grab the master status (position) change the slave to point to the master new position start the slave Hope this helps. Carlos On 12/23/2009 2:20 PM, Daevid Vincent wrote: I got an alert that one of the drives was filling...
Re: Removing MySQL to install new version
Yes, you only have to delete the directories. Carlos On 11/12/2009 8:16 AM, Miguel Cardenas wrote: Hello I noticed that mysql 6 alpha is no longer available so I want to remove it from my laptop and install the latest 5.x version. I have just restored my Linux system image so my database is clean and empty and is a good moment to do it. My question is, how can I remove it completely from my disk to make a new clean installation? The current installation is from source code in /usr/local/mysql and as far as I know everything is under this directory except /etc/my.cnf Is it enough to delete that directory and file? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL alternative to PostgreSQL's pgpool2?
Hi Tomasz There are several solutions for this, one of them, maybe the most popular, is MySQL proxy, take a look to see if solves your issues. http://forge.mysql.com/wiki/MySQL_Proxy Carlos* * On 10/25/2009 12:41 PM, Tomasz Chmielewski wrote: With PostgreSQL, I can use pgpool2[1] tool as a load balancer/replicator for queries: postgres_server_1\ pgpool2 --- client postgres_server_2/ Generally, pgpool will query either of the SQL servers to load balance (and failover, if one of them goes down), and send writes to both servers (replication) so that their content is identical. With MySQL, what tool should I use to achieve a similar setup? [1] http://pgpool.projects.postgresql.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Some MySQL questions
On 10/8/2009 4:19 PM, John Oliver wrote: 1) When I select * from whatever; is there a way to have the results go by one screen at a time? You can limit output by delimiting your search ie select * from users limit x,y; x=offset y=rows after the ofset 2) In reference to the above, is there a way to just display the row that shows the names of each column? I guess you are talking about describe to show table attributes describe users; I need to drop one row from a table with a few thousand rows. I guessing I want to: delete from 'users' where COLUMN_NAME = 1898; Is that right? I'm not sure if COLUMN_NAME is uid or id or maye something else, and since I can't pipe it through more or less... :-) Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
You have to reset the permissions. http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Carlos On 8/25/2009 7:12 PM, Joe wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: error 135
This might help http://dev.mysql.com/doc/refman/5.0/en/repair.html Carlos On 8/10/2009 11:51 PM, Ron wrote: Hi All, why do i have this error. i have recently setup a mysql cluster, i now have around 60 tables on it. when i create a new one, it gives me this error. ERROR 1005 (HY000): Can't create table './mydb/#sql-3e87_24c9a5d.frm' (errno: 135) any idea? TIA! Regards Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Remote connection
Are you sure that root has granted access to connect remotely ?? Carlos On 8/4/2009 3:09 PM, Hugo Leonardo Ferrer Rebello wrote: Hello Guys, I have a big doubt. I'm trying to access the mysql database remotely, but I can't. I have changed the skip-networking option on my.cnf file however it doesn't work. I have tried to include the bind_address = 0.0.0.0 but it still doesn't work. Sure I have commented the skip-networking option before enable the bind_address. I don't know what else I must do. Please, anybody can help me ? Look at the error message below. # mysql -u root -p -h 192.168.12.50 Enter password: ERROR 1045 (28000): Access denied for user 'root'@'192.168.12.50' (using password: YES) Cheers, Hugo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Copy 70GB ibdata, etc. and server won't start now
Another thing you can do is simply erase the innodb logs. If the dbms really was shutdown properly then the logs are useful, they will be recreated again and you can go forward with your demo. Carlos On 7/14/2009 6:23 PM, Daevid Vincent wrote: Not sure what you mean start it against the USB drive. I copied from 'old' - USB - 'new' /var/lib/mysql/*. At no time did I run the database pointing at the USB drive (is that even possible? I guess a symlink or something maybe with mods to app armor). The 'old' is growing and changing dates because it is still live and actually in production. This 'new' box is going to be used for a demo at a conference and is therefore a copy from about a month ago and will not grow in size, but the dates will change as I muck with the data on it. From: Gary Smith [mailto:g...@primeexalia.com] Sent: Tuesday, July 14, 2009 2:11 PM To: Johnny Withers; Daevid Vincent Cc: mysql@lists.mysql.com Subject: RE: Copy 70GB ibdata, etc. and server won't start now Johnny, I'm less worried about the month in between than the fact that all of the dates for the files on his USB data should be roughly the same. It looks to me like he tried to start it against the data on the USB drive. Another question is, was this an cold backup or hot backup? If this were a hot backup, I could see this problem happening. If it were a could backup, it should work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Copy 70GB ibdata, etc. and server won't start now
Uppps i mean...useless instead of useful. On 7/14/2009 7:11 PM, Carlos Proal wrote: Another thing you can do is simply erase the innodb logs. If the dbms really was shutdown properly then the logs are useful, they will be recreated again and you can go forward with your demo. Carlos On 7/14/2009 6:23 PM, Daevid Vincent wrote: Not sure what you mean start it against the USB drive. I copied from 'old' - USB - 'new' /var/lib/mysql/*. At no time did I run the database pointing at the USB drive (is that even possible? I guess a symlink or something maybe with mods to app armor). The 'old' is growing and changing dates because it is still live and actually in production. This 'new' box is going to be used for a demo at a conference and is therefore a copy from about a month ago and will not grow in size, but the dates will change as I muck with the data on it. From: Gary Smith [mailto:g...@primeexalia.com] Sent: Tuesday, July 14, 2009 2:11 PM To: Johnny Withers; Daevid Vincent Cc: mysql@lists.mysql.com Subject: RE: Copy 70GB ibdata, etc. and server won't start now Johnny, I'm less worried about the month in between than the fact that all of the dates for the files on his USB data should be roughly the same. It looks to me like he tried to start it against the data on the USB drive. Another question is, was this an cold backup or hot backup? If this were a hot backup, I could see this problem happening. If it were a could backup, it should work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Copy 70GB ibdata, etc. and server won't start now
Yep, you dont need to recover the database because: 1) You did a clean shutdown 2) You dont need (as i believe) an integral copy of your database, because its for a demo, anyway the data is 1 month older, so if you miss some records that probably will be missing on the log those are not relevant for your purpose, right ? Carlos On 7/14/2009 9:08 PM, Daevid Vincent wrote: -Original Message- From: Carlos Proal [mailto:carlos.pr...@gmail.com] Sent: Tuesday, July 14, 2009 5:13 PM Another thing you can do is simply erase the innodb logs. If the dbms really was shutdown properly then the logs are useful, they will be recreated again and you can go forward with your demo. You're suggesting I can delete these two files? -rw-rw 1 mysql mysql 5242880 2009-07-14 19:18 ib_logfile0 -rw-rw 1 mysql mysql 5242880 2009-07-14 18:30 ib_logfile1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Copy 70GB ibdata, etc. and server won't start now
On 7/13/2009 9:19 PM, Daevid Vincent wrote: Both systems are UTC time so I don't get the issue with the sequence number is in the future business either. If I ever do get mysqld to start using the innodb_force_recovery = 4 line, then as you know, I can't alter/update/insert. And it seems any attempt to do so further corrupts the database and I have to re-copy the 70GB files again. :-\ I think level 4 is not the appropiate, maybe you can try level 3 or 5 which are more convenient to skip the log, then you can check if everything works fine (aka review you tables integrity) if it is, then restore the default value so the next restart would make the recovery if neccesary. Hope this helps. Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with configuring 32-bit MySQL 5.0 on Windows Vista x64
Did you deleted the data dir (inside Program Files) after uninstall ?? i think that is was not empty and when the new install tries to set up finds it and get messy (maybe because the 32 and 64 bit issue). Carlos On 7/13/2009 11:07 PM, Edward Diener wrote: I had previously installed the 64-bit version of MySQL 5.1 server under Windows Vista x64 and both the installation and configuration were successful. Then for compatibility reasons with something on which I am working I realized I needed to install the 32-bit version of MySQL 5.0 server. Since I did not see anything about installing both on the same machine, I decided to uninstall the 64-bit version and then install the 32-bit version. The uninstall ran successfully. When I installed MySQL 5.083 32-bit under Windows Vista x64 it installed successfully. When I try to configure an instance of that version, all steps work successfully until the Apply Security Settings step, which fails. The failure message is: The security settings could not be applied to the database because the connection has failed with the following error. Error Nr. 1045 Accessed denied for user 'root'@'localhost' [using password: YES]' I have never changed the password from the previous 64-bit version I installed and then uninstalled and the 32-bit version I installed. Any ideas of solving this problem ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What to Download????
On 7/11/2009 10:09 AM, Michael Abbott wrote: Hi Folks, can someone give me some info on what I need to download here?? I just bought a new 64 bit laptop Im sitting on the MySQL dowload site and wondering if Im looking at the correct package to download Im at Windows x64 downloads (is this for 64 bit machines) Yeah you are in the right place I have it appears 3 options Windows Esentials (AMD64 / Intel EM64T) Windows MSI Installer Without Installer This depends in what you prefer, i like the msi installer, but is a personal choice. What else might I need to download in order to run a Java Application on MySQL ... I know I need the JDBC drivers, but anything else from MySQL? Nothing else, but you may want a nice interface to create modeling (ie MySQL workbench) or a nice admin and query interface (ie GUI Tools ) Also, obviously a nice development IDE like Eclipse or Netbeans :) Carlos Proal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: XAMMP-MySQL crashing
You have to take a look on the error log, a file named hostname.err (hostname=your machine name) and should be located inside the mysql dir on the xammp dir. That file will give an insight about the problem, or you can post the error here to get help. Carlos On 7/11/2009 11:52 AM, Gary wrote: I have an issue that Mysql will not start on my local machine. I noticed a few days ago on a restart that I got an error saying that Mysqld had encountered a problem and had to close. I did not pay any attention to it because I was not working in it for a bit. I then tried to start it today and got the same error message saying mysqld.exe has encountered a problem and had to close. + Apache 2.2.11 + MySQL 5.1.30 (Community Server) + PHP 5.2.8 + PEAR (Support for PHP 4 has been discontinued) + PHP-Switch win32 1.0 (use php-switch.bat in the xampp main directory) + XAMPP Control Version 2.5 from www.nat32.com + XAMPP Security 1.0 + SQLite 2.8.15 + OpenSSL 0.9.8i + phpMyAdmin 3.1.1 + ADOdb 4.990 + Mercury Mail Transport System v4.52 + FileZilla FTP Server 0.9.29 + Webalizer 2.01-10 + Zend Optimizer 3.3.0 + eAccelerator 0.9.5.3 für PHP 5.2.8 (but not activated in the php.ini) Anyone have an idea to where I should start to look? Thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: XAMMP-MySQL crashing
Take a look on the my.ini to see if there is something wrong (maybe a path pointing to a missing place or memory setting bigger than your actual RAM) Carlos On 7/11/2009 12:17 PM, Gary wrote: Not sure if this is it, it was the only .err file that I have. Also this seems to be the latest entry that has a date. Thanks for your help. Gary 090617 21:50:45 - mysqld got exception 0xc005 ; 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=16777216 read_buffer_size=262144 max_used_connections=0 max_threads=151 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 133305 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x0 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... 006B8853mysqld.exe!??? The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 090627 9:35:07 - mysqld got exception 0xc005 ; 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. Carlos Proal carlos.pr...@gmail.com wrote in message news:4a58c79b.2090...@gmail.com... You have to take a look on the error log, a file named hostname.err (hostname=your machine name) and should be located inside the mysql dir on the xammp dir. That file will give an insight about the problem, or you can post the error here to get help. Carlos On 7/11/2009 11:52 AM, Gary wrote: I have an issue that Mysql will not start on my local machine. I noticed a few days ago on a restart that I got an error saying that Mysqld had encountered a problem and had to close. I did not pay any attention to it because I was not working in it for a bit. I then tried to start it today and got the same error message saying mysqld.exe has encountered a problem and had to close. + Apache 2.2.11 + MySQL 5.1.30 (Community Server) + PHP 5.2.8 + PEAR (Support for PHP 4 has been discontinued) + PHP-Switch win32 1.0 (use php-switch.bat in the xampp main directory) + XAMPP Control Version 2.5 from www.nat32.com + XAMPP Security 1.0 + SQLite 2.8.15 + OpenSSL 0.9.8i + phpMyAdmin 3.1.1 + ADOdb 4.990 + Mercury Mail Transport System v4.52 + FileZilla FTP Server 0.9.29 + Webalizer 2.01-10 + Zend Optimizer 3.3.0 + eAccelerator 0.9.5.3 für PHP 5.2.8 (but not activated in the php.ini) Anyone have an idea to where I should start to look? Thanks Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Hi Carlos Try this mysql update user set password=password('letmein') where user='root'; This way the password is saved encrypted, thats the way is compared when you try to log in. Carlos On 5/14/2009 5:28 PM, Carlos Williams wrote: I noticed today that I strangely was unable to login to MySQL as root. I just assumed I forgot the password and decided to reset my root password: 1 - /etc/init.d/mysqld stop 2 - mysqld_safe --skip-grant-tables 3 - mysql -u root 4 - mysql use mysql; mysql mysql update user set password='letmein' where user='root'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql flush privileges; mysql quit 5 - /etc/init.d/mysqld restart *Now after I do all that, I get the following:* mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) I have done this over and over and can't login so I am wondering if something is wrong with MySQL or am I just not properly resetting the password... Someone please help! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. Carlos On 5/14/2009 5:39 PM, Carlos Williams wrote: On Thu, May 14, 2009 at 6:31 PM, Carlos Proal carlos.pr...@gmail.com wrote: Hi Carlos Try this mysql update user set password=password('letmein') where user='root'; This way the password is saved encrypted, thats the way is compared when you try to log in. Thanks for the reply! I followed both methods in the following URL / link: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix After doing both successfully, I was unable to login over and over. I think something is wrong with MySQL. I have never seen this before :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
The machine mysql.unixslut.com is not the same than localhost, right ??, If you only need root access from localhost you can delete the first row (delete from user where user='root' and host='mysql.unixslut.com';) Carlos On 5/14/2009 5:55 PM, Carlos Williams wrote: On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Duplicate values inserted!
Seems fine to me, i guess the problem is somewhere else. Maybe you should try a full trace in Eclipse to detect the issue. Carlos jean claude babin wrote: Hello, I'm using Eclipse 3.3 and I use a model class HandlereplyModel.java to insert values to a mysql database using mysql5.0 server.When I run my application, I got duplicate values everytime I enter a record. my java class is : import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class HandlereplyModel { public String insert(String fname, String lname, String Email, String phone) { /* Create string of connection url within specified format with machine name, port number and database name. Here machine name id localhost and database name is myfanclub. */ try { String url = jdbc:mysql://localhost:3306/myfanclub; /* myfanclub is my new database that contains my MemberDetails table */ // Load JBBC driver com.mysql.jdbc.Driver Class.forName(com.mysql.jdbc.Driver).newInstance(); /* create a connection to the database by using Connection interface and java.sql.DriverManager's getConnection( ) method */ Connection conn = DriverManager.getConnection(url,root,root); /* createStatement() is used for create statement object that is used for sending sql statements to the specified database. */ Statement st = conn.createStatement(); // sql query to insert values in the specified table st.executeUpdate(INSERT into MemberDetails (First_name, Last_Name, E_mail,Phone_num) VALUES ( ' + fname + ' , ' + lname + ' , ' + Email + ' , ' + phone + ' ); ); st.close(); conn.close(); } catch (Exception e) { returnFailure; } return Success; In Handlereply servlet we can check if the result of this function is success, and if it is, serve back the SuccesPage.jsp } } Please,advise... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create table
Hi. group is a reserved word i think you must enclose the table name with single quotes (as when you created the table) in order to avoid the confusion. Carlos Krishna Chandra Prajapati wrote: Hi, I have created a table name group. CREATE TABLE `group` ( `group_id` int(11) NOT NULL, `group_name` varchar(128) NOT NULL, `date_created` datetime NOT NULL, `created_by` int(11) NOT NULL, `modified_by` int(11) default NULL, `status` char(1) NOT NULL default 'Y', PRIMARY KEY (`group_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table was successfully created without any errors. After that when i try to describe it. Its giving error. mysql desc group; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group' at line 1 mysql drop table group; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group' at line 1 Does the problem is with table name. Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table crashes
The manual is your friend for everything, besides any additional book like Paul Dubois (cool book). But, in order to help you, we need more information about the crash: errors, version, table type, hardware, logs, etc. in that way we can offer solutions to your specific issue. feel free to send us more details and we'll be happy to help. Carlos [EMAIL PROTECTED] wrote: Can someone help me out on this..I need a detailed document Regards, Amarnath Shivashankar SQL Database Management | GSMC | Wipro Infotech | Mysore | Toll free: 1800-345-5656 | Spirit of Wipro : Intensity to Win | Act with Sensitivity | Unyielding Integrity From: Amarnath Shivashankar (WI01 - Services) Sent: Tuesday, October 23, 2007 11:40 AM To: 'mysql@lists.mysql.com' Subject: Table crashes Hi Friends, I need a document on why tables crash in MYSQL and what are the repair methods? I would also like to know if there are any preventive measures to avoid table crashes? Regards, Amarnath Shivashankar SQL Database Management The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
One common error is trying to do this inside the mysql console, instead of the shell prompt, i think thats your problem because at least the first one is correct. Carlos Red Hope wrote: Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Well hopefully, typing quit at the mysql console get you back to the shell. Otherwise, you need to open a Gnome Console, KDE Konsole or Windows CommandPrompt from the different menus on your operating system If the mysql directory is on your PATH you can use mysqldump anywhere, if not, move to the mysql/bin directory and then you can run it without any problem. Hope that help. Carlos Red Hope wrote: Oh boy, and here's the big stupid question. How do I get to the shell prompt? Sorry! Yea, I'm that newbie. :) Thank you, Lillian --- Carlos Proal [EMAIL PROTECTED] wrote: One common error is trying to do this inside the mysql console, instead of the shell prompt, i think thats your problem because at least the first one is correct. Carlos Red Hope wrote: Hey y'all, I've been fussing with the MySQL dump procedure to backup my stuff. I'm on MySQL 5.0.41 and I have tried so hard to get it to dump my files into .sql format but my syntax is wrong, wrong, wrong. lol I put below examples what I've been entering to get my databases backed up. I always get an error or I get locked into the - thing if I don't use the semicolons. Any help would be awesome! Thank you, Lillian Tried Wrong mysqldump -u root -ppassword test test.sql mysqldump -u root -ppassword test test.sql; mysqldump -u root -ppassword -databases test test.sql; Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos more. http://mobile.yahoo.com/go?refer=1GNXIC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost Connection
This can be a serious issue, maybe a sign of some kind of data corruption. If you lost the connection, probably the server is restarting (you can check that on the error log), do this happen only with this table ? Carlos krishna chandra prajapati wrote: Hi All, I have connect to mysql-server using client. When i run the query it give message lost connection to mysql. The details is as below. mysql select user_id, user_name from user_info; ERROR 2013 (HY000): Lost connection to MySQL server during query. System configuration is Pentium 2.6 Ghz Ram 1 GB My.cnf key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K thread_cache_size = 8 max_connections= 50 #table_cache= 64 #thread_concurrency = 10 sort_buffer_size= 1M innodb_buffer_pool_size = 70M innodb_log_buffer_size = 2M innodb_additional_mem_pool_size = 8M table_cache = 64 thread_cache_size = 8 read_buffer_size= 1M read_rnd_buffer_size= 2M myisam_sort_buffer_size = 4M # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size= 16M I don't understand why the connection get lost during simplest query also. Regards, Krishna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql dump help!
Yep, good for you, welcome to the real world You are changing the prompt but are still inside the dbms, you need to get out, because mysqldump is an application (.exe file) not a sql command, ie Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.0.41-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql mysql quit Bye D:\lillianmysqldump -u root -ppassword test test.sql; -- but maybe after the quit, the window will close, if that happens, open a new command prompt from start - programs - accesories -- command prompt and then run mysqldump Carlos Red Hope wrote: Hey y'all, I use charming Windows XP on here. I've taken database classes but lucky for me we never used *real* MySQL. Below I typed up exactly what I put into the MySQL prompt and this is what it kicks back to me. Lillian mysql mysql \R shell PROMPT set to 'shell' shell shellmysqldump -u root -ppassword test test.sql; ERROR 1064 4200: You have an error in your SQL syntax; checkthe manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -ppassword test test.sql' at line 1 --- Carlos Proal [EMAIL PROTECTED] wrote: Can you email us the complete command and the error ? Carlos Red Hope wrote: Well, that went over my head. :) I understand what you're telling me, how to get there, but not how to do it. bleh. When I start up MySQL Command Line Client, I'm always prompted at mysql. So I told it to switch from that prompt to shell prompt. It always starts up in mysql prompt. Once I'm in shell, I tried the dump procedure and it kept saying it couldn't connect. So I checked what databases it had, it shows them. I can't even switch to a database because of no connection. I'm not exactly sure why there's no 'connection' at all. Thanks for trying so hard, Carlos! Lillian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Got a little couch potato? Check out fun summer activities for kids. http://search.yahoo.com/search?fr=oni_on_mailp=summer+activities+for+kidscs=bz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error in login
Hola Hector, el problema son las 2 tuplas que mencionas, ya que debido a su jerarquia pueden hacer un conflicto que te evita conectarte correctamente, en tu caso si deseas que el usuario se conecte desde donde sea (%) elimina la tupla de la tabla user donde aparezca el localhost, esto puede ser en la table user o en la tabla db de la base de datos mysql. Hector, the problem is maybe that you have 2 rows granting permissions to this user and is possible that due to hierarchies or precedence one overrides each other. I suggest to eliminate the localhost entry and leave the % because anyways you are allowing this user to connect from anywhere including the localhost. This problem can be on the user or db tables of the mysql database. Carlos Héctor S. Mendoza O. wrote: The problem persists, with /usr/bin/mysql or with a php application, same sympthoms TIA Hector -Mensaje original- De: Gerald L. Clark [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 09 de Julio de 2007 02:54 p.m. Para: Héctor S. Mendoza O. CC: mysql@lists.mysql.com Asunto: Re: error in login Héctor S. Mendoza O. wrote: Hello all… I’ve scratched my head for a while on this one, so if someone could shed some Light on it I would appreciate it very much I have an account setup, and know about the password settings regarding hosts. The account is named web19_u1, and has listed ‘%’ and ‘localhost’ as posible hosts to be able to log in On remote machines I do a “mysql -u web19_u1 -p -h10.10.10.2 –p web19_db1” and have no problems on querying the tables On the local host (where the mysqld resides) I do the same command and get a “ERROR 1045 (28000): Access denied for user 'web19_u1'@'db1.intranet' (using password: YES)”. If I change this to –hlocalhost I can login correctly. My problem is that the application (php based) resides both on local intranet and in internet (two different servers), remote machine is in intranet and needs ip 10.10.10.2 to access database, and local host (mysqld resides in) can only use localhost. Development and testing of application is done in intranet so basically I need two different configuration files depending on where the application is running to access the database. I’ve tried removing the ‘localhost’ from the user and only leaving ‘%’ with no difference at all. I’ve also tried the –protocol=TCP option with no luck either. Any pointers? TIA Hector S. Mendoza O PS. mysqld Ver 5.0.27 Use a my.cnf file in the user's home directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you allow external computers to access server instance?
Mysql restricts access outside the server after the installation (on not all, but several cases) so i suggest to connect to the mysql database as root and review the host values on the user table, that can give you an idea of who is allowed and from where is allowed ...remeber that % means anywhere. If you need more information i suggest to read the manual searching by users permissions. Carlos Ferindo Middleton wrote: I found how to bind to addrees to but didn't find anything in my.ini about skip-networking but now I have this problem where the I can't connect locally sitting at the computer using hostname localhost if I type in the IP address of the computer I get a messsage saying {Hostname} is not allowed to connect to this MySQL server Ferindo On 6/21/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Ferindo Middleton wrote: I've installed MySQL5 on a machine running Windows XP. I'm not an advanced user so I chose all the default configuration settings when I went through the setup wizard for the server instance. The database works fine and I can access it when I'm sitting at the computer through the command line client and MySQL Query Browser. However, if I try to access the server instance from another computer on my LAN via MySQL Query Browser, I get a message saying the connection is refused How do I configure the server to allow incoming connections from other computers on my network? Try to configure bind-address to the server's IP address, and ensure skip-networking is not defined. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to either update or insert in a single query?
Have you look at replace ? http://dev.mysql.com/doc/refman/5.0/en/replace.html Carlos Douglas Pearson wrote: Apologies if this is a dumb question, but is it possible to write a single query that either updates certain columns in a row, or adds an entirely new row if there is none already? I seem to be running into this a lot, and so far I've solved it by: 1) run UPDATE table SET x,y WHERE some row 2) if rowsChanged == 0 then run the INSERT It just feels like there must be a way to do this more efficiently. Thanks, Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert data in to columns base on the selection of the list box.
Can you tell us what exactly is your problem ? ie returned error, logic, or what ? My first impression es that your insert is wrong, because inserts cant have where conditions (it makes no sense) probably you want to do a completely new insert including the column1 or maybe you want an update. Anywhere you can check the manual for sintax issues. Carlos sam rumaizan wrote: Can you help me please? 1-I have created a while loop to populate the list box with the information of column1. 2-I need to update (insert data) in to column2, column3, column4 base on the selection of the list box. echo'form'; $query = SELECT column1 FROM table; $result = mysql_query($query); echobr; echobr; echocenter; echoselect NAME='R'; echooption value='NULL'Choose a Category:/option ; while ($line = mysql_fetch_array($result)) { foreach ($line as $value) { echoOPTION value='$value'; } echo $value/OPTION; } echo /select; echo /form; $sql=INSERT INTO table WHERE column1='.$_POST[R].' (column2, column3, column4)VALUES('info2', 'info3', 'info4'); $result=mysql_query($sql); - No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thank you for the free ride
As probably you saw in the announcement (http://jcole.us/blog/archives/2007/03/31/mysql-conference-expo-free-ride-winners/), im one of the winners of the free ride to the Mysql Conference. So, i just want to say thanks to Jeremy Cole, Proven Scaling and the whole Mysql team for the opportunity to go and share experience with other people involved on the databases world. I really hope to meet some great guys from Mysql as Mark Matthews, Brian Aker, Jim Starkey, Heikki Tuuri, Stewart Smith and non mysql people like John Newton from Alfresco and the Pentaho's group and Zmanda crew (just to mention a few). See ya at the conference :) Carlos Proal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating a Table With a Default
It makes no sense to use a default value with an auto_increment attribute, which means, the default value is the auto-incremented value. Carlos Al Sparks wrote: I'm trying to install ProBIND, and I'm running mysql 4.1.20. One of the ProBIND install scripts calls for tables to be created in MySQL, and I've culled it down to this: mysql CREATE TABLE zones ( - id INT(11) DEFAULT '1' NOT NULL AUTO_INCREMENT, - PRIMARY KEY (id) - ); ERROR 1067 (42000): Invalid default value for 'id' I've tried double quotes, changing it to INT(1), and still I get that error. Any hints? === Al -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem Installation
The log files are named hostname.err and must be located inside your data dir, which can be inside your /usr/local directory or in the /var/ Carlos Scott Johnson wrote: 20Hi; I had a working installation of MySQL, and then I zapped it. I found out the hard way that when you delete symlinks, you also delete the files to which they are linked :( I wiped my entire /usr/local/include dir. I'm using ports on FreeBSD 6.2. As it happens, I had a complete copy of the identical include dir on another machine, so I tarballed it and copied it over. Unfortunately, that didn't seem to help. I rebuild MySQL from ports and everything looks fine. But when I try to fire it up I discover it's not up. When I run /usr/local/bin/mysqld_safe it starts up and immediately dies. What log file would tell me what happened? Also, strangely, if I move the /usr/local/include/mysql folder and rebuild MySQL, it *doesn't* rebuild that folder! I would have assumed that folder would have been built in the installation of MySQL. Please advise. TIA, Scott No need to miss a message. Get email on-the-go with Yahoo! Mail for Mobile. Get started. http://mobile.yahoo.com/mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Growing innodb size
You need to close the size of ibdata1 and add the second file ibdata2 with the size you need (and i you like you can even add the autoextend option, but only to the last ibdata file added) for example, if you are aproaching the 1gb size on ibdata1, you specify the maximun size that you want for that file and then add the second file: innodb_data_file_path = ibdata1:988M;ibdata2:10G:autoextend For further information check: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html Carlos Jean-Sebastien Pilon wrote: The following does not work, it gives a syntax error. innodb_data_file_path = ibdata1:10M:autoextend;ibdata2:10240M I tried this instead and it didn't want to start and mysql crashes, is it because it expects the data to be located in the 1st datafile ? innodb_data_file_path = ibdata2:10240M;ibdata1:10M:autoextend Then I tried with the following option in my.cnf as suggested on mysql website innodb_force_recovery = 4 It still crashes Works good when I put only the following innodb_data_file_path = ibdata1:10M:autoextend Any idea someone ? -Original Message- From: Jean-Sebastien Pilon [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 12:53 PM To: Nils Meyer; mysql@lists.mysql.com Subject: RE: Growing innodb size +---++ | Variable_name | Value | +---++ | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | /var/lib/mysql/| +---++ There is one defined, since I have innodb tables in there, very small ones. But the one I need know will grow easily to 5GB of data and will archive rows that are 30+ days old once every week. Since I am not running out of space on the machine, I though I could give 10GB to the datafile that will contain this new table... Should I do something like ? innodb_data_file_path = ibdata1:10M:autoextend;ibdata2:10240M Will this keep the data in ibdata1 intact ? Is it good to create a separate tablespace like this ? -Original Message- From: Nils Meyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 11:33 AM To: mysql@lists.mysql.com Subject: Re: Growing innodb size Hi, Jean-Sebastien Pilon wrote: I would like to grow my innodb table space, the only problem that I have is that I did not declare any size in the config file since we were not using it to start with. If I modify the config file, will this override the current innodb file or will it grow it ? Or should dump all the data, modify the config file and re-import everything ? Chances are that innodb isn't even functional if you did not specify a tablespace any way. You can check with: mysql SHOW VARIABLES LIKE LIKE 'innodb_data%'; If there is a data path given you'll see how it has been set up. Do you want a fixed size or should it grow automatically? Anyways, you might want to check this documentation: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et n’est destiné qu'à la personne à qui il est adressé. Si vous n’êtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement l’expéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt d’erreur puisque les informations qu’il contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: Duplicate entry 'xxxx-L' for key 6
i think you are in fact duplicating an entry on the index, probably one of your indexes on that table is length restricted and that cause that 2 fields that as a whole are different, they are not to the index length. My two cents. Carlos On 1/3/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I'm gettig it on browser after I run php code for adding new products. http://www.vernoncompany.com/screen/PrntScrn.gif Where is the error message itself coming from? Is MySQL itself generating any error? I ask because that error message shouldn't be coming from MySQL. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 03, 2007 4:10 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: RE: Error: Duplicate entry '-L' for key 6 Yes, it's custom made app, written in php. The code/query is really very simple: INSERT INTO products (prod_no, prod_name, prod_description, prod_colors, prod_includes, prod_catalog, prod_supplier, prod_start_date, prod_end_date, prod_status) VALUES ('H846', 'Lavender Peak Purse', 'Large main top load opening for easy access, additional slim detachable strap for hands-free carrying, 420D/PVC backed lining, comfortable, long, easy-carry handles. Dimensions: 12 inches L X 4 inches W X 7.75 inches H.', '03 Blk, 04 Brn, 05 Clay', 'Included one location debossed imprint. Die/setup $130.00(a) Second location $6.00(a)', '2007 Canyon Outback', 'Canyon Outback', '-00-00', '-00-00', 'new' ) Duplicate entry 'Lavender Peak Purse-L' for key 6 Nothing special. :( Are you using a custom-written application to do the loading? If so, what language is it written in? I suspect that some of your variables are getting clobbered by a bad subscript, bad pointer, so something like that. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 03, 2007 3:45 PM To: mysql@lists.mysql.com Subject: Error: Duplicate entry '-L' for key 6 Hi, While we were using old hosting company (15 days ago) on php 4.3.4/mysql 4.x based Linux server, the application I wrote to upload new products i MySQL was working fine. After we moved now I'm getting strange error I can't explain. I think it's caused by moving to mysql 5. For example: in the application csv file is uploaded to the server, then it reads product no. from product row and search if such exosts in DB. If yes, add prod. number to message that will list all products that admin tried to upload but they already exists. But, I'll sometimes get the error message, e.g.: Duplicate entry 'Lavander Peak Purse-L' fo r key 6 The thing I don't understand is 'Lavander Peak Purse' (without -L) is product name and it's not index nor unique?!? And there is no such a product in DB?!? Why is '-L' added to the products? What does it mean? What's key 6? I tried to find answer on mysql.com but only think I found is Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry '%s' for key %d CREATE TABLE `web_products` ( `prod_id` int(8) unsigned NOT NULL auto_increment, `prod_no` varchar(50) NOT NULL default '', `prod_name` varchar(255) NOT NULL default '', `prod_description` text, `prod_colors` text, `prod_includes` text, `prod_catalog` varchar(45) default NULL, `prod_status` enum('hidden','live','new') NOT NULL default 'new', `prod_supplier` varchar(45) default NULL, `prod_start_date` date default '-00-00', `prod_end_date` date default '-00-00', `prod_featured` enum('0','1') default NULL, `on_sale` enum('Yes','No') NOT NULL default 'No', PRIMARY KEY (`prod_id`), UNIQUE KEY `prod_no` (`prod_no`), KEY `products_index1` (`prod_status`), KEY `products_index2` (`prod_start_date`,`prod_end_date`), KEY `on_sale` (`on_sale`), FULLTEXT KEY `prod_search` (`prod_name`,`prod_description`), FULLTEXT KEY `prod_no_2` (`prod_no`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6151 ; Thanks for any help -afan -- 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: Show the engine/table type for the specific table...
show table status; Carlos On 12/10/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi friends, How to show the engine/table type of the one table in my database?? I want to know the engine/table type (InnoDB, MyIsam.) for the tables that I did in my database. Thanks. Wagner Bianchi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB: What happens when a single AUTOEXTEND file goes past 2GB?
Daniel, innodb data files keep growing as needed, it even be of 20Gb or more. If you want several data files (mainly because performance) you need to add them in the my.cnf following the instructions in: 14.2.7. Adding and Removing InnoDB Data and Log Files http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html Hopes this help. Carlos On 12/3/06, Daniel Kasak [EMAIL PROTECTED] wrote: Hi all. I'm about to import a LOT of data ( 20 GB ) into some InnoDB tables. At the moment, I have: innodb_data_file_path = ibdata1:10M:autoextend in my /etc/mysql/my.cnf The ibdata1 file is 499MB at the moment. What happens when this goes past 2GB? Do I automatically get allocated another, ibdata2, or something, or do I have to manually add one by shutting down MySQL, adding the extra file to the line above, and restarting? Dan -- 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: Undelete a droped table
Only if you had enabled the log or bin-log options, and the log's history contains the information you need (depending on the log's creation date). Carlos On 11/7/06, Eric Lommatsch [EMAIL PROTECTED] wrote: Hello, I have accidently droped a table from one of my database's. The table was an innodb table. Is there anyway that I can recover the table or is that information gone for good if I don't have a current backup of that table? Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1064 when importing 4.0 dump into 4.1 via command line
I dont think that is the problem but, what do you mean by a slow connection ?, you cant run the dos2unix command on the remote server ? The error ocurred on line 2, did you see any special word in that line ? can you share with us that line? , remember that each version may can reserve different words. Carlos On 9/26/06, Curious George [EMAIL PROTECTED] wrote: I dumped a database from a 4.0 mysql and am attempting to move it to a server running 4.1 - using the command line: $ mysql -u root -pmypassword empty4.1db 4.0dump.sql The result: ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '77, 137289, 4)' at line 1 When I look at the file using vi, I see lots of ^M (all over the place). I believe that these are DOS line breaks and I know how to get rid of them, but it is a 35MB file and I don't know if I can do it over my slow connection from home (via SSH). My question is: are those ^Ms my problem? Or is that normal and I have some other issue? Thanks in advance for any pointers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about innodb_file_per_table
Besides some recent change on the engine, i remember that the initial size is 0 because the metadata remains on the main ibdata file(s), and grows as needed, there is no specific range of extending. Carlos On 9/25/06, Takanobu Kawabe [EMAIL PROTECTED] wrote: Hello. In InnoDB tables , some table name.ibd files are created when the innodb_file_per_table parameter is specified. I want to know How the MySQL server determines the initial size of table name .ibd files and the extended size of them. Please tell me about this . With regards -- 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: Transactions in MySQL.
It only works with engines that support transactions like innodb and solid, i strongly sugget to read these links from the manual. http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html Carlos On 9/25/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi All, How transactions and locking are handled in MYSQL? Is it a part of configuration? Or a query (lock tables, Unlock tables) for each set of queries? Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question Concerning (innodb_buffer_pool_size)
Yes of course you can and...you should As the my.cnf says # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. So if you are using innodb as you main engine i suggest the advice of 80% of ram, obviously it depends of the current load in the server; if you are running ie. an application server you would have to divide the ram according to each needs. Carlos. On 8/15/06, Shaun Adams [EMAIL PROTECTED] wrote: I'm currently running RHEL4.1 64-bit, mysql 4.0.26 w/ 4GB RAM and have my innodb_buffer_pool_size set to 2GB, My question is, can I increase my ram to 8GB and set innodb_buffer_pool_size to 4GB or even 6? Thanks
Re: Adding index -- Need help
Remember that if you dont use an index on a colum and you search by that criteria, there would be a whole table scan :(. Its recommended that you always index those columns that appear on a search and/or an order (where column_a='yyy' order by column_b) and those involved on joins; generally speaking joins are always based on primary keys, so, if you have already defined the primary keys there is an index associated with the columns of that primary key (just remember that order matters), so the join use the index and not the whole table. Hope this helps. Carlos On 8/10/06, Ratheesh K J [EMAIL PROTECTED] wrote: Hello All, Need a suggestion for this: We have tables which have very few number of rows ( less than 600 ). For a long period of time the number of rows are going to remain almost the same. 1) Is it better to index the columns of such tables? 2) There are Joins on this table and then a search on certain fields of this table in the where clause. Does index make a difference? Example: TBL_XXX and TBL_YYY are related through FLD_ACC_ID. This fld is indxed on both the tables. None of the others are. Would adding indexes help on such tables ( only 342 rows in both ) DESC TBL_XXX;342 Rows - FLD_EMAIL_ID varchar(150) PRI FLD_ACC_ID int(11) unsigned PRI 0 FLD_PRIMARY_FLAG tinyint(1) YES 0 -- DESC TBL_YYY; 342 Rows FLD_ACC_ID int(11) unsigned PRI 0 FLD_ACC_NAME varchar(32) YES \N FLD_MAIN_ZONE tinyint(1) 0 FLD_FOR_ZONE_ID int(10) unsigned YES 0 FLD_STATE tinyint(1) unsigned 0 FLD_DOMAIN_ID tinyint(4) unsigned YES 0 FLD_SCAN_CUST_SERIAL_NUM_FLAG tinyint(1) unsigned YES 0 FLD_SCAN_CUST_NO_SUPPORT_FLAG tinyint(1) unsigned YES 0 FLD_INBOX varchar(64) YES \N FLD_INBOX_PASSWD varchar(16) YES \N FLD_INBOX_LOCK_FLAG tinyint(1) YES 0 FLD_INBOX_LOCK_DATE_TIME datetime YES \N FLD_EMAIL_INBOX_OUTWARD varchar(32) YES \N FLD_WEBMAIL_INDIVIDUAL_FLAG tinyint(1) unsigned YES 0 FLD_WEBMAIL_INDIVIDUAL_TYPE tinyint(5) unsigned YES 0 FLD_EMAIL_DONT_PROCESS_FLAG tinyint(1) unsigned 0 FLD_ATTACH_MAX_UPLD_SIZE tinyint(2) unsigned YES 0 FLD_ATTACH_MAX_RECV_SIZE tinyint(2) unsigned YES 0 FLD_ADD_BY int(11) 0 FLD_ADD_DATE_TIME datetime -00-00 00:00:00 FLD_UPDATE_BY int(11) YES \N FLD_UPDATE_DATE_TIME datetime YES \N FLD_PARENT_ID int(11) unsigned YES 0 FLD_THREAD_ID int(11) unsigned YES 0 FLD_POS tinyint(5) unsigned YES 0 FLD_LEVEL tinyint(5) unsigned YES 0 FLD_OWNER_COMPANY_ID int(10) unsigned 0 FLD_FOR_COMPANY_ID int(10) unsigned YES 0 FLD_NO_DIRECT_SUBMISSION_FLAG tinyint(1) YES 0 Thanks, Ratheesh Bhat K J
Re: Out of memory (Needed xxx ...
As far as i remember you must set that variable in order to send huge blobs, and the error message can vary because it can means that the current memory space for that connection has been filled. Why not do you try to set it up, as mentioned in the manual and check the results. Im forwarding this to the java list, maybe Mark can reserve a liitle time from the Mysql Users Conference and give us some feedback. Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: It's not set, but I am streaming the LOG to the server, would max packet impact this situation? Also, wouldn't I get a different error, i.e. Packet Too Large? From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:59 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- 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: Out of memory (Needed xxx ...
Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory (Needed xxx ...
And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- 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]
innodb per table
How stable is this feature? , i have been using it for a few time and works fine, but im thinking to move my current productive enviroment this way and i dont know if its a good choice. Any advise is appreciated. Carlos _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading mySQL 3.23 to 4.0
supposedly ?, you can check if the mysqld daemon is running with: ps -ef | grep mysql if the daemon is running then the error is something else but my first bet is that is not running. Carlos From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Carlos Proal [EMAIL PROTECTED] CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Upgrading mySQL 3.23 to 4.0 Date: Mon, 02 Aug 2004 18:26:05 -0400 in my Linux Services... the mysqld entry is missing i have mysql and that is supposedly running. - Original Message - From: Carlos Proal [EMAIL PROTECTED] Date: Monday, August 2, 2004 6:19 pm Subject: Re: Upgrading mySQL 3.23 to 4.0 The upgrade shutdown the database, i think you must restart it manually, maybe using some of the init scripts. Carlos From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: Japheth Cleaver [EMAIL PROTECTED] Subject: Re: Upgrading mySQL 3.23 to 4.0 Date: Mon, 02 Aug 2004 17:31:03 -0400 I am now getting [EMAIL PROTECTED] Desktop]# mysql -u root -pmypasshere ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) any thoughts... once again thanks for the assistance - Original Message - From: [EMAIL PROTECTED] Date: Monday, August 2, 2004 5:26 pm Subject: Re: Upgrading mySQL 3.23 to 4.0 I wound up having to do the following: [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-compat-4.0.20- 0.i386.rpm warning: MySQL-shared-compat-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 Preparing... ### [100%] 1:MySQL-shared-compat ### [100%] [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-shared-4.0.20-0.i386.rpm warning: MySQL-shared-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 Preparing... ### [100%] package MySQL-shared-4.0.20-0 is already installed [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-devel-4.0.20-0.i386.rpm warning: MySQL-devel-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 Preparing... ### [100%] file /usr/bin/comp_err from install of MySQL-devel- 4.0.20-0 conflicts with file from package mysql-3.23.58-9 file /usr/bin/mysql_config from install of MySQL-devel- 4.0.20-0 conflicts with file from package mysql-3.23.58-9 [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20-0.i386.rpm MySQL-client-4.0.20-0.i386.rpm MySQL-devel-4.0.20-0.i386.rpm warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 Preparing... ### [100%] 1:MySQL-server ### [ 33%] Installing all prepared tables 040802 17:21:17 Warning: Asked for 196608 thread stack, but got 126976040802 17:21:17 /usr/sbin/mysqld: Shutdown Complete PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h localhost.localdomain password 'new- password'See the manual for more instructions. NOTE: If you are upgrading from a MySQL = 3.22.10 you should run the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be able to use the new GRANT command! Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com 2:MySQL-client ### [ 67%] 3:MySQL-devel ### [100%] [EMAIL PROTECTED] Desktop]# Does this mean success? Also, what does this line mean? 040802 17:21:17 Warning: Asked for 196608 thread stack, but got 126976 Thanks for the help - Original Message - From: Japheth Cleaver [EMAIL PROTECTED] Date: Monday, August 2, 2004 5:12 pm Subject: Re: Upgrading mySQL 3.23 to 4.0 At 01:58 PM 8/2/2004, [EMAIL PROTECTED] wrote: Greetings: I am trying to upgrade to the new version of mySQL on Fedora Core 2 Linux. I have mysql Ver 11.18 Distrib 3.23.58, for redhat- linux-gnu (i386) I downloaded the following files: MySQL-client-4.0.20-0.i386.rpm MySQL-server-4.0.20-0.i386.rpm and as root ran the following: [EMAIL PROTECTED] Desktop]# rpm -Uvh MySQL-server-4.0.20- 0.i386.rpm MySQL-client-4.0.20-0.i386.rpm warning: MySQL-server-4.0.20-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libmysqlclient.so.10 is needed by (installed) perl
RE: Help understanding how the connection works
Hi, you cant connect to mysql with telnet, the service running in 3306 supports a dbms specific protocol so you need to use the mysql command aka mysql client to connect to mysql and execute any sql query. Carlos From: Gonçalo Silva Dias [EMAIL PROTECTED] To: [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Help understanding how the connection works Date: Tue, 3 Aug 2004 02:10:56 +0100 Hi ppl, Im trying to access to mysql server by telnet. When I do like telnet localhost 3306 if I write something the error is Bad handshake. Well can anyone tell me what info do I need to send to the server to establish a connection with mysql server? So I can execute some queries? _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lucene with MYSQL
Hi, i have been using Lucene MySQL but separately, MySQL is written in C/C++ with a JDBC driver available and Lucene is coded in Java, so (besides the mysql team opinion) you can mix searches throught both apis and referring to each other with the PrimaryKey. This combination works fine for some scenarios but it doesnt for others, in some cases the best choice is to use the FullText feature available in MySQL which employs the same Vectorial Model that Lucene's do. One advantage of Lucene is that is composed of layers and you can modify it to use different models instead of uniquely the Vectorial; on the other hand Lucene doesnt support concurrency which can be a serious problem in your application or maybe not. Hope my 5 cents works to you :) Carlos Proal Original Message Follows From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Lucene with MYSQL Date: Thu, 29 Jul 2004 13:36:56 -0400 Is it possible to use the Lucene fulltext search on a MYSQL database - say for text type fields? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Lucene with MYSQL
From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: Lucene with MYSQL Date: Thu, 29 Jul 2004 16:56:23 -0400 ...snip... This combination works fine for some scenarios but it doesnt for others, in some cases the best choice is to use the FullText feature available in MySQL which employs the same Vectorial Model that Lucene's do. One advantage of Lucene is that is composed of layers and you can modify it to use different models instead of uniquely the Vectorial; on the other hand Lucene doesnt support concurrency which can be a serious problem in your application or maybe not. Could you just briefly explain layers and concurrency maybe w/a simple real-life example so I can see the contrast between the two searchs? Thanks very much, Lee G. Sure, i mean layers relating about java packages, Lucene have different packages to store, index, retrieve, apply IR model, etc, so for example if you want to use a different index schema you can modify that package and improve or customize that layer, the same applies to IR models, most models are based on frequency, collection size, etc those methods are available so you can build an entirely new algorithm (ie. Latent Semantic) with the core indexing of Lucene. About concurrency, MySQL is a engine that supports thousand connections at the same time and mantains some indexes in memory (even Full Text indexes i think, someone at MySQL can confirm this ?) so every operation is very fast; Lucene is not an engine its an API, so every add/update must check if the index file is locked, wait and so on. If you need something more robust in this way MySQL is the best choice; if you are thinking in something more static than doesnt update frequently then Lucene can be a good one. Lucene's faq and http://today.java.net/pub/a/today/2003/07/30/LuceneIntro.html are good references for more details. regards Carlos Proal Hope my 5 cents works to you :) Carlos Proal Original Message Follows From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Lucene with MYSQL Date: Thu, 29 Jul 2004 13:36:56 -0400 Is it possible to use the Lucene fulltext search on a MYSQL database - say for text type fields? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help creating table...
Hi, the table name and the column names dont have to be inclosed by ' '. Carlos Original Message Follows From: Marvin Cummings [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Need help creating table... Date: Tue, 6 Apr 2004 12:29:48 -0400 _ From: Marvin Cummings [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 9:26 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Need help creating table... I attempt to create this table from the command line and get the following error: Error 1064 (42000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL version for the right syntax to use near ''ad_id' smallint(5) unsigned NOT NULL auto_increment, 'cat_id' smallint(5) unsign' at line 3 CREATE TABLE 'nuke_zc_ads' ( `ad_id` smallint(5) unsigned NOT NULL auto_increment, `cat_id` smallint(5) unsigned NOT NULL default '0', `user_name` varchar(20) NOT NULL default '', `email` varchar(50) NOT NULL default '', `price` text NOT NULL, `condition` varchar(10) NOT NULL default '', `city` varchar(20) NOT NULL default '', `state` varchar(20) NOT NULL default '', `country` varchar(20) NOT NULL default '', `lastup_date` int(11) NOT NULL default '0', `subject` text NOT NULL, `descript` text NOT NULL, `url` text NOT NULL, `views` int(11) NOT NULL default '0', `paypal` char(3) NOT NULL default 'No', `add_date` int(11) NOT NULL default '0', `exp_date` int(11) NOT NULL default '0', PRIMARY KEY (`ad_id`) ) TYPE=MyISAM AUTO_INCREMENT=1 ; I'm typing this exactly as it appears but it's not working. I also try to create this table in phpMyAdmin but have a question: Field Type ad_id SmallInt 5 Length/Values 5 Charset Latin1 Attributes unsigned Null Not_null Default** ? Extra auto_increment I'm not sure what to enter for Default**. Can someone tell me what I'm missing? I'm using MySQL5.0alpha w/PHP4.3.4 on W2K3. Thanks _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Dump Command Does Not Consider Foreign Key Dependencies
i turn off the checks before a dump in order to restore it properly. In my scripts looks like: echo SET FOREIGN_KEY_CHECKS=0; ${mysql_backup_directory}/${2}/${database_filename} mysqldump --opt -h $2 -P $3 -u $db_username --password=$db_password $1 ${mysql_backup_directory}/${2}/${database_filename} 2 $logfile } I would suggest that this can be included as a mysqldump option (--nochecks or similar). Carlos Original Message Follows From: Stephen Cuppett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: MySQL Dump Command Does Not Consider Foreign Key Dependencies Date: Fri, 05 Mar 2004 13:06:09 -0500 Description: The current mysqldump utility does an alphabetical dump of the tables as well as a row by row dump of the data following each table declaration. This is adequate enough for most databases; however, if there are strict foreign key constraints, the ordering will not always be conducive to utilizing mysqldump output to restore databases causing major headaches in order to restore when the table structures are sophisticated and/or if there is considerable BLOB output in the output. How-To-Repeat: I apologize for any syntactical mistakes, but I'm sure you can get the idea. Consider two tables: a,b. After creating these tables, defining several columns, and then implementing foreign keys, if a has a foreign key constraint on yet-to-be-created b in dump output, then a will fail to create. Further, even if a were to be created, data input into a would fail without the supporting rows in b. Fix: Ultimately, a dependency graph needs to be considered before dumping in order to dump the tables and data in an order that will allow the output to be used to restore the data rather than alphabetical. However, if we assume that the data existing now is in a normal state (simplified to mean, fulfills foreign key constraints), then we could dump structure and data in same order, but suppress details about the foreign key constraints and have those appended to the dump output as ALTER TABLE statements. I believe the latter to be a much simpler fix and would eliminate this problem. Submitter-Id: submitter ID Originator:Stephen Cuppett Organization: MySQL support: none Synopsis: Dump Data ABC order Inconsiderate of Foreign Key Constraints Severity: serious Priority: medium Category: mysql Class: change-request Release: mysql-4.0.17-standard (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux cuppett1 2.4.21-192-smp #1 SMP Wed Feb 18 19:31:29 UTC 2004 i686 athlon i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-suse-linux/3.3.1/specs Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir=/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib --enable-languages=c,c++,f77,objc,java,ada --disable-checking --enable-libgcj --with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zlib --enable-shared --enable-__cxa_atexit i586-suse-linux Thread model: posix gcc version 3.3.1 (SuSE Linux) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='g++' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: -rwxr-xr-x1 root root 1469811 2003-09-23 19:05 /lib/libc.so.6 -rw-r--r--1 root root 13553180 2003-09-23 12:04 /usr/lib/libc.a -rw-r--r--1 root root 204 2003-09-23 12:04 /usr/lib/libc.so -rw-r--r--1 root root 982008 2003-09-23 14:29 /usr/lib/libc-client.a lrwxrwxrwx1 root root 20 2004-01-08 19:04 /usr/lib/libc-client.so - libc-client.so.2002d -rwxr-xr-x1 root root 770436 2003-09-23 14:29 /usr/lib/libc-client.so.2002d Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce
about bug 2654
Hi Mark et al, do you have any clue of when this bug is going to be fixed ? i saw at the bug system that it is a non-critical low priority bug, but i think this is major issue that most people using at least java mysql require for some applications. Bug #2654 JDBC Exception: Column 'column.table' not found when order by in query thanks in advance Carlos _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb multiple tablespaces benchmark
Hi all, specially to Heikki. Its really amazing that multiple tablespaces are available before 2004, congratulations to Innodb Oy Inc. Right now im migrating from 4.1.0 to 4.1.1 but im figuring out if there is a downgrade in performance in order to use multiple tablespaces, obviously it must be one because handling several files adds an overhead but it something to worry about ? I thougt that these tablespaces would be for each database and not for each table, this is possible ?, factible ?, useful ?, its scheduled ?. Thanx a lot and again some claps for innodb :) Carlos _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: start innodb without transactions
Hi Heikki et al. I have solve my problem :). The fact about my missed table was my fault, because indeed those tables doesnt exist anymore (one developer had deleted them). Even when my db is up and running im figuring out why there is not an option to skip commit/rollback pending transactions ?, this way i would have a quitely weekend, heheehe, no problem thanx a lot for your help. Carlos Original Message Follows From: Carlos Proal [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: start innodb without transactions Date: Thu, 05 Jun 2003 23:40:16 -0500 Probably i need to describe the whole story: I did a huge insert, a file about 100Mb, and mysql crashed/restarted, the err file showed: -- 030605 12:11:59 mysqld restarted 030605 12:12:00 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 13 1000286684 InnoDB: Doing recovery: scanned up to log sequence number 13 1000286208 InnoDB: Last MySQL binlog file position 0 756499, file name ./catarina-bin.057 030605 12:12:01 InnoDB: Flushing modified pages from the buffer pool... 030605 12:12:01 InnoDB: Started /centia01/final/database/mysql/libexec/mysqld: ready for connections 030605 12:14:01 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. mysqld got signal 10; 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=67104768 read_buffer_size=131072 sort_buffer_size=524280 max_used_connections=4 max_connections=500 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 385528 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -- (i have 256Mb in buffer pool) But the next restart stopped mysql showing: -- 030605 12:14:30 mysqld restarted 030605 12:14:32 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 13 1002344016 InnoDB: Doing recovery: scanned up to log sequence number 13 1002343936 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: Trx id counter is 0 119167744 InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx with id 0 119167355 030605 12:14:32 InnoDB: Assertion failure in thread 1 in file btr0cur.c line 3350 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; -- i started with force_recovery and indeed i can recover with mysqldump some databases but i was confused because my first try was with a db that apparently i cant recover, even inside mysql i get the following: - mysql desc facturas; ERROR 1016: Can't open file: 'facturas.InnoDB'. (errno: 1) mysql show table status; +--+--++--++-+-+--+---++-+-++++ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +--+--++--++-+-+--+---++-+-++++ | facturas | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL| NULL | NULL | Can't open file: 'facturas.InnoDB'. (errno: 1) | | presupuestos | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL| NULL | NULL | Can't open file: 'presupuestos.InnoDB'. (errno: 1) | | proveedores | NULL | NULL
start innodb without transactions
Hi all Is there any way to start mysql/innodb skipping transactions ?, i had a crash and i cant get the database to work. i tried the force_recovery option level 3: # 3 (SRV_FORCE_NO_TRX_UNDO) do not run transaction rollbacks after recovery; this way i can get it to work, but as the manual said i cant do any update query while force_recovery is on and also cant do a mysqldump, so im stucked :(. any ideas ? Im using mysql 4.0.4, solaris 8 sparc . thanx Carlos _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: start innodb without transactions
| NULL | NULL | NULL | NULL| NULL| NULL | NULL | Can't open file: 'usuarios.InnoDB'. (errno: 1) | +--+--++--++-+-+--+---++-+-++++ 5 rows in set (0.06 sec) - Heikki, thanx a lot for your help, its really appreciated. Carlos Original Message Follows From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: start innodb without transactions Date: Fri, 6 Jun 2003 04:23:57 +0300 Carlos, - Original Message - From: Carlos Proal [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, June 06, 2003 4:04 AM Subject: start innodb without transactions Hi all Is there any way to start mysql/innodb skipping transactions ?, i had a crash and i cant get the database to work. i tried the force_recovery option level 3: # 3 (SRV_FORCE_NO_TRX_UNDO) do not run transaction rollbacks after recovery; this way i can get it to work, but as the manual said i cant do any update query while force_recovery is on and also cant do a mysqldump, so im stucked :(. why you cannot do a mysqldump? innodb_force_recovery does not prevent SELECT queries. any ideas ? Im using mysql 4.0.4, solaris 8 sparc . Upgrade to 4.0.13. Also send me the .err log. Database corruption is uncommon in Solaris, interesting to see what is in the .err log. thanx Carlos Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb enable/disable keys
Hi, i have a simple question: The sql statement: alter table _table_name_ disable keys; has any action on INNODB tables ?, helps improving bulk inserts ?. thanx Carlos _ Help STOP SPAM: Try the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
uploading 200,000 x 5 rows
Hi all, mysql-team i have a very very complicated problem , hope to be clear. i have a file report (.txt) with about 200,000 data about books (title, clasif, year, publisher, authors, subjects, etc etc) without tabs or separation, i built a parser to split each field and works fine. After each sequential parsing (one book) i upload that info to the database but in 5 tables (verifying duplication, ids, etc): book author subject book-author (relationship) book-subject (relationship) When i reach the book number 11500 (aprox) mysql shows a deepth fall in performance , each insertion takes 30 segs or more, even with/without indexes. First i though that parsing takes longer, so i parsed the entire file and generated a java serialized file, then just inserted to db but was unsuccessfully , still the same performance. I have calculated the total number of rows for each table and is about 200,000 for books, 150,000 for authors , 130,000 for subjects and relations can have 250,000 each one. Any have an idea of how can i upload this information quickly ??? I'll really appreciate any advise. Carlos _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: uploading 200,000 x 5 rows
Original Message Follows From: Rodney Broom [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: uploading 200,000 x 5 rows Date: Thu, 13 Sep 2001 18:42:22 -0700 Hi Carlos, I'm glad to help, but I'd like to clarify several things, first. thanx a lot ;) ...with about 200,000 data about books (title, clasif, year, publisher, authors, subjects, etc etc) So you have a single file with data for multiple tables? Yeah indeed, imagine an old bibliographical card, well i have a file with 200,000 consecutive cards. After each sequential parsing (one book) What defines one book? One file, several records from one or more files, or one record from a single file? I have a file something similar to: --- Classification: QAT67.0 Title: History of America System key: tyup Authors: Broom, Rodney. Proal, Carlos Subjects: History, Social Sciences ...other fields Classification: QE56.0 Title: Mysql System key: iuds Authors: Broom, Rodney. Dubois, Paul Subjects: Computer Science, History ...other fields ...200,000 records like these After each parsing (just reading one record) i have a java object containing: Book information: title, classif, systemkey, etc Book's authors: array of [author1,author2] Book's subjects: array of [subject1,subject2] i upload that info to the database You upload? How is this done, exactly? Do you mean that you run mysql from one machine with the host set for another machine? when i begin the whole process i open a connection to the DB by java using localhost (i work on the same machine). ...but in 5 tables... So you've split your data into sets that need to get loaded into five destination tables, or you upload five sets of data? the first one, i need to split into five tables. (verifying duplication, ids, etc): Does this mean that you are checking for duplicates before loading into MySQL? yep let me explain, i have a kind of do-while statement like these: open connection to db while (still records in the file) { parse one record insert book information into table book (classif, title, year, etc) for(each author (current book)) { if author currently in db (through a select) get authorID else authorID= insert author into table author (id authorname) insert into table book_author (authorID, classif) - relationship } ...subjects with similar for that authors } close connection to db thanx again _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: uploading 200,000 x 5 rows
Original Message Follows From: Rodney Broom [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: uploading 200,000 x 5 rows Date: Thu, 13 Sep 2001 18:42:22 -0700 Hi Carlos, I'm glad to help, but I'd like to clarify several things, first. thanx a lot ;) ...with about 200,000 data about books (title, clasif, year, publisher, authors, subjects, etc etc) So you have a single file with data for multiple tables? Yeah indeed, imagine an old bibliographical card, well i have a file with 200,000 consecutive cards. After each sequential parsing (one book) What defines one book? One file, several records from one or more files, or one record from a single file? I have a file something similar to: --- Classification: QAT67.0 Title: History of America System key: tyup Authors: Broom, Rodney. Proal, Carlos Subjects: History, Social Sciences ...other fields Classification: QE56.0 Title: Mysql System key: iuds Authors: Broom, Rodney. Dubois, Paul Subjects: Computer Science, History ...other fields ...200,000 records like these After each parsing (just reading one record) i have a java object containing: Book information: title, classif, systemkey, etc Book's authors: array of [author1,author2] Book's subjects: array of [subject1,subject2] i upload that info to the database You upload? How is this done, exactly? Do you mean that you run mysql from one machine with the host set for another machine? when i begin the whole process i open a connection to the DB by java using localhost (i work on the same machine). ...but in 5 tables... So you've split your data into sets that need to get loaded into five destination tables, or you upload five sets of data? the first one, i need to split into five tables. (verifying duplication, ids, etc): Does this mean that you are checking for duplicates before loading into MySQL? yep let me explain, i have a kind of do-while statement like these: open connection to db while (still records in the file) { parse one record insert book information into table book (classif, title, year, etc) for(each author (current book)) { if author currently in db (through a select) get authorID else authorID= insert author into table author (id authorname) insert into table book_author (authorID, classif) - relationship } ...subjects with similar for that authors } close connection to db thanx again _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Hardware advise
Hi all, i need to deploy a huge database (about 160 Gb) of information, mainly images and pdf documents, access will be aprox of 25 simultaneous connections in the worst case; so i need to choose the right hardware configuration and i need your help. We are thinking in a Sun Ultra 10 with 128 or 256 Mb Ram, but i have no idea about storage, i saw a Seagate hard drive of 181Gb. Ultra160 SCSI ( $ 2000 usd) but i dont know if this is the best option because i have heard about raids and racks but never used it. Any experienced DBA could give tips about the whole configuration ?? thanx in advance. Carlos. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installing in Solaris8 problem
Hi all, i have had several problems trying to install mysql on a Ultra60 sparc with Solaris8, i get the following messages running the configure script. I dont have any idea of why is this happenning, probably some related with 64 bits OS or something. Anyone have a clue ??? thanx friends. ./configure --prefix=/vol03/mysql-3.23.38 --with-berkeley-db loading cache ./config.cache checking host system type... sparc-sun-solaris2.8 checking target system type... sparc-sun-solaris2.8 checking build system type... sparc-sun-solaris2.8 checking for a BSD compatible install... ./install-sh -c checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for working aclocal... found checking for working autoconf... found checking for working automake... found checking for working autoheader... found checking for working makeinfo... missing checking whether to enable maintainer-specific portions of Makefiles... no checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for gawk... (cached) nawk checking for gcc... (cached) gcc checking whether the C compiler (gcc ) works... yes checking whether the C compiler (gcc ) is a cross-compiler... no checking whether we are using GNU C... (cached) yes checking whether gcc accepts -g... (cached) yes checking for c++... (cached) c++ checking whether the C++ compiler (c++ ) works... yes checking whether the C++ compiler (c++ ) is a cross-compiler... yes checking whether we are using GNU C++... (cached) yes checking whether c++ accepts -g... (cached) yes checking how to run the C preprocessor... (cached) gcc -E checking for ranlib... (cached) ranlib checking for ld used by GCC... (cached) /usr/ccs/bin/ld checking if the linker (/usr/ccs/bin/ld) is GNU ld... (cached) no checking for BSD-compatible nm... (cached) /usr/ccs/bin/nm -p checking whether ln -s works... (cached) yes checking for object suffix... o checking for executable suffix... no checking for gcc option to produce PIC... -fPIC checking if gcc PIC flag -fPIC works... yes checking if gcc supports -c -o file.o... yes checking if gcc supports -c -o file.lo... yes checking if gcc supports -fno-rtti -fno-exceptions ... yes checking if gcc static flag -static works... -static checking if the linker (/usr/ccs/bin/ld) is GNU ld... no checking whether the linker (/usr/ccs/bin/ld) supports shared libraries... yes checking command to parse /usr/ccs/bin/nm -p output... ok checking how to hardcode library paths into programs... immediate checking for /usr/ccs/bin/ld option to reload object files... -r checking dynamic linker characteristics... solaris2.8 ld.so checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes checking for objdir... .libs creating libtool loading cache ./config.cache checking for a BSD compatible install... ./install-sh -c checking for bison... no checking for byacc... no checking for pdftex... no checking return type of sprintf... configure: error: can not run test program while cross compiling _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
multiple databases performance
Hi all, glad to be back in the list :) I have a question related with a huge project, i could have these 2 schemes: 1) just one database with several tables database | tables DB_1 | user, profile, supplier, dept, etc... a lot of tables 2) several databases with few tables database | tables DB_1| user, profile DB_2| supplier, dept DB_etc | etc what would be the best choice ?, think that all tables contain thousands of rows, and applications have to access most of them, for example user information; even when working with suppliers users are required, so in case 2 the application must connect to both databases. any suggestions ?? thanx a lot. Carlos _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Hi all, glad to be back in the list :) I have a question related with a huge project, i could have these 2 schemes: 1) just one database with several tables database| tables DB_1 | user, profile, supplier, dept, etc...a lot of tables 2) several databases with few tables database| tables DB_1 | user, profile DB_2 | supplier, dept DB_etc |etc what would be the best choice ?, think that all tables contain thousands of rows, and applications have to access most of them, for example user information; even when working with suppliers users are required, so in case 2 the application must connect to both databases. any suggestions ?? thanx a lot. CarlosGet Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php