Re: MySql data between Linux and Windows
Carlos, We have a hosting provider with a Linux box and an MySql database. We have a local Mysql database. In both machines I use phpMyAdmin. On the startpage (right frame) of phpMyAdmin you should see a link called Show MySQL system variables. Look what the values of the variables character_set and character_sets are. When I export from Windows to Linux then all the data in Linux is messed up with respect to special characters, we work in spanish and all accents are lost and converted to some extrange character. When I export from Linux to Windows the same thing happens. Of course these has to do with character sets. Most probably the two MySQL servers you are using have different character sets. ¿Any suggestion? You can find more detailed information here: http://www.mysql.com/doc/en/Character_sets.html And here: http://www.mysql.com/doc/en/SHOW_VARIABLES.html Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query
-Original Message- From: Ulterior [mailto:[EMAIL PROTECTED] Sent: Saturday, May 31, 2003 7:35 AM To: [EMAIL PROTECTED] Subject: Re: Insert query sow what would you suggest, Jerry? ( I need a very FAST search on this table's filename field) Ulterior Don't use varchar unless you absolutely have to, that should help. Jerry - Original Message - From: Ulterior [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 3:13 PM Subject: Insert query Hi, I have a database table: CREATE TABLE FTPFILE ( ID int(11) NOT NULL auto_increment, FTPSITEID int(11) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE int(11) NOT NULL, FILEDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID) ); when this table reaches 1 records, insert queries are very slow, aproximately 2 records a second. And worst of all mysql-ntd.exe reaches 99% of my processor timing. I am using native mysql C API mysql_query() func for inserting data. Is there any way to speedup insert querys to this table? ( I am planning to have at least 2-5 million entries in this table ) Your VARCHAR columns can become CHAR columns, which should help a lot. But CHAR columns can't be longer than 255, so you're about at the limit. Are you locking the table before inserting, then using the multiple insert syntax? Those should help if you're not doing them, but I don't know what it is in the C API. How many indexes do you have? If you reduce the number of indexes, insertions will go faster, but that might slow down some of your queries, of course. If you're inserting quite a few at a time, you might disable indexing, do the inserts, then allow the indexing to happen all at once. For a large number of inserts, you might also try writing them to a file, then loading it. That's much faster for really large numbers of inserts, and you can also suspend indexing as I mentioned. I use the latter for building externally generated text indexes (frequency tables and such), generating a couple of million records at a shot. It made an enormous difference. But I'm working in Python and can't help you with the C API, as I said. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why doesn't this query work?
-Original Message- From: Mikey [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 11:53 AM To: [EMAIL PROTECTED] Subject: Why doesn't this query work? OK, first of all thanks for the pointers, however, the query I now have doesn't seem to work. If I run the query up until pricelevel IN (1, 2, 3, 4, 5) it returns a large result set (the clauses are fairly inclusive), however, when I add in the rest of the query no results are returned. Any ideas? This is just a guess, but perhaps you are comparing INTs to STRINGs with that last bit, in which case you'd want to change the column type for pricelevel or put quotes around the numbers in the query? I hit that problem all the time using 1 and 0 as Booleans in an ENUM column. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recovering table structures from .frm files?
Mark, Here's a brute force and ignorance approach. Disclaimer: It has worked once, and may work again some day. In particular, I haven't looked at the MySQL internals, and I've only tried it on a very small table. You have foo.frm, which used to be the .frm file for an InnoDB table. I note that the (only) difference between .frm files for MyISAM and InnoDB is that the fourth byte of the file is hex 09 for MyISAM and hex 0C for InnoDB. (This, from comparing .frm files for a very small and simple database.) 0. Make sure you have foo.frm saved somewhere other than your MySQL data directory. 1. Create a new MyISAM table foo; it doesn't matter what the layout is. For example, create table foo (n int); 2. Copy your foo.frm over the one created in step 1. 3. Change the fourth byte of foo.frm to hex 09 instead of hex 0C. 4. From the MySQL client, say show create table foo; Good luck. HTH. Bill Date: Thu, 29 May 2003 12:47:02 -0700 Subject: RE: Recovering table structures from .frm files? From: Mark Morley [EMAIL PROTECTED] To: Mark Morley [EMAIL PROTECTED], [EMAIL PROTECTED] I have a couple of .frm files with no corresponding data or index files. Is it possible to recover the table structure (field names, types, sizes) from these files? More info: these appear to have been created under MySQL 4.0.x and they were originally InnoDB files. I can see a list of field names by running strings on each .frm file, but I'd really like to get the colum types and sizes as well. Is the file format documented anywhere? Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA problem
Hello everybody, Im new to this list, and also new to MySQL. I would be very grateful if someone could help me with the following problem: I have MySQL server version 4.0.12 with a database on a Linux server. I work from a Windows XP client. I am trying to load data from a text file into a table on the server. I am using the LOAD DATA command and get the following error message: Database test - table RUBRO running on localhost Error SQL-query : LOAD DATA LOCAL INFILE '/tmp/php7oeURC' INTO TABLE `RUBRO` FIELDS TERMINATED BY ';'OPTIONALLY ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' MySQL said: The used command is not allowed with this MySQL version The manual says that LOAD DATA command can be used with version 3.5 onwards, and I have 4.0.12. What could the trouble be? Thanks in advance. Jerome Smith --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.470 / Virus Database: 268 - Release Date: 08-04-2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jerome Smith wrote: Hello everybody, Im new to this list, and also new to MySQL. I would be very grateful if someone could help me with the following problem: I have MySQL server version 4.0.12 with a database on a Linux server. I work from a Windows XP client. I am trying to load data from a text file into a table on the server. I am using the LOAD DATA command and get the following error message: Database test - table RUBRO running on localhost Error SQL-query : LOAD DATA LOCAL INFILE '/tmp/php7oeURC' INTO TABLE `RUBRO` FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' MySQL said: The used command is not allowed with this MySQL version The manual says that LOAD DATA command can be used with version 3.5 onwards, and I have 4.0.12. What could the trouble be? Thanks in advance. Jerome Smith It's actually a bug with the windows binaries (until version 4.0.13). This is fixed in 4.0.13 (which is the current stable build of MySQL). -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+2NVmtvXNTca6JD8RAiQFAJwJzqO+TfEbv9e7vLsZWd072dVnTgCdFIgR EkY+CRktLnc1YezXCA2IGxM= =Iakc -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Automatic calculation of date additions
Alex Thurgood [EMAIL PROTECTED] wrote: Le lun 26/05/2003 ? 21:00, [EMAIL PROTECTED] a ?crit : From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Automatic calculation of date additions Date: 26 May 2003 15:57:41 + Alex Thurgood [EMAIL PROTECTED] wrote: I'm just learning to get to grips with mysql, and I've been looking at the date functions. I want to be able to define a table that will take a starting date e.g. action_date and then use the value entered here to automatically calculate a second date to be inserted automatically into field final_date , using a third value taken from another field time_limit as the variable for the addition. I understand that statically this can be done using the INTERVAL function : action_date + INTERVAL 4 MONTH but I can't see how to replace this interval value with one from the field time_limit (defined as int) and insert the result into a second field final_date. Can I do this in a create table statement or is that not possible ? No, you can specify this in CREATE TABLE statement, but you can use DATE and TIME functions in the INSERT statement. Ok, thanks, so something like : INSERT INTO office_action VALUES(,2003-04-24,,) INSERT INTO office_action VALUES(final_date) WHERE final_date = action_date + INTERVAL time_limit MONTH ?? Would that work ? Nope. At first in your case you get 2 rows. At second you can't use column in the INTERVAL clause. If you insert all values together, you should know value of time_limit. So just put this value into INTERVAL clause. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Insert query
Hi, I would use mediumint rather than int for the ID column (int has support for up to 2.1 Billion records wheras mediumint is up to 8.3 million - more efficient for your data type). I don't think the varchar will cause much of a problem. Useful section here: http://www.mysql.com/doc/en/Data_size.html Also, do you really need 11 chars on the filesize column (that's 100GB)? If you're planning to have up to 5 million records then you only really need a length of 7 (8 to be safe). If you want a really fast search on filename then index that field. Another idea may be to turn on query caching. Here's what I would do.. CREATE TABLE FTPFILE ( ID mediumint(8) NOT NULL auto_increment, FTPSITEID mediumint(9) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE mediumint(9) NOT NULL, FILEDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID), KEY IDX_FN(FILENAME) ); Also there is an interesting section on the speed of inserts here: http://www.mysql.com/doc/en/Insert_speed.html Cheers, Andrew -Original Message- From: Ulterior [mailto:[EMAIL PROTECTED] Sent: Saturday 31 May 2003 15:35 To: [EMAIL PROTECTED] Subject: Re: Insert query sow what would you suggest, Jerry? ( I need a very FAST search on this table's filename field) Ulterior Don't use varchar unless you absolutely have to, that should help. Jerry - Original Message - From: Ulterior [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 3:13 PM Subject: Insert query Hi, I have a database table: CREATE TABLE FTPFILE ( ID int(11) NOT NULL auto_increment, FTPSITEID int(11) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE int(11) NOT NULL, FILEDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID) ); when this table reaches 1 records, insert queries are very slow, aproximately 2 records a second. And worst of all mysql-ntd.exe reaches 99% of my processor timing. I am using native mysql C API mysql_query() func for inserting data. Is there any way to speedup insert querys to this table? ( I am planning to have at least 2-5 million entries in this table ) Ulterior -- 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]
Newbie design question
Dear all, I'm designing a database for a travel company but i'm puzzled.. Maybe you can shed a light on this: I've got an travelpackage that can exicts of more possible departure date's but it has also have more than one accommodation. So 3 tables: (table one) -Travelpackageid (Primary Key) -Travelpackname etc (table two) -dateid (Primary Key) -date (table three) -accommodationid (Primary Key) -accommodationname etc Must a create 2 Join tables (travelpackageid/dateid and travelpackageid/accommodationid)??? Please help, Frank
Re: MySQL/InnoDB-4.0.13 is released
Rainer, - Original Message - From: Rainer Collet [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, May 31, 2003 5:49 PM Subject: Re: MySQL/InnoDB-4.0.13 is released [EMAIL PROTECTED] (Heikki Tuuri) wrote in message news:[EMAIL PROTECTED]... Hi! InnoDB is a MySQL table type which provides transactions, foreign key constraints, and a non-free hot backup tool to MySQL. Where can I get this hot backup tool? it is non-free and can be ordered from http://www.innodb.com. MySQL/InnoDB support contracts also contain some complimentary Hot Backup licenses. CONSTRAINT `0_16` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELE TE CASCADE How did you get this? I also use 4.0.13 but only get e.g. CONSTRAINT `0_16` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) Even I defined it with an on update or on delete statment. How can I retrieve these contraints afterwards? Could you post a full example? They should be there when you do SHOW CREATE TABLE. E.g.: [EMAIL PROTECTED]:~/mysql-4.0/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.0.14-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE parent(id INT NOT NULL, - PRIMARY KEY (id)) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE child(id INT, parent_id INT, - INDEX par_ind (parent_id), - FOREIGN KEY (parent_id) REFERENCES parent(id) - ON DELETE CASCADE - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql mysql mysql show create table child; +---+--- -+ | Table | Create Table | +---+--- -+ | child | CREATE TABLE `child` ( `id` int(11) default NULL, `parent_id` int(11) default NULL, KEY `par_ind` (`parent_id`), CONSTRAINT `0_14016` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON D ELETE CASCADE ) TYPE=InnoDB | +---+--- -+ 1 row in set (0.00 sec) mysql Thanks very much, Rainer 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]
Help NEWBIE!
Dear Friends, I am new user of MySQL and whenever I type mysql to start the ware it simply states -- Can't connect to local MySQL server through socket 'var/lib/mysql/mysql.sock'. I know I am doing something very wrong here and mabe some of you might be able to help me solve this problem. I am running a SuSE Linux 8.2 version AMD Duron machine and already have a subscription to my ISP to access their database. ALso, the file mysql.sock does not exist in the mysql folder. Thanking you all very much for your kind help. Sincerely, ABHIJIT NAIK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help NEWBIE!
Check running processes, to see how many are currently using mysql (mysqladmin proc). Compare to max_connections (mysqladmin var). Change my.cnf (or whever your config file is) and restart mysql Martin - Original Message - From: ABHIJIT NAIK [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Cc: SuSE [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 3:34 PM Subject: Help NEWBIE! Dear Friends, I am new user of MySQL and whenever I type mysql to start the ware it simply states -- Can't connect to local MySQL server through socket 'var/lib/mysql/mysql.sock'. I know I am doing something very wrong here and mabe some of you might be able to help me solve this problem. I am running a SuSE Linux 8.2 version AMD Duron machine and already have a subscription to my ISP to access their database. ALso, the file mysql.sock does not exist in the mysql folder. Thanking you all very much for your kind help. Sincerely, ABHIJIT NAIK -- 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: Help NEWBIE!
try passing the --host param to specify the ip address of the MySQL server. Lets say that you have MySQL running on 192.168.1.105 $ mysql --host=192.168.1.105 -u username -p do a man mysql for more info SK On Sat, 2003-05-31 at 15:34, ABHIJIT NAIK wrote: Dear Friends, I am new user of MySQL and whenever I type mysql to start the ware it simply states -- Can't connect to local MySQL server through socket 'var/lib/mysql/mysql.sock'. I know I am doing something very wrong here and mabe some of you might be able to help me solve this problem. I am running a SuSE Linux 8.2 version AMD Duron machine and already have a subscription to my ISP to access their database. ALso, the file mysql.sock does not exist in the mysql folder. Thanking you all very much for your kind help. Sincerely, ABHIJIT NAIK -- 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]
Find out what version I am using?
Howdy all! How do I find out what version of mysql I am running from the command line? Is there a type of system select statement I can execute? Rob :) :- :-} -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unicode
Howdy All! How do I activate unicode support for my tables? I searched the mysql manual and found a section that says MySQL software now offers extensive Unicode (UTF8) support but I could not find a section that went through how to do it. Secondly, if I turn on unicode support for a tables, what is the best way to get the unicode data into and out of the database in such a way that it stays as unicode? For example, when I paste unicode characters into a DOS screen, they are translated into some other characters. Thanks for any advice! Rob :) :- :-} -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find out what version I am using?
$ mysql --version On Sat, 2003-05-31 at 22:21, Robert Mark Bram wrote: Howdy all! How do I find out what version of mysql I am running from the command line? Is there a type of system select statement I can execute? Rob :) :- :-} -- 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: Find out what version I am using?
That will give you version of mysql client you're running, For version of mysql server, after successful connection to mysql, use status command. mysql status Or telnet to port 3306 of server running mysql C:\telnet 192.168.1.2 3306 you'll see few garbage and mysql version number in there too. Me - Original Message - From: Steven Kreuzer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Mysql [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 10:32 PM Subject: Re: Find out what version I am using? $ mysql --version On Sat, 2003-05-31 at 22:21, Robert Mark Bram wrote: Howdy all! How do I find out what version of mysql I am running from the command line? Is there a type of system select statement I can execute? Rob :) :- :-} -- 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]
Moving to version 4.1
Howdy all! I now know that I was running 3.23.55 and that I need 4.1 for Unicode support. I am running Windows XP Pro and in my add or remove programs utility, I see MySQL ODBC 3.51 Driver and MySQL Servers and Clients 3.23.55. If I use Windows add or remove programs utility to remove MySQL Servers and Clients 3.23.55 and then in stall 4.1, how will my MySQL ODBC 3.51 Driver driver know to connect to 4.1 now? Is there anything else I need to do? Thanks for any advice! Rob :) :- :-} -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
indexes question
hi just reading up on optimising indexes, does it matter what order u set your indexes ? The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index. i dont really understand what they meant by this , i use most the columns when searching but only a few of the columns which are fulltext get searched the most , do i add these indexes first then ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Removing old service in Windows XP
Howdy All! When I tried to install the new MySql, I got the result below: C:\mysql\binmysqld --install The service already exists! The current server installed: C:\mySql\bin\mysqld-nt C:\mysql\bin My problem is this: I used the add remove programs utility to remove the old MySql server, but in Control Panel | Administrative Tools | Services it *still* has a service there for MySql. There seems to be no facility to remove this through the services utility or msconfig. How can I do this? Any ideas would be most appreciated! Rob :) :- :-} -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes question
In the last episode (Jun 01), Daniel Rossi said: hi just reading up on optimising indexes, does it matter what order u set your indexes ? The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index. i dont really understand what they meant by this , i use most the columns when searching but only a few of the columns which are fulltext get searched the most , do i add these indexes first then ? That part of the manual is talking about a single multi-column index. If you have multiple separate indexes, it doesn't matter in which order you create them. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes question
i have one multiple column fulltext index for 3 columns i join in a fulltext match statement In the last episode (Jun 01), Daniel Rossi said: hi just reading up on optimising indexes, does it matter what order u set your indexes ? The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index. i dont really understand what they meant by this , i use most the columns when searching but only a few of the columns which are fulltext get searched the most , do i add these indexes first then ? That part of the manual is talking about a single multi-column index. If you have multiple separate indexes, it doesn't matter in which order you create them. -- Dan Nelson [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]
MYSQL DB PROBLEM
I am trying to insert a table ibf_posts into a localhost database using the MYSQL control center every time i try to insert that one table i get an error [forum] ERROR 2013: Lost connection to MySQL server during query ?? pleas help Adam
RE: Removing old service in Windows XP
Howdy All! When I tried to install the new MySql, I got the result below: C:\mysql\binmysqld --install The service already exists! The current server installed: C:\mySql\bin\mysqld-nt C:\mysql\bin My problem is this: I used the add remove programs utility to remove the old MySql server, but in Control Panel | Administrative Tools | Services it *still* has a service there for MySql. There seems to be no facility to remove this through the services utility or msconfig. How can I do this? I solved this problem by nuking all entries in the registry containing the String C:\mySql\bin\mysqld-nt - there were two of them. Rob :) :- :-} -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes question
In the last episode (Jun 01), [EMAIL PROTECTED] said: i have one multiple column fulltext index for 3 columns i join in a fulltext match statement Fulltext indexes are different from regular indexes. I don't think it matters what order the columns are in. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passwords don't work in a WinME installation?
Howdy All! I encountered a 1045 error myself and in searching Google found this thread from March 2003. I am not sure if it was solved, so, for the record, here is what I did to solve it. I tried logging into mysql as default with just this command: mysql --local-infile -h localhost I then tried to create a user for myself with the following command and error: mysqlGRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] -IDENTIFIED BY 'rob' WITH GRANT OPTION; ERROR 1045: Access denied for user: '@127.0.0.1' (Using password: NO) mysql I fixed this by quitting and logging in again as root, which has no password set by default and making the user: C:\mysql\binmysql -h localhost -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.1.0-alpha-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysqlGRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] -IDENTIFIED BY 'rob' WITH GRANT OPTION; Query OK, 0 rows affected (0.02 sec) mysql exit Bye Rob :) :- :-} -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting data?
I have a table, in the table is a field called name_id; in a second table (a linking table) I also have the field name_id, this should be the same/reference the same name_id as in the first table. The first table is fully populated. How do write a SQL statement to get the data from the name_id field of table1 into the name_id field of table2? (I'll do this and find out after if it actually references the same data! So, if it doesn't at least I'll have learned the proper SQL statement. :) Thank you, Ted Rogers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Removing old service in Windows XP
Robert Mark Bram schrieb: Howdy All! When I tried to install the new MySql, I got the result below: C:\mysql\binmysqld --install The service already exists! The current server installed: C:\mySql\bin\mysqld-nt C:\mysql\bin My problem is this: I used the add remove programs utility to remove the old MySql server, but in Control Panel | Administrative Tools | Services it *still* has a service there for MySql. There seems to be no facility to remove this through the services utility or msconfig. How can I do this? I solved this problem by nuking all entries in the registry containing the String C:\mySql\bin\mysqld-nt - there were two of them. have you ever heard the word _manual_ ;-) eg. file:///usr/share/doc/mysql-3.23.56/manual.html [not tested c:\mysqld-nt /?] 2.1.2.1 Installing the Binaries 1. If you are working on an NT/2000/XP server, logon as a user with administrator privileges. 2. If you are doing an upgrade of an earlier MySQL installation, it is necessary to stop the server. If you are running the server as a service, use: C:\ NET STOP MySQL Otherwise, use: C:\mysql\bin mysqladmin -u root shutdown 3. On NT/2000/XP machines, if you want to change the server executable (e.g., -max or -nt), it is also necessary to remove the service: C:\mysql\bin mysqld-max-nt --remove -- shrek-m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help NEWBIE!
re-install mysql, it wont work without mysql.sock. On Sun, 2003-06-01 at 01:04, ABHIJIT NAIK wrote: Dear Friends, I am new user of MySQL and whenever I type mysql to start the ware it simply states -- Can't connect to local MySQL server through socket 'var/lib/mysql/mysql.sock'. I know I am doing something very wrong here and mabe some of you might be able to help me solve this problem. I am running a SuSE Linux 8.2 version AMD Duron machine and already have a subscription to my ISP to access their database. ALso, the file mysql.sock does not exist in the mysql folder. Thanking you all very much for your kind help. Sincerely, ABHIJIT NAIK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help NEWBIE!
Hi var/lib/mysql/mysql.sock is created when MySQl runs, it may be a permissions problem with the directory - does MySql have write permission for this directory Peter -Original Message- From: Vishal Thakur [mailto:[EMAIL PROTECTED] Sent: 01 June 2003 10:37 To: ABHIJIT NAIK Cc: MySQL; SuSE Subject: Re: Help NEWBIE! re-install mysql, it wont work without mysql.sock. On Sun, 2003-06-01 at 01:04, ABHIJIT NAIK wrote: Dear Friends, I am new user of MySQL and whenever I type mysql to start the ware it simply states -- Can't connect to local MySQL server through socket 'var/lib/mysql/mysql.sock'. I know I am doing something very wrong here and mabe some of you might be able to help me solve this problem. I am running a SuSE Linux 8.2 version AMD Duron machine and already have a subscription to my ISP to access their database. ALso, the file mysql.sock does not exist in the mysql folder. Thanking you all very much for your kind help. Sincerely, ABHIJIT NAIK -- 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: Insert query
What I was going to say :: I was always told to stay clear of the varchar for performance, and that was on the mysql training course and the dev's. So I'd take that its relevant Jerry - Original Message - From: Nick Arnett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 3:49 PM Subject: RE: Insert query -Original Message- From: Ulterior [mailto:[EMAIL PROTECTED] Sent: Saturday, May 31, 2003 7:35 AM To: [EMAIL PROTECTED] Subject: Re: Insert query sow what would you suggest, Jerry? ( I need a very FAST search on this table's filename field) Ulterior Don't use varchar unless you absolutely have to, that should help. Jerry - Original Message - From: Ulterior [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 3:13 PM Subject: Insert query Hi, I have a database table: CREATE TABLE FTPFILE ( ID int(11) NOT NULL auto_increment, FTPSITEID int(11) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE int(11) NOT NULL, FILEDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID) ); when this table reaches 1 records, insert queries are very slow, aproximately 2 records a second. And worst of all mysql-ntd.exe reaches 99% of my processor timing. I am using native mysql C API mysql_query() func for inserting data. Is there any way to speedup insert querys to this table? ( I am planning to have at least 2-5 million entries in this table ) Your VARCHAR columns can become CHAR columns, which should help a lot. But CHAR columns can't be longer than 255, so you're about at the limit. Are you locking the table before inserting, then using the multiple insert syntax? Those should help if you're not doing them, but I don't know what it is in the C API. How many indexes do you have? If you reduce the number of indexes, insertions will go faster, but that might slow down some of your queries, of course. If you're inserting quite a few at a time, you might disable indexing, do the inserts, then allow the indexing to happen all at once. For a large number of inserts, you might also try writing them to a file, then loading it. That's much faster for really large numbers of inserts, and you can also suspend indexing as I mentioned. I use the latter for building externally generated text indexes (frequency tables and such), generating a couple of million records at a shot. It made an enormous difference. But I'm working in Python and can't help you with the C API, as I said. Nick -- 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: Insert query
varchar doesn't cause a problem, its a case of not using it speeds things up. As with all things, horses for courses, if you don't need to use it, don't. Jerry - Original Message - From: Andrew Braithwaite [EMAIL PROTECTED] To: 'Ulterior' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 8:05 PM Subject: RE: Insert query Hi, I would use mediumint rather than int for the ID column (int has support for up to 2.1 Billion records wheras mediumint is up to 8.3 million - more efficient for your data type). I don't think the varchar will cause much of a problem. Useful section here: http://www.mysql.com/doc/en/Data_size.html Also, do you really need 11 chars on the filesize column (that's 100GB)? If you're planning to have up to 5 million records then you only really need a length of 7 (8 to be safe). If you want a really fast search on filename then index that field. Another idea may be to turn on query caching. Here's what I would do.. CREATE TABLE FTPFILE ( ID mediumint(8) NOT NULL auto_increment, FTPSITEID mediumint(9) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE mediumint(9) NOT NULL, FILEDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID), KEY IDX_FN(FILENAME) ); Also there is an interesting section on the speed of inserts here: http://www.mysql.com/doc/en/Insert_speed.html Cheers, Andrew -Original Message- From: Ulterior [mailto:[EMAIL PROTECTED] Sent: Saturday 31 May 2003 15:35 To: [EMAIL PROTECTED] Subject: Re: Insert query sow what would you suggest, Jerry? ( I need a very FAST search on this table's filename field) Ulterior Don't use varchar unless you absolutely have to, that should help. Jerry - Original Message - From: Ulterior [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 31, 2003 3:13 PM Subject: Insert query Hi, I have a database table: CREATE TABLE FTPFILE ( ID int(11) NOT NULL auto_increment, FTPSITEID int(11) DEFAULT '0' NOT NULL, FILENAME varchar(254) DEFAULT 'Not defined' NOT NULL, FILEPATH varchar(254) DEFAULT 'Not defined' NOT NULL, FILEEXTN varchar(3) DEFAULT '---' NOT NULL, FILESIZE int(11) NOT NULL, FILEDATE datetime DEFAULT '-00-00 00:00:00' NOT NULL, PRIMARY KEY (ID) ); when this table reaches 1 records, insert queries are very slow, aproximately 2 records a second. And worst of all mysql-ntd.exe reaches 99% of my processor timing. I am using native mysql C API mysql_query() func for inserting data. Is there any way to speedup insert querys to this table? ( I am planning to have at least 2-5 million entries in this table ) Ulterior -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to install from source due to crash
Dennis, are you building on Red Hat 8.0? Our guess is that there is some bug in glibc-2.2.92 there. mysqld crashes just after thread creation on many Red Hat 8.0 computers if built from source on that computer. Workaround: use an official binary from www.mysql.com or build on Red Hat = 7.3. 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/ - Original Message - From: Dennis van der Meer [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Sunday, June 01, 2003 4:04 PM Subject: Unable to install from source due to crash Description: The file scripts/mysql_install_db crashes when trying to create the databases. Since the system isn't configured MySQL cannot start How-To-Repeat: Just a simple build from source will do the trick. There are no compile errors and even the make install works without problems Fix: Unknown Submitter-Id: Originator: Organization: MySQL support: none Synopsis: Unable to install from source due to crash of mysql_install_db Severity: critical Priority: medium Category: mysql Class: support Release: mysql-4.0.13 (Source distribution) C compiler:gcc (GCC) 3.2.2 C++ compiler: gcc (GCC) 3.2.2 Environment: machine, os, target, libraries (multiple lines) System: Linux caveman 2.4.20-ac2 #14 Mon Apr 14 23:57:48 CEST 2003 i686 unknown 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/i386-slackware-linux/3.2.2/specs Configured with: ../gcc-3.2.2/configure --prefix=/usr --enable-shared --enable-threads=posix --enable-__cxa_atexit --disable-checking --with-gnu-ld --verbose --target=i386-slackware-linux --host=i386-slackware-linux Thread model: posix gcc version 3.2.2 Compilation info: CC='gcc' CFLAGS='-O2' CXX='gcc' CXXFLAGS='-O2 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Apr 13 19:44 /lib/libc.so.6 - libc-2.3.1.so -rwxr-xr-x1 root root 1435624 Mar 5 06:57 /lib/libc-2.3.1.so -rw-r--r--1 root root 2425490 Mar 5 06:57 /usr/lib/libc.a -rw-r--r--1 root root 178 Mar 5 06:50 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--with-mysqld-ldflags=-all-static' 'CFLAGS=-O2' 'CXXFLAGS=-O2 -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' Full output from scripts/mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 sort_buffer_size=2097144 max_used_connections=0 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x83d8eb8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbf5fea48, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8087af8 0x822811a 0x8239057 0x8096d79 0x8098f69 0x8096b98 0x8092003 0x88e 0x825cda4 scripts/mysql_install_db: line 1: 31598 Segmentation fault /usr/local/mysql/libexec/mysqld --bootstrap --skip-grant-tables --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --skip-innodb --skip-bdb Installation of grant tables failed! Examine the logs in /usr/local/mysql/var for more information. You can also try to start the mysqld daemon with: /usr/local/mysql/libexec/mysqld --skip-grant You can use the command line tool /usr/local/mysql/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/mysql/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/mysql/var that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that
