Re: Problem with data import from text file (part two)
I have also read the manual section on this {as I should have
done] to try and understand it
and how to enable reading a local file - the discussion section
seems confusing and confused.
I tried following what others have done and
modified the ini file with set-variable=local-infile=0
and with set-variable=local-infile=1 and with
set-variable=local-infile[=1]
and tried each
I did this directly using Notepad and I did it using
WinMySQLAdmin's modify ini section.
Same error message as I have already reported on each
modification/
I obviously need to do something different
Regards
Adrian Greeman
please also send a separate smaller message to tell me the other
one is coming.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
It's just a standard text file; here is a piece of it: 70321;451 70050;451 70322;451 70323;451 70051;451 67026;451 70053;451 67420;451 67419;451 70451;451 70452;451 67820;451 72080;451 72081;451 72082;451 72083;451 20148;452 39761;452 20147;452 20112;452 Just lines like this: a little over 15,000 of them. :) -Mike -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:58 PM To: Mike At Spy; Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE [snip] Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' [/snip] Mike, could we see just a bit of the php9GOwvw file...just out of curiosity -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Replication through a tunnel.
Hello All, How will i setup Mysql Replication btween two redhat 7.3/9.0 boxes. I want the replication to happen through a Secure tunnel between these two. I am following the steps that is given on the offcial Mysql website. Just wants to know if there is any easy howto to set this up using a Tunnel. Any comments will be appreciated. Regards, Tina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FROM_DAYS doesn't take timestamp?
Hi,
I'm using v 3.23.46-nt and the C API.
The manual says under 'Changes in release 3.20.17' that:
Change FROM_DAYS() and WEEKDAY() to also take a full TIMESTAMP or DATETIME as
argument. Before they only took a number of type MMDD or YYMMDD.
I'm confused since this doesn't seem to be the case.
If I do SELECT from_days(731799); I get today's date. But if I try and
pass a 'timestamp' I get '-00-00' e.g.
SELECT from_days(now());
1) Is this a doc bug or am I not passing the 'timestamp' in the right
way?
2) I'm trying to get/set the date and time in a datetime column without
having to use formatted date strings ('-mm'dd hh:mm:ss'). I hoped to
use a long long integer to store the datetime as the number of seconds
(or any smaller unit) since the epoch. If from_days()/to_days() aren't
the functions to do this are there similar functions in mysql which
handle the time as well as the date? I don't want to use from_unixtime()
as I think the dates screw up in 2038.
Thanks,
Andy.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql LOAD DATA INFILE
That is messy, but you are right. I have FTP access and such - that's why I was wondering that with the new version of MySQL out, is this a change in MySQL? Or can the people who do have command line access re-compile it or something to give me permission to do what I need to do? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:30 PM To: [EMAIL PROTECTED] Subject: FW: mysql LOAD DATA INFILE Then the only way you can do it that I can think of is to write a PHP script to do basically what PHPMyAdmin is trying to do but without the LOCAL in there. However to do that you would need to be able to place the PHP file on the server, and I guess you probably can't do that either. Talk about catch 22... The only other way I can think of is to install MySQL on a machine you control, then import the data there using the method I suggested earlier. Once you have done that, you can use PHPMyAdmin to export the database from your machine. It should give you a SQL command that will create the data on another machine. Just copy and paste that into the SQL window on the main server and that will insert it. It's a little messy but that's the only other way I can think of doing it. Sorry. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:24 PM To: Donald Tyler; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Ah. No wonder it dinna work. Neither did specifying the columns as Jay suggested. I also have no choice but to do it through the browser - I don't have command line access on the server. :\ -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 4:08 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Oh and just a note. This solution won't work if you are uploading the file to the server through the browser. You will need to put the file on the server and adjust the commands PATH accordingly. -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:07 PM To: '[EMAIL PROTECTED]' Subject: FW: mysql LOAD DATA INFILE I see you still have the word LOCAL in there. Did you try and remove it? To do that in PHPMyAdmin you will need to run the import so you get the error message and then copy and paste it into the SQL section of the PHPMyadmin tool. Delete the world LOCAL and then run the query. It should work. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:59 PM To: Donald Tyler; [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE Sorry, that is the error - my mistake. I am getting this: LOAD DATA LOCAL INFILE '/tmp/php9GOwvw' INTO TABLE `this_one` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' I am using phpMyAdmin 2.3.3 - would an upgrade to the latest version remedy the issue? Thanks, -Mike -Original Message- From: Donald Tyler [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 3:42 PM To: [EMAIL PROTECTED] Subject: RE: mysql LOAD DATA INFILE PHPMyAdmin uses the LOAD DATA LOCAL INFILE command. Just remove the word LOCAL and it should work fine. -Original Message- From: Mike At Spy [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 2:35 PM To: [EMAIL PROTECTED] Subject: mysql LOAD DATA INFILE When I come across this error: The used command is not allowed with this MySQL version Does this mean that I need a whole different verison of MySQL, or just a different compile? The command was 'LOAD DATA INFILE' and I was doing it through phpMyAdmin. Thanks, -Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
Subject: Re: Problem with data import from text file (part two) Thanks
Thanks for the advice - and your patience in giving it to me. The recommended changes in the ini file worked (but only along with removing the word local from the load data local infile instruction) [since I have a localhost Apache server as testing environment] thanks again to those more knowledgeable. Regards Adrian Greeman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamchk Illegal Instruction
Paul Mahon [EMAIL PROTECTED] wrote: Hello, I recently managed to corrupt a table pretty badly. I read the sections in the documentation about recovering after a crash. None of the methods worked. All give output similar to the following: % bin/myisamchk -t ~/tmp -f -o BROKE/EventsBROKE - recovering (with keycache) MyISAM-table 'BROKE/EventsBROKE.MYI' Data records: 101333504 bin/myisamchk: error: 127 for record at pos 0 MyISAM-table 'BROKE/EventsBROKE' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) option % bin/myisamchk -t ~/tmp -e BROKE/EventsBROKE Checking MyISAM file: BROKE/EventsBROKE Data records: 101333504 Deleted blocks: 86920613 bin/myisamchk: warning: Table is marked as crashed and last repair failed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check records and index references Illegal instruction The mysql server version is fairly old, 3.23.54 but unfortunatly it can't be upgraded. The table that is corrupted is large... in the range of 14GB. I couldn't find a reference to this error in my searches, so I apologise if this has been covered before, but has anyone encountered these problems before? And more importantly, were you able to fix it? You can repair table with REPAIR .. USE_FRM, but it's supported since 4.0.2. -- 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: Running two Servers?
Hi Why would you need two servers running when you could just have separate databases?? Is there a reason why these two cannot share?? Mike - Original Message - From: Ola Ogunneye [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: August 7, 2003 8:46 AM Subject: Running two Servers? I am looking into installing 2 mysql servers on the same windows 2000 PC. From what I have found, it is usually possible to have 2 different versions on the same machine. (Usually one for testing and the other for production). However, I am trying to run 2 production servers, one for personal family info, and the other for business opportunity. Being that I just started using mysql, is it possible then to have the same version installed twice, or how do I accomplish my task? Thanks all Ola -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql installation problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Wed, 6 Aug 2003, Anil Garg wrote: I am trying to install myql on redhat. Red Hat Linux? mysql package used: mysql-3.23.52-unknown-freebsdelf4.6-i386.tar.gz This is the wrong package - this one is for FreeBSD! Please use the Linux binaries instead. scripts/mysql_install_db: ./bin/my_print_defaults: /usr/libexec/ld-elf.so.1: bad ELF interpreter: No such file or directory scripts/mysql_install_db: ./bin/mysqld: /usr/libexec/ld-elf.so.1: bad ELF interpreter: No such file or directory FreeBSD binaries do not run on Linux. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany For technical support contracts, visit https://order.mysql.com/?ref=mlgr -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE/MhEhSVDhKrJykfIRAqNGAJkBJYbw9uzsIOv48A+wQ9ol/BWOHQCfeAu7 yXzHGHVLi4tLINiXyomftLM= =3+9Z -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Control Center
In Windows MySQLCC open a query window; select query menu; query window options; query options tab; set SQL_BIG_SELECTS=1. http://www.mysql.com/doc/en/SET_OPTION.html Quote: SQL_BIG_SELECTS = 0 | 1 If set to 0, MySQL will abort if a SELECT is attempted that probably will take a very long time. This is useful when an inadvisable WHERE statement has been issued. A big query is defined as a SELECT that probably will have to examine more than MAX_JOIN_SIZE rows. The default value for a new connection is 1 (which will allow all SELECT statements). - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Cabanillas Dulanto Ulises [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, August 09, 2003 9:21 AM Subject: Re: MySql Control Center Hi Ulises, Is that option only available on a Windows MySQLCC ?? I didnt find it on the linux version. Best regards Nils Valentin Tokyo/Japan 2003 8 9 01:47Cabanillas Dulanto, Ulises : The option Automatically limit SELECT queries to must be set in the Query Configuration Dialog. Regards, Ulises -Mensaje original- De: Trevor Morrison [mailto:[EMAIL PROTECTED] Enviado el: Viernes 8 de Agosto de 2003 09:36 AM Para: [EMAIL PROTECTED] Asunto: MySql Control Center Hi, I am new to using the Control Center-0.9.2-beta. My problem is that I cannot seem to display more than 1000 rows in my result set when I know that there is 1534 rows. I know that it is a setting somewhere, but is it in MySql or in the Control Center somewhere. I am running this on a Windows 2000 machine. TIA Trevor -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.506 / Virus Database: 303 - Release Date: 01/08/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
win200 - lost root privilege - cannot shutdown - need help
I am working on a Windows 2000 server. The version 4.0.13 was installed at the end of June because we are looking at using MySQL. Me and the other guy are novice with MySQL. I read the different emails about access and granting and password and flushing privilege from other people who couldn't get access. To get to the point, I know that the guy has altered the privileges and lost the password he would have set for root. mysqld was started from DOS window using mysqld and the window was closed. When I go to Task Manager, I see the mysqld process running. When I use now mysqld, if I try to specify root, because of not knowing the root password, I can't get correct privilege to run the shutdown command. If I just type mysql, I am user [EMAIL PROTECTED] and the only database I see is test. Since mysql database is not visible, I know that I cannot set privilege from this user. So my goal is to desinstall v4.0.13, and re-install v4.0.14. But my current issue is how do I stop the mysqld process? Even if stopping from Task Manager is not the best approach, do I have another option? Please help. Christophe Poirier
Re: replication problem
So does anyone else have any ideas what is going on here? Shall I report this as a bug? On Wed, Aug 06, 2003 at 02:19:21PM +0100, Andy Smith wrote: Hi folks, trying to set up replication and I'm getting this problem which I can't see how to fix despite reading of the manual and google. I have set up a master and a slave according to the manual. The master runs 4.0.12 and the slave runs 4.0.14, they are both actually on the same machine (it's a long story, but it's what I need). I did LOAD DATA FROM MASTER on the slave which completed successfully, and then I tried SLAVE START: ERROR 1200: The server is not configured as slave, fix in config file or with CHANGE MASTER TO but.. it is! on the slave: mysql show slave status\G *** 1. row *** Master_Host: 127.0.0.1 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: angora-bin.001 Read_Master_Log_Pos: 20102800 Relay_Log_File: angora-relay-bin.001 Relay_Log_Pos: 4 Relay_Master_Log_File: angora-bin.001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 20102800 Relay_log_space: 8 1 row in set (0.00 sec) On the master: mysql show master status; ++--+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | ++--+--+--+ | angora-bin.001 | 24954609 | | | ++--+--+--+ 1 row in set (0.01 sec) Any ideas what I missed? -- I remember the first time I made love. Perhaps it was not love exactly but I made it and it still works. -- The League Against Tedium -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- thoughtless whats the best place you've ever done it? G-Shock mum and dads bed MrMoves Adam, you were abused???!! ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Control Center
Hi, I am new to using the Control Center-0.9.2-beta. My problem is that I cannot seem to display more than 1000 rows in my result set when I know that there is 1534 rows. I know that it is a setting somewhere, but is it in MySql or in the Control Center somewhere. I am running this on a Windows 2000 machine. TIA Trevor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select count(*) Problem in PowerBuilder Thru MySQL ODBC 3.51
Yes, found the problem, you are right. Thanks. But this is something caused by powerbuilder, in my code I never put any space between count(*), but when it goes to odbc, an space is added. Any idea how to solve this? -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, August 08, 2003 12:17 AM To: Fatt Shin Cc: [EMAIL PROTECTED] Subject: Re: select count(*) Problem in PowerBuilder Thru MySQL ODBC 3.51 You are not doing select count(*) You are doing select count ( *) Get rid of the spaces before the ( Fatt Shin wrote: Hi, I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB Connector 3.51. I'm facing a problem where whenever I issue a SELECT COUNT(*) statement from PowerBuilder, I always get SQL syntax error back from MySQL. (Refer to ODBC Trace I captured below). metrohouse af8-b94 ENTER SQLExecDirect HSTMT 014D2360 UCHAR * 0x020A0EA2 [ -3] select count ( *) from code \ 0 SDWORD-3 metrohouse af8-b94 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 014D2360 UCHAR * 0x020A0EA2 [ -3] select count ( *) from code \ 0 SDWORD-3 DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-nt]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 '( *) from code' at line 1 (1064) metrohouse af8-b94 ENTER SQLErrorW HENV014D12A0 HDBC014D14B0 HSTMT 014D2360 WCHAR * 0x0012E65C (NYI) SDWORD *0x0012E6A8 WCHAR * 0x0012E25C SWORD 512 SWORD * 0x0012E6B0 metrohouse af8-b94 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV014D12A0 HDBC014D14B0 HSTMT 014D2360 WCHAR * 0x0012E65C (NYI) SDWORD *0x0012E6A8 (1064) WCHAR * 0x0012E25C [ 208] [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-nt]You have an error in your SQL syntax. Check the manual t SWORD 512 SWORD * 0x0012E6B0 (208) The same statement actually working fine whether I ran it using mysql or sql yog or even using the same ODBC connector thru Microsoft Access. (Refer to ODBC Trace below) MSACCESSfd4-ff4ENTER SQLExecDirectW HSTMT 09BB18C8 WCHAR * 0x0B431048 [ -3] SELECT COUNT(* ) FROM `code` \ 0 SDWORD-3 MSACCESSfd4-ff4EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS) HSTMT 09BB18C8 WCHAR * 0x0B431048 [ -3] SELECT COUNT(* ) FROM `code` \ 0 SDWORD-3 Anybody have any idea what may cause the error here ??? Thanks a lot. Regards, FattShin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
arbitrary ordering
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I have a few tables something like create table SURVEY ( SID int primary key not null auto_increment, SNAME varchar(20), QUESTION_ORDER varchar(20) ) ; create table SURVEY_QUESTION ( QID int primary key not null auto_increment, QBODY varchar(255), OPTION_ORDER varchar(20) ) ; create table SURVEY_OPTION ( OID int primary key not null auto_increment, OBODY varchar(255) ) ; and in the QUESTION_ORDER field I store a list of numbers 12 13 21 14 while in OPTION_ORDER it might be 432 435 435 550 or such. I want to do a select on all three (double left join, if I am starting to grasp this stuff :-) ordered first by the QID as shown in QUESTION_ORDER and then by the OID as shown in OPTION_ORDER. Is there a way to tell my join-and-select statement the sort order based on the contents of another field (but not simply sorting on that field)? TIA HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/NHTHGb7uCXufRwARAtPvAKDql3YjpBwwEpS5trzncnOzeTjXUACfZo93 Kep54aY/EeVXaCXXlItbKl0= =qi7g -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dirk Kunischewski/UK/IBM is out of the office.
I will be out of the office starting October 11, 2002 and will not return until November 4, 2010. Please contact Gerry McNamee ([EMAIL PROTECTED]) or Aurelien Thouard ([EMAIL PROTECTED]) since I left IBM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Updating table based upon matching field in second table
On Fri, 2003-08-08 at 21:14, Matthew McNicol wrote: I have a database of books that was originally created as a flat file. Each record has a number of fields, including the authors name. I'm trying to convert the database to something a little more efficient. I've created a new table (called Authors) of unique authors names and assigned each one a unique ID. I've added a new field in the original table (called Books) for the author's ID. Now, I need to update the original table with the author ID from the Author's table. Something like this: UPDATE Books SET AuthorID = Authors.AuthorID WHERE AuthorName = Authors.AuthorName you were close, try:- update books, authors set books.AuthorID = authors.AuthorID where books.first_name = authors.first_name and books.last_name = authors.last_name; Ah, so you have to list both tables in the update clause even though you're only changing one of them. That works like a charm. Thanks! Notes - you have loaded the existing flat file into a MySQL table called books. - you mentioned that you have altered the books table to add a new field called AuthorID which is good. - you have created a new table called authors where the unique key is AuthorID. so, - just run the update query, then remove the author name field(s) from the books table which are no longer needed. Yes, this was my plan. I'll also need to edit the authors table to split the names into first and last. In the flat file, author's name was a single field and editing it first would have ruined the correlation. This leads me to another question. What's the standard way of handling something like the author of a book when you have a collaboration or an anthology with multiple authors? The only way I can think of to handle it is to have a sentinel value that indicates multiple authors, then have another table which lists the primary key of the books table and associates it with multiple authors. For example: BookID AuthorID 15 22 15 39 15 43 27 03 27 94 This would show that book 15 had three authors - 22, 39 and 43 while book 27 had two authors - 03, 94. Of course, this would vastly complicate displaying or printing the database. It would also make it difficult for searches for a particular author to find books with multiple authors, etc. Is there an easier way to design the database which deals with these issues? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Remote access for mysql what variable is needed to be set
Jatin,
I am running mysql 4 on linux server. I want to have a remote access
on it. what are the setting that i need to do this.
You need to log into your MySQL server and grant access to your
databases from wherever you want to grant access, like:
mysql GRANT ALL ON *.* TO 'remoteuser'@'%' IDENTIFIED BY 'seCr3t';
That would allow access from anywhere ('%'), and allow to do anything
(ALL) on all databases and their contained tables (*.*) for a user
'remoteuser' that must give 'seCr3t' as a password when logging in.
This can, of course, be much more fine-tuned; see
http://www.mysql.com/doc/en/User_Account_Management.html for details.
'remoteuser' can now connect to the server like this:
shell mysql --host=mysqlhost --user=remoteuser -p
Where 'mysqlhost' is the name of the machine mysqld (the server) is
running. If the server is listening on a port other than 3306, you
must also specify --port=portnumber.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
I am MySQL certified - are you?
http://www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database per user
hi, it's possible in mysql set the db folder into home user? example: user fred, home is /home/fred i want store db data in /home/fred/db. it's possible? i've tested in /home/fred/.my.cnf this: datadir=/$HOME/db but don't function i expose the /etc/my.cnf and /home/fred/.my.cnf for more details. goobye *dump /etc/my.cnf [mysqld] #datadir=$HOME/db datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~ *dump /home/fred/.my.cnf [mysqld] # Example mysql config file for small systems. # # This is for a system with little memory (= 64M) where MySQL is only used # from time to time and it's important that the mysqld deamon # doesn't use much resources. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # One can in this file use all long options that the program supports. # If you want to know which options a program support, run the program # with --help option. # The following options will be passed to all MySQL clients [mysqld] datadir=home/matteo/db socket=/var/lib/mysql/mysql.sock skip-innodb [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=16K set-variable= max_allowed_packet=1M set-variable= thread_stack=64K set-variable= table_cache=4 set-variable= sort_buffer=64K set-variable= sort_buffer=64K set-variable= net_buffer_length=2K server-id = 1 # Uncomment the following if you want to log updates #log-bin # Uncomment the following if you are NOT using BDB tables #skip-bdb # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #set-variable = innodb_buffer_pool_size=16M #set-variable = innodb_additional_mem_pool_size=2M # Set .._log_file_size to 25 % of buffer pool size #set-variable = innodb_log_file_size=5M #set-variable = innodb_log_buffer_size=8M #innodb_flush_log_at_trx_commit=1 #set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=8M set-variable= sort_buffer=8M [myisamchk] set-variable= key_buffer=8M set-variable= sort_buffer=8M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date Calculation
Hi List I have two datetime fields on my database. I nedd to return the difference in minutes between them. I tried date_sub, extract(hour_minute from ...) and it does't work Any help will be very apreciated. Thanks Oswaldo Castro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Crash when using default-character-set tis620 (Thai sort order)
Description: When i using default-character-set tis620 (Thai sort order) mysql server has been crash and then I test to use latin1 can work fine but can't sort order in thai language. How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:apples Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.14 (Source distribution) Server: /usr/local/mysql/bin/mysqladmin Ver 8.40 Distrib 4.0.14, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.14 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/run/mysql/mysql.sock Uptime: 8 days 11 hours 45 min 23 sec Threads: 12 Questions: 463018 Slow queries: 0 Opens: 9665 Flush tables: 1 Open tables: 47 Queries per second avg: 0.631 C compiler:2.95.4 C++ compiler: 2.95.4 Environment: System: Linux mail 2.4.18-bf2.4 #1 Son Apr 14 09:53:28 CEST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs gcc version 2.95.4 20011002 (Debian prerelease) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 ¾.¤. 12 19:41 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1153784 àÁ.Â. 8 20:01 /lib/libc-2.2.5.so -rw-r--r--1 root root 2391002 àÁ.Â. 8 20:02 /usr/lib/libc.a -rw-r--r--1 root root 178 àÁ.Â. 8 20:02 /usr/lib/libc.so -rw-r--r--1 root root 716080 Á.¤. 14 2002 /usr/lib/libc-client.so.2001 Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/run/mysql/mysql.sock' '--prefix=/usr/local/mysql' '--with-extra-charsets=complex' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IP Addresses MySQL Listens On
Jim McAtee wrote: Quick question or two regarding the IP addresses MySQL listens on: Does MySQL automatically listen on all IP addresses bound to the machine on which it runs? (MySQL 3.23.xx on Windows 2000). I've got a multi-homed server with dual NICs. To date I've always connected from other servers on an IP address bound to NIC #1. I'd like to change this to use an IP address bound to NIC #2, on a different subnet. Will this require any configuration changes to the MySQL server? It's been quite a while since I first configured the server. I am using 'bind-address=127.0.0.1' in my.ini to prohibit all external contacts -but via the local webserver ;-)-. So, I see no reason for you to change your config, unless the service is started with the option '--bind-address=IP-NIC#1' or has 'bind-address=IP-NIC#1' in its my.ini. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IP Addresses MySQL Listens On
Quick question or two regarding the IP addresses MySQL listens on: Does MySQL automatically listen on all IP addresses bound to the machine on which it runs? (MySQL 3.23.xx on Windows 2000). I've got a multi-homed server with dual NICs. To date I've always connected from other servers on an IP address bound to NIC #1. I'd like to change this to use an IP address bound to NIC #2, on a different subnet. Will this require any configuration changes to the MySQL server? It's been quite a while since I first configured the server. Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to local MySQL question
Bill Hernandez wrote: I followed the install instructions at http://entropy.ch , and was able to get mySql php installed on my G4 - (OSX 10.2.6) last night. I downloaded Navicat and setup a password for the mysql user. I created a connection called myDatabase_connection to a database called myDatabase. I was able to get everything running OK. Did the server start and could you connect ? Today when I restarted the machine, I tried to launch the php program that I was running last night, and kept getting an error 2002 below. 1) Have the mysql daemon running 2) connect with a client [Home:/usr/local/mysql] justMe# ./bin/mysqld_safe [1] 479 [Home:/usr/local/mysql] justMe# Starting mysqld daemon with databases from /usr/local/mysql/data 030809 20:04:58 mysqld ended there is an error.log in the data directory. Look there for mysql's complaints. HERE I HIT RETURN TO GET THE PROMPT AGAIN (Should I have typed the name of the database here, then hit return?) no You are mixing up the server and client process. mysqld is the server. It runs in the background and stores databases in it's data directory. There can be many separate databases. You'll select one of them later when you connect with the client. mysql -- no d (=daemon) is the textmode client that comes with the mysql package. [1]Done ./bin/mysqld_safe [Home:/usr/local/mysql] justMe# ./bin/mysql myDatabase ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Without running server-process there is no socket to connect to. start by looking at the err-file in the data-dir. You can run mysqld_save without the to see more output. Sometimes the server comes up that way. Then you wont regain access to the shell where you ran mysqld_save. I had a hard time getting this kind of error. One day I figured out, that mc caused the hick-up. mc is a textmode filemanager I really use often. mysqld started and died at once again. If mc produces this effect then perhaps other filemanaging tools do, too. Use pure bash or xterm. Better yet, install mysql as a service to have it started at boot time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
read_const error 127 - then MySQL dies
Hi all, The following keeps happening and I can't pinpoint a query that is causing it. It did not happen in 3.23.x, but started upon upgrading to 4.0.14. The operating system/hardware information is as follows: RedHat 8.0 - kernel 2.4.18SMP 4x Xeon Processors 4x 80GB SCSI drives (hardware RAID-10) 2GB RAM The following is a log exerpt: 030808 15:22:09 read_const: Got error 127 when reading table 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 max_used_connections=184 max_connections=1000 threads_connected=21 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2184184 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x98772088 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=0x98a4ad98, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80dbe1f 0x4003b47e 0x8101e09 0x810e90d 0x80e6d8a 0x80ea88b 0x80e5ed3 0x80ebe0e 0x80e50bf 0x40035941 0x420da1ca New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x89af670 = SELECT * FROM order_data WHERE viewed='' ORDER BY order_num DESC thd-thread_id=42660972 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 030808 15:22:19 mysqld restarted 030808 15:22:20 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 5 4012008225 InnoDB: Doing recovery: scanned up to log sequence number 5 4012079335 030808 15:22:20 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 1058709429, file name ./db1-bin.062 030808 15:22:21 InnoDB: Flushing modified pages from the buffer pool... 030808 15:22:21 InnoDB: Started /usr/sbin/mysqld-max: ready for connections. Version: '4.0.14-Max-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 This happens often. Any ideas? Thanks. -- Richard Gabriel [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lowering the ft_min_word_len
On Tue, 2003-08-05 at 10:57, Paul DuBois wrote:
At 10:30 -0700 8/5/03, Justin Hopper wrote:
Hello,
I have a table with a FULLTEXT index on a column of type 'text'.
Searches on this table using MATCH() AGAINST() work fine for most
words. However, I needed to match against a 3 letter word. So I
lowered the ft_min_word_len to 3 in /etc/my.cnf. I then restarted
MySQL. I checked that the variable was set to 3 in the running mysqld.
I don't see that you rebuilt your FULLTEXT indexes after restarting
the server. Did you?
Yes, I did rebuild the indexes. Sorry I didn't mention that before. I
assume the word 'key' would not be picked up if I had not rebuilt the
indexes after lowering the ft_min_word_len.
But for some reason, I cannot fetch any results:
mysql select title_id from support_doc_articles where match(article)
against ('dns');
Empty set (0.00 sec)
It does not work IN BOOLEAN MODE either:
mysql select title_id from support_doc_articles where match(article)
against ('dns' IN BOOLEAN MODE);
Empty set (0.00 sec)
Actually, I just tried it again, searching for the 3 letter word 'key',
and it brought back results. Is 'dns' in the stopwords list? Is there
any way I can see what words are in there? Can I exclude words from the
stopword list without recompiling MySQL?
They're in the file myisam/ft_static.c in the source distribution. dns
is not one of them.
I don't believe you can exclude words from the list without recompiling.
Hmmm, any ideas why the word 'dns' would not be picked up then?
Thanks for any help.
--
Justin Hopper
UNIX Systems Engineer
Spry Hosting
http://www.spry.com
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
Justin Hopper
UNIX Systems Engineer
Spry Hosting
http://www.spry.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
