Comma Delimited Import Error
I just discovered that one of my MySQL tables is incomplete. I created it by importing a comma delimited file I had made from a spreadsheet, but it appears as many as 3,000 rows or so were missing. So I decided to give it another try. MySQL Front refused to import it because of some sort of error I haven't yet identified. Navicat imports it, logging roughly 80 errors. When I view the finished table, many of the entries in the first field are followed by ,. For example... al, tx, Here are a few rows from the comma delimited file I imported. Do you have a hunch what might be going on here? Thanks. gd,oat,239,,Grenada,nat gp,oat,240,,Guadeloupe,dep ht,oat,241,,Haiti,nat is,oat,242,,Iceland,nat jm,oat,243,,Jamaica,nat __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get fields in one field, separated by comma's
- Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, June 05, 2004 9:50 PM Subject: Re: Get fields in one field, separated by comma's Klaas Geldof [EMAIL PROTECTED] wrote: I have a question about MySQL queries. First I will explain the situation, then I will provide an example. Situation: I have the tables 'offer', 'contact' and 'offer_contact'. Table 'offer' contains data about offers, table 'contact' about contacts. Table 'offer_contact' tells which contacts are associated with which offers (since offers can have multiple contacts associated with it and vice versa). Now I want to run a query so I get a row for each offer and in the result there's a field 'offer_contacts' which contains all the names of the contacts associated with the offer on that row separated by comma's. Example: Table 'offer' (offer_id - offer_title): 1 - Test 2 - Also test Table 'contact' (contact_id - contact_name): 1 - John 2 - Anna Table 'offer_contact' (offer_id - contact_id): 1 - 1 1 - 2 2 - 1 Wanted result of query (offer_id - offer_name - offer_contacts): 1 - Test - John, Anna 2 - Also test - John Is this possible? GROUP_CONCAT() function may help. This function is available from version 4.1: http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html Works great. Thanks! Just a little problem when I add a HAVING clause. For example this query: SELECT offer_id, GROUP_CONCAT(contact_name SEPARATOR ', ') AS contact_names FROM offer LEFT OUTER JOIN offer_contact ON offer_contact_offer_id = offer_id LEFT OUTER JOIN contact ON offer_contact_contact_id = contact_id GROUP BY offer_id HAVING contact_names REGEXP 'john' This returns in the field 'contact_names' a comma-separated lists of the contact names of an offer, but the last two characters of the list (that is, the last two characters of the last name in the list) are gone! Without a HAVING clause, the error does not occur. Is this a bug? -- Klaas Geldof -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5.0: ERROR 1005 (HY000)
create database url; use url create table cath ( name varchar(10) primary key, ) type=innodb; create table site ( url varchar(40) primary key, aline varchar(40), cath varchar(10), constraint fk_cath foreign key(cath) references cath(name) on delete set null on update cascade ) type=innodb; ERROR 1005 (HY000): Can't create table './url/site.frm' (errno: 150) the perms are correct indeed without the constraint there are no problems. can you help me in testing? mororover when running the test suite i have: Errors are (from /usr/local/mysql/mysql-test/var/log/mysqltest-time) : /usr/local/mysql/bin/mysqltest: At line 26: query 'CREATE TABLE `ÔÁÂÌÉÃÁ` ( ÐÏÌÅ CHAR(32) CHARACTER SET koi8r NOT NULL COMMENT ËÏÍÍÅÎÔÁÒÉÊ ÐÏÌÑ ) COMMENT ËÏÍÍÅÎÔÁÒÉÊ ÔÁÂÌÉÃÙ' failed: 1: Can't create/write to file './test/ÑÐ °Ð±Ð»Ð¸Ñа.frm' (Errcode: 22) (the last line(s) may be the ones that caused the die() in mysqltest) and this: Errors are (from /usr/local/mysql/mysql-test/var/log/mysqltest-time) : /usr/local/mysql/bin/mysqltest: At line 41: Result length mismatch (the last line(s) may be the ones that caused the die() in mysqltest) Below are the diffs between actual and expected results: --- *** r/timezone.result Mon Dec 22 18:41:49 2003 --- r/timezone.reject Sun Jun 6 09:03:22 2004 *** *** 1,7 DROP TABLE IF EXISTS t1; show variables like timezone; Variable_name Value ! timezone MET select @a:=FROM_UNIXTIME(1); @a:=FROM_UNIXTIME(1) 1970-01-01 01:00:01 --- 1,7 DROP TABLE IF EXISTS t1; show variables like timezone; Variable_name Value ! timezone MEST select @a:=FROM_UNIXTIME(1); @a:=FROM_UNIXTIME(1) 1970-01-01 01:00:01 --- Failed 2/229 tests, 99.12% successful. -- here are more things in heaven and earth, horatio, than are dreamt of in your philosophy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get fields in one field, separated by comma's
- Original Message - From: Klaas Geldof [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 06, 2004 10:19 AM Subject: Re: Get fields in one field, separated by comma's - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, June 05, 2004 9:50 PM Subject: Re: Get fields in one field, separated by comma's Klaas Geldof [EMAIL PROTECTED] wrote: I have a question about MySQL queries. First I will explain the situation, then I will provide an example. Situation: I have the tables 'offer', 'contact' and 'offer_contact'. Table 'offer' contains data about offers, table 'contact' about contacts. Table 'offer_contact' tells which contacts are associated with which offers (since offers can have multiple contacts associated with it and vice versa). Now I want to run a query so I get a row for each offer and in the result there's a field 'offer_contacts' which contains all the names of the contacts associated with the offer on that row separated by comma's. Example: Table 'offer' (offer_id - offer_title): 1 - Test 2 - Also test Table 'contact' (contact_id - contact_name): 1 - John 2 - Anna Table 'offer_contact' (offer_id - contact_id): 1 - 1 1 - 2 2 - 1 Wanted result of query (offer_id - offer_name - offer_contacts): 1 - Test - John, Anna 2 - Also test - John Is this possible? GROUP_CONCAT() function may help. This function is available from version 4.1: http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html Works great. Thanks! Just a little problem when I add a HAVING clause. For example this query: SELECT offer_id, GROUP_CONCAT(contact_name SEPARATOR ', ') AS contact_names FROM offer LEFT OUTER JOIN offer_contact ON offer_contact_offer_id = offer_id LEFT OUTER JOIN contact ON offer_contact_contact_id = contact_id GROUP BY offer_id HAVING contact_names REGEXP 'john' This returns in the field 'contact_names' a comma-separated lists of the contact names of an offer, but the last two characters of the list (that is, the last two characters of the last name in the list) are gone! Without a HAVING clause, the error does not occur. Is this a bug? I tested some things myself, and I think it's a bug. When I take SEPARATOR ',' (instead of ', ') only one character is missing at the end. With SEPARATOR '' no characters are missing... Does anyone know if this is fixed already (I'm using MySQL 4.1)? Or a way to work around it? Thanks. -- Klaas Geldof -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mixing GROUP BY, AVG and COUNT
--Original Message- I have a table where the date a record was added is recorded in the date column. I can get count of how many records were entered on each day by doing this SELECT COUNT(*) FROM table GROUP BY date; I can get a total number of records by doing SELECT COUNT(*) FROM table but how do I find the average count per day? We can ignore the fact that it is possible that no records are added on a given day. I almost for got this is on 4.0.18 so nested selects are not an option. -- Chris W If I understood the question correctly, what you need is something like: SELECT @totaldates:=COUNT(DISTINCT date) FROM Table; SELECT COUNT(*)/@totaldates AS Average FROM Table; Regards Terry Riley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comma Delimited Import Error
David
looking at
http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
Any of the field- or line-handling options can specify an empty string ('').
If not empty, the FIELDS [OPTIONALLY] ENCLOSED BY and FIELDS ESCAPED BY
values must be a single character.
Addionally it is a good idea to say FIELDS TERMINATED BY ',' FIELDS ENCLOSED
BY ''
LINES TERMINATED BY '\n'
hth,
Martin
- Original Message -
From: David Blomstrom [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, June 06, 2004 2:40 AM
Subject: Comma Delimited Import Error
I just discovered that one of my MySQL tables is
incomplete. I created it by importing a comma
delimited file I had made from a spreadsheet, but it
appears as many as 3,000 rows or so were missing.
So I decided to give it another try. MySQL Front
refused to import it because of some sort of error I
haven't yet identified. Navicat imports it, logging
roughly 80 errors.
When I view the finished table, many of the entries in
the first field are followed by ,. For example...
al,
tx,
Here are a few rows from the comma delimited file I
imported. Do you have a hunch what might be going on
here?
Thanks.
gd,oat,239,,Grenada,nat
gp,oat,240,,Guadeloupe,dep
ht,oat,241,,Haiti,nat
is,oat,242,,Iceland,nat
jm,oat,243,,Jamaica,nat
__
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
--
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: FreeBSD 5.x
Hi, Thanks for the reply :-)) Any corruptions on MyIsam tables ? Cause in my case tables seems to crash a lot on the freebsd machines, processor almost die on 4.x ... Best regards, Peter Zyumbilev Project Manager Mansion Productions Inc. AIM: peterZmansion YMS: peterZmansion MSN: [EMAIL PROTECTED] Skype: peterZmansion ICQ: 108984925 support: http://support.hostmansion.com (24/7) email: [EMAIL PROTECTED] web: www.mansionproductions.com MPA2 - Mansion Production Automation Program MPA3 - Next Generation Automation Program CSS - Content Sale System MTS - Mansion Ticket System Hostmansion.com - Highest Quality Web Hosting The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the intended recipient of this message you are hereby notified that any use, review, retransmission, dissemination, distribution, reproduction or any action taken in reliance upon this message is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of the company. - Original Message - From: Gerrit Beine To: Peter Sent: Sunday, June 06, 2004 12:23 PM Subject: Re: FreeBSD 5.x -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter wrote: | Hi, | Does anybody have experience with FreeBSD 5.x | Mysql ? | FreeBSD 4.x and mysql seems to be awful combination due to threading , but I have no tried FreeBSD 5.x with mysql under heavy load | Hi, I have a running MySQL 4.0.18 on FreeBSD 5.2.1. It's 3 times faster than running under Linux 2.6. The server is a dual Xeon 2,7 with 2 Gigabytes of RAM. Especially fulltext search (which produces a very heavy load) runs acceptable. So long... Gerrit - -- beine computer support für open source software seumestrasse 82 08525 plauen fon: 0049 3741 404 837 fax: 0049 3741 404 839 gsm: 0049 178 279 48 32 mail: [EMAIL PROTECTED] url: http://www.beine-computer.de icq: 123851585 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFAwuKD9K8OA2Y695gRAgsDAJ9kLHl7Qddx7lwXywVr6sNNWjqsPQCeL1Ya wTgvuod8UxGw/1ItSkOT0QM= =RiNR -END PGP SIGNATURE-
Re: FreeBSD 5.x
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter wrote: | Hi, | Thanks for the reply :-)) | Any corruptions on MyIsam tables ? Cause in my case tables seems to | crash a lot on the freebsd machines, processor almost die on 4.x ... | Nothing like this. I have a lot of MyISAM tables and a view InnoDB Tables. There was no crash since April. So long... Gerrit - -- beine computer support für open source software seumestrasse 82 08525 plauen fon: 0049 3741 404 837 fax: 0049 3741 404 839 gsm: 0049 178 279 48 32 mail: [EMAIL PROTECTED] url: http://www.beine-computer.de icq: 123851585 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFAwxeS9K8OA2Y695gRAkw3AKDwHNnoqBvw7U4tphVerCavEPE4TwCgs1ha cX0FX4Ca33uxND9cjj3DDiU= =6E2l -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
URGENT !! InnoDB Constraint problem !
I have a problem with my innodb database wich freaks me out ,
first a description of the involving tables :
mysql describe pages ;
+-+--+--+-+-
++
| Field | Type | Null | Key | Default | Extra
|
+-+--+--+-+-
++
| id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| locked | enum('true','false') | | | false |
|
| protected | enum('true','false') | | | false |
|
| hidden | enum('true','false') | | | false |
|
| name| varchar(32) | | | |
|
| page_number | smallint(16) | | | 0 |
|
| data| blob | | | |
|
| is_section_root | enum('true','false') | | | false |
|
| layout_id | smallint(5) unsigned | | MUL | 0 |
|
| group_id| smallint(5) unsigned | | MUL | 0 |
|
+-+--+--+-+-
++
10 rows in set (0.00 sec)
mysql describe page_groups ;
+++--+-+-
++
| Field | Type | Null | Key | Default |
Extra |
+++--+-+-
++
| id | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| button | smallint(5) unsigned | | | 0 |
|
| locked | enum('true','false') | | | true|
|
| protected | enum('true','false') | | | false |
|
| hidden | enum('false','true') | | | false |
|
| name | varchar(16)| | | |
|
| lang | enum('nl','fr','en','de','es') | | | nl |
|
| variant| varchar(16)| | | |
|
| section| varchar(64)| | | |
|
| descript | tinyblob | | | |
|
| version_id | smallint(5) unsigned | | MUL | 0 |
|
+++--+-+-
++
11 rows in set (0.00 sec)
mysql describe templates ;
+---+---+--+-+-
++
| Field | Type | Null | Key | Default | Extra
|
+---+---+--+-+-
++
| id| smallint(5) unsigned | | PRI | NULL|
auto_increment |
| type | enum('layout','item') | | | layout |
|
| locked| enum('true','false') | | | true|
|
| protected | enum('true','false') | | | false |
|
| name | varchar(64) | | | nieuw |
|
| descript | tinyblob | | | |
|
| templ | blob | | | |
|
+---+---+--+-+-
++
7 rows in set (0.00 sec)
now i insert a new page_group:
mysql INSERT into
page_groups(button,locked,protected,hidden,name,variant,section,lang,des
cript,version_id) values(0,2,2,2,'new en','b2c','nieuwe
sectie','en','',1) ;
this goes fine
so i store the insert id
mysql set @LID = LAST_INSERT_ID() ;
goes fine too ;
then i want to use @LID as constraint-key to insert a new page ;
INSERT INTO
pages(hidden,name,page_number,data,is_section_root,layout_id,group_id)
VALUES(1,'nieuwe pagina',100,'',2,1,@LID);
this fails with a ERROR 1216: Cannot add a child row: a foreign key
constraint fails
the weird thing is that both the primaries templates:1(layout_id)
and page_groups:@LID(group_id) exist .
Replacing @LID by is numeric value gives the same error althoug if i
choose an other existing row in page_groups then all works fine
I'm in auto-commit mode and suspect that something does not get updated
but what ?? an index ??
How does this come and how can i fix it
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default accounts and custom installtion directory
Hi, First, if I install MySQL on a win32 system which are the default account and from which hosts can those be accesses? Which of these accounts am I encouraged to remove or change the password of? I know that the root/ accounts needs to be changed but what else is there? Secondly, if I move like to move the entire C:\mysql directory to some other disk, can I do so without re-installing? If I need to change to registry setting and/or .ini configuration file, what key/files are these? Sincerly, /m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT !! InnoDB Constraint problem !
On Sun, 6 Jun 2004 15:48:37 +0200 Dimitri Bosiers [EMAIL PROTECTED] wrote: snip INSERT INTO pages(hidden,name,page_number,data,is_section_root,layout_id,group_ id) VALUES(1,'nieuwe pagina',100,'',2,1,@LID); this fails with a ERROR 1216: Cannot add a child row: a foreign key constraint fails Run: SHOW INNODB STATUS; And post the results in the Latest Foreign Key Error section. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery help...
On Sat, 5 Jun 2004 21:10:42 -0600 Daniel Isenhower [EMAIL PROTECTED] wrote: First off, I assume you are using a version of mysql able to handle sub-queries. 4.1 or 5.0 (4.0.xx does NOT support sub-queries) Ugh... I feel dumb :) I'm using 4.0 No worries, there are too many versions of MySQL to choose from... FWIW, this is an easy query with a JOIN: SELECT id FROM work w INNER JOIN client_list cl ON cl.id = w.client_id WHERE cl.name = 'Some Company'; Just in case you are using mysql 4.0 or earlier... Thanks very much! This is what I need :) Also, while I'm at it, any book recommendations for getting to know MySQL better? I've always liked the reference manual, but it's essentially a reprint of the online documentation, which is excellent. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Err 2002 socks suck noway to start
Thansk for your suggestion Steve, see what I have. I don't know why by default server wants a sock in /var/lib/mysql and why it doesnt exists anymore ? How to create a new one ? A search [EMAIL PROTECTED] [~]# find / -name mysql.sock /var/tmp/mysql.sock /tmp/mysql.sock /usr/local/lib/mysql.sock I try to force sock in tmp dir [EMAIL PROTECTED] [~]# tail /etc/my.cnf [client] socket=/tmp/mysql.sock [mysqld] set-variable = max_connections=500 socket=/tmp/mysql.sock [EMAIL PROTECTED] [~]# mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) [EMAIL PROTECTED] [~]# tail ~/.my.cnf [client] user=root pass=** [mysqladmin] password=** user=root and now # is a comment, without any directive [EMAIL PROTECTED] [~]# tail /etc/my.cnf #[client] #socket=/tmp/mysql.sock [mysqld] set-variable = max_connections=500 #socket=/tmp/mysql.sock [EMAIL PROTECTED] [~]# mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) You advise me to test the other socks ? Thanks --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.693 / Virus Database: 454 - Release Date: 31/05/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Err 2002 socks suck noway to start
Noway :( [EMAIL PROTECTED] [/etc]# vi my.cnf [EMAIL PROTECTED] [/etc]# service mysql start [EMAIL PROTECTED] [/etc]# mysql ERROR 2002: Can't connect to local MySQL server through socket '/usr/local/lib/mysql.sock' (2) [EMAIL PROTECTED] [/etc]# vi my.cnf [EMAIL PROTECTED] [/etc]# service mysql start [EMAIL PROTECTED] [/etc]# mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/tmp/mysql.sock' (2) Thanks --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.693 / Virus Database: 454 - Release Date: 31/05/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0: ERROR 1005 (HY000)
saiph wrote: create database url; use url create table cath ( name varchar(10) primary key, ) type=innodb; create table site ( url varchar(40) primary key, aline varchar(40), cath varchar(10), constraint fk_cath foreign key(cath) references cath(name) on delete set null on update cascade ) type=innodb; ERROR 1005 (HY000): Can't create table './url/site.frm' (errno: 150) the perms are correct indeed without the constraint there are no problems. You have to put an index on cath before you can use it as a foreign key. See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP RPM + 3 binary install of Mysql-4.0.20
At 1:54 -0400 6/6/04, Minuk Choi wrote: I have redhat9. I removed the RPM install of MySQL, and instead installed 3 copies of MySQL-4.0.20 BINARY distributions into my /usr/local/ directory, under the helpful instructions of Thierno Cisse. However, it seems like now, the PHP RPM installation cannot find the mysql extension. When I try to load up phpMyAdmin-2.5.6(after changing the port and socket values to reflect the mysql directory), I get cannot load MySQL extension, please check PHP Configuration. Documentation After prodding around, my guess is that because when I uninstalled the MySQL RPM, the MySQL extension was also removed and the PHP RPM installation can no longer locate the MySQL directory. My question is... how do I resolve this; in the case of 1 PHP installation and multiple copies of MySQL Binary distribution installations, make PHP access the correct MySQL installation?? The general how-to-access-the-correct-server problem is solved like this: The three different servers must be listening to different TCP/IP port numbers and Unix socket files. Tell your client to connect to either the TCP/IP port or socket file corresponding to the server that you want to access. However, your problem is more fundamental. By removing MySQL support for PHP, you now cannot connect to any MySQL server at all. You'll need to reinstall that support. I don't know if you can do that for the PHP RPM without reinstalling the MySQL RPM; if not, you'll probably need to rebuild PHP yourself, with support for MySQL included. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Default accounts and custom installtion directory
At 16:00 +0200 6/6/04, Martin Olsson wrote: Hi, First, if I install MySQL on a win32 system which are the default account and from which hosts can those be accesses? Which of these accounts am I encouraged to remove or change the password of? I know that the root/ accounts needs to be changed but what else is there? The initial accounts and what to do with them are described in the post-installation section of the MySQL Reference Manual: http://dev.mysql.com/doc/mysql/en/Post-installation.html http://dev.mysql.com/doc/mysql/en/Windows_post-installation.html http://dev.mysql.com/doc/mysql/en/Default_privileges.html Secondly, if I move like to move the entire C:\mysql directory to some other disk, can I do so without re-installing? If I need to change to registry setting and/or .ini configuration file, what key/files are these? Yes, you can move it. You should specify the new values for basedir and datadir in the [mysqld] section of an option file. The allowable option files on Windows are described in the Reference Manual as well. http://dev.mysql.com/doc/mysql/en/Option_files.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Asterisks in Integer Columns
Suppose I have several columns of numberals - area, popoulation, etc. - and I want to include asterisks and footnotes, as in below: 200 4200 258* 234 24 258superscript1/superscsript 2400 What are some good strategies for doing this? You really aren't supposed to include asterisks in integer columns, right? It would also be nice to have a strategy that would give you the option of masking asterisks and footnotes when you don't want them displayed. Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Asterisks in Integer Columns
One simple option would be to add a footnote column. And add in your code, if footnote column is NOT NULL then add a * on to population and show footnote at the bottom. population footnote 100 null 200*yada yada Suppose I have several columns of numberals - area, popoulation, etc. - and I want to include asterisks and footnotes, as in below: 200 4200 258* 234 24 258superscript1/superscsript 2400 What are some good strategies for doing this? You really aren't supposed to include asterisks in integer columns, right? It would also be nice to have a strategy that would give you the option of masking asterisks and footnotes when you don't want them displayed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Err 2002 socks suck noway to start
# locate mysql.server # /path/to/mysql.server start what error it gives ? Peter Zyumbilev Project Manager Mansion Productions Inc. AIM: peterZmansion YMS: peterZmansion MSN: [EMAIL PROTECTED] Skype: peterZmansion ICQ: 108984925 support: http://support.hostmansion.com (24/7) email: [EMAIL PROTECTED] web: www.mansionproductions.com MPA2 - Mansion Production Automation Program MPA3 - Next Generation Automation Program CSS - Content Sale System MTS - Mansion Ticket System Hostmansion.com - Highest Quality Web Hosting The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the intended recipient of this message you are hereby notified that any use, review, retransmission, dissemination, distribution, reproduction or any action taken in reliance upon this message is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of the company. - Original Message - From: pourave To: Steve Buehler ; [EMAIL PROTECTED] Sent: Sunday, June 06, 2004 6:11 PM Subject: Re: Err 2002 socks suck noway to start Noway :( [EMAIL PROTECTED] [/etc]# vi my.cnf [EMAIL PROTECTED] [/etc]# service mysql start [EMAIL PROTECTED] [/etc]# mysql ERROR 2002: Can't connect to local MySQL server through socket '/usr/local/lib/mysql.sock' (2) [EMAIL PROTECTED] [/etc]# vi my.cnf [EMAIL PROTECTED] [/etc]# service mysql start [EMAIL PROTECTED] [/etc]# mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/tmp/mysql.sock' (2) Thanks --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.693 / Virus Database: 454 - Release Date: 31/05/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Err 2002 socks suck noway to start
[EMAIL PROTECTED] [/tmp]# locate mysql.server /usr/share/mysql/mysql.server [EMAIL PROTECTED] [/tmp]# /usr/share/mysql/mysql.server start [EMAIL PROTECTED] [/tmp]# it seems nothing ... should I have to see in some log file ? Thanks. - Original Message - # locate mysql.server # /path/to/mysql.server start what error it gives ? Peter Zyumbilev --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.693 / Virus Database: 454 - Release Date: 01/06/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Err 2002 socks suck noway to start
Blank does not mean bad try to connect with mysql client if not try #netstat -nvatp |grep mysql if you see nothing #locate .err You shold find some file lie your_hostname.err #tail /path/to/that_file.err aslo you may try #cat /var/log/messages |grep mysql Peter Zyumbilev Project Manager Mansion Productions Inc. AIM: peterZmansion YMS: peterZmansion MSN: [EMAIL PROTECTED] Skype: peterZmansion ICQ: 108984925 support: http://support.hostmansion.com (24/7) email: [EMAIL PROTECTED] web: www.mansionproductions.com MPA2 - Mansion Production Automation Program MPA3 - Next Generation Automation Program CSS - Content Sale System MTS - Mansion Ticket System Hostmansion.com - Highest Quality Web Hosting The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the intended recipient of this message you are hereby notified that any use, review, retransmission, dissemination, distribution, reproduction or any action taken in reliance upon this message is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of the company. - Original Message - From: pourave To: Peter ; [EMAIL PROTECTED] Sent: Monday, June 07, 2004 1:38 AM Subject: Re: Err 2002 socks suck noway to start [EMAIL PROTECTED] [/tmp]# locate mysql.server /usr/share/mysql/mysql.server [EMAIL PROTECTED] [/tmp]# /usr/share/mysql/mysql.server start [EMAIL PROTECTED] [/tmp]# it seems nothing ... should I have to see in some log file ? Thanks. - Original Message - # locate mysql.server # /path/to/mysql.server start what error it gives ? Peter Zyumbilev --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.693 / Virus Database: 454 - Release Date: 01/06/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test disregard
-- = - Linux rocks!!! http://www.dedserius.com/ = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I'm working on a library redesign, and need help
Please CC me, I am on digest. What is an int(11) as in the following table creation statement? I looked online at the documentation, and not only is the documentation MUCH different than the last time I looked at it, (been awhile), but I didn't see anything related to a parameter to the size of an int field that looked like 11. BTW, why does the make of the definition below have backtick quotes on all names? CREATE TABLE calendar_events` ( `eventID` int(11) NOT NULL auto_increment, `eventTitle` varchar(20) default NULL, `eventMessage` varchar(200) default NULL, `eventTime` time default NULL, `eventDate` date default NULL, PRIMARY KEY (`eventID`) ) TYPE=MyISAM; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I'm working on a library redesign, and need help
Dennis Gearon wrote: Please CC me, I am on digest. What is an int(11) as in the following table creation statement? I looked online at the documentation, and not only is the documentation MUCH different than the last time I looked at it, (been awhile), but I didn't see anything related to a parameter to the size of an int field that looked like 11. BTW, why does the make of the definition below have backtick quotes on all names? CREATE TABLE calendar_events` ( `eventID` int(11) NOT NULL auto_increment, `eventTitle` varchar(20) default NULL, `eventMessage` varchar(200) default NULL, `eventTime` time default NULL, `eventDate` date default NULL, PRIMARY KEY (`eventID`) ) TYPE=MyISAM; As far as I know, the number in brackets eg int(11) is only used for numeric fields when you are importing from a fixed-width text file, with 'load data infile'. In this case, the width of the fields is taken from the number in brackets from the table definition. Pretty strange stuff if you ask me. Anyway you can safely ignore it if you're not importing data from a fixed-width file. For more info see 'load data infile'. The backticks are there to aid MySQL in parsing the string later in case you have used reserved words or strange characters. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: Errors from Extra Spaces in Spreadsheets
I've been having a tough time importing comma-delimited files into my database tables. I just discovered that most of the errors are similar to this one: 060 is not a valid integer value I checked my spreadsheet - Microsoft Works - and discovered that there's a space after many of my numerals. I'm not sure how to remove these spaces. I didn't see anything in the Help files. I could do a search and replace, but that would also wipe out spaces between words - like... TrippspaceCounty I just wondered if there's a simple trick anyone knows of for getting rid of spaces like these or preventing them in the first place. Thanks. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stop query on first match
Hello all, I see a similar question was asked before, but it was not answered. I hope someone can assist me. My query uses two tables. The query selects one row on table b for each row on table a, but uses between in the select. It can only ever return one row from table b, for each row on table a, due to the contents that is stored in table b. The table contains in excess of a million records. What happens as a result of the between is that for the query, several rows seem to be candidates on table b, but once the query evaluates and sifts through the candidate rows on table b, only one row will ever match. So if I could inform MySql to stop the query for the particular row, once one row on table b matches the row on table a, the query would return hundreds of times faster. As an experiment I took one example and used limit and the query reduced from 4 secs to .01 sec. However, when doing the 'live' query, I cannot use limit because, I do not want overall only 1 row returned, I want one row returned for each of the rows from table a which has 1 match each on table b. I've looked in several books and searched Google but cannot get a way of doing this. It seems Oracle has a 'FIRST' in their select which they use for such a use case. But I do not see anything for MySql anywhere. Could someone please assist me. Kind regards Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advice on Database Schema Design
I think I get what you are trying to do and it sounds good (though I am still a beginner). The only thing I can think that might cause a problem is since you have more then a couple of tables you MAY have to write joins for all of them . Only you would know if you can do that and/or be comfortable doing it. To get a stronger idea though of your design I would need to see either a logical ER diagram or all of the creates (I am very visual so I like pictures). *shrugs* whether or not you want to pass those along is up to you. Respectfully, Ligaya Turmelle Robert A. Rosenberg [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I am converting a form that was originally designed to be Email Submitted into a Submit-to-PHP-Page Form (which will then insert the data into a MySQL Database). I expect no problems in actually scanning the submission to extract the data but I have some questions on the design of the Tables that I will need to define to store the data. I have done some research and have come up with a tentative design that I want to post here for critiquing. Here is what I have come up with. Each form will get assigned a sequence number (SubmitterID) that will identify the form submission in all the tables (thus linking them). There are a number of TextAreas where the user is requested to enter free form replies to questions. I am thinking that these should go into a separate table as Text fields of the correct size with SubmitterID as the Primary Key. That keeps the data away from the main table and thus only accessed when needed/requested as well as not bloating the size of the main table or slowing its retrieval/processing. There are a number of blocks of CheckBoxes on the form. While I could, in theory, use a SET column type to store them, I get the impression that a better way is to create a Many-to-One table for each block with the total contents of each row being the CheckBoxID and SubmitterID (in that order) as the Primary Key. A Index for SubmitterID would also be defined. This way I can do a WHERE on either Column and get Index Usage as opposed to needing to do a row-by-row lookup. I have the impression that doing a WHERE over a SET Column (especially when I'm looking for more than one value) is not a good or efficient idea. The CheckBoxID would map to a 3rd table to get the actual CheckBoxName. There are also two Select Tables (one for US States and one for Countries). While the States are passing the USPS 2-Letter State Codes, the Countries are passing the full Country Name as their OPTION VALUE=. I want to make a State Table and a Country Table using respectively the 2-Letter Code and a sequential reference number (which I will revise the Country OPTION tags to use as their VALUE) as the Primary Key with the State or Country Name as the other column. To create the reference key and populate the OPTION pages with it I plan to take the current HTML for these tags and read them into a Text Editor where I will then alter them into MySQL Insert Commands to populate the Table. Then using a one-shot QuickDirty PHP page, I will read the table and recreate the Option Statements which would the be CutPasted into the original HTML code replacing the old versions of the tags. The states go though the same Turn into INSERT Commands but there is no need for post processing or HTML Tag replacement. Of course all the Table Pointers would be defined as Foreign Keys to insure Referential Integrity. Am I making any mistakes in my design or am I on the correct track. I welcome any critique of my design or advice on how to improve it. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stop query on first match
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Monday 07 June 2004 12:49 am, Emmanuel van der Meulen wrote: I've looked in several books and searched Google but cannot get a way of doing this. It seems Oracle has a 'FIRST' in their select which they use for such a use case. But I do not see anything for MySql anywhere. If your using a later mysql, you could use sub query with limit. Other then that, splitting the query is the only think off hand. - -- I am under the influence of sugar, caffeine, and lack of sleep, and should not be held responsible for my behavior. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAxAMEld4MRA3gEwYRAgc5AJ0behkfDwijlpSANX1oXsRfNtwmKgCeOF4b j9366DtZYNuo2j0aTvQsudY= =ecrd -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
